Mountain View


SQL Practice

Posted On : 14-Dec-2018 [98]


Duplicate payment manager



create or replace
PROCEDURE              DUPLICATE_PAYMENTS_MANAGE_PROC AS 
CURSOR XGEN_CLAIM_NO_CUR IS 
SELECT  DISTINCT X1.CLAIM_NO FROM XGEN_CLAIM_MASTER X1 , XGEN_CLAIM_MASTER X2 WHERE X1.CLAIM_NO =X2.CLAIM_NO 
AND round(to_number(X1.PAYMENT_CODE)) = round(to_number(X2.PAYMENT_CODE)) 
AND X1.XGEN_SERVICE_NAME = 'doClaimSettlement'  
AND X2.XGEN_SERVICE_NAME = 'doClaimSettlement' 
AND trunc(X1.CREATED_DATE) >= trunc(SYSDATE -1)
AND X1.ID <> X2.ID;
CLAIM_NUM         XGEN_CLAIM_MASTER.CLAIM_NO%TYPE;
CURSOR XGEN_CLAIM_REC_CUR IS 
SELECT ID ,STATUS , REMARKS2  FROM XGEN_CLAIM_MASTER  WHERE CLAIM_NO =CLAIM_NUM AND XGEN_SERVICE_NAME ='doClaimSettlement' ORDER BY ID; 
TEMP_ID         XGEN_CLAIM_MASTER.id%type;

-- To remove duplicate records from claim_closure 

CLOSURE_CLAIM_NUM         CLAIM_CLOSURE.CLAIM_NUMBER%type;
CLOSURE_CLOSURE_ID        CLAIM_CLOSURE.CLOSURE_ID%type;

cursor CLAIM_CLOSURE_CUR is 
select max(CLOSURE_ID) as CLOSURE_ID , CLAIM_NUMBER as CLAIM_NUMBER_CLOSER from CLAIM_CLOSURE where CLOSURE_DECISION =0 and REJECTION_COMMENTS is null and CLAIM_NUMBER in (
SELECT CLAIM_NO FROM (
select COUNT(CLAIM_NUMBER) COUNT1  , CLAIM_NUMBER as CLAIM_NO from CLAIM_CLOSURE where CLOSURE_DECISION =0 and REJECTION_COMMENTS is null group by CLAIM_NUMBER , STREAM_NAME
) CLAIM_CLOSURE where COUNT1 >1
) group by CLAIM_NUMBER
;

-- end of closure

begin 
NO_PAYMENT_XML();
update XGEN_CLAIM_MASTER XGEN set XGEN.STATUS ='F' , XGEN.REMARKS2 ='LIABILTY_ZERO_ISSUE' where XGEN.id in
(
select x.id from CLAIM C , OBJECT_IN_CLAIM OBJ , CLAIMSTREAM CS , ASSESSMENT_AND_LIABILITY ASL , XGEN_CLAIM_MASTER x , EVENT_LOSS el where
C.id =OBJ.CLAIM_ID 
and c.id =EL.CLAIM_ID
and OBJ.id= CS.OBJECTINCLAIM_ID
and CS.id = ASL.CLAIMSTREAM_ID
and C.CLAIM_NUMBER = X.CLAIM_NO
and X.STATUS ='N' and X.XGEN_SERVICE_NAME ='doClaimSettlement'
and C.STATUS IN ('PAYMENT_IN_PROGRESS' , 'APPROVAL_IN_PROGRESS' ,'RECOMMENDATION_PENDING' ) 
and ASL.NET_LIABILITY = 0 
and EL.TYPE_OF_LOSS ='Partial'
and X.REMARKS2 not in ('NO_PROBLEM')
);


-- To remove duplicate records from claim_closure 


FOR CLOSURE_TEMP_CUR IN CLAIM_CLOSURE_CUR 
      LOOP 
            -- DBMS_OUTPUT.PUT_LINE('CLAIM_NUM'|| ' ' ||'CLAIM_NUM');
             CLOSURE_CLAIM_NUM:=CLOSURE_TEMP_CUR.CLAIM_NUMBER_CLOSER;
             DBMS_OUTPUT.PUT_LINE(CLOSURE_CLAIM_NUM);
             CLOSURE_CLOSURE_ID:=CLOSURE_TEMP_CUR.CLOSURE_ID;
             DBMS_OUTPUT.PUT_LINE(CLOSURE_CLOSURE_ID);
            -- DELETE  CLAIM_CLOSURE where CLOSURE_DECISION =0 and REJECTION_COMMENTS is null and CLOSURE_ID != CLOSURE_CLOSURE_ID and CLAIM_NUMBER = CLOSURE_CLAIM_NUM ;
             UPDATE  CLAIM_CLOSURE SET CLAIM_NUMBER = null , REJECTION_COMMENTS =CLOSURE_CLAIM_NUM  where CLOSURE_DECISION =0 and REJECTION_COMMENTS is null and CLOSURE_ID != CLOSURE_CLOSURE_ID and CLAIM_NUMBER = CLOSURE_CLAIM_NUM ;
             CLOSURE_CLAIM_NUM:=null;
             CLOSURE_CLOSURE_ID:=null;

      END LOOP;

-- end of closure

 FOR TEMP_CUR IN XGEN_CLAIM_NO_CUR 
      LOOP 
            -- DBMS_OUTPUT.PUT_LINE('CLAIM_NUM'|| ' ' ||'CLAIM_NUM');
             CLAIM_NUM:=TEMP_CUR.CLAIM_NO;
             DBMS_OUTPUT.PUT_LINE(CLAIM_NUM);
              FOR IN_TEMP_CUR IN XGEN_CLAIM_REC_CUR 
                 LOOP 
                     --DBMS_OUTPUT.PUT_LINE(IN_TEMP_CUR.STATUS);
                     TEMP_ID:=IN_TEMP_CUR.ID;
                     --DBMS_OUTPUT.PUT_LINE(TEMP_ID || ' ' ||TEMP_ID );
                     IF IN_TEMP_CUR.REMARKS2 IS  NULL THEN 
                     IN_TEMP_CUR.REMARKS2 :=' ';
                     END IF;
                      IF IN_TEMP_CUR.REMARKS2 != 'NOPROBLEM' AND IN_TEMP_CUR.STATUS !='Y' AND IN_TEMP_CUR.STATUS !='F' AND IN_TEMP_CUR.STATUS !='D' THEN
                      -- DBMS_OUTPUT.PUT_LINE(IN_TEMP_CUR.CLAIM_NO);
                      -- DBMS_OUTPUT.PUT_LINE(TEMP_ID || ' ' ||TEMP_ID );
                      UPDATE XGEN_CLAIM_MASTER SET STATUS='F' , REMARKS2 ='DUPLICATE_PROBLEM' WHERE ID = TEMP_ID;
                      COMMIT;
                      --EXIT;
                     END IF;
                 END LOOP;
                 CLAIM_NUM:=NULL;
      END LOOP;
END DUPLICATE_PAYMENTS_MANAGE_PROC;

Sql query example with inner statement




SELECT DISTINCT CLAIM_NUMBER,
  STATUS,
  LOCATION,
  BRANCH_NAME,
  POLICY_NUMBER,
  VEHICLE_REGISTRATION_NUMBER,
  SIXTYFOUR_VB_STATUS,
  INSURED_NAME,
  INSURED_CONTACT,
  FINANCIER_NAME,
  TYPE_OF_LOSS,
  INTIMATION_DATE,
  NBNR_CLAIM,
  SURVEY_ALLOTMENT_DATE,
  SURVEY_DATE,
  REPORT_DATE,
  SURVEYOR_NAME,
  SURVEYOR_CODE,
  SURVEYOR_CLASSIFICATION,
  REAPAIRER_NAME,
  REPAIRER_CODE,
  REAPAIRER_TYPE,
  REPAIRER_LOCATION,
  REPAIRER_CITY,
  REPAIRER_STATE,
  IS_LOCAL_REPAIRER,
  LAST_DOCUMENT_RECEIPT_DATE,
  BILL_NUMBER,
  BILL_DATE,
  BILL_RECEIPT_DATE,
  TOTAL_BILL_AMOUNT,
  NET_LIABILITY,
  APPROVAL_ROLE,
  APPROVAL_DONE_BY,
  APPROVAL_DATE,
  CROSS_REF_NO1,
  CROSS_REF_NO2,
  SETTLEMENT_TYPE,
  IS_CASHLESS,
  CASE
    WHEN FIRST_FINAL_PY_DATE IS NOT NULL
    AND FIRST_FINAL_PY_DATE  <>'NA'
    AND INTIMATION_DATE    IS NOT NULL
    AND INTIMATION_DATE    <>'NA'
    THEN 
    concat(EXTRACT(DAY FROM(CAST(to_date(FIRST_FINAL_PY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(INTIMATION_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days')
     ELSE 'NA'
  END AS TAT_DON_DOS,
  CASE
    WHEN LAST_DOCUMENT_RECEIPT_DATE IS NOT NULL
    AND LAST_DOCUMENT_RECEIPT_DATE  <>'NA'
    AND FIRST_FINAL_PY_DATE         IS NOT NULL
    AND FIRST_FINAL_PY_DATE         <>'NA'
    THEN 
     concat(EXTRACT(DAY FROM(CAST(to_date(FIRST_FINAL_PY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(LAST_DOCUMENT_RECEIPT_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days')   
    ELSE 'NA'
  END AS TAT_DLD_DOS,
  CASE
    WHEN FIRST_FINAL_PY_DATE IS NOT NULL
    AND FIRST_FINAL_PY_DATE  <>'NA'
    AND BILL_DATE            IS NOT NULL
    AND BILL_DATE            <>'NA'
    THEN 
    concat(EXTRACT(DAY FROM(CAST(to_date(FIRST_FINAL_PY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(BILL_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days')
    ELSE 'NA'
  END AS TAT_BILL_DOS,
  CASE
    WHEN FIRST_FINAL_PY_DATE IS NOT NULL
    AND FIRST_FINAL_PY_DATE  <>'NA'
    AND BILL_RECEIPT_DATE    IS NOT NULL
    AND BILL_RECEIPT_DATE    <>'NA'
    THEN 
      concat(EXTRACT(DAY FROM(CAST(to_date(FIRST_FINAL_PY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(BILL_RECEIPT_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days')
    ELSE 'NA'
  END AS TAT_BILL_RECEIPT_DOS,
  CASE
    WHEN BILL_RECEIPT_DATE IS NOT NULL
    AND BILL_RECEIPT_DATE  <>'NA'
    AND BILL_DATE          IS NOT NULL
    AND BILL_DATE          <>'NA'
    THEN 
     concat(EXTRACT(DAY FROM(CAST(to_date(BILL_RECEIPT_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(BILL_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days') 
    ELSE 'NA'
  END AS TAT_BILL_RECEIPT_BILL,
  CASE
    WHEN SURVEY_ALLOTMENT_DATE IS NOT NULL
    AND SURVEY_ALLOTMENT_DATE  <>'NA'
    AND SURVEY_DATE            IS NOT NULL
    AND SURVEY_DATE            <>'NA'
    THEN 
    concat(EXTRACT(DAY FROM(CAST(to_date(SURVEY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(SURVEY_ALLOTMENT_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days')  
    ELSE 'NA'
  END AS TAT_SURVEY_SURVEY_ALLOTMENT,
  CASE
    WHEN FIRST_FINAL_PY_DATE IS NOT NULL
    AND FIRST_FINAL_PY_DATE  <>'NA'
    AND SURVEY_DATE          IS NOT NULL
    AND SURVEY_DATE          <>'NA'
    THEN 
   concat(EXTRACT(DAY FROM(CAST(to_date(FIRST_FINAL_PY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP) - CAST( to_date(SURVEY_DATE,'DD-MM-YYYY HH:MI AM') AS TIMESTAMP))),' Days')    
    ELSE 'NA'
  END AS TAT_DOS_SURVEY,
  CF_DOCUMENT_POSITION,
  RC_DOCUMENT_POSITION,
  CASE
    WHEN IS_VEHICLE_PARKED='Yes'
    THEN 'NA'
    ELSE DL_DOCUMENT_POSITION
  END AS DL_DOCUMENT_POSITION,
  BILLS_DOCUMENT_POSITION
FROM
  (SELECT DISTINCT C.CLAIM_NUMBER AS CLAIM_NUMBER,
    NVL(C.STATUS,'NA')            AS STATUS,
    CASE
      WHEN el.loss_branch_loc LIKE '%Loc'
      THEN REPLACE(el.loss_branch_loc,'Loc','')
      WHEN el.loss_branch_loc LIKE '%Location'
      THEN REPLACE(el.loss_branch_loc,'Location','')
      ELSE el.loss_branch_loc
    END                      AS LOCATION,
    NVL(bm.branch_name,'NA') AS BRANCH_NAME,
    NVL(p.pol_no,'NA')       AS POLICY_NUMBER,
    CASE
      WHEN LENGTH(Validation_Details.document_value)>2
      THEN REPLACE(NVL(Validation_Details.document_value,'NA'),'~','')
      ELSE REPLACE(iv.registration_number,'~','')
    END                              AS VEHICLE_REGISTRATION_NUMBER,
    NVL(P.SIXTY_FOUR_VB_STATUS,'NA') AS SIXTYFOUR_VB_STATUS,
    CASE
      WHEN LENGTH(pip.name)>5
      THEN NVL(pip.name,'NA')
      ELSE NVL(concat(concat(pip.first_name,pip.middle_name),pip.last_name),'NA')
    END AS INSURED_NAME,
    CASE
      WHEN LENGTH(pip.mobile_1)>9
      THEN pip.mobile_1
      WHEN LENGTH(pip.mobile_2)>9
      THEN pip.mobile_2
      WHEN LENGTH(pip.telephone_1)>9
      THEN pip.telephone_1
      WHEN LENGTH(pip.telephone_2)>9
      THEN pip.telephone_2
      ELSE 'NA'
    END                                                            AS INSURED_CONTACT,
    iv.HYP_LEASE_HP_WITH                                           AS FINANCIER_NAME,
    NVL(el.type_of_loss,'NA')                                      AS TYPE_OF_LOSS,
    NVL(TO_CHAR(el.date_of_intimation,'DD-MM-YYYY HH:MI AM'),'NA') AS INTIMATION_DATE,
    NVL(TO_CHAR(c.creation_date,'DD-MM-YYYY HH:MI AM'),'NA')       AS NOTIFICATION_DATE,
    CASE
      WHEN ms.NOTIFIED_BUT_NOT_REG=0
      THEN 'NO'
      ELSE 'YES'
    END AS NBNR_CLAIM,
    CASE
      WHEN Allotment.allotment_date IS NOT NULL
      THEN NVL(TO_CHAR(Allotment.allotment_date,'DD-MM-YYYY HH:MI AM'),'NA')
      ELSE NVL(TO_CHAR(c.creation_date,'DD-MM-YYYY HH:MI AM'),'NA')
    END                                                               AS SURVEY_ALLOTMENT_DATE,
    NVL(TO_CHAR(cs.date_of_inspection,'DD-MM-YYYY HH:MI AM'),'NA')    AS SURVEY_DATE,
    NVL(TO_CHAR(cs.report_submitted_date,'DD-MM-YYYY HH:MI AM'),'NA') AS REPORT_DATE,
    NVL(cs.survey_by,'NA')                                            AS SURVEYOR_NAME,
    CASE
      WHEN surveyor.legacycode IS NOT NULL
      THEN surveyor.legacycode
      ELSE NVL(surveyor1.legacycode,'NA')
    END AS SURVEYOR_CODE,
    CASE
      WHEN cs.survey_by IS NOT NULL
      THEN sa.type
      ELSE 'NA'
    END                                                                                                                                       AS SURVEYOR_CLASSIFICATION,
    NVL(rp.pic_name,'NA')                                                                                                                     AS REAPAIRER_NAME,
    NVL(masters.legacycode, 'NA')                                                                                                             AS REPAIRER_CODE,
    NVL(RP.TYPE_OF_REPAIRER,'NA')                                                                                                             AS REAPAIRER_TYPE,
    NVL(concat(concat(concat(concat(concat(concat(rp.pic_address_1,','),rp.pic_address_2),','),rp.pic_address_3),','),rp.pic_address_4),'NA') AS REPAIRER_LOCATION,
    NVL(rp.pic_city,'NA')                                                                                                                     AS REPAIRER_CITY,
    NVL(rp.pic_state,'NA')                                                                                                                    AS REPAIRER_STATE,
    eml.is_vehicle_parked                                                                                                                     AS IS_VEHICLE_PARKED,
    CASE
      WHEN RP.IS_LOCAL_REPAIRER =0
      THEN 'NO'
      ELSE 'YES'
    END                                                                                AS IS_LOCAL_REPAIRER,
    NVL(TO_CHAR(Last_Uploaded_Document.date_of_submission,'DD-MM-YYYY HH:MI AM'),'NA') AS LAST_DOCUMENT_RECEIPT_DATE,
    NVL(Main_Assessment.Bill_No,'NA')                                                  AS BILL_NUMBER,
    NVL(TO_CHAR(Main_Assessment.Bill_Date,'DD-MM-YYYY HH:MI AM'),'NA')                 AS BILL_DATE,
    NVL(Claim_Document.date_of_submission,'NA')                                        AS BILL_RECEIPT_DATE,
    CASE
      WHEN el.type_of_loss             <>'Salvage'
      AND ms.main_assessment                     IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1           IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment2           IN('COMPLETED','CLOSED')
      THEN NVL(Main_Assessment.liability_amount,0)+NVL(Supply1_Assessment.liability_amount,0)+NVL(Supply2_Assessment.liability_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1  IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.liability_amount,0)+NVL(Supply1_Assessment.liability_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment1 IS NULL
      OR ms.supplementary_assessment1    ='INPROGRESS')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.liability_amount,0)
      WHEN el.type_of_loss    ='Salvage'
      AND ms.main_assessment IN('COMPLETED','CLOSED')
      THEN NVL(cba.net_liability,0)
      ELSE 0
    END AS NET_LIABILITY,
    CASE
      WHEN el.type_of_loss             <>'Salvage'
      AND ms.main_assessment                      IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1            IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment2            IN('COMPLETED','CLOSED')
      THEN NVL(Main_Assessment.total_bill_amount,0)+NVL(Supply1_Assessment.total_bill_amount,0)+NVL(Supply2_Assessment.total_bill_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1  IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.total_bill_amount,0)+NVL(Supply1_Assessment.total_bill_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment1 IS NULL
      OR ms.supplementary_assessment1    ='INPROGRESS')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.total_bill_amount,0)
      ELSE 0
    END AS TOTAL_BILL_AMOUNT,
    CASE
      WHEN ms.py_approval_role IS NOT NULL
      THEN ms.py_approval_role
      WHEN ms.csa_for_rcp_role IS NOT NULL
      THEN ms.csa_for_rcp_role
      ELSE 'NA'
    END AS APPROVAL_ROLE,
    CASE
      WHEN ms.py_approval_by IS NOT NULL
      THEN ms.py_approval_by
      WHEN ms.csa_for_rcp_name IS NOT NULL
      THEN ms.csa_for_rcp_name
      ELSE 'NA'
    END AS APPROVAL_DONE_BY,
    CASE
      WHEN Approval.Approval_Date IS NOT NULL
      THEN NVL(TO_CHAR(Approval.Approval_Date,'DD-MM-YYYY HH:MI AM'),'NA')
      WHEN Recommendation.Recommendation_Date IS NOT NULL
      THEN NVL(TO_CHAR(Recommendation.Recommendation_Date,'DD-MM-YYYY HH:MI AM'),'NA')
      ELSE 'NA'
    END                         AS APPROVAL_DATE,
    NVL(eml.cross_ref_no1,'NA') AS CROSS_REF_NO1,
    NVL(eml.cross_ref_no2,'NA') AS CROSS_REF_NO2,
    NVL(el.type_of_loss,'NA')   AS SETTLEMENT_TYPE,
    CASE
      WHEN rp.is_clf_available=0
      THEN 'NO'
      ELSE 'YES'
    END                                                                 AS IS_CASHLESS,
    NVL(TO_CHAR(Final_Payment.payment_date,'DD-MM-YYYY HH:MI AM'),'NA') AS FIRST_FINAL_PY_DATE,
    CASE
      WHEN cl_Document.doc_status='Yes'
      THEN 'Y'
      WHEN (cl_Document.doc_status='No'
      OR cl_Document.doc_status  IS NULL)
      THEN 'N'
    END AS CF_DOCUMENT_POSITION,
    CASE
      WHEN rc_Document.doc_status='Yes'
      THEN 'Y'
      WHEN (rc_Document.doc_status='No'
      OR rc_Document.doc_status  IS NULL)
      THEN 'N'
    END AS RC_DOCUMENT_POSITION,
    CASE
      WHEN dl_Document.doc_status='Yes'
      THEN 'Y'
      WHEN (dl_Document.doc_status='No'
      OR dl_Document.doc_status  IS NULL)
      THEN 'N'
    END AS DL_DOCUMENT_POSITION,
    CASE
      WHEN claim_document.doc_status='Yes'
      THEN 'Y'
      WHEN (claim_document.doc_status='No'
      OR claim_document.doc_status  IS NULL)
      THEN 'N'
    END AS BILLS_DOCUMENT_POSITION
  FROM claim c
  LEFT JOIN object_in_claim oic
  ON c.id = oic.claim_id
  LEFT JOIN claimstream strm
  ON strm.objectinclaim_id = oic.id
  LEFT JOIN claim_survey cs
  ON cs.claimstream_id = strm.id
  AND cs.survey_type   ='Final'
  LEFT JOIN
    (SELECT cd.claimstream_id                               AS claimstream_id ,
      TO_CHAR(cdd.date_of_submission,'DD-MM-YYYY HH:MI AM') AS date_of_submission,
      cd.doc_name                                           AS doc_name,
      cdd.doc_status                                        AS doc_status
    FROM claim_document cd,
      claim_doc_detail cdd
    WHERE cdd.document_id = cd.id
    AND cd.doc_name       ='Original Bills / Cash Bills / Credit Invoice'
    )claim_document
  ON claim_document.claimstream_id=strm.id
  LEFT JOIN
    (SELECT cd.claimstream_id                               AS claimstream_id ,
      TO_CHAR(cdd.date_of_submission,'DD-MM-YYYY HH:MI AM') AS date_of_submission,
      cd.doc_name                                           AS doc_name,
      cdd.doc_status                                        AS doc_status
    FROM claim_document cd,
      claim_doc_detail cdd
    WHERE cdd.document_id = cd.id
    AND cd.doc_name       ='Duly filled and signed Claim Form'
    )cl_document
  ON cl_document.claimstream_id=strm.id
  LEFT JOIN
    (SELECT cd.claimstream_id                               AS claimstream_id ,
      TO_CHAR(cdd.date_of_submission,'DD-MM-YYYY HH:MI AM') AS date_of_submission,
      cd.doc_name                                           AS doc_name,
      cdd.doc_status                                        AS doc_status
    FROM claim_document cd,
      claim_doc_detail cdd
    WHERE cdd.document_id = cd.id
    AND cd.doc_name       ='RC Book'
    )rc_document
  ON rc_document.claimstream_id=strm.id
  LEFT JOIN
    (SELECT cd.claimstream_id                               AS claimstream_id ,
      TO_CHAR(cdd.date_of_submission,'DD-MM-YYYY HH:MI AM') AS date_of_submission,
      cd.doc_name                                           AS doc_name,
      cdd.doc_status                                        AS doc_status
    FROM claim_document cd,
      claim_doc_detail cdd
    WHERE cdd.document_id = cd.id
    AND cd.doc_name       ='Driving Licence'
    )dl_document
  ON dl_document.claimstream_id=strm.id
  LEFT JOIN
    (SELECT cd.claimstream_id     AS claimstream_id ,
      MAX(cdd.date_of_submission) AS date_of_submission
    FROM claim_document cd,
      claim_doc_detail cdd
    WHERE cdd.document_id       = cd.id
    AND cdd.date_of_submission IS NOT NULL
    GROUP BY cd.claimstream_id
    )Last_Uploaded_Document
  ON Last_Uploaded_Document.claimstream_id=strm.id
  LEFT JOIN motor_survey mos
  ON mos.ms_id = cs.ms_id
  LEFT JOIN motor_status_flag ms
  ON ms.ms_id=strm.ms_id
  LEFT JOIN policy p
  ON p.policy_id= c.policy_id
  LEFT JOIN object_in_policy oip
  ON p.policy_id= oip.policy_policy_id
  LEFT JOIN involved_vehicle iv
  ON iv.iv_id = oip.iv_id
  LEFT JOIN party_in_policy pip
  ON pip.policy_id = p.policy_id
  LEFT JOIN event_loss el
  ON el.claim_id = oic.claim_id
  LEFT JOIN event_motor_loss eml
  ON eml.eml_id = el.eml_id
  LEFT JOIN LIVE_EPORTAL.branch_master bm
  ON bm.branch_code = el.loss_branch
  LEFT JOIN repairer rp
  ON rp.repairer_id = strm.repairer_id
  LEFT JOIN
    (SELECT claim_id,
      document_value
    FROM object_covered_validation
    WHERE NAME='RegistrationNumber'
    ) Validation_Details
  ON Validation_Details.claim_id=c.id
  LEFT JOIN
    (SELECT NVL(mal.net_liability,0)     AS liability_amount ,
      NVL(mal.total_assessment_amount,0) AS assessment_amount ,
      NVL(total_bill_amount,0)           AS total_bill_amount,
      mal.claimstream_id                 AS Stream_id,
      mal.bill_date                      AS Bill_Date,
      mal.repairer_invoice_no            AS Bill_No,
      mal.ser_tax_liab                   AS STAX,
      mal.deduction_tds                  AS TDS,
      mal.assessed_by                    AS ASSESSED_BY
    FROM assessment_and_liability mal
    WHERE mal.assessment_type='MAIN_ASSESSMENT'
    ) Main_Assessment
  ON Main_Assessment.Stream_id=strm.id
  LEFT JOIN
    (SELECT mal.claimstream_id           AS Stream_id,
      NVL(mal.total_assessment_amount,0) AS assessment_amount,
      NVL(total_bill_amount,0)           AS total_bill_amount,
      NVL(mal.net_liability,0)           AS liability_amount
    FROM assessment_and_liability mal
    WHERE mal.assessment_type='SUPPLEMENTARY_ASSESSMENT_1'
    ) Supply1_Assessment
  ON Supply1_Assessment.Stream_id=strm.id
  LEFT JOIN
    (SELECT mal.claimstream_id           AS Stream_id,
      NVL(mal.total_assessment_amount,0) AS assessment_amount,
      NVL(total_bill_amount,0)           AS total_bill_amount,
      NVL(mal.net_liability,0)           AS liability_amount
    FROM assessment_and_liability mal
    WHERE mal.assessment_type='SUPPLEMENTARY_ASSESSMENT_2'
    ) Supply2_Assessment
  ON Supply2_Assessment.Stream_id=strm.id
  LEFT JOIN cst_benf_anal cba
  ON cba.cba_id=oic.cba_id
  LEFT JOIN salvage_liability_computation sal
  ON sal.slc_id=c.slc_id
  LEFT JOIN recommendation rcm
  ON rcm.claimstream_id=strm.id
  LEFT JOIN
    (SELECT cr.claim_id     AS claim_id,
      MIN(re.modified_date) AS payment_date
    FROM claim_reserve cr,
      reserve_entry re
    WHERE re.cr_id =cr.id
    AND re.reason IN('Final Payment','Final payment')
    GROUP BY cr.claim_id
    ) Final_Payment
  ON Final_Payment.claim_id=c.id
  LEFT JOIN
    (SELECT legacycode,
      partycode
    FROM LIVE_EMASTER.party_master
    WHERE status ='ACTIVE'
    AND partytype='REPAIRER'
    ) masters
  ON masters.partycode=rp.pic_code
  LEFT JOIN survey_allotment sa
  ON sa.allot_id = strm.allot_id
  LEFT JOIN
    (SELECT u.screenname AS username,
      ev.data_           AS data_,
      pm.partycode       AS partycode,
      pm.legacycode      AS legacycode,
      pm.fullname        AS fullname,
      pm.firstname       AS firstname,
      pd.licensenumber   AS licensenumber
    FROM LIVE_EPORTAL.user_ u,
      LIVE_EPORTAL.Role_ r,
      LIVE_EPORTAL.USERS_ROLES ur,
      LIVE_EPORTAL.EXPANDOVALUE ev,
      LIVE_EPORTAL.EXPANDOCOLUMN EC ,
      LIVE_EMASTER.party_master pm,
      LIVE_EMASTER.party_detail pd
    WHERE u.userid  = ur.userid
    AND r.roleid    = ur.roleid
    AND u.userid    = ev.classpk
    AND ev.columnid = EC.COLUMNID
    AND pm.partycode=ev.data_
    AND pm.id       =pd.partymasterentity_id
    AND ec.name     = 'PartyCode'
    ) surveyor
  ON surveyor.username=cs.survey_by
  LEFT JOIN
    (SELECT u.screenname AS username,
      ev.data_           AS data_,
      pm.partycode       AS partycode,
      pm.legacycode      AS legacycode,
      pm.fullname        AS fullname,
      pm.firstname       AS firstname,
      pd.licensenumber   AS licensenumber
    FROM LIVE_EPORTAL.user_ u,
      LIVE_EPORTAL.Role_ r,
      LIVE_EPORTAL.USERS_ROLES ur,
      LIVE_EPORTAL.EXPANDOVALUE ev,
      LIVE_EPORTAL.EXPANDOCOLUMN EC ,
      LIVE_EMASTER.party_master pm,
      LIVE_EMASTER.party_detail pd
    WHERE u.userid  = ur.userid
    AND r.roleid    = ur.roleid
    AND u.userid    = ev.classpk
    AND ev.columnid = EC.COLUMNID
    AND pm.partycode=ev.data_
    AND pm.id       =pd.partymasterentity_id
    AND ec.name     = 'PartyCode'
    ) surveyor1
  ON surveyor1.firstname=cs.survey_by
  LEFT JOIN
    (SELECT mct.claim_number    AS claim_number,
      MIN(t.last_modified_date) AS Allotment_Date
    FROM task t,
      task_mtr_claim_ext mct
    WHERE t.mct_id=mct.mct_id
    AND mct.status='PENDING_SURVEY_ALLOTMENT'
    GROUP BY mct.claim_number
    ) Allotment
  ON Allotment.claim_number=c.claim_number
  LEFT JOIN
    (SELECT mct.claim_number    AS claim_number,
      MIN(t.last_modified_date) AS Recommendation_Date
    FROM task t,
      task_mtr_claim_ext mct
    WHERE t.mct_id=mct.mct_id
    AND mct.status='RECOMMENDATION_PENDING'
    GROUP BY mct.claim_number
    ) Recommendation
  ON Recommendation.claim_number=c.claim_number
  LEFT JOIN
    (SELECT mct.claim_number    AS claim_number,
      MIN(t.last_modified_date) AS Approval_Date
    FROM task t,
      task_mtr_claim_ext mct
    WHERE t.mct_id=mct.mct_id
    AND mct.status='APPROVAL_IN_PROGRESS'
    GROUP BY mct.claim_number
    ) Approval
  ON Approval.claim_number=c.claim_number
  WHERE
    --c.claim_number='PM00110846'
  TRUNC(c.creation_date) BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))
  AND c.claim_number NOT LIKE 'DUP%'
  GROUP BY C.CLAIM_NUMBER,
    NVL(C.STATUS,'NA'),
    CASE
      WHEN el.loss_branch_loc LIKE '%Loc'
      THEN REPLACE(el.loss_branch_loc,'Loc','')
      WHEN el.loss_branch_loc LIKE '%Location'
      THEN REPLACE(el.loss_branch_loc,'Location','')
      ELSE el.loss_branch_loc
    END,
    NVL(bm.branch_name,'NA'),
    NVL(p.pol_no,'NA'),
    CASE
      WHEN LENGTH(Validation_Details.document_value)>2
      THEN REPLACE(NVL(Validation_Details.document_value,'NA'),'~','')
      ELSE REPLACE(iv.registration_number,'~','')
    END,
    NVL(P.SIXTY_FOUR_VB_STATUS,'NA'),
    CASE
      WHEN LENGTH(pip.name)>5
      THEN NVL(pip.name,'NA')
      ELSE NVL(concat(concat(pip.first_name,pip.middle_name),pip.last_name),'NA')
    END,
    CASE
      WHEN LENGTH(pip.mobile_1)>9
      THEN pip.mobile_1
      WHEN LENGTH(pip.mobile_2)>9
      THEN pip.mobile_2
      WHEN LENGTH(pip.telephone_1)>9
      THEN pip.telephone_1
      WHEN LENGTH(pip.telephone_2)>9
      THEN pip.telephone_2
      ELSE 'NA'
    END,
    iv.HYP_LEASE_HP_WITH,
    NVL(el.type_of_loss,'NA'),
    NVL(TO_CHAR(el.date_of_intimation,'DD-MM-YYYY HH:MI AM'),'NA'),
    NVL(TO_CHAR(c.creation_date,'DD-MM-YYYY HH:MI AM'),'NA'),
    CASE
      WHEN ms.NOTIFIED_BUT_NOT_REG=0
      THEN 'NO'
      ELSE 'YES'
    END,
    CASE
      WHEN Allotment.allotment_date IS NOT NULL
      THEN NVL(TO_CHAR(Allotment.allotment_date,'DD-MM-YYYY HH:MI AM'),'NA')
      ELSE NVL(TO_CHAR(c.creation_date,'DD-MM-YYYY HH:MI AM'),'NA')
    END,
    NVL(TO_CHAR(cs.date_of_inspection,'DD-MM-YYYY HH:MI AM'),'NA'),
    NVL(TO_CHAR(cs.report_submitted_date,'DD-MM-YYYY HH:MI AM'),'NA'),
    NVL(cs.survey_by,'NA'),
    CASE
      WHEN surveyor.legacycode IS NOT NULL
      THEN surveyor.legacycode
      ELSE NVL(surveyor1.legacycode,'NA')
    END,
    CASE
      WHEN cs.survey_by IS NOT NULL
      THEN sa.type
      ELSE 'NA'
    END,
    NVL(rp.pic_name,'NA'),
    NVL(masters.legacycode, 'NA'),
    NVL(RP.TYPE_OF_REPAIRER,'NA'),
    NVL(concat(concat(concat(concat(concat(concat(rp.pic_address_1,','),rp.pic_address_2),','),rp.pic_address_3),','),rp.pic_address_4),'NA'),
    NVL(rp.pic_city,'NA'),
    NVL(rp.pic_state,'NA'),
    eml.is_vehicle_parked,
    CASE
      WHEN RP.IS_LOCAL_REPAIRER =0
      THEN 'NO'
      ELSE 'YES'
    END,
    NVL(TO_CHAR(Last_Uploaded_Document.date_of_submission,'DD-MM-YYYY HH:MI AM'),'NA'),
    NVL(Main_Assessment.Bill_No,'NA'),
    NVL(TO_CHAR(Main_Assessment.Bill_Date,'DD-MM-YYYY HH:MI AM'),'NA'),
    NVL(Claim_Document.date_of_submission,'NA'),
    CASE
      WHEN el.type_of_loss             <>'Salvage'
      AND ms.main_assessment                     IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1           IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment2           IN('COMPLETED','CLOSED')
      THEN NVL(Main_Assessment.liability_amount,0)+NVL(Supply1_Assessment.liability_amount,0)+NVL(Supply2_Assessment.liability_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1  IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.liability_amount,0)+NVL(Supply1_Assessment.liability_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment1 IS NULL
      OR ms.supplementary_assessment1    ='INPROGRESS')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.liability_amount,0)
      WHEN el.type_of_loss    ='Salvage'
      AND ms.main_assessment IN('COMPLETED','CLOSED')
      THEN NVL(cba.net_liability,0)
      ELSE 0
    END,
    CASE
      WHEN el.type_of_loss             <>'Salvage'
      AND ms.main_assessment                      IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1            IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment2            IN('COMPLETED','CLOSED')
      THEN NVL(Main_Assessment.total_bill_amount,0)+NVL(Supply1_Assessment.total_bill_amount,0)+NVL(Supply2_Assessment.total_bill_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND ms.supplementary_assessment1  IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.total_bill_amount,0)+NVL(Supply1_Assessment.total_bill_amount,0)
      WHEN el.type_of_loss              <>'Salvage'
      AND ms.main_assessment            IN('COMPLETED','CLOSED')
      AND (ms.supplementary_assessment1 IS NULL
      OR ms.supplementary_assessment1    ='INPROGRESS')
      AND (ms.supplementary_assessment2 IS NULL
      OR ms.supplementary_assessment2    ='INPROGRESS')
      THEN NVL(Main_Assessment.total_bill_amount,0)
      ELSE 0
    END,
    CASE
      WHEN ms.py_approval_role IS NOT NULL
      THEN ms.py_approval_role
      WHEN ms.csa_for_rcp_role IS NOT NULL
      THEN ms.csa_for_rcp_role
      ELSE 'NA'
    END,
    CASE
      WHEN ms.py_approval_by IS NOT NULL
      THEN ms.py_approval_by
      WHEN ms.csa_for_rcp_name IS NOT NULL
      THEN ms.csa_for_rcp_name
      ELSE 'NA'
    END,
    CASE
      WHEN Approval.Approval_Date IS NOT NULL
      THEN NVL(TO_CHAR(Approval.Approval_Date,'DD-MM-YYYY HH:MI AM'),'NA')
      WHEN Recommendation.Recommendation_Date IS NOT NULL
      THEN NVL(TO_CHAR(Recommendation.Recommendation_Date,'DD-MM-YYYY HH:MI AM'),'NA')
      ELSE 'NA'
    END,
    NVL(eml.cross_ref_no1,'NA'),
    NVL(eml.cross_ref_no2,'NA'),
    NVL(el.type_of_loss,'NA'),
    CASE
      WHEN rp.is_clf_available=0
      THEN 'NO'
      ELSE 'YES'
    END,
    NVL(TO_CHAR(Final_Payment.payment_date,'DD-MM-YYYY HH:MI AM'),'NA'),
    CASE
      WHEN cl_Document.doc_status='Yes'
      THEN 'Y'
      WHEN (cl_Document.doc_status='No'
      OR cl_Document.doc_status  IS NULL)
      THEN 'N'
    END,
    CASE
      WHEN rc_Document.doc_status='Yes'
      THEN 'Y'
      WHEN (rc_Document.doc_status='No'
      OR rc_Document.doc_status  IS NULL)
      THEN 'N'
    END,
    CASE
      WHEN dl_Document.doc_status='Yes'
      THEN 'Y'
      WHEN (dl_Document.doc_status='No'
      OR dl_Document.doc_status  IS NULL)
      THEN 'N'
    END,
    CASE
      WHEN claim_document.doc_status='Yes'
      THEN 'Y'
      WHEN (claim_document.doc_status='No'
      OR claim_document.doc_status  IS NULL)
      THEN 'N'
    END
  )
ORDER BY CLAIM_NUMBER;




Related Articles
Recent Articles
Most Viewed Articles