View: PA_XLA_REVENUE_LINES_ADJ_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_XLA_REVENUE_LINES_ADJ_V

View Text

SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, TO_NUMBER(5||CRDL.EXPENDITURE_ITEM_ID) LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM_REVERSED
, CRDL.PROJECT_ID
, CRDL.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(-1 * CRDL.AMOUNT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CRDL.CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, CRDL.LINE_NUM_REVERSED
, TO_NUMBER(NULL)
, 'REVENUE - NORMAL REVENUE'
, E.INCURRED_BY_PERSON_ID PERSON_ID
, DECODE(P.CURRENT_NPW_FLAG
, 'Y'
, P.NPW_NUMBER
, P.EMPLOYEE_NUMBER) EMPLOYEE_NUMBER
, NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, EI.ORGANIZATION_ID NL_RES_ORGANIZATION_ID
, EI.NON_LABOR_RESOURCE NON_LABOR_RESOURCE
, EI.SYSTEM_LINKAGE_FUNCTION
, EI.EXPENDITURE_TYPE
, EI.ATTRIBUTE_CATEGORY
, EI.ATTRIBUTE1
, EI.ATTRIBUTE2
, EI.ATTRIBUTE3
, EI.ATTRIBUTE4
, EI.ATTRIBUTE5
, EI.ATTRIBUTE6
, EI.ATTRIBUTE7
, EI.ATTRIBUTE8
, EI.ATTRIBUTE9
, EI.ATTRIBUTE10
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CRDL.REVPROC_CURRENCY_CODE
, CRDL.REVPROC_REVTRANS_EX_RATE
, CRDL.REVPROC_REVTRANS_RATE_TYPE
, CRDL.REVPROC_REVTRANS_RATE_DATE
, TO_NUMBER(DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, -1 * CRDL.AMOUNT
, -1 * CRDL.REVTRANS_AMOUNT))
, DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, CRDL.REVPROC_CURRENCY_CODE
, CRDL.REVTRANS_CURRENCY_CODE)
, DECODE(DR2.EVENT_ID
, NULL
, 'Y'
, 'N')
, TO_NUMBER(DECODE(SIGN(CRDL.AMOUNT)
, 1
, CRDL.CODE_COMBINATION_ID
, NULL))
, TO_NUMBER(DECODE(SIGN(CRDL.AMOUNT)
, -1
, CRDL.CODE_COMBINATION_ID
, NULL))
, DECODE(SIGN(CRDL.AMOUNT)
, 1
, 'REVENUE'
, NULL)
, DECODE(SIGN(CRDL.AMOUNT)
, -1
, 'REVENUE'
, NULL)
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_DRAFT_REVENUES_ALL DR
, PA_DRAFT_REVENUES_ALL DR2
, PA_IMPLEMENTATIONS_ALL IMP
, PER_ALL_PEOPLE_F P
, PA_EXPENDITURES_ALL E
WHERE CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND DR.PROJECT_ID = CRDL.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM
AND IMP.ORG_ID = DR.ORG_ID
AND DR.EVENT_ID IS NOT NULL /*
AND NVL(CRDL.AMOUNT
, 0) <> 0 */
AND P.PERSON_ID(+) = E.INCURRED_BY_PERSON_ID
AND EI.EXPENDITURE_ID = E.EXPENDITURE_ID
AND TRUNC(EI.EXPENDITURE_ITEM_DATE) BETWEEN TRUNC(NVL(P.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE))
AND TRUNC(NVL(P.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE))
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
AND DR2.PROJECT_ID = DR.PROJECT_ID
AND DR2.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM_CREDITED
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, TO_NUMBER(10||CRDL.EXPENDITURE_ITEM_ID) LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, CRDL2.EXPENDITURE_ITEM_ID
, CRDL2.LINE_NUM
, CRDL.PROJECT_ID
, CRDL.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER( -1 * CRDL.AMOUNT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CRDL.CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, CRDL.LINE_NUM_REVERSED
, TO_NUMBER(NULL)
, 'REVENUE - NORMAL REVENUE'
, E.INCURRED_BY_PERSON_ID PERSON_ID
, DECODE(P.CURRENT_NPW_FLAG
, 'Y'
, P.NPW_NUMBER
, P.EMPLOYEE_NUMBER) EMPLOYEE_NUMBER
, NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, E.INCURRED_BY_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, EI.ORGANIZATION_ID NL_RES_ORGANIZATION_ID
, EI.NON_LABOR_RESOURCE NON_LABOR_RESOURCE
, EI.SYSTEM_LINKAGE_FUNCTION
, EI.EXPENDITURE_TYPE
, EI.ATTRIBUTE_CATEGORY
, EI.ATTRIBUTE1
, EI.ATTRIBUTE2
, EI.ATTRIBUTE3
, EI.ATTRIBUTE4
, EI.ATTRIBUTE5
, EI.ATTRIBUTE6
, EI.ATTRIBUTE7
, EI.ATTRIBUTE8
, EI.ATTRIBUTE9
, EI.ATTRIBUTE10
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CRDL.REVPROC_CURRENCY_CODE
, CRDL.REVPROC_REVTRANS_EX_RATE
, CRDL.REVPROC_REVTRANS_RATE_TYPE
, CRDL.REVPROC_REVTRANS_RATE_DATE
, TO_NUMBER(DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, -1 * CRDL.AMOUNT
, -1 * CRDL.REVTRANS_AMOUNT))
, DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, CRDL.REVPROC_CURRENCY_CODE
, CRDL.REVTRANS_CURRENCY_CODE)
, DECODE(DR2.EVENT_ID
, NULL
, 'Y'
, 'N')
, TO_NUMBER(DECODE(SIGN(CRDL.AMOUNT)
, 1
, CRDL.CODE_COMBINATION_ID
, NULL))
, TO_NUMBER(DECODE(SIGN(CRDL.AMOUNT)
, -1
, CRDL.CODE_COMBINATION_ID))
, DECODE(SIGN(CRDL.AMOUNT)
, 1
, 'REVENUE'
, NULL)
, DECODE(SIGN(CRDL.AMOUNT)
, -1
, 'REVENUE'
, NULL)
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_CUST_REV_DIST_LINES_ALL CRDL2
, PA_DRAFT_REVENUES_ALL DR
, PA_DRAFT_REVENUES_ALL DR2
, PA_IMPLEMENTATIONS_ALL IMP
, PER_ALL_PEOPLE_F P
, PA_EXPENDITURES_ALL E
WHERE CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND DR.PROJECT_ID = CRDL.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM
AND IMP.ORG_ID = DR.ORG_ID
AND DR.EVENT_ID IS NOT NULL /*
AND NVL(CRDL.AMOUNT
, 0) <> 0 */
AND P.PERSON_ID(+) = E.INCURRED_BY_PERSON_ID
AND EI.EXPENDITURE_ID = E.EXPENDITURE_ID
AND TRUNC(EI.EXPENDITURE_ITEM_DATE) BETWEEN TRUNC(NVL(P.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE))
AND TRUNC(NVL(P.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE))
AND CRDL2.EXPENDITURE_ITEM_ID = EI.ADJUSTED_EXPENDITURE_ITEM_ID
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NOT NULL
AND CRDL2.PROJECT_ID = DR.PROJECT_ID
AND CRDL2.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM_CREDITED
AND DR2.PROJECT_ID = DR.PROJECT_ID
AND DR2.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM_CREDITED UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, TO_NUMBER(50||PE.EVENT_ID) LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, PE.EVENT_ID
, ERDL.LINE_NUM
, TO_NUMBER(PE.EVENT_ID)
, TO_NUMBER(ERDL.LINE_NUM_REVERSED)
, ERDL.PROJECT_ID
, ERDL.DRAFT_REVENUE_NUM
, ERDL.TASK_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PE.EVENT_ID
, ERDL.LINE_NUM
, TO_NUMBER(DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.AMOUNT)
, -1 * ERDL.AMOUNT))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ERDL.CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, ERDL.LINE_NUM_REVERSED
, 'REVENUE - EVENT REVENUE'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PE.EVENT_TYPE
, PE.ORGANIZATION_ID
, PE.ATTRIBUTE_CATEGORY
, PE.ATTRIBUTE1
, PE.ATTRIBUTE2
, PE.ATTRIBUTE3
, PE.ATTRIBUTE4
, PE.ATTRIBUTE5
, PE.ATTRIBUTE6
, PE.ATTRIBUTE7
, PE.ATTRIBUTE8
, PE.ATTRIBUTE9
, PE.ATTRIBUTE10
, PE.INVENTORY_ORG_ID
, PE.INVENTORY_ITEM_ID
, PE.REFERENCE1
, PE.REFERENCE2
, PE.REFERENCE3
, PE.REFERENCE4
, PE.REFERENCE5
, PE.REFERENCE6
, PE.REFERENCE7
, PE.REFERENCE8
, PE.REFERENCE9
, PE.REFERENCE10
, ERDL.REVPROC_CURRENCY_CODE
, ERDL.REVPROC_REVTRANS_EX_RATE
, ERDL.REVPROC_REVTRANS_RATE_TYPE
, ERDL.REVPROC_REVTRANS_RATE_DATE
, TO_NUMBER(DECODE(ERDL.REVTRANS_CURRENCY_CODE
, NULL
, DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.AMOUNT)
, -1 * ERDL.AMOUNT)
, DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.REVTRANS_AMOUNT)
, -1 * ERDL.REVTRANS_AMOUNT)))
, DECODE(ERDL.REVTRANS_CURRENCY_CODE
, NULL
, ERDL.REVPROC_CURRENCY_CODE
, ERDL.REVTRANS_CURRENCY_CODE)
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM PA_CUST_EVENT_RDL_ALL ERDL
, PA_EVENTS PE
, PA_DRAFT_REVENUES_ALL DR
, PA_IMPLEMENTATIONS_ALL IMP
, PA_EVENT_TYPES ET
WHERE ERDL.PROJECT_ID = DR.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND IMP.ORG_ID = DR.ORG_ID
AND ERDL.PROJECT_ID = PE.PROJECT_ID
AND NVL(ERDL.TASK_ID
, -1) = NVL(PE.TASK_ID
, -1)
AND ERDL.EVENT_NUM = PE.EVENT_NUM
AND DR.EVENT_ID IS NOT NULL /*
AND NVL(ERDL.AMOUNT
, 0) <> 0 */
AND PE.EVENT_TYPE = ET.EVENT_TYPE
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL
AND PE.EVENT_NUM_REVERSED IS NULL UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, TO_NUMBER(100||PE.EVENT_ID) LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, PE.EVENT_ID
, ERDL.LINE_NUM
, PE1.EVENT_ID
, ERDL1.LINE_NUM
, ERDL.PROJECT_ID
, ERDL.DRAFT_REVENUE_NUM
, ERDL.TASK_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PE.EVENT_ID
, ERDL.LINE_NUM
, TO_NUMBER(DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.AMOUNT)
, -1 * ERDL.AMOUNT))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ERDL.CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, ERDL.LINE_NUM_REVERSED
, 'REVENUE - EVENT REVENUE'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PE.EVENT_TYPE
, PE.ORGANIZATION_ID
, PE.ATTRIBUTE_CATEGORY
, PE.ATTRIBUTE1
, PE.ATTRIBUTE2
, PE.ATTRIBUTE3
, PE.ATTRIBUTE4
, PE.ATTRIBUTE5
, PE.ATTRIBUTE6
, PE.ATTRIBUTE7
, PE.ATTRIBUTE8
, PE.ATTRIBUTE9
, PE.ATTRIBUTE10
, PE.INVENTORY_ORG_ID
, PE.INVENTORY_ITEM_ID
, PE.REFERENCE1
, PE.REFERENCE2
, PE.REFERENCE3
, PE.REFERENCE4
, PE.REFERENCE5
, PE.REFERENCE6
, PE.REFERENCE7
, PE.REFERENCE8
, PE.REFERENCE9
, PE.REFERENCE10
, ERDL.REVPROC_CURRENCY_CODE
, ERDL.REVPROC_REVTRANS_EX_RATE
, ERDL.REVPROC_REVTRANS_RATE_TYPE
, ERDL.REVPROC_REVTRANS_RATE_DATE
, TO_NUMBER(DECODE(ERDL.REVTRANS_CURRENCY_CODE
, NULL
, DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.AMOUNT)
, -1 * ERDL.AMOUNT)
, DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.REVTRANS_AMOUNT)
, -1 * ERDL.REVTRANS_AMOUNT)))
, DECODE(ERDL.REVTRANS_CURRENCY_CODE
, NULL
, ERDL.REVPROC_CURRENCY_CODE
, ERDL.REVTRANS_CURRENCY_CODE)
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM PA_CUST_EVENT_RDL_ALL ERDL
, PA_CUST_EVENT_RDL_ALL ERDL1
, PA_EVENTS PE
, PA_EVENTS PE1
, PA_DRAFT_REVENUES_ALL DR
, PA_IMPLEMENTATIONS_ALL IMP
, PA_EVENT_TYPES ET
WHERE ERDL.PROJECT_ID = DR.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND IMP.ORG_ID = DR.ORG_ID
AND ERDL.PROJECT_ID = PE.PROJECT_ID
AND NVL(ERDL.TASK_ID
, -1) = NVL(PE.TASK_ID
, -1)
AND ERDL.EVENT_NUM = PE.EVENT_NUM
AND DR.EVENT_ID IS NOT NULL
AND PE.EVENT_TYPE = ET.EVENT_TYPE
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL
AND PE.EVENT_NUM_REVERSED IS NOT NULL
AND PE1.PROJECT_ID =PE.PROJECT_ID
AND PE1.EVENT_NUM = PE.EVENT_NUM_REVERSED
AND NVL(PE1.TASK_ID
, -1) = NVL(PE.TASK_ID
, -1)
AND PE1.PROJECT_ID = ERDL1.PROJECT_ID
AND ERDL1.EVENT_NUM = PE1.EVENT_NUM
AND NVL(ERDL1.TASK_ID
, -1) = NVL(PE1.TASK_ID
, -1) UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, 1 LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(DR.PROJECT_ID)
, TO_NUMBER(DR.DRAFT_REVENUE_NUM_CREDITED)
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER( -1 * DR.UNBILLED_RECEIVABLE_DR)
, DR.UNBILLED_CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'REVENUE - UBR'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PROJ.PROJFUNC_CURRENCY_CODE
, TO_NUMBER(DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, NULL
, DECODE(PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE
, NULL
, SUBSTR(TO_CHAR(DECODE(DR.REVTRANS_UBR_DR
, 0
, 0
, 1/(DR.UNBILLED_RECEIVABLE_DR/DR.REVTRANS_UBR_DR)))
, 1
, 30))))
, DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, NULL
, DECODE(PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE
, NULL
, 'USER'))
, DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, TO_DATE(NULL)
, DECODE(PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE
, TO_DATE(NULL)
, TRUNC(SYSDATE)))
, TO_NUMBER(DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, -1 * DR.UNBILLED_RECEIVABLE_DR
, -1 * DR.REVTRANS_UBR_DR))
, DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE)
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM PA_DRAFT_REVENUES_ALL DR
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PROJ
WHERE DR.UNBILLED_CODE_COMBINATION_ID IS NOT NULL
AND IMP.ORG_ID = DR.ORG_ID
AND PROJ.PROJECT_ID = DR.PROJECT_ID
AND DR.EVENT_ID IS NOT NULL /*
AND NVL(DR.UNBILLED_RECEIVABLE_DR
, 0) <> 0 */
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, 2 LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(DR.PROJECT_ID)
, TO_NUMBER(DR.DRAFT_REVENUE_NUM_CREDITED)
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(DR.UNEARNED_REVENUE_CR)
, TO_NUMBER(NULL)
, DR.UNEARNED_CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'REVENUE - UER'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PROJ.PROJFUNC_CURRENCY_CODE
, TO_NUMBER(DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, NULL
, DECODE(PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE
, NULL
, SUBSTR(TO_CHAR(DECODE(DR.REVTRANS_UER_CR
, 0
, 0
, 1/(DR.UNEARNED_REVENUE_CR/DR.REVTRANS_UER_CR)))
, 1
, 30))))
, DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, NULL
, DECODE(PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE
, NULL
, 'USER'))
, DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, TO_DATE(NULL)
, DECODE(PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE
, TO_DATE(NULL)
, TRUNC(SYSDATE)))
, TO_NUMBER(DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, DR.UNEARNED_REVENUE_CR
, DR.REVTRANS_UER_CR))
, DECODE(DR.REVTRANS_CURRENCY_CODE
, NULL
, PROJ.PROJFUNC_CURRENCY_CODE
, DR.REVTRANS_CURRENCY_CODE)
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM PA_DRAFT_REVENUES_ALL DR
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PROJ
WHERE DR.UNEARNED_CODE_COMBINATION_ID IS NOT NULL
AND DR.EVENT_ID IS NOT NULL
AND IMP.ORG_ID = DR.ORG_ID
AND PROJ.PROJECT_ID = DR.PROJECT_ID /*
AND NVL(DR.UNEARNED_REVENUE_CR
, 0) <> 0 */
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, 3 LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(DR.PROJECT_ID)
, TO_NUMBER(DR.DRAFT_REVENUE_NUM_CREDITED)
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ABS(DR.REALIZED_GAINS_AMOUNT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DR.REALIZED_GAINS_CCID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'REVENUE - REALIZED GAINS'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PROJ.PROJFUNC_CURRENCY_CODE
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, ABS(DR.REALIZED_GAINS_AMOUNT)
, PROJ.PROJFUNC_CURRENCY_CODE
, DECODE(DR2.EVENT_ID
, NULL
, 'Y'
, 'N')
, TO_NUMBER(DECODE(SIGN(DR2.REALIZED_GAINS_AMOUNT)
, -1
, DR.REALIZED_GAINS_CCID
, NULL))
, TO_NUMBER(DECODE(SIGN(DR2.REALIZED_GAINS_AMOUNT)
, 1
, DR.REALIZED_GAINS_CCID
, NULL))
, DECODE(SIGN(DR2.REALIZED_GAINS_AMOUNT)
, 1
, 'REVENUE'
, NULL)
, DECODE(SIGN(DR2.REALIZED_GAINS_AMOUNT)
, -1
, 'REVENUE'
, NULL)
FROM PA_DRAFT_REVENUES_ALL DR
, PA_DRAFT_REVENUES_ALL DR2
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PROJ
WHERE NVL(DR.REALIZED_GAINS_CCID
, 0) > 0
AND DR.EVENT_ID IS NOT NULL
AND IMP.ORG_ID = DR.ORG_ID
AND PROJ.PROJECT_ID = DR.PROJECT_ID
AND NVL(DR.REALIZED_GAINS_AMOUNT
, 0) <> 0
AND DR2.PROJECT_ID = DR.PROJECT_ID
AND DR2.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM_CREDITED
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL UNION ALL SELECT 275
, 'REVENUE'
, DR.EVENT_ID
, 4 LINE_NUMBER
, DR.DRAFT_REVENUE_NUM_CREDITED
, IMP.SET_OF_BOOKS_ID
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(DR.PROJECT_ID)
, TO_NUMBER(DR.DRAFT_REVENUE_NUM_CREDITED)
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ABS(DR.REALIZED_LOSSES_AMOUNT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DR.REALIZED_LOSSES_CCID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'REVENUE - REALIZED LOSSES'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PROJ.PROJFUNC_CURRENCY_CODE
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, ABS(DR.REALIZED_LOSSES_AMOUNT)
, PROJ.PROJFUNC_CURRENCY_CODE
, DECODE(DR2.EVENT_ID
, NULL
, 'Y'
, 'N')
, TO_NUMBER(DECODE(SIGN(DR2.REALIZED_LOSSES_AMOUNT)
, -1
, DR.REALIZED_GAINS_CCID
, NULL))
, TO_NUMBER(DECODE(SIGN(DR2.REALIZED_LOSSES_AMOUNT)
, 1
, DR.REALIZED_GAINS_CCID
, NULL))
, DECODE(SIGN(DR2.REALIZED_LOSSES_AMOUNT)
, 1
, 'REVENUE'
, NULL)
, DECODE(SIGN(DR2.REALIZED_LOSSES_AMOUNT)
, -1
, 'REVENUE'
, NULL)
FROM PA_DRAFT_REVENUES_ALL DR
, PA_DRAFT_REVENUES_ALL DR2
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PROJ
WHERE NVL(DR.REALIZED_LOSSES_CCID
, 0) > 0
AND DR.EVENT_ID IS NOT NULL
AND IMP.ORG_ID = DR.ORG_ID
AND PROJ.PROJECT_ID = DR.PROJECT_ID
AND NVL(DR.REALIZED_LOSSES_AMOUNT
, 0) <> 0
AND DR2.PROJECT_ID = DR.PROJECT_ID
AND DR2.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM_CREDITED
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL

Columns