View: PA_GL_REV_XFER_AUDIT_REP_MRC_V

View Text

SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'ERDL' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, DR_MC.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, DR_MC.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, DR_MC.TRANSFERRED_DATE TRANSFERRED_DATE
, DR.GL_DATE GL_DATE
, PE.EVENT_TYPE TRANSACTION_TYPE
, PE.COMPLETION_DATE TRANSACTION_DATE
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, RDL.LINE_NUM RDL_LINE_NUM
, RDL.EVENT_NUM RDL_EVENT_NUM
, TO_NUMBER(NULL) DEBIT_AMOUNT
, RDL.AMOUNT CREDIT_AMOUNT
, PRJ.ORG_ID ORG_ID
FROM PA_PROJECTS PRJ
, PA_TASKS TASK
, PA_EVENTS PE
, PA_CUST_EVENT_RDL_ALL_MRC_V2 RDL
, PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL DR_MC
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = PE.PROJECT_ID AND DR_MC.TRANSFER_STATUS_CODE||'' = 'A' AND RDL.PROJECT_ID = DR_MC.PROJECT_ID AND RDL.DRAFT_REVENUE_NUM = DR_MC.DRAFT_REVENUE_NUM AND RDL.PROJECT_ID = PE.PROJECT_ID AND NVL(RDL.TASK_ID
, -1) = NVL(PE.TASK_ID
, -1) AND RDL.EVENT_NUM = PE.EVENT_NUM AND TASK.TASK_ID (+) = PE.TASK_ID
AND DR_MC.PROJECT_ID = DR.PROJECT_ID
AND DR_MC.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM
AND AEL.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = DR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - EVENT REVENUE'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = RDL.LINE_NUM
AND XDL.ACCOUNTING_LINE_CODE IN('PJB_EVENT_REV'
, 'PJB_EVENT_WO_REV') UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RDL' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, DR_MC.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, DR_MC.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, DR_MC.TRANSFERRED_DATE TRANSFERRED_DATE
, DR.GL_DATE GL_DATE
, EI.EXPENDITURE_TYPE TRANSACTION_TYPE
, EI.EXPENDITURE_ITEM_DATE TRANSACTION_DATE
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, DECODE(EMP.FULL_NAME
, NULL
, ORG.NAME
, EMP.FULL_NAME ) EMP_OR_ORG_NAME
, RDL.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, RDL.LINE_NUM RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, TO_NUMBER(NULL) DEBIT_AMOUNT
, RDL.AMOUNT CREDIT_AMOUNT
, PRJ.ORG_ID ORG_ID
FROM HR_ORGANIZATION_UNITS ORG
, PER_PEOPLE_F EMP
, PA_TASKS TASK
, PA_PROJECTS PRJ
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, PA_CUST_RDL_ALL_MRC_V2 RDL
, PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL DR_MC
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE TASK.PROJECT_ID = PRJ.PROJECT_ID AND EI.TASK_ID = TASK.TASK_ID AND DR_MC.TRANSFER_STATUS_CODE||'' = 'A' AND RDL.PROJECT_ID = DR_MC.PROJECT_ID AND RDL.DRAFT_REVENUE_NUM = DR_MC.DRAFT_REVENUE_NUM AND RDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID AND EI.EXPENDITURE_ID = EXP.EXPENDITURE_ID
AND AEL.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = DR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = DR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - NORMAL REVENUE'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RDL.EXPENDITURE_ITEM_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = RDL.LINE_NUM
AND XDL.ACCOUNTING_LINE_CODE IN ('PJB_LABOR_REV'
, 'PJB_ER_REV'
, 'PJB_USG_REV'
, 'PJB_BTC_REV'
, 'PJB_WIP_REV'
, 'PJB_VI_REV'
, 'PJB_PJ_REV'
, 'PJB_INV_REV')
AND DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID) = ORG.ORGANIZATION_ID
AND EXP.INCURRED_BY_PERSON_ID = EMP.PERSON_ID (+)
AND ( EI.EXPENDITURE_ITEM_DATE BETWEEN NVL(EMP.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE)
AND NVL(EMP.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE ) )
AND DR_MC.PROJECT_ID = DR.PROJECT_ID
AND DR_MC.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'UER' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, DR_MC.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, DR_MC.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, DR_MC.TRANSFERRED_DATE TRANSFERRED_DATE
, DR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*DR_MC.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
, PRJ.ORG_ID ORG_ID
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL DR_MC
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = DR_MC.PROJECT_ID
AND DR_MC.TRANSFER_STATUS_CODE||'' = 'A'
AND DR_MC.PROJECT_ID = DR.PROJECT_ID
AND DR_MC.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM
AND AEL.CODE_COMBINATION_ID = DR.UNEARNED_CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = DR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = DR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - UER'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = DR.DRAFT_REVENUE_NUM
AND XDL.ACCOUNTING_LINE_CODE = 'PJB_UER' UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'UBR' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, DR_MC.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, DR_MC.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, DR_MC.TRANSFERRED_DATE TRANSFERRED_DATE
, DR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, DR_MC.UNBILLED_RECEIVABLE_DR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
, PRJ.ORG_ID ORG_ID
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL DR_MC
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = DR_MC.PROJECT_ID AND DR_MC.TRANSFER_STATUS_CODE||'' = 'A' AND DR_MC.PROJECT_ID = PRJ.PROJECT_ID
AND DR_MC.PROJECT_ID = DR.PROJECT_ID
AND DR_MC.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM
AND AEL.CODE_COMBINATION_ID = DR.UNBILLED_CODE_COMBINATION_ID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = DR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = DR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - UBR'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = DR.DRAFT_REVENUE_NUM
AND XDL.ACCOUNTING_LINE_CODE = 'PJB_UBR' UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RLZD-GAIN' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, DR_MC.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, DR_MC.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, DR_MC.TRANSFERRED_DATE TRANSFERRED_DATE
, DR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*DR_MC.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
, PRJ.ORG_ID ORG_ID
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL DR_MC
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = DR_MC.PROJECT_ID
AND DR_MC.TRANSFER_STATUS_CODE||'' = 'A'
AND AEL.CODE_COMBINATION_ID = DR.REALIZED_GAINS_CCID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = DR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = DR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - REALIZED GAINS'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = DR.DRAFT_REVENUE_NUM
AND XDL.ACCOUNTING_LINE_CODE = 'PJB_REALIZED_GAINS'
AND DR_MC.PROJECT_ID = DR.PROJECT_ID
AND DR_MC.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM UNION ALL SELECT DISTINCT AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, 'RLZD-LOSS' LINE_TYPE
, AEH.PERIOD_NAME PERIOD_NAME
, DR_MC.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, DR_MC.DRAFT_REVENUE_NUM DRAFT_REV_NUMBER
, DR_MC.TRANSFERRED_DATE TRANSFERRED_DATE
, DR.GL_DATE GL_DATE
, NULL TRANSACTION_TYPE
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) TASK_ID
, NULL TASK_NUMBER
, NULL EMP_OR_ORG_NAME
, TO_NUMBER(NULL) EXPENDITURE_ITEM_ID
, TO_NUMBER(NULL) RDL_LINE_NUM
, TO_NUMBER(NULL) RDL_EVENT_NUM
, -1*DR_MC.UNEARNED_REVENUE_CR DEBIT_AMOUNT
, TO_NUMBER(NULL) CREDIT_AMOUNT
, PRJ.ORG_ID ORG_ID
FROM PA_PROJECTS PRJ
, PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL DR_MC
, XLA_AE_LINES AEL
, XLA_AE_HEADERS AEH
, XLA_DISTRIBUTION_LINKS XDL
WHERE PRJ.PROJECT_ID = DR_MC.PROJECT_ID
AND DR_MC.TRANSFER_STATUS_CODE||'' = 'A'
AND DR_MC.PROJECT_ID = DR.PROJECT_ID
AND DR_MC.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM
AND AEL.CODE_COMBINATION_ID = DR.REALIZED_LOSSES_CCID(+)
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.EVENT_ID = DR.EVENT_ID
AND AEH.APPLICATION_ID = 275
AND XDL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = AEL.AE_LINE_NUM
AND XDL.EVENT_ID = DR.EVENT_ID
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'REVENUE - REALIZED LOSSES'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = DR.PROJECT_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = DR.DRAFT_REVENUE_NUM
AND XDL.ACCOUNTING_LINE_CODE = 'PJB_REALIZED_LOSSES'

Columns