1 PACKAGE BODY GMI_Pick_Release_PUB AS
2 /* $Header: GMIPPKRB.pls 120.0 2005/05/25 16:21:25 appldev noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIPPKRB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains public procedures relating to GMI |
13 | Pick Release process. |
14 | |
15 | - Auto_Detail |
16 | |
17 | |
18 | HISTORY |
19 | 27-Apr-2000 odaboval Created |
20 | 09/10/01 HW BUG#:1941429 Added code to support cross_docking | |
21 +=========================================================================+
22 API Name : GMI_Pick_Release_PUB
23 Type : Global
24 -
25 Pre-reqs : N/A
26 Parameters: Per function
27
28 Current Vers : 1.0
29 */
30
31 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMI_Pick_Release_PUB';
32 -- HW BUG#:2643440 -removed G_MISS_XXX from p_grouping_rule_id and
33 -- added DEFAULT NULL
34 PROCEDURE Auto_Detail
35 (
36 p_api_version IN NUMBER
37 , p_init_msg_lst IN VARCHAR2 DEFAULT FND_API.G_FALSE
38 , p_validation_flag IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
39 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
40 , p_mo_hdr_rec IN GMI_Move_Order_Global.mo_hdr_rec
41 , p_mo_line_tbl IN GMI_Move_Order_Global.mo_line_tbl
42 , p_grouping_rule_id IN NUMBER DEFAULT NULL
43 , p_allow_delete IN VARCHAR2 DEFAULT NULL
44 , x_pick_release_status OUT NOCOPY INV_Pick_Release_PUB.INV_Release_Status_Tbl_Type
45 , x_return_status OUT NOCOPY VARCHAR2
46 , x_msg_count OUT NOCOPY NUMBER
47 , x_msg_data OUT NOCOPY VARCHAR2
48 ) IS
49
50 -- HW BUG#:1941429 cross_docking
51 CURSOR FIND_QTY (l_source_line_id NUMBER)IS
52 select sum(ABS(trans_qty)), nvl(ABS(sum(trans_qty2)),0)
53 from IC_TRAN_PND
54 where line_id = l_source_line_id
55 AND completed_ind = 0
56 AND delete_mark = 0
57 AND lot_id <> 0
58 AND staged_ind = 0;
59
60 -- HW BUG#:1941429
61 CURSOR get_trans_id (l_source_line_id NUMBER) IS
62 select trans_id
63 from ic_tran_pnd
64 where line_id = l_source_line_id
65 AND completed_ind = 0
66 AND delete_mark = 0
67 AND staged_ind = 0;
68
69
70 l_api_version NUMBER := 1.0;
71 l_api_name VARCHAR2(30) := 'Auto_Detail';
72
73 l_line_index BINARY_INTEGER;
74 l_mo_line GMI_Move_Order_Global.mo_line_rec;
75 l_organization_id NUMBER;
76 l_grouping_rule_id NUMBER;
77 l_get_header_rule NUMBER;
78 l_api_return_status VARCHAR2(10);
79 l_processed_row_count NUMBER := 0;
80 l_detail_rec_count NUMBER := 0;
81 l_print_mode VARCHAR2(1);
82
83 -- HW variables for BUG#:1941429
84 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
85 l_quantity NUMBER;
86 l_trans_id NUMBER ;
87 l_secondary_quantity NUMBER;
88 l_line_status NUMBER;
89 l_source_line_id NUMBER;
90 l_transaction_quantity NUMBER;
91 l_transaction_quantity2 NUMBER;
92 l_source_header_id NUMBER;
93 l_released_status VARCHAR2(1);
94 l_delivery_detail_id NUMBER;
95 l_p_allow_delete VARCHAR2(3);
96
97 BEGIN
98 /* =======================================================================
99 Raise a temporary error, for Dummy calls
100 ======================================================================= */
101 /* FND_MESSAGE.SET_NAME('GMI','GMI_RSV_UNAVAILABLE'); */
102 /* OE_MSG_PUB.Add; */
103 /* RAISE FND_API.G_EXC_ERROR; */
104
105 GMI_Reservation_Util.PrintLn('Entering_GMI_Pick_Release....');
106 /* =====================================================================
107 Initialization
108 ===================================================================== */
109 /* Initialize API return status to success */
110 gmi_reservation_util.println('Value of p_grouping_rule_id in Auto_Detail is '||p_grouping_rule_id);
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112 GMI_Reservation_Util.PrintLn('EXITING GMI_Pick_Release....');
113
114 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, Before Init status');
115 /* Set a SavePoint */
116 SAVEPOINT Pick_Release_PUB;
117
118 /* Standard Call to check for call compatibility */
119 IF NOT FND_API.Compatible_API_Call(l_api_version
120 , p_api_version
121 , l_api_name
122 , G_PKG_NAME) THEN
123 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, Error in Compatible_API_Call');
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 /* Initialize message list if p_init_msg_list is set to true */
128 IF FND_API.to_Boolean(p_init_msg_lst) THEN
129 FND_MSG_PUB.initialize;
130 END IF;
131 /* =====================================================================
132 Validate parameters
133 =====================================================================
134 First determine whether the table of move order lines in p_mo_line_tbl has
135 any records
136 */
137 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, Before Validation');
138
139 IF ( p_mo_line_tbl.COUNT = 0 )
140 THEN
141 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, Validation Error count=0');
142 ROLLBACK TO Pick_Release_PUB;
143 FND_MESSAGE.SET_NAME('INV','INV_NO_LINES_TO_PICK');
144 FND_MSG_PUB.Add;
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146 END IF;
147
148 /* Validate that all move order lines are from the same org, that all lines
149 have a status of pre-approved (7) or approved (3), and that all of the move
150 order lines are of type Pick Wave (3)
151 */
152 l_line_index := p_mo_line_tbl.FIRST;
153 l_mo_line := p_mo_line_tbl(l_line_index);
154 l_organization_id := l_mo_line.organization_id;
155 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, Before loop org_id='||l_organization_id);
156 LOOP
157 /* Verify that the lines are all for the same organization */
158 IF l_mo_line.organization_id <> l_organization_id THEN
159 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, In loop Error pick_different_org');
160 FND_MESSAGE.SET_NAME('GMI','INV_PICK_DIFFERENT_ORG');
161 FND_MSG_PUB.Add;
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 END IF;
164
165 /* Verify that the line status is approved or pre-approved */
166 IF (l_mo_line.line_status <> 3 AND l_mo_line.line_status <> 7) THEN
167 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, In loop Error Pick_line_Status');
168 FND_MESSAGE.SET_NAME('GMI','INV_PICK_LINE_STATUS');
169 FND_MSG_PUB.Add;
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171 END IF;
172
173 /* Verify that the move order type is Pick Wave (3) */
174 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, In loop org_id='||l_organization_id||', index='||l_line_index);
175 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
176 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
177 END LOOP;
178
179 /* Determine what printing mode to use when pick releasing lines.
180 Parameter p_org_parameters.print_pick_slip_mode
181 The Move Order Lines may need to have their grouping rule ID defaulted from
182 the header.
183 This is only necessary if the Grouping Rule ID was not passed in as
184 a parameter.
185 */
186 -- HW BUG#:2643440 Replaced comparison to G_MISS_NUM with NULL
187 IF p_grouping_rule_id IS NOT NULL THEN
188 l_grouping_rule_id := p_grouping_rule_id;
189 l_get_header_rule := 2;
190
191 ELSE
192 l_get_header_rule := 1;
193
194 END IF;
195
196 GMI_Reservation_Util.PrintLn('GMI_Pick_Release_PUB, Validation complete');
197 /* =====================================================================
198 Validation complete; begin pick release processing row-by-row
199 =====================================================================
200 */
201 l_line_index := p_mo_line_tbl.FIRST;
202 l_organization_id := l_mo_line.organization_id;
203 LOOP
204 l_mo_line := p_mo_line_tbl(l_line_index);
205 /* First retrieve the new Grouping Rule ID if necessary. */
206 IF l_get_header_rule = 1 THEN
207 /* If the header did not have a grouping rule ID, retrieve it from
208 the organization-level default.
209 */
210 IF l_mo_line.grouping_rule_id IS NULL THEN
211 /* odab l_mo_line.grouping_rule_id := p_org_parameters.pick_slip_rule_id; */
212 null;
213 END IF;
214 END IF;
215
216 -- Bug 1717145, 02-Apr-2001 odaboval, set the pickSlip printMode :
217 BEGIN
218 SELECT NVL(print_pick_slip_mode, 'E')
219 INTO l_print_mode
220 FROM WSH_SHIPPING_PARAMETERS
221 WHERE organization_id = l_organization_id;
222 EXCEPTION
223 WHEN no_data_found THEN
224 GMI_Reservation_Util.PrintLn('WARNING: print_pick_slip_mode not defined for org_id='||l_organization_id);
225 l_print_mode := 'E';
226 END;
227
228 /* Call the Pick Release Process_Line API on the current Move Order Line */
229
230 l_p_allow_delete := p_allow_delete;
231
232 GMI_Reservation_Util.PrintLn('l_p_allow_delete = ' || l_p_allow_delete) ;
233 GMI_Reservation_Util.PrintLn('calling GMI_Pick_Release_PVT.process_line. mo_line_id='||l_mo_line.line_id||', sched_ship_date='||l_mo_line.date_required||', ps_mode='||l_print_mode);
234 gmi_reservation_util.println('Value of p_grouping_rule_idp_grouping_rule_id in Auto_Detail before calling process_line is '||p_grouping_rule_id);
235 gmi_reservation_util.println('Value of l_grouping_rule_idp_grouping_rule_id in Auto_Detail before calling process_line is '||l_grouping_rule_id);
236 GMI_Pick_Release_PVT.Process_Line(
237 p_api_version => 1.0
238 ,p_validation_flag => fnd_api.g_true
239 ,p_commit => fnd_api.g_false
240 ,p_mo_hdr_rec => p_mo_hdr_rec
241 ,p_mo_line_rec => l_mo_line
242 ,p_grouping_rule_id => l_grouping_rule_id
243 ,p_print_mode => l_print_mode
244 ,p_allow_delete => l_p_allow_delete
245 ,x_detail_rec_count => l_detail_rec_count
246 ,x_return_status => l_api_return_status
247 ,x_msg_count => x_msg_count
248 ,x_msg_data => x_msg_data
249 );
250
251 GMI_Reservation_Util.PrintLn('l_return_status from process_line is ' || l_api_return_status);
252 IF l_api_return_status <> FND_API.G_RET_STS_SUCCESS THEN
253 /* If partial picking is not allowed or a Move Order Line cannot be pick
254 released, and the parameter to allow partial picking is false, then the
255 API should rollback all changes and return an error.
256 */
257 x_pick_release_status.delete;
258 ROLLBACK TO Pick_Release_PUB;
259 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_PICK_FULL');
260 FND_MSG_PUB.Add;
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END IF;
263
264 /* HW BUG#:1941429 code for cross_docking */
265
266 /* select line_status into l_line_status
267 from ic_txn_request_lines
268 where line_id = l_mo_line.line_id ;
269
270 -- Need to check lines that are approved and pre-approved (status 3 and 7)
271
272 IF ( l_mo_line.line_status <> 5 ) THEN
273 gmi_reservation_util.println('In Auto_detail GMIPPKRB move order <> 5 ');
274 -- Need to find the record in delivery_details to map it to OPM ic_tran_pnd
275 BEGIN
276 gmi_reservation_util.println('In Aut_detail Going to select line_id from shipping');
277 select source_line_id,delivery_detail_id into
278 l_source_line_id, l_delivery_detail_id
279 from wsh_delivery_details
280 where move_order_line_id = l_mo_line.line_id ;
281 EXCEPTION
282 WHEN no_data_found THEN
283 ROLLBACK TO SAVEPOINT Pick_Release_PUB;
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 FND_MSG_PUB.count_and_get
286 ( p_count => x_msg_count
287 , p_data => x_msg_data
288 );
289 END ;
290 gmi_reservation_util.println('In Auto_detail going to sum qty from ic_tran_pnd');
291 gmi_reservation_util.println('In Auto_detail value of l_delivery_detail_id '||l_delivery_detail_id);
292 gmi_reservation_util.println('In Auto_detail value of l_source_line_id '||l_source_line_id);
293 gmi_reservation_util.println('In Auto_detail value of l_quantity '||l_quantity);
294
295 BEGIN
296 select sum(requested_quantity),nvl(sum(requested_quantity2),0)
297 into l_quantity, l_secondary_quantity
298 from wsh_delivery_details
299 where source_line_id=l_source_line_id ;
300 EXCEPTION
301 WHEN no_data_found then
302 GMI_Reservation_Util.Println('No Shipping data found in Auto_detail',
303 'Inv_Pick_Release_Pub.Pick_Release');
304 ROLLBACK TO SAVEPOINT Pick_Release_PUB;
305 FND_MESSAGE.SET_NAME('INV','INV_DELIV_INFO_MISSING');
306 FND_MSG_PUB.Add;
307 RAISE fnd_api.g_exc_unexpected_error;
308 END;
309
310 gmi_reservation_util.println('Value of l_quantity from WDD '||l_quantity);
311 gmi_reservation_util.println('Value of l_secondary_quantity from '||l_secondary_quantity);
312
313 OPEN get_trans_id (l_source_line_id);
314 FETCH get_trans_id into l_trans_id;
315 IF (get_trans_id%NOTFOUND ) THEN
316 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
317 GMI_RESERVATION_UTIL.PRINTLN('Error retrieving trans_id from ic_tran_pnd');
318 RAISE NO_DATA_FOUND;
319 CLOSE get_trans_id;
320 RETURN;
321 END IF;
322
323 CLOSE get_trans_id;
324
325 OPEN FIND_QTY(l_source_line_id);
326 FETCH FIND_QTY into l_transaction_quantity, l_transaction_quantity2;
327 IF ( FIND_QTY%NOTFOUND ) THEN
328 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
329 GMI_RESERVATION_UTIL.PRINTLN('Error retrieving info from ic_tran_pnd');
330 RAISE NO_DATA_FOUND;
331 CLOSE FIND_QTY;
332 RETURN;
333 END IF;
334
335 gmi_reservation_util.println('Done getting info from ic_tran_pnd and value of l_transaction_quantity is '||l_transaction_quantity);
336 CLOSE FIND_QTY;
337
338 gmi_reservation_util.println('In Auto_detail value of l_quantity '||l_quantity);
339
340 IF ( l_transaction_quantity < l_quantity ) THEN
341 GMI_Reservation_Util.PrintLn('Pick Short -back order in GMI_PICK_RELEASE_PUB.Auto_detail');
342 BEGIN
343 select delivery_detail_id, source_header_id, source_line_id,
344 released_status
345 into l_delivery_detail_id, l_source_header_id, l_source_line_id,
346 l_released_status
347 from wsh_delivery_details
348 where source_line_id = l_source_line_id
349 and move_order_line_id is NULL
350 and released_status = 'R';
351 EXCEPTION
352 WHEN no_data_found then
353 GMI_Reservation_Util.Println('No Shipping data found in Auto_detail',
354 'Inv_Pick_Release_Pub.Pick_Release');
355 ROLLBACK TO SAVEPOINT Pick_Release_PUB;
356 FND_MESSAGE.SET_NAME('INV','INV_DELIV_INFO_MISSING');
357 FND_MSG_PUB.Add;
358 RAISE fnd_api.g_exc_unexpected_error;
359 END;
360 gmi_reservation_util.println('In Auto_detail going to call update_shipping');
361 --Call Update_Shipping_Attributes to backorder detail line
362 l_shipping_attr(1).source_header_id := l_source_header_id;
363 l_shipping_attr(1).trans_id := l_trans_id;
364 l_shipping_attr(1).source_line_id := l_source_line_id;
365 l_shipping_attr(1).ship_from_org_id := l_mo_line.organization_id;
366 l_shipping_attr(1).released_status := l_released_status;
367 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
368 l_shipping_attr(1).action_flag := 'B';
369 l_shipping_attr(1).cycle_count_quantity := l_quantity - l_transaction_quantity ;
370 l_shipping_attr(1).cycle_count_quantity2 := nvl(l_secondary_quantity - l_transaction_quantity2,0);
371 l_shipping_attr(1).subinventory := l_mo_line.from_subinventory_code;
372 l_shipping_attr(1).locator_id := l_mo_line.from_locator_id;
373
374 gmi_reservation_util.println('value of l_shipping_attr(1).cycle_count_quantity1 before calling update shipping att is '||l_shipping_attr(1).cycle_count_quantity);
375 gmi_reservation_util.println('value of l_shipping_attr(1).cycle_count_quantity2 before calling update shipping att is '||l_shipping_attr(1).cycle_count_quantity2);
376
377 WSH_INTERFACE.Update_Shipping_Attributes
378 (p_source_code => 'INV',
379 p_changed_attributes => l_shipping_attr,
380 x_return_status => l_api_return_status
381 );
382 IF( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
383 GMI_Reservation_Util.Println('Retrun error from update shipping attributes',
384 'GMI_Pick_Release_Pub.Auto_Detail');
385 raise FND_API.G_EXC_ERROR;
386 END IF;
387
388 UPDATE IC_TXN_REQUEST_LINES
389 SET quantity = l_transaction_quantity,
390 secondary_quantity = l_transaction_quantity2
391 where line_id = l_mo_line.line_id;
392 END IF; -- of (l_transaction_quantity < l_quantity)
393 END IF ; -- of ( line_status <> 5 )
394 -- HW end of changes for cross docking BUG#:1941429*/
395
396 /* Populate return status structure with the processing status of this row. */
397 l_processed_row_count := l_processed_row_count + 1;
398 GMI_Reservation_Util.PrintLn('In Loop, Set the return pick_release_status rec_type row='||l_processed_row_count||', detail_rec_count='||l_detail_rec_count);
399 x_pick_release_status(l_processed_row_count).mo_line_id := l_mo_line.line_id;
400 x_pick_release_status(l_processed_row_count).return_status := l_api_return_status;
401 x_pick_release_status(l_processed_row_count).detail_rec_count := l_detail_rec_count;
402
403 /* l_detail_rec_count := 0; */
404 /* Update the Pick Release API's return status to an error if the line could
405 not be processed. Note that processing of other lines will continue.
406 */
407 IF l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
408 l_api_return_status = FND_API.G_RET_STS_ERROR THEN
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 END IF;
411
412 GMI_Reservation_Util.PrintLn('Before Next loop No Error');
413
414 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
415 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
416 END LOOP;
417
418 x_return_status := l_api_return_status;
419
420
421 EXCEPTION
422 WHEN FND_API.G_EXC_ERROR THEN
423 GMI_Reservation_Util.PrintLn('End of Pick_ReleasePUB Error (Rollback to Savepoint)');
424 ROLLBACK TO SAVEPOINT Pick_Release_PUB;
425
426 x_return_status := FND_API.G_RET_STS_ERROR;
427
428 /* Get message count and data */
429 FND_MSG_PUB.count_and_get
430 ( p_count => x_msg_count
431 , p_data => x_msg_data
432 );
433
434 WHEN OTHERS THEN
435 GMI_Reservation_Util.PrintLn('End of Pick_ReleasePUB ErrorOther (Rollback to Savepoint)');
436 ROLLBACK TO SAVEPOINT Pick_Release_PUB;
437 x_return_status := FND_API.G_RET_STS_ERROR;
438
439 /* Get message count and data */
440 FND_MSG_PUB.count_and_get
441 ( p_count => x_msg_count
442 , p_data => x_msg_data
443 );
444
445 END Auto_Detail;
446
447
448 END GMI_Pick_Release_PUB;
[Home] [Help]