[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ACT_ASSOCIATIONS'
||'(ASSET_ACTIVITY_ID,'
||'ORGANIZATION_ID,'
||'ASSET_REBUILD_ITEM_ID,'
||'DELETED_FLAG,'
||'REFRESH_NUMBER,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.ASSET_ACTIVITY_ID,'
||'x.ORGANIZATION_ID,'
||'x.ASSET_REBUILD_ITEM_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_EAM_ACT_ASSOC_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ACT_ASSOCIATIONS'
||'(ASSET_ACTIVITY_ID,'
||'ORGANIZATION_ID,'
||'ACTIVITY,'
||'ASSET_REBUILD_ITEM_ID,'
||'ACTIVITY_TYPE,'
||'ASSET_REBUILD_GROUP,'
||'EAM_ITEM_TYPE,'
||'DELETED_FLAG,'
||'REFRESH_NUMBER,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.ASSET_ACTIVITY_ID,'
||'x.ORGANIZATION_ID,'
||'x.ACTIVITY,'
||'x.INVENTORY_ITEM_ID,'
||'6,'
||'x.ASSET_REBUILD_GROUP,'
||'x.EAM_ITEM_TYPE,'
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_EAM_ACT_ASSOC_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ASSET_EQUIP_DTLS'
||'(ASSET_GROUP_ITEM_ID,'
||'ORGANIZATION_ID,'
||'ASSET_ACTIVITY_ID,'
||'ASSET_NUMBER_ID,'
||'EQUIPMENT_ITEM_ID,'
||'DELETED_FLAG,'
||'REFRESH_NUMBER,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'ASSET_GROUP_ITEM_ID,'
||'ORGANIZATION_ID,'
||'ASSET_ACTIVITY_ID,'
||'ASSET_NUMBER_ID,'
||'EQUIPMENT_ITEM_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_EAM_ASSET_EQUIP_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_EAM_ASSET_EQUIP_DTLS'
||'(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,'
||'DELETED_FLAG,'
||'REFRESH_NUMBER,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.ASSET_GROUP_ID,'
||'x.ORGANIZATION_ID,'
||'x.ASSET_ACTIVITY_ID,'
||'x.INSTANCE_ID,'
||'x.ASSET_SERIAL_NUMBER,'
||'x.EQUIPMENT_ITEM_ID,'
||'x.EQUIPMENT_SERIAL_NUMBER,'
||'y.RESOURCE_ID *2 ,'
||'y.DEPARTMENT_ID *2 ,'
||'y.RESOURCE_CODE,'
||'y.SCHEDULE_TO_INSTANCE,'
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_EAM_ASSET_EQUIP_V'
||MSC_CL_PULL.V_DBLINK||' x,'
||' MTL_EAM_EQUIP_RESOURCES_V'
||MSC_CL_PULL.V_DBLINK||' y'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
||' AND x.ORGANIZATION_ID = y.ORGANIZATION_ID (+)'
||' AND x.EQUIPMENT_SERIAL_NUMBER = y.SERIAL_NUMBER (+)'
||' AND x.EQUIPMENT_ITEM_ID = y.INVENTORY_ITEM_ID (+)';
v_sql_stmt:= ' INSERT INTO MSC_ST_DESIGNATORS'
||'(SRC_SIM_FCST_ID,'
||'ORGANIZATION_ID,'
||'DESIGNATOR,'
||'SRC_DESIGNATOR,'
||'SRC_DESCRIPTION,'
||'DESIGNATOR_TYPE,'
||'MPS_RELIEF,'
||'INVENTORY_ATP_FLAG,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.FORECAST_ID,'
||'x.ORGANIZATION_ID,'
||'-23453,'
||'x.FORECAST_NAME,'
||'x.DESCRIPTION,'
||'x.DESIGNATOR_TYPE,'
||'-999,'
||'2,'
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_EAM_FORECAST_DESIGS_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= 'Insert into MSC_ST_SUPPLIES'
||'( 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,'
||'DELETED_FLAG,'
||'ASSET_ITEM_ID,'
||'MAINTENANCE_OBJECT_SOURCE,'
||'CLASS_CODE,' --mnagilla
||'TO_BE_EXPLODED,'
||'SCHEDULE_DESIGNATOR_ID,'
||'SOURCE_ITEM_ID,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.ASSET_ITEM_ID,'
||'x.ORGANIZATION_ID,'
||'x.ORDER_NUMBER,'
||'x.NEW_ORDER_QUANTITY,'
||'LAST_DAY(x.SCHEDULED_COMPLETION_DATE),'
||'1,'
||'x.SCHEDULED_COMPLETION_DATE,'
||'x.ORDER_TYPE,'
||' 992, '
||'2,'
||'x.ASSET_ITEM_ID,'
||'x.MAINTENANCE_OBJECT_SOURCE,'
||'x.CLASS_CODE,'
||'1,'
||'x.FORECAST_ID,'
||'x.ASSET_ITEM_ID,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_EAM_FORECASTS_V '
||MSC_CL_PULL.v_dblink||' x'
||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' and x.SCHEDULED_COMPLETION_DATE >= :lv_eam_fc_st_date'
||' and x.SCHEDULED_COMPLETION_DATE <= :lv_eam_fc_end_date';
v_sql_stmt:= ' INSERT INTO MSC_ST_DEMANDS ( '
||'SCHEDULE_DESIGNATOR_ID,'
||'ORGANIZATION_ID,'
||'INVENTORY_ITEM_ID,'
||'USING_ASSEMBLY_ITEM_ID,'
||'USING_REQUIREMENT_QUANTITY,'
||'USING_ASSEMBLY_DEMAND_DATE, '
||'DEMAND_TYPE,'
||'ORIGINATION_TYPE,'
||'DELETED_FLAG,'
||'ASSET_ITEM_ID,'
||'CLASS_CODE,'
||'MAINTENANCE_OBJECT_SOURCE,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||'SELECT '
||'x.FORECAST_ID,'
||'x.ORGANIZATION_ID,'
||'x.COMPONENT,'
||'x.ASSET_ITEM_ID,'
||'x.QUANTITY,'
||'x.USING_ASSEMBLY_DEMAND_DATE,'
||'x.DEMAND_TYPE,'
||'x.ORIGINATION_TYPE,'
||' 2,'
||'x.ASSET_ITEM_ID,'
||'x.CLASS_CODE,'
||'x. MAINTENANCE_OBJECT_SOURCE,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_EAM_FC_MATREQ_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
||' AND x.USING_ASSEMBLY_DEMAND_DATE >= :lv_eam_fc_st_date '
||' AND x.USING_ASSEMBLY_DEMAND_DATE <= :lv_eam_fc_end_date '
-- ||' AND (x.RN1 >'||MSC_CL_PULL.v_lrn
-- ||' AND x.RN2 >'||MSC_CL_PULL.v_lrn||')'
;
v_sql_stmt:= ' INSERT INTO MSC_ST_RESOURCE_REQUIREMENTS '
||'(SCHEDULE_DESIGNATOR_ID,'
||'DEPARTMENT_ID,'
||'ORGANIZATION_ID,'
||'RESOURCE_ID,'
||'ASSIGNED_UNITS,'
||'START_DATE,'
||'OPERATION_HOURS_REQUIRED,'
||'TOUCH_TIME,'
||'UNADJUSTED_RESOURCE_HOURS,'
||'DELETED_FLAG,'
||'INVENTORY_ITEM_ID,'
||'CLASS_CODE,'--mnagilla
||'MAINTENANCE_OBJECT_SOURCE,'
||'supply_type,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT'
||' x.FORECAST_ID,'
||' x.DEPARTMENT_ID,'
||' x.ORGANIZATION_ID,'
||' x.RESOURCE_ID,'
||' x.ASSIGNED_UNITS ,'
||'x.START_DATE ,'
|| v_temp_sql
||' x.TOTAL_QUANTITY ,'
||' 2,'
||'x.ASSET_ITEM_ID,'
||'x.CLASS_CODE,'--mnagilla
||'x. MAINTENANCE_OBJECT_SOURCE,'
||'x.supply_type,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_EAM_FC_RESREQ_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
||' AND x.START_DATE >= :lv_eam_fc_st_date '
||' AND x.START_DATE <= :lv_eam_fc_end_date '
|| v_temp_whr
;