[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT item.payment_item_id,
item.actual_amount,
item.payment_schedule_id,
schedule.schedule_date,
schedule.payment_status_lookup_code
FROM pn_payment_items_all item,
pn_payment_schedules_all schedule
WHERE item.payment_term_id = p_term_id
AND item.payment_schedule_id = schedule.payment_schedule_id
AND item.payment_item_type_lookup_code = 'CASH'
AND item.last_adjustment_type_code IS NULL
ORDER BY schedule.schedule_date;
SELECT summary.adjustment_summary_id,
summary.adj_schedule_date,
summary.sum_adj_amount
FROM pn_adjustment_summaries summary
WHERE summary.payment_term_id = p_term_id
ORDER BY summary.adj_schedule_date;
l_info := ' (orig = adj): inserting the current item into result table '||
' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
l_info := ' (orig < adj): inserting the current item into result table '||
' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
l_info := ' inserting the current item into result table '||
' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
l_info := ' finished finding lesser adj dates, now inserting current orig '||
' item into result table orig_item date: '|| l_orig_item_tbl(i).schedule_date;
l_info := ' (curnt = virtl): inserting item into result table '||
' curnt_item date: '||p_current_sched(i).schedule_date ||
' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
l_info := ' (curnt < virtl): inserting item into result table '||
' curnt_item date: '||p_current_sched(i).schedule_date ||
' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
l_info := ' inserting into result table '||
' curnt_item date: '||p_current_sched(i).schedule_date ||
' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
l_info := ' finished finding lesser adj dates, now inserting current '||
' item into result table curnt_item date:'||p_current_sched(i).schedule_date;
SELECT item.payment_item_id,
item.actual_amount amount,
schedule.payment_schedule_id,
schedule.schedule_date
FROM pn_payment_items_all item,
pn_payment_schedules_all schedule
WHERE schedule.payment_schedule_id = item.payment_schedule_id
AND schedule.payment_status_lookup_code = 'DRAFT'
AND item.payment_item_id IN
(SELECT payment_item_id
FROM pn_adjustment_details
WHERE adjustment_summary_id = p_adj_summ_id);
l_items_table.delete;
SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE schedule_date = p_schedule_date
AND lease_id = p_lease_id
AND payment_status_lookup_code = 'DRAFT';
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT NULL
FROM pn_payment_items_all
WHERE payment_schedule_id = p_sch_id
AND payment_term_id = p_term_id);
SELECT org_id
FROM pn_leases_all
WHERE lease_id = p_lease_id;
l_info:= ' inserting a new draft schedule for date: '||p_schedule_date;
pnt_payment_schedules_pkg.insert_row(
x_context => null,
x_rowid => l_rowid,
x_payment_schedule_id => p_schedule_id,
x_schedule_date => p_schedule_date,
x_lease_change_id => p_lease_change_id,
x_lease_id => p_lease_id,
x_approved_by_user_id => null,
x_transferred_by_user_ID => null,
x_payment_status_lookup_code => 'DRAFT',
x_approval_date => null,
x_transfer_date => null,
x_period_name => null,
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_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_org_id => l_org_id
);
l_info := ' preparing for bulk update ';
l_info := ' performing bulk update ';
UPDATE pn_adjustment_details
SET payment_item_id = null,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_item_id = l_payment_id_tbl(i);
SELECT vendor_id,
vendor_site_id,
customer_id,
customer_site_use_id,
cust_ship_site_id,
set_of_books_id,
currency_code,
rate,
estimated_amount,
org_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
l_info := ' preparing for bulk update ';
UPDATE pn_payment_items_all
SET actual_amount = ROUND(l_act_amt_tbl(i), l_precision),
estimated_amount = ROUND(l_est_amt_tbl(i), l_precision),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_item_id = l_payment_id_tbl(i);
l_payment_id_tbl.delete;
l_info := ' preparing for bulk delete ';
DELETE pn_payment_items
WHERE payment_item_id = l_payment_id_tbl(i);
l_payment_id_tbl.delete;
l_act_amt_tbl.delete;
l_est_amt_tbl.delete;
l_info := ' preparing for bulk insert ';
INSERT INTO pn_payment_items_all
(
payment_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
actual_amount,
estimated_amount,
due_date,
adj_start_date,
adj_end_date,
last_adjustment_type_code,
payment_item_type_lookup_code,
payment_term_id,
payment_schedule_id,
period_fraction,
vendor_id,
customer_id,
vendor_site_id,
customer_site_use_id,
cust_ship_site_id,
set_of_books_id,
currency_code,
export_currency_code,
export_currency_amount,
rate,
org_id
)
VALUES
(
pn_payment_items_s.nextval,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
ROUND(l_act_amt_tbl(i), l_precision),
ROUND(l_est_amt_tbl(i), l_precision),
l_trx_date_tbl(i),
l_start_date_tbl(i),
l_end_date_tbl(i),
p_adj_type_cd,
'CASH',
p_term_id,
l_sched_id_tbl(i),
1,
l_vendor_id,
l_customer_id,
l_vendor_site_id,
l_customer_site_use_id,
l_cust_ship_site_id,
l_set_of_books_id,
l_currency_code,
l_currency_code,
null,
l_rate,
l_org_id
) RETURNING payment_item_id BULK COLLECT INTO l_new_itm_id_tbl;
l_info := ' updating p_new_item_tbl with newly inserted item id ';
l_info := ' inserting into adjustment summary table for schedule date:'||
p_adj_table(i).schedule_date;
INSERT INTO pn_adjustment_summaries (
adjustment_summary_id,
adj_schedule_date,
payment_term_id,
sum_adj_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
pn_adjustment_summaries_s.nextval,
p_adj_table(i).schedule_date,
p_term_id,
p_adj_table(i).amount,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
) RETURNING adjustment_summary_id INTO l_adj_summ_id;
l_info := ' inserting new adjustment for schedule date:'||
p_adj_table(i).schedule_date;
INSERT INTO pn_adjustment_details (
adjustment_detail_id,
term_history_id,
adjustment_summary_id,
payment_item_id,
adj_start_date,
adj_end_date,
adjustment_amount,
group_num,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
pn_adjustment_details_s.nextval,
p_term_hist_id,
l_adj_summ_id,
p_adj_table(i).item_id,
l_start_date,
l_end_date,
p_adj_table(i).amount,
l_group_num,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
);
UPDATE pn_adjustment_summaries
SET sum_adj_amount = sum_adj_amount + p_adj_table(i).amount,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE adjustment_summary_id = p_adj_table(i).adj_summ_id;
SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD');
SELECT payment_item_id
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = p_sch_id;
DELETE pn_payment_schedules_all
WHERE payment_schedule_id = schedules_draft_rec.payment_schedule_id;
SELECT SUM(DECODE(item.payment_item_type_lookup_code, 'CASH', 1, 0)) num_cash
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = p_sch_id;
SELECT vendor_id,
vendor_site_id,
customer_id,
customer_site_use_id,
cust_ship_site_id,
set_of_books_id,
currency_code,
rate,
estimated_amount,
org_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
l_info := ' inserting $0 cash item onto schedule id: '||l_sched_id_tbl(i);
INSERT INTO pn_payment_items_all
(
payment_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
actual_amount,
estimated_amount,
due_date,
adj_start_date,
adj_end_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,
cust_ship_site_id,
set_of_books_id,
currency_code,
export_currency_code,
export_currency_amount,
rate,
org_id
) VALUES
(
pn_payment_items_s.nextval,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
0,
null,
l_sched_dt_tbl(i),
null,
null,
'CASH',
p_term_id,
l_sched_id_tbl(i),
1,
l_vendor_id,
l_customer_id,
l_vendor_site_id,
l_customer_site_use_id,
l_cust_ship_site_id,
l_set_of_books_id,
l_currency_code,
l_currency_code,
null,
l_rate,
l_org_id
);
DELETE pn_payment_schedules_all
WHERE payment_schedule_id = l_sched_id_tbl(i);
PROCEDURE update_terms_history(
p_term_hist_id pn_payment_terms_history.term_history_id%TYPE,
p_adj_type_cd pn_payment_items.last_adjustment_type_code%TYPE,
p_lease_change_id pn_lease_changes.lease_change_id%TYPE,
p_term_id pn_payment_terms.payment_term_id%TYPE
)
IS
-- Get total adj amount
CURSOR get_total_adj_amt IS
SELECT SUM(pad.adjustment_amount) total_adj_amount
FROM pn_adjustment_details pad,
pn_payment_terms_history pth
WHERE pth.payment_term_id = p_term_id
AND pth.lease_change_id = p_lease_change_id
AND pad.term_history_id = pth.term_history_id;
l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.update_terms_history';
UPDATE pn_payment_terms_history
SET adjustment_type_code = p_adj_type_cd,
total_adj_amount = l_amount,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE term_history_id = p_term_hist_id;
END update_terms_history;
SELECT max(schedule_date) schedule_date
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND payment_status_lookup_code = 'APPROVED';
SELECT org_id
FROM pn_leases_all
WHERE lease_id = p_lease_id;
update_terms_history(
p_term_hist_id => p_term_hist_id,
p_adj_type_cd => p_adj_type_cd,
p_lease_change_id => p_lease_chg_id,
p_term_id => p_term_id
);