[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
selects to avoid re-parsing similar statements
*/
acct_class RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS%TYPE;
| the inner SELECT with a pair of nested for
| loops and some simple PLSQL code.
| Finally, replaced FOR-SELECT structure with
| modern CURSOR LOOP to reduce parsing.
| 17-SEP-04 M Raymond - Bug 3879222 - Moved call-once logic inside
| this procedure to make it more useful to
| external applications.
+-------------------------------------------------------------------------*/
PROCEDURE populate_glp_table(
p_sob IN NUMBER,
p_appl_id IN NUMBER) IS
CURSOR c_gl_period_rec(v_sob NUMBER, v_appl_id NUMBER) IS
SELECT
start_date,
end_date,
closing_status
FROM
gl_period_statuses
WHERE
application_id = v_appl_id
AND set_of_books_id = v_sob
AND adjustment_period_flag = 'N'
ORDER BY
period_year,
period_num,
start_date,
end_date;
| Updates ar_periods and ar_period_types tables with latest changes in |
| gl_periods table. |
| |
| PARAMETERS |
| INPUT |
| None. |
| |
| OUTPUT |
| Errbuf VARCHAR2 -- Conc Pgm Error mesgs. |
| RetCode VARCHAR2 -- Conc Pgm Error Code. |
| 0 - Success, 2 - Failure. |
| |
| NOTES |
| |
| EXAMPLE |
| refresh; |
UPDATE ar_period_types apt
SET
max_regular_period_length =
(
SELECT MAX(g.end_date - g.start_date) + 1
FROM gl_periods g
WHERE g.period_type = apt.period_type
AND g.adjustment_period_flag = 'N'
)
WHERE
max_regular_period_length <>
(
SELECT MAX(g.end_date - g.start_date) + 1
FROM gl_periods g
WHERE g.period_type = apt.period_type
AND g.adjustment_period_flag = 'N'
);
arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
arp_standard.debug('Inserting into ar_period_types: ');
INSERT
INTO ar_period_types
( period_type, max_regular_period_length )
(
SELECT
g.period_type,
MAX(g.end_date - g.start_date) + 1 max_regular_period_length
FROM
gl_periods g
WHERE
g.adjustment_period_flag = 'N'
AND NOT EXISTS
(
SELECT NULL
FROM ar_period_types apt
WHERE apt.period_type = g.period_type
)
GROUP BY period_type
);
arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
DELETE
FROM ar_periods ap
WHERE
NOT EXISTS
(
SELECT NULL
FROM gl_periods gp
WHERE gp.period_name = ap.period_name
AND gp.period_set_name = ap.period_set_name
AND gp.adjustment_period_flag = 'N'
);
arp_standard.debug('row(s) deleted: ' || TO_CHAR(sql%rowcount));
UPDATE ar_periods ap
SET
(period_type, start_date, end_date) =
(
SELECT period_type, start_date, end_date
FROM gl_periods gp
WHERE gp.period_name = ap.period_name
AND gp.period_set_name = ap.period_set_name
)
WHERE
EXISTS
(
SELECT NULL
FROM gl_periods gp
WHERE
gp.period_name = ap.period_name
AND gp.period_set_name = ap.period_set_name
AND NOT (gp.period_type = ap.period_type AND
gp.start_date = ap.start_date AND
gp.end_date = ap.end_date
)
);
arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
arp_standard.debug('Inserting into ar_periods: ');
INSERT
INTO ar_periods
(period_set_name , period_type, start_date, end_date,
new_period_num, period_name
)
(SELECT
period_set_name, period_type, start_date, end_date,
9999 + ROWNUM new_period_num,
period_name
FROM
gl_periods gp
WHERE
gp.adjustment_period_flag = 'N'
AND NOT EXISTS
(
SELECT NULL
FROM ar_periods ap
WHERE gp.period_name = ap.period_name
AND gp.period_set_name = ap.period_set_name
)
);
arp_standard.debug('row(s) inserted: ' || TO_CHAR(sql%rowcount));
UPDATE ar_periods p1
SET
new_period_num =
(
SELECT COUNT(*)
FROM ar_periods p2
WHERE p1.period_type = p2.period_type
AND p1.period_set_name = p2.period_set_name
AND p1.start_date >= p2.start_date
)
WHERE
new_period_num <>
(
SELECT COUNT(*)
FROM AR_PERIODS p2
WHERE p1.period_type = p2.period_type
AND p1.period_set_name = p2.period_set_name
AND p1.start_date >= p2.start_date
);
arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
| row count of number of records inserted. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 22-JAN-93 Nigel Smith created. |
| 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
| 20-MAR-98 S.Jandyala Modified the function to create |
| revenue account assignments by trx_id |
| 04-OCT-00 Jon Beckett Assignments not created if revenue |
| deferred(bug 1551488 Rev Mgmt phase II)|
| 10-DEC-01 M Raymond Added ORDERED hint to gl_date sql |
| that uses ar_revenue_assignments view. |
| See bug 2143064 for details. |
| 13-Aug-02 Debbie Jancis Modified for MRC Trigger replacement |
| added calls for |
| ra_cust_trx_line_gl_dist processing |
| 31-JAN-03 M Raymond Modified MRC cursor to include UNEARN
| rows where rec_offset_flag is null.
| 02-MAY-03 M Raymond Modified REV insert to include an
| outer join to ra_cust_trx_line_salesreps
| so we can assign proper salesrep id
| on CM distributions.
+-------------------------------------------------------------------------*/
FUNCTION create_assignments(
p_trx_id IN NUMBER,
p_period_set_name IN VARCHAR,
p_base_precision IN NUMBER,
p_bmau IN NUMBER)
RETURN NUMBER IS
/* added for MRC Trigger Replacement */
l_rows NUMBER;
SELECT
gl_date
INTO
rec_gl_date
FROM
ra_cust_trx_line_gl_dist
WHERE
account_class = 'REC'
AND account_set_flag = 'Y'
AND customer_trx_id = p_trx_id ;
SELECT /*+ ORDERED */
MIN(gl_date),
MAX(gl_date)
INTO
min_gl_date,
max_gl_date
FROM
ar_revenue_assignments
WHERE
customer_trx_id = p_trx_id
AND period_set_name = p_period_set_name ;
arp_standard.debug('before insert....');
INSERT INTO ra_cust_trx_line_gl_dist /* REV lines */
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
program_application_id,
program_id,
program_update_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
org_id
) /* Bug 2118867 - added ORDERED hint */
SELECT /*+ ORDERED */
ass.customer_trx_line_id, /* customer_trx_line_id */
lines.customer_trx_id, /* customer_trx_id */
dist.code_combination_id, /* code_combination_id */
arp_standard.sysparm.set_of_books_id, /* set_of_books_id */
ass.account_class, /* account_class */
'N', /* account_set_flag */
ROUND(
(DECODE(fc.minimum_accountable_unit,
NULL, ROUND( (dist.percent/100) *
DECODE(ass.amount, 0,
DECODE(ass.account_class,
'REV', DECODE( lines.previous_customer_trx_id,
NULL, 1, -1
),
DECODE( lines.previous_customer_trx_id,
NULL, -1, 1
)
),
ass.amount
), fc.precision),
ROUND( ((dist.percent/100) *
DECODE(ass.amount,
0, DECODE(ass.account_class,
'REV', DECODE( lines.previous_customer_trx_id,
NULL, 1, -1),
DECODE( lines.previous_customer_trx_id,
NULL, -1, 1)
),
ass.amount) ) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit) /
DECODE(lines.extended_amount,
0,1,
lines.extended_amount)) * decode(ass.amount, 0, 0, 100), /* Bug 944929 */
4), /* percent */
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( (dist.percent/100) * ass.amount, fc.precision),
ROUND( ((dist.percent/100) * ass.amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit), /* amount */
DECODE(p_bmau,
NULL, ROUND(
NVL(header.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( (dist.percent/100)
* ass.amount,
fc.precision),
ROUND( ((dist.percent/100) *
ass.amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit
),
p_base_precision),
ROUND(
( NVL(header.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( (dist.percent/100) * ass.amount,
fc.precision),
ROUND( ( (dist.percent/100) * ass.amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit
)
) / p_bmau) * p_bmau
), /* acctd_amount */
/*
Use the bump GL date if the actual Rev distribution GL date is in a
'Closed' OR 'Closed Pending' period.
Insert a NULL GL date if the transaction is post to GL = No.
*/
DECODE(rec.gl_date,
NULL, NULL,
assign_gl_date(ass.gl_date)
), /* derived gl_date */
DECODE(dist.customer_trx_id, header.customer_trx_id,
dist.cust_trx_line_salesrep_id,
cmsrep.cust_trx_line_salesrep_id), /* cust_trx_line_salesrep_id */
arp_standard.profile.request_id,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
-3,
ass.gl_date, /* original_gl_date */
ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
header.org_id
FROM
ra_customer_trx header,
fnd_currencies fc,
ra_customer_trx_lines lines,
ra_rules acc_rule,
ra_cust_trx_line_gl_dist rec,
ra_cust_trx_line_gl_dist dist,
ra_cust_trx_line_salesreps cmsrep,
ar_revenue_assignments ass
WHERE
header.customer_trx_id = p_trx_id
AND ass.customer_trx_id = p_trx_id /* 5752668 */
AND header.complete_flag = 'Y'
AND fc.currency_code = header.invoice_currency_code
/* only lines that are not comlpete with respect to autorule */
/* get accounting from view for line */
AND lines.customer_trx_id = header.customer_trx_id
AND lines.autorule_complete_flag||'' = 'N'
AND ass.customer_trx_line_id = lines.customer_trx_line_id
AND ass.period_set_name = p_period_set_name
AND acc_rule.rule_id = lines.accounting_rule_id
/* Bug 2560048/2639395 RAM-C - call collectivity engine to determine
if revenue should be deferred for INV or CM */
/* 6060283 - changed credits from cash_based to line_collectible
so they honor deferrals other than cash-based ones */
AND decode(header.invoicing_rule_id, -3, ar_revenue_management_pvt.collect,
decode(nvl(acc_rule.deferred_revenue_flag, 'N'),
'Y', ar_revenue_management_pvt.defer,
decode(g_rev_mgt_installed, 'N', ar_revenue_management_pvt.collect,
decode(header.previous_customer_trx_id, NULL,
ar_revenue_management_pvt.line_collectibility(p_trx_id, lines.customer_trx_line_id),
ar_revenue_management_pvt.line_collectible(
lines.previous_customer_trx_id,
lines.previous_customer_trx_line_id)))))
<> ar_revenue_management_pvt.defer
AND rec.customer_trx_id = header.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
/* join account set distribution to the transaction with the
account set. */
AND dist.customer_trx_line_id =
(SELECT
DECODE(COUNT(cust_trx_line_gl_dist_id),
0, NVL(lines.previous_customer_trx_line_id,
lines.customer_trx_line_id),
lines.customer_trx_line_id)
FROM
ra_cust_trx_line_gl_dist subdist2
WHERE
subdist2.customer_trx_line_id = lines.customer_trx_line_id
AND subdist2.account_set_flag = 'Y'
AND subdist2.gl_date IS NULL
AND ROWNUM < 2
)
AND dist.account_class = ass.account_class
/* only pick up account set accounts */
AND dist.account_set_flag = 'Y' /* model accounts */
/* Bug 2899714 */
AND dist.cust_trx_line_salesrep_id = cmsrep.prev_cust_trx_line_salesrep_id (+)
AND p_trx_id = cmsrep.customer_trx_id (+)
/* don't recreate those that already exist */
AND NOT EXISTS
(
SELECT
'distribution exists'
FROM
ra_cust_trx_line_gl_dist subdist
WHERE
subdist.customer_trx_line_id = ass.customer_trx_line_id
AND subdist.customer_trx_id + 0 = lines.customer_trx_id
AND subdist.account_set_flag = 'N'
AND subdist.account_class = ass.account_class
AND subdist.original_gl_date = ass.gl_date
);
arp_standard.debug('Revenue lines inserted: ' ||
l_rows);
| Row count of number of records inserted. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 22-JAN-93 Nigel Smith Created. |
| 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
| 20-MAR-98 S.Jandyala Modified the function to create |
| revenue account assignments by trx_id |
+-------------------------------------------------------------------------*/
FUNCTION create_other_receivable(
p_trx_id IN NUMBER,
p_base_precision IN NUMBER,
p_bmau IN NUMBER)
RETURN NUMBER IS
/* added for MRC Trigger Replacement */
l_rows NUMBER;
cursor c_update_ps( l_trx_id NUMBER) is
SELECT ps.payment_schedule_id ps_id,
gld.gl_date gl_date
FROM ar_payment_schedules ps,
ra_cust_trx_line_gl_dist gld
WHERE gld.customer_trx_id = l_trx_id
AND gld.account_class = 'REC'
AND gld.account_set_flag = 'N'
AND gld.customer_trx_id = ps.customer_trx_id
AND gld.gl_date <> ps.gl_date;
INSERT INTO ra_cust_trx_line_gl_dist /* REC line */
(
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
latest_rec_flag,
percent,
amount,
acctd_amount,
gl_date,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
program_application_id,
program_id,
program_update_date,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
org_id
) /* Bug 1544343 - added RULE hint, bug 2110069 - removed
RULE hint and changed subquery join (below) */
SELECT
rec.customer_trx_id,
rec.code_combination_id,
arp_standard.sysparm.set_of_books_id,
rec.account_class,
'N', /* account_set_flag */
'Y', /* latest_rec_flag */
rec.percent,
rec.amount,
rec.acctd_amount,
DECODE(rec.gl_date,
NULL, NULL,
assign_gl_rec(rec.gl_date)
), /* derived gl_date */
arp_standard.profile.request_id,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
-3, /* posting_control_id */
NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
ra_cust_trx_line_gl_dist_s.NEXTVAL,
header.org_id
FROM
ra_cust_trx_line_gl_dist rec,
ra_customer_trx header
WHERE
header.customer_trx_id = p_trx_id
AND header.complete_flag = 'Y'
AND rec.customer_trx_id = header.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND rec.account_set_flag = 'Y'
/* ensure that the receivable doesn't already exist */
AND NOT EXISTS
(
SELECT
'exist'
FROM
ra_cust_trx_line_gl_dist real_rec
WHERE
real_rec.customer_trx_id = rec.customer_trx_id
AND real_rec.account_class = 'REC'
AND real_rec.account_set_flag = 'N'
);
arp_standard.debug('REC lines inserted: ' ||
l_rows);
/* no mrc columns affected so no update to mrc table needed */
/* Bug 3416070 - Removed request_id from where clause */
UPDATE ra_cust_trx_line_gl_dist
SET
latest_rec_flag = 'N',
last_updated_by = arp_standard.profile.user_id,
last_update_date = sysdate
WHERE
account_set_flag = 'Y'
AND account_class = 'REC'
AND latest_rec_flag = 'Y'
AND customer_trx_id IN
(
SELECT
customer_trx_id
FROM
ra_cust_trx_line_gl_dist
WHERE
customer_trx_id = p_trx_id
AND account_class = 'REC'
AND account_set_flag = 'N'
);
FOR i IN c_update_ps(p_trx_id) LOOP
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('arp_auto_rule.create_other_receivable(): Change Payment Schedule Gl Date');
UPDATE ar_payment_schedules
SET gl_date = i.gl_date ,
last_updated_by = arp_standard.profile.user_id,
last_update_date = sysdate
WHERE payment_schedule_id = i.ps_id;
| Row count of number of records inserted. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 11-Sep-98 Ramakant Alat Added call to create_round and changed |
| 17-JAN-02 M Raymond If original gl_date is in closed period,
| REC row was getting bumped to next open,
| but ROUND was getting created in closed
| period. Now, ROUND gets bumped to same
| date as REC.
| See bug 2172061 for details.
| |
+-------------------------------------------------------------------------*/
FUNCTION create_round(
p_trx_id IN NUMBER,
p_base_precision IN NUMBER,
p_bmau IN NUMBER)
RETURN NUMBER IS
/* added for mrc */
l_rows NUMBER;
INSERT INTO ra_cust_trx_line_gl_dist /* ROUND line */
( /* drive from gl_dist */
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
latest_rec_flag,
percent,
amount,
acctd_amount,
gl_date,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
program_application_id,
program_id,
program_update_date,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
org_id
)
SELECT
rec.customer_trx_id,
rec.code_combination_id,
arp_standard.sysparm.set_of_books_id,
rec.account_class,
'N', /* account_set_flag */
null, /* latest_rec_flag */
rec.percent,
rec.amount,
rec.acctd_amount,
rrec.gl_date, /* 2172061 - now fetches date from REC row */
arp_standard.profile.request_id,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
-3, /* posting_control_id */
NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
ra_cust_trx_line_gl_dist_s.nextval,
header.org_id
FROM
ra_customer_trx header,
ra_cust_trx_line_gl_dist rec, /* ROUND row */
ra_cust_trx_line_gl_dist rrec /* REC row */
WHERE
header.customer_trx_id = p_trx_id
AND header.complete_flag = 'Y'
AND header.customer_trx_id = rec.customer_trx_id
AND rec.account_class = 'ROUND'
AND rec.account_set_flag = 'Y'
AND header.customer_trx_id = rrec.customer_trx_id
AND rrec.account_class = 'REC'
AND rrec.latest_rec_flag = 'Y'
/* ensure that the round record doesn't already exist */
AND NOT EXISTS
(
SELECT 'exist'
FROM
ra_cust_trx_line_gl_dist real_rec
WHERE
real_rec.customer_trx_id = rec.customer_trx_id
AND real_rec.account_class = 'ROUND'
AND real_rec.account_set_flag = 'N'
);
arp_standard.debug( 'ROUND lines inserted: ' ||
l_rows);
| Row count of number of records inserted. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 22-JAN-93 Nigel Smith created. |
| 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
| 20-MAR-98 S.Jandyala Modified the function to create |
| revenue account assignments by trx_id |
| 10-MAY-02 M Raymond Added column rec_offset_flag to
| ra_cust_trx_line_gl_dist_all. Added
| logic to this insert to populate it
| with a 'Y' if inserting UNEARN or
| UNBILL lines.
| See bug 2150541 for details.
| 18-SEP-02 J Beckett Bug 2560048 RAM-C - create cr UNEARN / |
| dr UNBILL if deferred/arrears. Select |
| restructured into cursor fetched into |
| variables. |
| 09-OCT-02 J Beckett Bug 2560048: U-turn on the above |
| approach - code is reverted to prior |
| state |
| 31-JAN-03 M Raymond Bug 2779454 - Added logic to limit
| the processing of UNEARN/UNBILL rows
| to only those with rof = Y. Rows with
| rof=null are processed in
| create_assignments
| 14-APR-03 M Raymond Bug 2899714 - Corrected assignment of
| cust_trx_line_salesrep_id for Credit
| Memos. Also removed some old RELEASE 9
| logic to improve performance a bit.
| 17-SEP-05 M Raymond Bug 4602892 - We now allow multiple
| lines on one CM to point to a single
| invoice line. The fix from 2899714
| causes too many rows to be inserted.
+-------------------------------------------------------------------------*/
FUNCTION create_other_plug(
p_trx_id IN NUMBER,
p_base_precision IN NUMBER,
p_bmau IN NUMBER)
RETURN NUMBER IS
/* added for MRC */
l_rows NUMBER;
SELECT cust_trx_type_id ,
invoicing_rule_id,
previous_customer_trx_id
INTO l_ctt_id,
l_inv_rule_id,
l_prev_cust_trx_id
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id;
INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
program_application_id,
program_id,
program_update_date,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
rec_offset_flag, /* Bug 2150541 */
org_id
)
SELECT
lines.customer_trx_line_id,
lines.customer_trx_id,
psum.code_combination_id,
arp_standard.sysparm.set_of_books_id,
psum.account_class,
'N', /* account_set_flag */
ROUND((DECODE(psum.account_class,
'SUSPENSE', (lines.extended_amount -
lines.revenue_amount),
decode(lines.revenue_amount,0,1,lines.revenue_amount)) /
DECODE(psum.account_class,
'SUSPENSE',decode((lines.extended_amount -
lines.revenue_amount),0,1,
(lines.extended_amount -
lines.revenue_amount)),
DECODE(lines.extended_amount,
0,1,
lines.extended_amount)) /*3550426*/
) * psum.percent, 4
), /* percent */
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE', (lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)), fc.precision),
ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE', (lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit
), /* amount */
DECODE(p_bmau,
NULL, ROUND(
NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)),
fc.precision),
ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit),
p_base_precision),
ROUND(
( NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)),
fc.precision),
ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit)) /
p_bmau) *
p_bmau), /* acctd_amount */
DECODE(rec.gl_date,
NULL, NULL,
assign_gl_rec(rec.gl_date)
), /* derived gl_date */
DECODE(psum.customer_trx_id, trx.customer_trx_id,
psum.cust_trx_line_salesrep_id,
cmsrep.cust_trx_line_salesrep_id), /* salescred ID */
arp_standard.profile.request_id,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
-3, /* posting_control_id */
NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
ra_cust_trx_line_gl_dist_s.NEXTVAL,
/* Bug 2150541 */
DECODE(psum.account_class, 'UNEARN', 'Y',
'UNBILL', 'Y',
NULL) ,
trx.org_id
FROM
ra_customer_trx_lines psum_lines,
ra_customer_trx psum_trx,
ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
ra_customer_trx_lines lines,
ra_cust_trx_line_gl_dist rec, /* model receivable account */
fnd_currencies fc,
ra_customer_trx trx
WHERE
trx.customer_trx_id = p_trx_id
AND trx.complete_flag = 'Y'
AND fc.currency_code = trx.invoice_currency_code
AND rec.customer_trx_id = trx.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND rec.customer_trx_line_id IS NULL
AND lines.customer_trx_id = trx.customer_trx_id
AND lines.autorule_complete_flag||'' = 'N'
AND psum_trx.customer_trx_id = psum.customer_trx_id
AND psum_lines.customer_trx_line_id = psum.customer_trx_line_id
AND psum.account_class IN
(
'SUSPENSE'||
DECODE(lines.extended_amount - NVL(lines.revenue_amount, 0),
0, 'X',
NULL),
DECODE(trx.invoicing_rule_id,
-2, 'UNEARN',
-3, 'UNBILL')
)
AND psum.customer_trx_line_id =
(SELECT
DECODE(COUNT(cust_trx_line_gl_dist_id),
0, NVL(lines.previous_customer_trx_line_id,
lines.customer_trx_line_id),
lines.customer_trx_line_id)
FROM
ra_cust_trx_line_gl_dist subdist2
WHERE
subdist2.customer_trx_line_id = lines.customer_trx_line_id
AND subdist2.account_set_flag = 'Y'
AND subdist2.gl_date IS NULL
AND ROWNUM < 2
)
/* Bug 2899714 */
AND cmsrep.prev_cust_trx_line_salesrep_id (+) =
psum.cust_trx_line_salesrep_id
AND cmsrep.customer_trx_id (+) = p_trx_id
/* Bug 4602892 - avoid cartesian product for CMs
w/ multiple lines against 1 invoice line. */
AND lines.customer_trx_line_id = DECODE(lines.previous_customer_trx_id,
NULL, lines.customer_trx_line_id,
NVL(cmsrep.customer_trx_line_id,
lines.customer_trx_line_id))
/* Bug 2899714 - removed RELEASE 9 code */
AND psum.account_set_flag = 'Y'
AND NOT EXISTS
(
SELECT
'plug sum account exists'
FROM
ra_cust_trx_line_gl_dist subdist
WHERE
subdist.account_class IN
( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
-2, 'UNEARN',
-3, 'UNBILL')
)
AND subdist.customer_trx_line_id = lines.customer_trx_line_id
AND subdist.account_set_flag = 'N'
AND subdist.rec_offset_flag = 'Y');
INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
program_application_id,
program_id,
program_update_date,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
rec_offset_flag, /* Bug 2150541 */
org_id
)
SELECT
lines.customer_trx_line_id,
lines.customer_trx_id,
psum.code_combination_id,
arp_standard.sysparm.set_of_books_id,
psum.account_class,
'N', /* account_set_flag */
ROUND((DECODE(psum.account_class,
'SUSPENSE', (lines.extended_amount -
lines.revenue_amount),
decode(lines.revenue_amount,0,1,lines.revenue_amount)) /
DECODE(psum.account_class,
'SUSPENSE', decode((lines.extended_amount -
lines.revenue_amount),0,1,
(lines.extended_amount -
lines.revenue_amount)),
DECODE(lines.extended_amount,
0,1,
lines.extended_amount))
) * psum.percent, 4
), /* percent */
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE', (lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)), fc.precision),
ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE', (lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit
), /* amount */
DECODE(p_bmau,
NULL, ROUND(
NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)),
fc.precision),
ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit),
p_base_precision),
ROUND(
( NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)),
fc.precision),
ROUND( ((psum.percent / 100) *
DECODE(psum.account_class,
'SUSPENSE',
(lines.extended_amount -
lines.revenue_amount),
lines.revenue_amount)) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit)) /
p_bmau) *
p_bmau), /* acctd_amount */
DECODE(rec.gl_date,
NULL, NULL,
assign_gl_rec(rec.gl_date)
), /* derived gl_date */
DECODE(psum.customer_trx_id, trx.customer_trx_id,
psum.cust_trx_line_salesrep_id,
cmsrep.cust_trx_line_salesrep_id), /* salescred ID */
arp_standard.profile.request_id,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
-3, /* posting_control_id */
NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
ra_cust_trx_line_gl_dist_s.NEXTVAL,
/* Bug 2150541 */
DECODE(psum.account_class, 'UNEARN', 'Y',
'UNBILL', 'Y',
NULL),
trx.org_id
FROM
ra_customer_trx_lines psum_lines,
ra_customer_trx psum_trx,
ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
ra_customer_trx_lines lines,
ra_cust_trx_line_gl_dist rec, /* model receivable account */
fnd_currencies fc,
ra_customer_trx trx
WHERE
trx.customer_trx_id = p_trx_id
AND trx.complete_flag = 'Y'
AND fc.currency_code = trx.invoice_currency_code
AND rec.customer_trx_id = trx.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND rec.customer_trx_line_id IS NULL
AND lines.customer_trx_id = trx.customer_trx_id
AND lines.autorule_complete_flag||'' = 'N'
AND psum_trx.customer_trx_id = psum.customer_trx_id
AND psum_lines.customer_trx_line_id = psum.customer_trx_line_id
AND psum.customer_trx_line_id = lines.previous_customer_trx_line_id
AND psum.account_set_flag = 'N'
AND ( ( psum.account_class IN ('UNEARN', 'UNBILL')
AND
psum.rec_offset_flag = 'Y'
)
OR
( psum.account_class = 'SUSPENSE'
AND
psum.rec_offset_flag IS NULL
)
)
AND cmsrep.prev_cust_trx_line_salesrep_id (+) =
psum.cust_trx_line_salesrep_id
AND cmsrep.customer_trx_id (+) = p_trx_id
/* Bug 4602892 - avoid cartesian product for CMs
w/ multiple lines against 1 invoice line. */
AND lines.customer_trx_line_id = DECODE(lines.previous_customer_trx_id,
NULL, lines.customer_trx_line_id,
NVL(cmsrep.customer_trx_line_id,
lines.customer_trx_line_id))
AND NOT EXISTS
(
SELECT
'plug sum account exists'
FROM
ra_cust_trx_line_gl_dist subdist
WHERE
subdist.account_class IN
( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
-2, 'UNEARN',
-3, 'UNBILL')
)
AND subdist.customer_trx_line_id = lines.customer_trx_line_id
AND subdist.account_set_flag = 'N'
AND subdist.rec_offset_flag = 'Y');
arp_standard.debug( 'Other plug lines inserted: ' ||
l_rows);
inserts no rows. To re-execute this function, we have to return -99.
The result values are -1 = None created, 0 = None needed, 1 = rows created */
IF l_result = 1
THEN
/* We set rof on some lines, so make the second call.
If result is 0, nothing was needed, and if it was -1, then we
have some sort of problem where we can't set rof when we think
one is needed */
l_rows := -99;
| Row count of number of records inserted. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 22-JAN-93 Nigel Smith created. |
| 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
| 20-MAR-98 S.Jandyala Modified the function to create |
| revenue account assignments by trx_id |
+-------------------------------------------------------------------------*/
FUNCTION create_other_tax(
p_trx_id IN NUMBER,
p_base_precision IN NUMBER,
p_bmau IN NUMBER,
p_ignore_rule_flag IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER IS
/* added for mrc */
l_rows NUMBER;
INSERT INTO ra_cust_trx_line_gl_dist /* TAX Lines */
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
program_application_id,
program_id,
program_update_date,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
collected_tax_ccid,
org_id
)
SELECT
tax_line.customer_trx_line_id,
tax_line.customer_trx_id,
tax.code_combination_id,
arp_standard.sysparm.set_of_books_id,
tax.account_class,
'N',
tax.percent,
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((tax.percent / 100) *
tax_line.extended_amount), fc.precision),
ROUND( ((tax.percent / 100) *
tax_line.extended_amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit), /* amount */
DECODE(p_bmau,
NULL, ROUND(
NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((tax.percent / 100) *
tax_line.extended_amount),
fc.precision),
ROUND( ((tax.percent / 100) *
tax_line.extended_amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit),
p_base_precision),
ROUND(
( NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((tax.percent / 100) *
tax_line.extended_amount),
fc.precision),
ROUND( ((tax.percent / 100) *
tax_line.extended_amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit)) /
p_bmau) *
p_bmau), /* acctd_amount */
DECODE(rec.gl_date,
NULL, NULL,
assign_gl_rec(rec.gl_date)
), /* derived gl_date */
tax.cust_trx_line_salesrep_id,
arp_standard.profile.request_id,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
-3,
NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
ra_cust_trx_line_gl_dist_s.NEXTVAL,
tax.collected_tax_ccid,
trx.org_id
FROM
ra_customer_trx model_trx,
ra_cust_trx_line_gl_dist tax,
ra_customer_trx_lines tax_line,
ra_customer_trx_lines line_line,
ra_cust_trx_line_gl_dist rec,
fnd_currencies fc,
ra_customer_trx trx
WHERE
trx.customer_trx_id = p_trx_id
AND trx.complete_flag = 'Y'
AND fc.currency_code = trx.invoice_currency_code
AND rec.customer_trx_id = trx.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND line_line.customer_trx_id = rec.customer_trx_id
AND (line_line.autorule_complete_flag||'' = 'N'
OR l_ignore_rule_flag = 'Y')
AND tax_line.link_to_cust_trx_line_id = line_line.customer_trx_line_id
AND tax_line.line_type = 'TAX'
AND tax_line.customer_trx_id + 0 = line_line.customer_trx_id
AND trx.customer_trx_id = tax_line.customer_trx_id
AND model_trx.customer_trx_id = tax.customer_trx_id
AND tax.account_class = 'TAX'
AND tax.customer_trx_line_id =
(SELECT
DECODE( COUNT(cust_trx_line_gl_dist_id),
0, NVL(tax_line.previous_customer_trx_line_id,
tax_line.customer_trx_line_id),
tax_line.customer_trx_line_id)
FROM
ra_cust_trx_line_gl_dist subdist2
WHERE
subdist2.customer_trx_line_id=tax_line.customer_trx_line_id
AND subdist2.account_set_flag = 'Y'
AND subdist2.gl_date IS NULL
AND ROWNUM < 2
)
AND ( tax.account_set_flag = 'Y'
OR
model_trx.created_from IN ('RAXTRX_REL9', 'FORM_REL9')
)
AND NOT EXISTS
(SELECT
'tax account exists'
FROM
ra_cust_trx_line_gl_dist subdist
WHERE
tax_line.customer_trx_line_id = subdist.customer_trx_line_id
AND subdist.account_set_flag = 'N'
AND subdist.gl_date IS NOT NULL
AND subdist.account_class = 'TAX'
);
arp_standard.debug( 'Tax lines inserted: ' ||
l_rows);
| Row count of number of records inserted. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 22-JAN-93 Nigel Smith created. |
| 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
| 20-MAR-98 S.Jandyala Modified the function to create |
| revenue account assignments by trx_id |
+-------------------------------------------------------------------------*/
FUNCTION create_other_freight(
p_trx_id IN NUMBER,
p_base_precision IN NUMBER,
p_bmau IN NUMBER)
RETURN NUMBER IS
/* added for mrc */
l_rows NUMBER;
INSERT INTO ra_cust_trx_line_gl_dist /* FREIGHT Lines */
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
program_application_id,
program_id,
program_update_date,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
org_id
)
SELECT
lines.customer_trx_line_id,
lines.customer_trx_id,
freight.code_combination_id,
arp_standard.sysparm.set_of_books_id,
freight.account_class,
'N',
freight.percent,
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((freight.percent / 100) *
lines.extended_amount), fc.precision),
ROUND( ((freight.percent / 100) *
lines.extended_amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit), /* amount */
DECODE(p_bmau,
NULL, ROUND(
NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((freight.percent / 100) *
lines.extended_amount),
fc.precision),
ROUND( ((freight.percent / 100) *
lines.extended_amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit),
p_base_precision),
ROUND(
( NVL(trx.exchange_rate, 1) *
DECODE(fc.minimum_accountable_unit,
NULL, ROUND( ((freight.percent / 100) *
lines.extended_amount),
fc.precision),
ROUND( ((freight.percent / 100) *
lines.extended_amount) /
fc.minimum_accountable_unit) *
fc.minimum_accountable_unit)) /
p_bmau) *
p_bmau), /* acctd_amount */
rec.gl_date,
freight.cust_trx_line_salesrep_id,
arp_standard.profile.request_id,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
-3,
NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
ra_cust_trx_line_gl_dist_s.NEXTVAL,
trx.org_id
FROM
ra_customer_trx model_trx,
ra_cust_trx_line_gl_dist freight,
ra_customer_trx_lines lines,
ra_cust_trx_line_gl_dist rec,
fnd_currencies fc,
ra_customer_trx trx
WHERE
trx.customer_trx_id = p_trx_id
AND trx.complete_flag = 'Y'
AND fc.currency_code = trx.invoice_currency_code
AND rec.customer_trx_id = trx.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND rec.customer_trx_line_id IS NULL
AND EXISTS
(
SELECT 1
FROM ra_customer_trx_lines line_line
WHERE line_line.customer_trx_id = trx.customer_trx_id
AND line_line.autorule_complete_flag||'' = 'N'
)
AND lines.customer_trx_id = rec.customer_trx_id
AND lines.line_type = 'FREIGHT'
AND model_trx.customer_trx_id = freight.customer_trx_id
/* for CMs: use the invoice's account set
if USE_INV_ACCT_FOR_CM_FLAG = Yes. */
AND freight.customer_trx_line_id =
(SELECT
DECODE( COUNT(cust_trx_line_gl_dist_id),
0, NVL(lines.previous_customer_trx_line_id,
lines.customer_trx_line_id),
lines.customer_trx_line_id)
FROM
ra_cust_trx_line_gl_dist subdist2
WHERE
subdist2.customer_trx_line_id = lines.customer_trx_line_id
AND subdist2.account_set_flag = 'Y'
AND subdist2.gl_date IS NULL
AND ROWNUM < 2
)
AND freight.account_class = 'FREIGHT'
AND ( freight.account_set_flag = 'Y'
OR
model_trx.created_from IN ( 'RAXTRX_REL9', 'FORM_REL9')
)
AND NOT EXISTS
(SELECT
'freight account exists'
FROM
ra_cust_trx_line_gl_dist subdist
WHERE
subdist.customer_trx_line_id = lines.customer_trx_line_id
AND subdist.account_set_flag = 'N'
AND subdist.gl_date IS NOT NULL
AND subdist.account_class = 'FREIGHT'
);
arp_standard.debug( 'Freight lines inserted: ' ||
l_rows);
| update_durations |
| |
| DESCRIPTION |
| Increment autorule_duration_processed and autorule_complete_flag for |
| lines for which we have created distributions. |
| |
| REQUIRES |
| |
| RETURNS |
| row count of number of records updated. |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 22-JAN-93 Nigel Smith created. |
| 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
| 20-MAR-98 S.Jandyala Modified the function to update |
| by trx_id |
| 05-OCT-00 Jon Beckett BUG 1551488 Deferred revenue |
| 19-SEP-02 J Beckett Bug 2560048 RAM-C - revenue can be |
| deferred on arrears or deferred due to |
| collectibility decision |
| 09-OCT-02 J Beckett Bug 2560048: above only applies to |
| advance invoicing rule |
| 29-JAN-03 O RASHID Added the fix for bug # 2774432. |
| credit memos on ramc invoices with |
| rules should stamp the |
| autorule_complete_flag. |
| 19-FEB-03 M Raymond Bug 2584263 - redesigned logic in
| this function to always update any
| transactions where distributions were
| created.
| 07-MAR-04 M Raymond Bug 3416070 - created branched logic
| that utilizes request_id when it is
| present or skips it when it is null
+-------------------------------------------------------------------------*/
FUNCTION update_durations( p_trx_id IN NUMBER )
RETURN NUMBER IS
l_rows NUMBER;
arp_standard.debug( 'arp_auto_rule.update_durations()+ ' ||
TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
/* This statement needs to update the autorule_complete_flag
and autorule_duration_processed for any transactions that
were picked up by the rev rec process. This includes:
o INV w/ rules
o INV w/ deferred rules
o INV that are non-collectible
o CMs targeting above transactions
o Either type of transaction if tax or customer is modified
We can do this based on the existance of a row with the
rev rec request_id because, at this time, no transaction will
ever intentionally pass through revenue recognition more
than once. The EXISTS clause is really just a safety net
to prevent us from flagging transactions that did not, for
other reasons, process.
*/
/* Bug 3416070/3403067 - Modified update to only be dependent on
request_id when one is set. ARP_ALLOCATIONS_PKG calls this
code without one. */
/*4578927 suppressed the index on autorule_complete_flag*/
IF arp_standard.profile.request_id IS NOT NULL
THEN
/* Existing logic - request_id is set */
update ra_customer_trx_lines ul
set autorule_complete_flag = null,
autorule_duration_processed =
accounting_rule_duration,
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id,
program_application_id = arp_standard.application_id,
program_update_date = sysdate,
program_id = arp_standard.profile.program_id
where customer_trx_id = p_trx_id
and autorule_complete_flag||'' = 'N'
and (exists (select 'at least one distribution'
from ra_cust_trx_line_gl_dist gl
where gl.customer_trx_line_id = ul.customer_trx_line_id
and gl.account_set_flag = 'N'
and gl.request_id = arp_standard.profile.request_id)
or exists (select 'a distribution for a linked line'
from ra_customer_trx_lines tl,
ra_cust_trx_line_gl_dist tgl
where tl.customer_trx_id = ul.customer_trx_id
and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
and tgl.customer_trx_line_id = tl.customer_trx_line_id
and tgl.account_set_flag = 'N'
and tgl.request_id = arp_standard.profile.request_id));
update ra_customer_trx_lines ul
set autorule_complete_flag = null,
autorule_duration_processed =
accounting_rule_duration,
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id,
program_application_id = arp_standard.application_id,
program_update_date = sysdate,
program_id = arp_standard.profile.program_id
where customer_trx_id = p_trx_id
and autorule_complete_flag||'' = 'N'
and (exists (select 'at least one distribution'
from ra_cust_trx_line_gl_dist gl
where gl.customer_trx_line_id = ul.customer_trx_line_id
and gl.account_set_flag = 'N')
or exists (select 'a distribution for a linked line'
from ra_customer_trx_lines tl,
ra_cust_trx_line_gl_dist tgl
where tl.customer_trx_id = ul.customer_trx_id
and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
and tgl.customer_trx_line_id = tl.customer_trx_line_id
and tgl.account_set_flag = 'N'));
arp_standard.debug(' rows updated: ' ||
l_rows);
arp_standard.debug( 'arp_auto_rule.update_durations()- ' ||
TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS' || cr));
arp_standard.debug('EXCEPTION: arp_auto_rule.update_durations()');
arp_standard.debug( 'arp_auto_rule.update_durations()- ' ||
TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
END update_durations;
| update_durations |
| |
| RETURNS |
| stats structure, with rowcount for each major operation |
| |
| EXCEPTIONS RAISED |
| None |
| |
| NOTES |
| |
| EXAMPLE |
| |
| stats := auto_rule.create_distributions( |
| p_commit_at_end, |
| p_debug_flag); |
durations NUMBER := 0 -- Number of durations updated.
);
/* Cursor selects all the transactions for which distributions are not
completely created. */
/* Bug 2133254 - Changed code from using a single c_trx cursor to
using either c_trx or c_trx_no_id cursors. (in Bug 2122202,
we had done this with dynamic cursors - but this caused the
cursors to be reparsed. */
/* Bug 2399504 - Added the autorule_duration_processed
condition to both cursors. This should help avoid
problems with CMs that are not flagged properly */
/*Change for bug-5444411 to suppress index on autorule_complete_flag*/
CURSOR c_trx IS
SELECT
ct.customer_trx_id,
ct.trx_number
FROM
ra_customer_trx ct
WHERE
ct.complete_flag = 'Y'
AND ct.customer_trx_id = p_trx_id
AND EXISTS (
SELECT 'line needing dists'
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = ct.customer_trx_id
AND ctl.autorule_complete_flag||'' = 'N'
AND (ctl.autorule_duration_processed <
ctl.accounting_rule_duration OR
ctl.autorule_duration_processed is NULL));
SELECT
DISTINCT ctl.customer_trx_id,
ct.trx_number
FROM
ra_customer_trx ct,
ra_customer_trx_lines ctl
WHERE
ctl.autorule_complete_flag = 'N'
AND nvl(ctl.autorule_duration_processed,-2) <
nvl(ctl.accounting_rule_duration,-1)
AND ct.customer_trx_id = ctl.customer_trx_id
AND ct.complete_flag = 'Y';
IF arp_standard.profile.last_update_login IS NULL
THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('create_distributions(): NULL Last_update_login');
'PARAMETER', 'ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN' );
SELECT
period_set_name,
precision,
minimum_accountable_unit
INTO
period_set_name,
base_precision,
base_min_acc_unit
FROM
fnd_currencies fc,
gl_sets_of_books gsb,
ar_system_parameters asp
WHERE
gsb.set_of_books_id = asp.set_of_books_id
AND fc.currency_code = gsb.currency_code;
/* This is almost exactly like original select */
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug('p_trx_id is null, using joined tables');
to set_rec_offset_flag updated more rows (should really never happen) */
IF stats.plugs = -99
THEN
stats.plugs := -1;
/* Update durations processed and correct rounding errors ONLY if any
distributions are created for this transaction in this run. */
IF ( trx_dist_created > 0 )
THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'trx_id = '|| trx_id ||
', distributions created = '|| trx_dist_created);
stats.durations := update_durations(trx_id);
If update_durations() fails, rollback and exit function.
*/
IF (stats.durations = -1)
THEN
ROLLBACK TO SAVEPOINT AR_AUTORULE_1;