with
--
q_portfolio_detail as
(
select
plrdg.intercompany_flag ico_flag,
decode( plrdg.as_of_period, plrdg.lease_num, plrdg.as_of_period) lease_num,
plrdg.lease_name lease_name,
plrdg.lia_bal_start,
plrdg.liability,
plrdg.cash,
plrdg.interest_accrual,
plrdg.rou_bal_start_fin,
plrdg.rou_asset_fin,
plrdg.rou_amort_fin,
plrdg.rou_bal_start_us_opr,
plrdg.rou_asset_opr,
plrdg.rou_amort_opr,
plrdg.lease_expenses,
plrdg.currency_code,
plrdg.seq,
plrdg.lia_bal_adj,
plrdg.rou_adj_fin,
plrdg.rou_adj_us_opr,
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_lease_reports_data_gtt plrdg,
pn_leases_all pla
where
plrdg.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
plrdg.intercompany_flag ico_flag,
decode( plrdg.as_of_period, plrdg.lease_num, plrdg.as_of_period) lease_num,
plrdg.lease_name lease_name,
plrdg.lia_bal_start,
plrdg.liability,
plrdg.cash,
plrdg.interest_accrual,
plrdg.rou_bal_start_fin,
plrdg.rou_asset_fin,
plrdg.rou_amort_fin,
plrdg.rou_bal_start_us_opr,
plrdg.rou_asset_opr,
plrdg.rou_amort_opr,
plrdg.lease_expenses,
plrdg.currency_code,
plrdg.seq,
plrdg.lia_bal_adj,
plrdg.rou_adj_fin,
plrdg.rou_adj_us_opr,
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_lease_reports_data_gtt plrdg,
pn_eqp_leases_all pla
where
plrdg.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))
),
--
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 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 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 period,
--
x.record_type,
xxen_util.meaning(x.ico_flag,'YES_NO',0) intercompany_flag,
x.lease_num,
x.lease_name,
x.lease_category,
x.representation,
x.currency,
-- portfolio_detail
x.liability_start_balance,
x.liability_close_balance,
x.cash,
x.interest_accrual,
x.liability_adjustment,
x.fin_rou_start_balance,
x.fin_rou_closing_balance,
x.fin_rou_amort_expense,
x.fin_rou_adjustments,
x.opr_rou_start_balance,
x.opr_rou_close_balance,
x.opr_rou_amort_expense,
x.opr_rou_adjustments,
x.lease_expense,
-- Amendment Details
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
from
(
select
'Lease 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,
--
qpd.lease_num,
qpd.lease_name,
qpd.lease_category,
qpd.accounting_method,
qpd.representation,
qpd.currency_code currency,
-- portfolio_detail
qpd.lia_bal_start liability_start_balance,
qpd.liability liability_close_balance,
qpd.cash cash,
qpd.interest_accrual interest_accrual,
qpd.lia_bal_adj liability_adjustment,
qpd.rou_bal_start_fin fin_rou_start_balance,
qpd.rou_asset_fin fin_rou_closing_balance,
qpd.rou_amort_fin fin_rou_amort_expense,
qpd.rou_adj_fin fin_rou_adjustments,
qpd.rou_bal_start_us_opr opr_rou_start_balance,
qpd.rou_asset_opr opr_rou_close_balance,
qpd.rou_amort_opr opr_rou_amort_expense,
qpd.rou_adj_us_opr opr_rou_adjustments,
qpd.lease_expenses lease_expense,
-- Amendment Details
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,
qpd.ico_flag,
1 rec_group,
qpd.seq
from
q_portfolio_detail qpd
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.lease_num,
qad.lease_name,
qad.lease_category,
qad.accounting_method,
qad.representation,
qad.currency_code currency,
-- portfolio_detail
to_number(null) liability_start_balance,
to_number(null) liability_close_balance,
to_number(null) cash,
to_number(null) interest_accrual,
to_number(null) liability_adjustment,
to_number(null) fin_rou_start_balance,
to_number(null) fin_rou_closing_balance,
to_number(null) fin_rou_amort_expense,
to_number(null) fin_rou_adjustments,
to_number(null) opr_rou_start_balance,
to_number(null) opr_rou_close_balance,
to_number(null) opr_rou_amort_expense,
to_number(null) opr_rou_adjustments,
to_number(null) lease_expense,
-- Amendment Details
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,
decode(x.seq,4,2,1),
x.lease_num,
x.currency,
x.seq |