1 PACKAGE BODY INV_Validate AS
2 /* $Header: INVSVATB.pls 120.14 2008/06/05 12:19:43 sdpaul ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_Validate';
7 g_orgid NUMBER;
8
9 -- Procedure Get_Attr_Tbl.
10 --
11 -- Used by generator to avoid overriding or duplicating existing
12 -- validation functions.
13 --
14 -- DO NOT REMOVE
15
16 PROCEDURE Get_Attr_Tbl
17 IS
18 I NUMBER:=0;
19 BEGIN
20
21 FND_API.g_attr_tbl.DELETE;
22
23 -- START GEN attributes
24
25 -- Generator will append new attributes before end generate comment.
26
27 I := I + 1;
28 FND_API.g_attr_tbl(I).name := 'Desc_Flex';
29 I := I + 1;
30 FND_API.g_attr_tbl(I).name := 'created_by';
31 I := I + 1;
32 FND_API.g_attr_tbl(I).name := 'creation_date';
33 I := I + 1;
34 FND_API.g_attr_tbl(I).name := 'date_required';
35 I := I + 1;
36 FND_API.g_attr_tbl(I).name := 'description';
37 I := I + 1;
38 FND_API.g_attr_tbl(I).name := 'from_subinventory';
39 I := I + 1;
40 FND_API.g_attr_tbl(I).name := 'header';
41 I := I + 1;
42 FND_API.g_attr_tbl(I).name := 'header_status';
43 I := I + 1;
44 FND_API.g_attr_tbl(I).name := 'last_updated_by';
45 I := I + 1;
46 FND_API.g_attr_tbl(I).name := 'last_update_date';
47 I := I + 1;
48 FND_API.g_attr_tbl(I).name := 'last_update_login';
49 I := I + 1;
50 FND_API.g_attr_tbl(I).name := 'organization';
51 I := I + 1;
52 FND_API.g_attr_tbl(I).name := 'program_application';
53 I := I + 1;
54 FND_API.g_attr_tbl(I).name := 'program';
55 I := I + 1;
56 FND_API.g_attr_tbl(I).name := 'program_update_date';
57 I := I + 1;
58 FND_API.g_attr_tbl(I).name := 'request';
59 I := I + 1;
60 FND_API.g_attr_tbl(I).name := 'request_number';
61 I := I + 1;
62 FND_API.g_attr_tbl(I).name := 'status_date';
63 I := I + 1;
64 FND_API.g_attr_tbl(I).name := 'to_account';
65 I := I + 1;
66 FND_API.g_attr_tbl(I).name := 'to_subinventory';
67 I := I + 1;
68 FND_API.g_attr_tbl(I).name := 'transaction_type';
69 I := I + 1;
70 FND_API.g_attr_tbl(I).name := 'from_locator';
71 I := I + 1;
72 FND_API.g_attr_tbl(I).name := 'inventory_item';
73 I := I + 1;
74 FND_API.g_attr_tbl(I).name := 'line';
75 I := I + 1;
76 FND_API.g_attr_tbl(I).name := 'line_number';
77 I := I + 1;
78 FND_API.g_attr_tbl(I).name := 'line_status';
79 I := I + 1;
80 FND_API.g_attr_tbl(I).name := 'lot_number';
81 I := I + 1;
82 FND_API.g_attr_tbl(I).name := 'project';
83 I := I + 1;
84 FND_API.g_attr_tbl(I).name := 'quantity';
85 I := I + 1;
86 FND_API.g_attr_tbl(I).name := 'quantity_delivered';
87 I := I + 1;
88 FND_API.g_attr_tbl(I).name := 'quantity_detailed';
89 I := I + 1;
90 FND_API.g_attr_tbl(I).name := 'reason';
91 I := I + 1;
92 FND_API.g_attr_tbl(I).name := 'reference';
93 I := I + 1;
94 FND_API.g_attr_tbl(I).name := 'reference';
95 I := I + 1;
96 FND_API.g_attr_tbl(I).name := 'reference_type';
97 I := I + 1;
98 FND_API.g_attr_tbl(I).name := 'revision';
99 I := I + 1;
100 FND_API.g_attr_tbl(I).name := 'serial_number_end';
101 I := I + 1;
102 FND_API.g_attr_tbl(I).name := 'serial_number_start';
103 I := I + 1;
104 FND_API.g_attr_tbl(I).name := 'task';
105 I := I + 1;
106 FND_API.g_attr_tbl(I).name := 'to_locator';
107 I := I + 1;
108 FND_API.g_attr_tbl(I).name := 'transaction_header';
109 I := I + 1;
110 FND_API.g_attr_tbl(I).name := 'uom';
111 I := I + 1;
112 FND_API.g_attr_tbl(I).name := 'uom';
113 --INVCONV
114 I := I + 1;
115 FND_API.g_attr_tbl(I).name := 'secondary_uom';
116 I := I + 1;
117 FND_API.g_attr_tbl(I).name := 'secondary_quantity';
118 I := I + 1;
119 FND_API.g_attr_tbl(I).name := 'secondary_quantity_delivered';
120 I := I + 1;
121 FND_API.g_attr_tbl(I).name := 'secondary_quantity_detailed';
122 I := I + 1;
123 FND_API.g_attr_tbl(I).name := 'grade_code';
124 --INVCONV
125
126 -- END GEN attributes
127
128 END Get_Attr_Tbl;
129
130 -- Prototypes for validate functions.
131
132 -- START GEN validate
133
134 -- Generator will append new prototypes before end generate comment.
135
136
137 -- ---------------------------------------------------------------------
138 -- ---------------------------------------------------------------------
139 FUNCTION Desc_Flex ( p_flex_name IN VARCHAR2 )
140 RETURN NUMBER
141 IS
142 BEGIN
143
144 -- Call FND validate API.
145
146
147 -- This call is temporarily commented out
148
149 RETURN T;
150
151 END Desc_Flex;
152
153
154 -- ---------------------------------------------------------------------
155 -- ---------------------------------------------------------------------
156 function check_creation_updation(p_created_updated_by in number,
157 p_is_creation in number)
158 RETURN NUMBER
159 is
160 l_dummy varchar2(10);
161
162 begin
163
164 IF p_created_updated_by IS NULL OR
165 p_created_updated_by = FND_API.G_MISS_NUM
166 then
167 return p_is_creation;
168 END IF;
169
170 SELECT 'VALID'
171 INTO l_dummy
172 FROM FND_USER
173 WHERE USER_ID = p_created_updated_by;
174
175 RETURN T;
176
177 EXCEPTION
178
179 WHEN NO_DATA_FOUND THEN
180
181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
182 THEN
183
184 FND_MESSAGE.SET_NAME('INV','INV_INT_USERCODE');
185 FND_MSG_PUB.Add;
186
187 END IF;
188
189 RETURN F;
190
191
192 WHEN OTHERS THEN
193
194 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
195 THEN
196 if (p_is_creation = T)
197 then
198 FND_MSG_PUB.Add_Exc_Msg
199 ( G_PKG_NAME
200 , 'Created_By'
201 );
202 else
203 FND_MSG_PUB.Add_Exc_Msg
204 ( G_PKG_NAME
205 , 'Last_Updated_By'
206 );
207 END IF;
208 end if;
209
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211
212 end check_creation_updation;
213
214
215 -- ---------------------------------------------------------------------
216 -- ---------------------------------------------------------------------
217 FUNCTION Created_By ( p_created_by IN NUMBER )
218 RETURN NUMBER
219 is
220
221 BEGIN
222
223 return check_creation_updation(p_created_by,T);
224
225 END Created_By;
226
227
228 -- ---------------------------------------------------------------------
229 -- ---------------------------------------------------------------------
230 function check_date (p_date in date, p_msg in varchar2)
231 RETURN NUMBER
232 is
233 begin
234 IF p_date IS NULL OR
235 p_date = FND_API.G_MISS_DATE
236 THEN
237 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
238 THEN
239 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
240 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV',p_msg),FALSE);
241 FND_MSG_PUB.Add;
242 END IF;
243 RETURN F;
244 END IF;
245 return T;
246 end check_date;
247
248
249 -- ---------------------------------------------------------------------
250 -- ---------------------------------------------------------------------
251 FUNCTION Creation_Date ( p_creation_date IN date)
252 RETURN NUMBER
253 IS
254 BEGIN
255
256 return check_date(p_creation_date, 'DATE');
257
258 END Creation_Date;
259
260
261 -- ---------------------------------------------------------------------
262 -- Bug 4373226 added parameter transaction_date for
263 -- checking the conversion rate on the basis of
264 -- transaction date but not on sysdate
265 -- ---------------------------------------------------------------------
266 FUNCTION conversion_rate(from_org IN NUMBER,
267 to_org IN NUMBER,transaction_date DATE DEFAULT SYSDATE)
268 RETURN NUMBER
269 IS
270 l_sob_id NUMBER;
271 l_xfr_sob_id NUMBER;
272 l_currency_code VARCHAR(15);
273 l_xfr_currency_code VARCHAR(15);
274 l_conv_type VARCHAR(240);
275 l_rate NUMBER;
276 excep varchar2(100);
277
278 BEGIN
279
280 SELECT set_of_books_id
281 INTO l_sob_id
282 FROM org_organization_definitions
283 WHERE organization_id = from_org;
284
285 SELECT set_of_books_id
286 INTO l_xfr_sob_id
287 FROM org_organization_definitions
288 WHERE organization_id = to_org;
289
290 SELECT currency_code
291 INTO l_currency_code
292 FROM gl_sets_of_books
293 WHERE set_of_books_id = l_sob_id;
294
295 SELECT currency_code
296 INTO l_xfr_currency_code
297 FROM gl_sets_of_books
298 WHERE set_of_books_id = l_xfr_sob_id;
299
300
301 IF (l_currency_code <> l_xfr_currency_code)
302 THEN
303 fnd_profile.get('CURRENCY_CONVERSION_TYPE',l_conv_type);
304
305 l_rate := gl_currency_api.get_closest_rate(l_sob_id,
306 l_xfr_currency_code,
307 transaction_date,
308 l_conv_type,
309 NULL);
310
311 -- RETURN T;
312 END IF;
313
314 RETURN T;
315 -- END IF;
316
317 EXCEPTION
318
319 WHEN gl_currency_api.NO_RATE THEN
320 RETURN F;
321
322 WHEN OTHERS THEN
323 RETURN f;
324
325 END conversion_rate;
326
327
328
329
330 -- ---------------------------------------------------------------------
331 -- ---------------------------------------------------------------------
332 FUNCTION Description ( p_description IN VARCHAR2 )
333 RETURN NUMBER
334 IS
335 BEGIN
336 return T;
337 END Description;
338
339
340 -- ---------------------------------------------------------------------
341 -- ---------------------------------------------------------------------
342 FUNCTION Employee(p_employee_id IN OUT NOCOPY NUMBER,
343 p_last_name IN OUT NOCOPY VARCHAR2,
344 p_full_name IN OUT NOCOPY VARCHAR2,
345 p_org IN ORG)
346 RETURN NUMBER
347 IS
348 BEGIN
349
350 IF p_employee_id IS NULL AND
351 p_last_name IS NULL AND
352 p_full_name IS NULL THEN
353 RETURN F;
354 END IF;
355
356 IF p_employee_id IS NOT NULL THEN
357 SELECT last_name,full_name
358 INTO p_last_name,p_full_name
359 FROM mtl_employees_current_view
360 WHERE employee_id = p_employee_id
361 AND organization_id = p_org.organization_id;
362 ELSE
363 BEGIN
364 SELECT employee_id,last_name,full_name
365 INTO p_employee_id,p_last_name,p_full_name
366 FROM mtl_employees_current_view
367 WHERE organization_id = p_org.organization_id
368 -- Bug 4951746, following where clause voided the index use
369 -- therefore cauased performance issue
370 -- changed to avoid the NVL and DECODE
371 -- AND (NVL(last_name,'@@@@') = DECODE(last_name,NULL,'@@@@',p_last_name)
372 -- OR NVL(full_name,'@@@@') = DECODE(full_name,NULL,'@@@@',p_full_name));
373 AND
374
375 -- Bug 6061411
376 ( -- Added this brace to make this work as expected
377 (p_last_name is null OR
378 (p_last_name is not null and last_name = p_last_name))
379 OR (p_full_name is null OR
380 (p_full_name is not null and full_name = p_full_name)
381 ) -- Added this brace to make it work as expected
382 );
383
384 -- End of Bug 6061411
385
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 RAISE NO_DATA_FOUND;
389 WHEN TOO_MANY_ROWS THEN
390 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
391 THEN
392 FND_MESSAGE.SET_NAME('INV','HR_51340_EMP_EMP_NUM_REQ');
393 FND_MSG_PUB.Add;
394 END IF;
395 RETURN F;
396 END;
397 END IF;
398
399 RETURN T;
400
401 EXCEPTION
402
403 WHEN NO_DATA_FOUND THEN
404
405 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
406 THEN
407 FND_MESSAGE.SET_NAME('INV','HR_7676_BOOKING_FLAG_CHANGE');
408 FND_MSG_PUB.Add;
409 END IF;
410
411 RETURN F;
412
413 WHEN OTHERS THEN
414
415 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
416 THEN
417 FND_MSG_PUB.Add_Exc_Msg
418 ( G_PKG_NAME
419 , 'Employee'
420 );
421 END IF;
422
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424
425
426 END employee;
427
428
429 -- ---------------------------------------------------------------------
430 -- ---------------------------------------------------------------------
431 FUNCTION From_Subinventory ( p_sub IN OUT nocopy sub,
432 p_org IN ORG,
433 p_item IN ITEM,
434 p_acct_txn IN NUMBER)
435 RETURN NUMBER
436 IS
437 l_dummy VARCHAR2(10);
438 v_expense_to_asset_profile VARCHAR2(1);
439 BEGIN
440
441 IF p_sub.secondary_inventory_name IS NULL OR
442 p_sub.secondary_inventory_name = FND_API.G_MISS_CHAR
443 THEN
444 RETURN F;
445 END IF;
446
447 FND_PROFILE.GET('INV:EXPENSE_TO_ASSET_TRANSFER',v_expense_to_asset_profile);
448 if( NVL(v_expense_to_asset_profile,'2') = '1')
449 then
450 if(p_acct_txn = 1)
451 then
452 if p_item.restrict_subinventories_code = 1
453 then
454 SELECT 'VALID'
455 INTO l_dummy
456 FROM MTL_ITEM_SUB_TRK_VAL_V
457 WHERE ORGANIZATION_ID = p_org.organization_id
458 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
459 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
460 else
461 SELECT 'VALID'
462 INTO l_dummy
463 FROM MTL_SUBINVENTORIES_TRK_VAL_V
464 WHERE ORGANIZATION_ID = p_org.organization_id
465 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
466 end if;
467 else
468 if p_item.restrict_subinventories_code = 1
469 then
470 SELECT 'VALID'
471 INTO l_dummy
472 FROM MTL_ITEM_SUB_TRK_VAL_V
473 WHERE ORGANIZATION_ID = p_org.organization_id
474 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
475 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
476 else
477 SELECT 'VALID'
478 INTO l_dummy
479 FROM MTL_SUBINVENTORIES_TRK_VAL_V
480 WHERE ORGANIZATION_ID = p_org.organization_id
481 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
482 end if;
483 end if;
484 else
485 if(p_acct_txn = 1)
486 then
487 if p_item.restrict_subinventories_code = 1
488 then
489 SELECT 'VALID'
490 INTO l_dummy
491 FROM MTL_ITEM_SUB_VAL_V
492 WHERE ORGANIZATION_ID = p_org.organization_id
493 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
494 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
495 else
496 SELECT 'VALID'
497 INTO l_dummy
498 FROM MTL_SUBINVENTORIES_TRK_VAL_V
499 WHERE ORGANIZATION_ID = p_org.organization_id
500 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
501 end if;
502 else
503 if p_item.restrict_subinventories_code = 1
504 then
505 SELECT 'VALID'
506 INTO l_dummy
507 FROM MTL_ITEM_SUB_TRK_VAL_V
508 WHERE ORGANIZATION_ID = p_org.organization_id
509 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
510 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
511 else
512 SELECT 'VALID'
513 INTO l_dummy
514 FROM MTL_SUBINVENTORIES_TRK_VAL_V
515 WHERE ORGANIZATION_ID = p_org.organization_id
516 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
517 end if;
518 end if;
519 end if;
520 SELECT *
521 INTO p_sub
522 FROM MTL_SECONDARY_INVENTORIES
523 WHERE ORGANIZATION_ID = p_org.organization_id
524 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
525
526 RETURN T;
527 EXCEPTION
528
529 WHEN NO_DATA_FOUND THEN
530
531 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
532 THEN
533 FND_MESSAGE.SET_NAME('INV','INV_INT_SUBCODE');
534 FND_MSG_PUB.Add;
535 END IF;
536
537 RETURN F;
538
539 WHEN OTHERS THEN
540
541 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
542 THEN
543 FND_MSG_PUB.Add_Exc_Msg
544 ( G_PKG_NAME
545 , 'From_Subinventory'
546 );
547 END IF;
548
549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550
551 END From_Subinventory;
552
553 -- ---------------------------------------------------------------------
554 -- ---------------------------------------------------------------------
555 /*
556 * Bug# 6633612
557 * Overloaded From_Subinventory function for Material Status Enhancement Project
558 */
559 FUNCTION From_Subinventory ( p_sub IN OUT nocopy sub,
560 p_org IN ORG,
561 p_item IN ITEM,
562 p_acct_txn IN NUMBER,
563 p_trx_type_id IN NUMBER, -- For Bug# 6633612
564 p_object_type IN VARCHAR2 DEFAULT 'Z' -- For Bug# 6633612
565 )
566 RETURN NUMBER
567 IS
568 l_result NUMBER;
569 l_status_result VARCHAR2(1);
570 BEGIN
571
572 /* First call the original From_Subinventory function,
573 * If it returns INV_VALIDATE.T then goahead to call
574 * inv_material_status_grp.is_status_applicable() function.
575 */
576 l_result := INV_VALIDATE.From_Subinventory(
577 p_sub => p_sub,
578 p_org => p_org,
579 p_item => p_item,
580 p_acct_txn => p_acct_txn);
581
582 IF (l_result = INV_VALIDATE.T)
583 THEN
584
585 -- Make the call for inv_material_status_grp.is_status_applicable()
586 -- with appropriate parameters
587 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
588 p_wms_installed => NULL,
589 p_trx_status_enabled => NULL,
590 p_trx_type_id => p_trx_type_id,
591 p_lot_status_enabled => NULL,
592 p_serial_status_enabled => NULL,
593 p_organization_id => p_org.organization_id,
594 p_inventory_item_id => p_item.inventory_item_id,
595 p_sub_code => p_sub.secondary_inventory_name,
596 p_locator_id => NULL,
597 p_lot_number => NULL,
598 p_serial_number => NULL,
599 p_object_type => p_object_type);
600
601 -- If l_status_result = 'N', it means that the status validation has failed.
602 -- Assign l_result = INV_VALIDATE.F and return l_result, else return l_result
603 -- directly.
604 IF (l_status_result = 'N')
605 THEN
606 l_result := INV_VALIDATE.F;
607 RETURN l_result;
608 ELSE
609 RETURN l_result;
610 END IF;
611
612 ELSE
613 -- Basic From_subinventory validation has failed return l_result
614 RETURN l_result;
615 END IF;
616
617 END From_Subinventory;
618
619 -- ---------------------------------------------------------------------
620 -- ---------------------------------------------------------------------
621 FUNCTION Last_Updated_By ( p_last_updated_by IN NUMBER )
622 RETURN NUMBER
623 IS
624 l_dummy VARCHAR2(10);
625 BEGIN
626
627 return check_creation_updation(p_last_updated_by, F);
628
629 END Last_Updated_By;
630
631
632 -- ---------------------------------------------------------------------
633 -- ---------------------------------------------------------------------
634 FUNCTION Last_Update_Date ( p_last_update_date IN DATE )
635 RETURN NUMBER
636 IS
637 BEGIN
638
639 return check_date(p_last_update_date, 'DATE');
640
641 END Last_Update_Date;
642
643
644 -- ---------------------------------------------------------------------
645 -- ---------------------------------------------------------------------
646 FUNCTION Last_Update_Login ( p_last_update_login IN NUMBER )
647 RETURN NUMBER
648 IS
649 l_dummy VARCHAR2(10);
650 BEGIN
651
652 IF p_last_update_login IS NULL OR
653 p_last_update_login = FND_API.G_MISS_NUM
654 THEN
655 RETURN T;
656 END IF;
657
658 RETURN T;
659
660 EXCEPTION
661
662 WHEN NO_DATA_FOUND THEN
663
664 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
665 THEN
666
667 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
668 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','last_update_login');
669 FND_MSG_PUB.Add;
670
671 END IF;
672
673 RETURN F;
674
675 WHEN OTHERS THEN
676
677 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
678 THEN
679 FND_MSG_PUB.Add_Exc_Msg
680 ( G_PKG_NAME
681 , 'Last_Update_Login'
682 );
683 END IF;
684
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686
687 END Last_Update_Login;
688
689
690 -- ---------------------------------------------------------------------
691 -- ---------------------------------------------------------------------
692 FUNCTION Organization (p_org IN OUT nocopy org)
693 RETURN NUMBER
694 IS
695
696 BEGIN
697
698 IF (p_org.organization_id IS NULL AND p_org.organization_code IS NULL) OR
699 p_org.organization_id = FND_API.G_MISS_NUM
700 THEN
701 RETURN F;
702 END IF;
703
704 IF (p_org.organization_id IS NOT NULL) THEN
705 SELECT *
706 INTO p_org
707 FROM MTL_PARAMETERS MP
708 WHERE ORGANIZATION_ID = p_org.organization_id;
709 RETURN T;
710 ELSE
711 SELECT *
712 INTO p_org
713 FROM MTL_PARAMETERS MP
714 WHERE MP.ORGANIZATION_CODE = p_org.organization_code
715 AND MP.ORGANIZATION_ID = p_org.ORGANIZATION_ID;
716 RETURN T;
717 END IF;
718
719 EXCEPTION
720
721 WHEN NO_DATA_FOUND THEN
722
723 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
724 THEN
725
726 FND_MESSAGE.SET_NAME('INV','INV_INT_ORGCODE');
727 FND_MSG_PUB.Add;
728
729 END IF;
730
731 RETURN F;
732
733 WHEN OTHERS THEN
734
735 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
736 THEN
737 FND_MSG_PUB.Add_Exc_Msg
738 ( G_PKG_NAME
739 , 'Organization'
740 );
741 END IF;
742
743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
744
745 END Organization;
746
747
748 -- ---------------------------------------------------------------------
749 -- ---------------------------------------------------------------------
750 FUNCTION Program_Application ( p_program_application_id IN NUMBER )
751 RETURN NUMBER
752 IS
753 l_dummy VARCHAR2(10);
754 BEGIN
755 RETURN T;
756 END Program_Application;
757
758 -- ---------------------------------------------------------------------
759 -- ---------------------------------------------------------------------
760 FUNCTION Program ( p_program_id IN NUMBER )
761 RETURN NUMBER
762 IS
763 l_dummy VARCHAR2(10);
764 BEGIN
765 RETURN T;
766 END Program;
767
768
769 -- ---------------------------------------------------------------------
770 -- ---------------------------------------------------------------------
771 FUNCTION Program_Update_Date ( p_program_update_date IN DATE )
772 RETURN NUMBER
773 IS
774 l_dummy VARCHAR2(10);
775 BEGIN
776 RETURN T;
777 END Program_Update_Date;
778
779
780 -- ---------------------------------------------------------------------
781 -- ---------------------------------------------------------------------
782 FUNCTION To_Account ( p_to_account_id IN NUMBER )
783 RETURN NUMBER
784 IS
785 l_dummy VARCHAR2(10);
786 BEGIN
787
788 IF p_to_account_id IS NULL OR
789 p_to_account_id = FND_API.G_MISS_NUM
790 THEN
791 RETURN T;
792 END IF;
793
794 SELECT 'VALID'
795 INTO l_dummy
796 FROM GL_CODE_COMBINATIONS
797 WHERE CODE_COMBINATION_ID = p_to_account_id;
798
799 RETURN T;
800
801 EXCEPTION
802
803 WHEN NO_DATA_FOUND THEN
804
805 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
806 THEN
807
808 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
809 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','ACCOUNT'),FALSE);
810 FND_MSG_PUB.Add;
811
812 END IF;
813
814 RETURN F;
815
816 WHEN OTHERS THEN
817
818 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
819 THEN
820 FND_MSG_PUB.Add_Exc_Msg
821 ( G_PKG_NAME
822 , 'To_Account'
823 );
824 END IF;
825
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827
828 END To_Account;
829
830
831 -- ---------------------------------------------------------------------
832 -- ---------------------------------------------------------------------
833 FUNCTION To_Subinventory ( p_sub IN OUT nocopy sub,
834 p_org IN ORG,
835 p_item IN ITEM,
836 p_from_sub IN SUB,
837 p_acct_txn IN NUMBER)
838 RETURN NUMBER
839 IS
840 l_dummy VARCHAR2(10);
841 v_expense_to_asset_profile VARCHAR2(1);
842 BEGIN
843
844 IF p_sub.secondary_inventory_name IS NULL OR
845 p_sub.secondary_inventory_name = FND_API.G_MISS_CHAR
846 THEN
847 RETURN T;
848 END IF;
849 FND_PROFILE.GET('INV:EXPENSE_TO_ASSET_TRANSFER',v_expense_to_asset_profile);
850 if(nvl(v_expense_to_asset_profile,'2') = '1')
851 then
852 if(p_acct_txn <> 1)
853 then
854 if p_item.restrict_subinventories_code = 1
855 then
856 SELECT 'VALID'
857 INTO l_dummy
858 FROM MTL_ITEM_SUB_VAL_V
859 WHERE ORGANIZATION_ID = p_org.organization_id
860 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
861 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
862 else
863 SELECT 'VALID'
864 INTO l_dummy
865 FROM MTL_SUBINVENTORIES_VAL_V
866 WHERE ORGANIZATION_ID = p_org.organization_id
867 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
868 end if;
869 end if;
870 else
871 if(p_acct_txn <> 1) then
872 if p_item.restrict_subinventories_code = 1 then
873 if p_item.inventory_asset_flag = 'Y' then
874 if p_from_sub.asset_inventory is null then
875 return T;
876 elsif p_from_sub.asset_inventory = 1 then
877 SELECT 'VALID'
878 INTO l_dummy
879 FROM MTL_ITEM_SUB_VAL_V
880 WHERE ORGANIZATION_ID = p_org.organization_id
881 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
882 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
883 else
884 BEGIN
885 SELECT 'VALID'
886 INTO l_dummy
887 FROM MTL_ITEM_SUB_EXP_VAL_V
888 WHERE ORGANIZATION_ID = p_org.organization_id
889 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
890 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
891 EXCEPTION
892 WHEN NO_DATA_FOUND THEN
893 FND_MESSAGE.SET_NAME('INV','INV_EXP_ACCT_REQ');
894 FND_MSG_PUB.Add;
895 return F;
896 END;
897 end if;
898 else
899 SELECT 'VALID'
900 INTO l_dummy
901 FROM MTL_ITEM_SUB_VAL_V
902 WHERE ORGANIZATION_ID = p_org.organization_id
903 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
904 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
905 end if;
906 else
907 if p_item.inventory_asset_flag = 'Y' then
908 if p_from_sub.asset_inventory is null then
909 return T;
910 elsif p_from_sub.asset_inventory = 1 then
911 SELECT 'VALID'
912 INTO l_dummy
913 FROM MTL_SUBINVENTORIES_VAL_V
914 WHERE ORGANIZATION_ID = p_org.organization_id
915 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
916 else
917 BEGIN
918 SELECT 'VALID'
919 INTO l_dummy
920 FROM MTL_SUB_EXP_VAL_V
921 WHERE ORGANIZATION_ID = p_org.organization_id
922 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 FND_MESSAGE.SET_NAME('INV','INV_EXP_ACCT_REQ');
926 FND_MSG_PUB.Add;
927 return F;
928 END;
929 end if;
930 else
931 SELECT 'VALID'
932 INTO l_dummy
933 FROM MTL_SUBINVENTORIES_VAL_V
934 WHERE ORGANIZATION_ID = p_org.organization_id
935 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
936 end if;
937 end if;
938 end if;
939 end if;
940
941 SELECT *
942 INTO p_sub
943 FROM MTL_SECONDARY_INVENTORIES
944 WHERE ORGANIZATION_ID = p_org.organization_id
945 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;
946 RETURN T;
947
948 EXCEPTION
949 WHEN NO_DATA_FOUND THEN
950 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
951 FND_MESSAGE.SET_NAME('INV','INV_INT_XSUBCODE');
952 FND_MSG_PUB.Add;
953 END IF;
954 RETURN F;
955
956 WHEN OTHERS THEN
957 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
958 FND_MSG_PUB.Add_Exc_Msg
959 ( G_PKG_NAME
960 , 'To_Subinventory'
961 );
962 END IF;
963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 END To_Subinventory;
965
966 -- ---------------------------------------------------------------------
967 -- ---------------------------------------------------------------------
968 /*
969 * Bug# 6633612
970 * Overloaded To_Subinventory function for Material Status Enhancement Project
971 */
972 FUNCTION To_Subinventory ( p_sub IN OUT nocopy sub,
973 p_org IN ORG,
974 p_item IN ITEM,
975 p_from_sub IN SUB,
976 p_acct_txn IN NUMBER,
977 p_trx_type_id IN NUMBER, -- For Bug# 6633612
978 p_object_type IN VARCHAR2 DEFAULT 'Z' -- For Bug# 6633612
979 )
980 RETURN NUMBER
981 IS
982 l_result NUMBER;
983 l_status_result VARCHAR2(1);
984 BEGIN
985
986 /* First call the original To_Subinventory function,
987 * If it returns INV_VALIDATE.T then goahead to call
988 * inv_material_status_grp.is_status_applicable() function.
989 */
990 l_result := INV_VALIDATE.To_Subinventory(
991 p_sub => p_sub,
992 p_org => p_org,
993 p_item => p_item,
994 p_from_sub => p_from_sub,
995 p_acct_txn => p_acct_txn);
996
997 IF (l_result = INV_VALIDATE.T)
998 THEN
999
1000 -- Make the call for inv_material_status_grp.is_status_applicable()
1001 -- with appropriate parameters
1002 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
1003 p_wms_installed => NULL,
1004 p_trx_status_enabled => NULL,
1005 p_trx_type_id => p_trx_type_id,
1006 p_lot_status_enabled => NULL,
1007 p_serial_status_enabled => NULL,
1008 p_organization_id => p_org.organization_id,
1009 p_inventory_item_id => p_item.inventory_item_id,
1010 p_sub_code => p_sub.secondary_inventory_name,
1011 p_locator_id => NULL,
1012 p_lot_number => NULL,
1013 p_serial_number => NULL,
1014 p_object_type => p_object_type);
1015
1016 -- If l_status_result = 'N', it means that the status validation has failed.
1017 -- Assign l_result = INV_VALIDATE.F and return l_result, else return l_result
1018 -- directly.
1019 IF (l_status_result = 'N')
1020 THEN
1021 l_result := INV_VALIDATE.F;
1022 RETURN l_result;
1023 ELSE
1024 RETURN l_result;
1025 END IF;
1026
1027 ELSE
1028 -- Basic To_Subinventory validation has failed return l_result
1029 RETURN l_result;
1030 END IF;
1031
1032 END To_Subinventory;
1033
1034 -- ---------------------------------------------------------------------
1035 -- ---------------------------------------------------------------------
1036 FUNCTION Transaction_Type ( p_transaction_type_id IN NUMBER,
1037 x_transaction_action_id OUT NOCOPY NUMBER,
1038 x_transaction_source_type_id OUT NOCOPY NUMBER)
1039 RETURN NUMBER
1040 IS
1041
1042 BEGIN
1043
1044 IF p_transaction_type_id IS NULL OR
1045 p_transaction_type_id = FND_API.G_MISS_NUM
1046 THEN
1047 FND_MESSAGE.SET_NAME('INV','INV_INT_TRXTYPCODE');
1048 FND_MSG_PUB.Add;
1049 RETURN F;
1050 END IF;
1051
1052 SELECT transaction_action_id,transaction_source_type_id
1053 INTO x_transaction_action_id,x_transaction_source_type_id
1054 FROM mtl_transaction_types
1055 WHERE transaction_type_id = p_transaction_type_id;
1056
1057 RETURN T;
1058
1059
1060 EXCEPTION
1061
1062 WHEN NO_DATA_FOUND THEN
1063
1064 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1065 THEN
1066
1067 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
1068
1069 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','TRANSACTION'),FALSE);
1070 FND_MSG_PUB.Add;
1071
1072 END IF;
1073
1074 RETURN F;
1075
1076 WHEN OTHERS THEN
1077
1078 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1079 THEN
1080 FND_MSG_PUB.Add_Exc_Msg
1081 ( G_PKG_NAME
1082 , 'Transaction_Type'
1083 );
1084 END IF;
1085
1086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1087
1088 /*
1089 IF p_transaction_type_id IN (63,64) THEN
1090 RETURN T;
1091 ELSE
1092 RETURN F;
1093 END IF;
1094 */
1095
1096 END Transaction_Type;
1097
1098
1099 -- ---------------------------------------------------------------------
1100 -- ---------------------------------------------------------------------
1101 FUNCTION Transaction_Type (x_transaction IN OUT nocopy transaction)RETURN NUMBER
1102 IS
1103
1104 BEGIN
1105
1106 IF x_transaction.transaction_type_id IS NULL OR
1107 x_transaction.transaction_type_id = FND_API.G_MISS_NUM
1108 THEN
1109 FND_MESSAGE.SET_NAME('INV','INV_INT_TRXTYPCODE');
1110 FND_MSG_PUB.Add;
1111 RETURN F;
1112 END IF;
1113
1114 SELECT *
1115 INTO x_transaction
1116 FROM mtl_transaction_types
1117 WHERE transaction_type_id = x_transaction.transaction_type_id;
1118
1119 RETURN T;
1120
1121
1122 EXCEPTION
1123
1124 WHEN NO_DATA_FOUND THEN
1125
1126 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1127 THEN
1128
1129 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
1130
1131 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','TRANSACTION'),FALSE);
1132 FND_MSG_PUB.Add;
1133
1134 END IF;
1135
1136 RETURN F;
1137
1138 WHEN OTHERS THEN
1139
1140 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1141 THEN
1142 FND_MSG_PUB.Add_Exc_Msg
1143 ( G_PKG_NAME
1144 , 'Transaction_Type'
1145 );
1146 END IF;
1147
1148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1149
1150 END Transaction_Type;
1151
1152 -- ---------------------------------------------------------------------
1153 -- ---------------------------------------------------------------------
1154 FUNCTION Check_Locator (p_locator IN OUT nocopy locator,
1155 p_org IN ORG,
1156 p_item IN ITEM,
1157 p_sub IN SUB,
1158 p_project_id IN NUMBER,
1159 p_task_id IN NUMBER,
1160 p_txn_action_id IN number,
1161 p_is_from_locator in number,
1162 p_dynamic_ok IN BOOLEAN)
1163
1164 RETURN NUMBER
1165 IS
1166 l_dummy VARCHAR2(10);
1167 l_return_status VARCHAR2(10);
1168 l_msg_count NUMBER;
1169 l_msg_data VARCHAR2(240);
1170 v_locator_control NUMBER;
1171 l_number NUMBER;
1172
1173 BEGIN
1174
1175 IF p_locator.inventory_location_id IS NULL OR
1176 p_locator.inventory_location_id = FND_API.G_MISS_NUM
1177 THEN
1178 RETURN T;
1179 END IF;
1180 v_locator_control := INV_Globals.Locator_control(
1181 l_return_status,
1182 l_msg_count,
1183 l_msg_data,
1184 p_org.stock_locator_control_code,
1185 nvl(p_sub.locator_type,1),
1186 p_item.location_control_code,
1187 p_item.restrict_locators_code,
1188 p_org.negative_inv_receipt_code,
1189 p_txn_action_id);
1190
1191 if(NVL(v_locator_control,1) = 2) then
1192 if (p_item.restrict_locators_code = 1) then -- if restricted
1193 select *
1194 INTO p_locator
1195 FROM MTL_ITEM_LOCATIONS
1196 WHERE ORGANIZATION_ID = p_org.organization_id
1197 AND INVENTORY_LOCATION_ID = p_locator.inventory_location_id
1198 AND SUBINVENTORY_CODE = p_sub.secondary_inventory_name
1199 AND (DISABLE_DATE > SYSDATE OR DISABLE_DATE IS NULL)
1200 AND INVENTORY_LOCATION_ID IN
1201 (SELECT SECONDARY_LOCATOR
1202 FROM MTL_SECONDARY_LOCATORS
1203 WHERE INVENTORY_ITEM_ID = p_item.inventory_item_id
1204 AND ORGANIZATION_ID = p_org.organization_id
1205 AND NVL(PROJECT_ID,-1) = NVL(p_project_id,-1)
1206 AND NVL(TASK_ID,-1) = NVL(p_task_id,-1)
1207 AND SUBINVENTORY_CODE = p_sub.secondary_inventory_name);
1208 else
1209 SELECT *
1210 INTO p_locator
1211 FROM MTL_ITEM_LOCATIONS
1212 WHERE ORGANIZATION_ID = p_org.organization_id
1213 AND INVENTORY_LOCATION_ID = p_locator.inventory_location_id
1214 AND (NVL(SUBINVENTORY_CODE,p_sub.secondary_inventory_name) =
1215 p_sub.secondary_inventory_name)
1216 AND (DISABLE_DATE > SYSDATE OR DISABLE_DATE IS NULL)
1217 AND NVL(PROJECT_ID,-1) = NVL(p_project_id,-1)
1218 AND NVL(TASK_ID,-1) = NVL(p_task_id,-1);
1219 end if;
1220 elsif NVL(v_locator_control,1) = 3 then -- if dynamic
1221 SELECT *
1222 INTO p_locator
1223 FROM MTL_ITEM_LOCATIONS
1224 WHERE ORGANIZATION_ID = p_org.organization_id
1225 AND INVENTORY_LOCATION_ID = p_locator.inventory_location_id
1226 AND (NVL(SUBINVENTORY_CODE,p_sub.secondary_inventory_name) =
1227 p_sub.secondary_inventory_name)
1228 AND (DISABLE_DATE > SYSDATE OR DISABLE_DATE IS NULL)
1229 AND NVL(PROJECT_ID,-1) = NVL(p_project_id,-1)
1230 AND NVL(TASK_ID,-1) = NVL(p_task_id,-1);
1231 end if;
1232
1233 RETURN T;
1234
1235 EXCEPTION
1236
1237 WHEN NO_DATA_FOUND THEN
1238 if v_locator_control = 3 and p_dynamic_ok THEN
1239 l_number := validateLocator(p_locator,p_org,p_sub,EXISTS_OR_CREATE);
1240 RETURN l_number;
1241 end if;
1242 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1243 THEN
1244
1245 FND_MESSAGE.SET_NAME('INV','INV_INT_LOCCODE');
1246 FND_MSG_PUB.Add;
1247
1248 END IF;
1249
1250 RETURN F;
1251
1252 WHEN OTHERS THEN
1253
1254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1255 then
1256 if (p_is_from_locator = 1)
1257 then
1258 FND_MSG_PUB.Add_Exc_Msg
1259 ( G_PKG_NAME
1260 , 'From_Locator'
1261 );
1262 else
1263 FND_MSG_PUB.Add_Exc_Msg
1264 ( G_PKG_NAME
1265 , 'To_Locator'
1266 );
1267 end if;
1268 END IF;
1269
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271
1272 END check_locator;
1273
1274
1275 -- ---------------------------------------------------------------------
1276 -- ---------------------------------------------------------------------
1277 FUNCTION From_Locator ( p_locator IN OUT nocopy locator,
1278 p_org IN ORG,
1279 p_item IN ITEM,
1280 p_from_sub IN SUB,
1281 p_project_id IN NUMBER,
1282 p_task_id IN NUMBER,
1283 p_txn_action_id IN NUMBER)
1284
1285 RETURN NUMBER
1286 IS
1287
1288 BEGIN
1289
1290 Return check_locator(p_locator,
1291 p_org, p_item,p_from_sub,
1292 p_project_id, p_task_id,
1293 p_txn_action_id, t, false);
1294
1295 END From_Locator;
1296
1297
1298 -- generate the concatenated segment given the application short name like
1299 -- 'INV' OR 'FND' AND the key flex field code LIKE 'MTLL' and the structure
1300 -- NUMBER LIKE 101
1301 -- ---------------------------------------------------------------------
1302 -- ---------------------------------------------------------------------
1303 FUNCTION concat_segments(p_appl_short_name IN VARCHAR2,
1304 p_key_flex_code IN VARCHAR2,
1305 p_structure_number IN NUMBER)
1306 RETURN VARCHAR2
1307 IS
1308
1309 l_key_flex_field fnd_flex_key_api.flexfield_type;
1310 l_structure_type fnd_flex_key_api.structure_type;
1311 l_segment_type fnd_flex_key_api.segment_type;
1312 l_segment_list fnd_flex_key_api.segment_list;
1313 l_segment_array fnd_flex_ext.SegmentArray;
1314 l_num_segments NUMBER;
1315 l_flag BOOLEAN;
1316 l_concat VARCHAR2(2000);
1317 j NUMBER;
1318 i NUMBER;
1319 BEGIN
1320
1321 fnd_flex_key_api.set_session_mode('seed_data');
1322
1323 l_key_flex_field :=
1324 fnd_flex_key_api.find_flexfield(p_appl_short_name,
1325 p_key_flex_code);
1326
1327 l_structure_type :=
1328 fnd_flex_key_api.find_structure(l_key_flex_field,
1329 p_structure_number);
1330
1331 fnd_flex_key_api.get_segments(l_key_flex_field, l_structure_type,
1332 TRUE, l_num_segments, l_segment_list);
1333
1334
1335 --
1336 -- The segments in the seg_list array are sorted in display order.
1337 -- i.e. sorted by segment number.
1338 --
1339 for i in 1..l_num_segments loop
1340 l_segment_type :=
1341 fnd_flex_key_api.find_segment(l_key_flex_field,
1342 l_structure_type,
1343 l_segment_list(i));
1344 j := to_number(substr(l_segment_type.column_name,8));
1345 l_segment_array(i) := g_kf_segment_values(j);
1346 end loop;
1347
1348 --
1349 -- Now we have the all segment values in correct order in segarray.
1350 --
1351 l_concat := fnd_flex_ext.concatenate_segments(l_num_segments,
1352 l_segment_array,
1353 l_structure_type.segment_separator);
1354
1355 RETURN l_concat;
1356
1357
1358 END concat_segments;
1359
1360 -- ---------------------------------------------------------------------
1361 -- ---------------------------------------------------------------------
1362 FUNCTION Inventory_Item (p_item IN OUT nocopy item, p_org IN org)
1363 RETURN NUMBER
1364 IS
1365 l_appl_short_name VARCHAR2(3) := 'INV';
1366 l_key_flex_code VARCHAR2(4) := 'MSTK';
1367 l_structure_number NUMBER := 101;
1368 l_conc_segments VARCHAR2(2000);
1369 l_keystat_val BOOLEAN;
1370 l_id NUMBER;
1371 l_validation_mode VARCHAR2(25) := EXISTS_ONLY;
1372
1373 BEGIN
1374
1375 -- IF (p_item.inventory_item_id IS NULL OR
1376 -- p_item.inventory_item_id = FND_API.g_miss_num) AND
1377 -- p_validation_mode IS NULL
1378 -- THEN
1379 -- RETURN F;
1380 -- END IF;
1381
1382 IF p_item.inventory_item_id IS NULL THEN
1383 g_kf_segment_values(1) := p_item.segment1;
1384 g_kf_segment_values(2) := p_item.segment2;
1385 g_kf_segment_values(3) := p_item.segment3;
1386 g_kf_segment_values(4) := p_item.segment4;
1387 g_kf_segment_values(5) := p_item.segment5;
1388 g_kf_segment_values(6) := p_item.segment6;
1389 g_kf_segment_values(7) := p_item.segment7;
1390 g_kf_segment_values(8) := p_item.segment8;
1391 g_kf_segment_values(9) := p_item.segment9;
1392 g_kf_segment_values(10) := p_item.segment10;
1393 g_kf_segment_values(11) := p_item.segment11;
1394 g_kf_segment_values(12) := p_item.segment12;
1395 g_kf_segment_values(13) := p_item.segment13;
1396 g_kf_segment_values(14) := p_item.segment14;
1397 g_kf_segment_values(15) := p_item.segment15;
1398 g_kf_segment_values(16) := p_item.segment16;
1399 g_kf_segment_values(17) := p_item.segment17;
1400 g_kf_segment_values(18) := p_item.segment18;
1401 g_kf_segment_values(19) := p_item.segment19;
1402 g_kf_segment_values(20) := p_item.segment20;
1403
1404 l_conc_segments := concat_segments(l_appl_short_name,
1405 l_key_flex_code,
1406 l_structure_number);
1407
1408 l_keystat_val := FND_FLEX_KEYVAL.Validate_Segs(
1409 OPERATION => l_validation_mode,
1410 APPL_SHORT_NAME => l_appl_short_name,
1411 KEY_FLEX_CODE => l_key_flex_code,
1412 STRUCTURE_NUMBER => l_structure_number,
1413 CONCAT_SEGMENTS => l_conc_segments,
1414 VALUES_OR_IDS => 'V',
1415 DATA_SET => p_org.organization_id
1416 );
1417
1418 IF (l_keystat_val = FALSE) THEN
1419 RETURN F;
1420 ELSE
1421 l_id := FND_FLEX_KEYVAL.combination_id;
1422 p_item.inventory_item_id := l_id;
1423 END IF;
1424 END IF;
1425
1426 SELECT *
1427 INTO p_item
1428 FROM MTL_SYSTEM_ITEMS
1429 WHERE ORGANIZATION_ID = p_org.organization_id
1430 AND INVENTORY_ITEM_ID = p_item.inventory_item_id;
1431
1432 IF p_item.mtl_transactions_enabled_flag = 'Y'
1433 THEN
1434 RETURN T;
1435 ELSE
1436 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1437 THEN
1438 FND_MESSAGE.SET_NAME('INV','INV_ITEM_TXNS_NOT_ENABLED');
1439 FND_MSG_PUB.Add;
1440 END IF;
1441 RETURN F;
1442 END IF;
1443
1444 EXCEPTION
1445
1446 WHEN NO_DATA_FOUND THEN
1447
1448 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1449 THEN
1450
1451 FND_MESSAGE.SET_NAME('INV','INV_INT_ITMCODE');
1452 FND_MSG_PUB.Add;
1453
1454 END IF;
1455
1456 RETURN F;
1457
1458 WHEN OTHERS THEN
1459
1460 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1461 THEN
1462 FND_MSG_PUB.Add_Exc_Msg
1463 ( G_PKG_NAME
1464 , 'Inventory_Item'
1465 );
1466 END IF;
1467
1468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1469
1470 END Inventory_Item;
1471
1472
1473 -- ---------------------------------------------------------------------
1474 -- ---------------------------------------------------------------------
1475 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
1476 p_org IN org,
1477 p_sub IN sub,
1478 p_validation_mode IN VARCHAR2 DEFAULT EXISTS_ONLY,
1479 p_value_or_id IN VARCHAR2 DEFAULT 'V')
1480 RETURN NUMBER
1481 IS
1482 l_appl_short_name VARCHAR2(3) := 'INV';
1483 l_key_flex_code VARCHAR2(4) := 'MTLL';
1484 l_structure_number NUMBER := 101;
1485 l_conc_segments VARCHAR2(2000);
1486 l_keystat_val BOOLEAN;
1487 l_id NUMBER;
1488
1489 BEGIN
1490
1491 -- Bug 2733385, setting the MFG_ORGANIZATION_ID profile value.
1492 if (g_orgid is null or g_orgid <> p_org.organization_id) then
1493 fnd_profile.put('MFG_ORGANIZATION_ID',p_org.organization_id);
1494 g_orgid := p_org.organization_id;
1495 end if;
1496
1497 IF (p_locator.inventory_location_id IS NULL OR
1498 p_locator.inventory_location_id = FND_API.g_miss_num)
1499 THEN
1500 g_kf_segment_values(1) := p_locator.segment1;
1501 g_kf_segment_values(2) := p_locator.segment2;
1502 g_kf_segment_values(3) := p_locator.segment3;
1503 g_kf_segment_values(4) := p_locator.segment4;
1504 g_kf_segment_values(5) := p_locator.segment5;
1505 g_kf_segment_values(6) := p_locator.segment6;
1506 g_kf_segment_values(7) := p_locator.segment7;
1507 g_kf_segment_values(8) := p_locator.segment8;
1508 g_kf_segment_values(9) := p_locator.segment9;
1509 g_kf_segment_values(10) := p_locator.segment10;
1510 g_kf_segment_values(11) := p_locator.segment11;
1511 g_kf_segment_values(12) := p_locator.segment12;
1512 g_kf_segment_values(13) := p_locator.segment13;
1513 g_kf_segment_values(14) := p_locator.segment14;
1514 g_kf_segment_values(15) := p_locator.segment15;
1515 g_kf_segment_values(16) := p_locator.segment16;
1516 g_kf_segment_values(17) := p_locator.segment17;
1517 g_kf_segment_values(18) := p_locator.segment18;
1518 g_kf_segment_values(19) := p_locator.segment19;
1519 g_kf_segment_values(20) := p_locator.segment20;
1520
1521 l_conc_segments := concat_segments(l_appl_short_name,
1522 l_key_flex_code,
1523 l_structure_number);
1524
1525 --inv_debug.message('l_conc_segments is ' || l_conc_segments);
1526
1527 l_keystat_val := FND_FLEX_KEYVAL.Validate_Segs(
1528 OPERATION => p_validation_mode,
1529 APPL_SHORT_NAME => l_appl_short_name,
1530 KEY_FLEX_CODE => l_key_flex_code,
1531 STRUCTURE_NUMBER => l_structure_number,
1532 CONCAT_SEGMENTS => l_conc_segments,
1533 VALUES_OR_IDS => p_value_or_id,
1534 DATA_SET => p_org.organization_id
1535 );
1536 IF (l_keystat_val = FALSE) THEN
1537 RETURN F;
1538 ELSE
1539 l_id := FND_FLEX_KEYVAL.combination_id;
1540 --inv_debug.message('l_id is ' || l_id);
1541 p_locator.inventory_location_id := l_id;
1542 if(p_validation_mode = EXISTS_OR_CREATE
1543 AND FND_FLEX_KEYVAL.new_combination)
1544 then
1545 --inv_debug.message('new combination');
1546 UPDATE mtl_item_locations
1547 SET subinventory_code = p_sub.secondary_inventory_name
1548 ,project_id = p_locator.project_id
1549 ,task_id = p_locator.task_id
1550 ,physical_location_id = p_locator.physical_location_id
1551 ,inventory_location_type = p_locator.inventory_location_type
1552 WHERE organization_id = p_org.organization_id
1553 AND inventory_location_id = p_locator.inventory_location_id;
1554 end if;
1555 END IF;
1556
1557 END IF;
1558
1559 SELECT *
1560 INTO p_locator
1561 FROM mtl_item_locations
1562 WHERE organization_id = p_org.organization_id
1563 AND subinventory_code = p_sub.secondary_inventory_name
1564 AND inventory_location_id = p_locator.inventory_location_id
1565 AND NVL(disable_date,SYSDATE) >= SYSDATE;
1566
1567 RETURN T;
1568
1569 EXCEPTION
1570
1571 WHEN no_data_found THEN
1572 --inv_debug.message('no data found');
1573 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1574 fnd_message.set_name('INV','INV_INT_LOCCODE');
1575 fnd_msg_pub.Add;
1576 END IF;
1577
1578 RETURN F;
1579
1580 WHEN OTHERS THEN
1581
1582 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1583 THEN
1584 fnd_msg_pub.add_exc_msg(g_pkg_name, 'validateLocator:org,sub');
1585 END IF;
1586 -- RETURN f;
1587 RAISE fnd_api.g_exc_unexpected_error;
1588
1589 END validateLocator;
1590
1591
1592 -- ---------------------------------------------------------------------
1593 -- ---------------------------------------------------------------------
1594 FUNCTION validateLocator(p_locator IN OUT nocopy locator,
1595 p_org IN org,
1596 p_sub IN sub,
1597 p_item IN item)
1598 RETURN NUMBER
1599 IS
1600 BEGIN
1601 IF p_locator.inventory_location_id IS NULL OR
1602 p_locator.inventory_location_id = FND_API.G_MISS_NUM
1603 THEN
1604 RETURN F;
1605 END IF;
1606
1607 if(p_item.restrict_locators_code <> 1) then
1608 return validateLocator(p_locator,p_org,p_sub);
1609 end if;
1610
1611 SELECT mil.*
1612 INTO p_locator
1613 FROM mtl_item_locations mil,mtl_secondary_locators msl
1614 WHERE mil.organization_id = p_org.organization_id
1615 AND mil.subinventory_code = p_sub.secondary_inventory_name
1616 AND mil.inventory_location_id = p_locator.inventory_location_id
1617 AND NVL(disable_date,SYSDATE) >= SYSDATE
1618 AND mil.organization_id = msl.organization_id
1619 AND mil.subinventory_code = msl.subinventory_code
1620 AND mil.inventory_location_id = msl.secondary_locator
1621 AND msl.inventory_item_id = p_item.inventory_item_id;
1622
1623 RETURN T;
1624
1625 EXCEPTION
1626
1627 WHEN no_data_found THEN
1628
1629 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1630 fnd_message.set_name('INV','INV_CCEOI_LOC_NOT_IN_LIST');
1631 fnd_msg_pub.Add;
1632 END IF;
1633
1634 RETURN F;
1635
1636 WHEN OTHERS THEN
1637
1638 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1639 THEN
1640 fnd_msg_pub.add_exc_msg(g_pkg_name, 'validateLocator:org,sub,item');
1641 END IF;
1642
1643 RAISE fnd_api.g_exc_unexpected_error;
1644
1645 END validateLocator;
1646
1647
1648 -- ---------------------------------------------------------------------
1649 -- ---------------------------------------------------------------------
1650 /*
1651 * Bug# 6633612
1652 * Overloaded validateLocator function for Material Status Enhancement Project
1653 */
1654 FUNCTION validateLocator( p_locator IN OUT nocopy locator,
1655 p_org IN org,
1656 p_sub IN sub,
1657 p_item IN item,
1658 p_trx_type_id IN NUMBER, -- For Bug# 6633612
1659 p_object_type IN VARCHAR2 DEFAULT 'L' -- For Bug# 6633612
1660 )
1661 RETURN NUMBER
1662 IS
1663 l_result NUMBER;
1664 l_status_result VARCHAR2(1);
1665 BEGIN
1666 /* First call the original validateLocator function,
1667 * If it returns INV_VALIDATE.T then goahead to call
1668 * inv_material_status_grp.is_status_applicable() function.
1669 */
1670 l_result := INV_VALIDATE.validateLocator(
1671 p_locator => p_locator,
1672 p_org => p_org,
1673 p_sub => p_sub,
1674 p_item => p_item);
1675
1676 IF (l_result = INV_VALIDATE.T)
1677 THEN
1678
1679 -- Make the call for inv_material_status_grp.is_status_applicable()
1680 -- with appropriate parameters
1681 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
1682 p_wms_installed => NULL,
1683 p_trx_status_enabled => NULL,
1684 p_trx_type_id => p_trx_type_id,
1685 p_lot_status_enabled => NULL,
1686 p_serial_status_enabled => NULL,
1687 p_organization_id => p_org.organization_id,
1688 p_inventory_item_id => p_item.inventory_item_id,
1689 p_sub_code => p_sub.secondary_inventory_name,
1690 p_locator_id => p_locator.inventory_location_id,
1691 p_lot_number => NULL,
1692 p_serial_number => NULL,
1693 p_object_type => p_object_type);
1694
1695 -- If l_status_result = 'N', it means that the status validation has failed.
1696 -- Assign l_result = INV_VALIDATE.F and return l_result, else return l_result
1697 -- directly.
1698 IF (l_status_result = 'N')
1699 THEN
1700 l_result := INV_VALIDATE.F;
1701 RETURN l_result;
1702 ELSE
1703 RETURN l_result;
1704 END IF;
1705
1706 ELSE
1707 -- Basic validateLocator validation has failed return l_result
1708 RETURN l_result;
1709 END IF;
1710
1711 END validateLocator;
1712
1713 -- ---------------------------------------------------------------------
1714 -- ---------------------------------------------------------------------
1715 FUNCTION Lot_Number ( p_lot IN OUT nocopy lot,
1716 p_org IN ORG,
1717 p_item IN ITEM,
1718 p_from_sub IN sub,
1719 p_loc IN LOCATOR,
1720 p_revision in VARCHAR
1721 )
1722 RETURN NUMBER
1723 IS
1724 l_dummy VARCHAR2(10);
1725
1726 BEGIN
1727 IF p_lot.lot_number IS NULL OR
1728 p_lot.lot_number = FND_API.G_MISS_CHAR
1729 THEN
1730 RETURN T;
1731 END IF;
1732
1733 IF p_item.lot_control_code = 1 THEN
1734 FND_MESSAGE.SET_NAME('INV','INV_NO_LOT_CONTROL');
1735 FND_MSG_PUB.Add;
1736 RETURN F;
1737 END IF;
1738
1739 SELECT mln.*
1740 INTO p_lot
1741 FROM MTL_LOT_NUMBERS MLN
1742 WHERE MLN.INVENTORY_ITEM_ID = p_item.inventory_item_id
1743 AND MLN.ORGANIZATION_ID = p_org.organization_id
1744 AND MLN.LOT_NUMBER = p_lot.lot_number
1745 AND MLN.LOT_NUMBER IN (SELECT LOT_NUMBER
1746 FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
1747 WHERE MOQ.INVENTORY_ITEM_ID = p_item.inventory_item_id
1748 AND MOQ.ORGANIZATION_ID = p_org.organization_id
1749 AND MOQ.LOT_NUMBER = p_lot.lot_number
1750 AND MOQ.SUBINVENTORY_CODE =
1751 NVL(p_from_sub.secondary_inventory_name,'##')
1752 AND NVL(MOQ.REVISION,'##') = NVL(p_revision,'##')
1753 AND NVL(MOQ.LOCATOR_ID,-1) = NVL(p_loc.inventory_location_id,-1)
1754 AND ROWNUM < 2);
1755
1756 RETURN T;
1757
1758 EXCEPTION
1759
1760 WHEN NO_DATA_FOUND THEN
1761
1762 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1763 THEN
1764
1765 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
1766 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','CAPS_LOT_NUMBER'),FALSE);
1767 FND_MSG_PUB.Add;
1768
1769 END IF;
1770 RETURN F;
1771
1772 WHEN OTHERS THEN
1773
1774 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1775 THEN
1776 FND_MSG_PUB.Add_Exc_Msg
1777 ( G_PKG_NAME
1778 , 'Lot_Number'
1779 );
1780 END IF;
1781
1782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1783
1784 END Lot_Number;
1785
1786 -- ---------------------------------------------------------------------
1787 -- ---------------------------------------------------------------------
1788 /*
1789 * Bug# 6633612
1790 * Overloaded Lot_Number function for Material Status Enhancement Project
1791 */
1792 FUNCTION Lot_Number ( p_lot IN OUT nocopy lot,
1793 p_org IN ORG,
1794 p_item IN ITEM,
1795 p_from_sub IN sub,
1796 p_loc IN LOCATOR,
1797 p_revision in VARCHAR,
1798 p_trx_type_id IN NUMBER, -- For Bug# 6633612
1799 p_object_type IN VARCHAR2 DEFAULT 'O' -- For Bug# 6633612
1800 )
1801 RETURN NUMBER
1802 IS
1803 l_result NUMBER;
1804 l_status_result VARCHAR2(1);
1805 BEGIN
1806 /* First call the original Lot_Number function,
1807 * If it returns INV_VALIDATE.T then goahead to call
1808 * inv_material_status_grp.is_status_applicable() function.
1809 */
1810 l_result := INV_VALIDATE.Lot_Number(
1811 p_lot => p_lot,
1812 p_org => p_org,
1813 p_item => p_item,
1814 p_from_sub => p_from_sub,
1815 p_loc => p_loc,
1816 p_revision => p_revision);
1817
1818 IF (l_result = INV_VALIDATE.T)
1819 THEN
1820
1821 -- Make the call for inv_material_status_grp.is_status_applicable()
1822 -- with appropriate parameters
1823 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
1824 p_wms_installed => NULL,
1825 p_trx_status_enabled => NULL,
1826 p_trx_type_id => p_trx_type_id,
1827 p_lot_status_enabled => NULL,
1828 p_serial_status_enabled => NULL,
1829 p_organization_id => p_org.organization_id,
1830 p_inventory_item_id => p_item.inventory_item_id,
1831 p_sub_code => p_from_sub.secondary_inventory_name,
1832 p_locator_id => p_loc.inventory_location_id,
1833 p_lot_number => p_lot.lot_number,
1834 p_serial_number => NULL,
1835 p_object_type => p_object_type);
1836
1837 -- If l_status_result = 'N', it means that the status validation has failed.
1838 -- Assign l_result = INV_VALIDATE.F and return l_result, else return l_result
1839 -- directly.
1840 IF (l_status_result = 'N')
1841 THEN
1842 l_result := INV_VALIDATE.F;
1843 RETURN l_result;
1844 ELSE
1845 RETURN l_result;
1846 END IF;
1847
1848 ELSE
1849 -- Basic Lot_Number validation has failed return l_result
1850 RETURN l_result;
1851 END IF;
1852
1853 END Lot_Number;
1854
1855
1856 -- ---------------------------------------------------------------------
1857 -- ---------------------------------------------------------------------
1858 FUNCTION Project ( p_project_id IN NUMBER )
1859 RETURN NUMBER
1860 IS
1861 l_dummy VARCHAR2(10);
1862 l_organization_id NUMBER;
1863 BEGIN
1864
1865 IF p_project_id IS NULL OR
1866 p_project_id = FND_API.G_MISS_NUM
1867 THEN
1868 RETURN T;
1869 END IF;
1870
1871 SELECT 'VALID'
1872 INTO l_dummy
1873 FROM PJM_PROJECTS_V
1874 WHERE PROJECT_ID = p_project_id;
1875
1876 RETURN T;
1877
1878 EXCEPTION
1879
1880 WHEN NO_DATA_FOUND THEN
1881
1882 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1883 THEN
1884
1885 FND_MESSAGE.SET_NAME('INV','INV_PRJ_ERR');
1886 FND_MSG_PUB.Add;
1887
1888 END IF;
1889
1890 RETURN F;
1891
1892 WHEN OTHERS THEN
1893
1894 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1895 THEN
1896 FND_MSG_PUB.Add_Exc_Msg
1897 ( G_PKG_NAME
1898 , 'Project'
1899 );
1900 END IF;
1901
1902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1903
1904 END Project;
1905
1906
1907 -- ---------------------------------------------------------------------
1908 -- ---------------------------------------------------------------------
1909 FUNCTION Quantity ( p_quantity IN NUMBER )
1910 RETURN NUMBER
1911 IS
1912 BEGIN
1913 IF p_quantity IS NULL OR
1914 p_quantity = FND_API.G_MISS_NUM
1915 THEN
1916 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1917 THEN
1918 FND_MESSAGE.SET_NAME('INV','INV_INT_QTYCODE');
1919 FND_MSG_PUB.Add;
1920 END IF;
1921 return F;
1922 END IF;
1923 RETURN T;
1924 END Quantity;
1925
1926
1927 -- ---------------------------------------------------------------------
1928 -- ---------------------------------------------------------------------
1929 FUNCTION Reason ( p_reason_id IN NUMBER )
1930 RETURN NUMBER
1931 IS
1932 l_dummy VARCHAR2(10);
1933 BEGIN
1934
1935 IF p_reason_id IS NULL OR
1936 p_reason_id = FND_API.G_MISS_NUM
1937 THEN
1938 RETURN T;
1939 END IF;
1940
1941 SELECT 'VALID'
1942 INTO l_dummy
1943 FROM MTL_TRANSACTION_REASONS
1944 WHERE NVL(DISABLE_DATE,SYSDATE) >= SYSDATE
1945 AND REASON_ID = p_reason_id;
1946
1947 RETURN T;
1948
1949 EXCEPTION
1950
1951 WHEN NO_DATA_FOUND THEN
1952
1953 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1954 THEN
1955
1956 FND_MESSAGE.SET_NAME('INV','INV_INT_REACODE');
1957 FND_MSG_PUB.Add;
1958
1959 END IF;
1960
1961 RETURN F;
1962
1963 WHEN OTHERS THEN
1964
1965 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1966 THEN
1967 FND_MSG_PUB.Add_Exc_Msg
1968 ( G_PKG_NAME
1969 , 'Reason'
1970 );
1971 END IF;
1972
1973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1974
1975 END Reason;
1976
1977
1978 -- ---------------------------------------------------------------------
1979 -- ---------------------------------------------------------------------
1980 FUNCTION Reference ( p_reference IN VARCHAR2 )
1981 RETURN NUMBER
1982 IS
1983 l_dummy VARCHAR2(10);
1984 BEGIN
1985 RETURN T;
1986 END Reference;
1987
1988
1989 -- ---------------------------------------------------------------------
1990 -- ---------------------------------------------------------------------
1991 FUNCTION Reference ( p_reference_id IN NUMBER, p_reference_type_code IN NUMBER )
1992 RETURN NUMBER
1993 IS
1994 l_dummy VARCHAR2(10);
1995 BEGIN
1996
1997 IF p_reference_id IS NULL OR
1998 p_reference_id = FND_API.G_MISS_NUM
1999 THEN
2000 RETURN T;
2001 END IF;
2002
2003 IF p_reference_type_code = INV_Transfer_Order_PVT.G_Ref_type_Kanban
2004 THEN
2005 SELECT 'VALID'
2006 INTO l_dummy
2007 FROM MTL_KANBAN_CARDS
2008 WHERE EXISTS (SELECT 1
2009 FROM MFG_LOOKUPS
2010 WHERE LOOKUP_TYPE = 'MTL_TXN_REQUEST_SOURCE'
2011 AND LOOKUP_CODE = p_reference_type_code)
2012 AND KANBAN_CARD_ID = p_reference_id;
2013 END IF;
2014 RETURN T;
2015
2016 EXCEPTION
2017
2018 WHEN NO_DATA_FOUND THEN
2019
2020 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2021 THEN
2022
2023 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
2024 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','INV_REFERENCE_ID'),FALSE); -- ND
2025 FND_MSG_PUB.Add;
2026
2027 END IF;
2028
2029 RETURN F;
2030
2031 WHEN OTHERS THEN
2032
2033 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2034 THEN
2035 FND_MSG_PUB.Add_Exc_Msg
2036 ( G_PKG_NAME
2037 , 'Reference'
2038 );
2039 END IF;
2040
2041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2042
2043 END Reference;
2044
2045
2046 -- ---------------------------------------------------------------------
2047 -- ---------------------------------------------------------------------
2048 FUNCTION Reference_Type ( p_reference_type_code IN NUMBER )
2049 RETURN NUMBER
2050 IS
2051 l_dummy VARCHAR2(10);
2052 BEGIN
2053
2054 IF p_reference_type_code IS NULL OR
2055 p_reference_type_code = FND_API.G_MISS_NUM
2056 THEN
2057 RETURN T;
2058 END IF;
2059
2060 SELECT 'VALID'
2061 INTO l_dummy
2062 FROM MFG_LOOKUPS
2063 WHERE LOOKUP_TYPE = 'MTL_TXN_REQUEST_SOURCE'
2064 AND LOOKUP_CODE = p_reference_type_code;
2065
2066 RETURN T;
2067
2068 EXCEPTION
2069
2070 WHEN NO_DATA_FOUND THEN
2071
2072 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2073 THEN
2074
2075 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
2076 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','INV_REFERENCE_TYPE'),FALSE); -- ND;
2077 FND_MSG_PUB.Add;
2078
2079 END IF;
2080
2081 RETURN F;
2082
2083 WHEN OTHERS THEN
2084
2085 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2086 THEN
2087 FND_MSG_PUB.Add_Exc_Msg
2088 ( G_PKG_NAME
2089 , 'Reference_Type'
2090 );
2091 END IF;
2092
2093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2094
2095 END Reference_Type;
2096
2097
2098 -- ---------------------------------------------------------------------
2099 -- ---------------------------------------------------------------------
2100 FUNCTION Revision ( p_revision IN VARCHAR2, p_org IN ORG, p_item IN ITEM )
2101 RETURN NUMBER
2102 IS
2103 l_dummy VARCHAR2(10);
2104 BEGIN
2105 IF p_revision IS NULL OR
2106 p_revision = FND_API.G_MISS_CHAR
2107 THEN
2108 RETURN T;
2109 END IF;
2110
2111 IF p_item.revision_qty_control_code = 1 THEN
2112 FND_MESSAGE.SET_NAME('INV','INV_NO_REVISION_CONTROL'); -- ND
2113 FND_MSG_PUB.Add;
2114 RETURN F;
2115 END IF;
2116
2117 SELECT 'VALID'
2118 INTO l_dummy
2119 FROM MTL_ITEM_REVISIONS
2120 WHERE ORGANIZATION_ID = p_org.organization_id
2121 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
2122 AND REVISION = p_revision;
2123
2124 RETURN T;
2125
2126 EXCEPTION
2127
2128 WHEN NO_DATA_FOUND THEN
2129
2130 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2131 THEN
2132
2133 FND_MESSAGE.SET_NAME('INV','INV_INT_REVCODE');
2134 FND_MSG_PUB.Add;
2135
2136 END IF;
2137 RETURN F;
2138
2139 WHEN OTHERS THEN
2140
2141 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2142 THEN
2143 FND_MSG_PUB.Add_Exc_Msg
2144
2145 ( G_PKG_NAME
2146 , 'Revision'
2147 );
2148 END IF;
2149
2150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2151
2152 END Revision;
2153
2154
2155 -- ---------------------------------------------------------------------
2156 -- ---------------------------------------------------------------------
2157 function check_serial( p_serial IN OUT nocopy serial,
2158 p_org IN ORG,
2159 p_item IN ITEM,
2160 p_from_sub IN sub,
2161 p_lot in lot,
2162 p_loc in locator,
2163 p_revision in VARCHAR2,
2164 p_msg IN VARCHAR2)
2165 RETURN NUMBER
2166 IS
2167 l_dummy VARCHAR2(10);
2168
2169 BEGIN
2170
2171 IF p_serial.serial_number IS NULL OR
2172 p_serial.serial_number = FND_API.G_MISS_CHAR
2173 THEN
2174 RETURN T;
2175 END IF;
2176
2177 IF p_item.serial_number_control_code = 1 THEN
2178 FND_MESSAGE.SET_NAME('INV','INV_ITEM_NOT_SERIAL_CONTROLLED');
2179 FND_MSG_PUB.Add;
2180 RETURN F;
2181 END IF;
2182
2183 IF p_item.serial_number_control_code = 2 THEN
2184
2185 -- Bug# 7149590
2186 -- Removed the condition AND (GROUP_MARK_ID IS NULL OR GROUP_MARK_ID = -1)
2187 -- because while ship confirming predefined serials, the group_mark_id is
2188 -- still not null and is stamped with a valid value.
2189 SELECT *
2190 INTO p_serial
2191 FROM MTL_SERIAL_NUMBERS
2192 WHERE INVENTORY_ITEM_ID = p_item.inventory_item_id
2193 AND CURRENT_ORGANIZATION_ID = p_org.organization_id
2194 AND SERIAL_NUMBER = p_serial.serial_number
2195 AND ((NVL(CURRENT_SUBINVENTORY_CODE,'@@@') =
2196 NVL(p_from_sub.secondary_inventory_name,'@@@')
2197 AND NVL(CURRENT_LOCATOR_ID,-1)=NVL(p_loc.inventory_location_id,-1)
2198 AND NVL(LOT_NUMBER,'@@@') = NVL(p_lot.lot_number,'@@@')
2199 AND NVL(REVISION,'@@@') = NVL(p_revision,'@@@')
2200 AND CURRENT_STATUS = 3));
2201
2202 RETURN T;
2203 ELSE
2204 -- serial number control code should be equal to 5 or 6,
2205 -- both are dynamically generated serial numbers
2206 -- Bug# 6898243
2207 -- Ship confirmation was failing for lot + serial @ SO Issue control. This was occuring
2208 -- because at the time of Ship Confirm the serial would be present in status 1
2209 -- and in this status, lot_number will NOT be stamped in MSN.
2210 -- Status 1 or 6 serials will not have lot/revision stamped in MSN.
2211
2212 SELECT *
2213 INTO p_serial
2214 FROM MTL_SERIAL_NUMBERS
2215 WHERE INVENTORY_ITEM_ID = p_item.inventory_item_id
2216 AND CURRENT_ORGANIZATION_ID = p_org.organization_id
2217 AND SERIAL_NUMBER = p_serial.serial_number
2218 AND (CURRENT_STATUS IN (1,6) OR NVL(LOT_NUMBER,'@@@') = NVL(p_lot.lot_number,'@@@'))
2219 AND (CURRENT_STATUS IN (1,6) OR NVL(REVISION,'@@@') = NVL(p_revision,'@@@'))
2220 AND CURRENT_STATUS IN (1, 3, 6);
2221
2222 -- End Bug# 6898243
2223
2224 RETURN T;
2225 END IF;
2226
2227 EXCEPTION
2228
2229 WHEN NO_DATA_FOUND THEN
2230
2231 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2232 THEN
2233
2234 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
2235 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV',p_msg),FALSE); -- ND
2236
2237 FND_MSG_PUB.Add;
2238
2239 END IF;
2240
2241 RETURN F;
2242
2243 WHEN OTHERS THEN
2244
2245 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2246 THEN
2247 FND_MSG_PUB.Add_Exc_Msg
2248 ( G_PKG_NAME
2249 , 'Check_serial_number'
2250 );
2251 END IF;
2252
2253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2254
2255 end check_serial;
2256
2257 -- ---------------------------------------------------------------------
2258 -- ---------------------------------------------------------------------
2259 FUNCTION Validate_serial ( p_serial IN OUT nocopy serial,
2260 p_org IN ORG,
2261 p_item IN ITEM,
2262 p_from_sub IN sub,
2263 p_lot in lot,
2264 p_loc in locator,
2265 p_revision in VARCHAR2)
2266 RETURN NUMBER
2267 IS
2268
2269 BEGIN
2270
2271 return check_serial(p_serial,p_org,p_item,p_from_sub,
2272 p_lot,p_loc, p_revision,
2273 'CAPS_SERIAL_NUMBER');
2274
2275 END Validate_serial;
2276
2277 -- ---------------------------------------------------------------------
2278 -- ---------------------------------------------------------------------
2279 /*
2280 * Bug# 6633612
2281 * Overloaded Validate_serial function for Material Status Enhancement Project
2282 */
2283 FUNCTION Validate_serial ( p_serial IN OUT nocopy serial,
2284 p_org IN ORG,
2285 p_item IN ITEM,
2286 p_from_sub IN sub,
2287 p_lot in lot,
2288 p_loc in locator,
2289 p_revision in VARCHAR2,
2290 p_trx_type_id IN NUMBER, -- For Bug# 6633612
2291 p_object_type IN VARCHAR2 DEFAULT 'S' -- For Bug# 6633612
2292 )
2293 RETURN NUMBER
2294 IS
2295 l_result NUMBER;
2296 l_status_result VARCHAR2(1);
2297 BEGIN
2298 /* First call the original Validate_serial function,
2299 * If it returns INV_VALIDATE.T then goahead to call
2300 * inv_material_status_grp.is_status_applicable() function.
2301 */
2302 l_result := INV_VALIDATE.Validate_serial(
2303 p_serial => p_serial,
2304 p_org => p_org,
2305 p_item => p_item,
2306 p_from_sub => p_from_sub,
2307 p_lot => p_lot,
2308 p_loc => p_loc,
2309 p_revision => p_revision);
2310
2311 IF (l_result = INV_VALIDATE.T)
2312 THEN
2313
2314 -- Make the call for inv_material_status_grp.is_status_applicable()
2315 -- with appropriate parameters
2316 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2317 p_wms_installed => NULL,
2318 p_trx_status_enabled => NULL,
2319 p_trx_type_id => p_trx_type_id,
2320 p_lot_status_enabled => NULL,
2321 p_serial_status_enabled => NULL,
2322 p_organization_id => p_org.organization_id,
2323 p_inventory_item_id => p_item.inventory_item_id,
2324 p_sub_code => p_from_sub.secondary_inventory_name,
2325 p_locator_id => p_loc.inventory_location_id,
2326 p_lot_number => p_lot.lot_number,
2327 p_serial_number => p_serial.serial_number,
2328 p_object_type => p_object_type);
2329
2330 -- If l_status_result = 'N', it means that status validation has failed.
2331 -- Assign l_result = INV_VALIDATE.F and return l_result, else return l_result
2332 -- directly.
2333 IF (l_status_result = 'N')
2334 THEN
2335 l_result := INV_VALIDATE.F;
2336 RETURN l_result;
2337 ELSE
2338 RETURN l_result;
2339 END IF;
2340
2341 ELSE
2342 -- Basic Validate_serial validation has failed return l_result
2343 RETURN l_result;
2344 END IF;
2345
2346 END Validate_serial;
2347
2348 -- ---------------------------------------------------------------------
2349 -- ---------------------------------------------------------------------
2350
2351 /*
2352 * Bug# 6633612
2353 * Validate_serial_range function for Material Status Enhancement Project
2354 */
2355
2356 FUNCTION validate_serial_range(p_fm_serial IN OUT nocopy SERIAL_NUMBER_TBL,
2357 p_to_serial IN OUT nocopy SERIAL_NUMBER_TBL,
2358 p_org in ORG,
2359 p_item IN ITEM,
2360 p_from_sub IN sub,
2361 p_lot in lot,
2362 p_loc in locator,
2363 p_revision in VARCHAR2,
2364 p_trx_type_id IN NUMBER, -- For Bug# 6633612
2365 p_object_type IN VARCHAR2 DEFAULT 'S', -- For Bug# 6633612
2366 x_errored_serials OUT nocopy SERIAL_NUMBER_TBL -- For Bug# 6633612
2367 )
2368 RETURN NUMBER
2369 IS
2370 l_result NUMBER;
2371 l_status_result VARCHAR2(1);
2372
2373 l_prefix VARCHAR2(30);
2374 l_quantity NUMBER;
2375 l_from_number VARCHAR2(30);
2376 l_to_number VARCHAR2(30);
2377 l_errorcode NUMBER;
2378 l_number_part NUMBER := 0;
2379 l_counter NUMBER := 0;
2380 l_length NUMBER;
2381 l_padded_length NUMBER;
2382 l_temp_serial serial;
2383 l_errored_serial_count NUMBER := 0;
2384
2385 BEGIN
2386
2387 -- From and To Serial is not of the same size return with INV_VALIDATE.F.
2388 IF((p_fm_serial IS NULL) OR (p_to_serial IS NULL) OR (p_fm_serial.LAST <> p_to_serial.LAST)) THEN
2389 l_result := INV_VALIDATE.F;
2390 RETURN l_result;
2391 END IF;
2392
2393 -- Check for the material status of sub,locator and lot if p_object_type = 'A'.
2394 IF (p_object_type = 'A') THEN
2395 -- Make the call for inv_material_status_grp.is_status_applicable()
2396 -- with appropriate parameters
2397 IF (p_from_sub.secondary_inventory_name IS NOT NULL) THEN
2398 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2399 p_wms_installed => NULL,
2400 p_trx_status_enabled => NULL,
2401 p_trx_type_id => p_trx_type_id,
2402 p_lot_status_enabled => NULL,
2403 p_serial_status_enabled => NULL,
2404 p_organization_id => p_org.organization_id,
2405 p_inventory_item_id => p_item.inventory_item_id,
2406 p_sub_code => p_from_sub.secondary_inventory_name,
2407 p_locator_id => NULL,
2408 p_lot_number => NULL,
2409 p_serial_number => NULL,
2410 p_object_type => 'Z');
2411 -- Subinventory status validation has failed
2412 IF (l_status_result = 'N')
2413 THEN
2414 l_result := INV_VALIDATE.F;
2415 RETURN l_result;
2416 END IF;
2417 END IF;
2418
2419 IF (p_loc.inventory_location_id IS NOT NULL) THEN
2420 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2421 p_wms_installed => NULL,
2422 p_trx_status_enabled => NULL,
2423 p_trx_type_id => p_trx_type_id,
2424 p_lot_status_enabled => NULL,
2425 p_serial_status_enabled => NULL,
2426 p_organization_id => p_org.organization_id,
2427 p_inventory_item_id => p_item.inventory_item_id,
2428 p_sub_code => p_from_sub.secondary_inventory_name,
2429 p_locator_id => p_loc.inventory_location_id,
2430 p_lot_number => NULL,
2431 p_serial_number => NULL,
2432 p_object_type => 'L');
2433 -- Locator status validation has failed
2434 IF (l_status_result = 'N')
2435 THEN
2436 l_result := INV_VALIDATE.F;
2437 RETURN l_result;
2438 END IF;
2439 END IF;
2440
2441 IF (p_lot.lot_number IS NOT NULL) THEN
2442 l_status_result := INV_MATERIAL_STATUS_GRP.is_status_applicable(
2443 p_wms_installed => NULL,
2444 p_trx_status_enabled => NULL,
2445 p_trx_type_id => p_trx_type_id,
2446 p_lot_status_enabled => NULL,
2447 p_serial_status_enabled => NULL,
2448 p_organization_id => p_org.organization_id,
2449 p_inventory_item_id => p_item.inventory_item_id,
2450 p_sub_code => p_from_sub.secondary_inventory_name,
2451 p_locator_id => p_loc.inventory_location_id,
2452 p_lot_number => p_lot.lot_number,
2453 p_serial_number => NULL,
2454 p_object_type => 'O');
2455 -- Lot status validation has failed
2456 IF (l_status_result = 'N')
2457 THEN
2458 l_result := INV_VALIDATE.F;
2459 RETURN l_result;
2460 END IF;
2461 END IF;
2462 END IF;-- End of p_object_type = 'A'
2463
2464 -- Material status validation for sub, locator and lot has passed
2465 -- Going ahead to explode intermediate serials within a range and checking
2466 -- for material status and basic validity of each serial, by calling the
2467 -- overloaded Validate_serial function.
2468
2469 -- Clearing the previously cached errored serials and initializing l_errored_serial_count
2470 -- to 0.
2471 x_errored_serials.DELETE;
2472 l_errored_serial_count := 0;
2473
2474 FOR v_counter IN p_fm_serial.FIRST .. p_fm_serial.LAST
2475 LOOP
2476
2477 -- Calling the API to get the range of serials in between a sub-range.
2478 IF NOT MTL_SERIAL_CHECK.INV_SERIAL_INFO(p_from_serial_number => p_fm_serial(v_counter) ,
2479 p_to_serial_number => p_to_serial(v_counter) ,
2480 x_prefix => l_prefix,
2481 x_quantity => l_quantity,
2482 x_from_number => l_from_number,
2483 x_to_number => l_to_number,
2484 x_errorcode => l_errorcode)
2485 THEN
2486 l_result := INV_VALIDATE.F;
2487 RETURN l_result;
2488 END IF;
2489
2490 l_number_part := TO_NUMBER(l_from_number);
2491 l_counter := 1;
2492 -- Get the length of the serial number
2493 l_length := LENGTH(p_fm_serial(v_counter));
2494
2495 WHILE (l_counter <= l_quantity) LOOP
2496
2497 -- The padded length will be the length of the serial number minus
2498 -- the length of the number part
2499 l_padded_length := l_length - LENGTH(l_number_part);
2500 l_temp_serial.serial_number := RPAD(NVL(l_prefix,'0'), l_padded_length, '0') ||l_number_part;
2501 -- Calling the overloaded Validate_serial function.
2502 l_result := INV_VALIDATE.Validate_serial(
2503 p_serial => l_temp_serial,
2504 p_org => p_org,
2505 p_item => p_item,
2506 p_from_sub => p_from_sub,
2507 p_lot => p_lot,
2508 p_loc => p_loc,
2509 p_revision => p_revision,
2510 p_trx_type_id => p_trx_type_id,
2511 p_object_type => 'S'
2512 );
2513 -- Material status for an intermediate serial has failed, add the serial to the
2514 -- errored serials list and when the count reaches 10 return from the function.
2515 IF (l_result = INV_VALIDATE.F) THEN
2516 l_errored_serial_count := l_errored_serial_count + 1;
2517 x_errored_serials(l_errored_serial_count) := l_temp_serial.serial_number;
2518 IF (l_errored_serial_count = 10) THEN
2519 l_result := INV_VALIDATE.F;
2520 RETURN l_result;
2521 END IF;
2522 END IF;
2523
2524 l_number_part := l_number_part + 1;
2525 l_counter := l_counter + 1;
2526
2527 END LOOP; -- End of WHILE
2528 END LOOP; -- End of FOR
2529
2530 -- If lesser than 10 serials has failed then set l_result to INV_VALIDATE.F
2531 -- and return.
2532 IF (l_errored_serial_count <> 0) THEN
2533 l_result := INV_VALIDATE.F;
2534 RETURN l_result;
2535 ELSE
2536 -- Entire range of serials has passed for material status
2537 -- set the l_result to INV_VALIDATE.T and return
2538 l_result := INV_VALIDATE.T;
2539 RETURN l_result;
2540 END IF;
2541
2542 EXCEPTION
2543 WHEN OTHERS THEN
2544 -- Exception has occured somewhere set the l_result to INV_VALIDATE.F
2545 -- and return
2546 l_result := INV_VALIDATE.F;
2547 RETURN l_result;
2548
2549 END validate_serial_range;
2550
2551 -- ---------------------------------------------------------------------
2552 -- ---------------------------------------------------------------------
2553
2554 -- ---------------------------------------------------------------------
2555 -- ---------------------------------------------------------------------
2556 FUNCTION Serial_Number_End ( p_serial IN OUT nocopy serial,
2557 p_org IN ORG,
2558 p_item IN ITEM,
2559 p_from_sub IN sub,
2560 p_lot in lot,
2561 p_loc in locator,
2562 p_revision in VARCHAR2)
2563 RETURN NUMBER
2564 IS
2565
2566 BEGIN
2567
2568 return check_serial(p_serial,p_org,p_item,p_from_sub,
2569 p_lot,p_loc, p_revision,
2570 'INV_END_SERIAL_NUMBER');
2571
2572 END Serial_Number_End;
2573
2574
2575 -- ---------------------------------------------------------------------
2576 -- ---------------------------------------------------------------------
2577 FUNCTION Serial_Number_Start ( p_serial IN OUT nocopy serial,
2578 p_org IN ORG,
2579 p_item IN ITEM,
2580 p_from_sub IN sub,
2581 p_lot in lot,
2582 p_loc in locator,
2583 p_revision in VARCHAR2)
2584 RETURN NUMBER
2585 IS
2586 l_dummy VARCHAR2(10);
2587 BEGIN
2588
2589 return check_serial(p_serial,p_org,p_item,p_from_sub,
2590 p_lot,p_loc, p_revision,
2591 'INV_START_SERIAL_NUMBER');
2592
2593
2594 END Serial_Number_Start;
2595
2596
2597 -- ---------------------------------------------------------------------
2598 -- ---------------------------------------------------------------------
2599 FUNCTION subinventory(p_sub IN OUT NOCOPY sub,
2600 p_org IN org)
2601 RETURN NUMBER
2602 IS
2603 BEGIN
2604 IF p_sub.secondary_inventory_name IS NULL OR
2605 p_sub.secondary_inventory_name = fnd_api.g_miss_char
2606 THEN
2607 RETURN F;
2608 END IF;
2609
2610 SELECT *
2611 INTO p_sub
2612 FROM mtl_secondary_inventories
2613 WHERE secondary_inventory_name = p_sub.secondary_inventory_name
2614 AND organization_id = p_org.organization_id
2615 AND NVL(disable_date,sysdate+1) > sysdate;
2616
2617 RETURN T;
2618
2619 EXCEPTION
2620
2621 WHEN no_data_found THEN
2622
2623 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2624 THEN
2625 fnd_message.set_name('INV','INV_INVALID_SUBINV');
2626 fnd_msg_pub.ADD;
2627 END IF;
2628
2629 RETURN F;
2630
2631 WHEN OTHERS THEN
2632
2633 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2634 THEN
2635 fnd_msg_pub.add_exc_msg(g_pkg_name,'Subinventory');
2636 END IF;
2637
2638 RAISE fnd_api.g_exc_unexpected_error;
2639
2640 END subinventory;
2641
2642
2643
2644 -- ---------------------------------------------------------------------
2645 -- ---------------------------------------------------------------------
2646 FUNCTION subinventory(p_sub IN OUT NOCOPY sub,
2647 p_org IN org,
2648 p_item IN ITEM)
2649 RETURN NUMBER
2650 IS
2651 BEGIN
2652 IF p_sub.secondary_inventory_name IS NULL OR
2653 p_sub.secondary_inventory_name = fnd_api.g_miss_char
2654 THEN
2655 RETURN F;
2656 END IF;
2657
2658 SELECT msi.*
2659 INTO p_sub
2660 FROM mtl_secondary_inventories msi,mtl_item_sub_inventories misi
2661 WHERE msi.secondary_inventory_name = p_sub.secondary_inventory_name
2662 AND msi.organization_id = p_org.organization_id
2663 AND NVL(MSI.DISABLE_DATE,SYSDATE) >= SYSDATE
2664 AND msi.organization_id = misi.organization_id
2665 AND msi.secondary_inventorY_name = misi.secondary_inventory
2666 AND misi.inventory_item_id = p_item.inventory_item_id;
2667
2668 RETURN T;
2669
2670 EXCEPTION
2671
2672 WHEN no_data_found THEN
2673
2674 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2675 THEN
2676 fnd_message.set_name('INV','INV_INT_RESSUBEXP');
2677 fnd_msg_pub.ADD;
2678 END IF;
2679
2680 RETURN F;
2681
2682 WHEN OTHERS THEN
2683
2684 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2685 THEN
2686 fnd_msg_pub.add_exc_msg(g_pkg_name,'Subinventory');
2687 END IF;
2688
2689 RAISE fnd_api.g_exc_unexpected_error;
2690
2691 END subinventory;
2692
2693
2694 -- ---------------------------------------------------------------------
2695 -- ---------------------------------------------------------------------
2696 FUNCTION Task ( p_task_id IN NUMBER, p_project_id IN NUMBER )
2697
2698 RETURN NUMBER
2699 IS
2700 l_dummy VARCHAR2(10);
2701 BEGIN
2702
2703 IF p_task_id IS NULL OR
2704 p_task_id = FND_API.G_MISS_NUM
2705 THEN
2706 RETURN T;
2707 END IF;
2708
2709 SELECT 'VALID'
2710 INTO l_dummy
2711 FROM PJM_TASKS_V
2712 WHERE PROJECT_ID = p_project_id
2713 AND TASK_ID = p_task_id;
2714
2715 RETURN T;
2716
2717 EXCEPTION
2718
2719 WHEN NO_DATA_FOUND THEN
2720
2721 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2722 THEN
2723
2724 FND_MESSAGE.SET_NAME('INV','INV_TASK_ERR');
2725 FND_MSG_PUB.Add;
2726
2727 END IF;
2728
2729 RETURN F;
2730
2731 WHEN OTHERS THEN
2732
2733 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2734 THEN
2735 FND_MSG_PUB.Add_Exc_Msg
2736 ( G_PKG_NAME
2737 , 'Task'
2738 );
2739 END IF;
2740
2741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2742
2743 END Task;
2744
2745
2746 -- ---------------------------------------------------------------------
2747 -- ---------------------------------------------------------------------
2748 FUNCTION To_Locator ( p_locator IN OUT nocopy locator,
2749 p_org IN ORG,
2750 p_item IN ITEM,
2751 p_to_sub IN SUB,
2752 p_project_id IN NUMBER,
2753 p_task_id IN NUMBER,
2754 p_txn_action_id IN NUMBER)
2755
2756 RETURN NUMBER
2757 IS
2758
2759 BEGIN
2760
2761 return check_locator(p_locator,
2762 p_org, p_item,p_to_sub,
2763 p_project_id, p_task_id,
2764 p_txn_action_id, f, true);
2765
2766 END To_Locator;
2767
2768
2769 -- ---------------------------------------------------------------------
2770 -- ---------------------------------------------------------------------
2771 FUNCTION Transaction_Header ( p_transaction_header_id IN NUMBER )
2772 RETURN NUMBER
2773 IS
2774 BEGIN
2775 RETURN T;
2776 END Transaction_Header;
2777
2778
2779 FUNCTION HR_Location(p_hr_location IN NUMBER)
2780 RETURN NUMBER
2781 IS
2782 l_dummy VARCHAR2(20);
2783 BEGIN
2784
2785 IF p_hr_location IS NULL OR
2786 p_hr_location = FND_API.G_MISS_NUM
2787 then
2788 return T;
2789 END IF;
2790
2791 --Bug 6270813, the ship to location LOV on the move orders form fetches locations
2792 --from both HR_LOCATIONS and HZ_LOCATIONS tables, hence added an exception in the
2793 --HR_LOCATIONS query and have put another query for checking the location in HZ_LOCATIONS.
2794
2795 BEGIN
2796
2797 SELECT 'valid'
2798 INTO l_dummy
2799 FROM HR_LOCATIONS
2800 WHERE LOCATION_ID = p_hr_location;
2801
2802 EXCEPTION
2803 WHEN NO_DATA_FOUND THEN
2804
2805 SELECT 'valid'
2806 INTO l_dummy
2807 FROM HZ_LOCATIONS
2808 WHERE LOCATION_ID = p_hr_location;
2809 END;
2810
2811 RETURN T;
2812
2813 EXCEPTION
2814 WHEN NO_DATA_FOUND THEN
2815
2816 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2817 THEN
2818
2819 FND_MESSAGE.SET_NAME('INV','INV_INVALID_LOCATION');
2820 FND_MSG_PUB.Add;
2821
2822 END IF;
2823
2824 RETURN F;
2825
2826 WHEN OTHERS THEN
2827
2828 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2829 THEN
2830 FND_MSG_PUB.Add_Exc_Msg
2831 ( G_PKG_NAME
2832 , 'Task'
2833 );
2834 END IF;
2835
2836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2837 END;
2838
2839
2840 -- ---------------------------------------------------------------------
2841 -- ---------------------------------------------------------------------
2842 FUNCTION Uom ( p_uom_code IN VARCHAR2, p_org IN ORG, p_item IN ITEM )
2843 RETURN NUMBER
2844 IS
2845 l_dummy VARCHAR2(10);
2846 BEGIN
2847
2848 IF p_uom_code IS NULL OR
2849 p_uom_code = FND_API.G_MISS_CHAR
2850 THEN
2851 RETURN F;
2852 END IF;
2853
2854 --Bug 2902031
2855 IF INV_CONVERT.validate_item_uom( p_uom_code => p_uom_code
2856 ,p_item_id => p_item.inventory_item_id
2857 ,p_organization_id => p_org.organization_id) THEN
2858
2859 RETURN T;
2860 END IF;
2861
2862 /* SELECT 'VALID'
2863 INTO l_dummy
2864 FROM MTL_ITEM_UOMS_VIEW
2865 WHERE ORGANIZATION_ID = p_org.organization_id
2866 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
2867 AND UOM_CODE = p_uom_code;
2868
2869 RETURN T;*/
2870
2871 EXCEPTION
2872
2873 WHEN NO_DATA_FOUND THEN
2874
2875 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2876 THEN
2877
2878 FND_MESSAGE.SET_NAME('INV','INV-NO ITEM UOM');
2879 FND_MSG_PUB.Add;
2880
2881 END IF;
2882
2883 RETURN F;
2884
2885 WHEN OTHERS THEN
2886
2887 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2888 THEN
2889 FND_MSG_PUB.Add_Exc_Msg
2890 ( G_PKG_NAME
2891 , 'Uom'
2892 );
2893 END IF;
2894
2895 RETURN F; --Bug2902031
2896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2897
2898 END Uom;
2899
2900 -- END GEN validate
2901
2902 PROCEDURE NUMBER_FROM_SEQUENCE (
2903 p_sequence IN VARCHAR2,
2904 x_prefix OUT NOCOPY VARCHAR2,
2905 x_number OUT NOCOPY NUMBER)
2906 IS
2907 l_ascii_0 NUMBER;
2908 l_ascii_code_minus_ascii_0 NUMBER;
2909 l_sequence_length NUMBER;
2910 l_loop_index NUMBER;
2911 BEGIN
2912 l_sequence_length := LENGTH(p_sequence);
2913 l_loop_index := l_sequence_length;
2914 l_ascii_0 := ASCII('0');
2915 WHILE l_loop_index >= 1 LOOP
2916 l_ascii_code_minus_ascii_0 := ASCII(SUBSTR(p_sequence,l_loop_index,1)) - l_ascii_0;
2917 EXIT WHEN (0 > l_ascii_code_minus_ascii_0 OR
2918 l_ascii_code_minus_ascii_0 > 9);
2919 l_loop_index := l_loop_index - 1;
2920 END LOOP;
2921 if(l_loop_index = 0) then
2922 x_prefix := '';
2923 x_number := TO_NUMBER(p_sequence);
2924 elsif(l_loop_index = l_sequence_length) then
2925 x_prefix := p_sequence;
2926 x_number := -1;
2927 else
2928 x_prefix := SUBSTR(p_sequence,1,l_loop_index);
2929 x_number := TO_NUMBER(SUBSTR(p_sequence,l_loop_index+1));
2930 end if;
2931
2932 END NUMBER_FROM_SEQUENCE;
2933
2934
2935 FUNCTION Cost_Group(
2936 p_cost_group_id IN NUMBER,
2937 p_org_id IN NUMBER) return NUMBER
2938 IS
2939 l_cost_group_id NUMBER;
2940 l_org_id NUMBER;
2941 l_result NUMBER;
2942 l_dummy VARCHAR2(10);
2943 BEGIN
2944 select 'VALID'
2945 into l_dummy
2946 from cst_cost_groups
2947 where cost_group_id = p_cost_group_id;
2948 return T;
2949 Exception
2950 when no_data_found then
2951 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2952 FND_MESSAGE.SET_NAME('INV','INV_CST_GRP');
2953 FND_MSG_PUB.Add;
2954 END IF;
2955 return F;
2956
2957 WHEN OTHERS THEN
2958 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2959 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Cost Group');
2960 END IF;
2961 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2962 return F;
2963 END;
2964
2965 FUNCTION LPN( p_lpn_id IN NUMBER) return NUMBER
2966 IS
2967 l_dummy VARCHAR2(10);
2968 BEGIN
2969 select 'valid'
2970 into l_dummy
2971 from wms_license_plate_numbers
2972 where lpn_id = p_lpn_id;
2973
2974 RETURN t;
2975
2976 Exception
2977 when no_data_found then
2978 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2979 FND_MESSAGE.SET_NAME('INV','INV_INVALID_LPN');
2980 FND_MSG_PUB.Add;
2981 END IF;
2982 return F;
2983
2984 WHEN OTHERS THEN
2985 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2986 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'LPN');
2987 END IF;
2988 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2989 return F;
2990 END;
2991
2992
2993
2994 -- ---------------------------------------------------------------------
2995 -- ---------------------------------------------------------------------
2996
2997
2998 /*** Validates a lot in the context of an org and item ***/
2999 FUNCTION Lot_Number(p_lot IN OUT nocopy lot,
3000 p_org IN ORG,
3001 p_item IN ITEM)
3002 RETURN NUMBER
3003 IS
3004 l_dummy VARCHAR2(10);
3005
3006 BEGIN
3007 IF p_lot.lot_number IS NULL OR
3008 p_lot.lot_number = FND_API.G_MISS_CHAR
3009 THEN
3010 RETURN T;
3011 END IF;
3012
3013 IF p_item.lot_control_code = 1 THEN
3014 FND_MESSAGE.SET_NAME('INV','INV_NO_LOT_CONTROL');
3015 FND_MSG_PUB.Add;
3016 RETURN F;
3017 END IF;
3018
3019
3020 SELECT mln.*
3021 INTO p_lot
3022 FROM MTL_LOT_NUMBERS MLN
3023 WHERE MLN.INVENTORY_ITEM_ID = p_item.inventory_item_id
3024 AND MLN.ORGANIZATION_ID = p_org.organization_id
3025 AND MLN.LOT_NUMBER = p_lot.lot_number;
3026
3027 RETURN T;
3028
3029 EXCEPTION
3030
3031 WHEN NO_DATA_FOUND THEN
3032
3033 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3034 THEN
3035
3036 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
3037 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','CAPS_LOT_NUMBER'),FALSE);
3038 FND_MSG_PUB.Add;
3039
3040 END IF;
3041 RETURN F;
3042
3043 WHEN OTHERS THEN
3044
3045 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3046 THEN
3047 FND_MSG_PUB.Add_Exc_Msg
3048 ( G_PKG_NAME
3049 , 'Lot_Number'
3050 );
3051 END IF;
3052
3053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3054
3055 END Lot_Number;
3056 -- ---------------------------------------------------------------------
3057 --INVCONV
3058 -- ---------------------------------------------------------------------
3059 FUNCTION Secondary_Quantity ( p_Secondary_quantity IN NUMBER )
3060 RETURN NUMBER
3061 IS
3062 BEGIN
3063 IF p_secondary_quantity IS NULL OR
3064 p_secondary_quantity = FND_API.G_MISS_NUM
3065 THEN
3066 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3067 THEN
3068 FND_MESSAGE.SET_NAME('INV','INV_INT_QTYCODE');
3069 FND_MSG_PUB.Add;
3070 END IF;
3071 return F;
3072 END IF;
3073 RETURN T;
3074 END Secondary_Quantity;
3075
3076 -- ---------------------------------------------------------------------
3077 -- ---------------------------------------------------------------------
3078 --INVCONV
3079
3080 PROCEDURE check_pending_transaction (
3081 p_transaction_type_id IN NUMBER,
3082 p_pending_tran_flag OUT NOCOPY NUMBER) AS
3083 /*************************************************************
3084 Created By : Nalin Kumar
3085 Date Created on: 05-05-05
3086 Purpose : This procedure will return NULL if there is no
3087 pending Transaction for the passed Transaction
3088 Type else it will pass '1', which indicates that
3089 there are pending transactions. Bug# 4348541
3090 Change History :
3091 -------------------------------------------------------------
3092 Who When What
3093 -------------------------------------------------------------
3094 (reverse chronological order - newest change first)
3095 ***************************************************************/
3096 CURSOR cur_chk_pnd_tran IS
3097 SELECT 1
3098 FROM DUAL
3099 WHERE EXISTS(
3100 SELECT 1
3101 FROM mtl_material_transactions_temp mmtt
3102 WHERE mmtt.transaction_type_id = p_transaction_type_id
3103 AND NVL(mmtt.transaction_status, 1) IN (1, 3));
3104 BEGIN
3105 OPEN cur_chk_pnd_tran;
3106 FETCH cur_chk_pnd_tran INTO p_pending_tran_flag;
3107 CLOSE cur_chk_pnd_tran;
3108 EXCEPTION WHEN OTHERS THEN
3109 NULL;
3110 END check_pending_transaction;
3111
3112 PROCEDURE check_location_required_setup(
3113 p_transaction_type_id IN NUMBER,
3114 p_required_flag OUT NOCOPY VARCHAR2) AS
3115 /*************************************************************
3116 Created By : Nalin Kumar
3117 Date Created on: 24-May-2005
3118 Purpose : This procedure will return 2 if the Location is
3119 Required for the given Transaction Type else it
3120 will return 1. Based on the returned value from
3121 this procedure the "Location' field can be made
3122 mandatory in different screens. Bug# 4348541
3123 Change History :
3124 -------------------------------------------------------------
3125 Who When What
3126 -------------------------------------------------------------
3127 (reverse chronological order - newest change first)
3128 ***************************************************************/
3129 CURSOR cur_chk_loc_req IS
3130 SELECT NVL(location_required_flag, 'N') location_required_flag
3131 FROM mtl_transaction_types
3132 WHERE transaction_type_id = NVL(p_transaction_type_id, -1)
3133 AND user_defined_flag = 'Y';
3134 rec_chk_loc_req cur_chk_loc_req%ROWTYPE;
3135 BEGIN
3136 p_required_flag := 'N'; /*Default as not required*/
3137 OPEN cur_chk_loc_req;
3138 FETCH cur_chk_loc_req INTO rec_chk_loc_req;
3139 IF cur_chk_loc_req%FOUND THEN
3140 p_required_flag := rec_chk_loc_req.location_required_flag;
3141 END IF;
3142 CLOSE cur_chk_loc_req;
3143 END check_location_required_setup;
3144 END INV_Validate;
[Home] [Help]