1 PACKAGE BODY INV_PP_DateCheck_PVT AS
2 /* $Header: INVVPPDB.pls 115.1 2003/11/12 09:33:19 gbhagra noship $*/
3 --
4
5 /*Date_Valid
6 * returns
7 * 'Y' - current date falls within date range
8 * 'N' - current date does not fall within range
9 * Parameters
10 * org_id NUMBER - ID of the organization for which the rules
11 * engine is being run Passing NULL for org_id
12 * has no effect if date_type is always, fulldate,
13 * day, date, month, or year
14 * date_type VARCHAR2 - equivalent to Date_Type column in table.
15 * This value signals how to process the date info.
16 * If this value is NULL, Date_Valid assumes
17 * date_type is fulldate.
18 * from_val NUMBER - The from value. Equivalent to Date_Type_From in
19 * table.Ignored if date_type is fulldate or always.
20 * NULL value implies no "from" boundray.
21 * to_val NUMBER - The to value. Equivalent to Date_Type_From in table.
22 * Ignored if date_type is fulldate or always. NULL
23 * value implies no "to" boundray.
24 * from_date DATE - The from value used if date_type is fulldate.
25 * Equivalent to Effective_From in table.
26 * NULL value implies no "from" boundray.
27 * to_date DATE - The to value used if date_type is fulldate.
28 * Equivalent to Effective_to in table.
29 * NULL value implies no "from" boundray.
30 *
31 * Given a "from" date and a "to" date, Date_Valid checks
32 * to see if the current date falls in the given range. The date
33 * values passed in can be of different types (e.g. Day, Date, Shift, etc.).
34 * First, Date_valid finds the current date by getting sysdate from the
35 * database (calls function Get_Current_Date).
36 * Second, it converts the current date to the appropriate date
37 * date type (e.g. 15-OCT-99 becomes '6' for date_type Friday). Third,
38 * it checks to see if the current value is between the from and to
39 * values (calls function Check_Between).
40 */
41
42 FUNCTION Date_Valid
43 (org_id NUMBER, --Organization ID
44 date_type VARCHAR2,
45 from_val NUMBER, --same as Date_Type_From
46 to_val NUMBER, --same as Date_Type_To
47 from_date DATE, --same as Effective_From
48 to_date DATE) --same as Effective_To
49 return VARCHAR2 IS
50
51 cur_date DATE; --holds today's date
52 cur_val NUMBER; --holds the value for today for the given date_type
53 ret_val VARCHAR2(1);
54 cal_code VARCHAR2(10);
55 except_set NUMBER;
56 cur_seconds NUMBER;
57 set_name VARCHAR2(15);
58 date_type_num NUMBER;
59
60 cursor cal_param IS
61 SELECT calendar_code, calendar_exception_set_id
62 FROM MTL_PARAMETERS
63 WHERE organization_id = org_id;
64
65
66 cursor current_shifts IS
67 SELECT shift_num
68 FROM BOM_SHIFT_DATES
69 WHERE calendar_code = cal_code AND
70 exception_set_id = nvl(except_set, -1) AND
71 shift_date = cur_date AND
72 shift_num NOT IN (SELECT shift_num
73 FROM BOM_SHIFT_TIMES
74 WHERE calendar_code = cal_code AND
75 cur_seconds not between FROM_TIME and TO_TIME) AND
76 seq_num IS NOT NULL;
77
78 cursor current_week IS
79 SELECT seq_num
80 FROM BOM_CAL_WEEK_START_DATES
81 WHERE calendar_code = cal_code AND
82 exception_set_id = nvl(except_set, -1) AND
83 cur_date BETWEEN week_start_date AND next_date;
84
85
86 cursor current_cal_period IS
87 SELECT period_sequence_num
88 FROM BOM_PERIOD_START_DATES
89 WHERE calendar_code = cal_code AND
90 exception_set_id = nvl(except_set, -1) AND
91 cur_date between period_start_date and next_date;
92
93 cursor current_acct_period IS
94 SELECT period_num
95 FROM org_acct_periods
96 WHERE organization_id = org_id
97 AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(cur_date,org_id) BETWEEN period_start_date AND
98 schedule_close_date;
99
100 cursor period_set IS
101 SELECT period_set_name
102 FROM org_acct_periods
103 WHERE organization_id = org_id;
104
105 cursor current_quarter IS
106 SELECT quarter_num
107 FROM gl_periods
108 WHERE period_set_name = set_name AND
109 cur_date BETWEEN quarter_start_date AND
110 add_months(quarter_start_date, 3);
111
112
113 BEGIN
114
115 date_type_num := to_number(date_type);
116
117
118 --date-type = always, return true
119 if (date_type_num = c_always) then
120 return 'Y';
121
122 --date-type = full date or is null
123 elsif (date_type_num = c_fulldate OR date_type_num IS NULL) then
124
125 --get current date in dd-mon-yy format
126 cur_date :=Get_Current_Date(NULL);
127 if ((cur_date >= from_date OR from_date IS NULL) AND
128 (cur_date <= to_date OR to_date IS NULL)) then
129 return 'Y';
130 else
131 return 'N';
132 end if;
133
134 elsif (date_type_num = c_day) then
135 -- D returns number of the dat (Sunday = 1)
136 cur_val := to_number(Get_Current_Date('D'));
137 ret_val := Check_Between(cur_val, from_val, to_val);
138 return ret_val;
139
140 elsif (date_type_num = c_date) then
141
142 --DD returns numerical date
143 cur_val := to_number(Get_Current_Date('DD'));
144 ret_val := Check_Between(cur_val, from_val, to_val);
145 return ret_val;
146
147 elsif (date_type_num = c_month) then
148 --MM returns number of the month (Jan = 1)
149 cur_val := to_number(Get_Current_Date('MM'));
150 ret_val := Check_Between(cur_val, from_val, to_val);
151 return ret_val;
152
153 elsif (date_type_num = c_year) then
154 --YYYY returns 4-digit year
155 cur_val := to_number(Get_Current_Date('YYYY'));
156 ret_val := Check_Between(cur_val, from_val, to_val);
157 return ret_val;
158
159 elsif (date_type_num = c_shift) then
160
161 --get the calendar code and exception_set_id
162 OPEN cal_param;
163 FETCH cal_param into cal_code, except_set;
164 CLOSE cal_param;
165 if (cal_code IS NULL) then
166 return 'F';
167 end if;
168
169 --SSSSS returns Seconds since midnight
170 cur_seconds := to_number(Get_Current_Date('SSSSS'));
171 cur_date := Get_Current_Date(NULL);
172 OPEN current_shifts;
173 FETCH current_shifts INTO cur_val;
174
175 --shifts can overlap, so select can return multiple rows
176 WHILE current_shifts%FOUND LOOP
177 ret_val := Check_Between(cur_val, from_val, to_val);
178 if (ret_val = 'Y') then
179 CLOSE current_shifts;
180 return 'Y';
181 end if;
182 FETCH current_shifts INTO cur_val;
183 end loop;
184 CLOSE current_shifts;
185 return 'N';
186
187 elsif (date_type_num = c_week) then
188
189 --get calendar_code and exception_set_id
190 OPEN cal_param;
191 FETCH cal_param into cal_code, except_set;
192 CLOSE cal_param;
193 if (cal_code IS NULL) then
194 return 'F';
195 end if;
196
197 cur_date := Get_Current_Date(NULL);
198
199 OPEN current_week;
200 FETCH current_week INTO cur_val;
201 ret_val := Check_Between(cur_val, from_val, to_val);
202 return ret_val;
203
204 elsif (date_type_num = c_cal_period) then
205
206 --get calendar code and exception set id
207 OPEN cal_param;
208 FETCH cal_param into cal_code, except_set;
209 ClOSE cal_param;
210 if (cal_code IS NULL) then
211 return 'F';
212 end if;
213 cur_date := Get_Current_Date(NULL);
214
215 OPEN current_cal_period;
216 FETCH current_cal_period INTO cur_val;
217 CLOSE current_cal_period;
218 ret_val := Check_Between(cur_val, from_val, to_val);
219 return ret_val;
220
221 elsif (date_type_num = c_acct_period) then
222
223 cur_date := Get_Current_Date(NULL);
224
225 OPEN current_acct_period;
226 FETCH current_acct_period INTO cur_val;
227 CLOSE current_acct_period;
228
229 ret_val := Check_Between(cur_val, from_val, to_val);
230 return ret_val;
231
232 elsif (date_type_num = c_quarter) then
233
234 --get the period_set_name
235 OPEN period_set;
236 FETCH period_set INTO set_name;
237 CLOSE period_set;
238
239 cur_date := Get_Current_Date(NULL);
240 OPEN current_quarter;
241 FETCH current_quarter INTO cur_val;
242
243 --returns multiple values since serveral periods in a single
244 --quarter
245 WHILE current_quarter%FOUND LOOP
246 ret_val := Check_Between(cur_val, from_val, to_val);
247 if(ret_val = 'Y') then
248 CLOSE current_quarter;
249 return 'Y';
250 end if;
251 FETCH current_quarter INTO cur_val;
252 end loop;
253 CLOSE current_quarter;
254 return 'N';
255
256 else
257 return 'N';
258 end if;
259
260 END Date_Valid;
261
262
263 /*Get_Current_Date
264 * Returns sysdate in form specified in format
265 * Passing NULL format = default sysdate format (DD-MON-YY)
266 */
267
268
269 FUNCTION Get_Current_Date(format VARCHAR2)
270 return VARCHAR2 IS
271
272 cursor c_date IS
273 SELECT sysdate
274 FROM DUAL;
275
276 cur_date DATE;
277
278 BEGIN
279
280 OPEN c_date;
281 FETCH c_date into cur_date;
282 CLOSE c_date;
283
284 if (format IS NOT NULL) then
285 return to_char(cur_date, format);
286 else
287 return to_char(cur_date);
288 end if;
289
290 end Get_Current_Date;
291
292
293 /*Check_Between
294 * Returns 'Y' if cur_val between from_val and to_val
295 * Returns 'N' otherwise
296 */
297
298
299 FUNCTION Check_Between
300 (cur_val NUMBER,
301 from_val NUMBER,
302 to_val NUMBER)
303
304 return VARCHAR2 IS
305
306 BEGIN
307 if (cur_val IS NULL) then
308 return 'N';
309 end if;
310
311 --easy case. current falls between from and to
312 if (to_val >= from_val) then
313 if ((cur_val >= from_val OR from_val IS NULL) AND
314 (cur_val <= to_val OR to_val IS NULL)) THEN
315 return 'Y';
316 else
317 return 'N';
318 end if;
319 else
320 if((cur_val >=from_val OR from_val IS NULL) OR
321 (cur_val <= to_val OR to_val IS NULL)) THEN
322 return 'Y';
323 else
324 return 'N';
325 end if;
326 end if;
327
328 end Check_Between;
329
330 END INV_PP_DateCheck_PVT;
[Home] [Help]