PN Generate Lease Details
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Property Manager
Source: Generate Lease Details Report
Short Name: PNGLDR_XML
DB package: XXEN_PN
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 |