[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* based upon parameters coming in, we have to update specific tables
to correct the CCID. The tables will be determined by several
Columns */
/* if we are dealing with GL DIST Records */
IF (p_source_table = 'GLD') THEN
/* update the distribution record */
UPDATE RA_CUST_TRX_LINE_GL_DIST
SET code_combination_id = nvl(p_new_ccid, code_combination_id),
last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE
cust_trx_line_gl_dist_id = p_distribution_id
AND code_combination_id = p_old_ccid;
/* we need to update ar_distributions */
update AR_DISTRIBUTIONS
SET code_combination_id = NVL( p_new_ccid, code_combination_id),
last_update_date = sysdate,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE code_combination_id = p_old_ccid
and line_id = p_distribution_id;
/* We need to update the parent record if the distribution
type is ADJ */
update AR_ADJUSTMENTS
SET code_combination_id = NVL(p_new_ccid, code_combination_id),
last_update_date = sysdate,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE adjustment_id = p_parent_id
AND code_combination_id = p_old_ccid;
UPDATE AR_MISC_CASH_DISTRIBUTIONS
SET code_combination_id = NVL(p_new_ccid, code_combination_id),
last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
where misc_cash_distribution_id = p_parent_id
and code_combination_id = p_old_ccid;
UPDATE AR_CASH_RECEIPT_HISTORY
SET account_code_combination_id = NVL(p_new_ccid,
account_code_combination_id),
last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login
WHERE account_code_combination_id = p_old_ccid
AND cash_Receipt_history_id = p_parent_id
AND current_record_flag = 'Y';
/* delete from the interim table */
DELETE FROM AR_CCID_CORRECTIONS
WHERE code_combination_id = p_old_ccid
AND distribution_type = p_dist_type
AND category_type = p_category_type
AND distribution_id = p_distribution_id
AND source_table = p_source_table
AND submission_id IS NULL;
| PUBLIC PROCEDURE lock_and_update
|
| DESCRIPTION
| This procedure will take an invalid CCID and lock and update all
| rows in the ar_ccid_corrections table.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| This is called from the form ARXGLCOR.fmb
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_old_ccid IN OLD CCID
| p_new_ccid_id IN NEW CCID to be replaced.
|
| KNOWN ISSUES
| none
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 17-Nov-2003 Debbie Sue Jancis Created
*=======================================================================*/
PROCEDURE lock_and_update ( p_old_ccid IN NUMBER,
p_new_ccid IN NUMBER,
p_category_type IN VARCHAR2,
p_dist_type IN VARCHAR2,
p_seq_id IN NUMBER) IS
l_status_code CONSTANT VARCHAR2(20) := 'IN_PROGRESS';
arp_standard.debug(' ARP_CORRECT_CCID.lock_and_update()+');
Update AR_CCID_CORRECTIONS
set NEW_CODE_COMBINATION_ID = p_new_ccid,
submission_id = p_seq_id
WHERE
code_combination_id = p_old_ccid and
distribution_type = p_dist_type and
category_type = p_category_type;
arp_standard.debug(' ARP_CORRECT_CCID.lock_and_update()-');
arp_standard.debug('EXCEPTION: ARP_CORRECT_CCID.lock_and_update()');
END lock_and_update;
UPDATE RA_CUST_TRX_LINE_GL_DIST gld
SET last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login,
code_combination_id = (
SELECT nvl(new_code_combination_id, code_combination_id)
FROM ar_ccid_corrections acc
WHERE acc.submission_id = p_submission_id
AND acc.source_table = 'GLD'
AND acc.distribution_type = gld.account_class
AND acc.distribution_id = gld.cust_trx_line_gl_dist_id)
WHERE gld.cust_trx_line_gl_dist_id in (
SELECT distribution_id
FROM ar_ccid_corrections
WHERE submission_id = p_submission_id
AND source_table = 'GLD');
arp_standard.debug(' ra_cust_trx_line_gl_dist rows updated: ' ||
SQL%ROWCOUNT);
UPDATE AR_DISTRIBUTIONS ard
SET last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login,
code_combination_id = (
SELECT nvl(new_code_combination_id, code_combination_id)
FROM ar_ccid_corrections acc
WHERE acc.submission_id = p_submission_id
AND acc.distribution_id = ard.line_id
AND acc.source_table = ard.source_table
AND acc.distribution_type = ard.source_type)
WHERE ard.line_id in (
SELECT distribution_id
FROM ar_ccid_corrections
WHERE submission_id = p_submission_id
AND source_table IN ('ADJ','CRH','RA','MCD','TH'));
arp_standard.debug(' ar_distribution rows updated: ' ||
SQL%ROWCOUNT);
UPDATE AR_ADJUSTMENTS adj
SET last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login,
code_combination_id = (
SELECT nvl(new_code_combination_id, code_combination_id)
FROM ar_ccid_corrections acc
WHERE acc.submission_id = p_submission_id
AND acc.source_table = 'ADJ'
AND acc.distribution_type = 'ADJ'
AND acc.parent_id = adj.adjustment_id)
WHERE adj.adjustment_id in (
SELECT parent_id
FROM ar_ccid_corrections
WHERE submission_id = p_submission_id
AND source_table = 'ADJ'
AND distribution_type = 'ADJ');
arp_standard.debug(' ar_adjustments rows updated: ' ||
SQL%ROWCOUNT);
UPDATE AR_CASH_RECEIPT_HISTORY crh
SET last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login,
account_code_combination_id = (
SELECT nvl(new_code_combination_id, account_code_combination_id)
FROM ar_ccid_corrections acc
WHERE acc.submission_id = p_submission_id
AND acc.source_table = 'CRH'
AND acc.distribution_type = 'CASH'
AND acc.parent_id = crh.cash_receipt_history_id)
WHERE crh.cash_receipt_history_id in (
SELECT parent_id
FROM ar_ccid_corrections
WHERE submission_id = p_submission_id
AND source_table = 'CRH'
AND distribution_type = 'CASH');
arp_standard.debug(' ar_cash_receipt_history rows updated: ' ||
SQL%ROWCOUNT);
UPDATE AR_MISC_CASH_DISTRIBUTIONS mcd
SET last_update_date = SYSDATE,
last_updated_by = arp_global.last_updated_by,
last_update_login = arp_global.last_update_login,
code_combination_id = (
SELECT nvl(new_code_combination_id, code_combination_id)
FROM ar_ccid_corrections acc
WHERE acc.submission_id = p_submission_id
AND acc.source_table = 'MCD'
AND acc.distribution_type = 'MISCCASH'
AND acc.parent_id = mcd.misc_cash_distribution_id)
WHERE misc_cash_distribution_id in (
SELECT parent_id
FROM ar_ccid_corrections
WHERE submission_id = p_submission_id
AND source_table = 'MCD'
AND distribution_type = 'MISCCASH');
arp_standard.debug(' ar_misc_cash_distributions rows updated: ' ||
SQL%ROWCOUNT);