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