[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Batch_Id IN OUT NOCOPY NUMBER,
P_Creation_Date DATE,
P_Created_By NUMBER,
P_Last_Update_Date DATE,
P_Last_Updated_By NUMBER,
P_Last_Update_Login NUMBER,
-- Bug 3266659 : Batch name prefix
p_batch_name_prefix VARCHAR2 DEFAULT NULL,
X_Name IN OUT NOCOPY VARCHAR2,
P_Backorders_Only_Flag VARCHAR2,
P_Document_Set_Id NUMBER,
P_Existing_Rsvs_Only_Flag VARCHAR2,
P_Shipment_Priority_Code VARCHAR2,
P_Ship_Method_Code VARCHAR2,
P_Customer_Id NUMBER,
P_Order_Header_Id NUMBER,
P_Ship_Set_Number NUMBER,
P_Inventory_Item_Id NUMBER,
P_Order_Type_Id NUMBER,
P_From_Requested_Date DATE,
P_To_Requested_Date DATE,
P_From_Scheduled_Ship_Date DATE,
P_To_Scheduled_Ship_Date DATE,
P_Ship_To_Location_Id NUMBER,
P_Ship_From_Location_Id NUMBER,
P_Trip_Id NUMBER,
P_Delivery_Id NUMBER,
P_Include_Planned_Lines VARCHAR2,
P_Pick_Grouping_Rule_Id NUMBER,
P_Pick_Sequence_Rule_Id NUMBER,
P_Autocreate_Delivery_Flag VARCHAR2,
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_Autodetail_Pr_Flag VARCHAR2,
P_Carrier_Id NUMBER,
P_Trip_Stop_Id NUMBER,
P_Default_Stage_Subinventory VARCHAR2,
P_Default_Stage_Locator_Id NUMBER,
P_Pick_From_Subinventory VARCHAR2,
P_Pick_From_locator_Id NUMBER,
P_Auto_Pick_Confirm_Flag VARCHAR2,
P_Delivery_Detail_Id NUMBER,
P_Project_Id NUMBER,
P_Task_Id NUMBER,
P_Organization_Id NUMBER,
P_Ship_Confirm_Rule_Id NUMBER,
P_Autopack_Flag VARCHAR2,
P_Autopack_Level NUMBER,
P_Task_Planning_Flag VARCHAR2,
P_Dynamic_replenishment_Flag VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
P_Non_Picking_Flag VARCHAR2 DEFAULT NULL,
p_regionID NUMBER,
p_zoneId NUMBER,
p_categoryID NUMBER,
p_categorySetID NUMBER,
p_acDelivCriteria VARCHAR2,
p_RelSubinventory VARCHAR2,
p_append_flag VARCHAR2,
p_task_priority NUMBER,
P_Ship_Set_Smc_Flag VARCHAR2 DEFAULT NULL, --- Added for pick release Public API
p_actual_departure_date DATE,
p_allocation_method VARCHAR2 , -- X-dock
p_crossdock_criteria_id NUMBER, -- X-dock
-- but 5117876, following 14 attributes are added
p_Delivery_Name_Lo VARCHAR2 DEFAULT NULL,
p_Delivery_Name_Hi VARCHAR2 DEFAULT NULL,
p_Bol_Number_Lo VARCHAR2 DEFAULT NULL,
p_Bol_Number_Hi VARCHAR2 DEFAULT NULL,
p_Intmed_Ship_To_Loc_Id NUMBER DEFAULT NULL,
p_Pooled_Ship_To_Loc_Id NUMBER DEFAULT NULL,
p_Fob_Code VARCHAR2 DEFAULT NULL,
p_Freight_Terms_Code VARCHAR2 DEFAULT NULL,
p_Pickup_Date_Lo DATE DEFAULT NULL,
p_Pickup_Date_Hi DATE DEFAULT NULL,
p_Dropoff_Date_Lo DATE DEFAULT NULL,
p_Dropoff_Date_Hi DATE DEFAULT NULL,
p_Planned_Flag VARCHAR2 DEFAULT NULL,
p_Selected_Batch_Id NUMBER DEFAULT NULL
) IS
--
CURSOR C IS SELECT rowid FROM WSH_PICKING_BATCHES
WHERE batch_id = X_Batch_Id;
CURSOR NEXTID IS SELECT mtl_txn_request_headers_s.nextval FROM sys.dual;
Select batch_id From WSH_PICKING_BATCHES
Where NAME = batch_name;
Select batch_id From WSH_PICKING_BATCHES
Where NAME = batch_name OR batch_id = batchid;
SELECT header_id FROM MTL_TXN_REQUEST_HEADERS
WHERE request_number = batch_name;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'p_Selected_Batch_Id', p_Selected_Batch_Id);
SELECT trip_id
INTO P_Trip_For_Stop_id
FROM wsh_trip_stops
WHERE stop_id = p_trip_stop_id;
WSH_DEBUG_SV.log(l_module_name,'Inserting into WSH_PICKING_BATCHES');
INSERT INTO WSH_PICKING_BATCHES(
batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
name,
backorders_only_flag,
document_set_id,
existing_rsvs_only_flag,
shipment_priority_code,
ship_method_code,
customer_id,
Order_Header_Id,
ship_set_number,
inventory_item_id,
order_type_id,
from_requested_date,
to_requested_date,
from_scheduled_ship_date,
to_scheduled_ship_date,
ship_to_location_id,
ship_from_location_id,
trip_id,
delivery_id,
include_planned_lines,
pick_grouping_rule_id,
pick_sequence_rule_id,
autocreate_delivery_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
autodetail_pr_flag,
Carrier_Id,
Trip_Stop_Id,
Default_Stage_Subinventory,
Default_Stage_Locator_Id,
Pick_From_Subinventory,
Pick_From_locator_Id,
Auto_Pick_Confirm_Flag,
Delivery_Detail_Id,
Project_Id,
Task_Id,
organization_id,
Ship_Confirm_Rule_Id,
Autopack_Flag,
Autopack_Level,
Task_Planning_Flag,
Dynamic_Replenishment_flag, --bug# 6689448 (replenishment project)
non_picking_flag,
/* rlanka : Pack J Enhancement */
region_ID,
zone_ID,
category_ID,
category_Set_ID,
ac_Delivery_Criteria,
rel_subinventory,
append_flag,
task_priority,
ship_set_smc_flag, -- Bug#: 3266659
actual_departure_date,
allocation_method, -- X-dock
crossdock_criteria_id, -- X-dock
Delivery_Name_Lo, -- bug 5117876, 14 attriubtes are added
Delivery_Name_Hi,
Bol_Number_Lo,
Bol_Number_Hi,
Intmed_Ship_To_Loc_Id,
Pooled_Ship_To_Loc_Id,
Fob_Code,
Freight_Terms_Code,
Pickup_Date_Lo,
Pickup_Date_Hi,
Dropoff_Date_Lo,
Dropoff_Date_Hi,
Planned_Flag,
Selected_Batch_Id
) VALUES (
X_Batch_Id,
SYSDATE,
userid,
SYSDATE,
userid,
loginid,
X_Name,
P_Backorders_Only_Flag,
P_Document_Set_Id,
P_Existing_Rsvs_Only_Flag,
P_Shipment_Priority_Code,
P_Ship_Method_Code,
P_Customer_Id,
P_Order_Header_Id,
P_Ship_Set_Number,
P_Inventory_Item_Id,
P_Order_Type_Id,
P_From_Requested_Date,
P_To_Requested_Date,
P_From_Scheduled_Ship_Date,
P_To_Scheduled_Ship_Date,
P_Ship_To_Location_Id,
P_Ship_From_Location_Id,
--Introduced NVL for Bugfix#1724744.
NVL(P_Trip_Id,P_Trip_For_Stop_Id),
P_Delivery_Id,
P_Include_Planned_Lines,
P_Pick_Grouping_Rule_Id,
P_Pick_Sequence_Rule_Id,
P_Autocreate_Delivery_Flag,
P_Attribute_Category,
P_Attribute1,
P_Attribute2,
P_Attribute3,
P_Attribute4,
P_Attribute5,
P_Attribute6,
P_Attribute7,
P_Attribute8,
P_Attribute9,
P_Attribute10,
P_Attribute11,
P_Attribute12,
P_Attribute13,
P_Attribute14,
P_Attribute15,
P_Autodetail_Pr_Flag,
P_Carrier_Id,
P_Trip_Stop_Id,
P_Default_Stage_Subinventory,
P_Default_Stage_Locator_Id,
P_Pick_From_Subinventory,
P_Pick_From_locator_Id,
P_Auto_Pick_Confirm_Flag,
P_Delivery_Detail_Id,
P_Project_id,
P_Task_Id,
P_Organization_Id,
P_Ship_Confirm_Rule_Id,
P_Autopack_Flag,
P_Autopack_Level,
P_Task_Planning_Flag,
P_Dynamic_Replenishment_flag, --bug# 6689448 (replenishment project)
P_Non_Picking_Flag,
p_regionID,
p_zoneId,
p_categoryID,
p_categorySetID,
p_acDelivCriteria,
p_RelSubinventory,
p_append_flag,
p_task_priority,
p_Ship_Set_Smc_Flag, -- Bug#: 3266659
p_actual_departure_date,
nvl(p_allocation_method,'I'), -- X-dock
p_crossdock_criteria_id, -- X-dock
p_Delivery_Name_Lo, -- bug 5117876, 14 attributes are added
p_Delivery_Name_Hi,
p_Bol_Number_Lo,
p_Bol_Number_Hi,
p_Intmed_Ship_To_Loc_Id,
p_Pooled_Ship_To_Loc_Id,
p_Fob_Code,
p_Freight_Terms_Code,
p_Pickup_Date_Lo,
p_Pickup_Date_Hi,
p_Dropoff_Date_Lo,
p_Dropoff_Date_Hi,
p_Planned_Flag,
p_Selected_Batch_Id
);
END Insert_Row;
SELECT *
FROM WSH_PICKING_BATCHES
WHERE rowid = X_Rowid
FOR UPDATE of Batch_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
P_Batch_Id NUMBER,
P_Last_Update_Date DATE,
P_Last_Updated_By NUMBER,
P_Last_Update_Login NUMBER,
P_Name VARCHAR2,
P_Backorders_Only_Flag VARCHAR2,
P_Document_Set_Id NUMBER,
P_Existing_Rsvs_Only_Flag VARCHAR2,
P_Shipment_Priority_Code VARCHAR2,
P_Ship_Method_Code VARCHAR2,
P_Customer_Id NUMBER,
P_Order_Header_Id NUMBER,
P_Ship_Set_Number NUMBER,
P_Inventory_Item_Id NUMBER,
P_Order_Type_Id NUMBER,
P_From_Requested_Date DATE,
P_To_Requested_Date DATE,
P_From_Scheduled_Ship_Date DATE,
P_To_Scheduled_Ship_Date DATE,
P_Ship_To_Location_Id NUMBER,
P_Ship_From_Location_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_Autodetail_Pr_Flag VARCHAR2,
P_Carrier_Id NUMBER,
P_Trip_Stop_Id NUMBER,
P_Default_Stage_Subinventory VARCHAR2,
P_Default_Stage_Locator_Id NUMBER,
P_Pick_From_Subinventory VARCHAR2,
P_Pick_From_locator_Id NUMBER,
P_Auto_Pick_Confirm_Flag VARCHAR2,
P_Delivery_Detail_Id NUMBER,
P_Project_Id NUMBER,
P_Task_Id NUMBER,
P_Organization_Id NUMBER,
P_Ship_Confirm_Rule_Id NUMBER,
P_Autopack_Flag VARCHAR2,
P_Autopack_Level NUMBER,
P_Task_Planning_Flag VARCHAR2,
P_Dynamic_replenishment_Flag VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
P_non_picking_flag VARCHAR2,
p_regionID NUMBER,
p_zoneId NUMBER,
p_categoryID NUMBER,
p_categorySetID NUMBER,
p_acDelivCriteria VARCHAR2,
p_RelSubinventory VARCHAR2,
p_append_flag VARCHAR2,
p_task_priority NUMBER,
p_actual_departure_date DATE,
p_allocation_method VARCHAR2 , -- X-dock
p_crossdock_criteria_id NUMBER -- X-dock
) IS
--
userid NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
UPDATE WSH_PICKING_BATCHES
SET
batch_id = P_Batch_Id,
last_update_date = SYSDATE,
last_updated_by = userid,
last_update_login = loginid,
name = P_Name,
backorders_only_flag = P_Backorders_Only_Flag,
Document_Set_Id = P_Document_Set_Id,
existing_rsvs_only_flag = P_Existing_Rsvs_Only_Flag,
shipment_priority_code = P_Shipment_Priority_Code,
ship_method_code = P_Ship_Method_Code,
customer_id = P_Customer_Id,
order_Header_Id = P_Order_Header_Id,
ship_set_number = P_Ship_Set_Number,
inventory_item_id = P_Inventory_Item_Id,
order_type_id = P_Order_Type_Id,
from_requested_date = P_From_Requested_Date,
to_requested_date = P_To_Requested_Date,
from_scheduled_ship_date = P_From_Scheduled_Ship_Date,
to_scheduled_ship_date = P_To_Scheduled_Ship_Date,
ship_to_location_id = P_Ship_To_Location_Id,
ship_from_location_id = P_Ship_From_Location_Id,
attribute_category = P_Attribute_Category,
attribute1 = P_Attribute1,
attribute2 = P_Attribute2,
attribute3 = P_Attribute3,
attribute4 = P_Attribute4,
attribute5 = P_Attribute5,
attribute6 = P_Attribute6,
attribute7 = P_Attribute7,
attribute8 = P_Attribute8,
attribute9 = P_Attribute9,
attribute10 = P_Attribute10,
attribute11 = P_Attribute11,
attribute12 = P_Attribute12,
attribute13 = P_Attribute13,
attribute14 = P_Attribute14,
attribute15 = P_Attribute15,
autodetail_pr_flag = P_Autodetail_Pr_Flag,
carrier_id = P_Carrier_Id,
trip_stop_id = P_Trip_Stop_Id,
default_stage_subinventory = P_Default_Stage_Subinventory,
default_stage_locator_id = P_Default_Stage_Locator_Id,
pick_from_subinventory = P_Pick_From_Subinventory,
pick_from_locator_id = P_Pick_From_locator_Id,
auto_pick_confirm_flag = P_Auto_Pick_Confirm_Flag,
project_id = P_Project_Id,
task_id = P_Task_Id,
delivery_detail_id = P_Delivery_Detail_Id,
ship_confirm_rule_id = P_Ship_Confirm_Rule_Id,
Autopack_Flag = P_Autopack_Flag,
autopack_level = P_Autopack_Level,
task_planning_flag = P_Task_Planning_Flag,
dynamic_replenishment_flag = P_dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
non_picking_flag = P_non_picking_flag,
region_id = p_regionID,
zone_id = p_zoneID,
category_id = p_categoryID,
category_set_id = p_categorySetID,
ac_Delivery_criteria = p_acDelivcriteria,
rel_subinventory = p_relsubinventory,
append_flag = p_append_flag,
task_priority = p_task_priority,
actual_departure_date = p_actual_departure_date,
allocation_method = nvl(p_allocation_method,'I'), -- X-dock
crossdock_criteria_id = p_crossdock_criteria_id -- X-dock
WHERE rowid = X_Rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid IN OUT NOCOPY VARCHAR2) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
DELETE FROM WSH_PICKING_BATCHES
WHERE rowid = X_Rowid;
END Delete_Row;
PROCEDURE Delete_And_Commit(X_Rowid IN OUT NOCOPY VARCHAR2) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_AND_COMMIT';
Delete_Row(X_Rowid);
END Delete_And_Commit;
/* SELECT MAX(P.LEVEL_TYPE_ID)
INTO level_type_id
FROM WSH_REPORT_PRINTERS P,
SO_REPORTS R
WHERE P.REPORT_SET_ID = R.REPORT_ID
AND R.NAME =
NVL(report, default_report)
AND P.LEVEL_VALUE_ID = DECODE(P.LEVEL_TYPE_ID,
10001,0,
10002,app_id,
10003,respid,
10004,userid)
AND ENABLE_FLAG = 'Y';
SELECT P.PRINTER_NAME
INTO printer
FROM WSH_REPORT_PRINTERS P,
SO_REPORTS R
WHERE P.REPORT_ID = R.REPORT_ID
AND R.NAME =
NVL(report, default_report)
AND P.LEVEL_TYPE_ID = level_type_id
AND P.LEVEL_VALUE_ID = DECODE(level_type_id,
10001,0,
10002,app_id,
10003,respid,
10004,userid);