VIEW: APPS.PA_VRM_SOURCE_DOC_SUB_LINES

Source

View Text - Preformatted

SELECT 'DISTRIBUTIONS' DOC_SUB_LINE_TYPE, 'H' DOC_SUB_LINE_LEVEL, CRDL.EXPENDITURE_ITEM_ID DOC_SUB_LINE_ID_INT_1, CRDL.LINE_NUM DOC_SUB_LINE_ID_INT_2, 'Expenditure Item' DOC_SUB_LINE_ID_CHAR_1, CRDL.PROJECT_ID DOC_ID_INT_1, CRDL.LINE_NUM LINE_NUMBER, CRDL.ORG_ID ORG_ID, PI.SET_OF_BOOKS_ID LEDGER_ID, CRDL.CREATION_DATE SUB_LINE_CREATION_DATE, CRDL.PROGRAM_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE, 'REV' ACCOUNT_CLASS, DECODE(CRDL.REVTRANS_CURRENCY_CODE,NULL,CRDL.AMOUNT,CRDL.REVTRANS_AMOUNT) AMOUNT, CRDL.AMOUNT ACCTD_AMOUNT, XE.EVENT_DATE ACCOUNTING_DATE, DECODE(XE.EVENT_STATUS_CODE,'P',DECODE(XE.PROCESS_STATUS_CODE,'P','Y','N') ,'N') SLA_POSTED_FLAG, XE.LAST_UPDATE_DATE SLA_POSTED_DATE, PDR.EVENT_ID EVENT_ID, XEL.CODE_COMBINATION_ID CODE_COMBINATION_ID, CRDL.REQUEST_ID REQUEST_ID, 1 OBJECT_VERSION_NUMBER, CRDL.CREATION_DATE CREATION_DATE, FU1.USER_NAME CREATED_BY, CRDL.PROGRAM_UPDATE_DATE LAST_UPDATE_DATE, CRDL.PROGRAM_ID LAST_UPDATED_BY, CRDL.PROGRAM_ID LAST_UPDATE_LOGIN FROM PA_CUST_REV_DIST_LINES_ALL CRDL, XLE_FIRSTPARTY_INFORMATION_V XEP, PA_IMPLEMENTATIONS_ALL PI, HR_ORGANIZATION_INFORMATION HOI, PA_DRAFT_REVENUES_ALL PDR, XLA_EVENTS XE, XLA_DISTRIBUTION_LINKS XDL, XLA_AE_LINES XEL, FND_USER FU1 WHERE PI.ORG_ID = CRDL.ORG_ID AND HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND HOI.ORGANIZATION_ID = PI.ORG_ID AND XEP.LEGAL_ENTITY_ID = HOI.ORG_INFORMATION2 AND PDR.PROJECT_ID = CRDL.PROJECT_ID AND PDR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM AND XE.EVENT_ID = PDR.EVENT_ID AND XDL.APPLICATION_ID = 275 AND XDL.EVENT_ID = PDR.EVENT_ID AND XDL.SOURCE_DISTRIBUTION_TYPE = 'Revenue - Normal Revenue' AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = CRDL.EXPENDITURE_ITEM_ID AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = CRDL.LINE_NUM AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM AND FU1.USER_ID = CRDL.CREATED_BY AND DECODE(XE.EVENT_STATUS_CODE,'P',DECODE(XE.PROCESS_STATUS_CODE,'P','Y','N'),'N') = 'Y' UNION SELECT 'DISTRIBUTIONS' DOC_SUB_LINE_TYPE, 'H' DOC_SUB_LINE_PARENT_LEVEL, PE.EVENT_ID DOC_SUB_LINE_ID_INT_1, CRDL.LINE_NUM DOC_SUB_LINE_ID_INT_2, 'Event' DOC_SUB_LINE_ID_CHAR_1, CRDL.PROJECT_ID DOC_ID_INT_1, CRDL.LINE_NUM LINE_NUMBER, CRDL.ORG_ID ORG_ID, PI.SET_OF_BOOKS_ID LEDGER_ID, CRDL.CREATION_DATE SUB_LINE_CREATION_DATE, CRDL.PROGRAM_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE, 'REV' ACCOUNT_CLASS, DECODE(CRDL.REVTRANS_CURRENCY_CODE,NULL,CRDL.AMOUNT,CRDL.REVTRANS_AMOUNT) AMOUNT, CRDL.AMOUNT ACCTD_AMOUNT, XE.EVENT_DATE ACCOUNTING_DATE, DECODE(XE.EVENT_STATUS_CODE,'P',DECODE(XE.PROCESS_STATUS_CODE,'P','Y','N') ,'N') SLA_POSTED_FLAG, XE.LAST_UPDATE_DATE SLA_POSTED_DATE, PDR.EVENT_ID EVENT_ID, XEL.CODE_COMBINATION_ID CODE_COMBINATION_ID, CRDL.REQUEST_ID REQUEST_ID, 1 OBJECT_VERSION_NUMBER, CRDL.CREATION_DATE CREATION_DATE, FU1.USER_NAME CREATED_BY, CRDL.PROGRAM_UPDATE_DATE LAST_UPDATE_DATE, CRDL.PROGRAM_ID LAST_UPDATED_BY, CRDL.PROGRAM_ID LAST_UPDATE_LOGIN FROM PA_CUST_EVENT_RDL_ALL CRDL, PA_EVENTS PE, XLE_FIRSTPARTY_INFORMATION_V XEP, PA_IMPLEMENTATIONS_ALL PI, HR_ORGANIZATION_INFORMATION HOI, PA_DRAFT_REVENUES_ALL PDR, XLA_EVENTS XE, XLA_DISTRIBUTION_LINKS XDL, XLA_AE_LINES XEL, FND_USER FU1 WHERE PI.ORG_ID = CRDL.ORG_ID AND HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND HOI.ORGANIZATION_ID = PI.ORG_ID AND XEP.LEGAL_ENTITY_ID = HOI.ORG_INFORMATION2 AND PDR.PROJECT_ID = CRDL.PROJECT_ID AND PDR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM AND PE.PROJECT_ID = CRDL.PROJECT_ID AND NVL(PE.TASK_ID,-99) = NVL(CRDL.TASK_ID,-99) AND PE.EVENT_NUM = CRDL.EVENT_NUM AND XE.EVENT_ID = PDR.EVENT_ID AND XDL.APPLICATION_ID = 275 AND XDL.EVENT_ID = PDR.EVENT_ID AND XDL.SOURCE_DISTRIBUTION_TYPE = 'Revenue - Event Revenue' AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PE.EVENT_ID AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = CRDL.LINE_NUM AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM AND FU1.USER_ID = CRDL.CREATED_BY AND DECODE(XE.EVENT_STATUS_CODE,'P',DECODE(XE.PROCESS_STATUS_CODE,'P','Y','N'),'N') = 'Y'

View Text - HTML Formatted

SELECT 'DISTRIBUTIONS' DOC_SUB_LINE_TYPE
, 'H' DOC_SUB_LINE_LEVEL
, CRDL.EXPENDITURE_ITEM_ID DOC_SUB_LINE_ID_INT_1
, CRDL.LINE_NUM DOC_SUB_LINE_ID_INT_2
, 'EXPENDITURE ITEM' DOC_SUB_LINE_ID_CHAR_1
, CRDL.PROJECT_ID DOC_ID_INT_1
, CRDL.LINE_NUM LINE_NUMBER
, CRDL.ORG_ID ORG_ID
, PI.SET_OF_BOOKS_ID LEDGER_ID
, CRDL.CREATION_DATE SUB_LINE_CREATION_DATE
, CRDL.PROGRAM_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE
, 'REV' ACCOUNT_CLASS
, DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, CRDL.AMOUNT
, CRDL.REVTRANS_AMOUNT) AMOUNT
, CRDL.AMOUNT ACCTD_AMOUNT
, XE.EVENT_DATE ACCOUNTING_DATE
, DECODE(XE.EVENT_STATUS_CODE
, 'P'
, DECODE(XE.PROCESS_STATUS_CODE
, 'P'
, 'Y'
, 'N')
, 'N') SLA_POSTED_FLAG
, XE.LAST_UPDATE_DATE SLA_POSTED_DATE
, PDR.EVENT_ID EVENT_ID
, XEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CRDL.REQUEST_ID REQUEST_ID
, 1 OBJECT_VERSION_NUMBER
, CRDL.CREATION_DATE CREATION_DATE
, FU1.USER_NAME CREATED_BY
, CRDL.PROGRAM_UPDATE_DATE LAST_UPDATE_DATE
, CRDL.PROGRAM_ID LAST_UPDATED_BY
, CRDL.PROGRAM_ID LAST_UPDATE_LOGIN
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, XLE_FIRSTPARTY_INFORMATION_V XEP
, PA_IMPLEMENTATIONS_ALL PI
, HR_ORGANIZATION_INFORMATION HOI
, PA_DRAFT_REVENUES_ALL PDR
, XLA_EVENTS XE
, XLA_DISTRIBUTION_LINKS XDL
, XLA_AE_LINES XEL
, FND_USER FU1
WHERE PI.ORG_ID = CRDL.ORG_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND HOI.ORGANIZATION_ID = PI.ORG_ID
AND XEP.LEGAL_ENTITY_ID = HOI.ORG_INFORMATION2
AND PDR.PROJECT_ID = CRDL.PROJECT_ID
AND PDR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM
AND XE.EVENT_ID = PDR.EVENT_ID
AND XDL.APPLICATION_ID = 275
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - NORMAL REVENUE'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = CRDL.EXPENDITURE_ITEM_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = CRDL.LINE_NUM
AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND FU1.USER_ID = CRDL.CREATED_BY
AND DECODE(XE.EVENT_STATUS_CODE
, 'P'
, DECODE(XE.PROCESS_STATUS_CODE
, 'P'
, 'Y'
, 'N')
, 'N') = 'Y' UNION SELECT 'DISTRIBUTIONS' DOC_SUB_LINE_TYPE
, 'H' DOC_SUB_LINE_PARENT_LEVEL
, PE.EVENT_ID DOC_SUB_LINE_ID_INT_1
, CRDL.LINE_NUM DOC_SUB_LINE_ID_INT_2
, 'EVENT' DOC_SUB_LINE_ID_CHAR_1
, CRDL.PROJECT_ID DOC_ID_INT_1
, CRDL.LINE_NUM LINE_NUMBER
, CRDL.ORG_ID ORG_ID
, PI.SET_OF_BOOKS_ID LEDGER_ID
, CRDL.CREATION_DATE SUB_LINE_CREATION_DATE
, CRDL.PROGRAM_UPDATE_DATE SUB_LINE_LAST_UPDATE_DATE
, 'REV' ACCOUNT_CLASS
, DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, CRDL.AMOUNT
, CRDL.REVTRANS_AMOUNT) AMOUNT
, CRDL.AMOUNT ACCTD_AMOUNT
, XE.EVENT_DATE ACCOUNTING_DATE
, DECODE(XE.EVENT_STATUS_CODE
, 'P'
, DECODE(XE.PROCESS_STATUS_CODE
, 'P'
, 'Y'
, 'N')
, 'N') SLA_POSTED_FLAG
, XE.LAST_UPDATE_DATE SLA_POSTED_DATE
, PDR.EVENT_ID EVENT_ID
, XEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CRDL.REQUEST_ID REQUEST_ID
, 1 OBJECT_VERSION_NUMBER
, CRDL.CREATION_DATE CREATION_DATE
, FU1.USER_NAME CREATED_BY
, CRDL.PROGRAM_UPDATE_DATE LAST_UPDATE_DATE
, CRDL.PROGRAM_ID LAST_UPDATED_BY
, CRDL.PROGRAM_ID LAST_UPDATE_LOGIN
FROM PA_CUST_EVENT_RDL_ALL CRDL
, PA_EVENTS PE
, XLE_FIRSTPARTY_INFORMATION_V XEP
, PA_IMPLEMENTATIONS_ALL PI
, HR_ORGANIZATION_INFORMATION HOI
, PA_DRAFT_REVENUES_ALL PDR
, XLA_EVENTS XE
, XLA_DISTRIBUTION_LINKS XDL
, XLA_AE_LINES XEL
, FND_USER FU1
WHERE PI.ORG_ID = CRDL.ORG_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND HOI.ORGANIZATION_ID = PI.ORG_ID
AND XEP.LEGAL_ENTITY_ID = HOI.ORG_INFORMATION2
AND PDR.PROJECT_ID = CRDL.PROJECT_ID
AND PDR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM
AND PE.PROJECT_ID = CRDL.PROJECT_ID
AND NVL(PE.TASK_ID
, -99) = NVL(CRDL.TASK_ID
, -99)
AND PE.EVENT_NUM = CRDL.EVENT_NUM
AND XE.EVENT_ID = PDR.EVENT_ID
AND XDL.APPLICATION_ID = 275
AND XDL.EVENT_ID = PDR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - EVENT REVENUE'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = PE.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = CRDL.LINE_NUM
AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND FU1.USER_ID = CRDL.CREATED_BY
AND DECODE(XE.EVENT_STATUS_CODE
, 'P'
, DECODE(XE.PROCESS_STATUS_CODE
, 'P'
, 'Y'
, 'N')
, 'N') = 'Y'