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