[Home] [Help]
PACKAGE BODY: APPS.WSH_PR_PICKING_OBJECTS
Source
1 PACKAGE BODY WSH_PR_PICKING_OBJECTS AS
2 /* $Header: WSHPRPOB.pls 115.9 99/07/16 08:19:59 porting ship $ */
3
4 --
5 -- Package
6 -- WSH_PR_PICKING_OBJECTS
7 --
8 -- Purpose
9 -- This package does the following:
10 -- - Determines whether a new picking header or pick slip number
11 -- is to be created.
12 -- - Inserts a new picking header if necessary
13 -- - Inserts picking lines
14 -- - Inserts picking line details
15 --
16
17 --
18 -- PACKAGE CONSTANTS
19 --
20
21 SUCCESS CONSTANT BINARY_INTEGER := 0;
22 FAILURE CONSTANT BINARY_INTEGER := -1;
23
24 RESOFF CONSTANT BINARY_INTEGER := 1;
25 NONTRANS CONSTANT BINARY_INTEGER := 2;
26 RESERVABLE CONSTANT BINARY_INTEGER := 3;
27 TRANSNONRES CONSTANT BINARY_INTEGER := 4;
28 BACKORDER CONSTANT BINARY_INTEGER := 5;
29
30 UNRELEASED_LINE CONSTANT BINARY_INTEGER := 1;
31 BACKORDER_LINE CONSTANT BINARY_INTEGER := 2;
32
33 --
34 -- PACKAGE VARIABLES
35 --
36
37 initialized BOOLEAN := FALSE;
38
39 --
40 -- PUBLIC FUNCTIONS/PROCEDURES
41 --
42
43 --
44 -- FORWARD DECLERATIONS
45 --
46 FUNCTION Insert_Picking_Line (
47 p_picking_line_id IN BINARY_INTEGER,
48 p_component_code IN VARCHAR2,
49 p_component_ratio IN BINARY_INTEGER,
50 p_component_sequence_id IN BINARY_INTEGER,
51 p_config_item_flag IN VARCHAR2,
52 p_date_requested IN DATE,
53 p_demand_class IN VARCHAR2,
54 p_included_item_flag IN VARCHAR2,
55 p_inventory_item_id IN BINARY_INTEGER,
56 p_customer_item_id IN BINARY_INTEGER,
57 p_original_line_detail_id IN BINARY_INTEGER,
58 p_order_line_id IN BINARY_INTEGER,
59 p_original_requested_quantity IN BINARY_INTEGER,
60 p_pick_header_id IN BINARY_INTEGER,
61 p_requested_quantity IN BINARY_INTEGER,
62 p_schedule_date IN DATE,
63 p_sequence_number IN BINARY_INTEGER,
64 p_shipment_priority_code IN VARCHAR2,
65 p_ship_method_code IN VARCHAR2,
66 p_ship_to_contact_id IN BINARY_INTEGER,
67 p_ship_to_site_use_id IN BINARY_INTEGER,
68 p_unit_code IN VARCHAR2,
69 p_warehouse_id IN BINARY_INTEGER,
70 p_org_id IN BINARY_INTEGER
71 ) RETURN BINARY_INTEGER;
72
73 FUNCTION Insert_Picking_Line_Details (
74 p_mode IN BINARY_INTEGER,
75 p_ps_number IN BINARY_INTEGER,
76 p_departure_id IN BINARY_INTEGER,
77 p_delivery_id IN OUT BINARY_INTEGER,
78 p_dep_plan_required_flag IN VARCHAR2,
79 p_autoscheduled_flag IN VARCHAR2,
80 p_customer_requested_lot_flag IN VARCHAR2,
81 p_ccid IN BINARY_INTEGER,
82 p_order_line_id IN BINARY_INTEGER,
83 p_master_container_item_id IN BINARY_INTEGER,
84 p_detail_container_item_id IN BINARY_INTEGER,
85 p_inventory_item_id IN BINARY_INTEGER,
86 p_load_seq_number IN BINARY_INTEGER,
87 p_autodetail_group_id IN BINARY_INTEGER,
88 p_delivery IN BINARY_INTEGER,
89 p_demand_class IN VARCHAR2,
90 p_picking_line_id IN BINARY_INTEGER,
91 p_requested_quantity IN BINARY_INTEGER,
92 p_reservable_flag IN VARCHAR2,
93 p_schedule_date IN DATE,
94 p_schedule_level IN VARCHAR2,
95 p_schedule_status_code IN VARCHAR2,
96 p_subinventory IN VARCHAR2,
97 p_transactable_flag IN VARCHAR2,
98 p_released_flag IN VARCHAR2,
99 p_warehouse_id IN BINARY_INTEGER
100 ) RETURN BINARY_INTEGER;
101
102 FUNCTION Insert_Order_Line_Detail (
103 p_original_line_detail_id IN BINARY_INTEGER,
104 p_new_line_detail_id IN OUT BINARY_INTEGER,
105 p_quantity IN BINARY_INTEGER,
106 p_new_delivery IN BINARY_INTEGER
107 ) RETURN BINARY_INTEGER;
108
109 FUNCTION Process_Key (
110 p_mode IN VARCHAR2,
111 p_header_id IN BINARY_INTEGER,
112 p_customer_id IN BINARY_INTEGER,
113 p_ship_method_code IN VARCHAR2,
114 p_ship_to_site_use_id IN BINARY_INTEGER,
115 p_shipment_priority IN VARCHAR2,
116 p_subinventory IN VARCHAR2,
117 p_departure_id IN BINARY_INTEGER,
118 p_delivery_id IN OUT BINARY_INTEGER,
119 p_warehouse_id IN BINARY_INTEGER,
120 new_flag IN OUT VARCHAR2
121 ) RETURN BINARY_INTEGER;
122
123 --
124 -- Name
125 -- FUNCTION Init
126 --
127 -- Purpose
128 -- This function initializes the who variables, g_reservations variable,
129 -- and the g_use_ variables to be used in determining the how to group
130 -- pick slips.
131 --
132 -- Return Values
133 -- -1 => Failure
134 -- 0 => Success
135 --
136
137 FUNCTION Init
138 RETURN BINARY_INTEGER IS
139 CURSOR ps_rule (x_psr_id IN BINARY_INTEGER) IS
140 SELECT NVL(ORDER_NUMBER_FLAG, 'N'),
141 NVL(SUBINVENTORY_FLAG, 'N'),
142 NVL(CUSTOMER_FLAG, 'N'),
143 NVL(SHIP_TO_FLAG, 'N'),
144 NVL(CARRIER_FLAG, 'N'),
145 NVL(SHIPMENT_PRIORITY_FLAG, 'N'),
146 NVL(DEPARTURE_FLAG, 'N'),
147 NVL(DELIVERY_FLAG, 'N')
148 FROM WSH_PICK_SLIP_RULES
149 WHERE PICK_SLIP_RULE_ID = x_psr_id;
150
151 CURSOR get_autocreate_del_orders(x_warehouse_id IN BINARY_INTEGER) IS
152 SELECT NVL(AUTOCREATE_DEL_ORDERS_FLAG, 'Y')
153 FROM WSH_PARAMETERS
154 WHERE organization_id = x_warehouse_id;
155
156 BEGIN
157 WSH_UTIL.Write_Log('Starting WSH_PR_PICKING_OBJECTS.Init');
158 IF initialized = TRUE THEN
159 RETURN SUCCESS;
160 END IF;
161
162 -- Initialize who parameters
163 g_login_id := WSH_PR_PICKING_SESSION.login_id;
164 g_user_id := WSH_PR_PICKING_SESSION.user_id;
165 g_program_id := WSH_PR_PICKING_SESSION.program_id;
166 g_request_id := WSH_PR_PICKING_SESSION.request_id;
167 g_application_id := WSH_PR_PICKING_SESSION.application_id;
168 g_batch_id := WSH_PR_PICKING_SESSION.batch_id;
169 g_pick_slip_rule_id := WSH_PR_PICKING_SESSION.pick_slip_rule_id;
170 g_reservations := WSH_PR_PICKING_SESSION.reservations;
171 g_warehouse_id := WSH_PR_PICKING_SESSION.warehouse_id;
172 g_autocreate_deliveries := WSH_PR_PICKING_SESSION.autocreate_deliveries;
173
174 -- Clear tables to track unique picking headers and pick slip numbers
175 g_ps_table.delete;
176 g_ph_table.delete;
177
178 -- fetch information on pick slip grouping rule
179 OPEN ps_rule(g_pick_slip_rule_id);
180 FETCH ps_rule
181 INTO g_use_order_ps,
182 g_use_sub_ps,
183 g_use_customer_ps,
184 g_use_ship_to_ps,
185 g_use_carrier_ps,
186 g_use_ship_priority_ps,
187 g_use_departure_ps,
188 g_use_delivery_ps;
189
190 IF ps_rule%NOTFOUND THEN
191 WSH_UTIL.Write_Log('Warning: Pick slip rule '
192 || to_char(g_pick_slip_rule_id) ||
193 ' does not exist');
194 RETURN FAILURE;
195 END IF;
196
197 -- fetch autocreate deliveries order rule
198 OPEN get_autocreate_del_orders(g_warehouse_id);
199 FETCH get_autocreate_del_orders
200 INTO g_use_autocreate_del_orders;
201
202 IF get_autocreate_del_orders%NOTFOUND THEN
203 WSH_UTIL.Write_Log('Warning: No autocreate rule will use Y');
204 g_use_autocreate_del_orders := 'Y';
205 END IF;
206
207 CLOSE ps_rule;
208 CLOSE get_autocreate_del_orders;
209
210 initialized := TRUE;
211
212 RETURN SUCCESS;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 WSH_UTIL.DEFAULT_HANDLER('WSH_PR_PICKING_OBJECTS.Init', 'Error in Init');
217 IF ps_rule%ISOPEN THEN
218 CLOSE ps_rule;
219 END IF;
220 IF get_autocreate_del_orders%ISOPEN THEN
221 CLOSE get_autocreate_del_orders;
222 END IF;
223 RETURN FAILURE;
224
225 END Init;
226
227
228 --
229 -- Name
230 -- FUNCTION Insert_Lines
231 --
232 -- Purpose
233 -- This function inserts picking headers, picking lines,
234 -- picking line details and order lines if applicable.
235 --
236 -- Return Values
237 -- -1 => Failure
238 -- 0 => Success
239 --
240
241 FUNCTION Insert_lines(
242 p_backorder_line IN BINARY_INTEGER,
243 p_order_header_id IN BINARY_INTEGER,
244 p_org_id IN BINARY_INTEGER,
245 p_customer_id IN BINARY_INTEGER,
246 p_ship_to_site_use_id IN BINARY_INTEGER,
247 p_component_code IN VARCHAR2,
248 p_component_ratio IN BINARY_INTEGER,
249 p_component_sequence_id IN BINARY_INTEGER,
250 p_date_requested IN DATE,
251 p_included_item_flag IN VARCHAR2,
252 p_inventory_item_id IN BINARY_INTEGER,
253 p_original_line_detail_id IN BINARY_INTEGER,
254 p_order_line_id IN BINARY_INTEGER,
255 p_original_requested_quantity IN BINARY_INTEGER,
256 p_requested_quantity IN BINARY_INTEGER,
257 p_schedule_date IN DATE,
258 p_sequence_number IN BINARY_INTEGER,
259 p_shipment_priority_code IN VARCHAR2,
260 p_ship_method_code IN VARCHAR2,
261 p_ship_to_contact_id IN BINARY_INTEGER,
262 p_unit_code IN VARCHAR2,
263 p_warehouse_id IN BINARY_INTEGER,
264 p_delivery IN BINARY_INTEGER,
265 p_demand_class IN VARCHAR2,
266 p_reservable_flag IN VARCHAR2,
267 p_schedule_level IN BINARY_INTEGER,
268 p_schedule_status_code IN VARCHAR2,
269 p_subinventory IN VARCHAR2,
270 p_autodetailed_quantity IN BINARY_INTEGER,
271 p_transactable_flag IN VARCHAR2,
272 p_config_item_flag IN VARCHAR2,
273 p_customer_requested_lot_flag IN VARCHAR2,
274 p_departure_id IN BINARY_INTEGER,
275 p_delivery_id IN OUT BINARY_INTEGER,
276 p_dep_plan_required_flag IN VARCHAR2,
277 p_customer_item_id IN BINARY_INTEGER,
278 p_master_container_item_id IN BINARY_INTEGER,
279 p_detail_container_item_id IN BINARY_INTEGER,
280 p_load_seq_number IN BINARY_INTEGER,
281 p_ccid IN BINARY_INTEGER,
282 p_autodetail_group_id IN BINARY_INTEGER,
283 p_autobackorder IN VARCHAR2,
284 p_picking_line_id IN OUT BINARY_INTEGER,
285 p_abo_picking_line_id IN OUT BINARY_INTEGER,
286 p_picking_header_id IN OUT BINARY_INTEGER,
287 p_new_line_detail_id IN OUT BINARY_INTEGER,
288 p_new_delivery IN OUT BINARY_INTEGER,
289 p_abo_recs IN OUT BINARY_INTEGER,
290 p_pld_recs IN OUT BINARY_INTEGER,
291 p_old_recs IN OUT BINARY_INTEGER
292 )
293 RETURN BINARY_INTEGER IS
294
295 CURSOR res_sub (x_ccid IN BINARY_INTEGER,
296 x_order_line_id IN BINARY_INTEGER,
297 x_delivery IN BINARY_INTEGER,
298 x_autodetail_group_id IN BINARY_INTEGER) IS
299 SELECT DISTINCT SUBINVENTORY
300 FROM MTL_DEMAND
301 WHERE DEMAND_SOURCE_HEADER_ID = x_ccid
302 AND DEMAND_SOURCE_TYPE IN (2,8)
303 AND DEMAND_SOURCE_LINE = to_char(x_order_line_id)
304 AND DEMAND_SOURCE_DELIVERY = to_char(x_delivery)
305 AND AUTODETAIL_GROUP_ID = x_autodetail_group_id
306 AND NVL(LINE_ITEM_QUANTITY,0) <> 0
307 AND PARENT_DEMAND_ID IS NOT NULL
308 AND RESERVATION_TYPE = 2;
309
310 CURSOR nores_sub (x_ccid IN BINARY_INTEGER,
311 x_order_line_id IN BINARY_INTEGER,
312 x_delivery IN BINARY_INTEGER) IS
313 SELECT DISTINCT SUBINVENTORY
314 FROM MTL_DEMAND
315 WHERE DEMAND_SOURCE_HEADER_ID = x_ccid
316 AND DEMAND_SOURCE_TYPE IN (2,8)
317 AND DEMAND_SOURCE_LINE = to_char(x_order_line_id)
318 AND DEMAND_SOURCE_DELIVERY = to_char(x_delivery)
319 AND AUTODETAIL_GROUP_ID IS NULL
320 AND NVL(LINE_ITEM_QUANTITY,0) <> 0
321 AND PARENT_DEMAND_ID IS NOT NULL;
322
323 new_flag VARCHAR2(1) := 'Y';
324 ph_id BINARY_INTEGER;
325 x_pl_id BINARY_INTEGER;
326 ps_number BINARY_INTEGER;
327 current_sub VARCHAR2(30);
328 rc BINARY_INTEGER;
329 x_delivery_id BINARY_INTEGER := NULL;
330
331 BEGIN
332 WSH_UTIL.Write_Log('In Insert_Lines...');
333
334 -- Initialize out variables
335 p_picking_line_id := 0;
336 p_abo_picking_line_id := 0;
337 p_picking_header_id := 0;
338 p_new_delivery := 0;
339 p_abo_recs := 0;
340 p_pld_recs := 0;
341 p_old_recs := 0;
342
343 IF p_backorder_line = UNRELEASED_LINE THEN
344 WSH_UTIL.Write_Log('Processing unreleased line');
345 ELSIF p_backorder_line = BACKORDER_LINE THEN
346 WSH_UTIL.Write_Log('Processing backordered line');
347 END IF;
348
349 -- Determine if a new picking_header_id needs to be created
350 ph_id := Process_Key(
351 'PICKING_HEADER_ID',
352 p_order_header_id,
353 p_customer_id,
354 p_ship_method_code,
355 p_ship_to_site_use_id,
356 p_shipment_priority_code,
357 p_subinventory,
358 p_departure_id,
359 p_delivery_id,
360 p_warehouse_id,
361 new_flag);
362
363 IF ph_id = FAILURE THEN
364 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
365 RETURN FAILURE;
366 END IF;
367
368 -- Create new picking header if necessary
369 IF new_flag = 'Y' THEN
370 WSH_UTIL.Write_Log('Inserting picking header ' || to_char(ph_id));
371 INSERT INTO SO_PICKING_HEADERS_ALL (
372 PICKING_HEADER_ID,
373 CREATION_DATE,
374 CREATED_BY,
375 LAST_UPDATE_DATE,
376 LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN,
378 PROGRAM_APPLICATION_ID,
379 PROGRAM_ID,
380 PROGRAM_UPDATE_DATE,
381 REQUEST_ID,
382 BATCH_ID,
383 ORDER_HEADER_ID,
384 WAREHOUSE_ID,
385 SHIP_TO_SITE_USE_ID,
386 STATUS_CODE,
387 PICK_SLIP_NUMBER,
388 SHIP_METHOD_CODE,
389 DATE_RELEASED,
390 ORG_ID)
391 VALUES (
392 ph_id,
393 sysdate,
394 g_user_id,
395 sysdate,
396 g_user_id,
397 g_login_id,
398 g_application_id,
399 g_program_id,
400 sysdate,
401 g_request_id,
402 g_batch_id,
403 p_order_header_id,
404 p_warehouse_id,
405 p_ship_to_site_use_id,
406 'OPEN',
407 -1,
408 p_ship_method_code,
409 sysdate,
410 decode(p_org_id, -3114, NULL, p_org_id));
411 END IF;
412
413 p_picking_header_id := ph_id;
414
415 -- If autocreate deliveries is on, d etermine delivery to use
416 IF ((g_autocreate_deliveries = 'Y') AND
417 (p_departure_id = -1) AND
418 (p_delivery_id = -1)) THEN
419 p_delivery_id := WSH_PR_CREATE_DELIVERIES.Get_Delivery(
420 p_order_header_id,
421 p_ship_to_site_use_id,
422 p_ship_method_code,
423 p_warehouse_id);
424 IF p_delivery_id = -1 THEN
425 WSH_UTIL.Write_Log('Invalid delivery id, ignoring...');
426 END IF;
427 END IF;
428
429 -- If reservations are off
430 IF g_reservations = 'N' THEN
431 WSH_UTIL.Write_Log('Reservations are off');
432 -- Determine the pick slip number
433 ps_number := Process_Key (
434 'PICK_SLIP_NUMBER',
435 p_order_header_id,
436 p_customer_id,
437 p_ship_method_code,
438 p_ship_to_site_use_id,
439 p_shipment_priority_code,
440 p_subinventory,
441 p_departure_id,
442 p_delivery_id,
443 p_warehouse_id,
444 new_flag);
445
446 WSH_UTIL.Write_Log('Pick Slip Number is ' || to_char(ps_number));
447
448 IF ps_number = FAILURE THEN
449 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
450 RETURN FAILURE;
451 END IF;
452
453 SELECT SO_PICKING_LINES_S.NEXTVAL
454 INTO x_pl_id
455 FROM DUAL;
456
457 rc := Insert_Picking_Line (
458 x_pl_id,
459 p_component_code,
460 p_component_ratio,
461 p_component_sequence_id,
462 NULL,
463 p_date_requested,
464 p_demand_class,
465 p_included_item_flag,
466 p_inventory_item_id,
467 p_customer_item_id,
468 p_original_line_detail_id,
469 p_order_line_id,
470 p_original_requested_quantity,
471 ph_id,
472 p_original_requested_quantity,
473 p_schedule_date,
474 p_sequence_number,
475 p_shipment_priority_code,
476 p_ship_method_code,
477 p_ship_to_contact_id,
478 p_ship_to_site_use_id,
479 p_unit_code,
480 p_warehouse_id,
481 p_org_id);
482
483 IF rc = FAILURE THEN
484 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
485 RETURN FAILURE;
486 END IF;
487
488 p_picking_line_id := x_pl_id;
489
490 IF p_transactable_flag = 'Y' THEN
491 OPEN nores_sub(p_ccid, p_order_line_id, p_delivery);
492 FETCH nores_sub INTO current_sub;
493 IF nores_sub%NOTFOUND THEN
494 WSH_UTIL.Write_Log('Could not fine a subinventory in nores_sub');
495 current_sub := NULL;
496 ELSE
497 WSH_UTIL.Write_Log('Using Subinventory ' || current_sub ||
498 ' for Transactable, Non-reservable');
499 END IF;
500 CLOSE nores_sub;
501
502 -- Determine the pick slip number
503 ps_number := Process_Key (
504 'PICK_SLIP_NUMBER',
505 p_order_header_id,
506 p_customer_id,
507 p_ship_method_code,
508 p_ship_to_site_use_id,
509 p_shipment_priority_code,
510 current_sub,
511 p_departure_id,
512 p_delivery_id,
513 p_warehouse_id,
514 new_flag);
515
516 WSH_UTIL.Write_Log('Pick Slip Number is ' || to_char(ps_number));
517
518 IF ps_number = FAILURE THEN
519 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
520 RETURN FAILURE;
521 END IF;
522
523 rc := Insert_Picking_Line_Details (
524 TRANSNONRES,
525 ps_number,
526 p_departure_id,
527 p_delivery_id,
528 p_dep_plan_required_flag,
529 'Y',
530 NULL,
531 p_ccid,
532 p_order_line_id,
533 p_master_container_item_id,
534 p_detail_container_item_id,
535 p_inventory_item_id,
536 p_load_seq_number,
537 p_autodetail_group_id,
538 p_delivery,
539 p_demand_class,
540 x_pl_id,
541 p_original_requested_quantity,
542 p_reservable_flag,
543 p_schedule_date,
544 p_schedule_level,
545 'DEMANDED',
546 current_sub,
547 p_transactable_flag,
548 NULL,
549 p_warehouse_id);
550 ELSE
551 rc := Insert_Picking_Line_Details (
552 RESOFF,
553 ps_number,
554 p_departure_id,
555 p_delivery_id,
556 p_dep_plan_required_flag,
557 NULL,
558 p_customer_requested_lot_flag,
559 p_ccid,
560 p_order_line_id,
561 p_master_container_item_id,
562 p_detail_container_item_id,
563 p_inventory_item_id,
564 p_load_seq_number,
565 p_autodetail_group_id,
566 p_delivery,
567 p_demand_class,
568 x_pl_id,
569 p_original_requested_quantity,
570 p_reservable_flag,
571 p_schedule_date,
572 p_schedule_level,
573 p_schedule_status_code,
574 current_sub,
575 p_transactable_flag,
576 NULL,
577 p_warehouse_id);
578
579 END IF;
580
581 IF rc = FAILURE THEN
582 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
583 RETURN FAILURE;
584 END IF;
585
586 p_pld_recs := p_pld_recs + rc;
587
588 ELSIF g_reservations = 'Y' THEN /* Reservations on */
589
590 WSH_UTIL.Write_Log('Reservations are on');
591
592 -- Process transactable and reservable lines
593 IF ((p_transactable_flag = 'Y') AND (p_reservable_flag = 'Y'))THEN
594 WSH_UTIL.Write_Log('Transactable and Reservable');
595
596 IF p_autodetailed_quantity > 0 THEN
597 -- Insert picking line
598 SELECT SO_PICKING_LINES_S.NEXTVAL
599 INTO x_pl_id
600 FROM DUAL;
601
602 rc := Insert_Picking_Line (
603 x_pl_id,
604 p_component_code,
605 p_component_ratio,
606 p_component_sequence_id,
607 p_config_item_flag,
608 p_date_requested,
609 p_demand_class,
610 p_included_item_flag,
611 p_inventory_item_id,
612 p_customer_item_id,
613 p_original_line_detail_id,
614 p_order_line_id,
615 p_original_requested_quantity,
616 ph_id,
617 p_requested_quantity,
618 p_schedule_date,
619 p_sequence_number,
620 p_shipment_priority_code,
621 p_ship_method_code,
622 p_ship_to_contact_id,
623 p_ship_to_site_use_id,
624 p_unit_code,
625 p_warehouse_id,
626 p_org_id);
627
628 IF rc = FAILURE THEN
629 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
630 RETURN FAILURE;
631 END IF;
632
633 p_picking_line_id := x_pl_id;
634
635 -------------------------------------------------------------------------
636 -- Since pick slip numbers might depend on subinventories, the picking
637 -- line details to be inserted when using subinventory as part of the
638 -- pick slip grouping rule is slightly different.
639 -------------------------------------------------------------------------
640 IF g_use_sub_ps = 'Y' THEN
641 OPEN res_sub(p_ccid, p_order_line_id, p_delivery, p_autodetail_group_id);
642 LOOP
643 FETCH res_sub INTO current_sub;
644 EXIT WHEN res_sub%NOTFOUND;
645 -- Determine the pick slip number
646 ps_number := Process_Key (
647 'PICK_SLIP_NUMBER',
648 p_order_header_id,
649 p_customer_id,
650 p_ship_method_code,
651 p_ship_to_site_use_id,
652 p_shipment_priority_code,
653 current_sub,
654 p_departure_id,
655 p_delivery_id,
656 p_warehouse_id,
657 new_flag);
658
659 WSH_UTIL.Write_Log('Pick Slip Number is ' || to_char(ps_number));
660
661 IF ps_number = FAILURE THEN
662 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
663 RETURN FAILURE;
664 END IF;
665
666 rc := Insert_Picking_Line_Details (
667 RESERVABLE,
668 ps_number,
669 p_departure_id,
670 p_delivery_id,
671 p_dep_plan_required_flag,
672 'Y',
673 p_customer_requested_lot_flag,
674 p_ccid,
675 p_order_line_id,
676 p_master_container_item_id,
677 p_detail_container_item_id,
678 p_inventory_item_id,
679 p_load_seq_number,
680 p_autodetail_group_id,
681 p_delivery,
682 p_demand_class,
683 x_pl_id,
684 p_original_requested_quantity,
685 p_reservable_flag,
686 p_schedule_date,
687 p_schedule_level,
688 p_schedule_status_code,
689 current_sub,
690 p_transactable_flag,
691 NULL,
692 p_warehouse_id);
693
694 IF rc = FAILURE THEN
695 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
696 RETURN FAILURE;
697 END IF;
698
699 p_pld_recs := p_pld_recs + rc;
700
701 END LOOP;
702 CLOSE res_sub;
703
704 ELSE /* IF NOT g_use_sub_ps */
705 -- Determine the pick slip number
706 ps_number := Process_Key (
707 'PICK_SLIP_NUMBER',
708 p_order_header_id,
709 p_customer_id,
710 p_ship_method_code,
711 p_ship_to_site_use_id,
712 p_shipment_priority_code,
713 p_subinventory,
714 p_departure_id,
715 p_delivery_id,
716 p_warehouse_id,
717 new_flag);
718
719 WSH_UTIL.Write_Log('Pick Slip Number is ' || to_char(ps_number));
720
721 IF ps_number = FAILURE THEN
722 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
723 RETURN FAILURE;
724 END IF;
725
726 rc := Insert_Picking_Line_Details (
727 RESERVABLE,
728 ps_number,
729 p_departure_id,
730 p_delivery_id,
731 p_dep_plan_required_flag,
732 'Y',
733 p_customer_requested_lot_flag,
734 p_ccid,
735 p_order_line_id,
736 p_master_container_item_id,
737 p_detail_container_item_id,
738 p_inventory_item_id,
739 p_load_seq_number,
740 p_autodetail_group_id,
741 p_delivery,
742 p_demand_class,
743 x_pl_id,
744 p_original_requested_quantity,
745 p_reservable_flag,
746 p_schedule_date,
747 p_schedule_level,
748 p_schedule_status_code,
749 p_subinventory,
750 p_transactable_flag,
751 NULL,
752 p_warehouse_id);
753
754 IF rc = FAILURE THEN
755 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
756 RETURN FAILURE;
757 END IF;
758
759 p_pld_recs := p_pld_recs + rc;
760
761 END IF; /* g_use_sub_ps */
762
763 END IF; /* p_autodetailed_quantity >0 */
764
765 ---------------------------------------------------------------------
766 -- If partial reservation was made, must split the order line detail
767 -- or create a new backorder picking line
768 ------------------------------------------------------------------
769 IF ((p_autodetailed_quantity <> p_original_requested_quantity) AND
770 (p_autodetailed_quantity > 0)) THEN
771
772 WSH_UTIL.Write_Log('Incomplete Reservations made');
773
774 IF p_backorder_line = BACKORDER_LINE THEN
775 -- Create a picking line for the remaining quantity with
776 -- picking header id = 0
777 SELECT SO_PICKING_LINES_S.NEXTVAL
778 INTO x_pl_id
779 FROM DUAL;
780
781 rc := Insert_Picking_Line (
782 x_pl_id,
783 p_component_code,
784 p_component_ratio,
785 p_component_sequence_id,
786 p_config_item_flag,
787 p_date_requested,
788 p_demand_class,
789 p_included_item_flag,
790 p_inventory_item_id,
791 p_customer_item_id,
792 p_original_line_detail_id,
793 p_order_line_id,
794 p_original_requested_quantity,
795 0,
796 p_original_requested_quantity -
797 p_autodetailed_quantity,
798 p_schedule_date,
799 p_sequence_number,
800 p_shipment_priority_code,
801 p_ship_method_code,
802 p_ship_to_contact_id,
803 p_ship_to_site_use_id,
804 p_unit_code,
805 p_warehouse_id,
806 p_org_id);
807
808 IF rc = FAILURE THEN
809 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
810 RETURN FAILURE;
811 END IF;
812
813 p_abo_picking_line_id := x_pl_id;
814 WSH_UTIL.Write_Log('Backorder picking_line_id = ' ||
815 to_char(p_abo_picking_line_id));
816
817 IF p_autodetailed_quantity = 0 THEN
818 p_new_delivery := 0;
819 ELSE
820 SELECT SO_DELIVERIES_S.NEXTVAL
821 INTO p_new_delivery
822 FROM DUAL;
823 END IF;
824
825 rc := Insert_Picking_Line_Details (
826 BACKORDER,
827 NULL,
828 NULL,
829 x_delivery_id,
830 p_dep_plan_required_flag,
831 NULL,
832 NULL,
833 p_ccid,
834 p_order_line_id,
835 p_master_container_item_id,
836 p_detail_container_item_id,
837 p_inventory_item_id,
838 p_load_seq_number,
839 p_autodetail_group_id,
840 p_new_delivery,
841 p_demand_class,
842 x_pl_id,
843 p_original_requested_quantity -
844 p_autodetailed_quantity,
845 p_reservable_flag,
846 p_schedule_date,
847 p_schedule_level,
848 'DEMANDED',
849 p_subinventory,
850 p_transactable_flag,
851 'N',
852 p_warehouse_id);
853
854 IF rc = FAILURE THEN
855 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
856 RETURN FAILURE;
857 END IF;
858
859 WSH_UTIL.Write_Log('Picking Line Detail created with delivery = ' || to_char(p_new_delivery));
860
861 p_abo_recs := p_abo_recs + rc;
862
863 ELSIF p_backorder_line = UNRELEASED_LINE THEN
864 IF p_autodetailed_quantity > 0 THEN
865 SELECT SO_DELIVERIES_S.NEXTVAL
866 INTO p_new_delivery
867 FROM DUAL;
868
869 -- Create a new order line detail
870 rc := Insert_Order_Line_Detail (
871 p_original_line_detail_id,
872 p_new_line_detail_id,
873 p_original_requested_quantity -
874 p_autodetailed_quantity,
875 p_new_delivery);
876
877 IF rc = FAILURE THEN
878 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
879 RETURN FAILURE;
880 END IF;
881
882 WSH_UTIL.Write_Log('Order Line Detail created with delivery = ' || to_char(p_new_delivery));
883
884 p_old_recs := p_old_recs + rc;
885
886 END IF;
887
888 ELSE
889 WSH_UTIL.Write_Log('Invalid line status');
890 RETURN FAILURE;
891
892 END IF; /* p_backorder_line = 'Y' */
893
894 END IF; /* p_autodetailed_quantity <> p_original_requested_quantity */
895
896 ELSIF (p_transactable_flag = 'Y') AND (p_reservable_flag = 'N') THEN
897
898 WSH_UTIL.Write_Log('Transactable, Not Reservable');
899
900 -- Insert picking line
901 SELECT SO_PICKING_LINES_S.NEXTVAL
902 INTO x_pl_id
903 FROM DUAL;
904
905 rc := Insert_Picking_Line (
906 x_pl_id,
907 p_component_code,
908 p_component_ratio,
909 p_component_sequence_id,
910 p_config_item_flag,
911 p_date_requested,
912 p_demand_class,
913 p_included_item_flag,
914 p_inventory_item_id,
915 p_customer_item_id,
916 p_original_line_detail_id,
917 p_order_line_id,
918 p_original_requested_quantity,
919 ph_id,
920 p_original_requested_quantity,
921 p_schedule_date,
922 p_sequence_number,
923 p_shipment_priority_code,
924 p_ship_method_code,
925 p_ship_to_contact_id,
926 p_ship_to_site_use_id,
927 p_unit_code,
928 p_warehouse_id,
929 p_org_id);
930
931 IF rc = FAILURE THEN
932 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
933 RETURN FAILURE;
934 END IF;
935
936 OPEN nores_sub(p_ccid, p_order_line_id, p_delivery);
937 FETCH nores_sub INTO current_sub;
938 IF nores_sub%NOTFOUND THEN
939 WSH_UTIL.Write_Log('Could not fine a subinventory in nores_sub');
940 current_sub := NULL;
941 ELSE
942 WSH_UTIL.Write_Log('Using Subinventory ' || current_sub ||
943 ' for Transactable, Non-reservable');
944 END IF;
945 CLOSE nores_sub;
946
947 -- Determine the pick slip number
948 ps_number := Process_Key (
949 'PICK_SLIP_NUMBER',
950 p_order_header_id,
951 p_customer_id,
952 p_ship_method_code,
953 p_ship_to_site_use_id,
954 p_shipment_priority_code,
955 current_sub,
956 p_departure_id,
957 p_delivery_id,
958 p_warehouse_id,
959 new_flag);
960
961 WSH_UTIL.Write_Log('Pick Slip Number is ' || to_char(ps_number));
962
963 IF ps_number = FAILURE THEN
964 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
965 RETURN FAILURE;
966 END IF;
967
968 rc := Insert_Picking_Line_Details (
969 TRANSNONRES,
970 ps_number,
971 p_departure_id,
972 p_delivery_id,
973 p_dep_plan_required_flag,
974 'Y',
975 NULL,
976 p_ccid,
977 p_order_line_id,
978 p_master_container_item_id,
979 p_detail_container_item_id,
980 p_inventory_item_id,
981 p_load_seq_number,
982 p_autodetail_group_id,
983 p_delivery,
984 p_demand_class,
985 x_pl_id,
986 p_original_requested_quantity,
987 p_reservable_flag,
988 p_schedule_date,
989 p_schedule_level,
990 'DEMANDED',
991 current_sub,
992 p_transactable_flag,
993 NULL,
994 p_warehouse_id);
995
996 IF rc = FAILURE THEN
997 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
998 RETURN FAILURE;
999 END IF;
1000
1001 ELSIF (p_transactable_flag = 'N') THEN
1002
1003 WSH_UTIL.Write_Log('Not Transactable');
1004
1005 -- Determine the pick slip number
1006 ps_number := Process_Key (
1007 'PICK_SLIP_NUMBER',
1008 p_order_header_id,
1009 p_customer_id,
1010 p_ship_method_code,
1011 p_ship_to_site_use_id,
1012 p_shipment_priority_code,
1013 p_subinventory,
1014 p_departure_id,
1015 p_delivery_id,
1016 p_warehouse_id,
1017 new_flag);
1018
1019 WSH_UTIL.Write_Log('Pick Slip Number is ' || to_char(ps_number));
1020
1021 IF ps_number = FAILURE THEN
1022 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Process_Key');
1023 RETURN FAILURE;
1024 END IF;
1025
1026 SELECT SO_PICKING_LINES_S.NEXTVAL
1027 INTO x_pl_id
1028 FROM DUAL;
1029
1030 rc := Insert_Picking_Line (
1031 x_pl_id,
1032 p_component_code,
1033 p_component_ratio,
1034 p_component_sequence_id,
1035 'Y',
1036 p_date_requested,
1037 p_demand_class,
1038 p_included_item_flag,
1039 p_inventory_item_id,
1040 p_customer_item_id,
1041 p_original_line_detail_id,
1042 p_order_line_id,
1043 p_original_requested_quantity,
1044 ph_id,
1045 p_original_requested_quantity,
1046 p_schedule_date,
1047 p_sequence_number,
1048 p_shipment_priority_code,
1049 p_ship_method_code,
1050 p_ship_to_contact_id,
1051 p_ship_to_site_use_id,
1052 p_unit_code,
1053 p_warehouse_id,
1054 p_org_id);
1055
1056 IF rc = FAILURE THEN
1057 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
1058 RETURN FAILURE;
1059 END IF;
1060
1061 rc := Insert_Picking_Line_Details (
1062 NONTRANS,
1063 ps_number,
1064 p_departure_id,
1065 p_delivery_id,
1066 p_dep_plan_required_flag,
1067 NULL,
1068 p_customer_requested_lot_flag,
1069 p_ccid,
1070 p_order_line_id,
1071 p_master_container_item_id,
1072 p_detail_container_item_id,
1073 p_inventory_item_id,
1074 p_load_seq_number,
1075 p_autodetail_group_id,
1076 p_delivery,
1077 p_demand_class,
1078 x_pl_id,
1079 p_original_requested_quantity,
1080 p_reservable_flag,
1081 p_schedule_date,
1082 p_schedule_level,
1083 p_schedule_status_code,
1084 p_subinventory,
1085 p_transactable_flag,
1086 NULL,
1087 p_warehouse_id);
1088
1089 IF rc = FAILURE THEN
1090 WSH_UTIL.Write_Log('WSH_PR_PICKING_OBJECTS: Error in Insert_Lines');
1091 RETURN FAILURE;
1092 END IF;
1093
1094 END IF; /* Reservable, transactable etc */
1095 END IF; /* reservations */
1096
1097 RETURN SUCCESS;
1098
1099 EXCEPTION
1100 WHEN OTHERS THEN
1101 IF res_sub%ISOPEN THEN
1102 CLOSE res_sub;
1103 END IF;
1104 IF nores_sub%ISOPEN THEN
1105 CLOSE res_sub;
1106 END IF;
1107 WSH_UTIL.Default_Handler('WSH_PR_PICKING_OBJECTS.Insert_Lines');
1108 RETURN FAILURE;
1109
1110 END Insert_Lines;
1111
1112
1113 --
1114 -- Name
1115 -- FUNCTION Insert_Picking_Line
1116 --
1117 -- Purpose
1118 -- This function inserts a picking line based on
1119 -- the parameters passed to it.
1120 --
1121 -- Return Values
1122 -- -1 => Failure
1123 -- 0 => Success
1124 --
1125
1126 FUNCTION Insert_Picking_Line (
1127 p_picking_line_id IN BINARY_INTEGER,
1128 p_component_code IN VARCHAR2,
1129 p_component_ratio IN BINARY_INTEGER,
1130 p_component_sequence_id IN BINARY_INTEGER,
1131 p_config_item_flag IN VARCHAR2,
1132 p_date_requested IN DATE,
1133 p_demand_class IN VARCHAR2,
1134 p_included_item_flag IN VARCHAR2,
1135 p_inventory_item_id IN BINARY_INTEGER,
1136 p_customer_item_id IN BINARY_INTEGER,
1137 p_original_line_detail_id IN BINARY_INTEGER,
1138 p_order_line_id IN BINARY_INTEGER,
1139 p_original_requested_quantity IN BINARY_INTEGER,
1140 p_pick_header_id IN BINARY_INTEGER,
1141 p_requested_quantity IN BINARY_INTEGER,
1142 p_schedule_date IN DATE,
1143 p_sequence_number IN BINARY_INTEGER,
1144 p_shipment_priority_code IN VARCHAR2,
1145 p_ship_method_code IN VARCHAR2,
1146 p_ship_to_contact_id IN BINARY_INTEGER,
1147 p_ship_to_site_use_id IN BINARY_INTEGER,
1148 p_unit_code IN VARCHAR2,
1149 p_warehouse_id IN BINARY_INTEGER,
1150 p_org_id IN BINARY_INTEGER
1151 )
1152 RETURN BINARY_INTEGER IS
1153 BEGIN
1154
1155 WSH_UTIL.Write_Log('--------------------');
1156 WSH_UTIL.Write_Log('Inserting Picking Line');
1157 WSH_UTIL.Write_Log('picking_line_id = ' || to_char(p_picking_line_id) ||
1158 ' picking_header_id = ' || to_char(p_pick_header_id));
1159 WSH_UTIL.Write_Log('order_line_id = ' || to_char(p_order_line_id) ||
1160 ' original_line_detail_id = ' || to_char(p_original_line_detail_id));
1161 WSH_UTIL.Write_Log('requested_quantity = ' || to_char(p_requested_quantity));
1162 WSH_UTIL.Write_Log('--------------------');
1163
1164 INSERT INTO SO_PICKING_LINES_ALL (
1165 PICKING_LINE_ID,
1166 CREATED_BY,
1167 CREATION_DATE,
1168 LAST_UPDATED_BY,
1169 LAST_UPDATE_DATE,
1170 LAST_UPDATE_LOGIN,
1171 PROGRAM_APPLICATION_ID,
1172 PROGRAM_ID,
1173 PROGRAM_UPDATE_DATE,
1174 REQUEST_ID,
1175 COMPONENT_CODE,
1176 COMPONENT_RATIO,
1177 COMPONENT_SEQUENCE_ID,
1178 CONFIGURATION_ITEM_FLAG,
1179 DATE_REQUESTED,
1180 DEMAND_CLASS_CODE,
1181 INCLUDED_ITEM_FLAG,
1182 INVENTORY_ITEM_ID,
1183 CUSTOMER_ITEM_ID,
1184 LINE_DETAIL_ID,
1185 ORDER_LINE_ID,
1186 ORIGINAL_REQUESTED_QUANTITY,
1187 PICKING_HEADER_ID,
1188 REQUESTED_QUANTITY,
1189 SCHEDULE_DATE,
1190 SEQUENCE_NUMBER,
1191 SHIPMENT_PRIORITY_CODE,
1192 SHIP_METHOD_CODE,
1193 SHIP_TO_CONTACT_ID,
1194 SHIP_TO_SITE_USE_ID,
1195 UNIT_CODE,
1196 WAREHOUSE_ID,
1197 ORG_ID)
1198 VALUES (
1199 p_picking_line_id,
1200 g_user_id,
1201 SYSDATE,
1202 g_user_id,
1203 SYSDATE,
1204 g_login_id,
1205 g_application_id,
1206 g_program_id,
1207 SYSDATE,
1208 g_request_id,
1209 p_component_code,
1210 p_component_ratio,
1211 p_component_sequence_id,
1212 p_config_item_flag,
1213 p_date_requested,
1214 p_demand_class,
1215 p_included_item_flag,
1216 p_inventory_item_id,
1217 decode(p_customer_item_id,
1218 -1, NULL,
1219 p_customer_item_id),
1220 p_original_line_detail_id,
1221 p_order_line_id,
1222 p_original_requested_quantity,
1223 p_pick_header_id,
1224 p_requested_quantity,
1225 p_schedule_date,
1226 p_sequence_number,
1227 p_shipment_priority_code,
1228 p_ship_method_code,
1229 DECODE(p_ship_to_contact_id,
1230 -1, NULL,
1231 p_ship_to_contact_id),
1232 DECODE(p_ship_to_site_use_id,
1233 -1, NULL,
1234 p_ship_to_site_use_id),
1235 p_unit_code,
1236 p_warehouse_id,
1237 decode(p_org_id, -3114, NULL, p_org_id));
1238
1239 RETURN SUCCESS;
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 WSH_UTIL.Default_Handler('WSH_PR_PICKING_OBJECTS.Insert_Picking_Line');
1244 RETURN FAILURE;
1245
1246 END Insert_Picking_Line;
1247
1248
1249 --
1250 -- Name
1251 -- FUNCTION Insert_Picking_Line_Details
1252 --
1253 -- Purpose
1254 -- This function inserts picking line details based on
1255 -- the parameters passed to it.
1256 --
1257 -- Return Values
1258 -- -1 => Failure
1259 -- 0 => Success
1260 --
1261
1262 FUNCTION Insert_Picking_Line_Details (
1263 p_mode IN BINARY_INTEGER,
1264 p_ps_number IN BINARY_INTEGER,
1265 p_departure_id IN BINARY_INTEGER,
1266 p_delivery_id IN OUT BINARY_INTEGER,
1267 p_dep_plan_required_flag IN VARCHAR2,
1268 p_autoscheduled_flag IN VARCHAR2,
1269 p_customer_requested_lot_flag IN VARCHAR2,
1270 p_ccid IN BINARY_INTEGER,
1271 p_order_line_id IN BINARY_INTEGER,
1272 p_master_container_item_id IN BINARY_INTEGER,
1273 p_detail_container_item_id IN BINARY_INTEGER,
1274 p_inventory_item_id IN BINARY_INTEGER,
1275 p_load_seq_number IN BINARY_INTEGER,
1276 p_autodetail_group_id IN BINARY_INTEGER,
1277 p_delivery IN BINARY_INTEGER,
1278 p_demand_class IN VARCHAR2,
1279 p_picking_line_id IN BINARY_INTEGER,
1280 p_requested_quantity IN BINARY_INTEGER,
1281 p_reservable_flag IN VARCHAR2,
1282 p_schedule_date IN DATE,
1283 p_schedule_level IN VARCHAR2,
1284 p_schedule_status_code IN VARCHAR2,
1285 p_subinventory IN VARCHAR2,
1286 p_transactable_flag IN VARCHAR2,
1287 p_released_flag IN VARCHAR2,
1288 p_warehouse_id IN BINARY_INTEGER
1289 )
1290 RETURN BINARY_INTEGER IS
1291
1292 rows_inserted BINARY_INTEGER;
1293 l_dpw_assigned_flag VARCHAR2(1);
1294 default_subinventory VARCHAR2(10);
1295
1296 BEGIN
1297 WSH_UTIL.Write_Log('--------------------');
1298 WSH_UTIL.Write_Log('Mode is ' || to_char(p_mode));
1299
1300 IF p_departure_id = -1 AND p_delivery_id = -1 THEN
1301 l_dpw_assigned_flag := 'N';
1302 ELSE
1303 l_dpw_assigned_flag := NULL;
1304 END IF;
1305
1306 -- Get default subinventory, if not already set
1307 IF p_reservable_flag = 'N' THEN
1308 default_subinventory := WSH_DEL_OI_CORE.DEFAULT_SUBINVENTORY (
1309 p_warehouse_id,
1310 p_inventory_item_id);
1311 END IF;
1312
1313 IF p_mode in (RESOFF, NONTRANS) THEN
1314 INSERT INTO SO_PICKING_LINE_DETAILS (
1315 PICKING_LINE_DETAIL_ID,
1316 PICK_SLIP_NUMBER,
1317 CREATED_BY,
1318 CREATION_DATE,
1319 LAST_UPDATED_BY,
1320 LAST_UPDATE_DATE,
1321 LAST_UPDATE_LOGIN,
1322 PROGRAM_APPLICATION_ID,
1323 PROGRAM_ID,
1324 PROGRAM_UPDATE_DATE,
1325 REQUEST_ID,
1326 AUTOSCHEDULED_FLAG,
1327 DEPARTURE_ID,
1328 DELIVERY_ID,
1329 MASTER_CONTAINER_ITEM_ID,
1330 DETAIL_CONTAINER_ITEM_ID,
1331 LOAD_SEQ_NUMBER,
1332 DPW_ASSIGNED_FLAG,
1333 DELIVERY,
1334 DEMAND_CLASS_CODE,
1335 PICKING_LINE_ID,
1336 REQUESTED_QUANTITY,
1337 RESERVABLE_FLAG,
1338 SCHEDULE_DATE,
1339 SCHEDULE_LEVEL,
1340 SCHEDULE_STATUS_CODE,
1341 SUBINVENTORY,
1342 TRANSACTABLE_FLAG,
1343 WAREHOUSE_ID,
1344 MVT_STAT_STATUS)
1345 VALUES (
1346 SO_PICKING_LINE_DETAILS_S.NEXTVAL,
1347 p_ps_number,
1348 g_user_id,
1349 SYSDATE,
1350 g_user_id,
1351 SYSDATE,
1352 g_login_id,
1353 g_application_id,
1354 g_program_id,
1355 SYSDATE,
1356 g_request_id,
1357 decode(p_mode,NONTRANS,'Y',NULL),
1358 decode(p_departure_id, -1, NULL, p_departure_id),
1359 decode(p_delivery_id, -1, NULL, p_delivery_id),
1360 decode(p_master_container_item_id, -1, NULL, p_master_container_item_id),
1361 decode(p_detail_container_item_id, -1, NULL, p_detail_container_item_id),
1362 DECODE(p_load_seq_number,
1363 -1, NULL,
1364 p_load_seq_number),
1365 l_dpw_assigned_flag,
1366 p_delivery,
1367 p_demand_class,
1368 p_picking_line_id,
1369 p_requested_quantity,
1370 p_reservable_flag,
1371 p_schedule_date,
1372 p_schedule_level,
1373 p_schedule_status_code,
1374 nvl(p_subinventory, decode(p_reservable_flag, 'N', default_subinventory, NULL)),
1375 p_transactable_flag,
1376 p_warehouse_id,
1377 'NEW');
1378
1379 rows_inserted := SQL%ROWCOUNT;
1380
1381 ELSIF p_mode = RESERVABLE THEN
1382 INSERT INTO SO_PICKING_LINE_DETAILS (
1383 PICKING_LINE_DETAIL_ID,
1384 PICK_SLIP_NUMBER,
1385 CREATED_BY,
1386 CREATION_DATE,
1387 LAST_UPDATED_BY,
1388 LAST_UPDATE_DATE,
1389 LAST_UPDATE_LOGIN,
1390 PROGRAM_APPLICATION_ID,
1391 PROGRAM_ID,
1392 PROGRAM_UPDATE_DATE,
1393 REQUEST_ID,
1394 AUTOSCHEDULED_FLAG,
1395 CUSTOMER_REQUESTED_LOT_FLAG,
1396 DEPARTURE_ID,
1397 DELIVERY_ID,
1398 MASTER_CONTAINER_ITEM_ID,
1399 DETAIL_CONTAINER_ITEM_ID,
1400 LOAD_SEQ_NUMBER,
1401 DPW_ASSIGNED_FLAG,
1402 DELIVERY,
1403 DEMAND_CLASS_CODE,
1404 DEMAND_ID,
1405 INVENTORY_LOCATION_ID,
1406 LOT_NUMBER,
1407 PICKING_LINE_ID,
1408 REQUESTED_QUANTITY,
1409 RESERVABLE_FLAG,
1410 REVISION,
1411 SCHEDULE_DATE,
1412 SCHEDULE_LEVEL,
1413 SCHEDULE_STATUS_CODE,
1414 SUBINVENTORY,
1415 SUPPLY_SOURCE_HEADER_ID,
1416 SUPPLY_SOURCE_TYPE,
1417 TRANSACTABLE_FLAG,
1418 WAREHOUSE_ID,
1419 MVT_STAT_STATUS)
1420 SELECT
1421 SO_PICKING_LINE_DETAILS_S.NEXTVAL,
1422 p_ps_number,
1423 g_user_id,
1424 SYSDATE,
1425 g_user_id,
1426 SYSDATE,
1427 g_login_id,
1428 g_application_id,
1429 g_program_id,
1430 SYSDATE,
1431 g_request_id,
1432 'Y',
1433 p_customer_requested_lot_flag,
1434 decode(p_departure_id, -1, NULL, p_departure_id),
1435 decode(p_delivery_id, -1, NULL, p_delivery_id),
1436 decode(p_master_container_item_id, -1, NULL, p_master_container_item_id),
1437 decode(p_detail_container_item_id, -1, NULL, p_detail_container_item_id),
1438 DECODE(p_load_seq_number,
1439 -1, NULL,
1440 p_load_seq_number),
1441 l_dpw_assigned_flag,
1442 D.DEMAND_SOURCE_DELIVERY,
1443 D.DEMAND_CLASS,
1444 D.DEMAND_ID,
1445 D.LOCATOR_ID,
1446 D.LOT_NUMBER,
1447 p_picking_line_id,
1448 D.LINE_ITEM_QUANTITY,
1449 p_reservable_flag,
1450 D.REVISION,
1451 D.REQUIREMENT_DATE,
1452 p_schedule_level,
1453 'RESERVED',
1454 D.SUBINVENTORY,
1455 D.SUPPLY_SOURCE_HEADER_ID,
1456 D.SUPPLY_SOURCE_TYPE,
1457 p_transactable_flag,
1458 D.ORGANIZATION_ID,
1459 'NEW'
1460 FROM MTL_DEMAND D
1461 WHERE D.DEMAND_SOURCE_HEADER_ID = p_ccid
1462 AND D.DEMAND_SOURCE_TYPE IN (2,8)
1463 AND D.DEMAND_SOURCE_LINE = to_char(p_order_line_id)
1464 AND D.DEMAND_SOURCE_DELIVERY = to_char(p_delivery)
1465 AND D.AUTODETAIL_GROUP_ID = p_autodetail_group_id
1466 AND NVL(D.LINE_ITEM_QUANTITY,0) <> 0
1467 AND D.PARENT_DEMAND_ID IS NOT NULL
1468 AND D.RESERVATION_TYPE = 2
1469 AND D.SUBINVENTORY = DECODE(g_use_sub_ps, 'Y', p_subinventory, D.SUBINVENTORY);
1470
1471 rows_inserted := SQL%ROWCOUNT;
1472
1473 ELSIF p_mode = TRANSNONRES THEN
1474 INSERT INTO SO_PICKING_LINE_DETAILS (
1475 PICKING_LINE_DETAIL_ID,
1476 PICK_SLIP_NUMBER,
1477 CREATED_BY,
1478 CREATION_DATE,
1479 LAST_UPDATED_BY,
1480 LAST_UPDATE_DATE,
1481 LAST_UPDATE_LOGIN,
1482 PROGRAM_APPLICATION_ID,
1483 PROGRAM_ID,
1484 PROGRAM_UPDATE_DATE,
1485 REQUEST_ID,
1486 AUTOSCHEDULED_FLAG,
1487 CUSTOMER_REQUESTED_LOT_FLAG,
1488 DEPARTURE_ID,
1489 DELIVERY_ID,
1490 MASTER_CONTAINER_ITEM_ID,
1491 DETAIL_CONTAINER_ITEM_ID,
1492 LOAD_SEQ_NUMBER,
1493 DPW_ASSIGNED_FLAG,
1494 DELIVERY,
1495 DEMAND_CLASS_CODE,
1496 DEMAND_ID,
1497 INVENTORY_LOCATION_ID,
1498 LOT_NUMBER,
1499 PICKING_LINE_ID,
1500 REQUESTED_QUANTITY,
1501 RESERVABLE_FLAG,
1502 REVISION,
1503 SCHEDULE_DATE,
1504 SCHEDULE_LEVEL,
1505 SCHEDULE_STATUS_CODE,
1506 SUBINVENTORY,
1507 SUPPLY_SOURCE_HEADER_ID,
1508 SUPPLY_SOURCE_TYPE,
1509 TRANSACTABLE_FLAG,
1510 WAREHOUSE_ID,
1511 MVT_STAT_STATUS)
1512 SELECT
1513 SO_PICKING_LINE_DETAILS_S.NEXTVAL,
1514 p_ps_number,
1515 g_user_id,
1516 SYSDATE,
1517 g_user_id,
1518 SYSDATE,
1519 g_login_id,
1520 g_application_id,
1521 g_program_id,
1522 SYSDATE,
1523 g_request_id,
1524 'Y',
1525 p_customer_requested_lot_flag,
1526 decode(p_departure_id, -1, NULL, p_departure_id),
1527 decode(p_delivery_id, -1, NULL, p_delivery_id),
1528 decode(p_master_container_item_id, -1, NULL, p_master_container_item_id),
1529 decode(p_detail_container_item_id, -1, NULL, p_detail_container_item_id),
1530 DECODE(p_load_seq_number,
1531 -1, NULL,
1532 p_load_seq_number),
1533 l_dpw_assigned_flag,
1534 D.DEMAND_SOURCE_DELIVERY,
1535 D.DEMAND_CLASS,
1536 D.DEMAND_ID,
1537 D.LOCATOR_ID,
1538 D.LOT_NUMBER,
1539 p_picking_line_id,
1540 D.LINE_ITEM_QUANTITY,
1541 p_reservable_flag,
1542 D.REVISION,
1543 D.REQUIREMENT_DATE,
1544 p_schedule_level,
1545 'DEMANDED',
1546 nvl(D.SUBINVENTORY, decode(p_reservable_flag, 'N', default_subinventory, NULL)),
1547 D.SUPPLY_SOURCE_HEADER_ID,
1548 D.SUPPLY_SOURCE_TYPE,
1549 p_transactable_flag,
1550 D.ORGANIZATION_ID,
1551 'NEW'
1552 FROM MTL_DEMAND D
1553 WHERE D.DEMAND_SOURCE_HEADER_ID = p_ccid
1554 AND D.DEMAND_SOURCE_TYPE IN (2,8)
1555 AND D.DEMAND_SOURCE_LINE = to_char(p_order_line_id)
1556 AND D.DEMAND_SOURCE_DELIVERY = to_char(p_delivery)
1557 AND D.AUTODETAIL_GROUP_ID IS NULL
1558 AND NVL(D.LINE_ITEM_QUANTITY,0) <> 0
1559 AND D.PARENT_DEMAND_ID IS NOT NULL
1560 AND nvl(D.SUBINVENTORY, -99) = DECODE(g_use_sub_ps, 'Y', nvl(p_subinventory, -99), nvl(D.SUBINVENTORY, -99));
1561
1562 rows_inserted := SQL%ROWCOUNT;
1563
1564 ELSIF p_mode = BACKORDER THEN
1565 INSERT INTO SO_PICKING_LINE_DETAILS (
1566 PICKING_LINE_DETAIL_ID,
1567 CREATED_BY,
1568 CREATION_DATE,
1569 LAST_UPDATED_BY,
1570 LAST_UPDATE_DATE,
1571 LAST_UPDATE_LOGIN,
1572 PROGRAM_APPLICATION_ID,
1573 PROGRAM_ID,
1574 PROGRAM_UPDATE_DATE,
1575 REQUEST_ID,
1576 DPW_ASSIGNED_FLAG,
1577 RELEASED_FLAG,
1578 DELIVERY,
1579 DEMAND_CLASS_CODE,
1580 PICKING_LINE_ID,
1581 REQUESTED_QUANTITY,
1582 RESERVABLE_FLAG,
1583 SCHEDULE_DATE,
1584 SCHEDULE_LEVEL,
1585 SCHEDULE_STATUS_CODE,
1586 SUBINVENTORY,
1587 TRANSACTABLE_FLAG,
1588 WAREHOUSE_ID,
1589 MVT_STAT_STATUS)
1590 VALUES (
1591 SO_PICKING_LINE_DETAILS_S.NEXTVAL,
1592 g_user_id,
1593 SYSDATE,
1594 g_user_id,
1595 SYSDATE,
1596 g_login_id,
1597 g_application_id,
1598 g_program_id,
1599 SYSDATE,
1600 g_request_id,
1601 'N',
1602 'N',
1603 p_delivery,
1604 p_demand_class,
1605 p_picking_line_id,
1606 p_requested_quantity,
1607 p_reservable_flag,
1608 p_schedule_date,
1609 p_schedule_level,
1610 'DEMANDED',
1611 p_subinventory,
1612 p_transactable_flag,
1613 p_warehouse_id,
1614 'NEW');
1615
1616 rows_inserted := SQL%ROWCOUNT;
1617
1618 ELSE
1619 WSH_UTIL.Write_Log('Invalid picking line detail insertion mode');
1620 RETURN FAILURE;
1621 END IF;
1622
1623 WSH_UTIL.Write_Log('Inserted ' || to_char(rows_inserted) ||
1624 ' picking_line_details for picking_line '|| to_char(p_picking_line_id));
1625
1626 IF rows_inserted = 0 THEN
1627 RETURN FAILURE;
1628 ELSE
1629 RETURN rows_inserted;
1630 END IF;
1631
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 WSH_UTIL.Default_Handler('WSH_PR_PICKING_OBJECTS.Insert_Picking_Line_Details',
1635 to_char(p_mode));
1636 RETURN FAILURE;
1637
1638 END Insert_Picking_Line_Details;
1639
1640
1641 --
1642 -- Name
1643 -- FUNCTION Insert_Order_Line_Detail
1644 --
1645 -- Purpose
1646 -- This function inserts an order line detail for the
1647 -- remaining quantity.
1648 --
1649 -- Return Values
1650 -- -1 => Failure
1651 -- 0 => Success
1652 --
1653
1654 FUNCTION Insert_Order_Line_Detail (
1655 p_original_line_detail_id IN BINARY_INTEGER,
1656 p_new_line_detail_id IN OUT BINARY_INTEGER,
1657 p_quantity IN BINARY_INTEGER,
1658 p_new_delivery IN BINARY_INTEGER
1659 ) RETURN BINARY_INTEGER IS
1660
1661 rows_inserted BINARY_INTEGER;
1662
1663 BEGIN
1664 WSH_UTIL.Write_Log('--------------------');
1665 WSH_UTIL.Write_Log('Inserting new order line detail');
1666
1667 SELECT SO_LINE_DETAILS_S.NEXTVAL
1668 INTO p_new_line_detail_id
1669 FROM DUAL;
1670
1671 INSERT INTO SO_LINE_DETAILS (
1672 LINE_DETAIL_ID,
1673 CREATION_DATE,
1674 CREATED_BY,
1675 LAST_UPDATE_DATE,
1676 LAST_UPDATED_BY,
1677 LAST_UPDATE_LOGIN,
1678 LINE_ID,
1679 INVENTORY_ITEM_ID,
1680 INCLUDED_ITEM_FLAG,
1681 COMPONENT_SEQUENCE_ID,
1682 COMPONENT_CODE,
1683 COMPONENT_RATIO,
1684 SHIPPABLE_FLAG,
1685 TRANSACTABLE_FLAG,
1686 RESERVABLE_FLAG,
1687 UNIT_CODE,
1688 RELEASED_FLAG,
1689 REQUIRED_FOR_REVENUE_FLAG,
1690 QUANTITY,
1691 WAREHOUSE_ID,
1692 DEMAND_CLASS_CODE,
1693 SCHEDULE_DATE,
1694 REVISION,
1695 LOT_NUMBER,
1696 SUBINVENTORY,
1697 CUSTOMER_REQUESTED_LOT_FLAG,
1698 SCHEDULE_STATUS_CODE,
1699 SCHEDULE_LEVEL_CODE,
1700 QUANTITY_SVRID,
1701 WAREHOUSE_SVRID,
1702 DEMAND_CLASS_SVRID,
1703 DATE_SVRID,
1704 REVISION_SVRID,
1705 LOT_SVRID,
1706 SUBINVENTORY_SVRID,
1707 CUSTOMER_REQUESTED_SVRID,
1708 DF_SVRID,
1709 CONTEXT,
1710 ATTRIBUTE1,
1711 ATTRIBUTE2,
1712 ATTRIBUTE3,
1713 ATTRIBUTE4,
1714 ATTRIBUTE5,
1715 ATTRIBUTE6,
1716 ATTRIBUTE7,
1717 ATTRIBUTE8,
1718 ATTRIBUTE9,
1719 ATTRIBUTE10,
1720 ATTRIBUTE11,
1721 ATTRIBUTE12,
1722 ATTRIBUTE13,
1723 ATTRIBUTE14,
1724 ATTRIBUTE15,
1725 DELIVERY,
1726 WIP_RESERVED_QUANTITY,
1727 WIP_COMPLETED_QUANTITY,
1728 SUPPLY_SOURCE_TYPE,
1729 SUPPLY_SOURCE_HEADER_ID,
1730 DEPARTURE_ID,
1731 DELIVERY_ID,
1732 DPW_ASSIGNED_FLAG,
1733 UPDATE_FLAG,
1734 INVENTORY_LOCATION_ID,
1735 CONFIGURATION_ITEM_FLAG,
1736 LATEST_ACCEPTABLE_DATE,
1737 LATEST_ACCEPTABLE_DATE_SVRID,
1738 DEP_PLAN_REQUIRED_FLAG,
1739 CUSTOMER_ITEM_ID,
1740 LOAD_SEQ_NUMBER
1741 )
1742 SELECT p_new_line_detail_id,
1743 SYSDATE,
1744 g_user_id,
1745 SYSDATE,
1746 g_user_id,
1747 g_login_id,
1748 LINE_ID,
1749 INVENTORY_ITEM_ID,
1750 INCLUDED_ITEM_FLAG,
1751 COMPONENT_SEQUENCE_ID,
1752 COMPONENT_CODE,
1753 COMPONENT_RATIO,
1754 SHIPPABLE_FLAG,
1755 TRANSACTABLE_FLAG,
1756 RESERVABLE_FLAG,
1757 UNIT_CODE,
1758 'N',
1759 REQUIRED_FOR_REVENUE_FLAG,
1760 p_quantity,
1761 WAREHOUSE_ID,
1762 DEMAND_CLASS_CODE,
1763 SCHEDULE_DATE,
1764 REVISION,
1765 LOT_NUMBER,
1766 SUBINVENTORY,
1767 CUSTOMER_REQUESTED_LOT_FLAG,
1768 SCHEDULE_STATUS_CODE,
1769 SCHEDULE_LEVEL_CODE,
1770 QUANTITY_SVRID,
1771 WAREHOUSE_SVRID,
1772 DEMAND_CLASS_SVRID,
1773 DATE_SVRID,
1774 REVISION_SVRID,
1775 LOT_SVRID,
1776 SUBINVENTORY_SVRID,
1777 CUSTOMER_REQUESTED_SVRID,
1778 DF_SVRID,
1779 CONTEXT,
1780 ATTRIBUTE1,
1781 ATTRIBUTE2,
1782 ATTRIBUTE3,
1783 ATTRIBUTE4,
1784 ATTRIBUTE5,
1785 ATTRIBUTE6,
1786 ATTRIBUTE7,
1787 ATTRIBUTE8,
1788 ATTRIBUTE9,
1789 ATTRIBUTE10,
1790 ATTRIBUTE11,
1791 ATTRIBUTE12,
1792 ATTRIBUTE13,
1793 ATTRIBUTE14,
1794 ATTRIBUTE15,
1795 p_new_delivery,
1796 WIP_RESERVED_QUANTITY,
1797 WIP_COMPLETED_QUANTITY,
1798 SUPPLY_SOURCE_TYPE,
1799 SUPPLY_SOURCE_HEADER_ID,
1800 DEPARTURE_ID,
1801 DELIVERY_ID,
1802 DPW_ASSIGNED_FLAG,
1803 UPDATE_FLAG,
1804 INVENTORY_LOCATION_ID,
1805 CONFIGURATION_ITEM_FLAG,
1806 LATEST_ACCEPTABLE_DATE,
1807 LATEST_ACCEPTABLE_DATE_SVRID,
1808 DEP_PLAN_REQUIRED_FLAG,
1809 CUSTOMER_ITEM_ID,
1810 LOAD_SEQ_NUMBER
1811 FROM SO_LINE_DETAILS
1812 WHERE line_detail_id = p_original_line_detail_id;
1813
1814 rows_inserted := SQL%ROWCOUNT;
1815
1816 WSH_UTIL.Write_Log('Inserted ' || to_char(rows_inserted) ||
1817 ' order_line_detail = ' || to_char(p_new_line_detail_id));
1818
1819 RETURN rows_inserted;
1820
1821 EXCEPTION
1822 WHEN OTHERS THEN
1823 WSH_UTIL.Default_Handler('WSH_PR_PICKING_OBJECTS.Insert_Order_Line_Detail');
1824 RETURN FAILURE;
1825
1826 END Insert_Order_Line_Detail;
1827
1828
1829 --
1830 -- Name
1831 -- FUNCTION Process_Key
1832 --
1833 -- Purpose
1834 -- This function returns the picking_header_id, or
1835 -- the pick slip number to be used for the picking
1836 -- headers and picking lines.
1837 --
1838 -- Return Values
1839 -- Pick Slip Number or Picking Header ID based on mode.
1840 --
1841
1842 FUNCTION Process_Key (
1843 p_mode IN VARCHAR2,
1844 p_header_id IN BINARY_INTEGER,
1845 p_customer_id IN BINARY_INTEGER,
1846 p_ship_method_code IN VARCHAR2,
1847 p_ship_to_site_use_id IN BINARY_INTEGER,
1848 p_shipment_priority IN VARCHAR2,
1849 p_subinventory IN VARCHAR2,
1850 p_departure_id IN BINARY_INTEGER,
1851 p_delivery_id IN OUT BINARY_INTEGER,
1852 p_warehouse_id IN BINARY_INTEGER,
1853 new_flag IN OUT VARCHAR2
1854 )
1855 RETURN BINARY_INTEGER IS
1856
1857 key VARCHAR2(200);
1858 x_value BINARY_INTEGER;
1859 found BOOLEAN;
1860 tab_size BINARY_INTEGER;
1861 i BINARY_INTEGER;
1862
1863 BEGIN
1864 found := FALSE;
1865 new_flag := 'N';
1866 IF p_mode = 'PICK_SLIP_NUMBER' THEN
1867 -- Construct Key
1868 key := 'r';
1869 IF (g_use_order_ps = 'Y') THEN
1870 key := key || to_char(p_header_id);
1871 END IF;
1872 IF (g_use_sub_ps = 'Y') THEN
1873 key := key || p_subinventory;
1874 END IF;
1875 IF (g_use_customer_ps = 'Y') THEN
1876 key := key || to_char(p_customer_id);
1877 END IF;
1878 IF (g_use_ship_to_ps = 'Y') THEN
1879 key := key || to_char(p_ship_to_site_use_id);
1880 END IF;
1881 IF (g_use_carrier_ps = 'Y') THEN
1882 key := key || p_ship_method_code;
1883 END IF;
1884 IF (g_use_ship_priority_ps = 'Y') THEN
1885 key := key || p_shipment_priority;
1886 END IF;
1887 IF (g_use_departure_ps = 'Y') THEN
1888 key := key || to_char(p_departure_id);
1889 END IF;
1890 IF (g_use_delivery_ps = 'Y') THEN
1891 key := key || to_char(p_delivery_id);
1892 END IF;
1893 -- Implicitly use warehouse
1894 key := key || to_char(p_warehouse_id);
1895
1896 -- Find key in table
1897 FOR i IN 1..g_ps_table.count LOOP
1898 IF g_ps_table(i).key = key THEN
1899 x_value := g_ps_table(i).value;
1900 found := TRUE;
1901 EXIT;
1902 END IF;
1903 END LOOP;
1904
1905 IF found THEN
1906 RETURN x_value;
1907 ELSE
1908 SELECT SO_PICKING_HEADERS_S.NEXTVAL
1909 INTO x_value
1910 FROM DUAL;
1911 tab_size := g_ps_table.count;
1912 g_ps_table(tab_size+1).key := key;
1913 g_ps_table(tab_size+1).value := x_value;
1914 new_flag := 'Y';
1915 RETURN x_value;
1916 END IF;
1917
1918 ELSIF p_mode = 'PICKING_HEADER_ID' THEN
1919 -- Construct Key
1920 key := 'r' || to_char(p_header_id) || to_char(p_warehouse_id) ||
1921 to_char(p_ship_to_site_use_id) || p_shipment_priority ||
1922 p_ship_method_code;
1923
1924 -- Find key in table
1925 FOR i IN 1..g_ph_table.count LOOP
1926 IF g_ph_table(i).key = key THEN
1927 x_value := g_ph_table(i).value;
1928 found := TRUE;
1929 EXIT;
1930 END IF;
1931 END LOOP;
1932
1933 IF found THEN
1934 RETURN x_value;
1935 ELSE
1936 SELECT SO_PICKING_HEADERS_S.NEXTVAL
1937 INTO x_value
1938 FROM DUAL;
1939 tab_size := g_ph_table.count;
1940 g_ph_table(tab_size+1).key := key;
1941 g_ph_table(tab_size+1).value := x_value;
1942 new_flag := 'Y';
1943 RETURN x_value;
1944 END IF;
1945
1946 END IF;
1947
1948 EXCEPTION
1949 WHEN OTHERS THEN
1950 WSH_UTIL.Default_Handler('WSH_PR_PICKING_OBJECTS.Process_Key');
1951 RETURN FAILURE;
1952
1953 END Process_Key;
1954
1955 END WSH_PR_PICKING_OBJECTS;