1 PACKAGE BODY wms_engine_pvt AS
2 /* $Header: WMSVPPEB.pls 120.11.12010000.4 2008/12/08 09:53:46 abasheer ship $ */
3 --
4 -- File : WMSVPPEB.pls
5 -- Content : WMS_Engine_PVT package body
6 -- Description : wms rules engine private API's
7 -- Notes :
8 -- Modified : 30/10/98 ckuenzel created orginal file in inventory
9 -- 02/08/99 mzeckzer changed
10 -- 07/31/99 bitang created in wms
11 -- 05/12/05 grao added logic for rule search - 'K'
12 --
13 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_Engine_PVT';
14 g_debug NUMBER;
15 g_use_rule VARCHAR2(1) := 'Y' ;
16 -- [g_use_rule VARCHAR2(1) := 'Y'; used to track if stg/rule
17 -- search API is required to be called or not ]
18
19 --
20 SUBTYPE g_wms_txn_temp_rec_type IS wms_transactions_temp%ROWTYPE;
21
22 TYPE g_wms_txn_temp_tbl_type IS TABLE OF g_wms_txn_temp_rec_type
23 INDEX BY BINARY_INTEGER;
24
25 -- a record type used in the combine_transfer procedure
26 TYPE g_combine_rec_type IS RECORD(
27 revision wms_transactions_temp.revision%TYPE
28 , from_subinventory_code wms_transactions_temp.from_subinventory_code%TYPE
29 , from_locator_id wms_transactions_temp.from_locator_id%TYPE
30 , from_cost_group_id wms_transactions_temp.from_cost_group_id%TYPE
31 , to_subinventory_code wms_transactions_temp.to_subinventory_code%TYPE
32 , to_locator_id wms_transactions_temp.to_locator_id%TYPE
33 , to_cost_group_id wms_transactions_temp.to_cost_group_id%TYPE
34 , lot_number wms_transactions_temp.lot_number%TYPE
35 , lot_expiration_date wms_transactions_temp.lot_expiration_date%TYPE
36 , serial_number wms_transactions_temp.serial_number%TYPE
37 , transaction_quantity wms_transactions_temp.transaction_quantity%TYPE
38 , primary_quantity wms_transactions_temp.primary_quantity%TYPE
39 , secondary_quantity wms_transactions_temp.secondary_quantity%TYPE
40 , grade_code wms_transactions_temp.grade_code%TYPE
41 , rule_id wms_transactions_temp.rule_id%TYPE
42 , reservation_id wms_transactions_temp.reservation_id%TYPE
43 , lpn_id wms_transactions_temp.lpn_id%TYPE);
44
45 TYPE g_combine_tbl_type IS TABLE OF g_combine_rec_type
46 INDEX BY BINARY_INTEGER;
47
48 --
49 --Procedures for logging messages
50 PROCEDURE log_event(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
51 l_module VARCHAR2(255);
52
53 BEGIN
54 --l_progress := l_progress + 10;
55 l_module:= 'wms.plsql.'||g_pkg_name || '.' || p_api_name || '.' || p_label;
56 inv_log_util.trace(p_message, l_module, 9);
57 /*
58 fnd_log.STRING(log_level => fnd_log.level_event,
59 module => l_module, message => p_message);
60 gmi_reservation_util.println(p_message); */
61 END log_event;
62
63 PROCEDURE log_error(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
64 l_module VARCHAR2(255);
65 BEGIN
66 l_module:= 'wms.plsql.'||g_pkg_name || '.' || p_api_name || '.' || p_label;
67 inv_log_util.trace(p_message, l_module, 9);
68
69 /*fnd_log.STRING(log_level => fnd_log.level_error,
70 module => l_module, message => p_message);
71 gmi_reservation_util.println(p_message);*/
72 END log_error;
73
74 PROCEDURE log_error_msg(p_api_name VARCHAR2, p_label VARCHAR2) IS
75 l_module VARCHAR2(255);
76 BEGIN
77 l_module:= 'wms.plsql.'|| g_pkg_name ||'.' || p_api_name || '.' || p_label;
78 inv_log_util.trace(p_label, l_module, 9);
79 /*
80 fnd_log.message(log_level => fnd_log.level_error,
81 module => l_module, pop_message => FALSE);
82 inv_log_util.trace(p_label, l_module, 9);
83 gmi_reservation_util.println(p_label); */
84 END log_error_msg;
85
86 PROCEDURE log_procedure(p_api_name VARCHAR2
87 , p_label VARCHAR2
88 , p_message VARCHAR2) IS
89 l_module VARCHAR2(255);
90 BEGIN
91
92 l_module:= 'wms.plsql.'||g_pkg_name || '.' || p_api_name || '.' || p_label;
93 inv_log_util.trace(p_message, l_module, 9);
94 /*
95 fnd_log.STRING(log_level => fnd_log.level_procedure,
96 module => l_module, message => p_message);
97 inv_log_util.trace(p_message, l_module, 9);
98 gmi_reservation_util.println(p_message);*/
99 END log_procedure;
100
101 PROCEDURE log_statement(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
102 l_module VARCHAR2(255);
103 BEGIN
104 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
105 inv_log_util.trace(p_message, l_module, 9);
106 /*
107 fnd_log.STRING(log_level => fnd_log.level_statement, module => l_module, message => p_message);
108 IF inv_pp_debug.is_debug_mode THEN
109 inv_pp_debug.send_message_to_pipe(p_message);
110 END IF;
111 inv_log_util.trace(p_message, l_module, 9);
112 gmi_reservation_util.println(p_message); */
113 END log_statement;
114
115
116 -- Description
117 -- Insert all records in p_wms_txn_temp_tbl into wms_transactions_temp.
118 -- Value for column pp_transaction_temp_id will be derived in the
119 -- procedure
120 --
121 PROCEDURE insert_detail_temp_records
122 ( x_return_status OUT NOCOPY VARCHAR2
123 , p_wms_txn_temp_tbl IN g_wms_txn_temp_tbl_type
124 ) IS
125 l_api_name CONSTANT VARCHAR2(30) := 'insert_detail_temp_records';
126 l_debug NUMBER;
127
128 --
129 /* Bug 5265024
130 CURSOR l_wms_txn_temp_id_csr IS
131 SELECT wms_transactions_temp_s.NEXTVAL
132 FROM DUAL;
133 */
134
135 l_wms_txn_temp_tbl_size NUMBER;
136 l_temp_id_tbl g_number_tbl_type;
137 BEGIN
138 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
139 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
140 END IF;
141 l_debug := g_debug;
142 IF l_debug = 1 THEN
143 log_procedure(l_api_name, 'start', 'Start Insert_Detail_temp_records');
144 END IF;
145
146 -- Initialize API return status to access
147 x_return_status := fnd_api.g_ret_sts_success;
148 --
149 l_wms_txn_temp_tbl_size := p_wms_txn_temp_tbl.COUNT;
150
151 -- return if nothing to insert
152 IF l_wms_txn_temp_tbl_size IS NULL
153 OR l_wms_txn_temp_tbl_size < 1 THEN
154 RETURN;
155 END IF;
156
157 -- get pp_transaction_temp_id for all records
158 /* Bug 5265024
159 FOR l_counter IN 1 .. l_wms_txn_temp_tbl_size LOOP
160 OPEN l_wms_txn_temp_id_csr;
161 FETCH l_wms_txn_temp_id_csr INTO l_temp_id_tbl(l_counter);
162
163 IF l_wms_txn_temp_id_csr%NOTFOUND THEN
164 CLOSE l_wms_txn_temp_id_csr;
165 RAISE NO_DATA_FOUND;
166 END IF;
167
168 CLOSE l_wms_txn_temp_id_csr;
169 END LOOP;
170 */
171
172 -- insert to the table
173 FOR l_counter IN 1 .. l_wms_txn_temp_tbl_size LOOP
174 INSERT INTO wms_transactions_temp
175 (
176 pp_transaction_temp_id
177 , transaction_temp_id
178 , type_code
179 , line_type_code
180 , transaction_quantity
181 , primary_quantity
182 , secondary_quantity
183 , grade_code
184 , revision
185 , lot_number
186 , lot_expiration_date
187 , serial_number
188 , from_subinventory_code
189 , from_locator_id
190 , rule_id
191 , reservation_id
192 , to_subinventory_code
193 , to_locator_id
194 , from_organization_id
195 , to_organization_id
196 , from_cost_group_id
197 , to_cost_group_id
198 , lpn_id
199 )
200 VALUES (
201 wms_transactions_temp_s.NEXTVAL
202 -- l_temp_id_tbl(l_counter)
203 , p_wms_txn_temp_tbl(l_counter).transaction_temp_id
204 , p_wms_txn_temp_tbl(l_counter).type_code
205 , p_wms_txn_temp_tbl(l_counter).line_type_code
206 , p_wms_txn_temp_tbl(l_counter).transaction_quantity
207 , p_wms_txn_temp_tbl(l_counter).primary_quantity
208 , p_wms_txn_temp_tbl(l_counter).secondary_quantity
209 , p_wms_txn_temp_tbl(l_counter).grade_code
210 , p_wms_txn_temp_tbl(l_counter).revision
211 , p_wms_txn_temp_tbl(l_counter).lot_number
212 , p_wms_txn_temp_tbl(l_counter).lot_expiration_date
213 , p_wms_txn_temp_tbl(l_counter).serial_number
214 , p_wms_txn_temp_tbl(l_counter).from_subinventory_code
215 , p_wms_txn_temp_tbl(l_counter).from_locator_id
216 , p_wms_txn_temp_tbl(l_counter).rule_id
217 , p_wms_txn_temp_tbl(l_counter).reservation_id
218 , p_wms_txn_temp_tbl(l_counter).to_subinventory_code
219 , p_wms_txn_temp_tbl(l_counter).to_locator_id
220 , p_wms_txn_temp_tbl(l_counter).from_organization_id
221 , p_wms_txn_temp_tbl(l_counter).to_organization_id
222 , p_wms_txn_temp_tbl(l_counter).from_cost_group_id
223 , p_wms_txn_temp_tbl(l_counter).to_cost_group_id
224 , p_wms_txn_temp_tbl(l_counter).lpn_id
225 );
226 IF l_debug = 1 THEN
227 log_event(l_api_name, 'detail temp insert ', 'detail temp insert '
228 || p_wms_txn_temp_tbl(l_counter).secondary_quantity);
229 END IF;
230 END LOOP;
231
232 IF l_debug = 1 THEN
233 log_procedure(l_api_name, 'end', 'End Insert_Detail_temp_records');
234 END IF;
235 --
236 EXCEPTION
237 WHEN fnd_api.g_exc_error THEN
238 /* bug 5265024
239 IF l_wms_txn_temp_id_csr%ISOPEN THEN
240 CLOSE l_wms_txn_temp_id_csr;
241 END IF;
242 * /
243 x_return_status := fnd_api.g_ret_sts_error;
244 IF l_debug = 1 THEN
245 log_error(l_api_name, 'error', 'Error in Insert_Detail_temp_records');
246 END IF;
247 --
248 WHEN fnd_api.g_exc_unexpected_error THEN
249 /* bug 5265024
250 IF l_wms_txn_temp_id_csr%ISOPEN THEN
251 CLOSE l_wms_txn_temp_id_csr;
252 END IF;
253 */
254 x_return_status := fnd_api.g_ret_sts_unexp_error;
255 IF l_debug = 1 THEN
256 log_error(l_api_name, 'unexp_error',
257 'Unexpected error in Insert_Detail_temp_records');
258 END IF;
259 --
260 WHEN OTHERS THEN
261 /*bug 5265024
262 IF l_wms_txn_temp_id_csr%ISOPEN THEN
263 CLOSE l_wms_txn_temp_id_csr;
264 END IF;
265 */
266
267 x_return_status := fnd_api.g_ret_sts_unexp_error;
268
269 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
270 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
271 END IF;
272
273 IF l_debug = 1 THEN
274 log_error(l_api_name, 'other_error',
275 'Other error in Insert_Detail_temp_records');
276 END IF;
277 END insert_detail_temp_records;
278
279 --
280 -- Description
281 -- Purges all records from WMS_TRANSACTIONS_TEMP for the move
282 -- order line
283 PROCEDURE purge_detail_temp_records
284 ( x_return_status OUT NOCOPY VARCHAR2
285 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
286 ) IS
287 l_api_name CONSTANT VARCHAR2(30) := 'purge_detail_temp_records';
288 l_debug NUMBER;
289 BEGIN
290 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
291 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
292 END IF;
293 l_debug := g_debug;
294 IF l_debug = 1 THEN
295 log_procedure(l_api_name, 'start', 'Start purge_detail_temp_records');
296 END IF;
297
298 -- Initialisize API return status to success
299 x_return_status := fnd_api.g_ret_sts_success;
300
301 --
302 DELETE FROM wms_transactions_temp
303 WHERE transaction_temp_id = p_request_line_rec.line_id;
304
305 --
306 DELETE FROM wms_txn_context_temp
307 WHERE line_id = p_request_line_rec.line_id;
308 IF l_debug = 1 THEN
309 log_procedure(l_api_name, 'end', 'End purge_detail_temp_records');
310 END IF;
311 --
312 EXCEPTION
313 WHEN fnd_api.g_exc_error THEN
314 x_return_status := fnd_api.g_ret_sts_error;
315 IF l_debug = 1 THEN
316 log_error(l_api_name, 'error', 'Error in purge_detail_temp_records');
317 END IF;
318 --
319 WHEN fnd_api.g_exc_unexpected_error THEN
320 x_return_status := fnd_api.g_ret_sts_unexp_error;
321 IF l_debug = 1 THEN
322 log_error(l_api_name, 'unexp_error', 'Unexpected error in purge_detail_temp_records');
323 END IF;
324 --
325 WHEN OTHERS THEN
326 x_return_status := fnd_api.g_ret_sts_unexp_error;
327
328 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
329 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
330 END IF;
331 IF l_debug = 1 THEN
332 log_error(l_api_name, 'other_error', 'Other error in purge_detail_temp_records');
333 END IF;
334 --
335 END purge_detail_temp_records;
336
337 --
338 -- Description
339 -- For future serial number support
340 -- Resolves serial number ranges and insert records into the temporary table
341 -- for detailing
342 PROCEDURE resolve_serials(
343 x_return_status OUT NOCOPY VARCHAR2
344 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
345 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
346 ) IS
347 l_api_name CONSTANT VARCHAR2(30) := 'resolve_serials';
348 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
349 --
350 l_counter INTEGER;
351 l_prefix VARCHAR2(30);
352 l_fm_num VARCHAR2(30);
353 l_to_num VARCHAR2(30);
354 l_length_num INTEGER;
355 l_counter INTEGER;
356 l_wms_txn_temp_tbl g_wms_txn_temp_tbl_type;
357 l_wms_txn_temp_tbl_size NUMBER;
358 l_debug NUMBER;
359 BEGIN
360 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
361 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
362 END IF;
363 l_debug := g_debug;
364 IF l_debug = 1 THEN
365 log_procedure(l_api_name, 'start', 'Start resolve_serials');
366 END IF;
367 --
368 -- Initialisize API return status to access
369 x_return_status := fnd_api.g_ret_sts_success;
370
371 --
372 -- get the actual serial number based on range
373 IF p_request_line_rec.serial_number_end IS NOT NULL
374 AND p_request_line_rec.serial_number_end <> p_request_line_rec.serial_number_start THEN
375 inv_detail_util_pvt.split_prefix_num(p_request_line_rec.serial_number_start, l_prefix, l_fm_num);
376 inv_detail_util_pvt.split_prefix_num(p_request_line_rec.serial_number_end, l_prefix, l_to_num);
377 l_length_num := LENGTH(l_fm_num);
378
379 --
380 FOR l_counter IN 1 .. l_to_num - l_fm_num + 1 LOOP
381 --need to find out how to deal with this
382 --for multi-language purpose
383 l_wms_txn_temp_tbl(l_counter).serial_number := l_prefix || LPAD(TO_CHAR(l_fm_num + l_counter), l_length_num, '0');
384
385 -- if l_debug = 1 then
389
386 -- log_statement(l_api_name, 'resolve_serials', l_wms_txn_temp_tbl(l_counter).serial_number );
387 -- end if;
388 END LOOP;
390 l_wms_txn_temp_tbl_size := l_to_num - l_fm_num + 1;
391 ELSE
392 -- Insert record for single serial number into wms_transactions_temp
393 l_wms_txn_temp_tbl(1).serial_number := p_request_line_rec.serial_number_start;
394 l_wms_txn_temp_tbl_size := 1;
395 END IF;
396
397 -- taking care of other fields
398 FOR l_counter IN 1 .. l_wms_txn_temp_tbl_size LOOP
399 l_wms_txn_temp_tbl(l_counter).transaction_temp_id := p_request_line_rec.line_id;
400 l_wms_txn_temp_tbl(l_counter).type_code := p_request_context.type_code;
401 l_wms_txn_temp_tbl(l_counter).line_type_code := 1;
402 l_wms_txn_temp_tbl(l_counter).transaction_quantity := 1;
403 l_wms_txn_temp_tbl(l_counter).primary_quantity := 1;
404 l_wms_txn_temp_tbl(l_counter).revision := p_request_line_rec.revision;
405 l_wms_txn_temp_tbl(l_counter).lot_number := p_request_line_rec.lot_number;
406 l_wms_txn_temp_tbl(l_counter).lot_expiration_date := p_request_context.lot_expiration_date;
407 l_wms_txn_temp_tbl(l_counter).from_subinventory_code := p_request_line_rec.from_subinventory_code;
408 l_wms_txn_temp_tbl(l_counter).from_locator_id := p_request_line_rec.from_locator_id;
409 l_wms_txn_temp_tbl(l_counter).to_subinventory_code := p_request_line_rec.to_subinventory_code;
410 l_wms_txn_temp_tbl(l_counter).to_locator_id := p_request_line_rec.to_locator_id;
411 l_wms_txn_temp_tbl(l_counter).from_organization_id := p_request_line_rec.organization_id;
412 l_wms_txn_temp_tbl(l_counter).to_organization_id := p_request_line_rec.to_organization_id;
413 l_wms_txn_temp_tbl(l_counter).from_cost_group_id := p_request_line_rec.from_cost_group_id;
414 l_wms_txn_temp_tbl(l_counter).to_cost_group_id := p_request_line_rec.to_cost_group_id;
415 END LOOP;
416
417 --
418 x_return_status := l_return_status;
419
420 IF l_debug = 1 THEN
421 log_procedure(l_api_name, 'end', 'End resolve_serials');
422 END IF;
423 --
424 EXCEPTION
425 WHEN fnd_api.g_exc_error THEN
426 x_return_status := fnd_api.g_ret_sts_error;
427 IF l_debug = 1 THEN
428 log_error(l_api_name, 'error', 'Error in resolve_serials');
429 END IF;
430 --
431 WHEN fnd_api.g_exc_unexpected_error THEN
432 x_return_status := fnd_api.g_ret_sts_unexp_error;
433 If l_debug = 1 THEN
434 log_error(l_api_name, 'unexp_error', 'Unexpected error in resolve_serials');
435 END IF;
436 --
437 WHEN OTHERS THEN
438 x_return_status := fnd_api.g_ret_sts_unexp_error;
439
440 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
441 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
442 END IF;
443 IF l_debug = 1 THEN
444 log_error(l_api_name, 'other_error', 'Other error in resolve_serials');
445 END IF;
446 --
447 END resolve_serials;
448
449 --
450 -- Procedure : Prepare
451 -- FUNCTION : Creates records in WMS_TRANSACTIONS_TEMP for
452 -- each single lot and/or serial number and splits
453 -- transfer transactions into issue and receipt
454 -- transaction
455 PROCEDURE prepare(
456 x_return_status OUT NOCOPY VARCHAR2
457 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
458 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
459 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
460 , x_allow_non_partial_rules OUT NOCOPY BOOLEAN
461 ) IS
462 l_api_name CONSTANT VARCHAR2(30) := 'Prepare';
463 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
464 l_sum_qty NUMBER := 0;
465 l_trx_qty NUMBER;
466 -- For serial number support in the future
467 l_serial_support VARCHAR2(1) := 'N';
468 l_length_num NUMBER;
469 l_fm_num NUMBER;
470 l_to_num NUMBER;
471 l_counter NUMBER;
472 l_subinventory_code VARCHAR2(10);
473 l_locator_id NUMBER;
474 l_remain_pri_qty NUMBER;
475 l_pp_temp_qty NUMBER;
476 l_reserved_qty NUMBER;
477 l_index NUMBER;
478 --
479 l_detail_level_tbl inv_detail_util_pvt.g_detail_level_tbl_type;
480 l_detail_level_tbl_size NUMBER;
481 --
482 l_wms_txn_temp_tbl g_wms_txn_temp_tbl_type;
483 l_remaining_quantity NUMBER;
484 l_allow_non_partial_rules BOOLEAN;
485 l_debug NUMBER;
486 l_rsv_ctr NUMBER; -- [ Added to track number of detailed serial numbers ]
487 --
488 BEGIN
489 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
490 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
491 END IF;
492 l_debug := g_debug;
493 IF l_debug = 1 THEN
494 log_procedure(l_api_name, 'start', 'Start Prepare');
495 END IF;
496
500 --
497 -- Initialisize API return status to access
498 x_return_status := fnd_api.g_ret_sts_success;
499
501 -- check serial number control and support
502 IF l_debug =1 then
503 log_statement(l_api_name,'p_request_context.item_serial_control_code ', p_request_context.item_serial_control_code);
504 log_statement(l_api_name,'l_serial_support ', l_serial_support);
505 log_event(l_api_name, 'prepare', 'prepare' );
506 END IF;
507
508 IF p_request_context.item_serial_control_code IN (2, 5, 6)
509 AND l_serial_support = 'Y' THEN
510 --
511 -- Resolve FM_SERIAL_number and TO_SERIAL_number
512 -- and insert one record for
513 -- each serial number into WMS_TRANSACTION_TEMP
514 --
515 -- Here we assume that the number of serial numbers in the
516 -- range and the transaction_quantity are the same
517 --
518 -- Important!!!!!!
519 -- Currently this program does not handle the case that
520 -- requires both serial number support and detail based on
521 -- reservations. So you can not just change the value of
522 -- l_serial_support to Y and expect the code will work correctly!
523 --
524 -- The fuctionality in inv_detail_util_pvt for detailing serial
525 -- numbers is different from what I mean here. Over there
526 -- we do not use any pick and put away rules, just check
527 -- which serial number is free and take it. Here when we
528 -- say detailing serial numbers, we mean we will check the
529 -- rules defined by users and rules can specify constraints
530 -- or sort preferences using serial number attributes
531 --
532 -- Bin Tang 10/20/1999
533 --
534
535 IF l_debug = 1 THEN
536 log_statement(l_api_name , 'Calling resolve_serials() ', '');
537 END IF;
538 resolve_serials(l_return_status, p_request_line_rec, p_request_context);
539 IF l_return_status = fnd_api.g_ret_sts_error THEN
540 RAISE fnd_api.g_exc_error;
541 END IF;
542 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
543 RAISE fnd_api.g_exc_unexpected_error;
544 END IF;
545 ELSE
546 IF p_request_context.transfer_flag
547 OR p_request_context.type_code = 2 THEN -- pick only or transfer
548 inv_detail_util_pvt.compute_pick_detail_level(
549 l_return_status
550 , p_request_line_rec
551 , p_request_context
552 , p_reservations
553 , l_detail_level_tbl
554 , l_detail_level_tbl_size
555 , l_remaining_quantity
556 );
557
558 -- Bug # 2286454 ----
559 --
560 IF (l_remaining_quantity > 0) THEN
561 l_allow_non_partial_rules := FALSE;
562 ELSE
563 l_allow_non_partial_rules := TRUE;
564 END IF;
565
566 x_allow_non_partial_rules := l_allow_non_partial_rules;
567
568 --
569 IF l_debug = 1 THEN
570 log_event(l_api_name, 'prepare ', 'prepare detail_level_tbl_size '||l_detail_level_tbl_size);
571 END IF;
572
573 l_rsv_ctr := 0 ; -- [ reset the rsv_counter ]
574
575 FOR l_index IN 1 .. l_detail_level_tbl_size LOOP
576 l_wms_txn_temp_tbl(l_index).transaction_temp_id := p_request_line_rec.line_id;
577 l_wms_txn_temp_tbl(l_index).type_code := p_request_context.type_code;
578 l_wms_txn_temp_tbl(l_index).line_type_code := 1; -- input
579 l_wms_txn_temp_tbl(l_index).transaction_quantity := l_detail_level_tbl(l_index).transaction_quantity;
580 l_wms_txn_temp_tbl(l_index).primary_quantity := l_detail_level_tbl(l_index).primary_quantity;
581 l_wms_txn_temp_tbl(l_index).secondary_quantity := l_detail_level_tbl(l_index).secondary_quantity;
582 l_wms_txn_temp_tbl(l_index).grade_code := l_detail_level_tbl(l_index).grade_code;
583 l_wms_txn_temp_tbl(l_index).revision := l_detail_level_tbl(l_index).revision;
584 l_wms_txn_temp_tbl(l_index).lot_number := l_detail_level_tbl(l_index).lot_number;
585 --log_event(l_api_name, 'prepare ', 'in prepare sec qty '||l_detail_level_tbl(l_index).secondary_quantity);
586
587 IF l_wms_txn_temp_tbl(l_index).lot_number IS NOT NULL THEN
588 l_wms_txn_temp_tbl(l_index).lot_expiration_date :=
589 inv_detail_util_pvt.get_lot_expiration_date(
590 p_request_line_rec.organization_id
591 , p_request_line_rec.inventory_item_id
592 , l_wms_txn_temp_tbl(l_index).lot_number
593 );
594 END IF;
595
596 l_wms_txn_temp_tbl(l_index).from_organization_id := p_request_line_rec.organization_id;
597 l_wms_txn_temp_tbl(l_index).to_organization_id := p_request_line_rec.to_organization_id;
598 l_wms_txn_temp_tbl(l_index).from_cost_group_id := p_request_line_rec.from_cost_group_id;
599 l_wms_txn_temp_tbl(l_index).to_cost_group_id := p_request_line_rec.to_cost_group_id;
600 l_wms_txn_temp_tbl(l_index).from_subinventory_code := l_detail_level_tbl(l_index).subinventory_code;
601 l_wms_txn_temp_tbl(l_index).from_locator_id := l_detail_level_tbl(l_index).locator_id;
605 l_wms_txn_temp_tbl(l_index).serial_number := l_detail_level_tbl(l_index).serial_number; --- [ new code ]
602 l_wms_txn_temp_tbl(l_index).to_subinventory_code := p_request_line_rec.to_subinventory_code;
603 l_wms_txn_temp_tbl(l_index).to_locator_id := p_request_line_rec.to_locator_id;
604 l_wms_txn_temp_tbl(l_index).reservation_id := l_detail_level_tbl(l_index).reservation_id;
606 l_wms_txn_temp_tbl(l_index).lpn_id := l_detail_level_tbl(l_index).lpn_id;
607
608 --[ Seting the flag to determine , if strategy/rule search API will be called or not ]
609 IF l_detail_level_tbl(l_index).serial_resv_flag = 'Y' THEN
610 l_rsv_ctr := l_rsv_ctr + 1;
611 END IF;
612
613 IF l_debug = 1 THEN
614 log_event(l_api_name, 'prepare ', 'serial_number '||l_detail_level_tbl(l_index).serial_number);
615 log_event(l_api_name, 'prepare ', 'serial_resv_flag '||l_detail_level_tbl(l_index).serial_resv_flag);
616 log_event(l_api_name, 'prepare ', 'p_request_line_rec.line_id '||p_request_line_rec.line_id);
617 log_event(l_api_name, 'prepare ', 'p_request_context.type_code '||p_request_context.type_code);
618 log_event(l_api_name, 'prepare ', 'transaction_quantity '||l_detail_level_tbl(l_index).transaction_quantity);
619 log_event(l_api_name, 'prepare ', 'locator_id '||l_detail_level_tbl(l_index).locator_id);
620 log_event(l_api_name, 'prepare ', 'reservation_id '||l_detail_level_tbl(l_index).reservation_id);
621 END IF;
622
623 END LOOP;
624 -- [ setting the rule_use flag
625 IF l_detail_level_tbl_size = l_rsv_ctr THEN
626 g_use_rule := 'N' ;
627 ELSE
628 g_use_rule := 'Y' ;
629 END IF;
630 IF l_debug = 1 THEN
631 log_event(l_api_name, 'prepare ', 'l_rsv_ctr '|| l_rsv_ctr);
632 log_event(l_api_name, 'prepare ', 'g_use_rule '|| g_use_rule);
633 END IF;
634 -- ]
635
636 ELSE -- the request is for put away only
637 l_wms_txn_temp_tbl(1).from_organization_id := p_request_line_rec.organization_id;
638 l_wms_txn_temp_tbl(1).to_organization_id := p_request_line_rec.to_organization_id;
639 l_wms_txn_temp_tbl(1).from_subinventory_code := p_request_line_rec.from_subinventory_code;
640 l_wms_txn_temp_tbl(1).from_locator_id := p_request_line_rec.from_locator_id;
641 l_wms_txn_temp_tbl(1).to_subinventory_code := p_request_line_rec.to_subinventory_code;
642 l_wms_txn_temp_tbl(1).to_locator_id := p_request_line_rec.to_locator_id;
643 l_wms_txn_temp_tbl(1).from_cost_group_id := p_request_line_rec.from_cost_group_id;
644 l_wms_txn_temp_tbl(1).to_cost_group_id := p_request_line_rec.to_cost_group_id;
645 l_wms_txn_temp_tbl(1).transaction_temp_id := p_request_line_rec.line_id;
646 l_wms_txn_temp_tbl(1).type_code := p_request_context.type_code;
647 l_wms_txn_temp_tbl(1).line_type_code := 1; -- input
648 l_wms_txn_temp_tbl(1).primary_quantity := p_request_line_rec.primary_quantity;
649 l_wms_txn_temp_tbl(1).secondary_quantity := p_request_line_rec.secondary_quantity;
650 l_wms_txn_temp_tbl(1).grade_code := p_request_line_rec.grade_code;
651 l_wms_txn_temp_tbl(1).transaction_quantity := p_request_line_rec.quantity - NVL(p_request_line_rec.quantity_detailed, 0);
652 l_wms_txn_temp_tbl(1).revision := p_request_line_rec.revision;
653 l_wms_txn_temp_tbl(1).lot_number := p_request_line_rec.lot_number;
654 l_wms_txn_temp_tbl(1).lot_expiration_date := p_request_context.lot_expiration_date;
655 l_wms_txn_temp_tbl(1).lpn_id := p_request_line_rec.lpn_id;
656 -- Bug #2286454
657 l_allow_non_partial_rules := TRUE;
658 END IF;
659 --
660 -- now we can insert these temporary records derived from
661 -- above
662 insert_detail_temp_records(l_return_status, l_wms_txn_temp_tbl);
663 IF l_return_status = fnd_api.g_ret_sts_error THEN
664 RAISE fnd_api.g_exc_error;
665 END IF;
666 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
667 RAISE fnd_api.g_exc_unexpected_error;
668 END IF;
669 END IF;
670
671 IF l_debug = 1 THEN
672 log_procedure(l_api_name, 'end', 'End Prepare');
673 END IF;
674 --
675 EXCEPTION
676 WHEN fnd_api.g_exc_error THEN
677 /* --
678 -- debugging section
679 -- can be commented out for final code
680 IF inv_pp_debug.is_debug_mode THEN
681 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
682 -- the message retrieved here since it is no longer on the stack
683 inv_pp_debug.set_last_error_message(SQLERRM);
684 inv_pp_debug.send_message_to_pipe('exception in '|| l_api_name);
685 inv_pp_debug.send_last_error_message;
686 END IF;
687
688 -- end of debugging section
689 -- */
690 x_return_status := fnd_api.g_ret_sts_error;
691 IF l_debug = 1 THEN
692 log_error(l_api_name, 'error', 'Error in Prepare');
693 END IF;
694 --
695 WHEN fnd_api.g_exc_unexpected_error THEN
696 /* --
697 -- debugging section
698 -- can be commented out for final code
699 IF inv_pp_debug.is_debug_mode THEN
703 inv_pp_debug.send_message_to_pipe('exception in '|| l_api_name);
700 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
701 -- the message retrieved here since it is no longer on the stack
702 inv_pp_debug.set_last_error_message(SQLERRM);
704 inv_pp_debug.send_last_error_message;
705 END IF;
706
707 -- end of debugging section
708 -- */
709 x_return_status := fnd_api.g_ret_sts_unexp_error;
710 IF l_debug = 1 THEN
711 log_error(l_api_name, 'unexp_error', 'Unexpected error in Prepare');
712 END IF;
713 --
714 WHEN OTHERS THEN
715
716 x_return_status := fnd_api.g_ret_sts_unexp_error;
717
718 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
719 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
720 END IF;
721 IF l_debug = 1 THEN
722 log_error(l_api_name, 'other_error', 'Other error in Prepare');
723 END IF;
724 --
725 END prepare;
726
727 --
728 --
729 -- Procedure : Prepare_Transfer_Receipt
730 -- Pre-reqs : Record(s) exist(s) in WMS_TRANSACTIONS_TEMP with
731 -- p_transaction_temp_id = the move order line id
732 -- and type_code = 2 (pick) and
733 -- line_type_code = 2 (output line)
734 -- Function : Copies issue output record(s) into
735 -- WMS_TRANSACTIONS_TEMP as input records
736 -- for receipt portion within transfer transactions
737 --
738 PROCEDURE prepare_transfer_receipt
739 (
740 x_return_status IN OUT NOCOPY VARCHAR2
741 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
742 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
743 ) IS
744 l_api_name CONSTANT VARCHAR2(30) := 'prepare_transfer_receipt';
745 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
746
747 --
748 --changed by jcearley on 12/8/99 to attempt to order transfers
749 -- in order of pick suggestions
750
751 CURSOR l_put_input_csr IS
752 SELECT SUM(wtt.transaction_quantity) transaction_quantity
753 , SUM(wtt.primary_quantity) primary_quantity
754 , SUM(wtt.secondary_quantity) secondary_quantity
755 , wtt.grade_code grade_code
756 , wtt.revision revision
757 , wtt.lot_number lot_number
758 , wtt.from_subinventory_code from_subinventory_code
759 , wtt.from_locator_id from_locator_id
760 , wtt.from_cost_group_id from_cost_group_id
761 , wtt.lpn_id lpn_id
762 FROM wms_transactions_temp wtt
763 WHERE wtt.transaction_temp_id = p_request_line_rec.line_id
764 AND wtt.line_type_code = 2 -- output line
765 AND wtt.type_code = 2 -- pick
766 GROUP BY wtt.lot_number
767 , wtt.revision
768 , wtt.from_subinventory_code
769 , wtt.from_locator_id
770 , wtt.from_cost_group_id
771 , wtt.lpn_id
772 , wtt.grade_code
773 ORDER BY MIN(wtt.pp_transaction_temp_id);
774
775 --
776 l_put_input_rec l_put_input_csr%ROWTYPE;
777 --
778 l_txn_qty NUMBER;
779 l_wms_txn_temp_tbl g_wms_txn_temp_tbl_type;
780 l_wms_txn_temp_tbl_size NUMBER;
781 l_debug NUMBER;
782
783 -- Added for Bug 6063903
784 l_wms_installed BOOLEAN := TRUE;
785 x_api_return_status VARCHAR2(2);
786 x_msg_count NUMBER;
787 x_msg_data VARCHAR2(2000);
788 -- Added for Bug 6063903
789
790 BEGIN
791 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
792 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
793 END IF;
794 l_debug := g_debug;
795 IF l_debug = 1 THEN
796 log_procedure(l_api_name, 'start', 'Start prepare_transfer_receipt');
797 END IF;
798
799 --
800 -- Initialisize API return status to access
801 x_return_status := fnd_api.g_ret_sts_success;
802 --
803 l_wms_txn_temp_tbl_size := 0;
804 -- the fetching might be changed to use bulk
805 -- fetching if it is too slow
806 -- however, a disadvantage is that bulk fetching can
807 -- not use table of record. that will make the code
808 -- looks ugly
809 OPEN l_put_input_csr;
810
811 LOOP
812 FETCH l_put_input_csr INTO l_put_input_rec;
813 EXIT WHEN l_put_input_csr%NOTFOUND;
814 -- Note: serial number here is ignored here. The assumption
815 -- is that the put away side will not care about the serial number.
816 -- The purpose is to reduce the number of records as input in
817 -- wms_transactions_temp for put away since the more records
818 -- it is, the more runs the engine has to run, and thus the slower.
819 -- The assumption stated might not be valid for some situations.
820 -- So this might need to be enhanced later.
821 l_wms_txn_temp_tbl_size := l_wms_txn_temp_tbl_size + 1;
822 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).transaction_temp_id := p_request_line_rec.line_id;
823 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).type_code := 1;
827 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).secondary_quantity := l_put_input_rec.secondary_quantity;
824 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).line_type_code := 1;
825 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).transaction_quantity := l_put_input_rec.transaction_quantity;
826 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).primary_quantity := l_put_input_rec.primary_quantity;
828 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).grade_code := l_put_input_rec.grade_code;
829 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).revision := l_put_input_rec.revision;
830 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).lot_number := l_put_input_rec.lot_number;
831 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).lot_expiration_date := p_request_context.lot_expiration_date;
832 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).serial_number := NULL;
833 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_subinventory_code := l_put_input_rec.from_subinventory_code;
834 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_locator_id := l_put_input_rec.from_locator_id;
835 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_cost_group_id := l_put_input_rec.from_cost_group_id;
836 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_subinventory_code := p_request_line_rec.to_subinventory_code;
837 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_locator_id := p_request_line_rec.to_locator_id;
838 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_organization_id := p_request_line_rec.organization_id;
839 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_organization_id := p_request_line_rec.to_organization_id;
840 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).lpn_id := l_put_input_rec.lpn_id;
841
842 IF l_debug = 1 THEN
843 log_event(l_api_name, 'transfer and receipt input', 'transfer and receipt input');
844 log_event(l_api_name, 'transfer and receipt input', 'input qty1 '||
845 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).primary_quantity );
846 log_event(l_api_name, 'transfer and receipt input', 'input qty2 '||
847 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).secondary_quantity );
848 log_event(l_api_name, 'transfer and receipt input', 'input grade_code '||
849 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).secondary_quantity );
850 END IF;
851
852 -- Added for Bug 6063903
853 /*
854 we are setting the l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id as Null
855 as in inventory organization, cost group of Subinventory can be different from cost
856 group of desitnation subivnentory. Correct CG will get populated at a later stage.
857 */
858 l_wms_installed := WMS_INSTALL.check_install(
859 x_return_status => x_api_return_status,
860 x_msg_count => x_msg_count,
861 x_msg_data => x_msg_data,
862 p_organization_id => p_request_line_rec.organization_id);
863
864 --get to_cost_group id
865 -- if cost group on the move order line is not null, use it
866 -- if it is null, use the from_cost_group
867 IF (p_request_line_rec.to_cost_group_id IS NULL) THEN
868 IF not (l_wms_installed) THEN -- Added for Bug 6063903
869 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id := null; -- Added for Bug 6063903
870 ELSIF (l_wms_installed) THEN -- Added for Bug 6063903
871 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id := l_put_input_rec.from_cost_group_id;
872 END IF;
873 ELSE
874 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id := p_request_line_rec.to_cost_group_id;
875 END IF;
876 END LOOP;
877
878 CLOSE l_put_input_csr;
879 -- insert the records into the temporary table
880 -- as input to put away detailing
881 insert_detail_temp_records(l_return_status, l_wms_txn_temp_tbl);
882
883 IF l_return_status = fnd_api.g_ret_sts_error THEN
884 RAISE fnd_api.g_exc_error;
885 END IF;
886
887 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
888 RAISE fnd_api.g_exc_unexpected_error;
889 END IF;
890
891 x_return_status := l_return_status;
892
893 IF l_debug = 1 THEN
894 log_procedure(l_api_name, 'end', 'End prepare_transfer_receipt');
895 END IF;
896
897 EXCEPTION
898 WHEN fnd_api.g_exc_error THEN
899 IF l_put_input_csr%ISOPEN THEN
900 CLOSE l_put_input_csr;
901 END IF;
902
903 x_return_status := fnd_api.g_ret_sts_error;
904 IF l_debug = 1 THEN
905 log_error(l_api_name, 'error', 'Error in prepare_transfer_receipt');
906 END IF;
907 --
908 WHEN fnd_api.g_exc_unexpected_error THEN
909 IF l_put_input_csr%ISOPEN THEN
910 CLOSE l_put_input_csr;
911 END IF;
912
913 x_return_status := fnd_api.g_ret_sts_unexp_error;
914 IF l_debug = 1 THEN
915 log_error(l_api_name, 'unexp_error', 'Unexpected error in prepare_transfer_receipt');
916 END IF;
917 --
918 WHEN OTHERS THEN
919 IF l_put_input_csr%ISOPEN THEN
920 CLOSE l_put_input_csr;
921 END IF;
922
923 x_return_status := fnd_api.g_ret_sts_unexp_error;
924
928 IF l_debug = 1 THEN
925 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
926 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
927 END IF;
929 log_error(l_api_name, 'other_error', 'Other error in prepare_transfer_receipt');
930 END IF;
931 --
932 END prepare_transfer_receipt;
933
934 --
935 -- debugging routine
936 -- display the output records in wms_transactions_temp
937 -- when called
938 PROCEDURE display_temp_records IS
939 CURSOR l_cur IS
940 SELECT transaction_temp_id
941 , line_type_code
942 , type_code
943 , revision
944 , lot_number
945 , lot_expiration_date
946 , from_subinventory_code
947 , from_locator_id
948 , primary_quantity
949 , transaction_quantity
950 , secondary_quantity
951 , grade_code
952 , reservation_id
953 , to_subinventory_code
954 , to_locator_id
955 , lpn_id
956 FROM wms_transactions_temp
957 ORDER BY transaction_temp_id
958 , line_type_code
959 , type_code
960 , revision
961 , lot_number
962 , lot_expiration_date
963 , from_subinventory_code
964 , from_locator_id;
965
966 l_rec l_cur%ROWTYPE;
967 l_type VARCHAR2(20);
968 l_line_type VARCHAR2(20);
969 l_api_name VARCHAR2(30);
970 l_debug NUMBER;
971 BEGIN
972 /*IF inv_pp_debug.is_debug_mode = FALSE THEN
973 RETURN;
974 END IF; */
975
976 IF g_debug IS NULL THEN
977 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
978 END IF;
979 l_debug := g_debug;
980
981 IF l_debug = 1 THEN
982 log_error(l_api_name, 'display_temp_records', 'display_temp_records');
983 ELSE
984 RETURN;
985 END IF;
986
987
988 OPEN l_cur;
989 FETCH l_cur INTO l_rec;
990
991 WHILE l_cur%FOUND LOOP
992 --inv_pp_debug.send_message_to_pipe('move order line '|| l_rec.transaction_temp_id);
993
994 IF l_rec.line_type_code = 1 THEN
995 l_line_type := 'input line ';
996 ELSE
997 l_line_type := 'output line ';
998 END IF;
999
1000 IF l_rec.type_code = 1 THEN
1001 l_type := 'put away';
1002 ELSE
1003 l_type := 'pick';
1004 END IF;
1005
1006 /* inv_pp_debug.send_message_to_pipe('line_type_code '|| l_line_type);
1007 inv_pp_debug.send_message_to_pipe('type_code '|| l_type);
1008 inv_pp_debug.send_message_to_pipe('revision '|| l_rec.revision);
1009 inv_pp_debug.send_message_to_pipe('lot_number '|| l_rec.lot_number);
1010 inv_pp_debug.send_message_to_pipe('expiration date '|| l_rec.lot_expiration_date);
1011 inv_pp_debug.send_message_to_pipe('from subinventory'|| l_rec.from_subinventory_code);
1012 inv_pp_debug.send_message_to_pipe('from locator id '|| l_rec.from_locator_id);
1013 inv_pp_debug.send_message_to_pipe('to subinventory '|| l_rec.to_subinventory_code);
1014 inv_pp_debug.send_message_to_pipe('to locator id '|| l_rec.to_locator_id);
1015 inv_pp_debug.send_message_to_pipe('primary quantity '|| l_rec.primary_quantity);
1016 inv_pp_debug.send_message_to_pipe('transaction qty '|| l_rec.transaction_quantity);
1017 inv_pp_debug.send_message_to_pipe('reservation_id '|| l_rec.reservation_id);
1018 inv_pp_debug.send_message_to_pipe('lpn_id '|| l_rec.lpn_id);
1019
1020
1021 gmi_reservation_util.println('type_code '|| l_type);
1022 gmi_reservation_util.println('revision '|| l_rec.revision);
1023 gmi_reservation_util.println('lot_number '|| l_rec.lot_number);
1024 gmi_reservation_util.println('expiration date '|| l_rec.lot_expiration_date);
1025 gmi_reservation_util.println('from subinventory'|| l_rec.from_subinventory_code);
1026 gmi_reservation_util.println('from locator id '|| l_rec.from_locator_id);
1027 gmi_reservation_util.println('to subinventory '|| l_rec.to_subinventory_code);
1028 gmi_reservation_util.println('to locator id '|| l_rec.to_locator_id);
1029 gmi_reservation_util.println('primary quantity '|| l_rec.primary_quantity);
1030 gmi_reservation_util.println('secondary quantity '|| l_rec.secondary_quantity);
1031 gmi_reservation_util.println('transaction qty '|| l_rec.transaction_quantity);
1032 gmi_reservation_util.println('grade code '|| l_rec.grade_code);
1033 gmi_reservation_util.println('reservation_id '|| l_rec.reservation_id);
1034 gmi_reservation_util.println('lpn_id '|| l_rec.lpn_id); */
1035
1036
1037 log_statement(l_api_name,'type_code ', l_type);
1038 log_statement(l_api_name,'revision ', l_rec.revision);
1039 log_statement(l_api_name,'lot_number ', l_rec.lot_number);
1040 log_statement(l_api_name,'expiration date ', l_rec.lot_expiration_date );
1041 log_statement(l_api_name,'from subinventory ', l_rec.from_subinventory_code);
1042 log_statement(l_api_name,'from locator id ', l_rec.from_locator_id);
1043 log_statement(l_api_name,'To subinventory ', l_rec.to_subinventory_code);
1044 log_statement(l_api_name,'To locator id ', l_rec.to_locator_id);
1045 log_statement(l_api_name,'primary quantity ', l_rec.primary_quantity);
1049 log_statement(l_api_name,'reservation_id ', l_rec.reservation_id);
1046 log_statement(l_api_name,'secondary quantity ', l_rec.secondary_quantity);
1047 log_statement(l_api_name,'transaction qty ', l_rec.transaction_quantity);
1048 log_statement(l_api_name,'grade code ', l_rec.grade_code);
1050 log_statement(l_api_name,'lpn_id ', l_rec.lpn_id);
1051
1052 FETCH l_cur INTO l_rec;
1053 END LOOP;
1054
1055 CLOSE l_cur;
1056
1057 END display_temp_records;
1058
1059 --
1060 -- create output suggestion records for issue or receipt but not transfer
1061 -- read from the table wms_transactions_temp by the order of
1062 -- revision, from_sub, to_sub, from_loc, to_loc, lot_number, serial_number
1063 PROCEDURE output_issue_or_receipt(
1064 x_return_status OUT NOCOPY VARCHAR2
1065 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
1066 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
1067 , p_plan_tasks IN BOOLEAN ---DEFAULT FALSE
1068 ) IS
1069 l_transaction_temp_id NUMBER;
1070
1071 -- Cursor for receipts or issues
1072 --changed by jcearley on 12/8/99
1073 --added order by clause so suggestions are entered into
1074 --mmtt in order in which the engine found them
1075 CURSOR l_pp_temp_csr IS
1076 SELECT x.revision
1077 , x.from_subinventory_code
1078 , x.from_locator_id
1079 , x.to_subinventory_code
1080 , x.to_locator_id
1081 , x.lot_number
1082 , MAX(x.lot_expiration_date) lot_expiration_date
1083 , x.serial_number serial_number_start
1084 , x.serial_number serial_number_end
1085 , SUM(x.transaction_quantity) transaction_quantity
1086 , SUM(x.primary_quantity) primary_quantity
1087 , SUM(x.secondary_quantity) secondary_quantity
1088 , grade_code
1089 , MIN(x.pick_rule_id) pick_rule_id
1090 , MIN(x.put_away_rule_id) put_away_rule_id
1091 , x.reservation_id reservation_id
1092 , x.from_cost_group_id
1093 , x.to_cost_group_id
1094 , x.lpn_id
1095 FROM (SELECT wtt.revision
1096 , wtt.from_subinventory_code
1097 , wtt.from_locator_id
1098 , wtt.to_subinventory_code
1099 , wtt.to_locator_id
1100 , wtt.lot_number
1101 , wtt.lot_expiration_date
1102 , wtt.serial_number
1103 , wtt.transaction_quantity
1104 , wtt.primary_quantity
1105 , wtt.secondary_quantity
1106 , wtt.grade_code
1107 , DECODE(wtt.type_code, 2, wtt.rule_id, NULL) pick_rule_id
1108 , DECODE(wtt.type_code, 1, wtt.rule_id, NULL) put_away_rule_id
1109 , DECODE(wtt.type_code, 2, wtt.reservation_id, NULL) reservation_id
1110 , wtt.pp_transaction_temp_id
1111 , wtt.from_cost_group_id
1112 , wtt.to_cost_group_id
1113 , wtt.lpn_id
1114 FROM wms_transactions_temp wtt
1115 WHERE wtt.transaction_temp_id = l_transaction_temp_id
1116 AND wtt.line_type_code = 2 -- output line
1117 ) x
1118 GROUP BY x.revision
1119 , x.from_subinventory_code
1120 , x.to_subinventory_code
1121 , x.from_locator_id
1122 , x.to_locator_id
1123 , x.from_cost_group_id
1124 , x.to_cost_group_id
1125 , x.lot_number
1126 , x.serial_number
1127 , x.reservation_id
1128 , x.lpn_id
1129 , x.grade_code
1130 ORDER BY MIN(x.pp_transaction_temp_id);
1131
1132 --
1133 l_api_name CONSTANT VARCHAR2(30) := 'output_issue_or_receipt';
1134 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1135 l_curr_rec inv_detail_util_pvt.g_output_process_rec_type;
1136 l_debug NUMBER;
1137 --
1138 BEGIN
1139 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
1140 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1141 END IF;
1142 l_debug := g_debug;
1143 IF l_debug = 1 THEN
1144 log_procedure(l_api_name, 'start', 'Start output_issue_or_receipt');
1145 END IF;
1146
1147 --
1148 -- Initialisize API return status to access
1149 x_return_status := fnd_api.g_ret_sts_success;
1150 l_transaction_temp_id := p_request_line_rec.line_id;
1151 -- fetch the input request line into a record
1152 OPEN l_pp_temp_csr;
1153
1154 LOOP
1155 FETCH l_pp_temp_csr INTO l_curr_rec;
1156
1157 IF l_pp_temp_csr%NOTFOUND THEN
1158 EXIT;
1159 END IF;
1160 If l_debug = 1 THEN
1161 log_event(l_api_name, 'add output', 'add output');
1162 END IF;
1163 inv_detail_util_pvt.add_output(l_curr_rec);
1164 END LOOP;
1165
1166 CLOSE l_pp_temp_csr;
1167 IF l_debug = 1 THEN
1168 log_event(l_api_name, 'process output', 'process output');
1169 END IF;
1173 , p_plan_tasks);
1170 inv_detail_util_pvt.process_output(l_return_status
1171 , p_request_line_rec
1172 , p_request_context
1174
1175 IF l_return_status = fnd_api.g_ret_sts_error THEN
1176 RAISE fnd_api.g_exc_error;
1177 END IF;
1178
1179 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1180 RAISE fnd_api.g_exc_unexpected_error;
1181 END IF;
1182
1183 x_return_status := l_return_status;
1184
1185 IF l_debug = 1 THEN
1186 log_procedure(l_api_name, 'end', 'End output_issue_or_receipt');
1187 END IF;
1188
1189 EXCEPTION
1190 WHEN fnd_api.g_exc_error THEN
1191 IF l_pp_temp_csr%ISOPEN THEN
1192 CLOSE l_pp_temp_csr;
1193 END IF;
1194
1195 x_return_status := fnd_api.g_ret_sts_error;
1196 IF l_debug = 1 THEN
1197 log_error(l_api_name, 'error', 'Error in output_issue_or_receipt');
1198 END IF;
1199
1200 --
1201 WHEN fnd_api.g_exc_unexpected_error THEN
1202 IF l_pp_temp_csr%ISOPEN THEN
1203 CLOSE l_pp_temp_csr;
1204 END IF;
1205
1206 x_return_status := fnd_api.g_ret_sts_unexp_error;
1207 IF l_debug = 1 THEN
1208 log_error(l_api_name, 'unexp_error', 'Unexpected error in output_issue_or_receipt');
1209 END IF;
1210 --
1211 WHEN OTHERS THEN
1212 IF l_pp_temp_csr%ISOPEN THEN
1213 CLOSE l_pp_temp_csr;
1214 END IF;
1215
1216 x_return_status := fnd_api.g_ret_sts_unexp_error;
1217
1218 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1219 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1220 END IF;
1221 IF l_debug = 1 THEN
1222 log_error(l_api_name, 'other_error', 'Other error in output_issue_or_receipt');
1223 END IF;
1224 --
1225 END output_issue_or_receipt;
1226
1227 --
1228 -- create output suggestion records for transfer
1229 PROCEDURE combine_transfer(
1230 x_return_status OUT NOCOPY VARCHAR2
1231 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
1232 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
1233 , p_plan_tasks IN BOOLEAN ----DEFAULT FALSE
1234 ) IS
1235 l_api_name VARCHAR2(30) := 'combine_transfer';
1236 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1237 l_transaction_temp_id NUMBER;
1238
1239 --changed by jcearley on 12/8/99 to order transfer order in mmtt
1240 -- in the order in which suggestions were created
1241 CURSOR l_issue_csr IS
1242 SELECT revision
1243 , from_subinventory_code
1244 , from_locator_id
1245 , from_cost_group_id
1246 , to_subinventory_code
1247 , to_locator_id
1248 , to_cost_group_id
1249 , lot_number
1250 , MAX(lot_expiration_date) lot_expiration_date
1251 , serial_number
1252 , SUM(transaction_quantity) transaction_quantity
1253 , SUM(primary_quantity) primary_quantity
1254 , SUM(secondary_quantity) secondary_quantity
1255 , grade_code
1256 , MIN(rule_id) pick_rule_id
1257 , reservation_id
1258 , lpn_id
1259 FROM wms_transactions_temp
1260 WHERE transaction_temp_id = l_transaction_temp_id
1261 AND line_type_code = 2 -- output line
1262 AND type_code = 2 -- pick
1263 GROUP BY serial_number
1264 , lot_number
1265 , revision
1266 , from_subinventory_code
1267 , from_locator_id
1268 , from_cost_group_id
1269 , reservation_id
1270 , to_subinventory_code
1271 , to_locator_id
1272 , to_cost_group_id
1273 , lpn_id
1274 , grade_code
1275 ORDER BY revision
1276 , from_subinventory_code
1277 , from_locator_id
1278 , from_cost_group_id
1279 , lpn_id
1280 , lot_number
1281 , serial_number
1282 , reservation_id
1283 ;
1284 --bug 2828119 - order by sub and locator to prevent multiple picking
1285 --picking tasks from the same locator
1286 --ordER BY MIN(pp_transaction_temp_id);
1287
1288 --
1289 CURSOR l_receipt_csr IS
1290 SELECT revision
1291 , from_subinventory_code
1292 , from_locator_id
1293 , from_cost_group_id
1294 , to_subinventory_code
1295 , to_locator_id
1296 , to_cost_group_id
1297 , lot_number
1298 , MAX(lot_expiration_date) lot_expiration_date
1299 , serial_number
1300 , SUM(transaction_quantity) transaction_quantity
1301 , SUM(primary_quantity) primary_quantity
1302 , SUM(secondary_quantity) secondary_quantity
1303 , grade_code
1304 , MIN(rule_id) put_away_rule_id
1305 , NULL reservation_id
1306 , lpn_id lpn_id
1307 FROM wms_transactions_temp
1308 WHERE transaction_temp_id = l_transaction_temp_id
1309 AND line_type_code = 2 -- output line
1313 , revision
1310 AND type_code = 1 -- put away
1311 GROUP BY serial_number
1312 , lot_number
1314 , from_subinventory_code
1315 , from_locator_id
1316 , from_cost_group_id
1317 , to_subinventory_code
1318 , to_locator_id
1319 , to_cost_group_id
1320 , lpn_id
1321 , grade_code
1322 ORDER BY revision
1323 , from_subinventory_code
1324 , from_locator_id
1325 , from_cost_group_id
1326 , lpn_id
1327 , lot_number
1328 , serial_number
1329 , reservation_id
1330 ;
1331 --bug 2828119 - order by sub and locator to prevent multiple picking
1332 --picking tasks from the same locator
1333 --ordER BY MIN(pp_transaction_temp_id);
1334 --
1335 l_curr_issue_rec g_combine_rec_type;
1336 l_curr_receipt_rec g_combine_rec_type;
1337 l_issue_tbl g_combine_tbl_type;
1338 l_receipt_tbl g_combine_tbl_type;
1339 l_issue_tbl_size INTEGER;
1340 l_receipt_tbl_size INTEGER;
1341 l_curr_issue_idx INTEGER;
1342 l_curr_receipt_idx INTEGER;
1343 l_output_process_rec inv_detail_util_pvt.g_output_process_rec_type;
1344 l_xfer_qty NUMBER;
1345 l_txn_xfer_qty NUMBER;
1346 l_sec_xfer_qty NUMBER;
1347 l_grade_code VARCHAR2(150);
1348 l_debug NUMBER;
1349 BEGIN
1350 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
1351 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1352 END IF;
1353 l_debug := g_debug;
1354 IF l_debug = 1 THEN
1355 log_procedure(l_api_name, 'start', 'Start combine_transfer');
1356 END IF;
1357
1358 x_return_status := l_return_status;
1359 -- fetch all issue and receipt records into memory
1360 l_transaction_temp_id := p_request_line_rec.line_id;
1361 OPEN l_issue_csr;
1362 l_issue_tbl_size := 0;
1363
1364 LOOP
1365 FETCH l_issue_csr INTO l_curr_issue_rec;
1366 EXIT WHEN l_issue_csr%NOTFOUND;
1367 l_issue_tbl_size := l_issue_tbl_size + 1;
1368 l_issue_tbl(l_issue_tbl_size) := l_curr_issue_rec;
1369 --inv_pp_debug.send_message_to_pipe('issue sub '|| l_curr_issue_rec.from_subinventory_code);
1370 END LOOP;
1371
1372 CLOSE l_issue_csr;
1373 l_receipt_tbl_size := 0;
1374 OPEN l_receipt_csr;
1375
1376 LOOP
1377 FETCH l_receipt_csr INTO l_curr_receipt_rec;
1378 EXIT WHEN l_receipt_csr%NOTFOUND;
1379 l_receipt_tbl_size := l_receipt_tbl_size + 1;
1380 l_receipt_tbl(l_receipt_tbl_size) := l_curr_receipt_rec;
1381 --inv_pp_debug.send_message_to_pipe('receipt sub '|| l_curr_receipt_rec.to_subinventory_code);
1382 END LOOP;
1383
1384 CLOSE l_receipt_csr;
1385 --inv_pp_debug.send_message_to_pipe('receipt table size '|| l_receipt_tbl_size);
1386 --inv_pp_debug.send_message_to_pipe('issue table size '|| l_issue_tbl_size);
1387 --
1388 -- combine the issue and receipt records into a transfer record
1389 -- initialize the variables
1390 l_xfer_qty := 0;
1391 l_txn_xfer_qty := 0;
1392
1393 -- get the first issue suggestion
1394 IF l_issue_tbl_size < 1 THEN
1395 -- no issue could be recommended by the system -> exit
1396 RETURN;
1397 END IF;
1398
1399 l_curr_issue_idx := 1;
1400
1401 --
1402 -- get the first receipt suggestion
1403 IF l_receipt_tbl_size < 1 THEN
1404 -- no receipt could be recommended by the system -> exit
1405 RETURN;
1406 END IF;
1407
1408 l_curr_receipt_idx := 1;
1409
1410 --
1411 WHILE l_curr_issue_idx <= l_issue_tbl_size
1412 AND l_curr_receipt_idx <= l_receipt_tbl_size LOOP
1413 -- If the current issue record and receipt record
1414 -- do not have the same revision, and lot number,
1415 -- the engine has not found a put away suggestion for the given
1416 -- issue suggestion.
1417 -- Then we will try the next issue record
1418 IF NOT (l_issue_tbl(l_curr_issue_idx).revision = l_receipt_tbl(l_curr_receipt_idx).revision
1419 OR l_issue_tbl(l_curr_issue_idx).revision IS NULL
1420 AND l_receipt_tbl(l_curr_receipt_idx).revision IS NULL
1421 )
1422 OR NOT (l_issue_tbl(l_curr_issue_idx).lot_number = l_receipt_tbl(l_curr_receipt_idx).lot_number
1423 OR l_issue_tbl(l_curr_issue_idx).lot_number IS NULL
1424 AND l_receipt_tbl(l_curr_receipt_idx).lot_number IS NULL
1425 )
1426 OR NOT (l_issue_tbl(l_curr_issue_idx).from_subinventory_code = l_receipt_tbl(l_curr_receipt_idx).from_subinventory_code)
1427 OR NOT (l_issue_tbl(l_curr_issue_idx).from_locator_id = l_receipt_tbl(l_curr_receipt_idx).from_locator_id)
1428 OR NOT (l_issue_tbl(l_curr_issue_idx).from_cost_group_id = l_receipt_tbl(l_curr_receipt_idx).from_cost_group_id)
1429 OR NOT (l_issue_tbl(l_curr_issue_idx).lpn_id = l_receipt_tbl(l_curr_receipt_idx).lpn_id)
1430 THEN -- the follwing is commented out because
1431 -- we do not copy the serial number from the picking output
1435 -- it does.
1432 -- to put away input in prepare_transfer_receipt procedure
1433 -- for efficiency reason. so the output from put away
1434 -- suggestion does not have serial number at all. orignially
1436 --
1437 -- OR NOT (l_issue_tbl(l_curr_issue_idx).serial_number
1438 -- = l_receipt_tbl(l_curr_receipt_idx).serial_number
1439 -- OR l_issue_tbl(l_curr_issue_idx).serial_number IS NULL
1440 -- AND l_receipt_tbl(l_curr_receipt_idx).serial_number IS NULL
1441 -- )
1442
1443 IF l_debug = 1 THEN
1444 log_event(l_api_name, 'combine_failed', 'Unable to match ' || 'issue record with receipt record. Trying next ' || 'issue record');
1445 END IF;
1446 l_curr_issue_idx := l_curr_issue_idx + 1;
1447 -- pardon for the 'goto' but we haven't been able to solve it
1448 -- another way
1449 GOTO CONTINUE;
1450 END IF;
1451
1452 --
1453 -- compute the actual transfer qty ( minimum of issue and receipt )
1454 IF l_issue_tbl(l_curr_issue_idx).primary_quantity > l_receipt_tbl(l_curr_receipt_idx).primary_quantity THEN
1455 l_xfer_qty := l_receipt_tbl(l_curr_receipt_idx).primary_quantity;
1456 l_sec_xfer_qty := l_receipt_tbl(l_curr_receipt_idx).secondary_quantity;
1457 l_issue_tbl(l_curr_issue_idx).primary_quantity := l_issue_tbl(l_curr_issue_idx).primary_quantity - l_xfer_qty;
1458 l_issue_tbl(l_curr_issue_idx).secondary_quantity := l_issue_tbl(l_curr_issue_idx).secondary_quantity - l_sec_xfer_qty;
1459 l_receipt_tbl(l_curr_receipt_idx).primary_quantity := 0;
1460 l_receipt_tbl(l_curr_receipt_idx).secondary_quantity := 0;
1461 ELSE
1462 l_xfer_qty := l_issue_tbl(l_curr_issue_idx).primary_quantity;
1463 l_sec_xfer_qty := l_issue_tbl(l_curr_issue_idx).secondary_quantity;
1464 l_receipt_tbl(l_curr_receipt_idx).primary_quantity := l_receipt_tbl(l_curr_receipt_idx).primary_quantity - l_xfer_qty;
1465 l_receipt_tbl(l_curr_receipt_idx).secondary_quantity := l_receipt_tbl(l_curr_receipt_idx).secondary_quantity - l_sec_xfer_qty;
1466 l_issue_tbl(l_curr_issue_idx).primary_quantity := 0;
1467 l_issue_tbl(l_curr_issue_idx).secondary_quantity := 0;
1468 END IF;
1469
1470 -- Added the following code to remove the dependencies between WMSVPPEB.pls and INVVDEUB.pls for 1159
1471
1472 IF l_issue_tbl(l_curr_issue_idx).transaction_quantity > l_receipt_tbl(l_curr_receipt_idx).transaction_quantity THEN
1473 l_txn_xfer_qty := l_receipt_tbl(l_curr_receipt_idx).transaction_quantity;
1474 l_issue_tbl(l_curr_issue_idx).transaction_quantity := l_issue_tbl(l_curr_issue_idx).transaction_quantity - l_txn_xfer_qty;
1475 l_receipt_tbl(l_curr_receipt_idx).transaction_quantity := 0;
1476 ELSE
1477 l_txn_xfer_qty := l_issue_tbl(l_curr_issue_idx).transaction_quantity;
1478 l_receipt_tbl(l_curr_receipt_idx).transaction_quantity := l_receipt_tbl(l_curr_receipt_idx).transaction_quantity - l_txn_xfer_qty;
1479 l_issue_tbl(l_curr_issue_idx).transaction_quantity := 0;
1480 END IF;
1481
1482 l_output_process_rec.revision := l_issue_tbl(l_curr_issue_idx).revision;
1483 l_output_process_rec.from_subinventory_code := l_issue_tbl(l_curr_issue_idx).from_subinventory_code;
1484 l_output_process_rec.from_locator_id := l_issue_tbl(l_curr_issue_idx).from_locator_id;
1485 l_output_process_rec.from_cost_group_id := l_issue_tbl(l_curr_issue_idx).from_cost_group_id;
1486 l_output_process_rec.to_subinventory_code := l_receipt_tbl(l_curr_receipt_idx).to_subinventory_code;
1487 l_output_process_rec.to_locator_id := l_receipt_tbl(l_curr_receipt_idx).to_locator_id;
1488 l_output_process_rec.to_cost_group_id := l_receipt_tbl(l_curr_receipt_idx).to_cost_group_id;
1489 l_output_process_rec.lot_number := l_issue_tbl(l_curr_issue_idx).lot_number;
1490 l_output_process_rec.lot_expiration_date := l_issue_tbl(l_curr_issue_idx).lot_expiration_date;
1491 l_output_process_rec.serial_number_start := l_issue_tbl(l_curr_issue_idx).serial_number;
1492 l_output_process_rec.serial_number_end := l_issue_tbl(l_curr_issue_idx).serial_number;
1493 l_output_process_rec.primary_quantity := l_xfer_qty;
1494 l_output_process_rec.transaction_quantity := l_txn_xfer_qty;
1495 l_output_process_rec.secondary_quantity := l_sec_xfer_qty;
1496 l_output_process_rec.grade_code := l_issue_tbl(l_curr_issue_idx).grade_code;
1497 l_output_process_rec.pick_rule_id := l_issue_tbl(l_curr_issue_idx).rule_id;
1498 l_output_process_rec.put_away_rule_id := l_receipt_tbl(l_curr_receipt_idx).rule_id;
1499 l_output_process_rec.reservation_id := l_issue_tbl(l_curr_issue_idx).reservation_id;
1500 l_output_process_rec.lpn_id := l_issue_tbl(l_curr_issue_idx).lpn_id;
1501 inv_detail_util_pvt.add_output(l_output_process_rec);
1502
1503 --
1504 -- get next issue suggestion if suggested issue qty is used up
1505 IF l_issue_tbl(l_curr_issue_idx).primary_quantity = 0 THEN
1506 l_curr_issue_idx := l_curr_issue_idx + 1;
1507 END IF;
1508
1509 -- get next receipt suggestion if suggested receipt qty is used up
1513
1510 IF l_receipt_tbl(l_curr_receipt_idx).primary_quantity = 0 THEN
1511 l_curr_receipt_idx := l_curr_receipt_idx + 1;
1512 END IF;
1514 <<continue>>
1515 NULL;
1516 END LOOP;
1517
1518 inv_detail_util_pvt.process_output(l_return_status
1519 , p_request_line_rec
1520 , p_request_context
1521 , p_plan_tasks);
1522
1523 IF l_return_status = fnd_api.g_ret_sts_error THEN
1524 RAISE fnd_api.g_exc_error;
1525 END IF;
1526
1527 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1528 RAISE fnd_api.g_exc_unexpected_error;
1529 END IF;
1530
1531 x_return_status := l_return_status;
1532
1533 IF l_debug = 1 THEN
1534 log_procedure(l_api_name, 'end', 'End combine_transfer');
1535 END IF;
1536 --
1537 EXCEPTION
1538 WHEN fnd_api.g_exc_error THEN
1539 IF l_issue_csr%ISOPEN THEN
1540 CLOSE l_issue_csr;
1541 END IF;
1542
1543 IF l_receipt_csr%ISOPEN THEN
1544 CLOSE l_receipt_csr;
1545 END IF;
1546
1547 x_return_status := fnd_api.g_ret_sts_error;
1548 IF l_debug = 1 THEN
1549 log_error(l_api_name, 'error', 'Error in combine_transfer');
1550 END IF;
1551 --
1552 WHEN fnd_api.g_exc_unexpected_error THEN
1553 IF l_issue_csr%ISOPEN THEN
1554 CLOSE l_issue_csr;
1555 END IF;
1556
1557 IF l_receipt_csr%ISOPEN THEN
1558 CLOSE l_receipt_csr;
1559 END IF;
1560
1561 x_return_status := fnd_api.g_ret_sts_unexp_error;
1562 IF l_debug = 1 THEN
1563 log_error(l_api_name, 'unexp_error', 'Unexpected error in combine_transfer');
1564 END IF;
1565 --
1566 WHEN OTHERS THEN
1567 IF l_issue_csr%ISOPEN THEN
1568 CLOSE l_issue_csr;
1569 END IF;
1570
1571 IF l_receipt_csr%ISOPEN THEN
1572 CLOSE l_receipt_csr;
1573 END IF;
1574
1575 x_return_status := fnd_api.g_ret_sts_unexp_error;
1576
1577 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1578 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1579 END IF;
1580 IF l_debug = 1 THEN
1581 log_error(l_api_name, 'other_error', 'Other error in combine_transfer');
1582 END IF;
1583 --
1584 END combine_transfer;
1585
1586 -- API name : Create_Suggestions
1587 -- Type : Private
1588 -- Function : Creates pick and/or put away suggestions
1589 -- The program will use WMS pick/put rules/strategies
1590 -- if Oracle WMS is installed; otherwise, rules in
1591 -- mtl_picking_rules will be used.
1592 --
1593 -- Notes
1594 -- 1. Integration with reservations
1595 -- If table p_reservations passed by the calling is not empty, the
1596 -- engine will detailing based on a combination of the info in the
1597 -- move order line (the record that represents detailing request),
1598 -- and the info in p_reservations. For example, a sales order line
1599 -- can have two reservations, one for revision A in quantity of 10,
1600 -- and one for revision B in quantity of 5, and the line quantity
1601 -- can be 15; so when the pick release api calls the engine
1602 -- p_reservations will have two records of the reservations. So
1603 -- if the move order line based on the sales order line does not
1604 -- specify a revision, the engine will merge the information from
1605 -- move order line and p_reservations to create the input for
1606 -- detailing as two records, one for revision A, and one for revision
1607 -- B. Please see documentation for the pick release API for more
1608 -- details.
1609 --
1610 -- 2. Serial Number Detailing in Picking
1611 -- Currently the serial number detailing is quite simple. If the caller
1612 -- gives a range (start, and end) serial numbers in the move order line
1613 -- and pass p_suggest_serial as fnd_api.true, the engine will filter
1614 -- the locations found from a rule, and suggest unused serial numbers
1615 -- in the locator. If p_suggest_serial is passed as fnd_api.g_false
1616 -- (default), the engine will not give serial numbers in the output.
1617 --
1618 -- Input Parameters
1619 -- p_api_version_number standard input parameter
1620 -- p_init_msg_lst standard input parameter
1621 -- p_commit standard input parameter
1622 -- p_validation_level standard input parameter
1623 -- p_transaction_temp_id equals to the move order line id
1624 -- for the detailing request
1625 -- p_reservations reservations for the demand source
1626 -- as the transaction source
1627 -- in the move order line.
1628 -- p_suggest_serial whether or not the engine should suggest
1629 -- serial numbers in the detailing
1630 --
1631 -- Output Parameters
1632 -- x_return_status standard output parameters
1633 -- x_msg_count standard output parameters
1634 -- x_msg_data standard output parameters
1635 -- l_allow_non_partial_rules Set the value to false if
1636 -- l_remaining_quantity returned by compute_pick_detail
1640 --
1637 -- is greater than 0
1638
1639 -- Version : Current version 1.0
1641
1642 PROCEDURE create_suggestions(
1643 p_api_version IN NUMBER
1644 , p_init_msg_list IN VARCHAR2
1645 , p_commit IN VARCHAR2
1646 , p_validation_level IN NUMBER
1647 , x_return_status OUT NOCOPY VARCHAR2
1648 , x_msg_count OUT NOCOPY NUMBER
1649 , x_msg_data OUT NOCOPY VARCHAR2
1650 , p_transaction_temp_id IN NUMBER
1651 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
1652 , p_suggest_serial IN VARCHAR2
1653 , p_simulation_mode IN NUMBER
1654 , p_simulation_id IN NUMBER
1655 , p_plan_tasks IN BOOLEAN
1656 , p_quick_pick_flag IN VARCHAR2
1657 ) IS
1658 l_api_version CONSTANT NUMBER := 1.0;
1659 l_api_name CONSTANT VARCHAR2(30) := 'Create_Suggestions';
1660 l_strategy_id NUMBER;
1661 l_rule_id NUMBER; -- [l_rule_id New Column Added for K ]
1662 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1663 l_msg_count NUMBER;
1664 l_msg_data VARCHAR2(2000);
1665 l_counter INTEGER;
1666 l_type_code NUMBER;
1667 l_number NUMBER;
1668 l_request_context inv_detail_util_pvt.g_request_context_rec_type;
1669 l_request_line_rec inv_detail_util_pvt.g_request_line_rec_type;
1670 l_move_order_type NUMBER;
1671 l_loc_control_from NUMBER;
1672 l_loc_control_to NUMBER;
1673 l_item_control_from NUMBER;
1674 l_item_control_to NUMBER;
1675 l_simulation_mode NUMBER;
1676 l_revert_capacity BOOLEAN;
1677 l_project_id NUMBER;
1678 l_task_id NUMBER;
1679 l_allow_cross_proj_issues VARCHAR2(1);
1680 l_allow_cross_unitnum_issues VARCHAR2(1);
1681 l_unit_number VARCHAR2(30);
1682 l_allow_non_partial_rules BOOLEAN; --- DEFAULT TRUE;
1683 l_return_type VARCHAR2(1);
1684 l_return_type_id NUMBER;
1685 l_sequence_number NUMBER;
1686 l_rules_engine_mode NUMBER := 1; --:= NVL(fnd_profile.VALUE('WMS_RULES_ENGINE_MODE'), 0);
1687 l_wip_rsv_exists NUMBER;
1688
1689 --- Rules J Project Variables
1690 ---
1691 l_lpn_context NUMBER := 0;
1692 l_current_release_level NUMBER := WMS_UI_TASKS_APIS.G_WMS_PATCH_LEVEL;
1693 l_j_release_level NUMBER := WMS_UI_TASKS_APIS.G_PATCHSET_J;
1694 l_quick_pick_flag VARCHAR2(1); -- 'J Project:This variable is used for QuickPick during Inventory Move
1695 -- Values 'Y' - Perform Quick Pick ,
1696 -- 'N' - Do not call quick Pick
1697 -- 'Q' - Perform Quick pick for Version 11.5.9 without qtr_tee creation
1698 --- Switching to New Strategy Search method
1699 l_org_loc_control NUMBER; -- Bug#3051649
1700 l_debug NUMBER; -- 1 for debug is on , 0 for debug is off
1701 l_progress VARCHAR2(10); -- local variable to track program progress,
1702 -- especially useful when exception occurs
1703
1704 -- Added to skip rules processing if pick release process and locator provided bug3237702
1705 l_locator_id NUMBER;
1706 is_pickrelease BOOLEAN;
1707
1708
1709 l_allow_nr_sub_xfer VARCHAR2(1) := 'N' ; -- Bug #4006426
1710
1711 -- LG convergence add
1712 l_pp_transaction_temp_id NUMBER;
1713 -- end LG convergence add
1714
1715 ---
1716 --the following cursors get information used in wms_rule_pvt.apply to
1717 --compare src sub/loc and dest sub/loc. The information is queried here
1718 --and stored in global variables to prevent multiple queries for the
1719 --same info (which would happen if we queried for data in apply)
1720
1721 CURSOR c_move_order_type IS
1722 SELECT move_order_type
1723 FROM mtl_txn_request_headers
1724 WHERE header_id = l_request_line_rec.header_id;
1725
1726 CURSOR c_sub_loc_control_from IS
1727 SELECT locator_type
1728 FROM mtl_secondary_inventories
1729 WHERE secondary_inventory_name = l_request_line_rec.from_subinventory_code
1730 AND organization_id = l_request_line_rec.organization_id;
1731
1732 CURSOR c_sub_loc_control_to IS
1733 SELECT locator_type
1734 FROM mtl_secondary_inventories
1735 WHERE secondary_inventory_name = l_request_line_rec.to_subinventory_code
1736 AND organization_id = l_request_line_rec.to_organization_id;
1737
1738 CURSOR c_item_loc_control_from IS
1739 SELECT location_control_code
1740 FROM mtl_system_items
1741 WHERE inventory_item_id = l_request_line_rec.inventory_item_id
1742 AND organization_id = l_request_line_rec.organization_id;
1743
1744 CURSOR c_item_loc_control_to IS
1748 AND organization_id = l_request_line_rec.to_organization_id;
1745 SELECT location_control_code
1746 FROM mtl_system_items
1747 WHERE inventory_item_id = l_request_line_rec.inventory_item_id
1749
1750 CURSOR c_rule_type_code IS
1751 SELECT type_code
1752 FROM wms_rules_b
1753 WHERE rule_id = p_simulation_id;
1754
1755 CURSOR c_strategy_type_code IS
1756 SELECT type_code
1757 FROM wms_strategies_b
1758 WHERE strategy_id = p_simulation_id;
1759
1760 CURSOR c_project_param IS
1761 SELECT allow_cross_proj_issues
1762 , allow_cross_unitnum_issues
1763 FROM pjm_org_parameters
1764 WHERE organization_id = l_request_line_rec.organization_id;
1765
1766 CURSOR c_wip_reservations IS
1767 SELECT 1
1768 FROM dual
1769 WHERE exists (
1770 SELECT 1
1771 FROM mtl_reservations
1772 WHERE organization_id = l_request_line_rec.organization_id
1773 AND supply_source_type_id = 5
1774 AND supply_source_header_id
1775 = l_request_line_rec.txn_source_id
1776 );
1777
1778 --Bug #3051649 /Grao : Org locator control
1779 CURSOR c_org_loc_control IS
1780 SELECT stock_locator_control_code
1781 FROM mtl_parameters
1782 WHERE organization_id = l_request_line_rec.organization_id;
1783
1784 /* Bug # 5265024
1785 CURSOR l_wms_txn_temp_id_csr IS
1786 SELECT wms_transactions_temp_s.NEXTVAL
1787 FROM DUAL;
1788 */
1789
1790 BEGIN
1791
1792 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
1793 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1794 END IF;
1795 l_debug := g_debug;
1796 l_progress := 10;
1797 --
1798 If l_debug = 1 THEN
1799 log_procedure(l_api_name, 'start', 'Start create_suggestions');
1800 log_event(
1801 l_api_name
1802 , 'start_detail'
1803 , 'Starting the WMS Rules engine ' || 'to allocate material for move order line: '
1804 || p_transaction_temp_id
1805 );
1806 End if;
1807 l_allow_nr_sub_xfer := Upper(NVL( SUBSTR(FND_PROFILE.VALUE('INV_ALLOW_NR_SUB_XFER'),1,1), 'N')) ; -- Bug#4006426
1808
1809 If l_debug = 1 THEN
1810 log_event( l_api_name, 'WMS_ALLOW_NR_SUB_XFER := ', l_allow_nr_sub_xfer );
1811 End if;
1812
1813 -- Standard start of API savepoint
1814 SAVEPOINT create_suggestions_sa;
1815
1816 --
1817 -- Standard Call to check for call compatibility
1818 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1819 RAISE fnd_api.g_exc_unexpected_error;
1820 END IF;
1821
1822 --
1823 -- Initialize message list if p_init_msg_list is set to true
1824 IF fnd_api.to_boolean(p_init_msg_list) THEN
1825 fnd_msg_pub.initialize;
1826 END IF;
1827
1828 --
1829 -- Initialisize API return status to access
1830 x_return_status := fnd_api.g_ret_sts_success;
1831 --
1832 g_trace_header_id := NULL;
1833 g_business_object_id := NULL;
1834 g_sugg_failure_message := NULL; -- Patchset 'J'
1835
1836 -- log_event(l_api_name, 'Create Suggestions', 'J check');
1837 -- 'J Changes Initilize the local Quickpick variable
1838 IF (l_current_release_level >= l_j_release_level
1839 And p_simulation_mode <> 10 -- LG convergence add
1840 ) THEN
1841
1842 IF l_debug = 1 THEN
1843 log_event(l_api_name, 'Check J release', 'Current release is above J. set l_quick_pick_flag to Y');
1844 END IF;
1845 l_quick_pick_flag := nvl(p_quick_pick_flag, 'N');
1846 IF ( l_quick_pick_flag = 'N' and p_simulation_mode IS not NULL) then
1847 --- get LPN Context for the given move order line
1848 --- if LPN_CONTEXT is 1 then set the quick_pick_flag = 'Y'
1849 IF l_debug = 1 THEN
1850 log_event(l_api_name, 'Create Suggestions',
1851 'If in Simulation Mode - Get LPN Context');
1852 END IF;
1853
1854 BEGIN
1855 l_progress := 100;
1856
1857 SELECT lpn_context
1858 INTO l_lpn_context
1859 FROM wms_license_plate_numbers
1860 WHERE lpn_id = (SELECT lpn_id
1861 FROM mtl_txn_request_lines mtrl
1862 WHERE mtrl.line_id = p_transaction_temp_id);
1863 l_progress := 110;
1864
1865 IF l_lpn_context = 1 THEN
1866 l_quick_pick_flag := 'Y';
1867 End if;
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 IF l_debug = 1 THEN
1871 log_error(l_api_name, 'other', 'lpn_context in create_suggestions is not available ');
1872 END IF;
1873 NULL;
1874 END; -- End of Begin
1875
1876 END IF; -- End If l_quick_pick_flag = 'N' and p_simulation_mode IS not NULL
1877
1878 ELSE
1879 /* Bug # 4006426 -- Quick Pick functionality is enabled for 11.5.9 /Version 'I'
1880 Following code is added to check the context of the LPN and the value
1881 of the l_quick_pick_flag is set to 'Q'
1885 check' will be done based on the MOQD and reservation tables. A new local procedure
1882 so that, the calling programs (WMSVPPSB.pls and WMSVPPRB.pls ) could behave differently
1883
1884 New Behavior : Qty tree is not going to be created or queried for performance reasons instead 'Availability
1886 validate_and_insert_noqtytree() will be created to insert data in WTT */
1887
1888 l_quick_pick_flag := 'N';
1889
1890 BEGIN
1891 SELECT lpn_context
1892 INTO l_lpn_context
1893 FROM wms_license_plate_numbers
1894 WHERE lpn_id = l_request_line_rec.lpn_id ; /* (SELECT lpn_id
1895 FROM mtl_txn_request_lines mtrl
1896 WHERE mtrl.line_id = p_transaction_temp_id);*/
1897
1898 IF l_lpn_context = 1 AND l_allow_nr_sub_xfer = 'Y' THEN
1899 l_quick_pick_flag := 'Q';
1900 ELSE
1901 l_quick_pick_flag := 'N';
1902 End if;
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 IF l_debug = 1 THEN
1906 log_error(l_api_name, 'other', 'lpn_context in create_suggestions is not available 1159 ');
1907 END IF;
1908 NULL;
1909 END; -- End of Begin
1910
1911 END IF;
1912
1913 -- log_event(l_api_name, 'Create Suggestions', 'after J check');
1914 --validation simulation mode
1915 -- Simulation mode should = 0 if user passes invalid value
1916 -- for simulation mode, or if user passes simulation mode as 1 or 2,
1917 -- but doesn't pass in simulation_id
1918 IF p_simulation_mode IS NULL
1919 OR (p_simulation_mode < g_full_simulation AND p_simulation_mode <> g_available_inventory)
1920 OR p_simulation_mode > g_put_full_mode THEN
1921 l_simulation_mode := g_no_simulation;
1922 ELSIF p_simulation_id IS NULL
1923 AND p_simulation_mode
1924 IN (g_pick_rule_mode, g_pick_strategy_mode
1925 , g_put_rule_mode, g_put_strategy_mode)
1926 THEN
1927 l_simulation_mode := g_no_simulation;
1928 ELSE
1929 l_simulation_mode := p_simulation_mode;
1930 END IF;
1931 IF l_debug = 1 THEN
1932 log_event(l_api_name, 'Create Suggestions', 'simulation mode '||l_simulation_mode);
1933 END IF;
1934
1935 -- Revert Capacity in any simulation mode
1936 IF l_simulation_mode <> g_no_simulation THEN
1937 l_revert_capacity := TRUE;
1938 ELSE
1939 l_revert_capacity := FALSE;
1940 END IF;
1941
1942 -- validate input and initialize
1943 If l_debug = 1 THEN
1944 log_event(l_api_name, 'Create Suggestions', 'before init');
1945 END IF;
1946 inv_detail_util_pvt.validate_and_init(x_return_status
1947 , p_transaction_temp_id
1948 , p_suggest_serial
1949 , l_request_line_rec
1950 , l_request_context);
1951
1952 IF l_return_status = fnd_api.g_ret_sts_error THEN
1953 RAISE fnd_api.g_exc_error;
1954 END IF;
1955 IF l_debug = 1 THEN
1956 log_event(l_api_name, 'Create Suggestions', 'after init');
1957 log_event(l_api_name, 'Create Suggestions', 'l_return_status '||l_return_status);
1958 log_event(l_api_name, 'Create Suggestions', 'Cross-Doc Data --------------');
1959 log_event(l_api_name, 'Create Suggestions', 'backorder_delivery_detail_id :'||l_request_line_rec.backorder_delivery_detail_id);
1960 log_event(l_api_name, 'Create Suggestions', 'to_subinventory_code :'||l_request_line_rec.to_subinventory_code);
1961 log_event(l_api_name, 'Create Suggestions', 'to_locator_id:'||l_request_line_rec.to_locator_id);
1962
1963 END IF;
1964
1965 g_mo_quantity := l_request_line_rec.primary_quantity ; -- [ Storing the mo qty for tolerance calculations ]
1966 g_Is_xdock := FALSE;
1967 IF l_request_line_rec.backorder_delivery_detail_id is not NULL and
1968 l_request_line_rec.to_subinventory_code is not null and
1969 l_request_line_rec.to_locator_id is not null THEN
1970 g_Is_xdock := TRUE ;
1971 END IF;
1972
1973 /*IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1974 RAISE fnd_api.g_exc_unexpected_error;
1975 END IF;*/
1976 -- Transfer flag is always false when simulating
1977 -- picking rules or strategies. When simulation putaway rules or
1978 -- strategies for transfers, we execute the picking side of the engine
1979 -- normally, so there is not need to change the transfer flag
1980 IF l_simulation_mode IN (g_pick_strategy_mode, g_pick_rule_mode, g_pick_full_mode) THEN
1981 l_request_context.transfer_flag := FALSE;
1982 l_request_context.type_code := 2;
1983 END IF;
1984
1985 --setting global variables used in wms_rule_pvt.
1986 If l_debug = 1 THEN
1987 log_event(l_api_name, 'Create Suggestions', 'before fetching move order type '||l_move_order_type);
1988 END IF;
1989
1990 OPEN c_move_order_type;
1991 FETCH c_move_order_type INTO l_move_order_type;
1992
1993 IF c_move_order_type%NOTFOUND THEN
1994 --produce error
1995 CLOSE c_move_order_type;
1996 RAISE fnd_api.g_exc_unexpected_error;
1997 END IF;
1998
1999 CLOSE c_move_order_type;
2000 IF l_debug = 1 THEN
2004 g_move_order_type := l_move_order_type;
2001 log_event(l_api_name, 'Create Suggestions', 'after fetching move order type '||l_move_order_type);
2002 END IF;
2003
2005 g_transaction_action_id := l_request_context.transaction_action_id;
2006
2007 --the locator control variables are only checked for non-pick-wave
2008 -- move orders
2009 -- GRAO
2010 -- Bug# 2454149 : Same Source and Dest Subinv is allowed for both
2011 -- WIP Issues and Back flush type of MOve orders also.
2012
2013 -- Bug 2666620: BackFlush MO Type Removed
2014 IF l_move_order_type IN (3, 5) THEN --pick wave, WIP type move order
2015 g_dest_sub_pick_allowed := 1;
2016 ELSE
2017 --log_event(l_api_name, 'Create Suggestions', 'else ');
2018 g_dest_sub_pick_allowed := 0;
2019 END IF; -- bug 3972784 populate the globals regardless.
2020 OPEN c_sub_loc_control_from;
2021 FETCH c_sub_loc_control_from INTO l_loc_control_from;
2022
2023 IF c_sub_loc_control_from%NOTFOUND THEN
2024 l_loc_control_from := NULL;
2025 END IF;
2026
2027 CLOSE c_sub_loc_control_from;
2028 OPEN c_sub_loc_control_to;
2029 FETCH c_sub_loc_control_to INTO l_loc_control_to;
2030
2031 IF c_sub_loc_control_to%NOTFOUND THEN
2032 l_loc_control_to := NULL;
2033 END IF;
2034
2035 CLOSE c_sub_loc_control_to;
2036 OPEN c_item_loc_control_from;
2037 FETCH c_item_loc_control_from INTO l_item_control_from;
2038
2039 IF c_item_loc_control_from%NOTFOUND THEN
2040 l_item_control_from := NULL;
2041 END IF;
2042
2043 CLOSE c_item_loc_control_from;
2044 OPEN c_item_loc_control_to;
2045 FETCH c_item_loc_control_to INTO l_item_control_to;
2046
2047 IF c_item_loc_control_to%NOTFOUND THEN
2048 l_item_control_to := NULL;
2049 END IF;
2050
2051 CLOSE c_item_loc_control_to;
2052 g_sub_loc_control := NVL(l_loc_control_from, l_loc_control_to);
2053 g_item_loc_control := NVL(l_item_control_from, l_item_control_to);
2054
2055 --Bug #3051649 /Grao : Org locator control
2056 OPEN c_org_loc_control;
2057 FETCH c_org_loc_control INTO l_org_loc_control ;
2058 IF c_org_loc_control%NOTFOUND THEN
2059 l_org_loc_control := NULL;
2060 END IF;
2061 g_org_loc_control := l_org_loc_control;
2062 CLOSE c_org_loc_control;
2063
2064
2065
2066 --bug 2589499 -- if reservation exists for WIP putaway, do not putaway
2067 -- to non-reservable sub.
2068 --set a global variable here, and reference it in WMS_RULE_PVT
2069 g_reservable_putaway_sub_only := FALSE;
2070 l_wip_rsv_exists := 0;
2071
2072 -- log_event(l_api_name, 'Create Suggestions', 'after all the item check');
2073 --for putaway move orders, set posting flag to N (it is Y by default)
2074 IF l_move_order_type = 6 THEN -- put away move order
2075 -- l_request_context.posting_flag := 'N'; -- bug fix 3438349
2076
2077 If l_request_context.transaction_source_type_id = 5 And
2078 l_request_line_rec.transaction_type_id = 44 Then
2079
2080 OPEN c_wip_reservations;
2081 FETCH c_wip_reservations INTO l_wip_rsv_exists;
2082 IF c_wip_reservations%NOTFOUND THEN
2083 l_wip_rsv_exists := 0;
2084 END IF;
2085 CLOSE c_wip_reservations; -- Bug # 4997883
2086 IF l_wip_rsv_exists = 1 THEN
2087 g_reservable_putaway_sub_only := TRUE;
2088 END IF;
2089 End If;
2090 END IF;
2091
2092 g_serial_number_control_code:=l_request_context.item_serial_control_code;
2093 --don't detail serial numbers for items that are serial controlled only
2094 -- at sales order issue
2095 IF l_request_context.item_serial_control_code = 6 THEN
2096 l_request_context.item_serial_control_code := 1;
2097 END IF;
2098
2099 --log_event(l_api_name, 'Create Suggestions', 'before sys_task_type');
2100 /* Get the wms system task type
2101 changed the call to add 2 new paramters p_transaction_Action_id
2102 and p_transaction_source_type_id for patchset H changes */
2103 wms_rule_pvt.get_wms_sys_task_type(
2104 p_move_order_type => l_move_order_type
2105 , p_transaction_action_id => l_request_context.transaction_action_id
2106 , p_transaction_source_type_id => l_request_context.transaction_source_type_id
2107 , x_wms_sys_task_type => l_request_context.wms_task_type
2108 );
2109 --log_event(l_api_name, 'Create Suggestions', 'after sys_task_type');
2110
2111 INSERT INTO wms_txn_context_temp
2112 (
2113 line_id
2114 , txn_source_id
2115 , txn_source_line_id
2116 , txn_source_name
2117 , txn_source_line_detail
2118 , freight_carrier_code
2119 , customer_id
2120 )
2121 VALUES (
2122 l_request_line_rec.line_id
2123 , l_request_context.txn_header_id
2124 , l_request_context.txn_line_id
2125 , NULL
2126 , l_request_context.txn_line_detail
2127 , l_request_context.freight_code
2128 , l_request_context.customer_id
2129 );
2130
2131 -- Check whether this is a pick release process and if locator is specified bug3237702
2132 If inv_cache.is_pickrelease then
2136
2133 is_pickrelease := true;
2134 l_locator_id := inv_cache.tolocator_id;
2135 End if;
2137 -- log_event(l_api_name, 'Create Suggestions', 'after insert txn_context');
2138 -- the first round will deal with issues, receipts
2139 -- and the issue part of transfers
2140 -- the second round will deal with the receipt part
2141 -- of transfers
2142 l_type_code := l_request_context.type_code;
2143 -- 'J Project' : Setting the quick_pick parameter to false , if it is putaway ---
2144 --IF (l_current_release_level >= l_j_release_level ) THEN --- Bug # 4006426
2145 IF l_type_code = 1 then
2146 l_quick_pick_flag := 'N';
2147 END IF;
2148 --END IF; -- Bug#4006426
2149 FOR l_counter IN 1 .. 2 LOOP
2150 -- if not a transfer, or if we are simulating strategy or rule,
2151 -- or simulation if for available inventory LG.
2152 -- no need to call Search and Apply a second time.
2153 IF l_counter = 2
2154 AND (l_request_context.transfer_flag = FALSE
2155 OR l_simulation_mode = g_available_inventory
2156 )
2157 THEN
2158 EXIT;
2159 END IF;
2160
2161 -- Find strategies when
2162 -- a. not simulating
2163 -- b. simulating the full pick process
2164 -- c. simulating the full put away process
2165 -- d. simulate put rule or strategy, you have to find pick strategy
2166 -- for transfers
2167
2168 IF l_simulation_mode IN (g_full_simulation, g_no_simulation, g_available_inventory) -- LG convergence
2169 OR l_simulation_mode IN (g_pick_full_mode, g_put_full_mode, g_pick_rule_mode, g_put_rule_mode
2170 ,g_put_strategy_mode, g_pick_strategy_mode) --Bug#6015798, Bug#7182139
2171 OR (l_simulation_mode IN (g_put_strategy_mode, g_put_rule_mode)
2172 AND l_request_context.transfer_flag = TRUE
2173 AND l_counter = 1
2174 ) THEN
2175 IF l_counter = 1 THEN
2176 IF l_request_context.type_code = 2 THEN
2177 IF l_debug = 1 THEN
2178 log_event(l_api_name, 'start_pick', 'Starting pick allocation');
2179 END IF;
2180 l_strategy_id := l_request_context.pick_strategy_id;
2181 IF l_simulation_mode = g_pick_strategy_mode THEN
2182 l_strategy_id := p_simulation_id;
2183 END IF;
2184 ELSE
2185 IF l_debug = 1 THEN
2186 log_event(l_api_name, 'start_put_only', 'Starting put away allocation');
2187 END IF;
2188 l_strategy_id := l_request_context.put_away_strategy_id;
2189 END IF;
2190 ELSE
2191 IF l_debug = 1 then
2192 log_event(l_api_name, 'start_put', 'Starting put away allocation');
2193 END IF;
2194 l_strategy_id := l_request_context.put_away_strategy_id;
2195 l_type_code := 1; -- put away for the second round
2196 END IF;
2197 --Begin bug 4749595/4769085
2198 IF l_type_code = 1 then
2199 l_quick_pick_flag := 'N';
2200 END IF;
2201 --End bug 4749595/4769085
2202
2203 IF l_debug = 1 THEN
2204 log_statement(l_api_name, 'strategy_search', 'modified set l_quick_pick_flag '|| l_quick_pick_flag);
2205 log_event(l_api_name, 'start_pick', 'getting the context ');
2206 END IF;
2207
2208 -- clean up the input records first
2209 DELETE FROM wms_transactions_temp
2210 WHERE transaction_temp_id = p_transaction_temp_id
2211 AND line_type_code = 1;
2212
2213 --
2214 IF p_simulation_mode <> 10 THEN -- LG convergenece add
2215 IF l_counter = 1 THEN
2216 -- Prepare transaction records for pp engine
2217 -- for transfers only the issue is considered at this time
2218 prepare(l_return_status
2219 , l_request_line_rec
2220 , l_request_context
2221 , p_reservations
2222 , l_allow_non_partial_rules);
2223
2224 --
2225 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2226 RAISE fnd_api.g_exc_error;
2227 END IF;
2228 ELSE
2229 -- Treat receipt for transfers, ie. copy the issue output data in
2230 -- WMS_TRANSACTIONS_TEMP as new input records for receipt part
2231 prepare_transfer_receipt(l_return_status
2232 , l_request_line_rec
2233 , l_request_context);
2234 --
2235 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2236 RAISE fnd_api.g_exc_error;
2237 END IF;
2238 END IF;
2239 END IF;
2240 -- LG convergence add
2241 -- Since we may not have a reservation when p_simulation_mode is 10 for all move order types
2242 -- so insert fake data into wms_transactions_temp
2243 IF p_simulation_mode = g_available_inventory
2244 THEN
2245 /* 5265024
2246 OPEN l_wms_txn_temp_id_csr;
2247 FETCH l_wms_txn_temp_id_csr INTO l_pp_transaction_temp_id ;
2248 CLOSE l_wms_txn_temp_id_csr;
2249 */
2250 log_event(l_api_name, '','insert into wtt '||l_pp_transaction_temp_id);
2251 INSERT INTO wms_transactions_temp
2252 (
2253 pp_transaction_temp_id
2257 , transaction_quantity
2254 , transaction_temp_id -- mo_line_id
2255 , type_code -- mo
2256 , line_type_code -- 1
2258 , primary_quantity
2259 , secondary_quantity
2260 )
2261 VALUES ( wms_transactions_temp_s.NEXTVAL
2262 -- l_pp_transaction_temp_id
2263 , l_request_line_rec.line_id
2264 , 2
2265 , 1
2266 , l_request_line_rec.quantity
2267 , l_request_line_rec.quantity
2268 , l_request_line_rec.secondary_quantity
2269 );
2270 END IF;
2271 -- end LG convergence
2272 --
2273 IF (wms_rule_pvt.isruledebugon(l_simulation_mode)
2274 and l_simulation_mode <> g_available_inventory) THEN
2275 IF l_debug = 1 THEN
2276 log_procedure(l_api_name, 'insert_trace_header', 'Calling insert_trace_header ');
2277 END IF;
2278 wms_search_order_globals_pvt.insert_trace_header(
2279 p_api_version => 1.0
2280 , p_init_msg_list => fnd_api.g_false
2281 , p_validation_level => fnd_api.g_valid_level_full
2282 , x_return_status => l_return_status
2283 , x_msg_count => l_msg_count
2284 , x_msg_data => l_msg_data
2285 , x_header_id => g_trace_header_id
2286 , p_pick_header_id => g_trace_header_id
2287 , p_move_order_line_id => p_transaction_temp_id
2288 , p_total_qty => l_request_line_rec.quantity - l_request_line_rec.quantity_detailed
2289 , p_secondary_total_qty => l_request_line_rec.secondary_quantity - l_request_line_rec.secondary_quantity_detailed
2290 , p_type_code => l_type_code
2291 , p_business_object_id => g_business_object_id
2292 , p_object_id => l_sequence_number
2293 , p_strategy_id => l_strategy_id
2294 );
2295
2296 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2297 RAISE fnd_api.g_exc_error;
2298 END IF;
2299 END IF;
2300
2301 -- Added to support PJM
2302 -- Query the columns ALLOW_CROSS_PROJ_ISSUES and ALLOW_CROSS_UNITNUM_ISSUES
2303 -- from the table pjm_org_Parameters where
2304 -- If ALLOW_CROSS_PROJ_ISSUES is Y, then set project and task to NULL
2305 -- for picks only.
2306 -- If ALLOW_CROSS_UNITNUM_ISSUES is Y, set the unit_number to NULL.
2307 -- BUG 2880682 : Treating null project/task as common project/task and forcing
2308 -- picking from common inventory when allow_cross_proj_issues is off.
2309 -- The same holds for unit number
2310 l_project_id := l_request_line_rec.project_id;
2311 l_task_id := l_request_line_rec.task_id;
2312 l_unit_number := l_request_line_rec.unit_number;
2313
2314 IF l_type_code = 2 THEN
2315 OPEN c_project_param;
2316 FETCH c_project_param INTO l_allow_cross_proj_issues, l_allow_cross_unitnum_issues;
2317
2318 IF c_project_param%NOTFOUND THEN
2319 l_allow_cross_proj_issues := 'Y';
2320 l_allow_cross_unitnum_issues := 'Y';
2321 END IF;
2322
2323 CLOSE c_project_param;
2324
2325 IF (l_allow_cross_proj_issues = 'Y') THEN
2326 l_project_id := NULL;
2327 l_task_id := NULL;
2328 ELSE
2329 l_project_id := nvl(l_project_id, -7777);
2330 l_task_id := nvl(l_task_id, -7777);
2331 END IF;
2332
2333 IF l_allow_cross_unitnum_issues = 'Y' THEN
2334 l_unit_number := NULL;
2335 ELSE
2336 l_unit_number := nvl(l_unit_number, '-7777');
2337 END IF;
2338 END IF;
2339 ------------------------
2340 -- [[
2341
2342 --Added bug3237702
2343 -- search for a strategy if not given in the input parameter
2344 -- If pick_release and locator supplied and doing Putaway side of transaction
2345 -- then no need to use rule
2346
2347 -- [ Setting g_use_rule flag = 'Y' for follwing cases ]
2348 -- Case 1. If pick release and putaway loop and locator is not null - Pick release
2349 -- Case 2. If Detailed reservations exsist for the total requested qty and the item is serial - handled in prepare()
2350 -- Case 3. For Cross-docking putaway
2351
2352 -- [Case 1
2353 IF (is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL ) THEN
2354 g_use_rule := 'N' ;
2355 END IF;
2356 -- [Case 3
2357 If (g_Is_xdock and l_type_code = 1) THEN
2358 g_use_rule := 'N' ;
2359 END IF;
2360 -----
2361 IF l_debug = 1 THEN
2362 log_event(l_api_name, 'Setting g_use_rule', g_use_rule);
2363 IF (is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL ) THEN
2364 log_statement(l_api_name, 'Case 2:' , 'Pick release + Putaway loop + Dest. Locator is not null');
2365 ELSIF (g_Is_xdock and l_type_code = 1) THEN
2366 log_statement(l_api_name, 'Case 3:' , 'Cross-dock + Putaway + Dest. Locator is not null');
2367 ELSE
2368 log_statement(l_api_name, 'Case 1:' , 'Serial detailed resv + Picking');
2369 END IF;
2370 END IF;
2371 -----
2375 log_event(l_api_name, 'strategy_search', 'Strategy not defined on move order line. Calling '
2372 If (l_strategy_id IS NULL) /* AND NOT (is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL)*/ THEN
2373 --IF l_strategy_id IS NULL THEN Bug 3237702 ends
2374 IF l_debug = 1 THEN
2376 || 'the strategy search procedure');
2377 log_statement(l_api_name, 'strategy_search', 'l_quick_pick_flag '|| l_quick_pick_flag );
2378 log_statement(l_api_name, 'strategy_search', 'g_use_rule '|| g_use_rule);
2379 END IF;
2380 -- [ Added the condition of - g_use_rule = 'Y' ]
2381 IF nvl(l_quick_pick_flag, 'N') = 'N' AND ( g_use_rule = 'Y' ) THEN
2382 IF l_debug = 1 THEN
2383 log_event(l_api_name, 'Calling Strategy Search', 'wms_rules_workbench_pvt.search()');
2384 log_statement(l_api_name, 'p_transaction_temp_id =>' ,p_transaction_temp_id);
2385 END IF;
2386 l_return_type := 0;
2387 l_return_type_id := 0;
2388 l_rule_id := NULL; --- Bug# 5178290 / 5233300
2389 wms_rules_workbench_pvt.search(
2390 p_api_version => 1.0
2391 , p_init_msg_list => fnd_api.g_false
2392 , p_validation_level => fnd_api.g_valid_level_none
2393 , x_return_status => l_return_status
2394 , x_msg_count => l_msg_count
2395 , x_msg_data => l_msg_data
2396 , p_transaction_temp_id => p_transaction_temp_id
2397 , p_type_code => l_type_code
2398 , x_return_type => l_return_type
2399 , x_return_type_id => l_return_type_id
2400 , p_organization_id => l_request_line_rec.organization_id
2401 , x_sequence_number => l_sequence_number
2402 );
2403
2404 IF l_debug = 1 THEN
2405 log_event(l_api_name, 'End Search', 'Values returned ..');
2406 log_statement(l_api_name, 'l_return_status =>' ,l_return_status);
2407 log_statement(l_api_name, 'p_organization_id =>' ,l_request_line_rec.organization_id);
2408 log_statement(l_api_name, 'l_type_code =>' ,l_type_code);
2409 log_statement(l_api_name, 'l_sequence_number =>' ,l_sequence_number);
2410 log_statement(l_api_name, 'l_return_type =>' ,l_return_type);
2411 log_statement(l_api_name, 'l_return_type_id =>' ,l_return_type_id);
2412 END IF;
2413
2414 -- If no strategy is assigned, still detail, but
2415 -- with no strategy or rules
2416
2417 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2418
2419 IF l_debug = 1 THEN
2420 log_event(
2421 l_api_name
2422 , 'no_strategy_found'
2423 , 'The strategy search function did not find an ' || 'eligible strategy for this move order.'
2424 );
2425 END IF;
2426
2427 l_strategy_id := NULL;
2428 ELSE -- for assigning strategy Id based on the Return type , if it is 'S'
2429
2430 IF l_return_type = 'S' THEN
2431 l_strategy_id := l_return_type_id;
2432 l_rule_id := NULL;
2433 -- [ Based on the rule assignments , rule work bench returns the rule_id ]
2434 ELSIF l_return_type = 'R' THEN
2435 l_rule_id := l_return_type_id;
2436 l_strategy_id := NULL;
2437 END IF;
2438 IF l_debug = 1 THEN
2439 log_statement(l_api_name, 'wms_rules_workbench_pvt.search()', 'l_strategy_id '|| l_strategy_id );
2440 ELSE
2441 log_statement(l_api_name, 'wms_rules_workbench_pvt.search()', 'l_rule_id '|| l_rule_id );
2442 END IF;
2443
2444 END IF; -- FND_API.G_RET_STS_SUCCESS
2445
2446 END IF; -- J PROJECT : if not quickpick
2447 END IF; --STRATEGY ID / Rule ID is NULL
2448
2449 ELSIF l_simulation_mode IN (g_pick_strategy_mode, g_put_strategy_mode) THEN
2450 l_strategy_id := p_simulation_id;
2451 ELSE
2452 l_strategy_id := NULL;
2453 END IF;
2454 -- [ setting the flag g_use_rule for putaway
2455 -- all the code to use/notuse rules search will be streamlined
2456 -- Logic : if the search API is not called , then set the rule id to -999
2457 -- so that it could be handled in the strategy program as a special program
2458 -- Case 1. Pick - Serial reservation exisit for the total requested qty
2459 -- Case 2. Pick- detailed reservations exisit and org-level override rule flag
2460 -- from the mtl_parameters is 'Yes' - OPM case
2461 -- Case 3. Put away - For pickrelease and if destnation locator exisit
2462 -- Case 4. Putaway - For non WMS enabled org, do not call putaway rules
2463 -- Case 5. Putaway - For Crossdock putaaway , do not call putaway rules
2464 --
2465 --]
2466 IF g_use_rule = 'N' THEN
2467 g_use_rule := 'Y';
2468 l_rule_id := -999;
2469 l_strategy_id := NULL;
2470 END IF;
2471 --
2472 -- For put away strategy and rule, set type code to put away on
2473 -- second time through the loop. Type code for putaway full is
2474 -- set above
2475 IF l_simulation_mode IN (g_put_strategy_mode, g_put_rule_mode)
2476 AND l_counter = 2 THEN
2477 l_type_code := 1;
2478 END IF;
2479 -- record the strategy ids in the package globals
2480 IF l_counter = 1 THEN
2481 IF l_type_code = 2 THEN
2482 l_request_context.pick_strategy_id := l_strategy_id;
2483 ELSE
2487 l_request_context.put_away_strategy_id := l_strategy_id;
2484 l_request_context.put_away_strategy_id := l_strategy_id;
2485 END IF;
2486 ELSE
2488 END IF;
2489
2490 --]]
2491 -------------------------
2492 display_temp_records;
2493 IF l_debug = 1 THEN
2494 log_event(l_api_name,'Engine_pvt', 'Calling wms_strategy.apply()' );
2495 log_statement(l_api_name, 'Engine_pvt', 'Begin allocating ' || 'for strategy: ' || l_strategy_id);
2496 End if;
2497 -- If pick release then no strategy or Rule
2498 IF is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL THEN
2499 l_strategy_id := NULL;
2500 END IF;
2501
2502 wms_strategy_pvt.apply
2503 (
2504 p_api_version => 1.0,
2505 p_init_msg_list => fnd_api.g_false,
2506 p_validation_level => fnd_api.g_valid_level_none,
2507 x_return_status => l_return_status,
2508 x_msg_count => l_msg_count,
2509 x_msg_data => l_msg_data,
2510 p_transaction_temp_id => p_transaction_temp_id,
2511 p_type_code => l_type_code,
2512 p_strategy_id => l_strategy_id,
2513 p_rule_id => l_rule_id,
2514 p_detail_serial => l_request_context.detail_serial,
2515 p_from_serial => l_request_line_rec.serial_number_start,
2516 p_to_serial => l_request_line_rec.serial_number_end,
2517 p_detail_any_serial => l_request_context.detail_any_serial,
2518 p_unit_volume => l_request_context.unit_volume,
2519 p_volume_uom_code => l_request_context.volume_uom_code,
2520 p_unit_weight => l_request_context.unit_weight,
2521 p_weight_uom_code => l_request_context.weight_uom_code,
2522 p_base_uom_code => l_request_context.base_uom_code,
2523 p_lpn_id => l_request_line_rec.lpn_id,
2524 p_unit_number => l_unit_number,
2525 p_allow_non_partial_rules => l_allow_non_partial_rules,
2526 p_simulation_mode => l_simulation_mode,
2527 p_simulation_id => p_simulation_id,
2528 p_project_id => l_project_id,
2529 p_task_id => l_task_id,
2530 p_quick_pick_flag => l_quick_pick_flag
2531 );
2532 --Bug3237702 ends
2533 -- IF (l_current_release_level >= l_j_release_level ) THEN -- Commented for Bug# 4006426
2534 IF l_quick_Pick_flag = 'Y' and l_type_code = 2 then
2535 l_quick_Pick_flag := 'N';
2536 IF l_debug = 1 THEN
2537 log_event(l_api_name, ' Create suggestions', ' Setting the value of l_quick_Pick');
2538 END IF;
2539 IF l_return_status <> fnd_api.g_ret_sts_success then
2540 RAISE fnd_api.g_exc_error;
2541 ELSE
2542 IF ((WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE = 'WMS_ATT_SUB_STATUS_NA' ) or
2543 (WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE = 'WMS_ATT_SERIAL_STATUS_NA' )) then
2544 IF l_debug = 1 THEN
2545 log_event(l_api_name, 'Create Suggestions',
2546 'Quick Pick Validation failure message '
2547 || WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE );
2548 END IF;
2549 ROLLBACK TO create_suggestions_sa;
2550 exit;
2551 END IF;
2552 END IF;
2553 END IF;
2554 /* Commented for Bug #4006426
2555 ELSE
2556 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2557 RAISE fnd_api.g_exc_error;
2558 END IF;
2559 END IF;
2560 */
2561 display_temp_records;
2562 END LOOP;
2563 --
2564 --
2565
2566 IF l_revert_capacity THEN
2567
2568 IF l_debug = 1 THEN
2569 log_event(l_api_name, 'Create Suggestions',
2570 'calling rollback_capacity for item '
2571 || l_request_line_rec.inventory_item_id);
2572 END IF;
2573
2574 wms_rule_pvt.rollback_capacity_update(
2575 x_return_status => l_return_status
2576 , x_msg_count => l_msg_count
2577 , x_msg_data => l_msg_data
2578 , p_organization_id => l_request_line_rec.organization_id
2579 , p_inventory_item_id => l_request_line_rec.inventory_item_id
2580 );
2581
2582 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2583 IF l_debug = 1 THEN
2584 log_event(l_api_name, 'Create Suggestions','err in rollback_capacity: '|| l_msg_data);
2585 END IF;
2586 RAISE fnd_api.g_exc_error;
2587 END IF;
2588 END IF;
2589
2590 -- Insert rows to MTL_MATERIAL_TRANSACTIONS_TEMP,
2591 -- MTL_TRANSACTIONLOTS_TEMP, MTL_SERIAL_NUMBERS_TEMP.
2592 -- For transfers combine issue and receipt suggestions
2593 -- to complete transfer transaction
2594 -- Skip this step when simulating rule or strategy; we want to
2595 -- keep records in WTT, and don't want to insert into MMTT.
2596 IF l_simulation_mode IN (g_full_simulation, g_no_simulation) THEN
2597 --added by jcearley on 11/22/99 - output table must be initialized
2598 inv_detail_util_pvt.init_output_process_tbl;
2599
2600 IF l_request_context.transaction_action_id IN (2, 3, 28) THEN
2601 combine_transfer(l_return_status, l_request_line_rec, l_request_context, p_plan_tasks);
2602 ELSE
2603 output_issue_or_receipt(l_return_status, l_request_line_rec, l_request_context, p_plan_tasks);
2604 END IF;
2605
2606 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2607 RAISE fnd_api.g_exc_error;
2608 END IF;
2609
2610 IF l_simulation_mode = g_no_simulation
2611 AND wms_rule_pvt.isruledebugon(l_simulation_mode) THEN
2612 --call insert run time trace lines
2613
2614 IF l_debug = 1 THEN
2615 log_procedure(l_api_name, 'insert_txn_trace_rows',
2616 'Calling insert_txn_trace_rows ');
2617 END IF;
2618 wms_search_order_globals_pvt.insert_txn_trace_rows(
2619 p_api_version => 1.0
2620 , p_init_msg_list => fnd_api.g_false
2621 , p_validation_level => fnd_api.g_valid_level_full
2622 , x_return_status => l_return_status
2623 , x_msg_count => l_msg_count
2624 , x_msg_data => l_msg_data
2625 , p_txn_header_id => inv_detail_util_pvt.g_transaction_header_id
2626 , p_insert_lot_flag => inv_detail_util_pvt.g_insert_lot_flag
2627 , p_insert_serial_flag => inv_detail_util_pvt.g_insert_serial_flag
2628 );
2629 END IF;
2630
2631 -- Delete records from WMS_TRANSACTIONS_TEMP
2632 purge_detail_temp_records(l_return_status, l_request_line_rec);
2633
2634 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2635 RAISE fnd_api.g_exc_error;
2636 END IF;
2637 --
2638 END IF;
2639
2640 -- Standard check of p_commit
2641 IF fnd_api.to_boolean(p_commit) THEN
2642 COMMIT;
2643 END IF;
2644
2645 /*-- debugging section
2646 -- can be commented out for final code
2647 IF inv_pp_debug.is_debug_mode THEN
2648 inv_pp_debug.send_message_to_pipe('exit '|| g_pkg_name || '.' || l_api_name);
2649 END IF; */
2650
2651 IF l_debug = 1 THEN
2652 log_procedure(l_api_name, 'exit' , g_pkg_name || '.' || l_api_name);
2653 END IF;
2654
2655 -- Patchset 'J' : New Error_messages
2656 -- Adding the Suggestion failure message to the message stack
2657 x_msg_data := nvl(WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE, '');
2658
2659 --BUG 3440344: We should not add message to the stack if
2660 --G_SUGG_FAILURE_MESSAGE is null
2661 IF x_msg_data IS NOT NULL OR x_msg_data <> '' THEN
2662 FND_MESSAGE.SET_NAME('WMS',WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE);
2663 FND_MSG_PUB.ADD;
2664 END IF;
2665
2666 IF l_debug = 1 THEN
2667 log_procedure(l_api_name, 'end G_SUGG_FAILURE_MESSAGE', x_msg_data );
2668 log_procedure(l_api_name, 'End', 'End create_suggestions');
2669 END IF;
2670 --
2671 EXCEPTION
2672 WHEN fnd_api.g_exc_error THEN
2673 ROLLBACK TO create_suggestions_sa;
2674 x_return_status := fnd_api.g_ret_sts_error;
2675 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2676 IF l_debug = 1 THEN
2677 log_error(l_api_name, 'error', 'Error in create_suggestions - ' || x_msg_data);
2678 END IF ;
2679 --
2680 WHEN fnd_api.g_exc_unexpected_error THEN
2681 ROLLBACK TO create_suggestions_sa;
2682 x_return_status := fnd_api.g_ret_sts_unexp_error;
2683 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2684 IF l_debug = 1 THEN
2685 log_error(l_api_name, 'unexp_error', 'Unexpected error ' || 'in create_suggestions - ' || x_msg_data);
2686 END IF;
2687 --
2688 WHEN OTHERS THEN
2689 ROLLBACK TO create_suggestions_sa;
2690 x_return_status := fnd_api.g_ret_sts_unexp_error;
2691
2692 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2693 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2694 END IF;
2695 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2696 IF l_debug = 1 THEN
2697 log_error(l_api_name, 'other_error', 'Other error ' || 'in create_suggestions - ' || x_msg_data);
2698 END IF;
2699
2700 END create_suggestions;
2701 --
2702 END wms_engine_pvt;
[Home] [Help]