[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppta.start_date,
ppta.end_date,
ppta.target_date,
ppta.actual_amount,
ppta.estimated_amount,
ppta.payment_term_type_code,
ppta.frequency_code,
ppta.payment_term_id,
pn_leases.lease_commencement_date,
pn_leases.lease_termination_date,
pn_leases.lease_id,
pn_leases.lease_change_id,
nvl(pn_leases.payment_term_rule,365) proration_rule,
ppta.vendor_id,
ppta.customer_id,
ppta.vendor_site_id,
ppta.customer_site_use_id,
ppta.set_of_books_id,
ppta.currency_code
FROM pn_payment_terms_all ppta, /*sdm14jul*/
pn_leases_v pn_leases /*sdm? shud form view be replaced*/
WHERE ppta.lease_id = pn_leases.lease_id /*sdm14jul*/
AND ppta.lease_id = p_lease_id /*sdm14jul*/
AND not exists
( SELECT 'x'
FROM pn_payment_items_all a /*sdm14jul*/
WHERE a.payment_term_id = ppta.payment_term_id
);
SELECT ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.frequency_code,
ppt.payment_term_id,
pls.lease_commencement_date,
pls.lease_termination_date,
pls.lease_id,
pls.lease_change_id,
nvl(pls.payment_term_rule,365) proration_rule,
ppt.vendor_id,
ppt.customer_id,
ppt.vendor_site_id,
ppt.customer_site_use_id,
ppt.set_of_books_id,
ppt.currency_code
FROM pn_leases_v pls, /*sdm should form view be replaced?*/
pn_payment_terms_all ppt /*sdm14jul*/
WHERE ppt.lease_id = pls.lease_id
AND ppt.lease_id = pn_lease_id
AND ppt.payment_term_id = p_paymentTermId
AND ppt.frequency_code = 'OT'
AND not exists
( SELECT 'x'
FROM pn_payment_items_all a /*sdm14jul*/
WHERE a.payment_term_id = ppt.payment_term_id
);
select trunc (i.start_date, 'MM')
into l_paymentScheduleDate
from dual;
select max (payment_schedule_id)
into l_paymentScheduleId
from pn_payment_schedules_all /*sdm14jul*/
where lease_id = i.lease_id
and schedule_date = l_paymentScheduleDate;
SELECT pn_payment_schedules_s.nextval
INTO l_paymentScheduleId
FROM dual;
SELECT org_id INTO l_org_id
FROM pn_leases_all
WHERE lease_id = i.lease_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
(
l_paymentScheduleId,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
l_paymentScheduleDate,
i.lease_id,
i.lease_change_id,
'DRAFT',
l_org_id /*sdm14jul*/
);
select payment_status_lookup_code
into l_paymentStatusLookupCode
from pn_payment_schedules_all /*sdm14jul*/
where payment_schedule_id = l_paymentScheduleId;
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,
export_currency_code,
export_currency_amount,
rate,
export_to_ap_flag,
org_id /*sdm14jul*/
)
SELECT
pn_payment_items_s.nextval,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
i.actual_amount,
decode(i.actual_amount, null, i.estimated_amount,
null),
i.start_date,
'CASH',
i.payment_term_id,
l_paymentScheduleId,
1,
i.vendor_id,
i.customer_id,
i.vendor_site_id,
i.customer_site_use_id,
i.set_of_books_id,
i.currency_code,
i.currency_code,
i.actual_amount,
1,
decode (l_paymentStatusLookupCode, 'APPROVED', 'Y',NULL),
l_org_id /*sdm14jul*/
from dual;
/* Loop - 1st time - insert CASH items and delete normalized items */
/* 2nd time - insert NORMALIZED items */
IF (normalize_only = 'Y') THEN
iteration_start := 2;
DELETE from pn_payment_items_all /*sdm14jul*/
WHERE payment_item_type_lookup_code = 'NORMALIZED'
AND payment_term_id = pt_rec.payment_term_id;
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_term_id = pt_rec.payment_term_id;
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_leases_all
WHERE lease_id = pt_rec.lease_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,
pt_rec.lease_change_id,
'DRAFT',
l_org_id /*sdm14jul*/
);
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,
export_currency_code,
export_currency_amount,
rate,
org_id /*sdm14jul*/
)
VALUES
( pn_payment_items_s.nextval,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
pt_rec.actual_amount,
decode(pt_rec.actual_amount,null,pt_rec.estimated_amount,null),
pt_rec.start_date,
'CASH',
pt_rec.payment_term_id,
ps_id,
1,
pt_rec.vendor_id,
pt_rec.customer_id,
pt_rec.vendor_site_id,
pt_rec.customer_site_use_id,
pt_rec.set_of_books_id,
pt_rec.currency_code,
pt_rec.currency_code,
pt_rec.actual_amount,
1,
l_org_id /*sdm14jul*/
);
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;
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,
pt_rec.lease_change_id,
'DRAFT',
l_org_id /*sdm14jul*/
);
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,
export_currency_code,
export_currency_amount,
rate,
org_id /*sdm14jul*/
)
VALUES
( pn_payment_items_s.nextval,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
-pt_rec.actual_amount,
decode(pt_rec.actual_amount,null,-pt_rec.estimated_amount,null),
pt_rec.target_date,
'CASH',
pt_rec.payment_term_id,
ps_id,
1,
pt_rec.vendor_id,
pt_rec.customer_id,
pt_rec.vendor_site_id,
pt_rec.customer_site_use_id,
pt_rec.set_of_books_id,
pt_rec.currency_code,
pt_rec.currency_code,
pt_rec.actual_amount,
1,
l_org_id /*sdm14jul*/
);
DELETE FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND payment_item_type_lookup_code = 'NORMALIZED';
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 count(*)
INTO items_paid
FROM pn_payment_items_all pi, /*sdm14jul*/
pn_payment_schedules_all ps /*sdm14jul*/
WHERE pi.payment_schedule_id = ps.payment_schedule_id
AND pi.payment_term_id = pt_rec.payment_term_id
AND pi.payment_item_type_lookup_code = 'CASH'
AND ps.payment_status_lookup_code <> 'DRAFT';
DELETE FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id;
DELETE FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND payment_item_type_lookup_code = 'NORMALIZED';
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;
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
)
VALUES
(
ps_id,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
rent_sched_date,
pt_rec.lease_id,
pt_rec.lease_change_id,
'APPROVED',
l_org_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
)
VALUES
(
ps_id,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
rent_sched_date,
pt_rec.lease_id,
pt_rec.lease_change_id,
'DRAFT',
l_org_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,
export_currency_code,
export_currency_amount,
rate,
org_id /*sdm14jul*/
)
values
( pn_payment_items_s.nextval,
sysdate,
pn_user_id,
sysdate,
pn_user_id,
decode (iteration,2,
round_amount,
decode (pt_rec.actual_amount,null,
null,round_amount
)
),
decode(iteration,2,
null,
decode(pt_rec.actual_amount,null,
round_amount,null
)
),
payment_date,
cur_pit_lookup_code,
pt_rec.payment_term_id,
ps_id,
amount/decode(iteration,2,
decode(cur_amount,0,
1, cur_amount
),
decode(pt_rec.actual_amount,null,
cur_estimated_amount, cur_amount
)
),
decode(iteration,2,
null,pt_rec.vendor_id
),
decode(iteration,2,
null,pt_rec.customer_id
),
decode(iteration,2,
null,pt_rec.vendor_site_id
),
decode(iteration,2,
null,pt_rec.customer_site_use_id
),
pt_rec.set_of_books_id,
pt_rec.currency_code,
pt_rec.currency_code,
decode (iteration,2,
round_amount,
decode (pt_rec.actual_amount,null,
null,round_amount
)
),
1,
l_org_id /*sdm14jul*/
);
DELETE from pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
and payment_item_type_lookup_code = 'NORMALIZED';
UPDATE pn_payment_items_all /*sdm14jul*/
SET actual_amount = cur_amount * period_fraction,
estimated_amount = decode(pt_rec.actual_amount,null,
cur_estimated_amount * period_fraction,
null
),
last_update_date = sysdate,
last_updated_by = pn_user_id
WHERE payment_term_id = pt_rec.payment_term_id
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'
);
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 /*sdm14jul*/
)
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,
pt_rec.set_of_books_id,
pt_rec.currency_code,
1,
l_org_id /*sdm14jul*/
FROM pn_payment_items_all /*sdm14jul*/
WHERE payment_term_id = pt_rec.payment_term_id
AND actual_amount is null;