1 PACKAGE BODY hri_bpl_time AS
2 /* $Header: hribtime.pkb 120.1 2011/11/17 11:49:25 pathota ship $ */
3 --
4 g_start_date DATE;
5 g_end_date DATE;
6 g_ip_start_date DATE;
7 g_ip_end_date DATE;
8 g_ip_start_period varchar2(30);
9 g_ip_end_period varchar2(30);
10 --
11
12 /**************************************************************************
13 Description : For a given date, this function finds the start date of the
14 period in which the date lies.
15 The period can be 'YEAR','SEMIYEAR','QUARTERYEAR',
16 'BIMONTH','MONTH'.
17 Preconditions : None
18 In Parameters : p_start_date IN DATE
19 p_period IN VARCHAR2
20 Post Sucess : Returns the start date of the period
21 Post Failure : Raise error no_data_found
22 ***************************************************************************/
23
24 FUNCTION get_period_start_date(p_start_date IN DATE,
25 p_period IN VARCHAR2)
26 RETURN DATE IS
27 --
28 -- Cursors
29 --
30 cursor c_year is
31 select start_date
32 from HRI_TIME_YEAR_V
33 where p_start_date between start_date and end_date;
34 --
35 cursor c_semi_year is
36 select start_date
37 from HRI_TIME_SEMIYEAR_V
38 where p_start_date between start_date and end_date;
39 --
40 cursor c_quarter_year is
41 select start_date
42 from HRI_TIME_QTR_V
43 where p_start_date between start_date and end_date;
44 --
45 cursor c_bimonth is
46 select start_date
47 from HRI_TIME_BIMONTH_V
48 where p_start_date between start_date and end_date;
49 --
50 cursor c_month is
51 select start_date
52 from HRI_TIME_MONTH_V
53 where p_start_date between start_date and end_date;
54 --
55 BEGIN
56 --
57 IF (p_start_date is NULL or p_period IS NULL) THEN
58 --
59 raise NO_DATA_FOUND;
60 --
61 ELSIF p_start_date = g_ip_start_date and p_period = g_ip_start_period THEN
62 --
63 return nvl(g_start_date,p_start_date);
64 --
65 ELSE
66 -- Flush out the values in the cache
67 g_start_date := null;
68 g_ip_start_date := null;
69 g_ip_start_period := null;
70 --
71 if upper(p_period)='YEAR' then
72 open c_year;
73 fetch c_year into g_start_date;
74 close c_year;
75 elsif upper(p_period)='SEMIYEAR' then
76 open c_semi_year;
77 fetch c_semi_year into g_start_date;
78 close c_semi_year;
79 elsif upper(p_period)='QUARTERYEAR' then
80 open c_quarter_year;
81 fetch c_quarter_year into g_start_date;
82 close c_quarter_year;
83 elsif upper(p_period)='BIMONTH' then
84 open c_bimonth;
85 fetch c_bimonth into g_start_date;
86 close c_bimonth;
87 elsif upper(p_period)='MONTH' then
88 open c_month;
89 fetch c_month into g_start_date;
90 close c_month;
91 else
92 raise no_data_found;
93 end if;
94 --
95 g_ip_start_date := p_start_date;
96 g_ip_start_period := p_period ;
97 --
98 END IF;
99 --
100 return nvl(g_start_date,p_start_date);
101 EXCEPTION
102 WHEN OTHERS THEN
103 raise NO_DATA_FOUND;
104 END get_period_start_date;
105 --
106
107
108 /**************************************************************************
109 Description : For a given date, this function finds the end date of the
110 period in which the date lies.
111 The period can be 'YEAR','SEMIYEAR','QUARTERYEAR',
112 'BIMONTH','MONTH'.
113 Preconditions : None
114 In Parameters : p_end_date IN DATE
115 p_period IN VARCHAR2
116 Post Sucess : Returns the end date fo the period
117 Post Failure : Raise error no_data_found
118 ***************************************************************************/
119
120 FUNCTION get_period_end_date(p_end_date IN DATE,
121 p_period IN VARCHAR2)
122 RETURN DATE IS
123 --
124 -- Cursors
125 --
126 cursor c_year is
127 select end_date
128 from HRI_TIME_YEAR_V
129 where p_end_date between start_date and end_date;
130 --
131 cursor c_semi_year is
132 select end_date
133 from HRI_TIME_SEMIYEAR_V
134 where p_end_date between start_date and end_date;
135 --
136 cursor c_quarter_year is
137 select end_date
138 from HRI_TIME_QTR_V
139 where p_end_date between start_date and end_date;
140 --
141 cursor c_bimonth is
142 select end_date
143 from HRI_TIME_BIMONTH_V
144 where p_end_date between start_date and end_date;
145 --
146 cursor c_month is
147 select end_date
148 from HRI_TIME_MONTH_V
149 where p_end_date between start_date and end_date;
150 --
151 BEGIN
152 --
153 IF (p_end_date is NULL or p_period IS NULL) THEN
154 --
155 raise NO_DATA_FOUND;
156 --
157 ELSIF p_end_date = g_ip_end_date and p_period = g_ip_end_period THEN
158 return nvl(g_end_date,p_end_date);
159 ELSE
160 -- Flush out the values in the cache
161 g_end_date := null;
162 g_ip_end_date := null;
163 g_ip_end_period := null;
164 --
165 if upper(p_period)='YEAR' then
166 open c_year;
167 fetch c_year into g_end_date;
168 close c_year;
169 elsif upper(p_period)='SEMIYEAR' then
170 open c_semi_year;
171 fetch c_semi_year into g_end_date;
172 close c_semi_year;
173 elsif upper(p_period)='QUARTERYEAR' then
174 open c_quarter_year;
175 fetch c_quarter_year into g_end_date;
176 close c_quarter_year;
177 elsif upper(p_period)='BIMONTH' then
178 open c_bimonth;
179 fetch c_bimonth into g_end_date;
180 close c_bimonth;
181 elsif upper(p_period)='MONTH' then
182 open c_month;
183 fetch c_month into g_end_date;
184 close c_month;
185 else
186 raise no_data_found;
187 end if;
188 --
189 g_ip_end_date := p_end_date;
190 g_ip_end_period := p_period;
191 --
192 END IF;
193 --
194 return nvl(g_end_date, p_end_date);
195 --
196 EXCEPTION
197 WHEN OTHERS THEN
198 raise NO_DATA_FOUND;
199 END get_period_end_date;
200 --
201 END hri_bpl_time;
[Home] [Help]