[Home] [Help]
PACKAGE BODY: APPS.BIM_SET_OF_BOOKS
Source
1 PACKAGE BODY BIM_SET_OF_BOOKS AS
2 /* $Header: bimsobfb.pls 120.2 2005/09/26 23:45:05 arvikuma noship $*/
3
4 -------------------------------------------------------------------------------
5 -- PROCEDURE
6 -- GET_FISCAL_DATA
7 --
8 -- Note
9 -- This procedure will get the fiscal year, quarter and month for the
10 -- given date and org_id.
11 -------------------------------------------------------------------------------
12
13 PROCEDURE GET_FISCAL_DATA
14 (
15 p_input_date IN DATE DEFAULT sysdate
16 ,p_org_id IN NUMBER
17 ,x_year OUT NOCOPY VARCHAR2
18 ,x_quarter OUT NOCOPY VARCHAR2
19 ,x_month OUT NOCOPY VARCHAR2
20 ,x_quarter_num OUT NOCOPY NUMBER
21 ,x_month_num OUT NOCOPY NUMBER
22 ) IS
23 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_data';
24
25 /* CURSOR period_info
26 IS
27 SELECT b.period_year year,
28 SUBSTR(b.entered_period_name, 0, 2) quarter,
29 TO_CHAR(p_input_date, 'MON') month
30 FROM gl_sets_of_books a, gl_periods b
31 WHERE a.set_of_books_id = (select set_of_books_id FROM
32 ozf_sys_parameters_all WHERE org_id = p_org_id)
33 AND b.period_set_name = a.period_set_name
34 AND b.period_type=quarter_type
35 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
36 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
37 */
38 CURSOR period_month
39 IS
40 SELECT b.period_name, b.period_num
41 FROM gl_periods b
42 WHERE b.period_set_name = default_calender
43 AND b.period_type=month_type
44 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
45 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
46
47 CURSOR period_quarter
48 IS
49 SELECT b.period_name, b.period_num
50 FROM gl_periods b
51 WHERE b.period_set_name = default_calender
52 AND b.period_type=quarter_type
53 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
54 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
55
56 CURSOR period_year
57 IS
58 SELECT b.period_name
59 FROM gl_periods b
60 WHERE b.period_set_name = default_calender
61 AND b.period_type=year_type
62 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
63 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
64
65 BEGIN
66
67 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
68 OPEN period_month;
69 FETCH period_month INTO x_month, x_month_num;
70
71 IF period_month%NOTFOUND THEN
72 CLOSE period_month;
73 RAISE fnd_api.g_exc_unexpected_error;
74 END IF;
75
76 CLOSE period_month;
77
78 OPEN period_quarter;
79 FETCH period_quarter INTO x_quarter, x_quarter_num;
80
81 IF period_quarter%NOTFOUND THEN
82 CLOSE period_quarter;
83 RAISE fnd_api.g_exc_unexpected_error;
84 END IF;
85
86 CLOSE period_quarter;
87
88 OPEN period_year;
89 FETCH period_year INTO x_year;
90
91 IF period_year%NOTFOUND THEN
92 CLOSE period_year;
93 RAISE fnd_api.g_exc_unexpected_error;
94 END IF;
95
96 CLOSE period_year;
97
98 --ams_utility_pvt.debug_message('fiscal year --' || x_year);
99 --ams_utility_pvt.debug_message('fiscal quarter --' || x_quarter);
100 --ams_utility_pvt.debug_message('fiscal month --' || x_month);
101
102 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
103
104 EXCEPTION
105 WHEN fnd_api.g_exc_unexpected_error THEN
106 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
107 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
108 fnd_message.set_token('ROW', l_api_name || ' ' ||
109 p_input_date || ' ' || p_org_id || ' '
110 || ' -- NO DATA FOUND -- ' );
111 fnd_msg_pub.add;
112 RAISE;
113
114 WHEN OTHERS THEN
115 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
116 RAISE;
117
118 END GET_FISCAL_DATA;
119
120 -------------------------------------------------------------------------------
121 -- FUNCTION
122 -- GET_FISCAL_MONTH
123 --
124 -- Note
125 -- This procedure will get the fiscal month
126 -- given date and org_id.
127 -------------------------------------------------------------------------------
128
129 FUNCTION GET_FISCAL_MONTH
130 (
131 p_input_date IN DATE DEFAULT sysdate
132 ,p_org_id IN NUMBER
133 ) RETURN VARCHAR2 IS
134 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_month';
135 l_month VARCHAR2(30);
136
137 CURSOR period_info
138 IS
139 SELECT b.period_name
140 FROM gl_periods b
141 WHERE b.period_set_name = default_calender
142 --AND b.period_type=month_type
143 AND b.period_type=month_type
144 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
145 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
146
147 BEGIN
148
149 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
150 OPEN period_info;
151 FETCH period_info INTO l_month;
152
153 IF period_info%NOTFOUND THEN
154 CLOSE period_info;
155 RAISE fnd_api.g_exc_unexpected_error;
156 END IF;
157
158 CLOSE period_info;
159
160 --ams_utility_pvt.debug_message('fiscal month --' || l_month);
161
162 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
163
164 RETURN(l_month);
165
166 EXCEPTION
167 WHEN fnd_api.g_exc_unexpected_error THEN
168 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
169 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
170 fnd_message.set_token('ROW', l_api_name || ' ' ||
171 p_input_date || ' ' || p_org_id || ' '
172 || ' -- NO DATA FOUND -- ' );
173 fnd_msg_pub.add;
174 RAISE;
175
176 WHEN OTHERS THEN
177 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
178 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
179 fnd_message.set_token('ROW', l_api_name || ' ' ||
180 p_input_date || ' ' || p_org_id || ' '
181 || SQLERRM||' ' ||SQLCODE);
182 fnd_msg_pub.add;
183 RAISE;
184
185 END GET_FISCAL_MONTH;
186 -------------------------------------------------------------------------------
187 -- FUNCTION
188 -- GET_FISCAL_QTR
189 --
190 -- Note
191 -- This procedure will get the fiscal qtr
192 -- given date and org_id.
193 -------------------------------------------------------------------------------
194 FUNCTION GET_FISCAL_QTR
195 (
196 p_input_date IN DATE DEFAULT sysdate
197 ,p_org_id IN NUMBER
198 ) RETURN VARCHAR2 IS
199 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_qtr';
200 l_qtr VARCHAR2(30);
201
202 CURSOR period_info
203 IS
204 SELECT b.period_name
205 FROM gl_periods b
206 WHERE b.period_set_name = default_calender
207 AND b.period_type=quarter_type
208 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
209 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
210 BEGIN
211
212 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
213 OPEN period_info;
214 FETCH period_info INTO l_qtr;
215
216 IF period_info%NOTFOUND THEN
217 CLOSE period_info;
218 RAISE fnd_api.g_exc_unexpected_error;
219 END IF;
220
221 CLOSE period_info;
222
223 --ams_utility_pvt.debug_message('fiscal quarter --' || l_qtr);
224
225 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
226
227 RETURN(l_qtr);
228
229 EXCEPTION
230 WHEN fnd_api.g_exc_unexpected_error THEN
231 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
232 fnd_message.set_token('ROW', l_api_name || ' ' ||
233 p_input_date || ' ' || p_org_id || ' '
234 || ' -- NO DATA FOUND -- ' );
235 fnd_msg_pub.add;
236 RAISE;
237
238 WHEN OTHERS THEN
239 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
240 fnd_message.set_token('ROW', l_api_name || ' ' ||
241 p_input_date || ' ' || p_org_id || ' '
242 || SQLERRM||' ' ||SQLCODE);
243 fnd_msg_pub.add;
244 RAISE;
245
246 END GET_FISCAL_QTR;
247 -------------------------------------------------------------------------------
248 -- FUNCTION
249 -- GET_FISCAL_YEAR
250 --
251 -- Note
252 -- This procedure will get the fisical qtr
253 -- given date and org_id.
254 -------------------------------------------------------------------------------
255
256 FUNCTION GET_FISCAL_YEAR
257 (
258 p_input_date IN DATE DEFAULT sysdate
259 ,p_org_id IN NUMBER
260 ) RETURN VARCHAR2 IS
261 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_year';
262 l_year VARCHAR2(30);
263
264 CURSOR period_info
265 IS
266 SELECT b.period_name year
267 FROM gl_periods b
268 WHERE b.period_set_name = default_calender
269 AND b.period_type=year_type
270 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
271 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
272
273 BEGIN
274
275 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
276 OPEN period_info;
277 FETCH period_info INTO l_year;
278
279 IF period_info%NOTFOUND THEN
280 CLOSE period_info;
281 RAISE fnd_api.g_exc_unexpected_error;
282 END IF;
283
284 CLOSE period_info;
285
286 --ams_utility_pvt.debug_message('fisical year --' || l_year);
287
288 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
289
290 RETURN(l_year);
291
292 EXCEPTION
293 WHEN fnd_api.g_exc_unexpected_error THEN
294 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
295 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
296 fnd_message.set_token('ROW', l_api_name || ' ' ||
297 p_input_date || ' ' || p_org_id || ' '
298 || ' -- NO DATA FOUND -- ' );
299 fnd_msg_pub.add;
300 RAISE;
301
302 WHEN OTHERS THEN
303 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
304 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
305 fnd_message.set_token('ROW', l_api_name || ' ' ||
306 p_input_date || ' ' || p_org_id || ' '
307 || SQLERRM||' ' ||SQLCODE);
308 fnd_msg_pub.add;
309 RAISE;
310
311 END GET_FISCAL_YEAR;
312 -------------------------------------------------------------------------------
313 -- FUNCTION
314 -- GET_FISCAL_QTR_NUM
315 --
316 -- Note
317 -- This procedure will get the fisical qtr number
318 -- given date and org_id.
319 -------------------------------------------------------------------------------
320
321 FUNCTION GET_FISCAL_QTR_NUM
322 (
323 p_input_date IN DATE DEFAULT sysdate
324 ,p_org_id IN NUMBER
325 ) RETURN NUMBER IS
326 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_qtr_num';
327 l_period_num NUMBER;
328
329 CURSOR period_info
330 IS
331 SELECT b.period_num period_num
332 FROM gl_periods b
333 WHERE b.period_set_name = default_calender
334 AND b.period_type=quarter_type
335 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
336 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
337
338 BEGIN
339
340 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
341 OPEN period_info;
342 FETCH period_info INTO l_period_num;
343
344 IF period_info%NOTFOUND THEN
345 CLOSE period_info;
346 RAISE fnd_api.g_exc_unexpected_error;
347 END IF;
348
349 CLOSE period_info;
350
351 --ams_utility_pvt.debug_message('fisical quarter period num--' || l_period_num);
352
353 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
354
355 RETURN(l_period_num);
356
357 EXCEPTION
358 WHEN fnd_api.g_exc_unexpected_error THEN
359 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
360 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
361 fnd_message.set_token('ROW', l_api_name || ' ' ||
362 p_input_date || ' ' || p_org_id || ' '
363 || ' -- NO DATA FOUND -- ' );
364 fnd_msg_pub.add;
365 RAISE;
366
367 WHEN OTHERS THEN
368 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
369 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
370 fnd_message.set_token('ROW', l_api_name || ' ' ||
371 p_input_date || ' ' || p_org_id || ' '
372 || SQLERRM||' ' ||SQLCODE);
373 fnd_msg_pub.add;
374 RAISE;
375
376 END GET_FISCAL_QTR_NUM;
377 -------------------------------------------------------------------------------
378 -- FUNCTION
379 -- GET_PRE_PERIOD
380 --
381 -- Note
382 -- This procedure will get the previous period name
383 -- given current period name , type and org_id.
384 -------------------------------------------------------------------------------
385
386 FUNCTION GET_PRE_PERIOD
387 ( p_name IN VARCHAR2
388 ,p_type IN VARCHAR2
389 ,p_org_id IN NUMBER
390 ) RETURN VARCHAR2 IS
391 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_pre_period';
392 l_name VARCHAR2(30);
393
394 CURSOR period_name IS
395 SELECT period_name
396 FROM gl_periods
397 WHERE end_date =( SELECT start_date -1
398 FROM gl_periods
399 WHERE period_name =p_name
400 AND period_set_name = default_calender)
401 AND period_set_name = default_calender
402 AND UPPER(period_type) =p_type;
403
404 BEGIN
408 FETCH period_name INTO l_name;
405
406 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
407 OPEN period_name;
409
410 IF period_name%NOTFOUND THEN
411 CLOSE period_name;
412 RAISE fnd_api.g_exc_unexpected_error;
413 END IF;
414
415 CLOSE period_name;
416
417 --ams_utility_pvt.debug_message('fisical month --' || l_month);
418
419 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
420
421 RETURN(l_name);
422
423 EXCEPTION
424 WHEN fnd_api.g_exc_unexpected_error THEN
425 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_name || ' ' || SQLERRM(SQLCODE));
426 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
427 fnd_message.set_token('ROW', l_api_name || ' ' ||
428 p_name || ' ' ||p_type||' '|| p_org_id || ' '
429 || ' -- NO DATA FOUND -- ' );
430 fnd_msg_pub.add;
431 RAISE;
432
433 WHEN OTHERS THEN
434 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_name || ' ' || SQLERRM(SQLCODE));
435 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
436 fnd_message.set_token('ROW', l_api_name || ' ' ||
437 p_name || ' ' ||p_type||' '|| p_org_id || ' '
438 || SQLERRM||' ' ||SQLCODE);
439 fnd_msg_pub.add;
440 RAISE;
441
442 END GET_PRE_PERIOD;
443 -------------------------------------------------------------------------------
444 -- FUNCTION
445 -- GET_MONTH_ORDER
446 --
447 -- Note
448 -- This procedure will get the fsical month number
449 -- given month name and org_id.
450 -------------------------------------------------------------------------------
451
452 FUNCTION GET_MONTH_ORDER
453 (
454 p_month IN VARCHAR2
455 ,p_org_id IN NUMBER
456 ) RETURN NUMBER IS
457 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_month_order';
458 l_period_num NUMBER;
459
460 CURSOR period_info
461 IS
462 SELECT b.period_num period_num
463 FROM gl_periods b
464 WHERE b.period_set_name = default_calender
465 AND b.period_type=month_type
466 AND b.period_name =p_month;
467 BEGIN
468
469 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
470 OPEN period_info;
471 FETCH period_info INTO l_period_num;
472
473 IF period_info%NOTFOUND THEN
474 CLOSE period_info;
475 RAISE fnd_api.g_exc_unexpected_error;
476 END IF;
477
478 CLOSE period_info;
479
480 --ams_utility_pvt.debug_message('fisical quarter period num--' || l_period_num);
481
482 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
483
484 RETURN(l_period_num);
485
486 EXCEPTION
487 WHEN fnd_api.g_exc_unexpected_error THEN
488 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_month || ' ' || SQLERRM(SQLCODE));
489 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || SQLERRM(SQLCODE));
490 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
491 fnd_message.set_token('ROW', l_api_name || ' ' ||
492 p_month || ' ' || p_org_id || ' '
493 || ' -- NO DATA FOUND -- ' );
494 fnd_msg_pub.add;
495 RAISE;
496
497 WHEN OTHERS THEN
498 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_month || ' ' || SQLERRM(SQLCODE));
499 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
500 fnd_message.set_token('ROW', l_api_name || ' ' ||
501 p_month || ' ' || p_org_id || ' '
502 || SQLERRM||' ' ||SQLCODE);
503 fnd_msg_pub.add;
504 RAISE;
505
506 END GET_MONTH_ORDER;
507 -------------------------------------------------------------------------------
508 -- FUNCTION
509 -- GET_FISCAL_MONTH_NUM
510 --
511 -- Note
512 -- This procedure will get the fsical month number
513 -- given date and org_id.
514 -------------------------------------------------------------------------------
515
516 FUNCTION GET_FISCAL_MONTH_NUM
517 (
518 p_input_date IN DATE DEFAULT sysdate
519 ,p_org_id IN NUMBER
520 ) RETURN NUMBER IS
521 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_month_num';
522 l_period_num NUMBER;
523
524 CURSOR period_info
525 IS
526 SELECT b.period_num period_num
527 FROM gl_periods b
528 WHERE b.period_set_name = default_calender
529 AND b.period_type=month_type
530 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
531 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
532
533 BEGIN
534
535 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
536 OPEN period_info;
537 FETCH period_info INTO l_period_num;
538
539 IF period_info%NOTFOUND THEN
540 CLOSE period_info;
541 RAISE fnd_api.g_exc_unexpected_error;
542 END IF;
543
547
544 CLOSE period_info;
545
546 --ams_utility_pvt.debug_message('fisical quarter period num--' || l_period_num);
548 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
549
550 RETURN(l_period_num);
551
552 EXCEPTION
553 WHEN fnd_api.g_exc_unexpected_error THEN
554 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
555 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
556 fnd_message.set_token('ROW', l_api_name || ' ' ||
557 p_input_date || ' ' || p_org_id || ' '
558 || ' -- NO DATA FOUND -- ' );
559 fnd_msg_pub.add;
560 RAISE;
561
562 WHEN OTHERS THEN
563 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
564 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
565 fnd_message.set_token('ROW', l_api_name || ' ' ||
566 p_input_date || ' ' || p_org_id || ' '
567 || SQLERRM||' ' ||SQLCODE);
568 fnd_msg_pub.add;
569 RAISE;
570
571 END GET_FISCAL_MONTH_NUM;
572 -------------------------------------------------------------------------------
573 -- FUNCTION
574 -- GET_FISCAL_ROLL_YEAR_START
575 --
576 -- Note
577 -- This procedure will get the start of the fiscal year
578 -- given date and org_id.
579 -------------------------------------------------------------------------------
580
581 FUNCTION GET_FISCAL_ROLL_YEAR_START
582 (
583 p_input_date IN DATE DEFAULT sysdate
584 ,p_org_id IN NUMBER
585 ) RETURN DATE IS
586 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_roll_year_start';
587 l_date DATE;
588
589 /*
590 CURSOR period_info(v_date DATE)
591 IS
592 SELECT b.start_date
593 FROM gl_sets_of_books a, gl_periods b
594 WHERE a.set_of_books_id = (select set_of_books_id FROM
595 ozf_sys_parameters_all WHERE org_id = p_org_id)
596 AND b.period_set_name = a.period_set_name
597 AND b.period_type=quarter_type
598 AND TRUNC(v_date) >= TRUNC(b.start_date)
599 AND TRUNC(v_date) <= TRUNC(b.end_date);
600 */
601 CURSOR period_info(v_date DATE)
602 IS
603 SELECT b.start_date
604 FROM gl_periods b
605 WHERE b.period_set_name = default_calender
606 AND b.period_type=quarter_type
607 AND TRUNC(v_date) >= TRUNC(b.start_date)
608 AND TRUNC(v_date) <= TRUNC(b.end_date);
609
610 BEGIN
611
612 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
613
614 OPEN period_info(p_input_date);
615 FETCH period_info INTO l_date;
616
617 IF period_info%NOTFOUND THEN
618 CLOSE period_info;
619 RAISE fnd_api.g_exc_unexpected_error;
620 END IF;
621
622 CLOSE period_info;
623
624 OPEN period_info(l_date-1);
625 FETCH period_info INTO l_date;
626
627 IF period_info%NOTFOUND THEN
628 CLOSE period_info;
629 RAISE fnd_api.g_exc_unexpected_error;
630 END IF;
631
632 CLOSE period_info;
633
634 OPEN period_info(l_date-1);
635 FETCH period_info INTO l_date;
636
637 IF period_info%NOTFOUND THEN
638 CLOSE period_info;
639 RAISE fnd_api.g_exc_unexpected_error;
640 END IF;
641
642 CLOSE period_info;
643
644 OPEN period_info(l_date-1);
645 FETCH period_info INTO l_date;
646
647 IF period_info%NOTFOUND THEN
648 CLOSE period_info;
649 RAISE fnd_api.g_exc_unexpected_error;
650 END IF;
651
652 CLOSE period_info;
653
654 --ams_utility_pvt.debug_message('fisical year --' || l_year);
655 --ams_utility_pvt.debug_message('fisical quarter --' || l_qtr);
656 --ams_utility_pvt.debug_message('fisical month --' || l_month);
657
658 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
659
660 RETURN(l_date);
661
662 EXCEPTION
663 WHEN fnd_api.g_exc_unexpected_error THEN
664 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
665 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
666 fnd_message.set_token('ROW', l_api_name || ' ' ||
667 p_input_date || ' ' || p_org_id || ' '
668 || ' -- NO DATA FOUND -- ' );
669 fnd_msg_pub.add;
670 RAISE;
671
672 WHEN OTHERS THEN
673 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
674 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
675 fnd_message.set_token('ROW', l_api_name || ' ' ||
676 p_input_date || ' ' || p_org_id || ' '
677 || SQLERRM||' ' ||SQLCODE);
678 fnd_msg_pub.add;
679 RAISE;
680
681 END GET_FISCAL_ROLL_YEAR_START;
682
683 -------------------------------------------------------------------------------
684 -- FUNCTION
685 -- GET_PRE_FISCAL_ROLL_YEAR_START
686 --
687 -- Note
691
688 -- This procedure will get the previous start of the rolling fiscal year
689 -- given date and org_id.
690 -------------------------------------------------------------------------------
692 FUNCTION GET_PRE_FISCAL_ROLL_YEAR_START
693 (
694 p_input_date IN DATE DEFAULT sysdate
695 ,p_org_id IN NUMBER
696 ) RETURN DATE IS
697 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_pre_fiscal_roll_year_start';
698 l_date DATE;
699
700
701
702 BEGIN
703
704 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
705 l_date :=get_fiscal_roll_year_start(p_input_date, p_org_id);
706
707 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
708 l_date := l_date-1;
709 l_date := get_fiscal_roll_year_start(l_date, p_org_id);
710 RETURN(l_date);
711
712 EXCEPTION
713 WHEN fnd_api.g_exc_unexpected_error THEN
714 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
715 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
716 fnd_message.set_token('ROW', l_api_name || ' ' ||
717 p_input_date || ' ' || p_org_id || ' '
718 || ' -- NO DATA FOUND -- ' );
719 fnd_msg_pub.add;
720 RAISE;
721
722 WHEN OTHERS THEN
723 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
724 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
725 fnd_message.set_token('ROW', l_api_name || ' ' ||
726 p_input_date || ' ' || p_org_id || ' '
727 || SQLERRM||' ' ||SQLCODE);
728 fnd_msg_pub.add;
729 RAISE;
730
731 END GET_PRE_FISCAL_ROLL_YEAR_START;
732
733 -------------------------------------------------------------------------------
734 -- FUNCTION
735 -- GET_PRE_FISCAL_ROLL_YEAR_END
736 --
737 -- Note
738 -- This procedure will get the previous start of the rolling fiscal year
739 -- given date and org_id.
740 -------------------------------------------------------------------------------
741
742 FUNCTION GET_PRE_FISCAL_ROLL_YEAR_END
743 (
744 p_input_date IN DATE DEFAULT sysdate
745 ,p_org_id IN NUMBER
746 ) RETURN DATE IS
747 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_pre_fiscal_roll_year_end';
748 l_date DATE;
749 l_delta NUMBER;
750
751 BEGIN
752
753 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
754 l_date :=get_fiscal_roll_year_start(p_input_date, p_org_id);
755 l_delta :=p_input_date- l_date;
756 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
757 l_date := l_date-1;
758 l_date := get_fiscal_roll_year_start(l_date, p_org_id);
759 l_date :=l_date +l_delta;
760 RETURN(l_date);
761
762 EXCEPTION
763 WHEN fnd_api.g_exc_unexpected_error THEN
764 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
765 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
766 fnd_message.set_token('ROW', l_api_name || ' ' ||
767 p_input_date || ' ' || p_org_id || ' '
768 || ' -- NO DATA FOUND -- ' );
769 fnd_msg_pub.add;
770 RAISE;
771
772 WHEN OTHERS THEN
773 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
774 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
775 fnd_message.set_token('ROW', l_api_name || ' ' ||
776 p_input_date || ' ' || p_org_id || ' '
777 || SQLERRM||' ' ||SQLCODE);
778 fnd_msg_pub.add;
779 RAISE;
780
781 END GET_PRE_FISCAL_ROLL_YEAR_END;
782 -------------------------------------------------------------------------------
783 -- FUNCTION
784 -- GET_FISCAL_YEAR_START
785 --
786 -- Note
787 -- This procedure will get the start of the fiscal year
788 -- given date and org_id.
789 -------------------------------------------------------------------------------
790
791 FUNCTION GET_FISCAL_YEAR_START
792 (
793 p_input_date IN DATE DEFAULT sysdate
794 ,p_org_id IN NUMBER
795 ) RETURN DATE IS
796 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_year_start';
797 l_date DATE;
798
799 /*
800 CURSOR period_info
801 IS
802 SELECT b.start_date
803 FROM gl_sets_of_books a, gl_periods b
804 WHERE a.set_of_books_id = (select set_of_books_id FROM
805 ozf_sys_parameters_all WHERE org_id = p_org_id)
806 AND b.period_set_name = a.period_set_name
807 AND b.period_type=year_type
808 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
809 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
810 */
811 CURSOR period_info
812 IS
813 SELECT b.start_date
814 FROM gl_periods b
815 WHERE b.period_set_name = default_calender
819
816 AND b.period_type=year_type
817 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
818 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
820 BEGIN
821
822 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
823 OPEN period_info;
824 FETCH period_info INTO l_date;
825
826 IF period_info%NOTFOUND THEN
827 CLOSE period_info;
828 RAISE fnd_api.g_exc_unexpected_error;
829 END IF;
830
831 CLOSE period_info;
832
833 --ams_utility_pvt.debug_message('fisical year --' || l_date);
834
835 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
836
837 RETURN(l_date);
838
839 EXCEPTION
840 WHEN fnd_api.g_exc_unexpected_error THEN
841 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
842 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
843 fnd_message.set_token('ROW', l_api_name || ' ' ||
844 p_input_date || ' ' || p_org_id || ' '
845 || ' -- NO DATA FOUND -- ' );
846 fnd_msg_pub.add;
847 RAISE;
848
849 WHEN OTHERS THEN
850 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
851 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
852 fnd_message.set_token('ROW', l_api_name || ' ' ||
853 p_input_date || ' ' || p_org_id || ' '
854 || SQLERRM||' ' ||SQLCODE);
855 fnd_msg_pub.add;
856 RAISE;
857
858 END GET_FISCAL_YEAR_START;
859 -------------------------------------------------------------------------------
860 -- FUNCTION
861 -- GET_FISCAL_QTR_START
862 --
863 -- Note
864 -- This procedure will get the start of the fiscal qtr
865 -- given date and org_id.
866 -------------------------------------------------------------------------------
867
868 FUNCTION GET_FISCAL_QTR_START
869 (
870 p_input_date IN DATE DEFAULT sysdate
871 ,p_org_id IN NUMBER
872 ) RETURN DATE IS
873 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_qtr_start';
874 l_date DATE;
875
876 /*
877 CURSOR period_info
878 IS
879 SELECT b.start_date
880 FROM gl_sets_of_books a, gl_periods b
881 WHERE a.set_of_books_id = (select set_of_books_id FROM
882 ozf_sys_parameters_all WHERE org_id = p_org_id)
883 AND b.period_set_name = a.period_set_name
884 AND b.period_type=quarter_type
885 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
886 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
887 */
888 CURSOR period_info
889 IS
890 SELECT b.start_date
891 FROM gl_periods b
892 WHERE b.period_set_name = default_calender
893 AND b.period_type=quarter_type
894 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
895 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
896
897 BEGIN
898
899 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
900 OPEN period_info;
901 FETCH period_info INTO l_date;
902
903 IF period_info%NOTFOUND THEN
904 CLOSE period_info;
905 RAISE fnd_api.g_exc_unexpected_error;
906 END IF;
907
908 CLOSE period_info;
909
910 --ams_utility_pvt.debug_message('fisical year --' || l_date);
911
912 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
913
914 RETURN(l_date);
915
916 EXCEPTION
917 WHEN fnd_api.g_exc_unexpected_error THEN
918 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
919 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
920 fnd_message.set_token('ROW', l_api_name || ' ' ||
921 p_input_date || ' ' || p_org_id || ' '
922 || ' -- NO DATA FOUND -- ' );
923 fnd_msg_pub.add;
924 RAISE;
925
926 WHEN OTHERS THEN
927 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
928 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
929 fnd_message.set_token('ROW', l_api_name || ' ' ||
930 p_input_date || ' ' || p_org_id || ' '
931 || SQLERRM||' ' ||SQLCODE);
932 fnd_msg_pub.add;
933 RAISE;
934
935 END GET_FISCAL_QTR_START;
936
937
938 -------------------------------------------------------------------------------
939 -- FUNCTION
940 -- GET_FISCAL_MONTH_START
941 --
942 -- Note
943 -- This procedure will get the start of the fiscal month
944 -- given date and org_id.
945 -------------------------------------------------------------------------------
946
947 FUNCTION GET_FISCAL_MONTH_START
948 (
949 p_input_date IN DATE DEFAULT sysdate
950 ,p_org_id IN NUMBER
951 ) RETURN DATE IS
952 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_month_start';
956 CURSOR period_info
953 l_date DATE;
954
955 /*
957 IS
958 SELECT b.start_date
959 FROM gl_sets_of_books a, gl_periods b
960 WHERE a.set_of_books_id = (select set_of_books_id FROM
961 ozf_sys_parameters_all WHERE org_id = p_org_id)
962 AND b.period_set_name = a.period_set_name
963 AND b.period_type=month_type
964 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
965 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
966 */
967 CURSOR period_info
968 IS
969 SELECT b.start_date
970 FROM gl_periods b
971 WHERE b.period_set_name = default_calender
972 AND b.period_type=month_type
973 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
974 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
975
976 BEGIN
977
978 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
979 OPEN period_info;
980 FETCH period_info INTO l_date;
981
982 IF period_info%NOTFOUND THEN
983 CLOSE period_info;
984 RAISE fnd_api.g_exc_unexpected_error;
985 END IF;
986
987 CLOSE period_info;
988
989 --ams_utility_pvt.debug_message('fisical year --' || l_date);
990
991 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
992
993 RETURN(l_date);
994
995 EXCEPTION
996 WHEN fnd_api.g_exc_unexpected_error THEN
997 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
998 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
999 fnd_message.set_token('ROW', l_api_name || ' ' ||
1000 p_input_date || ' ' || p_org_id || ' '
1001 || ' -- NO DATA FOUND -- ' );
1002 fnd_msg_pub.add;
1003 RAISE;
1004
1005 WHEN OTHERS THEN
1006 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1007 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1008 fnd_message.set_token('ROW', l_api_name || ' ' ||
1009 p_input_date || ' ' || p_org_id || ' '
1010 || SQLERRM||' ' ||SQLCODE);
1011 fnd_msg_pub.add;
1012 RAISE;
1013
1014 END GET_FISCAL_MONTH_START;
1015
1016 /* GET_PRE_FISCAL_QTR_START */
1017
1018 FUNCTION GET_PRE_FISCAL_QTR_START
1019 (
1020 p_input_date IN DATE DEFAULT sysdate
1021 ,p_org_id IN NUMBER
1022 ) RETURN DATE IS
1023
1024 l_org_id NUMBER;
1025 l_current_fiscal_qtr_start DATE;
1026 l_previous_fiscal_qtr_start DATE;
1027 l_api_name CONSTANT VARCHAR2(300) := 'GET_PRE_FISCAL_QTR_START';
1028
1029
1030
1031 BEGIN
1032 l_current_fiscal_qtr_start := get_fiscal_qtr_start(p_input_date,l_org_id);
1033 l_previous_fiscal_qtr_start := get_fiscal_qtr_start(l_current_fiscal_qtr_start -1,l_org_id);
1034
1035 RETURN(l_previous_fiscal_qtr_start);
1036
1037 EXCEPTION
1038 WHEN fnd_api.g_exc_unexpected_error THEN
1039 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1040 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1041 fnd_message.set_token('ROW', l_api_name || ' ' ||
1042 p_input_date || ' ' || p_org_id || ' '
1043 || ' -- NO DATA FOUND -- ' );
1044 fnd_msg_pub.add;
1045 RAISE;
1046
1047 WHEN OTHERS THEN
1048 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1049 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1050 fnd_message.set_token('ROW', l_api_name || ' ' ||
1051 p_input_date || ' ' || p_org_id || ' '
1052 || SQLERRM||' ' ||SQLCODE);
1053 fnd_msg_pub.add;
1054 RAISE;
1055
1056 END GET_PRE_FISCAL_QTR_START;
1057
1058
1059 /* GET_PRE_FISCAL_QTR_START */
1060
1061
1062 /* GET_PRE_FISCAL_QTR_END */
1063
1064 FUNCTION GET_PRE_FISCAL_QTR_END
1065 (
1066 p_input_date IN DATE DEFAULT sysdate
1067 ,p_org_id IN NUMBER
1068 ) RETURN DATE IS
1069
1070 l_org_id NUMBER;
1071 l_current_fiscal_qtr_start DATE;
1072 l_current_fiscal_qtr_end DATE;
1073 l_previous_fiscal_qtr_start DATE;
1074 l_previous_fiscal_qtr_end DATE;
1075 l_api_name CONSTANT VARCHAR2(300) := 'GET_PRE_FISCAL_QTR_END';
1076 l_diff NUMBER;
1077
1078
1079 BEGIN
1080
1081 l_current_fiscal_qtr_start := get_fiscal_qtr_start(p_input_date,l_org_id);
1082 l_current_fiscal_qtr_end := get_fiscal_qtr_end(p_input_date,l_org_id);
1083 l_previous_fiscal_qtr_start := get_fiscal_qtr_start(l_current_fiscal_qtr_start -1,l_org_id);
1084 l_previous_fiscal_qtr_end := get_fiscal_qtr_end(l_current_fiscal_qtr_start -1,l_org_id);
1085
1086 IF (l_current_fiscal_qtr_end <> p_input_date) THEN
1087 l_diff := p_input_date - l_current_fiscal_qtr_start;
1088 l_previous_fiscal_qtr_end := l_previous_fiscal_qtr_start + l_diff;
1092 RETURN(l_previous_fiscal_qtr_end);
1089 --l_previous_fiscal_qtr_end := l_previous_fiscal_qtr_end + 1;
1090 END IF;
1091
1093
1094 EXCEPTION
1095 WHEN fnd_api.g_exc_unexpected_error THEN
1096 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1097 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1098 fnd_message.set_token('ROW', l_api_name || ' ' ||
1099 p_input_date || ' ' || p_org_id || ' '
1100 || ' -- NO DATA FOUND -- ' );
1101 fnd_msg_pub.add;
1102 RAISE;
1103
1104 WHEN OTHERS THEN
1105 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1106 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1107 fnd_message.set_token('ROW', l_api_name || ' ' ||
1108 p_input_date || ' ' || p_org_id || ' '
1109 || SQLERRM||' ' ||SQLCODE);
1110 fnd_msg_pub.add;
1111 RAISE;
1112
1113 END GET_PRE_FISCAL_QTR_END;
1114
1115 /* GET_PRE_FISCAL_QTR_END */
1116
1117 -------------------------------------------------------------------------------
1118 -- FUNCTION
1119 -- GET_PRE_FISCAL_YEAR_START
1120 --
1121 -- Note
1122 -- This procedure will get the previous start of the fiscal year
1123 -- given date and org_id.
1124 -------------------------------------------------------------------------------
1125
1126 FUNCTION GET_PRE_FISCAL_YEAR_START
1127 (
1128 p_input_date IN DATE DEFAULT sysdate
1129 ,p_org_id IN NUMBER
1130 ) RETURN DATE IS
1131
1132 l_org_id NUMBER;
1133 l_current_fiscal_year_start DATE;
1134 l_previous_fiscal_year_start DATE;
1135 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:GET_PRE_FISCAL_YEAR_START';
1136
1137
1138
1139 BEGIN
1140 l_current_fiscal_year_start := get_fiscal_year_start(p_input_date,l_org_id);
1141 l_previous_fiscal_year_start := get_fiscal_year_start(l_current_fiscal_year_start -1,l_org_id);
1142
1143 RETURN(l_previous_fiscal_year_start);
1144
1145 EXCEPTION
1146 WHEN fnd_api.g_exc_unexpected_error THEN
1147 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1148 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1149 fnd_message.set_token('ROW', l_api_name || ' ' ||
1150 p_input_date || ' ' || p_org_id || ' '
1151 || ' -- NO DATA FOUND -- ' );
1152 fnd_msg_pub.add;
1153 RAISE;
1154
1155 WHEN OTHERS THEN
1156 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1157 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1158 fnd_message.set_token('ROW', l_api_name || ' ' ||
1159 p_input_date || ' ' || p_org_id || ' '
1160 || SQLERRM||' ' ||SQLCODE);
1161 fnd_msg_pub.add;
1162 RAISE;
1163
1164 END GET_PRE_FISCAL_YEAR_START;
1165
1166 -------------------------------------------------------------------------------
1167 -- FUNCTION
1168 -- GET_PRE_FISCAL_YEAR_END
1169 --
1170 -- Note
1171 -- This procedure will get the previous end of the fiscal year
1172 -- given date and org_id.
1173 -------------------------------------------------------------------------------
1174
1175 FUNCTION GET_PRE_FISCAL_YEAR_END
1176 (
1177 p_input_date IN DATE DEFAULT sysdate
1178 ,p_org_id IN NUMBER
1179 ) RETURN DATE IS
1180
1181 l_org_id NUMBER;
1182 l_current_fiscal_year_start DATE;
1183 l_current_fiscal_year_end DATE;
1184 l_previous_fiscal_year_start DATE;
1185 l_previous_fiscal_year_end DATE;
1186 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:GET_PRE_FISCAL_YEAR_END';
1187 l_diff NUMBER;
1188
1189
1190 BEGIN
1191
1192 l_current_fiscal_year_start := get_fiscal_year_start(p_input_date,l_org_id);
1193 l_current_fiscal_year_end := get_fiscal_year_end(p_input_date,l_org_id);
1194 l_previous_fiscal_year_start := get_fiscal_year_start(l_current_fiscal_year_start -1,l_org_id);
1195 l_previous_fiscal_year_end := get_fiscal_year_end(l_current_fiscal_year_start -1,l_org_id);
1196 IF (l_current_fiscal_year_end <> p_input_date) THEN
1197 l_diff := p_input_date - l_current_fiscal_year_start;
1198 l_previous_fiscal_year_end := l_previous_fiscal_year_start + l_diff;
1199 --l_previous_fiscal_year_end := l_previous_fiscal_year_end + 1;
1200 END IF;
1201
1202 RETURN(l_previous_fiscal_year_end);
1203
1204 EXCEPTION
1205 WHEN fnd_api.g_exc_unexpected_error THEN
1206 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1207 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1208 fnd_message.set_token('ROW', l_api_name || ' ' ||
1209 p_input_date || ' ' || p_org_id || ' '
1210 || ' -- NO DATA FOUND -- ' );
1211 fnd_msg_pub.add;
1212 RAISE;
1213
1214 END GET_PRE_FISCAL_YEAR_END;
1218 -- GET_PRE_FISCAL_MONTH_START
1215
1216 -------------------------------------------------------------------------------
1217 -- FUNCTION
1219 --
1220 -- Note
1221 -- This procedure will get the previous start of the rolling fiscal year
1222 -- given date and org_id.
1223 -------------------------------------------------------------------------------
1224
1225 FUNCTION GET_PRE_FISCAL_MONTH_START
1226 (
1227 p_input_date IN DATE DEFAULT sysdate
1228 ,p_org_id IN NUMBER
1229 ) RETURN DATE IS
1230 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_pre_fiscal_month_start';
1231 l_date DATE;
1232
1233 BEGIN
1234
1235 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1236 l_date :=get_fiscal_month_start(p_input_date, p_org_id);
1237
1238 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1239 l_date := l_date-1;
1240 l_date := get_fiscal_month_start(l_date, p_org_id);
1241 RETURN(l_date);
1242
1243 EXCEPTION
1244 WHEN fnd_api.g_exc_unexpected_error THEN
1245 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1246 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1247 fnd_message.set_token('ROW', l_api_name || ' ' ||
1248 p_input_date || ' ' || p_org_id || ' '
1249 || ' -- NO DATA FOUND -- ' );
1250 fnd_msg_pub.add;
1251 RAISE;
1252
1253 WHEN OTHERS THEN
1254 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1255 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1256 fnd_message.set_token('ROW', l_api_name || ' ' ||
1257 p_input_date || ' ' || p_org_id || ' '
1258 || SQLERRM||' ' ||SQLCODE);
1259 fnd_msg_pub.add;
1260 RAISE;
1261
1262 END GET_PRE_FISCAL_MONTH_START;
1263
1264 -------------------------------------------------------------------------------
1265 -- FUNCTION
1266 -- GET_PRE_FISCAL_MONTH_END
1267 --
1268 -- Note
1269 -- This procedure will get the previous start of the rolling fiscal year
1270 -- given date and org_id.
1271 -------------------------------------------------------------------------------
1272
1273 FUNCTION GET_PRE_FISCAL_MONTH_END
1274 (
1275 p_input_date IN DATE DEFAULT sysdate
1276 ,p_org_id IN NUMBER
1277 ) RETURN DATE IS
1278 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_pre_fiscal_month_end';
1279 l_date DATE;
1280 l_date1 DATE;
1281 l_date2 DATE;
1282 l_delta NUMBER;
1283
1284 BEGIN
1285
1286 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1287 l_date :=get_fiscal_month_start(p_input_date, p_org_id);
1288 l_date1 :=get_fiscal_month_end(p_input_date, p_org_id);
1289 l_delta :=p_input_date- l_date;
1290 l_date := l_date-1;
1291 l_date := get_fiscal_month_start(l_date, p_org_id);
1292 l_date2 := get_fiscal_month_end(l_date, p_org_id);
1293 IF (l_date1 <> p_input_date) THEN
1294 l_date :=l_date +l_delta;
1295 ELSE
1296 l_date := l_date2;
1297 END IF;
1298 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1299 RETURN(l_date);
1300
1301 EXCEPTION
1302 WHEN fnd_api.g_exc_unexpected_error THEN
1303 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1304 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1305 fnd_message.set_token('ROW', l_api_name || ' ' ||
1306 p_input_date || ' ' || p_org_id || ' '
1307 || ' -- NO DATA FOUND -- ' );
1308 fnd_msg_pub.add;
1309 RAISE;
1310
1311 WHEN OTHERS THEN
1312 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1313 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1314 fnd_message.set_token('ROW', l_api_name || ' ' ||
1315 p_input_date || ' ' || p_org_id || ' '
1316 || SQLERRM||' ' ||SQLCODE);
1317 fnd_msg_pub.add;
1318 RAISE;
1319
1320 END GET_PRE_FISCAL_MONTH_END;
1321 -------------------------------------------------------------------------------
1322 -- FUNCTION
1323 -- GET_FISCAL_MONTH_END
1324 --
1325 -- Note
1326 -- This procedure will get the end of the fiscal month
1327 -- given date and org_id.
1328 -------------------------------------------------------------------------------
1329
1330 FUNCTION GET_FISCAL_MONTH_END
1331 (
1332 p_input_date IN DATE DEFAULT sysdate
1333 ,p_org_id IN NUMBER
1334 ) RETURN DATE IS
1335 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_month_end';
1336 l_date DATE;
1337
1338 /*
1339 CURSOR period_info
1340 IS
1341 SELECT b.end_date
1342 FROM gl_sets_of_books a, gl_periods b
1343 WHERE a.set_of_books_id = (select set_of_books_id FROM
1347 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
1344 ozf_sys_parameters_all WHERE org_id = p_org_id)
1345 AND b.period_set_name = a.period_set_name
1346 AND b.period_type=month_type
1348 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
1349 */
1350 CURSOR period_info
1351 IS
1352 SELECT b.end_date
1353 FROM gl_periods b
1354 WHERE b.period_set_name = default_calender
1355 AND b.period_type=month_type
1356 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
1357 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
1358
1359 BEGIN
1360
1361 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1362 OPEN period_info;
1363 FETCH period_info INTO l_date;
1364
1365 IF period_info%NOTFOUND THEN
1366 CLOSE period_info;
1367 RAISE fnd_api.g_exc_unexpected_error;
1368 END IF;
1369
1370 CLOSE period_info;
1371
1372 --ams_utility_pvt.debug_message('fisical year --' || l_date);
1373
1374 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1375
1376 RETURN(l_date);
1377
1378 EXCEPTION
1379 WHEN fnd_api.g_exc_unexpected_error THEN
1380 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1381 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1382 fnd_message.set_token('ROW', l_api_name || ' ' ||
1383 p_input_date || ' ' || p_org_id || ' '
1384 || ' -- NO DATA FOUND -- ' );
1385 fnd_msg_pub.add;
1386 RAISE;
1387
1388 WHEN OTHERS THEN
1389 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1390 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1391 fnd_message.set_token('ROW', l_api_name || ' ' ||
1392 p_input_date || ' ' || p_org_id || ' '
1393 || SQLERRM||' ' ||SQLCODE);
1394 fnd_msg_pub.add;
1395 RAISE;
1396
1397 END GET_FISCAL_MONTH_END;
1398 -------------------------------------------------------------------------------
1399 -- FUNCTION
1400 -- GET_FISCAL_QTR_END
1401 --
1402 -- Note
1403 -- This procedure will get the end of the fiscal qtr
1404 -- given date and org_id.
1405 -------------------------------------------------------------------------------
1406
1407 FUNCTION GET_FISCAL_QTR_END
1408 (
1409 p_input_date IN DATE DEFAULT sysdate
1410 ,p_org_id IN NUMBER
1411 ) RETURN DATE IS
1412 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_qtr_end';
1413 l_date DATE;
1414
1415 /*
1416 CURSOR period_info
1417 IS
1418 SELECT b.end_date
1419 FROM gl_sets_of_books a, gl_periods b
1420 WHERE a.set_of_books_id = (select set_of_books_id FROM
1421 ozf_sys_parameters_all WHERE org_id = p_org_id)
1422 AND b.period_set_name = a.period_set_name
1423 AND b.period_type=quarter_type
1424 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
1425 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
1426 */
1427 CURSOR period_info
1428 IS
1429 SELECT b.end_date
1430 FROM gl_periods b
1431 WHERE b.period_set_name = default_calender
1432 AND b.period_type=quarter_type
1433 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
1434 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
1435
1436 BEGIN
1437
1438 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1439 OPEN period_info;
1440 FETCH period_info INTO l_date;
1441
1442 IF period_info%NOTFOUND THEN
1443 CLOSE period_info;
1444 RAISE fnd_api.g_exc_unexpected_error;
1445 END IF;
1446
1447 CLOSE period_info;
1448
1449 --ams_utility_pvt.debug_message('fisical year --' || l_date);
1450
1451 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1452
1453 RETURN(l_date);
1454
1455 EXCEPTION
1456 WHEN fnd_api.g_exc_unexpected_error THEN
1457 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1458 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1459 fnd_message.set_token('ROW', l_api_name || ' ' ||
1460 p_input_date || ' ' || p_org_id || ' '
1461 || ' -- NO DATA FOUND -- ' );
1462 fnd_msg_pub.add;
1463 RAISE;
1464
1465 WHEN OTHERS THEN
1466 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1467 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1468 fnd_message.set_token('ROW', l_api_name || ' ' ||
1469 p_input_date || ' ' || p_org_id || ' '
1470 || SQLERRM||' ' ||SQLCODE);
1471 fnd_msg_pub.add;
1472 RAISE;
1473
1474 END GET_FISCAL_QTR_END;
1475 -------------------------------------------------------------------------------
1476 -- FUNCTION
1477 -- GET_FISCAL_YEAR_END
1478 --
1479 -- Note
1480 -- This procedure will get the end of the fiscal month
1481 -- given date and org_id.
1482 -------------------------------------------------------------------------------
1483
1484 FUNCTION GET_FISCAL_YEAR_END
1485 (
1486 p_input_date IN DATE DEFAULT sysdate
1487 ,p_org_id IN NUMBER
1488 ) RETURN DATE IS
1489 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_fiscal_year_end';
1490 l_date DATE;
1491
1492 /*
1493 CURSOR period_info
1494 IS
1495 SELECT b.end_date
1496 FROM gl_sets_of_books a, gl_periods b
1497 WHERE a.set_of_books_id = (select set_of_books_id FROM
1498 ozf_sys_parameters_all WHERE org_id = p_org_id)
1499 AND b.period_set_name = a.period_set_name
1500 AND b.period_type=year_type
1501 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
1502 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
1503 */
1504 CURSOR period_info
1505 IS
1506 SELECT b.end_date
1507 FROM gl_periods b
1508 WHERE b.period_set_name = default_calender
1509 AND b.period_type=year_type
1510 AND TRUNC(p_input_date) >= TRUNC(b.start_date)
1511 AND TRUNC(p_input_date) <= TRUNC(b.end_date);
1512
1513 BEGIN
1514
1515 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1516 OPEN period_info;
1517 FETCH period_info INTO l_date;
1518
1519 IF period_info%NOTFOUND THEN
1520 CLOSE period_info;
1521 RAISE fnd_api.g_exc_unexpected_error;
1522 END IF;
1523
1524 CLOSE period_info;
1525
1526 --ams_utility_pvt.debug_message('fisical year --' || l_date);
1527
1528 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1529
1530 RETURN(l_date);
1531
1532 EXCEPTION
1533 WHEN fnd_api.g_exc_unexpected_error THEN
1534 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1535 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1536 fnd_message.set_token('ROW', l_api_name || ' ' ||
1537 p_input_date || ' ' || p_org_id || ' '
1538 || ' -- NO DATA FOUND -- ' );
1539 fnd_msg_pub.add;
1540 RAISE;
1541
1542 WHEN OTHERS THEN
1543 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_input_date || ' ' || SQLERRM(SQLCODE));
1544 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1545 fnd_message.set_token('ROW', l_api_name || ' ' ||
1546 p_input_date || ' ' || p_org_id || ' '
1547 || SQLERRM||' ' ||SQLCODE);
1548 fnd_msg_pub.add;
1549 RAISE;
1550
1551 END GET_FISCAL_YEAR_END;
1552 -------------------------------------------------------------------------------
1553 -- FUNCTION
1554 -- GET_QTR_FROM_MONTH
1555 --
1556 -- Note
1557 -- This procedure will get the qtr name
1558 -- given month name and org_id.
1559 -------------------------------------------------------------------------------
1560
1561 FUNCTION GET_QTR_FROM_MONTH
1562 (
1563 p_period_name IN VARCHAR2
1564 ,p_org_id IN NUMBER
1565 ) RETURN VARCHAR2 IS
1566 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_qtr_from_month';
1567 l_period_name VARCHAR2(15);
1568 l_period_date DATE;
1569
1570 CURSOR cur_period_date
1571 IS
1572 SELECT b.start_date
1573 FROM gl_periods b
1574 WHERE b.period_set_name = default_calender
1575 AND b.period_type=month_type
1576 AND b.period_name =p_period_name;
1577
1578 CURSOR cur_period_name(l_start_date DATE)
1579 IS
1580 SELECT b.period_name period_name
1581 FROM gl_periods b
1582 WHERE b.period_set_name = default_calender
1583 AND b.period_type=quarter_type
1584 AND TRUNC(l_start_date) >= TRUNC(b.start_date)
1585 AND TRUNC(l_start_date) <= TRUNC(b.end_date);
1586 BEGIN
1587
1588 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1589 OPEN cur_period_date;
1590 FETCH cur_period_date INTO l_period_date;
1591
1592 IF cur_period_date%NOTFOUND THEN
1593 CLOSE cur_period_date;
1594 RAISE fnd_api.g_exc_unexpected_error;
1595 END IF;
1596
1597 CLOSE cur_period_date;
1598
1599 OPEN cur_period_name(l_period_date);
1600 FETCH cur_period_name INTO l_period_name;
1601
1602 IF cur_period_name%NOTFOUND THEN
1603 CLOSE cur_period_name;
1604 RAISE fnd_api.g_exc_unexpected_error;
1605 END IF;
1606
1607 CLOSE cur_period_name;
1608
1609 --ams_utility_pvt.debug_message('fiscal quarter period name--' || l_period_name);
1610
1611 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1612
1613 RETURN(l_period_name);
1614
1615 EXCEPTION
1616 WHEN fnd_api.g_exc_unexpected_error THEN
1617 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_period_name || ' ' || SQLERRM(SQLCODE));
1618 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1619 fnd_message.set_token('ROW', l_api_name || ' ' ||
1620 p_period_name || ' ' || p_org_id || ' '
1621 || ' -- NO DATA FOUND -- ' );
1622 fnd_msg_pub.add;
1623 RAISE;
1624
1625 WHEN OTHERS THEN
1626 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_period_name || ' ' || SQLERRM(SQLCODE));
1627 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1628 fnd_message.set_token('ROW', l_api_name || ' ' ||
1629 p_period_name || ' ' || p_org_id || ' '
1630 || SQLERRM||' ' ||SQLCODE);
1631 fnd_msg_pub.add;
1632 RAISE;
1633
1634 END GET_QTR_FROM_MONTH;
1635 -------------------------------------------------------------------------------
1636 -- FUNCTION
1637 -- GET_YEAR_FROM_MONTH
1638 --
1639 -- Note
1640 -- This procedure will get the year name
1641 -- given month name and org_id.
1642 -------------------------------------------------------------------------------
1643
1644 FUNCTION GET_YEAR_FROM_MONTH
1645 (
1646 p_period_name IN VARCHAR2
1647 ,p_org_id IN NUMBER
1648 ) RETURN VARCHAR2 IS
1649 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_year_from_qtr';
1650 l_period_name VARCHAR2(15);
1651 l_period_date DATE;
1652
1653 CURSOR cur_period_date
1654 IS
1655 SELECT b.start_date
1656 FROM gl_periods b
1657 WHERE b.period_set_name = default_calender
1658 AND b.period_type=month_type
1659 AND b.period_name =p_period_name;
1660
1661 CURSOR cur_period_name(l_start_date DATE)
1662 IS
1663 SELECT b.period_name period_name
1664 FROM gl_periods b
1665 WHERE b.period_set_name = default_calender
1666 AND b.period_type=year_type
1667 AND TRUNC(l_start_date) >= TRUNC(b.start_date)
1668 AND TRUNC(l_start_date) <= TRUNC(b.end_date);
1669 BEGIN
1670
1671 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1672 OPEN cur_period_date;
1673 FETCH cur_period_date INTO l_period_date;
1674
1675 IF cur_period_date%NOTFOUND THEN
1676 CLOSE cur_period_date;
1677 RAISE fnd_api.g_exc_unexpected_error;
1678 END IF;
1679
1680 CLOSE cur_period_date;
1681
1682 OPEN cur_period_name(l_period_date);
1683 FETCH cur_period_name INTO l_period_name;
1684
1685 IF cur_period_name%NOTFOUND THEN
1686 CLOSE cur_period_name;
1687 RAISE fnd_api.g_exc_unexpected_error;
1688 END IF;
1689
1690 CLOSE cur_period_name;
1691
1692 --ams_utility_pvt.debug_message('fiscal quarter period name--' || l_period_name);
1693
1694 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1695
1696 RETURN(l_period_name);
1697
1698 EXCEPTION
1699 WHEN fnd_api.g_exc_unexpected_error THEN
1700 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_period_name || ' ' || SQLERRM(SQLCODE));
1701 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1702 fnd_message.set_token('ROW', l_api_name || ' ' ||
1703 p_period_name || ' ' || p_org_id || ' '
1704 || ' -- NO DATA FOUND -- ' );
1705 fnd_msg_pub.add;
1706 RAISE;
1707
1708 WHEN OTHERS THEN
1709 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_period_name || ' ' || SQLERRM(SQLCODE));
1710 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1711 fnd_message.set_token('ROW', l_api_name || ' ' ||
1712 p_period_name || ' ' || p_org_id || ' '
1713 || SQLERRM||' ' ||SQLCODE);
1714 fnd_msg_pub.add;
1715 RAISE;
1716
1717 END GET_YEAR_FROM_MONTH;
1718 -------------------------------------------------------------------------------
1719 -- FUNCTION
1720 -- GET_YEAR_FROM_QTR
1721 --
1722 -- Note
1723 -- This procedure will get the year name
1724 -- given qtr name and org_id.
1725 -------------------------------------------------------------------------------
1726
1727 FUNCTION GET_YEAR_FROM_QTR
1728 (
1729 p_period_name IN VARCHAR2
1730 ,p_org_id IN NUMBER
1731 ) RETURN VARCHAR2 IS
1732 l_api_name CONSTANT VARCHAR2(300) := 'bim_set_of_books:get_year_from_qtr';
1733 l_period_name VARCHAR2(15);
1734 l_period_date DATE;
1735
1736 CURSOR cur_period_date
1737 IS
1738 SELECT b.start_date
1739 FROM gl_periods b
1740 WHERE b.period_set_name = default_calender
1741 AND b.period_type=quarter_type
1742 AND b.period_name =p_period_name;
1743
1744 CURSOR cur_period_name(l_start_date DATE)
1745 IS
1746 SELECT b.period_name period_name
1747 FROM gl_periods b
1748 WHERE b.period_set_name = default_calender
1749 AND b.period_type=year_type
1750 AND TRUNC(l_start_date) >= TRUNC(b.start_date)
1751 AND TRUNC(l_start_date) <= TRUNC(b.end_date);
1752 BEGIN
1753
1754 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'START');
1755 OPEN cur_period_date;
1756 FETCH cur_period_date INTO l_period_date;
1757
1758 IF cur_period_date%NOTFOUND THEN
1759 CLOSE cur_period_date;
1760 RAISE fnd_api.g_exc_unexpected_error;
1761 END IF;
1762
1763 CLOSE cur_period_date;
1764
1765 OPEN cur_period_name(l_period_date);
1766 FETCH cur_period_name INTO l_period_name;
1767
1768 IF cur_period_name%NOTFOUND THEN
1769 CLOSE cur_period_name;
1770 RAISE fnd_api.g_exc_unexpected_error;
1771 END IF;
1772
1773 CLOSE cur_period_name;
1774
1775 --ams_utility_pvt.debug_message('fiscal quarter period name--' || l_period_name);
1776
1777 ams_utility_pvt.debug_message('PUBLIC API: ' || l_api_name || 'END');
1778
1779 RETURN(l_period_name);
1780
1781 EXCEPTION
1782 WHEN fnd_api.g_exc_unexpected_error THEN
1783 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_period_name || ' ' || SQLERRM(SQLCODE));
1784 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1785 fnd_message.set_token('ROW', l_api_name || ' ' ||
1786 p_period_name || ' ' || p_org_id || ' '
1787 || ' -- NO DATA FOUND -- ' );
1788 fnd_msg_pub.add;
1789 RAISE;
1790
1791 WHEN OTHERS THEN
1792 fnd_file.put_line(fnd_file.log,'ERROR-' || l_api_name || ' ' || p_period_name || ' ' || SQLERRM(SQLCODE));
1793 fnd_message.set_name('AMS', 'API_DEBUG_MESSAGE');
1794 fnd_message.set_token('ROW', l_api_name || ' ' ||
1795 p_period_name || ' ' || p_org_id || ' '
1796 || SQLERRM||' ' ||SQLCODE);
1797 fnd_msg_pub.add;
1798 RAISE;
1799
1800 END GET_YEAR_FROM_QTR;
1801 END BIM_SET_OF_BOOKS;