VIEW: APPS.OZF_XLA_CLAIM_HEADER_V

Source

View Text - Preformatted

SELECT /*+ leading(XEG) use_nl(XEG OXH)*/ XEG.EVENT_ID EVENT_ID, 'C' THIRD_PARTY_TYPE, CLA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, CLA.cust_billto_acct_site_id BILL_TO_SITE_USE_ID, 'CLAIM_SETTLEMENT' DISTRIBUTION_TYPE, CLA.CLAIM_ID CLAIM_ID, CLA.CLAIM_NUMBER CLAIM_NUMBER, 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.ORG_ID ORG_ID, NVL(CLA.GL_DATE, DECODE(SYSPA.gl_date_type, 'CLAIM_DATE', CLA.CLAIM_DATE, SYSDATE)) GL_DATE, ARSYS.code_combination_id_gain EXCHANGE_GAIN_ACCT_CCID, ARSYS.code_combination_id_LOSS EXCHANGE_LOSS_ACCT_CCID, SYSPA.gl_id_accr_promo_liab SYSPARAM_LIABILITY_ACCT_CCID, SYSPA.gl_id_ded_adj SYSPARAM_EXPENSE_ACCT_CCID, SYSPA.gl_rec_clearing_account SYSPARAM_AR_CLEARING_ACCT_CCID, SYSPA.gl_id_ded_clearing SYSPARAM_AP_CLEARING_ACCT_CCID, CLTY.gl_id_ded_adj_clearing CLA_TYPE_AR_CLEARING_ACCT_CCID, CLTY.gl_id_ded_clearing CLA_TYPE_AP_CLEARING_ACCT_CCID, CLA.amount_settled CLAIM_SETTLED_AMOUNT, CLA.amount_adjusted CLAIM_ADJUSTED_AMOUNT, CLA.payment_method payment_method, CLA.payment_reference_id payment_reference_id, CLA.payment_reference_number payment_reference_number, CLA.payment_reference_date payment_reference_date, CLA.payment_status payment_status, CLA.ATTRIBUTE_CATEGORY HDR_ATTR_CATEGORY, CLA.ATTRIBUTE1 HDR_ATTRIBUTE1 , CLA.ATTRIBUTE2 HDR_ATTRIBUTE2 , CLA.ATTRIBUTE3 HDR_ATTRIBUTE3 , CLA.ATTRIBUTE4 HDR_ATTRIBUTE4 , CLA.ATTRIBUTE5 HDR_ATTRIBUTE5 , CLA.ATTRIBUTE6 HDR_ATTRIBUTE6 , CLA.ATTRIBUTE7 HDR_ATTRIBUTE7 , CLA.ATTRIBUTE8 HDR_ATTRIBUTE8 , CLA.ATTRIBUTE9 HDR_ATTRIBUTE9 , CLA.ATTRIBUTE10 HDR_ATTRIBUTE10 , CLA.ATTRIBUTE11 HDR_ATTRIBUTE11 , CLA.ATTRIBUTE12 HDR_ATTRIBUTE12 , CLA.ATTRIBUTE13 HDR_ATTRIBUTE13 , CLA.ATTRIBUTE14 HDR_ATTRIBUTE14 , CLA.ATTRIBUTE15 HDR_ATTRIBUTE15 , 'Y', OXH.reversal_flag, CLA.SOURCE_OBJECT_CLASS SOURCE_OBJECT_CLASS FROM OZF_CLAIMS_ALL CLA, XLA_EVENTS_GT XEG, OZF_XLA_CLAIM_HEADERS OXH, OZF_SYS_PARAMETERS_ALL SYSPA, OZF_CLAIM_TYPES_ALL_B CLTY, AR_SYSTEM_PARAMETERS_ALL ARSYS WHERE CLA.CLAIM_ID = OXH.CLAIM_ID AND XEG.ENTITY_CODE = 'CLAIM_SETTLEMENT' 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 XEG.APPLICATION_ID = 682 AND SYSPA.ORG_ID = CLA.ORG_ID AND CLA.CLAIM_TYPE_ID = CLTY.CLAIM_TYPE_ID AND CLA.ORG_ID = SYSPA.ORG_ID AND ARSYS.ORG_ID = CLA.ORG_ID

View Text - HTML Formatted

SELECT /*+ LEADING(XEG) USE_NL(XEG OXH)*/ XEG.EVENT_ID EVENT_ID
, 'C' THIRD_PARTY_TYPE
, CLA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, CLA.CUST_BILLTO_ACCT_SITE_ID BILL_TO_SITE_USE_ID
, 'CLAIM_SETTLEMENT' DISTRIBUTION_TYPE
, CLA.CLAIM_ID CLAIM_ID
, CLA.CLAIM_NUMBER CLAIM_NUMBER
, 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.ORG_ID ORG_ID
, NVL(CLA.GL_DATE
, DECODE(SYSPA.GL_DATE_TYPE
, 'CLAIM_DATE'
, CLA.CLAIM_DATE
, SYSDATE)) GL_DATE
, ARSYS.CODE_COMBINATION_ID_GAIN EXCHANGE_GAIN_ACCT_CCID
, ARSYS.CODE_COMBINATION_ID_LOSS EXCHANGE_LOSS_ACCT_CCID
, SYSPA.GL_ID_ACCR_PROMO_LIAB SYSPARAM_LIABILITY_ACCT_CCID
, SYSPA.GL_ID_DED_ADJ SYSPARAM_EXPENSE_ACCT_CCID
, SYSPA.GL_REC_CLEARING_ACCOUNT SYSPARAM_AR_CLEARING_ACCT_CCID
, SYSPA.GL_ID_DED_CLEARING SYSPARAM_AP_CLEARING_ACCT_CCID
, CLTY.GL_ID_DED_ADJ_CLEARING CLA_TYPE_AR_CLEARING_ACCT_CCID
, CLTY.GL_ID_DED_CLEARING CLA_TYPE_AP_CLEARING_ACCT_CCID
, CLA.AMOUNT_SETTLED CLAIM_SETTLED_AMOUNT
, CLA.AMOUNT_ADJUSTED CLAIM_ADJUSTED_AMOUNT
, CLA.PAYMENT_METHOD PAYMENT_METHOD
, CLA.PAYMENT_REFERENCE_ID PAYMENT_REFERENCE_ID
, CLA.PAYMENT_REFERENCE_NUMBER PAYMENT_REFERENCE_NUMBER
, CLA.PAYMENT_REFERENCE_DATE PAYMENT_REFERENCE_DATE
, CLA.PAYMENT_STATUS PAYMENT_STATUS
, CLA.ATTRIBUTE_CATEGORY HDR_ATTR_CATEGORY
, CLA.ATTRIBUTE1 HDR_ATTRIBUTE1
, CLA.ATTRIBUTE2 HDR_ATTRIBUTE2
, CLA.ATTRIBUTE3 HDR_ATTRIBUTE3
, CLA.ATTRIBUTE4 HDR_ATTRIBUTE4
, CLA.ATTRIBUTE5 HDR_ATTRIBUTE5
, CLA.ATTRIBUTE6 HDR_ATTRIBUTE6
, CLA.ATTRIBUTE7 HDR_ATTRIBUTE7
, CLA.ATTRIBUTE8 HDR_ATTRIBUTE8
, CLA.ATTRIBUTE9 HDR_ATTRIBUTE9
, CLA.ATTRIBUTE10 HDR_ATTRIBUTE10
, CLA.ATTRIBUTE11 HDR_ATTRIBUTE11
, CLA.ATTRIBUTE12 HDR_ATTRIBUTE12
, CLA.ATTRIBUTE13 HDR_ATTRIBUTE13
, CLA.ATTRIBUTE14 HDR_ATTRIBUTE14
, CLA.ATTRIBUTE15 HDR_ATTRIBUTE15
, 'Y'
, OXH.REVERSAL_FLAG
, CLA.SOURCE_OBJECT_CLASS SOURCE_OBJECT_CLASS
FROM OZF_CLAIMS_ALL CLA
, XLA_EVENTS_GT XEG
, OZF_XLA_CLAIM_HEADERS OXH
, OZF_SYS_PARAMETERS_ALL SYSPA
, OZF_CLAIM_TYPES_ALL_B CLTY
, AR_SYSTEM_PARAMETERS_ALL ARSYS
WHERE CLA.CLAIM_ID = OXH.CLAIM_ID
AND XEG.ENTITY_CODE = 'CLAIM_SETTLEMENT'
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 XEG.APPLICATION_ID = 682
AND SYSPA.ORG_ID = CLA.ORG_ID
AND CLA.CLAIM_TYPE_ID = CLTY.CLAIM_TYPE_ID
AND CLA.ORG_ID = SYSPA.ORG_ID
AND ARSYS.ORG_ID = CLA.ORG_ID