[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Count(1)
INTO v_tag_reading_count
FROM MTH_TAG_READINGS TAG,
MTH_RUN_LOG RUN_LOG
WHERE
(TAG.CREATION_DATE < p_recal_from_date
AND TAG.CREATION_DATE > p_recal_to_date)
OR TAG.last_update_date >= RUN_LOG.FROM_DATE;
INSERT INTO MTH_TAG_READINGS_T_STG
(EQUIPMENT_FK_KEY,
WORKORDER_FK_KEY,
SEGMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
ITEM_FK_KEY,
RECIPE_VERSION,
RECIPE_NUM,
READ_TIME,
TAG_DATA,
DB_COL,
ATTR_GROUP_ID)
(SELECT DISTINCT TAG.EQUIPMENT_FK_KEY,
TAG.WORKORDER_FK_KEY,
TAG.SEGMENT_FK_KEY,
TAG.SHIFT_WORKDAY_FK_KEY,
TAG.HOUR_FK_KEY,
TAG.ITEM_FK_KEY,
TAG.RECIPE_VERSION,
TAG.RECIPE_NUM,
TAG.READING_TIME,
TAG.TAG_DATA,
EGO.DATABASE_COLUMN,
TAG.ATTRIBUTE_GROUP
FROM MTH_TAG_READINGS TAG,
MTH_RUN_LOG RUN_LOG,
EGO_ATTRS_V EGO,
EGO_ATTR_GROUPS_V EGO_ATTR_GRP
WHERE RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENTS_EXT_B'
AND TAG.creation_date >= RUN_LOG.from_date
AND TAG.creation_date <= Nvl(RUN_LOG.To_Date,SYSDATE)
AND TAG.MTH_ENTITY IS NULL
AND EGO.ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP'
AND EGO_ATTR_GRP.ATTR_GROUP_ID = TAG.ATTRIBUTE_GROUP
AND EGO.ATTR_GROUP_NAME = EGO_ATTR_GRP.ATTR_GROUP_NAME
AND EGO.ATTR_ID = TAG.ATTRIBUTE
AND TAG.processed_flag = 0);
mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
UPDATE MTH_TAG_READINGS
SET processed_flag = 1
WHERE reading_time IN (SELECT read_time
FROM MTH_TAG_READINGS_T_STG)
AND processed_flag=0
AND mth_entity IS NULL;
mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_TAG_READINGS_T_STG
(EQUIPMENT_FK_KEY,
WORKORDER_FK_KEY,
SEGMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
ITEM_FK_KEY,
RECIPE_VERSION,
RECIPE_NUM,
READ_TIME,
TAG_DATA,
DB_COL,
ATTR_GROUP_ID)
(SELECT TAG.EQUIPMENT_FK_KEY,
TAG.WORKORDER_FK_KEY,
TAG.SEGMENT_FK_KEY,
TAG.SHIFT_WORKDAY_FK_KEY,
TAG.HOUR_FK_KEY,
TAG.ITEM_FK_KEY,
TAG.RECIPE_VERSION,
TAG.RECIPE_NUM,
TAG.READING_TIME,
TAG.TAG_DATA,
EGO.DATABASE_COLUMN,
TAG.GROUP_ID
FROM MTH_TAG_READINGS TAG,
MTH_RUN_LOG RUN_LOG,
EGO_ATTRS_V EGO,
EGO_ATTR_GROUPS_V EGO_ATTR_GRP
WHERE RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENTS_EXT_B'
AND TAG.EQUIPMENT_FK_KEY = NVL(p_equipment_pk_key, TAG.EQUIPMENT_FK_KEY)
AND TAG.CREATION_DATE >= RUN_LOG.FROM_DATE
AND TAG.CREATION_DATE < RUN_LOG.TO_DATE
AND EGO.ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP'
AND EGO_ATTR_GRP.ATTR_GROUP_ID = TAG.GROUP_ID
AND EGO.ATTR_GROUP_NAME = EGO_ATTR_GRP.ATTR_GROUP_NAME
AND EGO.ATTR_ID = TAG.ATTRIBUTE);
DELETE FROM MTH_EQUIPMENTS_EXT_B EQEXT
WHERE EQEXT.READ_TIME > p_recal_from_date
AND EQEXT.READ_TIME <= p_recal_to_date
AND NVL(p_equipment_pk_key,EQEXT.EQUIPMENT_PK_KEY) in
(SELECT distinct EQUIPMENT_PK_KEY from MTH_EQUIPMENTS_D
WHERE PLANT_FK_KEY = p_plant);
INSERT INTO MTH_TAG_READINGS_T_STG
(EQUIPMENT_FK_KEY,
WORKORDER_FK_KEY,
SEGMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
ITEM_FK_KEY,
RECIPE_VERSION,
RECIPE_NUM,
READ_TIME,
TAG_DATA,
DB_COL,
ATTR_GROUP_ID)
SELECT TAG.EQUIPMENT_FK_KEY,
TAG.WORKORDER_FK_KEY,
TAG.SEGMENT_FK_KEY,
TAG.SHIFT_WORKDAY_FK_KEY,
TAG.HOUR_FK_KEY,
TAG.ITEM_FK_KEY,
TAG.RECIPE_VERSION,
TAG.RECIPE_NUM,
TAG.READING_TIME,
TAG.TAG_DATA,
EGO.DATABASE_COLUMN,
TAG.ATTRIBUTE_GROUP
FROM MTH_TAG_READINGS TAG,
EGO_ATTRS_V EGO,
EGO_ATTR_GROUPS_V EGO_ATTR_GRP,
MTH_EQUIPMENTS_D EQUIP
WHERE TAG.CREATION_DATE <= p_recal_to_date
AND EGO.ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP'
AND EGO_ATTR_GRP.ATTR_GROUP_ID = TAG.ATTRIBUTE_GROUP
AND TAG.MTH_ENTITY IS NULL
AND EGO.ATTR_GROUP_NAME = EGO_ATTR_GRP.ATTR_GROUP_NAME
AND EGO.ATTR_ID = TAG.ATTRIBUTE
AND(( p_equipment_pk_key IS NOT NULL AND TAG.EQUIPMENT_FK_KEY = p_equipment_pk_key)
OR (EQUIP.PLANT_FK_KEY = NVL( p_plant, EQUIP.PLANT_FK_KEY)))
AND TAG.EQUIPMENT_FK_KEY = EQUIP.EQUIPMENT_PK_KEY
AND TAG.EQUIPMENT_FK_KEY = NVL(p_equipment_pk_key, TAG.EQUIPMENT_FK_KEY)
AND EQUIP.PLANT_FK_KEY = NVL(p_plant, EQUIP.PLANT_FK_KEY);
SELECT DISTINCT DB_COL FROM MTH_TAG_READINGS_T_STG;
SELECT TAG_DATA,
EQUIPMENT_FK_KEY,
WORKORDER_FK_KEY,
SEGMENT_FK_KEY,
SHIFT_WORKDAY_FK_KEY,
HOUR_FK_KEY,
ITEM_FK_KEY,
READ_TIME,
ATTR_GROUP_ID,
RECIPE_NUM,
RECIPE_VERSION,
NVL(FND_GLOBAL.User_Id,-1)l_updated_by,
NVL(FND_GLOBAL.Login_Id,-1)l_last_update_login
FROM MTH_TAG_READINGS_T_STG
WHERE DB_COL = '||''''||v_colname||''''||') TS
ON (';
UPDATE
SET ED.'||v_colname||' = TS.TAG_DATA,
ED.LAST_UPDATED_BY = TS.l_updated_by,';
v_stmt := v_stmt||'ED.LAST_UPDATE_LOGIN = TS.l_last_update_login
WHEN NOT MATCHED THEN
INSERT ('||v_colname||',EXTENSION_ID, EQUIPMENT_PK_KEY,WORKORDER_FK_KEY,SEGMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY, HOUR_FK_KEY, ITEM_FK_KEY, READ_TIME, ATTR_GROUP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,';
v_stmt||'LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE,RECIPE_NUM,RECIPE_VERSION)
VALUES (TS.TAG_DATA,EGO_EXTFWK_S.NEXTVAL, TS.EQUIPMENT_FK_KEY, TS.WORKORDER_FK_KEY, TS.SEGMENT_FK_KEY,TS.SHIFT_WORKDAY_FK_KEY,TS.HOUR_FK_KEY, TS.ITEM_FK_KEY, TS.READ_TIME,';
v_stmt := v_stmt||'TS.ATTR_GROUP_ID,'||''''||SYSDATE||''''||',TS.l_updated_by,TS.l_last_update_login,TS.l_updated_by,'||''''||SYSDATE||''''||',TS.RECIPE_NUM, TS.RECIPE_VERSION)';
RAISE_APPLICATION_ERROR(-20008,'The Tag Data you are tyring to insert is of Character Data Type. A number is expected instead.');
INSERT INTO MTH_EXT_ATTR_ERR
(
ATTR_GRP_NAME,
ATTR_NAME,
ATTR_VALUE,
GROUP_ID
)
SELECT stg_egv.attr_grp_name,
Nvl(Decode(stg_egv.attr_group_type,
'MTH_USER_ENTITIES_GROUP',stg_egv.ATTR_NAME,
eav.ATTR_NAME),stg_egv.ATTR_NAME) attr_name,
stg_egv.attr_value,
stg_egv.group_id
FROM ego_attrs_v eav,
(
SELECT stg.*,
egv.attr_group_name,
egv.attr_group_id,
egv.attr_group_type
FROM ego_attr_groups_v egv,
mth_ext_attr_i_stg stg
WHERE egv.APPLICATION_ID(+) = 9001
AND egv.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
'ITEMS','MTH_ITEMS_GROUP',
'OTHERS','MTH_OTHERS_GROUP',
'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
AND stg.attr_grp_name = egv.attr_group_name(+)) stg_egv
WHERE eav.APPLICATION_ID(+) = 9001
AND eav.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
'ITEMS','MTH_ITEMS_GROUP',
'OTHERS','MTH_OTHERS_GROUP',
'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
AND stg_egv.attr_group_name = eav.attr_group_name(+)
AND stg_egv.attr_name = eav.attr_name(+)
AND (
(eav.DATABASE_COLUMN IS NULL
AND NOT (stg_egv.ATTR_NAME IN ('EQUIPMENT','ITEM','OTHER','WORKORDER','SEGMENT','USER_ENTITY','ENTITY_TYPE'))
)
OR (stg_egv.ATTR_GROUP_ID IS NULL)
OR (eav.REQUIRED_FLAG ='Y' AND stg_egv.ATTR_VALUE IS NULL)
OR (P_ENTITY='EQUIPMENTS' AND stg_egv.ATTR_NAME='EQUIPMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT EQUIPMENT_PK_KEY FROM MTH_EQUIPMENTS_D))
OR (P_ENTITY='ITEMS' AND stg_egv.ATTR_NAME='ITEM' AND stg_egv.ATTR_VALUE NOT IN (SELECT ITEM_PK_KEY FROM MTH_ITEMS_EXT_B))
OR (P_ENTITY='OTHERS' AND stg_egv.ATTR_NAME='OTHER' AND stg_egv.ATTR_VALUE NOT IN (SELECT OTHER_PK_KEY FROM MTH_OTHERS_EXT_B))
OR (P_ENTITY='PRODUCTION_SCHEDULES' AND stg_egv.ATTR_NAME='WORKORDER' AND stg_egv.ATTR_VALUE NOT IN (SELECT WORKORDER_PK_KEY FROM MTH_PRODUCTION_SCHEDULES_EXT_B))
OR (P_ENTITY='PRODUCTION_SEGMENTS' AND stg_egv.ATTR_NAME='SEGMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT SEGMENT_PK_KEY FROM MTH_PRODUCTION_SEGMENTS_EXT_B))
OR (P_ENTITY='USER_ENTITIES' AND stg_egv.ATTR_NAME='USER_ENTITY' AND stg_egv.ATTR_VALUE NOT IN
(
SELECT e.ENTITY_PK_KEY
FROM MTH_USER_ENTITIES_EXT_B e,
mth_ext_attr_i_stg s
WHERE stg_egv.group_id = s.group_id
AND s.ATTR_NAME = 'ENTITY_TYPE'
AND s.ATTR_VALUE = e.ENTITY_TYPE
)
)
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EXT_ATTR_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT INTO MTH_EXT_ATTR_T_STG
(
ATTR_GROUP_ID,
ATTR_NAME,
ATTR_VALUE,
GROUP_ID,
DB_COL,
UNIQUE_KEY_FLAG
)
SELECT stg_egv.attr_group_id,
Nvl(Decode(stg_egv.attr_group_type,
'MTH_USER_ENTITIES_GROUP',stg_egv.ATTR_NAME,
eav.ATTR_NAME),stg_egv.ATTR_NAME) attr_name,
stg_egv.attr_value,
stg_egv.group_id,
eav.database_column,
eav.unique_key_flag
FROM ego_attrs_v eav,
(
SELECT stg.*,
egv.attr_group_name,
egv.attr_group_id,
egv.attr_group_type
FROM ego_attr_groups_v egv,
mth_ext_attr_i_stg stg
WHERE egv.APPLICATION_ID(+) = 9001
AND egv.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
'ITEMS','MTH_ITEMS_GROUP',
'OTHERS','MTH_OTHERS_GROUP',
'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
AND stg.attr_grp_name = egv.attr_group_name(+)) stg_egv
WHERE eav.APPLICATION_ID(+) = 9001
AND eav.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
'ITEMS','MTH_ITEMS_GROUP',
'OTHERS','MTH_OTHERS_GROUP',
'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
AND stg_egv.attr_group_name = eav.attr_group_name(+)
AND stg_egv.attr_name = eav.attr_name(+)
AND NOT(
(
(eav.DATABASE_COLUMN IS NULL
AND NOT (stg_egv.ATTR_NAME IN ('EQUIPMENT','ITEM','OTHER','WORKORDER','SEGMENT','USER_ENTITY','ENTITY_TYPE'))
)
OR (stg_egv.ATTR_GROUP_ID IS NULL)
OR (eav.REQUIRED_FLAG ='Y' AND stg_egv.ATTR_VALUE IS NULL)
OR (P_ENTITY='EQUIPMENTS' AND stg_egv.ATTR_NAME='EQUIPMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT EQUIPMENT_PK_KEY FROM MTH_EQUIPMENTS_D))
OR (P_ENTITY='ITEMS' AND stg_egv.ATTR_NAME='ITEM' AND stg_egv.ATTR_VALUE NOT IN (SELECT ITEM_PK_KEY FROM MTH_ITEMS_EXT_B))
OR (P_ENTITY='OTHERS' AND stg_egv.ATTR_NAME='OTHER' AND stg_egv.ATTR_VALUE NOT IN (SELECT OTHER_PK_KEY FROM MTH_OTHERS_EXT_B))
OR (P_ENTITY='PRODUCTION_SCHEDULES' AND stg_egv.ATTR_NAME='WORKORDER' AND stg_egv.ATTR_VALUE NOT IN (SELECT WORKORDER_PK_KEY FROM MTH_PRODUCTION_SCHEDULES_EXT_B))
OR (P_ENTITY='PRODUCTION_SEGMENTS' AND stg_egv.ATTR_NAME='SEGMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT SEGMENT_PK_KEY FROM MTH_PRODUCTION_SEGMENTS_EXT_B))
OR (P_ENTITY='USER_ENTITIES' AND stg_egv.ATTR_NAME='USER_ENTITY' AND stg_egv.ATTR_VALUE NOT IN
(
SELECT e.ENTITY_PK_KEY
FROM MTH_USER_ENTITIES_EXT_B e,
mth_ext_attr_i_stg s
WHERE stg_egv.group_id = s.group_id
AND s.ATTR_NAME = 'ENTITY_TYPE'
AND s.ATTR_VALUE = e.ENTITY_TYPE
)
)
)
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EXT_ATTR_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
INSERT
INTO
MTH_EXT_ATTR_ERR
(ATTR_GRP_NAME,
ATTR_NAME,
ATTR_VALUE,
GROUP_ID)
(SELECT
egv.attr_group_name,
stg.ATTR_NAME,
stg.ATTR_VALUE,
stg.attr_group_id
FROM
ego_attr_groups_v egv,
MTH_EXT_ATTR_ERR err,
MTH_EXT_ATTR_T_STG stg
WHERE
( stg.ATTR_GROUP_ID = egv.ATTR_GROUP_ID) AND
(stg.GROUP_ID = err.GROUP_ID) AND
( egv.ATTR_GROUP_NAME = err.ATTR_GRP_NAME)
);
mth_util_pkg.log_msg('Number of rows inserted in MTH_EXT_ATTR_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
DELETE
FROM
MTH_EXT_ATTR_T_STG
WHERE EXISTS
( SELECT 1 FROM
(SELECT
egv.ATTR_GROUP_ID,
err.ATTR_NAME,
err.ATTR_VALUE,
err.GROUP_ID
FROM
ego_attr_groups_v egv,
MTH_EXT_ATTR_ERR err
WHERE
(egv.APPLICATION_ID = 9001 ) AND
(egv.ATTR_GROUP_TYPE = Decode(P_ENTITY,'EQUIPMENTS','MTH_EQUIPMENTS_GROUP','ITEMS','MTH_ITEMS_GROUP','OTHERS','MTH_OTHERS_GROUP',
'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP','PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')) AND
(err.ATTR_GRP_NAME = egv.ATTR_GROUP_NAME)
));
mth_util_pkg.log_msg('Number of rows deleted from MTH_EXT_ATTR_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);