View: PA_CAPITAL_PROJECTS_BASE_V

Product:	PA - Projects
Description:	R10SC Only -Base view that shows summary capital amounts for a project
Implementation/DBA Data:	APPS.PA_CAPITAL_PROJECTS_BASE_V

View Text

SELECT PP.PROJECT_ID
, PP.NAME
, PP.SEGMENT1
, PP.PROJECT_STATUS_CODE
, PP.PROJECT_TYPE
, PPT.INTERFACE_COMPLETE_ASSET_FLAG
, PPT.INTERFACE_ASSET_COST_CODE
, ORG.NAME
, 0
, DECODE(PPT.CAPITAL_COST_TYPE_CODE
, 'R'
, A.BILLABLE_RAW_COST_ITD
, 'B'
, A.BILLABLE_BURDENED_COST_ITD) CAPITALIZABLE_COST
, DECODE(PPT.CAPITAL_COST_TYPE_CODE
, 'R'
, A.RAW_COST_ITD
, 'B'
, A.BURDENED_COST_ITD) TOTAL_COST
, NVL(PP.ASSET_ALLOCATION_METHOD
, 'N')
, NVL(PP.CAPITAL_EVENT_PROCESSING
, 'N')
, PPT.CAPITAL_COST_TYPE_CODE
, NVL(PPT.BURDEN_AMT_DISPLAY_METHOD
, 'S')
, NVL(PPT.TOTAL_BURDEN_FLAG
, DECODE(PPT.BURDEN_AMT_DISPLAY_METHOD
, 'S'
, 'Y'
, 'D'
, 'N'
, 'Y'))
FROM PA_PROJECT_ACCUM_HEADERS H
, PA_PROJECT_ACCUM_ACTUALS A
, PA_PROJECTS_ALL PP
, PA_PROJECT_TYPES_ALL PPT
, HR_ORGANIZATION_UNITS ORG
WHERE H.TASK_ID = 0
AND H.RESOURCE_ID = 0
AND H.PROJECT_ACCUM_ID = A.PROJECT_ACCUM_ID
AND ORG.ORGANIZATION_ID = PP.CARRYING_OUT_ORGANIZATION_ID
AND PP.PROJECT_TYPE = PPT.PROJECT_TYPE
AND PP.TEMPLATE_FLAG <> 'Y'
AND PPT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND H.PROJECT_ID = PP.PROJECT_ID
AND (PP.ORG_ID IS NULL OR PP.ORG_ID =PPT.ORG_ID)
AND PP.PJI_SOURCE_FLAG IS NULL UNION ALL SELECT PP.PROJECT_ID
, PP.NAME
, PP.SEGMENT1
, PP.PROJECT_STATUS_CODE
, PP.PROJECT_TYPE
, PPT.INTERFACE_COMPLETE_ASSET_FLAG
, PPT.INTERFACE_ASSET_COST_CODE
, ORG.NAME
, NVL(PPA.CAPITALIZED_COST
, 0)
, 0
, 0
, NVL(PP.ASSET_ALLOCATION_METHOD
, 'N')
, NVL(PP.CAPITAL_EVENT_PROCESSING
, 'N')
, PPT.CAPITAL_COST_TYPE_CODE
, NVL(PPT.BURDEN_AMT_DISPLAY_METHOD
, 'S')
, NVL(PPT.TOTAL_BURDEN_FLAG
, DECODE(PPT.BURDEN_AMT_DISPLAY_METHOD
, 'S'
, 'Y'
, 'D'
, 'N'
, 'Y'))
FROM PA_PROJECTS_ALL PP
, HR_ORGANIZATION_UNITS ORG
, PA_PROJECT_ASSETS PPA
, PA_PROJECT_TYPES_ALL PPT
WHERE ORG.ORGANIZATION_ID = PP.CARRYING_OUT_ORGANIZATION_ID
AND PP.PROJECT_TYPE = PPT.PROJECT_TYPE
AND PP.TEMPLATE_FLAG <> 'Y'
AND PPT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND PPA.PROJECT_ID(+) = PP.PROJECT_ID
AND (PP.ORG_ID IS NULL OR PP.ORG_ID = PPT.ORG_ID)
AND ( PPA.PROJECT_ASSET_TYPE IN ('AS-BUILT'
, 'ESTIMATED') OR NOT EXISTS (SELECT 1
FROM PA_PROJECT_ASSETS PPAS
WHERE PPAS.PROJECT_ID = PP.PROJECT_ID )) UNION ALL SELECT PP.PROJECT_ID
, PP.NAME
, PP.SEGMENT1
, PP.PROJECT_STATUS_CODE
, PP.PROJECT_TYPE
, PPT.INTERFACE_COMPLETE_ASSET_FLAG
, PPT.INTERFACE_ASSET_COST_CODE
, ORG.NAME
, (0)
, (DECODE(PPT.CAPITAL_COST_TYPE_CODE
, 'R'
, PTA.I_TOT_BILLABLE_RAW_COST
, 'B'
, PTA.I_TOT_BILLABLE_BURDENED_COST)) CAPITALIZABLE_COST
, (DECODE(PPT.CAPITAL_COST_TYPE_CODE
, 'R'
, PTA.I_TOT_RAW_COST
, 'B'
, PTA.I_TOT_BURDENED_COST)) TOTAL_COST
, NVL(PP.ASSET_ALLOCATION_METHOD
, 'N')
, NVL(PP.CAPITAL_EVENT_PROCESSING
, 'N')
, PPT.CAPITAL_COST_TYPE_CODE
, NVL(PPT.BURDEN_AMT_DISPLAY_METHOD
, 'S')
, NVL(PPT.TOTAL_BURDEN_FLAG
, DECODE(PPT.BURDEN_AMT_DISPLAY_METHOD
, 'S'
, 'Y'
, 'D'
, 'N'
, 'Y'))
FROM PA_PROJECTS_ALL PP
, HR_ORGANIZATION_UNITS ORG
, PA_TXN_ACCUM PTA
, PA_PROJECT_TYPES_ALL PPT
WHERE ORG.ORGANIZATION_ID = PP.CARRYING_OUT_ORGANIZATION_ID
AND PP.PROJECT_TYPE = PPT.PROJECT_TYPE
AND PP.TEMPLATE_FLAG <> 'Y'
AND PPT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND PTA.PROJECT_ID = PP.PROJECT_ID
AND PTA.ACTUAL_COST_ROLLUP_FLAG ='Y'
AND (PP.ORG_ID IS NULL OR PP.ORG_ID = PPT.ORG_ID)
AND PP.PJI_SOURCE_FLAG IS NULL UNION ALL SELECT PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.PROJECT_STATUS_CODE
, PROJ.PROJECT_TYPE
, PTYPE.INTERFACE_COMPLETE_ASSET_FLAG
, PTYPE.INTERFACE_ASSET_COST_CODE
, ORG.NAME
, 0
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, FACT.CAPITALIZABLE_RAW_COST
, 'B'
, FACT.CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_COST
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, FACT.RAW_COST
, 'B'
, FACT.BRDN_COST) TOTAL_COST
, NVL(PROJ.ASSET_ALLOCATION_METHOD
, 'N')
, NVL(PROJ.CAPITAL_EVENT_PROCESSING
, 'N')
, PTYPE.CAPITAL_COST_TYPE_CODE
, NVL(PTYPE.BURDEN_AMT_DISPLAY_METHOD
, 'S')
, NVL(PTYPE.TOTAL_BURDEN_FLAG
, DECODE(PTYPE.BURDEN_AMT_DISPLAY_METHOD
, 'S'
, 'Y'
, 'D'
, 'N'
, 'Y'))
FROM PJI_FP_XBS_ACCUM_F FACT
, PJI_PJP_WBS_HEADER WBS
, PA_PROJ_ELEMENT_VERSIONS VER
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_TYPES_ALL PTYPE
, HR_ORGANIZATION_UNITS ORG
WHERE FACT.PROJECT_ID = PROJ.PROJECT_ID
AND FACT.CALENDAR_TYPE = 'A'
AND FACT.PERIOD_TYPE_ID = 2048
AND FACT.TIME_ID = -1
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 4) = 4
AND FACT.RBS_AGGR_LEVEL = 'T'
AND FACT.PLAN_VERSION_ID = -1
AND FACT.PROJECT_ID = WBS.PROJECT_ID
AND FACT.PLAN_VERSION_ID = WBS.PLAN_VERSION_ID
AND WBS.WP_FLAG = 'N'
AND WBS.WBS_VERSION_ID = VER.ELEMENT_VERSION_ID
AND FACT.PROJECT_ELEMENT_ID = VER.PROJ_ELEMENT_ID
AND ORG.ORGANIZATION_ID = PROJ.CARRYING_OUT_ORGANIZATION_ID
AND PROJ.PROJECT_TYPE = PTYPE.PROJECT_TYPE
AND PROJ.TEMPLATE_FLAG <> 'Y'
AND PTYPE.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND PROJ.PJI_SOURCE_FLAG = 'Y'
AND (PROJ.ORG_ID IS NULL OR PROJ.ORG_ID = PTYPE.ORG_ID)

Columns