[Home] [Help]
PACKAGE BODY: APPS.WSH_PICKING_HEADER_PVT
Source
1 PACKAGE BODY WSH_PICKING_HEADER_PVT AS
2 /* $Header: WSHPKHVB.pls 115.2 99/07/16 08:19:29 porting ship $ */
3
4 PROCEDURE consolidate_pld (picking_header_id IN NUMBER,
5 ret_status OUT NUMBER,
6 msg OUT VARCHAR2)
7 IS
8 ph_id NUMBER;
9 CURSOR c1 (ph_id NUMBER ) IS
10 SELECT pl.picking_line_id
11 FROM so_picking_lines pl, so_picking_headers ph
12 WHERE pl.picking_header_id = ph.picking_header_id
13 AND ph.picking_header_id = ph_id
14 AND ph.status_code = 'PENDING';
15 pl_id NUMBER;
16 CURSOR c2 (pl_id NUMBER ) IS
17 select
18 count(picking_line_detail_id),
19 min(picking_line_detail_id),
20 sum(requested_quantity)
21 from so_picking_line_details
22 where picking_line_id = pl_id
23 and NVL(shipped_quantity, 0) = 0
24 and NVL(released_flag, 'Y') = 'Y'
25 group by
26 warehouse_id,
27 subinventory,
28 inventory_location_id,
29 revision,
30 lot_number,
31 CUSTOMER_REQUESTED_LOT_FLAG,
32 CONTEXT,
33 ATTRIBUTE1,
34 ATTRIBUTE2,
35 ATTRIBUTE3,
36 ATTRIBUTE4,
37 ATTRIBUTE5,
38 ATTRIBUTE6,
39 ATTRIBUTE7,
40 ATTRIBUTE8,
41 ATTRIBUTE9,
42 ATTRIBUTE10,
43 ATTRIBUTE11,
44 ATTRIBUTE12,
45 ATTRIBUTE13,
46 ATTRIBUTE14,
47 ATTRIBUTE15,
48 schedule_date,
49 schedule_level,
50 schedule_status_code,
51 demand_id,
52 demand_class_code,
53 AUTOSCHEDULED_FLAG,
54 delivery,
55 update_flag,
56 detail_type_code;
57 keep_pld_id NUMBER;
58 pld_count NUMBER;
59 sum_requested NUMBER;
60 dummy NUMBER;
61 sql_statement NUMBER;
62 err_msg VARCHAR2(255);
63 CURSOR c3( pl_id NUMBER ) IS
64 SELECT picking_line_detail_id
65 FROM so_picking_line_details
66 WHERE picking_line_id = pl_id
67 FOR UPDATE OF picking_line_detail_id NOWAIT;
68 BEGIN
69 SAVEPOINT before_consolidate;
70
71 ph_id := picking_header_id;
72
73 sql_statement := 0;
74 OPEN c1(ph_id);
75
76 LOOP
77
78 sql_statement := 10;
79 FETCH c1 INTO pl_id;
80
81 EXIT WHEN c1%NOTFOUND;
82
83 sql_statement := 20;
84 OPEN c3(pl_id);
85
86 sql_statement := 25;
87 FETCH c3 INTO dummy;
88
89 IF c3%NOTFOUND THEN
90 CLOSE c3;
91 ELSE
92
93 sql_statement := 30;
94 OPEN c2(pl_id);
95 LOOP
96
97 sql_statement := 40;
98 FETCH c2 INTO pld_count, keep_pld_id, sum_requested;
99
100 EXIT WHEN c2%NOTFOUND;
101
102 IF ( pld_count > 1 ) THEN
103 sql_statement := 50;
104 DELETE FROM so_picking_line_details
105 WHERE picking_line_id = pl_id
106 AND shipped_quantity = 0
107 AND NVL(released_flag, 'Y') = 'Y'
108 AND picking_line_detail_id <> keep_pld_id
109 AND (nvl(warehouse_id, -99999),
110 nvl(subinventory, -99999),
111 nvl(inventory_location_id, -99999),
112 nvl(revision, -99999),
113 nvl(lot_number, -99999),
114 nvl(CUSTOMER_REQUESTED_LOT_FLAG, -99999),
115 nvl(CONTEXT, -99999),
116 nvl(ATTRIBUTE1, -99999),
117 nvl(ATTRIBUTE2, -99999),
118 nvl(ATTRIBUTE3, -99999),
119 nvl(ATTRIBUTE4, -99999),
120 nvl(ATTRIBUTE5, -99999),
121 nvl(ATTRIBUTE6, -99999),
122 nvl(ATTRIBUTE7, -99999),
123 nvl(ATTRIBUTE8, -99999),
124 nvl(ATTRIBUTE9, -99999),
125 nvl(ATTRIBUTE10, -99999),
126 nvl(ATTRIBUTE11, -99999),
127 nvl(ATTRIBUTE12, -99999),
128 nvl(ATTRIBUTE13, -99999),
129 nvl(ATTRIBUTE14, -99999),
130 nvl(ATTRIBUTE15, -99999),
131 nvl(schedule_date, sysdate),
132 nvl(schedule_level, -99999),
133 nvl(schedule_status_code, -99999),
134 nvl(demand_id, -99999),
135 nvl(demand_class_code, -99999),
136 nvl(AUTOSCHEDULED_FLAG, -99999),
137 nvl(delivery, -99999),
138 nvl(update_flag, -99999),
139 nvl(detail_type_code, -99999)) =
140 (SELECT nvl(warehouse_id, -99999),
141 nvl(subinventory, -99999),
142 nvl(inventory_location_id, -99999),
143 nvl(revision, -99999),
144 nvl(lot_number, -99999),
145 nvl(CUSTOMER_REQUESTED_LOT_FLAG, -99999),
146 nvl(CONTEXT, -99999),
147 nvl(ATTRIBUTE1, -99999),
148 nvl(ATTRIBUTE2, -99999),
149 nvl(ATTRIBUTE3, -99999),
150 nvl(ATTRIBUTE4, -99999),
151 nvl(ATTRIBUTE5, -99999),
152 nvl(ATTRIBUTE6, -99999),
153 nvl(ATTRIBUTE7, -99999),
154 nvl(ATTRIBUTE8, -99999),
155 nvl(ATTRIBUTE9, -99999),
156 nvl(ATTRIBUTE10, -99999),
157 nvl(ATTRIBUTE11, -99999),
158 nvl(ATTRIBUTE12, -99999),
159 nvl(ATTRIBUTE13, -99999),
160 nvl(ATTRIBUTE14, -99999),
161 nvl(ATTRIBUTE15, -99999),
162 nvl(schedule_date, sysdate),
163 nvl(schedule_level, -99999),
164 nvl(schedule_status_code, -99999),
165 nvl(demand_id, -99999),
166 nvl(demand_class_code, -99999),
167 nvl(AUTOSCHEDULED_FLAG, -99999),
168 nvl(delivery, -99999),
169 nvl(update_flag, -99999),
170 nvl(detail_type_code, -99999)
171 FROM so_picking_line_details
172 WHERE picking_line_detail_id = keep_pld_id);
173
174 sql_statement := 60;
175 UPDATE so_picking_line_details
176 SET requested_quantity = sum_requested,
177 serial_number = NULL
178 WHERE picking_line_detail_id = keep_pld_id;
179 END IF;
180 END LOOP;
181 IF c2%ISOPEN THEN
182 CLOSE c2;
183 END IF;
184
185 IF c3%ISOPEN THEN
186 CLOSE c3;
187 END IF;
188
189 END IF;
190 END LOOP;
191 IF c3%ISOPEN THEN
192 CLOSE c3;
193 END IF;
194
195 IF c1%ISOPEN THEN
196 CLOSE c1;
197 END IF;
198
199 err_msg := 'Calling wsh_picking_headers.consolidate_pld Successfully.';
200 msg := err_msg;
201 ret_status := 1;
202 RETURN;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 err_msg := 'Error at Statement ' || TO_CHAR(sql_statement);
207 err_msg := err_msg || ' with ' || SUBSTR(SQLERRM,1,170);
208 ROLLBACK TO before_consolidate;
209 msg := err_msg;
210 ret_status := 0;
211 RETURN;
212 END consolidate_pld;
213
214
215 END WSH_PICKING_HEADER_PVT;