Question
· Dec 10, 2024

ERROR #5821: Cannot instantiate query: 'SQLCODE = -146, %msg = Error: '' is an invalid DISPLAY Date value'

We have error - ERROR #5821: Cannot instantiate query: 'SQLCODE = -146, %msg = Error: '' is an invalid DISPLAY Date value' not able to see what is causing:

CASE When P.PatReltoGuar->Name = 'SELF' then  P.Lnm  Else  Substring(P.GuarNmIfNotPat, 1,Charindex(',',P.GuarNmIfNotPat)-1) End  as DemoGuarLastName,

CASE When P.PatReltoGuar->Name = 'SELF' then  P.Fnm  Else Substring(P.GuarNmIfNotPat,Charindex(',',P.GuarNmIfNotPat)+1,LEN(P.GuarNmIfNotPat)) End  AS DemoGUARFIRSTNAME,

CASE When P.PatReltoGuar->Name = 'SELF' then P.StreetAddrL1 Else P.GuarStreetAddrL1 End as DemoGuarAddresslineOne,

CASE When P.PatReltoGuar->Name = 'SELF' then P.StreetAddrL2 Else P.GuarStreetAddrL2  End as DemoGuarAddressLineTwo,

CASE When P.PatReltoGuar->Name = 'SELF' then Substring(P.CtySt, 1,Charindex(',',P.CtySt)-1) Else Substring(P.GuarCtyst, 1,Charindex(',',P.GuarCtyst)-1) End  as DemoGuarCity ,

CASE When P.PatReltoGuar->Name = 'SELF' then Substring(P.CtySt,Charindex(',',P.CtySt)+1,LEN(P.CtySt))  Else Substring(P.GuarCtyst,Charindex(',',P.GuarCtyst)+1,LEN(P.GuarCtyst)) End  as DemoGuarState ,

CASE When P.PatReltoGuar->Name = 'SELF' then P.Zip Else P.GuarZip End  AS DemoGUARZIP,

CASE When P.PatReltoGuar->Name = 'SELF' then P.Tel  Else P.GuarTel End as DemoGtel ,
CASE When P.PatReltoGuar->Name = 'SELF' then P.Ssn Else P.GuarSsn End as DemoGSSN ,
CASE When P.PatReltoGuar->Name = 'SELF' then P.PatEmployerFtxt  Else P.GuarEmp end as DemoGEmployer ,

CASE When P.PatReltoGuar->Name = 'SELF' then P.EmpTel Else P.GuarEmpTel End as DemoGEmpTel ,

CASE When P.PatReltoGuar->Name = 'SELF' then P.EmailAddress Else P.GuarEmail  end  as DemoGGEmail ,

CASE When P.PatReltoGuar->Name = 'SELF' then P.Dob Else P.GuarDob end  as DemoGDOB ,

V.GuarId->PatRelToGuar->Name as VisitlevelPatientReltoGuarantor,

V.GuarId as VisitGuarID,

V.GuarId->Mrn as VisitGUARMRN,

CASE When P.PatReltoGuar->Name = 'SELF' then  P.Lnm  Else  Substring(V.GuarId->GuarNmIfNotPat, 1,Charindex(',',V.GuarId->GuarNmIfNotPat)-1) End  as VisitGUARlastname ,

CASE When P.PatReltoGuar->Name = 'SELF' then  P.Fnm  Else Substring(V.GuarId->GuarNmIfNotPat,Charindex(',',V.GuarId->GuarNmIfNotPat)+1,LEN(V.GuarId->GuarNmIfNotPat)) End  as VisitGuarfirstname,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.StreetAddrL1 Else V.GuarId->GuarStreetAddrL1 End  as visitguaradresslineone,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.StreetAddrL2 Else V.GuarId->GuarStreetAddrL2 End as visitguaraddresslinetwo,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then Substring(P.CtySt, 1,Charindex(',',P.CtySt)-1) Else Substring(V.GuarId->GuarCtyst, 1,Charindex(',',V.GuarId->GuarCtyst)-1) End  as visitguarcity ,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then Substring(P.CtySt,Charindex(',',P.CtySt)+1,LEN(P.CtySt))  Else Substring(V.GuarId->GuarCtyst,Charindex(',',V.GuarId->GuarCtyst)+1,LEN(V.GuarId->GuarCtyst)) End  as visitguarstate ,

CASE When  V.GuarId->PatRelToGuar->Name = 'SELF' then P.Zip Else V.GuarId->GuarZip End as visitguarzipcode,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.Tel  Else V.GuarId->GuarTel End as visitguartel,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.Ssn Else V.GuarId->GuarSsn End as visitguarssn,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.PatEmployerFtxt Else V.GuarId->GuarEmpDict End as visitguaremployer,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.EmpTel Else V.GuarId->GuarEmpTel End as visitguaremplytel,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.EmailAddress Else V.GuarId->GuarEmail End as visitguaremail,

CASE When V.GuarId->PatRelToGuar->Name = 'SELF' then P.Dob Else V.GuarId->GuarDob End as visitguardob,

P.Lnm as PatientLastName,
 P.Fnm   as PatientFirstName,
P.StreetAddrL1 as PatientAddrLineOne,
P.StreetAddrL2 as PatientAddrLineTwo,
Substring(P.CtySt, 1,Charindex(',',P.CtySt)-1) as PatientCity,
Substring(P.CtySt,Charindex(',',P.CtySt)+1,LEN(P.CtySt)) as PatientState,
P.Zip as PatientZip,
P.Tel as PatientTel,
P.Ssn as PatientSSN,
P.PatEmployerFtxt as PatientEPlaceofEmployment,
P.PtEmploymentStatus->Name as PatientEmployementStatus,
P.EmpTel as PatientEmpTel,
P.EmailAddress as PatientEmailAddr,
P.Dob as PatientDOB,

V.Number as VisitNumber,

Convert(Varchar(80),P.Mrn) as PatientMRN,

VC.Charges,

(Coalesce(VC.Charges, 0) - Coalesce(VC.TotProfChg, 0))/100 as TotHospCharges ,

VC.TotProfChg,

AC.TotalPayments,

AC.TotProfPayments, 

AC.TotSelfPayPayments ,

AC.TotProfSelfPayPayments,

AC.TotalAdjustments,

(Coalesce(AC.TotalPayments, 0) - Coalesce(AC.TotSelfPayPayments, 0))/100 AS INSPAYMENTS,

(Coalesce(AC.TotalPayments,0) - Coalesce(AC.TotSelfPayPayments,0)- Coalesce( AC.TotProfPayments,0))/100 AS HOSPPYT,

(Coalesce(AC.TotalPayments,0) - Coalesce(AC.TotSelfPayPayments,0)- Coalesce( AC.TotProfPayments,0)+ Coalesce(AC.TotProfSelfPayPayments,0))/100 AS HOSPINSPYT,

(Coalesce(AC.TotProfPayments,0) - Coalesce(AC.TotProfSelfPayPayments,0))/100 AS PROFINSPYT ,

(Coalesce(V.AcctReceivable->TotalAdjustments,0) - Coalesce(V.AcctReceivable->TotProfAdj,0))/100 AS HOSPADJ  ,

(Coalesce(V.AcctReceivable->TotalAdjustments,0) - Coalesce(V.AcctReceivable->TotProfAdj,0) - Coalesce(V.AcctReceivable->TotSelfPayAdj,0) + Coalesce(V.AcctReceivable->TotProfSelfPayAdj,0))/100  AS HOSPINSADJ,

(Coalesce(V.AcctReceivable->TotSelfPayAdj,0) - Coalesce(V.AcctReceivable->TotProfSelfPayAdj,0))/100  AS HOSPSELFPAYADJ,

(Coalesce(V.AcctReceivable->TotProfAdj,0) - Coalesce(V.AcctReceivable->TotSelfPayAdj,0))/100 AS PROFINSADJ ,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Billing Adjustment'
AND RT.Code->RestrictPostToHospOrProf = 'H') AS Hosp_Bill_Adj,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Credit Adjustment'
  AND RT.Code->RestrictPostToHospOrProf = 'H'
) AS HOSP_Credit_Adj,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Transfer Balance Adjustment' AND RT.Code->RestrictPostToHospOrProf = 'H'
   AND Amount<0) AS HOSP_Txfr_Adj,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Billing Adjustment'
AND RT.Code->RestrictPostToHospOrProf = 'P') AS PROF_Bill_Adj,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Credit Adjustment'
  AND RT.Code->RestrictPostToHospOrProf = 'P'
) AS PROF_Credit_Adj,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Transfer Balance Adjustment' AND RT.Code->RestrictPostToHospOrProf = 'P'
   AND Amount<0) AS PROF_Txfr_Adj,

(Coalesce(AC.TotalPayments, 0) - Coalesce(AC.TotProfPayments, 0))/100 AS TotalHospitalPayments,
(Coalesce(AC.TotalPayments, 0) -  Coalesce(AC.TotProfPayments, 0) - Coalesce(AC.TotSelfPayPayments, 0) + Coalesce(AC.TotProfSelfPayPayments,0))/100  AS HospitalInsurancePayments,

(Coalesce(AC.TotSelfPayPayments, 0) - Coalesce(AC.TotProfSelfPayPayments, 0))/100 AS TotalHospitalSelfPayPayments,

(Coalesce(Ac.ArAccountBalance, 0) - Coalesce(AC.ArSelfPayBalance, 0))/100 AS CurrentInsuranceBalance,
COALESCE(AC.ArSelfPayBalance, 0) AS CurrentSelfpayBalance,
COALESCE(Ac.ArAccountBalance, 0) AS VisitBalance,

(Coalesce(Ac.ArAccountBalance, 0) - Coalesce(AC.ProfBal, 0 ))/100 AS CurrentHospitalBalance,

((Coalesce(V.AcctReceivable->ArAccountBalance,  0)  -  Coalesce(V.AcctReceivable->ArSelfPayBalance, 0))/100 -  (Coalesce(V.AcctReceivable->ProfBal, 0) - Coalesce(V.AcctReceivable->ArSelfPayBalanceProf, 0))/100)
AS CurrentHospitalInsBalance,
(Coalesce(AC.ArSelfPayBalance, 0) - Coalesce(AC.ArSelfPayBalanceProf, 0))/100 AS CurrentHospitalSelfPayBalance,

V.AcctReceivable->ProfBal AS ProfesionalVisitBalance,
 (Coalesce(V.AcctReceivable->ProfBal, 0) - Coalesce(V.AcctReceivable->ArSelfPayBalanceProf, 0))/100  as ProfessionalInsuranceVisitBalance,
V.AcctReceivable->ArSelfPayBalanceProf as ProfessionalSelfPayBalance,
AC.ArSelfPayBalance,
V.AcctReceivable->FirstStatementDate,
V.AcctReceivable->LastStatementDate,
V.AcctReceivable->PaymentLastStmt,
V.AcctReceivable->SelfPayPymtLastStmt,
V.AcctReceivable->PymtThisCycle,
V.AcctReceivable->SelfPayPymtThisCyc,
V.AcctReceivable->ProfLastPymtDt,
V.AcctReceivable->ProfBalLastStmt,
V.AcctReceivable->ProfSelfPayBalLastStmt,

(SELECT MAX(RT.PostingDate)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments' 
AND RT.Code->RestrictPostToHospOrProf = 'H') AS Last_Hosp_Payment_Date,
 

(SELECT MAX(RT.PostingDate)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
  AND RT.Code->TransactionType='Payments' 
AND RT.Code->RestrictPostToHospOrProf = 'P') AS Last_Prof_Payment_Date,

V.AdmDt as DateofAdmission,
V.DischDt as DateofDischarge,

 VD.PlanDescr as PrimaryInsurancePlanName ,
IM.CertNum as PrimaryPlanInsuranceCertNum,

 SVD.PlanDescr as SecondaryInsurancePlanName ,
SIM.CertNum as SecondaryInsurancePlanCert,

 TVD.PlanDescr as TertiaryInsurancePlanName ,
TIM.CertNum as TertiaryInsurancePlanCert,

V.PRIMFSC->HpaFscCategory->Name as PrimaryInsFSCCategory,
P.VIPcode->Name as VIPName,
P.Deceased as IsPatientDeceased,
P.DtOfDeath as DateofDeath,
V.PatientType->Name as VisittypeName,
P.UBadAddress as BadAddress,
P.UHomelessFlag,
Max(Fin.Appl.ApprDate) as LastDateofFinancialAssistanceApplication,
Max(Case when Fin.Appl.ApprDate is not null then Fin.Appl.Status->Name Else '' End) as FINStatus,
V.AcctReceivable->CurrentArFsc->Number as CurrentARFSCNum,
V.AcctReceivable->CurrentArFsc->Name as CurrentARFSCName,

(SELECT SUM(VM.Visit.AcctReceivable->ArSelfPayBalance)
 FROM VM.Visit

 WHERE  VM.Visit.Aid =  ACC.Aid    AND VM.Visit.AcctReceivable->ArSelfPayBalance <> 0
) AS Account_Balance,
AC.TotalBilledDollars,
AC.TotProfBilledDollars,
 (Coalesce(AC.TotalBilledDollars, 0) - Coalesce(AC.TotProfBilledDollars, 0))/100 AS HospitalBilledCharges,

(Coalesce(VC.TotProfChg, 0) - Coalesce(AC.TotProfBilledDollars, 0))/100 AS ProfessionalUnBilledDollars,

((Coalesce(VC.Charges, 0)  -  Coalesce(VC.TotProfChg, 0))/100 -  (Coalesce(AC.TotalBilledDollars, 0) - Coalesce(AC.TotProfBilledDollars, 0))/100) AS HospitalUnbilledCharges,

VC.TotalLateCharges,
VC.TotProfLateChg,

(VC.TotalLateCharges) - (VC.TotProfLateChg) AS HospitalLateCharges,
V.Vid AS VisitNumberInternalID,
Case When TX.FromVid  <> ' ' Then 'CombinedVisit' Else '' End as CombinedVisit,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments'
AND RT.Code->RestrictPostToHospOrProf = 'P') AS Professional_Total_Payments,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments'
AND RT.Code->RestrictPostToHospOrProf = 'P'
AND RT.Code->Number  IN ('18984','11968','17100','17144','17154','11965','11969','18989','11914','11915','11984','17100','18990')
) AS Professional_SelfPay_Payments,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments'
AND RT.Code->RestrictPostToHospOrProf = 'P'
AND RT.Code->Number  NOT IN ('18984','11968','17100','17144','17154','11965','11969','18989','11914','11915','11984','17100','18990')
) AS Professional_Insurance_Payments,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments'
AND RT.Code->RestrictPostToHospOrProf = 'H') AS Hospital_Total_Payments,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments'
AND RT.Code->RestrictPostToHospOrProf = 'H'
AND RT.Code->Number  IN ('8984','1968','7100','7144','7154','1965','1969','8989','1914','1915','1984','7100','8990')
) AS Hospital_SelfPay_Payments,

(SELECT SUM(RT.Amount)
 FROM HPA.AccountReceivableTx RT
 WHERE RT.AccountReceivable='2||'||V.Vid
   AND RT.Code->TransactionType='Payments'
AND RT.Code->RestrictPostToHospOrProf = 'H'
AND RT.Code->Number  NOT IN ('8984','1968','7100','7144','7154','1965','1969','8989','1914','1915','1984','7100','8990')
) AS Hospital_Insurance_Payments,

P.ULegAbdh ,
P.ULegAcct ,
P.ULegMva ,
P.ULegWcc 

From
Registration.Patient P
JOIN VM.Visit V On P.Id = V.Id 

Left Join  HPA.Account ACC On ACC.Aid = V.Aid
LEFT JOIN  HPA.VisitCharge VC on (VC.GRP = V.Grp and VC.Vid = V.Vid)
LEFT JOIN HPA.AccountReceivable AC ON ( Ac.Grp = V.Grp and AC.Vid = V.Vid)
Left Join HPA.AccountReceivableTX TX On V.Visit = TX.Visit
Left Join Fin.Appl ON(P.%Id = Fin.Appl.Patient)
LEFT Join VM.VisitPlan VP on (V.%Id=VP.Visit) and VP.PN = '1 '
LEFT Join VM.VisitPlanDetail VD on VP.Id=VD.VisitPlan
LEFT Join VM.VisitPlanDetailImsData IM on ( IM.VisitPlanDetail = VD.%Id)
LEFT Join VM.VisitPlan SVP on (V.%Id=SVP.Visit) and SVP.PN = '2 '
LEFT Join VM.VisitPlanDetail SVD on SVP.Id=SVD.VisitPlan
LEFT Join VM.VisitPlanDetailImsData SIM on ( SIM.VisitPlanDetail = SVD.%Id)
LEFT Join VM.VisitPlan TVP on (V.%Id=TVP.Visit) and TVP.PN = '3 '
LEFT Join VM.VisitPlanDetail TVD on TVP.Id=TVD.VisitPlan
LEFT Join VM.VisitPlanDetailImsData TIM on ( TIM.VisitPlanDetail = TVD.%Id)

Where
 TX.AccountReceivable->Vid = V.Vid  
and AC.ArSelfPayBalance > 0
and (Ac.ArAccountBalance) - (AC.ArSelfPayBalance) = 0
and  (VC.Charges)  -  (AC.TotalBilledDollars)  = 0
and V.GuarId->PatRelToGuar->Name <> 'VENDOR' 
AND V.AcctReceivable->CurrentArFsc->Number NOT IN ('650', '655', '657', '859', '879', '858', '869', '857', '856', '860', '652', '653' , '863', '864', '865', '866', '867', '635', '855', '861', '862', '868', '203', '625', '628')
AND  TX.PostingDate <> ''

AND 
((P.ULegAcct  is null) or
(P.ULegMva is null) or
(P.ULegWcc is null))

AND P.MRN NOT IN
('3925171',
'3935651',
'3925169',
'3928634',
'3981558',
'3981642',
'3925173',
'3928797',
'3925175',
'3934223',
'3933656',
'3970177',
'3970178',
'3925178',
'3925162',
'3925164',
'3925194',
'3932423',
'3925196',
'3925201',
'3970176',
'3933502',
'3994881',
'934855',
'941033',
'934857',
'2060513',
'8097980',
'8097979',
'660108968',
'3940069',
'3977063',
'3924744',
'3983194',
'1020572',
'3983191',
'2060485',
'2060194',
'2016146',
'887157',
'3924561',
'811381',
'877583',
'948206',
'861546',
'922473',
'845500',
'934029',
'940255',
'805655',
'855914',
'930741',
'2054362',
'930937',
'3939397',
'861540',
'968147',
'8097913',
'1021353',
'8142393',
'967198',
'8144486',
'853383',
'3933255',
'660097937',
'953296',
'955119',
'953650',
'845189',
'832618',
'765169',
'870833',
'910859',
'003924681',
'003998793',
'003931015',
'003924682',
'4007442',
'003938862',
'003924683',
'3932179',
'003937032',
'003924684',
'003960410',
'003986794',
'003924685',
'003994510',
'003924686',
'743128',
'3974817',
'4007860',
'3972915',
'3924687',
'665246',
'3969179',
'3932283',
'3925224',
'3924665',
'3924710',
'3960411',
'3936194',
'003924711',
'003924689',
'003931443',
'3924680',
'3932766',
'8108131',
'003924690',
'003924691',
'003924692',
'003924693',
'003924694',
'003938949',
'003933636',
'3924695',
'3969214',
'003938860',
'003950463',
'003924696',
'003924697',
'3998956',
'3924698',
'3924699',
'3924700',
'3924701',
'3924702',
'3948813',
'3924703',
'3924705',
'3924706',
'3932174',
'3924707',
'3927055',
'3924709',
'3931489',
'3983132',
'3988017',
'3924713',
'3924714',
'3938858',
'3938855',
'8082729',
'3924739',
'3924715',
'3924729',
'3924716',
'3924717',
'3924730',
'915490',
'3924718',
'3924719',
'3925239',
'3924720',
'3924721',
'3924722',
'3939024',
'3938856',
'3924723',
'3984464',
'3924724',
'3924725',
'3987736',
'3924726',
'3924727',
'3924735',
'3924728',
'2025928')

Group By

V.Number

Discussion (1)0
Log in or sign up to continue