[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_LAST_UPDATED_BY NUMBER
,p_LAST_UPDATE_DATE DATE
,p_LAST_UPDATE_LOGIN NUMBER
,p_FORECAST_RULE_ID NUMBER
,p_ORGANIZATION_TYPE VARCHAR2
,P_ORGANIZATION_ID NUMBER
,p_SECONDARY_INVENTORY VARCHAR2
,p_SERVICE_LEVEL NUMBER
,p_EDQ_FACTOR NUMBER
,p_PLANNER_CODE VARCHAR2
,p_CONDITION_TYPE VARCHAR2
,p_ASL_FLAG VARCHAR2
,P_SAFETY_STOCK_FLAG VARCHAR2
,P_AUTORECEIPT_FLAG VARCHAR2
,p_EXCESS_RULE_ID NUMBER
,p_NOTIFICATION_RULE_ID NUMBER
,p_RECOMMENDATION_RULE_ID NUMBER
,p_ATTRIBUTE_CATEGORY VARCHAR2
,p_ATTRIBUTE1 VARCHAR2
,p_ATTRIBUTE2 VARCHAR2
,p_ATTRIBUTE3 VARCHAR2
,p_ATTRIBUTE4 VARCHAR2
,p_ATTRIBUTE5 VARCHAR2
,p_ATTRIBUTE6 VARCHAR2
,p_ATTRIBUTE7 VARCHAR2
,p_ATTRIBUTE8 VARCHAR2
,p_ATTRIBUTE9 VARCHAR2
,p_ATTRIBUTE10 VARCHAR2
,p_ATTRIBUTE11 VARCHAR2
,p_ATTRIBUTE12 VARCHAR2
,p_ATTRIBUTE13 VARCHAR2
,p_ATTRIBUTE14 VARCHAR2
,p_ATTRIBUTE15 VARCHAR2
,p_CATEGORY_SET_ID NUMBER
,p_CATEGORY_ID NUMBER
,p_RECOMMEND_METHOD VARCHAR2
,p_EDQ_MULTIPLE NUMBER
,p_MINIMUM_VALUE NUMBER
,p_USABLE_ASSIGNMENT_SET_ID NUMBER
,p_DEFECTIVE_ASSIGNMENT_SET_ID NUMBER
,p_REPAIR_ASSIGNMENT_SET_ID NUMBER
,p_PRODUCT_NORM VARCHAR2
,P_PRODUCT_NORM_NODE_ID NUMBER
,P_USAGE_WEIGHT1 NUMBER
,P_USAGE_WEIGHT2 NUMBER
,P_USAGE_WEIGHT3 NUMBER
,P_USAGE_WEIGHT4 NUMBER
,p_DEFAULT_FORECAST_RULE_ID VARCHAR2
,p_DEFAULT_SERVICE_LEVEL VARCHAR2
,p_DEFAULT_EDQ_FACTOR VARCHAR2
,p_DEFAULT_PLANNER_CODE VARCHAR2
,p_DEFAULT_CONDITION_TYPE VARCHAR2
,p_DEFAULT_ASL_FLAG VARCHAR2
,p_DEFAULT_SAFETY_STOCK_FLAG VARCHAR2
,p_DEFAULT_EXCESS_RULE_ID VARCHAR2
,p_DEFAULT_CATEGORY_SET_ID VARCHAR2
,p_DEFAULT_CATEGORY_ID VARCHAR2
,p_DEFAULT_RECOMMEND_METHOD VARCHAR2
,P_DEFAULT_PRODUCT_NORM_NODE_ID VARCHAR2
,P_DEFAULT_USAGE_WEIGHT1 VARCHAR2
,P_DEFAULT_USAGE_WEIGHT2 VARCHAR2
,P_DEFAULT_USAGE_WEIGHT3 VARCHAR2
,P_DEFAULT_USAGE_WEIGHT4 VARCHAR2
,P_DEFAULT_REC_RULE_ID VARCHAR2
,p_OVERRIDE_FORECAST_RULE_ID VARCHAR2
,p_OVERRIDE_SERVICE_LEVEL VARCHAR2
,p_OVERRIDE_EDQ_FACTOR VARCHAR2
,p_OVERRIDE_PLANNER_CODE VARCHAR2
,p_OVERRIDE_CONDITION_TYPE VARCHAR2
,p_OVERRIDE_ASL_FLAG VARCHAR2
,p_OVERRIDE_SAFETY_STOCK_FLAG VARCHAR2
,p_OVERRIDE_EXCESS_RULE_ID VARCHAR2
,p_OVERRIDE_CATEGORY_SET_ID VARCHAR2
,p_OVERRIDE_CATEGORY_ID VARCHAR2
,p_OVERRIDE_RECOMMEND_METHOD VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT1 VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT2 VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT3 VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT4 VARCHAR2
,P_OVERRIDE_REC_RULE_ID VARCHAR2
,p_NODE_TYPE VARCHAR2
,p_NODE_NAME VARCHAR2
,p_PARENT_NODE_ID NUMBER
,p_LEVEL_ID VARCHAR2
,p_EXCESS_EDQ_FACTOR VARCHAR2
,p_REPAIR_EDQ_FACTOR VARCHAR2
,p_NEWBUY_EDQ_FACTOR VARCHAR2
,p_EXCESS_SERVICE_LEVEL VARCHAR2
,p_REPAIR_SERVICE_LEVEL VARCHAR2
,p_NEWBUY_SERVICE_LEVEL VARCHAR2
,p_RESCHEDULE_RULE_ID NUMBER
,p_WRP_RULE_ID NUMBER
,P_STOCKING_SITE_TYPE VARCHAR2
,P_CALENDAR_ID NUMBER
,P_TIMEZONE_ID NUMBER
,P_MANAGED_BY VARCHAR2
,P_CONTACT_NAME VARCHAR2
,P_CONTACT_PHONE VARCHAR2
,P_AH_CONTACT_NAME VARCHAR2
,P_AH_CONTACT_PHONE VARCHAR2
,P_STOCKING_SITE_EXCL VARCHAR2
,P_SPECIAL_INSTRUCTIONS VARCHAR2
,p_hz_location_id number ) is
l_planning_parameters_id number;
L_LAST_UPDATE_DATE DATE ;
Select secondary_inventory_name
from mtl_secondary_inventories
where organization_id = L_ORGANIZATION_ID
and nvl(Disable_date,Sysdate+1) > Sysdate
and secondary_inventory_name not in
(Select secondary_inventory from csp_planning_parameters
where organization_id = L_ORGANIZATION_ID)
and rownum =1;
Select secondary_inventory_name
from mtl_secondary_inventories
where organization_id = L_ORGANIZATION_ID
AND SECONDARY_INVENTORY_NAME <> P_SECONDARY_INVENTORY
and nvl(Disable_date,Sysdate+1) > Sysdate
and secondary_inventory_name not in
(Select secondary_inventory from csp_planning_parameters
where organization_id = L_ORGANIZATION_ID);
CURSOR C2 IS SELECT CSP_PLANNING_PARAMETERS_S1.nextval FROM sys.dual;
SELECT SECONDARY_INVENTORY_ID
FROM CSP_SEC_INVENTORIES_V
WHERE organization_id = l_organization_id
AND secondary_inventory_name = l_secondary_inventory;
Csp_Planning_Parameters_pkg.Insert_Row (
px_PLANNING_PARAMETERS_ID => l_Planning_Parameters_Id,
p_FORECAST_RULE_ID => p_Forecast_Rule_Id,
p_ORGANIZATION_ID => p_Organization_Id,
p_SECONDARY_INVENTORY => p_secondary_inventory,
p_SERVICE_LEVEL => p_service_level,
p_EDQ_FACTOR => p_edq_factor,
p_PLANNER_CODE => p_planner_code,
p_CONDITION_TYPE => p_condition_type,
p_ORGANIZATION_TYPE => p_organization_type,
p_ASL_FLAG => p_asl_flag,
p_safety_stock_flag => p_safety_stock_flag,
p_excess_rule_id => to_number(p_Excess_Rule_Id),
p_RECOMMENDATION_RULE_ID => p_Recommendation_Rule_Id,
P_NOTIFICATION_RULE_ID => P_NOTIFICATION_RULE_ID,
p_ATTRIBUTE1 => p_Attribute1,
p_ATTRIBUTE2 => p_Attribute2,
p_ATTRIBUTE3 => p_Attribute3,
p_ATTRIBUTE4 => p_Attribute4,
p_ATTRIBUTE5 => p_Attribute5,
p_ATTRIBUTE6 => p_Attribute6,
p_ATTRIBUTE7 => p_Attribute7,
p_ATTRIBUTE8 => p_Attribute8,
p_ATTRIBUTE9 => p_Attribute9,
p_ATTRIBUTE10 => p_Attribute10,
p_ATTRIBUTE11 => p_Attribute11,
p_ATTRIBUTE12 => p_Attribute12,
p_ATTRIBUTE13 => p_Attribute13,
p_ATTRIBUTE14 => p_Attribute14,
p_ATTRIBUTE15 => p_Attribute15,
P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
P_CREATED_BY => P_CREATED_BY,
p_CREATION_DATE => p_Creation_Date,
p_LAST_UPDATED_BY => p_Last_Updated_By,
p_LAST_UPDATE_DATE => p_Last_Update_Date,
p_LAST_UPDATE_LOGIN => p_Last_Update_Login
,p_CATEGORY_SET_ID => p_CATEGORY_SET_ID
,p_CATEGORY_ID => p_CATEGORY_ID
,p_RECOMMEND_METHOD => p_RECOMMEND_METHOD
,p_EDQ_MULTIPLE => p_EDQ_MULTIPLE
,p_MINIMUM_VALUE => p_MINIMUM_VALUE
,p_USABLE_ASSIGNMENT_SET_ID => p_USABLE_ASSIGNMENT_SET_ID
,p_DEFECTIVE_ASSIGNMENT_SET_ID =>p_DEFECTIVE_ASSIGNMENT_SET_ID
,p_REPAIR_ASSIGNMENT_SET_ID => p_REPAIR_ASSIGNMENT_SET_ID
,p_PRODUCT_NORM => p_PRODUCT_NORM
,p_PRODUCT_NORM_NODE_ID => p_PRODUCT_NORM_NODE_ID
,p_USAGE_WEIGHT1 => p_USAGE_WEIGHT1
,p_USAGE_WEIGHT2 => p_USAGE_WEIGHT2
,p_USAGE_WEIGHT3 => p_USAGE_WEIGHT3
,p_USAGE_WEIGHT4 => p_USAGE_WEIGHT4
,p_DEFAULT_FORECAST_RULE_ID => p_DEFAULT_FORECAST_RULE_ID
,p_DEFAULT_SERVICE_LEVEL => p_DEFAULT_SERVICE_LEVEL
,p_DEFAULT_EDQ_FACTOR => p_DEFAULT_EDQ_FACTOR
,p_DEFAULT_PLANNER_CODE => p_DEFAULT_PLANNER_CODE
,p_DEFAULT_CONDITION_TYPE => p_DEFAULT_CONDITION_TYPE
,p_DEFAULT_ASL_FLAG => p_DEFAULT_ASL_FLAG
,p_DEFAULT_SAFETY_STOCK_FLAG => p_DEFAULT_SAFETY_STOCK_FLAG
,p_DEFAULT_EXCESS_RULE_ID => p_DEFAULT_EXCESS_RULE_ID
,p_DEFAULT_REC_RULE_ID => p_DEFAULT_REC_RULE_ID
,p_DEFAULT_CATEGORY_SET_ID => p_DEFAULT_CATEGORY_SET_ID
,p_DEFAULT_CATEGORY_ID => p_DEFAULT_CATEGORY_ID
,p_DEFAULT_RECOMMEND_METHOD => p_DEFAULT_RECOMMEND_METHOD
,p_DEFAULT_PRODUCT_NORM_NODE_ID => p_DEFAULT_PRODUCT_NORM_NODE_ID
,p_DEFAULT_USAGE_WEIGHT1 => p_DEFAULT_USAGE_WEIGHT1
,p_DEFAULT_USAGE_WEIGHT2 => p_DEFAULT_USAGE_WEIGHT2
,p_DEFAULT_USAGE_WEIGHT3 => p_DEFAULT_USAGE_WEIGHT3
,p_DEFAULT_USAGE_WEIGHT4 =>p_DEFAULT_USAGE_WEIGHT4
,p_OVERRIDE_FORECAST_RULE_ID => p_OVERRIDE_FORECAST_RULE_ID
,p_OVERRIDE_SERVICE_LEVEL => p_OVERRIDE_SERVICE_LEVEL
,p_OVERRIDE_EDQ_FACTOR => p_OVERRIDE_EDQ_FACTOR
,p_OVERRIDE_PLANNER_CODE => p_OVERRIDE_PLANNER_CODE
,p_OVERRIDE_CONDITION_TYPE => p_OVERRIDE_CONDITION_TYPE
,p_OVERRIDE_ASL_FLAG => p_OVERRIDE_ASL_FLAG
,p_OVERRIDE_SAFETY_STOCK_FLAG => p_OVERRIDE_SAFETY_STOCK_FLAG
,p_OVERRIDE_EXCESS_RULE_ID => p_OVERRIDE_EXCESS_RULE_ID
,p_OVERRIDE_REC_RULE_ID => p_OVERRIDE_REC_RULE_ID
,p_OVERRIDE_CATEGORY_SET_ID => p_OVERRIDE_CATEGORY_SET_ID
,p_OVERRIDE_CATEGORY_ID => p_OVERRIDE_CATEGORY_ID
,p_OVERRIDE_RECOMMEND_METHOD => p_OVERRIDE_RECOMMEND_METHOD
,p_OVERRIDE_USAGE_WEIGHT1 => p_OVERRIDE_USAGE_WEIGHT1
,p_OVERRIDE_USAGE_WEIGHT2 => p_OVERRIDE_USAGE_WEIGHT2
,p_OVERRIDE_USAGE_WEIGHT3 => p_OVERRIDE_USAGE_WEIGHT3
,p_OVERRIDE_USAGE_WEIGHT4 => p_OVERRIDE_USAGE_WEIGHT4
,p_NODE_TYPE => p_Node_Type,
p_NODE_NAME => p_NODE_NAME,
p_PARENT_NODE_ID => p_PARENT_NODE_ID,
p_LEVEL_ID => p_LEVEL_ID,
p_EXCESS_SERVICE_LEVEL => p_excess_service_level,
p_REPAIR_SERVICE_LEVEL =>p_repair_service_level,
p_NEWBUY_SERVICE_LEVEL => p_newbuy_service_level,
p_EXCESS_EDQ_FACTOR => p_excess_edq_factor,
p_REPAIR_EDQ_FACTOR => p_repair_edq_factor,
p_NEWBUY_EDQ_FACTOR => p_newbuy_edq_factor,
p_RESCHEDULE_RULE_ID => p_reschedule_rule_id,
p_WRP_RULE_ID => p_wrp_rule_id,
p_stocking_site_type => p_stocking_site_type,
p_CALENDAR_ID => p_CALENDAR_ID,
p_TIMEZONE_ID => p_TIMEZONE_ID,
p_MANAGED_BY => p_MANAGED_BY,
p_CONTACT_NAME => p_CONTACT_NAME,
p_CONTACT_PHONE => p_CONTACT_PHONE,
p_AH_CONTACT_NAME => p_AH_CONTACT_NAME,
p_AH_CONTACT_PHONE => p_AH_CONTACT_PHONE,
p_STOCKING_SITE_EXCL => p_STOCKING_SITE_EXCL,
p_SPECIAL_INSTRUCTIONS => p_SPECIAL_INSTRUCTIONS,
p_HZ_LOCATION_ID => p_HZ_LOCATION_ID);
UPDATE CSP_USAGE_HEADERS
set planning_parameters_id = l_planning_parameters_id
WHERE HEADER_DATA_TYPE = 1
AND SECONDARY_INVENTORY = L_SECONDARY_INVENTORY
And Organization_Id = l_Organization_Id;
CSP_SEC_INVENTORIES_PKG.Insert_Row(
L_SECONDARY_INVENTORY_ID
,p_created_by
,p_CREATION_DATE
,P_LAST_UPDATED_BY
,p_LAST_UPDATE_DATE
,P_LAST_UPDATE_LOGIN
,p_ORGANIZATION_ID
,NULL
,NULL
,L_SECONDARY_INVENTORY
,NULL
,P_CONDITION_TYPE
,NVL(p_AUTORECEIPT_FLAG,'Y')
,'Y'
,NULL
,null
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);
UPDATE CSP_SEC_INVENTORIES SET CONDITION_TYPE = P_CONDITION_TYPE,
AUTORECEIPT_FLAG = NVL(p_AUTORECEIPT_FLAG,'Y')
Where SECONDARY_INVENTORY_NAME = L_SECONDARY_INVENTORY
And ORGANIZATION_ID = l_organization_id
AND SECONDARY_INVENTORY_ID = l_sec_inv_id;
UPDATE CSP_USAGE_HEADERS
set planning_parameters_id = l_planning_parameters_id
WHERE HEADER_DATA_TYPE IN (1,4)
And Organization_Id = l_Organization_Id
and Secondary_Inventory = '-';
procedure update_planning_parameters(
p_PLANNING_PARAMETERS_ID NUMBER
,p_CREATED_BY NUMBER
,p_CREATION_DATE DATE
,p_LAST_UPDATED_BY NUMBER
,p_LAST_UPDATE_DATE DATE
,p_LAST_UPDATE_LOGIN NUMBER
,p_FORECAST_RULE_ID NUMBER
,p_ORGANIZATION_TYPE VARCHAR2
,p_ORGANIZATION_ID NUMBER
,p_SECONDARY_INVENTORY VARCHAR2
,p_SERVICE_LEVEL NUMBER
,p_EDQ_FACTOR NUMBER
,p_PLANNER_CODE VARCHAR2
,p_CONDITION_TYPE VARCHAR2
,p_ASL_FLAG VARCHAR2
,P_SAFETY_STOCK_FLAG VARCHAR2
,p_EXCESS_RULE_ID NUMBER
,p_NOTIFICATION_RULE_ID NUMBER
,p_RECOMMENDATION_RULE_ID NUMBER
,p_ATTRIBUTE_CATEGORY VARCHAR2
,p_ATTRIBUTE1 VARCHAR2
,p_ATTRIBUTE2 VARCHAR2
,p_ATTRIBUTE3 VARCHAR2
,p_ATTRIBUTE4 VARCHAR2
,p_ATTRIBUTE5 VARCHAR2
,p_ATTRIBUTE6 VARCHAR2
,p_ATTRIBUTE7 VARCHAR2
,p_ATTRIBUTE8 VARCHAR2
,p_ATTRIBUTE9 VARCHAR2
,p_ATTRIBUTE10 VARCHAR2
,p_ATTRIBUTE11 VARCHAR2
,p_ATTRIBUTE12 VARCHAR2
,p_ATTRIBUTE13 VARCHAR2
,p_ATTRIBUTE14 VARCHAR2
,p_ATTRIBUTE15 VARCHAR2
,p_CATEGORY_SET_ID NUMBER
,p_CATEGORY_ID NUMBER
,p_RECOMMEND_METHOD VARCHAR2
,p_EDQ_MULTIPLE NUMBER
,p_MINIMUM_VALUE NUMBER
,p_USABLE_ASSIGNMENT_SET_ID NUMBER
,p_DEFECTIVE_ASSIGNMENT_SET_ID NUMBER
,p_REPAIR_ASSIGNMENT_SET_ID NUMBER
,p_PRODUCT_NORM VARCHAR2
,P_PRODUCT_NORM_NODE_ID NUMBER
,P_USAGE_WEIGHT1 NUMBER
,P_USAGE_WEIGHT2 NUMBER
,P_USAGE_WEIGHT3 NUMBER
,P_USAGE_WEIGHT4 NUMBER
,p_DEFAULT_FORECAST_RULE_ID VARCHAR2
,p_DEFAULT_SERVICE_LEVEL VARCHAR2
,p_DEFAULT_EDQ_FACTOR VARCHAR2
,p_DEFAULT_PLANNER_CODE VARCHAR2
,p_DEFAULT_CONDITION_TYPE VARCHAR2
,p_DEFAULT_ASL_FLAG VARCHAR2
,p_DEFAULT_SAFETY_STOCK_FLAG VARCHAR2
,p_DEFAULT_EXCESS_RULE_ID VARCHAR2
,p_DEFAULT_CATEGORY_SET_ID VARCHAR2
,p_DEFAULT_CATEGORY_ID VARCHAR2
,p_DEFAULT_RECOMMEND_METHOD VARCHAR2
,P_DEFAULT_PRODUCT_NORM_NODE_ID VARCHAR2
,P_DEFAULT_USAGE_WEIGHT1 VARCHAR2
,P_DEFAULT_USAGE_WEIGHT2 VARCHAR2
,P_DEFAULT_USAGE_WEIGHT3 VARCHAR2
,P_DEFAULT_USAGE_WEIGHT4 VARCHAR2
,P_DEFAULT_REC_RULE_ID VARCHAR2
,p_OVERRIDE_FORECAST_RULE_ID VARCHAR2
,p_OVERRIDE_SERVICE_LEVEL VARCHAR2
,p_OVERRIDE_EDQ_FACTOR VARCHAR2
,p_OVERRIDE_PLANNER_CODE VARCHAR2
,p_OVERRIDE_CONDITION_TYPE VARCHAR2
,p_OVERRIDE_ASL_FLAG VARCHAR2
,p_OVERRIDE_SAFETY_STOCK_FLAG VARCHAR2
,p_OVERRIDE_EXCESS_RULE_ID VARCHAR2
,p_OVERRIDE_CATEGORY_SET_ID VARCHAR2
,p_OVERRIDE_CATEGORY_ID VARCHAR2
,p_OVERRIDE_RECOMMEND_METHOD VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT1 VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT2 VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT3 VARCHAR2
,P_OVERRIDE_USAGE_WEIGHT4 VARCHAR2
,P_OVERRIDE_REC_RULE_ID VARCHAR2
,p_NODE_TYPE VARCHAR2
,p_NODE_NAME VARCHAR2
,p_PARENT_NODE_ID NUMBER
,p_LEVEL_ID VARCHAR2
,p_EXCESS_EDQ_FACTOR VARCHAR2
,p_REPAIR_EDQ_FACTOR VARCHAR2
,p_NEWBUY_EDQ_FACTOR VARCHAR2
,p_EXCESS_SERVICE_LEVEL VARCHAR2
,p_REPAIR_SERVICE_LEVEL VARCHAR2
,p_NEWBUY_SERVICE_LEVEL VARCHAR2
,p_RESCHEDULE_RULE_ID NUMBER
,p_WRP_RULE_ID NUMBER
,P_STOCKING_SITE_TYPE VARCHAR2
,P_CALENDAR_ID NUMBER
,P_TIMEZONE_ID NUMBER
,P_MANAGED_BY VARCHAR2
,P_CONTACT_NAME VARCHAR2
,P_CONTACT_PHONE VARCHAR2
,P_AH_CONTACT_NAME VARCHAR2
,P_AH_CONTACT_PHONE VARCHAR2
,P_STOCKING_SITE_EXCL VARCHAR2
,P_SPECIAL_INSTRUCTIONS VARCHAR2
,p_hz_location_id number) is
L_CREATED_BY NUMBER;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATE_LOGIN NUMBER;
Select product_norm
,FORECAST_RULE_ID
,EXCESS_RULE_ID
,RECOMMENDATION_RULE_ID
,SERVICE_LEVEL
,EDQ_FACTOR
,ASL_FLAG
,SAFETY_STOCK_FLAG
,CONDITION_TYPE
,PLANNER_CODE
,CATEGORY_SET_ID
,CATEGORY_ID
,RECOMMEND_METHOD
,PRODUCT_NORM_NODE_ID
,USAGE_WEIGHT1
,USAGE_WEIGHT2
,USAGE_WEIGHT3
,USAGE_WEIGHT4
,default_FORECAST_RULE_ID
,default_EXCESS_RULE_ID
,default_REC_RULE_ID
,default_SERVICE_LEVEL
,default_EDQ_FACTOR
,default_ASL_FLAG
,default_SAFETY_STOCK_FLAG
,default_CONDITION_TYPE
,default_PLANNER_CODE
,default_CATEGORY_SET_ID
,default_CATEGORY_ID
,default_RECOMMEND_METHOD
,DEFAULT_PRODUCT_NORM_NODE_ID
,DEFAULT_USAGE_WEIGHT1
,DEFAULT_USAGE_WEIGHT2
,DEFAULT_USAGE_WEIGHT3
,DEFAULT_USAGE_WEIGHT4
,override_FORECAST_RULE_ID
,override_EXCESS_RULE_ID
,override_REC_RULE_ID
,override_SERVICE_LEVEL
,override_EDQ_FACTOR
,override_ASL_FLAG
,override_SAFETY_STOCK_FLAG
,override_CONDITION_TYPE
,override_PLANNER_CODE
,override_CATEGORY_SET_ID
,override_CATEGORY_ID
,override_RECOMMEND_METHOD
,OVERRIDE_USAGE_WEIGHT1
,OVERRIDE_USAGE_WEIGHT2
,OVERRIDE_USAGE_WEIGHT3
,OVERRIDE_USAGE_WEIGHT4
,NODE_NAME
into
L_PRODUCT_NORM
,L_FORECAST_RULE_ID
,l_EXCESS_RULE_ID
,l_RECOMMENDATION_RULE_ID
,l_SERVICE_LEVEL
,l_EDQ_FACTOR
,l_ASL_FLAG
,L_SAFETY_STOCK_FLAG
,l_CONDITION_TYPE
,l_PLANNER_CODE
,l_CATEGORY_SET_ID
,l_CATEGORY_ID
,l_RECOMMEND_METHOD
,l_PRODUCT_NORM_NODE_ID
,l_USAGE_WEIGHT1
,l_USAGE_WEIGHT2
,l_USAGE_WEIGHT3
,l_USAGE_WEIGHT4
,l_default_FORECAST_RULE_ID
,l_default_EXCESS_RULE_ID
,l_default_REC_RULE_ID
,l_default_SERVICE_LEVEL
,l_default_EDQ_FACTOR
,l_default_ASL_FLAG
,l_default_SAFETY_STOCK_FLAG
,l_default_CONDITION_TYPE
,l_default_PLANNER_CODE
,l_default_CATEGORY_SET_ID
,l_default_CATEGORY_ID
,l_default_RECOMMEND_METHOD
,l_DEFAULT_PRODUCT_NORM_NODE_ID
,l_DEFAULT_USAGE_WEIGHT1
,l_DEFAULT_USAGE_WEIGHT2
,l_DEFAULT_USAGE_WEIGHT3
,l_DEFAULT_USAGE_WEIGHT4
,L_OVERRIDE_FORECAST_RULE_ID
,l_override_EXCESS_RULE_ID
,l_override_REC_RULE_ID
,l_override_SERVICE_LEVEL
,l_override_EDQ_FACTOR
,l_override_ASL_FLAG
,l_override_SAFETY_STOCK_FLAG
,l_override_CONDITION_TYPE
,l_override_PLANNER_CODE
,l_override_CATEGORY_SET_ID
,l_override_CATEGORY_ID
,l_override_RECOMMEND_METHOD
,l_OVERRIDE_USAGE_WEIGHT1
,l_OVERRIDE_USAGE_WEIGHT2
,l_OVERRIDE_USAGE_WEIGHT3
,L_OVERRIDE_USAGE_WEIGHT4
,l_node_name
From CSP_PLANNING_PARAMETERS
WHERE planning_parameters_id = l_planning_parameters_id;
Csp_Planning_Parameters_pkg.Update_Row (
p_PLANNING_PARAMETERS_ID => p_Planning_Parameters_Id,
p_FORECAST_RULE_ID => p_Forecast_Rule_Id,
p_ORGANIZATION_ID => p_Organization_Id,
p_SECONDARY_INVENTORY => p_secondary_inventory,
p_SERVICE_LEVEL => p_service_level,
p_EDQ_FACTOR => p_edq_factor,
p_PLANNER_CODE => p_planner_code,
p_CONDITION_TYPE => p_condition_type,
p_ORGANIZATION_TYPE => p_organization_type,
p_ASL_FLAG => p_asl_flag,
p_SAFETY_STOCK_FLAG => p_safety_stock_flag,
p_EXCESS_RULE_ID => p_Excess_Rule_Id,
p_recommendation_rule_id => p_recommendation_rule_id,
p_NOTIFICATION_RULE_ID => p_NOTIFICATION_RULE_ID,
p_ATTRIBUTE1 => p_Attribute1,
p_ATTRIBUTE2 => p_Attribute2,
p_ATTRIBUTE3 => p_Attribute3,
p_ATTRIBUTE4 => p_Attribute4,
p_ATTRIBUTE5 => p_Attribute5,
p_ATTRIBUTE6 => p_Attribute6,
p_ATTRIBUTE7 => p_Attribute7,
p_ATTRIBUTE8 => p_Attribute8,
p_ATTRIBUTE9 => p_Attribute9,
p_ATTRIBUTE10 => p_Attribute10,
p_ATTRIBUTE11 => p_Attribute11,
p_ATTRIBUTE12 => p_Attribute12,
p_ATTRIBUTE13 => p_Attribute13,
p_ATTRIBUTE14 => p_Attribute14,
p_ATTRIBUTE15 => p_Attribute15,
p_ATTRIBUTE_CATEGORY => p_Attribute_category,
p_CREATED_BY => p_Created_by,
p_CREATION_DATE => p_Creation_Date,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
p_LAST_UPDATE_DATE => p_Last_Update_Date,
p_LAST_UPDATE_LOGIN => p_Last_Update_Login
,p_CATEGORY_SET_ID => p_CATEGORY_SET_ID
,p_CATEGORY_ID => p_CATEGORY_ID
,p_RECOMMEND_METHOD => p_RECOMMEND_METHOD
,p_EDQ_MULTIPLE => p_EDQ_MULTIPLE
,p_MINIMUM_VALUE => p_MINIMUM_VALUE
,p_USABLE_ASSIGNMENT_SET_ID => p_USABLE_ASSIGNMENT_SET_ID
,p_DEFECTIVE_ASSIGNMENT_SET_ID =>p_DEFECTIVE_ASSIGNMENT_SET_ID
,p_REPAIR_ASSIGNMENT_SET_ID => p_REPAIR_ASSIGNMENT_SET_ID
,p_PRODUCT_NORM => p_PRODUCT_NORM
,p_PRODUCT_NORM_NODE_ID => p_PRODUCT_NORM_NODE_ID
,p_USAGE_WEIGHT1 => p_USAGE_WEIGHT1
,P_USAGE_WEIGHT2 => P_USAGE_WEIGHT2
,p_USAGE_WEIGHT3 => p_USAGE_WEIGHT3
,p_USAGE_WEIGHT4 => p_USAGE_WEIGHT4
,p_DEFAULT_FORECAST_RULE_ID => p_DEFAULT_FORECAST_RULE_ID
,p_DEFAULT_SERVICE_LEVEL => p_DEFAULT_SERVICE_LEVEL
,p_DEFAULT_EDQ_FACTOR => p_DEFAULT_EDQ_FACTOR
,p_DEFAULT_PLANNER_CODE => p_DEFAULT_PLANNER_CODE
,p_DEFAULT_CONDITION_TYPE => p_DEFAULT_CONDITION_TYPE
,p_DEFAULT_ASL_FLAG => p_DEFAULT_ASL_FLAG
,p_DEFAULT_SAFETY_STOCK_FLAG => p_DEFAULT_SAFETY_STOCK_FLAG
,p_DEFAULT_EXCESS_RULE_ID => p_DEFAULT_EXCESS_RULE_ID
,p_DEFAULT_REC_RULE_ID => p_DEFAULT_REC_RULE_ID
,p_DEFAULT_CATEGORY_SET_ID => p_DEFAULT_CATEGORY_SET_ID
,p_DEFAULT_CATEGORY_ID => p_DEFAULT_CATEGORY_ID
,p_DEFAULT_RECOMMEND_METHOD => p_DEFAULT_RECOMMEND_METHOD
,p_DEFAULT_PRODUCT_NORM_NODE_ID => p_DEFAULT_PRODUCT_NORM_NODE_ID
,p_DEFAULT_USAGE_WEIGHT1 => p_DEFAULT_USAGE_WEIGHT1
,p_DEFAULT_USAGE_WEIGHT2 => p_DEFAULT_USAGE_WEIGHT2
,p_DEFAULT_USAGE_WEIGHT3 => p_DEFAULT_USAGE_WEIGHT3
,p_DEFAULT_USAGE_WEIGHT4 => p_DEFAULT_USAGE_WEIGHT4
,p_OVERRIDE_FORECAST_RULE_ID => p_OVERRIDE_FORECAST_RULE_ID
,p_OVERRIDE_SERVICE_LEVEL => p_OVERRIDE_SERVICE_LEVEL
,p_OVERRIDE_EDQ_FACTOR => p_OVERRIDE_EDQ_FACTOR
,p_OVERRIDE_PLANNER_CODE => p_OVERRIDE_PLANNER_CODE
,p_OVERRIDE_CONDITION_TYPE => p_OVERRIDE_CONDITION_TYPE
,p_OVERRIDE_ASL_FLAG => p_OVERRIDE_ASL_FLAG
,p_OVERRIDE_SAFETY_STOCK_FLAG => p_OVERRIDE_SAFETY_STOCK_FLAG
,p_OVERRIDE_EXCESS_RULE_ID => p_OVERRIDE_EXCESS_RULE_ID
,p_OVERRIDE_REC_RULE_ID => p_OVERRIDE_REC_RULE_ID
,p_OVERRIDE_CATEGORY_SET_ID => p_OVERRIDE_CATEGORY_SET_ID
,p_OVERRIDE_CATEGORY_ID => p_OVERRIDE_CATEGORY_ID
,p_OVERRIDE_RECOMMEND_METHOD => p_OVERRIDE_RECOMMEND_METHOD
,p_OVERRIDE_USAGE_WEIGHT1 => p_OVERRIDE_USAGE_WEIGHT1
,p_OVERRIDE_USAGE_WEIGHT2 => p_OVERRIDE_USAGE_WEIGHT2
,p_OVERRIDE_USAGE_WEIGHT3 => p_OVERRIDE_USAGE_WEIGHT3
,p_OVERRIDE_USAGE_WEIGHT4 => p_OVERRIDE_USAGE_WEIGHT4
,p_NODE_TYPE => p_Node_Type,
p_NODE_NAME => p_NODE_NAME,
p_PARENT_NODE_ID => p_PARENT_NODE_ID,
p_LEVEL_ID => p_LEVEL_ID,
p_EXCESS_SERVICE_LEVEL => p_excess_service_level,
p_REPAIR_SERVICE_LEVEL =>p_repair_service_level,
p_NEWBUY_SERVICE_LEVEL => p_newbuy_service_level,
p_EXCESS_EDQ_FACTOR => p_excess_edq_factor,
p_REPAIR_EDQ_FACTOR => p_repair_edq_factor,
p_NEWBUY_EDQ_FACTOR => p_newbuy_edq_factor,
p_RESCHEDULE_RULE_ID => p_reschedule_rule_id,
p_WRP_RULE_ID => p_wrp_rule_id,
p_stocking_site_type => p_stocking_site_type,
p_CALENDAR_ID => p_CALENDAR_ID,
p_TIMEZONE_ID => p_TIMEZONE_ID,
p_MANAGED_BY => p_MANAGED_BY,
p_CONTACT_NAME => p_CONTACT_NAME,
p_CONTACT_PHONE => p_CONTACT_PHONE,
p_AH_CONTACT_NAME => p_AH_CONTACT_NAME,
p_AH_CONTACT_PHONE => p_AH_CONTACT_PHONE,
p_STOCKING_SITE_EXCL => p_STOCKING_SITE_EXCL,
p_SPECIAL_INSTRUCTIONS => p_SPECIAL_INSTRUCTIONS,
P_HZ_LOCATION_ID => P_HZ_LOCATION_ID );
UPDATE CSP_PLANNING_PARAMETERS SET
PRODUCT_NORM_NODE_ID = Decode(DEFAULT_PRODUCT_NORM_NODE_ID,l_node_name,P_PRODUCT_NORM_NODE_ID,PRODUCT_NORM_NODE_ID)
,DEFAULT_PRODUCT_NORM_NODE_ID = DECODE(DEFAULT_PRODUCT_NORM_NODE_ID,L_NODE_NAME,P_DEFAULT_PRODUCT_NORM_NODE_ID,DEFAULT_PRODUCT_NORM_NODE_ID)
,FORECAST_RULE_ID = Decode(DEFAULT_FORECAST_RULE_ID,l_node_name,P_FORECAST_RULE_ID,FORECAST_RULE_ID)
,DEFAULT_FORECAST_RULE_ID = Decode(DEFAULT_FORECAST_RULE_ID,l_node_name,P_DEFAULT_FORECAST_RULE_ID,DEFAULT_FORECAST_RULE_ID)
,OVERRIDE_FORECAST_RULE_ID = DECODE(DEFAULT_FORECAST_RULE_ID,L_NODE_NAME,P_OVERRIDE_FORECAST_RULE_ID,OVERRIDE_FORECAST_RULE_ID)
,EXCESS_RULE_ID = Decode(DEFAULT_EXCESS_RULE_ID,l_node_name,P_EXCESS_RULE_ID,EXCESS_RULE_ID)
,DEFAULT_EXCESS_RULE_ID = Decode(DEFAULT_EXCESS_RULE_ID,l_node_name,P_DEFAULT_EXCESS_RULE_ID,DEFAULT_EXCESS_RULE_ID)
,OVERRIDE_EXCESS_RULE_ID = DECODE(DEFAULT_EXCESS_RULE_ID,L_NODE_NAME,P_OVERRIDE_EXCESS_RULE_ID,OVERRIDE_EXCESS_RULE_ID)
,RECOMMENDATION_RULE_ID = Decode(DEFAULT_REC_RULE_ID,l_node_name,P_RECOMMENDATION_RULE_ID,RECOMMENDATION_RULE_ID)
,DEFAULT_REC_RULE_ID = Decode(DEFAULT_REC_RULE_ID,l_node_name,P_DEFAULT_REC_RULE_ID,DEFAULT_REC_RULE_ID)
,OVERRIDE_REC_RULE_ID = DECODE(DEFAULT_REC_RULE_ID,L_NODE_NAME,P_OVERRIDE_REC_RULE_ID,OVERRIDE_REC_RULE_ID)
,SERVICE_LEVEL = Decode(DEFAULT_SERVICE_LEVEL,l_node_name,P_SERVICE_LEVEL,SERVICE_LEVEL)
,DEFAULT_SERVICE_LEVEL = Decode(DEFAULT_SERVICE_LEVEL,l_node_name,P_DEFAULT_SERVICE_LEVEL,DEFAULT_SERVICE_LEVEL)
,OVERRIDE_SERVICE_LEVEL = DECODE(DEFAULT_SERVICE_LEVEL,L_NODE_NAME,P_OVERRIDE_SERVICE_LEVEL,OVERRIDE_SERVICE_LEVEL)
,EDQ_FACTOR = Decode(DEFAULT_EDQ_FACTOR,l_node_name,P_Edq_Factor,EDQ_FACTOR)
,DEFAULT_EDQ_FACTOR = Decode(DEFAULT_EDQ_FACTOR,l_node_name,P_DEFAULT_Edq_Factor,DEFAULT_EDQ_FACTOR)
,OVERRIDE_EDQ_FACTOR = DECODE(DEFAULT_EDQ_FACTOR,L_NODE_NAME,P_OVERRIDE_EDQ_FACTOR,OVERRIDE_EDQ_FACTOR)
,ASL_FLAG = Decode(DEFAULT_ASL_FLAG,l_node_name,P_Asl_Flag,ASL_FLAG)
,DEFAULT_ASL_FLAG = Decode(DEFAULT_ASL_FLAG,l_node_name,P_DEFAULT_Asl_Flag,DEFAULT_ASL_FLAG)
,OVERRIDE_ASL_FLAG = DECODE(DEFAULT_ASL_FLAG,L_NODE_NAME,P_OVERRIDE_ASL_FLAG,OVERRIDE_ASL_FLAG)
,SAFETY_STOCK_FLAG = Decode(DEFAULT_SAFETY_STOCK_FLAG,l_node_name,P_Safety_Stock_Flag,SAFETY_STOCK_FLAG)
,DEFAULT_SAFETY_STOCK_FLAG = Decode(DEFAULT_SAFETY_STOCK_FLAG,l_node_name,p_DEFAULT_Safety_Stock_Flag,DEFAULT_SAFETY_STOCK_FLAG)
,OVERRIDE_SAFETY_STOCK_FLAG = DECODE(DEFAULT_SAFETY_STOCK_FLAG,L_NODE_NAME,P_OVERRIDE_SAFETY_STOCK_FLAG,OVERRIDE_SAFETY_STOCK_FLAG)
,CONDITION_TYPE = Decode(DEFAULT_CONDITION_TYPE,l_node_name,P_Condition_Type,CONDITION_TYPE)
,DEFAULT_CONDITION_TYPE = Decode(DEFAULT_CONDITION_TYPE,l_node_name,p_DEFAULT_Condition_Type,DEFAULT_CONDITION_TYPE)
,OVERRIDE_CONDITION_TYPE = DECODE(DEFAULT_CONDITION_TYPE,L_NODE_NAME,P_OVERRIDE_CONDITION_TYPE,OVERRIDE_CONDITION_TYPE)
,PLANNER_CODE = Decode(DEFAULT_PLANNER_CODE,l_node_name,P_Planner_Code,PLANNER_CODE)
,DEFAULT_PLANNER_CODE = Decode(DEFAULT_PLANNER_CODE,l_node_name,p_DEFAULT_Planner_Code,DEFAULT_PLANNER_CODE)
,OVERRIDE_PLANNER_CODE = Decode(DEFAULT_PLANNER_CODE,l_node_name,p_OVERRIDE_Planner_Code,OVERRIDE_PLANNER_CODE)
,CATEGORY_SET_ID = Decode(DEFAULT_CATEGORY_SET_ID,l_node_name,P_CATEGORY_SET_ID,CATEGORY_SET_ID)
,DEFAULT_CATEGORY_SET_ID = Decode(DEFAULT_CATEGORY_SET_ID,l_node_name,p_DEFAULT_CATEGORY_SET_ID,DEFAULT_CATEGORY_SET_ID)
,OVERRIDE_CATEGORY_SET_ID = DECODE(DEFAULT_CATEGORY_SET_ID,L_NODE_NAME,P_OVERRIDE_CATEGORY_SET_ID,OVERRIDE_CATEGORY_SET_ID)
,CATEGORY_ID = Decode(DEFAULT_CATEGORY_ID,l_node_name,P_CATEGORY_ID,CATEGORY_ID)
,DEFAULT_CATEGORY_ID = Decode(DEFAULT_CATEGORY_ID,l_node_name,p_DEFAULT_CATEGORY_ID,DEFAULT_CATEGORY_ID)
,OVERRIDE_CATEGORY_ID = DECODE(DEFAULT_CATEGORY_ID,L_NODE_NAME,P_OVERRIDE_CATEGORY_ID,OVERRIDE_CATEGORY_ID)
,RECOMMEND_METHOD = Decode(DEFAULT_RECOMMEND_METHOD,l_node_name,P_RECOMMEND_METHOD,RECOMMEND_METHOD)
,DEFAULT_RECOMMEND_METHOD = Decode(DEFAULT_RECOMMEND_METHOD,l_node_name,p_DEFAULT_RECOMMEND_METHOD,DEFAULT_RECOMMEND_METHOD)
,OVERRIDE_RECOMMEND_METHOD = DECODE(DEFAULT_RECOMMEND_METHOD,L_NODE_NAME,P_OVERRIDE_RECOMMEND_METHOD,OVERRIDE_RECOMMEND_METHOD)
,USAGE_WEIGHT1 = Decode(DEFAULT_USAGE_WEIGHT1,l_node_name,P_USAGE_WEIGHT1,USAGE_WEIGHT1)
,DEFAULT_USAGE_WEIGHT1 = Decode(DEFAULT_USAGE_WEIGHT1,l_node_name,p_DEFAULT_USAGE_WEIGHT1,DEFAULT_USAGE_WEIGHT1)
,OVERRIDE_USAGE_WEIGHT1 = DECODE(DEFAULT_USAGE_WEIGHT1,L_NODE_NAME,P_OVERRIDE_USAGE_WEIGHT1,OVERRIDE_USAGE_WEIGHT1)
,USAGE_WEIGHT2 = Decode(DEFAULT_USAGE_WEIGHT2,l_node_name,P_USAGE_WEIGHT2,USAGE_WEIGHT2)
,DEFAULT_USAGE_WEIGHT2 = Decode(DEFAULT_USAGE_WEIGHT2,l_node_name,p_DEFAULT_USAGE_WEIGHT2,DEFAULT_USAGE_WEIGHT2)
,OVERRIDE_USAGE_WEIGHT2 = DECODE(DEFAULT_USAGE_WEIGHT2,L_NODE_NAME,P_OVERRIDE_USAGE_WEIGHT2,OVERRIDE_USAGE_WEIGHT2)
,USAGE_WEIGHT3 = Decode(DEFAULT_USAGE_WEIGHT3,l_node_name,P_USAGE_WEIGHT3,USAGE_WEIGHT3)
,DEFAULT_USAGE_WEIGHT3 = Decode(DEFAULT_USAGE_WEIGHT3,l_node_name,p_DEFAULT_USAGE_WEIGHT3,DEFAULT_USAGE_WEIGHT3)
,OVERRIDE_USAGE_WEIGHT3 = DECODE(DEFAULT_USAGE_WEIGHT3,L_NODE_NAME,P_OVERRIDE_USAGE_WEIGHT3,OVERRIDE_USAGE_WEIGHT3)
,USAGE_WEIGHT4 = Decode(DEFAULT_USAGE_WEIGHT4,l_node_name,P_USAGE_WEIGHT4,USAGE_WEIGHT4)
,default_usage_weight4 = decode(default_usage_weight4,l_node_name,p_DEFAULT_USAGE_WEIGHT4,default_usage_weight4)
,override_usage_weight4 = decode(default_usage_weight4,l_node_name,p_OVERRIDE_USAGE_WEIGHT4,override_usage_weight4)
where level_id like p_level_id || '.' || '%';
End Update_Planning_Parameters;
Procedure Delete_Planning_Parameters(p_LEVEL_ID VARCHAR2) Is
l_level_id Varchar2(2000);
Csp_Planning_Parameters_Pkg.Delete_Row (p_LEVEL_ID => p_level_id);
DELETE FROM CSP_STOCK_LISTS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND SUBINVENTORY_CODE IS NULL;
UPDATE MTL_SYSTEM_ITEMS
SET INVENTORY_PLANNING_CODE = 6
WHERE ORGANIZATION_ID = p_ORGANIZATION_ID
AND INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID;
DELETE FROM CSP_STOCK_LISTS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND NVL(SUBINVENTORY_CODE,'X') = NVL(P_SUBINVENTORY_CODE,'X');
Update MTL_ITEM_SUB_INVENTORIES
SET INVENTORY_PLANNING_CODE = 6
WHERE INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID AND
ORGANIZATION_ID = p_ORGANIZATION_ID AND
SECONDARY_INVENTORY = p_SECONDARY_INVENTORY;
END DELETE_PLANNING_PARAMETERS;
Select csi.condition_type
From csp_inv_loc_assignments cila,
csp_sec_inventories csi
Where cila.organization_id = p_org_id
And cila.subinventory_code = p_subinv
--And nvl(cila.effective_date_end,sysdate) >= sysdate /* Forward port fix of 115.9 bug 4896449*/
And csi.secondary_inventory_name = cila.subinventory_code
And csi.organization_id = cila.organization_id;
Select csi.condition_type
from csp_sec_inventories csi
Where csi.return_organization_id = p_org_id
And csi.return_subinventory_name = p_subinv
Group by csi.condition_type;