[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_sql_c number;
TYPE select_rec_type IS RECORD
(
rec_customer_trx_id BINARY_INTEGER,
rec_code_combination_id BINARY_INTEGER,
round_customer_trx_id BINARY_INTEGER
);
SELECT DISTINCT inv_trx.customer_trx_id
FROM RA_CUSTOMER_TRX cm_trx,
RA_CUSTOMER_TRX inv_trx,
RA_CUST_TRX_LINE_GL_DIST inv_rec
WHERE cm_trx.request_id = pp_request_id
AND cm_trx.previous_customer_trx_id = inv_trx.customer_trx_id
AND inv_trx.invoicing_rule_id IS NOT NULL
AND inv_trx.customer_trx_id = inv_rec.customer_trx_id
AND inv_rec.account_class = 'REC'
AND inv_rec.account_set_flag = 'N'
AND inv_rec.latest_rec_flag = 'Y'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */
'rof already set'
FROM ra_cust_trx_line_gl_dist g2
WHERE g2.customer_trx_id = inv_trx.customer_trx_id
AND g2.account_set_flag = 'N'
AND g2.account_class in ('UNEARN','UNBILL')
AND g2.rec_offset_flag = 'Y');
SELECT count(*)
INTO l_no_rof
FROM ra_customer_trx_lines tl
WHERE tl.customer_trx_id = p_customer_trx_id
AND tl.line_type = 'LINE'
AND tl.autorule_complete_flag IS NULL
AND tl.accounting_rule_id IS NOT NULL
AND NOT EXISTS
(SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */
'rof already set'
FROM ra_cust_trx_line_gl_dist g2
WHERE g2.customer_trx_id = tl.customer_trx_id
AND g2.account_set_flag = 'N'
AND g2.account_class in ('UNEARN','UNBILL')
AND g2.rec_offset_flag = 'Y');
update line. */
/* 7039838 - added autoinv specific logic for
FT tuning effort */
IF g_autoinv
THEN
UPDATE RA_CUST_TRX_LINE_GL_DIST
SET rec_offset_flag = 'Y'
WHERE cust_trx_line_gl_dist_id in
(SELECT /*+ PUSH_SUBQ UNNEST
index(tl RA_CUSTOMER_TRX_LINES_N4) */
g.cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist g,
ra_customer_trx_lines tl,
ra_cust_trx_line_gl_dist grec
WHERE tl.customer_trx_id = p_customer_trx_id
AND tl.request_id = g_autoinv_request_id
AND tl.accounting_rule_id is not null
AND tl.customer_trx_line_id = g.customer_trx_line_id
AND tl.line_type = 'LINE'
AND grec.customer_trx_id = tl.customer_trx_id
AND grec.account_class = 'REC'
AND grec.latest_rec_flag = 'Y'
AND grec.gl_date = g.gl_date
AND g.account_set_flag = 'N'
AND g.account_class in ('UNEARN','UNBILL')
AND g.revenue_adjustment_id is null
AND g.request_id is not null
AND sign(g.amount) = sign(tl.revenue_amount)
AND g.rec_offset_flag is null);
UPDATE RA_CUST_TRX_LINE_GL_DIST
SET rec_offset_flag = 'Y'
WHERE cust_trx_line_gl_dist_id in
(SELECT /*+ PUSH_SUBQ UNNEST */
g.cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist g,
ra_customer_trx_lines tl,
ra_cust_trx_line_gl_dist grec
WHERE tl.customer_trx_id = p_customer_trx_id
AND tl.accounting_rule_id is not null
AND tl.customer_trx_line_id = g.customer_trx_line_id
AND tl.line_type = 'LINE'
AND grec.customer_trx_id = tl.customer_trx_id
AND grec.account_class = 'REC'
AND grec.latest_rec_flag = 'Y'
AND grec.gl_date = g.gl_date
AND g.account_set_flag = 'N'
AND g.account_class in ('UNEARN','UNBILL')
AND g.revenue_adjustment_id is null
AND g.request_id is not null
AND sign(g.amount) = sign(tl.revenue_amount)
AND g.rec_offset_flag is null);
arp_util.debug(' updated ' || l_count ||
' rec_offset rows.');
forall update to improve performance */
OPEN inv_needing_rof(p_request_id);
UPDATE RA_CUST_TRX_LINE_GL_DIST G
SET rec_offset_flag = 'Y'
WHERE G.cust_trx_line_gl_dist_id in
(SELECT /*+ PUSH_SUBQ ORDERED UNNEST */
inv_g.cust_trx_line_gl_dist_id
FROM ra_customer_trx_lines inv_l,
ra_cust_trx_line_gl_dist inv_g,
ra_cust_trx_line_gl_dist inv_grec
WHERE inv_l.customer_trx_id = t_trx_id(i)
AND inv_l.accounting_rule_id is not null
AND inv_l.customer_trx_line_id =
inv_g.customer_trx_line_id
AND inv_l.line_type = 'LINE'
AND inv_grec.customer_trx_id = inv_l.customer_trx_id
AND inv_grec.account_class = 'REC'
AND inv_grec.latest_rec_flag = 'Y'
AND inv_grec.gl_date = inv_g.gl_date
AND inv_g.account_set_flag = 'N'
AND inv_g.account_class in ('UNEARN','UNBILL')
AND inv_g.revenue_adjustment_id is null
AND inv_g.request_id is not null
AND sign(inv_g.amount) = sign(inv_l.revenue_amount)
AND inv_g.rec_offset_flag is null);
arp_util.debug(' updated ' || l_count || ' rec_offset rows.');
/* we updated some. Technically, this does not mean
we are out of the woods, but we'll assume it set them */
p_result := 1;
/* no rows updated when some needed it */
p_result := -1;
| we update a dist row on that date for the delta amount and/or percent.
| The row chosen for update is the one with the max gl_dist_id on that
| date with an amount that has the same sign as the extended_amount
| of the line.
|
| This means that we will generally update REV lines for both invoices
| and credit memos - unless there is a more recent adjustment on the
| line, which will push us to choose the latest adjustment distribution.
| |
| As a bonus, we now also check RAM distributions separately from
| conventional distributions and round them (by line_id, gl_date, and
| revenue_adjustment_id) if they need it.
|
| SCOPE - PRIVATE |
| called from correct_rule_records_by_line
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| OUT: |
| |
| IN/ OUT: |
| None |
| |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 05-SEP-2002 M RAYMOND Bug 2535023 - Created
| 09-SEP-2002 M RAYMOND Bug 2543675 - Excluded non-rule trx from
| being processed.
| 13-SEP-2002 M RAYMOND Bug 2543675 - Process RAM dists to make sure
| that they balance, too.
|
+===========================================================================*/
PROCEDURE true_lines_by_gl_date(p_customer_trx_id IN
ra_customer_trx.customer_trx_id%type) IS
/* Cursor for TRUing by gl_date
Detects GL_DATES that do not sum to zero.
This is usually due to behavior of older
autoaccounting or shortcomings in trx workbench
calculations. It should not pick up non-rule trx
or CMs on non-rule trx.*/
CURSOR true_rows_by_date(p_trx_id NUMBER) IS
select g.customer_trx_line_id, g.gl_date,
sum(g.amount), sum(g.acctd_amount), sum(g.percent),
nvl(revenue_adjustment_id, -99) revenue_adjustment_id
from ra_cust_trx_line_gl_dist g,
ra_customer_trx h,
ra_customer_trx prev_h
where h.customer_trx_id = p_trx_id
and h.previous_customer_trx_id = prev_h.customer_trx_id (+)
and nvl(h.invoicing_rule_id, prev_h.invoicing_rule_id) is not null
and g.customer_trx_id = h.customer_trx_id
and g.account_class in ('REV','UNEARN','UNBILL')
and g.account_set_flag = 'N'
and g.rec_offset_flag is null
and g.posting_control_id = -3
group by g.customer_trx_line_id, g.gl_date, nvl(g.revenue_adjustment_id, -99)
having sum(amount) <> 0 or sum(acctd_amount) <> 0 or sum(percent) <> 0;
select g.customer_trx_line_id, g.gl_date,
sum(g.amount), sum(g.acctd_amount), sum(g.percent),
nvl(g.revenue_adjustment_id, -99) revenue_adjustment_id
from ra_cust_trx_line_gl_dist g,
ar_line_rev_adj_gt gt,
ra_customer_trx h,
ra_customer_trx prev_h
where h.customer_trx_id = g.customer_trx_id
and h.previous_customer_trx_id = prev_h.customer_trx_id (+)
and nvl(h.invoicing_rule_id, prev_h.invoicing_rule_id) is not null
and g.customer_trx_line_id = gt.customer_trx_line_id
and g.account_class in ('REV','UNEARN','UNBILL')
and g.account_set_flag = 'N'
and g.rec_offset_flag is null
and g.posting_control_id = -3
group by g.customer_trx_line_id, g.gl_date, nvl(g.revenue_adjustment_id, -99)
having sum(g.amount) <> 0 or sum(g.acctd_amount) <> 0 or sum(g.percent) <> 0;
/* Now update all the rows that require it */
arp_standard.debug('Rows that need truing: ' || l_rows_needing_truing);
UPDATE ra_cust_trx_line_gl_dist g
SET amount = amount - t_true_amount(i),
percent = percent - t_true_percent(i),
acctd_amount = acctd_amount - t_true_acctd(i),
last_updated_by = arp_global.last_updated_by,
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id in (
/* SELECT GL_DIST_ID FOR EACH DATE THAT
REQUIRES TRUING */
select MAX(g.cust_trx_line_gl_dist_id)
from ra_cust_trx_line_gl_dist g,
ra_customer_trx_lines tl
where g.customer_trx_line_id = t_true_line_id(i)
and g.gl_date = t_true_gl_date(i)
and g.customer_trx_line_id = tl.customer_trx_line_id
and sign(g.amount) = sign(tl.revenue_amount)
and g.account_set_flag = 'N'
and g.rec_offset_flag is null
and nvl(g.revenue_adjustment_id, -99) = t_true_ram_id(i)
and g.posting_control_id = -3
/* END OF GL_DIST BY DATE SELECT */
);
/* There was a problem and we did not update the correct number
of rows. Display the rows requiring update and indicate if they were
updated. */
arp_standard.debug('Mismatch between lines found and lines updated for truing (see below)');
UPDATE RA_CUST_TRX_LINE_GL_DIST
SET acctd_amount = acctd_amount + l_acctd_correction
WHERE cust_trx_line_gl_dist_id in
(SELECT MAX(g.cust_trx_line_gl_dist_id)
FROM ra_cust_trx_line_gl_dist g
WHERE g.account_class = 'SUSPENSE'
AND g.account_set_flag = 'N'
AND g.customer_trx_id = p_customer_trx_id
AND g.posting_control_id = -3
AND g.acctd_amount = (
SELECT MAX(g2.acctd_amount)
FROM ra_cust_trx_line_gl_dist g2
WHERE g2.customer_trx_id = p_customer_trx_id
AND g2.account_class = 'SUSPENSE'
AND g2.account_set_flag = 'N'
AND g2.posting_control_id = -3));
arp_util.debug(' updated ' || l_rows
|| ' suspense rows.');
SELECT
precision,
minimum_accountable_unit,
period_set_name
INTO
base_precision,
base_min_accountable_unit,
period_set_name
FROM
fnd_currencies f,
gl_sets_of_books b,
ar_system_parameters p
WHERE
p.set_of_books_id = b.set_of_books_id
AND f.currency_code = b.currency_code;
| insert_round_records() |
| |
| DESCRIPTION |
| This function inserts one record of account_class ROUND into the |
| ra_cust_trx_line_gl_dist table. |
| |
| If the ROUND record already exist for a transaction then it is not |
| inserted again. Like the REC record there will be only 1 (2 in case of|
| transaction with rule) ROUND record for each transaction. |
| The ROUND record is copied from the REC record of the invoice |
| |
| Some of the column values for the ROUND record are as follows: |
| |
| customer_trx_line_id = NULL |
| gl_date = receivable gl_date |
| latest_rec_flag = NULL |
| account_set_flag = receivable account_set_flag |
| |
| REQUIRES |
| |
| RETURNS |
| TRUE if no errors occur |
| An ORACLE ERROR EXCEPTION if an ORACLE error occurs |
| |
| NOTES |
| *** PLEASE READ THE PACKAGE LEVEL NOTE BEFORE MODIFYING THIS FUNCTION.|
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 13-Aug-2002 Debbie Jancis Modified for mrc trigger replacement |
| added calls for insert into |
| ra_cust_trx_line_gl_dist |
| 24-SEP-2002 M.Ryzhikova Modified for mrc trigger replacement. |
| 01-OCT-2003 M Raymond Bug 3067588 - made this function public
+-------------------------------------------------------------------------*/
FUNCTION insert_round_records( P_REQUEST_ID IN NUMBER,
P_CUSTOMER_TRX_ID IN NUMBER,
P_ROWS_PROCESSED IN OUT NOCOPY NUMBER,
P_ERROR_MESSAGE OUT NOCOPY VARCHAR2,
P_BASE_PRECISION IN NUMBER,
P_BASE_MAU IN NUMBER,
P_TRX_CLASS_TO_PROCESS IN VARCHAR2,
P_TRX_HEADER_ROUND_CCID IN NUMBER)
RETURN NUMBER IS
rows NUMBER;
arp_standard.debug('arp_rounding.insert_round_record()+ ' ||
to_char(sysdate, 'DD-MON-YY HH:MI:SS'));
insert into ra_cust_trx_line_gl_dist
(POST_REQUEST_ID ,
POSTING_CONTROL_ID ,
ACCOUNT_CLASS ,
RA_POST_LOOP_NUMBER ,
CUSTOMER_TRX_ID ,
ACCOUNT_SET_FLAG ,
ACCTD_AMOUNT ,
USSGL_TRANSACTION_CODE ,
USSGL_TRANSACTION_CODE_CONTEXT ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
LATEST_REC_FLAG ,
ORG_ID ,
CUST_TRX_LINE_GL_DIST_ID ,
CUSTOMER_TRX_LINE_ID ,
CODE_COMBINATION_ID ,
SET_OF_BOOKS_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
PERCENT ,
AMOUNT ,
GL_DATE ,
GL_POSTED_DATE ,
CUST_TRX_LINE_SALESREP_ID ,
COMMENTS ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CONCATENATED_SEGMENTS ,
ORIGINAL_GL_DATE )
select
POST_REQUEST_ID,
-3,
'ROUND',
RA_POST_LOOP_NUMBER,
CUSTOMER_TRX_ID,
ACCOUNT_SET_FLAG,
NULL, /* acctd_amount */
USSGL_TRANSACTION_CODE,
USSGL_TRANSACTION_CODE_CONTEXT,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
NULL, /* LATEST_REC_FLAG */
ORG_ID,
RA_CUST_TRX_LINE_GL_DIST_s.nextval,
CUSTOMER_TRX_LINE_ID,
P_TRX_HEADER_ROUND_CCID, /* CODE_COMBINATION_ID */
SET_OF_BOOKS_ID,
SYSDATE,
arp_global.last_updated_by,
SYSDATE,
arp_global.created_by,
arp_global.last_update_login,
PERCENT,
NULL, /* AMOUNT */
GL_DATE,
GL_POSTED_DATE,
CUST_TRX_LINE_SALESREP_ID,
COMMENTS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
arp_global.request_id,
arp_global.program_application_id,
arp_global.program_id,
arp_global.program_update_date,
CONCATENATED_SEGMENTS,
ORIGINAL_GL_DATE
from ra_cust_trx_line_gl_dist rec
where account_class = 'REC'
and latest_rec_flag = 'Y'
and gl_posted_date is null
and rec.request_id = p_request_id
/* bug3311759 : Removed
and not exists ( select 1
from ra_cust_trx_line_gl_dist dist2
where dist2.customer_trx_id = rec.customer_trx_id
and dist2.account_class in ('UNEARN','UNBILL')
and dist2.account_set_flag = 'N')
*/
and not exists ( select 1
from ra_cust_trx_line_gl_dist dist2
where dist2.customer_trx_id = rec.customer_trx_id
and dist2.account_class = 'ROUND'
and dist2.account_set_flag = rec.account_set_flag);
arp_standard.debug('calling mrc engine for insertion of gl dist data');
insert into ra_cust_trx_line_gl_dist
(POST_REQUEST_ID ,
POSTING_CONTROL_ID ,
ACCOUNT_CLASS ,
RA_POST_LOOP_NUMBER ,
CUSTOMER_TRX_ID ,
ACCOUNT_SET_FLAG ,
ACCTD_AMOUNT ,
USSGL_TRANSACTION_CODE ,
USSGL_TRANSACTION_CODE_CONTEXT ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
LATEST_REC_FLAG ,
ORG_ID ,
CUST_TRX_LINE_GL_DIST_ID ,
CUSTOMER_TRX_LINE_ID ,
CODE_COMBINATION_ID ,
SET_OF_BOOKS_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
PERCENT ,
AMOUNT ,
GL_DATE ,
GL_POSTED_DATE ,
CUST_TRX_LINE_SALESREP_ID ,
COMMENTS ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CONCATENATED_SEGMENTS ,
ORIGINAL_GL_DATE )
select
POST_REQUEST_ID,
-3,
'ROUND',
RA_POST_LOOP_NUMBER,
CUSTOMER_TRX_ID,
ACCOUNT_SET_FLAG,
NULL, /* acctd_amount */
USSGL_TRANSACTION_CODE,
USSGL_TRANSACTION_CODE_CONTEXT,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
NULL, /* LATEST_REC_FLAG */
ORG_ID,
RA_CUST_TRX_LINE_GL_DIST_s.nextval,
CUSTOMER_TRX_LINE_ID,
P_TRX_HEADER_ROUND_CCID, /* CODE_COMBINATION_ID */
SET_OF_BOOKS_ID,
SYSDATE,
arp_global.last_updated_by,
SYSDATE,
arp_global.created_by,
arp_global.last_update_login,
PERCENT,
NULL, /* AMOUNT */
GL_DATE,
GL_POSTED_DATE,
CUST_TRX_LINE_SALESREP_ID,
COMMENTS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
arp_global.request_id,
arp_global.program_application_id,
arp_global.program_id,
arp_global.program_update_date,
CONCATENATED_SEGMENTS,
ORIGINAL_GL_DATE
from ra_cust_trx_line_gl_dist rec
where account_class = 'REC'
and latest_rec_flag = 'Y'
and gl_posted_date is null
and rec.customer_trx_id = p_customer_trx_id
/* bug3311759 : Removed
and not exists ( select 1
from ra_cust_trx_line_gl_dist dist2
where dist2.customer_trx_id = rec.customer_trx_id
and dist2.account_class in ('UNEARN','UNBILL')
and dist2.account_set_flag = 'N')
*/
and not exists ( select 1
from ra_cust_trx_line_gl_dist dist2
where dist2.customer_trx_id = rec.customer_trx_id
and dist2.account_class = 'ROUND'
and dist2.account_set_flag = rec.account_set_flag);
arp_standard.debug('Rows were inserted into gl dist ');
SELECT cust_trx_line_gl_dist_id
BULK COLLECT INTO l_gl_dist_key_value_list
FROM ra_cust_trx_line_gl_dist rec
where rec.customer_trx_id = p_customer_trx_id
and account_class = 'ROUND';
| call mrc engine to insert RA_CUST_TRX_LINES_GL_DIST |
+-----------------------------------------------------*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('before calling maintain_mrc ');
p_event_mode => 'INSERT',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'BATCH',
p_key_value_list => l_gl_dist_key_value_list) ;
arp_standard.debug('arp_rounding.insert_round_record()- ' ||
to_char(sysdate, 'DD-MON-YY HH:MI:SS'));
end insert_round_records;
select nvl(rec.acctd_amount,0) -
sum( decode(fc.minimum_accountable_unit,
null, round(l.extended_amount *
nvl(ct.exchange_rate,1),
fc.precision),
round( (l.extended_amount *
nvl(ct.exchange_rate,1)
) / fc.minimum_accountable_unit
) * fc.minimum_accountable_unit
)
)
into l_round_acctd_amount
from ra_customer_trx ct,
ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist rec,
fnd_currencies fc,
gl_sets_of_books gsb
where ct.customer_trx_id = l.customer_trx_id
and ct.customer_trx_id = rec.customer_trx_id
and ct.customer_trx_id = P_CUSTOMER_TRX_ID
and ct.set_of_books_id = gsb.set_of_books_id
and fc.currency_code = gsb.currency_code
and rec.account_class = 'REC'
and rec.latest_rec_flag = 'Y'
group by rec.acctd_amount;
select
nvl(rec.acctd_amount,0) - sum(nvl(lgd.acctd_amount,0))
into l_round_acctd_amount
from ra_cust_trx_line_gl_dist lgd,
ra_cust_trx_line_gl_dist rec
where lgd.customer_trx_id = rec.customer_trx_id
and rec.customer_trx_id = P_CUSTOMER_TRX_ID
and rec.account_class = 'REC'
and rec.latest_rec_flag = 'Y'
and lgd.account_set_flag = 'N'
and lgd.account_class not in ('REC', 'ROUND')
group by rec.acctd_amount;
| for a give transaction and update it's ROUND record with it. |
| |
| The rounding difference is calculated as follows : |
| |
| round acctd_amount = |
| receivable acctd_amount - |
| Sum( line amount converted to functional currency rounded for |
| functional currency) |
| |
| This function also update the following columns of the round record. |
| amount = 0 |
| code_combination_id = code_combination_id for ROUND account after |
| substituting the balancing segment with REC |
| account |
| concatenated_segments = concatenated_segments returned by the |
| replace_balancing_segment function |
| |
| REQUIRES |
| |
| RETURNS |
| TRUE if no errors occur |
| An ORACLE ERROR EXCEPTION if an ORACLE error occurs |
| |
| NOTES |
| *** PLEASE READ THE PACKAGE LEVEL NOTE BEFORE MODIFYING THIS FUNCTION.|
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| |
+-------------------------------------------------------------------------*/
FUNCTION correct_round_records( P_REQUEST_ID IN NUMBER,
P_CUSTOMER_TRX_ID IN NUMBER,
P_CUSTOMER_TRX_LINE_ID IN NUMBER,
P_ROWS_PROCESSED IN OUT NOCOPY NUMBER,
P_ERROR_MESSAGE OUT NOCOPY VARCHAR2,
P_BASE_PRECISION IN NUMBER,
P_BASE_MAU IN NUMBER,
P_TRX_CLASS_TO_PROCESS IN VARCHAR2,
concat_segs IN VARCHAR2,
balanced_round_ccid IN NUMBER)
RETURN NUMBER IS
/* Bug 2736599 - replaced get_line_round_acctd_amount with
get_dist_round_acctd_amount to resolve issues with
header level rounding and SUSPENSE accounts */
l_line_round_acctd_amount number := nvl(get_dist_round_acctd_amount(P_CUSTOMER_TRX_ID),0);
update ra_cust_trx_line_gl_dist dist
set (amount, acctd_amount, code_combination_id, concatenated_segments) =
(select 0,
l_line_round_acctd_amount,
nvl(balanced_round_ccid,-1),
concatenated_segments
from ra_customer_trx ct
where ct.customer_trx_id = dist.customer_trx_id
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
where dist.customer_trx_id = P_CUSTOMER_TRX_ID
and dist.account_class = 'ROUND'
and dist.gl_posted_date is null
and (
nvl(dist.amount,0) <> 0 OR
nvl(dist.acctd_amount, 0)<> l_line_round_acctd_amount OR
dist.code_combination_id <> nvl(balanced_round_ccid,-1) OR
dist.acctd_amount is null OR
dist.amount is null
);
update ra_cust_trx_line_gl_dist rec
set (amount, acctd_amount, percent) =
( select nvl(rec.amount, 0) +
(sum(l.extended_amount) - nvl(rec.amount, 0) ),
nvl(rec.acctd_amount, 0) +
( decode(p_base_mau,
null, round(sum(l.extended_amount) *
max(nvl(exchange_rate,1)),
p_base_precision),
round( (sum(l.extended_amount) *
max(nvl(exchange_rate,1))
) / p_base_mau
) * p_base_mau
)
- nvl(rec.acctd_amount, 0)
), /* acctd_amount */
rec.percent + (100 - rec.percent) /* percent */
from ra_customer_trx t,
ra_customer_trx_lines l
where t.customer_trx_id = l.customer_trx_id
and t.customer_trx_id = rec.customer_trx_id
group by l.customer_trx_id,
t.trx_number,
t.exchange_rate_type,
t.invoice_currency_code,
t.exchange_date,
exchange_rate
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
where customer_trx_id in
( select l.customer_trx_id
from ra_customer_trx_lines l,
ra_customer_trx t,
ra_cust_trx_line_gl_dist d
where t.customer_trx_id = l.customer_trx_id
and t.customer_trx_id = d.customer_trx_id
and d.account_class = 'REC'
and d.latest_rec_flag = 'Y'
/*-------------------------------------------
---CUT HERE--- */
and d.request_id = p_request_id
/* *
*------------------------------------------*/
and nvl(t.previous_customer_trx_id, -1) =
decode(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
nvl(t.previous_customer_trx_id, -1) )
having (
sum(l.extended_amount) <> nvl(d.amount, 0) OR
100 <> nvl(d.percent, 0) OR
decode(p_base_mau,
null, round(sum(l.extended_amount) *
max(nvl(exchange_rate,1)),
p_base_precision),
round( (sum(l.extended_amount) *
max(nvl(exchange_rate,1))
) / p_base_mau
) * p_base_mau
)
<> nvl(d.acctd_amount, 0) OR
d.acctd_amount is null OR
d.amount is null
)
group by l.customer_trx_id,
t.trx_number,
d.amount,
d.acctd_amount,
d.percent,
t.invoice_currency_code,
t.exchange_date,
t.exchange_rate_type,
exchange_rate
)
and rec.account_class = 'REC'
and rec.gl_posted_date is null;
update ra_cust_trx_line_gl_dist rec
set (amount, acctd_amount, percent) =
( select /*+ index(L RA_CUSTOMER_TRX_LINES_N4) */
nvl(rec.amount, 0) +
(sum(l.extended_amount) - nvl(rec.amount, 0) ),
nvl(rec.acctd_amount, 0) +
( decode(p_base_mau,
null, round(sum(l.extended_amount) *
max(nvl(exchange_rate,1)),
p_base_precision),
round( (sum(l.extended_amount) *
max(nvl(exchange_rate,1))
) / p_base_mau
) * p_base_mau
)
- nvl(rec.acctd_amount, 0)
), /* acctd_amount */
rec.percent + (100 - rec.percent) /* percent */
from ra_customer_trx t,
ra_customer_trx_lines l
where t.customer_trx_id = l.customer_trx_id
and l.customer_trx_id = rec.customer_trx_id
and l.request_id = g_autoinv_request_id -- 7039838
group by l.customer_trx_id,
t.trx_number,
t.invoice_currency_code,
t.exchange_date,
t.exchange_rate_type,
exchange_rate
),
last_updated_by = arp_global.last_updated_by, /*Bug 2089972 */
last_update_date = sysdate
where customer_trx_id in
( select /*+ leading(T,D,L) use_hash(L)
index(L RA_CUSTOMER_TRX_LINES_N4) */
l.customer_trx_id
from ra_customer_trx t,
ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist d
where t.customer_trx_id = l.customer_trx_id
and l.customer_trx_id = d.customer_trx_id
and l.request_id = g_autoinv_request_id -- 7039838
and l.customer_trx_id = p_customer_trx_id -- 7039838
and d.account_class = 'REC'
and d.latest_rec_flag = 'Y'
/*-------------------------------------------------
---CUT HERE--- */
and d.customer_trx_id = p_customer_trx_id
/*
*------------------------------------------------*/
and nvl(t.previous_customer_trx_id, -1) =
decode(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
nvl(t.previous_customer_trx_id, -1) )
having (
sum(l.extended_amount) <> nvl(d.amount, 0) OR
100 <> nvl(d.percent, 0) OR
decode(p_base_mau,
null, round(sum(l.extended_amount) *
max(nvl(exchange_rate,1)),
p_base_precision),
round( (sum(l.extended_amount) *
max(nvl(exchange_rate,1))
) / p_base_mau
) * p_base_mau
)
<> nvl(d.acctd_amount, 0) OR
d.acctd_amount is null OR
d.amount is null
)
group by l.customer_trx_id,
t.trx_number,
d.amount,
d.acctd_amount,
d.percent,
t.invoice_currency_code,
t.exchange_date,
t.exchange_rate_type,
exchange_rate
)
and rec.account_class = 'REC'
and rec.gl_posted_date is null;
update ra_cust_trx_line_gl_dist rec
set (amount, acctd_amount, percent) =
( select nvl(rec.amount, 0) +
(sum(l.extended_amount) - nvl(rec.amount, 0) ),
nvl(rec.acctd_amount, 0) +
( decode(p_base_mau,
null, round(sum(l.extended_amount) *
max(nvl(exchange_rate,1)),
p_base_precision),
round( (sum(l.extended_amount) *
max(nvl(exchange_rate,1))
) / p_base_mau
) * p_base_mau
)
- nvl(rec.acctd_amount, 0)
), /* acctd_amount */
rec.percent + (100 - rec.percent) /* percent */
from ra_customer_trx t,
ra_customer_trx_lines l
where t.customer_trx_id = l.customer_trx_id
and l.customer_trx_id = rec.customer_trx_id
group by l.customer_trx_id,
t.trx_number,
t.invoice_currency_code,
t.exchange_date,
t.exchange_rate_type,
exchange_rate
),
last_updated_by = arp_global.last_updated_by, /*Bug 2089972 */
last_update_date = sysdate
where customer_trx_id in
( select l.customer_trx_id
from ra_customer_trx t,
ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist d
where t.customer_trx_id = l.customer_trx_id
and l.customer_trx_id = d.customer_trx_id
and d.account_class = 'REC'
and d.latest_rec_flag = 'Y'
/*-------------------------------------------------
---CUT HERE--- */
and d.customer_trx_id = p_customer_trx_id
/*
*------------------------------------------------*/
and nvl(t.previous_customer_trx_id, -1) =
decode(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
nvl(t.previous_customer_trx_id, -1) )
having (
sum(l.extended_amount) <> nvl(d.amount, 0) OR
100 <> nvl(d.percent, 0) OR
decode(p_base_mau,
null, round(sum(l.extended_amount) *
max(nvl(exchange_rate,1)),
p_base_precision),
round( (sum(l.extended_amount) *
max(nvl(exchange_rate,1))
) / p_base_mau
) * p_base_mau
)
<> nvl(d.acctd_amount, 0) OR
d.acctd_amount is null OR
d.amount is null
)
group by l.customer_trx_id,
t.trx_number,
d.amount,
d.acctd_amount,
d.percent,
t.invoice_currency_code,
t.exchange_date,
t.exchange_rate_type,
exchange_rate
)
and rec.account_class = 'REC'
and rec.gl_posted_date is null;
UPDATE ra_cust_trx_line_gl_dist rec
SET (amount, acctd_amount, percent) =
( SELECT
NVL(rec.amount, 0) +
(SUM(l.extended_amount) - NVL(rec.amount, 0) ),
NVL(rec.acctd_amount, 0) +
(
sum( decode(p_base_mau,
null, round(l.extended_amount *
nvl(exchange_rate,1),
p_base_precision),
round( (l.extended_amount *
nvl(exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
- NVL(rec.acctd_amount, 0)
), /* acctd_amount */
rec.percent + (100 - rec.percent) /* percent */
FROM
ra_customer_trx_lines l,
ra_customer_trx t
WHERE
t.customer_trx_id = rec.customer_trx_id
AND l.customer_trx_id = t.customer_trx_id
GROUP BY
l.customer_trx_id,
t.trx_number
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
WHERE customer_trx_id IN
( SELECT
l.customer_trx_id
FROM
ra_customer_trx_lines l,
ra_customer_trx t,
ra_cust_trx_line_gl_dist d
WHERE
t.customer_trx_id = d.customer_trx_id
AND l.customer_trx_id = t.customer_trx_id
AND d.account_class = 'REC'
AND d.latest_rec_flag = 'Y'
/*-------------------------------------------
---CUT HERE--- */
AND d.request_id = p_request_id
/* *
*------------------------------------------*/
AND NVL(t.previous_customer_trx_id, -1) =
DECODE(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
nvl(t.previous_customer_trx_id, -1) )
having (
sum(l.extended_amount) <> nvl(d.amount, 0) OR
100 <> nvl(d.percent, 0) OR
sum(
decode(p_base_mau,
null, round(l.extended_amount *
nvl(exchange_rate,1),
p_base_precision),
round( (l.extended_amount *
nvl(exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
<> nvl(d.acctd_amount, 0) OR
d.acctd_amount is null OR
d.amount is null
)
GROUP BY
l.customer_trx_id,
t.trx_number,
d.amount,
d.acctd_amount,
d.percent
)
AND rec.account_class = 'REC'
AND rec.gl_posted_date IS NULL;
UPDATE ra_cust_trx_line_gl_dist rec
SET (amount, acctd_amount, percent) =
( SELECT
NVL(rec.amount, 0) +
(SUM(l.extended_amount) - NVL(rec.amount, 0) ),
NVL(rec.acctd_amount, 0) +
(
sum(
decode(p_base_mau,
null, round(l.extended_amount *
nvl(exchange_rate,1),
p_base_precision),
round( (l.extended_amount *
nvl(exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
- NVL(rec.acctd_amount, 0)
),
rec.percent + (100 - rec.percent) /* percent */
FROM
ra_customer_trx_lines l,
ra_customer_trx t
WHERE
t.customer_trx_id = rec.customer_trx_id
AND l.customer_trx_id = t.customer_trx_id
GROUP BY
l.customer_trx_id,
t.trx_number
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
WHERE customer_trx_id IN
( SELECT
l.customer_trx_id
FROM
ra_customer_trx t,
ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist d
WHERE
t.customer_trx_id = d.customer_trx_id
AND l.customer_trx_id = t.customer_trx_id
AND d.account_class = 'REC'
AND d.latest_rec_flag = 'Y'
/*-------------------------------------------------
---CUT HERE--- */
AND d.customer_trx_id = p_customer_trx_id
/*
*------------------------------------------------*/
AND NVL(t.previous_customer_trx_id, -1) =
DECODE(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
nvl(t.previous_customer_trx_id, -1) )
having (
sum(l.extended_amount) <> nvl(d.amount, 0) OR
100 <> nvl(d.percent, 0) OR
sum(
decode(p_base_mau,
null, round(l.extended_amount *
nvl(exchange_rate,1),
p_base_precision),
round( (l.extended_amount *
nvl(exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
<> nvl(d.acctd_amount, 0) OR
d.acctd_amount is null OR
d.amount is null
)
GROUP BY
l.customer_trx_id,
t.trx_number,
d.amount,
d.acctd_amount,
d.percent
)
AND rec.account_class = 'REC'
AND rec.gl_posted_date IS NULL;
UPDATE ra_cust_trx_line_gl_dist lgd
SET (amount, acctd_amount) =
(SELECT /*+ index(rec1 RA_CUST_TRX_LINE_GL_DIST_N6) ordered */ NVL(lgd.amount, 0) -
(
SUM(lgd2.amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
ctl.extended_amount
)
), /* entered amount */
NVL(lgd.acctd_amount, 0) -
(
SUM(lgd2.acctd_amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND( ctl.extended_amount *
NVL(ct.exchange_rate,1),
p_base_precision),
ROUND( ( ctl.extended_amount *
NVL(ct.exchange_rate,1)
) / p_base_mau ) * p_base_mau
)
)
) /* accounted amount */
FROM
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist lgd2,
ra_cust_trx_line_gl_dist rec1
WHERE
ctl.customer_trx_line_id = lgd2.customer_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND lgd.customer_trx_line_id = ctl.customer_trx_line_id
-- 718096 AND lgd.account_class = lgd2.account_class
AND lgd2.account_set_flag = 'N'
AND rec1.customer_trx_id = ct.customer_trx_id
AND rec1.account_class = 'REC'
AND rec1.latest_rec_flag = 'Y'
AND NVL(lgd.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd2.gl_date, to_date( 2415021, 'J') )
GROUP BY
ctl.customer_trx_line_id,
-- 718096 lgd2.account_class,
rec1.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
ct.exchange_rate
),
percent =
(SELECT /*+ index(rec2 RA_CUST_TRX_LINE_GL_DIST_N6) */ DECODE(lgd.account_class || lgd.account_set_flag,
'SUSPENSEN', lgd.percent,
'UNBILLN', lgd.percent,
'UNEARNN', lgd.percent,
NVL(lgd.percent, 0) -
(
SUM(NVL(lgd4.percent, 0))
- DECODE(rec2.gl_date,
NVL(lgd.gl_date,
rec2.gl_date), 100,
0)
)
) /* percent */
FROM
ra_cust_trx_line_gl_dist lgd4,
ra_cust_trx_line_gl_dist rec2
WHERE
lgd.customer_trx_line_id = lgd4.customer_trx_line_id
AND rec2.customer_trx_id = lgd.customer_trx_id
AND rec2.customer_trx_id = lgd4.customer_trx_id
AND rec2.account_class = 'REC'
AND rec2.latest_rec_flag = 'Y'
AND lgd4.account_set_flag = lgd.account_set_flag
AND DECODE(lgd4.account_set_flag,
'Y', lgd4.account_class,
lgd.account_class) = lgd.account_class
AND NVL(lgd.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd4.gl_date, to_date( 2415021, 'J') )
GROUP BY
rec2.gl_date,
lgd.gl_date
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id IN
(SELECT /*+ index(rec3 RA_CUST_TRX_LINE_GL_DIST_N6) */
MIN(DECODE(lgd3.gl_posted_date,
NULL, lgd3.cust_trx_line_gl_dist_id,
NULL) )
FROM
ra_customer_trx_lines ctl,
ra_customer_trx t,
ra_cust_trx_line_gl_dist lgd3,
ra_cust_trx_line_gl_dist rec3
WHERE
t.request_id = p_request_id
AND T.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
AND (CTL.LINE_TYPE IN ( 'TAX','FREIGHT','CHARGES','SUSPENSE' ) OR
(CTL.LINE_TYPE = 'LINE' AND CTL.ACCOUNTING_RULE_ID IS NULL ))
AND LGD3.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID
AND LGD3.ACCOUNT_SET_FLAG = 'N'
AND REC3.CUSTOMER_TRX_ID = T.CUSTOMER_TRX_ID
AND REC3.ACCOUNT_CLASS = 'REC'
AND REC3.LATEST_REC_FLAG = 'Y'
AND NVL(t.previous_customer_trx_id, -1) =
DECODE(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
NVL(t.previous_customer_trx_id, -1) )
GROUP BY
ctl.customer_trx_line_id,
-- 718096 lgd3.account_class,
lgd3.gl_date,
rec3.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
t.exchange_rate
HAVING (
SUM(NVL(lgd3.amount, 0))
<> ctl.extended_amount *
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0)
OR
SUM(NVL(lgd3.acctd_amount, 0)) <>
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND( ctl.extended_amount *
NVL(t.exchange_rate,1),
p_base_precision ),
ROUND( ( ctl.extended_amount *
NVL(t.exchange_rate,1)
) / p_base_mau ) * p_base_mau
)
)
UNION
SELECT /*+ index(rec5 RA_CUST_TRX_LINE_GL_DIST_N6) INDEX (lgd5 ra_cust_trx_line_gl_dist_n6) index(ctl2 ra_customer_trx_lines_u1) */
TO_NUMBER(
MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
lgd5.account_set_flag,
'REVN', lgd5.cust_trx_line_gl_dist_id,
'REVY', lgd5.cust_trx_line_gl_dist_id,
'TAXN', lgd5.cust_trx_line_gl_dist_id,
'TAXY', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTN', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTY', lgd5.cust_trx_line_gl_dist_id,
'CHARGESN', lgd5.cust_trx_line_gl_dist_id,
'CHARGESY', lgd5.cust_trx_line_gl_dist_id,
'UNEARNY', lgd5.cust_trx_line_gl_dist_id,
'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
NULL ) )
)
FROM
ra_cust_trx_line_gl_dist lgd5,
ra_cust_trx_line_gl_dist rec5,
ra_customer_trx_lines ctl2,
ra_customer_trx t
WHERE
T.REQUEST_ID = p_request_id
AND T.CUSTOMER_TRX_ID = REC5.CUSTOMER_TRX_ID
AND CTL2.CUSTOMER_TRX_LINE_ID = LGD5.CUSTOMER_TRX_LINE_ID
AND REC5.CUSTOMER_TRX_ID = LGD5.CUSTOMER_TRX_ID
AND REC5.ACCOUNT_CLASS = 'REC'
AND REC5.LATEST_REC_FLAG = 'Y'
AND (CTL2.LINE_TYPE IN ( 'TAX','FREIGHT','CHARGES','SUSPENSE')
OR
(CTL2.LINE_TYPE = 'LINE' AND
(CTL2.ACCOUNTING_RULE_ID IS NULL OR LGD5.ACCOUNT_SET_FLAG = 'Y' )))
GROUP BY
lgd5.customer_trx_line_id,
lgd5.gl_date,
rec5.gl_date,
lgd5.account_set_flag,
DECODE(lgd5.account_set_flag,
'N', NULL,
lgd5.account_class)
HAVING
SUM(NVL(lgd5.percent, 0)) <>
DECODE( NVL(lgd5.gl_date, rec5.gl_date),
rec5.gl_date, 100,
0)
);
UPDATE ra_cust_trx_line_gl_dist lgd
SET (amount, acctd_amount) =
(SELECT /*+ index(LGD2 RA_CUST_TRX_LINE_GL_DIST_N10) */
NVL(lgd.amount, 0) -
(
SUM(lgd2.amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount)
)
), /* entered amount */
NVL(lgd.acctd_amount, 0) -
(
SUM(lgd2.acctd_amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND(DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(ct.exchange_rate,1),
p_base_precision ),
ROUND( (DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(ct.exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
) /* accounted amount */
FROM
ra_cust_trx_line_gl_dist lgd2,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist rec1
WHERE
rec1.customer_trx_id = lgd.customer_trx_id
AND rec1.account_class = 'REC'
AND rec1.latest_rec_flag = 'Y'
AND ct.customer_trx_id = rec1.customer_trx_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ctl.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd2.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd2.account_class = lgd.account_class
AND lgd2.account_set_flag = 'N'
AND lgd2.request_id = g_autoinv_request_id
AND NVL(lgd2.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd.gl_date, to_date( 2415021, 'J') )
GROUP BY
ctl.customer_trx_line_id,
DECODE(lgd2.account_class,'UNEARN','REV',
lgd2.account_class),
rec1.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
ct.exchange_rate
),
percent =
(SELECT /*+ index(LGD4 RA_CUST_TRX_LINE_GL_DIST_N10) */
DECODE(lgd.account_class || lgd.account_set_flag,
'SUSPENSEN', lgd.percent,
'UNBILLN', lgd.percent,
'UNEARNN', lgd.percent,
NVL(lgd.percent, 0) -
(
SUM(NVL(lgd4.percent, 0))
- DECODE(rec2.gl_date,
NVL(lgd.gl_date, rec2.gl_date),
100, 0)
)
) /* percent */
FROM
ra_cust_trx_line_gl_dist lgd4,
ra_cust_trx_line_gl_dist rec2
WHERE
rec2.customer_trx_id = lgd.customer_trx_id
AND rec2.account_class = 'REC'
AND rec2.latest_rec_flag = 'Y'
AND lgd4.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd4.account_set_flag = lgd.account_set_flag
AND DECODE(lgd4.account_set_flag,
'Y', lgd4.account_class,
lgd.account_class) = lgd.account_class
AND NVL(lgd4.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd.gl_date, to_date( 2415021, 'J') )
AND lgd4.request_id = g_autoinv_request_id
GROUP BY
rec2.gl_date,
lgd.gl_date
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id IN
(SELECT /*+ leading(T,LGD3,REC3,CTL)
use_hash(CTL) index(CTL RA_CUSTOMER_TRX_LINES_N4)
index(LGD3 RA_CUST_TRX_LINE_GL_DIST_N6)
index(REC3 RA_CUST_TRX_LINE_GL_DIST_N6) */
MIN(DECODE(lgd3.gl_posted_date,
NULL, lgd3.cust_trx_line_gl_dist_id,
NULL) )
FROM
ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist lgd3,
ra_cust_trx_line_gl_dist rec3,
ra_customer_trx t
WHERE
t.customer_trx_id = p_customer_trx_id
AND rec3.customer_trx_id = t.customer_trx_id
AND rec3.account_class = 'REC'
AND rec3.latest_rec_flag = 'Y'
AND lgd3.customer_trx_id = t.customer_trx_id
AND lgd3.account_set_flag = 'N'
AND ctl.customer_trx_line_id = lgd3.customer_trx_line_id
AND (
ctl.line_type IN ('TAX', 'FREIGHT', 'CHARGES', 'SUSPENSE')
OR
(ctl.line_type = 'LINE' AND ctl.accounting_rule_id IS NULL)
)
AND ctl.request_id = g_autoinv_request_id
AND ctl.customer_trx_id = p_customer_trx_id
AND NVL(t.previous_customer_trx_id, -1) =
DECODE(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
NVL(t.previous_customer_trx_id, -1) )
GROUP BY
ctl.customer_trx_line_id,
DECODE(lgd3.account_class,'UNEARN','REV',lgd3.account_class),
lgd3.gl_date,
rec3.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
t.exchange_rate
HAVING (
SUM(NVL(lgd3.amount, 0))
<> DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0)
OR
SUM(NVL(lgd3.acctd_amount, 0)) <>
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND(DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',
lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(t.exchange_rate,1),
p_base_precision),
ROUND( (DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',
lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(t.exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
UNION
SELECT /*+ leading(CTL2 LGD5,REC5)
use_hash(LGD5) index(CTL2 RA_CUSTOMER_TRX_LINES_N4)
index(REC5 RA_CUST_TRX_LINE_GL_DIST_N6)
index(LGD5 RA_CUST_TRX_LINE_GL_DIST_N6) */
TO_NUMBER(
MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
lgd5.account_set_flag,
'REVN', lgd5.cust_trx_line_gl_dist_id,
'REVY', lgd5.cust_trx_line_gl_dist_id,
'TAXN', lgd5.cust_trx_line_gl_dist_id,
'TAXY', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTN', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTY', lgd5.cust_trx_line_gl_dist_id,
'CHARGESN', lgd5.cust_trx_line_gl_dist_id,
'CHARGESY', lgd5.cust_trx_line_gl_dist_id,
'UNEARNY', lgd5.cust_trx_line_gl_dist_id,
'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
NULL
)
)
)
FROM
ra_cust_trx_line_gl_dist rec5,
ra_cust_trx_line_gl_dist lgd5,
ra_customer_trx_lines ctl2
WHERE
ctl2.customer_trx_id = p_customer_trx_id
AND ctl2.request_id = g_autoinv_request_id
AND rec5.customer_trx_id = lgd5.customer_trx_id
AND rec5.account_class = 'REC'
AND rec5.latest_rec_flag = 'Y'
AND lgd5.customer_trx_line_id = ctl2.customer_trx_line_id
AND lgd5.customer_trx_id = p_customer_trx_id
AND (
ctl2.line_type IN ('TAX', 'FREIGHT', 'CHARGES', 'SUSPENSE')
OR
(ctl2.line_type = 'LINE' AND
(ctl2.accounting_rule_id IS NULL OR
lgd5.account_set_flag = 'Y')
)
)
GROUP BY
lgd5.customer_trx_line_id,
lgd5.gl_date,
rec5.gl_date,
lgd5.account_set_flag,
DECODE(lgd5.account_set_flag,
'N', NULL,
lgd5.account_class)
HAVING SUM(NVL(lgd5.percent, 0)) <>
DECODE( NVL(lgd5.gl_date, rec5.gl_date),
rec5.gl_date, 100,
0)
);
UPDATE ra_cust_trx_line_gl_dist lgd
SET (amount, acctd_amount) =
(SELECT NVL(lgd.amount, 0) -
(
SUM(lgd2.amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount)
)
), /* entered amount */
NVL(lgd.acctd_amount, 0) -
(
SUM(lgd2.acctd_amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND(DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(ct.exchange_rate,1),
p_base_precision ),
ROUND( (DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(ct.exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
) /* accounted amount */
FROM
ra_cust_trx_line_gl_dist lgd2,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist rec1
WHERE
rec1.customer_trx_id = lgd.customer_trx_id
AND rec1.account_class = 'REC'
AND rec1.latest_rec_flag = 'Y'
AND ct.customer_trx_id = rec1.customer_trx_id
AND ctl.customer_trx_id = ct.customer_trx_id
AND ctl.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd2.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd2.account_class = lgd.account_class
AND lgd2.account_set_flag = 'N'
AND NVL(lgd2.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd.gl_date, to_date( 2415021, 'J') )
GROUP BY
ctl.customer_trx_line_id,
DECODE(lgd2.account_class,'UNEARN','REV',
lgd2.account_class),
rec1.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
ct.exchange_rate
),
percent =
(SELECT DECODE(lgd.account_class || lgd.account_set_flag,
'SUSPENSEN', lgd.percent,
'UNBILLN', lgd.percent,
'UNEARNN', lgd.percent,
NVL(lgd.percent, 0) -
(
SUM(NVL(lgd4.percent, 0))
- DECODE(rec2.gl_date,
NVL(lgd.gl_date, rec2.gl_date),
100, 0)
)
) /* percent */
FROM
ra_cust_trx_line_gl_dist lgd4,
ra_cust_trx_line_gl_dist rec2
WHERE
rec2.customer_trx_id = lgd.customer_trx_id
AND rec2.account_class = 'REC'
AND rec2.latest_rec_flag = 'Y'
AND lgd4.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd4.account_set_flag = lgd.account_set_flag
AND DECODE(lgd4.account_set_flag,
'Y', lgd4.account_class,
lgd.account_class) = lgd.account_class
AND NVL(lgd4.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd.gl_date, to_date( 2415021, 'J') )
GROUP BY
rec2.gl_date,
lgd.gl_date
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id IN
(SELECT MIN(DECODE(lgd3.gl_posted_date,
NULL, lgd3.cust_trx_line_gl_dist_id,
NULL) )
FROM
ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist lgd3,
ra_cust_trx_line_gl_dist rec3,
ra_customer_trx t
WHERE
t.customer_trx_id = p_customer_trx_id
AND rec3.customer_trx_id = t.customer_trx_id
AND rec3.account_class = 'REC'
AND rec3.latest_rec_flag = 'Y'
AND lgd3.customer_trx_id = t.customer_trx_id
AND lgd3.account_set_flag = 'N'
AND ctl.customer_trx_line_id = lgd3.customer_trx_line_id
AND (
ctl.line_type IN ('TAX', 'FREIGHT', 'CHARGES', 'SUSPENSE')
OR
(ctl.line_type = 'LINE' AND ctl.accounting_rule_id IS NULL)
)
AND NVL(t.previous_customer_trx_id, -1) =
DECODE(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
NVL(t.previous_customer_trx_id, -1) )
GROUP BY
ctl.customer_trx_line_id,
DECODE(lgd3.account_class,'UNEARN','REV',lgd3.account_class),
lgd3.gl_date,
rec3.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
t.exchange_rate
HAVING (
SUM(NVL(lgd3.amount, 0))
<> DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0)
OR
SUM(NVL(lgd3.acctd_amount, 0)) <>
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND(DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',
lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(t.exchange_rate,1),
p_base_precision),
ROUND( (DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',
lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(t.exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
UNION
SELECT /*+ index( REC5 RA_CUST_TRX_LINE_GL_DIST_N6) */
TO_NUMBER(
MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
lgd5.account_set_flag,
'REVN', lgd5.cust_trx_line_gl_dist_id,
'REVY', lgd5.cust_trx_line_gl_dist_id,
'TAXN', lgd5.cust_trx_line_gl_dist_id,
'TAXY', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTN', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTY', lgd5.cust_trx_line_gl_dist_id,
'CHARGESN', lgd5.cust_trx_line_gl_dist_id,
'CHARGESY', lgd5.cust_trx_line_gl_dist_id,
'UNEARNY', lgd5.cust_trx_line_gl_dist_id,
'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
NULL
)
)
)
FROM
ra_cust_trx_line_gl_dist rec5,
ra_cust_trx_line_gl_dist lgd5,
ra_customer_trx_lines ctl2
WHERE
rec5.customer_trx_id = p_customer_trx_id
AND rec5.account_class = 'REC'
AND rec5.latest_rec_flag = 'Y'
AND rec5.customer_trx_id = ctl2.customer_trx_id
AND ctl2.customer_trx_line_id = lgd5.customer_trx_line_id
AND lgd5.account_set_flag =
DECODE(ctl2.line_type, 'LINE',
DECODE(ctl2.accounting_rule_id, NULL, 'N', 'Y'),
lgd5.account_set_flag)
GROUP BY
lgd5.customer_trx_line_id,
lgd5.gl_date,
rec5.gl_date,
lgd5.account_set_flag,
DECODE(lgd5.account_set_flag,
'N', NULL,
lgd5.account_class)
HAVING SUM(NVL(lgd5.percent, 0)) <>
DECODE( NVL(lgd5.gl_date, rec5.gl_date),
rec5.gl_date, 100,
0)
);
UPDATE ra_cust_trx_line_gl_dist lgd
SET (amount, acctd_amount) =
(SELECT NVL(lgd.amount, 0) -
(
SUM(lgd2.amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount)
)
), /* entered amount */
NVL(lgd.acctd_amount, 0) -
(
SUM(lgd2.acctd_amount) -
(
DECODE(lgd.gl_date,
rec1.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND(DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(ct.exchange_rate,1),
p_base_precision ),
ROUND( (DECODE(DECODE(lgd2.account_class,
'UNEARN','REV',
lgd2.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(ct.exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
) /* accounted amount */
FROM
ra_cust_trx_line_gl_dist lgd2,
ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_cust_trx_line_gl_dist rec1
WHERE
rec1.customer_trx_id = lgd.customer_trx_id
and rec1.account_class = 'REC'
and rec1.latest_rec_flag = 'Y'
and ct.customer_trx_id = rec1.customer_trx_id
and ctl.customer_trx_id = ct.customer_trx_id
and ctl.customer_trx_line_id = lgd.customer_trx_line_id
and lgd2.customer_trx_line_id = lgd.customer_trx_line_id
and lgd2.account_class = lgd.account_class
and lgd2.account_set_flag = 'N'
and NVL(lgd2.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd.gl_date, to_date( 2415021, 'J') )
GROUP BY
ctl.customer_trx_line_id,
DECODE(lgd2.account_class,'UNEARN','REV',
lgd2.account_class),
rec1.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
ct.exchange_rate
),
percent =
(SELECT DECODE(lgd.account_class || lgd.account_set_flag,
'SUSPENSEN', lgd.percent,
'UNBILLN', lgd.percent,
'UNEARNN', lgd.percent,
NVL(lgd.percent, 0) -
(
SUM(NVL(lgd4.percent, 0))
- DECODE(rec2.gl_date,
NVL(lgd.gl_date, rec2.gl_date),
100, 0)
)
) /* percent */
FROM
ra_cust_trx_line_gl_dist lgd4,
ra_cust_trx_line_gl_dist rec2
WHERE
rec2.customer_trx_id = lgd.customer_trx_id
AND rec2.account_class = 'REC'
AND rec2.latest_rec_flag = 'Y'
AND lgd4.customer_trx_line_id = lgd.customer_trx_line_id
AND lgd4.account_set_flag = lgd.account_set_flag
AND DECODE(lgd4.account_set_flag,
'Y', lgd4.account_class,
lgd.account_class) = lgd.account_class
AND NVL(lgd4.gl_date, to_date( 2415021, 'J') ) =
NVL(lgd.gl_date, to_date( 2415021, 'J') )
GROUP BY
rec2.gl_date,
lgd.gl_date
),
last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id IN
(SELECT MIN(DECODE(lgd3.gl_posted_date,
NULL, lgd3.cust_trx_line_gl_dist_id,
NULL) )
FROM
ra_cust_trx_line_gl_dist lgd3,
ra_cust_trx_line_gl_dist rec3,
ra_customer_trx t,
ra_customer_trx_lines ctl
WHERE
ctl.customer_trx_line_id = p_customer_trx_line_id
AND t.customer_trx_id = ctl.customer_trx_id
AND rec3.customer_trx_id = t.customer_trx_id
AND rec3.account_class = 'REC'
AND rec3.latest_rec_flag = 'Y'
AND (
ctl.line_type IN ('TAX', 'FREIGHT', 'CHARGES', 'SUSPENSE')
OR
(ctl.line_type = 'LINE' AND ctl.accounting_rule_id IS NULL)
)
AND lgd3.customer_trx_line_id = ctl.customer_trx_line_id
AND lgd3.account_set_flag = 'N'
AND NVL(t.previous_customer_trx_id, -1) =
DECODE(p_trx_class_to_process,
'INV', -1,
'REGULAR_CM', t.previous_customer_trx_id,
NVL(t.previous_customer_trx_id, -1) )
GROUP BY
ctl.customer_trx_line_id,
DECODE(lgd3.account_class,'UNEARN','REV',lgd3.account_class),
lgd3.gl_date,
rec3.gl_date,
ctl.extended_amount,
ctl.revenue_amount,
t.exchange_rate
HAVING (
SUM(NVL(lgd3.amount, 0))
<> DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0)
OR
SUM(NVL(lgd3.acctd_amount, 0)) <>
DECODE(lgd3.gl_date,
rec3.gl_date, 1,
0) *
DECODE(p_base_mau,
NULL, ROUND(DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',
lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(t.exchange_rate,1),
p_base_precision),
ROUND( (DECODE(DECODE(lgd3.account_class,
'UNEARN','REV',
lgd3.account_class),
'REV', ctl.revenue_amount,
'SUSPENSE', ctl.extended_amount -
ctl.revenue_amount,
ctl.extended_amount) *
NVL(t.exchange_rate,1)
) / p_base_mau
) * p_base_mau
)
)
UNION
SELECT TO_NUMBER(
MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
lgd5.account_set_flag,
'REVN', lgd5.cust_trx_line_gl_dist_id,
'REVY', lgd5.cust_trx_line_gl_dist_id,
'TAXN', lgd5.cust_trx_line_gl_dist_id,
'TAXY', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTN', lgd5.cust_trx_line_gl_dist_id,
'FREIGHTY', lgd5.cust_trx_line_gl_dist_id,
'CHARGESN', lgd5.cust_trx_line_gl_dist_id,
'CHARGESY', lgd5.cust_trx_line_gl_dist_id,
'UNEARNY', lgd5.cust_trx_line_gl_dist_id,
'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
NULL) )
)
FROM
ra_cust_trx_line_gl_dist lgd5,
ra_cust_trx_line_gl_dist rec5,
ra_customer_trx_lines ctl2
WHERE
ctl2.customer_trx_line_id = p_customer_trx_line_id
AND rec5.customer_trx_id = lgd5.customer_trx_id
AND rec5.account_class = 'REC'
AND rec5.latest_rec_flag = 'Y'
AND lgd5.customer_trx_line_id = ctl2.customer_trx_line_id
AND (
ctl2.line_type IN ('TAX', 'FREIGHT', 'CHARGES', 'SUSPENSE')
OR
(ctl2.line_type = 'LINE' AND
(ctl2.accounting_rule_id IS NULL OR
lgd5.account_set_flag = 'Y')
)
)
GROUP BY
lgd5.customer_trx_line_id,
lgd5.gl_date,
rec5.gl_date,
lgd5.account_set_flag,
DECODE(lgd5.account_set_flag,
'N', NULL,
lgd5.account_class)
HAVING SUM(NVL(lgd5.percent, 0)) <>
DECODE( NVL(lgd5.gl_date, rec5.gl_date),
rec5.gl_date, 100,
0)
);
| The new function is broken into two pieces and relies upon bulk updates
| to update multiple rows at one time. The first component is the
| driving cursor that identifies the specific lines that require rounding
| (customer_trx_line_id, account_class, amount, acctd_amount, and percent).
| The amount, acctd_amount, and percent are all DELTA values (the amount of
| rounding required. To avoid problems with partially generated CMs (via
| ARTECMMB.pls, this logic will not round if the autorule_complete_flag is
| not null. To avoid issues with old transactions, I now skip lines
| that have no unposted distributions.
|
| The second component is an update statement that is fed by a second
| (included) subquery that identifies the specific gl_dist lines to update
| for each customer_trx_line_id. This routine will always update the
| gl_dist line with the latest gl_date, highest amount, and if the prior
| two columns are the same, max(gl_dist_id). This means that gl_dist_id
| is now only the tiebreaker, not the driving column. For bug 2495595,
| we now only consider rows with posting_control_id = -3 to be recipients
| of rounding amounts.
|
| Another noteworthy feature as of bug 2390821 is that we now round
| the REV, UNEARN (rec offset), and UNEARN (rev offset) separately.
| This was necessary because the original logic assummed (incorrectly)
| that the rec_offset UNEARN or UNBILL rows would be in balance naturally.
|
| In bug 2480898, 2493896, and 2497841, we learned that older transactions
| that do not have rec_offset_flag set will be corrupted if they pass
| through the rounding logic again. This happens if users manipulate
| the distributions of a completed and posted rule-based transaction.
| So, we now watch for transactions that do not have the rec_offset_flag
| set and set them where possible. Will will not round a transaction line
| unless there is a rec_offset_flag=Y row for that line.
|
| In bug 2535023 (see bug 2543675), we discovered that older versions of
| autoaccounting and unexpected behavior in ARXTWMAI can lead to situations
| where distributions are out of balance in interim (not last) period(s).
| When rounding fires, it would correct (but in last period) creating out
| of balance entries in two or more periods. To prevent this, we included
| a new procedure called true_lines_by_gl_date to push rows back in synch
| before we actually round them for the line in total.
|
| In bug 2449955, we figured out that we were not handling deferred
| lines on ARREARS invoices properly. We should treat them as if they
| were not deferred at all (just like conventional non-deferred rules).
|
| In bugs 6325023 and 6473284, we learned that SLA will not post
| distributions with entered and acctd amounts having opposite signs.
| Since this is possible for transactions that are not in functional
| currency with very small line amounts (<.20). To resolve that,
| we added logic to detect these situations and to insert a separate
| distributions to record amount and percent corrections and another
| distribution if the acctd_amount correction is the wrong sign.
|
| For example, if the rounding correction would reverse the sign of
| the acctd_amount, then we will insert a separate distribution to
| record that correction. However, if the entered and acctd corrections
| are themselves of opposite signs, then we'll insert one positive
| and a separate one with zero amount and negative acctd_amount.
|
| This matrix helps explain what we round each line (by account_class)
| to:
|
| CLASS ROF DEF RULE RESULT NOTES
| REV N N -2/-3 rev_amt
| REV N Y -2 0 form adjustments
| REV N Y -3 rev_amt
| UE N N -2/-3 rev_amt*-1
| UE Y N -2/-3 rev_amt
| UE N Y -2 0 form adjustments
| UE Y Y -2/-3 rev_amt
| UE N Y -3 rev_amt overrides deferred rules
|
| REQUIRES |
| All IN parameters |
| |
| RETURNS |
| TRUE if no errors occur |
| An ORACLE ERROR EXCEPTION if an ORACLE error occurs |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
|
| Created by bug 2150541
|
| 06-JUN-2002 M Raymond 2398021 Restructured both select and update
| to accomodate rounding of the
| rec_offset_rows. |
| 09-JUL-2002 M Raymond 2445800 Added a where clause to accomodate
| CMs against invoices that have been
| reversed and regenerated by RAM.
| 31-JUL-2002 M Raymond 2487744 Modified logic for deferred rules
| to round CMs against deferred invoices.
| 02-AUG-2002 M Raymond 2492345 Exclude model rows when determining
| the max gl_date
| 03-AUG-2002 M Raymond 2497841 Test and (when necessary) set the
| rec_offset_flag
| Added parameter for suppressing
| rec_offset_flag on calls from
| revenue recognition.
| 20-AUG-2002 M Raymond 2480852 Change handling of deferred rules
| and revenue adjustments.
| 20-AUG-2002 M Raymond 2480852 Exclude posted rows from being
| recipients of rounding amounts.
| 26-AUG-2002 M Raymond 2532648 Exclude posted rows from rounding
| completely.
| 27-AUG-2002 M Raymond 2532648 Re-implemented skipping of lines
| bearing deferred rules.
| 04-SEP-2002 M Raymond 2535023 Revised SELECT to carefully round
| form adjustments on deferred rule lines
| to zero instead of extended amount.
| 05-SEP-2002 M Raymond 2535023 Added a separate private procedure
| called true_lines_by_gl_date. Now
| calling this routine to make sure
| gl_dates in all periods balance before
| I round the line in total.
| 10-SEP-2002 M Raymond 2559944 Not handling deferred lines for
| ARREARS invoices properly. Revised
| CURSOR to properly ignore defers
| on ARREARS invoices.
| 13-SEP-2002 M Raymond 2543576 Switched from extended_amount to
| revenue_amount. This accomodates
| situations where suspense accounts are
| in use. Just FYI, ext_amt = qty * prc
| and rev_amt can equal ext_amt unless
| the user passed a different ext_amt
| via autoinvoice and had clearing
| enabled. The amt passed by user
| and used for line is stored in
| revenue_amount
| 14-SEP-2002 M Raymond 2569870 Prevented RAM dists from being
| recipient of rounding (UPDATE). Also
| changed SELECT to exclude rule-based
| lines when no rec_offset row exists.
| 06-MAR-2003 M Raymond 2632863 Fixed rounding errors when dist in
| last period was of opposite sign
| ex: CM vs .2/12 invoice
| 02-OCT-2003 M Raymond 3033850/3067588
| Modified code to execute three times
| for same, opposite, and zero rounding.
| Also removed sign subquery.
| 04-MAY-2004 M Raymond 3605089 Added logic for SUSPENSE to this
| logic to round for salescredit
| splits. later removed logic as
| it does not resolve issue at hand.
| See ARPLCREB.pls 115.64 if SUSPENSE
| rounding comes in conjunction with
| salescredits.
| 06-OCT-2007 M Raymond 6325023/6473284 - Added logic to handle
| unusual rounding issues for
| acctd amounts.
+-------------------------------------------------------------------------*/
FUNCTION correct_rule_records_by_line(
P_REQUEST_ID IN NUMBER,
P_CUSTOMER_TRX_ID IN NUMBER,
P_ROWS_PROCESSED IN OUT NOCOPY NUMBER,
P_ERROR_MESSAGE OUT NOCOPY VARCHAR2,
P_BASE_PRECISION IN NUMBER,
P_BASE_MAU IN NUMBER,
P_TRX_CLASS_TO_PROCESS IN VARCHAR2,
P_CHECK_RULES_FLAG IN VARCHAR2,
P_PERIOD_SET_NAME IN OUT NOCOPY VARCHAR2,
P_FIX_REC_OFFSET IN VARCHAR2 DEFAULT 'Y')
RETURN NUMBER IS
t_line_id l_line_id_type;
would be to put it in the UPDATE instead, thus limiting the number
of times it gets called.*/
CURSOR round_rows_by_trx(p_trx_id NUMBER,
p_base_mau NUMBER,
p_base_precision NUMBER) IS
select l.customer_trx_line_id, g.account_class,
/* AMOUNT LOGIC */
(DECODE(g.rec_offset_flag, 'Y', l.revenue_amount,
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(t.invoicing_rule_id, -2, 0, l.revenue_amount),
l.revenue_amount))
- (sum(g.amount) *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1))))
* DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1)) ROUND_AMT,
/* PERCENT LOGIC */
(DECODE(g.rec_offset_flag, 'Y', 100,
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(t.invoicing_rule_id, -2, 0, 100),
100))
- (sum(g.percent) *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1))))
* DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1)) ROUND_PCT,
/* ACCTD_AMOUNT LOGIC */
(DECODE(p_base_mau, NULL,
ROUND(DECODE(g.rec_offset_flag, 'Y', l.revenue_amount,
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(t.invoicing_rule_id, -2, 0, l.revenue_amount),
l.revenue_amount))
* nvl(t.exchange_rate,1), p_base_precision),
ROUND((DECODE(g.rec_offset_flag, 'Y', l.revenue_amount,
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(t.invoicing_rule_id, -2, 0, l.revenue_amount),
l.revenue_amount))
* nvl(t.exchange_rate,1)) / p_base_mau) * p_base_mau)
- (sum(g.acctd_amount) *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1))))
* DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1)) ROUND_ACCT_AMT,
/* END ACCTD_AMOUNT LOGIC */
g.rec_offset_flag
from ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist g,
ra_customer_trx t,
ra_rules r
where t.customer_trx_id = p_trx_id
and l.customer_trx_id = t.customer_trx_id
and l.customer_trx_id = g.customer_trx_id
and l.customer_trx_line_id = g.customer_trx_line_id
/* Skip any entries created by revenue adjustments
or for deferred rules */
and l.accounting_rule_id = r.rule_id
and g.revenue_adjustment_id is NULL
/* Only round transaction lines with rules */
and l.accounting_rule_id is not NULL
and l.autorule_complete_flag is NULL
and g.account_class IN ('REV','UNEARN','UNBILL')
and g.account_set_flag = 'N'
/* Only round lines that actually have a rec_offset row */
and exists ( SELECT 'has rof row'
FROM ra_cust_trx_line_gl_dist rof
WHERE rof.customer_trx_line_id = g.customer_trx_line_id
AND rof.account_set_flag = 'N'
AND rof.account_class in ('UNEARN','UNBILL')
AND rof.rec_offset_flag = 'Y')
having
/* AMOUNT LOGIC */
(sum(g.amount) <> DECODE(g.account_class, 'REV', l.revenue_amount,
DECODE(g.rec_offset_flag, 'Y', l.revenue_amount,
l.revenue_amount * -1)) *
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(g.rec_offset_flag, 'Y', 1,
DECODE(t.invoicing_rule_id, -2, 0, 1)),1) or
/* PERCENT LOGIC */
sum(g.percent) <> DECODE(g.account_class, 'REV', 100,
DECODE(g.rec_offset_flag, 'Y', 100, -100)) *
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(g.rec_offset_flag, 'Y', 1,
DECODE(t.invoicing_rule_id, -2, 0, 1)),1) or
/* ACCTD_AMOUNT LOGIC */
sum(g.acctd_amount) <> DECODE(p_base_mau, NULL,
ROUND(l.revenue_amount * nvl(t.exchange_rate,1), p_base_precision),
ROUND((l.revenue_amount * nvl(t.exchange_rate,1)) /
p_base_mau) * p_base_mau) *
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(g.rec_offset_flag, 'Y', 1,
DECODE(t.invoicing_rule_id, -2, 0, 1)),1) *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1)))
/* Only round lines w/unposted distributions */
and min(g.posting_control_id) = -3
group by l.customer_trx_line_id, g.account_class, g.rec_offset_flag,
l.revenue_amount, t.exchange_rate, r.deferred_revenue_flag,
t.invoicing_rule_id;
With this cursor and subsequent UPDATE, we detect situations
where REV, UNEARN, or UNEARN(rof) for each line do not total
to the revenue_amount of the line. This routine assumes that
the previous one has executed and that everything is already
in balance by gl_date.
NOTE: Under normal circumstances, this routine will only make
changes to distributions as part of Revenue Recognition. It
should not make changes based on form-level adjustments or
RAM adjustments (after Revenue Recognition has completed).
As of bug 3033850, I revised the rounding logic to execute up
to three separate times/phases to handle unusual cases (opposite sign,
zero dists) The code will execute first for same sign rounding,
then opposite sign, and finally, using zero sign dists. The code
should be able to detect if rounding is complete and exit after
having rounded all the distributions. Even if only 1 or two phases
have been completed.
The phases/passes are:
1=Dists with any sign (same, opposite, or zero) as line (UPDATE)
2=Dists where corrections cause signs to mismatch (+/-) (INSERT)
3=Continuation of 4, corrections themselves have opposite signs (INSERT)
Note: phase 2 and 3 will only function if the acctd_amount
correction is a different sign than the entered amount.
*/
OPEN round_rows_by_trx(P_CUSTOMER_TRX_ID, P_BASE_MAU, P_BASE_PRECISION);
/* Now update all the rows that require it */
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('Rows that need rounding: ' || l_rows_needing_rounding);
simplified the update logic. The original phases 1-3
are now handled in a single call (phase 1). The original
phases 4 and 5 are now 2 and 3 respectively. The need
for phases 1-3 was replaced in the simplified logic by
DECODES that map a '3', '2', or '1' as 9th digit in the sorted
string that uses gl_date, amount, and gl_dist_id. This logic
was forward ported from version 115.59.15101.3. */
/* In the logic below, we fetch the gl_date, a single
digit (3, 2, or 1) representing signs, the amount, and
the gl_dist_id and append them in that order.. the result
looks like this:
200908123000000000000123.710000000000123412341234
GL_DATE|#|GL_DIST_AMOUNT__|GL_DIST_ID___________|
In this example, the gl_date of this gl_dist row
is 12-AUG-2009. The '3' indicates that the gl_dist
amount and line.revenue_amount are of same sign.
The gl_dist.amount is 123.71 and the gl_dist_id is
123412341234. The sql would return only the gl_dist_id
of the distribution for each account class to be
rounded.
The sql selects one REV, one UNEARN(rof), and
one UNEARN(non-rof) for each trx_line_id. */
FORALL i IN t_line_id.FIRST .. t_line_id.LAST
UPDATE ra_cust_trx_line_gl_dist
SET amount = amount + t_round_amount(i),
percent = percent + t_round_percent(i),
acctd_amount = acctd_amount + t_round_acctd(i),
last_updated_by = arp_global.last_updated_by,
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id in (
/* Bug 4082528 - Select restructured */
/* START OF GL_DIST_ID SELECT */
select
to_number(substr(max(
to_char(g.gl_date,'YYYYMMDD') ||
decode(sign(g.amount *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1))),
sign(tl.revenue_amount), '3',
sign(tl.revenue_amount * -1), '2', '1') ||
ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
ltrim(to_char(g.cust_trx_line_gl_dist_id,
'0999999999999999999999'))),28))
from ra_cust_trx_line_gl_dist g,
ra_customer_trx_lines tl
where g.customer_trx_line_id = t_line_id(i)
and tl.customer_trx_line_id = g.customer_trx_line_id
and g.account_class = t_account_class(i)
and g.account_set_flag = 'N'
/* ONLY USE UNPOSTED ROWS */
and g.posting_control_id = -3
/* ONLY CONSIDERS REC_OFFSET_FLAG IF NOT NULL */
and nvl(g.rec_offset_flag, '~') = nvl(t_rec_offset(i), '~')
/* DO NOT ROUND RAM DISTRIBUTIONS */
and g.revenue_adjustment_id is null
/* SKIP UPDATE IF SIGNS ARE OPPOSITE */
and (sign(g.amount + t_round_amount(i)) =
sign(g.acctd_amount + t_round_acctd(i)) or
sign(g.amount + t_round_amount(i)) = 0)
/* END OF GL_DIST_ID SELECT */
);
INSERT INTO RA_CUST_TRX_LINE_GL_DIST
(CUST_TRX_LINE_GL_DIST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
POSTING_CONTROL_ID,
SET_OF_BOOKS_ID,
CUSTOMER_TRX_LINE_ID,
CUSTOMER_TRX_ID,
ACCOUNT_CLASS,
CODE_COMBINATION_ID,
AMOUNT,
ACCTD_AMOUNT,
PERCENT,
GL_DATE,
ORIGINAL_GL_DATE,
ACCOUNT_SET_FLAG,
COMMENTS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LATEST_REC_FLAG,
USSGL_TRANSACTION_CODE,
REC_OFFSET_FLAG,
USER_GENERATED_FLAG,
ORG_ID,
REQUEST_ID,
CUST_TRX_LINE_SALESREP_ID,
ROUNDING_CORRECTION_FLAG
)
SELECT
RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
-3,
SET_OF_BOOKS_ID,
CUSTOMER_TRX_LINE_ID,
CUSTOMER_TRX_ID,
ACCOUNT_CLASS,
CODE_COMBINATION_ID,
DECODE(l_phase, 2, t_round_amount(i), 0),
DECODE(l_phase, 2,
DECODE(SIGN(t_round_amount(i)),0,t_round_acctd(i),
ABS(t_round_acctd(i)) * SIGN(t_round_amount(i))),
t_round_acctd(i) * 2),
DECODE(l_phase, 2, t_round_percent(i), 0),
GL_DATE,
ORIGINAL_GL_DATE,
ACCOUNT_SET_FLAG,
'PHASE ' || l_phase || ': Rounding correction derived from ' ||
cust_trx_line_gl_dist_id,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LATEST_REC_FLAG,
USSGL_TRANSACTION_CODE,
REC_OFFSET_FLAG,
USER_GENERATED_FLAG,
ORG_ID,
REQUEST_ID,
CUST_TRX_LINE_SALESREP_ID,
'Y'
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUST_TRX_LINE_GL_DIST_ID IN (
/* SELECT GL_DIST_ID FOR EACH LINE THAT
REQUIRES ROUNDING */
select
to_number(substr(max(
to_char(g.gl_date,'YYYYMMDD') ||
decode(sign(g.amount *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1))),
sign(tl.revenue_amount), '3',
sign(tl.revenue_amount * -1), '2', '1') ||
ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
ltrim(to_char(g.cust_trx_line_gl_dist_id,
'0999999999999999999999'))),28))
from ra_cust_trx_line_gl_dist g,
ra_customer_trx_lines tl
where g.customer_trx_line_id = t_line_id(i)
and tl.customer_trx_line_id = g.customer_trx_line_id
and g.account_class = t_account_class(i)
and g.account_set_flag = 'N'
/* ONLY USE UNPOSTED ROWS */
and g.posting_control_id = -3
/* ONLY CONSIDERS REC_OFFSET_FLAG IF NOT NULL */
and nvl(g.rec_offset_flag, '~') = nvl(t_rec_offset(i), '~')
/* DO NOT ROUND RAM DISTRIBUTIONS */
and g.revenue_adjustment_id is null
/* END OF GL_DIST_ID SELECT */
);
/* This piece of code determines that 1 row was updated
for each invoice line and account class. Once the
row is updated, we need to remove it from further
consideration. To do that, we change the line_id
to line_id * -1 (a row that should never exist)
and this prevents it from being processed in
subsequent passes.
Incidentally, I tried to just delete the
processed rows - but this caused subsequent
passes to fail with ORA errors due to missing
plsql table rows. The bulk update requires
a continuous list in sequential order and, by deleting
rows from the table, we cause the update to fail.
*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug(' Target: ' || t_line_id(upd) ||
' ' || t_account_class(upd) ||
' ' || t_rec_offset(upd) ||
' ' || t_round_amount(upd) ||
' ' || t_round_acctd(upd) ||
' ' || t_round_percent(upd) ||
' ' || SQL%BULK_ROWCOUNT(upd));
/* This phase inserted complete dists
so no need to insert another dist */
l_rows_rounded_this_pass := l_rows_rounded_this_pass + 1;
forces the last phase and an insert of
a dist with amount=0 and acctd_amount=
*/
NULL;
/* make line_id negative so it causes no further updates */
t_line_id(upd) := -1 * t_line_id(upd);
one line is updated for a given customer_trx_line_id
and account_class. That would mean that the rounding
logic was unable to identify a single line for update
and rounding would then raise an error to roll back
any corrections or calculations for this transaction.
Revenue recognition has been modified to roll back
transactions that fail and to document the lines
that have problems. */
IF PG_DEBUG in ('Y', 'C')
THEN
FOR err in t_line_id.FIRST .. t_line_id.LAST LOOP
arp_standard.debug(t_line_id(err)|| ' ' ||
t_account_class(err) ||
' ' || t_rec_offset(err) ||
' ' || t_round_amount(err) ||
' ' || t_round_acctd(err) || ' ' ||
t_round_percent(err) || ' ' || SQL%BULK_ROWCOUNT(err));
arp_standard.debug('Mismatch between lines found and lines updated (see below)');
arp_standard.debug('Total number of rows updated: ' || l_rows_rounded);
select /*+ leading(gt t) index(l ra_customer_trx_lines_u1) index(g ra_cust_trx_line_gl_dist_n1)*/
l.customer_trx_line_id, g.account_class,
/* AMOUNT LOGIC */
(gt.amount
- (sum(g.amount)
* DECODE(g.account_class, 'REV',1,-1)))
* DECODE(g.account_class, 'REV',1,-1) ROUND_AMT,
/* END AMOUNT LOGIC */
/* Leaving percent alone for now */
0 ROUND_PCT,
/* ACCTD_AMOUNT LOGIC */
(DECODE(p_base_mau, NULL,
ROUND(gt.amount
* nvl(t.exchange_rate,1), p_base_precision),
ROUND((gt.amount
* nvl(t.exchange_rate,1))
/ p_base_mau) * p_base_mau)
- (sum(g.acctd_amount)
* DECODE(g.account_class, 'REV', 1, -1)))
* DECODE(g.account_class, 'REV', 1, -1) ROUND_ACCT_AMT,
/* END ACCTD_AMOUNT LOGIC */
gt.revenue_adjustment_id
from ra_customer_trx_lines l,
ar_line_rev_adj_gt gt,
ra_cust_trx_line_gl_dist g,
ra_customer_trx t
where t.customer_trx_id = gt.customer_trx_id
and l.customer_trx_id = t.customer_trx_id
and l.customer_trx_id = g.customer_trx_id
and l.customer_trx_line_id = g.customer_trx_line_id
/* Bug Number 6782307 -- Added the below join condition */
and l.customer_trx_line_id = gt.customer_trx_line_id
and g.revenue_adjustment_id = gt.revenue_adjustment_id
and l.autorule_complete_flag is NULL
and g.account_class IN ('REV','UNEARN','UNBILL')
and g.account_set_flag = 'N'
having
/* AMOUNT LOGIC */
(sum(g.amount) <> gt.amount *
DECODE(g.account_class, 'REV',1,-1) or
/* PERCENT LOGIC
sum(g.percent) <> DECODE(g.account_class, 'REV', 100,
DECODE(g.rec_offset_flag, 'Y', 100, -100)) *
DECODE(r.deferred_revenue_flag, 'Y',
DECODE(g.rec_offset_flag, 'Y', 1,
DECODE(t.invoicing_rule_id, -2, 0, 1)),1) or */
/* ACCTD_AMOUNT LOGIC */
sum(g.acctd_amount) <> DECODE(p_base_mau, NULL,
ROUND(gt.amount
* nvl(t.exchange_rate,1), p_base_precision),
ROUND((gt.amount
* nvl(t.exchange_rate,1)) /
p_base_mau) * p_base_mau) *
DECODE(g.account_class, 'REV', 1,-1))
/* Only round lines w/unposted distributions */
and min(g.posting_control_id) = -3
group by l.customer_trx_line_id, g.account_class,
gt.revenue_adjustment_id, gt.amount, t.exchange_rate;
With this cursor and subsequent UPDATE, we detect situations
where REV or UNEARN for each line do not total
to the adjustment amount of the line. This routine assumes that
the previous one has executed and that everything is already
in balance by gl_date.
The phases are 1=Dists with same sign as line
2=Dists with opposite sign as line
3=Dists with zero amount (when line is non-zero
4=Dists where acctd_amount sign changes */
OPEN round_rows_by_trx(AR_RAAPI_UTIL.g_min_acc_unit,
AR_RAAPI_UTIL.g_trx_precision);
/* Now update all the rows that require it */
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('Rows that need rounding: ' || l_rows_needing_rounding);
UPDATE ra_cust_trx_line_gl_dist
SET amount = amount + t_round_amount(i),
percent = percent + t_round_percent(i),
acctd_amount = acctd_amount + t_round_acctd(i),
last_updated_by = arp_global.last_updated_by,
last_update_date = sysdate
WHERE cust_trx_line_gl_dist_id in (
/* SELECT GL_DIST_ID FOR EACH LINE THAT
REQUIRES ROUNDING */
select MAX(g.cust_trx_line_gl_dist_id)
from ra_cust_trx_line_gl_dist g,
ra_cust_trx_line_gl_dist gmax,
ra_customer_trx_lines tl
where g.customer_trx_line_id = t_line_id(i)
and tl.customer_trx_line_id = g.customer_trx_line_id
and g.account_class = t_account_class(i)
and g.account_set_flag = 'N'
/* ONLY USE UNPOSTED ROWS */
and g.posting_control_id = -3
/* ONLY CONSIDERS NON-REC_OFFSET ROWS */
and g.rec_offset_flag IS NULL
/* only a specific rev_adj */
and g.revenue_adjustment_id = t_rev_adj_id(i)
/* FORCES USE OF ROW IN LAST PERIOD */
and g.gl_date = (
select max(gl_date)
from ra_cust_trx_line_gl_dist gdmax
where gdmax.customer_trx_line_id = g.customer_trx_line_id
and gdmax.account_class = g.account_class
and nvl(gdmax.rec_offset_flag, '~') =
nvl(g.rec_offset_flag, '~')
and gdmax.account_set_flag = 'N'
and gdmax.posting_control_id = -3
and gdmax.revenue_adjustment_id = t_rev_adj_id(i))
and gmax.customer_trx_line_id = g.customer_trx_line_id
and gmax.account_class = g.account_class
and gmax.account_set_flag = 'N'
and nvl(gmax.rec_offset_flag, '~') = nvl(g.rec_offset_flag, '~')
and gmax.gl_date = g.gl_date
/* ONLY RAM DISTRIBUTIONS */
and g.revenue_adjustment_id = gmax.revenue_adjustment_id
/* USE DISTS THAT MATCH SIGN OF LINE FIRST,
THEN OTHERS (ZERO, NEGATIVE). */
and (SIGN(g.amount) = SIGN(tl.revenue_amount) *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1)) *
DECODE(l_phase, 1, 1, 2, -1, 0))
/* SKIP UPDATE IF SIGNS AR OPPOSITE */
and (sign(g.amount + t_round_amount(i)) =
sign(g.acctd_amount + t_round_acctd(i)) or
sign(g.amount + t_round_amount(i)) = 0)
having
/* USE LINE WITH LARGEST ABS(AMOUNT) */
g.amount = decode(sign(g.amount), -1, MIN(gmax.amount),
1, MAX(gmax.amount),
0)
group by g.amount
/* END OF GL_DIST_ID SELECT */
);
Either is zero or they are same sign, then we update the
existing dists (phase 1-3), however, if the corrections force
the resulting amount or acctd to be a different sign, then
phase 4 and 5 may each insert additional distributions.
Phase 4 inserts a new distribution if the signs become
opposites after rounding. Phase 5 splits entered and
acctd when the amounts themselves are opposite signs
Based on bug 6473284, I'm going to coin a new phrase..
cases where the rounding is pennies is now called
near-zero rounding. Phases 4 and 5 are specific to
cases where the rounding amount is near-zero (pennies)
and the effect of that rounding makes the distributions
change signs unpredictably. This is just FYI */
FORALL i in t_line_id.first .. t_line_id.last
INSERT INTO RA_CUST_TRX_LINE_GL_DIST
(CUST_TRX_LINE_GL_DIST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
POSTING_CONTROL_ID,
SET_OF_BOOKS_ID,
CUSTOMER_TRX_LINE_ID,
CUSTOMER_TRX_ID,
ACCOUNT_CLASS,
CODE_COMBINATION_ID,
AMOUNT,
ACCTD_AMOUNT,
PERCENT,
GL_DATE,
ORIGINAL_GL_DATE,
ACCOUNT_SET_FLAG,
COMMENTS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LATEST_REC_FLAG,
USSGL_TRANSACTION_CODE,
REC_OFFSET_FLAG,
USER_GENERATED_FLAG,
ORG_ID,
REQUEST_ID,
CUST_TRX_LINE_SALESREP_ID,
REVENUE_ADJUSTMENT_ID,
EVENT_ID,
ROUNDING_CORRECTION_FLAG
)
SELECT
RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
-3,
SET_OF_BOOKS_ID,
CUSTOMER_TRX_LINE_ID,
CUSTOMER_TRX_ID,
ACCOUNT_CLASS,
CODE_COMBINATION_ID,
DECODE(l_phase, 4, t_round_amount(i), 0),
DECODE(l_phase, 4,
DECODE(SIGN(t_round_amount(i)),0,t_round_acctd(i),
ABS(t_round_acctd(i)) * SIGN(t_round_amount(i))),
t_round_acctd(i) * 2),
DECODE(l_phase, 4, t_round_percent(i), 0),
GL_DATE,
ORIGINAL_GL_DATE,
ACCOUNT_SET_FLAG,
'PHASE ' || l_phase || ': Rounding correction derived from ' ||
cust_trx_line_gl_dist_id,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LATEST_REC_FLAG,
USSGL_TRANSACTION_CODE,
REC_OFFSET_FLAG,
USER_GENERATED_FLAG,
ORG_ID,
REQUEST_ID,
CUST_TRX_LINE_SALESREP_ID,
REVENUE_ADJUSTMENT_ID,
EVENT_ID,
'Y'
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUST_TRX_LINE_GL_DIST_ID IN (
/* SELECT GL_DIST_ID FOR EACH LINE THAT
REQUIRES ROUNDING */
select
to_number(substr(max(
to_char(g.gl_date,'YYYYMMDD') ||
decode(sign(g.amount *
DECODE(g.account_class, 'REV', 1,
DECODE(g.rec_offset_flag, 'Y', 1, -1))),
sign(tl.revenue_amount), '3',
sign(tl.revenue_amount * -1), '2', '1') ||
ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
ltrim(to_char(g.cust_trx_line_gl_dist_id,
'0999999999999999999999'))),28))
from ra_cust_trx_line_gl_dist g,
ra_customer_trx_lines tl
where g.customer_trx_line_id = t_line_id(i)
and tl.customer_trx_line_id = g.customer_trx_line_id
and g.account_class = t_account_class(i)
and g.account_set_flag = 'N'
/* ONLY USE UNPOSTED ROWS */
and g.posting_control_id = -3
/* REVENUE ADJUSTMENTS DO NOT AFFECT REC OFFSET ROWS */
and g.rec_offset_flag IS NULL
/* ONLY ROUND RAM DISTRIBUTIONS */
and g.revenue_adjustment_id = t_rev_adj_id(i)
/* END OF GL_DIST_ID SELECT */
);
/* This piece of code determines that 1 row was updated
for each invoice line and account class. Once the
row is updated, we need to remove it from further
consideration. To do that, we change the line_id
to line_id * -1 (a row that should never exist)
and this prevents it from being processed in
subsequent passes.
Incidentally, I tried to just delete the
processed rows - but this caused subsequent
passes to fail with ORA errors due to missing
plsql table rows. The bulk update requires
a continuous list in sequential order and, by deleting
rows from the table, we cause the update to fail.
*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug(' Target: ' || t_line_id(upd) ||
' ' || t_account_class(upd) ||
' ' || t_round_amount(upd) ||
' ' || t_round_acctd(upd) ||
' ' || t_round_percent(upd) ||
' ' || SQL%BULK_ROWCOUNT(upd));
/* This phase inserted complete dists
so no need to insert another dist */
l_rows_rounded_this_pass := l_rows_rounded_this_pass + 1;
forces the last phase and an insert of
a dist with amount=0 and acctd_amount=
*/
NULL;
/* make line_id negative so it causes no further updates */
t_line_id(upd) := -1 * t_line_id(upd);
one line is updated for a given customer_trx_line_id
and account_class. That would mean that the rounding
logic was unable to identify a single line for update
and rounding would then raise an error to roll back
any corrections or calculations for this transaction.
Revenue recognition has been modified to roll back
transactions that fail and to document the lines
that have problems. */
IF PG_DEBUG in ('Y', 'C')
THEN
FOR err in t_line_id.FIRST .. t_line_id.LAST LOOP
arp_standard.debug(t_line_id(err)|| ' ' ||
t_account_class(err) ||
' ' || t_round_amount(err) ||
' ' || t_round_acctd(err) || ' ' ||
t_round_percent(err) || ' ' || SQL%BULK_ROWCOUNT(err));
arp_standard.debug('Mismatch between lines found and lines updated [see below]');
arp_standard.debug('Total number of rows updated: ' || l_rows_rounded);
DELETE from ar_line_rev_adj_gt;
PROCEDURE get_select_column_values(
P_SELECT_SQL_C IN INTEGER,
P_SELECT_REC IN OUT NOCOPY SELECT_REC_TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_rounding.get_select_column_values()+' );
dbms_sql.column_value( p_select_sql_c, 1,
p_select_rec.rec_customer_trx_id);
dbms_sql.column_value( p_select_sql_c, 2,
p_select_rec.rec_code_combination_id);
dbms_sql.column_value( p_select_sql_c, 3,
p_select_rec.round_customer_trx_id);
arp_standard.debug( 'arp_rounding.get_select_column_values()-' );
arp_standard.debug('EXCEPTION: arp_rounding.get_select_column_values()');
END get_select_column_values;
PROCEDURE dump_select_rec( P_SELECT_REC IN SELECT_REC_TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_rounding.dump_select_rec()+' );
arp_standard.debug(' Dumping select record: ');
p_select_rec.rec_customer_trx_id);
p_select_rec.rec_code_combination_id);
p_select_rec.round_customer_trx_id);
arp_standard.debug( 'arp_rounding.dump_select_rec()-' );
arp_standard.debug( 'EXCEPTION: arp_rounding.dump_select_rec()' );
END dump_select_rec;
PROCEDURE define_columns( P_SELECT_SQL_C IN INTEGER,
P_SELECT_REC IN SELECT_REC_TYPE) IS
BEGIN
arp_standard.debug( 'arp_rounding.define_columns()+' );
arp_standard.debug( ' Defining columns for select_sql_c');
dbms_sql.define_column( p_select_sql_c, 1,
p_select_rec.rec_customer_trx_id );
dbms_sql.define_column( p_select_sql_c, 2,
p_select_rec.rec_code_combination_id );
dbms_sql.define_column( p_select_sql_c, 3,
p_select_rec.round_customer_trx_id );
arp_standard.debug( 'EXCEPTION: Error defining columns for select_sql_c' );
PROCEDURE build_select_sql(
P_REQUEST_ID IN INTEGER,
P_CUSTOMER_TRX_ID IN INTEGER,
P_SELECT_SQL_C IN OUT NOCOPY INTEGER ) IS
l_select_sql VARCHAR2(1000);
arp_standard.debug( 'arp_rounding.build_select_sql()+' );
l_select_sql :=
'select rec.customer_trx_id,
rec.code_combination_id,
round.customer_trx_id
from
ra_cust_trx_line_gl_dist rec,
ra_cust_trx_line_gl_dist round
where
rec.customer_trx_id = round.customer_trx_id(+)
and rec.account_set_flag = round.account_set_flag(+)' ||
l_where_pred ||
'and rec.account_class = ''REC''
and rec.latest_rec_flag = ''Y''
and round.account_class(+) = ''ROUND''';
arp_standard.debug('select_sql = ' ||
l_select_sql);
arp_standard.debug('Parsing select stmt');
p_select_sql_c := dbms_sql.open_cursor;
dbms_sql.parse( p_select_sql_c, l_select_sql, dbms_sql.v7 );
dbms_sql.bind_variable(p_select_sql_c, ':p_customer_trx_id', p_customer_trx_id);
dbms_sql.bind_variable(p_select_sql_c, ':p_request_id', p_request_id);
arp_standard.debug('build_select_sql: ' || 'EXCEPTION: Error parsing select stmt' );
arp_standard.debug( 'arp_rounding.build_select_sql()-' );
arp_standard.debug( 'EXCEPTION: arp_rounding.build_select_sql()' );
END build_select_sql;
| This function inserts a record of account_class = ROUND into |
| ra_cust_trx_line_gl_dist table. If the transaction was created before |
| setting the header level rounding option On then this function will |
| insert the round record only if there is no activity on it otherwise |
| it will do the release 10 rounding (do_line_level_rounding). |
| Also if arp_rounding is called from revenue recognition program then |
| this function will not insert the ROUND record but revenue recognition|
| will insert it. |
| |
| REQUIRES |
| P_REQUEST_ID, P_CUSTOMER_TRX_ID |
| |
| RETURNS |
| TRUE if no errors occur |
| FALSE otherwise. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| |
+-------------------------------------------------------------------------*/
FUNCTION do_header_level_rounding
( P_REQUEST_ID IN NUMBER,
P_CUSTOMER_TRX_ID IN NUMBER,
P_CUSTOMER_TRX_LINE_ID IN NUMBER,
P_ROWS_PROCESSED IN OUT NOCOPY NUMBER,
P_ERROR_MESSAGE OUT NOCOPY VARCHAR2,
P_BASE_PRECISION IN NUMBER,
P_BASE_MIN_ACCOUNTABLE_UNIT IN VARCHAR2,
P_TRX_CLASS_TO_PROCESS IN VARCHAR2,
P_PERIOD_SET_NAME IN OUT NOCOPY VARCHAR2,
P_CHECK_RULES_FLAG IN VARCHAR2,
P_TRX_HEADER_LEVEL_ROUNDING IN VARCHAR2,
P_ACTIVITY_FLAG IN VARCHAR2,
P_TRX_HEADER_ROUND_CCID IN NUMBER,
P_FIX_REC_OFFSET IN VARCHAR2 DEFAULT 'Y'
)
RETURN NUMBER IS
l_select_rec select_rec_type;
l_null_rec CONSTANT select_rec_type := l_select_rec;
if ( insert_round_records( P_REQUEST_ID,
P_CUSTOMER_TRX_ID,
P_ROWS_PROCESSED,
P_ERROR_MESSAGE,
P_BASE_PRECISION,
P_BASE_MIN_ACCOUNTABLE_UNIT,
P_TRX_CLASS_TO_PROCESS,
P_TRX_HEADER_ROUND_CCID) = iFALSE)
then return(iFALSE);
build_select_sql( P_REQUEST_ID,
P_CUSTOMER_TRX_ID,
SELECT_SQL_C);
define_columns( select_sql_c, l_select_rec );
arp_standard.debug(' Executing select sql' );
l_ignore := dbms_sql.execute( select_sql_c );
arp_standard.debug('EXCEPTION: Error executing select sql' );
arp_standard.debug(' Fetching select stmt');
if (dbms_sql.fetch_rows( select_sql_c ) > 0)
then
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug(' fetched a row' );
l_select_rec := l_null_rec;
get_select_column_values( select_sql_c, l_select_rec );
dump_select_rec( l_select_rec );
l_customer_trx_id := l_select_rec.rec_customer_trx_id;
if (l_select_rec.round_customer_trx_id is null)
then
-- ROUND record does not exist for this transaction
-- This means the transaction was created before
-- setting TRX_HEADER_LEVEL_ROUNDING ON
-- Round the transaction with release 10 method
if ( do_line_level_rounding( l_REQUEST_ID,
l_CUSTOMER_TRX_ID,
P_CUSTOMER_TRX_LINE_ID,
P_ROWS_PROCESSED,
P_ERROR_MESSAGE,
P_BASE_PRECISION,
P_BASE_MIN_ACCOUNTABLE_UNIT,
P_PERIOD_SET_NAME,
P_CHECK_RULES_FLAG,
P_TRX_CLASS_TO_PROCESS,
P_FIX_REC_OFFSET) = iFALSE)
then return(iFALSE);
l_select_rec.rec_code_combination_id,
P_TRX_HEADER_ROUND_CCID) = iFALSE)
then return(iFALSE);
dbms_sql.close_cursor(select_sql_c);
l_select_rec select_rec_type;
l_null_rec CONSTANT select_rec_type := l_select_rec;
SELECT line_dist.acctd_amount,line_dist.amount,
arpcurr.currround(line_dist.amount * nvl(ct.exchange_rate,1),ct. invoice_currency_code)
INTO l_rec_acctd_amt,l_rec_amt,l_actual_acctd_amt
FROM ra_cust_trx_line_gl_dist line_dist,
ra_customer_trx ct
WHERE line_dist.customer_trx_id = ct.customer_trx_id
AND line_dist.customer_trx_id = p_customer_trx_id
AND line_dist.account_class = 'REC'
AND line_dist.account_set_flag = 'N'
AND line_dist.latest_rec_flag = 'Y'
AND ct.invoicing_rule_id is NULL;
UPDATE ra_cust_trx_line_gl_dist
SET acctd_amount = acctd_amount -
Decode(Sign(l_rec_acctd_amt),-1 ,(Abs(l_actual_acctd_amt)-Abs(l_rec_acctd_amt)),(l_rec_acctd_amt - l_actual_acctd_amt))
WHERE cust_trx_line_gl_dist_id =
(SELECT MAX(cust_trx_line_gl_dist_id)
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND account_class = 'REV'
AND account_set_flag = 'N'
AND sign(acctd_amount) = Decode(Sign(l_rec_acctd_amt),-1,
sign(Abs(l_actual_acctd_amt)-Abs(l_rec_acctd_amt)),sign(l_rec_acctd_amt - l_actual_acctd_amt)));
UPDATE ra_cust_trx_line_gl_dist
SET acctd_amount = l_actual_acctd_amt
WHERE customer_trx_id = p_customer_trx_id
AND account_class = 'REC'
AND account_set_flag = 'N'
AND latest_rec_flag = 'Y';
UPDATE ra_cust_trx_line_salesreps ctls
SET (
ctls.revenue_amount_split,
ctls.revenue_percent_split
) =
(
SELECT ctls.revenue_amount_split +
(
ctl1.extended_amount -
SUM(
NVL(ctls1.revenue_amount_split, 0)
)
),
ctls.revenue_percent_split +
(
100 -
SUM(
NVL(ctls1.revenue_percent_split, 0)
)
)
FROM ra_customer_trx_lines ctl1,
ra_cust_trx_line_salesreps ctls1
WHERE ctl1.customer_trx_line_id = ctls1.customer_trx_line_id
AND ctls.customer_trx_line_id = ctls1.customer_trx_line_id
GROUP BY ctls1.customer_trx_line_id,
ctl1.extended_amount,
ctls.revenue_amount_split,
ctls.revenue_percent_split
)
WHERE ctls.cust_trx_line_salesrep_id in
(
SELECT MIN(cust_trx_line_salesrep_id)
FROM ra_cust_trx_line_salesreps ctls,
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_line_id = ctls.customer_trx_line_id
AND ctl.customer_trx_id = p_customer_trx_id
GROUP BY ctls.customer_trx_line_id,
ctl.extended_amount
HAVING (
-- Check Revenue Amount Split
ctl.extended_amount <> SUM(
NVL(ctls.revenue_amount_split, 0)
) AND
100 = SUM(
NVL(ctls.revenue_percent_split, 0)
)
)
OR
-- Check Revenue Percent Split
(
100 <> SUM(
NVL(ctls.revenue_percent_split, 0)
) AND
ctl.extended_amount = SUM(
NVL(ctls.revenue_amount_split, 0)
)
)
);
SELECT req.request_id
INTO g_autoinv_request_id
FROM fnd_concurrent_programs prog,
fnd_concurrent_requests req
WHERE req.request_id = FND_GLOBAL.CONC_REQUEST_ID
AND req.concurrent_program_id = prog.concurrent_program_id
AND prog.application_id = 222
AND prog.concurrent_program_name = 'RAXTRX';