[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_CALENDAR_PKG
Source
1 PACKAGE BODY OPI_DBI_CALENDAR_PKG AS
2 /* $Header: OPIDRCALB.pls 115.3 2002/10/18 00:09:02 csheu noship $ */
3
4 Function current_report_start_date(as_of_date date,
5 period_type varchar2,
6 comparison_type varchar2)
7 return date is
8
9 l_date date;
10 l_curr_year number;
11 l_curr_qtr number;
12 l_curr_period number;
13 l_week_start_date date;
14 l_curr_p445 number;
15
16 begin
17
18 if(period_type = 'YTD') then
19
20 select sequence
21 into l_curr_year
22 from fii_time_ent_year
23 where as_of_date between start_date and end_date;
24
25 select start_date
26 into l_date
27 from fii_time_ent_year
28 where sequence=l_curr_year-3;
29
30 end if;
31
32 if(period_type = 'QTD') then
33
34 select sequence, ent_year_id
35 into l_curr_qtr, l_curr_year
36 from fii_time_ent_qtr
37 where as_of_date between start_date and end_date;
38
39 if(comparison_type = 'Y') then
40 select start_date
41 into l_date
42 from (select start_date
43 from fii_time_ent_qtr
44 where ((sequence>=l_curr_qtr+1 and ent_year_id=l_curr_year-1)
45 or (sequence>=1 and ent_year_id=l_curr_year))
46 order by start_date)
47 where rownum <= 1;
48 else
49 select start_date
50 into l_date
51 from (select start_date
52 from fii_time_ent_qtr
53 where ((sequence>=l_curr_qtr+1 and ent_year_id=l_curr_year-2)
54 or (sequence>=1 and ent_year_id=l_curr_year-1))
55 order by start_date)
56 where rownum <= 1;
57 end if;
58
59 end if;
60
61 if(period_type = 'MTD') then
62
63 select p.sequence, q.ent_year_id
64 into l_curr_period, l_curr_year
65 from fii_time_ent_period p, fii_time_ent_qtr q
66 where p.ent_qtr_id=q.ent_qtr_id
67 and as_of_date between p.start_date and p.end_date;
68
69 select p.start_date
70 into l_date
71 from fii_time_ent_period p, fii_time_ent_qtr q
72 where p.ent_qtr_id=q.ent_qtr_id
73 and p.sequence=l_curr_period
74 and q.ent_year_id=l_curr_year-1;
75
76 end if;
77
78 if(period_type = 'WTD') then
79
80 select start_date
81 into l_week_start_date
82 from fii_time_week
83 where as_of_date between start_date and end_date;
84
85 select start_date
86 into l_date
87 from fii_time_week
88 where start_date=l_week_start_date-7*12;
89
90 end if;
91
92 if l_date < bis_common_parameters.get_global_start_date
93 then
94 l_date := bis_common_parameters.get_global_start_date;
95 end if;
96
97
98 return l_date;
99
100 exception
101 when others then
102 return bis_common_parameters.get_global_start_date;
103
104 end;
105
106 Function previous_report_start_date(as_of_date date,
107 period_type varchar2,
108 comparison_type varchar2)
109 return date is
110
111 l_prev_date date;
112 l_date date;
113
114 begin
115
116 l_prev_date := previous_period_asof_date(as_of_date, period_type, comparison_type);
117
118 l_date := current_report_start_date(l_prev_date, period_type, comparison_type );
119
120 return l_date;
121
122 end;
123
124 Function current_period_start_date(as_of_date date,
125 period_type varchar2)
126 return date is
127
128 l_date date;
129
130 begin
131
132 if(period_type='YTD') then
133 l_date := fii_time_api.ent_cyr_start(as_of_date);
134
135 elsif(period_type='QTD') then
136 l_date := fii_time_api.ent_cqtr_start(as_of_date);
137
138 elsif(period_type='MTD') then
139 l_date := fii_time_api.ent_cper_start(as_of_date);
140
141 elsif(period_type = 'WTD') then
142 l_date := fii_time_api.cwk_start(as_of_date);
143
144 end if;
145
146 return l_date;
147
148 EXCEPTION
149 WHEN OTHERS
150 THEN RETURN BIS_COMMON_PARAMETERS.Get_Global_Start_Date;
151
152
153 end;
154
155 Function previous_period_start_date(as_of_date date,
156 period_type varchar2,
157 comparison_type varchar2)
158 return date is
159
160 l_prev_date date;
161 l_date date;
162
163 begin
164
165 l_prev_date := previous_period_asof_date(as_of_date, period_type, comparison_type);
166
167 l_date := current_period_start_date(l_prev_date, period_type);
168
169 return l_date;
170 exception
171 when others then
172 return bis_common_parameters.get_global_start_date;
173 end;
174
175 Function previous_period_asof_date(as_of_date date,
176 period_type varchar2,
177 comparison_type varchar2)
178 return date is
179
180 l_date date;
181
182 begin
183
184 if(period_type='YTD') then
185
186 l_date := fii_time_api.ent_sd_lyr_end(as_of_date);
187
188 elsif(period_type='QTD') then
189
190 if(comparison_type = 'Y') then
191 l_date := fii_time_api.ent_sd_lysqtr_end(as_of_date);
192 else
193 l_date := fii_time_api.ent_sd_pqtr_end(as_of_date);
194 end if;
195
196 elsif(period_type='MTD') then
197
198 if(comparison_type = 'Y') then
199 l_date := fii_time_api.ent_sd_lysper_end(as_of_date);
200 else
201 l_date := fii_time_api.ent_sd_pper_end(as_of_date);
202 end if;
203
204 elsif(period_type='WTD') then
205
206 if(comparison_type = 'Y') then
207 l_date := fii_time_api.sd_lyswk(as_of_date);
208 else
209 l_date := fii_time_api.sd_pwk(as_of_date);
210 end if;
211
212 end if;
213
214 return l_date;
215 exception
216 when others then
217 return bis_common_parameters.get_global_start_date - 1; /* making sure it's b4 the min_date of current_report_date */
218 end;
219
220 end;