[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select plan_id, organization_id, plan_start_date
from msc_plans
where sr_instance_id = pINSTANCE_ID
and compile_designator = pDESIGNATOR;
' SELECT count(*)'
||' FROM MRP_PLANS'||v_dblink
||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id ' ;
' INSERT INTO MRP_PLANS'||v_dblink
||' ( ORGANIZATION_ID , '
||' COMPILE_DESIGNATOR , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' CURR_SCHEDULE_DESIGNATOR , '
||' CURR_OPERATION_SCHEDULE_TYPE , '
||' CURR_PLAN_TYPE , '
||' CURR_OVERWRITE_OPTION , '
||' CURR_APPEND_PLANNED_ORDERS , '
||' CURR_SCHEDULE_TYPE , '
||' CURR_CUTOFF_DATE , '
||' CURR_PART_INCLUDE_TYPE , '
||' CURR_PLANNING_TIME_FENCE_FLAG , '
||' CURR_DEMAND_TIME_FENCE_FLAG , '
||' CURR_CONSIDER_RESERVATIONS , '
||' CURR_PLAN_SAFETY_STOCK , '
||' CURR_CONSIDER_WIP , '
||' CURR_CONSIDER_PO , '
||' CURR_SNAPSHOT_LOCK , '
||' COMPILE_DEFINITION_DATE , '
||' SCHEDULE_DESIGNATOR , '
||' OPERATION_SCHEDULE_TYPE , '
||' PLAN_TYPE , '
||' OVERWRITE_OPTION , '
||' APPEND_PLANNED_ORDERS , '
||' SCHEDULE_TYPE , '
||' CUTOFF_DATE , '
||' PART_INCLUDE_TYPE , '
||' PLANNING_TIME_FENCE_FLAG , '
||' DEMAND_TIME_FENCE_FLAG , '
||' CONSIDER_RESERVATIONS , '
||' PLAN_SAFETY_STOCK , '
||' CONSIDER_WIP , '
||' CONSIDER_PO , '
||' SNAPSHOT_LOCK , '
||' DATA_START_DATE , '
||' DATA_COMPLETION_DATE , '
||' EXPLOSION_COMPLETION_DATE , '
||' PLAN_START_DATE , '
||' PLAN_COMPLETION_DATE , '
||' DESCRIPTION , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' ATTRIBUTE_CATEGORY , '
||' ATTRIBUTE1 , '
||' ATTRIBUTE2 , '
||' ATTRIBUTE3 , '
||' ATTRIBUTE4 , '
||' ATTRIBUTE5 , '
||' ATTRIBUTE6 , '
||' ATTRIBUTE7 , '
||' ATTRIBUTE8 , '
||' ATTRIBUTE9 , '
||' ATTRIBUTE10 , '
||' ATTRIBUTE11 , '
||' ATTRIBUTE12 , '
||' ATTRIBUTE13 , '
||' ATTRIBUTE14 , '
||' ATTRIBUTE15 , '
||' ONLINE_PLANNER_START_DATE , '
||' ONLINE_PLANNER_COMPLETION_DATE , '
||' CURR_FULL_PEGGING , '
||' FULL_PEGGING , '
||' ASSIGNMENT_SET_ID , '
||' CURR_ASSIGNMENT_SET_ID , '
||' ORGANIZATION_SELECTION , '
||' CURR_RESERVATION_LEVEL , '
||' CURR_HARD_PEGGING_LEVEL , '
||' RESERVATION_LEVEL , '
||' HARD_PEGGING_LEVEL ) '
||' SELECT '
||' ORGANIZATION_ID,'
||' COMPILE_DESIGNATOR , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' CURR_SCHEDULE_DESIGNATOR , '
||' CURR_OPERATION_SCHEDULE_TYPE , '
||' CURR_PLAN_TYPE , '
||' CURR_OVERWRITE_OPTION , '
||' CURR_APPEND_PLANNED_ORDERS , '
||' CURR_SCHEDULE_TYPE , '
||' CURR_CUTOFF_DATE , '
||' CURR_PART_INCLUDE_TYPE , '
||' CURR_PLANNING_TIME_FENCE_FLAG , '
||' CURR_DEMAND_TIME_FENCE_FLAG , '
||' 1,'
||' 1,'
||' 1, '
||' 1,'
||' 1,'
||' SYSDATE, '
||' SCHEDULE_DESIGNATOR , '
||' OPERATION_SCHEDULE_TYPE , '
||' PLAN_TYPE , '
||' OVERWRITE_OPTION , '
||' APPEND_PLANNED_ORDERS , '
||' SCHEDULE_TYPE , '
||' CUTOFF_DATE , '
||' PART_INCLUDE_TYPE , '
||' PLANNING_TIME_FENCE_FLAG , '
||' DEMAND_TIME_FENCE_FLAG , '
||' CONSIDER_RESERVATIONS , '
||' PLAN_SAFETY_STOCK , '
||' CONSIDER_WIP , '
||' CONSIDER_PO , '
||' SNAPSHOT_LOCK , '
||' DATA_START_DATE , '
||' DATA_COMPLETION_DATE , '
||' DATA_COMPLETION_DATE , '
||' PLAN_START_DATE , '
||' PLAN_COMPLETION_DATE , '
||' DESCRIPTION , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' ATTRIBUTE_CATEGORY , '
||' ATTRIBUTE1 , '
||' ATTRIBUTE2 , '
||' ATTRIBUTE3 , '
||' ATTRIBUTE4 , '
||' ATTRIBUTE5 , '
||' ATTRIBUTE6 , '
||' ATTRIBUTE7 , '
||' ATTRIBUTE8 , '
||' ATTRIBUTE9 , '
||' ATTRIBUTE10 , '
||' ATTRIBUTE11 , '
||' ATTRIBUTE12 , '
||' ATTRIBUTE13 , '
||' ATTRIBUTE14 , '
||' ATTRIBUTE15 , '
||' ONLINE_PLANNER_START_DATE , '
||' ONLINE_PLANNER_COMPLETION_DATE , '
||' CURR_FULL_PEGGING , '
||' FULL_PEGGING , '
||' ASSIGNMENT_SET_ID , '
||' CURR_ASSIGNMENT_SET_ID , '
||' ORGANIZATION_SELECTION , '
||' CURR_RESERVATION_LEVEL , '
||' CURR_HARD_PEGGING_LEVEL , '
||' RESERVATION_LEVEL , '
||' HARD_PEGGING_LEVEL '
||' FROM MSC_PLANS '
||' WHERE plan_id <> -1 '
||' AND plan_id = :plan_id '
||' AND sr_instance_id = :pINSTANCE_ID '
||' AND organization_id = :organization_id ';
/* 2208398 - If the plan exists, update the plan completion date and
data_completion_date */
ELSIF lv_count = 1 then
lv_sql_stmt := ' SELECT plan_start_date'
||' FROM mrp_plans'|| v_dblink
||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id1 ';
lv_sql_stmt := 'update mrp_plans'|| v_dblink
||' set (plan_start_date,plan_completion_date,data_completion_date) = '
||' (select plan_start_date,plan_completion_date, data_completion_date '
||' from msc_plans'
||' where plan_id <> -1'
||' and plan_id = :plan_id '
||' AND sr_instance_id = :pINSTANCE_ID '
||' AND organization_id = :organization_id) '
||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id1 ';
v_buff := 'Number of MRP Plans updated : '||SQL%ROWCOUNT;
select organization_id ,plan_id
from msc_plans
where sr_instance_id = pINSTANCE_ID
and compile_designator = pDESIGNATOR;
select organization_id
from msc_plan_organizations
where sr_instance_id = pINSTANCE_ID
and organization_id = nvl(pORGANIZATION_ID,organization_id)
and plan_id = c_plan_id;
' SELECT count(*)'
||' FROM MRP_PLAN_ORGANIZATIONS'||v_dblink
||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id'
||' AND PLANNED_ORGANIZATION = :organization_id1 ' ;
' INSERT INTO MRP_PLAN_ORGANIZATIONS'||v_dblink
||' (ORGANIZATION_ID ,'
||' COMPILE_DESIGNATOR ,'
||' PLANNED_ORGANIZATION ,'
||' PLAN_LEVEL ,'
||' LAST_UPDATED_BY ,'
||' LAST_UPDATE_DATE ,'
||' CREATED_BY ,'
||' CREATION_DATE ,'
||' LAST_UPDATE_LOGIN ,'
||' NET_WIP ,'
||' NET_RESERVATIONS ,'
||' NET_PURCHASING ,'
||' PLAN_SAFETY_STOCK ,'
||' REQUEST_ID ,'
||' PROGRAM_APPLICATION_ID ,'
||' PROGRAM_ID ,'
||' PROGRAM_UPDATE_DATE ,'
||' ATTRIBUTE_CATEGORY ,'
||' ATTRIBUTE1 ,'
||' ATTRIBUTE2 ,'
||' ATTRIBUTE3 ,'
||' ATTRIBUTE4 ,'
||' ATTRIBUTE5 ,'
||' ATTRIBUTE6 ,'
||' ATTRIBUTE7 ,'
||' ATTRIBUTE8 ,'
||' ATTRIBUTE9 ,'
||' ATTRIBUTE10 ,'
||' ATTRIBUTE11 ,'
||' ATTRIBUTE12 ,'
||' ATTRIBUTE13 ,'
||' ATTRIBUTE14 ,'
||' ATTRIBUTE15 ) '
||'SELECT '
||' :organization_id ,'
||' :pDESIGNATOR, '
||' ORGANIZATION_ID,'
||' PLAN_LEVEL ,'
||' LAST_UPDATED_BY ,'
||' LAST_UPDATE_DATE ,'
||' CREATED_BY ,'
||' CREATION_DATE ,'
||' LAST_UPDATE_LOGIN ,'
||' NET_WIP ,'
||' NET_RESERVATIONS ,'
||' NET_PURCHASING ,'
||' PLAN_SAFETY_STOCK ,'
||' REQUEST_ID ,'
||' PROGRAM_APPLICATION_ID ,'
||' PROGRAM_ID ,'
||' PROGRAM_UPDATE_DATE ,'
||' ATTRIBUTE_CATEGORY ,'
||' ATTRIBUTE1 ,'
||' ATTRIBUTE2 ,'
||' ATTRIBUTE3 ,'
||' ATTRIBUTE4 ,'
||' ATTRIBUTE5 ,'
||' ATTRIBUTE6 ,'
||' ATTRIBUTE7 ,'
||' ATTRIBUTE8 ,'
||' ATTRIBUTE9 ,'
||' ATTRIBUTE10 ,'
||' ATTRIBUTE11 ,'
||' ATTRIBUTE12 ,'
||' ATTRIBUTE13 ,'
||' ATTRIBUTE14 ,'
||' ATTRIBUTE15 '
||'FROM MSC_PLAN_ORGANIZATIONS '
||'WHERE plan_id = :plan_id '
||'AND sr_instance_id = :pINSTANCE_ID '
||' AND organization_id = :organization_id1 ';
select organization_id
from msc_plans
where sr_instance_id = pINSTANCE_ID
and compile_designator = pDESIGNATOR;
SELECT PRODUCTION , ORGANIZATION_ID, DESIGNATOR_TYPE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
FROM MSC_DESIGNATORS
WHERE designator = pDESIGNATOR
AND sr_instance_id = pINSTANCE_ID
AND organization_id = c_org_id
AND designator_type <> G_MPS_IND;
SELECT PRODUCTION , ORGANIZATION_ID, DESIGNATOR_TYPE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
FROM MSC_DESIGNATORS
WHERE designator = pDESIGNATOR
AND sr_instance_id = pINSTANCE_ID
AND organization_id = c_org_id
AND designator_type = G_MPS_IND;
' SELECT count(*)'
||' FROM MRP_DESIGNATORS'||v_dblink
||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id ';
' INSERT INTO MRP_DESIGNATORS'||v_dblink
||' (COMPILE_DESIGNATOR , '
||' ORGANIZATION_ID , '
||' LAST_UPDATE_DATE ,'
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' DESCRIPTION , '
||' DISABLE_DATE , '
||' FEEDBACK_FLAG , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' ATTRIBUTE_CATEGORY , '
||' ATTRIBUTE1 , '
||' ATTRIBUTE2 , '
||' ATTRIBUTE3 , '
||' ATTRIBUTE4 , '
||' ATTRIBUTE5 , '
||' ATTRIBUTE6 , '
||' ATTRIBUTE7 , '
||' ATTRIBUTE8 , '
||' ATTRIBUTE9 , '
||' ATTRIBUTE10 , '
||' ATTRIBUTE11 , '
||' ATTRIBUTE12 , '
||' ATTRIBUTE13 , '
||' ATTRIBUTE14 , '
||' ATTRIBUTE15 , '
||' ORGANIZATION_SELECTION , '
||' DRP_PLAN , '
||' PRODUCTION )'
||'SELECT '
||' DESIGNATOR , '
||' ORGANIZATION_ID , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' DESCRIPTION , '
||' DISABLE_DATE , '
||' 1, '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' ATTRIBUTE_CATEGORY , '
||' ATTRIBUTE1 , '
||' ATTRIBUTE2 , '
||' ATTRIBUTE3 , '
||' ATTRIBUTE4 , '
||' ATTRIBUTE5 , '
||' ATTRIBUTE6 , '
||' ATTRIBUTE7 , '
||' ATTRIBUTE8 , '
||' ATTRIBUTE9 , '
||' ATTRIBUTE10 , '
||' ATTRIBUTE11 , '
||' ATTRIBUTE12 , '
||' ATTRIBUTE13 , '
||' ATTRIBUTE14 , '
||' ATTRIBUTE15 , '
||' ORGANIZATION_SELECTION , '
||' DECODE(DESIGNATOR_TYPE,4,1,2) ,'
||' PRODUCTION '
||' FROM MSC_DESIGNATORS '
||' WHERE designator = :pDESIGNATOR '
||' AND sr_instance_id = :pINSTANCE_ID '
||' AND organization_id = :organization_id '
||' AND designator_type <> '|| G_MPS_IND;
' UPDATE MRP_DESIGNATORS'||v_dblink
||' SET PRODUCTION = :production '
||' , DRP_PLAN = DECODE(:designator_type,4,1,2)'
||' , LAST_UPDATE_DATE = :LAST_UPDATE_DATE'
||' , LAST_UPDATED_BY = :LAST_UPDATED_BY'
||' , LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN'
||' WHERE COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id ';
c_rec1.LAST_UPDATE_DATE,
c_rec1.LAST_UPDATED_BY,
c_rec1.LAST_UPDATE_LOGIN,
pDESIGNATOR,
c_rec1.organization_id;
' SELECT count(*)'
||' FROM MRP_SCHEDULE_DESIGNATORS'||v_dblink
||' WHERE SCHEDULE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id ';
' INSERT INTO MRP_SCHEDULE_DESIGNATORS'||v_dblink
||' (SCHEDULE_DESIGNATOR , '
||' ORGANIZATION_ID , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' DESCRIPTION , '
||' DISABLE_DATE , '
||' MPS_RELIEF , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' ATTRIBUTE_CATEGORY , '
||' ATTRIBUTE1 , '
||' ATTRIBUTE2 , '
||' ATTRIBUTE3 , '
||' ATTRIBUTE4 , '
||' ATTRIBUTE5 , '
||' ATTRIBUTE6 , '
||' ATTRIBUTE7 , '
||' ATTRIBUTE8 , '
||' ATTRIBUTE9 , '
||' ATTRIBUTE10 , '
||' ATTRIBUTE11 , '
||' ATTRIBUTE12 , '
||' ATTRIBUTE13 , '
||' ATTRIBUTE14 , '
||' ATTRIBUTE15 , '
||' ORGANIZATION_SELECTION , '
||' SCHEDULE_TYPE , '
||' INVENTORY_ATP_FLAG , '
||' DEMAND_CLASS , '
||' PRODUCTION )'
||'SELECT '
||' DESIGNATOR , '
||' ORGANIZATION_ID , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' DESCRIPTION , '
||' DISABLE_DATE , '
||' MPS_RELIEF , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' ATTRIBUTE_CATEGORY , '
||' ATTRIBUTE1 , '
||' ATTRIBUTE2 , '
||' ATTRIBUTE3 , '
||' ATTRIBUTE4 , '
||' ATTRIBUTE5 , '
||' ATTRIBUTE6 , '
||' ATTRIBUTE7 , '
||' ATTRIBUTE8 , '
||' ATTRIBUTE9 , '
||' ATTRIBUTE10 , '
||' ATTRIBUTE11 , '
||' ATTRIBUTE12 , '
||' ATTRIBUTE13 , '
||' ATTRIBUTE14 , '
||' ATTRIBUTE15 , '
||' ORGANIZATION_SELECTION , '
|| G_MPS_IND ||' , '
||' INVENTORY_ATP_FLAG , '
||' DEMAND_CLASS , '
||' PRODUCTION '
||' FROM MSC_DESIGNATORS '
||' WHERE designator = :pDESIGNATOR '
||' AND sr_instance_id = :pINSTANCE_ID '
||' AND organization_id = :organization_id '
||' AND designator_type = ' || G_MPS_IND;
' UPDATE MRP_SCHEDULE_DESIGNATORS'||v_dblink
||' SET PRODUCTION = :production '
||' , LAST_UPDATE_DATE = :LAST_UPDATE_DATE'
||' , LAST_UPDATED_BY = :LAST_UPDATED_BY'
||' , LAST_UPDATE_LOGIN = :LAST_UPDATE_LOGIN'
||' WHERE SCHEDULE_DESIGNATOR = :pDESIGNATOR '
||' AND ORGANIZATION_ID = :organization_id '
||' AND SCHEDULE_TYPE = ' || G_MPS_IND;
c_rec1.LAST_UPDATE_DATE,
c_rec1.LAST_UPDATED_BY,
c_rec1.LAST_UPDATE_LOGIN,
pDESIGNATOR,
c_rec1.organization_id;
select plan_id
from msc_plans
where sr_instance_id = pINSTANCE_ID
and compile_designator = pDESIGNATOR;
select organization_id
from msc_plan_organizations
where sr_instance_id = pINSTANCE_ID
and organization_id = nvl(pORGANIZATION_ID,organization_id)
and plan_id = c_plan_id;
select count(*) from MSC_SUPPLIES ms
where ms.plan_id = c_plan_id
and ms.sr_instance_id = pINSTANCE_ID
and ms.order_type = 5
AND nvl(ms.source_supplier_id,ms.supplier_id) is NOT NULL
AND nvl(ms.source_supplier_site_id,ms.supplier_site_id) is NOT NULL;
SELECT DECODE( M2A_DBLINK,
NULL, NULL_DBLINK,
'@'||M2A_DBLINK),
DECODE( M2A_DBLINK,
NULL, SYS_NO,
SYS_YES),
APPS_VER,
INSTANCE_TYPE -- For outbound XML
INTO v_dblink,
v_distributed_config_flag,
lv_apps_ver,
v_ins_type -- For outbound XML
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
select sr_tp_id into lv_sr_tp_id
from msc_trading_partners
where sr_instance_id = pINSTANCE_ID
and partner_id = pSUPPLIER_ID;
select sr_tp_site_id into lv_sr_tp_site_id
from msc_trading_partner_sites
where partner_id = pSUPPLIER_ID
and partner_site_id = pSUPPLIER_SITE_ID
and sr_instance_id = pINSTANCE_ID;
/* Begin Delete Recommendations */
lv_sql_stmt :=
'DELETE FROM MRP_RECOMMENDATIONS'||v_dblink||' MRO'
||' WHERE MRO.COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND MRO.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
' AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS'||v_dblink||' MSI'
||' WHERE MSI.INVENTORY_ITEM_ID = MRO.INVENTORY_ITEM_ID'
||' AND MSI.ORGANIZATION_ID = MRO.ORGANIZATION_ID'
||' AND MSI.PLANNER_CODE = :pPLANNER)';
||' AND EXISTS (SELECT 1 FROM MRP_AP_ITEM_CATEGORIES_V'||v_dblink||' MAICV, MRP_AP_CATEGORY_SETS_V'||v_dblink||' MACSV'
||' WHERE MAICV.INVENTORY_ITEM_ID = MRO.INVENTORY_ITEM_ID AND MAICV.ORGANIZATION_ID ='
||' MRO.ORGANIZATION_ID AND MAICV.CATEGORY_ID = :pCATEGORY_ID'
||' AND MAICV.CATEGORY_SET_ID = MACSV.CATEGORY_SET_ID'
||' AND MAICV.LANGUAGE = MACSV.LANGUAGE'
||' AND MACSV.LANGUAGE = :pLANG'
||' AND MACSV.DEFAULT_FLAG = 1)';
'(SELECT SR_TP_SITE_ID ' ||
'FROM MSC_TP_SITE_ID_LID ' ||
'WHERE SR_INSTANCE_ID = :pINSTANCE_ID '||
'AND PARTNER_TYPE = 1 '||
'AND TP_SITE_ID = :pSUPPLIER_SITE_ID)';
/* End Delete Recommendations */
/* Begin Delete Items */
lv_sql_stmt := 'DELETE FROM MRP_SYSTEM_ITEMS'||v_dblink||' MSI'
||' WHERE MSI.COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND MSI.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
' AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS'||v_dblink||' MSI2'
||' WHERE MSI2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID'
||' AND MSI2.ORGANIZATION_ID = MSI.ORGANIZATION_ID'
||' AND MSI2.PLANNER_CODE = :pPLANNER)';
||' AND EXISTS (SELECT 1 FROM MRP_AP_ITEM_CATEGORIES_V'||v_dblink||' MAICV, MRP_AP_CATEGORY_SETS_V'||v_dblink||' MACSV'
||' WHERE MAICV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MAICV.ORGANIZATION_ID ='
||' MSI.ORGANIZATION_ID AND MAICV.CATEGORY_ID = :pCATEGORY_ID'
||' AND MAICV.CATEGORY_SET_ID = MACSV.CATEGORY_SET_ID'
||' AND MAICV.LANGUAGE = MACSV.LANGUAGE'
||' AND MACSV.LANGUAGE = :pLANG'
||' AND MACSV.DEFAULT_FLAG = 1)';
/* End Delete Items */
IF pDEMAND = 1 THEN
/* Begin Delete Gross Requirements */
lv_sql_stmt := 'DELETE FROM MRP_GROSS_REQUIREMENTS'||v_dblink||' MGR'
||' WHERE MGR.COMPILE_DESIGNATOR = :pDESIGNATOR '
||' AND MGR.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
' AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS'||v_dblink||' MSI'
||' WHERE MSI.INVENTORY_ITEM_ID = MGR.INVENTORY_ITEM_ID'
||' AND MSI.ORGANIZATION_ID = MGR.ORGANIZATION_ID'
||' AND MSI.PLANNER_CODE = :pPLANNER)';
||' AND EXISTS (SELECT 1 FROM MRP_AP_ITEM_CATEGORIES_V'||v_dblink||' MAICV, MRP_AP_CATEGORY_SETS_V'||v_dblink||' MACSV'
||' WHERE MAICV.INVENTORY_ITEM_ID = MGR.INVENTORY_ITEM_ID AND MAICV.ORGANIZATION_ID ='
||' MGR.ORGANIZATION_ID AND MAICV.CATEGORY_ID = :pCATEGORY_ID'
||' AND MAICV.CATEGORY_SET_ID = MACSV.CATEGORY_SET_ID'
||' AND MAICV.LANGUAGE = MACSV.LANGUAGE'
||' AND MACSV.LANGUAGE = :pLANG'
||' AND MACSV.DEFAULT_FLAG = 1)';
/* End Delete Gross requirements */
END IF; --for bug 3073566
'INSERT INTO MRP_RECOMMENDATIONS'||v_dblink
||'( TRANSACTION_ID , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' INVENTORY_ITEM_ID , '
||' ORGANIZATION_ID , '
||' COMPILE_DESIGNATOR,'
||' NEW_SCHEDULE_DATE , '
||' OLD_SCHEDULE_DATE , '
||' NEW_WIP_START_DATE , '
||' OLD_WIP_START_DATE , '
||' DISPOSITION_ID , '
||' DISPOSITION_STATUS_TYPE , '
||' ORDER_TYPE , '
||' VENDOR_ID , '
||' VENDOR_SITE_ID , '
||' NEW_ORDER_QUANTITY , '
||' OLD_ORDER_QUANTITY , '
||' NEW_ORDER_PLACEMENT_DATE , '
||' OLD_ORDER_PLACEMENT_DATE , '
||' FIRM_PLANNED_TYPE , '
||' NEW_PROCESSING_DAYS , '
||' IMPLEMENTED_QUANTITY , '
||' PURCH_LINE_NUM , '
||' REVISION , '
||' LAST_UNIT_COMPLETION_DATE , '
||' FIRST_UNIT_START_DATE , '
||' LAST_UNIT_START_DATE , '
||' DAILY_RATE , '
||' OLD_DOCK_DATE , '
||' NEW_DOCK_DATE , '
||' RESCHEDULE_DAYS , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' QUANTITY_IN_PROCESS , '
||' FIRM_QUANTITY , '
||' FIRM_DATE , '
||' UPDATED , '
||' STATUS , '
||' APPLIED , '
||' IMPLEMENT_DEMAND_CLASS , '
||' IMPLEMENT_DATE , '
||' IMPLEMENT_QUANTITY , '
||' IMPLEMENT_FIRM , '
||' IMPLEMENT_WIP_CLASS_CODE , '
||' IMPLEMENT_JOB_NAME , '
||' IMPLEMENT_DOCK_DATE , '
||' IMPLEMENT_STATUS_CODE , '
||' IMPLEMENT_EMPLOYEE_ID , '
||' IMPLEMENT_UOM_CODE , '
||' IMPLEMENT_LOCATION_ID , '
||' RELEASE_STATUS , '
||' LOAD_TYPE , '
||' IMPLEMENT_AS , '
||' DEMAND_CLASS , '
||' ALTERNATE_BOM_DESIGNATOR , '
||' ALTERNATE_ROUTING_DESIGNATOR , '
||' LINE_ID , '
||' BY_PRODUCT_USING_ASSY_ID , '
||' IMPLEMENT_SOURCE_ORG_ID , '
||' IMPLEMENT_VENDOR_ID , '
||' IMPLEMENT_VENDOR_SITE_ID , '
||' SOURCE_ORGANIZATION_ID , '
||' SOURCE_VENDOR_SITE_ID , '
||' SOURCE_VENDOR_ID , '
||' NEW_SHIP_DATE , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' IMPLEMENT_PROJECT_ID , '
||' IMPLEMENT_TASK_ID , '
||' IMPLEMENT_SCHEDULE_GROUP_ID , '
||' IMPLEMENT_BUILD_SEQUENCE , '
||' RELEASE_ERRORS , '
||' SCHEDULE_COMPRESSION_DAYS , '
||' NUMBER1 '
||')'
||'SELECT /*+ index(ms msc_supplies_n8) leading(ms) */ '
||' MRP_SCHEDULE_DATES_S.nextval'||v_dblink||' , '
||' ms.LAST_UPDATE_DATE , '
||' ms.LAST_UPDATED_BY , '
||' ms.CREATION_DATE , '
||' ms.CREATED_BY , '
||' ms.LAST_UPDATE_LOGIN , '
||' msi.SR_INVENTORY_ITEM_ID , '
||' ms.ORGANIZATION_ID , '
||' :p_DESIGNATOR,'
||' NEW_SCHEDULE_DATE , '
||' OLD_SCHEDULE_DATE , '
||' NEW_WIP_START_DATE , '
||' OLD_WIP_START_DATE , '
||' MRP_SCHEDULE_DATES_S.currval'||v_dblink||' , '
||' DISPOSITION_STATUS_TYPE , '
||' ORDER_TYPE , '
||' mtil.sr_tp_id,'
||' mtsil.sr_tp_site_id,'
||' NEW_ORDER_QUANTITY , '
||' OLD_ORDER_QUANTITY , '
||' NEW_ORDER_PLACEMENT_DATE , '
||' OLD_ORDER_PLACEMENT_DATE , '
||' FIRM_PLANNED_TYPE , '
||' NEW_PROCESSING_DAYS , '
||' IMPLEMENTED_QUANTITY , '
||' PURCH_LINE_NUM , '
||' ms.REVISION , '
||' LAST_UNIT_COMPLETION_DATE , '
||' FIRST_UNIT_START_DATE , '
||' LAST_UNIT_START_DATE , '
||' DAILY_RATE , '
||' OLD_DOCK_DATE , '
||' NEW_DOCK_DATE , '
||' RESCHEDULE_DAYS , '
||' ms.REQUEST_ID , '
||' ms.PROGRAM_APPLICATION_ID , '
||' ms.PROGRAM_ID , '
||' ms.PROGRAM_UPDATE_DATE , '
||' ms.QUANTITY_IN_PROCESS , '
||' ms.FIRM_QUANTITY , '
||' ms.FIRM_DATE , '
||' UPDATED , '
||' ms.STATUS , '
||' APPLIED , '
||' IMPLEMENT_DEMAND_CLASS , '
||' IMPLEMENT_DATE , '
||' IMPLEMENT_QUANTITY , '
||' IMPLEMENT_FIRM , '
||' IMPLEMENT_WIP_CLASS_CODE , '
||' IMPLEMENT_JOB_NAME , '
||' IMPLEMENT_DOCK_DATE , '
||' IMPLEMENT_STATUS_CODE , '
||' IMPLEMENT_EMPLOYEE_ID , '
||' IMPLEMENT_UOM_CODE , '
||' IMPLEMENT_LOCATION_ID , '
||' RELEASE_STATUS , '
||' LOAD_TYPE , '
||' IMPLEMENT_AS , '
||' DEMAND_CLASS , '
||' ALTERNATE_BOM_DESIGNATOR , '
||' ALTERNATE_ROUTING_DESIGNATOR , '
||' LINE_ID , '
||' BY_PRODUCT_USING_ASSY_ID , '
||' IMPLEMENT_SOURCE_ORG_ID , '
||' mtil.sr_tp_id,'
||' mtsil.sr_tp_site_id,'
||' SOURCE_ORGANIZATION_ID , '
||' mtsil.sr_tp_site_id,'
||' mtil.sr_tp_id,'
||' NEW_SHIP_DATE , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' IMPLEMENT_PROJECT_ID , '
||' IMPLEMENT_TASK_ID , '
||' IMPLEMENT_SCHEDULE_GROUP_ID , '
||' IMPLEMENT_BUILD_SEQUENCE , '
||' RELEASE_ERRORS , '
||' SCHEDULE_COMPRESS_DAYS , '
||' NUMBER1 '
||' FROM MSC_SUPPLIES ms,'
||' MSC_TP_ID_LID mtil,'
||' MSC_TP_SITE_ID_LID mtsil ,'
||' msc_trading_partners ORG ,'
||' MSC_SYSTEM_ITEMS msi'
||' WHERE ms.plan_id = :PLAN_ID'
||' AND ms.sr_instance_id = :pINSTANCE_ID'
||' AND msi.organization_id = ms.organization_id'
||' AND msi.inventory_item_id = ms.inventory_item_id'
||' AND msi.sr_instance_id = ms.sr_instance_id'
||' AND msi.plan_id = ms.plan_id'
||' and trunc(ms.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
||' AND mtil.tp_id = nvl(ms.source_supplier_id,ms.supplier_id)'
||' AND mtil.partner_type = 1'
||' AND ORG.SR_TP_ID = ms.organization_id '
||' AND ORG.partner_type = 3'
||' AND ORG.sr_instance_id = ms.sr_instance_id'
||' AND nvl(mtsil.operating_unit, -1) = nvl(ORG.OPERATING_UNIT, -1) '
||' AND mtil.sr_instance_id = ms.sr_instance_id'
||' AND mtsil.tp_site_id = nvl(source_supplier_site_id,ms.supplier_site_id)'
||' AND mtsil.partner_type = 1'
||' AND mtsil.sr_instance_id = ms.sr_instance_id'
||' AND nvl(source_supplier_site_id,ms.supplier_site_id) IS NOT NULL'
||' AND NOT EXISTS (select 1 from msc_system_items msi1 , msc_trading_partners mtp'
||' where msi1.inventory_item_id = ms.inventory_item_id '
||' and msi1.organization_id = ms.organization_id '
||' and msi1.plan_id = ms.plan_id '
||' AND msi1.sr_instance_id = ms.sr_instance_id '
||' and nvl(msi1.release_time_fence_code,-1) = 7 '
||' and mtp.sr_tp_id = msi1.organization_id '
||' and mtp.sr_instance_id = msi1.sr_instance_id '
||' and mtp.partner_type=3 '
||' and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))'
||' AND ms.order_type = 5 '|| lv_is_supp_not_null;
lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
||' where mic.inventory_item_id = msi.inventory_item_id'
||' and mic.organization_id = msi.organization_id'
||' and mic.sr_instance_id = msi.sr_instance_id'
||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
||' and mic.category_set_id = mcs.category_set_id'
||' and mcs.sr_instance_id = mic.sr_instance_id'
||' and mcs.DEFAULT_FLAG = 1)';
'INSERT INTO MRP_RECOMMENDATIONS'||v_dblink
||'( TRANSACTION_ID , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' INVENTORY_ITEM_ID , '
||' ORGANIZATION_ID , '
||' COMPILE_DESIGNATOR,'
||' NEW_SCHEDULE_DATE , '
||' OLD_SCHEDULE_DATE , '
||' NEW_WIP_START_DATE , '
||' OLD_WIP_START_DATE , '
||' DISPOSITION_ID , '
||' DISPOSITION_STATUS_TYPE , '
||' ORDER_TYPE , '
||' VENDOR_ID , '
||' NEW_ORDER_QUANTITY , '
||' OLD_ORDER_QUANTITY , '
||' NEW_ORDER_PLACEMENT_DATE , '
||' OLD_ORDER_PLACEMENT_DATE , '
||' FIRM_PLANNED_TYPE , '
||' NEW_PROCESSING_DAYS , '
||' IMPLEMENTED_QUANTITY , '
||' PURCH_LINE_NUM , '
||' REVISION , '
||' LAST_UNIT_COMPLETION_DATE , '
||' FIRST_UNIT_START_DATE , '
||' LAST_UNIT_START_DATE , '
||' DAILY_RATE , '
||' OLD_DOCK_DATE , '
||' NEW_DOCK_DATE , '
||' RESCHEDULE_DAYS , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' QUANTITY_IN_PROCESS , '
||' FIRM_QUANTITY , '
||' FIRM_DATE , '
||' UPDATED , '
||' STATUS , '
||' APPLIED , '
||' IMPLEMENT_DEMAND_CLASS , '
||' IMPLEMENT_DATE , '
||' IMPLEMENT_QUANTITY , '
||' IMPLEMENT_FIRM , '
||' IMPLEMENT_WIP_CLASS_CODE , '
||' IMPLEMENT_JOB_NAME , '
||' IMPLEMENT_DOCK_DATE , '
||' IMPLEMENT_STATUS_CODE , '
||' IMPLEMENT_EMPLOYEE_ID , '
||' IMPLEMENT_UOM_CODE , '
||' IMPLEMENT_LOCATION_ID , '
||' RELEASE_STATUS , '
||' LOAD_TYPE , '
||' IMPLEMENT_AS , '
||' DEMAND_CLASS , '
||' ALTERNATE_BOM_DESIGNATOR , '
||' ALTERNATE_ROUTING_DESIGNATOR , '
||' LINE_ID , '
||' BY_PRODUCT_USING_ASSY_ID , '
||' IMPLEMENT_SOURCE_ORG_ID , '
||' IMPLEMENT_VENDOR_ID , '
||' IMPLEMENT_VENDOR_SITE_ID , '
||' SOURCE_ORGANIZATION_ID , '
||' SOURCE_VENDOR_SITE_ID , '
||' SOURCE_VENDOR_ID , '
||' NEW_SHIP_DATE , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' IMPLEMENT_PROJECT_ID , '
||' IMPLEMENT_TASK_ID , '
||' IMPLEMENT_SCHEDULE_GROUP_ID , '
||' IMPLEMENT_BUILD_SEQUENCE , '
||' RELEASE_ERRORS , '
||' SCHEDULE_COMPRESSION_DAYS , '
||' NUMBER1 '
||')'
||'SELECT /*+ index(ms msc_supplies_n8) leading(ms) */'
||' MRP_SCHEDULE_DATES_S.nextval'||v_dblink||' , '
||' ms.LAST_UPDATE_DATE , '
||' ms.LAST_UPDATED_BY , '
||' ms.CREATION_DATE , '
||' ms.CREATED_BY , '
||' ms.LAST_UPDATE_LOGIN , '
||' msi.SR_INVENTORY_ITEM_ID , '
||' ms.ORGANIZATION_ID , '
||' :p_DESIGNATOR,'
||' NEW_SCHEDULE_DATE , '
||' OLD_SCHEDULE_DATE , '
||' NEW_WIP_START_DATE , '
||' OLD_WIP_START_DATE , '
||' MRP_SCHEDULE_DATES_S.currval'||v_dblink||' , '
||' DISPOSITION_STATUS_TYPE , '
||' ORDER_TYPE , '
||' mtil.sr_tp_id,'
||' NEW_ORDER_QUANTITY , '
||' OLD_ORDER_QUANTITY , '
||' NEW_ORDER_PLACEMENT_DATE , '
||' OLD_ORDER_PLACEMENT_DATE , '
||' FIRM_PLANNED_TYPE , '
||' NEW_PROCESSING_DAYS , '
||' IMPLEMENTED_QUANTITY , '
||' PURCH_LINE_NUM , '
||' ms.REVISION , '
||' LAST_UNIT_COMPLETION_DATE , '
||' FIRST_UNIT_START_DATE , '
||' LAST_UNIT_START_DATE , '
||' DAILY_RATE , '
||' OLD_DOCK_DATE , '
||' NEW_DOCK_DATE , '
||' RESCHEDULE_DAYS , '
||' ms.REQUEST_ID , '
||' ms.PROGRAM_APPLICATION_ID , '
||' ms.PROGRAM_ID , '
||' ms.PROGRAM_UPDATE_DATE , '
||' ms.QUANTITY_IN_PROCESS , '
||' ms.FIRM_QUANTITY , '
||' ms.FIRM_DATE , '
||' UPDATED , '
||' ms.STATUS , '
||' APPLIED , '
||' IMPLEMENT_DEMAND_CLASS , '
||' IMPLEMENT_DATE , '
||' IMPLEMENT_QUANTITY , '
||' IMPLEMENT_FIRM , '
||' IMPLEMENT_WIP_CLASS_CODE , '
||' IMPLEMENT_JOB_NAME , '
||' IMPLEMENT_DOCK_DATE , '
||' IMPLEMENT_STATUS_CODE , '
||' IMPLEMENT_EMPLOYEE_ID , '
||' IMPLEMENT_UOM_CODE , '
||' IMPLEMENT_LOCATION_ID , '
||' RELEASE_STATUS , '
||' LOAD_TYPE , '
||' IMPLEMENT_AS , '
||' DEMAND_CLASS , '
||' ALTERNATE_BOM_DESIGNATOR , '
||' ALTERNATE_ROUTING_DESIGNATOR , '
||' LINE_ID , '
||' BY_PRODUCT_USING_ASSY_ID , '
||' IMPLEMENT_SOURCE_ORG_ID , '
||' mtil.sr_tp_id,'
||' null,'
||' SOURCE_ORGANIZATION_ID , '
||' null,'
||' mtil.sr_tp_id,'
||' NEW_SHIP_DATE , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' IMPLEMENT_PROJECT_ID , '
||' IMPLEMENT_TASK_ID , '
||' IMPLEMENT_SCHEDULE_GROUP_ID , '
||' IMPLEMENT_BUILD_SEQUENCE , '
||' RELEASE_ERRORS , '
||' SCHEDULE_COMPRESS_DAYS , '
||' NUMBER1 '
||' FROM MSC_SUPPLIES ms,'
||' MSC_TP_ID_LID mtil,'
||' MSC_SYSTEM_ITEMS msi'
||' WHERE ms.plan_id = :PLAN_ID'
||' AND ms.sr_instance_id = :pINSTANCE_ID'
||' AND msi.organization_id = ms.organization_id'
||' AND msi.inventory_item_id = ms.inventory_item_id'
||' AND msi.sr_instance_id = ms.sr_instance_id'
||' AND msi.plan_id = ms.plan_id'
||' and trunc(ms.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
||' AND mtil.tp_id = nvl(ms.source_supplier_id,ms.supplier_id)'
||' AND mtil.partner_type = 1'
||' AND mtil.sr_instance_id = ms.sr_instance_id'
||' AND nvl(source_supplier_site_id,ms.supplier_site_id) IS NULL '
||' AND NOT EXISTS (select 1 from msc_system_items msi1 , msc_trading_partners mtp'
||' where msi1.inventory_item_id = ms.inventory_item_id '
||' and msi1.organization_id = ms.organization_id '
||' and msi1.plan_id = ms.plan_id '
||' AND msi1.sr_instance_id = ms.sr_instance_id '
||' and nvl(msi1.release_time_fence_code,-1) = 7 '
||' and mtp.sr_tp_id = msi1.organization_id '
||' and mtp.sr_instance_id = msi1.sr_instance_id '
||' and mtp.partner_type=3 '
||' and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))'
||' AND ms.order_type = 5 '|| lv_is_supp_not_null;
lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
||' where mic.inventory_item_id = msi.inventory_item_id'
||' and mic.organization_id = msi.organization_id'
||' and mic.sr_instance_id = msi.sr_instance_id'
||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
||' and mic.category_set_id = mcs.category_set_id'
||' and mcs.sr_instance_id = mic.sr_instance_id'
||' and mcs.DEFAULT_FLAG = 1)';
'INSERT INTO MRP_RECOMMENDATIONS'||v_dblink
||'( TRANSACTION_ID , '
||' LAST_UPDATE_DATE , '
||' LAST_UPDATED_BY , '
||' CREATION_DATE , '
||' CREATED_BY , '
||' LAST_UPDATE_LOGIN , '
||' INVENTORY_ITEM_ID , '
||' ORGANIZATION_ID , '
||' COMPILE_DESIGNATOR,'
||' NEW_SCHEDULE_DATE , '
||' OLD_SCHEDULE_DATE , '
||' NEW_WIP_START_DATE , '
||' OLD_WIP_START_DATE , '
||' DISPOSITION_ID , '
||' DISPOSITION_STATUS_TYPE , '
||' ORDER_TYPE , '
||' VENDOR_ID , '
||' NEW_ORDER_QUANTITY , '
||' OLD_ORDER_QUANTITY , '
||' NEW_ORDER_PLACEMENT_DATE , '
||' OLD_ORDER_PLACEMENT_DATE , '
||' FIRM_PLANNED_TYPE , '
||' NEW_PROCESSING_DAYS , '
||' IMPLEMENTED_QUANTITY , '
||' PURCH_LINE_NUM , '
||' REVISION , '
||' LAST_UNIT_COMPLETION_DATE , '
||' FIRST_UNIT_START_DATE , '
||' LAST_UNIT_START_DATE , '
||' DAILY_RATE , '
||' OLD_DOCK_DATE , '
||' NEW_DOCK_DATE , '
||' RESCHEDULE_DAYS , '
||' REQUEST_ID , '
||' PROGRAM_APPLICATION_ID , '
||' PROGRAM_ID , '
||' PROGRAM_UPDATE_DATE , '
||' QUANTITY_IN_PROCESS , '
||' FIRM_QUANTITY , '
||' FIRM_DATE , '
||' UPDATED , '
||' STATUS , '
||' APPLIED , '
||' IMPLEMENT_DEMAND_CLASS , '
||' IMPLEMENT_DATE , '
||' IMPLEMENT_QUANTITY , '
||' IMPLEMENT_FIRM , '
||' IMPLEMENT_WIP_CLASS_CODE , '
||' IMPLEMENT_JOB_NAME , '
||' IMPLEMENT_DOCK_DATE , '
||' IMPLEMENT_STATUS_CODE , '
||' IMPLEMENT_EMPLOYEE_ID , '
||' IMPLEMENT_UOM_CODE , '
||' IMPLEMENT_LOCATION_ID , '
||' RELEASE_STATUS , '
||' LOAD_TYPE , '
||' IMPLEMENT_AS , '
||' DEMAND_CLASS , '
||' ALTERNATE_BOM_DESIGNATOR , '
||' ALTERNATE_ROUTING_DESIGNATOR , '
||' LINE_ID , '
||' BY_PRODUCT_USING_ASSY_ID , '
||' IMPLEMENT_SOURCE_ORG_ID , '
||' IMPLEMENT_VENDOR_ID , '
||' IMPLEMENT_VENDOR_SITE_ID , '
||' SOURCE_ORGANIZATION_ID , '
||' SOURCE_VENDOR_SITE_ID , '
||' SOURCE_VENDOR_ID , '
||' NEW_SHIP_DATE , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' IMPLEMENT_PROJECT_ID , '
||' IMPLEMENT_TASK_ID , '
||' IMPLEMENT_SCHEDULE_GROUP_ID , '
||' IMPLEMENT_BUILD_SEQUENCE , '
||' RELEASE_ERRORS , '
||' SCHEDULE_COMPRESSION_DAYS , '
||' NUMBER1 '
||')'
||'SELECT'
||' MRP_SCHEDULE_DATES_S.nextval'||v_dblink||' , '
||' ms.LAST_UPDATE_DATE , '
||' ms.LAST_UPDATED_BY , '
||' ms.CREATION_DATE , '
||' ms.CREATED_BY , '
||' ms.LAST_UPDATE_LOGIN , '
||' msi.SR_INVENTORY_ITEM_ID , '
||' ms.ORGANIZATION_ID , '
||' :p_DESIGNATOR,'
||' NEW_SCHEDULE_DATE , '
||' OLD_SCHEDULE_DATE , '
||' NEW_WIP_START_DATE , '
||' OLD_WIP_START_DATE , '
||' MRP_SCHEDULE_DATES_S.currval'||v_dblink||' , '
||' DISPOSITION_STATUS_TYPE , '
||' ORDER_TYPE , '
||' NULL,'
||' NEW_ORDER_QUANTITY , '
||' OLD_ORDER_QUANTITY , '
||' NEW_ORDER_PLACEMENT_DATE , '
||' OLD_ORDER_PLACEMENT_DATE , '
||' FIRM_PLANNED_TYPE , '
||' NEW_PROCESSING_DAYS , '
||' IMPLEMENTED_QUANTITY , '
||' PURCH_LINE_NUM , '
||' ms.REVISION , '
||' LAST_UNIT_COMPLETION_DATE , '
||' FIRST_UNIT_START_DATE , '
||' LAST_UNIT_START_DATE , '
||' DAILY_RATE , '
||' OLD_DOCK_DATE , '
||' NEW_DOCK_DATE , '
||' RESCHEDULE_DAYS , '
||' ms.REQUEST_ID , '
||' ms.PROGRAM_APPLICATION_ID , '
||' ms.PROGRAM_ID , '
||' ms.PROGRAM_UPDATE_DATE , '
||' ms.QUANTITY_IN_PROCESS , '
||' ms.FIRM_QUANTITY , '
||' ms.FIRM_DATE , '
||' UPDATED , '
||' STATUS , '
||' APPLIED , '
||' IMPLEMENT_DEMAND_CLASS , '
||' IMPLEMENT_DATE , '
||' IMPLEMENT_QUANTITY , '
||' IMPLEMENT_FIRM , '
||' IMPLEMENT_WIP_CLASS_CODE , '
||' IMPLEMENT_JOB_NAME , '
||' IMPLEMENT_DOCK_DATE , '
||' IMPLEMENT_STATUS_CODE , '
||' IMPLEMENT_EMPLOYEE_ID , '
||' IMPLEMENT_UOM_CODE , '
||' IMPLEMENT_LOCATION_ID , '
||' RELEASE_STATUS , '
||' LOAD_TYPE , '
||' IMPLEMENT_AS , '
||' DEMAND_CLASS , '
||' ALTERNATE_BOM_DESIGNATOR , '
||' ALTERNATE_ROUTING_DESIGNATOR , '
||' LINE_ID , '
||' BY_PRODUCT_USING_ASSY_ID , '
||' IMPLEMENT_SOURCE_ORG_ID , '
||' NULL,'
||' NULL,'
||' SOURCE_ORGANIZATION_ID , '
||' NULL,'
||' NULL,'
||' NEW_SHIP_DATE , '
||' PROJECT_ID , '
||' TASK_ID , '
||' PLANNING_GROUP , '
||' IMPLEMENT_PROJECT_ID , '
||' IMPLEMENT_TASK_ID , '
||' IMPLEMENT_SCHEDULE_GROUP_ID , '
||' IMPLEMENT_BUILD_SEQUENCE , '
||' RELEASE_ERRORS , '
||' SCHEDULE_COMPRESS_DAYS , '
||' NUMBER1 '
||' FROM MSC_SUPPLIES ms,'
||' MSC_SYSTEM_ITEMS msi'
||' WHERE ms.plan_id = :PLAN_ID'
||' AND ms.sr_instance_id = :pINSTANCE_ID'
||' AND msi.organization_id = ms.organization_id'
||' AND msi.inventory_item_id = ms.inventory_item_id'
||' AND msi.sr_instance_id = ms.sr_instance_id'
||' AND msi.plan_id = ms.plan_id'
||' and trunc(ms.NEW_SCHEDULE_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
||' AND NOT EXISTS (select 1 from msc_system_items msi1 , msc_trading_partners mtp'
||' where msi1.inventory_item_id = ms.inventory_item_id '
||' and msi1.organization_id = ms.organization_id '
||' and msi1.plan_id = ms.plan_id '
||' AND msi1.sr_instance_id = ms.sr_instance_id '
||' and nvl(msi1.release_time_fence_code,-1) = 7 '
||' and mtp.sr_tp_id = msi1.organization_id '
||' and mtp.sr_instance_id = msi1.sr_instance_id '
||' and mtp.partner_type=3 '
||' and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))'
||' AND ms.order_type = 5 '|| lv_is_supp_null;
lv_sql_stmt1 := lv_sql_stmt1 || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
||' where mic.inventory_item_id = msi.inventory_item_id'
||' and mic.organization_id = msi.organization_id'
||' and mic.sr_instance_id = msi.sr_instance_id'
||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
||' and mic.category_set_id = mcs.category_set_id'
||' and mcs.sr_instance_id = mic.sr_instance_id'
||' and mcs.DEFAULT_FLAG = 1)';
' INSERT INTO MRP_SYSTEM_ITEMS'||v_dblink
||' (INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' COMPILE_DESIGNATOR ,'
||' LAST_UPDATE_DATE ,'
||' LAST_UPDATED_BY ,'
||' CREATION_DATE ,'
||' CREATED_BY ,'
||' INVENTORY_TYPE ,'
||' MRP_PLANNING_CODE ,'
||' INVENTORY_PLANNING_CODE ,'
||' LOW_LEVEL_CODE ,'
||' FULL_LEAD_TIME ,'
||' UOM_CODE ,'
||' BUILD_IN_WIP_FLAG ,'
||' PURCHASING_ENABLED_FLAG ,'
||' PLANNING_MAKE_BUY_CODE ,'
||' REPETITIVE_TYPE ,'
||' LOT_CONTROL_CODE ,'
||' ROUNDING_CONTROL_TYPE ,'
||' CALCULATE_ATP ,'
||' END_ASSEMBLY_PEGGING ,'
||' NETTABLE_INVENTORY_QUANTITY ,'
||' NONNETTABLE_INVENTORY_QUANTITY ,'
||' ENGINEERING_ITEM_FLAG ,'
||' SAFETY_STOCK_CODE ,'
||' PREPROCESSING_LEAD_TIME ,'
||' POSTPROCESSING_LEAD_TIME ,'
||' CUMULATIVE_TOTAL_LEAD_TIME ,'
||' CUM_MANUFACTURING_LEAD_TIME ,'
||' LAST_UPDATE_LOGIN ,'
||' FIXED_LEAD_TIME ,'
||' VARIABLE_LEAD_TIME ,'
||' STANDARD_COST ,'
||' WIP_SUPPLY_TYPE ,'
||' OVERRUN_PERCENTAGE ,'
||' ACCEPTABLE_RATE_INCREASE ,'
||' ACCEPTABLE_RATE_DECREASE ,'
||' SAFETY_STOCK_PERCENT ,'
||' SAFETY_STOCK_BUCKET_DAYS ,'
||' SAFETY_STOCK_QUANTITY ,'
||' DESCRIPTION ,'
||' CATEGORY_ID ,'
||' BUYER_ID ,'
||' BUYER_NAME ,'
||' PLANNER_CODE ,'
||' ABC_CLASS ,'
||' REVISION ,'
||' FIXED_DAYS_SUPPLY ,'
||' FIXED_ORDER_QUANTITY ,'
||' FIXED_LOT_MULTIPLIER ,'
||' MINIMUM_ORDER_QUANTITY ,'
||' MAXIMUM_ORDER_QUANTITY ,'
||' PLANNING_TIME_FENCE_DAYS ,'
-- ||' PLANNING_TIME_FENCE_DATE ,'
||' DEMAND_TIME_FENCE_DAYS ,'
||' INVENTORY_USE_UP_DATE ,'
||' ACCEPTABLE_EARLY_DELIVERY ,'
||' PLANNER_STATUS_CODE ,'
||' SHRINKAGE_RATE ,'
||' EXCEPTION_SHORTAGE_DAYS ,'
||' EXCEPTION_EXCESS_DAYS ,'
||' EXCEPTION_REP_VARIANCE_DAYS ,'
||' EXCEPTION_OVERPROMISED_DAYS ,'
||' PLANNING_EXCEPTION_SET ,'
||' EXCESS_QUANTITY ,'
||' REPETITIVE_VARIANCE ,'
||' BASE_ITEM_ID ,'
||' ATO_FORECAST_CONTROL ,'
||' EXCEPTION_CODE ,'
||' PROGRAM_UPDATE_DATE ,'
||' REQUEST_ID ,'
||' PROGRAM_APPLICATION_ID ,'
||' PROGRAM_ID ,'
||' DEMAND_TIME_FENCE_DATE ,'
||' IN_SOURCE_PLAN ,'
||' BOM_ITEM_TYPE ,'
||' FULL_PEGGING, '
||' ORGANIZATION_CODE )'
-- ||' EFFECTIVITY_CONTROL ) '
||'SELECT '
||' SR_INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' :pdesignator,'
||' LAST_UPDATE_DATE ,'
||' LAST_UPDATED_BY ,'
||' CREATION_DATE ,'
||' CREATED_BY ,'
||' 1,'
||' MRP_PLANNING_CODE ,'
||' INVENTORY_PLANNING_CODE ,'
||' decode(LOW_LEVEL_CODE,null,1,LOW_LEVEL_CODE),'
||' FULL_LEAD_TIME ,'
||' UOM_CODE ,'
||' BUILD_IN_WIP_FLAG ,'
||' PURCHASING_ENABLED_FLAG ,'
||' PLANNING_MAKE_BUY_CODE ,'
||' REPETITIVE_TYPE ,'
||' LOT_CONTROL_CODE ,'
||' ROUNDING_CONTROL_TYPE ,'
||' CALCULATE_ATP ,'
||' decode(END_ASSEMBLY_PEGGING,null,1,END_ASSEMBLY_PEGGING) ,'
||' decode(NETTABLE_INVENTORY_QUANTITY,null,0,NETTABLE_INVENTORY_QUANTITY ) ,'
||' decode(NONNETTABLE_INVENTORY_QUANTITY,NULL,0,NONNETTABLE_INVENTORY_QUANTITY ),'
||' ENGINEERING_ITEM_FLAG ,'
||' SAFETY_STOCK_CODE ,'
||' PREPROCESSING_LEAD_TIME ,'
||' POSTPROCESSING_LEAD_TIME ,'
||' CUMULATIVE_TOTAL_LEAD_TIME ,'
||' CUM_MANUFACTURING_LEAD_TIME ,'
||' LAST_UPDATE_LOGIN ,'
||' FIXED_LEAD_TIME ,'
||' VARIABLE_LEAD_TIME ,'
||' STANDARD_COST ,'
||' WIP_SUPPLY_TYPE ,'
||' OVERRUN_PERCENTAGE ,'
||' ACCEPTABLE_RATE_INCREASE ,'
||' ACCEPTABLE_RATE_DECREASE ,'
||' SAFETY_STOCK_PERCENT ,'
||' SAFETY_STOCK_BUCKET_DAYS ,'
||' FIXED_SAFETY_STOCK_QTY ,'
||' DESCRIPTION ,'
||' SR_CATEGORY_ID ,'
||' BUYER_ID ,'
||' BUYER_NAME ,'
||' PLANNER_CODE ,'
||' ABC_CLASS ,'
||' REVISION ,'
||' FIXED_DAYS_SUPPLY ,'
||' FIXED_ORDER_QUANTITY ,'
||' FIXED_LOT_MULTIPLIER ,'
||' MINIMUM_ORDER_QUANTITY ,'
||' MAXIMUM_ORDER_QUANTITY ,'
||' PLANNING_TIME_FENCE_DAYS ,'
-- ||' PLANNING_TIME_FENCE_DATE ,'
||' DEMAND_TIME_FENCE_DAYS ,'
||' INVENTORY_USE_UP_DATE ,'
||' ACCEPTABLE_EARLY_DELIVERY ,'
||' PLANNER_STATUS_CODE ,'
||' SHRINKAGE_RATE ,'
||' EXCEPTION_SHORTAGE_DAYS ,'
||' EXCEPTION_EXCESS_DAYS ,'
||' EXCEPTION_REP_VARIANCE_DAYS ,'
||' EXCEPTION_OVERPROMISED_DAYS ,'
||' PLANNING_EXCEPTION_SET ,'
||' EXCESS_QUANTITY ,'
||' REPETITIVE_VARIANCE ,'
||' BASE_ITEM_ID ,'
||' ATO_FORECAST_CONTROL ,'
||' EXCEPTION_CODE ,'
||' PROGRAM_UPDATE_DATE ,'
||' REQUEST_ID ,'
||' PROGRAM_APPLICATION_ID ,'
||' PROGRAM_ID ,'
||' DEMAND_TIME_FENCE_DATE ,'
||' IN_SOURCE_PLAN ,'
||' BOM_ITEM_TYPE ,'
||' FULL_PEGGING, '
||' substr(ORGANIZATION_CODE,5,3) '
-- ||' EFFECTIVITY_CONTROL '
||' FROM MSC_SYSTEM_ITEMS msi'
||' WHERE msi.plan_id = :PLAN_ID'
||' AND msi.sr_instance_id = :pINSTANCE_ID'
-- check orgs which are in the plan only bug#7016427 **hbinjola**
||' AND msi.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
||' where mic.inventory_item_id = msi.inventory_item_id'
||' and mic.organization_id = msi.organization_id'
||' and mic.sr_instance_id = msi.sr_instance_id'
||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
||' and mic.category_set_id = mcs.category_set_id'
||' and mcs.sr_instance_id = mic.sr_instance_id'
||' and mcs.DEFAULT_FLAG = 1)';
' INSERT INTO MRP_GROSS_REQUIREMENTS'||v_dblink
||' (DEMAND_ID ,'
||' LAST_UPDATE_DATE ,'
||' LAST_UPDATED_BY ,'
||' CREATION_DATE ,'
||' CREATED_BY ,'
||' LAST_UPDATE_LOGIN ,'
||' INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' COMPILE_DESIGNATOR ,'
||' USING_ASSEMBLY_ITEM_ID ,'
||' USING_ASSEMBLY_DEMAND_DATE ,'
||' USING_REQUIREMENTS_QUANTITY ,'
||' ASSEMBLY_DEMAND_COMP_DATE ,'
||' DEMAND_TYPE ,'
||' ORIGINATION_TYPE ,'
||' DISPOSITION_ID ,'
||' DAILY_DEMAND_RATE ,'
||' REQUEST_ID ,'
||' RESERVE_QUANTITY ,'
||' SOURCE_ORGANIZATION_ID ,'
||' UPDATED ,'
||' STATUS ,'
||' APPLIED ,'
||' DEMAND_CLASS ,'
||' FIRM_QUANTITY ,'
||' FIRM_DATE ,'
||' OLD_DEMAND_QUANTITY ,'
||' DEMAND_SCHEDULE_NAME ,'
||' OLD_DEMAND_DATE ,'
||' PROJECT_ID ,'
||' TASK_ID ,'
||' PLANNING_GROUP )'
||' SELECT '
||' MRP_GROSS_REQUIREMENTS_S.nextval'||v_dblink||' , '
||' md.LAST_UPDATE_DATE ,'
||' md.LAST_UPDATED_BY ,'
||' md.CREATION_DATE ,'
||' md.CREATED_BY ,'
||' md.LAST_UPDATED_BY ,'
||' msi.SR_INVENTORY_ITEM_ID ,'
||' md.ORGANIZATION_ID ,'
||' :pdesignator ,'
||' mtil.SR_INVENTORY_ITEM_ID ,'
||' trunc(md.USING_ASSEMBLY_DEMAND_DATE) ,'
||' md.USING_REQUIREMENT_QUANTITY ,'
||' trunc(md.ASSEMBLY_DEMAND_COMP_DATE) ,'
||' md.DEMAND_TYPE ,'
||' decode(md.ORIGINATION_TYPE,29,7 '
||' ,30,6, '
||' md.ORIGINATION_TYPE) ,'
||' NULL ,'
||' md.DAILY_DEMAND_RATE ,'
||' NULL ,'
||' md.RESERVED_QUANTITY ,'
||' md.SOURCE_ORGANIZATION_ID ,'
||' md.UPDATED ,'
||' md.STATUS ,'
||' md.APPLIED ,'
||' md.DEMAND_CLASS ,'
||' md.FIRM_QUANTITY ,'
||' md.FIRM_DATE ,'
||' md.OLD_DEMAND_QUANTITY ,'
||' NULL ,'
||' md.OLD_DEMAND_DATE ,'
||' md.PROJECT_ID ,'
||' md.TASK_ID ,'
||' md.PLANNING_GROUP '
||' FROM MSC_DEMANDS md ,'
||' MSC_SYSTEM_ITEMS msi ,'
||' MSC_ITEM_ID_LID mtil '
||' WHERE md.plan_id = :PLAN_ID '
||' AND md.sr_instance_id = :pINSTANCE_ID '
||' and trunc(md.USING_ASSEMBLY_DEMAND_DATE) BETWEEN (:pHORIZON_START_DATE) and (:pHORIZON_END_DATE)'
||' AND msi.organization_id = md.organization_id '
||' AND msi.inventory_item_id = md.inventory_item_id '
||' AND msi.sr_instance_id = md.sr_instance_id '
||' AND msi.plan_id = md.plan_id '
||' AND mtil.sr_instance_id = md.sr_instance_id '
||' AND mtil.inventory_item_id = md.USING_ASSEMBLY_ITEM_ID'
||' AND NOT EXISTS (select 1 from msc_system_items msi1'
||' where msi1.inventory_item_id = md.USING_ASSEMBLY_ITEM_ID '
||' and msi1.organization_id = md.organization_id '
||' and msi1.plan_id = md.plan_id '
||' AND msi1.sr_instance_id = md.sr_instance_id '
||' and nvl(msi1.release_time_fence_code,-1) = 7)';
lv_sql_stmt := lv_sql_stmt || ' and exists (select 1 from msc_item_categories mic, msc_category_sets mcs'
||' where mic.inventory_item_id = msi.inventory_item_id'
||' and mic.organization_id = msi.organization_id'
||' and mic.sr_instance_id = msi.sr_instance_id'
||' and mic.SR_CATEGORY_ID = :pCATEGORY_ID'
||' and mic.category_set_id = mcs.category_set_id'
||' and mcs.sr_instance_id = mic.sr_instance_id'
||' and mcs.DEFAULT_FLAG = 1)';
select organization_id
into lv_organization_id
from msc_plans
where sr_instance_id = pINSTANCE_ID
and compile_designator = pDESIGNATOR;
'DELETE FROM MRP_SCHEDULE_DATES'||v_dblink||' MSD'
||' WHERE MSD.SCHEDULE_DESIGNATOR = :pDESIGNATOR '
||' AND MSD.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
v_buff := 'Deleted MRP_SCHEDULE_DATES for Plan : '||pDESIGNATOR;
'DELETE FROM MRP_SCHEDULE_ITEMS'||v_dblink||' MSI'
||' WHERE MSI.SCHEDULE_DESIGNATOR = :pDESIGNATOR '
||' AND MSI.ORGANIZATION_ID IN (SELECT MPOV.PLANNED_ORGANIZATION'
||' FROM MSC_PLAN_ORGANIZATIONS_V MPOV'
||' WHERE MPOV.SR_INSTANCE_ID = :pINSTANCE_ID'
||' AND MPOV.COMPILE_DESIGNATOR = :pDESIGNATOR)';
v_buff := 'Deleted MRP_SCHEDULE_ITEMS for Plan : '||pDESIGNATOR;
SELECT FND_GLOBAL.USER_ID
INTO lv_user_id
FROM dual;
lv_sql_stmt:= ' INSERT INTO MRP_SCHEDULE_ITEMS'||v_dblink
||' (INVENTORY_ITEM_ID,'
||' ORGANIZATION_ID,'
||' SCHEDULE_DESIGNATOR,'
||' LAST_UPDATE_DATE,'
||' LAST_UPDATED_BY,'
||' creation_date,'
||' created_by,'
||' last_update_login,'
||' MPS_EXPLOSION_LEVEL)'
||' SELECT DISTINCT dates.inventory_item_id,'
||' dates.organization_id,'
||' :compile_desig,'
||' SYSDATE,'
||' :user_id,'
||' SYSDATE,'
||' :user_id,'
||' -1,'
||' 100 /* this has no meaning for an MRP part */'
||' FROM mrp_schedule_dates'||v_dblink||' dates,'
||' mrp_system_items'||v_dblink||' data,'
||' mrp_plan_organizations_v'||v_dblink||' mpo'
||' WHERE NOT EXISTS'
||' (SELECT inventory_item_id'
||' FROM mrp_schedule_items'||v_dblink||' items'
||' WHERE items.organization_id ='
||' mpo.planned_organization'
||' AND items.inventory_item_id ='
||' dates.inventory_item_id'
||' AND items.schedule_designator ='
||' mpo.compile_designator)'
||' AND dates.organization_id = data.organization_id'
||' AND dates.schedule_designator = data.compile_designator'
||' AND dates.inventory_item_id = data.inventory_item_id'
||' AND data.mrp_planning_code IN'
||' (4, 8)'
||' AND data.organization_id = mpo.planned_organization'
||' AND data.compile_designator = mpo.compile_designator'
||' AND mpo.organization_id = :org_id'
||' AND mpo.compile_designator = :compile_desig';