PA Capital Project Summary with Asset Detail

Description
Categories: Draft, Toolkit - Operations
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
Run PA Capital Project Summary with Asset Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
select
 haouv.name operating_unit,
 cv.project_type,
 cv.project_number,
 cv.project_name,
 (select ppa.long_name from pa_projects_all ppa where ppa.project_id = cv.project_id) long_name,
 (select pps.project_status_name from pa_project_statuses pps where pps.project_status_code = cv.project_status_code) project_status,
 cv.project_id,
 cv.expensed -(decode(pa_mc_currency_pkg.get_mrc_sob_type_code,'R',0, nvl(rv.retirable_cost,0))) expensed_amount,
 cv.cip_cost cip_amount,
 cv.capitalized_cost interfaced_cip_amount,
 decode(pa_mc_currency_pkg.get_mrc_sob_type_code,'R',null, nvl(rv.rwip,0))rwip_amount,
 decode(pa_mc_currency_pkg.get_mrc_sob_type_code, 'R',null, nvl(rv.retired_cost,0))interfaced_rwip_amount,
 cv.total_costs total_amount,
 cv.project_organization,
 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,
 (select listagg(ppc.class_category || ' > ' || ppc.class_code,', ') within group(order by ppc.class_category,ppc.class_code)
  from pa_project_classes ppc
  where ppc.project_id = cv.project_id
 ) project_class_category_class,
 fth.transaction_date_entered asset_trx_entered_date,
 fdp.period_name asset_trx_entered_period,
 fth.date_effective asset_date_effective,
 fdp2.period_name asset_date_effective_period
from
 hr_all_organization_units_vl haouv,
 fa_locations_kfv flk,
 pa_project_assets_v ppav,
 pa_capital_projects_v cv,
 (select
   sum(prcv.retirable_cost) retirable_cost,
   sum(prcv.retired_cost) retired_cost,
   sum(prcv.retirable_cost - retired_cost) rwip,
   prcv.project_id
  from
   pa_retirement_costs_v prcv
  where
   2=2
  group by
   prcv.project_id
 ) rv,
 fa_transaction_headers fth,
 fa_deprn_periods fdp,
 fa_deprn_periods fdp2
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
 and fth.asset_id(+)=ppav.fa_asset_id
 and fth.book_type_code(+)=ppav.book_type_code
 and fth.transaction_type_code(+)='ADDITION'
 and fdp.book_type_code(+)=fth.book_type_code
 and fdp.calendar_period_open_date(+)<=trunc(fth.transaction_date_entered)
 and fdp.calendar_period_close_date(+)>=trunc(fth.transaction_date_entered)
 and fdp2.book_type_code(+)=fth.book_type_code
 and fdp2.period_open_date(+)<=fth.date_effective
 and nvl(fdp2.period_close_date(+),sysdate)>=fth.date_effective
order by
 cv.project_type,
 cv.project_organization,
 cv.project_name,
 ppav.asset_name
Parameter Name SQL text Validation
Project Type
cv.project_type=:p_project_type
LOV Oracle
Project Number
cv.project_id=:p_project_id
LOV Oracle
Operating Unit
haouv.name=:operating_unit
LOV
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
Asset Trx Entered Period
fdp.period_name=:p_asset_trx_ent_period
LOV
Asset Date Effective Period
fdp2.period_name=:p_asset_date_eff_period
LOV
Asset Trx Entered Date From
fth.transaction_date_entered>=trunc(:p_asset_trx_date_fr)
Date
Asset Trx Entered Date To
fth.transaction_date_entered<trunc(:p_asset_trx_date_to)+1
Date
Asset Date Effective From
fth.date_effective>=trunc(:p_asset_date_eff_fr)
Date
Asset Date Effective To
fth.date_effective<trunc(:p_asset_date_eff_to)+1
Date
Blitz Report™