1 PACKAGE BODY WMS_DateCheck_PVT AS
2 /* $Header: WMSVPPDB.pls 120.1 2005/09/29 00:51:23 amohamme 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 cur_date 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 --date-type = always, return true
118 if (date_type_num = c_always) then
119 return 'Y';
120
121 --date-type = full date or is null
122 elsif (date_type_num = c_fulldate OR date_type_num IS NULL) then
123
124 --get current date in dd-mon-yy format
125 cur_date :=Get_Current_Date(NULL);
126 if ((cur_date >= from_date OR from_date IS NULL) AND
127 (cur_date <= to_date OR to_date IS NULL)) then
128 return 'Y';
129 else
130 return 'N';
131 end if;
132
133 elsif (date_type_num = c_day) then
134 -- D returns number of the dat (Sunday = 1)
135 cur_val := to_number(Get_Current_Date('D'));
136 ret_val := Check_Between(cur_val, from_val, to_val);
137 return ret_val;
138
139 elsif (date_type_num = c_date) then
140
141 --DD returns numerical date
142 cur_val := to_number(Get_Current_Date('DD'));
143 ret_val := Check_Between(cur_val, from_val, to_val);
144 return ret_val;
145
146 elsif (date_type_num = c_month) then
147 --MM returns number of the month (Jan = 1)
148 cur_val := to_number(Get_Current_Date('MM'));
149 ret_val := Check_Between(cur_val, from_val, to_val);
150 return ret_val;
151
152 elsif (date_type_num = c_year) then
153 --YYYY returns 4-digit year
154 cur_val := to_number(Get_Current_Date('YYYY'));
155 ret_val := Check_Between(cur_val, from_val, to_val);
156 return ret_val;
157
158 elsif (date_type_num = c_shift) then
159
160 --get the calendar code and exception_set_id
161 OPEN cal_param;
162 FETCH cal_param into cal_code, except_set;
163 CLOSE cal_param;
164 if (cal_code IS NULL) then
165 return 'F';
166 end if;
167
168 --SSSSS returns Seconds since midnight
169 cur_seconds := to_number(Get_Current_Date('SSSSS'));
170 cur_date := Get_Current_Date(NULL);
171 OPEN current_shifts;
172 FETCH current_shifts INTO cur_val;
173
174 --shifts can overlap, so select can return multiple rows
175 WHILE current_shifts%FOUND LOOP
176 ret_val := Check_Between(cur_val, from_val, to_val);
177 if (ret_val = 'Y') then
178 CLOSE current_shifts;
179 return 'Y';
180 end if;
181 FETCH current_shifts INTO cur_val;
182 end loop;
183 CLOSE current_shifts;
184 return 'N';
185
186 elsif (date_type_num = c_week) then
187
188 --get calendar_code and exception_set_id
189 OPEN cal_param;
190 FETCH cal_param into cal_code, except_set;
191 CLOSE cal_param;
192 if (cal_code IS NULL) then
193 return 'F';
194 end if;
195
196 cur_date := Get_Current_Date(NULL);
197
198 OPEN current_week;
199 FETCH current_week INTO cur_val;
200 ret_val := Check_Between(cur_val, from_val, to_val);
201 return ret_val;
202
203 elsif (date_type_num = c_cal_period) then
204
205 --get calendar code and exception set id
206 OPEN cal_param;
207 FETCH cal_param into cal_code, except_set;
208 ClOSE cal_param;
209 if (cal_code IS NULL) then
210 return 'F';
211 end if;
212 cur_date := Get_Current_Date(NULL);
213
214 OPEN current_cal_period;
215 FETCH current_cal_period INTO cur_val;
216 CLOSE current_cal_period;
217 ret_val := Check_Between(cur_val, from_val, to_val);
218 return ret_val;
219
220 elsif (date_type_num = c_acct_period) then
221
222 cur_date := Get_Current_Date(NULL);
223
224 OPEN current_acct_period;
225 FETCH current_acct_period INTO cur_val;
226 CLOSE current_acct_period;
227 ret_val := Check_Between(cur_val, from_val, to_val);
228 return ret_val;
229
230 elsif (date_type_num = c_quarter) then
231
232 --get the period_set_name
233 OPEN period_set;
234 FETCH period_set INTO set_name;
235 CLOSE period_set;
236
237 cur_date := Get_Current_Date(NULL);
238 OPEN current_quarter;
239 FETCH current_quarter INTO cur_val;
240
241 --returns multiple values since serveral periods in a single
242 --quarter
243 WHILE current_quarter%FOUND LOOP
244 ret_val := Check_Between(cur_val, from_val, to_val);
245 if(ret_val = 'Y') then
246 CLOSE current_quarter;
247 return 'Y';
248 end if;
249 FETCH current_quarter INTO cur_val;
250 end loop;
251 CLOSE current_quarter;
252 return 'N';
253
254 else
255 return 'N';
256 end if;
257
258 END Date_Valid;
259
260
261 /*Get_Current_Date
262 * Returns sysdate in form specified in format
263 * Passing NULL format = default sysdate format (DD-MON-YY)
264 */
265
266
267 FUNCTION Get_Current_Date(format VARCHAR2)
268 return VARCHAR2 IS
269
270 cursor c_date IS
271 SELECT sysdate
272 FROM DUAL;
273
274 cur_date DATE;
275
276 BEGIN
277
278 OPEN c_date;
279 FETCH c_date into cur_date;
280 CLOSE c_date;
281
282 if (format IS NOT NULL) then
283 return to_char(cur_date, format);
284 else
285 return to_char(cur_date);
286 end if;
287
288 end Get_Current_Date;
289
290
291 /*Check_Between
292 * Returns 'Y' if cur_val between from_val and to_val
293 * Returns 'N' otherwise
294 */
295
296
297 FUNCTION Check_Between
298 (cur_val NUMBER,
299 from_val NUMBER,
300 to_val NUMBER)
301
302 return VARCHAR2 IS
303
304 BEGIN
305 if (cur_val IS NULL) then
306 return 'N';
307 end if;
308
309 --easy case. current falls between from and to
310 if (to_val >= from_val) then
311 if ((cur_val >= from_val OR from_val IS NULL) AND
312 (cur_val <= to_val OR to_val IS NULL)) THEN
313 return 'Y';
314 else
315 return 'N';
316 end if;
317 else
318 if((cur_val >=from_val OR from_val IS NULL) OR
319 (cur_val <= to_val OR to_val IS NULL)) THEN
320 return 'Y';
321 else
322 return 'N';
323 end if;
324 end if;
325
326 end Check_Between;
327
328 END WMS_DateCheck_PVT;
[Home] [Help]