[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE validate_args_update_manual(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE );
PROCEDURE validate_args_update_remit(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE );
PROCEDURE validate_args_update_auto(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE );
| update_manual_batch - Updates a row in AR_BATCHES after checking for|
| uniqueness |
| |
| DESCRIPTION |
| This function updates a row in AR_BATCHES table after checking for |
| uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_row_id - Row ID |
| p_batch_source_id - Batch Source Id |
| p_batch_name - Batch Name |
| p_module_name - Module that called this procedure |
| p_module_version - Version of the module that called this |
| procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - This procedure calls the check_unique_batch_name and |
| check_unique_media_ref and arp_rw_icr_pkg.update_batch procedures |
| |
| MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
| 19-MAR-96 Simon Leung Call update_batch to update batch status. |
| 27-NOV-96 Karen Lawrance Bug fix #423518. Added GL Date, Deposit |
| Date and Batch Source to update list. |
+===========================================================================*/
PROCEDURE update_manual_batch(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_source_id IN ar_batches.batch_source_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_deposit_date IN ar_batches.batch_date%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_comments IN ar_batches.comments%TYPE,
p_control_amount IN ar_batches.control_amount%TYPE,
p_control_count IN ar_batches.control_count%TYPE,
p_exchange_date IN ar_batches.exchange_date%TYPE,
p_exchange_rate IN ar_batches.exchange_rate%TYPE,
p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
p_remittance_bank_account_id
IN ar_batches.remit_bank_acct_use_id%type,
p_remittance_bank_branch_id
IN ar_batches.remittance_bank_branch_id%TYPE,
p_attribute_category IN ar_batches.attribute_category%TYPE,
p_attribute1 IN ar_batches.attribute1%TYPE,
p_attribute2 IN ar_batches.attribute2%TYPE,
p_attribute3 IN ar_batches.attribute3%TYPE,
p_attribute4 IN ar_batches.attribute4%TYPE,
p_attribute5 IN ar_batches.attribute5%TYPE,
p_attribute6 IN ar_batches.attribute6%TYPE,
p_attribute7 IN ar_batches.attribute7%TYPE,
p_attribute8 IN ar_batches.attribute8%TYPE,
p_attribute9 IN ar_batches.attribute9%TYPE,
p_attribute10 IN ar_batches.attribute10%TYPE,
p_attribute11 IN ar_batches.attribute11%TYPE,
p_attribute12 IN ar_batches.attribute12%TYPE,
p_attribute13 IN ar_batches.attribute13%TYPE,
p_attribute14 IN ar_batches.attribute14%TYPE,
p_attribute15 IN ar_batches.attribute15%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
--
l_batch_rec ar_batches%ROWTYPE;
arp_util.debug( 'arp_rw_batches_pkg.update_manual_batch()+' );
validate_args_update_manual( p_row_id, l_batch_rec.batch_id,
l_batch_rec.batch_date );
arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
arp_rw_batches_check_pkg.update_batch_status( l_batch_rec.batch_id );
arp_util.debug( 'arp_rw_batches_pkg.update_manual_batch()-' );
arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.update_manual_batch' );
END update_manual_batch;
| update_remit_batch |
| |
| DESCRIPTION |
| This function updates a row in AR_BATCHES table after checking for |
| uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE. Used for |
| Remittance Batches only. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_row_id - Row ID |
| p_batch_source_id - Batch Source Id |
| p_batch_name - Batch Name |
| p_module_name - Module that called this procedure |
| p_module_version - Version of the module that called |
| this procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - This procedure calls the check_unique_batch_name and check_ |
| unique_media_ref and arp_rw_icr_pkg.update_batch procedures. |
| |
| MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
+===========================================================================*/
PROCEDURE update_remit_batch(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_source_id IN ar_batches.batch_source_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_deposit_date IN ar_batches.batch_date%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_comments IN ar_batches.comments%TYPE,
p_control_amount IN ar_batches.control_amount%TYPE,
p_control_count IN ar_batches.control_count%TYPE,
p_exchange_date IN ar_batches.exchange_date%TYPE,
p_exchange_rate IN ar_batches.exchange_rate%TYPE,
p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
p_remittance_bank_account_id
IN ar_batches.remit_bank_acct_use_id%type,
p_remittance_bank_branch_id
IN ar_batches.remittance_bank_branch_id%TYPE,
p_media_reference IN ar_batches.media_reference%TYPE,
p_bank_deposit_number IN ar_batches.bank_deposit_number%TYPE,
p_request_id IN ar_batches.request_id%TYPE,
p_operation_request_id IN ar_batches.operation_request_id%TYPE,
p_attribute_category IN ar_batches.attribute_category%TYPE,
p_attribute1 IN ar_batches.attribute1%TYPE,
p_attribute2 IN ar_batches.attribute2%TYPE,
p_attribute3 IN ar_batches.attribute3%TYPE,
p_attribute4 IN ar_batches.attribute4%TYPE,
p_attribute5 IN ar_batches.attribute5%TYPE,
p_attribute6 IN ar_batches.attribute6%TYPE,
p_attribute7 IN ar_batches.attribute7%TYPE,
p_attribute8 IN ar_batches.attribute8%TYPE,
p_attribute9 IN ar_batches.attribute9%TYPE,
p_attribute10 IN ar_batches.attribute10%TYPE,
p_attribute11 IN ar_batches.attribute11%TYPE,
p_attribute12 IN ar_batches.attribute12%TYPE,
p_attribute13 IN ar_batches.attribute13%TYPE,
p_attribute14 IN ar_batches.attribute14%TYPE,
p_attribute15 IN ar_batches.attribute15%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
l_batch_rec ar_batches%ROWTYPE;
arp_util.debug( 'arp_rw_batches_pkg.update_remit_batch()+' );
validate_args_update_remit( p_row_id, l_batch_rec.batch_id,
l_batch_rec.batch_date );
arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
arp_util.debug( 'arp_rw_batches_pkg.update_remit_batch()-' );
arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.update_remit_batch' );
END update_remit_batch;
| update_auto_batch |
| |
| DESCRIPTION |
| This function updates a row in AR_BATCHES table after checking for |
| uniqueness for items such as NAME, MEDIA_REFERENCE, GL_DATE. Used for |
| Automatic Batches only. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_row_id - Row ID |
| p_batch_source_id - Batch Source Id |
| p_batch_name - Batch Name |
| p_module_name - Module that called this procedure |
| p_module_version - Version of the module that called |
| this procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - This procedure calls the check_unique_batch_name and check_ |
| unique_media_ref and arp_rw_icr_pkg.update_batch procedures. |
| |
| MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
+===========================================================================*/
PROCEDURE update_auto_batch(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_source_id IN ar_batches.batch_source_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_deposit_date IN ar_batches.batch_date%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_comments IN ar_batches.comments%TYPE,
p_control_amount IN ar_batches.control_amount%TYPE,
p_control_count IN ar_batches.control_count%TYPE,
p_exchange_date IN ar_batches.exchange_date%TYPE,
p_exchange_rate IN ar_batches.exchange_rate%TYPE,
p_exchange_rate_type IN ar_batches.exchange_rate_type%TYPE,
p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
p_receipt_method_id IN ar_batches.receipt_method_id%TYPE,
p_remittance_bank_account_id
IN ar_batches.remit_bank_acct_use_id%type,
p_remittance_bank_branch_id
IN ar_batches.remittance_bank_branch_id%TYPE,
p_media_reference IN ar_batches.media_reference%TYPE,
p_bank_deposit_number IN ar_batches.bank_deposit_number%TYPE,
p_request_id IN ar_batches.request_id%TYPE,
p_operation_request_id IN ar_batches.operation_request_id%TYPE,
p_attribute_category IN ar_batches.attribute_category%TYPE,
p_attribute1 IN ar_batches.attribute1%TYPE,
p_attribute2 IN ar_batches.attribute2%TYPE,
p_attribute3 IN ar_batches.attribute3%TYPE,
p_attribute4 IN ar_batches.attribute4%TYPE,
p_attribute5 IN ar_batches.attribute5%TYPE,
p_attribute6 IN ar_batches.attribute6%TYPE,
p_attribute7 IN ar_batches.attribute7%TYPE,
p_attribute8 IN ar_batches.attribute8%TYPE,
p_attribute9 IN ar_batches.attribute9%TYPE,
p_attribute10 IN ar_batches.attribute10%TYPE,
p_attribute11 IN ar_batches.attribute11%TYPE,
p_attribute12 IN ar_batches.attribute12%TYPE,
p_attribute13 IN ar_batches.attribute13%TYPE,
p_attribute14 IN ar_batches.attribute14%TYPE,
p_attribute15 IN ar_batches.attribute15%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
l_batch_rec ar_batches%ROWTYPE;
arp_util.debug( 'arp_rw_batches_pkg.update_auto_batch()+' );
validate_args_update_auto( p_row_id, l_batch_rec.batch_id,
l_batch_rec.batch_date );
arp_cr_batches_pkg.update_p( l_batch_rec, l_batch_rec.batch_id );
arp_util.debug( 'arp_rw_batches_pkg.update_auto_batch()-' );
arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.update_auto_batch' );
END update_auto_batch;
| update_batch_status - Update the receipt batch with the status |
| |
| DESCRIPTION |
| Update the receipt batch with the status |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_batch_id - Batch Id |
| |
| RETURNS : NONE |
| |
| NOTES - This procedure will be called by update_row, insert_row procedure |
| and update_manual_batch procedure. |
| |
| MODIFICATION HISTORY - 08/09/95 - Created by Ganesh Vaidee |
| |
+===========================================================================*/
PROCEDURE update_batch_status( p_batch_id IN ar_batches.batch_id%TYPE,
p_called_from IN VARCHAR2 DEFAULT NULL) IS
l_icr_count NUMBER := 0;
arp_util.debug( 'arp_rw_batches_check_pkg.update_batch_status()+' );
arp_util.debug( 'arp_rw_batches_check_pkg.update_batch_status()-' );
SELECT NVL(SUM(DECODE
(
icr.status,
NULL, 0,
'UNAPP', 1,
1
)),0),
NVL(SUM( icr.amount ), 0 )
INTO l_icr_count, l_icr_amount
FROM ar_interim_cash_receipts icr,
ar_batches b
WHERE b.batch_id = p_batch_id
AND b.batch_id = icr.batch_id(+);
SELECT DECODE
(
ABS((NVL(SUM(DECODE
(
cr.status,
NULL, 0,
'REV', 0, 'CC_CHARGEBACK_REV',0,
1
)), 0) +
l_icr_count -
b.control_count )) +
ABS((NVL(SUM(DECODE
(
cr.status,
NULL, 0,
'REV', 0, 'CC_CHARGEBACK_REV',0,
cr.amount
)),0) +
l_icr_amount -
b.control_amount )) + --Qiong fix bug 10252186 changed from - to +
ABS((NVL(SUM(DECODE
(
cr.status,
'APP', cr.amount,
'NSF', cr.amount,
'STOP', cr.amount,
0
)),0) -
b.control_amount )) + --Qiong fix bug 10252186 changed from - to +
ABS((NVL(SUM(DECODE
(
cr.status,
'APP', 1,
'NSF', 1,
'STOP', 1,
0
)),0) -
b.control_count )),
0, 'CL',
DECODE
(
ABS(SIGN(NVL(SUM(DECODE
(
cr.status,
NULL, 0,
/* 'REV', 1, 'CC_CHARGEBACK_REV',1,*/
1
)),0) +
l_icr_count -
b.control_count )) +
ABS(SIGN(NVL(SUM(DECODE
(
cr.status,
NULL, 0,
/* 'REV', cr.amount, 'CC_CHARGEBACK_REV',cr.amount,*/
cr.amount
)),0) +
l_icr_amount -
b.control_amount )),
0, 'OP',
'OOB'
)
)
INTO l_batch_status
FROM ar_cash_receipt_history crh,
ar_cash_receipts cr,
ar_batches b
WHERE b.batch_id = p_batch_id
AND crh.batch_id(+) = b.batch_id
AND crh.first_posted_record_flag(+) = 'Y'
AND cr.cash_receipt_id(+) = crh.cash_receipt_id
GROUP BY b.batch_id,
b.control_count,
b.control_amount;
arp_standard.debug('arp_rw_batches_check_pkg.update_batch_status: Before locking');
SELECT 'Y',TYPE
INTO l_exists,l_type
FROM ar_batches
WHERE batch_id = p_batch_id;
arp_standard.debug('arp_rw_batches_check_pkg.update_batch_status: No data with new status');
arp_standard.debug( 'EXCEPTION: arp_rw_batches_check_pkg.update_batch_status lock batch' );
SELECT batch_id
INTO l_bat_id
FROM ar_batches
WHERE batch_id = p_batch_id
FOR UPDATE OF STATUS;
arp_standard.debug('arp_rw_batches_check_pkg.update_batch_status: Before calling update for batch');
arp_cr_batches_pkg.update_p( l_batch_rec, p_batch_id );
'EXCEPTION: arp_rw_batches_check_pkg.update_batch_status' );
END update_batch_status;
| validate_args_update_manual |
| |
| DESCRIPTION |
| Validate arguments passed to update_manual_batch procedure |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_type - Batch Type |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 07/12/95 |
| |
+===========================================================================*/
PROCEDURE validate_args_update_manual(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_manual()+' );
arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_manual()-' );
'EXCEPTION: arp_rw_batches_pkg.validate_args_update_manual' );
END validate_args_update_manual;
| validate_args_update_remit |
| |
| DESCRIPTION |
| Validate arguments passed to update_remit_batch procedure |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_type - Batch Type |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
| |
+===========================================================================*/
PROCEDURE validate_args_update_remit(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_remit()+' );
arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_remit()-' );
'EXCEPTION: arp_rw_batches_pkg.validate_args_update_remit' );
END validate_args_update_remit;
| validate_args_update_auto |
| |
| DESCRIPTION |
| Validate arguments passed to update_auto_batch procedure |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_util.debug - debug procedure |
| |
| ARGUMENTS : IN: |
| p_type - Batch Type |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES - |
| |
| MODIFICATION HISTORY - Created by Caroline M Clyde (21 MAY 1997) |
| |
+===========================================================================*/
PROCEDURE validate_args_update_auto(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_auto()+' );
arp_util.debug( 'arp_rw_batches_pkg.validate_args_update_auto()-' );
'EXCEPTION: arp_rw_batches_pkg.validate_args_update_auto' );
END validate_args_update_auto;
SELECT COUNT(*)
INTO l_count
FROM AR_BATCHES bat
WHERE bat.batch_source_id = p_batch_source_id
AND bat.name = p_batch_name
AND ( p_row_id IS NULL
OR bat.rowid <> p_row_id );
SELECT COUNT(*)
INTO l_count
FROM AR_BATCH_SOURCES bs,
AR_BATCHES bat
WHERE bat.batch_source_id = bs.batch_source_id
AND bs.name = p_batch_source_name
AND bat.name = p_batch_name
AND ( p_row_id IS NULL
OR bat.rowid <> p_row_id );
SELECT COUNT(*)
INTO l_count
FROM AR_BATCHES bat
WHERE bat.media_reference = p_media_ref
AND ( p_row_id IS NULL
OR bat.rowid <> p_row_id );
select org_id into l_org_id from ar_system_parameters;
| 15-OCT-98 Karen Murphy Cross Currency Lockbox. Modified select |
| statements that look at payment_amount in |
| interim cash receipt lines. Need to consider|
| the amount applied from column as this stores|
| the amount in receipt currency for cross |
| currency applications. |
| 01-JUN-01 Muthuraman. R Added an NVL condition so that Unapplied |
| Receipt counts are correct prior to |
| postquick cash in receipt batches window. |
| 07-JAN-03 K Dhaliwal Bug 2707190 added Claim Amount and Count |
| to get_quick_amount_totals |
+===========================================================================*/
PROCEDURE get_quick_amount_totals( p_batch_id IN ar_batches.batch_id%TYPE,
p_actual_amount_total OUT NOCOPY NUMBER,
p_actual_count_total OUT NOCOPY NUMBER,
p_unidentified_amount_total OUT NOCOPY NUMBER,
p_unidentified_count_total OUT NOCOPY NUMBER,
p_on_account_amount_total OUT NOCOPY NUMBER,
p_on_account_count_total OUT NOCOPY NUMBER,
p_unapplied_amount_total OUT NOCOPY NUMBER,
p_unapplied_count_total OUT NOCOPY NUMBER,
p_applied_amount_total OUT NOCOPY NUMBER,
p_applied_count_total OUT NOCOPY NUMBER,
p_claim_amount_total OUT NOCOPY NUMBER,
p_claim_count_total OUT NOCOPY NUMBER,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
--
l_unapplied_amount_total NUMBER := 0;
l_break_point := '1st SELECT';
SELECT SUM( NVL( icr.amount, 0 ) ), COUNT(*),
NVL( SUM( DECODE( icr.special_type,
'CLAIM', icr.amount,
0
)
), 0
),
NVL( COUNT( DECODE( icr.special_type,
'CLAIM', 1,
''
)
), 0
),
NVL( SUM( DECODE( icr.special_type,
'ON_ACCOUNT', icr.amount,
0
)
), 0
),
NVL( COUNT( DECODE( icr.special_type,
'ON_ACCOUNT', 1,
''
)
), 0
),
NVL( SUM( DECODE( NVL(icr.special_type,'SINGLE'),
'RECEIPT_RULE', icr.amount,
'SINGLE', icr.amount,
0
)
), 0
),
NVL( COUNT( DECODE( NVL(icr.special_type,'SINGLE'),
'RECEIPT_RULE', 1,
'SINGLE', 1,
''
)
), 0
),
NVL( SUM( DECODE( icr.special_type,
'UNAPPLIED', icr.amount,
'MULTIPLE', icr.amount,
0
)
), 0
),
NVL( COUNT( DECODE( icr.special_type,
'UNAPPLIED', 1,
''
)
), 0
),
NVL( SUM( DECODE( icr.special_type,
'UNIDENTIFIED', icr.amount,
0
)
), 0
),
NVL( COUNT( DECODE( icr.special_type,
'UNIDENTIFIED', 1,
''
)
), 0
)
INTO l_actual_amount_total,
l_actual_count_total,
l_claim_amount_total_header,
l_claim_count_total_header,
l_onacct_amount_total_header,
l_onacct_count_total_header,
l_applied_amount_total,
l_applied_count_total,
l_unapplied_amount_total,
l_unapplied_count_total,
p_unidentified_amount_total,
p_unidentified_count_total
FROM ar_interim_cash_receipts icr
WHERE icr.batch_id = p_batch_id;
l_break_point := '2nd SELECT';
SELECT l_applied_amount_total + NVL(SUM( nvl(icrl.amount_applied_from, icrl.payment_amount) ),0),
l_unapplied_amount_total - NVL(SUM( nvl(icrl.amount_applied_from, icrl.payment_amount) ),0),
NVL( SUM(decode(icrl.payment_schedule_id,-4,nvl(icrl.amount_applied_from, icrl.payment_amount),0 )),0),
NVL( SUM(decode(icrl.payment_schedule_id,-1,nvl(icrl.amount_applied_from, icrl.payment_amount),0 )),0)
INTO l_applied_amount_total,
l_unapplied_amount_total,
l_claim_amount_total_lines,
l_onacct_amount_total_lines
FROM ar_interim_cash_receipts icr,
ar_interim_cash_receipt_lines icrl
WHERE icrl.cash_receipt_id = icr.cash_receipt_id
AND icr.batch_id = p_batch_id;
l_break_point := '3rd SELECT';
SELECT NVL(l_applied_count_total,0) +
COUNT( DECODE( SIGN(rec.amount - SUM( nvl(ln.amount_applied_from, ln.payment_amount) ) ),
0, 1, '' )),
NVL(l_unapplied_count_total,0) +
COUNT( DECODE( SIGN(rec.amount - NVL( SUM( nvl(ln.amount_applied_from, ln.payment_amount) ) , 0 )),
1, 1, '' ))
INTO l_applied_count_total,
l_unapplied_count_total
FROM ar_interim_cash_receipt_lines ln,
ar_interim_cash_receipts rec
WHERE rec.batch_id = p_batch_id
AND rec.cash_receipt_id = ln.cash_receipt_id
GROUP BY
rec.cash_receipt_id,
rec.amount;
l_break_point := '4th SELECT';
SELECT sum(sign(CLAIM)) Claims_Count
,sum(sign(ON_ACC)) On_Account_Count
--,sum(nvl(TRX,0)) + nvl(l_applied_count_total,0) Trx_Count
INTO l_claim_count_total_lines
,l_onacct_count_total_lines
--,l_applied_count_total
FROM (select sum(decode(payment_schedule_id,-4,1,0)) CLAIM
, SUM(decode(payment_schedule_id,-1,1,0)) ON_ACC
, sum(decode(sign(payment_schedule_id),1,1,0)) TRX
from ar_interim_cash_receipt_lines ln
where ln.batch_id = p_batch_id
);
l_break_point := '1st SELECT';
SELECT NVL( SUM( cr.amount ),0),
NVL( COUNT( cr.status ) ,0 ),
NVL( SUM(DECODE( cr.status, 'NSF', cr.amount, 'STOP', cr.amount,0)),0),
NVL( COUNT(DECODE( cr.status, 'NSF', 1, 'STOP', 1,'')),0),
NVL( SUM(DECODE( cr.status, 'REV', cr.amount, 'CC_CHARGEBACK_REV', cr.amount, 0)), 0),
NVL( COUNT(DECODE( cr.status, 'REV', 1, 'CC_CHARGEBACK_REV', 1, '')), 0),
NVL( COUNT(DECODE( cr.status , 'UNAPP', 1, '')),0),
NVL(COUNT (DECODE(cr.status,
'APP', DECODE( cr.type, 'MISC', '', 1),
'')), 0),
NVL( SUM( DECODE( cr.status, 'UNID', cr.amount,0)), 0),
NVL( COUNT(DECODE( cr.status , 'UNID', 1, '')),0),
NVL( SUM( DECODE( cr.type,
'MISC', DECODE( cr.status, 'REV', 0,
'NSF', 0,
'STOP', 0,
'CC_CHARGEBACK_REV', 0,cr.amount), --For9821276
0 )), 0),
NVL( COUNT(DECODE( cr.type,
'MISC', DECODE( cr.status, 'REV', '',
'NSF', '',
'STOP', '',
'CC_CHARGEBACK_REV', '',1), --For9821276
'')), 0)
INTO l_actual_amount_total,
l_actual_count_total,
p_returned_amount_total,
p_returned_count_total,
p_reversed_amount_total,
p_reversed_count_total,
l_unapplied_count_total,
l_applied_count_total,
p_unidentified_amount_total,
p_unidentified_count_total,
l_misc_amount_total,
l_misc_count_total
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.first_posted_record_flag = 'Y'
AND crh.batch_id = p_batch_id;
l_break_point := '2nd SELECT';
SELECT NVL(SUM(DECODE(ra.STATUS, 'APP', nvl(ra.amount_applied_from, ra.amount_applied),
'ACTIVITY',--Added for bug 1647470
DECODE(ra.applied_payment_schedule_id,
-3,ra.amount_applied,
-8,ra.amount_applied
,Decode(ra.RECEIVABLES_TRX_ID,-16,ra.amount_applied,0)),
0)),0),
NVL( SUM( DECODE( ra.status, 'UNAPP', ra.amount_applied,0)), 0),
NVL(SUM(DECODE(ra.STATUS, 'ACC', ra.amount_applied, 0)),0),
NVL(SUM(DECODE(ra.STATUS, 'OTHER ACC',DECODE(applied_payment_schedule_id,
-4,NVL(ra.amount_applied_from, ra.amount_applied),0),0)),0),
NVL(SUM(DECODE(ra.STATUS, 'OTHER ACC',DECODE(applied_payment_schedule_id,
-7,NVL(ra.amount_applied_from, ra.amount_applied),0),0)),0)
INTO l_applied_amount_total,
l_unapplied_amount_total,
l_on_account_amount_total,
l_claim_amount_total,
l_prepayment_amount_total
FROM ar_receivable_applications ra,
ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.first_posted_record_flag = 'Y'
AND crh.batch_id = p_batch_id;
l_break_point := '3rd SELECT';
SELECT nvl(count(cr.cash_receipt_id), 0)
INTO l_on_account_count_total
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.first_posted_record_flag = 'Y'
AND crh.batch_id = p_batch_id
AND cr.amount = (SELECT sum(ra.amount_applied)
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'ACC'
AND ra.display = 'Y'); -- Fix 1178963
l_break_point := '4th SELECT';
SELECT nvl(count(cr.cash_receipt_id), 0)
INTO l_claim_count_total
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.first_posted_record_flag = 'Y'
AND crh.batch_id = p_batch_id
AND cr.amount = (SELECT
sum(ra.amount_applied)
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'OTHER ACC'
AND ra.applied_payment_schedule_id + 0 = -4
AND ra.display = 'Y');
l_break_point := '5th SELECT';
SELECT nvl(count(cr.cash_receipt_id), 0)
INTO l_prepayment_count_total
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = cr.cash_receipt_id
AND crh.first_posted_record_flag = 'Y'
AND crh.batch_id = p_batch_id
AND cr.amount = (SELECT
sum(ra.amount_applied)
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'OTHER ACC'
AND ra.applied_payment_schedule_id + 0 = -7
AND ra.display = 'Y');