[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE process_selected_receipts(
p_receipt_method_id in ar_cash_receipts.receipt_method_id%type default null,
p_batch_id in ar_batches.batch_id%type,
p_approval_mode IN VARCHAR2 DEFAULT 'APPROVE'
);
pg_last_updated_by NUMBER;
pg_last_update_login NUMBER;
update ar_cash_receipts
SET creation_date = sysdate,
created_by = pg_created_by,
last_update_date = sysdate,
last_updated_by = pg_created_by,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id = pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE cash_receipt_id in
( select cash_receipt_id
from AR_RECEIPTS_GT
where gt_id = p_gt_id );
arp_debug.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
update ar_cash_receipt_history SET
batch_id = p_batch_id,
created_by = pg_created_by,
last_update_date = sysdate,
last_updated_by = pg_created_by,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id = pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE cash_receipt_id in
( select cash_receipt_id
from AR_RECEIPTS_GT
where gt_id = p_gt_id );
arp_debug.debug ( 'NO of Receipts updated CRH = '|| to_char(SQL%ROWCOUNT));
update AR_payment_schedules SET
created_by = pg_created_by,
last_update_date = sysdate,
last_updated_by = pg_created_by,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id = pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE cash_receipt_id in
( select cash_receipt_id
from AR_RECEIPTS_GT
where gt_id = p_gt_id );
arp_debug.debug ( 'NO of Receipts updated PS = '|| to_char(SQL%ROWCOUNT));
update ar_receivable_applications SET
created_by = pg_created_by,
last_update_date = sysdate,
last_updated_by = pg_created_by,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id = pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE cash_receipt_id in
( select cash_receipt_id
from AR_RECEIPTS_GT
where gt_id = p_gt_id );
/* Bug 10137089 : Do not update receipt_batch_id
if require confirm flag is checked on receipt class */
update ar_payment_schedules
set selected_for_receipt_batch_id = NULL
where payment_schedule_id in
( select /*+ unnest */ r.payment_schedule_id
from ar_receipts_gt r,
ar_receivable_applications ra,
ra_customer_trx ct,
ar_receipt_methods rm,
ar_receipt_classes rc
where r.gt_id = p_gt_id
and ra.applied_customer_trx_id = r.customer_trx_id
and ra.request_id = pg_request_id
and ra.status = 'APP'
and ct.customer_trx_id = r.customer_trx_id
and ct.receipt_method_id = rm.receipt_method_id
and rm.receipt_class_id = rc.receipt_class_id
and rc.confirm_flag = 'N'
UNION ALL
select r.payment_schedule_id
from ar_receipts_gt r,
ra_customer_trx ct
where r.gt_id = p_gt_id
and ct.customer_trx_id = r.customer_trx_id
and ct.cc_error_flag = 'Y'
);
arp_debug.debug ( 'NO of RA updated = '|| to_char(SQL%ROWCOUNT));
select min(gt.cash_receipt_id),
max(gt.cash_receipt_id),
min(ra.receivable_application_id),
max(ra.receivable_application_id)
into l_from_doc_id,
l_to_doc_id,
l_from_ra_doc_id,
l_to_ra_doc_id
from AR_RECEIPTS_GT gt,
ar_receivable_applications ra
where gt.cash_receipt_id = ra.cash_receipt_id
and gt_id = p_gt_id;
Select min(ra.receivable_application_id),
max(ra.receivable_application_id)
into l_from_ra_doc_id,
l_to_ra_doc_id
from AR_RECEIPTS_GT gt,
ar_receivable_applications ra
where gt.cash_receipt_id = ra.cash_receipt_id
and gt_id = p_gt_id
and ra.event_id is null ;
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => pg_request_id,
p_exception_code => 'AUTORECERR',
p_additional_message => 'process_events() '|| SQLERRM );
FOR rec IN (select * from ar_receipts_gt) LOOP
arp_debug.debug( '------------------------------------------------------');
select receipt_method_id
into l_receipt_method_id
from ar_batches
where batch_id = p_batch_id;
process_selected_receipts( p_receipt_method_id => l_receipt_method_id,
p_batch_id => p_batch_id,
p_approval_mode => 'RE-APPROVAL');
UPDATE ar_payment_schedules
SET selected_for_receipt_batch_id = null
WHERE selected_for_receipt_batch_id = p_batch_id
AND status = 'CL';
delete
from ar_receipts_gt;
select payment_schedule_id,
receipt_number rec_num,
amount_due_remaining amt
from AR_RECEIPTS_GT;
pg_last_updated_by := arp_standard.profile.last_update_login ;
pg_last_update_login := arp_standard.profile.last_update_login ;
arp_debug.debug('pg_last_updated_by ' || pg_last_updated_by);
arp_debug.debug('pg_last_update_login ' || pg_last_update_login);
SELECT CASE WHEN (NVL(auto_rec_receipts_per_commit,0) <= 0) THEN 1000
ELSE auto_rec_receipts_per_commit END
INTO MAX_ARRAY_SIZE
FROM ar_system_parameters;
arp_debug.debug('p_batch_id is null,Calling insert_batch..');
/* 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,
fnd_date.canonical_to_date(p_exchange_date),
to_number(p_exchange_rate),
p_exchange_rate_type,
o_batch_id
);
select batch_date ,
gl_date ,
currency_code,
receipt_method_id,
batch_applied_status
into l_batch_date,
l_gl_date,
l_currency_code,
l_receipt_method_id,
l_batch_app_status
from AR_BATCHES
where batch_id = p_batch_id;
insert_exceptions(
p_batch_id =>-333,
p_request_id =>pg_request_id,
p_exception_code => 'NO_BATCH',
p_additional_message => 'error during insert batch' );
stamps batch_id on all the selected invoices and populates the data into
interim and GT tables.
If the batch exists prior to this run then it selects all the invoices
associated to the current batch and populates the data into interim and
GT tables.
This distinction is done based on l_approve_only_flag being passed to
procedure select_valid_invoices */
IF ( p_create_flag = 'Y' OR
p_approve_flag = 'Y' ) AND
G_ERROR = 'N' THEN
IF PG_DEBUG in ('Y','C') THEN
arp_debug.debug('l_approve_only_flag :' || l_approve_only_flag);
arp_debug.debug('selecting the data for batch_id :' || to_char(o_batch_id));
* table with the selected data.*/
IF l_total_workers = 0 THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.debug ( ' l_total_workers :'||l_total_workers);
arp_debug.debug ( ' Calling select_valid_invoices..');
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,
p_customer_number_h => p_customer_number_h,
p_customer_name_l => p_customer_name_l,
p_customer_name_h => p_customer_name_h,
p_batch_id => o_batch_id,
p_approve_only_flag => l_approve_only_flag,
p_receipt_method_id => l_receipt_method_id,
p_total_workers => 1 );
arp_debug.debug ( ' Returned from select_valid_invoices..');
insert into ar_receipts_gt(
payment_schedule_id,
customer_trx_id,
cash_receipt_id,
paying_customer_id,
paying_site_use_id,
payment_trxn_extension_id,
due_date,
amount_due_remaining,
customer_bank_account_id,
cust_min_amount,
receipt_number,
payment_channel_code,
payment_instrument,
authorization_id,
gt_id)
select distinct payment_schedule_id,
customer_trx_id,
cash_receipt_id,
paying_customer_id,
paying_site_use_id,
payment_trxn_extension_id,
due_date,
amount_due_remaining,
customer_bank_account_id,
cust_min_amount,
null,
payment_channel_code,
payment_instrument,
null,
null
from ar_autorec_interim a
where a.worker_id = decode(l_current_worker_number,0,a.worker_id,
l_current_worker_number)
and a.batch_id = o_batch_id;
arp_debug.debug ( 'NO of rows inserted into ar_receipts_gt :'|| to_char(SQL%ROWCOUNT));
UPDATE ar_batches
SET batch_applied_status = 'STARTED_APPROVAL'
WHERE batch_id = o_batch_id;
UPDATE ar_batches
SET batch_applied_status = 'COMPLETED_CREATION'
WHERE batch_id = o_batch_id;
arp_debug.debug('Batches updated '||SQL%ROWCOUNT);
arp_debug.debug('CALLING process_selected_receipts()');
process_selected_receipts( p_receipt_method_id => l_receipt_method_id,
p_batch_id => o_batch_id,
p_approval_mode => 'APPROVAL');
update ar_batches
set batch_applied_status = 'COMPLETED_APPROVAL'
where batch_id = o_batch_id;
arp_debug.debug('Batches updated '||SQL%ROWCOUNT);
delete
from ar_autorec_interim
where batch_id = o_batch_id;
SELECT PC.INSTRUMENT_TYPE
INTO l_instrument_type
FROM AR_RECEIPT_METHODS RM,
IBY_FNDCPT_PMT_CHNNLS_B PC
WHERE RECEIPT_METHOD_ID = l_receipt_method_id
AND RM.PAYMENT_CHANNEL_CODE = PC.PAYMENT_CHANNEL_CODE;
arp_debug.debug ( 'Cleaning interim table Rows deleted:'|| SQL%ROWCOUNT);
delete
from ar_autorec_interim
where batch_id = o_batch_id;
pg_last_updated_by := arp_standard.profile.last_update_login ;
pg_last_update_login := arp_standard.profile.last_update_login ;
arp_debug.debug('pg_last_updated_by ' || pg_last_updated_by);
arp_debug.debug('pg_last_update_login ' || pg_last_update_login);
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,
fnd_date.canonical_to_date(p_exchange_date),
to_number(p_exchange_rate),
p_exchange_rate_type,
o_batch_id
);
insert_exceptions( p_batch_id => -333,
p_request_id =>pg_request_id,
p_exception_code => 'NO_BATCH',
p_additional_message => 'error during insert batch' );
SELECT batch_applied_status
INTO l_batch_app_status
FROM ar_batches
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,
p_customer_number_h => p_customer_number_h,
p_customer_name_l => p_customer_name_l,
p_customer_name_h => p_customer_name_h,
p_batch_id => o_batch_id,
p_approve_only_flag => l_approve_only_flag,
p_receipt_method_id => l_receipt_method_id,
p_total_workers => p_total_workers
);
UPDATE ar_batches
SET batch_applied_status = 'STARTED_APPROVAL'
WHERE batch_id = o_batch_id;
UPDATE ar_batches
SET batch_applied_status = 'COMPLETED_CREATION'
WHERE batch_id = o_batch_id;
UPDATE ar_batches
SET batch_applied_status = 'COMPLETED_APPROVAL'
WHERE batch_id = o_batch_id;
SELECT PC.INSTRUMENT_TYPE
INTO l_instrument_type
FROM AR_RECEIPT_METHODS RM,
IBY_FNDCPT_PMT_CHNNLS_B PC
WHERE RECEIPT_METHOD_ID = l_receipt_method_id
AND RM.PAYMENT_CHANNEL_CODE = PC.PAYMENT_CHANNEL_CODE;
delete
from ar_autorec_interim
where batch_id = o_batch_id;
delete
from ar_autorec_interim
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_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_exchange_date IN ar_batches.exchange_date%TYPE DEFAULT NULL,
p_exchange_rate IN ar_batches.exchange_rate%TYPE DEFAULT NULL,
p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE DEFAULT NULL,
p_batch_id OUT NOCOPY NUMBER
) IS
l_batch_rec ar_batches%ROWTYPE;
arp_debug.debug('insert_batch()+');
/* insert the batch record here */
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.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_debug.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_debug.debug('insert_batch()-');
arp_debug.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_total_workers IN NUMBER DEFAULT 1
) IS
trx_invoices INTEGER;
arp_debug.debug('select_valid_invoices start ()+');
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;
l_sel_stmt := 'INSERT /*+ parallel(a) append */ into ar_autorec_interim a ';
l_sel_stmt := 'INSERT into ar_autorec_interim a ';
' 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) */
'||p_batch_id||',
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, greatest(:apply_date,ct.trx_date)) amount_due_remaining,
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,
mod(ct.paying_customer_id,:l_total_workers) + 1 worker_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 ';
arp_debug.debug( 'the select statement ' || l_sel_stmt);
arp_debug.debug('Number of invoices selected : '||l_rows_processed);
update ar_payment_schedules
set selected_for_receipt_batch_id = p_batch_id
where payment_schedule_id in
( select /*+ cardinality(a 10) */
payment_schedule_id
from ar_autorec_interim a
where batch_id = p_batch_id);
arp_debug.debug('Exception : select_valid_invoices() '|| SQLERRM);
arp_debug.debug(' Exception : select_valid_invoices '|| SQLERRM);
arp_debug.debug( 'the select statement ' || 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,
rt.calc_discount_on_lines_flag,
ps.amount_line_items_original,
rt.partial_discount_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,
l_calc_discount_on_lines_flag,
l_amount_line_items_original,
l_partial_discount_flag
from ar_payment_schedules ps,
ra_cust_trx_types ctt,
ra_terms rt
where ps.payment_schedule_id = l_applied_payment_schedule_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id
AND ps.term_id = rt.term_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_paying_customer_id NUMBER;
arp_debug.debug('insert_exceptions()+');
select pay_from_customer
into l_paying_customer_id
from ar_cash_receipts
where cash_receipt_id = p_cash_receipt_id;
arp_debug.debug('l_last_updated_by '|| pg_last_updated_by );
arp_debug.debug('l_last_update_login '|| pg_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,
pg_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,
substr(p_additional_message, 1, 240),
sysdate,
pg_last_updated_by,
sysdate,
pg_created_by,
pg_last_update_login,
pg_program_application_id,
pg_program_id,
sysdate
FROM DUAL;
arp_debug.debug ( 'insert_exceptions()-');
arp_debug.debug ( 'ERROR IN insert_exceptions '||SQLERRM );
END insert_exceptions;
select org_id into l_org_id
from ar_batches_all
where batch_id = p_batch_id;
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 /*+ LEADING(R) INDEX (R, AR_RECEIPTS_GT_N1) USE_NL(R, CR) */ r.customer_trx_id
FROM ar_receipts_gt r,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_fndcpt_tx_operations op,
iby_trxn_summaries_all summ
WHERE r.gt_id = p_gt_id
AND cr.cash_receipt_id = r.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 = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype = 'ORAPMTREQ'
AND summ.status IN(0, 100, 111)
AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
AND(summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND(reqtype = 'ORAPMTREQ')
AND(status IN(0, 100, 111))
AND(trxntypeid = 20)))))
)
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 /*+ LEADING(R) INDEX (R, AR_RECEIPTS_GT_N1) USE_NL(R, CR) */ cr.cash_receipt_id
FROM ar_receipts_gt r,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
iby_fndcpt_tx_operations op,
iby_trxn_summaries_all summ
WHERE r.gt_id = p_gt_id
AND cr.cash_receipt_id = r.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 = op.trxn_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype = 'ORAPMTREQ'
AND summ.status IN(0, 100, 111)
AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
AND(summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND(reqtype = 'ORAPMTREQ')
AND(status IN(0, 100, 111))
AND(trxntypeid = 20)))))
)
and request_id = pg_request_id;
fnd_file.put_line(FND_FILE.LOG,'rows deleted from ar_autorec_exceptions: '||sql%rowcount);
payment_schedule_id before going in for the delete */
/* start unapply */
DECLARE
ul_return_status VARCHAR2(1);
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 cash_receipt_id,
ps.org_id org_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
and r.gt_id = p_gt_id;
arp_debug.debug('delete the bad receipts');
/* Start of delete XLA events code. Doing this is bulk */
Begin
IF PG_DEBUG in ('Y','C') THEN
arp_debug.debug ( 'Start calling xla delete_bulk_events');
arp_debug.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 = pg_request_id));
arp_debug.debug ( 'rows inserted into xla gt table = '|| sql%rowcount);
arp_debug.debug ( 'Calling xla_events_pub_pkg.delete_bulk_events()');
xla_events_pub_pkg.delete_bulk_events(222);
arp_debug.debug ( 'End calling xla delete_bulk_events');
arp_debug.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'),
actual_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,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.payment_schedule_id = ps.payment_schedule_id
AND trx.customer_trx_id = ps.customer_trx_id
and trx.cc_error_flag = 'Y');
arp_debug.debug ( ' rows updated PS = ' || SQL%ROWCOUNT );
delete
from ar_payment_schedules
where cash_receipt_id
in ( select distinct ex.cash_receipt_id
from ar_autorec_exceptions ex,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.cash_receipt_id = ex.cash_receipt_id);
arp_debug.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 ex.cash_receipt_id
from ar_autorec_exceptions ex,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.cash_receipt_id = ex.cash_receipt_id
)
);
arp_debug.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 ex.cash_receipt_id
from ar_autorec_exceptions ex,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.cash_receipt_id = ex.cash_receipt_id
)
);
arp_debug.debug ( ' rows DELETED AR_DIST2 = ' || SQL%ROWCOUNT );
delete
from ar_receivable_applications
where cash_receipt_id in
( select distinct ex.cash_receipt_id
from ar_autorec_exceptions ex,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.cash_receipt_id = ex.cash_receipt_id);
arp_debug.debug ( ' rows DELETED REC_APPS = ' || SQL%ROWCOUNT );
delete
from ar_cash_receipt_history
where cash_receipt_id in
( select distinct ex.cash_receipt_id
from ar_autorec_exceptions ex,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.cash_receipt_id = ex.cash_receipt_id);
arp_debug.debug ( ' rows DELETED CRH = ' || SQL%ROWCOUNT );
delete from ar_cash_receipts
where cash_receipt_id in
( select distinct ex.cash_receipt_id
from ar_autorec_exceptions ex,
ar_receipts_gt r
where r.gt_id = p_gt_id
AND r.cash_receipt_id = ex.cash_receipt_id);
arp_debug.debug ( ' rows DELETED CR = ' || SQL%ROWCOUNT );
insert_exceptions( p_batch_id => nvl(g_batch_id, -333),
p_request_id => pg_request_id,
p_exception_code => 'AUTORECERR',
p_additional_message => 'rec_reset() '|| SQLERRM );
select org_id
into l_org_id
from ar_batches_all
where batch_id = p_batch_id;
SELECT bat.batch_applied_status,
app.program_name
INTO l_batch_app_status,
l_program_name
FROM ar_batches bat,
ar_receipt_methods rm,
ap_payment_programs app
WHERE bat.batch_id = p_batch_id
AND bat.receipt_method_id = rm.receipt_method_id
AND rm.auto_print_program_id = app.program_id;
UPDATE ar_cash_receipts
SET seq_type_last = 'Y'
WHERE cash_receipt_id IN (
SELECT crh.cash_receipt_id
FROM ar_cash_receipt_history crh,
ar_receivable_applications ra,
ra_customer_trx ct,
iby_fndcpt_tx_extensions ext
WHERE crh.batch_id = p_batch_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'CONFIRMED'
AND ra.cash_receipt_id = crh.cash_receipt_id
AND ra.application_type = 'CASH'
AND ra.status = 'APP'
AND ct.customer_trx_id = ra.applied_customer_trx_id
AND ext.trxn_extension_id = ct.payment_trxn_extension_id
AND NVL(ext.seq_type_last, 'N') = 'Y');
SELECT lower(iso_language),iso_territory
INTO l_iso_language,l_iso_territory
FROM FND_LANGUAGES
WHERE language_code = USERENV('LANG');
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;
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_paying_customer_id => p_customer_id,
p_exception_code => 'AUTORECERR',
p_additional_message => p_msg_count||'.'||p_msg_data );
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_paying_customer_id => p_customer_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||'.'||l_msg_data );
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_paying_customer_id => p_customer_id,
p_exception_code => 'AUTORECERR',
p_additional_message => 'create_receipt() '||sqlerrm );
l_msg_data := 'Application failure. You need to nullify the SELECTED FOR RECEIPT BATCH ID on the invoice'||
' when the invoice is fixed. Then apply the invoice manually to the receipt'||
' with receipt id: '||p_cash_receipt_id||', created by automatic receipts for that invoice';
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_payment_schedule_id => p_applied_ps_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data );
l_msg_data := 'Application failure. You need to nullify the SELECTED FOR RECEIPT BATCH ID on the invoice'||
' when the invoice is fixed. Then apply the invoice manually to the receipt'||
' with receipt id: '||p_cash_receipt_id||', created by automatic receipts for that invoice';
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_payment_schedule_id => p_applied_ps_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_count||l_msg_data );
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_payment_schedule_id => p_applied_ps_id,
p_exception_code => 'AUTORECERR',
p_additional_message => 'receipt_application() '||sqlerrm );
select payment_trxn_extension_id
into l_pmt_trxn_id
from ar_cash_receipts
where cash_receipt_id = p_cash_receipt_id;
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_cash_receipt_id => p_cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => l_count||l_msg_data );
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_cash_receipt_id => p_cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => l_count||l_msg_data );
insert_exceptions( p_batch_id => p_batch_id,
p_request_id => p_request_id,
p_cash_receipt_id => p_cash_receipt_id,
p_exception_code => 'AR_CC_AUTH_FAILED',
p_additional_message => 'process_payments() '|| sqlerrm);
PROCEDURE update_ar_receipts_gt(p_creation_rule IN VARCHAR2,
p_update_stmt IN VARCHAR2,
p_cr_id_array IN DBMS_SQL.NUMBER_TABLE,
p_cr_number_array IN DBMS_SQL.VARCHAR2_TABLE,
p_gt_id_array IN DBMS_SQL.NUMBER_TABLE,
p_ps_id_array IN DBMS_SQL.NUMBER_TABLE,
p_paying_customer_id_array IN DBMS_SQL.NUMBER_TABLE,
p_pmt_instrument_array IN DBMS_SQL.VARCHAR2_TABLE,
p_paying_site_use_id_array IN DBMS_SQL.NUMBER_TABLE,
p_due_date_array IN DBMS_SQL.DATE_TABLE,
p_pmt_channel_code_array IN DBMS_SQL.VARCHAR2_TABLE,
p_cust_bank_acct_id_array IN DBMS_SQL.NUMBER_TABLE,
p_trxn_extension_id_array IN DBMS_SQL.NUMBER_TABLE,
p_authorization_id_array IN DBMS_SQL.NUMBER_TABLE )IS
l_update_cursor NUMBER;
arp_debug.debug('update_ar_receipts_gt()+');
arp_debug.debug('p_update_stmt '||p_update_stmt);
l_update_cursor := dbms_sql.open_cursor;
dbms_sql.parse (l_update_cursor, p_update_stmt,dbms_sql.v7);
dbms_sql.bind_array (l_update_cursor,':l_receipt_num_array',p_cr_number_array);
dbms_sql.bind_array (l_update_cursor,':l_receipt_id_array',p_cr_id_array);
dbms_sql.bind_array (l_update_cursor,':l_gt_id_array',p_gt_id_array);
dbms_sql.bind_array (l_update_cursor,':l_ps_id_array',p_ps_id_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_site_use_id_array',p_paying_site_use_id_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_site_use_id_array',p_paying_site_use_id_array);
dbms_sql.bind_array (l_update_cursor,':l_due_date_array',p_due_date_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
dbms_sql.bind_array (l_update_cursor,':l_due_date_array',p_due_date_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_trxn_extension_id_array',p_trxn_extension_id_array);
dbms_sql.bind_array (l_update_cursor,':l_pmt_channel_code_array',p_pmt_channel_code_array);
dbms_sql.bind_array (l_update_cursor,':l_cust_bank_acct_id_array',p_cust_bank_acct_id_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_trxn_extension_id_array',p_trxn_extension_id_array);
dbms_sql.bind_array (l_update_cursor,':l_pmt_instrument_array',p_pmt_instrument_array);
dbms_sql.bind_array (l_update_cursor,':l_paying_customer_id_array',p_paying_customer_id_array);
dbms_sql.bind_array (l_update_cursor,':l_trxn_extension_id_array',p_trxn_extension_id_array);
dbms_sql.bind_array (l_update_cursor,':l_authorization_id_array',p_authorization_id_array);
l_dummy := dbms_sql.execute(l_update_cursor);
dbms_sql.close_cursor(l_update_cursor);
arp_debug.debug('Rows updated :'||SQL%ROWCOUNT);
arp_debug.debug('update_ar_receipts_gt()-');
if dbms_sql.is_open(l_update_cursor) then
dbms_sql.close_cursor(l_update_cursor);
arp_debug.debug('update_ar_receipts_gt : error code() '|| to_char(SQLCODE));
insert_exceptions( p_batch_id => NVL(g_batch_id, -333),
p_request_id => arp_standard.profile.request_id,
p_exception_code => 'AUTORECERR',
p_additional_message => 'update_ar_receipts_gt() '|| SQLERRM );
END update_ar_receipts_gt;
p_update_stmt OUT NOCOPY VARCHAR2,
p_inv_rct_mp_qry OUT NOCOPY VARCHAR2 ) IS
l_update_where_clause VARCHAR2(2000);
p_update_stmt := 'UPDATE /*+INDEX(ar_receipts_gt AR_RECEIPTS_GT_N2)*/ '||
' ar_receipts_gt '||
' SET receipt_number = :l_receipt_num_array, '||
' cash_receipt_id = :l_receipt_id_array, '||
' gt_id = :l_gt_id_array ';
insert into ar_receipts_gt
( payment_schedule_id,
customer_trx_id,
cash_receipt_id,
amount_due_remaining,
gt_id
) ';
select paying_customer_id,
null paying_site_use_id,
null due_date,
null payment_instrument,
null payment_channel_code,
null payment_trxn_extension_id,
null authorization_id,
null amount,
payment_schedule_id,
null customer_bank_account_id,
null instr_assignment_id,
null party_id,
null trx_number,
null cust_min_amount,
cash_receipt_id
from ar_receipts_gt gt ';
select /*+ leading(crh) */
ps.payment_schedule_id,
ct.customer_trx_id,
cr.cash_receipt_id,
ps.amount_due_remaining,
-1 gt_id
from ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_payment_schedules ps,
ra_customer_trx ct,
iby_fndcpt_tx_xe_copies cp
where crh.batch_id = :p_batch_id
and cr.status = ''UNAPP''
and ps.customer_trx_id = ct.customer_trx_id
and ps.selected_for_receipt_batch_id = crh.batch_id
and ct.bill_to_customer_id = cr.pay_from_customer
and crh.cash_receipt_id = cr.cash_receipt_id
and cp.source_trxn_extension_id = ct.payment_trxn_extension_id
and cp.copy_trxn_extension_id = cr.payment_trxn_extension_id';
'select paying_customer_id,
gt.paying_site_use_id,
null due_date,
null payment_instrument,
gt.payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
sum(gt.amount_due_remaining) amount,
gt.payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
trx_number||''-''||to_char(terms_sequence_number) trx_number,
MAX(gt.cust_min_amount) cust_min_amount,
null cash_receipt_id
from ar_receipts_gt gt,
iby_fndcpt_tx_extensions ext,
hz_cust_accounts hca,
ar_payment_schedules ps
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id
and ps.payment_schedule_id = gt.payment_schedule_id
group by gt.customer_trx_id,
gt.payment_schedule_id,
gt.paying_customer_id,
gt.payment_trxn_extension_id,
gt.payment_channel_code,
ext.instr_assignment_id,
hca.party_id,
trx_number||''-''||to_char(terms_sequence_number),
gt.paying_site_use_id';
l_update_where_clause := ' WHERE payment_schedule_id = :l_ps_id_array ';
select /*+ leading(crh) */
ps.payment_schedule_id,
ct.customer_trx_id,
cr.cash_receipt_id,
ps.amount_due_remaining,
-1 gt_id
from ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_payment_schedules ps,
ra_customer_trx ct
where crh.batch_id = :p_batch_id
and cr.status = ''UNAPP''
and ps.customer_trx_id = ct.customer_trx_id
and ps.selected_for_receipt_batch_id = crh.batch_id
and ct.bill_to_customer_id = cr.pay_from_customer
and crh.cash_receipt_id = cr.cash_receipt_id ';
'select gt.paying_customer_id,
null paying_site_use_id,
null due_date,
gt.payment_instrument,
null payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
gt.amount,
null payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
payment_instrument,
MIN(payment_trxn_extension_id) payment_trxn_extension_id,
sum(amount_due_remaining) amount,
MAX(cust_min_amount) cust_min_amount
from ar_receipts_gt
group by paying_customer_id,
payment_instrument ) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND payment_instrument =:l_pmt_instrument_array ' ;
select /*+ leading(crh) */
ps.payment_schedule_id,
ct.customer_trx_id,
cr.cash_receipt_id,
ps.amount_due_remaining,
-1 gt_id
from ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_payment_schedules ps,
ra_customer_trx ct
where crh.batch_id = :p_batch_id
and cr.status = ''UNAPP''
and ps.customer_trx_id = ct.customer_trx_id
and ps.selected_for_receipt_batch_id = crh.batch_id
and ct.bill_to_customer_id = cr.pay_from_customer
and crh.cash_receipt_id = cr.cash_receipt_id
AND cr.customer_site_use_id = ct.bill_to_site_use_id ';
' select gt.paying_customer_id,
gt.paying_site_use_id,
null due_date,
gt.payment_instrument,
null payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
gt.amount,
null payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
paying_site_use_id,
payment_instrument,
MIN(payment_trxn_extension_id) payment_trxn_extension_id,
sum(amount_due_remaining) amount,
MAX(cust_min_amount) cust_min_amount
from AR_RECEIPTS_GT
group by paying_customer_id,
paying_site_use_id,
payment_instrument ) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND paying_site_use_id =:l_paying_site_use_id_array '||
' AND payment_instrument =:l_pmt_instrument_array ' ;
SELECT payment_schedule_id,
customer_trx_id,
cash_receipt_id,
amount_due_remaining,
-1 gt_id
FROM
(
select /*+ leading(ps) */
ps.payment_schedule_id,
ps.customer_trx_id,
cr.cash_receipt_id,
ps.amount_due_remaining,
RANK( ) OVER (PARTITION BY cr.cash_receipt_id
ORDER BY ps.customer_trx_id, cr.amount) rct_rank,
RANK( ) OVER (PARTITION BY ps.customer_trx_id
ORDER BY cr.cash_receipt_id, cr.amount) inv_rank
from ar_cash_receipt_history crh,
ar_cash_receipts cr,
( SELECT ps.payment_schedule_id,
ps.selected_for_receipt_batch_id,
ct.bill_to_customer_id,
ps.amount_due_remaining,
SUM( ps.amount_due_remaining )
OVER( PARTITION BY ct.bill_to_site_use_id,ps.due_date) group_amount,
ct.bill_to_site_use_id,
ps.customer_trx_id
FROM ar_payment_schedules ps,
ra_customer_trx ct
WHERE ps.customer_trx_id = ct.customer_trx_id
AND ps.selected_for_receipt_batch_id = :batch_id
) ps
where crh.batch_id = ps.selected_for_receipt_batch_id
and cr.status = ''UNAPP''
and ps.bill_to_customer_id = cr.pay_from_customer
AND ps.group_amount = cr.amount
and crh.cash_receipt_id = cr.cash_receipt_id
AND cr.customer_site_use_id = ps.bill_to_site_use_id
)
WHERE rct_rank = inv_rank ';
' select gt.paying_customer_id,
gt.paying_site_use_id,
gt.due_date,
gt.payment_instrument,
null payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
gt.amount,
null payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
paying_site_use_id,
payment_instrument,
due_date,
MIN(payment_trxn_extension_id) payment_trxn_extension_id,
sum(amount_due_remaining) amount,
MAX(cust_min_amount) cust_min_amount
from AR_RECEIPTS_GT
group by paying_customer_id,
due_date,
paying_site_use_id,
payment_instrument ) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND paying_site_use_id =:l_paying_site_use_id_array '||
' AND due_date =:l_due_date_array'||
' AND payment_instrument =:l_pmt_instrument_array ' ;
SELECT payment_schedule_id,
customer_trx_id,
cash_receipt_id,
amount_due_remaining,
-1 gt_id
FROM
(
select /*+ leading(ps) */
ps.payment_schedule_id,
ps.customer_trx_id,
cr.cash_receipt_id,
ps.amount_due_remaining,
RANK( ) OVER (PARTITION BY cr.cash_receipt_id
ORDER BY ps.customer_trx_id, cr.amount) rct_rank,
RANK( ) OVER (PARTITION BY ps.customer_trx_id
ORDER BY cr.cash_receipt_id, cr.amount) inv_rank
from ar_cash_receipt_history crh,
ar_cash_receipts cr,
( SELECT ps.payment_schedule_id,
ps.selected_for_receipt_batch_id,
ct.bill_to_customer_id,
ps.amount_due_remaining,
SUM( ps.amount_due_remaining )
OVER( PARTITION BY ct.bill_to_customer_id,ps.due_date) group_amount,
ps.customer_trx_id
FROM ar_payment_schedules ps,
ra_customer_trx ct
WHERE ps.customer_trx_id = ct.customer_trx_id
AND ps.selected_for_receipt_batch_id = :batch_id
) ps
where crh.batch_id = ps.selected_for_receipt_batch_id
and cr.status = ''UNAPP''
and ps.bill_to_customer_id = cr.pay_from_customer
AND ps.group_amount = cr.amount
and crh.cash_receipt_id = cr.cash_receipt_id
)
WHERE rct_rank = inv_rank ';
' select gt.paying_customer_id,
null paying_site_use_id,
gt.due_date,
gt.payment_instrument,
null payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
gt.amount,
null payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
payment_instrument,
due_date,
MIN(payment_trxn_extension_id) payment_trxn_extension_id,
sum(amount_due_remaining) amount,
MAX(cust_min_amount) cust_min_amount
from AR_RECEIPTS_GT
group by paying_customer_id,
due_date,
payment_instrument ) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND due_date =:l_due_date_array'||
' AND payment_instrument =:l_pmt_instrument_array ' ;
' select gt.paying_customer_id,
null paying_site_use_id,
null due_date,
null payment_instrument,
gt.payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
gt.amount,
null payment_schedule_id,
gt.customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
payment_trxn_extension_id,
sum(amount_due_remaining) amount,
customer_bank_account_id,
payment_channel_code,
MAX(cust_min_amount) cust_min_amount
from AR_RECEIPTS_GT
group by paying_customer_id,
payment_trxn_extension_id,
customer_bank_account_id,
payment_channel_code) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND payment_trxn_extension_id =:l_trxn_extension_id_array'||
' AND payment_channel_code =:l_pmt_channel_code_array'||
' AND customer_bank_account_id =:l_cust_bank_acct_id_array' ;
' select gt.paying_customer_id,
null paying_site_use_id,
null due_date,
gt.payment_instrument,
null payment_channel_code,
gt.payment_trxn_extension_id,
null authorization_id,
gt.amount,
null payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
payment_trxn_extension_id,
sum(amount_due_remaining) amount,
payment_instrument,
MAX(cust_min_amount) cust_min_amount
from AR_RECEIPTS_GT
group by paying_customer_id,
payment_trxn_extension_id,
payment_instrument) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND payment_trxn_extension_id =:l_trxn_extension_id_array'||
' AND payment_instrument =:l_pmt_instrument_array ' ;
' select gt.paying_customer_id,
null paying_site_use_id,
null due_date,
null payment_instrument,
null payment_channel_code,
gt.payment_trxn_extension_id,
gt.authorization_id,
gt.amount,
null payment_schedule_id,
null customer_bank_account_id,
ext.instr_assignment_id,
hca.party_id,
null trx_number,
gt.cust_min_amount,
null cash_receipt_id
from hz_cust_accounts hca,
iby_fndcpt_tx_extensions ext,
( select paying_customer_id,
payment_trxn_extension_id,
sum(amount_due_remaining) amount,
authorization_id,
MAX(cust_min_amount) cust_min_amount
from AR_RECEIPTS_GT
group by paying_customer_id,
payment_trxn_extension_id,
authorization_id) gt
where ext.trxn_extension_id = gt.payment_trxn_extension_id
and hca.cust_account_id = gt.paying_customer_id ';
l_update_where_clause := ' WHERE paying_customer_id =:l_paying_customer_id_array '||
' AND payment_trxn_extension_id =:l_trxn_extension_id_array'||
' AND authorization_id =:l_authorization_id_array' ;
p_update_stmt := p_update_stmt || l_update_where_clause;
arp_debug.debug('p_update_stmt '||p_update_stmt);
PROCEDURE process_selected_receipts( p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE,
p_batch_id IN ar_batches.batch_id%TYPE,
p_approval_mode IN VARCHAR2 DEFAULT 'APPROVE' ) IS
l_set_of_books_id ar_batches.set_of_books_id%TYPE;
l_update_stmt VARCHAR2(2000);
SELECT ard.source_id, ard.source_id_secondary, trx.upgrade_method,
ra.applied_customer_trx_id, ra.payment_schedule_id, ra.applied_payment_schedule_id
FROM ar_receivable_applications ra,
ar_distributions ard,
ra_customer_trx trx,
ar_receipts_gt rgt
WHERE ra.cash_receipt_id = p_cr_id
AND ra.applied_customer_trx_id IS NOT NULL
AND ard.source_id_secondary = ra.receivable_application_id
AND ard.source_table_secondary = 'RA'
AND ard.source_table = 'RA'
AND trx.customer_trx_id = ra.applied_customer_trx_id
AND rgt.cash_receipt_id = p_cr_id
AND rgt.gt_id = p_gt_id;
SELECT /*+INDEX(rgt AR_RECEIPTS_GT_N1) INDEX(inv_ps AR_PAYMENT_SCHEDULES_U1) INDEX(ps AR_PAYMENT_SCHEDULES_U2) */
cr.pay_from_customer customer_id,
crh.gl_date cr_gl_date,
cr.amount cr_amount,
cr.customer_site_use_id cust_site_use_id,
cr.receipt_date ,
cr.currency_code cr_currency_code,
cr.exchange_rate cr_exchange_rate,
ps.payment_schedule_id cr_payment_schedule_id,
cr.remit_bank_acct_use_id remittance_bank_account_id,
cr.receipt_method_id,
cr.cash_receipt_id,
inv_ps.amount_due_remaining inv_bal_amount,
inv_ps.amount_due_original inv_orig_amount,
ctt.allow_overapplication_flag allow_over_app,
rma.unapplied_ccid,
ed.code_combination_id ed_disc_ccid,
uned.code_combination_id uned_disc_ccid,
crh.batch_id,
rgt.customer_trx_id,
(select 'Y' rev_rec_flag
from ra_customer_trx_lines ctl
where ctl.customer_trx_id = rgt.customer_trx_id
and ctl.autorule_complete_flag||'' = 'N'
and rownum = 1 ) rev_rec_flag,
(select 'Y' def_tax_flag
from ra_cust_trx_line_gl_dist gld
where gld.account_class = 'TAX'
and gld.customer_trx_id = rgt.customer_trx_id
and gld.collected_tax_ccid IS NOT NULL
and rownum = 1 ) def_tax_flag,
ot.cust_trx_type_id cust_trx_type_id ,
ot.trx_due_date trx_due_date,
ot.invoice_currency_code trx_currency_code,
ot.trx_exchange_rate trx_exchange_rate,
ot.trx_date trx_date ,
ot.trx_gl_date trx_gl_date,
ot.calc_discount_on_lines_flag calc_discount_on_lines_flag,
ot.partial_discount_flag partial_discount_flag,
ot.allow_overapplication_flag allow_overappln_flag,
ot.natural_application_only_flag natural_appln_only_flag,
ot.creation_sign creation_sign,
ot.payment_schedule_id applied_payment_schedule_id,
greatest(crh.gl_date,ot.trx_gl_date,
decode(ar_receipt_lib_pvt.pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate,
'INV_REC_DT', ot.trx_gl_date,
ot.trx_gl_date)) ot_gl_date,
ot.term_id term_id,
ot.amount_due_original amount_due_original,
ot.amount_line_items_original amount_line_items_original,
arp_util.CurrRound(ot.balance_due_curr_unformatted,
ot.invoice_currency_code) amount_due_remaining,
ot.discount_taken_earned discount_taken_earned,
ot.discount_taken_unearned discount_taken_unearned,
ot.amount_line_items_original line_items_original,
ot.amount_line_items_remaining line_items_remaining,
ot.tax_original tax_original,
ot.tax_remaining tax_remaining,
ot.freight_original freight_original,
ot.freight_remaining freight_remaining,
Null rec_charges_charged,
ot.receivables_charges_remaining rec_charges_remaining,
ot.location location,
rgt.amount_due_remaining amount_apply
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_payment_schedules ps,
ra_cust_trx_types ctt,
ar_payment_schedules inv_ps,
ar_receipt_method_accounts rma,
ar_receivables_trx ed,
ar_receivables_trx uned,
ar_open_trx_v ot,
ar_receipts_gt rgt
WHERE rgt.cash_receipt_id IS NOT NULL
AND rgt.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND inv_ps.payment_schedule_id = rgt.payment_schedule_id
AND inv_ps.cust_trx_type_id = ctt.cust_trx_type_id
AND crh.current_record_flag = 'Y'
AND rma.receipt_method_id = cr.receipt_method_id
AND rma.remit_bank_acct_use_id = cr.remit_bank_acct_use_id
AND ot.payment_schedule_id = rgt.payment_schedule_id
AND rgt.gt_id = p_gt_id
AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
AND rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+)
ORDER BY rgt.cash_receipt_id,
rgt.amount_due_remaining;
SELECT payment_schedule_id,
paying_customer_id,
customer_trx_id,
cash_receipt_id
FROM ar_receipts_gt
WHERE gt_id = l_gt_id
AND receipt_number = l_rec_num;
arp_debug.debug('process_selected_receipts()+');
SELECT DECODE(rc.confirm_flag,'Y','AUTORECAPI','AUTORECAPI2'),
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 = p_receipt_method_id
AND rm.receipt_class_id = rc.receipt_class_id;
SELECT b.set_of_books_id,
r.name,
b.batch_date,
b.gl_date, /*Bug 13146325*/
b.currency_code,
DECODE(exchange_rate_type,'User',exchange_rate,NULL),
exchange_date,
exchange_rate_type
INTO l_set_of_books_id,
l_name,
l_batch_date,
l_gl_date, /*Bug 13146325*/
l_currency_code,
l_exchange_rate,
l_exchange_date,
l_exchange_rate_type
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;
l_update_stmt,
l_inv_rct_mp_qry );
arp_debug.debug('Number of rows inserted '||SQL%ROWCOUNT);
l_err_rcpt_num_array.DELETE;
/* Set the minimum receipt amount got from main select query */
l_cust_site_min_rec_amt := l_rcpt_info_tab(i).cust_min_amount;
SELECT site_use.site_use_id
INTO l_rcpt_info_tab(i).paying_site_use_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = l_rcpt_info_tab(i).paying_customer_id
AND acct_site.status = 'A'
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND site_use.site_use_code = nvl('BILL_TO', site_use.site_use_code)
AND site_use.status = 'A'
AND site_use.primary_flag = 'Y';
Select cpa.auto_rec_min_receipt_amount
into l_cust_site_min_rec_amt
From hz_customer_profiles cp,
hz_cust_profile_amts cpa
WHERE cp.cust_account_profile_id = cpa.cust_account_profile_id
AND cpa.currency_code = l_currency_code
AND cp.site_use_id = l_rcpt_info_tab(i).paying_site_use_id;
Select min_receipt_amount
into l_rec_method_min_rec_amt
from ar_receipt_method_accounts
where receipt_method_id = p_receipt_method_id
and remit_bank_acct_use_id = l_remittance_bank_account_id;
select nvl(terms_sequence_number,1)
into l_installment
from ar_payment_schedules
where payment_schedule_id = l_rcpt_info_tab(i).payment_schedule_id;
UPDATE ar_cash_receipt_history
SET batch_id = p_batch_id,
created_by = pg_created_by,
last_update_date = sysdate,
last_updated_by = pg_created_by,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id = pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE cash_receipt_id = l_cash_receipt_id;
l_unbal_rcpt_tab.delete;
l_err_text := 'Receipt with cash_receipt_id '||l_unbal_rcpt_tab(i).cash_receipt_id||' is deleted due to unbalanced journal entries.';
arp_standard.debug('Delete unbalanced receipt with receipt_id : '||l_receipt_id_array(j));
delete from ar_payment_schedules
where cash_receipt_id = l_receipt_id_array(j);
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 = l_receipt_id_array(j));
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 = l_receipt_id_array(j));
arp_standard.debug ( ' rows DELETED AR_DIST2 = ' || SQL%ROWCOUNT );
delete from ar_receivable_applications
where cash_receipt_id = l_receipt_id_array(j);
arp_standard.debug ( ' rows DELETED REC_APPS = ' || SQL%ROWCOUNT );
delete from ar_cash_receipt_history
where cash_receipt_id = l_receipt_id_array(j);
arp_standard.debug ( ' rows DELETED CRH = ' || SQL%ROWCOUNT );
delete from ar_cash_receipts
where cash_receipt_id = l_receipt_id_array(j);
arp_standard.debug ( ' rows DELETED CR = ' || SQL%ROWCOUNT );
update_ar_receipts_gt ( p_creation_rule => l_rec_creation_rule_code,
p_update_stmt => l_update_stmt,
p_cr_id_array => l_receipt_id_array,
p_cr_number_array => l_receipt_num_array,
p_gt_id_array => l_gt_id_array,
p_ps_id_array => l_ps_id_array,
p_paying_customer_id_array => l_paying_customer_id_array,
p_pmt_instrument_array => l_pmt_instrument_array,
p_paying_site_use_id_array => l_paying_site_use_id_array,
p_due_date_array => l_due_date_array,
p_pmt_channel_code_array => l_pmt_channel_code_array,
p_cust_bank_acct_id_array => l_cust_bank_acct_id_array,
p_trxn_extension_id_array => l_pmt_trxn_extn_id_array,
p_authorization_id_array => l_authorization_id_array );
--loop through all the receipts which failed the min amount condition and insert
--them into exceptions table
FOR k IN 1..l_err_rcpt_index LOOP
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.debug( 'loop through errored receipts '||l_err_rcpt_index);
insert_exceptions(
p_batch_id => p_batch_id,
p_request_id => pg_request_id,
p_payment_schedule_id => rec.payment_schedule_id,
p_paying_customer_id => rec.paying_customer_id,
p_exception_code => l_err_code_array(k),
p_additional_message => l_cc_err_text_array(k) );
UPDATE ar_payment_schedules
SET selected_for_receipt_batch_id = NULL
WHERE payment_schedule_id = rec.payment_schedule_id;
/* update the error flag in ra_customer_trx */
UPDATE ra_customer_trx
SET cc_error_flag = 'Y',
cc_error_code = l_cc_err_code_array(k),
cc_error_text = l_cc_err_text_array(k),
last_updated_by = pg_last_updated_by,
last_update_date = sysdate,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id= pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE customer_trx_id = rec.customer_trx_id;
UPDATE ar_receipts_gt
SET gt_id = l_gt_id
WHERE cash_receipt_id = l_rcpt_info_tab(i).cash_receipt_id
AND gt_id = -1 ;
UPDATE ra_customer_trx
SET cc_error_flag = 'Y',
cc_error_code = l_cc_error_code,
cc_error_text = l_cc_error_text,
last_updated_by = pg_last_updated_by,
last_update_date = sysdate,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id= pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE customer_trx_id IN
( SELECT customer_trx_id
FROM ar_payment_schedules
WHERE payment_schedule_id = l_rcpt_info_tab(i).payment_schedule_id
);
excecute ps.selected_for_receipt_batch_id is null
---------------------------------------------------*/
arp_view_constants.set_ps_selected_in_batch('Y');
l_unbal_rcpt_tab.delete;
select min(gt.cash_receipt_id),
max(gt.cash_receipt_id)
into l_from_cr_id,
l_to_cr_id
from AR_RECEIPTS_GT gt
where gt.gt_id = l_gt_id;
arp_standard.debug('Update Invoice PS Before deleting Application');
UPDATE AR_PAYMENT_SCHEDULES PS SET (
PS.AMOUNT_DUE_REMAINING,
PS.AMOUNT_APPLIED,
PS.AMOUNT_LINE_ITEMS_REMAINING,
PS.RECEIVABLES_CHARGES_REMAINING,
PS.FREIGHT_REMAINING,
PS.TAX_REMAINING,
PS.ACCTD_AMOUNT_DUE_REMAINING,
PS.STATUS,
PS.GL_DATE_CLOSED,
PS.ACTUAL_DATE_CLOSED,
PS.DISCOUNT_REMAINING,
PS.DISCOUNT_TAKEN_EARNED ) =
( SELECT
NVL(PS.AMOUNT_DUE_REMAINING, 0)
+ NVL(RA1.AMOUNT_APPLIED, 0)
+ NVL(RA1.EARNED_DISCOUNT_TAKEN, 0),
NVL(PS.AMOUNT_APPLIED, 0)
- NVL(RA1.AMOUNT_APPLIED, 0),
NVL(PS.AMOUNT_LINE_ITEMS_REMAINING, 0)
+ NVL(RA1.LINE_APPLIED, 0)
+ NVL(LINE_EDISCOUNTED, 0),
NVL(PS.RECEIVABLES_CHARGES_REMAINING, 0)
+ NVL(RA1.RECEIVABLES_CHARGES_APPLIED, 0)
+ NVL(CHARGES_EDISCOUNTED, 0),
NVL(PS.FREIGHT_REMAINING, 0)
+ NVL(RA1.FREIGHT_APPLIED, 0)
+ NVL(FREIGHT_EDISCOUNTED, 0),
NVL(PS.TAX_REMAINING, 0)
+ NVL(RA1.TAX_APPLIED, 0)
+ NVL(TAX_EDISCOUNTED, 0),
NVL(PS.ACCTD_AMOUNT_DUE_REMAINING, 0)
+ NVL(RA1.ACCTD_AMOUNT_APPLIED_TO, 0)
+ NVL(RA1.ACCTD_EARNED_DISCOUNT_TAKEN, 0),
DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0)
+ NVL(RA1.AMOUNT_APPLIED, 0)
+ NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)), 0, 'CL', 'OP'),
DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0)
+ NVL(RA1.AMOUNT_APPLIED, 0)
+ NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)),
0, PS.GL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY')),
DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0)
+ NVL(RA1.AMOUNT_APPLIED, 0)
+ NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)),
0, PS.ACTUAL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY')),
NVL(PS.DISCOUNT_REMAINING, 0)
+ NVL(RA1.EARNED_DISCOUNT_TAKEN, 0),
NVL(PS.DISCOUNT_TAKEN_EARNED, 0)
- NVL(RA1.EARNED_DISCOUNT_TAKEN, 0)
FROM AR_RECEIVABLE_APPLICATIONS RA1
WHERE RA1.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND RA1.CASH_RECEIPT_ID = l_unbal_rcpt_tab(i).cash_receipt_id
AND RA1.RECEIVABLE_APPLICATION_ID = unbal_rec_appln.source_id_secondary )
WHERE PS.PAYMENT_SCHEDULE_ID = unbal_rec_appln.applied_payment_schedule_id;
arp_standard.debug('Update Receipt PS Before deleting Application');
UPDATE AR_PAYMENT_SCHEDULES PS SET (
PS.AMOUNT_DUE_REMAINING,
PS.AMOUNT_APPLIED,
PS.ACCTD_AMOUNT_DUE_REMAINING,
PS.STATUS,
PS.GL_DATE_CLOSED,
PS.ACTUAL_DATE_CLOSED ) =
( SELECT
NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0),
NVL(PS.AMOUNT_APPLIED, 0) + NVL(RA1.AMOUNT_APPLIED, 0),
NVL(PS.ACCTD_AMOUNT_DUE_REMAINING, 0) - NVL(RA1.ACCTD_AMOUNT_APPLIED_TO, 0),
DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0)), 0, 'CL', 'OP'),
DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0)),
0, PS.GL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY')),
DECODE((NVL(PS.AMOUNT_DUE_REMAINING, 0) - NVL(RA1.AMOUNT_APPLIED, 0)),
0, PS.ACTUAL_DATE_CLOSED, to_date('12/31/4712', 'MM/DD/YYYY'))
FROM AR_RECEIVABLE_APPLICATIONS RA1
WHERE RA1.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND RA1.RECEIVABLE_APPLICATION_ID = unbal_rec_appln.source_id_secondary )
WHERE PS.PAYMENT_SCHEDULE_ID = unbal_rec_appln.payment_schedule_id;
arp_standard.debug('Delete Application Distribution Entires');
DELETE FROM AR_DISTRIBUTIONS WHERE SOURCE_ID IN
(unbal_rec_appln.source_id,
unbal_rec_appln.source_id_secondary)
AND SOURCE_TABLE = 'RA';
arp_standard.debug('Delete Application Entries');
DELETE FROM AR_RECEIVABLE_APPLICATIONS WHERE RECEIVABLE_APPLICATION_ID IN
(unbal_rec_appln.source_id,
unbal_rec_appln.source_id_secondary);
UPDATE RA_CUSTOMER_TRX_LINES TL SET (
AMOUNT_DUE_REMAINING,
ACCTD_AMOUNT_DUE_REMAINING,
CHRG_AMOUNT_REMAINING,
CHRG_ACCTD_AMOUNT_REMAINING,
FRT_ADJ_REMAINING,
FRT_ADJ_ACCTD_REMAINING,
FRT_ED_AMOUNT,
FRT_ED_ACCTD_AMOUNT,
FRT_UNED_AMOUNT,
FRT_UNED_ACCTD_AMOUNT) = (
SELECT
TL.AMOUNT_DUE_ORIGINAL + REM_TYPE_LINE,
TL.ACCTD_AMOUNT_DUE_ORIGINAL + ACCTD_REM_TYPE_LINE,
CHRG_ON_REV_LINE,
ACCTD_CHRG_ON_REV_LINE,
FRT_ON_REV_LINE,
ACCTD_FRT_ON_REV_LINE,
ED_FRT_REV_LINE,
ACCTD_ED_FRT_REV_LINE,
UNED_FRT_REV_LINE,
ACCTD_UNED_FRT_REV_LINE
FROM (SELECT
SUM((decode(a.activity_bucket, 'ADJ_CHRG',
amt, 'APP_CHRG', decode(a.line_type,
'LINE', amt, 0) * -1, 0))) chrg_on_rev_line,
SUM((decode(a.activity_bucket, 'ADJ_CHRG',
acctd_amt, 'APP_CHRG', decode(a.line_type,
'LINE', acctd_amt, 0) * -1, 0)))
acctd_chrg_on_rev_line,
SUM((decode(a.activity_bucket, 'ADJ_FRT',
amt, 'APP_FRT', decode(a.line_type, 'LINE',
amt, 0) * -1, 0))) frt_on_rev_line,
SUM((decode(a.activity_bucket, 'ADJ_FRT', amt,
'APP_FRT', decode(a.line_type, 'LINE',
acctd_amt, 0) * -1, 0))) acctd_frt_on_rev_line,
SUM((decode(a.activity_bucket,
'ED_FRT', amt, 0))) ed_frt_rev_line,
SUM((decode(a.activity_bucket, 'ED_FRT',
acctd_amt, 0))) acctd_ed_frt_rev_line,
SUM((decode(a.activity_bucket, 'UNED_FRT',
amt, 0))) uned_frt_rev_line,
SUM((decode(a.activity_bucket, 'UNED_FRT',
acctd_amt, 0))) acctd_uned_frt_rev_line,
SUM((decode(a.activity_bucket, 'ADJ_LINE', amt,
'APP_LINE', (amt * -1), 'ED_LINE', amt,
'UNED_LINE', amt,'ADJ_TAX', amt,
'APP_TAX', (amt * -1), 'ED_TAX', amt,
'UNED_TAX', amt, 'APP_FRT',
(decode(a.line_type, 'FREIGHT', amt, 0) * -1),
'APP_CHRG', (decode(a.line_type, 'CHARGES',
amt, 0) * -1), 0))) rem_type_line,
SUM((decode(a.activity_bucket, 'ADJ_LINE', acctd_amt,
'APP_LINE', (acctd_amt * -1), 'ED_LINE',
acctd_amt, 'UNED_LINE', acctd_amt, 'ADJ_TAX',
acctd_amt, 'APP_TAX', (acctd_amt * -1), 'ED_TAX',
acctd_amt, 'UNED_TAX', acctd_amt,'APP_FRT',
(decode(a.line_type, 'FREIGHT',
acctd_amt, 0) * -1), 'APP_CHRG',
(decode(a.line_type, 'CHARGES',
acctd_amt, 0) * -1), 0))) acctd_rem_type_line,
a.customer_trx_line_id customer_trx_line_id
FROM (
SELECT
SUM(nvl(ard.amount_cr, 0)
- nvl(ard.amount_dr, 0)) amt,
SUM(nvl(ard.acctd_amount_cr, 0)
- nvl(ard.acctd_amount_dr, 0)) acctd_amt,
ctl.customer_trx_line_id,
ard.ref_account_class,
ard.activity_bucket,
ctl.line_type
FROM ar_distributions ard,
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id =
unbal_rec_appln.applied_customer_trx_id
AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id (+)
GROUP BY ctl.customer_trx_line_id,
ard.ref_account_class,
ard.activity_bucket,
ctl.line_type) a
GROUP BY a.customer_trx_line_id) bal
WHERE bal.customer_trx_line_id = TL.customer_trx_line_id)
WHERE TL.CUSTOMER_TRX_ID = unbal_rec_appln.applied_customer_trx_id;
l_msg_data := 'Application failure. You need to nullify the SELECTED FOR RECEIPT BATCH ID on the invoice.'||
' When the invoice is fixed, then apply the invoice manually to the receipt with receipt id: '||
l_unbal_rcpt_tab(i).cash_receipt_id||', created by automatic receipts for that invoice';
arp_standard.debug('Inserting Exception');
insert_exceptions(
p_batch_id => p_batch_id,
p_request_id => pg_request_id,
p_payment_schedule_id => unbal_rec_appln.applied_payment_schedule_id,
p_exception_code => 'AUTORECERR',
p_additional_message => l_msg_data );
arp_view_constants.set_ps_selected_in_batch( null);
/*Update the receipt_number with the value of trx_number,this update is needed
sine we considered concatenated string of trx_number and term_sequence as
receipt_number during receipt creation.*/
IF l_rec_creation_rule_code = 'PER_INVOICE' AND
l_rec_inher_inv_num_flag = 'Y' THEN
update ar_cash_receipts cr
SET receipt_number =
NVL(SUBSTR(RECEIPT_NUMBER, 1, INSTR(RECEIPT_NUMBER,'-', -1) -1), RECEIPT_NUMBER)
WHERE cash_receipt_id in
( select cr.cash_receipt_id
from ar_cash_receipts cr,
ar_receipts_gt arg
where arg.gt_id = l_gt_id
AND cr.cash_receipt_id = arg.cash_receipt_id
);
arp_debug.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
update ar_payment_schedules
SET TRX_NUMBER =
NVL(SUBSTR(TRX_NUMBER, 1, INSTR(TRX_NUMBER,'-', -1) -1), TRX_NUMBER)
WHERE cash_receipt_id in
( select ps.cash_receipt_id
from ar_payment_schedules ps,
ar_receipts_gt arg
where arg.gt_id = l_gt_id
AND ps.cash_receipt_id = arg.cash_receipt_id
);
arp_debug.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
l_receipt_num_array.delete;
l_receipt_id_array.delete;
l_ps_id_array.delete;
END LOOP;--main select cursor loop
arp_debug.debug('process_selected_receipts()-');
insert_exceptions(
p_batch_id =>p_batch_id,
p_request_id =>pg_request_id,
p_paying_customer_id =>-3,
p_exception_code => 'AUTORECERR',
p_additional_message => SQLERRM
);
arp_debug.debug('Exception : process_selected_receipts() '|| SQLERRM);
END process_selected_receipts;
UPDATE /*+ index(ct ra_customer_trx_u1) */ ra_customer_trx_all ct
SET cc_error_flag = 'Y',
last_updated_by = pg_last_updated_by,
last_update_date = sysdate,
last_update_login = pg_last_update_login,
request_id = pg_request_id,
program_application_id = pg_program_application_id,
program_id = pg_program_id,
program_update_date = sysdate
WHERE customer_trx_id in
(
SELECT /*+ push_pred(trxn_ext) */ r.customer_trx_id
FROM ar_receipts_gt r,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
(SELECT op.trxn_extension_id, summ.status
FROM iby_trxn_summaries_all summ,
iby_fndcpt_tx_operations op
WHERE(summ.transactionid = op.transactionid)
AND(reqtype = 'ORAPMTREQ')
AND(status IN(0, 100, 111, 31, 32))
AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
AND(summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND(reqtype = 'ORAPMTREQ')
AND(status IN(0, 100, 111, 31, 32))
AND(trxntypeid = 20)))))
) trxn_ext
WHERE r.gt_id = p_gt_id
AND cr.cash_receipt_id = r.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.status IS NULL
);
INSERT INTO ar_autorec_exceptions
(batch_id,
request_id,
payment_schedule_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 /*+ push_pred(trxn_ext) */ p_batch_id,
pg_request_id,
r.payment_schedule_id,
cr.cash_receipt_id,
cr.pay_from_customer,
'AR_CC_AUTH_FAILED',
'Failure in Authorization',
sysdate,
pg_last_updated_by,
sysdate,
pg_created_by,
pg_last_update_login,
pg_program_application_id,
pg_program_id,
sysdate
FROM ar_receipts_gt r,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
(SELECT op.trxn_extension_id, summ.status
FROM iby_trxn_summaries_all summ,
iby_fndcpt_tx_operations op
WHERE(summ.transactionid = op.transactionid)
AND(reqtype = 'ORAPMTREQ')
AND(status IN(0, 100, 111, 31, 32))
AND((trxntypeid IN(2, 3)) OR((trxntypeid = 20)
AND(summ.trxnmid =
(SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all
WHERE transactionid = summ.transactionid
AND(reqtype = 'ORAPMTREQ')
AND(status IN(0, 100, 111, 31, 32))
AND(trxntypeid = 20)))))
) trxn_ext
WHERE r.gt_id = p_gt_id
AND cr.cash_receipt_id = r.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.status IS NULL;
fnd_file.put_line(FND_FILE.LOG,'insert into autorec_exceptions count : '||sql%rowcount);