[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(l.revenue_amount)
INTO g_potential_revenue
FROM ra_customer_trx_lines_all l
WHERE EXISTS
(SELECT /*+ INDEX (cogs AR_TRX_COGS_N1) */
'eligible transaction captured in GT table'
FROM ar_trx_cogs_gt cogs
WHERE cogs.so_line_id = p_so_line_id
AND cogs.period_number <= p_period_number
AND cogs.customer_trx_id = l.customer_trx_id
AND cogs.customer_trx_line_id = l.customer_trx_line_id);
SELECT closing_status
FROM gl_period_statuses ps
WHERE adjustment_period_flag = 'N'
AND application_id = 222
AND set_of_books_id = p_sob_id
AND effective_period_num = p_eff_period_num;
SELECT effective_period_num, sp.set_of_books_id
FROM gl_period_statuses ps, ar_system_parameters sp
WHERE ps.set_of_books_id = sp.set_of_books_id
AND adjustment_period_flag = 'N'
AND application_id = 222
AND period_name = p_period_name;
l_so_rows_inserted NUMBER;
SELECT sales_order, org_id
FROM
(
SELECT l.sales_order,
l.org_id,
row_number() over
(partition by l.sales_order,l.org_id
order by l.sales_order, l.org_id) rn
FROM ra_customer_trx_lines_all l,
ra_cust_trx_line_gl_dist_all gld,
gl_date_period_map gl_map,
gl_sets_of_books gl_sob
WHERE gld.cogs_request_id = p_request_id
AND gld.org_id = p_org_id
AND gld.account_class = 'REV'
AND gld.latest_rec_flag IS NULL
AND gld.gl_date = gl_map.accounting_date
AND gld.customer_trx_line_id = l.customer_trx_line_id
AND gl_sob.set_of_books_id = p_sob_id
AND gl_sob.period_set_name = gl_map.period_set_name
AND gl_sob.accounted_period_type = gl_map.period_type
AND gl_map.accounting_date BETWEEN p_start_date
AND p_end_date
) a
WHERE a.rn = 1;
SELECT gld.ROWID,
Decode(gld.account_set_flag, 'Y', -100,
Decode(l.interface_line_context, g_om_context,
Decode(l.sales_order, NULL, -98,
Decode(l.sales_order_line, NULL, -97,
Decode(l.interface_line_attribute6, NULL, -96,
p_request_id))),-100)) cogs_request_id
FROM ra_customer_trx_lines_all l,
ra_cust_trx_line_gl_dist_all gld,
gl_date_period_map gl_map,
gl_sets_of_books gl_sob
WHERE gld.cogs_request_id IS NULL
AND gld.org_id = p_org_id
AND gld.account_class = 'REV'
AND gld.latest_rec_flag IS NULL
AND gld.gl_date = gl_map.accounting_date
AND gld.customer_trx_line_id = l.customer_trx_line_id
AND gl_sob.set_of_books_id = p_sob_id
AND gl_sob.period_set_name = gl_map.period_set_name
AND gl_sob.accounted_period_type = gl_map.period_type
AND gl_map.accounting_date BETWEEN p_start_date
AND p_end_date;
SELECT org_id, set_of_books_id
FROM ar_system_parameters_all
WHERE set_of_books_id = NVL(p_sob_id, set_of_books_id)
AND set_of_books_id > 0
AND org_id >= 0;
SELECT cogs_request_id, count(*) error_count,
DECODE(cogs_request_id, -100, 'model or non-OM',
-99, 'corrupt or missing line',
-98, 'null sales_order',
-97, 'null sales_order_line',
-96, 'null int_attr6 col') meaning
FROM ra_cust_trx_line_gl_dist_all
WHERE org_id = p_org_id
AND cogs_request_id BETWEEN -98 AND -1
AND gl_date BETWEEN p_from_date AND p_to_date
AND account_class = 'REV'
AND latest_rec_flag IS NULL
GROUP BY cogs_request_id;
SELECT *
FROM ar_trx_cogs_gt;
Update gl_dist table with cogs_request_id
END LOOP
LOOP
Open/Fetch unique sales orders
- If no orders, goto next_org
Insert into ar_trx_cogs_gt
END LOOP
Merge into cst_revenue_recognition_lines
<>
END LOOP
*/
FOR c_org IN ar_operations(p_ledger_id)
LOOP
IF PG_DEBUG in ('Y', 'C') THEN
arp_debug.debug('Processing org_id =' || c_org.org_id || ', sob_id = ' ||
c_org.set_of_books_id);
' distinct gld row(s) for update.');
/* Now update the gld rows with cogs_request_ids */
FORALL i in 1 .. gld_rowids.count
UPDATE ra_cust_trx_line_gl_dist_all gld
SET cogs_request_id = gld_cogs_request_ids(i)
WHERE rowid = gld_rowids(i);
arp_debug.debug(' updated ' || l_rows ||
' distinct gld row(s).');
/* Rows found, insert into ar_trx_cogs_gt */
/* Step 3 - populate AR_TRX_COGS_GT with summarized data. Data is
recorded per invoice line and GL period. The potential revenue
(revenue_line_amount) is recorded in each period that a transaction
line effects but it must be considered only once in creating
the divisor for the final revenue percentage. This is currently
handled via a function call */
FORALL i in 1 .. so_numbers.count
INSERT INTO ar_trx_cogs_gt
( customer_trx_id,
customer_trx_line_id,
previous_customer_trx_line_id,
so_line_id,
period_number,
revenue_dist_amount,
revenue_line_amount,
latest_gl_date,
org_id,
set_of_books_id
)
SELECT /*+ ORDERED */
tl.customer_trx_id,
tl.customer_trx_line_id,
tl.previous_customer_trx_line_id,
to_number(
decode(tl.previous_customer_trx_line_id, NULL,
tl.interface_line_attribute6,
tli.interface_line_attribute6)),
gps.effective_period_num,
sum(tlgld.amount), -- revenue_dist_amount
sum(tlgld.percent),-- revenue_line_amount (storing rev percent)
MAX(tlgld.gl_date),-- latest_gl_date
tl.org_id,
tl.set_of_books_id
FROM ra_customer_trx_lines_all tl,
ra_customer_trx_lines_all tli,
ra_cust_trx_line_gl_dist_all tlgld,
gl_period_statuses gps
WHERE
tl.sales_order = so_numbers(i)
AND tl.org_id = so_orgs(i)
AND tl.customer_trx_line_id = tlgld.customer_trx_line_id
AND tlgld.account_set_flag = 'N'
AND tlgld.account_class = 'REV'
AND tl.previous_customer_trx_line_id = tli.customer_trx_line_id (+)
AND tl.interface_line_context = g_om_context -- 7349970
AND NVL(tli.interface_line_context,tl.interface_line_context) =
g_om_context
AND NVL(tli.interface_line_attribute6, tl.interface_line_attribute6)
IS NOT NULL
AND NVL(tli.sales_order_line, tl.sales_order_line)
IS NOT NULL -- 7349970
AND gps.set_of_books_id = tl.set_of_books_id
AND gps.application_id = 222
AND gps.adjustment_period_flag = 'N'
AND tlgld.gl_date between gps.start_date and gps.end_date
AND NVL(LENGTH(REPLACE(TRANSLATE(
DECODE(tl.previous_customer_trx_line_id, NULL,
tl.interface_line_attribute6, tli.interface_line_attribute6),
'123456789','0000000000'),'0','')),0) = 0
GROUP BY
tl.customer_trx_id, tl.customer_trx_line_id,
tl.previous_customer_trx_line_id,
to_number(
decode(tl.previous_customer_trx_line_id, NULL,
tl.interface_line_attribute6,
tli.interface_line_attribute6)),
gps.effective_period_num, tl.revenue_amount,
tl.org_id, tl.set_of_books_id;
l_so_rows_inserted := SQL%ROWCOUNT;
arp_debug.debug(' inserted ' || l_so_rows_inserted ||
' row(s) into ar_trx_cogs_gt');
SELECT
rev.so_line_id,
max(rev.latest_gl_date) gl_date,
gps.effective_period_num period_number,
DECODE(ar_match_rev_cogs_grp.potential_revenue(
rev.so_line_id,gps.effective_period_num),0,
DECODE(SUM(rev.revenue_line_amount),0,0,1),
DECODE(SUM(rev.revenue_dist_amount),0,0,
ROUND(SUM(rev.revenue_dist_amount) /
ar_match_rev_cogs_grp.potential_revenue(rev.so_line_id,
gps.effective_period_num),4)))
rev_percent,
max(rev.org_id) org_id,
gps.set_of_books_id set_of_books_id
FROM ar_trx_cogs_gt rev,
gl_period_statuses gps
WHERE gps.application_id = 222
AND gps.set_of_books_id = rev.set_of_books_id
AND gps.ledger_id = p_ledger_id
AND gps.start_date <= p_to_gl_date
AND gps.adjustment_period_flag = 'N'
AND rev.period_number <= gps.effective_period_num
GROUP BY rev.so_line_id, gps.effective_period_num,
gps.set_of_books_id, gps.start_date, gps.end_date
HAVING max(rev.latest_gl_date) between
gps.start_date AND gps.end_date
) Q
ON (Q.so_line_id = crrl.revenue_om_line_id AND
Q.period_number = crrl.acct_period_num)
WHEN MATCHED THEN
UPDATE SET
revenue_recognition_percent = Q.rev_percent,
last_event_date = Q.gl_date,
potentially_unmatched_flag =
DECODE(revenue_recognition_percent, Q.rev_percent,
potentially_unmatched_flag,'Y'),
request_id =
DECODE(revenue_recognition_percent, Q.rev_percent,
request_id,l_request_id)
WHEN NOT MATCHED THEN
INSERT (revenue_om_line_id,
acct_period_num,
revenue_recognition_percent,
last_event_date,
operating_unit_id,
ledger_id,
customer_trx_line_id,
potentially_unmatched_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (Q.so_line_id,
Q.period_number,
Q.rev_percent,
Q.gl_date,
Q.org_id,
Q.set_of_books_id,
NULL,
'Y',
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
l_request_id,
l_pgm_app_id,
l_pgm_id,
sysdate
);