[Home] [Help]
PACKAGE BODY: APPS.INV_PR_PICK_SLIP_NUMBER
Source
1 PACKAGE BODY inv_pr_pick_slip_number AS
2 /* $Header: INVPRPNB.pls 120.5 2006/10/31 19:45:07 stdavid ship $ */
3
4
5 --
6 -- PACKAGE TYPES
7 --
8 TYPE grprectyp IS RECORD(
9 grouping_rule_id NUMBER
10 , use_order_ps VARCHAR2(1) := 'N'
11 , use_customer_ps VARCHAR2(1) := 'N'
12 , use_ship_to_ps VARCHAR2(1) := 'N'
13 , use_carrier_ps VARCHAR2(1) := 'N'
14 , use_ship_priority_ps VARCHAR2(1) := 'N'
15 , use_trip_stop_ps VARCHAR2(1) := 'N'
16 , use_delivery_ps VARCHAR2(1) := 'N'
17 , use_src_sub_ps VARCHAR2(1) := 'N'
18 , use_src_locator_ps VARCHAR2(1) := 'N'
19 , use_item_ps VARCHAR2(1) := 'N'
20 , use_revision_ps VARCHAR2(1) := 'N'
21 , use_lot_ps VARCHAR2(1) := 'N'
22 , use_jobsch_ps VARCHAR2(1) := 'N'
23 , use_oper_seq_ps VARCHAR2(1) := 'N'
24 , use_dept_ps VARCHAR2(1) := 'N'
25 , use_supply_type_ps VARCHAR2(1) := 'N'
26 , use_supply_sub_ps VARCHAR2(1) := 'N'
27 , use_supply_loc_ps VARCHAR2(1) := 'N'
28 , use_project_ps VARCHAR2(1) := 'N'
29 , use_task_ps VARCHAR2(1) := 'N'
30 , pick_method VARCHAR2(30) := '-99');
31
32 TYPE grptabtyp IS TABLE OF grprectyp
33 INDEX BY BINARY_INTEGER;
34
35 TYPE wipkeyrectyp IS RECORD
36 ( grouping_rule_id NUMBER
37 , organization_id NUMBER
38 , wip_entity_id NUMBER
39 , rep_schedule_id NUMBER
40 , operation_seq_num NUMBER
41 , dept_id NUMBER
42 , push_or_pull VARCHAR2(4)
43 , supply_subinventory VARCHAR2(10)
44 , supply_locator_id NUMBER
45 , project_id NUMBER
46 , task_id NUMBER
47 , src_subinventory VARCHAR2(10)
48 , src_locator_id NUMBER
49 , inventory_item_id NUMBER
50 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
51 , lot_number VARCHAR2(80)
52 , revision VARCHAR2(3)
53 , pick_slip_number NUMBER
54 );
55
56 TYPE wipkeytabtyp IS TABLE OF wipkeyrectyp
57 INDEX BY BINARY_INTEGER;
58
59 --
60 -- PACKAGE VARIABLES
61 --
62 g_wip_pskey_table wipkeytabtyp;
63 g_rule_table grptabtyp;
64 g_hash_base NUMBER := 1;
65 g_hash_size NUMBER := POWER(2, 25);
66 g_pkg_name CONSTANT VARCHAR2(50) := 'INV_PR_PICK_SLIP_NUMBER';
67 g_trace_on NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),2);
68 -- For cahing the limit information for an org
69 g_prev_org_id NUMBER;
70 g_pickslip_limit NUMBER;
71
72 /***********************************************************************************************
73 * *
74 * Shipping Related Procedures *
75 * *
76 ***********************************************************************************************/
77
78 --
79 -- Name
80 -- PROCEDURE INSERT_KEY
81 --
82 -- Purpose
83 -- Insert new key to table and returns newly generated Pick Slip Number
84 -- This procedure is used by Shipping GET_PICK_SLIP_NUMBER Procedure.
85 --
86
87 PROCEDURE insert_key
88 ( l_hash_value IN NUMBER
89 , l_insert_key_rec IN keyrectyp
90 , x_pick_slip_number OUT NOCOPY NUMBER
91 , x_error_message OUT NOCOPY VARCHAR2
92 ) IS
93 BEGIN
94 SELECT wsh_pick_slip_numbers_s.NEXTVAL
95 INTO x_pick_slip_number
96 FROM DUAL;
97
98 g_pskey_table(l_hash_value) := l_insert_key_rec;
99 g_pskey_table(l_hash_value).counter := 1;
100 g_pskey_table(l_hash_value).pick_slip_number := x_pick_slip_number;
101 EXCEPTION
102 WHEN OTHERS THEN
103 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.INSERT_KEY';
104 IF g_trace_on = 1 THEN
105 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.INSERT_KEY',3);
106 END IF;
107 END insert_key;
108
109 --
110 -- Name
111 -- PROCEDURE CREATE_HASH
112 --
113 -- Purpose
114 -- Generate a hash value for the given values for the column strings.
115 -- This procedure is used by Shipping GET_PICK_SLIP_NUMBER Procedure.
116 --
117 -- Input Parameter
118 -- p_rule_index => Index to the Grouping Rule Table
119 -- p_header_id => Order Header ID
120 -- p_customer_id => Customer ID
121 -- p_ship_method_code => Ship Method
122 -- p_ship_to_loc_id => Ship to Location
123 -- p_shipment_priority => Shipment Priority
124 -- p_subinventory => SubInventory
125 -- p_trip_stop_id => Trip Stop
126 -- p_delivery_id => Delivery
127 -- p_inventory_item_id => Item
128 -- p_locator_id => Locator
129 -- p_lot_number => Lot Number
130 -- p_revision => Revision
131 -- p_org_id => Organization
132 /* FP-J PAR Replenishment Count:
133 Introduced four new DEFAULT NULL inputs dest_subinventory, dest_locator_id,
134 project_id, task_id to the signature of the procedure. */
135 -- p_dest_subinventory => Destination Subinventory
136 -- p_dest_locator_id => Destination Locator Id
137 -- p_project_id => Project Id
138 -- p_task_id => Task Id
139
140 --
141 -- Output Parameter
142 -- x_hash_value => Hash Value for g_pskey_table
143 -- x_Insert_key_Rec => keyRecTyp
144 -- x_error_message => Error message
145 --
146
147 PROCEDURE create_hash(
148 p_rule_index IN NUMBER
149 , p_header_id IN NUMBER
150 , p_customer_id IN NUMBER
151 , p_ship_method_code IN VARCHAR2
152 , p_ship_to_loc_id IN NUMBER
153 , p_shipment_priority IN VARCHAR2
154 , p_subinventory IN VARCHAR2
155 , p_trip_stop_id IN NUMBER
156 , p_delivery_id IN NUMBER
157 , p_inventory_item_id IN NUMBER
158 , p_locator_id IN NUMBER
159 , p_lot_number IN VARCHAR2
160 , p_revision IN VARCHAR2
161 , p_org_id IN NUMBER
162 , x_hash_value OUT NOCOPY NUMBER
163 , x_insert_key_rec OUT NOCOPY keyrectyp
164 , x_error_message OUT NOCOPY VARCHAR2
165 , p_dest_subinventory IN VARCHAR2 DEFAULT NULL
166 , p_dest_locator_id IN NUMBER DEFAULT NULL
167 , p_project_id IN NUMBER DEFAULT NULL
168 , p_task_id IN NUMBER DEFAULT NULL
169 ) IS
170 l_hash_string VARCHAR2(2000) := NULL;
171 BEGIN
172 l_hash_string := TO_CHAR(g_rule_table(p_rule_index).grouping_rule_id);
173 x_insert_key_rec.grouping_rule_id := g_rule_table(p_rule_index).grouping_rule_id;
174
175 IF (g_rule_table(p_rule_index).use_order_ps = 'Y') THEN
176 l_hash_string := l_hash_string || '-' || TO_CHAR(p_header_id);
177 x_insert_key_rec.header_id := p_header_id;
178 END IF;
179
180 IF (g_rule_table(p_rule_index).use_customer_ps = 'Y') THEN
181 l_hash_string := l_hash_string || '-' || TO_CHAR(p_customer_id);
182 x_insert_key_rec.customer_id := p_customer_id;
183 END IF;
184
185 IF (g_rule_table(p_rule_index).use_carrier_ps = 'Y') THEN
186 l_hash_string := l_hash_string || '-' || p_ship_method_code;
187 x_insert_key_rec.ship_method_code := p_ship_method_code;
188 END IF;
189
190 IF (g_rule_table(p_rule_index).use_ship_to_ps = 'Y') THEN
191 l_hash_string := l_hash_string || '-' || TO_CHAR(p_ship_to_loc_id);
192 x_insert_key_rec.ship_to_loc_id := p_ship_to_loc_id;
193 END IF;
194
195 IF (g_rule_table(p_rule_index).use_ship_priority_ps = 'Y') THEN
196 l_hash_string := l_hash_string || '-' || p_shipment_priority;
197 x_insert_key_rec.shipment_priority := p_shipment_priority;
198 END IF;
199
200 IF (g_rule_table(p_rule_index).use_trip_stop_ps = 'Y') THEN
201 l_hash_string := l_hash_string || '-' || TO_CHAR(p_trip_stop_id);
202 x_insert_key_rec.trip_stop_id := p_trip_stop_id;
203 END IF;
204
205 IF (g_rule_table(p_rule_index).use_delivery_ps = 'Y') THEN
206 l_hash_string := l_hash_string || '-' || TO_CHAR(p_delivery_id);
207 x_insert_key_rec.delivery_id := p_delivery_id;
208 END IF;
209
210 IF (g_rule_table(p_rule_index).use_src_sub_ps = 'Y') THEN
211 l_hash_string := l_hash_string || '-' || p_subinventory;
212 x_insert_key_rec.subinventory := p_subinventory;
213 END IF;
214
215 IF (g_rule_table(p_rule_index).use_src_locator_ps = 'Y') THEN
216 l_hash_string := l_hash_string || '-' || TO_CHAR(p_locator_id);
217 x_insert_key_rec.locator_id := p_locator_id;
218 END IF;
219
220 IF (g_rule_table(p_rule_index).use_item_ps = 'Y') THEN
221 l_hash_string := l_hash_string || '-' || TO_CHAR(p_inventory_item_id);
222 x_insert_key_rec.inventory_item_id := p_inventory_item_id;
223 END IF;
224
225 IF (g_rule_table(p_rule_index).use_lot_ps = 'Y') THEN
226 l_hash_string := l_hash_string || '-' || p_lot_number;
227 x_insert_key_rec.lot_number := p_lot_number;
228 END IF;
229
230 IF (g_rule_table(p_rule_index).use_revision_ps = 'Y') THEN
231 l_hash_string := l_hash_string || '-' || p_revision;
232 x_insert_key_rec.revision := p_revision;
233 END IF;
234
235 /* PAR Replenishment Count: It is now possible to define grouping rule
236 with Destination Sub, Destination Locator, Project and Task for Pick Wave also */
237 IF (g_rule_table(p_rule_index).use_supply_sub_ps = 'Y') THEN
238 l_hash_string := l_hash_string || '-' || p_dest_subinventory;
239 x_insert_key_rec.dest_subinventory := p_dest_subinventory;
240 END IF;
241
242 IF (g_rule_table(p_rule_index).use_supply_loc_ps = 'Y') THEN
243 l_hash_string := l_hash_string || '-' || TO_CHAR(p_dest_locator_id);
244 x_insert_key_rec.dest_locator_id := p_dest_locator_id;
245 END IF;
246
247 IF (g_rule_table(p_rule_index).use_project_ps = 'Y') THEN
248 l_hash_string := l_hash_string || '-' || TO_CHAR(p_project_id);
249 x_insert_key_rec.project_id := p_project_id;
250 END IF;
251
252 IF (g_rule_table(p_rule_index).use_task_ps = 'Y') THEN
253 l_hash_string := l_hash_string || '-' || TO_CHAR(p_task_id);
254 x_insert_key_rec.task_id := p_task_id;
255 END IF;
256
257
258 x_insert_key_rec.organization_id := p_org_id;
259 l_hash_string := l_hash_string || '-' || TO_CHAR(p_org_id);
260 x_hash_value := DBMS_UTILITY.get_hash_value(NAME => l_hash_string, base => g_hash_base, hash_size => g_hash_size);
261 EXCEPTION
262 WHEN OTHERS THEN
263 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.CREATE_HASH';
264 IF g_trace_on = 1 THEN
265 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.CREATE_HASH',3);
266 END IF;
267 END create_hash;
268
269 --
270 -- Name
271 -- PROCEDURE GET_PICK_SLIP_NUMBER (Used by Shipping)
272 --
273 -- Purpose
274 -- Returns Pick Slip Number and whether a Pick Slip should be printed. This
275 -- overloaded procedure is used by Shipping.
276 --
277 -- Input Parameters
278 -- p_ps_mode => Pick Slip Print Mode: I = Immediate, E = Deferred
279 -- p_pick_grouping_rule_id => Pick Grouping Rule ID
280 -- p_org_id => Organization ID
281 -- p_header_id => Order Header ID
282 -- p_customer_id => Customer ID
283 -- p_ship_method_code => Ship Method
284 -- p_ship_to_loc_id => Ship to Location
285 -- p_shipment_priority => Shipment Priority
286 -- p_subinventory => SubInventory
287 -- p_trip_stop_id => Trip Stop
288 -- p_delivery_id => Delivery
289 -- p_inventory_item_id => Inventory Item ID
290 -- p_locator_id => Locator ID
291 -- p_lot_number => Lot Number
292 -- p_revision => Revision
293 /* FP-J PAR Replenishment Count:
294 Introduced four new DEFAULT NULL inputs dest_subinventory, dest_locator_id,
295 project_id, task_id to the signature of the procedure. */
296 -- p_dest_subinventory => Destination Subinventory
297 -- p_dest_locator_id => Destination Locator Id
298 -- p_project_id => Project Id
299 -- p_task_id => Task Id
300
301 --
302 -- Output Parameters
303 -- x_pick_slip_number => Pick Slip Number
304 -- x_ready_to_print => FND_API.G_TRUE or FND_API.G_FALSE
305 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
306 -- FND_API.G_RET_STS_ERROR
307 -- x_error_message => Error message
308
309 PROCEDURE get_pick_slip_number(
310 p_ps_mode IN VARCHAR2
311 , p_pick_grouping_rule_id IN NUMBER
312 , p_org_id IN NUMBER
313 , p_header_id IN NUMBER
314 , p_customer_id IN NUMBER
315 , p_ship_method_code IN VARCHAR2
316 , p_ship_to_loc_id IN NUMBER
317 , p_shipment_priority IN VARCHAR2
318 , p_subinventory IN VARCHAR2
319 , p_trip_stop_id IN NUMBER
320 , p_delivery_id IN NUMBER
321 , p_inventory_item_id IN NUMBER
322 , p_locator_id IN NUMBER
323 , p_lot_number IN VARCHAR2
324 , p_revision IN VARCHAR2
325 , x_pick_slip_number OUT NOCOPY NUMBER
326 , x_ready_to_print OUT NOCOPY VARCHAR2
327 , x_call_mode OUT NOCOPY VARCHAR2
328 , x_api_status OUT NOCOPY VARCHAR2
329 , x_error_message OUT NOCOPY VARCHAR2
330 , p_dest_subinventory IN VARCHAR2
331 , p_dest_locator_id IN NUMBER
332 , p_project_id IN NUMBER
333 , p_task_id IN NUMBER
334 ) IS
335 -- cursor to get the pick slip grouping rule
336 /* FP-J PAR Replenishment Count:
337 Introduced 4 new columns fetch in the below cursor viz.,
338 dest_sub_flag, dest_loc_flag, project_flag, task_flag */
339 CURSOR ps_rule(v_pgr_id IN NUMBER) IS
340 SELECT NVL(order_number_flag, 'N')
341 , NVL(customer_flag, 'N')
342 , NVL(ship_to_flag, 'N')
343 , NVL(carrier_flag, 'N')
344 , NVL(shipment_priority_flag, 'N')
345 , NVL(trip_stop_flag, 'N')
346 , NVL(delivery_flag, 'N')
347 , NVL(subinventory_flag, 'N')
348 , NVL(locator_flag, 'N')
349 , NVL(dest_sub_flag, 'N')
350 , NVL(dest_loc_flag, 'N')
351 , NVL(project_flag, 'N')
352 , NVL(task_flag, 'N')
353 , NVL(item_flag, 'N')
354 , NVL(revision_flag, 'N')
355 , NVL(lot_flag, 'N')
356 , NVL(pick_method, '-99')
357 FROM wsh_pick_grouping_rules
358 WHERE pick_grouping_rule_id = v_pgr_id;
359
360 -- cursor to get number of times called before printer
361 CURSOR get_limit(v_org_id IN NUMBER) IS
362 SELECT NVL(pick_slip_lines, -1)
363 FROM wsh_shipping_parameters
364 WHERE organization_id = v_org_id;
365
366 l_limit NUMBER;
367 l_insert_key_rec keyrectyp;
368 l_hash_value NUMBER;
369 l_rule_index NUMBER;
370 l_found BOOLEAN;
371 BEGIN
372 IF (wsh_pick_list.g_batch_id IS NOT NULL) THEN
373 -- Needed for inventory to know whether this API is triggered Manually or thru Pick Release
374 x_call_mode := 'Y';
375 END IF;
376
377 /* Get the number of times called for a pick slip before
378 setting the ready to print flag to TRUE. If print is immediate,
379 pickslip limit is cached and fetched only if current org defers from the last org */
380
381 IF p_ps_mode = 'I' THEN
382 IF p_org_id = g_prev_org_id THEN
383 l_limit := g_pickslip_limit;
384 ELSE
385 OPEN get_limit(p_org_id);
386 FETCH get_limit INTO l_limit;
387
388 IF get_limit%NOTFOUND THEN
389 x_error_message := 'Organization ' || TO_CHAR(p_org_id) || ' does not exist. ';
390 x_api_status := fnd_api.g_ret_sts_error;
391 RETURN;
392 END IF;
393
394 g_prev_org_id := p_org_id;
395 g_pickslip_limit := l_limit;
396 END IF;
397 END IF;
398
399 -- Set ready to print flag to FALSE initially
400 x_ready_to_print := fnd_api.g_false;
401
402 -- Bug 2777688: Do not store the pick slip numbers generated when the l_limt value is 1
403 -- as we want to generate a new one for each line
404 -- Bug 5212435: Store the pick slip number even when limit is 1
405 IF (p_ps_mode = 'I' AND l_limit = 1) THEN
406 SELECT wsh_pick_slip_numbers_s.NEXTVAL
407 INTO x_pick_slip_number
408 FROM dual;
409 wsh_pr_pick_slip_number.g_print_ps_table(wsh_pr_pick_slip_number.g_print_ps_table.COUNT + 1) :=
410 x_pick_slip_number;
411 x_ready_to_print := FND_API.G_TRUE;
412 x_api_status := FND_API.G_RET_STS_SUCCESS;
413 RETURN;
414 END IF;
415
416 -- l_found is used to determine whether Grouping Rule exists in Rule Table.
417 l_found := FALSE;
418
419 IF g_rule_table.EXISTS(p_pick_grouping_rule_id) THEN
420 l_found := TRUE;
421 l_rule_index := p_pick_grouping_rule_id;
422 END IF;
423
424 IF ((l_found) AND (g_rule_table(l_rule_index).pick_method = g_cluster_pick_method)) THEN
425 /* Cluster Picking:
426 Do not store the pick slip numbers generated, as a new one is required for each line. */
427 SELECT wsh_pick_slip_numbers_s.NEXTVAL
428 INTO x_pick_slip_number
429 FROM DUAL;
430
431 x_api_status := fnd_api.g_ret_sts_success;
432 RETURN;
433 END IF;
434
435 -- Rule is not found. Fetch the attributes concerning the Pick Slip Grouping Rule
436 IF (NOT l_found) THEN
437 l_rule_index := p_pick_grouping_rule_id;
438 OPEN ps_rule(p_pick_grouping_rule_id);
439 /* FP-J PAR Replenishment Count: Introduced fetching 4 new columns
440 from cursor ps_rule into g_rule_table viz., use_supply_sub_ps,
441 use_supply_loc_ps, use_project_ps, use_task_ps. Note that supply_sub and supply_loc
442 denote Destination Subinv and Destination Locator in usage */
443 FETCH ps_rule INTO g_rule_table(l_rule_index).use_order_ps
444 , g_rule_table(l_rule_index).use_customer_ps
445 , g_rule_table(l_rule_index).use_ship_to_ps
446 , g_rule_table(l_rule_index).use_carrier_ps
447 , g_rule_table(l_rule_index).use_ship_priority_ps
448 , g_rule_table(l_rule_index).use_trip_stop_ps
449 , g_rule_table(l_rule_index).use_delivery_ps
450 , g_rule_table(l_rule_index).use_src_sub_ps
451 , g_rule_table(l_rule_index).use_src_locator_ps
452 , g_rule_table(l_rule_index).use_supply_sub_ps
453 , g_rule_table(l_rule_index).use_supply_loc_ps
454 , g_rule_table(l_rule_index).use_project_ps
455 , g_rule_table(l_rule_index).use_task_ps
456 , g_rule_table(l_rule_index).use_item_ps
457 , g_rule_table(l_rule_index).use_revision_ps
458 , g_rule_table(l_rule_index).use_lot_ps
459 , g_rule_table(l_rule_index).pick_method;
460
461 IF ps_rule%NOTFOUND THEN
462 x_error_message := 'Pick grouping rule ' || TO_CHAR(p_pick_grouping_rule_id) || ' does not exist';
463 x_api_status := fnd_api.g_ret_sts_error;
464 RETURN;
465 END IF;
466
467 g_rule_table(l_rule_index).grouping_rule_id := p_pick_grouping_rule_id;
468
469 -- Rule Table is cached with the Rule and the Attributes. Now PickSlip Number has to be Generated.
470
471 IF (g_rule_table(l_rule_index).pick_method = g_cluster_pick_method) THEN
472 /* Cluster Picking:
473 Do not store the pick slip numbers generated, as a new one is required for each line. */
474 SELECT wsh_pick_slip_numbers_s.NEXTVAL
475 INTO x_pick_slip_number
476 FROM DUAL;
477 ELSE
478 -- Generate a new PickSlip Number and Insert it for future use.
479 create_hash(
480 p_rule_index => l_rule_index
481 , p_header_id => p_header_id
482 , p_customer_id => p_customer_id
483 , p_ship_method_code => p_ship_method_code
484 , p_ship_to_loc_id => p_ship_to_loc_id
485 , p_shipment_priority => p_shipment_priority
486 , p_subinventory => p_subinventory
487 , p_trip_stop_id => p_trip_stop_id
488 , p_delivery_id => p_delivery_id
489 , p_inventory_item_id => p_inventory_item_id
490 , p_locator_id => p_locator_id
491 , p_lot_number => p_lot_number
492 , p_revision => p_revision
493 , p_org_id => p_org_id
494 , x_hash_value => l_hash_value
495 , x_insert_key_rec => l_insert_key_rec
496 , x_error_message => x_error_message
497 , p_dest_subinventory => p_dest_subinventory
498 , p_dest_locator_id => p_dest_locator_id
499 , p_project_id => p_project_id
500 , p_task_id => p_task_id
501 );
502 insert_key(
503 l_hash_value => l_hash_value
504 , l_insert_key_rec => l_insert_key_rec
505 , x_pick_slip_number => x_pick_slip_number
506 , x_error_message => x_error_message
507 );
508 END IF;
509
510 x_api_status := fnd_api.g_ret_sts_success;
511 RETURN;
512 END IF;
513
514 -- Comes here only if l_found is TRUE. (Grouping Rule is already cached)
515 -- (ie) Rule is Found. But Pick Slip Number may not yet be generated.
516
517 create_hash(
518 p_rule_index => l_rule_index
519 , p_header_id => p_header_id
520 , p_customer_id => p_customer_id
521 , p_ship_method_code => p_ship_method_code
522 , p_ship_to_loc_id => p_ship_to_loc_id
523 , p_shipment_priority => p_shipment_priority
524 , p_subinventory => p_subinventory
525 , p_trip_stop_id => p_trip_stop_id
526 , p_delivery_id => p_delivery_id
527 , p_inventory_item_id => p_inventory_item_id
528 , p_locator_id => p_locator_id
529 , p_lot_number => p_lot_number
530 , p_revision => p_revision
531 , p_org_id => p_org_id
532 , x_hash_value => l_hash_value
533 , x_insert_key_rec => l_insert_key_rec
534 , x_error_message => x_error_message
535 , p_dest_subinventory => p_dest_subinventory
536 , p_dest_locator_id => p_dest_locator_id
537 , p_project_id => p_project_id
538 , p_task_id => p_task_id
539 );
540
541 IF g_pskey_table.EXISTS(l_hash_value) THEN
542 -- Pick Slip Number already exists.
543 x_pick_slip_number := g_pskey_table(l_hash_value).pick_slip_number;
544 g_pskey_table(l_hash_value).counter := g_pskey_table(l_hash_value).counter + 1;
545
546 -- Print is immediate so check if limit has been reached
547 IF (p_ps_mode = 'I' AND l_limit <> -1) THEN
548 IF (g_pskey_table(l_hash_value).counter >= l_limit) THEN
549 x_ready_to_print := fnd_api.g_true;
550 wsh_pr_pick_slip_number.g_print_ps_table(wsh_pr_pick_slip_number.g_print_ps_table.COUNT + 1) :=
551 x_pick_slip_number;
552 g_pskey_table.DELETE(l_hash_value);
553 END IF;
554 END IF;
555 ELSE
556 -- Pick Slip Number doesnt exists. Insert a new one.
557 insert_key(
558 l_hash_value => l_hash_value
559 , l_insert_key_rec => l_insert_key_rec
560 , x_pick_slip_number => x_pick_slip_number
561 , x_error_message => x_error_message
562 );
563 END IF;
564
565 x_api_status := fnd_api.g_ret_sts_success;
566 EXCEPTION
567 WHEN OTHERS THEN
568 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.GET_PICK_SLIP_NUMBER';
569 IF g_trace_on = 1 THEN
570 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER',3);
571 END IF;
572 x_api_status := fnd_api.g_ret_sts_unexp_error;
573 END get_pick_slip_number;
574
575 /***********************************************************************************************
576 * *
577 * Component Picking Related Procedures *
578 * *
579 ***********************************************************************************************/
580
581 --
582 -- Name
583 -- PROCEDURE INSERT_KEY
584 --
585 -- Purpose
586 -- Insert new key to table and returns newly generated Pick Slip Number
587 -- This procedure is used by WIP GET_PICK_SLIP_NUMBER Procedure.
588 --
589
590 PROCEDURE insert_key
591 ( l_hash_value IN NUMBER
592 , l_insert_key_rec IN wipkeyrectyp
593 , x_pick_slip_number OUT NOCOPY NUMBER
594 , x_error_message OUT NOCOPY VARCHAR2
595 ) IS
596 BEGIN
597 SELECT wsh_pick_slip_numbers_s.NEXTVAL
598 INTO x_pick_slip_number
599 FROM DUAL;
600
601 g_wip_pskey_table(l_hash_value) := l_insert_key_rec;
602 g_wip_pskey_table(l_hash_value).pick_slip_number := x_pick_slip_number;
603 EXCEPTION
604 WHEN OTHERS THEN
605 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.INSERT_KEY';
606 IF g_trace_on = 1 THEN
607 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.INSERT_KEY',3);
608 END IF;
609 END insert_key;
610
611 --
612 -- Name
613 -- PROCEDURE CREATE_HASH
614 --
615 -- Purpose
616 -- Generate a hash value for the given values for the column strings.
617 -- This procedure is used by WIP GET_PICK_SLIP_NUMBER Procedure.
618 --
619 -- Input Parameter
620 -- p_rule_index => Index to the Grouping Rule Table
621 -- p_org_id => Organization ID
622 -- p_wip_entity_id => WIP Entity ID
623 -- p_rep_schedule_id => Repetitive Schedule ID
624 -- p_operation_seq_num => Operation Sequence Number
625 -- p_dept_id => Department ID
626 -- p_push_or_pull => Push or Pull
627 -- p_supply_subinventory => Supply SubInventory
628 -- p_supply_locator_id => Supply Locator ID
629 -- p_project_id => Project ID
630 -- p_task_id => Task ID
631 -- p_src_subinventory => Source SubInventory
632 -- p_src_locator_id => Source Locator ID
633 -- p_inventory_item_id => Inventory Item ID
634 -- p_revision => Revision
635 -- p_lot_number => Lot Number
636 -- p_dest_subinventory => Destination Subinventory
637 -- p_dest_locator_id => Destination Locator Id
638 -- p_project_id => Project Id
639 -- p_task_id => Task Id
640 --
641 -- Output Parameter
642 -- x_hash_value => Hash Value for g_wip_pskey_table
643 -- x_insert_key_rec => WIPKeyRecTyp
644 -- x_error_message => Error message
645 --
646
647 PROCEDURE create_hash(
648 p_rule_index IN NUMBER
649 , p_org_id IN NUMBER
650 , p_wip_entity_id IN NUMBER
651 , p_rep_schedule_id IN NUMBER
652 , p_operation_seq_num IN NUMBER
653 , p_dept_id IN NUMBER
654 , p_push_or_pull IN VARCHAR2
655 , p_supply_subinventory IN VARCHAR2
656 , p_supply_locator_id IN NUMBER
657 , p_project_id IN NUMBER
658 , p_task_id IN NUMBER
659 , p_src_subinventory IN VARCHAR2
660 , p_src_locator_id IN NUMBER
661 , p_inventory_item_id IN NUMBER
662 , p_revision IN VARCHAR2
663 , p_lot_number IN VARCHAR2
664 , x_hash_value OUT NOCOPY NUMBER
665 , x_insert_key_rec OUT NOCOPY wipkeyrectyp
666 , x_error_message OUT NOCOPY VARCHAR2
667 ) IS
668 l_hash_string VARCHAR2(2000) := NULL;
669 BEGIN
670 l_hash_string := TO_CHAR(g_rule_table(p_rule_index).grouping_rule_id);
671 x_insert_key_rec.grouping_rule_id := g_rule_table(p_rule_index).grouping_rule_id;
672
673 IF (g_rule_table(p_rule_index).use_jobsch_ps = 'Y') THEN
674 l_hash_string := l_hash_string || '-' || TO_CHAR(p_wip_entity_id) || '-' || TO_CHAR(p_rep_schedule_id);
675 x_insert_key_rec.wip_entity_id := p_wip_entity_id;
676 x_insert_key_rec.rep_schedule_id := p_rep_schedule_id;
677 END IF;
678
679 IF (g_rule_table(p_rule_index).use_oper_seq_ps = 'Y') THEN
680 l_hash_string := l_hash_string || '-' || TO_CHAR(p_operation_seq_num);
681 x_insert_key_rec.operation_seq_num := p_operation_seq_num;
682 END IF;
683
684 IF (g_rule_table(p_rule_index).use_dept_ps = 'Y') THEN
685 l_hash_string := l_hash_string || '-' || TO_CHAR(p_dept_id);
686 x_insert_key_rec.dept_id := p_dept_id;
687 END IF;
688
689 IF (g_rule_table(p_rule_index).use_supply_type_ps = 'Y') THEN
690 l_hash_string := l_hash_string || '-' || p_push_or_pull;
691 x_insert_key_rec.push_or_pull := p_push_or_pull;
692 END IF;
693
694 IF (g_rule_table(p_rule_index).use_supply_sub_ps = 'Y') THEN
695 l_hash_string := l_hash_string || '-' || p_supply_subinventory;
696 x_insert_key_rec.supply_subinventory := p_supply_subinventory;
697 END IF;
698
699 IF (g_rule_table(p_rule_index).use_supply_loc_ps = 'Y') THEN
700 l_hash_string := l_hash_string || '-' || TO_CHAR(p_supply_locator_id);
701 x_insert_key_rec.supply_locator_id := p_supply_locator_id;
702 END IF;
703
704 IF (g_rule_table(p_rule_index).use_project_ps = 'Y') THEN
705 l_hash_string := l_hash_string || '-' || TO_CHAR(p_project_id);
706 x_insert_key_rec.project_id := p_project_id;
707 END IF;
708
709 IF (g_rule_table(p_rule_index).use_task_ps = 'Y') THEN
710 l_hash_string := l_hash_string || '-' || TO_CHAR(p_task_id);
711 x_insert_key_rec.task_id := p_task_id;
712 END IF;
713
714 IF (g_rule_table(p_rule_index).use_src_sub_ps = 'Y') THEN
715 l_hash_string := l_hash_string || '-' || p_src_subinventory;
716 x_insert_key_rec.src_subinventory := p_src_subinventory;
717 END IF;
718
719 IF (g_rule_table(p_rule_index).use_src_locator_ps = 'Y') THEN
720 l_hash_string := l_hash_string || '-' || TO_CHAR(p_src_locator_id);
721 x_insert_key_rec.src_locator_id := p_src_locator_id;
722 END IF;
723
724 IF (g_rule_table(p_rule_index).use_item_ps = 'Y') THEN
725 l_hash_string := l_hash_string || '-' || TO_CHAR(p_inventory_item_id);
726 x_insert_key_rec.inventory_item_id := p_inventory_item_id;
727 END IF;
728
729 IF (g_rule_table(p_rule_index).use_lot_ps = 'Y') THEN
730 l_hash_string := l_hash_string || '-' || p_lot_number;
731 x_insert_key_rec.lot_number := p_lot_number;
732 END IF;
733
734 IF (g_rule_table(p_rule_index).use_revision_ps = 'Y') THEN
735 l_hash_string := l_hash_string || '-' || p_revision;
736 x_insert_key_rec.revision := p_revision;
737 END IF;
738
739 x_insert_key_rec.organization_id := p_org_id;
740 l_hash_string := l_hash_string || '-' || TO_CHAR(p_org_id);
741 x_hash_value := DBMS_UTILITY.get_hash_value(NAME => l_hash_string, base => g_hash_base, hash_size => g_hash_size);
742 EXCEPTION
743 WHEN OTHERS THEN
744 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.CREATE_HASH';
745 IF g_trace_on = 1 THEN
746 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.CREATE_HASH',3);
747 END IF;
748 END create_hash;
749
750 --
751 -- Name
752 -- PROCEDURE GET_PICK_SLIP_NUMBER (Used by Component Picking (WIP))
753 --
754 -- Purpose
755 -- Returns Pick Slip Number. This overloaded procedure is used for WIP.
756 --
757 -- Input Parameters
758 -- p_pick_grouping_rule_id => Pick Grouping Rule ID
759 -- p_org_id => Organization ID
760 -- p_wip_entity_id => WIP Entity ID
761 -- p_rep_schedule_id => Repetitive Schedule ID
762 -- p_operation_seq_num => Operation Sequence Number
763 -- p_dept_id => Department ID
764 -- p_push_or_pull => Push or Pull
765 -- p_supply_subinventory => Supply SubInventory
766 -- p_supply_locator_id => Supply Locator ID
767 -- p_project_id => Project ID
768 -- p_task_id => Task ID
769 -- p_src_subinventory => Source SubInventory
770 -- p_src_locator_id => Source Locator ID
771 -- p_inventory_item_id => Inventory Item ID
772 -- p_revision => Revision
773 -- p_lot_number => Lot Number
774 --
775 -- Output Parameters
776 -- x_pick_slip_number => Pick Slip Number
777 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
778 -- FND_API.G_RET_STS_ERROR
779 -- x_error_message => Error message
780 --
781 PROCEDURE get_pick_slip_number(
782 p_pick_grouping_rule_id IN NUMBER
783 , p_org_id IN NUMBER
784 , p_wip_entity_id IN NUMBER
785 , p_rep_schedule_id IN NUMBER
786 , p_operation_seq_num IN NUMBER
787 , p_dept_id IN NUMBER
788 , p_push_or_pull IN VARCHAR2
789 , p_supply_subinventory IN VARCHAR2
790 , p_supply_locator_id IN NUMBER
791 , p_project_id IN NUMBER
792 , p_task_id IN NUMBER
793 , p_src_subinventory IN VARCHAR2
794 , p_src_locator_id IN NUMBER
795 , p_inventory_item_id IN NUMBER
796 , p_revision IN VARCHAR2
797 , p_lot_number IN VARCHAR2
798 , x_pick_slip_number OUT NOCOPY NUMBER
799 , x_api_status OUT NOCOPY VARCHAR2
800 , x_error_message OUT NOCOPY VARCHAR2
801 ) IS
802 -- cursor to get the pick slip grouping rule
803 CURSOR ps_rule(v_pgr_id IN NUMBER) IS
804 SELECT NVL(job_schedule_flag, 'N')
805 , NVL(operation_flag, 'N')
806 , NVL(department_flag, 'N')
807 , NVL(push_vs_pull_flag, 'N')
808 , NVL(dest_sub_flag, 'N')
809 , NVL(dest_loc_flag, 'N')
810 , NVL(project_flag, 'N')
811 , NVL(task_flag, 'N')
812 , NVL(subinventory_flag, 'N')
813 , NVL(locator_flag, 'N')
814 , NVL(item_flag, 'N')
815 , NVL(revision_flag, 'N')
816 , NVL(lot_flag, 'N')
817 , NVL(pick_method, '-99')
818 FROM wsh_pick_grouping_rules
819 WHERE pick_grouping_rule_id = v_pgr_id;
820
821 l_insert_key_rec wipkeyrectyp;
822 l_hash_value NUMBER;
823 l_rule_index NUMBER;
824 l_found BOOLEAN;
825 BEGIN
826 -- l_found is used to determine whether Grouping Rule exists in Rule Table.
827 l_found := FALSE;
828
829 IF g_rule_table.EXISTS(p_pick_grouping_rule_id) THEN
830 l_found := TRUE;
831 l_rule_index := p_pick_grouping_rule_id;
832 END IF;
833
834 IF ((l_found) AND (g_rule_table(l_rule_index).pick_method = g_cluster_pick_method)) THEN
835 /* Cluster Picking:
836 Do not store the pick slip numbers generated, as a new one is required for each line. */
837 SELECT wsh_pick_slip_numbers_s.NEXTVAL
838 INTO x_pick_slip_number
839 FROM DUAL;
840
841 x_api_status := fnd_api.g_ret_sts_success;
842 RETURN;
843 END IF;
844
845 -- Rule is not found. Fetch the attributes concerning the Pick Slip Grouping Rule
846 IF (NOT l_found) THEN
847 l_rule_index := p_pick_grouping_rule_id;
848 OPEN ps_rule(p_pick_grouping_rule_id);
849 FETCH ps_rule INTO g_rule_table(l_rule_index).use_jobsch_ps
850 , g_rule_table(l_rule_index).use_oper_seq_ps
851 , g_rule_table(l_rule_index).use_dept_ps
852 , g_rule_table(l_rule_index).use_supply_type_ps
853 , g_rule_table(l_rule_index).use_supply_sub_ps
854 , g_rule_table(l_rule_index).use_supply_loc_ps
855 , g_rule_table(l_rule_index).use_project_ps
856 , g_rule_table(l_rule_index).use_task_ps
857 , g_rule_table(l_rule_index).use_src_sub_ps
858 , g_rule_table(l_rule_index).use_src_locator_ps
859 , g_rule_table(l_rule_index).use_item_ps
860 , g_rule_table(l_rule_index).use_revision_ps
861 , g_rule_table(l_rule_index).use_lot_ps
862 , g_rule_table(l_rule_index).pick_method;
863
864 IF ps_rule%NOTFOUND THEN
865 x_error_message := 'Pick grouping rule ' || TO_CHAR(p_pick_grouping_rule_id) || ' does not exist';
866 x_api_status := fnd_api.g_ret_sts_error;
867 RETURN;
868 END IF;
869
870 g_rule_table(l_rule_index).grouping_rule_id := p_pick_grouping_rule_id;
871
872 -- Rule Table is cached with the Rule and the Attributes. Now PickSlip Number has to be Generated.
873
874 IF (g_rule_table(l_rule_index).pick_method = g_cluster_pick_method) THEN
875 /* Cluster Picking:
876 Do not store the pick slip numbers generated, as a new one is required for each line. */
877 SELECT wsh_pick_slip_numbers_s.NEXTVAL
878 INTO x_pick_slip_number
879 FROM DUAL;
880 ELSE
881 -- Generate a new PickSlip Number and Insert it for future use.
882 create_hash(
883 p_rule_index => l_rule_index
884 , p_org_id => p_org_id
885 , p_wip_entity_id => p_wip_entity_id
886 , p_rep_schedule_id => p_rep_schedule_id
887 , p_operation_seq_num => p_operation_seq_num
888 , p_dept_id => p_dept_id
889 , p_push_or_pull => p_push_or_pull
890 , p_supply_subinventory => p_supply_subinventory
891 , p_supply_locator_id => p_supply_locator_id
892 , p_project_id => p_project_id
893 , p_task_id => p_task_id
894 , p_src_subinventory => p_src_subinventory
895 , p_src_locator_id => p_src_locator_id
896 , p_inventory_item_id => p_inventory_item_id
897 , p_revision => p_revision
898 , p_lot_number => p_lot_number
899 , x_hash_value => l_hash_value
900 , x_insert_key_rec => l_insert_key_rec
901 , x_error_message => x_error_message
902 );
903 insert_key(
904 l_hash_value => l_hash_value
905 , l_insert_key_rec => l_insert_key_rec
906 , x_pick_slip_number => x_pick_slip_number
907 , x_error_message => x_error_message
908 );
909 END IF;
910
911 x_api_status := fnd_api.g_ret_sts_success;
912 RETURN;
913 END IF;
914
915 -- Comes here only if l_found is TRUE. (Grouping Rule is already cached)
916 -- (ie) Rule is Found. But Pick Slip Number may not yet be generated.
917
918 create_hash(
919 p_rule_index => l_rule_index
920 , p_org_id => p_org_id
921 , p_wip_entity_id => p_wip_entity_id
922 , p_rep_schedule_id => p_rep_schedule_id
923 , p_operation_seq_num => p_operation_seq_num
924 , p_dept_id => p_dept_id
925 , p_push_or_pull => p_push_or_pull
926 , p_supply_subinventory => p_supply_subinventory
927 , p_supply_locator_id => p_supply_locator_id
928 , p_project_id => p_project_id
929 , p_task_id => p_task_id
930 , p_src_subinventory => p_src_subinventory
931 , p_src_locator_id => p_src_locator_id
932 , p_inventory_item_id => p_inventory_item_id
933 , p_revision => p_revision
934 , p_lot_number => p_lot_number
935 , x_hash_value => l_hash_value
936 , x_insert_key_rec => l_insert_key_rec
937 , x_error_message => x_error_message
938 );
939
940 IF g_wip_pskey_table.EXISTS(l_hash_value) THEN
941 -- Pick Slip Number already exists.
942 x_pick_slip_number := g_wip_pskey_table(l_hash_value).pick_slip_number;
943 ELSE
944 -- Pick Slip Number doesnt exists. Insert a new one.
945 insert_key(
946 l_hash_value => l_hash_value
947 , l_insert_key_rec => l_insert_key_rec
948 , x_pick_slip_number => x_pick_slip_number
949 , x_error_message => x_error_message
950 );
951 END IF;
952 x_api_status := fnd_api.g_ret_sts_success;
953 EXCEPTION
954 WHEN OTHERS THEN
955 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.GET_PICK_SLIP_NUMBER';
956 IF g_trace_on = 1 THEN
957 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER',3);
958 END IF;
959 x_api_status := fnd_api.g_ret_sts_unexp_error;
960 END get_pick_slip_number;
961
962 --
963 -- Name
964 -- PROCEDURE PRINT_PICK_SLIP (Used by Component Picking (WIP))
965 --
966 -- Purpose
967 -- Submits the Concurrent Request to print the Move Order Pick Slip Report.
968 --
969 -- Input Parameters
970 -- p_organization_id => Organization ID
971 -- p_mo_request_number => Move Order Request Number
972 --
973 -- Output Parameters
974 -- x_request_id => Concurrent Request ID
975 -- x_return_status => FND_API.G_RET_STS_SUCESSS or
976 -- FND_API.G_RET_STS_ERROR
977 -- x_msg_data => Error Messages
978 -- x_msg_count => Error Messages Count
979 FUNCTION print_pick_slip(
980 x_return_status OUT NOCOPY VARCHAR2
981 , x_msg_data OUT NOCOPY VARCHAR2
982 , x_msg_count OUT NOCOPY NUMBER
983 , p_organization_id NUMBER
984 , p_mo_request_number NUMBER
985 , p_plan_tasks BOOLEAN
986 ) RETURN NUMBER IS
987 l_request_id NUMBER;
988 l_plan_tasks VARCHAR2(1) := 'N';
989 BEGIN
990 IF p_plan_tasks THEN
991 l_plan_tasks := 'Y';
992 END IF;
993 l_request_id := inv_pick_slip_report.print_pick_slip(
994 p_organization_id => p_organization_id
995 , p_move_order_from => p_mo_request_number
996 , p_move_order_to => p_mo_request_number
997 , p_plan_tasks => l_plan_tasks
998 );
999
1000 IF l_request_id = 0 THEN
1001 x_return_status := fnd_api.g_ret_sts_error;
1002 fnd_msg_pub.count_and_get(p_encoded=>fnd_api.g_false,p_data=>x_msg_data, p_count=>x_msg_count);
1003 ELSE
1004 x_return_status := fnd_api.g_ret_sts_success;
1005 END IF;
1006
1007 RETURN l_request_id;
1008 END print_pick_slip;
1009
1010 --
1011 -- Name
1012 -- PROCEDURE DELETE_WIP_PS_TBL
1013 --
1014 -- Purpose
1015 -- Deletes the global PL/SQL table used to store pick slip numbers
1016 -- This is called at the end of component pick release
1017 --
1018 -- Input Parameters
1019 -- None
1020 --
1021 -- Output Parameters
1022 -- None
1023 PROCEDURE delete_wip_ps_tbl IS
1024 BEGIN
1025 g_wip_pskey_table.DELETE;
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.DELETE_WIP_PS_TBL', 3);
1029 END delete_wip_ps_tbl;
1030
1031 -- /* For Parallel Pick-Release */
1032 -- Name
1033 -- PROCEDURE CREATE_PICK_SLIP_STRING
1034 --
1035 -- Purpose
1036 -- Generate a hash value for the given values for the column strings.
1037 -- This procedure is used by Shipping GET_PICK_SLIP_NUMBER_PARALLEL Procedure.
1038 --
1039 -- Input Parameter
1040 -- p_rule_index => Index to the Grouping Rule Table
1041 -- p_header_id => Order Header ID
1042 -- p_customer_id => Customer ID
1043 -- p_ship_method_code => Ship Method
1044 -- p_ship_to_loc_id => Ship to Location
1045 -- p_shipment_priority => Shipment Priority
1046 -- p_subinventory => SubInventory
1047 -- p_trip_stop_id => Trip Stop
1048 -- p_delivery_id => Delivery
1049 -- p_inventory_item_id => Item
1050 -- p_locator_id => Locator
1051 -- p_lot_number => Lot Number
1052 -- p_revision => Revision
1053 -- p_org_id => Organization
1054 -- p_dest_subinventory => Destination Subinventory
1055 -- p_dest_locator_id => Destination Locator Id
1056 -- p_project_id => Project Id
1057 -- p_task_id => Task Id
1058
1059 --
1060 -- Output Parameter
1061 -- x_hash_string => Hash string to insert into mtl_pick_slip_numbers
1062 -- x_error_message => Error message
1063 --
1064
1065 PROCEDURE create_pick_slip_string(
1066 p_rule_index IN NUMBER
1067 , p_header_id IN NUMBER
1068 , p_customer_id IN NUMBER
1069 , p_ship_method_code IN VARCHAR2
1070 , p_ship_to_loc_id IN NUMBER
1071 , p_shipment_priority IN VARCHAR2
1072 , p_subinventory IN VARCHAR2
1073 , p_trip_stop_id IN NUMBER
1074 , p_delivery_id IN NUMBER
1075 , p_inventory_item_id IN NUMBER
1076 , p_locator_id IN NUMBER
1077 , p_lot_number IN VARCHAR2
1078 , p_revision IN VARCHAR2
1079 , p_org_id IN NUMBER
1080 , x_error_message OUT NOCOPY VARCHAR2
1081 , x_hash_string IN OUT NOCOPY VARCHAR2
1082 , p_dest_subinventory IN VARCHAR2 DEFAULT NULL
1083 , p_dest_locator_id IN NUMBER DEFAULT NULL
1084 , p_project_id IN NUMBER DEFAULT NULL
1085 , p_task_id IN NUMBER DEFAULT NULL
1086 ) IS
1087 l_batch_id NUMBER := WSH_PICK_LIST.G_BATCH_ID;
1088 l_hash_string VARCHAR2(2000);
1089 BEGIN
1090
1091 l_hash_string := TO_CHAR(l_batch_id) || '-' || TO_CHAR(g_rule_table(p_rule_index).grouping_rule_id);
1092
1093 IF (g_rule_table(p_rule_index).use_order_ps = 'Y') THEN
1094 l_hash_string := l_hash_string || '-' || TO_CHAR(p_header_id);
1095 END IF;
1096
1097 IF (g_rule_table(p_rule_index).use_customer_ps = 'Y') THEN
1098 l_hash_string := l_hash_string || '-' || TO_CHAR(p_customer_id);
1099 END IF;
1100
1101 IF (g_rule_table(p_rule_index).use_carrier_ps = 'Y') THEN
1102 l_hash_string := l_hash_string || '-' || p_ship_method_code;
1103 END IF;
1104
1105 IF (g_rule_table(p_rule_index).use_ship_to_ps = 'Y') THEN
1106 l_hash_string := l_hash_string || '-' || TO_CHAR(p_ship_to_loc_id);
1107 END IF;
1108
1109 IF (g_rule_table(p_rule_index).use_ship_priority_ps = 'Y') THEN
1110 l_hash_string := l_hash_string || '-' || p_shipment_priority;
1111 END IF;
1112
1113 IF (g_rule_table(p_rule_index).use_trip_stop_ps = 'Y') THEN
1114 l_hash_string := l_hash_string || '-' || TO_CHAR(p_trip_stop_id);
1115 END IF;
1116
1117 IF (g_rule_table(p_rule_index).use_delivery_ps = 'Y') THEN
1118 l_hash_string := l_hash_string || '-' || TO_CHAR(p_delivery_id);
1119 END IF;
1120
1121 IF (g_rule_table(p_rule_index).use_src_sub_ps = 'Y') THEN
1122 l_hash_string := l_hash_string || '-' || p_subinventory;
1123 END IF;
1124
1125 IF (g_rule_table(p_rule_index).use_src_locator_ps = 'Y') THEN
1126 l_hash_string := l_hash_string || '-' || TO_CHAR(p_locator_id);
1127 END IF;
1128
1129 IF (g_rule_table(p_rule_index).use_item_ps = 'Y') THEN
1130 l_hash_string := l_hash_string || '-' || TO_CHAR(p_inventory_item_id);
1131 END IF;
1132
1133 IF (g_rule_table(p_rule_index).use_lot_ps = 'Y') THEN
1134 l_hash_string := l_hash_string || '-' || p_lot_number;
1135 END IF;
1136
1137 IF (g_rule_table(p_rule_index).use_revision_ps = 'Y') THEN
1138 l_hash_string := l_hash_string || '-' || p_revision;
1139 END IF;
1140
1141 IF (g_rule_table(p_rule_index).use_supply_sub_ps = 'Y') THEN
1142 l_hash_string := l_hash_string || '-' || p_dest_subinventory;
1143 END IF;
1144
1145 IF (g_rule_table(p_rule_index).use_supply_loc_ps = 'Y') THEN
1146 l_hash_string := l_hash_string || '-' || TO_CHAR(p_dest_locator_id);
1147 END IF;
1148
1149 IF (g_rule_table(p_rule_index).use_project_ps = 'Y') THEN
1150 l_hash_string := l_hash_string || '-' || TO_CHAR(p_project_id);
1151 END IF;
1152
1153 IF (g_rule_table(p_rule_index).use_task_ps = 'Y') THEN
1154 l_hash_string := l_hash_string || '-' || TO_CHAR(p_task_id);
1155 END IF;
1156
1157 l_hash_string := l_hash_string || '-' || TO_CHAR(p_org_id);
1158 x_hash_string := l_hash_string;
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.CREATE_PICK_SLIP_STRING';
1163 IF g_trace_on = 1 THEN
1164 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.CREATE_PICK_SLIP_STRING',3);
1165 END IF;
1166 END create_pick_slip_string;
1167
1168 -- /* For Parallel Pick-Release */
1169 -- Name
1170 -- PROCEDURE GEN_PARALLEL_PICK_SLIP_NUMBER
1171 --
1172 -- Purpose
1173 -- Insert a new row with the new pick_slip_number generated or
1174 -- update the table record for count. Table: MTL_PICK_SLIP_NUMBERS
1175 -- This procedure is used by Shipping GET_PICK_SLIP_NUMBER_PARALLEL Procedure.
1176 --
1177
1178 PROCEDURE gen_parallel_pick_slip_number
1179 ( p_hash_string IN VARCHAR2
1180 , p_limit IN NUMBER
1181 , x_pick_slip_number OUT NOCOPY NUMBER
1182 , x_error_message OUT NOCOPY VARCHAR2
1183 , x_api_status OUT NOCOPY VARCHAR2
1184 , x_pick_slip_status OUT NOCOPY NUMBER
1185 ) IS
1186
1187 PRAGMA AUTONOMOUS_TRANSACTION;
1188
1189 l_debug NUMBER;
1190 l_batch_id NUMBER := WSH_PICK_LIST.G_BATCH_ID;
1191 l_pick_slip_status NUMBER;
1192 l_pick_slip_count NUMBER;
1193 l_pick_slip_number NUMBER;
1194 l_num_attempts NUMBER;
1195 l_max_attempts NUMBER;
1196 l_success BOOLEAN;
1197
1198 unique_constraint_exc EXCEPTION;
1199 PRAGMA EXCEPTION_INIT (unique_constraint_exc, -1);
1200 wait_timeout_exc EXCEPTION;
1201 PRAGMA EXCEPTION_INIT (wait_timeout_exc, -30006);
1202
1203 BEGIN
1204 x_api_status := fnd_api.g_ret_sts_success;
1205 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1206
1207 IF (l_debug = 1) THEN
1208 inv_log_util.trace('p_hash_string = ' || p_hash_string,
1209 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1210 inv_log_util.trace('p_limit = ' || p_limit,
1211 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1212 END IF;
1213
1214 l_success := FALSE;
1215 l_num_attempts := 1;
1216 l_max_attempts := 3;
1217 l_pick_slip_status := 1;
1218 l_pick_slip_count := 0;
1219 l_pick_slip_number := NULL;
1220
1221 -- Make "l_max_attempts" iterations to either INSERT or lock a record
1222 -- in MTL_PICK_SLIP_NUMBERS
1223 WHILE (l_num_attempts <= l_max_attempts AND (NOT l_success))
1224 LOOP
1225 -- {
1226 -- begin
1227 -- try insert
1228 -- exception -00001 (unique constraint violated)
1229 -- begin
1230 -- select for update, wait 5 seconds
1231 -- exception -30006 (timeout)
1232 -- retry insert
1233 -- exception no_data_found (pick slip STATUS updated to 2 by locking process)
1234 -- retry insert
1235 -- exception others
1236 -- exit loop
1237 -- exception others
1238 -- exit loop
1239 IF (l_debug = 1) THEN
1240 inv_log_util.trace('l_num_attempts = ' || l_num_attempts,
1241 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1242 END IF;
1243 BEGIN
1244 INSERT INTO mtl_pick_slip_numbers
1245 ( id
1246 , pick_slip_batch_id
1247 , pick_slip_count
1248 , pick_slip_identifier
1249 , pick_slip_number
1250 , status
1251 ) VALUES ( mtl_pick_slip_numbers_s.nextval
1252 , l_batch_id
1253 , l_pick_slip_count
1254 , p_hash_string
1255 , wsh_pick_slip_numbers_s.nextval
1256 , l_pick_slip_status
1257 )
1258 RETURNING pick_slip_number
1259 INTO l_pick_slip_number;
1260
1261 IF (l_debug = 1) THEN
1262 inv_log_util.trace('Inserted pick slip # ' || l_pick_slip_number,
1263 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1264 END IF;
1265 l_success := TRUE;
1266 EXCEPTION
1267 -- {
1268 WHEN unique_constraint_exc THEN
1269 IF (l_debug = 1) THEN
1270 inv_log_util.trace('INSERT failed, row already exists',
1271 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1272 END IF;
1273 BEGIN
1274 SELECT pick_slip_number
1275 , pick_slip_count
1276 INTO l_pick_slip_number
1277 , l_pick_slip_count
1278 FROM mtl_pick_slip_numbers
1279 WHERE pick_slip_identifier = p_hash_string
1280 AND status = 1
1281 FOR UPDATE WAIT 5;
1282
1283 l_success := TRUE;
1284
1285 IF (l_debug = 1) THEN
1286 inv_log_util.trace('Locked row, pick slip # is ' || l_pick_slip_number
1287 || ', pick slip count is ' || l_pick_slip_count,
1288 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1289 END IF;
1290 EXCEPTION
1291 WHEN wait_timeout_exc THEN
1292 IF (l_debug = 1) THEN
1293 inv_log_util.trace('Timeout waiting for lock',
1294 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1295 END IF;
1296 l_success := FALSE;
1297 WHEN NO_DATA_FOUND THEN
1298 IF (l_debug = 1) THEN
1299 inv_log_util.trace('No data found, so retrying INSERT',
1300 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1301 END IF;
1302 l_success := FALSE;
1303 WHEN OTHERS THEN
1304 IF (l_debug = 1) THEN
1305 inv_log_util.trace('Other exception: ' || sqlerrm,
1306 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1307 END IF;
1308 l_success := FALSE;
1309 EXIT;
1310 END;
1311 WHEN OTHERS THEN
1312 IF (l_debug = 1) THEN
1313 inv_log_util.trace('Other exception: ' || sqlerrm,
1314 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1315 END IF;
1316 l_success := FALSE;
1317 EXIT;
1318 -- }
1319 END;
1320 l_num_attempts := l_num_attempts + 1;
1321 -- }
1322 END LOOP;
1323
1324 IF (NOT l_success) AND l_pick_slip_number IS NULL THEN
1325 SELECT wsh_pick_slip_numbers_s.nextval
1326 INTO l_pick_slip_number
1327 FROM dual;
1328 l_pick_slip_status := 2;
1329 IF (l_debug = 1) THEN
1330 inv_log_util.trace('Failed to INSERT or LOCK pick slip record. Returning next value: '
1331 || l_pick_slip_number,
1332 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1333 END IF;
1334 ELSE
1335 IF (l_pick_slip_count + 1) >= p_limit THEN
1336 l_pick_slip_status := 2;
1337 END IF;
1338 UPDATE mtl_pick_slip_numbers
1339 SET pick_slip_count = pick_slip_count + 1
1340 , status = l_pick_slip_status
1341 WHERE pick_slip_identifier = p_hash_string
1342 AND status = 1
1343 RETURNING pick_slip_count INTO l_pick_slip_count;
1344 IF (l_debug = 1) AND SQL%FOUND THEN
1345 inv_log_util.trace('Updated count to ' || l_pick_slip_count ||
1346 ', status to ' || l_pick_slip_status,
1347 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
1348 END IF;
1349 END IF;
1350
1351 COMMIT;
1352
1353 x_pick_slip_number := l_pick_slip_number;
1354 x_pick_slip_status := l_pick_slip_status;
1355
1356 EXCEPTION
1357 WHEN OTHERS THEN
1358 x_error_message := 'Error occurred in GEN_PARALLEL_PICK_SLIP_NUMBER';
1359 x_api_status := fnd_api.g_ret_sts_unexp_error;
1360 IF (l_debug = 1) THEN
1361 inv_log_util.trace('Exception: ' || SQLERRM,
1362 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER',3);
1363 END IF;
1364 END gen_parallel_pick_slip_number;
1365
1366
1367 -- /* For Parallel Pick-Release */
1368 -- Name
1369 -- PROCEDURE GET_PICK_SLIP_NUMBER_PARALLEL (Used by Shipping)
1370 --
1371 -- Purpose
1372 -- Returns Pick Slip Number and whether a Pick Slip should be printed. This
1373 -- overloaded procedure is used by Shipping for Parallel Pick Release.
1374 --
1375 -- Input Parameters
1376 -- p_ps_mode => Pick Slip Print Mode: I = Immediate, E = Deferred
1377 -- p_pick_grouping_rule_id => Pick Grouping Rule ID
1378 -- p_org_id => Organization ID
1379 -- p_header_id => Order Header ID
1380 -- p_customer_id => Customer ID
1381 -- p_ship_method_code => Ship Method
1382 -- p_ship_to_loc_id => Ship to Location
1383 -- p_shipment_priority => Shipment Priority
1384 -- p_subinventory => SubInventory
1385 -- p_trip_stop_id => Trip Stop
1386 -- p_delivery_id => Delivery
1387 -- p_inventory_item_id => Inventory Item ID
1388 -- p_locator_id => Locator ID
1389 -- p_lot_number => Lot Number
1390 -- p_revision => Revision
1391 -- p_dest_subinventory => Destination Subinventory
1392 -- p_dest_locator_id => Destination Locator Id
1393 -- p_project_id => Project Id
1394 -- p_task_id => Task Id
1395
1396 --
1397 -- Output Parameters
1398 -- x_pick_slip_number => Pick Slip Number
1399 -- x_ready_to_print => FND_API.G_TRUE or FND_API.G_FALSE
1400 -- x_api_status => FND_API.G_RET_STS_SUCESSS or
1401 -- FND_API.G_RET_STS_ERROR
1402 -- x_error_message => Error message
1403
1404 PROCEDURE get_pick_slip_number_parallel(
1405 p_ps_mode IN VARCHAR2
1406 , p_pick_grouping_rule_id IN NUMBER
1407 , p_org_id IN NUMBER
1408 , p_header_id IN NUMBER
1409 , p_customer_id IN NUMBER
1410 , p_ship_method_code IN VARCHAR2
1411 , p_ship_to_loc_id IN NUMBER
1412 , p_shipment_priority IN VARCHAR2
1413 , p_subinventory IN VARCHAR2
1414 , p_trip_stop_id IN NUMBER
1415 , p_delivery_id IN NUMBER
1416 , p_inventory_item_id IN NUMBER
1417 , p_locator_id IN NUMBER
1418 , p_lot_number IN VARCHAR2
1419 , p_revision IN VARCHAR2
1420 , x_pick_slip_number OUT NOCOPY NUMBER
1421 , x_ready_to_print OUT NOCOPY VARCHAR2
1422 , x_call_mode OUT NOCOPY VARCHAR2
1423 , x_api_status OUT NOCOPY VARCHAR2
1424 , x_error_message OUT NOCOPY VARCHAR2
1425 , p_dest_subinventory IN VARCHAR2
1426 , p_dest_locator_id IN NUMBER
1427 , p_project_id IN NUMBER
1428 , p_task_id IN NUMBER
1429 ) IS
1430 -- cursor to get the pick slip grouping rule
1431 CURSOR ps_rule(v_pgr_id IN NUMBER) IS
1432 SELECT NVL(order_number_flag, 'N')
1433 , NVL(customer_flag, 'N')
1434 , NVL(ship_to_flag, 'N')
1435 , NVL(carrier_flag, 'N')
1436 , NVL(shipment_priority_flag, 'N')
1437 , NVL(trip_stop_flag, 'N')
1438 , NVL(delivery_flag, 'N')
1439 , NVL(subinventory_flag, 'N')
1440 , NVL(locator_flag, 'N')
1441 , NVL(dest_sub_flag, 'N')
1442 , NVL(dest_loc_flag, 'N')
1443 , NVL(project_flag, 'N')
1444 , NVL(task_flag, 'N')
1445 , NVL(item_flag, 'N')
1446 , NVL(revision_flag, 'N')
1447 , NVL(lot_flag, 'N')
1448 , NVL(pick_method, '-99')
1449 FROM wsh_pick_grouping_rules
1450 WHERE pick_grouping_rule_id = v_pgr_id;
1451 -- cursor to get number of times called before printer
1452 CURSOR get_limit(v_org_id IN NUMBER) IS
1453 SELECT NVL(pick_slip_lines, -1)
1454 FROM wsh_shipping_parameters
1455 WHERE organization_id = v_org_id;
1456
1457 l_limit NUMBER;
1458 l_insert_key_rec keyrectyp;
1459 l_hash_string VARCHAR2(2000) := NULL;
1460 l_rule_index NUMBER;
1461 l_found BOOLEAN;
1462 l_pick_slip_status NUMBER;
1463 BEGIN
1464 IF (wsh_pick_list.g_batch_id IS NOT NULL) THEN
1465 -- Needed for inventory to know whether this API is triggered Manually or thru Pick Release
1466 x_call_mode := 'Y';
1467 END IF;
1468
1469 IF p_ps_mode = 'I' THEN
1470 IF p_org_id = g_prev_org_id THEN
1471 l_limit := g_pickslip_limit;
1472 ELSE
1473 OPEN get_limit(p_org_id);
1474 FETCH get_limit INTO l_limit;
1475
1476 IF get_limit%NOTFOUND THEN
1477 x_error_message := 'Organization ' || TO_CHAR(p_org_id) || ' does not exist. ';
1478 x_api_status := fnd_api.g_ret_sts_error;
1479 RETURN;
1480 END IF;
1481
1482 g_prev_org_id := p_org_id;
1483 g_pickslip_limit := l_limit;
1484 END IF;
1485 END IF;
1486
1487 -- Set ready to print flag to FALSE initially
1488 x_ready_to_print := fnd_api.g_false;
1489
1490 IF (p_ps_mode = 'I' AND l_limit = 1) THEN
1491 SELECT wsh_pick_slip_numbers_s.NEXTVAL
1492 INTO x_pick_slip_number
1493 FROM dual;
1494 wsh_pr_pick_slip_number.g_print_ps_table(wsh_pr_pick_slip_number.g_print_ps_table.COUNT + 1) :=
1495 x_pick_slip_number;
1496 x_ready_to_print := FND_API.G_TRUE;
1497 x_api_status := FND_API.G_RET_STS_SUCCESS;
1498 RETURN;
1499 END IF;
1500
1501 l_found := FALSE;
1502
1503 IF g_rule_table.EXISTS(p_pick_grouping_rule_id) THEN
1504 l_found := TRUE;
1505 l_rule_index := p_pick_grouping_rule_id;
1506 END IF;
1507
1508 IF (NOT l_found) THEN
1509 l_rule_index := p_pick_grouping_rule_id;
1510 OPEN ps_rule(p_pick_grouping_rule_id);
1511 FETCH ps_rule INTO g_rule_table(l_rule_index).use_order_ps
1512 , g_rule_table(l_rule_index).use_customer_ps
1513 , g_rule_table(l_rule_index).use_ship_to_ps
1514 , g_rule_table(l_rule_index).use_carrier_ps
1515 , g_rule_table(l_rule_index).use_ship_priority_ps
1516 , g_rule_table(l_rule_index).use_trip_stop_ps
1517 , g_rule_table(l_rule_index).use_delivery_ps
1518 , g_rule_table(l_rule_index).use_src_sub_ps
1519 , g_rule_table(l_rule_index).use_src_locator_ps
1520 , g_rule_table(l_rule_index).use_supply_sub_ps
1521 , g_rule_table(l_rule_index).use_supply_loc_ps
1522 , g_rule_table(l_rule_index).use_project_ps
1523 , g_rule_table(l_rule_index).use_task_ps
1524 , g_rule_table(l_rule_index).use_item_ps
1525 , g_rule_table(l_rule_index).use_revision_ps
1526 , g_rule_table(l_rule_index).use_lot_ps
1527 , g_rule_table(l_rule_index).pick_method;
1528
1529 IF ps_rule%NOTFOUND THEN
1530 x_error_message := 'Pick grouping rule ' || TO_CHAR(p_pick_grouping_rule_id) || ' does not exist';
1531 x_api_status := fnd_api.g_ret_sts_error;
1532 RETURN;
1533 END IF;
1534
1535 g_rule_table(l_rule_index).grouping_rule_id := p_pick_grouping_rule_id;
1536 END IF;
1537
1538 IF (g_rule_table(l_rule_index).pick_method = g_cluster_pick_method) THEN
1539 SELECT wsh_pick_slip_numbers_s.NEXTVAL
1540 INTO x_pick_slip_number
1541 FROM DUAL;
1542
1543 x_api_status := fnd_api.g_ret_sts_success;
1544 RETURN;
1545 END IF;
1546
1547 create_pick_slip_string(
1548 p_rule_index => l_rule_index
1549 , p_header_id => p_header_id
1550 , p_customer_id => p_customer_id
1551 , p_ship_method_code => p_ship_method_code
1552 , p_ship_to_loc_id => p_ship_to_loc_id
1553 , p_shipment_priority => p_shipment_priority
1554 , p_subinventory => p_subinventory
1555 , p_trip_stop_id => p_trip_stop_id
1556 , p_delivery_id => p_delivery_id
1557 , p_inventory_item_id => p_inventory_item_id
1558 , p_locator_id => p_locator_id
1559 , p_lot_number => p_lot_number
1560 , p_revision => p_revision
1561 , p_org_id => p_org_id
1562 , x_error_message => x_error_message
1563 , x_hash_string => l_hash_string
1564 , p_dest_subinventory => p_dest_subinventory
1565 , p_dest_locator_id => p_dest_locator_id
1566 , p_project_id => p_project_id
1567 , p_task_id => p_task_id
1568 );
1569
1570
1571 gen_parallel_pick_slip_number( p_hash_string => l_hash_string
1572 ,p_limit => l_limit
1573 ,x_pick_slip_number => x_pick_slip_number
1574 ,x_error_message => x_error_message
1575 ,x_api_status => x_api_status
1576 ,x_pick_slip_status => l_pick_slip_status);
1577
1578 IF (x_api_status <> fnd_api.g_ret_sts_success) THEN
1579 x_error_message := 'Error occurred in INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER';
1580 RETURN;
1581 END IF;
1582
1583 IF (p_ps_mode = 'I' AND l_limit <> -1) THEN
1584 IF l_pick_slip_status = 2 THEN
1585 x_ready_to_print := fnd_api.g_true;
1586 wsh_pr_pick_slip_number.g_print_ps_table(wsh_pr_pick_slip_number.g_print_ps_table.COUNT + 1) :=
1587 x_pick_slip_number;
1588 --<< check if deletion from the table is required, if so we can delete instead of update >>
1589 END IF;
1590 END IF;
1591
1592 x_api_status := fnd_api.g_ret_sts_success;
1593 EXCEPTION
1594 WHEN OTHERS THEN
1595 x_error_message := 'Error occurred in INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER';
1596 IF g_trace_on = 1 THEN
1597 inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER',3);
1598 END IF;
1599 x_api_status := fnd_api.g_ret_sts_unexp_error;
1600
1601 END get_pick_slip_number_parallel;
1602
1603 END inv_pr_pick_slip_number;