[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE validate_args_insert_manual(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_type IN ar_batches.type%TYPE,
p_batch_source_id IN ar_batches.batch_source_id%TYPE,
p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
p_name IN ar_batches.name%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_batch_applied_status IN ar_batches.batch_applied_status%TYPE,
p_auto_batch_numbering IN ar_batch_sources.auto_batch_numbering%TYPE);
PROCEDURE validate_args_insert_remit(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_type IN ar_batches.type%TYPE,
p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_remit_method_code IN ar_batches.remit_method_code%TYPE,
p_remittance_bank_branch_id IN
ar_batches.remittance_bank_branch_id%TYPE,
p_batch_applied_status IN ar_batches.batch_applied_status%TYPE );
PROCEDURE validate_args_insert_auto(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_type IN ar_batches.type%TYPE,
p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
p_name IN ar_batches.name%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_receipt_class_id IN
ar_batches.receipt_class_id%TYPE,
p_receipt_method_id IN
ar_batches.receipt_method_id%TYPE,
p_batch_applied_status IN ar_batches.batch_applied_status%TYPE );
| insert_manual_batch - Insert a row AR_BATCHES table after checking for|
| uniqueness |
| |
| DESCRIPTION |
| This function inserts 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 procedures |
| |
| MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
| 19-MAR-96 Simon Leung Set batch status to 'NB' for new batch. |
+===========================================================================*/
PROCEDURE insert_manual_batch(
p_row_id IN OUT NOCOPY VARCHAR2,
p_batch_type IN VARCHAR2,
p_batch_id IN OUT NOCOPY 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_currency_code IN ar_batches.currency_code%TYPE,
p_name IN OUT NOCOPY ar_batches.name%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_deposit_date IN ar_batches.deposit_date%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_gl_date IN ar_batches.gl_date%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_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
--
l_batch_rec ar_batches%ROWTYPE;
arp_util.debug('validate_args_insert_manual: ' || 'arp_rw_batches_pkg.insert_manual_batch()+' );
arp_util.debug('validate_args_insert_manual: ' || 'Row Id : '||p_row_id );
arp_util.debug('validate_args_insert_manual: ' || 'Batch Id : '||p_batch_id );
arp_util.debug('validate_args_insert_manual: ' || 'Batch Source Id : '||TO_CHAR( p_batch_source_id ) );
arp_util.debug('validate_args_insert_manual: ' || 'Batch Name : '||p_name );
arp_util.debug('validate_args_insert_manual: ' || 'GL Date : '||p_gl_date );
SELECT auto_batch_numbering
INTO l_auto_batch_numbering
FROM ar_batch_sources
WHERE batch_source_id = l_batch_rec.batch_source_id;
arp_util.debug('validate_args_insert_manual: ' || 'Auto Batch Num : '||l_auto_batch_numbering );
validate_args_insert_manual( p_row_id,
l_batch_rec.batch_id,
l_batch_rec.type,
l_batch_rec.batch_source_id,
l_batch_rec.set_of_books_id,
l_batch_rec.name,
l_batch_rec.currency_code,
l_batch_rec.gl_date,
l_batch_rec.batch_date,
l_batch_rec.batch_applied_status,
l_auto_batch_numbering );
UPDATE ar_batch_sources
SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
WHERE batch_source_id = l_batch_rec.batch_source_id;
SELECT NVL( LAST_BATCH_NUM, 0 )
INTO l_batch_rec.name
FROM ar_batch_sources
WHERE batch_source_id = l_batch_rec.batch_source_id;
arp_cr_batches_pkg.insert_p( l_batch_rec,
p_row_id,
p_batch_id );
arp_util.debug('validate_args_insert_manual: ' || 'arp_rw_batches_pkg.insert_manual_batch()-' );
arp_util.debug('validate_args_insert_manual: ' || 'EXCEPTION: arp_rw_batches_pkg.insert_manual_batch' );
END insert_manual_batch;
| validate_args_insert_manual |
| |
| DESCRIPTION |
| Validate arguments passed to insert_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_insert_manual(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_type IN ar_batches.type%TYPE,
p_batch_source_id IN ar_batches.batch_source_id%TYPE,
p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
p_name IN ar_batches.name%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_batch_applied_status IN ar_batches.batch_applied_status%TYPE,
p_auto_batch_numbering IN ar_batch_sources.auto_batch_numbering%TYPE) IS
l_field VARCHAR2(30);
arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_manual()+' );
FND_MESSAGE.set_token('PROCEDURE', 'VALIDATE_ARGS_INSERT_MANUAL');
arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_manual()-' );
arp_util.debug('validate_args_insert_manual: ' ||
'EXCEPTION: arp_rw_batches_pkg.validate_args_insert_manual' );
END validate_args_insert_manual;
| insert_auto_batch - Insert a row AR_BATCHES table after checking for|
| uniqueness |
| |
| DESCRIPTION |
| This function inserts 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 procedures |
| |
| MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
| 09-FEB-96 Simon Leung If confirm is required, gl_date should be |
| null. |
| 16-DEC-03 K Mahajan Added Batch-related validations to the |
| insert_auto_batch procedure as part |
| of the changes for bug 3167260 |
+===========================================================================*/
PROCEDURE insert_auto_batch(
p_row_id IN OUT NOCOPY VARCHAR2,
p_batch_id IN OUT NOCOPY ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_name IN OUT NOCOPY ar_batches.name%TYPE,
p_comments IN ar_batches.comments%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_gl_date IN ar_batches.gl_date%TYPE,
p_media_reference IN ar_batches.media_reference%TYPE,
p_receipt_class_id IN ar_batches.receipt_class_id%TYPE,
p_receipt_method_id IN ar_batches.receipt_method_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_call_conc_req IN VARCHAR2,
p_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
p_request_id OUT NOCOPY ar_batches.request_id%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
p_bank_account_low IN VARCHAR2,
p_bank_account_high IN VARCHAR2 ) IS
--
l_batch_rec ar_batches%ROWTYPE;
SELECT confirm_flag
FROM ar_receipt_classes
WHERE receipt_class_id = p_receipt_class_id;
SELECT rc.receipt_class_id
FROM ar_receipt_classes rc
WHERE rc.receipt_class_id = l_batch_rec.receipt_class_id
AND rc.creation_method_code = 'AUTOMATIC';
SELECT rm.receipt_method_id
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = l_batch_rec.receipt_method_id
AND rm.receipt_class_id = l_batch_rec.receipt_class_id
AND l_batch_rec.batch_date between rm.start_date and nvl(rm.end_date, l_batch_rec.batch_date)
AND EXISTS (SELECT 1
FROM ar_receipt_method_accounts rma,
ce_bank_accounts cba,
ce_bank_acct_uses ba
WHERE rma.receipt_method_id = l_batch_rec.receipt_method_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND cba.bank_account_id = ba.bank_account_id
AND cba.currency_code = decode( cba.receipt_multi_currency_flag, 'Y', cba.currency_code,l_batch_rec.currency_code)
AND l_batch_rec.batch_date <= nvl(ba.end_date, l_batch_rec.batch_date)
AND l_batch_rec.batch_date between rma.start_date and nvl(rma.end_date, l_batch_rec.batch_date));
arp_util.debug( 'arp_rw_batches_pkg.insert_auto_batch()+' );
arp_util.debug('insert_auto_batch: ' || 'Row Id : '||p_row_id );
arp_util.debug('insert_auto_batch: ' || 'Batch Id : '||p_batch_id );
arp_util.debug('insert_auto_batch: ' || 'GL Date : '||p_gl_date );
arp_util.debug('insert_auto_batch: ' || 'Bank_account_low : '||p_bank_account_low );
arp_util.debug('insert_auto_batch: ' || 'Bank_account_high : '||p_bank_account_high );
validate_args_insert_auto( p_row_id,
l_batch_rec.batch_id,
l_batch_rec.type,
l_batch_rec.set_of_books_id,
l_batch_rec.name,
l_batch_rec.currency_code,
l_batch_rec.batch_date,
l_batch_rec.receipt_class_id,
l_batch_rec.receipt_method_id,
l_batch_rec.batch_applied_status );
fnd_message.set_token('PROCEDURE', 'INSERT_AUTO_BATCH');
fnd_message.set_token('PROCEDURE', 'INSERT_AUTO_BATCH');
fnd_message.set_token('PROCEDURE', 'INSERT_AUTO_BATCH');
UPDATE ar_batch_sources
SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
WHERE batch_source_id = l_batch_rec.batch_source_id;
SELECT NVL( LAST_BATCH_NUM, 0 )
INTO l_batch_rec.name
FROM ar_batch_sources
WHERE batch_source_id = l_batch_rec.batch_source_id;
arp_cr_batches_pkg.insert_p( l_batch_rec,
p_row_id,
p_batch_id );
arp_cr_batches_pkg.update_p( l_batch_rec, p_batch_id );
arp_util.debug( 'arp_rw_batches_pkg.insert_auto_batch()-' );
arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.insert_auto_batch' );
END insert_auto_batch;
| validate_args_insert_auto |
| |
| DESCRIPTION |
| Validate arguments passed to insert_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 Ganesh Vaidee - 07/12/95 |
| |
+===========================================================================*/
PROCEDURE validate_args_insert_auto(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_type IN ar_batches.type%TYPE,
p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
p_name IN ar_batches.name%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_receipt_class_id IN
ar_batches.receipt_class_id%TYPE,
p_receipt_method_id IN
ar_batches.receipt_method_id%TYPE,
p_batch_applied_status IN ar_batches.batch_applied_status%TYPE ) IS
l_field VARCHAR2(30);
arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_auto()+' );
FND_MESSAGE.set_token('PROCEDURE', 'VALIDATE_ARGS_INSERT_AUTO');
arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_auto()-' );
arp_util.debug('validate_args_insert_auto: ' ||
'EXCEPTION: arp_rw_batches_pkg.validate_args_insert_auto' );
END validate_args_insert_auto;
| insert_remit_batch - Insert a row AR_BATCHES table after checking for|
| uniqueness |
| |
| DESCRIPTION |
| This function inserts 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_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 procedures |
| |
| MODIFICATION HISTORY - 07/12/95 - Created by Ganesh Vaidee |
| 25-JAN-96 Simon Leung Implemented logic to set batch_applied_status|
| for auto-creation and manual-creation batch |
| Removed logic to submit conc req process. |
| 09-FEB-96 Simon Leung If the bank is a clearing house, no remit |
| bank account should be specified. |
| 13-MAY-96 Simon Leung Added new parameter receipt_class_id. |
| 16-DEC-03 K Mahajan Added validations for Auto-Remit SRS program |
| as part of the fix for bug / enh. 3167260 |
| 12-JAN-04 M Ryzhikova Modified fix for 3157260 to incorporate |
| consolidated bank accounts changes |
+===========================================================================*/
PROCEDURE insert_remit_batch(
p_row_id IN OUT NOCOPY VARCHAR2,
p_batch_id IN OUT NOCOPY ar_batches.batch_id%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_name IN OUT NOCOPY ar_batches.name%TYPE,
p_comments IN ar_batches.comments%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_gl_date IN ar_batches.gl_date%TYPE,
p_media_reference IN ar_batches.media_reference%TYPE,
p_remit_method_code IN ar_batches.remit_method_code%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_bank_deposit_number IN ar_batches.bank_deposit_number%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_auto_creation IN VARCHAR2,
p_batch_applied_status OUT NOCOPY ar_batches.batch_applied_status%TYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
--
l_batch_rec ar_batches%ROWTYPE;
SELECT bank_institution_type
FROM ce_bank_branches_v
WHERE branch_party_id = p_remittance_bank_branch_id;
SELECT rc.receipt_class_id
FROM ar_receipt_classes rc
WHERE rc.receipt_class_id = l_batch_rec.receipt_class_id
AND rc.remit_flag = 'Y'
AND rc.creation_method_code not in ('BR','BR_REMIT')
AND (rc.remit_method_code = l_batch_rec.remit_method_code
OR rc.remit_method_code = 'STANDARD_AND_FACTORING');
SELECT rm.receipt_method_id
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = l_batch_rec.receipt_method_id
AND rm.receipt_class_id = l_batch_rec.receipt_class_id
AND l_batch_rec.batch_date between rm.start_date and nvl(rm.end_date, l_batch_rec.batch_date);
SELECT distinct bb.branch_party_id
FROM ce_bank_branches_v bb
WHERE bb.branch_party_id = l_batch_rec.remittance_bank_branch_id
AND ( bb.branch_party_id in
(SELECT cba.bank_branch_id
FROM ce_bank_accounts cba,
ce_bank_acct_uses ba
WHERE cba.bank_account_id = ba.bank_account_id
AND ba.bank_acct_use_id =l_batch_rec.remit_bank_acct_use_id)
AND l_batch_rec.remit_bank_acct_use_id is not null)
OR (bb.bank_institution_type = 'CLEARING HOUSE'
AND l_batch_rec.remit_bank_acct_use_id is null
AND EXISTS (SELECT 1
FROM ar_receipt_method_accounts rma2,
ce_bank_accounts cba2,
ce_bank_acct_uses ba2,
ce_bank_branches_v bb2,
hz_parties CHParty,
hz_parties RelParty,
hz_relationships BCRel,
hz_contact_points Eft
WHERE rma2.receipt_method_id = l_batch_rec.receipt_method_id
AND rma2.remit_bank_acct_use_id = ba2.bank_acct_use_id
AND cba2.bank_account_id = ba2.bank_account_id
AND cba2.currency_code = l_batch_rec.currency_code
AND cba2.bank_branch_id = bb2.branch_party_id
AND BCRel.subject_id = bb2.branch_party_id
AND BCRel.subject_type = 'ORGANIZATION'
AND BCRel.subject_table_name = 'HZ_PARTIES'
AND BCRel.object_id = CHParty.party_id
AND BCRel.object_type = 'ORGANIZATION'
AND BCRel.object_table_name = 'HZ_PARTIES'
AND BCRel.relationship_type = 'CLEARINGHOUSE_BANK'
AND BCRel.relationship_code = 'USES_CLEARINGHOUSE'
AND BCRel.status = 'A'
AND RelParty.party_id = BCRel.party_id
AND Eft.owner_table_name(+) = 'HZ_PARTIES'
AND Eft.owner_table_id(+) = CHParty.party_id
AND Eft.contact_point_type(+) = 'EFT'
AND Eft.status(+) = 'A'
AND CHParty.party_id = bb.branch_party_id));
SELECT ba.bank_acct_use_id
FROM ce_bank_accounts cba,
ce_bank_acct_uses ba,
ce_bank_branches_v br,
ar_receipt_method_accounts rma
WHERE ba.bank_acct_use_id = l_batch_rec.remit_bank_acct_use_id
AND rma.receipt_method_id = nvl(l_batch_rec.receipt_method_id, rma.receipt_method_id)
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND cba.bank_account_id = ba.bank_account_id
AND cba.bank_branch_id = l_batch_rec.remittance_bank_branch_id
AND cba.bank_branch_id = br.branch_party_id
AND (cba.currency_code = l_batch_rec.currency_code
OR cba.receipt_multi_currency_flag = 'Y')
AND l_batch_rec.batch_date <= nvl(ba.end_date, l_batch_rec.batch_date)
AND l_batch_rec.batch_date between rma.start_date and nvl(rma.end_date, l_batch_rec.batch_date);
arp_util.debug( 'arp_rw_batches_pkg.insert_remit_batch()+' );
arp_util.debug('insert_remit_batch: ' || 'Row Id : '||p_row_id );
arp_util.debug('insert_remit_batch: ' || 'Batch Id : '||p_batch_id );
arp_util.debug('insert_remit_batch: ' || 'GL Date : '||p_gl_date );
validate_args_insert_remit( p_row_id,
l_batch_rec.batch_id,
l_batch_rec.type,
l_batch_rec.set_of_books_id,
l_batch_rec.currency_code,
l_batch_rec.gl_date,
l_batch_rec.batch_date,
l_batch_rec.remit_method_code,
l_batch_rec.remittance_bank_branch_id,
l_batch_rec.batch_applied_status );
FND_MESSAGE.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
FND_MESSAGE.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
fnd_message.set_token('PROCEDURE', 'INSERT_REMIT_BATCH');
UPDATE ar_batch_sources
SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
WHERE batch_source_id = l_batch_rec.batch_source_id;
SELECT NVL( LAST_BATCH_NUM, 0 )
INTO l_batch_rec.name
FROM ar_batch_sources
WHERE batch_source_id = l_batch_rec.batch_source_id;
arp_cr_batches_pkg.insert_p( l_batch_rec,
p_row_id,
p_batch_id );
arp_util.debug( 'arp_rw_batches_pkg.insert_remit_batch()-' );
arp_util.debug( 'EXCEPTION: arp_rw_batches_pkg.insert_remit_batch' );
END insert_remit_batch;
| validate_args_insert_remit |
| |
| DESCRIPTION |
| Validate arguments passed to insert_remit_remit 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_insert_remit(
p_row_id IN VARCHAR2,
p_batch_id IN ar_batches.batch_id%TYPE,
p_type IN ar_batches.type%TYPE,
p_set_of_books_id IN ar_batches.set_of_books_id%TYPE,
p_currency_code IN ar_batches.currency_code%TYPE,
p_gl_date IN ar_batches.gl_date%TYPE,
p_batch_date IN ar_batches.batch_date%TYPE,
p_remit_method_code IN ar_batches.remit_method_code%TYPE,
p_remittance_bank_branch_id IN
ar_batches.remittance_bank_branch_id%TYPE,
p_batch_applied_status IN ar_batches.batch_applied_status%TYPE ) IS
l_field VARCHAR2(30);
arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_remit()+' );
FND_MESSAGE.set_token('PROCEDURE', 'VALIDATE_ARGS_INSERT_REMIT');
arp_util.debug( 'arp_rw_batches_pkg.validate_args_insert_remit()-' );
arp_util.debug('validate_args_insert_remit: ' ||
'EXCEPTION: arp_rw_batches_pkg.validate_args_insert_remit' );
END validate_args_insert_remit;
PROCEDURE delete_batch( p_batch_id IN ar_batches.batch_id%TYPE ) IS
l_id NUMBER;
SELECT batch_id
INTO l_id
FROM ar_batches
WHERE batch_id = p_batch_id
FOR UPDATE;
UPDATE ar_payment_schedules
SET selected_for_receipt_batch_id = NULL
WHERE selected_for_receipt_batch_id = p_batch_id;
ARP_CR_BATCHES_PKG.delete_p(p_batch_id);
arp_standard.debug('EXCEPTION: arp_rw_batches_pkg.delete_batch');
END delete_batch;
SELECT bs.batch_source_id,
bs.auto_batch_numbering,
bs.default_receipt_class_id,
bs.default_receipt_method_id,
bs.default_remit_bank_account_id,
bs.org_id
FROM ar_batch_sources bs
WHERE bs.name = p_batch_source_name
AND bs.type <> 'AUTOMATIC'
AND p_batch_date BETWEEN bs.start_date_active AND NVL(bs.end_date_active,p_batch_date);
/*SELECT bs.batch_source_id,
bs.auto_batch_numbering,
bs.default_receipt_class_id,
bs.default_receipt_method_id,
bs.default_remit_bank_account_id
INTO l_batch_source_id,
l_batch_number,
l_rec_class_id,
l_pay_method_id,
l_bank_account_id
FROM ar_batch_sources bs
WHERE bs.name = p_batch_source_name
AND bs.type <> 'AUTOMATIC'
AND p_batch_date BETWEEN bs.start_date_active AND NVL(bs.end_date_active,p_batch_date);*/
SELECT
bb.bank_name,
cba.bank_account_num,
cba.currency_code,
bb.bank_branch_name,
bb.branch_party_id,
rma.override_remit_account_flag
INTO
l_bank_name,
l_bank_account_num,
l_currency_code,
l_bank_branch_name,
l_bank_branch_id,
l_override_remit_flag
FROM ce_bank_accounts cba,
ce_bank_branches_v bb,
ar_receipt_method_accounts rma,
ce_bank_acct_uses_ou_v ba
WHERE cba.bank_account_id = l_bank_account_id
AND rma.receipt_method_id = l_pay_method_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND cba.bank_account_id = ba.bank_account_id
AND cba.bank_branch_id = bb.branch_party_id
AND p_batch_date between rma.start_date and NVL(rma.end_date,p_batch_date)
AND to_Date(p_batch_date,'DD/MM/RRRR') <
NVL(cba.end_date,to_date(p_batch_date,'DD/MM/RRRR')+1)
AND p_batch_date <=NVL(bb.end_date,p_batch_date)
AND ROWNUM <=1;
SELECT
rc.name,
rm.name,
rc.creation_status,
l.meaning,
rc.remit_flag
INTO
l_rec_class_name,
l_pay_method_name,
l_creation_status,
l_meaning,
l_remit_flag
FROM ar_receipt_classes rc,
ar_receipt_methods rm,
ar_lookups l
WHERE rc.receipt_class_id=l_rec_class_id
AND rc.receipt_class_id = rm.receipt_class_id
AND rm.receipt_method_id = l_pay_method_id
AND p_batch_date between rm.start_date and NVL(rm.end_date,p_batch_date)
AND rc.creation_status = l.lookup_code(+)
AND l.lookup_type(+) = 'RECEIPT_CREATION_STATUS'
AND EXISTS
(SELECT
rma.receipt_method_id
FROM ce_bank_accounts cba,
ce_bank_branches_v bb,
ar_receipt_method_accounts rma,
ce_bank_acct_uses_ou_v ba
WHERE rma.receipt_method_id = l_pay_method_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND cba.bank_account_id = ba.bank_account_id
AND (cba.currency_code=NVL(l_currency_code,p_currency_code)
OR cba.receipt_multi_currency_flag='Y')
AND bb.branch_party_id = cba.bank_branch_id
AND p_batch_date between rma.start_date and NVL(rma.end_date,p_batch_date)
AND to_date(p_batch_date,'DD/MM/RRRR') <
NVL(cba.end_date,to_date(p_batch_date,'DD/MM/RRRR')+1)
AND p_batch_date <=NVL(bb.end_date,p_batch_date))
AND ROWNUM <=1;
SELECT
rc.name,
rc.creation_status,
l.meaning,
rc.remit_flag
INTO
l_rec_class_name,
l_creation_status,
l_meaning,
l_remit_flag
FROM ar_receipt_classes rc,
ar_lookups l
WHERE rc.receipt_class_id=l_rec_class_id
AND rc.creation_status = l.lookup_code(+)
AND l.lookup_type(+) = 'RECEIPT_CREATION_STATUS'
AND EXISTS
(SELECT
rm.receipt_class_id
FROM ar_receipt_methods rm,
ce_bank_accounts cba,
ce_bank_branches_v bb,
ar_receipt_method_accounts rma,
ce_bank_acct_uses ba
WHERE rm.receipt_class_id = l_rec_class_id
AND rm.receipt_method_id = rma.receipt_method_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND cba.bank_account_id = ba.bank_account_id
AND (cba.currency_code=NVL(l_currency_code,p_currency_code) OR
cba.receipt_multi_currency_flag='Y')
AND bb.branch_party_id = cba.bank_branch_id
AND p_batch_date between rm.start_date and
NVL(rm.end_date,p_batch_date)
AND p_batch_date between
rma.start_date and NVL(rma.end_date,p_batch_date)
AND to_Date(p_batch_date,'DD/MM/RRRR') <
NVL(cba.end_date,to_date(p_batch_date,'DD/MM/RRRR')+1)
AND p_batch_date <=NVL(bb.end_date,p_batch_date))
AND ROWNUM <=1;
/* Assign the values fetched from Various select stmts to parameter variables */
IF p_batch_source_name IS NOT NULL THEN
p_batch_number:=l_batch_number;