[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| call to update_batch_after_process() to update the |
| batch status after all the child requests are completed|
| DESCRIPTION |
| Submits child requests. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug - debug procedure |
| arp_standard.debug() - debug procedure |
| FND_REQUEST.wait_for_request |
| ARGUMENTS : IN: |
| p_org_id - Org ID |
| p_batch_id - Batch Id |
| p_transmission_id - Lockbox transmission ID |
| p_total_workers - Number of workers |
| |
| OUT: P_ERRBUF |
| P_RETCODE |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
| 01/02/2008 - Modified parameter list. |
| Added parameter p_app_unearn_disc - 24/03/2010 |
| for ER 9288902 |
+===========================================================================*/
PROCEDURE submit_postbatch_parallel(
P_ERRBUF OUT NOCOPY VARCHAR2,
P_RETCODE OUT NOCOPY NUMBER,
p_org_id IN NUMBER,
p_batch_id IN NUMBER,
p_transmission_id IN NUMBER,
p_app_unearn_disc IN VARCHAR2,
p_total_workers IN NUMBER DEFAULT 1 ) AS
l_worker_number NUMBER ;
SELECT ab.name batch_name,
abs.name batch_source_name,
ab.batch_date,
ab.gl_date,
ab.deposit_date,
ab.status,
ab.comments,
ab.batch_applied_status,
ab.control_count,
ab.control_amount,
cba.bank_account_name,
cba.bank_account_num,
ab.currency_code,
to_number(to_char(ab.gl_date, 'J')),
to_number(to_char(ab.deposit_date, 'J'))
FROM ar_batches ab,
ar_batch_sources abs,
ce_bank_accounts cba,
ce_bank_acct_uses_all ba
WHERE ab.batch_source_id = abs.batch_source_id
AND ab.remit_bank_acct_use_id
= ba.bank_acct_use_id (+)
AND ba.bank_account_id = cba.bank_account_id (+)
AND ab.org_id = ba.org_id
AND ab.batch_id = p_batch_id;
SELECT ab.name batch_name,
abs.name batch_source_name,
ab.batch_date,
ab.gl_date,
ab.deposit_date,
ab.status,
ab.comments,
ab.batch_applied_status,
ab.control_count,
ab.control_amount,
cba.bank_account_name,
cba.bank_account_num,
ab.currency_code,
to_number(to_char(ab.gl_date, 'J')),
to_number(to_char(ab.deposit_date, 'J')),
ab.batch_id
FROM ar_batches ab,
ar_batch_sources abs,
ce_bank_accounts cba,
ce_bank_acct_uses_all ba
WHERE ab.batch_source_id = abs.batch_source_id
AND ab.remit_bank_acct_use_id
= ba.bank_acct_use_id
AND cba.bank_account_id = ba.bank_account_id
AND ab.batch_applied_status = l_batch_applied_status
AND ab.org_id = ba.org_id
AND ab.transmission_id = p_transmission_id
ORDER BY ab.batch_id;
| update_batch_after_process() -This process updates the batch status |
| after all the receipts in the batch are processed by ARCABP. |
| DESCRIPTION |
| Updates the batch_applied_status of the batch to "PROCESSED' |
| and status to 'CL'/'OP' |
| SCOPE - |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: |
| p_batch_id - Batch ID |
| p_batch_applied_status - Batch Applied Status |
| p_ct_cnt - Control Count |
| p_ct_amt - Control Amount |
| OUT: None |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
+===========================================================================*/
PROCEDURE update_batch_after_process( p_batch_id ar_batches.batch_id%TYPE,
p_batch_applied_status ar_batches.batch_applied_status%TYPE,
p_ct_cnt NUMBER,
p_ct_amt NUMBER
) AS
l_act_app_cnt NUMBER;
fnd_file.put_line( FND_FILE.LOG, 'Process_batch_for_update()+');
SELECT count(*), nvl(sum(cr.amount),0)
INTO l_act_app_cnt, l_act_app_amt
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.batch_id = p_batch_id
AND cr.status = 'APP';
SELECT COUNT(1)
INTO l_err_receipt_cnt
FROM ar_interim_cash_receipts
WHERE batch_id = p_batch_id;
UPDATE ar_batches
SET batch_applied_status = 'PROCESSED',
status = l_status,
last_updated_by = G_USER_ID,
last_update_date = sysdate,
program_id = G_CONC_PROGRAM_ID,
request_id = G_CONC_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = sysdate
WHERE batch_id = p_batch_id;
update_batch_for_rerun( l_status, p_batch_id);
END update_batch_after_process;
* if user selects 'Y' for Apply unearned Discounts during the submission
* of Postbatch Master Program *
* ******************************************************************** */
l_app_unearn_disc := NVL(p_app_unearn_disc, 'N');
SELECT NVL(unearned_discount, 'N')
INTO l_allow_unearn_disc
FROM ar_system_parameters;
SELECT 'locked'
INTO l_locked_status
FROM ar_batches
WHERE batch_id = lbr.batch_id
FOR UPDATE OF batch_applied_status, status,
last_update_date, last_updated_by;
UPDATE ar_batches
SET batch_applied_status = 'IN_PROCESS',
last_update_date = sysdate,
last_updated_by = G_USER_ID,
program_id = G_CONC_PROGRAM_ID,
request_id = G_CONC_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = sysdate
WHERE batch_id = lbr.batch_id;
SELECT 'locked'
INTO l_locked_status
FROM ar_batches
WHERE batch_id = p_batch_id
FOR UPDATE OF batch_applied_status, status,
last_update_date, last_updated_by;
UPDATE ar_batches
SET batch_applied_status = 'IN_PROCESS',
last_update_date = sysdate,
last_updated_by = G_USER_ID,
program_id = G_CONC_PROGRAM_ID,
request_id = G_CONC_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = sysdate
WHERE batch_id = p_batch_id;
update_batch_after_process( lbr.batch_id,
lbr.batch_applied_status,
lbr.control_count,
lbr.control_amount );
update_batch_after_process( p_batch_id,
qcr.batch_applied_status,
qcr.control_count,
qcr.control_amount );
UPDATE ar_transmissions t
SET status = 'CL',
last_updated_by = G_USER_ID,
last_update_date = trunc(sysdate)
WHERE transmission_id = p_transmission_id
AND NOT EXISTS ( SELECT 'pending post'
FROM ar_batches b
WHERE b.transmission_id =
t.transmission_id
AND batch_applied_status
= 'POSTBATCH_WAITING' )
AND NOT EXISTS ( SELECT 'pending transfer'
FROM ar_payments_interface pi
WHERE pi.transmission_id =
t.transmission_id);
| update_batch_for_rerun() - If any error occurs during the postbatch |
| process, the batch_applied_status is put back to 'POSTBATCH_WAITING' |
| for rerun at later time. |
| DESCRIPTION |
| Updates batch_applied_Status to 'POSTBATCH_WAITING' |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| ARGUMENTS : IN: |
| p_status - Batch Status |
| p_batch_id - Batch Id |
| |
| OUT: None |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY - 09/01/2008 - Created by AGHORAKA |
+===========================================================================*/
PROCEDURE update_batch_for_rerun( p_status IN ar_batches.status%TYPE,
p_batch_id IN NUMBER) AS
BEGIN
fnd_file.put_line( FND_FILE.LOG, 'update_batch_for_rerun()+');
UPDATE ar_batches
SET batch_applied_status = 'POSTBATCH_WAITING',
status = p_status,
last_updated_by = G_USER_ID,
last_update_date = sysdate,
program_id = G_CONC_PROGRAM_ID,
request_id = G_CONC_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_update_date = sysdate
WHERE batch_id = p_batch_id;
fnd_file.put_line( FND_FILE.LOG, 'update_batch_for_rerun()-');
END update_batch_for_rerun;