[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_sql_stmt:= ' INSERT INTO MSC_ST_VISITS'
||'(VISIT_ID,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.VISIT_ID,'
||'x.ORGANIZATION_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_AHL_VISITS_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_VISITS'
||'(VISIT_ID,'
||'VISIT_NAME,'
||'VISIT_DESC,'
||'VISIT_START_DATE,'
||'VISIT_END_DATE,'
||'ORGANIZATION_ID,'
||' DELETED_FLAG,'
||'VISIT_TYPE,'
||'VISIT_PRIORITY,'
||'VISIT_STATUS,'
||'VISIT_NUMBER,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.VISIT_ID,'
||'x.VISIT_NAME,'
||'x.DESCRIPTION,'
||'x.START_DATE_TIME,'
||'x.CLOSE_DATE_TIME,'
||'x.ORGANIZATION_ID,'
||'2,'
||'x.VISIT_TYPE_CODE,'
||'x.PRIORITY_CODE,'
||'x.STATUS_CODE,'
||'x.VISIT_NUMBER,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AP_VISITS_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_WO_ATTRIBUTES'
||'(WIP_ENTITY_ID,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.WIP_ENTITY_ID,'
||'x.ORGANIZATION_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_WO_ATTRIBUTES_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;
||' SELECT '
||'x.SERIAL_NUMBER,'
||'x.WIP_ENTITY_ID,'
||'x.FLEET_HEADER_ID,'
||'x.PRODUCES_TO_STOCK,'
||'x.ORGANIZATION_ID,'
||'2,'
||'x.VISIT_ID,'
||'x.VISIT_NAME,'
||'x.PARAMETER1,'
||'x.PARAMETER2,'
||'x.PARAMETER3,'
||'x.PARAMETER4,'
||'x.PARAMETER5,'
||'x.PARAMETER6,'
||'x.PARAMETER7,'
||'x.PARAMETER8,'
||'x.PARAMETER9,'
||'x.MASTER_WORKORDER_FLAG,'
||'x.PREV_MILESTONE,'
||'x.NEXT_MILESTONE,'
||'x.PRODUCT_CLASSIFICATION,'
-- ||'x.Operating_Fleet,'/* remove the above line and uncomment this line for bug fix# 12428753 */
||'x.MAINTENANCE_REQT,'
||'x.ITEM_ALTERNATES_EXIST,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_WO_ATTRIBUTES_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN2> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_WO_ATTRIBUTES'
||'(SERIAL_NUM,'
||'WIP_ENTITY_ID,'
||'FLEET_HEADER_ID,'
||'PRODUCES_TO_STOCK,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'VISIT_ID,'
||'VISIT_NAME,'
||'PARAMETER1,'
||'PARAMETER2,'
||'PARAMETER3,'
||'PARAMETER4,'
||'PARAMETER5,'
||'PARAMETER6,'
||'PARAMETER7,'
||'PARAMETER8,'
||'PARAMETER9,'
||'MASTER_WO,'
||'PREV_MILESTONE,'
||'NEXT_MILESTONE,'
||'PRODUCT_CLASSIFICATION,'
-- ||'Operating_Fleet,'/* remove the above line and uncomment this line for bug fix# 12428753 */
||'MAINTENANCE_REQT,'
||'ITEM_ALTERNATES_EXIST,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.SERIAL_NUMBER,'
||'x.WIP_ENTITY_ID,'
||'x.FLEET_HEADER_ID,'
||'x.PRODUCES_TO_STOCK,'
||'x.ORGANIZATION_ID,'
||'2,'
||'x.VISIT_ID,'
||'x.VISIT_NAME,'
||'x.PARAMETER1,'
||'x.PARAMETER2,'
||'x.PARAMETER3,'
||'x.PARAMETER4,'
||'x.PARAMETER5,'
||'x.PARAMETER6,'
||'x.PARAMETER7,'
||'x.PARAMETER8,'
||'x.PARAMETER9,'
||'x.MASTER_WORKORDER_FLAG,'
||'x.PREV_MILESTONE,'
||'x.NEXT_MILESTONE,'
||'x.PRODUCT_CLASSIFICATION,'
-- ||'x.Operating_Fleet,'/* remove the above line and uncomment this line for bug fix# 12428753 */
||'x.MAINTENANCE_REQT,'
||'x.ITEM_ALTERNATES_EXIST,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_WO_ATTRIBUTES_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
|| v_union_sql ;
v_sql_stmt:= ' INSERT INTO MSC_ST_WO_TASK_HIERARCHY'
||'(TASK_LINK_ID,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.TASK_LINK_ID,'
||'x.ORGANIZATION_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_WO_TASK_HIERARCHY_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;
||' SELECT '
||'x.TASK_LINK_ID,'
||'x.CURRENT_WIP_ENTITY_ID,'
||'x.NEXT_WIP_ENTITY_ID,'
||'x.PRECEDENCE_CONSTRAINT,'
||'x.MIN_SEPARATION,'
||'x.MIN_SEP_TIME_UNIT,'
||'x.MAX_SEPARATION,'
||'x.MAX_SEP_TIME_UNIT,'
||'x.ORGANIZATION_ID, '
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_WO_TASK_HRY_V'||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN2> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
||' UNION '
||' SELECT '
||'x.TASK_LINK_ID,'
||'x.CURRENT_WIP_ENTITY_ID,'
||'x.NEXT_WIP_ENTITY_ID,'
||'x.PRECEDENCE_CONSTRAINT,'
||'x.MIN_SEPARATION,'
||'x.MIN_SEP_TIME_UNIT,'
||'x.MAX_SEPARATION,'
||'x.MAX_SEP_TIME_UNIT,'
||'x.ORGANIZATION_ID, '
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_WO_TASK_HRY_V'||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN3> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_WO_TASK_HIERARCHY'
||'(TASK_LINK_ID,'
||'CURRENT_WIP_ENTITY_ID,'
||'NEXT_WIP_ENTITY_ID,'
||'PRECEDENCE_CONSTRAINT,'
||'MIN_SEPARATION,'
||'MIN_SEP_TIME_UNIT,'
||'MAX_SEPARATION,'
||'MAX_SEP_TIME_UNIT,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' select '
||'x.TASK_LINK_ID,'
||'x.CURRENT_WIP_ENTITY_ID,'
||'x.NEXT_WIP_ENTITY_ID,'
||'x.PRECEDENCE_CONSTRAINT,'
||'x.MIN_SEPARATION,'
||'x.MIN_SEP_TIME_UNIT,'
||'x.MAX_SEPARATION,'
||'x.MAX_SEP_TIME_UNIT,'
||'x.ORGANIZATION_ID, '
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_WO_TASK_HRY_V'||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
|| v_union_sql ;
v_sql_stmt:= ' INSERT INTO MSC_ST_WO_MILESTONES'
||'(VISIT_ID,'
||'MILESTONE,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.VISIT_ID,'
||'x.MILESTONE,'
||'x.ORGANIZATION_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_MILESTONES_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_WO_MILESTONES'
||'(MILESTONE,'
||'MILESTONE_DESC,'
||'VISIT_ID,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'EARLIEST_START_DATE,'
||'TARGET_COMPLETION_DATE,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.MILESTONE,'
||'x.MILESTONE_DESCRIPTION,'
||'x.VISIT_ID,'
||'x.ORGANIZATION_ID,'
||'2,'
||'x.EARLIEST_START_DATE,'
||'x.PLANNED_END_DATE,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_MILESTONES_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_WO_OPERATION_REL'
||'(WORKORDER_ID,'
||'FROM_OP_SEQ_NUM,'
||'TO_OP_SEQ_NUM,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.WORKORDER_ID,'
||'x.FROM_OP_SEQ_NUM,'
||'x.TO_OP_SEQ_NUM,'
||'x.ORGANIZATION_ID,'
||'1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_WO_OPERATIONS_REL_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;
||' SELECT '
||'x.WORKORDER_ID,'
||'x.WIP_ENTITY_ID,'
||'x.FROM_OPERATION_SEQ,'
||'x.TO_OPERATION_SEQ,'
||'x.PRECEDENCE_CONSTRAINT,'
||'x.MIN_SEPARATION,'
||'x.MIN_SEPARATION_TIME_UNIT,'
||'x.MAX_SEPARATION,'
||'x.MAX_SEPARATION_TIME_UNIT,'
||'x.ORGANIZATION_ID,'
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_WO_OPER_RELN_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN2> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_WO_OPERATION_REL'
||'(WORKORDER_ID,'
||'WIP_ENTITY_ID,'
||'FROM_OP_SEQ_NUM,'
||'TO_OP_SEQ_NUM,'
||'PRECEDENCE_CONSTRAINT,'
||'MIN_SEPARATION,'
||'MIN_SEP_TIME_UNIT,'
||'MAX_SEPARATION,'
||'MAX_SEP_TIME_UNIT,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.WORKORDER_ID,'
||'x.WIP_ENTITY_ID,'
||'x.FROM_OPERATION_SEQ,'
||'x.TO_OPERATION_SEQ,'
||'x.PRECEDENCE_CONSTRAINT,'
||'x.MIN_SEPARATION,'
||'x.MIN_SEPARATION_TIME_UNIT,'
||'x.MAX_SEPARATION,'
||'x.MAX_SEPARATION_TIME_UNIT,'
||'x.ORGANIZATION_ID,'
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_WO_OPER_RELN_V'
||MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
|| v_union_sql ;
v_sql_stmt:= ' INSERT INTO MSC_ST_WORK_BREAKDOWN_STRUCT'
||'(ORGANIZATION_ID,'
||'PARAMETER_NAME,'
||'DISPLAY_NAME,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' select '
||'x.ORGANIZATION_ID,'
||'x.LOOKUP_CODE,'
||'x.MEANING,'
||'2,'
||':v_refresh_id,'
||':v_instance_id'
||' from MRP_AP_WORK_BREAKDOWN_V'
||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_WO_SUB_COMP'
||'(ALTERNATE_COMPONENT_ID,'
||'WIP_ENTITY_ID,'
||'OP_SEQ_NUM,'
||'ORGANIZATION_ID,'
||'PLAN_ID,'
||'PRIMARY_COMPONENT_ID,'
||'RANK,'
||'RATIO,'
||'REQUEST_ID,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||'SELECT '
||' x.ALTERNATE_COMPONENT_ID,'
||'x.WIP_ENTITY_ID,'
||'x.OP_SEQ_NUM,'
||'x.ORGANIZATION_ID,'
||'x.PLAN_ID,'
||'x.PRIMARY_COMPONENT_ID,'
||'x.RANK,'
||'x.RATIO,'
||'x.REQUEST_ID,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_WO_SUB_COMP_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
v_sql_stmt:= ' INSERT INTO MSC_ST_SUPPLIES'
||'(DISPOSITION_ID,'
||'OBJECT_TYPE,'
||'SIMULATION_PLAN_ID,'
||'ORDER_TYPE,'
||'MAINTENANCE_OBJECT_SOURCE,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.MAINTENANCE_REQMNT_ID,'
||'x.OBJECT_TYPE,'
||'x.SIMULATION_PLAN_ID,'
||' x.ORDER_TYPE,'
||' x.MAINTENANCE_OBJECT_SOURCE,'
||' 1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_UMP_MAINT_REQMNTS_V'
|| MSC_CL_PULL.V_DBLINK||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
v_sql_stmt:= ' INSERT INTO MSC_ST_DEMANDS'
||'(DISPOSITION_ID,'
||'OBJECT_TYPE,'
||'SCHEDULE_DESIGNATOR_ID,'
||'ORGANIZATION_ID,'
||'INVENTORY_ITEM_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.MAINTENANCE_REQMNT_ID,'
||'x.OBJECT_TYPE,'
||'x.SIMULATION_PLAN_ID,'
||'x.OPERATING_ORG_ID,'
||'x.INVENTORY_ITEM_ID,'
||' 1,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AD_UMP_MATERIAL_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
v_sql_stmt:= ' INSERT INTO MSC_ST_RESOURCE_REQUIREMENTS'
||'(DISPOSITION_ID,'
||'OBJECT_TYPE,'
||'SCHEDULE_DESIGNATOR_ID,'
||'DEPARTMENT_ID,'
||'ORGANIZATION_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.MAINTENANCE_REQMNT_ID,'
||'x.OBJECT_TYPE,'
||'x.SIMULATION_PLAN_ID,'
||'x.OPERATING_ORG_ID,'
||'x.INVENTORY_ITEM_ID,'
||' 1,'
||':v_refresh_id,'
||':v_instance_id'
||' MRP_AD_UMP_RESOURCE_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
v_sql_stmt:= ' INSERT INTO MSC_ST_DESIGNATORS'
||'(DESIGNATOR,'
||'ORGANIZATION_ID,'
||'SRC_DESIGNATOR,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'-23453,'
||'-23453,'
||'x.SIMULATION_PLAN_NAME,'
||' 1,'
||':v_refresh_id,'
||':v_instance_id'
||' MRP_AD_AHL_DESIGNATORS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN> '||MSC_CL_PULL.v_lrn;
v_sql_stmt:= ' INSERT INTO MSC_ST_SUPPLIES '
||'(SCHEDULE_DESIGNATOR_ID,'
||'ORGANIZATION_ID,'
||'NEW_WIP_START_DATE,'
||'NEW_SCHEDULE_DATE,'
-- ||'MAINTENANCE_TYPE_CODE,'
||'to_be_exploded,'
||'ORDER_NUMBER,'
||'INVENTORY_ITEM_ID,'
||'ORDER_TYPE,'
||'COLL_ORDER_TYPE,'
||'MAINTENANCE_OBJECT_SOURCE,'
||'NEW_ORDER_QUANTITY,'
||'FIRM_PLANNED_TYPE,'
||'DELETED_FLAG,'
||'PRODUCT_CLASSIFICATION,'
||'OPERATING_FLEET ,'
||'MAINTENANCE_REQUIREMENT,'
||'SOURCE_ITEM_ID,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||' x.SIMULATION_PLAN_ID,'
||' x.OPERATING_ORG_ID,'
||' x.DUE_DATE,'
||' LAST_DAY(x.DUE_DATE),'
-- ||' x.MAINTENANCE_TYPE_CODE,'
||' 2,'
||' x.TITLE,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORDER_TYPE,'
||' 92,'
||' x.MAINTENANCE_OBJECT_SOURCE,'
||' x.QUANTITY,'
||' 1,'
||' 2,'
||'x.PRODUCT_CLASSIFICATION,'
||'x.FLEET_HEADER_ID ,'
||'x.MAINTENANCE_REQUIREMENT,'
||' x.INVENTORY_ITEM_ID,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_UMP_MAINT_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.OPERATING_ORG_ID '|| MSC_UTIL.v_in_org_str
||' AND x.DUE_DATE >= :p_eam_cmro_st_date '
||' AND x.DUE_DATE <= :p_eam_cmro_end_date '
-- ||' AND x.RN > '||MSC_CL_PULL.v_lrn
;
v_sql_stmt:= ' INSERT INTO MSC_ST_DEMANDS '
||'(SCHEDULE_DESIGNATOR_ID,'
||'ORGANIZATION_ID,'
||'INVENTORY_ITEM_ID,'
||'USING_REQUIREMENT_QUANTITY,'
||'USING_ASSEMBLY_DEMAND_DATE, '
||'DEMAND_TYPE,'
||'ORIGINATION_TYPE,'
||'DELETED_FLAG,'
||'OPERATING_FLEET,'
||'MAINTENANCE_REQUIREMENT,'
||'MAINTENANCE_OBJECT_SOURCE,'
||'USING_ASSEMBLY_ITEM_ID,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.SIMULATION_PLAN_ID,'
||'x.OPERATING_ORG_ID,'
||'x.COMPONENT,'
||'x.QUANTITY,'
||'x.USING_ASSEMBLY_DEMAND_DATE,'
||'x.DEMAND_TYPE,'
||'x.ORIGINATION_TYPE,'
||' 2,'
||'x.FLEET_HEADER_ID,'
||'x.TITLE,'--mnagilla
||'x.MAINTENANCE_OBJECT_SOURCE,'
||'x.USING_ASSEMBLY_ITEM_ID,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_UMP_MATERIAL_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.OPERATING_ORG_ID '||MSC_UTIL.v_in_org_str
||' AND x.USING_ASSEMBLY_DEMAND_DATE >= :p_eam_cmro_st_date '
||' AND x.USING_ASSEMBLY_DEMAND_DATE <= :p_eam_cmro_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,'
||'OPERATION_HOURS_REQUIRED,'
||'TOUCH_TIME,'
||'UNADJUSTED_RESOURCE_HOURS,'
||'DELETED_FLAG,'
||'INVENTORY_ITEM_ID,'
||'OPERATING_FLEET,'
||'MAINTENANCE_REQUIREMENT,'--mnagilla
||'MAINTENANCE_OBJECT_SOURCE,'
||'SUPPLY_TYPE,'
||'START_DATE,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||' x.SIMULATION_PLAN_ID,'
||' x.DEPARTMENT_ID,'
||' x.OPERATING_ORG_ID,'
||' x.BOM_RESOURCE_ID,'
||' x.ASSIGNED_UNITS ,'
|| v_temp_sql
||' x.TOTAL_QUANTITY ,'
||' 2,'
||'x.INVENTORY_ITEM_ID,'
||'x.FLEET_HEADER_ID,'
||'x.TITLE,'--mnagilla
||'x.MAINTENANCE_OBJECT_SOURCE,'
||'x.SUPPLY_TYPE,'
||'x.due_date,'
||':v_refresh_id,'
||':v_instance_id'
||' FROM MRP_AP_UMP_RESOURCE_REQMNTS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.OPERATING_ORG_ID '||MSC_UTIL.v_in_org_str
||' AND x.DUE_DATE >= :p_eam_cmro_st_date '
||' AND x.DUE_DATE <= :p_eam_cmro_end_date '
|| v_temp_whr
;
v_sql_stmt:= ' INSERT INTO MSC_ST_DESIGNATORS( '
-- ||'(DESIGNATOR_ID, '
||' DESIGNATOR, '
||'DESIGNATOR_TYPE, '
||'MPS_RELIEF,'
||'INVENTORY_ATP_FLAG, '
||'SRC_SIM_FCST_ID, '
||'SRC_DESCRIPTION, '
||'SRC_DESIGNATOR, '
||'ORGANIZATION_ID, '
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID) '
||'SELECT '
-- ||'SIMULATION_PLAN_ID, '
||'-23453, '
||'DESIGNATOR_TYPE, '
||'MPS_RELIEF, '
||'INVENTORY_ATP_FLAG, '
||'SRC_SIM_FCST_ID, '
||'DESCRIPTION, '
||'SIMULATION_PLAN_NAME,'
||'-23453, '
||' 2,'
||':v_refresh_id,'
||':v_instance_id '
||' FROM '
||' MRP_AP_AHL_DESIGNATORS_V'||MSC_CL_PULL.v_dblink||' x';
v_sql_stmt:= ' INSERT INTO MSC_ST_RESERVATIONS'
||' ( TRANSACTION_ID,'
||' ORGANIZATION_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.DEMAND_ID,'
||' x.ORGANIZATION_ID,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AD_HARD_RESERVATIONS_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;
||' SELECT '
||'x.RESERVATION_ID * 2,'
||'x.DEMAND_SOURCE_HEADER_ID,'
||'x.DEMAND_SOURCE_LINE_ID,'
||'x.DISPOSITION_TYPE,'
||'x.ORGANIZATION_ID,'
||'x.INVENTORY_ITEM_ID,'
||'x.RESERVATION_TYPE,'
||'x.REQUIREMENT_DATE,'
||'x.RESERVATION_QUANTITY,'
||'x.SUBINVENTORY_ID,'
||'x.TASK_ID,'
||'x.PROJECT_ID,'
||'x.SUPPLY_SOURCE_TYPE_ID,'
||'x.SUPPLY_SOURCE_HEADER_ID,'
||'2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AP_CMRO_RESERVATIONS_V'
||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str
||' AND x.RN2> '||MSC_CL_PULL.v_lrn;
v_sql_stmt:= ' INSERT INTO MSC_ST_RESERVATIONS'
||'(TRANSACTION_ID,'
||'DISPOSITION_ID,'
||'DEMAND_SOURCE_LINE_ID,'
||'DISPOSITION_TYPE,'
||'ORGANIZATION_ID,'
||'INVENTORY_ITEM_ID,'
||'RESERVATION_TYPE,'
||'REQUIREMENT_DATE,'
||'RESERVED_QUANTITY,'
||'SUBINVENTORY,'
||'TASK_ID,'
||'PROJECT_ID,'
||'SUPPLY_SOURCE_TYPE_ID,'
||'SUPPLY_SOURCE_HEADER_ID,'
||'DELETED_FLAG,'
||'REFRESH_ID,'
||'SR_INSTANCE_ID)'
||' SELECT '
||'x.RESERVATION_ID * 2,'
||'x.DEMAND_SOURCE_HEADER_ID,'
||'x.DEMAND_SOURCE_LINE_ID,'
||'x.DISPOSITION_TYPE,'
||'x.ORGANIZATION_ID,'
||'x.INVENTORY_ITEM_ID,'
||'x.RESERVATION_TYPE,'
||'x.REQUIREMENT_DATE,'
||'x.RESERVATION_QUANTITY,'
||'x.SUBINVENTORY_ID,'
||'x.TASK_ID,'
||'x.PROJECT_ID,'
||'x.SUPPLY_SOURCE_TYPE_ID,'
||'x.SUPPLY_SOURCE_HEADER_ID,'
||'2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AP_CMRO_RESERVATIONS_V'
||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str
||v_union_sql;
' insert into MSC_ST_SUPPLIES'
||' ( DISPOSITION_ID,'
||' ORDER_TYPE,'
||' ORGANIZATION_ID,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.WIP_ENTITY_ID,'
||' x.ORDER_TYPE,'
||' x.ORGANIZATION_ID,'
||' x.MAINTENANCE_OBJECT_SOURCE,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_CMRO_CLOSED_JOBS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN> :v_lrn '
||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
||' select'
||' x.Inventory_Item_ID,'
||' x.asset_item_id,'
||' x.visit_id,'
||' x.ORGANIZATION_ID,'
||' x.WIP_ENTITY_ID,'
||' x.WIP_ENTITY_NAME, '
||' x.ACTUAL_END_DATE,'
||' x.quantity_scrapped,'
||' x.quantity_completed,'
||' x.FIRM_PLANNED_STATUS_TYPE,'
||' x.START_DATE- :v_dgmt,'
||' x.ORDER_TYPE,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.SCHEDULE_GROUP_ID,'
||' x.BUILD_SEQUENCE,'
||' x.LINE_ID,'
||' x.ALTERNATE_BOM_DESIGNATOR,'
||' x.ALTERNATE_ROUTING_DESIGNATOR,'
||' x.END_ITEM_UNIT_NUMBER,'
||' x.STATUS_CODE,'
||' x.DEMAND_CLASS,'
||' 2,'
||' x.coproducts_supply,'
||' x.requested_start_date,'
||' x.requested_completion_date,'
||' x.schedule_priority,'
||' x.ACTUAL_START_DATE,'
||' x.cfm_routing_flag, '
||' x.wip_start_quantity,'
||' x.maintenance_object_source,'
||' x.description,'
||' x.maintenance_object_type,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_CMRO_CLOSED_JOBS_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND x.RN2 > :v_lrn ';
'insert into MSC_ST_SUPPLIES'
||' ( INVENTORY_ITEM_ID,'
||' asset_item_id,'
||' visit_id,'
||' ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' NEW_SCHEDULE_DATE,'
||' QTY_SCRAPPED,'
||' QTY_COMPLETED,'
||' FIRM_PLANNED_TYPE,'
||' NEW_WIP_START_DATE,'
||' ORDER_TYPE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' SCHEDULE_GROUP_ID,'
||' BUILD_SEQUENCE,'
||' LINE_ID,'
||' ALTERNATE_BOM_DESIGNATOR,'
||' ALTERNATE_ROUTING_DESIGNATOR,'
||' UNIT_NUMBER,'
||' WIP_STATUS_CODE,'
||' DEMAND_CLASS,'
||' DELETED_FLAG,'
||' COPRODUCTS_SUPPLY,'
||' REQUESTED_START_DATE,'
||' REQUESTED_COMPLETION_DATE,'
||' SCHEDULE_PRIORITY,'
||' ACTUAL_START_DATE,'
||' CFM_ROUTING_FLAG,'
||' WIP_START_QUANTITY,'
||' MAINTENANCE_OBJECT_SOURCE,'
||' DESCRIPTION,'
||' MAINTENANCE_OBJECT_TYPE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.Inventory_Item_ID,'
||' x.asset_item_id,'
||' x.visit_id,'
||' x.ORGANIZATION_ID,'
||' x.WIP_ENTITY_ID,'
||' x.WIP_ENTITY_NAME, '
||' x.ACTUAL_END_DATE,'
||' x.quantity_scrapped,'
||' x.quantity_completed,'
||' x.FIRM_PLANNED_STATUS_TYPE,'
||' x.START_DATE- :v_dgmt,'
||' x.ORDER_TYPE,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.SCHEDULE_GROUP_ID,'
||' x.BUILD_SEQUENCE,'
||' x.LINE_ID,'
||' x.ALTERNATE_BOM_DESIGNATOR,'
||' x.ALTERNATE_ROUTING_DESIGNATOR,'
||' x.END_ITEM_UNIT_NUMBER,'
||' x.STATUS_CODE,'
||' x.DEMAND_CLASS,'
||' 2,'
||' x.coproducts_supply,'
||' x.requested_start_date,'
||' x.requested_completion_date,'
||' x.schedule_priority,'
||' x.ACTUAL_START_DATE,'
||' x.cfm_routing_flag, '
||' x.wip_start_quantity,'
||' x.maintenance_object_source,'
||' x.description,'
||' x.maintenance_object_type,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_CMRO_CLOSED_JOBS_V '||MSC_CL_PULL.v_dblink||' x'
||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
'insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ORDER_TYPE,'
||' COLL_ORDER_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' FIRM_PLANNED_TYPE,'
||' ORDER_NUMBER,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' PRODUCES_TO_STOCK,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT '
||' x.LINE_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.ORDER_TYPE,'
||' x.COLL_ORDER_TYPE,'
||' x.NEED_BY_DATE,'
||' x.QUANTITY,'
||' 1,'
||' x.ORDER_NUMBER,'
||' x.VENDOR_ID,'
||' x.VENDOR_SITE_ID,'
||' x.PRODUCES_TO_STOCK,'
||' :v_item_type_id,'
||' :v_item_type_good,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AP_OSP_SUP_V '||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str ;