[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_log (p_run_date IN DATE);
SELECT rep_uom_code
FROM opi_dbi_rep_uoms
WHERE measure_code = p_measure_code
AND rep_uom_code IS NOT NULL;
/* update_log
Update the log table for the reporting UOM conversion rates ETL
with the run date provided as a parameter.
Parameters:
1. p_run_date - run date of the program, provided by the wrapper. This
is the start time of the program, which can be used
as a marker between incremental runs.
No commits done here. Calling function coordinates commit.
History:
Date Author Action
12/13/04 Dinkar Gupta Wrote Function.
*/
PROCEDURE update_log (p_run_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'update_log';
UPDATE opi_dbi_conc_prog_run_log
SET last_run_date = p_run_date,
last_update_date = sysdate,
last_updated_by = s_user_id,
last_update_login = s_login_id,
request_id = s_request_id,
program_application_id = s_program_application_id,
program_id = s_program_id,
program_login_id = s_program_login_id
WHERE etl_type = C_ETL_TYPE;
RAISE LOG_UPDATE_FAILED;
END update_log;
'SELECT DISTINCT unit_weight_uom_code
FROM ' || p_table_name || '
WHERE weight_conv_rate < 0';
'SELECT DISTINCT unit_volume_uom_code
FROM ' || p_table_name || '
WHERE volume_conv_rate < 0';
INSERT INTO opi_dbi_wms_stor_item_conv_stg (
organization_id,
inventory_item_id,
unit_weight_uom_code,
weight_conv_rate,
weight_conv_rate_type,
unit_volume_uom_code,
volume_conv_rate,
volume_conv_rate_type
)
SELECT
organization_id,
inventory_item_id,
unit_weight_uom_code,
weight_conv_rate,
weight_conv_rate_type,
unit_volume_uom_code,
volume_conv_rate,
volume_conv_rate_type
FROM opi_dbi_wms_stor_item_conv_f;
DELETE
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = C_ETL_TYPE;
INSERT INTO opi_dbi_conc_prog_run_log (
etl_type,
last_run_date,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
VALUES (C_ETL_TYPE,
C_START_RUN_DATE,
s_user_id,
sysdate,
sysdate,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id);
INSERT /*+ append parallel (opi_dbi_wms_stor_item_conv_f) */
INTO opi_dbi_wms_stor_item_conv_f (
organization_id,
inventory_item_id,
unit_weight_uom_code,
weight_conv_rate,
weight_conv_rate_type,
unit_volume_uom_code,
volume_conv_rate,
volume_conv_rate_type,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
SELECT /*+ use_hash (items, wt_conv, vol_conv)
parallel (items) parallel (wt_conv) parallel (vol_conv) */
items.organization_id,
items.inventory_item_id,
decode (items.weight_uom_code,
C_DUMMY_UOM_CODE, NULL,
items.weight_uom_code) unit_weight_uom_code,
CASE -- order of conditions matters
WHEN items.unit_weight IS NULL THEN
-- Weight not setup. Acceptable.
NULL
WHEN items.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.
wt_conv.conversion_rate
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END weight_conv_rate,
wt_conv.conversion_type weight_conv_rate_type,
decode (items.volume_uom_code,
C_DUMMY_UOM_CODE, NULL,
items.volume_uom_code) unit_volume_uom_code,
CASE -- order of conditions matters
WHEN items.unit_volume IS NULL THEN
-- Volume not setup. Acceptable.
NULL
WHEN items.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.
vol_conv.conversion_rate
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END volume_conv_rate,
vol_conv.conversion_type volume_conv_rate_type,
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 (conv_items) parallel (item_attr)
use_hash (conv_items, item_attr) */
DISTINCT
conv_items.organization_id,
conv_items.inventory_item_id,
item_attr.primary_uom_code,
nvl (item_attr.weight_uom_code, C_DUMMY_UOM_CODE) weight_uom_code,
l_wt_rep_uom_code wt_rep_uom_code,
nvl (item_attr.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
l_vol_rep_uom_code vol_rep_uom_code,
item_attr.unit_weight,
item_attr.unit_volume
FROM
(SELECT /*+ parallel (fact) parallel (mp)
use_hash (fact, mp) */
DISTINCT
fact.organization_id,
fact.inventory_item_id
FROM opi_dbi_inv_value_f fact,
mtl_parameters mp
WHERE mp.wms_enabled_flag = 'Y'
AND fact.organization_id = mp.organization_id
AND fact.source = C_DISCRETE_ORGS
) conv_items,
eni_oltp_item_star item_attr
WHERE conv_items.organization_id = item_attr.organization_id
AND conv_items.inventory_item_id = item_attr.inventory_item_id
) items,
opi_dbi_rep_uom_std_conv_f wt_conv,
opi_dbi_rep_uom_std_conv_f vol_conv
WHERE items.weight_uom_code = wt_conv.from_uom_code (+)
AND items.wt_rep_uom_code = wt_conv.rep_uom_code (+)
AND items.volume_uom_code = vol_conv.from_uom_code (+)
AND items.vol_rep_uom_code = vol_conv.rep_uom_code (+);
update_log (l_run_date);
WHEN LOG_UPDATE_FAILED THEN
rollback;
(LOG_UPDATE_FAILED_MESG,
l_proc_name, l_stmt_id));
SELECT count (1)
INTO l_cnt
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = C_ETL_TYPE;
updated as little as possible to maintain effective fast
refreshability of MVs built on top of it.
If the conversion rate for the weight/volume to the reporting UOMs
is not defined, store negative error codes.
Note also that one or both of the reporting units for weight
and volume can be NULL. That is not an error condition.
No committing of data here. The calling function handles that.
Parameters:
1. p_wt_rep_uom_code - Weight reporting UOM code
2. p_vol_rep_uom_code - Volume reporting UOM code
History:
Date Author Action
12/13/04 Dinkar Gupta Wrote Function.
*/
PROCEDURE compute_conv_rates_incr (p_wt_rep_uom_code IN VARCHAR2,
p_vol_rep_uom_code IN VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'compute_conv_rates_incr';
INSERT /*+ append */
INTO opi_dbi_wms_stor_item_conv_stg (
organization_id,
inventory_item_id,
unit_weight_uom_code,
weight_conv_rate,
weight_conv_rate_type,
unit_volume_uom_code,
volume_conv_rate,
volume_conv_rate_type)
SELECT /*+ use_hash (all_rates, existing) */
all_rates.organization_id,
all_rates.inventory_item_id,
all_rates.unit_weight_uom_code,
all_rates.weight_conv_rate,
all_rates.weight_conv_rate_type,
all_rates.unit_volume_uom_code,
all_rates.volume_conv_rate,
all_rates.volume_conv_rate_type
FROM
(SELECT /*+ use_hash (items, wt_conv, vol_conv) */
items.organization_id,
items.inventory_item_id,
decode (items.weight_uom_code,
C_DUMMY_UOM_CODE, NULL,
items.weight_uom_code) unit_weight_uom_code,
CASE -- order of conditions matters
WHEN items.unit_weight IS NULL THEN
-- Weight not setup. Acceptable.
NULL
WHEN items.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.
wt_conv.conversion_rate
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END weight_conv_rate,
wt_conv.conversion_type weight_conv_rate_type,
decode (items.volume_uom_code,
C_DUMMY_UOM_CODE, NULL,
items.volume_uom_code) unit_volume_uom_code,
CASE -- order of conditions matters
WHEN items.unit_volume IS NULL THEN
-- Volume not setup. Acceptable.
NULL
WHEN items.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.
vol_conv.conversion_rate
ELSE
-- Why will we get here? Should really never.
C_CONV_NOT_SETUP
END volume_conv_rate,
vol_conv.conversion_type volume_conv_rate_type
FROM
(SELECT /*+ use_hash (conv_items, item_attr) */
conv_items.organization_id,
conv_items.inventory_item_id,
item_attr.primary_uom_code,
nvl (item_attr.weight_uom_code, C_DUMMY_UOM_CODE) weight_uom_code,
l_wt_rep_uom_code wt_rep_uom_code,
nvl (item_attr.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
l_vol_rep_uom_code vol_rep_uom_code,
item_attr.unit_weight,
item_attr.unit_volume
FROM
(SELECT /*+ use_hash (fact, mp) */
DISTINCT
fact.organization_id,
fact.inventory_item_id
FROM opi_dbi_inv_value_f fact,
mtl_parameters mp
WHERE mp.wms_enabled_flag = 'Y'
AND fact.organization_id = mp.organization_id
AND fact.source = C_DISCRETE_ORGS
) conv_items,
eni_oltp_item_star item_attr
WHERE conv_items.organization_id = item_attr.organization_id
AND conv_items.inventory_item_id = item_attr.inventory_item_id
) items,
opi_dbi_rep_uom_std_conv_f wt_conv,
opi_dbi_rep_uom_std_conv_f vol_conv
WHERE items.weight_uom_code = wt_conv.from_uom_code (+)
AND items.wt_rep_uom_code = wt_conv.rep_uom_code (+)
AND items.volume_uom_code = vol_conv.from_uom_code (+)
AND items.vol_rep_uom_code = vol_conv.rep_uom_code (+)
) all_rates,
opi_dbi_wms_stor_item_conv_f existing
WHERE all_rates.organization_id = existing.organization_id (+)
AND all_rates.inventory_item_id = existing.inventory_item_id (+)
AND ( ( existing.organization_id IS NULL
AND existing.inventory_item_id IS NULL )
OR nvl (all_rates.unit_weight_uom_code, C_DUMMY_UOM_CODE) <>
nvl (existing.unit_weight_uom_code, C_DUMMY_UOM_CODE)
OR nvl (all_rates.weight_conv_rate, -1) <>
nvl (existing.weight_conv_rate, -1)
OR nvl (all_rates.weight_conv_rate_type, -1) <>
nvl (existing.weight_conv_rate_type, -1)
OR nvl (all_rates.unit_volume_uom_code, C_DUMMY_UOM_CODE) <>
nvl (existing.unit_volume_uom_code, C_DUMMY_UOM_CODE)
OR nvl (all_rates.volume_conv_rate, -1)<>
nvl (existing.volume_conv_rate, -1)
OR nvl (all_rates.volume_conv_rate_type, -1) <>
nvl (existing.volume_conv_rate_type, -1)
);
(SELECT
organization_id,
inventory_item_id,
unit_weight_uom_code,
weight_conv_rate,
weight_conv_rate_type,
unit_volume_uom_code,
volume_conv_rate,
volume_conv_rate_type,
sysdate creation_date,
sysdate last_update_date,
s_user_id created_by,
s_user_id last_updated_by,
s_login_id last_update_login,
s_program_id program_id,
s_program_login_id program_login_id,
s_program_application_id program_application_id,
s_request_id request_id
FROM opi_dbi_wms_stor_item_conv_stg) new
ON ( base.organization_id = new.organization_id
AND base.inventory_item_id = new.inventory_item_id)
WHEN MATCHED THEN UPDATE
SET
base.unit_weight_uom_code = new.unit_weight_uom_code,
base.weight_conv_rate = new.weight_conv_rate,
base.weight_conv_rate_type = new.weight_conv_rate_type,
base.unit_volume_uom_code = new.unit_volume_uom_code,
base.volume_conv_rate = new.volume_conv_rate,
base.volume_conv_rate_type = new.volume_conv_rate_type,
base.last_update_date = new.last_update_date,
base.last_updated_by = new.last_updated_by,
base.last_update_login = new.last_update_login,
base.program_id = new.program_id,
base.program_login_id = new.program_login_id,
base.program_application_id = new.program_application_id,
base.request_id = new.request_id
WHEN NOT MATCHED THEN
INSERT (
organization_id,
inventory_item_id,
unit_weight_uom_code,
weight_conv_rate,
weight_conv_rate_type,
unit_volume_uom_code,
volume_conv_rate,
volume_conv_rate_type,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
VALUES (
new.organization_id,
new.inventory_item_id,
new.unit_weight_uom_code,
new.weight_conv_rate,
new.weight_conv_rate_type,
new.unit_volume_uom_code,
new.volume_conv_rate,
new.volume_conv_rate_type,
new.creation_date,
new.last_update_date,
new.created_by,
new.last_updated_by,
new.last_update_login,
new.program_id,
new.program_login_id,
new.program_application_id,
new.request_id);
update_log (l_run_date);
WHEN LOG_UPDATE_FAILED THEN
rollback;
(LOG_UPDATE_FAILED_MESG,
l_proc_name, l_stmt_id));