[Home] [Help]
PACKAGE BODY: APPS.FV_SETUP_PKG
Source
1 PACKAGE BODY FV_SETUP_PKG AS
2 -- $Header: FVFCATTB.pls 120.17.12020000.2 2013/03/20 13:46:26 yanasing ship $
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_SETUP_PKG.';
4 -------------------------------------------------------------------
5 l_config_file VARCHAR2(100) := '@FV:patch/115/import/';
6 l_data_file VARCHAR2(100) := '@FV:patch/115/import/US/';
7 l_fa_config VARCHAR2(100) := '@OFA:patch/115/import/';
8 l_language VARCHAR2(20) ;
9 l_req_id NUMBER ;
10
11 --------------------------------------------------------------------
12 Procedure FACTS_ATTRIBUTES (errbuf OUT NOCOPY varchar2,
13 retcode OUT NOCOPY varchar2,
14 p_yes_no in varchar2) is
15 l_module_name VARCHAR2(200) := g_module_name || 'FACTS_ATTRIBUTES';
16 --v_count number;
17 v_message Varchar2(500);
18 --v_errbuf Varchar2(255);
19 --v_retcode Varchar2(255);
20 --v_attr_inserted number :=0;
21 --v_codes_inserted number :=0;
22 --v_accts_inserted number :=0;
23
24 begin
25
26 l_req_id := fnd_request.submit_request
27 (program => 'FNDLOAD',
28 application => 'FND',
29 description => NULL,
30 start_time => NULL,
31 sub_request => FALSE,
32 argument1 => 'UPLOAD',
33 argument2 => l_config_file||'fvfcattb.lct',
34 argument3 => l_data_file||'fvfcattb.ldt');
35
36 if l_req_id = 0 then
37
38 errbuf := fnd_message.get ;
39 retcode := -1 ;
40 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
41 raise fnd_api.g_exc_error ;
42 rollback;
43 return;
44 ELSE
45 l_req_id := fnd_request.submit_request
46 (program => 'FNDLOAD',
47 application => 'FND',
48 description => NULL,
49 start_time => NULL,
50 sub_request => FALSE,
51 argument1 => 'UPLOAD',
52 argument2 => l_config_file||'fvfcrt7.lct',
53 argument3 => l_data_file||'fvfcrt7.ldt');
54
55 if l_req_id = 0 then
56 errbuf := fnd_message.get ;
57 retcode := -1 ;
58 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message2',errbuf);
59 raise fnd_api.g_exc_error ;
60 rollback;
61 return;
62 END IF;
63
64 COMMIT;
65
66 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
67 v_message :='FACTS attributes table setup program successfully Requested';
68 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message3',v_message);
69 END IF;
70
71 if p_yes_no = 'N' then
72 retcode := 1;
73 errbuf := 'FACTS II requires US SGL compliance if the natural account segment has been expanded to accomodate Agency
74 specific requirements, designate a parent account that is 4-digit US SGL Account';
75 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message4',errbuf);
76 end if;
77 END IF;
78
79 Exception
80 When Others Then
81 errbuf := substr(SQLERRM,1,225);
82 retcode := -1;
83 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
84 l_module_name||'.final_exception',errbuf);
85 END;
86 ----------------------------------------------------------------------------
87
88 Procedure FUNDS_AVAILABLE (errbuf OUT NOCOPY varchar2,
89 retcode OUT NOCOPY varchar2) is
90 l_module_name VARCHAR2(200) := g_module_name || 'FUNDS_AVAILABLE';
91 begin
92 l_req_id := fnd_request.submit_request
93 (program => 'FNDLOAD',
94 application => 'FND',
95 description => NULL,
96 start_time => NULL,
97 sub_request => FALSE,
98 argument1 => 'UPLOAD',
99 argument2 => l_config_file||'fvfundav.lct',
100 argument3 => l_data_file||'fvfundav.ldt');
101
102 if l_req_id = 0 then
103 errbuf := fnd_message.get ;
104 retcode := -1 ;
105 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
106 raise fnd_api.g_exc_error ;
107 rollback;
108 return;
109 END IF;
110 commit;
111 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
112 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message2',
113 'Fund Available table seed data process sucessfully requested');
114 END IF;
115 EXCEPTION
116 WHEN OTHERS THEN
117 errbuf := SQLERRM;
118 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
119 l_module_name||'.final_exception',errbuf);
120 RAISE;
121 End;
122 ------------------------------------------------------------------------------
123 Procedure USSGL_LOAD (errbuf OUT NOCOPY varchar2,
124 retcode OUT NOCOPY varchar2,
125 del_existing_data IN VARCHAR2) is
126 l_module_name VARCHAR2(200) := g_module_name || 'USSGL_LOAD';
127
128 BEGIN
129
130 IF del_existing_data = 'Y' THEN
131 fv_utility.log_mesg('Deleting USSGL setup data!');
132 BEGIN
133
134 delete from FV_FACTS_USSGL_ACCOUNTS;
135 fv_utility.log_mesg('Deleted from FV_FACTS_USSGL_ACCOUNTS');
136 EXCEPTION WHEN OTHERS THEN
137 fv_utility.log_mesg('When others error when deleting
138 USSGL setup data: '||SQLERRM);
139 END;
140
141 END IF;
142
143 l_req_id := fnd_request.submit_request
144 (program => 'FNDLOAD',
145 application => 'FND',
146 description => NULL,
147 start_time => NULL,
148 sub_request => FALSE,
149 argument1 => 'UPLOAD',
150 argument2 => l_config_file||'fvfcusgl.lct',
151 argument3 => l_data_file||'fvfcusgl.ldt');
152
153 if l_req_id = 0 then
154 errbuf := fnd_message.get ;
155 retcode := -1 ;
156 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
157 raise fnd_api.g_exc_error ;
158 rollback;
159 return;
160 END IF;
161 commit;
162 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
163 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message2',
164 'USSGL table setup seed data process sucessfully requested');
165 END IF;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 errbuf := SQLERRM;
170 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
171 RAISE;
172
173 End;
174 ------------------------------------------------------------------------------
175
176 Procedure LOAD_FUND_TRANSMISSION_FORMATS (errbuf OUT NOCOPY varchar2,
177 retcode OUT NOCOPY varchar2) is
178 l_module_name VARCHAR2(200) := g_module_name || 'LOAD_FUND_TRANSMISSION_FORMATS';
179 begin
180 l_req_id := fnd_request.submit_request
181 (program => 'FNDLOAD',
182 application => 'FND',
183 description => NULL,
184 start_time => NULL,
185 sub_request => FALSE,
186 argument1 => 'UPLOAD',
187 argument2 => l_config_file||'fvxftran.lct',
188 argument3 => l_data_file||'fvxftran.ldt');
189
190 if l_req_id = 0 then
191 errbuf := fnd_message.get ;
192 retcode := -1 ;
193 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
194 raise fnd_api.g_exc_error ;
195 rollback;
196 return;
197 END IF;
198 commit;
199 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
200 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message4',
201 'Treasury transmission format seed data process successfully requested');
202 END IF;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 errbuf := SQLERRM;
207 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
208 RAISE;
209 End;
210 ------------------------------------------------------------------------------
211 Procedure CFS_TABLE_SETUP (errbuf OUT NOCOPY varchar2,
212 retcode OUT NOCOPY varchar2) is
213 l_module_name VARCHAR2(200) := g_module_name || 'CFS_TABLE_SETUP';
214 rphase1 VARCHAR2(30);
215 rstatus1 VARCHAR2(30);
216 dphase1 VARCHAR2(30);
217 dstatus1 VARCHAR2(30);
218 message1 VARCHAR2(240);
219 call_status1 BOOLEAN;
220
221 begin
222
223 begin
224 l_req_id := fnd_request.submit_request
225 (program => 'FNDLOAD',
226 application => 'FND',
227 description => NULL,
228 start_time => NULL,
229 sub_request => FALSE,
230 argument1 => 'UPLOAD',
231 argument2 => l_config_file||'fvcfsrds.lct',
232 argument3 => l_data_file||'fvcfsrd1.ldt');
233
234 if l_req_id = 0 then
235 errbuf := fnd_message.get ;
236 retcode := -1 ;
237 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
238 raise fnd_api.g_exc_error ;
239 rollback;
240 return;
241 END IF;
242 commit;
243 end ;
244 LOOP
245 call_status1 :=
246 FND_CONCURRENT.GET_REQUEST_STATUS(l_req_id,NULL,NULL,rphase1,rstatus1,dphase1,dstatus1,message1);
247 EXIT WHEN ((call_status1 and dphase1 = 'COMPLETE') or
248 NOT(call_status1));
249 DBMS_LOCK.SLEEP(5);
250 END LOOP;
251 begin
252 l_req_id := fnd_request.submit_request
253 (program => 'FNDLOAD',
254 application => 'FND',
255 description => NULL,
256 start_time => NULL,
257 sub_request => FALSE,
258 argument1 => 'UPLOAD',
259 argument2 => l_config_file||'fvcfsrds.lct',
260 argument3 => l_data_file||'fvcfsrd2.ldt');
261
262 if l_req_id = 0 then
263 errbuf := fnd_message.get ;
264 retcode := -1 ;
265 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message2',errbuf);
266 raise fnd_api.g_exc_error ;
267 rollback;
268 return;
269 END IF;
270 commit;
271 end ;
272 begin
273 l_req_id := fnd_request.submit_request
274 (program => 'FNDLOAD',
275 application => 'FND',
276 description => NULL,
277 start_time => NULL,
278 sub_request => FALSE,
279 argument1 => 'UPLOAD',
280 argument2 => l_config_file||'fvcfsrds.lct',
281 argument3 => l_data_file||'fvcfsrd3.ldt');
282
283 if l_req_id = 0 then
284 errbuf := fnd_message.get ;
285 retcode := -1 ;
286 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message3',errbuf);
287 raise fnd_api.g_exc_error ;
288 rollback;
289 return;
290 END IF;
291 commit;
292 end ;
293 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
294 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
295 l_module_name||'.message4','Financial statements table seed data process
296 sucessfully requested');
297 END IF;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 errbuf := SQLERRM;
302 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
303 RAISE;
304 End;
305 ------------------------------------------------------------------------------
306 Procedure LOAD_SF133_SETUP_DATA (errbuf OUT NOCOPY varchar2,
307 retcode OUT NOCOPY varchar2,
308 p_delete_133_setup IN VARCHAR2) is
309 l_module_name VARCHAR2(200) := g_module_name || 'LOAD_SF133_SETUP_DATA';
310 begin
311
312
313 IF p_delete_133_setup = 'Y' THEN
314 fv_utility.log_mesg('Deleting 133 setup data');
315 BEGIN
316
317 delete from FV_SF133_REP_LINE_CALC;
318 fv_utility.log_mesg('Deleted from FV_SF133_REP_LINE_CALC');
319 delete from FV_SF133_DEFINITIONS_ACCTS;
320 fv_utility.log_mesg('Deleted from FV_SF133_DEFINITIONS_ACCTS');
321 delete from FV_SF133_DEFINITIONS_LINES;
322 fv_utility.log_mesg('Deleted from FV_SF133_DEFINITIONS_LINES');
323 EXCEPTION WHEN OTHERS THEN
327
324 fv_utility.log_mesg('When others error when deleting
325 SF133 data: '||SQLERRM);
326 END;
328 END IF;
329 l_req_id := fnd_request.submit_request
330 (program => 'FNDLOAD',
331 application => 'FND',
332 description => NULL,
333 start_time => NULL,
334 sub_request => FALSE,
335 argument1 => 'UPLOAD',
336 argument2 => l_config_file||'fvsf133.lct',
337 argument3 => l_data_file||'fvsf133.ldt');
338
339
340 if l_req_id = 0 then
341 errbuf := fnd_message.get ;
342 retcode := -1 ;
343 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
344 raise fnd_api.g_exc_error ;
345 rollback;
346 return;
347 END IF;
348 commit;
349 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
350 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',
351 'SF133 seed data process successfully requested');
352 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 errbuf := SQLERRM;
357 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
358 RAISE;
359 End;
360
361 ------------------------------------------------------------------------------
362 Procedure LOAD_SBR_SETUP_DATA (errbuf OUT NOCOPY varchar2,
363 retcode OUT NOCOPY varchar2,
364 p_delete_sbr_setup IN VARCHAR2) is
365 l_module_name VARCHAR2(200) := g_module_name || 'LOAD_SBR_SETUP_DATA';
366 begin
367
368
369 IF p_delete_sbr_setup = 'Y' THEN
370 fv_utility.log_mesg('Deleting SBR setup data');
371 BEGIN
372
373 delete from FV_SBR_REP_LINE_CALC;
374 fv_utility.log_mesg('Deleted from FV_SBR_REP_LINE_CALC');
375 delete from FV_SBR_DEFINITIONS_ACCTS;
376 fv_utility.log_mesg('Deleted from FV_SBR_DEFINITIONS_ACCTS');
377 delete from FV_SBR_DEFINITIONS_LINES;
378 fv_utility.log_mesg('Deleted from FV_SBR_DEFINITIONS_LINES');
379 EXCEPTION WHEN OTHERS THEN
380 fv_utility.log_mesg('When others error when deleting SBR data: '||SQLERRM);
381 END;
382
383 END IF;
384 l_req_id := fnd_request.submit_request
385 (program => 'FNDLOAD',
386 application => 'FND',
387 description => NULL,
388 start_time => NULL,
389 sub_request => FALSE,
390 argument1 => 'UPLOAD',
391 argument2 => l_config_file||'fvsbr.lct',
392 argument3 => l_data_file||'fvsbr.ldt');
393
394
395 if l_req_id = 0 then
396 errbuf := fnd_message.get ;
397 retcode := -1 ;
398 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
399 raise fnd_api.g_exc_error ;
400 rollback;
401 return;
402 END IF;
403 commit;
404 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
405 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',
406 'SBR seed data process successfully requested');
407 END IF;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 errbuf := SQLERRM;
412 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
413 RAISE;
414 End;
415
416 ------------------------------------------------------------------------------
417 PROCEDURE load_rx_reports (errbuf OUT NOCOPY varchar2,
418 retcode OUT NOCOPY varchar2) IS
419 l_module_name VARCHAR2(200) := g_module_name || 'load_rx_reports';
420
421 BEGIN
422 l_req_id := fnd_request.submit_request
423 (program => 'FNDLOAD',
424 application => 'FND',
425 description => NULL,
426 start_time => NULL,
427 sub_request => FALSE,
428 argument1 => 'UPLOAD',
429 argument2 => l_fa_config||'farx.lct',
430 --argument2 => l_config_file||'fvrxi.lct',
431 argument3 => l_data_file||'fvrxi.ldt');
432
433 IF l_req_id = 0 THEN
434 errbuf := fnd_message.get ;
435 retcode := -1 ;
436 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
437 raise fnd_api.g_exc_error ;
438 ROLLBACK;
439 RETURN;
440 END IF;
441 COMMIT;
442 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
443 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message2',
444 'RXi reports upload process successfully requested');
445 END IF;
446
447 -- Updating the RX report responsiblity_id
448 -- This is required because Rxi seed populates responsiblity id
449 -- stored in seed115 for Federal Administrator, which is not correct
450 -- Bug 12800627: Added report ids for RX-only: Rollover Treasury Account Symbols
451 -- and RX-only: Reimbursable Activity Report
452 UPDATE fa_rx_reports
453 SET responsibility_id = (SELECT responsibility_id
454 FROM FND_responsibility_tl
455 WHERE responsibility_name = 'Federal Administrator'
456 AND language= 'US')
457 WHERE report_id IN (395,397,399,415,417,100227);
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 errbuf := SQLERRM;
462 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
463 RAISE;
464 END;
465
466 PROCEDURE load_reimb_act_definitions (errbuf OUT NOCOPY varchar2,
467 retcode OUT NOCOPY varchar2) IS
468
469 l_module_name VARCHAR2(200) := g_module_name ||
470 'load_reimb_act_definitions';
471
472 BEGIN
473 l_req_id := fnd_request.submit_request
474 (program => 'FNDLOAD',
475 application => 'FND',
476 description => NULL,
477 start_time => NULL,
478 sub_request => FALSE,
479 argument1 => 'UPLOAD',
480 argument2 =>
481 l_config_file||'fvreimrd.lct',
482 argument3 =>
483 l_data_file||'fvreimrd.ldt');
484
485 IF l_req_id = 0 THEN
486 errbuf := fnd_message.get ;
487 retcode := -1 ;
488 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
489 l_module_name||'.message1',errbuf);
490 ROLLBACK;
491 RAISE fnd_api.g_exc_error ;
492 END IF;
493 commit;
494 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
495 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message1',
496 'Load Reimbursement Activity Definitions process successfully requested');
497 END IF;
498 END load_reimb_act_definitions;
499
500 ----------------------------------------------------------------------------
501
502 --GTAS. To load data into GTAS attributes form
503 PROCEDURE GTAS_ATTRIBUTES (errbuf OUT NOCOPY varchar2,
504 retcode OUT NOCOPY varchar2,
505 p_sob_id IN NUMBER,
506 del_existing_data IN VARCHAR2) is
507 l_module_name VARCHAR2(200) := g_module_name || 'GTAS_ATTRIBUTES';
508 v_message VARCHAR2(500);
509
510 BEGIN
511
512 IF del_existing_data = 'Y' THEN
513 fv_utility.log_mesg('Deleting GTAS Attributes setup data!');
514 BEGIN
515 IF p_sob_id IS NOT NULL THEN
516 fv_utility.log_mesg
517 ('Deleting GTAS Attributes setup data for sob id: '||p_sob_id);
518 DELETE FROM FV_GTAS_ATTRIBUTES
519 WHERE set_of_books_id = p_sob_id;
520 ELSE
521 fv_utility.log_mesg
522 ('Deleting GTAS Attributes setup data for all set of books.');
523 DELETE FROM FV_GTAS_ATTRIBUTES;
524 END IF;
525
526 fv_utility.log_mesg('Deleted from FV_GTAS_ATTRIBUTES');
527 EXCEPTION WHEN OTHERS THEN
528 fv_utility.log_mesg
529 ('When others error when deleting GTAS Attributes setup data: '
530 ||SQLERRM);
531 END;
532 END IF;
533
534 l_req_id := fnd_request.submit_request
535 (program => 'FNDLOAD',
536 application => 'FND',
537 description => NULL,
538 start_time => NULL,
539 sub_request => FALSE,
540 argument1 => 'UPLOAD',
541 argument2 => l_config_file||'fvgtattb.lct',
542 argument3 => l_data_file||'fvgtattb.ldt');
543
544 IF l_req_id = 0 THEN
545 errbuf := fnd_message.get ;
546 retcode := -1 ;
547 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
548 RAISE fnd_api.g_exc_error ;
549 ROLLBACK;
550 RETURN;
551 ELSE
552 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553 v_message := 'GTAS attributes table setup program successfully requested';
554 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
555 l_module_name||'.message2',v_message);
556 END IF;
557 END IF;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 errbuf := substr(SQLERRM,1,225);
562 retcode := -1;
563 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
564 l_module_name||'.final_exception',errbuf);
565 END;
566 ----------------------------------------------------------------------------
567
568 END fv_setup_pkg;
569