PN Generate Portfolio Summary

with
--
q_portfolio_summary as
(
select
 plrdg.intercompany_flag ico_flag,
 plrdg.as_of_period period,
 plrdg.liability,
 plrdg.cash,
 plrdg.interest_accrual,
 plrdg.rou_asset_fin,
 plrdg.rou_amort_fin,
 plrdg.rou_asset_opr,
 plrdg.rou_amort_opr,
 plrdg.lease_expenses,
 plrdg.currency_code,
 nvl(plrdg.period_start_date,to_date(plrdg.as_of_period,'Mon-YYYY')) period_start_date,
 plrdg.seq
from
 pn_lease_reports_data_gtt plrdg
),
--
q_amendment_detail as
(
select
 case when parg.amendment_type like '%(InterCompany)%' then 'Y' else 'N' end ico_flag,
 parg.lease_num,
 parg.lease_name,
 parg.amendment_date,
 parg.effective_date,
 parg.amendment_type,
 parg.amt_per_period amt_diff_per_period,
 parg.tot_period no_of_periods,
 parg.tot_amt tot_diff_amt,
 parg.currency_code,
 decode(pla.payment_term_proration_rule,365,'365 Days/Year',360,'360 Days/Year',999,'Days/Month',null) payment_term_proration_rule,
 pla.accounting_method accounting_method,
 xxen_util.meaning(pla.accounting_method,'PN_ACCT_METHOD_TYPE',0) representation,
 xxen_util.meaning('PROPERTY','PN_ECC_LEASE_CATEGORY',240) lease_category
from
 pn_amendment_report_gtt parg,
 pn_leases_all pla
where
    parg.lease_num=pla.lease_num
and nvl(pla.accounting_method,'BOTH') = decode(nvl(:p_representation,'BOTH'),'BOTH',nvl(pla.accounting_method,'BOTH'),:p_representation)
and exists
  (select
    'X'
   from
    pn_lease_details_all plda
   where
    plda.lease_id = pla.lease_id
    and trunc(xxen_pn.get_as_of_date_start(pla.lease_id)) <= trunc(plda.lease_termination_date)
    and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
  )
union
select
 case when parg.amendment_type like '%(InterCompany)%' then 'Y' else 'N' end ico_flag,
 parg.lease_num,
 parg.lease_name,
 parg.amendment_date,
 parg.effective_date,
 parg.amendment_type,
 parg.amt_per_period amt_diff_per_period,
 parg.tot_period no_of_periods,
 parg.tot_amt tot_diff_amt,
 parg.currency_code,
 decode(pla.payment_term_proration_rule,365,'365 Days/Year',360,'360 Days/Year',999,'Days/Month',null) payment_term_proration_rule,
 pla.accounting_method accounting_method,
 xxen_util.meaning(pla.accounting_method,'PN_ACCT_METHOD_TYPE',0) representation,
 xxen_util.meaning('EQUIPMENT','PN_ECC_LEASE_CATEGORY',240) lease_category
from
 pn_amendment_report_gtt parg,
 pn_eqp_leases_all pla
where
    parg.lease_num=pla.lease_num
and nvl(pla.accounting_method,'BOTH') = decode(nvl(:p_representation,'BOTH'),'BOTH',nvl(pla.accounting_method,'BOTH'),:p_representation)
and exists
  (select
    'X'
   from
    pn_eqp_lease_details_all plda
   where
    plda.lease_id = pla.lease_id
    and trunc(xxen_pn.get_as_of_date_start(pla.lease_id)) <= trunc(plda.lease_termination_date)
    and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
  )
)
--
--
-- =================================
-- Main Query Starts Here
-- =================================
--
select
 x.legal_entity,
 x.ledger,
 x.operating_unit,
 x.functional_currency,
 x.report_as_of_period,
 --
 x.record_type,
 xxen_util.meaning(x.ico_flag,'YES_NO',0) intercompany_flag,
 x.currency,
 -- portfolio_summary
 x.period,
 x.liability,
 x.cash,
 x.interest_accrual,
 x.fin_rou_asset,
 x.fin_rou_amort_expense,
 x.opr_rou_asset,
 x.opr_rou_amort_expense,
 x.lease_expense,
 -- Amendment Details
 x.lease_num,
 x.lease_name,
 x.lease_category,
 x.representation,
 trunc(x.amendment_date) amendment_date,
 trunc(x.effective_date) effective_date,
 x.amendment_type,
 x.amount_per_period,
 x.number_of_periods,
 x.total_change_amount,
 --
 x.report_status,
 nvl2(x.lease_name,x.lease_num || ' - ' || x.lease_name,null) lease_num_name_label,
 nvl2(lease_name,x.lease_name || ' (' || x.lease_num || ')',null) lease_name_num_label,
 decode(x.ico_flag,'Y','Intercompany','Non Intercompany') intercompany_label,
 x.period_start_date
from
(
select
 'Period Balance'         record_type,
 :p_legal_entity          legal_entity,
 :p_ledger_name           ledger,
 :p_org_name              operating_unit,
 :p_functional_currency   functional_currency,
 :p_as_of_period          report_as_of_period,
 :p_end_date              report_end_date,
 pn_lease_portfolio_reports.get_report_status(mo_global.get_current_org_id,:p_period_end_date) report_status,
 --
 qps.currency_code currency,
 -- portfolio_summary
 qps.period,
 qps.period_start_date,
 qps.liability,
 qps.cash,
 qps.interest_accrual,
 qps.rou_asset_fin        fin_rou_asset,
 qps.rou_amort_fin        fin_rou_amort_expense,
 qps.rou_asset_opr        opr_rou_asset,
 qps.rou_amort_opr        opr_rou_amort_expense,
 qps.lease_expenses       lease_expense,
 -- Amendment Details
 null                     lease_num,
 null                     lease_name,
 null                     lease_category,
 null                     representation,
 null                     accounting_method,
 to_date(null)            amendment_date,
 to_date(null)            effective_date,
 null                     amendment_type,
 to_number(null)          amount_per_period,
 to_number(null)          number_of_periods,
 to_number(null)          total_change_amount,
 --
 qps.ico_flag,
 1 rec_group,
 qps.seq
from
 q_portfolio_summary qps
where
 :p_incl_balances is not null
union
select
 'Lease Amendment'        record_type,
 :p_legal_entity          legal_entity,
 :p_ledger_name           ledger,
 :p_org_name              operating_unit,
 :p_functional_currency   functional_currency,
 :p_as_of_period          report_as_of_period,
 :p_end_date              report_end_date,
 pn_lease_portfolio_reports.get_report_status(mo_global.get_current_org_id,:p_period_end_date) report_status,
 --
 qad.currency_code        currency,
 -- portfolio_summary
 null                     period,
 to_date(null)            period_start_date,
 to_number(null)          liability,
 to_number(null)          cash,
 to_number(null)          interest_accrual,
 to_number(null)          fin_rou_asset,
 to_number(null)          fin_rou_amort_expense,
 to_number(null)          opr_rou_asset,
 to_number(null)          opr_rou_amort_expense,
 to_number(null)          lease_expense,
 -- Amendment Details
 qad.lease_num,
 qad.lease_name,
 qad.lease_category,
 qad.representation,
 qad.accounting_method,
 qad.amendment_date,
 qad.effective_date,
 qad.amendment_type,
 qad.amt_diff_per_period  amount_per_period,
 qad.no_of_periods        number_of_periods,
 qad.tot_diff_amt         total_change_amount,
 --
 qad.ico_flag,
 2 rec_group,
 to_number(null) seq
from
 q_amendment_detail qad
where
 :p_incl_amendments is not null
) x
order by
 x.ico_flag,
 x.rec_group,
 x.lease_num,
 x.period_start_date,
 x.currency,
 x.seq