[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROUND(carry_forward_amount, p_l_precision) carry_forward_amount,
carry_forward_percent,
ROUND (constraint_rent_due, p_l_precision) old_rent
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND assessment_date =
ADD_MONTHS (
term_rec.ilp_assessment_date,
-12 * term_rec.assessment_interval
);
AND loc.rowid = (select max(loc1.rowid) from pn_locations_all loc1
where loc.location_id = loc1.location_id)
AND ten.location_id = '
|| ''''
|| p_location_id
|| '''';
', PN_TENANCIES_ALL ten, (select bld.location_id, bld.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
pn_properties_all prop,
pn_locations_all bld
WHERE
bld.location_type_lookup_code IN ('
|| ''''
|| 'BUILDING'
|| ''''
|| ','
|| ''''
|| 'LAND'
|| ''''
|| ') AND
bld.property_id = prop.property_id (+)
UNION
select flr.location_id, flr.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
pn_properties_all prop,
pn_locations_all bld,
pn_locations_all flr
WHERE flr.location_type_lookup_code IN ('
|| ''''
|| 'FLOOR'
|| ''''
|| ','
|| ''''
|| 'PARCEL'
|| ''''
|| ') AND
bld.property_id = prop.property_id (+) AND
bld.location_id = flr.parent_location_id AND
((flr.active_start_date BETWEEN bld.active_start_date AND
bld.active_end_date) OR (flr.active_end_date BETWEEN bld.active_start_date AND
bld.active_end_date))
UNION
select off.location_id, off.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
pn_properties_all prop,
pn_locations_all bld,
pn_locations_all flr,
pn_locations_all off
WHERE off.location_type_lookup_code IN ('
|| ''''
|| 'OFFICE'
|| ''''
|| ','
|| ''''
|| 'SECTION'
|| ''''
|| ') AND
bld.property_id = prop.property_id (+) AND
flr.location_id = off.parent_location_id AND
bld.location_id = flr.parent_location_id AND
((off.active_start_date BETWEEN flr.active_start_date AND
flr.active_end_date) OR (off.active_end_date BETWEEN flr.active_start_date AND
flr.active_end_date)) ) loc';
', PN_TENANCIES_ALL ten, (select bld.location_id, bld.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
pn_properties_all prop,
pn_locations_all bld
WHERE
bld.location_type_lookup_code IN ('
|| ''''
|| 'BUILDING'
|| ''''
|| ','
|| ''''
|| 'LAND'
|| ''''
|| ') AND
bld.property_id = prop.property_id (+)
UNION
select flr.location_id, flr.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
pn_properties_all prop,
pn_locations_all bld,
pn_locations_all flr
WHERE flr.location_type_lookup_code IN ('
|| ''''
|| 'FLOOR'
|| ''''
|| ','
|| ''''
|| 'PARCEL'
|| ''''
|| ') AND
bld.property_id = prop.property_id (+) AND
bld.location_id = flr.parent_location_id AND
((flr.active_start_date BETWEEN bld.active_start_date AND
bld.active_end_date) OR (flr.active_end_date BETWEEN bld.active_start_date AND
bld.active_end_date))
UNION
select off.location_id, off.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
pn_properties_all prop,
pn_locations_all bld,
pn_locations_all flr,
pn_locations_all off
WHERE off.location_type_lookup_code IN ('
|| ''''
|| 'OFFICE'
|| ''''
|| ','
|| ''''
|| 'SECTION'
|| ''''
|| ') AND
bld.property_id = prop.property_id (+) AND
flr.location_id = off.parent_location_id AND
bld.location_id = flr.parent_location_id AND
((off.active_start_date BETWEEN flr.active_start_date AND
flr.active_end_date) OR (off.active_end_date BETWEEN flr.active_start_date AND
flr.active_end_date)) ) loc';
AND loc.rowid = (select max(loc1.rowid) from pn_locations_all loc1
where loc.location_id = loc1.location_id) ';
OPEN c_lease_pn FOR 'SELECT
l.name --2
,l.lease_num --3
,l.payment_term_proration_rule --5
,l.abstracted_by_user --6
,l.lease_class_code --9
,ld.lease_commencement_date --12
,ld.lease_termination_date --13
,ld.lease_execution_date --14
,ld.lease_extension_end_date
,it.payment_purpose_code --16
,it.payment_term_type_code --17
,it.frequency_code --18
,it.start_date --19
,it. end_date --20
,it.vendor_id --21
,it.vendor_site_id --22
,it.target_date --23
,it.actual_amount --24
,it.estimated_amount --25
,it.currency_code --27
,it.rate --28
,it.customer_id --29
,it.customer_site_use_id --30
,it.normalize'
|| location_code_field
|| ',it.schedule_day
,it.cust_ship_site_id --34
,it.ap_ar_term_id --35
,it.cust_trx_type_id --36
,it.project_id --37
,it.task_id --38
,it.organization_id --39
,it.expenditure_type --40
,it.expenditure_item_date --41
,it.inv_rule_id --42
,it.account_rule_id --43
,it.salesrep_id --44
,it.status payterm_status --45
,it.index_term_indicator --47
,il.index_lease_id --53
,il.index_id --54
,il.commencement_date il_commencement_date --55
,il.termination_date il_termination_date --56
,il.index_lease_number --57
,il.assessment_date il_assessment_date --58
,il.assessment_interval --59
,il.spread_frequency --60
,il.basis_percent_default --62
,il.initial_basis --63
,il.base_index --64
,il.index_finder_method --66
,il.index_finder_months --67
,il.negative_rent_type --68
,il.increase_on --69
,il.basis_type --70
,il.reference_period --71
,il.base_year --72
,il.index_multiplier -- slk
,il.proration_rule -- slk
,ilp.assessment_date ilp_assessment_date --73
,ilp.basis_start_date --74
,ilp.basis_end_date --75
,ilp.index_finder_date --76
,ilp.current_basis --77
,ilp.relationship --78
,ilp.index_percent_change --79
,ilp.basis_percent_change --80
,ilp.unconstraint_rent_due --81
,ilp.constraint_rent_due --82
,ilp.carry_forward_amount
,ilp.carry_forward_percent
,ilp.constraint_applied_amount
,ilp.constraint_applied_percent
,ilp.current_index_line_value
,ilp.previous_index_line_value
,dist.account_id
,dist.account_class
,dist.percentage
,it.approved_by
,it.po_header_id
,it.receipt_method_id
,it.tax_code_id
,it.tax_group_id
,it.tax_included
,it.include_in_var_rent -- slk
FROM
pn_payment_terms_all it,
pn_leases_all l,
pn_lease_details_all ld,
pn_index_leases il ,
pn_distributions_all dist,
pn_index_lease_periods_all ilp'
|| location_code_from
|| property_name_from
|| building_name_from
|| ' WHERE it.lease_id = l.lease_id
AND l.lease_id = ld.lease_id
AND it.lease_id = il.lease_id
AND l.lease_id = il.lease_id
AND dist.payment_term_id = it.payment_term_id
AND it.index_period_id IS NOT NULL
AND ilp.index_lease_id = il.index_lease_id
AND ilp.index_period_id = it.index_period_id '
|| lease_number_where_clause
|| ri_number_where_clause
|| lease_date_where_clause
|| lease_class_where
|| location_code_where
|| property_name_where
|| building_name_where
|| account_class_where
|| account_id_where
|| rent_type_where
|| include_draft_where;
term_rec.last_update_date := SYSDATE;
term_rec.last_updated_by := l_user_id;
term_rec.last_update_login := l_user_id;
SELECT DECODE (
term_rec.frequency_meaning,
'Monthly', 12,
'Quarterly', 4,
'Semiannually', 2,
'Annually', 1,
1
) * ROUND(NVL(term_rec.actual_amount,0), l_precision)
INTO term_rec.annual
FROM DUAL;
INSERT INTO pn_rent_increase_detailacc_itf
(name,
lease_num,
payment_term_proration_rule,
abstracted_by_user_name,
lease_class_meaning,
lease_commencement_date,
lease_termination_date,
lease_execution_date,
lease_extension_end_date,
payment_purpose_meaning,
payment_term_type_meaning,
frequency_meaning,
start_date,
end_date,
vendor_name,
supplier_number,
vendor_site_code,
target_date,
actual_amount,
estimated_amount,
currency_code,
rate,
customer_name,
customer_number,
customer_bill_site,
normalize,
location_code,
location_type_lookup_code,
schedule_day,
customer_ship_site,
ap_ar_term_name,
cust_trx_type_name,
project_number,
task_number,
organization_name,
expenditure_type,
expenditure_item_date,
inv_rule_name,
account_rule_name,
salesrep_name,
payterm_status,
index_term_indicator,
index_name,
il_commencement_date,
il_termination_date,
index_lease_number,
il_assessment_date,
assessment_interval,
spread_frequency_meaning,
basis_percent_default,
initial_basis,
base_index,
index_finder_method,
index_finder_months,
negative_rent_type_meaning,
increase_on_meaning,
basis_type,
reference_period_meaning,
base_year,
ilp_assessment_date,
basis_start_date,
basis_end_date,
index_finder_date,
current_basis,
relationship_meaning,
index_percent_change,
basis_percent_change,
unconstraint_rent_due,
constraint_rent_due,
carry_forward_amount,
carry_forward_percent,
constraint_applied_amount,
constraint_applied_percent,
open_carry_forward_amount,
open_carry_forward_percent,
app_carry_forward_amount,
app_carry_forward_percent,
old_rent,
new_rent,
change_rent,
current_index_line_value,
previous_index_line_value,
account_class_meaning,
distribution_percentage,
distribution_amount,
gl_concatenated_segments,
approved_by_name,
po_number,
payment_method,
tax_code,
tax_group,
tax_included,
annual,
area,
annual_area,
constraint_proration, -- slk
index_multiplier, -- slk
unadjusted_index_change, -- slk
adjusted_index_change_percent,
include_in_var_rent, -- slk
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id
)
VALUES (term_rec.name,
term_rec.lease_num,
term_rec.payment_term_proration_rule,
term_rec.abstracted_by_user_name,
term_rec.lease_class_meaning,
term_rec.lease_commencement_date,
term_rec.lease_termination_date,
term_rec.lease_execution_date,
term_rec.lease_extension_end_date,
term_rec.payment_purpose_meaning,
term_rec.payment_term_type_meaning,
term_rec.frequency_meaning,
term_rec.start_date,
term_rec.end_date,
term_rec.vendor_name,
term_rec.supplier_number,
term_rec.vendor_site_code,
term_rec.target_date,
ROUND (term_rec.actual_amount, l_precision), -- slk
ROUND (term_rec.estimated_amount, l_precision), -- slk
term_rec.currency_code,
term_rec.rate,
term_rec.customer_name,
term_rec.customer_number,
term_rec.customer_bill_site,
term_rec.normalize,
term_rec.location_code,
term_rec.location_type_lookup_code,
term_rec.schedule_day,
term_rec.customer_ship_site,
term_rec.ap_ar_term_name,
term_rec.cust_trx_type_name,
term_rec.project_number,
term_rec.task_number,
term_rec.organization_name,
term_rec.expenditure_type,
term_rec.expenditure_item_date,
term_rec.inv_rule_name,
term_rec.account_rule_name,
term_rec.salesrep_name,
term_rec.payterm_status,
term_rec.index_term_indicator,
term_rec.index_name,
term_rec.il_commencement_date,
term_rec.il_termination_date,
term_rec.index_lease_number,
term_rec.il_assessment_date,
term_rec.assessment_interval,
term_rec.spread_frequency_meaning,
term_rec.basis_percent_default,
ROUND (term_rec.initial_basis, l_precision), -- slk
term_rec.base_index,
term_rec.index_finder_method,
term_rec.index_finder_months,
term_rec.negative_rent_type_meaning,
term_rec.increase_on_meaning,
term_rec.basis_type,
term_rec.reference_period_meaning,
term_rec.base_year,
term_rec.ilp_assessment_date,
term_rec.basis_start_date,
term_rec.basis_end_date,
term_rec.index_finder_date,
ROUND (term_rec.current_basis, l_precision), -- slk
term_rec.relationship_meaning,
term_rec.index_percent_change,
term_rec.basis_percent_change,
ROUND (term_rec.unconstraint_rent_due, l_precision), -- slk
ROUND (term_rec.constraint_rent_due, l_precision), -- slk
ROUND (term_rec.carry_forward_amount, l_precision), -- slk
term_rec.carry_forward_percent,
ROUND (term_rec.constraint_applied_amount, l_precision), -- slk
term_rec.constraint_applied_percent,
term_rec.open_carry_forward_amount,
term_rec.open_carry_forward_percent,
NVL (term_rec.open_carry_forward_amount, 0) - NVL (term_rec.carry_forward_amount, 0),
NVL (term_rec.open_carry_forward_percent, 0) - NVL (term_rec.carry_forward_percent, 0),
term_rec.old_rent,
ROUND (term_rec.constraint_rent_due, l_precision),
NVL (term_rec.constraint_rent_due, 0) - NVL (term_rec.old_rent, 0),
term_rec.current_index_line_value,
term_rec.previous_index_line_value,
term_rec.account_class_meaning,
term_rec.distribution_percentage,
((ROUND (term_rec.actual_amount, l_precision) * term_rec.distribution_percentage) / 100),
term_rec.gl_concatenated_segments,
term_rec.approved_by_name,
term_rec.po_number,
term_rec.payment_method,
term_rec.tax_code,
term_rec.tax_group,
term_rec.tax_included,
term_rec.annual,
term_rec.area,
DECODE (NVL (term_rec.area, 0), 0, 0, ROUND ((term_rec.annual / term_rec.area), 10)),
term_rec.constraint_proration, -- slk
term_rec.index_multiplier, -- slk
term_rec.index_percent_change,
term_rec.index_percent_change * NVL (term_rec.index_multiplier, 1), -- slk
term_rec.include_in_var_rent, -- slk
term_rec.last_update_date,
term_rec.last_updated_by,
term_rec.creation_date,
term_rec.created_by,
term_rec.last_update_login,
term_rec.request_id
);
pnp_debug_pkg.put_log_msg ('pn_rentincdet_insert(-)');