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_FP_CI_AMOUNTS_V

Source

View Text - Preformatted


SELECT bv.ci_id, bv.project_id, NVL(SUM(bv.est_quantity),0) est_quantity, NVL(SUM(bv.labor_quantity),0) labor_quantity, NVL(SUM(bv.est_project_raw_cost),0) est_project_raw_cost, NVL(SUM(bv.total_project_raw_cost),0) total_project_raw_cost, NVL(SUM(bv.est_project_burdened_cost),0) est_project_burdened_cost, NVL(SUM(bv.total_project_burdened_cost),0) total_project_burdened_cost, NVL(SUM(bv.est_project_revenue),0) est_project_revenue, NVL(SUM(bv.total_project_revenue),0) total_project_revenue, DECODE(MAX(po.margin_derived_from_code),'R', NVL(SUM(bv.est_project_raw_cost),0), NVL(SUM(bv.est_project_burdened_cost),0)) est_project_cost, DECODE(MAX(po.margin_derived_from_code),'R', NVL(SUM(bv.total_project_raw_cost),0), NVL(SUM(bv.total_project_burdened_cost),0)) total_project_cost, DECODE(NVL(SUM(bv.labor_quantity),0),0,NVL(SUM(bv.est_quantity),0),NVL(SUM(bv.labor_quantity),0)) derived_quantity, DECODE(MAX(po.margin_derived_from_code),'R', DECODE(NVL(SUM(bv.total_project_raw_cost),0), 0, NVL(SUM(bv.est_project_raw_cost),0), NVL(SUM(bv.total_project_raw_cost),0)), DECODE(NVL(SUM(bv.total_project_burdened_cost),0), 0, NVL(SUM(bv.est_project_burdened_cost),0), NVL(SUM(bv.total_project_burdened_cost),0))) derived_cost, DECODE(NVL(SUM(bv.total_project_revenue),0), 0, NVL(SUM(bv.est_project_revenue),0), NVL(SUM(bv.total_project_revenue),0)) derived_revenue, DECODE(NVL(SUM(bv.est_project_revenue),0), 0,null, NVL(SUM(bv.est_project_revenue),0) - DECODE(MAX(po.margin_derived_from_code),'R', NVL(SUM(bv.est_project_raw_cost),0), NVL(SUM(bv.est_project_burdened_cost),0)) ) est_margin, DECODE(NVL(SUM(bv.est_project_revenue),0), 0,null, ((NVL(SUM(bv.est_project_revenue),0) - DECODE(MAX(po.margin_derived_from_code),'R', NVL(SUM(bv.est_project_raw_cost),0), NVL(SUM(bv.est_project_burdened_cost),0)))/ NVL(SUM(bv.est_project_revenue),0)) * 100 ) est_margin_percentage, DECODE(max(COST_IMPACT_FLAG || REVENUE_IMPACT_FLAG),'YY', NVL(SUM(bv.total_project_revenue),0) - DECODE(MAX(po.margin_derived_from_code),'R', NVL(SUM(bv.total_project_raw_cost),0), NVL(SUM(bv.total_project_burdened_cost),0)),null) planned_margin, /* Bug 5117346 */ DECODE(max(COST_IMPACT_FLAG || REVENUE_IMPACT_FLAG),'YY', DECODE(NVL(SUM(bv.total_project_revenue),0), 0, null, ((NVL(SUM(bv.total_project_revenue),0) - DECODE(MAX(po.margin_derived_from_code),'R', NVL(SUM(bv.total_project_raw_cost),0), NVL(SUM(bv.total_project_burdened_cost),0)))/ NVL(SUM(bv.total_project_revenue),0)) * 100),null) planned_margin_percentage, /* Bug 5388954 */ DECODE(DECODE(NVL(SUM(bv.total_project_revenue),0), 0, NVL(SUM(bv.est_project_revenue),0), NVL(SUM(bv.total_project_revenue),0)), 0, null, DECODE(NVL(SUM(bv.total_project_revenue),0), 0, NVL(SUM(bv.est_project_revenue),0), NVL(SUM(bv.total_project_revenue),0)) - DECODE(MAX(po.margin_derived_from_code),'R', DECODE(NVL(SUM(bv.total_project_raw_cost),0), 0, NVL(SUM(bv.est_project_raw_cost),0), NVL(SUM(bv.total_project_raw_cost),0)), DECODE(NVL(SUM(bv.total_project_burdened_cost),0), 0, NVL(SUM(bv.est_project_burdened_cost),0), NVL(SUM(bv.total_project_burdened_cost),0)))) derived_margin, DECODE(DECODE(NVL(SUM(bv.total_project_revenue),0), 0, NVL(SUM(bv.est_project_revenue),0), NVL(SUM(bv.total_project_revenue),0)), 0, null, ((DECODE(NVL(SUM(bv.total_project_revenue),0), 0, NVL(SUM(bv.est_project_revenue),0), NVL(SUM(bv.total_project_revenue),0)) - DECODE(MAX(po.margin_derived_from_code),'R', DECODE(NVL(SUM(bv.total_project_raw_cost),0), 0, NVL(SUM(bv.est_project_raw_cost),0), NVL(SUM(bv.total_project_raw_cost),0)), DECODE(NVL(SUM(bv.total_project_burdened_cost),0), 0, NVL(SUM(bv.est_project_burdened_cost),0), NVL(SUM(bv.total_project_burdened_cost),0))))/ DECODE(NVL(SUM(bv.total_project_revenue),0), 0, NVL(SUM(bv.est_project_revenue),0), NVL(SUM(bv.total_project_revenue),0))) * 100 ) derived_margin_percentage FROM (SELECT decode(approved_cost_plan_type_flag,'Y',total_project_raw_cost,0) total_project_raw_cost, decode(approved_cost_plan_type_flag,'Y',total_project_burdened_cost,0) total_project_burdened_cost, decode(approved_rev_plan_type_flag,'Y',total_project_revenue,0) total_project_revenue, est_quantity, labor_quantity, est_project_raw_cost, est_project_burdened_cost, est_project_revenue, project_id, ci_id, budget_version_id FROM pa_budget_versions) bv, pa_proj_fp_options po, pa_ci_types_w_finplan_v pctwf, /*Bug 5117346 */ pa_control_items pci WHERE po.fin_plan_version_id = bv.budget_version_id AND pctwf.ci_type_id = pci.ci_type_id AND pci.ci_id = bv.ci_id AND bv.ci_id > 0 GROUP BY bv.ci_id, bv.project_id

View Text - HTML Formatted

SELECT BV.CI_ID
, BV.PROJECT_ID
, NVL(SUM(BV.EST_QUANTITY)
, 0) EST_QUANTITY
, NVL(SUM(BV.LABOR_QUANTITY)
, 0) LABOR_QUANTITY
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0) EST_PROJECT_RAW_COST
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0) TOTAL_PROJECT_RAW_COST
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0) EST_PROJECT_BURDENED_COST
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0) TOTAL_PROJECT_BURDENED_COST
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0) EST_PROJECT_REVENUE
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0) TOTAL_PROJECT_REVENUE
, DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0)) EST_PROJECT_COST
, DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0)) TOTAL_PROJECT_COST
, DECODE(NVL(SUM(BV.LABOR_QUANTITY)
, 0)
, 0
, NVL(SUM(BV.EST_QUANTITY)
, 0)
, NVL(SUM(BV.LABOR_QUANTITY)
, 0)) DERIVED_QUANTITY
, DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0))
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0))) DERIVED_COST
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)) DERIVED_REVENUE
, DECODE(NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, 0
, NULL
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0) - DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0)) ) EST_MARGIN
, DECODE(NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, 0
, NULL
, ((NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0) - DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0)))/ NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)) * 100 ) EST_MARGIN_PERCENTAGE
, DECODE(MAX(COST_IMPACT_FLAG || REVENUE_IMPACT_FLAG)
, 'YY'
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0) - DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0))
, NULL) PLANNED_MARGIN
, /* BUG 5117346 */ DECODE(MAX(COST_IMPACT_FLAG || REVENUE_IMPACT_FLAG)
, 'YY'
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NULL
, ((NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0) - DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0)))/ NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)) * 100)
, NULL) PLANNED_MARGIN_PERCENTAGE
, /* BUG 5388954 */ DECODE(DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0))
, 0
, NULL
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)) - DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0))
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0)))) DERIVED_MARGIN
, DECODE(DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0))
, 0
, NULL
, ((DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)) - DECODE(MAX(PO.MARGIN_DERIVED_FROM_CODE)
, 'R'
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_RAW_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_RAW_COST)
, 0))
, DECODE(NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_BURDENED_COST)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_BURDENED_COST)
, 0))))/ DECODE(NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0)
, 0
, NVL(SUM(BV.EST_PROJECT_REVENUE)
, 0)
, NVL(SUM(BV.TOTAL_PROJECT_REVENUE)
, 0))) * 100 ) DERIVED_MARGIN_PERCENTAGE
FROM (SELECT DECODE(APPROVED_COST_PLAN_TYPE_FLAG
, 'Y'
, TOTAL_PROJECT_RAW_COST
, 0) TOTAL_PROJECT_RAW_COST
, DECODE(APPROVED_COST_PLAN_TYPE_FLAG
, 'Y'
, TOTAL_PROJECT_BURDENED_COST
, 0) TOTAL_PROJECT_BURDENED_COST
, DECODE(APPROVED_REV_PLAN_TYPE_FLAG
, 'Y'
, TOTAL_PROJECT_REVENUE
, 0) TOTAL_PROJECT_REVENUE
, EST_QUANTITY
, LABOR_QUANTITY
, EST_PROJECT_RAW_COST
, EST_PROJECT_BURDENED_COST
, EST_PROJECT_REVENUE
, PROJECT_ID
, CI_ID
, BUDGET_VERSION_ID
FROM PA_BUDGET_VERSIONS) BV
, PA_PROJ_FP_OPTIONS PO
, PA_CI_TYPES_W_FINPLAN_V PCTWF
, /*BUG 5117346 */ PA_CONTROL_ITEMS PCI
WHERE PO.FIN_PLAN_VERSION_ID = BV.BUDGET_VERSION_ID
AND PCTWF.CI_TYPE_ID = PCI.CI_TYPE_ID
AND PCI.CI_ID = BV.CI_ID
AND BV.CI_ID > 0 GROUP BY BV.CI_ID
, BV.PROJECT_ID