1. Tables and Views for SCM
  2. CJM_XLA_CUST_CLAIM_HEADER_V

CJM_XLA_CUST_CLAIM_HEADER_V

Details

  • Schema: FUSION

  • Object owner: CJM

  • Object type: VIEW

Columns

Name

EVENT_ID

CUST_THIRD_PARTY_TYPE

CUST_CLAIM_ID

CUST_CLAIM_NUMBER

CUST_CLAIM_CLASS_CODE

CUST_CLAIM_SOURCE_CODE

CUST_CLAIM_TYPE_ID

CUST_CLAIM_REASON_CODE_ID

CUST_CLAIM_GL_DATE

CUST_CLA_BILL_TO_CUSTOMER_ID

CUST_CLA_BILL_TO_SITE_USE_ID

CUST_CLAIM_CURRENCY_CODE

CUST_CLAIM_AMOUNT

CUST_CLAIM_LEDGER_AMOUNT

CUST_CLAIM_ADJ_AMOUNT

CUST_CLAIM_EXCHANGE_RATE_TYPE

CUST_CLAIM_EXCHANGE_RATE_DATE

CUST_CLAIM_EXCHANGE_RATE

CUST_CLAIM_DATE

CUST_CLAIM_BU_ID

CUST_CLAIM_EVENT_DATE

CUST_CLAIM_SETTLED_AMOUNT

CUST_CLAIM_PAYMENT_METHOD

CUST_CLAIM_PAYMENT_REF_NUMBER

CUST_CLAIM_PAYMENT_REF_DATE

CUST_CLAIM_PAYMENT_STATUS

CUST_CLAIM_SUPPLIER_ID

CUST_CLAIM_SUPPLIER_SITE_ID

CUST_CLAIM_SOURCE_OBJ_CLASS

CUST_CLAIM_SOURCE_OBJ_ID

CUST_CLAIM_SOURCE_OBJ_DATE

CUST_CLAIM_SOURCE_OBJ_NUM

CUST_CLAIM_SOURCE_OBJ_TYPE_ID

CUST_CLAIM_TRXN_REV_FLAG

CUST_CLM_OVERIDE_ACNT_AMT_FLAG

CUST_CLM_ATTRIBUTE_CATEGORY

CUST_CLM_ATTRIBUTE_CHAR1

CUST_CLM_ATTRIBUTE_CHAR2

CUST_CLM_ATTRIBUTE_CHAR3

CUST_CLM_ATTRIBUTE_CHAR4

CUST_CLM_ATTRIBUTE_CHAR5

CUST_CLM_ATTRIBUTE_CHAR6

CUST_CLM_ATTRIBUTE_CHAR7

CUST_CLM_ATTRIBUTE_CHAR8

CUST_CLM_ATTRIBUTE_CHAR9

CUST_CLM_ATTRIBUTE_CHAR10

CUST_CLM_ATTRIBUTE_CHAR11

CUST_CLM_ATTRIBUTE_CHAR12

CUST_CLM_ATTRIBUTE_CHAR13

CUST_CLM_ATTRIBUTE_CHAR14

CUST_CLM_ATTRIBUTE_CHAR15

CUST_CLM_ATTRIBUTE_CHAR16

CUST_CLM_ATTRIBUTE_CHAR17

CUST_CLM_ATTRIBUTE_CHAR18

CUST_CLM_ATTRIBUTE_CHAR19

CUST_CLM_ATTRIBUTE_CHAR20

CUST_CLM_ATTRIBUTE_NUMBER1

CUST_CLM_ATTRIBUTE_NUMBER2

CUST_CLM_ATTRIBUTE_NUMBER3

CUST_CLM_ATTRIBUTE_NUMBER4

CUST_CLM_ATTRIBUTE_NUMBER5

CUST_CLM_ATTRIBUTE_NUMBER6

CUST_CLM_ATTRIBUTE_NUMBER7

CUST_CLM_ATTRIBUTE_NUMBER8

CUST_CLM_ATTRIBUTE_NUMBER9

CUST_CLM_ATTRIBUTE_NUMBER10

CUST_CLM_ATTRIBUTE_DATE1

CUST_CLM_ATTRIBUTE_DATE2

CUST_CLM_ATTRIBUTE_DATE3

CUST_CLM_ATTRIBUTE_DATE4

CUST_CLM_ATTRIBUTE_DATE5

Query

SQL_Statement

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

xeg.event_id event_id,

'C' cust_third_party_type,

cla.claim_id cust_claim_id,

cla.claim_number cust_claim_number,

cla.claim_class_code cust_claim_class_code,

map.claim_source_code cust_claim_source_code,

cla.claim_type_id cust_claim_type_id,

cla.reason_code_id cust_claim_reason_code_id,

NVL(cla.gl_date,xeg.event_date) cust_claim_gl_date,

cla.bill_to_customer_id cust_cla_bill_to_customer_id,

cla.bill_to_site_use_id cust_cla_bill_to_site_use_id,

cla.currency_code cust_claim_currency_code,

cla.amount cust_claim_amount,

cla.acctd_amount cust_claim_ledger_amount,

cla.amount_adjusted cust_claim_adj_amount,

cla.exchange_rate_type cust_claim_exchange_rate_type,

cla.exchange_rate_date cust_claim_exchange_rate_date,

cla.exchange_rate cust_claim_exchange_rate,

cla.claim_date cust_claim_date,

cla.bu_id cust_claim_bu_id,

xeg.event_date cust_claim_event_date,

cla.amount_settled cust_claim_settled_amount,

ccp.payment_method_code cust_claim_payment_method,

ccp.payment_reference_number cust_claim_payment_ref_number,

ccp.payment_reference_date cust_claim_payment_ref_date,

ccp.payment_status_code cust_claim_payment_status,

ccp.vendor_id cust_claim_supplier_id,

ccp.vendor_site_id cust_claim_supplier_site_id,

cla.source_object_class cust_claim_source_obj_class,

cla.source_object_id cust_claim_source_obj_id,

cla.source_object_date cust_claim_source_obj_date,

cla.source_object_number cust_claim_source_obj_num,

cla.source_object_type_id cust_claim_source_obj_type_id,

cxh.reversal_flag cust_claim_trxn_rev_flag,

'N' cust_clm_overide_acnt_amt_flag,

cla.attribute_category cust_clm_attribute_category,

cla.attribute_char1 cust_clm_attribute_char1,

cla.attribute_char2 cust_clm_attribute_char2,

cla.attribute_char3 cust_clm_attribute_char3,

cla.attribute_char4 cust_clm_attribute_char4,

cla.attribute_char5 cust_clm_attribute_char5,

cla.attribute_char6 cust_clm_attribute_char6,

cla.attribute_char7 cust_clm_attribute_char7,

cla.attribute_char8 cust_clm_attribute_char8,

cla.attribute_char9 cust_clm_attribute_char9,

cla.attribute_char10 cust_clm_attribute_char10,

cla.attribute_char11 cust_clm_attribute_char11,

cla.attribute_char12 cust_clm_attribute_char12,

cla.attribute_char13 cust_clm_attribute_char13,

cla.attribute_char14 cust_clm_attribute_char14,

cla.attribute_char15 cust_clm_attribute_char15,

cla.attribute_char16 cust_clm_attribute_char16,

cla.attribute_char17 cust_clm_attribute_char17,

cla.attribute_char18 cust_clm_attribute_char18,

cla.attribute_char19 cust_clm_attribute_char19,

cla.attribute_char20 cust_clm_attribute_char20,

cla.attribute_number1 cust_clm_attribute_number1,

cla.attribute_number2 cust_clm_attribute_number2,

cla.attribute_number3 cust_clm_attribute_number3,

cla.attribute_number4 cust_clm_attribute_number4,

cla.attribute_number5 cust_clm_attribute_number5,

cla.attribute_number6 cust_clm_attribute_number6,

cla.attribute_number7 cust_clm_attribute_number7,

cla.attribute_number8 cust_clm_attribute_number8,

cla.attribute_number9 cust_clm_attribute_number9,

cla.attribute_number10 cust_clm_attribute_number10,

cla.attribute_date1 cust_clm_attribute_date1,

cla.attribute_date2 cust_clm_attribute_date2,

cla.attribute_date3 cust_clm_attribute_date3,

cla.attribute_date4 cust_clm_attribute_date4,

cla.attribute_date5 cust_clm_attribute_date5

FROM

cjm_claims_all cla,

cjm_claim_payments_all ccp,

xla_events_gt xeg,

cjm_xla_customer_claim_headers cxh,

cjm_claim_class_src_map map

WHERE

cla.claim_id = cxh.claim_id

AND cla.claim_id = ccp.claim_id

AND ccp.payment_method_code != 'WRITE_OFF'

AND xeg.application_id = 10566

AND 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.source_id_int_1 = cxh.claim_id

AND cxh.event_type_code = xeg.event_type_code

AND xeg.event_id = cxh.event_id

AND cla.source_map_id = map.source_map_id