CJM_XLA_CUST_CLAIM_ACCRUALS

Details

Schema: FUSION
Object owner: CJM
Object type: VIEW

Columns

Name
EVENT_ID LINE_NUMBER CUST_CLAIM_DISTRIB_TYPE CUST_CLAIM_DISTRIB_IDENTIFIER CUST_CLAIM_ASO_ACC_ACCTD_AMT CUST_CLAIM_ASO_ACRL_AMT CUST_CLAIM_ACCRUAL_ID CUST_CLAIM_ACCOUNTED_DATE CUST_CLAIM_ACRL_AMT CUST_CLAIM_ACRL_ACCTD_AMT CUST_CLAIM_ACRL_CURR_AMT CUST_CLAIM_ACRL_LEDGER_AMT CUST_CLA_GAIN_LOSS_LEDGER_AMT CUST_CLAIM_GAIN_LOSS_AMOUNT CUST_CLA_ASO_PGM_TEMPLATE_ID CUST_CLA_ASO_PRG_TYPE_ID CUST_CLA_ASO_PROGRAM_CODE CUST_CLA_ASO_PRG_TEMP_NAME CUST_CLA_ASO_PROGRAM_LINE_ID CUST_CLA_ASO_ACCRUAL_TYPE CUST_CLA_ASO_OBJECT_TYPE CUST_CLA_ASO_OBJECT_ID CUST_CLA_ASO_OBJECT_LINE_ID CUST_CLA_ASO_ITEM_ID CUST_CLA_ASO_ITEM_LEVEL_CODE CUST_CLA_ASO_ITM_VAL_ORG_ID CUST_CLA_ASO_ITEM CUST_CLA_ASO_EXG_RATE_DATE CUST_CLA_ASO_EXG_RATE_TYPE CUST_CLA_ASO_EXG_RATE CUST_CLA_ASO_INVOICE_DATE CUST_CLA_ASO_INVOICE_NUMBER CUST_CLA_ASO_ADJ_TYPE_ID CUST_CLA_ASO_PGM_HDR_ATR_CATG CUST_CLA_ASO_PGM_HDR_ATTR_1 CUST_CLA_ASO_PGM_HDR_ATTR_2 CUST_CLA_ASO_PGM_HDR_ATTR_3 CUST_CLA_ASO_PGM_HDR_ATTR_4 CUST_CLA_ASO_PGM_HDR_ATTR_5 CUST_CLA_ASO_PGM_HDR_ATTR_6 CUST_CLA_ASO_PGM_HDR_ATTR_7 CUST_CLA_ASO_PGM_HDR_ATTR_8 CUST_CLA_ASO_PGM_HDR_ATTR_9 CUST_CLA_ASO_PGM_HDR_ATTR_10 CUST_CLA_ASO_PGM_HDR_ATTR_11 CUST_CLA_ASO_PGM_HDR_ATTR_12 CUST_CLA_ASO_PGM_HDR_ATTR_13 CUST_CLA_ASO_PGM_HDR_ATTR_14 CUST_CLA_ASO_PGM_HDR_ATTR_15 CUST_CLA_ASO_PGM_HDR_ATTR_16 CUST_CLA_ASO_PGM_HDR_ATTR_17 CUST_CLA_ASO_PGM_HDR_ATTR_18 CUST_CLA_ASO_PGM_HDR_ATTR_19 CUST_CLA_ASO_PGM_HDR_ATTR_20 CUST_CLA_ASO_PG_HD_ATR_NUM_1 CUST_CLA_ASO_PG_HD_ATR_NUM_2 CUST_CLA_ASO_PG_HD_ATR_NUM_3 CUST_CLA_ASO_PG_HD_ATR_NUM_4 CUST_CLA_ASO_PG_HD_ATR_NUM_5 CUST_CLA_ASO_PG_HD_ATR_NUM_6 CUST_CLA_ASO_PG_HD_ATR_NUM_7 CUST_CLA_ASO_PG_HD_ATR_NUM_8 CUST_CLA_ASO_PG_HD_ATR_NUM_9 CUST_CLA_ASO_PG_HD_ATR_NUM_10 CUST_CLA_ASO_PG_HD_ATR_DT_1 CUST_CLA_ASO_PG_HD_ATR_DT_2 CUST_CLA_ASO_PG_HD_ATR_DT_3 CUST_CLA_ASO_PG_HD_ATR_DT_4 CUST_CLA_ASO_PG_HD_ATR_DT_5

Name

EVENT_ID

LINE_NUMBER

CUST_CLAIM_DISTRIB_TYPE

CUST_CLAIM_DISTRIB_IDENTIFIER

CUST_CLAIM_ASO_ACC_ACCTD_AMT

CUST_CLAIM_ASO_ACRL_AMT

CUST_CLAIM_ACCRUAL_ID

CUST_CLAIM_ACCOUNTED_DATE

CUST_CLAIM_ACRL_AMT

CUST_CLAIM_ACRL_ACCTD_AMT

CUST_CLAIM_ACRL_CURR_AMT

CUST_CLAIM_ACRL_LEDGER_AMT

CUST_CLA_GAIN_LOSS_LEDGER_AMT

CUST_CLAIM_GAIN_LOSS_AMOUNT

CUST_CLA_ASO_PGM_TEMPLATE_ID

CUST_CLA_ASO_PRG_TYPE_ID

CUST_CLA_ASO_PROGRAM_CODE

CUST_CLA_ASO_PRG_TEMP_NAME

CUST_CLA_ASO_PROGRAM_LINE_ID

CUST_CLA_ASO_ACCRUAL_TYPE

CUST_CLA_ASO_OBJECT_TYPE

CUST_CLA_ASO_OBJECT_ID

CUST_CLA_ASO_OBJECT_LINE_ID

CUST_CLA_ASO_ITEM_ID

CUST_CLA_ASO_ITEM_LEVEL_CODE

CUST_CLA_ASO_ITM_VAL_ORG_ID

CUST_CLA_ASO_ITEM

CUST_CLA_ASO_EXG_RATE_DATE

CUST_CLA_ASO_EXG_RATE_TYPE

CUST_CLA_ASO_EXG_RATE

CUST_CLA_ASO_INVOICE_DATE

CUST_CLA_ASO_INVOICE_NUMBER

CUST_CLA_ASO_ADJ_TYPE_ID

CUST_CLA_ASO_PGM_HDR_ATR_CATG

CUST_CLA_ASO_PGM_HDR_ATTR_1

CUST_CLA_ASO_PGM_HDR_ATTR_2

CUST_CLA_ASO_PGM_HDR_ATTR_3

CUST_CLA_ASO_PGM_HDR_ATTR_4

CUST_CLA_ASO_PGM_HDR_ATTR_5

CUST_CLA_ASO_PGM_HDR_ATTR_6

CUST_CLA_ASO_PGM_HDR_ATTR_7

CUST_CLA_ASO_PGM_HDR_ATTR_8

CUST_CLA_ASO_PGM_HDR_ATTR_9

CUST_CLA_ASO_PGM_HDR_ATTR_10

CUST_CLA_ASO_PGM_HDR_ATTR_11

CUST_CLA_ASO_PGM_HDR_ATTR_12

CUST_CLA_ASO_PGM_HDR_ATTR_13

CUST_CLA_ASO_PGM_HDR_ATTR_14

CUST_CLA_ASO_PGM_HDR_ATTR_15

CUST_CLA_ASO_PGM_HDR_ATTR_16

CUST_CLA_ASO_PGM_HDR_ATTR_17

CUST_CLA_ASO_PGM_HDR_ATTR_18

CUST_CLA_ASO_PGM_HDR_ATTR_19

CUST_CLA_ASO_PGM_HDR_ATTR_20

CUST_CLA_ASO_PG_HD_ATR_NUM_1

CUST_CLA_ASO_PG_HD_ATR_NUM_2

CUST_CLA_ASO_PG_HD_ATR_NUM_3

CUST_CLA_ASO_PG_HD_ATR_NUM_4

CUST_CLA_ASO_PG_HD_ATR_NUM_5

CUST_CLA_ASO_PG_HD_ATR_NUM_6

CUST_CLA_ASO_PG_HD_ATR_NUM_7

CUST_CLA_ASO_PG_HD_ATR_NUM_8

CUST_CLA_ASO_PG_HD_ATR_NUM_9

CUST_CLA_ASO_PG_HD_ATR_NUM_10

CUST_CLA_ASO_PG_HD_ATR_DT_1

CUST_CLA_ASO_PG_HD_ATR_DT_2

CUST_CLA_ASO_PG_HD_ATR_DT_3

CUST_CLA_ASO_PG_HD_ATR_DT_4

CUST_CLA_ASO_PG_HD_ATR_DT_5

Query

SQL_Statement
SELECT /+ leading(XEG) use_nl(XEG CXH)/ xeg.event_id event_id, cxl.line_number line_number, 'CUSTOMER_CLAIM_SETTLEMENT' cust_claim_distrib_type, claccrual.claim_line_accrual_id cust_claim_distrib_identifier, classo.association_acctd_amount cust_claim_aso_acc_acctd_amt, claccrual.amount cust_claim_aso_acrl_amt, accr.customer_accrual_id cust_claim_accrual_id, cla.gl_date cust_claim_accounted_date, classo.association_trx_amount cust_claim_acrl_amt, claccrual.acctd_amount cust_claim_acrl_acctd_amt, claccrual.ACCRUAL_CURR_AMOUNT cust_claim_acrl_curr_amt, claccrual.accrual_acctd_amount cust_claim_acrl_ledger_amt, claccrual.FXGL_ACCTD_AMOUNT cust_cla_gain_loss_ledger_amt, 0 cust_claim_gain_loss_amount, ptype_b.PARENT_PROGRAM_TYPE_ID cust_cla_aso_pgm_template_id, ptype_b.program_type_id cust_cla_aso_prg_type_id, pgm_b.program_code cust_cla_aso_program_code, cjm_utility_pkg.get_lookup_meaning('ORA_CJM_PROGRAM_TYPE', pgm_b.program_type_code) cust_cla_aso_prg_temp_name, accr.program_line_id cust_cla_aso_program_line_id, accr.utilization_type cust_cla_aso_accrual_type, accr.object_type cust_cla_aso_object_type, accr.object_id cust_cla_aso_object_id, accr.object_line_id cust_cla_aso_object_line_id, accr.item_id cust_cla_aso_item_id, accr.item_level_code cust_cla_aso_item_level_code, accr.Inventory_Organization_Id cust_cla_aso_itm_val_org_id, cjm_utility_pkg.get_item_name(accr.item_level_code, accr.item_id, accr.bu_id) cust_cla_aso_item, accr.exchange_rate_date cust_cla_aso_exg_rate_date, accr.exchange_rate_type cust_cla_aso_exg_rate_type, accr.exchange_rate cust_cla_aso_exg_rate, accr.invoice_date cust_cla_aso_invoice_date, accr.invoice_number cust_cla_aso_invoice_number, accr.adjustment_type_id cust_cla_aso_adj_type_id, pgm_b.attribute_category cust_cla_aso_pgm_hdr_atr_catg, pgm_b.attribute_char1 cust_cla_aso_pgm_hdr_attr_1, pgm_b.attribute_char2 cust_cla_aso_pgm_hdr_attr_2, pgm_b.attribute_char3 cust_cla_aso_pgm_hdr_attr_3, pgm_b.attribute_char4 cust_cla_aso_pgm_hdr_attr_4, pgm_b.attribute_char5 cust_cla_aso_pgm_hdr_attr_5, pgm_b.attribute_char6 cust_cla_aso_pgm_hdr_attr_6, pgm_b.attribute_char7 cust_cla_aso_pgm_hdr_attr_7, pgm_b.attribute_char8 cust_cla_aso_pgm_hdr_attr_8, pgm_b.attribute_char9 cust_cla_aso_pgm_hdr_attr_9, pgm_b.attribute_char10 cust_cla_aso_pgm_hdr_attr_10, pgm_b.attribute_char11 cust_cla_aso_pgm_hdr_attr_11, pgm_b.attribute_char12 cust_cla_aso_pgm_hdr_attr_12, pgm_b.attribute_char13 cust_cla_aso_pgm_hdr_attr_13, pgm_b.attribute_char14 cust_cla_aso_pgm_hdr_attr_14, pgm_b.attribute_char15 cust_cla_aso_pgm_hdr_attr_15, pgm_b.attribute_char16 cust_cla_aso_pgm_hdr_attr_16, pgm_b.attribute_char17 cust_cla_aso_pgm_hdr_attr_17, pgm_b.attribute_char18 cust_cla_aso_pgm_hdr_attr_18, pgm_b.attribute_char19 cust_cla_aso_pgm_hdr_attr_19, pgm_b.attribute_char20 cust_cla_aso_pgm_hdr_attr_20, pgm_b.attribute_number1 cust_cla_aso_pg_hd_atr_num_1, pgm_b.attribute_number2 cust_cla_aso_pg_hd_atr_num_2, pgm_b.attribute_number3 cust_cla_aso_pg_hd_atr_num_3, pgm_b.attribute_number4 cust_cla_aso_pg_hd_atr_num_4, pgm_b.attribute_number5 cust_cla_aso_pg_hd_atr_num_5, pgm_b.attribute_number6 cust_cla_aso_pg_hd_atr_num_6, pgm_b.attribute_number7 cust_cla_aso_pg_hd_atr_num_7, pgm_b.attribute_number8 cust_cla_aso_pg_hd_atr_num_8, pgm_b.attribute_number9 cust_cla_aso_pg_hd_atr_num_9, pgm_b.attribute_number10 cust_cla_aso_pg_hd_atr_num_10, pgm_b.attribute_date1 cust_cla_aso_pg_hd_atr_dt_1, pgm_b.attribute_date2 cust_cla_aso_pg_hd_atr_dt_2, pgm_b.attribute_date3 cust_cla_aso_pg_hd_atr_dt_3, pgm_b.attribute_date4 cust_cla_aso_pg_hd_atr_dt_4, pgm_b.attribute_date5 cust_cla_aso_pg_hd_atr_dt_5 FROM cjm_claims_all cla, cjm_claim_associations_all classo, cjm_claim_lines_accrual_all claccrual, cjm_customer_accruals_all accr, cjm_xla_customer_claim_lines cxl, cjm_xla_customer_claim_headers cxh, cjm_programs_all_b pgm_b, cjm_program_types_b ptype_b, xla_events_gt xeg WHERE xeg.entity_code IN ('CUSTOMER_CLAIM_SETTLEMENT','CUST_CLAIM_SETLMNT_EXT_AP','CUST_CLAIM_SETLMNT_EXT_AR') AND cla.claim_for_code = 'CUSTOMER' AND xeg.application_id = 10566 AND xeg.source_id_int_1 = cxh.claim_id AND cxh.event_type_code = xeg.event_type_code AND xeg.event_id = cxh.event_id AND cxh.claim_id = cla.claim_id AND cla.claim_id = classo.claim_id AND classo.association_id = claccrual.association_id AND claccrual.customer_accrual_id = accr.customer_accrual_id AND cxh.xla_customer_claim_header_id = cxl.xla_customer_claim_header_id AND cxl.claim_line_accrual_id = claccrual.claim_line_accrual_id AND accr.program_header_id = pgm_b.program_header_id AND pgm_b.program_type_id = ptype_b.program_type_id

SQL_Statement

SELECT /*+ leading(XEG) use_nl(XEG CXH)*/

xeg.event_id event_id,

cxl.line_number line_number,

'CUSTOMER_CLAIM_SETTLEMENT' cust_claim_distrib_type,

claccrual.claim_line_accrual_id cust_claim_distrib_identifier,

classo.association_acctd_amount cust_claim_aso_acc_acctd_amt,

claccrual.amount cust_claim_aso_acrl_amt,

accr.customer_accrual_id cust_claim_accrual_id,

cla.gl_date cust_claim_accounted_date,

classo.association_trx_amount cust_claim_acrl_amt,

claccrual.acctd_amount cust_claim_acrl_acctd_amt,

claccrual.ACCRUAL_CURR_AMOUNT cust_claim_acrl_curr_amt,

claccrual.accrual_acctd_amount cust_claim_acrl_ledger_amt,

claccrual.FXGL_ACCTD_AMOUNT cust_cla_gain_loss_ledger_amt,

0 cust_claim_gain_loss_amount,

ptype_b.PARENT_PROGRAM_TYPE_ID cust_cla_aso_pgm_template_id,

ptype_b.program_type_id cust_cla_aso_prg_type_id,

pgm_b.program_code cust_cla_aso_program_code,

cjm_utility_pkg.get_lookup_meaning('ORA_CJM_PROGRAM_TYPE', pgm_b.program_type_code) cust_cla_aso_prg_temp_name,

accr.program_line_id cust_cla_aso_program_line_id,

accr.utilization_type cust_cla_aso_accrual_type,

accr.object_type cust_cla_aso_object_type,

accr.object_id cust_cla_aso_object_id,

accr.object_line_id cust_cla_aso_object_line_id,

accr.item_id cust_cla_aso_item_id,

accr.item_level_code cust_cla_aso_item_level_code,

accr.Inventory_Organization_Id cust_cla_aso_itm_val_org_id,

cjm_utility_pkg.get_item_name(accr.item_level_code, accr.item_id, accr.bu_id) cust_cla_aso_item,

accr.exchange_rate_date cust_cla_aso_exg_rate_date,

accr.exchange_rate_type cust_cla_aso_exg_rate_type,

accr.exchange_rate cust_cla_aso_exg_rate,

accr.invoice_date cust_cla_aso_invoice_date,

accr.invoice_number cust_cla_aso_invoice_number,

accr.adjustment_type_id cust_cla_aso_adj_type_id,

pgm_b.attribute_category cust_cla_aso_pgm_hdr_atr_catg,

pgm_b.attribute_char1 cust_cla_aso_pgm_hdr_attr_1,

pgm_b.attribute_char2 cust_cla_aso_pgm_hdr_attr_2,

pgm_b.attribute_char3 cust_cla_aso_pgm_hdr_attr_3,

pgm_b.attribute_char4 cust_cla_aso_pgm_hdr_attr_4,

pgm_b.attribute_char5 cust_cla_aso_pgm_hdr_attr_5,

pgm_b.attribute_char6 cust_cla_aso_pgm_hdr_attr_6,

pgm_b.attribute_char7 cust_cla_aso_pgm_hdr_attr_7,

pgm_b.attribute_char8 cust_cla_aso_pgm_hdr_attr_8,

pgm_b.attribute_char9 cust_cla_aso_pgm_hdr_attr_9,

pgm_b.attribute_char10 cust_cla_aso_pgm_hdr_attr_10,

pgm_b.attribute_char11 cust_cla_aso_pgm_hdr_attr_11,

pgm_b.attribute_char12 cust_cla_aso_pgm_hdr_attr_12,

pgm_b.attribute_char13 cust_cla_aso_pgm_hdr_attr_13,

pgm_b.attribute_char14 cust_cla_aso_pgm_hdr_attr_14,

pgm_b.attribute_char15 cust_cla_aso_pgm_hdr_attr_15,

pgm_b.attribute_char16 cust_cla_aso_pgm_hdr_attr_16,

pgm_b.attribute_char17 cust_cla_aso_pgm_hdr_attr_17,

pgm_b.attribute_char18 cust_cla_aso_pgm_hdr_attr_18,

pgm_b.attribute_char19 cust_cla_aso_pgm_hdr_attr_19,

pgm_b.attribute_char20 cust_cla_aso_pgm_hdr_attr_20,

pgm_b.attribute_number1 cust_cla_aso_pg_hd_atr_num_1,

pgm_b.attribute_number2 cust_cla_aso_pg_hd_atr_num_2,

pgm_b.attribute_number3 cust_cla_aso_pg_hd_atr_num_3,

pgm_b.attribute_number4 cust_cla_aso_pg_hd_atr_num_4,

pgm_b.attribute_number5 cust_cla_aso_pg_hd_atr_num_5,

pgm_b.attribute_number6 cust_cla_aso_pg_hd_atr_num_6,

pgm_b.attribute_number7 cust_cla_aso_pg_hd_atr_num_7,

pgm_b.attribute_number8 cust_cla_aso_pg_hd_atr_num_8,

pgm_b.attribute_number9 cust_cla_aso_pg_hd_atr_num_9,

pgm_b.attribute_number10 cust_cla_aso_pg_hd_atr_num_10,

pgm_b.attribute_date1 cust_cla_aso_pg_hd_atr_dt_1,

pgm_b.attribute_date2 cust_cla_aso_pg_hd_atr_dt_2,

pgm_b.attribute_date3 cust_cla_aso_pg_hd_atr_dt_3,

pgm_b.attribute_date4 cust_cla_aso_pg_hd_atr_dt_4,

pgm_b.attribute_date5 cust_cla_aso_pg_hd_atr_dt_5

FROM

cjm_claims_all cla,

cjm_claim_associations_all classo,

cjm_claim_lines_accrual_all claccrual,

cjm_customer_accruals_all accr,

cjm_xla_customer_claim_lines cxl,

cjm_xla_customer_claim_headers cxh,

cjm_programs_all_b pgm_b,

cjm_program_types_b ptype_b,

xla_events_gt xeg

WHERE

xeg.entity_code IN ('CUSTOMER_CLAIM_SETTLEMENT','CUST_CLAIM_SETLMNT_EXT_AP','CUST_CLAIM_SETLMNT_EXT_AR')

AND cla.claim_for_code = 'CUSTOMER'

AND xeg.application_id = 10566

AND xeg.source_id_int_1 = cxh.claim_id

AND cxh.event_type_code = xeg.event_type_code

AND xeg.event_id = cxh.event_id

AND cxh.claim_id = cla.claim_id

AND cla.claim_id = classo.claim_id

AND classo.association_id = claccrual.association_id

AND claccrual.customer_accrual_id = accr.customer_accrual_id

AND cxh.xla_customer_claim_header_id = cxl.xla_customer_claim_header_id

AND cxl.claim_line_accrual_id = claccrual.claim_line_accrual_id

AND accr.program_header_id = pgm_b.program_header_id

AND pgm_b.program_type_id = ptype_b.program_type_id