[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_last_updated_by number(15) := fnd_global.user_id;
v_last_update_date date := sysdate;
select *
from ar_batches
where transmission_id = cv_transmission_id;
select distinct(decode(a.customer_trx_id,null,b.customer_trx_id,
a.customer_trx_id)) customer_trx_id
from ar_interim_cash_receipts a,
ar_interim_cash_rcpt_lines_all b
where a.batch_id = cv_batch_id
and a.cash_receipt_id = b.cash_receipt_id (+)
and(a.customer_trx_id is not null or b.customer_trx_id is not null);
select distinct(customer_trx_id) customer_trx_id
from ar_interim_cash_receipts
where batch_id = cv_batch_id
and customer_trx_id is not null
union
select distinct(customer_trx_id) customer_trx_id
from ar_interim_cash_rcpt_lines_all
where batch_id = cv_batch_id
and org_id = cv_org_id
and customer_trx_id is not null;
Select aps.customer_trx_id,
aps.amount_due_remaining,
fcc.priority,
aps.payment_schedule_id
from ra_customer_trx rct,
ar_payment_schedules aps,
fv_finance_charge_controls fcc
where rct.related_customer_trx_id = cv_invoice_id
and aps.customer_trx_id = rct.customer_trx_id
and rct.interface_header_attribute3 = fcc.charge_type
and rct.set_of_books_id = fcc.set_of_books_id
and aps.amount_due_remaining > 0
order by fcc.priority ;
select sum(amount) amount,
aicr.customer_trx_id,
aicr.payment_schedule_id
from ar_interim_cash_receipts aicr,
ra_customer_trx rct,
fv_finance_charge_controls fcc
where aicr.batch_id = cv_batch_id
and aicr.customer_trx_id is not null
and aicr.customer_trx_id = rct.customer_trx_id
and rct.interface_header_attribute3 = fcc.charge_type
and rct.set_of_books_id = fcc.set_of_books_id
group by aicr.customer_trx_id, aicr.payment_schedule_id
union
select sum(payment_amount) amount,
aicrl.customer_trx_id,
aicrl.payment_schedule_id
from ar_interim_cash_rcpt_lines_all aicrl,
ra_customer_trx rct,
fv_finance_charge_controls fcc
where aicrl.batch_id = cv_batch_id
and aicrl.customer_trx_id is not null
and aicrl.customer_trx_id = rct.customer_trx_id
and rct.interface_header_attribute3 = fcc.charge_type
and rct.set_of_books_id = fcc.set_of_books_id
and aicrl.org_id = cv_org_id
group by aicrl.customer_trx_id, aicrl.payment_schedule_id;
select customer_trx_id, aicr.amount amount, 'R' origin,
cash_receipt_id, 0 cash_receipt_line_id, payment_schedule_id,
0 sold_to_customer, ussgl_transaction_code
from ar_interim_cash_receipts aicr
where batch_id = cv_batch_id
and customer_trx_id is not null
and exists (select 'x' from fv_lockbox_ipa_temp f
where f.invoice_id = aicr.customer_trx_id
and f.batch_id = aicr.batch_id)
union
select customer_trx_id, aicrl.payment_amount amount, 'L' origin,
cash_receipt_id, cash_receipt_line_id, payment_schedule_id,
sold_to_customer, ussgl_transaction_code
from ar_interim_cash_rcpt_lines_all aicrl
where batch_id = cv_batch_id
and org_id = cv_org_id
and customer_trx_id is not null
and exists (select 'x' from fv_lockbox_ipa_temp f
where f.invoice_id = aicrl.customer_trx_id
and f.batch_id = aicrl.batch_id);
select debit_memo_id, amount, priority, payment_schedule_id
from fv_lockbox_ipa_temp
where transmission_id = cv_trans_id
and batch_id = cv_batch_id
and invoice_id = cv_invoice_id
order by priority;
delete from fv_lockbox_ipa_temp
where transmission_id = v_transmission_id;
SELECT fv_lockbox_ipa_temp_s.nextval
INTO v_temp_id
FROM dual;
insert into fv_lockbox_ipa_temp(temp_id,
invoice_id,
debit_memo_id,
amount,
priority,
batch_id,
payment_schedule_id,
transmission_id)
values(v_temp_id,
v_invoice_id,
v_debit_memo_id,
nvl(v_amount,0),
v_priority,
v_batch_id,
v_payment_schedule_id,
v_transmission_id);
update fv_lockbox_ipa_temp
set amount = amount - nvl(c_receipt_dms_rec.amount,0)
where debit_memo_id = c_receipt_dms_rec.customer_trx_id
and batch_id = v_batch_id
and transmission_id = v_transmission_id
and payment_schedule_id = c_receipt_dms_rec.payment_schedule_id;
IF v_dm_amt >0 THEN -- don't want to insert $0 receipts
insert_cash_receipt(v_appl_dm_id, v_dm_pay_sch_id, v_dm_amt, null);
update_lockbox_temp(v_dm_amt);
insert_cash_receipt(v_appl_dm_id, v_dm_pay_sch_id,
v_appl_amt_remain, null);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE AR_INTERIM_CASH_RECEIPTS TO MULTIPLE');
update ar_interim_cash_receipts
set customer_trx_id = null,
payment_schedule_id = null,
special_type = 'MULTIPLE',
amount_applied = null,
last_updated_by = v_last_updated_by,
last_update_date = v_last_update_date
where cash_receipt_id = v_cash_receipt_id;
update_interim_table(v_origin, v_appl_amt_remain,
v_appl_dm_id,v_dm_pay_sch_id);
update ar_interim_cash_rcpt_lines_all
set ussgl_transaction_code = null
where cash_receipt_id = v_cash_receipt_id
and cash_receipt_line_id = v_cash_receipt_line_id;
update_lockbox_temp(v_appl_amt_remain);
insert_cash_receipt(v_appl_inv_id, v_appl_inv_ps_id,
v_appl_amt_remain, v_transaction_code);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE AR_INTERIM_CASH_RECEIPTS TO MULTIPLE');
update ar_interim_cash_receipts
set customer_trx_id = null,
payment_schedule_id = null,
special_type = 'MULTIPLE',
amount_applied = null,
last_updated_by = v_last_updated_by,
last_update_date = v_last_update_date
where cash_receipt_id = v_cash_receipt_id;
update_interim_table(v_origin, v_appl_amt_remain,
v_appl_inv_id, v_appl_inv_ps_id);
update_lockbox_temp(v_appl_amt_remain);
PROCEDURE insert_cash_receipt(v_cust_trx_id IN number,
v_pay_sch_id IN number,
v_amount IN number,
v_ussgl_tran_code IN varchar2) AS
l_module_name VARCHAR2(200) := g_module_name || 'insert_cash_receipt';
select max(cash_receipt_line_id)
into v_cash_receipt_line_id_ctr
from ar_interim_cash_rcpt_lines_all
where batch_id = v_batch_id
and cash_receipt_id = v_cash_receipt_id;
insert into ar_interim_cash_rcpt_lines_all
(cash_receipt_id,
cash_receipt_line_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
sold_to_customer,
payment_amount,
payment_schedule_id,
customer_trx_id,
batch_id,
ussgl_transaction_code)
values
(v_cash_receipt_id,
v_cash_receipt_line_id_ctr,
v_last_updated_by,
v_last_update_date,
v_created_by,
v_creation_date,
v_sold_to_customer,
v_amount,
v_pay_sch_id, -- fin chrg debit memo or invoice_id payment_schedule_id
v_cust_trx_id, -- fin chrg debit memo or invoice_id customer_trx_id
v_batch_id,
v_ussgl_tran_code);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INSERTED NEW CASH RECEIPT FOR CUSTOMER_TRX_ID '||
to_char(v_cust_trx_id));
v_errbuf := 'insert_cash_receipt '||sqlerrm;
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN INSERT_CASH_RECEIPT - '||SQLERRM);
END insert_cash_receipt;
PROCEDURE update_lockbox_temp(v_decrease_dm_amount IN NUMBER) IS
l_module_name VARCHAR2(200) := g_module_name || 'update_lockbox_temp';
update fv_lockbox_ipa_temp
set amount = nvl(amount,0) - nvl(v_decrease_dm_amount,0)
where debit_memo_id = v_appl_dm_id -- current debit memo being processed
and invoice_id = v_appl_inv_id -- current invoice being processed
and batch_id = v_batch_id; -- curent batch being processed
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE TEMP TABLE DECREASING BY '
||to_char(v_decrease_dm_amount));
v_errbuf := 'update_lockbox_temp '||sqlerrm;
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN UPDATE_LOCKBOX_TEMP - '||SQLERRM);
END update_lockbox_temp;
PROCEDURE update_interim_table(v_table IN VARCHAR2,
v_decrease_appl_amt IN NUMBER,
v_upd_customer_trx_id IN NUMBER,
v_upd_pay_sch_id IN NUMBER) IS
l_module_name VARCHAR2(200) := g_module_name || 'update_interim_table';
update ar_interim_cash_rcpt_lines_all
set payment_amount = nvl(v_decrease_appl_amt,0),
customer_trx_id = v_upd_customer_trx_id,
payment_schedule_id = v_upd_pay_sch_id,
last_updated_by = v_last_updated_by,
last_update_date = v_last_update_date
where cash_receipt_id = v_cash_receipt_id
and cash_receipt_line_id = v_cash_receipt_line_id;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE INTERIM LINES TABLE SETTING AMOUNT = '
||to_char(v_decrease_appl_amt)||' on cash_receipt_id '
||to_char(v_cash_receipt_id));
v_errbuf := 'update_interim_table '||sqlerrm;
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN UPDATE_INTERIM_TABLE - '||SQLERRM);
END update_interim_table;