[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 '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;
SELECT /*+ LEADING(p,gld,gld2)
INDEX(gld,RA_CUST_TRX_LINE_GL_DIST_N2)
USE_HASH(gld) USE_NL(gld2) ROWID(gld2) */
distinct l.sales_order, l.org_id
FROM ra_customer_trx_lines_all l,
ra_cust_trx_line_gl_dist_all gld,
ra_cust_trx_line_gl_dist_all gld2,
ar_system_parameters_all p
WHERE gld.gl_date between p_low_date and p_hi_date
AND gld2.account_set_flag = 'N'
AND gld.org_id = p.org_id
AND p.set_of_books_id = p_ledger_id
AND gld.ROWID = gld2.rowid
AND gld2.customer_trx_line_id = l.customer_trx_line_id
AND l.sales_order_line is NOT NULL
AND l.interface_line_context = g_om_context
AND l.interface_line_attribute6 IS NOT NULL;
SELECT distinct l.sales_order, l.org_id
FROM ra_customer_trx_lines_all l,
ra_cust_trx_line_gl_dist_all gld
WHERE gld.gl_date between p_low_date and p_hi_date
AND gld.account_set_flag = 'N'
AND gld.customer_trx_line_id = l.customer_trx_line_id
AND l.sales_order_line is NOT NULL
AND l.interface_line_context = g_om_context
AND l.interface_line_attribute6 IS NOT NULL;
SELECT *
FROM ar_trx_cogs_gt;
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
tl.revenue_amount, -- revenue_line_amount (not currently used)
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;
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,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.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 = 'Y'
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
);