1 PACKAGE BODY MTL_DEFAULT_CATEGORY_SETS_PKG AS
2 /* $Header: INVDCSTB.pls 120.3 2010/08/30 03:17:50 kaizhao ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_DEF_FUNCTIONA_AREA_PKG';
5
6
7 FUNCTION val_inventory_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER;
8 FUNCTION val_purchasing_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER;
9 FUNCTION val_planning_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER;
10 FUNCTION val_costing_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER;
11 FUNCTION val_eng_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER;
12 FUNCTION val_order_entry_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER;
13 -- FUNCTION val_Product_Line_cat_set(P_Category_Set_Id NUMBER)RETURN NUMBER;
14 FUNCTION val_Product_reporting_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER;
15 FUNCTION val_Asset_Management_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER;
16 FUNCTION val_Service_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER;
17 FUNCTION val_Contracts_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER;
18 FUNCTION val_UCCNet_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER; -- added for bug : 3953009
19 FUNCTION val_UCCNet_GPC_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER;
20 FUNCTION val_process_cost_class_cat_set(P_Category_Set_Id NUMBER) return number;--bug: 9965888
21 -- FUNCTION Check_Mult_Assign_Flag RETURN VARCHAR2;
22
23 --**********************************************************************
24 -- Functional Validations before updating MTL_DEFAULT_CATEGORY_SETS
25 --**********************************************************************
26
27 PROCEDURE validate_all_cat_sets(P_Functional_area_id NUMBER,
28 P_Category_Set_Id NUMBER,
29 X_Msg_Name OUT NOCOPY VARCHAR2)
30 IS
31 rc Number;
32 BEGIN
33
34 IF ( P_functional_area_id = 1 ) Then
35 rc := val_inventory_cat_set(P_Category_Set_Id);
36 elsif ( P_functional_area_id = 2 ) Then
37 rc := val_purchasing_cat_set(P_Category_Set_Id);
38 elsif ( P_functional_area_id = 3 ) Then
39 rc := val_planning_cat_set(P_Category_Set_Id);
40 elsif ( P_functional_area_id = 5 ) Then
41 rc := val_costing_cat_set(P_Category_Set_Id);
42 elsif ( P_functional_area_id = 6 ) Then
43 rc := val_eng_cat_set(P_Category_Set_Id);
44 elsif ( P_functional_area_id = 7 ) Then
45 rc := val_order_entry_cat_set(P_Category_Set_Id);
46 -- elsif ( :deflt_cat_set.functional_area_id = 8 ) Then
47 -- rc := val_Product_Line_cat_set(P_Category_Set_Id);
48 ELSIF ( P_functional_area_id = 9 ) THEN
49 rc := val_Asset_Management_Cat_Set(P_Category_Set_Id);
50 ELSIF ( P_functional_area_id = 4 ) THEN
51 rc := val_Service_Cat_Set(P_Category_Set_Id);
52 ELSIF ( P_functional_area_id = 10 ) THEN
53 rc := val_Contracts_Cat_Set(P_Category_Set_Id);
54 ELSIF ( P_functional_area_id = 11 ) THEN
55 rc := val_Product_reporting_Cat_Set(P_Category_Set_Id);
56 ELSIF (P_functional_area_id = 12 ) THEN
57 rc :=val_UCCNet_Cat_Set(P_Category_Set_Id);--Bug:4082162
58 ELSIF (P_functional_area_id = 21 ) THEN
59 rc :=val_UCCNet_GPC_Cat_Set(P_Category_Set_Id);
60 ELSIF (P_functional_area_id = 19 ) THEN--Bug: 9965888
61 rc := val_process_cost_class_cat_set(P_Category_Set_Id);
62 END IF;
63
64 IF (rc = -2) THEN
65 if ( P_functional_area_id = 2) THEN
66 X_msg_Name := 'INV_CAT_PO_REFER';
67 elsif ( P_functional_area_id = 5) THEN
68 X_msg_Name := 'INV_CAT_CST_REFER';
69 elsif ( P_functional_area_id = 19) THEN
70 X_msg_Name := 'INV_CAT_OPM_REFER';
71 end if;
72 END IF;
73
74 IF ( rc = -1 ) THEN
75 /*
76 if ( P_functional_area_id = 8 ) THEN
77 FND_MESSAGE.SET_NAME('INV', 'INV_PL_DEF_CAT_SET_WARN');
78 if ( NOT FND_MESSAGE.Warn ) then
79 :deflt_cat_set.category_set_id := :parameter.PL_DEF_CAT_SET_ID;
80
81 select CATEGORY_SET_NAME, DESCRIPTION
82 into :deflt_cat_set.category_set_name, :deflt_cat_set.category_set_description
83 from MTL_CATEGORY_SETS_VL
84 where CATEGORY_SET_ID = :deflt_cat_set.category_set_id;
85
86 set_record_property(:system.cursor_record, 'DEFLT_CAT_SET', STATUS, QUERY_STATUS);
87 end if;
88 else */
89 if ( P_functional_area_id = 11 ) THEN
90 X_msg_Name := 'INV_PR_DEF_CAT_SET_WARN';
91 else
92 X_msg_Name := 'INV_ASSIGN_ITEM_TO_CS';
93 end if;
94 -- end if;
95
96 END IF;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 X_msg_Name := 'INV_UNHANDLED_EXCEPTION';
101
102 END validate_all_cat_sets;
103
104
105 /* Function validating category set name for Inventory Function */
106
107 FUNCTION val_inventory_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
108 IS
109 buffer VARCHAR2(1);
110 Begin
111
112 Select 'X'
113 Into buffer
114 FROM dual
115 WHERE EXISTS (
116 SELECT 'X'
117 From MTL_SYSTEM_ITEMS_B Item
118 Where Item.INVENTORY_ITEM_FLAG = 'Y'
119 And NOT EXISTS
120 ( Select 'X'
121 From MTL_ITEM_CATEGORIES Cat
122 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
123 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
124 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
125 )
126 );
127
128 return -1;
129
130 EXCEPTION
131
132 WHEN NO_DATA_FOUND THEN
133 return 0;
134
135 END val_inventory_cat_set;
136
137 /* Function validating category set name for Purchasing Function */
138
139 FUNCTION val_purchasing_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
140 IS
141 buffer VARCHAR2(1);
142 Old_Cat_value NUMBER;
143 Old_Structure_id NUMBER;
144 New_Structure_id NUMBER;
145 Po_Count NUMBER := 0;
146 Sql_Stmt Number := 0;
147 Begin
148
149 Sql_Stmt := 1;
150 Select STRUCTURE_ID
151 Into Old_Structure_id
152 From MTL_CATEGORY_SETS_B
153 WHERE CATEGORY_SET_ID = (select category_set_id
154 From MTL_DEFAULT_CATEGORY_SETS
155 Where FUNCTIONAL_AREA_ID = 2);
156
157 Sql_Stmt := 3;
158 Select STRUCTURE_ID
159 Into New_Structure_id
160 From MTL_CATEGORY_SETS_B
161 WHERE CATEGORY_SET_ID = P_Category_Set_Id;
162
163 If New_Structure_id <> Old_Structure_Id Then
164 Select count(*)
165 Into Po_Count
166 From Po_Line_Types_b
167 where category_id is NOT NULL
168 and rownum < 2;
169
170 If (Po_Count = 0) then
171 Select count(*)
172 Into Po_Count
173 From PO_REQEXPRESS_LINES_ALL
174 where category_id is NOT NULL
175 and rownum < 2;
176
177 If (Po_Count = 0) then
178 Select count(*)
179 Into Po_Count
180 From PO_AGENTS
181 where category_id is NOT NULL
182 and rownum < 2;
183
184 If (Po_Count = 0) then
185 Select count(*)
186 Into Po_Count
187 From PO_APPROVED_SUPPLIER_LIST
188 where category_id is NOT NULL
189 and rownum < 2;
190
191 If (Po_Count = 0) then
192 Select count(*)
193 Into Po_Count
194 From PO_ASL_ATTRIBUTES
195 where category_id is NOT NULL
196 and rownum < 2;
197
198 If (Po_Count = 0) then
199 Select count(*)
200 Into Po_Count
201 From PO_REQUISITION_LINES_ALL
202 where category_id is NOT NULL
203 and rownum < 2;
204
205 If (Po_Count = 0) then
206 Select count(*)
207 Into Po_Count
208 From PO_LINES_ALL
209 where category_id is NOT NULL
210 and rownum < 2;
211
212 If (Po_Count = 0) then
213 Select count(*)
214 Into Po_Count
215 From RCV_SHIPMENT_LINES
216 where category_id is NOT NULL
217 and rownum < 2;
218 END IF;
219 END IF;
220 END IF;
221 END IF;
222 END IF;
223 END IF;
224 END IF;
225 END IF;
226
227 If Po_count <> 0 then
228 return -2;
229 End if;
230
231 Sql_Stmt := 4;
232 Select DISTINCT 'X'
233 Into buffer
234 From MTL_SYSTEM_ITEMS_B Item
235 Where ( Item.PURCHASING_ITEM_FLAG = 'Y' OR
236 Item.INTERNAL_ORDER_FLAG = 'Y' )
237 And NOT EXISTS
238 ( Select 'X'
239 From MTL_ITEM_CATEGORIES Cat
240 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
241 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
242 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
243 );
244
245 return -1;
246
247 EXCEPTION
248
249 WHEN NO_DATA_FOUND THEN
250 If (Sql_Stmt = 4) then
251 return 0;
252 Else
253 return -1;
254 End if;
255 WHEN OTHERS THEN
256 return -1;
257 END val_purchasing_cat_set;
258
259
260 /* Function validating category set name for Planning Function */
261
262 FUNCTION val_planning_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
263 IS
264 buffer VARCHAR2(1);
265 Begin
266
267 Select 'X'
268 Into buffer
269 FROM dual
270 WHERE EXISTS (
271 SELECT 'X'
272 From MTL_SYSTEM_ITEMS_B Item
273 Where Item.MRP_PLANNING_CODE <> 6
274 And NOT EXISTS
275 ( Select 'X'
276 From MTL_ITEM_CATEGORIES Cat
277 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
278 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
279 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
280 )
281 );
282
283 return -1;
284
285 EXCEPTION
286
287 WHEN NO_DATA_FOUND THEN
288 return 0;
289
290 END val_planning_cat_set;
291
292
293 /* Function validating category set name for Costing Function */
294 FUNCTION val_costing_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
295 IS
296 buffer VARCHAR2(1);
297 Old_Cat_value NUMBER;
298 Old_Structure_id NUMBER;
299 New_Structure_id NUMBER;
300 co_Count NUMBER := 0;
301 Sql_Stmt NUMBER := 0;
302
303 l_dyn_sql VARCHAR2(500);
304 source_cursor INTEGER;
305 ignore INTEGER;
306
307 Begin
308
309 Sql_Stmt := 1;
310 Select STRUCTURE_ID
311 Into Old_Structure_id
312 From MTL_CATEGORY_SETS_B
313 WHERE CATEGORY_SET_ID = (select category_set_id
314 From MTL_DEFAULT_CATEGORY_SETS
315 Where FUNCTIONAL_AREA_ID = 5);
316
317 Sql_Stmt := 3;
318 Select STRUCTURE_ID
319 Into New_Structure_id
320 From MTL_CATEGORY_SETS_B
321 WHERE CATEGORY_SET_ID = P_Category_Set_Id;
322
323
324 If New_Structure_id <> Old_Structure_Id Then
325 /* Select count(*)
326 Into co_Count
327 From Po_Line_Types_b
328 where category_id is NOT NULL
329 and rownum < 2;
330
331 If (co_Count = 0) then *//* Commented out for bugfix:8679416 */
332 Select count(*)
333 Into co_Count
334 From CST_AP_VARIANCE_BATCHES
335 where category_id is NOT NULL
336 and rownum < 2;
337
338 If (co_Count = 0) then
339 Select count(*)
340 Into co_Count
341 From CST_COST_TYPE_HISTORY
342 where category_id is NOT NULL
343 and rownum < 2;
344
345 If (co_Count = 0) then
346 Select count(*)
347 Into co_Count
348 From CST_COST_UPDATES
349 where category_id is NOT NULL
350 and rownum < 2;
351
352 If (co_Count = 0) then
353 Select count(*)
354 Into co_Count
355 From CST_ITEM_OVERHEAD_DEFAULTS
356 where category_id is NOT NULL
357 and rownum < 2;
358
359 If (co_Count = 0) then
360 Select count(*)
361 Into co_Count
362 From CST_ITEM_OVERHEAD_DEFAULTS_EFC
363 where category_id is NOT NULL
364 and rownum < 2;
365
366 If (co_Count = 0) then
367
368 IF INV_ITEM_UTIL.Object_Exists(p_object_type => 'SYNONYM'
369 ,p_object_name => 'CST_MATERIAL_OVHD_RULES') ='Y'
370 THEN
371 source_cursor := dbms_sql.open_cursor;
372 l_dyn_sql := ' Select count(*) '||
373 ' From CST_MATERIAL_OVHD_RULES '||
374 ' where category_id is NOT NULL '||
375 ' and rownum < 2';
376 DBMS_SQL.PARSE(source_cursor,l_dyn_sql,1);
377 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, co_Count);
378 ignore := DBMS_SQL.EXECUTE(source_cursor);
379 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
380 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, co_Count);
381 END IF;
382 DBMS_SQL.CLOSE_CURSOR(source_cursor);
383 END IF;
384 If (co_Count = 0) then
385 Select count(*)
386 Into co_Count
387 From CST_SC_ROLLUP_HISTORY
388 where category_id is NOT NULL
389 and rownum < 2;
390 END IF;
391 END IF;
392 END IF;
393 END IF;
394 END IF;
395 END IF;
396 -- END IF;/* commented out for bugfix:8679416 */
397 END IF;
398
399 If co_Count <> 0 then
400 return -2;
401 End if;
402
403 Sql_Stmt := 4;
404 Select DISTINCT 'X'
405 Into buffer
406 From MTL_SYSTEM_ITEMS_B Item
407 Where Item.COSTING_ENABLED_FLAG = 'Y'
408 And NOT EXISTS
409 ( Select 'X'
410 From MTL_ITEM_CATEGORIES Cat
411 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
412 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
413 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
414 );
415
416 return -1;
417
418 EXCEPTION
419
420 WHEN NO_DATA_FOUND THEN
421 If (Sql_Stmt = 4) then
422 return 0;
423 Else
424 return -1;
425 End if;
426 WHEN OTHERS THEN
427 return -1;
428
429 END val_costing_cat_set;
430
431 /* Function validating category set name for Engineering Function */
432
433 FUNCTION val_eng_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
434 IS
435 buffer VARCHAR2(1);
436 Begin
437
438 Select 'X'
439 Into buffer
440 FROM dual
441 WHERE EXISTS (
442 SELECT 'X'
443 From MTL_SYSTEM_ITEMS_B Item
444 Where Item.ENG_ITEM_FLAG = 'Y'
445 And NOT EXISTS
446 ( Select 'X'
447 From MTL_ITEM_CATEGORIES Cat
448 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
449 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
450 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
451 )
452 );
453
454 return -1;
455
456 EXCEPTION
457
458 WHEN NO_DATA_FOUND THEN
459 return 0;
460
461 END val_eng_cat_set;
462
463
464 /* Function validating category set name for Order Entry Function */
465
466 FUNCTION val_order_entry_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
467 IS
468 buffer VARCHAR2(1);
469 Begin
470
471 Select 'X'
472 Into buffer
473 FROM dual
474 WHERE EXISTS (
475 SELECT 'X'
476 From MTL_SYSTEM_ITEMS_B Item
477 Where Item.CUSTOMER_ORDER_FLAG = 'Y'
478 And NOT EXISTS
479 ( Select 'X'
480 From MTL_ITEM_CATEGORIES Cat
481 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
482 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
483 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
484 )
485 );
486
487 return -1;
488
489 EXCEPTION
490
491 WHEN NO_DATA_FOUND THEN
492 return 0;
493
494 END val_order_entry_cat_set;
495
496 /* function validating category set name for Product_Line function
497
498 FUNCTION val_Product_Line_cat_set(P_Category_Set_Id NUMBER) RETURN NUMBER
499 IS
500 BEGIN
501
502 if ( :parameter.PL_DEF_CAT_SET_ID != P_Category_Set_Id ) then
503 return -1;
504 else
505 return 0;
506 end if;
507
508 RETURN NULL;
509
510 END val_Product_Line_cat_set;*/
511
512
513 /* FUNCTION validating category set name for Asset Management function */
514
515 FUNCTION val_Asset_Management_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER
516 IS
517 l_count NUMBER;
518 BEGIN
519
520 SELECT DECODE( COUNT(*), 0, 0, -1 )
521 INTO l_count
522 FROM MTL_SYSTEM_ITEMS_B Item
523 WHERE
524 Item.EAM_ITEM_TYPE IS NOT NULL
525 AND NOT EXISTS
526 ( SELECT 'X'
527 FROM MTL_ITEM_CATEGORIES Cat
528 WHERE
529 Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
530 AND Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
531 AND Cat.CATEGORY_SET_ID = P_Category_Set_Id
532 );
533
534 RETURN (l_count);
535
536 END val_Asset_Management_Cat_Set;
537
538 /* Function validating category set name for Service Function */
539
540 FUNCTION val_Service_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER
541 IS
542 l_count NUMBER;
543 BEGIN
544
545 SELECT DECODE( COUNT(*), 0, 0, -1 )
546 INTO l_count
547 FROM MTL_SYSTEM_ITEMS_B Item
548 WHERE
549 Item.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY')
550 AND NOT EXISTS
551 ( SELECT 'X'
552 FROM MTL_ITEM_CATEGORIES Cat
553 WHERE
554 Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
555 AND Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
556 AND Cat.CATEGORY_SET_ID = P_Category_Set_Id
557 );
558
559 RETURN (l_count);
560
561 END val_Service_Cat_Set;
562
563 /* Function validating category set name for Contracts Function */
564 FUNCTION val_Contracts_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER
565 IS
566 l_count NUMBER;
567 BEGIN
568
569 SELECT DECODE( COUNT(*), 0, 0, -1 )
570 INTO l_count
571 FROM MTL_SYSTEM_ITEMS_B Item
572 WHERE
573 Item.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY', 'SUBSCRIPTION', 'USAGE')
574 AND NOT EXISTS
575 ( SELECT 'X'
576 FROM MTL_ITEM_CATEGORIES Cat
577 WHERE
578 Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
579 AND Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
580 AND Cat.CATEGORY_SET_ID = P_Category_Set_Id
581 );
582
583 RETURN (l_count);
584
585 END val_Contracts_Cat_Set;
586
587 /* Private Function validating category set name for Product Reporting/DBI Function */
588
589 FUNCTION val_Product_reporting_Cat_Set(P_Category_Set_Id NUMBER) RETURN NUMBER
590 IS
591 buffer VARCHAR2(1);
592 Begin
593
594 Select 'X'
595 Into buffer
596 FROM dual
597 WHERE EXISTS (
598 SELECT 'X'
599 From MTL_SYSTEM_ITEMS_B Item
600 Where ( Item.CUSTOMER_ORDER_FLAG = 'Y' OR
601 Item.INTERNAL_ORDER_FLAG = 'Y' )
602 And NOT EXISTS
603 ( Select 'X'
604 From MTL_ITEM_CATEGORIES Cat
605 Where Cat.INVENTORY_ITEM_ID = Item.INVENTORY_ITEM_ID
606 And Cat.ORGANIZATION_ID = Item.ORGANIZATION_ID
607 And Cat.CATEGORY_SET_ID = P_Category_Set_Id
608 )
609 );
610 return -1;
611 EXCEPTION
612
613 WHEN NO_DATA_FOUND THEN
614 return 0;
615
616 END val_Product_reporting_Cat_Set;
617
618
619 /* Private Function validating Category Set for UCCNet */
620 --Bug:4082162 User can change the default category set (catalog) to new default catalog
621 -- ONLY if all the items belongs to current category set are already assigned to
622 -- new category set(Catalog).
623 FUNCTION val_UCCNet_Cat_Set(P_Category_Set_Id NUMBER)
624 RETURN NUMBER
625 IS
626 buffer VARCHAR2(1);
627 Begin
628
629 Select 'X'
630 Into buffer
631 From MTL_ITEM_CATEGORIES Cat,
632 MTL_DEFAULT_CATEGORY_SETS DefCat
633 Where DefCat.FUNCTIONAL_AREA_ID = 12
634 And DefCat.CATEGORY_SET_ID = Cat.CATEGORY_SET_ID
635 And NOT EXISTS
636 ( Select 'X'
637 From MTL_ITEM_CATEGORIES Cat1
638 Where Cat1.INVENTORY_ITEM_ID = Cat.INVENTORY_ITEM_ID
639 And Cat1.ORGANIZATION_ID = Cat.ORGANIZATION_ID
640 And Cat1.CATEGORY_SET_ID = P_Category_Set_Id
641 )
642 And rownum=1;
643 return -1;
644 EXCEPTION
645
646 WHEN NO_DATA_FOUND THEN
647 return 0;
648 End val_UCCNet_Cat_Set;
649
650 /* Private Function validating Category Set for UCCNet GPC Catalog */
651 --User can change the default category set (catalog) to new default catalog
652 -- ONLY if all the items belongs to current category set are already assigned to
653 -- new category set(Catalog).
654 FUNCTION val_UCCNet_GPC_Cat_Set(P_Category_Set_Id NUMBER)
655 RETURN NUMBER
656 IS
657 buffer VARCHAR2(1);
658 Begin
659
660 Select 'X'
661 Into buffer
662 From MTL_ITEM_CATEGORIES Cat,
663 MTL_DEFAULT_CATEGORY_SETS DefCat
664 Where DefCat.FUNCTIONAL_AREA_ID = 21
665 And DefCat.CATEGORY_SET_ID = Cat.CATEGORY_SET_ID
666 And NOT EXISTS
667 ( Select 'X'
668 From MTL_ITEM_CATEGORIES Cat1
669 Where Cat1.INVENTORY_ITEM_ID = Cat.INVENTORY_ITEM_ID
670 And Cat1.ORGANIZATION_ID = Cat.ORGANIZATION_ID
671 And Cat1.CATEGORY_SET_ID = P_Category_Set_Id
672 )
673 And rownum=1;
674 return -1;
675 EXCEPTION
676
677 WHEN NO_DATA_FOUND THEN
678 return 0;
679 End val_UCCNet_GPC_Cat_Set;
680
681 FUNCTION val_process_cost_class_cat_set(P_Category_Set_Id NUMBER)
682 RETURN NUMBER
683 IS
684 buffer VARCHAR2 (1);
685 old_cat_value NUMBER;
686 old_structure_id NUMBER;
687 new_structure_id NUMBER;
688 opm_count NUMBER := 0;
689 sql_stmt NUMBER := 0;
690 BEGIN
691 sql_stmt := 1;
692
693 SELECT structure_id
694 INTO old_structure_id
695 FROM mtl_category_sets_b
696 WHERE category_set_id = (SELECT category_set_id
697 FROM mtl_default_category_sets
698 WHERE functional_area_id = 19);
699
700 sql_stmt := 3;
701
702 SELECT structure_id
703 INTO new_structure_id
704 FROM mtl_category_sets_b
705 WHERE category_set_id = P_Category_Set_Id;
706
707 IF new_structure_id <> old_structure_id
708 THEN
709 SELECT COUNT (*)
710 INTO opm_count
711 FROM cm_cmpt_mtl
712 WHERE cost_category_id IS NOT NULL AND ROWNUM < 2;
713
714 IF (opm_count = 0)
715 THEN
716 SELECT COUNT (*)
717 INTO opm_count
718 FROM cm_whse_eff
719 WHERE cost_category_id IS NOT NULL AND ROWNUM < 2;
720
721 IF (opm_count = 0)
722 THEN
723 SELECT COUNT (*)
724 INTO opm_count
725 FROM cm_whse_src
726 WHERE cost_category_id IS NOT NULL AND ROWNUM < 2;
727
728 IF (opm_count = 0)
729 THEN
730 SELECT COUNT (*)
731 INTO opm_count
732 FROM gmf_burden_percentages
733 WHERE cost_category_id IS NOT NULL AND ROWNUM < 2;
734
735 IF (opm_count = 0)
736 THEN
737 SELECT COUNT (*)
738 INTO opm_count
739 FROM gmf_lot_costed_items
740 WHERE cost_category_id IS NOT NULL AND ROWNUM < 2;
741 END IF;
742 END IF;
743 END IF;
744 END IF;
745 END IF;
746
747 IF opm_count <> 0
748 THEN
749 RETURN -2;
750 END IF;
751
752 SELECT 'X'
753 INTO buffer
754 FROM DUAL
755 WHERE EXISTS
756 (SELECT 'X'
757 FROM mtl_system_items_b item
758 WHERE item.process_costing_enabled_flag = 'Y'
759 AND item.organization_id IN (SELECT ood.organization_id
760 FROM org_organization_definitions ood
761 WHERE ood.inventory_enabled_flag = 'Y')
762 AND NOT EXISTS
763 (SELECT 'X'
764 FROM mtl_item_categories cat
765 WHERE cat.inventory_item_id = item.inventory_item_id
766 AND cat.organization_id = item.organization_id
767 AND cat.category_set_id = P_Category_Set_Id));
768
769 IF buffer = 'X'
770 THEN
771 RETURN -1;
772 END IF;
773
774 RETURN 0;
775 EXCEPTION
776 WHEN NO_DATA_FOUND
777 THEN
778 RETURN -1;
779 WHEN OTHERS
780 THEN
781 RETURN -1;
782 END val_process_cost_class_cat_set;
783
784 END MTL_DEFAULT_CATEGORY_SETS_PKG;
[Home] [Help]