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