The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payment_term_id,
start_date,
end_date,
actual_amount,
estimated_amount,
payment_term_type_code,
frequency_code,
lease_id,
set_of_books_id,
currency_code
FROM pn_payment_terms_all /*sdm14jul*/
WHERE lease_id = p_lease_id
FOR UPDATE OF end_date;
SELECT pn_leases.lease_commencement_date,
pn_leases.lease_termination_date,
nvl(pn_leases.payment_term_rule,365),
pn_leases.lease_change_id
INTO lease_start_date, lease_end_date, proration_rule, pn_lease_change_id
FROM pn_leases_v pn_leases /*sdm??should form view be replaced*/
WHERE pn_leases.lease_id = pt_rec.lease_id;
SELECT count(*)
INTO items_paid
FROM pn_payment_schedules_all ps, /*sdm14jul*/
pn_payment_items_all pi /*sdm14jul*/
WHERE ps.payment_schedule_id = pi.payment_schedule_id
AND pi.payment_term_id = pt_rec.payment_term_id
AND ps.payment_status_lookup_code <> 'DRAFT'
AND pi.payment_item_type_lookup_code = 'CASH';
delete from pn_payment_items_all /*sdm14jul*/
where payment_term_id = pt_rec.payment_term_id;
delete from pn_lease_milestones
where payment_term_id = pt_rec.payment_term_id
and RESPONSIBILITY_LOOKUP_CODE = 'PAYMENT_TERM';*/
delete from pn_payment_terms_all /*sdm14jul*/
where current of pt_cur;
SELECT min(due_date)
INTO first_payment_date
FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND payment_item_type_lookup_code = 'CASH';
SELECT nvl(max(due_date),first_payment_date)
INTO item_to_change_date
FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND due_date <= greatest(lease_end_date, pt_rec.start_date);
SELECT ps.payment_status_lookup_code, pi.payment_item_id
INTO sched_status, pi_id
FROM pn_payment_schedules_all ps, /*sdm14jul*/
pn_payment_items_all pi /*sdm14jul*/
WHERE pi.payment_schedule_id = ps.payment_schedule_id
AND pi.due_date = item_to_change_date
AND pi.payment_term_id = pt_rec.payment_term_id
AND pi.payment_item_type_lookup_code = 'CASH';
UPDATE pn_payment_items_all /*sdm14jul*/
SET actual_amount = decode(pt_rec.actual_amount,
null,null, round(amount,2)),
estimated_amount = decode(pt_rec.actual_amount,
null,round(amount,2),null),
period_fraction = amount / nvl(pt_rec.actual_amount,
pt_rec.estimated_amount),
last_update_date = sysdate,
last_updated_by = pn_user_id
WHERE payment_item_id = pi_id
AND payment_item_type_lookup_code = 'CASH';
DELETE from pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND due_date > item_to_change_date
AND payment_item_type_lookup_code = 'CASH'
AND payment_schedule_id in (SELECT payment_schedule_id
FROM pn_payment_schedules_all /*sdm14jul*/
WHERE payment_status_lookup_code = 'DRAFT'
AND lease_id = pt_rec.lease_id
);
update pn_payment_terms_all /*sdm14jul*/
set end_date = lease_end_date,
last_update_date = sysdate,
last_updated_by = pn_user_id
where payment_term_id = pt_rec.payment_term_id;
update pn_lease_milestones
set milestone_date = lease_end_date,
last_update_date = sysdate,
last_updated_by = pn_user_id
where payment_term_id = pt_rec.payment_term_id
and RESPONSIBILITY_LOOKUP_CODE = 'PAYMENT_TERM'
and (description like 'End%'
or description like 'Security Deposit refund due');*/
update pn_payment_terms_all /*sdm14jul*/
set end_date = pt_rec.start_date,
last_update_date = sysdate,
last_updated_by = pn_user_id
where payment_term_id = pt_rec.payment_term_id;
update pn_lease_milestones
set milestone_date = pt_rec.start_date,
last_update_date = sysdate,
last_updated_by = pn_user_id
where payment_term_id = pt_rec.payment_term_id
and responsibility_lookup_code = 'PAYMENT_TERM'
and (description like 'End%'
or description like 'Security Deposit refund due');*/
SELECT max(schedule_date)
INTO min_sched_date
FROM pn_payment_schedules_all /*sdm14jul*/
WHERE lease_id = pt_rec.lease_id
AND payment_status_lookup_code <> 'DRAFT';
DELETE from pn_payment_items_all /*sdm14jul*/
WHERE payment_item_type_lookup_code = 'NORMALIZED'
AND payment_term_id = pt_rec.payment_term_id;
SELECT count(*)
INTO actual_items
FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND actual_amount is not null
AND payment_item_type_lookup_code = 'CASH';
SELECT nvl(sum(actual_amount),0)
INTO total_cash
FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND payment_item_type_lookup_code = 'CASH';
SELECT max(payment_schedule_id)
INTO ps_id
FROM pn_payment_schedules_all /*sdm14jul*/
WHERE lease_id = pt_rec.lease_id
AND schedule_date = rent_sched_date;
SELECT pn_payment_schedules_s.nextval
INTO ps_id
FROM dual;
SELECT org_id INTO l_org_id
FROM pn_payment_schedules_all
WHERE payment_schedule_id = ps_id;
INSERT INTO pn_payment_schedules_all /*sdm14jul*/
(
payment_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
schedule_date,
lease_id,
lease_change_id,
payment_status_lookup_code,
org_id /*sdm14jul*/
)
VALUES
( ps_id,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
rent_sched_date,
pt_rec.lease_id,
pn_lease_change_id,
'APPROVED',
l_org_id /*sdm14jul*/
);
INSERT INTO pn_payment_schedules_all /*sdm14jul*/
(
payment_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
schedule_date,
lease_id,
lease_change_id,
payment_status_lookup_code,
org_id /*sdm14jul*/
)
VALUES
(
ps_id,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
rent_sched_date,
pt_rec.lease_id,
pn_lease_change_id,
'DRAFT',
l_org_id /*sdm14jul*/
);
SELECT org_id INTO l_org_id
FROM pn_payment_terms_all
WHERE payment_term_id = pt_rec.payment_term_id;
INSERT INTO pn_payment_items_all /*sdm14jul*/
(
payment_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
actual_amount,
estimated_amount,
due_date,
payment_item_type_lookup_code,
payment_term_id,
payment_schedule_id,
period_fraction,
vendor_id,
customer_id,
vendor_site_id,
customer_site_use_id,
set_of_books_id,
currency_code,
rate,
org_id /*sdm14jul*/
)
VALUES
(
pn_payment_items_s.nextval,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
amount,
null,
payment_date,
'NORMALIZED',
pt_rec.payment_term_id,
ps_id,
amount/decode(amount,0,1,months),
null,
null,
null,
null,
pt_rec.set_of_books_id,
pt_rec.currency_code,
1,
l_org_id /*sdm14jul*/
);
INSERT INTO pn_payment_items_all /*sdm14jul*/
(
payment_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
estimated_amount,
due_date,
payment_item_type_lookup_code,
payment_term_id,
payment_schedule_id,
period_fraction,
set_of_books_id,
currency_code,
rate,
org_id
)
SELECT
pn_payment_items_s.nextval,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
nvl(actual_amount, estimated_amount),
due_date,
'NORMALIZED',
payment_term_id,
payment_schedule_id,
1,
set_of_books_id,
currency_code,
rate,
l_org_id /*sdm14jul*/
FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND actual_amount is null;
DELETE FROM pn_payment_schedules_all ps /*sdm14jul*/
WHERE ps.lease_id = pn_lease_id
AND NOT exists (SELECT 'x'
FROM pn_payment_items_all pi /*sdm14jul*/
WHERE pi.payment_schedule_id = ps.payment_schedule_id);