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.

[Home] [Help]

View: PA_PROG_ACT_BY_PERIOD_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT PPR.PROJECT_ID
, ELEM.PARENT_STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, AS_OF_DATE
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'T'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_EFFORT(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PPR.PROG_PA_PERIOD_NAME
, PPR.TXN_CURRENCY_CODE
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'P'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'F'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'T'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'P'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, PPR.PROG_PA_PERIOD_NAME
, 'P'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'F'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, DECODE(PTAV.RESOURCE_ASSIGNMENT_ID
, NULL
, PA_TASK_ASSIGNMENT_UTILS.GET_TASK_LEVEL_RECORD(PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID)
, NULL)
, PTAV.RESOURCE_LIST_MEMBER_ID
FROM PA_PROGRESS_ROLLUP PPR
, PA_TASK_ASSIGNMENTS_V PTAV
, PA_PROJ_ELEMENT_VERSIONS ELEM
WHERE NVL(PA_PROGRESS_UTILS.GET_TIME_PHASE_PERIOD(PPR.STRUCTURE_VERSION_ID
, PPR.PROJECT_ID)
, 'N') = 'P' AND PPR.PROJECT_ID = ELEM.PROJECT_ID AND PPR.OBJECT_VERSION_ID = ELEM.ELEMENT_VERSION_ID AND PTAV.PROJECT_ID (+) = PPR.PROJECT_ID AND PTAV.TASK_ID (+) = PPR.PROJ_ELEMENT_ID AND PTAV.RESOURCE_LIST_MEMBER_ID (+) = PPR.OBJECT_ID AND PPR.OBJECT_TYPE <> 'PA_DELIVERABLES' AND PTAV.STRUCTURE_VERSION_ID (+) = DECODE(PPR.STRUCTURE_VERSION_ID
, NULL
, PA_PROJ_ELEMENTS_UTILS.LATEST_PUBLISHED_VER_ID(PPR.PROJECT_ID
, 'WORKPLAN')
, PPR.STRUCTURE_VERSION_ID)
AND PPR.AS_OF_DATE = (SELECT MAX(AS_OF_DATE)
FROM PA_PROGRESS_ROLLUP PPR2
WHERE PPR2.PROJECT_ID = PPR.PROJECT_ID
AND PPR2.OBJECT_ID = PPR.OBJECT_ID
AND PPR2.PROJ_ELEMENT_ID = PPR.PROJ_ELEMENT_ID
AND PPR2.STRUCTURE_TYPE = PPR.STRUCTURE_TYPE
AND NVL(PPR2.STRUCTURE_VERSION_ID
, 0) = NVL(PPR.STRUCTURE_VERSION_ID
, 0)
AND PPR2.OBJECT_TYPE = PPR.OBJECT_TYPE
AND PPR2.PROG_PA_PERIOD_NAME = PPR.PROG_PA_PERIOD_NAME
AND NOT EXISTS ( SELECT 'X'
FROM PA_PERCENT_COMPLETES
WHERE PROJECT_ID = PPR2.PROJECT_ID
AND OBJECT_ID = PPR2.OBJECT_ID
AND TASK_ID = PPR2.PROJ_ELEMENT_ID
AND OBJECT_TYPE= PPR2.OBJECT_TYPE
AND CURRENT_FLAG = 'N'
AND PUBLISHED_FLAG = 'N'
AND DATE_COMPUTED = PPR2.AS_OF_DATE
AND STRUCTURE_TYPE = 'WORKPLAN' ) ) AND PPR.STRUCTURE_TYPE = 'WORKPLAN' UNION SELECT PPR.PROJECT_ID
, ELEM.PARENT_STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, AS_OF_DATE
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'T'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_EFFORT(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PPR.PROG_GL_PERIOD_NAME
, PPR.TXN_CURRENCY_CODE
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'P'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'F'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'T'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'P'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, PPR.PROG_GL_PERIOD_NAME
, 'G'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'F'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, DECODE(PTAV.RESOURCE_ASSIGNMENT_ID
, NULL
, PA_TASK_ASSIGNMENT_UTILS.GET_TASK_LEVEL_RECORD(PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID)
, NULL)
, PTAV.RESOURCE_LIST_MEMBER_ID
FROM PA_PROGRESS_ROLLUP PPR
, PA_TASK_ASSIGNMENTS_V PTAV
, PA_PROJ_ELEMENT_VERSIONS ELEM
WHERE NVL(PA_PROGRESS_UTILS.GET_TIME_PHASE_PERIOD(PPR.STRUCTURE_VERSION_ID
, PPR.PROJECT_ID)
, 'N') = 'G' AND PPR.PROJECT_ID = ELEM.PROJECT_ID AND PPR.OBJECT_VERSION_ID = ELEM.ELEMENT_VERSION_ID
AND PTAV.PROJECT_ID (+) = PPR.PROJECT_ID
AND PTAV.TASK_ID (+) = PPR.PROJ_ELEMENT_ID AND PTAV.RESOURCE_LIST_MEMBER_ID (+) = PPR.OBJECT_ID AND PPR.OBJECT_TYPE <> 'PA_DELIVERABLES'
AND PTAV.STRUCTURE_VERSION_ID (+) = DECODE(PPR.STRUCTURE_VERSION_ID
, NULL
, PA_PROJ_ELEMENTS_UTILS.LATEST_PUBLISHED_VER_ID(PPR.PROJECT_ID
, 'WORKPLAN')
, PPR.STRUCTURE_VERSION_ID)
AND PPR.AS_OF_DATE = (SELECT MAX(AS_OF_DATE)
FROM PA_PROGRESS_ROLLUP PPR2
WHERE PPR2.PROJECT_ID = PPR.PROJECT_ID
AND PPR2.OBJECT_ID = PPR.OBJECT_ID
AND PPR2.PROJ_ELEMENT_ID = PPR.PROJ_ELEMENT_ID
AND PPR2.STRUCTURE_TYPE = PPR.STRUCTURE_TYPE
AND NVL(PPR2.STRUCTURE_VERSION_ID
, 0) = NVL(PPR.STRUCTURE_VERSION_ID
, 0)
AND PPR2.OBJECT_TYPE = PPR.OBJECT_TYPE
AND PPR2.PROG_GL_PERIOD_NAME = PPR.PROG_GL_PERIOD_NAME
AND NOT EXISTS ( SELECT 'X'
FROM PA_PERCENT_COMPLETES
WHERE PROJECT_ID = PPR2.PROJECT_ID
AND OBJECT_ID = PPR2.OBJECT_ID
AND TASK_ID = PPR2.PROJ_ELEMENT_ID
AND OBJECT_TYPE= PPR2.OBJECT_TYPE
AND CURRENT_FLAG = 'N'
AND PUBLISHED_FLAG = 'N'
AND DATE_COMPUTED = PPR2.AS_OF_DATE
AND STRUCTURE_TYPE = 'WORKPLAN' ) ) AND PPR.STRUCTURE_TYPE = 'WORKPLAN' UNION SELECT PPR.PROJECT_ID
, ELEM.PARENT_STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, AS_OF_DATE
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'T'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_EFFORT(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, TO_CHAR(NULL)
, PPR.TXN_CURRENCY_CODE
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'P'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_COST(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'F'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'T'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'P'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_INCREMENTAL_ACTUAL_RAWCOST(AS_OF_DATE
, TO_CHAR(NULL)
, 'N'
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, 'F'
, PPR.STRUCTURE_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, DECODE(PTAV.RESOURCE_ASSIGNMENT_ID
, NULL
, PA_TASK_ASSIGNMENT_UTILS.GET_TASK_LEVEL_RECORD(PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID)
, NULL)
, PTAV.RESOURCE_LIST_MEMBER_ID
FROM PA_PROGRESS_ROLLUP PPR
, PA_TASK_ASSIGNMENTS_V PTAV
, PA_PROJ_ELEMENT_VERSIONS ELEM
WHERE NVL(PA_PROGRESS_UTILS.GET_TIME_PHASE_PERIOD(PPR.STRUCTURE_VERSION_ID
, PPR.PROJECT_ID)
, 'N') = 'N' AND PPR.PROJECT_ID = ELEM.PROJECT_ID AND PPR.OBJECT_VERSION_ID = ELEM.ELEMENT_VERSION_ID
AND PTAV.PROJECT_ID (+) = PPR.PROJECT_ID
AND PTAV.TASK_ID (+) = PPR.PROJ_ELEMENT_ID AND PTAV.RESOURCE_LIST_MEMBER_ID (+) = PPR.OBJECT_ID AND PPR.OBJECT_TYPE <> 'PA_DELIVERABLES'
AND PTAV.STRUCTURE_VERSION_ID (+) = DECODE(PPR.STRUCTURE_VERSION_ID
, NULL
, PA_PROJ_ELEMENTS_UTILS.LATEST_PUBLISHED_VER_ID(PPR.PROJECT_ID
, 'WORKPLAN')
, PPR.STRUCTURE_VERSION_ID)
AND PPR.AS_OF_DATE = (SELECT MAX(AS_OF_DATE)
FROM PA_PROGRESS_ROLLUP PPR2
WHERE PPR2.PROJECT_ID = PPR.PROJECT_ID
AND PPR2.OBJECT_ID = PPR.OBJECT_ID
AND PPR2.PROJ_ELEMENT_ID = PPR.PROJ_ELEMENT_ID
AND PPR2.STRUCTURE_TYPE = PPR.STRUCTURE_TYPE
AND PPR2.OBJECT_TYPE = PPR.OBJECT_TYPE
AND NVL(PPR2.STRUCTURE_VERSION_ID
, 0) = NVL(PPR.STRUCTURE_VERSION_ID
, 0)
AND NOT EXISTS ( SELECT 'X'
FROM PA_PERCENT_COMPLETES
WHERE PROJECT_ID = PPR2.PROJECT_ID
AND OBJECT_ID = PPR2.OBJECT_ID
AND TASK_ID = PPR2.PROJ_ELEMENT_ID
AND OBJECT_TYPE= PPR2.OBJECT_TYPE
AND CURRENT_FLAG = 'N'
AND PUBLISHED_FLAG = 'N'
AND DATE_COMPUTED = PPR2.AS_OF_DATE
AND STRUCTURE_TYPE = 'WORKPLAN' ) ) AND PPR.STRUCTURE_TYPE = 'WORKPLAN'

Columns

Name
PROJECT_ID
STRUCTURE_VERSION_ID
TASK_ID
RESOURCE_ASSIGNMENT_ID
AS_OF_DATE
ACTUAL_COST
ACTUAL_EFFORT
PERIOD_NAME
TXN_CURRENCY_CODE
ACTUAL_COST_PC
ACTUAL_COST_FC
ACTUAL_RAWCOST
ACTUAL_RAWCOST_PC
ACTUAL_RAWCOST_FC
HIDDEN_RES_ASSGN_ID
RESOURCE_LIST_MEMBER_ID