[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR (location_code, 1, 30)
INTO l_location_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND ROWNUM < 2;
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
,l.lease_num
,l.payment_term_proration_rule
,l.abstracted_by_user
,l.lease_class_code
,ld.lease_commencement_date
,ld.lease_termination_date
,ld.lease_execution_date
,ld.lease_extension_end_date
,it.payment_purpose_code
,it.payment_term_type_code
,it.frequency_code
,it.start_date
,it. end_date
,it.vendor_id
,it.vendor_site_id
,it.target_date
,it.actual_amount
,it.estimated_amount
,it.currency_code
,it.rate
,it.customer_id
,it.customer_site_use_id
,it.normalize'
|| location_code_field
|| ' ,it.schedule_day
,it.cust_ship_site_id
,it.ap_ar_term_id
,it.cust_trx_type_id
,it.project_id
,it.task_id
,it.organization_id
,it.expenditure_type
,it.expenditure_item_date
,it.inv_rule_id
,it.account_rule_id
,it.salesrep_id
,it.status payterm_status
,it.index_term_indicator
,il.index_lease_id
,il.index_id
,il.commencement_date il_commencement_date
,il.termination_date il_termination_date
,il.index_lease_number
,il.assessment_date il_assessment_date
,il.assessment_interval
,il.spread_frequency
,il.basis_percent_default
,il.initial_basis
,il.base_index
,il.index_finder_method
,il.index_finder_months
,il.negative_rent_type
,il.increase_on
,il.basis_type
,il.reference_period
,il.base_year
,il.index_multiplier -- slk
,il.proration_rule -- slk
,ilp.assessment_date ilp_assessment_date
,ilp.basis_start_date
,ilp.basis_end_date
,ilp.index_finder_date
,ilp.current_basis
,ilp.relationship
,ilp.index_percent_change
,ilp.basis_percent_change
,ilp.unconstraint_rent_due
,ilp.constraint_rent_due
,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
,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_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 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
|| 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_detail_itf
(name,
lease_num,
payment_term_proration_rule,
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,
supplier_number,
target_date,
actual_amount,
estimated_amount,
currency_code,
rate,
customer_number,
normalize,
schedule_day,
expenditure_type,
expenditure_item_date,
payterm_status,
index_term_indicator,
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,
abstracted_by_user_name,
vendor_name,
vendor_site_code,
customer_name,
customer_bill_site,
customer_ship_site,
cust_trx_type_name,
account_rule_name,
inv_rule_name,
index_name,
location_code,
location_type_lookup_code,
project_number,
task_number,
organization_name,
ap_ar_term_name,
salesrep_name,
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.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.supplier_number,
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_number,
term_rec.normalize,
term_rec.schedule_day,
term_rec.expenditure_type,
term_rec.expenditure_item_date,
term_rec.payterm_status,
term_rec.index_term_indicator,
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.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.abstracted_by_user_name,
term_rec.vendor_name,
term_rec.vendor_site_code,
term_rec.customer_name,
term_rec.customer_bill_site,
term_rec.customer_ship_site,
term_rec.cust_trx_type_name,
term_rec.account_rule_name,
term_rec.inv_rule_name,
term_rec.index_name,
term_rec.location_code,
term_rec.location_type_lookup_code,
term_rec.project_number,
term_rec.task_number,
term_rec.organization_name,
term_rec.ap_ar_term_name,
term_rec.salesrep_name,
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(-)');
SELECT SUBSTR (vendor_name, 1, 30),
segment1
INTO l_vendor_rec
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT vendor_site_code
INTO l_vendor_site
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id;
SELECT SUBSTR (hp.party_name, 1, 30),
hca.account_number
INTO l_customer_rec
FROM hz_cust_accounts hca, hz_parties hp
WHERE cust_account_id = p_customer_id
AND hca.party_id = hp.party_id;
SELECT distinct hzc.location
INTO l_bill_site
FROM hz_cust_site_uses_all hzc,
pn_payment_terms pterm
WHERE hzc.site_use_id (+) = pterm.customer_site_use_id
AND pterm.customer_site_use_id =p_site_use_id;
SELECT distinct hzc.location
INTO l_ship_site
FROM hz_cust_site_uses_all hzc,
pn_payment_terms pterm
WHERE hzc.site_use_id (+) = pterm.cust_ship_site_id
AND pterm.cust_ship_site_id =p_site_use_id;
SELECT SUBSTR (NAME, 1, 30)
INTO l_index_name
FROM pn_index_history_headers
WHERE index_id = p_index_id;
SELECT segment1
INTO l_project_number
FROM pa_projects
WHERE project_id = p_project_id;
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT meaning
INTO l_lookup_meaning
FROM fnd_lookups
WHERE lookup_code = p_lookup_code
AND lookup_type = p_lookup_type;