[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
mth_util_pkg.log_msg('Inserting MTH_DIM_HIERARCHY ', mth_util_pkg.G_DBG_USER_INFO);
SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Resource Group Hierarchy' HIERARCHY_NAME, 'MTH_EQUIP_RG' HIER_INTERNAL_NAME, 3 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Department Hierarchy' HIERARCHY_NAME, 'MTH_EQUIP_DEPT' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -3 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Equipment Hierarchy 01' HIERARCHY_NAME, 'MTH_EQUIP_HIER1' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -4 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Equipment Hierarchy 02' HIERARCHY_NAME, 'MTH_EQUIP_HIER2' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 'EQUIPMENT' DIMENSION_NAME, 'Facility Hierarchy' HIERARCHY_NAME, 'MTH_EQUIP_FACILITY' HIER_INTERNAL_NAME, 5 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -2 DIMENSION_ID, -5 HIERARCHY_ID, 'ITEM' DIMENSION_NAME, 'Item Hierarchy 01' HIERARCHY_NAME, 'MTH_ITEM_HIER1' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -2 DIMENSION_ID, -6 HIERARCHY_ID, 'ITEM' DIMENSION_NAME, 'Item Hierarchy 02' HIERARCHY_NAME, 'MTH_ITEM_HIER2' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -2 DIMENSION_ID, -7 HIERARCHY_ID, 'ITEM' DIMENSION_NAME, 'Item Hierarchy 03' HIERARCHY_NAME, 'MTH_ITEM_HIER3' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'Gregorian Calendar' HIERARCHY_NAME, 'MTH_GREGORIAN CALENDAR' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'Fiscal Calendar' HIERARCHY_NAME, 'MTH_FISCAL_CALENDAR' HIER_INTERNAL_NAME, NULL NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'MTH 445 Period Calendar' HIERARCHY_NAME, 'MTH_445_PERIOD_CALENDAR' HIER_INTERNAL_NAME, 5 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'MTH 544 Period Calendar' HIERARCHY_NAME, 'MTH_544_PERIOD_CALENDAR' HIER_INTERNAL_NAME, 5 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 'TIME' DIMENSION_NAME, '13 Period Calendar' HIERARCHY_NAME, 'MTH_13_PERIOD_CALENDAR' HIER_INTERNAL_NAME, 4 NUMBER_OF_LEVEL FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -13 HIERARCHY_ID, 'TIME' DIMENSION_NAME, 'Custom Calendar' HIERARCHY_NAME, 'MTH_CUSTOM_CALENDAR' HIER_INTERNAL_NAME, NULL FROM DUAL
) s
ON (s.DIMENSION_NAME = t.DIMENSION_NAME AND s.HIERARCHY_NAME=t.HIERARCHY_NAME)
WHEN matched THEN
UPDATE SET
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
HIER_INTERNAL_NAME = s.HIER_INTERNAL_NAME,
NUMBER_OF_LEVEL = s.NUMBER_OF_LEVEL
WHEN NOT matched THEN
INSERT
(t.DIMENSION_ID,
t.HIERARCHY_ID,
t.DIMENSION_NAME,
t.HIERARCHY_NAME,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.HIER_INTERNAL_NAME,
t.NUMBER_OF_LEVEL)
VALUES
(s.DIMENSION_ID,
s.HIERARCHY_ID,
s.DIMENSION_NAME,
s.HIERARCHY_NAME,
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.HIER_INTERNAL_NAME,
s.NUMBER_OF_LEVEL);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_DIM_LEVEL_LOOKUP ', mth_util_pkg.G_DBG_USER_INFO);
SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 8 LEVEL_NUM, 'RESOURCE GROUP' LEVEL_TYPE, 'RESOURCE GROUP' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 10 LEVEL_NUM, 'EQUIPMENT' LEVEL_TYPE, 'EQUIPMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -1 HIERARCHY_ID, 9 LEVEL_NUM, 'RESOURCE' LEVEL_TYPE, 'RESOURCE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 10 LEVEL_NUM, 'EQUIPMENT' LEVEL_TYPE, 'EQUIPMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 7 LEVEL_NUM, 'SITE' LEVEL_TYPE, 'SITE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 9 LEVEL_NUM, 'RESOURCE' LEVEL_TYPE, 'RESOURCE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -2 HIERARCHY_ID, 8 LEVEL_NUM, 'DEPARTMENT' LEVEL_TYPE, 'DEPARTMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 10 LEVEL_NUM, 'EQUIPMENT' LEVEL_TYPE, 'EQUIPMENT' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 8 LEVEL_NUM, 'FLR' LEVEL_TYPE, 'FLR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 6 LEVEL_NUM, 'SITE' LEVEL_TYPE, 'SITE' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 7 LEVEL_NUM, 'BLD' LEVEL_TYPE, 'BLD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -1 DIMENSION_ID, -14 HIERARCHY_ID, 9 LEVEL_NUM, 'FS' LEVEL_TYPE, 'FS' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 2 LEVEL_NUM, 'QUARTER' LEVEL_TYPE, 'QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 3 LEVEL_NUM, 'MONTH' LEVEL_TYPE, 'MONTH' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -8 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 1 LEVEL_NUM, 'FISCAL YEAR' LEVEL_TYPE, 'FISCAL YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 3 LEVEL_NUM, 'MONTH' LEVEL_TYPE, 'MONTH' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -9 HIERARCHY_ID, 2 LEVEL_NUM, 'FISCAL QUARTER' LEVEL_TYPE, 'FISCAL QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 3 LEVEL_NUM, 'PERIOD' LEVEL_TYPE, 'PERIOD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 4 LEVEL_NUM, 'WEEK' LEVEL_TYPE, 'WEEK' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 2 LEVEL_NUM, 'QUARTER' LEVEL_TYPE, 'QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -10 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 3 LEVEL_NUM, 'PERIOD' LEVEL_TYPE, 'PERIOD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 2 LEVEL_NUM, 'QUARTER' LEVEL_TYPE, 'QUARTER' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -11 HIERARCHY_ID, 4 LEVEL_NUM, 'WEEK' LEVEL_TYPE, 'WEEK' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 4 LEVEL_NUM, 'WEEK' LEVEL_TYPE, 'WEEK' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 5 LEVEL_NUM, 'DAY' LEVEL_TYPE, 'DAY' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 3 LEVEL_NUM, 'PERIOD' LEVEL_TYPE, 'PERIOD' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL UNION ALL
SELECT -3 DIMENSION_ID, -12 HIERARCHY_ID, 1 LEVEL_NUM, 'YEAR' LEVEL_TYPE, 'YEAR' LEVEL_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID FROM DUAL
) s
ON (
s.DIMENSION_ID = t.DIMENSION_ID AND
s.HIERARCHY_ID = t.HIERARCHY_ID AND
s.LEVEL_NUM = t.LEVEL_NUM
)
WHEN matched THEN
UPDATE SET
LEVEL_TYPE = s.LEVEL_TYPE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
LEVEL_NAME = s.LEVEL_NAME
WHEN NOT matched THEN
INSERT
(t.DIMENSION_ID,
t.HIERARCHY_ID,
t.LEVEL_NUM,
t.LEVEL_TYPE,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.LEVEL_NAME)
VALUES(
s.DIMENSION_ID,
s.HIERARCHY_ID,
s.LEVEL_NUM,
s.LEVEL_TYPE,
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.LEVEL_NAME);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_ENTITIES ', mth_util_pkg.G_DBG_USER_INFO);
SELECT 'Shift' MTH_ALIAS, 1 ID FROM DUAL UNION ALL
SELECT 'Workorder' MTH_ALIAS, 2 ID FROM DUAL UNION ALL
SELECT 'Workorder Segment' MTH_ALIAS, 3 ID FROM DUAL UNION ALL
SELECT 'Item' MTH_ALIAS, 4 ID FROM DUAL UNION ALL
SELECT 'Status' MTH_ALIAS, 5 ID FROM DUAL UNION ALL
SELECT 'Completed Quantity' MTH_ALIAS, 6 ID FROM DUAL UNION ALL
SELECT 'Equipment' MTH_ALIAS, 7 ID FROM DUAL UNION ALL
SELECT 'Equipment Downtime Reason' MTH_ALIAS, 8 ID FROM DUAL UNION ALL
SELECT 'Output Quantity' MTH_ALIAS, 9 ID FROM DUAL UNION ALL
SELECT 'Rejected Quantity' MTH_ALIAS, 10 ID FROM DUAL UNION ALL
SELECT 'Rework Quantity' MTH_ALIAS, 11 ID FROM DUAL UNION ALL
SELECT 'Scrap Quantity' MTH_ALIAS, 12 ID FROM DUAL UNION ALL
SELECT 'Scrap Reason' MTH_ALIAS, 13 ID FROM DUAL UNION ALL
SELECT 'Recipe Number' MTH_ALIAS, 14 ID FROM DUAL UNION ALL
SELECT 'Recipe Version' MTH_ALIAS, 15 ID FROM DUAL UNION ALL
SELECT 'Equipment Uptime' MTH_ALIAS, 16 ID FROM DUAL UNION ALL
SELECT 'Equipment Idle Reason' MTH_ALIAS, 17 ID FROM DUAL UNION ALL
SELECT 'Equipment Cycles' MTH_ALIAS, 18 ID FROM DUAL UNION ALL
SELECT 'Equipment Fault' MTH_ALIAS, 19 ID FROM DUAL
) s
ON (s.ID = t.ID)
WHEN matched THEN
UPDATE SET
MTH_ALIAS = s.MTH_ALIAS,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT matched THEN
INSERT
(t.MTH_ALIAS,
t.ID,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(s.MTH_ALIAS,
s.ID,
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL()
);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_EQUIP_ENTITIES_MST ', mth_util_pkg.G_DBG_USER_INFO);
SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() ENTITY_PK_KEY, MTH_UTIL_PKG.MTH_UA_GET_VAL() ENTITY_PK, MTH_UTIL_PKG.MTH_UA_GET_MEANING() ENTITY_NAME, MTH_UTIL_PKG.MTH_UA_GET_MEANING() ENTITY_TYPE, MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY FROM DUAL
)s
ON (s.ENTITY_PK_KEY = t.ENTITY_PK_KEY)
WHEN matched THEN
UPDATE SET
ENTITY_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
ENTITY_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
ENTITY_TYPE = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT matched THEN
INSERT
(t.ENTITY_PK_KEY,
t.ENTITY_PK,
t.ENTITY_NAME,
t.ENTITY_TYPE,
t.SYSTEM_FK_KEY,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(s.ENTITY_PK_KEY,
s.ENTITY_PK,
s.ENTITY_NAME,
s.ENTITY_TYPE,
s.SYSTEM_FK_KEY,
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL());
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_EQUIPMENTS_D ', mth_util_pkg.G_DBG_USER_INFO);
SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() EQUIPMENT_PK_KEY, MTH_UTIL_PKG.MTH_UA_GET_VAL() EQUIPMENT_PK, MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY, MTH_UTIL_PKG.MTH_UA_GET_MEANING() EQUIPMENT_NAME, MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY FROM DUAL
)s
ON (s.EQUIPMENT_PK_KEY = t.EQUIPMENT_PK_KEY )
WHEN matched THEN
UPDATE SET
t.EQUIPMENT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.EQUIPMENT_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT(
t.EQUIPMENT_PK_KEY,
t.EQUIPMENT_PK,
t.PLANT_FK_KEY,
t.SYSTEM_FK_KEY,
t.EQUIPMENT_NAME,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL());
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_ITEM_CATEGORIES_D ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() CATEGORY_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() CATEGORY_PK,
MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() CATEGORY_NAME,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
NULL SEGMENT1,
NULL SEGMENT2,
NULL SEGMENT3,
NULL SEGMENT4,
NULL SEGMENT5,
NULL SEGMENT6,
NULL SEGMENT7,
NULL SEGMENT8,
NULL SEGMENT9,
NULL SEGMENT10,
NULL SEGMENT11,
NULL SEGMENT12,
NULL SEGMENT13,
NULL SEGMENT14,
NULL SEGMENT15,
NULL SEGMENT16,
NULL SEGMENT17,
NULL SEGMENT18,
NULL SEGMENT19,
NULL SEGMENT20
FROM DUAL CONSTANT
)s
ON (s.CATEGORY_PK_KEY = t.CATEGORY_PK_KEY )
WHEN matched THEN
UPDATE SET
t.CATEGORY_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.CATEGORY_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT1= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT2= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT3= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT4= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT5= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT6= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT7= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT8= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT9= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT10= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT11= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT12= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT13= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT14= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT15= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT16= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT17= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT18= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT19= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.SEGMENT20= MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT(
t.CATEGORY_PK_KEY,
t.CATEGORY_PK,
t.SYSTEM_FK_KEY,
t.CATEGORY_NAME,
t.SEGMENT1,
t.SEGMENT2,
t.SEGMENT3,
t.SEGMENT4,
t.SEGMENT5,
t.SEGMENT6,
t.SEGMENT7,
t.SEGMENT8,
t.SEGMENT9,
t.SEGMENT10,
t.SEGMENT11,
t.SEGMENT12,
t.SEGMENT13,
t.SEGMENT14,
t.SEGMENT15,
t.SEGMENT16,
t.SEGMENT17,
t.SEGMENT18,
t.SEGMENT19,
t.SEGMENT20,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL());
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_ITEM_COST ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_FK_KEY,
TO_DATE('1900-01-01', 'YYYY-MM-DD') FROM_DATE,
NULL TO_DATE,
0 COST,
NULL COST_ELEMENT,
1 ISCURRENT,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
SYSDATE CREATION_DATE,
SYSDATE LAST_UPDATE_DATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL() CREATION_SYSTEM_ID,
MTH_UTIL_PKG.MTH_UA_GET_VAL() LAST_UPDATE_SYSTEM_ID,
NULL USER_ATTR1,
NULL USER_ATTR2,
NULL USER_ATTR3,
NULL USER_ATTR4,
NULL USER_ATTR5,
NULL CREATED_BY,
NULL LAST_UPDATE_LOGIN,
NULL LAST_UPDATED_BY
FROM DUAL
)s
ON (t.ITEM_FK_KEY = s.ITEM_FK_KEY)
WHEN matched THEN
UPDATE SET
t.FROM_DATE = s.FROM_DATE,
t.COST = s.COST,
t.ISCURRENT = s.ISCURRENT,
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT(
t.ITEM_FK_KEY,
t.FROM_DATE,
t.COST,
t.ISCURRENT,
t.SYSTEM_FK_KEY,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.FROM_DATE,
s.COST,
s.ISCURRENT,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL());
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_ITEMS_D ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_PK,
'0' PRIMARY_UOM,
MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() ITEM_NAME
FROM DUAL CONSTANT
)s
ON (t.ITEM_PK_KEY = s.ITEM_PK_KEY)
WHEN matched THEN
UPDATE SET
t.ITEM_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.PRIMARY_UOM = s.PRIMARY_UOM,
t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.ITEM_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT(
t.ITEM_PK_KEY,
t.ITEM_PK,
t.PRIMARY_UOM,
t.PLANT_FK_KEY,
t.SYSTEM_FK_KEY,
t.ITEM_NAME,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.PRIMARY_UOM,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL());
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_LOOKUPS_D ', mth_util_pkg.G_DBG_USER_INFO);
SELECT 'MTH_BATCH_TERM_IND_L' LOOKUP_TYPE, 0 LOOKUP_CODE, 'No' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 1 LOOKUP_CODE, 'Unreleased' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_BATCH_TERM_IND_L' LOOKUP_TYPE, 1 LOOKUP_CODE, 'Yes' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 2 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 3 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 4 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 5 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 6 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 7 LOOKUP_CODE, 'Cancel' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 8 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 9 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 10 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 11 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 12 LOOKUP_CODE, 'Close' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 13 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 14 LOOKUP_CODE, 'Complete' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 15 LOOKUP_CODE, 'Cancel' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 16 LOOKUP_CODE, 'Terminated' REPORTING_CODE FROM DUAL UNION ALL
SELECT 'MTH_WORKORDER_STATUS_L' LOOKUP_TYPE, 17 LOOKUP_CODE, 'Open' REPORTING_CODE FROM DUAL
)s
ON (
t.LOOKUP_TYPE = s.LOOKUP_TYPE AND
t.LOOKUP_CODE = s.LOOKUP_CODE
)
WHEN matched THEN
UPDATE SET
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.REPORTING_CODE = s.REPORTING_CODE
WHEN NOT MATCHED THEN
INSERT(
t.LOOKUP_TYPE,
t.LOOKUP_CODE,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.REPORTING_CODE)
VALUES(
s.LOOKUP_TYPE,
s.LOOKUP_CODE,
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.REPORTING_CODE);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_PLANTS_D ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_PK,
'USD' CURRENCY_CODE,
1 SOURCE,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() PLANT_NAME,
0 GRAVEYARD_SHIFT_FLAG,
TO_DATE ('01-01-1900','DD-MM-YYYY' ) FROM_DATE
FROM
DUAL
)s
ON (t.PLANT_PK_KEY = s.PLANT_PK_KEY)
WHEN matched THEN
UPDATE SET
t.PLANT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.CURRENCY_CODE = s.CURRENCY_CODE,
t.GRAVEYARD_SHIFT_FLAG = s.GRAVEYARD_SHIFT_FLAG,
t.FROM_DATE = s.FROM_DATE,
t.SOURCE = s.SOURCE,
t.PLANT_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT
(
t.PLANT_PK_KEY,
t.PLANT_PK,
t.CURRENCY_CODE,
t.SOURCE,
t.PLANT_NAME,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.GRAVEYARD_SHIFT_FLAG,
t.FROM_DATE)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.CURRENCY_CODE,
s.SOURCE,
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.GRAVEYARD_SHIFT_FLAG,
s.FROM_DATE);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_PROD_SUB_SEGMENTS_F ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() SUB_SEGMENT_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SUB_SEGMENT_PK,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() DESCRIPTION
FROM
DUAL CONSTANT
)s
ON (t.SUB_SEGMENT_PK_KEY = s.SUB_SEGMENT_PK_KEY)
WHEN matched THEN
UPDATE SET
t.SEGMENT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SUB_SEGMENT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.DESCRIPTION = s.DESCRIPTION
WHEN NOT MATCHED THEN
INSERT(
t.SUB_SEGMENT_PK_KEY,
t.SEGMENT_FK_KEY,
t.SUB_SEGMENT_PK,
t.SYSTEM_FK_KEY,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.DESCRIPTION
)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.DESCRIPTION
);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_PRODUCTION_SCHEDULES_F ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_PK,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() WORKORDER_NAME,
1 WORKORDER_TYPE,
1 STATUS_CODE,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY
FROM
DUAL CONSTANT
)s
ON (t.WORKORDER_PK_KEY = s.WORKORDER_PK_KEY)
WHEN matched THEN
UPDATE SET
t.WORKORDER_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.WORKORDER_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.WORKORDER_TYPE = s.WORKORDER_TYPE,
t.STATUS_CODE = s.STATUS_CODE,
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT(
t.WORKORDER_PK_KEY,
t.WORKORDER_PK,
t.WORKORDER_NAME,
t.PLANT_FK_KEY,
t.WORKORDER_TYPE,
t.STATUS_CODE,
t.SYSTEM_FK_KEY,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.WORKORDER_TYPE,
s.STATUS_CODE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL()
);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_PRODUCTION_SEGMENTS_F ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() WORKORDER_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SEGMENT_PK,
MTH_UTIL_PKG.MTH_UA_GET_VAL() ITEM_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() WORKORDER_NAME,
1 WORKORDER_TYPE,
1 STATUS_CODE
FROM
DUAL CONSTANT
)s
ON (t.SEGMENT_PK_KEY = s.SEGMENT_PK_KEY)
WHEN matched THEN
UPDATE SET
t.WORKORDER_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SEGMENT_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.ITEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT(
t.WORKORDER_FK_KEY,
t.SEGMENT_PK_KEY,
t.SEGMENT_PK,
t.ITEM_FK_KEY,
t.SYSTEM_FK_KEY,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL()
);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_RESOURCE_COST ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() RESOURCE_FK_KEY,
TO_DATE ('1900-01-01', 'YYYY-MM-DD') FROM_DATE,
0 COST,
1 ISCURRENT,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY
FROM
DUAL
)s
ON (t.RESOURCE_FK_KEY = s.RESOURCE_FK_KEY)
WHEN matched THEN
UPDATE SET
t.FROM_DATE = s.FROM_DATE,
t.COST = t.COST,
t.ISCURRENT = t.ISCURRENT,
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL()
WHEN NOT MATCHED THEN
INSERT
(
t.RESOURCE_FK_KEY,
t.FROM_DATE,
t.COST,
t.ISCURRENT,
t.SYSTEM_FK_KEY,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.FROM_DATE,
s.COST,
s.ISCURRENT,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL());
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_RESOURCES_D ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() RESOURCE_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() RESOURCE_PK,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() RESOURCE_NAME,
MTH_UTIL_PKG.MTH_UA_GET_VAL() PLANT_FK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_FK_KEY,
'MACHINE' RESOURCE_TYPE
FROM
DUAL
)s
ON (t.RESOURCE_PK_KEY = s.RESOURCE_PK_KEY)
WHEN matched THEN
UPDATE SET
t.RESOURCE_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.PLANT_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_FK_KEY = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.RESOURCE_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.RESOURCE_TYPE = s.RESOURCE_TYPE
WHEN NOT MATCHED THEN
INSERT
(
t.RESOURCE_PK_KEY,
t.RESOURCE_PK,
t.PLANT_FK_KEY,
t.SYSTEM_FK_KEY,
t.RESOURCE_NAME,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.RESOURCE_TYPE)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING(),
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.RESOURCE_TYPE);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);
mth_util_pkg.log_msg('Inserting MTH_SYSTEMS_SETUP ', mth_util_pkg.G_DBG_USER_INFO);
SELECT
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_PK_KEY,
MTH_UTIL_PKG.MTH_UA_GET_VAL() SYSTEM_PK,
MTH_UTIL_PKG.MTH_UA_GET_MEANING() SYSTEM_NAME,
'OTH' SYSTEM_TYPE
FROM
DUAL
)s
ON (t.SYSTEM_PK_KEY = s.SYSTEM_PK_KEY)
WHEN matched THEN
UPDATE SET
t.SYSTEM_PK = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_TYPE = s.SYSTEM_TYPE,
t.LAST_UPDATE_DATE = SYSDATE,
t.LAST_UPDATE_SYSTEM_ID = MTH_UTIL_PKG.MTH_UA_GET_VAL(),
t.SYSTEM_NAME = MTH_UTIL_PKG.MTH_UA_GET_MEANING()
WHEN NOT MATCHED THEN
INSERT
(
t.SYSTEM_PK_KEY,
t.SYSTEM_PK,
t.SYSTEM_TYPE,
t.CREATION_DATE,
t.LAST_UPDATE_DATE,
t.CREATION_SYSTEM_ID,
t.LAST_UPDATE_SYSTEM_ID,
t.SYSTEM_NAME)
VALUES
(MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
s.SYSTEM_TYPE,
SYSDATE,
SYSDATE,
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_VAL(),
MTH_UTIL_PKG.MTH_UA_GET_MEANING()
);
mth_util_pkg.log_msg('Inserted/Updated ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_USER_INFO);