[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 1
From mtl_inv_picking_rules
Where inv_rule_id = p_inv_rule_id;
inv_rule_gen_pvt.Restrictions_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.consistency_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.sorting_criteria_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.restrictions_update
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.consistency_update
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.sorting_criteria_update
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
select mtl_inv_picking_rules_s.nextval into l_inv_Rule_id from dual;
debug('insert rule: rule_id '||l_inv_rule_id);
debug('insert rule: fnd_global.user_id '||fnd_global.user_id);
Insert into mtl_inv_picking_rules
(
INV_RULE_ID
, SHELF_DAYS
, SINGLE_LOT
, PARTIAL_ALLOWED_FLAG
, CUST_SPEC_MATCH_FLAG
, LOT_SORT
, LOT_SORT_RANK
, REVISION_SORT
, REVISION_SORT_RANK
, SUBINVENTORY_SORT
, SUBINVENTORY_SORT_RANK
, LOCATOR_SORT
, LOCATOR_SORT_RANK
, WMS_RULE_ID
, WMS_STRATEGY_ID
, APPLY_TO_SOURCE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, REQUEST_ID
)
values
(
l_inv_rule_id
, p_mtl_picking_rule_rec.shelf_days
, p_mtl_picking_rule_rec.single_lot
, p_mtl_picking_rule_rec.partial_allowed_flag
, p_mtl_picking_rule_rec.cust_spec_match_flag
, p_mtl_picking_rule_rec.lot_sort
, p_mtl_picking_rule_rec.lot_sort_rank
, p_mtl_picking_rule_rec.revision_sort
, p_mtl_picking_rule_rec.revision_sort_rank
, p_mtl_picking_rule_rec.subinventory_sort
, p_mtl_picking_rule_rec.subinventory_sort_rank
, p_mtl_picking_rule_rec.locator_sort
, p_mtl_picking_rule_rec.locator_sort_rank
, p_mtl_picking_rule_rec.wms_rule_id
, p_mtl_picking_rule_rec.wms_strategy_id
, p_mtl_picking_rule_rec.apply_to_source
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, null
, null
, null
);
debug('insert, inv_rule_id '||l_inv_rule_id);
else -- update
update mtl_inv_picking_rules
set
SHELF_DAYS = p_mtl_picking_rule_rec.shelf_days
, SINGLE_LOT = p_mtl_picking_rule_rec.single_lot
, PARTIAL_ALLOWED_FLAG = p_mtl_picking_rule_rec.partial_allowed_flag
, CUST_SPEC_MATCH_FLAG = p_mtl_picking_rule_rec.cust_spec_match_flag
, LOT_SORT = p_mtl_picking_rule_rec.lot_sort
, LOT_SORT_RANK = p_mtl_picking_rule_rec.lot_sort_rank
, REVISION_SORT = p_mtl_picking_rule_rec.revision_sort
, REVISION_SORT_RANK = p_mtl_picking_rule_rec.revision_sort_rank
, SUBINVENTORY_SORT = p_mtl_picking_rule_rec.subinventory_sort
, SUBINVENTORY_SORT_RANK = p_mtl_picking_rule_rec.subinventory_sort_rank
, LOCATOR_SORT = p_mtl_picking_rule_rec.locator_sort
, LOCATOR_SORT_RANK = p_mtl_picking_rule_rec.locator_sort_rank
, WMS_RULE_ID = p_mtl_picking_rule_rec.wms_rule_id
, WMS_STRATEGY_ID = NULL /* p_mtl_picking_rule_rec.wms_strategy_id */
, APPLY_TO_SOURCE = p_mtl_picking_rule_rec.apply_to_source
, LAST_UPDATE_DATE = sysdate
, LAST_UPDATED_BY = fnd_global.user_id
, LAST_UPDATE_LOGIN = fnd_global.login_id
where inv_rule_id = l_inv_rule_id;
select wms_rules_s.nextval into l_wms_rule_Id from dual;
debug('insert wms rule ');
wms_RULES_PKG.Insert_Row
(
X_Rowid => l_Row_Id,
X_Rule_Id => l_wms_rule_Id,
X_Organization_Id => l_Organization_Id,
X_Type_Code => 2,
X_Name => l_picking_rule_rec.Name,
X_Description => l_picking_rule_rec.Description,
X_Qty_Function_Parameter_Id => 10009,
X_Enabled_Flag => l_picking_rule_rec.Enabled_Flag,
X_min_pick_tasks_flag => 'N',
X_User_Defined_Flag => 'Y',
X_Creation_Date => sysdate,
X_Created_By => fnd_global.user_id,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => fnd_global.user_id,
X_Last_Update_Login => fnd_global.login_id,
X_Type_header_id => null,
X_Rule_Weight => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category => null,
X_Allocation_mode_id => l_Allocation_mode_id,
X_wms_enabled_flag => 'N'
);
/* update the rec */
l_picking_rule_rec.wms_rule_id := l_wms_rule_id;
debug('insert wms_rule_id '||l_wms_rule_id);
else -- update
debug('update wms rule ');
debug('update wms rule, enabled_flag '||l_picking_rule_rec.enabled_flag);
wms_RULES_PKG.Update_Row
(
X_Rule_Id => l_picking_rule_rec.wms_Rule_Id,
X_Organization_Id => l_Organization_Id,
X_Type_Code => 2,
X_Name => l_picking_rule_rec.Name,
X_Description => l_picking_rule_rec.Description,
X_Qty_Function_Parameter_Id => 10009,
X_Enabled_Flag => l_picking_rule_rec.Enabled_Flag,
X_User_Defined_Flag => 'Y',
X_min_pick_tasks_flag => 'N',
X_Last_Updated_By => fnd_global.user_id,
X_Last_Update_Date => sysdate,
X_Last_Update_Login => fnd_global.user_id,
X_Type_header_id => null,
X_Rule_Weight => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category => null,
X_Allocation_mode_id => l_Allocation_mode_id
);
l_picking_rule_rec.last_updated_by := fnd_global.user_id;
l_picking_rule_rec.last_update_login := fnd_global.login_id;
l_picking_rule_rec.last_update_date := sysdate;
PROCEDURE Restrictions_insert
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_insert';
debug('Procedure restrictions_insert i loop '||i);
l_logical_operator_code := 1; -- 'AND' grade is inserted already
l_logical_operator_code := 2; -- 'AND' grade is inserted already
l_logical_operator_code := 2; -- 'AND' grade is inserted already
debug('call wms restriction insert '||l_go_ahead);
wms_RESTRICTIONS_PKG.Insert_Row(
X_Rowid => l_Row_Id,
X_Rule_Id => l_picking_rule_rec.WMS_Rule_Id,
X_Sequence_Number => l_Sequence_Number,
X_Last_Updated_By => fnd_global.user_id,
X_Last_Update_Date => sysdate,
X_Created_By => fnd_global.user_id,
X_Creation_Date => sysdate,
X_Last_Update_Login => fnd_global.login_id,
X_Parameter_Id => l_Parameter_Id,
X_Operator_Code => l_Operator_Code,
X_Operand_Type_Code => l_Operand_Type_Code,
X_Operand_Constant_Number=> l_Operand_Constant_Number,
X_Operand_Constant_Character=> l_Operand_Constant_Character,
X_Operand_Constant_Date=> l_Operand_Constant_Date,
X_Operand_Parameter_Id => l_Operand_Parameter_Id,
X_Operand_Expression => l_Operand_Expression,
X_Operand_Flex_Value_Set_Id=> l_Operand_Flex_Value_Set_Id,
X_Logical_Operator_Code=> l_Logical_Operator_Code,
X_Bracket_Open => l_Bracket_Open,
X_Bracket_Close => l_Bracket_Close,
X_Attribute_Category => null,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null
);
END restrictions_insert;
/*Restrictions update for the form will consist two parts,
* 1) delete the current rows for the rule_id
* 2) insert the new rows for the current p_mtl_picking_rule_rec
*/
PROCEDURE Restrictions_update
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_Update';
inv_rule_gen_pvt.Restrictions_delete
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.Restrictions_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
END restrictions_update;
PROCEDURE Restrictions_delete
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_Delete';
Delete wms_restrictions where rule_id = p_mtl_picking_rule_rec.wms_rule_id;
END restrictions_delete;
PROCEDURE consistency_insert
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'consistency_insert';
select wms_rule_consistencies_s.nextval into l_consistency_Id from dual;
WMS_RULE_CONSISTENCIES_PKG.INSERT_ROW(
X_ROWID => l_ROW_ID,
X_CONSISTENCY_ID => l_CONSISTENCY_ID,
X_RULE_ID => l_picking_rule_rec.WMS_RULE_ID,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_PARAMETER_ID => l_parameter_id,
X_ATTRIBUTE_CATEGORY => '',
X_ATTRIBUTE1 => '',
X_ATTRIBUTE2 => '',
X_ATTRIBUTE3 => '',
X_ATTRIBUTE4 => '',
X_ATTRIBUTE5 => '',
X_ATTRIBUTE6 => '',
X_ATTRIBUTE7 => '',
X_ATTRIBUTE8 => '',
X_ATTRIBUTE9 => '',
X_ATTRIBUTE10 => '',
X_ATTRIBUTE11 => '',
X_ATTRIBUTE12 => '',
X_ATTRIBUTE13 => '',
X_ATTRIBUTE14 => '',
X_ATTRIBUTE15 => ''
);
END consistency_insert;
PROCEDURE Consistency_update
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Consistency_Update';
inv_rule_gen_pvt.consistency_delete
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.consistency_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
END consistency_update;
PROCEDURE Consistency_delete
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Consistency_Delete';
Delete wms_rule_consistencies where rule_id = p_mtl_picking_Rule_rec.wms_rule_id;
END consistency_delete;
PROCEDURE Sorting_criteria_insert
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_insert';
debug('sort insert, lot_sort_rank '||p_mtl_picking_rule_rec.lot_sort_rank);
debug('sort insert, revision_sort_rank '||p_mtl_picking_rule_rec.revision_sort_rank);
debug('sort insert, sub_sort_rank '||p_mtl_picking_rule_rec.subinventory_sort_rank);
debug('sort insert, locator_sort_rank '||p_mtl_picking_rule_rec.locator_sort_rank);
l_go_ahead := 0; -- NO insert
debug('sort insert, '|| i);
debug('sort insert, sequence '||l_sequence);
debug('sort insert, wms_rule_id '||p_mtl_picking_rule_rec.wms_rule_id);
wms_SORT_CRITERIA_PKG.Insert_Row
(
X_Rowid => l_Row_Id,
X_Rule_Id => p_mtl_picking_rule_rec.WMS_Rule_Id,
X_Sequence_Number => l_Sequence,
X_Parameter_Id => l_Parameter_Id,
X_Order_Code => l_Order_Code,
X_Created_By => fnd_global.user_id,
X_Creation_Date => sysdate,
X_Last_Updated_By => fnd_global.user_id,
X_Last_Update_Date => sysdate,
X_Last_Update_Login => fnd_global.login_id,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category => null
);
END sorting_criteria_insert;
/* Update consist two parts
* 1) Delete the current row for the rule_id
* 2) Insert new rows for the current setup
*/
PROCEDURE sorting_criteria_update
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_Update';
inv_rule_gen_pvt.sorting_criteria_delete
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.sorting_criteria_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
END sorting_criteria_update;
PROCEDURE Sorting_criteria_delete
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_Delete';
debug('delete sorting criteria');
Delete wms_sort_criteria where rule_id = p_mtl_picking_Rule_rec.wms_rule_id;
END sorting_criteria_delete;
PROCEDURE Strategy_insert
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_insert';
debug('Procedure Strategy_insert');
select WMS_strategies_s.nextval into l_strategy_id from sys.dual;
WMS_STRATEGIES_PKG.Insert_Row
(
X_Rowid => l_ROWID
, X_Strategy_Id => l_Strategy_Id
, X_Organization_Id => -1
, X_Type_Code => 2
, X_Name => l_strategy_name
, X_Description => l_strategy_name
, X_Enabled_Flag => 'Y'
, X_User_Defined_Flag => 'Y'
, X_Created_By => fnd_global.user_id
, X_Creation_Date => SYSDATE
, X_Last_Updated_By => fnd_global.user_id
, X_Last_Update_Date => SYSDATE
, X_Last_Update_Login => fnd_global.login_id
, X_Attribute1 => null
, X_Attribute2 => null
, X_Attribute3 => null
, X_Attribute4 => null
, X_Attribute5 => null
, X_Attribute6 => null
, X_Attribute7 => null
, X_Attribute8 => null
, X_Attribute9 => null
, X_Attribute10 => null
, X_Attribute11 => null
, X_Attribute12 => null
, X_Attribute13 => null
, X_Attribute14 => null
, X_Attribute15 => null
, X_Attribute_Category => null
);
/* insert strategy_members */
debug('calling insert strategy members ');
WMS_STRATEGY_MEMBERS_PKG.Insert_Row(
X_Rowid => l_RowId
, X_Strategy_Id => l_Strategy_Id
, X_Sequence_Number => 10
, X_Rule_Id => p_mtl_picking_rule_rec.wms_Rule_Id
, X_Partial_Success_Allowed_Flag=> p_mtl_picking_rule_rec.Partial_Allowed_Flag
, X_Effective_From => null
, X_Effective_To => null
, X_Created_By => fnd_global.user_id
, X_Creation_Date => sysdate
, X_Last_Updated_By => fnd_global.user_id
, X_Last_Update_Date => sysdate
, X_Last_Update_Login => fnd_global.login_id
, X_Attribute1 => null
, X_Attribute2 => null
, X_Attribute3 => null
, X_Attribute4 => null
, X_Attribute5 => null
, X_Attribute6 => null
, X_Attribute7 => null
, X_Attribute8 => null
, X_Attribute9 => null
, X_Attribute10 => null
, X_Attribute11 => null
, X_Attribute12 => null
, X_Attribute13 => null
, X_Attribute14 => null
, X_Attribute15 => null
, X_Attribute_Category => null
, X_Date_Type_Code => 11 -- always
, X_Date_Type_Lookup_Type => null
, X_Date_Type_From => null
, X_Date_Type_To => null
);
End strategy_insert;
/* Only enabled flag can be updated. */
/* disable the strategy when rule is disabled */
PROCEDURE Strategy_update
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Update';
WMS_STRATEGIES_PKG.Update_Row(
X_Strategy_Id => p_mtl_picking_rule_rec.wms_Strategy_Id,
X_Organization_Id => -1,
X_Type_Code => 2,
X_Name => p_mtl_picking_rule_rec.name,
X_Description => p_mtl_picking_rule_rec.name,
X_Enabled_Flag => p_mtl_picking_rule_rec.enabled_flag,
X_User_Defined_Flag => 'Y',
X_Last_Updated_By => fnd_global.user_id,
X_Last_Update_Date => sysdate,
X_Last_Update_Login => fnd_global.user_id,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category => null
);
/* when rule is deleted, strategy is also deleted */
PROCEDURE Strategy_delete
(p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Delete';
l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Delete';
inv_rule_gen_pvt.Strategy_insert
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
inv_rule_gen_pvt.Strategy_Update
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
debug('after strategy_insert '||p_mtl_picking_rule_rec.wms_strategy_id);
update wms_rules_b
set enabled_flag = 'Y'
where rule_id=p_mtl_picking_rule_rec.wms_rule_id;
inv_rule_gen_pvt.Strategy_Update
(p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
);
debug('after strategy_update '||p_mtl_picking_rule_rec.wms_strategy_id);
update wms_rules_b
set enabled_flag = 'N'
where rule_id=p_mtl_picking_rule_rec.wms_rule_id;
SELECT 'X'
FROM wms_strategy_members MPSM, wms_strategies_b S
WHERE MPSM.rule_id = p_rule_id
AND S.Strategy_Id = MPSM.Strategy_Id
AND S.Enabled_Flag = 'Y'
AND NVL(MPSM.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
AND NVL(MPSM.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
AND rownum < 2;
SELECT 'X'
FROM wms_selection_criteria_txn WSCT
WHERE WSCT.return_type_code = 'S'
AND WSCT.return_type_id = p_strategy_id
AND WSCT.enabled_flag = 1
AND NVL(WSCT.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
AND NVL(WSCT.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
AND rownum < 2;
SELECT 'X'
FROM wms_selection_criteria_txn WSCT
WHERE WSCT.return_type_code = 'R'
AND WSCT.return_type_id = p_rule_id
AND WSCT.enabled_flag = 1
AND NVL(WSCT.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
AND NVL(WSCT.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
AND rownum < 2;