[Home] [Help]
PACKAGE BODY: APPS.FV_UTILITY
Source
1 PACKAGE BODY FV_UTILITY AS
2 --$Header: FVXUTL1B.pls 120.16.12000000.3 2007/05/15 17:24:46 sasukuma 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 FND_FILE.PUT_LINE(FND_FILE.LOG, p_message);
77 END;
78
79 PROCEDURE log_mesg
80 (
81 p_message IN VARCHAR2,
82 p_module IN VARCHAR2 DEFAULT NULL,
83 p_level IN NUMBER DEFAULT NULL
84 ) IS
85
86 l_level NUMBER ;
87 l_module VARCHAR2(2000) ;
88 BEGIN
89
90 IF p_level IS NULL THEN
91 l_level := fnd_log.LEVEL_STATEMENT;
92 ELSE
93 l_level := p_level;
94 END IF;
95
96 IF p_module IS NULL THEN
97 l_module := 'fv.plsql.';
98 ELSE
99 l_module := p_module;
100 END IF;
101
102 log_mesg (l_level, l_module, p_message);
103 END;
104
105 PROCEDURE debug_mesg
106 (
107 p_level IN NUMBER,
108 p_module IN VARCHAR2,
109 p_message IN VARCHAR2
110 ) IS
111 BEGIN
112 IF p_level >= fnd_log.LEVEL_STATEMENT THEN
113 fnd_log.string (p_level, p_module, p_message);
114 END IF;
115 END;
116
117
118 PROCEDURE debug_mesg
119 (
120 p_message IN VARCHAR2,
121 p_module IN VARCHAR2 DEFAULT NULL,
122 p_level IN NUMBER DEFAULT NULL
123 ) IS
124 l_level NUMBER ;
125 l_module VARCHAR2(2000);
126
127
128 BEGIN
129 IF p_level IS NULL THEN
130 l_level := fnd_log.LEVEL_STATEMENT;
131 ELSE
132 l_level := p_level;
133 END IF;
134
135 IF p_module IS NULL THEN
136 l_module := 'fv.plsql.';
137 ELSE
138 l_module := p_module;
139 END IF;
140 IF (p_level >= g_current_level) THEN
141 debug_mesg (l_level, l_module, p_message);
142 END IF;
143 END;
144
145
146 -- Time Stamp Function - returns date and time
147 function TIME_STAMP return varchar2
148 IS
149 l_module_name VARCHAR2(200) ;
150 l_errbuf VARCHAR2(1024);
151 l_time varchar2(25);
152 BEGIN
153 l_module_name := g_module_name || 'TIME_STAMP';
154
155 SELECT to_char(SYSDATE, 'MM/DD/YYYY HH:MM:SS')
156 INTO l_time
157 FROM dual;
158
159 RETURN (l_time);
160 EXCEPTION
161 WHEN OTHERS THEN
162 l_errbuf := SQLERRM;
163 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
164 RAISE;
165
166 END;
167
168 ----------------------------------------------------------------------------
169
170 PROCEDURE GET_LEDGER_INFO (p_org_id in number ,
171 p_ledger_id out nocopy varchar2,
172 p_coa_id out nocopy varchar2,
173 p_currency out nocopy varchar2,
174 p_status out nocopy varchar2) is
175
176 l_ledger_name gl_ledgers_public_v.name%type;
177
178 BEGIN
179
180 iF (p_org_id IS NOT NULL) THEN
181
182 mo_utils.get_ledger_info(p_org_id,p_ledger_id,l_ledger_name);
183
184 if (p_ledger_id is not null) then
185 select chart_of_accounts_id,currency_code into p_coa_id , p_currency
186 from gl_ledgers_public_v
187 where ledger_id = p_ledger_id;
188 p_status := 0;
189 End if;
190 else
191 p_status := 1;
192 End if;
193
194 EXCEPTION
195 when no_data_found then
196 p_status := 1;
197 when others then
198 p_status := 1;
199 End ;
200
201 -------------------------------------------------------------------------------------
202
203
204 -- Procedure used to retrieve FV context variable values.
205 -- User_id is current fnd_global.userid
206 -- resp_id is the current fnd_global.resp_id (responsibility_id)
207 -- Variable value should be
208 -- CHART_OF_ACCOUNTS_ID to obtain chart_of_accounts_id context variable,
209 -- ACCT_SEGMENT to obtain acct_segment name context variable,
210 -- BALANCE_SEGMENT to obtain balance_segment name context variable
211 -- Returned is the value for the context variable specified above.
212 -- Returned variable values are all varchar2.
213 -- Error_code is a boolean which will be FALSE if NO errors are found and
214 -- TRUE if errors are raised during processing. Error_message will only
215 -- contain an error message if error_code is TRUE.
216 --
217 PROCEDURE get_context(user_id IN number,
218 resp_id IN number,
219 variable_type IN varchar2,
220 variable_value OUT NOCOPY varchar2,
221 error_code OUT NOCOPY boolean,
222 error_message OUT NOCOPY varchar2) IS
223 l_module_name VARCHAR2(200) ;
224 x_appl_id number;
225 no_data_exception EXCEPTION;
226 BEGIN
227 l_module_name := g_module_name || 'get_context';
228
229
230 error_code := FALSE;
231
232 fnd_profile.get('RESP_APPL_ID',x_appl_id);
233
234 -- initialize the FV Context
235 fnd_global.apps_initialize(user_id,resp_id,8901);
236
237
238 -- retrieving the context variables for the specified type
239 variable_value := sys_context('FV_CONTEXT',variable_type);
240
241 fnd_global.apps_initialize(user_id, resp_id, x_appl_id);
242 IF variable_value is null THEN
243 raise no_data_exception;
244 END IF;
245
246 EXCEPTION
247 WHEN no_data_exception THEN
248 error_code := TRUE;
249 error_message := ('No data found for this FV Context Variable '||variable_type);
250 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception',error_message);
251
252 WHEN others THEN
253 error_code := TRUE;
254 error_message := 'Error in retrieving FV Context Variables for '||variable_type||' - '||sqlerrm;
255 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',error_message);
256
257 END get_context;
258
259 ---added GET REPORT INFO PROCEDURE
260 PROCEDURE GET_REPORT_INFO(
261 p_request_id IN NUMBER,
262 p_report_id OUT NOCOPY NUMBER,
263 p_report_set OUT NOCOPY VARCHAR2,
264 p_responsibility OUT NOCOPY VARCHAR2,
265 p_application OUT NOCOPY VARCHAR2,
266 p_request_time OUT NOCOPY DATE,
267 p_resub_interval OUT NOCOPY VARCHAR2,
268 p_run_time OUT NOCOPY DATE,
269 p_printer OUT NOCOPY VARCHAR2,
270 p_copies OUT NOCOPY NUMBER,
271 p_save_output OUT NOCOPY VARCHAR2 )
272
273 AS
274 l_module_name VARCHAR2(200) ;
275 l_errbuf VARCHAR2(1024);
276 v_report_id NUMBER(15);
277 v_responsibility VARCHAR2(240);
278 v_application VARCHAR2(240);
279 v_request_time DATE;
280 v_resub_interval VARCHAR2(100);
281 v_run_time DATE;
282 v_printer VARCHAR2(30);
283 v_copies NUMBER(15);
284 v_so_flag VARCHAR2(1);
285 v_save_output VARCHAR2(10);
286 v_parent_id NUMBER(15);
287 v_request_type VARCHAR2(1);
288 v_description VARCHAR2(100);
289
290 CURSOR c_get_info IS
291 SELECT fcr.concurrent_program_id,
292 fcr.parent_request_id,
293 fr.responsibility_name,
294 fa.description,
295 fcr.requested_start_date,
296 TO_CHAR(fcr.RESUBMIT_INTERVAL)||' '||fcr.RESUBMIT_INTERVAL_UNIT_CODE,
297 fcr.actual_start_date,
298 fcr.printer,
299 fcr.number_of_copies,
300 fcr.save_output_flag
301 FROM FND_CONCURRENT_REQUESTS FCR,
302 FND_APPLICATION_VL FA,
303 FND_RESPONSIBILITY_VL FR
304 WHERE fcr.responsibility_id = fr.responsibility_id
305 AND fcr.program_application_id = fa.application_id
306 and fcr.request_id = p_request_id;
307
308 CURSOR c_get_rs (cp_parent_id fnd_concurrent_requests.parent_request_id%TYPE) IS
309 SELECT parent_request_id,
310 request_type, description
311 FROM fnd_concurrent_requests
312 WHERE request_id = cp_parent_id;
313
314
315 BEGIN
316
317 l_module_name := g_module_name || 'GET_REPORT_INFO';
318 OPEN c_get_info;
319
320 FETCH c_get_info
321 INTO v_report_id,
322 v_parent_id,
323 v_responsibility,
324 v_application,
325 v_request_time,
326 v_resub_interval,
327 v_run_time,
328 v_printer,
329 v_copies,
330 v_so_flag;
331 CLOSE c_get_info;
332
333
334 IF
335 v_so_flag = 'Y'
336 THEN
337 v_save_output := 'YES';
338 ELSE
339 v_save_output := 'NO';
340
341 END IF;
342
343 v_description := '';
344 v_request_type := '';
345
346 IF v_parent_id > 0
347 THEN
348 OPEN c_get_rs (v_parent_id);
349 FETCH c_get_rs
350 INTO v_parent_id,v_request_type,v_description;
351 CLOSE c_get_rs;
352
353 IF v_request_type = 'S'
354 THEN
355 OPEN c_get_rs (v_parent_id);
356 FETCH c_get_rs
357 INTO v_parent_id,v_request_type,v_description;
358 CLOSE c_get_rs;
359 END IF;
360
361 IF v_request_type = 'M'
362 THEN
363 p_report_set := v_description;
364 END IF;
365 END IF;
366
367 p_report_id := v_report_id;
368 p_responsibility := v_responsibility;
369 p_application := v_application;
370 p_request_time := v_request_time;
371 p_resub_interval := v_resub_interval;
372 p_run_time := v_run_time;
373 p_printer := v_printer;
374 p_copies := v_copies;
375 p_save_output := v_save_output;
376
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 l_errbuf := SQLERRM;
381 Fnd_Message.Set_Name('FV','FV_DC_GENERAL');
382 FND_MESSAGE.SET_TOKEN('MSG','UNHANDLED EXCEPTION IN GETTING REPORT INFORMATION');
383 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
384 MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception');
385
386 App_Exception.Raise_Exception;
387
388 END GET_REPORT_INFO;
389
390 --
391 -- This procedure should be called to determine the Organization Name for a
392 -- NON-Multiorg Database only. If an error occurs error_code will be TRUE
393 -- and error_message will contain the error message. Please check in
394 -- the calling process.
395 --
396 PROCEDURE GET_ORG_INFO(v_set_of_books_id IN NUMBER,
397 v_organization_name OUT NOCOPY VARCHAR2,
398 error_code OUT NOCOPY BOOLEAN,
399 error_message OUT NOCOPY VARCHAR2) IS
400
401 l_module_name VARCHAR2(200) ;
402 BEGIN
403
404 l_module_name := g_module_name || 'GET_ORG_INFO';
405
406
407
408 select substr(legal_entity_name,1,60)
409 into v_organization_name
410 from GL_LEDGER_LE_V
411 where ledger_id = v_set_of_books_id
412 and rownum = 1
413 order by legal_entity_name;
414
415 error_code := FALSE;
416 error_message := null;
417
418 EXCEPTION
419 when others then
420 error_code := TRUE;
421 error_message := 'Error in FV_UTILTIY.GET_ORG_INFO -'||sqlerrm;
422 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',error_message);
423
424 END get_org_info;
425
426 Procedure gl_get_first_period(tset_of_books_id IN NUMBER,
427 tperiod_name IN VARCHAR2,
428 tfirst_period OUT NOCOPY VARCHAR2,
429 errbuf OUT NOCOPY VARCHAR2)
430 IS
431 l_module_name VARCHAR2(200) ;
432
433 BEGIN
434
435 l_module_name := g_module_name || 'gl_get_first_period';
436
437
438 SELECT a.period_name
439 INTO tfirst_period
440 FROM gl_period_statuses a, gl_period_statuses b
441 WHERE a.application_id = 101
442 AND b.application_id = 101
443 AND a.ledger_id = tset_of_books_id
444 AND b.ledger_id = tset_of_books_id
445 AND a.period_type = b.period_type
446 AND a.period_year = b.period_year
447 AND b.period_name = tperiod_name
448 AND a.period_num =
449 (SELECT min(c.period_num)
450 FROM gl_period_statuses c
451 WHERE c.application_id = 101
452 AND c.ledger_id = tset_of_books_id
453 AND c.period_year = a.period_year
454 AND c.period_type = a.period_type
455 GROUP BY c.period_year);
456
457 EXCEPTION
458
459 WHEN NO_DATA_FOUND THEN
460
461 errbuf := gl_message.get_message('GL_PLL_INVALID_FIRST_PERIOD', 'Y',
462 'PERIOD', tperiod_name,
463 'SOBID', tset_of_books_id);
464 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception',errbuf);
465
466 WHEN OTHERS THEN
467
468 errbuf := SQLERRM;
469 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
470
471 END;
472
473 -------------------------------------------------------------------------------
474 PROCEDURE get_segment_col_names(chart_of_accounts_id IN NUMBER,
475 acct_seg_name OUT NOCOPY VARCHAR2,
476 balance_seg_name OUT NOCOPY VARCHAR2,
477 error_code OUT NOCOPY BOOLEAN,
478 error_message OUT NOCOPY VARCHAR2) IS
479 l_module_name VARCHAR2(200) := g_module_name || 'get_segment_col_names';
480 l_errbuf VARCHAR2(1024);
481
482 num_boolean BOOLEAN;
483 apps_id NUMBER := 101;
484 flex_code VARCHAR2(25) := 'GL#';
485 flex_num NUMBER;
486 invalid_acct_segment_error EXCEPTION;
487 invalid_bal_segment_error EXCEPTION;
488
489 BEGIN
490
491 error_code := FALSE;
492 error_message := null;
493
494 flex_num := chart_of_accounts_id;
495
496 num_boolean := FND_FLEX_APIS.GET_SEGMENT_COLUMN(apps_id,flex_code,flex_num,
497 'GL_ACCOUNT',acct_seg_name);
498
499 IF(num_boolean) THEN
500 null;
501 ELSE
502 raise invalid_acct_segment_error;
503 END IF;
504
505 acct_seg_name := upper(acct_seg_name);
506
507 num_boolean := FND_FLEX_APIS.GET_SEGMENT_COLUMN(apps_id,flex_code,flex_num,
508 'GL_BALANCING',balance_seg_name);
509
510 IF(num_boolean) THEN
511 null;
512 ELSE
513 raise invalid_bal_segment_error;
514 END IF;
515
516 balance_seg_name := upper(balance_seg_name);
517
518 EXCEPTION
519 WHEN invalid_acct_segment_error THEN
520 l_errbuf := SQLERRM;
521 error_code := TRUE;
522 error_message := 'Error in FV_UTILTIY.GET_SEGMENT_COL_NAMES - '||l_errbuf;
523 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception1',l_errbuf);
524 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');
525 RAISE_APPLICATION_ERROR(-20002,'Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: Cannot read Account Segment Information');
526
527 WHEN invalid_bal_segment_error THEN
528 l_errbuf := SQLERRM;
529 error_code := TRUE;
530 error_message := 'Error in FV_UTILTIY.GET_SEGMENT_COL_NAMES - '||l_errbuf;
531 LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.final_exception1',l_errbuf);
532 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');
533 RAISE_APPLICATION_ERROR(-20002,'Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: Cannot read Account Segment Information');
534
535 WHEN others THEN
536 l_errbuf := SQLERRM;
537 error_code := TRUE;
538 error_message := 'Error in FV_UTILTIY.GET_SEGMENT_COL_NAMES - '||l_errbuf;
539 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception2',l_errbuf);
540 RAISE_APPLICATION_ERROR(-20003,'Error in FV_CONTEXT_PKG.GET_SEGMENT_COL_NAMES: '||l_errbuf);
541
542 END get_segment_col_names;
543
544 PROCEDURE calc_child_flex_value (p_flex_value_set_id IN NUMBER, p_parent_flex_value IN VARCHAR2)
545 IS
546
547 CURSOR c_child_flex_value IS
548 SELECT flex_value, summary_flag, flex_value_set_id, parent_flex_value
549 FROM fnd_flex_value_children_v
550 WHERE (flex_value_set_id = p_flex_value_set_id) AND (parent_flex_value = p_parent_flex_value)
551 ORDER BY flex_value;
552
553 l_index NUMBER;
554 l_flag BOOLEAN;
555 l_module_name VARCHAR2(200) := g_module_name || 'calc_child_flex_value';
556 l_errbuf VARCHAR2(1024);
557
558 BEGIN
559
560 l_flag := FALSE;
561
562 FOR l_child_flex_value in c_child_flex_Value
563 LOOP
564 IF (l_child_flex_value.summary_flag = 'N') THEN
565 IF NOT (g_l_index = 0 ) THEN
566 FOR l_index IN g_flex_acct.first..g_flex_acct.last
567 LOOP
568 IF (l_child_flex_value.flex_value = g_flex_acct(l_index)) THEN
569 l_flag := TRUE;
570 exit;
571 END IF;
572 END LOOP;
573 END IF;
574 IF NOT l_flag THEN
575 g_flex_acct(g_l_index):= l_child_flex_value.flex_value;
576 g_l_index := g_l_index + 1;
577 END IF;
578 ELSIF (l_child_flex_value.summary_flag = 'Y') THEN
579 calc_child_flex_value(p_flex_value_set_id, l_child_flex_value.flex_value);
580 END IF;
581 END LOOP;
582 EXCEPTION
583 WHEN OTHERS THEN
584 l_errbuf := SQLERRM;
585 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',l_errbuf);
586 END calc_child_flex_value;
587
588 FUNCTION calc_concat_accts(p_flex_value IN VARCHAR2,
589 p_coa_id IN NUMBER)
590 RETURN VARCHAR2 IS
591
592 CURSOR c_flex_value IS
593 SELECT flexvalue.flex_value, flexvalue.summary_flag, fndidflex.flex_value_set_id
594 FROM fnd_segment_attribute_values fndseg,
595 fnd_id_flex_segments_vl fndidflex,
596 fnd_flex_values_vl flexvalue
597 WHERE fndseg.id_flex_num = p_coa_id
598 AND fndseg.segment_attribute_type = 'GL_ACCOUNT'
599 AND fndseg.id_flex_code = 'GL#'
600 AND fndseg.attribute_value = 'Y'
601 AND fndseg.application_column_name = fndidflex.application_column_name
602 AND fndidflex.id_flex_num = p_coa_id
603 AND fndidflex.id_flex_code = 'GL#'
604 AND fndidflex.flex_value_set_id = flexvalue.flex_value_set_id
605 AND flexvalue.enabled_flag = 'Y'
606 and flexvalue.flex_value = p_flex_value;
607 l_index NUMBER;
608 l_str VARCHAR2(4000);
609 l_module_name VARCHAR2(200) := g_module_name || 'calc_concat_accts';
610 l_errbuf VARCHAR2(1024);
611 l_flex_value c_flex_value%ROWTYPE;
612
613 BEGIN
614 g_flex_acct := l_flex_acct;
615 g_l_index := 0;
616 OPEN c_flex_value;
617 FETCH c_flex_value INTO l_flex_value;
618 CLOSE c_flex_value;
619 IF (l_flex_value.summary_flag = 'N') THEN
620 RETURN l_flex_value.flex_value;
621 END IF;
622
623 calc_child_flex_value(l_flex_value.flex_value_set_id,l_flex_value.flex_value);
624 FOR l_index IN g_flex_acct.first..g_flex_acct.last
625 LOOP
626 IF l_str IS NULL THEN
627 l_str := g_flex_acct(l_index);
628 ELSE
629 l_str := l_str || ', ' || g_flex_acct(l_index);
630 END IF;
631 END LOOP;
632 RETURN l_str;
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 l_errbuf := SQLERRM;
637 LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',l_errbuf);
638 RETURN NULL;
639 END calc_concat_accts;
640 ----------------------------------------------------------------------------
641
642 PROCEDURE Get_Period_Year(period_from VARCHAR2,
643 period_to VARCHAR2,
644 sob_id NUMBER,
645 period_start_date OUT NOCOPY DATE,
646 period_end_date OUT NOCOPY DATE,
647 period_year OUT NOCOPY NUMBER,
648 errbuf OUT NOCOPY VARCHAR2,
649 retcode OUT NOCOPY NUMBER) IS
650 l_module_name VARCHAR2(200) := g_module_name || 'Get_Period_Year';
651 vl_period_set_name Gl_Periods.period_set_name%TYPE;
652 BEGIN
653 BEGIN
654 SELECT period_set_name
655 INTO vl_period_set_name
656 FROM Gl_Sets_Of_Books
657 WHERE set_of_books_id = sob_id;
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 retcode := 2;
661 errbuf := 'Period Set name not found for set of books '||to_char(sob_id);
662 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found',errbuf);
663 RETURN ;
664 WHEN OTHERS THEN
665 retcode := SQLCODE ;
666 errbuf := SQLERRM ||
667 ' -- Error in Get_Period_Year procedure,while getting the period set name.' ;
668 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1',errbuf);
669 RETURN ;
670 END;
671 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
672 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD SET NAME IS '||VL_PERIOD_SET_NAME);
673 END IF;
674
675 BEGIN
676 SELECT period_year
677 INTO period_year
678 FROM Gl_Periods
679 WHERE period_set_name = vl_period_set_name
680 AND period_name = period_from;
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 retcode := 2;
684 errbuf := 'Period Year not found for the set of books '||to_char(sob_id) ||
685 ' and the period set name '||vl_period_set_name;
686 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found1',errbuf);
687 RETURN ;
688
689 WHEN OTHERS THEN
690 retcode := SQLCODE ;
691 errbuf := SQLERRM ||
692 ' -- Error in Get_Period_Year procedure,while getting the period year.' ;
693 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception2',errbuf);
694 RETURN ;
695 END;
696 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
697 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD YEAR IS '||TO_CHAR(PERIOD_YEAR));
698 END IF;
699
700 BEGIN /* From Period Start Date */
701 SELECT start_date
702 INTO period_start_date
703 FROM Gl_Period_Statuses
704 WHERE ledger_id = sob_id
705 AND application_id = 101
706 AND period_year = period_year
707 AND period_name = period_from
708 AND adjustment_period_flag = 'N';
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 retcode := 2;
712 errbuf := 'Start Date not defined for the period name '||period_from;
713 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found2',errbuf);
714 RETURN ;
715
716 WHEN OTHERS THEN
717 retcode := SQLCODE ;
718 errbuf := SQLERRM ||
719 ' -- Error in Get_Period_Year procedure,while getting the start date for the from period '||period_from ;
720 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception3',errbuf);
721 RETURN ;
722 END; /* From Period Start Date */
723 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
724 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD START DATE IS '||TO_CHAR(PERIOD_START_DATE));
725 END IF;
726
727 BEGIN /* To Period End Date */
728 SELECT end_date
729 INTO period_end_date
730 FROM Gl_Period_Statuses
731 WHERE ledger_id = sob_id
732 AND application_id = 101
733 AND period_year = period_year
734 AND period_name = period_to
735 AND adjustment_period_flag = 'N';
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 retcode := 2;
739 errbuf := 'End Date not defined for the period name '||period_to;
740 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data_found3',errbuf);
741 RETURN ;
742
743 WHEN OTHERS THEN
744 retcode := SQLCODE ;
745 errbuf := SQLERRM ||
746 ' -- Error in Get_Period_Year procedure,while getting the end date for the to period '||period_to ;
747 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception4',errbuf);
748 RETURN ;
749 END; /* To Period End Date */
750 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
751 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD END DATE IS '||TO_CHAR(PERIOD_END_DATE));
752 END IF;
753
754 -- Setting up the retcode
755 retcode := 0;
756 EXCEPTION
757 WHEN OTHERS THEN
758 retcode := SQLCODE;
759 errbuf := SQLERRM;
760 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
761 RETURN;
762
763 END Get_Period_Year;
764
765 FUNCTION tin
766 (
767 p_vendor_type_lookup_code IN VARCHAR2,
768 p_org_type_lookup_code IN VARCHAR2,
769 p_num_1099 IN VARCHAR2,
770 p_individual_1099 IN VARCHAR2,
771 p_employee_id IN NUMBER
772 )
773 RETURN VARCHAR2
774 IS
775 l_tin VARCHAR2(100);
776 l_module_name VARCHAR2(200);
777 l_errbuf VARCHAR2(1024);
778 BEGIN
779 l_module_name := g_module_name || 'tin';
780 IF (p_vendor_type_lookup_code = 'EMPLOYEE') THEN
781 BEGIN
782 SELECT papf.national_identifier
783 INTO l_tin
784 FROM per_all_people_f papf
785 WHERE person_id = p_employee_id
786 AND ROWNUM < 2;
787 EXCEPTION
788 WHEN NO_DATA_FOUND THEN
789 l_tin := NVL(p_num_1099, p_individual_1099);
790 END;
791 ELSIF(p_vendor_type_lookup_code = 'CONTRACTOR') THEN
792 IF (p_org_type_lookup_code IN ('INDIVIDUAL', 'FOREIGN INDIVIDUAL', 'PARTNERSHIP', 'FOREIGN PARTNERSHIP')) THEN
793 l_tin := p_individual_1099;
794 ELSE
795 l_tin := p_num_1099;
796 END IF;
797 ELSE
798 l_tin := NVL(p_num_1099, p_individual_1099);
799 END IF;
800 RETURN l_tin;
801 EXCEPTION
802 WHEN OTHERS THEN
803 l_errbuf := SQLERRM;
804 log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception',l_errbuf);
805 RAISE;
806 END;
807
808 ---------------------------------------------------------------
809
810
811 BEGIN
812 g_module_name := 'FV_UTILITY';
813 g_current_level := fnd_log.g_current_runtime_level;
814
815 END; -- package body