[Home] [Help]
PACKAGE BODY: APPS.BIM_IRES_COLLECTION_PKG
Source
1 PACKAGE BODY BIM_IRES_COLLECTION_PKG AS
2 /* $Header: bimiresb.pls 115.1 2002/04/29 10:21:23 pkm ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'BIM_IRES_COLLECTION_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimiresb.pls';
6
7 ---------------------------------------------------------------------
8 -- FUNCTION
9 -- calculate_days
10 -- NOTE
11 -- PARAMETER
12 -- p_start_date IN DATE,
13 -- p_end_date IN DATE,
14 -- p_aggregate IN VARCHAR2
15 -- p_period IN VARCHAR2
16 -- RETURN NUMBER
17 ---------------------------------------------------------------------
18
19 FUNCTION calculate_days(
20 p_start_date DATE
21 ,p_end_date DATE
22 ,p_aggregate VARCHAR2
23 ,p_period VARCHAR2) return NUMBER
24 IS
25
26 l_date DATE;
27 l_days NUMBER;
28
29 l_day_code VARCHAR2(30);
30 l_week_code VARCHAR2(30);
31 l_month_code VARCHAR2(30);
32 l_quarter_code VARCHAR2(30);
33 l_year_code VARCHAR2(30);
34
35 l_cur_period_start_date DATE;
36 l_cur_period_end_date DATE;
37 l_pre_period_start_date DATE;
38 l_pre_period_end_date DATE;
39
40 l_period_start_date DATE;
41 l_period_end_date DATE;
42 l_temp_start_date DATE;
43 l_temp_end_date DATE;
44 l_org_id NUMBER;
45
46 BEGIN
47
48 l_day_code := 'DAY';
49 l_week_code := 'WEEK';
50 l_month_code := 'MONTH';
51 l_quarter_code := 'QUARTER';
52 l_year_code := 'YEAR';
53
54 l_date := sysdate - 1;
55 l_org_id := 0;
56
57 IF (p_aggregate = l_day_code) THEN
58 IF (p_period = 'Current') THEN
59 IF (p_end_date >= l_date) THEN
60 RETURN 1;
61 ELSE
62 RETURN 0;
63 END IF;
64 ELSE
65 IF (p_end_date >= l_date - 1) THEN
66 RETURN 1;
67 ELSE
68 RETURN 0;
69 END IF;
70 END IF;
71 END IF;
72
73 IF (p_aggregate = l_month_code) THEN
74 l_cur_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_date, l_org_id);
75 l_cur_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_END(l_date, l_org_id);
76 IF (p_period = 'Previous') THEN
77 l_pre_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_cur_period_start_date - 1, l_org_id);
78 l_pre_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_END(l_cur_period_start_date - 1, l_org_id);
79 END IF;
80 ELSIF (p_aggregate = l_quarter_code) THEN
81 l_cur_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_date, l_org_id);
82 l_cur_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_date, l_org_id);
83 IF (p_period = 'Previous') THEN
84 l_pre_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_cur_period_start_date - 1, l_org_id);
85 l_pre_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_cur_period_start_date - 1, l_org_id);
86 END IF;
87 ELSIF (p_aggregate = l_year_code) THEN
88 l_cur_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_ROLL_YEAR_START(l_date, l_org_id);
89 l_cur_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_date, l_org_id);
90 IF (p_period = 'Previous') THEN
91 l_pre_period_start_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_ROLL_YEAR_START(l_date, l_org_id);
92 l_pre_period_end_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_ROLL_YEAR_END(l_date, l_org_id);
93 END IF;
94 ELSIF (p_aggregate = l_week_code) THEN
95 l_cur_period_start_date := l_date-7;
96 l_cur_period_end_date := l_date;
97 IF (p_period = 'Previous') THEN
98 l_pre_period_start_date := l_date-15;
99 l_pre_period_end_date := l_date-8;
100 END IF;
101 END IF;
102
103 /*
104
105 here u have values for :>>
106
107 1) What Increment-periodtype u r calculating for : DAY, Week etc..
108 2) ldate == date w.r.t. which u r calculating #ofDays
109 3) ur period start date
110 4) ur period end date
111 5) previous period start date
112 6) previous period end date
113
114 */
115
116 IF (p_period = 'Previous') THEN
117 l_days := trunc(l_date) - trunc(l_cur_period_start_date);
118 IF trunc(l_cur_period_end_date) = trunc(l_date) THEN
119 l_period_end_date := l_pre_period_end_date;
120 ELSE
121 l_period_end_date := l_pre_period_start_date + l_days;
122 END IF;
123 l_period_start_date := l_pre_period_start_date;
124 ELSE
125 l_period_start_date := l_cur_period_start_date;
126 l_period_end_date := l_date;
127 END IF;
128
129 --dbms_output.put_line('period_start_date -- ' || l_period_start_date);
130 --dbms_output.put_line('period_end_date -- ' || l_period_end_date);
131
132 --dbms_output.put_line('p_start_date -- ' || p_start_date);
133 --dbms_output.put_line('p_end_date -- ' || p_end_date);
134
135 l_days := 0;
136 IF (p_start_date > l_period_end_date) THEN
137 l_days := 0;
138 ELSIF (p_end_date < l_period_start_date) THEN
139 l_days := 0;
140 ELSIF (p_start_date <= l_period_start_date) THEN
141 l_temp_start_date := l_period_start_date;
142 IF(p_end_date >= l_period_end_date) THEN
143 l_temp_end_date := l_period_end_date;
144 ELSE
145 l_temp_end_date := p_end_date;
146 END IF;
147 l_days := trunc(l_temp_end_date) - trunc(l_temp_start_date) + 1;
148 ELSIF (p_start_date > l_period_start_date) THEN
149 l_temp_start_date := p_start_date;
150 IF(p_end_date >= l_period_end_date) THEN
151 l_temp_end_date := l_period_end_date;
152 ELSE
153 l_temp_end_date := p_end_date;
154 END IF;
155 l_days := trunc(l_temp_end_date) - trunc(l_temp_start_date) + 1;
156 END IF;
157
158 --dbms_output.put_line('l_days -- ' || l_days);
159
160 RETURN (l_days);
161
162 END calculate_days;
163
164 /* Overloaded Function for MQY cost caclulation (number of days) */
165
166 FUNCTION calculate_days( --overloaded function
167 p_start_date DATE
168 ,p_end_date DATE
169 ,p_aggregate VARCHAR2
170 ,p_period VARCHAR2
171 ,p_date DATE
172 ,p_cur_period_start_date DATE
173 ,p_cur_period_end_date DATE
174 ,p_prev_period_start_date DATE
175 ,p_prev_period_end_date DATE) return NUMBER
176 IS
177
178 l_date DATE;
179 l_days NUMBER;
180
181 l_month_code VARCHAR2(30);
182 l_quarter_code VARCHAR2(30);
183 l_year_code VARCHAR2(30);
184
185 l_cur_period_start_date DATE;
186 l_cur_period_end_date DATE;
187 l_pre_period_start_date DATE;
188 l_pre_period_end_date DATE;
189
190 l_period_start_date DATE;
191 l_period_end_date DATE;
192 l_temp_start_date DATE;
193 l_temp_end_date DATE;
194 l_org_id NUMBER;
195
196 BEGIN
197
198 l_month_code := 'MONTH';
199 l_quarter_code := 'QUARTER';
200 l_year_code := 'YEAR';
201
202 l_date := p_date;
203 l_org_id := 0;
204
205 l_cur_period_start_date := p_cur_period_start_date;
206 l_cur_period_end_date := p_cur_period_end_date;
207
208 IF (p_period = 'Previous') THEN
209 l_pre_period_start_date := p_prev_period_start_date;
210 l_pre_period_end_date := p_prev_period_end_date;
211 END IF;
212
213 IF (p_period = 'Previous') THEN
214 l_days := trunc(l_date) - trunc(l_cur_period_start_date);
215 IF trunc(l_cur_period_end_date) = trunc(l_date) THEN
216 l_period_end_date := l_pre_period_end_date;
217 ELSE
218 l_period_end_date := l_pre_period_start_date + l_days;
219 END IF;
220 l_period_start_date := l_pre_period_start_date;
221 ELSE
222 l_period_start_date := l_cur_period_start_date;
223 l_period_end_date := l_date;
224 END IF;
225
226 --dbms_output.put_line('period_start_date -- ' || l_period_start_date);
227 --dbms_output.put_line('period_end_date -- ' || l_period_end_date);
228
229 --dbms_output.put_line('p_start_date -- ' || p_start_date);
230 --dbms_output.put_line('p_end_date -- ' || p_end_date);
231
232 l_days := 0;
233 IF (p_start_date > l_period_end_date) THEN
234 l_days := 0;
235 ELSIF (p_end_date < l_period_start_date) THEN
236 l_days := 0;
237 ELSIF (p_start_date <= l_period_start_date) THEN
238 l_temp_start_date := l_period_start_date;
239 IF(p_end_date >= l_period_end_date) THEN
240 l_temp_end_date := l_period_end_date;
241 ELSE
242 l_temp_end_date := p_end_date;
243 END IF;
244 l_days := trunc(l_temp_end_date) - trunc(l_temp_start_date) + 1;
245 ELSIF (p_start_date > l_period_start_date) THEN
246 l_temp_start_date := p_start_date;
247 IF(p_end_date >= l_period_end_date) THEN
248 l_temp_end_date := l_period_end_date;
249 ELSE
250 l_temp_end_date := p_end_date;
251 END IF;
252 l_days := trunc(l_temp_end_date) - trunc(l_temp_start_date) + 1;
253 END IF;
254
255 -- dbms_output.put_line('l_days -- ' || l_days);
256
257 RETURN (l_days);
258 END calculate_days;
259 FUNCTION get_min_date return DATE IS
260 l_date DATE;
261 BEGIN
262 select min(start_date) into l_date
263 from bim_rep_history
264 where object='CAMPAIGN';
265 return l_date;
266 END get_min_date;
267 END BIM_IRES_COLLECTION_PKG;