PA Project Budget

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

SQL 101 lines

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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') 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

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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') 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

Parameters 20

Parameter

Type

List of values SQL

Operating Unit

LOV

haouv.name=:p_operating_unit

Project Number

LOV

ppa.segment1=:p_project_number

Project Name

LOV

ppa.name=:p_project_name

Budget Type

LOV

pbt.budget_type=:p_budget_type

Budget Status

LOV

pbv.budget_status_code=:p_budget_status_code

Latest Budget Version

LOV

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
)

Budget Version

LOV

decode(pbv.budget_status_code,'B',to_char(pbv.version_number),xxen_util.meaning(pbv.budget_status_code,'BUDGET STATUS',275)) = :p_budget_version

Task Number

LOV

pt.task_number=:p_task_number

Task Name

LOV

pt.task_name=:p_task_name

Resource Alias

LOV

prlm.alias=:p_resource_alias

Period From

LOV

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
)

Period To

LOV

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
)

Budget Line Start Date

Date

pbl.start_date=:p_date_start

Budget Line End Date

Date

pbl.end_date=:p_date_end

Budget Line Active On Date

Date

trunc(:p_date_active) between pbl.start_date and pbl.end_date

DFF Display

LOV

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',p_display_mode=>:dff_display)) 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',p_display_mode=>:dff_display)) sql_text from dual
) x
where
x.sql_text is not null
order by
x.seq

Show Actuals

LOV

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%')

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%')

Sort Precedence 1

LOV

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)),

Sort Precedence 2

LOV

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)),

Sort Precedence 3

LOV

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)),

Description

SQL 101 lines

Parameters 20

Find out more

Features

Download

FAQ