[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
distinct 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
,il.location_id --32
,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.proration_rule -- slk
,il.index_multiplier -- 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
FROM
pn_leases_all l,
pn_lease_details_all ld,
pn_index_leases il ,
pn_payment_terms_all it,
pn_index_lease_periods_all ilp'
|| location_code_from
|| property_name_from
|| building_name_from
|| ' WHERE l.lease_id = ld.lease_id
AND il.lease_id = l.lease_id
AND ilp.index_lease_id = il.index_lease_id
AND ilp.index_period_id = it.index_period_id
AND it.index_period_id IS NOT NULL'
|| lease_number_where_clause
|| ri_number_where_clause
|| lease_date_where_clause
|| lease_class_where
|| location_code_where
|| property_name_where
|| building_name_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 currency_code
INTO l_currency_code
FROM pn_index_leases
WHERE index_lease_id = l_lease_detail.index_lease_id;
INSERT INTO pn_rent_increase_summary_itf
(name,
lease_num,
payment_term_proration_rule,
lease_class_meaning,
lease_commencement_date,
lease_termination_date,
lease_execution_date,
lease_extension_end_date,
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,
current_index_line_value,
previous_index_line_value,
abstracted_by_user_name,
index_name,
location_code,
constraint_proration, -- slk
index_multiplier, -- slk
unadjusted_index_change, -- slk
adjusted_index_change_percent,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
open_carry_forward_amount,
open_carry_forward_percent,
old_rent,
new_rent,
change_rent,
app_carry_forward_amount,
app_carry_forward_percent
)
VALUES (term_rec.name,
term_rec.lease_num,
term_rec.payment_term_proration_rule,
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.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,
NVL (term_rec.increase_on_meaning, 'Gross'),
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.current_index_line_value,
term_rec.previous_index_line_value,
term_rec.abstracted_by_user_name,
term_rec.index_name,
term_rec.location_code,
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.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,
term_rec.open_carry_forward_amount,
term_rec.open_carry_forward_percent,
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),
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)
);
pnp_debug_pkg.put_log_msg ('pn_rentincdet_insert(-)');
pnp_debug_pkg.put_log_msg ('pn_rentincdet_updates(+)');
pnp_debug_pkg.put_log_msg ('pn_rentincdet_updates(-)');