[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_log (p_run_date IN DATE);
PROCEDURE insert_std_rates_init (p_run_date IN DATE);
INSERT /*+ append */
INTO opi_dbi_rep_uom_conv_stg (
inventory_item_id,
from_uom_code,
from_uom_class,
rep_uom_code,
rep_uom_class,
conversion_rate,
conversion_type
)
SELECT
C_STD_RATE_ITEM_ID,
convs.uom_code,
convs.uom_class,
msr_mst.rep_uom_code,
msr_mst.rep_uom_class,
opi_dbi_rep_uom_pkg.uom_convert
(C_STD_RATE_ITEM_ID, C_CONV_PRECISION, 1,
convs.uom_code, msr_mst.rep_uom_code),
C_INTRA_CONV_TYPE
FROM
(SELECT
DISTINCT
rep_uom_code,
rep_uom_class,
base_rep_uom_code
FROM opi_dbi_rep_uoms
WHERE measure_code in ('WT', 'VOL')
AND rep_uom_code IS NOT NULL) msr_mst,
mtl_units_of_measure convs
WHERE msr_mst.rep_uom_class = convs.uom_class;
INSERT /*+ append */
INTO opi_dbi_rep_uom_conv_stg (
inventory_item_id,
from_uom_code,
from_uom_class,
rep_uom_code,
rep_uom_class,
conversion_rate,
conversion_type
)
SELECT
C_STD_RATE_ITEM_ID,
from_uoms.uom_code,
from_class.uom_class,
from_rep_bases.rep_uom_code,
from_rep_bases.rep_uom_class,
opi_dbi_rep_uom_pkg.uom_convert
(C_STD_RATE_ITEM_ID, C_CONV_PRECISION, 1,
from_uoms.uom_code, from_rep_bases.rep_uom_code),
C_INTER_CONV_TYPE
FROM
(SELECT
DISTINCT
msr_mst.rep_uom_class,
msr_mst.rep_uom_code,
msr_mst.base_rep_uom_code,
decode (msr_mst.base_rep_uom_code,
convs.to_base_uom_code, convs.from_base_uom_code,
convs.to_base_uom_code) base_from_uom_code
FROM
(SELECT
DISTINCT
rep_uom_code,
rep_uom_class,
base_rep_uom_code
FROM opi_dbi_rep_uoms
WHERE measure_code in ('WT', 'VOL')
AND rep_uom_code IS NOT NULL) msr_mst,
opi_dbi_uom_class_std_conv convs
WHERE ( convs.from_base_uom_code = msr_mst.base_rep_uom_code
OR convs.to_base_uom_code = msr_mst.base_rep_uom_code))
from_rep_bases,
mtl_units_of_measure_vl from_class,
mtl_units_of_measure_vl from_uoms
WHERE from_rep_bases.base_from_uom_code = from_class.uom_code
AND from_class.base_uom_flag = C_IS_BASE_UOM
AND from_class.uom_class = from_uoms.uom_class;
/* 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/07/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;
Deletes:
1. Rows from OPI_DBI_CONC_PROG_RUN_LOG where row type = REP_UOM.
Inserts:
1. Row into OPI_DBI_CONC_PROG_RUN_LOG where row type = REP_UOM with a
last run date that is very old (e.g. 1st Jan, 1950);
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_std_rates_init
Insert the new standard conversion rates found from the
staging table to the fact table.
No commits done here. Calling function coordinates commit.
Using an append hint on the insert since this function is designed
to be used after initial loads. Expect to commit after this function
before querying from OPI_DBI_REP_UOM_STD_CONV_F.
No commits done here. Calling function coordinates commit.
Parameters:
1. p_run_date - The run time date passed in from the wrapper routine.
Ensures that all records are created with the same
creation date as the logged run time of the program.
History:
Date Author Action
12/07/04 Dinkar Gupta Wrote Function.
*/
PROCEDURE insert_std_rates_init (p_run_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2(40) := 'insert_std_rates_init';
INSERT /*+ append */
INTO opi_dbi_rep_uom_std_conv_f (
from_uom_code,
from_uom_class,
rep_uom_code,
rep_uom_class,
conversion_rate,
conversion_type,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
stg.from_uom_code,
stg.from_uom_class,
stg.rep_uom_code,
stg.rep_uom_class,
stg.conversion_rate,
stg.conversion_type,
s_user_id,
p_run_date,
p_run_date,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM
opi_dbi_rep_uom_conv_stg stg
WHERE stg.inventory_item_id = C_STD_RATE_ITEM_ID;
RAISE INSERT_NEW_RATES_FAILED;
END insert_std_rates_init;
insert_std_rates_init (l_run_date);
update_log (l_run_date);
WHEN INSERT_NEW_RATES_FAILED THEN
rollback;
(INSERT_NEW_RATES_FAILED_MESG,
l_proc_name, l_stmt_id));
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;
Any existing combinations must be updated
only if the new rate is different from the old rate.
Do not alter records that have not changed between the previous and
current run.
No commits done here. Calling function coordinates commit.
Parameters:
1. p_run_date - The run time date passed in from the wrapper routine.
Ensures that all records are created with the same
creation date as the logged run time of the program.
History:
Date Author Action
12/07/04 Dinkar Gupta Wrote Function.
*/
PROCEDURE merge_std_rates_incr (p_run_date IN DATE)
IS
l_proc_name CONSTANT VARCHAR2 (40) := 'merge_std_rates_incr';
(SELECT
from_uom_code,
from_uom_class,
rep_uom_code,
rep_uom_class,
conversion_rate,
conversion_type,
s_user_id created_by,
p_run_date creation_date,
p_run_date last_update_date,
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_rep_uom_conv_stg
WHERE inventory_item_id = C_STD_RATE_ITEM_ID) new
ON ( base.from_uom_code = new.from_uom_code
AND base.rep_uom_code = new.rep_uom_code)
WHEN MATCHED THEN UPDATE
SET
base.conversion_rate = decode
(base.conversion_rate,
new.conversion_rate, base.conversion_rate,
new.conversion_rate),
base.last_update_date = decode
(base.conversion_rate,
new.conversion_rate, base.last_update_date,
new.last_update_date),
base.last_updated_by = decode
(base.conversion_rate,
new.conversion_rate, base.last_updated_by,
new.last_updated_by),
base.last_update_login = decode
(base.conversion_rate,
new.conversion_rate,
base.last_update_login,
new.last_update_login),
base.program_id = decode
(base.conversion_rate,
new.conversion_rate, base.program_id,
new.program_id),
base.program_login_id = decode
(base.conversion_rate,
new.conversion_rate, base.program_login_id,
new.program_login_id),
base.program_application_id = decode
(base.conversion_rate,
new.conversion_rate,
base.program_application_id,
new.program_application_id),
base.request_id = decode
(base.conversion_rate,
new.conversion_rate, base.request_id,
new.request_id)
WHEN NOT MATCHED THEN INSERT (
from_uom_code,
from_uom_class,
rep_uom_code,
rep_uom_class,
conversion_rate,
conversion_type,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
VALUES (
new.from_uom_code,
new.from_uom_class,
new.rep_uom_code,
new.rep_uom_class,
new.conversion_rate,
new.conversion_type,
new.created_by,
new.creation_date,
new.last_update_date,
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));