VIEW: APPS.DPP_XLA_CLAIM_HEADERS_V

Source

View Text - Preformatted

SELECT XEG.EVENT_ID, DXH.PP_TRANSACTION_TYPE, 'PP_CLAIM_SETTLEMENT' DISTRIBUTION_TYPE, DTH.TRANSACTION_HEADER_ID, DXH.BASE_TRANSACTION_HEADER_ID, DTH.TRANSACTION_NUMBER, ASP.VENDOR_NAME, ASS.VENDOR_SITE_CODE, 'Y', DPP_XLA_EVENT_PUB.get_pp_accrual_ccid(DTH.org_id ,DTH.vendor_id ,dth.vendor_site_id) PP_ACCRUAL_CCID, DPP_XLA_EVENT_PUB.get_pp_cost_adjustment_ccid(DTH.org_id,DTH.vendor_id,dth.vendor_site_id ) PP_COST_ADJ_CCID, DPP_XLA_EVENT_PUB.get_pp_contra_liab_ccid( dth.org_id , dth.vendor_id , dth.vendor_site_id ) PP_CONTRA_LIAB_CCID, DPP_XLA_EVENT_PUB.get_pp_ap_clearing_ccid(oca.claim_id,dth.org_id,dth.vendor_id,dth.vendor_site_id) PP_AP_CLEARING_CCID, DPP_XLA_EVENT_PUB.get_pp_ar_clearing_ccid(oca.claim_id,dth.org_id,dth.vendor_id,dth.vendor_site_id) PP_AR_CLEARING_CCID, ASS.ORG_ID, DECODE(SUBSTR(DTC.CLAIM_TYPE,1,1),'C',OCA.CUST_ACCOUNT_ID,OCA.VENDOR_ID) PARTY_ID, DECODE(SUBSTR(DTC.CLAIM_TYPE,1,1),'C',OCA.CUST_BILLTO_ACCT_SITE_ID,OCA.VENDOR_SITE_ID) PARTY_SITE_ID, DECODE(SUBSTR(DTC.CLAIM_TYPE,1,1),'C','C','S') PARTY_TYPE, DTH.ATTRIBUTE_CATEGORY, DTH.ATTRIBUTE1 , DTH.ATTRIBUTE2 , DTH.ATTRIBUTE3 , DTH.ATTRIBUTE4 , DTH.ATTRIBUTE5 , DTH.ATTRIBUTE6 , DTH.ATTRIBUTE7 , DTH.ATTRIBUTE8 , DTH.ATTRIBUTE9 , DTH.ATTRIBUTE10 , DTH.ATTRIBUTE11 , DTH.ATTRIBUTE12 , DTH.ATTRIBUTE13 , DTH.ATTRIBUTE14 , DTH.ATTRIBUTE15 , DTH.ATTRIBUTE16 , DTH.ATTRIBUTE17 , DTH.ATTRIBUTE18 , DTH.ATTRIBUTE19 , DTH.ATTRIBUTE20 , DTH.ATTRIBUTE21 , DTH.ATTRIBUTE22 , DTH.ATTRIBUTE23 , DTH.ATTRIBUTE24 , DTH.ATTRIBUTE25 , DTH.ATTRIBUTE26 , DTH.ATTRIBUTE27 , DTH.ATTRIBUTE28 , DTH.ATTRIBUTE29 , DTH.ATTRIBUTE30 , DTH.CONTACT_EMAIL_ADDRESS , DTH.CONTACT_PHONE , DTH.TRANSACTION_SOURCE , DTH.EFFECTIVE_START_DATE , DTH.DAYS_COVERED , DTH.TRX_CURRENCY , DTH.TRANSACTION_STATUS , ASS.ORG_ID FROM XLA_EVENTS_GT XEG, DPP_XLA_HEADERS DXH, DPP_TRANSACTION_HEADERS_ALL DTH, OZF_CLAIMS_ALL OCA, DPP_TRANSACTION_CLAIMS_ALL DTC, AP_SUPPLIERS ASP, AP_SUPPLIER_SITES_ALL ASS WHERE XEG.ENTITY_CODE = 'CLAIM_SETTLEMENT' AND XEG.SOURCE_ID_INT_1 = DTH.TRANSACTION_HEADER_ID AND XEG.SOURCE_ID_INT_2 = DXH.BASE_TRANSACTION_HEADER_ID AND DXH.TRANSACTION_HEADER_ID = DTH.TRANSACTION_HEADER_ID AND DXH.PP_TRANSACTION_TYPE IN ('SUPP_DSTR_CL','SUPP_DSTR_INC_CL','SUPP_CUST_CL','CUST_CL') AND OCA.CLAIM_ID =DXH.BASE_TRANSACTION_HEADER_ID AND DTC.CLAIM_ID=DXH.BASE_TRANSACTION_HEADER_ID AND DTC.TRANSACTION_HEADER_ID=DXH.TRANSACTION_HEADER_ID AND ASP.VENDOR_ID=DTH.VENDOR_ID AND ASS.VENDOR_SITE_ID=DTH.VENDOR_SITE_ID AND ASS.ORG_ID=DTH.ORG_ID

View Text - HTML Formatted

SELECT XEG.EVENT_ID
, DXH.PP_TRANSACTION_TYPE
, 'PP_CLAIM_SETTLEMENT' DISTRIBUTION_TYPE
, DTH.TRANSACTION_HEADER_ID
, DXH.BASE_TRANSACTION_HEADER_ID
, DTH.TRANSACTION_NUMBER
, ASP.VENDOR_NAME
, ASS.VENDOR_SITE_CODE
, 'Y'
, DPP_XLA_EVENT_PUB.GET_PP_ACCRUAL_CCID(DTH.ORG_ID
, DTH.VENDOR_ID
, DTH.VENDOR_SITE_ID) PP_ACCRUAL_CCID
, DPP_XLA_EVENT_PUB.GET_PP_COST_ADJUSTMENT_CCID(DTH.ORG_ID
, DTH.VENDOR_ID
, DTH.VENDOR_SITE_ID ) PP_COST_ADJ_CCID
, DPP_XLA_EVENT_PUB.GET_PP_CONTRA_LIAB_CCID( DTH.ORG_ID
, DTH.VENDOR_ID
, DTH.VENDOR_SITE_ID ) PP_CONTRA_LIAB_CCID
, DPP_XLA_EVENT_PUB.GET_PP_AP_CLEARING_CCID(OCA.CLAIM_ID
, DTH.ORG_ID
, DTH.VENDOR_ID
, DTH.VENDOR_SITE_ID) PP_AP_CLEARING_CCID
, DPP_XLA_EVENT_PUB.GET_PP_AR_CLEARING_CCID(OCA.CLAIM_ID
, DTH.ORG_ID
, DTH.VENDOR_ID
, DTH.VENDOR_SITE_ID) PP_AR_CLEARING_CCID
, ASS.ORG_ID
, DECODE(SUBSTR(DTC.CLAIM_TYPE
, 1
, 1)
, 'C'
, OCA.CUST_ACCOUNT_ID
, OCA.VENDOR_ID) PARTY_ID
, DECODE(SUBSTR(DTC.CLAIM_TYPE
, 1
, 1)
, 'C'
, OCA.CUST_BILLTO_ACCT_SITE_ID
, OCA.VENDOR_SITE_ID) PARTY_SITE_ID
, DECODE(SUBSTR(DTC.CLAIM_TYPE
, 1
, 1)
, 'C'
, 'C'
, 'S') PARTY_TYPE
, DTH.ATTRIBUTE_CATEGORY
, DTH.ATTRIBUTE1
, DTH.ATTRIBUTE2
, DTH.ATTRIBUTE3
, DTH.ATTRIBUTE4
, DTH.ATTRIBUTE5
, DTH.ATTRIBUTE6
, DTH.ATTRIBUTE7
, DTH.ATTRIBUTE8
, DTH.ATTRIBUTE9
, DTH.ATTRIBUTE10
, DTH.ATTRIBUTE11
, DTH.ATTRIBUTE12
, DTH.ATTRIBUTE13
, DTH.ATTRIBUTE14
, DTH.ATTRIBUTE15
, DTH.ATTRIBUTE16
, DTH.ATTRIBUTE17
, DTH.ATTRIBUTE18
, DTH.ATTRIBUTE19
, DTH.ATTRIBUTE20
, DTH.ATTRIBUTE21
, DTH.ATTRIBUTE22
, DTH.ATTRIBUTE23
, DTH.ATTRIBUTE24
, DTH.ATTRIBUTE25
, DTH.ATTRIBUTE26
, DTH.ATTRIBUTE27
, DTH.ATTRIBUTE28
, DTH.ATTRIBUTE29
, DTH.ATTRIBUTE30
, DTH.CONTACT_EMAIL_ADDRESS
, DTH.CONTACT_PHONE
, DTH.TRANSACTION_SOURCE
, DTH.EFFECTIVE_START_DATE
, DTH.DAYS_COVERED
, DTH.TRX_CURRENCY
, DTH.TRANSACTION_STATUS
, ASS.ORG_ID
FROM XLA_EVENTS_GT XEG
, DPP_XLA_HEADERS DXH
, DPP_TRANSACTION_HEADERS_ALL DTH
, OZF_CLAIMS_ALL OCA
, DPP_TRANSACTION_CLAIMS_ALL DTC
, AP_SUPPLIERS ASP
, AP_SUPPLIER_SITES_ALL ASS
WHERE XEG.ENTITY_CODE = 'CLAIM_SETTLEMENT'
AND XEG.SOURCE_ID_INT_1 = DTH.TRANSACTION_HEADER_ID
AND XEG.SOURCE_ID_INT_2 = DXH.BASE_TRANSACTION_HEADER_ID
AND DXH.TRANSACTION_HEADER_ID = DTH.TRANSACTION_HEADER_ID
AND DXH.PP_TRANSACTION_TYPE IN ('SUPP_DSTR_CL'
, 'SUPP_DSTR_INC_CL'
, 'SUPP_CUST_CL'
, 'CUST_CL')
AND OCA.CLAIM_ID =DXH.BASE_TRANSACTION_HEADER_ID
AND DTC.CLAIM_ID=DXH.BASE_TRANSACTION_HEADER_ID
AND DTC.TRANSACTION_HEADER_ID=DXH.TRANSACTION_HEADER_ID
AND ASP.VENDOR_ID=DTH.VENDOR_ID
AND ASS.VENDOR_SITE_ID=DTH.VENDOR_SITE_ID
AND ASS.ORG_ID=DTH.ORG_ID