View: PA_CAPITAL_PROJECTS_WBS_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_CAPITAL_PROJECTS_WBS_V

View Text

SELECT TASKS.PROJECT_ID
, TASKS.TASK_ID
, 'X' TASK_NUMBER
, 'X' TASK_NAME
, 0 PARENT_TASK_ID
, 0 WBS_LEVEL
, 0 CAPITALIZED_COST
, 0 RETIRED_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'N'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(ACCUM.TOT_BILLABLE_RAW_COST
, 0) + NVL(ACCUM.I_TOT_BILLABLE_RAW_COST
, 0)
, 'B'
, NVL(ACCUM.TOT_BILLABLE_BURDENED_COST
, 0)+ NVL(ACCUM.I_TOT_BILLABLE_BURDENED_COST
, 0))
, 0)) CAPITALIZABLE_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'Y'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(ACCUM.TOT_BILLABLE_RAW_COST
, 0) + NVL(ACCUM.I_TOT_BILLABLE_RAW_COST
, 0)
, 'B'
, NVL(ACCUM.TOT_BILLABLE_BURDENED_COST
, 0) + NVL(ACCUM.I_TOT_BILLABLE_BURDENED_COST
, 0))
, 0)) RETIRABLE_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'N'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(ACCUM.TOT_RAW_COST
, 0) + NVL(ACCUM.I_TOT_RAW_COST
, 0)
, 'B'
, NVL(ACCUM.TOT_BURDENED_COST
, 0) + NVL(ACCUM.I_TOT_BURDENED_COST
, 0))
, 0)) TOTAL_CAP_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'Y'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(ACCUM.TOT_RAW_COST
, 0) + NVL(ACCUM.I_TOT_RAW_COST
, 0)
, 'B'
, NVL(ACCUM.TOT_BURDENED_COST
, 0) + NVL(ACCUM.I_TOT_BURDENED_COST
, 0))
, 0)) TOTAL_RET_COST
FROM PA_TXN_ACCUM ACCUM
, PA_TASKS TASKS
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_TYPES_ALL PTYPE
WHERE ACCUM.TASK_ID = TASKS.TASK_ID
AND TASKS.PROJECT_ID = ACCUM.PROJECT_ID
AND PROJ.PROJECT_ID = ACCUM.PROJECT_ID
AND PROJ.PJI_SOURCE_FLAG IS NULL
AND PROJ.PROJECT_TYPE = PTYPE.PROJECT_TYPE
AND PROJ.TEMPLATE_FLAG <> 'Y'
AND PTYPE.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND PTYPE.ORG_ID = PROJ.ORG_ID UNION ALL SELECT TASKS.PROJECT_ID
, TASKS.TASK_ID
, 'X' TASK_NUMBER
, 'X' TASK_NAME
, 0 PARENT_TASK_ID
, 0 WBS_LEVEL
, 0 CAPITALIZED_COST
, 0 RETIRED_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'N'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(FACT.CAPITALIZABLE_RAW_COST
, 0)
, 'B'
, NVL(FACT.CAPITALIZABLE_BRDN_COST
, 0))
, 0)) CAPITALIZABLE_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'Y'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(FACT.BILL_RAW_COST
, 0)
, 'B'
, NVL(FACT.BILL_BRDN_COST
, 0))
, 0)) RETIRABLE_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'N'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(FACT.RAW_COST
, 0)
, 'B'
, NVL(FACT.BRDN_COST
, 0))
, 0)) TOTAL_CAP_COST
, (DECODE(NVL(TASKS.RETIREMENT_COST_FLAG
, 'N')
, 'Y'
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, NVL(FACT.RAW_COST
, 0)
, 'B'
, NVL(FACT.BRDN_COST
, 0))
, 0)) TOTAL_RET_COST
FROM PJI_FP_XBS_ACCUM_F FACT
, PA_TASKS TASKS
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_TYPES_ALL PTYPE
WHERE FACT.PROJECT_ELEMENT_ID = TASKS.TASK_ID
AND PROJ.PROJECT_ID = FACT.PROJECT_ID
AND PROJ.PJI_SOURCE_FLAG = 'Y'
AND FACT.CALENDAR_TYPE = 'A'
AND FACT.PERIOD_TYPE_ID = 2048
AND FACT.TIME_ID = -1
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND FACT.WBS_ROLLUP_FLAG = 'N' AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 8) = 8
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.PLAN_VERSION_ID = -1
AND PROJ.PROJECT_TYPE = PTYPE.PROJECT_TYPE
AND PROJ.TEMPLATE_FLAG <> 'Y'
AND PTYPE.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND PTYPE.ORG_ID = PROJ.ORG_ID

Columns