[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'insert into MSC_ST_DESIGNATORS'
||' ( DESIGNATOR,'
||' FORECAST_SET,'
||' ORGANIZATION_ID,'
||' MPS_RELIEF,'
||' INVENTORY_ATP_FLAG,'
||' DESCRIPTION,'
||' DISABLE_DATE,'
||' DEMAND_CLASS,'
||' CONSUME_FORECAST,'
||' UPDATE_TYPE,'
||' FORWARD_UPDATE_TIME_FENCE,'
||' BACKWARD_UPDATE_TIME_FENCE,'
||' OUTLIER_UPDATE_PERCENTAGE,'
||' CUSTOMER_ID,'
||' SHIP_ID,'
||' BILL_ID,'
||' BUCKET_TYPE,'
||' DESIGNATOR_TYPE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select '
||' x.FORECAST_DESIGNATOR,'
||' x.FORECAST_SET,'
||' x.ORGANIZATION_ID,'
||' x.MPS_RELIEF,'
||' x.INVENTORY_ATP_FLAG,'
||' x.DESCRIPTION,'
||' x.DISABLE_DATE,'
||' x.DEMAND_CLASS,'
||' x.CONSUME_FORECAST,'
||' x.UPDATE_TYPE,'
||' x.FOREWARD_UPDATE_TIME_FENCE,'
||' x.BACKWARD_UPDATE_TIME_FENCE,'
||' x.OUTLIER_UPDATE_PERCENTAGE,'
||' x.CUSTOMER_ID,'
||' x.SHIP_ID,'
||' x.BILL_ID,'
||' x.BUCKET_TYPE,'
||' x.DESIGNATOR_TYPE,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
'insert into MSC_ST_DESIGNATORS'
||' ( DESIGNATOR,'
||' FORECAST_SET,'
||' ORGANIZATION_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select '
||' x.FORECAST_DESIGNATOR,'
||' x.FORECAST_SET,'
||' x.ORGANIZATION_ID,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND x.RN> '||MSC_CL_PULL.v_lrn
||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
'insert into MSC_ST_DEMANDS'
||' ( INVENTORY_ITEM_ID,'
||' SALES_ORDER_LINE_ID,'
||' ORIGINATION_TYPE,'
||' ORGANIZATION_ID,'
||' FORECAST_DESIGNATOR,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select '
||' x.INVENTORY_ITEM_ID,'
||' x.TRANSACTION_ID,'
||' 29,'
||' x.ORGANIZATION_ID,'
||' x.forecast_designator,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
||' AND x.RN>'||MSC_CL_PULL.v_lrn;
'insert into MSC_ST_DEMANDS'
||' ( INVENTORY_ITEM_ID,'
||' SALES_ORDER_LINE_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' DEMAND_CLASS,'
||' ORDER_PRIORITY,'
||' FORECAST_MAD,'
||' CONFIDENCE_PERCENTAGE,'
||' BUCKET_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' CUSTOMER_ID,'
||' FORECAST_DESIGNATOR,'
||' DELETED_FLAG,'
||' ORIGINATION_TYPE,'
||' DEMAND_TYPE,'
||' REFRESH_ID,'
||' PLANNING_GROUP,'
||' SR_INSTANCE_ID,'
||' SOURCE_SALES_ORDER_LINE_ID)'
||' select '
||' x.INVENTORY_ITEM_ID,'
||' x.TRANSACTION_ID,'
||' x.ORGANIZATION_ID,'
||' x.USING_ASSEMBLY_ID,'
||' x.FORECAST_DATE,'
||' x.RATE_END_DATE,'
||' x.ORIGINAL_FORECAST_QUANTITY,'
||' x.DEMAND_CLASS,'
-- for bug13562886 ,there are some junk date time value in the AttributeX column ,so use MSC_UTIL.MSC_NUMVAL to filter those date time value.
||' MSC_UTIL.MSC_NUMVAL(DECODE( :v_msc_fcst_priority_flex_num,'
||' 1, x.Attribute1,'
||' 2, x.Attribute2,'
||' 3, x.Attribute3,'
||' 4, x.Attribute4,'
||' 5, x.Attribute5,'
||' 6, x.Attribute6,'
||' 7, x.Attribute7,'
||' 8, x.Attribute8,'
||' 9, x.Attribute9,'
||' 10, x.Attribute10,'
||' 11, x.Attribute11,'
||' 12, x.Attribute12,'
||' 13, x.Attribute13,'
||' 14, x.Attribute14,'
||' 15, x.Attribute15,NULL)),'
||' x.FORECAST_MAD,'
||' x.CONFIDENCE_PERCENTAGE,'
||' x.BUCKET_TYPE,'
||' x.SOURCE_ORGANIZATION_ID,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.CUSTOMER_ID,'
||' x.FORECAST_DESIGNATOR,'
||' 2,'
||' x.origination_type,'
||' x.demand_type,'
||' :v_refresh_id,'
||' x.PLANNING_GROUP,'
||' :v_instance_id,'
||' x.TRANSACTION_ID '
||' from MRP_AP_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
'insert into MSC_ST_DEMANDS'
||' ( DISPOSITION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' ORIGINATION_TYPE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.DISPOSITION_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.USING_ASSEMBLY_ID,'
||' x.ORIGINATION_TYPE,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_MDS_DEMANDS_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.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.USING_ASSEMBLY_ID,'
||' x.SCHEDULE_WORKDATE- :v_dgmt,'
||' x.USING_REQUIREMENTS_QUANTITY,'
||' x.ASSEMBLY_DEMAND_COMP_DATE -:v_dgmt,'
||' x.DEMAND_TYPE,'
||' x.DAILY_DEMAND_RATE,'
||' x.ORIGINATION_TYPE,'
||' x.SOURCE_ORGANIZATION_ID,'
||' x.DISPOSITION_ID,'
||' x.DISPOSITION_ID,'
||' x.RESERVATION_ID,'
||' x.DEMAND_SCHEDULE_NAME,'
||' x.SALES_ORDER_NUMBER,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
||' x.SCHEDULE_DATE- :v_dgmt,'
||' x.LIST_PRICE,'
|| v_temp_sql1
||' x.REQUEST_DATE,'
||' TO_NUMBER(NVL(DECODE(:v_msc_dmd_priority_flex_num,'
||' 1, x.Attribute21,'
||' 2, x.Attribute22,'
||' 3, x.Attribute23,'
||' 4, x.Attribute24,'
||' 5, x.Attribute25,'
||' 6, x.Attribute26,'
||' 7, x.Attribute27,'
||' 8, x.Attribute28,'
||' 9, x.Attribute29,'
||' 10, x.Attribute30,'
||' 11, x.Attribute31,'
||' 12, x.Attribute32,'
||' 13, x.Attribute33,'
||' 14, x.Attribute34,'
||' 15, x.Attribute35),'
||' DECODE(x.Schedule_Origination_Type,'
||' 2, DECODE(:v_msc_fcst_priority_flex_num,'
||' 1, x.Attribute1,'
||' 2, x.Attribute2,'
||' 3, x.Attribute3,'
||' 4, x.Attribute4,'
||' 5, x.Attribute5,'
||' 6, x.Attribute6,'
||' 7, x.Attribute7,'
||' 8, x.Attribute8,'
||' 9, x.Attribute9,'
||' 10, x.Attribute10,'
||' 11, x.Attribute11,'
||' 12, x.Attribute12,'
||' 13, x.Attribute13,'
||' 14, x.Attribute14,'
||' 15, x.Attribute15),'
||' 3, x.SALES_ORDER_PRIORITY))),'
||' x.SALES_ORDER_LINE_ID,'
||' x.CUSTOMER_ID,'
||' x.SHIP_TO_SITE_ID,'
||' 2,'
|| v_temp_sql
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'; --NCP: changed to RN3
'insert into MSC_ST_DEMANDS'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' USING_REQUIREMENT_QUANTITY,'
||' ASSEMBLY_DEMAND_COMP_DATE,'
||' DEMAND_TYPE,'
||' DAILY_DEMAND_RATE,'
||' ORIGINATION_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' DISPOSITION_ID,'
||' SOURCE_DISPOSITION_ID,'
||' RESERVATION_ID,'
||' DEMAND_SCHEDULE_NAME,'
||' ORDER_NUMBER,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' END_ITEM_UNIT_NUMBER,'
||' SCHEDULE_DATE,'
||' SELLING_PRICE,'
||' DMD_LATENESS_COST,'
||' REQUEST_DATE,'
||' ORDER_PRIORITY,'
||' SALES_ORDER_LINE_ID,'
||' CUSTOMER_ID,'
||' SHIP_TO_SITE_ID,'
||' DELETED_FLAG,'
||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
||' ORIGINAL_SYSTEM_REFERENCE,'
||' DEMAND_SOURCE_TYPE,'
||' DEMAND_CLASS,'
||' PROMISE_DATE,'
||' LINK_TO_LINE_ID,'
||' ORDER_DATE_TYPE_CODE,'
||' SCHEDULE_ARRIVAL_DATE,'
||' LATEST_ACCEPTABLE_DATE,'
||' SHIPPING_METHOD_CODE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.USING_ASSEMBLY_ID,'
||' x.SCHEDULE_WORKDATE- :v_dgmt,'
||' x.USING_REQUIREMENTS_QUANTITY,'
||' x.ASSEMBLY_DEMAND_COMP_DATE -:v_dgmt,'
||' x.DEMAND_TYPE,'
||' x.DAILY_DEMAND_RATE,'
||' x.ORIGINATION_TYPE,'
||' x.SOURCE_ORGANIZATION_ID,'
||' x.DISPOSITION_ID,'
||' x.DISPOSITION_ID,'
||' x.RESERVATION_ID,'
||' x.DEMAND_SCHEDULE_NAME,'
||' x.SALES_ORDER_NUMBER,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
||' x.SCHEDULE_DATE- :v_dgmt,'
||' x.LIST_PRICE,'
|| v_temp_sql1
||' x.REQUEST_DATE,'
||' TO_NUMBER(NVL(DECODE(:v_msc_dmd_priority_flex_num,'
||' 1, x.Attribute21,'
||' 2, x.Attribute22,'
||' 3, x.Attribute23,'
||' 4, x.Attribute24,'
||' 5, x.Attribute25,'
||' 6, x.Attribute26,'
||' 7, x.Attribute27,'
||' 8, x.Attribute28,'
||' 9, x.Attribute29,'
||' 10, x.Attribute30,'
||' 11, x.Attribute31,'
||' 12, x.Attribute32,'
||' 13, x.Attribute33,'
||' 14, x.Attribute34,'
||' 15, x.Attribute35),'
||' DECODE(x.Schedule_Origination_Type,'
||' 2, DECODE(:v_msc_fcst_priority_flex_num,'
||' 1, x.Attribute1,'
||' 2, x.Attribute2,'
||' 3, x.Attribute3,'
||' 4, x.Attribute4,'
||' 5, x.Attribute5,'
||' 6, x.Attribute6,'
||' 7, x.Attribute7,'
||' 8, x.Attribute8,'
||' 9, x.Attribute9,'
||' 10, x.Attribute10,'
||' 11, x.Attribute11,'
||' 12, x.Attribute12,'
||' 13, x.Attribute13,'
||' 14, x.Attribute14,'
||' 15, x.Attribute15),'
||' 3, x.SALES_ORDER_PRIORITY))),'
||' x.SALES_ORDER_LINE_ID,'
||' x.CUSTOMER_ID,'
||' x.SHIP_TO_SITE_ID,'
||' 2,'
|| v_temp_sql
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
|| v_union_sql ;
v_select_sql varchar2(100);
select delete_ods_data,sales_order
into lv_complete_ref,lv_so_param
from msc_coll_parameters
where instance_id= MSC_CL_PULL.v_instance_id;
'insert into MSC_ST_SALES_ORDERS'
||' ( DEMAND_ID,'
||' ROW_TYPE,'
||' PARENT_DEMAND_ID,'
||' DELETED_FLAG,'
||' RESERVATION_TYPE,'
||' CTO_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.DEMAND_ID,'
||' x.ROW_TYPE,' --row type
||' x.PARENT_DEMAND_ID,'
||' 1,'
||' 2,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.RN> :v_so_sr_lrn '
|| lv_temp_sql;
'insert into MSC_ST_SALES_ORDERS'
||' ( DEMAND_ID,'
||' ROW_TYPE,'
||' PARENT_DEMAND_ID,'
||' DELETED_FLAG,'
||' RESERVATION_TYPE,'
||' CTO_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.DEMAND_ID,'
||' x.ROW_TYPE,'
||' x.PARENT_DEMAND_ID,'
||' 1,'
||' 1,'
|| v_temp_sql
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN> :v_so_lrn '
|| lv_temp_sql;
||' SELECT /*+ first_rows leading(x.msik) use_nl(x.msik x.ool) */ '
||' x.INVENTORY_ITEM_ID,'
||' x.DESTINATION_ORGANIZATION_ID,'/*8915213*/
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.PRIMARY_UOM_QUANTITY,'
||' x.RESERVATION_TYPE,'
||' x.RESERVATION_QUANTITY,'
||' x.DEMAND_SOURCE_TYPE,'
||' x.DEMAND_SOURCE_HEADER_ID,'
||' x.COMPLETED_QUANTITY,'
||' x.SUBINVENTORY,'
||' x.DEMAND_CLASS,'
||' x.REQUIREMENT_DATE,'
||' x.DEMAND_SOURCE_LINE,'
||' x.DEMAND_SOURCE_LINE,'
||' x.DEMAND_SOURCE_DELIVERY,'
||' x.DEMAND_SOURCE_NAME,'
||' x.DEMAND_ID,'
||' x.ROW_TYPE,'
||' x.DEMAND_ID,'
||' x.PARENT_DEMAND_ID,'
||' x.SALES_ORDER_NUMBER,'
||v_temp_sql10
||' x.DEMAND_VISIBLE,'
||' x.SALESREP_CONTACT,';
v_select_sql := ' SELECT /*+ index( x.ool oe_odr_lines_sn_n1) */ ';
v_select_sql := ' SELECT ';
'INSERT INTO MSC_ST_SALES_ORDERS'
||' ( INVENTORY_ITEM_ID,'
||' SOURCE_ORGANIZATION_ID,' /*8915213*/
||' SOURCE_INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' PRIMARY_UOM_QUANTITY,'
||' RESERVATION_TYPE,'
||' RESERVATION_QUANTITY,'
||' DEMAND_SOURCE_TYPE,'
||' DEMAND_SOURCE_HEADER_ID,'
||' COMPLETED_QUANTITY,'
||' SUBINVENTORY,'
||' DEMAND_CLASS,'
||' REQUIREMENT_DATE,'
||' DEMAND_SOURCE_LINE,'
||' SOURCE_DEMAND_SOURCE_LINE,'
||' DEMAND_SOURCE_DELIVERY,'
||' DEMAND_SOURCE_NAME,'
||' DEMAND_ID,'
||' ROW_TYPE,'
||' SOURCE_DEMAND_ID,'
||' PARENT_DEMAND_ID,'
||' SALES_ORDER_NUMBER,'
||' FORECAST_VISIBLE,'
||' DEMAND_VISIBLE,'
||' SALESREP_CONTACT,'
||' SALESREP_ID,'
||' CUSTOMER_ID,'
||' SHIP_TO_SITE_USE_ID,'
||' BILL_TO_SITE_USE_ID,'
||' REQUEST_DATE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' SELLING_PRICE,'
||' END_ITEM_UNIT_NUMBER,'
||' ORDERED_ITEM_ID,'
||' ORIGINAL_ITEM_ID,'
||' LINK_TO_LINE_ID ,'
||' CUST_PO_NUMBER,'
||' CUSTOMER_LINE_NUMBER,'
||' MFG_LEAD_TIME,'
||' ORG_FIRM_FLAG,'
||' SHIP_SET_ID,'
||' ARRIVAL_SET_ID,'
||' SHIP_SET_NAME,'
||' ARRIVAL_SET_NAME,'
||' ATP_REFRESH_NUMBER,'
||' DELETED_FLAG,'
||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
||' ORIGINAL_SYSTEM_REFERENCE,'
||' CTO_FLAG,'
||' AVAILABLE_TO_MRP,'
||' DEMAND_PRIORITY,'
||' PROMISE_DATE,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID,'
||' SCHEDULE_ARRIVAL_DATE,'
||' LATEST_ACCEPTABLE_DATE,'
||' SHIPPING_METHOD_CODE,'
||' ATO_LINE_ID,'
||' ORDER_DATE_TYPE_CODE,'
||' INTRANSIT_LEAD_TIME)'
||v_select_sql
||' x.INVENTORY_ITEM_ID,'
||' x.DESTINATION_ORGANIZATION_ID,'/*8915213*/
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.PRIMARY_UOM_QUANTITY,'
||' x.RESERVATION_TYPE,'
||' x.RESERVATION_QUANTITY,'
||' x.DEMAND_SOURCE_TYPE,'
||' x.DEMAND_SOURCE_HEADER_ID,'
||' x.COMPLETED_QUANTITY,'
||' x.SUBINVENTORY,'
||' x.DEMAND_CLASS,'
||' x.REQUIREMENT_DATE,'
||' x.DEMAND_SOURCE_LINE,'
||' x.DEMAND_SOURCE_LINE,'
||' x.DEMAND_SOURCE_DELIVERY,'
||' x.DEMAND_SOURCE_NAME,'
||' x.DEMAND_ID,'
||' x.ROW_TYPE,'
||' x.DEMAND_ID,'
||' x.PARENT_DEMAND_ID,'
||' x.SALES_ORDER_NUMBER,'
||v_temp_sql10
||' x.DEMAND_VISIBLE,'
||' x.SALESREP_CONTACT,';
'INSERT INTO MSC_ST_SALES_ORDERS'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' PRIMARY_UOM_QUANTITY,'
||' RESERVATION_TYPE,'
||' RESERVATION_QUANTITY,'
||' DEMAND_SOURCE_TYPE,'
||' DEMAND_SOURCE_HEADER_ID,'
||' COMPLETED_QUANTITY,'
||' SUBINVENTORY,'
||' DEMAND_CLASS,'
||' REQUIREMENT_DATE,'
||' DEMAND_SOURCE_LINE,'
||' DEMAND_SOURCE_DELIVERY,'
||' DEMAND_SOURCE_NAME,'
||' DEMAND_ID,'
||' ROW_TYPE,'
||' PARENT_DEMAND_ID,'
||' SALES_ORDER_NUMBER,'
||' SALESREP_CONTACT,'
||' CUSTOMER_ID,'
||' SHIP_TO_SITE_USE_ID,'
||' BILL_TO_SITE_USE_ID,'
||' ATP_REFRESH_NUMBER,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' SR_INSTANCE_ID)'
||' SELECT'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.PRIMARY_UOM_QUANTITY,'
||' x.RESERVATION_TYPE,'
||' x.RESERVATION_QUANTITY,'
||' x.DEMAND_SOURCE_TYPE,'
||' x.DEMAND_SOURCE_HEADER_ID,'
||' x.COMPLETED_QUANTITY,'
||' x.SUBINVENTORY,'
||' x.DEMAND_CLASS,'
||' x.REQUIREMENT_DATE,'
||' x.DEMAND_SOURCE_LINE,'
||' x.DEMAND_SOURCE_DELIVERY,'
||' x.DEMAND_SOURCE_NAME,'
||' x.DEMAND_ID,'
||' x.ROW_TYPE,'
||' x.PARENT_DEMAND_ID,'
||' x.SALES_ORDER_NUMBER,'
||' x.SALESREP_CONTACT,'
||' x.CUSTOMER_ID,'
||' x.SHIP_TO_SITE_ID,'
||' x.BILL_TO_SITE_ID,'
||' x.RN1,'
||' 2,'
||' :v_refresh_id,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' :v_instance_id'
||' FROM MRP_AP_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND (x.RN1>'||MSC_CL_PULL.v_so_lrn
||' OR x.RN2>'||MSC_CL_PULL.v_so_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_so_lrn||')';
'insert into MSC_ST_RESERVATIONS'
||' ( TRANSACTION_ID,'
||' PARENT_DEMAND_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.DEMAND_ID,'
||' x.PARENT_DEMAND_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
|| v_temp_sql;
'insert into MSC_ST_RESERVATIONS'
||' ( TRANSACTION_ID,'
||' SUPPLY_SOURCE_TYPE_ID,'
||' ORGANIZATION_ID,'
||' INVENTORY_ITEM_ID,'
||' DISPOSITION_TYPE,'
||' DISPOSITION_ID,'
||' ORDER2_ORGANIZATION_ID,'
||' ORDER2_INVENTORY_ITEM_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.TRANSACTION_ID,'
||' x.SUPPLY_SOURCE_TYPE_ID,'
||' x.ORGANIZATION_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.DISPOSITION_TYPE,'
||' x.DISPOSITION_ID,'
||' x.ORDER2_ORGANIZATION_ID,'
||' x.ORDER2_INVENTORY_ITEM_ID,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_REPAIR_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
;
'insert into MSC_ST_RESERVATIONS'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' TRANSACTION_ID,'
||' PARENT_DEMAND_ID,'
||' DISPOSITION_ID,'
||' REQUIREMENT_DATE,'
||' REVISION,'
||' RESERVED_QUANTITY,'
||' DISPOSITION_TYPE,'
||' SUBINVENTORY,'
||' RESERVATION_TYPE,'
||' DEMAND_CLASS,'
||' AVAILABLE_TO_MRP,'
||' RESERVATION_FLAG,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
|| v_temp_sql2
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.DEMAND_ID,'
||' x.PARENT_DEMAND_ID,'
||' x.DISPOSITION_ID,'
||' x.REQUIREMENT_DATE- :v_dgmt,'
||' x.REVISION,'
||' x.RESERVED_QUANTITY,'
||' x.DISPOSITION_TYPE,'
||' x.SUBINVENTORY,'
||' x.RESERVATION_TYPE,'
--||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
||' x.DEMAND_CLASS,'
||' x.AVAILABLE_TO_MRP,'
||' 2,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
|| v_temp_sql3
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str|| v_temp_sql || v_temp_sql1;
'insert into MSC_ST_RESERVATIONS'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' TRANSACTION_ID,'
||' PARENT_DEMAND_ID,'
||' DISPOSITION_ID,'
||' REVISION,'
||' RESERVED_QUANTITY,'
||' DISPOSITION_TYPE,'
||' RESERVATION_TYPE,'
||' SUPPLY_SOURCE_TYPE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.DEMAND_ID,'
||' x.DISPOSITION_ID ,'
||' x.DISPOSITION_ID,'
||' x.REVISION,'
||' x.RESERVED_QUANTITY,'
||' x.DISPOSITION_TYPE,'
||' x.RESERVATION_TYPE,'
||' x.supply_source_type_id,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_REPAIR_TRANSFERS_RESV_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str
||' AND x.RN1 > :v_lrn'
|| v_temp_sql1;
'Insert into MSC_ST_RESERVATIONS'
||' ( TRANSACTION_ID,'
||' SUPPLY_SOURCE_TYPE_ID,'
||' ORGANIZATION_ID,'
||' INVENTORY_ITEM_ID,'
||' DISPOSITION_TYPE,'
||' DISPOSITION_ID,'
||' ORDER2_ORGANIZATION_ID,'
||' ORDER2_INVENTORY_ITEM_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' Select'
||' x.repair_line_id,'
||' 200,'
||' ORGANIZATION_ID,'
||' INVENTORY_ITEM_ID,'
||' null,'
||' null,'
||' null,'
||' null,'
||' 1,'
||' :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
|| ' AND x.RO_STATUS_CODE = '||'''C'''
||' AND x. LAST_UPDATE_DATE > :date1' ;
'insert into MSC_ST_RESERVATIONS'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' TRANSACTION_ID,'
||' PARENT_DEMAND_ID,'
||' DISPOSITION_ID,'
||' REVISION,'
||' RESERVED_QUANTITY,'
||' DISPOSITION_TYPE,'
||' RESERVATION_TYPE,'
||' REPAIR_PO_HEADER_ID,'
||' SUPPLY_SOURCE_TYPE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' distinct'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.DEMAND_ID,'
||' x.DISPOSITION_ID ,'
||' x.DISPOSITION_ID,'
||' x.REVISION,'
||' x.RESERVED_QUANTITY,'
||' x.DISPOSITION_TYPE,'
||' x.RESERVATION_TYPE,'
||' X.REPAIR_PO_HEADER_ID,'
||' x.SUPPLY_SOURCE_TYPE_ID,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_EXT_REP_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
||' AND ((x.RN > :v_lrn) OR (x.RN1 > :v_lrn) OR (x.RN2 > :v_lrn) OR (x.RN3 > :v_lrn)
OR (x.RN4 > :v_lrn) OR (x.RN5 > :v_lrn) OR (x.RN6 > :v_lrn))'
;
' INSERT INTO MSC_ST_DEMANDS'
||'( DISPOSITION_ID,'
||' ORIGINATION_TYPE,'
||' DELETED_FLAG,'
||' ORGANIZATION_ID,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT'
||' x.TRANSACTION_ID,'
||' x.ORIGINATION_TYPE,'
||' 1,'
||' x.ORGANIZATION_ID,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AD_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
|| v_temp_sql;
' INSERT INTO MSC_ST_DEMANDS'
||'( DISPOSITION_ID,'
||' ORIGINATION_TYPE,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' USING_ASSEMBLY_ITEM_ID,'
||' ORDER_NUMBER,'
||' USING_REQUIREMENT_QUANTITY,'
||' USING_ASSEMBLY_DEMAND_DATE,'
||' DEMAND_TYPE,'
||' DEMAND_CLASS,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT'
||' x.TRANSACTION_ID,'
||' x.ORIGINATION_TYPE,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.SOURCE_NAME,'
||' x.PRIMARY_UOM_QUANTITY,'
||' x.REQUIREMENT_DATE,'
||' 1,' -- demand type
--||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
||' x.DEMAND_CLASS,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AP_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
'INSERT INTO MSC_ST_SALES_ORDERS'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' PRIMARY_UOM_QUANTITY,'
||' RESERVATION_TYPE,'
||' RESERVATION_QUANTITY,'
||' DEMAND_SOURCE_TYPE,'
||' DEMAND_SOURCE_HEADER_ID,'
||' COMPLETED_QUANTITY,'
||' SUBINVENTORY,'
||' DEMAND_CLASS,'
||' REQUIREMENT_DATE,'
||' DEMAND_SOURCE_LINE,'
||' DEMAND_SOURCE_DELIVERY,'
||' DEMAND_SOURCE_NAME,'
||' DEMAND_ID,'
||' ROW_TYPE,'
||' PARENT_DEMAND_ID,'
||' SALES_ORDER_NUMBER,'
||' FORECAST_VISIBLE,'
||' DEMAND_VISIBLE,'
||' SALESREP_CONTACT,'
||' CUSTOMER_ID,'
||' SHIP_TO_SITE_USE_ID,'
||' BILL_TO_SITE_USE_ID,'
||' REQUEST_DATE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' SELLING_PRICE,'
||' END_ITEM_UNIT_NUMBER,'
||' ORDERED_ITEM_ID,'
||' ORIGINAL_ITEM_ID,'
||' LINK_TO_LINE_ID ,'
||' CUST_PO_NUMBER,'
||' CUSTOMER_LINE_NUMBER,'
||' MFG_LEAD_TIME,'
||' ORG_FIRM_FLAG,'
-- ||' SHIP_SET_ID,'
-- ||' ARRIVAL_SET_ID,'
||' ATP_REFRESH_NUMBER,'
||' DELETED_FLAG,'
||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
||' ORIGINAL_SYSTEM_REFERENCE,'
||' CTO_FLAG,'
||' AVAILABLE_TO_MRP,'
||' DEMAND_PRIORITY,'
||' PROMISE_DATE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID) '
-- ||' SCHEDULE_ARRIVAL_DATE,'
-- ||' LATEST_ACCEPTABLE_DATE,'
-- ||' SHIPPING_METHOD_CODE,'
-- ||' ATO_LINE_ID,'
-- ||' ORDER_DATE_TYPE_CODE)'
||' SELECT '
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.PRIMARY_UOM_QUANTITY,'
||' x.RESERVATION_TYPE,'
||' x.RESERVATION_QUANTITY,'
||' x.DEMAND_SOURCE_TYPE,'
||' x.DEMAND_SOURCE_HEADER_ID,'
||' x.COMPLETED_QUANTITY,'
||' x.SUBINVENTORY,'
||' x.DEMAND_CLASS,'
||' x.REQUIREMENT_DATE,'
||' x.DEMAND_SOURCE_LINE,'
||' x.DEMAND_SOURCE_DELIVERY,'
||' x.DEMAND_SOURCE_NAME,'
||' x.DEMAND_ID,'
||' x.ROW_TYPE,'
||' x.PARENT_DEMAND_ID,'
||' x.SALES_ORDER_NUMBER,'
-- ||' x.FORECAST_VISIBLE,' Bug 14105975
||' ''Y'','
||' x.DEMAND_VISIBLE,'
||' x.SALESREP_CONTACT,'
||' x.CUSTOMER_ID,'
||' x.SHIP_TO_SITE_ID,'
||' x.BILL_TO_SITE_ID,'
||' x.REQUEST_DATE,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' x.LIST_PRICE,'
||' x.END_ITEM_UNIT_NUMBER ,'
||' x.ordered_item_id,'
||' x.ORIGINAL_INVENTORY_ITEM_ID , '
||' x.LINK_TO_LINE_ID,'
||' x.cust_po_number,'
||' x.customer_line_number,'
||' x.MFG_LEAD_TIME,'
||' 1,'
-- ||' x.SHIP_SET_ID,'
-- ||' x.ARRIVAL_SET_ID,'
||' x.RN1,'
||' 2,'
||' x.ORIGINAL_SYSTEM_LINE_REFERENCE,'
||' x.ORIGINAL_SYSTEM_REFERENCE,'
||' x.CTO_FLAG,'
||lv_usaf_temp_sql
||' x.DEMAND_PRIORITY,'
||' x.PROMISE_DATE,'
||' :v_refresh_id,'
||' :v_instance_id '
-- ||' x.SCHEDULE_ARRIVAL_DATE,'
-- ||' x.LATEST_ACCEPTABLE_DATE,'
-- ||' x.SHIPPING_METHOD_CODE,'
-- ||' x.ATO_LINE_ID,'
-- ||' x.ORDER_DATE_TYPE_CODE '
||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
|| v_temp_sql3 ;
' INSERT INTO MSC_ST_OPEN_PAYBACKS(
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SCHEDULED_PAYBACK_DATE,
QUANTITY,
LENDING_PROJECT_ID,
LENDING_TASK_ID,
BORROW_PROJECT_ID,
BORROW_TASK_ID,
PLANNING_GROUP,
LENDING_PROJ_PLANNING_GROUP,
END_ITEM_UNIT_NUMBER)
SELECT
:v_instance_id,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SCHEDULED_PAYBACK_DATE,
QUANTITY,
LENDING_PROJECT_ID,
LENDING_TASK_ID,
BORROW_PROJECT_ID,
BORROW_TASK_ID,
PLANNING_GROUP,
LENDING_PROJ_PLANNING_GROUP,
END_ITEM_UNIT_NUMBER
FROM MRP_AP_OPEN_PAYBACK_QTY_V'||MSC_CL_PULL.v_dblink||
' WHERE ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str;