PA Project Budget

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report of Standard Project Budgets. This report does not include Financial Plan Budgets.
In addition to the budget details, the report will also display the actuals matching the budget line datapoints.
Note: Inclusion of actuals data requires Blitz Report Build Data later than 04-APR-2025 03:21:50
select
x.*
from
(
select
--
-- Budget
--
haouv.name operating_unit,
ppa.segment1 project_number,
ppa.name project_name,
pbt.budget_type,
pbv.version_name,
pbv.version_number,
xxen_util.meaning(pbv.budget_status_code,'BUDGET STATUS',275) status,
pbv.description,
pbem.budget_entry_method entry_method,
prl.name resource_list,
xxen_util.meaning(pbv.change_reason_code,'BUDGET CHANGE REASON',275) change_reason,
--
-- Budget line
--
pt.task_number,
pt.task_name,
pbl.period_name,
pbl.start_date,
pbl.end_date,
prlm.alias_path resource_alias,
pbl.raw_cost,
pbl.burdened_cost,
pbl.revenue,
pbl.quantity,
xxen_util.meaning(pra.unit_of_measure,'UNIT',275) unit_of_measure,
pbl.description line_description,
xxen_util.meaning(pbl.change_reason_code,'BUDGET CHANGE REASON',275) line_change_reason,
xxen_util.meaning(pra.track_as_labor_flag,'YES_NO',0) track_as_labor,
--
-- Actuals
&actuals_columns
--
-- DFF Columns
&dff_columns
--
pbv.project_id,
pbv.budget_version_id,
pbl.budget_line_id,
pbv.resource_list_id,
pra.task_id,
pra.resource_assignment_id,
pra.resource_list_member_id
from
hr_all_organization_units_vl haouv,
pa_projects_all ppa,
pa_budget_versions pbv,
pa_budget_types pbt,
pa_budget_entry_methods pbem,
pa_resource_lists prl,
pa_resource_assignments pra,
pa_budget_lines pbl,
pa_tasks pt,
--pa_resource_list_members prlm
(select
  prlm.resource_list_id,
  prlm.resource_list_member_id,
  prlm.alias alias,
  substr(sys_connect_by_path (prlm.alias,'|'),2) alias_path
 from
  pa_resource_list_members prlm
 where
  nvl(prlm.migration_code, '-99') <> 'N'
 connect by
  prior prlm.resource_list_member_id = prlm.parent_member_id and
  prior prlm.resource_list_id = prlm.resource_list_id
 start with
  prlm.parent_member_id is null
) prlm
where
1=1 and
haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
haouv.organization_id = ppa.org_id and
ppa.project_id = pbv.project_id and
pbv.budget_type_code = pbt.budget_type_code and
pbv.budget_entry_method_code = pbem.budget_entry_method_code and
pbv.resource_list_id = prl.resource_list_id and
pbv.budget_version_id  = pra.budget_version_id (+) and
pbv.project_id = pra.project_id (+) and
pra.resource_assignment_id = pbl.resource_assignment_id (+) and
pra.task_id = pt.task_id (+) and
pra.resource_list_member_id = prlm.resource_list_member_id (+)
) x
order by
x.operating_unit,
x.project_number,
x.project_name,
x.budget_type,
x.version_name,
x.version_number desc,
&lp_sort_col1
&lp_sort_col2
&lp_sort_col3
x.budget_line_id
Parameter NameSQL textValidation
Operating Unit
haouv.name=:p_operating_unit
LOV
Project Number
ppa.segment1=:p_project_number
LOV
Project Name
ppa.name=:p_project_name
LOV
Budget Type
pbt.budget_type=:p_budget_type
LOV
Budget Status
pbv.budget_status_code=:p_budget_status_code
LOV
Latest Budget Version
pbv.version_number =
(select
 max(pbv2.version_number)
 from
 pa_budget_versions pbv2
 where
 pbv2.project_id = pbv.project_id and
 pbv2.budget_type_code = pbv.budget_type_code
)
LOV
Budget Version
decode(pbv.budget_status_code,'B',to_char(pbv.version_number),xxen_util.meaning(pbv.budget_status_code,'BUDGET STATUS',275)) = :p_budget_version
LOV
Task Number
pt.task_number=:p_task_number
LOV
Task Name
pt.task_name=:p_task_name
LOV
Resource Alias
prlm.alias=:p_resource_alias
LOV
Period From
pbl.period_name is not null and
pbl.start_date >=
(select
 min(pbpv.period_start_date)
 from
 pa_budget_periods_v2 pbpv
 where
 pbpv.org_id = ppa.org_id and
 pbpv.period_name = :p_period_from
)
LOV
Period To
pbl.period_name is not null and
pbl.end_date <=
(select
 max(pbpv.period_end_date)
 from
 pa_budget_periods_v2 pbpv
 where
 pbpv.org_id = ppa.org_id and
 pbpv.period_name = :p_period_to
)
LOV
Budget Line Start Date
pbl.start_date=:p_date_start
Date
Budget Line End Date
pbl.end_date=:p_date_end
Date
Budget Line Active On Date
trunc(:p_date_active) between pbl.start_date and pbl.end_date
Date
Show DFF Attributes
select
x.sql_text
from
(
select 1 seq, 'pbv.attribute_category budget_attribute_category,' sql_text from dual
union
select 2 seq, dbms_lob.substr(xxen_util.dff_columns(p_table_name=>'PA_BUDGET_VERSIONS',p_table_alias=>'pbv', p_row_id=>'rowid')) sql_text from dual
union
select 3 seq, 'pbl.attribute_category line_attribute_category,' sql_text from dual
union
select 4 seq, dbms_lob.substr(xxen_util.dff_columns(p_table_name=>'PA_BUDGET_LINES',p_table_alias=>'pbl', p_row_id=>'rowid')) sql_text from dual
) x
where
x.sql_text is not null
order by
x.seq
LOV
Show Actuals
select
'xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''REVENUE'') actual_revenue,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''RAW_COST'') actual_raw_cost,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''BURDENED_COST'') actual_burdened_cost,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''QUANTITY'') actual_quantity,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''LABOR_HOURS'') actual_labor_hours,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''BILLABLE_RAW_COST'') actual_billable_raw_cost,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''BILLABLE_BURDENED_COST'') actual_billable_burdened_cost,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''BILLABLE_QUANTITY'') actual_billable_quantity,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''BILLABLE_LABOR_HOURS'') actual_billable_labor_hours,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''CMT_RAW_COST'') actual_cmt_raw_cost,
xxen_pa_upload.get_actuals_datapoint(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date, ''CMT_BURDENED_COST'') actual_cmt_burdened_cost,
xxen_util.meaning(xxen_pa_upload.get_actuals_uom(ppa.org_id, pbv.project_id, pra.task_id, pra.resource_list_member_id, pbem.time_phased_type_code, pbl.period_name, pbl.start_date, pbl.end_date),''UNIT'',275) actual_uom,'
from
dual where exists (select null from dba_source where owner = 'APPS' and type = 'PACKAGE' and name = 'XXEN_PA_UPLOAD' and lower(text) like '%get_actuals_datapoint%')
union
select
'''Actuals cannot be reported in this version of Blitz Report. Please upgrade Blitz Report to use this functionality'' actuals_status,'
from
dual where not exists (select null from dba_source where owner = 'APPS' and type = 'PACKAGE' and name = 'XXEN_PA_UPLOAD' and lower(text) like '%get_actuals_datapoint%')
LOV
Sort Precedence 1
nvl(x.start_date,(select pbpv.period_start_date from hr_all_organization_units_vl haouv, pa_budget_periods_v2 pbpv where haouv.organization_id = pbpv.org_id and haouv.name = x.operating_unit and pbpv.period_name = x.period_name and rownum <= 1)),
LOV
Sort Precedence 2
nvl(x.start_date,(select pbpv.period_start_date from hr_all_organization_units_vl haouv, pa_budget_periods_v2 pbpv where haouv.organization_id = pbpv.org_id and haouv.name = x.operating_unit and pbpv.period_name = x.period_name and rownum <= 1)),
LOV
Sort Precedence 3
nvl(x.start_date,(select pbpv.period_start_date from hr_all_organization_units_vl haouv, pa_budget_periods_v2 pbpv where haouv.organization_id = pbpv.org_id and haouv.name = x.operating_unit and pbpv.period_name = x.period_name and rownum <= 1)),
LOV
Download
Related Blitz Reports
PA Project Budget Upload ECC Projects, Projects Costing: Transactions, SQL1 SAM: GL Account Analysis (Drilldown) (with inventory and WIP) TB GL Account Analysis (Drilldown)v2 GL Account Analysis (Drilldown) (with inventory and WIP) TB GL Account Analysis (Drilldown)v3 GL Account Analysis PA Revenue, Cost, Budgets by Resources COPY OF: GL Account Analysis (Distributions)
Blitz Report™