[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM MSC_EXCEPTION_DETAILS WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEPARTMENT_ID,
RESOURCE_ID,
EXCEPTION_DETAIL_ID,
EXCEPTION_TYPE,
QUANTITY,
NUMBER2,
DATE1,
DATE2,
NUMBER1,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
DepartmentID,
-1,
MSC_EXCEPTION_DETAILS_S.NEXTVAL,
MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
TO_DATE(PeriodStart, 'YYYY-MM-DD'),
TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
AND msc_demands.origination_type=81
AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
then
msc_demands.demand_id
else
null
end,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
ELSE
NULL
END,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
ELSE
NULL
END,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
ELSE
NULL
END,
SYSDATE,
'-1',
SYSDATE,
'-1'
FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=msc_demands.sr_instance_id
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
AND msc_demands.origination_type=81
AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001' || ' : ' || SQLERRM;
INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEPARTMENT_ID,
RESOURCE_ID,
EXCEPTION_DETAIL_ID,
EXCEPTION_TYPE,
QUANTITY,
NUMBER2,
DATE1,
DATE2,
NUMBER1,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
DepartmentID,
-1,
MSC_EXCEPTION_DETAILS_S.NEXTVAL,
MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
TO_DATE(PeriodStart, 'YYYY-MM-DD'),
TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
case WHEN MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Demand'
AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
AND TO_DATE(MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID= -1
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
AND msc_demands.origination_type=81
AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1
-- CUSTOMER_SITE_ID:
AND (
( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
AND (SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) ) = msc_demands.CUSTOMER_SITE_ID )
-- CUSTOMER_ID:
AND (
MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null )
AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
AND ( SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) = msc_demands.CUSTOMER_ID
)
then
msc_demands.demand_id
else
null
end,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
ELSE
NULL
END,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)+1) -- CUSTOMER_SITE_ID
ELSE
NULL
END,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1) <> 0 )
THEN SUBSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID, 1, INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':',1,1)-1) -- CUSTOMER_ID
ELSE
NULL
END,
SYSDATE,
'-1',
SYSDATE,
'-1'
FROM msc_demands, MSC_INT_ASCP_EXCEPTION_DETAILS
where MSC_INT_ASCP_EXCEPTION_DETAILS.ALERTCATEGORY = 'Demand'
AND msc_demands.plan_id=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
AND TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400 =msc_demands.dmd_satisfied_date
AND MSC_INT_ASCP_EXCEPTION_DETAILS.INSTANCE_ID=-1
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID=msc_demands.inventory_item_id
AND MSC_INT_ASCP_EXCEPTION_DETAILS.ORGANIZATION_ID=msc_demands.organization_id
AND msc_demands.origination_type=81
AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id = -1;
g_ErrorCode := 'ERROR_UPDATE_ALERTS_001001' || ' : ' || SQLERRM;
INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEPARTMENT_ID,
RESOURCE_ID,
EXCEPTION_DETAIL_ID,
EXCEPTION_TYPE,
QUANTITY,
NUMBER2,
DATE1,
DATE2,
NUMBER1,
ZONE_ID,
SUPPLIER_SITE_ID,
SUPPLIER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
MSC_PLANS.sr_instance_id,
MSC_PLANS.organization_id,
MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID,
-1,
-1,
MSC_EXCEPTION_DETAILS_S.NEXTVAL,
MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
TO_DATE(PeriodStart, 'YYYY-MM-DD'),
TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
case when MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Supply'
AND msc_supplies.Plan_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID
AND msc_supplies.ORGANIZATION_ID=MSC_PLANS.organization_id -- no org id in Supply category
AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
AND msc_supplies.SR_INSTANCE_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
AND msc_supplies.NEW_SCHEDULE_DATE= TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
AND msc_supplies.order_type=1
AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1
then
msc_supplies.transaction_id
else
null
end,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
ELSE
NULL
END,
MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id, --SupplierSiteId
MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId, --SupplierId
SYSDATE,
'-1',
SYSDATE,
'-1'
FROM msc_supplies, MSC_INT_ASCP_EXCEPTION_DETAILS, MSC_PLANS
WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory='Supply'
AND MSC_INT_ASCP_EXCEPTION_DETAILS.PLAN_ID=PlanIdVar
AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=msc_supplies.Plan_ID
AND MSC_INT_ASCP_EXCEPTION_DETAILS.plan_id=MSC_PLANS.PLAN_ID
AND msc_supplies.organization_id = MSC_PLANS.organization_id -- use owning org id to suppress extra records
AND msc_supplies.SUPPLIER_SITE_ID =MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id
AND msc_supplies.SUPPLIER_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
AND msc_supplies.SR_INSTANCE_ID = MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id
AND msc_supplies.INVENTORY_ITEM_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
AND msc_supplies.NEW_SCHEDULE_DATE=TO_DATE( MSC_INT_ASCP_EXCEPTION_DETAILS.PERIODEND, 'YYYY-MM-DD')-1/86400
AND (msc_supplies.order_type=1) AND (msc_supplies.FIRM_PLANNED_TYPE=2)
AND MSC_INT_ASCP_EXCEPTION_DETAILS.organization_id <> -1;
g_ErrorCode := 'ERROR_UPDATE_ALERTS_001002' || ' : ' || SQLERRM;
INSERT INTO MSC_EXCEPTION_DETAILS (PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEPARTMENT_ID,
RESOURCE_ID,
EXCEPTION_DETAIL_ID,
EXCEPTION_TYPE,
QUANTITY,
NUMBER2,
DATE1,
DATE2,
NUMBER1,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
-- inventory_item_id
case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
) > 0 ) )
then -1
else (
case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )
then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID -- Inventory, Transportation and Others
else MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentID end
)
end,
-- department_id
case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
) > 0 ))
then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
else -1
end,
-- resource_id
case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
) > 0 ) )
then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
else -1
end,
MSC_EXCEPTION_DETAILS_S.NEXTVAL,
MSC_INT_ASCP_EXCEPTION_DETAILS.ViolationCode, -- Used to be: 28,
MSC_INT_ASCP_EXCEPTION_DETAILS.Quantity,
MSC_INT_ASCP_EXCEPTION_DETAILS.Percentage,
TO_DATE(PeriodStart, 'YYYY-MM-DD'),
TO_DATE(PeriodEnd, 'YYYY-MM-DD')-1/86400,
NULL,
CASE WHEN ( MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID is not null ) AND ( INSTR(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID,':') = 0 )
THEN TO_NUMBER(MSC_INT_ASCP_EXCEPTION_DETAILS.Zone_ID)
ELSE
NULL
END,
null, --customerSiteId
null, --customerId
SYSDATE,
'-1',
SYSDATE,
'-1'
FROM MSC_INT_ASCP_EXCEPTION_DETAILS
WHERE MSC_INT_ASCP_EXCEPTION_DETAILS.Plan_Id = PlanIdVar
AND MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id <> -1
AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Demand'
AND MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Supply';
g_ErrorCode := 'ERROR_UPDATE_ALERTS_001003' || ' : ' || SQLERRM;
DELETE FROM MSC_SYSTEM_ITEMS WHERE PLAN_ID=PlanIdVar;
--Now update the temp table in order to get Number Of Sources measure for each item
-- delete records from MSC_INT_APCC_ITEM_SOURCE table for the given PlanId, if any
--BEGIN
--DELETE FROM MSC_INT_APCC_ITEM_SOURCE WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_SYSTEM_ITEMS (
PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SR_INSTANCE_ID,
SR_INVENTORY_ITEM_ID,
ITEM_NAME,
LOTS_EXPIRATION,
LOT_CONTROL_CODE,
SHRINKAGE_RATE,
FIXED_DAYS_SUPPLY,
FIXED_ORDER_QUANTITY,
FIXED_LOT_MULTIPLIER,
MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY,
ROUNDING_CONTROL_TYPE,
PLANNING_TIME_FENCE_DAYS,
PLANNING_TIME_FENCE_DATE,
DEMAND_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DATE,
DESCRIPTION,
RELEASE_TIME_FENCE_CODE,
RELEASE_TIME_FENCE_DAYS,
IN_SOURCE_PLAN,
REVISION,
SR_CATEGORY_ID,
ABC_CLASS,
CATEGORY_NAME,
MRP_PLANNING_CODE,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
POSTPROCESSING_LEAD_TIME,
FULL_LEAD_TIME,
CUMULATIVE_TOTAL_LEAD_TIME,
CUM_MANUFACTURING_LEAD_TIME,
UOM_CODE,
UNIT_WEIGHT,
UNIT_VOLUME,
WEIGHT_UOM,
VOLUME_UOM,
PRODUCT_FAMILY_ID,
ATP_RULE_ID,
ATP_COMPONENTS_FLAG,
BUILD_IN_WIP_FLAG,
PURCHASING_ENABLED_FLAG,
PLANNING_MAKE_BUY_CODE,
REPETITIVE_TYPE,
REPETITIVE_VARIANCE,
STANDARD_COST,
CARRYING_COST,
ORDER_COST,
MATERIAL_COST,
DMD_LATENESS_COST,
RESOURCE_COST,
SS_PENALTY_COST,
SUPPLIER_CAP_OVERUTIL_COST,
LIST_PRICE,
AVERAGE_DISCOUNT,
ENGINEERING_ITEM_FLAG,
WIP_SUPPLY_TYPE,
SAFETY_STOCK_CODE,
SAFETY_STOCK_PERCENT,
SAFETY_STOCK_BUCKET_DAYS,
INVENTORY_USE_UP_DATE,
BUYER_NAME,
PLANNER_CODE,
PLANNING_EXCEPTION_SET,
EXCESS_QUANTITY,
EXCEPTION_SHORTAGE_DAYS,
EXCEPTION_EXCESS_DAYS,
EXCEPTION_OVERPROMISED_DAYS,
EXCEPTION_CODE,
BOM_ITEM_TYPE,
ATO_FORECAST_CONTROL,
EFFECTIVITY_CONTROL,
ORGANIZATION_CODE,
ACCEPTABLE_RATE_INCREASE,
ACCEPTABLE_RATE_DECREASE,
EXCEPTION_REP_VARIANCE_DAYS,
OVERRUN_PERCENTAGE,
INVENTORY_PLANNING_CODE,
ACCEPTABLE_EARLY_DELIVERY,
CALCULATE_ATP,
END_ASSEMBLY_PEGGING_FLAG,
END_ASSEMBLY_PEGGING,
FULL_PEGGING,
INVENTORY_ITEM_FLAG,
SOURCE_ORG_ID,
BASE_ITEM_ID,
ABC_CLASS_NAME,
FIXED_SAFETY_STOCK_QTY,
PRIMARY_SUPPLIER_ID,
ATP_FLAG,
LOW_LEVEL_CODE,
PLANNER_STATUS_CODE,
NETTABLE_INVENTORY_QUANTITY,
NONNETTABLE_INVENTORY_QUANTITY,
REFRESH_NUMBER,
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,
REVISION_QTY_CONTROL_CODE,
EXPENSE_ACCOUNT,
INVENTORY_ASSET_FLAG,
BUYER_ID,
REPETITIVE_PLANNING_FLAG,
PICK_COMPONENTS_FLAG,
SERVICE_LEVEL,
REPLENISH_TO_ORDER_FLAG,
PIP_FLAG,
YIELD_CONV_FACTOR,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
NEW_ATP_FLAG,
SOURCE_TYPE,
SUBSTITUTION_WINDOW,
CREATE_SUPPLY_FLAG,
REORDER_POINT,
AVERAGE_ANNUAL_DEMAND,
ECONOMIC_ORDER_QUANTITY,
SERIAL_NUMBER_CONTROL_CODE,
CONVERGENCE,
DIVERGENCE,
CONTINOUS_TRANSFER,
CRITICAL_COMPONENT_FLAG,
REDUCE_MPS,
CONSIGNED_FLAG,
VMI_MINIMUM_UNITS,
VMI_MINIMUM_DAYS,
VMI_MAXIMUM_UNITS,
VMI_MAXIMUM_DAYS,
AVERAGE_DAILY_DEMAND,
VMI_FIXED_ORDER_QUANTITY,
SO_AUTHORIZATION_FLAG,
VMI_FORECAST_TYPE,
FORECAST_HORIZON,
ASN_AUTOEXPIRE_FLAG,
VMI_REFRESH_FLAG,
BUDGET_CONSTRAINED,
MAX_QUANTITY,
MAX_QUANTITY_DOS,
DAYS_TGT_INV_WINDOW,
DAYS_MAX_INV_WINDOW,
DAYS_TGT_INV_SUPPLY,
DAYS_MAX_INV_SUPPLY,
DRP_PLANNED,
AGGREGATE_TIME_FENCE_DATE,
INFERRED_CRITICAL_FLAG,
SS_WINDOW_SIZE,
ITEM_CREATION_DATE,
PLANNING_TIME_FENCE_CODE,
SHORTAGE_TYPE,
EXCESS_TYPE,
PEGGING_DEMAND_WINDOW_DAYS,
PEGGING_SUPPLY_WINDOW_DAYS,
UNSATISFIED_DEMAND_FACTOR,
SAFETY_LEAD_TIME,
-- COUNT_OF_SOURCES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
SELECT DISTINCT PlanIdVar,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SR_INSTANCE_ID,
SR_INVENTORY_ITEM_ID,
ITEM_NAME,
LOTS_EXPIRATION,
LOT_CONTROL_CODE,
SHRINKAGE_RATE,
FIXED_DAYS_SUPPLY,
FIXED_ORDER_QUANTITY,
FIXED_LOT_MULTIPLIER,
MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY,
ROUNDING_CONTROL_TYPE,
PLANNING_TIME_FENCE_DAYS,
PLANNING_TIME_FENCE_DATE,
DEMAND_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DATE,
DESCRIPTION,
RELEASE_TIME_FENCE_CODE,
RELEASE_TIME_FENCE_DAYS,
IN_SOURCE_PLAN,
REVISION,
SR_CATEGORY_ID,
ABC_CLASS,
CATEGORY_NAME,
MRP_PLANNING_CODE,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
POSTPROCESSING_LEAD_TIME,
FULL_LEAD_TIME,
CUMULATIVE_TOTAL_LEAD_TIME,
CUM_MANUFACTURING_LEAD_TIME,
UOM_CODE,
UNIT_WEIGHT,
UNIT_VOLUME,
WEIGHT_UOM,
VOLUME_UOM,
PRODUCT_FAMILY_ID,
ATP_RULE_ID,
ATP_COMPONENTS_FLAG,
BUILD_IN_WIP_FLAG,
PURCHASING_ENABLED_FLAG,
PLANNING_MAKE_BUY_CODE,
REPETITIVE_TYPE,
REPETITIVE_VARIANCE,
STANDARD_COST,
CARRYING_COST,
ORDER_COST,
MATERIAL_COST,
DMD_LATENESS_COST,
RESOURCE_COST,
SS_PENALTY_COST,
SUPPLIER_CAP_OVERUTIL_COST,
LIST_PRICE,
AVERAGE_DISCOUNT,
ENGINEERING_ITEM_FLAG,
WIP_SUPPLY_TYPE,
SAFETY_STOCK_CODE,
SAFETY_STOCK_PERCENT,
SAFETY_STOCK_BUCKET_DAYS,
INVENTORY_USE_UP_DATE,
BUYER_NAME,
PLANNER_CODE,
PLANNING_EXCEPTION_SET,
EXCESS_QUANTITY,
EXCEPTION_SHORTAGE_DAYS,
EXCEPTION_EXCESS_DAYS,
EXCEPTION_OVERPROMISED_DAYS,
EXCEPTION_CODE,
BOM_ITEM_TYPE,
ATO_FORECAST_CONTROL,
EFFECTIVITY_CONTROL,
ORGANIZATION_CODE,
ACCEPTABLE_RATE_INCREASE,
ACCEPTABLE_RATE_DECREASE,
EXCEPTION_REP_VARIANCE_DAYS,
OVERRUN_PERCENTAGE,
INVENTORY_PLANNING_CODE,
ACCEPTABLE_EARLY_DELIVERY,
CALCULATE_ATP,
END_ASSEMBLY_PEGGING_FLAG,
END_ASSEMBLY_PEGGING,
FULL_PEGGING,
INVENTORY_ITEM_FLAG,
SOURCE_ORG_ID,
BASE_ITEM_ID,
ABC_CLASS_NAME,
FIXED_SAFETY_STOCK_QTY,
PRIMARY_SUPPLIER_ID,
ATP_FLAG,
LOW_LEVEL_CODE,
PLANNER_STATUS_CODE,
NETTABLE_INVENTORY_QUANTITY,
NONNETTABLE_INVENTORY_QUANTITY,
REFRESH_NUMBER,
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,
REVISION_QTY_CONTROL_CODE,
EXPENSE_ACCOUNT,
INVENTORY_ASSET_FLAG,
BUYER_ID,
REPETITIVE_PLANNING_FLAG,
PICK_COMPONENTS_FLAG,
SERVICE_LEVEL,
REPLENISH_TO_ORDER_FLAG,
PIP_FLAG,
YIELD_CONV_FACTOR,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
NEW_ATP_FLAG,
SOURCE_TYPE,
SUBSTITUTION_WINDOW,
CREATE_SUPPLY_FLAG,
REORDER_POINT,
AVERAGE_ANNUAL_DEMAND,
ECONOMIC_ORDER_QUANTITY,
SERIAL_NUMBER_CONTROL_CODE,
CONVERGENCE,
DIVERGENCE,
CONTINOUS_TRANSFER,
CRITICAL_COMPONENT_FLAG,
REDUCE_MPS,
CONSIGNED_FLAG,
VMI_MINIMUM_UNITS,
VMI_MINIMUM_DAYS,
VMI_MAXIMUM_UNITS,
VMI_MAXIMUM_DAYS,
AVERAGE_DAILY_DEMAND,
VMI_FIXED_ORDER_QUANTITY,
SO_AUTHORIZATION_FLAG,
VMI_FORECAST_TYPE,
FORECAST_HORIZON,
ASN_AUTOEXPIRE_FLAG,
VMI_REFRESH_FLAG,
BUDGET_CONSTRAINED,
MAX_QUANTITY,
MAX_QUANTITY_DOS,
DAYS_TGT_INV_WINDOW,
DAYS_MAX_INV_WINDOW,
DAYS_TGT_INV_SUPPLY,
DAYS_MAX_INV_SUPPLY,
DRP_PLANNED,
AGGREGATE_TIME_FENCE_DATE,
INFERRED_CRITICAL_FLAG,
SS_WINDOW_SIZE,
ITEM_CREATION_DATE,
PLANNING_TIME_FENCE_CODE,
SHORTAGE_TYPE,
EXCESS_TYPE,
PEGGING_DEMAND_WINDOW_DAYS,
PEGGING_SUPPLY_WINDOW_DAYS,
UNSATISFIED_DEMAND_FACTOR,
SAFETY_LEAD_TIME,
-- number of sources ( new measure )
--(select COUNT( DISTINCT MSC_INT_APCC_ITEM_SOURCE.source_organization_id )
-- from MSC_INT_APCC_ITEM_SOURCE
-- WHERE PlanIdVar = MSC_INT_APCC_ITEM_SOURCE.plan_ID AND
-- MSC_SYSTEM_ITEMS.SR_INSTANCE_ID = MSC_INT_APCC_ITEM_SOURCE.sr_instance_id AND
-- MSC_SYSTEM_ITEMS.ORGANIZATION_ID = MSC_INT_APCC_ITEM_SOURCE.organization_id AND
-- MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID = MSC_INT_APCC_ITEM_SOURCE.inventory_item_id
-- ), -- must specify table name before columns to avoid duplicate count
SYSDATE,
'-1',
SYSDATE,
'-1',
-- last update login :
-- we do not have the userId here ( no validation should be done at this point as required earlier) :
'-1'
FROM MSC_SYSTEM_ITEMS LEFT JOIN MSC_INT_ASCP_INVENTORY ON
MSC_INT_ASCP_INVENTORY.OrganizationID = MSC_SYSTEM_ITEMS.ORGANIZATION_ID AND
MSC_INT_ASCP_INVENTORY.InstanceID = MSC_SYSTEM_ITEMS.SR_INSTANCE_ID AND
MSC_INT_ASCP_INVENTORY.ItemId = MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID
WHERE MSC_SYSTEM_ITEMS.PLAN_ID=-1 AND MSC_INT_ASCP_INVENTORY.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_MSC_SYSTEM_ITEMS_001002' || ' : ' || SQLERRM;
DELETE FROM MSC_PLAN_BUCKETS WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_PLAN_BUCKETS (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
BUCKET_INDEX,
CURR_FLAG,
BKT_START_DATE,
BKT_END_DATE,
DAYS_IN_BKT,
BUCKET_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT PlanIdVar, MSC_PLANS.ORGANIZATION_ID, MSC_PLANS.SR_INSTANCE_ID,
TO_NUMBER(BucketIndex), 1,
TO_DATE(BktStartDate,'YYYY-MM-DD'), TO_DATE(BktEndDate,'YYYY-MM-DD')-1/86400,
TO_NUMBER(DaysInBucket), TO_NUMBER(BucketType),
SYSDATE, '-1',
SYSDATE, '-1'
FROM MSC_INT_ASCP_EXPORT_BUCKETS, MSC_PLANS
WHERE PLANID=PlanIdVar AND
MSC_PLANS.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_PLAN_BUCKETS_001001' || ' : ' || SQLERRM;
DELETE FROM MSC_BIS_ORG_DETAIL WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_BIS_ORG_DETAIL (PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
DETAIL_DATE,
FACILITY_COST,
FACILITY_COST_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
PlanIdVar,
SR_INSTANCE_ID,
ORGANIZATION_ID,
TO_DATE(PERIOD_END,'YYYY-MM-DD'),
FACILITY_COST,
FACILITY_COST_TYPE,
SYSDATE, '-1',
SYSDATE, '-1', -1
FROM MSC_INT_APCC_ORG_DETAIL
WHERE PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_FACILITY_COSTS_001001' || ' : ' || SQLERRM;
DELETE FROM MSC_DEMANDS WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_DEMANDS (
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PLAN_ID,
SR_INSTANCE_ID,
DEMAND_ID,
ORIGINATION_TYPE,
USING_REQUIREMENT_QUANTITY,
QUANTITY_BY_DUE_DATE,
DMD_SATISFIED_DATE,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
SERVICE_LEVEL,
USING_ASSEMBLY_DEMAND_DATE,
USING_ASSEMBLY_ITEM_ID,
DEMAND_TYPE,
UNMET_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
CASE WHEN ( ZoneID is not null )
THEN -1
ELSE
OrganizationID
END,
ItemID,
PlanIdVar,
InstanceID,
MSC_DEMANDS_S.NEXTVAL,
81,
Demand,
Satisfied,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':') = 0 )
THEN TO_NUMBER(ZoneID)
ELSE
NULL
END,
CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':',1,1) <> 0 )
THEN SUBSTR(ZoneID, INSTR(ZoneID,':',1,1)+1) -- CUSTOMER_SITE_ID
ELSE
NULL
END,
CASE WHEN ( ZoneID is not null ) AND ( INSTR(ZoneID,':',1,1) <> 0 )
THEN SUBSTR(ZoneID, 1, INSTR(ZoneID,':',1,1)-1) -- CUSTOMER_ID
ELSE
NULL
END,
0,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
ItemID,
1,
Demand-Satisfied,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_DEMANDS
WHERE PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_DEMAND_001001' || ' : ' || SQLERRM;
INSERT INTO MSC_DEMANDS (
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PLAN_ID,
SR_INSTANCE_ID,
DEMAND_ID,
ORIGINATION_TYPE,
USING_REQUIREMENT_QUANTITY,
DMD_SATISFIED_DATE,
USING_ASSEMBLY_DEMAND_DATE,
USING_ASSEMBLY_ITEM_ID,
DEMAND_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
FromOrgID,
ItemID,
PlanIdVar,
InstanceID,
MSC_DEMANDS_S.NEXTVAL, --DEMAND_ID,
1, --ORIGINATION_TYPE,
Quantity, --BUGBUG: how to show Infinity ? --USING_REQUIREMENT_QUANTITY
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, --DMD_SATISFIED_DATE
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, --USING_ASSEMBLY_DEMAND_DATE
ItemID, --USING_ASSEMBLY_ITEM_ID
1, --DEMAND_TYPE
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_DEPENDENT_DEMAND
WHERE PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_DEPENDENT_DEMAND_001002' || ' : ' || SQLERRM;
INSERT INTO MSC_DEMANDS (ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PLAN_ID,
SR_INSTANCE_ID,
DEMAND_ID,
ORIGINATION_TYPE,
USING_REQUIREMENT_QUANTITY,
QUANTITY_BY_DUE_DATE,
DMD_SATISFIED_DATE,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
SERVICE_LEVEL,
USING_ASSEMBLY_DEMAND_DATE,
USING_ASSEMBLY_ITEM_ID,
DEMAND_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
FromOrgID,
ItemID,
PlanIdVar,
FromInstanceID,
MSC_DEMANDS_S.NEXTVAL,
82,
Quantity,
Quantity,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
NULL,
NULL,
NULL,
0,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
ItemID,
1,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_TRANSPORTATION
WHERE PLAN_ID=PlanIdVar AND
Subcategory <> 'Customer';
g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_TRANSPORTATION_001003' || ' : ' || SQLERRM;
DELETE FROM MSC_SUPPLIES WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_SUPPLIES (PLAN_ID,
TRANSACTION_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
ORDER_TYPE,
NEW_SCHEDULE_DATE,
NEW_ORDER_QUANTITY,
FIRM_PLANNED_TYPE,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT PlanIdVar, msc_supplies_s.nextval, organizationID,
instanceID, itemID, 1, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
supply, 2, SupplierID,
supplierSiteID,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_SUPPLY
WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_SUPPLY.CATEGORY='Supply';
g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_SUPPLY_001001' || ' : ' || SQLERRM;
INSERT INTO MSC_SUPPLIES (
PLAN_ID,
TRANSACTION_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
ORDER_TYPE,
NEW_SCHEDULE_DATE,
NEW_ORDER_QUANTITY,
FIRM_PLANNED_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SR_INSTANCE_ID,
SHIP_METHOD,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
PlanIdVar,
msc_supplies_s.nextval,
toOrgID,
toInstanceID,
itemID,
80,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
quantity,
2,
fromOrgID,
fromInstanceID,
transportMode,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_TRANSPORTATION
WHERE PLAN_ID=PlanIdVar AND
Subcategory <> 'Customer';
g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_ASCP_TRANSPORTATION_001002' || ' : ' || SQLERRM;
INSERT INTO MSC_SUPPLIES (PLAN_ID,
TRANSACTION_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
ORDER_TYPE,
NEW_SCHEDULE_DATE,
NEW_ORDER_QUANTITY,
FIRM_PLANNED_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT PlanIdVar, msc_supplies_s.nextval, organizationId, instanceId,
itemID, 18, TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, onHand, 2,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_INVENTORY
WHERE PLAN_ID=PlanIdVar AND MSC_INT_ASCP_INVENTORY.CATEGORY='Inventory';
g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_INVENTORY_001003' || ' : ' || SQLERRM;
INSERT INTO MSC_SUPPLIES (PLAN_ID,
TRANSACTION_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
ORDER_TYPE,
NEW_SCHEDULE_DATE,
NEW_ORDER_QUANTITY,
FIRM_PLANNED_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
select PlanIdVar, msc_supplies_s.nextval,
organizationID, instanceID, itemID, 88,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400, flow,
2,
SYSDATE, '-1', SYSDATE, '-1'
from (
( select distinct organizationID, instanceID, itemID, PeriodEnd, flow
from MSC_INT_ASCP_MFG_PLAN_MACHINE where PLAN_ID=PlanIdVar AND CATEGORY='Manufacturing')
UNION
( select distinct organizationID, instanceID, itemID, PeriodEnd, flow
from MSC_INT_ASCP_MFG_PLAN_LABOUR where PLAN_ID=PlanIdVar AND CATEGORY='Manufacturing') );
g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_MFG_PLAN_MACHINE_001004' || ' : ' || SQLERRM;
DELETE FROM MSC_SAFETY_STOCKS WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_SAFETY_STOCKS (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
PERIOD_START_DATE,
SAFETY_STOCK_QUANTITY,
TASK_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT PlanIdVar, OrganizationID, InstanceID,
ItemID, TO_DATE(Period,'YYYY-MM-DD'), Safety,
ROWNUM, --BUGBUG this value is in, just to make index unique
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_INVENTORY
WHERE PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_MSC_SAFETY_STOCKS_001001' || ' : ' || SQLERRM;
DELETE FROM MSC_DEPARTMENT_RESOURCES WHERE PLAN_ID=PlanIdVar;
INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
RESOURCE_ID,
DEPARTMENT_ID,
OWNING_DEPARTMENT_ID,
CAPACITY_UNITS,
RESOURCE_TYPE,
RESOURCE_CODE,
RESOURCE_DESCRIPTION,
DEPARTMENT_CODE,
DEPARTMENT_DESCRIPTION,
DEPARTMENT_CLASS,
RESOURCE_GROUP_NAME,
BOTTLENECK_FLAG,
LINE_FLAG,
AGGREGATE_RESOURCE_FLAG,
AVAILABLE_24_HOURS_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT DISTINCT MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID,
MSC_INT_ASCP_MACHINE_UTIL.OrganizationID,
MSC_INT_ASCP_MACHINE_UTIL.InstanceID,
MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
MSC_INT_ASCP_MACHINE_UTIL.DepartmentID,
CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.ResourceID = -1
THEN NULL
ELSE MSC_INT_ASCP_MACHINE_UTIL.DepartmentID
END,--OWNING_DEPARTMENT_ID
NULL, --CAPACITY_UNITS: nullable column, resource units
1, -- RESOURCE_TYPE it is a machine
MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
NULL, --DEPARTMENT_CLASS,
MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;
INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
RESOURCE_ID,
DEPARTMENT_ID,
OWNING_DEPARTMENT_ID,
CAPACITY_UNITS,
RESOURCE_TYPE,
RESOURCE_CODE,
RESOURCE_DESCRIPTION,
DEPARTMENT_CODE,
DEPARTMENT_DESCRIPTION,
DEPARTMENT_CLASS,
RESOURCE_GROUP_NAME,
BOTTLENECK_FLAG,
LINE_FLAG,
AGGREGATE_RESOURCE_FLAG,
AVAILABLE_24_HOURS_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT DISTINCT MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID,
MSC_INT_ASCP_LABOUR_UTIL.OrganizationID,
MSC_INT_ASCP_LABOUR_UTIL.InstanceID,
MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
MSC_INT_ASCP_LABOUR_UTIL.DepartmentID,
CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.ResourceID = -1
THEN NULL
ELSE MSC_INT_ASCP_LABOUR_UTIL.DepartmentID
END,--OWNING_DEPARTMENT_ID
NULL, --CAPACITY_UNITS: nullable column, resource units
2, -- RESOURCE_TYPE - it is a labour
MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
NULL, --DEPARTMENT_CLASS,
MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;
DELETE FROM MSC_BIS_RES_SUMMARY WHERE PLAN_ID=PlanIdVar;
select DISTINCT ScenarioName into ScenarioNameVar from MSC_INT_ASCP_KPI where PLAN_ID=PlanIdVar;
INSERT INTO MSC_BIS_RES_SUMMARY (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DEPARTMENT_ID,
RESOURCE_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
RESOURCE_DATE,
REQUIRED_HOURS,
AVAILABLE_HOURS,
UTILIZATION,
RESOURCE_GROUP,
OVERUTILIZATION_COST,
RESOURCE_COST)
SELECT MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID, MSC_INT_ASCP_MACHINE_UTIL.OrganizationID,
MSC_INT_ASCP_MACHINE_UTIL.InstanceID, MSC_INT_ASCP_MACHINE_UTIL.DepartmentID, MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
TO_DATE(MSC_INT_ASCP_MACHINE_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_MACHINE_UTIL.USED,
MSC_INT_ASCP_MACHINE_UTIL.CAPACITY, MSC_INT_ASCP_MACHINE_UTIL.UTILIZATION,
MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_MACHINE_UTIL.OVERCOST,
-- There may be multiple instances of the same machine (same dept and res id). We do not want to
-- blindly get production cost from KPI table since it does not distinguish the instances. Instead
-- we get corresponding cost of the resource (assume instances have same cost in the bucket).
MSC_INT_ASCP_MACHINE_UTIL.USED *
(
select distinct MSC_INT_ASCP_MFG_PLAN_MACHINE.RESOURCECOST
from MSC_INT_ASCP_MFG_PLAN_MACHINE
where MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_MFG_PLAN_MACHINE.PLAN_ID AND
MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_INT_ASCP_MFG_PLAN_MACHINE.INSTANCEID AND
MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_INT_ASCP_MFG_PLAN_MACHINE.ORGANIZATIONID AND
MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_INT_ASCP_MFG_PLAN_MACHINE.DEPARTMENTID AND
MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_INT_ASCP_MFG_PLAN_MACHINE.RESOURCEID AND
MSC_INT_ASCP_MACHINE_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_MACHINE.PERIODEND
)
FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES
WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;
INSERT INTO MSC_BIS_RES_SUMMARY (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DEPARTMENT_ID,
RESOURCE_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
RESOURCE_DATE,
REQUIRED_HOURS,
AVAILABLE_HOURS,
UTILIZATION,
RESOURCE_GROUP,
OVERUTILIZATION_COST,
RESOURCE_COST)
SELECT MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID, MSC_INT_ASCP_LABOUR_UTIL.OrganizationID,
MSC_INT_ASCP_LABOUR_UTIL.InstanceID, MSC_INT_ASCP_LABOUR_UTIL.DepartmentID,
MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
TO_DATE(MSC_INT_ASCP_LABOUR_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_LABOUR_UTIL.USED,
MSC_INT_ASCP_LABOUR_UTIL.CAPACITY, MSC_INT_ASCP_LABOUR_UTIL.UTILIZATION,
MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_LABOUR_UTIL.OVERCOST,
-- There may be multiple instances of the same crew (same dept and res id). We do not want to
-- blindly get production cost from KPI table since it does not distinguish the instances. Instead
-- we get corresponding cost of the resource (assume instances have same cost in the bucket).
MSC_INT_ASCP_LABOUR_UTIL.USED *
(
select distinct MSC_INT_ASCP_MFG_PLAN_LABOUR.RESOURCECOST
from MSC_INT_ASCP_MFG_PLAN_LABOUR
where MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_MFG_PLAN_LABOUR.PLAN_ID AND
MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_INT_ASCP_MFG_PLAN_LABOUR.INSTANCEID AND
MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_INT_ASCP_MFG_PLAN_LABOUR.ORGANIZATIONID AND
MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_INT_ASCP_MFG_PLAN_LABOUR.DEPARTMENTID AND
MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_INT_ASCP_MFG_PLAN_LABOUR.RESOURCEID AND
MSC_INT_ASCP_LABOUR_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_LABOUR.PERIODEND
)
FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES
WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;
DELETE FROM msc_int_ascp_prodcost_kpi; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar; --??BUGBUG ??
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001001' || ' : ' || SQLERRM;
DELETE FROM msc_int_ascp_totalprodcost; -- where msc_int_ascp_totalprodcost.plan_id=PlanIdVar; --??BUGBUG ??
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001002' || ' : ' || SQLERRM;
DELETE FROM MSC_BIS_INV_DETAIL WHERE PLAN_ID=PlanIdVar;
INSERT INTO msc_int_ascp_prodcost_kpi ( plan_id,
organizationID,
instanceID,
itemID,
periodEnd,
prodCost )
SELECT plan_id, organizationID, instanceID, itemID, periodEnd, SUM(resourcecost*resourceusage)
FROM msc_int_ascp_mfg_plan_machine
WHERE plan_id=PlanIdVar
GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001004' || ' : ' || SQLERRM;
INSERT INTO msc_int_ascp_prodcost_kpi ( plan_id,
organizationID,
instanceID,
itemID,
periodEnd,
prodCost )
SELECT plan_id, organizationID, instanceID, itemID, periodEnd, SUM
(resourcecost*resourceusage)
FROM msc_int_ascp_mfg_plan_labour
WHERE plan_id=PlanIdVar
GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001005' || ' : ' || SQLERRM;
INSERT INTO msc_int_ascp_totalprodcost ( plan_id,
organizationID,
instanceID,
itemID,
periodEnd,
totalProdCost )
SELECT msc_int_ascp_prodcost_kpi.plan_id,
msc_int_ascp_prodcost_kpi.organizationID, msc_int_ascp_prodcost_kpi.instanceID,
msc_int_ascp_prodcost_kpi.itemID, msc_int_ascp_prodcost_kpi.periodEnd,
SUM(msc_int_ascp_prodcost_kpi.prodCost)
FROM msc_int_ascp_prodcost_kpi
--, MSC_INT_ASCP_KPI
WHERE
--MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar AND
msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar -- AND
--msc_int_ascp_prodcost_kpi.organizationID=MSC_INT_ASCP_KPI.OrganizationID AND
--msc_int_ascp_prodcost_kpi.itemID=MSC_INT_ASCP_KPI.ItemID AND
--msc_int_ascp_prodcost_kpi.periodEnd=MSC_INT_ASCP_KPI.PERIODEND AND
--msc_int_ascp_prodcost_kpi.instanceID=MSC_INT_ASCP_KPI.InstanceID
GROUP BY msc_int_ascp_prodcost_kpi.plan_id,
msc_int_ascp_prodcost_kpi.organizationID, msc_int_ascp_prodcost_kpi.instanceID,
msc_int_ascp_prodcost_kpi.itemID, msc_int_ascp_prodcost_kpi.PeriodEnd;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001006' || ' : ' || SQLERRM;
INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
DETAIL_DATE,
MDS_PRICE,
ZONE_ID,
PRODUCTION_COST,
PURCHASING_COST,
CARRYING_COST,
TRANSPORTATION_COST,
OTHER_COST,
PAB,
TOTAL_COST,
SERVICE_LEVEL_1,
SERVICE_LEVEL_2,
SHIP_METHOD,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
SUPPLIER_USAGE,
SUPPLIER_CAPACITY,
SOURCE_ORG_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
MDS_QUANTITY,
INVENTORY_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
--------------------------------
-- Supplier-dependent records
--------------------------------
SELECT MSC_INT_ASCP_SUPPLY.PLAN_ID,
MSC_INT_ASCP_SUPPLY.OrganizationID,
MSC_INT_ASCP_SUPPLY.InstanceID,
MSC_INT_ASCP_SUPPLY.ItemID,
( select DISTINCT MSC_INT_ASCP_KPI.PeriodType from MSC_INT_ASCP_KPI where MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar ), -- we do not have it ASCP_Supply report ( BUGBUG does not accept FIRST )
1,
TO_DATE( MSC_INT_ASCP_SUPPLY.PeriodEnd,'YYYY-MM-DD' )-1/86400,
0, --TO_NUMBER(NULL) MDS_PRICE,
-23453, -- ZONEID,
0, --TO_NUMBER(NULL) PRODUCTION_COST,
-- supplier related column: purchase cost
MSC_INT_ASCP_SUPPLY.COST * MSC_INT_ASCP_SUPPLY.SUPPLY,
0,--TO_NUMBER(NULL) CARRYING_COST,
0,--TO_NUMBER(NULL) TRANSPORTATION_COST,
0,--TO_NUMBER(NULL) OTHER_COST,
0,--TO_NUMBER(NULL) PAB,
0,--TO_NUMBER(NULL) TOTAL_COST,
0,--TO_NUMBER(NULL) SERVICE_LEVEL_1,
0,--TO_NUMBER(NULL) SERVICE_LEVEL_2,
null,--TO_CHAR(NULL) SHIP_METHOD,
-- supplier related columns
MSC_INT_ASCP_SUPPLY.SupplierID,
MSC_INT_ASCP_SUPPLY.SupplierSiteID,
MSC_INT_ASCP_SUPPLY.SUPPLY,
MSC_INT_ASCP_SUPPLY.CAPACITY,
-23453, -- SOURCE_ORG_INSTANCE_ID
-23453, -- SOURCE_ORGANIZATION_ID
-23453, -- CUSTOMER_SITE_ID
-23453, -- CUSTOMER_ID
0,--TO_NUMBER(NULL) MDS_QUANTITY,
0,--TO_NUMBER(NULL) INVENTORY_QUANTITY,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_SUPPLY
WHERE MSC_INT_ASCP_SUPPLY.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001008' || ' : ' || SQLERRM;
INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
DETAIL_DATE,
SHIP_METHOD,
TRANSPORTATION_COST,
MDS_QUANTITY,
INVENTORY_QUANTITY,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
SOURCE_ORG_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
PlanIdVar,
-- Next 2 lines are changed to accomodate the S&OP request to populate dest org with source org when shipping to a customer:
-- case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN -23453 else MSC_INT_ASCP_TRANSPORTATION.ToOrgID end,
-- case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN -23453 else MSC_INT_ASCP_TRANSPORTATION.ToInstanceID end,
case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN MSC_INT_ASCP_TRANSPORTATION.FromOrgID else MSC_INT_ASCP_TRANSPORTATION.ToOrgID end,
case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN MSC_INT_ASCP_TRANSPORTATION.FromInstanceID else MSC_INT_ASCP_TRANSPORTATION.ToInstanceID end,
MSC_INT_ASCP_TRANSPORTATION.ItemID,
( select DISTINCT MSC_INT_ASCP_KPI.PeriodType from MSC_INT_ASCP_KPI where MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar ), -- we do not have it ASCP_Transportation report, -- DETAIL_LEVEL ( BUGBUG does not accept FIRST )
1, --PERIOD_TYPE
TO_DATE( MSC_INT_ASCP_TRANSPORTATION.PeriodEnd,'YYYY-MM-DD' )-1/86400,
MSC_INT_ASCP_TRANSPORTATION.TransportMode,
MSC_INT_ASCP_TRANSPORTATION.Cost * MSC_INT_ASCP_TRANSPORTATION.Quantity,
-- Constrained Forecast
CASE
WHEN MSC_INT_ASCP_TRANSPORTATION.CATEGORY='Transportation' THEN
( case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Inter-Organization' then
( case when (select count( MSC_INT_ASCP_DEMANDS.Satisfied ) from MSC_INT_ASCP_DEMANDS
where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
MSC_INT_ASCP_DEMANDS.InstanceID = MSC_INT_ASCP_TRANSPORTATION.ToInstanceID and
MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
MSC_INT_ASCP_DEMANDS.OrganizationID = MSC_INT_ASCP_TRANSPORTATION.ToOrgID and
MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) > 0 then ( select MSC_INT_ASCP_DEMANDS.Satisfied from MSC_INT_ASCP_DEMANDS
where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
MSC_INT_ASCP_DEMANDS.InstanceID = MSC_INT_ASCP_TRANSPORTATION.ToInstanceID and
MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
MSC_INT_ASCP_DEMANDS.OrganizationID = MSC_INT_ASCP_TRANSPORTATION.ToOrgID and
MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) else 0 end )
else
( case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' then
( case when ( SELECT COUNT( MSC_INT_ASCP_DEMANDS.Satisfied ) from MSC_INT_ASCP_DEMANDS
where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) = 0 ) and
MSC_INT_ASCP_DEMANDS.ZoneID = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
--MSC_INT_ASCP_DEMANDS.OrganizationID = -1 and --BUGBUG Constrained Forecast - SS in ??
--MSC_INT_ASCP_TRANSPORTATION.ToOrgID is NULL and --BUGBUG Constrained Forecast - SS in ??
MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) > 0 then
( select MSC_INT_ASCP_DEMANDS.Satisfied from MSC_INT_ASCP_DEMANDS
where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) = 0 ) and
MSC_INT_ASCP_DEMANDS.ZoneID = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
--MSC_INT_ASCP_DEMANDS.OrganizationID = -1 and --BUGBUG Constrained Forecast - SS in ??
--MSC_INT_ASCP_TRANSPORTATION.ToOrgID is NULL and --BUGBUG Constrained Forecast - SS in ??
MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd )
else ( case when ( SELECT COUNT( MSC_INT_ASCP_DEMANDS.Satisfied ) from MSC_INT_ASCP_DEMANDS
where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) <> 0 ) and
SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)+1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToOrgID) and
MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, 1, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)-1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd) > 0 then
( select MSC_INT_ASCP_DEMANDS.Satisfied from MSC_INT_ASCP_DEMANDS
where MSC_INT_ASCP_DEMANDS.Category ='Demand' and
MSC_INT_ASCP_DEMANDS.PLAN_ID = planIdVar and
( MSC_INT_ASCP_DEMANDS.ZoneID is not null ) AND ( INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1) <> 0 ) and
SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)+1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToOrgID) and
MSC_INT_ASCP_DEMANDS.ItemID = MSC_INT_ASCP_TRANSPORTATION.ItemID and
SUBSTR(MSC_INT_ASCP_DEMANDS.ZoneID, 1, INSTR(MSC_INT_ASCP_DEMANDS.ZoneID,':',1,1)-1) = TO_CHAR(MSC_INT_ASCP_TRANSPORTATION.ToInstanceID) and
MSC_INT_ASCP_DEMANDS.PeriodEnd = MSC_INT_ASCP_TRANSPORTATION.PeriodEnd ) else 0 end )
end )
else 0
end )
end )
ELSE 0
END,
0,
-23453, --SUPPLIER_ID
-23453, --SUPPLIER_SITE_ID
MSC_INT_ASCP_TRANSPORTATION.FromInstanceID, --SOURCE_ORG_INSTANCE_ID
MSC_INT_ASCP_TRANSPORTATION.FromOrgID, --SOURCE_ORGANIZATION_ID
--Zone_Id
case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN
CASE WHEN ( ToInstanceID is not null ) AND ( ToOrgID is null )
THEN ToInstanceID
else -23453 end
ELSE
-23453
END,
--Customer_Site_Id
case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN
CASE WHEN ( ToInstanceID is not null ) AND ( ToOrgID is not null )
THEN ToOrgID
else -23453 end
ELSE
-23453
END,
-- Customer_Id
case when MSC_INT_ASCP_TRANSPORTATION.SUBCATEGORY='Customer' THEN
CASE WHEN ( ToInstanceID is not null ) AND ( ToOrgID is not null )
THEN ToInstanceID
else -23453 end
ELSE
-23453
END,
SYSDATE, '-1',
SYSDATE, '-1',
-1
FROM MSC_INT_ASCP_TRANSPORTATION
WHERE MSC_INT_ASCP_TRANSPORTATION.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001009' || ' : ' || SQLERRM;
INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
DETAIL_DATE,
MDS_PRICE,
SOURCE_ORG_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
ZONE_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
PRODUCTION_COST,
CARRYING_COST,
OTHER_COST,
PAB,
SERVICE_LEVEL_1,
SERVICE_LEVEL_2,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
SUPPLIER_USAGE,
SUPPLIER_CAPACITY,
MDS_QUANTITY,
INVENTORY_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT MSC_INT_ASCP_KPI.PLAN_ID,
-- Next 2 lines are changed to accomodate the S&OP request to populate dest org with source org when shipping to a customer:
-- case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Organization_ID else MSC_INT_ASCP_KPI.OrganizationID end,
-- case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Org_Instance_ID else MSC_INT_ASCP_KPI.InstanceID end,
case when MSC_INT_ASCP_KPI.DemandCost<>0 AND OrganizationID = -23453 THEN Source_Organization_ID ELSE OrganizationID END,
case when MSC_INT_ASCP_KPI.DemandCost<>0 AND InstanceID = -23453 THEN Source_Org_Instance_ID ELSE InstanceID END,
MSC_INT_ASCP_KPI.ItemID,
MSC_INT_ASCP_KPI.PeriodType,
1,
TO_DATE( MSC_INT_ASCP_KPI.PeriodEnd,'YYYY-MM-DD' )-1/86400,
-- demand cost
MSC_INT_ASCP_KPI.DemandCost,
case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Org_Instance_ID else -23453 end, -- SOURCE_ORG_INSTANCE_ID,
case when MSC_INT_ASCP_KPI.DemandCost<>0 then Source_Organization_ID else -23453 end, -- SOURCE_ORGANIZATION_ID,
MSC_INT_ASCP_KPI.ZoneID,
MSC_INT_ASCP_KPI.Customer_Site_ID, --CUSTOMER_SITE_ID,
MSC_INT_ASCP_KPI.Customer_ID, --CUSTOMER_ID,
-- production_cost: added as separate records
0,
--case when (SELECT COUNT(*) FROM msc_int_ascp_totalprodcost
-- WHERE PLAN_ID=PlanIdVar AND
-- OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
-- ItemID=MSC_INT_ASCP_KPI.ItemID AND
-- PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
-- InstanceID=MSC_INT_ASCP_KPI.InstanceID
-- )=0 then 0
-- else (SELECT DISTINCT ( totalProdCost ) FROM -- ( BUGBUG Does not accept FIRST )
-- msc_int_ascp_totalprodcost
-- WHERE PLAN_ID=PlanIdVar AND
-- OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
-- ItemID=MSC_INT_ASCP_KPI.ItemID AND
-- PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
-- InstanceID=MSC_INT_ASCP_KPI.InstanceID)
--end,
-- carrying cost
MSC_INT_ASCP_KPI.InventoryCost,
-- other cost
MSC_INT_ASCP_KPI.OtherCost,
-- PAB
case when (SELECT COUNT(*) FROM MSC_INT_ASCP_INVENTORY
WHERE PLAN_ID=PlanIdVar AND
OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
ItemID=MSC_INT_ASCP_KPI.ItemID AND
PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
InstanceID=MSC_INT_ASCP_KPI.InstanceID
)=0 then 0
else (SELECT DISTINCT ( StorageAmount ) FROM MSC_INT_ASCP_INVENTORY -- ( BUGBUG Does not accept FIRST )
WHERE
PLAN_ID=PlanIdVar AND
OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
ItemID=MSC_INT_ASCP_KPI.ItemID AND
PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
InstanceID=MSC_INT_ASCP_KPI.InstanceID )
end,
MSC_INT_ASCP_KPI.DemandFillRate,
1,
-- supplier related columns
-23453, --SUPPLIER_ID,
-23453, --SUPPLIER_SITE_ID,
0,-- TO_NUMBER(NULL) SUPPLIER_USAGE,
0,-- TO_NUMBER(NULL) SUPPLIER_CAPACITY,
0,
0,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_KPI
WHERE MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001010' || ' : ' || SQLERRM;
INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
DETAIL_DATE,
MDS_PRICE,
ZONE_ID,
PRODUCTION_COST,
PURCHASING_COST,
CARRYING_COST,
TRANSPORTATION_COST,
OTHER_COST,
PAB,
TOTAL_COST,
SERVICE_LEVEL_1,
SERVICE_LEVEL_2,
SHIP_METHOD,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
SUPPLIER_USAGE,
SUPPLIER_CAPACITY,
SOURCE_ORG_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
CUSTOMER_SITE_ID,
CUSTOMER_ID,
MDS_QUANTITY,
INVENTORY_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT msc_int_ascp_totalprodcost.PLAN_ID,
msc_int_ascp_totalprodcost.OrganizationID,
msc_int_ascp_totalprodcost.InstanceID,
msc_int_ascp_totalprodcost.ItemID,
( select DISTINCT MSC_INT_ASCP_KPI.PeriodType from MSC_INT_ASCP_KPI where MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar ), -- we do not have it ASCP_Supply report ( BUGBUG does not accept FIRST )
1,
TO_DATE( msc_int_ascp_totalprodcost.PeriodEnd,'YYYY-MM-DD' )-1/86400,
0, --TO_NUMBER(NULL) MDS_PRICE,
-23453, -- ZONEID,
msc_int_ascp_totalprodcost.totalprodcost,
0, -- purchase cost
0,--TO_NUMBER(NULL) CARRYING_COST,
0,--TO_NUMBER(NULL) TRANSPORTATION_COST,
0,--TO_NUMBER(NULL) OTHER_COST,
0,--TO_NUMBER(NULL) PAB,
0,--TO_NUMBER(NULL) TOTAL_COST,
0,--TO_NUMBER(NULL) SERVICE_LEVEL_1,
0,--TO_NUMBER(NULL) SERVICE_LEVEL_2,
null,--TO_CHAR(NULL) SHIP_METHOD,
-23453,
-23453,
0,
0,
-23453, -- SOURCE_ORG_INSTANCE_ID
-23453, -- SOURCE_ORGANIZATION_ID
-23453, -- CUSTOMER_SITE_ID
-23453, -- CUSTOMER_ID
0,--TO_NUMBER(NULL) MDS_QUANTITY,
0,--TO_NUMBER(NULL) INVENTORY_QUANTITY,
SYSDATE, '-1', SYSDATE, '-1'
FROM msc_int_ascp_totalprodcost
WHERE msc_int_ascp_totalprodcost.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001118' || ' : ' || SQLERRM;
INSERT INTO MSC_BIS_INV_DETAIL (PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
DETAIL_LEVEL,
PERIOD_TYPE,
DETAIL_DATE,
SHIP_METHOD,
ITEM_TRAVEL_DISTANCE,
MDS_QUANTITY,
INVENTORY_QUANTITY,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
ZONE_ID,
SOURCE_ORG_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
CUSTOMER_ID,
CUSTOMER_SITE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
PlanIdVar,
-23453,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
PERIOD_TYPE, -- DETAIL_LEVEL
1, --PERIOD_TYPE
TO_DATE( MSC_INT_ITEM_TRAVEL_DISTANCE.Period_End,'YYYY-MM-DD' )-1/86400,
TRANSPORTATION_MODE,
ITEM_TRAVEL_DISTANCE,
0,
0,
-23453, --SUPPLIER_ID
-23453, --SUPPLIER_SITE_ID
-23453, --ZONE_ID,
-23453, --SOURCE_ORG_INSTANCE_ID
-23453, --SOURCE_ORGANIZATION_ID
-23453, --CUSTOMER_ID
-23453, --CUSTOMER_SITE_ID
SYSDATE, '-1',
SYSDATE, '-1',
-1
FROM MSC_INT_ITEM_TRAVEL_DISTANCE
WHERE PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001011' || ' : ' || SQLERRM;
DELETE from MSC_ITEM_SOURCING
WHERE MSC_ITEM_SOURCING.PLAN_ID = PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001005' || ' : ' || SQLERRM;
INSERT into MSC_ITEM_SOURCING (
PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID2,
SOURCE_ORGANIZATION_ID,
INVENTORY_ITEM_ID,
EFFECTIVE_DATE,
ASSIGNMENT_ID,
ASSIGNMENT_SET_ID,
ASSIGNMENT_TYPE,
SOURCING_RULE_TYPE,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
CUSTOMER_ID,
CUSTOMER_SITE_ID,
ZONE_ID,
SHIP_METHOD,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
SELECT
PlanIdVar,
-- Destination Instance: when destination is customer, assign to -23453
CASE WHEN (sourcing_type = 'Sale') THEN
-23453
ELSE
TO_NUMBER(substr(destination_code,1,instr(destination_code,':',1,1)-1))
END,
-- Destination Org: when destination is customer, assign to -23453
CASE WHEN (sourcing_type = 'Sale') THEN
-23453
ELSE
TO_NUMBER(substr(destination_code,instr(destination_code,':',1,1)+1))
END,
-- Origin Instance: when origin is supplier, assign to -23453
CASE WHEN (sourcing_type = 'Supplier') THEN
-23453
ELSE
TO_NUMBER(substr(origin_code,1,instr(origin_code,':',1,1)-1))
END,
-- Origin Org: when origin is supplier, assign to -23453
CASE WHEN (sourcing_type = 'Supplier') THEN
-23453
ELSE
TO_NUMBER(substr(origin_code,instr(origin_code,':',1,1)+1))
END,
TO_NUMBER(substr(item_code,instr(item_code,':',1,1)+1)),
-- Effective date is string type in temp table
TO_DATE((select start_date from msc_int_src_recommend_detail where rownum=1), 'YYYY-MM-DD'),
-23453,
assignmentSetOutIdVar,
-23453,
-23453,
-- Supplier: when origin is not supplier, assign to -23453
CASE WHEN (sourcing_type <> 'Supplier') THEN
-23453
ELSE
TO_NUMBER(substr(origin_code,1,instr(origin_code,':',1,1)-1))
END,
-- Supplier Site: when origin is not supplier, assign to -23453
CASE WHEN (sourcing_type <> 'Supplier') THEN
-23453
ELSE
TO_NUMBER(substr(origin_code,instr(origin_code,':',1,1)+1))
END,
-- Customer: when destination is not customer, assign to -23453
CASE WHEN (sourcing_type <> 'Sale') THEN
-23453
ELSE
-- if zone, assign to -23453
(CASE WHEN (INSTR(destination_code,':') = 0) THEN
-23453
ELSE
TO_NUMBER(substr(destination_code,1,instr(destination_code,':',1,1)-1))
END)
END,
-- Customer Site: when destination is not customer, assign to -23453
CASE WHEN (sourcing_type <> 'Sale') THEN
-23453
ELSE
-- if zone, assign to -23453
(CASE WHEN (INSTR(destination_code,':') = 0) THEN
-23453
ELSE
TO_NUMBER(substr(destination_code,instr(destination_code,':',1,1)+1))
END)
END,
-- Zone: when destination is not customer, assign to -23453
CASE WHEN (sourcing_type <> 'Sale') THEN
-23453
ELSE
-- if not zone, assign to -23453
(CASE WHEN (INSTR(destination_code,':') = 0) THEN
TO_NUMBER(destination_code)
ELSE
-23453
END)
END,
transport_mode_code,
sysdate,
-1,
sysdate,
-1
FROM msc_int_src_recommend_detail
WHERE planName = PlanIdVar
GROUP BY planName, origin_code, destination_code,
item_code, sourcing_type, transport_mode_code;
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001010' || ' : ' || SQLERRM;
SELECT COMPILE_DESIGNATOR INTO l_PlanName
FROM MSC_PLANS
WHERE PLAN_ID = PlanId;
SELECT USER_ID INTO V_USER_ID
FROM FND_USER
WHERE USER_ID = USERID;
SELECT RESPONSIBILITY_ID INTO V_RESPID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = V_USER_ID AND RESPONSIBILITY_ID = RESPID AND
(sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate));
SELECT APPLICATION_ID INTO V_APPID
FROM FND_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = V_RESPID;
DELETE FROM MSC_INT_ITEM_TRAVEL_DISTANCE WHERE PLAN_ID=PlanIdVar;
DELETE FROM MSC_INT_ASCP_INVENTORY WHERE PLAN_ID=PlanIdVar;