1 PACKAGE BODY BOM_CALENDARS_PKG as
2 /* $Header: bompbclb.pls 115.2 99/07/16 05:47:10 porting ship $ */
3
4
5 FUNCTION Workday_Pattern_Exist (x_calendar_code VARCHAR2,
6 x_shift_num NUMBER,
7 x_calendar_or_shift NUMBER) RETURN NUMBER IS
8 workday_pattern NUMBER;
9 BEGIN
10 if x_calendar_or_shift = 0 then
11 select count(*) into workday_pattern
12 from BOM_WORKDAY_PATTERNS
13 where calendar_code = x_calendar_code
14 and shift_num is null;
15 elsif x_calendar_or_shift = 1 then
16 select count(*) into workday_pattern from BOM_WORKDAY_PATTERNS
17 where calendar_code = x_calendar_code
18 and shift_num = x_shift_num;
19 end if;
20 return (workday_pattern);
21 END Workday_Pattern_Exist;
22
23
24 PROCEDURE Calendar_Check_Unique (x_calendar_code VARCHAR2) IS
25 dummy NUMBER;
26 BEGIN
27 select 1 into dummy from DUAL where not exists
28 (select 1 from BOM_CALENDARS
29 where calendar_code = x_calendar_code
30 );
31
32 EXCEPTION
33 when NO_DATA_FOUND then
34 fnd_message.set_name('BOM', 'BOM_ALREADY_EXISTS');
35 fnd_message.set_token('ENTITY1', 'CALENDAR_CAP', TRUE);
36 fnd_message.set_token('ENTITY2', x_calendar_code);
37 app_exception.raise_exception;
38 END Calendar_Check_Unique;
39
40
41 PROCEDURE Check_Exception_Range (x_calendar_code VARCHAR2,
42 x_lo_except_date IN OUT DATE,
43 x_hi_except_date IN OUT DATE) IS
44 cal_except_date DATE;
45 shift_except_date DATE;
46 BEGIN
47
48 select max(exception_date) into cal_except_date
49 from BOM_CALENDAR_EXCEPTIONS
50 where calendar_code = x_calendar_code;
51
52 select max(exception_date) into shift_except_date
53 from BOM_SHIFT_EXCEPTIONS
54 where calendar_code = x_calendar_code;
55
56 if (cal_except_date is NULL and shift_except_date is NULL) then
57 x_hi_except_date := NULL;
58 elsif (cal_except_date is NULL and shift_except_date is not NULL) then
59 x_hi_except_date := shift_except_date;
60 elsif (cal_except_date is not NULL and shift_except_date is NULL) then
61 x_hi_except_date := cal_except_date;
62 elsif (cal_except_date is NULL) and (shift_except_date is NULL) then
63 begin
64 if cal_except_date >= shift_except_date then
65 x_hi_except_date := shift_except_date;
66 else
67 x_hi_except_date := cal_except_date;
68 end if;
69 end;
70 end if;
71
72 cal_except_date := NULL;
73 shift_except_date := NULL;
74
75 select min(exception_date) into cal_except_date
76 from BOM_CALENDAR_EXCEPTIONS
77 where calendar_code = x_calendar_code;
78 select min(exception_date) into shift_except_date
79 from BOM_SHIFT_EXCEPTIONS
80 where calendar_code = x_calendar_code;
81
82 if (cal_except_date is NULL and shift_except_date is NULL) then
83 x_lo_except_date := NULL;
84 elsif (cal_except_date is NULL and shift_except_date is not NULL) then
85 x_lo_except_date := shift_except_date;
86 elsif (cal_except_date is not NULL and shift_except_date is NULL) then
87 x_lo_except_date := cal_except_date;
88 elsif (cal_except_date is not NULL and shift_except_date is not NULL) then
89 begin
90 if cal_except_date >= shift_except_date then
91 x_lo_except_date := shift_except_date;
92 else
93 x_lo_except_date := cal_except_date;
94 end if;
95 end;
96 end if;
97
98 EXCEPTION
99 when NO_DATA_FOUND then
100 null;
101 END Check_Exception_Range;
102
103
104 PROCEDURE Cal_Exception_Check_Unique (x_calendar_code VARCHAR2,
105 x_exception_date DATE) IS
106 dummy NUMBER;
107 BEGIN
108 select 1 into dummy from DUAL where not exists
109 (select 1 from BOM_CALENDAR_EXCEPTIONS
110 where calendar_code = x_calendar_code
111 and exception_date = x_exception_date
112 );
113
114 EXCEPTION
115 when NO_DATA_FOUND then
116 fnd_message.set_name('BOM', 'BOM_ALREADY_EXISTS');
117 fnd_message.set_token('ENTITY1', x_exception_date);
118 fnd_message.set_token('ENTITY2', NULL);
119 app_exception.raise_exception;
120 END Cal_Exception_Check_Unique;
121
122
123
124 PROCEDURE Shift_Check_Unique (x_calendar_code VARCHAR2,
125 x_shift_num NUMBER) IS
126 dummy NUMBER;
127 BEGIN
128 select 1 into dummy from DUAL where not exists
129 (select 1 from BOM_CALENDAR_SHIFTS
130 where calendar_code = x_calendar_code
131 and shift_num = x_shift_num
132 );
133
134 EXCEPTION
135 when NO_DATA_FOUND then
136 fnd_message.set_name('BOM', 'BOM_ALREADY_EXISTS');
137 fnd_message.set_token('ENTITY1', 'SHIFT NUM', TRUE);
138 fnd_message.set_token('ENTITY2', x_shift_num);
139 app_exception.raise_exception;
140 END Shift_Check_Unique;
141
142
143 PROCEDURE Shift_Exception_Check_Unique (x_calendar_code VARCHAR2,
144 x_shift_num NUMBER,
145 x_exception_date DATE) IS
146 dummy NUMBER;
147 BEGIN
148 select 1 into dummy from DUAL where not exists
149 (select 1 from BOM_SHIFT_EXCEPTIONS
150 where calendar_code = x_calendar_code
151 and shift_num = x_shift_num
152 and exception_date = x_exception_date
153 );
154
155 EXCEPTION
156 when NO_DATA_FOUND then
157 fnd_message.set_name('BOM', 'BOM_ALREADY_EXISTS');
158 fnd_message.set_token('ENTITY1', x_exception_date);
159 fnd_message.set_token('ENTITY2', NULL);
160 app_exception.raise_exception;
161 END Shift_Exception_Check_Unique;
162
163
164 PROCEDURE Times_Check_Unique (x_calendar_code VARCHAR2,
165 x_row_id VARCHAR2,
166 x_shift_num NUMBER,
167 x_start_time NUMBER,
168 x_end_time NUMBER) IS
169 dummy NUMBER;
170 BEGIN
171 select 1 into dummy from DUAL where not exists
172 (select 1 from BOM_SHIFT_TIMES
173 where calendar_code = x_calendar_code
174 and shift_num = x_shift_num
175 and from_time = x_start_time
176 and to_time = x_end_time
177 and (
178 rowid is null
179 or
180 rowid <> x_row_id
181 )
182 );
183
184 EXCEPTION
185 when NO_DATA_FOUND then
186 fnd_message.set_name('BOM', 'BOM_ALREADY_EXISTS');
187 fnd_message.set_token('ENTITY1', 'TIME FROM_CAP', TRUE);
188 fnd_message.set_token('ENTITY2', 'TIME TO_CAP', TRUE);
189 app_exception.raise_exception;
190 END Times_Check_Unique;
191
192
193 FUNCTION Shift_Times_Overlap (x_calendar_code VARCHAR2,
194 x_shift_num NUMBER,
195 x_start_time NUMBER,
196 x_end_time NUMBER,
197 x_rowid VARCHAR2,
198 x_flag NUMBER) RETURN NUMBER IS
199 dummy NUMBER := 0;
200 BEGIN
201 if x_flag = 1 then
202 select count(*) into dummy from dual where not exists
203 (select 1 from BOM_SHIFT_TIMES
204 where calendar_code = x_calendar_code
205 and shift_num = x_shift_num
206 and from_time < to_time
207 and x_start_time > from_time
208 and x_start_time < to_time
209 union
210 select 1 from BOM_SHIFT_TIMES
211 where calendar_code = x_calendar_code
212 and shift_num = x_shift_num
213 and from_time > to_time
214 and (x_start_time > from_time or x_start_time < to_time)
215 );
216 elsif x_flag = 2 then
217 select count(*) into dummy from dual where not exists
218 (select 1 from BOM_SHIFT_TIMES
219 where calendar_code = x_calendar_code
220 and shift_num = x_shift_num
221 and from_time < to_time
222 and x_start_time > from_time
223 and x_start_time < to_time
224 and x_rowid <> x_rowid
225 union
226 select 1 from BOM_SHIFT_TIMES
227 where calendar_code = x_calendar_code
228 and shift_num = x_shift_num
229 and from_time > to_time
230 and (x_start_time > from_time or x_start_time < to_time)
231 and x_rowid <> x_rowid
232 );
233 end if;
234
235 if Dummy = 0 then
236 return (0);
237 end if;
238
239 if x_flag = 1 then
240 select count(*) into dummy from DUAL where not exists
241 (select 1 from BOM_SHIFT_TIMES
242 where calendar_code = x_calendar_code
243 and shift_num = x_shift_num
244 and from_time < to_time
245 and x_end_time > from_time
246 and x_end_time < to_time
247 union
248 select 1 from BOM_SHIFT_TIMES
249 where calendar_code = x_calendar_code
250 and shift_num = x_shift_num
251 and from_time > to_time
252 and (x_end_time < to_time or x_end_time > from_time)
253 );
254 elsif x_flag = 2 then
255 select count(*) into dummy from DUAL where not exists
256 (select 1 from BOM_SHIFT_TIMES
257 where calendar_code = x_calendar_code
258 and shift_num = x_shift_num
259 and from_time < to_time
260 and x_end_time > from_time
261 and x_end_time < to_time
262 and rowid <> x_rowid
263 union
264 select 1 from BOM_SHIFT_TIMES
265 where calendar_code = x_calendar_code
266 and shift_num = x_shift_num
267 and from_time > to_time
268 and (x_end_time < to_time or x_end_time > from_time)
269 and rowid <> x_rowid
270 );
271 end if;
272
273 if dummy = 0 then
274 return (0);
275 end if;
276
277 if x_flag = 1 then
278 select count(*) into dummy from DUAL where not exists
279 (select 1 from BOM_SHIFT_TIMES
280 where calendar_code = x_calendar_code
281 and shift_num = x_shift_num
282 and from_time < to_time
283 and x_start_time < x_end_time
284 and x_start_time < from_time
285 and x_end_time > to_time
286 union
287 select 1 from BOM_SHIFT_TIMES
288 where calendar_code = x_calendar_code
289 and shift_num = x_shift_num
290 and from_time < to_time
291 and x_start_time < x_end_time
292 and x_start_time > from_time
293 and x_end_time < to_time
294 );
295 elsif x_flag = 2 then
296 select count(*) into dummy from DUAL where not exists
297 (select 1 from BOM_SHIFT_TIMES
298 where calendar_code = x_calendar_code
299 and shift_num = x_shift_num
300 and from_time < to_time
301 and x_start_time < x_end_time
302 and x_start_time < from_time
303 and x_end_time > to_time
304 and rowid <> x_rowid
305 union
306 select 1 from BOM_SHIFT_TIMES
307 where calendar_code = x_calendar_code
308 and shift_num = x_shift_num
309 and from_time < to_time
310 and x_start_time < x_end_time
311 and x_start_time > from_time
312 and x_end_time < to_time
313 and rowid <> x_rowid
314 );
315 end if;
316
317 if dummy = 0 then
318 return (0);
319 end if;
320
321 if x_flag = 1 then
322 select count(*) into dummy from DUAL where not exists
323 (select 1 from BOM_SHIFT_TIMES
324 where calendar_code = x_calendar_code
325 and shift_num = x_shift_num
326 and from_time > to_time
327 and x_start_time > x_end_time
328 and x_start_time < from_time
329 and x_end_time > to_time
330 union
331 select 1 from BOM_SHIFT_TIMES
332 where calendar_code = x_calendar_code
333 and shift_num = x_shift_num
334 and from_time > to_time
335 and x_start_time > x_end_time
336 and x_start_time > from_time
337 and x_end_time < to_time
338 );
339 elsif x_flag = 2 then
340 select count(*) into dummy from DUAL where not exists
341 (select 1 from BOM_SHIFT_TIMES
342 where calendar_code = x_calendar_code
343 and shift_num = x_shift_num
344 and from_time > to_time
345 and x_start_time > x_end_time
346 and x_start_time < from_time
347 and x_end_time > to_time
348 and rowid <> x_rowid
349 union
350 select 1 from BOM_SHIFT_TIMES
351 where calendar_code = x_calendar_code
352 and shift_num = x_shift_num
353 and from_time > to_time
354 and x_start_time > x_end_time
355 and x_start_time > from_time
356 and x_end_time < to_time
357 and rowid <> x_rowid
358 );
359 end if;
360
361 if dummy = 0 then
362 return (0);
363 else
364 return (1);
365 end if;
366
367 END Shift_Times_Overlap;
368
369
370 END BOM_CALENDARS_PKG;
[Home] [Help]