[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) is not null )
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) is not null )
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';
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) is not null )
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) is not null )
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) is not null )
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) is not null )
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';
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';
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';
DELETE FROM MSC_SYSTEM_ITEMS 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,
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,
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_001001';
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';
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,
CASE WHEN ( ZoneID is not null )
THEN -1
ELSE
InstanceID
END,
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) is not null )
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) is not null )
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';
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';
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,
CASE WHEN ( Category = 'Customer' )
THEN ToInstanceID -- CUSTOMER_SITE_ID
ELSE
NULL
END,
CASE WHEN ( Category = 'Customer' )
THEN ToOrgID -- CUSTOMER_ID
ELSE
NULL
END,
0,
TO_DATE(PeriodEnd,'YYYY-MM-DD')-1/86400,
ItemID,
1,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_TRANSPORTATION
WHERE PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_DEMAND_FROM_ASCP_TRANSPORTATION_001003';
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';
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;
g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_ASCP_TRANSPORTATION_001002';
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';
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');
g_ErrorCode := 'ERROR_UPDATE_SUPPLIES_FROM_MSC_INT_ASCP_MFG_PLAN_MACHINE_001004';
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';
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';
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';
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,
MSC_INT_ASCP_KPI.PRODUCTIONCOST
FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
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 AND
MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar AND
MSC_INT_ASCP_KPI.INSTANCEID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
MSC_INT_ASCP_KPI.ORGANIZATIONID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
MSC_INT_ASCP_KPI.DEPARTMENTID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND -- toggled dept and resource id because in KPI export
MSC_INT_ASCP_KPI.ITEMID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND -- the dept and item are toggled due to lack of category
MSC_INT_ASCP_KPI.PERIODEND = MSC_INT_ASCP_MACHINE_UTIL.PERIODEND;
g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001';
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,
MSC_INT_ASCP_KPI.PRODUCTIONCOST
FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
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 AND
MSC_INT_ASCP_KPI.PLAN_ID = PlanIdVar AND
MSC_INT_ASCP_KPI.INSTANCEID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
MSC_INT_ASCP_KPI.ORGANIZATIONID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
MSC_INT_ASCP_KPI.ITEMID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
MSC_INT_ASCP_KPI.DEPARTMENTID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
MSC_INT_ASCP_KPI.PERIODEND = MSC_INT_ASCP_LABOUR_UTIL.PERIODEND;
g_ErrorCode := 'ERROR_UPDATE_RES_SUMMARY_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002';
DELETE FROM msc_int_ascp_prodcost_kpi; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001001';
DELETE FROM msc_int_ascp_totalprodcost; -- where msc_int_ascp_totalprodcost.plan_id=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001002';
DELETE FROM msc_int_ascp_transpcost_kpi; -- where msc_int_ascp_transpcost_kpi.plan_id=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001003';
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';
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';
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_KPI.TRANSPORTATIONMODE is null 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';
INSERT INTO msc_int_ascp_transpcost_kpi ( plan_id,
organizationID,
instanceID,
itemID,
periodEnd,
transpCost )
SELECT DISTINCT plan_id, organizationID, instanceID, itemID, periodEnd, sum
(transportationcost)
FROM msc_int_ascp_kpi
WHERE plan_id=PlanIdVar and transportationmode is not null
GROUP BY plan_id, organizationID, instanceID, itemID, periodEnd;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001007';
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,
MDS_QUANTITY,
INVENTORY_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
--------------------------------
-- Supplier-denpendent records
--------------------------------
SELECT MSC_INT_ASCP_KPI.PLAN_ID,
MSC_INT_ASCP_KPI.OrganizationID,
MSC_INT_ASCP_KPI.InstanceID,
MSC_INT_ASCP_KPI.ItemID,
MSC_INT_ASCP_KPI.PeriodType,
1,
TO_DATE( MSC_INT_ASCP_KPI.PeriodEnd,'YYYY-MM-DD' )-1/86400,
0, --TO_NUMBER(NULL) MDS_PRICE,
TO_NUMBER(NULL), -- ZONEID,
0, --TO_NUMBER(NULL) PRODUCTION_COST,
-- supplier related column: purchase cost
case when MSC_INT_ASCP_SUPPLY.COST * MSC_INT_ASCP_SUPPLY.SUPPLY is
null then 0
else MSC_INT_ASCP_SUPPLY.COST * MSC_INT_ASCP_SUPPLY.SUPPLY end,
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,
0,--TO_NUMBER(NULL) MDS_QUANTITY,
0,--TO_NUMBER(NULL) INVENTORY_QUANTITY,
SYSDATE, '-1', SYSDATE, '-1'
FROM MSC_INT_ASCP_KPI, MSC_INT_ASCP_SUPPLY
WHERE MSC_INT_ASCP_SUPPLY.PLAN_ID=MSC_INT_ASCP_KPI.PLAN_ID AND
MSC_INT_ASCP_SUPPLY.OrganizationID=MSC_INT_ASCP_KPI.OrganizationID AND
MSC_INT_ASCP_SUPPLY.ItemID=MSC_INT_ASCP_KPI.ItemID AND
MSC_INT_ASCP_SUPPLY.PERIODEND=MSC_INT_ASCP_KPI.PERIODEND AND
MSC_INT_ASCP_SUPPLY.InstanceID=MSC_INT_ASCP_KPI.InstanceID AND
MSC_INT_ASCP_KPI.PLAN_ID=PlanIdVar;
g_ErrorCode := 'ERROR_UPDATE_BIS_INV_DETAIL_001008';
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,
MDS_QUANTITY,
INVENTORY_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT MSC_INT_ASCP_KPI.PLAN_ID,
MSC_INT_ASCP_KPI.OrganizationID,
MSC_INT_ASCP_KPI.InstanceID,
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
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
MSC_INT_ASCP_KPI.DemandCost else 0 end,
MSC_INT_ASCP_KPI.ZoneID,
-- production_cost
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null
then (
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
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)
else 0 end,
-- supplier related column: purchase cost
0, --TO_NUMBER(NULL) PURCHASING_COST,
-- carrying cost
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
MSC_INT_ASCP_KPI.InventoryCost else 0 end,
-- transportastion cost
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then 0
else MSC_INT_ASCP_KPI.TRANSPORTATIONCOST end,
-- other cost
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
MSC_INT_ASCP_KPI.OtherCost else 0 end,
-- PAB
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null
then (
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
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)
else
0
end,
-- total cost
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
-- add demand cost
case when MSC_INT_ASCP_KPI.DEMANDCOST > 0 then
MSC_INT_ASCP_KPI.DEMANDCOST
else 0 end +
-- purchasing cost is by supplier, not added here
-- production cost
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
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 +
-- transportation cost
case when (SELECT COUNT(*) FROM msc_int_ascp_transpcost_kpi
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 TRANSPCOST FROM
msc_int_ascp_transpcost_kpi
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.INVENTORYCOST +
MSC_INT_ASCP_KPI.OTHERCOST
else 0 end,
case when MSC_INT_ASCP_KPI.TRANSPORTATIONMODE is null then
MSC_INT_ASCP_KPI.DemandFillRate
else 0 end,
1,
MSC_INT_ASCP_KPI.TRANSPORTATIONMODE,
-- supplier related columns
TO_NUMBER(NULL), --SUPPLIER_ID,
TO_NUMBER(NULL), --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_001009';
DELETE FROM msc_int_source1; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar;
g_ErrorCode := 'ERROR_DELETE_msc_int_source1_001011';
DELETE FROM msc_int_source2; -- where msc_int_ascp_prodcost_kpi.plan_id=PlanIdVar;
g_ErrorCode := 'ERROR_DELETE_msc_int_source2_001012';
INSERT into msc_int_source1 ( item_code, branch_code )
SELECT msc_int_src_recommend_detail.item_code,
msc_int_src_recommend_detail.destination_code
FROM msc_int_src_recommend_detail
WHERE msc_int_src_recommend_detail.available = 'Yes';
g_ErrorCode := 'ERROR_INSERT_msc_int_source1_001021';
INSERT into msc_int_source2 ( item_code, branch_code, enable_date, disable_date )
SELECT msc_int_src_recommend_detail.item_code,
msc_int_src_recommend_detail.destination_code,
TO_DATE(msc_int_src_recommend_detail.start_date, 'YYYY-MM-DD'),
TO_DATE(msc_int_src_recommend_detail.end_date, 'YYYY-MM-DD')
FROM msc_int_src_recommend_detail
WHERE msc_int_src_recommend_detail.available = 'Yes';
g_ErrorCode := 'ERROR_INSERT_msc_int_source2_001022';
DELETE from MSC_SR_SOURCE_ORG
WHERE
MSC_SR_SOURCE_ORG.SR_RECEIPT_ID in
( select MSC_SR_RECEIPT_ORG.SR_RECEIPT_ID from MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG
where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID = MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID );
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001001';
DELETE from MSC_SR_RECEIPT_ORG
WHERE
MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID in
( select MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID from MSC_SR_ASSIGNMENTS
where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar );
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001002';
DELETE from MSC_SOURCING_RULES
WHERE
MSC_SOURCING_RULES.SOURCING_RULE_ID in
( select MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID from MSC_SR_ASSIGNMENTS
where MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar );
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001003';
DELETE from MSC_SR_ASSIGNMENTS
WHERE
MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar;
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001004';
INSERT into MSC_SR_ASSIGNMENTS (
ASSIGNMENT_ID,
SR_ASSIGNMENT_ID,
SR_ASSIGNMENT_INSTANCE_ID,
ASSIGNMENT_SET_ID,
ASSIGNMENT_TYPE,
SOURCING_RULE_ID,
SOURCING_RULE_TYPE,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT
MSC_SR_ASSIGNMENTS_S.NEXTVAL,
-1 * MSC_SR_ASSIGNMENTS_S.NEXTVAL, -- Part of unique key. Maybe should use current value.
-1, -- // -1 until we figure out what goes here.
assignmentSetOutIdVar,
6, -- // 6 is item-org assignment type
MSC_SOURCING_RULES_S.NEXTVAL, -- //Cache these because we cannot look them up by name
1,
substr(msc_int_source1.branch_code,instr(msc_int_source1.branch_code,':',1,1)+1),
substr(msc_int_source1.item_code,1,instr(msc_int_source1.item_code,':',1,1)-1),
substr(msc_int_source1.item_code,instr(msc_int_source1.item_code,':',1,1)+1), -- CHANGE
sysdate,
'-1',
sysdate,
'-1'
FROM msc_int_source1;
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001005';
INSERT into MSC_SOURCING_RULES (
SOURCING_RULE_ID,
SR_SOURCING_RULE_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
SOURCING_RULE_NAME,
STATUS,
SOURCING_RULE_TYPE,
PLANNING_ACTIVE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DELETED_FLAG )
SELECT DISTINCT
MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
-1 * MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID, -- // I suppose these are the same.
MSC_SR_ASSIGNMENTS.ORGANIZATION_ID,
(MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_NAME || ':' || MSC_SYSTEM_ITEMS.ITEM_NAME || ':' || MSC_PLAN_ORGANIZATIONS.ORGANIZATION_CODE),
1, -- // STATUS
1, -- // 1 means sourcing rule type
1,
sysdate,
'-1',
sysdate,
'-1',
'2'
FROM MSC_SR_ASSIGNMENTS , MSC_ASSIGNMENT_SETS , MSC_SYSTEM_ITEMS , MSC_PLAN_ORGANIZATIONS -- ( alias org )
WHERE
MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
MSC_ASSIGNMENT_SETS.ASSIGNMENT_SET_ID = MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID and
MSC_SYSTEM_ITEMS.SR_INSTANCE_ID = MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID and
MSC_SYSTEM_ITEMS.ORGANIZATION_ID = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID and
MSC_SYSTEM_ITEMS.INVENTORY_ITEM_ID = MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID and
MSC_SYSTEM_ITEMS.PLAN_ID = -1 and
MSC_PLAN_ORGANIZATIONS.PLAN_ID = planIdVar and
MSC_PLAN_ORGANIZATIONS.SR_INSTANCE_ID = MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID and
MSC_PLAN_ORGANIZATIONS.ORGANIZATION_ID = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID;
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001006';
INSERT into MSC_SR_RECEIPT_ORG (
SR_RECEIPT_ID,
SR_SR_RECEIPT_ID,
SR_INSTANCE_ID,
SR_RECEIPT_ORG,
RECEIPT_ORG_INSTANCE_ID,
SOURCING_RULE_ID,
RECEIPT_PARTNER_ID,
RECEIPT_PARTNER_SITE_ID,
EFFECTIVE_DATE,
DISABLE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
) -- // msc_int_source2 == source
SELECT
MSC_SR_RECEIPT_ORG_S.NEXTVAL,
-1 * MSC_SR_RECEIPT_ORG_S.NEXTVAL,
substr(msc_int_source2.branch_code,1,instr(msc_int_source2.branch_code,':',1,1)-1), -- // I suspect I am supposed to put something here but I don't know what.
substr(msc_int_source2.branch_code,instr(msc_int_source2.branch_code,':',1,1)+1),
substr(msc_int_source2.branch_code,1,instr(msc_int_source2.branch_code,':',1,1)-1), -- // Eventually this should be obtained from the branch code.
MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID,
NULL, -- This is empty until we get customer sourcing
NULL, -- This is empty until we get customer sourcing
msc_int_source2.enable_date,
msc_int_source2.disable_date,
sysdate,
'-1',
sysdate,
'-1'
FROM MSC_SR_ASSIGNMENTS, msc_int_source2
WHERE
MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar and
MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID || ':' || MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID = msc_int_source2.item_code and
MSC_SR_ASSIGNMENTS.SR_INSTANCE_ID || ':' || MSC_SR_ASSIGNMENTS.ORGANIZATION_ID = msc_int_source2.branch_code;
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001007';
INSERT into MSC_SR_SOURCE_ORG (
SR_SOURCE_ID,
SR_SR_SOURCE_ID,
SR_RECEIPT_ID,
SOURCE_PARTNER_ID,
SOURCE_PARTNER_SITE_ID,
SR_INSTANCE_ID,
SOURCE_ORGANIZATION_ID,
SOURCE_ORG_INSTANCE_ID,
SHIP_METHOD,
ALLOCATION_PERCENT,
RANK,
SOURCE_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
SELECT
MSC_SR_SOURCE_ORG_S.NEXTVAL,
-1 * MSC_SR_SOURCE_ORG_S.NEXTVAL,
MSC_SR_RECEIPT_ORG.SR_RECEIPT_ID,
case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
then substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1)
else NULL
end,
case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
then substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )
else NULL
end,
MSC_SR_RECEIPT_ORG.SR_INSTANCE_ID,
case when not MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
then substr( MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code, ':', 1,1) + 1 )
else NULL
end,
case when not MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier'
then substr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,1,instr(MSC_INT_SRC_RECOMMEND_DETAIL.origin_code,':',1,1)-1)
else NULL
end,
case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Internal'
then MSC_INT_SRC_RECOMMEND_DETAIL.transport_mode_code
end,
NVL(MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_percent,100),
MSC_INT_SRC_RECOMMEND_DETAIL.preference,
case when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Internal' then 1
when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Manufactured' then 2
when MSC_INT_SRC_RECOMMEND_DETAIL.sourcing_type = 'Supplier' then 3
else 1
end,
sysdate,
'-1',
sysdate,
'-1'
FROM MSC_SR_ASSIGNMENTS, MSC_SR_RECEIPT_ORG, MSC_INT_SRC_RECOMMEND_DETAIL
WHERE MSC_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID = assignmentSetOutIdVar
AND MSC_SR_ASSIGNMENTS.SOURCING_RULE_ID = MSC_SR_RECEIPT_ORG.SOURCING_RULE_ID
AND MSC_SR_RECEIPT_ORG.SR_RECEIPT_ORG = MSC_SR_ASSIGNMENTS.ORGANIZATION_ID
AND MSC_SR_RECEIPT_ORG.RECEIPT_ORG_INSTANCE_ID || ':' || MSC_SR_RECEIPT_ORG.SR_RECEIPT_ORG = MSC_INT_SRC_RECOMMEND_DETAIL.destination_code
AND MSC_SR_RECEIPT_ORG.EFFECTIVE_DATE = TO_DATE( MSC_INT_SRC_RECOMMEND_DETAIL.start_date, 'YYYY-MM-DD' )
AND MSC_SR_ASSIGNMENTS.INVENTORY_ITEM_ID = substr(MSC_INT_SRC_RECOMMEND_DETAIL.item_code,instr(MSC_INT_SRC_RECOMMEND_DETAIL.item_code,':',1)+1)
AND MSC_INT_SRC_RECOMMEND_DETAIL.available = 'Yes';
g_ErrorCode := 'ERROR_UPDATE_MSC_INT_SRC_RECOMMEND_DETAIL_001007';
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;