[Home] [Help]
| Product: | PA - Projects |
|---|---|
| Description: | View for Cost Audit by GL Account showing the Cost Distribution Lines and the corresponding entries in Oracle General Ledger. This view shows the cost transactions in Oracle Projects and the corresponding GL Journal Entry batch numbers and |
| Implementation/DBA Data: | Not implemented in this database |
SELECT EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CDL.LINE_NUM CDL_LINE_NUM
, JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PRJ.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, TASK.TASK_ID TASK_ID
, TASK.TASK_NUMBER TASK_NUMBER
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, DECODE(EMP.FULL_NAME
, NULL
, HR.NAME
, EMP.FULL_NAME ) EMP_OR_ORG_NAME
, JEB.NAME JE_BATCH_NAME
, REPLACE(JEB.NAME
, JES.USER_JE_SOURCE_NAME || ' ') JE_REP_BATCH_NAME
, JEB.JE_BATCH_ID JE_BATCH_ID
, JEH.PERIOD_NAME PERIOD_NAME
, JEL.JE_LINE_NUM JE_LINE_NUMBER
, CDL.GL_DATE GL_DATE
, CDL.TRANSFERRED_DATE TRANSFERRED_DATE
, DECODE(JEL.REFERENCE_3
, 'LIABILITY'
, TO_NUMBER(NULL)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'BTC'
, DECODE(CDL.LINE_TYPE
, 'R'
, CDL.ACCT_BURDENED_COST
, CDL.ACCT_RAW_COST)
, CDL.ACCT_RAW_COST)) GL_DR_AMOUNT
, DECODE(JEL.REFERENCE_3
, 'COST'
, TO_NUMBER(NULL)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'BTC'
, DECODE(CDL.LINE_TYPE
, 'R'
, CDL.ACCT_BURDENED_COST
, DECODE(CDL.LINE_TYPE
, 'C'
, -CDL.ACCT_RAW_COST
, CDL.ACCT_RAW_COST))
, DECODE(CDL.LINE_TYPE
, 'C'
, -CDL.ACCT_RAW_COST
, CDL.ACCT_RAW_COST))) GL_CR_AMOUNT
, CDL.CR_CODE_COMBINATION_ID CR_CODE_COMBINATION_ID
, PRJ.ORG_ID
FROM GL_JE_SOURCES JES
, HR_ORGANIZATION_UNITS HR
, PER_PEOPLE_F EMP
, PA_PROJECTS_ALL PRJ
, PA_TASKS TASK
, PA_EXPENDITURES_MRC_V2 EXP
, PA_EXPENDITURE_ITEMS_MRC_V2 EI
, PA_COST_DIST_LINES_MRC_V2 CDL
, GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
, PA_SYSTEM_LINKAGES SYSLINK
WHERE PRJ.PROJECT_ID = TASK.PROJECT_ID
AND EI.TASK_ID = TASK.TASK_ID
AND JEH.JE_CATEGORY = DECODE(CDL.LINE_TYPE
, 'R'
, SYSLINK.COST_JE_CATEGORY_NAME
, JEH.JE_CATEGORY)
AND EI.SYSTEM_LINKAGE_FUNCTION = SYSLINK.FUNCTION
AND CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CDL.TRANSFER_STATUS_CODE||'' = 'A'
AND CDL.BATCH_NAME = JEL.REFERENCE_1
AND ((CDL.DR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'COST') OR (CDL.CR_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'LIABILITY'))
AND JEL.REFERENCE_3 IN ( 'COST'
, 'LIABILITY')
AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEH.REVERSED_JE_HEADER_ID IS NULL
AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_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 JES.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND DECODE(EI.OVERRIDE_TO_ORGANIZATION_ID
, NULL
, EXP.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID) = HR.ORGANIZATION_ID