[Home] [Help]
PACKAGE BODY: APPS.FV_UTILITY
Source
1 PACKAGE BODY FV_UTILITY AS
2 --$Header: FVXUTL1B.pls 120.25.12020000.3 2013/02/13 14:52:06 snama ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100) ;
5 g_current_level NUMBER ;
6
7 TYPE typ_flex_acct IS TABLE OF varchar2(150) INDEX BY BINARY_INTEGER;
8
9 g_flex_acct typ_flex_acct ;
10 l_flex_acct typ_flex_acct ;
11 g_l_index NUMBER := 0;
12
13 PROCEDURE message
14 (
15 p_level IN NUMBER DEFAULT NULL,
16 p_module IN VARCHAR2 DEFAULT NULL,
17 p_pop IN BOOLEAN DEFAULT FALSE
18 ) IS
19 l_level NUMBER ;
20 l_module VARCHAR2(2000) ;
21 BEGIN
22 IF p_level IS NULL THEN
23 l_level := fnd_log.LEVEL_ERROR ;
24 ELSE
25 l_level := p_level;
26 END IF;
27
28 IF p_module IS NULL THEN
29 l_module := 'fv.plsql.';
30 ELSE
31 l_module := p_module;
32 END IF;
33
34 IF (l_level >= g_current_level) THEN
35 fnd_log.message (l_level, l_module, p_pop);
36 END IF;
37 END;
38
39 PROCEDURE message
40 (
41 p_module IN VARCHAR2 DEFAULT NULL,
42 p_level IN NUMBER DEFAULT NULL,
43 p_pop IN BOOLEAN DEFAULT FALSE
44 ) IS
45 l_level NUMBER ;
46 l_module VARCHAR2(2000) ;
47 BEGIN
48
49 IF p_level IS NULL THEN
50 l_level := fnd_log.LEVEL_ERROR ;
51 ELSE
52 l_level := p_level;
53 END IF;
54
55 IF p_module IS NULL THEN
56 l_module := 'fv.plsql.';
57 ELSE
58 l_module := p_module;
59 END IF;
60
61 IF (l_level >= g_current_level) THEN
62 fnd_log.message (l_level, l_module, p_pop);
63 END IF;
64 END;
65
66 PROCEDURE log_mesg
67 (
68 p_level IN NUMBER,
69 p_module IN VARCHAR2,
70 p_message IN VARCHAR2
71 ) IS
72 BEGIN
73 IF (p_level >= g_current_level) THEN
74 fnd_log.string (p_level, p_module, p_message);
75 END IF;
76 END;
77
78 PROCEDURE log_mesg
79 (
80 p_message IN VARCHAR2,
81 p_module IN VARCHAR2 DEFAULT NULL,
82 p_level IN NUMBER DEFAULT NULL
83 ) IS
84
85 l_level NUMBER ;
86 l_module VARCHAR2(2000) ;
87 BEGIN
88
89 IF p_level IS NULL THEN
90 l_level := fnd_log.LEVEL_STATEMENT;
91 ELSE
92 l_level := p_level;
93 END IF;
94
95 IF p_module IS NULL THEN
96 l_module := 'fv.plsql.';
97 ELSE
98 l_module := p_module;
99 END IF;
100
101 log_mesg (l_level, l_module, p_message);
102 END;
103
104 PROCEDURE debug_mesg
105 (
106 p_level IN NUMBER,
107 p_module IN VARCHAR2,
108 p_message IN VARCHAR2
109 ) IS
110 BEGIN
111 IF p_level >= fnd_log.LEVEL_STATEMENT THEN
112 fnd_log.string (p_level, p_module, p_message);
113 END IF;
114 END;
115
116
117 PROCEDURE debug_mesg
118 (
119 p_message IN VARCHAR2,
120 p_module IN VARCHAR2 DEFAULT NULL,
121 p_level IN NUMBER DEFAULT NULL
122 ) IS
123 l_level NUMBER ;
124 l_module VARCHAR2(2000);
125
126
127 BEGIN
128 IF p_level IS NULL THEN
129 l_level := fnd_log.LEVEL_STATEMENT;
130 ELSE
131 l_level := p_level;
132 END IF;
133
134 IF p_module IS NULL THEN
135 l_module := 'fv.plsql.';
136 ELSE
137 l_module := p_module;
138 END IF;
139 IF (p_level >= g_current_level) THEN
140 debug_mesg (l_level, l_module, p_message);
141 END IF;
142 END;
143
144
145 -- Time Stamp Function - returns date and time
146 function TIME_STAMP return varchar2
147 IS
148 l_module_name VARCHAR2(200) ;
149 l_errbuf VARCHAR2(1024);
150 l_time varchar2(25);
151 BEGIN
152 l_module_name := g_module_name || 'TIME_STAMP';
153
154 SELECT to_char(SYSDATE, 'MM/DD/YYYY HH:MM:SS')
155 INTO l_time
156 FROM dual;
157
158 RETURN (l_time);
159 EXCEPTION
160 WHEN OTHERS THEN
161 l_errbuf := SQLERRM;
162 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
163 RAISE;
164
165 END;
166
167 ----------------------------------------------------------------------------
168
169 PROCEDURE GET_LEDGER_INFO (p_org_id in number ,
170 p_ledger_id out nocopy varchar2,
171 p_coa_id out nocopy varchar2,
172 p_currency out nocopy varchar2,
173 p_status out nocopy varchar2) is
174
175 l_ledger_name gl_ledgers_public_v.name%type;
176
177 BEGIN
178
179 iF (p_org_id IS NOT NULL) THEN
180
181 mo_utils.get_ledger_info(p_org_id,p_ledger_id,l_ledger_name);
182
183 if (p_ledger_id is not null) then
184 select chart_of_accounts_id,currency_code into p_coa_id , p_currency
185 from gl_ledgers_public_v
186 where ledger_id = p_ledger_id;
187 p_status := 0;
188 End if;
189 else
190 p_status := 1;
191 End if;
192
193 EXCEPTION
194 when no_data_found then
195 p_status := 1;
196 when others then
197 p_status := 1;
198 End ;
199
200 -------------------------------------------------------------------------------------
201
202
203 -- Procedure used to retrieve FV context variable values.
204 -- User_id is current fnd_global.userid
205 -- resp_id is the current fnd_global.resp_id (responsibility_id)
206 -- Variable value should be
207 -- CHART_OF_ACCOUNTS_ID to obtain chart_of_accounts_id context variable,
208 -- ACCT_SEGMENT to obtain acct_segment name context variable,
209 -- BALANCE_SEGMENT to obtain balance_segment name context variable
210 -- Returned is the value for the context variable specified above.
211 -- Returned variable values are all varchar2.
212 -- Error_code is a boolean which will be FALSE if NO errors are found and
213 -- TRUE if errors are raised during processing. Error_message will only
214 -- contain an error message if error_code is TRUE.
215 --
216 PROCEDURE get_context(user_id IN number,
217 resp_id IN number,
218 variable_type IN varchar2,
219 variable_value OUT NOCOPY varchar2,
220 error_code OUT NOCOPY boolean,
221 error_message OUT NOCOPY varchar2) IS
222 l_module_name VARCHAR2(200) ;
223 x_appl_id number;
224 no_data_exception EXCEPTION;
225 BEGIN
226 l_module_name := g_module_name || 'get_context';
227
228
229 error_code := FALSE;
230
231 fnd_profile.get('RESP_APPL_ID',x_appl_id);
232
233 -- initialize the FV Context
234 fnd_global.apps_initialize(user_id,resp_id,8901);
235
236
237 -- retrieving the context variables for the specified type
238 variable_value := sys_context('FV_CONTEXT',variable_type);
239
240 fnd_global.apps_initialize(user_id, resp_id, x_appl_id);
241 IF variable_value is null THEN
242 raise no_data_exception;
243 END IF;
244
245 EXCEPTION
246 WHEN no_data_exception THEN
247 error_code := TRUE;
248 error_message := ('No data found for this FV Context Variable '||variable_type);
249 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception',error_message);
250
251 WHEN others THEN
252 error_code := TRUE;
253 error_message := 'Error in retrieving FV Context Variables for '||variable_type||' - '||sqlerrm;
254 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',error_message);
255
256 END get_context;
257
258 ---added GET REPORT INFO PROCEDURE
259 PROCEDURE GET_REPORT_INFO(
260 p_request_id IN NUMBER,
261 p_report_id OUT NOCOPY NUMBER,
262 p_report_set OUT NOCOPY VARCHAR2,
263 p_responsibility OUT NOCOPY VARCHAR2,
264 p_application OUT NOCOPY VARCHAR2,
265 p_request_time OUT NOCOPY DATE,
266 p_resub_interval OUT NOCOPY VARCHAR2,
267 p_run_time OUT NOCOPY DATE,
268 p_printer OUT NOCOPY VARCHAR2,
269 p_copies OUT NOCOPY NUMBER,
270 p_save_output OUT NOCOPY VARCHAR2 )
271
272 AS
273 l_module_name VARCHAR2(200) ;
274 l_errbuf VARCHAR2(1024);
275 v_report_id NUMBER(15);
276 v_responsibility VARCHAR2(240);
277 v_application VARCHAR2(240);
278 v_request_time DATE;
279 v_resub_interval VARCHAR2(100);
280 v_run_time DATE;
281 v_printer VARCHAR2(30);
282 v_copies NUMBER(15);
283 v_so_flag VARCHAR2(1);
284 v_save_output VARCHAR2(10);
285 v_parent_id NUMBER(15);
286 v_request_type VARCHAR2(1);
287 v_description VARCHAR2(100);
288
289 CURSOR c_get_info IS
290 SELECT fcr.concurrent_program_id,
291 fcr.parent_request_id,
292 fr.responsibility_name,
293 fa.description,
294 fcr.requested_start_date,
295 TO_CHAR(fcr.RESUBMIT_INTERVAL)||' '||fcr.RESUBMIT_INTERVAL_UNIT_CODE,
296 fcr.actual_start_date,
297 fcr.printer,
298 fcr.number_of_copies,
299 fcr.save_output_flag
300 FROM FND_CONCURRENT_REQUESTS FCR,
301 FND_APPLICATION_VL FA,
302 FND_RESPONSIBILITY_VL FR
303 WHERE fcr.responsibility_id = fr.responsibility_id
304 AND fcr.program_application_id = fa.application_id
305 and fcr.request_id = p_request_id;
306
307 CURSOR c_get_rs (cp_parent_id fnd_concurrent_requests.parent_request_id%TYPE) IS
308 SELECT parent_request_id,
309 request_type, description
310 FROM fnd_concurrent_requests
311 WHERE request_id = cp_parent_id;
312
313
314 BEGIN
315
316 l_module_name := g_module_name || 'GET_REPORT_INFO';
317 OPEN c_get_info;
318
319 FETCH c_get_info
320 INTO v_report_id,
321 v_parent_id,
322 v_responsibility,
323 v_application,
324 v_request_time,
325 v_resub_interval,
326 v_run_time,
327 v_printer,
328 v_copies,
329 v_so_flag;
330 CLOSE c_get_info;
331
332
333 IF
334 v_so_flag = 'Y'
335 THEN
336 v_save_output := 'YES';
337 ELSE
338 v_save_output := 'NO';
339
340 END IF;
341
342 v_description := '';
343 v_request_type := '';
344
345 IF v_parent_id > 0
346 THEN
347 OPEN c_get_rs (v_parent_id);
348 FETCH c_get_rs
349 INTO v_parent_id,v_request_type,v_description;
350 CLOSE c_get_rs;
351
352 IF v_request_type = 'S'
353 THEN
354 OPEN c_get_rs (v_parent_id);
355 FETCH c_get_rs
356 INTO v_parent_id,v_request_type,v_description;
357 CLOSE c_get_rs;
358 END IF;
359
360 IF v_request_type = 'M'
361 THEN
362 p_report_set := v_description;
363 END IF;
364 END IF;
365
366 p_report_id := v_report_id;
367 p_responsibility := v_responsibility;
368 p_application := v_application;
369 p_request_time := v_request_time;
370 p_resub_interval := v_resub_interval;
371 p_run_time := v_run_time;
372 p_printer := v_printer;
373 p_copies := v_copies;
374 p_save_output := v_save_output;
375
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 l_errbuf := SQLERRM;
380 Fnd_Message.Set_Name('FV','FV_DC_GENERAL');
381 FND_MESSAGE.SET_TOKEN('MSG','UNHANDLED EXCEPTION IN GETTING REPORT INFORMATION');
382 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
383 MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception');
384
385 App_Exception.Raise_Exception;
386
387 END GET_REPORT_INFO;
388
389 --
390 -- This procedure should be called to determine the Organization Name for a
391 -- NON-Multiorg Database only. If an error occurs error_code will be TRUE
392 -- and error_message will contain the error message. Please check in
393 -- the calling process.
394 --
395 PROCEDURE GET_ORG_INFO(v_set_of_books_id IN NUMBER,
396 v_organization_name OUT NOCOPY VARCHAR2,
397 error_code OUT NOCOPY BOOLEAN,
398 error_message OUT NOCOPY VARCHAR2) IS
399
400 l_module_name VARCHAR2(200) ;
401 BEGIN
402
403 l_module_name := g_module_name || 'GET_ORG_INFO';
404
405
406
407 select substr(legal_entity_name,1,60)
408 into v_organization_name
409 from GL_LEDGER_LE_V
410 where ledger_id = v_set_of_books_id
411 and rownum = 1
412 order by legal_entity_name;
413
414 error_code := FALSE;
415 error_message := null;
416
417 EXCEPTION
418 when others then
419 error_code := TRUE;
420 error_message := 'Error in FV_UTILTIY.GET_ORG_INFO -'||sqlerrm;
421 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',error_message);
422
423 END get_org_info;
424
425 Procedure gl_get_first_period(tset_of_books_id IN NUMBER,
426 tperiod_name IN VARCHAR2,
427 tfirst_period OUT NOCOPY VARCHAR2,
428 errbuf OUT NOCOPY VARCHAR2)
429 IS
430 l_module_name VARCHAR2(200) ;
431
432 BEGIN
433
434 l_module_name := g_module_name || 'gl_get_first_period';
435
436
437 SELECT a.period_name
438 INTO tfirst_period
439 FROM gl_period_statuses a, gl_period_statuses b
440 WHERE a.application_id = 101
441 AND b.application_id = 101
442 AND a.ledger_id = tset_of_books_id
443 AND b.ledger_id = tset_of_books_id
444 AND a.period_type = b.period_type
445 AND a.period_year = b.period_year
446 AND b.period_name = tperiod_name
447 AND a.period_num =
448 (SELECT min(c.period_num)
449 FROM gl_period_statuses c
450 WHERE c.application_id = 101
451 AND c.ledger_id = tset_of_books_id
452 AND c.period_year = a.period_year
453 AND c.period_type = a.period_type
454 GROUP BY c.period_year);
455
456 EXCEPTION
457
458 WHEN NO_DATA_FOUND THEN
459
460 errbuf := gl_message.get_message('GL_PLL_INVALID_FIRST_PERIOD', 'Y',
461 'PERIOD', tperiod_name,
462 'SOBID', tset_of_books_id);
463 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception',errbuf);
464
465 WHEN OTHERS THEN
466
467 errbuf := SQLERRM;
468 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
469
470 END;
471
472 -------------------------------------------------------------------------------
473 PROCEDURE get_segment_col_names(chart_of_accounts_id IN NUMBER,
474 acct_seg_name OUT NOCOPY VARCHAR2,
475 balance_seg_name OUT NOCOPY VARCHAR2,
476 error_code OUT NOCOPY BOOLEAN,
477 error_message OUT NOCOPY VARCHAR2) IS
478 l_module_name VARCHAR2(200) := g_module_name || 'get_segment_col_names';
479 l_errbuf VARCHAR2(1024);
480
481 num_boolean BOOLEAN;
482 apps_id NUMBER := 101;
483 flex_code VARCHAR2(25) := 'GL#';
484 flex_num NUMBER;
485 invalid_acct_segment_error EXCEPTION;
486 invalid_bal_segment_error EXCEPTION;
487
488 BEGIN
489
490 error_code := FALSE;
491 error_message := null;
492
493 flex_num := chart_of_accounts_id;
494
495 num_boolean := FND_FLEX_APIS.GET_SEGMENT_COLUMN(apps_id,flex_code,flex_num,
496 'GL_ACCOUNT',acct_seg_name);
497
498 IF(num_boolean) THEN
499 null;
500 ELSE
501 raise invalid_acct_segment_error;
502 END IF;
503
504 acct_seg_name := upper(acct_seg_name);
505
506 num_boolean := FND_FLEX_APIS.GET_SEGMENT_COLUMN(apps_id,flex_code,flex_num,
507 'GL_BALANCING',balance_seg_name);
508
509 IF(num_boolean) THEN
510 null;
511 ELSE
512 raise invalid_bal_segment_error;
513 END IF;
514
515 balance_seg_name := upper(balance_seg_name);
516
517 EXCEPTION
518 WHEN invalid_acct_segment_error THEN
519 l_errbuf := SQLERRM;
520 error_code := TRUE;
521 error_message := 'Error in FV_UTILTIY.GET_SEGMENT_COL_NAMES - '||l_errbuf;
522 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception1',l_errbuf);
523 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception1','Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: Cannot read Account Segment Information');
524 RAISE_APPLICATION_ERROR(-20002,'Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: Cannot read Account Segment Information');
525
526 WHEN invalid_bal_segment_error THEN
527 l_errbuf := SQLERRM;
528 error_code := TRUE;
529 error_message := 'Error in FV_UTILTIY.GET_SEGMENT_COL_NAMES - '||l_errbuf;
530 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception1',l_errbuf);
531 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception1','Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: Cannot read Balancing Segment Information');
532 RAISE_APPLICATION_ERROR(-20002,'Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: Cannot read Account Segment Information');
533
534 WHEN others THEN
535 l_errbuf := SQLERRM;
536 error_code := TRUE;
537 error_message := 'Error in FV_UTILTIY.GET_SEGMENT_COL_NAMES - '||l_errbuf;
538 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception2',l_errbuf);
539 RAISE_APPLICATION_ERROR(-20003,'Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: '||l_errbuf);
540
541 END get_segment_col_names;
542
543 PROCEDURE calc_child_flex_value (p_flex_value_set_id IN NUMBER, p_parent_flex_value IN VARCHAR2)
544 IS
545
546 CURSOR c_child_flex_value IS
547 SELECT flex_value, summary_flag, flex_value_set_id, parent_flex_value
548 FROM fnd_flex_value_children_v
549 WHERE (flex_value_set_id = p_flex_value_set_id) AND (parent_flex_value = p_parent_flex_value)
550 ORDER BY flex_value;
551
552 l_index NUMBER;
553 l_flag BOOLEAN;
554 l_module_name VARCHAR2(200) := g_module_name || 'calc_child_flex_value';
555 l_errbuf VARCHAR2(1024);
556
557 BEGIN
558
559 l_flag := FALSE;
560
561 FOR l_child_flex_value in c_child_flex_Value
562 LOOP
563 IF (l_child_flex_value.summary_flag = 'N') THEN
564 IF NOT (g_l_index = 0 ) THEN
565 FOR l_index IN g_flex_acct.first..g_flex_acct.last
566 LOOP
567 IF (l_child_flex_value.flex_value = g_flex_acct(l_index)) THEN
568 l_flag := TRUE;
569 exit;
570 END IF;
571 END LOOP;
572 END IF;
573 IF NOT l_flag THEN
574 g_flex_acct(g_l_index):= l_child_flex_value.flex_value;
575 g_l_index := g_l_index + 1;
576 END IF;
577 ELSIF (l_child_flex_value.summary_flag = 'Y') THEN
578 calc_child_flex_value(p_flex_value_set_id, l_child_flex_value.flex_value);
579 END IF;
580 END LOOP;
581 EXCEPTION
582 WHEN OTHERS THEN
583 l_errbuf := SQLERRM;
584 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',l_errbuf);
585 END calc_child_flex_value;
586
587 FUNCTION calc_concat_accts(p_flex_value IN VARCHAR2,
588 p_coa_id IN NUMBER)
589 RETURN VARCHAR2 IS
590
591 CURSOR c_flex_value IS
592 SELECT flexvalue.flex_value, flexvalue.summary_flag, fndidflex.flex_value_set_id
593 FROM fnd_segment_attribute_values fndseg,
594 fnd_id_flex_segments_vl fndidflex,
595 fnd_flex_values_vl flexvalue
596 WHERE fndseg.id_flex_num = p_coa_id
597 AND fndseg.segment_attribute_type = 'GL_ACCOUNT'
598 AND fndseg.id_flex_code = 'GL#'
599 AND fndseg.attribute_value = 'Y'
600 AND fndseg.application_column_name = fndidflex.application_column_name
601 AND fndidflex.id_flex_num = p_coa_id
602 AND fndidflex.id_flex_code = 'GL#'
603 AND fndidflex.flex_value_set_id = flexvalue.flex_value_set_id
604 AND flexvalue.enabled_flag = 'Y'
605 and flexvalue.flex_value = p_flex_value;
606 l_index NUMBER;
607 l_str VARCHAR2(4000);
608 l_module_name VARCHAR2(200) := g_module_name || 'calc_concat_accts';
609 l_errbuf VARCHAR2(1024);
610 l_flex_value c_flex_value%ROWTYPE;
611
612 BEGIN
613 g_flex_acct := l_flex_acct;
614 g_l_index := 0;
615 OPEN c_flex_value;
616 FETCH c_flex_value INTO l_flex_value;
617 CLOSE c_flex_value;
618 IF (l_flex_value.summary_flag = 'N') THEN
619 RETURN l_flex_value.flex_value;
620 END IF;
621
622 calc_child_flex_value(l_flex_value.flex_value_set_id,l_flex_value.flex_value);
623 FOR l_index IN g_flex_acct.first..g_flex_acct.last
624 LOOP
625 IF l_str IS NULL THEN
626 l_str := g_flex_acct(l_index);
627 ELSE
628 l_str := l_str || ', ' || g_flex_acct(l_index);
629 END IF;
630 END LOOP;
631 RETURN l_str;
632
633 EXCEPTION
634 WHEN OTHERS THEN
635 l_errbuf := SQLERRM;
636 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',l_errbuf);
637 RETURN NULL;
638 END calc_concat_accts;
639 ----------------------------------------------------------------------------
640
641 PROCEDURE Get_Period_Year(period_from VARCHAR2,
642 period_to VARCHAR2,
643 sob_id NUMBER,
644 period_start_date OUT NOCOPY DATE,
645 period_end_date OUT NOCOPY DATE,
646 period_year OUT NOCOPY NUMBER,
647 errbuf OUT NOCOPY VARCHAR2,
648 retcode OUT NOCOPY NUMBER) IS
649 l_module_name VARCHAR2(200) := g_module_name || 'Get_Period_Year';
650 vl_period_set_name Gl_Periods.period_set_name%TYPE;
651 BEGIN
652 BEGIN
653 SELECT period_set_name
654 INTO vl_period_set_name
655 FROM Gl_Sets_Of_Books
656 WHERE set_of_books_id = sob_id;
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 retcode := 2;
660 errbuf := 'Period Set name not found for set of books '||to_char(sob_id);
661 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found',errbuf);
662 RETURN ;
663 WHEN OTHERS THEN
664 retcode := SQLCODE ;
665 errbuf := SQLERRM ||
666 ' -- Error in Get_Period_Year procedure,while getting the period set name.' ;
667 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1',errbuf);
668 RETURN ;
669 END;
670 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
671 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD SET NAME IS '||VL_PERIOD_SET_NAME);
672 END IF;
673
674 BEGIN
675 SELECT period_year
676 INTO period_year
677 FROM Gl_Periods
678 WHERE period_set_name = vl_period_set_name
679 AND period_name = period_from;
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 retcode := 2;
683 errbuf := 'Period Year not found for the set of books '||to_char(sob_id) ||
684 ' and the period set name '||vl_period_set_name;
685 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found1',errbuf);
686 RETURN ;
687
688 WHEN OTHERS THEN
689 retcode := SQLCODE ;
690 errbuf := SQLERRM ||
691 ' -- Error in Get_Period_Year procedure,while getting the period year.' ;
692 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception2',errbuf);
693 RETURN ;
694 END;
695 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
696 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD YEAR IS '||TO_CHAR(PERIOD_YEAR));
697 END IF;
698
699 BEGIN /* From Period Start Date */
700 SELECT start_date
701 INTO period_start_date
702 FROM Gl_Period_Statuses
703 WHERE ledger_id = sob_id
704 AND application_id = 101
705 AND period_year = period_year
706 AND period_name = period_from
707 AND adjustment_period_flag = 'N';
708 EXCEPTION
709 WHEN NO_DATA_FOUND THEN
710 retcode := 2;
711 errbuf := 'Start Date not defined for the period name '||period_from;
712 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found2',errbuf);
713 RETURN ;
714
715 WHEN OTHERS THEN
716 retcode := SQLCODE ;
717 errbuf := SQLERRM ||
718 ' -- Error in Get_Period_Year procedure,while getting the start date for the from period '||period_from ;
719 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception3',errbuf);
720 RETURN ;
721 END; /* From Period Start Date */
722 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
723 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD START DATE IS '||TO_CHAR(PERIOD_START_DATE));
724 END IF;
725
726 BEGIN /* To Period End Date */
727 SELECT end_date
728 INTO period_end_date
729 FROM Gl_Period_Statuses
730 WHERE ledger_id = sob_id
731 AND application_id = 101
732 AND period_year = period_year
733 AND period_name = period_to
734 AND adjustment_period_flag = 'N';
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737 retcode := 2;
738 errbuf := 'End Date not defined for the period name '||period_to;
739 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found3',errbuf);
740 RETURN ;
741
742 WHEN OTHERS THEN
743 retcode := SQLCODE ;
744 errbuf := SQLERRM ||
745 ' -- Error in Get_Period_Year procedure,while getting the end date for the to period '||period_to ;
746 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception4',errbuf);
747 RETURN ;
748 END; /* To Period End Date */
749 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
750 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD END DATE IS '||TO_CHAR(PERIOD_END_DATE));
751 END IF;
752
753 -- Setting up the retcode
754 retcode := 0;
755 EXCEPTION
756 WHEN OTHERS THEN
757 retcode := SQLCODE;
758 errbuf := SQLERRM;
759 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
760 RETURN;
761
762 END Get_Period_Year;
763
764 FUNCTION tin
765 (
766 p_vendor_type_lookup_code IN VARCHAR2,
767 p_org_type_lookup_code IN VARCHAR2,
768 p_num_1099 IN VARCHAR2,
769 p_individual_1099 IN VARCHAR2,
770 p_employee_id IN NUMBER
771 )
772 RETURN VARCHAR2
773 IS
774 l_tin VARCHAR2(100);
775 l_module_name VARCHAR2(200);
776 l_errbuf VARCHAR2(1024);
777 BEGIN
778 l_module_name := g_module_name || 'tin';
779 IF (p_vendor_type_lookup_code = 'EMPLOYEE') THEN
780 BEGIN
781 SELECT papf.national_identifier
782 INTO l_tin
783 FROM per_all_people_f papf
784 WHERE person_id = p_employee_id
785 AND ROWNUM < 2;
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 l_tin := NVL(p_num_1099, p_individual_1099);
789 END;
790 ELSIF(p_vendor_type_lookup_code = 'CONTRACTOR') THEN
791 IF (p_org_type_lookup_code IN ('INDIVIDUAL', 'FOREIGN INDIVIDUAL', 'PARTNERSHIP', 'FOREIGN PARTNERSHIP')) THEN
792 l_tin := p_individual_1099;
793 ELSE
794 l_tin := p_num_1099;
795 END IF;
796 ELSE
797 l_tin := NVL(p_num_1099, p_individual_1099);
798 END IF;
799 RETURN l_tin;
800 EXCEPTION
801 WHEN OTHERS THEN
802 l_errbuf := SQLERRM;
803 log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception',l_errbuf);
804 RAISE;
805 END;
806
807 ---------------------------------------------------------------
808 PROCEDURE get_accrual_account
809 (
810 p_wf_item_type IN VARCHAR2,
811 p_wf_item_key IN VARCHAR2,
812 p_new_accrual_ccid OUT NOCOPY NUMBER
813 ) IS
814
815 l_module VARCHAR2(200) := g_module_name||'get_accrual_account.';
816 l_default_accrual_acct_id NUMBER;
817 l_result BOOLEAN;
818 l_chart_of_accounts_id NUMBER;
819 l_account_segment_num NUMBER;
820 l_no_of_segments NUMBER;
821 l_charge_ccid_segs fnd_flex_ext.segmentarray;
822 l_def_accrual_ccid_segs fnd_flex_ext.segmentarray;
823 l_org_id NUMBER;
824 l_ledger_id NUMBER;
825 l_ledger_name gl_ledgers.name%TYPE;
826 l_charge_account_ccid NUMBER;
827
828
829
830 BEGIN
831
832 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'BEGIN');
833 log_mesg(fnd_log.LEVEL_STATEMENT,l_module,'p_wf_item_type: '||p_wf_item_type);
834 log_mesg(fnd_log.LEVEL_STATEMENT,l_module,'p_wf_item_key: '||p_wf_item_key);
835
836 -- get the default accrual_account_ccid from po_system_parameters
837 BEGIN
838
839 SELECT accrued_code_combination_id
840 INTO l_default_accrual_acct_id
841 FROM po_system_parameters;
842
843 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'l_default_accrual_acct_id: '
844 ||l_default_accrual_acct_id);
845
846 EXCEPTION
847 WHEN NO_DATA_FOUND THEN
848 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'No default accrual account found in po system parameters!');
849 WHEN OTHERS THEN
850 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'When others error: '||sqlerrm);
851 END;
852
853 -- get the charge account ccid from workflow
854 l_charge_account_ccid := wf_engine.GetItemAttrNumber
855 (
856 itemtype => p_wf_item_type,
857 itemkey => p_wf_item_key,
858 aname => 'CODE_COMBINATION_ID'
859 );
860
861 IF l_charge_account_ccid IS NULL THEN
862 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Charge account ccid is null!');
863 RETURN;
864 ELSE
865 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Charge account ccid is: '||l_charge_account_ccid);
866 END IF;
867
868
869 -- get the chard of accounts id from workflow
870 l_chart_of_accounts_id := wf_engine.GetItemAttrNumber
871 (
872 itemtype => p_wf_item_type,
873 itemkey => p_wf_item_key,
874 aname => 'CHART_OF_ACCOUNTS_ID'
875 );
876
877 IF l_chart_of_accounts_id IS NULL THEN
878 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Chart of accounts id is null!');
879 RETURN;
880 ELSE
881 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Chart of accounts id: '||l_chart_of_accounts_id);
882 END IF;
883
884 l_result := fnd_flex_apis.get_qualifier_segnum
885 (
886 appl_id => 101,
887 key_flex_code => 'GL#',
888 structure_number => l_chart_of_accounts_id,
889 flex_qual_name => 'GL_ACCOUNT',
890 segment_number => l_account_segment_num
891 );
892 IF l_result THEN
893 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Accounting segment number: '||l_account_segment_num);
894 ELSE
895 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Could not get Accounting segment from coa id: '
896 ||l_chart_of_accounts_id);
897 RETURN;
898 END IF;
899
900 --Get the segments from the po charge ccid
901 l_result := fnd_flex_ext.get_segments
902 (
903 application_short_name => 'SQLGL',
904 key_flex_code => 'GL#',
905 structure_number => l_chart_of_accounts_id,
906 combination_id => l_charge_account_ccid,
907 n_segments => l_no_of_segments,
908 segments => l_charge_ccid_segs
909 );
910
911 IF NOT l_result THEN
912 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Could not get segments for po charge ccid: '||l_charge_account_ccid);
913 RETURN;
914 END IF;
915
916 --Get the segments from the default accrual ccid
917 l_result := fnd_flex_ext.get_segments
918 (
919 application_short_name => 'SQLGL',
920 key_flex_code => 'GL#',
921 structure_number => l_chart_of_accounts_id,
922 combination_id => l_default_accrual_acct_id,
923 n_segments => l_no_of_segments,
924 segments => l_def_accrual_ccid_segs
925 );
926
927 IF NOT l_result THEN
928 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Could not get segments for default accrual ccid: '||l_default_accrual_acct_id);
929 RETURN;
930 END IF;
931
932 --Set the accounting segment of default accrual to the charge account
933 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Replacing charge account segment with that from default account');
934 l_charge_ccid_segs(l_account_segment_num) := l_def_accrual_ccid_segs(l_account_segment_num);
935
936 -- validate this segment combination and get ccid
937 -- flex API will create combination if it does not exist
938 l_result := fnd_flex_ext.get_combination_id
939 (
940 application_short_name => 'SQLGL',
941 key_flex_code => 'GL#',
942 structure_number => l_chart_of_accounts_id,
943 validation_date => sysdate,
944 n_segments => l_no_of_segments,
945 segments => l_charge_ccid_segs,
946 combination_id => p_new_accrual_ccid
947 );
948
949 IF l_result THEN
950 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'New accrual ccid: '||p_new_accrual_ccid);
951 ELSE
952 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Could not create new default charge account!');
953 RETURN;
954 END IF;
955
956 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'END');
957 EXCEPTION WHEN OTHERS THEN
958 log_mesg(fnd_log.LEVEL_UNEXPECTED, l_module, 'When others error: '||SQLERRM);
959 END get_accrual_account;
960
961 ---------------------------------------------------------------
962 /*-------------------------------------------------------------
963 *Procedure to delete orphan BC events.
964 *Called from psa_ap_bc_pvt.delete_events.
965 *Returns 'S' for success and 'E' for error.
966 *
967 *-----------------------------------------------------------*/
968 PROCEDURE delete_fv_bc_orphan
969 ( p_ledger_id IN NUMBER,
970 p_start_date IN DATE,
971 p_end_date IN DATE,
972 p_status OUT NOCOPY VARCHAR2
973 ) IS
974
975 l_module VARCHAR2(200) := g_module_name||'delete_fv_bc_orphan.';
976 l_event_count NUMBER;
977 TYPE Event_tab_type IS TABLE OF XLA_EVENTS_INT_GT%rowtype
978 INDEX BY BINARY_INTEGER;
979 l_events_Tab Event_tab_type;
980
981 CURSOR c_get_unprocessed_fv_events IS
982 SELECT xte.transaction_number, xla.application_id, xla.event_id,
983 xla.event_type_code,
984 xla.event_date,
985 xla.event_status_code,
986 xla.process_status_code,
987 xte.entity_id,
988 xte.legal_entity_id,
989 xte.entity_code,
990 xte.source_id_int_1,
991 xte.source_id_int_2,
992 xte.source_id_int_3,
993 xte.source_id_int_4,
994 xte.source_id_char_1
995 FROM xla_events xla,
996 xla_transaction_entities xte
997 WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
998 AND xla.application_id = 8901
999 AND xla.event_date BETWEEN p_start_date AND p_end_date
1000 AND xla.event_status_code in ('U','I')
1001 AND xla.process_status_code <> 'P'
1002 AND xla.entity_id = xte.entity_id
1003 AND xla.application_id = xte.application_id
1004 AND xte.ledger_id = p_ledger_id;
1005
1006 BEGIN
1007 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'BEGIN');
1008 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Parameters: ');
1009 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'p_ledger_id: '||p_ledger_id);
1010 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'p_start_date: '||p_start_date);
1011 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'p_end_date: '||p_end_date);
1012
1013 xla_security_pkg.set_security_context(8901);
1014
1015 DELETE FROM XLA_EVENTS_INT_GT;
1016 log_mesg(fnd_log.LEVEL_STATEMENT, l_module,
1017 '# of rows deleted from xla_events_int_gt: '|| SQL%ROWCOUNT );
1018 fnd_file.put_line(fnd_file.log,
1019 '-------------------------------------------------------');
1020 fnd_file.put_line(fnd_file.log,'Deleting Federal events, if any.');
1021 l_event_count := 0;
1022
1023 FOR rec_events IN c_get_unprocessed_fv_events
1024 LOOP
1025 l_event_count := l_event_count+1;
1026 l_events_tab(l_event_count).entity_id := rec_events.entity_id;
1027 l_events_tab(l_event_count).application_id := 8901;
1028 l_events_tab(l_event_count).ledger_id := p_ledger_id;
1029 l_events_tab(l_event_count).legal_entity_id := rec_events.legal_entity_id;
1030 l_events_tab(l_event_count).entity_code := rec_events.entity_code;
1031 l_events_tab(l_event_count).event_id := rec_events.event_id;
1032 l_events_tab(l_event_count).transaction_number := rec_events.transaction_number;
1033 l_events_tab(l_event_count).event_status_code := rec_events.event_status_code;
1034 l_events_tab(l_event_count).process_status_code := rec_events.process_status_code;
1035 l_events_tab(l_event_count).source_id_int_1 := rec_events.source_id_int_1;
1036 END LOOP;
1037
1038 IF l_event_count > 0 THEN
1039 FORALL i IN 1..l_event_count
1040 INSERT INTO XLA_EVENTS_INT_GT
1041 VALUES l_events_tab(i) ;
1042 fnd_file.put_line(fnd_file.log,' # of rows inserted into xla_events_int_gt table: ' || l_event_count);
1043 fnd_file.put_line(fnd_file.log,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
1044
1045 XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 8901);
1046
1047 fnd_file.put_line(fnd_file.log,'After Deletion of Federal Unprocessed Events');
1048 fnd_file.put_line(fnd_file.log,'The following Federal BC unprocessed/Error events have been deleted');
1049 fnd_file.put_line(fnd_file.log ,'Event ID Event Status Code Process Status Code');
1050 fnd_file.put_line(fnd_file.log ,'--------- ----------------- -------------------');
1051
1052 FOR i IN 1..l_event_count
1053 LOOP
1054 fnd_file.put_line(fnd_file.log ,l_events_tab(i).event_id||' '||
1055 l_events_tab(i).event_status_code ||' '||
1056 l_events_tab(i).process_status_code);
1057
1058
1059
1060 --Update the event id of the BE row to null if the row exists.
1061 --If the row does not exist, it means that the user has deleted it from
1062 --the form.
1063 log_mesg(fnd_log.LEVEL_STATEMENT, l_module,
1064 'Updating event id: '||l_events_tab(i).event_id||' to NULL for related distributions.');
1065 UPDATE fv_be_trx_dtls
1066 SET event_id = NULL
1067 WHERE event_id = l_events_tab(i).event_id;
1068
1069 log_mesg(fnd_log.LEVEL_STATEMENT, l_module,
1070 '# distributions in fv_be_trx_dtls that have been updated to NULL: '||SQL%ROWCOUNT);
1071
1072 END LOOP;
1073
1074 ELSE
1075 fnd_file.put_line(fnd_file.log,'**** No Federal events found to delete ****');
1076 END IF;
1077 p_status := 'S';
1078
1079 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'END');
1080 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
1081 EXCEPTION WHEN OTHERS THEN
1082 log_mesg(fnd_log.LEVEL_UNEXPECTED, l_module, 'When others error: '||SQLERRM);
1083 p_status := 'E';
1084 END delete_fv_bc_orphan;
1085 ----------------------------------------------------------------------------------------
1086 PROCEDURE fv_cleanup_xla_gt IS
1087
1088 l_module VARCHAR2(200) := g_module_name||'fv_cleanup_xla_gt.';
1089
1090 BEGIN
1091
1092 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, 'Begin - Cleaning up xla GT Tables');
1093 DELETE FROM XLA_AE_HEADERS_GT;
1094 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '1 XLA_AE_HEADERS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1095 DELETE FROM XLA_AE_LINES_GT;
1096 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '2 XLA_AE_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1097 DELETE FROM XLA_VALIDATION_HDRS_GT;
1098 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '3 XLA_VALIDATION_HDRS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1099 DELETE FROM XLA_VALIDATION_LINES_GT;
1100 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '4 XLA_VALIDATION_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1101 DELETE FROM XLA_BAL_CTRL_CTRBS_GT;
1102 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '5 XLA_BAL_CTRL_CTRBS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1103 DELETE FROM XLA_BAL_PERIOD_STATS_GT;
1104 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '6 XLA_BAL_PERIOD_STATS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1105 DELETE FROM XLA_BAL_RECREATE_GT;
1106 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '7 XLA_BAL_RECREATE_GT: Deleted Row count :'||SQL%ROWCOUNT);
1107 DELETE FROM XLA_BAL_ANACRI_LINES_GT;
1108 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '8 XLA_BAL_ANACRI_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1109 DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;
1110 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '9 XLA_BAL_ANACRI_CTRBS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1111 DELETE FROM XLA_BAL_SYNCHRONIZE_GT;
1112 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '10 XLA_BAL_SYNCHRONIZE_GT: Deleted Row count :'||SQL%ROWCOUNT);
1113 DELETE FROM XLA_BAL_STATUSES_GT;
1114 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '11 XLA_BAL_STATUSES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1115 DELETE FROM XLA_BAL_CTRL_LINES_GT;
1116 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '12 XLA_BAL_CTRL_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1117 DELETE FROM XLA_EVENTS_GT;
1118 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '13 XLA_EVENTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1119 DELETE FROM XLA_EVT_CLASS_SOURCES_GT;
1120 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '14 XLA_EVT_CLASS_SOURCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1121 DELETE FROM XLA_EVT_CLASS_ORDERS_GT;
1122 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '15 XLA_EVT_CLASS_ORDERS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1123 DELETE FROM XLA_TAB_ERRORS_GT;
1124 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '16 XLA_TAB_ERRORS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1125 DELETE FROM XLA_SEQ_JE_HEADERS_GT;
1126 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '17 XLA_SEQ_JE_HEADERS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1127 DELETE FROM XLA_TAB_NEW_CCIDS_GT;
1128 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '18 XLA_TAB_NEW_CCIDS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1129 DELETE FROM XLA_EXTRACT_OBJECTS_GT;
1130 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '19 XLA_EXTRACT_OBJECTS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1131 DELETE FROM XLA_REFERENCE_OBJECTS_GT;
1132 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '20 XLA_REFERENCE_OBJECTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1133 DELETE FROM XLA_TRANSACTION_ACCTS_GT;
1134 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '21 XLA_TRANSACTION_ACCTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1135 DELETE FROM XLA_UPG_LINE_CRITERIA_GT;
1136 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '22 XLA_UPG_LINE_CRITERIA_GT : Deleted Row count :'||SQL%ROWCOUNT);
1137 DELETE FROM XLA_TRIAL_BALANCES_GT;
1138 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '23 XLA_TRIAL_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1139 DELETE FROM XLA_ACCT_PROG_EVENTS_GT;
1140 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '24 XLA_ACCT_PROG_EVENTS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1141 DELETE FROM XLA_ACCT_PROG_DOCS_GT;
1142 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '25 XLA_ACCT_PROG_DOCS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1143 DELETE FROM XLA_MERGE_SEG_MAPS_GT;
1144 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '26 XLA_MERGE_SEG_MAPS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1145 DELETE FROM XLA_EVENTS_INT_GT;
1146 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '27 XLA_EVENTS_INT_GT: Deleted Row count :'||SQL%ROWCOUNT);
1147 DELETE FROM XLA_REPORT_BALANCES_GT;
1148 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '28 XLA_REPORT_BALANCES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1149 DELETE FROM XLA_TB_BALANCES_GT;
1150 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '29 XLA_TB_BALANCES_GT: Deleted Row count :'||SQL%ROWCOUNT);
1151 DELETE FROM XLA_BAL_AC_CTRBS_GT;
1152 log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '30 XLA_BAL_AC_CTRBS_GT: Deleted Row count :'||SQL%ROWCOUNT);
1153 log_mesg(fnd_log.LEVEL_STATEMENT, l_module,'End - Cleaning up xla GT Tables');
1154
1155 END fv_cleanup_xla_gt;
1156
1157 ----------------------------------------------------------------------------------------
1158
1159 -- Public API for CLM
1160 PROCEDURE get_fund_information
1161 (
1162 p_ledger_id in number,
1163 p_ccid in number,
1164 x_fiscal_year out nocopy number,
1165 x_fund_cancellation_date out nocopy date,
1166 x_fund_start_date out nocopy date,
1167 x_fund_end_date out nocopy date,
1168 x_non_annual_fund_flag out nocopy varchar2,
1169 x_return_status out nocopy varchar2,
1170 x_return_msg out nocopy varchar2
1171 )
1172 IS
1173 BEGIN
1174 fv_sla_utl_processing_pkg.get_fund_information
1175 (
1176 p_ledger_id => p_ledger_id,
1177 p_ccid => p_ccid,
1178 p_fiscal_year => x_fiscal_year,
1179 p_fund_cancellation_date => x_fund_cancellation_date,
1180 p_fund_start_date => x_fund_start_date,
1181 p_fund_end_date => x_fund_end_date,
1182 p_non_annual_fund_flag => x_non_annual_fund_flag,
1183 p_error_code => x_return_status,
1184 p_error_desc => x_return_msg
1185 );
1186 END;
1187
1188
1189 BEGIN
1190 g_module_name := 'FV_UTILITY.';
1191 g_current_level := fnd_log.g_current_runtime_level;
1192
1193 END; -- package body