[Home] [Help]
PACKAGE BODY: APPS.INV_CONSIGNED_VALIDATIONS
Source
1 PACKAGE BODY INV_CONSIGNED_VALIDATIONS AS
2 /* $Header: INVVMILB.pls 120.4.12010000.3 2008/12/03 11:05:15 rkatoori ship $ */
3
4 /*****************
5 * Private API *
6 *****************/
7
8 -- This api queries the global temp table based on different levels
9 -- Level 1 = no query
10 -- Level 2 = query all CONSIGNED_VMI
11 -- Level 3 = query VMI at revsion
12 -- Level 4 = query VMI at Lot
13 -- Level 5 = query VMI at Sub
14 -- Level 6 = query VMI at locator
15 -- Level 7 equals VMI at cost group level
16
17 --Variable indicating whether debugging is turned on
18 g_debug NUMBER := NULL;
19
20 PROCEDURE debug_print( p_message IN VARCHAR2
21 , p_title IN VARCHAR2 DEFAULT 'INV_VMI_QT'
22 , p_level IN NUMBER := 9)
23 IS
24 BEGIN
25 IF g_debug IS NULL THEN
26 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
27 END IF;
28
29 IF (g_debug = 1) THEN
30 inv_log_util.trace(p_message, p_title, p_level);
31 END IF;
32 --dbms_output.put_line(p_message);
33 END debug_print;
34
35 PROCEDURE query_vmi_consigned
36 ( x_return_status OUT NOCOPY VARCHAR2
37 , x_msg_count OUT NOCOPY VARCHAR2
38 , x_msg_data OUT NOCOPY VARCHAR2
39 , p_organization_id IN NUMBER
40 , p_planning_org_id IN NUMBER
41 , p_owning_org_id NUMBER
42 , p_inventory_item_id IN NUMBER
43 , p_tree_mode IN NUMBER
44 , p_is_revision_control IN BOOLEAN
45 , p_is_lot_control IN BOOLEAN
46 , p_is_serial_control IN BOOLEAN
47 , p_demand_source_line_id IN NUMBER
48 , p_revision IN VARCHAR2
49 , p_lot_number IN VARCHAR2
50 , p_lot_expiration_date IN DATE
51 , p_subinventory_code IN VARCHAR2
52 , p_locator_id IN NUMBER
53 , p_cost_group_id IN NUMBER
54 , x_qoh OUT NOCOPY NUMBER
55 , x_sqoh OUT NOCOPY NUMBER -- invConv change
56 ) IS
57
58 l_table_count NUMBER := 0;
59 l_count NUMBER := 0;
60 -- l_total_qty NUMBER := 0; -- not used !!!
61 l_level NUMBER := 1;
62 l_qoh NUMBER := 0;
63 l_sqoh NUMBER := 0; -- invConv change
64 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
65
66 -- invConv changes begin
67 l_uom_ind VARCHAR2(3);
68 l_lot_ctl NUMBER;
69 l_grade_ctl VARCHAR2(1);
70
71
72 CURSOR get_item_info( l_org_id IN NUMBER
73 , l_item_id IN NUMBER) IS
74 SELECT tracking_quantity_ind
75 , lot_control_code
76 , grade_control_flag
77 FROM mtl_system_items
78 WHERE inventory_item_id = l_item_id
79 AND organization_id = l_org_id;
80 -- invConv changes end
81
82 BEGIN
83
84 IF (l_debug = 1) THEN
85 inv_log_util.trace('In Query_VMI_Consigned','CONSIGNED_VALIDATIONS',9);
86 END IF;
87
88 x_return_status:= fnd_api.g_ret_sts_success;
89
90 -- invConv changes begin
91 -- Validations : DUOM item
92 OPEN get_item_info( p_organization_id, p_inventory_item_id);
93 FETCH get_item_info
94 INTO l_uom_ind
95 , l_lot_ctl
96 , l_grade_ctl;
97 CLOSE get_item_info;
98 -- invConv changes end
99
100 -- query the temp table and first check if any rows exists
101 --SELECT COUNT(*)INTO l_table_count FROM mtl_consigned_qty_temp
102 -- WHERE inventory_item_id = p_inventory_item_id
103 --AND organization_id = p_organization_id;
104 -- Use Exists to check existance
105 l_table_count := 0;
106 BEGIN
107 SELECT 1 INTO l_table_count FROM dual
108 WHERE EXISTS (SELECT 1 FROM mtl_consigned_qty_temp
109 WHERE inventory_item_id = p_inventory_item_id
110 AND organization_id = p_organization_id);
111 EXCEPTION
112 WHEN others THEN
113 l_table_count := 0;
114 END;
115
116 IF (l_table_count = 0) THEN
117 IF (l_debug = 1) THEN
118 inv_log_util.trace('No record found in mtl_consigned_qty_temp, return 0','CONSIGNED_VALIDATIONS',9);
119 END IF;
120 x_qoh := 0;
121
122 -- invConv changes begin
123 IF (l_uom_ind = 'PS')
124 THEN
125 x_sqoh := 0;
126 ELSE
127 x_sqoh := NULL;
128 END IF;
129 -- invConv changes end
130
131 x_return_status:= fnd_api.g_ret_sts_success;
132 RETURN;
133 END IF;
134
135 -- compute level
136
137 IF(p_revision is null) THEN
138 l_level := 2;
139 END IF;
140 IF (p_revision IS NOT NULL) THEN
141 l_level:=3;
142 END IF;
143 IF((l_level=2) AND (p_lot_number IS null)) then
144 l_level:= 2;
145 END IF;
146 IF((l_level=3) AND (p_lot_number IS null)) then
147 l_level:= 3;
148 END IF;
149 IF((l_level=2 OR l_level=3) AND (p_lot_number IS NOT NULL)) THEN
150 l_level:= 4;
151 END IF;
152 IF((l_level=2) AND (p_subinventory_code IS NULL)) THEN
153 l_level:= 2;
154 END IF;
155 IF((l_level=3) AND (p_subinventory_code IS NULL)) THEN
156 l_level:= 3;
157 END IF;
158 IF((l_level=4) AND (p_subinventory_code IS NULL)) THEN
159 l_level:= 4;
160 END IF;
161 IF((l_level=2 OR l_level =3 or l_level =4) AND (p_subinventory_code IS NOT NULL)) THEN
162 l_level:= 5;
163 END IF;
164 IF((l_level = 5) AND ( p_locator_id IS NULL)) THEN
165 l_level:= 5;
166 END IF;
167 IF((l_level = 5) AND (p_locator_id IS NOT NULL)) THEN
168 l_level:= 6;
169 END IF;
170 IF((l_level = 6) AND ( p_cost_group_id IS NULL)) THEN
171 l_level:= 6;
172 END IF;
173 IF((l_level = 6) AND ( p_cost_group_id IS NOT NULL)) THEN
174 l_level:= 7;
175 END IF;
176
177 IF (l_debug = 1) THEN
178 inv_log_util.trace('Final Level= '||L_LEVEL,'CONSIGNED_VALIDATIONS',9);
179 END IF;
180
181
182 IF (l_level =2) THEN
183 -- invConv change : added secondary quantity
184 SELECT Nvl(sum(primary_quantity),0)
185 , Nvl(sum(secondary_quantity),0)
186 INTO l_qoh, l_sqoh
187 FROM mtl_consigned_qty_temp
188 WHERE organization_id = p_organization_id
189 AND inventory_item_id = p_inventory_item_id
190 AND
191 DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
192 DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
193 AND Nvl(planning_organization_id, -999) =
194 NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
195 AND NVL(owning_organization_id, -999) =
196 NVL(p_owning_org_id,Nvl(owning_organization_id, -999));
197 END IF;
198 IF (l_level=3)then
199 -- invConv change : added secondary quantity
200 SELECT Nvl(sum(primary_quantity),0)
201 , Nvl(sum(secondary_quantity),0)
202 INTO l_qoh, l_sqoh
203 FROM mtl_consigned_qty_temp
204 WHERE organization_id = p_organization_id
205 AND inventory_item_id = p_inventory_item_id
206 AND
207 DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
208 DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
209 AND NVL(planning_organization_id, -999) =
210 NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
211 AND Nvl(owning_organization_id, -999) =
212 NVL(p_owning_org_id,Nvl(owning_organization_id, -999))
213 AND revision = p_revision;
214 END IF;
215 IF (l_level =4) THEN
216 -- invConv change : added secondary quantity
217 SELECT Nvl(sum(primary_quantity),0)
218 , Nvl(sum(secondary_quantity),0)
219 INTO l_qoh, l_sqoh
220 FROM mtl_consigned_qty_temp
221 WHERE organization_id = p_organization_id
222 AND inventory_item_id = p_inventory_item_id
223 AND Nvl(planning_organization_id, -999) =
224 NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
225 AND Nvl(owning_organization_id, -999) =
226 NVL(p_owning_org_id,Nvl(owning_organization_id, -999))
227 AND
228 DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
229 DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
230 AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
231 AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'));
232 END IF;
233 IF(l_level =5)THEN
234 -- invConv change : added secondary quantity
235 SELECT Nvl(sum(primary_quantity),0)
236 , Nvl(sum(secondary_quantity),0)
237 INTO l_qoh, l_sqoh
238 FROM mtl_consigned_qty_temp
239 WHERE organization_id = p_organization_id
240 AND inventory_item_id = p_inventory_item_id
241 AND Nvl(planning_organization_id, -999) =
242 NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
243 AND Nvl(owning_organization_id, -999) =
244 NVL(p_owning_org_id,Nvl(owning_organization_id, -999))
245 AND
246 DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
247 DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
248 AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
249 AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'))
250 AND subinventory_code = p_subinventory_code;
251 END IF;
252 IF(l_LEVEL=6)THEN
253 -- invConv change : added secondary quantity
254 SELECT Nvl(sum(primary_quantity),0)
255 , Nvl(sum(secondary_quantity),0)
256 INTO l_qoh, l_sqoh
257 FROM mtl_consigned_qty_temp
258 WHERE organization_id = p_organization_id
259 AND inventory_item_id = p_inventory_item_id
260 AND Nvl(planning_organization_id, -999) =
261 Nvl(p_planning_org_id,Nvl(planning_organization_id, -999))
262 AND Nvl(owning_organization_id, -999) =
263 Nvl(p_owning_org_id,Nvl(owning_organization_id, -999))
264 AND
265 DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
266 DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
267 AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
268 AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'))
269 AND subinventory_code = p_subinventory_code
270 AND locator_id = p_locator_id ;
271 END IF;
272 IF(l_level=7) THEN
273 -- invConv change : added secondary quantity
274 SELECT Nvl(sum(primary_quantity),0)
275 , Nvl(sum(secondary_quantity),0)
276 INTO l_qoh, l_sqoh
277 FROM mtl_consigned_qty_temp
278 WHERE organization_id = p_organization_id
279 AND inventory_item_id = p_inventory_item_id
280 AND Nvl(planning_organization_id, -999) =
281 Nvl(p_planning_org_id,Nvl(planning_organization_id, -999))
282 AND Nvl(owning_organization_id, -999) =
283 Nvl(p_owning_org_id,Nvl(owning_organization_id, -999))
284 AND
285 Decode(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
286 Decode(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
287 AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
288 AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'))
289 AND subinventory_code = p_subinventory_code
290 AND locator_id = p_locator_id
291 AND cost_group_id = p_cost_group_id;
292 END IF;
293
294 debug_print('After Querying mtl_consigned_qty_temp, qoh='||l_qoh||', sqoh='||l_sqoh||', item_type='||l_uom_ind);
295
296 x_qoh := l_qoh;
297 -- invConv changes begin
298 IF (l_uom_ind = 'PS')
299 THEN
300 x_sqoh := l_sqoh;
301 ELSE
302 x_sqoh := NULL;
303 END IF;
304 -- invConv changes end
305
306 EXCEPTION
307 when others THEN
308 IF (l_debug = 1) THEN
309 inv_log_util.trace('When others in query CONSIGNED/VMI ','CONSIGNED_VALIDATIONS',9);
310 END IF;
311 x_return_status := 'E';
312 x_qoh := 0;
313 -- invConv changes begin
314 IF (l_uom_ind = 'PS')
315 THEN
316 x_sqoh := 0;
317 ELSE
318 x_sqoh := NULL;
319 END IF;
320 -- invConv changes end
321 RAISE fnd_api.g_exc_unexpected_error;
322 END query_vmi_consigned;
323
324
325 -------------------------------------------------------------------------------
326 -- Procedure --
327 -- build_sql --
328 -- --
329 -- Description --
330 -- build the sql statement for the tree creation --
331 -- --
332 -- Notes --
333 -- This procedure is also used by the pick and put away engine to build --
334 -- the picking base sql --
335 -- --
336 -- Input Parameters --
337 -- p_mode --
341 -- true or false --
338 -- equals inv_quantity_tree_pvt.g_reservation_mode or --
339 -- inv_quantity_tree_pvt.g_transaction_mode --
340 -- p_is_lot_control --
342 -- p_asset_sub_only --
343 -- true or false --
344 -- p_include_suggestion --
345 -- always true now
346 -- p_lot_expiration_date --
347 -- if not null, only consider lots that will not expire at the date --
348 -- or ealier --
349 -- --
350 -- Output Parameters --
351 -- x_return_status --
352 -- standard output parameter. Possible values are --
353 -- 1. fnd_api.g_ret_sts_success for success --
354 -- 2. fnd_api.g_ret_sts_exc_error for expected error --
355 -- 3. fnd_api.g_ret_sts_unexp_error for unexpected error --
356 -------------------------------------------------------------------------------
357
358
359 PROCEDURE build_sql
360 ( x_return_status OUT NOCOPY VARCHAR2
361 , p_mode IN INTEGER
362 , p_grade_code IN VARCHAR2 -- invConv change
363 , p_is_lot_control IN BOOLEAN
364 , p_asset_sub_only IN BOOLEAN
365 , p_lot_expiration_date IN DATE
366 , p_onhand_source IN NUMBER
367 , p_pick_release IN NUMBER
368 , x_sql_statement OUT NOCOPY long
369 , p_is_revision_control IN BOOLEAN
370 ) IS
371
372
373 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
374 --
375 l_stmt long;
376 l_asset_sub_where long;
377 l_revision_select long;
378 l_lot_select long;
379 l_lot_select2 long;
380 l_lot_from long;
381 l_lot_where long;
382 l_lot_expiration_where long;
383 l_lot_group long;
384 l_onhand_source_where long;
385 l_onhand_stmt long;
386 l_pending_txn_stmt long;
387 l_onhand_qty_part VARCHAR2(3000);
388 l_mmtt_qty_part VARCHAR2(3000);
389 l_mtlt_qty_part VARCHAR2(3000);
390 p_n NUMBER;
391 p_v VARCHAR2(1);
392 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
393
394 -- invConv changes begin
395 l_onhand_sqty_part VARCHAR2(1000);
396 l_mmtt_sqty_part VARCHAR2(1000);
397 l_mtlt_sqty_part VARCHAR2(1000);
398 -- invConv changes end
399 BEGIN
400
401 -- Bug 2824557, Remove the reference to demand_source_line_id
402 -- Because consign quantity does not care of PJM unit numbers
403 -- Therefore onhand quantity only query from MOQD
404 -- pjm support
405 --
406 /*IF g_unit_eff_enabled IS NULL THEN
407 -- To improve performance, avoid using select from dual;
408 g_unit_eff_enabled := pjm_unit_eff.enabled;
409 --SELECT pjm_unit_eff.enabled INTO g_unit_eff_enabled FROM dual;
410 END IF; */
411 --IF g_unit_eff_enabled <> 'Y' THEN
412 l_onhand_qty_part := ' moq.primary_transaction_quantity ';
413 l_mmtt_qty_part := ' mmtt.primary_quantity ';
414 l_mtlt_qty_part := ' mtlt.primary_quantity ';
415
416 -- invConv changes begin
417 l_onhand_sqty_part := ' moq.secondary_transaction_quantity ';
418 l_mmtt_sqty_part := ' mmtt.secondary_transaction_quantity ';
419 l_mtlt_sqty_part := ' mtlt.secondary_quantity ';
420 -- invConv changes end
421 /* ELSE
422 l_onhand_qty_part := ' decode(:demand_source_line_id, NULL, moq.primary_transaction_quantity, nvl(pjm_ueff_onhand.onhand_quantity
423 (:demand_source_line_id,moq.inventory_item_id,moq.organization_id
424 ,moq.revision,moq.subinventory_code,moq.locator_id,moq.lot_number)
425 ,moq.primary_transaction_quantity)) ';
426 l_mmtt_qty_part := ' decode(:demand_source_line_id, NULL, mmtt.primary_quantity, Nvl(pjm_ueff_onhand.txn_quantity(:demand_source_line_id,mmtt.transaction_temp_id,mmtt.lot_number,
427 ''N'',mmtt.inventory_item_id, mmtt.organization_id, mmtt.transaction_source_type_id,
428 mmtt.transaction_source_id, mmtt.rcv_transaction_id,
429 sign(mmtt.primary_quantity)
430 ),mmtt.primary_quantity)) ';
431 l_mtlt_qty_part := ' decode(:demand_source_line_id, NULL, mtlt.primary_quantity, Nvl(pjm_ueff_onhand.txn_quantity(:demand_source_line_id,mmtt.transaction_temp_id,mtlt.lot_number,
432 ''N'',mmtt.inventory_item_id, mmtt.organization_id, mmtt.transaction_source_type_id,
433 mmtt.transaction_source_id, mmtt.rcv_transaction_id,
434 sign(mmtt.primary_quantity)
435 ) ,mtlt.primary_quantity)) ';
436 END IF; */
437
438
439 -- deal with onhand quantities
440 -- if containerized_flag is 1, then quantity is in container(s)
441
442 -- invConv changes begin : added 2nd qty management in the query.
443 l_onhand_stmt := '
444
445 -- onhand quantities
446 SELECT
447 moq.organization_id organization_id
451 , moq.subinventory_code subinventory_code
448 , moq.inventory_item_id inventory_item_id
449 , moq.revision revision
450 , moq.lot_number lot_number
452 , moq.locator_id locator_id
453 , ' || l_onhand_qty_part || ' primary_quantity
454 , ' || l_onhand_sqty_part || ' secondary_quantity
455 , nvl(moq.orig_date_received,
456 moq.date_received) date_received
457 , 1 quantity_type
458 , moq.cost_group_id cost_group_id
459 , decode(moq.containerized_flag,
460 1, 1, 0) containerized
461 , moq.planning_organization_id planning_organization_id
462 , moq.owning_organization_id owning_organization_id
463 FROM
464 mtl_onhand_quantities_detail moq
465 WHERE moq.organization_id <> Nvl(moq.planning_organization_id,moq.organization_id)
466 OR moq.organization_id <> nvl(moq.owning_organization_id, moq.organization_id) ';
467
468 -- dealing with pending transactions in mmtt
469 -- and picking suggestions
470 --
471 -- Notes: the put away suggestions are not considered either
472 -- as reservation nor as pending transactions because of the
473 -- way the integration between reservation and suggestion
474 -- is currently implemented. A put away suggestion with transaction_status
475 -- as 2, is not a reservation since the corresponding quantity
476 -- has not been moved to the destination; it is not a pending
477 -- transaction because the quantity that will be moved from source
478 -- location to destination location is not available as onhand.
479 -- The reason is that once it is moved, the pick confirm process might transfer
480 -- an existing reservation for that quantity to the new destination.
481 --
482 -- WARNING: the value of transaction_action_id is used to
483 -- decide whether a suggestion is a picking or it is a
484 -- put away, so any changes to the transaction id
485 -- should be reflected in the decode portion in the where clause
486
487 /* we are not considering pending transaction, blocking it
488
489 IF p_is_lot_control THEN
490 -- here we assume that even there is only one lot number
491 -- involved in a transaction, a child record would be
492 -- created in the mtl_transaction_lots_temp table.
493 l_pending_txn_stmt := '
494 UNION ALL
495 -- pending transactions and picking suggestions
496 -- in mmtt with lot number in mmtt
497 --added 1 to decode statement so that we make sure the
498 --issue qtys in mmtt are seen as negative.
499 --added decode stmt to quantity_type. If record is a
500 --suggestion, qty-type is 5 (txn suggestion). If it is
501 --a pending txn, qty_type is 1 (quantity on hand).
502 --also, added another decode stmt to primary qty. If the
503 --record is a suggestion, we want the primary_qty to be positive,
504 --like a reservation
505 -- if quantity is in an lpn, then it is containerized
506 -- packed mmtt recs can have either lpn_id or
507 -- content lpn_id populated. To handle this, changed
508 -- how containerized is determined for MMTT recs. Assuming
509 -- that lpn_Id and content_lpn_id are always positive,
510 -- the existence of either causes containerized to be 1 (since
511 -- lpn_id will be greater than 1). If both are null,
512 -- containerized will be 0 (0 is less than 1).
513 SELECT
514 mmtt.organization_id organization_id
515 , mmtt.inventory_item_id inventory_item_id
516 , mmtt.revision revision
517 , mmtt.lot_number lot_number
518 , mmtt.subinventory_code subinventory_code
519 , mmtt.locator_id locator_id
520 , Decode (mmtt.transaction_status, 2, 1
521 , Decode(mmtt.transaction_action_id
522 , 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.primary_quantity))
523 )
524 * Abs('|| l_mmtt_qty_part || ')
525 , Decode (mmtt.transaction_status, 2, 1
526 , Decode(mmtt.transaction_action_id
527 , 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.secondary_transaction_quantity))
528 )
529 * Abs('|| l_mmtt_sqty_part || ')
530 , Decode(mmtt.transaction_action_id
531 , 1, To_date(NULL)
532 , 2, To_date(NULL)
533 , 28, To_date(NULL)
534 , 3, To_date(NULL)
535 , Decode(Sign(mmtt.primary_quantity)
536 , -1, To_date(NULL)
537 , mmtt.transaction_date)) date_received
538 , Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
539 , mmtt.cost_group_id cost_group_id
540 , least(1,NVL(mmtt.lpn_id,0)+NVL(mmtt.content_lpn_id,0))
541 containerized
542 , planning_organization_id planning_organziation_id
543 , owning_organization_id owning_organization_id
544 FROM
545 mtl_material_transactions_temp mmtt
546 WHERE
547 mmtt.posting_flag = ''Y''
548 AND mmtt.lot_number IS NOT NULL
549 AND mmtt.subinventory_code IS NOT NULL
550 AND (Nvl(mmtt.transaction_status,0) <> 2 OR -- pending txns
551 -- only picking side of the suggested transactions are used
552 Nvl(mmtt.transaction_status,0) = 2 AND
556 AND mmtt.transaction_action_id NOT IN (24,30)
553 mmtt.transaction_action_id IN (1,2,28,3,5,21,29,32,34)
554 )
555 -- dont look at scrap and costing txns
557 AND( (mmtt.organization_id <> Nvl(mmtt.planning_organization_id,mmtt.organization_id))
558 OR(mmtt.organization_id <> Nvl(mmtt.owning_organization_id,mmtt.organization_id)))
559 UNION ALL
560 -- pending transactions and suggestions in mmtt with lot numbers in lots_temp
561 --added 1 to decode statement so that we make sure the
562 --issue qtys in mmtt are seen as negative.
563 -- if quantity is in an lpn, then it is containerized.
564 -- packed mmtt recs can have either lpn_id or
565 -- content lpn_id populated. To handle this, changed
566 -- how containerized is determined for MMTT recs. Assuming
567 -- that lpn_Id and content_lpn_id are always positive,
568 -- the existence of either causes containerized to be 1 (since
569 -- lpn_id will be greater than 1). If both are null,
570 -- containerized will be 0 (0 is less than 1).
571 SELECT
572 mmtt.organization_id organization_id
573 , mmtt.inventory_item_id inventory_item_id
574 , mmtt.revision revision
575 , mtlt.lot_number lot_number
576 , mmtt.subinventory_code subinventory_code
577 , mmtt.locator_id locator_id
578 , Decode(mmtt.transaction_status, 2, 1
579 , Decode(mmtt.transaction_action_id
580 , 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.transaction_quantity))
581 )
582 * Abs('||l_mtlt_qty_part||')
583 , Decode(mmtt.transaction_status, 2, 1
584 , Decode(mmtt.transaction_action_id
585 , 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.secondary_transaction_quantity))
586 )
587 * Abs('||l_mtlt_sqty_part||')
588 , Decode(mmtt.transaction_action_id
589 , 1, To_date(NULL)
590 , 2, To_date(NULL)
591 , 28, To_date(NULL)
592 , 3, To_date(NULL)
593 , Decode(Sign(mmtt.primary_quantity)
594 , -1, To_date(NULL)
595 , mmtt.transaction_date)) date_received
596 , Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
597 , mmtt.cost_group_id cost_group_id
598 , least(1,NVL(mmtt.lpn_id,0)+NVL(mmtt.content_lpn_id,0))
599 containerized
600 , mmtt.planning_organization_id planning_organization_id
601 , mmtt.owning_organization_id owning_organization_id
602 FROM
603 mtl_material_transactions_temp mmtt
604 , mtl_transaction_lots_temp mtlt
605 WHERE
606 mmtt.posting_flag = ''Y''
607 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
608 AND mmtt.lot_number IS NULL
609 AND mmtt.subinventory_code IS NOT NULL
610 AND (Nvl(mmtt.transaction_status,0) <> 2 OR -- pending txns
611 -- only picking side of the suggested transactions are used
612 Nvl(mmtt.transaction_status,0) = 2 AND
613 mmtt.transaction_action_id IN (1,2,28,3,5,21,29,32,34)
614 )
615 -- dont look at scrap and costing txns
616 AND mmtt.transaction_action_id NOT IN (24,30)
617 AND ( (mmtt.organization_id <>Nvl(mmtt.planning_organization_id,mmtt.organization_id))
618 OR(mmtt.organization_id <>Nvl(mmtt.owning_organization_id,mmtt.organization_id))) ';
619
620 ELSE -- without lot control
621 l_pending_txn_stmt := '
622 UNION ALL
623 -- pending transactions in mmtt
624 --changed by jcearley on 12/8/99
625 --added 1 to decode statement so that we make sure the
626 --issue qtys in mmtt are seen as negative.
627 -- if quantity is in an lpn, then it is containerized
628 -- packed mmtt recs can have either lpn_id or
629 -- content lpn_id populated. To handle this, changed
630 -- how containerized is determined for MMTT recs. Assuming
631 -- that lpn_Id and content_lpn_id are always positive,
632 -- the existence of either causes containerized to be 1 (since
633 -- lpn_id will be greater than 1). If both are null,
634 -- containerized will be 0 (0 is less than 1).
635 SELECT
636 mmtt.organization_id organization_id
637 , mmtt.inventory_item_id inventory_item_id
638 , mmtt.revision revision
639 , NULL lot_number
640 , mmtt.subinventory_code subinventory_code
641 , mmtt.locator_id locator_id
642 , Decode(mmtt.transaction_status, 2, 1
643 , Decode(mmtt.transaction_action_id
644 , 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.primary_quantity))
645 )
646 * Abs('|| l_mmtt_qty_part || ')
647 , Decode(mmtt.transaction_status, 2, 1
648 , Decode(mmtt.transaction_action_id
649 , 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.secondary_transaction_quantity))
650 )
651 * Abs('|| l_mmtt_sqty_part || ')
652 , Decode(mmtt.transaction_action_id
653 , 1, To_date(NULL)
654 , 2, To_date(NULL)
655 , 28, To_date(NULL)
656 , 3, To_date(NULL)
657 , Decode(Sign(mmtt.primary_quantity)
658 , -1, To_date(NULL)
659 , mmtt.transaction_date)) date_received
663 containerized
660 , Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
661 , mmtt.cost_group_id cost_group_id
662 , least(1,NVL(mmtt.lpn_id,0)+NVL(mmtt.content_lpn_id,0))
664 , mmtt.planning_organization_id planning_organization_id
665 , mmtt.owning_organization_id owning_organization_id
666 FROM
667 mtl_material_transactions_temp mmtt
668 WHERE
669 mmtt.posting_flag = ''Y''
670 AND mmtt.subinventory_code IS NOT NULL
671 AND (Nvl(mmtt.transaction_status,0) <> 2 OR -- pending txns
672 -- only picking side of the suggested transactions are used
673 Nvl(mmtt.transaction_status,0) = 2 AND
674 mmtt.transaction_action_id IN (1,2,28,3,5,21,29,32,34)
675 )
676 -- dont look at scrap and costing txns
677 AND mmtt.transaction_action_id NOT IN (24,30)
678 AND ( (mmtt.organization_id <> Nvl(mmtt.planning_organization_id,mmtt.organization_id))
679 OR(mmtt.organization_id <> Nvl(mmtt.owning_organization_id,mmtt.organization_id))) ';
680
681 END IF;
682
683 */
684
685 -- common restrictions
686 IF p_asset_sub_only THEN
687 l_asset_sub_where := '
688 AND Nvl(sub.asset_inventory,1) = 1';
689 ELSE
690 l_asset_sub_where := NULL;
691 END IF;
692
693 IF (p_onhand_source = g_atpable_only) THEN
694 l_onhand_source_where := '
695 AND Nvl(sub.inventory_atp_code, 1) = 1';
696 ELSIF (p_onhand_source = g_nettable_only) THEN
697 l_onhand_source_where := '
698 AND Nvl(sub.availability_type, 1) = 1';
699 ELSE --do nothing if g_all_subs
700 l_onhand_source_where := NULL;
701 END IF;
702
703 --bug 1384720 - performanc improvements
704 -- need 2 lot selects - one for inner query, one for outer
705 IF p_is_lot_control THEN
706 l_lot_select := '
707 , x.lot_number lot_number ';
708 l_lot_select2 := '
709 , lot.expiration_date lot_expiration_date';
710 l_lot_from := '
711 , mtl_lot_numbers lot';
712 l_lot_where := '
713 AND x.organization_id = lot.organization_id (+)
714 AND x.inventory_item_id = lot.inventory_item_id (+)
715 AND x.lot_number = lot.lot_number (+) ';
716
717 -- invConv changes begin
718 -- odab added the grade in the query :
719 IF (p_grade_code IS NOT NULL)
720 THEN
721 l_lot_where := l_lot_where || ' AND lot.grade_code = :grade_code ';
722 END IF;
723 -- invConv changes end
724
725 l_lot_group := '
726 , x.lot_number ';
727 ELSE
728 l_lot_select := '
729 , NULL lot_number';
730 l_lot_select2 := '
731 , To_date(NULL) lot_expiration_date';
732 l_lot_from := NULL;
733 l_lot_where := NULL;
734 l_lot_group := NULL;
735 END IF;
736
737 IF p_is_lot_control AND p_lot_expiration_date IS NOT NULL THEN
738 l_lot_expiration_where := '
739 AND (lot.expiration_date IS NULL OR
740 lot.expiration_date > :lot_expiration_date) ';
741 ELSE
742 l_lot_expiration_where := NULL;
743 END IF;
744
745 --Bug 1830809 - If revision control is passed in a No, set
746 -- revision to be NULL.
747 IF p_is_revision_control THEN
748 l_revision_select := '
749 , x.revision revision';
750 ELSE
751 l_revision_select := '
752 , NULL revision';
753 END IF;
754
755
756 --bug 1384720
757 -- Moved group by statement into subquery. This minimizes
758 -- the number of joins to the lot and sub tables.
759 l_stmt := '
760 SELECT
761 x.organization_id organization_id
762 , x.inventory_item_id inventory_item_id
763 , x.revision revision
764 , x.lot_number lot_number '
765 || l_lot_select2 || '
766 , x.subinventory_code subinventory_code
767 , sub.reservable_type reservable_type
768 , x.locator_id locator_id
769 , x.primary_quantity primary_quantity
770 , x.secondary_quantity secondary_quantity
771 , x.date_received date_received
772 , x.quantity_type quantity_type
773 , x.cost_group_id cost_group_id
774 , x.containerized containerized
775 , x.planning_organization_id planning_organization_id
776 , x.owning_organization_id owning_organization_id
777 FROM (
778 SELECT
779 x.organization_id organization_id
780 , x.inventory_item_id inventory_item_id '
781 || l_revision_select || l_lot_select || '
782 , x.subinventory_code subinventory_code
783 , x.locator_id locator_id
784 , SUM(x.primary_quantity) primary_quantity
785 , SUM(x.secondary_quantity) secondary_quantity
786 , MIN(x.date_received) date_received
787 , x.quantity_type quantity_type
788 , x.cost_group_id cost_group_id
789 , x.containerized containerized
790 , x.planning_organization_id planning_organization_id
791 , x.owning_organization_id owning_organization_id
792 FROM ('
793 || l_onhand_stmt || '
794 ) x
798 x.organization_id, x.inventory_item_id, x.revision '
795 WHERE x.organization_id = :organization_id
796 AND x.inventory_item_id = :inventory_item_id
797 GROUP BY
799 || l_lot_group || '
800 , x.subinventory_code, x.locator_id
801 , x.quantity_type, x.cost_group_id, x.containerized
802 , x.planning_organization_id, x.owning_organization_id
803 ) x
804 , mtl_secondary_inventories sub '
805 || l_lot_from || '
806 WHERE
807 x.organization_id = sub.organization_id (+)
808 AND x.subinventory_code = sub.secondary_inventory_name (+) '
809 || l_lot_where || l_lot_expiration_where || l_asset_sub_where
810 || l_onhand_source_where ;
811
812 x_return_status := l_return_status;
813 x_sql_statement := l_stmt;
814
815 -- This prints the above SQL
816 /*dbms_output.put_line(x_return_status);
817 dbms_output.put_line('1'||l_lot_group);
818 dbms_output.put_line('2'||l_lot_from);
819 dbms_output.put_line('3'||l_lot_where);
820 dbms_output.put_line('4'||l_asset_sub_where);
821 dbms_output.put_line('5'||l_onhand_source_where);
822 dbms_output.put_line('6'||l_lot_expiration_where);
823
824
825 dbms_output.enable(5000000);
826 FOR p_n IN 1..length(x_sql_statement) LOOP
827 p_v := Substr( x_sql_statement,p_n,1);
828 IF p_v = Chr(10) THEN
829 dbms_output.new_line;
830 ELSE
831 dbms_output.put(p_v);
832 END IF;
833 END LOOP;
834 dbms_output.new_line;*/
835
836
837
838 EXCEPTION
839 WHEN OTHERS THEN
840 x_return_status := fnd_api.g_ret_sts_unexp_error;
841 IF (l_debug = 1) THEN
842 inv_log_util.trace('When Others Ex. in build sql','CONSIGNED_VALIDATIONS',9);
843 END IF;
844 END build_sql;
845
846
847 -- Procedure
848 -- build_cursor
849 -- Description
850 -- this procedure calls the build_sql procedure to get the sql statment and
851 -- parse it, bind variables, and return the cursor
852 PROCEDURE build_cursor
853 (
854 x_return_status OUT NOCOPY VARCHAR2
855 , p_organization_id IN NUMBER
856 , p_inventory_item_id IN NUMBER
857 , p_mode IN INTEGER
858 , p_grade_code IN VARCHAR2 -- invConv change
859 , p_demand_source_line_id IN NUMBER
860 , p_is_lot_control IN BOOLEAN
861 , p_asset_sub_only IN BOOLEAN
862 , p_lot_expiration_date IN DATE
863 , p_onhand_source IN NUMBER
864 , p_pick_release IN NUMBER
865 , x_cursor OUT NOCOPY NUMBER
866 , p_is_revision_control IN BOOLEAN
867 ) IS
868 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
869 l_cursor NUMBER;
870 l_sql LONG;
871 l_last_error_pos NUMBER;
872 l_temp_str VARCHAR2(30);
873 l_err VARCHAR2(240);
874 l_pos NUMBER;
875 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
876 BEGIN
877 l_cursor := dbms_sql.open_cursor;
878 IF (l_debug = 1) THEN
879 inv_log_util.trace('Inside Build Cursor','CONSIGNED_VALIDATIONS',9);
880 END IF;
881
882 build_sql
883 ( x_return_status => l_return_status
884 , p_mode => p_mode
885 , p_grade_code => p_grade_code -- invConv change
886 , p_is_lot_control => p_is_lot_control
887 , p_asset_sub_only => p_asset_sub_only
888 , p_lot_expiration_date => p_lot_expiration_date
889 , p_onhand_source => p_onhand_source
890 , p_pick_release => p_pick_release
891 , x_sql_statement => l_sql
892 , p_is_revision_control => p_is_revision_control);
893
894 IF l_return_status <> fnd_api.g_ret_sts_success THEN
895 RAISE fnd_api.g_exc_unexpected_error;
896 END IF;
897
898
899 BEGIN
900 dbms_sql.parse(l_cursor,l_sql,dbms_sql.v7);
901 EXCEPTION
902 WHEN OTHERS THEN
903 l_last_error_pos := dbms_sql.last_error_position();
904 l_temp_str := Substr(l_sql, l_last_error_pos-5, 30);
905 RAISE;
906 END;
907 dbms_sql.bind_variable(l_cursor, ':organization_id', p_organization_id);
908 dbms_sql.bind_variable(l_cursor, ':inventory_item_id', p_inventory_item_id);
909
910 -- invConv changes begin
911 IF (p_grade_code IS NOT NULL AND p_grade_code <> '')
912 THEN
913 dbms_sql.bind_variable(l_cursor, ':grade_code', p_grade_code);
914 END IF;
915 -- invConv changes end
916
917 -- Bug 2824557, Remove the reference to demand_source_line_id
918 -- Because consign quantity does not care of PJM unit numbers
919 /*IF p_mode IN (g_loose_only_mode) OR
920 g_unit_eff_enabled = 'Y' THEN
921 dbms_sql.bind_variable(l_cursor, ':demand_source_line_id'
922 , p_demand_source_line_id);
923 END IF;*/
924
925 IF p_is_lot_control AND p_lot_expiration_date IS NOT NULL THEN
926 dbms_sql.bind_variable(l_cursor, ':lot_expiration_date'
927 , p_lot_expiration_date);
928 END IF;
929 x_cursor := l_cursor;
930 x_return_status := l_return_status;
931
935 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
932 EXCEPTION
933 WHEN OTHERS THEN
934 x_return_status := fnd_api.g_ret_sts_unexp_error ;
936 THEN
937 fnd_msg_pub.add_exc_msg
938 ( g_pkg_name
939 , 'Build_Cursor'
940 );
941 END IF;
942 END build_cursor;
943
944
945 -------------------------------------------------------------------------------
946 -- Procedure --
947 -- populate_consigned_qty_temp --
948 -- --
949 -- Description --
950 -- This is a server side test procedure for build_sql. It calls --
951 -- build_sql with the input values to build a sql statement. Then --
952 -- it execute the statement to print out query result to dbms_output. --
953 -- You should turn on serveroutput to see the output. --
954 -- --
955 -- Input Parameters --
956 -- p_mode --
957 -- equals inv_quantity_tree_pvt.g_loose_mode or --
958 -- inv_quantity_tree_pvt.g_transaction_mode --
959 -- p_organization_id --
960 -- organization_id --
961 -- p_inventory_item_id --
962 -- inventory_item_id --
963 -- p_is_lot_control --
964 -- true or false --
965 -- p_asset_sub_only --
966 -- true or false --
967 -- p_include_suggestion --
968 -- true or false should be true only for pick/put engine --
969 -- p_lot_expiration_date --
970 -- if not null, only consider lots that will not expire before --
971 -- or at the date --
972 -- p_demand_source_type_id --
973 -- demand_source_type_id --
974 -------------------------------------------------------------------------------
975 PROCEDURE populate_consigned_qty_temp
976 (
977 p_organization_id IN NUMBER
978 , p_inventory_item_id IN NUMBER
979 , p_mode IN INTEGER
980 , p_grade_code IN VARCHAR2 -- invConv change
981 , p_is_lot_control IN BOOLEAN
982 , p_is_revision_control IN BOOLEAN
983 , p_asset_sub_only IN BOOLEAN
984 , p_lot_expiration_date IN DATE
985 , p_demand_source_line_id IN NUMBER
986 , p_onhand_source IN NUMBER
987 , p_qty_tree_att IN NUMBER
988 , p_qty_tree_satt IN NUMBER -- invConv change
989 , x_return_status OUT NOCOPY VARCHAR2
990 ) IS
991 l_cursor NUMBER;
992 l_return_status VARCHAR2(1);
993 l_revision VARCHAR2(3);
994 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
995 l_lot_number VARCHAR2(80);
996 l_subinventory_code VARCHAR2(10);
997 l_lot_expiration_date DATE;
998 l_reservable_type NUMBER;
999 l_primary_quantity NUMBER;
1000 l_secondary_quantity NUMBER; -- InvConv change
1001 l_date_received DATE;
1002 l_quantity_type NUMBER;
1003 l_dummy INTEGER;
1004 l_locator_id NUMBER;
1005 l_inventory_item_id NUMBER;
1006 l_organization_id NUMBER;
1007 l_cost_group_id NUMBER;
1008 l_containerized NUMBER;
1009 l_planning_organization_id NUMBER;
1010 l_owning_organization_id NUMBER;
1011 ll_transactable_vmi NUMBER;
1012 ll_transactable_secondary_vmi NUMBER; -- InvConv change
1013 ---- Variabls to get values from cursor
1014 lL_revision VARCHAR2(3);
1015 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1016 lL_lot_number VARCHAR2(80);
1017 lL_subinventory_code VARCHAR2(10);
1018 lL_lot_expiration_date DATE;
1019 ll_reservable_type NUMBER;
1020 ll_primary_quantity NUMBER;
1021 ll_secondary_quantity NUMBER; -- InvConv change
1022 ll_date_received DATE;
1023 ll_quantity_type NUMBER;
1024 ll_locator_id NUMBER;
1025 ll_inventory_item_id NUMBER;
1026 ll_organization_id NUMBER;
1027 ll_cost_group_id NUMBER;
1028 ll_containerized NUMBER;
1029 ll_planning_organization_id NUMBER;
1030 ll_owning_organization_id NUMBER;
1031 --------------------------------------
1032 l_count NUMBER := 0;
1033 l_temp NUMBER := 0;
1034 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1035 BEGIN
1036
1037 build_cursor
1038 ( x_return_status => l_return_status
1039 , p_organization_id => p_organization_id
1040 , p_inventory_item_id => p_inventory_item_id
1041 , p_mode => p_mode
1045 , p_is_revision_control => p_is_revision_control
1042 , p_grade_code => p_grade_code -- invConv change
1043 , p_demand_source_line_id => p_demand_source_line_id
1044 , p_is_lot_control => p_is_lot_control
1046 , p_asset_sub_only => p_asset_sub_only
1047 , p_lot_expiration_date => p_lot_expiration_date
1048 , p_onhand_source => p_onhand_source
1049 , p_pick_release => 0
1050 , x_cursor => l_cursor
1051 );
1052
1053 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1054 l_return_status:= fnd_api.g_ret_sts_error;
1055 RAISE fnd_api.g_exc_unexpected_error;
1056 END IF;
1057
1058
1059
1060 dbms_sql.define_column(l_cursor, 1,l_organization_id);
1061 dbms_sql.define_column(l_cursor, 2,l_inventory_item_id);
1062 dbms_sql.define_column(l_cursor, 3,l_revision,3);
1063 dbms_sql.define_column(l_cursor, 4,l_lot_number,30);
1064 dbms_sql.define_column(l_cursor, 5,l_lot_expiration_date);
1065 dbms_sql.define_column(l_cursor, 6,l_subinventory_code,10);
1066 dbms_sql.define_column(l_cursor, 7,l_reservable_type);
1067 dbms_sql.define_column(l_cursor, 8,l_locator_id);
1068 dbms_sql.define_column(l_cursor, 9,l_primary_quantity);
1069 dbms_sql.define_column(l_cursor,10,l_secondary_quantity); -- invConv change
1070 dbms_sql.define_column(l_cursor,11,l_date_received); -- invConv renamed order number
1071 dbms_sql.define_column(l_cursor,12,l_quantity_type); -- invConv renamed order number
1072 dbms_sql.define_column(l_cursor,13,l_cost_group_id); -- invConv renamed order number
1073 dbms_sql.define_column(l_cursor,14,l_containerized); -- invConv renamed order number
1074 dbms_sql.define_column(l_cursor,15,l_planning_organization_id); -- invConv renamed order number
1075 dbms_sql.define_column(l_cursor,16,l_owning_organization_id); -- invConv renamed order number
1076
1077 l_dummy := dbms_sql.execute(l_cursor);
1078 LOOP
1079 IF dbms_sql.fetch_rows(l_cursor) = 0 THEN
1080 EXIT;
1081 END IF;
1082
1083 l_count := l_count + 1;
1084 ll_transactable_vmi:= 0;
1085 ll_transactable_secondary_vmi:= 0; -- invConv change
1086
1087 dbms_sql.column_value(l_cursor, 1,ll_organization_id);
1088 dbms_sql.column_value(l_cursor, 2,ll_inventory_item_id);
1089 dbms_sql.column_value(l_cursor, 3,ll_revision);
1090 dbms_sql.column_value(l_cursor, 4,ll_lot_number);
1091 dbms_sql.column_value(l_cursor, 5,ll_lot_expiration_date);
1092 dbms_sql.column_value(l_cursor, 6,ll_subinventory_code);
1093 dbms_sql.column_value(l_cursor, 7,ll_reservable_type);
1094 dbms_sql.column_value(l_cursor, 8,ll_locator_id);
1095 dbms_sql.column_value(l_cursor, 9,ll_primary_quantity);
1096 dbms_sql.column_value(l_cursor,10,ll_secondary_quantity); -- InvConv change
1097 dbms_sql.column_value(l_cursor,11,ll_date_received); -- invConv renamed order number
1098 dbms_sql.column_value(l_cursor,12,ll_quantity_type); -- invConv renamed order number
1099 dbms_sql.column_value(l_cursor,13,ll_cost_group_id); -- invConv renamed order number
1103
1100 dbms_sql.column_value(l_cursor,14,ll_containerized); -- invConv renamed order number
1101 dbms_sql.column_value(l_cursor,15,ll_planning_organization_id); -- invConv renamed order number
1102 dbms_sql.column_value(l_cursor,16,ll_owning_organization_id); -- invConv renamed order number
1104 IF (p_qty_tree_att<=ll_primary_quantity)THEN
1105 ll_transactable_vmi:=p_qty_tree_att;
1106 ll_transactable_secondary_vmi:=p_qty_tree_satt; -- InvConv change
1107 ELSE
1108 ll_transactable_vmi:=ll_primary_quantity;
1109 ll_transactable_secondary_vmi:=ll_secondary_quantity; -- InvConv change
1110 END IF;
1111
1112 INSERT INTO mtl_consigned_qty_temp (organization_id,
1113 inventory_item_id,
1114 revision,
1115 lot_number,
1116 lot_expiration_date,
1117 subinventory_code,
1118 reservable_type,
1119 locator_id,
1120 grade_code, -- invConv change
1121 primary_quantity,
1122 secondary_quantity, -- invConv change
1123 transactable_vmi,
1124 transactable_secondary_vmi, -- invConv change
1125 date_received,
1126 quantity_type,
1127 cost_group_id,
1128 containerized,
1129 planning_organization_id,
1130 owning_organization_id)
1131 VALUES
1132 (
1133 ll_organization_id,
1134 ll_inventory_item_id,
1135 ll_revision,
1136 ll_lot_number,
1137 ll_lot_expiration_date,
1138 ll_subinventory_code,
1139 ll_reservable_type,
1140 ll_locator_id,
1141 p_grade_code, -- invConv change
1142 ll_primary_quantity,
1143 ll_secondary_quantity, -- invConv change
1144 ll_transactable_vmi,
1145 ll_transactable_secondary_vmi, -- invConv change
1146 ll_date_received,
1147 ll_quantity_type,
1148 ll_cost_group_id,
1149 ll_containerized,
1150 ll_planning_organization_id,
1151 ll_owning_organization_id);
1152 END LOOP;
1153 dbms_sql.close_cursor(l_cursor);
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 IF (l_debug = 1) THEN
1157 inv_log_util.trace('When others Ex. in Inserting in temp table','CONSIGNED_VALIDATIONS',9);
1158 END IF;
1159 END populate_consigned_qty_temp;
1160
1161 /* invconv changes begin : this procedure is now obsolete
1162 and replaced by check_is_reservable :
1163 -- Procedure
1164 -- check_is_reservable_sub
1165 -- Description
1166 -- check from db tables whether the sub specified in
1167 -- the input is a reservable sub or not.
1168 PROCEDURE check_is_reservable_sub
1169 ( x_return_status OUT NOCOPY VARCHAR2
1170 , p_organization_id IN VARCHAR2
1171 , p_subinventory_code IN VARCHAR2
1172 , x_is_reservable_sub OUT NOCOPY BOOLEAN
1173 ) IS
1174 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1175 l_reservable_type NUMBER;
1176 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1177 BEGIN
1178 SELECT reservable_type INTO l_reservable_type
1179 FROM mtl_secondary_inventories
1180 WHERE organization_id = p_organization_id
1181 AND secondary_inventory_name = p_subinventory_code;
1182 IF (l_reservable_type = 1) THEN
1183 x_is_reservable_sub := TRUE;
1184 ELSE
1185 x_is_reservable_sub := FALSE;
1186 END IF;
1187
1188 x_return_status := l_return_status;
1189
1190 EXCEPTION
1191
1192 WHEN OTHERS THEN
1193 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1194 IF (l_debug = 1) THEN
1195 inv_log_util.trace('check_is_reservable_sub','CONSIGNED_VALIDATIONS',9);
1196 END IF;
1197
1198 END check_is_reservable_sub;
1199 invConv changes end. */
1200
1201 -- invConv change begin : new procedure in replacement of check_is_reservable_sub:
1202 -- Procedure
1203 -- check_is_reservable
1204 -- Description
1205 -- check from db tables whether the sub specified in
1206 -- the input is a reservable sub or not.
1207 PROCEDURE check_is_reservable
1208 ( x_return_status OUT NOCOPY VARCHAR2
1209 , p_node_level IN INTEGER DEFAULT NULL
1210 , p_inventory_item_id IN NUMBER
1211 , p_organization_id IN NUMBER
1212 , p_subinventory_code IN VARCHAR2
1213 , p_locator_id IN NUMBER
1214 , p_lot_number IN VARCHAR2
1215 , x_is_reservable OUT NOCOPY BOOLEAN
1216 ) IS
1217
1218 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1219 l_reservable_type NUMBER;
1220
1221 CURSOR is_RSV_subInv( org_id IN NUMBER
1222 , subinv IN VARCHAR2) IS
1223 SELECT reservable_type
1224 FROM mtl_secondary_inventories
1225 WHERE organization_id = org_id
1226 AND secondary_inventory_name = subinv;
1227
1228 --SELECT TO_NUMBER(NVL(attribute1, '0'))
1229 CURSOR is_RSV_loct( org_id IN NUMBER
1230 , loct_id IN NUMBER) IS
1231 SELECT '1'
1232 FROM mtl_item_locations mil
1233 WHERE mil.status_id IN
1234 (SELECT mms.status_id
1235 FROM mtl_material_statuses mms
1236 WHERE NVL(mms.attribute1, '1') = '1'
1237 AND mms.locator_control = 1)
1238 AND mil.organization_id = org_id
1239 AND mil.inventory_location_id = loct_id;
1240
1244 , lot IN VARCHAR2) IS
1241 --SELECT TO_NUMBER(NVL(attribute1, '0'))
1242 CURSOR is_RSV_lot( org_id IN NUMBER
1243 , item_id IN NUMBER
1245 SELECT '1'
1246 FROM mtl_lot_numbers mln
1247 WHERE mln.status_id IN
1248 (SELECT mms.status_id
1249 FROM mtl_material_statuses mms
1250 WHERE NVL(mms.attribute1, '1') = '1'
1251 AND mms.lot_control = 1)
1252 AND mln.inventory_item_id = item_id
1253 AND mln.organization_id = org_id
1254 AND mln.lot_number = lot;
1255
1256 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1257
1258 BEGIN
1259 IF (l_debug = 1) THEN
1260 inv_log_util.trace('in check_is_reservable. node_level='||p_node_level||', subinv='||p_subinventory_code||', loct='||p_locator_id||', lot='||p_lot_number, 'CONSIGNED_VALIDATIONS',9);
1261 END IF;
1262
1263 -- lot level = 4
1264 -- subinv level = 5
1265 -- locator level = 6
1266 IF (NVL(p_node_level,0) = 4)
1267 OR (p_lot_number IS NOT NULL)
1268 THEN
1269 OPEN is_RSV_lot( p_organization_id, p_inventory_item_id, p_lot_number);
1270 FETCH is_RSV_lot
1271 INTO l_reservable_type;
1272 IF (is_RSV_lot%NOTFOUND)
1273 THEN
1274 l_reservable_type := '0';
1275 END IF;
1276 CLOSE is_RSV_lot;
1277
1278 IF (l_debug = 1) THEN
1279 inv_log_util.trace('in RSV reservable='||l_reservable_type||', for lot='||p_lot_number, 'CONSIGNED_VALIDATIONS',9);
1280 END IF;
1281
1282 ELSIF (NVL(p_node_level, 0) = 6)
1283 OR (p_locator_id IS NOT NULL)
1284 THEN
1285 OPEN is_RSV_loct( p_organization_id, p_locator_id);
1286 FETCH is_RSV_loct
1287 INTO l_reservable_type;
1288 IF (is_RSV_loct%NOTFOUND)
1289 THEN
1290 l_reservable_type := '0';
1291 END IF;
1292 CLOSE is_RSV_loct;
1293
1294 IF (l_debug = 1) THEN
1295 inv_log_util.trace('in RSV reservable='||l_reservable_type||', for locator='||p_locator_id, 'CONSIGNED_VALIDATIONS',9);
1296 END IF;
1297
1298 ELSIF (NVL(p_node_level, 0) = 5)
1299 OR (p_subinventory_code IS NOT NULL)
1300 THEN
1301 OPEN is_RSV_subInv( p_organization_id, p_subinventory_code);
1302 FETCH is_RSV_subInv
1303 INTO l_reservable_type;
1304 CLOSE is_RSV_subInv;
1305
1306 IF (l_debug = 1) THEN
1307 inv_log_util.trace('in RSV reservable='||l_reservable_type||', for subInv='||p_subinventory_code, 'CONSIGNED_VALIDATIONS',9);
1308 END IF;
1309
1310 END IF;
1311
1312 IF (l_reservable_type = 1) THEN
1313 x_is_reservable := TRUE;
1314 IF (l_debug = 1) THEN
1315 inv_log_util.trace('in RSV reservable=TRUE', 'CONSIGNED_VALIDATIONS',9);
1316 END IF;
1317 ELSE
1318 x_is_reservable := FALSE;
1319 IF (l_debug = 1) THEN
1320 inv_log_util.trace('in RSV reservable=FALSE', 'CONSIGNED_VALIDATIONS',9);
1321 END IF;
1322 END IF;
1323
1324 x_return_status := l_return_status;
1325
1326 EXCEPTION
1327
1328 WHEN OTHERS THEN
1329 IF (l_debug = 1) THEN
1330 inv_log_util.trace('in check_is_reservable, OTHERS Error='||SQLERRM, 'CONSIGNED_VALIDATIONS',9);
1331 END IF;
1332 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1333
1334 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1335 THEN
1336 fnd_msg_pub.add_exc_msg
1337 ( g_pkg_name
1338 , 'Check_Is_Reservable'
1339 );
1340 END IF;
1341
1342 END check_is_reservable;
1343 -- invConv changes end.
1344
1345 -- This API is to be called to delete the existing
1346 -- cache of the global temporary table.
1347 -- This will also delete the cache of the quantity tree
1348 PROCEDURE clear_vmi_cache
1349 IS
1350 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1351 BEGIN
1352 inv_quantity_tree_pub.clear_quantity_cache;
1353 DELETE FROM mtl_consigned_qty_temp;
1354 END clear_vmi_cache;
1355
1356 /********************
1357 * Public API *
1358 ********************/
1359
1360 /*------------------------*
1361 * GET_CONSIGNED_QUANTITY *
1362 *------------------------*/
1363 /** This API will return VMI/consigned Quantity */
1364
1365
1366 PROCEDURE GET_CONSIGNED_QUANTITY(
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 x_return_msg OUT NOCOPY VARCHAR2,
1369 p_tree_mode IN NUMBER,
1370 p_organization_id IN NUMBER,
1371 p_owning_org_id IN NUMBER,
1372 p_planning_org_id IN NUMBER,
1373 p_inventory_item_id IN NUMBER,
1374 p_is_revision_control IN VARCHAR2,
1375 p_is_lot_control IN VARCHAR2,
1376 p_is_serial_control IN VARCHAR2,
1377 p_revision IN VARCHAR2,
1378 p_lot_number IN VARCHAR2,
1379 p_lot_expiration_date IN DATE,
1380 p_subinventory_code IN VARCHAR2,
1381 p_locator_id IN NUMBER,
1382 p_source_type_id IN NUMBER,
1383 p_demand_source_line_id IN NUMBER,
1384 p_demand_source_header_id IN NUMBER,
1385 p_demand_source_name IN VARCHAR2,
1386 p_onhand_source IN NUMBER,
1387 p_cost_group_id IN NUMBER,
1388 p_query_mode IN NUMBER,
1389 x_qoh OUT NOCOPY NUMBER,
1390 x_att OUT NOCOPY NUMBER) IS
1391
1392 l_sqoh NUMBER; -- invConv change
1393 l_satt NUMBER; -- invConv change
1394
1398 -- invConv changes begin:
1395 BEGIN
1396 debug_print('entering old get_consigned_quantity');
1397
1399 -- Calling the new get_consigned_quantity:
1400 INV_CONSIGNED_VALIDATIONS.get_consigned_quantity
1401 ( x_return_status => x_return_status
1402 , x_return_msg => x_return_msg
1403 , p_tree_mode => p_tree_mode
1404 , p_organization_id => p_organization_id
1405 , p_owning_org_id => p_owning_org_id
1406 , p_planning_org_id => p_planning_org_id
1407 , p_inventory_item_id => p_inventory_item_id
1408 , p_is_revision_control => p_is_revision_control
1409 , p_is_lot_control => p_is_lot_control
1410 , p_is_serial_control => p_is_serial_control
1411 , p_revision => p_revision
1412 , p_lot_number => p_lot_number
1413 , p_lot_expiration_date => p_lot_expiration_date
1414 , p_subinventory_code => p_subinventory_code
1415 , p_locator_id => p_locator_id
1416 , p_grade_code => NULL -- invConv change
1417 , p_source_type_id => p_source_type_id
1418 , p_demand_source_line_id => p_demand_source_line_id
1419 , p_demand_source_header_id => p_demand_source_header_id
1420 , p_demand_source_name => p_demand_source_name
1421 , p_onhand_source => p_onhand_source
1422 , p_cost_group_id => p_cost_group_id
1423 , p_query_mode => p_query_mode
1424 , x_qoh => x_qoh
1425 , x_att => x_att
1426 , x_sqoh => l_sqoh -- invConv change
1427 , x_satt => l_satt); -- invConv change
1428
1429 END get_consigned_quantity;
1430
1431
1432 -- invConv changes begin:
1433 -- Overloaded procedure (entry point).
1434 PROCEDURE get_consigned_quantity(
1435 x_return_status OUT NOCOPY VARCHAR2,
1436 x_return_msg OUT NOCOPY VARCHAR2,
1437 p_tree_mode IN NUMBER,
1438 p_organization_id IN NUMBER,
1439 p_owning_org_id IN NUMBER,
1440 p_planning_org_id IN NUMBER,
1441 p_inventory_item_id IN NUMBER,
1442 p_is_revision_control IN VARCHAR2,
1443 p_is_lot_control IN VARCHAR2,
1444 p_is_serial_control IN VARCHAR2,
1445 p_revision IN VARCHAR2,
1446 p_lot_number IN VARCHAR2,
1447 p_lot_expiration_date IN DATE,
1448 p_subinventory_code IN VARCHAR2,
1449 p_locator_id IN NUMBER,
1450 p_grade_code IN VARCHAR2, -- invConv change
1451 p_source_type_id IN NUMBER,
1452 p_demand_source_line_id IN NUMBER,
1453 p_demand_source_header_id IN NUMBER,
1454 p_demand_source_name IN VARCHAR2,
1455 p_onhand_source IN NUMBER,
1456 p_cost_group_id IN NUMBER,
1457 p_query_mode IN NUMBER,
1458 x_qoh OUT NOCOPY NUMBER,
1459 x_att OUT NOCOPY NUMBER,
1460 x_sqoh OUT NOCOPY NUMBER, -- invConv change
1461 x_satt OUT NOCOPY NUMBER) IS -- invConv change
1462
1463 l_msg_count VARCHAR2(100);
1464 l_msg_data VARCHAR2(1000);
1465 l_is_revision_control BOOLEAN := FALSE;
1466 l_is_lot_control BOOLEAN := FALSE;
1467 l_is_serial_control BOOLEAN := FALSE;
1468 l_tree_mode NUMBER;
1469 l_table_count NUMBER := 0;
1470
1471 l_qoh NUMBER;
1472 l_rqoh NUMBER;
1473 l_qr NUMBER;
1474 l_qs NUMBER;
1475 l_atr NUMBER;
1476 l_att NUMBER;
1477 l_vcoh NUMBER;
1478 l_sqoh NUMBER; -- invConv change
1479 l_srqoh NUMBER; -- invConv change
1480 l_sqr NUMBER; -- invConv change
1481 l_sqs NUMBER; -- invConv change
1482 l_satr NUMBER; -- invConv change
1483 l_satt NUMBER; -- invConv change
1484 l_svcoh NUMBER; -- invConv change
1485 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1486 BEGIN
1487
1488 IF (l_debug = 1) THEN
1489 debug_print('****** GET_CONSIGNED_QUANTITIES *******','CONSIGNED_VALIDATIONS',9);
1490 debug_print(' Org, Owning_org, planning_org='|| p_organization_id ||','
1491 || p_owning_org_id ||','||p_planning_org_id,'CONSIGNED_VALIDATIONS',9);
1492 debug_print(' Item, Is Rev, Lot, Serial controlled: '||p_inventory_item_id|| ','||
1493 p_is_revision_control ||','|| p_is_lot_control ||','|| p_is_serial_control,'CONSIGNED_VALIDATIONS',9);
1494 debug_print(' Rev, Lot, LotExpDate: '|| p_revision ||','||p_lot_number ||','|| p_lot_expiration_date,'CONSIGNED_VALIDATIONS',9);
1495 debug_print(' grade='||p_grade_code||'...','CONSIGNED_VALIDATIONS',9);
1496 debug_print(' Sub, Loc: '||p_subinventory_code||','||p_locator_id,'CONSIGNED_VALIDATIONS',9);
1497 debug_print(' SourceTypeID, DemdSrcLineID, DemdSrcHdrID, DemdSrcName: ' ||
1498 p_source_type_id ||',' ||p_demand_source_line_id || ','||
1499 p_demand_source_header_id || ',' || p_demand_source_name,'CONSIGNED_VALIDATIONS',9);
1500 debug_print(' OnhandSource, CstGroupID, QueryMode: '|| p_onhand_source || ','||
1501 p_cost_group_id ||',' ||p_query_mode,'CONSIGNED_VALIDATIONS',9);
1502 END IF;
1503
1504 x_return_status:= fnd_api.g_ret_sts_success;
1505
1506 IF p_tree_mode IS NULL THEN
1507 l_tree_mode := INV_Quantity_Tree_PUB.g_loose_only_mode;
1508 ELSE l_tree_mode := p_tree_mode;
1509 END IF ;
1510
1511 -- validate demand source info
1512 IF p_tree_mode IN (g_transaction_mode, g_loose_only_mode) THEN
1513 IF p_source_type_id IS NULL THEN
1514 fnd_message.set_name('INV', 'INV-MISSING DEMAND SOURCE TYPE');
1515 fnd_msg_pub.ADD;
1519
1516 x_return_msg := fnd_message.get;
1517 RAISE fnd_api.g_exc_error;
1518 END IF;
1520 IF p_demand_source_header_id IS NULL THEN
1521 IF p_demand_source_name IS NULL THEN
1522 fnd_message.set_name('INV', 'INV-MISSING DEMAND SRC HEADER');
1523 fnd_msg_pub.ADD;
1524 x_return_msg := fnd_message.get;
1525 RAISE fnd_api.g_exc_error;
1526 END IF;
1527 END IF;
1528
1529 IF p_demand_source_header_id IS NULL
1530 AND p_demand_source_line_id IS NOT NULL THEN
1531 fnd_message.set_name('INV', 'INV-MISSING DEMAND SRC HEADER');
1532 fnd_msg_pub.ADD;
1533 x_return_msg := fnd_message.get;
1534 RAISE fnd_api.g_exc_error;
1535 END IF;
1536 END IF;
1537
1538 IF (Upper(p_is_revision_control) = 'TRUE') OR (Upper(p_is_revision_control)=fnd_api.g_true) THEN
1539 l_is_revision_control := TRUE;
1540 END IF;
1541
1542 IF (Upper(p_is_lot_control) = 'TRUE') OR (Upper(p_is_lot_control)=fnd_api.g_true) THEN
1543 l_is_lot_control := TRUE;
1544 END IF;
1545
1546 IF (Upper(p_is_serial_control) = 'TRUE') OR (Upper(p_is_serial_control) = fnd_api.g_true) THEN
1547 l_is_serial_control := TRUE;
1548 END IF;
1549
1550 /* Validate input parameters */
1551 IF (p_inventory_item_id IS NULL) THEN
1552 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
1553 fnd_msg_pub.ADD;
1554 x_return_msg := fnd_message.get;
1555 RAISE fnd_api.g_exc_unexpected_error;
1556 END IF ;
1557
1558 IF (p_query_mode = G_TXN_MODE) THEN
1559 IF (p_owning_org_id IS NULL AND p_planning_org_id IS NULL) THEN
1560 fnd_message.set_name('INV', 'INV_OWN_PLAN_ORG_REQUIRED');
1561 fnd_msg_pub.ADD;
1562 x_return_msg := fnd_message.get;
1563 RAISE fnd_api.g_exc_unexpected_error;
1564 END IF ;
1565 ELSIF (p_query_mode = G_REG_MODE) THEN
1566 IF (p_owning_org_id IS NULL) THEN
1567 fnd_message.set_name('INV', 'INV_OWN_ORG_REQUIRED');
1568 fnd_msg_pub.ADD;
1569 x_return_msg := fnd_message.get;
1570 RAISE fnd_api.g_exc_unexpected_error;
1571 END IF ;
1572 END IF;
1573
1574 IF (l_debug = 1) THEN
1575 inv_log_util.trace('Done validation','CONSIGNED_VALIDATIONS',9);
1576 END IF;
1577 IF (p_query_mode = G_REG_MODE) THEN
1578
1579 IF (l_debug = 1) THEN
1580 inv_log_util.trace('Transfer regular to consigned','CONSIGNED_VALIDATIONS',9);
1581 END IF;
1582 -- invConv changes begin : added secondary quantities :
1583 SELECT Nvl(sum(primary_transaction_quantity),0)
1584 , Nvl(sum(secondary_transaction_quantity),0)
1585 INTO x_att
1586 , x_satt
1587 FROM mtl_onhand_quantities_detail
1588 WHERE owning_organization_id = organization_id
1589 AND organization_id = p_organization_id
1590 AND owning_organization_id <> p_owning_org_id
1591 AND inventory_item_id = p_inventory_item_id
1592 AND nvl(revision,'@@@') = nvl(p_revision, nvl(revision,'@@@'))
1593 AND nvl(lot_number, '@@@') = nvl(p_lot_number, nvl(lot_number, '@@@'))
1594 AND subinventory_code = nvl(p_subinventory_code, subinventory_code)
1595 AND nvl(locator_id, -999) = nvl(p_locator_id, nvl(locator_id, -999))
1596 AND nvl(cost_group_id, -999) = nvl(p_cost_group_id, nvl(cost_group_id, -999));
1597
1598 x_qoh := x_att;
1599 x_sqoh := x_satt; -- invConv change
1600 IF (l_debug = 1) THEN
1601 inv_log_util.trace('Got qty, x_qoh=x_att='||x_att,'CONSIGNED_VALIDATIONS',9);
1602 END IF;
1603
1604 RETURN;
1605 END IF;
1606
1607
1608 --SELECT COUNT(*)INTO l_table_count FROM mtl_consigned_qty_temp
1609 --WHERE inventory_item_id = p_inventory_item_id
1610 --AND organization_id = p_organization_id;
1611 --Use Exists to check existance
1612 l_table_count := 0;
1613 BEGIN
1614 SELECT 1 INTO l_table_count FROM dual
1615 WHERE EXISTS (SELECT 1 FROM mtl_consigned_qty_temp
1616 WHERE inventory_item_id = p_inventory_item_id
1617 AND organization_id = p_organization_id);
1618 EXCEPTION
1619 WHEN others THEN
1620 l_table_count:=0;
1621 END;
1622
1623 -- Clear the already existing cache only if for this item and org no table
1624 -- exists.
1625 IF (l_table_count = 0) THEN
1626 IF (l_debug = 1) THEN
1627 inv_log_util.trace('Going to build SQL','CONSIGNED_VALIDATIONS',9);
1628 END IF;
1629
1630 populate_consigned_qty_temp(
1631 p_organization_id => p_organization_id
1632 , p_inventory_item_id => p_inventory_item_id
1633 , p_mode => l_tree_mode
1634 , p_grade_code => p_grade_code -- invConv change
1635 , p_is_lot_control => l_is_lot_control
1636 , p_is_revision_control => l_is_revision_control
1637 , p_asset_sub_only => null
1638 , p_lot_expiration_date => null
1639 , p_demand_source_line_id => p_demand_source_line_id
1640 , p_onhand_source => p_onhand_source
1641 , p_qty_tree_att => x_att
1642 , p_qty_tree_satt => x_satt -- invConv change
1643 , x_return_status => x_return_status) ;
1644
1645 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1646 IF (l_debug = 1) THEN
1647 inv_log_util.trace('populate consigned temp Failed','CONSIGNED_VALIDATIONS',9);
1648 END IF;
1649 RAISE fnd_api.g_exc_unexpected_error;
1650 END IF;
1651 END IF;
1652
1656 inv_consigned_validations.query_vmi_consigned(
1653 IF (l_debug = 1) THEN
1654 inv_log_util.trace('Query consigned temp table for l_vcoh','CONSIGNED_VALIDATIONS',9);
1655 END IF;
1657 x_return_status => x_return_status
1658 , x_msg_count => l_msg_count
1659 , x_msg_data => l_msg_data
1660 , p_organization_id => p_organization_id
1661 , p_planning_org_id => p_planning_org_id
1662 , p_owning_org_id => p_owning_org_id
1663 , p_inventory_item_id => p_inventory_item_id
1664 , p_tree_mode => l_tree_mode
1665 , p_is_revision_control => l_is_revision_control
1666 , p_is_lot_control => l_is_lot_control
1667 , p_is_serial_control => l_is_serial_control
1668 , p_demand_source_line_id => p_demand_source_line_id
1669 , p_revision => p_revision
1670 , p_lot_number => p_lot_number
1671 , p_lot_expiration_date => NULL
1672 , p_subinventory_code => p_subinventory_code
1673 , p_locator_id => p_locator_id
1674 , p_cost_group_id => p_cost_group_id
1675 , x_qoh => l_vcoh
1676 , x_sqoh => l_svcoh -- invConv change
1677 );
1678
1679 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1680 IF (l_debug = 1) THEN
1681 inv_log_util.trace('CONSIGNED_VMI table query Failed'||l_msg_data,'CONSIGNED_VALIDATIONS',9);
1682 END IF;
1683 RAISE fnd_api.g_exc_unexpected_error;
1684 END IF;
1685
1686 IF (l_debug = 1) THEN
1687 inv_log_util.trace('Got l_vcoh='||l_vcoh,'CONSIGNED_VALIDATIONS',9);
1688 END IF;
1689
1690 IF (p_query_mode = G_TXN_MODE) THEN
1691
1692 -- Call the quantity tree
1693 -- This API calls the public qty tree api to create and query the tree
1694 --togethor. The created tree is stored in the memory as a PL/SQL table.
1695 IF (l_debug = 1) THEN
1696 inv_log_util.trace('Transaction Mode, calling quantity tree','CONSIGNED_VALIDATIONS',9);
1697 END IF;
1698 inv_quantity_tree_pub.query_quantities(
1699 p_api_version_number => 1.0
1700 , p_init_msg_lst => fnd_api.g_false
1701 , x_return_status => x_return_status
1702 , x_msg_count => l_msg_count
1703 , x_msg_data => l_msg_data
1704 , p_organization_id => p_organization_id
1705 , p_inventory_item_id => p_inventory_item_id
1706 , p_tree_mode => l_tree_mode
1707 , p_grade_code => p_grade_code -- invConv change
1708 , p_is_revision_control => l_is_revision_control
1709 , p_is_lot_control => l_is_lot_control
1710 , p_is_serial_control => l_is_serial_control
1711 , p_demand_source_type_id => p_source_type_id
1712 , p_demand_source_line_id => p_demand_source_line_id
1713 , p_demand_source_header_id=> p_demand_source_header_id
1714 , p_demand_source_name => p_demand_source_name
1715 , p_revision => p_revision
1716 , p_lot_number => p_lot_number
1717 , p_lot_expiration_date => NULL --for bug# 2219136
1718 , p_subinventory_code => p_subinventory_code
1719 , p_locator_id => p_locator_id
1720 , p_cost_group_id => p_cost_group_id
1721 , x_qoh => l_qoh
1722 , x_rqoh => l_rqoh
1723 , x_qr => l_qr
1724 , x_qs => l_qs
1725 , x_att => l_att
1726 , x_atr => l_atr
1727 , x_sqoh => l_sqoh -- invConv change
1728 , x_srqoh => l_srqoh -- invConv change
1729 , x_sqr => l_sqr -- invConv change
1730 , x_sqs => l_sqs -- invConv change
1731 , x_satt => l_satt -- invConv change
1732 , x_satr => l_satr -- invConv change
1733 );
1734
1735 -- If the qty tree returns and error raise an exception.
1736 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1737 IF (l_debug = 1) THEN
1738 inv_log_util.trace('Qty Tree Failed'||l_msg_data,'CONSIGNED_VALIDATIONS',9);
1739 END IF;
1740 x_return_msg:= l_msg_data;
1741 RAISE fnd_api.g_exc_unexpected_error;
1742 END IF;
1743
1744 IF (l_debug = 1) THEN
1745 debug_print('Called qty tree, l_qoh='||l_qoh||', sqoh='||l_sqoh||',l_att='||l_att||', satt='||l_satt,'CONSIGNED_VALIDATIONS',9);
1746 debug_print('Comparing with l_vcoh='||l_vcoh||', svcoh='||l_svcoh,'CONSIGNED_VALIDATIONS',9);
1747 END IF;
1748 --consign/VMI att is min of qty tree att and vmi/consigned onhand.
1749 IF (l_vcoh <= l_att) THEN
1750 x_att:= l_vcoh;
1751 x_satt:= l_svcoh; -- invConv change
1752 ELSE
1753 x_att:= l_att;
1754 x_satt:= l_satt; -- invConv change
1755 END IF;
1756 x_qoh := l_vcoh;
1757 x_sqoh := l_svcoh; -- invConv change
1758
1759 ELSIF (p_query_mode = G_XFR_MODE) THEN
1760 x_att := l_vcoh;
1761 x_qoh := x_att;
1762 x_satt := l_svcoh; -- invConv change
1763 x_sqoh := x_satt; -- invConv change
1764 IF (l_debug = 1) THEN
1765 debug_print('Transfer mode, x_qoh=x_att=l_vcoh='||x_att||', x_satt='||x_satt,'CONSIGNED_VALIDATIONS',9);
1766 END IF;
1767
1768 END IF;
1769
1770 x_return_status:= fnd_api.g_ret_sts_success;
1771 debug_print('Normal end of get_consigned_quantity2.');
1772
1773 EXCEPTION
1774 when others THEN
1775 IF (l_debug = 1) THEN
1779 RETURN;
1776 inv_log_util.trace('When others Exception in get_consigned_quantity','CONSIGNED_VALIDATIONS',9);
1777 END IF;
1778 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1780 END get_consigned_quantity;
1781
1782
1783
1784
1785
1786
1787
1788 -- This API will allow update of the existing temp table.
1789 ---This API needs to be called after a transaction is commited or
1790 -- when moving onto the next line for the same transaction without a
1791 --commit.
1792
1793 PROCEDURE update_consigned_quantities
1794 ( x_return_status OUT NOCOPY varchar2
1795 , x_msg_count OUT NOCOPY varchar2
1796 , x_msg_data OUT NOCOPY varchar2
1797 , p_organization_id IN NUMBER
1798 , p_inventory_item_id IN NUMBER
1799 , p_revision IN VARCHAR2
1800 , p_lot_number IN VARCHAR
1801 , p_subinventory_code IN VARCHAR2
1802 , p_locator_id IN NUMBER
1803 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
1804 , p_primary_quantity IN NUMBER
1805 , p_secondary_quantity IN NUMBER DEFAULT NULL -- invConv change
1806 , p_cost_group_id IN NUMBER
1807 , p_containerized IN NUMBER
1808 , p_planning_organization_id IN NUMBER
1809 , p_owning_organization_id IN number
1810 ) IS
1811
1812 -- l_is_reservable_sub BOOLEAN; -- invConv change : not used anymore
1813 b_reservable BOOLEAN; -- invConv change
1814 l_reservable_type NUMBER;
1815 -- l_update_quantity NUMBER; -- not used
1816 -- l_quantity_type NUMBER; -- not used
1817 -- l_containerized NUMBER; -- not used
1818 -- l_table_count NUMBER := 0; -- not used
1819 -- l_att_vmi NUMBER; -- not used
1820 -- l_new_att_vmi NUMBER; -- not used
1821 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1822 BEGIN
1823
1824 x_return_status := fnd_api.g_ret_sts_success;
1825
1826
1827 IF (p_inventory_item_id IS NULL) THEN
1828 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
1829 fnd_msg_pub.ADD;
1830 x_msg_data := fnd_message.get;
1831 x_return_status :='E';
1832 RAISE fnd_api.g_exc_unexpected_error;
1833 END IF ;
1834 IF (p_organization_id IS NULL) THEN
1835 fnd_message.set_name('INV', 'INV-NO ORG INFORMATION');
1836 fnd_msg_pub.ADD;
1837 x_msg_data := fnd_message.get;
1838 x_return_status :='E';
1839 RAISE fnd_api.g_exc_unexpected_error;
1840 END IF ;
1841
1842 -- We assume that this API is only invoked for VMI related transactions.
1843
1844 -- The update quantity API should have minimum level of Subinventory
1845 --level.
1846
1847 IF (p_subinventory_code IS NULL) THEN
1848 fnd_message.set_name('INV', 'INV-WRONG_LEVEL');
1849 fnd_msg_pub.ADD;
1850 x_msg_data := fnd_message.get;
1851 x_return_status :='E';
1852 RAISE fnd_api.g_exc_unexpected_error;
1853 END IF ;
1854
1855
1856 -- need to find out whether the sub is reservable or not
1857 -- to appropriate update the vmi_temp table.
1858 -- This is currently not being used, but in the future we may
1859 -- consider reservations seperately from the qty tree
1860
1861 /* invConv change begin : check_is_reservable_sub becomes obsolete.
1862 this is replace by check_is_reservable :
1863 check_is_reservable_sub
1864 (
1865 x_return_status => x_return_status
1866 , p_organization_id => p_organization_id
1867 , p_subinventory_code => p_subinventory_code
1868 , x_is_reservable_sub => l_is_reservable_sub
1869 );
1870 */
1871 check_is_reservable
1872 ( x_return_status => x_return_status
1873 , p_node_level => NULL
1874 , p_inventory_item_id => p_inventory_item_id
1875 , p_organization_id => p_organization_id
1876 , p_subinventory_code => p_subinventory_code
1877 , p_locator_id => p_locator_id
1878 , p_lot_number => p_lot_number
1879 , x_is_reservable => b_reservable);
1880
1881 IF b_reservable
1882 THEN
1883 IF (l_debug = 1) THEN
1884 inv_log_util.trace('in update_consigned_quantities is_rsv=TRUE', 'CONSIGNED_VALIDATIONS',9);
1885 END IF;
1886 ELSE
1887 IF (l_debug = 1) THEN
1888 inv_log_util.trace('in update_consigned_quantities is_rsv=FALSE', 'CONSIGNED_VALIDATIONS',9);
1889 END IF;
1890 END IF;
1891 -- invConv changes end.
1892
1893 IF x_return_status = fnd_api.g_ret_sts_error THEN
1894 RAISE fnd_api.g_exc_error;
1895 End IF ;
1896 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1897 RAISE fnd_api.g_exc_unexpected_error;
1898 End IF;
1899
1900 -- invConv change : replaced l_is_reservable_sub by b_reservable
1901 -- IF (l_is_reservable_sub) THEN
1902 IF (b_reservable) THEN
1903 l_reservable_type := 1;
1904 ELSE
1905 l_reservable_type := 2;
1906 END IF;
1907
1908
1909 -- At this point we can insert another row into the vmi
1910 -- temp table.
1911
1912 INSERT INTO mtl_consigned_qty_temp ( organization_id,
1913 inventory_item_id,
1914 revision,
1918 reservable_type,
1915 lot_number,
1916 lot_expiration_date,
1917 subinventory_code,
1919 locator_id,
1920 grade_code, -- invConv change
1921 primary_quantity,
1922 secondary_quantity, -- invConv change
1923 transactable_vmi,
1924 transactable_secondary_vmi, -- invConv change
1925 date_received,
1926 quantity_type,
1927 cost_group_id,
1928 containerized,
1929 planning_organization_id,
1930 owning_organization_id)
1931 VALUES
1932 (p_organization_id,
1933 p_inventory_item_id,
1934 p_revision,
1935 p_lot_number,
1936 NULL,
1937 p_subinventory_code,
1938 l_reservable_type,
1939 p_locator_id,
1940 p_grade_code, -- invConv change
1941 p_primary_quantity,
1942 p_secondary_quantity, -- invConv change
1943 p_primary_quantity,
1944 p_secondary_quantity, -- invConv change
1945 NULL,
1946 1,
1947 p_cost_group_id,
1948 p_containerized,
1949 p_planning_organization_id,
1950 p_owning_organization_id);
1951
1952 x_return_status := fnd_api.g_ret_sts_success;
1953
1954 EXCEPTION
1955
1956 WHEN OTHERS THEN
1957 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1958 IF (l_debug = 1) THEN
1959 inv_log_util.trace('Ex in update_vmi_quantities','CONSIGNED_VALIDATIONS',9);
1960 END IF;
1961
1962
1963 END update_consigned_quantities;
1964
1965
1966
1967
1968 PROCEDURE CHECK_CONSUME
1969 (
1970 P_TRANSACTION_TYPE_ID IN NUMBER,
1971 P_ORGANIZATION_ID IN NUMBER ,
1972 P_SUBINVENTORY_CODE IN VARCHAR2,
1973 P_XFER_SUBINVENTORY_CODE IN VARCHAR2,
1974 p_from_locator_id IN NUMBER,
1975 p_TO_locator_id IN NUMBER,
1976 P_INVENTORY_ITEM_ID IN NUMBER,
1977 P_OWNING_ORGANIZATION_ID IN NUMBER,
1978 P_PLANNING_ORGANIZATION_ID IN NUMBER,
1979 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1980 X_MSG_COUNT OUT NOCOPY NUMBER,
1981 X_MSG_DATA OUT NOCOPY VARCHAR2,
1982 X_CONSUME_CONSIGNED OUT NOCOPY NUMBER,
1983 X_CONSUME_VMI OUT NOCOPY NUMBER) IS
1984 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1985 l_weight NUMBER;
1986 BEGIN
1987
1988 x_return_status := fnd_api.g_ret_sts_success;
1989
1990 SELECT decode(consume_consigned_flag,'Y',1,0), decode(consume_vmi_flag,'Y',1,0),weight
1991 INTO x_consume_consigned, x_consume_vmi,l_weight from
1992 (SELECT nvl(consume_consigned_flag,'N') consume_consigned_flag, nvl(consume_vmi_flag,'N') consume_vmi_flag,weight
1993 FROM MTL_CONSUMPTION_DEFINITION
1994 WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_TYPE_ID
1995 and nvl(ORGANIZATION_ID, nvl(P_ORGANIZATION_ID,-999) )= nvl(P_ORGANIZATION_ID,-999)
1996 and nvl(SUBINVENTORY_CODE, nvl(P_SUBINVENTORY_CODE,-999) ) = nvl(P_SUBINVENTORY_CODE,-999)
1997 and nvl( XFER_SUBINVENTORY_CODE, nvl(P_XFER_SUBINVENTORY_CODE, -999) )
1998 = nvl(P_XFER_SUBINVENTORY_CODE, -999)
1999 and nvl( FROM_LOCATOR_ID, nvl(P_FROM_LOCATOR_ID, -999) ) = nvl(P_FROM_LOCATOR_ID, -999)
2000 and nvl( TO_LOCATOR_ID, nvl(P_TO_LOCATOR_ID, -999) ) = nvl(P_TO_LOCATOR_ID, -999)
2001 and nvl( INVENTORY_ITEM_ID , nvl( P_INVENTORY_ITEM_ID ,-999)) = nvl( P_INVENTORY_ITEM_ID , -999)
2002 and nvl(OWNING_ORGANIZATION_ID, nvl(P_OWNING_ORGANIZATION_ID, -999) ) = nvl(P_OWNING_ORGANIZATION_ID, -999)
2003 and nvl(PLANNING_ORGANIZATION_ID, nvl( P_PLANNING_ORGANIZATION_ID, -999))
2004 = nvl( P_PLANNING_ORGANIZATION_ID, -999)
2005 ORDER BY Nvl(weight,-1) DESC )
2006 where ROWNUM < 2;
2007
2008 IF (l_debug = 1) THEN
2009 inv_log_util.trace('x_consume_consigned:'||x_consume_consigned||'x_consume_vmi :'||x_consume_vmi||'weight:'||l_weight,'CONSIGNED_VALIDATIONS',9);
2010 END IF;
2011 EXCEPTION
2012 WHEN no_data_found THEN
2013 x_consume_consigned := 0;
2014 x_consume_vmi := 0;
2015 x_return_status := fnd_api.g_ret_sts_success;
2016 WHEN others THEN
2017 x_return_status := fnd_api.G_RET_STS_ERROR;
2018 END check_consume;
2019
2020
2021 --VALUE RETURNED:
2022 --If there are pending transactions - 'Y'
2023 --otherwise - 'N'
2024
2025 FUNCTION check_pending_transactions(
2026 P_ORGANIZATION_ID IN NUMBER,
2027 P_SUBINVENTORY_CODE IN VARCHAR2,
2028 p_locator_id IN VARCHAR2,
2029 p_item_id IN NUMBER,
2030 p_lpn_id IN NUMBER) RETURN VARCHAR2 IS
2031
2032 l_pending_txn_cnt NUMBER:=0;
2033 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2034 BEGIN
2035
2036 IF p_locator_id IS NOT NULL THEN
2037
2038 IF p_item_id IS NOT NULL AND p_lpn_id IS NULL THEN
2039
2040 SELECT 1 INTO l_pending_txn_cnt FROM dual
2041 WHERE exists (select 1 from mtl_material_transactions_temp
2042 where organization_id = P_ORGANIZATION_ID
2043 and Nvl(transaction_status,1) in (1,3) --pending txn
2044 AND inventory_item_id = p_item_id
2045 and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE
2046 and LOCATOR_ID = p_locator_id);
2047
2051 WHERE exists (select 1 from mtl_material_transactions_temp
2048 ELSIF p_lpn_id IS NOT NULL AND p_item_id IS NULL THEN
2049
2050 SELECT 1 INTO l_pending_txn_cnt FROM dual
2052 where organization_id = P_ORGANIZATION_ID
2053 and Nvl(transaction_status,1) in (1,3) --pending txn
2054 AND ((transfer_lpn_id = p_lpn_id)
2055 OR (content_lpn_id = p_lpn_id)
2056 OR (lpn_id = p_lpn_id)
2057 OR (allocated_lpn_id = p_lpn_id))
2058 and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE
2059 and LOCATOR_ID = p_locator_id);
2060 END IF;
2061
2062 ELSE--p_locator_id IS NULL
2063
2064 IF p_item_id IS NOT NULL AND p_lpn_id IS NULL THEN
2065
2066 SELECT 1 INTO l_pending_txn_cnt FROM dual
2067 WHERE exists (select 1 from mtl_material_transactions_temp
2068 where organization_id = P_ORGANIZATION_ID
2069 and Nvl(transaction_status,1) in (1,3) --pending txn
2070 AND inventory_item_id = p_item_id
2071 and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE);
2072
2073 ELSIF p_lpn_id IS NOT NULL AND p_item_id IS NULL THEN
2074
2075 SELECT 1 INTO l_pending_txn_cnt FROM dual
2076 WHERE exists (select 1 from mtl_material_transactions_temp
2077 where organization_id = P_ORGANIZATION_ID
2078 and Nvl(transaction_status,1) in (1,3) --pending txn
2079 AND ((transfer_lpn_id = p_lpn_id)
2080 OR (content_lpn_id = p_lpn_id)
2081 OR (lpn_id = p_lpn_id)
2082 OR (allocated_lpn_id = p_lpn_id))
2083 and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE);
2084 END IF;
2085
2086
2087 END IF;
2088
2089 IF l_pending_txn_cnt = 0 THEN
2090 RETURN 'N'; --THERE ARE NO PENDING TXN
2091 ELSE
2092 RETURN 'Y';
2093 END IF;
2094 EXCEPTION
2095 WHEN others THEN
2096 IF (l_debug = 1) THEN
2097 inv_log_util.trace('Other error in inv_consigned_validations.check_pending_transactions','CONSIGNED_VALIDATIONS',9);
2098 END IF;
2099 RETURN 'N';
2100 END check_pending_transactions ;
2101
2102 -- This API returns the onhand quantity for planning purpose
2103 -- When it is called for Subinventory level query, it includes VMI quantity, because replenishment within the warehouse should not distinguish VMI stocks
2104 -- When it is called for Organization level query, it does not include VMI quantity, because relenishment for the whole warehouse should affect VMI stock
2105 -- The quantity is calculated with onhand quantity from
2106 -- MTL_ONHAND_QUANTITIES_DETAIL and pending transactions from
2107 -- MTL_MATERIAL_TRANSACTIONS_TEMP
2108 -- The quantities does not include suggestions
2109 -- Input Parameters
2110 -- P_INCLUDE_NONNET: Whether include non-nettable subinventories
2111 -- Values: 1 => Include non-nettable subinventories
2112 -- 2 => Only include nettabel subinventores
2113 -- P_LEVEL: Query onhand at Organization level (1)
2114 -- or Subinventory level (2)
2115 -- P_ORG_ID: Organization ID
2116 -- P_SUBINV: Subinventory
2117 -- P_ITEM_ID: Item ID
2118
2119 -- Note that this may includes pending transactions that
2120 -- will keep the VMI attributes of inventory stock
2121 FUNCTION GET_PLANNING_QUANTITY(
2122 P_INCLUDE_NONNET NUMBER
2123 , P_LEVEL NUMBER
2124 , P_ORG_ID NUMBER
2125 , P_SUBINV VARCHAR2
2126 , P_ITEM_ID NUMBER
2127 ) RETURN NUMBER IS
2128
2129 l_qoh NUMBER := 0;
2130 l_sqoh NUMBER := NULL;
2131 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2132
2133 BEGIN
2134 IF (l_debug=1) THEN
2135 inv_log_util.trace('p_include_nonnet: ' || to_char(p_include_nonnet) ||
2136 ', p_level: ' || to_char(p_level) ||
2137 ', p_org_id: ' || to_char(p_org_id) ||
2138 ', p_subinv: ' || p_subinv ||
2139 ', p_item_id: ' || to_char(p_item_id)
2140 , 'GET_PLANNING_ONHAND_QTY'
2141 , 9);
2142 END IF;
2143
2144 -- invConv changes begin :
2145 -- Calling the new GET_PLANNING_QUANTITY procedure
2146 GET_PLANNING_QUANTITY(
2147 P_INCLUDE_NONNET => P_INCLUDE_NONNET
2148 , P_LEVEL => P_LEVEL
2149 , P_ORG_ID => P_ORG_ID
2150 , P_SUBINV => P_SUBINV
2151 , P_ITEM_ID => P_ITEM_ID
2152 , P_GRADE_CODE => NULL -- invConv change
2153 , X_QOH => l_qoh -- invConv change
2154 , X_SQOH => l_sqoh); -- invConv change
2155 -- invConv changes end.
2156
2157
2158 IF(l_debug=1) THEN
2159 inv_log_util.trace('Total quantity on-hand: ' || to_char(l_qoh), 'GET_PLANNING_ONHAND_QTY', 9);
2160 END IF;
2161 RETURN(l_qoh);
2162
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 IF(l_debug=1) THEN
2167 inv_log_util.trace(sqlcode || ', ' || sqlerrm, 'GET_PLANNING_ONHAND_QTY', 1);
2168 END IF;
2169 RETURN(0);
2170
2171 END GET_PLANNING_QUANTITY;
2172
2173 -- invConv changes begin : new procedure because GET_PLANNING_QUANTITY only returns one value.
2174 PROCEDURE GET_PLANNING_QUANTITY(
2175 P_INCLUDE_NONNET IN NUMBER
2179 , P_ITEM_ID IN NUMBER
2176 , P_LEVEL IN NUMBER
2177 , P_ORG_ID IN NUMBER
2178 , P_SUBINV IN VARCHAR2
2180 , P_GRADE_CODE IN VARCHAR2 -- invConv change
2181 , X_QOH OUT NOCOPY NUMBER -- invConv change
2182 , X_SQOH OUT NOCOPY NUMBER -- invConv change
2183 ) IS
2184
2185 x_return_status VARCHAR2(30);
2186 l_qoh NUMBER := 0;
2187 l_moq_qty NUMBER := 0;
2188 l_mmtt_qty_src NUMBER := 0;
2189 l_mmtt_qty_dest NUMBER := 0;
2190 l_sqoh NUMBER := 0; -- invConv change
2191 l_moq_sqty NUMBER := 0; -- invConv change
2192 l_mmtt_sqty_src NUMBER := 0; -- invConv change
2193 l_mmtt_sqty_dest NUMBER := 0; -- invConv change
2194 l_lot_control NUMBER := 1;
2195 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2196 l_lpn_qty NUMBER := 0; -- Bug 4209192
2197 l_default_status_id number:= -1; -- Added for 6633612
2198
2199 -- invConv changes begin
2200 l_uom_ind VARCHAR2(4);
2201
2202 CURSOR get_item_info( l_org_id IN NUMBER
2203 , l_item_id IN NUMBER) IS
2204 SELECT tracking_quantity_ind
2205 , lot_control_code
2206 FROM mtl_system_items_b
2207 WHERE inventory_item_id = l_item_id
2208 AND organization_id = l_org_id;
2209 -- invConv changes end
2210
2211 BEGIN
2212
2213 IF (l_debug=1) THEN
2214 debug_print('p_include_nonnet: ' || to_char(p_include_nonnet) ||
2215 ', p_level: ' || to_char(p_level) ||
2216 ', p_org_id: ' || to_char(p_org_id) ||
2217 ', p_subinv: ' || p_subinv ||
2218 ', p_grade_code: ' || p_grade_code ||
2219 ', p_item_id: ' || to_char(p_item_id)
2220 , 'GET_PLANNING_ONHAND_QTY'
2221 , 9);
2222 END IF;
2223
2224 -- invConv changes begin
2225 -- Only run this function when DUOM item.
2226 OPEN get_item_info( p_org_id, p_item_id);
2227 FETCH get_item_info
2228 INTO l_uom_ind
2229 , l_lot_control;
2230 CLOSE get_item_info;
2231
2232 -- invConv change : this is included in the above cursor.
2233 -- SELECT lot_control_code
2234 -- into l_lot_control
2235 -- from mtl_system_items_b
2236 -- where inventory_item_id = p_item_id
2237 -- and organization_id = p_org_id;
2238
2239 -- Added the below for 6633612
2240 if inv_cache.set_org_rec(p_org_id) then
2241 l_default_status_id := inv_cache.org_rec.default_status_id;
2242 if l_default_status_id is null then
2243 l_default_status_id := -1;
2244 end if;
2245 end if;
2246
2247 IF (p_level = 1) THEN
2248 -- Organization Level
2249
2250 /* nsinghi MIN-MAX INVCONV start */
2251
2252 IF p_include_nonnet = 1 THEN
2253
2254 -- invConv change : replaced primary by secondary qty field.
2255
2256 SELECT SUM(moq.primary_transaction_quantity)
2257 , SUM( NVL(moq.secondary_transaction_quantity, 0))
2258 INTO l_moq_qty
2259 , l_moq_sqty
2260 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_secondary_inventories msi
2261 WHERE moq.organization_id = p_org_id
2262 AND moq.inventory_item_id = p_item_id
2263 AND msi.organization_id = moq.organization_id
2264 AND msi.secondary_inventory_name = moq.subinventory_code
2265 AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
2266 AND moq.lot_number = mln.lot_number(+)
2267 AND moq.organization_id = mln.organization_id(+)
2268 AND moq.inventory_item_id = mln.inventory_item_id(+)
2269 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
2270 AND nvl(moq.planning_tp_type,2) = 2;
2271
2272
2273 ELSE /* include nettable */
2274
2275 SELECT SUM(mon.primary_transaction_quantity)
2276 , SUM( NVL(mon.secondary_transaction_quantity, 0))
2277 INTO l_moq_qty
2278 , l_moq_sqty
2279 FROM mtl_onhand_net mon, mtl_lot_numbers mln
2280 WHERE mon.organization_id = p_org_id
2281 AND mon.inventory_item_id = p_item_id
2282 AND mon.organization_id = nvl(mon.planning_organization_id, mon.organization_id)
2283 AND mon.lot_number = mln.lot_number(+)
2284 AND mon.organization_id = mln.organization_id(+)
2285 AND mon.inventory_item_id = mln.inventory_item_id(+)
2286 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
2287 AND nvl(mon.planning_tp_type,2) = 2;
2288
2289 END IF;
2290
2291 IF(l_debug=1) THEN
2292 debug_print('Total MOQ Org level: qty='||l_moq_qty||', qty2='||l_moq_sqty, 'GET_PLANNING_ONHAND_QTY', 9);
2293 END IF;
2294
2295
2296 /* nsinghi MIN-MAX INVCONV end */
2297
2298 IF (l_lot_control = 2) THEN /* Lot - Full Control*/
2299
2300 -- Added the below if for 6633612
2301 IF l_default_status_id = -1 THEN
2302
2306
2303 IF(l_debug=1) THEN
2304 debug_print('In the lot controlled non onhand status enabled:', 'GET_PLANNING_ONHAND_QTY', 9);
2305 END IF;
2307 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2308 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
2309 , SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2310 Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2311 INTO l_mmtt_qty_src
2312 , l_mmtt_sqty_src
2313 FROM mtl_material_transactions_temp mmtt
2314 WHERE mmtt.organization_id = p_org_id
2315 AND mmtt.inventory_item_id = p_item_id
2316 AND mmtt.posting_flag = 'Y'
2317 AND mmtt.subinventory_code IS NOT NULL
2318 AND Nvl(mmtt.transaction_status,0) <> 2
2319 AND mmtt.transaction_action_id NOT IN (24,30)
2320 AND EXISTS (SELECT 'x' FROM mtl_secondary_inventories msi
2321 WHERE msi.organization_id = mmtt.organization_id
2322 AND msi.secondary_inventory_name = mmtt.subinventory_code
2323 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
2324 AND mmtt.planning_organization_id IS NULL
2325 AND EXISTS (SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
2326 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2327 AND mtlt.lot_number = mln.lot_number(+)
2328 AND p_org_id = mln.organization_id(+)
2329 AND p_item_id = mln.inventory_item_id(+)
2330 /* nsinghi MIN-MAX INVCONV start */
2331 AND nvl(mln.availability_type,2) = decode(p_include_nonnet,1,nvl(mln.availability_type,2),1)
2332 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),SYSDATE+1))> trunc(sysdate))
2333 AND (mmtt.locator_id IS NULL OR
2334 (mmtt.locator_id IS NOT NULL AND
2335 EXISTS (SELECT 'x' FROM mtl_item_locations mil
2336 WHERE mmtt.organization_id = mil.organization_id
2337 AND mmtt.locator_id = mil.inventory_location_id
2338 AND mmtt.subinventory_code = mil.subinventory_code
2339 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1))))
2340 /* nsinghi MIN-MAX INVCONV end */
2341
2342 AND nvl(mmtt.planning_tp_type,2) = 2;
2343
2344 IF(l_debug=1) THEN
2345 debug_print('Total MMTT Trx qty Source Org level (lot Controlled): qty='||l_mmtt_qty_src||', qty2='||l_mmtt_sqty_src, 'GET_PLANNING_ONHAND_QTY', 9);
2346 END IF;
2347
2348 SELECT SUM(Abs(mmtt.primary_quantity))
2349 , SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2350 INTO l_mmtt_qty_dest
2351 , l_mmtt_sqty_dest
2352 FROM mtl_material_transactions_temp mmtt
2353 WHERE decode(mmtt.transaction_action_id,3,
2354 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
2355 AND mmtt.inventory_item_id = p_item_id
2356 AND mmtt.posting_flag = 'Y'
2357 AND Nvl(mmtt.transaction_status,0) <> 2
2358 AND mmtt.transaction_action_id in (2,28,3)
2359 AND
2360 (
2361 (mmtt.transfer_subinventory IS NULL)
2362 OR
2363 (
2364 mmtt.transfer_subinventory IS NOT NULL
2365 AND EXISTS
2366 (
2367 SELECT 'x' FROM mtl_secondary_inventories msi
2368 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
2369 3, mmtt.transfer_organization,mmtt.organization_id)
2370 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
2371 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1)
2372 )
2373 )
2374 )
2375 AND mmtt.planning_organization_id IS NULL
2376 AND EXISTS
2377 (
2378 SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
2379 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2380 AND mtlt.lot_number = mln.lot_number (+)
2381 AND decode(mmtt.transaction_action_id,
2382 3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
2383 AND p_item_id = mln.inventory_item_id(+)
2384 /* nsinghi MIN-MAX INVCONV start */
2385 AND nvl(mln.availability_type,2) = decode(p_include_nonnet,1,nvl(mln.availability_type,2),1)
2386 AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate)
2387 )
2388 AND
2389 (
2390 mmtt.transfer_to_location IS NULL OR
2391 (
2392 mmtt.transfer_to_location IS NOT NULL AND
2393 EXISTS
2394 (
2395 SELECT 'x' FROM mtl_item_locations mil
2396 WHERE decode(mmtt.transaction_action_id,
2397 3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
2398 AND mmtt.transfer_to_location = mil.inventory_location_id
2402 )
2399 AND mmtt.transfer_subinventory = mil.subinventory_code
2400 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)
2401 )
2403 )
2404 /* nsinghi MIN-MAX INVCONV end */
2405 AND nvl(mmtt.planning_tp_type,2) = 2;
2406
2407 IF(l_debug=1) THEN
2408 debug_print('Total MMTT Trx qty Dest Org level (lot controlled): qty='||l_mmtt_qty_dest||', qty2='||l_mmtt_sqty_dest, 'GET_PLANNING_ONHAND_QTY', 9);
2409 END IF;
2410
2411 ELSE /* default material status enabled of the org */
2412
2413 IF(l_debug=1) THEN
2414 debug_print('In the lot contolled onhand status enabled:', 'GET_PLANNING_ONHAND_QTY', 9);
2415 END IF;
2416
2417 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2418 Sign(mtlt.primary_quantity)) * Abs( mtlt.primary_quantity ))
2419 , SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2420 Sign(mtlt.secondary_quantity)) * Abs( NVL(mtlt.secondary_quantity, 0) ))
2421 INTO l_mmtt_qty_src
2422 , l_mmtt_sqty_src
2423 FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
2424 WHERE mmtt.organization_id = p_org_id
2425 AND mmtt.inventory_item_id = p_item_id
2426 AND mmtt.posting_flag = 'Y'
2427 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
2428 AND mmtt.subinventory_code IS NOT NULL
2429 AND mmtt.subinventory_code IS NOT NULL
2430 AND Nvl(mmtt.transaction_status,0) <> 2
2431 AND mmtt.transaction_action_id NOT IN (24,30)
2432 AND EXISTS (SELECT 'x' FROM mtl_secondary_inventories msi
2433 WHERE msi.organization_id = mmtt.organization_id
2434 AND msi.secondary_inventory_name = mmtt.subinventory_code
2435 )
2436 AND mmtt.planning_organization_id IS NULL
2437 AND EXISTS (SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
2438 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2439 AND mtlt.lot_number = mln.lot_number(+)
2440 AND p_org_id = mln.organization_id(+)
2441 AND p_item_id = mln.inventory_item_id(+)
2442 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),SYSDATE+1))> trunc(sysdate))
2443 AND (mmtt.locator_id IS NULL OR
2444 (mmtt.locator_id IS NOT NULL AND
2445 EXISTS (SELECT 'x' FROM mtl_item_locations mil
2446 WHERE mmtt.organization_id = mil.organization_id
2447 AND mmtt.locator_id = mil.inventory_location_id
2448 AND mmtt.subinventory_code = mil.subinventory_code)))
2449 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
2450 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
2451 mmtt.inventory_item_id,
2452 mmtt.subinventory_code,
2453 mmtt.locator_id,
2454 mtlt.lot_number,
2455 mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
2456 AND mms.availability_type =1)
2457 AND nvl(mmtt.planning_tp_type,2) = 2;
2458
2459 IF(l_debug=1) THEN
2460 debug_print('Total MMTT Trx qty Source Org level (lot Controlled): qty='||l_mmtt_qty_src||', qty2='||l_mmtt_sqty_src, 'GET_PLANNING_ONHAND_QTY', 9);
2461 END IF;
2462
2463
2464 SELECT SUM(Abs(mtlt.primary_quantity))
2465 , SUM(Abs( NVL(mtlt.secondary_quantity, 0) ))
2466 INTO l_mmtt_qty_dest
2467 , l_mmtt_sqty_dest
2468 FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
2469 WHERE decode(mmtt.transaction_action_id,3,
2470 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
2471 AND mmtt.inventory_item_id = p_item_id
2472 AND mmtt.posting_flag = 'Y'
2473 AND Nvl(mmtt.transaction_status,0) <> 2
2474 AND mmtt.transaction_action_id in (2,28,3)
2475 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
2476 AND
2477 (
2478 (mmtt.transfer_subinventory IS NULL)
2479 OR
2480 (
2481 mmtt.transfer_subinventory IS NOT NULL
2482 AND EXISTS
2483 (
2484 SELECT 'x' FROM mtl_secondary_inventories msi
2485 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
2486 3, mmtt.transfer_organization,mmtt.organization_id)
2487 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
2488 )
2489 )
2490 )
2491 AND mmtt.planning_organization_id IS NULL
2492 AND EXISTS
2493 (
2494 SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
2495 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2496 AND mtlt.lot_number = mln.lot_number (+)
2497 AND decode(mmtt.transaction_action_id,
2501 AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate)
2498 3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
2499 AND p_item_id = mln.inventory_item_id(+)
2500 /* nsinghi MIN-MAX INVCONV start */
2502 )
2503 AND
2504 (
2505 mmtt.transfer_to_location IS NULL OR
2506 (
2507 mmtt.transfer_to_location IS NOT NULL AND
2508 EXISTS
2509 (
2510 SELECT 'x' FROM mtl_item_locations mil
2511 WHERE decode(mmtt.transaction_action_id,
2512 3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
2513 AND mmtt.transfer_to_location = mil.inventory_location_id
2514 AND mmtt.transfer_subinventory = mil.subinventory_code
2515 )
2516 )
2517 )
2518 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
2519 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(decode(mmtt.transaction_action_id,3, mmtt.transfer_organization,mmtt.organization_id),
2520 mmtt.inventory_item_id,
2521 mmtt.transfer_subinventory,
2522 mmtt.transfer_to_location,
2523 mtlt.lot_number,
2524 mmtt.lpn_id, mmtt.transaction_action_id,
2525 INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
2526 mmtt.inventory_item_id,
2527 mmtt.subinventory_code,
2528 mmtt.locator_id,
2529 mtlt.lot_number,
2530 mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
2531 AND mms.availability_type =1)
2532 /* nsinghi MIN-MAX INVCONV end */
2533 AND nvl(mmtt.planning_tp_type,2) = 2;
2534
2535 -- Rkatoori, For sub inventory transfer type, transfer_organization is null, so we have to do testing in that aspect..
2536 -- If there are any issues, need to add decode for that..
2537 END IF; /* End of IF l_default_status_id = -1 */
2538
2539 ELSE /* non lot controlled */
2540 -- Added the below if for 6633612
2541 IF l_default_status_id = -1 THEN
2542
2543 IF(l_debug=1) THEN
2544 debug_print('In non lot controlled non onhand status enabled:', 'GET_PLANNING_ONHAND_QTY', 9);
2545 END IF;
2546
2547 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2548 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
2549 , SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2550 Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2551 INTO l_mmtt_qty_src
2552 , l_mmtt_sqty_src
2553 FROM mtl_material_transactions_temp mmtt
2554 WHERE mmtt.organization_id = p_org_id
2555 AND mmtt.inventory_item_id = p_item_id
2556 AND mmtt.posting_flag = 'Y'
2557 AND mmtt.subinventory_code IS NOT NULL
2558 AND Nvl(mmtt.transaction_status,0) <> 2
2559 AND mmtt.transaction_action_id NOT IN (24,30)
2560 AND EXISTS (select 'x' from mtl_secondary_inventories msi
2561 WHERE msi.organization_id = mmtt.organization_id
2562 AND msi.secondary_inventory_name = mmtt.subinventory_code
2563 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
2564 AND mmtt.planning_organization_id IS NULL
2565
2566 /* nsinghi MIN-MAX INVCONV start */
2567 AND (mmtt.locator_id IS NULL OR
2568 (mmtt.locator_id IS NOT NULL AND
2569 EXISTS (select 'x' from mtl_item_locations mil
2570 WHERE mmtt.organization_id = mil.organization_id
2571 AND mmtt.locator_id = mil.inventory_location_id
2572 AND mmtt.subinventory_code = mil.subinventory_code
2573 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1))))
2574 /* nsinghi MIN-MAX INVCONV end */
2575
2576 AND nvl(mmtt.planning_tp_type,2) = 2;
2577
2578 IF(l_debug=1) THEN
2579 debug_print('Total MMTT Trx qty Source Org level: qty='||l_mmtt_qty_src||', qty2='||l_mmtt_sqty_src, 'GET_PLANNING_ONHAND_QTY', 9);
2580 END IF;
2581
2582 SELECT SUM(Abs(mmtt.primary_quantity))
2583 , SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2584 INTO l_mmtt_qty_dest
2585 , l_mmtt_sqty_dest
2586 FROM mtl_material_transactions_temp mmtt
2587 WHERE decode(mmtt.transaction_action_id,3,
2588 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
2589 AND mmtt.inventory_item_id = p_item_id
2590 AND mmtt.posting_flag = 'Y'
2591 AND Nvl(mmtt.transaction_status,0) <> 2
2592 AND mmtt.transaction_action_id in (2,28,3)
2593 AND ((mmtt.transfer_subinventory IS NULL) OR
2594 (mmtt.transfer_subinventory IS NOT NULL
2595 AND EXISTS (select 'x' from mtl_secondary_inventories msi
2599 AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))))
2596 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
2597 3, mmtt.transfer_organization,mmtt.organization_id)
2598 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
2600 AND mmtt.planning_organization_id IS NULL
2601
2602 /* nsinghi MIN-MAX INVCONV start */
2603 AND (mmtt.transfer_to_location IS NULL OR
2604 (mmtt.transfer_to_location IS NOT NULL AND
2605 EXISTS (select 'x' from mtl_item_locations mil
2606 WHERE decode(mmtt.transaction_action_id,
2607 3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
2608 AND mmtt.transfer_to_location = mil.inventory_location_id
2609 AND mmtt.transfer_subinventory = mil.subinventory_code
2610 AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1))))
2611 /* nsinghi MIN-MAX INVCONV end */
2612
2613 AND nvl(mmtt.planning_tp_type,2) = 2;
2614
2615 IF(l_debug=1) THEN
2616 debug_print('Total MMTT Trx qty Dest Org level: qty=' ||l_mmtt_qty_dest||', qty2='||l_mmtt_sqty_dest, 'GET_PLANNING_ONHAND_QTY', 9);
2617 END IF;
2618
2619 ELSE /* default material status enabled of the org */
2620
2621 IF(l_debug=1) THEN
2622 debug_print('In non lot controlled onhand status enabled:', 'GET_PLANNING_ONHAND_QTY', 9);
2623 END IF;
2624
2625 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2626 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
2627 , SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2628 Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2629 INTO l_mmtt_qty_src
2630 , l_mmtt_sqty_src
2631 FROM mtl_material_transactions_temp mmtt
2632 WHERE mmtt.organization_id = p_org_id
2633 AND mmtt.inventory_item_id = p_item_id
2634 AND mmtt.posting_flag = 'Y'
2635 AND mmtt.subinventory_code IS NOT NULL
2636 AND Nvl(mmtt.transaction_status,0) <> 2
2637 AND mmtt.transaction_action_id NOT IN (24,30)
2638 AND EXISTS (select 'x' from mtl_secondary_inventories msi
2639 WHERE msi.organization_id = mmtt.organization_id
2640 AND msi.secondary_inventory_name = mmtt.subinventory_code)
2641 AND mmtt.planning_organization_id IS NULL
2642
2643 /* nsinghi MIN-MAX INVCONV start */
2644 AND (mmtt.locator_id IS NULL OR
2645 (mmtt.locator_id IS NOT NULL AND
2646 EXISTS (select 'x' from mtl_item_locations mil
2647 WHERE mmtt.organization_id = mil.organization_id
2648 AND mmtt.locator_id = mil.inventory_location_id
2649 AND mmtt.subinventory_code = mil.subinventory_code)))
2650 /* nsinghi MIN-MAX INVCONV end */
2651 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
2652 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
2653 mmtt.inventory_item_id,
2654 mmtt.subinventory_code,
2655 mmtt.locator_id,
2656 mmtt.lot_number,
2657 mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
2658 AND mms.availability_type =1)
2659 AND nvl(mmtt.planning_tp_type,2) = 2;
2660
2661 IF(l_debug=1) THEN
2662 debug_print('Total MMTT Trx qty Source Org level: qty='||l_mmtt_qty_src||', qty2='||l_mmtt_sqty_src, 'GET_PLANNING_ONHAND_QTY', 9);
2663 END IF;
2664
2665 SELECT SUM(Abs(mmtt.primary_quantity))
2666 , SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2667 INTO l_mmtt_qty_dest
2668 , l_mmtt_sqty_dest
2669 FROM mtl_material_transactions_temp mmtt
2670 WHERE decode(mmtt.transaction_action_id,3,
2671 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
2672 AND mmtt.inventory_item_id = p_item_id
2673 AND mmtt.posting_flag = 'Y'
2674 AND Nvl(mmtt.transaction_status,0) <> 2
2675 AND mmtt.transaction_action_id in (2,28,3)
2676 AND ((mmtt.transfer_subinventory IS NULL) OR
2677 (mmtt.transfer_subinventory IS NOT NULL
2678 AND EXISTS (select 'x' from mtl_secondary_inventories msi
2679 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
2680 3, mmtt.transfer_organization,mmtt.organization_id)
2681 AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
2682 AND mmtt.planning_organization_id IS NULL
2683
2684 /* nsinghi MIN-MAX INVCONV start */
2685 AND (mmtt.transfer_to_location IS NULL OR
2686 (mmtt.transfer_to_location IS NOT NULL AND
2687 EXISTS (select 'x' from mtl_item_locations mil
2688 WHERE decode(mmtt.transaction_action_id,
2692 /* nsinghi MIN-MAX INVCONV end */
2689 3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
2690 AND mmtt.transfer_to_location = mil.inventory_location_id
2691 AND mmtt.transfer_subinventory = mil.subinventory_code)))
2693 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
2694 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(decode(mmtt.transaction_action_id,3, mmtt.transfer_organization,mmtt.organization_id),
2695 mmtt.inventory_item_id,
2696 mmtt.transfer_subinventory,
2697 mmtt.transfer_to_location,
2698 mmtt.lot_number,
2699 mmtt.lpn_id, mmtt.transaction_action_id,
2700 INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
2701 mmtt.inventory_item_id,
2702 mmtt.subinventory_code,
2703 mmtt.locator_id,
2704 mmtt.lot_number,
2705 mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
2706 AND mms.availability_type =1)
2707
2708 AND nvl(mmtt.planning_tp_type,2) = 2;
2709
2710 IF(l_debug=1) THEN
2711 debug_print('Total MMTT Trx qty Dest Org level: qty=' ||l_mmtt_qty_dest||', qty2='||l_mmtt_sqty_dest, 'GET_PLANNING_ONHAND_QTY', 9);
2712 END IF;
2713
2714 END IF; /* End of IF l_default_status_id = -1 */
2715
2716 END IF;
2717
2718 -- Bug 4209192, adding below query to account for undelivered LPNs for WIP assembly completions.
2719 SELECT SUM(inv_decimals_pub.get_primary_quantity( p_org_id
2720 ,p_item_id
2721 ,mtrl.uom_code
2722 ,mtrl.quantity - NVL(mtrl.quantity_delivered,0))
2723 )
2724 INTO l_lpn_qty
2725 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mtl_transaction_types mtt
2726 where mtrl.organization_id = p_org_id
2727 AND mtrl.inventory_item_id = p_item_id
2728 AND mtrl.header_id = mtrh.header_id
2729 AND mtrh.move_order_type = 6 -- Putaway Move Order
2730 AND mtrl.transaction_source_type_id = 5 -- Wip
2731 AND mtt.transaction_action_id = 31 -- WIP Assembly Completion
2732 AND mtt.transaction_type_id = mtrl.transaction_type_id
2733 AND mtrl.line_status = 7 -- Pre Approved
2734 AND mtrl.lpn_id is not null;
2735
2736 IF(l_debug=1) THEN
2737 inv_log_util.trace('Total MTRL undelivered LPN quantity for WIP completions: ' || to_char(l_lpn_qty), 'GET_PLANNING_ONHAND_QTY', 9);
2738 END IF;
2739
2740
2741 ELSIF (p_level = 2) THEN
2742
2743 /* nsinghi MIN-MAX INVCONV start */
2744
2745 /* If Min-Max Planning is run at sub-inventory level, value for include-nonnettable is always
2746 assumned to be 1. Thus no need to check for nettablity when run at sub-inv level. */
2747
2748 /* nsinghi MIN-MAX INVCONV end */
2749
2750 -- Subinventory level
2751 SELECT SUM(moq.primary_transaction_quantity)
2752 , SUM( NVL(moq.secondary_transaction_quantity, 0))
2753 INTO l_moq_qty
2754 , l_moq_sqty
2755 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
2756 WHERE moq.organization_id = p_org_id
2757 AND moq.inventory_item_id = p_item_id
2758 AND moq.subinventory_code = p_subinv
2759 AND moq.lot_number = mln.lot_number(+)
2760 AND moq.organization_id = mln.organization_id(+)
2761 AND moq.inventory_item_id = mln.inventory_item_id(+)
2762 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate);
2763
2764 IF(l_debug=1) THEN
2765 debug_print('Total MOQ qty Sub Level: qty='||l_moq_qty||', qty2='||l_moq_sqty, 'GET_PLANNING_ONHAND_QTY', 9);
2766 END IF;
2767
2768 IF (l_lot_control = 2) THEN
2769
2770 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2771 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
2772 , SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2773 Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2774 INTO l_mmtt_qty_src
2775 , l_mmtt_sqty_src
2776 FROM mtl_material_transactions_temp mmtt
2777 WHERE mmtt.organization_id = p_org_id
2778 AND mmtt.inventory_item_id = p_item_id
2779 AND mmtt.subinventory_code = p_subinv
2780 AND mmtt.posting_flag = 'Y'
2781 AND mmtt.subinventory_code IS NOT NULL
2782 AND Nvl(mmtt.transaction_status,0) <> 2
2783 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
2784 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2785 AND mtlt.lot_number = mln.lot_number (+)
2786 AND p_org_id = mln.organization_id(+)
2787 AND p_item_id = mln.inventory_item_id(+)
2788 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
2789 AND mmtt.transaction_action_id NOT IN (24,30);
2790
2794
2791 IF(l_debug=1) THEN
2792 debug_print('Total MMTT Trx qty Source Org Sub(lot controlled): qty='||l_mmtt_qty_src||', qty2='||l_mmtt_sqty_src, 'GET_PLANNING_ONHAND_QTY', 9);
2793 END IF;
2795 SELECT SUM(Abs(mmtt.primary_quantity))
2796 , SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0)))
2797 INTO l_mmtt_qty_dest
2798 , l_mmtt_sqty_dest
2799 FROM mtl_material_transactions_temp mmtt
2800 WHERE decode(mmtt.transaction_action_id,3,
2801 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
2802 AND mmtt.inventory_item_id = p_item_id
2803 AND mmtt.transfer_subinventory = p_subinv
2804 AND mmtt.posting_flag = 'Y'
2805 AND Nvl(mmtt.transaction_status,0) <> 2
2806 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
2807 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2808 AND mtlt.lot_number = mln.lot_number (+)
2809 AND decode(mmtt.transaction_action_id,3,
2810 mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
2811 AND p_item_id = mln.inventory_item_id(+)
2812 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
2813 AND mmtt.transaction_action_id in (2,28,3);
2814
2815 IF(l_debug=1) THEN
2816 debug_print('Total MMTT Trx qty Dest Org Sub(lot controlled): qty='||l_mmtt_qty_dest||', qty2='||l_mmtt_sqty_dest, 'GET_PLANNING_ONHAND_QTY', 9);
2817 END IF;
2818 ELSE
2819 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2820 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
2821 , SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
2822 Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
2823 INTO l_mmtt_qty_src
2824 , l_mmtt_sqty_src
2825 FROM mtl_material_transactions_temp mmtt
2826 WHERE mmtt.organization_id = p_org_id
2827 AND mmtt.inventory_item_id = p_item_id
2828 AND mmtt.subinventory_code = p_subinv
2829 AND mmtt.posting_flag = 'Y'
2830 AND mmtt.subinventory_code IS NOT NULL
2831 AND Nvl(mmtt.transaction_status,0) <> 2
2832 AND mmtt.transaction_action_id NOT IN (24,30);
2833
2834 IF(l_debug=1) THEN
2835 debug_print('Total MMTT Trx qty Source Org Sub: qty='||l_mmtt_qty_src||', qty2='||l_mmtt_sqty_src, 'GET_PLANNING_ONHAND_QTY', 9);
2836 END IF;
2837
2838 SELECT SUM(Abs(mmtt.primary_quantity))
2839 , SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0)))
2840 INTO l_mmtt_qty_dest
2841 , l_mmtt_sqty_dest
2842 FROM mtl_material_transactions_temp mmtt
2843 WHERE decode(mmtt.transaction_action_id,3,
2844 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
2845 AND mmtt.inventory_item_id = p_item_id
2846 AND mmtt.transfer_subinventory = p_subinv
2847 AND mmtt.posting_flag = 'Y'
2848 AND Nvl(mmtt.transaction_status,0) <> 2
2849 AND mmtt.transaction_action_id in (2,28,3);
2850
2851 IF(l_debug=1) THEN
2852 debug_print('Total MMTT Trx qty Dest Org Sub: qty='||l_mmtt_qty_dest||', qty2='||l_mmtt_sqty_dest, 'GET_PLANNING_ONHAND_QTY', 9);
2853 END IF;
2854 END IF;
2855
2856 END IF;
2857
2858 -- Bug 4209192, adding undelivered LPN l_lpn_qty for WIP assembly completions in total onhand.
2859 l_qoh := nvl(l_moq_qty,0) + nvl(l_mmtt_qty_src,0) + nvl(l_mmtt_qty_dest,0) + nvl(l_lpn_qty,0);
2860
2861 -- invConv change
2862 l_sqoh := nvl(l_moq_sqty,0) + nvl(l_mmtt_sqty_src,0) + nvl(l_mmtt_sqty_dest,0);
2863
2864 If(l_debug=1) THEN
2865 debug_print('Total quantity on-hand: qty='||l_qoh||', qty2='||l_sqoh, 'GET_PLANNING_ONHAND_QTY', 9);
2866 END IF;
2867
2868 x_qoh := l_qoh;
2869
2870 -- invConv changes begin
2871 IF (l_uom_ind = 'P')
2872 THEN
2873 -- This is not a DUOM item.
2874 IF(l_debug=1) THEN
2875 debug_print('Total secondary quantity on-hand: NULL', 'GET_PLANNING_ONHAND_QTY', 9);
2876 END IF;
2877 x_sqoh := NULL;
2878 ELSE
2879 x_sqoh := l_sqoh;
2880 END IF;
2881 -- invConv changes end
2882
2883
2884 EXCEPTION
2885 WHEN OTHERS THEN
2886 IF(l_debug=1) THEN
2887 debug_print(sqlcode || ', ' || sqlerrm, 'GET_PLANNING_ONHAND_QTY', 1);
2888 END IF;
2889 x_qoh := NULL;
2890 x_sqoh := NULL;
2891
2892 END GET_PLANNING_QUANTITY;
2893
2894 -- Bug 4247148: Added a new function to get the onhand qty
2895 -- This API returns the onhand quantity for planning purpose based on ATPable/Nettable/All subs
2896 -- When it is called for Organization level query, it does not include VMI quantity, because relenishment for the whole warehouse should affect VMI stock
2897 -- The quantity is calculated with onhand quantity from
2898 -- MTL_ONHAND_QUANTITIES_DETAIL and pending transactions from
2899 -- MTL_MATERIAL_TRANSACTIONS_TEMP
2900 -- The quantities does not include suggestions
2904 -- g_nettable_only => Only include nettable subinventores
2901 -- Input Parameters
2902 -- P_ONHAND_SOURCE: Whether include atpable/non-nettable subinventories
2903 -- Values: g_atpable_only => Only include atpable subinventories
2905 -- g_all_subs => Include all subinventores
2906 -- P_ORG_ID: Organization ID
2907 -- P_ITEM_ID: Item ID
2908
2909 -- Note that this may includes pending transactions that
2910 -- will keep the VMI attributes of inventory stock
2911 FUNCTION get_planning_sd_quantity
2912 (
2913 P_ONHAND_SOURCE NUMBER
2914 , P_ORG_ID NUMBER
2915 , P_ITEM_ID NUMBER
2916 ) RETURN NUMBER IS
2917
2918 x_return_status VARCHAR2(30);
2919 l_moq_qty NUMBER := 0;
2920 l_mmtt_qty_src NUMBER := 0;
2921 l_mmtt_qty_dest NUMBER := 0;
2922 l_qoh NUMBER := 0;
2923 l_lot_control NUMBER := 1;
2924 l_lpn_qty NUMBER := 0; -- bug 4189319
2925 l_default_status_id NUMBER := -1; /* Added for bug 7193862 */
2926 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2927
2928 BEGIN
2929 IF (l_debug=1) THEN
2930 inv_log_util.trace
2931 ('p_onhand_source: ' || to_char(p_onhand_source) ||
2932 ', p_org_id: ' || to_char(p_org_id) ||
2933 ', p_item_id: ' || to_char(p_item_id)
2934 , 'GET_PLANNING_SD_QTY'
2935 , 9);
2936 END IF;
2937 SELECT lot_control_code
2938 into l_lot_control
2939 from mtl_system_items_b
2940 where inventory_item_id = p_item_id
2941 and organization_id = p_org_id;
2942
2943 /* Added the below for bug 7193862 */
2944
2945 IF inv_cache.set_org_rec(p_org_id) THEN
2946 l_default_status_id := inv_cache.org_rec.default_status_id;
2947
2948 IF l_default_status_id IS NULL THEN
2949 l_default_status_id := -1;
2950 END IF;
2951 END IF;
2952
2953 IF l_default_status_id = -1 THEN
2954
2955 IF(l_debug=1) THEN
2956 debug_print('Inside non onhand status organization ', 'GET_PLANNING_SD_QTY', 9);
2957 END IF;
2958
2959 IF (p_onhand_source = g_atpable_only) THEN
2960 SELECT SUM(moq.primary_transaction_quantity)
2961 INTO l_moq_qty
2962 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
2963 WHERE moq.organization_id = p_org_id
2964 AND moq.inventory_item_id = p_item_id
2965 AND EXISTS (select 'x' from mtl_secondary_inventories msi
2966 WHERE msi.organization_id = moq.organization_id and
2967 msi.secondary_inventory_name = moq.subinventory_code
2968 AND nvl(msi.inventory_atp_code,1) = 1)
2969 AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
2970 AND moq.lot_number = mln.lot_number(+)
2971 AND moq.organization_id = mln.organization_id(+)
2972 AND moq.inventory_item_id = mln.inventory_item_id(+)
2973 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
2974 AND nvl(moq.planning_tp_type,2) = 2;
2975 ELSE
2976 SELECT SUM(moq.primary_transaction_quantity)
2977 INTO l_moq_qty
2978 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
2979 WHERE moq.organization_id = p_org_id
2980 AND moq.inventory_item_id = p_item_id
2981 AND EXISTS
2982 (select 'x' from mtl_secondary_inventories msi
2983 WHERE msi.organization_id = moq.organization_id and
2984 msi.secondary_inventory_name = moq.subinventory_code
2985 AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))
2986 AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
2987 AND moq.lot_number = mln.lot_number(+)
2988 AND moq.organization_id = mln.organization_id(+)
2989 AND moq.inventory_item_id = mln.inventory_item_id(+)
2990 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
2991 AND nvl(moq.planning_tp_type,2) = 2;
2992 END IF;
2993
2994 IF(l_debug=1) THEN
2995 inv_log_util.trace('Total MOQ quantity Org level: ' || to_char(l_moq_qty), 'GET_PLANNING_SD_QTY', 9);
2996 END IF;
2997
2998 IF (l_lot_control = 2) THEN
2999
3000 IF (p_onhand_source = g_atpable_only) THEN
3001
3002 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3003 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3004 INTO l_mmtt_qty_src
3005 FROM mtl_material_transactions_temp mmtt
3006 WHERE mmtt.organization_id = p_org_id
3007 AND mmtt.inventory_item_id = p_item_id
3008 AND mmtt.posting_flag = 'Y'
3009 AND mmtt.subinventory_code IS NOT NULL
3010 AND Nvl(mmtt.transaction_status,0) <> 2
3011 AND mmtt.transaction_action_id NOT IN (24,30)
3012 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3013 WHERE msi.organization_id = mmtt.organization_id
3014 AND msi.secondary_inventory_name = mmtt.subinventory_code
3015 AND nvl(msi.inventory_atp_code,1) = 1)
3016 AND mmtt.planning_organization_id IS NULL
3017 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3018 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3019 AND mtlt.lot_number = mln.lot_number(+)
3020 AND p_org_id = mln.organization_id(+)
3021 AND p_item_id = mln.inventory_item_id(+)
3025 ELSE
3022 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
3023 AND nvl(mmtt.planning_tp_type,2) = 2;
3024
3026
3027 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3028 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3029 INTO l_mmtt_qty_src
3030 FROM mtl_material_transactions_temp mmtt
3031 WHERE mmtt.organization_id = p_org_id
3032 AND mmtt.inventory_item_id = p_item_id
3033 AND mmtt.posting_flag = 'Y'
3034 AND mmtt.subinventory_code IS NOT NULL
3035 AND Nvl(mmtt.transaction_status,0) <> 2
3036 AND mmtt.transaction_action_id NOT IN (24,30)
3037 AND EXISTS
3038 (select 'x' from mtl_secondary_inventories msi
3039 WHERE msi.organization_id = mmtt.organization_id
3040 AND msi.secondary_inventory_name = mmtt.subinventory_code
3041 AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))
3042 AND mmtt.planning_organization_id IS NULL
3043 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3044 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3045 AND mtlt.lot_number = mln.lot_number(+)
3046 AND p_org_id = mln.organization_id(+)
3047 AND p_item_id = mln.inventory_item_id(+)
3048 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
3049 AND nvl(mmtt.planning_tp_type,2) = 2;
3050
3051 END IF;
3052
3053 IF(l_debug=1) THEN
3054 inv_log_util.trace('Total MMTT Trx quantity Source Org level (lot Controlled): ' || to_char(l_mmtt_qty_src), 'GET_PLANNING_SD_QTY', 9);
3055 END IF;
3056
3057 IF (p_onhand_source = g_atpable_only) THEN
3058
3059 SELECT SUM(Abs(mmtt.primary_quantity))
3060 INTO l_mmtt_qty_dest
3061 FROM mtl_material_transactions_temp mmtt
3062 WHERE decode(mmtt.transaction_action_id,3,
3063 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3064 AND mmtt.inventory_item_id = p_item_id
3065 AND mmtt.posting_flag = 'Y'
3066 AND Nvl(mmtt.transaction_status,0) <> 2
3067 AND mmtt.transaction_action_id in (2,28,3)
3068 AND ((mmtt.transfer_subinventory IS NULL) OR
3069 (mmtt.transfer_subinventory IS NOT NULL
3070 AND EXISTS
3071 (select 'x' from mtl_secondary_inventories msi
3072 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3073 3, mmtt.transfer_organization,mmtt.organization_id)
3074 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
3075 AND nvl(msi.inventory_atp_code,1) = 1)))
3076 AND mmtt.planning_organization_id IS NULL
3077 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3078 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3079 AND mtlt.lot_number = mln.lot_number (+)
3080 AND decode(mmtt.transaction_action_id,
3081 3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
3082 AND p_item_id = mln.inventory_item_id(+)
3083 AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
3084 AND nvl(mmtt.planning_tp_type,2) = 2;
3085
3086 ELSE
3087
3088 SELECT SUM(Abs(mmtt.primary_quantity))
3089 INTO l_mmtt_qty_dest
3090 FROM mtl_material_transactions_temp mmtt
3091 WHERE decode(mmtt.transaction_action_id,3,
3092 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3093 AND mmtt.inventory_item_id = p_item_id
3094 AND mmtt.posting_flag = 'Y'
3095 AND Nvl(mmtt.transaction_status,0) <> 2
3096 AND mmtt.transaction_action_id in (2,28,3)
3097 AND ((mmtt.transfer_subinventory IS NULL) OR
3098 (mmtt.transfer_subinventory IS NOT NULL
3099 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3100 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3101 3, mmtt.transfer_organization,mmtt.organization_id)
3102 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
3103 AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))))
3104 AND mmtt.planning_organization_id IS NULL
3105 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3106 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3107 AND mtlt.lot_number = mln.lot_number (+)
3108 AND decode(mmtt.transaction_action_id,
3109 3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
3110 AND p_item_id = mln.inventory_item_id(+)
3111 AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
3112 AND nvl(mmtt.planning_tp_type,2) = 2;
3113
3114 END IF;
3115
3116 IF(l_debug=1) THEN
3117 inv_log_util.trace('Total MMTT Trx quantity Dest Org level (lot controlled): ' || to_char(l_mmtt_qty_dest), 'GET_PLANNING_SD_QTY', 9);
3118 END IF;
3119 ELSE
3120
3121 IF (p_onhand_source = g_atpable_only) THEN
3122
3123 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3124 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3125 INTO l_mmtt_qty_src
3126 FROM mtl_material_transactions_temp mmtt
3127 WHERE mmtt.organization_id = p_org_id
3128 AND mmtt.inventory_item_id = p_item_id
3129 AND mmtt.posting_flag = 'Y'
3130 AND mmtt.subinventory_code IS NOT NULL
3131 AND Nvl(mmtt.transaction_status,0) <> 2
3132 AND mmtt.transaction_action_id NOT IN (24,30)
3133 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3134 WHERE msi.organization_id = mmtt.organization_id
3135 AND msi.secondary_inventory_name = mmtt.subinventory_code
3136 AND nvl(msi.inventory_atp_code,1) = 1)
3137 AND mmtt.planning_organization_id IS NULL
3138 AND nvl(mmtt.planning_tp_type,2) = 2;
3139
3140 ELSE
3141
3142 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3143 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3144 INTO l_mmtt_qty_src
3145 FROM mtl_material_transactions_temp mmtt
3146 WHERE mmtt.organization_id = p_org_id
3147 AND mmtt.inventory_item_id = p_item_id
3148 AND mmtt.posting_flag = 'Y'
3149 AND mmtt.subinventory_code IS NOT NULL
3150 AND Nvl(mmtt.transaction_status,0) <> 2
3151 AND mmtt.transaction_action_id NOT IN (24,30)
3152 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3153 WHERE msi.organization_id = mmtt.organization_id
3154 AND msi.secondary_inventory_name = mmtt.subinventory_code
3155 AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))
3156 AND mmtt.planning_organization_id IS NULL
3157 AND nvl(mmtt.planning_tp_type,2) = 2;
3158
3159 END IF;
3160
3161 IF(l_debug=1) THEN
3162 inv_log_util.trace('Total MMTT Trx quantity Source Org level: ' || to_char(l_mmtt_qty_src), 'GET_PLANNING_SD_QTY', 9);
3163 END IF;
3164
3165 IF (p_onhand_source = g_atpable_only) THEN
3166
3167 SELECT SUM(Abs(mmtt.primary_quantity))
3168 INTO l_mmtt_qty_dest
3169 FROM mtl_material_transactions_temp mmtt
3170 WHERE decode(mmtt.transaction_action_id,3,
3171 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3172 AND mmtt.inventory_item_id = p_item_id
3173 AND mmtt.posting_flag = 'Y'
3174 AND Nvl(mmtt.transaction_status,0) <> 2
3175 AND mmtt.transaction_action_id in (2,28,3)
3176 AND ((mmtt.transfer_subinventory IS NULL) OR
3177 (mmtt.transfer_subinventory IS NOT NULL
3178 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3179 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3180 3, mmtt.transfer_organization,mmtt.organization_id)
3181 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
3182 AND nvl(msi.inventory_atp_code,1) = 1)))
3183 AND mmtt.planning_organization_id IS NULL
3184 AND nvl(mmtt.planning_tp_type,2) = 2;
3185
3186 ELSE
3187
3188 SELECT SUM(Abs(mmtt.primary_quantity))
3189 INTO l_mmtt_qty_dest
3190 FROM mtl_material_transactions_temp mmtt
3191 WHERE decode(mmtt.transaction_action_id,3,
3192 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3193 AND mmtt.inventory_item_id = p_item_id
3194 AND mmtt.posting_flag = 'Y'
3195 AND Nvl(mmtt.transaction_status,0) <> 2
3196 AND mmtt.transaction_action_id in (2,28,3)
3197 AND ((mmtt.transfer_subinventory IS NULL) OR
3198 (mmtt.transfer_subinventory IS NOT NULL
3199 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3200 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3201 3, mmtt.transfer_organization,mmtt.organization_id)
3202 AND msi.secondary_inventory_name = mmtt.transfer_subinventory
3203 AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))))
3204 AND mmtt.planning_organization_id IS NULL
3205 AND nvl(mmtt.planning_tp_type,2) = 2;
3206
3207 END IF;
3208
3209 IF(l_debug=1) THEN
3210 inv_log_util.trace('Total MMTT Trx quantity Dest Org level: ' || to_char(l_mmtt_qty_dest), 'GET_PLANNING_SD_QTY', 9);
3211 END IF;
3212 END IF;
3213
3214 ELSE /* onhand material status check */
3215
3216
3217 IF(l_debug=1) THEN
3218 debug_print('Inside onhand status organization ', 'GET_PLANNING_SD_QTY', 9);
3219 END IF;
3220
3221 IF (p_onhand_source = g_atpable_only) THEN
3222 SELECT SUM(moq.primary_transaction_quantity)
3223 INTO l_moq_qty
3224 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
3225 WHERE moq.organization_id = p_org_id
3229 msi.secondary_inventory_name = moq.subinventory_code
3226 AND moq.inventory_item_id = p_item_id
3227 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3228 WHERE msi.organization_id = moq.organization_id and
3230 )
3231 AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
3232 AND moq.lot_number = mln.lot_number(+)
3233 AND moq.organization_id = mln.organization_id(+)
3234 AND moq.inventory_item_id = mln.inventory_item_id(+)
3235 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
3236 AND nvl(moq.planning_tp_type,2) = 2
3237 AND ((moq.status_id IS NOT NULL
3238 AND EXISTS (SELECT 1 FROM mtl_material_statuses mms
3239 WHERE status_id = moq.status_id
3240 and mms.inventory_atp_code = 1
3241 )
3242 )
3243 OR
3244 moq.status_id IS NULL
3245 );
3246 ELSE
3247 SELECT SUM(moq.primary_transaction_quantity)
3248 INTO l_moq_qty
3249 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
3250 WHERE moq.organization_id = p_org_id
3251 AND moq.inventory_item_id = p_item_id
3252 AND EXISTS
3253 (select 'x' from mtl_secondary_inventories msi
3254 WHERE msi.organization_id = moq.organization_id and
3255 msi.secondary_inventory_name = moq.subinventory_code)
3256 AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
3257 AND moq.lot_number = mln.lot_number(+)
3258 AND moq.organization_id = mln.organization_id(+)
3259 AND moq.inventory_item_id = mln.inventory_item_id(+)
3260 AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
3261 AND nvl(moq.planning_tp_type,2) = 2
3262 AND ((moq.status_id IS NOT NULL
3263 AND EXISTS (SELECT 1 FROM mtl_material_statuses mms
3264 WHERE status_id = moq.status_id
3265 and mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1)
3266 )
3267 )
3268 OR
3269 moq.status_id IS NULL
3270 );
3271
3272 END IF;
3273
3274 IF(l_debug=1) THEN
3275 inv_log_util.trace('Total MOQ quantity Org level: ' || to_char(l_moq_qty), 'GET_PLANNING_SD_QTY', 9);
3276 END IF;
3277
3278 IF (l_lot_control = 2) THEN
3279
3280 IF (p_onhand_source = g_atpable_only) THEN
3281
3282 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3283 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3284 INTO l_mmtt_qty_src
3285 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
3286 WHERE mmtt.organization_id = p_org_id
3287 AND mmtt.inventory_item_id = p_item_id
3288 AND mmtt.posting_flag = 'Y'
3289 AND mmtt.subinventory_code IS NOT NULL
3290 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
3291 AND Nvl(mmtt.transaction_status,0) <> 2
3292 AND mmtt.transaction_action_id NOT IN (24,30)
3293 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3294 WHERE msi.organization_id = mmtt.organization_id
3295 AND msi.secondary_inventory_name = mmtt.subinventory_code)
3296 AND mmtt.planning_organization_id IS NULL
3297 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3298 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3299 AND mtlt.lot_number = mln.lot_number(+)
3300 AND p_org_id = mln.organization_id(+)
3301 AND p_item_id = mln.inventory_item_id(+)
3302 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
3303 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3304 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3305 mmtt.inventory_item_id,
3306 mmtt.subinventory_code,
3307 mmtt.locator_id,
3308 mtlt.lot_number,
3309 mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
3310 AND mms.inventory_atp_code =1)
3311 AND nvl(mmtt.planning_tp_type,2) = 2;
3312
3313 ELSE
3314
3315 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3316 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3317 INTO l_mmtt_qty_src
3318 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
3319 WHERE mmtt.organization_id = p_org_id
3320 AND mmtt.inventory_item_id = p_item_id
3321 AND mmtt.posting_flag = 'Y'
3322 AND mmtt.subinventory_code IS NOT NULL
3323 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
3324 AND Nvl(mmtt.transaction_status,0) <> 2
3325 AND mmtt.transaction_action_id NOT IN (24,30)
3326 AND EXISTS
3327 (select 'x' from mtl_secondary_inventories msi
3328 WHERE msi.organization_id = mmtt.organization_id
3329 AND msi.secondary_inventory_name = mmtt.subinventory_code)
3330 AND mmtt.planning_organization_id IS NULL
3331 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3332 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3333 AND mtlt.lot_number = mln.lot_number(+)
3334 AND p_org_id = mln.organization_id(+)
3335 AND p_item_id = mln.inventory_item_id(+)
3336 AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
3340 mmtt.subinventory_code,
3337 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3338 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3339 mmtt.inventory_item_id,
3341 mmtt.locator_id,
3342 mtlt.lot_number,
3343 mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
3344 AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
3345 AND nvl(mmtt.planning_tp_type,2) = 2;
3346
3347 END IF;
3348
3349 IF(l_debug=1) THEN
3350 inv_log_util.trace('Total MMTT Trx quantity Source Org level (lot Controlled): ' || to_char(l_mmtt_qty_src), 'GET_PLANNING_SD_QTY', 9);
3351 END IF;
3352
3353 IF (p_onhand_source = g_atpable_only) THEN
3354
3355 SELECT SUM(Abs(mmtt.primary_quantity))
3356 INTO l_mmtt_qty_dest
3360 AND mmtt.inventory_item_id = p_item_id
3357 FROM mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
3358 WHERE decode(mmtt.transaction_action_id,3,
3359 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3361 AND mmtt.posting_flag = 'Y'
3362 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
3363 AND Nvl(mmtt.transaction_status,0) <> 2
3364 AND mmtt.transaction_action_id in (2,28,3)
3365 AND ((mmtt.transfer_subinventory IS NULL) OR
3366 (mmtt.transfer_subinventory IS NOT NULL
3367 AND EXISTS
3368 (select 'x' from mtl_secondary_inventories msi
3369 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3370 3, mmtt.transfer_organization,mmtt.organization_id)
3371 AND msi.secondary_inventory_name = mmtt.transfer_subinventory )))
3372 AND mmtt.planning_organization_id IS NULL
3373 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3374 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3375 AND mtlt.lot_number = mln.lot_number (+)
3376 AND decode(mmtt.transaction_action_id,
3377 3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
3378 AND p_item_id = mln.inventory_item_id(+)
3379 AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
3380 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3384 mmtt.transfer_to_location,
3381 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(decode(mmtt.transaction_action_id,3, mmtt.transfer_organization,mmtt.organization_id),
3382 mmtt.inventory_item_id,
3383 mmtt.transfer_subinventory,
3385 mtlt.lot_number,
3386 mmtt.lpn_id, mmtt.transaction_action_id,
3387 INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3388 mmtt.inventory_item_id,
3389 mmtt.subinventory_code,
3390 mmtt.locator_id,
3391 mtlt.lot_number,
3392 mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
3393 AND mms.inventory_atp_code =1)
3394 AND nvl(mmtt.planning_tp_type,2) = 2;
3395
3396 ELSE
3397
3398 SELECT SUM(Abs(mmtt.primary_quantity))
3399 INTO l_mmtt_qty_dest
3400 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
3401 WHERE decode(mmtt.transaction_action_id,3,
3402 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3403 AND mmtt.inventory_item_id = p_item_id
3404 AND mmtt.posting_flag = 'Y'
3405 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
3406 AND Nvl(mmtt.transaction_status,0) <> 2
3407 AND mmtt.transaction_action_id in (2,28,3)
3408 AND ((mmtt.transfer_subinventory IS NULL) OR
3409 (mmtt.transfer_subinventory IS NOT NULL
3410 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3411 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3412 3, mmtt.transfer_organization,mmtt.organization_id)
3413 AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
3414 AND mmtt.planning_organization_id IS NULL
3415 AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
3416 WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
3417 AND mtlt.lot_number = mln.lot_number (+)
3418 AND decode(mmtt.transaction_action_id,
3419 3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
3420 AND p_item_id = mln.inventory_item_id(+)
3421 AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
3422 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3423 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(decode(mmtt.transaction_action_id,3, mmtt.transfer_organization,mmtt.organization_id),
3424 mmtt.inventory_item_id,
3425 mmtt.transfer_subinventory,
3426 mmtt.transfer_to_location,
3427 mtlt.lot_number,
3428 mmtt.lpn_id, mmtt.transaction_action_id,
3429 INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3430 mmtt.inventory_item_id,
3431 mmtt.subinventory_code,
3432 mmtt.locator_id,
3433 mtlt.lot_number,
3434 mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
3435 AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
3436 AND nvl(mmtt.planning_tp_type,2) = 2;
3437
3438 END IF;
3439
3440 IF(l_debug=1) THEN
3441 inv_log_util.trace('Total MMTT Trx quantity Dest Org level (lot controlled): ' || to_char(l_mmtt_qty_dest), 'GET_PLANNING_SD_QTY', 9);
3442 END IF;
3443 ELSE
3444
3445 IF (p_onhand_source = g_atpable_only) THEN
3446
3447 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3448 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3449 INTO l_mmtt_qty_src
3450 FROM mtl_material_transactions_temp mmtt
3451 WHERE mmtt.organization_id = p_org_id
3452 AND mmtt.inventory_item_id = p_item_id
3453 AND mmtt.posting_flag = 'Y'
3454 AND mmtt.subinventory_code IS NOT NULL
3455 AND Nvl(mmtt.transaction_status,0) <> 2
3456 AND mmtt.transaction_action_id NOT IN (24,30)
3457 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3458 WHERE msi.organization_id = mmtt.organization_id
3459 AND msi.secondary_inventory_name = mmtt.subinventory_code)
3460 AND mmtt.planning_organization_id IS NULL
3461 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3462 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3463 mmtt.inventory_item_id,
3464 mmtt.subinventory_code,
3465 mmtt.locator_id,
3466 null,
3467 mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
3468 AND mms.inventory_atp_code =1)
3472
3469 AND nvl(mmtt.planning_tp_type,2) = 2;
3470
3471 ELSE
3473 SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
3474 Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
3475 INTO l_mmtt_qty_src
3476 FROM mtl_material_transactions_temp mmtt
3477 WHERE mmtt.organization_id = p_org_id
3478 AND mmtt.inventory_item_id = p_item_id
3479 AND mmtt.posting_flag = 'Y'
3480 AND mmtt.subinventory_code IS NOT NULL
3481 AND Nvl(mmtt.transaction_status,0) <> 2
3482 AND mmtt.transaction_action_id NOT IN (24,30)
3483 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3484 WHERE msi.organization_id = mmtt.organization_id
3485 AND msi.secondary_inventory_name = mmtt.subinventory_code)
3486 AND mmtt.planning_organization_id IS NULL
3487 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3488 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3489 mmtt.inventory_item_id,
3490 mmtt.subinventory_code,
3491 mmtt.locator_id,
3492 null,
3493 mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
3494 AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
3495 AND nvl(mmtt.planning_tp_type,2) = 2;
3496
3497 END IF;
3498
3499 IF(l_debug=1) THEN
3500 inv_log_util.trace('Total MMTT Trx quantity Source Org level: ' || to_char(l_mmtt_qty_src), 'GET_PLANNING_SD_QTY', 9);
3501 END IF;
3502
3503 IF (p_onhand_source = g_atpable_only) THEN
3504
3505 SELECT SUM(Abs(mmtt.primary_quantity))
3506 INTO l_mmtt_qty_dest
3507 FROM mtl_material_transactions_temp mmtt
3508 WHERE decode(mmtt.transaction_action_id,3,
3509 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3510 AND mmtt.inventory_item_id = p_item_id
3511 AND mmtt.posting_flag = 'Y'
3512 AND Nvl(mmtt.transaction_status,0) <> 2
3513 AND mmtt.transaction_action_id in (2,28,3)
3514 AND ((mmtt.transfer_subinventory IS NULL) OR
3515 (mmtt.transfer_subinventory IS NOT NULL
3516 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3517 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3518 3, mmtt.transfer_organization,mmtt.organization_id)
3519 AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
3520 AND mmtt.planning_organization_id IS NULL
3521 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3522 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(decode(mmtt.transaction_action_id,3, mmtt.transfer_organization,mmtt.organization_id),
3523 mmtt.inventory_item_id,
3524 mmtt.transfer_subinventory,
3525 mmtt.transfer_to_location,
3526 null,
3527 mmtt.lpn_id, mmtt.transaction_action_id,
3528 INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3529 mmtt.inventory_item_id,
3530 mmtt.subinventory_code,
3531 mmtt.locator_id,
3532 null,
3533 mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
3534 AND mms.inventory_atp_code =1)
3535 AND nvl(mmtt.planning_tp_type,2) = 2;
3536
3537 ELSE
3538
3539 SELECT SUM(Abs(mmtt.primary_quantity))
3540 INTO l_mmtt_qty_dest
3541 FROM mtl_material_transactions_temp mmtt
3542 WHERE decode(mmtt.transaction_action_id,3,
3543 mmtt.transfer_organization,mmtt.organization_id) = p_org_id
3544 AND mmtt.inventory_item_id = p_item_id
3545 AND mmtt.posting_flag = 'Y'
3546 AND Nvl(mmtt.transaction_status,0) <> 2
3547 AND mmtt.transaction_action_id in (2,28,3)
3548 AND ((mmtt.transfer_subinventory IS NULL) OR
3549 (mmtt.transfer_subinventory IS NOT NULL
3550 AND EXISTS (select 'x' from mtl_secondary_inventories msi
3551 WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3552 3, mmtt.transfer_organization,mmtt.organization_id)
3553 AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
3554 AND mmtt.planning_organization_id IS NULL
3555 AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
3556 WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(decode(mmtt.transaction_action_id,3, mmtt.transfer_organization,mmtt.organization_id),
3557 mmtt.inventory_item_id,
3558 mmtt.transfer_subinventory,
3559 mmtt.transfer_to_location,
3560 null,
3561 mmtt.lpn_id, mmtt.transaction_action_id,
3562 INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
3563 mmtt.inventory_item_id,
3564 mmtt.subinventory_code,
3565 mmtt.locator_id,
3566 null,
3567 mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
3568 AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
3569 AND nvl(mmtt.planning_tp_type,2) = 2;
3570
3571 END IF;
3572
3573 IF(l_debug=1) THEN
3574 inv_log_util.trace('Total MMTT Trx quantity Dest Org level: ' || to_char(l_mmtt_qty_dest), 'GET_PLANNING_SD_QTY', 9);
3575 END IF;
3576
3577 END IF;
3578
3579 END IF;
3580 /* End of changes for bug 7193862 */
3581
3585 ,mtrl.uom_code
3582 -- Bug 4189319, adding below query to account for undelivered LPNs for WIP assembly completions.
3583 SELECT SUM(inv_decimals_pub.get_primary_quantity( p_org_id
3584 ,p_item_id
3586 ,mtrl.quantity - NVL(mtrl.quantity_delivered,0))
3587 )
3588 INTO l_lpn_qty
3589 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mtl_transaction_types mtt
3590 where mtrl.organization_id = p_org_id
3591 AND mtrl.inventory_item_id = p_item_id
3592 AND mtrl.header_id = mtrh.header_id
3593 AND mtrh.move_order_type = 6 -- Putaway Move Order
3594 AND mtrl.transaction_source_type_id = 5 -- Wip
3595 AND mtt.transaction_action_id = 31 -- WIP Assembly Completion
3596 AND mtt.transaction_type_id = mtrl.transaction_type_id
3597 AND mtrl.line_status = 7 -- Pre Approved
3598 AND mtrl.lpn_id is not null;
3599
3600 IF(l_debug=1) THEN
3601 inv_log_util.trace('Total MTRL undelivered LPN quantity for WIP completions: ' || to_char(l_lpn_qty), 'GET_PLANNING_SD_QTY', 9);
3602 END IF;
3603
3604 -- Bug 4189319, adding undelivered LPN l_lpn_qty for WIP assembly completions in total onhand.
3605 l_qoh := nvl(l_moq_qty,0) + nvl(l_mmtt_qty_src,0) + nvl(l_mmtt_qty_dest,0) + nvl(l_lpn_qty,0);
3606
3607 IF(l_debug=1) THEN
3608 inv_log_util.trace('Total quantity on-hand: ' || to_char(l_qoh), 'GET_PLANNING_SD_QTY', 9);
3609 END IF;
3610 RETURN(l_qoh);
3611
3612
3613 EXCEPTION
3614 WHEN OTHERS THEN
3615 IF(l_debug=1) THEN
3616 inv_log_util.trace(sqlcode || ', ' || sqlerrm, 'GET_PLANNING_SD_QTY', 1);
3617 END IF;
3618 RETURN(0);
3619
3620 END GET_PLANNING_SD_QUANTITY;
3621
3622 --Bug#7001958. This procedure is forbuilding cursor with LPN
3623 --as a bind variable.
3624 PROCEDURE build_lpn_sql
3625 (
3626 x_return_status OUT NOCOPY VARCHAR2
3627 , p_mode IN INTEGER
3628 , p_is_lot_control IN BOOLEAN
3629 , p_asset_sub_only IN BOOLEAN
3630 , p_lot_expiration_date IN DATE
3631 , p_onhand_source IN NUMBER
3632 , p_pick_release IN NUMBER
3633 , x_sql_statement OUT NOCOPY long
3634 , p_is_revision_control IN BOOLEAN
3635 ) IS
3636
3637
3638 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3639 --
3640 l_stmt long;
3641 l_asset_sub_where long;
3642 l_revision_select long;
3643 l_lot_select long;
3644 l_lot_select2 long;
3645 l_lot_from long;
3646 l_lot_where long;
3647 l_lot_expiration_where long;
3648 l_lot_group long;
3649 l_onhand_source_where long;
3650 l_onhand_stmt long;
3651 l_pending_txn_stmt long;
3652 l_onhand_qty_part VARCHAR2(3000);
3653 l_mmtt_qty_part VARCHAR2(3000);
3654 l_mtlt_qty_part VARCHAR2(3000);
3655 p_n NUMBER;
3656 p_v VARCHAR2(1);
3657 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3658 BEGIN
3659
3660
3661 l_onhand_qty_part := ' moq.primary_transaction_quantity ';
3662 l_mmtt_qty_part := ' mmtt.primary_quantity ';
3663 l_mtlt_qty_part := ' mtlt.primary_quantity ';
3664
3665
3666 l_onhand_stmt := '
3667
3668 -- onhand quantities
3669 SELECT
3670 moq.organization_id organization_id
3671 , moq.inventory_item_id inventory_item_id
3672 , moq.revision revision
3673 , moq.lot_number lot_number
3674 , moq.subinventory_code subinventory_code
3675 , moq.locator_id locator_id
3676 , ' || l_onhand_qty_part || ' primary_quantity
3677 , nvl(moq.orig_date_received,
3678 moq.date_received) date_received
3679 , 1 quantity_type
3680 , moq.cost_group_id cost_group_id
3681 , decode(moq.containerized_flag,
3682 1, 1, 0) containerized
3683 , moq.planning_organization_id planning_organization_id
3684 , moq.owning_organization_id owning_organization_id
3685 , moq.lpn_id lpn_id
3686 FROM
3687 mtl_onhand_quantities_detail moq
3688 WHERE moq.organization_id <> Nvl(moq.planning_organization_id,moq.organization_id)
3689 OR moq.organization_id <> nvl(moq.owning_organization_id, moq.organization_id) ';
3690
3691 -- common restrictions
3692 IF p_asset_sub_only THEN
3693 l_asset_sub_where := '
3694 AND Nvl(sub.asset_inventory,1) = 1';
3695 ELSE
3696 l_asset_sub_where := NULL;
3697 END IF;
3698
3699 IF (p_onhand_source = g_atpable_only) THEN
3700 l_onhand_source_where := '
3701 AND Nvl(sub.inventory_atp_code, 1) = 1';
3702 ELSIF (p_onhand_source = g_nettable_only) THEN
3703 l_onhand_source_where := '
3704 AND Nvl(sub.availability_type, 1) = 1';
3705 ELSE --do nothing if g_all_subs
3706 l_onhand_source_where := NULL;
3707 END IF;
3708
3709
3710 IF p_is_lot_control THEN
3711 l_lot_select := '
3712 , x.lot_number lot_number ';
3713 l_lot_select2 := '
3714 , lot.expiration_date lot_expiration_date';
3715 l_lot_from := '
3716 , mtl_lot_numbers lot';
3720 AND x.lot_number = lot.lot_number (+) ';
3717 l_lot_where := '
3718 AND x.organization_id = lot.organization_id (+)
3719 AND x.inventory_item_id = lot.inventory_item_id (+)
3721 l_lot_group := '
3722 , x.lot_number ';
3723 ELSE
3724 l_lot_select := '
3725 , NULL lot_number';
3726 l_lot_select2 := '
3727 , To_date(NULL) lot_expiration_date';
3728 l_lot_from := NULL;
3729 l_lot_where := NULL;
3730 l_lot_group := NULL;
3731 END IF;
3732
3733
3734 IF p_is_lot_control AND p_lot_expiration_date IS NOT NULL THEN
3735 l_lot_expiration_where := '
3736 AND (lot.expiration_date IS NULL OR
3737 lot.expiration_date > :lot_expiration_date) ';
3738 ELSE
3739 l_lot_expiration_where := NULL;
3740 END IF;
3741
3742 IF p_is_revision_control THEN
3743 l_revision_select := '
3744 , x.revision revision';
3745 ELSE
3746 l_revision_select := '
3747 , NULL revision';
3748 END IF;
3749
3750
3751 l_stmt := '
3752 SELECT
3753 x.organization_id organization_id
3754 , x.inventory_item_id inventory_item_id
3755 , x.revision revision
3756 , x.lot_number lot_number '
3757 || l_lot_select2 || '
3758 , x.subinventory_code subinventory_code
3759 , sub.reservable_type reservable_type
3760 , x.locator_id locator_id
3761 , x.primary_quantity primary_quantity
3762 , x.date_received date_received
3763 , x.quantity_type quantity_type
3764 , x.cost_group_id cost_group_id
3765 , x.containerized containerized
3766 , x.planning_organization_id planning_organization_id
3767 , x.owning_organization_id owning_organization_id
3768 FROM (
3769 SELECT
3770 x.organization_id organization_id
3771 , x.inventory_item_id inventory_item_id '
3772 || l_revision_select || l_lot_select || '
3773 , x.subinventory_code subinventory_code
3774 , x.locator_id locator_id
3775 , SUM(x.primary_quantity) primary_quantity
3776 , MIN(x.date_received) date_received
3777 , x.quantity_type quantity_type
3778 , x.cost_group_id cost_group_id
3779 , x.containerized containerized
3780 , x.planning_organization_id planning_organization_id
3781 , x.owning_organization_id owning_organization_id
3782 FROM ('
3783 || l_onhand_stmt || '
3784 ) x
3785 WHERE x.organization_id = :organization_id
3786 AND x.inventory_item_id = :inventory_item_id
3787 AND x.lpn_id = :lpn_id
3788 GROUP BY
3789 x.organization_id, x.inventory_item_id, x.revision '
3790 || l_lot_group || '
3791 , x.subinventory_code, x.locator_id
3792 , x.quantity_type, x.cost_group_id, x.containerized
3793 , x.planning_organization_id, x.owning_organization_id
3794 ) x
3795 , mtl_secondary_inventories sub '
3796 || l_lot_from || '
3797 WHERE
3798 x.organization_id = sub.organization_id (+)
3799 AND x.subinventory_code = sub.secondary_inventory_name (+) '
3800 || l_lot_where || l_lot_expiration_where || l_asset_sub_where
3801 || l_onhand_source_where ;
3802
3803 x_return_status := l_return_status;
3804 x_sql_statement := l_stmt;
3805
3806
3807 EXCEPTION
3808 WHEN OTHERS THEN
3809 x_return_status := fnd_api.g_ret_sts_unexp_error;
3810 IF (l_debug = 1) THEN
3811 inv_log_util.trace('When Others Ex. in build_lpn_sql','CONSIGNED_VALIDATIONS',9);
3812 END IF;
3813 END build_lpn_sql;
3814
3815
3816 --Bug#7001958.This is overloaded with addition of lpn_id.
3817 PROCEDURE build_lpn_cursor
3818 (
3819 x_return_status OUT NOCOPY VARCHAR2
3820 , p_organization_id IN NUMBER
3821 , p_inventory_item_id IN NUMBER
3822 , p_mode IN INTEGER
3823 , p_demand_source_line_id IN NUMBER
3824 , p_is_lot_control IN BOOLEAN
3825 , p_asset_sub_only IN BOOLEAN
3826 , p_lot_expiration_date IN DATE
3827 , p_onhand_source IN NUMBER
3828 , p_pick_release IN NUMBER
3829 , p_lpn_id IN NUMBER
3830 , x_cursor OUT NOCOPY NUMBER
3831 , p_is_revision_control IN BOOLEAN
3832 ) IS
3833 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3834 l_cursor NUMBER;
3835 l_sql LONG;
3836 l_last_error_pos NUMBER;
3837 l_temp_str VARCHAR2(30);
3838 l_err VARCHAR2(240);
3839 l_pos NUMBER;
3840 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3841 BEGIN
3842 l_cursor := dbms_sql.open_cursor;
3843 IF (l_debug = 1) THEN
3844 inv_log_util.trace('Inside build_lpn_cursor','CONSIGNED_VALIDATIONS',9);
3845 END IF;
3846
3847 build_lpn_sql
3848 (l_return_status,
3849 p_mode,
3850 p_is_lot_control,
3851 p_asset_sub_only,
3852 p_lot_expiration_date,
3856 p_is_revision_control);
3853 p_onhand_source,
3854 p_pick_release,
3855 l_sql,
3857
3858 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3859 RAISE fnd_api.g_exc_unexpected_error;
3860 END IF;
3861
3862 BEGIN
3863 dbms_sql.parse(l_cursor,l_sql,dbms_sql.v7);
3864 EXCEPTION
3865 WHEN OTHERS THEN
3866 l_last_error_pos := dbms_sql.last_error_position();
3867 l_temp_str := Substr(l_sql, l_last_error_pos-5, 30);
3868 RAISE;
3869 END;
3870
3871 IF (l_debug = 1) THEN
3872 inv_log_util.trace('p_lpn_id:'||p_lpn_id||',org :'||p_organization_id ||',item:'|| p_inventory_item_id,'CONSIGNED_VALIDATIONS',9);
3873 END IF;
3874 dbms_sql.bind_variable(l_cursor, ':organization_id', p_organization_id);
3875 dbms_sql.bind_variable(l_cursor, ':inventory_item_id', p_inventory_item_id);
3876 dbms_sql.bind_variable(l_cursor, ':lpn_id', p_lpn_id );
3877
3878 IF p_is_lot_control AND p_lot_expiration_date IS NOT NULL THEN
3879 dbms_sql.bind_variable(l_cursor, ':lot_expiration_date'
3880 , p_lot_expiration_date);
3881 END IF;
3882 x_cursor := l_cursor;
3883 x_return_status := l_return_status;
3884
3885 EXCEPTION
3886 WHEN OTHERS THEN
3887 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3888 IF (l_debug = 1) THEN
3889 inv_log_util.trace('When Others Ex. in build_lpn_cursor','CONSIGNED_VALIDATIONS',9);
3890 END IF;
3891
3892 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3893 THEN
3894 fnd_msg_pub.add_exc_msg
3895 ( g_pkg_name
3896 , 'Build_Cursor'
3897 );
3898 END IF;
3899 END build_lpn_cursor;
3900
3901
3902 --Bug#7001958. This proc populates mtl_consigned_qty_temp for LPN.
3903 PROCEDURE populate_lpn_temp
3904 (
3905 p_organization_id IN NUMBER
3906 , p_inventory_item_id IN NUMBER
3907 , p_mode IN INTEGER
3908 , p_is_lot_control IN BOOLEAN
3909 , p_is_revision_control IN BOOLEAN
3910 , p_asset_sub_only IN BOOLEAN
3911 , p_lot_expiration_date IN DATE
3912 , p_demand_source_line_id IN NUMBER
3913 , p_onhand_source IN NUMBER
3914 , p_qty_tree_att IN NUMBER
3915 , p_lpn_id IN NUMBER
3916 , x_return_status OUT NOCOPY VARCHAR2
3917 ) IS
3918 l_cursor NUMBER;
3919 l_return_status VARCHAR2(1);
3920 l_revision VARCHAR2(3);
3921 l_lot_number VARCHAR2(30);
3922 l_subinventory_code VARCHAR2(10);
3923 l_lot_expiration_date DATE;
3924 l_reservable_type NUMBER;
3925 l_primary_quantity NUMBER;
3926 l_date_received DATE;
3927 l_quantity_type NUMBER;
3928 l_dummy INTEGER;
3929 l_locator_id NUMBER;
3930 l_inventory_item_id NUMBER;
3931 l_organization_id NUMBER;
3932 l_cost_group_id NUMBER;
3933 l_containerized NUMBER;
3934 l_planning_organization_id NUMBER;
3935 l_owning_organization_id NUMBER;
3936 ll_transactable_vmi NUMBER;
3937 ---- Variabls to get values from cursor
3938 lL_revision VARCHAR2(3);
3939 lL_lot_number VARCHAR2(30);
3940 lL_subinventory_code VARCHAR2(10);
3941 lL_lot_expiration_date DATE;
3942 ll_reservable_type NUMBER;
3943 ll_primary_quantity NUMBER;
3944 ll_date_received DATE;
3945 ll_quantity_type NUMBER;
3946 ll_locator_id NUMBER;
3947 ll_inventory_item_id NUMBER;
3948 ll_organization_id NUMBER;
3949 ll_cost_group_id NUMBER;
3950 ll_containerized NUMBER;
3951 ll_planning_organization_id NUMBER;
3952 ll_owning_organization_id NUMBER;
3953 --------------------------------------
3954 l_count NUMBER := 0;
3955 l_temp NUMBER := 0;
3956 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3957 BEGIN
3958
3959 build_lpn_cursor
3960 (
3961 x_return_status => l_return_status
3962 , p_organization_id => p_organization_id
3963 , p_inventory_item_id => p_inventory_item_id
3964 , p_mode => p_mode
3965 , p_demand_source_line_id => p_demand_source_line_id
3966 , p_is_lot_control => p_is_lot_control
3967 , p_is_revision_control => p_is_revision_control
3968 , p_asset_sub_only => p_asset_sub_only
3969 , p_lot_expiration_date => p_lot_expiration_date
3970 , p_onhand_source => p_onhand_source
3971 , p_pick_release => 0
3972 , p_lpn_id => p_lpn_id
3973 , x_cursor => l_cursor
3974 );
3975
3976 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3977 l_return_status:= fnd_api.g_ret_sts_error;
3978 RAISE fnd_api.g_exc_unexpected_error;
3979 END IF;
3980
3981 dbms_sql.define_column(l_cursor,1,l_organization_id);
3982 dbms_sql.define_column(l_cursor,2,l_inventory_item_id);
3983 dbms_sql.define_column(l_cursor,3,l_revision,3);
3984 dbms_sql.define_column(l_cursor,4,l_lot_number,30);
3985 dbms_sql.define_column(l_cursor,5,l_lot_expiration_date);
3986 dbms_sql.define_column(l_cursor,6,l_subinventory_code,10);
3987 dbms_sql.define_column(l_cursor,7,l_reservable_type);
3988 dbms_sql.define_column(l_cursor,8,l_locator_id);
3992 dbms_sql.define_column(l_cursor,12,l_cost_group_id);
3989 dbms_sql.define_column(l_cursor,9,l_primary_quantity);
3990 dbms_sql.define_column(l_cursor,10,l_date_received);
3991 dbms_sql.define_column(l_cursor,11,l_quantity_type);
3993 dbms_sql.define_column(l_cursor,13,l_containerized);
3994 dbms_sql.define_column(l_cursor,14,l_planning_organization_id);
3995 dbms_sql.define_column(l_cursor,15,l_owning_organization_id);
3996
3997 l_dummy := dbms_sql.execute(l_cursor);
3998
3999 LOOP
4000
4001 IF dbms_sql.fetch_rows(l_cursor) = 0 THEN
4002 EXIT;
4003 END IF;
4004
4005 l_count := l_count + 1;
4006 ll_transactable_vmi:= 0;
4007
4008 dbms_sql.column_value(l_cursor,1,ll_organization_id);
4009 dbms_sql.column_value(l_cursor,2,ll_inventory_item_id);
4010 dbms_sql.column_value(l_cursor,3,ll_revision);
4011 dbms_sql.column_value(l_cursor,4,ll_lot_number);
4012 dbms_sql.column_value(l_cursor,5,ll_lot_expiration_date);
4013 dbms_sql.column_value(l_cursor,6,ll_subinventory_code);
4014 dbms_sql.column_value(l_cursor,7,ll_reservable_type);
4015 dbms_sql.column_value(l_cursor,8,ll_locator_id);
4016 dbms_sql.column_value(l_cursor,9,ll_primary_quantity);
4017 dbms_sql.column_value(l_cursor,10,ll_date_received);
4018 dbms_sql.column_value(l_cursor,11,ll_quantity_type);
4019 dbms_sql.column_value(l_cursor,12,ll_cost_group_id);
4020 dbms_sql.column_value(l_cursor,13,ll_containerized);
4021 dbms_sql.column_value(l_cursor,14,ll_planning_organization_id);
4022 dbms_sql.column_value(l_cursor,15,ll_owning_organization_id);
4023
4024 IF (p_qty_tree_att<=ll_primary_quantity)THEN
4025 ll_transactable_vmi:=p_qty_tree_att;
4026 ELSE
4027 ll_transactable_vmi:=ll_primary_quantity;
4028 END IF;
4029
4030 INSERT INTO mtl_consigned_qty_temp (organization_id,
4031 inventory_item_id,
4032 revision,
4033 lot_number,
4034 lot_expiration_date,
4035 subinventory_code,
4036 reservable_type,
4037 locator_id,
4038 primary_quantity,
4039 transactable_vmi,
4040 date_received,
4041 quantity_type,
4042 cost_group_id,
4043 containerized,
4044 planning_organization_id,
4045 owning_organization_id)
4046 VALUES
4047 (
4048 ll_organization_id,
4049 ll_inventory_item_id,
4050 ll_revision,
4051 ll_lot_number,
4052 ll_lot_expiration_date,
4053 ll_subinventory_code,
4054 ll_reservable_type,
4055 ll_locator_id,
4056 ll_primary_quantity,
4057 ll_transactable_vmi,
4058 ll_date_received,
4059 ll_quantity_type,
4060 ll_cost_group_id,
4061 ll_containerized,
4062 ll_planning_organization_id,
4063 ll_owning_organization_id);
4064 END LOOP;
4065
4066 IF (l_debug = 1) THEN
4067 inv_log_util.trace('#of records inserted into mtl_consigned_qty_temp :'||l_count,'CONSIGNED_VALIDATIONS',9);
4068 END IF;
4069 dbms_sql.close_cursor(l_cursor);
4070 EXCEPTION
4071 WHEN OTHERS THEN
4072 IF (l_debug = 1) THEN
4073 inv_log_util.trace('When others Ex. in populate_lpn_temp','CONSIGNED_VALIDATIONS',9);
4074 END IF;
4075 END populate_lpn_temp;
4076
4077
4078 --Bug#7001958. This procedure calculates the consigned qty at LPN level.
4079 PROCEDURE GET_CONSIGNED_LPN_QUANTITY(
4080 x_return_status OUT NOCOPY VARCHAR2,
4081 x_return_msg OUT NOCOPY VARCHAR2,
4082 p_tree_mode IN NUMBER,
4083 p_organization_id IN NUMBER,
4084 p_owning_org_id IN NUMBER,
4085 p_planning_org_id IN NUMBER,
4086 p_inventory_item_id IN NUMBER,
4087 p_is_revision_control IN VARCHAR2,
4088 p_is_lot_control IN VARCHAR2,
4089 p_is_serial_control IN VARCHAR2,
4090 p_revision IN VARCHAR2,
4091 p_lot_number IN VARCHAR2,
4092 p_lot_expiration_date IN DATE,
4093 p_subinventory_code IN VARCHAR2,
4094 p_locator_id IN NUMBER,
4095 p_source_type_id IN NUMBER,
4096 p_demand_source_line_id IN NUMBER,
4097 p_demand_source_header_id IN NUMBER,
4098 p_demand_source_name IN VARCHAR2,
4099 p_onhand_source IN NUMBER,
4100 p_cost_group_id IN NUMBER,
4101 p_query_mode IN NUMBER,
4102 p_lpn_id IN NUMBER,
4103 x_qoh OUT NOCOPY NUMBER,
4104 x_att OUT NOCOPY NUMBER) IS
4105
4106 l_msg_count VARCHAR2(100);
4107 l_msg_data VARCHAR2(1000);
4108 l_is_revision_control BOOLEAN := FALSE;
4109 l_is_lot_control BOOLEAN := FALSE;
4110 l_is_serial_control BOOLEAN := FALSE;
4111 l_tree_mode NUMBER;
4112 l_table_count NUMBER := 0;
4113
4114 l_qoh NUMBER;
4118 l_atr NUMBER;
4115 l_rqoh NUMBER;
4116 l_qr NUMBER;
4117 l_qs NUMBER;
4119 l_att NUMBER;
4120 l_vcoh NUMBER;
4121 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4122 BEGIN
4123
4124 IF (l_debug = 1) THEN
4125 inv_log_util.trace('****** GET_CONSIGNED_LPN_QUANTITIES *******','CONSIGNED_VALIDATIONS',9);
4126 inv_log_util.trace(' Org, Owning_org, planning_org='|| p_organization_id ||','
4127 || p_owning_org_id ||','||p_planning_org_id,'CONSIGNED_VALIDATIONS',9);
4128 inv_log_util.trace(' Item, Is Rev, Lot, Serial controlled: '||p_inventory_item_id|| ','||
4129 p_is_revision_control ||','|| p_is_lot_control ||','|| p_is_serial_control,'CONSIGNED_VALIDATIONS',9);
4130 inv_log_util.trace(' Rev, Lot, LotExpDate: '|| p_revision ||','||p_lot_number ||','|| p_lot_expiration_date,'CONSIGNED_VALIDATIONS',9);
4131 inv_log_util.trace(' Sub, Loc: '||p_subinventory_code||','||p_locator_id,'CONSIGNED_VALIDATIONS',9);
4132 inv_log_util.trace(' SourceTypeID, DemdSrcLineID, DemdSrcHdrID, DemdSrcName: ' ||
4133 p_source_type_id ||',' ||p_demand_source_line_id || ','||
4134 p_demand_source_header_id || ',' || p_demand_source_name,'CONSIGNED_VALIDATIONS',9);
4135 inv_log_util.trace(' OnhandSource, CstGroupID, QueryMode: '|| p_onhand_source || ','||
4136 p_cost_group_id ||',' ||p_query_mode||',p_lpn_id :'||p_lpn_id,'CONSIGNED_VALIDATIONS',9);
4137 END IF;
4138
4139 x_return_status:= fnd_api.g_ret_sts_success;
4140
4141 l_tree_mode := p_tree_mode;
4142
4143 -- validate demand source info
4144 IF p_tree_mode IN (g_transaction_mode, g_loose_only_mode) THEN
4145 IF p_source_type_id IS NULL THEN
4146 fnd_message.set_name('INV', 'INV-MISSING DEMAND SOURCE TYPE');
4147 fnd_msg_pub.ADD;
4148 x_return_msg := fnd_message.get;
4149 RAISE fnd_api.g_exc_error;
4150 END IF;
4151
4152 IF p_demand_source_header_id IS NULL THEN
4153 IF p_demand_source_name IS NULL THEN
4154 fnd_message.set_name('INV', 'INV-MISSING DEMAND SRC HEADER');
4155 fnd_msg_pub.ADD;
4156 x_return_msg := fnd_message.get;
4157 RAISE fnd_api.g_exc_error;
4158 END IF;
4159 END IF;
4160
4161 IF p_demand_source_header_id IS NULL
4162 AND p_demand_source_line_id IS NOT NULL THEN
4163 fnd_message.set_name('INV', 'INV-MISSING DEMAND SRC HEADER');
4164 fnd_msg_pub.ADD;
4165 x_return_msg := fnd_message.get;
4166 RAISE fnd_api.g_exc_error;
4167 END IF;
4168 END IF;
4169
4170 IF (Upper(p_is_revision_control) = 'TRUE') OR (Upper(p_is_revision_control)=fnd_api.g_true) THEN
4171 l_is_revision_control := TRUE;
4172 END IF;
4173
4174 IF (Upper(p_is_lot_control) = 'TRUE') OR (Upper(p_is_lot_control)=fnd_api.g_true) THEN
4175 l_is_lot_control := TRUE;
4176 END IF;
4177
4178 IF (Upper(p_is_serial_control) = 'TRUE') OR (Upper(p_is_serial_control) = fnd_api.g_true) THEN
4179 l_is_serial_control := TRUE;
4180 END IF;
4181
4182 /* Validate input parameters */
4183 IF (p_inventory_item_id IS NULL) THEN
4184 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
4185 fnd_msg_pub.ADD;
4186 x_return_msg := fnd_message.get;
4187 RAISE fnd_api.g_exc_unexpected_error;
4188 END IF ;
4189
4190 IF (p_query_mode = G_TXN_MODE) THEN
4191 IF (p_owning_org_id IS NULL AND p_planning_org_id IS NULL) THEN
4192 fnd_message.set_name('INV', 'INV_OWN_PLAN_ORG_REQUIRED');
4193 fnd_msg_pub.ADD;
4194 x_return_msg := fnd_message.get;
4195 RAISE fnd_api.g_exc_unexpected_error;
4196 END IF ;
4197 ELSIF (p_query_mode = G_REG_MODE) THEN
4198 IF (p_owning_org_id IS NULL) THEN
4199 fnd_message.set_name('INV', 'INV_OWN_ORG_REQUIRED');
4200 fnd_msg_pub.ADD;
4201 x_return_msg := fnd_message.get;
4202 RAISE fnd_api.g_exc_unexpected_error;
4203 END IF ;
4204 END IF;
4205
4206 IF (l_debug = 1) THEN
4207 inv_log_util.trace('Done with validations','CONSIGNED_VALIDATIONS',9);
4208 END IF;
4209 IF (p_query_mode = G_REG_MODE) THEN
4210
4211 IF (l_debug = 1) THEN
4212 inv_log_util.trace('Transfer regular to consigned','CONSIGNED_VALIDATIONS',9);
4213 END IF;
4214 SELECT Nvl(sum(primary_transaction_quantity),0) INTO x_att
4215 FROM mtl_onhand_quantities_detail
4216 WHERE owning_organization_id = organization_id
4217 AND organization_id = p_organization_id
4218 AND owning_organization_id <> p_owning_org_id
4219 AND inventory_item_id = p_inventory_item_id
4220 AND nvl(revision,'@@@') = nvl(p_revision, nvl(revision,'@@@'))
4221 AND nvl(lot_number, '@@@') = nvl(p_lot_number, nvl(lot_number, '@@@'))
4222 AND subinventory_code = nvl(p_subinventory_code, subinventory_code)
4226
4223 AND nvl(locator_id, -999) = nvl(p_locator_id, nvl(locator_id, -999))
4224 AND nvl(lpn_id , -999) = nvl(p_lpn_id , -999)
4225 AND nvl(cost_group_id, -999) = nvl(p_cost_group_id, nvl(cost_group_id, -999));
4227 x_qoh := x_att;
4228 IF (l_debug = 1) THEN
4229 inv_log_util.trace('Got qty, x_qoh=x_att='||x_att,'CONSIGNED_VALIDATIONS',9);
4230 END IF;
4231
4232 RETURN;
4233 END IF;
4234
4235 --Use Exists to check existance
4236 l_table_count := 0;
4237 BEGIN
4238 SELECT 1 INTO l_table_count FROM dual
4239 WHERE EXISTS (SELECT 1 FROM mtl_consigned_qty_temp
4240 WHERE inventory_item_id = p_inventory_item_id
4241 AND organization_id = p_organization_id);
4242 EXCEPTION
4243 WHEN others THEN
4244 l_table_count:=0;
4245 END;
4246
4247 -- Clear the already existing cache only if for this item and org no table
4248 -- exists.
4249 IF (l_table_count = 0) THEN
4250 IF (l_debug = 1) THEN
4251 inv_log_util.trace('calling populate_lpn_temp','CONSIGNED_VALIDATIONS',9);
4252 END IF;
4253
4254 populate_lpn_temp(
4255 p_organization_id => p_organization_id
4256 , p_inventory_item_id => p_inventory_item_id
4257 , p_mode => l_tree_mode
4258 , p_is_lot_control => l_is_lot_control
4259 , p_is_revision_control => l_is_revision_control
4260 , p_asset_sub_only => null
4261 , p_lot_expiration_date => null
4262 , p_demand_source_line_id => p_demand_source_line_id
4263 , p_onhand_source => p_onhand_source
4264 , p_lpn_id => p_lpn_id
4265 , p_qty_tree_att => x_att
4266 , x_return_status => x_return_status) ;
4267
4268 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4269 IF (l_debug = 1) THEN
4270 inv_log_util.trace('populate_lpn_temp Failed','CONSIGNED_VALIDATIONS',9);
4271 END IF;
4272 RAISE fnd_api.g_exc_unexpected_error;
4273 END IF;
4274 IF (l_debug = 1) THEN
4275 inv_log_util.trace('after populate_lpn_temp x_att'||x_att,'CONSIGNED_VALIDATIONS',9);
4276 END IF;
4277
4278 END IF;
4279
4280 IF (l_debug = 1) THEN
4281 inv_log_util.trace('Query consigned temp table for l_vcoh','CONSIGNED_VALIDATIONS',9);
4282 END IF;
4283
4284 SELECT Nvl(sum(primary_quantity),0) INTO l_vcoh
4285 FROM mtl_consigned_qty_temp
4286 WHERE organization_id = p_organization_id
4287 AND inventory_item_id = p_inventory_item_id
4288 AND Nvl(planning_organization_id, -999) = Nvl(p_planning_org_id,Nvl(planning_organization_id, -999))
4289 AND Nvl(owning_organization_id, -999) = Nvl(p_owning_org_id,Nvl(owning_organization_id, -999))
4290 AND containerized = 1
4291 AND Nvl(revision,'@@@') = Nvl(p_revision,'@@@')
4292 AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,'@@@')
4293 AND subinventory_code = p_subinventory_code
4294 AND locator_id = p_locator_id ;
4295
4296 IF (l_debug = 1) THEN
4297 inv_log_util.trace('Got l_vcoh='||l_vcoh,'CONSIGNED_VALIDATIONS',9);
4298 END IF;
4299
4300 IF (p_query_mode = G_TXN_MODE) THEN
4301
4302 -- Call the quantity tree
4303 -- This API calls the public qty tree api to create and query the tree
4304 --togethor. The created tree is stored in the memory as a PL/SQL table.
4305 IF (l_debug = 1) THEN
4306 inv_log_util.trace('Transaction Mode, calling quantity tree','CONSIGNED_VALIDATIONS',9);
4307 END IF;
4308 inv_quantity_tree_pub.query_quantities(
4309 p_api_version_number => 1.0
4310 , p_init_msg_lst => fnd_api.g_false
4311 , x_return_status => x_return_status
4312 , x_msg_count => l_msg_count
4313 , x_msg_data => l_msg_data
4314 , p_organization_id => p_organization_id
4315 , p_inventory_item_id => p_inventory_item_id
4316 , p_tree_mode => l_tree_mode
4317 , p_is_revision_control => l_is_revision_control
4318 , p_is_lot_control => l_is_lot_control
4319 , p_is_serial_control => l_is_serial_control
4320 , p_demand_source_type_id => p_source_type_id
4321 , p_demand_source_line_id => p_demand_source_line_id
4322 , p_demand_source_header_id => p_demand_source_header_id
4323 , p_demand_source_name => p_demand_source_name
4324 , p_revision => p_revision
4325 , p_lot_number => p_lot_number
4326 , p_lot_expiration_date => NULL
4327 , p_subinventory_code => p_subinventory_code
4328 , p_locator_id => p_locator_id
4329 , p_lpn_id => p_lpn_id
4330 , p_cost_group_id => p_cost_group_id
4331 , x_qoh => l_qoh
4332 , x_rqoh => l_rqoh
4333 , x_qr => l_qr
4334 , x_qs => l_qs
4335 , x_att => l_att
4336 , x_atr => l_atr
4337 );
4338
4339 -- If the qty tree returns and error raise an exception.
4340 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4341 IF (l_debug = 1) THEN
4342 inv_log_util.trace('Qty Tree Failed'||l_msg_data,'CONSIGNED_VALIDATIONS',9);
4343 END IF;
4344 x_return_msg:= l_msg_data;
4345 RAISE fnd_api.g_exc_unexpected_error;
4346 END IF;
4347
4348 IF (l_debug = 1) THEN
4349 inv_log_util.trace('Called qty tree, l_qoh='||l_qoh||',l_att='||l_att,'CONSIGNED_VALIDATIONS',9);
4350 inv_log_util.trace('Comparing with l_vcoh='||l_vcoh,'CONSIGNED_VALIDATIONS',9);
4351 END IF;
4352 --consign/VMI att is min of qty tree att and vmi/consigned onhand.
4353 IF (l_vcoh <= l_att) THEN
4354 x_att:= l_vcoh;
4355 ELSE
4356 x_att:= l_att;
4357 END IF;
4358 x_qoh := l_vcoh;
4359
4360 ELSIF (p_query_mode = G_XFR_MODE) THEN
4361 x_att := l_vcoh;
4362 x_qoh := x_att;
4363 IF (l_debug = 1) THEN
4364 inv_log_util.trace('Transfer mode, x_qoh=x_att=l_vcoh='||x_att,'CONSIGNED_VALIDATIONS',9);
4365 END IF;
4366
4367 END IF;
4368
4369 x_return_status:= fnd_api.g_ret_sts_success;
4370
4371 EXCEPTION
4372 when others THEN
4373 IF (l_debug = 1) THEN
4374 inv_log_util.trace('When others Exception in GET_CONSIGNED_LPN_QUANTITY','CONSIGNED_VALIDATIONS',9);
4375 END IF;
4376 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
4377 RETURN;
4378 END GET_CONSIGNED_LPN_QUANTITY;
4379
4380
4381 END INV_CONSIGNED_VALIDATIONS;