[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' SELECT
lease.lease_id lease_id,
lease.lease_status lease_status,
lease.status status,
det.lease_extension_end_date lease_extension_end_date,
det.lease_change_id lease_change_id
FROM pn_leases_all lease,
pn_lease_details_all det
WHERE lease.lease_id = det.lease_id ';
SELECT lease_num,
responsible_user,
GREATEST(lease_termination_date,lease_extension_end_date) old_ext_dt,
SYSDATE,
lease.org_id org_id,
lease_detail_id,
expense_account_id,
accrual_account_id,
receivable_account_id,
term_template_id,
grouping_rule_id,
lease_commencement_date,
lease_termination_date,
lease_execution_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM pn_leases_all lease,pn_lease_details_all det
WHERE lease.lease_id = p_lease_id
AND det.lease_id = lease.lease_id;
l_last_updated_by NUMBER := NVL(fnd_profile.value ('USER_ID'), 0);
l_last_update_login NUMBER := NVL(fnd_profile.value ('LOGIN_ID'),0);
pn_lease_changes_pkg.Insert_Row
(
x_rowid => l_rowid
,x_lease_change_id => l_leaseChangeId
,x_lease_id => p_lease_id
,x_lease_change_number => l_leaseChangeNumber
,x_lease_change_name => l_leaseChangeName
,x_responsible_user => l_user_id
,x_change_commencement_date => l_changeCommencementDate
,x_change_termination_date => l_changeTerminationdate
,x_change_type_lookup_code => 'AMEND'
,x_change_execution_date => l_changeExecutionDate
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_abstracted_by_user => l_responsibleUser
,x_creation_date => l_creationDate
,x_created_by => l_user_id
,x_last_update_date => l_creationDate
,x_last_updated_by => l_last_updated_by
,x_last_update_login => l_last_update_login
,x_org_id => l_org_id
,x_cutoff_date => NULL
);
pn_lease_details_pkg.Update_Row
(
x_lease_detail_id => l_lease_detail_id
,x_lease_change_id => l_leaseChangeId
,x_lease_id => p_lease_id
,x_responsible_user => l_user_id
,x_expense_account_id => l_expense_account_id
,x_lease_commencement_date => l_leaseCommencementDate
,x_lease_termination_date => l_leaseTerminationDate
,x_lease_extension_end_date => p_lease_ext_end_dt
,x_lease_execution_date => l_leaseExecutionDate
,x_last_update_date => l_creationDate
,x_last_updated_by => l_last_updated_by
,x_last_update_login => l_last_update_login
,x_accrual_account_id => l_accrual_account_id
,x_receivable_account_id => l_receivable_account_id
,x_term_template_id => l_term_template_id
,x_grouping_rule_id => l_grouping_rule_id
,x_attribute_category => l_attribute_category
,x_attribute1 => l_attribute1
,x_attribute2 => l_attribute2
,x_attribute3 => l_attribute3
,x_attribute4 => l_attribute4
,x_attribute5 => l_attribute5
,x_attribute6 => l_attribute6
,x_attribute7 => l_attribute7
,x_attribute8 => l_attribute8
,x_attribute9 => l_attribute9
,x_attribute10 => l_attribute10
,x_attribute11 => l_attribute11
,x_attribute12 => l_attribute12
,x_attribute13 => l_attribute13
,x_attribute14 => l_attribute14
,x_attribute15 => l_attribute15
);
SELECT pta.tenancy_id,
pta.location_id,
NVL(pta.occupancy_date,pta.estimated_occupancy_date) st_date,
pta.org_id
FROM pn_tenancies_all pta, pn_lease_details_all plda
WHERE pta.lease_id = p_lease_id
AND pta.lease_id = plda.lease_id
AND pta.expiration_date = NVL(p_old_ext_end_dt, plda.lease_termination_date);
SELECT lease_class_code
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT location_type_lookup_code
FROM pn_locations_all
WHERE location_id = p_location_id;
SELECT 'Y'
FROM DUAl
WHERE 100 < (SELECT sum(allocated_area_pct)
FROM pn_leases_all pnl,
pn_tenancies_all ten
WHERE pnl.lease_class_code <> 'DIRECT'
AND pnl.lease_id = ten.lease_id
AND ten.location_id = p_location_id
AND NVL(ten.estimated_occupancy_date, ten.occupancy_date)
<= p_ten_end_dt
AND ten.expiration_date >= p_ten_st_dt
);
UPDATE pn_tenancies_all
SET expiration_date = p_lease_ext_end_dt
,fin_oblig_end_date = p_lease_ext_end_dt
WHERE tenancy_id = rec.tenancy_id;
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = p_lease_ext_end_dt
,fin_oblig_end_date = p_lease_ext_end_dt
WHERE tenancy_id = rec.tenancy_id;
SELECT prop.property_name property_name,
NVL(loc.building,NVL(loc.floor,loc.office)) location_name,
lease.lease_num lease_num,
lease_det.lease_termination_date lease_termination_date,
lease_det.lease_extension_end_date lease_extension_end_date,
/*SUM(NVL(terms.estimated_amount,terms.actual_amount))*/
NULL charge,
ilease.index_lease_number index_rent_num,
var.rent_num var_rent_num
FROM pn_leases_all lease,
pn_lease_details_all lease_det,
pn_tenancies_all ten,
pn_locations_all loc,
pn_properties_all prop,
pn_index_leases_all ilease,
pn_var_rents_all var,
pn_payment_terms_all terms
WHERE lease.lease_id = p_lease_id
AND ten.primary_flag = 'Y'
AND ten.location_id = loc.location_id
AND ten.occupancy_date <= loc.active_start_date
AND ten.expiration_date <= loc.active_end_date
AND lease.lease_id = terms.lease_id
AND loc.property_id = prop.property_id (+)
AND lease.lease_id = ten.lease_id
AND lease.lease_id = lease_det.lease_id
AND lease.lease_id = ilease.lease_id (+)
AND lease.lease_id = var.lease_id (+)
ORDER BY property_name,
location_name,
lease_num,
index_rent_num,
var_rent_num;