1. Tables and Views for SCM
  2. CJM_XLA_SUPP_CLAIM_HEADER_V

CJM_XLA_SUPP_CLAIM_HEADER_V

Details

  • Schema: FUSION

  • Object owner: CJM

  • Object type: VIEW

Columns

Name

EVENT_ID

THIRD_PARTY_TYPE

CLAIM_ID

CLAIM_NUMBER

CLAIM_CLASS_CODE

CLAIM_SOURCE_CODE

CLAIM_TYPE_ID

REASON_CODE_ID

VENDOR_ID

VENDOR_SITE_ID

CLAIM_CURRENCY_CODE

CLAIM_AMOUNT

CLAIM_LEDGER_AMOUNT

CLAIM_EXCHANGE_RATE_TYPE

CLAIM_EXCHANGE_RATE_DATE

CLAIM_EXCHANGE_RATE

CLAIM_BU_ID

CLAIM_GL_DATE

CLAIM_SETTLED_AMOUNT

PAYMENT_METHOD_CODE

PAYMENT_REFERENCE_ID

PAYMENT_REFERENCE_NUMBER

PAYMENT_REFERENCE_DATE

PAYMENT_STATUS_CODE

SOURCE_OBJECT_CLASS

TRANSACTION_REVERSAL_FLAG

OVERRIDE_ACCTD_AMT_FLAG

CLM_HEADER_ATTRIB_CATGORY

CLM_HEADER_ATTRIB_1

CLM_HEADER_ATTRIB_2

CLM_HEADER_ATTRIB_3

CLM_HEADER_ATTRIB_4

CLM_HEADER_ATTRIB_5

CLM_HEADER_ATTRIB_6

CLM_HEADER_ATTRIB_7

CLM_HEADER_ATTRIB_8

CLM_HEADER_ATTRIB_9

CLM_HEADER_ATTRIB_10

CLM_HEADER_ATTRIB_11

CLM_HEADER_ATTRIB_12

CLM_HEADER_ATTRIB_13

CLM_HEADER_ATTRIB_14

CLM_HEADER_ATTRIB_15

CLM_HEADER_ATTRIB_16

CLM_HEADER_ATTRIB_17

CLM_HEADER_ATTRIB_18

CLM_HEADER_ATTRIB_19

CLM_HEADER_ATTRIB_20

CLM_HEADER_ATTRIB_NUM_1

CLM_HEADER_ATTRIB_NUM_2

CLM_HEADER_ATTRIB_NUM_3

CLM_HEADER_ATTRIB_NUM_4

CLM_HEADER_ATTRIB_NUM_5

CLM_HEADER_ATTRIB_NUM_6

CLM_HEADER_ATTRIB_NUM_7

CLM_HEADER_ATTRIB_NUM_8

CLM_HEADER_ATTRIB_NUM_9

CLM_HEADER_ATTRIB_NUM_10

CLM_HEADER_ATTRIB_DATE_1

CLM_HEADER_ATTRIB_DATE_2

CLM_HEADER_ATTRIB_DATE_3

CLM_HEADER_ATTRIB_DATE_4

CLM_HEADER_ATTRIB_DATE_5

Query

SQL_Statement

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

xeg.event_id event_id,

'S' third_party_type,

cla.claim_id claim_id,

cla.claim_number claim_number,

cla.claim_class_code claim_class_code,

map.claim_source_code claim_source_code,

cla.claim_type_id claim_type_id,

cla.reason_code_id reason_code_id,

cla.vendor_id,

cla.vendor_site_id,

cla.currency_code claim_currency_code,

cla.amount claim_amount,

cla.acctd_amount claim_ledger_amount,

cla.exchange_rate_type claim_exchange_rate_type,

cla.exchange_rate_date claim_exchange_rate_date,

cla.exchange_rate claim_exchange_rate,

cla.bu_id claim_bu_id,

xeg.event_date claim_gl_date,

cla.amount_settled claim_settled_amount,

ccp.payment_method_code payment_method_code,

ccp.payment_reference_id payment_reference_id,

ccp.payment_reference_number payment_reference_number,

ccp.payment_reference_date payment_reference_date,

ccp.payment_status_code payment_status_code,

cla.source_object_class source_object_class,

oxh.reversal_flag transaction_reversal_flag,

'Y' override_acctd_amt_flag,

cla.ATTRIBUTE_CATEGORY CLM_HEADER_ATTRIB_CATGORY ,

cla.ATTRIBUTE_CHAR1 CLM_HEADER_ATTRIB_1 ,

cla.ATTRIBUTE_CHAR2 CLM_HEADER_ATTRIB_2 ,

cla.ATTRIBUTE_CHAR3 CLM_HEADER_ATTRIB_3 ,

cla.ATTRIBUTE_CHAR4 CLM_HEADER_ATTRIB_4 ,

cla.ATTRIBUTE_CHAR5 CLM_HEADER_ATTRIB_5 ,

cla.ATTRIBUTE_CHAR6 CLM_HEADER_ATTRIB_6 ,

cla.ATTRIBUTE_CHAR7 CLM_HEADER_ATTRIB_7 ,

cla.ATTRIBUTE_CHAR8 CLM_HEADER_ATTRIB_8 ,

cla.ATTRIBUTE_CHAR9 CLM_HEADER_ATTRIB_9 ,

cla.ATTRIBUTE_CHAR10 CLM_HEADER_ATTRIB_10 ,

cla.ATTRIBUTE_CHAR11 CLM_HEADER_ATTRIB_11 ,

cla.ATTRIBUTE_CHAR12 CLM_HEADER_ATTRIB_12 ,

cla.ATTRIBUTE_CHAR13 CLM_HEADER_ATTRIB_13 ,

cla.ATTRIBUTE_CHAR14 CLM_HEADER_ATTRIB_14 ,

cla.ATTRIBUTE_CHAR15 CLM_HEADER_ATTRIB_15 ,

cla.ATTRIBUTE_CHAR16 CLM_HEADER_ATTRIB_16 ,

cla.ATTRIBUTE_CHAR17 CLM_HEADER_ATTRIB_17 ,

cla.ATTRIBUTE_CHAR18 CLM_HEADER_ATTRIB_18 ,

cla.ATTRIBUTE_CHAR19 CLM_HEADER_ATTRIB_19 ,

cla.ATTRIBUTE_CHAR20 CLM_HEADER_ATTRIB_20 ,

cla.ATTRIBUTE_NUMBER1 CLM_HEADER_ATTRIB_NUM_1 ,

cla.ATTRIBUTE_NUMBER2 CLM_HEADER_ATTRIB_NUM_2 ,

cla.ATTRIBUTE_NUMBER3 CLM_HEADER_ATTRIB_NUM_3 ,

cla.ATTRIBUTE_NUMBER4 CLM_HEADER_ATTRIB_NUM_4 ,

cla.ATTRIBUTE_NUMBER5 CLM_HEADER_ATTRIB_NUM_5 ,

cla.ATTRIBUTE_NUMBER6 CLM_HEADER_ATTRIB_NUM_6 ,

cla.ATTRIBUTE_NUMBER7 CLM_HEADER_ATTRIB_NUM_7 ,

cla.ATTRIBUTE_NUMBER8 CLM_HEADER_ATTRIB_NUM_8 ,

cla.ATTRIBUTE_NUMBER9 CLM_HEADER_ATTRIB_NUM_9 ,

cla.ATTRIBUTE_NUMBER10 CLM_HEADER_ATTRIB_NUM_10 ,

cla.ATTRIBUTE_DATE1 CLM_HEADER_ATTRIB_DATE_1 ,

cla.ATTRIBUTE_DATE2 CLM_HEADER_ATTRIB_DATE_2 ,

cla.ATTRIBUTE_DATE3 CLM_HEADER_ATTRIB_DATE_3 ,

cla.ATTRIBUTE_DATE4 CLM_HEADER_ATTRIB_DATE_4 ,

cla.ATTRIBUTE_DATE5 CLM_HEADER_ATTRIB_DATE_5

FROM

cjm_claims_all cla,

cjm_claim_payments_all ccp,

xla_events_gt xeg,

cjm_xla_claim_headers oxh,

cjm_claim_class_src_map map

WHERE cla.claim_id = oxh.claim_id

AND cla.claim_id = ccp.claim_id

AND xeg.application_id = 10566

AND xeg.entity_code = 'SUPPLIER_CLAIM_SETTLEMENT'

AND cla.claim_for_code = 'SUPPLIER'

AND xeg.source_id_int_1 = oxh.claim_id

AND oxh.event_type_code = xeg.event_type_code

AND xeg.event_id = oxh.event_id

AND cla.source_map_id = map.source_map_id