[Home] [Help]
PACKAGE BODY: APPS.WSH_PR_PICKING_SESSION
Source
1 PACKAGE BODY WSH_PR_PICKING_SESSION AS
2 /* $Header: WSHPRPSB.pls 115.23 99/08/18 17:46:23 porting ship $ */
3
4 --
5 -- Package
6 -- WSH_PR_PICKING_SESSION
7 --
8 -- Purpose
9 -- This package does the following:
10 -- - Maintain all the session variables for Pick Release
11 -- - Construct SQL statements to fetch backordered picking
12 -- line details, unreleased line details, non-shippable
13 -- lines satisfying the release criteria.
14 -- - Provides API to retrieve session values
15 --
16
17 --
18 -- PACKAGE TYPES
19 --
20
21 TYPE rsrTyp IS RECORD (
22 attribute BINARY_INTEGER,
23 attribute_name VARCHAR2(30),
24 priority BINARY_INTEGER,
25 sort_order VARCHAR2(4)
26 );
27
28 TYPE rsrTabTyp IS TABLE OF rsrTyp INDEX BY BINARY_INTEGER;
29
30 --
31 -- PACKAGE CONSTANTS
32 --
33
34 -- Reflect status of calls to functions and procedures
35 SUCCESS CONSTANT BINARY_INTEGER := 0;
36 FAILURE CONSTANT BINARY_INTEGER := -1;
37
38 -- Indicates the pick slip printing mode
39 IMMEDIATE_PRINT_PS CONSTANT BINARY_INTEGER := 2;
40 DEFERRED_PRINT_PS CONSTANT BINARY_INTEGER := 3;
41
42 -- Indicates what attributes are used in Release Sequence
43 -- Rules
44 C_INVOICE_VALUE CONSTANT BINARY_INTEGER := 1;
45 C_ORDER_NUMBER CONSTANT BINARY_INTEGER := 2;
46 C_SCHEDULE_DATE CONSTANT BINARY_INTEGER := 3;
47 C_DEPARTURE CONSTANT BINARY_INTEGER := 4;
48 C_SHIPMENT_PRIORITY CONSTANT BINARY_INTEGER := 5;
49
50 --
51 -- PACKAGE VARIABLES
52 --
53
54 initialized BOOLEAN := FALSE;
55 ordered_rsr rsrTabTyp;
56 total_release_criteria BINARY_INTEGER;
57 unreleased_SQL VARCHAR2(10000) := NULL;
58 backordered_SQL VARCHAR2(10000) := NULL;
59 non_ship_SQL VARCHAR2(10000) := NULL;
60 sync_SQL VARCHAR2(10000) := NULL;
61 sreq_SQL VARCHAR2(10000) := NULL;
62 orderby_SQL VARCHAR2(500) := NULL;
63 invoice_value_flag VARCHAR2(1) := 'N';
64 print_ps_mode_param VARCHAR2(1);
65 print_ps_mode BINARY_INTEGER;
66 use_order_header BOOLEAN := FALSE;
67 error_message VARCHAR2(240);
68
69 --
70 -- PUBLIC FUNCTIONS/PROCEDURES
71 --
72
73 --
74 -- FORWARD DECLERATIONS
75 --
76 PROCEDURE Process_Buffer(
77 p_buffer_name IN VARCHAR2,
78 p_buffer_text IN VARCHAR2
79 );
80
81
82 --
83 -- Name
84 -- FUNCTION Init
85 --
86 -- Purpose
87 -- This function initializes variables for the session:
88 -- - Retrieves criteria for the batch and sets up session
89 -- variables
90 -- - Locks row for the batch
91 -- - Update who columns for the batch
92 --
93 -- Arguments
94 -- p_batch_id - batch to be processed
95 -- p_reservations - reservations flag
96 --
97 -- Return Values
98 -- -1 => Failure
99 -- 0 => Success
100 --
101 -- Notes
102 --
103
104 FUNCTION Init(
105 p_batch_id IN BINARY_INTEGER,
106 p_request_id IN BINARY_INTEGER,
107 p_application_id IN BINARY_INTEGER,
108 p_program_id IN BINARY_INTEGER,
109 p_user_id IN BINARY_INTEGER,
110 p_login_id IN BINARY_INTEGER,
111 p_reservations IN BINARY_INTEGER,
112 p_backorders_flag IN OUT VARCHAR2,
113 p_header_id IN OUT BINARY_INTEGER,
114 p_ship_set_number IN OUT BINARY_INTEGER,
115 p_order_type_id IN OUT BINARY_INTEGER,
116 p_warehouse_id IN OUT BINARY_INTEGER,
117 p_customer_id IN OUT BINARY_INTEGER,
118 p_ship_site_use_id IN OUT BINARY_INTEGER,
119 p_shipment_priority IN OUT VARCHAR2,
120 p_ship_method_code IN OUT VARCHAR2,
121 p_from_request_date IN OUT VARCHAR2,
122 p_to_request_date IN OUT VARCHAR2,
123 p_from_sched_ship_date IN OUT VARCHAR2,
124 p_to_sched_ship_date IN OUT VARCHAR2,
125 p_existing_rsvs_only_flag IN OUT VARCHAR2,
126 p_subinventory IN OUT VARCHAR2,
127 p_inventory_item_id IN OUT BINARY_INTEGER,
128 p_departure_id IN OUT BINARY_INTEGER,
129 p_delivery_id IN OUT BINARY_INTEGER,
130 p_pick_slip_rule_id IN OUT BINARY_INTEGER,
131 p_release_seq_rule_id IN OUT BINARY_INTEGER,
132 p_report_set_id IN OUT BINARY_INTEGER,
133 p_include_planned_lines IN OUT VARCHAR2,
134 p_partial_allowed_flag IN OUT VARCHAR2,
135 p_print_ps_mode IN OUT BINARY_INTEGER
136 )
137 RETURN BINARY_INTEGER IS
138
139 -- cursor to get batch parameter information
140 /* Note: print_flag indicates the report_set_id */
141 CURSOR get_lock_batch(x_batch_id IN BINARY_INTEGER) IS
142 SELECT NAME,
143 BACKORDERS_ONLY_FLAG,
144 NVL(RELEASE_SEQ_RULE_ID, -1),
145 NVL(PICK_SLIP_RULE_ID, -1),
146 NVL(PARTIAL_ALLOWED_FLAG, 'N'),
147 NVL(INCLUDE_PLANNED_LINES, 'N'),
148 NVL(CUSTOMER_ID, 0),
149 NVL(DATE_REQUESTED_FROM, NULL),
150 NVL(DATE_REQUESTED_TO, NULL),
151 NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
152 NVL(HEADER_ID, 0),
153 NVL(INVENTORY_ITEM_ID, 0),
154 NVL(DEPARTURE_ID, 0),
155 NVL(DELIVERY_ID, 0),
156 NVL(ORDER_TYPE_ID, 0),
157 NVL(SCHEDULED_SHIPMENT_DATE_FROM, NULL),
158 NVL(SCHEDULED_SHIPMENT_DATE_TO, NULL),
159 NVL(SHIPMENT_PRIORITY_CODE, ''),
160 NVL(SHIP_METHOD_CODE, ''),
161 NVL(SHIP_SET_NUMBER, 0),
162 NVL(SITE_USE_ID, 0),
163 NVL(SUBINVENTORY, ''),
164 NVL(WAREHOUSE_ID, -1),
165 NVL(ORG_ID, -3114),
166 NVL(AUTOCREATE_DELIVERY_FLAG,'N'),
167 NVL(ORDER_LINE_ID, 0),
168 TO_NUMBER(NVL(PRINT_FLAG, '-1'))
169 FROM SO_PICKING_BATCHES_ALL
170 WHERE BATCH_ID = x_batch_id
171 FOR UPDATE OF BATCH_ID NOWAIT;
172
173 -- cursor to fetch release sequence rule info
174 CURSOR rel_seq_rule(x_rsr_id IN BINARY_INTEGER) IS
175 SELECT NAME,
176 NVL(ORDER_ID_PRIORITY, -1),
177 DECODE(ORDER_ID_SORT, 'A', 'ASC', 'D', 'DESC', ''),
178 NVL(INVOICE_VALUE_PRIORITY, -1),
179 DECODE(INVOICE_VALUE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
180 NVL(SCHEDULE_DATE_PRIORITY, -1),
181 DECODE(SCHEDULE_DATE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
182 NVL(SHIPMENT_PRI_PRIORITY, -1),
183 DECODE(SHIPMENT_PRI_SORT, 'A', 'ASC', 'D', 'DESC', ''),
184 NVL(DEPARTURE_PRIORITY, -1),
185 DECODE(DEPARTURE_SORT, 'A', 'ASC', 'D', 'DESC', '')
186 FROM WSH_RELEASE_SEQ_RULES
187 WHERE RELEASE_SEQ_RULE_ID = x_rsr_id
188 AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
189 NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
190
191 -- cursor to determine if pick slip rule contains order number
192 CURSOR order_ps_group(x_psr_id IN BINARY_INTEGER) IS
196 AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
193 SELECT NVL(ORDER_NUMBER_FLAG,'N')
194 FROM WSH_PICK_SLIP_RULES
195 WHERE PICK_SLIP_RULE_ID = x_psr_id
197 NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
198
199 -- cursor to determine print pick slip mode parameter
200 CURSOR ps_mode_param(x_org_id IN BINARY_INTEGER) IS
201 SELECT NVL(PRINT_PICK_SLIP_MODE, 'E')
202 FROM WSH_PARAMETERS
203 WHERE ORGANIZATION_ID = x_org_id;
204
205 -- cursor to get line information
206 CURSOR get_line_info(x_line_id In BINARY_INTEGER) IS
207 SELECT HEADER_ID, NVL(PARENT_LINE_ID, -1)
208 FROM SO_LINES_ALL
209 WHERE LINE_ID = x_line_id;
210
211 record_locked EXCEPTION;
212 PRAGMA EXCEPTION_INIT(record_locked, -54);
213
214 use_order_ps VARCHAR2(1);
215 invoice_value_priority BINARY_INTEGER;
216 order_number_priority BINARY_INTEGER;
217 schedule_date_priority BINARY_INTEGER;
218 departure_priority BINARY_INTEGER;
219 shipment_pri_priority BINARY_INTEGER;
220 invoice_value_sort VARCHAR2(4);
221 order_number_sort VARCHAR2(4);
222 schedule_date_sort VARCHAR2(4);
223 departure_sort VARCHAR2(4);
224 shipment_pri_sort VARCHAR2(4);
225 v_header_id BINARY_INTEGER;
226 v_parent_line_id BINARY_INTEGER;
227 i BINARY_INTEGER;
228 j BINARY_INTEGER;
229 temp_rsr rsrTyp;
230 cs BINARY_INTEGER;
231
232 BEGIN
233
234 WSH_UTIL.Write_Log('Starting WSH_PR_PICKING_SESSION.Init');
235
236 IF initialized = TRUE THEN
237 RETURN SUCCESS;
238 END IF;
239
240 -- initialize the WHO session variables
241 request_id := p_request_id;
242 WSH_UTIL.Write_Log('request_id = ' || to_char(request_id));
243 application_id := p_application_id;
244 WSH_UTIL.Write_Log('application_id = ' || to_char(application_id));
245 program_id := p_program_id;
246 WSH_UTIL.Write_Log('program_id = ' || to_char(program_id));
247 user_id := p_user_id;
248 WSH_UTIL.Write_Log('user_id = ' || to_char(user_id));
249 login_id := p_login_id;
250 WSH_UTIL.Write_Log('login_id = ' || to_char(login_id));
251
252
253 -- initialize other session variables
254 batch_id := p_batch_id;
255 IF p_reservations = 1 THEN
256 reservations := 'Y';
257 ELSE
258 reservations := 'N';
259 END IF;
260
264 OPEN get_lock_batch(p_batch_id);
261 WSH_UTIL.Write_Log('Fetching release criteria for batch');
262
263 -- fetch release criteria for the batch and lock row
265 FETCH get_lock_batch
266 INTO batch_name,
267 backorders_flag,
268 release_seq_rule_id,
269 pick_slip_rule_id,
270 partial_allowed_flag,
271 include_planned_lines,
272 customer_id,
273 from_request_date,
274 to_request_date,
275 existing_rsvs_only_flag,
276 header_id,
277 inventory_item_id,
278 departure_id,
279 delivery_id,
280 order_type_id,
281 from_sched_ship_date,
282 to_sched_ship_date,
283 shipment_priority,
284 ship_method_code,
285 ship_set_number,
286 ship_site_use_id,
287 subinventory,
288 warehouse_id,
289 org_id,
290 autocreate_deliveries,
291 order_line_id,
292 report_set_id;
293
294 -- handle batch does not exist condition
295 IF get_lock_batch%NOTFOUND THEN
296 WSH_UTIL.Write_Log('Batch ID ' || to_char(p_batch_id) || ' does not exist.');
297 RETURN FAILURE;
298 END IF;
299
300 IF get_lock_batch%ISOPEN THEN
301 CLOSE get_lock_batch;
302 END IF;
303
304 WSH_UTIL.Write_Log('Pick Release parameters are...');
305
309
306 -- set all the out variables
307 p_backorders_flag := backorders_flag;
308 WSH_UTIL.Write_Log('backorders_flag = ' || p_backorders_flag);
310 p_header_id := header_id;
311 WSH_UTIL.Write_Log('header_id = ' || to_char(p_header_id));
312
313 p_ship_set_number := ship_set_number;
314 WSH_UTIL.Write_Log('ship_set_number = ' || to_char(p_ship_set_number));
315
316 p_order_type_id := order_type_id;
317 WSH_UTIL.Write_Log('order_type_id = ' || to_char(p_order_type_id));
318
319 p_warehouse_id := warehouse_id;
320 WSH_UTIL.Write_Log('warehouse_id = ' || to_char(p_warehouse_id));
321
322 p_customer_id := customer_id;
323 WSH_UTIL.Write_Log('customer_id = ' || to_char(p_customer_id));
324
325 p_ship_site_use_id := ship_site_use_id;
326 WSH_UTIL.Write_Log('ship_site_use_id = ' || to_char(p_ship_site_use_id));
327
328 p_shipment_priority := shipment_priority;
329 WSH_UTIL.Write_Log('shipment_priority = ' || p_shipment_priority);
330
331 p_ship_method_code := ship_method_code;
332 WSH_UTIL.Write_Log('ship_method_code = ' || p_ship_method_code);
333
334 p_from_request_date := to_char(from_request_date, 'YYYY/MM/DD HH24:MI:SS');
335 WSH_UTIL.Write_Log('from_request_date = ' || p_from_request_date);
336
337 p_to_request_date := to_char(to_request_date, 'YYYY/MM/DD HH24:MI:SS');
338 WSH_UTIL.Write_Log('to_request_date = ' || p_to_request_date);
339
340 p_from_sched_ship_date := to_char(from_sched_ship_date, 'YYYY/MM/DD HH24:MI:SS');
341 WSH_UTIL.Write_Log('from_sched_ship_date = ' || p_from_sched_ship_date);
342
343 p_to_sched_ship_date := to_char(to_sched_ship_date, 'YYYY/MM/DD HH24:MI:SS');
347 WSH_UTIL.Write_Log('existing_rsvs_only_flag = ' || p_existing_rsvs_only_flag);
344 WSH_UTIL.Write_Log('to_sched_ship_date = ' || p_to_sched_ship_date);
345
346 p_existing_rsvs_only_flag := existing_rsvs_only_flag;
348
349 p_subinventory := subinventory;
350 WSH_UTIL.Write_Log('subinventory = ' || p_subinventory);
351
352 p_inventory_item_id := inventory_item_id;
353 WSH_UTIL.Write_Log('inventory_item_id = ' || to_char(p_inventory_item_id));
354
355 p_departure_id := departure_id;
356 WSH_UTIL.Write_Log('departure_id = ' || to_char(p_departure_id));
357
358 p_delivery_id := delivery_id;
359 WSH_UTIL.Write_Log('delivery_id = ' || to_char(p_delivery_id));
360
361 p_pick_slip_rule_id := pick_slip_rule_id;
362 WSH_UTIL.Write_Log('pick_slip_rule_id = ' || to_char(p_pick_slip_rule_id));
363
364 p_release_seq_rule_id := release_seq_rule_id;
365 WSH_UTIL.Write_Log('release_seq_rule_id = ' || to_char(p_release_seq_rule_id));
366
367 p_report_set_id := report_set_id;
368 WSH_UTIL.Write_Log('report_set_id = ' || to_char(p_report_set_id));
369
370 p_include_planned_lines := include_planned_lines;
371 WSH_UTIL.Write_Log('include_planned_lines = ' || p_include_planned_lines);
372
373 p_partial_allowed_flag := partial_allowed_flag;
374 WSH_UTIL.Write_Log('partial_allowed_flag = ' || p_partial_allowed_flag);
375
376 WSH_UTIL.Write_Log('autocreate_delivery_flag = ' || autocreate_deliveries);
377
378 WSH_UTIL.Write_Log('order_line_id = ' || order_line_id);
379
380 --
381 -- Validating order_line_id
382 --
383
384 IF order_line_id <> 0 THEN
385
386 OPEN get_line_info(order_line_id);
387 FETCH get_line_info
388 INTO v_header_id,
389 v_parent_line_id;
390
391 IF get_line_info%NOTFOUND THEN
392 WSH_UTIL.Write_Log('Order Line ID ' || to_char(order_line_id) || 'does not exist');
393 RETURN FAILURE;
394 END IF;
395
396 IF v_header_id <> header_id THEN
397 WSH_UTIL.Write_Log('Order Line ID ' || to_char(order_line_id) || 'does not belong to');
398 WSH_UTIL.Write_Log('Order Header ID ' || to_char(header_id));
399 RETURN FAILURE;
400 END IF;
401
402 IF v_parent_line_id <> -1 THEN
403 WSH_UTIL.Write_Log('Order Line ID ' || to_char(order_line_id) || 'is not a top model line');
404 RETURN FAILURE;
405 END IF;
406
407 IF get_line_info%ISOPEN THEN
408 CLOSE get_line_info;
409 END IF;
410
411 END IF;
412
413 --
414 -- If warehouse id is NULL (-1), must error out here since Pick Release
415 -- is warehouse specific.
416 --
420 RETURN FAILURE;
417 IF p_warehouse_id = -1 THEN
418 WSH_UTIL.Write_Log('Warehouse is not available for this batch.');
419 WSH_UTIL.Write_Log('Cannot release batch.');
421 END IF;
422
423 WSH_UTIL.Write_Log('Fetching release sequence rule information for the batch');
424
425 -- fetch release sequence rule parameters
426 OPEN rel_seq_rule(release_seq_rule_id);
427 FETCH rel_seq_rule
428 INTO release_seq_rule_name,
429 order_number_priority,
430 order_number_sort,
431 invoice_value_priority,
432 invoice_value_sort,
433 schedule_date_priority,
434 schedule_date_sort,
435 shipment_pri_priority,
436 shipment_pri_sort,
437 departure_priority,
438 departure_sort;
439
440 -- handle release sequence rule does not exist
441 IF rel_seq_rule%NOTFOUND THEN
442 WSH_UTIL.Write_Log('Release sequence rule ID ' || to_char(release_seq_rule_id) || ' does not exist.');
443 RETURN FAILURE;
444 END IF;
445 IF rel_seq_rule%ISOPEN THEN
446 CLOSE rel_seq_rule;
447 END IF;
448
449 -- initialize the release sequence rule parameters
450 i := 1;
451 IF (invoice_value_priority <> -1) THEN
452 use_order_header := TRUE;
453 ordered_rsr(i).attribute := C_INVOICE_VALUE;
454 ordered_rsr(i).attribute_name := 'INVOICE_VALUE';
455 -- initialize the invoice_value_flag to be used as part
456 -- of building the select statement
457 invoice_value_flag := 'Y';
458 ordered_rsr(i).priority := invoice_value_priority;
459 ordered_rsr(i).sort_order := invoice_value_sort;
460 i := i + 1;
461 END IF;
462 IF (order_number_priority <> -1) THEN
463 use_order_header := TRUE;
464 ordered_rsr(i).attribute := C_ORDER_NUMBER;
465 ordered_rsr(i).attribute_name := 'ORDER_NUMBER';
466 ordered_rsr(i).priority := order_number_priority;
467 ordered_rsr(i).sort_order := order_number_sort;
468 i := i + 1;
469 END IF;
470 IF (schedule_date_priority <> -1) THEN
471 ordered_rsr(i).attribute := C_SCHEDULE_DATE;
472 ordered_rsr(i).attribute_name := 'SCHEDULE_DATE';
473 ordered_rsr(i).priority := schedule_date_priority;
474 ordered_rsr(i).sort_order := schedule_date_sort;
475 i := i + 1;
476 END IF;
477 IF (departure_priority <> -1) THEN
481 ordered_rsr(i).sort_order := departure_sort;
478 ordered_rsr(i).attribute := C_DEPARTURE;
479 ordered_rsr(i).attribute_name := 'DEPARTURE';
480 ordered_rsr(i).priority := departure_priority;
482 i := i + 1;
483 END IF;
484 IF (shipment_pri_priority <> -1) THEN
485 ordered_rsr(i).attribute := C_SHIPMENT_PRIORITY;
486 ordered_rsr(i).attribute_name := 'SHIPMENT_PRIORITY';
487 ordered_rsr(i).priority := shipment_pri_priority;
488 ordered_rsr(i).sort_order := shipment_pri_sort;
489 i := i + 1;
490 END IF;
491 total_release_criteria := i - 1;
492
493 -- sort the table for release sequence rule according to priority
494 FOR i IN 1..total_release_criteria LOOP
495 FOR j IN i+1..total_release_criteria LOOP
496 IF (ordered_rsr(j).priority < ordered_rsr(i).priority) THEN
497 temp_rsr := ordered_rsr(j);
498 ordered_rsr(j) := ordered_rsr(i);
499 ordered_rsr(i) := temp_rsr;
500 END IF;
501 END LOOP;
502 END LOOP;
503
504 -- determine the most significant release sequence rule attribute
505 primary_rsr := ordered_rsr(1).attribute_name;
506 WSH_UTIL.Write_Log('Primary release rule is ' || primary_rsr);
507
508 -- print release sequence rule information for debugging purposes
509 FOR i IN 1..total_release_criteria LOOP
510 WSH_UTIL.Write_Log('attribute = ' || ordered_rsr(i).attribute_name || ' ' ||
511 'priority = ' || to_char(ordered_rsr(i).priority) || ' ' ||
512 'sort = ' || ordered_rsr(i).sort_order );
513 END LOOP;
514
515 WSH_UTIL.Write_Log('Determining if order number is in grouping rule...');
516 OPEN order_ps_group(pick_slip_rule_id);
517 FETCH order_ps_group
518 INTO use_order_ps;
519 IF order_ps_group%NOTFOUND THEN
520 use_order_ps := 'N';
521 END IF;
522 IF order_ps_group%ISOPEN THEN
523 CLOSE order_ps_group;
524 END IF;
525
526 WSH_UTIL.Write_Log('Determining print pick slip parameter...');
527
528 -- Use warehouse_id and not org_id, as the Shipping Parameters table
529 -- is Warehouse (i.e. Organization ID) specific, and not specific
530 -- to any Operating Unit/Org
531
532 OPEN ps_mode_param(warehouse_id);
533 FETCH ps_mode_param INTO print_ps_mode_param;
534 IF ps_mode_param%NOTFOUND THEN
535 print_ps_mode_param := 'E';
536 END IF;
537 IF ps_mode_param%ISOPEN THEN
538 CLOSE ps_mode_param;
539 END IF;
540
541 IF ((ordered_rsr(1).attribute IN (C_INVOICE_VALUE, C_ORDER_NUMBER))
542 AND (print_ps_mode_param = 'I') AND (use_order_ps = 'Y')) THEN
543 print_ps_mode := IMMEDIATE_PRINT_PS;
547 WSH_UTIL.Write_Log('Print Pick slip mode is Deferred');
544 WSH_UTIL.Write_Log('Print Pick slip mode is Immediate');
545 ELSE
546 print_ps_mode := DEFERRED_PRINT_PS;
548 END IF;
549
550 p_print_ps_mode := print_ps_mode;
551
552 WSH_UTIL.Write_Log('Updating request id for batch');
553
554 -- Update picking batch setting request id and other who parameters
555
556 -- Use the parameters passed to the Init Function, instead of using the
557 -- column names in the = conditions of the SQL. That is use
558 -- p_user_id , p_program_id , p_request_id , p_login_id and p_batch_id and p_application_id
559
560 UPDATE SO_PICKING_BATCHES_ALL
561 SET REQUEST_ID = p_request_id,
562 PROGRAM_APPLICATION_ID = p_application_id,
563 PROGRAM_ID = p_program_id,
564 PROGRAM_UPDATE_DATE = SYSDATE,
565 LAST_UPDATED_BY = p_user_id,
566 LAST_UPDATE_DATE = SYSDATE,
567 LAST_UPDATE_LOGIN = p_login_id
568 WHERE BATCH_ID = p_batch_id
569 AND (REQUEST_ID IS NULL OR REQUEST_ID = p_request_id);
570
571 IF SQL%NOTFOUND THEN
572 WSH_UTIL.Write_Log('Picking Batch ' || to_char(p_batch_id) || ' does not exist ');
573 WSH_UTIL.Write_Log('or another pick release request has already released this batch');
574 END IF;
575
576 -- package WSH_PR_PICKING_SESSION has been initialized
577 initialized := TRUE;
578
579 RETURN SUCCESS;
580
581 EXCEPTION
582 -- handle unable to lock situation
583 WHEN record_locked THEN
584 WSH_UTIL.Write_Log('Could not lock Batch ID ' || to_char(p_batch_id) || ' for update.');
585 RETURN FAILURE;
586
587 -- handle other errors
588 WHEN OTHERS THEN
589 IF get_lock_batch%ISOPEN THEN
590 CLOSE get_lock_batch;
591 END IF;
592 IF get_line_info%ISOPEN THEN
593 CLOSE get_line_info;
594 END IF;
595 IF rel_seq_rule%ISOPEN THEN
596 CLOSE rel_seq_rule;
597 END IF;
598 IF order_ps_group%ISOPEN THEN
599 CLOSE order_ps_group;
600 END IF;
601 IF ps_mode_param%ISOPEN THEN
602 CLOSE ps_mode_param;
603 END IF;
604
605 WSH_UTIL.Default_Handler('WSH_PR_PICKING_SESSION.Init','');
606 RETURN FAILURE;
607
608 END Init;
609
610
611 --
612 -- Name
613 -- FUNCTION SRS_Picking_Batch
614 --
615 -- Purpose
616 -- This function inserts a picking batch for SRS
617 --
618 -- Arguments
619 --
620 -- Return Values
621 -- -1 => Failure
622 -- 0 => Success
623 --
624 -- Notes
625 --
626
627 FUNCTION SRS_Picking_Batch (
631 p_new_batch_id IN OUT BINARY_INTEGER,
628 p_user_id IN BINARY_INTEGER,
629 p_login_id IN BINARY_INTEGER,
630 p_batch_prefix IN VARCHAR2,
632 p_rule_name IN VARCHAR2,
633 p_doc_set IN VARCHAR2
634 )
635 RETURN BINARY_INTEGER IS
636
637 CURSOR doc_set(x_doc_set_id IN BINARY_INTEGER) IS
638 SELECT COUNT(*)
639 FROM SO_REPORT_SETS
640 WHERE REPORT_SET_ID = x_doc_set_id
641 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
642 AND NVL(END_DATE_ACTIVE, SYSDATE+1);
643
644 CURSOR pick_rule(x_rule_name IN VARCHAR2) IS
645 SELECT COUNT(*)
646 FROM SO_PICKING_RULES
647 WHERE PICKING_RULE = x_rule_name
648 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
649 AND NVL(END_DATE_ACTIVE, SYSDATE+1);
650
651 CURSOR get_default_psr(x_rule_name IN VARCHAR2) IS
652 SELECT par.pick_slip_rule_id
653 FROM WSH_PARAMETERS par,
654 SO_PICKING_RULES rules
655 WHERE rules.picking_rule = x_rule_name
656 AND rules.warehouse_id = par.organization_id;
657
658 CURSOR get_default_rsr(x_rule_name IN VARCHAR2) IS
659 SELECT par.release_seq_rule_id
660 FROM WSH_PARAMETERS par,
661 SO_PICKING_RULES rules
662 WHERE rules.picking_rule = x_rule_name
663 AND rules.warehouse_id = par.organization_id;
664
665 x_batch_name VARCHAR2(30);
666 x_batch_id BINARY_INTEGER;
667 count_temp BINARY_INTEGER;
668 default_psr NUMBER;
669 default_rsr NUMBER;
670
671 -- Declared these variables for Fetching Operating Org
672 operating_org NUMBER;
673 org_id_char1 VARCHAR2(30);
674 org_found_flag BOOLEAN;
675
676 BEGIN
677
678 -- Fetch the current Operating Org . If no operating org was returned by the function below
679 -- indicated by org_found_flag = FALSE, then we set the operating org to NULL , otherwise to its
680 -- fetched value.
681 FND_PROFILE.GET_SPECIFIC( 'ORG_ID' , NULL , NULL , NULL , org_id_char1 , org_found_flag );
682 IF org_found_flag = TRUE THEN
683 operating_org := to_number(org_id_char1);
684 ELSE
685 operating_org := NULL;
686 END IF;
687
688 -- Validate Document Set
689 IF p_doc_set IS NULL THEN
690 WSH_UTIL.Write_Log('No Document Set specified');
691 ELSE
692 OPEN doc_set(to_number(p_doc_set));
693 FETCH doc_set INTO count_temp;
694 IF doc_set%NOTFOUND THEN
695 WSH_UTIL.Write_Log('The document set ' || p_doc_set || ' is not in valid period');
696 ELSE
697 WSH_UTIL.Write_Log('The document set is valid');
698 END IF;
699 END IF;
700
701 -- Validate Picking Rule
702
703 OPEN pick_rule(p_rule_name);
704 FETCH pick_rule INTO count_temp;
705 IF pick_rule%NOTFOUND THEN
706 WSH_UTIL.Write_Log('The picking rule '|| p_rule_name ||
707 ' does not exist or has expired');
708 RETURN FAILURE;
709 END IF;
710
711 -- Get default Pick Slip Grouping Rule and Release
712 -- Sequence Rule for warehouse
713 OPEN get_default_psr(p_rule_name);
714 FETCH get_default_psr INTO default_psr;
718
715 IF get_default_psr%NOTFOUND THEN
716 default_psr := NULL;
717 END IF;
719 OPEN get_default_rsr(p_rule_name);
720 FETCH get_default_rsr INTO default_rsr;
721 IF get_default_rsr%NOTFOUND THEN
722 default_rsr := NULL;
723 END IF;
724
725 -- Create a unique picking batch name
726
727 WHILE TRUE LOOP
728 SELECT SO_PICKING_BATCHES_S.NEXTVAL
729 INTO x_batch_id
730 FROM DUAL;
731
732 p_new_batch_id := x_batch_id;
733
734 IF p_batch_prefix IS NULL THEN
735 x_batch_name := to_char(x_batch_id);
736 ELSE
737 x_batch_name := p_batch_prefix || '-' || to_char(x_batch_id);
738 END IF;
739
740 -- Check if batch already exists with this name
741 WSH_UTIL.Write_Log('Checking batch name ' || x_batch_name);
742 SELECT COUNT(*)
743 INTO count_temp
744 FROM SO_PICKING_BATCHES_ALL
745 WHERE NAME = x_batch_name;
746
747 IF count_temp = 0 THEN
748 EXIT;
749 END IF;
750
751 END LOOP;
752
753 -- Insert the new picking batch
754
755 INSERT INTO SO_PICKING_BATCHES_ALL
756 (BATCH_ID,
757 CREATION_DATE,
758 CREATED_BY,
759 LAST_UPDATE_DATE,
760 LAST_UPDATED_BY,
761 LAST_UPDATE_LOGIN,
762 NAME,
763 PRINT_FLAG,
764 BACKORDERS_ONLY_FLAG,
765 EXISTING_RSVS_ONLY_FLAG,
766 SHIPMENT_PRIORITY_CODE,
767 HEADER_ID,
768 ORDER_TYPE_ID,
769 WAREHOUSE_ID,
770 CUSTOMER_ID,
771 SITE_USE_ID,
772 SHIP_METHOD_CODE,
773 SUBINVENTORY,
774 SHIP_SET_NUMBER,
775 INVENTORY_ITEM_ID,
776 DATE_REQUESTED_FROM,
777 DATE_REQUESTED_TO,
778 SCHEDULED_SHIPMENT_DATE_FROM,
779 SCHEDULED_SHIPMENT_DATE_TO,
780 PICK_SLIP_RULE_ID,
781 RELEASE_SEQ_RULE_ID,
782 PARTIAL_ALLOWED_FLAG,
783 INCLUDE_PLANNED_LINES,
784 AUTOCREATE_DELIVERY_FLAG,
785 ORG_ID)
786 SELECT x_batch_id,
787 SYSDATE,
788 p_user_id,
789 SYSDATE,
790 p_user_id,
791 p_login_id,
792 x_batch_name,
793 decode(p_doc_set, '-1', NULL, p_doc_set),
794 BACKORDERS_ONLY_FLAG,
795 NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
796 SHIPMENT_PRIORITY_CODE,
797 HEADER_ID,
798 ORDER_TYPE_ID,
799 WAREHOUSE_ID,
800 CUSTOMER_ID,
801 SITE_USE_ID,
802 SHIP_METHOD_CODE,
803 SUBINVENTORY,
804 SHIP_SET_NUMBER,
805 INVENTORY_ITEM_ID,
806 DECODE(DATE_REQUESTED_FROM, TO_DATE (1,'J'), TRUNC(SYSDATE),
807 DATE_REQUESTED_FROM),
808 DECODE(DATE_REQUESTED_TO, TO_DATE (1,'J'), TRUNC(SYSDATE),
809 DATE_REQUESTED_TO),
810 DECODE(SCHEDULED_SHIPMENT_DATE_FROM,TO_DATE (1,'J'), TRUNC(SYSDATE),
814 NVL(PICK_SLIP_RULE_ID, default_psr),
811 SCHEDULED_SHIPMENT_DATE_FROM),
812 DECODE(SCHEDULED_SHIPMENT_DATE_TO,TO_DATE (1,'J'), TRUNC(SYSDATE),
813 SCHEDULED_SHIPMENT_DATE_TO),
815 NVL(RELEASE_SEQ_RULE_SET_ID, default_rsr),
816 PARTIAL_ALLOWED_FLAG,
817 INCLUDE_PLANNED_LINES_FLAG,
818 AUTOCREATE_DELIVERY_FLAG,
819 operating_org -- Insert Operating Org
820 FROM SO_PICKING_RULES
821 WHERE PICKING_RULE = p_rule_name;
822
823 WSH_UTIL.Write_Log('Inserted batch name ' || x_batch_name
824 || ' with batch_id ' ||
825 to_char(p_new_batch_id));
826
827 RETURN SUCCESS;
828
829 EXCEPTION
830 WHEN OTHERS THEN
831 WSH_UTIL.Default_Handler('WSH_PR_PICKING_SESSION.Launc_Doc_Set','');
832 RETURN FAILURE;
833
834 END SRS_Picking_Batch;
835
836
837 --
838 -- Name
839 -- FUNCTION Unreleased_Line_Details
840 --
841 -- Purpose
842 -- This function creates the unreleased line details SQL
843 -- statement
844 --
845 -- Return Values
846 -- -1 => Failure
847 -- 0 => Success
848 --
849 -- Notes
850 --
851
852 FUNCTION Unreleased_Line_Details
853 RETURN BINARY_INTEGER IS
854
855 cs BINARY_INTEGER;
856
857 BEGIN
858 -- handle uninitialized package errors here
859 IF initialized = FALSE THEN
860 WSH_UTIL.Write_Log('The package must be initialized before use');
861 RETURN FAILURE;
862 END IF;
863
864 IF unreleased_SQL IS NULL THEN
865 cs := Construct_SQL('UNRELEASED');
866 IF cs = FAILURE THEN
867 RETURN FAILURE;
868 END IF;
869 RETURN SUCCESS;
870 END IF;
871
872 END Unreleased_Line_Details;
873
874
875 --
876 -- Name
877 -- FUNCTION Backordered_Line_Details
878 --
879 -- Purpose
880 -- This function creates the backordered line details SQL
881 -- statement
882 --
883 -- Return Values
884 -- -1 => Failure
885 -- 0 => Success
886 --
887 -- Notes
888 --
889
890 FUNCTION Backordered_Line_Details
891 RETURN BINARY_INTEGER IS
892
893 cs BINARY_INTEGER;
894
895 BEGIN
896 -- handle uninitialized package errors here
897 IF initialized = FALSE THEN
898 WSH_UTIL.Write_Log('The package must be initialized before use');
899 RETURN FAILURE;
900 END IF;
901
902 IF backordered_SQL IS NULL THEN
903 cs := Construct_SQL('BACKORDERED');
904 IF cs = FAILURE THEN
905 RETURN FAILURE;
906 END IF;
907 RETURN SUCCESS;
908 END IF;
909
910 END Backordered_Line_Details;
911
912
913 --
914 -- Name
915 -- FUNCTION Sync_Details
916 --
917 -- Purpose
918 -- This function creates the SQL statement to fetch
919 -- BOM exploded/demand synchronized line details
920 --
921 -- Return Values
922 -- -1 => Failure
923 -- 0 => Success
924 --
925 -- Notes
926 --
927
928 FUNCTION Sync_Details
929 RETURN BINARY_INTEGER IS
930
931 cs BINARY_INTEGER;
932
933 BEGIN
934 -- handle uninitialized package errors here
935 IF initialized = FALSE THEN
936 WSH_UTIL.Write_Log('The package must be initialized before use');
937 RETURN FAILURE;
938 END IF;
939
940 cs := Construct_SQL('SYNC');
941 IF cs = FAILURE THEN
942 RETURN FAILURE;
943 END IF;
944 RETURN SUCCESS;
945
946 END Sync_Details;
947
948
949 --
950 -- Name
951 -- FUNCTION Non_Shippable_Lines
952 --
953 -- Purpose
954 -- This function creates the non-shippable lines SQL
955 -- statement
956 --
957 -- Return Values
958 -- -1 => Failure
959 -- 0 => Success
960 --
961 -- Notes
962 --
963
964 FUNCTION Non_Shippable_Lines
965 RETURN BINARY_INTEGER IS
966
967 cs BINARY_INTEGER;
968
969 BEGIN
970 -- handle uninitialized package errors here
971 IF initialized = FALSE THEN
972 WSH_UTIL.Write_Log('The package must be initialized before use');
973 RETURN FAILURE;
974 END IF;
975
976 cs := Construct_SQL('NON_SHIPPABLE');
977 IF cs = FAILURE THEN
978 RETURN FAILURE;
979 END IF;
980
981 RETURN SUCCESS;
982
983 END Non_Shippable_Lines;
984
985 --
986 -- Name
987 -- FUNCTION Construct_SQL
988 --
989 -- Purpose
990 -- This function creates the actual SQL statement based on a
991 -- parameter passed to it which determines if it is UNRELEASED,
992 -- BACKORDERED or NON_SHIPPABLE.
993 --
994 -- Arguments
995 -- p_sql_type is determine what kind of SQL to create
996 --
997 -- Notes
998 --
999
1000 FUNCTION Construct_SQL(
1001 p_sql_type IN VARCHAR2
1002 )
1003 RETURN BINARY_INTEGER IS
1004
1005 cs BINARY_INTEGER;
1006 i BINARY_INTEGER;
1007
1008 BEGIN
1009 -- Create unreleased SQL statement
1010 IF (p_sql_type = 'UNRELEASED') THEN
1011 unreleased_SQL := '';
1012 Process_Buffer('u', ' SELECT ');
1013 Process_Buffer('u', ' L.LINE_ID, ');
1014 Process_Buffer('u', ' H.HEADER_ID, ');
1015 Process_Buffer('u', ' NVL(H.ORG_ID, -3114), ');
1019 Process_Buffer('u', ' NVL(L.SHIP_SET_NUMBER, -9), ');
1016 Process_Buffer('u', ' L.ATO_FLAG, ');
1017 Process_Buffer('u', ' LD.LINE_DETAIL_ID, ');
1018 Process_Buffer('u', ' L.SHIP_MODEL_COMPLETE_FLAG, ');
1020 Process_Buffer('u', ' NVL(L.PARENT_LINE_ID, 0), ');
1021 Process_Buffer('u', ' NVL(LD.WAREHOUSE_ID, -1), ');
1022 Process_Buffer('u', ' NVL(NVL(L.SHIP_TO_SITE_USE_ID, H.SHIP_TO_SITE_USE_ID), -1), ');
1023 Process_Buffer('u', ' NVL(NVL(L.SHIP_TO_CONTACT_ID, H.SHIP_TO_CONTACT_ID), -1), ');
1024 Process_Buffer('u', ' NVL(L.SHIP_METHOD_CODE, H.SHIP_METHOD_CODE), ');
1025 Process_Buffer('u', ' NVL(L.SHIPMENT_PRIORITY_CODE, H.SHIPMENT_PRIORITY_CODE), ');
1026 Process_Buffer('u', ' NVL(LD.DEPARTURE_ID, -1), ');
1027 Process_Buffer('u', ' NVL(LD.DELIVERY_ID, -1), ');
1028 Process_Buffer('u', ' L.ITEM_TYPE_CODE, ');
1029 Process_Buffer('u', ' TO_NUMBER(TO_CHAR( LD.SCHEDULE_DATE, ''J'' )), ');
1030 Process_Buffer('u', ' L.ORDERED_QUANTITY, ');
1031 Process_Buffer('u', ' L.CANCELLED_QUANTITY, ');
1032 Process_Buffer('u', ' L.INVENTORY_ITEM_ID, ');
1033 Process_Buffer('u', ' LD.INVENTORY_ITEM_ID, ');
1034 Process_Buffer('u', ' NVL(LD.CUSTOMER_ITEM_ID, -1), ');
1035 Process_Buffer('u', ' LD.DEP_PLAN_REQUIRED_FLAG, ');
1036 Process_Buffer('u', ' NVL(L.SHIPMENT_SCHEDULE_LINE_ID,0), ');
1037 Process_Buffer('u', ' L.UNIT_CODE, ');
1038 Process_Buffer('u', ' L.LINE_TYPE_CODE, ');
1039 Process_Buffer('u', ' L.COMPONENT_CODE, ');
1040 Process_Buffer('u', ' NVL(TO_CHAR(L.STANDARD_COMPONENT_FREEZE_DATE,''YYYY/MM/DD HH24:MI''),''''), ');
1041 Process_Buffer('u', ' H.ORDER_NUMBER, ');
1042 Process_Buffer('u', ' H.ORDER_TYPE_ID, ');
1043 Process_Buffer('u', ' H.CUSTOMER_ID, ');
1044 Process_Buffer('u', ' H.INVOICE_TO_SITE_USE_ID, ');
1045 Process_Buffer('u', ' DECODE( DP.PLANNED_DEPARTURE_DATE, NULL, TO_NUMBER( ');
1046 Process_Buffer('u', ' TO_CHAR(DP.PLANNED_DEPARTURE_DATE,''J'' ) ), 9999999 ), ');
1047 Process_Buffer('u', ' DECODE( DP.PLANNED_DEPARTURE_DATE, NULL, TO_NUMBER( ');
1048 Process_Buffer('u', ' TO_CHAR(DP.PLANNED_DEPARTURE_DATE,''SSSSS'')), 0), ');
1049 Process_Buffer('u', ' NVL(LD.MASTER_CONTAINER_ITEM_ID, -1), ');
1050 Process_Buffer('u', ' NVL(LD.DETAIL_CONTAINER_ITEM_ID, -1), ');
1051 Process_Buffer('u', ' NVL(LD.LOAD_SEQ_NUMBER, -1) , ');
1052 IF invoice_value_flag = 'Y' THEN
1053 Process_Buffer('u',' WSH_PR_CUSTOM.OUTSTANDING_ORDER_VALUE(H.HEADER_ID) ');
1054 ELSE
1055 Process_Buffer('u',' -1');
1056 END IF;
1057 Process_Buffer('u', ' FROM WSH_DELIVERIES DL, ');
1058 Process_Buffer('u', ' WSH_DEPARTURES DP, ');
1059 Process_Buffer('u', ' SO_LINE_DETAILS LD, ');
1060 Process_Buffer('u', ' SO_HEADERS_ALL H, ');
1061 IF (header_id <> 0 OR departure_id <> 0 OR order_line_id <>0) THEN
1062 Process_Buffer('u', ' SO_LINES_ALL L ');
1063 ELSE
1064 -- inline view to use index on S2. This is for performance improvement.
1065 Process_Buffer('u', ' (SELECT * FROM SO_LINES_ALL SL');
1066 Process_Buffer('u', ' WHERE SL.S2 IN (18,5) AND ');
1067 Process_Buffer('u', ' ROWNUM > 0) L ');
1068 END IF;
1069 Process_Buffer('u', ' WHERE L.HEADER_ID = H.HEADER_ID ');
1070 Process_Buffer('u', ' AND L.LINE_ID = LD.LINE_ID ');
1071 Process_Buffer('u', ' AND LD.DEPARTURE_ID = DP.DEPARTURE_ID (+) ');
1072 Process_Buffer('u', ' AND LD.DELIVERY_ID = DL.DELIVERY_ID (+) ');
1073 Process_Buffer('u', ' AND L.LINE_TYPE_CODE IN (''REGULAR'',''DETAIL'') ');
1074 Process_Buffer('u', ' AND L.SOURCE_TYPE_CODE = ''INTERNAL'' ');
1075 Process_Buffer('u', ' AND L.ITEM_TYPE_CODE IN ');
1076 Process_Buffer('u', ' (''KIT'',''MODEL'',''CLASS'',''STANDARD'') ');
1077 Process_Buffer('u', ' AND NOT ((L.ITEM_TYPE_CODE = ''STANDARD'') AND ');
1078 Process_Buffer('u', ' (NVL(L.PARENT_LINE_ID,-9) = -9) AND ');
1079 Process_Buffer('u', ' (NVL(L.ATO_FLAG,''N'') = ''N'') AND ');
1080 Process_Buffer('u', ' (LD.SHIPPABLE_FLAG || '''' = ''N'')) ');
1081 Process_Buffer('u', ' AND NVL(L.CANCELLED_QUANTITY,0) < L.ORDERED_QUANTITY ');
1082 Process_Buffer('u', ' AND L.ATO_LINE_ID IS NULL ');
1083 Process_Buffer('u', ' AND L.OPEN_FLAG || '''' = ''Y'' ');
1084 Process_Buffer('u', ' AND H.OPEN_FLAG || '''' = ''Y'' ');
1085 Process_Buffer('u', ' AND LD.RELEASED_FLAG || '''' = ''N'' ');
1086 Process_Buffer('u', ' AND LD.SCHEDULE_DATE IS NOT NULL ');
1087
1088 Process_Buffer('u', ' AND ((L.STANDARD_COMPONENT_FREEZE_DATE IS NOT NULL ');
1089 Process_Buffer('u', ' AND DECODE(L.ATO_FLAG,''N'', ');
1090 Process_Buffer('u', ' LD.SHIPPABLE_FLAG,''Y'') || '''' = ''Y'') ');
1091 Process_Buffer('u', ' OR ');
1092 Process_Buffer('u', ' (L.STANDARD_COMPONENT_FREEZE_DATE IS NULL ');
1093 Process_Buffer('u', ' AND (LD.INCLUDED_ITEM_FLAG || '''' = ''N'' ');
1094 Process_Buffer('u', ' OR LD.SHIPPABLE_FLAG || '''' = ''Y''))) ');
1095
1096 IF (header_id <> 0 OR departure_id <> 0 OR order_line_id <> 0) THEN
1097 Process_Buffer('u', ' AND L.S2+0 IN (18, 5) ');
1098 END IF;
1099
1100 -- Following conditions use bind variables
1101 -- The columns which have indexes are included in the SQL statement conditionally
1102 -- This is done so that the index will be used
1103
1104 IF (header_id <> 0 AND order_line_id = 0) THEN
1105 Process_Buffer('u', ' AND H.HEADER_ID = :X_header_id ' || ' ');
1106 ELSIF (header_id <> 0 AND order_line_id <> 0) THEN
1107 Process_Buffer('u', ' AND H.HEADER_ID + 0 = :X_header_id ' || ' ');
1108 END IF;
1109
1110 Process_Buffer('u', ' AND (H.ORDER_TYPE_ID = :X_order_type_id OR :X_order_type_id = 0) ' || ' ');
1111
1112 IF (customer_id <> 0) THEN
1113 Process_Buffer('u', ' AND H.CUSTOMER_ID = :X_customer_id ' || ' ');
1114 END IF;
1115
1116 IF (order_line_id <> 0) THEN
1117 Process_Buffer('u', ' AND L.LINE_ID = :X_order_line_id ' || ' ');
1118 END IF;
1119
1123 Process_Buffer('u', ' OR :X_ship_site_use_id = 0) ' || ' ');
1120 Process_Buffer('u', ' AND (L.SHIP_SET_NUMBER = :X_ship_set_number OR :X_ship_set_number = 0) ' || ' ');
1121
1122 Process_Buffer('u', ' AND (NVL(L.SHIP_TO_SITE_USE_ID,H.SHIP_TO_SITE_USE_ID) = :X_ship_site_use_id ' || ' ');
1124
1125 Process_Buffer('u', ' AND (NVL(L.SHIP_METHOD_CODE,H.SHIP_METHOD_CODE) = :X_ship_method_code ' || ' ');
1126 Process_Buffer('u', ' OR :X_ship_method_code IS NULL) ' || ' ');
1127
1128 -- Make sure that lines to be shipped out of a particular warehouse is picked up
1129 Process_Buffer('u', ' AND (LD.WAREHOUSE_ID = :X_warehouse_id OR :X_warehouse_id = -1) ' || ' ');
1130
1131 Process_Buffer('u', ' AND (LD.SUBINVENTORY = :X_subinventory OR :X_subinventory IS NULL)' || ' ' );
1132
1133 Process_Buffer('u', ' AND (L.SHIPMENT_PRIORITY_CODE = :X_shipment_priority ' || ' ');
1134 Process_Buffer('u', ' OR :X_shipment_priority IS NULL) ' || ' ');
1135
1136 Process_Buffer('u', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) >= ');
1137 Process_Buffer('u', ' to_date(:X_from_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_request_date IS NULL) ' || ' ');
1138
1139 Process_Buffer('u', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) <= ');
1140 Process_Buffer('u', ' to_date(:X_to_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_request_date IS NULL) ' || ' ');
1141
1142 Process_Buffer('u', ' AND (LD.SCHEDULE_DATE >= ');
1143 Process_Buffer('u', ' to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_sched_ship_date IS NULL) ' || ' ');
1144
1145 Process_Buffer('u', ' AND (LD.SCHEDULE_DATE <= ');
1146 Process_Buffer('u', ' to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_sched_ship_date IS NULL) ' || ' ');
1147
1148 -- if existing_rsvs_only_flag is not 'Y', the following condition always returns true
1149 Process_Buffer('u', ' AND ((LD.RESERVABLE_FLAG = ''Y'' ');
1150 Process_Buffer('u', ' AND LD.SCHEDULE_STATUS_CODE IN ( ''RESERVED'', ''SUPPLY RESERVED'' )) ');
1151 Process_Buffer('u', ' OR NVL(:X_existing_rsvs_only_flag,''X'') <> ''Y'') ');
1152
1153 Process_Buffer('u', ' AND (L.INVENTORY_ITEM_ID + 0 = :X_inventory_item_id ');
1154 Process_Buffer('u', ' OR :X_inventory_item_id = 0) ' || ' ');
1155
1156 Process_Buffer('u', ' AND (L.ATO_FLAG = ''N'' OR :X_reservations <> ''N'') ' || ' ');
1157
1158 -- Handling departures and deliveries
1159
1160 IF (departure_id <> 0) THEN
1161 Process_Buffer('u', ' AND LD.DEPARTURE_ID = :X_departure_id ' || ' ');
1162 Process_Buffer('u', ' AND NVL(DL.STATUS_CODE, ''XX'') = ''PL'' ');
1163 END IF;
1164
1165 IF (delivery_id <> 0) THEN
1166 Process_Buffer('u', ' AND LD.DELIVERY_ID = :X_delivery_id ' || ' ');
1167 END IF;
1168
1169 Process_Buffer('u', ' AND ((NVL(LD.DEPARTURE_ID, -99) = -99 ');
1170 Process_Buffer('u', ' AND NVL(LD.DELIVERY_ID, -99) = -99) ');
1171 Process_Buffer('u', ' OR :X_include_planned_lines <> ''N'' OR :X_departure_id <> 0) ');
1172
1173 Process_Buffer('u', ' AND (((NVL(LD.DEP_PLAN_REQUIRED_FLAG,''N'') = ''Y'') ');
1174 Process_Buffer('u', ' AND NVL(DP.STATUS_CODE, ''XX'') = ''PL'') ');
1175 Process_Buffer('u', ' OR (NVL(LD.DEP_PLAN_REQUIRED_FLAG,''N'') = ''N'')) ');
1176
1177 -- Determine the order by clause
1178 orderby_SQL := ' ORDER BY ';
1179 WSH_UTIL.Write_Log(orderby_SQL);
1180
1181 FOR i IN 1..total_release_criteria LOOP
1182 IF (ordered_rsr(i).attribute = C_INVOICE_VALUE) THEN
1183 Process_Buffer('o', ' WSH_PR_CUSTOM.OUTSTANDING_ORDER_VALUE(H.HEADER_ID) ' || ordered_rsr(i).sort_order || ', ');
1184 Process_Buffer('o', ' H.HEADER_ID ASC ' || ', ');
1185 ELSIF (ordered_rsr(i).attribute = C_ORDER_NUMBER) THEN
1186 Process_Buffer('o', ' H.HEADER_ID ' || ordered_rsr(i).sort_order || ', ');
1187 ELSIF (ordered_rsr(i).attribute = C_SCHEDULE_DATE) THEN
1188 Process_Buffer('o', ' TO_NUMBER(TO_CHAR(LD.SCHEDULE_DATE,''J'')) ' || ordered_rsr(i).sort_order || ', ');
1189 ELSIF (ordered_rsr(i).attribute = C_DEPARTURE) THEN
1190 Process_Buffer('o', ' NVL(DP.PLANNED_DEPARTURE_DATE, SYSDATE) ' || ordered_rsr(i).sort_order || ', ');
1191 ELSIF (ordered_rsr(i).attribute = C_SHIPMENT_PRIORITY) THEN
1192 Process_Buffer('o', ' NVL(L.SHIPMENT_PRIORITY_CODE,H.SHIPMENT_PRIORITY_CODE) ' || ordered_rsr(i).sort_order || ', ');
1193 END IF;
1194 END LOOP;
1195
1196 -- Must add this for easy grouping of ship sets and smc components
1197 IF use_order_header = FALSE THEN
1198 Process_Buffer('o', ' H.HEADER_ID, ');
1199 END IF;
1200 Process_Buffer('o', ' NVL(L.SHIP_SET_NUMBER, 99999999), ');
1201 Process_Buffer('o', ' L.SHIP_MODEL_COMPLETE_FLAG DESC, ');
1202 Process_Buffer('o', ' NVL(L.PARENT_LINE_ID,L.LINE_ID), ');
1203 Process_Buffer('o', ' LD.WAREHOUSE_ID, ');
1204 Process_Buffer('o', ' NVL(L.SHIP_TO_SITE_USE_ID, H.SHIP_TO_SITE_USE_ID), ');
1205 Process_Buffer('o', ' NVL(L.SHIP_METHOD_CODE, H.SHIP_METHOD_CODE) ');
1206
1207 unreleased_SQL := unreleased_SQL || orderby_SQL;
1208
1209 ELSIF (p_sql_type = 'BACKORDERED') THEN
1210
1211 backordered_SQL := '';
1212 Process_Buffer('b', ' SELECT ');
1213 Process_Buffer('b', ' PL.PICKING_LINE_ID, ');
1214 Process_Buffer('b', ' H.HEADER_ID, ');
1215 Process_Buffer('b', ' NVL(H.ORG_ID, -3114), ');
1216 Process_Buffer('b', ' L.ATO_FLAG, ');
1217 Process_Buffer('b', ' PLD.PICKING_LINE_DETAIL_ID, ');
1218 Process_Buffer('b', ' L.SHIP_MODEL_COMPLETE_FLAG, ');
1219 Process_Buffer('b', ' NVL(L.SHIP_SET_NUMBER, -9), ');
1220 Process_Buffer('b', ' NVL(L.PARENT_LINE_ID, 0), ');
1221 Process_Buffer('b', ' NVL(PLD.WAREHOUSE_ID, NVL(PL.WAREHOUSE_ID, -1)), ');
1222 Process_Buffer('b', ' NVL(NVL(NVL(PL.SHIP_TO_SITE_USE_ID, L.SHIP_TO_SITE_USE_ID), ');
1223 Process_Buffer('b', ' H.SHIP_TO_SITE_USE_ID), -1), ');
1224 Process_Buffer('b', ' NVL(NVL(NVL(PL.SHIP_TO_CONTACT_ID, L.SHIP_TO_CONTACT_ID), ');
1225 Process_Buffer('b', ' H.SHIP_TO_CONTACT_ID), -1), ');
1229 Process_Buffer('b', ' NVL(PLD.DELIVERY_ID, -1), ');
1226 Process_Buffer('b', ' NVL(PL.SHIP_METHOD_CODE, L.SHIP_METHOD_CODE), ');
1227 Process_Buffer('b', ' NVL(PL.SHIPMENT_PRIORITY_CODE, L.SHIPMENT_PRIORITY_CODE), ');
1228 Process_Buffer('b', ' NVL(PLD.DEPARTURE_ID, -1), ');
1230 Process_Buffer('b', ' L.ITEM_TYPE_CODE, ');
1231 Process_Buffer('b', ' TO_NUMBER(TO_CHAR( PLD.SCHEDULE_DATE, ''J'' )), ');
1232 Process_Buffer('b', ' PL.ORIGINAL_REQUESTED_QUANTITY, ');
1233 Process_Buffer('b', ' PL.CANCELLED_QUANTITY, ');
1234 Process_Buffer('b', ' L.INVENTORY_ITEM_ID, ');
1235 Process_Buffer('b', ' PL.INVENTORY_ITEM_ID, ');
1236 Process_Buffer('b', ' NVL(PL.CUSTOMER_ITEM_ID, -1), ');
1237 Process_Buffer('b', ' PL.DEP_PLAN_REQUIRED_FLAG, ');
1238 Process_Buffer('b', ' NVL(L.SHIPMENT_SCHEDULE_LINE_ID,0), ');
1239 Process_Buffer('b', ' PL.UNIT_CODE, ');
1240 Process_Buffer('b', ' L.LINE_TYPE_CODE, ');
1241 Process_Buffer('b', ' L.COMPONENT_CODE, ');
1242 Process_Buffer('b', ' NVL(TO_CHAR(L.STANDARD_COMPONENT_FREEZE_DATE,''YYYY/MM/DD HH24:MI''),''''), ');
1243 Process_Buffer('b', ' H.ORDER_NUMBER, ');
1244 Process_Buffer('b', ' H.ORDER_TYPE_ID, ');
1245 Process_Buffer('b', ' H.CUSTOMER_ID, ');
1246 Process_Buffer('b', ' H.INVOICE_TO_SITE_USE_ID, ');
1247 Process_Buffer('b', ' DECODE( DP.PLANNED_DEPARTURE_DATE, NULL, TO_NUMBER( ');
1248 Process_Buffer('b', ' TO_CHAR(DP.PLANNED_DEPARTURE_DATE,''J'' ) ), 9999999 ), ');
1249 Process_Buffer('b', ' DECODE( DP.PLANNED_DEPARTURE_DATE, NULL, TO_NUMBER( ');
1250 Process_Buffer('b', ' TO_CHAR(DP.PLANNED_DEPARTURE_DATE,''SSSSS'')), 0), ');
1251 Process_Buffer('b', ' NVL(PLD.MASTER_CONTAINER_ITEM_ID, -1), ');
1252 Process_Buffer('b', ' NVL(PLD.DETAIL_CONTAINER_ITEM_ID, -1), ');
1253 Process_Buffer('b', ' NVL(PLD.LOAD_SEQ_NUMBER, -1), ');
1254 IF invoice_value_flag = 'Y' THEN
1255 Process_Buffer('b',' WSH_PR_CUSTOM.OUTSTANDING_ORDER_VALUE(H.HEADER_ID) ');
1256 ELSE
1257 Process_Buffer('b',' -1');
1258 END IF;
1259 Process_Buffer('b', ' FROM WSH_DELIVERIES DL, ');
1260 Process_Buffer('b', ' WSH_DEPARTURES DP, ');
1261 Process_Buffer('b', ' SO_PICKING_LINE_DETAILS PLD, ');
1262 IF (header_id <> 0 OR departure_id <> 0 OR order_line_id <> 0) THEN
1263 Process_Buffer('b', ' SO_LINES_ALL L, ');
1264 ELSE
1265 -- inline view to use index on S3. This is for performance improvement.
1266 Process_Buffer('b', ' (SELECT * FROM SO_LINES_ALL SL');
1267 Process_Buffer('b', ' WHERE SL.S3 IN (18,5) AND ');
1268 Process_Buffer('b', ' ROWNUM > 0) L, ');
1269 END IF;
1270 Process_Buffer('b', ' SO_HEADERS_ALL H, ');
1271 Process_Buffer('b', ' SO_PICKING_LINES_ALL PL ');
1272 Process_Buffer('b', ' WHERE PL.PICKING_HEADER_ID + 0 = 0 ');
1273 Process_Buffer('b', ' AND PL.ORDER_LINE_ID = L.LINE_ID ');
1274 Process_Buffer('b', ' AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID ');
1275 Process_Buffer('b', ' AND L.HEADER_ID = H.HEADER_ID ');
1276 Process_Buffer('b', ' AND PLD.DEPARTURE_ID = DP.DEPARTURE_ID (+) ');
1277 Process_Buffer('b', ' AND PLD.DELIVERY_ID = DL.DELIVERY_ID (+) ');
1278 Process_Buffer('b', ' AND L.LINE_TYPE_CODE IN (''REGULAR'',''DETAIL'') ');
1279 Process_Buffer('b', ' AND L.SOURCE_TYPE_CODE = ''INTERNAL'' ');
1280 Process_Buffer('b', ' AND NVL(PL.CANCELLED_QUANTITY,0) < PL.ORIGINAL_REQUESTED_QUANTITY ');
1281 Process_Buffer('b', ' AND L.ATO_LINE_ID IS NULL ');
1282 Process_Buffer('b', ' AND L.OPEN_FLAG || '''' = ''Y'' ');
1283 Process_Buffer('b', ' AND H.OPEN_FLAG || '''' = ''Y'' ');
1284 Process_Buffer('b', ' AND PLD.RELEASED_FLAG || '''' = ''N'' ');
1285 Process_Buffer('b', ' AND PLD.SCHEDULE_DATE IS NOT NULL ');
1286
1287 IF (header_id <> 0 OR departure_id <> 0 OR order_line_id <> 0) THEN
1288 Process_Buffer('b', ' AND L.S3+0 IN (18, 5) ');
1289 END IF;
1290
1291 -- Following conditions use bind variables
1292 -- The columns which have indexes are included in the SQL statement conditionally
1293 -- This is done so that the index will be used
1294
1295 IF (header_id <> 0 AND order_line_id = 0) THEN
1296 Process_Buffer('b', ' AND H.HEADER_ID = :X_header_id ' || ' ');
1297 ELSIF (header_id <> 0 AND order_line_id <> 0) THEN
1298 Process_Buffer('b', ' AND H.HEADER_ID+0 = :X_header_id ' || ' ');
1299 END IF;
1300
1301 Process_Buffer('b', ' AND (H.ORDER_TYPE_ID = :X_order_type_id OR :X_order_type_id = 0) ' || ' ');
1302
1303 IF (customer_id <> 0) THEN
1304 Process_Buffer('b', ' AND H.CUSTOMER_ID = :X_customer_id ' || ' ');
1305 END IF;
1306
1307 IF (order_line_id <> 0) THEN
1308 Process_Buffer('b', ' AND L.LINE_ID = :X_order_line_id ' || ' ');
1309 END IF;
1310
1311 Process_Buffer('b', ' AND (L.SHIP_SET_NUMBER = :X_ship_set_number OR :X_ship_set_number = 0) ' || ' ');
1312
1313 Process_Buffer('b', ' AND (NVL(NVL(PL.SHIP_TO_SITE_USE_ID,L.SHIP_TO_SITE_USE_ID), ');
1314 Process_Buffer('b', ' H.SHIP_TO_SITE_USE_ID) = :X_ship_site_use_id ' || ' ');
1315 Process_Buffer('b', ' OR :X_ship_site_use_id = 0) ' || ' ');
1316
1317 Process_Buffer('b', ' AND (NVL(NVL(PL.SHIP_METHOD_CODE,L.SHIP_METHOD_CODE), ');
1318 Process_Buffer('b', ' H.SHIP_METHOD_CODE) = :X_ship_method_code ' || ' ');
1319 Process_Buffer('b', ' OR :X_ship_method_code IS NULL) ' || ' ');
1320
1321 -- Make sure that lines to be shipped out of a particular warehouse is picked up
1322 Process_Buffer('b', ' AND (PLD.WAREHOUSE_ID = :X_warehouse_id OR :X_warehouse_id = -1) ' || ' ');
1323
1324 Process_Buffer('b', ' AND (PLD.SUBINVENTORY = :X_subinventory OR :X_subinventory IS NULL)' || ' ' );
1325
1326 Process_Buffer('b', ' AND (NVL(PL.SHIPMENT_PRIORITY_CODE,L.SHIPMENT_PRIORITY_CODE) = ');
1327 Process_Buffer('b', ' :X_shipment_priority OR :X_shipment_priority IS NULL) ' || ' ');
1328
1329 Process_Buffer('b', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) >= ');
1333 Process_Buffer('b', ' to_date(:X_to_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_request_date IS NULL) ' || ' ');
1330 Process_Buffer('b', ' to_date(:X_from_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_request_date IS NULL) ' || ' ');
1331
1332 Process_Buffer('b', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) <= ');
1334
1335 Process_Buffer('b', ' AND (PLD.SCHEDULE_DATE >= ');
1336 Process_Buffer('b', ' to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_sched_ship_date IS NULL) ' || ' ');
1337
1338 Process_Buffer('b', ' AND (PLD.SCHEDULE_DATE <= ');
1339 Process_Buffer('b', ' to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_sched_ship_date IS NULL) ' || ' ');
1340
1341 -- if X_existing_rsvs_only_flag is not 'Y', the following condition always returns true
1342 Process_Buffer('b', ' AND ((PLD.RESERVABLE_FLAG = ''Y'' ');
1343 Process_Buffer('b', ' AND PLD.SCHEDULE_STATUS_CODE IN ( ''RESERVED'', ''SUPPLY RESERVED'' )) ');
1344 Process_Buffer('b', ' OR NVL(:X_existing_rsvs_only_flag,''X'') <> ''Y'') ');
1345
1346 Process_Buffer('b', ' AND (PL.INVENTORY_ITEM_ID + 0 = :X_inventory_item_id ');
1347 Process_Buffer('b', ' OR :X_inventory_item_id = 0) ' || ' ');
1348
1349 Process_Buffer('b', ' AND (L.ATO_FLAG = ''N'' OR :X_reservations <> ''N'') ' || ' ');
1350
1351 -- Handling departures and deliveries
1352
1353 IF (departure_id <> 0) THEN
1354 Process_Buffer('b', ' AND PLD.DEPARTURE_ID = :X_departure_id ' || ' ');
1355 Process_Buffer('b', ' AND NVL(DL.STATUS_CODE, ''XX'') = ''PL'' ');
1356 END IF;
1357
1358 IF (delivery_id <> 0) THEN
1359 Process_Buffer('b', ' AND PLD.DELIVERY_ID = :X_delivery_id ' || ' ');
1360 END IF;
1361
1362 Process_Buffer('b', ' AND ((NVL(PLD.DEPARTURE_ID, -99) = -99 ');
1363 Process_Buffer('b', ' AND NVL(PLD.DELIVERY_ID, -99) = -99) ');
1364 Process_Buffer('b', ' OR :X_include_planned_lines <> ''N'' OR :X_departure_id <> 0) ');
1365
1366 Process_Buffer('b', ' AND (((NVL(PL.DEP_PLAN_REQUIRED_FLAG,''N'') = ''Y'') ');
1367 Process_Buffer('b', ' AND NVL(DP.STATUS_CODE, ''XX'') = ''PL'') ');
1368 Process_Buffer('b', ' OR (NVL(PL.DEP_PLAN_REQUIRED_FLAG,''N'') = ''N'')) ');
1369
1370 -- Determine the order by clause
1371 orderby_SQL := ' ORDER BY ';
1372 WSH_UTIL.Write_Log(orderby_SQL);
1373
1374 FOR i IN 1..total_release_criteria LOOP
1375 IF (ordered_rsr(i).attribute = C_INVOICE_VALUE) THEN
1376 Process_Buffer('o', ' WSH_PR_CUSTOM.OUTSTANDING_ORDER_VALUE(H.HEADER_ID) ' || ordered_rsr(i).sort_order || ', ');
1377 Process_Buffer('o', ' H.HEADER_ID ASC ' || ', ');
1378 ELSIF (ordered_rsr(i).attribute = C_ORDER_NUMBER) THEN
1379 Process_Buffer('o', ' H.HEADER_ID ' || ordered_rsr(i).sort_order || ', ');
1380 ELSIF (ordered_rsr(i).attribute = C_SCHEDULE_DATE) THEN
1381 Process_Buffer('o', ' TO_NUMBER(TO_CHAR(PLD.SCHEDULE_DATE,''J'')) ' || ordered_rsr(i).sort_order || ', ');
1382 ELSIF (ordered_rsr(i).attribute = C_DEPARTURE) THEN
1383 Process_Buffer('o', ' NVL(DP.PLANNED_DEPARTURE_DATE, SYSDATE) ' || ordered_rsr(i).sort_order || ', ');
1384 ELSIF (ordered_rsr(i).attribute = C_SHIPMENT_PRIORITY) THEN
1385 Process_Buffer('o', ' NVL(L.SHIPMENT_PRIORITY_CODE,H.SHIPMENT_PRIORITY_CODE) ' || ordered_rsr(i).sort_order || ', ');
1386 END IF;
1387 END LOOP;
1388
1389 -- Must add this for easy grouping of ship sets and smc components
1390 IF use_order_header = FALSE THEN
1391 Process_Buffer('o', ' H.HEADER_ID, ');
1392 END IF;
1393 Process_Buffer('o', ' NVL(L.SHIP_SET_NUMBER, 99999999), ');
1394 Process_Buffer('o', ' L.SHIP_MODEL_COMPLETE_FLAG DESC, ');
1395 Process_Buffer('o', ' NVL(L.PARENT_LINE_ID,L.LINE_ID), ');
1396 Process_Buffer('o', ' PLD.WAREHOUSE_ID, ');
1397 Process_Buffer('o', ' NVL(L.SHIP_TO_SITE_USE_ID, H.SHIP_TO_SITE_USE_ID), ');
1398 Process_Buffer('o', ' NVL(L.SHIP_METHOD_CODE, H.SHIP_METHOD_CODE) ');
1399
1400 backordered_SQL := backordered_SQL || orderby_SQL;
1401
1402 ELSIF (p_sql_type = 'SYNC') THEN
1403 sync_SQL := '';
1404 Process_Buffer('s', ' SELECT ');
1405 Process_Buffer('s', ' L.LINE_ID, ');
1406 Process_Buffer('s', ' H.HEADER_ID, ');
1407 Process_Buffer('s', ' NVL(H.ORG_ID, -3114), ');
1408 Process_Buffer('s', ' L.ATO_FLAG, ');
1409 Process_Buffer('s', ' LD.LINE_DETAIL_ID, ');
1410 Process_Buffer('s', ' L.SHIP_MODEL_COMPLETE_FLAG, ');
1411 Process_Buffer('s', ' NVL(L.SHIP_SET_NUMBER, -9), ');
1412 Process_Buffer('s', ' NVL(L.PARENT_LINE_ID, 0), ');
1413 Process_Buffer('s', ' NVL(LD.WAREHOUSE_ID, -1), ');
1414 Process_Buffer('s', ' NVL(NVL(L.SHIP_TO_SITE_USE_ID, H.SHIP_TO_SITE_USE_ID), -1), ');
1415 Process_Buffer('s', ' NVL(NVL(L.SHIP_TO_CONTACT_ID, H.SHIP_TO_CONTACT_ID), -1), ');
1416 Process_Buffer('s', ' NVL(L.SHIP_METHOD_CODE, H.SHIP_METHOD_CODE), ');
1417 Process_Buffer('s', ' NVL(L.SHIPMENT_PRIORITY_CODE, H.SHIPMENT_PRIORITY_CODE), ');
1418 Process_Buffer('s', ' NVL(LD.DEPARTURE_ID, -1), ');
1419 Process_Buffer('s', ' NVL(LD.DELIVERY_ID, -1), ');
1420 Process_Buffer('s', ' TO_NUMBER(TO_CHAR( LD.SCHEDULE_DATE, ''J'' )), ');
1421 Process_Buffer('s', ' NVL(LD.CUSTOMER_ITEM_ID, -1), ');
1422 Process_Buffer('s', ' LD.DEP_PLAN_REQUIRED_FLAG, ');
1423 Process_Buffer('s', ' H.ORDER_NUMBER, ');
1424 Process_Buffer('s', ' H.ORDER_TYPE_ID, ');
1425 Process_Buffer('s', ' H.CUSTOMER_ID, ');
1426 Process_Buffer('s', ' H.INVOICE_TO_SITE_USE_ID, ');
1427 Process_Buffer('s', ' NVL(LD.MASTER_CONTAINER_ITEM_ID, -1), ');
1428 Process_Buffer('s', ' NVL(LD.DETAIL_CONTAINER_ITEM_ID, -1), ');
1429 Process_Buffer('s', ' NVL(LD.LOAD_SEQ_NUMBER, -1) ');
1430 Process_Buffer('s', ' FROM WSH_DELIVERIES DL, ');
1431 Process_Buffer('s', ' WSH_DEPARTURES DP, ');
1432 Process_Buffer('s', ' SO_LINE_DETAILS LD, ');
1433 Process_Buffer('s', ' SO_HEADERS_ALL H, ');
1434 Process_Buffer('s', ' SO_LINES_ALL L ');
1435 Process_Buffer('s', ' WHERE L.HEADER_ID = H.HEADER_ID ');
1439 Process_Buffer('s', ' AND L.LINE_TYPE_CODE IN (''REGULAR'',''DETAIL'') ');
1436 Process_Buffer('s', ' AND L.LINE_ID = LD.LINE_ID ');
1437 Process_Buffer('s', ' AND LD.DEPARTURE_ID = DP.DEPARTURE_ID (+) ');
1438 Process_Buffer('s', ' AND LD.DELIVERY_ID = DL.DELIVERY_ID (+) ');
1440 Process_Buffer('s', ' AND L.SOURCE_TYPE_CODE = ''INTERNAL'' ');
1441 Process_Buffer('s', ' AND NVL(L.CANCELLED_QUANTITY,0) < L.ORDERED_QUANTITY ');
1442 Process_Buffer('s', ' AND L.ATO_LINE_ID IS NULL ');
1443 Process_Buffer('s', ' AND L.OPEN_FLAG || '''' = ''Y'' ');
1444 Process_Buffer('s', ' AND H.OPEN_FLAG || '''' = ''Y'' ');
1445 Process_Buffer('s', ' AND LD.RELEASED_FLAG || '''' = ''N'' ');
1446 Process_Buffer('s', ' AND LD.SHIPPABLE_FLAG = ''Y'' ');
1447 Process_Buffer('s', ' AND LD.SCHEDULE_DATE IS NOT NULL ');
1448
1449 Process_Buffer('s', ' AND DECODE(L.ATO_FLAG,''Y'', ');
1450 Process_Buffer('s', ' DECODE(LD.SCHEDULE_STATUS_CODE,''RESERVED'',''Y'',''N''),''X'') = ');
1451 Process_Buffer('s', ' DECODE(L.ATO_FLAG,''Y'',''Y'',''X'') ');
1452
1453 Process_Buffer('s', ' AND L.LINE_ID IN ');
1454 Process_Buffer('s', ' (SELECT L1.LINE_ID FROM SO_LINES_ALL L1 ');
1455 Process_Buffer('s', ' WHERE L1.LINE_ID = :X_p_param_1 ');
1456 Process_Buffer('s', ' UNION ');
1457 Process_Buffer('s', ' SELECT L2.LINE_ID FROM SO_LINES_ALL L2 ');
1458 Process_Buffer('s', ' WHERE L2.PARENT_LINE_ID = :X_p_param_1) ');
1459
1460 Process_Buffer('s', ' AND L.S2 IN (18,5) ');
1461
1462 -- Make sure that lines to be shipped out of a particular warehouse is picked up
1463 Process_Buffer('s', ' AND (LD.WAREHOUSE_ID = :X_warehouse_id OR :X_warehouse_id = -1) ' || ' ');
1464
1465 Process_Buffer('s', ' AND (LD.SUBINVENTORY = :X_subinventory OR :X_subinventory IS NULL)' || ' ' );
1466
1467 Process_Buffer('s', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) >= ');
1468 Process_Buffer('s', ' to_date(:X_from_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_request_date IS NULL) ' || ' ');
1469
1470 Process_Buffer('s', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) <= ');
1471 Process_Buffer('s', ' to_date(:X_to_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_request_date IS NULL) ' || ' ');
1472
1473 Process_Buffer('s', ' AND (LD.SCHEDULE_DATE >= ');
1474 Process_Buffer('s', ' to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_sched_ship_date IS NULL) ' || ' ');
1475
1476 Process_Buffer('s', ' AND (LD.SCHEDULE_DATE <= ');
1477 Process_Buffer('s', ' to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_sched_ship_date IS NULL) ' || ' ');
1478
1479 -- if X_existing_rsvs_only_flag is not 'Y', the following condition always returns true
1480 Process_Buffer('s', ' AND ((LD.RESERVABLE_FLAG = ''Y'' ');
1481 Process_Buffer('s', ' AND LD.SCHEDULE_STATUS_CODE IN ( ''RESERVED'', ''SUPPLY RESERVED'' )) ');
1482 Process_Buffer('s', ' OR NVL(:X_existing_rsvs_only_flag,''X'') <> ''Y'') ');
1483
1484 Process_Buffer('s', ' AND (L.INVENTORY_ITEM_ID + 0 = :X_inventory_item_id ');
1485 Process_Buffer('s', ' OR :X_inventory_item_id = 0) ' || ' ');
1486
1487 Process_Buffer('s', ' AND (L.ATO_FLAG = ''N'' OR :X_reservations <> ''N'') ' || ' ');
1488
1489 -- Handling departures and deliveries
1490
1491 IF (departure_id <> 0) THEN
1492 Process_Buffer('s', ' AND LD.DEPARTURE_ID = :X_departure_id ' || ' ');
1493 Process_Buffer('s', ' AND NVL(DL.STATUS_CODE, ''XX'') = ''PL'' ');
1494 END IF;
1495
1496 IF (delivery_id <> 0) THEN
1497 Process_Buffer('s', ' AND LD.DELIVERY_ID = :X_delivery_id ' || ' ');
1498 END IF;
1499
1500 Process_Buffer('s', ' AND ((NVL(LD.DEPARTURE_ID, -99) = -99 ');
1501 Process_Buffer('s', ' AND NVL(LD.DELIVERY_ID, -99) = -99) ');
1502 Process_Buffer('s', ' OR :X_include_planned_lines <> ''N'' OR :X_departure_id <> 0) ');
1503
1504 Process_Buffer('s', ' AND (((NVL(LD.DEP_PLAN_REQUIRED_FLAG,''N'') = ''Y'') ');
1505 Process_Buffer('s', ' AND NVL(DP.STATUS_CODE, ''XX'') = ''PL'') ');
1506 Process_Buffer('s', ' OR (NVL(LD.DEP_PLAN_REQUIRED_FLAG,''N'') = ''N'')) ');
1507
1508 ELSIF (p_sql_type = 'NON_SHIPPABLE') THEN
1509 --
1510 -- This statement selects service and non-shippable standard lines
1511 --
1512
1513 non_ship_SQL := '';
1514 Process_Buffer('n', ' SELECT ');
1515 Process_Buffer('n', ' L.LINE_ID, ');
1516 Process_Buffer('n', ' H.HEADER_ID, ');
1517 Process_Buffer('n', ' NVL(LD.LINE_DETAIL_ID, -1), ');
1518 Process_Buffer('n', ' NVL(H.ORG_ID, -3114) ');
1519 Process_Buffer('n', ' FROM SO_LINE_DETAILS LD, ');
1520 Process_Buffer('n', ' SO_HEADERS_ALL H, ');
1521 Process_Buffer('n', ' SO_LINES_ALL L ');
1522 Process_Buffer('n', ' WHERE L.HEADER_ID = H.HEADER_ID ');
1523 Process_Buffer('n', ' AND L.LINE_ID = LD.LINE_ID(+) ');
1524 Process_Buffer('n', ' AND L.SOURCE_TYPE_CODE = ''INTERNAL'' ');
1525 Process_Buffer('n', ' AND NVL(L.CANCELLED_QUANTITY,0) < L.ORDERED_QUANTITY ');
1526 Process_Buffer('n', ' AND L.OPEN_FLAG || '''' = ''Y'' ');
1527 Process_Buffer('n', ' AND H.OPEN_FLAG || '''' = ''Y'' ');
1528 Process_Buffer('n', ' AND NVL(LD.RELEASED_FLAG, ''N'') = ''N'' ');
1529 Process_Buffer('n', ' AND (L.ITEM_TYPE_CODE = ''SERVICE'' ');
1530 Process_Buffer('n', ' OR (L.ITEM_TYPE_CODE = ''STANDARD'' ');
1531 Process_Buffer('n', ' AND NOT EXISTS ');
1532 Process_Buffer('n', ' ( SELECT XX.LINE_ID FROM ');
1533 Process_Buffer('n', ' SO_LINE_DETAILS XX ');
1534 Process_Buffer('n', ' WHERE ');
1535 Process_Buffer('n', ' XX.SHIPPABLE_FLAG || '''' = ''Y'' ');
1536 Process_Buffer('n', ' AND XX.LINE_ID = L.LINE_ID)) ');
1537 Process_Buffer('n', ' OR (L.ITEM_TYPE_CODE IN (''MODEL'',''KIT'',''CLASS'') ');
1538 Process_Buffer('n', ' AND L.REQUEST_ID = :X_request_id ');
1542 Process_Buffer('n', ' SO_LINE_DETAILS LD2 ');
1539 Process_Buffer('n', ' AND NOT EXISTS ( ');
1540 Process_Buffer('n', ' SELECT ''shippable component for a model'' ');
1541 Process_Buffer('n', ' FROM SO_LINES_ALL L2, ');
1543 Process_Buffer('n', ' WHERE L2.LINE_ID = NVL(L.PARENT_LINE_ID,L.LINE_ID) ');
1544 Process_Buffer('n', ' AND L2.LINE_ID = LD2.LINE_ID ');
1545 Process_Buffer('n', ' AND LD2.RELEASED_FLAG = ''N'' ');
1546 Process_Buffer('n', ' AND LD2.SHIPPABLE_FLAG = ''Y'' ');
1547 Process_Buffer('n', ' UNION ');
1548 Process_Buffer('n', ' SELECT ''shippable component for model components'' ');
1549 Process_Buffer('n', ' FROM SO_LINES_ALL L3, ');
1550 Process_Buffer('n', ' SO_LINE_DETAILS LD3 ');
1551 Process_Buffer('n', ' WHERE L3.PARENT_LINE_ID = NVL(L.PARENT_LINE_ID,L.LINE_ID) ');
1552 Process_Buffer('n', ' AND L3.LINE_ID = LD3.LINE_ID ');
1553 Process_Buffer('n', ' AND LD3.RELEASED_FLAG = ''N'' ');
1554 Process_Buffer('n', ' AND LD3.SHIPPABLE_FLAG = ''Y''))) ');
1555
1556 IF (header_id <> 0 OR customer_id <> 0 OR order_line_id <> 0) THEN
1557 Process_Buffer('n', ' AND L.S2+0 = 18 ');
1558 ELSE
1559 Process_Buffer('n', ' AND L.S2 = 18 ');
1560 END IF;
1561
1562 -- Following conditions use bind variables
1563 -- The columns which have indexes are included in the SQL statement conditionally
1564 -- This is done so that the index will be used
1565
1566 IF (header_id <> 0 AND order_line_id = 0) THEN
1567 Process_Buffer('n', ' AND H.HEADER_ID = :X_header_id ' || ' ');
1568 ELSIF (header_id <> 0 AND order_line_id <> 0) THEN
1569 Process_Buffer('n', ' AND H.HEADER_ID+0 = :X_header_id ' || ' ');
1570 END IF;
1571
1572 Process_Buffer('n', ' AND (H.ORDER_TYPE_ID = :X_order_type_id OR :X_order_type_id = 0) ' || ' ');
1573
1574 IF (customer_id <> 0) THEN
1575 Process_Buffer('n', ' AND H.CUSTOMER_ID = :X_customer_id ' || ' ');
1576 END IF;
1577
1578 IF (order_line_id <> 0) THEN
1579 Process_Buffer('n', 'AND L.LINE_ID = :X_order_line_id ' || ' ');
1580 END IF;
1581
1582 Process_Buffer('n', ' AND (L.SHIP_SET_NUMBER = :X_ship_set_number OR :X_ship_set_number = 0) ' || ' ');
1583
1584 -- Make sure that lines to be shipped out of a particular warehouse is picked up
1585 Process_Buffer('n', ' AND (NVL(LD.WAREHOUSE_ID, :X_warehouse_id) = :X_warehouse_id OR :X_warehouse_id = -1) ' || ' ');
1586
1587 Process_Buffer('n', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) >= ');
1588 Process_Buffer('n', ' to_date(:X_from_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_request_date IS NULL) ' || ' ');
1589
1590 Process_Buffer('n', ' AND (NVL(L.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) <= ');
1591 Process_Buffer('n', ' to_date(:X_to_request_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_request_date IS NULL) ' || ' ');
1592
1593 Process_Buffer('n', ' AND (NVL(LD.SCHEDULE_DATE,to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'')) >= ');
1594 Process_Buffer('n', ' to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_sched_ship_date IS NULL) ' || ' ');
1595
1596 Process_Buffer('n', ' AND (NVL(LD.SCHEDULE_DATE,to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'')) <= ');
1597 Process_Buffer('n', ' to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_sched_ship_date IS NULL) ' || ' ');
1598
1599 Process_Buffer('n', ' AND (L.INVENTORY_ITEM_ID + 0 = :X_inventory_item_id ');
1600 Process_Buffer('n', ' OR :X_inventory_item_id = 0) ' || ' ');
1601
1602 -- Handling departures and deliveries
1603
1604 IF (departure_id <> 0) THEN
1605 -- select non-ship lines from orders in departure
1606 Process_Buffer('n', ' AND EXISTS (SELECT LD1.LINE_DETAIL_ID ');
1607 Process_Buffer('n', ' FROM SO_LINES_ALL L1, ');
1608 Process_Buffer('n', ' SO_LINE_DETAILS LD1, ');
1609 Process_Buffer('n', ' WSH_DEPARTURES D1 ');
1610 Process_Buffer('n', ' WHERE LD1.LINE_ID = L1.LINE_ID ');
1611 Process_Buffer('n', ' AND L1.HEADER_ID = L.HEADER_ID ');
1612 Process_Buffer('n', ' AND LD1.DEPARTURE_ID = D1.DEPARTURE_ID ' || ' ');
1613 Process_Buffer('n', ' AND D1.DEPARTURE_ID = :X_departure_id ' || ' ');
1614 Process_Buffer('n', ' AND NVL(D1.STATUS_CODE, ''XX'') = ''PL'') ');
1615 END IF;
1616
1617 IF (delivery_id <> 0) THEN
1618 -- select non-ship lines from orders in delivery
1619 Process_Buffer('n', ' AND EXISTS (SELECT LD1.LINE_DETAIL_ID ');
1620 Process_Buffer('n', ' FROM SO_LINES_ALL L1, SO_LINE_DETAILS LD1 ');
1621 Process_Buffer('n', ' WHERE LD1.LINE_ID = L1.LINE_ID ');
1622 Process_Buffer('n', ' AND L1.HEADER_ID = L.HEADER_ID ');
1623 Process_Buffer('n', ' AND LD1.DELIVERY = :X_delivery_id) ' || ' ');
1624 END IF;
1625
1626 -- Determine the order by clause
1627 Process_Buffer('n', ' ORDER BY ');
1628 Process_Buffer('n', ' H.HEADER_ID ASC ');
1629
1630 ELSIF (p_sql_type = 'SET REQUEST') THEN
1631 sreq_SQL := '';
1632 Process_Buffer('sreq',' UPDATE SO_LINES_ALL L ');
1633 Process_Buffer('sreq',' SET L.REQUEST_ID = :X_request_id ');
1634 Process_Buffer('sreq',' WHERE L.LINE_ID IN ');
1635 Process_Buffer('sreq',' (SELECT L2.LINE_ID ');
1636 Process_Buffer('sreq',' FROM SO_HEADERS_ALL H, ');
1637 Process_Buffer('sreq',' SO_LINES_ALL L2 ');
1638 Process_Buffer('sreq',' WHERE L2.LINE_ID = :X_sync_line_id ');
1639 Process_Buffer('sreq',' AND L2.HEADER_ID = H.HEADER_ID ');
1640 Process_Buffer('sreq',' AND (NVL(L2.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) >= ');
1644 Process_Buffer('sreq',' to_date(:X_to_request_date,''YYYY/MM/DD HH24:MI:SS'') OR ');
1641 Process_Buffer('sreq',' to_date(:X_from_request_date,''YYYY/MM/DD HH24:MI:SS'') OR ');
1642 Process_Buffer('sreq',' :X_from_request_date IS NULL) ');
1643 Process_Buffer('sreq',' AND (NVL(L2.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) <= ');
1645 Process_Buffer('sreq',' :X_to_request_date IS NULL) ');
1646 Process_Buffer('sreq',' UNION ');
1647 Process_Buffer('sreq',' SELECT L3.LINE_ID ');
1648 Process_Buffer('sreq',' FROM SO_HEADERS_ALL H, ');
1649 Process_Buffer('sreq',' SO_LINES_ALL L3 ');
1650 Process_Buffer('sreq',' WHERE L3.PARENT_LINE_ID = :X_sync_line_id ');
1651 Process_Buffer('sreq',' AND L3.HEADER_ID = H.HEADER_ID ');
1652 Process_Buffer('sreq',' AND (NVL(L3.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) >= ');
1653 Process_Buffer('sreq',' to_date(:X_from_request_date,''YYYY/MM/DD HH24:MI:SS'') OR ');
1654 Process_Buffer('sreq',' :X_from_request_date IS NULL) ');
1655 Process_Buffer('sreq',' AND (NVL(L3.DATE_REQUESTED_CURRENT, H.DATE_REQUESTED_CURRENT) <= ');
1656 Process_Buffer('sreq',' to_date(:X_to_request_date,''YYYY/MM/DD HH24:MI:SS'') OR ');
1657 Process_Buffer('sreq',' :X_to_request_date IS NULL)) ');
1658 Process_Buffer('sreq', ' AND (L.INVENTORY_ITEM_ID + 0 = :X_inventory_item_id ');
1659 Process_Buffer('sreq', ' OR :X_inventory_item_id = 0) ');
1660 Process_Buffer('sreq', ' AND EXISTS (SELECT ''a line detail'' ');
1661 Process_Buffer('sreq', ' FROM SO_LINE_DETAILS LD ');
1662 Process_Buffer('sreq', ' WHERE LD.LINE_ID = L.LINE_ID ');
1663 Process_Buffer('sreq', ' AND (NVL(LD.WAREHOUSE_ID, :X_warehouse_id) = :X_warehouse_id OR :X_warehouse_id = -1) ');
1664 Process_Buffer('sreq', ' AND (NVL(LD.SCHEDULE_DATE,to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'')) >= ');
1665 Process_Buffer('sreq', ' to_date(:X_from_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_from_sched_ship_date IS NULL) ');
1666 Process_Buffer('sreq', ' AND (NVL(LD.SCHEDULE_DATE,to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'')) <= ');
1667 Process_Buffer('sreq', ' to_date(:X_to_sched_ship_date,''YYYY/MM/DD HH24:MI:SS'') OR :X_to_sched_ship_date IS NULL)) ');
1668 ELSE
1669 WSH_UTIL.Write_Log('Invalid parameter');
1670 RETURN FAILURE;
1671 END IF;
1672
1673 RETURN SUCCESS;
1674
1675 END Construct_SQL;
1676
1677
1678 --
1679 -- Name
1680 -- FUNCTION Get_Next_Line_Detail
1681 --
1682 -- Purpose
1683 -- This function compares attributes of two line details
1684 -- passed to it and returns the one that should be
1685 -- processed earlier based on the release sequence rule.
1686 --
1687 -- Arguments
1688 -- Attributes of a line that determine the order of
1689 -- releasing the lines. These are used in conjunction
1690 -- with the release sequence rule.
1691 --
1692 -- Return Values
1693 -- 'u' => unreleased line
1694 -- 'b' => backordered line
1695 -- other values => Error
1696 --
1697 -- Notes
1698 --
1699
1700 FUNCTION Get_Next_Line_Detail(
1701 u_invoice_value IN BINARY_INTEGER,
1702 b_invoice_value IN BINARY_INTEGER,
1703 u_order_number IN BINARY_INTEGER,
1704 b_order_number IN BINARY_INTEGER,
1705 u_schedule_date IN BINARY_INTEGER,
1706 b_schedule_date IN BINARY_INTEGER,
1707 u_departure_date_d IN BINARY_INTEGER,
1708 u_departure_date_t IN BINARY_INTEGER,
1709 b_departure_date_d IN BINARY_INTEGER,
1710 b_departure_date_t IN BINARY_INTEGER,
1711 u_shipment_pri IN VARCHAR2,
1712 b_shipment_pri IN VARCHAR2
1713 )
1714 RETURN VARCHAR2 IS
1715
1716 i BINARY_INTEGER;
1717
1718 BEGIN
1719 --
1720 -- Compares releases sequence rule attribute and
1721 -- return the next one based on the sort order
1722 -- of the attribute.
1723 --
1724
1725 FOR i in 1..total_release_criteria LOOP
1726
1727 -- Compare invoice values
1728 IF ordered_rsr(i).attribute = C_INVOICE_VALUE THEN
1729 IF ordered_rsr(i).sort_order = 'DESC' THEN
1730 IF u_invoice_value > b_invoice_value THEN
1731 RETURN 'u';
1732 ELSIF u_invoice_value < b_invoice_value THEN
1733 RETURN 'b';
1734 END IF;
1735 ELSIF ordered_rsr(i).sort_order = 'ASC' THEN
1736 IF u_invoice_value > b_invoice_value THEN
1737 RETURN 'b';
1738 ELSIF u_invoice_value < b_invoice_value THEN
1739 RETURN 'u';
1740 END IF;
1741 END IF;
1742
1743 -- Compare order number
1744 ELSIF ordered_rsr(i).attribute = C_ORDER_NUMBER THEN
1745 IF ordered_rsr(i).sort_order = 'DESC' THEN
1746 IF u_order_number > b_order_number THEN
1747 RETURN 'u';
1748 ELSIF u_order_number < b_order_number THEN
1749 RETURN 'b';
1750 END IF;
1751 ELSIF ordered_rsr(i).sort_order = 'ASC' THEN
1752 IF u_order_number > b_order_number THEN
1753 RETURN 'b';
1754 ELSIF u_order_number < b_order_number THEN
1755 RETURN 'u';
1756 END IF;
1757 END IF;
1758
1759 -- Compare schedule dates
1760 ELSIF ordered_rsr(i).attribute = C_SCHEDULE_DATE THEN
1761 IF ordered_rsr(i).sort_order = 'DESC' THEN
1762 IF u_schedule_date > b_schedule_date THEN
1763 RETURN 'u';
1764 ELSIF u_schedule_date < b_schedule_date THEN
1765 RETURN 'b';
1766 END IF;
1767 ELSIF ordered_rsr(i).sort_order = 'ASC' THEN
1768 IF u_schedule_date > b_schedule_date THEN
1769 RETURN 'b';
1770 ELSIF u_schedule_date < b_schedule_date THEN
1771 RETURN 'u';
1772 END IF;
1773 END IF;
1774
1775 -- Compare departure
1779 RETURN 'u';
1776 ELSIF ordered_rsr(i).attribute = C_DEPARTURE THEN
1777 IF ordered_rsr(i).sort_order = 'DESC' THEN
1778 IF u_departure_date_d > b_departure_date_d THEN
1780 ELSIF u_departure_date_d < b_departure_date_d THEN
1781 RETURN 'b';
1782 ELSIF u_departure_date_t > b_departure_date_t THEN
1783 RETURN 'u';
1784 ELSIF u_departure_date_t < b_departure_date_t THEN
1785 RETURN 'b';
1786 END IF;
1787 ELSIF ordered_rsr(i).sort_order = 'ASC' THEN
1788 IF u_departure_date_d > b_departure_date_d THEN
1789 RETURN 'b';
1790 ELSIF u_departure_date_d < b_departure_date_d THEN
1791 RETURN 'u';
1792 ELSIF u_departure_date_t > b_departure_date_t THEN
1793 RETURN 'b';
1794 ELSIF u_departure_date_t < b_departure_date_t THEN
1795 RETURN 'u';
1796 END IF;
1797 END IF;
1798
1799 -- Compare shipment priority
1800 ELSIF ordered_rsr(i).attribute = C_SHIPMENT_PRIORITY THEN
1801 IF ordered_rsr(i).sort_order = 'DESC' THEN
1802 IF u_shipment_pri > b_shipment_pri THEN
1803 RETURN 'u';
1804 ELSIF u_shipment_pri < b_shipment_pri THEN
1805 RETURN 'b';
1806 END IF;
1807 ELSIF ordered_rsr(i).sort_order = 'ASC' THEN
1808 IF u_shipment_pri > b_shipment_pri THEN
1809 RETURN 'b';
1810 ELSIF u_shipment_pri < b_shipment_pri THEN
1811 RETURN 'u';
1812 END IF;
1813 END IF;
1814 END IF;
1815 END LOOP;
1816
1817 -- at this point they are the same in all attributes
1818 RETURN 'u';
1819
1820 END Get_Next_Line_Detail;
1821
1822
1823 --
1824 -- Name
1825 -- PROCEDURE Process Buffer
1826 --
1827 -- Purpose
1828 -- This procedure processes a line of text, by first writing to the
1829 -- log file and then concatenating it to the required SQL buffer
1830 --
1831 -- Arguments
1832 -- p_buffer_name identifies which buffer to append to.
1833 -- 'u' -> Unreleased_SQL
1834 -- 'b' -> Backordered_SQL
1835 -- 'o' -> Orderby_SQL
1836 -- 'n' -> Non_ship_SQL
1837 -- p_buffer_text identifies the text to process
1838 --
1839 -- Notes
1840 --
1841
1842 PROCEDURE Process_Buffer(
1843 p_buffer_name IN VARCHAR2,
1844 p_buffer_text IN VARCHAR2
1845 ) IS
1846
1847 cs BINARY_INTEGER;
1848
1849 BEGIN
1850 WSH_UTIL.Write_Log(p_buffer_text);
1851 IF p_buffer_name = 'u' THEN
1852 Unreleased_SQL := Unreleased_SQL || p_buffer_text;
1853 ELSIF p_buffer_name = 'b' THEN
1854 Backordered_SQL := Backordered_SQL || p_buffer_text;
1855 ELSIF p_buffer_name = 's' THEN
1856 sync_SQL := sync_SQL || p_buffer_text;
1857 ELSIF p_buffer_name = 'o' THEN
1858 Orderby_SQL := Orderby_SQL || p_buffer_text;
1859 ELSIF p_buffer_name = 'n' THEN
1860 Non_Ship_SQL := Non_Ship_SQL || p_buffer_text;
1861 ELSIF p_buffer_name = 'sreq' THEN
1862 sreq_SQL := sreq_SQL || p_buffer_text;
1863 ELSE
1864 RETURN;
1865 END IF;
1866
1867 END Process_Buffer;
1868
1869
1870 --
1871 -- Name
1872 -- FUNCTION Launch_Doc_Set
1873 --
1874 -- Purpose
1875 -- This function launches the document set for pick release
1876 --
1877 -- Return Values
1878 -- -1 => Failure
1879 -- 0 => Success
1880 --
1881 -- Notes
1882 --
1883
1884 FUNCTION Launch_Doc_Set
1885 RETURN BINARY_INTEGER IS
1886
1887 cs BOOLEAN;
1888 message VARCHAR2(2000);
1889
1890 BEGIN
1891
1892 IF report_set_id <> -1 THEN
1893
1894 WSH_DOC_SETS.Print_Document_Sets(
1895 X_report_set_id => report_set_id,
1896 P_BATCH_ID => batch_id,
1897 P_PROG_REQUEST_ID => request_id,
1898 P_BATCH_NAME => batch_name,
1899 P_WAREHOUSE_ID => warehouse_id,
1900 message_string => message,
1901 status => cs);
1902 IF cs = FALSE THEN
1903 WSH_UTIL.Write_Log('Error in WSH_DOC_SETS.Print_Document_Sets');
1904 WSH_UTIL.Write_Log(message);
1905 RETURN FAILURE;
1906 END IF;
1907
1908 ELSE
1909 WSH_UTIL.Write_Log('Invalid documnet set');
1910 return FAILURE;
1911 END IF;
1912
1913 RETURN SUCCESS;
1914
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 WSH_UTIL.Default_Handler('WSH_PR_PICKING_SESSION.Launc_Doc_Set','');
1918 RETURN FAILURE;
1919
1920 END;
1921
1922 --
1923 -- Name
1924 -- FUNCTION Get_Session_Value
1925 --
1926 -- Purpose
1927 -- This function returns session values that may be used by other
1928 -- packages
1929 --
1930 -- Arguments
1931 -- p_token - attribute whose value needs to be determined
1932 --
1933 -- Return Values
1934 -- The value of the token in VARCHAR2 format
1935 --
1936 -- Notes
1937 --
1938
1939 FUNCTION Get_Session_Value(
1940 p_token IN VARCHAR2
1941 )
1942 RETURN VARCHAR2 IS
1943
1944 cs BINARY_INTEGER;
1945
1946 BEGIN
1947 -- handle uninitialized package errors here
1948 IF initialized = FALSE THEN
1949 WSH_UTIL.Write_Log('The package must be initialized before use');
1950 RETURN FAILURE;
1951 END IF;
1952
1953 IF p_token = 'UNRELEASED_SQL' THEN
1954 RETURN unreleased_SQL;
1955 ELSIF p_token = 'BACKORDERED_SQL' THEN
1956 RETURN backordered_SQL;
1957 ELSIF p_token = 'SYNC_SQL' THEN
1958 RETURN sync_SQL;
1962 RETURN sreq_SQL;
1959 ELSIF p_token = 'NON_SHIP_SQL' THEN
1960 RETURN non_ship_SQL;
1961 ELSIF p_token = 'SET_REQUEST_SQL' THEN
1963 ELSE
1964 -- handle invalid token
1965 WSH_UTIL.Write_Log('Invalid Token ' || p_token);
1966 RETURN to_char(FAILURE);
1967 END IF;
1968
1969 RETURN SUCCESS;
1970
1971 END Get_Session_Value;
1972
1973 END WSH_PR_PICKING_SESSION;