1 PACKAGE BODY inv_comingling_utils AS
2 /* $Header: INVCOMUB.pls 120.4 2005/12/06 11:32:34 arsawant noship $ */
3
4
5
6 PROCEDURE print_debug(p_message IN VARCHAR2) IS
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 BEGIN
9 IF (l_debug = 1) THEN
10 inv_log_util.trace(p_message, 'INV_COMINGLING_UTILS', 9);
11 END IF;
12 END;
13
14
15 /*
16 ** -------------------------------------------------------------------------
17 ** Procedure: comingle_check
18 ** Description:
19 ** Output:
20 ** x_return_status
21 ** return status indicating success, error, unexpected error
22 ** x_msg_count
23 ** number of messages in message list
24 ** x_msg_data
25 ** if the number of messages in message list is 1, contains
26 ** message text
27 ** x_comingling_occurs
28 ** Y: Co-mingling occurs as a result of transaction
29 ** N: Co-mingling does not occur as a result of transaction
30 ** x_count
31 ** Minimum Number of co-mingling instances for given data
32 ** Input:
33 ** p_organization_id number
34 ** Organization where cost group assignment/transaction occurs
35 ** For receipts, this will be the source organization,
36 ** For subinventory and staging transfers, this will be the source organization.
37 ** (Source Organization = Destination Organization)
38 ** For inter-organization transfers, this will be transfer organization
39 ** (Source Organization <> Destination Organization)
40 ** p_inventory_item_id number
41 ** Identifier of item involved in cost group assignment/transaction
42 ** p_revision varchar2
43 ** Revision of item involved
44 ** p_lot_number varchar2
45 ** Lot number of item
46 ** p_subinventory_code varchar2
47 ** Subinventory where the transaction occurs
48 ** For receipts, this will be source subinventory
49 ** For subinventory, staging and inter-organization transfers,
50 ** this will be transfer subinventory
51 ** p_locator_id number
52 ** Locator where the transaction occurs
53 ** For receipts, this will be source locator
54 ** For subinventory, staging and inter-organization transfers,
55 ** this will be transfer locator
56 ** p_lpn_id number
57 ** LPN into which material is packed
58 ** p_cost_group_id number
59 ** identifier of cost group that is used in the transaction
60 **
61 **
62 ** transaction actions
63 **
64 ** Issue from stores 1 inv_globals.G_Action_Issue
65 ** Subinventory Xfers 2 inv_globals.G_Action_Subxfr
66 ** Direct Org Xfers 3 inv_globals.G_Action_Orgxfr
67 ** Intransit Shipment 21 inv_globals.G_Action_IntransitShipment
68 ** Staging Xfers 28 inv_globals.G_Action_Stgxfr
69 ** Delivery Adjustments 29 inv_globals.G_Action_DeliveryAdj
70 ** Assembly Return 32 inv_globals.G_Action_AssyReturn
71 ** Negative Component Return 34 inv_globals.G_Action_NegCompReturn
72 **
73 ** Returns:
74 ** none
75 ** --------------------------------------------------------------------------
76 */
77
78 procedure comingle_check(
79 x_return_status OUT NOCOPY VARCHAR2
80 , x_msg_count OUT NOCOPY NUMBER
81 , x_msg_data OUT NOCOPY VARCHAR2
82 , x_comingling_occurs OUT NOCOPY VARCHAR2
83 , x_count OUT NOCOPY NUMBER
84 , p_organization_id IN NUMBER
85 , p_inventory_item_id IN NUMBER
86 , p_revision IN VARCHAR2
87 , p_lot_number IN VARCHAR2
88 , p_subinventory_code IN VARCHAR2
89 , p_locator_id IN NUMBER
90 , p_lpn_id IN NUMBER
91 , p_cost_group_id IN NUMBER)
92 as
93 -- l_moq_count number := 0;
94 -- l_mmtt_receipts_count number := 0;
95 -- l_mmtt_transfers_count number := 0;
96 -- l_lpn_contents_count number := 0;
97 -- l_mmtt_lpn_receipts_count number := 0;
98 -- l_serial_count number := 0;
99 --BUG 2921882 Changing the count(*) to existence for performance improvement
100 l_moq_exist VARCHAR2(1) := 'N';
101 l_mmtt_receipts_exist VARCHAR2(1) := 'N';
102 l_mmtt_transfers_exist VARCHAR2(1) := 'N';
103 l_lpn_contents_exist VARCHAR2(1) := 'N';
104 l_mmtt_lpn_receipts_exist VARCHAR2(1) := 'N';
105 l_serial_item VARCHAR2(1) := 'N';
106 begin
107 x_return_status := fnd_api.g_ret_sts_success;
108
109 if p_cost_group_id is null then
110 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
111 fnd_msg_pub.add;
112 RAISE fnd_api.g_exc_error;
113 end if;
114
115 BEGIN
116 SELECT 'Y' INTO l_serial_item
117 FROM dual
118 WHERE
119 exists
120 (--select count(*)
121 --into l_serial_count
122 SELECT inventory_item_id
123 from mtl_system_items
124 where organization_id = p_organization_id
125 and inventory_item_id = p_inventory_item_id
126 and serial_number_control_code NOT IN (1, 6)); --serial controlled items
127 EXCEPTION
128 WHEN no_data_found THEN
129 l_serial_item := 'N';
130 END;
131
132 if l_serial_item = 'Y' then
133 x_comingling_occurs := 'N';
134 return;
135 end if;
136
137 if (p_lpn_id is null) then /* Non LPN transaction */
138 /*
139 ** Look at MTL_ONHAND_QUANTIES, the on hand table
140 */
141 BEGIN
142 SELECT 'Y' INTO l_moq_exist
143 FROM dual
144 WHERE
145 exists
146 (SELECT organization_id
147 --BUG 2921882
148 --Changing the count(*) to existence for performance improvement
149 --select
150 --count(*)
151 --into l_moq_count
152 from mtl_onhand_quantities_detail
153 where organization_id = p_organization_id
154 AND inventory_item_id = p_inventory_item_id
155 AND (revision = p_revision
156 OR revision is null and p_revision is null)
157 AND (lot_number = p_lot_number
158 OR lot_number is null and p_lot_number is null)
159 AND subinventory_code = p_subinventory_code
160 AND (locator_id = p_locator_id
161 OR locator_id is null and p_locator_id is null)
162 AND cost_group_id is not null
163 AND cost_group_id <> p_cost_group_id
164 AND containerized_flag = 2 -- (loose material)
165 );
166 EXCEPTION
167 WHEN no_data_found THEN
168 l_moq_exist := 'N';
169 END;
170
171 if (l_moq_exist = 'Y') then
172 x_count := 1;
173 x_comingling_occurs := 'Y';
174 return;
175 end if;
176
177 /*
178 ** Look at MTL_MATERIAL_TRANSACTIONS_TEMP, Pending transactions and Suggestions table
179 **
180 ** For MTL_MATERIAL_TRANSACTIONS_TEMP records - Suggestions and Pending Transactions,
181 ** only receipts are considered.
182 */
183
184 /*
185 ** Suggestions, Pending Transactions - Receipts
186 **
187 ** Following Transactions Actions are Issues
188 ** Issue from stores 1
189 ** Subinventory Xfers 2
190 ** Direct Org Xfers 3
191 ** Intransit Shipment 21
192 ** Staging Xfers 28
193 ** Delivery Adjustments 29
194 ** Assembly Return 32
195 ** Negative Component Return 34
196 **
197 */
198 if (p_lot_number is null) then
199 BEGIN
200 SELECT 'Y' INTO
201 l_mmtt_receipts_exist FROM dual
202 WHERE
203 exists
204 (SELECT organization_id
205 --BUG 2921882
206 --Changing the count(*) to existence for performance improvement
207 --select
208 --count(*)
209 --into l_mmtt_receipts_count
210 from mtl_material_transactions_temp
211 where organization_id = p_organization_id
212 AND inventory_item_id = p_inventory_item_id
213 AND (revision = p_revision
214 OR revision is null and p_revision is null)
215 AND lot_number is null
216 AND subinventory_code = p_subinventory_code
217 AND (locator_id = p_locator_id
218 OR locator_id is null and p_locator_id is null)
219 AND cost_group_id is not null
220 AND cost_group_id <> p_cost_group_id
221 AND transaction_action_id not in (inv_globals.G_Action_Issue,
222 inv_globals.G_Action_Subxfr,
223 inv_globals.G_Action_Orgxfr,
224 inv_globals.G_Action_IntransitShipment,
225 inv_globals.G_Action_Stgxfr,
226 inv_globals.G_Action_DeliveryAdj,
227 inv_globals.G_Action_AssyReturn,
228 inv_globals.G_Action_NegCompReturn)
229 AND posting_flag = 'Y');
230 EXCEPTION
231 WHEN no_data_found THEN
232 l_mmtt_receipts_exist := 'N';
233 END;
234
235
236 else /* Lot Controlled Item transaction */
237
238 BEGIN
239 SELECT 'Y' INTO l_mmtt_receipts_exist
240 FROM dual
241 WHERE
242 exists
243 (SELECT mmtt.organization_id --Bug 4496965
244 --BUG 2921882
245 --Changing the count(*) to existence for performance improvement
246 --select
247 --count(*)
248 --into l_mmtt_receipts_count
249 from mtl_material_transactions_temp mmtt,
250 mtl_transaction_lots_temp mtlt
251 where mmtt.organization_id = p_organization_id
252 AND mmtt.inventory_item_id = p_inventory_item_id
253 AND (mmtt.revision = p_revision
254 OR mmtt.revision is null and p_revision is null)
255 AND (mtlt.lot_number = p_lot_number
256 and mtlt.transaction_temp_id = mmtt.transaction_temp_id)
257 AND mmtt.subinventory_code = p_subinventory_code
258 AND (mmtt.locator_id = p_locator_id
259 OR mmtt.locator_id is null and p_locator_id is null)
260 AND mmtt.cost_group_id is not null
261 AND mmtt.cost_group_id <> p_cost_group_id
262 AND transaction_action_id not in (inv_globals.G_Action_Issue,
263 inv_globals.G_Action_Subxfr,
264 inv_globals.G_Action_Orgxfr,
265 inv_globals.G_Action_IntransitShipment,
266 inv_globals.G_Action_Stgxfr,
267 inv_globals.G_Action_DeliveryAdj,
268 inv_globals.G_Action_AssyReturn,
269 inv_globals.G_Action_NegCompReturn)
270 AND mmtt.posting_flag = 'Y');
271 EXCEPTION
272 WHEN no_data_found THEN
273 l_mmtt_receipts_exist := 'N';
274 END;
275
276 end if;
277
278 IF (l_mmtt_receipts_exist = 'Y') THEN
279 x_count := 1;--l_mmtt_receipts_count
280 x_comingling_occurs := 'Y';
281 return;
282 END IF;
283
284 /*
285 ** Suggestions, Pending Transactions - Transfers - Destination Side
286 **
287 ** Following Transactions Actions are Transfers
288 ** Subinventory Xfers 2
289 ** Direct Org Xfers 3
290 ** Staging Xfers 28
291 **
292 */
293
294 IF (p_lot_number is null) THEN
295 --Splitting the query for bug 2921882
296 IF p_locator_id IS NULL THEN
297 BEGIN
298 SELECT 'Y' INTO l_mmtt_transfers_exist
299 FROM dual
300 WHERE
301 exists
302 (SELECT organization_id
303 --BUG 2921882
304 --Changing the count(*) to existence for performance improvement
305 --select
306 --count(*)
307 --into l_mmtt_transfers_count
308 from mtl_material_transactions_temp
309 where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, organization_id)= p_organization_id
310 and inventory_item_id = p_inventory_item_id
311 and (revision = p_revision
312 OR revision is null and p_revision is null)
313 AND lot_number is null
314 AND transfer_subinventory = p_subinventory_code
315 AND transfer_to_location IS null
316 AND transfer_cost_group_id is not null
317 AND transfer_cost_group_id <> p_cost_group_id
318 AND transaction_action_id in (inv_globals.G_Action_Subxfr,
319 inv_globals.G_Action_Orgxfr,
320 inv_globals.G_Action_Stgxfr)
321 AND posting_flag = 'Y');
322 EXCEPTION
323 WHEN no_data_found THEN
324 l_mmtt_transfers_exist := 'N';
325 END;
326 ELSE
327 BEGIN
328 SELECT 'Y' INTO l_mmtt_transfers_exist
329 FROM dual
330 WHERE
331 exists
332 (SELECT organization_id
333 --BUG 2921882
334 --Changing the count(*) to existence for performance improvement
335 --select
336 --count(*)
337 --into l_mmtt_transfers_count
338 from mtl_material_transactions_temp
339 where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, organization_id)= p_organization_id
340 and inventory_item_id = p_inventory_item_id
341 and (revision = p_revision
342 OR revision is null and p_revision is null)
343 AND lot_number is null
344 AND transfer_subinventory = p_subinventory_code
345 AND transfer_to_location = p_locator_id
346 AND transfer_cost_group_id is not null
347 AND transfer_cost_group_id <> p_cost_group_id
348 AND transaction_action_id in (inv_globals.G_Action_Subxfr,
349 inv_globals.G_Action_Orgxfr,
350 inv_globals.G_Action_Stgxfr)
351 AND posting_flag = 'Y');
352 EXCEPTION
353 WHEN no_data_found THEN
354 l_mmtt_transfers_exist := 'N';
355 END;
356 END IF;
357
358 else /* Lot Controlled Item transaction */
359
360 IF p_locator_id IS NULL then
361 BEGIN
362 SELECT 'Y' INTO l_mmtt_transfers_exist
363 FROM dual
364 WHERE
365 exists
366 (SELECT mmtt.organization_id --Bug 4496965
367 --BUG 2921882
368 --Changing the count(*) to existence for performance improvement
369 --select
370 --count(*)
371 --into l_mmtt_transfers_count
372 from mtl_material_transactions_temp mmtt,
373 mtl_transaction_lots_temp mtlt
374 where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, mmtt.organization_id)= p_organization_id
375 AND mmtt.inventory_item_id = p_inventory_item_id
376 AND (mmtt.revision = p_revision
377 OR mmtt.revision is null and p_revision is null)
378 AND (mtlt.lot_number = p_lot_number
379 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id)
380 AND mmtt.transfer_subinventory = p_subinventory_code
381 AND mmtt.transfer_to_location IS null
382 AND mmtt.transfer_cost_group_id is not null
383 AND mmtt.transfer_cost_group_id <> p_cost_group_id
384 AND transaction_action_id in (inv_globals.G_Action_Subxfr,
385 inv_globals.G_Action_Orgxfr,
386 inv_globals.G_Action_Stgxfr)
387 AND posting_flag = 'Y');
388 EXCEPTION
389 WHEN no_data_found THEN
390 l_mmtt_transfers_exist := 'N';
391 END;
392 ELSE
393 BEGIN
394 SELECT 'Y' INTO l_mmtt_transfers_exist
395 FROM dual
396 WHERE
397 exists
398 (SELECT mmtt.organization_id --Bug 4496965
399 --BUG 2921882
400 --Changing the count(*) to existence for performance improvement
401 --select
402 --count(*)
403 --into l_mmtt_transfers_count
404 from mtl_material_transactions_temp mmtt,
405 mtl_transaction_lots_temp mtlt
406 where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, mmtt.organization_id)= p_organization_id
407 AND mmtt.inventory_item_id = p_inventory_item_id
408 AND (mmtt.revision = p_revision
409 OR mmtt.revision is null and p_revision is null)
410 AND (mtlt.lot_number = p_lot_number
411 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id)
412 AND mmtt.transfer_subinventory = p_subinventory_code
413 AND mmtt.transfer_to_location = p_locator_id
414 AND mmtt.transfer_cost_group_id is not null
415 AND mmtt.transfer_cost_group_id <> p_cost_group_id
416 AND transaction_action_id in (inv_globals.G_Action_Subxfr,
417 inv_globals.G_Action_Orgxfr,
418 inv_globals.G_Action_Stgxfr)
419 AND posting_flag = 'Y');
420 EXCEPTION
421 WHEN no_data_found THEN
422 l_mmtt_transfers_exist := 'N';
423 END;
424 END IF;
425 END IF;
426
427 IF (l_mmtt_transfers_exist = 'Y') THEN
428 x_count := 1;--l_mmtt_transfers_count;
429 x_comingling_occurs := 'Y';
430 return;
431 END IF;
432
433 else /* LPN transaction */
434 /*
435 ** Look at WMS_LPN_CONTENTS, lpn content details table
436 */
437
438 BEGIN
439 SELECT 'Y' INTO l_lpn_contents_exist
440 FROM dual
441 WHERE
442 exists
443 (SELECT organization_id
444 --BUG 2921882
445 --Changing the count(*) to existence for performance improvement
446 --select
447 --count(*)
448 --into l_lpn_contents_count
449 from wms_lpn_contents
450 where organization_id = p_organization_id
451 AND inventory_item_id = p_inventory_item_id
452 AND (revision = p_revision
453 OR revision is null and p_revision is null)
454 AND (lot_number = p_lot_number
455 OR lot_number is null and p_lot_number is null)
456 AND cost_group_id is not null
457 AND cost_group_id <> p_cost_group_id
458 AND parent_lpn_id = p_lpn_id);
459 EXCEPTION
460 WHEN no_data_found THEN
461 l_lpn_contents_exist := 'N';
462 END;
463
464 IF ( l_lpn_contents_exist = 'Y') THEN
465 x_count := 1;--l_lpn_contents_count
466 x_comingling_occurs := 'Y';
467 return;
468 END IF;
469
470 /*
471 ** Look at MTL_MATERIAL_TRANSACTIONS_TEMP, Pending transactions and Suggestions table
472 ** for pending pack transactions and suggestions.
473 **
474 ** For MTL_MATERIAL_TRANSACTIONS_TEMP LPN records - Suggestions and Pending
475 ** Transactions, only pack transactions are considered.
476 */
477
478 IF (p_lot_number is null) THEN
479 BEGIN
480 SELECT 'Y' INTO l_mmtt_lpn_receipts_exist
481 FROM dual
482 WHERE
483 exists
484 (SELECT organization_id
485 --BUG 2921882
486 --Changing the count(*) to existence for performance improvement
487 --select
488 --count(*)
489 --into l_mmtt_lpn_receipts_count
490 from mtl_material_transactions_temp
491 where organization_id = p_organization_id
492 AND inventory_item_id = p_inventory_item_id
493 AND (revision = p_revision
494 OR revision is null and p_revision is null)
495 AND lot_number is null
496 and subinventory_code = p_subinventory_code
497 and (locator_id = p_locator_id
498 OR locator_id is null and p_locator_id is null)
499 AND cost_group_id is not null
500 AND cost_group_id <> p_cost_group_id
501 AND posting_flag = 'Y'
502 AND transfer_lpn_id is not null
503 AND transfer_lpn_id = p_lpn_id);
504 EXCEPTION
505 WHEN no_data_found THEN
506 l_mmtt_lpn_receipts_exist := 'N';
507 END;
508
509 ELSE /* Lot Controlled LPN transaction */
510 BEGIN
511 SELECT 'Y' INTO l_mmtt_lpn_receipts_exist
512 FROM dual
513 WHERE
514 exists
515 (SELECT mmtt.organization_id --Bug 4496965
516 --BUG 2921882
517 --Changing the count(*) to existence for performance improvement
518 --select
519 --count(*)
520 --into
521 --l_mmtt_lpn_receipts_count
522 from mtl_material_transactions_temp mmtt,
523 mtl_transaction_lots_temp mtlt
524 where mmtt.organization_id = p_organization_id
525 AND mmtt.inventory_item_id = p_inventory_item_id
526 AND (mmtt.revision = p_revision
527 OR mmtt.revision is null and p_revision is null)
528 AND (mtlt.lot_number = p_lot_number
529 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id)
530 AND mmtt.subinventory_code = p_subinventory_code
531 AND (mmtt.locator_id = p_locator_id
532 OR mmtt.locator_id is null and p_locator_id is null)
533 AND mmtt.cost_group_id is not null
534 AND mmtt.cost_group_id <> p_cost_group_id
535 AND mmtt.posting_flag = 'Y'
536 AND mmtt.transfer_lpn_id is not null
537 AND mmtt.transfer_lpn_id = p_lpn_id);
538 EXCEPTION
539 WHEN no_data_found THEN
540 l_mmtt_lpn_receipts_exist := 'N';
541 END;
542 END IF;
543
544 IF (l_mmtt_lpn_receipts_exist = 'Y') THEN
545 x_count := 1; --l_mmtt_lpn_receipts_count;
546 x_comingling_occurs := 'Y';
547 return;
548 END IF;
549 end if;
550
551 x_comingling_occurs := 'N';
552
553 EXCEPTION
554
555 WHEN fnd_api.g_exc_error THEN
556 x_return_status := fnd_api.g_ret_sts_error;
557 fnd_msg_pub.count_and_get
558 ( p_count => x_msg_count,
559 p_data => x_msg_data
560 );
561 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
562 WHEN fnd_api.g_exc_unexpected_error THEN
563 x_return_status := fnd_api.g_ret_sts_unexp_error ;
564 fnd_msg_pub.count_and_get
565 ( p_count => x_msg_count,
566 p_data => x_msg_data
567 );
568 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
569
570 WHEN OTHERS THEN
571 x_return_status := fnd_api.g_ret_sts_unexp_error;
572 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
573 fnd_msg_pub.add_exc_msg ('inv_comingling_utils'
574 , 'comingle_check'
575 );
576 END IF;
577 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
578 fnd_msg_pub.count_and_get
579 ( p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582 end comingle_check;
583
584 procedure comingle_check
585 (x_return_status OUT NOCOPY VARCHAR2
586 , x_msg_count OUT NOCOPY NUMBER
587 , x_msg_data OUT NOCOPY VARCHAR2
588 , x_comingling_occurs OUT NOCOPY VARCHAR2
589 , p_transaction_temp_id IN NUMBER)
590 IS
591 cursor mmtt_cur IS
592 SELECT * FROM
593 mtl_material_transactions_temp
594 WHERE
595 transaction_temp_id = p_transaction_temp_id;
596 BEGIN
597
598 FOR mmtt_rec IN mmtt_cur LOOP
599 --below procedure is called only once
600 --because one temp_id corresponds to only one record
601 inv_comingling_utils.comingle_check
602 (x_return_status => x_return_status
603 , x_msg_count => x_msg_count
604 , x_msg_data => x_msg_data
605 , x_comingling_occurs => x_comingling_occurs
606 , p_mmtt_rec => mmtt_rec);
607 END LOOP;
608
609 IF mmtt_cur%isopen THEN
610 CLOSE mmtt_cur;
611 END IF;
612
613 EXCEPTION
614
615 WHEN OTHERS THEN
616 x_return_status := fnd_api.g_ret_sts_unexp_error;
617 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
618 fnd_msg_pub.add_exc_msg ('inv_comingling_utils'
619 , 'comingle_check'
620 );
621 END IF;
622 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
623 fnd_msg_pub.count_and_get
624 ( p_count => x_msg_count,
625 p_data => x_msg_data);
626 IF mmtt_cur%isopen THEN
627 CLOSE mmtt_cur;
628 END IF;
629 END;
630
631
632
633 procedure comingle_check
634 (x_return_status OUT NOCOPY VARCHAR2
635 ,x_msg_count OUT NOCOPY NUMBER
636 ,x_msg_data OUT NOCOPY VARCHAR2
637 ,x_comingling_occurs OUT NOCOPY VARCHAR2
638 ,p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE)
639 IS
640
641 CURSOR mtlt_cur IS
642 SELECT
643 mtlt.lot_number lot
644 FROM
645 mtl_transaction_lots_temp mtlt
646 WHERE mtlt.transaction_temp_id = p_mmtt_rec.transaction_temp_id;
647
648 l_serials_exist VARCHAR2(1) := 'N';
649 l_return_status VARCHAR2(1) := NULL;
650 l_msg_data VARCHAR2(255) := NULL;
651 l_msg_count NUMBER := NULL;
652 l_comingling_occurs VARCHAR2(1) := 'N';
653 l_count NUMBER := NULL;
654 l_lot_number VARCHAR2(255) := NULL;
655
656 l_wms_org_flag BOOLEAN := FALSE;
657 l_comingle_sub VARCHAR2(30) := NULL;
658 l_comingle_org NUMBER := NULL;
659 l_comingle_loc NUMBER := NULL;
660 l_comingle_cg NUMBER := NULL;
661
662 l_lpn_id NUMBER := NULL;
663 l_content_lpn_id NUMBER := NULL;
664 l_transfer_lpn_id NUMBER := NULL;
665 l_lpn_controlled_flag NUMBER := NULL;
666 l_check_done BOOLEAN := FALSE; --4576727
667
668 BEGIN
669
670 x_return_status := fnd_api.g_ret_sts_success;
671 x_comingling_occurs := 'N';
672
673
674 IF p_mmtt_rec.transaction_temp_id IS NULL THEN
675 RAISE fnd_api.g_exc_unexpected_error;
676 END IF;
677
678 /***
679 --If serials are involved, comingling cannot occur
680 ***/
681
682 BEGIN
683 SELECT 'Y' INTO l_serials_exist
684 FROM dual
685 WHERE exists
686 ( SELECT 1
687 FROM mtl_serial_numbers_temp
688 WHERE transaction_temp_id = p_mmtt_rec.transaction_temp_id);
689 EXCEPTION
690 WHEN no_data_found THEN
691 BEGIN
692 SELECT 'Y' INTO l_serials_exist
693 FROM dual
694 WHERE exists
695 (SELECT msnt.transaction_temp_id
696 FROM
697 mtl_serial_numbers_temp msnt,
698 mtl_transaction_lots_temp mtlt
699 WHERE mtlt.transaction_temp_id = p_mmtt_rec.transaction_temp_id
700 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id);
701 EXCEPTION
702 WHEN no_data_found THEN
703 l_serials_exist := 'N';
704 END;
705 END;
706
707 IF l_serials_exist = 'Y' THEN
708 print_debug('serials exist - no comingle');
709 x_comingling_occurs := 'N';
710 RETURN;
711 END IF;
712
713
714
715 l_comingle_sub := p_mmtt_rec.subinventory_code;
716 l_comingle_org := p_mmtt_rec.organization_id;
717 l_comingle_loc := p_mmtt_rec.locator_id;
718 l_comingle_cg := p_mmtt_rec.cost_group_id;
719 -- For transfer transactions, pass the attributes of the transfer(receipt)
720 -- side to comingle check.
721 if (p_mmtt_rec.transaction_action_id = inv_globals.G_ACTION_SUBXFR)
722 OR (p_mmtt_rec.transaction_action_id = inv_globals.G_ACTION_PLANXFR)
723 OR (p_mmtt_rec.transaction_action_id = inv_globals.G_ACTION_ORGXFR)
724 OR (p_mmtt_rec.transaction_action_id = inv_globals.G_ACTION_STGXFR) then
725
726 l_comingle_sub := p_mmtt_rec.transfer_subinventory;
727 l_comingle_loc := p_mmtt_rec.transfer_to_location;
728 l_comingle_cg := p_mmtt_rec.transfer_cost_group_id;
729 if (p_mmtt_rec.transaction_action_id = inv_globals.G_ACTION_ORGXFR) then
730 l_comingle_org := Nvl(p_mmtt_rec.transfer_organization,p_mmtt_rec.organization_id);
731 end if;
732
733 end if;
734
735 --Bug 2892207 moved this from above so that l_wms_org_flag is
736 --queried for the right organization
737 l_wms_org_flag := wms_install.check_install
738 ( x_return_status =>l_return_status,
739 x_msg_count =>l_msg_count,
740 x_msg_data =>l_msg_data,
741 p_organization_id => l_comingle_org);
742 if (l_return_status = FND_API.G_RET_STS_ERROR) then
743 RAISE FND_API.G_EXC_ERROR;
744 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 end if;
747
748
749 l_lpn_id := p_mmtt_rec.lpn_id;
750 l_content_lpn_id := p_mmtt_rec.content_lpn_id;
751 l_transfer_lpn_id := p_mmtt_rec.transfer_lpn_id;
752 --bug 2712046 fix
753 IF (p_mmtt_rec.content_lpn_id IS NOT NULL
754 OR p_mmtt_rec.transfer_lpn_id IS NOT NULL) THEN
755 BEGIN
756 SELECT
757 Nvl(lpn_controlled_flag,2)
758 INTO
759 l_lpn_controlled_flag
760 FROM
761 mtl_secondary_inventories
762 WHERE
763 secondary_inventory_name = l_comingle_sub
764 AND organization_id = l_comingle_org;
765 EXCEPTION
766 WHEN no_data_found THEN
767 l_lpn_controlled_flag := 2;
768 END;
769
770 IF l_lpn_controlled_flag = 2 THEN
771 print_debug('l_comingle_sub '||l_comingle_sub||
772 ' is lpn ctrld:setting clpn-xfr lpn as null');
773 l_content_lpn_id := NULL;
774 l_transfer_lpn_id := NULL;
775 END IF;
776 END IF;
777
778
779
780 -- Check if Co-mingling occurs for transacting data
781 -- skip this if this is an Issue/Shipment/CostGroupXfr/LotSplit
782 -- transaction OR if it is occuring in a Non-WMS enabled ORG as these
783 -- could not result on CoMingle. Loop through MTL_LOT_NUMBERS_TEMP
784 -- in case item is lot controlled. If content_lpn_id is present, then this
785 -- transaction could not result in comingling, skip check
786 -- Skip comingle-check if (from)_lpn_id is same as transfer_lpn_id. This is the
787 -- case when receiving an LPN through the RcvTrxManager.
788 -- If a stageXfr transaction and packing to an LPN, then allow comingle.
789 -- Skip check if cycle/physical count and Issue transaction (qty < 0)
790 -- Skip check if cost-group is null OR it is a transfer-transaction
791 -- and transfer-cost-group is null. This is the case where the CostGroupAPI
792 -- does not populate cost-group as CostGroup is derived by INV_WWACST.
793 if (l_content_lpn_id is NULL)
794 AND (l_wms_org_flag)
795 AND (p_mmtt_rec.transaction_action_id <> inv_globals.G_ACTION_ISSUE)
796 AND (p_mmtt_rec.transaction_action_id <> inv_globals.g_action_inv_lot_translate)
797 AND (p_mmtt_rec.transaction_action_id <> inv_globals.G_Action_CostGroupXfr)
798 AND (p_mmtt_rec.transaction_action_id <> inv_globals.G_Action_IntransitShipment)
799 AND (p_mmtt_rec.transaction_action_id <> inv_globals.G_Action_inv_lot_split)
800 AND (NOT ((p_mmtt_rec.transaction_action_id = inv_globals.G_Action_CycleCountAdj)
801 AND (p_mmtt_rec.primary_quantity < 0) ))
802 AND (NOT ((p_mmtt_rec.transaction_action_id = inv_globals.G_Action_PhysicalCountAdj)
803 AND (p_mmtt_rec.primary_quantity < 0) ))
804 AND (NOT ((p_mmtt_rec.transaction_action_id = inv_globals.G_ACTION_STGXFR)
805 AND (l_transfer_lpn_id is not NULL)))
806 AND ( nvl(l_lpn_id, 1) <> nvl(l_transfer_lpn_id, 2))
807 AND l_comingle_cg IS NOT NULL then
808
809 /**2912538 Commenting the below code because with the below
810 ** condition, for a transfer transaction, if the transfer cost group
811 ** is null(we expect such a case when the destination is a project
812 ** locator) and cost group is not null, instead of skipping comingle
813 ** check on the destination side,it continues with the comingle
814 **check
815 **((p_mmtt_rec.cost_group_id is not NULL)
816 ** OR
817 ** (p_mmtt_rec.transaction_action_id in
818 ** (inv_globals.G_ACTION_SUBXFR,
819 ** inv_globals.G_ACTION_ORGXFR,
820 ** inv_globals.G_ACTION_STGXFR)
821 ** AND p_mmtt_rec.transfer_cost_group_id is not NULL) ) THEN ***/
822
823 l_check_done := FALSE; --4576727
824 OPEN mtlt_cur;
825 LOOP
826
827 l_lot_number := NULL;
828 l_return_status := fnd_api.g_ret_sts_success;
829 l_msg_data := NULL;
830 l_msg_count := NULL;
831 l_comingling_occurs := 'N';
832
833 FETCH mtlt_cur INTO l_lot_number;
834
835 IF mtlt_cur%rowcount = 0 THEN
836 --there are no lots involved
837 l_lot_number := NULL;
838 END IF;
839
840 EXIT WHEN ( mtlt_cur%notfound and l_check_done ); -- 4576727
841
842 print_debug('calling comingle_check:org '||l_comingle_org||
843 'item '||p_mmtt_rec.inventory_item_id||
844 'rev '||p_mmtt_rec.revision||
845 'lot '||l_lot_number||
846 'sub '||l_comingle_sub||
847 'loc '||l_comingle_loc||
848 'lpn '||l_transfer_lpn_id||
849 'cg '||l_comingle_cg);
850
851 print_debug(' Calling Comingle Check');
852 l_check_done := TRUE; --4576727
853
854 INV_COMINGLING_UTILS.comingle_check
855 ( x_return_status => l_return_status
856 , x_msg_count => l_msg_count
857 , x_msg_data => l_msg_data
858 , x_comingling_occurs => l_comingling_occurs
859 , x_count => l_count
860 , p_organization_id => l_comingle_org
861 , p_inventory_item_id => p_mmtt_rec.inventory_item_id
862 , p_revision => p_mmtt_rec.revision
863 , p_lot_number => l_lot_number
864 , p_subinventory_code => l_comingle_sub
865 , p_locator_id => l_comingle_loc
866 , p_lpn_id => l_transfer_lpn_id
867 , p_cost_group_id => l_comingle_cg);
868
869
870 IF l_return_status <> fnd_api.g_ret_sts_success THEN
871
872 x_return_status := l_return_status;
873 x_msg_count := l_msg_count;
874 x_msg_data := l_msg_data;
875
876 IF mtlt_cur%isopen THEN
877 CLOSE mtlt_cur;
878 END IF;
879
880 RETURN;
881
882 ELSIF l_comingling_occurs = 'Y' THEN
883
884 x_comingling_occurs := 'Y';
885
886 IF mtlt_cur%isopen THEN
887 CLOSE mtlt_cur;
888 END IF;
889
890 fnd_message.set_name('INV', 'INV_COMINGLE_FAIL');
891 FND_MESSAGE.SET_TOKEN('CG', l_comingle_cg);
892 fnd_msg_pub.add;
893 RETURN;
894
895 END IF;--l_comingling_occurs = 'Y'
896
897
898 END LOOP;
899
900 --Begin bug 4471702
901 IF mtlt_cur%isopen THEN
902 CLOSE mtlt_cur;
903 END IF;
904 --End bug 4471702
905
906 END IF;
907
908 x_comingling_occurs := 'N';
909
910 EXCEPTION
911
912 WHEN fnd_api.g_exc_error THEN
913 x_return_status := fnd_api.g_ret_sts_error;
914 fnd_msg_pub.count_and_get
915 ( p_count => x_msg_count,
916 p_data => x_msg_data
917 );
918 IF mtlt_cur%isopen THEN
919 CLOSE mtlt_cur;
920 END IF;
921 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
922 WHEN fnd_api.g_exc_unexpected_error THEN
923 x_return_status := fnd_api.g_ret_sts_unexp_error ;
924 fnd_msg_pub.count_and_get
925 ( p_count => x_msg_count,
926 p_data => x_msg_data
927 );
928 IF mtlt_cur%isopen THEN
929 CLOSE mtlt_cur;
930 END IF;
931 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
932 WHEN OTHERS THEN
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
935 fnd_msg_pub.add_exc_msg ('inv_comingling_utils'
936 , 'comingle_check'
937 );
938 END IF;
939 --dbms_output.put_line(replace(x_msg_data,chr(0),' '));
940 fnd_msg_pub.count_and_get
941 ( p_count => x_msg_count,
942 p_data => x_msg_data
943 );
944 IF mtlt_cur%isopen THEN
945 CLOSE mtlt_cur;
946 END IF;
947 end comingle_check;
948
949
950
951
952 end inv_comingling_utils;
[Home] [Help]