[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| validate_insert_adjustment |
| |
| DESCRIPTION |
| Validates row that is going to be inserted into ar_adjustments |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 25-AUG-95 Martin Johnson Created |
| 09-APR-96 Martin Johnson BugNo:354971. Added call to |
| arp_non_db_pkg.check_natural_ |
| application to check for overapp. |
| 18-AUG-97 Debbie Jancis Bug 715036: prevent saving of adj if |
| balances are not correct. |
| |
+===========================================================================*/
PROCEDURE validate_insert_adjustment( p_adj_amount IN number,
p_payment_schedule_id IN number,
p_type IN varchar2 ) IS
l_amount_due_original number;
arp_util.debug('arp_process_adjustment.validate_insert_adjustment()+');
SELECT ps.amount_due_original,
ps.amount_due_remaining,
ctt.creation_sign,
ctt.allow_overapplication_flag
INTO l_amount_due_original,
l_amount_due_remaining,
l_creation_sign,
l_allow_overapp_flag
FROM
ra_cust_trx_types ctt
, ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_payment_schedule_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
SELECT SUM(NVL(d.amount,0)) amount
INTO l_inv_amount
FROM ra_cust_trx_line_gl_dist d,
ar_payment_schedules p
WHERE p.payment_schedule_id = p_payment_schedule_id
AND d.customer_trx_id = p.customer_trx_id
AND d.account_class = 'UNEARN'
AND d.account_set_flag = 'N';
SELECT SUM(NVL(d.amount,0)) amount
INTO l_cm_amount
FROM ra_cust_trx_line_gl_dist d,
ra_customer_trx t,
ar_payment_schedules p
WHERE p.payment_schedule_id = p_payment_schedule_id
AND p.customer_trx_id = t.previous_customer_trx_id
AND d.customer_trx_id = t.customer_trx_id
AND d.account_class = 'UNEARN'
AND d.account_set_flag = 'N';
arp_util.debug('arp_process_adjustment.validate_insert_adjustment()-');
'EXCEPTION: arp_process_adjustment.validate_insert_adjustment()');
| validate_update_adjustment |
| |
| DESCRIPTION |
| Validates row that is going to be updateded in ar_adjustments |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 06-SEP-95 Martin Johnson Created |
| 18-APR-96 Martin Johnson BugNo:357974. Check for |
| overapplication when the adjustment |
| is approved. |
| |
+===========================================================================*/
PROCEDURE validate_update_adjustment(p_payment_schedule_id IN number,
p_adj_amount IN number,
p_type IN varchar2,
p_status_changed_flag IN boolean,
p_status IN varchar2,
p_tax_adjusted IN number )
IS
l_type_adr number;
arp_util.debug('arp_process_adjustment.validate_update_adjustment()+');
SELECT NVL(DECODE(p_type,
'CHARGES', ps.amount_due_remaining,
'INVOICE', ps.amount_due_remaining,
'FREIGHT', ps.freight_remaining,
'LINE', ps.amount_line_items_remaining,
'TAX', ps.tax_remaining ),
0 ),
NVL(DECODE(p_type,
'CHARGES', ps.amount_due_original,
'INVOICE', ps.amount_due_original,
'FREIGHT', ps.freight_original,
'LINE', ps.amount_line_items_original,
'TAX', ps.tax_original ),
0 ),
ps.tax_remaining,
ps.tax_original,
ps.amount_due_remaining,
ps.amount_due_original,
ctt.creation_sign,
ctt.allow_overapplication_flag
INTO l_type_adr,
l_type_ado,
/* VAT changes */
l_tax_remaining,
l_tax_original,
l_amount_due_remaining,
l_amount_due_original,
l_creation_sign,
l_allow_overapp_flag
FROM ar_payment_schedules ps,
ra_cust_trx_types ctt
WHERE ps.payment_schedule_id = p_payment_schedule_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
arp_util.debug('arp_process_adjustment.validate_update_adjustment()-');
'EXCEPTION: arp_process_adjustment.validate_update_adjustment()');
SELECT b.sum_orig sum_orig
,b.sum_acctd_orig sum_acctd_orig
,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',amt
,'APP_CHRG',DECODE(a.line_type,'LINE',amt,0) * -1,0))) CHRG_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',acctd_amt
,'APP_CHRG',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_CHRG_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
,'APP_FRT',DECODE(a.line_type,'LINE',amt,0) * -1,0))) FRT_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
,'APP_FRT',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_FRT_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ED_FRT',amt,0))) ED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'ED_FRT',acctd_amt,0))) ACCTD_ED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'UNED_FRT',amt,0))) UNED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'UNED_FRT',acctd_amt,0))) ACCTD_UNED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_LINE',amt
,'APP_LINE',(amt * -1)
,'ED_LINE' ,amt
,'UNED_LINE',amt -- line
,'ADJ_TAX' ,amt
,'APP_TAX' ,(amt * -1)
,'ED_TAX' ,amt
,'UNED_TAX',amt --tax
,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',amt,0) * -1)
,'APP_CHRG',(DECODE(a.line_type,'CHARGES',amt,0) * -1)
,0))) REM_TYPE_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_LINE',acctd_amt
,'APP_LINE',(acctd_amt * -1)
,'ED_LINE' ,acctd_amt
,'UNED_LINE',acctd_amt -- line
,'ADJ_TAX' ,acctd_amt
,'APP_TAX' ,(acctd_amt * -1)
,'ED_TAX' ,acctd_amt
,'UNED_TAX',acctd_amt --tax
,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',acctd_amt,0) * -1)
,'APP_CHRG',(DECODE(a.line_type,'CHARGES',acctd_amt,0) * -1)
,0))) ACCTD_REM_TYPE_LINE
,b.customer_trx_line_id CUSTOMER_TRX_LINE_ID,
ctl1.line_type line_type
FROM
(SELECT SUM( NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0) ) amt,
SUM( NVL(ard.acctd_amount_cr,0) - NVL(ard.acctd_amount_dr,0)) acctd_amt,
ard.ref_customer_trx_line_id,
ard.ref_account_class,
ard.activity_bucket,
ctl.line_type
FROM ar_distributions ard,
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
AND (ctl.customer_trx_line_id = p_customer_trx_line_id
OR
(ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
AND ctl.line_type = 'TAX'
)
)
GROUP BY
ard.ref_customer_trx_line_id,
ard.ref_account_class,
ard.activity_bucket,
ctl.line_type) a,
(SELECT SUM(ctlgd.AMOUNT) sum_orig,
SUM(ctlgd.ACCTD_AMOUNT) sum_acctd_orig,
ctlgd.customer_trx_line_id
FROM ra_cust_trx_line_gl_dist ctlgd,
ra_customer_trx_lines ctll
WHERE ctlgd.customer_trx_id = p_customer_trx_id
AND ctlgd.customer_trx_line_id = ctll.customer_trx_line_id
AND (ctll.customer_trx_line_id = p_customer_trx_line_id
OR
(ctll.link_to_cust_trx_line_id = p_customer_trx_line_id
AND ctll.line_type = 'TAX'
)
)
GROUP BY ctlgd.customer_trx_line_id) b,
ra_customer_trx_lines ctl1
WHERE a.ref_customer_trx_line_id (+) = b.customer_trx_line_id
and b.customer_trx_line_id=ctl1.customer_trx_line_id
GROUP BY b.customer_trx_line_id,
b.sum_orig,
b.sum_acctd_orig, ctl1.line_type;
SELECT customer_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_id
AND (customer_trx_line_id = p_customer_trx_line_id
OR
(link_to_cust_trx_line_id = p_customer_trx_line_id
AND line_type = 'TAX'
)
);
SELECT tax_code_source
INTO l_tax_code_source
FROM ar_receivables_trx
WHERE receivables_trx_id = p_adj_rec.receivables_trx_id;
SELECT NVL( tl.relative_amount, 1) /
NVL( t.base_amount, 1),
arpcurr.CurrRound(
(
NVL( tl.relative_amount, 1) /
NVL( t.base_amount, 1)
) *
ctl.extended_amount,
p_ps_rec.invoice_currency_code
)
INTO l_term_ratio,
l_line_original
FROM ra_terms_lines tl,
ra_terms t,
ra_customer_trx_lines ctl
WHERE p_ps_rec.term_id = t.term_id
AND t.term_id = tl.term_id
AND p_ps_rec.terms_sequence_number = tl.sequence_num
AND ctl.customer_trx_line_id =
p_adj_rec.customer_trx_line_id;
/* SELECT ctl.extended_amount
INTO l_line_original
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_line_id =
p_adj_rec.customer_trx_line_id;
/* SELECT NVL(SUM(ctl.extended_amount),0)
INTO l_line_tax
FROM ra_customer_trx_lines ctl
WHERE ctl.link_to_cust_trx_line_id=
p_adj_rec.customer_trx_line_id;
SELECT NVL(SUM(amount),0)
INTO l_sum_line_adj
FROM ar_adjustments
WHERE customer_trx_line_id = p_adj_rec.customer_trx_line_id
AND NVL(postable, 'Y') = 'Y'
AND customer_trx_id = p_adj_rec.customer_trx_id;
SELECT arpcurr.CurrRound(
NVL(
SUM( ctl.extended_amount *
l_term_ratio ),
0
),
p_ps_rec.invoice_currency_code
)
INTO l_line_credited
FROM ra_customer_trx_lines ctl
WHERE ctl.previous_customer_trx_line_id =
p_adj_rec.customer_trx_line_id;
SELECT NVL(
SUM(ra.amount_applied )
, 0
)
INTO l_line_applied
FROM ar_receivable_applications ra
WHERE applied_payment_schedule_id = p_adj_rec.payment_schedule_id
AND applied_customer_trx_id = p_adj_rec.customer_trx_id;
/* SELECT NVL(
SUM(ra.amount_applied )
, 0
)
INTO l_line_applied
FROM ar_receivable_applications ra
WHERE
ra.applied_customer_trx_id = p_adj_rec.customer_trx_id
AND ra.applied_customer_trx_line_id= p_adj_rec.customer_trx_line_id;
SELECT term_id,
terms_sequence_number,
invoice_currency_code
INTO l_ps_rec.term_id,
l_ps_rec.terms_sequence_number,
l_ps_rec.invoice_currency_code
FROM ar_payment_schedules
WHERE payment_schedule_id = p_payment_schedule_id;
| validate_update_approve_adj |
| |
| DESCRIPTION |
| Validates row that is going to be approved. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_adj_rec |
| p_ps_rec |
| p_adjustment_code |
| p_chk_approval_limits |
| OUT: |
| IN/ OUT: |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 08-SEP-95 Charlie Tomberg Created |
| |
| 03-FEB-00 Saloni Shah Made changes data model changes to |
| AR_APPROVAL_USER_LIMITS. |
| |
| 03-FEB-00 Saloni Shah Made changes for the BR/BOE project |
| A new IN parameter p_chk_approval_limits |
| was added. |
| |
+===========================================================================*/
PROCEDURE validate_update_approve_adj( p_adj_rec IN ar_adjustments%rowtype,
p_ps_rec IN ar_payment_schedules%rowtype,
p_adjustment_code IN ar_lookups.lookup_code%type,
p_chk_approval_limits IN varchar2
) IS
l_varchar_dummy VARCHAR2(128);
arp_util.debug('arp_process_adjustment.validate_update_approve_adj()+',
pg_msg_level_debug);
'validate_update_approve_adj ()',
pg_msg_level_debug);
'validate_update_approve_adj ()',
pg_msg_level_debug);
SELECT aul.amount_to,
aul.amount_from
INTO l_approval_amount_to,
l_approval_amount_from
FROM ar_approval_user_limits aul
WHERE aul.user_id = arp_adjustments_pkg.pg_user_id
AND aul.currency_code = p_ps_rec.invoice_currency_code
/* Bug 941429: Credit memo workflow added a new document_type column
to AR_APPROVAL_USER_LIMITS. Now user_id and currency_code alone can't
uniquely identify a row. Need to include document_type as well */
AND aul.document_type = 'ADJ';
'validate_update_approve_adj ()',
pg_msg_level_debug);
'validate_update_approve_adj ()',
pg_msg_level_debug);
arp_util.debug('arp_process_adjustment.validate_update_approve_adj()-',
pg_msg_level_debug);
'validate_update_approve_adj()',
pg_msg_level_debug);
arp_util.debug('---------- parameters for validate_update_approve_adj()'
|| ' ---------',
pg_msg_level_debug);
| insert_adjustment |
| |
| DESCRIPTION |
| Inserts a record into ar_adjustments |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_form_name |
| p_form_version |
| p_check_amount |
| OUT: |
| p_adjustment_number |
| p_adjustment_id |
| IN/ OUT: |
| p_adj_rec |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 24-AUG-95 Martin Johnson Created |
| 4/17/1996 Harri Kaukovuo Added special handling for |
| chargebacks. |
| 9/17/1996 Harri Kaukovuo Bug fix 394553. |
| |
| 03-FEB-00 Saloni Shah Made changes for the BR/BOE project. |
| When adjustment is reversed, then the|
| validation on the amounts is not done|
| 17-May-00 Satheesh Nambiar Added p_move_deferred_tax for BOE/BR.
| The new parameter is used to detect
| whether the deferred tax is moved as
| part of maturity_date event or as a
| part of activity on the BR(Bug 1290698)
| 13-Jun-00 Satheesh Nambiar Bug 1329091 - Passing one more |
| parameter to accounting engine |
| 25-Aug-00 SNAMBIAR Bug 1395396
| Modified the code accept $0 adjustment
| 25-Aug-00 SNAMBIAR Added a new parameter p_called_from
| for BR to pass to Accounting engine.
| Added a new parameter old_adjustment_id
| for calling Accounting engine in REVERSE
| mode.(Bug 1415964)
| 31-Jan-01 SNAMBIAR Bug 1620930 - Modified for commitment
| adjustment
| 07-Mar-01 YREDDY Bug 1686556: Modified to have the
| correct account in the distributions
| 11-JUL-02 HYU Bug 2365805: Manual charge using "Finance Charge"
| is incorrect.
| 09-AUG-05 MRAYMOND 4544013 - Implemented etax calls for
| adjustment API and forms
+===========================================================================*/
PROCEDURE insert_adjustment(p_form_name IN varchar2,
p_form_version IN number,
p_adj_rec IN OUT
ar_adjustments%rowtype,
p_adjustment_number OUT NOCOPY
ar_adjustments.adjustment_number%type,
p_adjustment_id OUT NOCOPY
ar_adjustments.adjustment_id%type,
p_check_amount IN varchar2 := FND_API.G_TRUE,
p_move_deferred_tax IN varchar2 := 'Y',
p_called_from IN varchar2 DEFAULT NULL,
p_old_adjust_id IN ar_adjustments.adjustment_id%type DEFAULT NULL,
p_override_flag IN varchar2 DEFAULT NULL,
p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION')
IS
l_adjustment_id ar_adjustments.adjustment_id%type;
arp_util.debug('ar_process_adjustment.insert_adjustment()+');
| pre-insert logic |
+--------------------*/
arp_util.debug( 'p_app_level = ' || p_app_level);
| For a reverse adjustment the validation on insert |
| for the amounts is not done. |
| The reversal of an adjustment is indicated by |
| p_check_amount flag set to 'F' |
+----------------------------------------------------*/
select nvl(gl_account_source,'NONE'),
nvl(tax_code_source,'NONE')
into l_gl_account_source,
l_tax_code_source
from ar_receivables_trx
where receivables_trx_id = p_adj_rec.receivables_trx_id;
validate_insert_adjustment( p_adj_rec.amount,
p_adj_rec.payment_schedule_id,
p_adj_rec.type );
SELECT ps.amount_due_remaining-ofc.amount+p_adj_rec.amount,ofc.amount into v_cont,v_claim_amt ---Modified for the bug 14500887
FROM ra_customer_trx rct,
ar_payment_schedules ps,
ar_receivable_applications ra,
ozf_claims ofc
WHERE rct.customer_trx_id=ps.customer_trx_id
AND ra.applied_customer_trx_id=rct.customer_trx_id
AND ofc.source_object_id = rct.customer_trx_id
AND rct.customer_trx_id = p_adj_rec.customer_trx_id
AND application_ref_type='CLAIM'
AND application_ref_num is not null
AND ps.active_claim_flag='Y';
-- This is to make arp_ps_util.update_adj_related_columns work OK
-- CB means that we are adjusting chargeback amount to applied
-- transaction.
-- This does not work the same way as normal invoice adjustment
-- because normal invoice adjustment assumes that the whole
-- full amount of amount due remaining is adjusted.
-- Chargeback can be done to be less or equal to amount due remaining.
-- ------------------------------------------------------------------
IF (p_adj_rec.type = 'CB')
THEN
/* VAT changes */
arp_ps_util.update_adj_related_columns(
null,
p_adj_rec.type,
p_adj_rec.amount,
null,
p_adj_rec.line_adjusted,
p_adj_rec.tax_adjusted,
p_adj_rec.freight_adjusted,
p_adj_rec.receivables_charges_adjusted,
p_adj_rec.apply_date,
p_adj_rec.gl_date,
l_acctd_amount,
l_ps_rec);
arp_util.debug( 'before update_adj_related_adjustment');
| arp_ps_util.update_adj_related_columns procedure. |
+-------------------------------------------------------------*/
/*-------------------------------------------------------------+
| Bug 1290698 - For partial adjustment, p_check_amount is 'F'.|
| So set the type = 'REVERSE' only when it is actual reversal |
+-------------------------------------------------------------*/
--Modified to call Accounting Engine in reverse mode while
--creating reverse adjustment with old_adjustment_id
IF (p_check_amount = FND_API.G_FALSE)
and p_adj_rec.created_from = 'REVERSE_ADJUSTMENT' THEN
l_adj_type := 'REVERSE';
arp_ps_util.update_adj_related_columns(
null,
l_adj_type,
p_adj_rec.amount,
null,
p_adj_rec.line_adjusted,
p_adj_rec.tax_adjusted,
p_adj_rec.freight_adjusted,
p_adj_rec.receivables_charges_adjusted,
p_adj_rec.apply_date,
p_adj_rec.gl_date,
l_acctd_amount,
l_ps_rec);
arp_ps_pkg.update_p(l_ps_rec );
arp_ps_util.update_adj_related_columns(
null,
null,
null,
p_adj_rec.amount,
p_adj_rec.line_adjusted,
p_adj_rec.tax_adjusted,
p_adj_rec.freight_adjusted,
p_adj_rec.receivables_charges_adjusted,
l_ps_rec.actual_date_closed,
l_ps_rec.gl_date_closed,
l_acctd_amount,
l_ps_rec);
arp_adjustments_pkg.insert_p(p_adj_rec,
l_ps_rec.exchange_rate,
p_adjustment_number,
l_adjustment_id);
the proration code to update the adjustment
and target payment schedule with the new
prorated amounts (overriding what was
passed in or written in the original PS insert */
--================== For LLCA adjustment, inserting into Activity Details Table =================
IF p_app_level = 'LINE'
THEN
SELECT ar_activity_details_s.nextval
INTO l_line_id
FROM dual;
SELECT
LINE_ADJUSTED,
TAX_ADJUSTED
INTO
l_line_adjusted,
l_tax_adjusted
FROM ar_adjustments
WHERE adjustment_id = l_adjustment_id;
INSERT INTO AR_ACTIVITY_DETAILS (
LINE_ID,
APPLY_TO,
customer_trx_line_id,
CASH_RECEIPT_ID,
GROUP_ID,
AMOUNT,
TAX,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
OBJECT_VERSION_NUMBER,
CREATED_BY_MODULE,
SOURCE_ID,
SOURCE_TABLE,
CURRENT_ACTIVITY_FLAG
)
VALUES (
l_line_id, -- line_id
1, -- APPLY_TO
p_adj_rec.customer_trx_line_id, -- customer_Trx_line_id
NULL, -- cash_Receipt_id
NULL, -- Group_ID (ll grp adj not implem)
l_line_adjusted, -- Amount
l_tax_adjusted, -- TAX
NVL(FND_GLOBAL.user_id,-1), -- Created_by
SYSDATE, -- Creation_date
decode(FND_GLOBAL.conc_login_id,
null,FND_GLOBAL.login_id,
-1, FND_GLOBAL.login_id,
FND_GLOBAL.conc_login_id), -- Last_update_login
SYSDATE, -- Last_update_date
NVL(FND_GLOBAL.user_id,-1), -- last_updated_by
0, -- object_version_number
'ARXTWADJ', -- created_by_module
l_adjustment_id, -- source_id
'ADJ', -- source_table
'Y' -- Application record status
);
routine update the adj and ps rows, the returned
prorated amounts can be ignored from this point
on. */
/* display results in debug log */
arp_util.debug('After return from arp_etax_util.prorate_recoverable');
SELECT NVL(accounting_affect_flag, 'Y')
INTO l_accounting_affect_flag
FROM ar_receivables_trx
WHERE receivables_trx_id = p_adj_rec.receivables_trx_id;
| post-insert logic |
+---------------------*/
begin
select * into l_customer_trx from ra_customer_trx
where customer_trx_id= p_adj_rec.customer_trx_id;
arp_aah_pkg.insert_p(l_aah_rec,
l_approval_action_history_id);
arp_util.debug('ar_process_adjustment.insert_adjustment()-');
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.insert_adjustment exception: '||SQLERRM );
'EXCEPTION: ar_process_adjustment.insert_adjustment()');
| update_adjustment |
| |
| DESCRIPTION |
| Updates a record in ar_adjustments |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| Adjustment amount cannot be updated in Rel 10. This procedure |
| assumes that adjustment amount will never be updated. |
| |
| MODIFICATION HISTORY |
| 06-SEP-95 Martin Johnson Created |
| 26-MAR-96 Martin Johnson BugNo:352255. Fixed so that |
| l_old_adj_rec is always fetched. |
| 9/18/1996 Harri Kaukovuo Fixed the procedure to recalculate |
| accounted adjust amount when adjustment|
| is approved. Bug fix 403019. |
| 03-FEB-00 Saloni Shah Changes made for the BR/BOE project. |
| The accounting enteries will be created|
| only if the status is 'A' and the |
| accounting_affect_flag for the |
| receivables_Trx_id is not set to 'N' |
| 17-May-00 Satheesh Nambiar Added p_move_deferred_tax for BOE/BR. |
| The new parameter is used to detect |
| whether the deferred tax is moved as |
| part of maturity_date event or as a |
| part of activity on the BR(Bug 1290698)|
| 13-Jun-00 Satheesh Nambiar Bug 1329091- Passing one more parameter|
| to accounting engine to acknowledge PS |
| updated. |
| 05-Jun-02 Rahna Kader Bug 2377672: While updating an |
| adjustment reversal, the accounting |
| entries should not be re-created |
+===========================================================================*/
PROCEDURE update_adjustment(
p_form_name IN varchar2,
p_form_version IN varchar2,
p_adj_rec IN ar_adjustments%rowtype,
p_move_deferred_tax IN varchar2 := 'Y',
p_adjustment_id IN ar_adjustments.adjustment_id%type)
IS
l_adj_rec ar_adjustments%rowtype;
/* Added local variables to be used in Update_Adjustment procedure, manishri, Bug 9708599 */
l_mode VARCHAR2(20);
arp_util.debug('ar_process_adjustment.update_adjustment()+',
pg_msg_level_debug);
| pre-update logic |
+--------------------*/
set_flags(p_adjustment_id,
l_old_adj_rec,
l_adj_rec,
l_status_changed_flag);
validate_update_adjustment(l_adj_rec.payment_schedule_id,
l_adj_rec.amount,
l_adj_rec.type,
l_status_changed_flag,
l_adj_rec.status,
l_adj_rec.tax_adjusted );
/* Added cal to Validate_Update_Approve_Adj while approving Adjustment, manishri, Bug 9708599 */
IF (l_adj_rec.status IN ('A', 'R') and l_old_adj_rec.status = 'W') THEN
validate_update_approve_adj( p_adj_rec => l_adj_rec,
p_ps_rec => l_ps_rec,
p_adjustment_code => l_adj_rec.status,
p_chk_approval_limits => FND_API.G_TRUE);
arp_ps_util.update_adj_related_columns(
null, -- payment_schedule_id
l_adj_rec.type, -- p_type
l_adj_rec.amount, -- p_amount_adjusted
l_adj_rec.amount * -1, -- p_amount_adjusted_pending
l_adj_rec.line_adjusted, -- p_line_adjusted
l_adj_rec.tax_adjusted, -- p_tax_adjusted
l_adj_rec.freight_adjusted, -- p_freight_adjusted
l_adj_rec.receivables_charges_adjusted,
l_adj_rec.apply_date, -- p_apply_date
l_adj_rec.gl_date, -- p_gl_date
l_acctd_amount_adjusted, -- p_acctd_amount_adjusted
l_ps_rec ); -- p_ps_rec
arp_ps_util.update_adj_related_columns(
null, -- paymenty schedule id
l_adj_rec.type, -- p_type
null, -- p_amount_adjusted
-1 * l_adj_rec.amount,-- p_amount_adjusted_pending
l_adj_rec.line_adjusted,
l_adj_rec.tax_adjusted,
l_adj_rec.freight_adjusted,
l_adj_rec.receivables_charges_adjusted,
l_ps_rec.actual_date_closed,
l_ps_rec.gl_date_closed,
l_acctd_amount_adjusted,
l_ps_rec );
arp_adjustments_pkg.update_p(l_adj_rec,
p_adjustment_id,
l_ps_rec.exchange_rate);
SELECT
LINE_ADJUSTED,
TAX_ADJUSTED
INTO
l_line_adjusted,
l_tax_adjusted
FROM ar_adjustments
WHERE adjustment_id = p_adjustment_id;
UPDATE AR_ACTIVITY_DETAILS
SET AMOUNT = l_line_adjusted,
TAX = l_tax_adjusted
WHERE SOURCE_TABLE = 'ADJ'
AND SOURCE_ID = p_adjustment_id
AND customer_trx_line_id = l_adj_rec.customer_trx_line_id;
SELECT NVL(accounting_affect_flag,'Y')
INTO l_accounting_affect_flag
FROM ar_receivables_trx
WHERE receivables_trx_id = l_adj_rec.receivables_trx_id;
routine update the adj and ps rows, the returned
prorated amounts can be ignored from this point
on. */
/* display results in debug log */
arp_util.debug('After return from arp_etax_util.prorate_recoverable');
update PS */
l_adj_rec.line_adjusted := l_line_amt;
Select SUM(AMOUNT) into l_amount_adjusted_pending
FROM ar_adjustments where payment_schedule_id=l_adj_rec.payment_schedule_id
AND STATUS NOT IN ('A','R','U');
UPDATE ar_payment_schedules set amount_adjusted_pending=
DECODE(l_amount_adjusted_pending,0,NULL,l_amount_adjusted_pending)
WHERE payment_schedule_id=l_adj_rec.payment_schedule_id;
'EXCEPTION: arp_ps_util.update_adj_related_columns' );
Select decode (nvl(ctt.post_to_gl,'N'),'Y', 'Y', nvl(ctt.adj_post_to_gl,'N'))
into l_adj_post_to_gl
from ra_customer_trx ct, ra_cust_trx_types ctt
where ct.customer_trx_id = l_adj_rec.customer_trx_id
and ct.cust_trx_type_id = ctt.cust_trx_type_id ;
arp_util.debug('EXCEPTION: arp_ps_util.update_adjustment '|| SQLERRM);
select /*+ INDEX(xet xla_transaction_entities_N1) */
xet.legal_entity_id legal_entity_id,
adj.SET_OF_BOOKS_ID set_of_books_id,
adj.org_id org_id,
adj.event_id event_id,
xet.entity_code entity_code,
adj.adjustment_id adjustment_id,
xet.application_id
into
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_security.security_id_int_1,
l_event_id ,
l_event_source_info.entity_type_code,
l_event_source_info.source_id_int_1,
l_event_source_info.application_id
from
ar_adjustments adj ,
xla_transaction_entities_upg xet
where adj.adjustment_id = p_adjustment_id
and adj.adjustment_id = NVL(xet.source_id_int_1, -99)
and xet.entity_code ='ADJUSTMENTS'
AND xet.application_id = 222
AND adj.SET_OF_BOOKS_ID = xet.LEDGER_ID;
xla_events_pub_pkg.update_event
(p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_event_status_code => 'N',
p_valuation_method => null,
p_security_context => l_security);
'EXCEPTION: arp_ps_util.update_adjustment' );
| VAT changes: update the accounting by first deleting the old one |
| and then creating a new one. |
| Change for the BR/BOE project has been made. |
| Accounting is created only if the status is 'A' and the |
| accounting_affect_flag of the receivables_Trx is not set to 'N' |
+-------------------------------------------------------------------*/
/*--------------------------------------------+
| Change made for BR/BOE project. |
| Get the value for accounting_affect_flag |
+--------------------------------------------*/
/* Moved this code on top. Bug 12959780.
--Bug 1277494 Added NVL to selection which was missing
BEGIN
SELECT NVL(accounting_affect_flag,'Y')
INTO l_accounting_affect_flag
FROM ar_receivables_trx
WHERE receivables_trx_id = l_adj_rec.receivables_trx_id;
If the updated record is an adjustment reversal, the accounting
entries should not be changed */
select count(*)
into l_accounts
from ar_distributions
where source_id = p_adjustment_id
and source_table = 'ADJ';
so no need to delete */
--Bug 1329091 - PS is updated before Accounting engine call
l_ae_doc_rec.pay_sched_upd_yn := 'Y';
| Call the accounting engine in delete mode for unposted adjustments|
| This is necessary as the parent adjustment has changed so a fresh |
| call is given to the accounting engine to re-create the accounting|
+-------------------------------------------------------------------*/
/*bug2636927*/
IF ( l_old_adj_rec.status NOT IN ('M', 'W')
and l_adj_rec.posting_control_id =-3
and l_accounts <> 0 --Bug 3483238
)
THEN
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
| post-update logic |
+---------------------*/
IF l_status_changed_flag
THEN
-- insert row into ar_approval_action_history
l_aah_rec.action_name := l_adj_rec.status;
arp_aah_pkg.insert_p(
l_aah_rec,
l_approval_action_history_id);
arp_util.debug('ar_process_adjustment.update_adjustment()-',
pg_msg_level_debug);
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.update_adjustment exception: '||SQLERRM );
'EXCEPTION: ar_process_adjustment.update_adjustment()',
pg_msg_level_debug);
| update_approve_adj |
| |
| DESCRIPTION |
| Deletes a record from ar_adjustments |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_form_name |
| p_form_version |
| p_adj_rec |
| p_adjustment_code |
| p_adjustment_id |
| OUT: |
| None |
| IN/ OUT: |
| None |
| |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 05-SEP-95 Charlie Tomberg Created |
| 03-FEB-00 Saloni Shah Changes for the BR/BOE project is made|
| A new p_chk_approval_limits parameter |
| is added. |
| 17-May-00 Satheesh Nambiar Added p_move_deferred_tax for BOE/BR. |
| The new parameter is used to detect |
| whether the deferred tax is moved as |
| part of maturity_date event or as a |
| part of activity on the BR(Bug 1290698)|
| 13-Jun-00 Satheesh Nambiar Bug 1329091- Passing one more parameter|
| to accounting engine to acknowledge PS |
| updated. |
| |
+===========================================================================*/
PROCEDURE update_approve_adj(p_form_name IN varchar2,
p_form_version IN number,
p_adj_rec IN ar_adjustments%rowtype,
p_adjustment_code ar_lookups.lookup_code%type,
p_adjustment_id IN ar_adjustments.adjustment_id%type,
p_chk_approval_limits IN varchar2,
p_move_deferred_tax IN varchar2 := 'Y') IS
l_ps_rec ar_payment_schedules%rowtype;
arp_util.debug('ar_process_adjustment.update_approve_adj()+',
pg_msg_level_debug);
validate_update_approve_adj( l_adj_rec,
l_ps_rec,
p_adjustment_code,
p_chk_approval_limits );
routine update the adj and ps rows, the returned
prorated amounts can be ignored from this point
on. */
/* display results in debug log */
arp_util.debug('After return from arp_etax_util.prorate_recoverable');
update PS */
l_adj_rec.line_adjusted := l_line_amt;
| update ar_payment_schedules |
+---------------------------------*/
IF ( p_adjustment_code = 'A' )
THEN
arp_ps_util.update_adj_related_columns(
null,
l_adj_rec.type,
l_adj_rec.amount,
l_adj_rec.amount * -1,
l_adj_rec.line_adjusted,
l_adj_rec.tax_adjusted,
l_adj_rec.freight_adjusted,
l_adj_rec.receivables_charges_adjusted,
l_adj_rec.apply_date,
l_adj_rec.gl_date,
l_acctd_amount_adjusted,
l_ps_rec );
arp_ps_util.update_adj_related_columns(
null,
l_adj_rec.type,
null,
-1 *
l_ps_rec.amount_adjusted_pending,
l_adj_rec.line_adjusted,
l_adj_rec.tax_adjusted,
l_adj_rec.freight_adjusted,
l_adj_rec.receivables_charges_adjusted,
l_ps_rec.actual_date_closed,
l_ps_rec.gl_date_closed,
l_acctd_amount_adjusted,
l_ps_rec );
| Update ar_adjustments |
+--------------------------*/
arp_adjustments_pkg.update_p( l_adj_rec,
p_adjustment_id,
l_ps_rec.exchange_rate );
| updated without touching its accounting |
+------------------------------------------------*/
l_xla_ev_rec.xla_from_doc_id := p_adjustment_id;
| Bug 1277494 Added NVL to selection which was missing |
| and Call Accounting Engine if accounting affect flag is not 'N' |
+------------------------------------------------------------------+*/
BEGIN
SELECT NVL(accounting_affect_flag,'Y')
INTO l_accounting_affect_flag
FROM ar_receivables_trx
WHERE receivables_trx_id = l_adj_rec.receivables_trx_id;
| Insert into ar_approval_action_history |
+-------------------------------------------*/
l_aah_rec.action_name := l_adj_rec.status;
arp_aah_pkg.insert_p(
l_aah_rec,
l_approval_action_history_id
);
arp_util.debug('ar_process_adjustment.update_approve_adj()-',
pg_msg_level_debug);
'EXCEPTION: ar_process_adjustment.update_approve_adj()',
pg_msg_level_debug);
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.update_approce_adjustment exception: '||SQLERRM );
arp_util.debug('---------- parameters for update_approve_adj()'
|| ' ---------',
pg_msg_level_debug);
| Verifies that adjustment approvals updates the relevant tables |
| correctly. This procedure should only be called during tests of |
| the update_approve_adj() procedure. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| None |
| OUT: |
| None |
| IN / OUT: |
| p_result |
| p_old_ps_rec |
| p_adj_rec |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 12-SEP-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE test_adj( p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
p_result IN OUT NOCOPY varchar2,
p_old_ps_rec IN OUT NOCOPY ar_payment_schedules%rowtype) IS
l_new_ps_rec ar_payment_schedules%rowtype;
| Verify that the adjustment was updated properly |
+---------------------------------------------------*/
p_adj_rec.acctd_amount :=
arpcurr.functional_amount(
p_adj_rec.amount,
'USD',
p_old_ps_rec.exchange_rate,
2,
null);
select decode(max(adjustment_id),
NULL, 'A: Fail, ',
'A: Pass, ')
into p_result
from ar_adjustments
where adjustment_id = p_adj_rec.adjustment_id
and adjustment_number = p_adj_rec.adjustment_number
and payment_schedule_id = p_adj_rec.payment_schedule_id
and customer_trx_id = p_adj_rec.customer_trx_id
and amount = p_adj_rec.amount
and (
(
nvl(line_adjusted,
-99.9999) = decode(p_adj_rec.type,
'LINE', p_adj_rec.amount,
-99.9999)
and nvl(tax_adjusted,
-99.9999) = decode(p_adj_rec.type,
'TAX', p_adj_rec.amount,
-99.9999)
and nvl(freight_adjusted,
-99.9999) = decode(p_adj_rec.type,
'FREIGHT', p_adj_rec.amount,
-99.9999)
and nvl(receivables_charges_adjusted ,
-99.9999) = decode(p_adj_rec.type,
'CHARGES', p_adj_rec.amount,
-99.9999)
) OR
(
(
p_adj_rec.type = 'INVOICE'
and p_adj_rec.amount = nvl(line_adjusted, 0) +
nvl(tax_adjusted, 0) +
nvl(freight_adjusted, 0) +
nvl(receivables_charges_adjusted, 0)
)
)
)
and apply_date = p_adj_rec.apply_date
and gl_date = p_adj_rec.gl_date
and code_combination_id = p_adj_rec.code_combination_id
and type = p_adj_rec.type
and adjustment_type = p_adj_rec.adjustment_type
and status = p_adj_rec.status
and nvl(customer_trx_line_id,
-999.999) = NVL(p_adj_rec.customer_trx_line_id, -999.999)
and receivables_trx_id = p_adj_rec.receivables_trx_id
and created_from = p_adj_rec.created_from
-- check the derived columns
and postable = 'Y'
and approved_by = arp_adjustments_pkg.pg_user_id
and nvl(comments, '^%') = nvl(p_adj_rec.comments, '^%')
and acctd_amount = p_adj_rec.acctd_amount;
| Verify that a row was inserted into ar_approval_action_history |
+------------------------------------------------------------------*/
select p_result ||
decode(max(approval_action_history_id),
NULL, 'H: Fail, ',
'H: Pass, ')
into p_result
from ar_approval_action_history
where adjustment_id = p_adj_rec.adjustment_id
and action_name = p_adj_rec.status
and action_date = TRUNC(sysdate)
and nvl(comments, '!@#$%') = nvl(p_adj_rec.comments, '!@#$%');
| Verify that the payment schedule was updated properly |
+---------------------------------------------------------*/
arp_ps_pkg.fetch_p(p_adj_rec.payment_schedule_id, l_new_ps_rec);
select decode( max(dummy),
null, p_result || 'P: Fail',
p_result || 'P: Pass'
)
into p_result
from dual
where
(
(l_new_ps_rec.amount_due_remaining =
p_old_ps_rec.amount_due_remaining + p_adj_rec.amount)
AND
(l_new_ps_rec.acctd_amount_due_remaining =
round(
(p_old_ps_rec.amount_due_remaining + p_adj_rec.amount)
* p_old_ps_rec.exchange_rate,
2
) )
AND
(
(
decode(p_adj_rec.type,
'LINE', l_new_ps_rec.amount_line_items_remaining,
'TAX', l_new_ps_rec.tax_remaining,
'FREIGHT', l_new_ps_rec.freight_remaining,
'CHARGES', l_new_ps_rec.receivables_charges_remaining) =
decode(p_adj_rec.type,
'LINE', p_old_ps_rec.amount_line_items_remaining,
'TAX', p_old_ps_rec.tax_remaining,
'FREIGHT', p_old_ps_rec.freight_remaining,
'CHARGES', p_old_ps_rec.receivables_charges_remaining) +
p_adj_rec.amount
)
OR
( p_adj_rec.type = 'INVOICE')
)
AND
(l_new_ps_rec.amount_adjusted =
(
nvl(p_old_ps_rec.amount_adjusted, 0) +
p_adj_rec.amount) )
AND
(l_new_ps_rec.amount_due_remaining =
nvl(l_new_ps_rec.amount_line_items_remaining,0) +
nvl(l_new_ps_rec.tax_remaining,0) +
nvl(l_new_ps_rec.freight_remaining,0) +
nvl(l_new_ps_rec.receivables_charges_remaining,0))
AND
(l_new_ps_rec.amount_due_remaining =
l_new_ps_rec.amount_due_original
+ nvl(l_new_ps_rec.amount_adjusted,0)
- nvl(l_new_ps_rec.amount_applied,0)
+ nvl(l_new_ps_rec.amount_credited,0)
- nvl(l_new_ps_rec.discount_taken_earned,0)
- nvl(l_new_ps_rec.discount_taken_unearned,0))
AND
( (l_new_ps_rec.status = 'OP' AND
l_new_ps_rec.amount_due_remaining <> 0)
OR
(l_new_ps_rec.status = 'CL' AND
l_new_ps_rec.amount_due_remaining = 0))
);
PROCEDURE val_insert_rev_actions(
p_adj_id IN ar_adjustments.adjustment_id%TYPE );
| This function reverses an adjustment by inserting an opposing entry |
| in the AR_ADJUSTMENTS table |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_aa_history_pkg.insert_p - approval history table insert table |
| handler |
| |
| ARGUMENTS : IN: |
| p_adj_id - Id of row to be reversed |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| p_module_name - Name of the module that called this proc. |
| p_module_version - Version of module that called this proc|
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY
| 04/25/95 Ganesh Vaidee Created
| 4/18/1996 Harri Kaukovuo Added RAISE clause to locking block
| Added NOWAIT to FOR UPDATE OF ... clause
| Removed hard coded comment and replaced it
| with message dictionary equivalent.
+===========================================================================*/
PROCEDURE reverse_adjustment(
p_adj_id IN ar_adjustments.adjustment_id%TYPE,
p_reversal_gl_date IN DATE,
p_reversal_date IN DATE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
l_aah_rec ar_approval_action_history%ROWTYPE;
arp_util.debug ( 'before update all other adjustments');
SELECT *
INTO l_adj_rec
FROM ar_adjustments adj
WHERE adj.adjustment_id = p_adj_id
AND adj.status not in ('A', 'R')
FOR UPDATE of adj.STATUS NOWAIT;
arp_adj_pkg.update_p( l_adj_rec );
Select decode (nvl(ctt.post_to_gl,'N'),'Y', 'Y', nvl(ctt.adj_post_to_gl,'N'))
into l_adj_post_to_gl
from ra_customer_trx ct, ra_cust_trx_types ctt
where ct.customer_trx_id = l_adj_rec.customer_trx_id
and ct.cust_trx_type_id = ctt.cust_trx_type_id ;
select xet.legal_entity_id legal_entity_id,
adj.SET_OF_BOOKS_ID set_of_books_id,
adj.org_id org_id,
adj.event_id event_id,
xet.entity_code entity_code,
adj.adjustment_id adjustment_id,
xet.application_id
into
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_security.security_id_int_1,
l_event_id ,
l_event_source_info.entity_type_code,
l_event_source_info.source_id_int_1,
l_event_source_info.application_id
from
ar_adjustments adj ,
xla_transaction_entities_upg xet
where adj.adjustment_id = p_adj_id
and adj.adjustment_id = nvl(xet.source_id_int_1,-99)
and xet.entity_code ='ADJUSTMENTS'
AND xet.application_id = 222
AND adj.SET_OF_BOOKS_ID = xet.LEDGER_ID;
xla_events_pub_pkg.update_event
(p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_event_status_code => 'N',
p_valuation_method => null,
p_security_context => l_security);
'EXCEPTION: arp_process_adjustment.reverse_adjustment:SELECT' );
arp_util.debug( 'before insert_p for ar_approval_action_history');
arp_aa_history_pkg.insert_p( l_aah_rec, l_aah_id );
/* VAT changes: pass old adjustment_id to insert_reverse_actions
to be in turn passed to accounting library */
l_adj_rec.adjustment_id := p_adj_id;
insert_reverse_actions( l_adj_rec, NULL, NULL );
| insert_reverse_actions |
| |
| DESCRIPTION |
| This procedure performs all actions to modify the passed in |
| adjustments record and calls adjustments insert table handler to |
| insert the reversed adjuetments row |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| arp_adj_pkg.insert_p - Insert a row into AR_ADJUSTMENTS table|
| |
| ARGUMENTS : IN OUT: |
| p_adj_rec - Adjustment Record structure |
| p_module_name _ Name of module that called this procedure|
| p_module_version - Version of module that called this |
| procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 7/30/1996 Harri Kaukovuo Fixed the code to fnd_seqnum, because AOL
| has changed the data type of the date parameter.
| Fixed possible bug cancidate when trying to
| select name from ar_receivables_trx into
| VARCHAR2(30) field. Name is VARCHAR2(50).
| 7/30/1996 Harri Kaukovuo Bug fix 387035
|10/16/1998 Sushama Borde Bug fix 741725: Used AOL API get_next_sequence
| instead of get_seq_name.
+===========================================================================*/
PROCEDURE insert_reverse_actions (
p_adj_rec IN OUT NOCOPY ar_adjustments%ROWTYPE,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
l_new_adj_id ar_adjustments.adjustment_id%TYPE;
arp_standard.debug( 'arp_process_adjustment.insert_reverse_actions()+');
SELECT rt.name
INTO l_rec_name
FROM ar_receivables_trx rt
WHERE rt.receivables_trx_id = p_adj_rec.receivables_trx_id;
arp_standard.debug( 'after select in insert_revers_actions in app_delete' );
arp_standard.debug( 'before ar_adjustments_pkg.insert_p in app_delete' );
arp_adj_pkg.insert_p( p_adj_rec, l_new_adj_id );
arp_standard.debug( 'after ar_adjustments_pkg.insert_p in app_delete' );
Select decode (nvl(ctt.post_to_gl,'N'),'Y', 'Y', nvl(ctt.adj_post_to_gl,'N'))
into l_adj_post_to_gl
from ra_customer_trx ct, ra_cust_trx_types ctt
where ct.customer_trx_id = p_adj_rec.customer_trx_id
and ct.cust_trx_type_id = ctt.cust_trx_type_id ;
arp_util.debug('EXCEPTION: apr_process_adjustment.insert_reverse_actions '|| SQLERRM);
select status into l_adj_status from ar_adjustments
where adjustment_id = l_new_adj_id ;
select xet.legal_entity_id legal_entity_id,
adj.SET_OF_BOOKS_ID set_of_books_id,
adj.org_id org_id,
adj.event_id event_id,
xet.entity_code entity_code,
adj.adjustment_id adjustment_id,
xet.application_id
into
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_security.security_id_int_1,
l_event_id ,
l_event_source_info.entity_type_code,
l_event_source_info.source_id_int_1,
l_event_source_info.application_id
from
ar_adjustments adj ,
xla_transaction_entities_upg xet
where adj.adjustment_id = l_new_adj_id
and adj.adjustment_id = nvl(xet.source_id_int_1,-99)
and xet.entity_code ='ADJUSTMENTS'
AND xet.application_id = 222
AND adj.SET_OF_BOOKS_ID = xet.LEDGER_ID;
xla_events_pub_pkg.update_event
(p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_event_status_code => 'N',
p_valuation_method => null,
p_security_context => l_security);
'EXCEPTION: arp_process_adjustment.insert_reverse_actions' );
arp_standard.debug( 'arp_process_adjustment.insert_reverse_actions()-');
'EXCEPTION: arp_process_adjustment.insert_reverse_actions');
END insert_reverse_actions;
| val_insert_rev_actions |
| |
| DESCRIPTION |
| This procedure validated arguments passed to insert_reverse_actions |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN: |
| p_adj_id - Adjustment Record Id |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
+===========================================================================*/
PROCEDURE val_insert_rev_actions(
p_adj_id IN ar_adjustments.adjustment_id%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_process_adjustment.val_insert_rev_actions()+' );
arp_standard.debug( 'arp_process_adjustment.val_insert_rev_actions()-' );
'EXCEPTION: arp_process_adjustment.val_insert_rev_actions' );
END val_insert_rev_actions;
| and selected the balances from |
| ra_customer_Trx_lines for Line Level |
+===========================================================================*/
PROCEDURE cal_prorated_amounts( p_adj_amount IN number,
p_payment_schedule_id IN number,
p_type IN varchar2,
p_receivables_trx_id IN number,
p_apply_date IN date,
p_prorated_amt OUT NOCOPY number,
p_prorated_tax OUT NOCOPY number,
p_error_num OUT NOCOPY number,
p_cust_trx_line_id IN NUMBER default NULL
) IS
l_line_remaining number;
SELECT trx.legal_entity_id
INTO l_le_id
FROM ra_customer_Trx trx,
ar_payment_schedules ps
where ps.payment_schedule_id = p_payment_schedule_id
and ps.customer_trx_id = trx.customer_trx_id;
SELECT trx.type,
trx.tax_code_source,
nvl(details.asset_tax_code, trx.asset_tax_code),
trx.set_of_books_id
INTO l_activity_type,
l_tax_code_source,
l_asset_tax_code,
l_sob_id
FROM ar_receivables_trx trx,
ar_rec_trx_le_details details
WHERE trx.receivables_trx_id = p_receivables_trx_id
and trx.receivables_trx_id = details.receivables_trx_id (+)
and details.legal_entity_id (+) = l_le_id;
SELECT type, tax_code_source, asset_tax_code, set_of_books_id
INTO l_activity_type, l_tax_code_source, l_asset_tax_code, l_sob_id
FROM ar_receivables_trx
WHERE receivables_trx_id = p_receivables_trx_id;
SELECT amount_line_items_remaining,
tax_remaining,
invoice_currency_code
INTO l_line_remaining,
l_tax_remaining,
l_invoice_currency_code
FROM ar_payment_schedules
WHERE payment_schedule_id = p_payment_schedule_id;
SELECT sum(DECODE (lines.line_type,
'TAX',0,
'FREIGHT',0 , 1) *
DECODE(ct.complete_flag, 'N',
0, lines.amount_due_remaining)), -- line adr
sum(DECODE (lines.line_type,
'TAX',1,0) *
DECODE(ct.complete_flag,
'N', 0,
lines.amount_due_remaining )), -- tax adr
max(ct.invoice_currency_code) -- curr code
INTO l_line_remaining,
l_tax_remaining,
l_invoice_currency_code
FROM ra_customer_trx ct,
ra_customer_trx_lines lines
WHERE (lines.customer_Trx_line_id = p_cust_trx_line_id or
lines.link_to_cust_trx_line_id = p_cust_trx_line_id)
AND ct.customer_Trx_id = lines.customer_trx_id;
SELECT tax_rounding_rule INTO l_rounding_rule
FROM zx_product_options
WHERE application_id = 222
AND org_id = arp_global.sysparam.org_id;
SELECT trx.legal_entity_id
INTO l_le_id
FROM ra_customer_Trx trx,
ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_payment_schedule_id
AND ps.customer_trx_id = trx.customer_trx_id;
SELECT zxr.percentage_rate
INTO l_tax_rate
FROM zx_sco_rates zxr,
zx_accounts zxa
WHERE zxa.tax_account_entity_code = 'RATES'
AND zxa.tax_account_entity_id = zxr.tax_rate_id
AND NVL(zxr.tax_class, 'OUTPUT') = 'OUTPUT'
AND zxr.tax_jurisdiction_code is NULL
AND p_apply_date
BETWEEN nvl(zxr.effective_from, p_apply_date)
AND nvl(zxr.effective_to, p_apply_date)
AND zxr.tax_rate_code = l_asset_tax_code
AND (zxa.internal_organization_id = arp_standard.sysparm.org_id
OR zxa.internal_organization_id IS NULL)
AND active_flag = 'Y';