1 PACKAGE BODY WSH_SC_DELIVERY_PVT as
2 /* $Header: WSHSDELB.pls 115.7 99/10/18 18:46:56 porting ship $ */
3
4 -- Function Name : Close_Delivery
5
6 -- Purpose :
7 -- To process Ship_all and Ship_entered actions on a Delivery
8
9 -- Parameters :
10 -- 1. Del_Id IN NUMBER
11 -- - delivery id we are working with
12 -- 2. Action_Code IN VARCHAR2
13 -- -ALL for Ship All
14 -- -ENTERED for Ship Entered
15
16 -- Return Value : BOOLEAN
17 --
18 FUNCTION Close_Delivery(Del_Id IN NUMBER,
19 Action_Code IN VARCHAR2,
20 default_fcc IN VARCHAR2,
21 default_bol IN VARCHAR2,
22 p_vehicle_item_id IN NUMBER DEFAULT NULL,
23 p_vehicle_number IN VARCHAR2 DEFAULT NULL,
24 p_seal_code IN VARCHAR2 DEFAULT NULL,
25 p_volume_uom IN VARCHAR2 DEFAULT NULL,
26 p_volume_total IN NUMBER DEFAULT NULL,
27 p_weight_uom IN VARCHAR2 DEFAULT NULL,
28 p_gross_wt IN NUMBER DEFAULT NULL,
29 p_tare_wt IN NUMBER DEFAULT NULL,
30 p_pack_instr IN VARCHAR2 DEFAULT NULL,
31 default_actual_date IN DATE DEFAULT SYSDATE)
32 RETURN BOOLEAN IS
33
34 --go ahead and declare some local variables to be used in the function
35
36 dep_id NUMBER;
37 org_id NUMBER;
38 freight_carrier VARCHAR2(30);
39 dep_freight_carrier VARCHAR2(30);
40 weight_uom VARCHAR2(3);
41 volume_uom VARCHAR2(3);
42 weight_of_delivery NUMBER;
43 volume_of_delivery NUMBER;
44 trans_temp_id NUMBER;
45 x_waybill VARCHAR2(30);
46 X_net_weight NUMBER;
47 X_tare_weight NUMBER;
48 X_volume NUMBER;
49 X_status NUMBER;
50 weight_volume_sc_flag VARCHAR2(1);
51 close_date DATE;
52 auto_created_dep BOOLEAN :=FALSE;
53 industry VARCHAR2(30);
54 status VARCHAR2(30);
55 return_msg1 VARCHAR2(2000);
56 BEGIN
57
58 -- If we have delivery lines that are not assicated with this delivery
59 -- associated with picking headers or picking lines that we are working
60 -- with then we need to first split these picking headers and picking lines
61 -- to kinda get rid of these delivery lines.
62 -- So go ahead and call the routine which splits the picking headers
63 -- and picking lines, if necessary
64
65 Split_Picking_Headers (del_id);
66
67
68 -- If a serial number range has been entered for the shipped quantity of the
69 -- item, then we need to explode the picking line details.
70 -- wsh_sc_pld_pkg (WShSCPDB.pls) is our guy to do this. He takes care
71 -- of exploding only those lines that need to be.
72
73 WSH_SC_PLD_PKG.Close_Details(del_id);
74
75 -- We are now ready to update the delivery status to closed
76 select sysdate into close_date from sys.dual;
77 UPDATE wsh_deliveries
78 SET status_code = 'CL',
79 date_closed = close_date
80 WHERE delivery_id = del_id;
81
82 -- if this delivery has not been associated with a departure yet
83 -- then we need to do an 'auto create departure' ie create a
84 -- departure for this delivery to be able to actually 'depart' !
85 -- so call Auto_Create_Departure
86
87 -- first check if we need to do it
88 SELECT actual_departure_id
89 ,organization_id
90 ,freight_carrier_code
91 ,weight_uom_code
92 ,volume_uom_code
93 ,gross_weight
94 ,volume
95 ,waybill
96 INTO dep_id
97 ,org_id
98 ,freight_carrier
99 ,weight_uom
100 ,volume_uom
101 ,weight_of_delivery
102 ,volume_of_delivery
103 ,x_waybill
104 FROM wsh_deliveries
105 WHERE delivery_id = del_id;
106 -- if freight carrier at the delivery is null, update it from the departure
107 IF freight_carrier IS NULL and dep_id IS NOT NULL THEN
108 SELECT freight_carrier_code
109 INTO dep_freight_carrier
110 FROM wsh_departures
111 WHERE departure_id = dep_id;
112
113 UPDATE wsh_deliveries
114 SET freight_carrier_code = dep_freight_carrier
115 WHERE delivery_id = del_id;
116 END IF;
117
118 IF dep_id is NULL THEN
119
120 dep_id := Auto_Create_Departure ( org_id,
121 NVL( default_fcc, freight_carrier),
122 NVL( p_weight_uom, weight_uom),
123 NVL( p_volume_uom, volume_uom),
124 NVL( p_gross_wt, weight_of_delivery),
125 p_tare_wt,
126 NVL( p_volume_total, volume_of_delivery),
127 p_vehicle_item_id,
128 p_vehicle_number,
129 p_seal_code,
130 p_pack_instr,
131 default_bol,
132 default_actual_date );
133
134 -- now update the actual departure id on this delivery with the new departure created
135 -- and the departure id on the delivery lines. Also call the ASN API for ASN integration
136
137 IF dep_id > 0 THEN
138
139 UPDATE wsh_deliveries
140 SET actual_departure_id = dep_id,
141 freight_carrier_code = NVL(default_fcc, freight_carrier)
142 WHERE delivery_id = del_id;
143
144 auto_created_dep := TRUE;
145 WSH_PARAMETERS_PVT.get_param_value(org_id, 'WEIGHT_VOLUME_SC_FLAG',
146 weight_volume_sc_flag);
147 if (weight_volume_sc_flag = 'A') then
148 wsh_wv_pvt.departure_weight_volume(
149 source => 'SC',
150 departure_id => dep_id,
151 organization_id => org_id,
152 wv_flag => 'ALWAYS',
153 update_flag => 'Y',
154 menu_flag => 'N',
155 dpw_pack_flag => 'N',
156 master_weight_uom => weight_uom,
157 net_weight => X_net_weight,
158 tare_weight => X_tare_weight,
159 master_volume_uom => volume_uom,
160 volume => X_volume,
161 status => X_status);
162 if (X_status = 0) then
163
164 update wsh_departures
165 set net_weight = X_net_weight,
166 fill_percent = 0
167 where departure_id = dep_id;
168 end if;
169
170 end if;
171
172
173 -- check if EDI product is installed
174 IF (edi_installed_flag = 'U') THEN
175 edi_installed_flag := 'N';
176 IF (fnd_installation.get(175, 175, status, industry)) THEN
177 IF (status = 'I') THEN
178 edi_installed_flag := 'Y';
179 END IF;
180 END IF;
181 END IF;
182
183 /*now call the ASN package
184 IF (edi_installed_flag = 'Y') THEN
185 ece_dsno.export_deliveries_api(dep_id);
186 END IF;
187 */
188
189 END IF;
190 END IF;
191
192 --We will delay the update the status of the picking_header_id
193 --in so_picking_headers_all until the departure is closed
194
195 IF ( auto_created_dep ) THEN
196 UPDATE so_picking_headers_all
197 SET waybill_num = x_waybill,
198 STATUS_CODE = 'PENDING'
199 WHERE delivery_id = del_id;
200 ELSE
201 UPDATE so_picking_headers_all
202 SET waybill_num = x_waybill
203 WHERE delivery_id = del_id;
204 END IF;
205
206 -- make departure id in so_picking_line_details in correct
207 UPDATE so_picking_line_details
208 SET departure_id = dep_id
209 WHERE delivery_id = del_id;
210
211 -- clear any serial number entered in MTL_SERIAL_NUMBERS_TEMP
212 wsh_sc_pld_pkg.delete_from_msnt(del_id);
213
214
215 -- if we come here with no errors then we are ready to return back to the client
216 -- with a value of 'True'
217 RETURN TRUE;
218
219 -- Handle any exceptions
220 Exception
221
222 WHEN OTHERS THEN
223 return_msg1 := FND_MESSAGE.get;
224 wsh_del_oi_core.println('msg ='|| return_msg1);
225 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
226 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_DELIVERY_PVT.Close_Delivery');
227 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
228 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
229 APP_EXCEPTION.Raise_Exception;
230 return FALSE;
231
232 END Close_Delivery;
233
234 FUNCTION Print_Shipping_Doc_Set( x_del_id IN NUMBER,
235 x_doc_set_id IN NUMBER,
236 x_return_msg OUT VARCHAR2 )
237 RETURN BOOLEAN IS
238 return_code BOOLEAN;
239 return_message VARCHAR2(2000);
240 return_msg1 VARCHAR2(2000);
241 x_org_id NUMBER DEFAULT NULL;
242 x_report_set_id NUMBER;
243 CURSOR c1( x_del_id NUMBER) IS
244 SELECT report_set_id, organization_id
245 FROM wsh_deliveries
246 WHERE delivery_id = x_del_id;
247 invalid_parameters EXCEPTION;
248 BEGIN
249 IF ( x_del_id IS NULL ) THEN
250 RAISE invalid_parameters;
251 END IF;
252 x_report_set_id := x_doc_set_id;
253 IF ( NVL(x_report_set_id,-1) <= 0) THEN
254 OPEN c1( x_del_id);
255 FETCH c1 INTO x_report_set_id, x_org_id;
256 IF ( c1%NOTFOUND ) THEN
257 RAISE invalid_parameters;
258 END IF;
259 IF ( c1%ISOPEN) THEN
260 CLOSE c1;
261 END IF;
262 END IF;
263 IF ( x_report_set_id IS NULL ) THEN
264 FND_MESSAGE.SET_NAME('OE','WSH_NO_DOCS');
265 return_message := FND_MESSAGE.GET;
266 x_return_msg := return_message;
267 RETURN TRUE;
268 END IF;
269
270 WSH_DOC_SETS.Print_Document_Sets (
271 X_report_set_id => x_report_set_id,
272 P_DELIVERY_ID => TO_CHAR(x_del_id),
273 P_ORGANIZATION_ID => TO_CHAR(x_org_id),
274 P_WAREHOUSE_ID => TO_CHAR(x_org_id),
275 message_string => return_message,
276 status => return_code );
277 x_return_msg := return_message;
278 RETURN return_code;
279 EXCEPTION
280 WHEN invalid_parameters THEN
281 IF ( c1%ISOPEN) THEN
282 CLOSE c1;
283 END IF;
284 FND_MESSAGE.SET_NAME('OE','WSH_SC_INVALID_PARA');
285 return_message := FND_MESSAGE.GET;
286 x_return_msg := return_message;
287 RETURN FALSE;
288 WHEN others THEN
289 return_msg1 := FND_MESSAGE.get;
290 wsh_del_oi_core.println('msg ='|| return_msg1);
291 IF ( c1%ISOPEN) THEN
292 CLOSE c1;
293 END IF;
294 x_return_msg := return_message;
295 RETURN FALSE;
296 END Print_Shipping_Doc_Set;
297
298 -- Function Name : Backorder_Delivery
299
300 -- Purpose :
301 -- To process Backorder_all action on a Delivery
302
303 -- Parameters :
304 -- 1. Del_Id IN NUMBER
305 -- - delivery id we are working with
306 -- 2. Process_Online IN VARCHAR2
307 -- Y - run Update Shipping and Inventory Interface Online
308 -- N - do not run Update Shipping and Inventory Interface Online
309
310 -- Return Value : BOOLEAN
311 --
312 FUNCTION Backorder_Delivery( Del_Id IN NUMBER)
313 RETURN BOOLEAN IS
314 CURSOR c1( x_del_id NUMBER) IS
315 SELECT actual_departure_id
316 FROM wsh_deliveries
317 WHERE delivery_id = x_del_id;
318
319 dep_id NUMBER;
320 return_msg1 varchar2(2000);
321 BEGIN
322
323 -- update shipped quantity in so_picking_line_details
324 wsh_sc_del_lines.update_shp_qty (del_id, 'BACKORDER_ALL');
325
326 -- clear any charges entered in SO_FREIGHT_CHARGES
327 delete from so_freight_charges
328 where delivery_id = del_id;
329
330 -- clear any container entered in WSH_PACKED_CONTAINERS
331 delete from wsh_packed_containers
332 where delivery_id = del_id;
333
334 OPEN c1(del_id);
335 FETCH c1 INTO dep_id;
336 IF ( c1%ISOPEN) THEN
337 CLOSE c1;
338 END IF;
339
340 UPDATE so_picking_line_details
341 SET departure_id = dep_id,
342 container_id = NULL,
343 shipped_quantity = 0
344 WHERE delivery_id = del_id;
345
346 --call the server routine to unassign the unreleased delivery
347 --lines from this delivery
348 wsh_sc_del_lines.update_unrel_lines(del_id);
349
350 -- Split the picking headers, picking lines and update the
351 -- shipped quantity in picking lines
352
353 split_picking_headers (del_id);
354
355
356 -- update the delivery as 'CB' /* close backordered - is it CB or BO ? -open issue */
357
358 UPDATE so_picking_headers_all
359 SET status_code = 'PENDING'
360 WHERE picking_header_id in
361 (SELECT distinct pl.picking_header_id
362 FROM so_picking_lines_all pl, so_picking_line_details pld
363 WHERE pld.delivery_id = del_id
364 AND pl.picking_line_id = pld.picking_line_id
365 AND pl.picking_header_id+0 > 0);
366
367 update wsh_deliveries
368 set status_code = 'CB'
369 where delivery_id = del_id;
370
371 return TRUE;
372
373 Exception
374 WHEN OTHERS THEN
375 return_msg1 := FND_MESSAGE.get;
376 wsh_del_oi_core.println('msg ='|| return_msg1);
377 IF ( c1%ISOPEN) THEN
378 CLOSE c1;
379 END IF;
380 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
381 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_DELIVERY_PVT.Backorder_Delivery');
382 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
383 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
384 APP_EXCEPTION.Raise_Exception;
385 return FALSE;
386
387 END Backorder_Delivery;
388
389 -- Function Name : Unpack_Delivery
390
391 -- Purpose :
392 -- To process the unpacking action on a Delivery
393
394 -- Parameters :
395 -- 1. Del_Id IN NUMBER
396 -- - delivery id we are working with
397
398 -- Return Value : BOOLEAN
399 --
400 FUNCTION Unpack_Delivery (Del_Id IN NUMBER) RETURN BOOLEAN IS
401
402 Source Varchar2(1);
403
404 BEGIN
405
406 --We need to take out the containers off the Delivery Lines and set the Shipped
407 --quantity to null on them.
408
409 UPDATE so_picking_line_details
410 SET container_id = null
411 WHERE delivery_id = Del_Id;
412
413 --Now look at the souce code of the delivery ie who created it - DPW or SC
414 SELECT source_code
415 INTO Source
416 FROM wsh_deliveries
417 WHERE delivery_id = Del_Id;
418
419 --Depending on the source, update the delivery status
420
421 IF Source = 'D' THEN
422 UPDATE wsh_deliveries
423 SET status_code = 'PL'
424 WHERE delivery_id = Del_Id;
425 ELSIF Source = 'S' THEN
426 UPDATE wsh_deliveries
427 SET status_code = 'OP'
428 WHERE delivery_id = Del_Id;
429 END IF;
430
431 RETURN TRUE;
432
433 --handle any exceptions
434 Exception
435
436 WHEN OTHERS THEN
437 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
438 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_DELIVERY_PVT.Unpack_Delivery');
439 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
440 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
441 APP_EXCEPTION.Raise_Exception;
442 return FALSE;
443
444 END Unpack_Delivery;
445
446 -- Procedure Name : Update_Del_Status
447
448 -- Purpose :
449 -- Updates the status code on a delivery to the required value
450
451 -- Parameters :
452 -- 1. Del_Id IN NUMBER
453 -- - delivery id we are working with
454 -- 2. Del_Status_Code
455 -- - status to which we want the delivery to be updated
456
457 PROCEDURE Update_Del_Status( Del_Id IN NUMBER,
458 Del_Status_Code IN VARCHAR2) IS
459 BEGIN
460
461 --just a simple and stupid statement to update the delivery status
462 UPDATE wsh_deliveries
463 SET status_code = del_status_code
464 WHERE delivery_id = Del_Id;
465
466 END Update_Del_Status;
467
468
469 -- Procedure Name : Insert_Ph_Row
470
471 -- Purpose :
472 -- Inserts a New Picking Header record in so_picking_headers
473
474 -- Parameters :
475 -- 1. Ph_Id IN NUMBER
476 -- - Picking Header record from which the new picking header
477 -- record is to be created
478 -- 2. New_Ph_Id IN NUMBER
479 -- - Picking Header Id for the New picking header record
480 PROCEDURE Insert_Ph_Row ( Ph_Id IN NUMBER,
481 New_Ph_Id IN NUMBER) IS
482 BEGIN
483
484 --Go ahead and insert a record in so_picking_headers_all
485 INSERT INTO so_picking_headers_all
486 ( PICKING_HEADER_ID
487 ,CREATION_DATE
488 ,CREATED_BY
489 ,LAST_UPDATE_DATE
490 ,LAST_UPDATED_BY
491 ,LAST_UPDATE_LOGIN
492 ,PROGRAM_APPLICATION_ID
493 ,PROGRAM_ID
494 ,PROGRAM_UPDATE_DATE
495 ,REQUEST_ID
496 ,BATCH_ID
497 ,ORDER_HEADER_ID
498 ,WAREHOUSE_ID
499 ,SHIP_TO_SITE_USE_ID
500 ,STATUS_CODE
501 ,PICK_SLIP_NUMBER
502 ,WAYBILL_NUM
503 ,PICKED_BY_ID
504 ,PACKED_BY_ID
505 ,WEIGHT
506 ,WEIGHT_UNIT_CODE
507 ,NUMBER_OF_BOXES
508 ,SHIP_METHOD_CODE
509 ,DATE_RELEASED
510 ,DATE_SHIPPED
511 ,DATE_CONFIRMED
512 ,CONTEXT
513 ,ATTRIBUTE1
514 ,ATTRIBUTE2
515 ,ATTRIBUTE3
516 ,ATTRIBUTE4
517 ,ATTRIBUTE5
518 ,ATTRIBUTE6
519 ,ATTRIBUTE7
520 ,ATTRIBUTE8
521 ,ATTRIBUTE9
522 ,ATTRIBUTE10
523 ,ATTRIBUTE11
524 ,ATTRIBUTE12
525 ,ATTRIBUTE13
526 ,ATTRIBUTE14
527 ,ATTRIBUTE15
528 ,EXPECTED_ARRIVAL_DATE
529 ,ORG_ID
530 ,SHIP_NOTICE_SENT_DATE
531 ,SHIP_NOTICE_SENT_FLAG
532 ,DELIVERY_ID
533 ,ARRIVED_FLAG )
534 SELECT
535 new_ph_id
536 ,SYSDATE
537 ,fnd_global.user_id
538 ,SYSDATE
539 ,fnd_global.user_id
540 ,fnd_global.login_id
541 ,PROGRAM_APPLICATION_ID
542 ,PROGRAM_ID
543 ,PROGRAM_UPDATE_DATE
544 ,REQUEST_ID
545 ,BATCH_ID
546 ,ORDER_HEADER_ID
547 ,WAREHOUSE_ID
548 ,SHIP_TO_SITE_USE_ID
549 ,STATUS_CODE
550 ,PICK_SLIP_NUMBER
551 ,WAYBILL_NUM
552 ,PICKED_BY_ID
553 ,PACKED_BY_ID
554 ,WEIGHT
555 ,WEIGHT_UNIT_CODE
556 ,NUMBER_OF_BOXES
557 ,SHIP_METHOD_CODE
558 ,DATE_RELEASED
559 ,DATE_SHIPPED
560 ,DATE_CONFIRMED
561 ,CONTEXT
562 ,ATTRIBUTE1
563 ,ATTRIBUTE2
564 ,ATTRIBUTE3
565 ,ATTRIBUTE4
566 ,ATTRIBUTE5
567 ,ATTRIBUTE6
568 ,ATTRIBUTE7
569 ,ATTRIBUTE8
570 ,ATTRIBUTE9
571 ,ATTRIBUTE10
572 ,ATTRIBUTE11
573 ,ATTRIBUTE12
574 ,ATTRIBUTE13
575 ,ATTRIBUTE14
576 ,ATTRIBUTE15
577 ,EXPECTED_ARRIVAL_DATE
578 ,ORG_ID
579 ,SHIP_NOTICE_SENT_DATE
580 ,SHIP_NOTICE_SENT_FLAG
581 ,DELIVERY_ID
582 ,ARRIVED_FLAG
583 FROM so_picking_headers_all
584 WHERE picking_header_id = Ph_Id;
585
586 END Insert_Ph_Row;
587
588 -- Procedure Name : Insert_Pl_Row
589
590 -- Purpose :
591 -- Inserts a new picking line record in so_picking_lines_all
592
593 -- Parameters :
594 -- 1. Pl_Id IN NUMBER
595 -- - picking line record from which the new picking line
596 -- record is created
597 -- 2. New_Pl_Id IN NUMBER
598 -- - picking line id for the new picking line record
599 -- 3. New_Ph_Id IN NUMBER
600 -- - Picking Header Id for the New picking header record
601 PROCEDURE Insert_Pl_Row ( Pl_Id IN NUMBER,
602 New_Pl_Id IN NUMBER,
603 New_Ph_Id IN NUMBER) IS
604 BEGIN
605
606 --Insert a row in so_picking_lines_all
607 INSERT INTO so_picking_lines_all
608 ( PICKING_LINE_ID
609 ,CREATION_DATE
610 ,CREATED_BY
611 ,LAST_UPDATE_DATE
612 ,LAST_UPDATED_BY
613 ,LAST_UPDATE_LOGIN
614 ,PROGRAM_APPLICATION_ID
615 ,PROGRAM_ID
616 ,PROGRAM_UPDATE_DATE
617 ,REQUEST_ID
618 ,PICKING_HEADER_ID
619 ,SEQUENCE_NUMBER
620 ,ORDER_LINE_ID
621 ,COMPONENT_CODE
622 ,LINE_DETAIL_ID
623 ,COMPONENT_RATIO
624 ,REQUESTED_QUANTITY
625 ,INVENTORY_ITEM_ID
626 ,INCLUDED_ITEM_FLAG
627 ,DATE_REQUESTED
628 ,ORIGINAL_REQUESTED_QUANTITY
629 ,WAREHOUSE_ID
630 ,SHIPPED_QUANTITY
631 ,CANCELLED_QUANTITY
632 ,SHIP_TO_SITE_USE_ID
633 ,SHIP_TO_CONTACT_ID
634 ,SHIPMENT_PRIORITY_CODE
635 ,SHIP_METHOD_CODE
636 ,DATE_CONFIRMED
637 ,RA_INTERFACE_STATUS
638 ,INVOICED_QUANTITY
639 ,INVENTORY_STATUS
640 ,UNIT_CODE
641 ,CONTEXT
642 ,ATTRIBUTE1
643 ,ATTRIBUTE2
644 ,ATTRIBUTE3
645 ,ATTRIBUTE4
646 ,ATTRIBUTE5
647 ,ATTRIBUTE6
648 ,ATTRIBUTE7
649 ,ATTRIBUTE8
650 ,ATTRIBUTE9
651 ,ATTRIBUTE10
652 ,ATTRIBUTE11
653 ,ATTRIBUTE12
654 ,ATTRIBUTE13
655 ,ATTRIBUTE14
656 ,ATTRIBUTE15
657 ,SCHEDULE_DATE
658 ,DEMAND_CLASS_CODE
659 ,COMPONENT_SEQUENCE_ID
660 ,CONFIGURATION_ITEM_FLAG
661 ,LATEST_ACCEPTABLE_DATE
662 ,MOVEMENT_ID
663 ,ORG_ID
664 ,TRANSACTION_HEADER_ID
665 ,SERVICE_INTERFACE_STATUS
666 ,BO_PICKING_LINE_ID
667 ,DEP_PLAN_REQUIRED_FLAG
668 ,CUSTOMER_ITEM_ID )
669 SELECT
670 new_pl_id
671 ,SYSDATE
672 ,fnd_global.user_id
673 ,SYSDATE
674 ,fnd_global.user_id
675 ,fnd_global.login_id
676 ,PROGRAM_APPLICATION_ID
677 ,PROGRAM_ID
678 ,PROGRAM_UPDATE_DATE
679 ,REQUEST_ID
680 ,new_ph_id
681 ,SEQUENCE_NUMBER
682 ,ORDER_LINE_ID
683 ,COMPONENT_CODE
684 ,LINE_DETAIL_ID
685 ,COMPONENT_RATIO
686 ,REQUESTED_QUANTITY
687 ,INVENTORY_ITEM_ID
688 ,INCLUDED_ITEM_FLAG
689 ,DATE_REQUESTED
690 ,ORIGINAL_REQUESTED_QUANTITY
691 ,WAREHOUSE_ID
692 ,SHIPPED_QUANTITY
693 ,CANCELLED_QUANTITY
694 ,SHIP_TO_SITE_USE_ID
695 ,SHIP_TO_CONTACT_ID
696 ,SHIPMENT_PRIORITY_CODE
697 ,SHIP_METHOD_CODE
698 ,DATE_CONFIRMED
699 ,RA_INTERFACE_STATUS
700 ,INVOICED_QUANTITY
701 ,INVENTORY_STATUS
702 ,UNIT_CODE
703 ,CONTEXT
704 ,ATTRIBUTE1
705 ,ATTRIBUTE2
706 ,ATTRIBUTE3
707 ,ATTRIBUTE4
708 ,ATTRIBUTE5
709 ,ATTRIBUTE6
710 ,ATTRIBUTE7
711 ,ATTRIBUTE8
712 ,ATTRIBUTE9
713 ,ATTRIBUTE10
714 ,ATTRIBUTE11
715 ,ATTRIBUTE12
716 ,ATTRIBUTE13
717 ,ATTRIBUTE14
718 ,ATTRIBUTE15
719 ,SCHEDULE_DATE
720 ,DEMAND_CLASS_CODE
721 ,COMPONENT_SEQUENCE_ID
722 ,CONFIGURATION_ITEM_FLAG
723 ,LATEST_ACCEPTABLE_DATE
724 ,MOVEMENT_ID
725 ,ORG_ID
726 ,TRANSACTION_HEADER_ID
727 ,SERVICE_INTERFACE_STATUS
728 ,BO_PICKING_LINE_ID
729 ,DEP_PLAN_REQUIRED_FLAG
730 ,CUSTOMER_ITEM_ID
731 FROM so_picking_lines_all
732 WHERE picking_line_id = Pl_Id;
733
734 END Insert_Pl_Row;
735
736 -- Procedure Name : Split_Picking_Headers
737
738 -- Purpose :
739 -- Splits Picking Headers and associated Picking Lines if
740 -- necessary while closing a delivery.
741
742 -- Parameters :
743 -- 1. Del_Id IN NUMBER
744 -- - delivery id we are working with
745 PROCEDURE Split_Picking_Headers (Del_Id IN NUMBER) IS
746
747 --declare some local variables
748 ph_id NUMBER;
749 new_ph_id NUMBER;
750 pl_id NUMBER;
751 new_pl_id NUMBER;
752 num_found NUMBER;
753 shp_qty NUMBER;
754 req_qty NUMBER;
755
756 --declare the picking header cursor
757 CURSOR c1(del_id NUMBER) IS
758 SELECT distinct pl.picking_header_id picking_header_id
759 FROM so_picking_lines_all pl, so_picking_line_details pld
760 WHERE pld.delivery_id = del_id
761 AND pld.picking_line_id = pl.picking_line_id
762 AND pl.picking_header_id > 0;
763
764 --declare the picking_lines_cursor
765 CURSOR c2(ph_id NUMBER) IS
766 SELECT picking_line_id
767 FROM so_picking_lines_all
768 WHERE picking_header_id = ph_id;
769
770 BEGIN
771
772 -- Split the picking headers, picking lines and update the shipped
773 -- quantity in picking lines
774
775 OPEN c1 (del_id);
776 Fetch c1 into ph_id ;
777
778 WHILE c1%FOUND LOOP
779 --begin picking header id loop
780
781 --update the delivery_id on so_picking_headers_all
782 UPDATE so_picking_headers_all
783 SET delivery_id = del_id
784 WHERE picking_header_id = ph_id;
785
786 SELECT COUNT(*)
787 into num_found
788 FROM so_picking_line_details pld, so_picking_lines_all pl
789 WHERE pl.picking_header_id = ph_id
790 AND pl.picking_line_id = pld.picking_line_id
791 AND ( pld.delivery_id <> del_id OR
792 pld.delivery_id IS NULL ) ;
793
794 IF num_found > 0 then
795 --insert a new row into so_picking_headers_all by using the row with
796 --picking_header_id = ph_id - except who info.
797
798 --store the new picking header id as new_ph_id
799 select so_picking_headers_s.nextval
800 into new_ph_id
801 from dual;
802
803 insert_ph_row (ph_id, new_ph_id);
804
805 UPDATE so_picking_headers_all
806 set delivery_id = NULL
807 where picking_header_id = ph_id;
808
809 OPEN c2 (ph_id);
810 Fetch c2 into pl_id;
811
812 WHILE c2%FOUND LOOP
813 -- begin picking line loop
814
815 SELECT COUNT(*)
816 INTO num_found
817 FROM so_picking_line_details
818 WHERE picking_line_id = pl_id
819 AND ( delivery_id <> del_id OR
820 delivery_id IS NULL ) ;
821
822 IF num_found > 0 then -- outer if condition
823
824 SELECT COUNT(*)
825 INTO num_found
826 FROM so_picking_line_details
827 WHERE picking_line_id = pl_id
828 AND delivery_id = del_id;
829
830 IF num_found > 0 then -- inner if condition
831
832 --insert a new row into so_picking_lines_all by using the row with
833 --picking_line_id = pl_id and ph_id = new ph id (except the
834 --requested_quantity, shipped_quantity, and who info).
835
836 --get the new picking line id
837 select so_picking_lines_s.nextval
838 into new_pl_id
839 from dual;
840
841 insert_pl_row (pl_id, new_pl_id, new_ph_id);
842
843
844 UPDATE so_picking_line_details
845 SET picking_line_id = new_pl_id
846 WHERE picking_line_id = pl_id
847 AND delivery_id = del_id;
848
849 -- now update the shipped and requested quantities on this new picking line
850 SELECT sum(nvl(shipped_quantity,0)), sum(nvl(requested_quantity,0))
851 INTO shp_qty, req_qty
852 FROM so_picking_line_details
853 WHERE picking_line_id = new_pl_id
854 GROUP BY picking_line_id;
855
856 UPDATE so_picking_lines_all
857 SET shipped_quantity = shp_qty,
858 requested_quantity = req_qty,
859 original_requested_quantity = req_qty
860 where picking_line_id = new_pl_id;
861
862 -- also update the shipped and requested quantities on the old picking line
863 SELECT sum(nvl(shipped_quantity,0)), sum(nvl(requested_quantity,0))
864 INTO shp_qty, req_qty
865 FROM so_picking_line_details
866 WHERE picking_line_id = pl_id
867 GROUP BY picking_line_id;
868
869 UPDATE so_picking_lines_all
870 SET shipped_quantity = shp_qty,
871 requested_quantity = req_qty,
872 original_requested_quantity = req_qty
873 where picking_line_id = pl_id;
874
875 -- ELSE
876
877 -- UPDATE so_picking_lines_all
878 -- SET picking_header_id = new_ph_id
879 -- WHERE picking_line_id = pl_id;
880
881
882 END IF; -- inner if condition
883 ELSE
884
885 UPDATE so_picking_lines_all
886 SET picking_header_id = new_ph_id
887 WHERE picking_line_id = pl_id;
888
889 END IF; -- outer if condition
890
891 -- fetch the next record
892 Fetch c2 into pl_id;
893
894 END LOOP ; -- picking lines loop
895
896 Close c2; -- close the picking line id cursor
897
898 END IF;
899
900 -- fetch the next record
901 Fetch c1 into ph_id ;
902 END LOOP; -- picking header id loop
903
904 Close c1; -- close the picking header id cursor
905
906 END Split_Picking_Headers;
907
908 -- Function Name : Auto_Create_Departure
909
910 -- Purpose :
911 -- To Create a departure and assign it to a delivery if the
912 -- delivery is not already associated with a departure
913
914 -- Parameters :
915 -- All the parameters refer to the values on the delivery
916 -- for which we are creating the departure
917 -- 1. Org_Id IN Number
918 -- 2. Freight_Carrier IN Varchar2(30)
919 -- 3. Weight_UOM IN Varchar2(3)
920 -- 4. Volume_UOM IN Varchar2(3)
921 -- 5. Weight_of_Delivery IN Number
922 -- 6. Volume_of_Delivery IN Number
923
924 -- Return Value : Number - the departure id of the created
925 -- departure
926 --
927 FUNCTION Auto_Create_Departure (
928 Org_Id IN Number,
929 Freight_Carrier IN Varchar2,
930 Weight_UOM IN Varchar2,
931 Volume_UOM IN Varchar2,
932 Weight_of_Delivery IN Number,
933 p_tare_wt IN Number,
934 Volume_of_Delivery IN Number,
935 p_vehicle_item_id IN Number,
936 p_vehicle_number IN Varchar2,
937 p_seal_code IN Varchar2,
938 p_pack_instr IN Varchar2,
939 bol IN VARCHAR2,
940 actual_date IN DATE DEFAULT SYSDATE,
941 dep_name IN VARCHAR2 DEFAULT NULL)
942 RETURN NUMBER IS
943
944 -- declare local variables
945 dep_id Number;
946 x_bol WSH_DEPARTURES.BILL_OF_LADING%TYPE;
947 rep_id number;
948 BEGIN
949
950 -- Get the new departure id from the sequence
951 select WSH_DEPARTURES_S.nextval into dep_id
952 FROM DUAL;
953
954 IF ( bol IS NULL) THEN
955 x_bol := WSH_External_Custom.Bill_Of_Lading( dep_id);
956 ELSE
957 x_bol := bol;
958 END IF;
959
960 wsh_parameters_pvt.get_param_value_num(org_id,'DEPARTURE_REPORT_SET_ID',rep_id);
961
962
963 -- Now go ahead and to the Insert
964
965 INSERT INTO WSH_DEPARTURES (
966 ORGANIZATION_ID
967 ,DEPARTURE_ID
968 ,NAME
969 ,SOURCE_CODE
970 ,ARRIVE_AFTER_DEPARTURE_ID
971 ,STATUS_CODE
972 ,REPORT_SET_ID
973 ,DATE_CLOSED
974 ,VEHICLE_ITEM_ID
975 ,VEHICLE_NUMBER
976 ,FREIGHT_CARRIER_CODE
977 ,PLANNED_DEPARTURE_DATE
978 ,ACTUAL_DEPARTURE_DATE
979 ,BILL_OF_LADING
980 ,GROSS_WEIGHT
981 ,NET_WEIGHT
982 ,WEIGHT_UOM_CODE
983 ,VOLUME
984 ,VOLUME_UOM_CODE
985 ,FILL_PERCENT
986 ,SEAL_CODE
987 ,ROUTING_INSTRUCTIONS
988 ,ATTRIBUTE_CATEGORY
989 ,ATTRIBUTE1
990 ,ATTRIBUTE2
991 ,ATTRIBUTE3
992 ,ATTRIBUTE4
993 ,ATTRIBUTE5
994 ,ATTRIBUTE6
995 ,ATTRIBUTE7
996 ,ATTRIBUTE8
997 ,ATTRIBUTE9
998 ,ATTRIBUTE10
999 ,ATTRIBUTE11
1000 ,ATTRIBUTE12
1001 ,ATTRIBUTE13
1002 ,ATTRIBUTE14
1003 ,ATTRIBUTE15
1004 ,CREATION_DATE
1005 ,CREATED_BY
1006 ,LAST_UPDATE_DATE
1007 ,LAST_UPDATED_BY
1008 ,LAST_UPDATE_LOGIN
1009 ,PROGRAM_APPLICATION_ID
1010 ,PROGRAM_ID
1011 ,PROGRAM_UPDATE_DATE
1012 ,REQUEST_ID )
1013 VALUES
1014 (
1015 Org_Id /*ORGANIZATION_ID */
1016 ,dep_id /* DEPARTURE_ID */
1017 ,NVL( dep_name, to_char(dep_id)) /* NAME */
1018 ,'S' /* SOURCE_CODE */
1019 ,NULL /* ARRIVE_AFTER_DEPARTURE_ID */
1020 ,'CL' /* STATUS_CODE */
1021 ,rep_id /* REPORT_SET_ID */
1022 ,SYSDATE /* DATE_CLOSED */
1023 ,p_vehicle_item_id /* VEHICLE_ITEM_ID */
1024 ,p_vehicle_number /* VEHICLE_NUMBER */
1025 ,Freight_Carrier /* FREIGHT_CARRIER_CODE */
1026 ,SYSDATE /* PLANNED_DEPARTURE_DATE */
1027 ,NVL( actual_date, SYSDATE) /* ACTUAL_DEPARTURE_DATE */
1028 ,x_bol
1029 ,Weight_of_Delivery /* GROSS_WEIGHT */
1030 ,Weight_of_delivery - p_tare_wt /* NET_WEIGHT */
1031 ,Weight_UOM /* WEIGHT_UOM_CODE */
1032 ,Volume_of_Delivery /* VOLUME */
1033 ,Volume_UOM /* VOLUME_UOM_CODE */
1034 ,NULL /* FILL_PERCENT */
1035 ,p_seal_code /* SEAL_CODE */
1036 ,p_pack_instr /* ROUTING_INSTRUCTIONS */
1037 ,NULL /* ATTRIBUTE_CATEGORY */
1038 ,NULL /* ATTRIBUTE1 */
1039 ,NULL /* ATTRIBUTE2 */
1040 ,NULL /* ATTRIBUTE3 */
1041 ,NULL /* ATTRIBUTE4 */
1042 ,NULL /* ATTRIBUTE5 */
1043 ,NULL /* ATTRIBUTE6 */
1044 ,NULL /* ATTRIBUTE7 */
1045 ,NULL /* ATTRIBUTE8 */
1046 ,NULL /* ATTRIBUTE9 */
1047 ,NULL /* ATTRIBUTE10 */
1048 ,NULL /* ATTRIBUTE11 */
1049 ,NULL /* ATTRIBUTE12 */
1050 ,NULL /* ATTRIBUTE13 */
1051 ,NULL /* ATTRIBUTE14 */
1052 ,NULL /* ATTRIBUTE15 */
1053 ,SYSDATE /* CREATION_DATE */
1054 ,FND_GLOBAL.User_Id /* CREATED_BY */
1055 ,SYSDATE /* LAST_UPDATE_DATE */
1056 ,FND_GLOBAL.User_Id /* LAST_UPDATED_BY */
1057 ,FND_GLOBAL.Login_Id /* LAST_UPDATE_LOGIN */
1058 ,300 /* PROGRAM_APPLICATION_ID - 300 for order entry */
1059 ,NULL /* PROGRAM_ID */
1060 ,SYSDATE /* PROGRAM_UPDATE_DATE */
1061 ,NULL /* REQUEST_ID */
1062 );
1063
1064 Return Dep_Id;
1065
1066 --handle any exceptions
1067
1068 Exception
1069
1070 WHEN OTHERS THEN
1071 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1072 FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_DELIVERY_PVT.Auto_Create_Departure');
1073 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1074 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1075 APP_EXCEPTION.Raise_Exception;
1076 Return -1;
1077
1078 END Auto_Create_Departure;
1079
1080 -- Function Name : Delete_Container_Contents
1081
1082 -- Purpose :
1083
1084 -- Parameters : X_Contaier_Id IN Number
1085
1086 -- Return Value : Boolean
1087
1088 FUNCTION Delete_Container_Contents( x_container_id IN NUMBER)
1089 RETURN BOOLEAN IS
1090
1091 CURSOR c1( x_cid NUMBER) IS
1092 SELECT container_id FROM so_picking_line_details
1093 WHERE container_id = x_cid
1094 FOR UPDATE OF picking_line_detail_id NOWAIT;
1095 dummy_id NUMBER;
1096 record_locked EXCEPTION;
1097 PRAGMA EXCEPTION_INIT( record_locked, -54);
1098 BEGIN
1099 SAVEPOINT before_lock;
1100 OPEN c1(x_container_id);
1101 FETCH c1 INTO dummy_id;
1102 CLOSE c1;
1103
1104 UPDATE so_picking_line_details
1105 SET container_id = NULL
1106 WHERE container_id = x_container_id;
1107
1108 RETURN TRUE;
1109 EXCEPTION
1110 WHEN others THEN
1111 ROLLBACK TO before_lock;
1112 IF c1%ISOPEN THEN
1113 CLOSE c1;
1114 END IF;
1115 FND_MESSAGE.SET_NAME('OE', 'WSH_FAIL_TO_LOCK_PLD');
1116 RETURN FALSE;
1117 END Delete_Container_Contents;
1118
1119
1120
1121 END WSH_SC_DELIVERY_PVT;
[Home] [Help]