[Home] [Help]
PACKAGE BODY: APPS.INV_DETAIL_UTIL_PVT
Source
1 PACKAGE BODY inv_detail_util_pvt AS
2 /* $Header: INVVDEUB.pls 120.33.12020000.6 2013/02/22 21:49:34 sahmahes ship $ */
3 --
4 -- File : INVVDEUB.pls
5 -- Content : INV_DETAIL_UTIL_PVT package body
6 -- Description : utlitities used by the detailing engine (both inv and wms versions)
7 -- Notes :
8 -- Modified : 10/22/99 bitang created
9 -- Modified : 04/04/2002 grao bug# 228645
10 -- Package name used in error messages
11 --
12 g_pkg_name VARCHAR2(30) := 'INV_DETAIL_UTIL_PVT';
13 g_version_printed BOOLEAN := FALSE;
14 --
15 TYPE g_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
16 --
17 -- The following types are used to define plsql tables for
18 -- inserting into transaction temporary tables
19 TYPE g_mmtt_tbl_type IS TABLE OF mtl_material_transactions_temp%ROWTYPE
20 INDEX BY BINARY_INTEGER;
21 TYPE g_mtlt_tbl_type IS TABLE OF mtl_transaction_lots_temp%ROWTYPE
22 INDEX BY BINARY_INTEGER;
23 TYPE g_msnt_tbl_type IS TABLE OF mtl_serial_numbers_temp%ROWTYPE
24 INDEX BY BINARY_INTEGER;
25 --
26
27 --Cache for function is_sub_loc_lot_trx_allowed
28 g_isllta_subinventory_code VARCHAR2(10);
29 g_isllta_locator_id NUMBER;
30 g_isllta_lot_number VARCHAR2(80);
31 g_lot_return VARCHAR2(1);
32 g_sub_return VARCHAR2(1);
33 g_loc_return VARCHAR2(1);
34 g_isllta_transaction_type_id NUMBER;
35
36 -- Globals added for performance
37 g_transaction_uom_code VARCHAR2(10);
38 g_base_uom_code VARCHAR2(10);
39 g_nl_installed BOOLEAN;
40 -- To preserve consistency reset g_serial_status_enabled to NULL if item or org changes
41 g_organization_id NUMBER;
42 g_inventory_item_id NUMBER;
43 g_serial_status_enabled VARCHAR2(1);
44 g_serial_status NUMBER;
45 g_serial_return VARCHAR2(1);
46 g_transaction_type_id NUMBER;
47 -- Used in get_acct_period
48 g_acct_organization_id NUMBER;
49 g_acct_period_id NUMBER;
50 g_debug NUMBER;
51 g_conc_request_id number := FND_GLOBAL.CONC_REQUEST_ID;
52 g_conc_program boolean;
53
54
55 PROCEDURE print_debug( p_message VARCHAR2, p_level NUMBER := 9 ) IS
56 BEGIN
57
58 IF (g_conc_program is null) or (g_debug is null) then
59 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
60 if (g_conc_request_id > 0) then
61 g_conc_program := TRUE;
62 end if;
63 END IF;
64
65 IF g_debug = 1 THEN
66 --dbms_output.put_line(p_message);
67 IF NOT g_version_printed THEN
68 inv_log_util.trace('$Header: INVVDEUB.pls 120.33.12020000.6 2013/02/22 21:49:34 sahmahes ship $', g_pkg_name, 1);
69 g_version_printed := TRUE;
70 END IF;
71 inv_log_util.trace(
72 p_message => p_message
73 , p_module => g_pkg_name
74 , p_level => p_level);
75
76 gmi_reservation_util.println(p_message);
77 END IF;
78 END print_debug;
79
80 -- find lot expiration date, and if not found, return null
81 FUNCTION get_lot_expiration_date
82 (p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER,
83 p_lot_number IN VARCHAR2)
84 RETURN DATE IS
85 --
86 CURSOR l_cur IS
87 SELECT expiration_date
88 FROM mtl_lot_numbers
89 WHERE inventory_item_id = p_inventory_item_id
90 AND organization_id = p_organization_id
91 AND lot_number = p_lot_number;
92 --
93 l_date DATE;
94 BEGIN
95 OPEN l_cur;
96 FETCH l_cur INTO l_date;
97 IF l_cur%notfound THEN
98 l_date := NULL;
99 END IF;
100 CLOSE l_cur;
101 RETURN l_date;
102 END get_lot_expiration_date;
103 --
104 -- read the request record into package variable and
105 -- initialize x_request_context
106 PROCEDURE get_request_context
107 (x_return_status OUT NOCOPY VARCHAR2 ,
108 p_move_order_line_id IN NUMBER ,
109 x_request_context OUT NOCOPY g_request_context_rec_type,
110 x_request_line_rec OUT NOCOPY g_request_line_rec_type,
111 p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
112 ) IS
113 l_api_name VARCHAR2(30) := 'Get_Request_Context';
114 l_allocate_serial_flag VARCHAR2(1);
115 l_quantity_to_detail NUMBER;
116 l_secondary_quantity_to_detail NUMBER; /* Bug 9172258 */
117
118 CURSOR l_req_csr IS
119 SELECT *
120 FROM mtl_txn_request_lines
121 WHERE line_id = p_move_order_line_id FOR UPDATE nowait;
122 --
123 l_primary_quantity NUMBER;
124 l_txn_type_id NUMBER;
125 l_inventory_item_id NUMBER;
126 l_organization_id NUMBER;
127 l_end_assembly_pegging_flag VARCHAR2(1);
128 --
129 /* CURSOR l_context_csr IS
130 SELECT
131 mtt.transaction_action_id
132 ,mtt.transaction_source_type_id
133 ,msi.primary_uom_code
134 ,msi.revision_qty_control_code
135 ,msi.lot_control_code
136 ,msi.serial_number_control_code
137 ,msi.location_control_code
138 ,mp.stock_locator_control_code
139 ,msi.unit_volume
140 ,msi.volume_uom_code
141 ,msi.unit_weight
142 ,msi.weight_uom_code
143 ,msi.reservable_type
144 ,NVL(msi.end_assembly_pegging_flag,'N')
145 ,mp.allocate_serial_flag
146 FROM mtl_transaction_types mtt
147 ,mtl_system_items msi
148 ,mtl_parameters mp
149 WHERE mtt.transaction_type_id = l_txn_type_id
150 AND msi.inventory_item_id = l_inventory_item_id
151 AND msi.organization_id = l_organization_id
152 AND mp.organization_id = l_organization_id;
153 --
154 */
155 CURSOR l_base_uom IS
156 SELECT muom.uom_code
157 FROM mtl_units_of_measure_tl muom,mtl_units_of_measure_tl muom2
158 WHERE muom2.uom_code = x_request_context.transaction_uom_code
159 AND muom2.language = userenv('LANG')
160 AND muom.uom_class = muom2.uom_class
161 AND muom.language = userenv('LANG')
162 AND muom.base_uom_flag = 'Y';
163
164
165 --
166 CURSOR l_ship_info_csr IS
167 SELECT
168 wdd.source_header_id oe_header_id,
169 wdd.source_line_id oe_line_id,
170 NULL,
171 wdd.customer_id,
172 NULL,
173 wdd.ship_to_location_id ship_to_location,
174 NULL,
175 wc.freight_code -- Bug Fix 5594517
176 FROM wsh_delivery_details wdd,
177 wsh_carriers wc,
178 wsh_carrier_services wcs
179 WHERE wdd.move_order_line_id = p_move_order_line_id
180 AND wdd.move_order_line_id is NOT NULL
181 AND wdd.ship_method_code = wcs.ship_method_code (+)
182 AND wcs.carrier_id = wc.carrier_id (+);
183
184 CURSOR l_order_info_csr (p_src_line_id IN NUMBER) IS
185 SELECT oedtl.header_id oe_header_id,
186 oedtl.line_id oe_line_id,
187 NULL,
188 oedtl.sold_to_org_id, -- customer_id
189 NULL,
190 NULL,
191 NULL,
192 oedtl.freight_carrier_code
193 FROM oe_order_lines_all oedtl
194 WHERE oedtl.line_id = p_src_line_id;
195
196 --Bug #4598134 - Replace ra_customers with TCA entities
197 CURSOR l_rma_info_csr IS
198 SELECT
199 oola.header_id
200 ,oola.line_id
201 ,NULL
202 ,oola.sold_to_org_id
203 --,rc.customer_number
204 ,party.party_number
205 ,NULL
206 ,oola.shipment_number
207 ,oola.freight_carrier_code
208 FROM oe_order_lines_all oola
209 , hz_parties party
210 , hz_cust_accounts cust_acct
211 WHERE oola.line_id = x_request_line_rec.reference_id
212 AND cust_acct.cust_account_id = oola.sold_to_org_id
213 AND cust_acct.party_id = party.party_id;
214
215 BEGIN
216 --
217 -- debugging section
218 -- can be commented ut for final code
219 IF inv_pp_debug.is_debug_mode THEN
220 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
221 END IF;
222 -- end of debugging section
223 --
224 x_return_status := fnd_api.g_ret_sts_success;
225 -- get the request record
226 print_debug('in context ');
227 OPEN l_req_csr;
228 BEGIN
229 FETCH l_req_csr INTO x_request_line_rec;
230 EXCEPTION
231 WHEN timeout_on_resource THEN
232 --
233 -- debugging section
234 -- can be commented ut for final code
235 IF inv_pp_debug.is_debug_mode THEN
236 inv_pp_debug.send_message_to_pipe('can not lock the move order line record');
237 END IF;
238 -- end of debugging section
239 --
240 RAISE timeout_on_resource;
241 --
242 END;
243 print_debug('after fething req line');
244 IF l_req_csr%notfound THEN
245 print_debug('mo line not found ');
246 IF inv_pp_debug.is_debug_mode THEN
247 inv_pp_debug.send_message_to_pipe('mo line not found');
248 END IF;
249 fnd_message.set_name('INV','INV_PP_INPUT_LINE_NOTFOUND');
250 fnd_message.set_token
251 ('LINE_ID',fnd_number.number_to_canonical(p_move_order_line_id));
252 fnd_msg_pub.ADD;
253 CLOSE l_req_csr;
254 RAISE fnd_api.g_exc_error;
255 END IF;
256 CLOSE l_req_csr;
257 print_debug('init request_context');
258 -- initialize x_request_context
259 l_primary_quantity := x_request_line_rec.primary_quantity;
260 l_txn_type_id := x_request_line_rec.transaction_type_id;
261 l_inventory_item_id := x_request_line_rec.inventory_item_id;
262 l_organization_id := x_request_line_rec.organization_id;
263 If (inv_cache.set_mtt_rec(l_txn_type_id) AND
264 inv_cache.set_item_rec(l_organization_id, l_inventory_item_id) AND
265 inv_cache.set_org_rec(l_organization_id)) THEN
266
267 print_debug('initing request_context ');
268 x_request_context.transaction_action_id := inv_cache.mtt_rec.transaction_action_id;
269 x_request_context.transaction_source_type_id := inv_cache.mtt_rec.transaction_source_type_id;
270 x_request_context.primary_uom_code := inv_cache.item_rec.primary_uom_code;
271 x_request_context.secondary_uom_code := inv_cache.item_rec.secondary_uom_code;
272 x_request_context.item_revision_control := inv_cache.item_rec.revision_qty_control_code;
273 x_request_context.item_lot_control_code := inv_cache.item_rec.lot_control_code;
274 x_request_context.item_serial_control_code := inv_cache.item_rec.serial_number_control_code;
275 x_request_context.item_locator_control_code := inv_cache.item_rec.location_control_code;
276 x_request_context.org_locator_control_code := inv_cache.org_rec.stock_locator_control_code;
277 x_request_context.unit_volume := inv_cache.item_rec.unit_volume;
278 x_request_context.volume_uom_code := inv_cache.item_rec.volume_uom_code;
279 x_request_context.unit_weight := inv_cache.item_rec.unit_weight;
280 x_request_context.weight_uom_code := inv_cache.item_rec.weight_uom_code;
281 x_request_context.item_reservable_type := inv_cache.item_rec.reservable_type;
282 l_end_assembly_pegging_flag := NVL(inv_cache.item_rec.end_assembly_pegging_flag,'N');
283 l_allocate_serial_flag := inv_cache.org_rec.allocate_serial_flag;
284 ELSE
285 print_debug('init request_context no data found');
286 IF inv_pp_debug.is_debug_mode THEN
287 inv_pp_debug.send_message_to_pipe('mo context not found');
288 END IF;
289 RAISE no_data_found;
290 END IF;
291 print_debug('after init request_context ');
292
293 IF ( l_allocate_serial_flag <> 'N' ) THEN
294 x_request_context.detail_any_serial := 1;
295 ELSE
296 x_request_context.detail_any_serial := 2;
297 END IF;
298
299 --commented out 2/6/03
300 --bug 2778814
301 --We now need to know if a item is serial controlled at issue, since it
302 -- affects WMS putaway. Move this logic into INVRSV4B.pls and WMSVPPEB.pls
303 --IF x_request_context.item_serial_control_code = 6 THEN
304 -- -- dynamic entry at sales order issue
305 -- x_request_context.item_serial_control_code := 1; -- No serial control
306 --END IF;
307 --
308 IF x_request_context.item_lot_control_code = 2
309 AND x_request_line_rec.lot_number IS NOT NULL THEN
310 x_request_context.lot_expiration_date := get_lot_expiration_date
311 (l_organization_id
312 , l_inventory_item_id
313 , x_request_line_rec.lot_number
314 );
315 END IF;
316 IF x_request_context.transaction_action_id IN (1,2,28,3,21,29,32,34) THEN
317 x_request_context.type_code := 2; -- picking or transfer
318 ELSE
319 x_request_context.type_code := 1; -- put away
320 END IF;
321 IF x_request_context.transaction_action_id IN (2,28,3) THEN
322 x_request_context.transfer_flag := TRUE;
323 ELSE
324 x_request_context.transfer_flag := FALSE;
325 END IF;
326 --by default, set posting flag to Y (only set to No in WMS for
327 -- put away move orders)
328 if p_wave_simulation_mode = 'Y' then
329 x_request_context.posting_flag := 'N';
330 else
331 x_request_context.posting_flag := 'Y';
332 end if;
333 x_request_context.transaction_uom_code :=
334 x_request_line_rec.uom_code;
335
336 print_debug('after flags ');
337 IF NVL(g_transaction_uom_code,'@@@') <> x_request_context.transaction_uom_code THEN
338 OPEN l_base_uom;
339 FETCH l_base_uom INTO g_base_uom_code;
340 IF l_base_uom%NOTFOUND THEN
341 g_base_uom_code := NULL;
342 END IF;
343 CLOSE l_base_uom;
344 g_transaction_uom_code := x_request_context.transaction_uom_code;
345 END IF;
346 x_request_context.base_uom_code := g_base_uom_code;
347
348
349 -- compute quantity to detail in primary uom
350 IF x_request_line_rec.quantity_detailed IS NULL THEN
351 x_request_line_rec.quantity_detailed := 0;
352 END IF;
353 IF x_request_line_rec.quantity_delivered IS NULL THEN
354 x_request_line_rec.quantity_delivered := 0;
355 END IF;
356 --compute quantity the rules engine should allocate
357 --First, if the required quantity is less than quantity, use
358 -- the required quantity as the new base. Then the total allocations
359 -- and already delivered plus the current allocation should not exceed
360 -- the new base quantity. Because of overpicking, the quantity
361 -- delivered can sometimes exceed the quantity allocated. We should
362 -- take this into account
363
364 l_quantity_to_detail := x_request_line_rec.quantity;
365
366 /* Start Bug 9172258 */
367 print_debug('inv_cache.item_rec.tracking_quantity_ind '||inv_cache.item_rec.tracking_quantity_ind);
368 print_debug('(1)x_request_line_rec.secondary_quantity '||x_request_line_rec.secondary_quantity);
369 IF (inv_cache.item_rec.tracking_quantity_ind = 'PS') THEN
370 -- this item is dual UOM
371 l_secondary_quantity_to_detail := x_request_line_rec.secondary_quantity;
372 END IF;
373 /* End Bug 9172258 */
374
375 IF x_request_line_rec.required_quantity IS NOT NULL AND
376 x_request_line_rec.required_quantity < l_quantity_to_detail THEN
377
378 l_quantity_to_detail := x_request_line_rec.required_quantity;
379 /* Start Bug 9172258 */
380 IF (inv_cache.item_rec.tracking_quantity_ind = 'PS') THEN
381 -- this item is dual UOM
382 l_secondary_quantity_to_detail := x_request_line_rec.secondary_required_quantity;
383 END IF;
384 /* End Bug 9172258 */
385 END IF;
386
387 print_debug(' Debug l_quantity_to_detail is'||l_quantity_to_detail);
388 print_debug(' Debug x_request_line_rec.quantity_detailed is'||x_request_line_rec.quantity_detailed);
389 print_debug(' Debug x_request_line_rec.quantity_delivered is'||x_request_line_rec.quantity_delivered);
390 print_debug(' Debug x_request_line_rec.primary_quantity is'||x_request_line_rec.primary_quantity);
391 print_debug(' Debug x_request_context.primary_uom_code is'||x_request_context.primary_uom_code);
392 print_debug(' Debug x_request_context.transaction_uom_code is'||x_request_context.transaction_uom_code);
393 print_debug(' Debug x_request_line_rec.QUANTITY is'||x_request_line_rec.QUANTITY);
394
395
396 l_quantity_to_detail := l_quantity_to_detail -
397 greatest(x_request_line_rec.quantity_detailed,
398 x_request_line_rec.quantity_delivered);
399
400 IF x_request_context.transaction_uom_code <>
401 X_Request_Context.Primary_Uom_Code Then
402 If (L_Quantity_To_Detail = X_Request_Line_Rec.Quantity ) Then --if detailing full, no need of calculating pri qty
403 null;
404 else
405 x_request_line_rec.primary_quantity :=
406 inv_convert.inv_um_convert
407 (
408 x_request_line_rec.inventory_item_id,
409 NULL,
410 l_quantity_to_detail,
411 x_request_context.transaction_uom_code,
412 x_request_context.primary_uom_code,
413 NULL,
414 Null);
415 end if;
416 ELSE
417 x_request_line_rec.primary_quantity := l_quantity_to_detail;
418 END IF;
419
420 /* Start Bug 9172258 */
421 print_debug('l_secondary_quantity_to_detail '||l_secondary_quantity_to_detail);
422 print_debug('x_request_line_rec.secondary_quantity_detailed '||x_request_line_rec.secondary_quantity_detailed);
423 print_debug('x_request_line_rec.secondary_quantity_delivered '||x_request_line_rec.secondary_quantity_delivered);
424 IF (inv_cache.item_rec.tracking_quantity_ind = 'PS') THEN
425 -- this item is dual UOM
426 x_request_line_rec.secondary_quantity := l_secondary_quantity_to_detail -
427 greatest(NVL(x_request_line_rec.secondary_quantity_detailed ,0),
428 NVL(x_request_line_rec.secondary_quantity_delivered,0));
429 END IF;
430 print_debug('(2)x_request_line_rec.secondary_quantity '||x_request_line_rec.secondary_quantity);
431 /* End Bug 9172258 */
432
433 -- bug 5677255, keep the source_type_id for reservations, not overriding
434 If x_request_line_rec.reference = 'ORDER_LINE_ID_RSV' Then
435 null;
436 Else
437 x_request_line_rec.transaction_source_type_id := x_request_context.transaction_source_type_id;
438 End if;
439 --
440 --for put away (but not transfer), copy organization_id into
441 -- to_organization_id
442 if (((x_request_context.type_code = 1) OR
443 (x_request_context.transfer_flag = TRUE)) AND
444 x_request_line_rec.to_organization_id IS NULL) THEN
445
446 x_request_line_rec.to_organization_id := x_request_line_rec.organization_id;
447 END IF;
448 x_request_context.pick_strategy_id :=
449 x_request_line_rec.pick_strategy_id;
450 x_request_context.put_away_strategy_id :=
451 x_request_line_rec.put_away_strategy_id;
452 x_request_context.wms_task_type := NULL;
453 x_request_context.end_assembly_pegging_code := 0;
454
455 IF x_request_line_rec.transaction_source_type_id IN (2,8) THEN -- Order Entry
456 OPEN l_ship_info_csr;
457 FETCH l_ship_info_csr INTO
458 x_request_context.txn_header_id,
459 x_request_context.txn_line_id,
460 x_request_context.txn_line_detail,
461 x_request_context.customer_id,
462 x_request_context.customer_number,
463 x_request_context.ship_to_location,
464 x_request_context.shipment_number,
465 x_request_context.freight_code;
466 IF l_ship_info_csr%notfound THEN
467 IF inv_pp_debug.is_debug_mode THEN
468 inv_pp_debug.send_message_to_pipe('mo shipping not found');
469 inv_pp_debug.send_message_to_pipe('trans source type:' || x_request_line_rec.transaction_source_type_id);
470 END IF;
471 CLOSE l_ship_info_csr;
472 -- bug 5677255, keep the source_type_id for reservations
473 If x_request_line_rec.reference = 'ORDER_LINE_ID_RSV' Then
474 -- fetch info from order line
475 Open l_order_info_csr(x_request_line_rec.txn_source_line_id) ;
476 Fetch l_order_info_csr INTO
477 x_request_context.txn_header_id,
478 x_request_context.txn_line_id,
479 x_request_context.txn_line_detail,
480 x_request_context.customer_id,
481 x_request_context.customer_number,
482 x_request_context.ship_to_location,
483 x_request_context.shipment_number,
484 x_request_context.freight_code;
485 IF l_ship_info_csr%notfound THEN
486 IF inv_pp_debug.is_debug_mode THEN
487 inv_pp_debug.send_message_to_pipe('reservation mo order line not found');
488 inv_pp_debug.send_message_to_pipe('trans source type:' || x_request_line_rec.transaction_source_type_id);
489 END IF;
490 CLOSE l_order_info_csr;
491 RAISE no_data_found;
492 End if;
493 CLOSE l_order_info_csr;
494 null;
495 Else
496 RAISE no_data_found;
497 End if;
498 END IF;
499 CLOSE l_ship_info_csr;
500 -- using mso header id as the demand source header id
501 x_request_context.txn_header_id :=
502 inv_salesorder.get_salesorder_for_oeheader(x_request_context.txn_header_id);
503 IF x_request_context.txn_header_id IS NULL THEN
504 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
505 FND_MSG_PUB.Add;
506 RAISE fnd_api.g_exc_unexpected_error;
507 END IF;
508 --
509 --bug 1248138
510 --not sure why this is happening, but it probably shouldn't be, so
511 -- i'm commenting it out.
512 /*
513 UPDATE mtl_txn_request_lines
514 SET txn_source_id = header_id
515 WHERE line_id = p_move_order_line_id;
516 */
517 ELSIF x_request_line_rec.transaction_source_type_id = 12 THEN --RMA
518 OPEN l_rma_info_csr;
519 FETCH l_rma_info_csr INTO
520 x_request_context.txn_header_id,
521 x_request_context.txn_line_id,
522 x_request_context.txn_line_detail,
523 x_request_context.customer_id,
524 x_request_context.customer_number,
525 x_request_context.ship_to_location,
526 x_request_context.shipment_number,
527 x_request_context.freight_code;
528 IF l_rma_info_csr%notfound THEN
529 IF inv_pp_debug.is_debug_mode THEN
530 inv_pp_debug.send_message_to_pipe('rma info not found');
531 inv_pp_debug.send_message_to_pipe('trans source type:' || x_request_line_rec.transaction_source_type_id);
532 END IF;
533 CLOSE l_rma_info_csr;
534 RAISE no_data_found;
535 END IF;
536 CLOSE l_rma_info_csr;
537
538 --For a putaway move order created for an RMA Receipt, the txn_source_id
539 --column of the move order line should point to the parent
540 --record in RCV_TRANSACTIONS and should not be overridden
541 IF (x_request_context.transaction_action_id = 27) THEN
542 x_request_context.txn_header_id := x_request_line_rec.txn_source_id;
543 x_request_context.txn_line_id := x_request_line_rec.txn_source_line_id;
544 ELSE
545 -- using mso header id as the demand source header id
546 x_request_context.txn_header_id :=
547 inv_salesorder.get_salesorder_for_oeheader(x_request_context.txn_header_id);
548 IF x_request_context.txn_header_id IS NULL THEN
549 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
550 FND_MSG_PUB.Add;
551 RAISE fnd_api.g_exc_unexpected_error;
552 END IF;
553 END IF;
554
555 -- Bug 2027368
556 -- Because WIP move orders for backflush replenish have txn source
557 -- type of 13, we can't check txn source type here. Instead, we
558 -- look at txn type.
559 ELSIF x_request_line_rec.transaction_source_type_Id = 5 OR
560 x_request_line_rec.transaction_type_id IN (35,51) THEN -- WIP
561 x_request_context.txn_header_id := x_request_line_rec.txn_source_id;
562 x_request_context.txn_line_id :=
563 x_request_line_rec.txn_source_line_id;
564 /* BUG 4737839 - done below
565 --check whether item is pegged. This affects which material we allocate
566 -- in INV detailing.
567 If l_end_assembly_pegging_flag IN ('A','Y','B') Then
568 --soft pegging
569 x_request_context.end_assembly_pegging_code:= 1;
570 Elsif l_end_assembly_pegging_flag IN ('I', 'X') Then
571 --hard pegging
572 x_request_context.end_assembly_pegging_code:= 2;
573 End If; --for all others, code is 0 (no pegging) */
574
575 --For a putaway move order created for an PO, Int ship or Int Req receipt
576 --the txn_source_id column of the move order line should point to the parent
577 --record in RCV_TRANSACTIONS and should not be overridden
578 ELSIF(
579 (x_request_context.transaction_source_type_id = 1)
580 OR(
581 (
582 x_request_context.transaction_source_type_id = 13
583 OR x_request_context.transaction_source_type_id = 7
584 )
585 AND(x_request_context.transaction_action_id = 12)
586 )
587 ) THEN
588 x_request_context.txn_header_id := x_request_line_rec.txn_source_id;
589 x_request_context.txn_line_id := x_request_line_rec.txn_source_line_id;
590 ELSE -- for all other transaction source types,
591 --use the move order header/line as demand source header/line
592 x_request_context.txn_header_id := x_request_line_rec.header_id;
593 x_request_context.txn_line_id := x_request_line_rec.line_id;
594 END IF;
595
596 --BUG 4737839 : setting the assembly pegging flag should be done whatever the transaction source
597 --check whether item is pegged. This affects which material we allocate
598 -- in INV detailing.
599 If l_end_assembly_pegging_flag IN ('A','Y','B') Then
600 --soft pegging
601 x_request_context.end_assembly_pegging_code:= 1;
602 Elsif l_end_assembly_pegging_flag IN ('I', 'X') Then
603 --hard pegging
604 x_request_context.end_assembly_pegging_code:= 2;
605 End If; --for all others, code is 0 (no pegging)
606
607 -- ugly, but we need to do this before the strategy search
608 -- since users might have defined rules to use the primary_quantity in
609 -- mtl_pp_strategy_mat_txn_tmp_v which is mapped to the move order line
610 -- primary_quantity
611
612 -- If data is changed then do update. Performance Improvement
613 IF ((l_primary_quantity <> x_request_line_rec.primary_quantity) OR
614 (x_request_context.transaction_source_type_id <> x_request_line_rec.transaction_source_type_id) OR
615 (x_request_context.txn_header_id <> x_request_line_rec.txn_source_id) OR
616 (x_request_context.txn_line_id <> x_request_line_rec.txn_source_line_id)) THEN
617 UPDATE mtl_txn_request_lines SET
618 primary_quantity = x_request_line_rec.primary_quantity,
619 transaction_source_type_id = x_request_context.transaction_source_type_id,
620 txn_source_id = x_request_context.txn_header_id,
621 txn_source_line_id = x_request_context.txn_line_id
622 WHERE line_id = x_request_line_rec.line_id
623 ;
624 END IF;
625
626 --
627 -- debugging section
628 -- can be commented ut for final code
629 IF inv_pp_debug.is_debug_mode THEN
630 inv_pp_debug.send_message_to_pipe
631 ('======== Move Order Line Information ========');
632 inv_pp_debug.send_message_to_pipe('line_id '
633 || x_request_line_rec.line_id);
634 inv_pp_debug.send_message_to_pipe('header_id '
635 || x_request_line_rec.header_id);
636 inv_pp_debug.send_message_to_pipe('line_number '
637 || x_request_line_rec.line_number);
638 inv_pp_debug.send_message_to_pipe('organization_id '
639 || x_request_line_rec.organization_id);
640 inv_pp_debug.send_message_to_pipe('inventory_item_id '
641 || x_request_line_rec.inventory_item_id);
642 inv_pp_debug.send_message_to_pipe('revision '
643 || x_request_line_rec.revision);
644 inv_pp_debug.send_message_to_pipe('from_subinventory_id '
645 || x_request_line_rec.from_subinventory_id);
646 inv_pp_debug.send_message_to_pipe('from_subinventory_code '
647 || x_request_line_rec.from_subinventory_code);
648 inv_pp_debug.send_message_to_pipe('from_locator_id '
649 || x_request_line_rec.from_locator_id);
650 inv_pp_debug.send_message_to_pipe('to_subinventory_code '
651 || x_request_line_rec.to_subinventory_code);
652 inv_pp_debug.send_message_to_pipe('to_subinventory_id '
653 || x_request_line_rec.to_subinventory_id);
654 inv_pp_debug.send_message_to_pipe('to_locator_id '
655 || x_request_line_rec.to_locator_id);
656 inv_pp_debug.send_message_to_pipe('to_account_id '
657 || x_request_line_rec.to_account_id);
658 inv_pp_debug.send_message_to_pipe('lot_number '
659 || x_request_line_rec.lot_number);
660 inv_pp_debug.send_message_to_pipe('serial_number_start '
661 || x_request_line_rec.serial_number_start);
662 inv_pp_debug.send_message_to_pipe('serial_number_end '
663 || x_request_line_rec.serial_number_end);
664 inv_pp_debug.send_message_to_pipe('uom_code '
665 || x_request_line_rec.uom_code);
666 inv_pp_debug.send_message_to_pipe('quantity '
667 || x_request_line_rec.quantity);
668 inv_pp_debug.send_message_to_pipe('quantity_delivered '
669 || x_request_line_rec.quantity_delivered);
670 inv_pp_debug.send_message_to_pipe('quantity_detailed '
671 || x_request_line_rec.quantity_detailed);
672 inv_pp_debug.send_message_to_pipe('date_required '
673 || x_request_line_rec.date_required);
674 inv_pp_debug.send_message_to_pipe('reason_id '
675 || x_request_line_rec.reason_id);
676 inv_pp_debug.send_message_to_pipe('reference '
677 || x_request_line_rec.reference);
678 inv_pp_debug.send_message_to_pipe('reference_type_code '
679 || x_request_line_rec.reference_type_code);
680 inv_pp_debug.send_message_to_pipe('reference_id '
681 || x_request_line_rec.reference_id);
682 inv_pp_debug.send_message_to_pipe('project_id '
683 || x_request_line_rec.project_id);
684 inv_pp_debug.send_message_to_pipe('task_id '
685 || x_request_line_rec.task_id);
686 inv_pp_debug.send_message_to_pipe('transaction_header_id '
687 || x_request_line_rec.transaction_header_id);
688 inv_pp_debug.send_message_to_pipe('line_status '
689 || x_request_line_rec.line_status);
690 inv_pp_debug.send_message_to_pipe('status_date '
691 || x_request_line_rec.status_date);
692 inv_pp_debug.send_message_to_pipe('txn_source_id '
693 || x_request_line_rec.txn_source_id);
694 inv_pp_debug.send_message_to_pipe('txn_source_line_id '
695 || x_request_line_rec.txn_source_line_id);
696 inv_pp_debug.send_message_to_pipe('txn_source_line_detail_id '
697 || x_request_line_rec.txn_source_line_detail_id);
698 inv_pp_debug.send_message_to_pipe('transaction_type_id '
699 || x_request_line_rec.transaction_type_id);
700 inv_pp_debug.send_message_to_pipe('transaction_source_type_id '
701 || x_request_line_rec.transaction_source_type_id);
702 inv_pp_debug.send_message_to_pipe('primary_quantity '
703 || x_request_line_rec.primary_quantity);
704 inv_pp_debug.send_message_to_pipe('to_organization_id '
705 || x_request_line_rec.to_organization_id);
706 inv_pp_debug.send_message_to_pipe('put_away_strategy_id '
707 || x_request_line_rec.put_away_strategy_id);
708 inv_pp_debug.send_message_to_pipe('pick_strategy_id '
709 || x_request_line_rec.pick_strategy_id);
710 inv_pp_debug.send_message_to_pipe('unit_number '
711 || x_request_line_rec.unit_number);
712 inv_pp_debug.send_message_to_pipe
713 ('======== Request Context ========');
714 inv_pp_debug.send_message_to_pipe
715 ('type_code = '|| x_request_context.type_code);
716 IF x_request_context.transfer_flag THEN
717 inv_pp_debug.send_message_to_pipe('transfer_flag = true');
718 ELSE
719 inv_pp_debug.send_message_to_pipe('transfer_flag = false');
720 END IF;
721 inv_pp_debug.send_message_to_pipe
722 ('transaction_action_id = '|| x_request_context.transaction_action_id);
723 inv_pp_debug.send_message_to_pipe
724 ('item_revision_control = '|| x_request_context.item_revision_control);
725 inv_pp_debug.send_message_to_pipe
726 ('item_lot_control_code = '|| x_request_context.item_lot_control_code);
727 inv_pp_debug.send_message_to_pipe
728 ('item_serial_control_code = '|| x_request_context.item_serial_control_code);
729 inv_pp_debug.send_message_to_pipe
730 ('lot_expiration_date = '|| x_request_context.lot_expiration_date);
731 inv_pp_debug.send_message_to_pipe
732 ('primary_uom_code = '|| x_request_context.primary_uom_code);
733 inv_pp_debug.send_message_to_pipe
734 ('transaction_uom_code = '|| x_request_context.transaction_uom_code);
735 inv_pp_debug.send_message_to_pipe
736 ('pick_strategy_id = '|| x_request_context.pick_strategy_id);
737 inv_pp_debug.send_message_to_pipe
738 ('put_away_strategy_id = '|| x_request_context.put_away_strategy_id);
739 inv_pp_debug.send_message_to_pipe
740 ('txn_header_id = '|| x_request_context.txn_header_id);
741 inv_pp_debug.send_message_to_pipe
742 ('txn_line_id = '|| x_request_context.txn_line_id);
743 inv_pp_debug.send_message_to_pipe
744 ('txn_line_detail = '|| x_request_context.txn_line_detail);
745 inv_pp_debug.send_message_to_pipe
746 ('customer_id = '|| x_request_context.customer_id);
747 inv_pp_debug.send_message_to_pipe
748 ('customer_number = '|| x_request_context.customer_number);
749 inv_pp_debug.send_message_to_pipe
750 ('ship_to_location = '|| x_request_context.ship_to_location);
751 inv_pp_debug.send_message_to_pipe
752 ('shipment_number = '|| x_request_context.shipment_number);
753 inv_pp_debug.send_message_to_pipe
754 ('freight_code = '|| x_request_context.freight_code);
755 --
756 inv_pp_debug.send_message_to_pipe
757 ('exit '||g_pkg_name||'.'||l_api_name);
758 END IF;
759 -- end of debugging section
760 --
761 EXCEPTION
762 WHEN fnd_api.g_exc_error THEN
763 --
764 -- debugging section
765 -- can be commented ut for final code
766 IF inv_pp_debug.is_debug_mode THEN
767 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
768 -- the message retrieved here since it is no longer on the stack
769 inv_pp_debug.set_last_error_message(Sqlerrm);
770 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
771 inv_pp_debug.send_last_error_message;
772 END IF;
773 -- end of debugging section
774 --
775 x_return_status := fnd_api.g_ret_sts_error;
776 IF l_req_csr%isopen THEN
777 CLOSE l_req_csr;
778 END IF;
779 /*IF l_context_csr%isopen THEN
780 CLOSE l_context_csr;
781 END IF;*/
782 IF l_ship_info_csr%isopen THEN
783 CLOSE l_ship_info_csr;
784 END IF;
785 --
786 WHEN fnd_api.g_exc_unexpected_error THEN
787 --
788 -- debugging section
789 -- can be commented ut for final code
790 IF inv_pp_debug.is_debug_mode THEN
791 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
792 -- the message retrieved here since it is no longer on the stack
793 inv_pp_debug.set_last_error_message(Sqlerrm);
794 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
795 inv_pp_debug.send_last_error_message;
796 END IF;
797 -- end of debugging section
798 --
799 x_return_status := fnd_api.g_ret_sts_unexp_error;
800 IF l_req_csr%isopen THEN
801 CLOSE l_req_csr;
802 END IF;
803 /*IF l_context_csr%isopen THEN
804 CLOSE l_context_csr;
805 END IF;*/
806 IF l_ship_info_csr%isopen THEN
807 CLOSE l_ship_info_csr;
808 END IF;
809 --
810 WHEN OTHERS THEN
811 --
812 -- debugging section
813 -- can be commented ut for final code
814 IF inv_pp_debug.is_debug_mode THEN
815 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
816 -- the message retrieved here since it is no longer on the stack
817 inv_pp_debug.set_last_error_message(Sqlerrm);
818 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
819 inv_pp_debug.send_last_error_message;
820 END IF;
821 -- end of debugging section
822 --
823 x_return_status := fnd_api.g_ret_sts_unexp_error;
824 IF l_req_csr%isopen THEN
825 CLOSE l_req_csr;
826 END IF;
827 /*IF l_context_csr%isopen THEN
828 CLOSE l_context_csr;
829 END IF; */
830 IF l_ship_info_csr%isopen THEN
831 CLOSE l_ship_info_csr;
832 END IF;
833 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
834 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
835 END IF;
836 END get_request_context;
837 --
838 -- compute picking detailing levels based on the move order line and
839 -- reservations
840 -- Added x_remaining_quantity as part of the bug fix for 2286454 and initilized with l_remain_pri_qty
841 PROCEDURE compute_pick_detail_level
842 ( x_return_status OUT NOCOPY VARCHAR2
843 , p_request_line_rec IN g_request_line_rec_type
844 , p_request_context IN g_request_context_rec_type
845 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
846 , x_detail_level_tbl IN OUT nocopy g_detail_level_tbl_type
847 , x_detail_level_tbl_size OUT NOCOPY NUMBER
848 , x_remaining_quantity OUT NOCOPY NUMBER
849 , x_remaining_sec_qty OUT NOCOPY NUMBER
850 )
851 IS
852 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
853 l_api_name VARCHAR2(30) := 'compute_pick_detail_level';
854 l_remain_pri_qty NUMBER;
855 l_remain_sec_qty NUMBER;
856 l_pp_temp_qty NUMBER;
857 l_sec_pp_temp_qty NUMBER;
858 l_reserved_qty NUMBER;
859 l_sec_reserved_qty NUMBER;
860 l_res_index NUMBER;
861 l_index NUMBER := 0;
862 -- l_other_wdds_count NUMBER; --BUG14014540/14061007/14240024/12781502 -- Commented for Bug 14170648
863 BEGIN
864 IF inv_pp_debug.is_debug_mode THEN
865 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
866 END IF;
867 -- split the picking request into multiple records
868 -- based on reservations
869 --
870 -- store total quantity to split in l_remain_txn_qty
871 l_remain_pri_qty := p_request_line_rec.primary_quantity; -- Changed for Bug 14170648
872 l_remain_sec_qty := p_request_line_rec.secondary_quantity; -- Changed for Bug 14170648
873
874 print_debug('in comupte detail , req pri qty '||l_remain_pri_qty);
875 print_debug('in comupte detail , req sec qty '||l_remain_sec_qty);
876
877 IF p_reservations.COUNT > 0 AND p_request_context.type_code = 2 THEN --{
878 FOR l_res_index IN 1..p_reservations.COUNT LOOP --{
879 -- Fix for bug #1063622 - l_index was not always retaining
880 -- its value upon exiting the loop. Hence we have replaced
881 -- it with variable l_res_index, which acts solely as a loop
882 -- index, and mirroring its value to l_index
883 l_index := l_res_index;
884
885 -- decide the quantity for the new record
886 l_reserved_qty :=
887 p_reservations(l_index).primary_reservation_quantity -
888 NVL(p_reservations(l_index).detailed_quantity,0);
889 l_sec_reserved_qty :=
890 p_reservations(l_index).secondary_reservation_quantity -
891 NVL(p_reservations(l_index).secondary_detailed_quantity,0);
892
893 print_debug('in comupte detail , res qty '||l_reserved_qty);
894 print_debug('in comupte detail , res sec qty '||l_sec_reserved_qty);
895
896 IF (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_reserved_qty <= 0)
897 OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_sec_reserved_qty <= 0)
898 THEN
899 GOTO next_rsv;
900 END IF;
901
902 IF (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_reserved_qty > l_remain_pri_qty)
903 OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_sec_reserved_qty > l_remain_sec_qty)
904 THEN
905 l_pp_temp_qty := l_remain_pri_qty;
906 l_sec_pp_temp_qty := l_remain_sec_qty;
907 l_remain_pri_qty := 0;
908 l_remain_sec_qty := 0;
909 ELSE
910 l_pp_temp_qty := l_reserved_qty;
911 l_sec_pp_temp_qty := l_sec_reserved_qty;
912 l_remain_pri_qty := l_remain_pri_qty - l_reserved_qty;
913 l_remain_sec_qty := l_remain_sec_qty - l_sec_reserved_qty;
914 END IF;
915
916 print_debug('in comupte detail , l_pp_temp_qty '||l_pp_temp_qty);
917 print_debug('in comupte detail , l_sec_pp_temp_qty '||l_sec_pp_temp_qty);
918
919 -- FIX for BUG 2448249 - the default pick should come from the
920 -- reservation and not from the move order. Changing the following
921 -- IF statements for rev, sub, locator, lpn to check the reservation first
922 -- decide revision
923 IF p_reservations(l_index).revision IS NOT NULL THEN
924 x_detail_level_tbl(l_index).revision :=
925 p_reservations(l_index).revision;
926 ELSIF p_request_line_rec.revision IS NOT NULL THEN
927 x_detail_level_tbl(l_index).revision :=
928 p_request_line_rec.revision;
929 ELSE
930 x_detail_level_tbl(l_index).revision := NULL;
931 END IF;
932 -- decide lot number
933 IF p_reservations(l_index).lot_number IS NOT NULL THEN
934 x_detail_level_tbl(l_index).lot_number :=
935 p_reservations(l_index).lot_number;
936 ELSIF p_request_line_rec.lot_number IS NOT NULL THEN
937 x_detail_level_tbl(l_index).lot_number :=
938 p_request_line_rec.lot_number;
939 ELSE
940 x_detail_level_tbl(l_index).lot_number := NULL;
941 END IF;
942 -- [ added the following code to support the allocation for serial reserved items ]
943 -- [ decide serial Number ]
944 IF p_reservations(l_index).serial_number IS NOT NULL
945 AND p_reservations(l_index).serial_number <> fnd_api.g_miss_char THEN -- bug#10392328
946 x_detail_level_tbl(l_index).serial_number := p_reservations(l_index).serial_number ;
947 x_detail_level_tbl(l_index).serial_resv_flag := 'Y' ;
948 ELSE
949 x_detail_level_tbl(l_index).serial_number := NULL;
950 x_detail_level_tbl(l_index).serial_resv_flag := 'N' ;
951 END IF;
952
953 -- decide sub
954 IF p_reservations(l_index).subinventory_code IS NOT NULL THEN
955 x_detail_level_tbl(l_index).subinventory_code :=
956 p_reservations(l_index).subinventory_code;
957 ELSIF p_request_line_rec.from_subinventory_code IS NOT NULL THEN
958 x_detail_level_tbl(l_index).subinventory_code :=
959 p_request_line_rec.from_subinventory_code;
960 ELSE
961 x_detail_level_tbl(l_index).subinventory_code := NULL;
962 END IF;
963 -- decide locator
964 IF p_reservations(l_index).locator_id IS NOT NULL THEN
965 x_detail_level_tbl(l_index).locator_id :=
966 p_reservations(l_index).locator_id;
967 ELSIF p_request_line_rec.from_locator_id IS NOT NULL THEN
968 x_detail_level_tbl(l_index).locator_id :=
969 p_request_line_rec.from_locator_id;
970 ELSE
971 x_detail_level_tbl(l_index).locator_id := NULL;
972 END IF;
973 -- decide lpn
974 IF p_reservations(l_index).lpn_id IS NOT NULL THEN
975 x_detail_level_tbl(l_index).lpn_id :=
976 p_reservations(l_index).lpn_id;
977 ELSIF p_request_line_rec.lpn_id IS NOT NULL THEN
978 x_detail_level_tbl(l_index).lpn_id :=
979 p_request_line_rec.lpn_id;
980 ELSE
981 x_detail_level_tbl(l_index).lpn_id := NULL;
982 END IF;
983 -- record the reservation id
984 x_detail_level_tbl(l_index).reservation_id
985 := p_reservations(l_index).reservation_id;
986 --
987 x_detail_level_tbl(l_index).primary_quantity := l_pp_temp_qty;
988 x_detail_level_tbl(l_index).secondary_quantity := l_sec_pp_temp_qty;
989
990 IF p_request_context.transaction_uom_code = p_request_context.primary_uom_code THEN
991 x_detail_level_tbl(l_index).transaction_quantity := l_pp_temp_qty;
992 ELSIF p_request_context.transaction_uom_code = p_request_context.secondary_uom_code THEN
993 x_detail_level_tbl(l_index).transaction_quantity := l_sec_pp_temp_qty;
994 ELSE
995 IF NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' THEN
996 x_detail_level_tbl(l_index).transaction_quantity :=
997 inv_convert.inv_um_convert(
998 item_id => p_request_line_rec.inventory_item_id
999 , lot_number => x_detail_level_tbl(l_index).lot_number
1000 , organization_id => p_request_line_rec.organization_id
1001 , precision => NULL
1002 , from_quantity => l_sec_pp_temp_qty
1003 , from_unit => p_request_context.secondary_uom_code
1004 , to_unit => p_request_context.transaction_uom_code
1005 , from_name => NULL
1006 , to_name => NULL
1007 );
1008 ELSE
1009 x_detail_level_tbl(l_index).transaction_quantity :=
1010 inv_convert.inv_um_convert(
1011 item_id => p_request_line_rec.inventory_item_id
1012 , lot_number => x_detail_level_tbl(l_index).lot_number
1013 , organization_id => p_request_line_rec.organization_id
1014 , precision => NULL
1015 , from_quantity => l_sec_pp_temp_qty
1016 , from_unit => p_request_context.primary_uom_code
1017 , to_unit => p_request_context.transaction_uom_code
1018 , from_name => NULL
1019 , to_name => NULL
1020 );
1021 END IF;
1022 END IF;
1023
1024 IF (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_remain_pri_qty = 0)
1025 OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_remain_sec_qty = 0)
1026 THEN
1027 EXIT;
1028 END IF;
1029 <<next_rsv>>
1030 NULL;
1031 END LOOP; --}
1032 END IF; --}
1033
1034 -- if reservation quantity is less than request quantity for detailing;
1035 -- Bug 1851999 - For staging transfers, we should only allocate
1036 -- the quantity that has been reserved. We need this check to handle
1037 -- WIP reservations for sales orders.
1038 IF ( (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_remain_pri_qty > 0)
1039 OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_remain_sec_qty > 0)
1040 )
1041 AND NOT
1042 (p_request_context.transaction_action_id = 28 AND p_reservations.COUNT > 0)
1043 THEN --{
1044 l_index := x_detail_level_tbl.COUNT + 1;
1045 x_detail_level_tbl(l_index).subinventory_code := p_request_line_rec.from_subinventory_code;
1046 x_detail_level_tbl(l_index).locator_id := p_request_line_rec.from_locator_id;
1047 x_detail_level_tbl(l_index).primary_quantity := l_remain_pri_qty;
1048 x_detail_level_tbl(l_index).secondary_quantity := l_remain_sec_qty;
1049
1050 IF p_request_context.transaction_uom_code = p_request_context.primary_uom_code THEN
1051 x_detail_level_tbl(l_index).transaction_quantity := l_remain_pri_qty;
1052 ELSIF p_request_context.transaction_uom_code = p_request_context.secondary_uom_code THEN
1053 x_detail_level_tbl(l_index).transaction_quantity := l_remain_sec_qty;
1054 ELSE
1055 IF NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' THEN
1056 x_detail_level_tbl(l_index).transaction_quantity :=
1057 inv_convert.inv_um_convert(
1058 item_id => p_request_line_rec.inventory_item_id
1059 , lot_number => p_request_line_rec.lot_number
1060 , organization_id => p_request_line_rec.organization_id
1061 , precision => NULL
1062 , from_quantity => l_remain_sec_qty
1063 , from_unit => p_request_context.secondary_uom_code
1064 , to_unit => p_request_context.transaction_uom_code
1065 , from_name => NULL
1066 , to_name => NULL
1067 );
1068 ELSE
1069 x_detail_level_tbl(l_index).transaction_quantity :=
1070 inv_convert.inv_um_convert(
1071 item_id => p_request_line_rec.inventory_item_id
1072 , lot_number => p_request_line_rec.lot_number
1073 , organization_id => p_request_line_rec.organization_id
1074 , precision => NULL
1075 , from_quantity => l_remain_pri_qty
1076 , from_unit => p_request_context.primary_uom_code
1077 , to_unit => p_request_context.transaction_uom_code
1078 , from_name => NULL
1079 , to_name => NULL
1080 );
1081 END IF;
1082 END IF;
1083
1084 l_remain_pri_qty := 0;
1085 l_remain_sec_qty := 0;
1086 x_detail_level_tbl(l_index).revision := p_request_line_rec.revision;
1087 x_detail_level_tbl(l_index).grade_code := p_request_line_rec.grade_code;
1088 x_detail_level_tbl(l_index).lot_number := p_request_line_rec.lot_number;
1089 x_detail_level_tbl(l_index).lpn_id := p_request_line_rec.lpn_id;
1090 END IF; --}
1091 x_detail_level_tbl_size := l_index;
1092 --
1093 x_return_status := l_return_status;
1094
1095 -- Bug # 2286454-----------------------
1096 x_remaining_quantity := l_remain_pri_qty;
1097 x_remaining_sec_qty := l_remain_sec_qty;
1098
1099 IF inv_pp_debug.is_debug_mode THEN
1100 inv_pp_debug.send_message_to_pipe('detail table size: ' || x_detail_level_tbl_size);
1101 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
1102 END IF;
1103 --
1104 EXCEPTION
1105 when fnd_api.g_exc_error then
1106 x_return_status := fnd_api.g_ret_sts_error ;
1107 --
1108 when fnd_api.g_exc_unexpected_error then
1109 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1110 --
1111 when others then
1112 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1113 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1114 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1115 end if;
1116 --
1117 END compute_pick_detail_level;
1118 --
1119 PROCEDURE compute_pick_detail_level
1120 ( x_return_status OUT NOCOPY VARCHAR2
1121 , p_request_line_rec IN g_request_line_rec_type
1122 , p_request_context IN g_request_context_rec_type
1123 , p_reservations IN inv_reservation_global.mtl_reservation_tbl_type
1124 , x_detail_level_tbl IN OUT NOCOPY g_detail_level_tbl_type
1125 , x_detail_level_tbl_size OUT NOCOPY NUMBER
1126 , x_remaining_quantity OUT NOCOPY NUMBER
1127 ) IS
1128 l_remaining_sec_qty NUMBER;
1129 BEGIN
1130 -- Call the overloaded version
1131 compute_pick_detail_level
1132 ( x_return_status => x_return_status
1133 , p_request_line_rec => p_request_line_rec
1134 , p_request_context => p_request_context
1135 , p_reservations => p_reservations
1136 , x_detail_level_tbl => x_detail_level_tbl
1137 , x_detail_level_tbl_size => x_detail_level_tbl_size
1138 , x_remaining_quantity => x_remaining_quantity
1139 , x_remaining_sec_qty => l_remaining_sec_qty
1140 );
1141 END compute_pick_detail_level;
1142 --
1143 PROCEDURE validate_and_init
1144 (x_return_status OUT NOCOPY VARCHAR2,
1145 p_request_line_id IN NUMBER,
1146 p_suggest_serial IN VARCHAR2,
1147 x_request_line_rec OUT NOCOPY g_request_line_rec_type,
1148 x_request_context OUT NOCOPY g_request_context_rec_type,
1149 p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
1150 )
1151 IS
1152 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1153 l_api_name VARCHAR2(30) := 'validate_and_init';
1154 BEGIN
1155 --
1156 IF inv_pp_debug.is_debug_mode THEN
1157 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
1158 END IF;
1159 -- Validation and Initialization
1160 --
1161 print_debug('in inv validate and init');
1162 IF p_request_line_id IS NULL THEN
1163 fnd_message.set_name('INV','INV_PP_TRX_REQ_LINE_ID_MISS');
1164 fnd_msg_pub.add;
1165 RAISE fnd_api.g_exc_error;
1166 END IF;
1167 --
1168 -- get request context
1169 print_debug('before context ');
1170 get_request_context(l_return_status,
1171 p_request_line_id,
1172 x_request_context,
1173 x_request_line_rec,
1174 p_wave_simulation_mode
1175 );
1176 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1177 RAISE fnd_api.g_exc_error;
1178 END IF;
1179 --
1180 -- check whether quantity to detail is >0, if not, return
1181 IF x_request_line_rec.quantity IS NOT NULL
1182 AND x_request_line_rec.quantity >0
1183 AND (x_request_line_rec.quantity_detailed IS NULL
1184 OR x_request_line_rec.quantity_detailed
1185 < x_request_line_rec.quantity) THEN
1186 NULL;
1187 ELSE
1188 -- no quantity to detail, so return
1189 x_return_status := l_return_status;
1190 RETURN;
1191 END IF;
1192 --
1193 -- Determine whether serial numbers should be detailed.
1194 -- First, get value for profile
1195 -- If profile = 1, detail any serial number, not just those
1196 -- within the given range
1197 -- Bug 1712465 - We now get detail_any_serial from mtl_parameters
1198 -- in the get_request_context procedure.
1199 --x_request_context.detail_any_serial :=
1200 -- to_number(fnd_profile.value('INV:DETAIL_SERIAL_NUMBERS'));
1201
1202 IF p_suggest_serial = fnd_api.g_true AND
1203 x_request_context.item_serial_control_code NOT IN (1,6) AND
1204 (x_request_context.detail_any_serial = 1 OR
1205 (x_request_line_rec.serial_number_start IS NOT NULL AND
1206 x_request_line_rec.serial_number_end IS NOT NULL)) THEN
1207 x_request_context.detail_serial := TRUE;
1208 ELSE
1209 x_request_context.detail_serial := FALSE;
1210 IF inv_pp_debug.is_debug_mode THEN
1211 inv_pp_debug.send_message_to_pipe('detail serial is FALSE');
1212 END IF;
1213
1214 END IF;
1215
1216 -- initialize serial output table
1217 init_output_serial_rows;
1218 x_return_status := l_return_status;
1219 IF inv_pp_debug.is_debug_mode THEN
1220 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
1221 END IF;
1222 --
1223 EXCEPTION
1224 when fnd_api.g_exc_error then
1225 x_return_status := fnd_api.g_ret_sts_error ;
1226 --
1227 when fnd_api.g_exc_unexpected_error then
1228 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1229 --
1230 when others then
1231 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1232 if (fnd_msg_pub.check_msg_level(
1233 fnd_msg_pub.g_msg_lvl_unexp_error)) then
1234 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1235 end if;
1236 --
1237 END validate_and_init;
1238 --
1239
1240
1241 FUNCTION is_sub_loc_lot_trx_allowed(
1242 p_transaction_type_id IN NUMBER
1243 ,p_organization_id IN NUMBER
1244 ,p_inventory_item_id IN NUMBER
1245 ,p_subinventory_code IN VARCHAR2
1246 ,p_locator_id IN NUMBER
1247 ,p_lot_number IN VARCHAR2
1248 ) RETURN VARCHAR2 IS
1249
1250 l_return_status VARCHAR2(1);
1251 l_msg_count NUMBER;
1252 l_msg_data VARCHAR2(240);
1253 l_lot_status_enabled VARCHAR2(1);
1254 l_default_lot_status_id NUMBER;
1255 l_serial_status_enabled VARCHAR2(1);
1256 l_default_serial_status_id NUMBER;
1257 l_sub_return VARCHAR2(1);
1258 l_loc_return VARCHAR2(1);
1259 l_lot_return VARCHAR2(1);
1260 l_api_name constant varchar(30) := 'is_sub_loc_lot_trx_allowed';
1261 l_sub_status NUMBER;
1262 l_loc_status NUMBER;
1263 l_lot_status NUMBER;
1264
1265 CURSOR c_sub_status IS
1266 SELECT status_id
1267 FROM mtl_secondary_inventories
1268 WHERE organization_id = p_organization_id
1269 AND secondary_inventory_name = p_subinventory_code;
1270
1271 CURSOR c_loc_status IS
1272 SELECT status_id
1273 FROM mtl_item_locations
1274 WHERE organization_id = p_organization_id
1275 AND inventory_location_id = p_locator_id;
1276
1277 CURSOR c_lot_status IS
1278 SELECT status_id
1279 FROM mtl_lot_numbers
1280 WHERE organization_id = p_organization_id
1281 AND inventory_item_id = p_inventory_item_id
1282 AND lot_number = p_lot_number;
1283
1284 BEGIN
1285
1286 l_sub_return := 'Y';
1287 l_loc_return := 'Y';
1288 l_lot_return := 'Y';
1289
1290 /* Performance issue - now check this flag only on INV side when
1291 * building dynamic SQL
1292 *--Check to see if status is enabled. if not, return 'Y'
1293 *IF NOT inv_install.adv_inv_installed(NULL) THEN
1294 * return 'Y';
1295 *END IF;
1296 */
1297
1298 IF p_subinventory_code IS NOT NULL THEN
1299 -- get status
1300 IF (nvl(g_isllta_subinventory_code,'@@@') = p_subinventory_code
1301 AND nvl(g_isllta_transaction_type_id, -1) = p_transaction_type_id
1302 AND (inv_cache.is_pickrelease)) THEN --Bug 5246569
1303 l_sub_return := g_sub_return;
1304 ELSE
1305 OPEN c_sub_status;
1306 FETCH c_sub_status INTO l_sub_status;
1307 IF c_sub_status%FOUND AND l_sub_status IS NOT NULL THEN
1308 --check if txn type allowed with given sub status
1309 l_sub_return := inv_material_status_grp.is_trx_allowed(
1310 p_status_id => l_sub_status
1311 ,p_transaction_type_id => p_transaction_type_id
1312 ,x_return_status => l_return_status
1313 ,x_msg_count => l_msg_count
1314 ,x_msg_data => l_msg_data);
1315
1316 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1317 CLOSE c_sub_status;
1318 RAISE fnd_api.g_exc_unexpected_error;
1319 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1320 CLOSE c_sub_status;
1321 RAISE fnd_api.g_exc_error;
1322 END IF;
1323 END IF;
1324 CLOSE c_sub_status;
1325 g_isllta_subinventory_code := p_subinventory_code;
1326 g_sub_return := l_sub_return;
1327 if nvl(g_isllta_transaction_type_id, -1) <> p_transaction_type_id THEN
1328 g_isllta_locator_id := NULL;
1329 g_isllta_lot_number := NULL;
1330 end if;
1331 g_isllta_transaction_type_id := p_transaction_type_id;
1332 END IF;
1333 print_debug('check sub_lot_loc_trx_allowed l_sub_return '||l_sub_return);
1334 END IF;
1335
1336 IF p_locator_id IS NOT NULL THEN
1337 --get status
1338 IF (nvl(g_isllta_locator_id,-1) = p_locator_id
1339 AND nvl(g_isllta_transaction_type_id, -1) = p_transaction_type_id
1340 AND (inv_cache.is_pickrelease)) THEN --Bug 5246569
1341 l_loc_return := g_loc_return;
1342 ELSE
1343 OPEN c_loc_status;
1344 FETCH c_loc_status INTO l_loc_status;
1345 IF c_loc_status%FOUND AND l_loc_status IS NOT NULL THEN
1346 --check if txn type allowed with given locator status
1347 l_loc_return := inv_material_status_grp.is_trx_allowed(
1348 p_status_id => l_loc_status
1349 ,p_transaction_type_id => p_transaction_type_id
1350 ,x_return_status => l_return_status
1351 ,x_msg_count => l_msg_count
1352 ,x_msg_data => l_msg_data);
1353
1354 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1355 CLOSE c_loc_status;
1356 RAISE fnd_api.g_exc_unexpected_error;
1357 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1358 CLOSE c_loc_status;
1359 RAISE fnd_api.g_exc_error;
1360 END IF;
1361 END IF;
1362 CLOSE c_loc_status;
1363 g_isllta_locator_id := p_locator_id;
1364 g_loc_return := l_loc_return;
1365 if nvl(g_isllta_transaction_type_id, -1) <> p_transaction_type_id THEN
1366 g_isllta_subinventory_code := NULL;
1367 g_isllta_lot_number := NULL;
1368 end if;
1369 g_isllta_transaction_type_id := p_transaction_type_id;
1370 END IF;
1371 print_debug('check sub_lot_loc_trx_allowed l_loc_return '||l_loc_return);
1372 END IF;
1373
1374 --if item is lot status controlled, check if txn type is allowed
1375 inv_material_status_grp.get_lot_serial_status_control(
1376 p_organization_id => p_organization_id
1377 ,p_inventory_item_id => p_inventory_item_id
1378 ,x_return_status => l_return_status
1379 ,x_msg_count => l_msg_count
1380 ,x_msg_data => l_msg_data
1381 ,x_lot_status_enabled => l_lot_status_enabled
1382 ,x_default_lot_status_id => l_default_lot_status_id
1383 ,x_serial_status_enabled => l_serial_status_enabled
1384 ,x_default_serial_status_id => l_default_serial_status_id);
1385
1386 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1387 RAISE fnd_api.g_exc_unexpected_error;
1388 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1389 RAISE fnd_api.g_exc_error;
1390 END IF;
1391
1392 if l_lot_status_enabled = 'Y' AND p_lot_number IS NOT NULL THEN
1393 --get status
1394 IF (nvl(g_isllta_lot_number,'@@@') = p_lot_number
1395 AND nvl(g_isllta_transaction_type_id, -1) = p_transaction_type_id
1396 AND (inv_cache.is_pickrelease)) THEN --Bug 5246569
1397 l_lot_return := g_lot_return;
1398 ELSE
1399 OPEN c_lot_status;
1400 FETCH c_lot_status INTO l_lot_status;
1401 IF c_lot_status%FOUND AND l_lot_status IS NOT NULL THEN
1402 l_lot_return := inv_material_status_grp.is_trx_allowed(
1403 p_status_id => l_lot_status
1404 ,p_transaction_type_id => p_transaction_type_id
1405 ,x_return_status => l_return_status
1406 ,x_msg_count => l_msg_count
1407 ,x_msg_data => l_msg_data);
1408
1409 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1410 CLOSE c_lot_status;
1411 RAISE fnd_api.g_exc_unexpected_error;
1412 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1413 CLOSE c_lot_status;
1414 RAISE fnd_api.g_exc_error;
1415 END IF;
1416 END IF;
1417 CLOSE c_lot_status;
1418 g_isllta_lot_number := p_lot_number;
1419 g_lot_return := l_lot_return;
1420 if nvl(g_isllta_transaction_type_id, -1) <> p_transaction_type_id THEN
1421 g_isllta_locator_id := NULL;
1422 g_isllta_subinventory_code := NULL;
1423 end if;
1424 g_isllta_transaction_type_id := p_transaction_type_id;
1425 END IF;
1426 print_debug('check sub_lot_loc_trx_allowed l_lot_return '||l_lot_return);
1427 END IF;
1428
1429 IF (l_sub_return='Y' AND l_loc_return='Y' AND l_lot_return='Y') THEN
1430 return 'Y';
1431 ELSE
1432 return 'N';
1433 END IF;
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1437 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1438 END IF;
1439 fnd_msg_pub.count_and_get( p_count => l_msg_count
1440 ,p_data => l_msg_data );
1441 return 'N';
1442
1443 END is_sub_loc_lot_trx_allowed;
1444
1445
1446 FUNCTION is_serial_trx_allowed(
1447 p_transaction_type_id IN NUMBER
1448 ,p_organization_id IN NUMBER
1449 ,p_inventory_item_id IN NUMBER
1450 ,p_serial_status IN NUMBER
1451 ) RETURN VARCHAR2 IS
1452
1453 l_return_status VARCHAR2(1);
1454 l_msg_count NUMBER;
1455 l_msg_data VARCHAR2(240);
1456 l_lot_status_enabled VARCHAR2(1);
1457 l_default_lot_status_id NUMBER;
1458 l_serial_status_enabled VARCHAR2(1);
1459 l_default_serial_status_id NUMBER;
1460 l_serial_return VARCHAR2(1);
1461 l_api_name constant varchar(30) := 'is_serial_trx_allowed';
1462
1463 BEGIN
1464
1465 l_serial_return := 'Y';
1466
1467
1468 /* Performance issue - now check this flag only on INV side when
1469 * building dynamic SQL
1470 *--Check to see if status is enabled. if not, return 'Y'
1471 *IF NOT inv_install.adv_inv_installed(NULL) THEN
1472 * return 'Y';
1473 *END IF;
1474 */
1475
1476
1477 IF ((p_organization_id <> NVL(g_organization_id,-999)) OR
1478 (p_inventory_item_id <> NVL(g_inventory_item_id,-999)) OR
1479 (g_serial_status_enabled IS NULL)) THEN
1480 --if item is serial status controlled, check if txn type is allowed
1481 g_organization_id := p_organization_id;
1482 g_inventory_item_id := p_inventory_item_id;
1483
1484 inv_material_status_grp.get_lot_serial_status_control(
1485 p_organization_id => p_organization_id
1486 ,p_inventory_item_id => p_inventory_item_id
1487 ,x_return_status => l_return_status
1488 ,x_msg_count => l_msg_count
1489 ,x_msg_data => l_msg_data
1490 ,x_lot_status_enabled => l_lot_status_enabled
1491 ,x_default_lot_status_id => l_default_lot_status_id
1492 ,x_serial_status_enabled => g_serial_status_enabled
1493 ,x_default_serial_status_id => l_default_serial_status_id);
1494
1495 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1496 RAISE fnd_api.g_exc_unexpected_error;
1497 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1498 RAISE fnd_api.g_exc_error;
1499 END IF;
1500 END IF;
1501
1502 IF g_serial_status_enabled = 'Y' AND p_serial_status IS NOT NULL THEN
1503 IF ((p_serial_status = g_serial_status) AND
1504 (p_transaction_type_id = NVL(g_transaction_type_id,-999))) THEN
1505 l_serial_return := g_serial_return;
1506 ELSE
1507 g_serial_status := p_serial_status;
1508 g_transaction_type_id := p_transaction_type_id;
1509
1510 g_serial_return := inv_material_status_grp.is_trx_allowed(
1511 p_status_id => p_serial_status
1512 ,p_transaction_type_id => p_transaction_type_id
1513 ,x_return_status => l_return_status
1514 ,x_msg_count => l_msg_count
1515 ,x_msg_data => l_msg_data);
1516
1517 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1518 RAISE fnd_api.g_exc_unexpected_error;
1519 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1520 RAISE fnd_api.g_exc_error;
1521 END IF;
1522 l_serial_return := g_serial_return;
1523 END IF;
1524 END IF;
1525
1526 IF (l_serial_return = 'Y') THEN
1527 return 'Y';
1528 ELSE
1529 return 'N';
1530 END IF;
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1534 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1535 END IF;
1536 fnd_msg_pub.count_and_get( p_count => l_msg_count
1537 ,p_data => l_msg_data );
1538 return 'N';
1539
1540 END is_serial_trx_allowed;
1541
1542 PROCEDURE build_sql (
1543 x_return_status OUT NOCOPY VARCHAR2
1544 ,x_sql_statement OUT NOCOPY LONG)
1545 IS
1546
1547 BEGIN
1548 x_return_status := fnd_api.g_ret_sts_success;
1549 x_sql_statement := '
1550 SELECT
1551 x.organization_id
1552 ,x.inventory_item_id
1553 ,x.revision
1554 ,x.lot_number
1555 ,lot.expiration_date lot_expiration_date
1556 ,x.subinventory_code
1557 ,sub.reservable_type
1558 ,x.locator_id
1559 ,x.cost_group_id
1560 ,x.date_received date_received
1561 ,x.primary_quantity primary_quantity
1562 ,NULL lpn_id
1563 ,x.project_id project_id
1564 ,x.task_id task_id
1565 FROM
1566 (SELECT
1567 moq.organization_id
1568 ,moq.inventory_item_id
1569 ,moq.revision
1570 ,moq.lot_number
1571 ,moq.subinventory_code
1572 ,moq.locator_id
1573 ,moq.cost_group_id
1574 ,min(NVL(moq.orig_date_received,
1575 moq.date_received)) date_received
1576 ,sum(moq.primary_transaction_quantity) primary_quantity
1577 ,moq.project_id
1578 ,moq.task_id
1579 FROM
1580 MTL_ONHAND_QUANTITIES_DETAIL moq
1581 WHERE
1582 moq.organization_id = :organization_id
1583 AND moq.inventory_item_id = :inventory_item_id
1584 GROUP BY
1585 moq.organization_id, moq.inventory_item_id
1586 ,moq.revision, moq.lot_number
1587 ,moq.subinventory_code, moq.locator_id
1588 ,moq.cost_group_id
1589 ,moq.project_id
1590 ,moq.task_id
1591 ) x
1592 ,mtl_secondary_inventories sub
1593 ,mtl_lot_numbers lot
1594 WHERE
1595 x.primary_quantity > 0
1596 AND x.organization_id = sub.organization_id
1597 AND x.subinventory_code = sub.secondary_inventory_name
1598 AND NVL(sub.disable_date, sysdate+1) > sysdate
1599 AND x.organization_id = lot.organization_id (+)
1600 AND x.inventory_item_id = lot.inventory_item_id (+)
1601 AND x.lot_number = lot.lot_number (+)
1602 ';
1603 END build_sql;
1604
1605
1606 -- Description
1607 -- Initialize the internal table that stores the serial numbers detailed
1608 -- to empty
1609 PROCEDURE init_output_serial_rows IS
1610 BEGIN
1611 g_serial_tbl_ptr := 0;
1612 g_output_serial_rows.DELETE;
1613 END init_output_serial_rows;
1614 --
1615 -- --------------------------------------------------------------------------
1616 -- What does it do:
1617 -- Sees if the passed serial number exists in our memory structure,
1618 -- g_output_serial_rows.
1619 -- If found, x_found = TRUE, else FALSE.
1620 -- --------------------------------------------------------------------------
1621 procedure search_serial_numbers(
1622 p_inventory_item_id IN NUMBER
1623 , p_organization_id IN NUMBER
1624 , p_serial_number IN VARCHAR2
1625 , x_found OUT NOCOPY BOOLEAN
1626 , x_return_status OUT NOCOPY VARCHAR2
1627 , x_msg_count OUT NOCOPY NUMBER
1628 , x_msg_data OUT NOCOPY VARCHAR2) is
1629
1630 -- constants
1631 l_api_name constant varchar(30) := 'search_serial_numbers';
1632 begin
1633 x_return_status := fnd_api.g_ret_sts_success;
1634 x_found := FALSE;
1635
1636 if (g_serial_tbl_ptr > 0) then
1637 for i in 1..g_serial_tbl_ptr loop
1638
1639 if (g_output_serial_rows(i).inventory_item_id = p_inventory_item_id) and
1640 (g_output_serial_rows(i).organization_id = p_organization_id) and
1641 (g_output_serial_rows(i).serial_number = p_serial_number) then
1642 x_found := TRUE;
1643 exit;
1644 end if;
1645 end loop;
1646 end if;
1647 --
1648 exception
1649 when fnd_api.g_exc_error then
1650 x_return_status := fnd_api.g_ret_sts_error ;
1651 x_found := TRUE;
1652
1653 when fnd_api.g_exc_unexpected_error then
1654 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1655 x_found := TRUE;
1656
1657 when others then
1658 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1659 x_found := TRUE;
1660
1661 if (fnd_msg_pub.check_msg_level(
1662 fnd_msg_pub.g_msg_lvl_unexp_error)) then
1663 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1664 end if;
1665
1666 end search_serial_numbers;
1667 --
1668 -- add serial number to pl/sql table
1669 PROCEDURE add_serial_number(
1670 p_inventory_item_id IN NUMBER
1671 ,p_organization_id IN NUMBER
1672 ,p_serial_number IN VARCHAR2
1673 ,x_serial_index OUT NOCOPY NUMBER
1674 ) IS
1675
1676 BEGIN
1677
1678 g_serial_tbl_ptr := g_serial_tbl_ptr + 1;
1679 g_output_serial_rows(g_serial_tbl_ptr).serial_identifier :=
1680 g_serial_tbl_ptr;
1681 g_output_serial_rows(g_serial_tbl_ptr).inventory_item_id:=
1682 p_inventory_item_id;
1683 g_output_serial_rows(g_serial_tbl_ptr).organization_id:=
1684 p_organization_id;
1685 g_output_serial_rows(g_serial_tbl_ptr).serial_number:=
1686 p_serial_number;
1687 x_serial_index := g_serial_tbl_ptr;
1688 END add_serial_number;
1689
1690 -- try to lock a serial number , return true if success, else flase
1691 FUNCTION lock_serial_number
1692 (p_inventory_item_id IN NUMBER,
1693 p_serial_number IN VARCHAR2
1694 ) RETURN BOOLEAN
1695 IS
1696 CURSOR l_cur IS
1697 SELECT serial_number
1698 FROM mtl_serial_numbers
1699 WHERE inventory_item_id = p_inventory_item_id
1700 AND serial_number = p_serial_number
1701 FOR UPDATE nowait;
1702 l_serial_number VARCHAR2(30);
1703 BEGIN
1704 OPEN l_cur;
1705 IF l_cur%notfound THEN
1706 CLOSE l_cur;
1707 RETURN FALSE;
1708 END IF;
1709 FETCH l_cur INTO l_serial_number;
1710 CLOSE l_cur;
1711 IF l_serial_number IS NULL THEN
1712 RETURN FALSE;
1713 END IF;
1714 RETURN TRUE;
1715 EXCEPTION
1716 WHEN timeout_on_resource THEN
1717 RETURN FALSE;
1718 WHEN OTHERS THEN
1719 RETURN FALSE;
1720 END lock_serial_number;
1721 --
1722 --
1723 -- --------------------------------------------------------------------------
1724 -- What does it do:
1725 -- Given the item/organization, inventory controls, quantity for a autodetailed
1726 -- row and also from/to serial number range info,
1727 -- it fetches and populates available serial numbers into g_output_serial_rows.
1728 -- --------------------------------------------------------------------------
1729 PROCEDURE get_serial_numbers (
1730 p_inventory_item_id IN NUMBER
1731 , p_organization_id IN NUMBER
1732 , p_revision IN VARCHAR2
1733 , p_lot_number IN VARCHAR2
1734 , p_subinventory_code IN VARCHAR2
1735 , p_locator_id IN NUMBER
1736 , p_required_sl_qty IN NUMBER
1737 , p_from_range IN VARCHAR2
1738 , p_to_range IN VARCHAR2
1739 , p_unit_number IN VARCHAR2
1740 , p_detail_any_serial IN NUMBER
1741 , p_cost_group_id IN NUMBER
1742 , p_transaction_type_id IN NUMBER
1743 , x_available_sl_qty OUT NOCOPY NUMBER
1744 , x_serial_index OUT NOCOPY NUMBER
1745 , x_return_status OUT NOCOPY VARCHAR2
1746 , x_msg_count OUT NOCOPY NUMBER
1747 , x_msg_data OUT NOCOPY VARCHAR2
1748 , p_demand_source_type_id IN NUMBER := null
1749 , p_demand_source_header_id IN NUMBER := null
1750 , p_demand_source_line_id IN NUMBER := null
1751 ) IS
1752 l_api_name CONSTANT VARCHAR2(30) := 'Get_Serial_Numbers';
1753 l_api_version CONSTANT NUMBER := 1.0;
1754 l_debug NUMBER;
1755 l_progress VARCHAR2(10) := '0';
1756
1757 --bug 2620572 - allocate ordered by serial number
1758 CURSOR msnc IS
1759 SELECT p_inventory_item_id
1760 , p_organization_id
1761 , msn.serial_number
1762 , null
1763 FROM mtl_serial_numbers msn, inv_msn_gtemp img
1764 WHERE msn.inventory_item_id = p_inventory_item_id
1765 AND msn.current_organization_id = p_organization_id
1766 AND nvl(msn.revision,'@@@') = nvl(p_revision,'@@@')
1767 AND nvl(msn.lot_number, '@@@') = nvl(p_lot_number,'@@@')
1768 AND nvl(msn.current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
1769 AND nvl(msn.current_locator_id,-1) = nvl(p_locator_id,-1)
1770 AND nvl(msn.end_item_unit_number,'@@@') = nvl(p_unit_number,'@@@')
1771 --AND nvl(msn.cost_group_id,-1) = nvl(p_cost_group_id, -1)
1772 AND msn.current_status = 3
1773 AND ((msn.group_mark_id is null) or (msn.group_mark_id = -1))
1774 AND (p_detail_any_serial = 1 OR
1775 (p_from_range <= msn.serial_number AND
1776 Length(p_from_range) = Length(msn.serial_number))
1777 )
1778 AND (p_detail_any_serial = 1 OR
1779 (msn.serial_number <= p_to_range AND
1780 Length(p_to_range) = Length(msn.serial_number))
1781 )
1782 AND msn.serial_number = img.serial_number (+)
1783 AND msn.inventory_item_id = img.inventory_item_id (+)
1784 AND msn.current_organization_id = img.organization_id (+)
1785 AND img.serial_number IS NULL
1786 ORDER BY msn.serial_number;
1787
1788 CURSOR validate_sn_cur IS
1789 SELECT img.inventory_item_id
1790 , img.organization_id
1791 , img.serial_number
1792 --, null dont forget
1793 , msn.status_id
1794 FROM inv_msn_gtemp img, mtl_serial_numbers msn
1795 WHERE img.use_flag = 0
1796 AND msn.serial_number = img.serial_number
1797 AND msn.inventory_item_id = img.inventory_item_id
1798 AND msn.current_organization_id = img.organization_id
1799 AND nvl(msn.revision,'@') = nvl(p_revision,'@')
1800 AND nvl(msn.lot_number, '@') = nvl(p_lot_number,'@')
1801 AND nvl(msn.current_subinventory_code,'@') = nvl(p_subinventory_code,'@')
1802 AND nvl(msn.current_locator_id,-1) = nvl(p_locator_id,-1)
1803 AND nvl(msn.end_item_unit_number,'@') = nvl(p_unit_number,'@')
1804 AND msn.current_status = 3;
1805
1806 l_allocate_serial_flag VARCHAR2(1);
1807 l_custom_select_serials INV_DETAIL_UTIL_PVT.g_serial_row_table_rec;
1808 l_selected_serials INV_DETAIL_UTIL_PVT.g_serial_row_table_rec;
1809
1810 BEGIN
1811 x_return_status := fnd_api.g_ret_sts_success ;
1812 x_available_sl_qty := 0;
1813 x_serial_index := 0;
1814
1815 IF g_debug IS NULL or NOT INV_CACHE.is_pickrelease THEN
1816 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),2);
1817 END IF;
1818 l_debug := g_debug;
1819
1820 IF ( l_debug = 1 ) THEN
1821 print_debug('enter '||g_pkg_name||'.'||l_api_name, 1);
1822 print_debug('orgid='||p_organization_id||' itm='||p_inventory_item_id||' rev='||p_revision||' lot='||p_lot_number||' sub='||p_subinventory_code||' loc='||p_locator_id||' qty='||p_required_sl_qty||' fmsn='||p_from_range||' tosn='||p_to_range, 4);
1823 print_debug('unt='||p_unit_number||' det='||p_detail_any_serial||' cg='||p_cost_group_id||' trxtyp='||p_transaction_type_id||' styp='||p_demand_source_type_id||' shdr='||p_demand_source_header_id||' sln='||p_demand_source_line_id, 4);
1824 END IF;
1825
1826 IF ( inv_cache.set_org_rec(p_organization_id) ) THEN
1827 l_allocate_serial_flag := inv_cache.org_rec.allocate_serial_flag;
1828 IF ( l_debug = 1 ) THEN
1829 print_debug('allocate_serial_flag= '||l_allocate_serial_flag);
1830 END IF;
1831 ELSE
1832 IF ( l_debug = 1 ) THEN
1833 print_debug('mo context not found');
1834 END IF;
1835 RAISE no_data_found;
1836 END IF;
1837
1838 IF ( l_allocate_serial_flag = 'C' ) THEN
1839 INV_DETAIL_SERIAL_PUB.Get_User_Serial_Numbers (
1840 x_return_status => x_return_status
1841 , x_msg_count => x_msg_count
1842 , x_msg_data => x_msg_data
1843 , p_organization_id => p_organization_id
1844 , p_inventory_item_id => p_inventory_item_id
1845 , p_revision => p_revision
1846 , p_lot_number => p_lot_number
1847 , p_subinventory_code => p_subinventory_code
1848 , p_locator_id => p_locator_id
1849 , p_required_sl_qty => p_required_sl_qty
1850 , p_from_range => p_from_range
1851 , p_to_range => p_to_range
1852 , p_unit_number => p_unit_number
1853 , p_cost_group_id => p_cost_group_id
1854 , p_transaction_type_id => p_transaction_type_id
1855 , p_demand_source_type_id => p_demand_source_type_id
1856 , p_demand_source_header_id => p_demand_source_header_id
1857 , p_demand_source_line_id => p_demand_source_line_id
1858 , x_serial_numbers => l_custom_select_serials );
1859
1860 IF ( x_return_status = fnd_api.g_ret_sts_unexp_error ) THEN
1861 IF ( l_debug = 1 ) THEN
1862 print_debug('unexp_error from Get_User_Serial_Numbers');
1863 END IF;
1864 RAISE fnd_api.g_exc_unexpected_error;
1865 ELSIF ( x_return_status = fnd_api.g_ret_sts_error ) THEN
1866 IF ( l_debug = 1 ) THEN
1867 print_debug('error from Get_User_Serial_Numbers');
1868 END IF;
1869 RAISE fnd_api.g_exc_error;
1870 END IF;
1871
1872 --Bulk insert the return values from user into temp table for validation
1873 BEGIN
1874 FORALL i IN l_custom_select_serials.serial_number.first..l_custom_select_serials.serial_number.last
1875 INSERT INTO inv_msn_gtemp (
1876 inventory_item_id
1877 , organization_id
1878 , serial_number
1879 , use_flag )
1880 values (
1881 l_custom_select_serials.inventory_item_id(i)
1882 , l_custom_select_serials.organization_id(i)
1883 , l_custom_select_serials.serial_number(i)
1884 , 0 );
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 IF ( l_debug = 1 ) THEN
1888 print_debug('Get_User_Serial_Numbers returned duplicate serials', 1);
1889 END IF;
1890 fnd_message.set_name('INV', 'INV_DUPLICATE_SERIAL');
1891 fnd_msg_pub.ADD;
1892 RAISE fnd_api.g_exc_error;
1893 END;
1894
1895 --Retrieve only valid rows returned from api
1896 OPEN validate_sn_cur;
1897 FETCH validate_sn_cur
1898 BULK COLLECT INTO
1899 l_selected_serials.inventory_item_id
1900 , l_selected_serials.organization_id
1901 , l_selected_serials.serial_number
1902 , l_selected_serials.serial_status;
1903 CLOSE validate_sn_cur;
1904
1905 --Delete any serial numbers not being used in inv_msn_gtemp
1906 DELETE FROM inv_msn_gtemp
1907 WHERE use_flag = 0;
1908 ELSE
1909 --bug 1348067 - causing serial numbers to detail twice
1910 -- we should initialize ptr once per detailing, not once per function call
1911 -- Serial_ptr_table now initialized in validate_and_init
1912 --g_serial_tbl_ptr := 0;
1913 OPEN msnc;
1914 FETCH msnc BULK COLLECT INTO
1915 l_selected_serials.inventory_item_id
1916 , l_selected_serials.organization_id
1917 , l_selected_serials.serial_number
1918 , l_selected_serials.serial_status;
1919 CLOSE msnc;
1920 END IF;
1921
1922 IF l_selected_serials.serial_number.count > 0 THEN
1923 FOR i IN l_selected_serials.serial_number.first..l_selected_serials.serial_number.last LOOP
1924 IF (x_available_sl_qty >= p_required_sl_qty) THEN
1925 EXIT;
1926 END IF;
1927
1928 IF ( is_serial_trx_allowed(
1929 p_transaction_type_id
1930 , p_organization_id
1931 , p_inventory_item_id
1932 , l_selected_serials.serial_status(i) ) = 'Y' ) THEN
1933 BEGIN
1934 INSERT INTO inv_msn_gtemp (
1935 serial_identifier
1936 , INVENTORY_ITEM_ID
1937 , organization_id
1938 , Serial_number
1939 , use_flag )
1940 VALUES (
1941 1
1942 , p_inventory_item_id
1943 , p_organization_id
1944 , l_selected_serials.serial_number(i)
1945 , 1 );
1946
1947 -- if the serial number is available, we want to lock it now so that
1948 -- other concurrent sessions would not try to use it
1949 IF ( lock_serial_number(p_inventory_item_id, l_selected_serials.serial_number(i)) ) THEN
1950 -- Move last row pointer of g_output_serial_rows by 1.
1951 g_serial_tbl_ptr := g_serial_tbl_ptr + 1;
1952
1953 -- Another serial number that can be returned.
1954 x_available_sl_qty := x_available_sl_qty + 1;
1955
1956 -- Record the index for the first serial number.
1957 -- This will be returned and also used here.
1958 if (x_available_sl_qty = 1) then
1959 x_serial_index := g_serial_tbl_ptr;
1960 end if;
1961
1962 -- Populate g_output_serial_rows.
1963 -- All serial nos populated in this call will share the same serial
1964 -- identifier value. This is the pl/sql table index of the 1st row in
1965 -- the set of serial numbers populated in this call, in pl/sql table,
1966 -- g_output_serial_rows. It will also be used in the parent
1967 -- autodetailed row to provide a link to g_output_serial_rows. Using
1968 -- a pl/sql table index for a link rather than some random number
1969 -- should make searching easier.
1970
1971 g_output_serial_rows(g_serial_tbl_ptr).serial_identifier := x_serial_index;
1972 g_output_serial_rows(g_serial_tbl_ptr).inventory_item_id := p_inventory_item_id;
1973 g_output_serial_rows(g_serial_tbl_ptr).organization_id := p_organization_id;
1974 g_output_serial_rows(g_serial_tbl_ptr).serial_number := l_selected_serials.serial_number(i);
1975 END IF;
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 IF ( l_debug = 1 ) THEN
1979 print_debug('sn='||l_selected_serials.serial_number(i)||' skipped SQL err: '||SQLERRM(SQLCODE), 1);
1980 END IF;
1981 END;
1982 ELSIF ( l_debug = 1 ) THEN
1983 print_debug('sn='||l_selected_serials.serial_number(i)||' of wrong status='||l_selected_serials.serial_status(i));
1984 END IF;
1985 END LOOP;
1986 END IF;
1987
1988 IF ( l_debug = 1 )THEN
1989 print_debug('exit '||g_pkg_name||'.'||l_api_name);
1990 END IF;
1991 EXCEPTION
1992 WHEN fnd_api.g_exc_error then
1993 x_return_status := fnd_api.g_ret_sts_error;
1994 x_available_sl_qty := 0;
1995 x_serial_index := 0;
1996
1997 IF (l_debug = 1) THEN
1998 print_debug(l_api_name ||' Exc err progress='||l_progress||' SQL err: '||SQLERRM(SQLCODE), 1);
1999 END IF;
2000 WHEN OTHERS THEN
2001 x_return_status := fnd_api.g_ret_sts_unexp_error;
2002 x_available_sl_qty := 0;
2003 x_serial_index := 0;
2004
2005 IF (l_debug = 1) THEN
2006 print_debug(l_api_name ||' Others err progress='||l_progress||' SQL err: '||SQLERRM(SQLCODE), 1);
2007 END IF;
2008
2009 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
2010 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2011 END IF;
2012 END Get_Serial_Numbers;
2013 --
2014 -- insert record into mtl_material_transactions_temp
2015 -- who columns will be derived in the procedure
2016 PROCEDURE insert_mmtt
2017 (
2018 x_return_status OUT NOCOPY VARCHAR2
2019 ,p_mmtt_tbl IN g_mmtt_tbl_type
2020 ,p_mmtt_tbl_size IN INTEGER
2021 )
2022 IS
2023 l_api_name CONSTANT VARCHAR2(30) := 'Insert_MMTT';
2024 l_today DATE;
2025 l_user_id NUMBER;
2026 l_login_id NUMBER;
2027 l_rowid VARCHAR2(20);
2028 BEGIN
2029 --
2030 -- debugging portion
2031 -- can be commented ut for final code
2032 IF inv_pp_debug.is_debug_mode THEN
2033 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
2034 END IF;
2035 -- end of debugging section
2036 --
2037 -- Initialisize API return status to access
2038 x_return_status := fnd_api.g_ret_sts_success;
2039 IF p_mmtt_tbl_size IS NULL OR p_mmtt_tbl_size <1 THEN
2040 RETURN;
2041 END IF;
2042 --
2043 l_today := SYSDATE;
2044 l_user_id := fnd_global.user_id;
2045 l_login_id := fnd_global.login_id;
2046 --
2047 FOR l_counter IN 1..p_mmtt_tbl_size LOOP
2048 print_debug('in insert mmtt '||p_mmtt_tbl(l_counter).transaction_temp_id );
2049 INSERT INTO mtl_material_transactions_temp
2050 (
2051 transaction_header_id ,
2052 transaction_temp_id ,
2053 source_code ,
2054 source_line_id ,
2055 transaction_mode ,
2056 lock_flag ,
2057 last_update_date ,
2058 last_updated_by ,
2059 creation_date ,
2060 created_by ,
2061 last_update_login ,
2062 request_id ,
2063 program_application_id ,
2064 program_id ,
2065 program_update_date ,
2066 inventory_item_id ,
2067 revision ,
2068 organization_id ,
2069 subinventory_code ,
2070 locator_id ,
2071 transaction_quantity ,
2072 primary_quantity ,
2073 secondary_transaction_quantity ,
2074 transaction_uom ,
2075 secondary_uom_code ,
2076 transaction_cost ,
2077 transaction_type_id ,
2078 transaction_action_id ,
2079 transaction_source_type_id ,
2080 transaction_source_id ,
2081 transaction_source_name ,
2082 transaction_date ,
2083 acct_period_id ,
2084 distribution_account_id ,
2085 transaction_reference ,
2086 requisition_line_id ,
2087 requisition_distribution_id ,
2088 reason_id ,
2089 lot_number ,
2090 lot_expiration_date ,
2091 serial_number ,
2092 receiving_document ,
2093 demand_id ,
2094 rcv_transaction_id ,
2095 move_transaction_id ,
2096 completion_transaction_id ,
2097 wip_entity_type ,
2098 schedule_id ,
2099 repetitive_line_id ,
2100 employee_code ,
2101 primary_switch ,
2102 schedule_update_code ,
2103 setup_teardown_code ,
2104 item_ordering ,
2105 negative_req_flag ,
2106 operation_seq_num ,
2107 picking_line_id ,
2108 trx_source_line_id ,
2109 trx_source_delivery_id ,
2110 physical_adjustment_id ,
2111 cycle_count_id ,
2112 rma_line_id ,
2113 customer_ship_id ,
2114 currency_code ,
2115 currency_conversion_rate ,
2116 currency_conversion_type ,
2117 currency_conversion_date ,
2118 ussgl_transaction_code ,
2119 vendor_lot_number ,
2120 encumbrance_account ,
2121 encumbrance_amount ,
2122 ship_to_location ,
2123 shipment_number ,
2124 transfer_cost ,
2125 transportation_cost ,
2126 transportation_account ,
2127 freight_code ,
2128 containers ,
2129 waybill_airbill ,
2130 expected_arrival_date ,
2131 transfer_subinventory ,
2132 transfer_organization ,
2133 transfer_to_location ,
2134 new_average_cost ,
2135 value_change ,
2136 percentage_change ,
2137 material_allocation_temp_id ,
2138 demand_source_header_id ,
2139 demand_source_line ,
2140 demand_source_delivery ,
2141 item_segments ,
2142 item_description ,
2143 item_trx_enabled_flag ,
2144 item_location_control_code ,
2145 item_restrict_subinv_code ,
2146 item_restrict_locators_code ,
2147 item_revision_qty_control_code ,
2148 item_primary_uom_code ,
2149 item_uom_class ,
2150 item_shelf_life_code ,
2151 item_shelf_life_days ,
2152 item_lot_control_code ,
2153 item_serial_control_code ,
2154 item_inventory_asset_flag ,
2155 allowed_units_lookup_code ,
2156 department_id ,
2157 department_code ,
2158 wip_supply_type ,
2159 supply_subinventory ,
2160 supply_locator_id ,
2161 valid_subinventory_flag ,
2162 valid_locator_flag ,
2163 locator_segments ,
2164 current_locator_control_code ,
2165 number_of_lots_entered ,
2166 wip_commit_flag ,
2167 next_lot_number ,
2168 lot_alpha_prefix ,
2169 next_serial_number ,
2170 serial_alpha_prefix ,
2171 shippable_flag ,
2172 posting_flag ,
2173 required_flag ,
2174 process_flag ,
2175 error_code ,
2176 error_explanation ,
2177 attribute_category ,
2178 attribute1 ,
2179 attribute2 ,
2180 attribute3 ,
2181 attribute4 ,
2182 attribute5 ,
2183 attribute6 ,
2184 attribute7 ,
2185 attribute8 ,
2186 attribute9 ,
2187 attribute10 ,
2188 attribute11 ,
2189 attribute12 ,
2190 attribute13 ,
2191 attribute14 ,
2192 attribute15 ,
2193 movement_id ,
2194 reservation_quantity ,
2195 shipped_quantity ,
2196 transaction_line_number ,
2197 task_id ,
2198 to_task_id ,
2199 source_task_id ,
2200 project_id ,
2201 source_project_id ,
2202 pa_expenditure_org_id ,
2203 to_project_id ,
2204 expenditure_type ,
2205 final_completion_flag ,
2206 transfer_percentage ,
2207 transaction_sequence_id ,
2208 material_account ,
2209 material_overhead_account ,
2210 resource_account ,
2211 outside_processing_account ,
2212 overhead_account ,
2213 flow_schedule ,
2214 cost_group_id ,
2215 demand_class ,
2216 qa_collection_id ,
2217 kanban_card_id ,
2218 overcompletion_transaction_id ,
2219 overcompletion_primary_qty ,
2220 overcompletion_transaction_qty ,
2221 end_item_unit_number ,
2222 scheduled_payback_date ,
2223 line_type_code ,
2224 parent_transaction_temp_id ,
2225 put_away_strategy_id ,
2226 put_away_rule_id ,
2227 pick_strategy_id ,
2228 pick_rule_id ,
2229 common_bom_seq_id ,
2230 common_routing_seq_id ,
2231 cost_type_id ,
2232 org_cost_group_id ,
2233 move_order_line_id ,
2234 task_group_id ,
2235 pick_slip_number ,
2236 reservation_id ,
2237 transaction_status ,
2238 transfer_cost_group_id ,
2239 lpn_id ,
2240 wms_task_type ,
2241 allocated_lpn_id ,
2242 move_order_header_id ,
2243 serial_allocated_flag ,
2244 wms_task_status ,
2245 task_priority ,
2246 fulfillment_base
2247 )
2248 VALUES
2249 (
2250 p_mmtt_tbl(l_counter).transaction_header_id
2251 ,p_mmtt_tbl(l_counter).transaction_temp_id
2252 ,p_mmtt_tbl(l_counter).source_code
2253 ,p_mmtt_tbl(l_counter).source_line_id
2254 ,p_mmtt_tbl(l_counter).transaction_mode
2255 ,p_mmtt_tbl(l_counter).lock_flag
2256 ,l_today
2257 ,l_user_id
2258 ,l_today
2259 ,l_user_id
2260 ,l_login_id
2261 ,p_mmtt_tbl(l_counter).request_id
2262 ,p_mmtt_tbl(l_counter).program_application_id
2263 ,p_mmtt_tbl(l_counter).program_id
2264 ,p_mmtt_tbl(l_counter).program_update_date
2265 ,p_mmtt_tbl(l_counter).inventory_item_id
2266 ,p_mmtt_tbl(l_counter).revision
2267 ,p_mmtt_tbl(l_counter).organization_id
2268 ,p_mmtt_tbl(l_counter).subinventory_code
2269 ,p_mmtt_tbl(l_counter).locator_id
2270 ,p_mmtt_tbl(l_counter).transaction_quantity
2271 ,p_mmtt_tbl(l_counter).primary_quantity
2272 ,p_mmtt_tbl(l_counter).secondary_transaction_quantity
2273 ,p_mmtt_tbl(l_counter).transaction_uom
2274 ,p_mmtt_tbl(l_counter).secondary_uom_code
2275 ,p_mmtt_tbl(l_counter).transaction_cost
2276 ,p_mmtt_tbl(l_counter).transaction_type_id
2277 ,p_mmtt_tbl(l_counter).transaction_action_id
2278 ,p_mmtt_tbl(l_counter).transaction_source_type_id
2279 ,p_mmtt_tbl(l_counter).transaction_source_id
2280 ,p_mmtt_tbl(l_counter).transaction_source_name
2281 ,p_mmtt_tbl(l_counter).transaction_date
2282 ,p_mmtt_tbl(l_counter).acct_period_id
2283 ,p_mmtt_tbl(l_counter).distribution_account_id
2284 ,p_mmtt_tbl(l_counter).transaction_reference
2285 ,p_mmtt_tbl(l_counter).requisition_line_id
2286 ,p_mmtt_tbl(l_counter).requisition_distribution_id
2287 ,p_mmtt_tbl(l_counter).reason_id
2288 ,p_mmtt_tbl(l_counter).lot_number
2289 ,p_mmtt_tbl(l_counter).lot_expiration_date
2290 ,p_mmtt_tbl(l_counter).serial_number
2291 ,p_mmtt_tbl(l_counter).receiving_document
2292 ,p_mmtt_tbl(l_counter).demand_id
2293 ,p_mmtt_tbl(l_counter).rcv_transaction_id
2294 ,p_mmtt_tbl(l_counter).move_transaction_id
2295 ,p_mmtt_tbl(l_counter).completion_transaction_id
2296 ,p_mmtt_tbl(l_counter).wip_entity_type
2297 ,p_mmtt_tbl(l_counter).schedule_id
2298 ,p_mmtt_tbl(l_counter).repetitive_line_id
2299 ,p_mmtt_tbl(l_counter).employee_code
2300 ,p_mmtt_tbl(l_counter).primary_switch
2301 ,p_mmtt_tbl(l_counter).schedule_update_code
2302 ,p_mmtt_tbl(l_counter).setup_teardown_code
2303 ,p_mmtt_tbl(l_counter).item_ordering
2304 ,p_mmtt_tbl(l_counter).negative_req_flag
2305 ,p_mmtt_tbl(l_counter).operation_seq_num
2306 ,p_mmtt_tbl(l_counter).picking_line_id
2307 ,p_mmtt_tbl(l_counter).trx_source_line_id
2308 ,p_mmtt_tbl(l_counter).trx_source_delivery_id
2309 ,p_mmtt_tbl(l_counter).physical_adjustment_id
2310 ,p_mmtt_tbl(l_counter).cycle_count_id
2311 ,p_mmtt_tbl(l_counter).rma_line_id
2312 ,p_mmtt_tbl(l_counter).customer_ship_id
2313 ,p_mmtt_tbl(l_counter).currency_code
2314 ,p_mmtt_tbl(l_counter).currency_conversion_rate
2315 ,p_mmtt_tbl(l_counter).currency_conversion_type
2316 ,p_mmtt_tbl(l_counter).currency_conversion_date
2317 ,p_mmtt_tbl(l_counter).ussgl_transaction_code
2318 ,p_mmtt_tbl(l_counter).vendor_lot_number
2319 ,p_mmtt_tbl(l_counter).encumbrance_account
2320 ,p_mmtt_tbl(l_counter).encumbrance_amount
2321 ,p_mmtt_tbl(l_counter).ship_to_location
2322 ,p_mmtt_tbl(l_counter).shipment_number
2323 ,p_mmtt_tbl(l_counter).transfer_cost
2324 ,p_mmtt_tbl(l_counter).transportation_cost
2325 ,p_mmtt_tbl(l_counter).transportation_account
2326 ,p_mmtt_tbl(l_counter).freight_code
2327 ,p_mmtt_tbl(l_counter).containers
2328 ,p_mmtt_tbl(l_counter).waybill_airbill
2329 ,p_mmtt_tbl(l_counter).expected_arrival_date
2330 ,p_mmtt_tbl(l_counter).transfer_subinventory
2331 ,p_mmtt_tbl(l_counter).transfer_organization
2332 ,p_mmtt_tbl(l_counter).transfer_to_location
2333 ,p_mmtt_tbl(l_counter).new_average_cost
2334 ,p_mmtt_tbl(l_counter).value_change
2335 ,p_mmtt_tbl(l_counter).percentage_change
2336 ,p_mmtt_tbl(l_counter).material_allocation_temp_id
2337 ,p_mmtt_tbl(l_counter).demand_source_header_id
2338 ,p_mmtt_tbl(l_counter).demand_source_line
2339 ,p_mmtt_tbl(l_counter).demand_source_delivery
2340 ,p_mmtt_tbl(l_counter).item_segments
2341 ,p_mmtt_tbl(l_counter).item_description
2342 ,p_mmtt_tbl(l_counter).item_trx_enabled_flag
2343 ,p_mmtt_tbl(l_counter).item_location_control_code
2344 ,p_mmtt_tbl(l_counter).item_restrict_subinv_code
2345 ,p_mmtt_tbl(l_counter).item_restrict_locators_code
2346 ,p_mmtt_tbl(l_counter).item_revision_qty_control_code
2347 ,p_mmtt_tbl(l_counter).item_primary_uom_code
2348 ,p_mmtt_tbl(l_counter).item_uom_class
2349 ,p_mmtt_tbl(l_counter).item_shelf_life_code
2350 ,p_mmtt_tbl(l_counter).item_shelf_life_days
2351 ,p_mmtt_tbl(l_counter).item_lot_control_code
2352 ,p_mmtt_tbl(l_counter).item_serial_control_code
2353 ,p_mmtt_tbl(l_counter).item_inventory_asset_flag
2354 ,p_mmtt_tbl(l_counter).allowed_units_lookup_code
2355 ,p_mmtt_tbl(l_counter).department_id
2356 ,p_mmtt_tbl(l_counter).department_code
2357 ,p_mmtt_tbl(l_counter).wip_supply_type
2358 ,p_mmtt_tbl(l_counter).supply_subinventory
2359 ,p_mmtt_tbl(l_counter).supply_locator_id
2360 ,p_mmtt_tbl(l_counter).valid_subinventory_flag
2361 ,p_mmtt_tbl(l_counter).valid_locator_flag
2362 ,p_mmtt_tbl(l_counter).locator_segments
2363 ,p_mmtt_tbl(l_counter).current_locator_control_code
2364 ,p_mmtt_tbl(l_counter).number_of_lots_entered
2365 ,p_mmtt_tbl(l_counter).wip_commit_flag
2366 ,p_mmtt_tbl(l_counter).next_lot_number
2367 ,p_mmtt_tbl(l_counter).lot_alpha_prefix
2368 ,p_mmtt_tbl(l_counter).next_serial_number
2369 ,p_mmtt_tbl(l_counter).serial_alpha_prefix
2370 ,p_mmtt_tbl(l_counter).shippable_flag
2371 ,p_mmtt_tbl(l_counter).posting_flag
2372 ,p_mmtt_tbl(l_counter).required_flag
2373 ,p_mmtt_tbl(l_counter).process_flag
2374 ,p_mmtt_tbl(l_counter).error_code
2375 ,p_mmtt_tbl(l_counter).error_explanation
2376 ,p_mmtt_tbl(l_counter).attribute_category
2377 ,p_mmtt_tbl(l_counter).attribute1
2378 ,p_mmtt_tbl(l_counter).attribute2
2379 ,p_mmtt_tbl(l_counter).attribute3
2380 ,p_mmtt_tbl(l_counter).attribute4
2381 ,p_mmtt_tbl(l_counter).attribute5
2382 ,p_mmtt_tbl(l_counter).attribute6
2383 ,p_mmtt_tbl(l_counter).attribute7
2384 ,p_mmtt_tbl(l_counter).attribute8
2385 ,p_mmtt_tbl(l_counter).attribute9
2386 ,p_mmtt_tbl(l_counter).attribute10
2387 ,p_mmtt_tbl(l_counter).attribute11
2388 ,p_mmtt_tbl(l_counter).attribute12
2389 ,p_mmtt_tbl(l_counter).attribute13
2390 ,p_mmtt_tbl(l_counter).attribute14
2391 ,p_mmtt_tbl(l_counter).attribute15
2392 ,p_mmtt_tbl(l_counter).movement_id
2393 ,p_mmtt_tbl(l_counter).reservation_quantity
2394 ,p_mmtt_tbl(l_counter).shipped_quantity
2395 ,p_mmtt_tbl(l_counter).transaction_line_number
2396 ,p_mmtt_tbl(l_counter).task_id
2397 ,p_mmtt_tbl(l_counter).to_task_id
2398 ,p_mmtt_tbl(l_counter).source_task_id
2399 ,p_mmtt_tbl(l_counter).project_id
2400 ,p_mmtt_tbl(l_counter).source_project_id
2401 ,p_mmtt_tbl(l_counter).pa_expenditure_org_id
2402 ,p_mmtt_tbl(l_counter).to_project_id
2403 ,p_mmtt_tbl(l_counter).expenditure_type
2404 ,p_mmtt_tbl(l_counter).final_completion_flag
2405 ,p_mmtt_tbl(l_counter).transfer_percentage
2406 ,p_mmtt_tbl(l_counter).transaction_sequence_id
2407 ,p_mmtt_tbl(l_counter).material_account
2408 ,p_mmtt_tbl(l_counter).material_overhead_account
2409 ,p_mmtt_tbl(l_counter).resource_account
2410 ,p_mmtt_tbl(l_counter).outside_processing_account
2411 ,p_mmtt_tbl(l_counter).overhead_account
2412 ,p_mmtt_tbl(l_counter).flow_schedule
2413 ,p_mmtt_tbl(l_counter).cost_group_id
2414 ,p_mmtt_tbl(l_counter).demand_class
2415 ,p_mmtt_tbl(l_counter).qa_collection_id
2416 ,p_mmtt_tbl(l_counter).kanban_card_id
2417 ,p_mmtt_tbl(l_counter).overcompletion_transaction_id
2418 ,p_mmtt_tbl(l_counter).overcompletion_primary_qty
2419 ,p_mmtt_tbl(l_counter).overcompletion_transaction_qty
2420 ,p_mmtt_tbl(l_counter).end_item_unit_number
2421 ,p_mmtt_tbl(l_counter).scheduled_payback_date
2422 ,p_mmtt_tbl(l_counter).line_type_code
2423 ,p_mmtt_tbl(l_counter).parent_transaction_temp_id
2424 ,p_mmtt_tbl(l_counter).put_away_strategy_id
2425 ,p_mmtt_tbl(l_counter).put_away_rule_id
2426 ,p_mmtt_tbl(l_counter).pick_strategy_id
2427 ,p_mmtt_tbl(l_counter).pick_rule_id
2428 ,p_mmtt_tbl(l_counter).common_bom_seq_id
2429 ,p_mmtt_tbl(l_counter).common_routing_seq_id
2430 ,p_mmtt_tbl(l_counter).cost_type_id
2431 ,p_mmtt_tbl(l_counter).org_cost_group_id
2432 ,p_mmtt_tbl(l_counter).move_order_line_id
2433 ,p_mmtt_tbl(l_counter).task_group_id
2434 ,p_mmtt_tbl(l_counter).pick_slip_number
2435 ,p_mmtt_tbl(l_counter).reservation_id
2436 ,p_mmtt_tbl(l_counter).transaction_status
2437 ,p_mmtt_tbl(l_counter).transfer_cost_group_id
2438 ,p_mmtt_tbl(l_counter).lpn_id
2439 ,p_mmtt_tbl(l_counter).wms_task_type
2440 ,p_mmtt_tbl(l_counter).allocated_lpn_id
2441 ,p_mmtt_tbl(l_counter).move_order_header_id
2442 ,p_mmtt_tbl(l_counter).serial_allocated_flag
2443 ,p_mmtt_tbl(l_counter).wms_task_status
2444 ,p_mmtt_tbl(l_counter).task_priority
2445 ,p_mmtt_tbl(l_counter).fulfillment_base
2446 );
2447 END LOOP;
2448 --
2449 -- debugging portion
2450 -- can be commented ut for final code
2451 IF inv_pp_debug.is_debug_mode THEN
2452 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
2453 END IF;
2454 -- end of debugging section
2455 --
2456 EXCEPTION
2457 when fnd_api.g_exc_error then
2458 --
2459 -- debugging portion
2460 -- can be commented ut for final code
2461 IF inv_pp_debug.is_debug_mode THEN
2462 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2463 -- the message retrieved here since it is no longer on the stack
2464 inv_pp_debug.set_last_error_message(Sqlerrm);
2465 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2466 inv_pp_debug.send_last_error_message;
2467 END IF;
2468 -- end of debugging section
2469 --
2470 x_return_status := fnd_api.g_ret_sts_error;
2471 --
2472 when fnd_api.g_exc_unexpected_error then
2473 --
2474 -- debugging portion
2475 -- can be commented ut for final code
2476 IF inv_pp_debug.is_debug_mode THEN
2477 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2478 -- the message retrieved here since it is no longer on the stack
2479 inv_pp_debug.set_last_error_message(Sqlerrm);
2480 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2481 inv_pp_debug.send_last_error_message;
2482 END IF;
2483 -- end of debugging section
2484 --
2485 x_return_status := fnd_api.g_ret_sts_unexp_error;
2486 --
2487 when others then
2488 --
2489 -- debugging portion
2490 -- can be commented ut for final code
2491 IF inv_pp_debug.is_debug_mode THEN
2492 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2493 -- the message retrieved here since it is no longer on the stack
2494 inv_pp_debug.set_last_error_message(Sqlerrm);
2495 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2496 inv_pp_debug.send_last_error_message;
2497 END IF;
2498 -- end of debugging section
2499 --
2500 x_return_status := fnd_api.g_ret_sts_unexp_error;
2501 if fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) then
2502 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2503 end if;
2504 --
2505 END insert_mmtt;
2506 --
2507 -- insert record into mtl_transaction_lots_temp
2508 -- who columns will be derived in the procedure
2509 PROCEDURE insert_mtlt
2510 (
2511 x_return_status OUT NOCOPY VARCHAR2
2512 ,p_mtlt_tbl IN g_mtlt_tbl_type
2513 ,p_mtlt_tbl_size IN INTEGER
2514 )
2515 IS
2516 l_api_name CONSTANT VARCHAR2(30) := 'Insert_MTLT';
2517 l_today DATE;
2518 l_user_id NUMBER;
2519 l_login_id NUMBER;
2520 l_rowid VARCHAR2(20);
2521 BEGIN
2522 --
2523 -- debugging portion
2524 -- can be commented ut for final code
2525 IF inv_pp_debug.is_debug_mode THEN
2526 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
2527 END IF;
2528 -- end of debugging section
2529 --
2530 -- Initialisize API return status to access
2531 x_return_status := fnd_api.g_ret_sts_success;
2532 IF p_mtlt_tbl_size IS NULL OR p_mtlt_tbl_size < 1 THEN
2533 RETURN;
2534 END IF;
2535 --
2536 l_today := SYSDATE;
2537 l_user_id := fnd_global.user_id;
2538 l_login_id := fnd_global.login_id;
2539 FOR l_counter IN 1..p_mtlt_tbl_size LOOP
2540 INSERT INTO mtl_transaction_lots_temp
2541 (
2542 transaction_temp_id
2543 ,last_update_date
2544 ,last_updated_by
2545 ,creation_date
2546 ,created_by
2547 ,last_update_login
2548 ,request_id
2549 ,program_application_id
2550 ,program_id
2551 ,program_update_date
2552 ,transaction_quantity
2553 ,primary_quantity
2554 ,secondary_quantity
2555 ,secondary_unit_of_measure -- Bug 8217560
2556 ,lot_number
2557 ,lot_expiration_date
2558 ,error_code
2559 ,serial_transaction_temp_id
2560 ,group_header_id
2561 ,put_away_rule_id
2562 ,pick_rule_id
2563 ,lot_attribute_category
2564 ,attribute_category
2565 ,attribute1
2566 ,attribute2
2567 ,attribute3
2568 ,attribute4
2569 ,attribute5
2570 ,attribute6
2571 ,attribute7
2572 ,attribute8
2573 ,attribute9
2574 ,attribute10
2575 ,attribute11
2576 ,attribute12
2577 ,attribute13
2578 ,attribute14
2579 ,attribute15
2580 ,c_attribute1
2581 ,c_attribute2
2582 ,c_attribute3
2583 ,c_attribute4
2584 ,c_attribute5
2585 ,c_attribute6
2586 ,c_attribute7
2587 ,c_attribute8
2588 ,c_attribute9
2589 ,c_attribute10
2590 ,c_attribute11
2591 ,c_attribute12
2592 ,c_attribute13
2593 ,c_attribute14
2594 ,c_attribute15
2595 ,c_attribute16
2596 ,c_attribute17
2597 ,c_attribute18
2598 ,c_attribute19
2599 ,c_attribute20
2600 ,n_attribute1
2601 ,n_attribute2
2602 ,n_attribute3
2603 ,n_attribute4
2604 ,n_attribute5
2605 ,n_attribute6
2606 ,n_attribute7
2607 ,n_attribute8
2608 ,n_attribute9
2609 ,n_attribute10
2610 ,d_attribute1
2611 ,d_attribute2
2612 ,d_attribute3
2613 ,d_attribute4
2614 ,d_attribute5
2615 ,d_attribute6
2616 ,d_attribute7
2617 ,d_attribute8
2618 ,d_attribute9
2619 ,d_attribute10
2620 ,grade_code
2621 ,origination_date
2622 ,date_code
2623 ,change_date
2624 ,age
2625 ,retest_date
2626 ,maturity_date
2627 ,item_size
2628 ,color
2629 ,volume
2630 ,volume_uom
2631 ,place_of_origin
2632 ,best_by_date
2633 ,length
2634 ,length_uom
2635 ,recycled_content
2636 ,thickness
2637 ,thickness_uom
2638 ,width
2639 ,width_uom
2640 ,territory_code
2641 ,supplier_lot_number
2642 ,vendor_name
2643 ,vendor_id
2644 ,curl_wrinkle_fold
2645 ,description
2646 ,expiration_action_date
2647 ,expiration_action_code
2648 ,hold_date
2649 )
2650 (SELECT
2651 p_mtlt_tbl(l_counter).transaction_temp_id
2652 ,l_today
2653 ,l_user_id
2654 ,l_today
2655 ,l_user_id
2656 ,l_login_id
2657 ,p_mtlt_tbl(l_counter).request_id
2658 ,p_mtlt_tbl(l_counter).program_application_id
2659 ,p_mtlt_tbl(l_counter).program_id
2660 ,p_mtlt_tbl(l_counter).program_update_date
2661 ,p_mtlt_tbl(l_counter).transaction_quantity
2662 ,p_mtlt_tbl(l_counter).primary_quantity
2663 ,p_mtlt_tbl(l_counter).secondary_quantity
2664 ,p_mtlt_tbl(l_counter).secondary_unit_of_measure --Bug# 8217560
2665 ,p_mtlt_tbl(l_counter).lot_number
2666 ,p_mtlt_tbl(l_counter).lot_expiration_date
2667 ,p_mtlt_tbl(l_counter).error_code
2668 ,p_mtlt_tbl(l_counter).serial_transaction_temp_id
2669 ,p_mtlt_tbl(l_counter).group_header_id
2670 ,p_mtlt_tbl(l_counter).put_away_rule_id
2671 ,p_mtlt_tbl(l_counter).pick_rule_id
2672 ,mln.lot_attribute_category
2673 ,mln.attribute_category
2674 ,mln.attribute1
2675 ,mln.attribute2
2676 ,mln.attribute3
2677 ,mln.attribute4
2678 ,mln.attribute5
2679 ,mln.attribute6
2680 ,mln.attribute7
2681 ,mln.attribute8
2682 ,mln.attribute9
2683 ,mln.attribute10
2684 ,mln.attribute11
2685 ,mln.attribute12
2686 ,mln.attribute13
2687 ,mln.attribute14
2688 ,mln.attribute15
2689 ,mln.c_attribute1
2690 ,mln.c_attribute2
2691 ,mln.c_attribute3
2692 ,mln.c_attribute4
2693 ,mln.c_attribute5
2694 ,mln.c_attribute6
2695 ,mln.c_attribute7
2696 ,mln.c_attribute8
2697 ,mln.c_attribute9
2698 ,mln.c_attribute10
2699 ,mln.c_attribute11
2700 ,mln.c_attribute12
2701 ,mln.c_attribute13
2702 ,mln.c_attribute14
2703 ,mln.c_attribute15
2704 ,mln.c_attribute16
2705 ,mln.c_attribute17
2706 ,mln.c_attribute18
2707 ,mln.c_attribute19
2708 ,mln.c_attribute20
2709 ,mln.n_attribute1
2710 ,mln.n_attribute2
2711 ,mln.n_attribute3
2712 ,mln.n_attribute4
2713 ,mln.n_attribute5
2714 ,mln.n_attribute6
2715 ,mln.n_attribute7
2716 ,mln.n_attribute8
2717 ,mln.n_attribute9
2718 ,mln.n_attribute10
2719 ,mln.d_attribute1
2720 ,mln.d_attribute2
2721 ,mln.d_attribute3
2722 ,mln.d_attribute4
2723 ,mln.d_attribute5
2724 ,mln.d_attribute6
2725 ,mln.d_attribute7
2726 ,mln.d_attribute8
2727 ,mln.d_attribute9
2728 ,mln.d_attribute10
2729 ,mln.grade_code
2730 ,mln.origination_date
2731 ,mln.date_code
2732 ,mln.change_date
2733 ,mln.age
2734 ,mln.retest_date
2735 ,mln.maturity_date
2736 ,mln.item_size
2737 ,mln.color
2738 ,mln.volume
2739 ,mln.volume_uom
2740 ,mln.place_of_origin
2741 ,mln.best_by_date
2742 ,mln.length
2743 ,mln.length_uom
2744 ,mln.recycled_content
2745 ,mln.thickness
2746 ,mln.thickness_uom
2747 ,mln.width
2748 ,mln.width_uom
2749 ,mln.territory_code
2750 ,mln.supplier_lot_number
2751 ,mln.vendor_name
2752 ,mln.vendor_id
2753 ,mln.curl_wrinkle_fold
2754 ,mln.description
2755 ,mln.expiration_action_date
2756 ,mln.expiration_action_code
2757 ,mln.hold_date
2758 FROM mtl_material_transactions_temp mmtt,
2759 mtl_lot_numbers mln
2760 WHERE mmtt.transaction_temp_id = p_mtlt_tbl(l_counter).transaction_temp_id
2761 and mln.inventory_item_id = mmtt.inventory_item_id
2762 and mln.organization_id = mmtt.organization_id
2763 and mln.lot_number = p_mtlt_tbl(l_counter).lot_number
2764 );
2765 END LOOP;
2766 --
2767 -- debugging portion
2768 -- can be commented ut for final code
2769 IF inv_pp_debug.is_debug_mode THEN
2770 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
2771 END IF;
2772 -- end of debugging section
2773 --
2774 EXCEPTION
2775 when fnd_api.g_exc_error then
2776 --
2777 -- debugging portion
2778 -- can be commented ut for final code
2779 IF inv_pp_debug.is_debug_mode THEN
2780 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2781 -- the message retrieved here since it is no longer on the stack
2782 inv_pp_debug.set_last_error_message(Sqlerrm);
2783 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2784 inv_pp_debug.send_last_error_message;
2785 END IF;
2786 -- end of debugging section
2787 --
2788 x_return_status := fnd_api.g_ret_sts_error;
2789 --
2790 when fnd_api.g_exc_unexpected_error then
2791 --
2792 -- debugging portion
2793 -- can be commented ut for final code
2794 IF inv_pp_debug.is_debug_mode THEN
2795 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2796 -- the message retrieved here since it is no longer on the stack
2797 inv_pp_debug.set_last_error_message(Sqlerrm);
2798 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2799 inv_pp_debug.send_last_error_message;
2800 END IF;
2801 -- end of debugging section
2802 --
2803 x_return_status := fnd_api.g_ret_sts_unexp_error;
2804 --
2805 when others then
2806 --
2807 -- debugging portion
2808 -- can be commented ut for final code
2809 IF inv_pp_debug.is_debug_mode THEN
2810 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2811 -- the message retrieved here since it is no longer on the stack
2812 inv_pp_debug.set_last_error_message(Sqlerrm);
2813 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2814 inv_pp_debug.send_last_error_message;
2815 END IF;
2816 -- end of debugging section
2817 --
2818 x_return_status := fnd_api.g_ret_sts_unexp_error;
2819 if fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) then
2820 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
2821 end if;
2822 --
2823 END insert_mtlt;
2824 --
2825 -- insert record into mtl_serial_numbers_temp
2826 -- who columns will be derived in the procedure
2827 PROCEDURE insert_msnt
2828 (
2829 x_return_status OUT NOCOPY VARCHAR2
2830 ,p_msnt_tbl IN g_msnt_tbl_type
2831 ,p_msnt_tbl_size IN INTEGER
2832 )
2833 IS
2834 l_api_name CONSTANT VARCHAR2(30) := 'Insert_MSNT';
2835 l_today DATE;
2836 l_user_id NUMBER;
2837 l_login_id NUMBER;
2838 l_rowid VARCHAR2(20);
2839 BEGIN
2840 --
2841 -- debugging portion
2842 -- can be commented ut for final code
2843 IF inv_pp_debug.is_debug_mode THEN
2844 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
2845 END IF;
2846 -- end of debugging section
2847 --
2848 -- Initialisize API return status to access
2849 x_return_status := fnd_api.g_ret_sts_success;
2850 IF p_msnt_tbl_size IS NULL OR p_msnt_tbl_size < 1 THEN
2851 RETURN;
2852 END IF;
2853 --
2854 l_today := SYSDATE;
2855 l_user_id := fnd_global.user_id;
2856 l_login_id := fnd_global.login_id;
2857 FOR l_counter IN 1..p_msnt_tbl_size LOOP
2858 INSERT INTO mtl_serial_numbers_temp
2859 (
2860 transaction_temp_id
2861 ,last_update_date
2862 ,last_updated_by
2863 ,creation_date
2864 ,created_by
2865 ,last_update_login
2866 ,request_id
2867 ,program_application_id
2868 ,program_id
2869 ,program_update_date
2870 ,vendor_serial_number
2871 ,vendor_lot_number
2872 ,fm_serial_number
2873 ,to_serial_number
2874 ,serial_prefix
2875 ,error_code
2876 ,group_header_id
2877 ,parent_serial_number
2878 ,end_item_unit_number
2879 )
2880 VALUES
2881 (
2882 p_msnt_tbl(l_counter).transaction_temp_id
2883 ,l_today
2884 ,l_user_id
2885 ,l_today
2886 ,l_user_id
2887 ,l_login_id
2888 ,p_msnt_tbl(l_counter).request_id
2889 ,p_msnt_tbl(l_counter).program_application_id
2890 ,p_msnt_tbl(l_counter).program_id
2891 ,p_msnt_tbl(l_counter).program_update_date
2892 ,p_msnt_tbl(l_counter).vendor_serial_number
2893 ,p_msnt_tbl(l_counter).vendor_lot_number
2894 ,p_msnt_tbl(l_counter).fm_serial_number
2895 ,p_msnt_tbl(l_counter).to_serial_number
2896 ,p_msnt_tbl(l_counter).serial_prefix
2897 ,p_msnt_tbl(l_counter).error_code
2898 ,p_msnt_tbl(l_counter).group_header_id
2899 ,p_msnt_tbl(l_counter).parent_serial_number
2900 ,p_msnt_tbl(l_counter).end_item_unit_number
2901 );
2902 END LOOP;
2903 --
2904 -- debugging portion
2905 -- can be commented ut for final code
2906 IF inv_pp_debug.is_debug_mode THEN
2907 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
2908 END IF;
2909 -- end of debugging section
2910 --
2911 EXCEPTION
2912 when fnd_api.g_exc_error then
2913 --
2914 -- debugging portion
2915 -- can be commented ut for final code
2916 IF inv_pp_debug.is_debug_mode THEN
2917 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2918 -- the message retrieved here since it is no longer on the stack
2919 inv_pp_debug.set_last_error_message(Sqlerrm);
2920 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2921 inv_pp_debug.send_last_error_message;
2922 END IF;
2923 -- end of debugging section
2924 --
2925 x_return_status := fnd_api.g_ret_sts_error;
2926 --
2927 when fnd_api.g_exc_unexpected_error then
2928 --
2929 -- debugging portion
2930 -- can be commented ut for final code
2931 IF inv_pp_debug.is_debug_mode THEN
2932 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2933 -- the message retrieved here since it is no longer on the stack
2934 inv_pp_debug.set_last_error_message(Sqlerrm);
2935 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2936 inv_pp_debug.send_last_error_message;
2937 END IF;
2938 -- end of debugging section
2939 --
2940 x_return_status := fnd_api.g_ret_sts_unexp_error;
2941 --
2942 when others then
2943 --
2944 -- debugging portion
2945 -- can be commented ut for final code
2946 IF inv_pp_debug.is_debug_mode THEN
2947 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2948 -- the message retrieved here since it is no longer on the stack
2949 inv_pp_debug.set_last_error_message(Sqlerrm);
2950 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2951 inv_pp_debug.send_last_error_message;
2952 END IF;
2953 -- end of debugging section
2954 --
2955 x_return_status := fnd_api.g_ret_sts_unexp_error;
2956 if fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) then
2957 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
2958 end if;
2959 --
2960 END insert_msnt;
2961 --
2962 -- Start of comments
2963 -- Name : split_prefix_num
2964 -- Function : Separates prefix and numeric part of a serial number
2965 -- Pre-reqs : none
2966 -- Parameters :
2967 -- p_serial_number in varchar2
2968 -- p_prefix in/out varchar2 the prefix
2969 -- x_num out varchar2(30) the numeric portion
2970 -- Notes : privat procedure for internal use only
2971 -- needed only once serial numbers are supported
2972 -- End of comments
2973 --
2974 PROCEDURE split_prefix_num
2975 (
2976 p_serial_number IN VARCHAR2
2977 ,p_prefix IN OUT NOCOPY VARCHAR2
2978 ,x_num OUT NOCOPY VARCHAR2
2979 ) is
2980 l_counter number;
2981 BEGIN
2982 IF p_prefix IS NOT NULL THEN
2983 x_num := SUBSTR(p_serial_number,length(p_prefix)+1);
2984 ELSE
2985 l_counter := length(p_serial_number);
2986 WHILE l_counter >= 0 AND SUBSTR(p_serial_number,l_counter,1) >= '0' AND
2987 SUBSTR(p_serial_number,l_counter,1) <= '9'
2988 LOOP
2989 l_counter := l_counter - 1;
2990 END LOOP;
2991 IF l_counter = 0 THEN
2992 p_prefix := NULL;
2993 ELSE
2994 p_prefix := SUBSTR(p_serial_number,1,l_counter);
2995 END IF;
2996 x_num := SUBSTR(p_serial_number,l_counter+1);
2997 END IF;
2998 END split_prefix_num;
2999 --
3000 -- Subtract two serial numbers and return the difference
3001 FUNCTION subtract_serials
3002 (
3003 p_operand1 IN VARCHAR2,
3004 p_operand2 IN VARCHAR2
3005 ) RETURN NUMBER IS
3006 l_prefix1 VARCHAR2(30);
3007 l_prefix2 VARCHAR2(30);
3008 l_num1 NUMBER;
3009 l_num2 NUMBER;
3010 l_return NUMBER;
3011 BEGIN
3012 split_prefix_num(p_operand1,l_prefix1,l_num1);
3013 split_prefix_num(p_operand2,l_prefix2,l_num2);
3014 IF l_prefix1 = l_prefix2
3015 OR l_prefix1 IS NULL AND l_prefix2 IS NULL THEN
3016 l_return := NVL(l_num2,0) - NVL(l_num1,0);
3017 ELSE
3018 l_return := 0;
3019 END IF;
3020 RETURN(l_return);
3021 END subtract_serials;
3022 --
3023 -- get the next val of mtl_material_transactions_s
3024 FUNCTION next_temp_id RETURN NUMBER IS
3025 CURSOR l_cursor IS SELECT mtl_material_transactions_s.NEXTVAL
3026 FROM dual;
3027 l_temp_id NUMBER;
3028 BEGIN
3029 OPEN l_cursor;
3030 FETCH l_cursor INTO l_temp_id;
3031 IF l_cursor%notfound THEN
3032 CLOSE l_cursor;
3033 RAISE no_data_found;
3034 END IF;
3035 CLOSE l_cursor;
3036 RETURN l_temp_id;
3037 END next_temp_id;
3038 --
3039 -- get accounting period for an organization on a specific date, return -1 if
3040 -- no data found
3041 -- (copied from inltgp.ppc)
3042 FUNCTION get_acct_period_id
3043 ( p_organization_id IN NUMBER, p_date IN DATE)
3044 RETURN NUMBER IS
3045 CURSOR l_cur IS
3046 SELECT acct_period_id
3047 FROM org_acct_periods
3048 WHERE period_close_date IS NULL
3049 AND organization_id = p_organization_id
3050 AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Nvl(p_date, Sysdate),p_organization_id)
3051 BETWEEN trunc(period_start_date) and trunc(schedule_close_date)
3052 ORDER BY period_start_date DESC, schedule_close_date ASC;
3053 l_val NUMBER;
3054 BEGIN
3055
3056 IF nvl(g_acct_organization_id,-1) = p_organization_id Then
3057 l_val := g_acct_period_id;
3058 ELSE
3059 OPEN l_cur;
3060 FETCH l_cur INTO l_val;
3061 IF l_cur%notfound THEN
3062 l_val := -1;
3063 END IF;
3064 CLOSE l_cur;
3065 g_acct_period_id := l_val;
3066 g_acct_organization_id := p_organization_id;
3067 END IF;
3068
3069 RETURN l_val;
3070 END get_acct_period_id;
3071 --
3072 --
3073 PROCEDURE init_output_process_tbl
3074 IS
3075 l_api_name VARCHAR2(30) := 'Init_Output_Process_Tbl';
3076 BEGIN
3077 --
3078 -- debugging section
3079 -- can be commented ut for final code
3080 IF inv_pp_debug.is_debug_mode THEN
3081 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
3082 END IF;
3083 -- end of debugging section
3084 --
3085 g_output_process_tbl_size := 0;
3086 g_output_process_tbl.DELETE;
3087 --
3088 -- debugging section
3089 -- can be commented ut for final code
3090 IF inv_pp_debug.is_debug_mode THEN
3091 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
3092 END IF;
3093 -- end of debugging section
3094 --
3095 END init_output_process_tbl;
3096 --
3097 --
3098 PROCEDURE add_output (p_output_process_rec IN g_output_process_rec_type)
3099 IS
3100 BEGIN
3101 -- this procedure has no debug section since it might be called
3102 -- too many times
3103 g_output_process_tbl_size := g_output_process_tbl_size +1;
3104 g_output_process_tbl(g_output_process_tbl_size) := p_output_process_rec;
3105 END add_output;
3106 --
3107 --
3108 -- Description
3109 -- Set the group_mark_id in mtl_serial_numbers for all serial numbers
3110 -- used in the output table to be the move order line id that
3111 -- initiates the detailing request, so that they would not be used
3112 -- by later detailing.
3113 -- Bug #1267029
3114 -- Changed how mark_serial_number works. Now, this procedure
3115 -- is called for each input line in mtl_serial_numbers_temp.
3116 -- It takes the inventory_item_id, group_mark_id, serial number
3117 -- start, and serial number end. It does not look at all the
3118 -- output process records.
3119 PROCEDURE mark_serial_numbers
3120 (p_inventory_item_id IN NUMBER
3121 ,p_group_mark_id IN NUMBER
3122 ,p_serial_number_start IN VARCHAR2
3123 ,p_serial_number_end IN VARCHAR2)
3124 IS
3125 BEGIN
3126 UPDATE mtl_serial_numbers
3127 SET group_mark_id = p_group_mark_id
3128 WHERE inventory_item_id = p_inventory_item_id
3129 AND serial_number between p_serial_number_start and p_serial_number_end;
3130 END mark_serial_numbers;
3131 --
3132 PROCEDURE print_output_process_tbl
3133 (p_request_context IN g_request_context_rec_type)
3134 IS
3135 BEGIN
3136 NULL;
3137 /*
3138 IF p_request_context.transfer_flag THEN
3139 dbms_output.put_line('transfer_flag y');
3140 ELSE
3141 dbms_output.put_line('transfer_flag n');
3142 END IF;
3143 dbms_output.put_line('printing output process table');
3144 FOR l_index IN 1..g_output_process_tbl_size LOOP
3145 dbms_output.put_line('>> revision ' || g_output_process_tbl(l_index).revision );
3146 dbms_output.put_line(' from_subinventory_code ' || g_output_process_tbl(l_index).from_subinventory_code );
3147 dbms_output.put_line(' from_locator_id ' || g_output_process_tbl(l_index).from_locator_id );
3148 dbms_output.put_line(' to_subinventory_code ' || g_output_process_tbl(l_index).to_subinventory_code );
3149 dbms_output.put_line(' to_locator_id ' || g_output_process_tbl(l_index).to_locator_id );
3150 dbms_output.put_line(' lot_number ' || g_output_process_tbl(l_index).lot_number );
3151 dbms_output.put_line(' lot_expiration_date ' || g_output_process_tbl(l_index).lot_expiration_date );
3152 dbms_output.put_line(' serial_number_start ' || g_output_process_tbl(l_index).serial_number_start );
3153 dbms_output.put_line(' serial_number_end ' || g_output_process_tbl(l_index).serial_number_end );
3154 dbms_output.put_line(' transaction_quantity ' || g_output_process_tbl(l_index).transaction_quantity );
3155 dbms_output.put_line(' primary_quantity ' || g_output_process_tbl(l_index).primary_quantity );
3156 dbms_output.put_line(' pick_rule_id ' || g_output_process_tbl(l_index).pick_rule_id );
3157 dbms_output.put_line(' put_away_rule_id ' || g_output_process_tbl(l_index).put_away_rule_id );
3158 dbms_output.put_line(' reservation_id ' || g_output_process_tbl(l_index).reservation_id );
3159 END LOOP;
3160 */
3161 FOR l_index IN 1..g_output_process_tbl_size LOOP
3162 print_debug('>> revision ' || g_output_process_tbl(l_index).revision );
3163 print_debug(' from_subinventory_code ' || g_output_process_tbl(l_index).from_subinventory_code );
3164 print_debug(' from_locator_id ' || g_output_process_tbl(l_index).from_locator_id );
3165 print_debug(' to_subinventory_code ' || g_output_process_tbl(l_index).to_subinventory_code );
3166 print_debug(' to_locator_id ' || g_output_process_tbl(l_index).to_locator_id );
3167 print_debug(' lot_number ' || g_output_process_tbl(l_index).lot_number );
3168 print_debug(' lot_expiration_date ' || g_output_process_tbl(l_index).lot_expiration_date );
3169 print_debug(' serial_number_start ' || g_output_process_tbl(l_index).serial_number_start );
3170 print_debug(' serial_number_end ' || g_output_process_tbl(l_index).serial_number_end );
3171 print_debug(' transaction_quantity ' || g_output_process_tbl(l_index).transaction_quantity );
3172 print_debug(' primary_quantity ' || g_output_process_tbl(l_index).primary_quantity );
3173 print_debug(' secondary_quantity ' || g_output_process_tbl(l_index).secondary_quantity );
3174 print_debug(' pick_rule_id ' || g_output_process_tbl(l_index).pick_rule_id );
3175 print_debug(' put_away_rule_id ' || g_output_process_tbl(l_index).put_away_rule_id );
3176 print_debug(' reservation_id ' || g_output_process_tbl(l_index).reservation_id );
3177 END LOOP;
3178
3179 END print_output_process_tbl;
3180 --
3181 -- update detailed quantity column for reservations
3182 -- Description
3183 -- This needs to be done after process_output procedure is called.
3184 -- It updates the detailed quantity column in the corresponding reservation records
3185 -- to reflect the fact the process_output procedure has created the suggestions in mmtt
3186 PROCEDURE update_detailed_quantities
3187 (x_return_status OUT NOCOPY VARCHAR2)
3188 IS
3189 l_reservation_id NUMBER;
3190 BEGIN
3191 FOR l_index IN 1..g_output_process_tbl_size LOOP
3192 l_reservation_id := g_output_process_tbl(l_index).reservation_id ;
3193 IF l_reservation_id IS NOT NULL THEN
3194 UPDATE mtl_reservations
3195 SET detailed_quantity = detailed_quantity + g_output_process_tbl(l_index).primary_quantity
3196 , secondary_detailed_quantity = secondary_detailed_quantity + g_output_process_tbl(l_index).secondary_quantity
3197 WHERE reservation_id = l_reservation_id;
3198 END IF;
3199 END LOOP;
3200 x_return_status := fnd_api.g_ret_sts_success;
3201 EXCEPTION
3202 WHEN OTHERS THEN
3203 FND_MESSAGE.SET_NAME('INV','INV_UPD_RSV_FAILED');
3204 FND_MSG_PUB.Add;
3205 x_return_status := fnd_api.g_ret_sts_unexp_error;
3206 END update_detailed_quantities;
3207 --
3208 -- create suggestion records.
3209 -- insert into mtl_material_transactions_temp, mtl_serial_numbers_temp,
3210 -- or mtl_transaction_lots_temp
3211 PROCEDURE process_output
3212 (x_return_status OUT NOCOPY VARCHAR2,
3213 p_request_line_rec IN g_request_line_rec_type,
3214 p_request_context IN g_request_context_rec_type,
3215 p_plan_tasks IN BOOLEAN
3216 ) IS
3217 l_api_name CONSTANT VARCHAR2(30) := 'Process_Output';
3218 l_debug NUMBER;
3219 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3220 l_insert_lot BOOLEAN; -- insert a new lot record
3221 l_insert_serial BOOLEAN; -- insert a new serial record
3222 l_insert_txn BOOLEAN; -- insert a new transaction temp record
3223 l_txn_temp_id NUMBER;
3224 l_txn_header_id NUMBER;
3225 l_serial_temp_id NUMBER;
3226 l_txn_temp_qty NUMBER;
3227 l_lot_temp_qty NUMBER;
3228 l_sec_txn_temp_qty NUMBER;
3229 l_sec_lot_temp_qty NUMBER;
3230 l_qty_sign NUMBER;
3231 --
3232 l_mmtt_tbl g_mmtt_tbl_type;
3233 l_mtlt_tbl g_mtlt_tbl_type;
3234 l_msnt_tbl g_msnt_tbl_type;
3235 l_mmtt_tbl_size INTEGER;
3236 l_mtlt_tbl_size INTEGER;
3237 l_msnt_tbl_size INTEGER;
3238 --
3239 l_serial_index_start INTEGER;
3240 l_serial_index_in_loop INTEGER;
3241 l_proj_enabled NUMBER;
3242 l_today DATE;
3243 l_status VARCHAR2(1);
3244 l_msg_data VARCHAR2(2000);
3245 l_msg_count NUMBER;
3246 l_task_priority NUMBER;
3247 --8498798
3248 l_txn_temp_qty_txn_uom NUMBER;
3249 l_lot_temp_qty_txn_uom NUMBER;
3250
3251 l_ten_temp_qty NUMBER; --14192711
3252 l_lot_ten_temp_qty NUMBER; --14192711
3253
3254 l_mo_allocate_lot_flag VARCHAR2(1) := 'Y'; -- Added for 14699845 (Flexible lot allocation)
3255 l_allocate_lot_flag VARCHAR2(1) := 'Y'; -- Added for 14699845 (Flexible lot allocation)
3256 l_reserved_lot VARCHAR2(200) := NULL; -- Added for 14699845 (Flexible lot allocation)
3257
3258 BEGIN
3259 -- debugging section
3260 -- can be commented ut for final code
3261 IF inv_pp_debug.is_debug_mode THEN
3262 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
3263 END IF;
3264 -- end of debugging section
3265 --
3266 IF g_debug IS NULL or NOT INV_CACHE.is_pickrelease THEN
3267 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),2);
3268 END IF;
3269 l_debug := g_debug;
3270
3271 inv_pp_debug.set_debug_mode(g_debug);
3272
3273 IF l_debug = 1 THEN
3274 print_debug('in process output ');
3275 print_output_process_tbl(p_request_context);
3276 END IF;
3277 -- Initialisize API return status to access
3278 x_return_status := fnd_api.g_ret_sts_success;
3279 IF g_output_process_tbl_size IS NULL OR
3280 g_output_process_tbl_size < 1 THEN
3281 RETURN;
3282 END IF;
3283 g_insert_lot_flag := 0;
3284 g_insert_serial_flag := 0;
3285
3286 -- Added for 14699845 (Flexible lot allocation) temporarily
3287 l_mo_allocate_lot_flag := inv_flex_lot_allocation_pub.get_allocate_lot_flag(
3288 p_organization_id => p_request_line_rec.organization_id
3289 , p_move_order_line_id => p_request_line_rec.line_id
3290 , p_inventory_item_id => p_request_line_rec.inventory_item_id
3291 , p_subinventory_code => p_request_line_rec.from_subinventory_code
3292 , p_locator_id => p_request_line_rec.from_locator_id
3293 , p_revision => p_request_line_rec.revision
3294 , p_lpn_id => p_request_line_rec.lpn_id
3295 );
3296
3297 IF l_debug = 1 THEN
3298 print_debug('l_mo_allocate_lot_flag := '||l_mo_allocate_lot_flag);
3299 END IF;
3300
3301 --
3302 -- The following code is commented because
3303 -- the sign for the quantity in mtl_material_transactions_temp
3304 -- and mtl_transaction_lots_temp and mtl_serial_numbers_temp
3305 -- for the suggestion records will be positive always.
3306 -- The reason is that the move order transaction form
3307 -- and api expect the quantity to be positive regardless
3308 -- what transaction (pick/put) it is. The form or the
3309 -- api will actually call a procedure to negate the
3310 -- quantity if necessary, before performing the transactions
3311 --
3312 -- IF p_request_context.transfer_flag -- transfer
3313 -- OR p_request_context.type_code = 1 -- put away
3314 -- THEN
3315 -- l_qty_sign := 1;
3316 -- ELSE
3317 -- l_qty_sign := -1;
3318 -- END IF;
3319 l_qty_sign := 1;
3320 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
3321 INTO l_txn_header_id FROM DUAL;
3322 g_transaction_header_id := l_txn_header_id;
3323 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
3324 INTO l_txn_temp_id FROM DUAL;
3325 IF p_request_context.item_lot_control_code = 2 THEN
3326 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
3327 INTO l_serial_temp_id FROM DUAL;
3328 END IF;
3329 l_mmtt_tbl_size := 0;
3330 l_mtlt_tbl_size := 0;
3331 l_msnt_tbl_size := 0;
3332 IF p_request_context.item_lot_control_code = 2 THEN
3333 l_lot_temp_qty := g_output_process_tbl(1).primary_quantity;
3334 l_sec_lot_temp_qty := g_output_process_tbl(1).secondary_quantity;
3335 END IF;
3336 l_txn_temp_qty := g_output_process_tbl(1).primary_quantity;
3337 l_sec_txn_temp_qty := g_output_process_tbl(1).secondary_quantity;
3338 --IF inv_pp_debug.is_debug_mode THEN
3339 print_debug('process output before loop, lot pri qty '||l_lot_temp_qty);
3340 print_debug('process output before loop, lot sec qty '||l_sec_lot_temp_qty);
3341 print_debug('process output before loop, txn pri qty '||l_txn_temp_qty);
3342 print_debug('process output before loop, txn sec qty '||l_sec_txn_temp_qty);
3343 print_debug('process output before loop, p_request_line_rec.quantity '|| p_request_line_rec.quantity);
3344 print_debug('process output before loop, p_request_line_rec.PRIMARY_quantity '||p_request_line_rec.PRIMARY_quantity);
3345 print_debug('process output before loop, p_request_line_rec.LOT_NUMBER '||p_request_line_rec.LOT_NUMBER); -- Added for 14699845 (Flexible lot allocation)
3346 --END IF;
3347 FOR l_index IN 2..g_output_process_tbl_size+1 LOOP
3348 l_insert_serial := FALSE;
3349 l_insert_lot := FALSE;
3350 l_insert_txn := FALSE;
3351
3352 -- Added for 14699845 (Flexible lot allocation)
3353 IF (g_output_process_tbl.EXISTS(l_index-1)) THEN
3354 l_allocate_lot_flag := NVL(inv_flex_lot_allocation_pub.get_allocate_lot_flag(
3355 p_organization_id => p_request_line_rec.organization_id
3356 , p_move_order_line_id => p_request_line_rec.line_id
3357 , p_inventory_item_id => p_request_line_rec.inventory_item_id
3358 , p_subinventory_code => g_output_process_tbl(l_index-1).from_subinventory_code
3359 , p_locator_id => g_output_process_tbl(l_index-1).from_locator_id
3360 , p_revision => g_output_process_tbl(l_index-1).revision
3361 , p_lpn_id => g_output_process_tbl(l_index-1).lpn_id
3362 ),
3363 l_mo_allocate_lot_flag);
3364
3365 ELSE
3366 l_allocate_lot_flag := l_mo_allocate_lot_flag;
3367 END IF;
3368
3369 IF l_debug = 1 THEN
3370 print_debug('l_allocate_lot_flag := '||l_allocate_lot_flag);
3371 END IF;
3372 --bug 1500614 - added reservation_id to this stmt so that
3373 --we insert only one MMTT record per reservation id
3374 -- bug 2111022 - add cost_group_id to this stmt
3375 --bug 2573353 - remove cost group id from this stmt, as we
3376 -- no longer allocate cost group
3377 -- Modified for 14699845 (Flexible lot allocation)
3378 IF l_index = g_output_process_tbl_size+1
3379 OR (p_request_context.item_revision_control =2
3380 AND g_output_process_tbl(l_index).revision
3381 <> g_output_process_tbl(l_index-1).revision)
3382 OR g_output_process_tbl(l_index).from_subinventory_code <>
3383 g_output_process_tbl(l_index-1).from_subinventory_code
3384 OR NOT((g_output_process_tbl(l_index).from_locator_id IS NULL
3385 AND g_output_process_tbl(l_index-1).from_locator_id IS NULL)
3386 OR (g_output_process_tbl(l_index).from_locator_id IS NOT NULL
3387 AND g_output_process_tbl(l_index-1).from_locator_id IS NOT NULL
3388 AND g_output_process_tbl(l_index).from_locator_id
3389 = g_output_process_tbl(l_index-1).from_locator_id)
3390 )
3391 OR g_output_process_tbl(l_index).to_subinventory_code <>
3392 g_output_process_tbl(l_index-1).to_subinventory_code
3393 OR NOT(g_output_process_tbl(l_index).to_locator_id IS NULL
3394 AND g_output_process_tbl(l_index-1).to_locator_id IS NULL
3395 OR (g_output_process_tbl(l_index).to_locator_id IS NOT NULL
3396 AND g_output_process_tbl(l_index-1).to_locator_id IS NOT NULL
3397 AND g_output_process_tbl(l_index).to_locator_id
3398 = g_output_process_tbl(l_index-1).to_locator_id))
3399 OR NOT((g_output_process_tbl(l_index).reservation_id IS NULL
3400 AND g_output_process_tbl(l_index-1).reservation_id IS NULL)
3401 OR (g_output_process_tbl(l_index).reservation_id IS NOT NULL
3402 AND g_output_process_tbl(l_index-1).reservation_id IS NOT NULL
3403 AND g_output_process_tbl(l_index).reservation_id
3404 = g_output_process_tbl(l_index-1).reservation_id))
3405 OR NOT((g_output_process_tbl(l_index).lpn_id IS NULL
3406 AND g_output_process_tbl(l_index-1).lpn_id IS NULL)
3407 OR NVL(g_output_process_tbl(l_index).lpn_id, -9999)
3408 = NVL(g_output_process_tbl(l_index-1).lpn_id, -9999))
3409 /*
3410 *OR NOT((g_output_process_tbl(l_index).from_cost_group_id IS NULL
3411 * AND g_output_process_tbl(l_index-1).from_cost_group_id IS NULL)
3412 * OR g_output_process_tbl(l_index).from_cost_group_id
3413 * = g_output_process_tbl(l_index-1).from_cost_group_id)
3414 */
3415 THEN
3416 l_insert_txn := TRUE;
3417 END IF;
3418
3419 -- Start of changes Added for 14699845 (Flexible lot allocation)
3420 print_debug('process output, p_request_context.item_lot_control_code '||p_request_context.item_lot_control_code);
3421 IF (l_insert_txn) THEN
3422 print_debug('process output, l_insert_txn TRUE');
3423 ELSE
3424 print_debug('process output, l_insert_txn FALSE');
3425 END IF;
3426 IF (g_output_process_tbl.EXISTS(l_index)) THEN
3427 print_debug('process output, g_output_process_tbl(l_index).lot_number '||g_output_process_tbl(l_index).lot_number);
3428 ELSE
3429 print_debug('process output, g_output_process_tbl(l_index).lot_number doesnt exist');
3430 END IF;
3431 print_debug('process output, l_allocate_lot_flag '||l_allocate_lot_flag);
3432
3433 l_reserved_lot := NULL;
3434
3435 IF (g_output_process_tbl.EXISTS(l_index-1)) THEN
3436 print_debug('process output, g_output_process_tbl(l_index-1).lot_number '||g_output_process_tbl(l_index-1).lot_number);
3437 print_debug('process output, g_output_process_tbl(l_index-1).reservation_id '||g_output_process_tbl(l_index-1).reservation_id);
3438
3439 IF (g_output_process_tbl(l_index-1).reservation_id IS NOT NULL AND l_allocate_lot_flag <> 'Y') THEN
3440 BEGIN
3441 SELECT lot_number
3442 INTO l_reserved_lot
3443 FROM mtl_reservations
3444 WHERE reservation_id = g_output_process_tbl(l_index-1).reservation_id;
3445 EXCEPTION
3446 WHEN No_Data_Found THEN
3447 l_reserved_lot := NULL;
3448 END;
3449 print_debug('process output, l_reserved_lot '||l_reserved_lot);
3450 END IF;
3451 ELSE
3452 print_debug('process output, g_output_process_tbl(l_index-1) doesnt exist or l_allocate_lot_flag <> Y');
3453 END IF;
3454
3455 print_debug('process output, l_reserved_lot at last'||l_reserved_lot);
3456
3457 IF p_request_line_rec.lot_number IS NOT NULL OR l_reserved_lot IS NOT NULL THEN
3458 l_allocate_lot_flag := 'Y';
3459 END IF;
3460
3461 -- End of changes Added for 14699845 (Flexible lot allocation)
3462
3463 IF (p_request_context.item_lot_control_code = 2
3464 AND (l_insert_txn OR
3465 g_output_process_tbl(l_index).lot_number
3466 <> g_output_process_tbl(l_index-1).lot_number
3467 )
3468 AND (l_allocate_lot_flag='Y' OR p_request_context.wms_task_type = 2)) THEN -- Added for 14699845 (Flexible lot allocation)
3469
3470 l_insert_lot := TRUE;
3471 g_insert_lot_flag := 1;
3472 END IF;
3473
3474 -- Start changes for 14699845 (Flexible lot allocation)
3475 IF (l_insert_lot) THEN
3476 print_debug('process output, l_insert_lot TRUE');
3477 ELSE
3478 print_debug('process output, l_insert_lot FALSE');
3479 END IF;
3480
3481 print_debug('process output, g_output_process_tbl(l_index-1).serial_number_start '||g_output_process_tbl(l_index-1).serial_number_start);
3482 print_debug('process output, g_output_process_tbl(l_index-1).serial_number_end '||g_output_process_tbl(l_index-1).serial_number_end);
3483
3484 IF (g_output_process_tbl.EXISTS(l_index)) THEN
3485 print_debug('process output, g_output_process_tbl(l_index).serial_number_start '||g_output_process_tbl(l_index).serial_number_start);
3486 print_debug('process output, g_output_process_tbl(l_index).serial_number_end '||g_output_process_tbl(l_index).serial_number_end);
3487 ELSE
3488 print_debug('process output, g_output_process_tbl(l_index).serial_number_start doesnt exist');
3489 END IF;
3490
3491 print_debug('process output, p_request_context.item_serial_control_code '||p_request_context.item_serial_control_code);
3492 -- End changes for 14699845 (Flexible lot allocation)
3493
3494 -- ugly, will try to clean later
3495 IF (p_request_context.item_serial_control_code IN (2,5,6)
3496 AND g_output_process_tbl(l_index-1).serial_number_start IS NOT NULL
3497 AND (l_insert_txn OR l_insert_lot OR
3498 g_output_process_tbl(l_index).serial_number_start IS NULL
3499 OR g_output_process_tbl(l_index).serial_number_start
3500 <> g_output_process_tbl(l_index-1).serial_number_start)
3501 AND (p_request_context.item_lot_control_code <> 2 OR l_allocate_lot_flag='Y')) -- Added for 14699845 (Flexible lot allocation)
3502 THEN
3503 -- I rather not do the range overlapping comparision in the
3504 -- condition for this if clause since that is overkill.
3505 -- I assume the serial number will not overlap
3506 l_insert_serial := TRUE;
3507 g_insert_serial_flag := 1;
3508 END IF;
3509
3510 -- Added for 14699845 (Flexible lot allocation)
3511 IF (l_insert_serial) THEN
3512 print_debug('process output, l_insert_serial TRUE');
3513 ELSE
3514 print_debug('process output, l_insert_serial FALSE');
3515 END IF;
3516
3517 IF l_insert_serial THEN
3518 l_msnt_tbl_size := l_msnt_tbl_size +1;
3519 l_msnt_tbl(l_msnt_tbl_size).fm_serial_number := g_output_process_tbl(l_index-1).serial_number_start;
3520 l_msnt_tbl(l_msnt_tbl_size).to_serial_number := g_output_process_tbl(l_index-1).serial_number_end;
3521 l_msnt_tbl(l_msnt_tbl_size).end_item_unit_number := p_request_line_rec.unit_number;
3522 l_msnt_tbl(l_msnt_tbl_size).serial_prefix := '1';
3523 IF p_request_context.item_lot_control_code = 2 THEN
3524 l_msnt_tbl(l_msnt_tbl_size).transaction_temp_id := l_serial_temp_id;
3525 ELSE
3526 l_msnt_tbl(l_msnt_tbl_size).transaction_temp_id := l_txn_temp_id;
3527 END IF;
3528 mark_serial_numbers(
3529 p_request_line_rec.inventory_item_id,
3530 l_msnt_tbl(l_msnt_tbl_size).transaction_temp_id,
3531 l_msnt_tbl(l_msnt_tbl_size).fm_serial_number,
3532 l_msnt_tbl(l_msnt_tbl_size).to_serial_number);
3533 END IF;
3534
3535 IF l_insert_lot THEN
3536 l_mtlt_tbl_size := l_mtlt_tbl_size +1;
3537 l_mtlt_tbl(l_mtlt_tbl_size).transaction_temp_id := l_txn_temp_id;
3538 l_mtlt_tbl(l_mtlt_tbl_size).primary_quantity := l_qty_sign * l_lot_temp_qty;
3539 l_mtlt_tbl(l_mtlt_tbl_size).secondary_quantity := l_qty_sign * l_sec_lot_temp_qty;
3540 l_mtlt_tbl(l_mtlt_tbl_size).secondary_unit_of_measure := p_request_context.secondary_uom_code; --Bug#8217560
3541 IF inv_pp_debug.is_debug_mode THEN
3542 print_debug('process output , lot pri qty '||l_lot_temp_qty);
3543 print_debug('process output , lot sec qty '||l_sec_lot_temp_qty);
3544 END IF;
3545 l_mtlt_tbl(l_mtlt_tbl_size).lot_number := g_output_process_tbl(l_index-1).lot_number;
3546 l_mtlt_tbl(l_mtlt_tbl_size).lot_expiration_date := g_output_process_tbl(l_index-1).lot_expiration_date;
3547 If l_insert_serial Then
3548 l_mtlt_tbl(l_mtlt_tbl_size).serial_transaction_temp_id := l_serial_temp_id;
3549 End If;
3550
3551 IF (p_request_context.primary_uom_code = p_request_context.transaction_uom_code) THEN
3552 l_mtlt_tbl(l_mtlt_tbl_size).transaction_quantity := l_qty_sign * l_lot_temp_qty;
3553 ELSIF (p_request_context.secondary_uom_code = p_request_context.transaction_uom_code) THEN
3554 l_mtlt_tbl(l_mtlt_tbl_size).transaction_quantity := l_qty_sign * l_sec_lot_temp_qty;
3555 ELSE
3556 -- Start 8498798: When the suggested qty is in decimals according to Txn Uom then convert the
3557 -- Uom to Primary Uom so that the qty is a integer.
3558
3559 IF((NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S')) THEN
3560 l_lot_temp_qty_txn_uom := inv_convert.inv_um_convert
3561 (
3562 p_request_line_rec.inventory_item_id,
3563 NULL,
3564 l_sec_lot_temp_qty,
3565 p_request_context.secondary_uom_code,
3566 p_request_context.transaction_uom_code,
3567 NULL,
3568 NULL);
3569 ELSE
3570 l_lot_temp_qty_txn_uom := inv_convert.inv_um_convert
3571 (
3572 p_request_line_rec.inventory_item_id,
3573 NULL,
3574 l_lot_temp_qty,
3575 p_request_context.primary_uom_code,
3576 p_request_context.transaction_uom_code,
3577 NULL,
3578 NULL);
3579 END IF;
3580
3581 l_lot_ten_temp_qty := inv_convert.inv_um_convert --14192711
3582 (
3583 p_request_line_rec.inventory_item_id,
3584 10,
3585 p_request_line_rec.quantity,
3586 p_request_context.transaction_uom_code,
3587 p_request_context.primary_uom_code,
3588 NULL,
3589 NULL);
3590
3591 print_debug('process output before mtlt update , l_lot_ten_temp_qty '|| l_lot_ten_temp_qty);
3592 print_debug('process output before mtlt update , l_lot_temp_qty '|| l_lot_temp_qty);
3593 print_debug('process output before mtlt update , l_lot_temp_qty_txn_uom '||l_lot_temp_qty_txn_uom);
3594 print_debug('process output before mtlt update , l_mmtt_tbl(l_mmtt_tbl_size).wms_task_type '||p_request_context.wms_task_type );
3595
3596 IF(abs(l_lot_ten_temp_qty - l_lot_temp_qty) <> 0 AND abs(l_lot_ten_temp_qty - l_lot_temp_qty) < 0.00005 ) THEN --14192711
3597 l_lot_temp_qty_txn_uom := p_request_line_rec.quantity;
3598 print_debug('process output before mtlt update inside if residual, l_lot_temp_qty_txn_uom '||l_lot_temp_qty_txn_uom);
3599 END IF;
3600
3601 l_mtlt_tbl(l_mtlt_tbl_size).transaction_quantity := l_qty_sign * l_lot_temp_qty_txn_uom;
3602
3603 --10067944 -starts / changed decimal precision calc to 5..see bug 13451492
3604 If (Abs(P_Request_Line_Rec.Quantity - Round(l_lot_temp_qty_txn_uom,5) ) < 0.00005 ) Then --13591755
3605 l_mtlt_tbl(l_mtlt_tbl_size).Transaction_Quantity := L_Qty_Sign * P_Request_Line_Rec.Quantity ;
3606 elsIF (l_lot_temp_qty_txn_uom - Trunc(l_lot_temp_qty_txn_uom,5)<0.00005) THEN --If there is decimal dust on txn qty lets clear it off
3607 l_mtlt_tbl(l_mtlt_tbl_size).transaction_quantity := l_qty_sign * trunc( l_lot_temp_qty_txn_uom,5);
3608 ELSIF ( (l_lot_temp_qty_txn_uom - Trunc(l_lot_temp_qty_txn_uom,5)>=0.00005) --txn quantity is decimal
3609 AND p_request_line_rec.primary_quantity = l_lot_temp_qty --but primary qty matches MOL's pri qty
3610 AND p_request_line_rec.quantity = trunc(p_request_line_rec.quantity) --AND mol's txn qty is not decimal
3611 ) THEN --In this case we can take mol's txn qty
3612 l_mtlt_tbl(l_mtlt_tbl_size).transaction_quantity := l_qty_sign * p_request_line_rec.quantity;
3613 ELSIF ( (l_lot_temp_qty_txn_uom - Trunc(l_lot_temp_qty_txn_uom , 5 )>=0.00005) --txn quantity is decimal
3614 AND (l_lot_temp_qty - trunc(l_lot_temp_qty,5) <= 0.00005)) --primary qty is not decimal/ has a decimal dust
3615 THEN --We will use primary qty after cleaning decimal dust
3616 l_mtlt_tbl(l_mtlt_tbl_size).transaction_quantity := l_qty_sign * trunc(l_lot_temp_qty,5);
3617 END IF;
3618 --10067944 -ends.
3619
3620 END IF;
3621
3622 l_mtlt_tbl(l_mtlt_tbl_size).pick_rule_id := g_output_process_tbl(l_index-1).pick_rule_id;
3623 l_mtlt_tbl(l_mtlt_tbl_size).put_away_rule_id := g_output_process_tbl(l_index-1).put_away_rule_id;
3624 END IF;
3625 IF l_insert_txn THEN
3626 l_mmtt_tbl_size := l_mmtt_tbl_size +1;
3627 l_mmtt_tbl(l_mmtt_tbl_size).transaction_header_id := l_txn_header_id;
3628 l_mmtt_tbl(l_mmtt_tbl_size).transaction_temp_id := l_txn_temp_id;
3629 l_mmtt_tbl(l_mmtt_tbl_size).inventory_item_id := p_request_line_rec.inventory_item_id;
3630 l_mmtt_tbl(l_mmtt_tbl_size).revision := g_output_process_tbl(l_index-1).revision;
3631 l_mmtt_tbl(l_mmtt_tbl_size).organization_id := p_request_line_rec.organization_id;
3632 IF p_request_context.transfer_flag -- transfer
3633 OR p_request_context.type_code = 2 -- picking
3634 THEN
3635 l_mmtt_tbl(l_mmtt_tbl_size).subinventory_code := g_output_process_tbl(l_index-1).from_subinventory_code;
3636 l_mmtt_tbl(l_mmtt_tbl_size).locator_id := g_output_process_tbl(l_index-1).from_locator_id;
3637 --bug 2573353 - do not allocate cost group
3638 --l_mmtt_tbl(l_mmtt_tbl_size).cost_group_id := -- g_output_process_tbl(l_index-1).from_cost_group_id;
3639 IF p_request_context.transfer_flag THEN
3640 l_mmtt_tbl(l_mmtt_tbl_size).transfer_subinventory := g_output_process_tbl(l_index-1).to_subinventory_code;
3641 l_mmtt_tbl(l_mmtt_tbl_size).transfer_to_location := g_output_process_tbl(l_index-1).to_locator_id;
3642 --bug 2573353 - do not allocate cost group
3643 --l_mmtt_tbl(l_mmtt_tbl_size).transfer_cost_group_id := -- g_output_process_tbl(l_index-1).to_cost_group_id;
3644 l_mmtt_tbl(l_mmtt_tbl_size).transfer_organization := p_request_line_rec.to_organization_id;
3645 ELSE
3646 l_mmtt_tbl(l_mmtt_tbl_size).transfer_subinventory := NULL;
3647 l_mmtt_tbl(l_mmtt_tbl_size).transfer_to_location := NULL;
3648 l_mmtt_tbl(l_mmtt_tbl_size).transfer_organization := NULL;
3649 l_mmtt_tbl(l_mmtt_tbl_size).transfer_cost_group_id := NULL;
3650 END IF;
3651 -- for transfer or picking only, store the reservation id
3652 -- in the mmtt record
3653 l_mmtt_tbl(l_mmtt_tbl_size).reservation_id := g_output_process_tbl(l_index-1).reservation_id;
3654 l_mmtt_tbl(l_mmtt_tbl_size).allocated_lpn_id := g_output_process_tbl(l_index-1).lpn_id;
3655 ELSE -- put away
3656 l_mmtt_tbl(l_mmtt_tbl_size).subinventory_code :=
3657 g_output_process_tbl(l_index-1).to_subinventory_code;
3658 --bug 2573353 - do not allocate cost group
3659 --bug 2661134/2747315 - for putaway transactions, we need to
3660 -- copy the to cost group on the move order line to the
3661 -- MMTT record
3662 l_mmtt_tbl(l_mmtt_tbl_size).cost_group_id :=
3663 g_output_process_tbl(l_index-1).to_cost_group_id;
3664 l_mmtt_tbl(l_mmtt_tbl_size).locator_id :=
3665 g_output_process_tbl(l_index-1).to_locator_id;
3666 l_mmtt_tbl(l_mmtt_tbl_size).organization_id :=
3667 p_request_line_rec.to_organization_id;
3668 -- 4292416: planned crossdocking
3669 IF p_request_line_rec.crossdock_type = 2
3670 THEN
3671 l_mmtt_tbl(l_mmtt_tbl_size).demand_source_header_id :=
3672 p_request_line_rec.wip_entity_id;
3673 l_mmtt_tbl(l_mmtt_tbl_size).repetitive_line_id :=
3674 p_request_line_rec.repetitive_schedule_id;
3675 l_mmtt_tbl(l_mmtt_tbl_size).operation_seq_num :=
3676 p_request_line_rec.operation_seq_num;
3677 l_mmtt_tbl(l_mmtt_tbl_size).wip_supply_type :=
3678 p_request_line_rec.wip_supply_type;
3679 END IF;
3680 END IF;
3681 l_mmtt_tbl(l_mmtt_tbl_size).primary_quantity:=
3682 l_qty_sign * l_txn_temp_qty;
3683 l_mmtt_tbl(l_mmtt_tbl_size).secondary_transaction_quantity:=
3684 l_qty_sign * l_sec_txn_temp_qty;
3685 IF inv_pp_debug.is_debug_mode THEN
3686 print_debug('process output , txn pri qty '||l_txn_temp_qty);
3687 print_debug('process output , txn sec qty '||l_sec_txn_temp_qty);
3688 inv_pp_debug.send_message_to_pipe('pri qty '|| l_txn_temp_qty);
3689 inv_pp_debug.send_message_to_pipe('qty_sign' || l_qty_sign);
3690 inv_pp_debug.send_message_to_pipe('sec qty '|| l_sec_txn_temp_qty);
3691 END IF;
3692
3693
3694 IF (p_request_context.primary_uom_code = p_request_context.transaction_uom_code ) THEN
3695 l_mmtt_tbl(l_mmtt_tbl_size).transaction_quantity := l_qty_sign * l_txn_temp_qty;
3696 l_mmtt_tbl(l_mmtt_tbl_size).transaction_uom := p_request_context.transaction_uom_code;
3697 ELSIF (p_request_context.secondary_uom_code = p_request_context.transaction_uom_code ) THEN
3698 l_mmtt_tbl(l_mmtt_tbl_size).transaction_quantity := l_qty_sign * l_sec_txn_temp_qty;
3699 l_mmtt_tbl(l_mmtt_tbl_size).transaction_uom := p_request_context.secondary_uom_code;
3700 ELSE
3701 --10067944-starts
3702 print_debug('process output before mmtt update, pri UOM:'||p_request_context.primary_uom_code||',txn uom:'||
3703 p_request_context.transaction_uom_code);
3704 --Start 8498798
3705
3706 IF ((NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S')) THEN
3707 l_txn_temp_qty_txn_uom := inv_convert.inv_um_convert
3708 (
3709 p_request_line_rec.inventory_item_id,
3710 NULL,
3711 l_sec_txn_temp_qty,
3712 p_request_context.secondary_uom_code,
3713 p_request_context.transaction_uom_code,
3714 NULL,
3715 NULL);
3716 ELSE
3717 l_txn_temp_qty_txn_uom := inv_convert.inv_um_convert
3718 (
3719 p_request_line_rec.inventory_item_id,
3720 NULL,
3721 l_txn_temp_qty,
3722 p_request_context.primary_uom_code,
3723 p_request_context.transaction_uom_code,
3724 NULL,
3725 NULL);
3726 END IF;
3727
3728 l_ten_temp_qty := inv_convert.inv_um_convert --14192711
3729 (
3730 p_request_line_rec.inventory_item_id,
3731 10,
3732 p_request_line_rec.quantity,
3733 p_request_context.transaction_uom_code,
3734 p_request_context.primary_uom_code,
3735 NULL,
3736 NULL);
3737
3738 print_debug('process output before mtlt update , l_ten_temp_qty '|| l_ten_temp_qty);
3739 print_debug('process output before mmtt update , l_txn_temp_qty '|| l_txn_temp_qty);
3740 print_debug('process output before mmtt update , l_txn_temp_qty_txn_uom '||l_txn_temp_qty_txn_uom);
3741 print_debug('process output before mmtt update , l_mmtt_tbl(l_mmtt_tbl_size).wms_task_type '||p_request_context.wms_task_type );
3742
3743 IF(abs(l_ten_temp_qty - l_txn_temp_qty) <> 0 AND abs(l_ten_temp_qty - l_txn_temp_qty) < 0.00005 ) THEN --14192711
3744 l_txn_temp_qty_txn_uom := p_request_line_rec.quantity;
3745 print_debug('process output before mtlt update inside if residual, l_txn_temp_qty_txn_uom '||l_txn_temp_qty_txn_uom);
3746 END IF;
3747
3748 l_mmtt_tbl(l_mmtt_tbl_size).transaction_quantity := l_qty_sign * l_txn_temp_qty_txn_uom; --10067944
3749 l_mmtt_tbl(l_mmtt_tbl_size).transaction_uom := p_request_context.transaction_uom_code;
3750
3751 --10067944 -starts / changed decimal precision calc to 5..see bug 13451492
3752 If (Abs(P_Request_Line_Rec.Quantity - Round(L_Txn_Temp_Qty_Txn_Uom,5) ) < 0.00005 ) Then --13591755
3753 L_Mmtt_Tbl(L_Mmtt_Tbl_Size).Transaction_Quantity := L_Qty_Sign * P_Request_Line_Rec.Quantity ;
3754 elsIF (l_txn_temp_qty_txn_uom - Trunc(l_txn_temp_qty_txn_uom,5)<0.00005) THEN --If there is decimal dust on txn qty lets clear it off
3755 l_mmtt_tbl(l_mmtt_tbl_size).transaction_quantity := l_qty_sign * trunc( l_txn_temp_qty_txn_uom,5);
3756 ELSIF ( (l_txn_temp_qty_txn_uom - Trunc(l_txn_temp_qty_txn_uom,5)>=0.00005) --txn quantity is decimal
3757 AND p_request_line_rec.primary_quantity = l_txn_temp_qty --but primary qty matches MOL's pri qty
3758 AND p_request_line_rec.quantity = trunc(p_request_line_rec.quantity) --AND mol's txn qty is not decimal
3759 ) THEN --In this case we can take mol's txn qty
3760 l_mmtt_tbl(l_mmtt_tbl_size).transaction_quantity := l_qty_sign * p_request_line_rec.quantity;
3761 ELSIF ( (l_txn_temp_qty_txn_uom - Trunc(l_txn_temp_qty_txn_uom,5)>=0.00005) --txn quantity is decimal
3762 AND (l_txn_temp_qty - trunc(l_txn_temp_qty,5) <= 0.00005)) --primary qty is not decimal/ has a decimal dust
3763 THEN --We will use primary qty after cleaning decimal dust
3764
3765 l_mmtt_tbl(l_mmtt_tbl_size).transaction_quantity := l_qty_sign * trunc(l_txn_temp_qty,5);
3766 l_mmtt_tbl(l_mmtt_tbl_size).transaction_uom := p_request_context.primary_uom_code;
3767
3768 IF l_index = 2 THEN
3769 UPDATE mtl_txn_request_lines
3770 SET quantity = primary_quantity,
3771 uom_code = p_request_context.primary_uom_code
3772 WHERE line_id = p_request_line_rec.line_id;
3773 END IF;
3774
3775 END IF;
3776 END IF; --10067944 Ends
3777
3778 IF ((NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S')) THEN
3779 l_mmtt_tbl(l_mmtt_tbl_size).fulfillment_base := 'S';
3780 ELSE
3781 l_mmtt_tbl(l_mmtt_tbl_size).fulfillment_base := 'P';
3782 END IF;
3783 print_debug('process output before mmtt update , wms_engine_pvt.g_fulfillment_base value is '|| wms_engine_pvt.g_fulfillment_base);
3784 print_debug('process output before mmtt update , fulfillment_base value is '|| l_mmtt_tbl(l_mmtt_tbl_size).fulfillment_base);
3785
3786 /* jxlu bug 1544670: need to populate column item_primary_uom_code */
3787 l_mmtt_tbl(l_mmtt_tbl_size).item_primary_uom_code := p_request_context.primary_uom_code;
3788 /* end of bug 1544670 */
3789 /* BUG 5338723 - The lpn_id should be set from the output table if it was not supplied on the mo line */
3790 IF p_request_line_rec.lpn_id IS NOT NULL THEN
3791 l_mmtt_tbl(l_mmtt_tbl_size).lpn_id := p_request_line_rec.lpn_id;
3792 ELSE
3793 l_mmtt_tbl(l_mmtt_tbl_size).lpn_id := g_output_process_tbl(l_index-1).lpn_id;
3794 END IF;
3795 /* end of BUG 5338723 */
3796 l_mmtt_tbl(l_mmtt_tbl_size).secondary_uom_code := p_request_context.secondary_uom_code;
3797 --l_mmtt_tbl(l_mmtt_tbl_size).lpn_id := p_request_line_rec.lpn_id;
3798
3799 -- 8498798
3800 -- l_mmtt_tbl(l_mmtt_tbl_size).transaction_uom := p_request_context.transaction_uom_code;
3801
3802 l_mmtt_tbl(l_mmtt_tbl_size).transaction_type_id := p_request_line_rec.transaction_type_id;
3803 l_mmtt_tbl(l_mmtt_tbl_size).transaction_action_id := p_request_context.transaction_action_id;
3804 l_mmtt_tbl(l_mmtt_tbl_size).transaction_source_type_id := p_request_line_rec.transaction_source_type_id;
3805 l_mmtt_tbl(l_mmtt_tbl_size).transaction_source_id := p_request_context.txn_header_id;
3806 l_mmtt_tbl(l_mmtt_tbl_size).trx_source_line_id := p_request_context.txn_line_id;
3807 l_mmtt_tbl(l_mmtt_tbl_size).trx_source_delivery_id := p_request_context.txn_line_detail;
3808 l_mmtt_tbl(l_mmtt_tbl_size).demand_source_line := p_request_context.txn_line_id;
3809 l_mmtt_tbl(l_mmtt_tbl_size).demand_source_delivery := p_request_context.txn_line_detail;
3810 l_mmtt_tbl(l_mmtt_tbl_size).wms_task_type := p_request_context.wms_task_type;
3811 --Add the new column WMS_tASK_STATUS to MMTT
3812 IF p_plan_tasks THEN
3813 -- set status to unreleased if plan_tasks is true
3814 l_mmtt_tbl(l_mmtt_tbl_size).wms_task_status := 8;
3815 ELSE
3816 -- set status to pending if plan_tasks is fasle
3817 l_mmtt_tbl(l_mmtt_tbl_size).wms_task_status := 1;
3818 END IF;
3819
3820 --l_mmtt_tbl(l_mmtt_tbl_size).transaction_source_name :=
3821 -- p_request_line_rec.transaction_source_name;
3822 l_today := nvl(inv_cache.mo_transaction_date, SYSDATE);
3823
3824 l_mmtt_tbl(l_mmtt_tbl_size).transaction_date := l_today;
3825 -- get accounting period id
3826 IF p_request_context.transfer_flag -- transfer
3827 OR p_request_context.type_code = 2 THEN -- picking
3828 l_mmtt_tbl(l_mmtt_tbl_size).acct_period_id :=
3829 get_acct_period_id(p_request_line_rec.organization_id,
3830 l_today);
3831 ELSE
3832 l_mmtt_tbl(l_mmtt_tbl_size).acct_period_id :=
3833 get_acct_period_id(p_request_line_rec.to_organization_id,
3834 l_today);
3835 END IF;
3836 IF l_mmtt_tbl(l_mmtt_tbl_size).acct_period_id = -1 THEN
3837 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
3838 FND_MSG_PUB.add;
3839 x_return_status := FND_API.G_RET_STS_ERROR;
3840 raise FND_API.G_EXC_ERROR;
3841 END IF;
3842 l_mmtt_tbl(l_mmtt_tbl_size).transaction_reference :=
3843 p_request_line_rec.reference;
3844 l_mmtt_tbl(l_mmtt_tbl_size).reason_id :=
3845 p_request_line_rec.reason_id;
3846 -- do not store lot number or lot expiration date in mmtt
3847 -- they are in the lots temp table (mtlt)
3848 l_mmtt_tbl(l_mmtt_tbl_size).lot_number := NULL;
3849 l_mmtt_tbl(l_mmtt_tbl_size).lot_expiration_date := NULL;
3850 l_mmtt_tbl(l_mmtt_tbl_size).serial_number := NULL;
3851 l_mmtt_tbl(l_mmtt_tbl_size).pick_rule_id :=
3852 g_output_process_tbl(l_index-1).pick_rule_id;
3853 l_mmtt_tbl(l_mmtt_tbl_size).put_away_rule_id :=
3854 g_output_process_tbl(l_index-1).put_away_rule_id;
3855 l_mmtt_tbl(l_mmtt_tbl_size).pick_strategy_id :=
3856 p_request_context.pick_strategy_id;
3857 l_mmtt_tbl(l_mmtt_tbl_size).put_away_strategy_id :=
3858 p_request_context.put_away_strategy_id;
3859 l_mmtt_tbl(l_mmtt_tbl_size).posting_flag :=
3860 p_request_context.posting_flag;
3861 l_mmtt_tbl(l_mmtt_tbl_size).process_flag := 'Y';
3862 l_mmtt_tbl(l_mmtt_tbl_size).transaction_status := 2; -- suggestions
3863 -- which column stores the request line id?
3864 l_mmtt_tbl(l_mmtt_tbl_size).move_order_line_id :=
3865 p_request_line_rec.line_id;
3866 /* BUG 3181559: item_lot_control_code and item_serial_control_code need to be populated */
3867 l_mmtt_tbl(l_mmtt_tbl_size).item_lot_control_code :=
3868 p_request_context.item_lot_control_code;
3869 l_mmtt_tbl(l_mmtt_tbl_size).item_serial_control_code :=
3870 p_request_context.item_serial_control_code;
3871 /* New columns move_order_header_id and serial_allocated_flag added to MMTT for Bulk Picking*/
3872 l_mmtt_tbl(l_mmtt_tbl_size).move_order_header_id :=
3873 p_request_line_rec.header_id;
3874 IF l_insert_serial THEN
3875 l_mmtt_tbl(l_mmtt_tbl_size).serial_allocated_flag := 'Y';
3876 ELSIF p_request_context.item_serial_control_code <> 1 THEN
3877 l_mmtt_tbl(l_mmtt_tbl_size).serial_allocated_flag := 'N';
3878 END IF;
3879 -- 4292157: task priority project
3880 l_task_priority := NVL(inv_cache.wpb_rec.task_priority,-1);
3881 IF l_task_priority > 0 AND inv_cache.wms_installed
3882 THEN
3883 l_mmtt_tbl(l_mmtt_tbl_size).task_priority := l_task_priority;
3884 END IF;
3885
3886 -- IF ( WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= INV_RELEASE.G_J_RELEASE_LEVEL) then
3887 -- END IF;
3888 /* bug 2372764 - since we can pick from projects other than the
3889 *project on the move order line, we leave these columns blank
3890 *and let the TM derive the info
3891 l_mmtt_tbl(l_mmtt_tbl_size).project_id :=
3892 p_request_line_rec.project_id;
3893 l_mmtt_tbl(l_mmtt_tbl_size).task_id :=
3894 p_request_line_rec.task_id;
3895 */
3896 --if transaction_action is issue, and transaction_type is
3897 -- project_enabled or transaction source type is WIP,
3898 -- copy task and project into source task and proj
3899 IF p_request_context.transaction_action_id = 1 THEN --issue from stores
3900
3901 IF p_request_line_rec.transaction_source_type_id = 5 THEN --WIP
3902 l_mmtt_tbl(l_mmtt_tbl_size).source_project_id :=
3903 p_request_line_rec.project_id;
3904 l_mmtt_tbl(l_mmtt_tbl_size).source_task_id :=
3905 p_request_line_rec.task_id;
3906 ELSE
3907 IF inv_cache.set_mtt_rec(p_request_line_rec.transaction_type_id) THEN
3908 l_proj_enabled := NVL(inv_cache.mtt_rec.type_class,2);
3909 END IF;
3910
3911 --find out if network logistics is installed
3912 IF g_nl_installed is NULL THEN
3913 g_nl_installed := inv_check_product_install.check_cse_install(
3914 x_return_status => l_status
3915 ,x_msg_count => l_msg_count
3916 ,x_msg_data => l_msg_data);
3917 END IF;
3918
3919 /* Bug 3228686. Move order issue to Project could be performed
3920 * in non-pjm enabled organization
3921 */
3922
3923 IF l_proj_enabled = 1 THEN --1 is enabled, 2 means not enabled
3924 l_mmtt_tbl(l_mmtt_tbl_size).source_project_id :=
3925 p_request_line_rec.project_id;
3926 l_mmtt_tbl(l_mmtt_tbl_size).source_task_id :=
3927 p_request_line_rec.task_id;
3928 END IF;
3929 END IF;
3930
3931 /* Bug : 3622435. Issue transactions should be populated with
3932 * distribution_account_id.
3933 */
3934
3935 l_mmtt_tbl(l_mmtt_tbl_size).distribution_account_id :=
3936 p_request_line_rec.to_account_id;
3937
3938 IF ( l_debug = 1 ) THEN
3939 print_debug('distribution_account_id = '
3940 || l_mmtt_tbl(l_mmtt_tbl_size).distribution_account_id);
3941 END IF;
3942 END IF;
3943 END IF;
3944 IF l_index >= g_output_process_tbl_size+1 THEN
3945 EXIT; -- we are done with the output creation
3946 END IF;
3947 IF (l_insert_lot OR (l_allocate_lot_flag='N' AND p_request_context.wms_task_type <> 2)) THEN -- Added for 14699845 (Flexible lot allocation)
3948 -- reset the quantity for the next mtlt record
3949 l_lot_temp_qty := 0;
3950 l_sec_lot_temp_qty := 0;
3951 -- see whether we need a new l_serial_temp_id
3952 IF p_request_context.item_serial_control_code IN (2,5,6) THEN
3953 -- get the serial temp id if serial control is yes
3954 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
3955 INTO l_serial_temp_id FROM DUAL;
3956 END IF;
3957 END IF;
3958 IF p_request_context.item_lot_control_code = 2 THEN
3959 -- lot control is yes
3960 -- accumulate the quantity from g_output_process_tbl(l_index)
3961 -- for the lot
3962 -- to be inserted next time regardless whether serial number
3963 -- control is yes or no
3964 l_lot_temp_qty := l_lot_temp_qty
3965 + g_output_process_tbl(l_index).primary_quantity;
3966 l_sec_lot_temp_qty := l_sec_lot_temp_qty
3967 + g_output_process_tbl(l_index).secondary_quantity;
3968 END IF;
3969 IF l_insert_txn THEN
3970 -- reset the quantity for the next mmtt record
3971 l_txn_temp_qty := 0;
3972 l_sec_txn_temp_qty := 0;
3973 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
3974 INTO l_txn_temp_id FROM DUAL;
3975 END IF;
3976 -- accumulate the quantity from g_output_process_tbl(l_index)
3977 -- for the next mmtt record
3978 l_txn_temp_qty := l_txn_temp_qty
3979 + g_output_process_tbl(l_index).primary_quantity;
3980 l_sec_txn_temp_qty := l_sec_txn_temp_qty
3981 + g_output_process_tbl(l_index).secondary_quantity;
3982 END LOOP;
3983 IF l_mmtt_tbl_size > 0 THEN
3984 insert_mmtt
3985 (
3986 x_return_status => l_return_status ,
3987 p_mmtt_tbl => l_mmtt_tbl ,
3988 p_mmtt_tbl_size => l_mmtt_tbl_size
3989 );
3990 IF l_return_status = fnd_api.g_ret_sts_error THEN
3991 RAISE fnd_api.g_exc_error;
3992 END IF;
3993 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3994 RAISE fnd_api.g_exc_unexpected_error;
3995 END IF;
3996 END IF;
3997 IF l_mtlt_tbl_size > 0 THEN
3998 insert_mtlt
3999 (
4000 x_return_status => l_return_status ,
4001 p_mtlt_tbl => l_mtlt_tbl ,
4002 p_mtlt_tbl_size => l_mtlt_tbl_size
4003 );
4004 IF l_return_status = fnd_api.g_ret_sts_error THEN
4005 RAISE fnd_api.g_exc_error;
4006 END IF;
4007 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4008 RAISE fnd_api.g_exc_unexpected_error;
4009 END IF;
4010 END IF;
4011 IF l_msnt_tbl_size > 0 THEN
4012 insert_msnt
4013 (
4014 x_return_status => l_return_status ,
4015 p_msnt_tbl => l_msnt_tbl ,
4016 p_msnt_tbl_size => l_msnt_tbl_size
4017 );
4018 IF l_return_status = fnd_api.g_ret_sts_error THEN
4019 RAISE fnd_api.g_exc_error;
4020 END IF;
4021 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4022 RAISE fnd_api.g_exc_unexpected_error;
4023 END IF;
4024 END IF;
4025 IF p_request_context.detail_serial AND
4026 (p_request_context.transfer_flag OR
4027 p_request_context.type_code = 2) THEN
4028 -- in the case of picking or transfer
4029 -- mark all serial numbers inserted above as used
4030 --bug #1267029 - mark serial numbers called once for each
4031 --output record.
4032 --mark_serial_numbers(p_request_line_rec, l_txn_temp_id);
4033 -- clear in memory serial numbers detailing table
4034 init_output_serial_rows;
4035 END IF;
4036 --Bug 1766302
4037 -- Quantity tree was reporting wrong values because we were updating
4038 -- detailed quantity but not updating the quantity tree.
4039 -- Since we update detailed_quantity in INV_PICK_RELEASE_PVT.Process_line,
4040 -- we don't need to do it here.
4041 --update_detailed_quantities(l_return_status);
4042 --IF l_return_status = fnd_api.g_ret_sts_error THEN
4043 -- RAISE fnd_api.g_exc_error;
4044 --END IF;
4045 --IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4046 -- RAISE fnd_api.g_exc_unexpected_error;
4047 --END IF;
4048 x_return_status := l_return_status;
4049 --
4050 -- debugging section
4051 -- can be commented ut for final code
4052 IF inv_pp_debug.is_debug_mode THEN
4053 inv_pp_debug.send_message_to_pipe('=================== create output records: ');
4054 inv_pp_debug.send_message_to_pipe('# of records inserted to mtl_material_transactions_temp: '
4055 || l_mmtt_tbl_size);
4056 inv_pp_debug.send_message_to_pipe('# of records inserted to mtl_transaction_lots_temp: '
4057 || l_mtlt_tbl_size);
4058 inv_pp_debug.send_message_to_pipe('# of records inserted to mtl_serial_numbers_temp: '
4059 || l_msnt_tbl_size);
4060 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
4061 END IF;
4062 -- end of debugging section
4063 --
4064 EXCEPTION
4065 WHEN fnd_api.g_exc_error THEN
4066 --
4067 -- debugging section
4068 -- can be commented ut for final code
4069 IF inv_pp_debug.is_debug_mode THEN
4070 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
4071 -- the message retrieved here since it is no longer on the stack
4072 inv_pp_debug.set_last_error_message(Sqlerrm);
4073 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
4074 inv_pp_debug.send_last_error_message;
4075 END IF;
4076 -- end of debugging section
4077 --
4078 x_return_status := fnd_api.g_ret_sts_error;
4079 --
4080 WHEN fnd_api.g_exc_unexpected_error THEN
4081 --
4082 -- debugging section
4083 -- can be commented ut for final code
4084 IF inv_pp_debug.is_debug_mode THEN
4085 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
4086 -- the message retrieved here since it is no longer on the stack
4087 inv_pp_debug.set_last_error_message(Sqlerrm);
4088 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
4089 inv_pp_debug.send_last_error_message;
4090 END IF;
4091 -- end of debugging section
4092 --
4093 x_return_status := fnd_api.g_ret_sts_unexp_error;
4094 --
4095 WHEN OTHERS THEN
4096 --
4097 -- debugging section
4098 -- can be commented ut for final code
4099 IF inv_pp_debug.is_debug_mode THEN
4100 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
4101 -- the message retrieved here since it is no longer on the stack
4102 inv_pp_debug.set_last_error_message(Sqlerrm);
4103 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
4104 inv_pp_debug.send_last_error_message;
4105 END IF;
4106 -- end of debugging section
4107 --
4108 x_return_status := fnd_api.g_ret_sts_unexp_error;
4109 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4110 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4111 END IF;
4112 --
4113 END process_output;
4114 FUNCTION is_sub_loc_lot_reservable(
4115 p_organization_id IN NUMBER
4116 ,p_inventory_item_id IN NUMBER
4117 ,p_subinventory_code IN VARCHAR2
4118 ,p_locator_id IN NUMBER
4119 ,p_lot_number IN VARCHAR2
4120 ) RETURN BOOLEAN IS
4121 l_return_status VARCHAR2(1);
4122 l_msg_count NUMBER;
4123 l_msg_data VARCHAR2(240);
4124 l_api_name constant varchar(30) := 'is_sub_loc_lot_rsv_allowed';
4125 l_sub_reservable NUMBER;
4126 l_loc_reservable NUMBER;
4127 l_lot_reservable NUMBER;
4128
4129 CURSOR c_sub_status (p_organization_id in NUMBER
4130 , p_subinventory_code in varchar2
4131 )
4132 IS
4133 SELECT decode(reservable_type,2,0,reservable_type)
4134 FROM mtl_secondary_inventories
4135 WHERE organization_id = p_organization_id
4136 AND secondary_inventory_name = p_subinventory_code;
4137
4138 CURSOR c_loc_status (p_organization_id in NUMBER
4139 , p_locator_id in NUMBER
4140 )
4141 IS
4142 SELECT decode(reservable_type,2,0,reservable_type)
4143 FROM mtl_item_locations
4144 WHERE organization_id = p_organization_id
4145 AND inventory_location_id = p_locator_id;
4146
4147 CURSOR c_lot_status (p_organization_id in NUMBER
4148 , p_lot_number in varchar2)
4149 IS
4150 SELECT decode(reservable_type,2,0,reservable_type)
4151 FROM mtl_lot_numbers
4152 WHERE organization_id = p_organization_id
4153 AND inventory_item_id = p_inventory_item_id
4154 AND lot_number = p_lot_number;
4155
4156 BEGIN
4157
4158 l_sub_reservable := 1;
4159 l_lot_reservable := 1;
4160 l_loc_reservable := 1;
4161
4162 print_debug('check sub_lot_loc_reservable p_organization_id '||p_organization_id);
4163 print_debug('check sub_lot_loc_reservable p_subinventory_code '||p_subinventory_code);
4164 print_debug('check sub_lot_loc_reservable p_locator_id '||p_locator_id);
4165 print_debug('check sub_lot_loc_reservable p_lot_number '||p_lot_number);
4166 IF p_subinventory_code IS NOT NULL THEN
4167 OPEN c_sub_status(p_organization_id,p_subinventory_code);
4168
4169 FETCH c_sub_status INTO l_sub_reservable;
4170 IF c_sub_status%NOTFOUND THEN
4171 l_sub_reservable := 0;
4172 END IF;
4173 CLOSE c_sub_status;
4174 print_debug('check sub_lot_loc_reservable l_sub_reservable '||l_sub_reservable);
4175 END IF;
4176
4177 /* check the profile value, if set only sub reservable is checked */
4178
4179 IF p_locator_id IS NOT NULL THEN
4180 --get status
4181 OPEN c_loc_status(p_organization_id, p_locator_id);
4182 FETCH c_loc_status INTO l_loc_reservable;
4183 IF c_loc_status%NOTFOUND THEN
4184 l_loc_reservable := 0;
4185 END IF;
4186 CLOSE c_loc_status;
4187 print_debug('check sub_lot_loc_reservable l_loc_reservable '||l_loc_reservable);
4188 END IF;
4189
4190 if p_lot_number IS NOT NULL THEN
4191 --get status
4192 OPEN c_lot_status(p_organization_id, p_lot_number);
4193 FETCH c_lot_status INTO l_lot_reservable;
4194 IF c_lot_status%NOTFOUND THEN
4195 l_lot_reservable := 0;
4196 END IF;
4197 CLOSE c_lot_status;
4198 print_debug('check sub_lot_loc_reservable l_lot_reservable '||l_lot_reservable);
4199 END IF;
4200
4201 IF (l_sub_reservable * l_loc_reservable * l_lot_reservable = 1 )THEN
4202 print_debug('check sub_lot_loc_reservable returning true ');
4203 return TRUE;
4204 ELSE
4205 print_debug('check sub_lot_loc_reservable returning false ');
4206 return FALSE; -- anything with 0 is a 'N'
4207 END IF;
4208 EXCEPTION
4209 WHEN OTHERS THEN
4210 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4211 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4212 END IF;
4213 fnd_msg_pub.count_and_get( p_count => l_msg_count
4214 ,p_data => l_msg_data );
4215 print_debug('check sub_lot_loc_reservable exception false ');
4216 return FALSE;
4217
4218 END is_sub_loc_lot_reservable;
4219
4220 FUNCTION get_organization_code(
4221 p_organization_id IN NUMBER
4222 ) RETURN VARCHAR2 IS
4223
4224 l_organization_code Varchar2(10);
4225
4226 Cursor get_org (p_organization_id IN NUMBER) is
4227 Select organization_code
4228 from mtl_parameters
4229 Where organization_id = p_organization_id;
4230 Begin
4231 Open get_org(p_organization_id);
4232 Fetch get_org into l_organization_code;
4233 Close get_org;
4234 Return l_organization_code;
4235 End get_organization_code;
4236
4237 PROCEDURE set_mo_transact_date (
4238 p_date IN DATE) IS
4239 BEGIN
4240 inv_cache.mo_transaction_date := p_date;
4241 END;
4242
4243 PROCEDURE clear_mo_transact_date IS
4244 BEGIN
4245 inv_cache.mo_transaction_date := NULL;
4246 END;
4247
4248
4249 -- LPN Status Project
4250 FUNCTION is_onhand_status_trx_allowed(
4251 p_transaction_type_id IN NUMBER
4252 ,p_organization_id IN NUMBER
4253 ,p_inventory_item_id IN NUMBER
4254 ,p_subinventory_code IN VARCHAR2
4255 ,p_locator_id IN NUMBER
4256 ,p_lot_number IN VARCHAR2
4257 ,p_lpn_id IN NUMBER
4258 ) RETURN VARCHAR2 IS
4259
4260
4261 l_api_name VARCHAR2(30) := 'is_onhand_status_trx_allowed';
4262 l_return_status VARCHAR2(1);
4263 l_msg_count NUMBER;
4264 l_msg_data VARCHAR2(240);
4265 l_status_id NUMBER;
4266 l_status_return VARCHAR2(1):='Y';
4267 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4268 l_progress VARCHAR2(20);
4269
4270
4271 BEGIN
4272
4273 IF ( l_debug = 1 ) THEN
4274 print_debug('enter '||g_pkg_name||'.'||l_api_name, 1);
4275 print_debug('orgid='||p_organization_id||' item='||p_inventory_item_id ||' lot='||p_lot_number||' sub='||p_subinventory_code||' loc='||p_locator_id, 1);
4276 print_debug('p_transaction_type_id='||p_transaction_type_id||' p_lpn_id='||p_lpn_id, 1);
4277 END IF;
4278
4279 IF p_lpn_id IS NOT NULL THEN
4280 -- Entire LPN is being allocated
4281
4282 BEGIN
4283 SELECT status_id into l_status_id
4284 FROM mtl_onhand_quantities_detail
4285 WHERE organization_id = p_organization_id
4286 AND inventory_item_id = p_inventory_item_id
4287 AND subinventory_code = p_subinventory_code
4288 AND locator_id = p_locator_id
4289 AND nvl(lot_number,-9999) = nvl(p_lot_number, -9999)
4290 AND lpn_id = p_lpn_id
4291 AND rownum = 1;
4292
4293 IF ( l_debug = 1 ) THEN
4294 print_debug('Value of l_status_id:'||l_status_id, 1);
4295 END IF;
4296
4297 l_status_return := inv_material_status_grp.is_trx_allowed(
4298 p_status_id => l_status_id
4299 ,p_transaction_type_id => p_transaction_type_id
4300 ,x_return_status => l_status_return ,x_msg_count => l_msg_count
4301 ,x_msg_data => l_msg_data);
4302
4303 IF l_status_return = fnd_api.g_ret_sts_unexp_error THEN
4304 RAISE fnd_api.g_exc_unexpected_error;
4305 ELSIF l_status_return = fnd_api.g_ret_sts_error THEN
4306 RAISE fnd_api.g_exc_error;
4307 END IF;
4308 EXCEPTION
4309 WHEN OTHERS THEN
4310 IF (l_debug = 1) THEN
4311 l_progress := 'WMSSCC-0890';
4312 print_debug('INV_DETAIL_UTIL_PVT:'||l_api_name||': Error occured'||l_progress, 1);
4313 RAISE fnd_api.g_exc_unexpected_error;
4314 END IF;
4315 END;
4316
4317 IF ( l_debug = 1 ) THEN
4318 print_debug('Value of l_status_return:'||l_status_return, 1);
4319 END IF;
4320
4321 return l_status_return ;
4322
4323
4324 ELSE --IF p_lpn_id IS NOT NULL THEN
4325 -- Allocation is across loose and packed material.
4326
4327 BEGIN
4328
4329 SELECT 'Y' into l_status_return FROM DUAL WHERE EXISTS(
4330 SELECT 1 FROM mtl_onhand_quantities_detail moqd
4331 WHERE moqd.organization_id = p_organization_id
4332 AND moqd.inventory_item_id = p_inventory_item_id
4333 AND moqd.subinventory_code = p_subinventory_code
4334 AND nvl(moqd.locator_id,-999) = nvl(p_locator_id,-999)
4335 AND nvl(moqd.lot_number,-999) = nvl(p_lot_number, -999)
4336 AND NOT EXISTS(SELECT 1 from mtl_status_transaction_control mtc
4337 WHERE mtc.status_id = moqd.status_id
4338 AND mtc.transaction_type_id = p_transaction_type_id
4339 AND mtc.is_allowed = 2 ));
4340 EXCEPTION
4341 WHEN NO_DATA_FOUND THEN
4342 l_status_return:= 'N';
4343 WHEN OTHERS THEN
4344 IF (l_debug = 1) THEN
4345 l_progress := 'WMSSCC-08891';
4346 print_debug('INV_DETAIL_UTIL_PVT:'||l_api_name||': Error occured'||l_progress, 1);
4347 END IF;
4348 END;
4349 RETURN l_status_return;
4350
4351 END IF; --IF p_lpn_id IS NOT NULL THEN
4352 END is_onhand_status_trx_allowed;
4353
4354 -- LPN Status Project
4355
4356 END inv_detail_util_pvt;