|
|
| S. No. |
Claim Type |
Claim No. |
Policy Type |
Claim Entry Date |
Memberid |
Sbu Id |
Policy No |
Insured |
Patient |
Category |
Relation |
Age |
Sex |
Date of Admission |
AL Date |
Date of Discharge |
Sum Insured |
Service Tax |
Preauth Amount |
Claimed Amount |
Passed Amount |
TDS Amount |
Deduction |
Status |
Passed Date |
Float Date |
Settled Date |
Cheque Date |
Cheque No. |
Query Date |
Nature of illness |
ICD Code |
Almtype |
Doctor Opinion |
Hospital |
Hospital Location |
Development Officer |
Pending Query Details |
Error ygtfttddcrjava.lang.NullPointerExceptionSELECT DISTINCT crd.CLAIM_NO,PHD.MEMBERID,pm.POLICY_NUMBER,GETSTATUS(CRD.CLAIM_NO) STATUS,GETHOLDERNAMEBYID(CRD.POLICY_INSURED_ID) HOLDERNAME,pid.NAME PATIENT_NAME,PHD.CATEGORY,MR.RELATION,DECODE(Getsitypeid(CRD.POLICY_INSURED_ID),'S',Getsi(CRD.POLICY_INSURED_ID),'F',Getcoverageofsingleinsured(CRD.POLICY_INSUREd_ID),Getsi(CRD.POLICY_INSURED_ID)) SUM_INSURED,GETTDSDEDUCTAMOUNT(crd.CLAIM_NO) TDS_AMOUNT,DECODE(CRD.CLAIM_TYPE,'N','Cashless','Reimbursement') CLAIM_TYPE,PHD.SBU_ID,DECODE(NVL(CR.CLAIMAMOUNT,0),0,GETCLAIMAMOUNTFROMCLAIMSTAGES(crd.CLAIM_NO),CR.CLAIMAMOUNT) CLAIMAMOUNT,cp.PASSAMOUNT,GETDEDUCTIOND(CRD.CLAIM_NO) DEDUCTION,DECODE(PM.CORPORATEID,'0','Individual','Corporate') POLICY_TYPE,PID.SEX,DECODE(CR.FINAL_DIAGNOSIS,NULL,CRD.DISEASE,CR.FINAL_DIAGNOSIS) FINAL_DIAGNOSIS,TO_CHAR(CRD.ADMISSION_DATE,'DD-MON-YYYY') ADMISSION_DATE,(SELECT MAX(TO_CHAR(cs.ACTION_DATE,'DD-MON-YYYY')) FROM CLAIM_STAGES cs WHERE cs.STAGE_CODE=3 AND cs.CLAIM_NO=CRD.CLAIM_NO) AL_DATE,Getauthorisedamount(CRD.CLAIM_NO) APPROVED_AMOUNT, (SELECT TO_CHAR(MAX(cs.ACTION_DATE),'DD-MON-YYYY') FROM CLAIM_STAGES cs WHERE cs.STAGE_CODE=30 AND cs.CLAIM_NO=CRD.CLAIM_NO) QUERY_DATE, (SELECT TO_CHAR(MAX(cs.ACTION_DATE),'DD-MON-YYYY') FROM CLAIM_STAGES cs WHERE cs.STAGE_CODE=11 AND cs.CLAIM_NO=CRD.CLAIM_NO) PASSED_DATE, (SELECT TO_CHAR(MAX(cs.ACTION_DATE),'DD-MON-YYYY') FROM CLAIM_STAGES cs WHERE cs.STAGE_CODE=12 AND cs.CLAIM_NO=CRD.CLAIM_NO) FLOAT_DATE, (SELECT TO_CHAR(MAX(cs.ACTION_DATE),'DD-MON-YYYY') FROM CLAIM_STAGES cs WHERE cs.STAGE_CODE=13 AND cs.CLAIM_NO=CRD.CLAIM_NO) SETTLED_DATE, TO_CHAR(CR.DISCHARGE_DATE,'DD-MON-YYYY') DISCHARGE_DATE,ROUND((SYSDATE-PID.DATE_OF_BIRTH)/365) AGE,TO_CHAR(crd.CLAIMENTRYDATE,'DD-MON-YYYY') CLAIMENTRYDATE,TO_CHAR(CP.CHEQUE_DATE,'DD-MON-YYYY') CHEQUE_DATE,CP.CHEQUE_NO,pm.DEV_OFFICE_CODE,GETQUERYBYCLAIMNO(crd.CLAIM_NO) pending_doc, DECODE(CRD.HOSPITALID,'202',CRD.OTHER_HOSPITAL,hm.HOSPITALNAME) HOSPITAL_NAME,replace(UPPER(HM.LOCATION),'
',' ') LOCATION,DECODE(CR.ICD_CODE,NULL,CRD.ICDCODE,CR.ICD_CODE) ICD_CODE,DECODE(CRD.ALMTYPE,0,'Major',1,'Minor',2,'Dental',3,'Meternity',5,'OPD') ALMTYPE,replace(UPPER(CP.DOCTORS_OPINION),'
',' ') DOCTORS_OPINION,GETSERVICETAX(crd.CLAIM_NO) SERVICE_TAX FROM POLICY_MASTER pm,CASHLESS_REQUEST_DETAILS crd,CLAIM_PROCESSED cp,INS_POLICY_TYPE ipt,CLAIM_RECEIVED cr,POLICY_INSURED_DETAILS PID,MASTER_RELATION mr,HOSPITAL_MASTER HM,POLICY_HOLDER_DETAILS PHD WHERE crd.POLICY_ID=pm.POLICY_ID AND PM.POLICY_NUMBER LIKE 'null%' AND cp.CLAIM_NO(+)=crd.CLAIM_NO AND ipt.POLICY_TYPE_ID=pm.POLICY_TYPE_ID AND cr.CLAIM_NO(+)=crd.CLAIM_NO AND crd.CLAIMENTRYDATE BETWEEN TO_DATE('null-null-null:12:00 AM','DD-Mon-YYYY:HH:MI AM') AND TO_DATE('null-null-null:11:59 PM','DD-Mon-YYYY:HH:MI PM') AND CRD.POLICY_INSURED_ID=PID.POLICY_INSURED_ID AND MR.RELATION_ID=PID.RELATION_ID AND CRD.HOSPITALID=HM.HOSPITALID AND PID.POLICY_HOLDER_ID=PHD.POLICY_HOLDER_ID