[Home] [Help]
PACKAGE BODY: APPS.WSH_UPGRADE_PICK_SLIP_DATA_NEW
Source
1 PACKAGE BODY WSH_UPGRADE_PICK_SLIP_DATA_NEW AS
2 /* $Header: wshpupdb.pls 120.2 2005/07/28 11:34:20 parkhj noship $ */
3
4 --
5 -- Package
6 -- WSH_UPGRADE_PICK_SLIP_DATA_NEW
7 --
8 -- Purpose
9 --
10
11 --
12 -- PACKAGE CONSTANTS
13 --
14
15 SUCCESS CONSTANT BINARY_INTEGER := 0;
16 FAILURE CONSTANT BINARY_INTEGER := -1;
17
18 --
19 -- PACKAGE VARIABLES
20 --
21 error_flag BOOLEAN := FALSE;
22 error_mesg VARCHAR2(2001);
23 long_waybill_count NUMBER := 0;
24
25 PROCEDURE Init_Mesg( x_mesg IN VARCHAR2) IS
26 BEGIN
27 error_mesg := substr(x_mesg,1,2000);
28 END;
29
30 PROCEDURE Add_Mesg( x_mesg IN VARCHAR2) IS
31 BEGIN
32 error_mesg := substr(error_mesg || ' '||x_mesg,1,2000);
33 END;
34
35 PROCEDURE Insert_Mesg IS
36 BEGIN
37 insert into wsh_upgrade_log
38 ( error_mesg )
39 values
40 ( error_mesg
41 );
42 END;
43
44 PROCEDURE Insert_Mesg( x_mesg IN VARCHAR2) IS
45 BEGIN
46 error_mesg := substr(x_mesg,1,2000);
47 insert into wsh_upgrade_log
48 ( error_mesg )
49 values
50 ( error_mesg
51 );
52 END;
53
54 PROCEDURE Get_Delivery(X_Delivery_ID IN OUT NOCOPY NUMBER,
55 X_name IN OUT NOCOPY VARCHAR2
56 ) IS
57 CURSOR C2 IS SELECT wsh_new_deliveries_s.nextval FROM sys.dual;
58
59 CURSOR C3 (l_delivery_id NUMBER) IS
60 SELECT 1
61 FROM wsh_deliveries
62 WHERE delivery_id = l_delivery_id;
63
64 temp NUMBER;
65 temp_id NUMBER;
66
67 BEGIN
68
69 LOOP
70 OPEN C2;
71 FETCH C2 INTO temp_id;
72 OPEN C3(temp_id);
73 FETCH C3 INTO temp;
74 IF (C3%NOTFOUND) THEN
75 CLOSE C3;
76 CLOSE C2;
77 EXIT;
78 END IF;
79 CLOSE C3;
80 CLOSE C2;
81 END LOOP;
82
83 X_Delivery_Id := temp_id;
84 X_Name := TO_CHAR(X_Delivery_Id);
85
86 EXCEPTION
87 WHEN others THEN
88 rollback;
89 Init_mesg('Error in procedure Get_Delivery ');
90 Insert_mesg;
91 commit;
92 raise;
93 END Get_Delivery;
94
95 PROCEDURE Get_Departure(X_Departure_Id IN OUT NOCOPY NUMBER,
96 X_name IN OUT NOCOPY VARCHAR2
97 ) IS
98 CURSOR C2 IS SELECT wsh_trips_s.nextval FROM sys.dual;
99
100 CURSOR C3 (l_departure_id NUMBER) IS
101 SELECT 1
102 FROM wsh_departures
103 WHERE departure_id = l_departure_id;
104
105 temp NUMBER;
106 temp_id NUMBER;
107 BEGIN
108
109 LOOP
110 OPEN C2;
111 FETCH C2 INTO temp_id;
112 OPEN C3(temp_id);
113 FETCH C3 INTO temp;
114 IF (C3%NOTFOUND) THEN
115 CLOSE C3;
116 CLOSE C2;
117 EXIT;
118 END IF;
119 CLOSE C3;
120 CLOSE C2;
121 END LOOP;
122
123 X_Departure_Id := temp_id;
124 X_Name := TO_CHAR(X_Departure_Id);
125
126
127 EXCEPTION
128 WHEN others THEN
129 rollback;
130 Init_mesg('Error in procedure Get_Departure');
131 Insert_mesg;
132 commit;
133 Raise;
134 END Get_Departure;
135
136 --
137 -- Name
138 -- FUNCTION Insert_Row
139 --
140 -- Purpose
141 -- Gets the delivery_id to be used in creating deliveries when
142 -- inserting picking line details
143 --
144 -- Arguments
145 -- p_header_id => order header id
146 -- p_ship_to_site_use_id => ship to site use id (ultimate ship to)
147 -- p_ship_method_code => ship method (freight carrier)
148 --
149 -- Return Values
150 -- -1 => Failure
151 -- others => delivery_id
152 --
153
154
155 FUNCTION Insert_Row(
156 p_header_id IN BINARY_INTEGER,
157 p_departure_id IN OUT NOCOPY BINARY_INTEGER
158 )
159 RETURN BINARY_INTEGER IS
160
161 CURSOR get_picking_info(x_header_id IN BINARY_INTEGER) IS
162 SELECT order_header_id,
163 ship_to_site_use_id,
164 ship_method_code,
165 warehouse_id,
166 date_shipped,
167 date_confirmed,
168 waybill_num,
169 weight,
170 weight_unit_code,
171 picked_by_id,
172 packed_by_id,
173 context,
174 attribute1,
175 attribute2,
176 attribute3,
177 attribute4,
178 attribute5,
179 attribute6,
180 attribute7,
181 attribute8,
182 attribute9,
183 attribute10,
184 attribute11,
185 attribute12,
186 attribute13,
187 attribute14,
188 attribute15,
189 creation_date,
190 created_by,
191 last_updated_by,
192 last_update_login,
193 last_update_date,
194 expected_arrival_date -- added: for bug 1413000
195 FROM SO_PICKING_HEADERS_ALL
196 WHERE picking_header_id = x_header_id;
197
198 CURSOR get_order_info(x_header_id IN BINARY_INTEGER) IS
199 SELECT NVL(CUSTOMER_ID,-1),
200 NVL(FOB_CODE, 'XX'),
201 NVL(FREIGHT_TERMS_CODE, 'XX'),
202 CURRENCY_CODE
203 FROM SO_HEADERS_ALL
204 WHERE HEADER_ID = x_header_id;
205
206 CURSOR count_shipped_pls (x_header_id IN BINARY_INTEGER) IS
207 SELECT count(*)
208 FROM SO_PICKING_LINES_ALL
209 WHERE picking_header_id = x_header_id
210 AND nvl(shipped_quantity,0) > 0;
211
212 p_order_header_id BINARY_INTEGER;
213 p_ship_to_site_use_id BINARY_INTEGER;
214 p_ship_method_code VARCHAR2(30);
215 p_organization_id BINARY_INTEGER;
216 p_date_shipped DATE;
217 p_date_confirmed DATE;
218 p_waybill_num VARCHAR2(50);
219 p_weight NUMBER;
220 p_weight_unit_code VARCHAR2(3);
221 p_picked_by_id BINARY_INTEGER;
222 p_packed_by_id BINARY_INTEGER;
223 p_creation_date DATE;
224 p_created_by BINARY_INTEGER;
225 p_last_updated_by BINARY_INTEGER;
226 p_last_update_login BINARY_INTEGER;
227 p_last_update_date DATE;
228 p_expected_arrival_date DATE; -- added: for bug 1413000
229 v_customer_id BINARY_INTEGER;
230 v_fob_code VARCHAR2(30);
231 v_freight_terms_code VARCHAR2(30);
232 v_currency_code VARCHAR2(15);
233 v_delivery_name VARCHAR2(15);
234 v_delivery_id BINARY_INTEGER := -1;
235 v_departure_name VARCHAR2(15);
236 p_pick_line_count BINARY_INTEGER;
237 p_context VARCHAR2(150);
238 p_attribute1 VARCHAR2(150);
239 p_attribute2 VARCHAR2(150);
240 p_attribute3 VARCHAR2(150);
241 p_attribute4 VARCHAR2(150);
242 p_attribute5 VARCHAR2(150);
243 p_attribute6 VARCHAR2(150);
244 p_attribute7 VARCHAR2(150);
245 p_attribute8 VARCHAR2(150);
246 p_attribute9 VARCHAR2(150);
247 p_attribute10 VARCHAR2(150);
248 p_attribute11 VARCHAR2(150);
249 p_attribute12 VARCHAR2(150);
250 p_attribute13 VARCHAR2(150);
251 p_attribute14 VARCHAR2(150);
252 p_attribute15 VARCHAR2(150);
253
254
255 BEGIN
256
257 -- Fetch all picking header parameters
258 OPEN get_picking_info(p_header_id);
259 FETCH get_picking_info
260 INTO p_order_header_id,
261 p_ship_to_site_use_id,
262 p_ship_method_code,
263 p_organization_id,
264 p_date_shipped,
265 p_date_confirmed,
266 p_waybill_num,
267 p_weight,
268 p_weight_unit_code,
269 p_picked_by_id,
270 p_packed_by_id,
271 p_context,
272 p_attribute1,
273 p_attribute2,
274 p_attribute3,
275 p_attribute4,
276 p_attribute5,
277 p_attribute6,
278 p_attribute7,
279 p_attribute8,
280 p_attribute9,
281 p_attribute10,
282 p_attribute11,
283 p_attribute12,
284 p_attribute13,
285 p_attribute14,
286 p_attribute15,
287 p_creation_date,
288 p_created_by,
289 p_last_updated_by,
290 p_last_update_login,
291 p_last_update_date,
292 p_expected_arrival_date;
293 CLOSE get_picking_info;
294
295 -- Fetch all delivery parameters
296 OPEN get_order_info(p_order_header_id);
297 FETCH get_order_info
298 INTO v_customer_id,
299 v_fob_code,
300 v_freight_terms_code,
301 v_currency_code;
302
303 IF get_order_info%NOTFOUND THEN
304 Init_Mesg('Error: Cannot find order header, header_id '|| p_order_header_id);
305 RETURN FAILURE;
306 END IF;
307
308 CLOSE get_order_info;
309
310 IF (p_ship_to_site_use_id is NULL) THEN
311 Init_Mesg('ERROR: Ship_To_Site_Use_Id column for picking_header_id '||p_header_id||' in so_picking_headers_all is NULL');
312 RETURN FAILURE;
313 END IF;
314
315 IF (v_customer_id is NULL) THEN
316 Init_Mesg('ERROR: Customer_Id column for header_id '|| p_order_header_id ||' in so_headers_all is NULL');
317 RETURN FAILURE;
318 END IF;
319
320 IF (p_date_shipped is NULL) THEN
321 OPEN count_shipped_pls(p_header_id);
322 FETCH count_shipped_pls INTO p_pick_line_count;
323 CLOSE count_shipped_pls;
324
325 IF p_pick_line_count > 0 THEN
326 Init_Mesg('ERROR: Date_Shipped column for picking_header_id '||p_header_id||' in so_picking_headers_all is NULL');
327 RETURN FAILURE;
328 ELSE
329 p_date_shipped := p_last_update_date;
330 END IF;
331 END IF;
332
333
334 Get_Departure( p_departure_id, v_departure_name);
335
336 INSERT INTO wsh_departures(
337 organization_id,
338 departure_id,
339 name,
340 source_code,
341 arrive_after_departure_id,
342 status_code,
343 report_set_id,
344 date_closed,
345 vehicle_item_id,
346 vehicle_number,
347 freight_carrier_code,
348 planned_departure_date,
349 actual_departure_date,
350 bill_of_lading,
351 gross_weight,
352 net_weight,
353 weight_uom_code,
354 volume,
355 volume_uom_code,
356 fill_percent,
357 seal_code,
358 routing_instructions,
359 creation_date,
360 created_by,
361 last_update_date,
362 last_updated_by,
363 last_update_login,
364 program_application_id
365 ) VALUES (
366 p_organization_id,
367 p_departure_id,
368 v_departure_name,
369 'S',
370 NULL,
371 'CL',
372 NULL,
373 p_date_confirmed,
374 NULL,
375 '',
376 p_ship_method_code,
377 p_date_shipped,
378 p_date_shipped,
379 NULL,
380 p_weight,
381 p_weight,
382 p_weight_unit_code,
383 NULL,
384 NULL,
385 NULL,
386 NULL,
387 NULL,
388 p_creation_date,
389 p_created_by,
390 SYSDATE,
391 p_last_updated_by,
392 p_last_update_login,
393 -999
394 );
395
396 Get_Delivery(v_delivery_id, v_delivery_name);
397
398 IF ( length(p_waybill_num) > 30 ) THEN
399 p_waybill_num := substrb(p_waybill_num,1,30);
400 long_waybill_count := long_waybill_count + 1;
401 END IF;
402
403
404 INSERT INTO wsh_deliveries(
405 organization_id,
406 delivery_id,
407 name,
408 source_code,
409 planned_departure_id,
410 actual_departure_id,
411 status_code,
412 loading_order_flag,
413 date_closed,
414 report_set_id,
415 sequence_number,
416 customer_id,
417 ultimate_ship_to_id,
418 intermediate_ship_to_id,
419 pooled_ship_to_id,
420 waybill,
421 gross_weight,
422 weight_uom_code,
423 volume,
424 volume_uom_code,
425 picked_by_id,
426 packed_by_id,
427 expected_arrival_date,
428 asn_date_sent,
429 asn_seq_number,
430 attribute_category,
431 attribute1,
432 attribute2,
433 attribute3,
434 attribute4,
435 attribute5,
436 attribute6,
437 attribute7,
438 attribute8,
439 attribute9,
440 attribute10,
441 attribute11,
442 attribute12,
443 attribute13,
444 attribute14,
445 attribute15,
446 freight_carrier_code,
447 freight_terms_code,
448 currency_code,
449 fob_code,
450 creation_date,
451 created_by,
452 last_update_date,
453 last_updated_by,
454 last_update_login,
455 program_application_id
456 ) VALUES (
457 p_organization_id,
458 v_delivery_id,
459 v_delivery_name,
460 'S',
461 p_departure_id,
462 p_departure_id,
463 'CL',
464 NULL,
465 p_date_confirmed,
466 NULL,
467 NULL,
468 v_customer_id,
469 p_ship_to_site_use_id,
470 NULL,
471 NULL,
472 p_waybill_num,
473 p_weight,
474 p_weight_unit_code,
475 NULL,
476 NULL,
477 p_picked_by_id,
478 p_packed_by_id,
479 p_expected_arrival_date, -- added: for bug 1413000
480 NULL,
481 NULL,
482 p_context, -- attribute_category
483 p_attribute1,
484 p_attribute2,
485 p_attribute3,
486 p_attribute4,
487 p_attribute5,
488 p_attribute6,
489 p_attribute7,
490 p_attribute8,
491 p_attribute9,
492 p_attribute10,
493 p_attribute11,
494 p_attribute12,
495 p_attribute13,
496 p_attribute14,
497 p_attribute15,
498 p_ship_method_code,
499 v_freight_terms_code,
500 v_currency_code,
501 v_fob_code,
502 p_creation_date,
503 p_created_by,
504 SYSDATE,
505 p_last_updated_by,
506 p_last_update_login,
507 -999
508 );
509
510 RETURN v_delivery_id;
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 IF (get_picking_info%ISOPEN) THEN
515 CLOSE get_picking_info;
516 END IF;
517 IF (get_order_info%ISOPEN) THEN
518 CLOSE get_order_info;
519 END IF;
520 IF (count_shipped_pls%ISOPEN) THEN
521 CLOSE count_shipped_pls;
522 END IF;
526 commit;
523 Rollback; -- to savepoint before_insert; <bug 1475847>
524 Init_mesg('Error in creating delivery/departure for picking_header_id '||p_header_id);
525 Insert_mesg;
527 raise;
528 END Insert_Row;
529
530 PROCEDURE Upgrade_Rows ( num_rows IN BINARY_INTEGER,
531 total_workers IN BINARY_INTEGER,
532 worker IN BINARY_INTEGER,
533 batch_number IN BINARY_INTEGER
534 ) IS
535
536 /* for parallel execution */
537
538 l_job_min BINARY_INTEGER := 0;
539 l_job_max BINARY_INTEGER := 0;
540 l_lower_limit BINARY_INTEGER := 0;
541 l_upper_limit BINARY_INTEGER := 0;
542 l_worker_job_min BINARY_INTEGER := 0;
543 l_worker_job_max BINARY_INTEGER := 0;
544 l_commit_count BINARY_INTEGER := 0;
545 l_test NUMBER := 0;
546 u_picking_header_id BINARY_INTEGER;
547 u_delivery_id BINARY_INTEGER;
548 u_departure_id BINARY_INTEGER;
549 i BINARY_INTEGER;
550 j BINARY_INTEGER;
551 NULL_ERROR EXCEPTION;
552
553 CURSOR get_header_id(l_min BINARY_INTEGER, l_max BINARY_INTEGER) IS
554 SELECT picking_header_id
555 FROM SO_PICKING_HEADERS_ALL
556 WHERE
557 delivery_id is NULL
558 AND status_code = 'CLOSED'
559 AND picking_header_id BETWEEN l_min and l_max;
560
561 CURSOR get_any_row IS
562 SELECT 1
563 FROM so_picking_headers_all WHERE
564 delivery_id is NULL and
565 picking_header_id > 0 and
566 status_code = 'CLOSED';
567
568
569 BEGIN
570
571 long_waybill_count := 0;
572
573 IF num_rows IS NULL or num_rows = 0 THEN
574 RAISE NULL_ERROR;
575 END IF;
576
577 open get_any_row;
578 fetch get_any_row into l_test;
579 if get_any_row%FOUND then
580
581 /*=======================================================
582 This fix trifurcates the Picking Header ID Processing
583 as per the logic given below:
584
585 1. If wshrpupd.sql invokes this, batch_number = 0 and
586 only picking_header_ids from 1 to 9999 will be processed.
587
588 2. If wshrpupd01.sql invokes this, batch_number = 1 and
589 only picking_header_ids from 10000 to 99999999 will be
590 processed.
591
592 3. If wshrpupd02.sql invokes this, batch_number = 2 and
593 only picking_header_ids >= 100000000 will be processed.
594 ========================================================*/
595
596 IF batch_number = 0 THEN
597
598 SELECT NVL(MIN(picking_header_id),0), NVL(MAX(picking_header_id),0)
599 INTO l_job_min, l_job_max
600 FROM SO_PICKING_HEADERS_ALL
601 WHERE picking_header_id between 1 and 9999
602 AND delivery_id IS NULL
603 AND status_code = 'CLOSED';
604
605 ELSIF batch_number = 1 THEN
606
607 SELECT NVL(MIN(picking_header_id),0), NVL(MAX(picking_header_id),0)
608 INTO l_job_min, l_job_max
609 FROM SO_PICKING_HEADERS_ALL
610 WHERE picking_header_id between 10000 and 99999999
611 AND delivery_id IS NULL
612 AND status_code = 'CLOSED';
613
614 ELSIF batch_number = 2 THEN
615
616 SELECT NVL(MIN(picking_header_id),0), NVL(MAX(picking_header_id),0)
617 INTO l_job_min, l_job_max
618 FROM SO_PICKING_HEADERS_ALL
619 WHERE picking_header_id >= 100000000
620 AND delivery_id IS NULL
621 AND status_code = 'CLOSED';
622
623 END IF;
624
625
626 l_worker_job_min := l_job_min + TRUNC(((worker -1)*(l_job_max - l_job_min)) / total_workers);
627 l_worker_job_max := l_job_min + TRUNC((worker * (l_job_max - l_job_min)) / total_workers);
628
629 if worker <> total_workers then
630 l_worker_job_max := l_worker_job_max - 1;
631 end if;
632
633 Insert_Mesg('Worker: ' || to_char(worker) || ' processing picking_header_id ' ||
634 to_char(l_worker_job_min) || '..' || to_char(l_worker_job_max));
635 commit;
636
637 l_lower_limit := l_worker_job_min;
638 l_upper_limit := l_worker_job_min - 1;
639
640 -- Bug 1475858 : We need to close the cursor after the commit and re-open it to avoid Snapshot Too Old error.
641
642 LOOP
643 -- exit when upper limit already reach the max in previous round
644 EXIT WHEN l_upper_limit = l_worker_job_max;
645
646 -- set the upper limit for this round
647 if (l_worker_job_max - l_upper_limit) > num_rows then
648 l_upper_limit := l_upper_limit + num_rows;
649 else
650 l_upper_limit := l_worker_job_max;
651 end if;
652
653 Insert_Mesg('Upgrading picking header id:' || l_lower_limit ||' to '|| l_upper_limit);
654
655 OPEN get_header_id(l_lower_limit, l_upper_limit);
656 LOOP
657
658 FETCH get_header_id INTO u_picking_header_id;
659
660 EXIT WHEN get_header_id%NOTFOUND;
661
662 u_departure_id := NULL;
663 u_delivery_id := Insert_Row( u_picking_header_id, u_departure_id);
664
668 ELSE
665 IF (u_delivery_id = FAILURE ) OR (u_departure_id is NULL) THEN
666 Add_Mesg('Error: Failed to create departure or delivery for picking_header_id: ' ||u_picking_header_id||' , ignoring ...');
667 Insert_Mesg;
669 UPDATE SO_FREIGHT_CHARGES
670 SET delivery_id = u_delivery_id
671 WHERE picking_header_id = u_picking_header_id;
672
673 UPDATE SO_PICKING_HEADERS_ALL PH
674 SET PH.delivery_id = u_delivery_id
675 WHERE PH.picking_header_id = u_picking_header_id;
676
677 UPDATE SO_PICKING_LINE_DETAILS
678 SET delivery_id = u_delivery_id,
679 departure_id = u_departure_id,
680 dpw_assigned_flag = NULL
681 WHERE picking_line_detail_id IN
682 ( SELECT pld.picking_line_detail_id
683 FROM
684 so_picking_line_details pld,
685 so_picking_lines_all pl
686 WHERE pl.picking_header_id = u_picking_header_id
687 AND pld.picking_line_id = pl.picking_line_id );
688 END IF;
689
690
691 END LOOP;
692
696 l_lower_limit := l_lower_limit + num_rows;
693 CLOSE get_header_id;
694 commit;
695
697
698 END LOOP;
699 Insert_Mesg('Long waybill count : ' || to_char(long_waybill_count));
700 ELSE
701 Insert_Mesg('No closed row in table SO_PICKING_HEADERS_ALL, skipping Upgrade_Row');
702 END IF;
703 close get_any_row;
704 Commit;
705 EXCEPTION
706 WHEN NULL_ERROR THEN
707 rollback;
708 Init_Mesg('Number of rows to commit at a time cannot be NULL or zero');
709 Insert_Mesg;
710 commit;
711 WHEN OTHERS THEN
712 IF (get_header_id%ISOPEN) THEN
713 CLOSE get_header_id;
714 END IF;
715 ROLLBACK;
716 Init_Mesg( sqlerrm || ' ' || to_char(sqlcode));
717 Insert_Mesg;
718 commit;
719 raise;
720
721 END Upgrade_Rows;
722
723 END WSH_UPGRADE_PICK_SLIP_DATA_NEW;