1 PACKAGE BODY WSH_SC_DEL_LINES as
2 /* $Header: WSHSCDLB.pls 115.1 99/07/16 08:20:29 porting ship $ */
3
4 Procedure update_shp_qty(del_id number, action_code varchar2) is
5 begin
6
7 --this procedure updates the shipped quantity when we try to pack
8 --depending on the action_code passed
9
10 IF action_code = 'ALL' THEN
11
12 UPDATE so_picking_line_details
13 SET shipped_quantity = requested_quantity
14 WHERE shipped_quantity IS NULL
15 AND picking_line_detail_id IN
16 ( SELECT pld.picking_line_detail_id
17 FROM so_picking_lines_all pl, so_picking_line_details pld
18 WHERE pl.picking_header_id+0 > 0
19 AND pl.picking_line_id = pld.picking_line_id
20 AND pld.delivery_id = del_id );
21
22 ELSIF action_code = 'ENTERED' THEN
23
24 UPDATE so_picking_line_details
25 SET shipped_quantity = 0
26 WHERE shipped_quantity IS NULL
27 AND picking_line_detail_id IN
28 ( SELECT pld.picking_line_detail_id
29 FROM so_picking_lines_all pl, so_picking_line_details pld
30 WHERE pl.picking_header_id+0 > 0
31 AND pl.picking_line_id = pld.picking_line_id
32 AND pld.delivery_id = del_id );
33
34 ELSIF action_code = 'BACKORDER_ALL' THEN
35
36 UPDATE so_picking_line_details
37 SET shipped_quantity = 0
38 WHERE picking_line_detail_id IN
39 ( SELECT pld.picking_line_detail_id
40 FROM so_picking_lines_all pl, so_picking_line_details pld
41 WHERE pl.picking_header_id + 0 > 0
42 AND pl.picking_line_id = pld.picking_line_id
43 AND pld.delivery_id = del_id );
44
45 END IF;
46
47 -- now go ahead and update the shipped quantity on picking lines
48 -- we need to do this here since we do not do this at close time
49 -- unless we have a case of Split picking header/line.
50
51 UPDATE so_picking_lines_all pl
52 SET pl.shipped_quantity =
53 (select sum (pld.shipped_quantity)
54 from so_picking_lines_all pl1, so_picking_line_details pld
55 where pld.delivery_id = del_id
56 and pld.picking_line_id = pl1.picking_line_id
57 and pl.picking_line_id = pl1.picking_line_id
58 and pl.picking_header_id+0 > 0)
59 WHERE pl.picking_line_id in
60 (select picking_line_id
61 from so_picking_line_details
62 where delivery_id = del_id ) ;
63
64
65 end;
66
67 PROCEDURE update_unrel_lines(del_id number) is
68 begin
69 UPDATE so_line_details
70 SET released_flag = 'N'
71 WHERE line_detail_id IN
72 ( SELECT ld.line_detail_id
73 FROM so_line_details ld, so_lines_all l
74 WHERE ld.delivery_id = del_id
75 AND l.ato_line_id is NULL
76 AND ld.configuration_item_flag is NULL
77 AND l.ato_flag = 'Y'
78 AND l.s2 = 5
79 AND ld.released_flag = 'Y'
80 AND ld.shippable_flag = 'N'
81 AND l.line_id = ld.line_id);
82
83
84 UPDATE so_line_details
85 SET delivery_id = NULL,
86 departure_id = NULL,
87 dpw_assigned_flag = 'N'
88 WHERE line_detail_id IN
89 ( SELECT ld.line_detail_id
90 FROM so_line_details ld, so_lines_all l
91 WHERE ld.delivery_id = del_id
92 AND ld.released_flag = 'N'
93 AND l.line_id = ld.line_id);
94
95 UPDATE so_picking_line_details
96 SET delivery_id = NULL,
97 departure_id = NULL,
98 dpw_assigned_flag = 'N'
99 WHERE picking_line_detail_id IN
100 ( SELECT pld.picking_line_detail_id
101 FROM so_picking_lines_all pl, so_picking_line_details pld
102 WHERE pld.delivery_id = del_id
103 AND pld.picking_line_id = pl.picking_line_id
104 AND pl.picking_header_id = 0 ) ;
105
106 end;
107
108
109 END WSH_SC_DEL_LINES;
[Home] [Help]