[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_CALENDAR_PKG
Source
1 PACKAGE BODY POA_DBI_CALENDAR_PKG AS
2 /* $Header: poadbicalb.pls 115.3 2002/10/01 21:25:12 mangupta 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 min(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 start_date
70 into l_date
71 from (select p.start_date
72 from fii_time_ent_period p, fii_time_ent_qtr q
73 where p.ent_qtr_id=q.ent_qtr_id
74 and ((p.sequence>=l_curr_period+1 and q.ent_year_id=l_curr_year-1)
75 or (p.sequence>=1 and q.ent_year_id=l_curr_year))
76 order by p.start_date)
77 where rownum <= 1;
78
79 end if;
80
81 if(period_type = 'WTD') then
82
83 select start_date
84 into l_week_start_date
85 from fii_time_week
86 where as_of_date between start_date and end_date;
87
88 select min(start_date)
89 into l_date
90 from fii_time_week
91 where start_date>=l_week_start_date-7*12;
92
93 end if;
94
95 return l_date;
96
97 exception
98 when others then
99 return bis_common_parameters.get_global_start_date;
100
101 end;
102
103 Function previous_report_start_date(as_of_date date,
104 period_type varchar2,
105 comparison_type varchar2)
106 return date is
107
108 l_prev_date date;
109 l_date date;
110
111 begin
112
113 l_prev_date := previous_period_asof_date(as_of_date, period_type, comparison_type);
114
115 l_date := current_report_start_date(l_prev_date, period_type, comparison_type);
116
117 return l_date;
118
119 end;
120
121 Function current_period_start_date(as_of_date date,
122 period_type varchar2)
123 return date is
124
125 l_date date;
126
127 begin
128
129 if(period_type='YTD') then
130 l_date := fii_time_api.ent_cyr_start(as_of_date);
131
132 elsif(period_type='QTD') then
133 l_date := fii_time_api.ent_cqtr_start(as_of_date);
134
135 elsif(period_type='MTD') then
136 l_date := fii_time_api.ent_cper_start(as_of_date);
137
138 elsif(period_type = 'WTD') then
139 l_date := fii_time_api.cwk_start(as_of_date);
140
141 end if;
142
143 return l_date;
144
145 exception
146 when others then
147 return bis_common_parameters.get_global_start_date;
148
149 end;
150
151 Function previous_period_start_date(as_of_date date,
152 period_type varchar2,
153 comparison_type varchar2)
154 return date is
155
156 l_prev_date date;
157 l_date date;
158
159 begin
160
161 l_prev_date := previous_period_asof_date(as_of_date, period_type, comparison_type);
162
163 l_date := current_period_start_date(l_prev_date, period_type);
164
165 return l_date;
166 exception
167 when others then
168 return bis_common_parameters.get_global_start_date;
169 end;
170
171 Function previous_period_asof_date(as_of_date date,
172 period_type varchar2,
173 comparison_type varchar2)
174 return date is
175
176 l_date date;
177
178 begin
179
180 if(period_type='YTD') then
181
182 l_date := fii_time_api.ent_sd_lyr_end(as_of_date);
183
184 elsif(period_type='QTD') then
185
186 if(comparison_type = 'Y') then
187 l_date := fii_time_api.ent_sd_lysqtr_end(as_of_date);
188 else
189 l_date := fii_time_api.ent_sd_pqtr_end(as_of_date);
190 end if;
191
192 elsif(period_type='MTD') then
193
194 if(comparison_type = 'Y') then
195 l_date := fii_time_api.ent_sd_lysper_end(as_of_date);
196 else
197 l_date := fii_time_api.ent_sd_pper_end(as_of_date);
198 end if;
199
200 elsif(period_type='WTD') then
201
202 if(comparison_type = 'Y') then
203 l_date := fii_time_api.sd_lyswk(as_of_date);
204 else
205 l_date := fii_time_api.sd_pwk(as_of_date);
206 end if;
207
208 end if;
209
210 return l_date;
211 exception
212 when others then
213 return bis_common_parameters.get_global_start_date - 1; /* making sure it's b4 the min_date of current_report_date */
214 end;
215
216 end;