1 PACKAGE BODY wms_engine_pvt AS
2 /* $Header: WMSVPPEB.pls 120.33.12020000.6 2013/02/22 21:49:55 sahmahes 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
386 -- log_statement(l_api_name, 'resolve_serials', l_wms_txn_temp_tbl(l_counter).serial_number );
387 -- end if;
388 END LOOP;
389
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_remaining_sec_qty NUMBER;
485 l_allow_non_partial_rules BOOLEAN;
486 l_debug NUMBER;
487 l_rsv_ctr NUMBER; -- [ Added to track number of detailed serial numbers ]
488 --
489 BEGIN
490 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
491 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
492 END IF;
493 l_debug := g_debug;
494 IF l_debug = 1 THEN
495 log_procedure(l_api_name, 'start', 'Start Prepare');
496 END IF;
497
498 -- Initialisize API return status to access
499 x_return_status := fnd_api.g_ret_sts_success;
500
501 --
502 -- check serial number control and support
503 IF l_debug =1 then
504 log_statement(l_api_name,'p_request_context.item_serial_control_code ', p_request_context.item_serial_control_code);
505 log_statement(l_api_name,'l_serial_support ', l_serial_support);
506 log_event(l_api_name, 'prepare', 'prepare' );
507 END IF;
508
509 IF p_request_context.item_serial_control_code IN (2, 5, 6)
510 AND l_serial_support = 'Y' THEN
511 --
512 -- Resolve FM_SERIAL_number and TO_SERIAL_number
513 -- and insert one record for
514 -- each serial number into WMS_TRANSACTION_TEMP
515 --
516 -- Here we assume that the number of serial numbers in the
517 -- range and the transaction_quantity are the same
518 --
519 -- Important!!!!!!
520 -- Currently this program does not handle the case that
521 -- requires both serial number support and detail based on
522 -- reservations. So you can not just change the value of
523 -- l_serial_support to Y and expect the code will work correctly!
524 --
525 -- The fuctionality in inv_detail_util_pvt for detailing serial
526 -- numbers is different from what I mean here. Over there
527 -- we do not use any pick and put away rules, just check
528 -- which serial number is free and take it. Here when we
529 -- say detailing serial numbers, we mean we will check the
530 -- rules defined by users and rules can specify constraints
531 -- or sort preferences using serial number attributes
532 --
533 -- Bin Tang 10/20/1999
534 --
535
536 IF l_debug = 1 THEN
537 log_statement(l_api_name , 'Calling resolve_serials() ', '');
538 END IF;
539 resolve_serials(l_return_status, p_request_line_rec, p_request_context);
540 IF l_return_status = fnd_api.g_ret_sts_error THEN
541 RAISE fnd_api.g_exc_error;
542 END IF;
543 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
544 RAISE fnd_api.g_exc_unexpected_error;
545 END IF;
546 ELSE
547 IF p_request_context.transfer_flag
548 OR p_request_context.type_code = 2 THEN -- pick only or transfer
549 inv_detail_util_pvt.compute_pick_detail_level(
550 l_return_status
551 , p_request_line_rec
552 , p_request_context
553 , p_reservations
554 , l_detail_level_tbl
555 , l_detail_level_tbl_size
556 , l_remaining_quantity
557 , l_remaining_sec_qty
558 );
559
560 -- Bug # 2286454 ----
561 --
562 IF (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_remaining_quantity > 0)
563 OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_remaining_sec_qty > 0)
564 THEN
565 l_allow_non_partial_rules := FALSE;
566 ELSE
567 l_allow_non_partial_rules := TRUE;
568 END IF;
569
570 x_allow_non_partial_rules := l_allow_non_partial_rules;
571
572 --
573 IF l_debug = 1 THEN
574 log_event(l_api_name, 'prepare ', 'prepare detail_level_tbl_size '||l_detail_level_tbl_size);
575 END IF;
576
577 l_rsv_ctr := 0 ; -- [ reset the rsv_counter ]
578
579 FOR l_index IN 1 .. l_detail_level_tbl_size LOOP
580 l_wms_txn_temp_tbl(l_index).transaction_temp_id := p_request_line_rec.line_id;
581 l_wms_txn_temp_tbl(l_index).type_code := p_request_context.type_code;
582 l_wms_txn_temp_tbl(l_index).line_type_code := 1; -- input
583 l_wms_txn_temp_tbl(l_index).transaction_quantity := l_detail_level_tbl(l_index).transaction_quantity;
584 l_wms_txn_temp_tbl(l_index).primary_quantity := l_detail_level_tbl(l_index).primary_quantity;
585 l_wms_txn_temp_tbl(l_index).secondary_quantity := l_detail_level_tbl(l_index).secondary_quantity;
586 l_wms_txn_temp_tbl(l_index).grade_code := l_detail_level_tbl(l_index).grade_code;
587 l_wms_txn_temp_tbl(l_index).revision := l_detail_level_tbl(l_index).revision;
588 l_wms_txn_temp_tbl(l_index).lot_number := l_detail_level_tbl(l_index).lot_number;
589 --log_event(l_api_name, 'prepare ', 'in prepare sec qty '||l_detail_level_tbl(l_index).secondary_quantity);
590
591 IF l_wms_txn_temp_tbl(l_index).lot_number IS NOT NULL THEN
592 l_wms_txn_temp_tbl(l_index).lot_expiration_date :=
593 inv_detail_util_pvt.get_lot_expiration_date(
594 p_request_line_rec.organization_id
595 , p_request_line_rec.inventory_item_id
596 , l_wms_txn_temp_tbl(l_index).lot_number
597 );
598 END IF;
599
600 l_wms_txn_temp_tbl(l_index).from_organization_id := p_request_line_rec.organization_id;
601 l_wms_txn_temp_tbl(l_index).to_organization_id := p_request_line_rec.to_organization_id;
602 l_wms_txn_temp_tbl(l_index).from_cost_group_id := p_request_line_rec.from_cost_group_id;
603 l_wms_txn_temp_tbl(l_index).to_cost_group_id := p_request_line_rec.to_cost_group_id;
604 l_wms_txn_temp_tbl(l_index).from_subinventory_code := l_detail_level_tbl(l_index).subinventory_code;
605 l_wms_txn_temp_tbl(l_index).from_locator_id := l_detail_level_tbl(l_index).locator_id;
606 l_wms_txn_temp_tbl(l_index).to_subinventory_code := p_request_line_rec.to_subinventory_code;
607 l_wms_txn_temp_tbl(l_index).to_locator_id := p_request_line_rec.to_locator_id;
608 l_wms_txn_temp_tbl(l_index).reservation_id := l_detail_level_tbl(l_index).reservation_id;
609 l_wms_txn_temp_tbl(l_index).serial_number := l_detail_level_tbl(l_index).serial_number; --- [ new code ]
610 l_wms_txn_temp_tbl(l_index).lpn_id := l_detail_level_tbl(l_index).lpn_id;
611
612 --[ Seting the flag to determine , if strategy/rule search API will be called or not ]
613 IF l_detail_level_tbl(l_index).serial_resv_flag = 'Y' THEN
614 l_rsv_ctr := l_rsv_ctr + 1;
615 END IF;
616
617 IF l_debug = 1 THEN
618 log_event(l_api_name, 'prepare ', 'serial_number '||l_detail_level_tbl(l_index).serial_number);
619 log_event(l_api_name, 'prepare ', 'serial_resv_flag '||l_detail_level_tbl(l_index).serial_resv_flag);
620 log_event(l_api_name, 'prepare ', 'p_request_line_rec.line_id '||p_request_line_rec.line_id);
621 log_event(l_api_name, 'prepare ', 'p_request_context.type_code '||p_request_context.type_code);
622 log_event(l_api_name, 'prepare ', 'transaction_quantity '||l_detail_level_tbl(l_index).transaction_quantity);
623 log_event(l_api_name, 'prepare ', 'locator_id '||l_detail_level_tbl(l_index).locator_id);
624 log_event(l_api_name, 'prepare ', 'reservation_id '||l_detail_level_tbl(l_index).reservation_id);
625 END IF;
626
627 END LOOP;
628 -- [ setting the rule_use flag
629 IF l_detail_level_tbl_size = l_rsv_ctr THEN
630 g_use_rule := 'N' ;
631 ELSE
632 g_use_rule := 'Y' ;
633 END IF;
634 IF l_debug = 1 THEN
635 log_event(l_api_name, 'prepare ', 'l_rsv_ctr '|| l_rsv_ctr);
636 log_event(l_api_name, 'prepare ', 'g_use_rule '|| g_use_rule);
637 END IF;
638 -- ]
639
640 ELSE -- the request is for put away only
641 l_wms_txn_temp_tbl(1).from_organization_id := p_request_line_rec.organization_id;
642 l_wms_txn_temp_tbl(1).to_organization_id := p_request_line_rec.to_organization_id;
643 l_wms_txn_temp_tbl(1).from_subinventory_code := p_request_line_rec.from_subinventory_code;
644 l_wms_txn_temp_tbl(1).from_locator_id := p_request_line_rec.from_locator_id;
645 l_wms_txn_temp_tbl(1).to_subinventory_code := p_request_line_rec.to_subinventory_code;
646 l_wms_txn_temp_tbl(1).to_locator_id := p_request_line_rec.to_locator_id;
647 l_wms_txn_temp_tbl(1).from_cost_group_id := p_request_line_rec.from_cost_group_id;
648 l_wms_txn_temp_tbl(1).to_cost_group_id := p_request_line_rec.to_cost_group_id;
649 l_wms_txn_temp_tbl(1).transaction_temp_id := p_request_line_rec.line_id;
650 l_wms_txn_temp_tbl(1).type_code := p_request_context.type_code;
651 l_wms_txn_temp_tbl(1).line_type_code := 1; -- input
652 l_wms_txn_temp_tbl(1).primary_quantity := p_request_line_rec.primary_quantity;
653 l_wms_txn_temp_tbl(1).secondary_quantity := p_request_line_rec.secondary_quantity;
654 l_wms_txn_temp_tbl(1).grade_code := p_request_line_rec.grade_code;
655 l_wms_txn_temp_tbl(1).transaction_quantity := p_request_line_rec.quantity - NVL(p_request_line_rec.quantity_detailed, 0);
656 l_wms_txn_temp_tbl(1).revision := p_request_line_rec.revision;
657 l_wms_txn_temp_tbl(1).lot_number := p_request_line_rec.lot_number;
658 l_wms_txn_temp_tbl(1).lot_expiration_date := p_request_context.lot_expiration_date;
659 l_wms_txn_temp_tbl(1).lpn_id := p_request_line_rec.lpn_id;
660 -- Bug #2286454
661 l_allow_non_partial_rules := TRUE;
662 END IF;
663 --
664 -- now we can insert these temporary records derived from
665 -- above
666 insert_detail_temp_records(l_return_status, l_wms_txn_temp_tbl);
667 IF l_return_status = fnd_api.g_ret_sts_error THEN
668 RAISE fnd_api.g_exc_error;
669 END IF;
670 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
671 RAISE fnd_api.g_exc_unexpected_error;
672 END IF;
673 END IF;
674
675 IF l_debug = 1 THEN
676 log_procedure(l_api_name, 'end', 'End Prepare');
677 END IF;
678 --
679 EXCEPTION
680 WHEN fnd_api.g_exc_error THEN
681 /* --
682 -- debugging section
683 -- can be commented out for final code
684 IF inv_pp_debug.is_debug_mode THEN
685 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
686 -- the message retrieved here since it is no longer on the stack
687 inv_pp_debug.set_last_error_message(SQLERRM);
688 inv_pp_debug.send_message_to_pipe('exception in '|| l_api_name);
689 inv_pp_debug.send_last_error_message;
690 END IF;
691
692 -- end of debugging section
693 -- */
694 x_return_status := fnd_api.g_ret_sts_error;
695 IF l_debug = 1 THEN
696 log_error(l_api_name, 'error', 'Error in Prepare');
697 END IF;
698 --
699 WHEN fnd_api.g_exc_unexpected_error THEN
700 /* --
701 -- debugging section
702 -- can be commented out for final code
703 IF inv_pp_debug.is_debug_mode THEN
704 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
705 -- the message retrieved here since it is no longer on the stack
706 inv_pp_debug.set_last_error_message(SQLERRM);
707 inv_pp_debug.send_message_to_pipe('exception in '|| l_api_name);
708 inv_pp_debug.send_last_error_message;
709 END IF;
710
711 -- end of debugging section
712 -- */
713 x_return_status := fnd_api.g_ret_sts_unexp_error;
714 IF l_debug = 1 THEN
715 log_error(l_api_name, 'unexp_error', 'Unexpected error in Prepare');
716 END IF;
717 --
718 WHEN OTHERS THEN
719
720 x_return_status := fnd_api.g_ret_sts_unexp_error;
721
722 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
723 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
724 END IF;
725 IF l_debug = 1 THEN
726 log_error(l_api_name, 'other_error', 'Other error in Prepare');
727 END IF;
728 --
729 END prepare;
730
731 --
732 --
733 -- Procedure : Prepare_Transfer_Receipt
734 -- Pre-reqs : Record(s) exist(s) in WMS_TRANSACTIONS_TEMP with
735 -- p_transaction_temp_id = the move order line id
736 -- and type_code = 2 (pick) and
737 -- line_type_code = 2 (output line)
738 -- Function : Copies issue output record(s) into
739 -- WMS_TRANSACTIONS_TEMP as input records
740 -- for receipt portion within transfer transactions
741 --
742 PROCEDURE prepare_transfer_receipt
743 (
744 x_return_status IN OUT NOCOPY VARCHAR2
745 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
746 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
747 ) IS
748 l_api_name CONSTANT VARCHAR2(30) := 'prepare_transfer_receipt';
749 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
750
751 --
752 --changed by jcearley on 12/8/99 to attempt to order transfers
753 -- in order of pick suggestions
754
755 CURSOR l_put_input_csr IS
756 SELECT SUM(wtt.transaction_quantity) transaction_quantity
757 , SUM(wtt.primary_quantity) primary_quantity
758 , SUM(wtt.secondary_quantity) secondary_quantity
759 , wtt.grade_code grade_code
760 , wtt.revision revision
761 , wtt.lot_number lot_number
762 , wtt.from_subinventory_code from_subinventory_code
763 , wtt.from_locator_id from_locator_id
764 , wtt.from_cost_group_id from_cost_group_id
765 , wtt.lpn_id lpn_id
766 FROM wms_transactions_temp wtt
767 WHERE wtt.transaction_temp_id = p_request_line_rec.line_id
768 AND wtt.line_type_code = 2 -- output line
769 AND wtt.type_code = 2 -- pick
770 GROUP BY wtt.lot_number
771 , wtt.revision
772 , wtt.from_subinventory_code
773 , wtt.from_locator_id
774 , wtt.from_cost_group_id
775 , wtt.lpn_id
776 , wtt.grade_code
777 ORDER BY MIN(wtt.pp_transaction_temp_id);
778
779 --
780 l_put_input_rec l_put_input_csr%ROWTYPE;
781 --
782 l_txn_qty NUMBER;
783 l_wms_txn_temp_tbl g_wms_txn_temp_tbl_type;
784 l_wms_txn_temp_tbl_size NUMBER;
785 l_debug NUMBER;
786
787 -- Added for Bug 6063903
788 l_wms_installed BOOLEAN := TRUE;
789 x_api_return_status VARCHAR2(2);
790 x_msg_count NUMBER;
791 x_msg_data VARCHAR2(2000);
792 -- Added for Bug 6063903
793
794 BEGIN
795 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
796 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
797 END IF;
798 l_debug := g_debug;
799 IF l_debug = 1 THEN
800 log_procedure(l_api_name, 'start', 'Start prepare_transfer_receipt');
801 END IF;
802
803 --
804 -- Initialisize API return status to access
805 x_return_status := fnd_api.g_ret_sts_success;
806 --
807 l_wms_txn_temp_tbl_size := 0;
808 -- the fetching might be changed to use bulk
809 -- fetching if it is too slow
810 -- however, a disadvantage is that bulk fetching can
811 -- not use table of record. that will make the code
812 -- looks ugly
813 OPEN l_put_input_csr;
814
815 LOOP
816 FETCH l_put_input_csr INTO l_put_input_rec;
817 EXIT WHEN l_put_input_csr%NOTFOUND;
818 -- Note: serial number here is ignored here. The assumption
819 -- is that the put away side will not care about the serial number.
820 -- The purpose is to reduce the number of records as input in
821 -- wms_transactions_temp for put away since the more records
822 -- it is, the more runs the engine has to run, and thus the slower.
823 -- The assumption stated might not be valid for some situations.
824 -- So this might need to be enhanced later.
825 l_wms_txn_temp_tbl_size := l_wms_txn_temp_tbl_size + 1;
826 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).transaction_temp_id := p_request_line_rec.line_id;
827 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).type_code := 1;
828 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).line_type_code := 1;
829 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).transaction_quantity := l_put_input_rec.transaction_quantity;
830 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).primary_quantity := l_put_input_rec.primary_quantity;
831 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).secondary_quantity := l_put_input_rec.secondary_quantity;
832 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).grade_code := l_put_input_rec.grade_code;
833 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).revision := l_put_input_rec.revision;
834 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).lot_number := l_put_input_rec.lot_number;
835 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).lot_expiration_date := p_request_context.lot_expiration_date;
836 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).serial_number := NULL;
837 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_subinventory_code := l_put_input_rec.from_subinventory_code;
838 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_locator_id := l_put_input_rec.from_locator_id;
839 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_cost_group_id := l_put_input_rec.from_cost_group_id;
840 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_subinventory_code := p_request_line_rec.to_subinventory_code;
841 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_locator_id := p_request_line_rec.to_locator_id;
842 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).from_organization_id := p_request_line_rec.organization_id;
843 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_organization_id := p_request_line_rec.to_organization_id;
844 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).lpn_id := l_put_input_rec.lpn_id;
845
846 IF l_debug = 1 THEN
847 log_event(l_api_name, 'transfer and receipt input', 'transfer and receipt input');
848 log_event(l_api_name, 'transfer and receipt input', 'input qty1 '||
849 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).primary_quantity );
850 log_event(l_api_name, 'transfer and receipt input', 'input qty2 '||
851 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).secondary_quantity );
852 log_event(l_api_name, 'transfer and receipt input', 'input grade_code '||
853 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).secondary_quantity );
854 END IF;
855
856 -- Added for Bug 6063903
857 /*
858 we are setting the l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id as Null
859 as in inventory organization, cost group of Subinventory can be different from cost
860 group of desitnation subivnentory. Correct CG will get populated at a later stage.
861 */
862 l_wms_installed := WMS_INSTALL.check_install(
863 x_return_status => x_api_return_status,
864 x_msg_count => x_msg_count,
865 x_msg_data => x_msg_data,
866 p_organization_id => p_request_line_rec.organization_id);
867
868 --get to_cost_group id
869 -- if cost group on the move order line is not null, use it
870 -- if it is null, use the from_cost_group
871 IF (p_request_line_rec.to_cost_group_id IS NULL) THEN
872 IF not (l_wms_installed) THEN -- Added for Bug 6063903
873 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id := null; -- Added for Bug 6063903
874 ELSIF (l_wms_installed) THEN -- Added for Bug 6063903
875 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id := l_put_input_rec.from_cost_group_id;
876 END IF;
877 ELSE
878 l_wms_txn_temp_tbl(l_wms_txn_temp_tbl_size).to_cost_group_id := p_request_line_rec.to_cost_group_id;
879 END IF;
880 END LOOP;
881
882 CLOSE l_put_input_csr;
883 -- insert the records into the temporary table
884 -- as input to put away detailing
885 insert_detail_temp_records(l_return_status, l_wms_txn_temp_tbl);
886
887 IF l_return_status = fnd_api.g_ret_sts_error THEN
888 RAISE fnd_api.g_exc_error;
889 END IF;
890
891 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
892 RAISE fnd_api.g_exc_unexpected_error;
893 END IF;
894
895 x_return_status := l_return_status;
896
897 IF l_debug = 1 THEN
898 log_procedure(l_api_name, 'end', 'End prepare_transfer_receipt');
899 END IF;
900
901 EXCEPTION
902 WHEN fnd_api.g_exc_error THEN
903 IF l_put_input_csr%ISOPEN THEN
904 CLOSE l_put_input_csr;
905 END IF;
906
907 x_return_status := fnd_api.g_ret_sts_error;
908 IF l_debug = 1 THEN
909 log_error(l_api_name, 'error', 'Error in prepare_transfer_receipt');
910 END IF;
911 --
912 WHEN fnd_api.g_exc_unexpected_error THEN
913 IF l_put_input_csr%ISOPEN THEN
914 CLOSE l_put_input_csr;
915 END IF;
916
917 x_return_status := fnd_api.g_ret_sts_unexp_error;
918 IF l_debug = 1 THEN
919 log_error(l_api_name, 'unexp_error', 'Unexpected error in prepare_transfer_receipt');
920 END IF;
921 --
922 WHEN OTHERS THEN
923 IF l_put_input_csr%ISOPEN THEN
924 CLOSE l_put_input_csr;
925 END IF;
926
927 x_return_status := fnd_api.g_ret_sts_unexp_error;
928
929 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
930 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
931 END IF;
932 IF l_debug = 1 THEN
933 log_error(l_api_name, 'other_error', 'Other error in prepare_transfer_receipt');
934 END IF;
935 --
936 END prepare_transfer_receipt;
937
938 --
939 -- debugging routine
940 -- display the output records in wms_transactions_temp
941 -- when called
942 PROCEDURE display_temp_records IS
943 CURSOR l_cur IS
944 SELECT transaction_temp_id
945 , line_type_code
946 , type_code
947 , revision
948 , lot_number
949 , lot_expiration_date
950 , from_subinventory_code
951 , from_locator_id
952 , primary_quantity
953 , transaction_quantity
954 , secondary_quantity
955 , grade_code
956 , reservation_id
957 , to_subinventory_code
958 , to_locator_id
959 , lpn_id
960 FROM wms_transactions_temp
961 ORDER BY transaction_temp_id
962 , line_type_code
963 , type_code
964 , revision
965 , lot_number
966 , lot_expiration_date
967 , from_subinventory_code
968 , from_locator_id;
969
970 l_rec l_cur%ROWTYPE;
971 l_type VARCHAR2(20);
972 l_line_type VARCHAR2(20);
973 l_api_name VARCHAR2(30);
974 l_debug NUMBER;
975 BEGIN
976 /*IF inv_pp_debug.is_debug_mode = FALSE THEN
977 RETURN;
978 END IF; */
979
980 IF g_debug IS NULL THEN
981 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
982 END IF;
983 l_debug := g_debug;
984
985 IF l_debug = 1 THEN
986 log_error(l_api_name, 'display_temp_records', 'display_temp_records');
987 ELSE
988 RETURN;
989 END IF;
990
991
992 OPEN l_cur;
993 FETCH l_cur INTO l_rec;
994
995 WHILE l_cur%FOUND LOOP
996 --inv_pp_debug.send_message_to_pipe('move order line '|| l_rec.transaction_temp_id);
997
998 IF l_rec.line_type_code = 1 THEN
999 l_line_type := 'input line ';
1000 ELSE
1001 l_line_type := 'output line ';
1002 END IF;
1003
1004 IF l_rec.type_code = 1 THEN
1005 l_type := 'put away';
1006 ELSE
1007 l_type := 'pick';
1008 END IF;
1009
1010 /* inv_pp_debug.send_message_to_pipe('line_type_code '|| l_line_type);
1011 inv_pp_debug.send_message_to_pipe('type_code '|| l_type);
1012 inv_pp_debug.send_message_to_pipe('revision '|| l_rec.revision);
1013 inv_pp_debug.send_message_to_pipe('lot_number '|| l_rec.lot_number);
1014 inv_pp_debug.send_message_to_pipe('expiration date '|| l_rec.lot_expiration_date);
1015 inv_pp_debug.send_message_to_pipe('from subinventory'|| l_rec.from_subinventory_code);
1016 inv_pp_debug.send_message_to_pipe('from locator id '|| l_rec.from_locator_id);
1017 inv_pp_debug.send_message_to_pipe('to subinventory '|| l_rec.to_subinventory_code);
1018 inv_pp_debug.send_message_to_pipe('to locator id '|| l_rec.to_locator_id);
1019 inv_pp_debug.send_message_to_pipe('primary quantity '|| l_rec.primary_quantity);
1020 inv_pp_debug.send_message_to_pipe('transaction qty '|| l_rec.transaction_quantity);
1021 inv_pp_debug.send_message_to_pipe('reservation_id '|| l_rec.reservation_id);
1022 inv_pp_debug.send_message_to_pipe('lpn_id '|| l_rec.lpn_id);
1023
1024
1025 gmi_reservation_util.println('type_code '|| l_type);
1026 gmi_reservation_util.println('revision '|| l_rec.revision);
1027 gmi_reservation_util.println('lot_number '|| l_rec.lot_number);
1028 gmi_reservation_util.println('expiration date '|| l_rec.lot_expiration_date);
1029 gmi_reservation_util.println('from subinventory'|| l_rec.from_subinventory_code);
1030 gmi_reservation_util.println('from locator id '|| l_rec.from_locator_id);
1031 gmi_reservation_util.println('to subinventory '|| l_rec.to_subinventory_code);
1032 gmi_reservation_util.println('to locator id '|| l_rec.to_locator_id);
1033 gmi_reservation_util.println('primary quantity '|| l_rec.primary_quantity);
1034 gmi_reservation_util.println('secondary quantity '|| l_rec.secondary_quantity);
1035 gmi_reservation_util.println('transaction qty '|| l_rec.transaction_quantity);
1036 gmi_reservation_util.println('grade code '|| l_rec.grade_code);
1037 gmi_reservation_util.println('reservation_id '|| l_rec.reservation_id);
1038 gmi_reservation_util.println('lpn_id '|| l_rec.lpn_id); */
1039
1040
1041 log_statement(l_api_name,'type_code ', l_type);
1042 log_statement(l_api_name,'revision ', l_rec.revision);
1043 log_statement(l_api_name,'lot_number ', l_rec.lot_number);
1044 log_statement(l_api_name,'expiration date ', l_rec.lot_expiration_date );
1045 log_statement(l_api_name,'from subinventory ', l_rec.from_subinventory_code);
1046 log_statement(l_api_name,'from locator id ', l_rec.from_locator_id);
1047 log_statement(l_api_name,'To subinventory ', l_rec.to_subinventory_code);
1048 log_statement(l_api_name,'To locator id ', l_rec.to_locator_id);
1049 log_statement(l_api_name,'primary quantity ', l_rec.primary_quantity);
1050 log_statement(l_api_name,'secondary quantity ', l_rec.secondary_quantity);
1051 log_statement(l_api_name,'transaction qty ', l_rec.transaction_quantity);
1052 log_statement(l_api_name,'grade code ', l_rec.grade_code);
1053 log_statement(l_api_name,'reservation_id ', l_rec.reservation_id);
1054 log_statement(l_api_name,'lpn_id ', l_rec.lpn_id);
1055
1056 FETCH l_cur INTO l_rec;
1057 END LOOP;
1058
1059 CLOSE l_cur;
1060
1061 END display_temp_records;
1062
1063 --
1064 -- create output suggestion records for issue or receipt but not transfer
1065 -- read from the table wms_transactions_temp by the order of
1066 -- revision, from_sub, to_sub, from_loc, to_loc, lot_number, serial_number
1067 PROCEDURE output_issue_or_receipt(
1068 x_return_status OUT NOCOPY VARCHAR2
1069 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
1070 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
1071 , p_plan_tasks IN BOOLEAN ---DEFAULT FALSE
1072 ) IS
1073 l_transaction_temp_id NUMBER;
1074
1075 -- Cursor for receipts or issues
1076 --changed by jcearley on 12/8/99
1077 --added order by clause so suggestions are entered into
1078 --mmtt in order in which the engine found them
1079 CURSOR l_pp_temp_csr IS
1080 SELECT x.revision
1081 , x.from_subinventory_code
1082 , x.from_locator_id
1083 , x.to_subinventory_code
1084 , x.to_locator_id
1085 , x.lot_number
1086 , MAX(x.lot_expiration_date) lot_expiration_date
1087 , x.serial_number serial_number_start
1088 , x.serial_number serial_number_end
1089 , SUM(x.transaction_quantity) transaction_quantity
1090 , SUM(x.primary_quantity) primary_quantity
1091 , SUM(x.secondary_quantity) secondary_quantity
1092 , grade_code
1093 , MIN(x.pick_rule_id) pick_rule_id
1094 , MIN(x.put_away_rule_id) put_away_rule_id
1095 , x.reservation_id reservation_id
1096 , x.from_cost_group_id
1097 , x.to_cost_group_id
1098 , x.lpn_id
1099 FROM (SELECT wtt.revision
1100 , wtt.from_subinventory_code
1101 , wtt.from_locator_id
1102 , wtt.to_subinventory_code
1103 , wtt.to_locator_id
1104 , wtt.lot_number
1105 , wtt.lot_expiration_date
1106 , wtt.serial_number
1107 , wtt.transaction_quantity
1108 , wtt.primary_quantity
1109 , wtt.secondary_quantity
1110 , wtt.grade_code
1111 , DECODE(wtt.type_code, 2, wtt.rule_id, NULL) pick_rule_id
1112 , DECODE(wtt.type_code, 1, wtt.rule_id, NULL) put_away_rule_id
1113 , DECODE(wtt.type_code, 2, wtt.reservation_id, NULL) reservation_id
1114 , wtt.pp_transaction_temp_id
1115 , wtt.from_cost_group_id
1116 , wtt.to_cost_group_id
1117 , wtt.lpn_id
1118 FROM wms_transactions_temp wtt
1119 WHERE wtt.transaction_temp_id = l_transaction_temp_id
1120 AND wtt.line_type_code = 2 -- output line
1121 ) x
1122 GROUP BY x.revision
1123 , x.from_subinventory_code
1124 , x.to_subinventory_code
1125 , x.from_locator_id
1126 , x.to_locator_id
1127 , x.from_cost_group_id
1128 , x.to_cost_group_id
1129 , x.lot_number
1130 , x.serial_number
1131 , x.reservation_id
1132 , x.lpn_id
1133 , x.grade_code
1134 ORDER BY MIN(x.pp_transaction_temp_id);
1135
1136 --
1137 l_api_name CONSTANT VARCHAR2(30) := 'output_issue_or_receipt';
1138 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1139 l_curr_rec inv_detail_util_pvt.g_output_process_rec_type;
1140 l_debug NUMBER;
1141 --
1142 BEGIN
1143 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
1144 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1145 END IF;
1146 l_debug := g_debug;
1147 IF l_debug = 1 THEN
1148 log_procedure(l_api_name, 'start', 'Start output_issue_or_receipt');
1149 END IF;
1150
1151 --
1152 -- Initialisize API return status to access
1153 x_return_status := fnd_api.g_ret_sts_success;
1154 l_transaction_temp_id := p_request_line_rec.line_id;
1155 -- fetch the input request line into a record
1156 OPEN l_pp_temp_csr;
1157
1158 LOOP
1159 FETCH l_pp_temp_csr INTO l_curr_rec;
1160
1161 IF l_pp_temp_csr%NOTFOUND THEN
1162 EXIT;
1163 END IF;
1164 If l_debug = 1 THEN
1165 log_event(l_api_name, 'add output', 'add output');
1166 END IF;
1167 inv_detail_util_pvt.add_output(l_curr_rec);
1168 END LOOP;
1169
1170 CLOSE l_pp_temp_csr;
1171 IF l_debug = 1 THEN
1172 log_event(l_api_name, 'process output', 'process output');
1173 END IF;
1174 inv_detail_util_pvt.process_output(l_return_status
1175 , p_request_line_rec
1176 , p_request_context
1177 , p_plan_tasks);
1178
1179 IF l_return_status = fnd_api.g_ret_sts_error THEN
1180 RAISE fnd_api.g_exc_error;
1181 END IF;
1182
1183 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1184 RAISE fnd_api.g_exc_unexpected_error;
1185 END IF;
1186
1187 x_return_status := l_return_status;
1188
1189 IF l_debug = 1 THEN
1190 log_procedure(l_api_name, 'end', 'End output_issue_or_receipt');
1191 END IF;
1192
1193 EXCEPTION
1194 WHEN fnd_api.g_exc_error THEN
1195 IF l_pp_temp_csr%ISOPEN THEN
1196 CLOSE l_pp_temp_csr;
1197 END IF;
1198
1199 x_return_status := fnd_api.g_ret_sts_error;
1200 IF l_debug = 1 THEN
1201 log_error(l_api_name, 'error', 'Error in output_issue_or_receipt');
1202 END IF;
1203
1204 --
1205 WHEN fnd_api.g_exc_unexpected_error THEN
1206 IF l_pp_temp_csr%ISOPEN THEN
1207 CLOSE l_pp_temp_csr;
1208 END IF;
1209
1210 x_return_status := fnd_api.g_ret_sts_unexp_error;
1211 IF l_debug = 1 THEN
1212 log_error(l_api_name, 'unexp_error', 'Unexpected error in output_issue_or_receipt');
1213 END IF;
1214 --
1215 WHEN OTHERS THEN
1216 IF l_pp_temp_csr%ISOPEN THEN
1217 CLOSE l_pp_temp_csr;
1218 END IF;
1219
1220 x_return_status := fnd_api.g_ret_sts_unexp_error;
1221
1222 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1223 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1224 END IF;
1225 IF l_debug = 1 THEN
1226 log_error(l_api_name, 'other_error', 'Other error in output_issue_or_receipt');
1227 END IF;
1228 --
1229 END output_issue_or_receipt;
1230
1231 --
1232 -- create output suggestion records for transfer
1233 PROCEDURE combine_transfer(
1234 x_return_status OUT NOCOPY VARCHAR2
1235 , p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type
1236 , p_request_context IN inv_detail_util_pvt.g_request_context_rec_type
1237 , p_plan_tasks IN BOOLEAN ----DEFAULT FALSE
1238 ) IS
1239 l_api_name VARCHAR2(30) := 'combine_transfer';
1240 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1241 l_transaction_temp_id NUMBER;
1242
1243 --changed by jcearley on 12/8/99 to order transfer order in mmtt
1244 -- in the order in which suggestions were created
1245 CURSOR l_issue_csr IS
1246 SELECT revision
1247 , from_subinventory_code
1248 , from_locator_id
1249 , from_cost_group_id
1250 , to_subinventory_code
1251 , to_locator_id
1252 , to_cost_group_id
1253 , lot_number
1254 , MAX(lot_expiration_date) lot_expiration_date
1255 , serial_number
1256 , SUM(transaction_quantity) transaction_quantity
1257 , SUM(primary_quantity) primary_quantity
1258 , SUM(secondary_quantity) secondary_quantity
1259 , grade_code
1260 , MIN(rule_id) pick_rule_id
1261 , reservation_id
1262 , lpn_id
1263 FROM wms_transactions_temp
1264 WHERE transaction_temp_id = l_transaction_temp_id
1265 AND line_type_code = 2 -- output line
1266 AND type_code = 2 -- pick
1267 GROUP BY serial_number
1268 , lot_number
1269 , revision
1270 , from_subinventory_code
1271 , from_locator_id
1272 , from_cost_group_id
1273 , reservation_id
1274 , to_subinventory_code
1275 , to_locator_id
1276 , to_cost_group_id
1277 , lpn_id
1278 , grade_code
1279 ORDER BY revision
1280 , from_subinventory_code
1281 , from_locator_id
1282 , from_cost_group_id
1283 , lpn_id
1284 , lot_number
1285 , reservation_id --15955427
1286 , serial_number
1287 ;
1288 --bug 2828119 - order by sub and locator to prevent multiple picking
1289 --picking tasks from the same locator
1290 --ordER BY MIN(pp_transaction_temp_id);
1291
1292 --
1293 CURSOR l_receipt_csr IS
1294 SELECT revision
1295 , from_subinventory_code
1296 , from_locator_id
1297 , from_cost_group_id
1298 , to_subinventory_code
1299 , to_locator_id
1300 , to_cost_group_id
1301 , lot_number
1302 , MAX(lot_expiration_date) lot_expiration_date
1303 , serial_number
1304 , SUM(transaction_quantity) transaction_quantity
1305 , SUM(primary_quantity) primary_quantity
1306 , SUM(secondary_quantity) secondary_quantity
1307 , grade_code
1308 , MIN(rule_id) put_away_rule_id
1309 , NULL reservation_id
1310 , lpn_id lpn_id
1311 FROM wms_transactions_temp
1312 WHERE transaction_temp_id = l_transaction_temp_id
1313 AND line_type_code = 2 -- output line
1314 AND type_code = 1 -- put away
1315 GROUP BY serial_number
1316 , lot_number
1317 , revision
1318 , from_subinventory_code
1319 , from_locator_id
1320 , from_cost_group_id
1321 , to_subinventory_code
1322 , to_locator_id
1323 , to_cost_group_id
1324 , lpn_id
1325 , grade_code
1326 ORDER BY revision
1327 , from_subinventory_code
1328 , from_locator_id
1329 , from_cost_group_id
1330 , lpn_id
1331 , lot_number
1332 , reservation_id --15955427
1333 , serial_number
1334 ;
1335 --bug 2828119 - order by sub and locator to prevent multiple picking
1336 --picking tasks from the same locator
1337 --ordER BY MIN(pp_transaction_temp_id);
1338 --
1339 l_curr_issue_rec g_combine_rec_type;
1340 l_curr_receipt_rec g_combine_rec_type;
1341 l_issue_tbl g_combine_tbl_type;
1342 l_receipt_tbl g_combine_tbl_type;
1343 l_issue_tbl_size INTEGER;
1344 l_receipt_tbl_size INTEGER;
1345 l_curr_issue_idx INTEGER;
1346 l_curr_receipt_idx INTEGER;
1347 l_output_process_rec inv_detail_util_pvt.g_output_process_rec_type;
1348 l_xfer_qty NUMBER;
1349 l_txn_xfer_qty NUMBER;
1350 l_sec_xfer_qty NUMBER;
1351 l_grade_code VARCHAR2(150);
1352 l_debug NUMBER;
1353 BEGIN
1354 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
1355 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1356 END IF;
1357 l_debug := g_debug;
1358 IF l_debug = 1 THEN
1359 log_procedure(l_api_name, 'start', 'Start combine_transfer');
1360 END IF;
1361
1362 x_return_status := l_return_status;
1363 -- fetch all issue and receipt records into memory
1364 l_transaction_temp_id := p_request_line_rec.line_id;
1365 OPEN l_issue_csr;
1366 l_issue_tbl_size := 0;
1367
1368 LOOP
1369 FETCH l_issue_csr INTO l_curr_issue_rec;
1370 EXIT WHEN l_issue_csr%NOTFOUND;
1371 l_issue_tbl_size := l_issue_tbl_size + 1;
1372 l_issue_tbl(l_issue_tbl_size) := l_curr_issue_rec;
1373 --inv_pp_debug.send_message_to_pipe('issue sub '|| l_curr_issue_rec.from_subinventory_code);
1374 END LOOP;
1375
1376 CLOSE l_issue_csr;
1377 l_receipt_tbl_size := 0;
1378 OPEN l_receipt_csr;
1379
1380 LOOP
1381 FETCH l_receipt_csr INTO l_curr_receipt_rec;
1382 EXIT WHEN l_receipt_csr%NOTFOUND;
1383 l_receipt_tbl_size := l_receipt_tbl_size + 1;
1384 l_receipt_tbl(l_receipt_tbl_size) := l_curr_receipt_rec;
1385 --inv_pp_debug.send_message_to_pipe('receipt sub '|| l_curr_receipt_rec.to_subinventory_code);
1386 END LOOP;
1387
1388 CLOSE l_receipt_csr;
1389 --inv_pp_debug.send_message_to_pipe('receipt table size '|| l_receipt_tbl_size);
1390 --inv_pp_debug.send_message_to_pipe('issue table size '|| l_issue_tbl_size);
1391 --
1392 -- combine the issue and receipt records into a transfer record
1393 -- initialize the variables
1394 l_xfer_qty := 0;
1395 l_txn_xfer_qty := 0;
1396
1397 -- get the first issue suggestion
1398 IF l_issue_tbl_size < 1 THEN
1399 -- no issue could be recommended by the system -> exit
1400 RETURN;
1401 END IF;
1402
1403 l_curr_issue_idx := 1;
1404
1405 --
1406 -- get the first receipt suggestion
1407 IF l_receipt_tbl_size < 1 THEN
1408 -- no receipt could be recommended by the system -> exit
1409 RETURN;
1410 END IF;
1411
1412 l_curr_receipt_idx := 1;
1413
1414 --
1415 WHILE l_curr_issue_idx <= l_issue_tbl_size
1416 AND l_curr_receipt_idx <= l_receipt_tbl_size LOOP
1417 -- If the current issue record and receipt record
1418 -- do not have the same revision, and lot number,
1419 -- the engine has not found a put away suggestion for the given
1420 -- issue suggestion.
1421 -- Then we will try the next issue record
1422 IF NOT (l_issue_tbl(l_curr_issue_idx).revision = l_receipt_tbl(l_curr_receipt_idx).revision
1423 OR l_issue_tbl(l_curr_issue_idx).revision IS NULL
1424 AND l_receipt_tbl(l_curr_receipt_idx).revision IS NULL
1425 )
1426 OR NOT (l_issue_tbl(l_curr_issue_idx).lot_number = l_receipt_tbl(l_curr_receipt_idx).lot_number
1427 OR l_issue_tbl(l_curr_issue_idx).lot_number IS NULL
1428 AND l_receipt_tbl(l_curr_receipt_idx).lot_number IS NULL
1429 )
1430 OR NOT (l_issue_tbl(l_curr_issue_idx).from_subinventory_code = l_receipt_tbl(l_curr_receipt_idx).from_subinventory_code)
1431 OR NOT (l_issue_tbl(l_curr_issue_idx).from_locator_id = l_receipt_tbl(l_curr_receipt_idx).from_locator_id)
1432 OR NOT (l_issue_tbl(l_curr_issue_idx).from_cost_group_id = l_receipt_tbl(l_curr_receipt_idx).from_cost_group_id)
1433 OR NOT (l_issue_tbl(l_curr_issue_idx).lpn_id = l_receipt_tbl(l_curr_receipt_idx).lpn_id)
1434 THEN -- the follwing is commented out because
1435 -- we do not copy the serial number from the picking output
1436 -- to put away input in prepare_transfer_receipt procedure
1437 -- for efficiency reason. so the output from put away
1438 -- suggestion does not have serial number at all. orignially
1439 -- it does.
1440 --
1441 -- OR NOT (l_issue_tbl(l_curr_issue_idx).serial_number
1442 -- = l_receipt_tbl(l_curr_receipt_idx).serial_number
1443 -- OR l_issue_tbl(l_curr_issue_idx).serial_number IS NULL
1444 -- AND l_receipt_tbl(l_curr_receipt_idx).serial_number IS NULL
1445 -- )
1446
1447 IF l_debug = 1 THEN
1448 log_event(l_api_name, 'combine_failed', 'Unable to match ' || 'issue record with receipt record. Trying next ' || 'issue record');
1449 END IF;
1450 l_curr_issue_idx := l_curr_issue_idx + 1;
1451 -- pardon for the 'goto' but we haven't been able to solve it
1452 -- another way
1453 GOTO CONTINUE;
1454 END IF;
1455
1456 --
1457 -- compute the actual transfer qty ( minimum of issue and receipt )
1458 IF l_issue_tbl(l_curr_issue_idx).primary_quantity > l_receipt_tbl(l_curr_receipt_idx).primary_quantity THEN
1459 l_xfer_qty := l_receipt_tbl(l_curr_receipt_idx).primary_quantity;
1460 l_sec_xfer_qty := l_receipt_tbl(l_curr_receipt_idx).secondary_quantity;
1461 l_issue_tbl(l_curr_issue_idx).primary_quantity := l_issue_tbl(l_curr_issue_idx).primary_quantity - l_xfer_qty;
1462 l_issue_tbl(l_curr_issue_idx).secondary_quantity := l_issue_tbl(l_curr_issue_idx).secondary_quantity - l_sec_xfer_qty;
1463 l_receipt_tbl(l_curr_receipt_idx).primary_quantity := 0;
1464 l_receipt_tbl(l_curr_receipt_idx).secondary_quantity := 0;
1465 ELSE
1466 l_xfer_qty := l_issue_tbl(l_curr_issue_idx).primary_quantity;
1467 l_sec_xfer_qty := l_issue_tbl(l_curr_issue_idx).secondary_quantity;
1468 l_receipt_tbl(l_curr_receipt_idx).primary_quantity := l_receipt_tbl(l_curr_receipt_idx).primary_quantity - l_xfer_qty;
1469 l_receipt_tbl(l_curr_receipt_idx).secondary_quantity := l_receipt_tbl(l_curr_receipt_idx).secondary_quantity - l_sec_xfer_qty;
1470 l_issue_tbl(l_curr_issue_idx).primary_quantity := 0;
1471 l_issue_tbl(l_curr_issue_idx).secondary_quantity := 0;
1472 END IF;
1473
1474 -- Added the following code to remove the dependencies between WMSVPPEB.pls and INVVDEUB.pls for 1159
1475
1476 IF l_issue_tbl(l_curr_issue_idx).transaction_quantity > l_receipt_tbl(l_curr_receipt_idx).transaction_quantity THEN
1477 l_txn_xfer_qty := l_receipt_tbl(l_curr_receipt_idx).transaction_quantity;
1478 l_issue_tbl(l_curr_issue_idx).transaction_quantity := l_issue_tbl(l_curr_issue_idx).transaction_quantity - l_txn_xfer_qty;
1479 l_receipt_tbl(l_curr_receipt_idx).transaction_quantity := 0;
1480 ELSE
1481 l_txn_xfer_qty := l_issue_tbl(l_curr_issue_idx).transaction_quantity;
1482 l_receipt_tbl(l_curr_receipt_idx).transaction_quantity := l_receipt_tbl(l_curr_receipt_idx).transaction_quantity - l_txn_xfer_qty;
1483 l_issue_tbl(l_curr_issue_idx).transaction_quantity := 0;
1484 END IF;
1485
1486 l_output_process_rec.revision := l_issue_tbl(l_curr_issue_idx).revision;
1487 l_output_process_rec.from_subinventory_code := l_issue_tbl(l_curr_issue_idx).from_subinventory_code;
1488 l_output_process_rec.from_locator_id := l_issue_tbl(l_curr_issue_idx).from_locator_id;
1489 l_output_process_rec.from_cost_group_id := l_issue_tbl(l_curr_issue_idx).from_cost_group_id;
1490 l_output_process_rec.to_subinventory_code := l_receipt_tbl(l_curr_receipt_idx).to_subinventory_code;
1491 l_output_process_rec.to_locator_id := l_receipt_tbl(l_curr_receipt_idx).to_locator_id;
1492 l_output_process_rec.to_cost_group_id := l_receipt_tbl(l_curr_receipt_idx).to_cost_group_id;
1493 l_output_process_rec.lot_number := l_issue_tbl(l_curr_issue_idx).lot_number;
1494 l_output_process_rec.lot_expiration_date := l_issue_tbl(l_curr_issue_idx).lot_expiration_date;
1495 l_output_process_rec.serial_number_start := l_issue_tbl(l_curr_issue_idx).serial_number;
1496 l_output_process_rec.serial_number_end := l_issue_tbl(l_curr_issue_idx).serial_number;
1497 l_output_process_rec.primary_quantity := l_xfer_qty;
1498 l_output_process_rec.transaction_quantity := l_txn_xfer_qty;
1499 l_output_process_rec.secondary_quantity := l_sec_xfer_qty;
1500 l_output_process_rec.grade_code := l_issue_tbl(l_curr_issue_idx).grade_code;
1501 l_output_process_rec.pick_rule_id := l_issue_tbl(l_curr_issue_idx).rule_id;
1502 l_output_process_rec.put_away_rule_id := l_receipt_tbl(l_curr_receipt_idx).rule_id;
1503 l_output_process_rec.reservation_id := l_issue_tbl(l_curr_issue_idx).reservation_id;
1504 l_output_process_rec.lpn_id := l_issue_tbl(l_curr_issue_idx).lpn_id;
1505 inv_detail_util_pvt.add_output(l_output_process_rec);
1506
1507 --
1508 -- get next issue suggestion if suggested issue qty is used up
1509 IF l_issue_tbl(l_curr_issue_idx).primary_quantity = 0 THEN
1510 l_curr_issue_idx := l_curr_issue_idx + 1;
1511 END IF;
1512
1513 -- get next receipt suggestion if suggested receipt qty is used up
1514 IF l_receipt_tbl(l_curr_receipt_idx).primary_quantity = 0 THEN
1515 l_curr_receipt_idx := l_curr_receipt_idx + 1;
1516 END IF;
1517
1518 <<continue>>
1519 NULL;
1520 END LOOP;
1521
1522 inv_detail_util_pvt.process_output(l_return_status
1523 , p_request_line_rec
1524 , p_request_context
1525 , p_plan_tasks);
1526
1527 IF l_return_status = fnd_api.g_ret_sts_error THEN
1528 RAISE fnd_api.g_exc_error;
1529 END IF;
1530
1531 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1532 RAISE fnd_api.g_exc_unexpected_error;
1533 END IF;
1534
1535 x_return_status := l_return_status;
1536
1537 IF l_debug = 1 THEN
1538 log_procedure(l_api_name, 'end', 'End combine_transfer');
1539 END IF;
1540 --
1541 EXCEPTION
1542 WHEN fnd_api.g_exc_error THEN
1543 IF l_issue_csr%ISOPEN THEN
1544 CLOSE l_issue_csr;
1545 END IF;
1546
1547 IF l_receipt_csr%ISOPEN THEN
1548 CLOSE l_receipt_csr;
1549 END IF;
1550
1551 x_return_status := fnd_api.g_ret_sts_error;
1552 IF l_debug = 1 THEN
1553 log_error(l_api_name, 'error', 'Error in combine_transfer');
1554 END IF;
1555 --
1556 WHEN fnd_api.g_exc_unexpected_error THEN
1557 IF l_issue_csr%ISOPEN THEN
1558 CLOSE l_issue_csr;
1559 END IF;
1560
1561 IF l_receipt_csr%ISOPEN THEN
1562 CLOSE l_receipt_csr;
1563 END IF;
1564
1565 x_return_status := fnd_api.g_ret_sts_unexp_error;
1566 IF l_debug = 1 THEN
1567 log_error(l_api_name, 'unexp_error', 'Unexpected error in combine_transfer');
1568 END IF;
1569 --
1570 WHEN OTHERS THEN
1571 IF l_issue_csr%ISOPEN THEN
1572 CLOSE l_issue_csr;
1573 END IF;
1574
1575 IF l_receipt_csr%ISOPEN THEN
1576 CLOSE l_receipt_csr;
1577 END IF;
1578
1579 x_return_status := fnd_api.g_ret_sts_unexp_error;
1580
1581 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1582 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1583 END IF;
1584 IF l_debug = 1 THEN
1585 log_error(l_api_name, 'other_error', 'Other error in combine_transfer');
1586 END IF;
1587 --
1588 END combine_transfer;
1589
1590 -- API name : Create_Suggestions
1591 -- Type : Private
1592 -- Function : Creates pick and/or put away suggestions
1593 -- The program will use WMS pick/put rules/strategies
1594 -- if Oracle WMS is installed; otherwise, rules in
1595 -- mtl_picking_rules will be used.
1596 --
1597 -- Notes
1598 -- 1. Integration with reservations
1599 -- If table p_reservations passed by the calling is not empty, the
1600 -- engine will detailing based on a combination of the info in the
1601 -- move order line (the record that represents detailing request),
1602 -- and the info in p_reservations. For example, a sales order line
1603 -- can have two reservations, one for revision A in quantity of 10,
1604 -- and one for revision B in quantity of 5, and the line quantity
1605 -- can be 15; so when the pick release api calls the engine
1606 -- p_reservations will have two records of the reservations. So
1607 -- if the move order line based on the sales order line does not
1608 -- specify a revision, the engine will merge the information from
1609 -- move order line and p_reservations to create the input for
1610 -- detailing as two records, one for revision A, and one for revision
1611 -- B. Please see documentation for the pick release API for more
1612 -- details.
1613 --
1614 -- 2. Serial Number Detailing in Picking
1615 -- Currently the serial number detailing is quite simple. If the caller
1616 -- gives a range (start, and end) serial numbers in the move order line
1617 -- and pass p_suggest_serial as fnd_api.true, the engine will filter
1618 -- the locations found from a rule, and suggest unused serial numbers
1619 -- in the locator. If p_suggest_serial is passed as fnd_api.g_false
1620 -- (default), the engine will not give serial numbers in the output.
1621 --
1622 -- Input Parameters
1623 -- p_api_version_number standard input parameter
1624 -- p_init_msg_lst standard input parameter
1625 -- p_commit standard input parameter
1626 -- p_validation_level standard input parameter
1627 -- p_transaction_temp_id equals to the move order line id
1628 -- for the detailing request
1629 -- p_reservations reservations for the demand source
1630 -- as the transaction source
1631 -- in the move order line.
1632 -- p_suggest_serial whether or not the engine should suggest
1633 -- serial numbers in the detailing
1634 --
1635 -- Output Parameters
1636 -- x_return_status standard output parameters
1637 -- x_msg_count standard output parameters
1638 -- x_msg_data standard output parameters
1639 -- l_allow_non_partial_rules Set the value to false if
1640 -- l_remaining_quantity returned by compute_pick_detail
1641 -- is greater than 0
1642
1643 -- Version : Current version 1.0
1644 --
1645
1646 PROCEDURE create_suggestions(
1647 p_api_version IN NUMBER
1648 , p_init_msg_list IN VARCHAR2
1649 , p_commit IN VARCHAR2
1650 , p_validation_level IN NUMBER
1651 , x_return_status OUT NOCOPY VARCHAR2
1652 , x_msg_count OUT NOCOPY NUMBER
1653 , x_msg_data OUT NOCOPY VARCHAR2
1654 , p_transaction_temp_id IN NUMBER
1655 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
1656 , p_suggest_serial IN VARCHAR2
1657 , p_simulation_mode IN NUMBER
1658 , p_simulation_id IN NUMBER
1659 , p_plan_tasks IN BOOLEAN
1660 , p_quick_pick_flag IN VARCHAR2
1661 , p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
1662 ) IS
1663 l_api_version CONSTANT NUMBER := 1.0;
1664 l_api_name CONSTANT VARCHAR2(30) := 'Create_Suggestions';
1665 l_strategy_id NUMBER;
1666 l_rule_id NUMBER; -- [l_rule_id New Column Added for K ]
1667 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1668 l_msg_count NUMBER;
1669 l_msg_data VARCHAR2(2000);
1670 l_counter PLS_INTEGER; --8809951 changed to Pls_Integer
1671 l_type_code PLS_INTEGER; --8809951 changed to Pls_Integer
1672 l_number NUMBER;
1673 l_request_context inv_detail_util_pvt.g_request_context_rec_type;
1674 l_request_line_rec inv_detail_util_pvt.g_request_line_rec_type;
1675 l_move_order_type PLS_INTEGER; --8809951 changed to Pls_Integer
1676 l_loc_control_from PLS_INTEGER; --8809951 changed to Pls_Integer
1677 l_loc_control_to PLS_INTEGER; --8809951 changed to Pls_Integer
1678 l_item_control_from PLS_INTEGER; --8809951 changed to Pls_Integer
1679 l_item_control_to PLS_INTEGER; --8809951 changed to Pls_Integer
1680 l_simulation_mode PLS_INTEGER; --8809951 changed to Pls_Integer
1681 l_revert_capacity BOOLEAN;
1682 l_project_id NUMBER;
1683 l_task_id NUMBER;
1684 l_allow_cross_proj_issues VARCHAR2(1);
1685 l_allow_cross_unitnum_issues VARCHAR2(1);
1686 l_unit_number VARCHAR2(30);
1687 l_allow_non_partial_rules BOOLEAN; --- DEFAULT TRUE;
1688 l_return_type VARCHAR2(1);
1689 l_return_type_id NUMBER;
1690 l_sequence_number NUMBER;
1691 l_rules_engine_mode NUMBER := 1; --:= NVL(fnd_profile.VALUE('WMS_RULES_ENGINE_MODE'), 0);
1692 l_wip_rsv_exists PLS_INTEGER; --8809951 changed to Pls_Integer
1693
1694 --- Rules J Project Variables
1695 ---
1696 l_lpn_context PLS_INTEGER := 0; --8809951 changed to Pls_Integer
1697 l_current_release_level NUMBER := WMS_UI_TASKS_APIS.G_WMS_PATCH_LEVEL;
1698 l_j_release_level NUMBER := WMS_UI_TASKS_APIS.G_PATCHSET_J;
1699 l_quick_pick_flag VARCHAR2(1); -- 'J Project:This variable is used for QuickPick during Inventory Move
1700 -- Values 'Y' - Perform Quick Pick ,
1701 -- 'N' - Do not call quick Pick
1702 -- 'Q' - Perform Quick pick for Version 11.5.9 without qtr_tee creation
1703 --- Switching to New Strategy Search method
1704 --8809951 changed to Pls_Integer
1705 l_org_loc_control PLS_INTEGER; -- Bug#3051649
1706 l_debug PLS_INTEGER; -- 1 for debug is on , 0 for debug is off
1707 l_progress VARCHAR2(10); -- local variable to track program progress,
1708 -- especially useful when exception occurs
1709
1710 -- Added to skip rules processing if pick release process and locator provided bug3237702
1711 l_locator_id NUMBER;
1712 is_pickrelease BOOLEAN;
1713
1714
1715 l_allow_nr_sub_xfer VARCHAR2(1) := 'N' ; -- Bug #4006426
1716
1717 -- LG convergence add
1718 l_pp_transaction_temp_id NUMBER;
1719 -- end LG convergence add
1720 l_wms_installed BOOLEAN := TRUE; --added for bug 8292754
1721 x_api_return_status VARCHAR2(2); --added for bug 8292754
1722 l_return_val BOOLEAN; --added for bug 9210454
1723
1724 l_sec_qty_round_mode VARCHAR2(1);
1725 l_target_uom VARCHAR2(1);
1726 l_mtrl_pri_qty NUMBER; -- 12942776
1727
1728 l_wip_entity_type NUMBER; --BUG14517947
1729 l_sec_uom_class VARCHAR2(10);
1730 l_txn_uom_class VARCHAR2(10);
1731
1732 ---
1733 --the following cursors get information used in wms_rule_pvt.apply to
1734 --compare src sub/loc and dest sub/loc. The information is queried here
1735 --and stored in global variables to prevent multiple queries for the
1736 --same info (which would happen if we queried for data in apply)
1737
1738 CURSOR c_move_order_type IS
1739 SELECT move_order_type
1740 FROM mtl_txn_request_headers
1741 WHERE header_id = l_request_line_rec.header_id;
1742
1743 CURSOR c_sub_loc_control_from IS
1744 SELECT locator_type
1745 FROM mtl_secondary_inventories
1746 WHERE secondary_inventory_name = l_request_line_rec.from_subinventory_code
1747 AND organization_id = l_request_line_rec.organization_id;
1748
1749 CURSOR c_sub_loc_control_to IS
1750 SELECT locator_type
1751 FROM mtl_secondary_inventories
1752 WHERE secondary_inventory_name = l_request_line_rec.to_subinventory_code
1753 AND organization_id = l_request_line_rec.to_organization_id;
1754
1755 CURSOR c_item_loc_control_from IS
1756 SELECT location_control_code
1757 FROM mtl_system_items
1758 WHERE inventory_item_id = l_request_line_rec.inventory_item_id
1759 AND organization_id = l_request_line_rec.organization_id;
1760
1761 CURSOR c_item_loc_control_to IS
1762 SELECT location_control_code
1763 FROM mtl_system_items
1764 WHERE inventory_item_id = l_request_line_rec.inventory_item_id
1765 AND organization_id = l_request_line_rec.to_organization_id;
1766
1767 CURSOR c_rule_type_code IS
1768 SELECT type_code
1769 FROM wms_rules_b
1770 WHERE rule_id = p_simulation_id;
1771
1772 CURSOR c_strategy_type_code IS
1773 SELECT type_code
1774 FROM wms_strategies_b
1775 WHERE strategy_id = p_simulation_id;
1776
1777 CURSOR c_project_param IS
1778 SELECT allow_cross_proj_issues
1779 , allow_cross_unitnum_issues
1780 FROM pjm_org_parameters
1781 WHERE organization_id = l_request_line_rec.organization_id;
1782
1783 -- 8809951 Modified Cursor
1784 CURSOR c_wip_reservations IS
1785 SELECT 1
1786 FROM mtl_reservations
1787 WHERE organization_id = l_request_line_rec.organization_id
1788 AND supply_source_type_id = 5
1789 AND supply_source_header_id = l_request_line_rec.txn_source_id;
1790
1791
1792 --Bug #3051649 /Grao : Org locator control
1793 CURSOR c_org_loc_control IS
1794 SELECT stock_locator_control_code
1795 FROM mtl_parameters
1796 WHERE organization_id = l_request_line_rec.organization_id;
1797
1798 /* Bug # 5265024
1799 CURSOR l_wms_txn_temp_id_csr IS
1800 SELECT wms_transactions_temp_s.NEXTVAL
1801 FROM DUAL;
1802 */
1803
1804 --BUG14517947
1805 CURSOR get_wip_entity_type IS
1806 SELECT entity_type
1807 FROM wip_entities
1808 WHERE wip_entity_id = l_request_line_rec.txn_source_id
1809 AND organization_id = l_request_line_rec.organization_id;
1810
1811 CURSOR c_uom_class(p_uom IN VARCHAR2) IS
1812 SELECT uom_class
1813 FROM mtl_units_of_measure_vl
1814 WHERE uom_code = p_uom;
1815
1816 BEGIN
1817
1818 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
1819 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1820 END IF;
1821 l_debug := g_debug;
1822 l_progress := 10;
1823 --
1824 If l_debug = 1 THEN
1825 log_procedure(l_api_name, 'start', 'Start create_suggestions');
1826 log_event(
1827 l_api_name
1828 , 'start_detail'
1829 , 'Starting the WMS Rules engine ' || 'to allocate material for move order line: '
1830 || p_transaction_temp_id
1831 );
1832 End if;
1833 --8809951 Clean up all the rules in the cahce.
1834
1835 --10182554 Start
1836 IF check_exp_lot_txn_allowed (p_transaction_temp_id) THEN
1837 wms_rule_pvt.g_allow_expired_lot_txn := 'Y';
1838 IF (l_debug = 1) THEN
1839 log_event( l_api_name, 'g_allow_expired_lot_txn in WMS ENGINE','Setting value for allow expired lots to be transacted as True');
1840 END IF;
1841 ELSE
1842 wms_rule_pvt.g_allow_expired_lot_txn := 'N';
1843 IF (l_debug = 1) THEN
1844 log_event( l_api_name, 'g_allow_expired_lot_txn in WMS ENGINE','Setting value for allow expired lots to be transacted as FALSE');
1845 END IF;
1846 END IF;
1847 --10182554 End
1848
1849 Wms_cache.Cleanup_rules_cache;
1850
1851 l_allow_nr_sub_xfer := Upper(NVL( SUBSTR(FND_PROFILE.VALUE('INV_ALLOW_NR_SUB_XFER'),1,1), 'N')) ; -- Bug#4006426
1852
1853 If l_debug = 1 THEN
1854 log_event( l_api_name, 'WMS_ALLOW_NR_SUB_XFER := ', l_allow_nr_sub_xfer );
1855 End if;
1856
1857 /* Fix for Bug#8355668. Remove existing elements for Select Available Inventory form */
1858 if (p_simulation_mode = 10 ) then
1859 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.DELETE ;
1860 end if ;
1861
1862 -- Standard start of API savepoint
1863 SAVEPOINT create_suggestions_sa;
1864
1865 --
1866 -- Standard Call to check for call compatibility
1867 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1868 RAISE fnd_api.g_exc_unexpected_error;
1869 END IF;
1870
1871 --
1872 -- Initialize message list if p_init_msg_list is set to true
1873 IF fnd_api.to_boolean(p_init_msg_list) THEN
1874 fnd_msg_pub.initialize;
1875 END IF;
1876
1877 if p_wave_simulation_mode ='Y' then l_simulation_mode := 0; end if;
1878 --
1879 -- Initialisize API return status to access
1880 x_return_status := fnd_api.g_ret_sts_success;
1881 --
1882 g_trace_header_id := NULL;
1883 g_business_object_id := NULL;
1884 g_sugg_failure_message := NULL; -- Patchset 'J'
1885
1886 -- log_event(l_api_name, 'Create Suggestions', 'J check');
1887 -- 'J Changes Initilize the local Quickpick variable
1888 IF (l_current_release_level >= l_j_release_level
1889 And p_simulation_mode <> 10 -- LG convergence add
1890 ) THEN
1891
1892 IF l_debug = 1 THEN
1893 log_event(l_api_name, 'Check J release', 'Current release is above J. set l_quick_pick_flag to Y');
1894 END IF;
1895 l_quick_pick_flag := nvl(p_quick_pick_flag, 'N');
1896 IF ( l_quick_pick_flag = 'N' and p_simulation_mode IS not NULL) then
1897 --- get LPN Context for the given move order line
1898 --- if LPN_CONTEXT is 1 then set the quick_pick_flag = 'Y'
1899 IF l_debug = 1 THEN
1900 log_event(l_api_name, 'Create Suggestions',
1901 'If in Simulation Mode - Get LPN Context');
1902 END IF;
1903
1904 BEGIN
1905 l_progress := 100;
1906
1907 SELECT lpn_context
1908 INTO l_lpn_context
1909 FROM wms_license_plate_numbers wlpn
1910 WHERE lpn_id = (SELECT lpn_id
1911 FROM mtl_txn_request_lines mtrl
1912 WHERE mtrl.line_id = p_transaction_temp_id)
1913 AND EXISTS(SELECT 1 FROM wms_lpn_contents wlc, mtl_txn_request_lines mtrl -- for bug 14035958
1914 WHERE wlc.parent_lpn_id=mtrl.lpn_id
1915 AND mtrl.line_id = p_transaction_temp_id
1916 AND wlc.parent_lpn_id=wlpn.lpn_id
1917 And wlc.inventory_item_id=mtrl.inventory_item_id
1918 AND (wlc.lot_number IS NULL OR wlc.lot_number=mtrl.lot_number) --if no lot infor in mtrl, don't quick pick
1919 AND (wlc.revision IS NULL OR wlc.revision=mtrl.revision) --if no revision infor in mtrl, don't quick pick
1920 AND (wlc.serial_summary_entry <>1 OR (mtrl.serial_number_start IS NOT NULL AND mtrl.serial_number_end IS NOT NULL)) --if no serial infor in mtrl, don't quick pick
1921 );
1922 l_progress := 110;
1923
1924 IF l_lpn_context = 1 THEN
1925 l_quick_pick_flag := 'Y';
1926 End if;
1927 EXCEPTION
1928 WHEN OTHERS THEN
1929 IF l_debug = 1 THEN
1930 log_error(l_api_name, 'other', 'lpn_context in create_suggestions is not available ');
1931 END IF;
1932 NULL;
1933 END; -- End of Begin
1934
1935 END IF; -- End If l_quick_pick_flag = 'N' and p_simulation_mode IS not NULL
1936
1937 ELSE
1938 /* Bug # 4006426 -- Quick Pick functionality is enabled for 11.5.9 /Version 'I'
1939 Following code is added to check the context of the LPN and the value
1940 of the l_quick_pick_flag is set to 'Q'
1941 so that, the calling programs (WMSVPPSB.pls and WMSVPPRB.pls ) could behave differently
1942
1943 New Behavior : Qty tree is not going to be created or queried for performance reasons instead 'Availability
1944 check' will be done based on the MOQD and reservation tables. A new local procedure
1945 validate_and_insert_noqtytree() will be created to insert data in WTT */
1946
1947 l_quick_pick_flag := 'N';
1948
1949 BEGIN
1950 SELECT lpn_context
1951 INTO l_lpn_context
1952 FROM wms_license_plate_numbers
1953 WHERE lpn_id = l_request_line_rec.lpn_id ; /* (SELECT lpn_id
1954 FROM mtl_txn_request_lines mtrl
1955 WHERE mtrl.line_id = p_transaction_temp_id);*/
1956
1957 IF l_lpn_context = 1 AND l_allow_nr_sub_xfer = 'Y' THEN
1958 l_quick_pick_flag := 'Q';
1959 ELSE
1960 l_quick_pick_flag := 'N';
1961 End if;
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 IF l_debug = 1 THEN
1965 log_error(l_api_name, 'other', 'lpn_context in create_suggestions is not available 1159 ');
1966 END IF;
1967 NULL;
1968 END; -- End of Begin
1969
1970 END IF;
1971
1972 -- log_event(l_api_name, 'Create Suggestions', 'after J check');
1973 --validation simulation mode
1974 -- Simulation mode should = 0 if user passes invalid value
1975 -- for simulation mode, or if user passes simulation mode as 1 or 2,
1976 -- but doesn't pass in simulation_id
1977 IF p_simulation_mode IS NULL
1978 OR (p_simulation_mode < g_full_simulation AND p_simulation_mode <> g_available_inventory)
1979 OR p_simulation_mode > g_put_full_mode THEN
1980 l_simulation_mode := g_no_simulation;
1981 ELSIF p_simulation_id IS NULL
1982 AND p_simulation_mode
1983 IN (g_pick_rule_mode, g_pick_strategy_mode
1984 , g_put_rule_mode, g_put_strategy_mode)
1985 THEN
1986 l_simulation_mode := g_no_simulation;
1987 ELSE
1988 l_simulation_mode := p_simulation_mode;
1989 END IF;
1990 IF l_debug = 1 THEN
1991 log_event(l_api_name, 'Create Suggestions', 'simulation mode '||l_simulation_mode);
1992 END IF;
1993
1994 -- Revert Capacity in any simulation mode
1995 IF l_simulation_mode <> g_no_simulation THEN
1996 l_revert_capacity := TRUE;
1997 ELSE
1998 l_revert_capacity := FALSE;
1999 END IF;
2000
2001 -- validate input and initialize
2002 If l_debug = 1 THEN
2003 log_event(l_api_name, 'Create Suggestions', 'before init');
2004 END IF;
2005 inv_detail_util_pvt.validate_and_init(x_return_status
2006 , p_transaction_temp_id
2007 , p_suggest_serial
2008 , l_request_line_rec
2009 , l_request_context
2010 , p_wave_simulation_mode);
2011
2012 IF l_return_status = fnd_api.g_ret_sts_error THEN
2013 RAISE fnd_api.g_exc_error;
2014 END IF;
2015 IF l_debug = 1 THEN
2016 log_event(l_api_name, 'Create Suggestions', 'after init');
2017 log_event(l_api_name, 'Create Suggestions', 'l_return_status '||l_return_status);
2018 log_event(l_api_name, 'Create Suggestions', 'Cross-Doc Data --------------');
2019 log_event(l_api_name, 'Create Suggestions', 'backorder_delivery_detail_id :'||l_request_line_rec.backorder_delivery_detail_id);
2020 log_event(l_api_name, 'Create Suggestions', 'to_subinventory_code :'||l_request_line_rec.to_subinventory_code);
2021 log_event(l_api_name, 'Create Suggestions', 'to_locator_id:'||l_request_line_rec.to_locator_id);
2022 log_event(l_api_name, 'Create Suggestions', 'txn_source_id:'||l_request_line_rec.txn_source_id); --BUG14517947
2023 log_event(l_api_name, 'Create Suggestions', 'txn uom code :'||l_request_line_rec.uom_code);
2024 log_event(l_api_name, 'Create Suggestions', 'sec uom code :'||l_request_line_rec.secondary_uom_code);
2025 END IF;
2026
2027 g_mo_quantity := l_request_line_rec.primary_quantity; -- [ Storing the mo qty for tolerance calculations ]
2028 g_mo_sec_qty := l_request_line_rec.secondary_quantity;
2029 g_Is_xdock := FALSE;
2030 IF l_request_line_rec.backorder_delivery_detail_id is not NULL and
2031 l_request_line_rec.to_subinventory_code is not null and
2032 l_request_line_rec.to_locator_id is not null THEN
2033 g_Is_xdock := TRUE ;
2034 END IF;
2035
2036 /*IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2037 RAISE fnd_api.g_exc_unexpected_error;
2038 END IF;*/
2039 -- Transfer flag is always false when simulating
2040 -- picking rules or strategies. When simulation putaway rules or
2041 -- strategies for transfers, we execute the picking side of the engine
2042 -- normally, so there is not need to change the transfer flag
2043 IF l_simulation_mode IN (g_pick_strategy_mode, g_pick_rule_mode, g_pick_full_mode) THEN
2044 l_request_context.transfer_flag := FALSE;
2045 l_request_context.type_code := 2;
2046 END IF;
2047
2048 --setting global variables used in wms_rule_pvt.
2049 If l_debug = 1 THEN
2050 log_event(l_api_name, 'Create Suggestions', 'before fetching move order type '||l_move_order_type);
2051 END IF;
2052 --8809951 start, Removed code to fetch from cursor and fetched it from INV.CACHE
2053
2054 IF NOT inv_cache. set_mtrh_rec(l_request_line_rec.header_id) THEN
2055 If l_debug = 1 THEN
2056 log_event(l_api_name,'Create Suggestions','MTRH not found ');
2057 End If;
2058 RAISE fnd_api.g_exc_unexpected_error;
2059 END IF;
2060 l_move_order_type :=inv_cache.mtrh_rec.move_order_type;
2061 --8809951 end
2062 IF l_debug = 1 THEN
2063 log_event(l_api_name, 'Create Suggestions', 'after fetching move order type '||l_move_order_type);
2064 log_event(l_api_name, 'Create Suggestions', 'before fetching wip entity type'||l_wip_entity_type);
2065 END IF;
2066
2067 g_move_order_type := l_move_order_type;
2068 g_transaction_action_id := l_request_context.transaction_action_id;
2069
2070 IF g_move_order_type IN (5, 8) THEN --BUG14517947
2071 OPEN get_wip_entity_type;
2072 FETCH get_wip_entity_type INTO l_wip_entity_type;
2073 CLOSE get_wip_entity_type;
2074
2075 g_wip_entity_type := l_wip_entity_type;
2076 ELSE
2077 g_wip_entity_type := -1;
2078 END IF;
2079
2080 IF l_debug = 1 THEN
2081 log_event(l_api_name, 'Create Suggestions', 'after fetching wip entity type'||g_wip_entity_type);
2082 END IF;
2083
2084 --the locator control variables are only checked for non-pick-wave
2085 -- move orders
2086 -- GRAO
2087 -- Bug# 2454149 : Same Source and Dest Subinv is allowed for both
2088 -- WIP Issues and Back flush type of MOve orders also.
2089
2090 -- Bug 2666620: BackFlush MO Type Removed
2091 IF l_move_order_type IN (3, 5) THEN --pick wave, WIP type move order
2092 g_dest_sub_pick_allowed := 1;
2093 ELSE
2094 --log_event(l_api_name, 'Create Suggestions', 'else ');
2095 g_dest_sub_pick_allowed := 0;
2096 END IF; -- bug 3972784 populate the globals regardless.
2097
2098 --8809951 start Removed cursors and using INV_CACHE
2099
2100 IF (inv_cache.set_fromsub_rec(l_request_line_rec.organization_id, l_request_line_rec.from_subinventory_code))
2101 THEN
2102 l_loc_control_from:=inv_cache. fromsub_rec.locator_type;
2103 END IF;
2104 IF (inv_cache.set_tosub_rec(l_request_line_rec.to_organization_id, l_request_line_rec.to_subinventory_code))
2105 THEN
2106 l_loc_control_to:=inv_cache. tosub_rec.locator_type;
2107 END IF;
2108 IF ( INV_CACHE.set_item_rec(l_request_line_rec.organization_id, l_request_line_rec.inventory_item_id) )
2109 THEN
2110 l_item_control_from:= inv_cache.item_rec.location_control_code;
2111 END IF;
2112 IF ( INV_CACHE.set_item_rec(l_request_line_rec.to_organization_id, l_request_line_rec.inventory_item_id) )
2113 THEN
2114 l_item_control_to := inv_cache.item_rec.location_control_code;
2115 END IF;
2116 IF (INV_CACHE.set_org_rec(l_request_line_rec.organization_id) ) THEN
2117 l_org_loc_control := inv_cache.org_rec. stock_locator_control_code;
2118 END If;
2119
2120 g_sub_loc_control := NVL(l_loc_control_from, l_loc_control_to);
2121 g_item_loc_control := NVL(l_item_control_from, l_item_control_to);
2122 g_org_loc_control := l_org_loc_control;
2123
2124 --8809951 end
2125
2126 --bug 2589499 -- if reservation exists for WIP putaway, do not putaway
2127 -- to non-reservable sub.
2128 --set a global variable here, and reference it in WMS_RULE_PVT
2129 g_reservable_putaway_sub_only := FALSE;
2130 l_wip_rsv_exists := 0;
2131
2132 -- log_event(l_api_name, 'Create Suggestions', 'after all the item check');
2133 --for putaway move orders, set posting flag to N (it is Y by default)
2134 IF l_move_order_type = 6 THEN -- put away move order
2135 -- l_request_context.posting_flag := 'N'; -- bug fix 3438349
2136
2137 If l_request_context.transaction_source_type_id = 5 And
2138 l_request_line_rec.transaction_type_id = 44 Then
2139
2140 OPEN c_wip_reservations;
2141 FETCH c_wip_reservations INTO l_wip_rsv_exists;
2142 IF c_wip_reservations%NOTFOUND THEN
2143 l_wip_rsv_exists := 0;
2144 END IF;
2145 CLOSE c_wip_reservations; -- Bug # 4997883
2146 IF l_wip_rsv_exists = 1 THEN
2147 g_reservable_putaway_sub_only := TRUE;
2148 END IF;
2149 End If;
2150 END IF;
2151
2152 g_serial_number_control_code:=l_request_context.item_serial_control_code;
2153 --don't detail serial numbers for items that are serial controlled only
2154 -- at sales order issue
2155 IF l_request_context.item_serial_control_code = 6 THEN
2156 l_request_context.item_serial_control_code := 1;
2157 END IF;
2158
2159 --log_event(l_api_name, 'Create Suggestions', 'before sys_task_type');
2160 /* Get the wms system task type
2161 changed the call to add 2 new paramters p_transaction_Action_id
2162 and p_transaction_source_type_id for patchset H changes */
2163 wms_rule_pvt.get_wms_sys_task_type(
2164 p_move_order_type => l_move_order_type
2165 , p_transaction_action_id => l_request_context.transaction_action_id
2166 , p_transaction_source_type_id => l_request_context.transaction_source_type_id
2167 , x_wms_sys_task_type => l_request_context.wms_task_type
2168 );
2169 --log_event(l_api_name, 'Create Suggestions', 'after sys_task_type');
2170
2171 -- Call the pick release stub API to get dual UOM allocation settings
2172 BEGIN
2173 g_sec_qty_round_mode := NULL;
2174 g_fulfillment_base := 'P';
2175
2176 IF l_request_context.type_code = 2 THEN --{
2177 IF (inv_cache.set_item_rec(
2178 l_request_line_rec.to_organization_id
2179 ,l_request_line_rec.inventory_item_id))
2180 THEN --{
2181 IF inv_cache.item_rec.dual_uom_control > 1 AND
2182 inv_cache.item_rec.tracking_quantity_ind = 'PS'
2183 THEN --{
2184 IF inv_cache.set_mol_rec(p_transaction_temp_id) THEN
2185 IF l_request_context.transaction_action_id = 28
2186 AND l_request_context.transaction_source_type_id IN (2,8)
2187 THEN
2188 IF l_debug = 1 THEN
2189 log_error(l_api_name,'Create Suggestions','Staging transfer');
2190 END IF;
2191 IF NVL(OE_DUAL_UOM_UTIL.get_fulfillment_base(inv_cache.mol_rec.txn_source_line_id), 'P') = 'S'
2192 THEN
2193 g_fulfillment_base := 'S'; -- secondary UOM
2194 ELSE
2195 g_fulfillment_base := 'P'; -- primary UOM
2196 END IF;
2197 IF l_debug = 1 THEN
2198 log_statement(l_api_name,'Create Suggestions','g_fulfillment_base from order line: ' || g_fulfillment_base);
2199 END IF;
2200 ELSE
2201 IF l_debug = 1 THEN
2202 log_error(l_api_name,'Create Suggestions','Not a staging transfer');
2203 log_error(l_api_name,'Create Suggestions','txn action: ' || l_request_context.transaction_action_id);
2204 log_error(l_api_name,'Create Suggestions','txn src type: ' || l_request_context.transaction_source_type_id);
2205 END IF;
2206 g_fulfillment_base := 'P';
2207 END IF;
2208 ELSE
2209 IF l_debug = 1 THEN
2210 log_error(l_api_name, 'Create Suggestions','Error calling inv_cache.set_mol_rec: ');
2211 END IF;
2212 RAISE fnd_api.g_exc_unexpected_error;
2213 END IF;
2214
2215 IF l_debug = 1 THEN
2216 log_statement(l_api_name, 'Create Suggestions'
2217 ,'Calling stub API to check dual UOM allocation settings');
2218 END IF;
2219
2220 l_return_status := fnd_api.g_ret_sts_success;
2221 wms_re_custom_pub.set_dualuom_alloc_prefs(
2222 p_organization_id => l_request_line_rec.organization_id
2223 , p_inventory_item_id => l_request_line_rec.inventory_item_id
2224 , p_move_order_type => l_move_order_type
2225 , p_transaction_type_id => l_request_line_rec.transaction_type_id
2226 , p_move_order_line_id => p_transaction_temp_id
2227 , x_return_status => l_return_status
2228 , x_msg_count => l_msg_count
2229 , x_msg_data => l_msg_data
2230 , x_sec_qty_round_mode => l_sec_qty_round_mode
2231 , x_target_uom => l_target_uom
2232 );
2233
2234 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2235 IF l_debug = 1 THEN
2236 log_error(l_api_name, 'Create Suggestions'
2237 ,'Error status returned from set_dualuom_alloc_prefs: ' || l_return_status);
2238 log_error(l_api_name, 'Create Suggestions'
2239 ,'Error message: ' || l_msg_data);
2240 END IF;
2241 g_sec_qty_round_mode := NULL;
2242 g_fulfillment_base := 'P'; -- primary UOM
2243 ELSE --{
2244 IF l_debug = 1 THEN
2245 log_statement(l_api_name, 'Create Suggestions'
2246 ,'API set_dualuom_alloc_prefs returned success');
2247 log_statement(l_api_name, 'Create Suggestions'
2248 ,'l_sec_qty_round_mode: ' || l_sec_qty_round_mode);
2249 log_statement(l_api_name, 'Create Suggestions'
2250 ,'l_target_uom: ' || l_target_uom);
2251 END IF;
2252 IF l_sec_qty_round_mode NOT IN ('F','C','R') THEN
2253 g_sec_qty_round_mode := NULL;
2254 ELSE
2255 g_sec_qty_round_mode := l_sec_qty_round_mode;
2256 END IF;
2257
2258 -- Do not override OM fulfillment base
2259 IF l_request_context.transaction_action_id <> 28
2260 AND l_request_context.transaction_source_type_id NOT IN (2,8)
2261 THEN
2262 IF (l_target_uom NOT IN ('P','S') OR l_target_uom IS NULL) THEN
2263 g_fulfillment_base := 'P'; -- primary UOM
2264 ELSE
2265 g_fulfillment_base := l_target_uom;
2266 END IF;
2267 END IF;
2268
2269 -- Secondary qty rounding is only supported if OM indivisible
2270 -- is not set to 'Y'
2271 IF NVL(inv_cache.item_rec.indivisible_flag,'N') = 'Y' THEN
2272 g_sec_qty_round_mode := NULL;
2273 END IF;
2274
2275 -- For fulfillment based on secondary, only flooring the
2276 -- secondary qty is supported
2277 IF g_fulfillment_base = 'S' AND g_sec_qty_round_mode IS NOT NULL THEN
2278 g_sec_qty_round_mode := 'F';
2279 END IF;
2280
2281 IF l_debug = 1 THEN
2282 log_statement(l_api_name, 'Create Suggestions'
2283 ,'g_sec_qty_round_mode (final): ' || g_sec_qty_round_mode);
2284 log_statement(l_api_name, 'Create Suggestions'
2285 ,'g_fulfillment_base (final): ' || g_fulfillment_base);
2286 END IF;
2287 END IF; --} return status of success from set_dualuom_alloc_prefs API
2288 END IF; --} Dual UOM item
2289 END IF; --} Set item cache
2290 END IF; --} Type code is 2 (Pick)
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 IF l_debug = 1 THEN
2294 log_error(l_api_name, 'Create Suggestions'
2295 ,'Error setting dualuom allocation preferences: ' || SQLERRM);
2296 END IF;
2297 g_sec_qty_round_mode := NULL;
2298 g_fulfillment_base := 'P';
2299 END;
2300
2301 OPEN c_uom_class(l_request_line_rec.secondary_uom_code);
2302 FETCH c_uom_class INTO l_sec_uom_class;
2303 CLOSE c_uom_class;
2304
2305 OPEN c_uom_class(l_request_line_rec.uom_code);
2306 FETCH c_uom_class INTO l_txn_uom_class;
2307 CLOSE c_uom_class;
2308
2309 IF l_sec_uom_class <> l_txn_uom_class THEN
2310 l_request_context.transaction_uom_code := inv_cache.item_rec.primary_uom_code;
2311 END IF;
2312
2313 IF l_debug = 1 THEN
2314 log_event(l_api_name, 'Create Suggestions', 'l_sec_uom_class :'||l_sec_uom_class);
2315 log_event(l_api_name, 'Create Suggestions', 'l_txn_uom_class :'||l_txn_uom_class);
2316 log_event(l_api_name, 'Create Suggestions', 'l_request_context.transaction_uom_code :'||l_request_context.transaction_uom_code);
2317 END IF;
2318
2319 INSERT INTO wms_txn_context_temp
2320 (
2321 line_id
2322 , txn_source_id
2323 , txn_source_line_id
2324 , txn_source_name
2325 , txn_source_line_detail
2326 , freight_carrier_code
2327 , customer_id
2328 )
2329 VALUES (
2330 l_request_line_rec.line_id
2331 , l_request_context.txn_header_id
2332 , l_request_context.txn_line_id
2333 , NULL
2334 , l_request_context.txn_line_detail
2335 , l_request_context.freight_code
2336 , l_request_context.customer_id
2337 );
2338
2339 -- Check whether this is a pick release process and if locator is specified bug3237702
2340 If inv_cache.is_pickrelease then
2341 is_pickrelease := true;
2342 l_locator_id := inv_cache.tolocator_id;
2343 ELSIF p_wave_simulation_mode = 'Y' THEN
2344 l_locator_id := inv_cache.tolocator_id;
2345 End if;
2346
2347 -- log_event(l_api_name, 'Create Suggestions', 'after insert txn_context');
2348 -- the first round will deal with issues, receipts
2349 -- and the issue part of transfers
2350 -- the second round will deal with the receipt part
2351 -- of transfers
2352 l_type_code := l_request_context.type_code;
2353 -- 'J Project' : Setting the quick_pick parameter to false , if it is putaway ---
2354 --IF (l_current_release_level >= l_j_release_level ) THEN --- Bug # 4006426
2355 IF l_type_code = 1 then
2356 l_quick_pick_flag := 'N';
2357 END IF;
2358 --END IF; -- Bug#4006426
2359 FOR l_counter IN 1 .. 2 LOOP
2360 -- if not a transfer, or if we are simulating strategy or rule,
2361 -- or simulation if for available inventory LG.
2362 -- no need to call Search and Apply a second time.
2363 IF l_counter = 2
2364 AND (l_request_context.transfer_flag = FALSE
2365 OR l_simulation_mode = g_available_inventory
2366 )
2367 THEN
2368 EXIT;
2369 END IF;
2370
2371 -- Find strategies when
2372 -- a. not simulating
2373 -- b. simulating the full pick process
2374 -- c. simulating the full put away process
2375 -- d. simulate put rule or strategy, you have to find pick strategy
2376 -- for transfers
2377
2378 IF l_simulation_mode IN (g_full_simulation, g_no_simulation, g_available_inventory) -- LG convergence
2379 OR l_simulation_mode IN (g_pick_full_mode, g_put_full_mode, g_pick_rule_mode, g_put_rule_mode
2380 ,g_put_strategy_mode, g_pick_strategy_mode) --Bug#6015798, Bug#7182139
2381 OR (l_simulation_mode IN (g_put_strategy_mode, g_put_rule_mode)
2382 AND l_request_context.transfer_flag = TRUE
2383 AND l_counter = 1
2384 ) THEN
2385 IF l_counter = 1 THEN
2386 IF l_request_context.type_code = 2 THEN
2387 IF l_debug = 1 THEN
2388 log_event(l_api_name, 'start_pick', 'Starting pick allocation');
2389 END IF;
2390 l_strategy_id := l_request_context.pick_strategy_id;
2391 IF l_simulation_mode = g_pick_strategy_mode THEN
2392 l_strategy_id := p_simulation_id;
2393 END IF;
2394 ELSE
2395 IF l_debug = 1 THEN
2396 log_event(l_api_name, 'start_put_only', 'Starting put away allocation');
2397 END IF;
2398 l_strategy_id := l_request_context.put_away_strategy_id;
2399 END IF;
2400 ELSE
2401 IF l_debug = 1 then
2402 log_event(l_api_name, 'start_put', 'Starting put away allocation');
2403 END IF;
2404 l_strategy_id := l_request_context.put_away_strategy_id;
2405 l_type_code := 1; -- put away for the second round
2406 END IF;
2407 --Begin bug 4749595/4769085
2408 IF l_type_code = 1 then
2409 l_quick_pick_flag := 'N';
2410 END IF;
2411 --End bug 4749595/4769085
2412
2413 IF l_debug = 1 THEN
2414 log_statement(l_api_name, 'strategy_search', 'modified set l_quick_pick_flag '|| l_quick_pick_flag);
2415 log_event(l_api_name, 'start_pick', 'getting the context ');
2416 END IF;
2417
2418 -- clean up the input records first
2419 DELETE FROM wms_transactions_temp
2420 WHERE transaction_temp_id = p_transaction_temp_id
2421 AND line_type_code = 1;
2422
2423 --
2424 IF p_simulation_mode <> 10 THEN -- LG convergenece add
2425 IF l_counter = 1 THEN
2426 -- Prepare transaction records for pp engine
2427 -- for transfers only the issue is considered at this time
2428 prepare(l_return_status
2429 , l_request_line_rec
2430 , l_request_context
2431 , p_reservations
2432 , l_allow_non_partial_rules);
2433
2434 --
2435 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2436 RAISE fnd_api.g_exc_error;
2437 END IF;
2438 ELSE
2439 -- Treat receipt for transfers, ie. copy the issue output data in
2440 -- WMS_TRANSACTIONS_TEMP as new input records for receipt part
2441 prepare_transfer_receipt(l_return_status
2442 , l_request_line_rec
2443 , l_request_context);
2444 --
2445 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2446 RAISE fnd_api.g_exc_error;
2447 END IF;
2448 END IF;
2449 END IF;
2450 -- LG convergence add
2451 -- Since we may not have a reservation when p_simulation_mode is 10 for all move order types
2452 -- so insert fake data into wms_transactions_temp
2453 IF p_simulation_mode = g_available_inventory
2454 THEN
2455 /* 5265024
2456 OPEN l_wms_txn_temp_id_csr;
2457 FETCH l_wms_txn_temp_id_csr INTO l_pp_transaction_temp_id ;
2458 CLOSE l_wms_txn_temp_id_csr;
2459 */
2460 log_event(l_api_name, '','insert into wtt '||l_pp_transaction_temp_id);
2461 INSERT INTO wms_transactions_temp
2462 (
2463 pp_transaction_temp_id
2464 , transaction_temp_id -- mo_line_id
2465 , type_code -- mo
2466 , line_type_code -- 1
2467 , transaction_quantity
2468 , primary_quantity
2469 , secondary_quantity
2470 )
2471 VALUES ( wms_transactions_temp_s.NEXTVAL
2472 -- l_pp_transaction_temp_id
2473 , l_request_line_rec.line_id
2474 , 2
2475 , 1
2476 , l_request_line_rec.quantity
2477 , l_request_line_rec.quantity
2478 , l_request_line_rec.secondary_quantity
2479 );
2480 END IF;
2481 -- end LG convergence
2482 --
2483 IF (wms_rule_pvt.isruledebugon(l_simulation_mode)
2484 and l_simulation_mode <> g_available_inventory) THEN
2485 IF l_debug = 1 THEN
2486 log_procedure(l_api_name, 'insert_trace_header', 'Calling insert_trace_header ');
2487 END IF;
2488 wms_search_order_globals_pvt.insert_trace_header(
2489 p_api_version => 1.0
2490 , p_init_msg_list => fnd_api.g_false
2491 , p_validation_level => fnd_api.g_valid_level_full
2492 , x_return_status => l_return_status
2493 , x_msg_count => l_msg_count
2494 , x_msg_data => l_msg_data
2495 , x_header_id => g_trace_header_id
2496 , p_pick_header_id => g_trace_header_id
2497 , p_move_order_line_id => p_transaction_temp_id
2498 , p_total_qty => l_request_line_rec.quantity - l_request_line_rec.quantity_detailed
2499 , p_secondary_total_qty => l_request_line_rec.secondary_quantity - l_request_line_rec.secondary_quantity_detailed
2500 , p_type_code => l_type_code
2501 , p_business_object_id => g_business_object_id
2502 , p_object_id => l_sequence_number
2503 , p_strategy_id => l_strategy_id
2504 );
2505
2506 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2507 RAISE fnd_api.g_exc_error;
2508 END IF;
2509 END IF;
2510
2511 -- Added to support PJM
2512 -- Query the columns ALLOW_CROSS_PROJ_ISSUES and ALLOW_CROSS_UNITNUM_ISSUES
2513 -- from the table pjm_org_Parameters where
2514 -- If ALLOW_CROSS_PROJ_ISSUES is Y, then set project and task to NULL
2515 -- for picks only.
2516 -- If ALLOW_CROSS_UNITNUM_ISSUES is Y, set the unit_number to NULL.
2517 -- BUG 2880682 : Treating null project/task as common project/task and forcing
2518 -- picking from common inventory when allow_cross_proj_issues is off.
2519 -- The same holds for unit number
2520 l_project_id := l_request_line_rec.project_id;
2521 l_task_id := l_request_line_rec.task_id;
2522 l_unit_number := l_request_line_rec.unit_number;
2523
2524 IF l_type_code = 2 THEN
2525 --8809951 start
2526 IF ( INV_CACHE.set_pjm_org_parms_rec(l_request_line_rec.organization_id)) THEN --9650219
2527 l_allow_cross_proj_issues := Nvl(inv_cache.pjm_org_parms_rec.allow_cross_proj_issues,'Y');
2528 l_allow_cross_unitnum_issues := Nvl(inv_cache.pjm_org_parms_rec.allow_cross_unitnum_issues,'Y');
2529 ELSE
2530 log_procedure(l_api_name, 'Error in setting pjm_org_parms_rec','Setting pjm_org_parms');
2531 END IF;
2532 --8809951 end
2533
2534 --start change for bug 8292754
2535 --checking if org is wms enabled .Added for bug 8292754
2536 l_wms_installed := WMS_INSTALL.check_install(
2537 x_return_status => x_api_return_status,
2538 x_msg_count => x_msg_count,
2539 x_msg_data => x_msg_data,
2540 p_organization_id => l_request_line_rec.organization_id);
2541 --Changed for bug 8292754
2542 --Allow cross project issue is not supported for sales order issue in non-wms org
2543
2544 -- Added for Bug 10379126
2545 IF (l_request_line_rec.transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE or
2546 l_request_line_rec.transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_REPL_SUBXFR) THEN
2547 l_project_id := nvl(l_project_id, -7777);
2548 l_task_id := nvl(l_task_id, -7777);
2549 ELSE -- Added for 10379126
2550
2551 IF (l_allow_cross_proj_issues = 'Y')
2552 AND ((l_request_context.transaction_source_type_id <> 2 AND l_wms_installed= FALSE )
2553 OR (l_wms_installed = TRUE))
2554 THEN
2555 log_procedure(l_api_name, 'l_allow_cross_proj_issues', 'Nulling project and Task ');
2556 l_project_id := NULL;
2557 l_task_id := NULL;
2558 ELSE
2559 log_procedure(l_api_name, 'l_allow_cross_proj_issues', 'Maintaining project and Task ');
2560 l_project_id := nvl(l_project_id, -7777);
2561 l_task_id := nvl(l_task_id, -7777);
2562 END IF;
2563 --End change for bug 8292754
2564
2565
2566 IF l_allow_cross_unitnum_issues = 'Y' THEN
2567 l_unit_number := NULL;
2568 ELSE
2569 l_unit_number := nvl(l_unit_number, '-7777');
2570 END IF;
2571
2572 --start change for bug 9210454
2573 BEGIN
2574 l_return_val := inv_cache.set_item_rec(l_request_line_rec.organization_id, l_request_line_rec.inventory_item_id);
2575 log_procedure(l_api_name, 'Setting inv_cache', 'for soft pegg item');
2576 EXCEPTION
2577 WHEN OTHERS THEN
2578 NULL;
2579 END;
2580
2581 IF (l_wms_installed= FALSE AND inv_cache.item_rec.end_assembly_pegging_flag IN ('A','Y','B')) THEN
2582 log_procedure(l_api_name, 'Soft Pegged Item in INV org', 'Nulling project and Task for soft pegged item ');
2583 l_project_id := NULL;
2584 l_task_id := NULL;
2585 END IF;
2586
2587 --end change for bug 9210454
2588 END IF; -- Added for Bug 10379126
2589 END IF;
2590 ------------------------
2591 -- [[
2592
2593 --Added bug3237702
2594 -- search for a strategy if not given in the input parameter
2595 -- If pick_release and locator supplied and doing Putaway side of transaction
2596 -- then no need to use rule
2597
2598 -- [ Setting g_use_rule flag = 'Y' for follwing cases ]
2599 -- Case 1. If pick release and putaway loop and locator is not null - Pick release
2600 -- Case 2. If Detailed reservations exsist for the total requested qty and the item is serial - handled in prepare()
2601 -- Case 3. For Cross-docking putaway
2602
2603 -- [Case 1
2604 --IF (is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL ) THEN
2605 IF ((is_pickrelease OR p_wave_simulation_mode = 'Y') AND l_type_code = 1 AND l_locator_id IS NOT NULL ) THEN
2606 g_use_rule := 'N' ;
2607 END IF;
2608 -- [Case 3
2609 If (g_Is_xdock and l_type_code = 1) THEN
2610 g_use_rule := 'N' ;
2611 END IF;
2612 -----
2613 IF l_debug = 1 THEN
2614 log_event(l_api_name, 'Setting g_use_rule', g_use_rule);
2615 --IF (is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL ) THEN
2616 IF ((is_pickrelease OR p_wave_simulation_mode = 'Y') AND l_type_code = 1 AND l_locator_id IS NOT NULL ) THEN
2617 log_statement(l_api_name, 'Case 2:' , 'Pick release + Putaway loop + Dest. Locator is not null');
2618 ELSIF (g_Is_xdock and l_type_code = 1) THEN
2619 log_statement(l_api_name, 'Case 3:' , 'Cross-dock + Putaway + Dest. Locator is not null');
2620 ELSE
2621 log_statement(l_api_name, 'Case 1:' , 'Serial detailed resv + Picking');
2622 END IF;
2623 END IF;
2624 -----
2625 If (l_strategy_id IS NULL) /* AND NOT (is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL)*/ THEN
2626 --IF l_strategy_id IS NULL THEN Bug 3237702 ends
2627 IF l_debug = 1 THEN
2628 log_event(l_api_name, 'strategy_search', 'Strategy not defined on move order line. Calling '
2629 || 'the strategy search procedure');
2630 log_statement(l_api_name, 'strategy_search', 'l_quick_pick_flag '|| l_quick_pick_flag );
2631 log_statement(l_api_name, 'strategy_search', 'g_use_rule '|| g_use_rule);
2632 END IF;
2633 -- [ Added the condition of - g_use_rule = 'Y' ]
2634 IF nvl(l_quick_pick_flag, 'N') = 'N' AND ( g_use_rule = 'Y' ) THEN
2635 IF l_debug = 1 THEN
2636 log_event(l_api_name, 'Calling Strategy Search', 'wms_rules_workbench_pvt.search()');
2637 log_statement(l_api_name, 'p_transaction_temp_id =>' ,p_transaction_temp_id);
2638 END IF;
2639 l_return_type := 0;
2640 l_return_type_id := 0;
2641 l_rule_id := NULL; --- Bug# 5178290 / 5233300
2642 wms_rules_workbench_pvt.search(
2643 p_api_version => 1.0
2644 , p_init_msg_list => fnd_api.g_false
2645 , p_validation_level => fnd_api.g_valid_level_none
2646 , x_return_status => l_return_status
2647 , x_msg_count => l_msg_count
2648 , x_msg_data => l_msg_data
2649 , p_transaction_temp_id => p_transaction_temp_id
2650 , p_type_code => l_type_code
2651 , x_return_type => l_return_type
2652 , x_return_type_id => l_return_type_id
2653 , p_organization_id => l_request_line_rec.organization_id
2654 , x_sequence_number => l_sequence_number
2655 );
2656
2657 IF l_debug = 1 THEN
2658 log_event(l_api_name, 'End Search', 'Values returned ..');
2659 log_statement(l_api_name, 'l_return_status =>' ,l_return_status);
2660 log_statement(l_api_name, 'p_organization_id =>' ,l_request_line_rec.organization_id);
2661 log_statement(l_api_name, 'l_type_code =>' ,l_type_code);
2662 log_statement(l_api_name, 'l_sequence_number =>' ,l_sequence_number);
2663 log_statement(l_api_name, 'l_return_type =>' ,l_return_type);
2664 log_statement(l_api_name, 'l_return_type_id =>' ,l_return_type_id);
2665 END IF;
2666
2667 -- If no strategy is assigned, still detail, but
2668 -- with no strategy or rules
2669
2670 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2671
2672 IF l_debug = 1 THEN
2673 log_event(
2674 l_api_name
2675 , 'no_strategy_found'
2676 , 'The strategy search function did not find an ' || 'eligible strategy for this move order.'
2677 );
2678 END IF;
2679
2680 l_strategy_id := NULL;
2681 ELSE -- for assigning strategy Id based on the Return type , if it is 'S'
2682
2683 IF l_return_type = 'S' THEN
2684 l_strategy_id := l_return_type_id;
2685 l_rule_id := NULL;
2686 -- [ Based on the rule assignments , rule work bench returns the rule_id ]
2687 ELSIF l_return_type = 'R' THEN
2688 l_rule_id := l_return_type_id;
2689 l_strategy_id := NULL;
2690 END IF;
2691 IF l_debug = 1 THEN
2692 log_statement(l_api_name, 'wms_rules_workbench_pvt.search()', 'l_strategy_id '|| l_strategy_id );
2693 ELSE
2694 log_statement(l_api_name, 'wms_rules_workbench_pvt.search()', 'l_rule_id '|| l_rule_id );
2695 END IF;
2696
2697 END IF; -- FND_API.G_RET_STS_SUCCESS
2698
2699 END IF; -- J PROJECT : if not quickpick
2700 END IF; --STRATEGY ID / Rule ID is NULL
2701
2702 ELSIF l_simulation_mode IN (g_pick_strategy_mode, g_put_strategy_mode) THEN
2703 l_strategy_id := p_simulation_id;
2704 ELSE
2705 l_strategy_id := NULL;
2706 END IF;
2707 -- [ setting the flag g_use_rule for putaway
2708 -- all the code to use/notuse rules search will be streamlined
2709 -- Logic : if the search API is not called , then set the rule id to -999
2710 -- so that it could be handled in the strategy program as a special program
2711 -- Case 1. Pick - Serial reservation exisit for the total requested qty
2712 -- Case 2. Pick- detailed reservations exisit and org-level override rule flag
2713 -- from the mtl_parameters is 'Yes' - OPM case
2714 -- Case 3. Put away - For pickrelease and if destnation locator exisit
2715 -- Case 4. Putaway - For non WMS enabled org, do not call putaway rules
2716 -- Case 5. Putaway - For Crossdock putaaway , do not call putaway rules
2717 --
2718 --]
2719 IF g_use_rule = 'N' THEN
2720 g_use_rule := 'Y';
2721 l_rule_id := -999;
2722 l_strategy_id := NULL;
2723 END IF;
2724 --
2725 -- For put away strategy and rule, set type code to put away on
2726 -- second time through the loop. Type code for putaway full is
2727 -- set above
2728 IF l_simulation_mode IN (g_put_strategy_mode, g_put_rule_mode)
2729 AND l_counter = 2 THEN
2730 l_type_code := 1;
2731 END IF;
2732 -- record the strategy ids in the package globals
2733 IF l_counter = 1 THEN
2734 IF l_type_code = 2 THEN
2735 l_request_context.pick_strategy_id := l_strategy_id;
2736 ELSE
2737 l_request_context.put_away_strategy_id := l_strategy_id;
2738 END IF;
2739 ELSE
2740 l_request_context.put_away_strategy_id := l_strategy_id;
2741 END IF;
2742
2743 --]]
2744 -------------------------
2745 display_temp_records;
2746 IF l_debug = 1 THEN
2747 log_event(l_api_name,'Engine_pvt', 'Calling wms_strategy.apply()' );
2748 log_statement(l_api_name, 'Engine_pvt', 'Begin allocating ' || 'for strategy: ' || l_strategy_id);
2749 End if;
2750 -- If pick release then no strategy or Rule
2751 --IF is_pickrelease AND l_type_code = 1 AND l_locator_id IS NOT NULL THEN
2752 IF (is_pickrelease OR p_wave_simulation_mode = 'Y') AND l_type_code = 1 AND l_locator_id IS NOT NULL THEN
2753 l_strategy_id := NULL;
2754 END IF;
2755
2756 wms_strategy_pvt.apply
2757 (
2758 p_api_version => 1.0,
2759 p_init_msg_list => fnd_api.g_false,
2760 p_validation_level => fnd_api.g_valid_level_none,
2761 x_return_status => l_return_status,
2762 x_msg_count => l_msg_count,
2763 x_msg_data => l_msg_data,
2764 p_transaction_temp_id => p_transaction_temp_id,
2765 p_type_code => l_type_code,
2766 p_strategy_id => l_strategy_id,
2767 p_rule_id => l_rule_id,
2768 p_detail_serial => l_request_context.detail_serial,
2769 p_from_serial => l_request_line_rec.serial_number_start,
2770 p_to_serial => l_request_line_rec.serial_number_end,
2771 p_detail_any_serial => l_request_context.detail_any_serial,
2772 p_unit_volume => l_request_context.unit_volume,
2773 p_volume_uom_code => l_request_context.volume_uom_code,
2774 p_unit_weight => l_request_context.unit_weight,
2775 p_weight_uom_code => l_request_context.weight_uom_code,
2776 p_base_uom_code => l_request_context.base_uom_code,
2777 p_lpn_id => l_request_line_rec.lpn_id,
2778 p_unit_number => l_unit_number,
2779 p_allow_non_partial_rules => l_allow_non_partial_rules,
2780 p_simulation_mode => l_simulation_mode,
2781 p_simulation_id => p_simulation_id,
2782 p_project_id => l_project_id,
2783 p_task_id => l_task_id,
2784 p_quick_pick_flag => l_quick_pick_flag,
2785 p_wave_simulation_mode => p_wave_simulation_mode
2786 );
2787 --Bug3237702 ends
2788 -- IF (l_current_release_level >= l_j_release_level ) THEN -- Commented for Bug# 4006426
2789 IF l_quick_Pick_flag = 'Y' and l_type_code = 2 then
2790 l_quick_Pick_flag := 'N';
2791 IF l_debug = 1 THEN
2792 log_event(l_api_name, ' Create suggestions', ' Setting the value of l_quick_Pick');
2793 END IF;
2794 IF l_return_status <> fnd_api.g_ret_sts_success then
2795 RAISE fnd_api.g_exc_error;
2796 ELSE
2797 IF ((WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE = 'WMS_ATT_SUB_STATUS_NA' ) or
2798 (WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE = 'WMS_ATT_SERIAL_STATUS_NA' )) then
2799 IF l_debug = 1 THEN
2800 log_event(l_api_name, 'Create Suggestions',
2801 'Quick Pick Validation failure message '
2802 || WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE );
2803 END IF;
2804 ROLLBACK TO create_suggestions_sa;
2805 exit;
2806 END IF;
2807 END IF;
2808 END IF;
2809 /* Commented for Bug #4006426
2810 ELSE
2811 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2812 RAISE fnd_api.g_exc_error;
2813 END IF;
2814 END IF;
2815 */
2816 display_temp_records;
2817 END LOOP;
2818 --
2819 --
2820
2821 IF l_revert_capacity THEN
2822
2823 IF l_debug = 1 THEN
2824 log_event(l_api_name, 'Create Suggestions',
2825 'calling rollback_capacity for item '
2826 || l_request_line_rec.inventory_item_id);
2827 END IF;
2828
2829 wms_rule_pvt.rollback_capacity_update(
2830 x_return_status => l_return_status
2831 , x_msg_count => l_msg_count
2832 , x_msg_data => l_msg_data
2833 , p_organization_id => l_request_line_rec.organization_id
2834 , p_inventory_item_id => l_request_line_rec.inventory_item_id
2835 );
2836
2837 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2838 IF l_debug = 1 THEN
2839 log_event(l_api_name, 'Create Suggestions','err in rollback_capacity: '|| l_msg_data);
2840 END IF;
2841 RAISE fnd_api.g_exc_error;
2842 END IF;
2843 END IF;
2844
2845 -- Insert rows to MTL_MATERIAL_TRANSACTIONS_TEMP,
2846 -- MTL_TRANSACTIONLOTS_TEMP, MTL_SERIAL_NUMBERS_TEMP.
2847 -- For transfers combine issue and receipt suggestions
2848 -- to complete transfer transaction
2849 -- Skip this step when simulating rule or strategy; we want to
2850 -- keep records in WTT, and don't want to insert into MMTT.
2851
2852 -- Fix for Bug#8421562 . Added g_available_inventory in following if clause as
2853 -- Records in GTT should be deleted if Select Available inventory form is called
2854 -- multiple times in a single session with same move_order_line_id
2855
2856 IF l_simulation_mode IN (g_full_simulation, g_no_simulation, g_available_inventory ) THEN
2857 --added by jcearley on 11/22/99 - output table must be initialized
2858 inv_detail_util_pvt.init_output_process_tbl;
2859
2860 IF l_request_context.transaction_action_id IN (2, 3, 28) THEN
2861 combine_transfer(l_return_status, l_request_line_rec, l_request_context, p_plan_tasks);
2862 ELSE
2863 output_issue_or_receipt(l_return_status, l_request_line_rec, l_request_context, p_plan_tasks);
2864 END IF;
2865
2866 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2867 RAISE fnd_api.g_exc_error;
2868 END IF;
2869
2870 IF l_simulation_mode = g_no_simulation
2871 AND wms_rule_pvt.isruledebugon(l_simulation_mode) THEN
2872 --call insert run time trace lines
2873
2874 IF l_debug = 1 THEN
2875 log_procedure(l_api_name, 'insert_txn_trace_rows',
2876 'Calling insert_txn_trace_rows ');
2877 END IF;
2878 wms_search_order_globals_pvt.insert_txn_trace_rows(
2879 p_api_version => 1.0
2880 , p_init_msg_list => fnd_api.g_false
2881 , p_validation_level => fnd_api.g_valid_level_full
2882 , x_return_status => l_return_status
2883 , x_msg_count => l_msg_count
2884 , x_msg_data => l_msg_data
2885 , p_txn_header_id => inv_detail_util_pvt.g_transaction_header_id
2886 , p_insert_lot_flag => inv_detail_util_pvt.g_insert_lot_flag
2887 , p_insert_serial_flag => inv_detail_util_pvt.g_insert_serial_flag
2888 );
2889 END IF;
2890
2891 -- Delete records from WMS_TRANSACTIONS_TEMP
2892 purge_detail_temp_records(l_return_status, l_request_line_rec);
2893
2894 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2895 RAISE fnd_api.g_exc_error;
2896 END IF;
2897 --
2898 END IF;
2899
2900 -- 12942776
2901 /* inv_detail_util_pvt.validate_and_init calls
2902 inv_detail_util_pvt.get_request_context which has updated the primary_quantity
2903 based on the quantity, quantity detailed calculations which is not reverted back
2904 after creating suggestion. Reverting the primary quantity back to correct quantity
2905 for inbound mtrls.. */
2906 -- collecting values of current mtrl...
2907 if (inv_cache.set_mol_rec(p_transaction_temp_id)) then
2908 -- collecting values of current item + org combination..
2909 if (inv_cache.set_item_rec(inv_cache.mol_rec.organization_id, inv_cache.mol_rec.inventory_item_id)) then
2910
2911 l_mtrl_pri_qty := inv_convert.inv_um_convert(
2912 inv_cache.mol_rec.inventory_item_id,
2913 5, -- precision
2914 inv_cache.mol_rec.quantity,
2915 inv_cache.mol_rec.uom_code,
2916 inv_cache.item_rec.PRIMARY_UOM_CODE,
2917 NULL,
2918 NULL);
2919
2920
2921 if (abs(inv_cache.mol_rec.primary_quantity - l_mtrl_pri_qty) > 0.00005) then --BUG13591755
2922 update mtl_txn_Request_lines mtrl
2923 set primary_quantity = l_mtrl_pri_qty
2924 where line_id = p_transaction_temp_id
2925 and TRANSACTION_SOURCE_TYPE_ID in (1,7,12); -- only inbound mtrls
2926 end if;
2927 end if;
2928 end if;
2929
2930 -- Standard check of p_commit
2931 IF fnd_api.to_boolean(p_commit) THEN
2932 COMMIT;
2933 END IF;
2934
2935 /*-- debugging section
2936 -- can be commented out for final code
2937 IF inv_pp_debug.is_debug_mode THEN
2938 inv_pp_debug.send_message_to_pipe('exit '|| g_pkg_name || '.' || l_api_name);
2939 END IF; */
2940
2941 IF l_debug = 1 THEN
2942 log_procedure(l_api_name, 'exit' , g_pkg_name || '.' || l_api_name);
2943 END IF;
2944
2945 -- Patchset 'J' : New Error_messages
2946 -- Adding the Suggestion failure message to the message stack
2947 x_msg_data := nvl(WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE, '');
2948
2949 --BUG 3440344: We should not add message to the stack if
2950 --G_SUGG_FAILURE_MESSAGE is null
2951 IF x_msg_data IS NOT NULL OR x_msg_data <> '' THEN
2952 FND_MESSAGE.SET_NAME('WMS',WMS_ENGINE_PVT.G_SUGG_FAILURE_MESSAGE);
2953 FND_MSG_PUB.ADD;
2954 END IF;
2955
2956 IF l_debug = 1 THEN
2957 log_procedure(l_api_name, 'end G_SUGG_FAILURE_MESSAGE', x_msg_data );
2958 log_procedure(l_api_name, 'End', 'End create_suggestions');
2959 END IF;
2960 --
2961 EXCEPTION
2962 WHEN fnd_api.g_exc_error THEN
2963 ROLLBACK TO create_suggestions_sa;
2964 x_return_status := fnd_api.g_ret_sts_error;
2965 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2966 IF l_debug = 1 THEN
2967 log_error(l_api_name, 'error', 'Error in create_suggestions - ' || x_msg_data);
2968 END IF ;
2969 --
2970 WHEN fnd_api.g_exc_unexpected_error THEN
2971 ROLLBACK TO create_suggestions_sa;
2972 x_return_status := fnd_api.g_ret_sts_unexp_error;
2973 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2974 IF l_debug = 1 THEN
2975 log_error(l_api_name, 'unexp_error', 'Unexpected error ' || 'in create_suggestions - ' || x_msg_data);
2976 END IF;
2977 --
2978 WHEN OTHERS THEN
2979 ROLLBACK TO create_suggestions_sa;
2980 x_return_status := fnd_api.g_ret_sts_unexp_error;
2981
2982 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2983 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2984 END IF;
2985 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2986 IF l_debug = 1 THEN
2987 log_error(l_api_name, 'other_error', 'Other error ' || 'in create_suggestions - ' || x_msg_data);
2988 END IF;
2989
2990 END create_suggestions;
2991
2992 --10182554 Function Start
2993 FUNCTION check_exp_lot_txn_allowed (p_mol_id NUMBER)
2994 RETURN BOOLEAN IS
2995
2996 l_allow_expired_lot_txn NUMBER;
2997 l_value_from_user_pkg_lot BOOLEAN := FALSE;
2998
2999 l_organization_id NUMBER;
3000 l_inventory_item_id NUMBER;
3001 l_transaction_source_type_id NUMBER;
3002 l_txn_source_line_id NUMBER;
3003
3004 l_transaction_action_id NUMBER;
3005 l_txn_source_type_id NUMBER;
3006 l_debug PLS_INTEGER;
3007 l_is_item_lot_controlled NUMBER;
3008
3009 l_return_value BOOLEAN;
3010 l_transaction_type_id NUMBER;
3011
3012 BEGIN
3013
3014 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3015
3016 l_debug := g_debug;
3017 l_allow_expired_lot_txn := 0 ; --By Default we would not allow expired lots to be transacted.
3018 --Only some transactions like move order txfer or sub txfer allow exp or custom API lot txn
3019
3020
3021 IF inv_cache.set_mol_rec(p_mol_id) THEN
3022 l_organization_id := inv_cache.mol_rec.organization_id;
3023 l_inventory_item_id := inv_cache.mol_rec.inventory_item_id;
3024 l_transaction_source_type_id := inv_cache.mol_rec.transaction_source_type_id;
3025 l_txn_source_line_id := inv_cache.mol_rec.txn_source_line_id;
3026 l_transaction_type_id := inv_cache.mol_rec.transaction_type_id;
3027 ELSE
3028 RAISE fnd_api.g_exc_unexpected_error;
3029 END IF;
3030
3031 l_return_value := INV_CACHE.set_item_rec(l_organization_id, l_inventory_item_id);
3032
3033 IF (l_return_value) THEN
3034 l_is_item_lot_controlled := NVL(inv_cache.item_rec.lot_control_code,1);
3035 ELSE
3036 RAISE fnd_api.g_exc_unexpected_error;
3037 END IF;
3038
3039 IF(l_debug=1) THEN
3040 inv_log_util.trace( 'Value of organization_id is : ' ||l_organization_id, 'Inside check_exp_lot_txn_allowed ' , 9);
3041 inv_log_util.trace( 'Value of inventory_item_id is : ' ||l_inventory_item_id, 'Inside check_exp_lot_txn_allowed ', 9);
3042 inv_log_util.trace( 'Value of l_transaction_source_type_id is: ' ||l_transaction_source_type_id, 'Inside check_exp_lot_txn_allowed ', 9);
3043 inv_log_util.trace( 'Value of l_txn_source_line_id is : ' ||l_txn_source_line_id, 'Inside check_exp_lot_txn_allowed ' , 9);
3044 inv_log_util.trace( 'Value of l_is_item_lot_controlled is : ' ||l_is_item_lot_controlled, 'Inside check_exp_lot_txn_allowed ', 9);
3045 inv_log_util.trace( 'Value of l_allow_expired_lot_txn is : ' ||l_allow_expired_lot_txn, 'Inside check_exp_lot_txn_allowed ' , 9);
3046 END IF;
3047
3048
3049 IF (l_is_item_lot_controlled = 1) THEN --If the item is not lot controlled then we need to allow transaction it does not matter as its not lot item.
3050 IF(l_debug=1) THEN
3051 inv_log_util.trace('This is not lot controlled item ' ||l_is_item_lot_controlled, 'We dont care as there is no question of lot expiration ' ,9);
3052 END IF;
3053 l_allow_expired_lot_txn := 1;
3054 RETURN TRUE;
3055 END IF;
3056
3057 IF inv_cache.set_mtt_rec(l_transaction_type_id) THEN
3058 l_transaction_action_id := inv_cache.mtt_rec.transaction_action_id;
3059 l_txn_source_type_id := inv_cache.mtt_rec.transaction_source_type_id;
3060 ELSE
3061 RAISE fnd_api.g_exc_unexpected_error;
3062 END IF;
3063
3064 IF(l_debug=1) THEN
3065 inv_log_util.trace( 'Value of l_transaction_type_id : ' ||l_transaction_type_id, 'Inside check_exp_lot_txn_allowed ', 9);
3066 inv_log_util.trace( 'Value of l_transaction_action_id : ' ||l_transaction_action_id, 'Inside check_exp_lot_txn_allowed ', 9);
3067 inv_log_util.trace( 'Value of l_txn_source_type_id : ' ||l_txn_source_type_id, 'Inside check_exp_lot_txn_allowed ', 9);
3068 END IF;
3069
3070 l_value_from_user_pkg_lot := USER_PKG_LOT.use_expired_lots (
3071 p_organization_id => l_organization_id
3072 , p_inventory_item_id => l_inventory_item_id
3073 , p_demand_source_type_id => l_transaction_source_type_id
3074 , p_demand_source_line_id => l_txn_source_line_id
3075 );
3076
3077 --Condition to check user_pkg_lot for expired lot allocation
3078 IF (l_value_from_user_pkg_lot) THEN --IF the use hook returns TRUE means they want expired lots to be allocated
3079
3080 IF(l_debug = 1) THEN
3081 inv_log_util.trace( 'The USER_PKG_LOT returned TRUE and so we will allow expired lots to be transaced::: ' , 'Coming from USER_PKG_LOT.use_expired_lots ', 9);
3082 END IF;
3083 l_allow_expired_lot_txn := 1;
3084 RETURN TRUE;
3085 END IF;
3086
3087 --Condition to allow expired lot allocation in certain transctions like Sub Txer or MO Txfer etc
3088 IF ((l_transaction_action_id = 2 AND l_txn_source_type_id = 13 AND l_transaction_type_id <> 51) OR --Allow expired lot allocation in Sub Txfer but do not allow it for Backflush txfer
3089 (l_transaction_action_id = 2 AND l_txn_source_type_id = 4)) THEN --Allow expired lot allocaiton in MO Txfer
3090 IF(l_debug = 1) THEN
3091 inv_log_util.trace( 'The transaction being done allows expired lots to be transaced action_id ' ||l_transaction_action_id, 'Coming from seeded txn for which expired lot allocation is allowed ', 9);
3092 inv_log_util.trace( 'The transaction being done allows expired lots to be transaced txn_source_type_id ' ||l_transaction_action_id, 'Coming from seeded txn for which expired lot allocation is allowed ', 9);
3093 END IF;
3094 l_allow_expired_lot_txn := 1;
3095 RETURN TRUE;
3096 END IF;
3097
3098 IF(l_debug=1) THEN
3099 inv_log_util.trace('The final value of l_allow_expired_lot_txn' ||l_allow_expired_lot_txn , 'This is a lot controlled item ' , 9);
3100 END IF;
3101
3102
3103 IF l_allow_expired_lot_txn >= 1 THEN
3104 RETURN TRUE;
3105 ELSE
3106 RETURN FALSE;
3107 END IF;
3108
3109 EXCEPTION WHEN OTHERS THEN
3110 RETURN FALSE;
3111 END check_exp_lot_txn_allowed;
3112 -- 10182554 Function End
3113 --
3114 END wms_engine_pvt;
[Home] [Help]