1 PACKAGE BODY INVUPCTF as
2 /* $Header: INVUPCTB.pls 115.3 1999/11/24 00:04:32 pkm ship $*/
3
4 PROCEDURE UPDATE_CATALOG_STATUS_FLAG(
5 current_catalog_id IN NUMBER,
6 current_element_name IN VARCHAR2
7 )
8 IS
9
10 el_value VARCHAR2(30);
11
12 CURSOR get_iii_cursor IS
13 select msi.inventory_item_id III
14 from mtl_system_items_B msi
15 where msi.item_catalog_group_id = current_catalog_id
16 and msi.catalog_status_flag <> 'N';
17
18 BEGIN
19
20 FOR get_iii_cursor_row in get_iii_cursor LOOP
21
22 el_value := NULL;
23
24 SELECT ELEMENT_VALUE INTO el_value
25 FROM MTL_DESCR_ELEMENT_VALUES MDEV
26 WHERE MDEV.INVENTORY_ITEM_ID = get_iii_cursor_row.III
27 AND MDEV.ELEMENT_NAME = current_element_name;
28
29 IF el_value IS NULL THEN
30 UPDATE MTL_SYSTEM_ITEMS_B MSI
31 SET MSI.CATALOG_STATUS_FLAG = 'N'
32 WHERE MSI.INVENTORY_ITEM_ID = get_iii_cursor_row.III
33 AND MSI.ITEM_CATALOG_GROUP_ID = current_catalog_id;
34 END IF;
35
36 END LOOP;
37
38 END UPDATE_CATALOG_STATUS_FLAG;
39
40
41 PROCEDURE UPDATE_CATSTAT_FLAG_NEW_DE(
42 current_catalog_id IN NUMBER)
43 IS
44 BEGIN
45 UPDATE MTL_SYSTEM_ITEMS_B MSI
46 SET MSI.CATALOG_STATUS_FLAG = 'N'
47 WHERE MSI.ITEM_CATALOG_GROUP_ID = current_catalog_id
48 AND MSI.CATALOG_STATUS_FLAG <> 'N';
49
50 END UPDATE_CATSTAT_FLAG_NEW_DE;
51
52
53 FUNCTION Check_Reqd_Desc_Elems
54 (
55 current_catalog_id IN NUMBER
56 , current_inv_item_id IN NUMBER
57 )
58 RETURN BOOLEAN
59 IS
60 t_count NUMBER;
61 BEGIN
62
63 SELECT count(*) INTO t_count
64 FROM DUAL
65 WHERE EXISTS
66 ( SELECT null
67 FROM MTL_DESCRIPTIVE_ELEMENTS E
68 , MTL_DESCR_ELEMENT_VALUES V
69 WHERE E.REQUIRED_ELEMENT_FLAG = 'Y'
70 AND E.ITEM_CATALOG_GROUP_ID = current_catalog_id
71 AND V.INVENTORY_ITEM_ID = current_inv_item_id
72 AND V.ELEMENT_NAME = E.ELEMENT_NAME
73 AND V.ELEMENT_VALUE IS NULL
74 );
75
76 RETURN (t_count = 0);
77
78 END Check_Reqd_Desc_Elems;
79
80
81 END INVUPCTF;
[Home] [Help]