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_FP_CI_AMOUNTS_V

Product:	PA - Projects
Description:	This view is used for the Control Item pages. It returns financial amounts from budget versions grouped by ci_id and project_id.
Implementation/DBA Data:	APPS.PA_FP_CI_AMOUNTS_V

View Text

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

Columns

Name
CI_ID
PROJECT_ID
EST_QUANTITY
LABOR_QUANTITY
EST_PROJECT_RAW_COST
TOTAL_PROJECT_RAW_COST
EST_PROJECT_BURDENED_COST
TOTAL_PROJECT_BURDENED_COST
EST_PROJECT_REVENUE
TOTAL_PROJECT_REVENUE
EST_PROJECT_COST
TOTAL_PROJECT_COST
DERIVED_QUANTITY
DERIVED_COST
DERIVED_REVENUE
EST_MARGIN
EST_MARGIN_PERCENTAGE
PLANNED_MARGIN
PLANNED_MARGIN_PERCENTAGE
DERIVED_MARGIN
DERIVED_MARGIN_PERCENTAGE