[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
fnd_file.put_line(FND_FILE.LOG, 'value l_last_updated_by ' || l_last_updated_by);
fnd_file.put_line(FND_FILE.LOG, 'value l_last_update_login'|| l_last_update_login);
create_and_update_remit_rec_pa(
p_batch_id => o_batch_id,
p_return_status => cr_return_status
);
select count(*)
INTO l_count
from AR_CASH_RECEIPTS
where selected_remittance_batch_id = p_batch_id;
select batch_date ,gl_date , remit_method_code, currency_code,
receipt_method_id, REMITTANCE_BANK_BRANCH_ID,REMIT_BANK_ACCT_USE_ID
into l_batch_date,l_gl_date,l_remittance_method,l_currency_code,
l_receipt_method_id,l_remittance_bank_branch_id,l_remittance_bank_account_id
from AR_BATCHES
where batch_id = p_batch_id;
/* insert batch */
insert_batch(
l_batch_date,
l_gl_date,
l_approve_flag,
l_format_flag,
l_currency_code,
l_remittance_method,
l_receipt_class_id,
l_receipt_method_id,
l_remittance_bank_branch_id,
l_remittance_bank_account_id,
o_batch_id
);
/* select and update receipt */
if l_receipt_method_id is not null then /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
select rm.payment_channel_code,b.set_of_books_id
into p_payment_type_code, p_sob_id
from ar_receipt_methods rm,
ar_batches b
where rm.receipt_method_id = l_receipt_method_id /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
and b.receipt_method_id = rm.receipt_method_id
and b.batch_id = o_batch_id;
select b.set_of_books_id
into p_sob_id
from ar_batches b
where b.batch_id = o_batch_id;
select_update_rec(
p_customer_number_l=> p_customer_number_l,
p_customer_number_h=> p_customer_number_h,
p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
p_trx_num_l=> p_trx_num_l,
p_trx_num_h=> p_trx_num_h,
p_remittance_total_to=> p_remittance_total_to,
p_remittance_total_from=> p_remittance_total_from,
p_batch_id=> o_batch_id ,
p_receipt_method_id=> l_receipt_method_id ,
p_currency_code=> p_batch_currency,
p_payment_type_code=> p_payment_type_code,
p_sob_id=> p_sob_id ,
p_remit_method_code=> p_remit_method_code,
p_remit_bank_account_id=> l_remittance_bank_account_id ,
p_return_status=>l_return_status);
/* CALL TO INSERT BATCH FROM MAIN */
insert_batch(
l_batch_date,
l_gl_date,
l_approve_flag,
l_format_flag,
l_currency_code,
l_remittance_method,
l_receipt_class_id,
l_receipt_method_id,
l_remittance_bank_branch_id,
l_remittance_bank_account_id,
o_batch_id
);
/* CALL TO select_and_update receipts */
if l_receipt_method_id is not null then /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
select rm.payment_channel_code,b.set_of_books_id
into p_payment_type_code, p_sob_id
from ar_receipt_methods rm,
ar_batches b
where rm.receipt_method_id = l_receipt_method_id /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
and b.receipt_method_id = rm.receipt_method_id
and b.batch_id = o_batch_id;
select b.set_of_books_id
into p_sob_id
from ar_batches b
where b.batch_id = o_batch_id;
select_update_rec(
p_customer_number_l=> p_customer_number_l,
p_customer_number_h=> p_customer_number_h,
p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
p_trx_num_l=> p_trx_num_l,
p_trx_num_h=> p_trx_num_h,
p_remittance_total_to=> p_remittance_total_to,
p_remittance_total_from=> p_remittance_total_from,
p_batch_id=> o_batch_id ,
p_receipt_method_id=> l_receipt_method_id ,
p_currency_code=> p_batch_currency,
p_payment_type_code=> p_payment_type_code,
p_sob_id=> p_sob_id ,
p_remit_method_code=> p_remit_method_code,
p_remit_bank_account_id=> l_remittance_bank_account_id ,
p_return_status=>l_return_status);
/* CALL TO CREATE AND UPDATE REM RECEIPTS */
IF PG_DEBUG in ('Y','C') THEN
fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to create remit rec ' || to_char(o_batch_id));
create_and_update_remit_rec(
p_batch_id => o_batch_id,
p_return_status => cr_return_status
);
select rm.payment_channel_code,b.set_of_books_id
into p_payment_type_code, p_sob_id
from ar_receipt_methods rm,
ar_batches b
where b.receipt_method_id = rm.receipt_method_id
and b.batch_id = o_batch_id;
select b.set_of_books_id
into p_sob_id
from ar_batches b
where b.batch_id = o_batch_id;
select_update_rec(
p_customer_number_l=> p_customer_number_l,
p_customer_number_h=> p_customer_number_h,
p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
p_trx_num_l=> p_trx_num_l,
p_trx_num_h=> p_trx_num_h,
p_remittance_total_to=> p_remittance_total_to,
p_remittance_total_from=> p_remittance_total_from,
p_batch_id=> o_batch_id ,
p_receipt_method_id=> l_receipt_method_id ,
p_currency_code=> l_currency_code,
p_payment_type_code=> p_payment_type_code,
p_sob_id=> p_sob_id ,
p_remit_method_code=> l_remittance_method,
p_remit_bank_account_id=> l_remittance_bank_account_id ,
p_return_status=>l_return_status);
fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to select update rec ' || to_char(o_batch_id));
select rm.payment_channel_code,b.set_of_books_id
into p_payment_type_code, p_sob_id
from ar_receipt_methods rm,
ar_batches b
where b.receipt_method_id = rm.receipt_method_id
and b.batch_id = o_batch_id;
select b.set_of_books_id
into p_sob_id
from ar_batches b
where b.batch_id = o_batch_id;
select_update_rec(
p_customer_number_l=> p_customer_number_l,
p_customer_number_h=> p_customer_number_h,
p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
p_trx_num_l=> p_trx_num_l,
p_trx_num_h=> p_trx_num_h,
p_remittance_total_to=> p_remittance_total_to,
p_remittance_total_from=> p_remittance_total_from,
p_batch_id=> o_batch_id ,
p_receipt_method_id=> l_receipt_method_id ,
p_currency_code=> l_currency_code,
p_payment_type_code=> p_payment_type_code,
p_sob_id=> p_sob_id ,
p_remit_method_code=> l_remittance_method,
p_remit_bank_account_id=> l_remittance_bank_account_id ,
p_return_status=>l_return_status);
create_and_update_remit_rec(
p_batch_id => o_batch_id,
p_return_status => cr_return_status
);
create_and_update_remit_rec(
p_batch_id => o_batch_id,
p_return_status => cr_return_status
);
fnd_file.put_line(FND_FILE.LOG,'reset selected_remittance_batch_id for all receipts after approval');
update ar_cash_receipts
set selected_remittance_batch_id = null
where selected_remittance_batch_id = o_batch_id
and cash_receipt_id in (select cash_receipt_id from
ar_cash_receipt_history
where request_id = l_request_id
and status = 'REMITTED'
and current_record_flag = 'Y');
fnd_file.put_line(FND_FILE.LOG,'selected_remittance_batch_id reset for rows: '||sql%rowcount);
/* CALL TO REC_RESET to delete bad rows */
IF G_ERROR = 'Y' THEN
fnd_file.put_line( FND_FILE.LOG, 'CALLING REC_RESET');
/* Bug 5051186 Update the Batch Status */
IF TOTAL_WORKERS < 1 THEN
IF l_format_flag = 'Y' THEN
l_batch_applied_status := 'COMPLETED_FORMAT';
/*bug 7352164 update control count and control amount */
IF p_create_only = 'Y' THEN
SELECT
nvl(sum(cr.amount),0),
count(*) into
l_control_amount,
l_control_count
FROM ar_cash_receipts cr
WHERE cr.selected_remittance_batch_id = o_batch_id;
update ar_batches
SET batch_applied_status = l_batch_applied_status,
control_count = l_control_count,
control_amount= l_control_amount
where batch_id = o_batch_id;
SELECT
nvl(sum(crh.amount),0),
count(*) into
l_control_amount,
l_control_count
FROM ar_cash_receipt_history crh
WHERE crh.batch_id = o_batch_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y';
update ar_batches
SET batch_applied_status = l_batch_applied_status,
control_count = l_control_count,
control_amount= l_control_amount
where batch_id = o_batch_id;
/* No need to update counts if the batch is submitted for format only */
update ar_batches
SET batch_applied_status = l_batch_applied_status
where batch_id = o_batch_id;
SELECT batch_applied_status
INTO l_batch_applied_status
FROM ar_batches
WHERE batch_id = p_batch_id;
insert_batch( l_batch_date,
l_gl_date,
l_approve_flag,
l_format_flag,
l_currency_code,
l_remittance_method,
l_receipt_class_id,
l_receipt_method_id,
l_remittance_bank_branch_id,
l_remittance_bank_account_id,
o_batch_id
);
select rm.payment_channel_code,b.set_of_books_id
into p_payment_type_code, p_sob_id
from ar_receipt_methods rm,
ar_batches b
where rm.receipt_method_id = p_payment_method_id
and b.receipt_method_id = rm.receipt_method_id
and b.batch_id = o_batch_id;
select b.set_of_books_id
into p_sob_id
from ar_batches b
where b.batch_id = o_batch_id;
select_update_rec(
p_customer_number_l=> p_customer_number_l,
p_customer_number_h=> p_customer_number_h,
p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
p_trx_num_l=> p_trx_num_l,
p_trx_num_h=> p_trx_num_h,
p_remittance_total_to=> p_remittance_total_to,
p_remittance_total_from=> p_remittance_total_from,
p_batch_id=> o_batch_id ,
p_receipt_method_id=> l_receipt_method_id ,
p_currency_code=> p_batch_currency,
p_payment_type_code=> p_payment_type_code,
p_sob_id=> p_sob_id ,
p_remit_method_code=> p_remit_method_code,
p_remit_bank_account_id=> l_remittance_bank_account_id ,
p_return_status=>l_return_status);
insert_exceptions( p_batch_id => -333,
p_request_id =>l_request_id,
p_exception_code => 'NO_BATCH',
p_additional_message => 'Error during inserting the
batch' );
select count(*)
INTO l_count
from AR_CASH_RECEIPTS
where selected_remittance_batch_id = o_batch_id;
SELECT org_id
INTO l_org_id
FROM ar_system_parameters;
l_ins_stmt := 'INSERT /*+ append */ INTO ar_autorem_interim
(receipt_number, --cr.receipt_number
cash_receipt_id,
creation_status,--rc.creation_status
org_id, -- cr.org_id
party_id,
payment_channel_code,
merchant_ref,
currency_code,
pay_from_customer,
customer_site_use_id,
cash_receipt_history_id,
exchange_date,
exchange_rate,
exchange_rate_type,
amount1,
acctd_amount,
factor_discount_amount,
remittance_ccid,
bank_charges_ccid,
code_combination_id,
crh_gl_date,
crh_trx_date,
payment_server_order_num,
approval_code,
unique_ref,
customer_bank_account_id,
payment_trxn_extension_id,
amount2,
batch_id,
current_worker
) ';
'SELECT /*+ leading(gtt,crh) cardinality(gtt 1000) swap_join_inputs(bat) use_hash(bat) parallel(gtt) parallel(crh) use_nl(crh,cr,hca,party,ps,d) */';
'SELECT /*+ leading(gtt,crh) cardinality(gtt 1000) swap_join_inputs(bat) use_hash(bat) use_nl(crh,cr,hca,party,ps,d) */';
cr.selected_remittance_batch_id,
--MOD(cr.cash_receipt_id, p_total_workers) + 1
--MOD(CEIL(cr.cash_receipt_id/10000), p_total_workers) + 1
MOD(CEIL((DENSE_RANK() over(order by crh.cash_receipt_id))/5000), :b_total_workers) + 1
FROM ar_rem_cr_id_gtt gtt,
ar_cash_receipts cr,
ar_receipt_methods rm,
ar_receipt_classes rc,
hz_cust_accounts hca,
hz_parties party,
/*ar_remit_gt g,*/
ar_cash_receipt_history crh,
ar_batches bat,
ar_receipt_method_accounts rma,
ar_payment_schedules ps,
ar_distributions d
WHERE cr.selected_remittance_batch_id = :b_batch_id
AND cr.REMIT_BANK_ACCT_USE_ID = rma.REMIT_BANK_ACCT_USE_ID
AND bat.batch_id = cr.selected_remittance_batch_id
AND rm.receipt_method_id = cr.receipt_method_id
AND rma.receipt_method_id = cr.receipt_method_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = ''Y''
AND ps.cash_receipt_id(+) = cr.cash_receipt_id
AND crh.status = ''CONFIRMED''
AND crh.cash_receipt_history_id = d.source_id
AND d.source_type = ''CONFIRMATION''
AND d.source_table = ''CRH''
AND hca.party_id = party.party_id(+)
AND hca.cust_account_id(+) = cr.pay_from_customer
AND cr.receipt_method_id = rm.receipt_method_id
AND rm.receipt_class_id = rc.receipt_class_id
and gtt.CASH_RECEIPT_HISTORY_ID = CRH.CASH_RECEIPT_HISTORY_ID ';
delete from ar_autorem_interim where batch_id = o_batch_id;
/* Bug 5051186 Update the Batch Status */
IF l_format_flag = 'Y' THEN
l_batch_applied_status := 'COMPLETED_FORMAT';
/* bug 7352164 update control count and control amount */
IF ( l_create_flag = 'Y' AND l_approve_flag = 'N' AND l_format_flag = 'N') THEN
SELECT
nvl(sum(cr.amount),0),
count(*) into
l_control_amount,
l_control_count
FROM ar_cash_receipts cr
WHERE cr.selected_remittance_batch_id = o_batch_id;
UPDATE ar_batches
SET batch_applied_status = l_batch_applied_status,
control_count = l_control_count,
control_amount= l_control_amount
WHERE batch_id = o_batch_id;
SELECT
nvl(sum(crh.amount),0),
count(*) into
l_control_amount,
l_control_count
FROM ar_cash_receipt_history crh
WHERE crh.batch_id = o_batch_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y';
UPDATE ar_batches
SET batch_applied_status = l_batch_applied_status,
control_count = l_control_count,
control_amount= l_control_amount
WHERE batch_id = o_batch_id;
/* No need to update counts if the batch is submitted for format only */
UPDATE ar_batches
SET batch_applied_status = l_batch_applied_status
WHERE batch_id = o_batch_id;
| PROCEDURE insert_batch |
| |
| DESCRIPTION |
| |
| This procedure is used to insert the batch record when called from |
| srs. It also gets the other required parameters from sysparm |
| and conc program |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-JUL-2005 bichatte Created |
*=========================================================================*/
PROCEDURE insert_batch(
p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
p_batch_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
p_approve_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
p_format_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
p_currency_code IN ar_batches.currency_code%TYPE,
p_remmitance_method IN ar_batches.remit_method_code%TYPE,
p_receipt_class_id IN ar_receipt_classes.receipt_class_id%TYPE,
p_payment_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
p_remmitance_bank_branch_id IN ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL,
p_remmitance_bank_account_id IN ar_receipt_method_accounts.REMIT_BANK_ACCT_USE_ID%TYPE DEFAULT NULL,
p_batch_id OUT NOCOPY NUMBER
) IS
l_batch_rec ar_batches%ROWTYPE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
select bbt.bank_branch_id ,bbt.remit_account_id
from ap_bank_branches bbt
where bbt.bank_branch_id in
(select distinct bb.bank_branch_id
from ap_bank_accounts ba, ap_bank_branches bb, ar_receipt_method_accounts rma, ar_system_parameters asp
where rma.receipt_method_id = nvl(p_receipt_method_id, rma.receipt_method_id )
and rma.bank_account_id = ba.bank_account_id
and ( ba.currency_code = p_currency_code
or ba.receipt_multi_currency_flag = 'Y' )
and ba.set_of_books_id = asp.set_of_books_id
and nvl(ba.inactive_date , fnd_date.canonical_to_date(p_batch_date) + 1 ) > fnd_date.canonical_to_date(p_batch_date)
and fnd_date.canonical_to_date(p_batch_date)
between rma.start_date
and nvl(rma.end_date,fnd_date.canonical_to_date(p_batch_date))
and ( bb.bank_branch_id = ba.bank_branch_id
or ( bb.institution_type = 'CLEARING HOUSE'
and exists
( select 1
from ar_receipt_method_accounts rma2, ap_bank_accounts ba2, ap_bank_branches bb2
where rma2.receipt_method_id = nvl(p_receipt_method_id, rma2.receipt_method_id)
and rma2.bank_account_id = ba2.bank_account_id
and ba2.set_of_books_id = asp.set_of_books_id
and ba2.currency_code = p_currency_code
and ba2.bank_branch_id = bb2.bank_branch_id
and bb2.clearing_house_id = bb.bank_branch_id) ) ) )
order by bbt.bank_branch_name, bbt.bank_name;
arp_rw_batches_pkg.insert_remit_batch(
l_row_id,
l_batch_id,
l_batch_rec.batch_date,
l_batch_rec.currency_code,
l_batch_name, --out
l_batch_rec.comments,
l_batch_rec.exchange_date,
l_batch_rec.exchange_rate,
l_batch_rec.exchange_rate_type,
l_batch_rec.gl_date,
l_batch_rec.media_reference,
l_batch_rec.remit_method_code,
l_batch_rec.receipt_class_id,
l_batch_rec.receipt_method_id,
l_batch_rec.remittance_bank_account_id,
l_batch_rec.remittance_bank_branch_id,
l_batch_rec.bank_deposit_number,
l_batch_rec.attribute_category,
l_batch_rec.attribute1,
l_batch_rec.attribute2,
l_batch_rec.attribute3,
l_batch_rec.attribute4,
l_batch_rec.attribute5,
l_batch_rec.attribute6,
l_batch_rec.attribute7,
l_batch_rec.attribute8,
l_batch_rec.attribute9,
l_batch_rec.attribute10,
l_batch_rec.attribute11,
l_batch_rec.attribute12,
l_batch_rec.attribute13,
l_batch_rec.attribute14,
l_batch_rec.attribute15,
'Y',
l_batch_applied_status, --Out
'AUTOREMSRS',
'1.0'
);
/* inserted the batch record end */
/* GET THE VALUES from SYSTEM PARAMETERS */
IF PG_DEBUG in ('Y','C') THEN
fnd_file.put_line(FND_FILE.LOG, 'get info from system parameters');
SELECT asp.site_required_flag,
asp.auto_rec_invoices_per_commit,
asp.auto_rec_receipts_per_commit,
gsob.currency_code,
asp.accounting_method
INTO psite_required,
pinvoices_per_commit,
preceipts_per_commit,
pfunctional_currency,
pacc_method
FROM ar_system_parameters asp,
gl_sets_of_books gsob,
ar_batches ab
WHERE ab.batch_id = p_batch_id
AND ab.set_of_books_id = gsob.set_of_books_id
AND gsob.set_of_books_id = asp.set_of_books_id;
fnd_file.put_line(FND_FILE.LOG,'insert_batch ()-');
fnd_file.put_line(FND_FILE.LOG,'Exception : insert_batch() ');
END insert_batch;
| PROCEDURE create_and_update_remit_rec |
| |
| DESCRIPTION |
| |
| This procedure is used to select receipts to be remitted |
| update and insert records into the necessary tables. |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-JUL-2005 bichatte Created |
*=========================================================================*/
PROCEDURE create_and_update_remit_rec(
p_batch_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
) IS
l_rows_processed INTEGER;
/* declare the insert array elements */
i NUMBER;
select hist.cash_receipt_id cr_id
from ar_cash_receipt_history hist,
AR_REMIT_GT rec
where hist.STATUS = 'REMITTED'
and hist.cash_receipt_id = rec.cash_receipt_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
select decode(remit_method_code, 'FACTORING', 'Y', 'N')
into l_factor_flag
from ar_batches
where batch_id = p_batch_id;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
l_sel_stmt := ' SELECT cr.cash_receipt_id,
rm.payment_channel_code,
rm.merchant_ref,
cr.currency_code,
cr.pay_from_customer,
cr.customer_site_use_id,
crh.cash_receipt_history_id,
crh.exchange_date,
crh.exchange_rate,
crh.exchange_rate_type,
crh.amount,
crh.acctd_amount,
nvl(cr.factor_discount_amount,0),
decode( bat.remit_method_code,
''FACTORING'', rma.factor_ccid,
rma.remittance_ccid),
rma.bank_charges_ccid,
d.code_combination_id,
ar_cash_receipt_history_s.nextval,
greatest((bat.gl_date), (crh.trx_date)),
greatest((bat.batch_date), (crh.trx_date)),
cr.payment_server_order_num,
cr.approval_code,
cr.receipt_number,
DECODE(cr.unique_reference,
NULL,SYS_GUID(),
cr.unique_reference ),
cr.customer_bank_account_id,
cr.payment_trxn_extension_id
FROM ar_cash_receipts cr, ar_cash_receipt_history crh,
ar_receipt_methods rm,
ar_batches bat,
ar_receipt_method_accounts rma,
ar_payment_schedules ps,
ar_distributions d
WHERE cr.selected_remittance_batch_id = :ab_batch_id
AND cr.REMIT_BANK_ACCT_USE_ID = rma.REMIT_BANK_ACCT_USE_ID
AND bat.batch_id = cr.selected_remittance_batch_id
AND rm.receipt_method_id = cr.receipt_method_id
AND rma.receipt_method_id = cr.receipt_method_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = ''Y''
AND ps.cash_receipt_id(+) = cr.cash_receipt_id
AND crh.status = ''CONFIRMED''
AND crh.cash_receipt_history_id = d.source_id
AND d.source_type = ''CONFIRMATION''
AND d.source_table = ''CRH''
';
fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
inst_stmt := 'insert into AR_REMIT_GT values ( :c1_array,:c2_array,:c3_array,:c4_array,:c5_array,
:c6_array,:c7_array,:c8_array,:c9_array,:c10_array,:c11_array,:c12_array,
:c13_array,:c14_array,:c15_array,:c16_array,:c17_array,:c18_array,
:c19_array,:c20_array,:c21_array,:c22_array,:c23_array,:c24_array,:c25_array)';
/* the update and inserts */
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'upd-crh1 ()+');
/* the first update into ar_cash_receipt_history */
upd_stmt1 := ' UPDATE ar_cash_receipt_history
SET reversal_cash_receipt_hist_id = :ucrh_id_array,
reversal_gl_date = :ucrh_gl_date_array,
reversal_created_from = ''ARZARM'',
current_record_flag = NULL,
last_update_date = sysdate,
last_updated_by = :i_last_updated_by,
last_update_login = :i_last_update_login,
request_id = :i_request_id,
program_application_id = :i_program_application_id,
program_id = :i_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = :cr_id_array
AND current_record_flag = ''Y''
AND status = ''CONFIRMED''
';
dbms_sql.bind_variable (rem_t1,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (rem_t1,':i_last_update_login',l_last_update_login);
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
fnd_file.put_line(FND_FILE.LOG,'failed to update() '|| to_char(SQLCODE));
/* insert into crh */
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'insert-crh1 ()+');
/* the first update into ar_cash_receipt_history */
ins_crh1 := 'INSERT into ar_cash_receipt_history
(cash_receipt_history_id,
cash_receipt_id,
status,
trx_date,
amount,
acctd_amount,
first_posted_record_flag,
postable_flag,
factor_flag,
gl_date,
current_record_flag,
batch_id,
exchange_date,
exchange_rate,
exchange_rate_type,
account_code_combination_id,
reversal_gl_date,
reversal_cash_receipt_hist_id,
prv_stat_cash_receipt_hist_id,
factor_discount_amount,
acctd_factor_discount_amount,
bank_charge_account_ccid,
posting_control_id,
created_from,
reversal_posting_control_id,
gl_posted_date,
reversal_gl_posted_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
org_id,
program_application_id,
program_id,
program_update_date
)
select
r.CASH_RECEIPT_HISTORY_ID,
r.CASH_RECEIPT_ID,
''REMITTED'',
r.CRH_TRX_DATE,
r.cr_amount - r.cr_factor_discount_amount,
r.cr_acctd_amount - DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
''User'', arp_util.functional_amount(
r.cr_factor_discount_amount,
'''||ARP_GLOBAL.functional_currency||''',
nvl(r.exchange_rate,1),
NULL, NULL),
gl_currency_api.convert_amount(
r.currency_code,
'''||ARP_GLOBAL.functional_currency||''',
r.exchange_date,
r.exchange_type,
r.cr_factor_discount_amount)),
''N'',
''Y'',
:factor_flag,
r.crh_gl_date,
''Y'',
:i_batch_id,
r.exchange_date,
r.exchange_rate,
r.exchange_type,
r.remmitance_ccid,
NULL,
NULL,
r.prv_cash_receipt_hist_id,
r.cr_factor_discount_amount,
DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
''User'', arp_util.functional_amount(
r.cr_factor_discount_amount,
'''||ARP_GLOBAL.functional_currency||''',
nvl(r.exchange_rate,1),
NULL, NULL),
gl_currency_api.convert_amount(
r.currency_code,
'''||ARP_GLOBAL.functional_currency||''',
r.exchange_date,
r.exchange_type,
r.cr_factor_discount_amount)),
r.bank_charges_ccid,
''-3'',
''ARZARM'',
NULL,
NULL,
NULL,
:i_created_by,
sysdate,
:i_last_updated_by,
sysdate,
:i_last_update_login,
:i_request_id,
:i_org_id,
:i_program_application_id,
:i_program_id,
sysdate
FROM AR_REMIT_GT r
WHERE r.cash_receipt_history_id is not null';
dbms_sql.bind_variable (rem_t3,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (rem_t3,':i_last_update_login',l_last_update_login);
fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
/* BICHATTE after the insert into crh we have to fire the XLA event */
FOR rec in c_rec LOOP
l_xla_ev_rec.xla_from_doc_id := rec.cr_id;
fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
fnd_file.put_line(FND_FILE.LOG,'failed to insert() '|| to_char(SQLCODE));
fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
/* end insert into crh */
/* insert into dist */
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'insert-dist1 ()+');
/* the first update into ar_cash_receipt_history */
ins_dist1 := 'INSERT into ar_distributions
(line_id,
source_id,
source_table,
source_type,
code_combination_id,
currency_code,
third_party_id,
third_party_sub_id,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_update_date,
last_updated_by,
org_id,
last_update_login)
select
ar_distributions_s.nextval,
r.cash_receipt_history_id,
''CRH'',
decode(:factor_flag,
''N'',decode(l.lookup_code,
''1'',''REMITTANCE'',
''2'',''CONFIRMATION''),
''Y'',decode(l.lookup_code,
''1'',''FACTOR'',
''2'',''CONFIRMATION'')),
decode(l.lookup_code,
''1'',r.REMMITANCE_CCID,
''2'',r.code_combination_id), /* its confirmation_ccid */
r.currency_code,
r.pay_from_customer,
r.customer_site_use_id,
r.exchange_date,
r.exchange_rate,
r.exchange_type,
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_amount)),
''-1'',null,
to_number(r.cr_amount)),
''2'',decode(sign(to_number(r.cr_amount)),
''1'',null,
-(to_number(r.cr_amount)))),
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_amount)),
''1'',null,
-(to_number(r.cr_amount))),
''2'',decode(sign(to_number(r.cr_amount)),
''-1'',null,
to_number(r.cr_amount))),
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_acctd_amount)),
''-1'',null,
to_number(r.cr_acctd_amount)),
''2'',decode(sign(to_number(r.cr_acctd_amount)),
''1'',null,
-(to_number(r.cr_acctd_amount)))),
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_acctd_amount)),
''1'',null,
-(to_number(r.cr_acctd_amount))),
''2'',decode(sign(to_number(r.cr_acctd_amount)),
''-1'',null,
to_number(r.cr_acctd_amount))),
sysdate,
:i_created_by,
sysdate,
:i_last_updated_by,
:i_org_id,
:i_last_update_login
FROM ar_cash_receipt_history crh,AR_REMIT_GT r,
ar_lookups l
WHERE crh.cash_receipt_history_id = r.cash_receipt_history_id
AND l.lookup_type = ''AR_CARTESIAN_JOIN''
AND l.lookup_code IN (''1'',''2'')';
fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
dbms_sql.bind_variable (rem_t4,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (rem_t4,':i_last_update_login',l_last_update_login);
fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
fnd_file.put_line(FND_FILE.LOG,'DIST failed to insert() '|| to_char(SQLCODE));
/* end insert into dist */
EXCEPTION
WHEN others THEN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'Exception : instrem() ');
END create_and_update_remit_rec ;
| PROCEDURE create_and_update_remit_rec_pa |
| |
| DESCRIPTION |
| |
| This procedure is used to select receipts to be remitted |
| update and insert records into the necessary tables. |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 10-JUN-2008 AGHORAKA Created for Parallelization |
*=========================================================================*/
PROCEDURE create_and_update_remit_rec_pa(
p_batch_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2
) IS
l_rows_processed INTEGER;
/* declare the insert array elements */
i NUMBER;
select hist.cash_receipt_id cr_id
from ar_cash_receipt_history hist,
AR_REMIT_GT rec
where hist.STATUS = 'REMITTED'
and hist.cash_receipt_id = rec.cash_receipt_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
select decode(remit_method_code, 'FACTORING', 'Y', 'N')
into l_factor_flag
from ar_batches
where batch_id = p_batch_id;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
l_sel_stmt := ' SELECT cash_receipt_id,
payment_channel_code,
merchant_ref,
currency_code,
pay_from_customer,
customer_site_use_id,
cash_receipt_history_id,
exchange_date,
exchange_rate,
exchange_rate_type,
amount1,
acctd_amount,
nvl(factor_discount_amount,0),
remittance_ccid,
bank_charges_ccid,
code_combination_id,
ar_cash_receipt_history_s.nextval,
crh_gl_date,
crh_trx_date,
payment_server_order_num,
approval_code,
receipt_number,
unique_ref,
customer_bank_account_id,
payment_trxn_extension_id
FROM ar_autorem_interim
WHERE batch_id = :ab_batch_id
AND current_worker = :h_worker_number
';
fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
inst_stmt := 'insert into AR_REMIT_GT values
( :c1_array,:c2_array,:c3_array,:c4_array,:c5_array,
:c6_array,:c7_array,:c8_array,:c9_array,:c10_array,:c11_array,:c12_array,
:c13_array,:c14_array,:c15_array,:c16_array,:c17_array,:c18_array,
:c19_array,:c20_array,:c21_array,:c22_array,:c23_array,:c24_array,:c25_array)';
/* the update and inserts */
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'upd-crh1 ()+');
/* the first update into ar_cash_receipt_history */
upd_stmt1 := ' UPDATE ar_cash_receipt_history
SET reversal_cash_receipt_hist_id = :ucrh_id_array,
reversal_gl_date = :ucrh_gl_date_array,
reversal_created_from = ''ARZARM'',
current_record_flag = NULL,
last_update_date = sysdate,
last_updated_by = :i_last_updated_by,
last_update_login = :i_last_update_login,
request_id = :i_request_id,
program_application_id = :i_program_application_id,
program_id = :i_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = :cr_id_array
AND current_record_flag = ''Y''
AND status = ''CONFIRMED''
';
dbms_sql.bind_variable (rem_t1,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (rem_t1,':i_last_update_login',l_last_update_login);
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
fnd_file.put_line(FND_FILE.LOG,'failed to update() '|| to_char(SQLCODE));
/* insert into crh */
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'insert-crh1 ()+');
/* the first update into ar_cash_receipt_history */
ins_crh1 := 'INSERT into ar_cash_receipt_history
(cash_receipt_history_id,
cash_receipt_id,
status,
trx_date,
amount,
acctd_amount,
first_posted_record_flag,
postable_flag,
factor_flag,
gl_date,
current_record_flag,
batch_id,
exchange_date,
exchange_rate,
exchange_rate_type,
account_code_combination_id,
reversal_gl_date,
reversal_cash_receipt_hist_id,
prv_stat_cash_receipt_hist_id,
factor_discount_amount,
acctd_factor_discount_amount,
bank_charge_account_ccid,
posting_control_id,
created_from,
reversal_posting_control_id,
gl_posted_date,
reversal_gl_posted_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
org_id,
program_application_id,
program_id,
program_update_date
)
select
r.CASH_RECEIPT_HISTORY_ID,
r.CASH_RECEIPT_ID,
''REMITTED'',
r.CRH_TRX_DATE,
r.cr_amount - r.cr_factor_discount_amount,
r.cr_acctd_amount - DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
''User'', arp_util.functional_amount(
r.cr_factor_discount_amount,
'''||ARP_GLOBAL.functional_currency||''',
nvl(r.exchange_rate,1),
NULL, NULL),
gl_currency_api.convert_amount(
r.currency_code,
'''||ARP_GLOBAL.functional_currency||''',
r.exchange_date,
r.exchange_type,
r.cr_factor_discount_amount)),
''N'',
''Y'',
:factor_flag,
r.crh_gl_date,
''Y'',
:i_batch_id,
r.exchange_date,
r.exchange_rate,
r.exchange_type,
r.remmitance_ccid,
NULL,
NULL,
r.prv_cash_receipt_hist_id,
r.cr_factor_discount_amount,
DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
''User'', arp_util.functional_amount(
r.cr_factor_discount_amount,
'''||ARP_GLOBAL.functional_currency||''',
nvl(r.exchange_rate,1),
NULL, NULL),
gl_currency_api.convert_amount(
r.currency_code,
'''||ARP_GLOBAL.functional_currency||''',
r.exchange_date,
r.exchange_type,
r.cr_factor_discount_amount)),
r.bank_charges_ccid,
''-3'',
''ARZARM'',
NULL,
NULL,
NULL,
:i_created_by,
sysdate,
:i_last_updated_by,
sysdate,
:i_last_update_login,
:i_request_id,
:i_org_id,
:i_program_application_id,
:i_program_id,
sysdate
FROM AR_REMIT_GT r
WHERE r.cash_receipt_history_id is not null';
dbms_sql.bind_variable (rem_t3,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (rem_t3,':i_last_update_login',l_last_update_login);
fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
/* BICHATTE after the insert into crh we have to fire the XLA event */
FOR rec in c_rec LOOP
l_xla_ev_rec.xla_from_doc_id := rec.cr_id;
fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
fnd_file.put_line(FND_FILE.LOG,'failed to insert() '|| to_char(SQLCODE));
fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
/* end insert into crh */
/* insert into dist */
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'insert-dist1 ()+');
/* the first update into ar_cash_receipt_history */
ins_dist1 := 'INSERT into ar_distributions
(line_id,
source_id,
source_table,
source_type,
code_combination_id,
currency_code,
third_party_id,
third_party_sub_id,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_update_date,
last_updated_by,
org_id,
last_update_login)
select
ar_distributions_s.nextval,
r.cash_receipt_history_id,
''CRH'',
decode(:factor_flag,
''N'',decode(l.lookup_code,
''1'',''REMITTANCE'',
''2'',''CONFIRMATION''),
''Y'',decode(l.lookup_code,
''1'',''FACTOR'',
''2'',''CONFIRMATION'')),
decode(l.lookup_code,
''1'',r.REMMITANCE_CCID,
''2'',r.code_combination_id), /* its confirmation_ccid */
r.currency_code,
r.pay_from_customer,
r.customer_site_use_id,
r.exchange_date,
r.exchange_rate,
r.exchange_type,
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_amount)),
''-1'',null,
to_number(r.cr_amount)),
''2'',decode(sign(to_number(r.cr_amount)),
''1'',null,
-(to_number(r.cr_amount)))),
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_amount)),
''1'',null,
-(to_number(r.cr_amount))),
''2'',decode(sign(to_number(r.cr_amount)),
''-1'',null,
to_number(r.cr_amount))),
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_acctd_amount)),
''-1'',null,
to_number(r.cr_acctd_amount)),
''2'',decode(sign(to_number(r.cr_acctd_amount)),
''1'',null,
-(to_number(r.cr_acctd_amount)))),
decode(l.lookup_code,
''1'',decode(sign(to_number(r.cr_acctd_amount)),
''1'',null,
-(to_number(r.cr_acctd_amount))),
''2'',decode(sign(to_number(r.cr_acctd_amount)),
''-1'',null,
to_number(r.cr_acctd_amount))),
sysdate,
:i_created_by,
sysdate,
:i_last_updated_by,
:i_org_id,
:i_last_update_login
FROM ar_cash_receipt_history crh,AR_REMIT_GT r,
ar_lookups l
WHERE crh.cash_receipt_history_id = r.cash_receipt_history_id
AND l.lookup_type = ''AR_CARTESIAN_JOIN''
AND l.lookup_code IN (''1'',''2'')';
fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
dbms_sql.bind_variable (rem_t4,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (rem_t4,':i_last_update_login',l_last_update_login);
fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
fnd_file.put_line(FND_FILE.LOG,'DIST failed to insert() '|| to_char(SQLCODE));
/* end insert into dist */
EXCEPTION
WHEN others THEN
IF PG_DEBUG in ('Y', 'C') THEN
fnd_file.put_line(FND_FILE.LOG,'Exception : instrem() ');
END create_and_update_remit_rec_pa ;
| PROCEDURE select_and_update_rec |
| |
| DESCRIPTION |
| |
| This procedure is used to select receipts to be remitted |
| update and insert records into the necessary tables. |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-JUL-2005 bichatte Created |
*=========================================================================*/
PROCEDURE select_update_rec(
p_customer_number_l IN hz_cust_accounts.account_number%TYPE,
p_customer_number_h IN hz_cust_accounts.account_number%TYPE,
p_trx_date_l IN ar_payment_schedules.trx_date%TYPE,
p_trx_date_h IN ar_payment_schedules.trx_date%TYPE,
p_due_date_l IN ar_payment_schedules.due_date%TYPE,
p_due_date_h IN ar_payment_schedules.due_date%TYPE,
p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
p_remittance_total_to IN ar_cash_receipts.amount%TYPE,
p_remittance_total_from IN ar_cash_receipts.amount%TYPE,
p_batch_id IN ar_batches.batch_id%TYPE,
p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
p_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_payment_type_code IN ar_receipt_methods.payment_type_code%TYPE,
p_sob_id IN ar_cash_receipts.set_of_books_id%TYPE,
p_remit_method_code IN ar_receipt_classes.remit_method_code%TYPE,
p_remit_bank_account_id IN ar_cash_receipts.remittance_bank_account_id%TYPE,
p_return_status OUT NOCOPY VARCHAR2
) IS
remit_recs INTEGER;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
ins_stmt := 'insert /*+ append parallel(gtt) */ into ar_rem_cr_id_gtt gtt ';
ins_stmt := 'insert /*+ append */ into ar_rem_cr_id_gtt gtt ';
ins_stmt := ins_stmt ||' select /*+ index(crh AR_CASH_RECEIPT_HISTORY_N6) */
CASH_RECEIPT_HISTORY_ID,
cash_receipt_id
from ar_cash_receipt_history crh
where crh.status = ''CONFIRMED'' AND
crh.current_record_flag = ''Y'' ';
l_sel_stmt := ' SELECT /*+ unnest LEADING(crh,crh1,cr) parallel(crh) parallel(crh1) parallel(cr) parallel(ps) swap_join_inputs(rm) swap_join_inputs(rclass) use_hash(rm,rclass) use_nl(crh1,cr,ps,rma1,rma2) cardinality(crh,10000) */';
l_sel_stmt := ' SELECT /*+ unnest LEADING(crh,crh1,cr) swap_join_inputs(rm) swap_join_inputs(rclass) use_hash(rm,rclass) use_nl(crh1,cr,ps,rma1,rma2) cardinality(crh,10000) */';
(SELECT 1 FROM ar_lookups l
WHERE NVL(cr.reversal_category,''~'') = l.lookup_code
AND l.lookup_type = ''REVERSAL_CATEGORY_TYPE'')
AND cr.receipt_method_id = nvl(:bs_receipt_method_id,cr.receipt_method_id)
AND cr.currency_code = :bs_currency
AND cr.cash_receipt_id = ps.cash_receipt_id(+)
AND cr.receipt_method_id = rm.receipt_method_id
AND (nvl(rm.payment_channel_code,''~'')<>''CREDIT_CARD'' OR (rm.payment_channel_code=''CREDIT_CARD'' AND cr.cc_error_flag IS NULL))
AND cr.selected_remittance_batch_id is null
AND (( cr.amount >= 0) OR
(cr.type = ''MISC'' and cr.amount < 0))
AND cr.set_of_books_id = :bs_sob_id
AND rm.receipt_class_id = rclass.receipt_class_id
AND (rclass.remit_method_code = :bs_remit_method_code
OR rclass.remit_method_code = ''STANDARD_AND_FACTORING''
)
AND rma1.receipt_method_id = cr.receipt_method_id
AND rma1.REMIT_BANK_ACCT_USE_ID = cr.REMIT_BANK_ACCT_USE_ID
AND rma2.receipt_method_id = rma1.receipt_method_id
AND rma2.REMIT_BANK_ACCT_USE_ID= :bs_remit_account_id
AND ((
(nvl(cr.override_remit_account_flag,''Y'') = ''Y'')
AND rma1.unapplied_ccid = rma2.unapplied_ccid
AND rma1.on_account_ccid = rma2.on_account_ccid
AND rma1.unidentified_ccid = rma2.unidentified_ccid
)
OR
(
(nvl(cr.override_remit_account_flag,''Y'') = ''N'')
and cr.REMIT_BANK_ACCT_USE_ID = :bs_remit_account_id
))' ;
AND EXISTS ( select ''x''
from hz_cust_accounts rc
where rc.cust_account_id = cr.pay_from_customer ' ;
upd_stmt2 := ' UPDATE /*+ parallel(R) index(R) */ ar_cash_receipts R';
upd_stmt2 := ' UPDATE /*+ index(R) */ ar_cash_receipts R';
upd_stmt2 := upd_stmt2 ||' SET selected_remittance_batch_id = :u_batch_id,
REMIT_BANK_ACCT_USE_ID = :u_remit_bank_account_id,
last_update_date = sysdate,
last_updated_by = :i_last_updated_by,
last_update_login = :i_last_update_login,
request_id = :i_request_id,
program_application_id = :i_program_application_id,
program_id = :i_program_id,
program_update_date = sysdate
WHERE selected_remittance_batch_id is null ';
/* bind the variables used in update statement */
dbms_sql.bind_variable (remit_recs,':u_batch_id',p_batch_id);
dbms_sql.bind_variable (remit_recs,':i_last_updated_by',l_last_updated_by);
dbms_sql.bind_variable (remit_recs,':i_last_update_login',l_last_update_login);
fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
fnd_file.put_line(FND_FILE.LOG,'sel_and_update recs ()-');
fnd_file.put_line(FND_FILE.LOG,'Exception : select and upd err () ');
fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
END select_update_rec;
| This procedure is used to select receipts to be remitted |
| update and insert records into the necessary tables. |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-JUL-2005 bichatte Created |
*=========================================================================*/
PROCEDURE process_pay_receipt(
p_batch_id IN NUMBER,
p_called_from IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR rct_info_cur IS
SELECT cr.receipt_number,
cr.amount,
cr.cash_receipt_id,
cr.currency_code,
rm.PAYMENT_CHANNEL_CODE, /* NEW ADDED */
rc.creation_status, /* AR USE */
cr.org_id,
party.party_id,
cr.pay_from_customer,
cr.customer_site_use_id,
cr.payment_trxn_extension_id,
cr.selected_remittance_batch_id,
cr.receipt_date
FROM ar_cash_receipts cr,
ar_receipt_methods rm,
ar_receipt_classes rc,
hz_cust_accounts hca,
hz_parties party,
ar_remit_gt g
WHERE cr.selected_remittance_batch_id = p_batch_id
AND g.cash_receipt_id = cr.cash_receipt_id
AND hca.party_id = party.party_id
AND hca.cust_account_id = cr.pay_from_customer
AND cr.receipt_method_id = rm.receipt_method_id
AND rm.receipt_class_id = rc.receipt_class_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
/* SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
into l_auth_flag
FROM iby_trxn_summaries_all summ,
iby_fndcpt_tx_operations op
WHERE summ.transactionid = op.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20)
AND op.trxn_extension_id = l_payment_trxn_extension_id
AND summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20)); */
select AUTHORIZATION_ID
into l_auth_id
from IBY_TRXN_EXT_AUTHS_V
where TRXN_EXTENSION_ID = l_payment_trxn_extension_id;
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = l_response_rec.Result_Code,
cc_error_text = l_response_rec.Result_Message,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = l_authresult_rec.PaymentSys_Code,
cc_error_text = l_authresult_rec.PaymentSys_Msg,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => substrb(l_iby_msg_data||l_vend_msg_data,1,240)
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => l_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => lc_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = lc_response_rec.Result_Code,
cc_error_text = lc_response_rec.Result_Message,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => l_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
select count(*)
into l_count1
from AR_FUNDS_CAPTURE_ORDERS_V
where CALL_APP_SERVICE_REQ_CODE = 'AR_'||p_batch_id;
select cash_receipt_id,pay_from_customer,customer_site_use_id
into l_cr_id,l_paying_customer_id,l_cust_site_id
from ar_cash_receipts
where selected_remittance_batch_id = p_batch_id and
payment_trxn_extension_id = ls_response_rec_tab(i).Trxn_Extension_Id;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_cash_receipt_id => l_cr_id,
p_paying_customer_id =>l_paying_customer_id,
p_paying_site_use_id => l_cust_site_id,
p_exception_code => 'AR_CC_CAPTURE_FAILED',
p_additional_message => ls_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = ls_response_rec_tab(i).Result.Result_Code,
cc_error_text = ls_response_rec_tab(i).Result.Result_Message,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = l_cr_id;
| This procedure is used to select receipts to be remitted |
| update and insert records into the necessary tables. |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 10-JUN-2008 AGHORAKA Created for Parallelization |
*=========================================================================*/
PROCEDURE process_pay_receipt_parallel(
p_batch_id IN NUMBER,
p_called_from IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR rct_info_cur IS
SELECT a.receipt_number,
a.amount2 amount,
a.cash_receipt_id,
a.currency_code,
a.PAYMENT_CHANNEL_CODE, /* NEW ADDED */
a.creation_status, /* AR USE */
a.org_id,
a.party_id,
a.pay_from_customer,
a.customer_site_use_id,
a.payment_trxn_extension_id,
a.batch_id
FROM ar_autorem_interim a
WHERE a.batch_id = p_batch_id
AND a.current_worker = WORKER_NUMBER;
SELECT receipt_date
FROM ar_cash_receipts
WHERE cash_receipt_id = c_cash_receipt_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
/* SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
into l_auth_flag
FROM iby_trxn_summaries_all summ,
iby_fndcpt_tx_operations op
WHERE summ.transactionid = op.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20)
AND op.trxn_extension_id = l_payment_trxn_extension_id
AND summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND trxntypeid IN(2, 3, 20)); */
select AUTHORIZATION_ID
into l_auth_id
from IBY_TRXN_EXT_AUTHS_V
where TRXN_EXTENSION_ID = l_payment_trxn_extension_id;
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = l_response_rec.Result_Code,
cc_error_text = l_response_rec.Result_Message,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = l_authresult_rec.PaymentSys_Code,
cc_error_text = l_authresult_rec.PaymentSys_Msg,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => substrb(l_iby_msg_data||l_vend_msg_data,1,240)
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => l_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
/* 7666285 - Selecting settlement_date*/
IF (rct_info.cash_receipt_id IS NOT NULL) THEN
OPEN rct_date_cur(rct_info.cash_receipt_id);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => lc_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = lc_response_rec.Result_Code,
cc_error_text = lc_response_rec.Result_Message,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>l_payer_rec.cust_account_id,
p_cash_receipt_id => rct_info.cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => l_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = rct_info.cash_receipt_id;
fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
select count(*)
into l_count1
from AR_FUNDS_CAPTURE_ORDERS_V
where CALL_APP_SERVICE_REQ_CODE = 'AR_'||p_batch_id;
select cash_receipt_id,pay_from_customer,customer_site_use_id
into l_cr_id,l_paying_customer_id,l_cust_site_id
from ar_cash_receipts
where selected_remittance_batch_id = p_batch_id and
payment_trxn_extension_id = ls_response_rec_tab(i).Trxn_Extension_Id;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_cash_receipt_id => l_cr_id,
p_paying_customer_id =>l_paying_customer_id,
p_paying_site_use_id => l_cust_site_id,
p_exception_code => 'AR_CC_CAPTURE_FAILED',
p_additional_message => ls_iby_msg_data
);
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
cc_error_code = ls_response_rec_tab(i).Result.Result_Code,
cc_error_text = ls_response_rec_tab(i).Result.Result_Message,
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id= l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = l_cr_id;
| PROCEDURE insert_exceptions |
| |
| DESCRIPTION |
| |
| This procedure is used to insert the exception record when |
| |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-JUL-2005 bichatte Created |
*=========================================================================*/
PROCEDURE insert_exceptions(
p_batch_id IN ar_batches.batch_id%TYPE DEFAULT NULL,
p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
p_due_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
p_cust_min_rec_amount IN NUMBER DEFAULT NULL,
p_bank_min_rec_amount IN NUMBER DEFAULT NULL,
p_exception_code IN VARCHAR2,
p_additional_message IN VARCHAR2
) IS
l_request_id NUMBER;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
fnd_file.put_line(FND_FILE.LOG, 'enter insert exceptions');
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
fnd_file.put_line(FND_FILE.LOG, 'value of l_last_updated_by ' || l_last_updated_by );
fnd_file.put_line(FND_FILE.LOG, 'value of l_last_update_login ' || l_last_update_login );
INSERT
INTO ar_autorec_exceptions
(batch_id,
request_id,
cash_receipt_id,
payment_schedule_id,
paying_customer_id,
paying_site_use_id,
due_date,
cust_min_rec_amount,
bank_min_rec_amount,
exception_code,
additional_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date)
SELECT
p_batch_id,
l_request_id,
p_cash_receipt_id,
p_payment_schedule_id,
p_paying_customer_id,
p_paying_site_use_id,
p_due_date,
p_cust_min_rec_amount,
p_bank_min_rec_amount,
p_exception_code,
p_additional_message,
sysdate,
l_last_updated_by,
sysdate,
l_created_by,
l_last_update_login,
l_program_application_id,
l_program_id,
sysdate FROM DUAL;
fnd_file.put_line(FND_FILE.LOG,'ERROR IN INSERT_AUTOREC_EXCEPTIONS' );
END insert_exceptions;
select org_id into l_org_id
from ar_batches_all
where batch_id = p_batch_id;
select org_id into l_org_id
from ar_batches_all
where batch_id = p_batch_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id in (
SELECT cr.cash_receipt_id
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE crh.request_id = l_request_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y'
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.type = 'CASH'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.settled_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100))
) AND selected_remittance_batch_id = p_batch_id;
INSERT INTO ar_autorec_exceptions
(batch_id,
request_id,
cash_receipt_id,
paying_customer_id,
exception_code,
additional_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date)
SELECT
p_batch_id,
l_request_id,
cr.cash_receipt_id,
cr.pay_from_customer,
'AR_CC_AUTH_FAILED',
'Failure in settlements',
sysdate,
l_last_updated_by,
sysdate,
l_created_by,
l_last_update_login,
l_program_application_id,
l_program_id,
sysdate
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE crh.request_id = l_request_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y'
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.type = 'CASH'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.settled_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100));
UPDATE ar_cash_receipts
SET cc_error_flag = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = sysdate,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE cash_receipt_id in (
SELECT cr.cash_receipt_id
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE crh.request_id = l_request_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y'
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.type = 'MISC'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.returned_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTRETURN','ORAPMTCREDIT')
AND status IN(0, 11, 100))
) AND selected_remittance_batch_id = p_batch_id;
INSERT INTO ar_autorec_exceptions
(batch_id,
request_id,
cash_receipt_id,
paying_customer_id,
exception_code,
additional_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date)
SELECT
p_batch_id,
l_request_id,
cr.cash_receipt_id,
cr.pay_from_customer,
'AR_CC_AUTH_FAILED',
'Failure in settlements',
sysdate,
l_last_updated_by,
sysdate,
l_created_by,
l_last_update_login,
l_program_application_id,
l_program_id,
sysdate
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE crh.request_id = l_request_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y'
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.type = 'MISC'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.returned_flag = 'N'
AND NOT EXISTS (SELECT 'x'
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTRETURN','ORAPMTCREDIT')
AND status IN(0, 11, 100));
UPDATE ar_cash_receipts
SET cc_error_flag = null
WHERE cash_receipt_id in (
SELECT cr.cash_receipt_id
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE cr.request_id = p_request_id
AND cr.cc_error_flag = 'Y'
AND cr.type = 'CASH'
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.settled_flag = 'Y'
AND EXISTS (SELECT 'x'
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100))
) AND request_id = p_request_id
AND cc_error_flag = 'Y';
fnd_file.put_line(FND_FILE.LOG,'CASH receipt rows updated : '||sql%rowcount);
UPDATE ar_cash_receipts
SET cc_error_flag = null
WHERE cash_receipt_id in (
SELECT cr.cash_receipt_id
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE cr.request_id = p_request_id
AND cr.cc_error_flag = 'Y'
AND cr.type = 'MISC'
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.status = 'REMITTED'
AND crh.current_record_flag = 'Y'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.returned_flag = 'Y'
AND EXISTS (SELECT 'x'
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTRETURN','ORAPMTCREDIT')
AND status IN(0, 11, 100))
) AND request_id = p_request_id
AND cc_error_flag = 'Y';
fnd_file.put_line(FND_FILE.LOG,'MISC receipt rows updated : '||sql%rowcount);
fnd_file.put_line(FND_FILE.LOG,'delete the bad receipts');
update ar_cash_receipts
set selected_remittance_batch_id = null
where request_id = p_request_id
and cc_error_flag = 'Y';
fnd_file.put_line(FND_FILE.LOG,' rows updated CR = ' || SQL%ROWCOUNT );
UPDATE ar_cash_receipt_history
SET reversal_cash_receipt_hist_id = null,
reversal_gl_date = null,
reversal_created_from = null,
current_record_flag = 'Y'
where request_id = p_request_id
and status = 'CONFIRMED'
and cash_receipt_id in ( select cash_receipt_id
from ar_cash_receipts
where request_id = p_request_id
and cc_error_flag = 'Y');
fnd_file.put_line(FND_FILE.LOG,' rows UPDATED CRH = ' || SQL%ROWCOUNT );
delete from ar_distributions
where source_table = 'CRH'
and source_id in ( select crh.cash_receipt_history_id
from ar_cash_receipt_history crh,
ar_cash_receipts cr
where crh.STATUS = 'REMITTED'
and crh.request_id = p_request_id
and cr.cash_receipt_id = crh.cash_receipt_id
and cr.request_id = p_request_id
and cr.cc_error_flag = 'Y' );
fnd_file.put_line(FND_FILE.LOG,' rows DELETED AR_DIST = ' || SQL%ROWCOUNT );
delete from ar_cash_receipt_history
where STATUS = 'REMITTED'
and request_id = p_request_id
and cash_receipt_id in ( select cash_receipt_id
from ar_cash_receipts
where request_id = p_request_id
and cc_error_flag = 'Y');
fnd_file.put_line(FND_FILE.LOG,' rows DELETED CRH = ' || SQL%ROWCOUNT );