[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_line_id = p_line_id
AND account_set_flag = 'N'
AND account_class = 'REV';
SELECT cgs.cogs_acct_description code_combination_id,
gcc.chart_of_accounts_id
FROM ar_cogs_rev_itf cgs, gl_code_combinations gcc
WHERE cgs.cogs_acct_description = gcc.code_combination_id;
UPDATE ar_cogs_rev_itf
SET cogs_acct_description = l_description
WHERE cogs_acct_description = rec.code_combination_id;
SELECT cur.precision
FROM gl_sets_of_books sob,
fnd_currencies cur
WHERE sob.currency_code = cur.currency_code
AND sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
DELETE FROM ar_cogs_rev_itf;
fnd_file.put_line(fnd_file.log, 'table being populated with selected rows');
INSERT INTO ar_cogs_rev_itf
(
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
set_of_books_id,
cogs_gl_account,
cogs_acct_description,
customer_name,
sales_order_type,
sales_order,
sales_order_line,
trx_class,
trx_number,
trx_line_number,
order_amount_orig,
cogs_amount_orig,
cogs_amount_period,
rev_amount_period
)
SELECT
p_request_id request_id,
p_user_id create_by,
sysdate creation_date,
p_user_id last_udpated_by,
sysdate last_update_date,
p_user_id last_update_login,
arp_standard.sysparm.set_of_books_id,
MAX(fnd_flex_ext.get_segs(
'SQLGL',
'GL#',
gcc.chart_of_accounts_id,
gcc.code_combination_id)) cogs_account,
MAX(gcc.code_combination_id) description,
party.party_name customer,
lines.interface_line_attribute2 order_type,
lines.interface_line_attribute1 order_num,
lines.sales_order_line sales_order_line,
trx_type.type trx_class,
trx.trx_number trx_number,
lines.line_number trx_line_number,
ROUND((SUM(lines.revenue_amount)/
count(dist.cust_trx_line_gl_dist_id)), l_precision) orig_revenue,
SUM
( arrx_cogs_rep_inner.get_cost(
dist.account_class,
dist.rec_offset_flag,
lines.customer_trx_line_id,
mta.base_transaction_value)) orig_cost,
ROUND((SUM
(
DECODE
(
(DECODE(sign(mmt.transaction_date -fnd_date.chardate_to_date(p_gl_date_low)), -1, 0, 1)
+
DECODE(sign(mmt.transaction_date -fnd_date.chardate_to_date(p_gl_date_high)), 1, 0, 1)
),
2, arrx_cogs_rep_inner.get_cost(
dist.account_class,
dist.rec_offset_flag,
lines.customer_trx_line_id, mta.base_transaction_value), 0
)
)), l_precision) cost,
ROUND((SUM
(
DECODE
(
(
DECODE(sign(dist.gl_date - fnd_date.chardate_to_date(p_gl_date_low)), -1, 0, 1)
+
DECODE(sign(dist.gl_date - fnd_date.chardate_to_date(p_gl_date_high)), 1, 0, 1)
),
2, DECODE(dist.account_class, 'UNEARN', 0, dist.acctd_amount), 0
)
))/count(DISTINCT mmt.transaction_id), l_precision) revenue
FROM ra_cust_trx_line_gl_dist dist,
ra_customer_trx_lines lines,
mtl_material_transactions mmt,
mtl_transaction_accounts mta,
ra_customer_trx trx,
hz_cust_accounts acct,
hz_parties party,
mtl_system_items_b msi,
ra_cust_trx_types trx_type,
gl_code_combinations gcc,
cst_item_costs cic,
mtl_parameters mp
WHERE dist.customer_trx_line_id = lines.customer_trx_line_id
AND dist.account_set_flag = 'N'
AND dist.account_class IN ('REV', 'UNEARN')
AND lines.customer_trx_id = trx.customer_trx_id
AND lines.inventory_item_id = msi.inventory_item_id
AND lines.interface_line_attribute10 = msi.organization_id
AND lines.line_type = 'LINE'
AND lines.interface_line_context = 'ORDER ENTRY'
AND lines.interface_line_attribute6 = mmt.trx_source_line_id
AND mmt.transaction_source_type_id IN (2, 12)
AND mmt.transaction_action_id IN (1, 27)
AND mmt.costed_flag IS NULL
AND mmt.organization_id = msi.organization_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.transaction_id = mta.transaction_id
AND mta.accounting_line_type <> 2
AND msi.inventory_item_id = cic.inventory_item_id
AND msi.organization_id = cic.organization_id
AND msi.organization_id = mp.organization_id
AND cic.cost_type_id = mp.primary_cost_method
AND msi.shippable_item_flag = 'Y'
AND msi.costing_enabled_flag = 'Y'
AND msi.invoiceable_item_flag = 'Y'
AND msi.invoice_enabled_flag = 'Y'
AND cic.inventory_asset_flag = 1
AND trx.cust_trx_type_id = trx_type.cust_trx_type_id
AND trx_type.type IN ('INV', 'CM')
AND trx.bill_to_customer_id = acct.cust_account_id
AND acct.party_id = party.party_id
AND mta.reference_account = gcc.code_combination_id
AND dist.gl_date
BETWEEN p_gl_date_low AND p_gl_date_high
AND lines.interface_line_attribute1
BETWEEN NVL(p_sales_order_low, lines.interface_line_attribute1) AND
NVL(p_sales_order_high, lines.interface_line_attribute1)
AND dist.posting_control_id <> NVL(l_posting_control_id, -99999999999)
AND mta.gl_batch_id <> NVL(l_gl_batch_id, -99999999999)
GROUP BY mta.reference_account,
party.party_name,
lines.interface_line_attribute2,
lines.interface_line_attribute1,
trx_type.type,
trx.trx_number,
lines.sales_order_line,
lines.interface_line_attribute6,
lines.line_number;
fnd_file.put_line(fnd_file.log, 'table populated with selected rows');
UPDATE ar_cogs_rev_itf
SET rev_percent_period = ROUND((((rev_amount_period/order_amount_orig) *
DECODE(trx_class, 'CM', -100, 100))),
l_precision),
cogs_percent_period = ROUND(((cogs_amount_period/cogs_amount_orig)
* DECODE(trx_class, 'CM', -100, 100)),
l_precision),
cogs_adjustment = ROUND(((((rev_amount_period/order_amount_orig))
- (cogs_amount_period/cogs_amount_orig)) *
cogs_amount_orig), l_precision)
WHERE cogs_amount_orig <> 0
AND order_amount_orig <> 0;
UPDATE ar_cogs_rev_itf
SET rev_percent_period = 0,
cogs_percent_period = 0,
cogs_adjustment = 0
WHERE cogs_amount_orig = 0
AND order_amount_orig = 0;
fnd_file.put_line(fnd_file.log, 'update done');
DELETE FROM ar_cogs_rev_itf
WHERE rev_percent_period = cogs_percent_period;
SELECT cur.precision
FROM gl_sets_of_books sob,
fnd_currencies cur
WHERE sob.currency_code = cur.currency_code
AND sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
SELECT cogs_gl_account,
cogs_acct_description,
ROUND(SUM(cogs_adjustment), l_precision)
FROM ar_cogs_rev_itf
GROUP BY cogs_gl_account, cogs_acct_description;
DELETE FROM ar_cogs_rev_itf;
INSERT INTO ar_cogs_rev_itf
(
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
set_of_books_id,
cogs_gl_account,
cogs_acct_description,
cogs_adjustment
)
VALUES
(
p_request_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
arp_standard.sysparm.set_of_books_id,
l_gl_acct_tbl(i),
l_gl_acct_desc_tbl(i),
l_cogs_adjustment_tbl(i)
);
DELETE FROM ar_cogs_rev_itf
WHERE COGS_GL_ACCOUNT
NOT BETWEEN p_gl_account_low AND p_gl_account_high;