[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rep_uom_code
FROM opi_dbi_rep_uoms
WHERE measure_code = p_measure_code;
INSERT /*+ append */
INTO opi_dbi_wms_curr_utz_sub_stg (
organization_id,
subinventory_code,
locator_id,
weight_capacity_b,
weight_uom_code,
weight_capacity_rep,
volume_capacity_b,
volume_uom_code,
volume_capacity_rep)
SELECT /*+ parallel (loc) parallel (wt_conv) parallel (vol_conv)
use_hash (loc, wt_conv, vol_conv) */
loc.organization_id,
loc.subinventory_code,
loc.locator_id,
loc.max_weight weight_capacity_b,
decode (loc.weight_uom_code,
C_DUMMY_UOM_CODE, NULL,
loc.weight_uom_code) weight_uom_code,
CASE -- the order of conditions matters
WHEN loc.max_weight IS NULL THEN
-- Weight capacity is not setup up. Legitimate NULL case.
NULL
WHEN loc.wt_rep_uom_code = C_DUMMY_UOM_CODE THEN
-- No weight reporting UOM defined. Acceptable.
NULL
WHEN loc.weight_uom_code = C_DUMMY_UOM_CODE AND
loc.max_weight IS NOT NULL THEN
-- Setup error. Value defined, but no UOM.
-- Ignore this locator.
NULL
WHEN wt_conv.conversion_rate IS NULL THEN
-- Row created from pure outer join i.e. it is a valid
-- combination with non-null UOMs and there is
-- no conv. rate for this combination.
-- From_uom_code and rep_uom_code
-- will be null in the wt_conv table for this row also.
C_CONV_NOT_SETUP
WHEN wt_conv.conversion_rate < 0 THEN
-- Error found in conversion rates table.
C_CONV_NOT_SETUP
WHEN wt_conv.conversion_rate >= 0 THEN
-- Valid conv. rate found.
-- Note: allowing conv. rate = 0.
loc.max_weight * wt_conv.conversion_rate
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END weight_capacity_rep,
loc.max_cubic_area volume_capacity_b,
decode (loc.volume_uom_code,
C_DUMMY_UOM_CODE, NULL,
loc.volume_uom_code) volume_uom_code,
CASE -- the order of conditions matters
WHEN loc.max_cubic_area IS NULL THEN
-- Volume capacity is not setup up. Legitimate NULL case.
NULL
WHEN loc.vol_rep_uom_code = C_DUMMY_UOM_CODE THEN
-- No volume reporting UOM defined. Acceptable.
NULL
WHEN loc.volume_uom_code = C_DUMMY_UOM_CODE AND
loc.max_cubic_area IS NOT NULL THEN
-- Setup error. Value defined, but no UOM.
-- Ignore this locator.
NULL
WHEN vol_conv.conversion_rate IS NULL THEN
-- Row created from pure outer join i.e. it is a valid
-- combination with non-null UOMs and there is
-- no conv. rate for this combination.
-- From_uom_code and rep_uom_code
-- will be null in the vol_conv table for this row also.
C_CONV_NOT_SETUP
WHEN vol_conv.conversion_rate < 0 THEN
-- Error found in conversion rates table.
C_CONV_NOT_SETUP
WHEN vol_conv.conversion_rate >= 0 THEN
-- Valid conv. rate found.
-- Note: allowing conv. rate = 0.
loc.max_cubic_area * vol_conv.conversion_rate
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END volume_capacity_rep
FROM
(SELECT /*+ parallel (mil) parallel (mp) */
mil.organization_id,
mil.subinventory_code,
mil.inventory_location_id locator_id,
mil.max_weight max_weight,
mil.max_cubic_area max_cubic_area,
nvl (mil.location_weight_uom_code,
C_DUMMY_UOM_CODE) weight_uom_code,
l_wt_rep_uom_code wt_rep_uom_code,
nvl (mil.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
l_vol_rep_uom_code vol_rep_uom_code
FROM mtl_item_locations mil,
mtl_parameters mp
WHERE mp.wms_enabled_flag = 'Y'
AND mil.organization_id = mp.organization_id
-- filter out locators with neither capacity defined
AND ( ( mil.max_weight IS NOT NULL
AND mil.location_weight_uom_code IS NOT NULL)
OR ( mil.max_cubic_area IS NOT NULL
AND mil.volume_uom_code IS NOT NULL)
)
) loc,
opi_dbi_rep_uom_std_conv_f wt_conv,
opi_dbi_rep_uom_std_conv_f vol_conv
WHERE loc.weight_uom_code = wt_conv.from_uom_code (+)
AND loc.wt_rep_uom_code = wt_conv.rep_uom_code (+)
AND loc.volume_uom_code = vol_conv.from_uom_code (+)
AND loc.vol_rep_uom_code = vol_conv.rep_uom_code (+);
INSERT /*+ append */
INTO opi_dbi_wms_curr_utz_sub_f (
organization_id,
subinventory_code,
aggregation_level_flag,
weight_capacity,
volume_capacity,
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 (stg) */
stg.organization_id,
decode (stg.subinventory_code,
NULL, NULL,
stg.subinventory_code || '-' || stg.organization_id)
subinventory_code,
decode (grouping_id (stg.organization_id,
decode (stg.subinventory_code,
NULL, NULL,
stg.subinventory_code || '-' ||
stg.organization_id)),
0, 1,
1, 7,
-1) aggregation_level_flag,
sum (stg.weight_capacity_rep) weight_capacity_rep,
sum (stg.volume_capacity_rep) volume_capacity_rep,
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 opi_dbi_wms_curr_utz_sub_stg stg
GROUP BY
stg.organization_id,
rollup (decode (stg.subinventory_code,
NULL, NULL,
stg.subinventory_code || '-' || stg.organization_id));
SELECT DISTINCT
weight_uom_code uom_code
FROM opi_dbi_wms_curr_utz_sub_stg
WHERE weight_capacity_rep < 0;
SELECT DISTINCT
volume_uom_code uom_code
FROM opi_dbi_wms_curr_utz_sub_stg
WHERE volume_capacity_rep < 0;
INSERT /*+ append */
INTO opi_dbi_wms_curr_utz_item_f (
organization_id,
item_org_id,
uom_code,
subinventory_code,
inv_category_id,
aggregation_level_flag,
stored_qty,
weight_qty,
stored_weight,
weight_uom_code,
volume_qty,
utilized_volume,
volume_uom_code,
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 (onh) parallel (wt_conv) parallel (vol_conv)
use_hash (onh, wt_conv, vol_conv) */
onh.organization_id,
(onh.inventory_item_id || '-' || onh.organization_id)
item_org_id,
onh.primary_uom_code uom_code,
decode (onh.subinventory_code,
NULL, NULL,
(onh.subinventory_code || '-' || onh.organization_id))
subinventory_code,
nvl (onh.inv_category_id, -1) inv_category_id,
grouping_id (onh.organization_id,
nvl (onh.inv_category_id, -1),
decode (onh.subinventory_code,
NULL, NULL,
(onh.subinventory_code || '-' ||
onh.organization_id)),
(onh.inventory_item_id || '-' ||
onh.organization_id))
aggregation_level_flag,
sum (onh.stored_qty) stored_qty,
sum (onh.weight_qty) weight_qty,
sum (
CASE -- order of conditions matters
WHEN onh.unit_weight IS NULL THEN
-- Weight not setup. Acceptable.
NULL
WHEN onh.wt_rep_uom_code = C_DUMMY_UOM_CODE THEN
-- Weight reporting UOM not set up. Acceptable
NULL
WHEN wt_conv.conversion_rate IS NULL THEN
-- Row created from pure outer join i.e. it is a valid
-- combination with non-null UOMs and there is
-- no conv. rate for this combination.
-- From_uom_code and rep_uom_code
-- will be null in the wt_conv table for this row also.
C_CONV_NOT_SETUP
WHEN wt_conv.conversion_rate < 0 THEN
-- Error found in conversion rates table.
C_CONV_NOT_SETUP
WHEN wt_conv.conversion_rate >= 0 THEN
-- Valid conv. rate found.
-- Note: allowing conv. rate = 0.
onh.weight_qty * wt_conv.conversion_rate *
onh.unit_weight
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END
) stored_weight,
onh.weight_uom_code,
sum (onh.volume_qty) volume_qty,
sum (
CASE -- order of conditions matters
WHEN onh.unit_volume IS NULL THEN
-- Volume not setup. Acceptable.
NULL
WHEN onh.vol_rep_uom_code = C_DUMMY_UOM_CODE THEN
-- Volume reporting UOM not set up. Acceptable
NULL
WHEN vol_conv.conversion_rate IS NULL THEN
-- Row created from pure outer join i.e. it is a valid
-- combination with non-null UOMs and there is
-- no conv. rate for this combination.
-- From_uom_code and rep_uom_code
-- will be null in the vol_conv table for this row also.
C_CONV_NOT_SETUP
WHEN vol_conv.conversion_rate < 0 THEN
-- Error found in conversion rates table.
C_CONV_NOT_SETUP
WHEN vol_conv.conversion_rate >= 0 THEN
-- Valid conv. rate found.
-- Note: allowing conv. rate = 0.
onh.volume_qty * vol_conv.conversion_rate *
onh.unit_volume
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END
) utilized_volume,
onh.volume_uom_code,
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 (moq) parallel (mp) parallel (loc)
parallel (items) */
moq.organization_id,
moq.inventory_item_id,
items.primary_uom_code,
moq.subinventory_code,
nvl (items.weight_uom_code, C_DUMMY_UOM_CODE) weight_uom_code,
l_wt_rep_uom_code wt_rep_uom_code,
nvl (items.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
l_vol_rep_uom_code vol_rep_uom_code,
items.inv_category_id,
items.unit_weight,
items.unit_volume,
sum (moq.transaction_quantity) stored_qty,
sum (decode (weight_capacity_rep,
NULL, NULL,
moq.transaction_quantity)) weight_qty,
sum (decode (volume_capacity_rep,
NULL, NULL,
moq.transaction_quantity)) volume_qty
FROM
mtl_onhand_quantities moq,
mtl_parameters mp,
opi_dbi_wms_curr_utz_sub_stg loc,
eni_oltp_item_star items
WHERE mp.wms_enabled_flag = 'Y'
AND moq.organization_id = mp.organization_id
AND moq.organization_id = items.organization_id
AND moq.inventory_item_id = items.inventory_item_id
AND moq.organization_id = loc.organization_id
AND moq.subinventory_code = loc.subinventory_code
AND moq.locator_id = loc.locator_id
GROUP BY
moq.organization_id,
moq.inventory_item_id,
items.primary_uom_code,
moq.subinventory_code,
nvl (items.weight_uom_code, C_DUMMY_UOM_CODE),
nvl (items.volume_uom_code, C_DUMMY_UOM_CODE),
items.inv_category_id,
items.unit_weight,
items.unit_volume
) onh,
opi_dbi_rep_uom_std_conv_f wt_conv,
opi_dbi_rep_uom_std_conv_f vol_conv
WHERE onh.weight_uom_code = wt_conv.from_uom_code (+)
AND onh.wt_rep_uom_code = wt_conv.rep_uom_code (+)
AND onh.volume_uom_code = vol_conv.from_uom_code (+)
AND onh.vol_rep_uom_code = vol_conv.rep_uom_code (+)
GROUP BY
onh.organization_id,
rollup ( (nvl (onh.inv_category_id, -1),
decode (onh.subinventory_code,
NULL, NULL,
(onh.subinventory_code || '-' ||
onh.organization_id))),
((onh.inventory_item_id || '-' ||
onh.organization_id),
onh.primary_uom_code,
onh.weight_uom_code,
onh.volume_uom_code) );
SELECT DISTINCT
weight_uom_code uom_code
FROM opi_dbi_wms_curr_utz_item_f
WHERE stored_weight/weight_qty < 0
AND aggregation_level_flag = 0;
SELECT DISTINCT
volume_uom_code uom_code
FROM opi_dbi_wms_curr_utz_item_f
WHERE utilized_volume/volume_qty < 0
AND aggregation_level_flag = 0;