[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select payment_schedule_id,
receipt_number rec_num,
cash_receipt_id rec_id,
amount_due_remaining amt
from AR_RECEIPTS_GT gt
where payment_schedule_id <> -99999
order by amt;
/* CALL TO INSERT BATCH FROM MAIN */
insert_batch(
l_gl_date,
l_batch_date,
l_receipt_class_id,
l_receipt_method_id,
l_currency_code,
l_approve_flag,
l_format_flag,
l_create_flag,
o_batch_id
);
select batch_date ,gl_date , currency_code, receipt_method_id
into l_batch_date,l_gl_date,l_currency_code, l_receipt_method_id
from AR_BATCHES
where batch_id = p_batch_id;
insert_exceptions(
p_batch_id =>-333,
p_request_id =>l_request_id,
p_exception_code => 'NO_BATCH',
p_additional_message => 'error during insert batch' );
select_valid_invoices
( 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_doc_num_l =>p_doc_num_l,
p_doc_num_h => p_doc_num_h,
p_customer_number_l => p_customer_number_l, --Bug6734688
p_customer_number_h => p_customer_number_h, --Bug6734688
p_customer_name_l => p_customer_name_l, --Bug6734688
p_customer_name_h => p_customer_name_h, --Bug6734688
p_batch_id => o_batch_id,
p_approve_only_flag => 'N',
p_receipt_method_id => l_receipt_method_id,
p_payment_schedule_id =>op_payment_schedule_id,
p_customer_trx_id =>op_customer_trx_id,
p_cash_receipt_id =>op_cash_receipt_id,
p_paying_customer_id =>op_paying_customer_id,
p_paying_site_use_id =>op_paying_site_use_id,
p_payment_server_order_num =>op_payment_server_order_num,
p_due_date => op_due_date,
p_amount_due_remaining =>op_amount_due_remaining,
p_cust_bank_account_id =>op_cust_bank_account_id,
p_cust_min_amt =>op_cust_min_amt,
p_payment_trxn_extension_id =>op_payment_trxn_extension_id,
p_payment_channel_code =>op_payment_channel_code,
p_instrument_type =>op_instrument_type,
p_return_status =>op_return_status
);
update ar_payment_schedules
set selected_for_receipt_batch_id = o_batch_id
where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);
arp_standard.debug ( 'NO of PS rows updated = '|| to_char(SQL%ROWCOUNT));
arp_standard.debug ( 'NO of PS rows updated = '|| to_char(SQL%ROWCOUNT));
update ar_batches SET
batch_applied_status = 'COMPLETED_CREATION'
where batch_id = o_batch_id;
select_valid_invoices
( 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_doc_num_l =>p_doc_num_l,
p_doc_num_h => p_doc_num_h,
p_customer_number_l => p_customer_number_l, --Bug6734688
p_customer_number_h => p_customer_number_h, --Bug6734688
p_customer_name_l => p_customer_name_l, --Bug6734688
p_customer_name_h => p_customer_name_h, --Bug6734688
p_batch_id => o_batch_id,
p_approve_only_flag => 'N',
p_receipt_method_id => l_receipt_method_id,
p_payment_schedule_id =>op_payment_schedule_id,
p_customer_trx_id =>op_customer_trx_id,
p_cash_receipt_id =>op_cash_receipt_id,
p_paying_customer_id =>op_paying_customer_id,
p_paying_site_use_id =>op_paying_site_use_id,
p_payment_server_order_num =>op_payment_server_order_num,
p_due_date => op_due_date,
p_amount_due_remaining =>op_amount_due_remaining,
p_cust_bank_account_id =>op_cust_bank_account_id,
p_cust_min_amt =>op_cust_min_amt,
p_payment_trxn_extension_id =>op_payment_trxn_extension_id,
p_payment_channel_code =>op_payment_channel_code,
p_instrument_type =>op_instrument_type,
p_return_status =>op_return_status
);
update ar_payment_schedules
set selected_for_receipt_batch_id = o_batch_id
where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);
arp_standard.debug ( 'NO of PS rows updated = '|| to_char(SQL%ROWCOUNT));
update ar_batches SET
batch_applied_status = 'COMPLETED_CREATION'
where batch_id = o_batch_id;
arp_standard.debug('Selecting invoices for approval');
select_valid_invoices
( 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_doc_num_l =>p_doc_num_l,
p_doc_num_h => p_doc_num_h,
p_customer_number_l => p_customer_number_l, --Bug6734688
p_customer_number_h => p_customer_number_h, --Bug6734688
p_customer_name_l => p_customer_name_l, --Bug6734688
p_customer_name_h => p_customer_name_h, --Bug6734688
p_batch_id => o_batch_id,
p_approve_only_flag => 'A',
p_receipt_method_id => l_receipt_method_id,
p_payment_schedule_id =>op_payment_schedule_id,
p_customer_trx_id =>op_customer_trx_id,
p_cash_receipt_id =>op_cash_receipt_id,
p_paying_customer_id =>op_paying_customer_id,
p_paying_site_use_id =>op_paying_site_use_id,
p_payment_server_order_num =>op_payment_server_order_num,
p_due_date => op_due_date,
p_amount_due_remaining =>op_amount_due_remaining,
p_cust_bank_account_id =>op_cust_bank_account_id,
p_cust_min_amt =>op_cust_min_amt,
p_payment_trxn_extension_id =>op_payment_trxn_extension_id,
p_payment_channel_code =>op_payment_channel_code,
p_instrument_type =>op_instrument_type,
p_return_status =>op_return_status
);
select decode(rc.confirm_flag,'Y','AUTORECAPI',null),nvl(rm.RECEIPT_CREATION_RULE_CODE,'MANUAL')
,nvl(rm.receipt_inherit_inv_num_flag,'N')
INTO l_called_from,l_rec_creation_rule_code,l_rec_inher_inv_num_flag
from ar_receipt_classes rc,
ar_receipt_methods rm
where rm.receipt_method_id = l_receipt_method_id
and rm.receipt_class_id = rc.receipt_class_id;
excecute ps.selected_for_receipt_batch_id is null
---------------------------------------------------*/
arp_view_constants.set_ps_selected_in_batch('Y');
insert_exceptions(
p_batch_id =>o_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => PS.payment_schedule_id,
p_exception_code => 'AUTORECERR',
p_additional_message => al_count||al_msg_data
);
insert_exceptions(
p_batch_id =>o_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => PS.payment_schedule_id,
p_exception_code => 'AUTORECERR',
p_additional_message => al_count||al_msg_data
);
arp_view_constants.set_ps_selected_in_batch( null);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
select distinct cr.cash_receipt_id cr_id,
cr.payment_trxn_extension_id pmt_trxn_id
from ar_cash_receipts cr,
ar_receipts_gt rec
where rec.cash_receipt_id = cr.cash_receipt_id;
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
insert_exceptions(
p_batch_id =>o_batch_id,
p_request_id =>l_request_id,
p_cash_receipt_id => CREC.cr_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => pl_count||pl_msg_data
);
/* update the error flag in ra_customer_trx */
UPDATE ra_customer_trx
SET cc_error_flag = 'Y',
cc_error_code = l_response_error_code,
cc_error_text = pl_msg_data,
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 customer_trx_id in (SELECT r.customer_trx_id
FROM ar_receipts_gt r,
ar_cash_receipts rec
WHERE r.receipt_number = rec.receipt_number
AND rec.cash_receipt_id = CREC.cr_id);
arp_standard.debug ( ' rows updated TRX = ' || SQL%ROWCOUNT );
insert_exceptions(
p_batch_id =>o_batch_id,
p_request_id =>l_request_id,
p_cash_receipt_id => CREC.cr_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => pl_count||pl_msg_data
);
/* update the error flag in ra_customer_trx */
UPDATE ra_customer_trx
SET cc_error_flag = 'Y',
cc_error_code = l_response_error_code,
cc_error_text = pl_msg_data,
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 customer_trx_id in (SELECT r.customer_trx_id
FROM ar_receipts_gt r,
ar_cash_receipts rec
WHERE r.receipt_number = rec.receipt_number
AND rec.cash_receipt_id = CREC.cr_id);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_login := arp_standard.profile.last_update_login ;
update ar_batches SET
batch_applied_status = 'COMPLETED_APPROVAL'
where batch_id = o_batch_id;
update ar_cash_receipts SET
creation_date = sysdate,
created_by = l_created_by,
last_update_date = sysdate,
last_updated_by = l_created_by,
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 cash_receipt_id from
AR_RECEIPTS_GT);
arp_standard.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
update ar_cash_receipt_history SET
batch_id = o_batch_id,
created_by = l_created_by,
last_update_date = sysdate,
last_updated_by = l_created_by,
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 cash_receipt_id from
AR_RECEIPTS_GT);
arp_standard.debug ( 'NO of Receipts updated CRH = '|| to_char(SQL%ROWCOUNT));
update AR_payment_schedules SET
created_by = l_created_by,
last_update_date = sysdate,
last_updated_by = l_created_by,
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 cash_receipt_id from
AR_RECEIPTS_GT);
arp_standard.debug ( 'NO of Receipts updated PS = '|| to_char(SQL%ROWCOUNT));
update ar_receivable_applications SET
created_by = l_created_by,
last_update_date = sysdate,
last_updated_by = l_created_by,
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 cash_receipt_id from
AR_RECEIPTS_GT);
update ar_payment_schedules
set selected_for_receipt_batch_id = NULL
where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);
update ar_cash_receipts cr SET
receipt_number = nvl((
select ps.trx_number
from
ar_receivable_applications app,
ar_payment_schedules ps
where
app.cash_receipt_id = cr.cash_receipt_id
and ps.customer_trx_id = app.applied_customer_trx_id
and app.status = 'APP'
and rownum =1),receipt_number)
WHERE cash_receipt_id in (
select cash_receipt_id from ar_receipts_gt)
AND request_id = l_request_id;
arp_standard.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
| 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_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
p_receipt_class_id IN ar_receipt_classes.receipt_class_id%TYPE DEFAULT NULL,
p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE DEFAULT NULL,
p_currency_code IN ar_cash_receipts.currency_code%TYPE DEFAULT NULL,
p_approve_flag IN ar_cash_receipts.confirmed_flag%TYPE DEFAULT NULL,
p_format_flag IN ar_cash_receipts.confirmed_flag%TYPE DEFAULT NULL,
p_create_flag IN ar_cash_receipts.confirmed_flag%TYPE DEFAULT NULL,
p_batch_id OUT NOCOPY NUMBER
) IS
l_batch_rec ar_batches%ROWTYPE;
/* insert the batch record here */
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('autorecapi calling auto_batch ()+');
arp_rw_batches_pkg.insert_auto_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.receipt_class_id,
l_batch_rec.receipt_method_id,
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,
l_call_conc_request,
l_batch_applied_status, --Out
l_request_id,--OUT
'AUTORECSRS',
'1.0',
l_bank_account_id_low,
l_bank_account_id_high
);
/* inserted the batch record end */
/* GET THE VALUES from SYSTEM PARAMETERS */
IF PG_DEBUG in ('Y','C') THEN
arp_standard.debug( '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;
arp_standard.debug('Exception : insert_batch() ');
END insert_batch;
| PUBLIC PROCEDURE SELECT_VALID_INVOICES |
| |
| DESCRIPTION |
| |
| This procedure is used to select the valied invoices and insert them |
| into the GT table AR_RECEIPTS_GT |
| PSEUDO CODE/LOGIC |
| |
| PARAMETERS |
| |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 16-JUL-2005 bichatte Created |
*=========================================================================*/
PROCEDURE select_valid_invoices(
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_doc_num_l IN ra_customer_trx.doc_sequence_value%TYPE,
p_doc_num_h IN ra_customer_trx.doc_sequence_value%TYPE,
p_customer_number_l IN hz_cust_accounts.account_number%TYPE, --Bug6734688
p_customer_number_h IN hz_cust_accounts.account_number%TYPE, --Bug6734688
p_customer_name_l IN hz_parties.party_name%TYPE, --Bug6734688
p_customer_name_h IN hz_parties.party_name%TYPE, --Bug6734688
p_batch_id IN ar_batches.batch_id%TYPE,
p_approve_only_flag IN VARCHAR2 ,--Bug5344405
p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
p_payment_schedule_id OUT NOCOPY ar_payment_schedules.payment_schedule_id%TYPE,
p_customer_trx_id OUT NOCOPY ar_payment_schedules.customer_trx_id%TYPE,
p_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
p_paying_customer_id OUT NOCOPY ar_payment_schedules.customer_id%TYPE,
p_paying_site_use_id OUT NOCOPY hz_cust_site_uses.site_use_id%TYPE,
p_payment_server_order_num OUT NOCOPY ra_customer_trx.payment_server_order_num%TYPE,
p_due_date OUT NOCOPY ar_payment_schedules.due_date%TYPE,
p_amount_due_remaining OUT NOCOPY ar_payment_schedules.amount_due_remaining%TYPE,
p_cust_bank_account_id OUT NOCOPY ra_customer_trx.customer_bank_account_id%TYPE,
p_cust_min_amt OUT NOCOPY hz_cust_profile_amts.auto_rec_min_receipt_amount%TYPE,
p_payment_trxn_extension_id OUT NOCOPY ra_customer_trx.payment_trxn_extension_id%TYPE,
p_payment_channel_code OUT NOCOPY ar_receipt_methods.payment_channel_code%TYPE,
p_instrument_type OUT NOCOPY IBY_PMT_INSTR_USES_ALL.instrument_type%TYPE,
p_return_status OUT NOCOPY VARCHAR2
) IS
trx_invoices INTEGER;
SELECT b.currency_code,
b.batch_date,
r.lead_days,
r.receipt_creation_rule_code
INTO p_currency_code,
p_batch_date,
p_lead_days,
p_creation_rule
from ar_batches b,
ar_receipt_methods r
WHERE b.batch_id = p_batch_id
AND b.receipt_method_id = r.receipt_method_id
AND r.receipt_method_id = p_receipt_method_id;
SELECT /*+ leading(PS1) use_nl(ps,cust_cp,site_cp,cust_cpa,site_cpa,ct,x,u,p) rowid(ps) index_ffs(ps1) parallel_index(ps1) */
ps.payment_schedule_id,
ps.customer_trx_id,
ps.cash_receipt_id,
ct.paying_customer_id,
ct.paying_site_use_id,
ct.payment_trxn_extension_id,
ps.due_date,
AR_AUTOREC_API.Get_Invoice_Bal_After_Disc(ps.payment_schedule_id,:apply_date),
ct.customer_bank_account_id,
DECODE(:creation_rule,
''PER_CUSTOMER'', NVL(cust_cpa.auto_rec_min_receipt_amount,0),
''PER_CUSTOMER_DUE_DATE'', NVL(cust_cpa.auto_rec_min_receipt_amount,0),
nvl(nvl(site_cpa.auto_rec_min_receipt_amount,cust_cpa.auto_rec_min_receipt_amount),0)),
p.payment_channel_code,
u.instrument_id
FROM hz_customer_profiles cust_cp,
hz_customer_profiles site_cp,
hz_cust_profile_amts cust_cpa,
hz_cust_profile_amts site_cpa,
ra_customer_trx ct,
iby_fndcpt_tx_extensions x,
iby_pmt_instr_uses_all u,
iby_fndcpt_pmt_chnnls_b p,
ar_payment_schedules ps,
ar_payment_schedules_all ps1 ';
l_sel_stmt := l_sel_stmt|| ' AND ps.selected_for_receipt_batch_id = :batch_id';
l_sel_stmt := l_sel_stmt|| ' AND ps.selected_for_receipt_batch_id IS NULL ';
l_sel_stmt := l_sel_stmt || ' FOR UPDATE OF ps.selected_for_receipt_batch_id ';
arp_standard.debug( 'the select statemnt' || l_sel_stmt);
inst_stmt := 'insert into ar_receipts_gt values ( :psid_array,:trxid_array,:crid_array,
:paycust_array,:paysite_array,:pmt_trxn_id_array,
:duedate_array,:amtdue_array,:custbank_array,
:cust_amt_array,null,:pmt_channel_array,:pmt_instr_array,null)';
arp_standard.debug( 'the select statemnt' || l_sel_stmt);
END select_valid_invoices;
select ps.customer_id,
ps.customer_site_use_id,
ps.term_id,
ps.terms_sequence_number,
ps.trx_date,
ps.amount_due_original,
ps.amount_due_remaining,
ps.invoice_currency_code,
ps.discount_taken_unearned,
ps.discount_taken_earned,
ps.exchange_rate,
ctt.allow_overapplication_flag
into
l_customer_id,
l_bill_to_site_use_id,
l_term_id,
l_installment,
l_trx_date,
l_amount_due_original,
l_amount_due_remaining,
l_trx_currency_code,
l_discount_taken_unearned,
l_discount_taken_earned,
l_trx_exchange_rate,
l_allow_overappln_flag
from ar_payment_schedules ps,
ra_cust_trx_types ctt
where ps.payment_schedule_id = l_applied_payment_schedule_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
SELECT receipt_creation_rule_code,receipt_class_id
INTO p_creation_rule,l_receipt_class_id
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
SELECT decode( confirm_flag, 'Y','AUTORECAPI',null)
INTO l_called_from
FROM ar_receipt_classes
WHERE receipt_class_id = l_receipt_class_id;
SQL> select distinct receipt_creation_rule_code
2 from ar_receipt_methods;
SELECT DISTINCT payment_schedule_id
FROM ar_receipts_gt
WHERE PAYMENT_SCHEDULE_ID is not null;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
,nvl(r.receipt_inherit_inv_num_flag,'N')
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
,l_rec_inher_inv_num_flag
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE AR_RECEIPTS_GT
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE payment_schedule_id = cust1.payment_schedule_id;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
update ar_receipts_gt arg
set receipt_number = (select trx_number||decode(terms_sequence_number,1,'','-'||terms_sequence_number)
from ar_payment_schedules ps
where ps.payment_schedule_id = arg.payment_schedule_id
and rownum = 1)
where payment_schedule_id > 0;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated to set receipt_number.');
select receipt_number rec_num,
customer_trx_id,
payment_schedule_id,
paying_customer_id pay_cust_id,
payment_trxn_extension_id pmt_trxn_ext_id,
paying_site_use_id pay_site_use_id,
sum(amount_due_remaining) amt
from AR_RECEIPTS_GT
group by receipt_number,customer_trx_id,payment_schedule_id,paying_customer_id,payment_trxn_extension_id, paying_site_use_id;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R2.pay_cust_id
AND auto_rec_min_receipt_amount > R2.amt
AND currency_code = p_currency_code); -- Currency_Code Condition Added for Bug:5488085
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R2.payment_schedule_id,
p_paying_customer_id =>R2.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R2.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num;
select nvl(terms_sequence_number,1)
into l_installment
from ar_payment_schedules
where payment_schedule_id = R2.payment_schedule_id;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R2.rec_num
and customer_trx_id = R2.customer_trx_id
and payment_schedule_id = R2.payment_schedule_id;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R2.rec_num
and customer_trx_id = R2.customer_trx_id
and payment_schedule_id = R2.payment_schedule_id);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num
and customer_trx_id = R2.customer_trx_id
and payment_schedule_id = R2.payment_schedule_id;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R2.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R2.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
SELECT paying_customer_id,payment_instrument
FROM AR_RECEIPTS_GT
WHERE PAYMENT_SCHEDULE_ID is not null
Group by paying_customer_id,payment_instrument;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE AR_RECEIPTS_GT
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE paying_customer_id = cust2.paying_customer_id
AND payment_instrument = cust2.payment_instrument;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
sum(amount_due_remaining) amt
from AR_RECEIPTS_GT
group by receipt_number,paying_customer_id;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R1.pay_cust_id
AND auto_rec_min_receipt_amount > R1.amt
AND currency_code = p_currency_code); -- For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R1.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R1_inv.payment_schedule_id,
p_paying_customer_id =>R1.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R1.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;
select payment_trxn_extension_id
into l_payment_trxn_extension_id
from ar_receipts_gt
where receipt_number = R1.rec_num
and paying_customer_id = R1.pay_cust_id
and rownum = 1;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R1.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R1.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R1.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R1.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
SELECT paying_site_use_id,payment_instrument
FROM AR_RECEIPTS_GT
WHERE PAYMENT_SCHEDULE_ID is not null
Group by paying_site_use_id,payment_instrument;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE AR_RECEIPTS_GT
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE paying_site_use_id = cust3.paying_site_use_id
AND payment_instrument = cust3.payment_instrument;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
paying_site_use_id pay_site_id,
sum(amount_due_remaining) amt
from AR_RECEIPTS_GT
group by receipt_number,paying_customer_id,paying_site_use_id;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R3.pay_cust_id
AND auto_rec_min_receipt_amount > R3.amt
AND currency_code = p_currency_code); --For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R3.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R3_inv.payment_schedule_id,
p_paying_customer_id =>R3.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R3.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;
select payment_trxn_extension_id
into l_payment_trxn_extension_id
from ar_receipts_gt
where receipt_number = R3.rec_num
and paying_customer_id = R3.pay_cust_id
and paying_site_use_id = R3.pay_site_id
and rownum = 1;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R3.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R3.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R3.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R3.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
SELECT paying_site_use_id ,due_date,payment_instrument
FROM ar_receipts_gt
WHERE payment_schedule_id is not null
Group by paying_site_use_id,due_date,payment_instrument;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE AR_RECEIPTS_GT
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE due_date = cust4.due_date
AND payment_instrument = cust4.payment_instrument
AND paying_site_use_id = cust4.paying_site_use_id;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
paying_site_use_id pay_site_id,
due_date,
sum(amount_due_remaining) amt
from ar_receipts_gt
group by receipt_number,paying_customer_id,paying_site_use_id,due_date;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R4.pay_cust_id
AND auto_rec_min_receipt_amount > R4.amt
AND currency_code = p_currency_code);--For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R4.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R4_inv.payment_schedule_id,
p_paying_customer_id =>R4.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R4.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;
select payment_trxn_extension_id
into l_payment_trxn_extension_id
from ar_receipts_gt
where receipt_number = R4.rec_num
and paying_customer_id = R4.pay_cust_id
and paying_site_use_id = R4.pay_site_id
and due_date = R4.due_date
and rownum = 1;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R4.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R4.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R4.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R4.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
SELECT paying_customer_id ,due_date,payment_instrument
FROM ar_receipts_gt
WHERE payment_schedule_id is not null
Group by paying_customer_id ,due_date,payment_instrument;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE AR_RECEIPTS_GT
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE due_date = cust5.due_date
AND payment_instrument = cust5.payment_instrument;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
due_date,
sum(amount_due_remaining) amt
from ar_receipts_gt
group by receipt_number,paying_customer_id,due_date;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R5.pay_cust_id
AND auto_rec_min_receipt_amount > R5.amt
AND currency_code = p_currency_code); --For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R5.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R5_inv.payment_schedule_id,
p_paying_customer_id =>R5.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R5.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;
select payment_trxn_extension_id
into l_payment_trxn_extension_id
from ar_receipts_gt
where receipt_number = R5.rec_num
and paying_customer_id = R5.pay_cust_id
and due_date = R5.due_date
and rownum = 1;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R5.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R5.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R5.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R5.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
select payment_channel_code,paying_customer_id,payment_trxn_extension_id
from ar_receipts_gt where payment_schedule_id is not null
group by payment_channel_code, paying_customer_id,payment_trxn_extension_id;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE ar_receipts_gt
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE payment_channel_code = cust6.payment_channel_code
and payment_trxn_extension_id = cust6.payment_trxn_extension_id;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
payment_trxn_extension_id pmt_trxn_ext_id,
sum(amount_due_remaining) amt
from ar_receipts_gt
group by receipt_number,paying_customer_id,customer_bank_account_id,payment_trxn_extension_id;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R6.pay_cust_id
AND auto_rec_min_receipt_amount > R6.amt
AND currency_code = p_currency_code);--For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R6.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R6_inv.payment_schedule_id,
p_paying_customer_id =>R6.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R6.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R6.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R6.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R6.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R6.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
select payment_instrument,paying_customer_id,payment_trxn_extension_id
from ar_receipts_gt where payment_schedule_id is not null
group by payment_instrument,paying_customer_id,payment_trxn_extension_id;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE ar_receipts_gt
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE payment_instrument = cust7.payment_instrument
and payment_trxn_extension_id = cust7.payment_trxn_extension_id;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
payment_trxn_extension_id pmt_trxn_ext_id,
sum(amount_due_remaining) amt
from ar_receipts_gt
group by receipt_number,paying_customer_id,payment_trxn_extension_id;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R7.pay_cust_id
AND auto_rec_min_receipt_amount > R7.amt
AND currency_code = p_currency_code); --For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R7.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R7_inv.payment_schedule_id,
p_paying_customer_id =>R7.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R7.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R7.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R7.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R7.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R7.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
select authorization_id,paying_customer_id,payment_trxn_extension_id
from ar_receipts_gt where payment_schedule_id is not null
group by authorization_id,paying_customer_id,payment_trxn_extension_id;
select b.set_of_books_id,r.name,
b.batch_date,
b.currency_code
/** Changes for Bug 7313058 Start Here **/
,DECODE(exchange_rate_type,'User',exchange_rate,NULL)
,exchange_date
,exchange_rate_type
/** Changes for Bug 7313058 End Here **/
into p_set_of_books_id,
p_name,
p_batch_date,
p_currency_code
/** Changes for Bug 7313058 Start Here **/
,p_exchange_rate
,p_exchange_date
,p_exchange_rate_type
/** Changes for Bug 7313058 End Here **/
from ar_batches b,
ar_receipt_methods r
where b.batch_id = p_batch_id
and r.receipt_method_id = p_receipt_method_id
and b.receipt_method_id = r.receipt_method_id;
UPDATE ar_receipts_gt
SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
WHERE authorization_id = cust8.authorization_id
and payment_trxn_extension_id = cust8.payment_trxn_extension_id;
arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
select receipt_number rec_num,
paying_customer_id pay_cust_id,
payment_trxn_extension_id pmt_trxn_ext_id,
sum(amount_due_remaining) amt
from ar_receipts_gt
group by receipt_number,paying_customer_id,payment_trxn_extension_id;
select 'ARZCAR_CUST_MIN_AMT'
INTO l_err_code
from dual
WHERE exists (select 1 from HZ_CUST_PROFILE_AMTS where cust_account_id = R8.pay_cust_id
AND auto_rec_min_receipt_amount > R8.amt
AND currency_code = p_currency_code); --For Bug:5488085
select payment_schedule_id
from AR_RECEIPTS_GT
where receipt_number = R8.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_payment_schedule_id => R8_inv.payment_schedule_id,
p_paying_customer_id =>R8.pay_cust_id,
p_exception_code => l_err_code ,
p_additional_message => l_err_code
);
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R8.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;
Update ar_receipts_gt set cash_receipt_id = l_cr_id
where receipt_number = R8.rec_num;
Update ar_payment_schedules set selected_for_receipt_batch_id = null
where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
where receipt_number = R8.rec_num);
Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R8.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>R8.pay_cust_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data
);
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>l_request_id,
p_paying_customer_id =>-3,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => SQLERRM
);
| 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;
arp_standard.debug( 'enter insert exceptions');
l_last_updated_by := arp_standard.profile.last_update_login ;
l_last_update_login := arp_standard.profile.last_update_login ;
select pay_from_customer
into l_paying_customer_id
from ar_cash_receipts
where cash_receipt_id = p_cash_receipt_id;
arp_standard.debug( 'value of l_last_updated_by ' || l_last_updated_by );
arp_standard.debug( '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,
l_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;
arp_standard.debug ( '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;
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 /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
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 customer_trx_id in (
SELECT r.customer_trx_id
FROM ar_cash_receipts cr,
ar_receipts_gt r,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE cr.request_id = l_request_id
AND r.cash_receipt_id = cr.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.status = 'CONFIRMED'
AND crh.current_record_flag = 'Y'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.authorized_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 ('ORAPMTREQ','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100))
);
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 Authorization',
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 cr.request_id = l_request_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.status = 'CONFIRMED'
AND crh.current_record_flag = 'Y'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.authorized_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 ('ORAPMTREQ','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100));
fnd_file.put_line(FND_FILE.LOG,'insert into autorec_exceptions count : '||sql%rowcount);
payment_schedule_id before going in for the delete */
UPDATE /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
SET cc_error_flag = null,
cc_error_code = null,
cc_error_text = null
WHERE customer_trx_id in (
SELECT r.customer_trx_id
FROM ar_cash_receipts cr,
ar_receipts_gt r,
ar_cash_receipt_history crh,
iby_trxn_extensions_v trxn_ext
WHERE cr.request_id = l_request_id
AND r.cash_receipt_id = cr.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.status = 'CONFIRMED'
AND crh.current_record_flag = 'Y'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.authorized_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 ('ORAPMTREQ','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100))
) AND cc_error_flag = 'Y';
fnd_file.put_line(FND_FILE.LOG,'receipt rows updated to reset cc_error_flag : '||sql%rowcount);
delete from ar_autorec_exceptions
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 = l_request_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.status = 'CONFIRMED'
AND crh.current_record_flag = 'Y'
AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
AND trxn_ext.authorized_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 ('ORAPMTREQ','ORAPMTBATCHREQ')
AND summ.status IN(0, 11, 100))
) and request_id = l_request_id;
fnd_file.put_line(FND_FILE.LOG,'rows deleted from ar_autorec_exceptions: '||sql%rowcount);
select ps.payment_schedule_id ps_id,
ps.trx_number trx_num,
nvl(ps.terms_sequence_number,1) inst_num,
ps.customer_trx_id trx_id,
r.receipt_number rec_num,
r.cash_receipt_id rec_id
from ar_payment_schedules ps,
ra_customer_trx trx,
ar_receipts_gt r
where trx.customer_trx_id = ps.customer_trx_id
and trx.cc_error_flag = 'Y'
and r.payment_schedule_id = ps.payment_schedule_id;
arp_standard.debug('delete the bad receipts');
/* Start of delete XLA events code. Doing this is bulk */
Begin
IF PG_DEBUG in ('Y','C') THEN
arp_standard.debug ( 'Start calling xla delete_bulk_events');
arp_standard.debug ( 'Inserting into xla_events_int_gt...');
INSERT INTO xla_events_int_gt
(event_id
,ledger_id
,entity_code
,application_id
,event_type_code
,entity_id
,event_number
,event_status_code
,process_status_code
,event_date
,transaction_date
,budgetary_control_flag
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,on_hold_flag)
( SELECT event_id
,ledger_id
,entity_code
,xte.application_id
,event_type_code
,xte.entity_id
,event_number
,event_status_code
,process_status_code
,TRUNC(event_date)
,nvl(transaction_date, TRUNC(event_date))
,'N'
,reference_num_1
,reference_num_2
,reference_num_3
,reference_num_4
,reference_char_1
,reference_char_2
,reference_char_3
,reference_char_4
,reference_date_1
,reference_date_2
,reference_date_3
,reference_date_4
,on_hold_flag
from xla_transaction_entities_upg xte,
xla_events xe
where xte.application_id = 222
and xte.entity_code = 'RECEIPTS'
and xe.application_id = 222
and xe.event_number > 0
and xe.entity_id = xte.entity_id
and xte.ledger_id = ARP_STANDARD.sysparm.set_of_books_id
and NVL(xte.source_id_int_1, -99) IN
(select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id));
arp_standard.debug ( 'rows inserted into xla gt table = '|| sql%rowcount);
arp_standard.debug ( 'Calling xla_events_pub_pkg.delete_bulk_events()');
xla_events_pub_pkg.delete_bulk_events(222);
arp_standard.debug ( 'End calling xla delete_bulk_events');
arp_standard.debug('Error in call to xla_events_pub_pkg.delete_bulk_events ' || sqlerrm);
/* End of delete XLA events code */
update ar_payment_schedules
set selected_for_receipt_batch_id = null,
gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD'),
status = 'OP'
where payment_schedule_id in (
select ps.payment_schedule_id
from ar_payment_schedules ps,
ra_customer_trx trx
where trx.customer_trx_id = ps.customer_trx_id
and trx.cc_error_flag = 'Y'
and trx.request_id = l_request_id);
arp_standard.debug ( ' rows updated PS = ' || SQL%ROWCOUNT );
delete from ar_payment_schedules
where cash_receipt_id in (select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id);
arp_standard.debug ( ' rows DELETED PS = ' || SQL%ROWCOUNT );
delete from ar_distributions
where source_table = 'CRH'
and source_id in ( select cash_receipt_history_id
from ar_cash_receipt_history
where cash_receipt_id in ( select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id));
arp_standard.debug ( ' rows DELETED AR_DIST = ' || SQL%ROWCOUNT );
delete from ar_distributions
where source_table = 'RA'
and source_id in ( select receivable_application_id
from ar_receivable_applications
where cash_receipt_id in ( select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id));
arp_standard.debug ( ' rows DELETED AR_DIST2 = ' || SQL%ROWCOUNT );
delete from ar_receivable_applications
where cash_receipt_id in ( select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id);
arp_standard.debug ( ' rows DELETED REC_APPS = ' || SQL%ROWCOUNT );
delete from ar_cash_receipt_history
where cash_receipt_id in ( select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id);
arp_standard.debug ( ' rows DELETED CRH = ' || SQL%ROWCOUNT );
delete from ar_cash_receipts
where cash_receipt_id in ( select distinct cash_receipt_id
from ar_autorec_exceptions
where request_id = l_request_id);
arp_standard.debug ( ' rows DELETED CR = ' || SQL%ROWCOUNT );
select org_id into l_org_id
from ar_batches_all
where batch_id = p_batch_id;
update ar_batches SET
batch_applied_status = 'COMPLETED_APPROVAL'
where batch_id = p_batch_id;
update ar_batches SET
batch_applied_status = 'COMPLETED_APPROVAL'
where batch_id = p_batch_id;
update ar_batches SET
batch_applied_status = 'COMPLETED_FORMAT'
where batch_id = p_batch_id;