[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insert_ps_rec_cash |
| |
| DESCRIPTION |
| Inserts a payment schedule record for a cash receipt |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 8-SEP-95 OSTEINME created |
| 12-JUL-96 OSTEINME now populates gl_date_closed with 12-31-4712 |
| to avoid problems if DB trigger is not |
| installed. Same for actual_date_closed. |
| |
+===========================================================================*/
Procedure insert_ps_rec_cash(
p_cr_rec IN ar_cash_receipts%ROWTYPE,
p_gl_date IN DATE,
p_maturity_date IN DATE,
p_acctd_amount IN
ar_payment_schedules.acctd_amount_due_remaining%TYPE,
p_ps_id OUT NOCOPY
ar_payment_schedules.payment_schedule_id%TYPE
) IS
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_standard.debug('arp_process_rct_util.insert_ps_rec_cash()+');
select status
into l_status
from ar_cash_receipt_history
where cash_receipt_id = p_cr_rec.cash_receipt_id
and current_record_flag = 'Y' ;
arp_ps_pkg.insert_p(l_ps_rec, l_ps_id);
arp_standard.debug('arp_process_rct_util.insert_ps_rec_cash()-');
arp_standard.debug('EXCEPTION: arp_process_rct_util.insert_ps_rec_cash()');
END insert_ps_rec_cash;
| insert_crh_rec |
| |
| DESCRIPTION |
| Creates a new record in AR_CASH_RECEIPT_HISTORY for a new cash or |
| misc receipt. |
| |
| SCOPE - PRIVATE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 08-SEP-95 OSTEINME created |
| 04-NOV-96 OSTEINME modified for Japan enhancements: |
| added new parameters for bank charges |
| |
+===========================================================================*/
PROCEDURE insert_crh_rec(
p_cr_rec IN ar_cash_receipts%ROWTYPE,
p_crh_amount IN ar_cash_receipt_history.amount%TYPE,
p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
p_factor_discount_amount IN
ar_cash_receipt_history.factor_discount_amount%TYPE,
p_acctd_factor_discount_amount IN
ar_cash_receipt_history.acctd_factor_discount_amount%TYPE,
p_gl_date IN DATE,
p_creation_status IN VARCHAR2,
p_batch_id IN ar_cash_receipt_history.batch_id%TYPE,
p_ccid IN
ar_cash_receipt_history.account_code_combination_id%TYPE,
p_bank_charges_ccid IN
ar_cash_receipt_history.bank_charge_account_ccid%TYPE,
p_crh_rec OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
p_called_from IN VARCHAR2 DEFAULT NULL
) IS
l_crh_rec ar_cash_receipt_history%ROWTYPE;
arp_standard.debug('arp_process_rct_util.insert_crh_rec()+');
l_crh_rec.program_update_date := NULL;
arp_cr_history_pkg.insert_p(l_crh_rec, l_crh_id);
arp_standard.debug('arp_process_rct_util.insert_crh_rec()-');
arp_standard.debug('EXCEPTION: arp_process_rct_util.insert_crh_rec');
| insert_ra_rec_cash |
| |
| DESCRIPTION |
| Creates a new record in AR_RECEIVABLE_APPLICATIONS table for a cash |
| receipt. |
| |
| SCOPE : PRIVATE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 11-SEP-95 OSTEINME created |
| 13-MAY-97 KLAWRANC Bug fix #487513. |
| Added parameter p_reversal_gl_date. Reversal |
| GL Date must be set for all reversed |
| receivable applications records. Parameter |
| defaults to NULL, so that other packages that|
| call this procedure do not need to be |
| modified. |
| 03-SEP-97 OSTEINME Bug 547165: Changed |
| %type to %rowtype |
| |
| 14-APR-2000 Jani Rautiainen Added parameter p_called_from. This is needed|
| in the logic to decide whether first UNAPP |
| row is postable or not. In BR scenario when |
| Activity Application of Short Term Debt is |
| created the UNAPP rows are not postable. |
| This is an user requirement for BR. |
| The parameter is defaulted to NULL so no |
| impact for the existing functionality. |
| 03-Sep-2002 Debbie Jancis Modified for MRC trigger replacement |
| Added calls to AR_MRC_ENGINE3 for |
| for processing inserts into |
| AR_RECEIVABLE_APPLICATIONS |
| |
+===========================================================================*/
PROCEDURE insert_ra_rec_cash(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_amount IN ar_cash_receipts.amount%TYPE,
p_apply_date IN DATE,
p_status IN ar_cash_receipts.status%TYPE,
p_acctd_amount IN
ar_receivable_applications.acctd_amount_applied_from%TYPE,
p_gl_date IN DATE,
p_ccid IN
ar_receivable_applications.code_combination_id%TYPE,
p_payment_schedule_id IN
ar_payment_schedules.payment_schedule_id%TYPE,
p_application_rule IN ar_receivable_applications.application_rule%TYPE,
p_reversal_gl_date IN DATE default null,
p_ra_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
p_called_from IN VARCHAR2 DEFAULT NULL -- jrautiai BR project
) IS
l_ra_rec ar_receivable_applications%ROWTYPE;
arp_standard.debug('arp_process_rct_util.insert_ra_rec_cash()+');
arp_app_pkg.insert_p(l_ra_rec, l_ra_id);
arp_standard.debug('arp_process_rct_util.insert_ra_rec_cash()-');
END; -- insert_ra_rec_cash()
| insert_dist_rec |
| |
| DESCRIPTION |
| inserts ar_distributions record for a cash/misc receipt |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 19-AUG-95 OSTEINME created |
| 05-Jan-98 DJancis added p_cr_id so we can get additional info |
| required for 11.5 VAT changes |
| |
+===========================================================================*/
PROCEDURE insert_dist_rec(
p_amount IN ar_cash_receipts.amount%TYPE,
p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
p_crh_id IN
ar_cash_receipt_history.cash_receipt_history_id%TYPE,
p_source_type IN ar_distributions.source_type%TYPE,
p_ccid IN ar_distributions.code_combination_id%TYPE,
p_called_from IN VARCHAR2 DEFAULT NULL -- jrautiai BR project
) IS
l_dist_rec ar_distributions%ROWTYPE;
arp_standard.debug('arp_process_rct_util.insert_dist_rec()+');
arp_distributions_pkg.insert_p(l_dist_rec, l_dummy);
/* need to insert records into the MRC table. Calling new
mrc engine */
--{BUG4301323
-- ar_mrc_engine2.maintain_mrc_data2(
-- p_event_mode => 'INSERT',
-- p_table_name => 'AR_DISTRIBUTIONS',
-- p_mode => 'SINGLE',
-- p_key_value => l_dist_rec.line_id,
-- p_row_info => l_dist_rec);
arp_standard.debug('arp_process_rct_util.insert_dist_rec()-');
END; -- insert_dist_rec()
SELECT ROUND(100 - sum(mcd.percent),3)
INTO l_rounding_diff
FROM ar_misc_cash_distributions mcd
WHERE mcd.cash_receipt_id = p_cash_receipt_id;
UPDATE ar_misc_cash_distributions mcd
SET mcd.percent = mcd.percent + l_rounding_diff
WHERE cash_receipt_id = p_cash_receipt_id
AND ROWNUM =1
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
p_event_mode => 'UPDATE',
p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
| insert_misc_dist |
| |
| DESCRIPTION |
| Inserts distributions for miscellaneous transactions |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 21-SEP-95 OSTEINME created |
| 28-SEP-98 K.Murphy Bug #705078. Added code to validate the key |
| flex before the MISC distribution is |
| created. Checks both the enabled flag and |
| From/To dates. |
| 30-SEP-98 K.Murphy Cash Management Enhancement: Allow creation |
| of Misc Receipts with distribution set. |
| Added p_created_from parameter and included |
| "default NULL" for p_distribution_set_id. |
| Modified code so that the distribution set |
| is selected along with the cc id (as in the |
| form you can no longer choose a different |
| distribution set for a given activity. |
| 03-Mar-05 JASSING Added the code to check for the profile |
| option 'AR:Disable Receivable Activity |
| Balancing Segment Substitution' for the |
| misc receipt creation during Credit Card |
| Refunds. Bug fix 4025652. |
+===========================================================================*/
PROCEDURE insert_misc_dist(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
p_amount IN ar_cash_receipts.amount%TYPE,
p_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
p_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
p_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE default NULL,
p_ussgl_trx_code IN ar_cash_receipts.ussgl_transaction_code%TYPE default NULL,
p_created_from IN ar_misc_cash_distributions.created_from%TYPE default 'ARRERCT'
) IS
l_trx_code_combination_id ar_receivables_trx.code_combination_id%TYPE;
SELECT cash_receipt_history_id
FROM ar_cash_receipt_history
WHERE current_record_flag = 'Y'
AND cash_receipt_id = p_cash_receipt_id;
arp_standard.debug('arp_process_receipts.insert_misc_dist()+');
DELETE ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
p_event_mode => 'DELETE',
p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
SELECT rt.code_combination_id,
rt.default_acctg_distribution_set,
rt.type
INTO l_trx_code_combination_id,
l_distribution_set_id,
l_type
FROM ar_receivables_trx rt
WHERE rt.type in
('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND','CC_CHARGEBACK')
AND NVL( rt.status, 'A' ) = 'A'
AND rt.RECEIVABLES_TRX_ID = p_receivables_trx_id;
select account_code_combination_id
into l_crh_ccid
from ar_cash_receipt_history
where cash_receipt_id = p_cash_receipt_id
and current_record_flag = 'Y';
arp_misc_cash_dist_pkg.insert_p(l_misc_cash_dist_rec, l_dummy);
arp_standard.debug('arp_process_receipts.insert_misc_dist()-');
END; -- arp_process_receipts.insert_misc_dist()
| update_misc_dist |
| |
| DESCRIPTION |
| updates distribution in ar_misc_cash_distributions |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 22-SEP-95 OSTEINME created |
| 30-MAY-01 MRAMANAT Added an ELSIF condition to handle Case# 6 |
| to fix bug 1792989 |
| |
+===========================================================================*/
PROCEDURE update_misc_dist(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_amount IN ar_cash_receipts.amount%TYPE,
p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
p_amount_changed_flag IN BOOLEAN,
p_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE,
p_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
p_old_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE,
p_old_receivables_trx_id IN ar_cash_receipts.receivables_trx_id%TYPE,
p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
p_gl_date_changed_flag IN BOOLEAN,
p_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
p_receipt_date_changed_flag IN BOOLEAN,
p_gl_tax_acct IN ar_distributions.code_combination_id%TYPE
) IS
l_trx_code_combination_id ar_receivables_trx.code_combination_id%TYPE;
arp_standard.debug('arp_process_receipts.update_misc_dist()+');
arp_util.debug('update_misc_dist: ' || '=====> Case 8: amount is zero -- deleting applications');
SELECT count(mcd.misc_cash_distribution_id)
INTO l_count
FROM ar_misc_cash_distributions mcd
WHERE mcd.cash_receipt_id = p_cash_receipt_id
AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
AND mcd.posting_control_id = -3 --Not posted
AND EXISTS (SELECT 'x'
FROM ar_distributions ard
WHERE ard.source_id = mcd.misc_cash_distribution_id
AND ard.source_table = 'MCD');
arp_standard.debug('update_misc_dist: ' || ' l_count ' || TO_CHAR(l_count));
SELECT distinct posting_control_id
INTO l_posted
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND current_record_flag = 'Y';
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
/*DELETE ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list; */
p_event_mode => 'DELETE',
p_table_name => 'AR_MISC_CASH_DISTRIBTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
SELECT rt.code_combination_id, rt.default_acctg_distribution_set
INTO l_old_trx_code_combination_id, l_old_default_distribution_set
FROM ar_receivables_trx rt
WHERE rt.type in
('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND', 'CC_CHARGEBACK')
-- AND ( (NVL( rt.status, 'A' ) = 'A') or (rt.end_date_active=p_receipt_date))
AND rt.RECEIVABLES_TRX_ID = p_old_receivables_trx_id;
SELECT 'x' INTO dummy from gl_code_combinations
where code_combination_id = l_old_trx_code_combination_id
AND ENABLED_FLAG='Y';
SELECT rt.code_combination_id, rt.default_acctg_distribution_set
INTO l_trx_code_combination_id, l_default_distribution_set
FROM ar_receivables_trx rt
WHERE rt.type in
('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND' , 'CC_CHARGEBACK')
-- AND ((NVL( rt.status, 'A' ) = 'A') or (rt.end_date_active=p_receipt_date))
AND rt.RECEIVABLES_TRX_ID = p_receivables_trx_id;
SELECT 'x' INTO dummy from gl_code_combinations
where code_combination_id = l_trx_code_combination_id
AND ENABLED_FLAG='Y';
arp_standard.debug('update_misc_dist: ' || 'l_new_recs_auto_flag = TRUE');
arp_standard.debug('update_misc_dist: ' || 'l_new_recs_auto_flag = FALSE');
arp_standard.debug('update_misc_dist: ' || 'l_old_recs_auto_flag = TRUE');
arp_standard.debug('update_misc_dist: ' || 'l_old_recs_auto_flag = FALSE');
arp_standard.debug('update_misc_dist: ' || 'p_distribution_set_id = ' ||
to_char(p_distribution_set_id));
arp_standard.debug('update_misc_dist: ' || 'p_old_distribution_set_id = ' ||
to_char(p_old_distribution_set_id));
arp_standard.debug('update_misc_dist: ' || 'l_trx_code_combination_id = ' ||
to_char(l_trx_code_combination_id));
arp_standard.debug('update_misc_dist: ' || 'l_old_trx_code_combination_id = ' ||
to_char(l_old_trx_code_combination_id));
arp_util.debug('update_misc_dist: ' || '=====> Case 1/7: updating distributions with new date(s)');
UPDATE ar_misc_cash_distributions
SET -- gl_date = p_gl_date,
apply_date = p_receipt_date
WHERE cash_receipt_id = p_cash_receipt_id
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
p_event_mode => 'UPDATE',
p_table_name => 'AR_MISC_CASH_DISTRIBTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
arp_util.debug('update_misc_dist: ' || '=====> Case 1/7: no update required');
affected and only the amounts should be updated in
AR_MISC_CASH_DISTRIBUTIONS. Also Accounting engine is called to
recreate accounting in AR_DISTRIBUTIONS for the new amounts.
This is done by
1. Calling the Accounting engine to delete the MCD records
from AR_DISTRIBUTIONS for the passed cash_receipt_id.
2. Update the amount in AR_MISC_CASH_DISTRIBUTIONS by calling
procedure update_manual_dist.
3. Calling the Accounting engine to recreate the MCD records
taking the new amount into consideration.
*/
/* bug 3324670 : modified the below ELSIF */
ELSIF (p_amount_changed_flag = TRUE AND
(l_old_recs_auto_flag = TRUE AND
l_new_recs_auto_flag = TRUE AND
(p_distribution_set_id = p_old_distribution_set_id OR
l_trx_code_combination_id = l_old_trx_code_combination_id)) OR
(l_old_recs_auto_flag = FALSE AND
l_new_recs_auto_flag = FALSE)) THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('update_misc_dist: ' || '=====> Case 2: updating amounts');
SELECT count(mcd.misc_cash_distribution_id)
INTO l_count
FROM ar_misc_cash_distributions mcd
WHERE mcd.cash_receipt_id = p_cash_receipt_id
AND mcd.reversal_gl_date IS NULL
AND mcd.posting_control_id = -3
AND EXISTS (SELECT 'x'
FROM ar_distributions ard
WHERE ard.source_id = mcd.misc_cash_distribution_id
AND ard.source_table = 'MCD');
SELECT distinct posting_control_id
INTO l_posted
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND current_record_flag = 'Y';
arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () +');
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () -');
update_manual_dist( p_cash_receipt_id,
p_amount,
p_acctd_amount,
p_exchange_rate,
p_currency_code,
p_gl_date,
p_receipt_date );
SELECT distinct posting_control_id
INTO l_posted
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND current_record_flag = 'Y';
arp_standard.debug('update_misc_dist: ' || ' Create Acct Entry');
arp_util.debug('update_misc_dist: ' || '=====> Case 2: updating amounts');
update_manual_dist( p_cash_receipt_id,
p_amount,
p_acctd_amount,
p_exchange_rate,
p_currency_code,
p_gl_date,
p_receipt_date );
arp_util.debug('update_misc_dist: ' || 'Case 3: deleting old records');
DELETE ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id;
arp_util.debug('update_misc_dist: ' || '=====> Case 3: do nothing');
arp_standard.debug('update_misc_dist: ' || ' =====> CASE 4, 5, 6');
SELECT count(mcd.misc_cash_distribution_id)
INTO l_count
FROM ar_misc_cash_distributions mcd
WHERE mcd.cash_receipt_id = p_cash_receipt_id
AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate
AND mcd.posting_control_id = -3 --Not posted
AND EXISTS (SELECT 'x'
FROM ar_distributions ard
WHERE ard.source_id = mcd.misc_cash_distribution_id
AND ard.source_table = 'MCD');
arp_standard.debug('update_misc_dist: ' || ' l_count ' || TO_CHAR(l_count));
SELECT distinct posting_control_id
INTO l_posted
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND current_record_flag = 'Y';
arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () +');
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
arp_standard.debug('update_misc_dist: ' || 'Delete Misc Cash Receipt start () -');
insert_misc_dist( p_cash_receipt_id,
p_gl_date,
p_amount,
p_currency_code,
p_exchange_rate,
p_acctd_amount,
p_receipt_date,
p_receivables_trx_id,
p_distribution_set_id);
SELECT distinct posting_control_id
INTO l_posted
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND current_record_flag = 'Y';
arp_standard.debug('update_misc_dist: ' || ' Create Acct Entry');
arp_standard.debug('arp_process_receipts.update_misc_dist()-');
END update_misc_dist;
| this. Changed the insert from "insert as |
| select" to using a cursor. |
| |
+===========================================================================*/
PROCEDURE create_mcd_recs(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_amount IN ar_cash_receipts.amount%TYPE,
p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
p_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
p_receipt_date IN ar_cash_receipts.receipt_date%TYPE,
p_distribution_set_id IN ar_cash_receipts.distribution_set_id%TYPE,
p_ussgl_trx_code IN ar_cash_receipts.ussgl_transaction_code%TYPE
) IS
l_min_unit NUMBER;
SELECT
dist_code_combination_id,
DECODE(p_amount,0,ROUND(percent_distribution,3),
ROUND
(
ROUND((percent_distribution/100.0) * p_amount,3) * 100/
p_amount,
3
)) percent,
decode
(
l_min_unit, null,
round(p_amount * percent_distribution/100,
l_precision),
round(p_amount * (percent_distribution/100)/l_min_unit)
* l_min_unit
) amount,
decode
(
arp_global.base_min_acc_unit, null,
round((p_amount * percent_distribution/100) * nvl(p_exchange_rate,1),
arp_global.base_precision),
round(p_amount * (percent_distribution/100) * nvl(p_exchange_rate,1)
/ arp_global.base_precision) * arp_global.base_precision
) acctd_amount
FROM
ar_distribution_set_lines
WHERE
distribution_set_id = p_distribution_set_id;
SELECT cash_receipt_history_id
FROM ar_cash_receipt_history
WHERE current_record_flag = 'Y'
AND cash_receipt_id = p_cash_receipt_id;
SELECT minimum_accountable_unit,precision
INTO l_min_unit, l_precision
FROM fnd_currencies
WHERE currency_code = p_currency_code;
SELECT ar_misc_cash_distributions_s.nextval
INTO l_misc_cash_dist_id
FROM DUAL;
INSERT INTO ar_misc_cash_distributions (
misc_cash_distribution_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
cash_receipt_id,
gl_date,
apply_date,
code_combination_id,
percent,
amount,
set_of_books_id,
acctd_amount,
posting_control_id,
created_from,
ussgl_transaction_code,org_id,
cash_receipt_history_id)
VALUES
(
l_misc_cash_dist_id,
arp_global.user_id,
arp_global.last_update_date,
arp_global.created_by,
arp_global.creation_date,
p_cash_receipt_id,
trunc(p_gl_date),
p_receipt_date,
dist.dist_code_combination_id,
dist.percent,
dist.amount,
arp_global.set_of_books_id,
dist.acctd_amount,
-3,
'ARRERCT',
p_ussgl_trx_code, arp_standard.sysparm.org_id,
l_crh_id);
p_event_mode => 'INSERT',
p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_misc_cash_dist_id );
SELECT NVL(p_amount, 0) -
NVL(SUM
(
decode
(
l_min_unit, null,
round(p_amount * percent_distribution/100,
l_precision),
round(p_amount*(percent_distribution/100)/l_min_unit)
*l_min_unit
)
),0)
,
NVL(p_acctd_amount, 0) -
NVL(SUM
(
decode
(
arp_global.base_min_acc_unit, null,
round((p_amount * percent_distribution/100)
* nvl(p_exchange_rate,1),
arp_global.base_precision),
round(p_amount * (percent_distribution/100)
* nvl(p_exchange_rate ,1) /
arp_global.base_precision)
* arp_global.base_precision
)
),0)
INTO l_rounding_diff,
l_acctd_rounding_diff
FROM ar_distribution_set_lines
WHERE distribution_set_id = p_distribution_set_id;
UPDATE ar_misc_cash_distributions
SET amount = amount + l_rounding_diff,
acctd_amount = acctd_amount + l_acctd_rounding_diff,
percent = ROUND (
(amount + l_rounding_diff)*100/p_amount,
3 )
WHERE cash_receipt_id = p_cash_receipt_id
AND ROWNUM = 1
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
p_event_mode => 'UPDATE',
p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
| update_manual_dist |
| |
| DESCRIPTION |
| creates distribution in ar_misc_cash_distributions based on a pre- |
| defined distribution set. This function also takes care of possible |
| rounding errors. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 22-SEP-95 OSTEINME created |
| |
+===========================================================================*/
PROCEDURE update_manual_dist(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_amount IN ar_cash_receipts.amount%TYPE,
p_acctd_amount IN ar_cash_receipt_history.acctd_amount%TYPE,
p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE,
p_currency_code IN ar_cash_receipts.currency_code%TYPE,
p_gl_date IN ar_cash_receipt_history.gl_date%TYPE,
p_receipt_date IN ar_cash_receipts.receipt_date%TYPE
) IS
l_min_unit NUMBER;
arp_standard.debug('arp_process_receipts.update_manual_dist()+');
SELECT minimum_accountable_unit,precision
INTO l_min_unit, l_precision
FROM fnd_currencies
WHERE currency_code = p_currency_code;
UPDATE ar_misc_cash_distributions
SET gl_date = p_gl_date,
apply_date = p_receipt_date,
amount = decode
(
l_min_unit, null,
round(p_amount * percent/100,
l_precision),
round(p_amount * (percent/100)/l_min_unit)
* l_min_unit
),
acctd_amount = decode
(
arp_global.base_min_acc_unit, null,
round((p_amount * percent/100) * nvl(p_exchange_rate,1),
arp_global.base_precision),
round(p_amount * (percent/100) * nvl(p_exchange_rate,1)
/ arp_global.base_precision) * arp_global.base_precision
),
last_updated_by = arp_global.user_id,
last_update_date = arp_global.last_update_date
WHERE cash_receipt_id = p_cash_receipt_id
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
p_event_mode => 'UPDATE',
p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
SELECT NVL(p_amount, 0) -
NVL(SUM
(
decode
(
l_min_unit, null,
round(p_amount * percent/100,
l_precision),
round(p_amount*(percent/100)/l_min_unit)
*l_min_unit
)
),0)
,
NVL(p_acctd_amount, 0) -
NVL(SUM
(
decode
(
arp_global.base_min_acc_unit, null,
round((p_amount * percent/100)
* nvl(p_exchange_rate,1),
arp_global.base_precision),
round(p_amount * (percent/100)
* nvl(p_exchange_rate ,1) /
arp_global.base_precision)
* arp_global.base_precision
)
),0)
INTO l_rounding_diff,
l_acctd_rounding_diff
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id;
UPDATE ar_misc_cash_distributions
SET amount = amount + l_rounding_diff,
acctd_amount = acctd_amount + l_acctd_rounding_diff,
percent = ROUND (
(amount + l_rounding_diff)*100/p_amount,
3 )
WHERE cash_receipt_id = p_cash_receipt_id
AND ROWNUM = 1
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
p_event_mode => 'UPDATE',
p_table_name => 'AR_MISC_CASH_DISTRIBUTIONS',
p_mode => 'BATCH',
p_key_value_list => l_misc_cash_key_value_list);
arp_standard.debug('arp_process_receipts.update_manual_dist()-');
END; -- update_manual_dist()
| This function inserts a record into the rate adjustments table by |
| calling the rate adjustments table handler. The INSERT will cause |
| a database trigger to fire, which will update related tables. |
| This function is being called from update_cash_receipts and from |
| update_misc_receipts. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 26-JAN-96 OSTEINME created |
| 08-AUG-97 KLAWRANC Added call to arp_rate_adj.main as the rate |
| adjustments trigger has been removed in |
| Release 11. |
| |
+===========================================================================*/
PROCEDURE rate_adjust(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_rate_adjust_gl_date IN DATE,
p_new_exchange_date IN DATE,
p_new_exchange_rate IN ar_rate_adjustments.new_exchange_rate%TYPE,
p_new_exchange_rate_type IN ar_rate_adjustments.new_exchange_rate_type%TYPE,
p_old_exchange_date IN DATE,
p_old_exchange_rate IN ar_rate_adjustments.old_exchange_rate%TYPE,
p_old_exchange_rate_type IN ar_rate_adjustments.old_exchange_rate_type%TYPE,
p_gain_loss IN ar_rate_adjustments.gain_loss%TYPE,
p_exchange_rate_attr_cat IN ar_rate_adjustments.attribute_category%TYPE,
p_exchange_rate_attr1 IN ar_rate_adjustments.attribute1%TYPE,
p_exchange_rate_attr2 IN ar_rate_adjustments.attribute2%TYPE,
p_exchange_rate_attr3 IN ar_rate_adjustments.attribute3%TYPE,
p_exchange_rate_attr4 IN ar_rate_adjustments.attribute4%TYPE,
p_exchange_rate_attr5 IN ar_rate_adjustments.attribute5%TYPE,
p_exchange_rate_attr6 IN ar_rate_adjustments.attribute6%TYPE,
p_exchange_rate_attr7 IN ar_rate_adjustments.attribute7%TYPE,
p_exchange_rate_attr8 IN ar_rate_adjustments.attribute8%TYPE,
p_exchange_rate_attr9 IN ar_rate_adjustments.attribute9%TYPE,
p_exchange_rate_attr10 IN ar_rate_adjustments.attribute10%TYPE,
p_exchange_rate_attr11 IN ar_rate_adjustments.attribute11%TYPE,
p_exchange_rate_attr12 IN ar_rate_adjustments.attribute12%TYPE,
p_exchange_rate_attr13 IN ar_rate_adjustments.attribute13%TYPE,
p_exchange_rate_attr14 IN ar_rate_adjustments.attribute14%TYPE,
p_exchange_rate_attr15 IN ar_rate_adjustments.attribute15%TYPE) IS
l_radj_rec ar_rate_adjustments%ROWTYPE;
arp_rate_adjustments_pkg.insert_p(
l_radj_rec,
l_radj_id);
arp_standard.profile.last_update_login,
TRUE,
l_crh_id_out);
| 07-JAN-98 DJANCIS selected code combination id's for earned |
| and unearnd ccids from |
| ar_receivables_trx instead of |
| ar_receipt_method_accounts for |
| 11.5 VAT changes |
| |
+===========================================================================*/
PROCEDURE get_ccids(
p_receipt_method_id IN NUMBER,
p_remittance_bank_account_id IN NUMBER,
p_unidentified_ccid OUT NOCOPY NUMBER,
p_unapplied_ccid OUT NOCOPY NUMBER,
p_on_account_ccid OUT NOCOPY NUMBER,
p_earned_ccid OUT NOCOPY NUMBER,
p_unearned_ccid OUT NOCOPY NUMBER,
p_bank_charges_ccid OUT NOCOPY NUMBER,
p_factor_ccid OUT NOCOPY NUMBER,
p_confirmation_ccid OUT NOCOPY NUMBER,
p_remittance_ccid OUT NOCOPY NUMBER,
p_cash_ccid OUT NOCOPY NUMBER
) IS
BEGIN
/* selected code combination id's for earned and unearnd ccids from
ar_receivables_trx instead of ar_receipt_method_accounts for
11.5 VAT changes */
SELECT
rma.unidentified_ccid,
rma.unapplied_ccid,
rma.on_account_ccid,
ed.code_combination_id, /* earned_ccid */
uned.code_combination_id, /* unearned_ccid */
rma.bank_charges_ccid,
rma.factor_ccid,
rma.receipt_clearing_ccid,
rma.remittance_ccid,
rma.cash_ccid
INTO
p_unidentified_ccid,
p_unapplied_ccid,
p_on_account_ccid,
p_earned_ccid,
p_unearned_ccid,
p_bank_charges_ccid,
p_factor_ccid,
p_confirmation_ccid,
p_remittance_ccid,
p_cash_ccid
FROM
AR_RECEIPT_METHOD_ACCOUNTS rma,
AR_RECEIVABLES_TRX ed,
AR_RECEIVABLES_TRX uned
WHERE remit_bank_acct_use_id = p_remittance_bank_account_id
AND receipt_method_id = p_receipt_method_id
AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
AND rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+);
SELECT payment_schedule_id
INTO l_ps_id
FROM ar_payment_schedules
WHERE cash_receipt_id = p_cash_receipt_id;
| update_dist_rec |
| |
| DESCRIPTION |
| updates a record in AR_DISTRIBUTIONS table |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 19-NOV-96 OSTEINME created |
+===========================================================================*/
PROCEDURE update_dist_rec( p_crh_id IN NUMBER,
p_source_type IN ar_distributions.source_type%TYPE,
p_amount IN NUMBER,
p_acctd_amount IN NUMBER) IS
l_dist_rec AR_DISTRIBUTIONS%ROWTYPE;
arp_distributions_pkg.update_p(l_dist_rec);
/* need to update records into the MRC table. Calling new
mrc engine */
/*4301323
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'UPDATE',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
END update_dist_rec;