[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mseaa.ASSET_ACTIVITY_ID,
mseaa.ORGANIZATION_ID,
mseaa.ASSET_REBUILD_ITEM_ID,
mseaa.SR_INSTANCE_ID
FROM MSC_ST_EAM_ACT_ASSOCIATIONS mseaa
WHERE mseaa.deleted_flag = MSC_UTIL.SYS_YES
AND mseaa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
SELECT mseaa.ASSET_GROUP_ITEM_ID,
mseaa.ORGANIZATION_ID,
mseaa.ASSET_ACTIVITY_ID,
mseaa.EQUIPMENT_ITEM_ID,
mseaa.SR_INSTANCE_ID
FROM MSC_ST_EAM_ASSET_EQUIP_DTLS mseaa
WHERE mseaa.deleted_flag = MSC_UTIL.SYS_YES
AND mseaa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ACT_ASSOCIATIONS',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ACT_ASSOCIATIONS',
MSC_CL_COLLECTION.v_instance_id,
null,
MSC_CL_COLLECTION.v_sub_str);
lv_cursor_stmt := 'SELECT'
||' m1.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
||' mseaa.ORGANIZATION_ID,'
||' mseaa.ACTIVITY,'
||' m2.INVENTORY_ITEM_ID ASSET_REBUILD_ITEM_ID,'
||' mseaa.ACTIVITY_TYPE,'
||' mseaa.ASSET_REBUILD_GROUP,'
||' mseaa.EAM_ITEM_TYPE,'
||' mseaa.SR_INSTANCE_ID'
||' FROM MSC_ST_EAM_ACT_ASSOCIATIONS mseaa,'
||' MSC_ITEM_ID_LID m1,'
||' MSC_ITEM_ID_LID m2'
||' WHERE mseaa.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
--||' AND mseaa.PLAN_ID= -1'
||' AND mseaa.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
||' AND m1.SR_INVENTORY_ITEM_ID= mseaa.ASSET_ACTIVITY_ID'
||' AND mseaa.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
||' AND m2.SR_INVENTORY_ITEM_ID= mseaa.ASSET_REBUILD_ITEM_ID'
||' AND mseaa.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
lv_sql_stmt:= 'insert into '||lv_tbl
||' ( ASSET_ACTIVITY_ID,'
||' ORGANIZATION_ID,'
||' ACTIVITY,'
||' ASSET_REBUILD_ITEM_ID,'
||' ACTIVITY_TYPE,'
||' ASSET_REBUILD_GROUP,'
||' EAM_ITEM_TYPE,'
||' SR_INSTANCE_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
||' VALUES'
||'( :ASSET_ACTIVITY_ID,'
||' :ORGANIZATION_ID,'
||' :ACTIVITY,'
||' :ASSET_REBUILD_ITEM_ID,'
||' :ACTIVITY_TYPE,'
||' :ASSET_REBUILD_GROUP,'
||' :EAM_ITEM_TYPE,'
||' :SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user)';
lv_sql_ins:= 'insert into '||lv_tbl
||' ( ASSET_ACTIVITY_ID,'
||' ORGANIZATION_ID,'
||' ACTIVITY,'
||' ASSET_REBUILD_ITEM_ID,'
||' ACTIVITY_TYPE,'
||' ASSET_REBUILD_GROUP,'
||' EAM_ITEM_TYPE,'
||' SR_INSTANCE_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
||' SELECT '
||' m1.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
||' mseaa.ORGANIZATION_ID,'
||' mseaa.ACTIVITY,'
||' m2.INVENTORY_ITEM_ID ASSET_REBUILD_ITEM_ID,'
||' mseaa.ACTIVITY_TYPE,'
||' mseaa.ASSET_REBUILD_GROUP,'
||' mseaa.EAM_ITEM_TYPE,'
||' mseaa.SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date, '
||' :v_current_user, '
||' :v_current_date, '
||' :v_current_user '
||' FROM MSC_ST_EAM_ACT_ASSOCIATIONS mseaa,'
||' MSC_ITEM_ID_LID m1,'
||' MSC_ITEM_ID_LID m2'
||' WHERE mseaa.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
--||' AND mseaa.PLAN_ID= -1'
||' AND mseaa.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
||' AND m1.SR_INVENTORY_ITEM_ID= mseaa.ASSET_ACTIVITY_ID'
||' AND mseaa.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
||' AND m2.SR_INVENTORY_ITEM_ID= '
||' mseaa.ASSET_REBUILD_ITEM_ID'
||' AND mseaa.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
DELETE MSC_EAM_ACT_ASSOCIATIONS
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND ASSET_ACTIVITY_ID = c_rec.asset_activity_id
AND ORGANIZATION_ID = c_rec.organization_id
AND ASSET_REBUILD_ITEM_ID = c_rec.asset_rebuild_item_id;
UPDATE MSC_EAM_ACT_ASSOCIATIONS
SET ACTIVITY = lv_ACTIVITY,
ACTIVITY_TYPE =lv_ACTIVITY_TYPE,
ASSET_REBUILD_GROUP = lv_ASSET_REBUILD_GROUP,
EAM_ITEM_TYPE = lv_EAM_ITEM_TYPE,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ASSET_ACTIVITY_ID = lv_asset_activity_id
AND ORGANIZATION_ID = lv_organization_id
AND ASSET_REBUILD_ITEM_ID = lv_asset_rebuild_item_id;
lv_sql_stmt:= 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_EAM_ACT_ASSOCIATIONS'
||' WHERE sr_instance_id = '
||MSC_CL_COLLECTION.v_instance_id
||' AND organization_id not '||MSC_UTIL.v_in_org_str;
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ASSET_EQUIP_DTLS',
MSC_CL_COLLECTION.v_instance_id,
-1);
MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ASSET_EQUIP_DTLS',
MSC_CL_COLLECTION.v_instance_id,
null,
MSC_CL_COLLECTION.v_sub_str);
lv_cursor_stmt := 'SELECT'
||' m1.INVENTORY_ITEM_ID ASSET_GROUP_ITEM_ID,'
||' mse.ORGANIZATION_ID,'
||' m2.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
||' mse.ASSET_NUMBER_ID,'
||' mse.ASSET_NUMBER,'
||' m3.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID,'
||' mse.EQUIPMENT_SERIAL_NUM,'
||' mse.RESOURCE_ID,'
||' mse.DEPARTMENT_ID,'
||' mse.RESOURCE_CODE,'
||' mse.SCHEDULE_TO_INSTANCE,'
||' mse.SR_INSTANCE_ID'
||' FROM MSC_ST_EAM_ASSET_EQUIP_DTLS mse,'
||' MSC_ITEM_ID_LID m1,'
||' MSC_ITEM_ID_LID m2,'
||' MSC_ITEM_ID_LID m3'
||' WHERE mse.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
||' AND mse.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
||' AND m1.SR_INVENTORY_ITEM_ID= mse.ASSET_GROUP_ITEM_ID'
||' AND mse.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
||' AND m2.SR_INVENTORY_ITEM_ID= mse.ASSET_ACTIVITY_ID'
||' AND mse.SR_INSTANCE_ID= m3.SR_INSTANCE_ID'
||' AND m3.SR_INVENTORY_ITEM_ID= mse.EQUIPMENT_ITEM_ID'
||' AND mse.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
lv_sql_stmt:= 'insert into '||lv_tbl
||'(ASSET_GROUP_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ASSET_ACTIVITY_ID,'
||' ASSET_NUMBER_ID,'
||' ASSET_NUMBER,'
||' EQUIPMENT_ITEM_ID,'
||' EQUIPMENT_SERIAL_NUM,'
||' RESOURCE_ID,'
||' DEPARTMENT_ID,'
||' RESOURCE_CODE,'
||' SCHEDULE_TO_INSTANCE,'
||' SR_INSTANCE_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
||' VALUES'
||'( :ASSET_GROUP_ITEM_ID,'
||' :ORGANIZATION_ID,'
||' :ASSET_ACTIVITY_ID,'
||' :ASSET_NUMBER_ID,'
||' :ASSET_NUMBER,'
||' :EQUIPMENT_ITEM_ID,'
||' :EQUIPMENT_SERIAL_NUM,'
||' :RESOURCE_ID,'
||' :DEPARTMENT_ID,'
||' :RESOURCE_CODE,'
||' :SCHEDULE_TO_INSTANCE,'
||' :SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user)';
lv_sql_ins:= 'insert into '||lv_tbl
||'(ASSET_GROUP_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ASSET_ACTIVITY_ID,'
||' ASSET_NUMBER_ID,'
||' ASSET_NUMBER,'
||' EQUIPMENT_ITEM_ID,'
||' EQUIPMENT_SERIAL_NUM,'
||' RESOURCE_ID,'
||' DEPARTMENT_ID,'
||' RESOURCE_CODE,'
||' SCHEDULE_TO_INSTANCE,'
||' SR_INSTANCE_ID,'
||' REFRESH_NUMBER,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' CREATION_DATE,'
||' CREATED_BY)'
||'SELECT'
||' m1.INVENTORY_ITEM_ID ASSET_GROUP_ITEM_ID,'
||' mse.ORGANIZATION_ID,'
||' m2.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
||' mse.ASSET_NUMBER_ID,'
||' mse.ASSET_NUMBER,'
||' m3.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID,'
||' mse.EQUIPMENT_SERIAL_NUM,'
||' mse.RESOURCE_ID,'
||' mse.DEPARTMENT_ID,'
||' mse.RESOURCE_CODE,'
||' mse.SCHEDULE_TO_INSTANCE,'
||' mse.SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date, '
||' :v_current_user, '
||' :v_current_date, '
||' :v_current_user '
||' FROM MSC_ST_EAM_ASSET_EQUIP_DTLS mse,'
||' MSC_ITEM_ID_LID m1,'
||' MSC_ITEM_ID_LID m2,'
||' MSC_ITEM_ID_LID m3'
||' WHERE mse.SR_INSTANCE_ID= '
||MSC_CL_COLLECTION.v_instance_id
||' AND mse.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
||' AND m1.SR_INVENTORY_ITEM_ID= mse.ASSET_GROUP_ITEM_ID'
||' AND mse.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
||' AND m2.SR_INVENTORY_ITEM_ID= mse.ASSET_ACTIVITY_ID'
||' AND mse.SR_INSTANCE_ID= m3.SR_INSTANCE_ID'
||' AND m3.SR_INVENTORY_ITEM_ID= mse.EQUIPMENT_ITEM_ID'
||' AND mse.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
DELETE MSC_EAM_ASSET_EQUIP_DTLS
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND ASSET_GROUP_ITEM_ID = c_rec.ASSET_GROUP_ITEM_ID
AND ORGANIZATION_ID = c_rec.organization_id
AND ASSET_ACTIVITY_ID = c_rec.ASSET_ACTIVITY_ID
AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID;
UPDATE MSC_EAM_ASSET_EQUIP_DTLS
SET ASSET_NUMBER_ID = lv_ASSET_NUMBER_ID,
ASSET_NUMBER =lv_ASSET_NUMBER,
EQUIPMENT_SERIAL_NUM = lv_EQUIPMENT_SERIAL_NUM,
RESOURCE_ID = lv_RESOURCE_ID,
DEPARTMENT_ID = lv_DEPARTMENT_ID,
RESOURCE_CODE = lv_RESOURCE_CODE,
SCHEDULE_TO_INSTANCE = lv_SCHEDULE_TO_INSTANCE,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ASSET_GROUP_ITEM_ID = lv_ASSET_GROUP_ITEM_ID
AND ASSET_ACTIVITY_ID = lv_asset_activity_id
AND ORGANIZATION_ID = lv_organization_id
AND EQUIPMENT_ITEM_ID = lv_EQUIPMENT_ITEM_ID;
lv_sql_stmt:= 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_EAM_ASSET_EQUIP_DTLS'
||' WHERE sr_instance_id = '
||MSC_CL_COLLECTION.v_instance_id
||' AND organization_id not '||MSC_UTIL.v_in_org_str;
DELETE MSC_EAM_ACT_ASSOCIATIONS
WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
AND ASSET_ACTIVITY_ID = c_rec.asset_activity_id
AND ORGANIZATION_ID = c_rec.organization_id
AND ASSET_REBUILD_ITEM_ID = c_rec.asset_rebuild_item_id;
'INSERT INTO '||lv_tbl
||'( ASSET_ACTIVITY_ID,'
||'ORGANIZATION_ID,'
||'ACTIVITY,'
||'ASSET_REBUILD_ITEM_ID,'
||'ACTIVITY_TYPE,'
||'ASSET_REBUILD_GROUP,'
||'EAM_ITEM_TYPE,'
||'REFRESH_NUMBER,'
||'SR_INSTANCE_ID,'
||'LAST_UPDATE_DATE,'
||'LAST_UPDATED_BY,'
||'CREATION_DATE,'
||'CREATED_BY) '
||'VALUES'
||'( :ASSET_ACTIVITY_ID,'
||':ORGANIZATION_ID,'
||':ACTIVITY,'
||':ASSET_REBUILD_ITEM_ID,'
||':ACTIVITY_TYPE,'
||':ASSET_REBUILD_GROUP,'
||':EAM_ITEM_TYPE,'
||':REFRESH_NUMBER,'
||':SR_INSTANCE_ID,'
||':v_current_date,'
||':v_current_user,'
||':v_current_date,'
||':v_current_user)';
UPDATE MSC_EAM_ACT_ASSOCIATIONS
SET
ACTIVITY = c_rec.ACTIVITY,
ACTIVITY_TYPE = c_rec.ACTIVITY_TYPE,
ASSET_REBUILD_GROUP = c_rec.ASSET_REBUILD_GROUP,
EAM_ITEM_TYPE = c_rec.EAM_ITEM_TYPE,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND ASSET_ACTIVITY_ID = c_rec.asset_activity_id
AND ORGANIZATION_ID = c_rec.organization_id
AND ASSET_REBUILD_ITEM_ID = c_rec.asset_rebuild_item_id;
/*Delete msc_table based on org group ???? wher wil that be handled...*/
-- ========= Prepare the Cursor Statement ==========
IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
lv_sql_ins := 'insert into '||lv_tbl
||' ( PLAN_ID,'
||' TRANSACTION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ORDER_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' NEW_SCHEDULE_DATE,'
||' FIRM_PLANNED_TYPE, '
||' NEW_WIP_START_DATE,'
||' ORDER_TYPE,'
||' COLL_ORDER_TYPE, '
||' WIP_STATUS_CODE,'
||' ASSET_ITEM_ID ,'
||' SCHEDULE_DESIGNATOR_ID,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' CLASS_CODE,'
||' SOURCE_ITEM_ID,'
||' TO_BE_EXPLODED,'
||' SR_INSTANCE_ID, '
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
||'SELECT'
||' -1,'
||' MSC_SUPPLIES_S.NEXTVAL,'
||' t1.INVENTORY_ITEM_ID,'
||' ms.ORGANIZATION_ID,'
||' ''AGGR-'''||'||MSC_SUPPLIES_AGGR_WO_S.NEXTVAL,'
||' ms.NEW_ORDER_QUANTITY,'
||' ms.NEW_SCHEDULE_DATE,'
||' ms.FIRM_PLANNED_TYPE,'
||' ms.NEW_WIP_START_DATE,'
||' ms.ORDER_TYPE,'
||' ms.COLL_ORDER_TYPE, '
||' 17,'
||' t2.INVENTORY_ITEM_ID,'
-- ||' ms.ASSET_ITEM_ID ,'
||' md.DESIGNATOR_ID,'
||' ms.MAINTENANCE_OBJECT_SOURCE,'
||' ms.CLASS_CODE,'
||' ms.SOURCE_ITEM_ID,'
||' ms.TO_BE_EXPLODED,'
||' ms.SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_ITEM_ID_LID t2,'
||' MSC_ST_SUPPLIES ms,'
||' MSC_DESIGNATORS md'
||' WHERE t1.SR_INVENTORY_ITEM_ID= -1002'
||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND t2.SR_INVENTORY_ITEM_ID= ms.ASSET_ITEM_ID '
||' AND t2.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND ms.DELETED_FLAG = '|| MSC_UTIL.SYS_NO
||' AND ms.SCHEDULE_DESIGNATOR_ID = md.SRC_SIM_FCST_ID'
||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND ms.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
||' AND ms.ORDER_TYPE = 92'
||' AND ms.MAINTENANCE_OBJECT_SOURCE = 1'
||' AND md.DESIGNATOR_TYPE = 12'
||' AND md.organization_id = ms.organization_id '
||' AND md.designator = ''-23453''';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'no.of rows inserted '||SQL%ROWCOUNT);
'SELECT '
||' -1,'
||' MSC_DEMANDS_S.NEXTVAL,'
||' ms.TRANSACTION_ID DISPOSITION_ID ,'
||' substr(ms.ORDER_NUMBER, 1,62) ORDER_NUMBER, '
||' ms.SCHEDULE_DESIGNATOR_ID,'
||' md.ORGANIZATION_ID,'
||' t1.INVENTORY_ITEM_ID,'
||' ms.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
||' md.USING_REQUIREMENT_QUANTITY,'
||' md.USING_ASSEMBLY_DEMAND_DATE,'
||' md.SR_INSTANCE_ID,'
||' md.DEMAND_TYPE, '
||' md.ORIGINATION_TYPE,'
||' md.MAINTENANCE_OBJECT_SOURCE,'
||' 1,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ITEM_ID_LID t1,'
||' MSC_ST_DEMANDS md,'
||' MSC_DESIGNATORS md1 ,'
|| lv_supplies_tbl||' ms'
||' WHERE'
||' ms.MAINTENANCE_OBJECT_SOURCE = 1'
||' AND md.MAINTENANCE_OBJECT_SOURCE = 1'
||' AND ms.SOURCE_ITEM_ID =md.USING_ASSEMBLY_ITEM_ID'
||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
||' AND md.ASSET_ITEM_ID = ms.INVENTORY_ITEM_ID'
||' AND md.CLASS_CODE = ms.CLASS_CODE'
--||' AND ms.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
||' AND ms.ORDER_TYPE = 92 '
||' AND md.ORIGINATION_TYPE = 92'
||' AND ms.PLAN_ID = -1'
||' AND ms.NEW_WIP_START_DATE = md.USING_ASSEMBLY_DEMAND_DATE'
||' AND ms.ORGANIZATION_ID = md.ORGANIZATION_ID'
||' AND md.SR_INSTANCE_ID= '|| MSC_CL_COLLECTION.v_instance_id
||' AND t1.SR_INVENTORY_ITEM_ID= md.inventory_item_id '
||' AND t1.sr_instance_id= '|| MSC_CL_COLLECTION.v_instance_id
||' AND md.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
||' AND md.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND md1.DESIGNATOR_TYPE = 12'
||' AND md1.organization_id = ms.organization_id '
||' AND md1.designator = ''-23453''';
'INSERT INTO '||lv_tbl
||' ( PLAN_ID,'
||' DEMAND_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' SCHEDULE_DESIGNATOR_ID,'
||' ORGANIZATION_ID,'
||' INVENTORY_ITEM_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_REQUIREMENT_QUANTITY,'
||' USING_ASSEMBLY_DEMAND_DATE, '
||' SR_INSTANCE_ID, '
||' DEMAND_TYPE, '
||' ORIGINATION_TYPE, '
||' MAINTENANCE_OBJECT_SOURCE,'
||' OP_SEQ_NUM,'
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
|| lv_cursor_stmt ;
'SELECT '
||' -1, '
||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
||' ms.TRANSACTION_ID ,'
||' mrr.DEPARTMENT_ID,'
||' mrr.ORGANIZATION_ID,'
||' mrr.RESOURCE_ID,'
||' mrr.ASSIGNED_UNITS,'
||' mrr.OPERATION_HOURS_REQUIRED,'
||' mrr.TOUCH_TIME,'
||' mrr.UNADJUSTED_RESOURCE_HOURS,'
||' mrr.OPERATION_HOURS_REQUIRED,'
||' ms.NEW_WIP_START_DATE START_DATE,'
||' LAST_DAY(NEW_WIP_START_DATE) END_DATE,'
||' mrr.supply_type,'
||' mrr.MAINTENANCE_OBJECT_SOURCE,'
||' 1,'
||' 1,'
||' 1,'
||' mrr.SR_INSTANCE_ID,'
||' :v_last_collection_id,'
||' :v_current_date,'
||' :v_current_user,'
||' :v_current_date,'
||' :v_current_user '
||' FROM MSC_ST_RESOURCE_REQUIREMENTS mrr, '
||' MSC_DESIGNATORS md1 ,'
|| lv_supplies_tbl||' ms '
||' WHERE ms.MAINTENANCE_OBJECT_SOURCE = 1'
||' AND mrr.MAINTENANCE_OBJECT_SOURCE = 1'
||' AND mrr.CLASS_CODE = ms.CLASS_CODE'
||' AND ms.ORDER_TYPE = 92 '
||' AND mrr.supply_type = 92 '
||' AND ms.PLAN_ID = -1'
||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
||' AND ms.SOURCE_ITEM_ID = mrr.INVENTORY_ITEM_ID'
||' AND ms.NEW_WIP_START_DATE = mrr.START_DATE'
||' AND ms.ORGANIZATION_ID = mrr.ORGANIZATION_ID'
||' AND mrr.SR_INSTANCE_ID='|| MSC_CL_COLLECTION.v_instance_id
||' AND mrr.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
||' AND mrr.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
||' AND mrr.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
||' AND md1.DESIGNATOR_TYPE = 12'
||' AND md1.organization_id = ms.organization_id '
||' AND md1.designator = ''-23453''';
lv_sql_stmt:= 'INSERT INTO '||lv_tbl
||' ( PLAN_ID,'
||' TRANSACTION_ID,'
||' SUPPLY_ID,'
||' DEPARTMENT_ID,'
||' ORGANIZATION_ID,'
||' RESOURCE_ID,'
||' ASSIGNED_UNITS, '
||' RESOURCE_HOURS,'
||' TOUCH_TIME,'
||' UNADJUSTED_RESOURCE_HOURS,'
||' TOTAL_RESOURCE_HOURS,'
||' START_DATE,'
||' END_DATE,'
||' SUPPLY_TYPE,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' OPERATION_SEQ_NUM,'
||' RESOURCE_SEQ_NUM,'
||' SCHEDULE_FLAG,'
||' SR_INSTANCE_ID, '
||' REFRESH_NUMBER, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
|| lv_cursor_stmt ;
lv_cursor_stmt := 'SELECT b.BILL_SEQUENCE_ID, '
||'b.ASSEMBLY_TYPE, '
||'mea.ASSET_REBUILD_ITEM_ID, '
||'b.ORGANIZATION_ID, '
||'mea.ASSET_ACTIVITY_ID, '
||'b.REPAIRABLE, '
||'b.PLAN_ID, '
||'b.SR_INSTANCE_ID '
||'FROM MSC_BOMS b, '
||'MSC_EAM_ACT_ASSOCIATIONS mea '
||'WHERE b.ASSEMBLY_ITEM_ID = mea.ASSET_REBUILD_ITEM_ID '
||'AND b.ACTIVITY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
||'AND b.ORGANIZATION_ID = mea.ORGANIZATION_ID '
||'AND b.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
||'AND b.PLAN_ID =-1 '
||'AND b.SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id
||' AND b.repairable =1 ';
UPDATE MSC_BOMS
SET BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID,
ASSEMBLY_TYPE =lv_ASSEMBLY_TYPE,
ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID,
ORGANIZATION_ID = lv_ORGANIZATION_ID,
ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID,
REPAIRABLE = lv_REPAIRABLE,
--PLAN_ID = lv_PLAN_ID,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND PLAN_ID = lv_PLAN_ID
AND BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID
AND ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID
AND ORGANIZATION_ID = lv_organization_id
AND ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'after update of MSC_BOMS: count ' || cnt);
'INSERT INTO MSC_BOMS ( '
||'BILL_SEQUENCE_ID, '
||'ASSEMBLY_TYPE, '
||'ASSEMBLY_ITEM_ID, '
||'ORGANIZATION_ID, '
||'ACTIVITY_ITEM_ID, '
||'REPAIRABLE, '
||'PLAN_ID, '
||'SR_INSTANCE_ID, '
||'REFRESH_NUMBER, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY ) '
||'SELECT '
||'b.BILL_SEQUENCE_ID, '
||'b.ASSEMBLY_TYPE, '
||'mea.ASSET_REBUILD_ITEM_ID, '
||'b.ORGANIZATION_ID, '
||'mea.ASSET_ACTIVITY_ID, '
||'1, '
||'b.PLAN_ID, '
||'b.SR_INSTANCE_ID, '
||':v_last_collection_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||'FROM MSC_BOMS b, '
||'MSC_EAM_ACT_ASSOCIATIONS mea '
||'WHERE b.ASSEMBLY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
||'AND b.ORGANIZATION_ID = mea.ORGANIZATION_ID '
||'AND b.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
||'AND b.PLAN_ID =-1 '
||'AND b.SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id
||' and (b.BILL_SEQUENCE_ID,mea.ASSET_REBUILD_ITEM_ID,mea.ASSET_ACTIVITY_ID,b.ORGANIZATION_ID) '
||' not in '
||'(select BILL_SEQUENCE_ID,ASSEMBLY_ITEM_ID,ACTIVITY_ITEM_ID,ORGANIZATION_ID '
||'from MSC_BOMS where PLAN_ID =-1 '
||' AND SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id ||') ';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'rows inserted into msc_boms '
||SQL%ROWCOUNT);
lv_cursor_stmt1 := 'SELECT '
||'mr.ROUTING_SEQUENCE_ID, '
||'mr.ROUTING_TYPE, '
||'mea.ASSET_REBUILD_ITEM_ID, '
||'mr.ORGANIZATION_ID, '
||'mea.ASSET_ACTIVITY_ID, '
||'mr.REPAIRABLE, '
||'mr.PLAN_ID, '
||'mr.SR_INSTANCE_ID '
||'FROM MSC_ROUTINGS mr, '
||'MSC_EAM_ACT_ASSOCIATIONS mea '
||'WHERE mr.ASSEMBLY_ITEM_ID = mea.ASSET_REBUILD_ITEM_ID '
||'AND mr.ACTIVITY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
||'AND mr.ORGANIZATION_ID = mea.ORGANIZATION_ID '
||'AND mr.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
||'AND mr.repairable=1 '
||'AND mr.PLAN_ID =-1 '
||'AND mr.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id;
UPDATE MSC_ROUTINGS
SET ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID,
ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID,
ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID,
ROUTING_TYPE =lv_ROUTING_TYPE,
ORGANIZATION_ID = lv_ORGANIZATION_ID,
REPAIRABLE = lv_REPAIRABLE,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND PLAN_ID = lv_PLAN_ID
AND ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID
AND ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID
AND ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'after update of MSC_ROUTINGS: count ' || cnt1);
'INSERT INTO MSC_ROUTINGS ( '
||'ROUTING_SEQUENCE_ID, '
||'ROUTING_TYPE, '
||'ASSEMBLY_ITEM_ID, '
||'ORGANIZATION_ID, '
||'ACTIVITY_ITEM_ID, '
||'REPAIRABLE, '
||'PLAN_ID, '
||'SR_INSTANCE_ID, '
||'REFRESH_NUMBER, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY ) '
||'SELECT '
||'mr.ROUTING_SEQUENCE_ID, '
||'mr.ROUTING_TYPE, '
||'mea.ASSET_REBUILD_ITEM_ID, '
||'mr.ORGANIZATION_ID, '
||'mea.ASSET_ACTIVITY_ID, '
||'1, '
||'mr.PLAN_ID, '
||'mr.SR_INSTANCE_ID, '
||':v_last_collection_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||'FROM MSC_ROUTINGS mr, '
||'MSC_EAM_ACT_ASSOCIATIONS mea '
||'WHERE mr.ASSEMBLY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
||'AND mr.ORGANIZATION_ID = mea.ORGANIZATION_ID '
||'AND mr.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
||'AND mr.PLAN_ID =-1 '
||'AND mr.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id
||' AND (mr.ROUTING_SEQUENCE_ID,mea.ASSET_REBUILD_ITEM_ID,mea.ASSET_ACTIVITY_ID,mr.ORGANIZATION_ID)'
||' not in '
||'(select ROUTING_SEQUENCE_ID, ASSEMBLY_ITEM_ID,ASSET_ACTIVITY_ID,ORGANIZATION_ID '
||'from MSC_ROUTINGS where PLAN_ID =-1 '
||'AND SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id ||') ';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'rows inserted into msc_routings'
||SQL%ROWCOUNT);
lv_cursor_stmt2 := 'SELECT '
||'mpe.PROCESS_SEQUENCE_ID, '
||'mpe.ROUTING_SEQUENCE_ID, '
||'mpe.BILL_SEQUENCE_ID, '
||'mea.ASSET_REBUILD_ITEM_ID, '
||'mea.ASSET_ACTIVITY_ID, '
||'mpe.ORGANIZATION_ID, '
||'mpe.EFFECTIVITY_DATE, '
||'mpe.LINE_ID, '
||'mpe.REPAIRABLE, '
||'mpe.PLAN_ID, '
||'mpe.SR_INSTANCE_ID '
||'FROM MSC_PROCESS_EFFECTIVITY mpe, '
||'MSC_EAM_ACT_ASSOCIATIONS mea '
||'WHERE mpe.ITEM_ID = mea.ASSET_REBUILD_ITEM_ID '
||'AND mpe.ACTIVITY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
||'AND mpe.ORGANIZATION_ID = mea.ORGANIZATION_ID '
||'AND mpe.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
||'AND mpe.PLAN_ID =-1 '
||'AND mpe.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id
||' AND mpe.repairable =1';
UPDATE MSC_PROCESS_EFFECTIVITY
SET PROCESS_SEQUENCE_ID =lv_PROCESS_SEQUENCE_ID,
ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID,
BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID,
ITEM_ID = lv_ASSET_REBUILD_ITEM_ID,
ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID,
ORGANIZATION_ID = lv_ORGANIZATION_ID,
EFFECTIVITY_DATE = lv_EFFECTIVITY_DATE,
LINE_ID = lv_LINE_ID,
REPAIRABLE = lv_REPAIRABLE,
--PLAN_ID = lv_PLAN_ID,
REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
AND PLAN_ID = lv_PLAN_ID
AND PROCESS_SEQUENCE_ID =lv_PROCESS_SEQUENCE_ID
AND ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID
AND BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID
AND ITEM_ID = lv_ASSET_REBUILD_ITEM_ID
AND ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID
AND EFFECTIVITY_DATE = lv_EFFECTIVITY_DATE
AND LINE_ID = lv_LINE_ID
AND REPAIRABLE = lv_REPAIRABLE;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'after update of MSC_PROCESS_EFFECTIVITY: count ' || cnt2);
'INSERT INTO MSC_PROCESS_EFFECTIVITY ( '
||'PROCESS_SEQUENCE_ID, '
||'ROUTING_SEQUENCE_ID, '
||'BILL_SEQUENCE_ID, '
||'ITEM_ID, '
||'ACTIVITY_ITEM_ID, '
||'ORGANIZATION_ID, '
||'EFFECTIVITY_DATE, '
||'LINE_ID, '
||'REPAIRABLE, '
||'PLAN_ID, '
||'SR_INSTANCE_ID, '
||'REFRESH_NUMBER, '
||'LAST_UPDATE_DATE, '
||'LAST_UPDATED_BY, '
||'CREATION_DATE, '
||'CREATED_BY ) '
||'SELECT '
||'MSC_PROCESS_EFFECTIVITY_S.NEXTVAL, '
||'mpe.ROUTING_SEQUENCE_ID, '
||'mpe.BILL_SEQUENCE_ID, '
||'mea.ASSET_REBUILD_ITEM_ID, '
||'mea.ASSET_ACTIVITY_ID, '
||'mpe.ORGANIZATION_ID, '
||'mpe.EFFECTIVITY_DATE, '
||'mpe.LINE_ID, '
||'1, '
||'mpe.PLAN_ID, '
||'mpe.SR_INSTANCE_ID, '
||':v_last_collection_id, '
||':v_current_date, '
||':v_current_user, '
||':v_current_date, '
||':v_current_user '
||' FROM MSC_PROCESS_EFFECTIVITY mpe, '
||' MSC_EAM_ACT_ASSOCIATIONS mea '
||' WHERE mpe.ITEM_ID = mea.ASSET_ACTIVITY_ID '
||' AND mpe.ORGANIZATION_ID = mea.ORGANIZATION_ID '
||' AND mpe.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
||' AND mpe.PLAN_ID =-1 '
||' AND mpe.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id
||' AND (mea.ASSET_REBUILD_ITEM_ID,mpe.ROUTING_SEQUENCE_ID,mpe.BILL_SEQUENCE_ID,mea.ASSET_ACTIVITY_ID,mpe.LINE_ID,mpe.ORGANIZATION_ID)'
||' not in '
||' (select ITEM_ID,ROUTING_SEQUENCE_ID,BILL_SEQUENCE_ID,ACTIVITY_ITEM_ID,LINE_ID,ORGANIZATION_ID '
||' from MSC_PROCESS_EFFECTIVITY where PLAN_ID =-1 '
||' AND SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id ||') ';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'rows inserted into msc_process_effec'
||'tivity '||SQL%ROWCOUNT);
select s.inventory_item_id,s.asset_item_id,s.order_type,s.order_number,
s.transaction_id,s.organization_id,s.activity_item_id
from msc_supplies s,
msc_eam_act_associations mea
where s.asset_item_id = mea.asset_rebuild_item_id
and s.inventory_item_id = mea.asset_rebuild_item_id
and s.activity_item_id = mea.asset_activity_id
and s.organization_id = mea.organization_id
and s.sr_instance_id = mea.sr_instance_id
and mea.activity_type = 6
and s.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and s.plan_id=-1
and s.order_type=70
and s.maintenance_object_source=1
and s.schedule_designator_id is null
order by s.transaction_id;
update msc_supplies ms
set ms.produces_to_stock =1
where ms.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
and ms.plan_id=-1
and ms.order_type= c_rec.order_type
and ms.maintenance_object_source=1
and ms.schedule_designator_id is null
and ms.asset_item_id = c_rec.asset_item_id
and ms.organization_id = c_rec.organization_id
and ms.inventory_item_id = c_rec.inventory_item_id
and ms.activity_item_id = c_rec.activity_item_id ;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Updated rows'||SQL%ROWCOUNT);