The following lines contain the word 'select', 'insert', 'update' or 'delete':
' insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' ORDER_TYPE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.TRANSACTION_ID,'
||' 1,' -- using 1, such that the MSCCLBAB.LOAD_SUPPLY
||' 1,' -- can pick this PO record up for delete
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_PO_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.RN>'||MSC_CL_PULL.v_lrn
|| v_temp_sql;
'insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
|| v_temp_sql1
||' FROM_ORGANIZATION_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' DISPOSITION_ID,'
||' ORDER_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' QTY_SCRAPPED,'
||' EXPECTED_SCRAP_QTY,'
||' DELIVERY_PRICE,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' FIRM_PLANNED_TYPE,'
||' NEW_DOCK_DATE,'
||' ORDER_NUMBER,'
||' REVISION,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' VMI_FLAG ,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' POSTPROCESSING_LEAD_TIME,'
||' DELETED_FLAG,'
||' PO_LINE_LOCATION_ID,'
||' INTRANSIT_OWNING_ORG_ID,'
||' REQ_LINE_ID,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT '
||' x.TRANSACTION_ID,'
||' x.ITEM_ID,'
||' x.TO_ORGANIZATION_ID,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_TO_SUBINVENTORY,'
||' x.TO_SUBINVENTORY),'
|| v_temp_sql2
||' x.FROM_ORGANIZATION_ID,'
||' x.FROM_ORGANIZATION_ID,'
||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||' x.SHIPMENT_HEADER_ID,'
||' 11,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY),'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY)* '
||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY)* '
||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||' TO_NUMBER(NULL),'
||' x.SHIPMENT_LINE_NUM,'
||' x.SHIPMENT_LINE_ID,'
||' 2,'
||' x.DOCK_DATE- :v_dgmt,'
||' x.SHIPMENT_HEADER_NUM,'
||' TO_CHAR(NULL),'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' mpp.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
|| v_temp_sql
||' 2,'
||' x.LINE_LOCATION_ID,'
||' x.INTRANSIT_OWNING_ORG_ID,'
||' x.REQUISITION_LINE_ID,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||' MRP_AP_PO_SHIP_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND mpp.project_id (+)= x.project_id'
||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_TO_Organization_ID,'
||' x.Organization_ID)'
||' and DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_DESTINATION_TYPE_CODE,'
||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
||' x.RN2>'||MSC_CL_PULL.v_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
' insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' FROM_ORGANIZATION_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' DISPOSITION_ID,'
||' SUPPLIER_ID,'
||' ORDER_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' QTY_SCRAPPED,'
||' EXPECTED_SCRAP_QTY,'
||' DELIVERY_PRICE,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' FIRM_PLANNED_TYPE,'
||' NEW_DOCK_DATE,'
||' ORDER_NUMBER,'
||' REVISION,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' VMI_FLAG,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT '
||' x.TRANSACTION_ID,'
||' x.ITEM_ID,'
||' x.TO_ORGANIZATION_ID,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_TO_SUBINVENTORY,'
||' x.TO_SUBINVENTORY),'
||' x.FROM_ORGANIZATION_ID,'
||' x.FROM_ORGANIZATION_ID,'
||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||' x.SHIPMENT_HEADER_ID,'
||' TO_NUMBER(NULL),'
||' 12,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY),'
||' TO_NUMBER(NULL),'
||' TO_NUMBER(NULL),'
||' TO_NUMBER(NULL),'
||' x.SHIPMENT_LINE_NUM,'
||' x.SHIPMENT_LINE_ID,'
||' 1,'
||' x.DOCK_DATE- :v_dgmt,'
||' x.SHIPMENT_NUM,'
||' TO_CHAR(NULL),'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' mpp.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
|| v_temp_sql
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||' MRP_AP_PO_SHIP_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND mpp.project_id (+)= x.project_id'
||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_TO_Organization_ID,'
||' x.Organization_ID)'
||' and DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_DESTINATION_TYPE_CODE,'
||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
||' x.RN2>'||MSC_CL_PULL.v_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
' insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' FROM_ORGANIZATION_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' DISPOSITION_ID,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' ORDER_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' QTY_SCRAPPED,'
||' EXPECTED_SCRAP_QTY,'
||' DELIVERY_PRICE,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' FIRM_PLANNED_TYPE,'
||' NEW_DOCK_DATE,'
||' ORDER_NUMBER,'
||' REVISION,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' VMI_FLAG,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.TRANSACTION_ID,'
||' x.ITEM_ID,'
||' x.TO_ORGANIZATION_ID,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_TO_SUBINVENTORY,'
||' x.TO_SUBINVENTORY),'
||' x.FROM_ORGANIZATION_ID,'
||' x.FROM_ORGANIZATION_ID,'
||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||' x.PO_HEADER_ID,'
||' x.VENDOR_ID,'
||' x.VENDOR_SITE_ID,'
||' 8,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||' DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY),'
||' TO_NUMBER(NULL),'
||' TO_NUMBER(NULL),'
||' x.UNIT_PRICE,'
||' x.LINE_NUM,'
||' x.PO_LINE_ID,'
||' 1,'
||' x.DOCK_DATE- :v_dgmt,'
||' x.PO_NUMBER,'
||' x.ITEM_REVISION,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' mpp.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
|| v_temp_sql
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||' MRP_AP_PO_RCV_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND mpp.project_id (+)= x.project_id'
||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_TO_Organization_ID,'
||' x.Organization_ID)'
||' and DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_DESTINATION_TYPE_CODE,'
||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||' AND (' /* x.RN1>'||MSC_CL_PULL.v_lrn */
||' x.RN2>'||MSC_CL_PULL.v_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
' insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' DISPOSITION_ID,'
||' ORDER_NUMBER,'
||' INVENTORY_ITEM_ID,'
||' ORDER_TYPE,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' FIRM_PLANNED_TYPE,'
||' NEW_ORDER_QUANTITY,'
||' NEW_SCHEDULE_DATE,'
||' ORGANIZATION_ID,'
||' SUPPLIER_ID,'
||' POSTPROCESSING_LEAD_TIME,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' -1,'
||' x.HEADER_ID,'
||' x.PO_NUMBER,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORDER_TYPE,'
||' x.PURCH_LINE_NUM,'
||' x.LINE_ID,'
||' x.FIRM_PLANNED_STATUS_TYPE,'
||' x.NEW_ORDER_QUANTITY,'
||' x.NEW_SCHEDULE_DATE- :v_dgmt,'
||' x.ORGANIZATION_ID,'
||' x.CUSTOMER_ID,'
|| v_temp_sql
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AP_INTRANSIT_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND (DECODE( :v_so_ship_arrive_value,'
||' 1, NVL(x.arrived_flag, 2), 2)= 2)'
||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
' insert into MSC_ST_SUPPLIES'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' LOT_NUMBER,'
||' ORDER_TYPE,'
||' DELETED_FLAG,'
||' NEW_ORDER_QUANTITY,'
||' PROJECT_ID,'
||' TASK_ID,'
||' UNIT_NUMBER,'
||' PLANNING_PARTNER_SITE_ID,'
||' PLANNING_TP_TYPE,'
||' OWNING_PARTNER_SITE_ID,'
||' OWNING_TP_TYPE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.SUBINVENTORY_CODE,'
||' x.LOT_NUMBER,'
||' 18,'
||' 1,'
|| v_temp_sql --for 11i sources
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_ONHAND_SUPPLIES_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_SUPPLIES'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' LOT_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' EXPIRATION_DATE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' ORDER_TYPE,'
||' FIRM_PLANNED_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' DELETED_FLAG,'
||' PLANNING_PARTNER_SITE_ID,'
||' PLANNING_TP_TYPE,'
||' OWNING_PARTNER_SITE_ID,'
||' OWNING_TP_TYPE,'
||' SR_CUSTOMER_ACCT_ID,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.SUBINVENTORY_CODE,'
||' x.LOT_NUMBER,'
||' x.QUANTITY,'
||' x.EXPIRATION_DATE,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
||' 18,'
||' 2,'
||' SYSDATE,'
||' 2,'
|| v_temp_sql
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x '
/*
||' ( SELECT DISTINCT'
||' inventory_item_id,'
||' organization_id,'
||' subinventory_code,'
||' lot_number'
||' FROM MRP_AN_ONHAND_SUPPLIES_V'||MSC_CL_PULL.v_dblink
||' WHERE ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND rn>'||MSC_CL_PULL.v_lrn||') a'
||' WHERE a.inventory_item_id= x.inventory_item_id'
||' AND a.organization_id= x.organization_id'
||' AND NVL(a.subinventory_code,'' '')= NVL( x.subinventory_code,'' '')'
||' AND NVL(a.lot_number,'' '')= NVL( x.lot_number,'' '')';*/
'insert into MSC_ST_SUPPLIES'
||' ( INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' LOT_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' EXPIRATION_DATE,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' PLANNING_PARTNER_SITE_ID,'
||' PLANNING_TP_TYPE,'
||' OWNING_PARTNER_SITE_ID,'
||' OWNING_TP_TYPE,'
||' SR_CUSTOMER_ACCT_ID,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' ORDER_TYPE,'
||' FIRM_PLANNED_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.SUBINVENTORY_CODE,'
||' x.LOT_NUMBER,'
||' x.QUANTITY,'
||' x.EXPIRATION_DATE,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
|| v_temp_sql
||' 18,'
||' 2,'
||' SYSDATE,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
'insert into MSC_ST_SUPPLIES'
||' ( DISPOSITION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ORDER_TYPE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' select'
||' x.DISPOSITION_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.ORDER_TYPE,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' from MRP_AD_MPS_SUPPLIES_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;
'insert into MSC_ST_SUPPLIES'
||' ( DISPOSITION_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SCHEDULE_DESIGNATOR,'
||' NEW_SCHEDULE_DATE,'
||' LAST_UNIT_START_DATE,'
||' NEW_ORDER_QUANTITY,'
||' DAILY_RATE,'
||' ORDER_TYPE,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' PROJECT_ID,'
||' TASK_ID,'
||' LINE_ID,'
||' UNIT_NUMBER,'
||' PLANNING_GROUP,'
||' FIRM_PLANNED_TYPE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID,'
||' Schedule_origination_type'
||' )'
||' select'
||' x.DISPOSITION_ID,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.SCHEDULE_DESIGNATOR,'
||' x.SCHEDULE_DATE- :v_dgmt,'
||' x.RATE_END_DATE- :v_dgmt,'
||' x.SCHEDULE_QUANTITY,'
||' x.REPETITIVE_DAILY_RATE,'
||' x.ORDER_TYPE,'
||' x.SOURCE_ORGANIZATION_ID,'
||' DECODE(x.SOURCE_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' x.LINE_ID,'
||' x.END_ITEM_UNIT_NUMBER,'
||' x.PLANNING_GROUP,'
||' x.FIRM_PLANNED_TYPE,'
||' 2,'
||' :v_refresh_id,'
||' :v_instance_id,'
||' x.schedule_origination_type '
||' from MRP_AP_MPS_SUPPLIES_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
||' x.RN2>'||MSC_CL_PULL.v_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
' INSERT INTO MSC_ST_SUPPLIES'
||'( DISPOSITION_ID,'
||' ORDER_TYPE,'
||' ORGANIZATION_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT'
||' x.TRANSACTION_ID,'
||' x.ORDER_TYPE,'
||' x.ORGANIZATION_ID,'
||' 1,'
||' :v_refresh_id,'
||' :v_instance_id'
||' FROM MRP_AD_USER_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
|| v_temp_sql;
' INSERT INTO MSC_ST_SUPPLIES'
||'( DISPOSITION_ID,'
||' ORDER_TYPE,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' ORDER_NUMBER,'
||' NEW_ORDER_QUANTITY,'
||' NEW_SCHEDULE_DATE,'
||' FIRM_PLANNED_TYPE,'
||' DEMAND_CLASS,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
||' SR_INSTANCE_ID)'
||' SELECT'
||' x.TRANSACTION_ID,'
||' x.ORDER_TYPE,'
||' x.INVENTORY_ITEM_ID,'
||' x.ORGANIZATION_ID,'
||' x.SOURCE_NAME,'
||' x.PRIMARY_UOM_QUANTITY,'
||' x.EXPECTED_DELIVERY_DATE,'
||' 1,' -- firm planned 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_SUPPLIES_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_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' FROM_ORGANIZATION_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' DISPOSITION_ID,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
||' ORDER_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' QTY_SCRAPPED,'
||' EXPECTED_SCRAP_QTY,'
||' DELIVERY_PRICE,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' FIRM_PLANNED_TYPE,'
||' NEW_DOCK_DATE,'
||' ORDER_NUMBER,'
||' REVISION,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' VMI_FLAG,'
||' PO_LINE_LOCATION_ID,'
||' PO_DISTRIBUTION_ID,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
/* CP change starts */
||' NEW_ORDER_PLACEMENT_DATE,'
/* CP change stops */
/* CP-ACK starts */
||' ORIGINAL_NEED_BY_DATE,'
||' ORIGINAL_QUANTITY,'
||' PROMISED_DATE,'
||' NEED_BY_DATE,'
||' ACCEPTANCE_REQUIRED_FLAG,'
||' POSTPROCESSING_LEAD_TIME,'
/* CP-ACK ends */
||' SR_INSTANCE_ID)'
||' select'
||' x.TRANSACTION_ID,'
||' x.ITEM_ID,'
||' x.TO_ORGANIZATION_ID,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_TO_SUBINVENTORY,'
||' x.TO_SUBINVENTORY),'
||' x.FROM_ORGANIZATION_ID,'
||' x.FROM_ORGANIZATION_ID,'
||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||' x.PO_HEADER_ID,'
||' x.VENDOR_ID,'
||' x.VENDOR_SITE_ID,'
|| v_order_type
||' DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY),'
||' DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY)*'
||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||' DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY)*'
||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||' x.UNIT_PRICE,'
||' x.LINE_NUM,'
||' x.PO_LINE_ID,'
||' DECODE( decode( decode( sign(nvl(x.ph_firm_date,sysdate+1)-sysdate),'
||' 1, x.ph_firm_status_lookup_code,'
||' ''Y''),'
||' ''N'',decode(sign(nvl(x.pll_firm_date,sysdate+1)-sysdate),'
||' 1, x.pll_firm_status_lookup_code,'
||' ''Y''),'
||' ''Y''),'
||' ''Y'',1,'
||' 2),'
||' x.EXPECTED_DOCK_DATE- :v_dgmt,'
||' x.PO_NUMBER,'
||' x.ITEM_REVISION,'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' mpp.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
|| v_temp_sql
||' 2,'
||' :v_refresh_id,'
/* CP change starts */
||' x.NEW_ORDER_PLACEMENT_DATE,'
/* CP change ends */
/* CP-ACK starts */
||' Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_NEED_BY_DATE),'
||' Decode (:G_MSC_CONFIGURATION,:G_CONF_APS,NULL, ORIGINAL_QUANTITY),'
||' x.PROMISED_DATE,'
||' x.NEED_BY_DATE,'
||' x.ACCEPTANCE_REQUIRED_FLAG,'
||' x.POSTPROCESSING_LEAD_TIME,'
/* CP-ACK ends */
||' :v_instance_id'
||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||v_view_name||MSC_CL_PULL.v_dblink||' x'
||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND mpp.project_id (+)= x.project_id'
||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_TO_Organization_ID,'
||' x.Organization_ID)'
||' and DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_DESTINATION_TYPE_CODE,'
||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||' AND (' -- x.RN1>'||MSC_CL_PULL.v_lrn
||' x.RN2>'||MSC_CL_PULL.v_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS120,'x.VMI_FLAG,',MSC_UTIL.G_APPS115,'x.VMI_FLAG,',' NULL, ')
into v_temp_sql
from dual;
'insert into MSC_ST_SUPPLIES'
||' ( SR_MTL_SUPPLY_ID,'
||' INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SUBINVENTORY_CODE,'
||' FROM_ORGANIZATION_ID,'
||' SOURCE_ORGANIZATION_ID,'
||' SOURCE_SR_INSTANCE_ID,'
||' DISPOSITION_ID,'
||' SUPPLIER_ID,'
||' SUPPLIER_SITE_ID,'
-- ||' ORDER_TYPE,'
||' NEW_SCHEDULE_DATE,'
||' NEW_ORDER_QUANTITY,'
||' QTY_SCRAPPED,'
||' EXPECTED_SCRAP_QTY,'
||' DELIVERY_PRICE,'
||' PURCH_LINE_NUM,'
||' PO_LINE_ID,'
||' FIRM_PLANNED_TYPE,'
||' NEW_DOCK_DATE,'
||' ORDER_NUMBER,'
||' REVISION,'
||' PROJECT_ID,'
||' TASK_ID,'
||' PLANNING_GROUP,'
||' UNIT_NUMBER,'
||' VMI_FLAG,'
||' POSTPROCESSING_LEAD_TIME, '
||' ORDER_TYPE,'
||' ITEM_TYPE_ID,'
||' ITEM_TYPE_VALUE,'
||' DELETED_FLAG,'
||' REFRESH_ID,'
/* CP change starts */
||' NEW_ORDER_PLACEMENT_DATE,'
/* CP change ends */
||' SR_INSTANCE_ID)'
||' select'
||' x.TRANSACTION_ID,'
||' x.ITEM_ID,'
||' x.TO_ORGANIZATION_ID,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_TO_SUBINVENTORY,'
||' x.TO_SUBINVENTORY),'
||' x.FROM_ORGANIZATION_ID,'
||' x.FROM_ORGANIZATION_ID,'
||' DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,:v_instance_id),'
||' x.REQUISITION_HEADER_ID,'
||' x.VENDOR_ID,'
||' x.VENDOR_SITE_ID,'
-- ||' 2,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_EXPECTED_DELIVERY_DATE,'
||' x.EXPECTED_DELIVERY_DATE)- :v_dgmt,'
||' DECODE( :v_mps_consume_profile_value, '
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY),'
||' DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY)*'
||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||' DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_PRIMARY_QUANTITY,'
||' x.TO_ORG_PRIMARY_QUANTITY)*'
||' DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),'
||' x.UNIT_PRICE,'
||' x.LINE_NUM,'
||' x.REQ_LINE_ID,'
||' 2,'
||' x.EXPECTED_DOCK_DATE- :v_dgmt,'
||' x.REQUISITION_NUMBER,'
||' TO_CHAR(NULL),'
||' x.PROJECT_ID,'
||' x.TASK_ID,'
||' mpp.PLANNING_GROUP,'
||' x.END_ITEM_UNIT_NUMBER,'
|| v_temp_sql
||' 2,'
||' :v_refresh_id,'
/* CP change starts */
||' x.NEW_ORDER_PLACEMENT_DATE,'
/* CP change starts */
||' :v_instance_id'
||' from PJM_PROJECT_PARAMETERS'||MSC_CL_PULL.v_dblink||' mpp,'
||' MRP_AP_PO_REQ_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'
||' where x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
||' AND mpp.project_id (+)= x.project_id'
||' and mpp.organization_id (+)= DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_TO_Organization_ID,'
||' x.Organization_ID)'
||' and DECODE( :v_mps_consume_profile_value,'
||' 1, x.MRP_DESTINATION_TYPE_CODE,'
||' x.DESTINATION_TYPE_CODE)= ''INVENTORY'''
||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
||' x.RN2>'||MSC_CL_PULL.v_lrn
||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Test Sql PO_REC INSERT :'||v_sql_stmt);