1 PACKAGE BODY WSH_PR_PICK_SLIP_NUMBER AS
2 /* $Header: WSHPRPNB.pls 120.1 2006/06/20 09:06:52 aymohant noship $ */
3
4 /*
5 ############### PLEASE READ ####################################
6 Following type/variable declarations were owned by WSH until
7 patchset H (11.5.8).
8 From Patchset-I onwards, ownership has been transfered to INV Team.
9 Please do not modify these APIs for any ongoing development
10 or bug-fixes from Patchset-I and beyond.
11
12 Modify these type/variable declarations only if you are making bug-fix for
13 pre-I
14 customers. Please consult Nikhil Parikh/Anil Verma, if you have
15 any questions.
16
17 The APIs are maintained here only for backward-compatibility, i.e.
18 if customer has applied INV-H and WSH-I, it should still
19 continue to work.
20
21 */
22 --
23 -- PACKAGE TYPES
24 --
25 TYPE keyRecTyp IS RECORD (
26 grouping_rule_id NUMBER := FND_API.G_MISS_NUM,
27 header_id NUMBER := FND_API.G_MISS_NUM,
28 customer_id NUMBER := FND_API.G_MISS_NUM,
29 ship_method_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
30 ship_to_loc_id NUMBER := FND_API.G_MISS_NUM,
31 shipment_priority VARCHAR2(30) := FND_API.G_MISS_CHAR,
32 subinventory VARCHAR2(10) := FND_API.G_MISS_CHAR,
33 trip_stop_id NUMBER := FND_API.G_MISS_NUM,
34 delivery_id NUMBER := FND_API.G_MISS_NUM,
35 inventory_item_id NUMBER := FND_API.G_MISS_NUM,
36 locator_id NUMBER := FND_API.G_MISS_NUM,
37 -- HW OPMCONV - Increate the size of lot_number
38 lot_number VARCHAR2(80) := FND_API.G_MISS_CHAR,
39 revision VARCHAR2(3) := FND_API.G_MISS_CHAR,
40 organization_id NUMBER := FND_API.G_MISS_NUM,
41 pick_slip_number NUMBER := FND_API.G_MISS_NUM,
42 counter NUMBER := FND_API.G_MISS_NUM
43 );
44
45
46 TYPE keyTabTyp IS TABLE OF keyRecTyp INDEX BY BINARY_INTEGER;
47
48 TYPE grpRecTyp IS RECORD (
49 grouping_rule_id NUMBER := FND_API.G_MISS_NUM,
50 use_order_ps VARCHAR2(1) := 'N',
51 use_sub_ps VARCHAR2(1) := 'N',
52 use_customer_ps VARCHAR2(1) := 'N',
53 use_ship_to_ps VARCHAR2(1) := 'N',
54 use_carrier_ps VARCHAR2(1) := 'N',
55 use_ship_priority_ps VARCHAR2(1) := 'N',
56 use_trip_stop_ps VARCHAR2(1) := 'N',
57 use_delivery_ps VARCHAR2(1) := 'N',
58 use_item_ps VARCHAR2(1) := 'N',
59 use_locator_ps VARCHAR2(1) := 'N',
60 use_lot_ps VARCHAR2(1) := 'N',
61 use_revision_ps VARCHAR2(1) := 'N',
62 pick_method VARCHAR2(30) := '-99'
63 );
64
65 TYPE grpTabTyp IS TABLE OF grpRecTyp INDEX BY BINARY_INTEGER;
66
67 --
68 -- PACKAGE VARIABLES
69 --
70 g_rule_table grpTabTyp;
71 g_pskey_table keyTabTyp;
72
73 g_hash_base NUMBER := 1;
74 g_hash_size NUMBER := power(2, 25);
75
76 -- For cahing the limit information for an org
77
78 g_prev_org_id NUMBER;
79 g_pickslip_limit NUMBER;
80 --
81 --
82 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PR_PICK_SLIP_NUMBER';
83 --
84 --
85 -- Name
86 -- PROCEDURE Print_Pvt
87 --
88 -- Purpose
89 -- Print Pick Slip based on Pick Slip Number
90 --
91 -- Input Parameter
92 -- p_report_set_id => report set
93 -- p_pick_slip_number => pick slip number
94 -- p_order_header_id => sales Order Header id
95 -- Order Header id is mainly to Obtain the Order Number which has to be passed on
96 -- to the Call to Document Set thru l_document_info table. (Ref. bug: 1520991)
97 -- p_batch_id => batch id , which is also the Move Order High and Low,
98 -- this is passed on for an entire Batch,
99 -- ie. when a particular P.slip No. is not specified
100 --
101 -- Output Parameters
102 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
103 -- FND_API.G_RET_STS_ERROR or
104 -- FND_API.G_RET_STS_UNEXP_ERROR
105 --
106 PROCEDURE Print_Pvt (
107 p_report_set_id IN NUMBER,
108 p_organization_id IN NUMBER,
109 p_pick_slip_number IN NUMBER,
110 p_order_header_id IN NUMBER,
111 p_batch_id IN NUMBER,
112 p_ps_mode IN VARCHAR2 default NULL ,
113 x_api_status OUT NOCOPY VARCHAR2)
114 IS
115
116 CURSOR get_order_number (X_order_header_id in number) is
117 SELECT order_number
118 FROM oe_order_headers_all
119 WHERE header_id = X_order_header_id;
120
121 -- Bug# 1577520 - Pass the Batch Name to MoveOrderHeader, instead of Batch Id
122 -- Prasanna Vanguri 5th March'01
123 CURSOR get_batch_name (X_batch_id in number ) is
124 SELECT name
125 FROM wsh_picking_batches
126 WHERE batch_id = X_batch_id;
127
128 l_batch_name varchar2(30);
129 l_order_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE;
130 l_report_set_id NUMBER;
131 l_trip_ids WSH_UTIL_CORE.Id_Tab_Type;
132 l_stop_ids WSH_UTIL_CORE.Id_Tab_Type;
133 l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
134 l_document_info WSH_DOCUMENT_SETS.document_set_tab_type;
135 l_organization_id NUMBER;
136
137 --
138 l_debug_on BOOLEAN;
139 --
140 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_PVT';
141 --
142 BEGIN
143 --
144 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
145 --
146 IF l_debug_on IS NULL
147 THEN
148 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
149 END IF;
150 --
151
152 --
153 IF l_debug_on THEN
154 WSH_DEBUG_SV.push(l_module_name);
155 WSH_DEBUG_SV.logmsg(l_module_name, 'Inside print_pvt ');
156 --
157 WSH_DEBUG_SV.log(l_module_name,'P_REPORT_SET_ID',P_REPORT_SET_ID);
158 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
159 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SLIP_NUMBER',P_PICK_SLIP_NUMBER);
160 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
161 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
162 WSH_DEBUG_SV.logmsg(l_module_name, 'REPORT SET ID ' || TO_CHAR ( P_REPORT_SET_ID ) );
163 WSH_DEBUG_SV.logmsg(l_module_name, 'p_ps_mode ' || p_ps_mode );
164 WSH_DEBUG_SV.logmsg(l_module_name, 'count of g_printertab ' || WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count );
165 END IF;
166 --
167 l_order_number := null;
168 IF ( nvl(p_order_header_id,0) <> 0) THEN
169 OPEN get_order_number(p_order_header_id);
170 FETCH get_order_number
171 INTO l_order_number;
172
173 IF get_order_number%NOTFOUND THEN
174 null;
175 END IF;
176 --
177 IF l_debug_on THEN
178 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER HDR ID: ' || TO_CHAR ( P_ORDER_HEADER_ID ) );
179 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER NUMBER: ' || L_ORDER_NUMBER );
180 END IF;
181 --
182 CLOSE get_order_number;
183 END IF;
184
185 l_report_set_id := p_report_set_id;
186 IF ( nvl(p_batch_id, 0) <> 0) THEN
187 --Bug# 1577520 , Assign Batch name to Move Order , instead of Batchid
188
189 OPEN get_batch_name(p_batch_id);
190 FETCH get_batch_name into l_batch_name;
191 CLOSE get_batch_name;
192
193 l_document_info(1).p_move_order_h := l_batch_name;
194 l_document_info(1).p_move_order_l := l_batch_name;
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.log(l_module_name,'l_batch_name',l_batch_name);
197 END IF;
198
199 ELSE
200 l_document_info(1).pick_slip_num_l := p_pick_slip_number;
201 l_document_info(1).pick_slip_num_h := p_pick_slip_number;
202 END IF;
203
204 IF ( nvl(p_order_header_id,0) <> 0) THEN
205 l_document_info(1).p_order_num_low := l_order_number;
206 l_document_info(1).p_order_num_high := l_order_number;
207 END IF;
208
209 l_organization_id := p_organization_id;
210
211
212 if p_ps_mode <> 'I' and WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count > 0 Then
213 for i in 1..WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count LOOP
214
215 l_document_info(1).p_printer_name := WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i);
216 --
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Print_Document_sets for printer ' || WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i) );
219 END IF ;
220 --
221 WSH_DOCUMENT_SETS.Print_Document_Sets(
222 p_report_set_id => l_report_set_id,
223 p_organization_id => l_organization_id,
224 p_trip_ids => l_trip_ids,
225 p_stop_ids => l_stop_ids,
226 p_delivery_ids => l_delivery_ids,
227 p_document_param_info => l_document_info,
228 x_return_status => x_api_status);
229 --
230 IF l_debug_on THEN
231 WSH_DEBUG_SV.log(l_module_name,'x_api_status',x_api_status);
232 WSH_DEBUG_SV.pop(l_module_name);
233 END IF;
234 --
235 End LOOP ;
236 else
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.logmsg(l_module_name, 'ORDER NUMBER: ' || L_ORDER_NUMBER );
239 END IF;
240 WSH_DOCUMENT_SETS.Print_Document_Sets(
241 p_report_set_id => l_report_set_id,
242 p_organization_id => l_organization_id,
243 p_trip_ids => l_trip_ids,
244 p_stop_ids => l_stop_ids,
245 p_delivery_ids => l_delivery_ids,
246 p_document_param_info => l_document_info,
247 x_return_status => x_api_status);
248 --
249 IF l_debug_on THEN
250 WSH_DEBUG_SV.log(l_module_name,'x_api_status',x_api_status);
251 WSH_DEBUG_SV.pop(l_module_name);
252 END IF;
253 --
254 end if ;
255
256 END Print_Pvt;
257 --
258 -- Name
259 -- PROCEDURE Print_Pick_Slip
260 --
261 -- Purpose
262 -- This function initializesthe g_use_ variables to be used
263 -- in determining the how to group pick slips.
264 --
265 -- Input Parameters
266 -- p_pick_slip_number => pick slip number
267 -- p_report_set_id => report set
268 -- p_order_header_id => Order Header id
269 -- p_batch_id => Batch Id of the Picking Batch id
270 --
271 -- Output Parameters
272 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
273 -- FND_API.G_RET_STS_ERROR or
274 -- FND_API.G_RET_STS_UNEXP_ERROR
275 -- x_error_message => Error message
276 --
277 PROCEDURE Print_Pick_Slip (
278 p_pick_slip_number IN NUMBER DEFAULT FND_API.G_MISS_NUM,
279 p_report_set_id IN NUMBER,
280 p_organization_id IN NUMBER,
281 p_order_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
282 p_batch_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
283 p_ps_mode IN VARCHAR2 DEFAULT NULL,
284 x_api_status OUT NOCOPY VARCHAR2,
285 x_error_message OUT NOCOPY VARCHAR2 )
286 IS
287 l_index NUMBER;
288 l_ps_num NUMBER;
289 l_organization_id NUMBER;
290 l_batch_id NUMBER;
291
292 --
293 l_debug_on BOOLEAN;
294 --
295 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_PICK_SLIP';
296 l_pick_slip_number NUMBER;
297 --
298 CURSOR ps_list IS
299 SELECT pick_slip_number
300 FROM mtl_pick_slip_numbers
301 WHERE pick_slip_batch_id = WSH_PICK_LIST.G_BATCH_ID
302 AND status = 1;
303
304 BEGIN
305
306 /* p_report_set_id is no longer used as we print the seeded Pick Slip report always
307 Stored in WSH_PICK_LIST.G_SEED_DOC_SET. Keeping it for compilation dependency
308 */
309 --
310 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
311 --
312 IF l_debug_on IS NULL
313 THEN
314 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
315 END IF;
316 --
317 --
318 IF l_debug_on THEN
319 WSH_DEBUG_SV.push(l_module_name);
320 --
321 WSH_DEBUG_SV.log(l_module_name,'P_PICK_SLIP_NUMBER',P_PICK_SLIP_NUMBER);
322 WSH_DEBUG_SV.log(l_module_name,'P_REPORT_SET_ID',P_REPORT_SET_ID);
323 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
324 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
325 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
326 WSH_DEBUG_SV.log(l_module_name,'P_PS_MODE',P_PS_MODE);
327 END IF;
328 --
329 /*
330 ############### PLEASE READ ####################################
331 Get_pick_slip_number API was owned by WSH until patchset H (11.5.8).
332 From Patchset-I onwards, ownership has been transfered to INV Team.
333
334 This API populates g_pskey_table and Print API reads from the table.
335
336 Hence, we have the following in-line branch in the code.
337
338 IF WSH is at level before I, we continue to read
339 from wsh_pr_pick_slip_number.g_pskey_table
340 Otherwise,
341 we read from inv_pr_pick_slip_number.g_pskey_table
342 */
343
344 IF WSH_CODE_CONTROL.Get_Code_Release_Level >= '110509'
345 THEN
346 l_index := INV_PR_PICK_SLIP_NUMBER.g_pskey_table.first;
347 ELSE
348 l_index := g_pskey_table.first;
349 END IF;
350 --
351 x_api_status := FND_API.G_RET_STS_SUCCESS;
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.log(l_module_name,'l_index',l_index);
354 END IF;
355
356 -- If report set id is NULL, there is no report to print
357 IF (WSH_PICK_LIST.G_SEED_DOC_SET IS NULL) THEN
358 x_api_status := FND_API.G_RET_STS_SUCCESS;
359 --
360 IF l_debug_on THEN
361 WSH_DEBUG_SV.log(l_module_name,'there is no report to print');
362 WSH_DEBUG_SV.pop(l_module_name);
363 END IF;
364 --
365 RETURN;
366 END IF;
367
368 -- Added if condition IF WSH_PICK_LIST.G_PICK_REL_PARALLEL for
369 -- parallel pick-release
370
371 IF (p_pick_slip_number = FND_API.G_MISS_NUM) THEN
372 IF (p_ps_mode = 'I') THEN
373 -- Loop through the pl-sql table to print the remaining pick slips
374 IF WSH_PICK_LIST.G_PICK_REL_PARALLEL THEN
375 OPEN ps_list;
376 LOOP
377 FETCH ps_list into l_pick_slip_number;
378 EXIT WHEN ps_list%NOTFOUND;
379 Print_Pvt(p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
380 p_organization_id => p_organization_id,
381 p_pick_slip_number => l_pick_slip_number,
382 p_order_header_id => p_order_header_id,
383 p_batch_id => NULL,
384 p_ps_mode => p_ps_mode ,
385 x_api_status => x_api_status);
386 END LOOP;
387 CLOSE ps_list;
388 ELSE
389 WHILE l_index IS NOT NULL LOOP
390 l_batch_id := null; /* Since specific P.slip Numbers are used here */
391 --
392 IF WSH_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
393 Print_Pvt(p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
394 p_organization_id => p_organization_id,
395 p_pick_slip_number => INV_PR_PICK_SLIP_NUMBER.g_pskey_table(l_index).pick_slip_number,
396 p_order_header_id => p_order_header_id,
397 p_batch_id => l_batch_id,
398 p_ps_mode => p_ps_mode ,
399 x_api_status => x_api_status);
400 -- Remove from table
401 INV_PR_PICK_SLIP_NUMBER.g_pskey_table.delete(l_index);
402 l_index := INV_PR_PICK_SLIP_NUMBER.g_pskey_table.next(l_index);
403 ELSE
404 Print_Pvt(p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
405 p_organization_id => p_organization_id,
406 p_pick_slip_number => g_pskey_table(l_index).pick_slip_number,
407 p_order_header_id => p_order_header_id,
408 p_batch_id => l_batch_id,
409 p_ps_mode => p_ps_mode ,
410 x_api_status => x_api_status);
411 -- Remove from table
412 g_pskey_table.delete(l_index);
413 l_index := g_pskey_table.next(l_index);
414 END IF;
415 --
416 END LOOP;
417 END IF;
418 ELSE
419 l_ps_num := null; /* Since this is for an entire Batch and Not for p.Slip No (s) */
420 --
421 Print_Pvt(p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
422 p_organization_id => p_organization_id,
423 p_pick_slip_number => l_ps_num,
424 p_order_header_id => p_order_header_id,
425 p_batch_id => p_batch_id,
426 p_ps_mode => p_ps_mode ,
427 x_api_status => x_api_status);
428 --
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.logmsg(l_module_name, 'X_API_STATUS PRINT_PVT:'|| X_API_STATUS );
431 END IF;
432 --
433 END IF;
434 ELSE
435 Print_Pvt(p_report_set_id => WSH_PICK_LIST.G_SEED_DOC_SET,
436 p_organization_id => p_organization_id,
437 p_pick_slip_number => p_pick_slip_number,
438 p_order_header_id => p_order_header_id,
439 p_batch_id => l_batch_id,
440 p_ps_mode => p_ps_mode ,
441 x_api_status => x_api_status);
442 END IF;
443
444 IF x_api_status <> FND_API.G_RET_STS_SUCCESS THEN
445 x_error_message := 'Error occurred in call to ' ||
446 'Print_Pvt in ' ||
447 'WSH_PR_PICK_SLIP_NUMBER.Print_Pick_Slip';
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.log(l_module_name,'Error:',x_error_message);
450
451 END IF;
452 END IF;
453 --
454 IF l_debug_on THEN
455 WSH_DEBUG_SV.log(l_module_name,'x_api_status',x_api_status);
456 WSH_DEBUG_SV.pop(l_module_name);
457 END IF;
458 --
459 EXCEPTION
460 WHEN OTHERS THEN
461 x_error_message := 'Exception occurred in WSH_PR_PICK_SLIP_NUMBER.Print_Pick_Slip';
462 x_api_status := FND_API.G_RET_STS_UNEXP_ERROR;
463 --
464 IF l_debug_on THEN
465 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
466 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
467 END IF;
468 --
469 END Print_Pick_Slip;
470
471 --Procedure
472 -- Delete_Pick_Slip_Numbers for Parallel Pick-Release process
473 --
474
475 PROCEDURE delete_pick_slip_numbers(p_batch_id IN NUMBER) IS
476 BEGIN
477 /*if nvl(p_batch_id, -1) = -1 then
478 p_batch_id := wsh_pick_list.g_batch_id;
479 end if; */
480
481 DELETE FROM mtl_pick_slip_numbers
482 WHERE pick_slip_batch_id = p_batch_id;
483
484 END delete_pick_slip_numbers;
485
486 /*
487 ############### PLEASE READ ####################################
488 Following APIs were owned by WSH until patchset H (11.5.8).
489 From Patchset-I onwards, ownership has been transfered to INV Team.
490 Please do not modify these APIs for any ongoing development
491 or bug-fixes from Patchset-I and beyond.
492
493 Modify these APIs only if you are making bug-fix for pre-I
494 customers. Please consult Nikhil Parikh/Anil Verma, if you have
495 any questions.
496
497 The APIs are maintained here only for backward-compatibility, i.e.
498 if customer has applied INV-H and WSH-I, it should still
499 continue to work.
500
501 */
502 --
503 -- Name
504 -- PROCEDURE Insert_Key
505 --
506 -- Purpose
507 -- Insert new key to table and returns newly generated pick slip number
508 --
509 -- Input Parameter
510 -- l_hash_value
511 -- l_Insert_key_Rec
512 --
513 -- Output Parameter
514 -- x_pick_slip_number => pick_slip_number
515 -- x_error_message => Error message
516 --
517
518 PROCEDURE Insert_Key (
519 l_hash_value IN NUMBER,
520 l_Insert_key_Rec IN keyRecTyp,
521 x_pick_slip_number OUT NOCOPY NUMBER,
522 x_error_message OUT NOCOPY VARCHAR2)
523 IS
524
525 BEGIN
526 /*
527 ############### PLEASE READ ####################################
528 Following APIs were owned by WSH until patchset H (11.5.8).
529 From Patchset-I onwards, ownership has been transfered to INV Team.
530 Please do not modify these APIs for any ongoing development
531 or bug-fixes from Patchset-I and beyond.
532
533 Modify these APIs only if you are making bug-fix for pre-I
534 customers. Please consult Nikhil Parikh/Anil Verma, if you have
535 any questions.
536
537 The APIs are maintained here only for backward-compatibility, i.e.
538 if customer has applied INV-H and WSH-I, it should still
539 continue to work.
540
541 */
542 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL
543 INTO x_pick_slip_number
544 FROM DUAL;
545
546 g_pskey_table(l_hash_value) := l_Insert_key_Rec;
547 g_pskey_table(l_hash_value).counter := 1;
548 g_pskey_table(l_hash_value).pick_slip_number := x_pick_slip_number;
549 EXCEPTION
550 WHEN OTHERS THEN
551 x_error_message := 'Error occurred in WSH_PR_PICK_NUMBER.Insert_Key';
552 END Insert_Key;
553
554 --
555 -- Name
556 -- PROCEDURE CreateHash
557 --
558 -- Purpose
559 -- Generate a hash value for the given values for the column strings
560 --
561 -- Input Parameter
562 -- p_rule_index => index to the grouping rule table
563 -- p_header_id => order header id
564 -- p_customer_id => customer id
565 -- p_ship_method_code => ship method
566 -- p_ship_to_loc_id => ship to location
567 -- p_shipment_priority => shipment priority
568 -- p_subinventory => subinventory
569 -- p_trip_stop_id => trip stop
570 -- p_delivery_id => delivery
571 -- p_inventory_item_id => item
572 -- p_locator_id => locator
573 -- p_lot_number => lot number
574 -- p_revision => revision
575 -- p_org_id => organization
576 --
577 -- Output Parameter
578 -- x_hash_value => hash value for g_pskey_table
579 -- x_Insert_key_Rec => keyRecTyp
580 -- x_error_message => Error message
581 --
582 /*
583 ############### PLEASE READ ####################################
584 Following APIs were owned by WSH until patchset H (11.5.8).
585 From Patchset-I onwards, ownership has been transfered to INV Team.
586 Please do not modify these APIs for any ongoing development
587 or bug-fixes from Patchset-I and beyond.
588
589 Modify these APIs only if you are making bug-fix for pre-I
590 customers. Please consult Nikhil Parikh/Anil Verma, if you have
591 any questions.
592
593 The APIs are maintained here only for backward-compatibility, i.e.
594 if customer has applied INV-H and WSH-I, it should still
595 continue to work.
596
597 */
598
599 PROCEDURE Create_Hash (
600 p_rule_index IN NUMBER,
601 p_header_id IN NUMBER,
602 p_customer_id IN NUMBER,
603 p_ship_method_code IN VARCHAR2,
604 p_ship_to_loc_id IN NUMBER,
605 p_shipment_priority IN VARCHAR2,
606 p_subinventory IN VARCHAR2,
607 p_trip_stop_id IN NUMBER,
608 p_delivery_id IN NUMBER,
609 p_inventory_item_id IN NUMBER,
610 p_locator_id IN NUMBER,
611 p_lot_number IN VARCHAR2,
612 p_revision IN VARCHAR2,
613 p_org_id IN NUMBER,
614 x_hash_value OUT NOCOPY NUMBER,
615 x_Insert_key_Rec OUT NOCOPY keyRecTyp,
616 x_error_message OUT NOCOPY VARCHAR2)
617 IS
618 l_hash_string VARCHAR2(2000) := NULL;
619
620 BEGIN
621 /*
622 ############### PLEASE READ ####################################
623 Following APIs were owned by WSH until patchset H (11.5.8).
624 From Patchset-I onwards, ownership has been transfered to INV Team.
625 Please do not modify these APIs for any ongoing development
626 or bug-fixes from Patchset-I and beyond.
627
628 Modify these APIs only if you are making bug-fix for pre-I
629 customers. Please consult Nikhil Parikh/Anil Verma, if you have
630 any questions.
631
632 The APIs are maintained here only for backward-compatibility, i.e.
633 if customer has applied INV-H and WSH-I, it should still
634 continue to work.
635
636 */
637
638 l_hash_string := to_char(g_rule_table(p_rule_index).grouping_rule_id);
639
640 x_Insert_key_Rec.grouping_rule_id := g_rule_table(p_rule_index).grouping_rule_id;
641
642 IF (g_rule_table(p_rule_index).use_order_ps = 'Y') THEN
643 l_hash_string := l_hash_string ||'-'|| to_char(p_header_id);
644 x_Insert_key_Rec.header_id := p_header_id;
645 END IF;
646 IF (g_rule_table(p_rule_index).use_sub_ps = 'Y') THEN
647 l_hash_string := l_hash_string ||'-'|| p_subinventory;
648 x_Insert_key_Rec.subinventory := p_subinventory;
649 END IF;
650 IF (g_rule_table(p_rule_index).use_customer_ps = 'Y') THEN
651 l_hash_string := l_hash_string ||'-'|| to_char(p_customer_id);
652 x_Insert_key_Rec.customer_id := p_customer_id;
653 END IF;
654 IF (g_rule_table(p_rule_index).use_carrier_ps = 'Y') THEN
655 l_hash_string := l_hash_string ||'-'|| p_ship_method_code;
656 x_Insert_key_Rec.ship_method_code := p_ship_method_code;
657 END IF;
658 IF (g_rule_table(p_rule_index).use_ship_to_ps = 'Y') THEN
659 l_hash_string := l_hash_string ||'-'|| to_char(p_ship_to_loc_id);
660 x_Insert_key_Rec.ship_to_loc_id := p_ship_to_loc_id;
661 END IF;
662 IF (g_rule_table(p_rule_index).use_ship_priority_ps = 'Y') THEN
663 l_hash_string := l_hash_string ||'-'|| p_shipment_priority;
664 x_Insert_key_Rec.shipment_priority := p_shipment_priority;
665 END IF;
666 IF (g_rule_table(p_rule_index).use_trip_stop_ps = 'Y') THEN
667 l_hash_string := l_hash_string ||'-'|| to_char(p_trip_stop_id);
668 x_Insert_key_Rec.trip_stop_id := p_trip_stop_id;
669 END IF;
670 IF (g_rule_table(p_rule_index).use_delivery_ps = 'Y') THEN
671 l_hash_string := l_hash_string ||'-'|| to_char(p_delivery_id);
672 x_Insert_key_Rec.delivery_id := p_delivery_id;
673 END IF;
674
675 IF (g_rule_table(p_rule_index).use_item_ps = 'Y') THEN
676 l_hash_string := l_hash_string ||'-'|| to_char(p_inventory_item_id);
677 x_Insert_key_Rec.inventory_item_id := p_inventory_item_id;
678 END IF;
679 IF (g_rule_table(p_rule_index).use_locator_ps = 'Y') THEN
680 l_hash_string := l_hash_string ||'-'|| to_char(p_locator_id);
681 x_Insert_key_Rec.locator_id := p_locator_id;
682 END IF;
683 IF (g_rule_table(p_rule_index).use_lot_ps = 'Y') THEN
684 l_hash_string := l_hash_string ||'-'|| p_lot_number;
685 x_Insert_key_Rec.lot_number := p_lot_number;
686 END IF;
687 IF (g_rule_table(p_rule_index).use_revision_ps = 'Y') THEN
688 l_hash_string := l_hash_string ||'-'|| p_revision;
689 x_Insert_key_Rec.revision := p_revision;
690 END IF;
691
692 x_Insert_key_Rec.organization_id := p_org_id;
693 l_hash_string := l_hash_string ||'-'|| to_char(p_org_id);
694
695 x_hash_value := dbms_utility.get_hash_value(
696 name => l_hash_string,
697 base => g_hash_base,
698 hash_size =>g_hash_size );
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 x_error_message := 'Error occurred in WSH_PR_PICK_NUMBER.Create_Hash';
703 END Create_Hash;
704
705 /*
706 ############### PLEASE READ ####################################
707 Following APIs were owned by WSH until patchset H (11.5.8).
708 From Patchset-I onwards, ownership has been transfered to INV Team.
709 Please do not modify these APIs for any ongoing development
710 or bug-fixes from Patchset-I and beyond.
711
712 Modify these APIs only if you are making bug-fix for pre-I
713 customers. Please consult Nikhil Parikh/Anil Verma, if you have
714 any questions.
715
716 The APIs are maintained here only for backward-compatibility, i.e.
717 if customer has applied INV-H and WSH-I, it should still
718 continue to work.
719
720 */
721 --
722 -- Name
723 -- PROCEDURE Get_Pick_Slip_Number
724 --
725 -- Purpose
726 -- Returns pick slip number
727 --
728 -- Input Parameters
729 -- p_ps_mode => pick slip print mode: I=immed, E=deferred
730 -- p_pick_grouping_rule_id => pick grouping rule id
731 -- p_org_id => organization_id
732 -- p_header_id => order header id
733 -- p_customer_id => customer id
734 -- p_ship_method_code => ship method
735 -- p_ship_to_loc_id => ship to location
736 -- p_shipment_priority => shipment priority
737 -- p_subinventory => subinventory
738 -- p_trip_stop_id => trip stop
739 -- p_delivery_id => delivery
740 -- p_inventory_item_id => item
741 -- p_locator_id => locator
742 -- p_lot_number => lot number
743 -- p_revision => revision
744 --
745 -- Output Parameters
746 -- x_pick_slip_number => pick_slip_number
747 -- x_ready_to_print => FND_API.G_TRUE or FND_API.G_FALSE
748 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
749 -- FND_API.G_RET_STS_ERROR or
750 -- FND_API.G_RET_STS_UNEXP_ERROR
751 -- x_error_message => Error message
752 --
753 PROCEDURE Get_Pick_Slip_Number (
754 p_ps_mode IN VARCHAR2,
755 p_pick_grouping_rule_id IN NUMBER,
756 p_org_id IN NUMBER,
757 p_header_id IN NUMBER,
758 p_customer_id IN NUMBER,
759 p_ship_method_code IN VARCHAR2,
760 p_ship_to_loc_id IN NUMBER,
761 p_shipment_priority IN VARCHAR2,
762 p_subinventory IN VARCHAR2,
763 p_trip_stop_id IN NUMBER,
764 p_delivery_id IN NUMBER,
765 p_inventory_item_id IN NUMBER DEFAULT NULL,
766 p_locator_id IN NUMBER DEFAULT NULL,
767 p_lot_number IN VARCHAR2 DEFAULT NULL,
768 p_revision IN VARCHAR2 DEFAULT NULL,
769 x_pick_slip_number OUT NOCOPY NUMBER,
770 x_ready_to_print OUT NOCOPY VARCHAR2,
771 x_call_mode OUT NOCOPY VARCHAR2,
772 x_api_status OUT NOCOPY VARCHAR2,
773 x_error_message OUT NOCOPY VARCHAR2
774 ) IS
775 -- cursor to get the pick slip grouping rule
776 CURSOR ps_rule (v_pgr_id IN NUMBER) IS
777 SELECT NVL(ORDER_NUMBER_FLAG, 'N'),
778 NVL(SUBINVENTORY_FLAG, 'N'),
779 NVL(CUSTOMER_FLAG, 'N'),
780 NVL(SHIP_TO_FLAG, 'N'),
781 NVL(CARRIER_FLAG, 'N'),
782 NVL(SHIPMENT_PRIORITY_FLAG, 'N'),
783 NVL(TRIP_STOP_FLAG, 'N'),
784 NVL(DELIVERY_FLAG, 'N'),
785 NVL(ITEM_FLAG, 'N'),
786 NVL(LOCATOR_FLAG, 'N'),
787 NVL(LOT_FLAG, 'N'),
788 NVL(REVISION_FLAG, 'N'),
789 NVL(PICK_METHOD,'-99')
790 FROM WSH_PICK_GROUPING_RULES
791 WHERE PICK_GROUPING_RULE_ID = v_pgr_id;
792
793 -- cursor to get number of times called before printer
794 CURSOR get_limit (v_org_id IN NUMBER) IS
795 SELECT NVL(pick_slip_lines,-1)
796 FROM WSH_SHIPPING_PARAMETERS
797 WHERE ORGANIZATION_ID = v_org_id;
798
799 l_limit NUMBER;
800 l_Insert_key_Rec keyRecTyp;
801 l_hash_value NUMBER;
802 l_rule_index NUMBER;
803 l_found BOOLEAN;
804 i NUMBER;
805
806 BEGIN
807 /*
808 ############### PLEASE READ ####################################
809 Following APIs were owned by WSH until patchset H (11.5.8).
810 From Patchset-I onwards, ownership has been transfered to INV Team.
811 Please do not modify these APIs for any ongoing development
812 or bug-fixes from Patchset-I and beyond.
813
814 Modify these APIs only if you are making bug-fix for pre-I
815 customers. Please consult Nikhil Parikh/Anil Verma, if you have
816 any questions.
817
818 The APIs are maintained here only for backward-compatibility, i.e.
819 if customer has applied INV-H and WSH-I, it should still
820 continue to work.
821
822 */
823 IF (WSH_PICK_LIST.G_BATCH_ID IS NOT NULL) THEN
824 -- Needed for inventory to know whether this API is triggered manually
825 -- or through pick release
826 x_call_mode := 'Y';
827 END IF;
828
829 -- get the number of times called for a pick slip before
830 -- setting the ready to print flag to TRUE, if print is immediate
831 -- pickslip limit is cahed and fetched only if current org defers from the last org
832
833 IF p_ps_mode = 'I' THEN
834 IF p_org_id = g_prev_org_id THEN
835 l_limit := g_pickslip_limit;
836 ELSE
837 OPEN get_limit(p_org_id);
838 FETCH get_limit INTO l_limit;
839 IF get_limit%NOTFOUND THEN
840 x_error_message := 'Organization ' ||
841 to_char(p_org_id) ||
842 ' does not exist. ';
843 x_api_status := FND_API.G_RET_STS_ERROR;
844 RETURN;
845 END IF;
846 g_prev_org_id := p_org_id;
847 g_pickslip_limit := l_limit;
848 END IF;
849 END IF;
850
851
852 -- Set ready to print flag to FALSE initially
853 x_ready_to_print := FND_API.G_FALSE;
854
855 -- find grouping rule in table
856 l_found := FALSE;
857
858
859 IF g_rule_table.exists(p_pick_grouping_rule_id) THEN
860 l_found := TRUE;
861 l_rule_index := p_pick_grouping_rule_id;
862 END IF;
863
864
865
866 IF ((l_found) AND (g_rule_table(l_rule_index).pick_method = '3')) THEN -- Cluster Picking
867 /* Do not store the pick slip numbers generated for cluster picking
868 as we want to generate a new one for each line
869 */
870 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL
871 INTO x_pick_slip_number
872 FROM DUAL;
873
874 x_api_status := FND_API.G_RET_STS_SUCCESS;
875 RETURN;
876 END IF;
877 -- Create hash for g_pskey table here instead of in Insert_Key
878
879 -- if not found, fetch information about pick slip grouping rule
880 IF (NOT l_found) THEN
881 l_rule_index := p_pick_grouping_rule_id;
882 OPEN ps_rule(p_pick_grouping_rule_id);
883 FETCH ps_rule
884 INTO g_rule_table(l_rule_index).use_order_ps,
885 g_rule_table(l_rule_index).use_sub_ps,
886 g_rule_table(l_rule_index).use_customer_ps,
887 g_rule_table(l_rule_index).use_ship_to_ps,
888 g_rule_table(l_rule_index).use_carrier_ps,
889 g_rule_table(l_rule_index).use_ship_priority_ps,
890 g_rule_table(l_rule_index).use_trip_stop_ps,
891 g_rule_table(l_rule_index).use_delivery_ps,
892 g_rule_table(l_rule_index).use_item_ps,
893 g_rule_table(l_rule_index).use_locator_ps,
894 g_rule_table(l_rule_index).use_lot_ps,
895 g_rule_table(l_rule_index).use_revision_ps,
896 g_rule_table(l_rule_index).pick_method;
897 IF ps_rule%NOTFOUND THEN
898 x_error_message := 'Pick grouping rule '
899 || to_char(p_pick_grouping_rule_id) ||
900 ' does not exist';
901 x_api_status := FND_API.G_RET_STS_ERROR;
902 RETURN;
903 END IF;
904
905 g_rule_table(l_rule_index).grouping_rule_id := p_pick_grouping_rule_id;
906
907 IF (g_rule_table(l_rule_index).pick_method = '3') THEN -- Cluster Picking
908 /* Do not store the pick slip numbers generated for cluster picking
909 as we want to generate a new one for each line
910 */
911 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL
912 INTO x_pick_slip_number
913 FROM DUAL;
914 ELSE
915 -- Insert new key to table based on grouping rule
916
917 Create_Hash(p_rule_index => l_rule_index,
918 p_header_id => p_header_id,
919 p_customer_id => p_customer_id,
920 p_ship_method_code => p_ship_method_code,
921 p_ship_to_loc_id => p_ship_to_loc_id,
922 p_shipment_priority => p_shipment_priority,
923 p_subinventory => p_subinventory,
924 p_trip_stop_id => p_trip_stop_id,
925 p_delivery_id => p_delivery_id,
926 p_inventory_item_id => p_inventory_item_id,
927 p_locator_id => p_locator_id,
928 p_lot_number => p_lot_number,
929 p_revision => p_revision,
930 p_org_id => p_org_id,
931 x_hash_value => l_hash_value,
932 x_Insert_key_Rec => l_Insert_key_Rec,
933 x_error_message => x_error_message);
934
935
936 Insert_Key(l_hash_value => l_hash_value,
937 l_Insert_key_Rec => l_Insert_key_Rec,
938 x_pick_slip_number => x_pick_slip_number,
939 x_error_message => x_error_message);
940 END IF;
941 x_api_status := FND_API.G_RET_STS_SUCCESS;
942 RETURN;
943
944 END IF;
945
946 -- Comes here only if l_found TRUE
947 -- If grouping rule is stored, find stored pick slip number for rule
948 -- l_found := FALSE; -- No longer required
949
950 -- If key table is empty, there is no looping through table
951
952 Create_Hash(p_rule_index => l_rule_index,
953 p_header_id => p_header_id,
954 p_customer_id => p_customer_id,
955 p_ship_method_code => p_ship_method_code,
956 p_ship_to_loc_id => p_ship_to_loc_id,
957 p_shipment_priority => p_shipment_priority,
958 p_subinventory => p_subinventory,
959 p_trip_stop_id => p_trip_stop_id,
960 p_delivery_id => p_delivery_id,
961 p_inventory_item_id => p_inventory_item_id,
962 p_locator_id => p_locator_id,
963 p_lot_number => p_lot_number,
964 p_revision => p_revision,
965 p_org_id => p_org_id,
966 x_hash_value => l_hash_value,
967 x_Insert_key_Rec => l_Insert_key_Rec,
968 x_error_message => x_error_message);
969
970
971 IF g_pskey_table.exists(l_hash_value) THEN
972 x_pick_slip_number := g_pskey_table(l_hash_value).pick_slip_number;
973 g_pskey_table(l_hash_value).counter := g_pskey_table(l_hash_value).counter + 1;
974
975 -- Print is immediate so check if limit has been reached
976 IF (p_ps_mode = 'I' AND l_limit <> -1) THEN
977 IF (g_pskey_table(l_hash_value).counter >= l_limit) THEN
978 x_ready_to_print := FND_API.G_TRUE;
979 g_print_ps_table(g_print_ps_table.count + 1) := x_pick_slip_number;
980 g_pskey_table.delete(l_hash_value);
981 END IF;
982 END IF;
983 ELSE
984 -- Insert new key
985
986 Insert_Key(l_hash_value => l_hash_value,
987 l_Insert_key_Rec => l_Insert_key_Rec,
988 x_pick_slip_number => x_pick_slip_number,
989 x_error_message => x_error_message);
990 END IF;
991
992 x_api_status := FND_API.G_RET_STS_SUCCESS;
993 EXCEPTION
994 WHEN OTHERS THEN
995 x_error_message := 'Error occurred in WSH_PR_PICK_NUMBER.Get_Pick_Slip_Number';
996 x_api_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 END Get_Pick_Slip_Number;
998
999 --
1000 -- Name
1001 -- PROCEDURE DELETE_PS_TBL
1002 --
1003 -- Purpose
1004 -- Deletes the global PL/SQL table used to store pick slip numbers
1005 -- For Code levels after 11.5.9 , it will delete the global table from INV
1006 --
1007 -- Input Parameters
1008 -- None
1009 --
1010 -- Output Parameters
1011 -- None
1012 PROCEDURE DELETE_PS_TBL
1013 ( x_api_status OUT NOCOPY VARCHAR2,
1014 x_error_message OUT NOCOPY VARCHAR2 ) IS
1015 BEGIN
1016
1017 x_api_status := FND_API.G_RET_STS_SUCCESS;
1018 IF WSH_CODE_CONTROL.Get_Code_Release_Level >= '110509'
1019 THEN
1020 INV_PR_PICK_SLIP_NUMBER.g_pskey_table.delete ;
1021 ELSE
1022 g_pskey_table.delete ;
1023 END IF;
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 x_error_message := 'Error occurred in WSH_PR_PICK_NUMBER.DELETE_PS_TBL: ' || SQLERRM;
1028 x_api_status := FND_API.G_RET_STS_UNEXP_ERROR;
1029 END DELETE_PS_TBL ;
1030
1031 END WSH_PR_PICK_SLIP_NUMBER;
[Home] [Help]