1 PACKAGE BODY EGO_UPGRADE_USER_ATTR_VAL_PUB AS
2 /* $Header: EGOUPGAB.pls 120.0 2005/05/26 21:35:12 appldev noship $ */
3
4
5
6 ------------------------------
7 -- Private Global Variables --
8 ------------------------------
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_UPGRADE_USER_ATTR_VAL_PUB';
11
12
13 ----------------
14 -- Procedures --
15 ----------------
16
17 ----------------------------------------------------------------------
18
19 PROCEDURE Upgrade_Cat_User_Attrs_Data
20 (
21 p_api_version IN NUMBER DEFAULT 1.0
22 ,p_functional_area_id IN NUMBER
23 ,p_attr_group_name IN VARCHAR2 DEFAULT NULL
24 ,x_return_status OUT NOCOPY VARCHAR2
25 ,x_errorcode OUT NOCOPY NUMBER
26 ,x_msg_count OUT NOCOPY NUMBER
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ) IS
29
30 l_proc_name CONSTANT VARCHAR2(30) := 'Upgrade_Cat_User_Attrs_Data';
31 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
32 l_data_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
33 l_attr_group_id NUMBER;
34 l_category_set_id NUMBER;
35 l_return_status VARCHAR2(1);
36
37 ---------------------------------------------------
38 -- For finding the default category set for the --
39 -- functional area --
40 ---------------------------------------------------
41 CURSOR category_set_cursor
42 IS
43 SELECT CATEGORY_SET_ID
44 FROM MTL_DEFAULT_CATEGORY_SETS
45 WHERE FUNCTIONAL_AREA_ID = p_functional_area_id;
46
47 -----------------------------------------------------
48 -- For finding all categories in the category set --
49 -- that don't already have attribute group entries --
50 -----------------------------------------------------
51 CURSOR category_cursor (p_cat_set_id IN NUMBER
52 ,p_attr_group_id IN NUMBER)
53 IS
54 SELECT CAT.CATEGORY_ID
55 FROM MTL_CATEGORY_SET_VALID_CATS CAT, MTL_CATEGORY_SETS_B CATSET
56 WHERE CAT.CATEGORY_SET_ID = CATSET.CATEGORY_SET_ID
57 AND CATSET.CATEGORY_SET_ID = p_cat_set_id
58 AND NOT EXISTS
59 (SELECT CATEGORY_ID
60 FROM EGO_PRODUCT_CAT_SET_EXT
61 WHERE CATEGORY_SET_ID = CATSET.CATEGORY_SET_ID
62 AND CATEGORY_ID = CAT.CATEGORY_ID
63 AND ATTR_GROUP_ID = p_attr_group_id);
64
65 -------------------------------------------------------
66 -- For finding ids for all category-level attribute --
67 -- groups. This cursor contains hard-coded --
68 -- attribute group names as there is currently no --
69 -- mechanism to determine whether a particular --
70 -- attribute group is at the category level or --
71 -- catalog level. In addition, there is currently no --
72 -- means to determine functional area to attribute --
73 -- group type association --
74 -------------------------------------------------------
75 CURSOR cat_level_attr_gp_cursor
76 IS
77 SELECT ATTRS.ATTR_GROUP_ID, ATTRS.ATTR_GROUP_NAME
78 FROM EGO_ATTR_GROUPS_V ATTRS
79 WHERE ATTRS.ATTR_GROUP_NAME = 'SalesAndMarketing'
80 AND p_functional_area_id = 11;
81
82 ----------------------------------------------
83 -- For finding the id of an attribute group --
84 ----------------------------------------------
85 CURSOR attr_gp_id_cursor (attribute_group_name IN VARCHAR2)
86 IS
87 SELECT ATTRS.ATTR_GROUP_ID
88 FROM EGO_ATTR_GROUPS_V ATTRS
89 WHERE ATTRS.ATTR_GROUP_NAME = attribute_group_name;
90
91 BEGIN
92
93
94 ---------------------------------------------------
95 -- Get default category set for functional area --
96 ---------------------------------------------------
97 OPEN category_set_cursor;
98 FETCH category_set_cursor INTO l_category_set_id;
99 CLOSE category_set_cursor;
100
101 ------------------------------------------------------------------
102 -- Initialize the PK column array and the attribute data array --
103 -- before we start iterating thru the categories in the category--
104 -- set --
105 ------------------------------------------------------------------
106 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
107 EGO_COL_NAME_VALUE_PAIR_OBJ('CATEGORY_SET_ID',
108 l_category_set_id));
109
110 l_data_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
111 EGO_COL_NAME_VALUE_PAIR_OBJ('CATEGORY_ID', null));
112
113 ------------------------------------------------------------------
114 -- If no specific attribute group has come in, then process all --
115 -- attribute groups attached to the category set --
116 ------------------------------------------------------------------
117 IF (p_attr_group_name IS NULL) THEN
118
119 FOR attr_gp_rec IN cat_level_attr_gp_cursor
120 LOOP
121 ------------------------------------------------------------------
122 -- We loop through the categories in the category set, and --
123 -- build appropriate objects to call --
124 -- EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row --
125 ------------------------------------------------------------------
126 FOR cat_rec IN category_cursor(l_category_set_id,
127 attr_gp_rec.attr_group_id)
128 LOOP
129 ------------------------------------------------------------------
130 -- Now we update the Primary Key value, and clear out the Attr --
131 -- Data table --
132 ------------------------------------------------------------------
133 l_data_column_name_value_pairs(1).VALUE := cat_rec.CATEGORY_ID;
134
135 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row(
136 p_api_version => 1.0
137 ,p_object_name => 'EGO_CATEGORY_SET'
138 ,p_application_id => 431
139 ,p_attr_group_type => 'EGO_PRODUCT_CATEGORY_SET'
140 ,p_attr_group_name => attr_gp_rec.attr_group_name
141 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
142 ,p_class_code_name_value_pairs => NULL
143 ,p_data_level_name_value_pairs => l_data_column_name_value_pairs
144 ,p_attr_name_value_pairs => null
145 ,p_use_def_vals_on_insert => FND_API.G_TRUE
146 ,x_return_status => x_return_status
147 ,x_errorcode => x_errorcode
148 ,x_msg_count => x_msg_count
149 ,x_msg_data => x_msg_data
150 );
151
152 END LOOP; -- category loop
153 END LOOP; -- attribute gp loop
154
155 ELSE
156
157 ------------------------------------------------------------------
158 -- If a specific attribute group has come in, then process --
159 -- only that attribute group --
160 ------------------------------------------------------------------
161
162 OPEN attr_gp_id_cursor(p_attr_group_name);
163 FETCH attr_gp_id_cursor INTO l_attr_group_id;
164 IF (attr_gp_id_cursor%FOUND) THEN
165
166 ------------------------------------------------------------------
167 -- We loop through the categories in the category set, and --
168 -- build appropriate objects to call --
169 -- EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row --
170 ------------------------------------------------------------------
171
172 FOR cat_rec IN category_cursor(l_category_set_id,
173 l_attr_group_id)
174 LOOP
175 ------------------------------------------
176 -- Now we update the Primary Key value --
177 ------------------------------------------
178 l_data_column_name_value_pairs(1).VALUE := cat_rec.CATEGORY_ID;
179
180 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row(
181 p_api_version => 1.0
182 ,p_object_name => 'EGO_CATEGORY_SET'
183 ,p_application_id => 431
184 ,p_attr_group_type => 'EGO_PRODUCT_CATEGORY_SET'
185 ,p_attr_group_name => p_attr_group_name
186 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
187 ,p_class_code_name_value_pairs => NULL
188 ,p_data_level_name_value_pairs => l_data_column_name_value_pairs
189 ,p_attr_name_value_pairs => null
190 ,p_use_def_vals_on_insert => FND_API.G_TRUE
191 ,x_return_status => x_return_status
192 ,x_errorcode => x_errorcode
193 ,x_msg_count => x_msg_count
194 ,x_msg_data => x_msg_data
195 );
196
197 END LOOP; -- Loop thru all categories in the category set
198 END IF; -- IF attribute group id was found
199 END IF; -- If attribute_group_name is NULL
200
201
202 COMMIT WORK;
203
204 EXCEPTION
205
206 WHEN OTHERS THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_proc_name||' '||SQLERRM;
209
210 END Upgrade_Cat_User_Attrs_Data;
211
212 ----------------------------------------------------------------------
213
214 END EGO_UPGRADE_USER_ATTR_VAL_PUB;
215
[Home] [Help]