[Home] [Help]
PACKAGE BODY: APPS.FV_INSTALL_EXTN
Source
1 PACKAGE BODY FV_Install_Extn AS
2 -- $Header: FVXPIXTB.pls 120.15 2010/08/27 09:38:03 amaddula 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 --CGAC: Load BETC data
173
174 l_data_file := l_config_file||l_language||'/fvbetc.ldt';
175
176 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
177 l_req_id := fnd_request.submit_request
178 (program => 'FNDLOAD',
179 application => 'FND',
180 description => NULL,
181 start_time => NULL,
182 sub_request => FALSE,
183 argument1 => 'UPLOAD',
184 argument2 => l_config_file||'fvbetc.lct',
185 argument3 => l_data_file);
186
187 if l_req_id = 0 then
188
189 errbuf := fnd_message.get ;
190 retcode := 2 ;
191 raise fnd_api.g_exc_error ;
192
193 end if;
194
195 end loop ;
196
197 insert_ap_income_tax_types;
198
199 -- Call this procedure to reload ldts after fvdelapi.sql run
200 -- This ensure that all the dropped AOL objects are re-created
201 -- This design also avoids dummy checkin creation for loader files.
202 -- Commented out as it this is causing errors.
203 --Load_FV_Ldts;
204
205 retcode := 0 ;
206
207 EXCEPTION
208 --
209 WHEN FND_API.G_EXC_ERROR THEN
210 --
211 ROLLBACK TO Run_Process_PVT ;
212 retcode := 2 ;
213 --
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 --
216 ROLLBACK TO Run_Process_PVT ;
217 retcode := 2 ;
218 --
219 WHEN OTHERS THEN
220 --
221 l_errbuf := sqlerrm;
222 ROLLBACK TO Run_Process_PVT ;
223 retcode := 2 ;
224 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
225 --
226 END Run_Process ;
227
228 -- Procedure to insert income tax types
229
230 PROCEDURE insert_ap_income_tax_types IS
231 l_module_name varchar2(200);
232 l_errbuf varchar2(300);
233 BEGIN
234
235 g_module_name := 'fv.plsql.fv_install_extn.';
236 l_module_name := g_module_name || 'insert_ap_income_tax_types';
237
238 INSERT INTO ap_income_tax_types
239 (income_tax_type,
240 description,
241 last_update_date,
242 last_updated_by,
243 last_update_login,
244 creation_date,
245 created_by)
246 (SELECT 'GOV 1','Unemployment compensation',sysdate,1,1,sysdate,1
247 FROM DUAL
248 WHERE NOT EXISTS
249 (SELECT 'x'
250 FROM ap_income_tax_types
251 WHERE income_tax_type = 'GOV 1'
252 AND description = 'Unemployment compensation'));
253
254 INSERT INTO ap_income_tax_types
255 (income_tax_type,
256 description,
257 last_update_date,
258 last_updated_by,
259 last_update_login,
260 creation_date,
261 created_by)
262 (SELECT 'GOV 6','Taxable grants',sysdate,1,1,sysdate,1
263 FROM DUAL
264 WHERE NOT EXISTS
265 (SELECT 'x'
266 FROM ap_income_tax_types
267 WHERE income_tax_type = 'GOV 6'
268 AND description = 'Taxable grants'));
269
270 INSERT INTO ap_income_tax_types
271 (income_tax_type,
272 description,
273 last_update_date,
274 last_updated_by,
275 last_update_login,
276 creation_date,
277 created_by)
278 (SELECT 'GOV 6A','Energy grants',sysdate,1,1,sysdate,1
279 FROM DUAL
280 WHERE NOT EXISTS
281 (SELECT 'x'
282 FROM ap_income_tax_types
283 WHERE income_tax_type = 'GOV 6A'
284 AND description = 'Energy grants'));
285
286 INSERT INTO ap_income_tax_types
287 (income_tax_type,
288 description,
289 last_update_date,
290 last_updated_by,
291 last_update_login,
292 creation_date,
293 created_by)
294 (SELECT 'GOV 7','Agriculture payments',sysdate,1,1,sysdate,1
295 FROM DUAL
296 WHERE NOT EXISTS
297 (SELECT 'x'
298 FROM ap_income_tax_types
299 WHERE income_tax_type = 'GOV 7'
300 AND description = 'Agriculture payments'));
301
302 INSERT INTO ap_income_tax_types
303 (income_tax_type,
304 description,
305 last_update_date,
306 last_updated_by,
307 last_update_login,
308 creation_date,
309 created_by)
310 (SELECT 'INT 1','Interest income not included in box 3',sysdate,1,1,sysdate,1
311 FROM DUAL
312 WHERE NOT EXISTS
313 (SELECT 'x'
314 FROM ap_income_tax_types
315 WHERE income_tax_type = 'INT 1'
316 AND description = 'Interest income not included in box 3'));
317
318 INSERT INTO ap_income_tax_types
319 (income_tax_type,
320 description,
321 last_update_date,
322 last_updated_by,
323 last_update_login,
324 creation_date,
325 created_by)
326 (SELECT 'INT 1A','Financial institution interest income not included in box 3',
327 sysdate,1,1,sysdate,1
328 FROM DUAL
329 WHERE NOT EXISTS
330 (SELECT 'x'
331 FROM ap_income_tax_types
332 WHERE income_tax_type = 'INT 1A'
333 AND description = 'Financial institution interest income not included in box
334 3'));
335
336 INSERT INTO ap_income_tax_types
337 (income_tax_type,
338 description,
339 last_update_date,
340 last_updated_by,
341 last_update_login,
342 creation_date,
343 created_by)
344 (SELECT 'INT 3','Interest on U.S. Savings Bonds and Treasury obligations',
345 sysdate,1,1,sysdate,1
346 FROM DUAL
347 WHERE NOT EXISTS
348 (SELECT 'x'
349 FROM ap_income_tax_types
350 WHERE income_tax_type = 'INT 3'
351 AND description = 'Interest on U.S. Savings Bonds and Treasury obligations'))
352 ;
353
354 EXCEPTION WHEN OTHERS THEN
355 l_errbuf := sqlerrm;
356 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
357 NULL;
358
359 END insert_ap_income_tax_types;
360
361 -- Bug# 3578880
362 -- This procedure loads fvmenus.ldt, fvlkups.ldt, fvcprog.ldt, fvreqst.ldt
363 -- fvreqln.ldt and fvreqgr.ldt for all the langauges found in fnd_languages
364 -- with installed flag of I/B.
365 --
366 PROCEDURE Load_FV_Ldts AS
367 l_config_file VARCHAR2(100) ;
368 l_fnd_config_file VARCHAR2(100) ;
369 l_data_file VARCHAR2(100);
370 l_cntrl_file VARCHAR2(100);
371 l_language VARCHAR2(20);
372 i NUMBER;
373 l_org_id NUMBER(15); --PSKI MOAC Changes
374
375 --Bug#3739019
376 /*
377 CURSOR c_territory IS select iso_territory
378 from fnd_languages
379 where installed_flag in ('I', 'B');
380 */
381
382 BEGIN
383 l_config_file := '@FV:patch/115/import/';
384 l_fnd_config_file := '@FND:patch/115/import/';
385 g_module_name := 'fv.plsql.fv_install_extn.';
386 l_module_name := g_module_name || 'load_fv_ldts';
387
388 --Bug#3739019
389 /*
390 -- Load Seed Data for each of the installed languages
391 FOR c_territory_rec IN c_territory
392 LOOP -- language
393 l_language := c_territory_rec.iso_territory;
394 */
395 l_language := 'US';
396
397 FOR i IN 1..6
398 LOOP -- ldts
399 IF i = 1
400 THEN
401 l_data_file := l_config_file||l_language||'/fvmenus.ldt';
402 l_cntrl_file:= l_fnd_config_file||'afsload.lct';
403 ELSIF i = 2
404 THEN
405 l_data_file := l_config_file||l_language||'/fvlkups.ldt';
406 l_cntrl_file:= l_fnd_config_file||'aflvmlu.lct';
407 ELSIF i = 3
408 THEN
409 l_data_file := l_config_file||l_language||'/fvcprog.ldt';
410 l_cntrl_file:= l_fnd_config_file||'afcpprog.lct';
411 ELSIF i = 4
412 THEN
413 l_data_file := l_config_file||l_language||'/fvreqst.ldt';
414 l_cntrl_file:= l_fnd_config_file||'afcprset.lct';
415 ELSIF i = 5
416 THEN
417 l_data_file := l_config_file||l_language||'/fvreqln.ldt';
418 l_cntrl_file:= l_fnd_config_file||'afcprset.lct';
419 ELSIF i = 6
420 THEN
421 l_data_file := l_config_file||l_language||'/fvreqgr.ldt';
422 l_cntrl_file:= l_fnd_config_file||'afcpreqg.lct';
423 END IF;
424
425 l_org_id := MO_GLOBAL.get_current_org_id; --PSKI MOAC Changes
426 FND_REQUEST.set_org_id(l_org_id); --PSKI MOAC Changes
427 l_req_id := fnd_request.submit_request
428 (program => 'FNDLOAD',
429 application => 'FND',
430 description => NULL,
431 start_time => NULL,
432 sub_request => FALSE,
433 argument1 => 'UPLOAD',
434 argument2 => l_cntrl_file,
435 argument3 => l_data_file);
436
437 IF l_req_id = 0
438 THEN
439 vp_errbuf := fnd_message.get;
440 vp_retcode := -1;
441 raise fnd_api.g_exc_error;
442 END IF;
443 END LOOP; -- ldts
444 --Bug#3739019
445 /*
446 END LOOP; -- language
447 */
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 vp_errbuf := 'Error in Procedure Load_FV_Ldts: '|| sqlerrm;
452 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',vp_errbuf);
453 END Load_FV_Ldts;
454
455 END FV_Install_Extn ;