The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_temp_sql1 := ' AND ((x.LAST_UPDATE_DATE > :g_last_succ_rio_time) OR (x.item_rn > '||MSC_CL_PULL.v_lrnn ||'))';
v_sql_stmt := ' INSERT INTO MSC_ST_SUPPLIES '
||' ( DISPOSITION_ID, '
||' ORDER_TYPE, '
||' ORDER_NUMBER, '
||' DELETED_FLAG,'
||' INVENTORY_ITEM_ID, '
||' ORGANIZATION_ID, '
||' PROMISED_DATE, '
||' NEW_ORDER_QUANTITY, '
||' UOM_CODE, '
||' CUSTOMER_PRODUCT_ID, '
||' SR_REPAIR_TYPE_ID, '
||' PROJECT_ID, '
||' TASK_ID, '
||' RO_STATUS_CODE, '
||' ASSET_SERIAL_NUMBER, '
||' REVISION, '
||' SR_REPAIR_GROUP_ID, '
||' SCHEDULE_PRIORITY, '
||' NEW_SCHEDULE_DATE,'
||' RO_CREATION_DATE,'
||' REPAIR_LEAD_TIME,'
||' FIRM_PLANNED_TYPE,'
||' ITEM_TYPE_ID, '
||' ITEM_TYPE_VALUE,'
||' REFRESH_ID, '
||' SR_INSTANCE_ID )'
||' select '
||' x.REPAIR_LINE_ID, '
||' 75, ' -- new order type for repair order supply
||' x.REPAIR_NUMBER, '
||' decode(x.RO_STATUS_CODE,''C'' ,1,2), '
||' x.INVENTORY_ITEM_ID, '
||' x.ORGANIZATION_ID, '
||' x.PROMISE_DATE, '
||' x.START_QUANTITY, '
||' x.UNIT_OF_MEASURE, '
||' x.CUSTOMER_PRODUCT_ID, '
||' x.SR_REPAIR_TYPE_ID, '
||' x.PROJECT_ID, '
||' x.TASK_ID, '
||' x.RO_STATUS_CODE, '
||' x.SERIAL_NUMBER, '
||' x.REVISION, '
||' x.SR_REPAIR_GROUP_ID, '
||' x.SCHEDULE_PRIORITY, '
||' x.PROMISE_DATE,'
||' x.CREATION_DATE,'
||' x.REPAIR_LEADTIME,'
||' 2,'
||' :v_item_type_id ,'
||' :v_item_type_good ,'
||' :v_refresh_id ,'
||' :v_instance_id'
||' from MRP_AP_REPAIR_ORDERS_V'||MSC_CL_PULL.v_dblink ||' x'
||' where x.organization_id '||MSC_UTIL.v_depot_org_str
|| v_temp_sql1
;
v_sql_stmt := ' INSERT INTO MSC_ST_DEMANDS '
||' ( '
||' repair_line_id, '
||' using_assembly_item_id, '
||' organization_id, '
||' using_assembly_demand_date, '
||' wip_entity_id ,'
||' inventory_item_id, '
||' USING_REQUIREMENT_QUANTITY, '
||' QUANTITY_ISSUED, '
||' DEMAND_TYPE, '
||' PROJECT_ID, '
||' TASK_ID, '
||' DEMAND_CLASS, '
||' ORIGINATION_TYPE, '
||' DELETED_FLAG, '
||' quantity_per_assembly, '
||' component_scaling_type, '
||' component_yield_factor, '
||' operation_seq_num, '
||' ITEM_TYPE_ID, '
||' ITEM_TYPE_VALUE,'
||' refresh_id ,'
||' sr_instance_id '
||' ) '
||' select '
||' repair_line_id, '
||' ro_inventory_item_id, '
||' repair_org_id, '
||' using_assembly_demand_date, '
||' wip_entity_id , '
||' inventory_item_id, '
||' new_required_quantity, '
||' quantity_issued, '
||' demand_type, '
||' task_id, '
||' planning_group, '
||' demand_class, '
||' origination_type, '
||' decode(x.RO_STATUS_CODE,''C'' ,1,2), '
||' quantity_per_assembly, '
||' basis_type, '
||' component_yield_factor, '
||' operation_seq_num, '
||' :v_item_type_id, '
||' :v_item_type_good,'
||' :v_refresh_id ,'
||' :v_instance_id'
|| ' from MRP_AP_REPAIR_DEMAND_V'|| MSC_CL_PULL.v_dblink ||' x'
|| ' where x.organization_id '||MSC_UTIL.v_depot_org_str
|| v_temp_sql1
;
'insert into MSC_ST_DEMANDS'
||' ( REPAIR_LINE_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' WIP_ENTITY_ID,'
||' DELETED_FLAG,'
||' ORIGINATION_TYPE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.REPAIR_LINE_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.WIP_ENTITY_ID,'
||' 1,'
||' 77,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_RO_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
||' Where x. date1 > :g_last_succ_iro_ref_time or x.date2 > :g_last_succ_iro_ref_time
or x.RN1 > ' ||MSC_CL_PULL.v_lrnn ;
'insert into MSC_ST_SUPPLIES'
||' ( DISPOSITION_ID,'
||' ORDER_TYPE,'
||' ORGANIZATION_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.WIP_ENTITY_ID,'
||' 86,'
||' x.organization_id,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_ERO_WIP_JOB_SUPP_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.RN> :v_lrn '
||' AND organization_id '||MSC_UTIL.v_non_depot_org_str;
select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
where instance_id = MSC_CL_PULL.v_instance_id ;
'insert into MSC_ST_SUPPLIES'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' NEW_SCHEDULE_DATE,'
||' EXPECTED_SCRAP_QTY,'
||' QTY_SCRAPPED,'
||' QTY_COMPLETED,'
||' FIRM_PLANNED_TYPE,'
||' NEW_WIP_START_DATE,'
||' REVISION,'
||' ORDER_TYPE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' SCHEDULE_GROUP_ID,'
||' BUILD_SEQUENCE,'
||' LINE_ID,'
||' ALTERNATE_BOM_DESIGNATOR,'
||' ALTERNATE_ROUTING_DESIGNATOR,'
||' UNIT_NUMBER,'
||' WIP_STATUS_CODE,'
||' SCHEDULE_GROUP_NAME,'
||' DEMAND_CLASS,'
||' DELETED_FLAG,'
||' ROUTING_SEQUENCE_ID,'
||' BILL_SEQUENCE_ID,'
||' COPRODUCTS_SUPPLY,'
||' OPERATION_SEQ_NUM,'
||' JUMP_OP_SEQ_NUM,'
||' JOB_OP_SEQ_NUM,'
||' REQUESTED_START_DATE,'
||' REQUESTED_COMPLETION_DATE,'
||' SCHEDULE_PRIORITY,'
||' ASSET_ITEM_ID,'
||' ASSET_SERIAL_NUMBER,'
||' ACTUAL_START_DATE,'
||' CFM_ROUTING_FLAG,'
||' WIP_START_QUANTITY,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.WIP_ENTITY_ID,'
||' x.WIP_ENTITY_NAME, '
||' x.NEW_ORDER_QUANTITY ,'
||' DECODE( x.wip_job_type,'
||' 1, DECODE( :v_mps_consume_profile_value,'
||' 1, x.mps_scheduled_completion_date,'
||' x.scheduled_completion_date),'
||' x.scheduled_completion_date)- :v_dgmt,'
||' DECODE( x.wip_job_type,'
||' 1, DECODE( :v_mps_consume_profile_value,'
||' 1, x.mps_expected_scrap_quantity,'
||' x.expected_scrap_quantity),'
||' x.expected_scrap_quantity),'
||' x.quantity_scrapped,'
||' x.quantity_completed,'
||' x.FIRM_PLANNED_STATUS_TYPE,'
||' x.START_DATE- :v_dgmt,'
||' x.REVISION,'
||' 86,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' 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.SCHEDULE_GROUP_NAME,'
||' x.DEMAND_CLASS,'
||' 2,'
||' x.routing_reference_id,x.bom_reference_id,x.coproducts_supply,x.jd_operation_seq_num,'
||' x.JUMP_OP_SEQ_NUM,x.JOB_OP_SEQ_NUM, '
||' x.requested_start_date,x.requested_completion_date,x.schedule_priority,x.asset_item_id,x.asset_serial_number,'
||' x.ACTUAL_START_DATE,x.cfm_routing_flag, '
||' x.wip_start_quantity,'
||' :v_item_type_id,'
||' :v_item_type_good,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_ERO_WIP_JOB_SUPPLY_V'||MSC_CL_PULL.v_dblink ||' x'
||' where x.organization_id '||MSC_UTIL.v_non_depot_org_str
|| ' AND (x.RN1 > :v_lrn or x.RN2 > :v_lrn or x.RN3 >:v_lrn)'
;
'insert into MSC_ST_DEMANDS'
||' ( WIP_ENTITY_ID,'
||' OPERATION_SEQ_NUM,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ORIGINATION_TYPE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.WIP_ID,'
||' x.OPERATION_SEQ_NUM,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.ORIGINATION_TYPE,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_ERO_WIP_COMP_DEM_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.RN> :v_lrn '
||' AND organization_id '||MSC_UTIL.v_non_depot_org_str;
'insert into MSC_ST_DEMANDS'
||' ( INVENTORY_ITEM_ID,'
||' SOURCE_INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' WIP_ENTITY_ID,'
||' SOURCE_WIP_ENTITY_ID,'
||' ORDER_NUMBER,' -- changes
||' WIP_STATUS_CODE,'
||' WIP_SUPPLY_TYPE,'
||' OPERATION_SEQ_NUM,'
||' USING_REQUIREMENT_QUANTITY,'
||' QUANTITY_ISSUED,'
||' USING_ASSEMBLY_ITEM_ID,'
||' DEMAND_TYPE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' END_ITEM_UNIT_NUMBER,'
||' DEMAND_CLASS,'
||' ORIGINATION_TYPE,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' MPS_DATE_REQUIRED,'
||' DELETED_FLAG,'
||' QUANTITY_PER_ASSEMBLY,'
||' ASSET_ITEM_ID,'
||' ASSET_SERIAL_NUMBER,'
||' COMPONENT_SCALING_TYPE,'
||' COMPONENT_YIELD_FACTOR,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.WIP_ENTITY_ID,'
||' x.WIP_ENTITY_ID,'
||' x.WIP_ENTITY_NAME, '
||' x.STATUS_CODE,'
||' x.WIP_SUPPLY_TYPE,'
||' x.COPY_OP_SEQ_NUM,'
||' x.NEW_REQUIRED_QUANTITY,'
||' x.QUANTITY_ISSUED,'
||' x.JOB_REFERENCE_ITEM_ID,'
||' x.DEMAND_TYPE,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
||' x.DEMAND_CLASS,'
||' 77,'
||' x.DATE_REQUIRED- :v_dgmt,'
||' x.MPS_DATE_REQUIRED- :v_dgmt,'
||' 2,'
||' x.quantity_per_assembly,x.asset_item_id,x.asset_serial_number,x.basis_type,x.component_yield_factor, '
||' :v_item_type_id,'
||' :v_item_type_good,'
||' :v_refresh_id,'
||' :v_instance_id'
|| ' from MRP_AP_ERO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink ||' x'
|| ' where x.organization_id '||MSC_UTIL.v_non_depot_org_str
|| ' AND (x.RN1 > :v_lrn or x.RN2 > :v_lrn or x.RN3 >:v_lrn)'
;