1 PACKAGE BODY GL_CALENDAR_PKG as
2 /* $Header: glustclb.pls 120.2 2005/05/19 22:38:13 djogg noship $ */
3
4 PROCEDURE get_num_periods_in_date_range(
5 calendar_name VARCHAR2,
6 period_type VARCHAR2,
7 start_date DATE,
8 end_date DATE,
9 check_missing BOOLEAN,
10 num_periods OUT NOCOPY NUMBER,
11 return_code OUT NOCOPY VARCHAR2,
12 unmapped_date OUT NOCOPY DATE) IS
13 low_period_name VARCHAR2(15);
14 high_period_name VARCHAR2(15);
15 BEGIN
16
17 return_code := success;
18
19 --Added by Service Contracts Team to handle monthly revenue recognition
20
21 Begin
22 if nvl(fnd_profile.value ('OKS_ACCDUR_BASIS'),'MONTHLY') = 'MONTHLY' Then
23 num_periods := ceil(months_between(end_date, start_date));
24 --Logic to handle one day
25 if num_periods = 0 then
26 num_periods := 1;
27 end if;
28 return;
29 end if;
30 Exception
31 When Others Then
32 return_code := 'ERROR';
33 return;
34 End;
35
36 --Added by Service Contracts Team to handle monthly revenue recognition
37
38 BEGIN
39 SELECT period_name
40 INTO low_period_name
41 FROM gl_date_period_map
42 WHERE period_set_name = calendar_name
43 AND period_type = get_num_periods_in_date_range.period_type
44 AND accounting_date = start_date;
45
46 IF (low_period_name = 'NOT ASSIGNED') THEN
47 return_code := bad_start;
48 unmapped_date := start_date;
49 END IF;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 return_code := bad_start;
53 unmapped_date := start_date;
54 END;
55
56 BEGIN
57 SELECT period_name
58 INTO high_period_name
59 FROM gl_date_period_map
60 WHERE period_set_name = calendar_name
61 AND period_type = get_num_periods_in_date_range.period_type
62 AND accounting_date = end_date;
63
64 IF (high_period_name = 'NOT ASSIGNED') THEN
65 return_code := bad_end;
66 unmapped_date := end_date;
67 END IF;
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 return_code := bad_end;
71 unmapped_date := end_date;
72 END;
73
74 IF ( (check_missing)
75 AND (return_code = 'SUCCESS')
76 ) THEN
77 BEGIN
78 SELECT min(accounting_date)
79 INTO unmapped_date
80 FROM gl_date_period_map
81 WHERE period_set_name = calendar_name
82 AND period_type = get_num_periods_in_date_range.period_type
83 AND accounting_date between start_date and end_date
84 AND period_name = 'NOT ASSIGNED';
85
86 IF (unmapped_date IS NOT NULL) THEN
87 return_code := unmapped_day;
88 END IF;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 unmapped_date := null;
92 END;
93 END IF;
94
95 SELECT count(*)
96 INTO num_periods
97 FROM gl_periods
98 WHERE period_set_name = calendar_name
99 AND period_type = get_num_periods_in_date_range.period_type
100 AND adjustment_period_flag = 'N'
101 AND start_date <= get_num_periods_in_date_range.end_date
102 AND end_date >= get_num_periods_in_date_range.start_date;
103 END get_num_periods_in_date_range;
104
105 END GL_CALENDAR_PKG;