[Home] [Help]
PACKAGE BODY: APPS.PSA_GVTMB
Source
1 PACKAGE BODY psa_gvtmb AS
2 /* $Header: psagvtmb.pls 120.1 2009/05/15 15:03:37 gaprasad noship $ */
3 g_module_name VARCHAR2(100) ;
4 g_FAILURE NUMBER;
5 g_SUCCESS NUMBER;
6 g_WARNING NUMBER;
7
8 PROCEDURE initialize_global_variables
9 IS
10 BEGIN
11 g_module_name := 'psa.plsql.psagvtmb.';
12 g_FAILURE := -1;
13 g_SUCCESS := 0;
14 g_WARNING := -2;
15 END;
16
17 PROCEDURE log
18 (
19 p_message IN VARCHAR2
20 )
21 IS
22 BEGIN
23 fnd_file.put_line (fnd_file.log, p_message);
24 END;
25
26 PROCEDURE error
27 (
28 p_module IN VARCHAR2,
29 p_location IN VARCHAR2,
30 p_message IN VARCHAR2
31 )
32 IS
33 BEGIN
34 fnd_file.put_line (fnd_file.log, 'ERROR :'||p_module||'.'||p_location||':'||p_message);
35 END;
36
37
38 PROCEDURE check_and_insert
39 (
40 p_program_name IN VARCHAR2,
41 p_errbuf OUT NOCOPY VARCHAR2,
42 p_retcode OUT NOCOPY NUMBER
43 )
44 IS
45 l_module_name VARCHAR2(200);
46 l_location VARCHAR2(200);
47 l_gl_request_group VARCHAR2(100):= 'GL Concurrent Program Group';
48 l_gl_application VARCHAR2(10) := 'SQLGL';
49 BEGIN
50 l_module_name := g_module_name || 'check_and_insert';
51 p_retcode := g_SUCCESS;
52 p_errbuf := NULL;
53 log ('ENTER *** '||l_module_name||' ***');
54
55 IF (fnd_program.program_exists
56 (
57 program => p_program_name,
58 application => l_gl_application
59 )) THEN
60 BEGIN
61 log ('Program '||p_program_name||' exists in fnd_concurrent_programs');
62
63 fnd_program.enable_program
64 (
65 short_name => p_program_name,
66 application => l_gl_application,
67 enabled => 'Y'
68 );
69
70 IF (fnd_program.program_in_group
71 (
72 program_short_name => p_program_name,
73 program_application => l_gl_application,
74 request_group => l_gl_request_group,
75 group_application => l_gl_application
76 ) = FALSE) THEN
77 log ('Program '||p_program_name||' does not exist in request_group');
78 log ('Inserting Program '||p_program_name||' into request_group');
79 fnd_program.add_to_group
80 (
81 program_short_name => p_program_name,
82 program_application => l_gl_application,
83 request_group => l_gl_request_group,
84 group_application => l_gl_application
85 );
86 ELSE
87 log ('Program '||p_program_name||' already exists in request_group');
88 END IF;
89 EXCEPTION
90 WHEN OTHERS THEN
91 p_retcode := g_FAILURE;
92 p_errbuf := SQLERRM;
93 l_location := 'add_program_to_group';
94 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
95 END;
96 ELSE
97 log ('Program '||p_program_name||' does not exist');
98 END IF;
99
100 log ('LEAVE *** '||l_module_name||' ***');
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 p_retcode := g_FAILURE;
105 p_errbuf := SQLERRM;
106 l_location := 'final_exception';
107 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
108 END;
109
110 PROCEDURE insert_into_request_group
111 (
112 p_errbuf OUT NOCOPY VARCHAR2,
113 p_retcode OUT NOCOPY NUMBER
114 )
115 IS
116 l_module_name VARCHAR2(200);
117 l_location VARCHAR2(200);
118 l_program_name VARCHAR2(100);
119 BEGIN
120 l_module_name := g_module_name || 'insert_into_request_group';
121 p_retcode := g_SUCCESS;
122 p_errbuf := NULL;
123 log ('ENTER *** '||l_module_name||' ***');
124
125 fnd_set.set_session_mode('seed_data');
126
127 IF (p_retcode = g_SUCCESS) THEN
128 l_program_name := 'GLGDOCDE';
129 log ('Calling check_and_insert with '||l_program_name);
130 check_and_insert
131 (
132 p_program_name => l_program_name,
133 p_errbuf => p_errbuf,
134 p_retcode => p_retcode
135 );
136 END IF;
137
138 IF (p_retcode = g_SUCCESS) THEN
139 l_program_name := 'GLGENCRE';
140 log ('Calling check_and_insert with '||l_program_name);
141 check_and_insert
142 (
143 p_program_name => l_program_name,
144 p_errbuf => p_errbuf,
145 p_retcode => p_retcode
146 );
147 END IF;
148
149 IF (p_retcode = g_SUCCESS) THEN
150 l_program_name := 'GLGEOT';
151 log ('Calling check_and_insert with '||l_program_name);
152 check_and_insert
153 (
154 p_program_name => l_program_name,
155 p_errbuf => p_errbuf,
156 p_retcode => p_retcode
157 );
158 END IF;
159
160 IF (p_retcode = g_SUCCESS) THEN
161 l_program_name := 'GLGFUN';
162 log ('Calling check_and_insert with '||l_program_name);
163 check_and_insert
164 (
165 p_program_name => l_program_name,
166 p_errbuf => p_errbuf,
167 p_retcode => p_retcode
168 );
169 END IF;
170
171 IF (p_retcode = g_SUCCESS) THEN
172 l_program_name := 'GLXRLTCL';
173 log ('Calling check_and_insert with '||l_program_name);
174 check_and_insert
175 (
176 p_program_name => l_program_name,
177 p_errbuf => p_errbuf,
178 p_retcode => p_retcode
179 );
180 END IF;
181
182 IF (p_retcode = g_SUCCESS) THEN
183 l_program_name := 'GLBCMP';
184 log ('Calling check_and_insert with '||l_program_name);
185 check_and_insert
186 (
187 p_program_name => l_program_name,
188 p_errbuf => p_errbuf,
189 p_retcode => p_retcode
190 );
191 END IF;
192
193 /*
194 Not yet ready to support this program
195 IF (p_retcode = g_SUCCESS) THEN
196 l_program_name := 'GLXFMA';
197 log ('Calling check_and_insert with '||l_program_name);
198 check_and_insert
199 (
200 p_program_name => l_program_name,
201 p_errbuf => p_errbuf,
202 p_retcode => p_retcode
203 );
204 END IF;
205 */
206
207 IF (p_retcode = g_SUCCESS) THEN
208 l_program_name := 'GLGHIST';
209 log ('Calling check_and_insert with '||l_program_name);
210 check_and_insert
211 (
212 p_program_name => l_program_name,
213 p_errbuf => p_errbuf,
214 p_retcode => p_retcode
215 );
216 END IF;
217
218 IF (p_retcode = g_SUCCESS) THEN
219 l_program_name := 'GLGPREP';
220 log ('Calling check_and_insert with '||l_program_name);
221 check_and_insert
222 (
223 p_program_name => l_program_name,
224 p_errbuf => p_errbuf,
225 p_retcode => p_retcode
226 );
227 END IF;
228 log ('LEAVE *** '||l_module_name||' ***');
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 p_retcode := g_FAILURE;
234 p_errbuf := SQLERRM;
235 l_location := 'final_exception';
236 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
237 END;
238
239 PROCEDURE glgv05
240 (
241 p_errbuf OUT NOCOPY VARCHAR2,
242 p_retcode OUT NOCOPY NUMBER
243 )
244 IS
245 l_module_name VARCHAR2(200);
246 l_location VARCHAR2(200);
247 BEGIN
248 l_module_name := g_module_name || 'glgv05';
249 p_retcode := g_SUCCESS;
250 p_errbuf := NULL;
251 log ('ENTER *** '||l_module_name||' ***');
252
253 IF (p_retcode = g_SUCCESS) THEN
254 BEGIN
255 log ('Updating columns in fnd_columns for Reporting Attributes');
256 UPDATE fnd_columns
257 SET flexfield_usage_code = 'K'
258 WHERE table_id = (SELECT table_id
259 FROM fnd_tables
260 WHERE application_id = 101
261 AND table_name = 'GL_CODE_COMBINATIONS')
262 AND column_name LIKE 'SEGMENT_ATTRIBUTE%';
263 log ('Updated '||SQL%ROWCOUNT||' rows');
264 EXCEPTION
265 WHEN OTHERS THEN
266 p_retcode := g_FAILURE;
267 p_errbuf := SQLERRM;
268 l_location := 'update_fnd_columns';
269 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
270 END;
271 END IF;
272
273 IF (p_retcode = g_SUCCESS) THEN
274 BEGIN
275 log ('Updating fnd_flex_value_sets');
276 UPDATE fnd_flex_value_sets
277 SET format_type = 'C'
278 WHERE flex_value_set_name LIKE 'Reporting Attribute:%'
279 AND format_type = 'V';
280 log ('Updated '||SQL%ROWCOUNT||' rows');
281 EXCEPTION
282 WHEN OTHERS THEN
283 p_retcode := g_FAILURE;
284 p_errbuf := SQLERRM;
285 l_location := 'update_fnd_flex_value_sets';
286 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
287 END;
288 END IF;
289 log ('LEAVE *** '||l_module_name||' ***');
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 p_retcode := g_FAILURE;
294 p_errbuf := SQLERRM;
295 l_location := 'final_exception';
296 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
297 END;
298
299 PROCEDURE enable_lookups
300 (
301 p_errbuf OUT NOCOPY VARCHAR2,
302 p_retcode OUT NOCOPY NUMBER
303 )
304 IS
305 l_module_name VARCHAR2(200);
306 l_location VARCHAR2(200);
307 BEGIN
308 l_module_name := g_module_name || 'enable_gl_lookups';
309 p_retcode := g_SUCCESS;
310 p_errbuf := NULL;
311 log ('ENTER *** '||l_module_name||' ***');
312
313 log ('Updating Lookups');
314 UPDATE gl_lookups
315 SET enabled_flag = 'Y'
316 WHERE lookup_type = 'ACCOUNT TYPE'
317 AND lookup_code IN ('C', 'D');
318
319 log ('Updated '||SQL%ROWCOUNT||' rows');
320 log ('LEAVE *** '||l_module_name||' ***');
321 UPDATE fnd_lookups
322 SET enabled_flag = 'Y'
323 WHERE lookup_type = 'ACCOUNT_TYPE'
324 AND lookup_code IN ('C', 'D');
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 p_retcode := g_FAILURE;
329 p_errbuf := SQLERRM;
330 l_location := 'final_exception';
331 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
332 END;
333
334 PROCEDURE ins_fnd_lookup_values
335 (
336 p_lookup_type IN VARCHAR2,
337 p_lookup_code IN VARCHAR2,
338 p_lookup_meaning IN VARCHAR2,
339 p_lookup_desc IN VARCHAR2,
340 p_errbuf OUT NOCOPY VARCHAR2,
341 p_retcode OUT NOCOPY NUMBER
342 )
343 IS
344 l_module_name VARCHAR2(200);
345 l_location VARCHAR2(200);
346 BEGIN
347 l_module_name := g_module_name || 'ins_fnd_lookup_values';
348 p_retcode := g_SUCCESS;
349 p_errbuf := NULL;
350 log ('ENTER *** '||l_module_name||' ***');
351
352 log ('Inserting '||p_lookup_type||':'||p_lookup_code);
353
354 INSERT INTO fnd_lookup_values
355 (
356 lookup_type,
357 language,
358 lookup_code,
359 meaning,
360 description,
361 enabled_flag,
362 start_date_active,
363 end_date_active,
364 created_by,
365 creation_date,
366 last_updated_by,
367 last_update_date,
368 last_update_login,
369 source_lang,
370 security_group_id,
371 view_application_id
372 )
373 SELECT p_lookup_type,
374 'US',
375 p_lookup_code,
376 p_lookup_meaning,
377 p_lookup_desc,
378 'Y',
379 NULL,
380 NULL,
381 0,
382 sysdate,
383 0,
384 sysdate,
385 0,
386 'US',
387 0,
388 0
389 FROM sys.dual
390 WHERE NOT EXISTS (SELECT 1
391 FROM fnd_lookup_values
392 WHERE lookup_type = p_lookup_type
393 AND language = 'US'
394 AND lookup_code = p_lookup_code
395 AND security_group_id = 0
396 AND view_application_id = 0);
397
398 log ('Inserted '||SQL%ROWCOUNT||' rows');
399 log ('LEAVE *** '||l_module_name||' ***');
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 p_retcode := g_FAILURE;
404 p_errbuf := SQLERRM;
405 l_location := 'final_exception';
406 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
407 END;
408
409 PROCEDURE insert_fnd_lookups
410 (
411 p_errbuf OUT NOCOPY VARCHAR2,
412 p_retcode OUT NOCOPY NUMBER
413 )
414 IS
415 l_module_name VARCHAR2(200);
416 l_location VARCHAR2(200);
417 BEGIN
418 l_module_name := g_module_name || 'insert_fnd_lookups';
419 p_retcode := g_SUCCESS;
420 p_errbuf := NULL;
421 log ('ENTER *** '||l_module_name||' ***');
422
423
424
425 log ('Inserting lookup value Fund');
426 ins_fnd_lookup_values
427 (
428 p_lookup_type => 'IND_COMPANY',
429 p_lookup_code => 'G',
430 p_lookup_meaning => 'Fund',
431 p_lookup_desc => 'Fund',
432 p_errbuf => p_errbuf,
433 p_retcode => p_retcode
434 );
435
436 IF (p_retcode = g_SUCCESS) THEN
437 log ('Inserting lookup value Revenue');
438 ins_fnd_lookup_values
439 (
440 p_lookup_type => 'IND_SALES',
441 p_lookup_code => 'G',
442 p_lookup_meaning => 'Revenue',
443 p_lookup_desc => 'Revenue',
444 p_errbuf => p_errbuf,
445 p_retcode => p_retcode
446 );
447 END IF;
448
449 IF (p_retcode = g_SUCCESS) THEN
450 log ('Inserting lookup value Fund Balance');
451 ins_fnd_lookup_values
452 (
453 p_lookup_type => 'IND_EQUITY',
454 p_lookup_code => 'G',
455 p_lookup_meaning => 'Fund Balance',
456 p_lookup_desc => 'Fund Balance',
457 p_errbuf => p_errbuf,
458 p_retcode => p_retcode
459 );
460 END IF;
461
462 IF (p_retcode = g_SUCCESS) THEN
463 log ('Inserting lookup value Net Revenue');
464 ins_fnd_lookup_values
465 (
466 p_lookup_type => 'IND_EARNING',
467 p_lookup_code => 'G',
468 p_lookup_meaning => 'Net Revenue',
469 p_lookup_desc => 'Net Revenue',
470 p_errbuf => p_errbuf,
471 p_retcode => p_retcode
472 );
473 END IF;
474
475 IF (p_retcode = g_SUCCESS) THEN
476 log ('Inserting lookup value Order');
477 ins_fnd_lookup_values
478 (
479 p_lookup_type => 'IND_SALES_ORDER',
480 p_lookup_code => 'G',
481 p_lookup_meaning => 'Order',
482 p_lookup_desc => 'Order',
483 p_errbuf => p_errbuf,
484 p_retcode => p_retcode
485 );
486 END IF;
487
488 IF (p_retcode = g_SUCCESS) THEN
489 log ('Inserting lookup value Agent');
490 ins_fnd_lookup_values
491 (
492 p_lookup_type => 'IND_SALES_REP',
493 p_lookup_code => 'G',
494 p_lookup_meaning => 'Agent',
495 p_lookup_desc => 'Agent',
496 p_errbuf => p_errbuf,
497 p_retcode => p_retcode
498 );
499 END IF;
500
501 IF (p_retcode = g_SUCCESS) THEN
502 log ('Inserting lookup value Agent');
503 ins_fnd_lookup_values
504 (
505 p_lookup_type => 'IND_SALES_TERRITORY',
506 p_lookup_code => 'G',
507 p_lookup_meaning => 'Territory',
508 p_lookup_desc => 'Territory',
509 p_errbuf => p_errbuf,
510 p_retcode => p_retcode
511 );
512 END IF;
513
514 IF (p_retcode = g_SUCCESS) THEN
515 log ('Inserting lookup value Agent');
516 ins_fnd_lookup_values
517 (
518 p_lookup_type => 'IND_SALES_CREDIT',
519 p_lookup_code => 'G',
520 p_lookup_meaning => 'Credit',
521 p_lookup_desc => 'Credit',
522 p_errbuf => p_errbuf,
523 p_retcode => p_retcode
524 );
525 END IF;
526
527 fnd_lookup_values_pkg.add_language;
528 log ('LEAVE *** '||l_module_name||' ***');
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 p_retcode := g_FAILURE;
533 p_errbuf := SQLERRM;
534 l_location := 'final_exception';
535 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
536 END;
537
538 PROCEDURE set_industry_profile
539 (
540 p_errbuf OUT NOCOPY VARCHAR2,
541 p_retcode OUT NOCOPY NUMBER
542 )
543 IS
544 l_module_name VARCHAR2(200);
545 l_location VARCHAR2(200);
546 l_industry_value VARCHAR2(1);
547 l_result BOOLEAN;
548 BEGIN
549 l_module_name := g_module_name || 'set_industry_profile';
550 p_retcode := g_SUCCESS;
551 p_errbuf := NULL;
552 log ('ENTER *** '||l_module_name||' ***');
553
554 log ('Getting Industry Value');
555
556 SELECT industry
557 INTO l_industry_value
558 FROM fnd_product_installations
559 WHERE application_id=101;
560
561 log ('Industry Value = '||l_industry_value);
562
563 IF ( l_industry_value = 'G' ) THEN
564 l_result := fnd_profile.save('INDUSTRY', 'G', 'SITE');
565 ELSE
566 l_result := fnd_profile.save('INDUSTRY', 'C', 'SITE');
567 END IF;
568 log ('LEAVE *** '||l_module_name||' ***');
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 p_retcode := g_FAILURE;
573 p_errbuf := SQLERRM;
574 l_location := 'final_exception';
575 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
576 END;
577
578 PROCEDURE set_industry_prod_installation
579 (
580 p_errbuf OUT NOCOPY VARCHAR2,
581 p_retcode OUT NOCOPY NUMBER
582 )
583 IS
584 l_module_name VARCHAR2(200);
585 l_location VARCHAR2(200);
586 BEGIN
587 l_module_name := g_module_name || 'set_industry_prod_installation';
588 p_retcode := g_SUCCESS;
589 p_errbuf := NULL;
590 log ('ENTER *** '||l_module_name||' ***');
591
592 UPDATE fnd_product_installations a
593 SET a.industry = 'G'
594 WHERE a.application_id in (SELECT application_id
595 FROM fnd_application
596 WHERE application_short_name IN ('FND',
597 'SYSADMIN',
598 'SQLGL',
599 'SQLAP',
600 'PO',
601 'AR'));
602 log ('LEAVE *** '||l_module_name||' ***');
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 p_retcode := g_FAILURE;
607 p_errbuf := SQLERRM;
608 l_location := 'final_exception';
609 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
610 END;
611
612 PROCEDURE main
613 (
614 p_errbuf OUT NOCOPY VARCHAR2,
615 p_retcode OUT NOCOPY NUMBER
616 )
617 IS
618 l_module_name VARCHAR2(200);
619 l_location VARCHAR2(200);
620 BEGIN
621 l_module_name := g_module_name || 'main';
622 p_retcode := g_SUCCESS;
623 p_errbuf := NULL;
624 log ('ENTER *** '||l_module_name||' ***');
625
626
627 IF (p_retcode = g_SUCCESS) THEN
628 log('Calling insert_into_request_group');
629 insert_into_request_group (p_errbuf, p_retcode);
630 END IF;
631
632 IF (p_retcode = g_SUCCESS) THEN
633 log('Calling glgv05');
634 glgv05 (p_errbuf, p_retcode);
635 END IF;
636
637 IF (p_retcode = g_SUCCESS) THEN
638 log('Calling enable_fnd_lookups');
639 insert_fnd_lookups (p_errbuf, p_retcode);
640 END IF;
641
642 IF (p_retcode = g_SUCCESS) THEN
643 log('Calling enable_lookups');
644 enable_lookups (p_errbuf, p_retcode);
645 END IF;
646
647 IF (p_retcode = g_SUCCESS) THEN
648 log('Calling set_industry_prod_installation');
649 set_industry_prod_installation (p_errbuf, p_retcode);
650 END IF;
651
652 IF (p_retcode = g_SUCCESS) THEN
653 log('Calling set_industry_profile');
654 set_industry_profile (p_errbuf, p_retcode);
655 END IF;
656
657 log ('LEAVE *** '||l_module_name||' ***');
658 EXCEPTION
659 WHEN OTHERS THEN
660 p_retcode := g_FAILURE;
661 p_errbuf := SQLERRM;
662 l_location := 'final_exception';
663 error (l_module_name, l_location, SQLCODE||':'||p_errbuf);
664 END;
665
666 BEGIN
667 initialize_global_variables;
668 END psa_gvtmb;