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.

VIEW: APPS.PA_CAPITAL_PROJECTS_BASE_V

Source

View Text - Preformatted


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', SUM(fact.CAPITALIZABLE_RAW_COST), 'B', SUM(fact.CAPITALIZABLE_BRDN_COST)) capitalizable_cost , DECODE(ptype.CAPITAL_COST_TYPE_CODE, 'R', SUM(fact.RAW_COST), 'B', SUM(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, 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.WBS_ROLLUP_FLAG = 'N' AND fact.PRG_ROLLUP_FLAG = 'N' AND BITAND(fact.CURR_RECORD_TYPE_ID,4) = 4 AND fact.RBS_AGGR_LEVEL = 'L' AND fact.PLAN_VERSION_ID = -1 AND fact.RBS_ELEMENT_ID = -1 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) GROUP BY 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 , 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'))

View Text - HTML Formatted

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'
, SUM(FACT.CAPITALIZABLE_RAW_COST)
, 'B'
, SUM(FACT.CAPITALIZABLE_BRDN_COST)) CAPITALIZABLE_COST
, DECODE(PTYPE.CAPITAL_COST_TYPE_CODE
, 'R'
, SUM(FACT.RAW_COST)
, 'B'
, SUM(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
, 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.WBS_ROLLUP_FLAG = 'N'
AND FACT.PRG_ROLLUP_FLAG = 'N'
AND BITAND(FACT.CURR_RECORD_TYPE_ID
, 4) = 4
AND FACT.RBS_AGGR_LEVEL = 'L'
AND FACT.PLAN_VERSION_ID = -1
AND FACT.RBS_ELEMENT_ID = -1
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) GROUP BY 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
, 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'))