1 package body fnd_industry_activator as
2 /* $Header: afindactb.pls 120.3 2005/08/31 21:09:14 dbowles noship $ */
3 v_industry_id NUMBER(3);
4
5 ---- PRIVATE ROUTINES
6 procedure activate_messages is
7
8 cursor industry_message_list(p_industry_id IN NUMBER) is
9 select application_id,
10 message_name,
11 message_text,
12 language_code
13 from fnd_new_messages_il
14 where industry_id = p_industry_id;
15
16 cursor get_original_text(p_app_id IN NUMBER,
17 p_message_name IN VARCHAR2,
18 p_language_code IN VARCHAR2) is
19 select
20 message_text
21 from fnd_new_messages
22 where application_id = p_app_id and
23 message_name = p_message_name and
24 language_code = p_language_code and
25 last_updated_by IN ('0','1','2');
26
27 BEGIN
28 FND_FILE.NEW_LINE(FND_FILE.LOG);
29 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating industry translated message text.');
30 -- get the list of messages for the current industry from fnd_new_messages_il
31 for v1 in industry_message_list(v_industry_id) loop
32 -- populate the orig_message_text column
33 for v2 in get_original_text(v1.application_id,
34 v1.message_name,
35 v1.language_code) loop
36 update fnd_new_messages_il
37 set orig_message_text = v2.message_text
38 where application_id = v1.application_id and
39 message_name = v1.message_name and
40 language_code = v1.language_code and
41 industry_id = v_industry_id;
42 end loop;
43 -- replace the message_text in fnd_new_messages with the message_text for the active
44 -- industry
45 update fnd_new_messages
46 set message_text = v1.message_text,
47 last_updated_by = 8
48 where application_id = v1.application_id and
49 message_name = v1.message_name and
50 language_code = v1.language_code;
51 end loop;
52 commit;
53 FND_FILE.NEW_LINE(FND_FILE.LOG);
54 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update of industry translated message text completed.');
55 END activate_messages;
56
57 procedure activate_lookups is
58
59 cursor industry_lookup_list(p_industry_id IN NUMBER) is
60 select application_id,
61 lookup_type,
62 lookup_code,
63 language_code,
64 meaning,
65 description
66 from fnd_lookup_values_il
67 where industry_id = p_industry_id;
68
69 cursor get_original_lookups(p_app_id IN NUMBER,
70 p_lookup_type IN VARCHAR2,
71 p_lookup_code IN VARCHAR2,
72 p_language_code IN VARCHAR2) is
73 select
74 meaning,
75 description
76 from fnd_lookup_values
77 where view_application_id = p_app_id and
78 lookup_type = p_lookup_type and
79 lookup_code = p_lookup_code and
80 language = p_language_code and
81 last_updated_by IN ('0','1','2');
82
83 BEGIN
84 FND_FILE.NEW_LINE(FND_FILE.LOG);
85 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating industry translated lookups.');
86 -- get the list of lookups for the current industry from fnd_lookup_values_il
87 for v1 in industry_lookup_list(v_industry_id) loop
88 -- populate the orig_message_text column
89 for v2 in get_original_lookups(v1.application_id,
90 v1.lookup_type,
91 v1.lookup_code,
92 v1.language_code) loop
93 update fnd_lookup_values_il
94 set orig_meaning = v2.meaning,
95 orig_description = v2.description
96 where application_id = v1.application_id and
97 lookup_type = v1.lookup_type and
98 lookup_code = v1.lookup_code and
99 language_code = v1.language_code and
100 industry_id = v_industry_id;
101 end loop;
102 -- replace the meaning and/or description in fnd_lookup_values
103 -- for the active industry
104 -- using exception handlers around the update meaning statement so that
105 -- we do not violate unique index constraint (APPLSYS.FND_LOOKUP_VALUES_U2)
106 -- this constraint means for a given lookup type there multiple lookup codes cannot
107 -- have the same meaning. If the update violates the constraint, we silently do not update that row.
108 begin
109 if v1.meaning is NOT NULL then
110 update fnd_lookup_values
111 set meaning = v1.meaning,
112 last_updated_by = 8
113 where view_application_id = v1.application_id and
114 lookup_type = v1.lookup_type and
115 lookup_code = v1.lookup_code and
116 language = v1.language_code;
117 end if;
118 exception
119 when others then
120 null;
121 end;
122 if v1.description is NOT NULL then
123 update fnd_lookup_values
124 set description = v1.description,
125 last_updated_by = 8
126 where view_application_id = v1.application_id and
127 lookup_type = v1.lookup_type and
128 lookup_code = v1.lookup_code and
129 language = v1.language_code;
130 end if;
131 end loop;
132 commit;
133 FND_FILE.NEW_LINE(FND_FILE.LOG);
134 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Completed updating industry translated lookups.');
135 END activate_lookups;
136
137 procedure section_title (which IN number,
138 title IN varchar2) is
139
140 begin
141 FND_FILE.NEW_LINE(which);
142 FND_FILE.PUT_LINE(which, '----------------------------------------------------------------');
143 FND_FILE.PUT_LINE(which, title);
144 FND_FILE.PUT_LINE(which, '----------------------------------------------------------------');
145 end section_title;
146
147
148 ----PUBLIC ROUTINES
149
150 procedure activate_industry(errbuf OUT NOCOPY VARCHAR2,
151 retcode OUT NOCOPY VARCHAR2,
152 p_industry_id IN VARCHAR2) is
153 e_bad_profile EXCEPTION;
154 e_deactivate EXCEPTION;
155 v_errbuf VARCHAR2(2000);
156 v_retcode VARCHAR2(3);
157 v_activate_date DATE;
158 BEGIN
159 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start of FND_INDUSTRY_ACTIVATION');
160 v_activate_date := SYSDATE;
161 deactivate_industries(v_errbuf,
162 v_retcode);
163 if retcode = 2 then
164 raise e_deactivate;
165 end if;
166 v_industry_id := to_number(p_industry_id);
167 --Now set the profile to the new value for the desired industry
168 if fnd_profile.save('FND_INDUSTRY_ID', v_industry_id,'SITE') then
169 section_title(FND_FILE.LOG, 'Activating industry '||v_industry_id);
170 commit;
171 else
172 raise e_bad_profile;
173 end if;
174 activate_messages;
175 activate_lookups;
176 errbuf := ('Industry activation complete.');
177 retcode := 0 ;
178 EXCEPTION
179 when e_bad_profile then
180 errbuf := ('Industry activation failed. Make sure that the FND_INDUSTRY_ID profile
181 has been defined for your system.');
182 retcode := 2 ;
183 when e_deactivate then
184 null;
185 END activate_industry;
186
187 procedure deactivate_industries(errbuf OUT NOCOPY VARCHAR2,
188 retcode OUT NOCOPY VARCHAR2) is
189 cursor restore_message_text is
190 select il.application_id,
191 il.message_name,
192 il.language_code,
193 il.orig_message_text
194 from fnd_new_messages_il il,
195 fnd_new_messages n
196 where il.industry_id = v_industry_id and
197 il.application_id = n.application_id and
198 il.language_code = n.language_code and
199 il.message_name = n.message_name and
200 n.last_updated_by = 8;
201
202 cursor restore_lookups is
203 select il.application_id,
204 il.lookup_type,
205 il.lookup_code,
206 il.language_code,
207 il.orig_meaning,
208 il.orig_description
209 from fnd_lookup_values_il il,
210 fnd_lookup_values lv
211 where il.industry_id = v_industry_id and
212 il.application_id = lv.view_application_id and
213 il.lookup_type = lv.lookup_type and
214 il.lookup_code = lv.lookup_code and
215 il.language_code = lv.language and
216 lv.last_updated_by = 8;
217
218 BEGIN
219 v_industry_id := to_number(fnd_profile.value('FND_INDUSTRY_ID'));
220 -- if the profile returns a null value then industries were never activated
221 if v_industry_id is NULL then
222
223 return;
224 end if;
225 FND_FILE.NEW_LINE(FND_FILE.LOG);
226 section_title(FND_FILE.LOG, 'Deactivating INDUSTRY_ID '||v_industry_id);
227 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Restoring original message text.');
228
229
230 for v1 in restore_message_text loop
231
232 if v1.orig_message_text is not null then
233 update fnd_new_messages
234 set message_text = v1.orig_message_text,
235 last_updated_by = 2
236 where application_id = v1.application_id and
237 message_name = v1.message_name and
238 language_code = v1.language_code;
239 end if;
240 end loop;
241 commit;
242 FND_FILE.PUT_LINE(FND_FILE.LOG,'Original message text restored.');
243 -- clean up the orig_message_text column in the FND_NEW_MESSAGES_IL table
244 update fnd_new_messages_il
245 set orig_message_text = null;
246 commit;
247 FND_FILE.NEW_LINE(FND_FILE.LOG);
248 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Restoring original lookup values meaning and description.');
249 for v2 in restore_lookups loop
250 if v2.orig_meaning is not null then
251 update fnd_lookup_values
252 set meaning = v2.orig_meaning,
253 description = v2.orig_description,
254 last_updated_by = 2
255 where view_application_id = v2.application_id
256 and lookup_type = v2.lookup_type
257 and lookup_code = v2.lookup_code
258 and language = v2.language_code;
259 end if;
260 end loop;
261 commit;
262 FND_FILE.PUT_LINE(FND_FILE.LOG,'Original lookup value meanings and descriptions restored.');
263 -- clean up the orig_meaning and orig_description columns in the FND_LOOKUP_VALUES_IL table
264 update fnd_lookup_values_il
265 set orig_meaning = null,
266 orig_description = null;
267 commit;
268 if fnd_profile.save('FND_INDUSTRY_ID', NULL,'SITE') then
269 errbuf := ('Industry deactivation complete.');
270 retcode := 0 ;
271 else
272 errbuf := ('Industry deactivation failed');
273 retcode := 2 ;
274 end if;
275 END deactivate_industries;
276
277 END;
[Home] [Help]