CJM_PROGRAM_CLAIMS

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

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

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