COPY OF: PA Capital Project Summary with Asset Detail

Description
Columns: Operating Unit, Project Type, Project Number, Project Name, Project Id, Expensed Amount, Cip Amount, Interfaced Cip Amount, Rwip Amount, Interfaced Rwip Amount ...
Detail project report that shows the project asset details, and combined project level costs breakdown, along with the project assets details from the Capital Summary
select
haouv.name operating_unit,
cv.project_type,
cv.project_number,
cv.project_name,
cv.project_id,
cv.expensed - nvl(rv.retirable_cost, 0) expensed_amount,
cv.cip_cost cip_amount,
cv.capitalized_cost interfaced_cip_amount,
nvl(rv.rwip, 0) rwip_amount,
nvl(rv.retired_cost, 0) interfaced_rwip_amount,
cv.total_costs total_amount,
cv.project_organization,
--pa_pacrcaps_xmlp_pkg.cp_project_id_p cp_project_id,
ppav.asset_name,
ppav.project_asset_type,
ppav.estimated_in_service_date,
ppav.date_placed_in_service,
ppav.total_asset_cost,
rep_total_asset_cost,
set_of_books_id,
ppav.reverse_flag,
ppav.capital_hold_flag,
ppav.book_type_code,
ppav.asset_number,
ppav.asset_units,
ppav.estimated_asset_units,
ppav.estimated_cost,
ppav.pm_product_code,
ppav.pm_asset_reference,
flk.concatenated_segments location,
ppav.full_name employee_name,
ppav.employee_number,
ppav.depreciate_flag,
ppav.amortize_flag,
ppav.asset_description,
ppav.manufacturer_name,
ppav.model_number,
ppav.serial_number,
ppav.tag_number,
ppav.capitalized_flag,
ppav.capitalized_date,
ppav.cost_adjustment_flag
from
hr_all_organization_units_vl haouv,
fa_locations_kfv flk,
pa_project_assets_v ppav,
pa_capital_projects_v cv,
(
select
sum(retirable_cost) retirable_cost,
sum(retired_cost) retired_cost,
sum(retirable_cost - retired_cost) rwip,
project_id
from
pa_retirement_costs_v
group by
project_id
) rv
where
1=1 and
haouv.organization_id=cv.org_id and
flk.location_id(+)=ppav.location_id and
ppav.project_id(+)=cv.project_id and
ppav.project_asset_type(+) != 'RETIREMENT_ADJUSTMENT' and
rv.project_id(+)=cv.project_id
order by
cv.project_type,
cv.project_organization,
cv.project_name,
ppav.asset_name
Parameter Name SQL text Validation
Operating Unit
haouv.name=:operating_unit
LOV
Project Type
cv.project_type=:p_project_type
LOV Oracle
Project Number
cv.project_id=:p_project_id
LOV Oracle
Project Organization
cv.project_organization in (select name from pa_organizations_proj_all_bg_v where organization_id=:p_project_org)
LOV Oracle
Class Category
cv.project_id in (select project_id from pa_project_classes where class_category = :p_class_category)
LOV Oracle
Class Code
cv.project_id in (select project_id from pa_project_classes where class_code = :p_class_code and class_category = :p_class_category)
LOV Oracle