CJM_PROGRAM_CLAIMS_VL
Details
-
Schema: FUSION
-
Object owner: CJM
-
Object type: VIEW
Columns
Name |
---|
PROGRAM_HEADER_ID CLAIM_ID CLAIM_STATUS_CODE USER_STATUS_NAME CLAIM_DATE CLAIM_NUMBER CLAIM_CURRENCY_CODE CLAIM_AMOUNT IN_PROGRESS PAID SETTLEMENT_DATE SETTLEMENT_TYPE SETTLEMENT_NUMBER INSTRUMENT_NUMBER INSTRUMENT_TYPE INSTRUMENT_DATE INSTRUMENT_AMOUNT INSTRUMENT_COUNT CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY |
Query
SQL_Statement |
---|
SELECT futb.program_header_id, cca.claim_id, cca.status_code claim_status_code, userstatus.user_status_name, cca.claim_date, cca.claim_number, cca.currency_code claim_currency_code, futb.in_progress + futb.paid claim_amount, futb.in_progress in_progress, futb.paid paid, claim_details.settlement_date settlement_date, claim_details.settlement_type settlement_type, claim_details.settlement_number settlement_number, claim_details.instrument_number instrument_number, claim_details.instrument_type instrument_type, claim_details.instrument_date instrument_date, claim_details.instrument_amount instrument_amount, ( SELECT COUNT(1) FROM cjm_settlement_docs_all cs WHERE cs.claim_id = cca.claim_id AND cs.settlement_doc_type_code = 'INSTRUMENT' ) instrument_count, cca.creation_date, cca.created_by, cca.last_update_date, cca.last_updated_by FROM cjm_claims_all cca, cjm_user_statuses_vl userstatus, ( SELECT util.program_header_id, ccla.claim_id, CASE WHEN cca2.status_code <> 'SETTLED' THEN SUM(cclu.amount) ELSE 0 END in_progress, CASE WHEN cca2.status_code = 'SETTLED' THEN SUM(cclu.amount) ELSE 0 END paid FROM cjm_claim_lines_util_all cclu, cjm_programs_utilized_all_b util, cjm_claim_lines_all ccla, cjm_claims_all cca2 WHERE cclu.utilization_id = util.utilization_id AND ccla.claim_line_id = cclu.claim_line_id AND cca2.claim_id = ccla.claim_id AND util.utilization_type IN ( 'ACCRUAL','ADJUSTMENT' ) AND util.gl_posted_flag = 'Y' AND util.accrual_type = 'SUPPLIER' GROUP BY util.program_header_id, ccla.claim_id, cca2.status_code ) futb, ( SELECT nvl( csda_transaction.claim_id, csda_instrument.claim_id ) claim_id, csda_transaction.settlement_date, csda_transaction.settlement_type, csda_transaction.settlement_number, csda_instrument.instrument_number, csda_instrument.instrument_type, csda_instrument.instrument_date, csda_instrument.instrument_amount FROM ( SELECT cca.claim_id, csda_trans.settlement_date settlement_date, csda_trans.settlement_type settlement_type, csda_trans.settlement_number settlement_number FROM cjm_claims_all cca, cjm_settlement_docs_all csda_trans, ( SELECT csda_in_trans.claim_id, MIN(csda_in_trans.creation_date) creation_date FROM cjm_settlement_docs_all csda_in_trans WHERE csda_in_trans.settlement_doc_type_code = 'TRANSACTION' AND nvl( csda_in_trans.purge_flag, 'N' ) = 'N' AND ( CASE WHEN csda_in_trans.payment_method_code <> 'CHECK' THEN 1 WHEN csda_in_trans.payment_method_code = 'CHECK' AND csda_in_trans.status_code IN ( 'ISSUED','NEGOTIABLE','STOP INITIATED','CLEARED','RECONCILED' ) THEN 1 WHEN csda_in_trans.payment_method_code IS NULL THEN 1 ELSE 0 END ) = 1 GROUP BY csda_in_trans.claim_id ) csda_in_transaction WHERE cca.claim_id = csda_trans.claim_id AND csda_trans.settlement_doc_type_code = 'TRANSACTION' AND csda_trans.claim_id = csda_in_transaction.claim_id AND csda_trans.creation_date = csda_in_transaction.creation_date ) csda_transaction FULL OUTER JOIN ( SELECT cca.claim_id, csda_instr.settlement_number instrument_number, csda_instr.settlement_type instrument_type, csda_instr.settlement_date instrument_date, csda_instr.settlement_amount instrument_amount FROM cjm_claims_all cca, cjm_settlement_docs_all csda_instr, ( SELECT csda_in_instr.claim_id, MIN(csda_in_instr.creation_date) creation_date FROM cjm_settlement_docs_all csda_in_instr WHERE csda_in_instr.settlement_doc_type_code = 'INSTRUMENT' AND nvl( csda_in_instr.purge_flag, 'N' ) = 'N' AND ( CASE WHEN csda_in_instr.payment_method_code <> 'CHECK' THEN 1 WHEN csda_in_instr.payment_method_code = 'CHECK' AND csda_in_instr.status_code IN ( 'ISSUED','NEGOTIABLE','STOP INITIATED','CLEARED','RECONCILED' ) THEN 1 WHEN csda_in_instr.payment_method_code IS NULL THEN 1 ELSE 0 END ) = 1 GROUP BY csda_in_instr.claim_id ) csda_in_instrument WHERE cca.claim_id = csda_instr.claim_id AND csda_instr.settlement_doc_type_code = 'INSTRUMENT' AND csda_instr.claim_id = csda_in_instrument.claim_id AND csda_instr.creation_date = csda_in_instrument.creation_date ) csda_instrument ON csda_instrument.claim_id = csda_transaction.claim_id ) claim_details WHERE cca.claim_id = futb.claim_id AND cca.claim_id = claim_details.claim_id (+) AND cca.user_status_id = userstatus.user_status_id |