PN Generate Lease Analysis

with
q_lease_detail as
(
select --Q1
 pla.org_id,
 pla.lease_id lease_id,
 pla.lease_num,
 pla.name lease_name,
 plda.lease_commencement_date lease_commencement_date,
 plda.lease_termination_date lease_termination_date,
 months_between(plda.lease_termination_date + 1, plda.lease_commencement_date) duration_in_months,
 ((pn_streams_util.get_interest_rate(pla.discount_rate_index_id, trunc(xxen_pn.get_as_of_date(pla.lease_id))) * 100) + nvl(pla.adder_rate, 0)) discount_rate,
 ppta.currency_code currency,
 xxen_util.meaning('PROPERTY','PN_ECC_LEASE_CATEGORY',240) lease_category,
 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_pn.get_as_of_date(pla.lease_id) as_of_date
from
 pn_leases_all pla,
 pn_lease_details_all plda,
 pn_payment_terms_all ppta
where
    pla.lease_id = plda.lease_id
and pla.lease_id = ppta.lease_id
and nvl(pla.accounting_method,'BOTH') = decode(nvl(:p_representation,'BOTH'),'BOTH',nvl(pla.accounting_method,'BOTH'),:p_representation)
and pla.org_id = :p_org_id
and pla.lease_status = 'ACT'
and pla.status = 'F'
and pla.lease_class_code = 'DIRECT'
union
select --Q2
 pla.org_id,
 pla.lease_id lease_id,
 pla.lease_num,
 pla.name lease_name,
 plda.lease_commencement_date lease_commencement_date,
 plda.lease_termination_date lease_termination_date,
 months_between(plda.lease_termination_date + 1, plda.lease_commencement_date) duration_in_months,
 ((pn_streams_util.get_interest_rate(pla.discount_rate_index_id, trunc(xxen_pn.get_as_of_date(pla.lease_id))) * 100) + nvl(pla.adder_rate, 0)) discount_rate,
 nvl(poa.currency_code, pn_lease_pvt.get_functional_currency(pla.org_id)) currency_code,
 xxen_util.meaning('PROPERTY','PN_ECC_LEASE_CATEGORY',240) lease_category,
 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_pn.get_as_of_date(pla.lease_id) as_of_date
from
 pn_leases_all pla,
 pn_lease_details_all plda,
 pn_options_all poa
where
    pla.lease_id = plda.lease_id
and pla.lease_id = poa.lease_id
and nvl(pla.accounting_method,'BOTH') = decode(nvl(:p_representation,'BOTH'),'BOTH',nvl(pla.accounting_method,'BOTH'),:p_representation)
and pla.org_id = :p_org_id
and pla.lease_status = 'ACT'
and pla.status = 'F'
and pla.lease_class_code = 'DIRECT'
union
select --Q3
 pla.org_id,
 pla.lease_id lease_id,
 pla.lease_num,
 pla.name lease_name,
 plda.lease_commencement_date lease_commencement_date,
 plda.lease_termination_date lease_termination_date,
 months_between(plda.lease_termination_date + 1, plda.lease_commencement_date) duration_in_months,
 ((pn_streams_util.get_interest_rate(pla.discount_rate_index_id, trunc(xxen_pn.get_as_of_date(pla.lease_id))) * 100) + nvl(pla.adder_rate, 0)) discount_rate,
 ppta.currency_code currency,
 xxen_util.meaning('EQUIPMENT','PN_ECC_LEASE_CATEGORY',240) lease_category,
 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_pn.get_as_of_date(pla.lease_id) as_of_date
from
 pn_eqp_leases_all pla,
 pn_eqp_lease_details_all plda,
 pn_eqp_payment_terms_all ppta
where
    pla.lease_id = plda.lease_id
and pla.lease_id = ppta.lease_id
and nvl(pla.accounting_method,'BOTH') = decode(nvl(:p_representation,'BOTH'),'BOTH',nvl(pla.accounting_method,'BOTH'),:p_representation)
and pla.org_id = :p_org_id
and pla.lease_status = 'ACT'
and pla.status = 'F'
and pla.lease_class_code = 'DIRECT'
union
select --Q4
 pla.org_id,
 pla.lease_id lease_id,
 pla.lease_num,
 pla.name lease_name,
 plda.lease_commencement_date lease_commencement_date,
 plda.lease_termination_date lease_termination_date,
 months_between(plda.lease_termination_date + 1, plda.lease_commencement_date) duration_in_months,
 ((pn_streams_util.get_interest_rate(pla.discount_rate_index_id, trunc(xxen_pn.get_as_of_date(pla.lease_id))) * 100) + nvl(pla.adder_rate, 0)) discount_rate,
 poa.currency_code,
 xxen_util.meaning('EQUIPMENT','PN_ECC_LEASE_CATEGORY',240) lease_category,
 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_pn.get_as_of_date(pla.lease_id) as_of_date
from
 pn_eqp_leases_all pla,
 pn_eqp_lease_details_all plda,
 pn_eqp_options_all poa
where
     pla.lease_id = plda.lease_id
and pla.lease_id = poa.lease_id
and nvl(pla.accounting_method,'BOTH') = decode(nvl(:p_representation,'BOTH'),'BOTH',nvl(pla.accounting_method,'BOTH'),:p_representation)
and pla.org_id = :p_org_id
and pla.lease_status = 'ACT'
and pla.status = 'F'
and pla.lease_class_code = 'DIRECT'
)
--
-- Main Query Starts Here
--
select
 :p_legal_entity legal_entity,
 :p_ledger_name ledger,
 :p_org_name operating_unit,
 :p_functional_currency functional_currency,
 :p_as_of_period as_of_period,
 qld.as_of_date as_of_date,
 qld.lease_num,
 qld.lease_name,
 qld.lease_category,
 qld.lease_commencement_date commencement_date,
 qld.lease_termination_date termination_date,
 qld.duration_in_months,
 qld.payment_term_proration_rule days_convention,
 qld.discount_rate discount_rate_pct,
 qld.representation representation,
 qld.currency currency,
 --
 pn_transaction_util.get_lease_liability
 (p_lease_id => qld.lease_id,
  p_org_id => qld.org_id,
  p_currency => qld.currency,
  p_as_of_date => trunc(qld.as_of_date),
  p_termination_date => trunc(qld.lease_termination_date),
  p_month => 12,
  p_mode => 'U'
 ) undisc_liability_le_1yr,
 pn_transaction_util.get_lease_liability
 (p_lease_id => qld.lease_id,
  p_org_id => qld.org_id,
  p_currency => qld.currency,
  p_as_of_date => trunc(qld.as_of_date),
  p_termination_date => trunc(qld.lease_termination_date),
  p_month => 60,
  p_mode => 'U'
 ) undisc_liability_le_5yr,
 pn_transaction_util.get_liability_more
 (p_lease_id => qld.lease_id,
  p_org_id => qld.org_id,
  p_currency => qld.currency,
  p_as_of_date => trunc(qld.as_of_date),
  p_termination_date => trunc(qld.lease_termination_date),
  p_month => 60,
  p_mode => 'U'
 ) undisc_liability_gt_5yr,
 pn_transaction_util.get_lease_liability
 (p_lease_id => qld.lease_id,
  p_org_id => qld.org_id,
  p_currency => qld.currency,
  p_as_of_date => trunc(qld.as_of_date),
  p_termination_date => trunc(qld.lease_termination_date),
  p_month => 12,
  p_mode => 'D'
 ) disc_liability_le_1yr,
 pn_transaction_util.get_lease_liability
 (p_lease_id => qld.lease_id,
  p_org_id => qld.org_id,
  p_currency => qld.currency,
  p_as_of_date => trunc(qld.as_of_date),
  p_termination_date => trunc(qld.lease_termination_date),
  p_month => 60,
  p_mode => 'D'
 ) disc_liability_le_5yr,
 pn_transaction_util.get_liability_more
 (p_lease_id => qld.lease_id,
  p_org_id => qld.org_id,
  p_currency => qld.currency,
  p_as_of_date => trunc(qld.as_of_date),
  p_termination_date => trunc(qld.lease_termination_date),
  p_month => 60,
  p_mode => 'D'
 ) disc_liability_gt_5yr,
 qld.accounting_method,
 :p_source source,
 qld.lease_num || ' - ' || qld.lease_name lease_num_name_label, 
 qld.lease_name || ' (' || qld.lease_num || ')' lease_name_num_label
from
 q_lease_detail qld
where
 1=1 and
 :p_org_name = :p_org_name and
 qld.lease_category = nvl(:p_lease_category,qld.lease_category)
order by
 qld.lease_num