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