1 PACKAGE BODY CS_CF_UPG_BUGFIX1_PKG as
2 /* $Header: cscfupg2b.pls 120.0 2005/06/01 12:05:33 appldev noship $ */
3
4
5 PROCEDURE Main IS
6
7 l_logfilename VARCHAR2(2000) := '';
8
9 BEGIN
10
11 -- generate a suffix to the filename
12 select to_char(sysdate, 'mm-dd-yy') || to_char(sysdate, 'hh24:mi:ss')
13 into l_logfilename
14 from dual;
15
16 CS_CF_UPG_UTL_PKG.setup_log('IBUCFUPG-' || l_logfilename);
17
18 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_BUGFIX1_PKG.Main', 'Starting bugfix1 procedure');
19 Fix_Regions_Bugfix1('R');
20 Fix_Regions_Bugfix1('A');
21 Fix_Regions_Bugfix1('GC');
22
23 CS_CF_UPG_UTL_PKG.wrapup('SUCCESS');
24
25 EXCEPTION
26 WHEN OTHERS THEN
27 CS_CF_UPG_UTL_PKG.wrapup('ERROR');
28 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_BUGFIX1_PKG.Main','Exception raised in Main');
29 RAISE;
30 End Main;
31
32 PROCEDURE Fix_Regions_Bugfix1(p_contextType IN VARCHAR2) IS
33
34 l_region_code AK_REGIONS.region_code%TYPE;
35 l_newRegionCode AK_REGIONS.region_code%TYPE;
36 l_suffix VARCHAR2(2000);
37 l_node_display_flag AK_REGION_ITEMS.node_display_flag%TYPE;
38 l_required_flag AK_REGION_ITEMS.required_flag%TYPE;
39 l_count NUMBER := 0;
40
41 CURSOR get_regions (p_region_code IN VARCHAR2, p_suffix IN VARCHAR2) IS
42 SELECT region_code
43 FROM ak_regions
44 WHERE region_code like p_region_code || p_suffix || '%';
45
46
47 CURSOR region_exist (p_region_code IN VARCHAR2) IS
48 SELECT count(*)
49 FROM ak_regions where region_code = p_region_code;
50
51 CURSOR get_display_attributes(p_region_code IN VARCHAR2, p_region_application_id IN VARCHAR2,
52 p_attribute_code IN VARCHAR2, p_attribute_application_id IN VARCHAR2) IS
53 SELECT node_display_flag, required_flag
54 FROM ak_region_items
55 WHERE region_code = p_region_code
56 AND region_application_id = p_region_application_id
57 AND attribute_code = p_attribute_code
58 AND attribute_application_id = p_attribute_application_id;
59
60
61
62 BEGIN
63
64 -- Find out if there are any regions cloned for IBU_CF_SR_30_G
65 -- for corresponding level
66
67 OPEN get_regions('IBU_CF_SR_30_',p_contextType);
68 FETCH get_regions INTO l_region_code;
69 WHILE get_regions%FOUND LOOP
70 -- get the resp id/appl from the suffix
71 IF (p_contextType <> 'GC') THEN
72 l_suffix := substr(l_region_code, 15);
73 ELSE
74 l_suffix := '';
75 END IF;
76 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_BUGFIX1_PKG.Fix_Regions_Bugfix1', 'Fixing for IBU_CF_SR_30_X suffix:' || p_contextType || l_suffix);
77
78 l_newRegionCode := 'IBU_CF_SR_35_' || p_contextType || l_suffix;
79
80 OPEN region_exist('IBU_CF_SR_35_' || p_contextType || l_suffix);
81 FETCH region_exist INTO l_count;
82 CLOSE region_exist;
83
84 IF (l_count = 0) THEN
85 CS_CF_UPG_UTL_PKG.Clone_Region(l_region_code,
86 672,
87 l_newRegionCode,
88 672, TRUE);
89
90 OPEN get_display_attributes(l_region_code, 672,
91 'IBU_CF_SR_ALL_PRODUCT_RG', 672);
92 FETCH get_display_attributes INTO l_node_display_flag, l_required_flag;
93 CLOSE get_display_attributes;
94 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newRegionCode,
95 'IBU_CF_SR_ALL_PRODUCT_RG',
96 l_node_display_flag,
97 l_required_flag,
98 'IBU_CF_SR_ALL_PRODUCT');
99 OPEN get_display_attributes(l_region_code, 672,
100 'IBU_CF_SR_REG_PRODUCT_RG', 672);
101 FETCH get_display_attributes INTO l_node_display_flag, l_required_flag;
102 CLOSE get_display_attributes;
103 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newRegionCode,
104 'IBU_CF_SR_REG_PRODUCT_RG',
105 l_node_display_flag,
106 l_required_flag,
107 'IBU_CF_SR_REG_PRODUCT');
108 -- For Create/Update template, set the nested_region_code to point to this
109 -- newly cloned region.
110
111 OPEN region_exist('IBU_CF_SR_310_' || p_contextType || l_suffix);
112 FETCH region_exist INTO l_count;
113 CLOSE region_exist;
114
115 IF (l_count > 0) THEN
116 OPEN get_display_attributes('IBU_CF_SR_310_' || p_contextType||l_suffix, 672,
117 'IBU_CF_SR_IDENTIFY_PRODUCT_RG', 672);
118 FETCH get_display_attributes INTO l_node_display_flag, l_required_flag;
119 CLOSE get_display_attributes;
120
121 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_310_' || p_contextType||l_suffix,
122 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
123 l_node_display_flag,
124 l_required_flag,
125 'IBU_CF_SR_35_' || p_contextType||l_suffix);
126 END IF;
127
128 OPEN region_exist('IBU_CF_SR_320_' || p_contextType || l_suffix);
129 FETCH region_exist INTO l_count;
130 CLOSE region_exist;
131
132 IF (l_count > 0) THEN
133 OPEN get_display_attributes('IBU_CF_SR_320_' || p_contextType||l_suffix, 672,
134 'IBU_CF_SR_IDENTIFY_PRODUCT_RG', 672);
135 FETCH get_display_attributes INTO l_node_display_flag, l_required_flag;
136 CLOSE get_display_attributes;
137
138 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_320_'|| p_contextType||l_suffix,
139 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
140 l_node_display_flag,
141 l_required_flag,
142 'IBU_CF_SR_35_'|| p_contextType||l_suffix);
143
144 END IF;
145 END IF; -- end if IBU_CF_SR_35_XX doesn't exist
146
147 OPEN region_exist('IBU_CF_SR_420_' || p_contextType|| l_suffix);
148 FETCH region_exist INTO l_count;
149 CLOSE region_exist;
150
151 IF (l_count > 0) THEN
152 OPEN get_display_attributes('IBU_CF_SR_420_'|| p_contextType||l_suffix, 672,
153 'IBU_CF_SR_ALL_PRODUCT_RG', 672);
154 FETCH get_display_attributes INTO l_node_display_flag, l_required_flag;
155 CLOSE get_display_attributes;
156 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_420_' || p_contextType|| l_suffix,
157 'IBU_CF_SR_ALL_PRODUCT_RG',
158 l_node_display_flag,
159 l_required_flag,
160 'IBU_CF_SR_ALL_PRODUCT');
161 OPEN get_display_attributes('IBU_CF_SR_420_'|| p_contextType||l_suffix, 672,
162 'IBU_CF_SR_REG_PRODUCT_RG', 672);
163 FETCH get_display_attributes INTO l_node_display_flag, l_required_flag;
164 CLOSE get_display_attributes;
165 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_420_' || p_contextType|| l_suffix,
166 'IBU_CF_SR_REG_PRODUCT_RG',
167 l_node_display_flag,
168 l_required_flag,
169 'IBU_CF_SR_REG_PRODUCT');
170 END IF; -- end if l_count > 0
171 FETCH get_regions INTO l_region_code;
172 END LOOP;
173 CLOSE get_regions;
174
175 -- Find out if there are any regions cloned for IBU_CF_SR_130_G (Update SR Overview)
176 -- for corresponding level. We need to set the address region to IBU_CF_SR_25_G
177
178 OPEN get_regions('IBU_CF_SR_130_',p_contextType);
179 FETCH get_regions INTO l_region_code;
180 WHILE get_regions%FOUND LOOP
181 -- get the resp id/appl from the suffix
182 IF (p_contextType <> 'GC') THEN
183 l_suffix := substr(l_region_code, 16);
184 ELSE
185 l_suffix := '';
186 END IF;
187 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_BUGFIX1_PKG.Fix_Regions_Bugfix1', 'Fixing for IBU_CF_SR_130_X suffix:' || p_contextType || l_suffix);
188
189 OPEN region_exist('IBU_CF_SR_25_' || p_contextType || l_suffix);
190 FETCH region_exist INTO l_count;
191 CLOSE region_exist;
192
193 IF (l_count = 0) THEN
194 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_130_' || p_contextType || l_suffix,
195 'IBU_CF_SR_ADDRESS_RG',
196 'N', 'N', 'IBU_CF_SR_25_G');
197 ELSE
198 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_130_' || p_contextType || l_suffix,
199 'IBU_CF_SR_ADDRESS_RG',
200 'N', 'N', 'IBU_CF_SR_25_'||p_contextType||l_suffix);
201 END IF;
202 FETCH get_regions INTO l_region_code;
203 END LOOP;
204 CLOSE get_regions;
205
206 -- Find out if there are any regions cloned for IBU_CF_SR_210_G (Update SR Contacts)
207 -- for corresponding level. We need to set the address region to IBU_CF_SR_25_G
208
209 OPEN get_regions('IBU_CF_SR_210_',p_contextType);
210 FETCH get_regions INTO l_region_code;
211 WHILE get_regions%FOUND LOOP
212 -- get the resp id/appl from the suffix
213 IF (p_contextType <> 'GC') THEN
214 l_suffix := substr(l_region_code, 16);
215 ELSE
216 l_suffix := '';
217 END IF;
218 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_BUGFIX1_PKG.Fix_Regions_Bugfix1', 'Fixing for IBU_CF_SR_210_X suffix:' || p_contextType || l_suffix);
219
220 OPEN region_exist('IBU_CF_SR_25_' || p_contextType || l_suffix);
221 FETCH region_exist INTO l_count;
222 CLOSE region_exist;
223
224 IF (l_count = 0) THEN
225 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_210_' || p_contextType || l_suffix,
226 'IBU_CF_SR_ADDRESS_RG',
227 'Y', 'N', 'IBU_CF_SR_25_G');
228 ELSE
229 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_210_' || p_contextType || l_suffix,
230 'IBU_CF_SR_ADDRESS_RG',
231 'Y', 'N', 'IBU_CF_SR_25_' || p_contextType || l_suffix);
232 END IF;
233 FETCH get_regions INTO l_region_code;
234 END LOOP;
235 CLOSE get_regions;
236
237 -- Find out if there are any regions cloned for IBU_CF_SR_310_G (Create Template)
238 -- for corresponding level. We need to set the primary contact region to IBU_CF_SR_15_G
239
240 OPEN get_regions('IBU_CF_SR_310_',p_contextType);
241 FETCH get_regions INTO l_region_code;
242 WHILE get_regions%FOUND LOOP
243 -- get the resp id/appl from the suffix
244 IF (p_contextType <> 'GC') THEN
245 l_suffix := substr(l_region_code, 16);
246 ELSE
247 l_suffix := '';
248 END IF;
249 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_BUGFIX1_PKG.Fix_Regions_Bugfix1', 'Fixing for IBU_CF_SR_310_X suffix:' || p_contextType || l_suffix);
250
251 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_310_' || p_contextType || l_suffix,
252 'IBU_CF_SR_PRIMARY_CONTACT_RG',
253 'Y', 'N', 'IBU_CF_SR_15_G');
254 FETCH get_regions INTO l_region_code;
255 END LOOP;
256 CLOSE get_regions;
257
258 -- Find out if there are any regions cloned for IBU_CF_SR_320_G (Update Template)
259 -- for corresponding level. We need to set the primary contact region to IBU_CF_SR_15_G
260
261 OPEN get_regions('IBU_CF_SR_320_',p_contextType);
262 FETCH get_regions INTO l_region_code;
263 WHILE get_regions%FOUND LOOP
264 -- get the resp id/appl from the suffix
265 IF (p_contextType <> 'GC') THEN
266 l_suffix := substr(l_region_code, 16);
267 ELSE
268 l_suffix := '';
269 END IF;
270 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_BUGFIX1_PKG.Fix_Regions_Bugfix1', 'Fixing for IBU_CF_SR_320_X suffix:' || p_contextType || l_suffix);
271
272 CS_CF_UPG_UTL_PKG.UpdateRegionItems('IBU_CF_SR_320_' || p_contextType || l_suffix,
273 'IBU_CF_SR_PRIMARY_CONTACT_RG',
274 'Y', 'N', 'IBU_CF_SR_15_G');
275 FETCH get_regions INTO l_region_code;
276 END LOOP;
277 CLOSE get_regions;
278
279 commit;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 CS_CF_UPG_UTL_PKG.wrapup('ERROR');
284 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_BUGFIX1_PKG.Main','Exception raised in Main');
285
286 IF (get_regions%ISOPEN) THEN
287 CLOSE get_regions;
288 END IF;
289 IF (region_exist%ISOPEN) THEN
290 CLOSE region_exist;
291 END IF;
292 IF (get_display_attributes%ISOPEN) THEN
293 CLOSE get_display_attributes;
294 END IF;
295
296
297
298 RAISE;
299 End Fix_Regions_Bugfix1;
300
301 END CS_CF_UPG_BUGFIX1_PKG;
[Home] [Help]