[Home] [Help]
PACKAGE BODY: APPS.INV_TXN_VALIDATIONS
Source
1 PACKAGE BODY INV_TXN_VALIDATIONS AS
2 /* $Header: INVMWAVB.pls 120.10.12020000.4 2013/01/17 07:26:59 avrose ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_Txn_Validations';
6
7
8 PROCEDURE mdebug(msg in varchar2)
9 IS
10 l_msg VARCHAR2(5100);
11 l_ts VARCHAR2(30);
12 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 BEGIN
14 --select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual; -- Bug 13878269
15 --l_msg := l_ts||' '||msg;
16 l_msg := msg;
17
18 IF (l_debug = 1) THEN
19 inv_mobile_helper_functions.tracelog(p_err_msg => l_msg, p_module => 'INV_TXN_VALIDATIONS', p_level => 4);
20 END IF;
21 null;
22 END;
23
24
25
26 PROCEDURE VALIDATE_ITEM(x_Inventory_Item_Id OUT NOCOPY NUMBER,
27 x_Description OUT NOCOPY VARCHAR2,
28 x_Revision_Qty_Control_Code OUT NOCOPY NUMBER,
29 x_Lot_Control_Code OUT NOCOPY NUMBER,
30 x_Serial_Number_Control_Code OUT NOCOPY NUMBER,
31 x_Restrict_Locators_Code OUT NOCOPY NUMBER,
32 x_Location_Control_Code OUT NOCOPY NUMBER,
33 x_Restrict_Subinventories_Code OUT NOCOPY NUMBER,
34 x_Message OUT NOCOPY VARCHAR2,
35 x_Status OUT NOCOPY VARCHAR2,
36 p_Organization_Id IN NUMBER,
37 p_Concatenated_Segments IN VARCHAR2)
38
39 IS
40
41 l_Item_Info t_Item_Out;
42
43 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
44 BEGIN
45
46 Select inventory_item_id,
47 description,
48 Revision_qty_control_code,
49 lot_control_code,
50 serial_number_control_code,
51 restrict_locators_code,
52 location_control_code,
53 restrict_subinventories_code
54 INTO l_Item_Info
55 FROM MTL_SYSTEM_ITEMS_KFV
56 WHERE concatenated_segments = p_Concatenated_Segments and
57 organization_id = p_Organization_Id and
58 mtl_transactions_enabled_flag = 'Y';
59
60 x_Inventory_Item_Id := l_Item_Info.Inventory_Item_Id;
61 x_Description := l_Item_Info.Description;
62 x_Revision_Qty_Control_Code := l_Item_Info.Revision_Qty_Control_Code;
63 x_Lot_Control_Code := l_Item_Info.Lot_Control_Code;
64 x_Serial_Number_Control_Code:= l_Item_Info.Serial_Number_Control_Code;
65 x_Restrict_Locators_Code := l_Item_Info.Restrict_Locators_Code;
66 x_Location_Control_Code := l_Item_Info.Location_Control_Code;
67 x_Restrict_Subinventories_Code:=l_Item_Info.Restrict_Subinventories_Code;
68
69 x_Message := 'Item: '|| p_Concatenated_Segments;
70 x_Status := 'C';
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 x_Message := 'Not a Valid Item';
74 x_Inventory_Item_Id := NULL;
75 x_Description := NULL;
76 x_Revision_Qty_Control_Code := NULL;
77 x_Lot_Control_Code := NULL;
78 x_Serial_Number_Control_Code := NULL;
79 x_Restrict_Locators_Code := NULL;
80 x_Location_Control_Code := NULL;
81 x_Restrict_Subinventories_Code := NULL;
82 x_Status := 'E';
83 END;
84
85
86 PROCEDURE VALIDATE_SERIAL(x_Current_Locator_Id OUT NOCOPY NUMBER,
87 x_Concatenated_Segments OUT NOCOPY VARCHAR2, --Locator Name
88 x_Current_Subinventory_Code OUT NOCOPY VARCHAR2,
89 x_Revision OUT NOCOPY VARCHAR2,
90 x_Lot_Number OUT NOCOPY VARCHAR2,
91 x_Expiration_Date OUT NOCOPY DATE,
92 x_Message OUT NOCOPY VARCHAR2,
93 x_Status OUT NOCOPY VARCHAR2,
94 p_Inventory_Item_Id IN NUMBER,
95 p_Current_Organization_Id IN NUMBER,
96 p_Serial_Number IN VARCHAR2)
97
98 IS
99
100 l_SN_Info t_SN_Out;
101 l_curr_stat NUMBER;
102
103 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
104 BEGIN
105
106 Select current_locator_id,
107 current_subinventory_code,
108 revision,
109 lot_number
110 INTO l_SN_Info
111 FROM MTL_SERIAL_NUMBERS
112 WHERE inventory_item_id = p_Inventory_Item_Id and
113 current_organization_id = p_Current_Organization_Id and
114 serial_number = p_Serial_Number;
115
116 x_Current_Locator_Id := l_SN_Info.Current_Locator_Id;
117 x_Current_Subinventory_Code := l_SN_Info.Current_Subinventory_Code;
118 x_Revision := l_SN_Info.Revision;
119 x_Lot_Number := l_SN_Info.Lot_Number;
120 x_Expiration_Date := NULL;
121 x_Concatenated_Segments := NULL;
122
123 IF x_Lot_Number IS NOT NULL THEN
124 SELECT expiration_date INTO x_Expiration_Date
125 FROM mtl_lot_numbers
126 WHERE lot_number = x_Lot_Number AND
127 inventory_item_id = p_Inventory_Item_Id AND
128 organization_id = p_Current_Organization_Id;
129 END IF;
130
131
132 IF x_Current_Locator_Id IS NOT NULL THEN
133 SELECT concatenated_segments INTO x_Concatenated_Segments
134 FROM mtl_item_locations_kfv
135 WHERE inventory_location_id = x_Current_Locator_Id AND
136 organization_id = p_Current_Organization_Id;
137
138 END IF;
139
140 SELECT current_status INTO l_curr_stat
141 FROM mtl_serial_numbers
142 WHERE inventory_item_id = p_Inventory_Item_Id AND
143 current_organization_id = p_Current_Organization_Id AND
144 serial_number = p_Serial_Number;
145
146 IF l_curr_stat = 1 THEN
147 x_Message := 'SN Not In Use';
148 END IF;
149
150
151 IF l_curr_stat = 4 THEN
152 x_Message := 'Issued Out of Stores';
153 END IF;
154
155 IF l_curr_stat = 5 THEN
156 x_Message := 'SN In Intransit';
157 END IF;
158
159
160 x_Status := 'C';
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 x_Message := 'Not a Valid SN. Item Id is '||p_Inventory_Item_Id||', organization_id is '||p_Current_Organization_Id||', SN is '||p_Serial_Number;
165
166 x_Current_Locator_Id := NULL;
167 x_Current_Subinventory_Code := NULL;
168 x_Revision := NULL;
169 x_Lot_Number := NULL;
170 x_Expiration_Date := NULL;
171 x_Concatenated_Segments := NULL; --Locator
172 x_Status := 'E';
173 END VALIDATE_SERIAL;
174
175 -- This does not use cost group id
176 -- Bug 5125915 Added variables demand_source_header and demand_source_line
177 PROCEDURE GET_AVAILABLE_QUANTITY(
178 x_return_status OUT NOCOPY VARCHAR2,
179 p_tree_mode IN NUMBER,
180 p_organization_id IN NUMBER,
181 p_inventory_item_id IN NUMBER,
182 p_is_revision_control IN VARCHAR2,
183 p_is_lot_control IN VARCHAR2,
184 p_is_serial_control IN VARCHAR2,
185 p_demand_source_header_id IN NUMBER DEFAULT -9999,
186 p_demand_source_line_id IN NUMBER DEFAULT -9999,
187 p_revision IN VARCHAR2,
188 p_lot_number IN VARCHAR2,
189 p_lot_expiration_date IN DATE,
190 p_subinventory_code IN VARCHAR2,
191 p_locator_id IN NUMBER,
192 p_source_type_id IN NUMBER,
193 x_qoh OUT NOCOPY NUMBER,
194 x_att OUT NOCOPY NUMBER
195 )
196 IS
197 l_sqoh NUMBER; -- inv converge
198 l_satt NUMBER; -- inv converge
199 l_grade_code VARCHAR2(150); -- inv converge
200
201 BEGIN
202
203 GET_AVAILABLE_QUANTITY(
204 x_return_status => x_return_status
205 ,p_tree_mode => p_tree_mode
206 ,p_organization_id => p_organization_id
207 ,p_inventory_item_id => p_inventory_item_id
208 ,p_is_revision_control => p_is_revision_control
209 ,p_is_lot_control => p_is_lot_control
210 ,p_is_serial_control => p_is_serial_control
211 ,p_demand_source_header_id => p_demand_source_header_id
212 ,p_demand_source_line_id=> p_demand_source_line_id
213 ,p_revision => p_revision
214 ,p_lot_number => p_lot_number
215 ,p_grade_code => l_grade_code
216 ,p_lot_expiration_date => p_lot_expiration_date
217 ,p_subinventory_code => p_subinventory_code
218 ,p_locator_id => p_locator_id
219 ,p_source_type_id => p_source_type_id
220 ,x_qoh => x_qoh
221 ,x_att => x_att
222 ,x_sqoh => l_sqoh
223 ,x_satt => l_satt
224 );
225
226 END GET_AVAILABLE_QUANTITY;
227
228
229
230 -- Bug# 3952081
231 -- New Overloaded Version of the previous procedure for OPM convergence
232 -- Additionally returns secondary qoh, secondary att
233 -- Additionally takes grade_code as an input param.
234 -- Bug 5125915 Added variables demand_source_header and demand_source_line
235
236 PROCEDURE GET_AVAILABLE_QUANTITY(
237 x_return_status OUT NOCOPY VARCHAR2,
238 p_tree_mode IN NUMBER,
239 p_organization_id IN NUMBER,
240 p_inventory_item_id IN NUMBER,
241 p_is_revision_control IN VARCHAR2,
242 p_is_lot_control IN VARCHAR2,
243 p_is_serial_control IN VARCHAR2,
244 p_demand_source_header_id IN NUMBER DEFAULT -9999,
245 p_demand_source_line_id IN NUMBER DEFAULT -9999,
246 p_revision IN VARCHAR2,
247 p_lot_number IN VARCHAR2,
248 p_grade_code IN VARCHAR2, -- inv converge
249 p_lot_expiration_date IN DATE,
250 p_subinventory_code IN VARCHAR2,
251 p_locator_id IN NUMBER,
252 p_source_type_id IN NUMBER,
253 x_qoh OUT NOCOPY NUMBER,
254 x_att OUT NOCOPY NUMBER,
255 x_sqoh OUT NOCOPY NUMBER, -- inv converge
256 x_satt OUT NOCOPY NUMBER -- inv converge
257 )
258
259 IS
260 l_msg_count VARCHAR2(100);
261 l_msg_data VARCHAR2(1000);
262 l_rqoh NUMBER;
263 l_qr NUMBER;
264 l_qs NUMBER;
265 l_atr NUMBER;
266 l_is_revision_control BOOLEAN := FALSE;
267 l_is_lot_control BOOLEAN := FALSE;
268 l_is_serial_control BOOLEAN := FALSE;
269 l_tree_mode NUMBER;
270 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
271 -- Bug# 3952081
272 l_srqoh NUMBER;
273 l_sqr NUMBER;
274 l_sqs NUMBER;
275 l_satr NUMBER;
276 l_demand_source_header_id NUMBER ;
277 l_demand_source_line_id NUMBER ;
278 BEGIN
279 inv_quantity_tree_pub.clear_quantity_cache;
280 IF p_is_revision_control = 'true' THEN
281 l_is_revision_control := TRUE;
282 END IF;
283
284 IF p_is_lot_control = 'true' THEN
285 l_is_lot_control := TRUE;
286 END IF;
287
288 IF p_is_serial_control = 'true' THEN
289 l_is_serial_control := TRUE;
290 END IF;
291
292 IF p_demand_source_header_id IS NULL THEN
293 l_demand_source_header_id := -9999 ;
294 ELSE
295 l_demand_source_header_id := p_demand_source_header_id ;
296 END IF ;
297
298 IF p_demand_source_line_id IS NULL THEN
299 l_demand_source_line_id := -9999 ;
300 ELSE
301 l_demand_source_line_id := p_demand_source_line_id ;
302 END IF ;
303
304 IF p_tree_mode IS NULL THEN
305 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
306 ELSE l_tree_mode := p_tree_mode;
307 END IF ;
308
309 inv_quantity_tree_pub.query_quantities
310 ( p_api_version_number => 1.0
311 , p_init_msg_lst => fnd_api.g_false
312 , x_return_status => x_return_status
313 , x_msg_count => l_msg_count
314 , x_msg_data => l_msg_data
315 , p_organization_id => p_organization_id
316 , p_inventory_item_id => p_inventory_item_id
317 , p_tree_mode => l_tree_mode
318 , p_is_revision_control => l_is_revision_control
319 , p_is_lot_control => l_is_lot_control
320 , p_is_serial_control => l_is_serial_control
321 , p_demand_source_type_id=> p_source_type_id
322 , p_demand_source_header_id =>l_demand_source_header_id
323 , p_demand_source_line_id =>l_demand_source_line_id
324 , p_revision => p_revision
325 , p_lot_number => p_lot_number
326 , p_lot_expiration_date => NULL --for bug# 2219136
327 , p_grade_code => p_grade_code
328 , p_subinventory_code => p_subinventory_code
329 , p_locator_id => p_locator_id
330 , x_qoh => x_qoh
331 , x_rqoh => l_rqoh
332 , x_qr => l_qr
333 , x_qs => l_qs
334 , x_att => x_att
335 , x_atr => l_atr
336 , x_sqoh => x_sqoh
337 , x_srqoh => l_srqoh
338 , x_sqr => l_sqr
339 , x_sqs => l_sqs
340 , x_satt => x_satt
341 , x_satr => l_satr
342 );
343 IF (l_debug = 1) THEN
344 mdebug('@'||l_msg_data||'@');
345 END IF;
346
347 END get_available_quantity;
348
349
350
351 -- This uses cost group id
352 PROCEDURE GET_AVAILABLE_QUANTITY(
353 x_return_status OUT NOCOPY VARCHAR2,
354 p_tree_mode IN NUMBER,
355 p_organization_id IN NUMBER,
356 p_inventory_item_id IN NUMBER,
357 p_is_revision_control IN VARCHAR2,
358 p_is_lot_control IN VARCHAR2,
359 p_is_serial_control IN VARCHAR2,
360 p_revision IN VARCHAR2,
361 p_lot_number IN VARCHAR2,
362 p_lot_expiration_date IN DATE,
363 p_subinventory_code IN VARCHAR2,
364 p_locator_id IN NUMBER,
365 p_source_type_id IN NUMBER,
366 p_cost_group_id IN NUMBER,
367 x_qoh OUT NOCOPY NUMBER,
368 x_att OUT NOCOPY NUMBER
369 )
370 IS
371 l_sqoh NUMBER; -- inv converge
372 l_satt NUMBER; -- inv converge
373 l_grade_code VARCHAR2(150); -- inv converge
374
375 BEGIN
376
377 GET_AVAILABLE_QUANTITY(
378 x_return_status => x_return_status
379 ,p_tree_mode => p_tree_mode
380 ,p_organization_id => p_organization_id
381 ,p_inventory_item_id => p_inventory_item_id
382 ,p_is_revision_control => p_is_revision_control
383 ,p_is_lot_control => p_is_lot_control
384 ,p_is_serial_control => p_is_serial_control
385 ,p_revision => p_revision
386 ,p_lot_number => p_lot_number
387 ,p_grade_code => l_grade_code
388 ,p_lot_expiration_date => p_lot_expiration_date
389 ,p_subinventory_code => p_subinventory_code
390 ,p_locator_id => p_locator_id
391 ,p_source_type_id => p_source_type_id
392 ,p_cost_group_id => p_cost_group_id
393 ,x_qoh => x_qoh
394 ,x_att => x_att
395 ,x_sqoh => l_sqoh
396 ,x_satt => l_satt
397 );
398
399
400
401 END GET_AVAILABLE_QUANTITY;
402
403
404 -- Bug# 3952081
405 -- New Overloaded Version of the previous procedure for OPM convergence
406 -- Additionally returns secondary qoh, secondary att
407 -- Additionally takes grade_code as an input param.
408 PROCEDURE GET_AVAILABLE_QUANTITY(
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_tree_mode IN NUMBER,
411 p_organization_id IN NUMBER,
412 p_inventory_item_id IN NUMBER,
413 p_is_revision_control IN VARCHAR2,
414 p_is_lot_control IN VARCHAR2,
415 p_is_serial_control IN VARCHAR2,
416 p_revision IN VARCHAR2,
417 p_lot_number IN VARCHAR2,
418 p_grade_code IN VARCHAR2, -- inv converge
419 p_lot_expiration_date IN DATE,
420 p_subinventory_code IN VARCHAR2,
421 p_locator_id IN NUMBER,
422 p_source_type_id IN NUMBER,
423 p_cost_group_id IN NUMBER,
424 x_qoh OUT NOCOPY NUMBER,
425 x_att OUT NOCOPY NUMBER,
426 x_sqoh OUT NOCOPY NUMBER, -- inv converge
427 x_satt OUT NOCOPY NUMBER -- inv converge
428 )
429 IS
430 l_msg_count VARCHAR2(100);
431 l_msg_data VARCHAR2(1000);
432 l_rqoh NUMBER;
433 l_qr NUMBER;
434 l_qs NUMBER;
435 l_atr NUMBER;
436 l_is_revision_control BOOLEAN := FALSE;
437 l_is_lot_control BOOLEAN := FALSE;
438 l_is_serial_control BOOLEAN := FALSE;
439 l_tree_mode NUMBER;
440
441 l_srqoh NUMBER;
442 l_sqr NUMBER;
443 l_sqs NUMBER;
444 l_satr NUMBER;
445 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
446 BEGIN
447 inv_quantity_tree_pub.clear_quantity_cache;
448 IF p_is_revision_control = 'true' THEN
449 l_is_revision_control := TRUE;
450 END IF;
451
452 IF p_is_lot_control = 'true' THEN
453 l_is_lot_control := TRUE;
454 END IF;
455
456 IF p_is_serial_control = 'true' THEN
457 l_is_serial_control := TRUE;
458 END IF;
459 IF p_tree_mode IS NULL THEN
460 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
461 ELSE l_tree_mode := p_tree_mode;
462 END IF ;
463 inv_quantity_tree_pub.query_quantities(
464 p_api_version_number => 1.0
465 , p_init_msg_lst => fnd_api.g_false
466 , x_return_status => x_return_status
467 , x_msg_count => l_msg_count
468 , x_msg_data => l_msg_data
469 , p_organization_id => p_organization_id
470 , p_inventory_item_id => p_inventory_item_id
471 , p_tree_mode => l_tree_mode
472 , p_is_revision_control => l_is_revision_control
473 , p_is_lot_control => l_is_lot_control
474 , p_is_serial_control => l_is_serial_control
475 , p_demand_source_type_id=> p_source_type_id
476 , p_revision => p_revision
477 , p_lot_number => p_lot_number
478 , p_lot_expiration_date => NULL --for bug# 2219136
479 , p_grade_code => p_grade_code
480 , p_subinventory_code => p_subinventory_code
481 , p_locator_id => p_locator_id
482 , p_cost_group_id => p_cost_group_id
483 , x_qoh => x_qoh
484 , x_rqoh => l_rqoh
485 , x_qr => l_qr
486 , x_qs => l_qs
487 , x_att => x_att
488 , x_atr => l_atr
489 , x_sqoh => x_sqoh
490 , x_srqoh => l_srqoh
491 , x_sqr => l_sqr
492 , x_sqs => l_sqs
493 , x_satt => x_satt
494 , x_satr => l_satr
495 );
496 IF (l_debug = 1) THEN
497 mdebug('@'||l_msg_data||'@');
498 END IF;
499
500 END get_available_quantity;
501
502
503
504 -- Bug# 2358224
505 -- Overloaded version of the previous procedure
506 -- passing in the to/transfer subinventory
507 -- This uses cost group id and transfer subinventory
508 PROCEDURE GET_AVAILABLE_QUANTITY(
509 x_return_status OUT NOCOPY VARCHAR2,
510 p_tree_mode IN NUMBER,
511 p_organization_id IN NUMBER,
512 p_inventory_item_id IN NUMBER,
513 p_is_revision_control IN VARCHAR2,
514 p_is_lot_control IN VARCHAR2,
515 p_is_serial_control IN VARCHAR2,
516 p_revision IN VARCHAR2,
517 p_lot_number IN VARCHAR2,
518 p_lot_expiration_date IN DATE,
519 p_subinventory_code IN VARCHAR2,
520 p_locator_id IN NUMBER,
521 p_source_type_id IN NUMBER,
522 p_cost_group_id IN NUMBER,
523 p_to_subinventory_code IN VARCHAR2,
524 x_qoh OUT NOCOPY NUMBER,
525 x_att OUT NOCOPY NUMBER
526 )
527 IS
528 l_sqoh NUMBER; -- inv converge
529 l_satt NUMBER; -- inv converge
530 l_grade_code VARCHAR2(150); -- inv converge
531
532 BEGIN
533
534 GET_AVAILABLE_QUANTITY(
535 x_return_status => x_return_status
536 , p_tree_mode => p_tree_mode
537 , p_organization_id => p_organization_id
538 , p_inventory_item_id => p_inventory_item_id
539 , p_is_revision_control => p_is_revision_control
540 , p_is_lot_control => p_is_lot_control
541 , p_is_serial_control => p_is_serial_control
542 , p_revision => p_revision
543 , p_lot_number => p_lot_number
544 , p_grade_code => l_grade_code
545 , p_lot_expiration_date => p_lot_expiration_date
546 , p_subinventory_code => p_subinventory_code
547 , p_locator_id => p_locator_id
548 , p_source_type_id => p_source_type_id
549 , p_cost_group_id => p_cost_group_id
550 , p_to_subinventory_code => p_to_subinventory_code
551 , x_qoh => x_qoh
552 , x_att => x_att
553 , x_sqoh => l_sqoh
554 , x_satt => l_satt
555 );
556
557 END GET_AVAILABLE_QUANTITY;
558
559 -- Bug# 11812327
560 -- Overloaded the procedure GET_AVAILABLE_QUANTITY
561 -- return the Available Quantity on Hand,onhand qty,available transaction qty.
562 PROCEDURE GET_AVAILABLE_QUANTITY(
563 x_return_status OUT NOCOPY VARCHAR2,
564 p_tree_mode IN NUMBER,
565 p_organization_id IN NUMBER,
566 p_inventory_item_id IN NUMBER,
567 p_is_revision_control IN VARCHAR2,
568 p_is_lot_control IN VARCHAR2,
569 p_is_serial_control IN VARCHAR2,
570 p_revision IN VARCHAR2,
571 p_lot_number IN VARCHAR2,
572 p_lot_expiration_date IN DATE,
573 p_subinventory_code IN VARCHAR2,
574 p_locator_id IN NUMBER,
575 p_source_type_id IN NUMBER,
576 p_cost_group_id IN NUMBER,
577 p_to_subinventory_code IN VARCHAR2,
578 x_qoh OUT NOCOPY NUMBER,
579 x_att OUT NOCOPY NUMBER,
580 x_tqoh OUT NOCOPY NUMBER
581 )
582 IS
583 l_qoh1 NUMBER;
584 l_atpp1 NUMBER;
585 l_pqoh NUMBER;
586
587 BEGIN
588
589 --call the procedure
590 GET_AVAILABLE_QUANTITY(
591 x_return_status => x_return_status,
592 p_tree_mode => p_tree_mode,
593 p_organization_id => p_organization_id,
594 p_inventory_item_id => p_inventory_item_id,
595 p_is_revision_control => p_is_revision_control,
596 p_is_lot_control => p_is_lot_control,
597 p_is_serial_control => p_is_serial_control,
598 p_revision => p_revision,
599 p_lot_number => p_lot_number,
600 p_lot_expiration_date => p_lot_expiration_date,
601 p_subinventory_code => p_subinventory_code,
602 p_locator_id => p_locator_id,
603 p_source_type_id => p_source_type_id,
604 x_qoh => x_qoh,
605 x_att => x_att,
606 x_pqoh => l_pqoh,
607 x_tqoh => x_tqoh,
608 x_atpp1 => l_atpp1,
609 x_qoh1 => l_qoh1,
610 p_cost_group_id => p_cost_group_id,
611 p_transfer_subinventory => p_to_subinventory_code
612 );
613
614 END GET_AVAILABLE_QUANTITY;
615
616 -- Bug# 3952081
617 -- New Overloaded Version of the previous procedure for OPM convergence
618 -- Additionally returns secondary qoh, secondary att
619 -- Additionally takes grade_code as an input param.
620 PROCEDURE GET_AVAILABLE_QUANTITY(
621 x_return_status OUT NOCOPY VARCHAR2,
622 p_tree_mode IN NUMBER,
623 p_organization_id IN NUMBER,
624 p_inventory_item_id IN NUMBER,
625 p_is_revision_control IN VARCHAR2,
626 p_is_lot_control IN VARCHAR2,
627 p_is_serial_control IN VARCHAR2,
628 p_revision IN VARCHAR2,
629 p_lot_number IN VARCHAR2,
630 p_grade_code IN VARCHAR2, -- inv converge
631 p_lot_expiration_date IN DATE,
632 p_subinventory_code IN VARCHAR2,
633 p_locator_id IN NUMBER,
634 p_source_type_id IN NUMBER,
635 p_cost_group_id IN NUMBER,
636 p_to_subinventory_code IN VARCHAR2,
637 x_qoh OUT NOCOPY NUMBER,
638 x_att OUT NOCOPY NUMBER,
639 x_sqoh OUT NOCOPY NUMBER, -- inv converge
640 x_satt OUT NOCOPY NUMBER -- inv converge
641 )
642 IS
643 l_msg_count VARCHAR2(100);
644 l_msg_data VARCHAR2(1000);
645 l_rqoh NUMBER;
646 l_qr NUMBER;
647 l_qs NUMBER;
648 l_atr NUMBER;
649 l_is_revision_control BOOLEAN := FALSE;
650 l_is_lot_control BOOLEAN := FALSE;
651 l_is_serial_control BOOLEAN := FALSE;
652 l_tree_mode NUMBER;
653
654 l_srqoh NUMBER;
655 l_sqr NUMBER;
656 l_sqs NUMBER;
657 l_satr NUMBER;
658 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
659 BEGIN
660 inv_quantity_tree_pub.clear_quantity_cache;
661 IF p_is_revision_control = 'true' THEN
662 l_is_revision_control := TRUE;
663 END IF;
664
665 IF p_is_lot_control = 'true' THEN
666 l_is_lot_control := TRUE;
667 END IF;
668
669 IF p_is_serial_control = 'true' THEN
670 l_is_serial_control := TRUE;
671 END IF;
672
673 IF p_tree_mode IS NULL THEN
674 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
675 ELSE l_tree_mode := p_tree_mode;
676 END IF ;
677
678 inv_quantity_tree_pub.query_quantities(
679 p_api_version_number => 1.0 ,
680 p_init_msg_lst => fnd_api.g_false ,
681 x_return_status => x_return_status ,
682 x_msg_count => l_msg_count ,
683 x_msg_data => l_msg_data ,
684 p_organization_id => p_organization_id ,
685 p_inventory_item_id => p_inventory_item_id ,
686 p_tree_mode => l_tree_mode ,
687 p_is_revision_control => l_is_revision_control ,
688 p_is_lot_control => l_is_lot_control ,
689 p_is_serial_control => l_is_serial_control ,
690 p_demand_source_type_id => p_source_type_id ,
691 p_revision => p_revision ,
692 p_lot_number => p_lot_number ,
693 p_lot_expiration_date => NULL ,
694 p_grade_code => p_grade_code ,
695 p_subinventory_code => p_subinventory_code ,
696 p_locator_id => p_locator_id ,
697 p_cost_group_id => p_cost_group_id ,
698 p_transfer_subinventory_code => p_to_subinventory_code ,
699 x_qoh => x_qoh ,
700 x_rqoh => l_rqoh ,
701 x_qr => l_qr ,
702 x_qs => l_qs ,
703 x_att => x_att ,
704 x_atr => l_atr ,
705 x_sqoh => x_sqoh ,
706 x_srqoh => l_srqoh ,
707 x_sqr => l_sqr ,
708 x_sqs => l_sqs ,
709 x_satt => x_satt ,
710 x_satr => l_satr
711 );
712 IF (l_debug = 1) THEN
713 mdebug('@'||l_msg_data||'@');
714 END IF;
715
716 END GET_AVAILABLE_QUANTITY;
717
718
719
720
721 /* This Overloaded Procedure Calls INV_QUANTITY_TREE_PVT to return pqoh
722 */
723
724 PROCEDURE GET_AVAILABLE_QUANTITY(
725 x_return_status OUT NOCOPY VARCHAR2,
726 p_tree_mode IN NUMBER,
727 p_organization_id IN NUMBER,
728 p_inventory_item_id IN NUMBER,
729 p_is_revision_control IN VARCHAR2,
730 p_is_lot_control IN VARCHAR2,
731 p_is_serial_control IN VARCHAR2,
732 p_revision IN VARCHAR2,
733 p_lot_number IN VARCHAR2,
734 p_lot_expiration_date IN DATE,
735 p_subinventory_code IN VARCHAR2,
736 p_locator_id IN NUMBER,
737 p_source_type_id IN NUMBER,
738 x_qoh OUT NOCOPY NUMBER,
739 x_att OUT NOCOPY NUMBER,
740 x_pqoh OUT NOCOPY NUMBER,
741 x_tqoh OUT NOCOPY NUMBER,
742 x_atpp1 OUT NOCOPY NUMBER,
743 x_qoh1 OUT NOCOPY NUMBER
744 )
745 IS
746 l_sqoh NUMBER;
747 l_satt NUMBER;
748 l_spqoh NUMBER;
749 l_stqoh NUMBER;
750 l_satpp1 NUMBER;
751 l_sqoh1 NUMBER;
752 l_grade_code VARCHAR2(150); -- inv converge
753
754 BEGIN
755
756 GET_AVAILABLE_QUANTITY(
757 x_return_status => x_return_status
758 , p_tree_mode => p_tree_mode
759 , p_organization_id => p_organization_id
760 , p_inventory_item_id => p_inventory_item_id
761 , p_is_revision_control => p_is_revision_control
762 , p_is_lot_control => p_is_lot_control
763 , p_is_serial_control => p_is_serial_control
764 , p_revision => p_revision
765 , p_lot_number => p_lot_number
766 , p_grade_code => l_grade_code
767 , p_lot_expiration_date => p_lot_expiration_date
768 , p_subinventory_code => p_subinventory_code
769 , p_locator_id => p_locator_id
770 , p_source_type_id => p_source_type_id
771 , x_qoh => x_qoh
772 , x_att => x_att
773 , x_pqoh => x_pqoh
774 , x_tqoh => x_tqoh
775 , x_atpp1 => x_atpp1
776 , x_qoh1 => x_qoh1
777 , x_sqoh => l_sqoh
778 , x_satt => l_satt
779 , x_spqoh => l_spqoh
780 , x_stqoh => l_stqoh
781 , x_satpp1 => l_satpp1
782 , x_sqoh1 => l_sqoh1
783 );
784
785 END GET_AVAILABLE_QUANTITY;
786
787
788
789 -- Bug# 3952081
790 -- New Overloaded Version of the previous procedure for OPM convergence
791 -- Additionally returns secondary qoh, secondary att
792 -- Additionally takes grade_code as an input param.
793 PROCEDURE GET_AVAILABLE_QUANTITY(
794 x_return_status OUT NOCOPY VARCHAR2,
795 p_tree_mode IN NUMBER,
796 p_organization_id IN NUMBER,
797 p_inventory_item_id IN NUMBER,
798 p_is_revision_control IN VARCHAR2,
799 p_is_lot_control IN VARCHAR2,
800 p_is_serial_control IN VARCHAR2,
801 p_revision IN VARCHAR2,
802 p_lot_number IN VARCHAR2,
803 p_grade_code IN VARCHAR2,
804 p_lot_expiration_date IN DATE,
805 p_subinventory_code IN VARCHAR2,
806 p_locator_id IN NUMBER,
807 p_source_type_id IN NUMBER,
808 x_qoh OUT NOCOPY NUMBER,
809 x_att OUT NOCOPY NUMBER,
810 x_pqoh OUT NOCOPY NUMBER,
811 x_tqoh OUT NOCOPY NUMBER,
812 x_atpp1 OUT NOCOPY NUMBER,
813 x_qoh1 OUT NOCOPY NUMBER,
814 x_sqoh OUT NOCOPY NUMBER,
815 x_satt OUT NOCOPY NUMBER,
816 x_spqoh OUT NOCOPY NUMBER,
817 x_stqoh OUT NOCOPY NUMBER,
818 x_satpp1 OUT NOCOPY NUMBER,
819 x_sqoh1 OUT NOCOPY NUMBER
820 )
821 IS
822 l_msg_count VARCHAR2(100);
823 l_msg_data VARCHAR2(1000);
824 l_is_revision_control BOOLEAN := FALSE;
825 l_is_lot_control BOOLEAN := FALSE;
826 l_is_serial_control BOOLEAN := FALSE;
827 l_tree_mode NUMBER;
828 l_api_version_number CONSTANT NUMBER := 1.0;
829 l_api_name CONSTANT VARCHAR2(30) := 'Get_Avaliable_Quantity';
830 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
831 l_tree_id INTEGER;
832 l_rqoh NUMBER;
833 l_qr NUMBER;
834 l_qs NUMBER;
835 l_atr NUMBER;
836 l_pqoh NUMBER;
837
838 l_srqoh NUMBER;
839 l_sqr NUMBER;
840 l_sqs NUMBER;
841 l_satr NUMBER;
842 l_spqoh NUMBER;
843
844 x_msg_count VARCHAR2(100);
845 x_msg_data VARCHAR2(1000);
846 p_api_version_number number;
847 p_init_msg_lst VARCHAR2(30);
848 l_asset_sub_only BOOLEAN := FALSE;
849
850 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
851 BEGIN
852 inv_quantity_tree_pvt.clear_quantity_cache;
853
854 IF p_is_revision_control = 'true' THEN
855 l_is_revision_control := TRUE;
856 END IF;
857
858 IF p_is_lot_control = 'true' THEN
859 l_is_lot_control := TRUE;
860 END IF;
861
862 IF p_is_serial_control = 'true' THEN
863 l_is_serial_control := TRUE;
864 END IF;
865 IF p_tree_mode IS NULL THEN
866 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
867 ELSE l_tree_mode := p_tree_mode;
868 END IF ;
869
870
871 -- Initialize message list.
872 IF fnd_api.to_boolean(p_init_msg_lst) THEN
873 fnd_msg_pub.initialize;
874 END IF;
875
876 INV_QUANTITY_TREE_PVT.create_tree
877 ( p_api_version_number => 1.0
878 , p_init_msg_lst => fnd_api.g_false
879 , x_return_status => l_return_status
880 , x_msg_count => x_msg_count
881 , x_msg_data => x_msg_data
882 , p_organization_id => p_organization_id
883 , p_inventory_item_id => p_inventory_item_id
884 , p_tree_mode => p_tree_mode
885 , p_is_revision_control => l_is_revision_control
886 , p_is_lot_control => l_is_lot_control
887 , p_is_serial_control => l_is_serial_control
888 , p_asset_sub_only => l_asset_sub_only
889 , p_include_suggestion => FALSE
890 , p_demand_source_type_id => -9999
891 , p_demand_source_header_id => -9999
892 , p_demand_source_line_id => -9999
893 , p_demand_source_name => NULL
894 , p_demand_source_delivery => NULL
895 , p_lot_expiration_date => NULL
896 , p_grade_code => NULL
897 , x_tree_id => l_tree_id
898 , p_onhand_source => 3 --g_all_subs
899 , p_exclusive => 0 --g_non_exclusive
900 , p_pick_release => 0 --g_pick_release_no
901 ) ;
902
903
904 IF l_return_status = fnd_api.g_ret_sts_error THEN
905 RAISE fnd_api.g_exc_error;
906 END IF ;
907
908 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
909 RAISE fnd_api.g_exc_unexpected_error;
910 END IF;
911
912 INV_QUANTITY_TREE_PVT.query_tree
913 ( p_api_version_number => 1.0
914 , p_init_msg_lst => fnd_api.g_false
915 , x_return_status => l_return_status
916 , x_msg_count => x_msg_count
917 , x_msg_data => x_msg_data
918 , p_tree_id => l_tree_id
919 , p_revision => p_revision
920 , p_lot_number => p_lot_number
921 --, p_grade_code => p_grade_code
922 , p_subinventory_code => p_subinventory_code
923 , p_locator_id => p_locator_id
924 , x_qoh => x_qoh
925 , x_rqoh => l_rqoh
926 , x_pqoh => x_pqoh
927 , x_qr => l_qr
928 , x_qs => l_qs
929 , x_att => x_att
930 , x_atr => l_atr
931 , x_sqoh => x_sqoh
932 , x_srqoh => l_srqoh
933 , x_spqoh => x_spqoh
934 , x_sqr => l_sqr
935 , x_sqs => l_sqs
936 , x_satt => x_satt
937 , x_satr => l_satr
938 );
939
940
941 IF l_return_status = fnd_api.g_ret_sts_error THEN
942 RAISE fnd_api.g_exc_error;
943 END IF ;
944
945 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
946 RAISE fnd_api.g_exc_unexpected_error;
947 END IF;
948
949 -- This query_tree quaries qoh and att at Item level,
950 -- so we are passing null for p_subinventory_code and p_locator_id
951
952 INV_QUANTITY_TREE_PVT.query_tree
953 ( p_api_version_number => 1.0
954 , p_init_msg_lst => fnd_api.g_false
955 , x_return_status => l_return_status
956 , x_msg_count => x_msg_count
957 , x_msg_data => x_msg_data
958 , p_tree_id => l_tree_id
959 , p_revision => p_revision
960 , p_lot_number => p_lot_number
961 --, p_grade_code => p_grade_code
962 , p_subinventory_code => NULL
963 , p_locator_id => NULL
964 , x_qoh => x_qoh1
965 , x_rqoh => l_rqoh
966 , x_pqoh => l_pqoh
967 , x_qr => l_qr
968 , x_qs => l_qs
969 , x_att => x_atpp1
970 , x_atr => l_atr
971 , x_sqoh => x_sqoh1
972 , x_srqoh => l_srqoh
973 , x_spqoh => l_spqoh
974 , x_sqr => l_sqr
975 , x_sqs => l_sqs
976 , x_satt => x_satpp1
977 , x_satr => l_satr
978 );
979
980
981 IF l_return_status = fnd_api.g_ret_sts_error THEN
982 RAISE fnd_api.g_exc_error;
983 END IF ;
984
985 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
986 RAISE fnd_api.g_exc_unexpected_error;
987 END IF;
988
989 INV_QUANTITY_TREE_PVT.get_total_qoh
990 ( x_return_status => l_return_status
991 , x_msg_count => x_msg_count
992 , x_msg_data => x_msg_data
993 , p_tree_id => l_tree_id
994 , p_revision => p_revision
995 , p_lot_number => p_lot_number
996 --, p_grade_code => p_grade_code
997 , p_subinventory_code => p_subinventory_code
998 , p_locator_id => p_locator_id
999 , p_cost_group_id => NULL
1000 , x_tqoh => x_tqoh
1001 , x_stqoh => x_stqoh
1002 );
1003
1004
1005 IF l_return_status = fnd_api.g_ret_sts_error THEN
1006 RAISE fnd_api.g_exc_error;
1007 END IF ;
1008
1009 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1010 RAISE fnd_api.g_exc_unexpected_error;
1011 END IF;
1012
1013 x_return_status := l_return_status;
1014
1015 EXCEPTION
1016
1017 WHEN fnd_api.g_exc_error THEN
1018 x_return_status := fnd_api.g_ret_sts_error;
1019
1020 -- Get message count and data
1021 fnd_msg_pub.count_and_get
1022 ( p_count => x_msg_count
1023 , p_data => x_msg_data
1024 );
1025
1026 WHEN fnd_api.g_exc_unexpected_error THEN
1027 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1028
1029 -- Get message count and data
1030 fnd_msg_pub.count_and_get
1031 ( p_count => x_msg_count
1032 , p_data => x_msg_data
1033 );
1034
1035 WHEN OTHERS THEN
1036 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1037
1038 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1039 THEN
1040
1041 fnd_msg_pub.add_exc_msg
1042 ( g_pkg_name
1043 , l_api_name
1044 );
1045 END IF;
1046
1047 -- Get message count and data
1048 fnd_msg_pub.count_and_get
1049 ( p_count => x_msg_count
1050 , p_data => x_msg_data
1051 );
1052
1053
1054 END get_available_quantity;
1055
1056
1057
1058 /* This Overloaded Procedure Calls INV_QUANTITY_TREE_PVT to return pqoh.
1059 --This procedure takes in the cost group
1060 */
1061
1062 PROCEDURE GET_AVAILABLE_QUANTITY
1063 (x_return_status OUT NOCOPY VARCHAR2,
1064 p_tree_mode IN NUMBER,
1065 p_organization_id IN NUMBER,
1066 p_inventory_item_id IN NUMBER,
1067 p_is_revision_control IN VARCHAR2,
1068 p_is_lot_control IN VARCHAR2,
1069 p_is_serial_control IN VARCHAR2,
1070 p_revision IN VARCHAR2,
1071 p_lot_number IN VARCHAR2,
1072 p_lot_expiration_date IN DATE,
1073 p_subinventory_code IN VARCHAR2,
1074 p_locator_id IN NUMBER,
1075 p_source_type_id IN NUMBER,
1076 x_qoh OUT NOCOPY NUMBER,
1077 x_att OUT NOCOPY NUMBER,
1078 x_pqoh OUT NOCOPY NUMBER,
1079 x_tqoh OUT NOCOPY NUMBER,
1080 x_atpp1 OUT NOCOPY NUMBER,
1081 x_qoh1 OUT NOCOPY NUMBER,
1082 p_cost_group_id IN NUMBER,
1083 p_transfer_subinventory IN VARCHAR2)
1084
1085 IS
1086 l_sqoh NUMBER;
1087 l_satt NUMBER;
1088 l_spqoh NUMBER;
1089 l_stqoh NUMBER;
1090 l_satpp1 NUMBER;
1091 l_sqoh1 NUMBER;
1092 l_grade_code VARCHAR2(150); -- inv converge
1093
1094 BEGIN
1095
1096 GET_AVAILABLE_QUANTITY(
1097 x_return_status => x_return_status
1098 , p_tree_mode => p_tree_mode
1099 , p_organization_id => p_organization_id
1100 , p_inventory_item_id => p_inventory_item_id
1101 , p_is_revision_control => p_is_revision_control
1102 , p_is_lot_control => p_is_lot_control
1103 , p_is_serial_control => p_is_serial_control
1104 , p_revision => p_revision
1105 , p_lot_number => p_lot_number
1106 , p_grade_code => l_grade_code
1107 , p_lot_expiration_date => p_lot_expiration_date
1108 , p_subinventory_code => p_subinventory_code
1109 , p_locator_id => p_locator_id
1110 , p_source_type_id => p_source_type_id
1111 , x_qoh => x_qoh
1112 , x_att => x_att
1113 , x_pqoh => x_pqoh
1114 , x_tqoh => x_tqoh
1115 , x_atpp1 => x_atpp1
1116 , x_qoh1 => x_qoh1
1117 , x_sqoh => l_sqoh
1118 , x_satt => l_satt
1119 , x_spqoh => l_spqoh
1120 , x_stqoh => l_stqoh
1121 , x_satpp1 => l_satpp1
1122 , x_sqoh1 => l_sqoh1
1123 , p_cost_group_id => p_cost_group_id
1124 , p_transfer_subinventory=> p_transfer_subinventory
1125 );
1126
1127 END GET_AVAILABLE_QUANTITY;
1128
1129
1130 -- Bug# 3952081
1131 -- New Overloaded Version of the previous procedure for OPM convergence
1132 -- Additionally returns secondary qoh, secondary att
1133 -- Additionally takes grade_code as an input param.
1134 PROCEDURE GET_AVAILABLE_QUANTITY(
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 p_tree_mode IN NUMBER,
1137 p_organization_id IN NUMBER,
1138 p_inventory_item_id IN NUMBER,
1139 p_is_revision_control IN VARCHAR2,
1140 p_is_lot_control IN VARCHAR2,
1141 p_is_serial_control IN VARCHAR2,
1142 p_revision IN VARCHAR2,
1143 p_lot_number IN VARCHAR2,
1144 p_grade_code IN VARCHAR2,
1145 p_lot_expiration_date IN DATE,
1146 p_subinventory_code IN VARCHAR2,
1147 p_locator_id IN NUMBER,
1148 p_source_type_id IN NUMBER,
1149 x_qoh OUT NOCOPY NUMBER,
1150 x_att OUT NOCOPY NUMBER,
1151 x_pqoh OUT NOCOPY NUMBER,
1152 x_tqoh OUT NOCOPY NUMBER,
1153 x_atpp1 OUT NOCOPY NUMBER,
1154 x_qoh1 OUT NOCOPY NUMBER,
1155 x_sqoh OUT NOCOPY NUMBER,
1156 x_satt OUT NOCOPY NUMBER,
1157 x_spqoh OUT NOCOPY NUMBER,
1158 x_stqoh OUT NOCOPY NUMBER,
1159 x_satpp1 OUT NOCOPY NUMBER,
1160 x_sqoh1 OUT NOCOPY NUMBER,
1161 p_cost_group_id IN NUMBER,
1162 p_transfer_subinventory IN VARCHAR2
1163 )
1164 IS
1165 l_msg_count VARCHAR2(100);
1166 l_msg_data VARCHAR2(1000);
1167 l_is_revision_control BOOLEAN := FALSE;
1168 l_is_lot_control BOOLEAN := FALSE;
1169 l_is_serial_control BOOLEAN := FALSE;
1170 l_tree_mode NUMBER;
1171 l_api_version_number CONSTANT NUMBER := 1.0;
1172 l_api_name CONSTANT VARCHAR2(30) := 'Get_Avaliable_Quantity';
1173 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1174 l_tree_id INTEGER;
1175 l_rqoh NUMBER;
1176 l_qr NUMBER;
1177 l_qs NUMBER;
1178 l_atr NUMBER;
1179 l_pqoh NUMBER;
1180
1181 l_srqoh NUMBER;
1182 l_sqr NUMBER;
1183 l_sqs NUMBER;
1184 l_satr NUMBER;
1185 l_spqoh NUMBER;
1186
1187 x_msg_count VARCHAR2(100);
1188 x_msg_data VARCHAR2(1000);
1189 p_api_version_number number;
1190 p_init_msg_lst VARCHAR2(30);
1191 l_asset_sub_only BOOLEAN := FALSE;
1192
1193 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1194 BEGIN
1195 inv_quantity_tree_pvt.clear_quantity_cache;
1196
1197 IF p_is_revision_control = 'true' THEN
1198 l_is_revision_control := TRUE;
1199 END IF;
1200
1201 IF p_is_lot_control = 'true' THEN
1202 l_is_lot_control := TRUE;
1203 END IF;
1204
1205 IF p_is_serial_control = 'true' THEN
1206 l_is_serial_control := TRUE;
1207 END IF;
1208 IF p_tree_mode IS NULL THEN
1209 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
1210 ELSE l_tree_mode := p_tree_mode;
1211 END IF ;
1212
1213
1214 -- Initialize message list.
1215 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1216 fnd_msg_pub.initialize;
1217 END IF;
1218
1219 INV_QUANTITY_TREE_PVT.create_tree
1220 ( p_api_version_number => 1.0
1221 , p_init_msg_lst => fnd_api.g_false
1222 , x_return_status => l_return_status
1223 , x_msg_count => x_msg_count
1224 , x_msg_data => x_msg_data
1225 , p_organization_id => p_organization_id
1226 , p_inventory_item_id => p_inventory_item_id
1227 , p_tree_mode => p_tree_mode
1228 , p_is_revision_control => l_is_revision_control
1229 , p_is_lot_control => l_is_lot_control
1230 , p_is_serial_control => l_is_serial_control
1231 , p_asset_sub_only => l_asset_sub_only
1232 , p_include_suggestion => FALSE
1233 , p_demand_source_type_id => -9999
1234 , p_demand_source_header_id => -9999
1235 , p_demand_source_line_id => -9999
1236 , p_demand_source_name => NULL
1237 , p_demand_source_delivery => NULL
1238 , p_lot_expiration_date => NULL
1239 , p_grade_code => NULL
1240 , x_tree_id => l_tree_id
1241 , p_onhand_source => 3 --g_all_subs
1242 , p_exclusive => 0 --g_non_exclusive
1243 , p_pick_release => 0 --g_pick_release_no
1244 ) ;
1245
1246
1247 IF l_return_status = fnd_api.g_ret_sts_error THEN
1248 RAISE fnd_api.g_exc_error;
1249 END IF ;
1250
1251 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1252 RAISE fnd_api.g_exc_unexpected_error;
1253 END IF;
1254
1255 INV_QUANTITY_TREE_PVT.query_tree
1256 ( p_api_version_number => 1.0
1257 , p_init_msg_lst => fnd_api.g_false
1258 , x_return_status => l_return_status
1259 , x_msg_count => x_msg_count
1260 , x_msg_data => x_msg_data
1261 , p_tree_id => l_tree_id
1262 , p_revision => p_revision
1263 , p_lot_number => p_lot_number
1264 , p_subinventory_code => p_subinventory_code
1265 , p_locator_id => p_locator_id
1266 , x_qoh => x_qoh
1267 , x_rqoh => l_rqoh
1268 , x_pqoh => x_pqoh
1269 , x_qr => l_qr
1270 , x_qs => l_qs
1271 , x_att => x_att
1272 , x_atr => l_atr
1273 , x_sqoh => x_sqoh
1274 , x_srqoh => l_srqoh
1275 , x_spqoh => x_spqoh
1276 , x_sqr => l_sqr
1277 , x_sqs => l_sqs
1278 , x_satt => x_satt
1279 , x_satr => l_satr
1280 , p_cost_group_id => p_cost_group_id
1281 , p_transfer_subinventory_code=> p_transfer_subinventory);
1282
1283
1284 IF l_return_status = fnd_api.g_ret_sts_error THEN
1285 RAISE fnd_api.g_exc_error;
1286 END IF ;
1287
1288 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1289 RAISE fnd_api.g_exc_unexpected_error;
1290 END IF;
1291
1292 -- This query_tree quaries qoh and att at Item level,
1293 -- so we are passing null for p_subinventory_code and p_locator_id
1294
1295 INV_QUANTITY_TREE_PVT.query_tree
1296 ( p_api_version_number => 1.0
1297 , p_init_msg_lst => fnd_api.g_false
1298 , x_return_status => l_return_status
1299 , x_msg_count => x_msg_count
1300 , x_msg_data => x_msg_data
1301 , p_tree_id => l_tree_id
1302 , p_revision => p_revision
1303 , p_lot_number => p_lot_number
1304 --, p_grade_code => p_grade_code
1305 , p_subinventory_code => NULL
1306 , p_locator_id => NULL
1307 , x_qoh => x_qoh1
1308 , x_rqoh => l_rqoh
1309 , x_pqoh => l_pqoh
1310 , x_qr => l_qr
1311 , x_qs => l_qs
1312 , x_att => x_atpp1
1313 , x_atr => l_atr
1314 , x_sqoh => x_sqoh1
1315 , x_srqoh => l_srqoh
1316 , x_spqoh => l_spqoh
1317 , x_sqr => l_sqr
1318 , x_sqs => l_sqs
1319 , x_satt => x_satpp1
1320 , x_satr => l_satr
1321 , p_cost_group_id => p_cost_group_id);
1322
1323
1324 IF l_return_status = fnd_api.g_ret_sts_error THEN
1325 RAISE fnd_api.g_exc_error;
1326 END IF ;
1327
1328 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1329 RAISE fnd_api.g_exc_unexpected_error;
1330 END IF;
1331
1332 INV_QUANTITY_TREE_PVT.get_total_qoh
1333 ( x_return_status => l_return_status
1334 , x_msg_count => x_msg_count
1335 , x_msg_data => x_msg_data
1336 , p_tree_id => l_tree_id
1337 , p_revision => p_revision
1338 , p_lot_number => p_lot_number
1339 --, p_grade_code => p_grade_code
1340 , p_subinventory_code => p_subinventory_code
1341 , p_locator_id => p_locator_id
1342 , p_cost_group_id => p_cost_group_id
1343 , x_tqoh => x_tqoh
1344 , x_stqoh => x_stqoh
1345 );
1346
1347
1348 IF l_return_status = fnd_api.g_ret_sts_error THEN
1349 RAISE fnd_api.g_exc_error;
1350 END IF ;
1351
1352 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1353 RAISE fnd_api.g_exc_unexpected_error;
1354 END IF;
1355
1356 x_return_status := l_return_status;
1357
1358 EXCEPTION
1359
1360 WHEN fnd_api.g_exc_error THEN
1361 x_return_status := fnd_api.g_ret_sts_error;
1362
1363 -- Get message count and data
1364 fnd_msg_pub.count_and_get
1365 ( p_count => x_msg_count
1366 , p_data => x_msg_data
1367 );
1368
1369 WHEN fnd_api.g_exc_unexpected_error THEN
1370 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1371
1372 -- Get message count and data
1373 fnd_msg_pub.count_and_get
1374 ( p_count => x_msg_count
1375 , p_data => x_msg_data
1376 );
1377
1378 WHEN OTHERS THEN
1379 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1380
1381 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1382 THEN
1383
1384 fnd_msg_pub.add_exc_msg
1385 ( g_pkg_name
1386 , l_api_name
1387 );
1388 END IF;
1389
1390 -- Get message count and data
1391 fnd_msg_pub.count_and_get
1392 ( p_count => x_msg_count
1393 , p_data => x_msg_data
1394 );
1395
1396
1397 END get_available_quantity;
1398
1399 -- Bug 14516283
1400 -- Passing to/transfer locator
1401
1402 PROCEDURE GET_AVAILABLE_QUANTITY(
1403 x_return_status OUT NOCOPY VARCHAR2,
1404 p_tree_mode IN NUMBER,
1405 p_organization_id IN NUMBER,
1406 p_inventory_item_id IN NUMBER,
1407 p_is_revision_control IN VARCHAR2,
1408 p_is_lot_control IN VARCHAR2,
1409 p_is_serial_control IN VARCHAR2,
1410 p_revision IN VARCHAR2,
1411 p_lot_number IN VARCHAR2,
1412 p_lot_expiration_date IN DATE,
1413 p_subinventory_code IN VARCHAR2,
1414 p_locator_id IN NUMBER,
1415 p_source_type_id IN NUMBER,
1416 p_cost_group_id IN NUMBER,
1417 p_to_subinventory_code IN VARCHAR2,
1418 p_to_locator_id IN NUMBER,
1419 x_qoh OUT NOCOPY NUMBER,
1420 x_att OUT NOCOPY NUMBER,
1421 x_sqoh OUT NOCOPY NUMBER, -- inv converge
1422 x_satt OUT NOCOPY NUMBER -- inv converge
1423 )
1424 IS
1425 l_msg_count VARCHAR2(100);
1426 l_msg_data VARCHAR2(1000);
1427 l_rqoh NUMBER;
1428 l_qr NUMBER;
1429 l_qs NUMBER;
1430 l_atr NUMBER;
1431 l_is_revision_control BOOLEAN := FALSE;
1432 l_is_lot_control BOOLEAN := FALSE;
1433 l_is_serial_control BOOLEAN := FALSE;
1434 l_tree_mode NUMBER;
1435
1436 l_srqoh NUMBER;
1437 l_sqr NUMBER;
1438 l_sqs NUMBER;
1439 l_satr NUMBER;
1440 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1441 BEGIN
1442 inv_quantity_tree_pub.clear_quantity_cache;
1443 IF p_is_revision_control = 'true' THEN
1444 l_is_revision_control := TRUE;
1445 END IF;
1446
1447 IF p_is_lot_control = 'true' THEN
1448 l_is_lot_control := TRUE;
1449 END IF;
1450
1451 IF p_is_serial_control = 'true' THEN
1452 l_is_serial_control := TRUE;
1453 END IF;
1454
1455 IF p_tree_mode IS NULL THEN
1456 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
1457 ELSE l_tree_mode := p_tree_mode;
1458 END IF ;
1459
1460 inv_quantity_tree_pub.query_quantities(
1461 p_api_version_number => 1.0 ,
1462 p_init_msg_lst => fnd_api.g_false ,
1463 x_return_status => x_return_status ,
1464 x_msg_count => l_msg_count ,
1465 x_msg_data => l_msg_data ,
1466 p_organization_id => p_organization_id ,
1467 p_inventory_item_id => p_inventory_item_id ,
1468 p_tree_mode => l_tree_mode ,
1469 p_is_revision_control => l_is_revision_control ,
1470 p_is_lot_control => l_is_lot_control ,
1471 p_is_serial_control => l_is_serial_control ,
1472 p_demand_source_type_id => p_source_type_id ,
1473 p_revision => p_revision ,
1474 p_lot_number => p_lot_number ,
1475 p_lot_expiration_date => NULL ,
1476 p_grade_code => NULL ,
1477 p_subinventory_code => p_subinventory_code ,
1478 p_locator_id => p_locator_id ,
1479 p_cost_group_id => p_cost_group_id ,
1480 p_transfer_subinventory_code => p_to_subinventory_code ,
1481 p_transfer_locator_id => p_to_locator_id ,
1482 x_qoh => x_qoh ,
1483 x_rqoh => l_rqoh ,
1484 x_qr => l_qr ,
1485 x_qs => l_qs ,
1486 x_att => x_att ,
1487 x_atr => l_atr ,
1488 x_sqoh => x_sqoh ,
1489 x_srqoh => l_srqoh ,
1490 x_sqr => l_sqr ,
1491 x_sqs => l_sqs ,
1492 x_satt => x_satt ,
1493 x_satr => l_satr
1494 );
1495 IF (l_debug = 1) THEN
1496 mdebug('@'||l_msg_data||'@');
1497 END IF;
1498
1499 END GET_AVAILABLE_QUANTITY;
1500
1501
1502 /* CHECK_LOOSE_QUANTITY returns a 'true' string (p_ok_to_process)
1503 if there is sufficient loose (unpacked) quantity at a location
1504 to complete the transaction, 'false' otherwise. */
1505
1506 PROCEDURE CHECK_LOOSE_QUANTITY(
1507 p_api_version_number IN NUMBER
1508 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1509 , x_return_status OUT NOCOPY VARCHAR2
1510 , x_msg_count OUT NOCOPY NUMBER
1511 , x_msg_data OUT NOCOPY VARCHAR2
1512 , p_organization_id IN NUMBER
1513 , p_inventory_item_id IN NUMBER
1514 , p_is_revision_control IN VARCHAR2
1515 , p_is_lot_control IN VARCHAR2
1516 , p_is_serial_control IN VARCHAR2
1517 , p_revision IN VARCHAR2
1518 , p_lot_number IN VARCHAR2
1519 , p_transaction_quantity IN NUMBER
1520 , p_transaction_uom IN VARCHAR2
1521 , p_subinventory_code IN VARCHAR2
1522 , p_locator_id IN NUMBER
1523 , p_transaction_temp_id IN NUMBER
1524 , p_ok_to_process OUT NOCOPY VARCHAR2
1525 , p_transfer_subinventory IN VARCHAR2
1526 )
1527
1528 IS
1529 l_att NUMBER;
1530 l_qoh NUMBER;
1531 l_rqoh NUMBER;
1532 l_qr NUMBER;
1533 l_qs NUMBER;
1534 l_atr NUMBER;
1535 l_lot_exp_dt DATE;
1536 l_moq NUMBER;
1537 l_avail_qty NUMBER;
1538 l_uom_rate NUMBER;
1539 l_txn_qty NUMBER;
1540
1541 l_ok_to_process VARCHAR2(5);
1542
1543 l_is_revision_control BOOLEAN := FALSE;
1544 l_is_lot_control BOOLEAN := FALSE;
1545 l_is_serial_control BOOLEAN := FALSE;
1546 l_cost_group_id mtl_material_transactions_temp.cost_group_id%type;
1547 l_primary_uom_code mtl_material_transactions_temp.item_primary_uom_code%type;
1548 l_inv_rcpt_code mtl_parameters.negative_inv_receipt_code%type;
1549
1550 l_api_version_number CONSTANT NUMBER := 1.0;
1551 l_api_name CONSTANT VARCHAR2(30) := 'Check_Looose_Quantity';
1552 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1553
1554 l_transaction_source_type_id NUMBER;
1555 l_new_qoh NUMBER;
1556 l_new_att NUMBER;
1557 l_new_pqoh NUMBER;
1558 l_new_tqoh NUMBER;
1559 l_new_atpp1 NUMBER;
1560 l_new_qoh1 NUMBER;
1561
1562 l_suggested_sub_code VARCHAR2(30);
1563 l_suggested_loc_id NUMBER;
1564 l_cgcnt NUMBER;
1565
1566 CURSOR c_org IS
1567 SELECT negative_inv_receipt_code
1568 FROM mtl_parameters
1569 WHERE organization_id = p_organization_id;
1570
1571 CURSOR c_lot_exp IS
1572 SELECT expiration_date
1573 FROM mtl_lot_numbers
1574 WHERE inventory_item_id = p_inventory_item_id
1575 AND organization_id = p_organization_id
1576 AND lot_number = p_lot_number;
1577
1578 CURSOR c_mmtt IS
1579 SELECT cost_group_id,transaction_source_type_id, subinventory_code, locator_id
1580 FROM mtl_material_transactions_temp
1581 WHERE transaction_temp_id = p_transaction_temp_id;
1582
1583 CURSOR c_item IS
1584 SELECT primary_uom_code
1585 FROM mtl_system_items
1586 WHERE inventory_item_id = p_inventory_item_id
1587 AND organization_id = p_organization_id;
1588
1589 CURSOR c_moq(x_cost_group_id number) IS
1590 select count(*)
1591 from mtl_onhand_quantities_detail
1592 -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
1593 where organization_id = p_organization_id
1594 and inventory_item_id = p_inventory_item_id
1595 and subinventory_code = p_subinventory_code
1596 and locator_id = p_locator_id
1597 and nvl(lot_number, '###') = nvl(p_lot_number, nvl(lot_number,'###'))
1598 and containerized_flag =2
1599 and cost_group_id <> x_cost_group_id;
1600
1601 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1602 BEGIN
1603
1604 IF (l_debug = 1) THEN
1605 mdebug ('Start check_loose_quantity.');
1606 END IF;
1607
1608 inv_quantity_tree_pub.clear_quantity_cache;
1609
1610 -- Standard call to check for call compatibility
1611 IF NOT fnd_api.compatible_api_call(l_api_version_number
1612 , p_api_version_number
1613 , l_api_name
1614 , G_PKG_NAME
1615 ) THEN
1616 RAISE fnd_api.g_exc_unexpected_error;
1617 END IF;
1618 IF (l_debug = 1) THEN
1619 mdebug ('Done checking if compatible api call.');
1620 END IF;
1621
1622 -- Initialize message list.
1623 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1624 fnd_msg_pub.initialize;
1625 END IF;
1626
1627 p_ok_to_process := 'false';
1628
1629 --
1630 -- Initialize variables
1631 --
1632 IF p_is_revision_control = 'true' THEN
1633 l_is_revision_control := TRUE;
1634 END IF;
1635
1636 IF p_is_serial_control = 'true' THEN
1637 l_is_serial_control := TRUE;
1638 END IF;
1639 IF (l_debug = 1) THEN
1640 mdebug ('Done initializing variables.');
1641 END IF;
1642
1643 --
1644 -- Find the lot expiration date if
1645 -- the item is lot controlled.
1646 --
1647 IF p_is_lot_control = 'true' THEN
1648 l_is_lot_control := TRUE;
1649 OPEN c_lot_exp;
1650 FETCH c_lot_exp INTO l_lot_exp_dt;
1651 CLOSE c_lot_exp;
1652 END IF;
1653
1654 -- Find the cost group id being transacted
1655 OPEN c_mmtt;
1656 FETCH c_mmtt
1657 INTO
1658 l_cost_group_id, l_transaction_source_type_id,
1659 l_suggested_sub_code, l_suggested_loc_id;
1660 CLOSE c_mmtt;
1661 IF (l_debug = 1) THEN
1662 mdebug ('Cost group id from mmtt: '||l_cost_group_id);
1663 END IF;
1664
1665 -- Find the primary UOM code for the item
1666 OPEN c_item;
1667 FETCH c_item INTO l_primary_uom_code;
1668 CLOSE c_item;
1669 IF (l_debug = 1) THEN
1670 mdebug ('Primary UOM for this item: '||l_primary_uom_code);
1671 END IF;
1672
1673 -- Find if -ve inventory balances are allowed for this org
1674 OPEN c_org;
1675 FETCH c_org INTO l_inv_rcpt_code;
1676 CLOSE c_org;
1677 IF (l_debug = 1) THEN
1678 mdebug ('-ve inv rcpt code is: '||l_inv_rcpt_code);
1679 END IF;
1680
1681 -- Translate picked qty/uom into primary uom qty
1682 inv_convert.inv_um_conversion(
1683 from_unit => p_transaction_uom
1684 , to_unit => l_primary_uom_code
1685 , item_id => p_inventory_item_id
1686 , uom_rate => l_uom_rate
1687 );
1688 l_txn_qty := p_transaction_quantity * l_uom_rate;
1689 IF (l_debug = 1) THEN
1690 mdebug ('Transaction qty in primary units: '||l_txn_qty);
1691 END IF;
1692
1693 if l_cost_group_id is not null then
1694 OPEN c_moq(l_cost_group_id);
1695 FETCH c_moq INTO l_cgcnt;
1696 CLOSE c_moq;
1697 IF (l_debug = 1) THEN
1698 mdebug ('count for different cost group than the allocated: '||l_cgcnt);
1699 END IF;
1700 if l_cgcnt >=1 then
1701 p_ok_to_process := 'costgroup';
1702 x_return_status := l_return_status;
1703 x_msg_count:= 0;
1704 x_msg_data := null;
1705 return;
1706 end if;
1707 end if;
1708
1709
1710 inv_txn_validations.get_available_quantity
1711 (x_return_status => l_return_status,
1712 p_tree_mode => INV_Quantity_Tree_PUB.g_loose_only_mode,
1713 p_organization_id => p_organization_id,
1714 p_inventory_item_id => p_inventory_item_id,
1715 p_is_revision_control => p_is_revision_control,
1716 p_is_lot_control => p_is_lot_control,
1717 p_is_serial_control => p_is_serial_control ,
1718 p_revision => p_revision,
1719 p_lot_number => p_lot_number ,
1720 p_lot_expiration_date => l_lot_exp_dt,
1721 p_subinventory_code => p_subinventory_code,
1722 p_locator_id => p_locator_id,
1723 p_source_type_id => l_transaction_source_type_id,
1724 x_qoh => l_new_qoh,
1725 x_att => l_new_att,
1726 x_pqoh => l_new_pqoh,
1727 x_tqoh => l_new_tqoh,
1728 x_atpp1 => l_new_atpp1,
1729 x_qoh1 => l_new_qoh1,
1730 p_cost_group_id => l_cost_group_id,
1731 p_transfer_subinventory => p_transfer_subinventory);
1732
1733 IF (l_debug = 1) THEN
1734 mdebug(l_new_qoh || ' ' || l_new_att || ' ' || l_new_pqoh || ' ' || l_new_tqoh || ' ' || l_new_atpp1 || ' ' || l_new_qoh1);
1735 END IF;
1736
1737 -- If org allows negative inventory balances
1738 --
1739 IF l_inv_rcpt_code = 1 THEN
1740
1741 IF (l_debug = 1) THEN
1742 mdebug('org allows negative inventory balances');
1743 END IF;
1744
1745 p_ok_to_process := 'true';
1746
1747 IF (l_new_att < l_txn_qty) THEN
1748
1749 IF l_suggested_sub_code <> p_subinventory_code OR
1750 l_suggested_loc_id <> p_locator_id THEN
1751
1752 IF (l_debug = 1) THEN
1753 mdebug('suggested sub/loc are different from the actual sub/loc');
1754 END IF;
1755
1756 IF (least(nvl(l_new_att, 0), nvl(l_new_tqoh - l_new_pqoh,0)) >= l_txn_qty) THEN
1757
1758 p_ok_to_process := 'true';
1759
1760 ELSE
1761 /*
1762 Bug #2075166.
1763 When negative inventory is allowed for the organization
1764 Change the ok_to_process flag to warning in order that
1765 a warning message id displayed instead of error.
1766 */
1767 p_ok_to_process := 'warning';
1768 IF (l_debug = 1) THEN
1769 mdebug('Driving inventory negative. Throw a warning');
1770 END IF;
1771
1772 END IF;
1773
1774 ELSE
1775
1776 IF (l_debug = 1) THEN
1777 mdebug('suggested sub/loc are same as the actual sub/loc');
1778 END IF;
1779
1780 IF (least((nvl(l_new_att,0) + l_txn_qty), nvl(l_new_tqoh - l_new_pqoh,0)) >= l_txn_qty) THEN
1781
1782 p_ok_to_process := 'true';
1783
1784 ELSE
1785 /*
1786 Bug #2075166.
1787 When negative inventory is allowed for the organization
1788 Change the ok_to_process flag to warning in order that
1789 a warning message id displayed instead of error.
1790 */
1791 p_ok_to_process := 'warning';
1792 IF (l_debug = 1) THEN
1793 mdebug('Driving inventory negative. Throw a warning');
1794 END IF;
1795 --p_ok_to_process := 'false';
1796 --mdebug('Cannot drive inventory negative when reservations exist');
1797 END IF;
1798
1799 END IF;
1800
1801 END IF;
1802
1803 x_return_status := l_return_status; /* Success */
1804 return;
1805 END IF;
1806
1807 --
1808 -- Org does not allow negative inventory balances
1809 -- so continue.
1810 IF (l_debug = 1) THEN
1811 mdebug('org does not allow negative inventory balances');
1812 END IF;
1813
1814 IF (l_new_att < l_txn_qty) THEN
1815
1816 IF (l_debug = 1) THEN
1817 mdebug('l_new_att < l_txn_qty');
1818 END IF;
1819
1820 IF l_suggested_sub_code <> p_subinventory_code OR
1821 l_suggested_loc_id <> p_locator_id THEN
1822
1823 IF (l_debug = 1) THEN
1824 mdebug('suggested sub/loc are different from the actual sub/loc');
1825 END IF;
1826
1827 IF (least(nvl(l_new_att, 0), nvl(l_new_tqoh - l_new_pqoh,0)) >= l_txn_qty) THEN
1828
1829 p_ok_to_process := 'true';
1830
1831 ELSE
1832
1833 p_ok_to_process := 'false';
1834
1835 END IF;
1836
1837 ELSE
1838
1839 IF (l_debug = 1) THEN
1840 mdebug('suggested sub/loc are the same as the actual sub/loc');
1841 END IF;
1842
1843 IF (least((nvl(l_new_att,0) + l_txn_qty), nvl(l_new_tqoh - l_new_pqoh,0)) >= l_txn_qty) THEN
1844
1845 p_ok_to_process := 'true';
1846
1847 ELSE
1848
1849 p_ok_to_process := 'false';
1850
1851 END IF;
1852
1853 END IF;
1854
1855 ELSE
1856
1857 p_ok_to_process := 'true';
1858 END IF;
1859
1860 x_return_status := l_return_status;
1861
1862 EXCEPTION
1863 WHEN fnd_api.g_exc_error THEN
1864 x_return_status := fnd_api.g_ret_sts_error;
1865
1866 -- Get message count and data
1867 fnd_msg_pub.count_and_get
1868 ( p_count => x_msg_count
1869 , p_data => x_msg_data
1870 );
1871 IF (l_debug = 1) THEN
1872 mdebug ('@'||x_msg_data||'@');
1873 END IF;
1874
1875 WHEN fnd_api.g_exc_unexpected_error THEN
1876 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1877
1878 -- Get message count and data
1879 fnd_msg_pub.count_and_get
1880 ( p_count => x_msg_count
1881 , p_data => x_msg_data
1882 );
1883 IF (l_debug = 1) THEN
1884 mdebug ('@'||x_msg_data||'@');
1885 END IF;
1886
1887 WHEN OTHERS THEN
1888 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1889
1890 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1891 THEN
1892 fnd_msg_pub.add_exc_msg
1893 ( g_pkg_name
1894 , l_api_name
1895 );
1896 END IF;
1897
1898 -- Get message count and data
1899 fnd_msg_pub.count_and_get
1900 ( p_count => x_msg_count
1901 , p_data => x_msg_data
1902 );
1903 IF (l_debug = 1) THEN
1904 mdebug ('@'||x_msg_data||'@');
1905 END IF;
1906
1907 END check_loose_quantity;
1908
1909
1910 PROCEDURE CHECK_WMS_INSTALL (
1911 x_return_status OUT NOCOPY VARCHAR2,
1912 p_msg_count OUT NOCOPY NUMBER,
1913 p_msg_data OUT NOCOPY VARCHAR2,
1914 p_org IN NUMBER
1915 )
1916 IS
1917 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1918 BEGIN
1919 IF wms_install.check_install(x_return_status,
1920 p_msg_count,
1921 p_msg_data,
1922 p_org) THEN
1923 x_return_status := 'Y';
1924 ELSE
1925 x_return_status := 'N';
1926 END IF;
1927
1928 END check_WMS_install;
1929
1930
1931
1932 FUNCTION check_lpn_reservation(p_lpn_id IN NUMBER,
1933 p_org_id IN NUMBER,
1934 x_return_msg OUT NOCOPY VARCHAR2)
1935 RETURN VARCHAR2
1936
1937 IS
1938 l_lpn_id NUMBER;
1939 l_cnt NUMBER :=0 ;
1940 x_return VARCHAR2(1) := 'Y';
1941
1942 CURSOR c_lpn_content IS
1943 select lpn_id
1944 from wms_license_plate_numbers
1945 where outermost_lpn_id = p_lpn_id
1946 and organization_id = p_org_id;
1947
1948 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1949 BEGIN
1950 OPEN c_lpn_content;
1951 LOOP
1952 FETCH c_lpn_content INTO l_lpn_id;
1953 EXIT WHEN c_lpn_content%NOTFOUND;
1954
1955
1956 select count(*)
1957 into l_cnt
1958 from mtl_reservations
1959 where lpn_id = l_lpn_id
1960 and organization_id = p_org_id;
1961
1962 if l_cnt >= 1 then
1963 IF (l_debug = 1) THEN
1964 mdebug ('lpn '||l_lpn_id||' is reserved.');
1965 END IF;
1966 x_return := 'N';
1967 fnd_message.set_name('INV', 'INV_LPN_RESERVED');
1968 x_return_msg := fnd_message.get;
1969 RETURN x_return;
1970 end if;
1971
1972 END LOOP;
1973 CLOSE c_lpn_content;
1974 x_return_msg := 'SUCCESS';
1975 RETURN x_return;
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 IF (l_debug = 1) THEN
1979 mdebug ('Other exception raised in check_lpn_reservation');
1980 END IF;
1981 x_return := 'N';
1982 x_return_msg := 'OTHER ERROR';
1983 RETURN x_return;
1984 END check_lpn_reservation;
1985
1986
1987
1988 FUNCTION check_lpn_allocation(p_lpn_id IN NUMBER,
1989 p_org_id IN NUMBER,
1990 x_return_msg OUT NOCOPY VARCHAR2)
1991 RETURN VARCHAR2
1992
1993 IS
1994 l_lpn_id NUMBER;
1995 l_cnt NUMBER :=0 ;
1996 x_return VARCHAR2(1) := 'Y';
1997
1998 CURSOR c_lpn_content IS
1999 select lpn_id
2000 from wms_license_plate_numbers
2001 where outermost_lpn_id = p_lpn_id
2002 and organization_id = p_org_id;
2003
2004 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2005 BEGIN
2006 OPEN c_lpn_content;
2007 LOOP
2008 FETCH c_lpn_content INTO l_lpn_id;
2009 EXIT WHEN c_lpn_content%NOTFOUND;
2010
2011 select count(*)
2012 into l_cnt
2013 from mtl_material_transactions_temp
2014 where allocated_lpn_id = l_lpn_id
2015 and organization_id = p_org_id;
2016
2017 if l_cnt >=1 then
2018 IF (l_debug = 1) THEN
2019 mdebug ('lpn '||l_lpn_id||' is allocated.');
2020 END IF;
2021 x_return := 'N';
2022 fnd_message.set_name('INV', 'INV_LPN_ALLOCATED');
2023 x_return_msg := fnd_message.get;
2024 RETURN x_return;
2025 end if;
2026 END LOOP;
2027 CLOSE c_lpn_content;
2028 x_return_msg := 'SUCCESS';
2029 RETURN x_return;
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 IF (l_debug = 1) THEN
2033 mdebug ('Other exception raised in check_lpn_allocation');
2034 END IF;
2035 x_return := 'N';
2036 x_return_msg := 'OTHER ERROR';
2037 RETURN x_return;
2038 END check_lpn_allocation;
2039
2040
2041 FUNCTION check_lpn_serial_allocation(p_lpn_id IN NUMBER,
2042 p_org_id IN NUMBER,
2043 x_return_msg OUT NOCOPY VARCHAR2)
2044 RETURN VARCHAR2
2045
2046 IS
2047 l_lpn_id NUMBER;
2048 l_cnt NUMBER:= 0;
2049 l_inventory_item_id NUMBER;
2050 l_serial_number VARCHAR2(30);
2051 x_return VARCHAR2(1) := 'Y';
2052
2053 CURSOR c_lpn_content IS
2054 select lpn_id
2055 from wms_license_plate_numbers
2056 where outermost_lpn_id = p_lpn_id
2057 and organization_id = p_org_id;
2058
2059 CURSOR c_lpn_serial(p_lpnid NUMBER) IS
2060 select serial_number, inventory_item_id
2061 from mtl_serial_numbers
2062 where lpn_id = p_lpnid
2063 and current_organization_id = p_org_id;
2064
2065 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2066 BEGIN
2067 OPEN c_lpn_content;
2068 LOOP
2069 FETCH c_lpn_content INTO l_lpn_id;
2070 EXIT WHEN c_lpn_content%NOTFOUND;
2071
2072 OPEN c_lpn_serial(l_lpn_id);
2073 LOOP
2074 FETCH c_lpn_serial INTO l_serial_number, l_inventory_item_id;
2075 EXIT WHEN c_lpn_serial%NOTFOUND;
2076
2077 begin
2078 select 1
2079 into l_cnt
2080 from mtl_serial_numbers_temp msnt,
2081 mtl_transaction_lots_temp mtlt,
2082 mtl_material_transactions_temp mmtt
2083 where mmtt.organization_id = p_org_id
2084 and mmtt.inventory_item_id = l_inventory_item_id
2085 and mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
2086 and l_serial_number between msnt.fm_serial_number and nvl(msnt.to_serial_number,msnt.fm_serial_number)
2087 and msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id);
2088
2089 if l_cnt = 1 then
2090 IF (l_debug = 1) THEN
2091 mdebug ('serial no: '||l_serial_number||' in lpn '||l_lpn_id||' is allocated.');
2092 END IF;
2093 x_return := 'N';
2094 fnd_message.set_name('INV', 'INV_SERIAL_ALLOCATED');
2095 x_return_msg := fnd_message.get;
2096 RETURN x_return;
2097 end if;
2098 exception
2099 when no_data_found then
2100 l_cnt := 0;
2101 when OTHERS then
2102 x_return := 'N';
2103 x_return_msg := 'OTHER ERROR';
2104 RETURN x_return;
2105 end;
2106 END LOOP;
2107 CLOSE c_lpn_serial;
2108 END LOOP;
2109 CLOSE c_lpn_content;
2110 x_return_msg := 'SUCCESS';
2111 RETURN x_return;
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 IF (l_debug = 1) THEN
2115 mdebug ('Other exception raised in check_serial_allocation');
2116 END IF;
2117 x_return := 'N';
2118 x_return_msg := 'OTHER ERROR';
2119 RETURN x_return;
2120 END check_lpn_serial_allocation;
2121
2122
2123 --Adding below function for bug 13449987--START
2124
2125 FUNCTION check_partial_lpn_loaded(p_lpn_id IN NUMBER,
2126 p_org_id IN NUMBER,
2127 x_return_msg OUT NOCOPY VARCHAR2)
2128 RETURN VARCHAR2
2129
2130 IS
2131 l_lpn_id NUMBER;
2132 l_lpn_number varchar2(30);
2133 l_cnt NUMBER :=0 ;
2134 x_return VARCHAR2(1) := 'Y';
2135
2136 CURSOR c_lpn_content IS
2137 select lpn_id
2138 from wms_license_plate_numbers
2139 where outermost_lpn_id = p_lpn_id
2140 and organization_id = p_org_id;
2141
2142 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2143 BEGIN
2144 OPEN c_lpn_content;
2145 IF (l_debug = 1) THEN
2146 mdebug ('Inside check_partial_lpn_loaded');
2147 END IF;
2148 LOOP
2149 FETCH c_lpn_content INTO l_lpn_id;
2150 EXIT WHEN c_lpn_content%NOTFOUND;
2151
2152 select count(*)
2153 into l_cnt
2154 from mtl_material_transactions_temp mmtt,wms_dispatched_tasks wdt
2155 where mmtt.lpn_id = l_lpn_id
2156 and mmtt.transaction_temp_id = wdt.transaction_temp_id
2157 and wdt.status=4 -- Loaded task
2158 and mmtt.organization_id = p_org_id;
2159
2160 if l_cnt >=1 then
2161 SELECT license_plate_number
2162 INTO l_lpn_number
2163 FROM wms_license_plate_numbers
2164 WHERE lpn_id=p_lpn_id;
2165
2166 IF (l_debug = 1) THEN
2167 mdebug ('LPN '||l_lpn_number||' has contents which are part of a loaded task.');
2168 END IF;
2169 x_return := 'N';
2170 fnd_message.set_name('WMS', 'WMS_LPN_LOADED');
2171 fnd_message.set_token('LPN_NAME',l_lpn_number);
2172 x_return_msg := fnd_message.get;
2173 IF (l_debug = 1) THEN
2174 mdebug (x_return_msg);
2175 END IF;
2176 RETURN x_return;
2177 end if;
2178 END LOOP;
2179 CLOSE c_lpn_content;
2180 x_return_msg := 'SUCCESS';
2181 RETURN x_return;
2182 EXCEPTION
2183 WHEN OTHERS THEN
2184 IF (l_debug = 1) THEN
2185 mdebug ('Other exception raised in check_partial_lpn_loaded');
2186 mdebug('Other Error:'||SQLERRM);
2187 END IF;
2188 x_return := 'N';
2189 x_return_msg := 'OTHER ERROR';
2190 RETURN x_return;
2191 END check_partial_lpn_loaded;
2192
2193 --Added the function for bug 13449987--END
2194
2195
2196 FUNCTION check_item_serial_allocation(p_item_id IN NUMBER,
2197 p_org_id IN NUMBER,
2198 p_serial_number IN VARCHAR2,
2199 x_return_msg OUT NOCOPY VARCHAR2)
2200 RETURN VARCHAR2
2201
2202 IS
2203 l_cnt NUMBER:= 0;
2204 x_return VARCHAR2(1) := 'Y';
2205
2206 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2207 BEGIN
2208
2209 select 1
2210 into l_cnt
2211 from mtl_serial_numbers_temp msnt,
2212 mtl_transaction_lots_temp mtlt,
2213 mtl_material_transactions_temp mmtt
2214 where mmtt.organization_id = p_org_id
2215 and mmtt.inventory_item_id = p_item_id
2216 and mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
2217 and p_serial_number between msnt.fm_serial_number and nvl(msnt.to_serial_number,msnt.fm_serial_number)
2218 and msnt.transaction_temp_id = nvl(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id);
2219
2220 if l_cnt = 1 then
2221 x_return := 'N';
2222 fnd_message.set_name('INV', 'INV_SERIAL_ALLOCATED');
2223 x_return_msg := fnd_message.get;
2224 RETURN x_return;
2225 end if;
2226
2227 exception
2228 when no_data_found then
2229 l_cnt := 0;
2230 x_return_msg := 'SUCCESS';
2231 RETURN x_return;
2232 WHEN OTHERS THEN
2233 IF (l_debug = 1) THEN
2234 mdebug ('Other exception raised in check_item_serial_allocation');
2235 END IF;
2236 x_return := 'N';
2237 x_return_msg := 'OTHER ERROR';
2238 RETURN x_return;
2239 END check_item_serial_allocation;
2240
2241
2242
2243 FUNCTION validate_lpn_status_quantity(
2244 p_lpn_id IN NUMBER,
2245 p_orgid IN NUMBER,
2246 p_to_org_id IN NUMBER,
2247 p_wms_installed IN VARCHAR2,
2248 p_transaction_type_id IN NUMBER,
2249 p_source_type_id IN NUMBER,
2250 x_return_msg OUT NOCOPY VARCHAR2
2251 )
2252 RETURN VARCHAR2
2253 IS
2254 x_return VARCHAR2(1);
2255 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2256 BEGIN
2257 x_return := inv_ui_item_sub_loc_lovs.vaildate_lpn_status(
2258 p_lpn_id,
2259 p_orgid,
2260 p_to_org_id,
2261 p_wms_installed,
2262 p_transaction_type_id
2263 );
2264 /*
2265 x_return_msg is set to 'VALIDATE_LPN_STATUS_FAILED' to indicate to the calling
2266 java code that 'vaildate_lpn_status' has failed.
2267 */
2268 if ( x_return = 'N' ) then
2269 x_return_msg := 'VALIDATE_LPN_STATUS_FAILED';
2270 return x_return;
2271 end if;
2272
2273 -- check if lpn/any inner lpn is allocated
2274 x_return := check_lpn_allocation(p_lpn_id, p_orgid, x_return_msg );
2275
2276 if ( x_return = 'N' ) then
2277 x_return_msg := 'VALIDATE_LPN_ALLOC_FAILED';
2278 return x_return;
2279 end if;
2280
2281 -- check if any serial number in lpn/any inner lpn is allocated
2282 x_return := check_lpn_serial_allocation(p_lpn_id, p_orgid, x_return_msg);
2283 if (x_return = 'N' ) then
2284 x_return_msg := 'VALIDATE_LPN_SERIAL_ALLOC_FAILED';
2285 return x_return;
2286 end if;
2287
2288 --Bug#4446248.Check if any pending transactions are there for this LPN/inner LPNs.
2289 --Adding this code in both forms of validate_lpn_status_quantity
2290 x_return := check_lpn_pending_txns(p_lpn_id, p_orgid, x_return_msg);
2291 if (x_return = 'N' ) then
2292 x_return_msg := 'VALIDATE_LPN_PENDING_TXNS_FAILED';
2293 return x_return;
2294 end if;
2295
2296 return check_lpn_quantity( p_lpn_id, p_orgid, p_source_type_id, p_transaction_type_id, x_return_msg);
2297
2298 END validate_lpn_status_quantity;
2299
2300
2301 -- Bug# 2358224
2302 -- Overloaded version of the previous function passing in
2303 -- the to/transfer subinventory. The only difference is
2304 -- that it calls check_lpn_quantity passing in the
2305 -- to/transfer subinventory input parameter
2306 FUNCTION validate_lpn_status_quantity(
2307 p_lpn_id IN NUMBER,
2308 p_orgid IN NUMBER,
2309 p_to_org_id IN NUMBER,
2310 p_wms_installed IN VARCHAR2,
2311 p_transaction_type_id IN NUMBER,
2312 p_source_type_id IN NUMBER,
2313 p_to_subinventory_code IN VARCHAR2,
2314 x_return_msg OUT NOCOPY VARCHAR2
2315 )
2316 RETURN VARCHAR2
2317 IS
2318 x_return VARCHAR2(1);
2319 l_count NUMBER;
2320 l_action_id NUMBER;
2321 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2322 BEGIN
2323 /** Bug 2403417 - add a check of the lpn context. If the lpn Context is "Picked"
2324 and transaction is subtransfer, return error INV_LPN_DELIVERY_ASSOC
2325 **/
2326
2327 if( p_source_type_id = INV_GLOBALS.G_SourceType_Inventory ) then
2328 select transaction_action_id
2329 into l_action_id
2330 From mtl_transaction_types
2331 where transaction_type_id = p_transaction_Type_id
2332 And transaction_Source_Type_id = p_source_type_id;
2333
2334 if( l_action_id = INV_GLOBALS.G_Action_Subxfr ) then
2335 select count(wdd.delivery_detail_id)
2336 into l_count
2337 From wsh_delivery_details wdd, wms_license_plate_numbers wlpn
2338 WHere wdd.lpn_id = wlpn.lpn_id
2339 and wlpn.lpn_context = wms_Container_pub.LPN_Context_Picked
2340 and wlpn.lpn_id = p_lpn_id
2341 and wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
2342
2343 if( l_count > 0 ) then
2344 x_return := 'N';
2345 x_return_msg := 'INV_LPN_DELIVERY_ASSOC';
2346 return x_return;
2347 end if;
2348 end if;
2349 end if;
2350 /** End changes for bug 2403417 **/
2351
2352 --Bug 5512205 Commented out the call to inv_ui_item_sub_loc_lovs.vaildate_lpn_status as the LPN statuses are already validated
2353 --while populating the LPN LOV in the Sub Xfer Page.
2354 /*
2355 x_return := inv_ui_item_sub_loc_lovs.vaildate_lpn_status(
2356 p_lpn_id,
2357 p_orgid,
2358 p_to_org_id,
2359 p_wms_installed,
2360 p_transaction_type_id
2361 );*/
2362 /*
2363 x_return_msg is set to 'VALIDATE_LPN_STATUS_FAILED' to indicate to the calling
2364 java code that 'vaildate_lpn_status' has failed.
2365 */
2366 /*
2367 if ( x_return = 'N' ) then
2368 x_return_msg := 'VALIDATE_LPN_STATUS_FAILED';
2369 return x_return;
2370 end if;
2371 */
2372 --End Bug 5512205
2373
2374 -- check if lpn/any inner lpn is allocated
2375 x_return := check_lpn_allocation(p_lpn_id, p_orgid, x_return_msg );
2376
2377 if ( x_return = 'N' ) then
2378 x_return_msg := 'VALIDATE_LPN_ALLOC_FAILED';
2379 return x_return;
2380 end if;
2381
2382 -- check if any serial number in lpn/any inner lpn is allocated
2383 x_return := check_lpn_serial_allocation(p_lpn_id, p_orgid, x_return_msg);
2384 if (x_return = 'N' ) then
2385 x_return_msg := 'VALIDATE_LPN_SERIAL_ALLOC_FAILED';
2386 return x_return;
2387 end if;
2388
2389 --Bug#4446248.Check if any pending transactions are there for this LPN/inner LPNs.
2390 --Adding this code in both forms of validate_lpn_status_quantity
2391 x_return := check_lpn_pending_txns(p_lpn_id, p_orgid, x_return_msg);
2392 if (x_return = 'N' ) then
2393 x_return_msg := 'VALIDATE_LPN_PENDING_TXNS_FAILED';
2394 return x_return;
2395 end if;
2396
2397 return check_lpn_quantity( p_lpn_id, p_orgid, p_source_type_id,p_transaction_type_id,p_to_subinventory_code, x_return_msg);
2398
2399 END validate_lpn_status_quantity;
2400
2401
2402
2403 FUNCTION validate_lpn_status_quantity2(
2404 p_lpn_id IN NUMBER,
2405 p_orgid IN NUMBER,
2406 p_to_org_id IN NUMBER,
2407 p_wms_installed IN VARCHAR2,
2408 p_transaction_type_id IN NUMBER,
2409 p_source_type_id IN NUMBER,
2410 x_return_msg OUT NOCOPY VARCHAR2
2411 )
2412 RETURN VARCHAR2
2413 IS
2414 x_return VARCHAR2(1);
2415 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2416 BEGIN
2417 x_return := inv_ui_item_sub_loc_lovs.vaildate_lpn_status(
2418 p_lpn_id,
2419 p_orgid,
2420 p_to_org_id,
2421 p_wms_installed,
2422 p_transaction_type_id
2423 );
2424 /*
2425 x_return_msg is set to 'VALIDATE_LPN_STATUS_FAILED' to indicate to the calling
2426 java code that 'vaildate_lpn_status' has failed.
2427 */
2428 if ( x_return = 'N' ) then
2429 x_return_msg := 'VALIDATE_LPN_STATUS_FAILED';
2430 return x_return;
2431 end if;
2432
2433 -- check if lpn/any inner lpn is reserveded
2434 x_return := check_lpn_reservation(p_lpn_id, p_orgid, x_return_msg );
2435
2436 if ( x_return = 'N' ) then
2437 x_return_msg := 'VALIDATE_LPN_RSV_FAILED';
2438 return x_return;
2439 end if;
2440
2441 -- check if lpn/any inner lpn is allocated;
2442 x_return := check_lpn_allocation(p_lpn_id, p_orgid, x_return_msg );
2443
2444 if ( x_return = 'N' ) then
2445 x_return_msg := 'VALIDATE_LPN_ALLOC_FAILED';
2446 return x_return;
2447 end if;
2448
2449 -- check if any serial number in lpn/any inner lpn is allocated
2450 x_return := check_lpn_serial_allocation(p_lpn_id, p_orgid, x_return_msg);
2451 if (x_return = 'N' ) then
2452 x_return_msg := 'VALIDATE_LPN_SERIAL_ALLOC_FAILED';
2453 return x_return;
2454 end if;
2455
2456 --Adding below for Bug 13449987
2457
2458 IF p_transaction_type_id IN (31,32) THEN --alias issue or miscellaneous issue
2459 IF (l_debug = 1) THEN
2460 mdebug ('Alias issue/Miscellaneous Issue. Checking whether the LPN or child LPNs has some of its contents loaded.');
2461 END IF;
2462
2463 x_return := check_partial_lpn_loaded(p_lpn_id, p_orgid, x_return_msg);
2464
2465 IF (x_return = 'N') THEN
2466 x_return_msg := 'VALIDATE_LPN_LOADED_FAILED';
2467 return x_return;
2468 END IF;
2469
2470 END IF ;
2471
2472 --Adding below for Bug 13449987
2473
2474 return check_lpn_quantity( p_lpn_id, p_orgid, p_source_type_id, p_transaction_type_id,x_return_msg);
2475
2476 END validate_lpn_status_quantity2;
2477
2478
2479
2480 -- returns Y for success and N for not
2481
2482 FUNCTION orgxfer_lpn_check(
2483 p_lpn_id IN NUMBER,
2484 p_orgid IN NUMBER,
2485 p_to_org_id IN NUMBER,
2486 p_wms_installed IN VARCHAR2,
2487 p_transaction_type_id IN NUMBER,
2488 p_source_type_id IN NUMBER,
2489 x_return_msg OUT NOCOPY VARCHAR2
2490 )
2491 RETURN VARCHAR2
2492 IS
2493 x_return VARCHAR2(1);
2494 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2495 BEGIN
2496
2497 --check the toorg
2498 x_return := INV_UI_ITEM_SUB_LOC_LOVS.validate_lpn_for_toorg(p_lpn_id, p_to_org_id, p_orgid, p_transaction_type_id);
2499 if (x_return <> 'Y') then
2500 x_return_msg := 'INVALID_TO_ORG';
2501 return x_return;
2502 end if;
2503
2504 --check the status
2505 x_return := inv_ui_item_sub_loc_lovs.vaildate_lpn_status( p_lpn_id,
2506 p_orgid,
2507 p_to_org_id,
2508 p_wms_installed,
2509 p_transaction_type_id);
2510 if ( x_return <> 'Y' ) then
2511 x_return_msg := 'INVALID_STATUS';
2512 return x_return;
2513 end if;
2514
2515 -- check if lpn/any inner lpn is reserveded;
2516 x_return := check_lpn_reservation(p_lpn_id, p_orgid, x_return_msg);
2517
2518 if ( x_return = 'N' ) then
2519 x_return_msg := 'VALIDATE_LPN_RSV_FAILED';
2520 return x_return;
2521 end if;
2522
2523 -- check if lpn/any inner lpn is allocated;
2524 x_return := check_lpn_allocation(p_lpn_id, p_orgid, x_return_msg);
2525
2526 if ( x_return = 'N' ) then
2527 x_return_msg := 'VALIDATE_LPN_ALLOC_FAILED';
2528 return x_return;
2529 end if;
2530
2531 --14586822 start
2532 -- check if lpn/any inner lpn is partially loaded;
2533 x_return := check_partial_lpn_loaded(p_lpn_id, p_orgid, x_return_msg);
2534
2535 if ( x_return = 'N' ) then
2536 x_return_msg := 'VALIDATE_LPN_LOADED_FAILED';
2537 return x_return;
2538 end if;
2539 --14586822 end
2540
2541 -- check if any serial number in lpn/any inner lpn is allocated
2542 x_return := check_lpn_serial_allocation(p_lpn_id, p_orgid, x_return_msg);
2543 if (x_return = 'N' ) then
2544 x_return_msg := 'VALIDATE_LPN_SERIAL_ALLOC_FAILED';
2545 return x_return;
2546 end if;
2547
2548 --check quantity
2549 x_return := check_lpn_quantity( p_lpn_id, p_orgid, p_source_type_id, p_transaction_type_id,x_return_msg);
2550 if ( x_return <> 'Y' ) then
2551 return x_return;
2552 end if;
2553
2554 return x_return;
2555
2556 END orgxfer_lpn_check;
2557
2558
2559 FUNCTION check_lpn_quantity(p_lpn_id IN NUMBER,
2560 p_organization_id IN NUMBER,
2561 p_source_type_id IN NUMBER,
2562 p_transaction_type_id IN NUMBER,
2563 x_return_msg OUT NOCOPY VARCHAR2)
2564 RETURN VARCHAR2
2565
2566 IS
2567 l_msg_count VARCHAR2(100);
2568 l_msg_data VARCHAR2(1000);
2569 l_rqoh NUMBER;
2570 l_qr NUMBER;
2571 l_qs NUMBER;
2572 l_atr NUMBER;
2573 x_return VARCHAR2(1);
2574 l_return_status VARCHAR2(1);
2575 l_is_revision_control BOOLEAN := FALSE;
2576 l_is_lot_control BOOLEAN := FALSE;
2577 l_is_serial_control BOOLEAN := FALSE;
2578 l_item_id NUMBER;
2579 l_revision VARCHAR2(3);
2580 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2581 l_lot_number VARCHAR2(80);
2582 l_subinventory_code VARCHAR2(10);
2583 l_locator_id NUMBER ;
2584 l_revison_control_code NUMBER;
2585 l_serial_number_control_code NUMBER;
2586 l_lot_control_code NUMBER ;
2587 l_att NUMBER;
2588 l_qoh NUMBER;
2589 --l_sum NUMBER;
2590 l_parent_lpn_id NUMBER;
2591 l_lpn_context NUMBER ;
2592 l_updt_qoh NUMBER;
2593 l_tree_mode INTEGER := INV_Quantity_Tree_PUB.g_transaction_mode;
2594
2595 TYPE l_rec IS RECORD (
2596 inventory_item_id NUMBER,
2597 parent_lpn_id NUMBER,
2598 --sumqty NUMBER,
2599 revision VARCHAR2(3),
2600 lpn_context NUMBER ,
2601 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2602 lot_number VARCHAR2(80));
2603
2604 l_record l_rec;
2605
2606 CURSOR l_item_cursor IS
2607 SELECT wlc.inventory_item_id,
2608 wlc.parent_lpn_id, -- lpn reservation change
2609 --SUM(wlc.quantity) sumqty, lpn reservation change
2610 wlc.revision,
2611 wlpn.lpn_context, wlc.lot_number
2612 FROM wms_lpn_contents wlc,
2613 wms_license_plate_numbers wlpn
2614 WHERE wlpn.outermost_lpn_id = p_lpn_id
2615 AND wlpn.organization_id = p_organization_id
2616 AND wlc.parent_lpn_id = wlpn.lpn_id
2617 GROUP BY wlc.parent_lpn_id, wlc.inventory_item_id, wlc.revision, wlpn.lpn_context,wlc.lot_number ;
2618
2619 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2620 BEGIN
2621 /* Assuming p_lpn_id CAN NOT BE NULL */
2622 x_return :='Y';
2623 x_return_msg :='';
2624
2625 /* if transaction is subxfe ormo subxfer we should conside reserved qty. since reserved qty can be subxfered*/
2626 if p_transaction_type_id in (2,64) then
2627 l_tree_mode := INV_Quantity_Tree_PUB.g_no_lpn_rsvs_mode;
2628 end if;
2629
2630 -- Clearing the quantity cache
2631 inv_quantity_tree_pub.clear_quantity_cache;
2632
2633 -- OPTIMIZED BELOW FOR PERFORMANCE MXGUPTA JUNE 4 2001
2634 -- SELECT DISTINCT wlpn.subinventory_code, wlpn.locator_id
2635 -- INTO l_subinventory_code, l_locator_id
2636 -- FROM wms_lpn_contents wlc,
2637 -- wms_license_plate_numbers wlpn
2638 -- WHERE wlpn.organization_id = p_organization_id
2639 -- AND wlpn.outermost_lpn_id = p_lpn_id
2640 -- AND wlc.parent_lpn_id = wlpn.lpn_id;
2641
2642 SELECT DISTINCT subinventory_code, locator_id
2643 INTO l_subinventory_code, l_locator_id
2644 FROM wms_license_plate_numbers
2645 WHERE organization_id = p_organization_id
2646 AND lpn_id = p_lpn_id;
2647
2648 OPEN l_item_cursor;
2649
2650 LOOP
2651 fetch l_item_cursor into l_record;
2652 exit when l_item_cursor%notfound;
2653
2654 l_item_id := l_record.inventory_item_id;
2655 l_revision := l_record.revision;
2656 l_parent_lpn_id := l_record.parent_lpn_id;
2657 --l_sum := l_record.sumqty;
2658 l_lpn_context := l_record.lpn_context;
2659 l_lot_number := l_record.lot_number;
2660
2661 IF l_lpn_context NOT IN (1) THEN
2662 x_return := 'N';
2663 fnd_message.set_name('INV', 'INV_NOT_INVENTORY');
2664 x_return_msg := fnd_message.get;
2665 RETURN x_return;
2666 END IF ;
2667
2668 SELECT revision_qty_control_code ,
2669 serial_number_control_code,
2670 lot_control_code
2671 INTO l_revison_control_code,
2672 l_serial_number_control_code,
2673 l_lot_control_code
2674 FROM mtl_system_items
2675 WHERE inventory_item_id = l_item_id
2676 AND organization_id = p_organization_id;
2677
2678 l_is_revision_control := FALSE;
2679 l_is_lot_control := FALSE;
2680 l_is_serial_control := FALSE;
2681
2682 IF l_revison_control_code = 2 THEN
2683 l_is_revision_control := TRUE;
2684 END IF;
2685 IF l_lot_control_code = 2 THEN
2686 l_is_lot_control := TRUE;
2687 END IF;
2688
2689 IF l_serial_number_control_code IN (2,5,6) THEN
2690 l_is_serial_control := TRUE;
2691 END IF;
2692
2693 inv_quantity_tree_pub.query_quantities
2694 ( p_api_version_number => 1.0
2695 , p_init_msg_lst => fnd_api.g_false
2696 , x_return_status => l_return_status
2697 , x_msg_count => l_msg_count
2698 , x_msg_data => l_msg_data
2699 , p_organization_id => p_organization_id
2700 , p_inventory_item_id => l_item_id
2701 , p_tree_mode => l_tree_mode
2702 , p_is_revision_control => l_is_revision_control
2703 , p_is_lot_control => l_is_lot_control
2704 , p_is_serial_control => l_is_serial_control
2705 , p_demand_source_type_id => p_source_type_id
2706 , p_revision => l_revision
2707 , p_lot_number => l_lot_number
2708 , p_subinventory_code => l_subinventory_code
2709 , p_locator_id => l_locator_id
2710 , x_qoh => l_qoh
2711 , x_rqoh => l_rqoh
2712 , x_qr => l_qr
2713 , x_qs => l_qs
2714 , x_att => l_att
2715 , x_atr => l_atr
2716 , p_lpn_id => l_parent_lpn_id --added for lpn reservation
2717 );
2718
2719 IF (l_return_status = 'S') THEN
2720 --IF (l_sum < l_att OR l_sum = l_att) THEN LPN reservation change
2721 IF (l_qoh = l_att) THEN
2722 x_return := 'Y';
2723 x_return_msg :='SUCCESS';
2724 ELSE
2725 x_return := 'F';
2726 FND_MESSAGE.set_name('INV', 'INV_LPN_QTY_ERR');
2727 x_return_msg :=fnd_message.get;
2728 RETURN x_return;
2729 END IF ;
2730 ELSE
2731 x_return :='F';
2732 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
2733 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
2734 x_return_msg := fnd_message.get;
2735 RETURN x_return;
2736 END IF ;
2737 l_updt_qoh := - l_qoh;
2738 /*need to update qty tree */
2739 inv_quantity_tree_pub.update_quantities
2740 ( p_api_version_number => 1.0
2741 , p_init_msg_lst => fnd_api.g_false
2742 , x_return_status => l_return_status
2743 , x_msg_count => l_msg_count
2744 , x_msg_data => l_msg_data
2745 , p_organization_id => p_organization_id
2746 , p_inventory_item_id => l_item_id
2747 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
2748 , p_is_revision_control => l_is_revision_control
2749 , p_is_lot_control => l_is_lot_control
2750 , p_is_serial_control => l_is_serial_control
2751 , p_demand_source_type_id => p_source_type_id
2752 , p_revision => l_revision
2753 , p_lot_number => l_lot_number
2754 , p_subinventory_code => l_subinventory_code
2755 , p_locator_id => l_locator_id
2756 , p_primary_quantity => l_updt_qoh
2757 , p_quantity_type => inv_quantity_tree_pvt.g_qoh
2758 , x_qoh => l_qoh
2759 , x_rqoh => l_rqoh
2760 , x_qr => l_qr
2761 , x_qs => l_qs
2762 , x_att => l_att
2763 , x_atr => l_atr
2764 , p_lpn_id => l_parent_lpn_id --added for lpn reservation
2765 );
2766
2767 END LOOP ;
2768 CLOSE l_item_cursor;
2769 RETURN x_return;
2770 EXCEPTION
2771 WHEN NO_DATA_FOUND THEN
2772 x_return_msg := 'NO_DATA_FOUND';
2773 x_return :='F';
2774 RETURN x_return;
2775 END check_lpn_quantity;
2776
2777
2778 -- Bug# 2358224
2779 -- Overloaded version of the previous function passing in
2780 -- the to/transfer subinventory. This is the same as the
2781 -- previous call with the only difference being that the
2782 -- call to inv_quantity_tree_pub.query_quantities passes
2783 -- the p_transfer_subinventory_code input parameter
2784
2785
2786 -- Bug # 2433095 -- Changes to LPN reservations ported to the ovreloaded
2787 -- function. Transaction_type id is also being passed to check for the sub
2788 -- and move order transfer
2789
2790 FUNCTION check_lpn_quantity(p_lpn_id IN NUMBER,
2791 p_organization_id IN NUMBER,
2792 p_source_type_id IN NUMBER,
2793 p_transaction_type_id IN NUMBER,
2794 p_to_subinventory_code IN VARCHAR2,
2795 x_return_msg OUT NOCOPY VARCHAR2)
2796 RETURN VARCHAR2
2797
2798 IS
2799 l_msg_count VARCHAR2(100);
2800 l_msg_data VARCHAR2(1000);
2801 l_rqoh NUMBER;
2802 l_qr NUMBER;
2803 l_qs NUMBER;
2804 l_atr NUMBER;
2805 x_return VARCHAR2(1);
2806 l_return_status VARCHAR2(1);
2807 l_is_revision_control BOOLEAN := FALSE;
2808 l_is_lot_control BOOLEAN := FALSE;
2809 l_is_serial_control BOOLEAN := FALSE;
2810 l_item_id NUMBER;
2811 l_revision VARCHAR2(3);
2812 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2813 l_lot_number VARCHAR2(80);
2814 l_subinventory_code VARCHAR2(10);
2815 l_locator_id NUMBER;
2816 l_revison_control_code NUMBER;
2817 l_serial_number_control_code NUMBER;
2818 l_lot_control_code NUMBER;
2819 l_att NUMBER;
2820 l_qoh NUMBER;
2821 -- l_sum NUMBER;
2822 l_parent_lpn_id NUMBER;
2823 l_lpn_context NUMBER ;
2824 l_updt_qoh NUMBER;
2825 l_tree_mode INTEGER := INV_Quantity_Tree_PUB.g_transaction_mode;
2826 l_inv_rcpt_code MTL_PARAMETERS.NEGATIVE_INV_RECEIPT_CODE%TYPE; -- Bug 13878269
2827
2828 TYPE l_rec IS RECORD (
2829 inventory_item_id NUMBER,
2830 parent_lpn_id NUMBER,
2831 --sumqty NUMBER,
2832 revision VARCHAR2(3),
2833 lpn_context NUMBER ,
2834 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2835 lot_number VARCHAR2(80));
2836
2837 l_record l_rec;
2838
2839 CURSOR l_item_cursor IS
2840 SELECT wlc.inventory_item_id,
2841 wlc.parent_lpn_id, -- lpn reservation change
2842 -- SUM(wlc.quantity) sumqty,
2843 wlc.revision,
2844 wlpn.lpn_context, wlc.lot_number
2845 FROM wms_lpn_contents wlc,
2846 wms_license_plate_numbers wlpn
2847 WHERE wlpn.outermost_lpn_id = p_lpn_id
2848 AND wlpn.organization_id = p_organization_id
2849 AND wlc.parent_lpn_id = wlpn.lpn_id
2850 GROUP BY wlc.parent_lpn_id, wlc.inventory_item_id, wlc.revision, wlpn.lpn_context,wlc.lot_number ;
2851
2852 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2853 BEGIN
2854 /* Assuming p_lpn_id CAN NOT BE NULL */
2855 x_return :='Y';
2856 x_return_msg :='';
2857
2858 IF (l_debug = 1) THEN
2859 mdebug ('Inside the overloaded function');
2860 END IF;
2861
2862 /* if transaction is subxfe ormo subxfer we should conside reserved qty. since reserved qty can be subxfered*/
2863 if p_transaction_type_id in (2,64) then
2864 l_tree_mode := INV_Quantity_Tree_PUB.g_no_lpn_rsvs_mode;
2865 end if;
2866
2867 -- Clearing the quantity cache
2868 inv_quantity_tree_pub.clear_quantity_cache;
2869
2870 -- OPTIMIZED BELOW FOR PERFORMANCE MXGUPTA JUNE 4 2001
2871 -- SELECT DISTINCT wlpn.subinventory_code, wlpn.locator_id
2872 -- INTO l_subinventory_code, l_locator_id
2873 -- FROM wms_lpn_contents wlc,
2874 -- wms_license_plate_numbers wlpn
2875 -- WHERE wlpn.organization_id = p_organization_id
2876 -- AND wlpn.outermost_lpn_id = p_lpn_id
2877 -- AND wlc.parent_lpn_id = wlpn.lpn_id;
2878
2879 --mdebug ('l Tree mode' || l_tree_mode);
2880
2881 SELECT DISTINCT subinventory_code, locator_id
2882 INTO l_subinventory_code, l_locator_id
2883 FROM wms_license_plate_numbers
2884 WHERE organization_id = p_organization_id
2885 AND lpn_id = p_lpn_id;
2886
2887 -- Bug 13878269
2888 IF (inv_cache.set_org_rec(p_organization_id => p_organization_id)) THEN
2889 l_inv_rcpt_code := inv_cache.org_rec.negative_inv_receipt_code;
2890 END IF;
2891
2892 OPEN l_item_cursor;
2893
2894 LOOP
2895
2896 fetch l_item_cursor into l_record;
2897 exit when l_item_cursor%notfound;
2898
2899 l_item_id := l_record.inventory_item_id;
2900 l_revision := l_record.revision;
2901 l_parent_lpn_id := l_record.parent_lpn_id;
2902 --l_sum := l_record.sumqty;
2903 l_lpn_context := l_record.lpn_context;
2904 l_lot_number := l_record.lot_number;
2905
2906 IF l_lpn_context NOT IN (1) THEN
2907 x_return := 'N';
2908 fnd_message.set_name('INV', 'INV_NOT_INVENTORY');
2909 x_return_msg := fnd_message.get;
2910 RETURN x_return;
2911 END IF ;
2912
2913 SELECT revision_qty_control_code ,
2914 serial_number_control_code,
2915 lot_control_code
2916 INTO l_revison_control_code,
2917 l_serial_number_control_code,
2918 l_lot_control_code
2919 FROM mtl_system_items
2920 WHERE inventory_item_id = l_item_id
2921 AND organization_id = p_organization_id;
2922
2923 l_is_revision_control := FALSE;
2924 l_is_lot_control := FALSE;
2925 l_is_serial_control := FALSE;
2926
2927 IF l_revison_control_code = 2 THEN
2928 l_is_revision_control := TRUE;
2929 END IF;
2930 IF l_lot_control_code = 2 THEN
2931 l_is_lot_control := TRUE;
2932 END IF;
2933
2934 IF l_serial_number_control_code IN (2,5,6) THEN
2935 l_is_serial_control := TRUE;
2936 END IF;
2937
2938 inv_quantity_tree_pub.query_quantities
2939 ( p_api_version_number => 1.0
2940 , p_init_msg_lst => fnd_api.g_false
2941 , x_return_status => l_return_status
2942 , x_msg_count => l_msg_count
2943 , x_msg_data => l_msg_data
2944 , p_organization_id => p_organization_id
2945 , p_inventory_item_id => l_item_id
2946 , p_tree_mode => l_tree_mode
2947 , p_is_revision_control => l_is_revision_control
2948 , p_is_lot_control => l_is_lot_control
2949 , p_is_serial_control => l_is_serial_control
2950 , p_demand_source_type_id => p_source_type_id
2951 , p_revision => l_revision
2952 , p_lot_number => l_lot_number
2953 , p_subinventory_code => l_subinventory_code
2954 , p_locator_id => l_locator_id
2955 , p_transfer_subinventory_code => p_to_subinventory_code
2956 , x_qoh => l_qoh
2957 , x_rqoh => l_rqoh
2958 , x_qr => l_qr
2959 , x_qs => l_qs
2960 , x_att => l_att
2961 , x_atr => l_atr
2962 , p_lpn_id => l_parent_lpn_id --added for lpn reservation
2963 );
2964
2965 -- Bug 13878269
2966 IF (l_debug = 1) THEN
2967 mdebug ('p_lpn_id: ' || p_lpn_id || ', l_qoh: ' || l_qoh || ', l_att: ' || l_att || ', l_atr: ' || l_atr);
2968 END IF;
2969
2970 IF (l_return_status = 'S') THEN
2971 -- IF (l_sum < l_att OR l_sum = l_att) THEN -- LPN reservation change
2972 IF (l_qoh = l_att) THEN
2973 x_return := 'Y';
2974 x_return_msg :='SUCCESS';
2975 ELSE
2976 x_return := 'F';
2977 -- Bug 13878269
2978 IF (l_inv_rcpt_code = 1 AND p_transaction_type_id = 2) THEN
2979 -- If the organization allows Negative balances and this a Subinventory Transfer transaction
2980 IF (l_debug = 1) THEN
2981 mdebug ('l_inv_rcpt_code: ' || l_inv_rcpt_code || ', p_transaction_type_id: ' || p_transaction_type_id);
2982 END IF;
2983
2984 IF (l_att < l_qoh) THEN
2985 IF (l_debug = 1) THEN
2986 mdebug ('LPN quantity greater than allowable transactable quantity');
2987 END IF;
2988 x_return_msg := 'VALIDATE_INV_LPN_QTY_ERR';
2989 END IF;
2990 ELSE
2991 FND_MESSAGE.set_name('INV', 'INV_LPN_QTY_ERR');
2992 x_return_msg := fnd_message.get;
2993 END IF;
2994 RETURN x_return;
2995 END IF;
2996
2997 ELSE
2998 x_return :='F';
2999 FND_MESSAGE.set_name('INV', 'INV-INVALID_QUANTITY_TYPE');
3000 FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
3001 x_return_msg := fnd_message.get;
3002 RETURN x_return;
3003 END IF ;
3004
3005 -- Added for lpn reservations
3006 l_updt_qoh := - l_qoh;
3007
3008 --mdebug (' l_updt_qoh ' || l_updt_qoh|| ' l qoh ' || l_qoh);
3009
3010 /*need to update qty tree */
3011 inv_quantity_tree_pub.update_quantities
3012 ( p_api_version_number => 1.0
3013 , p_init_msg_lst => fnd_api.g_false
3014 , x_return_status => l_return_status
3015 , x_msg_count => l_msg_count
3016 , x_msg_data => l_msg_data
3017 , p_organization_id => p_organization_id
3018 , p_inventory_item_id => l_item_id
3019 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
3020 , p_is_revision_control => l_is_revision_control
3021 , p_is_lot_control => l_is_lot_control
3022 , p_is_serial_control => l_is_serial_control
3023 , p_demand_source_type_id => p_source_type_id
3024 , p_revision => l_revision
3025 , p_lot_number => l_lot_number
3026 , p_subinventory_code => l_subinventory_code
3027 , p_locator_id => l_locator_id
3028 , p_primary_quantity => l_updt_qoh
3029 , p_quantity_type => inv_quantity_tree_pvt.g_qoh
3030 , x_qoh => l_qoh
3031 , x_rqoh => l_rqoh
3032 , x_qr => l_qr
3033 , x_qs => l_qs
3034 , x_att => l_att
3035 , x_atr => l_atr
3036 , p_transfer_subinventory_code => p_to_subinventory_code
3037 , p_lpn_id => l_parent_lpn_id --added for lpn reservation
3038 );
3039
3040 END LOOP ;
3041 CLOSE l_item_cursor;
3042 RETURN x_return;
3043
3044 EXCEPTION
3045 WHEN NO_DATA_FOUND THEN
3046 x_return_msg := 'NO_DATA_FOUND';
3047 x_return :='F';
3048 RETURN x_return;
3049
3050
3051 END check_lpn_quantity;
3052
3053
3054
3055 -- Gets the immediate quantity of an item in an LPN.
3056 FUNCTION get_immediate_lpn_item_qty(p_lpn_id IN NUMBER,
3057 p_organization_id IN NUMBER,
3058 p_source_type_id IN NUMBER,
3059 p_inventory_item_id IN NUMBER,
3060 p_revision IN VARCHAR2,
3061 p_locator_id IN NUMBER,
3062 p_subinventory_code IN VARCHAR2,
3063 p_lot_number IN VARCHAR2,
3064 p_is_revision_control IN VARCHAR2,
3065 p_is_serial_control IN VARCHAR2,
3066 p_is_lot_control IN VARCHAR2,
3067 x_transactable_qty OUT NOCOPY NUMBER,
3068 x_qoh OUT NOCOPY NUMBER,
3069 x_lpn_onhand OUT NOCOPY NUMBER,
3070 x_return_msg OUT NOCOPY VARCHAR2)
3071 RETURN VARCHAR2
3072 IS
3073 l_msg_count VARCHAR2(100);
3074 l_msg_data VARCHAR2(1000);
3075 l_rqoh NUMBER;
3076 l_qr NUMBER;
3077 l_qs NUMBER;
3078 l_atr NUMBER;
3079 l_att NUMBER;
3080 l_lpn_context NUMBER ;
3081 l_return_status VARCHAR2(1);
3082 x_return VARCHAR2(1);
3083 l_is_revision_control BOOLEAN := FALSE;
3084 l_is_serial_control BOOLEAN := FALSE;
3085 l_is_lot_control BOOLEAN := FALSE ;
3086 l_lpn_context NUMBER;
3087 l_mod varchar2(20) := 'get_lpn_available';
3088 l_tree_mode NUMBER := INV_Quantity_Tree_PUB.g_transaction_mode;
3089
3090 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3091 BEGIN
3092
3093 x_return := 'Y';
3094 x_return_msg :='';
3095
3096 -- Clearing the quantity cache
3097 inv_quantity_tree_pub.clear_quantity_cache;
3098
3099 IF Upper(p_is_revision_control) = 'TRUE' THEN
3100 l_is_revision_control := TRUE;
3101 ELSE
3102 l_is_revision_control := FALSE;
3103 END IF;
3104
3105 IF Upper(p_is_serial_control) = 'TRUE' THEN
3106 l_is_serial_control := TRUE;
3107 ELSE
3108 l_is_serial_control := FALSE;
3109 END IF ;
3110
3111 IF Upper(p_is_lot_control) = 'TRUE' THEN
3112 l_is_lot_control := TRUE;
3113 ELSE
3114 l_is_lot_control := FALSE;
3115 END IF ;
3116
3117 IF (p_inventory_item_id IS NULL) THEN
3118 x_return := 'N';
3119 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
3120 x_return_msg := fnd_message.get;
3121 RETURN x_return;
3122 END IF ;
3123
3124 IF (p_lpn_id is NOT NULL AND p_lpn_id <> 0)
3125 THEN
3126 l_tree_mode := INV_Quantity_Tree_PUB.g_transaction_mode; --lpn reservation
3127 /* comment out for lpn reservation ??? how can we get immediate lpn item quantity by calling
3128 quantity tree API using new tree mode ?????
3129 SELECT SUM(quantity)
3130 INTO x_lpn_onhand
3131 FROM wms_lpn_contents
3132 WHERE parent_lpn_id = p_lpn_id
3133 AND inventory_item_id = p_inventory_item_id
3134 AND organization_id = p_organization_id
3135 AND (p_lot_number IS NULL OR lot_number = p_lot_number)
3136 AND (p_revision IS NULL OR revision = p_revision);
3137
3138 IF x_lpn_onhand is NULL
3139 THEN
3140 x_return := 'N';
3141 fnd_message.set_name('INV', 'INV_LPN_INVALID');
3142 x_return_msg := fnd_message.get;
3143 RETURN x_return;
3144 END IF;*/
3145 ELSE
3146 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
3147 END IF;
3148
3149 /* Bug 4108760, Commenting the changes done for bug 3246658.
3150 * Issue in bug 3246658 is solved by bug 3295705.
3151 * Passing p_lot_expiration_date as sysdate restrict only non
3152 * expired lots for lot transactions. As per functionality,
3153 * user should be able to perform lot transactions in expired lots
3154 */
3155 /*Bug# 3246658: pass p_lot_expiration_date as sysdate so that for lot controlled items
3156 *with user defined shelf life the build_query does not return FALSE
3157 */
3158 inv_quantity_tree_pub.query_quantities
3159 (p_api_version_number => 1.0
3160 , p_init_msg_lst => fnd_api.g_false
3161 , x_return_status => l_return_status
3162 , x_msg_count => l_msg_count
3163 , x_msg_data => l_msg_data
3164 , p_organization_id => p_organization_id
3165 , p_inventory_item_id => p_inventory_item_id
3166 , p_tree_mode => l_tree_mode
3167 , p_is_revision_control => l_is_revision_control
3168 , p_is_lot_control => l_is_lot_control
3169 , p_is_serial_control => l_is_serial_control
3170 , p_demand_source_type_id => p_source_type_id
3171 , p_lot_expiration_date => null --sysdate --bug3246658 --bug4108760
3172 , p_revision => p_revision
3173 , p_lot_number => p_lot_number
3174 , p_subinventory_code => p_subinventory_code
3175 , p_locator_id => p_locator_id
3176 , x_qoh => x_qoh
3177 , x_rqoh => l_rqoh
3178 , x_qr => l_qr
3179 , x_qs => l_qs
3180 , x_att => x_transactable_qty
3181 , x_atr => l_atr
3182 , p_lpn_id => p_lpn_id -- lpn reservation
3183 );
3184
3185
3186 IF (l_return_status = 'S') THEN
3187 IF (p_lpn_id IS NOT NULL AND p_lpn_id <> 0) THEN
3188 x_lpn_onhand := x_qoh;
3189 /*IF (l_att >= x_lpn_onhand) THEN
3190 x_transactable_qty := x_lpn_onhand;
3191 x_return := 'Y';
3192 x_return_msg :='SUCCESS';
3193 RETURN x_return;
3194 ELSE
3195 x_transactable_qty :=l_att;
3196 x_return := 'Y';
3197 x_return_msg :='SUCCESS';
3198 RETURN x_return;
3199 END IF ;*/
3200 ELSE
3201 --x_transactable_qty :=l_att;
3202 x_return := 'Y';
3203 x_return_msg :='SUCCESS';
3204 RETURN x_return;
3205 END IF ;
3206 ELSE
3207 x_return :='F';
3208 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
3209 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
3210 x_return_msg := fnd_message.get;
3211 RETURN x_return;
3212 END IF ;
3213
3214 RETURN x_return;
3215
3216 END get_immediate_lpn_item_qty;
3217
3218
3219 -- Gets the immediate quantity of an item in an LPN.
3220 -- Overloaded function with the following new output parameters (INVCONV):
3221 -- x_transactable_sec_qty OUT NOCOPY NUMBER,
3222 -- x_sqoh OUT NOCOPY NUMBER,
3223 --- x_lpn_sec_onhand OUT NOCOPY NUMBER,
3224
3225 FUNCTION get_immediate_lpn_item_qty(p_lpn_id IN NUMBER,
3226 p_organization_id IN NUMBER,
3227 p_source_type_id IN NUMBER,
3228 p_inventory_item_id IN NUMBER,
3229 p_revision IN VARCHAR2,
3230 p_locator_id IN NUMBER,
3231 p_subinventory_code IN VARCHAR2,
3232 p_lot_number IN VARCHAR2,
3233 p_is_revision_control IN VARCHAR2,
3234 p_is_serial_control IN VARCHAR2,
3235 p_is_lot_control IN VARCHAR2,
3236 x_transactable_qty OUT NOCOPY NUMBER,
3237 x_qoh OUT NOCOPY NUMBER,
3238 x_lpn_onhand OUT NOCOPY NUMBER,
3239 x_transactable_sec_qty OUT NOCOPY NUMBER,
3240 x_sqoh OUT NOCOPY NUMBER,
3241 x_lpn_sec_onhand OUT NOCOPY NUMBER,
3242 x_return_msg OUT NOCOPY VARCHAR2)
3243
3244 RETURN VARCHAR2
3245 IS
3246 l_msg_count VARCHAR2(100);
3247 l_msg_data VARCHAR2(1000);
3248
3249 l_srqoh NUMBER;
3250 l_sqr NUMBER;
3251 l_sqs NUMBER;
3252 l_satr NUMBER;
3253
3254 l_rqoh NUMBER;
3255 l_qr NUMBER;
3256 l_qs NUMBER;
3257 l_atr NUMBER;
3258
3259 l_att NUMBER;
3260 l_lpn_context NUMBER ;
3261 l_return_status VARCHAR2(1);
3262 x_return VARCHAR2(1);
3263 l_is_revision_control BOOLEAN := FALSE;
3264 l_is_serial_control BOOLEAN := FALSE;
3265 l_is_lot_control BOOLEAN := FALSE ;
3266 l_lpn_context NUMBER;
3267 l_mod varchar2(20) := 'get_lpn_available';
3268 l_tree_mode NUMBER := INV_Quantity_Tree_PUB.g_transaction_mode;
3269
3270 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3271 BEGIN
3272
3273 x_return := 'Y';
3274 x_return_msg :='';
3275
3276 -- Clearing the quantity cache
3277 inv_quantity_tree_pub.clear_quantity_cache;
3278
3279 IF Upper(p_is_revision_control) = 'TRUE' THEN
3280 l_is_revision_control := TRUE;
3281 ELSE
3282 l_is_revision_control := FALSE;
3283 END IF;
3284
3285 IF Upper(p_is_serial_control) = 'TRUE' THEN
3286 l_is_serial_control := TRUE;
3287 ELSE
3288 l_is_serial_control := FALSE;
3289 END IF ;
3290
3291 IF Upper(p_is_lot_control) = 'TRUE' THEN
3292 l_is_lot_control := TRUE;
3293 ELSE
3294 l_is_lot_control := FALSE;
3295 END IF ;
3296
3297 IF (p_inventory_item_id IS NULL) THEN
3298 x_return := 'N';
3299 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
3300 x_return_msg := fnd_message.get;
3301 RETURN x_return;
3302 END IF ;
3303
3304 IF (p_lpn_id is NOT NULL AND p_lpn_id <> 0)
3305 THEN
3306 l_tree_mode := INV_Quantity_Tree_PUB.g_transaction_mode; --lpn reservation
3307 /* comment out for lpn reservation ??? how can we get immediate lpn item quantity by calling
3308 quantity tree API using new tree mode ?????
3309 SELECT SUM(quantity)
3310 INTO x_lpn_onhand
3311 FROM wms_lpn_contents
3312 WHERE parent_lpn_id = p_lpn_id
3313 AND inventory_item_id = p_inventory_item_id
3314 AND organization_id = p_organization_id
3315 AND (p_lot_number IS NULL OR lot_number = p_lot_number)
3316 AND (p_revision IS NULL OR revision = p_revision);
3317
3318 IF x_lpn_onhand is NULL
3319 THEN
3320 x_return := 'N';
3321 fnd_message.set_name('INV', 'INV_LPN_INVALID');
3322 x_return_msg := fnd_message.get;
3323 RETURN x_return;
3324 END IF;*/
3325 ELSE
3326 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
3327 END IF;
3328
3329 /* Bug 4108760, Commenting the changes done for bug 3246658.
3330 * Issue in bug 3246658 is solved by bug 3295705.
3331 * Passing p_lot_expiration_date as sysdate restrict only non
3332 * expired lots for lot transactions. As per functionality,
3333 * user should be able to perform lot transactions in expired lots
3334 */
3335 /*Bug# 3246658: pass p_lot_expiration_date as sysdate so that for lot controlled items
3336 *with user defined shelf life the build_query does not return FALSE
3337 */
3338 inv_quantity_tree_pub.query_quantities
3339 (p_api_version_number => 1.0
3340 , p_init_msg_lst => fnd_api.g_false
3341 , x_return_status => l_return_status
3342 , x_msg_count => l_msg_count
3343 , x_msg_data => l_msg_data
3344 , p_organization_id => p_organization_id
3345 , p_inventory_item_id => p_inventory_item_id
3346 , p_tree_mode => l_tree_mode
3347 , p_is_revision_control => l_is_revision_control
3348 , p_is_lot_control => l_is_lot_control
3349 , p_is_serial_control => l_is_serial_control
3350 , p_grade_code => NULL
3351 , p_demand_source_type_id => p_source_type_id
3352 , p_lot_expiration_date => null --sysdate --bug3246658 --bug 4108760
3353 , p_revision => p_revision
3354 , p_lot_number => p_lot_number
3355 , p_subinventory_code => p_subinventory_code
3356 , p_locator_id => p_locator_id
3357 , x_qoh => x_qoh
3358 , x_rqoh => l_rqoh
3359 , x_qr => l_qr
3360 , x_qs => l_qs
3361 , x_att => x_transactable_qty
3362 , x_atr => l_atr
3363 , x_sqoh => x_sqoh
3364 , x_srqoh => l_srqoh
3365 , x_sqr => l_sqr
3366 , x_sqs => l_sqs
3367 , x_satt => x_transactable_sec_qty
3368 , x_satr => l_satr
3369 , p_lpn_id => p_lpn_id -- lpn reservation
3370 );
3371
3372
3373 IF (l_return_status = 'S') THEN
3374 IF (p_lpn_id IS NOT NULL AND p_lpn_id <> 0) THEN
3375 x_lpn_onhand := x_qoh;
3376 -- INVCONV start
3377 x_lpn_sec_onhand := x_sqoh;
3378 -- INVCONV end
3379 /*IF (l_att >= x_lpn_onhand) THEN
3380 x_transactable_qty := x_lpn_onhand;
3381 x_return := 'Y';
3382 x_return_msg :='SUCCESS';
3383 RETURN x_return;
3384 ELSE
3385 x_transactable_qty :=l_att;
3386 x_return := 'Y';
3387 x_return_msg :='SUCCESS';
3388 RETURN x_return;
3389 END IF ;*/
3390 ELSE
3391 --x_transactable_qty :=l_att;
3392 x_return := 'Y';
3393 x_return_msg :='SUCCESS';
3394 RETURN x_return;
3395 END IF ;
3396 ELSE
3397 x_return :='F';
3398 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
3399 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
3400 x_return_msg := fnd_message.get;
3401 RETURN x_return;
3402 END IF ;
3403
3404 RETURN x_return;
3405
3406 END get_immediate_lpn_item_qty;
3407
3408 FUNCTION get_unpacksplit_lpn_item_qty(p_lpn_id IN NUMBER,
3409 p_organization_id IN NUMBER,
3410 p_source_type_id IN NUMBER,
3411 p_inventory_item_id IN NUMBER,
3412 p_revision IN VARCHAR2,
3413 p_locator_id IN NUMBER,
3414 p_subinventory_code IN VARCHAR2,
3415 p_lot_number IN VARCHAR2,
3416 p_is_revision_control IN VARCHAR2,
3417 p_is_serial_control IN VARCHAR2,
3418 p_is_lot_control IN VARCHAR2,
3419 p_transfer_subinventory_code IN VARCHAR2,
3420 p_transfer_locator_id IN NUMBER,
3421 x_transactable_qty OUT NOCOPY NUMBER,
3422 x_qoh OUT NOCOPY NUMBER,
3423 x_lpn_onhand OUT NOCOPY NUMBER,
3424 x_return_msg OUT NOCOPY VARCHAR2)
3425 RETURN VARCHAR2
3426 IS
3427 l_msg_count VARCHAR2(100);
3428 l_msg_data VARCHAR2(1000);
3429 l_rqoh NUMBER;
3430 l_qr NUMBER;
3431 l_qs NUMBER;
3432 l_atr NUMBER;
3433 l_att NUMBER;
3434 l_lpn_context NUMBER ;
3435 l_return_status VARCHAR2(1);
3436 x_return VARCHAR2(1);
3437 l_is_revision_control BOOLEAN := FALSE;
3438 l_is_serial_control BOOLEAN := FALSE;
3439 l_is_lot_control BOOLEAN := FALSE ;
3440 l_lpn_context NUMBER;
3441 l_mod varchar2(20) := 'get_lpn_available';
3442 l_tree_mode NUMBER := INV_Quantity_Tree_PUB.g_transaction_mode;
3443
3444 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3445 BEGIN
3446
3447 x_return := 'Y';
3448 x_return_msg :='';
3449
3450 -- Clearing the quantity cache
3451 inv_quantity_tree_pub.clear_quantity_cache;
3452
3453 IF Upper(p_is_revision_control) = 'TRUE' THEN
3454 l_is_revision_control := TRUE;
3455 ELSE
3456 l_is_revision_control := FALSE;
3457 END IF;
3458
3459 IF Upper(p_is_serial_control) = 'TRUE' THEN
3460 l_is_serial_control := TRUE;
3461 ELSE
3462 l_is_serial_control := FALSE;
3463 END IF ;
3464
3465 IF Upper(p_is_lot_control) = 'TRUE' THEN
3466 l_is_lot_control := TRUE;
3467 ELSE
3468 l_is_lot_control := FALSE;
3469 END IF ;
3470
3471 IF (p_inventory_item_id IS NULL) THEN
3472 x_return := 'N';
3473 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
3474 x_return_msg := fnd_message.get;
3475 RETURN x_return;
3476 END IF ;
3477
3478 IF (p_lpn_id is NOT NULL AND p_lpn_id <> 0)
3479 THEN
3480 l_tree_mode := INV_Quantity_Tree_PUB.g_transaction_mode; --lpn reservation
3481 /* comment out for lpn reservation ??? how can we get immediate lpn item quantity by calling
3482 quantity tree API using new tree mode ?????
3483 SELECT SUM(quantity)
3484 INTO x_lpn_onhand
3485 FROM wms_lpn_contents
3486 WHERE parent_lpn_id = p_lpn_id
3487 AND inventory_item_id = p_inventory_item_id
3488 AND organization_id = p_organization_id
3489 AND (p_lot_number IS NULL OR lot_number = p_lot_number)
3490 AND (p_revision IS NULL OR revision = p_revision);
3491
3492 IF x_lpn_onhand is NULL
3493 THEN
3494 x_return := 'N';
3495 fnd_message.set_name('INV', 'INV_LPN_INVALID');
3496 x_return_msg := fnd_message.get;
3497 RETURN x_return;
3498 END IF;*/
3499 ELSE
3500 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
3501 END IF;
3502
3503 inv_quantity_tree_pub.query_quantities
3504 (p_api_version_number => 1.0
3505 , p_init_msg_lst => fnd_api.g_false
3506 , x_return_status => l_return_status
3507 , x_msg_count => l_msg_count
3508 , x_msg_data => l_msg_data
3509 , p_organization_id => p_organization_id
3510 , p_inventory_item_id => p_inventory_item_id
3511 , p_tree_mode => l_tree_mode
3512 , p_is_revision_control => l_is_revision_control
3513 , p_is_lot_control => l_is_lot_control
3514 , p_is_serial_control => l_is_serial_control
3515 , p_demand_source_type_id => p_source_type_id
3516 , p_revision => p_revision
3517 , p_lot_number => p_lot_number
3518 , p_subinventory_code => p_subinventory_code
3519 , p_locator_id => p_locator_id
3520 , x_qoh => x_qoh
3521 , x_rqoh => l_rqoh
3522 , x_qr => l_qr
3523 , x_qs => l_qs
3524 , x_att => x_transactable_qty
3525 , x_atr => l_atr
3526 , p_transfer_subinventory_code => p_transfer_subinventory_code
3527 , p_lpn_id => p_lpn_id -- lpn reservation
3528 , p_transfer_locator_id => p_transfer_locator_id
3529 );
3530
3531
3532 IF (l_return_status = 'S') THEN
3533 IF (p_lpn_id IS NOT NULL AND p_lpn_id <> 0) THEN
3534 x_lpn_onhand := x_qoh;
3535 /*IF (l_att >= x_lpn_onhand) THEN
3536 x_transactable_qty := x_lpn_onhand;
3537 x_return := 'Y';
3538 x_return_msg :='SUCCESS';
3539 RETURN x_return;
3540 ELSE
3541 x_transactable_qty :=l_att;
3542 x_return := 'Y';
3543 x_return_msg :='SUCCESS';
3544 RETURN x_return;
3545 END IF ;*/
3546 ELSE
3547 --x_transactable_qty :=l_att;
3548 x_return := 'Y';
3549 x_return_msg :='SUCCESS';
3550 RETURN x_return;
3551 END IF ;
3552 ELSE
3553 x_return :='F';
3554 FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
3555 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
3556 x_return_msg := fnd_message.get;
3557 RETURN x_return;
3558 END IF ;
3559
3560 RETURN x_return;
3561
3562 END get_unpacksplit_lpn_item_qty;
3563
3564
3565
3566 FUNCTION CHECK_SERIAL_UNPACKSPLIT( p_lpn_id IN NUMBER
3567 ,p_org_id IN NUMBER
3568 ,p_item_id IN NUMBER
3569 ,p_rev IN VARCHAR2
3570 ,p_lot IN VARCHAR2
3571 ,p_serial IN VARCHAR2)
3572 RETURN VARCHAR2
3573 IS
3574 x_return VARCHAR2(1);
3575 l_transaction_temp_id number := 0;
3576 l_allocated_lpn number := 0;
3577 l_serial_exist number := 0;
3578 cursor c_mmtt(p_org_id number,
3579 p_item_id number,
3580 p_rev varchar2) is
3581 select transaction_temp_id, allocated_lpn_id
3582 from mtl_material_transactions_temp
3583 where organization_id = p_org_id
3584 and inventory_item_id = p_item_id
3585 and nvl(revision,'@@@') = nvl(p_rev, nvl(revision,'@@@'));
3586 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3587 BEGIN
3588 x_return := 'Y';
3589 IF (l_debug = 1) THEN
3590 mdebug('check_serial_unpacksplit: lpn_id'||p_lpn_id||' orgid:'||p_org_id||' itemid:'||p_item_id||' rev:'||p_rev||' lot:'||p_lot||' serial:'||p_serial);
3591 END IF;
3592 open c_mmtt(p_org_id,p_item_id, p_rev);
3593 Loop
3594 FETCH c_mmtt INTO l_transaction_temp_id, l_allocated_lpn;
3595 EXIT WHEN c_mmtt%NOTFOUND;
3596
3597 if p_lot is null then
3598 begin
3599 select 1
3600 into l_serial_exist
3601 from mtl_serial_numbers_temp
3602 where transaction_temp_id = l_transaction_temp_id
3603 and p_serial between fm_serial_number and nvl(to_serial_number, fm_serial_number);
3604 exception
3605 when others then
3606 l_serial_exist := 0;
3607 end;
3608 else
3609 begin
3610 select 1
3611 into l_serial_exist
3612 from mtl_transaction_lots_temp mtlt,
3613 mtl_serial_numbers_temp msnt
3614 where mtlt.transaction_temp_id = l_transaction_temp_id
3615 and mtlt.lot_number = p_lot
3616 and msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
3617 and p_serial between msnt.fm_serial_number and nvl(msnt.to_serial_number, msnt.fm_serial_number);
3618 exception
3619 when others then
3620 l_serial_exist := 0;
3621 end;
3622
3623 end if;
3624 IF (l_debug = 1) THEN
3625 mdebug('check_serial_unpacksplit: l_transaction_temp_id:'||l_transaction_temp_id||' l_allocated_lpn:'||l_allocated_lpn);
3626 END IF;
3627 if (l_serial_exist > 0) and (l_allocated_lpn is not null) then
3628 x_return := 'N';
3629 return x_return;
3630 end if;
3631 End loop;
3632 close c_mmtt;
3633 return x_return;
3634 END CHECK_SERIAL_UNPACKSPLIT;
3635
3636
3637
3638 --"Returns"
3639 PROCEDURE GET_RETURN_LOT_QUANTITIES(
3640 x_lot_qty OUT NOCOPY t_genref
3641 , p_org_id IN NUMBER
3642 , p_lpn_id IN NUMBER
3643 , p_item_id IN NUMBER
3644 , p_revision IN VARCHAR2
3645 , p_uom IN VARCHAR2)
3646 IS
3647 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3648
3649 BEGIN
3650 --BugFix 3701796 SQL sum function changed to primary quantity
3651 OPEN x_lot_qty FOR
3652 select lot_number,
3653 SUM(inv_decimals_pub.get_primary_quantity(p_org_id,inventory_item_id,uom_code,Nvl(quantity,0))) primary_quantity
3654 from wms_lpn_contents
3655 where inventory_item_id = p_item_id
3656 and parent_lpn_id = p_lpn_id
3657 and ((revision = p_revision and p_revision is not null) or
3658 (p_revision is null and revision is null))
3659 and source_name in ('RETURN TO VENDOR',
3660 'RETURN TO CUSTOMER',
3661 'RETURN TO RECEIVING')
3662 and organization_id = p_org_id
3663 group by lot_number;
3664
3665 END GET_RETURN_LOT_QUANTITIES;
3666
3667 PROCEDURE GET_RETURN_TOTAL_QTY(
3668 x_tot_qty OUT NOCOPY t_genref
3669 , p_org_id IN NUMBER
3670 , p_lpn_id IN NUMBER
3671 , p_item_id IN NUMBER
3672 , p_revision IN VARCHAR2
3673 , p_uom IN VARCHAR2)
3674 IS
3675 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3676 BEGIN
3677 OPEN x_tot_qty FOR
3678 select sum(quantity)
3679 from wms_lpn_contents
3680 where inventory_item_id = p_item_id
3681 and parent_lpn_id = p_lpn_id
3682 and ((revision = p_revision and p_revision is not null) or
3683 (p_revision is null and revision is null))
3684 and source_name in ('RETURN TO VENDOR',
3685 'RETURN TO CUSTOMER',
3686 'RETURN TO RECEIVING')
3687 and organization_id = p_org_id;
3688 END GET_RETURN_TOTAL_QTY;
3689
3690
3691 -----------------------------------------------------------------------------
3692 --Bug 2765395
3693 PROCEDURE get_valid_to_locs(
3694 x_locators OUT NOCOPY t_genref
3695 , p_transaction_action_id IN NUMBER
3696 , p_to_organization_id IN NUMBER
3697 , p_organization_id IN NUMBER
3698 , p_subinventory_code IN VARCHAR2
3699 , p_restrict_locators_code IN NUMBER
3700 , p_inventory_item_id IN NUMBER
3701 , p_concatenated_segments IN VARCHAR2
3702 , p_transaction_type_id IN NUMBER
3703 , p_wms_installed IN VARCHAR2
3704 ) IS
3705 l_org NUMBER;
3706 l_restrict_locators_code NUMBER;
3707 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3708 BEGIN
3709 IF (l_debug = 1) THEN
3710 inv_log_util.trace('get_valid_to_locs Starting ', 'process_serial_subxfr');
3711 END IF;
3712 IF p_transaction_action_id IN (3, 21) THEN
3713 l_org := p_to_organization_id;
3714 SELECT restrict_locators_code
3715 INTO l_restrict_locators_code
3716 FROM mtl_system_items
3717 WHERE inventory_item_id = p_inventory_item_id
3718 AND organization_id = l_org;
3719 ELSE
3720 l_org := p_organization_id;
3721 l_restrict_locators_code := p_restrict_locators_code;
3722 END IF;
3723 IF (l_debug = 1) THEN
3724 inv_log_util.trace('get_valid_to_locs ::Fetch Locators ', 'process_serial_subxfr');
3725 END IF;
3726 IF l_restrict_locators_code = 1 THEN --Locators restricted to predefined list
3727 OPEN x_locators FOR
3728 SELECT a.inventory_location_id
3729 , a.concatenated_segments
3730 , a.description
3731 FROM mtl_item_locations_kfv a, mtl_secondary_locators b
3732 WHERE b.organization_id = l_org
3733 AND b.inventory_item_id = p_inventory_item_id
3734 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3735 AND b.subinventory_code = p_subinventory_code
3736 AND a.inventory_location_id = b.secondary_locator
3737 AND a.concatenated_segments LIKE (p_concatenated_segments||'%')
3738 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
3739 ORDER BY a.concatenated_segments;
3740 ELSE --Locators not restricted
3741 OPEN x_locators FOR
3742 SELECT inventory_location_id
3743 , concatenated_segments
3744 , description
3745 FROM mtl_item_locations_kfv
3746 WHERE organization_id = l_org
3747 AND subinventory_code = p_subinventory_code
3748 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3749 AND concatenated_segments LIKE (p_concatenated_segments||'%')
3750 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
3751 ORDER BY concatenated_segments;
3752 END IF;
3753 END get_valid_to_locs;
3754
3755
3756 PROCEDURE get_valid_prj_to_locs(
3757 x_locators OUT NOCOPY t_genref
3758 , p_transaction_action_id IN NUMBER
3759 , p_to_organization_id IN NUMBER
3760 , p_organization_id IN NUMBER
3761 , p_subinventory_code IN VARCHAR2
3762 , p_restrict_locators_code IN NUMBER
3763 , p_inventory_item_id IN NUMBER
3764 , p_concatenated_segments IN VARCHAR2
3765 , p_transaction_type_id IN NUMBER
3766 , p_wms_installed IN VARCHAR2
3767 , p_project_id IN NUMBER
3768 , p_task_id IN NUMBER
3769 ) IS
3770 l_org NUMBER;
3771 l_restrict_locators_code NUMBER;
3772 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3773 BEGIN
3774 IF (l_debug = 1) THEN
3775 inv_log_util.trace('get_valid_prj_to_locs ::Starting ', 'process_serial_subxfr');
3776 END IF;
3777 IF p_transaction_action_id IN (3, 21) THEN
3778 l_org := p_to_organization_id;
3779
3780 SELECT restrict_locators_code
3781 INTO l_restrict_locators_code
3782 FROM mtl_system_items
3783 WHERE inventory_item_id = p_inventory_item_id
3784 AND organization_id = l_org;
3785 ELSE
3786 l_org := p_organization_id;
3787 l_restrict_locators_code := p_restrict_locators_code;
3788 END IF;
3789 IF (l_debug = 1) THEN
3790 inv_log_util.trace('get_valid_prj_to_locs ::Fetching Locators ', 'process_serial_subxfr');
3791 END IF;
3792 IF l_restrict_locators_code= 1 THEN --Locators restricted to predefined list
3793 OPEN x_locators FOR
3794 SELECT a.inventory_location_id
3795 , inv_project.get_locsegs(a.inventory_location_id,l_org)
3796 , NVL(a.description, -1)
3797 FROM mtl_item_locations a, mtl_secondary_locators b
3798 WHERE b.organization_id = l_org
3799 AND b.inventory_item_id = p_inventory_item_id
3800 AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3801 AND b.subinventory_code = p_subinventory_code
3802 AND a.inventory_location_id = b.secondary_locator
3803 AND inv_project.get_locsegs(a.inventory_location_id, l_org) LIKE (p_concatenated_segments||'%')
3804 AND NVL(a.project_id, -1) = NVL(p_project_id, -1)
3805 AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
3806 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
3807 ORDER BY 2;
3808 ELSE --Locators not restricted
3809 OPEN x_locators FOR
3810 SELECT inventory_location_id
3811 , inv_project.get_locsegs(inventory_location_id, l_org)
3812 , description
3813 FROM mtl_item_locations
3814 WHERE organization_id = l_org
3815 AND subinventory_code = p_subinventory_code
3816 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3817 AND inv_project.get_locsegs(inventory_location_id, l_org) LIKE (p_concatenated_segments||'%')
3818 AND NVL(project_id, -1) = NVL(p_project_id, -1)
3819 AND NVL(task_id, -1) = NVL(p_task_id, -1)
3820 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, l_org, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
3821 ORDER BY 2;
3822 END IF;
3823 END get_valid_prj_to_locs;
3824 -------------------------------------------------------------------------------
3825
3826
3827
3828 --"Returns"
3829
3830 -- This procedure validates the serial number, to sub and to loc for a
3831 -- serial triggered sub transfer. It also updates the quantity tree. It
3832 -- sets the GROUP mark ID of the serial number to a non null value. It also
3833 -- inserts into MMTT, MTLT and MSNT tables for the sub transfer transaction
3834 PROCEDURE process_serial_subxfr(p_organization_id IN NUMBER,
3835 p_serial_number IN VARCHAR2,
3836 p_inventory_item_id IN NUMBER,
3837 p_inventory_item IN VARCHAR2,
3838 --I Development Bug 2634570
3839 p_project_id IN NUMBER,
3840 p_task_id IN NUMBER,
3841
3842 p_revision IN VARCHAR2,
3843 p_primary_uom_code IN VARCHAR2,
3844 p_subinventory_code IN VARCHAR2,
3845 p_locator_id IN NUMBER,
3846 p_locator IN VARCHAR2,
3847 p_to_subinventory_code IN VARCHAR2,
3848 p_to_locator IN VARCHAR2,
3849 p_to_locator_id IN NUMBER,
3850 p_reason_id IN NUMBER,
3851 p_lot_number IN VARCHAR2,
3852 p_wms_installed IN VARCHAR2,
3853 p_transaction_action_id IN NUMBER,
3854 p_transaction_type_id IN VARCHAR2,
3855 p_source_type_id IN NUMBER,
3856 p_user_id IN NUMBER,
3857 p_transaction_header_id IN NUMBER,
3858 p_restrict_sub_code IN NUMBER,
3859 p_restrict_loc_code IN NUMBER,
3860 p_from_sub_asset_inv IN NUMBER,
3861 p_serial_control_code IN NUMBER,
3862 p_process_serial IN VARCHAR2,
3863 x_serial_processed OUT NOCOPY VARCHAR2,
3864 x_transaction_header_id OUT NOCOPY NUMBER,
3865 x_return_status OUT NOCOPY VARCHAR2,
3866 x_return_msg OUT NOCOPY VARCHAR2)
3867 IS
3868 l_is_revision_control BOOLEAN;
3869 l_is_lot_control BOOLEAN;
3870
3871 l_tree_mode NUMBER := inv_quantity_tree_pub.g_transaction_mode;
3872 l_quantity_type NUMBER := inv_quantity_tree_pvt.g_qoh;
3873 l_onhand_source NUMBER := inv_quantity_tree_pvt.g_all_subs;
3874 l_qoh NUMBER;
3875 l_rqoh NUMBER;
3876 l_qr NUMBER;
3877 l_qs NUMBER;
3878 l_att NUMBER;
3879 l_atr NUMBER;
3880
3881 l_transaction_temp_id NUMBER;
3882 l_serial_transaction_temp_id NUMBER;
3883 l_proc_msg VARCHAR2(240);
3884 l_return_code NUMBER;
3885
3886 l_status_allowed VARCHAR2(1) := 'N';
3887 l_msg_count NUMBER;
3888 l_msg_data VARCHAR2(240);
3889 /** R12 Enhanced reservations project **/
3890 l_reservation_id NUMBER;
3891 l_group_mark_id NUMBER;
3892 /** End - R12 Enhanced reservations project **/
3893
3894 TYPE t_refcur IS ref CURSOR;
3895 l_ref_cur t_refcur;
3896
3897 l_to_subinventory_code VARCHAR2(10);
3898 l_locator_type NUMBER;
3899 l_to_locator_id NUMBER;
3900 l_to_locator mtl_item_locations_kfv.concatenated_segments%TYPE;
3901 l_description mtl_secondary_inventories.description%TYPE;
3902 l_asset_inventory mtl_secondary_inventories.asset_inventory%TYPE;
3903 l_lpn_controlled_flag mtl_secondary_inventories.lpn_controlled_flag%TYPE;
3904 l_enable_locator_alias mtl_secondary_inventories.enable_locator_alias%TYPE;
3905
3906 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3907 BEGIN
3908 x_serial_processed := 'NO'; -- No processing has been done
3909
3910 IF p_subinventory_code = p_to_subinventory_code
3911 AND Nvl(p_locator_id, -1) = Nvl(p_to_locator_id, -1) THEN
3912 fnd_message.set_name('INV', 'INV_NOT_SAME_LOC');
3913
3914 IF p_locator_id IS NOT NULL THEN
3915 x_serial_processed := 'TOLOC';
3916 ELSE
3917 x_serial_processed := 'TOSUB';
3918 END IF;
3919
3920 x_return_msg := fnd_message.get;
3921 RETURN;
3922 END IF;
3923
3924 -- Check if the serial number is available to be transacted for this transaction
3925 l_status_allowed := inv_material_status_grp.is_status_applicable
3926 (p_wms_installed => p_wms_installed,
3927 p_trx_status_enabled => NULL,
3928 p_trx_type_id => p_transaction_type_id,
3929 p_lot_status_enabled => NULL,
3930 p_serial_status_enabled => NULL,
3931 p_organization_id => p_organization_id,
3932 p_inventory_item_id => p_inventory_item_id,
3933 p_sub_code => p_subinventory_code,
3934 p_locator_id => p_locator_id,
3935 p_lot_number => p_lot_number,
3936 p_serial_number => p_serial_number,
3937 p_object_type => 'A');
3938
3939 IF (l_debug = 1) THEN
3940 inv_log_util.trace('Status Allowed: ' || l_status_allowed, 'process_serial_subxfr');
3941 END IF;
3942 IF l_status_allowed <> 'Y' THEN
3943 fnd_message.set_name('INV', 'INV_TRX_SER_NA_DUE_MS');
3944 fnd_message.set_token('TOKEN1', p_serial_number);
3945 fnd_message.set_token('TOKEN2', p_inventory_item);
3946 x_serial_processed := 'SERIAL';
3947 x_return_msg := fnd_message.get;
3948 RETURN;
3949 END IF;
3950
3951 inv_ui_item_sub_loc_lovs.get_to_sub(x_to_sub => l_ref_cur,
3952 p_organization_id => p_organization_id,
3953 p_inventory_item_id => p_inventory_item_id,
3954 p_from_Secondary_Name => p_subinventory_code,
3955 p_restrict_subinventories_code => p_restrict_sub_code,
3956 p_secondary_inventory_name => p_to_subinventory_code,
3957 p_from_sub_asset_inventory => p_from_sub_asset_inv,
3958 p_transaction_action_id => p_transaction_action_id,
3959 p_To_Organization_Id => p_organization_id,
3960 p_serial_number_control_code => p_serial_control_code,
3961 p_transaction_type_id => p_transaction_type_id,
3962 p_wms_installed => p_wms_installed);
3963
3964 LOOP
3965 FETCH l_ref_cur INTO
3966 l_to_subinventory_code,
3967 l_locator_type,
3968 l_description,
3969 l_asset_inventory,
3970 l_lpn_controlled_flag,
3971 l_enable_locator_alias;
3972 EXIT WHEN l_ref_cur%notfound OR l_to_subinventory_code = p_to_subinventory_code;
3973 END LOOP;
3974
3975 CLOSE l_ref_cur;
3976
3977 IF (l_debug = 1) THEN
3978 inv_log_util.trace('l_to_subinventory_code: ' || l_to_subinventory_code, 'process_serial_subxfr');
3979 END IF;
3980
3981 IF p_to_subinventory_code <> Nvl(l_to_subinventory_code, '@@@') THEN
3982 fnd_message.set_name('INV', 'INV_INVALID_SUB');
3983 x_serial_processed := 'TOSUB';
3984 x_return_msg := fnd_message.get;
3985 RETURN;
3986 END IF;
3987
3988
3989 IF p_to_locator_id IS NOT NULL THEN
3990 --I Development Bug 2634570
3991 IF p_project_id IS NOT NULL THEN
3992 -- inv_ui_item_sub_loc_lovs.GET_VALID_PRJ_TO_LOCS(x_Locators => l_ref_cur,
3993 --Bug 2765395
3994 GET_VALID_PRJ_TO_LOCS(x_Locators => l_ref_cur,
3995 p_transaction_action_id => p_transaction_action_id,
3996 p_to_organization_id => p_organization_id,
3997 p_organization_id => p_organization_id,
3998 p_subinventory_code => p_to_subinventory_code,
3999 p_restrict_locators_code => p_restrict_loc_code,
4000 p_inventory_item_id => p_inventory_item_id,
4001 p_concatenated_segments => p_to_locator,
4002 p_transaction_type_id => p_transaction_type_id,
4003 p_wms_installed => p_wms_installed,
4004 p_project_id => p_project_id,
4005 p_task_id => p_task_id );
4006 ELSE
4007 --inv_ui_item_sub_loc_lovs.get_valid_to_locs(x_Locators => l_ref_cur,
4008 --Bug 2765395
4009 GET_VALID_TO_LOCS(x_Locators => l_ref_cur,
4010 p_transaction_action_id => p_transaction_action_id,
4011 p_to_organization_id => p_organization_id,
4012 p_organization_id => p_organization_id,
4013 p_subinventory_code => p_to_subinventory_code,
4014 p_restrict_locators_code => p_restrict_loc_code,
4015 p_inventory_item_id => p_inventory_item_id,
4016 p_concatenated_segments => p_to_locator,
4017 p_transaction_type_id => p_transaction_type_id,
4018 p_wms_installed => p_wms_installed);
4019
4020
4021 LOOP
4022 FETCH l_ref_cur INTO
4023 l_to_locator_id,
4024 l_to_locator,
4025 l_description;
4026 EXIT WHEN l_ref_cur%notfound OR l_to_locator_id = p_to_locator_id;
4027 END LOOP;
4028
4029 CLOSE l_ref_cur;
4030
4031 IF (l_debug = 1) THEN
4032 inv_log_util.trace('p_to_locator: ' || p_to_locator, 'process_serial_subxfr');
4033 inv_log_util.trace('p_to_locator_id: ' || p_to_locator_id, 'process_serial_subxfr');
4034 inv_log_util.trace('l_to_locator: ' || l_to_locator, 'process_serial_subxfr');
4035 inv_log_util.trace('l_to_locator_id: ' || l_to_locator_id, 'process_serial_subxfr');
4036 END IF;
4037
4038 IF p_to_locator_id <> Nvl(l_to_locator_id, -1) THEN
4039 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
4040 x_serial_processed := 'TOLOC';
4041 x_return_msg := fnd_message.get;
4042 RETURN;
4043 END IF;
4044 END IF;
4045 -- End of the newly added loop I Development Bug 2634570
4046 END IF;
4047
4048 IF p_process_serial = 'Y' THEN
4049 IF p_lot_number IS NOT NULL THEN
4050 l_is_lot_control := TRUE;
4051 ELSE
4052 l_is_lot_control := FALSE;
4053 END IF;
4054
4055 IF p_revision IS NOT NULL THEN
4056 l_is_revision_control := TRUE;
4057 ELSE
4058 l_is_revision_control := FALSE;
4059 END IF;
4060
4061 -- Query the quantity tree for available to transact quantity
4062 inv_quantity_tree_pub.query_quantities
4063 (p_api_version_number => 1.0,
4064 p_init_msg_lst => fnd_api.g_false,
4065 x_return_status => x_return_status,
4066 x_msg_count => l_msg_count,
4067 x_msg_data => l_msg_data,
4068 p_organization_id => p_organization_id,
4069 p_inventory_item_id => p_inventory_item_id,
4070 p_tree_mode => l_tree_mode,
4071 p_is_revision_control => l_is_revision_control,
4072 p_is_lot_control => l_is_lot_control,
4073 p_is_serial_control => TRUE,
4074 p_demand_source_type_id => p_source_type_id,
4075 p_revision => p_revision,
4076 p_lot_number => p_lot_number,
4077 p_subinventory_code => p_subinventory_code,
4078 p_locator_id => p_locator_id,
4079 p_transfer_subinventory_code => p_to_subinventory_code,
4080 x_qoh => l_qoh,
4081 x_rqoh => l_rqoh,
4082 x_qr => l_qr,
4083 x_qs => l_qs,
4084 x_att => l_att,
4085 x_atr => l_atr);
4086
4087 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4088 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
4089 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
4090 x_return_msg := fnd_message.get;
4091 RETURN;
4092 END IF;
4093 IF (l_debug = 1) THEN
4094 inv_log_util.trace('ATT: ' || l_att, 'process_serial_subxfr');
4095 END IF;
4096
4097 /** R12 Enhanced reservations project **/
4098 BEGIN
4099 SELECT reservation_id, group_mark_id INTO l_reservation_id,
4100 l_group_mark_id FROM mtl_serial_numbers WHERE
4101 serial_number = p_serial_number AND inventory_item_id = p_inventory_item_id;
4102 EXCEPTION
4103 WHEN no_data_found THEN
4104 IF (l_debug = 1) THEN
4105 inv_log_util.trace('Serial is not reserved', 'process_serial_subxfr');
4106 END IF;
4107 END;
4108 /** end - R12 Enhanced reservations project **/
4109
4110 IF l_reservation_id IS NOT NULL AND l_reservation_id > 0 THEN
4111 l_att := l_att + 1; -- allow reserved serials to be processed.
4112 END IF;
4113
4114 IF l_att > 0 THEN
4115 -- Update the quantity tree so that the serial transaction is
4116 -- reflected in the available quantity
4117 inv_quantity_tree_pub.update_quantities
4118 (p_api_version_number => 1.0,
4119 p_init_msg_lst => fnd_api.g_false,
4120 x_return_status => x_return_status,
4121 x_msg_count => l_msg_count,
4122 x_msg_data => l_msg_data,
4123 p_organization_id => p_organization_id,
4124 p_inventory_item_id => p_inventory_item_id,
4125 p_tree_mode => l_tree_mode,
4126 p_is_revision_control => l_is_revision_control,
4127 p_is_lot_control => l_is_lot_control,
4128 p_is_serial_control => TRUE,
4129 p_demand_source_type_id => p_source_type_id,
4130 p_revision => p_revision,
4131 p_lot_number => p_lot_number,
4132 p_subinventory_code => p_subinventory_code,
4133 p_locator_id => p_locator_id,
4134 p_primary_quantity => -1,
4135 p_quantity_type => l_quantity_type,
4136 p_onhand_source => l_onhand_source,
4137 x_qoh => l_qoh,
4138 x_rqoh => l_rqoh,
4139 x_qr => l_qr,
4140 x_qs => l_qs,
4141 x_att => l_att,
4142 x_atr => l_atr);
4143
4144 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4145 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
4146 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
4147 x_return_msg := fnd_message.get;
4148 RETURN;
4149 END IF;
4150 IF (l_debug = 1) THEN
4151 inv_log_util.trace('ATT in source: ' || l_att, 'process_serial_subxfr');
4152 END IF;
4153
4154 inv_quantity_tree_pub.update_quantities
4155 (p_api_version_number => 1.0,
4156 p_init_msg_lst => fnd_api.g_false,
4157 x_return_status => x_return_status,
4158 x_msg_count => l_msg_count,
4159 x_msg_data => l_msg_data,
4160 p_organization_id => p_organization_id,
4161 p_inventory_item_id => p_inventory_item_id,
4162 p_tree_mode => l_tree_mode,
4163 p_is_revision_control => l_is_revision_control,
4164 p_is_lot_control => l_is_lot_control,
4165 p_is_serial_control => TRUE,
4166 p_demand_source_type_id => p_source_type_id,
4167 p_revision => p_revision,
4168 p_lot_number => p_lot_number,
4169 p_subinventory_code => p_to_subinventory_code,
4170 p_locator_id => p_to_locator_id,
4171 p_primary_quantity => 1,
4172 p_quantity_type => l_quantity_type,
4173 p_onhand_source => l_onhand_source,
4174 x_qoh => l_qoh,
4175 x_rqoh => l_rqoh,
4176 x_qr => l_qr,
4177 x_qs => l_qs,
4178 x_att => l_att,
4179 x_atr => l_atr);
4180
4181 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4182 FND_MESSAGE.set_name('INV', 'INV_ERR_CREATETREE');
4183 FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
4184 x_return_msg := fnd_message.get;
4185 RETURN;
4186 END IF;
4187
4188 IF (l_debug = 1) THEN
4189 inv_log_util.trace('ATT in dest: ' || l_att, 'process_serial_subxfr');
4190 END IF;
4191
4192 -- Update the group mark ID on the serial record so that it is not
4193 -- available in the LOV any more
4194 /** R12 Enhanced reservation project **/
4195 -- update only if is not reserved. Otherwise it would have been
4196 -- marked already
4197 IF l_group_mark_id IS NULL OR l_group_mark_id < 0 then
4198 update mtl_serial_numbers
4199 set group_mark_id = 1
4200 where inventory_item_id = p_inventory_item_id
4201 and serial_number = p_serial_number;
4202 END IF;
4203 /** End - R12 Enhanced reservation project **/
4204
4205 IF (l_debug = 1) THEN
4206 inv_log_util.trace('Updated Serial ' || p_serial_number || ' Item ID ' || p_inventory_item_id, 'process_serial_subxfr');
4207 END IF;
4208
4209 IF p_transaction_header_id IS NULL THEN
4210 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
4211 INTO x_transaction_header_id
4212 FROM dual;
4213 ELSE
4214 x_transaction_header_id := p_transaction_header_id;
4215 END IF;
4216
4217 IF (l_debug = 1) THEN
4218 inv_log_util.trace('Calling Insert MMTT', 'process_serial_subxfr');
4219 END IF;
4220
4221 -- Insert record into MMTT
4222 --I Development Bug 2634570
4223 -- Added two paramters to call the procedure insert_line_trx
4224 BEGIN
4225 l_return_code :=
4226 inv_trx_util_pub.insert_line_trx
4227 (p_trx_hdr_id => x_transaction_header_id,
4228 p_item_id => p_inventory_item_id,
4229 p_project_id => p_project_id,
4230 p_task_id => p_task_id,
4231 p_revision => p_revision,
4232 p_org_id => p_organization_id,
4233 p_trx_action_id => p_transaction_action_id,
4234 p_subinv_code => p_subinventory_code,
4235 p_tosubinv_code => p_to_subinventory_code,
4236 p_locator_id => p_locator_id,
4237 p_tolocator_id => p_to_locator_id,
4238 p_xfr_org_id => p_organization_id,
4239 p_trx_type_id => p_transaction_type_id,
4240 p_trx_src_type_id => p_source_type_id,
4241 p_trx_qty => 1,
4242 p_pri_qty => 1,
4243 p_uom => p_primary_uom_code,
4244 p_date => Sysdate,
4245 p_reason_id => p_reason_id,
4246 p_user_id => p_user_id,
4247 x_trx_tmp_id => l_transaction_temp_id,
4248 x_proc_msg => l_proc_msg);
4249 EXCEPTION
4250 WHEN OTHERS THEN
4251 IF (l_debug = 1) THEN
4252 inv_log_util.trace('SQL Error while inserting MTTT: ' || Sqlerrm, 'process_serial_subxfr');
4253 END IF;
4254 END;
4255
4256
4257 IF l_return_code = 0 THEN
4258 IF (l_debug = 1) THEN
4259 inv_log_util.trace('Inserted MMTT record', 'process_serial_subxfr');
4260 inv_log_util.trace('Temp ID = ' || l_transaction_temp_id, 'process_serial_subxfr');
4261 inv_log_util.trace('Header ID = ' || x_transaction_header_id, 'process_serial_subxfr');
4262 END IF;
4263 ELSE
4264 x_return_status := fnd_api.g_ret_sts_unexp_error;
4265 IF (l_debug = 1) THEN
4266 inv_log_util.trace(l_proc_msg, 'process_serial_subxfr');
4267 END IF;
4268 RETURN;
4269 END IF;
4270
4271 -- Insert record into MTLT
4272 IF p_lot_number IS NOT NULL THEN
4273 l_return_code :=
4274 inv_trx_util_pub.insert_lot_trx
4275 (p_trx_tmp_id => l_transaction_temp_id,
4276 p_user_id => p_user_id,
4277 p_lot_number => p_lot_number,
4278 p_trx_qty => 1,
4279 p_pri_qty => 1,
4280 x_ser_trx_id => l_serial_transaction_temp_id,
4281 x_proc_msg => l_proc_msg);
4282
4283 IF l_return_code = 0 THEN
4284 l_transaction_temp_id := l_serial_transaction_temp_id;
4285 IF (l_debug = 1) THEN
4286 inv_log_util.trace('Inserted MTLT record', 'process_serial_subxfr');
4287 inv_log_util.trace('Serial Temp ID = ' || l_serial_transaction_temp_id, 'process_serial_subxfr');
4288 inv_log_util.trace('Serial Temp ID = ' || l_transaction_temp_id, 'process_serial_subxfr');
4289 END IF;
4290 ELSE
4291 x_return_status := fnd_api.g_ret_sts_unexp_error;
4292 IF (l_debug = 1) THEN
4293 inv_log_util.trace(l_proc_msg, 'process_serial_subxfr');
4294 END IF;
4295 RETURN;
4296 END IF;
4297 --Bug 2779646
4298 END IF;
4299
4300 -- Insert record into MSNT
4301 l_return_code :=
4302 inv_trx_util_pub.insert_ser_trx
4303 (p_trx_tmp_id => l_transaction_temp_id,
4304 p_user_id => p_user_id,
4305 p_fm_ser_num => p_serial_number,
4306 p_to_ser_num => p_serial_number,
4307 x_proc_msg => l_proc_msg);
4308
4309 IF l_return_code = 0 THEN
4310 IF (l_debug = 1) THEN
4311 inv_log_util.trace('Inserted MSNT record', 'process_serial_subxfr');
4312 inv_log_util.trace('Serial Temp ID = ' || l_transaction_temp_id, 'process_serial_subxfr');
4313 END IF;
4314 ELSE
4315 x_return_status := fnd_api.g_ret_sts_unexp_error;
4316 IF (l_debug = 1) THEN
4317 inv_log_util.trace(l_proc_msg, 'process_serial_subxfr');
4318 END IF;
4319 RETURN;
4320 END IF;
4321
4322 x_serial_processed := 'YES';
4323
4324 ELSE -- att < 0
4325 FND_MESSAGE.set_name('INV', 'INV_SERIAL_EXCEED_AVAILABLE');
4326 x_return_msg := fnd_message.get;
4327 RETURN;
4328 END IF;
4329
4330 END IF;
4331 END process_serial_subxfr;
4332
4333
4334
4335
4336
4337 PROCEDURE check_loose_and_packed_qty
4338 (p_api_version_number IN NUMBER
4339 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
4340 , x_return_status OUT NOCOPY VARCHAR2
4341 , x_msg_count OUT NOCOPY NUMBER
4342 , x_msg_data OUT NOCOPY VARCHAR2
4343 , p_organization_id IN NUMBER
4344 , p_inventory_item_id IN NUMBER
4345 , p_is_revision_control IN VARCHAR2
4346 , p_is_lot_control IN VARCHAR2
4347 , p_is_serial_control IN VARCHAR2
4348 , p_revision IN VARCHAR2
4349 , p_lot_number IN VARCHAR2
4350 , p_transaction_quantity IN NUMBER
4351 , p_transaction_uom IN VARCHAR2
4352 , p_subinventory_code IN VARCHAR2
4353 , p_locator_id IN NUMBER
4354 , p_transaction_temp_id IN NUMBER
4355 , p_ok_to_process OUT NOCOPY VARCHAR2
4356 , p_transfer_subinventory IN VARCHAR2
4357 )
4358 IS
4359 l_att NUMBER;
4360 l_qoh NUMBER;
4361 l_rqoh NUMBER;
4362 l_qr NUMBER;
4363 l_qs NUMBER;
4364 l_atr NUMBER;
4365 l_lot_exp_dt DATE;
4366 l_moq NUMBER;
4367 l_avail_qty NUMBER;
4368 l_uom_rate NUMBER;
4369 l_txn_qty NUMBER;
4370
4371 l_ok_to_process VARCHAR2(5);
4372
4373 l_is_revision_control BOOLEAN := FALSE;
4374 l_is_lot_control BOOLEAN := FALSE;
4375 l_is_serial_control BOOLEAN := FALSE;
4376
4377 l_cost_group_id mtl_material_transactions_temp.cost_group_id%type;
4378 l_primary_uom_code mtl_material_transactions_temp.item_primary_uom_code%type;
4379 l_inv_rcpt_code mtl_parameters.negative_inv_receipt_code%type;
4380
4381 l_api_version_number CONSTANT NUMBER := 1.0;
4382 l_api_name CONSTANT VARCHAR2(30) := 'Check_Looose_and_packed_Qty';
4383 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4384
4385 l_transaction_source_type_id NUMBER;
4386 l_new_qoh NUMBER;
4387 l_new_att NUMBER;
4388 l_new_pqoh NUMBER;
4389 l_new_tqoh NUMBER;
4390 l_new_atpp1 NUMBER;
4391 l_new_qoh1 NUMBER;
4392
4393 l_suggested_sub_code VARCHAR2(30);
4394 l_suggested_loc_id NUMBER;
4395
4396 CURSOR c_org IS
4397 SELECT negative_inv_receipt_code
4398 FROM mtl_parameters
4399 WHERE organization_id = p_organization_id;
4400
4401 CURSOR c_lot_exp IS
4402 SELECT expiration_date
4403 FROM mtl_lot_numbers
4404 WHERE inventory_item_id = p_inventory_item_id
4405 AND organization_id = p_organization_id
4406 AND lot_number = p_lot_number;
4407
4408 CURSOR c_mmtt IS
4409 SELECT cost_group_id,transaction_source_type_id, subinventory_code, locator_id
4410 FROM mtl_material_transactions_temp
4411 WHERE transaction_temp_id = p_transaction_temp_id;
4412
4413 CURSOR c_item IS
4414 SELECT primary_uom_code
4415 FROM mtl_system_items
4416 WHERE inventory_item_id = p_inventory_item_id
4417 AND organization_id = p_organization_id;
4418
4419 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4420 BEGIN
4421
4422 IF (l_debug = 1) THEN
4423 mdebug ('Start check_loose_and_packed_qty.');
4424 END IF;
4425
4426 inv_quantity_tree_pub.clear_quantity_cache;
4427
4428 -- Standard call to check for call compatibility
4429 IF NOT fnd_api.compatible_api_call(l_api_version_number
4430 , p_api_version_number
4431 , l_api_name
4432 , G_PKG_NAME
4433 ) THEN
4434 RAISE fnd_api.g_exc_unexpected_error;
4435 END IF;
4436 IF (l_debug = 1) THEN
4437 mdebug ('Done checking if compatible api call.');
4438 END IF;
4439
4440 -- Initialize message list.
4441 IF fnd_api.to_boolean(p_init_msg_lst) THEN
4442 fnd_msg_pub.initialize;
4443 END IF;
4444
4445 p_ok_to_process := 'false';
4446
4447 --
4448 -- Initialize variables
4449 --
4450 IF p_is_revision_control = 'true' THEN
4451 l_is_revision_control := TRUE;
4452 END IF;
4453
4454 IF p_is_serial_control = 'true' THEN
4455 l_is_serial_control := TRUE;
4456 END IF;
4457 IF (l_debug = 1) THEN
4458 mdebug ('Done initializing variables.');
4459 END IF;
4460
4461 --
4462 -- Find the lot expiration date if
4463 -- the item is lot controlled.
4464 --
4465 IF p_is_lot_control = 'true' THEN
4466 l_is_lot_control := TRUE;
4467 OPEN c_lot_exp;
4468 FETCH c_lot_exp INTO l_lot_exp_dt;
4469 CLOSE c_lot_exp;
4470 END IF;
4471
4472 -- Find the cost group id being transacted
4473 OPEN c_mmtt;
4474 FETCH c_mmtt
4475 INTO
4476 l_cost_group_id, l_transaction_source_type_id,
4477 l_suggested_sub_code, l_suggested_loc_id;
4478 CLOSE c_mmtt;
4479 IF (l_debug = 1) THEN
4480 mdebug ('Cost group id from mmtt: '||l_cost_group_id);
4481 END IF;
4482
4483 -- Find the primary UOM code for the item
4484 OPEN c_item;
4485 FETCH c_item INTO l_primary_uom_code;
4486 CLOSE c_item;
4487 IF (l_debug = 1) THEN
4488 mdebug ('Primary UOM for this item: '||l_primary_uom_code);
4489 END IF;
4490
4491 -- Find if -ve inventory balances are allowed for this org
4492 OPEN c_org;
4493 FETCH c_org INTO l_inv_rcpt_code;
4494 CLOSE c_org;
4495 IF (l_debug = 1) THEN
4496 mdebug ('-ve inv rcpt code is: '||l_inv_rcpt_code);
4497 END IF;
4498
4499 -- Translate picked qty/uom into primary uom qty
4500 inv_convert.inv_um_conversion(
4501 from_unit => p_transaction_uom
4502 , to_unit => l_primary_uom_code
4503 , item_id => p_inventory_item_id
4504 , uom_rate => l_uom_rate
4505 );
4506 l_txn_qty := p_transaction_quantity * l_uom_rate;
4507 IF (l_debug = 1) THEN
4508 mdebug ('Transaction qty in primary units: '||l_txn_qty);
4509 END IF;
4510
4511 inv_txn_validations.get_available_quantity
4512 (x_return_status => l_return_status,
4513 p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode,
4514 p_organization_id => p_organization_id,
4515 p_inventory_item_id => p_inventory_item_id,
4516 p_is_revision_control => p_is_revision_control,
4517 p_is_lot_control => p_is_lot_control,
4518 p_is_serial_control => p_is_serial_control ,
4519 p_revision => p_revision,
4520 p_lot_number => p_lot_number ,
4521 p_lot_expiration_date => l_lot_exp_dt,
4522 p_subinventory_code => p_subinventory_code,
4523 p_locator_id => p_locator_id,
4524 p_source_type_id => l_transaction_source_type_id,
4525 x_qoh => l_new_qoh,
4526 x_att => l_new_att,
4527 x_pqoh => l_new_pqoh,
4528 x_tqoh => l_new_tqoh,
4529 x_atpp1 => l_new_atpp1,
4530 x_qoh1 => l_new_qoh1,
4531 p_cost_group_id => l_cost_group_id,
4532 p_transfer_subinventory => p_transfer_subinventory);
4533
4534
4535 IF (l_debug = 1) THEN
4536 mdebug(l_new_qoh || ' ' || l_new_att || ' ' || l_new_pqoh || ' ' || l_new_tqoh || ' ' || l_new_atpp1 || ' ' || l_new_qoh1);
4537 END IF;
4538
4539 -- If org allows negative inventory balances
4540 --
4541 IF l_inv_rcpt_code = 1 THEN
4542
4543 IF (l_debug = 1) THEN
4544 mdebug('org allows negative inventory balances');
4545 END IF;
4546
4547 p_ok_to_process := 'true';
4548
4549 IF (l_new_att < l_txn_qty) THEN
4550
4551 IF l_suggested_sub_code <> p_subinventory_code OR
4552 l_suggested_loc_id <> p_locator_id THEN
4553
4554 IF (l_debug = 1) THEN
4555 mdebug('suggested sub/loc are different from the actual sub/loc');
4556 END IF;
4557
4558 IF (least(nvl(l_new_att, 0), nvl(l_new_tqoh,0)) >= l_txn_qty) THEN
4559
4560 p_ok_to_process := 'true';
4561
4562 ELSE
4563 /*
4564 Bug #2075166.
4565 When negative inventory is allowed for the organization
4566 Change the ok_to_process flag to warning in order that
4567 a warning message id displayed instead of error.
4568 */
4569 p_ok_to_process := 'warning';
4570 IF (l_debug = 1) THEN
4571 mdebug('Driving inventory negative. Throw a warning');
4572 END IF;
4573
4574 END IF;
4575
4576 ELSE
4577
4578 IF (l_debug = 1) THEN
4579 mdebug('suggested sub/loc are same as the actual sub/loc');
4580 END IF;
4581
4582 IF (least((nvl(l_new_att,0) + l_txn_qty), nvl(l_new_tqoh,0)) >= l_txn_qty) THEN
4583
4584 p_ok_to_process := 'true';
4585
4586 ELSE
4587 /*
4588 Bug #2075166.
4589 When negative inventory is allowed for the organization
4590 Change the ok_to_process flag to warning in order that
4591 a warning message id displayed instead of error.
4592 */
4593 p_ok_to_process := 'warning';
4594 IF (l_debug = 1) THEN
4595 mdebug('Driving inventory negative. Throw a warning');
4596 END IF;
4597 --p_ok_to_process := 'false';
4598 --mdebug('Cannot drive inventory negative when reservations exist');
4599 END IF;
4600
4601 END IF;
4602
4603 END IF;
4604
4605 x_return_status := l_return_status; /* Success */
4606 return;
4607 END IF;
4608
4609 --
4610 -- Org does not allow negative inventory balances
4611 -- so continue.
4612 IF (l_debug = 1) THEN
4613 mdebug('org does not allow negative inventory balances');
4614 END IF;
4615
4616 IF (l_new_att < l_txn_qty) THEN
4617
4618 IF (l_debug = 1) THEN
4619 mdebug('l_new_att < l_txn_qty');
4620 END IF;
4621
4622 IF l_suggested_sub_code <> p_subinventory_code OR
4623 l_suggested_loc_id <> p_locator_id THEN
4624
4625 IF (l_debug = 1) THEN
4626 mdebug('suggested sub/loc are different from the actual sub/loc');
4627 END IF;
4628
4629 IF (least(nvl(l_new_att, 0), nvl(l_new_tqoh,0)) >= l_txn_qty) THEN
4630
4631 p_ok_to_process := 'true';
4632
4633 ELSE
4634
4635 p_ok_to_process := 'false';
4636
4637 END IF;
4638
4639 ELSE
4640
4641 IF (l_debug = 1) THEN
4642 mdebug('suggested sub/loc are the same as the actual sub/loc');
4643 END IF;
4644
4645 IF (least((nvl(l_new_att,0) + l_txn_qty), nvl(l_new_tqoh,0)) >= l_txn_qty) THEN
4646
4647 p_ok_to_process := 'true';
4648
4649 ELSE
4650
4651 p_ok_to_process := 'false';
4652
4653 END IF;
4654
4655 END IF;
4656
4657 ELSE
4658
4659 p_ok_to_process := 'true';
4660 END IF;
4661
4662 END check_loose_and_packed_qty;
4663
4664 /* Bug 4194323 Added Overloaded Procedure to get available quantity
4665 when Demand Information is provided as part of WIP Enhancement 4163405 */
4666 PROCEDURE GET_AVBL_TO_TRANSACT_QTY(
4667 x_return_status OUT NOCOPY VARCHAR2,
4668 p_organization_id IN NUMBER,
4669 p_inventory_item_id IN NUMBER,
4670 p_is_revision_control IN VARCHAR2,
4671 p_is_lot_control IN VARCHAR2,
4672 p_is_serial_control IN VARCHAR2,
4673 p_demand_source_type_id IN NUMBER,
4674 p_demand_source_header_id IN NUMBER,
4675 p_demand_source_line_id IN NUMBER,
4676 p_revision IN VARCHAR2,
4677 p_lot_number IN VARCHAR2,
4678 p_lot_expiration_date IN DATE,
4679 p_subinventory_code IN VARCHAR2,
4680 p_locator_id IN NUMBER,
4681 x_att OUT NOCOPY NUMBER
4682 )
4683 IS
4684 l_msg_count VARCHAR2(100);
4685 l_msg_data VARCHAR2(1000);
4686 l_qoh NUMBER := 0 ;
4687 l_rqoh NUMBER := 0 ;
4688 l_qr NUMBER := 0 ;
4689 l_qs NUMBER := 0 ;
4690 l_att NUMBER := 0 ;
4691 l_atr NUMBER := 0 ;
4692 l_is_revision_control BOOLEAN := FALSE;
4693 l_is_lot_control BOOLEAN := FALSE;
4694 l_is_serial_control BOOLEAN := FALSE;
4695 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4696 p_init_msg_lst VARCHAR2(30);
4697 l_api_name CONSTANT VARCHAR2(30) := 'Get_Avbl_To_Transact_Qty';
4698 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4699 BEGIN
4700 IF (l_debug = 1) THEN
4701 mdebug ('Inside get_avbl_to_transact_qty');
4702 END IF;
4703
4704 -- checking for all the item controls
4705 IF p_is_revision_control = 'true' THEN
4706 l_is_revision_control := TRUE;
4707 END IF;
4708
4709 IF p_is_lot_control = 'true' THEN
4710 l_is_lot_control := TRUE;
4711 END IF;
4712
4713 IF p_is_serial_control = 'true' THEN
4714 l_is_serial_control := TRUE;
4715 END IF;
4716
4717 -- Initialize message list.
4718 IF fnd_api.to_boolean(p_init_msg_lst) THEN
4719 fnd_msg_pub.initialize;
4720 END IF;
4721
4722 -- Clearing any cache if existent
4723 inv_quantity_tree_grp.clear_quantity_cache ;
4724
4725 -- this call will provide reserved quantity for all but the demand source specified
4726 inv_quantity_tree_pub.query_quantities
4727 ( p_api_version_number => 1.0
4728 , p_init_msg_lst => fnd_api.g_false
4729 , x_return_status => l_return_status
4730 , x_msg_count => l_msg_count
4731 , x_msg_data => l_msg_data
4732 , p_organization_id => p_organization_id
4733 , p_inventory_item_id => p_inventory_item_id
4734 , p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
4735 , p_is_revision_control => l_is_revision_control
4736 , p_is_lot_control => l_is_lot_control
4737 , p_is_serial_control => l_is_serial_control
4738 , p_demand_source_type_id => p_demand_source_type_id
4739 , p_demand_source_header_id => p_demand_source_header_id
4740 , p_demand_source_line_id =>p_demand_source_line_id
4741 , p_revision => p_revision
4742 , p_lot_number => p_lot_number
4743 , p_lot_expiration_date => p_lot_expiration_date
4744 , p_subinventory_code => p_subinventory_code
4745 , p_locator_id => p_locator_id
4746 , x_qoh => l_qoh
4747 , x_rqoh => l_rqoh
4748 , x_qr => l_qr
4749 , x_qs => l_qs
4750 , x_att => l_att
4751 , x_atr => l_atr
4752 );
4753
4754 IF l_return_status = fnd_api.g_ret_sts_error THEN
4755 RAISE fnd_api.g_exc_error;
4756 END IF ;
4757
4758 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4759 RAISE fnd_api.g_exc_unexpected_error;
4760 END IF;
4761
4762 x_att := l_att ;
4763
4764 IF (l_debug = 1) THEN
4765 mdebug ('Quantity Available for Demand Source Header : ' || p_demand_source_header_id ||
4766 ' Demand Source Line : '|| p_demand_source_line_id || ' is : '|| x_att );
4767 mdebug('@'||l_msg_data||'@');
4768 END IF;
4769
4770 x_return_status := l_return_status;
4771
4772 EXCEPTION
4773
4774 WHEN fnd_api.g_exc_error THEN
4775 x_return_status := fnd_api.g_ret_sts_error;
4776
4777 -- Get message count and data
4778 fnd_msg_pub.count_and_get
4779 ( p_count => l_msg_count
4780 , p_data => l_msg_data
4781 );
4782
4783 WHEN fnd_api.g_exc_unexpected_error THEN
4784 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4785
4786 -- Get message count and data
4787 fnd_msg_pub.count_and_get
4788 ( p_count => l_msg_count
4789 , p_data => l_msg_data
4790 );
4791
4792 WHEN OTHERS THEN
4793 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4794
4795 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4796 THEN
4797
4798 fnd_msg_pub.add_exc_msg
4799 ( g_pkg_name
4800 , l_api_name
4801 );
4802 END IF;
4803
4804 -- Get message count and data
4805 fnd_msg_pub.count_and_get
4806 ( p_count => l_msg_count
4807 , p_data => l_msg_data
4808 );
4809
4810 END GET_AVBL_TO_TRANSACT_QTY ;
4811
4812 --Bug#4446248.Added the following function to check any pending transaction
4813 --for the LPN.
4814 FUNCTION check_lpn_pending_txns( p_lpn_id IN NUMBER,
4815 p_org_id IN NUMBER,
4816 x_return_msg OUT NOCOPY VARCHAR2)
4817 RETURN VARCHAR2
4818 IS
4819 l_lpn_id NUMBER;
4820 l_count NUMBER :=0 ;
4821 x_return VARCHAR2(1) := 'Y';
4822
4823 CURSOR c_lpn_content IS
4824 select lpn_id
4825 from wms_license_plate_numbers
4826 where outermost_lpn_id = p_lpn_id
4827 and organization_id = p_org_id;
4828
4829 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4830 BEGIN
4831 OPEN c_lpn_content;
4832 LOOP
4833 FETCH c_lpn_content INTO l_lpn_id;
4834 EXIT WHEN c_lpn_content%NOTFOUND;
4835
4836 select count(1)
4837 into l_count
4838 from mtl_material_transactions_temp mmtt
4839 where (mmtt.lpn_id = l_lpn_id or mmtt.content_lpn_id = l_lpn_id)
4840 and mmtt.organization_id = p_org_id;
4841
4842 if l_count > 0 then
4843 CLOSE c_lpn_content;
4844 IF (l_debug = 1) THEN
4845 mdebug ('lpn '||l_lpn_id||' has some pending transactions to be completed.');
4846 END IF;
4847 x_return := 'N';
4848 fnd_message.set_name('INV', 'INV_PENDING_TXNS_EXISTS');
4849 x_return_msg := fnd_message.get;
4850 RETURN x_return;
4851 end if;
4852 END LOOP;
4853 CLOSE c_lpn_content;
4854 x_return_msg := 'SUCCESS';
4855 RETURN x_return;
4856 EXCEPTION
4857 WHEN OTHERS THEN
4858 IF (l_debug = 1) THEN
4859 mdebug ('Other exception raised in check_lpn_pending_txns');
4860 END IF;
4861 x_return := 'N';
4862 x_return_msg := 'OTHER ERROR';
4863 RETURN x_return;
4864 END check_lpn_pending_txns; --End of fix for bug#4446248
4865
4866 END INV_TXN_VALIDATIONS;