[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT /*+ append parallel (opi_dbi_curr_inv_exp_f) */
INTO opi_dbi_curr_inv_exp_f (
organization_id,
inventory_item_id,
item_org_id,
uom_code,
inv_category_id,
func_currency_code,
aggregation_level_flag,
onhand_qty,
expired_qty,
onhand_val_b,
onhand_val_g,
onhand_val_sg,
expired_val_b,
expired_val_g,
expired_val_sg,
conversion_rate,
sec_conversion_rate,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT /*+ parallel (exp_inv) parallel (conv_rates) */
exp_inv.organization_id,
exp_inv.inventory_item_id,
exp_inv.item_org_id,
exp_inv.primary_uom_code,
exp_inv.inv_category_id,
conv_rates.func_currency_code,
grouping_id (exp_inv.inv_category_id,
exp_inv.item_org_id)
aggregation_level_flag,
sum (exp_inv.onhand_qty) onhand_qty,
sum (exp_inv.expired_qty) expired_qty,
sum (exp_inv.onhand_val_b) onhand_val_b,
sum (exp_inv.onhand_val_b * conv_rates.conversion_rate)
onhand_val_g,
sum (exp_inv.onhand_val_b * conv_rates.sec_conversion_rate)
onhand_val_sg,
sum (exp_inv.expired_val_b) expired_val_b,
sum (exp_inv.expired_val_b * conv_rates.conversion_rate)
expired_val_g,
sum (exp_inv.expired_val_b * conv_rates.sec_conversion_rate)
expired_val_sg,
conv_rates.conversion_rate,
conv_rates.sec_conversion_rate,
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM
(
SELECT /*+ parallel (exp_qty) parallel (cic) parallel (cql)
parallel (items) parallel (msi) parallel (mp)
parallel (subs) */
exp_qty.organization_id,
exp_qty.inventory_item_id,
items.id item_org_id,
items.primary_uom_code,
nvl (items.inv_category_id, -1) inv_category_id,
sum (exp_qty.onhand_qty) onhand_qty,
sum (exp_qty.expired_qty) expired_qty,
sum (decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
(decode (msi.inventory_asset_flag,
C_EXPENSE_ITEM_FLAG, 0,
exp_qty.onhand_qty *
decode(mp.process_enabled_flag,'Y',
OPI_DBI_INV_VALUE_INIT_PKG.GET_OPM_ITEM_COST(exp_qty.organization_id,
exp_qty.inventory_item_id,
p_run_date),
decode (mp.primary_cost_method,
C_STANDARD_COSTING_ORG,
cic.item_cost,
cql.item_cost))))))
onhand_val_b,
sum (decode (subs.asset_inventory,
C_EXPENSE_SUBINVENTORY, 0,
(decode (msi.inventory_asset_flag,
C_EXPENSE_ITEM_FLAG, 0,
exp_qty.expired_qty *
decode(mp.process_enabled_flag,'Y',
OPI_DBI_INV_VALUE_INIT_PKG.GET_OPM_ITEM_COST(exp_qty.organization_id,
exp_qty.inventory_item_id,
p_run_date),
decode (mp.primary_cost_method,
C_STANDARD_COSTING_ORG,
cic.item_cost,
cql.item_cost))))))
expired_val_b
FROM
(
SELECT /*+ parallel (moq) parallel (mln)
use_hash (moq, mln) */
moq.organization_id,
moq.inventory_item_id,
moq.cost_group_id,
moq.subinventory_code,
sum (moq.transaction_quantity) onhand_qty,
sum (CASE WHEN mln.expiration_date < p_run_date THEN
moq.transaction_quantity
ELSE
0
END)
expired_qty
FROM mtl_onhand_quantities moq,
mtl_lot_numbers mln
WHERE moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND moq.lot_number = mln.lot_number
GROUP BY
moq.organization_id,
moq.inventory_item_id,
moq.cost_group_id,
moq.subinventory_code
) exp_qty,
mtl_system_items_b msi,
eni_oltp_item_star items,
mtl_parameters mp,
cst_item_costs cic,
cst_quantity_layers cql,
mtl_secondary_inventories subs
WHERE exp_qty.inventory_item_id = msi.inventory_item_id
AND exp_qty.organization_id = msi.organization_id
AND exp_qty.inventory_item_id = items.inventory_item_id
AND exp_qty.organization_id = items.organization_id
AND exp_qty.organization_id = mp.organization_id
AND exp_qty.inventory_item_id = cic.inventory_item_id (+)
AND exp_qty.organization_id = cic.organization_id (+)
AND 1 = cic.cost_type_id (+)
AND exp_qty.inventory_item_id = cql.inventory_item_id (+)
AND exp_qty.organization_id = cql.organization_id (+)
AND exp_qty.cost_group_id = cql.cost_group_id (+)
AND exp_qty.organization_id = subs.organization_id
AND exp_qty.subinventory_code = subs.secondary_inventory_name
GROUP BY
exp_qty.organization_id,
exp_qty.inventory_item_id,
items.id,
items.primary_uom_code,
nvl (items.inv_category_id, -1)
) exp_inv,
(
SELECT /*+ parallel (to_conv) parallel (curr_codes) */
to_conv.organization_id,
curr_codes.currency_code func_currency_code,
p_run_date run_date,
decode (curr_codes.currency_code,
s_global_curr_code, 1,
fii_currency.get_global_rate_primary (
curr_codes.currency_code,
p_run_date) )
conversion_rate,
decode (s_secondary_curr_code,
NULL, NULL,
curr_codes.currency_code, 1,
fii_currency.get_global_rate_secondary (
curr_codes.currency_code,
p_run_date))
sec_conversion_rate
FROM
(SELECT /*+ parallel (mtl_lot_numbers) */
DISTINCT organization_id
FROM mtl_lot_numbers) to_conv,
(SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
parallel (hoi) parallel (gsob)*/
DISTINCT hoi.organization_id, gsob.currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id))
curr_codes
WHERE curr_codes.organization_id = to_conv.organization_id
) conv_rates
WHERE conv_rates.organization_id = exp_inv.organization_id
GROUP BY
exp_inv.organization_id,
conv_rates.func_currency_code,
conv_rates.conversion_rate,
conv_rates.sec_conversion_rate,
ROLLUP (
exp_inv.inv_category_id,
(exp_inv.item_org_id,
exp_inv.inventory_item_id,
exp_inv.primary_uom_code)
);
SELECT 1
FROM opi_dbi_curr_inv_exp_f
WHERE ( nvl (conversion_rate, -999) < 0
OR nvl (sec_conversion_rate, 999) < 0)
AND rownum < 2;
SELECT /*+ parallel (compare) */
DISTINCT
report_order,
curr_code,
rate_type,
p_run_date run_date,
func_currency_code
FROM (
SELECT /*+ parallel (conv) */
s_global_curr_code curr_code,
s_global_rate_type rate_type,
1 report_order, -- ordering global currency first
conv.func_currency_code
FROM opi_dbi_curr_inv_exp_f conv
WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
AND conv.aggregation_level_flag = 3 -- org level records
UNION ALL
SELECT /*+ parallel (conv) */
s_secondary_curr_code curr_code,
s_secondary_rate_type rate_type,
2 report_order, --ordering secondary currency next
conv.func_currency_code
FROM opi_dbi_curr_inv_exp_f conv
WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
AND conv.aggregation_level_flag = 3 -- org level records
-- check here if primary not same as secondary
AND p_pri_sec_curr_same = 0
) compare
ORDER BY
report_order ASC,
func_currency_code;