[Home] [Help]
PACKAGE BODY: APPS.WSH_PICKING_BATCHES_PKG
Source
1 PACKAGE BODY WSH_PICKING_BATCHES_PKG as
2 /* $Header: WSHPRBTB.pls 120.3.12010000.2 2009/12/03 13:36:16 anvarshn ship $ */
3
4
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PICKING_BATCHES_PKG';
7 --
8 -- Bug 3266659: Added P_Ship_Set_Smc_Flag for the pick release public API
9
10 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
11 X_Batch_Id IN OUT NOCOPY NUMBER,
12 P_Creation_Date DATE,
13 P_Created_By NUMBER,
14 P_Last_Update_Date DATE,
15 P_Last_Updated_By NUMBER,
16 P_Last_Update_Login NUMBER,
17 -- Bug 3266659 : Batch name prefix
18 p_batch_name_prefix VARCHAR2 DEFAULT NULL,
19 X_Name IN OUT NOCOPY VARCHAR2,
20 P_Backorders_Only_Flag VARCHAR2,
21 P_Document_Set_Id NUMBER,
22 P_Existing_Rsvs_Only_Flag VARCHAR2,
23 P_Shipment_Priority_Code VARCHAR2,
24 P_Ship_Method_Code VARCHAR2,
25 P_Customer_Id NUMBER,
26 P_Order_Header_Id NUMBER,
27 P_Ship_Set_Number NUMBER,
28 P_Inventory_Item_Id NUMBER,
29 P_Order_Type_Id NUMBER,
30 P_From_Requested_Date DATE,
31 P_To_Requested_Date DATE,
32 P_From_Scheduled_Ship_Date DATE,
33 P_To_Scheduled_Ship_Date DATE,
34 P_Ship_To_Location_Id NUMBER,
35 P_Ship_From_Location_Id NUMBER,
36 P_Trip_Id NUMBER,
37 P_Delivery_Id NUMBER,
38 P_Include_Planned_Lines VARCHAR2,
39 P_Pick_Grouping_Rule_Id NUMBER,
40 P_Pick_Sequence_Rule_Id NUMBER,
41 P_Autocreate_Delivery_Flag VARCHAR2,
42 P_Attribute_Category VARCHAR2,
43 P_Attribute1 VARCHAR2,
44 P_Attribute2 VARCHAR2,
45 P_Attribute3 VARCHAR2,
46 P_Attribute4 VARCHAR2,
47 P_Attribute5 VARCHAR2,
48 P_Attribute6 VARCHAR2,
49 P_Attribute7 VARCHAR2,
50 P_Attribute8 VARCHAR2,
51 P_Attribute9 VARCHAR2,
52 P_Attribute10 VARCHAR2,
53 P_Attribute11 VARCHAR2,
54 P_Attribute12 VARCHAR2,
55 P_Attribute13 VARCHAR2,
56 P_Attribute14 VARCHAR2,
57 P_Attribute15 VARCHAR2,
58 P_Autodetail_Pr_Flag VARCHAR2,
59 P_Carrier_Id NUMBER,
60 P_Trip_Stop_Id NUMBER,
61 P_Default_Stage_Subinventory VARCHAR2,
62 P_Default_Stage_Locator_Id NUMBER,
63 P_Pick_From_Subinventory VARCHAR2,
64 P_Pick_From_locator_Id NUMBER,
65 P_Auto_Pick_Confirm_Flag VARCHAR2,
66 P_Delivery_Detail_Id NUMBER,
67 P_Project_Id NUMBER,
68 P_Task_Id NUMBER,
69 P_Organization_Id NUMBER,
70 P_Ship_Confirm_Rule_Id NUMBER,
71 P_Autopack_Flag VARCHAR2,
72 P_Autopack_Level NUMBER,
73 P_Task_Planning_Flag VARCHAR2,
74 P_Dynamic_replenishment_Flag VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
75 P_Non_Picking_Flag VARCHAR2 DEFAULT NULL,
76 p_regionID NUMBER,
77 p_zoneId NUMBER,
78 p_categoryID NUMBER,
79 p_categorySetID NUMBER,
80 p_acDelivCriteria VARCHAR2,
81 p_RelSubinventory VARCHAR2,
82 p_append_flag VARCHAR2,
83 p_task_priority NUMBER,
84 P_Ship_Set_Smc_Flag VARCHAR2 DEFAULT NULL, --- Added for pick release Public API
85 p_actual_departure_date DATE,
86 p_allocation_method VARCHAR2 , -- X-dock
87 p_crossdock_criteria_id NUMBER, -- X-dock
88 -- but 5117876, following 14 attributes are added
89 p_Delivery_Name_Lo VARCHAR2 DEFAULT NULL,
90 p_Delivery_Name_Hi VARCHAR2 DEFAULT NULL,
91 p_Bol_Number_Lo VARCHAR2 DEFAULT NULL,
92 p_Bol_Number_Hi VARCHAR2 DEFAULT NULL,
93 p_Intmed_Ship_To_Loc_Id NUMBER DEFAULT NULL,
94 p_Pooled_Ship_To_Loc_Id NUMBER DEFAULT NULL,
95 p_Fob_Code VARCHAR2 DEFAULT NULL,
96 p_Freight_Terms_Code VARCHAR2 DEFAULT NULL,
97 p_Pickup_Date_Lo DATE DEFAULT NULL,
98 p_Pickup_Date_Hi DATE DEFAULT NULL,
99 p_Dropoff_Date_Lo DATE DEFAULT NULL,
100 p_Dropoff_Date_Hi DATE DEFAULT NULL,
101 p_Planned_Flag VARCHAR2 DEFAULT NULL,
102 p_Selected_Batch_Id NUMBER DEFAULT NULL,
103 p_client_Id NUMBER DEFAULT NULL --Modified R12.1.1 LSP PROJECT
104 ) IS
105 --
106 CURSOR C IS SELECT rowid FROM WSH_PICKING_BATCHES
107 WHERE batch_id = X_Batch_Id;
108
109 -- bug 5117876, use sequence mtl_txn_request_headers_s
110 -- CURSOR NEXTID IS SELECT wsh_picking_batches_s.nextval FROM sys.dual;
111 CURSOR NEXTID IS SELECT mtl_txn_request_headers_s.nextval FROM sys.dual;
112
113 -- bug 5117876, add one more parameter batchid
114 /*
115 CURSOR Batch (batch_name VARCHAR2) IS
116 Select batch_id From WSH_PICKING_BATCHES
117 Where NAME = batch_name;
118 */
119 CURSOR Batch (batch_name VARCHAR2, batchid NUMBER) IS
120 Select batch_id From WSH_PICKING_BATCHES
121 Where NAME = batch_name OR batch_id = batchid;
122
123 --
124 CURSOR Move_Order (batch_name VARCHAR2) IS
125 SELECT header_id FROM MTL_TXN_REQUEST_HEADERS
126 WHERE request_number = batch_name;
127 --
128 userid NUMBER;
129 loginid NUMBER;
130 temp NUMBER;
131 temp2 NUMBER;
132 --Added for Bugfix#1724744.
133 P_Trip_For_Stop_Id NUMBER;
134 --
135 l_debug_on BOOLEAN;
136 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
137 --
138 -- Added for Bug#: 3266659
139 l_batch_name_prefix VARCHAR2(30);
140
141 BEGIN
142 --
143 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
144 --
145 IF l_debug_on IS NULL
146 THEN
147 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
148 END IF;
149 --
150 IF l_debug_on THEN
151 --
152 WSH_DEBUG_SV.push(l_module_name);
153 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
154 WSH_DEBUG_SV.log(l_module_name,'X_BATCH_ID',X_BATCH_ID);
155 WSH_DEBUG_SV.log(l_module_name,'P_CREATION_DATE',P_CREATION_DATE);
156 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_BY',P_CREATED_BY);
157 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
158 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
159 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
160 WSH_DEBUG_SV.log(l_module_name,'X_NAME',X_NAME);
161 WSH_DEBUG_SV.log(l_module_name,'P_BACKORDERS_ONLY_FLAG',P_BACKORDERS_ONLY_FLAG);
162 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_SET_ID',P_DOCUMENT_SET_ID);
163 WSH_DEBUG_SV.log(l_module_name,'P_EXISTING_RSVS_ONLY_FLAG',P_EXISTING_RSVS_ONLY_FLAG);
164 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_PRIORITY_CODE',P_SHIPMENT_PRIORITY_CODE);
165 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
166 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
167 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
168 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_NUMBER',P_SHIP_SET_NUMBER);
169 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
170 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE_ID',P_ORDER_TYPE_ID);
171 WSH_DEBUG_SV.log(l_module_name,'P_FROM_REQUESTED_DATE',P_FROM_REQUESTED_DATE);
172 WSH_DEBUG_SV.log(l_module_name,'P_TO_REQUESTED_DATE',P_TO_REQUESTED_DATE);
173 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SCHEDULED_SHIP_DATE',P_FROM_SCHEDULED_SHIP_DATE);
174 WSH_DEBUG_SV.log(l_module_name,'P_TO_SCHEDULED_SHIP_DATE',P_TO_SCHEDULED_SHIP_DATE);
175 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_LOCATION_ID',P_SHIP_TO_LOCATION_ID);
176 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOCATION_ID',P_SHIP_FROM_LOCATION_ID);
177 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
178 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
179 WSH_DEBUG_SV.log(l_module_name,'P_INCLUDE_PLANNED_LINES',P_INCLUDE_PLANNED_LINES);
180 WSH_DEBUG_SV.log(l_module_name,'P_PICK_GROUPING_RULE_ID',P_PICK_GROUPING_RULE_ID);
181 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SEQUENCE_RULE_ID',P_PICK_SEQUENCE_RULE_ID);
182 WSH_DEBUG_SV.log(l_module_name,'P_AUTOCREATE_DELIVERY_FLAG',P_AUTOCREATE_DELIVERY_FLAG);
183 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
184 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
185 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
186 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
187 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
188 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
189 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
190 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
191 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
192 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
193 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
194 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
195 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
196 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
197 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
198 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
199 WSH_DEBUG_SV.log(l_module_name,'P_AUTODETAIL_PR_FLAG',P_AUTODETAIL_PR_FLAG);
200 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
201 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
202 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_STAGE_SUBINVENTORY',P_DEFAULT_STAGE_SUBINVENTORY);
203 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_STAGE_LOCATOR_ID',P_DEFAULT_STAGE_LOCATOR_ID);
204 WSH_DEBUG_SV.log(l_module_name,'P_PICK_FROM_SUBINVENTORY',P_PICK_FROM_SUBINVENTORY);
205 WSH_DEBUG_SV.log(l_module_name,'P_PICK_FROM_LOCATOR_ID',P_PICK_FROM_LOCATOR_ID);
206 WSH_DEBUG_SV.log(l_module_name,'P_AUTO_PICK_CONFIRM_FLAG',P_AUTO_PICK_CONFIRM_FLAG);
207 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
208 WSH_DEBUG_SV.log(l_module_name,'P_PROJECT_ID',P_PROJECT_ID);
209 WSH_DEBUG_SV.log(l_module_name,'P_TASK_ID',P_TASK_ID);
210 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
211 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_CONFIRM_RULE_ID',P_SHIP_CONFIRM_RULE_ID);
212 WSH_DEBUG_SV.log(l_module_name,'P_AUTOPACK_FLAG',P_AUTOPACK_FLAG);
213 WSH_DEBUG_SV.log(l_module_name,'P_AUTOPACK_LEVEL',P_AUTOPACK_LEVEL);
214 WSH_DEBUG_SV.log(l_module_name,'P_TASK_PLANNING_FLAG',P_TASK_PLANNING_FLAG);
215 WSH_DEBUG_SV.log(l_module_name,'P_dynamic_replenishment_flag',P_dynamic_replenishment_flag); --bug# 6689448 (replenishment project)
216 WSH_DEBUG_SV.log(l_module_name,'P_Non_Picking_Flag',P_Non_Picking_Flag);
217 -- rlanka : Pack J Enhancement
218 wsh_debug_Sv.log(l_module_name,'p_RegionID', p_regionID);
219 wsh_debug_sv.log(l_module_name,'p_zoneID',p_zoneID);
220 wsh_debug_sv.log(l_module_name,'p_categoryID',p_categoryID);
221 wsh_debug_sv.log(l_module_name,'p_categorySetID',p_categorySetID);
222 wsh_debug_sv.log(l_module_name,'p_acDelivCriteria',p_acDelivCriteria);
223 wsh_debug_sv.log(l_module_name,'p_RelSubinventory', p_RelSubinventory);
224 wsh_debug_sv.log(l_module_name,'p_append_flag', p_append_flag);
225 -- Bug#: 3266659 : Pick Release API
226 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_SMC_FLAG',P_SHIP_SET_SMC_FLAG);
227 WSH_DEBUG_SV.log(l_module_name,'p_batch_name_prefix',p_batch_name_prefix);
228 WSH_DEBUG_SV.log(l_module_name,'p_task_priority',p_task_priority);
229 WSH_DEBUG_SV.log(l_module_name,'p_actual_departure_date',p_actual_departure_date);
230 -- X-dock
231 WSH_DEBUG_SV.log(l_module_name,'p_allocation_method',p_allocation_method);
232 WSH_DEBUG_SV.log(l_module_name,'p_crossdock_criteria_id',p_crossdock_criteria_id);
233 -- bug 5117876, 14 more attributes
234 WSH_DEBUG_SV.log(l_module_name,'p_Delivery_Name_Lo', p_Delivery_Name_Lo);
235 WSH_DEBUG_SV.log(l_module_name,'p_Delivery_Name_Hi', p_Delivery_Name_Hi);
236 WSH_DEBUG_SV.log(l_module_name,'p_Bol_Number_Lo',p_Bol_Number_Lo);
237 WSH_DEBUG_SV.log(l_module_name,'p_Bol_Number_Hi',p_Bol_Number_Hi);
238 WSH_DEBUG_SV.log(l_module_name,'p_Intmed_Ship_To_Loc_Id', p_Intmed_Ship_To_Loc_Id);
239 WSH_DEBUG_SV.log(l_module_name,'p_Pooled_Ship_To_Loc_Id', p_Pooled_Ship_To_Loc_Id);
240 WSH_DEBUG_SV.log(l_module_name,'p_Fob_Code', p_Fob_Code);
241 WSH_DEBUG_SV.log(l_module_name,'p_Freight_Terms_Code', p_Freight_Terms_Code);
242 WSH_DEBUG_SV.log(l_module_name,'p_Pickup_Date_Lo', p_Pickup_Date_Lo);
243 WSH_DEBUG_SV.log(l_module_name,'p_Pickup_Date_Hi', p_Pickup_Date_Hi);
244 WSH_DEBUG_SV.log(l_module_name,'p_Dropoff_Date_Lo', p_Dropoff_Date_Lo);
245 WSH_DEBUG_SV.log(l_module_name,'p_Dropoff_Date_Hi', p_Dropoff_Date_Hi);
246 WSH_DEBUG_SV.log(l_module_name,'p_Planned_Flag', p_Planned_Flag);
247 WSH_DEBUG_SV.log(l_module_name,'p_Selected_Batch_Id', p_Selected_Batch_Id);
248 WSH_DEBUG_SV.log(l_module_name,'p_Client_Id', p_client_Id); --Modified R12.1.1 LSP PROJECT
249
250 --
251 END IF;
252 --
253 userid := FND_GLOBAL.USER_ID;
254 loginid := FND_GLOBAL.LOGIN_ID;
255
256 -- Added for Bug#: 3266659
257 l_batch_name_prefix := p_batch_name_prefix;
258
259
260 IF (X_Batch_Id is NULL) THEN
261 OPEN NEXTID;
262 FETCH NEXTID INTO X_Batch_Id;
263 CLOSE NEXTID;
264 END IF;
265
266 --Bug#: 3266659: Added code to look for
267 -- Default Batch Name
268 IF ( X_Name is NULL) Then
269 IF ( l_batch_name_prefix is NOT NULL ) THEN
270 X_Name := l_batch_name_prefix ||'-'|| TO_CHAR(X_Batch_Id);
271 ELSE
272 X_Name := TO_CHAR(X_Batch_Id);
273 END IF;
274 ELSE
275 l_batch_name_prefix := NULL; ---later we will check this value
276 END IF;
277
278
279 Loop
280 OPEN Move_Order( X_Name);
281 FETCH Move_Order INTO temp;
282 IF Move_Order%NOTFOUND THEN
283 CLOSE Move_Order;
284 -- bug 5117876, X_Batch_Id is added
285 OPEN Batch( X_Name, X_Batch_Id);
286 FETCH Batch INTO temp;
287 IF Batch%NOTFOUND THEN
288 CLOSE Batch;
289 EXIT;
290 END IF;
291 END IF;
292
293 OPEN NEXTID;
294 FETCH NEXTID INTO X_Batch_Id;
295 CLOSE NEXTID;
296
297 -- Added for Bug#: 3266659
298 IF ( l_batch_name_prefix is NOT NULL ) THEN
299 X_Name := l_batch_name_prefix ||'-'|| TO_CHAR(X_Batch_Id);
300 ELSE
301 X_Name := TO_CHAR(X_Batch_Id);
302 END IF;
303
304 IF Move_Order%ISOPEN THEN
305 CLOSE Move_Order;
306 END IF;
307 IF Batch%ISOPEN THEN
308 CLOSE Batch;
309 END IF;
310 End Loop;
311
312 --Start Bugfix#1724744.
313 IF ( p_trip_stop_id IS NOT NULL ) then
314 IF ( p_trip_id IS NULL) then
315 SELECT trip_id
316 INTO P_Trip_For_Stop_id
317 FROM wsh_trip_stops
318 WHERE stop_id = p_trip_stop_id;
319 END IF;
320 END IF;
321 --End Bugfix#1724744.
322
323 IF l_debug_on THEN
324 WSH_DEBUG_SV.log(l_module_name,'Inserting into WSH_PICKING_BATCHES');
325 END IF;
326
327 INSERT INTO WSH_PICKING_BATCHES(
328 batch_id,
329 creation_date,
330 created_by,
331 last_update_date,
332 last_updated_by,
333 last_update_login,
334 name,
335 backorders_only_flag,
336 document_set_id,
337 existing_rsvs_only_flag,
338 shipment_priority_code,
339 ship_method_code,
340 customer_id,
341 Order_Header_Id,
342 ship_set_number,
343 inventory_item_id,
344 order_type_id,
345 from_requested_date,
346 to_requested_date,
347 from_scheduled_ship_date,
348 to_scheduled_ship_date,
349 ship_to_location_id,
350 ship_from_location_id,
351 trip_id,
352 delivery_id,
353 include_planned_lines,
354 pick_grouping_rule_id,
355 pick_sequence_rule_id,
356 autocreate_delivery_flag,
357 attribute_category,
358 attribute1,
359 attribute2,
360 attribute3,
361 attribute4,
362 attribute5,
363 attribute6,
364 attribute7,
365 attribute8,
366 attribute9,
367 attribute10,
368 attribute11,
369 attribute12,
370 attribute13,
371 attribute14,
372 attribute15,
373 autodetail_pr_flag,
374 Carrier_Id,
375 Trip_Stop_Id,
376 Default_Stage_Subinventory,
377 Default_Stage_Locator_Id,
378 Pick_From_Subinventory,
379 Pick_From_locator_Id,
380 Auto_Pick_Confirm_Flag,
381 Delivery_Detail_Id,
382 Project_Id,
383 Task_Id,
384 organization_id,
385 Ship_Confirm_Rule_Id,
386 Autopack_Flag,
387 Autopack_Level,
388 Task_Planning_Flag,
389 Dynamic_Replenishment_flag, --bug# 6689448 (replenishment project)
390 non_picking_flag,
391 /* rlanka : Pack J Enhancement */
392 region_ID,
393 zone_ID,
394 category_ID,
395 category_Set_ID,
396 ac_Delivery_Criteria,
397 rel_subinventory,
398 append_flag,
399 task_priority,
400 ship_set_smc_flag, -- Bug#: 3266659
401 actual_departure_date,
402 allocation_method, -- X-dock
403 crossdock_criteria_id, -- X-dock
404 Delivery_Name_Lo, -- bug 5117876, 14 attriubtes are added
405 Delivery_Name_Hi,
406 Bol_Number_Lo,
407 Bol_Number_Hi,
408 Intmed_Ship_To_Loc_Id,
409 Pooled_Ship_To_Loc_Id,
410 Fob_Code,
411 Freight_Terms_Code,
412 Pickup_Date_Lo,
413 Pickup_Date_Hi,
414 Dropoff_Date_Lo,
415 Dropoff_Date_Hi,
416 Planned_Flag,
417 Selected_Batch_Id,
418 Client_ID --Modified R12.1.1 LSP PROJECT
419 ) VALUES (
420 X_Batch_Id,
421 SYSDATE,
422 userid,
423 SYSDATE,
424 userid,
425 loginid,
426 X_Name,
427 P_Backorders_Only_Flag,
428 P_Document_Set_Id,
429 P_Existing_Rsvs_Only_Flag,
430 P_Shipment_Priority_Code,
431 P_Ship_Method_Code,
432 P_Customer_Id,
433 P_Order_Header_Id,
434 P_Ship_Set_Number,
435 P_Inventory_Item_Id,
436 P_Order_Type_Id,
437 P_From_Requested_Date,
438 P_To_Requested_Date,
439 P_From_Scheduled_Ship_Date,
440 P_To_Scheduled_Ship_Date,
441 P_Ship_To_Location_Id,
442 P_Ship_From_Location_Id,
443 --Introduced NVL for Bugfix#1724744.
444 NVL(P_Trip_Id,P_Trip_For_Stop_Id),
445 P_Delivery_Id,
446 P_Include_Planned_Lines,
447 P_Pick_Grouping_Rule_Id,
448 P_Pick_Sequence_Rule_Id,
449 P_Autocreate_Delivery_Flag,
450 P_Attribute_Category,
451 P_Attribute1,
452 P_Attribute2,
453 P_Attribute3,
454 P_Attribute4,
455 P_Attribute5,
456 P_Attribute6,
457 P_Attribute7,
458 P_Attribute8,
459 P_Attribute9,
460 P_Attribute10,
461 P_Attribute11,
462 P_Attribute12,
463 P_Attribute13,
464 P_Attribute14,
465 P_Attribute15,
466 P_Autodetail_Pr_Flag,
467 P_Carrier_Id,
468 P_Trip_Stop_Id,
469 P_Default_Stage_Subinventory,
470 P_Default_Stage_Locator_Id,
471 P_Pick_From_Subinventory,
472 P_Pick_From_locator_Id,
473 P_Auto_Pick_Confirm_Flag,
474 P_Delivery_Detail_Id,
475 P_Project_id,
476 P_Task_Id,
477 P_Organization_Id,
478 P_Ship_Confirm_Rule_Id,
479 P_Autopack_Flag,
480 P_Autopack_Level,
481 P_Task_Planning_Flag,
482 P_Dynamic_Replenishment_flag, --bug# 6689448 (replenishment project)
483 P_Non_Picking_Flag,
484 p_regionID,
485 p_zoneId,
486 p_categoryID,
487 p_categorySetID,
488 p_acDelivCriteria,
489 p_RelSubinventory,
490 p_append_flag,
491 p_task_priority,
492 p_Ship_Set_Smc_Flag, -- Bug#: 3266659
493 p_actual_departure_date,
494 nvl(p_allocation_method,'I'), -- X-dock
495 p_crossdock_criteria_id, -- X-dock
496 p_Delivery_Name_Lo, -- bug 5117876, 14 attributes are added
497 p_Delivery_Name_Hi,
498 p_Bol_Number_Lo,
499 p_Bol_Number_Hi,
500 p_Intmed_Ship_To_Loc_Id,
501 p_Pooled_Ship_To_Loc_Id,
502 p_Fob_Code,
503 p_Freight_Terms_Code,
504 p_Pickup_Date_Lo,
505 p_Pickup_Date_Hi,
506 p_Dropoff_Date_Lo,
507 p_Dropoff_Date_Hi,
508 p_Planned_Flag,
509 p_Selected_Batch_Id,
510 p_client_Id --Modified R12.1.1 LSP PROJECT
511 );
512
513 OPEN C;
514 FETCH C INTO X_Rowid;
515 if (C%NOTFOUND) then
516 CLOSE C;
517 Raise NO_DATA_FOUND;
518 end if;
519 IF C%ISOPEN THEN
520 CLOSE C;
521 END IF;
522 --
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.pop(l_module_name);
525 END IF;
526 --
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 IF C%ISOPEN THEN
531 CLOSE C;
532 END IF;
533 IF NEXTID%ISOPEN THEN
534 CLOSE NEXTID;
535 END IF;
536 IF Batch%ISOPEN THEN
537 CLOSE Batch;
538 END IF;
539 IF Move_Order%ISOPEN THEN
540 CLOSE Move_Order;
541 END IF;
542 --
543 IF l_debug_on THEN
544 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
545 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
546 END IF;
547 --
548
549 END Insert_Row;
550
551
552 PROCEDURE Lock_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
553 P_Batch_Id NUMBER,
554 P_Name VARCHAR2,
555 P_Backorders_Only_Flag VARCHAR2,
556 P_Document_Set_Id NUMBER,
557 P_Existing_Rsvs_Only_Flag VARCHAR2,
558 P_Shipment_Priority_Code VARCHAR2,
559 P_Ship_Method_Code VARCHAR2,
560 P_Customer_Id NUMBER,
561 P_Order_Header_Id NUMBER,
562 P_Ship_Set_Number NUMBER,
563 P_Inventory_Item_Id NUMBER,
564 P_Order_Type_Id NUMBER,
565 P_From_Requested_Date DATE,
566 P_To_Requested_Date DATE,
567 P_From_Scheduled_Ship_Date DATE,
568 P_To_Scheduled_Ship_Date DATE,
569 P_Ship_To_Location_Id NUMBER,
570 P_Ship_From_Location_Id NUMBER,
571 P_Trip_Id NUMBER,
572 P_Delivery_Id NUMBER,
573 P_Include_Planned_Lines VARCHAR2,
574 P_Pick_Grouping_Rule_Id NUMBER,
575 P_Pick_Sequence_Rule_Id NUMBER,
576 P_Autocreate_Delivery_Flag VARCHAR2,
577 P_Attribute_Category VARCHAR2,
578 P_Attribute1 VARCHAR2,
579 P_Attribute2 VARCHAR2,
580 P_Attribute3 VARCHAR2,
581 P_Attribute4 VARCHAR2,
582 P_Attribute5 VARCHAR2,
583 P_Attribute6 VARCHAR2,
584 P_Attribute7 VARCHAR2,
585 P_Attribute8 VARCHAR2,
586 P_Attribute9 VARCHAR2,
587 P_Attribute10 VARCHAR2,
588 P_Attribute11 VARCHAR2,
589 P_Attribute12 VARCHAR2,
590 P_Attribute13 VARCHAR2,
591 P_Attribute14 VARCHAR2,
592 P_Attribute15 VARCHAR2,
593 P_Autodetail_Pr_Flag VARCHAR2,
594 P_Carrier_Id NUMBER,
595 P_Trip_Stop_Id NUMBER,
596 P_Default_Stage_Subinventory VARCHAR2,
597 P_Default_Stage_Locator_Id NUMBER,
598 P_Pick_From_Subinventory VARCHAR2,
599 P_Pick_From_locator_Id NUMBER,
600 P_Auto_Pick_Confirm_Flag VARCHAR2,
601 P_Delivery_Detail_Id NUMBER,
602 P_Project_Id NUMBER,
603 P_Task_Id NUMBER,
604 P_Organization_Id NUMBER,
605 P_Ship_Confirm_Rule_Id NUMBER,
606 P_Autopack_Flag VARCHAR2,
607 P_Autopack_Level NUMBER,
608 P_Task_Planning_Flag VARCHAR2,
609 P_Dynamic_replenishment_Flag VARCHAR2, --bug# 6689448 (replenishment project)
610 P_Non_Picking_Flag VARCHAR2,
611 p_regionID NUMBER,
612 p_zoneId NUMBER,
613 p_categoryID NUMBER,
614 p_categorySetID NUMBER,
615 p_acDelivCriteria VARCHAR2,
616 p_RelSubinventory VARCHAR2,
617 p_append_flag VARCHAR2,
618 p_task_priority NUMBER,
619 p_actual_departure_date DATE,
620 p_allocation_method VARCHAR2 , -- X-dock
621 p_crossdock_criteria_id NUMBER, -- X-dock
622 p_client_Id NUMBER --Modified R12.1.1 LSP PROJECT
623 ) IS
624 --
625 CURSOR C IS
626 SELECT *
627 FROM WSH_PICKING_BATCHES
628 WHERE rowid = X_Rowid
629 FOR UPDATE of Batch_Id NOWAIT;
630 --
631 Recinfo C%ROWTYPE;
632 --
633 l_debug_on BOOLEAN;
634 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
635 --
636 BEGIN
637 --
638 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
639 --
640 IF l_debug_on IS NULL
641 THEN
642 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
643 END IF;
644 --
645 IF l_debug_on THEN
646 --
647 WSH_DEBUG_SV.push(l_module_name);
648 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
649 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
650 WSH_DEBUG_SV.log(l_module_name,'P_NAME',P_NAME);
651 WSH_DEBUG_SV.log(l_module_name,'P_BACKORDERS_ONLY_FLAG',P_BACKORDERS_ONLY_FLAG);
652 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_SET_ID',P_DOCUMENT_SET_ID);
653 WSH_DEBUG_SV.log(l_module_name,'P_EXISTING_RSVS_ONLY_FLAG',P_EXISTING_RSVS_ONLY_FLAG);
654 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_PRIORITY_CODE',P_SHIPMENT_PRIORITY_CODE);
655 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
656 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
657 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
658 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_NUMBER',P_SHIP_SET_NUMBER);
659 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
660 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE_ID',P_ORDER_TYPE_ID);
661 WSH_DEBUG_SV.log(l_module_name,'P_FROM_REQUESTED_DATE',P_FROM_REQUESTED_DATE);
662 WSH_DEBUG_SV.log(l_module_name,'P_TO_REQUESTED_DATE',P_TO_REQUESTED_DATE);
663 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SCHEDULED_SHIP_DATE',P_FROM_SCHEDULED_SHIP_DATE);
664 WSH_DEBUG_SV.log(l_module_name,'P_TO_SCHEDULED_SHIP_DATE',P_TO_SCHEDULED_SHIP_DATE);
665 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_LOCATION_ID',P_SHIP_TO_LOCATION_ID);
666 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOCATION_ID',P_SHIP_FROM_LOCATION_ID);
667 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
668 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
669 WSH_DEBUG_SV.log(l_module_name,'P_INCLUDE_PLANNED_LINES',P_INCLUDE_PLANNED_LINES);
670 WSH_DEBUG_SV.log(l_module_name,'P_PICK_GROUPING_RULE_ID',P_PICK_GROUPING_RULE_ID);
671 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SEQUENCE_RULE_ID',P_PICK_SEQUENCE_RULE_ID);
672 WSH_DEBUG_SV.log(l_module_name,'P_AUTOCREATE_DELIVERY_FLAG',P_AUTOCREATE_DELIVERY_FLAG);
673 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
674 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
675 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
676 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
677 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
678 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
679 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
680 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
681 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
682 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
683 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
684 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
685 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
686 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
687 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
688 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
689 WSH_DEBUG_SV.log(l_module_name,'P_AUTODETAIL_PR_FLAG',P_AUTODETAIL_PR_FLAG);
690 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
691 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
692 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_STAGE_SUBINVENTORY',P_DEFAULT_STAGE_SUBINVENTORY);
693 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_STAGE_LOCATOR_ID',P_DEFAULT_STAGE_LOCATOR_ID);
694 WSH_DEBUG_SV.log(l_module_name,'P_PICK_FROM_SUBINVENTORY',P_PICK_FROM_SUBINVENTORY);
695 WSH_DEBUG_SV.log(l_module_name,'P_PICK_FROM_LOCATOR_ID',P_PICK_FROM_LOCATOR_ID);
696 WSH_DEBUG_SV.log(l_module_name,'P_AUTO_PICK_CONFIRM_FLAG',P_AUTO_PICK_CONFIRM_FLAG);
697 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
698 WSH_DEBUG_SV.log(l_module_name,'P_PROJECT_ID',P_PROJECT_ID);
699 WSH_DEBUG_SV.log(l_module_name,'P_TASK_ID',P_TASK_ID);
700 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
701 WSH_DEBUG_SV.log(l_module_name,'P_TASK_PLANNING_FLAG',P_TASK_PLANNING_FLAG);
702 WSH_DEBUG_SV.log(l_module_name,'P_dynamic_replenishment_flag',P_dynamic_replenishment_flag); --bug# 6689448 (replenishment project)
703 WSH_DEBUG_SV.log(l_module_name,'P_Non_Picking_FLAG',P_Non_Picking_FLAG);
704 --
705 -- rlanka : Pack J Enhancement
706 --
707 wsh_debug_Sv.log(l_module_name,'p_RegionID', p_regionID);
708 wsh_debug_sv.log(l_module_name,'p_zoneID',p_zoneID);
709 wsh_debug_sv.log(l_module_name,'p_categoryID',p_categoryID);
710 wsh_debug_sv.log(l_module_name,'p_categorySetID',p_categorySetID);
711 wsh_debug_sv.log(l_module_name,'p_acDelivCriteria',p_acDelivCriteria);
712 wsh_debug_sv.log(l_module_name,'p_RelSubinventory', p_RelSubinventory);
713 wsh_debug_sv.log(l_module_name,'p_append_flag', p_append_flag);
714 wsh_debug_sv.log(l_module_name,'p_task_priority', p_task_priority);
715 wsh_debug_sv.log(l_module_name,'p_actual_departure_date',
716 p_actual_departure_date);
717 -- X-dock
718 WSH_DEBUG_SV.log(l_module_name,'p_allocation_method',p_allocation_method);
719 WSH_DEBUG_SV.log(l_module_name,'p_crossdock_criteria_id',p_crossdock_criteria_id);
720 WSH_DEBUG_SV.log(l_module_name,'p_client_Id',p_client_Id); --Modified R12.1.1 LSP PROJECT
721 END IF;
722 --
723 OPEN C;
724 FETCH C INTO Recinfo;
725 --
726 if (C%NOTFOUND) then
727 --
728 CLOSE C;
729 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
730 APP_EXCEPTION.Raise_Exception;
731 --
732 end if;
733 --
734 IF C%ISOPEN THEN
735 CLOSE C;
736 END IF;
737 --
738 if (
739
740 (Recinfo.batch_id = P_Batch_Id)
741 AND (Recinfo.name = P_Name)
742 AND (Recinfo.backorders_only_flag = P_Backorders_Only_Flag)
743 AND ( (Recinfo.Document_Set_Id = P_Document_Set_Id)
744 OR ( (Recinfo.Document_Set_Id IS NULL)
745 AND (P_Document_Set_Id IS NULL)))
746 AND ( (Recinfo.existing_rsvs_only_flag = P_Existing_Rsvs_Only_Flag)
747 OR ( (Recinfo.existing_rsvs_only_flag IS NULL)
748 AND (P_Existing_Rsvs_Only_Flag IS NULL)))
749 AND ( (Recinfo.shipment_priority_code = P_Shipment_Priority_Code)
750 OR ( (Recinfo.shipment_priority_code IS NULL)
751 AND (P_Shipment_Priority_Code IS NULL)))
752 AND ( (Recinfo.ship_method_code = P_Ship_Method_Code)
753 OR ( (Recinfo.ship_method_code IS NULL)
754 AND (P_Ship_Method_Code IS NULL)))
755 AND ( (Recinfo.customer_id = P_Customer_Id)
756 OR ( (Recinfo.customer_id IS NULL)
757 AND (P_Customer_Id IS NULL)))
758 AND ( (Recinfo.client_id = P_Client_Id) --Modified R12.1.1 LSP PROJECT
759 OR ( (Recinfo.client_id IS NULL)
760 AND (P_client_id IS NULL))) --Modified R12.1.1 LSP PROJECT
761 AND ( (Recinfo.Order_Header_Id = P_Order_Header_Id)
762 OR ( (Recinfo.Order_Header_Id IS NULL)
763 AND (P_Order_Header_Id IS NULL)))
764 AND ( (Recinfo.ship_set_number = P_Ship_Set_Number)
765 OR ( (Recinfo.ship_set_number IS NULL)
766 AND (P_Ship_Set_Number IS NULL)))
767 AND ( (Recinfo.inventory_item_id = P_Inventory_Item_Id)
768 OR ( (Recinfo.inventory_item_id IS NULL)
769 AND (P_Inventory_Item_Id IS NULL)))
770 AND ( (Recinfo.order_type_id = P_Order_Type_Id)
771 OR ( (Recinfo.order_type_id IS NULL)
772 AND (P_Order_Type_Id IS NULL)))
773 /* rlanka : Pack J Enhancement */
774 AND ((to_date(Recinfo.from_requested_date, 'DD/MM/YYYY HH24:MI:SS') = to_date(P_From_Requested_Date, 'DD/MM/YYYY HH24:MI:SS'))
775 OR (Recinfo.from_requested_date IS NULL AND P_From_Requested_Date IS NULL))
776 AND ((to_date(Recinfo.to_requested_date, 'DD/MM/YYYY HH24:MI:SS') = to_date(P_To_Requested_Date, 'DD/MM/YYYY HH24:MI:SS'))
777 OR (Recinfo.to_requested_date IS NULL AND P_To_Requested_Date IS NULL))
778 AND ((to_date(Recinfo.from_scheduled_ship_date, 'DD/MM/YYYY HH24:MI:SS') = to_date(P_From_Scheduled_Ship_Date, 'DD/MM/YYYY HH24:MI:SS'))
779 OR (Recinfo.from_scheduled_ship_date IS NULL AND P_From_Scheduled_Ship_Date IS NULL))
780 AND ((to_date(Recinfo.to_scheduled_ship_date, 'DD/MM/YYYY HH24:MI:SS') = to_date(P_To_Scheduled_Ship_Date, 'DD/MM/YYYY HH24:MI:SS'))
781 OR (Recinfo.to_scheduled_ship_date IS NULL AND P_To_Scheduled_Ship_Date IS NULL))
782 AND ( (Recinfo.ship_to_location_id = P_Ship_To_Location_Id)
783 OR ( (Recinfo.ship_to_location_id IS NULL)
784 AND (P_Ship_To_Location_Id IS NULL)))
785 AND ( (Recinfo.ship_from_location_id = P_Ship_From_Location_Id)
786 OR ( (Recinfo.ship_from_location_id IS NULL)
787 AND (P_Ship_From_Location_Id IS NULL)))
788 AND ( (Recinfo.trip_id = P_Trip_Id)
789 OR ( (Recinfo.trip_id IS NULL)
790 AND (P_Trip_Id IS NULL)))
791 AND ( (Recinfo.delivery_id = P_Delivery_Id)
792 OR ( (Recinfo.delivery_id IS NULL)
793 AND (P_Delivery_Id IS NULL)))
794 AND ( (Recinfo.include_planned_lines = P_Include_Planned_Lines)
795 OR ( (Recinfo.include_planned_lines IS NULL)
796 AND (P_Include_Planned_Lines IS NULL)))
797 AND ( (Recinfo.pick_grouping_rule_id = P_Pick_Grouping_Rule_Id)
798 OR ( (Recinfo.pick_grouping_rule_id IS NULL)
799 AND (P_Pick_Grouping_Rule_Id IS NULL)))
800 AND ( (Recinfo.pick_sequence_rule_id = P_Pick_Sequence_Rule_Id)
801 OR ( (Recinfo.pick_sequence_rule_id IS NULL)
802 AND (P_Pick_Sequence_Rule_Id IS NULL)))
803 /* rlanka : incorrect comparison was here */
804 AND ( (Recinfo.autocreate_delivery_flag = P_Autocreate_Delivery_Flag)
805 OR (Recinfo.autocreate_delivery_flag IS NULL AND P_Autocreate_Delivery_Flag IS NULL))
806 AND ( (Recinfo.attribute_category = P_Attribute_Category)
807 OR ( (Recinfo.attribute_category IS NULL)
808 AND (P_Attribute_Category IS NULL)))
809 AND ( (Recinfo.attribute1 = P_Attribute1)
810 OR ( (Recinfo.attribute1 IS NULL)
811 AND (P_Attribute1 IS NULL)))
812 AND ( (Recinfo.attribute2 = P_Attribute2)
813 OR ( (Recinfo.attribute2 IS NULL)
814 AND (P_Attribute2 IS NULL)))
815 AND ( (Recinfo.attribute3 = P_Attribute3)
816 OR ( (Recinfo.attribute3 IS NULL)
817 AND (P_Attribute3 IS NULL)))
818 AND ( (Recinfo.attribute4 = P_Attribute4)
819 OR ( (Recinfo.attribute4 IS NULL)
820 AND (P_Attribute4 IS NULL)))
821 AND ( (Recinfo.attribute5 = P_Attribute5)
822 OR ( (Recinfo.attribute5 IS NULL)
823 AND (P_Attribute5 IS NULL)))
824 AND ( (Recinfo.attribute6 = P_Attribute6)
825 OR ( (Recinfo.attribute6 IS NULL)
826 AND (P_Attribute6 IS NULL)))
827 AND ( (Recinfo.attribute7 = P_Attribute7)
828 OR ( (Recinfo.attribute7 IS NULL)
829 AND (P_Attribute7 IS NULL)))
830 AND ( (Recinfo.attribute8 = P_Attribute8)
831 OR ( (Recinfo.attribute8 IS NULL)
832 AND (P_Attribute8 IS NULL)))
833 AND ( (Recinfo.attribute9 = P_Attribute9)
834 OR ( (Recinfo.attribute9 IS NULL)
835 AND (P_Attribute9 IS NULL)))
836 AND ( (Recinfo.attribute10 = P_Attribute10)
837 OR ( (Recinfo.attribute10 IS NULL)
838 AND (P_Attribute10 IS NULL)))
839 AND ( (Recinfo.attribute11 = P_Attribute11)
840 OR ( (Recinfo.attribute11 IS NULL)
841 AND (P_Attribute11 IS NULL)))
842 AND ( (Recinfo.attribute12 = P_Attribute12)
843 OR ( (Recinfo.attribute12 IS NULL)
844 AND (P_Attribute12 IS NULL)))
845 AND ( (Recinfo.attribute13 = P_Attribute13)
846 OR ( (Recinfo.attribute13 IS NULL)
847 AND (P_Attribute13 IS NULL)))
848 AND ( (Recinfo.attribute14 = P_Attribute14)
849 OR ( (Recinfo.attribute14 IS NULL)
850 AND (P_Attribute14 IS NULL)))
851 AND ( (Recinfo.attribute15 = P_Attribute15)
852 OR ( (Recinfo.attribute15 IS NULL)
853 AND (P_Attribute15 IS NULL)))
854 AND ( (Recinfo.autodetail_pr_flag = P_Autodetail_Pr_Flag)
855 OR ( (Recinfo.autodetail_pr_flag IS NULL)
856 AND (P_Autodetail_Pr_Flag IS NULL)))
857 AND ( (Recinfo.carrier_id = P_Carrier_Id)
858 OR ( (Recinfo.carrier_id IS NULL)
859 AND (P_Carrier_Id IS NULL)))
860 AND ( (Recinfo.trip_stop_id = P_Trip_Stop_Id)
861 OR ( (Recinfo.trip_stop_id IS NULL)
862 AND (P_Trip_Stop_Id IS NULL)))
863 AND ( (Recinfo.default_stage_subinventory = P_Default_Stage_Subinventory)
864 OR ( (Recinfo.default_stage_subinventory IS NULL)
865 AND (P_Default_Stage_Subinventory IS NULL)))
866 AND ( (Recinfo.default_stage_locator_id = P_Default_Stage_Locator_Id)
867 OR ( (Recinfo.default_stage_locator_id IS NULL)
868 AND (P_Default_Stage_Locator_Id IS NULL)))
869 AND ( (Recinfo.pick_from_subinventory = P_Pick_From_Subinventory)
870 OR ( (Recinfo.pick_from_subinventory IS NULL)
871 AND (P_Pick_From_Subinventory IS NULL)))
872 AND ( (Recinfo.pick_from_locator_id = P_Pick_From_Locator_Id)
873 OR ( (Recinfo.pick_from_locator_id IS NULL)
874 AND (P_Pick_From_Locator_Id IS NULL)))
875 AND ( (Recinfo.auto_pick_confirm_flag = P_Auto_Pick_Confirm_Flag)
876 OR ( (Recinfo.auto_pick_confirm_flag IS NULL)
877 AND (P_Auto_Pick_Confirm_Flag IS NULL)))
878 AND ( (Recinfo.delivery_detail_id = P_delivery_detail_id)
879 OR ( (Recinfo.delivery_detail_id IS NULL)
880 AND (P_delivery_detail_id IS NULL)))
881 AND ( (Recinfo.project_id = P_project_id)
882 OR ( (Recinfo.project_id IS NULL)
883 AND (P_project_id IS NULL)))
884 AND ( (Recinfo.task_id = P_task_id)
885 OR ( (Recinfo.task_id IS NULL)
886 AND (P_task_id IS NULL)))
887 AND ( (Recinfo.ship_confirm_rule_id = P_ship_confirm_rule_id)
888 OR ( (Recinfo.ship_confirm_rule_id IS NULL)
889 AND (P_ship_confirm_rule_id IS NULL)))
890 AND ( (Recinfo.autopack_flag = P_autopack_flag)
891 OR ( (Recinfo.autopack_flag IS NULL)
892 AND (P_autopack_flag IS NULL)))
893 AND ( (Recinfo.autopack_level = P_autopack_level)
894 OR ( (Recinfo.autopack_level IS NULL)
895 AND (P_autopack_level IS NULL)))
896 AND ( (Recinfo.task_planning_flag = P_task_planning_flag)
897 OR ( (Recinfo.task_planning_flag IS NULL)
898 AND (P_task_planning_flag IS NULL)))
899 --bug# 6689448 (replenishment project)
900 AND ( (Recinfo.dynamic_replenishment_flag = P_dynamic_replenishment_flag)
901 OR ( (Recinfo.dynamic_replenishment_flag IS NULL)
902 AND (P_dynamic_replenishment_flag IS NULL)))
903 /* rlanka : Pack J Enhancement */
904 AND ((Recinfo.zone_id = p_zoneID)
905 OR (Recinfo.zone_id IS NULL AND p_ZoneID is NULL))
906 AND ((Recinfo.region_id = p_regionID)
907 OR (Recinfo.region_id IS NULL AND p_regionID is NULL))
908 AND ((Recinfo.category_id = p_categoryID)
909 OR (Recinfo.category_id IS NULL AND p_categoryID is NULL))
910 AND ((Recinfo.category_set_id = p_categorySetID)
911 OR (Recinfo.category_set_id IS NULL AND p_categorySetID is NULL))
912 AND ((Recinfo.rel_subinventory = p_relsubinventory)
913 OR (Recinfo.rel_subinventory IS NULL AND p_relsubinventory is NULL))
914 AND ((Recinfo.ac_delivery_criteria = p_acDelivcriteria)
915 OR (Recinfo.ac_delivery_criteria IS NULL AND p_acDelivcriteria is NULL))
916 AND ((Recinfo.append_flag = p_append_flag)
917 OR (Recinfo.append_flag IS NULL AND p_append_flag is NULL))
918 -- X-dock - allocation_method and crossdock_criteria_id
919 AND ((Recinfo.allocation_method = p_allocation_method)
920 OR (Recinfo.allocation_method IS NULL AND p_allocation_method is NULL))
921 AND ((Recinfo.crossdock_criteria_id = p_crossdock_criteria_id)
922 OR (Recinfo.crossdock_criteria_id IS NULL AND p_crossdock_criteria_id is NULL))
923 -- end of X-dock change
924 AND ((Recinfo.task_priority = p_task_priority)
925 OR (Recinfo.task_priority IS NULL AND p_task_priority is NULL)) AND ((to_date(Recinfo.actual_departure_date, 'DD/MM/YYYY HH24:MI:SS') =
926 to_date(p_actual_departure_date, 'DD/MM/YYYY HH24:MI:SS'))
927 OR (Recinfo.actual_departure_date IS NULL AND
928 p_actual_departure_date IS NULL)))
929 then
930 --
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.pop(l_module_name,'RETURN');
933 END IF;
934 --
935 return;
936 else
937 --
938 IF l_debug_on THEN
939 wsh_debug_sv.pop(l_module_name, 'FORM_RECORD_CHANGED');
940 END IF;
941 --
942 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
943 APP_EXCEPTION.Raise_Exception;
944 --
945 end if;
946 --
947 IF l_debug_on THEN
948 WSH_DEBUG_SV.pop(l_module_name);
949 END IF;
950 --
951 END Lock_Row;
952
953
954
955 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
956 P_Batch_Id NUMBER,
957 P_Last_Update_Date DATE,
958 P_Last_Updated_By NUMBER,
959 P_Last_Update_Login NUMBER,
960 P_Name VARCHAR2,
961 P_Backorders_Only_Flag VARCHAR2,
962 P_Document_Set_Id NUMBER,
963 P_Existing_Rsvs_Only_Flag VARCHAR2,
964 P_Shipment_Priority_Code VARCHAR2,
965 P_Ship_Method_Code VARCHAR2,
966 P_Customer_Id NUMBER,
967 P_Order_Header_Id NUMBER,
968 P_Ship_Set_Number NUMBER,
969 P_Inventory_Item_Id NUMBER,
970 P_Order_Type_Id NUMBER,
971 P_From_Requested_Date DATE,
972 P_To_Requested_Date DATE,
973 P_From_Scheduled_Ship_Date DATE,
974 P_To_Scheduled_Ship_Date DATE,
975 P_Ship_To_Location_Id NUMBER,
976 P_Ship_From_Location_Id NUMBER,
977 P_Attribute_Category VARCHAR2,
978 P_Attribute1 VARCHAR2,
979 P_Attribute2 VARCHAR2,
980 P_Attribute3 VARCHAR2,
981 P_Attribute4 VARCHAR2,
982 P_Attribute5 VARCHAR2,
983 P_Attribute6 VARCHAR2,
984 P_Attribute7 VARCHAR2,
985 P_Attribute8 VARCHAR2,
986 P_Attribute9 VARCHAR2,
987 P_Attribute10 VARCHAR2,
988 P_Attribute11 VARCHAR2,
989 P_Attribute12 VARCHAR2,
990 P_Attribute13 VARCHAR2,
991 P_Attribute14 VARCHAR2,
992 P_Attribute15 VARCHAR2,
993 P_Autodetail_Pr_Flag VARCHAR2,
994 P_Carrier_Id NUMBER,
995 P_Trip_Stop_Id NUMBER,
996 P_Default_Stage_Subinventory VARCHAR2,
997 P_Default_Stage_Locator_Id NUMBER,
998 P_Pick_From_Subinventory VARCHAR2,
999 P_Pick_From_locator_Id NUMBER,
1000 P_Auto_Pick_Confirm_Flag VARCHAR2,
1001 P_Delivery_Detail_Id NUMBER,
1002 P_Project_Id NUMBER,
1003 P_Task_Id NUMBER,
1004 P_Organization_Id NUMBER,
1005 P_Ship_Confirm_Rule_Id NUMBER,
1006 P_Autopack_Flag VARCHAR2,
1007 P_Autopack_Level NUMBER,
1008 P_Task_Planning_Flag VARCHAR2,
1009 P_Dynamic_replenishment_Flag VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
1010 P_non_picking_flag VARCHAR2,
1011 p_regionID NUMBER,
1012 p_zoneId NUMBER,
1013 p_categoryID NUMBER,
1014 p_categorySetID NUMBER,
1015 p_acDelivCriteria VARCHAR2,
1016 p_RelSubinventory VARCHAR2,
1017 p_append_flag VARCHAR2,
1018 p_task_priority NUMBER,
1019 p_actual_departure_date DATE,
1020 p_allocation_method VARCHAR2 , -- X-dock
1021 p_crossdock_criteria_id NUMBER, -- X-dock
1022 p_client_Id NUMBER DEFAULT NULL --Modified R12.1.1 LSP PROJECT
1023 ) IS
1024 --
1025 userid NUMBER;
1026 loginid NUMBER;
1027 --
1028 l_debug_on BOOLEAN;
1029 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
1030 --
1031 BEGIN
1032 --
1033 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1034 --
1035 IF l_debug_on IS NULL
1036 THEN
1037 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1038 END IF;
1039 --
1040 IF l_debug_on THEN
1041 --
1042 WSH_DEBUG_SV.push(l_module_name);
1043 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
1044 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
1045 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);
1046 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);
1047 WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);
1048 WSH_DEBUG_SV.log(l_module_name,'P_NAME',P_NAME);
1049 WSH_DEBUG_SV.log(l_module_name,'P_BACKORDERS_ONLY_FLAG',P_BACKORDERS_ONLY_FLAG);
1050 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_SET_ID',P_DOCUMENT_SET_ID);
1051 WSH_DEBUG_SV.log(l_module_name,'P_EXISTING_RSVS_ONLY_FLAG',P_EXISTING_RSVS_ONLY_FLAG);
1052 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_PRIORITY_CODE',P_SHIPMENT_PRIORITY_CODE);
1053 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
1054 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
1055 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
1056 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_SET_NUMBER',P_SHIP_SET_NUMBER);
1057 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
1058 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE_ID',P_ORDER_TYPE_ID);
1059 WSH_DEBUG_SV.log(l_module_name,'P_FROM_REQUESTED_DATE',P_FROM_REQUESTED_DATE);
1060 WSH_DEBUG_SV.log(l_module_name,'P_TO_REQUESTED_DATE',P_TO_REQUESTED_DATE);
1061 WSH_DEBUG_SV.log(l_module_name,'P_FROM_SCHEDULED_SHIP_DATE',P_FROM_SCHEDULED_SHIP_DATE);
1062 WSH_DEBUG_SV.log(l_module_name,'P_TO_SCHEDULED_SHIP_DATE',P_TO_SCHEDULED_SHIP_DATE);
1063 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_LOCATION_ID',P_SHIP_TO_LOCATION_ID);
1064 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOCATION_ID',P_SHIP_FROM_LOCATION_ID);
1065 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE_CATEGORY',P_ATTRIBUTE_CATEGORY);
1066 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE1',P_ATTRIBUTE1);
1067 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE2',P_ATTRIBUTE2);
1068 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE3',P_ATTRIBUTE3);
1069 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE4',P_ATTRIBUTE4);
1070 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE5',P_ATTRIBUTE5);
1071 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE6',P_ATTRIBUTE6);
1072 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE7',P_ATTRIBUTE7);
1073 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE8',P_ATTRIBUTE8);
1074 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE9',P_ATTRIBUTE9);
1075 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE10',P_ATTRIBUTE10);
1076 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE11',P_ATTRIBUTE11);
1077 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE12',P_ATTRIBUTE12);
1078 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE13',P_ATTRIBUTE13);
1079 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE14',P_ATTRIBUTE14);
1080 WSH_DEBUG_SV.log(l_module_name,'P_ATTRIBUTE15',P_ATTRIBUTE15);
1081 WSH_DEBUG_SV.log(l_module_name,'P_AUTODETAIL_PR_FLAG',P_AUTODETAIL_PR_FLAG);
1082 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
1083 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_STOP_ID',P_TRIP_STOP_ID);
1084 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_STAGE_SUBINVENTORY',P_DEFAULT_STAGE_SUBINVENTORY);
1085 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_STAGE_LOCATOR_ID',P_DEFAULT_STAGE_LOCATOR_ID);
1086 WSH_DEBUG_SV.log(l_module_name,'P_PICK_FROM_SUBINVENTORY',P_PICK_FROM_SUBINVENTORY);
1087 WSH_DEBUG_SV.log(l_module_name,'P_PICK_FROM_LOCATOR_ID',P_PICK_FROM_LOCATOR_ID);
1088 WSH_DEBUG_SV.log(l_module_name,'P_AUTO_PICK_CONFIRM_FLAG',P_AUTO_PICK_CONFIRM_FLAG);
1089 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1090 WSH_DEBUG_SV.log(l_module_name,'P_PROJECT_ID',P_PROJECT_ID);
1091 WSH_DEBUG_SV.log(l_module_name,'P_TASK_ID',P_TASK_ID);
1092 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1093 WSH_DEBUG_SV.log(l_module_name,'P_TASK_PLANNING_FLAG',P_TASK_PLANNING_FLAG);
1094 WSH_DEBUG_SV.log(l_module_name,'P_dynamic_replenishment_flag',P_dynamic_replenishment_flag); --bug# 6689448 (replenishment project)
1095 WSH_DEBUG_SV.log(l_module_name,'P_non_picking_flag',P_non_picking_flag);
1096 -- rlanka : Pack J Enhancement
1097 wsh_debug_Sv.log(l_module_name,'p_RegionID', p_regionID);
1098 wsh_debug_sv.log(l_module_name,'p_zoneID',p_zoneID);
1099 wsh_debug_sv.log(l_module_name,'p_categoryID',p_categoryID);
1100 wsh_debug_sv.log(l_module_name,'p_categorySetID',p_categorySetID);
1101 wsh_debug_sv.log(l_module_name,'p_acDelivCriteria',p_acDelivCriteria);
1102 wsh_debug_sv.log(l_module_name,'p_RelSubinventory', p_RelSubinventory);
1103 wsh_debug_sv.log(l_module_name,'p_append_flag', p_append_flag);
1104 wsh_debug_sv.log(l_module_name,'p_task_priority', p_task_priority);
1105 wsh_debug_sv.log(l_module_name,'p_actual_departure_date',
1106 p_actual_departure_date);
1107 -- X-dock
1108 WSH_DEBUG_SV.log(l_module_name,'p_allocation_method',p_allocation_method);
1109 WSH_DEBUG_SV.log(l_module_name,'p_crossdock_criteria_id',p_crossdock_criteria_id);
1110 WSH_DEBUG_SV.log(l_module_name,'p_client_Id',p_client_Id); --Modified R12.1.1 LSP PROJECT
1111 --
1112 END IF;
1113 --
1114 userid := FND_GLOBAL.USER_ID;
1115 loginid := FND_GLOBAL.LOGIN_ID;
1116 --
1117 UPDATE WSH_PICKING_BATCHES
1118 SET
1119 batch_id = P_Batch_Id,
1120 last_update_date = SYSDATE,
1121 last_updated_by = userid,
1122 last_update_login = loginid,
1123 name = P_Name,
1124 backorders_only_flag = P_Backorders_Only_Flag,
1125 Document_Set_Id = P_Document_Set_Id,
1126 existing_rsvs_only_flag = P_Existing_Rsvs_Only_Flag,
1127 shipment_priority_code = P_Shipment_Priority_Code,
1128 ship_method_code = P_Ship_Method_Code,
1129 customer_id = P_Customer_Id,
1130 order_Header_Id = P_Order_Header_Id,
1131 ship_set_number = P_Ship_Set_Number,
1132 inventory_item_id = P_Inventory_Item_Id,
1133 order_type_id = P_Order_Type_Id,
1134 from_requested_date = P_From_Requested_Date,
1135 to_requested_date = P_To_Requested_Date,
1136 from_scheduled_ship_date = P_From_Scheduled_Ship_Date,
1137 to_scheduled_ship_date = P_To_Scheduled_Ship_Date,
1138 ship_to_location_id = P_Ship_To_Location_Id,
1139 ship_from_location_id = P_Ship_From_Location_Id,
1140 attribute_category = P_Attribute_Category,
1141 attribute1 = P_Attribute1,
1142 attribute2 = P_Attribute2,
1143 attribute3 = P_Attribute3,
1144 attribute4 = P_Attribute4,
1145 attribute5 = P_Attribute5,
1146 attribute6 = P_Attribute6,
1147 attribute7 = P_Attribute7,
1148 attribute8 = P_Attribute8,
1149 attribute9 = P_Attribute9,
1150 attribute10 = P_Attribute10,
1151 attribute11 = P_Attribute11,
1152 attribute12 = P_Attribute12,
1153 attribute13 = P_Attribute13,
1154 attribute14 = P_Attribute14,
1155 attribute15 = P_Attribute15,
1156 autodetail_pr_flag = P_Autodetail_Pr_Flag,
1157 carrier_id = P_Carrier_Id,
1158 trip_stop_id = P_Trip_Stop_Id,
1159 default_stage_subinventory = P_Default_Stage_Subinventory,
1160 default_stage_locator_id = P_Default_Stage_Locator_Id,
1161 pick_from_subinventory = P_Pick_From_Subinventory,
1162 pick_from_locator_id = P_Pick_From_locator_Id,
1163 auto_pick_confirm_flag = P_Auto_Pick_Confirm_Flag,
1164 project_id = P_Project_Id,
1165 task_id = P_Task_Id,
1166 delivery_detail_id = P_Delivery_Detail_Id,
1167 ship_confirm_rule_id = P_Ship_Confirm_Rule_Id,
1168 Autopack_Flag = P_Autopack_Flag,
1169 autopack_level = P_Autopack_Level,
1170 task_planning_flag = P_Task_Planning_Flag,
1171 dynamic_replenishment_flag = P_dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
1172 non_picking_flag = P_non_picking_flag,
1173 region_id = p_regionID,
1174 zone_id = p_zoneID,
1175 category_id = p_categoryID,
1176 category_set_id = p_categorySetID,
1177 ac_Delivery_criteria = p_acDelivcriteria,
1178 rel_subinventory = p_relsubinventory,
1179 append_flag = p_append_flag,
1180 task_priority = p_task_priority,
1181 actual_departure_date = p_actual_departure_date,
1182 allocation_method = nvl(p_allocation_method,'I'), -- X-dock
1183 crossdock_criteria_id = p_crossdock_criteria_id, -- X-dock
1184 client_id = p_client_Id --Modified R12.1.1 LSP PROJECT
1185 WHERE rowid = X_Rowid;
1186 --
1187 if (SQL%NOTFOUND) then
1188 --
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.log(l_module_name,'NO_DATA_FOUND');
1191 END IF;
1192 --
1193 Raise NO_DATA_FOUND;
1194 --
1195 end if;
1196 --
1197 IF l_debug_on THEN
1198 WSH_DEBUG_SV.pop(l_module_name);
1199 END IF;
1200 --
1201 END Update_Row;
1202
1203
1204
1205 PROCEDURE Delete_Row(X_Rowid IN OUT NOCOPY VARCHAR2) IS
1206 --
1207 l_debug_on BOOLEAN;
1208 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
1209 --
1210 BEGIN
1211 --
1212 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1213 --
1214 IF l_debug_on IS NULL
1215 THEN
1216 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1217 END IF;
1218 --
1219 IF l_debug_on THEN
1220 WSH_DEBUG_SV.push(l_module_name);
1221 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
1222 END IF;
1223 --
1224 DELETE FROM WSH_PICKING_BATCHES
1225 WHERE rowid = X_Rowid;
1226 --
1227 if (SQL%NOTFOUND) then
1228 IF l_debug_on THEN
1229 WSH_DEBUG_SV.log(l_module_name,'NO_DATA_FOUND');
1230 END IF;
1231 Raise NO_DATA_FOUND;
1232 end if;
1233 --
1234 IF l_debug_on THEN
1235 WSH_DEBUG_SV.pop(l_module_name);
1236 END IF;
1237 --
1238 END Delete_Row;
1239
1240
1241 FUNCTION Submit_Release_Request(P_Batch_Id NUMBER,
1242 P_Log_Level NUMBER ,
1243 P_Num_Workers NUMBER ,
1244 P_Commit VARCHAR2 ) RETURN NUMBER IS -- log level fix
1245
1246 -- Bug # 2231365 : Defaulted the parameter p_log_level to 0
1247
1248 request_id NUMBER;
1249 --
1250 l_debug_on BOOLEAN;
1251 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SUBMIT_RELEASE_REQUEST';
1252 --
1253 BEGIN
1254 --
1255 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1256 --
1257 IF l_debug_on IS NULL
1258 THEN
1259 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1260 END IF;
1261 --
1262 IF l_debug_on THEN
1263 --
1264 WSH_DEBUG_SV.push(l_module_name);
1265 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
1266 WSH_DEBUG_SV.log(l_module_name,'P_LOG_LEVEL',P_LOG_LEVEL);
1267 WSH_DEBUG_SV.log(l_module_name,'P_NUM_WORKERS',P_NUM_WORKERS);
1268 WSH_DEBUG_SV.log(l_module_name,'P_Commit',P_Commit);
1269 --
1270 END IF;
1271 --
1272 request_id := FND_REQUEST.Submit_Request('WSH', 'WSHPSGL','','',FALSE,
1273 P_Batch_Id, P_Log_Level, P_Num_Workers); -- log level fix
1274
1275 IF NVL(P_Commit, 'Y') <> 'N' THEN -- we commit by default
1276 --
1277 if (request_id > 0) then
1278 COMMIT WORK;
1279 end if;
1280 --
1281 END IF;
1282 --
1283 IF l_debug_on THEN
1284 WSH_DEBUG_SV.pop(l_module_name);
1285 END IF;
1286 --
1287 return request_id;
1288 --
1289 END Submit_Release_Request;
1290
1291
1292
1293 PROCEDURE Delete_And_Commit(X_Rowid IN OUT NOCOPY VARCHAR2) IS
1294 --
1295 l_debug_on BOOLEAN;
1296 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_AND_COMMIT';
1297 --
1298 BEGIN
1299 --
1300 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1301 --
1302 IF l_debug_on IS NULL
1303 THEN
1304 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1305 END IF;
1306 --
1307 IF l_debug_on THEN
1308 WSH_DEBUG_SV.push(l_module_name);
1309 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
1310 END IF;
1311 --
1312 Delete_Row(X_Rowid);
1313 COMMIT WORK;
1314 --
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.pop(l_module_name);
1317 END IF;
1318 --
1319 END Delete_And_Commit;
1320
1321
1322 PROCEDURE Commit_Work IS
1323 --
1324 l_debug_on BOOLEAN;
1325 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COMMIT_WORK';
1326 --
1327 BEGIN
1328 --
1329 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1330 --
1331 IF l_debug_on IS NULL
1332 THEN
1333 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1334 END IF;
1335 --
1336 IF l_debug_on THEN
1337 WSH_DEBUG_SV.push(l_module_name);
1338 END IF;
1339 --
1340 COMMIT WORK;
1341 --
1342 IF l_debug_on THEN
1343 WSH_DEBUG_SV.pop(l_module_name);
1344 END IF;
1345 --
1346 END Commit_Work;
1347
1348
1349
1350 PROCEDURE Get_Printer ( p_report IN VARCHAR2,
1351 x_report_printer OUT NOCOPY VARCHAR2,
1352 p_default_report IN VARCHAR2 default 'OEXSHPIK' ) IS
1353 --
1354 level_type_id NUMBER;
1355 app_id NUMBER;
1356 respid NUMBER;
1357 userid NUMBER;
1358 printer varchar2(32);
1359 --
1360 l_debug_on BOOLEAN;
1361 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PRINTER';
1362 --
1363 BEGIN
1364 --
1365 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1366 --
1367 IF l_debug_on IS NULL
1368 THEN
1369 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1370 END IF;
1371 --
1372 IF l_debug_on THEN
1373 --
1374 WSH_DEBUG_SV.push(l_module_name);
1375 WSH_DEBUG_SV.log(l_module_name,'P_REPORT',P_REPORT);
1376 WSH_DEBUG_SV.log(l_module_name,'P_DEFAULT_REPORT',P_DEFAULT_REPORT);
1377 --
1378 END IF;
1379 --
1380 -- get the applications, responsibility, and user ID
1381 app_id := FND_GLOBAL.RESP_APPL_ID;
1382 respid := FND_GLOBAL.RESP_ID;
1383 userid := FND_GLOBAL.USER_ID;
1384
1385 -- get pick slip printer
1386 /* SELECT MAX(P.LEVEL_TYPE_ID)
1387 INTO level_type_id
1388 FROM WSH_REPORT_PRINTERS P,
1389 SO_REPORTS R
1390 WHERE P.REPORT_SET_ID = R.REPORT_ID
1391 AND R.NAME =
1392 NVL(report, default_report)
1393 AND P.LEVEL_VALUE_ID = DECODE(P.LEVEL_TYPE_ID,
1394 10001,0,
1395 10002,app_id,
1396 10003,respid,
1397 10004,userid)
1398 AND ENABLE_FLAG = 'Y';
1399
1400 SELECT P.PRINTER_NAME
1401 INTO printer
1402 FROM WSH_REPORT_PRINTERS P,
1403 SO_REPORTS R
1404 WHERE P.REPORT_ID = R.REPORT_ID
1405 AND R.NAME =
1406 NVL(report, default_report)
1407 AND P.LEVEL_TYPE_ID = level_type_id
1408 AND P.LEVEL_VALUE_ID = DECODE(level_type_id,
1409 10001,0,
1410 10002,app_id,
1411 10003,respid,
1412 10004,userid);
1413 */
1414 x_report_printer := printer;
1415
1416 --
1417 IF l_debug_on THEN
1418 WSH_DEBUG_SV.pop(l_module_name);
1419 END IF;
1420 --
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 x_report_printer := NULL;
1424 --
1425 IF l_debug_on THEN
1426 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1427 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1428 END IF;
1429 --
1430 END get_printer;
1431
1432 END WSH_PICKING_BATCHES_PKG;