PN Generate Lease Details

Description
Categories: Enginatics
Repository: Github
Application: Property Manager
Source: Generate Lease Details Report
Short Name: PNGLDR_XML
DB package: XXEN_PN
with
--
--q_lease -- Lease Header
--
q_lease as
(
select distinct
 pla.lease_id,
 pla.lease_num lease_num,
 pla.name lease_name,
 xxen_pn.get_lease_rate_value(pla.lease_id) rate_value,
 xxen_pn.get_lease_discount_rate(pla.lease_id,xxen_pn.get_as_of_date(pla.lease_id)) lease_discount_rate,
 xxen_pn.pngldr_report_status(pla.lease_id,xxen_pn.get_as_of_date(pla.lease_id)) report_status,
 xxen_util.meaning('PROPERTY','PN_ECC_LEASE_CATEGORY',240) lease_category
from
 pn_leases_all pla
where
    1=1
and pla.org_id = :p_org_id
and pla.status = 'F'
and pla.lease_status in ('ACT','TER')
and pla.lease_class_code = 'DIRECT'
and 'PROPERTY' = nvl(xxen_util.lookup_code(:p_lease_category,'PN_ECC_LEASE_CATEGORY',240),'PROPERTY')
union
select distinct
 pla.lease_id,
 pla.lease_num lease_num,
 pla.name lease_name,
 xxen_pn.get_lease_rate_value(pla.lease_id) rate_value,
 xxen_pn.get_lease_discount_rate(pla.lease_id,xxen_pn.get_as_of_date(pla.lease_id)) lease_discount_rate,
 xxen_pn.pngldr_report_status(pla.lease_id,xxen_pn.get_as_of_date(pla.lease_id)) report_status,
 xxen_util.meaning('EQUIPMENT','PN_ECC_LEASE_CATEGORY',240) lease_category
from
 pn_eqp_leases_all pla
where
    1=1
and pla.org_id = :p_org_id
and pla.status = 'F'
and pla.lease_status in ('ACT','TER')
and pla.lease_class_code = 'DIRECT'
and 'EQUIPMENT' = nvl(xxen_util.lookup_code(:p_lease_category,'PN_ECC_LEASE_CATEGORY',240),'EQUIPMENT')
),
--
--q_lease_detail -- Lease Details
--
q_lease_detail as
(
select --Q1
 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,
 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) lease_representation,
 decode(xxen_pn.get_lease_data_source,'P','Production data','A','Archive') lease_source
from
 pn_leases_all pla,
 pn_lease_details_all plda
where
    pla.lease_id = plda.lease_id
and xxen_pn.get_lease_data_source = 'P'
and (nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(pla.lease_id))) >= trunc(xxen_pn.get_as_of_date(pla.lease_id)) or pn_system_pub.get_frozen_flag (:p_org_id) = 'N')
and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
union
select --Q2
 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,
 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) lease_representation,
 decode(xxen_pn.get_lease_data_source,'P','Production data','A','Archive') lease_source
from
 pn_leases_all pla,
 pn_lease_details_all plda
where
    pla.lease_id = plda.lease_id
and xxen_pn.get_lease_data_source = 'A'
and exists (select 1 from pn_pmt_item_pv_all pivh where pivh.lease_id=pla.lease_id and pivh.as_of_date = xxen_pn.get_as_of_date(pla.lease_id))
and exists
  (select 1 from pn_payment_terms_all pt where pt.lease_id=pla.lease_id and pt.rept_inception_flag = 'Y'
   union all
   select 1 from pn_options_all po where po.lease_id=pla.lease_id and po.rept_inception_flag='Y'
  )
and (   nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(pla.lease_id))) >= trunc(xxen_pn.get_as_of_date(pla.lease_id))
     or pn_system_pub.get_frozen_flag (:p_org_id) = 'N'
    )
and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
union
select --Q3
 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,
 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) lease_representation,
 decode(xxen_pn.get_lease_data_source,'P','Production data','A','Archive') lease_source
from
 pn_leases_hist pla,
 pn_lease_details_hist plda
where
    pla.lease_id = plda.lease_id
and xxen_pn.get_lease_data_source = 'A'
and not exists (select 1 from pn_pmt_item_pv_all pivh where pivh.lease_id=pla.lease_id and pivh.as_of_date = xxen_pn.get_as_of_date(pla.lease_id))
and trunc(pla.as_of_date) = trunc((select max(as_of_date) from pn_leases_hist plhh where plhh.lease_num=pla.lease_num and trunc(plhh.as_of_date) <= trunc(xxen_pn.get_as_of_date(pla.lease_id))))
and trunc(plda.as_of_date) = trunc((select max(as_of_date) from pn_leases_hist plhh where plhh.lease_num=pla.lease_num and trunc(plhh.as_of_date) <= trunc(xxen_pn.get_as_of_date(pla.lease_id))))
and exists
  (select 1 from  pn_payment_terms_all pt where pt.lease_id=pla.lease_id and pt.rept_inception_flag = 'Y'
   union all
   select 1 from pn_options_all po where po.lease_id=pla.lease_id and po.rept_inception_flag='Y'
  )
and (nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(pla.lease_id))) >= trunc(xxen_pn.get_as_of_date(pla.lease_id)) or pn_system_pub.get_frozen_flag (:p_org_id) = 'N' )
and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
union
select --Q4
 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,
 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) lease_representation,
 decode(xxen_pn.get_lease_data_source,'P','Production data','A','Archive') lease_source
from
 pn_leases_hist pla,
 pn_lease_details_hist plda
where
    pla.lease_id = plda.lease_id
and xxen_pn.get_lease_data_source = 'A'
and trunc(pla.as_of_date) = trunc((select max(as_of_date) from pn_leases_hist plhh where plhh.lease_num=pla.lease_num and trunc(plhh.as_of_date) <= trunc(xxen_pn.get_as_of_date(pla.lease_id))))
and trunc(plda.as_of_date) = trunc((select max(as_of_date) from pn_leases_hist plhh where plhh.lease_num=pla.lease_num and trunc(plhh.as_of_date) <= trunc(xxen_pn.get_as_of_date(pla.lease_id))))
and exists (select 1 from pn_leases_hist plh where plh.lease_num=pla.lease_num)
and not exists
  (select 1 from pn_payment_terms_all pt where pt.lease_id=pla.lease_id and pt.rept_inception_flag = 'Y'
   union all
   select 1 from pn_options_all po where po.lease_id=pla.lease_id and po.rept_inception_flag='Y'
  )
and (nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(pla.lease_id))) >= trunc(xxen_pn.get_as_of_date(pla.lease_id)) or pn_system_pub.get_frozen_flag (:p_org_id) = 'N')
and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
union
select --Q5
 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,
 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) lease_representation,
 decode(xxen_pn.get_lease_data_source,'P','Production data','A','Archive') lease_source
from
 pn_leases_all pla,
 pn_lease_details_all plda
where
    pla.lease_id = plda.lease_id
and xxen_pn.get_lease_data_source = 'A'
and not exists (select 1 from pn_leases_hist plh where plh.lease_num=pla.lease_num)
and not exists
  (select 1 from pn_payment_terms_all pt where pt.lease_id=pla.lease_id and pt.rept_inception_flag = 'Y'
   union all
   select 1 from pn_options_all po where po.lease_id=pla.lease_id and po.rept_inception_flag='Y'
  )
and (nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(pla.lease_id))) >= trunc(xxen_pn.get_as_of_date(pla.lease_id)) or pn_system_pub.get_frozen_flag (:p_org_id) = 'N')
and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
union --Q6
select
 lease_id,
 lease_num,
 lease_name,
 lease_commencement_date lease_commencement_date,
 lease_termination_date lease_termination_date,
 months_between(lease_termination_date + 1, lease_commencement_date) duration_in_months,
 payment_term_proration_rule,
 accounting_method,
 lease_representation,
 lease_source
from
 (select
   pla.lease_id lease_id,
   pla.lease_num,
   pla.name lease_name,
   plda.lease_commencement_date lease_commencement_date,
   nvl(pn_transaction_util.get_change_date(pla.lease_id, trunc(xxen_pn.get_as_of_date(pla.lease_id)), 'TERMINATION'),plda.lease_termination_date) lease_termination_date,
   decode(pla.payment_term_proration_rule,365,'365 Days/Year',360,'360 Days/Year',999,'Days/Month',null) payment_term_proration_rule,
   xxen_util.meaning(pla.accounting_method,'PN_ACCT_METHOD_TYPE',0) lease_representation,
   pla.accounting_method accounting_method,
   'Production data' lease_source
  from
   pn_leases_all pla,
   pn_lease_details_all plda
  where
      pla.lease_id = plda.lease_id
  and nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(pla.lease_id))) < trunc(xxen_pn.get_as_of_date(pla.lease_id))
  and pn_system_pub.get_frozen_flag (:p_org_id) = 'Y'
  and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
 )
union --Q7
select
 lease_id,
 lease_num,
 lease_name,
 lease_commencement_date lease_commencement_date,
 lease_termination_date lease_termination_date,
 months_between(lease_termination_date + 1, lease_commencement_date) duration_in_months,
 payment_term_proration_rule,
 accounting_method,
 lease_representation,
 lease_source
from
 (select
   pla.lease_id lease_id,
   pla.lease_num,
   pla.name lease_name,
   nvl(pn_transaction_util.get_change_date(pla.lease_id, trunc(xxen_pn.get_as_of_date(pla.lease_id)), 'COMMENCEMENT'),plda.lease_commencement_date) lease_commencement_date,
   nvl(pn_transaction_util.get_change_date(pla.lease_id, trunc(xxen_pn.get_as_of_date(pla.lease_id)), 'TERMINATION'),plda.lease_termination_date) lease_termination_date,
   decode(pla.payment_term_proration_rule,365,'365 Days/Year',360,'360 Days/Year',999,'Days/Month',null) payment_term_proration_rule,
   xxen_util.meaning(pla.accounting_method,'PN_ACCT_METHOD_TYPE',0) lease_representation,
   pla.accounting_method accounting_method,
   'Production data' lease_source
  from
   pn_eqp_leases_all pla,
   pn_eqp_lease_details_all plda
  where
      pla.lease_id = plda.lease_id
  and trunc(xxen_pn.get_as_of_date(pla.lease_id)) >= trunc(plda.lease_commencement_date)
 )
),
--
-- q_lease_report -- Lease Reporting
--
q_lease_report as
(
select
 nvl(period,'Future Periods (Not Defined in GL)') period,
 period_start_date,
 currency,
 liability,
 cash,
 interest_accrual,
 rou_asset_fin,
 rou_amort_fin,
 rou_asset_opr,
 rou_amort_opr,
 lease_expenses,
 lease_id
from
 (select
   period_name period,
   period_start_date,
   currency_code currency,
   sum(nvl(lia_bal_end,0)) liability,
   sum(nvl(actual_pmt_amt,0)) cash,
   sum(nvl(lia_intrst_amt,0)) interest_accrual,
   sum(nvl(rou_bal_end_fin,0)) rou_asset_fin,
   sum(nvl(rou_amrtztn_amt_fin,0)) rou_amort_fin,
   decode(xxen_pn.get_lease_accounting_method(smry.lease_id),'FINANCE',to_number(null),sum(nvl(rou_bal_end_us_opr,0)) ) rou_asset_opr,
   decode(xxen_pn.get_lease_accounting_method(smry.lease_id),'FINANCE',to_number(null),sum(nvl(rou_amrtztn_amt_us_opr,0)) ) rou_amort_opr,
   decode(xxen_pn.get_lease_accounting_method(smry.lease_id),'FINANCE',to_number(null),sum(nvl(exp_amt,0)) ) lease_expenses,
   lease_id
  from
   (select
     piaa.option_id,
     piaa.payment_term_id,
     piaa.org_id,
     piaa.lease_id,
     piaa.as_of_date,
     piaa.period_date,
     decode((select calc_frequency from pn_system_setup_options where org_id =:p_org_id),
            null, to_char(to_date(to_char(trunc(period_date),'YYYY-MM-DD'),'YYYY-MM-DD'),'Mon-YYYY'),
            'PERIODICAL',to_char(to_date(to_char(trunc(period_date),'YYYY-MM-DD'),'YYYY-MM-DD' ),'Mon-YYYY'),
            (select
              glp.period_name
             from
              gl_periods glp,
              gl_sets_of_books glsob,
              pn_system_setup_options imp
             where
              glsob.period_set_name = glp.period_set_name
              and glp.period_type = glsob.accounted_period_type
              and glp.adjustment_period_flag <> 'Y'
              and glsob.set_of_books_id = imp.set_of_books_id
              and trunc(piaa.period_date) between trunc(glp.start_date) and trunc(glp.end_date)
              and imp.org_id =:p_org_id
            )
           ) period_name,
     decode((select calc_frequency from pn_system_setup_options where org_id =:p_org_id),
            null,trunc(last_day(period_date)),
            'PERIODICAL',trunc(last_day(period_date)),
            (select
              trunc(glp.start_date)
             from
              gl_periods glp,
              gl_sets_of_books glsob,
              pn_system_setup_options imp
             where
              glsob.period_set_name = glp.period_set_name
              and glp.period_type = glsob.accounted_period_type
              and glp.adjustment_period_flag <> 'Y'
              and glsob.set_of_books_id = imp.set_of_books_id
              and trunc(piaa.period_date) between trunc(glp.start_date) and trunc(glp.end_date)
              and imp.org_id =:p_org_id
             )
            ) period_start_date,
     piaa.lia_bal_end,
     piaa.currency_code,
     nvl((select sum(decode(nvl(pipa.actual_amount_rou,0),0,nvl(pipa.actual_amount_liability,0),nvl(pipa.actual_amount_rou,0)) )
          from pn_pmt_item_pv_all pipa
          where
              trunc(pipa.as_of_date) = trunc(xxen_pn.get_as_of_date(piaa.lease_id))
          and pipa.lease_id = piaa.lease_id
          and trunc(piaa.period_date) between add_months( (last_day(pipa.due_date) + 1),-1) and last_day(pipa.due_date )
          and (   (piaa.payment_term_id is not null and pipa.payment_term_id = piaa.payment_term_id)
               or (piaa.option_id is not null and pipa.option_id = piaa.option_id)
              )
         ),0) actual_pmt_amt,
     piaa.lia_intrst_amt,
     piaa.rou_bal_end_fin,
     piaa.rou_amrtztn_amt_fin,
     piaa.rou_bal_end_us_opr,
     piaa.rou_amrtztn_amt_us_opr,
     decode(pn_transaction_util.get_liab_only_flag(piaa.payment_term_id,piaa.option_id),'Y',piaa.lia_intrst_amt,piaa.exp_amt) exp_amt
    from
     pn_pmt_item_amrtzn_all piaa
    where
        trunc(piaa.as_of_date) = trunc(xxen_pn.get_as_of_date(piaa.lease_id))
    and (   nvl(pn_transaction_util.get_transition_date(:p_org_id),trunc(xxen_pn.get_as_of_date(piaa.lease_id)) ) >= trunc (xxen_pn.get_as_of_date(piaa.lease_id))
         or pn_system_pub.get_frozen_flag(:p_org_id) = 'N'
        )
    union
    select
     piah.option_id,
     piah.payment_term_id,
     piah.org_id,
     piah.lease_id,
     piah.as_of_date,
     piah.period_date,
     decode((select calc_frequency from pn_system_setup_options where org_id =:p_org_id),
            null,to_char(to_date(to_char(trunc(piah.period_date),'YYYY-MM-DD'),'YYYY-MM-DD'),'Mon-YYYY'),
            'PERIODICAL',to_char(to_date(to_char(trunc(piah.period_date),'YYYY-MM-DD'),'YYYY-MM-DD' ),'Mon-YYYY'),
            (select
              glp.period_name
             from
              gl_periods glp,
              gl_sets_of_books glsob,
              pn_system_setup_options imp
             where
                 glsob.period_set_name = glp.period_set_name
             and glp.period_type = glsob.accounted_period_type
             and glp.adjustment_period_flag <> 'Y'
             and glsob.set_of_books_id = imp.set_of_books_id
             and trunc(piah.period_date) between trunc(glp.start_date) and trunc(glp.end_date)
             and imp.org_id =:p_org_id
            )
           ) period_name,
     decode((select calc_frequency from pn_system_setup_options where org_id =:p_org_id),
            null,trunc(last_day(piah.period_date)),
            'PERIODICAL',trunc(last_day(piah.period_date)),
            (select
              trunc(glp.start_date)
             from
              gl_periods glp,
              gl_sets_of_books glsob,
              pn_system_setup_options imp
             where
                 glsob.period_set_name = glp.period_set_name
             and glp.period_type = glsob.accounted_period_type
             and glp.adjustment_period_flag <> 'Y'
             and glsob.set_of_books_id = imp.set_of_books_id
             and trunc(piah.period_date) between trunc(glp.start_date) and trunc