PA Update Project Summary Amounts

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Update Project Summary Amounts
Application: Projects
Source: PRC: Update Project Summary Amounts (XML)
Short Name: PAXACMPT_XML
DB package: PA_PAXACMPT_XMLP_PKG
SELECT
     PAH.PROJECT_ACCUM_ID PROJECT_ACCUM_ID2,
     PBT.BUDGET_TYPE,
     BASE_RAW_COST_ITD,
     BASE_RAW_COST_YTD,
     BASE_RAW_COST_PP,
     BASE_RAW_COST_PTD,
     BASE_BURDENED_COST_ITD,
     BASE_BURDENED_COST_YTD,
     BASE_BURDENED_COST_PP,
     BASE_BURDENED_COST_PTD,
     ORIG_RAW_COST_ITD,
     ORIG_RAW_COST_YTD,
     ORIG_RAW_COST_PP,
     ORIG_RAW_COST_PTD,
     ORIG_BURDENED_COST_ITD,
     ORIG_BURDENED_COST_YTD,
     ORIG_BURDENED_COST_PP,
     ORIG_BURDENED_COST_PTD,
     BASE_REVENUE_ITD,
     BASE_REVENUE_YTD,
     BASE_REVENUE_PP,
     BASE_REVENUE_PTD,
     ORIG_REVENUE_ITD,
     ORIG_REVENUE_YTD,
     ORIG_REVENUE_PP,
     ORIG_REVENUE_PTD,
     ORIG_LABOR_HOURS_ITD,
     ORIG_LABOR_HOURS_YTD,
     ORIG_LABOR_HOURS_PP,
     ORIG_LABOR_HOURS_PTD,
     BASE_LABOR_HOURS_ITD,
     BASE_LABOR_HOURS_YTD,
     BASE_LABOR_HOURS_PP,
     BASE_LABOR_HOURS_PTD,
     ORIG_QUANTITY_YTD,
     ORIG_QUANTITY_ITD,
     ORIG_QUANTITY_PP,
     ORIG_QUANTITY_PTD,
     BASE_QUANTITY_YTD,
     BASE_QUANTITY_ITD,
     BASE_QUANTITY_PP,
     BASE_QUANTITY_PTD,
     ORIG_LABOR_HOURS_TOT,
     BASE_LABOR_HOURS_TOT,
     ORIG_QUANTITY_TOT,
     BASE_QUANTITY_TOT,
     BASE_RAW_COST_TOT,
     BASE_BURDENED_COST_TOT,
     ORIG_RAW_COST_TOT,
     ORIG_BURDENED_COST_TOT,
     BASE_REVENUE_TOT,
     ORIG_REVENUE_TOT,
     BASE_UNIT_OF_MEASURE,
     ORIG_UNIT_OF_MEASURE
FROM
     PA_BUDGET_TYPES PBT,
     PA_PROJECT_ACCUM_HEADERS PAH,
     PA_PROJECT_ACCUM_BUDGETS PAB
 WHERE
     PAB.BUDGET_TYPE_CODE = PBT.BUDGET_TYPE_CODE
    AND PAH.PROJECT_ACCUM_ID = PAB.PROJECT_ACCUM_ID
    and pab.fin_plan_type_id is NULL
    and PAH.PROJECT_ACCUM_ID=:PROJECT_ACCUM_ID
union
SELECT
     PAH.PROJECT_ACCUM_ID,
     PBT.BUDGET_TYPE,
     BASE_RAW_COST_ITD,
     BASE_RAW_COST_YTD,
     BASE_RAW_COST_PP,
     BASE_RAW_COST_PTD,
     BASE_BURDENED_COST_ITD,
     BASE_BURDENED_COST_YTD,
     BASE_BURDENED_COST_PP,
     BASE_BURDENED_COST_PTD,
     ORIG_RAW_COST_ITD,
     ORIG_RAW_COST_YTD,
     ORIG_RAW_COST_PP,
     ORIG_RAW_COST_PTD,
     ORIG_BURDENED_COST_ITD,
     ORIG_BURDENED_COST_YTD,
     ORIG_BURDENED_COST_PP,
     ORIG_BURDENED_COST_PTD,
     BASE_REVENUE_ITD,
     BASE_REVENUE_YTD,
     BASE_REVENUE_PP,
     BASE_REVENUE_PTD,
     ORIG_REVENUE_ITD,
     ORIG_REVENUE_YTD,
     ORIG_REVENUE_PP,
     ORIG_REVENUE_PTD,
     ORIG_LABOR_HOURS_ITD,
     ORIG_LABOR_HOURS_YTD,
     ORIG_LABOR_HOURS_PP,
     ORIG_LABOR_HOURS_PTD,
     BASE_LABOR_HOURS_ITD,
     BASE_LABOR_HOURS_YTD,
     BASE_LABOR_HOURS_PP,
     BASE_LABOR_HOURS_PTD,
     ORIG_QUANTITY_YTD,
     ORIG_QUANTITY_ITD,
     ORIG_QUANTITY_PP,
     ORIG_QUANTITY_PTD,
     BASE_QUANTITY_YTD,
     BASE_QUANTITY_ITD,
     BASE_QUANTITY_PP,
     BASE_QUANTITY_PTD,
     ORIG_LABOR_HOURS_TOT,
     BASE_LABOR_HOURS_TOT,
     ORIG_QUANTITY_TOT,
     BASE_QUANTITY_TOT,
     BASE_RAW_COST_TOT,
     BASE_BURDENED_COST_TOT,
     ORIG_RAW_COST_TOT,
     ORIG_BURDENED_COST_TOT,
     BASE_REVENUE_TOT,
     ORIG_REVENUE_TOT,
     BASE_UNIT_OF_MEASURE,
     ORIG_UNIT_OF_MEASURE
 FROM
     PA_BUDGET_TYPES PBT,
     PA_PROJECT_ACCUM_HEADERS PAH,
     PA_PROJECT_ACCUM_BUDGETS PAB
 WHERE
     PAB.BUDGET_TYPE_CODE = PBT.BUDGET_TYPE_CODE
    AND PAH.PROJECT_ACCUM_ID = PAB.PROJECT_ACCUM_ID
    and pab.fin_plan_type_id is NULL
    and PAH.PROJECT_ACCUM_ID=:PROJECT_ACCUM_ID
union all
SELECT
     PAH.PROJECT_ACCUM_ID,
     substr(tl.name,1,30)         BUDGET_TYPE,
     BASE_RAW_COST_ITD,
     BASE_RAW_COST_YTD,
     BASE_RAW_COST_PP,
     BASE_RAW_COST_PTD,
     BASE_BURDENED_COST_ITD,
     BASE_BURDENED_COST_YTD,
     BASE_BURDENED_COST_PP,
     BASE_BURDENED_COST_PTD,
     ORIG_RAW_COST_ITD,
     ORIG_RAW_COST_YTD,
     ORIG_RAW_COST_PP,
     ORIG_RAW_COST_PTD,
     ORIG_BURDENED_COST_ITD,
     ORIG_BURDENED_COST_YTD,
     ORIG_BURDENED_COST_PP,
     ORIG_BURDENED_COST_PTD,
     BASE_REVENUE_ITD,
     BASE_REVENUE_YTD,
     BASE_REVENUE_PP,
     BASE_REVENUE_PTD,
     ORIG_REVENUE_ITD,
     ORIG_REVENUE_YTD,
     ORIG_REVENUE_PP,
     ORIG_REVENUE_PTD,
     ORIG_LABOR_HOURS_ITD,
     ORIG_LABOR_HOURS_YTD,
     ORIG_LABOR_HOURS_PP,
     ORIG_LABOR_HOURS_PTD,
     BASE_LABOR_HOURS_ITD,
     BASE_LABOR_HOURS_YTD,
     BASE_LABOR_HOURS_PP,
     BASE_LABOR_HOURS_PTD,
     ORIG_QUANTITY_YTD,
     ORIG_QUANTITY_ITD,
     ORIG_QUANTITY_PP,
     ORIG_QUANTITY_PTD,
     BASE_QUANTITY_YTD,
     BASE_QUANTITY_ITD,
     BASE_QUANTITY_PP,
     BASE_QUANTITY_PTD,
     ORIG_LABOR_HOURS_TOT,
    BASE_LABOR_HOURS_TOT,
     ORIG_QUANTITY_TOT,
     BASE_QUANTITY_TOT,
     BASE_RAW_COST_TOT,
     BASE_BURDENED_COST_TOT,
     ORIG_RAW_COST_TOT,
     ORIG_BURDENED_COST_TOT,
     BASE_REVENUE_TOT,
     ORIG_REVENUE_TOT,
     BASE_UNIT_OF_MEASURE,
     ORIG_UNIT_OF_MEASURE
 FROM
     pa_fin_plan_types_tl tl,
     PA_PROJECT_ACCUM_HEADERS PAH,
     PA_PROJECT_ACCUM_BUDGETS PAB
 WHERE
    PAH.PROJECT_ACCUM_ID = PAB.PROJECT_ACCUM_ID
    and tl.LANGUAGE = USERENV('LANG') 
 and PAH.PROJECT_ACCUM_ID=:PROJECT_ACCUM_ID
Parameter Name SQL text Validation
Operating Unit
 
LOV
From Project Number
 
Char
To Project Number
 
Char
Through Date
 
Date
Summarize Cost
 
LOV Oracle
Expenditure Type Class
 
LOV Oracle
Summarize Revenue
 
LOV Oracle
Summarize Budgets
 
LOV Oracle
Budget Type
 
LOV Oracle
Summarize Commitments
 
LOV Oracle
Project Type
 
LOV Oracle
Generate Report Output
 
LOV Oracle