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_ADJ_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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