The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dummy constants for use in update and lock operations |
+--------------------------------------------------------*/
--
AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
pg_program_update_date date;
pg_last_updated_by number;
pg_last_update_date date;
pg_last_update_login number;
PROCEDURE update_p( p_crh_rec IN ar_cash_receipt_history%ROWTYPE,
p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_cr_history_pkg.update_p()+' );
UPDATE ar_cash_receipt_history SET
amount = DECODE( p_crh_rec.amount,
AR_NUMBER_DUMMY, amount,
p_crh_rec.amount ),
acctd_amount = DECODE( p_crh_rec.acctd_amount,
AR_NUMBER_DUMMY, acctd_amount,
p_crh_rec.acctd_amount ),
factor_flag = DECODE( p_crh_rec.factor_flag,
AR_FLAG_DUMMY, factor_flag,
p_crh_rec.factor_flag ),
first_posted_record_flag =
DECODE( p_crh_rec.first_posted_record_flag,
AR_FLAG_DUMMY, first_posted_record_flag,
p_crh_rec.first_posted_record_flag ),
gl_date = DECODE( p_crh_rec.gl_date,
AR_DATE_DUMMY, gl_date,
p_crh_rec.gl_date ),
postable_flag = DECODE( p_crh_rec.postable_flag,
AR_FLAG_DUMMY, postable_flag,
p_crh_rec.postable_flag ),
status = DECODE( p_crh_rec.status,
AR_TEXT_DUMMY, status,
p_crh_rec.status ),
trx_date = DECODE( p_crh_rec.trx_date,
AR_DATE_DUMMY, trx_date,
p_crh_rec.trx_date ),
acctd_factor_discount_amount =
DECODE( p_crh_rec.acctd_factor_discount_amount,
AR_NUMBER_DUMMY, acctd_factor_discount_amount,
p_crh_rec.acctd_factor_discount_amount ),
account_code_combination_id =
DECODE( p_crh_rec.account_code_combination_id,
AR_NUMBER_DUMMY, account_code_combination_id,
p_crh_rec.account_code_combination_id ),
bank_charge_account_ccid =
DECODE( p_crh_rec.bank_charge_account_ccid,
AR_NUMBER_DUMMY, bank_charge_account_ccid,
p_crh_rec.amount ),
batch_id = DECODE( p_crh_rec.batch_id,
AR_NUMBER_DUMMY, batch_id,
p_crh_rec.batch_id ),
current_record_flag = DECODE( p_crh_rec.current_record_flag,
AR_FLAG_DUMMY, current_record_flag,
p_crh_rec.current_record_flag ),
exchange_date = DECODE( p_crh_rec.exchange_date,
AR_DATE_DUMMY, exchange_date,
p_crh_rec.exchange_date ),
exchange_rate = DECODE( p_crh_rec.exchange_rate,
AR_NUMBER_DUMMY, exchange_rate,
p_crh_rec.exchange_rate ),
exchange_rate_type = DECODE( p_crh_rec.exchange_rate_type,
AR_TEXT_DUMMY, exchange_rate_type,
p_crh_rec.exchange_rate_type ),
factor_discount_amount =
DECODE( p_crh_rec.factor_discount_amount,
AR_NUMBER_DUMMY, factor_discount_amount,
p_crh_rec.factor_discount_amount ),
gl_posted_date = DECODE( p_crh_rec.gl_posted_date,
AR_DATE_DUMMY, gl_posted_date,
p_crh_rec.gl_posted_date ),
posting_control_id = DECODE( p_crh_rec.posting_control_id,
AR_NUMBER_DUMMY, posting_control_id,
p_crh_rec.posting_control_id ),
reversal_cash_receipt_hist_id =
DECODE( p_crh_rec.reversal_cash_receipt_hist_id,
AR_NUMBER_DUMMY, reversal_cash_receipt_hist_id,
p_crh_rec.reversal_cash_receipt_hist_id ),
reversal_gl_date = DECODE( p_crh_rec.reversal_gl_date,
AR_DATE_DUMMY, reversal_gl_date,
p_crh_rec.reversal_gl_date ),
reversal_gl_posted_date =
DECODE( p_crh_rec.reversal_gl_posted_date,
AR_DATE_DUMMY, reversal_gl_posted_date,
p_crh_rec.reversal_gl_posted_date ),
reversal_posting_control_id =
DECODE( p_crh_rec.reversal_posting_control_id,
AR_NUMBER_DUMMY, reversal_posting_control_id,
p_crh_rec.reversal_posting_control_id ),
request_id = pg_request_id,
program_application_id =
pg_program_application_id,
program_id = pg_program_id,
program_update_date = pg_program_update_date,
last_updated_by = pg_last_updated_by,
last_update_date = pg_last_update_date,
last_update_login = pg_last_update_login,
prv_stat_cash_receipt_hist_id =
DECODE( p_crh_rec.prv_stat_cash_receipt_hist_id,
AR_NUMBER_DUMMY, prv_stat_cash_receipt_hist_id,
p_crh_rec.prv_stat_cash_receipt_hist_id ),
reversal_created_from =
DECODE( p_crh_rec.reversal_created_from,
AR_TEXT_DUMMY, reversal_created_from,
p_crh_rec.reversal_created_from ),
attribute_category = DECODE( p_crh_rec.attribute_category,
AR_TEXT_DUMMY, attribute_category,
p_crh_rec.attribute_category ),
attribute1 = DECODE( p_crh_rec.attribute1,
AR_TEXT_DUMMY, attribute1,
p_crh_rec.attribute1 ),
attribute2 = DECODE( p_crh_rec.attribute2,
AR_TEXT_DUMMY, attribute2,
p_crh_rec.attribute2 ),
attribute3 = DECODE( p_crh_rec.attribute3,
AR_TEXT_DUMMY, attribute3,
p_crh_rec.attribute3 ),
attribute4 = DECODE( p_crh_rec.attribute4,
AR_TEXT_DUMMY, attribute3,
p_crh_rec.attribute4 ),
attribute5 = DECODE( p_crh_rec.attribute5,
AR_TEXT_DUMMY, attribute5,
p_crh_rec.attribute5 ),
attribute6 = DECODE( p_crh_rec.attribute6,
AR_TEXT_DUMMY, attribute2,
p_crh_rec.attribute6 ),
attribute7 = DECODE( p_crh_rec.attribute7,
AR_TEXT_DUMMY, attribute2,
p_crh_rec.attribute7 ),
attribute8 = DECODE( p_crh_rec.attribute8,
AR_TEXT_DUMMY, attribute8,
p_crh_rec.attribute8 ),
attribute9 = DECODE( p_crh_rec.attribute9,
AR_TEXT_DUMMY, attribute9,
p_crh_rec.attribute9 ),
attribute10 = DECODE( p_crh_rec.attribute10,
AR_TEXT_DUMMY, attribute10,
p_crh_rec.attribute10 ),
attribute11 = DECODE( p_crh_rec.attribute11,
AR_TEXT_DUMMY, attribute11,
p_crh_rec.attribute11 ),
attribute12 = DECODE( p_crh_rec.attribute12,
AR_TEXT_DUMMY, attribute12,
p_crh_rec.attribute12 ),
attribute13 = DECODE( p_crh_rec.attribute13,
AR_TEXT_DUMMY, attribute13,
p_crh_rec.attribute13 ),
attribute14 = DECODE( p_crh_rec.attribute14,
AR_TEXT_DUMMY, attribute14,
p_crh_rec.attribute14 ),
attribute15 = DECODE( p_crh_rec.attribute15,
AR_TEXT_DUMMY, attribute15,
p_crh_rec.attribute15 )
WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id;
| Call central MRC library for the generic update |
| made above. This is done here rather then in |
| the generic update as the where clause changes |
| and that information is needed for the MRC engine |
+----------------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'UPDATE',
p_table_name => 'AR_CASH_RECEIPT_HISTORY',
p_mode => 'SINGLE',
p_key_value => p_crh_rec.cash_receipt_history_id
);
arp_standard.debug( 'arp_cr_history_pkg.update_p()-' );
arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.update_p' );
END update_p;
PROCEDURE insert_p(
p_crh_rec IN ar_cash_receipt_history%ROWTYPE,
p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
--
l_crh_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
arp_standard.debug('update_p: ' || 'arp_cr_history_pkg.insert_p()+' );
SELECT ar_cash_receipt_history_s.nextval
INTO l_crh_id
FROM dual;
INSERT INTO ar_cash_receipt_history (
cash_receipt_history_id,
amount,
acctd_amount,
cash_receipt_id,
factor_flag,
first_posted_record_flag,
gl_date,
postable_flag,
status,
trx_date,
acctd_factor_discount_amount,
account_code_combination_id,
bank_charge_account_ccid,
batch_id,
current_record_flag,
exchange_date,
exchange_rate,
exchange_rate_type,
factor_discount_amount,
gl_posted_date,
posting_control_id,
reversal_cash_receipt_hist_id,
reversal_gl_date,
reversal_gl_posted_date,
reversal_posting_control_id,
request_id,
program_application_id,
program_id,
program_update_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
prv_stat_cash_receipt_hist_id,
created_from,
reversal_created_from,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
,org_id
)
VALUES ( l_crh_id,
p_crh_rec.amount,
p_crh_rec.acctd_amount,
p_crh_rec.cash_receipt_id,
p_crh_rec.factor_flag,
p_crh_rec.first_posted_record_flag,
p_crh_rec.gl_date,
p_crh_rec.postable_flag,
p_crh_rec.status,
p_crh_rec.trx_date,
p_crh_rec.acctd_factor_discount_amount,
p_crh_rec.account_code_combination_id,
p_crh_rec.bank_charge_account_ccid,
p_crh_rec.batch_id,
p_crh_rec.current_record_flag,
p_crh_rec.exchange_date,
p_crh_rec.exchange_rate,
p_crh_rec.exchange_rate_type,
p_crh_rec.factor_discount_amount,
p_crh_rec.gl_posted_date,
p_crh_rec.posting_control_id,
p_crh_rec.reversal_cash_receipt_hist_id,
p_crh_rec.reversal_gl_date,
p_crh_rec.reversal_gl_posted_date,
p_crh_rec.reversal_posting_control_id,
NVL( arp_standard.profile.request_id, p_crh_rec.request_id ),
NVL( arp_standard.profile.program_application_id,
p_crh_rec.program_application_id ),
NVL( arp_standard.profile.program_id,
p_crh_rec.program_id ),
DECODE( arp_standard.profile.program_id,
NULL, NULL,
SYSDATE
),
arp_global.last_updated_by, /* FP Bug 5715840 arp_standard.profile.user_id,*/
SYSDATE,
arp_global.last_updated_by, /* FP Bug 5715840 arp_standard.profile.user_id,*/
SYSDATE,
NVL( arp_global.last_update_login,
p_crh_rec.last_update_login ),
p_crh_rec.prv_stat_cash_receipt_hist_id,
p_crh_rec.created_from,
p_crh_rec.reversal_created_from,
p_crh_rec.attribute_category,
p_crh_rec.attribute1,
p_crh_rec.attribute2,
p_crh_rec.attribute3,
p_crh_rec.attribute4,
p_crh_rec.attribute5,
p_crh_rec.attribute6,
p_crh_rec.attribute7,
p_crh_rec.attribute8,
p_crh_rec.attribute9,
p_crh_rec.attribute10,
p_crh_rec.attribute11,
p_crh_rec.attribute12,
p_crh_rec.attribute13,
p_crh_rec.attribute14,
p_crh_rec.attribute15
,arp_standard.sysparm.org_id /* SSA changes anuj */
);
| Call central MRC library for insertion |
| into MRC tables |
+-------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'INSERT',
p_table_name => 'AR_CASH_RECEIPT_HISTORY',
p_mode => 'SINGLE',
p_key_value => l_crh_id);
arp_standard.debug('update_p: ' || 'arp_cr_history_pkg.insert_p()-' );
arp_standard.debug('update_p: ' || 'EXCEPTION: arp_cr_history_pkg.insert_p' );
END insert_p;
PROCEDURE update_p( p_crh_rec IN ar_cash_receipt_history%ROWTYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_cr_history_pkg.update_p()+' );
UPDATE ar_cash_receipt_history SET
amount = p_crh_rec.amount,
acctd_amount = p_crh_rec.acctd_amount,
factor_flag = p_crh_rec.factor_flag,
first_posted_record_flag =
p_crh_rec.first_posted_record_flag,
gl_date = p_crh_rec.gl_date,
postable_flag = p_crh_rec.postable_flag,
status = p_crh_rec.status,
trx_date = p_crh_rec.trx_date,
acctd_factor_discount_amount =
p_crh_rec.acctd_factor_discount_amount,
account_code_combination_id =
p_crh_rec.account_code_combination_id,
bank_charge_account_ccid = p_crh_rec.bank_charge_account_ccid,
batch_id = p_crh_rec.batch_id,
current_record_flag = p_crh_rec.current_record_flag,
exchange_date = p_crh_rec.exchange_date,
exchange_rate = p_crh_rec.exchange_rate,
exchange_rate_type = p_crh_rec.exchange_rate_type,
factor_discount_amount = p_crh_rec.factor_discount_amount,
gl_posted_date = p_crh_rec.gl_posted_date,
posting_control_id = p_crh_rec.posting_control_id,
reversal_cash_receipt_hist_id =
p_crh_rec.reversal_cash_receipt_hist_id,
reversal_gl_date = p_crh_rec.reversal_gl_date,
reversal_gl_posted_date = p_crh_rec.reversal_gl_posted_date,
reversal_posting_control_id =
p_crh_rec.reversal_posting_control_id,
request_id = NVL( arp_standard.profile.request_id,
p_crh_rec.request_id ),
program_application_id =
NVL( arp_standard.profile.program_application_id,
p_crh_rec.program_application_id ),
program_id = NVL( arp_standard.profile.program_id,
p_crh_rec.program_id ),
program_update_date =
DECODE( arp_standard.profile.program_id,
NULL, NULL,
SYSDATE
),
last_updated_by = arp_global.last_updated_by, /* FP Bug 5715840 arp_standard.profile.user_id,*/
last_update_date = SYSDATE,
last_update_login =
NVL( arp_global.last_update_login,
p_crh_rec.last_update_login ),
prv_stat_cash_receipt_hist_id =
p_crh_rec.prv_stat_cash_receipt_hist_id,
created_from = p_crh_rec.created_from,
reversal_created_from = p_crh_rec.reversal_created_from,
attribute_category = p_crh_rec.attribute_category,
attribute1 = p_crh_rec.attribute1,
attribute2 = p_crh_rec.attribute2,
attribute3 = p_crh_rec.attribute3,
attribute4 = p_crh_rec.attribute4,
attribute5 = p_crh_rec.attribute5,
attribute6 = p_crh_rec.attribute6,
attribute7 = p_crh_rec.attribute7,
attribute8 = p_crh_rec.attribute8,
attribute9 = p_crh_rec.attribute9,
attribute10 = p_crh_rec.attribute10,
attribute11 = p_crh_rec.attribute11,
attribute12 = p_crh_rec.attribute12,
attribute13 = p_crh_rec.attribute13,
attribute14 = p_crh_rec.attribute14,
attribute15 = p_crh_rec.attribute15
WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id;
| Call central MRC library for the generic update |
| made above. This is done here rather then in |
| the generic update as the where clause changes |
| and that information is needed for the MRC engine |
+----------------------------------------------------*/
ar_mrc_engine.maintain_mrc_data(
p_event_mode => 'UPDATE',
p_table_name => 'AR_CASH_RECEIPT_HISTORY',
p_mode => 'SINGLE',
p_key_value => p_crh_rec.cash_receipt_history_id
);
arp_standard.debug( 'arp_cr_history_pkg.update_p()-' );
arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.update_p' );
END update_p;
PROCEDURE delete_p(
p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_cr_history_pkg.delete_p()+' );
DELETE FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = p_crh_id;
p_event_mode => 'DELETE',
p_table_name => 'AR_CASH_RECEIPT_HISTORY',
p_mode => 'SINGLE',
p_key_value => p_crh_id);
arp_standard.debug( 'arp_cr_history_pkg.delete_p()-' );
arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.delete_p' );
END delete_p;
PROCEDURE delete_p_cr(
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE ) IS
l_rec_hist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
arp_standard.debug( 'arp_cr_history_pkg.delete_p_cr()+' );
DELETE FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cr_id
RETURNING cash_receipt_history_id
BULK COLLECT INTO l_rec_hist_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_CASH_RECEIPT_HISTORY',
p_mode => 'BATCH',
p_key_value_list => l_rec_hist_key_value_list
);
arp_standard.debug( 'arp_cr_history_pkg.delete_p_cr()-' );
arp_standard.debug( 'EXCEPTION: arp_cr_history_pkg.delete_p_cr' );
END delete_p_cr;
SELECT cash_receipt_history_id
INTO l_crh_id
FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = p_crh_id
FOR UPDATE OF STATUS;
SELECT cash_receipt_history_id
INTO l_crh_id
FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = p_crh_id
FOR UPDATE OF status NOWAIT;
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = p_crh_id;
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cr_id AND
current_record_flag = 'Y'
FOR UPDATE OF status;
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_crh_rec.cash_receipt_id AND
current_record_flag = 'Y';
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id
FOR UPDATE OF status;
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_history_id = p_crh_rec.cash_receipt_history_id
FOR UPDATE OF status NOWAIT;
SELECT 'lock'
FROM ar_cash_receipt_history
WHERE batch_id = p_batch_id
FOR UPDATE OF status;
SELECT 'lock'
FROM ar_cash_receipt_history
WHERE batch_id = p_batch_id
FOR UPDATE OF status NOWAIT;
SELECT 'lock'
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cr_id
FOR UPDATE OF status;
SELECT 'lock'
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cr_id
FOR UPDATE OF status NOWAIT;
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_crh_rec.cash_receipt_id
AND current_record_flag = 'Y'
FOR UPDATE OF status;
SELECT *
INTO p_crh_rec
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_crh_rec.cash_receipt_id
AND current_record_flag = 'Y'
FOR UPDATE OF status NOWAIT;
SELECT *
INTO
l_new_crh_rec
FROM
ar_cash_receipt_history crh
WHERE
crh.cash_receipt_history_id = p_crh_rec.cash_receipt_history_id
AND crh.cash_receipt_id = p_crh_rec.cash_receipt_id
AND
NVL((crh.amount+NVL(crh.factor_discount_amount,0)), AR_NUMBER_DUMMY) =
NVL(
DECODE(p_crh_rec.amount,
AR_NUMBER_DUMMY, (crh.amount+NVL(crh.factor_discount_amount,0)),
p_crh_rec.amount),
AR_NUMBER_DUMMY
)
AND
NVL(crh.status , AR_TEXT_DUMMY) =
NVL(
DECODE(p_crh_rec.status ,
AR_TEXT_DUMMY, crh.status,
p_crh_rec.status),
AR_TEXT_DUMMY
)
AND NVL(crh.posting_control_id,AR_NUMBER_DUMMY) =
NVL(
DECODE(p_crh_rec.posting_control_id,
AR_NUMBER_DUMMY,crh.posting_control_id,
p_crh_rec.posting_control_id),
AR_NUMBER_DUMMY
)
AND NVL(crh.current_record_flag,AR_FLAG_DUMMY) = 'Y'
FOR UPDATE NOWAIT;
pg_program_update_date := arp_global.program_update_date;
pg_last_updated_by := arp_global.last_updated_by;
pg_last_update_date := arp_global.last_update_date;
pg_last_update_login := arp_global.last_update_login;