[Home] [Help]
PACKAGE BODY: APPS.FV_INSTALL_EXTN
Source
1 PACKAGE BODY FV_Install_Extn AS
2 -- $Header: FVXPIXTB.pls 120.14 2007/01/10 16:05:51 agovil ship $
3
4 PROCEDURE insert_ap_income_tax_types;
5 PROCEDURE Load_FV_Ldts;
6
7 g_module_name varchar2(100);
8 vp_retcode NUMBER;
9 vp_errbuf VARCHAR2(1000);
10 l_req_id NUMBER;
11 l_module_name VARCHAR2(200);
12
13 PROCEDURE Run_Process
14 (
15 errbuf OUT NOCOPY VARCHAR2,
16 retcode OUT NOCOPY VARCHAR2
17 )
18 IS
19 --
20 l_config_file VARCHAR2(100);
21 l_fnd_config_file VARCHAR2(100);
22 l_language VARCHAR2(20);
23 l_data_file VARCHAR2(100);
24 l_errbuf varchar2(300);
25 l_retval BOOLEAN;
26 l_org_id NUMBER(15); --PSKI MOAC Changes
27 --
28 cursor c_territory is select iso_territory
29 from fnd_languages
30 where installed_flag = 'B';
31 /* removed the installed_flag = 'I' to fix the issue of looking for top_dir for 'I' read
32 loader file*/
33
34 -- Check if profile is enabled at User/Responsibility level.
35 CURSOR c_prof_enabled IS
36 SELECT resp_enabled_flag, user_enabled_flag
37 FROM fnd_profile_options
38 WHERE profile_option_name = 'FV_ENABLED';
39
40 l_resp_flag fnd_profile_options.resp_enabled_flag%TYPE;
41 l_user_flag fnd_profile_options.user_enabled_flag%TYPE;
42
43 BEGIN
44 g_module_name := 'fv.plsql.fv_install_extn.';
45 l_module_name := g_module_name || 'run_process';
46 l_config_file := '@FV:patch/115/import/';
47 l_fnd_config_file := '@FND:patch/115/import/';
48 l_org_id := MO_GLOBAL.get_current_org_id; --PSKI MOAC Changes
49 l_resp_flag := 'N';
50 l_user_flag := 'N';
51
52 -- Check whether the FV_ENABLED profile is enabled for a responsibility/user
53 OPEN c_prof_enabled;
54 FETCH c_prof_enabled INTO l_resp_flag, l_user_flag;
55 CLOSE c_prof_enabled;
56
57 IF ((l_resp_flag = 'Y') OR (l_user_flag = 'Y')) THEN
58
59 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
60 'Note: As profile ''FV: Federal Enabled'' is enabled at Responsibility/User level,
61 we will not set the value of profile at Site level.');
62
63 ELSE
64 -- Bug#4533611
65 -- The following line is added to enable the profile
66 -- FV_ENABLED to Y as this process will be run only
67 -- by federal customers
68 l_retval := fnd_profile.save ('FV_ENABLED', 'Y', 'SITE');
69
70 END IF;
71
72 --
73 SAVEPOINT Run_Process_PVT ;
74 --
75 -- Load Seed Data for each of the installed languages
76
77 for c_territory_rec in c_territory loop
78
79 l_language := c_territory_rec.iso_territory ;
80
81 -- Load lookup AP data
82
83 l_data_file := l_config_file||l_language||'/fvaplkup.ldt';
84
85 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
86 l_req_id := fnd_request.submit_request
87 (program => 'FNDLOAD',
88 application => 'FND',
89 description => NULL,
90 start_time => NULL,
91 sub_request => FALSE,
92 argument1 => 'UPLOAD',
93 argument2 => l_fnd_config_file||'aflvmlu.lct',
94 argument3 => l_data_file);
95
96 if l_req_id = 0 then
97
98 errbuf := fnd_message.get ;
99 retcode := 2 ;
100 raise fnd_api.g_exc_error ;
101
102 end if;
103
104 -- Load lookup AR data
105
106 l_data_file := l_config_file||l_language||'/fvarlkup.ldt';
107
108 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
109 l_req_id := fnd_request.submit_request
110 (program => 'FNDLOAD',
111 application => 'FND',
112 description => NULL,
113 start_time => NULL,
114 sub_request => FALSE,
115 argument1 => 'UPLOAD',
116 argument2 => l_fnd_config_file||'aflvmlu.lct',
117 argument3 => l_data_file);
118
119 if l_req_id = 0 then
120
121 errbuf := fnd_message.get ;
122 retcode := 2 ;
123 raise fnd_api.g_exc_error ;
124
125 end if;
126
127 -- Load GL categories
128
129 l_data_file := l_config_file||l_language||'/fvglcat.ldt';
130
131 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
132 l_req_id := fnd_request.submit_request
133 (program => 'FNDLOAD',
134 application => 'FND',
135 description => NULL,
136 start_time => NULL,
137 sub_request => FALSE,
138 argument1 => 'UPLOAD',
139 argument2 => l_config_file||'fvglcat.lct',
140 argument3 => l_data_file);
141
142 if l_req_id = 0 then
143
144 errbuf := fnd_message.get ;
145 retcode := 2 ;
146 raise fnd_api.g_exc_error ;
147
148 end if;
149 -- Load GL Sources
150
151 l_data_file := l_config_file||l_language||'/fvglsrc.ldt';
152
153 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
154 l_req_id := fnd_request.submit_request
155 (program => 'FNDLOAD',
156 application => 'FND',
157 description => NULL,
158 start_time => NULL,
159 sub_request => FALSE,
160 argument1 => 'UPLOAD',
161 argument2 => l_config_file||'fvglsrc.lct',
162 argument3 => l_data_file);
163
164 if l_req_id = 0 then
165
166 errbuf := fnd_message.get ;
167 retcode := 2 ;
168 raise fnd_api.g_exc_error ;
169
170 end if;
171
172 end loop ;
173
174 insert_ap_income_tax_types;
175
176 -- Call this procedure to reload ldts after fvdelapi.sql run
177 -- This ensure that all the dropped AOL objects are re-created
178 -- This design also avoids dummy checkin creation for loader files.
179 -- Commented out as it this is causing errors.
180 --Load_FV_Ldts;
181
182 retcode := 0 ;
183
184 EXCEPTION
185 --
186 WHEN FND_API.G_EXC_ERROR THEN
187 --
188 ROLLBACK TO Run_Process_PVT ;
189 retcode := 2 ;
190 --
191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
192 --
193 ROLLBACK TO Run_Process_PVT ;
194 retcode := 2 ;
195 --
196 WHEN OTHERS THEN
197 --
198 l_errbuf := sqlerrm;
199 ROLLBACK TO Run_Process_PVT ;
200 retcode := 2 ;
201 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
202 --
203 END Run_Process ;
204
205 -- Procedure to insert income tax types
206
207 PROCEDURE insert_ap_income_tax_types IS
208 l_module_name varchar2(200);
209 l_errbuf varchar2(300);
210 BEGIN
211
212 g_module_name := 'fv.plsql.fv_install_extn.';
213 l_module_name := g_module_name || 'insert_ap_income_tax_types';
214
215 INSERT INTO ap_income_tax_types
216 (income_tax_type,
217 description,
218 last_update_date,
219 last_updated_by,
220 last_update_login,
221 creation_date,
222 created_by)
223 (SELECT 'GOV 1','Unemployment compensation',sysdate,1,1,sysdate,1
224 FROM DUAL
225 WHERE NOT EXISTS
226 (SELECT 'x'
227 FROM ap_income_tax_types
228 WHERE income_tax_type = 'GOV 1'
229 AND description = 'Unemployment compensation'));
230
231 INSERT INTO ap_income_tax_types
232 (income_tax_type,
233 description,
234 last_update_date,
235 last_updated_by,
236 last_update_login,
237 creation_date,
238 created_by)
239 (SELECT 'GOV 6','Taxable grants',sysdate,1,1,sysdate,1
240 FROM DUAL
241 WHERE NOT EXISTS
242 (SELECT 'x'
243 FROM ap_income_tax_types
244 WHERE income_tax_type = 'GOV 6'
245 AND description = 'Taxable grants'));
246
247 INSERT INTO ap_income_tax_types
248 (income_tax_type,
249 description,
250 last_update_date,
251 last_updated_by,
252 last_update_login,
253 creation_date,
254 created_by)
255 (SELECT 'GOV 6A','Energy grants',sysdate,1,1,sysdate,1
256 FROM DUAL
257 WHERE NOT EXISTS
258 (SELECT 'x'
259 FROM ap_income_tax_types
260 WHERE income_tax_type = 'GOV 6A'
261 AND description = 'Energy grants'));
262
263 INSERT INTO ap_income_tax_types
264 (income_tax_type,
265 description,
266 last_update_date,
267 last_updated_by,
268 last_update_login,
269 creation_date,
270 created_by)
271 (SELECT 'GOV 7','Agriculture payments',sysdate,1,1,sysdate,1
272 FROM DUAL
273 WHERE NOT EXISTS
274 (SELECT 'x'
275 FROM ap_income_tax_types
276 WHERE income_tax_type = 'GOV 7'
277 AND description = 'Agriculture payments'));
278
279 INSERT INTO ap_income_tax_types
280 (income_tax_type,
281 description,
282 last_update_date,
283 last_updated_by,
284 last_update_login,
285 creation_date,
286 created_by)
287 (SELECT 'INT 1','Interest income not included in box 3',sysdate,1,1,sysdate,1
288 FROM DUAL
289 WHERE NOT EXISTS
290 (SELECT 'x'
291 FROM ap_income_tax_types
292 WHERE income_tax_type = 'INT 1'
293 AND description = 'Interest income not included in box 3'));
294
295 INSERT INTO ap_income_tax_types
296 (income_tax_type,
297 description,
298 last_update_date,
299 last_updated_by,
300 last_update_login,
301 creation_date,
302 created_by)
303 (SELECT 'INT 1A','Financial institution interest income not included in box 3',
304 sysdate,1,1,sysdate,1
305 FROM DUAL
306 WHERE NOT EXISTS
307 (SELECT 'x'
308 FROM ap_income_tax_types
309 WHERE income_tax_type = 'INT 1A'
310 AND description = 'Financial institution interest income not included in box
311 3'));
312
313 INSERT INTO ap_income_tax_types
314 (income_tax_type,
315 description,
316 last_update_date,
317 last_updated_by,
318 last_update_login,
319 creation_date,
320 created_by)
321 (SELECT 'INT 3','Interest on U.S. Savings Bonds and Treasury obligations',
322 sysdate,1,1,sysdate,1
323 FROM DUAL
324 WHERE NOT EXISTS
325 (SELECT 'x'
326 FROM ap_income_tax_types
327 WHERE income_tax_type = 'INT 3'
328 AND description = 'Interest on U.S. Savings Bonds and Treasury obligations'))
329 ;
330
331 EXCEPTION WHEN OTHERS THEN
332 l_errbuf := sqlerrm;
333 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
334 NULL;
335
336 END insert_ap_income_tax_types;
337
338 -- Bug# 3578880
339 -- This procedure loads fvmenus.ldt, fvlkups.ldt, fvcprog.ldt, fvreqst.ldt
340 -- fvreqln.ldt and fvreqgr.ldt for all the langauges found in fnd_languages
341 -- with installed flag of I/B.
342 --
343 PROCEDURE Load_FV_Ldts AS
344 l_config_file VARCHAR2(100) ;
345 l_fnd_config_file VARCHAR2(100) ;
346 l_data_file VARCHAR2(100);
347 l_cntrl_file VARCHAR2(100);
348 l_language VARCHAR2(20);
349 i NUMBER;
350 l_org_id NUMBER(15); --PSKI MOAC Changes
351
352 --Bug#3739019
353 /*
354 CURSOR c_territory IS select iso_territory
355 from fnd_languages
356 where installed_flag in ('I', 'B');
357 */
358
359 BEGIN
360 l_config_file := '@FV:patch/115/import/';
361 l_fnd_config_file := '@FND:patch/115/import/';
362 g_module_name := 'fv.plsql.fv_install_extn.';
363 l_module_name := g_module_name || 'load_fv_ldts';
364
365 --Bug#3739019
366 /*
367 -- Load Seed Data for each of the installed languages
368 FOR c_territory_rec IN c_territory
369 LOOP -- language
370 l_language := c_territory_rec.iso_territory;
371 */
372 l_language := 'US';
373
374 FOR i IN 1..6
375 LOOP -- ldts
376 IF i = 1
377 THEN
378 l_data_file := l_config_file||l_language||'/fvmenus.ldt';
379 l_cntrl_file:= l_fnd_config_file||'afsload.lct';
380 ELSIF i = 2
381 THEN
382 l_data_file := l_config_file||l_language||'/fvlkups.ldt';
383 l_cntrl_file:= l_fnd_config_file||'aflvmlu.lct';
384 ELSIF i = 3
385 THEN
386 l_data_file := l_config_file||l_language||'/fvcprog.ldt';
387 l_cntrl_file:= l_fnd_config_file||'afcpprog.lct';
388 ELSIF i = 4
389 THEN
390 l_data_file := l_config_file||l_language||'/fvreqst.ldt';
391 l_cntrl_file:= l_fnd_config_file||'afcprset.lct';
392 ELSIF i = 5
393 THEN
394 l_data_file := l_config_file||l_language||'/fvreqln.ldt';
395 l_cntrl_file:= l_fnd_config_file||'afcprset.lct';
396 ELSIF i = 6
397 THEN
398 l_data_file := l_config_file||l_language||'/fvreqgr.ldt';
399 l_cntrl_file:= l_fnd_config_file||'afcpreqg.lct';
400 END IF;
401
402 l_org_id := MO_GLOBAL.get_current_org_id; --PSKI MOAC Changes
403 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
404 l_req_id := fnd_request.submit_request
405 (program => 'FNDLOAD',
406 application => 'FND',
407 description => NULL,
408 start_time => NULL,
409 sub_request => FALSE,
410 argument1 => 'UPLOAD',
411 argument2 => l_cntrl_file,
412 argument3 => l_data_file);
413
414 IF l_req_id = 0
415 THEN
416 vp_errbuf := fnd_message.get;
417 vp_retcode := -1;
418 raise fnd_api.g_exc_error;
419 END IF;
420 END LOOP; -- ldts
421 --Bug#3739019
422 /*
423 END LOOP; -- language
424 */
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 vp_errbuf := 'Error in Procedure Load_FV_Ldts: '|| sqlerrm;
429 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',vp_errbuf);
430 END Load_FV_Ldts;
431
432 END FV_Install_Extn ;