[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION select_mv (p_mv_set IN VARCHAR2, p_mv_level_flag IN VARCHAR2,
p_view_by IN VARCHAR2, p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
RETURN VARCHAR2;
(SELECT 1
FROM org_access o
WHERE o.responsibility_id = fnd_global.resp_id
AND o.resp_application_id = fnd_global.resp_appl_id
AND o.organization_id = fact.organization_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = fact.organization_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id))) ';
WHEN 'PGM' THEN select_mv(p_mv_set => p_mv_set,
p_mv_level_flag => p_mv_level_flag,
p_view_by => p_view_by,
p_dim_map => p_dim_map)
WHEN 'WMS_CURR_UTZ_ITEM' THEN 'opi_dbi_wms_curr_utz_item_f'
WHEN 'WMS_CURR_UTZ_SUB' THEN 'opi_dbi_wms_curr_utz_sub_f'
WHEN 'WMS_STOR_UTZ' THEN 'opi_wms_004_mv'
WHEN 'PEX' THEN 'opi_wms_006_mv'
WHEN 'PER' THEN 'opi_wms_007_mv'
WHEN 'OPP' THEN 'opi_wms_008_mv'
WHEN 'OPER' THEN 'opi_wms_009_mv'
WHEN 'OTP' THEN 'opi_ontime_prod_001_mv'
WHEN 'CPD' THEN 'opi_curr_prod_del_001_mv'
WHEN 'CURR_INV_EXP' THEN 'opi_dbi_curr_inv_exp_f'
WHEN 'PROD_CONS' THEN 'opi_jobs_001_mv'
WHEN 'COGS' THEN 'opi_cogs_001_mv'
WHEN 'CURR_INV_STAT' THEN 'mtl_onhand_quantities'
WHEN 'SBR' THEN 'opi_scrap_reason_001_mv'
ELSE ''
END);
Product Gross Margin. If the MV determined by select_mv is the Inline View the
column name is parent_id
*/
PROCEDURE reinit_dim_map (p_dim_map in out NOCOPY
poa_dbi_util_pkg.poa_dbi_dim_map,
p_mv IN VARCHAR2,
p_mv_set IN VARCHAR2)
IS
l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
WHEN 'ORGANIZATION+ORGANIZATION' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
WHEN 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
'eni_resource_department_v'
WHEN 'RESOURCE+ENI_RESOURCE_GROUP' THEN 'eni_resource_group_v'
WHEN 'RESOURCE+ENI_RESOURCE' THEN 'eni_resource_v'
WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN 'opi_subinventories_v'
WHEN 'OPI_INV_CC+OPI_INV_CC_LVL' THEN 'opi_inv_cc_lvl_v'
WHEN 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' THEN 'opi_inv_cc_cls_v'
--WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'eni_item_vbh_cat_v'
WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'eni_item_vbh_nodes_v'
WHEN 'CUSTOMER+FII_CUSTOMERS' THEN 'fii_customers_v'
WHEN 'OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL' THEN
'opi_wms_task_exc_reasons_lvl_v'
WHEN 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' THEN
'opi_wms_op_plan_name_lvl_v'
WHEN 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' THEN
'opi_locators_v'
WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' THEN
'opi_inv_item_grade_lvl_v'
WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' THEN
'opi_inv_item_lot_lvl_v'
WHEN 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL' THEN
'opi_mfg_mtl_trx_reasons_lvl_v'
ELSE ''
END);
FUNCTION get_viewby_select_clause (p_viewby IN VARCHAR2)
RETURN VARCHAR2
IS
l_viewby_sel VARCHAR2(200) := '';
END get_viewby_select_clause;
of user selected parameters
*/
FUNCTION get_cca_level_flag_val (p_dim_name IN VARCHAR2,
p_dim_map IN
poa_dbi_util_pkg.poa_dbi_dim_map)
RETURN VARCHAR2
IS
-- Aggregation Levels for the various dimensions
ITEM_LEVEL_ID CONSTANT INTEGER := 1;
/* Selecting the MV for Product Gross Margin
/*++++++++++++++++++++++++++++++++++++++++*/
/*
This Function is called from process_parameters if the p_mv_set is related to
Product Gross Margin. The following are the various combinations for all the MVs selected
depending on the mv_level_flag and the Dimension Values.
-OPI_PGM_CAT_MV
-OPI_PGM_SUM_MV
-Inline View.
*/
FUNCTION select_mv (p_mv_set IN VARCHAR2, p_mv_level_flag IN VARCHAR2, p_view_by IN VARCHAR2,
p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
RETURN VARCHAR2
IS
l_mv VARCHAR2(2000) := '';
l_mv := ' (select
v.imm_child_id item_category_id,
v.parent_id parent_id,
f.fulfilled_val_b FULFILLED_VAL_B,
f.fulfilled_val_g FULFILLED_VAL_G,
f.cogs_val_b COGS_VAL_B,
f.cogs_val_g COGS_VAL_G,
f.fulfilled_qty FULFILLED_QTY,
f.customer_item_cat_flag CUSTOMER_ITEM_CAT_FLAG,
f.item_org_id ITEM_ORG_ID,
f.uom_code UOM_CODE,
f.time_id TIME_ID,
f.organization_id ORGANIZATION_ID,
f.top_model_org_id TOP_MODEL_ORG_ID,
f.customer_ID CUSTOMER_ID,
f.fulfilled_val_sg,
f.cogs_val_sg
from
opi_pgm_sum_mv f,
eni_denorm_hierarchies v,
mtl_default_category_sets m
where
m.functional_area_id = 11
and v.object_id = m.category_set_id
and v.dbi_flag = ''Y''
and v.object_type = ''CATEGORY_SET''
and f.item_category_id = v.child_id
)';
END select_mv;
FUNCTION get_fact_select_columns (p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_fact_select_cols VARCHAR2(400);
l_fact_select_cols := '';
l_fact_select_cols := l_fact_select_cols ||
p_join_tbl(l_num).fact_column || ',
';
l_fact_select_cols := rtrim (l_fact_select_cols, ',
') || ' ';
return l_fact_select_cols;
END get_fact_select_columns;
insert the trunc of the sysdate into one of our DBI log
table, which would be used by all ETL's as the R12
migration date.
----------------------------------------------------------------------------------------------
*/
PROCEDURE set_inv_convergence_date (x_return_status OUT NOCOPY VARCHAR2)
IS
l_stmt_id NUMBER;
SELECT trunc(sysdate)
INTO l_inv_convergence_date
FROM dual;
which was inserted by the 'set'procedure into one of DBI
log table viz. OPI_DBI_CONC_PROG_RUN_LOG.
If such date is not found in the log table, we look at
the max of the last update in the Inventory Balances Table
viz. IC_LOCT_INV.
For a new customer directly going into R12, the above 2
would not give any date, so in that case we simply use the
sysdate for the purpose.
The procedure insert_inv_convergence_date is used to insert
the date in the log table in case it was not earlier
present there.
----------------------------------------------------------------------------------------------
*/
PROCEDURE get_inv_convergence_date (p_inv_migration_date OUT NOCOPY DATE)
IS
l_stmt_id NUMBER;
l_inv_migration_date ic_loct_inv.last_update_date%type;
SELECT count (*)
INTO l_rowcount
FROM
OPI_DBI_CONC_PROG_RUN_LOG
WHERE
ETL_TYPE = 'R12_MIGRATION';
SELECT
last_run_date
INTO
l_inv_migration_date
FROM
opi_dbi_conc_prog_run_log
WHERE
etl_type = 'R12_MIGRATION';
SELECT
MAX(last_update_date)
INTO
l_inv_migration_date
FROM
ic_loct_inv
WHERE
migrated_ind = 1;
update date from Inventory Balances Table');
'R12_MIGRATION'.The commit to the log table after inserting
/upadting this row would be done in this procedure only.
----------------------------------------------------------------------------------------------
*/
PROCEDURE merge_inv_convergence_date (p_migration_date IN DATE)
IS
l_user_id NUMBER;
SELECT
'R12_MIGRATION' ETL_TYPE,
p_migration_date LAST_RUN_DATE , -- R12 migration date
sysdate CREATION_DATE,
sysdate LAST_UPDATE_DATE,
l_user_id CREATED_BY,
l_user_id LAST_UPDATED_BY,
l_login_id LAST_UPDATE_LOGIN,
l_request_id REQUEST_ID,
l_program_application_id PROGRAM_APPLICATION_ID,
l_program_id PROGRAM_ID,
l_program_login_id PROGRAM_LOGIN_ID,
'-1' DRIVING_TABLE_CODE,
'-1' LOAD_TYPE,
'-1' BOUND_TYPE,
NULL BOUND_LEVEL_ENTITY_CODE,
NULL BOUND_LEVEL_ENTITY_ID,
NULL FROM_BOUND_DATE,
NULL TO_BOUND_DATE,
NULL FROM_BOUND_ID,
NULL TO_BOUND_ID,
NULL COMPLETION_STATUS_CODE,
NULL STOP_REASON_CODE
FROM
dual) migration_data
ON (log.etl_type = migration_data.etl_type
)
WHEN MATCHED THEN
UPDATE SET
log.last_run_date = migration_data.last_run_date
,log.last_update_date = migration_data.last_update_date
,log.last_updated_by = migration_data.last_updated_by
,log.last_update_login = migration_data.last_update_login
WHEN NOT MATCHED THEN
INSERT (ETL_TYPE, LAST_RUN_DATE, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_LOGIN_ID, DRIVING_TABLE_CODE, LOAD_TYPE, BOUND_TYPE,
BOUND_LEVEL_ENTITY_CODE, BOUND_LEVEL_ENTITY_ID, FROM_BOUND_DATE,
TO_BOUND_DATE, FROM_BOUND_ID, TO_BOUND_ID, COMPLETION_STATUS_CODE,
STOP_REASON_CODE)
VALUES (migration_data.etl_type, migration_data.LAST_RUN_DATE, migration_data.CREATION_DATE,
migration_data.LAST_UPDATE_DATE, migration_data.CREATED_BY, migration_data.LAST_UPDATED_BY,
migration_data.LAST_UPDATE_LOGIN, migration_data.REQUEST_ID, migration_data.PROGRAM_APPLICATION_ID,
migration_data.PROGRAM_ID, migration_data.PROGRAM_LOGIN_ID, migration_data.DRIVING_TABLE_CODE,
migration_data.LOAD_TYPE, migration_data.BOUND_TYPE, migration_data.BOUND_LEVEL_ENTITY_CODE,
migration_data.BOUND_LEVEL_ENTITY_ID, migration_data.FROM_BOUND_DATE, migration_data.TO_BOUND_DATE,
migration_data.FROM_BOUND_ID, migration_data.TO_BOUND_ID, migration_data.COMPLETION_STATUS_CODE,
migration_data.STOP_REASON_CODE);