eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_XLA_REVENUE_LINES_V

Source

View Text - Preformatted


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, to_number(NULL), to_number(NULL), 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(CRDL.AMOUNT), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), CRDL.CODE_COMBINATION_ID, TO_NUMBER(NULL), NULL, 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, CRDL.AMOUNT, CRDL.REVTRANS_AMOUNT)), DECODE(CRDL.REVTRANS_CURRENCY_CODE, NULL, CRDL.REVPROC_CURRENCY_CODE, CRDL.REVTRANS_CURRENCY_CODE), 'N', to_number(null), to_number(null), null, null FROM PA_CUST_REV_DIST_LINES_ALL CRDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_DRAFT_REVENUES_ALL DR, 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 DR.DRAFT_REVENUE_NUM_CREDITED IS NULL 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, NULL, NULL, 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), ERDL.AMOUNT)), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), ERDL.CODE_COMBINATION_ID, TO_NUMBER(NULL), NULL, '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), ERDL.AMOUNT), DECODE(ET.EVENT_TYPE_CLASSIFICATION, 'WRITE OFF', abs(ERDL.REVTRANS_AMOUNT), NVL(ERDL.REVTRANS_AMOUNT,0)))), 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 NULL 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, NULL, NULL, 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.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,DR.UNBILLED_RECEIVABLE_DR, 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 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, NULL, NULL, 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.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, -1 * DR.UNEARNED_REVENUE_CR, -1 * 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 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, NULL, NULL, DR.PROJECT_ID, DR.DRAFT_REVENUE_NUM, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), 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), 0, DR.REVTRANS_CURRENCY_CODE, 'N', to_number(DECODE(sign(DR.REALIZED_GAINS_AMOUNT), -1, DR.REALIZED_GAINS_CCID, null)), to_number(DECODE(sign(DR.REALIZED_GAINS_AMOUNT), 1, DR.REALIZED_GAINS_CCID, null)), DECODE(sign(DR.REALIZED_GAINS_AMOUNT), 1, 'Revenue', null), DECODE(sign(DR.REALIZED_GAINS_AMOUNT), -1, 'Revenue', null) FROM PA_DRAFT_REVENUES_ALL DR, 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 DR.DRAFT_REVENUE_NUM_CREDITED IS 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, NULL, NULL, DR.PROJECT_ID, DR.DRAFT_REVENUE_NUM, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), 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), 0, DR.REVTRANS_CURRENCY_CODE, 'N', to_number(DECODE(sign(DR.REALIZED_LOSSES_AMOUNT), -1, DR.REALIZED_GAINS_CCID, null)), to_number(DECODE(sign(DR.REALIZED_LOSSES_AMOUNT), 1, DR.REALIZED_GAINS_CCID, null)), DECODE(sign(DR.REALIZED_LOSSES_AMOUNT), 1, 'Revenue', null), DECODE(sign(DR.REALIZED_LOSSES_AMOUNT), -1, 'Revenue', null) FROM PA_DRAFT_REVENUES_ALL DR, 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 DR.DRAFT_REVENUE_NUM_CREDITED IS NULL

View Text - HTML Formatted

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
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 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(CRDL.AMOUNT)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CRDL.CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, NULL
, 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
, CRDL.AMOUNT
, CRDL.REVTRANS_AMOUNT))
, DECODE(CRDL.REVTRANS_CURRENCY_CODE
, NULL
, CRDL.REVPROC_CURRENCY_CODE
, CRDL.REVTRANS_CURRENCY_CODE)
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_DRAFT_REVENUES_ALL DR
, 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 DR.DRAFT_REVENUE_NUM_CREDITED IS NULL 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
, NULL
, NULL
, 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)
, ERDL.AMOUNT))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ERDL.CODE_COMBINATION_ID
, TO_NUMBER(NULL)
, NULL
, '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)
, ERDL.AMOUNT)
, DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, ABS(ERDL.REVTRANS_AMOUNT)
, NVL(ERDL.REVTRANS_AMOUNT
, 0))))
, 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 NULL 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
, NULL
, NULL
, 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.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
, DR.UNBILLED_RECEIVABLE_DR
, 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 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
, NULL
, NULL
, 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.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
, -1 * DR.UNEARNED_REVENUE_CR
, -1 * 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 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
, NULL
, NULL
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 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)
, 0
, DR.REVTRANS_CURRENCY_CODE
, 'N'
, TO_NUMBER(DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, DR.REALIZED_GAINS_CCID
, NULL))
, TO_NUMBER(DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, 1
, DR.REALIZED_GAINS_CCID
, NULL))
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, 1
, 'REVENUE'
, NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, 'REVENUE'
, NULL)
FROM PA_DRAFT_REVENUES_ALL DR
, 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 DR.DRAFT_REVENUE_NUM_CREDITED IS 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
, NULL
, NULL
, DR.PROJECT_ID
, DR.DRAFT_REVENUE_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 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)
, 0
, DR.REVTRANS_CURRENCY_CODE
, 'N'
, TO_NUMBER(DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, DR.REALIZED_GAINS_CCID
, NULL))
, TO_NUMBER(DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, 1
, DR.REALIZED_GAINS_CCID
, NULL))
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, 1
, 'REVENUE'
, NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, 'REVENUE'
, NULL)
FROM PA_DRAFT_REVENUES_ALL DR
, 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 DR.DRAFT_REVENUE_NUM_CREDITED IS NULL