VIEW: APPS.DPP_XLA_CLAIM_LINES_V

Source

View Text - Preformatted

SELECT DXC.EVENT_ID, ROW_NUMBER() OVER(PARTITION BY EVENT_ID ORDER BY EVENT_ID) AS LINE_NUMBER, OCL.AMOUNT ENTERED_AMOUNT, OCL.ACCTD_AMOUNT ACCOUNTED_AMOUNT, OCL.CURRENCY_CODE, OCL.CLAIM_LINE_ID DISTRIBUTION_IDENTIFIER, GL.LEDGER_ID, OCL.QUANTITY PRIMARY_QUANTITY, FSPA.INVENTORY_ORGANIZATION_ID ORGANIZATION_ID, OCA.CLAIM_DATE TRANSACTION_DATE, MSIB.COST_OF_SALES_ACCOUNT COGS_CCID, MSIB.SEGMENT1, CLAIM_NUMBER, DTL.ATTRIBUTE_CATEGORY, DTL.ATTRIBUTE1 , DTL.ATTRIBUTE2 , DTL.ATTRIBUTE3 , DTL.ATTRIBUTE4 , DTL.ATTRIBUTE5 , DTL.ATTRIBUTE6 , DTL.ATTRIBUTE7 , DTL.ATTRIBUTE8 , DTL.ATTRIBUTE9 , DTL.ATTRIBUTE10 , DTL.ATTRIBUTE11 , DTL.ATTRIBUTE12 , DTL.ATTRIBUTE13 , DTL.ATTRIBUTE14 , DTL.ATTRIBUTE15 , DTL.ATTRIBUTE16 , DTL.ATTRIBUTE17 , DTL.ATTRIBUTE18 , DTL.ATTRIBUTE19 , DTL.ATTRIBUTE20 , DTL.ATTRIBUTE21 , DTL.ATTRIBUTE22 , DTL.ATTRIBUTE23 , DTL.ATTRIBUTE24 , DTL.ATTRIBUTE25 , DTL.ATTRIBUTE26 , DTL.ATTRIBUTE27 , DTL.ATTRIBUTE28 , DTL.ATTRIBUTE29 , DTL.ATTRIBUTE30 , DTL.SUPPLIER_PART_NUM , DTL.PRIOR_PRICE , DTL.CHANGE_TYPE , DTL.CHANGE_VALUE FROM OZF_CLAIMS_ALL OCA, OZF_CLAIM_LINES_ALL OCL, DPP_XLA_CLAIM_HEADERS_V DXC, GL_LEDGERS GL , DPP_XLA_LINES DXL, FINANCIALS_SYSTEM_PARAMS_ALL FSPA, HR_OPERATING_UNITS HOU, MTL_SYSTEM_ITEMS_B MSIB, DPP_TRANSACTION_LINES_ALL DTL WHERE DXL.TRANSACTION_HEADER_ID=DXC.TRANSACTION_HEADER_ID AND DXL.BASE_TRANSACTION_HEADER_ID=DXC.BASE_TRANSACTION_HEADER_ID AND DXL.BASE_TRANSACTION_HEADER_ID=OCA.CLAIM_ID AND DXL.BASE_TRANSACTION_LINE_ID = OCL.CLAIM_LINE_ID AND FSPA.ORG_ID = DXC.CLAIM_ORG_ID AND FSPA.ORG_ID = HOU.ORGANIZATION_ID AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID AND OCA.CLAIM_ID=OCL.CLAIM_ID AND MSIB.organization_id = FSPA.INVENTORY_ORGANIZATION_ID AND MSIB.inventory_item_id=OCL.ITEM_ID AND DXC.PP_TRANSACTION_TYPE IN ('SUPP_DSTR_CL', 'SUPP_DSTR_INC_CL') AND DXL.TRANSACTION_LINE_ID=DTL.TRANSACTION_LINE_ID UNION ALL SELECT DXC.EVENT_ID, ROW_NUMBER() OVER(PARTITION BY EVENT_ID ORDER BY EVENT_ID) AS LINE_NUMBER, OCL.AMOUNT ENTERED_AMOUNT, OCL.ACCTD_AMOUNT ACCOUNTED_AMOUNT, OCL.CURRENCY_CODE CURRENCY_CODE, OCL.CLAIM_LINE_ID DISTRIBUTION_IDENTIFIER, GL.LEDGER_ID, OCL.QUANTITY PRIMARY_QUANTITY, FSPA.INVENTORY_ORGANIZATION_ID ORGANIZATION_ID, OCA.CLAIM_DATE TRANSACTION_DATE, MSIB.COST_OF_SALES_ACCOUNT COGS_CCID, MSIB.SEGMENT1, CLAIM_NUMBER, DCC.ATTRIBUTE_CATEGORY, DCC.ATTRIBUTE1 , DCC.ATTRIBUTE2 , DCC.ATTRIBUTE3 , DCC.ATTRIBUTE4 , DCC.ATTRIBUTE5 , DCC.ATTRIBUTE6 , DCC.ATTRIBUTE7 , DCC.ATTRIBUTE8 , DCC.ATTRIBUTE9 , DCC.ATTRIBUTE10 , DCC.ATTRIBUTE11 , DCC.ATTRIBUTE12 , DCC.ATTRIBUTE13 , DCC.ATTRIBUTE14 , DCC.ATTRIBUTE15 , DCC.ATTRIBUTE16 , DCC.ATTRIBUTE17 , DCC.ATTRIBUTE18 , DCC.ATTRIBUTE19 , DCC.ATTRIBUTE20 , DCC.ATTRIBUTE21 , DCC.ATTRIBUTE22 , DCC.ATTRIBUTE23 , DCC.ATTRIBUTE24 , DCC.ATTRIBUTE25 , DCC.ATTRIBUTE26 , DCC.ATTRIBUTE27 , DCC.ATTRIBUTE28 , DCC.ATTRIBUTE29 , DCC.ATTRIBUTE30 , NULL SUPPLIER_PART_NUM , NULL PRIOR_PRICE , NULL CHANGE_TYPE , NULL CHANGE_VALUE FROM OZF_CLAIMS_ALL OCA, OZF_CLAIM_LINES_ALL OCL, DPP_XLA_CLAIM_HEADERS_V DXC, GL_LEDGERS GL , DPP_XLA_LINES DXL, FINANCIALS_SYSTEM_PARAMS_ALL FSPA, HR_OPERATING_UNITS HOU, MTL_SYSTEM_ITEMS_B MSIB, DPP_CUSTOMER_CLAIMS_ALL DCC WHERE DXL.TRANSACTION_HEADER_ID=DXC.TRANSACTION_HEADER_ID AND DXL.BASE_TRANSACTION_HEADER_ID=DXC.BASE_TRANSACTION_HEADER_ID AND DXL.BASE_TRANSACTION_HEADER_ID=OCA.CLAIM_ID AND DXL.BASE_TRANSACTION_LINE_ID = OCL.CLAIM_LINE_ID AND FSPA.ORG_ID = DXC.CLAIM_ORG_ID AND FSPA.ORG_ID = HOU.ORGANIZATION_ID AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID AND OCA.CLAIM_ID=OCL.CLAIM_ID AND MSIB.organization_id = FSPA.INVENTORY_ORGANIZATION_ID AND MSIB.inventory_item_id=OCL.ITEM_ID AND DXC.PP_TRANSACTION_TYPE IN ('CUST_CL', 'SUPP_CUST_CL') AND DXL.TRANSACTION_LINE_ID=DCC.CUSTOMER_INV_LINE_ID

View Text - HTML Formatted

SELECT DXC.EVENT_ID
, ROW_NUMBER() OVER(PARTITION BY EVENT_ID ORDER BY EVENT_ID) AS LINE_NUMBER
, OCL.AMOUNT ENTERED_AMOUNT
, OCL.ACCTD_AMOUNT ACCOUNTED_AMOUNT
, OCL.CURRENCY_CODE
, OCL.CLAIM_LINE_ID DISTRIBUTION_IDENTIFIER
, GL.LEDGER_ID
, OCL.QUANTITY PRIMARY_QUANTITY
, FSPA.INVENTORY_ORGANIZATION_ID ORGANIZATION_ID
, OCA.CLAIM_DATE TRANSACTION_DATE
, MSIB.COST_OF_SALES_ACCOUNT COGS_CCID
, MSIB.SEGMENT1
, CLAIM_NUMBER
, DTL.ATTRIBUTE_CATEGORY
, DTL.ATTRIBUTE1
, DTL.ATTRIBUTE2
, DTL.ATTRIBUTE3
, DTL.ATTRIBUTE4
, DTL.ATTRIBUTE5
, DTL.ATTRIBUTE6
, DTL.ATTRIBUTE7
, DTL.ATTRIBUTE8
, DTL.ATTRIBUTE9
, DTL.ATTRIBUTE10
, DTL.ATTRIBUTE11
, DTL.ATTRIBUTE12
, DTL.ATTRIBUTE13
, DTL.ATTRIBUTE14
, DTL.ATTRIBUTE15
, DTL.ATTRIBUTE16
, DTL.ATTRIBUTE17
, DTL.ATTRIBUTE18
, DTL.ATTRIBUTE19
, DTL.ATTRIBUTE20
, DTL.ATTRIBUTE21
, DTL.ATTRIBUTE22
, DTL.ATTRIBUTE23
, DTL.ATTRIBUTE24
, DTL.ATTRIBUTE25
, DTL.ATTRIBUTE26
, DTL.ATTRIBUTE27
, DTL.ATTRIBUTE28
, DTL.ATTRIBUTE29
, DTL.ATTRIBUTE30
, DTL.SUPPLIER_PART_NUM
, DTL.PRIOR_PRICE
, DTL.CHANGE_TYPE
, DTL.CHANGE_VALUE
FROM OZF_CLAIMS_ALL OCA
, OZF_CLAIM_LINES_ALL OCL
, DPP_XLA_CLAIM_HEADERS_V DXC
, GL_LEDGERS GL
, DPP_XLA_LINES DXL
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, HR_OPERATING_UNITS HOU
, MTL_SYSTEM_ITEMS_B MSIB
, DPP_TRANSACTION_LINES_ALL DTL
WHERE DXL.TRANSACTION_HEADER_ID=DXC.TRANSACTION_HEADER_ID
AND DXL.BASE_TRANSACTION_HEADER_ID=DXC.BASE_TRANSACTION_HEADER_ID
AND DXL.BASE_TRANSACTION_HEADER_ID=OCA.CLAIM_ID
AND DXL.BASE_TRANSACTION_LINE_ID = OCL.CLAIM_LINE_ID
AND FSPA.ORG_ID = DXC.CLAIM_ORG_ID
AND FSPA.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND OCA.CLAIM_ID=OCL.CLAIM_ID
AND MSIB.ORGANIZATION_ID = FSPA.INVENTORY_ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=OCL.ITEM_ID
AND DXC.PP_TRANSACTION_TYPE IN ('SUPP_DSTR_CL'
, 'SUPP_DSTR_INC_CL')
AND DXL.TRANSACTION_LINE_ID=DTL.TRANSACTION_LINE_ID UNION ALL SELECT DXC.EVENT_ID
, ROW_NUMBER() OVER(PARTITION BY EVENT_ID ORDER BY EVENT_ID) AS LINE_NUMBER
, OCL.AMOUNT ENTERED_AMOUNT
, OCL.ACCTD_AMOUNT ACCOUNTED_AMOUNT
, OCL.CURRENCY_CODE CURRENCY_CODE
, OCL.CLAIM_LINE_ID DISTRIBUTION_IDENTIFIER
, GL.LEDGER_ID
, OCL.QUANTITY PRIMARY_QUANTITY
, FSPA.INVENTORY_ORGANIZATION_ID ORGANIZATION_ID
, OCA.CLAIM_DATE TRANSACTION_DATE
, MSIB.COST_OF_SALES_ACCOUNT COGS_CCID
, MSIB.SEGMENT1
, CLAIM_NUMBER
, DCC.ATTRIBUTE_CATEGORY
, DCC.ATTRIBUTE1
, DCC.ATTRIBUTE2
, DCC.ATTRIBUTE3
, DCC.ATTRIBUTE4
, DCC.ATTRIBUTE5
, DCC.ATTRIBUTE6
, DCC.ATTRIBUTE7
, DCC.ATTRIBUTE8
, DCC.ATTRIBUTE9
, DCC.ATTRIBUTE10
, DCC.ATTRIBUTE11
, DCC.ATTRIBUTE12
, DCC.ATTRIBUTE13
, DCC.ATTRIBUTE14
, DCC.ATTRIBUTE15
, DCC.ATTRIBUTE16
, DCC.ATTRIBUTE17
, DCC.ATTRIBUTE18
, DCC.ATTRIBUTE19
, DCC.ATTRIBUTE20
, DCC.ATTRIBUTE21
, DCC.ATTRIBUTE22
, DCC.ATTRIBUTE23
, DCC.ATTRIBUTE24
, DCC.ATTRIBUTE25
, DCC.ATTRIBUTE26
, DCC.ATTRIBUTE27
, DCC.ATTRIBUTE28
, DCC.ATTRIBUTE29
, DCC.ATTRIBUTE30
, NULL SUPPLIER_PART_NUM
, NULL PRIOR_PRICE
, NULL CHANGE_TYPE
, NULL CHANGE_VALUE
FROM OZF_CLAIMS_ALL OCA
, OZF_CLAIM_LINES_ALL OCL
, DPP_XLA_CLAIM_HEADERS_V DXC
, GL_LEDGERS GL
, DPP_XLA_LINES DXL
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, HR_OPERATING_UNITS HOU
, MTL_SYSTEM_ITEMS_B MSIB
, DPP_CUSTOMER_CLAIMS_ALL DCC
WHERE DXL.TRANSACTION_HEADER_ID=DXC.TRANSACTION_HEADER_ID
AND DXL.BASE_TRANSACTION_HEADER_ID=DXC.BASE_TRANSACTION_HEADER_ID
AND DXL.BASE_TRANSACTION_HEADER_ID=OCA.CLAIM_ID
AND DXL.BASE_TRANSACTION_LINE_ID = OCL.CLAIM_LINE_ID
AND FSPA.ORG_ID = DXC.CLAIM_ORG_ID
AND FSPA.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND OCA.CLAIM_ID=OCL.CLAIM_ID
AND MSIB.ORGANIZATION_ID = FSPA.INVENTORY_ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID=OCL.ITEM_ID
AND DXC.PP_TRANSACTION_TYPE IN ('CUST_CL'
, 'SUPP_CUST_CL')
AND DXL.TRANSACTION_LINE_ID=DCC.CUSTOMER_INV_LINE_ID