[Home] [Help]
PACKAGE BODY: APPS.CS_TM_LABOR_SCHEDULE_PVT
Source
1 PACKAGE BODY CS_TM_LABOR_SCHEDULE_PVT AS
2 /* $Header: csxvtmsb.pls 120.1 2005/09/20 18:26:39 talex noship $ */
3 RECORD_LOCK_EXCEPTION EXCEPTION;
4 PRAGMA EXCEPTION_INIT(RECORD_LOCK_EXCEPTION,-0054);
5
6 -- Structure Definitions
7 TYPE TM_REC_TYPE IS RECORD
8 ( START_TIME DATE ,
9 END_TIME DATE ,
10 DAY VARCHAR2(40) ,
11 HOLIDAY_FLAG VARCHAR2(1)
12 );
13
14 TYPE TM_TBL_TYPE IS TABLE OF TM_REC_TYPE INDEX BY BINARY_INTEGER;
15
16 -- Private Procedures
17 PROCEDURE VALIDATE_COMPLETE_CHECK
18 ( P_TM_TBL_TYPE IN TM_TBL_TYPE,
19 X_RETURN_STATUS OUT NOCOPY VARCHAR2
20 );
21
22 PROCEDURE VALIDATE_OVERLAP
23 ( P_TM_TBL_TYPE IN TM_TBL_TYPE,
24 X_RETURN_STATUS OUT NOCOPY VARCHAR2
25 );
26
27 --************************************************************************
28 -- Procedure to validate labor schedule is not overlapping with one another
29 -- for a specific business process
30 -- Defination of the Validate_Schedule_Overlap API
31 --************************************************************************
32
33 PROCEDURE VALIDATE_SCHEDULE_OVERLAP(
34 P_LABOR_SCHEDULE_TBL IN TM_SCHEDULE_TBL_TYPE,
35 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
36 X_MSG_COUNT OUT NOCOPY NUMBER,
37 X_MSG_DATA OUT NOCOPY VARCHAR2,
38 P_API_VERSION IN NUMBER,
39 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
40 ) IS
41
42 lv_return_status VARCHAR2(1);
43 lv_return_message VARCHAR2(2000);
44
45 -- declaring counters for populating the individual
46 lv_counter1 NUMBER := 0;
47 lv_counter2 NUMBER := 0;
48 lv_counter3 NUMBER := 0;
49 lv_counter4 NUMBER := 0;
50 lv_counter5 NUMBER := 0;
51 lv_counter6 NUMBER := 0;
52 lv_counter7 NUMBER := 0;
53 lv_counter8 NUMBER := 0;
54 lv_counter9 NUMBER := 0;
55 lv_counter10 NUMBER := 0;
56 lv_counter11 NUMBER := 0;
57 lv_counter12 NUMBER := 0;
58 lv_counter13 NUMBER := 0;
59 lv_counter14 NUMBER := 0;
60
61 -- declaring the tables for the Regular and Holidays Days
62 lv_tm_mon_reg_tbl_type TM_TBL_TYPE;
63 lv_tm_tue_reg_tbl_type TM_TBL_TYPE;
64 lv_tm_wed_reg_tbl_type TM_TBL_TYPE;
65 lv_tm_thurs_reg_tbl_type TM_TBL_TYPE;
66 lv_tm_fri_reg_tbl_type TM_TBL_TYPE;
67 lv_tm_sat_reg_tbl_type TM_TBL_TYPE;
68 lv_tm_sun_reg_tbl_type TM_TBL_TYPE;
69 lv_tm_mon_hol_tbl_type TM_TBL_TYPE;
70 lv_tm_tue_hol_tbl_type TM_TBL_TYPE;
71 lv_tm_wed_hol_tbl_type TM_TBL_TYPE;
72 lv_tm_thurs_hol_tbl_type TM_TBL_TYPE;
73 lv_tm_fri_hoL_tbl_type TM_TBL_TYPE;
74 lv_tm_sat_hol_tbl_type TM_TBL_TYPE;
75 lv_tm_sun_hol_tbl_type TM_TBL_TYPE;
76
77 -- DEBUG
78 l_errm VARCHAR2(200);
79
80 l_api_version NUMBER := 1.0;
81 l_api_name VARCHAR2(30) := 'Validate_Schedule_Overlap';
82 l_api_name_full VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
83
84
85 BEGIN
86
87 -- Standard call to check for call compatibility
88 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
89 G_PKG_NAME) THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93
94 /**-- Initialize the message list if p_init_msg_list is set to TRUE
95 IF FND_API.To_Boolean(p_init_msg_list) THEN
96 FND_MSG_PUB.Initialize;
97 END IF;**/
98
99
100 -- Initialize API return status to success
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 -- Begin the validate overlap functionality check
104 -- The table passed in this api will be sorted by holiday flag and start time
105
106 FOR lv_temp IN 1..P_LABOR_SCHEDULE_TBL.COUNT LOOP
107
108 IF ((p_labor_schedule_tbl(lv_temp).monday_flag = 'Y') AND
109 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
110 -- The Schedule is for Monday - Regular Day
111 -- Populate The Monday Regular Table
112 -- initialize the lv_counter to 1
113 lv_counter1 := lv_counter1 + 1;
114 lv_tm_mon_reg_tbl_type(lv_counter1).start_time := p_labor_schedule_tbl(lv_temp).start_time;
115 lv_tm_mon_reg_tbl_type(lv_counter1).end_time := p_labor_schedule_tbl(lv_temp).end_time;
116 lv_tm_mon_reg_tbl_type(lv_counter1).day := 'MONDAY';
117 lv_tm_mon_reg_tbl_type(lv_counter1).holiday_flag := 'N';
118 END IF;
119
120 IF ((p_labor_schedule_tbl(lv_temp).tuesday_flag = 'Y') AND
121 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
122 -- The Schedule is for Tuesday - Regular Day
123 -- Populate The Tuesday Regular Table
124 -- Initialize lv_counter2 to 1
125 lv_counter2 := lv_counter2 + 1;
126 lv_tm_tue_reg_tbl_type(lv_counter2).start_time := p_labor_schedule_tbl(lv_temp).start_time;
127 lv_tm_tue_reg_tbl_type(lv_counter2).end_time := p_labor_schedule_tbl(lv_temp).end_time;
128 lv_tm_tue_reg_tbl_type(lv_counter2).day := 'TUESDAY';
129 lv_tm_tue_reg_tbl_type(lv_counter2).holiday_flag := 'N';
130 END IF;
131
132 IF ((p_labor_schedule_tbl(lv_temp).wednesday_flag = 'Y') AND
133 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
134 -- The Schedule is for Wednesday - Regular Day
135 -- Populate The Wednesday Regular Table
136 -- Initialize lv_counter3 to 1
137 lv_counter3 := lv_counter3 + 1;
138 lv_tm_wed_reg_tbl_type(lv_counter3).start_time := p_labor_schedule_tbl(lv_temp).start_time;
139 lv_tm_wed_reg_tbl_type(lv_counter3).end_time := p_labor_schedule_tbl(lv_temp).end_time;
140 lv_tm_wed_reg_tbl_type(lv_counter3).day := 'WEDNESDAY';
141 lv_tm_wed_reg_tbl_type(lv_counter3).holiday_flag := 'N';
142 END IF;
143
144 IF ((p_labor_schedule_tbl(lv_temp).thursday_flag = 'Y') AND
145 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
146 -- The Schedule is for Thursday - Regular Day
147 -- Populate The Thursday Regular Table
148 -- Initialize lv_counter4 to 1
149 lv_counter4 := lv_counter4 + 1;
150 lv_tm_thurs_reg_tbl_type(lv_counter4).start_time := p_labor_schedule_tbl(lv_temp).start_time;
151 lv_tm_thurs_reg_tbl_type(lv_counter4).end_time := p_labor_schedule_tbl(lv_temp).end_time;
152 lv_tm_thurs_reg_tbl_type(lv_counter4).day := 'THURSDAY';
153 lv_tm_thurs_reg_tbl_type(lv_counter4).holiday_flag := 'N';
154 END IF;
155
156 IF ((p_labor_schedule_tbl(lv_temp).friday_flag = 'Y') AND
157 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
158 -- The Schedule is for Friday - Regular Day
159 -- Populate The Friday Regular Table
160 -- Initialize lv_counter5 to 1
161 lv_counter5 := lv_counter5 + 1;
162 lv_tm_fri_reg_tbl_type(lv_counter5).start_time := p_labor_schedule_tbl(lv_temp).start_time;
163 lv_tm_fri_reg_tbl_type(lv_counter5).end_time := p_labor_schedule_tbl(lv_temp).end_time;
164 lv_tm_thurs_reg_tbl_type(lv_counter5).day := 'FRIDAY';
165 lv_tm_thurs_reg_tbl_type(lv_counter5).holiday_flag := 'N';
166 END IF;
167
168 IF ((p_labor_schedule_tbl(lv_temp).saturday_flag = 'Y') AND
169 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
170 -- The Schedule is for Saturday - Regular Day
171 -- Populate The Saturday Regular Table
172 -- Initialize the lv_counter6 to 1
173 lv_counter6 := lv_counter6 + 1;
174 lv_tm_sat_reg_tbl_type(lv_counter6).start_time := p_labor_schedule_tbl(lv_temp).start_time;
175 lv_tm_sat_reg_tbl_type(lv_counter6).end_time := p_labor_schedule_tbl(lv_temp).end_time;
176 lv_tm_sat_reg_tbl_type(lv_counter6).day := 'SATURDAY';
177 lv_tm_sat_reg_tbl_type(lv_counter6).holiday_flag := 'N';
178 END IF;
179
180 IF ((p_labor_schedule_tbl(lv_temp).sunday_flag = 'Y') AND
181 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
182 -- The Schedule is for Sunday - Regular Day
183 -- Populate The Sunday Regular Table
184 -- Initialize the lv_counter7 to 1
185 lv_counter7 := lv_counter7 + 1;
186 lv_tm_sun_reg_tbl_type(lv_counter7).start_time := p_labor_schedule_tbl(lv_temp).start_time;
187 lv_tm_sun_reg_tbl_type(lv_counter7).end_time := p_labor_schedule_tbl(lv_temp).end_time;
188 lv_tm_sun_reg_tbl_type(lv_counter7).day := 'SUNDAY';
189 lv_tm_sun_reg_tbl_type(lv_counter7).holiday_flag := 'N';
190 END IF;
191
192 IF ((p_labor_schedule_tbl(lv_temp).monday_flag = 'Y') AND
193 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
194 -- The Schedule is for Monday - Holiday Day
195 -- Populate The Monday Holiday Table
196 -- Initialize lv_counter8 to 1
197 lv_counter8 := lv_counter8 + 1;
198 lv_tm_mon_hol_tbl_type(lv_counter8).start_time := p_labor_schedule_tbl(lv_temp).start_time;
199 lv_tm_mon_hol_tbl_type(lv_counter8).end_time := p_labor_schedule_tbl(lv_temp).end_time;
200 lv_tm_mon_hol_tbl_type(lv_counter8).day := 'MONDAY';
201 lv_tm_mon_hol_tbl_type(lv_counter8).holiday_flag := 'Y';
202 END IF;
203
204 IF ((p_labor_schedule_tbl(lv_temp).tuesday_flag = 'Y') AND
205 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
206 -- The Schedule is for Tuesday - Holiday Day
207 -- Populate The Tuesady Holiday Table
208 -- Initialize lv_counter9 to 1
209 lv_counter9 := lv_counter9 + 1;
210 lv_tm_tue_hol_tbl_type(lv_counter9).start_time := p_labor_schedule_tbl(lv_temp).start_time;
211 lv_tm_tue_hol_tbl_type(lv_counter9).end_time := p_labor_schedule_tbl(lv_temp).end_time;
212 lv_tm_mon_hol_tbl_type(lv_counter9).day := 'TUESDAY';
213 lv_tm_mon_hol_tbl_type(lv_counter9).holiday_flag := 'Y';
214 END IF;
215
216 IF ((p_labor_schedule_tbl(lv_temp).wednesday_flag = 'Y') AND
217 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
218 -- The Schedule is for Wednesday - Holiday Day
219 -- Populate the Wednesday Holiday Table
220 -- Initialize the lv_counter10 to 1
221 lv_counter10 := lv_counter10 + 1;
222 lv_tm_wed_hol_tbl_type(lv_counter10).start_time := p_labor_schedule_tbl(lv_temp).start_time;
223 lv_tm_wed_hol_tbl_type(lv_counter10).end_time := p_labor_schedule_tbl(lv_temp).end_time;
224 lv_tm_wed_hol_tbl_type(lv_counter10).day := 'WEDNESDAY';
225 lv_tm_wed_hol_tbl_type(lv_counter10).holiday_flag := 'Y';
226
227 END IF;
228
229 IF ((p_labor_schedule_tbl(lv_temp).thursday_flag = 'Y') AND
230 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
231 -- The Schedule is for Thursday - Holiday Day
232 -- Populate The Thursday Holiday Table
233 -- Initialize the lv_counter11 to 1
234 lv_counter11 := lv_counter11 + 1;
235 lv_tm_thurs_hol_tbl_type(lv_counter11).start_time := p_labor_schedule_tbl(lv_temp).start_time;
236 lv_tm_thurs_hol_tbl_type(lv_counter11).end_time := p_labor_schedule_tbl(lv_temp).end_time;
237 lv_tm_thurs_hol_tbl_type(lv_counter11).day := 'THURSDAY';
238 lv_tm_thurs_hol_tbl_type(lv_counter11).holiday_flag := 'Y';
239 END IF;
240
241 IF ((p_labor_schedule_tbl(lv_temp).friday_flag = 'Y') AND
242 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
243 -- The Schedule is for Friday - Holiday Day
244 -- Populate The Friday Holiday Table
245 -- Initialize lv_counter12 to 1
246 lv_counter12 := lv_counter12 + 1;
247 lv_tm_fri_hol_tbl_type(lv_counter12).start_time := p_labor_schedule_tbl(lv_temp).start_time;
248 lv_tm_fri_hol_tbl_type(lv_counter12).end_time := p_labor_schedule_tbl(lv_temp).end_time;
249 lv_tm_fri_hol_tbl_type(lv_counter12).day := 'FRIDAY';
250 lv_tm_fri_hol_tbl_type(lv_counter12).holiday_flag := 'Y';
251 END IF;
252
253 IF ((p_labor_schedule_tbl(lv_temp).saturday_flag = 'Y') AND
254 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
255 -- The Schedule is for Saturday - Holiday Day
256 -- Populate The Saturday Holiday Table
257 -- Initialize lv_counter13 to 1;
258 lv_counter13 := lv_counter13 + 1;
259 lv_tm_sat_hol_tbl_type(lv_counter13).start_time := p_labor_schedule_tbl(lv_temp).start_time;
260 lv_tm_sat_hol_tbl_type(lv_counter13).end_time := p_labor_schedule_tbl(lv_temp).end_time;
261 lv_tm_sat_hol_tbl_type(lv_counter12).day := 'SATURDAY';
262 lv_tm_sat_hol_tbl_type(lv_counter12).holiday_flag := 'Y';
263 END IF;
264
265 IF ((p_labor_schedule_tbl(lv_temp).sunday_flag = 'Y') AND
266 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
267 -- The Schedule is for Sunday - Holiday Day
268 -- Populate The Sunday Holiday Table
269 -- Initialize lv_counter13 to 1;
270 lv_counter14 := lv_counter14 + 1;
271 lv_tm_sun_hol_tbl_type(lv_counter14).start_time := p_labor_schedule_tbl(lv_temp).start_time;
272 lv_tm_sun_hol_tbl_type(lv_counter14).end_time := p_labor_schedule_tbl(lv_temp).end_time;
273 lv_tm_sun_hol_tbl_type(lv_counter14).day := 'SUNDAY';
274 lv_tm_sun_hol_tbl_type(lv_counter14).holiday_flag := 'Y';
275 END IF;
276
277 END LOOP;
278
279 -- Validate Monday Regular Table for Overlap
280 IF lv_tm_mon_reg_tbl_type.count = 0 THEN
281 --error
282 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
286 FND_MSG_PUB.add;
283 FND_MESSAGE.set_token('API_NAME', l_api_name);
284 FND_MESSAGE.set_token('VALUE', 'Regular');
285 FND_MESSAGE.set_token('DAY', 'MONDAY');
287 RAISE FND_API.g_exc_error;
288 ELSE ---Call Function To validate Monday Table
289 VALIDATE_OVERLAP
290 (p_tm_tbl_type => lv_tm_mon_reg_tbl_type,
291 x_return_status => lv_return_status
292 );
293 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
294 RAISE fnd_api.g_exc_error;
295 END IF;
296 END IF;
297
298 -- Validate Tuesday Regular Table for Overlap
299 IF lv_tm_tue_reg_tbl_type.count = 0 THEN
300 --error
301 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
302 FND_MESSAGE.set_token('API_NAME', l_api_name);
303 FND_MESSAGE.set_token ('VALUE', 'Regular');
304 FND_MESSAGE.set_token('DAY', 'TUESDAY');
305 FND_MSG_PUB.add;
306 RAISE FND_API.g_exc_error;
307 ELSE ---Call Function To validate Tuesday Table
308 VALIDATE_OVERLAP
309 (p_tm_tbl_type => lv_tm_tue_reg_tbl_type,
310 x_return_status => lv_return_status
311 );
312 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
313 RAISE fnd_api.g_exc_error;
314 END IF;
315 END IF;
316
317 -- Validate Wednesday Regular Table for Overlap
318 IF lv_tm_wed_reg_tbl_type.count = 0 THEN
319 --error
320 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
321 FND_MESSAGE.set_token('API_NAME', l_api_name);
322 FND_MESSAGE.set_token ('VALUE', 'Regular');
323 FND_MESSAGE.set_token('DAY', 'WEDNESDAY');
324 FND_MSG_PUB.add;
325 RAISE FND_API.g_exc_error;
326 ELSE ---Call Function To validate Wednesday Table
327 VALIDATE_OVERLAP
328 (p_tm_tbl_type => lv_tm_wed_reg_tbl_type,
329 x_return_status => lv_return_status
330 );
331 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
332 RAISE fnd_api.g_exc_error;
333 END IF;
334 END IF;
335
336 -- Validate Thursday Regular Table for Overlap
337 IF lv_tm_thurs_reg_tbl_type.count = 0 THEN
338 --error
339 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
340 FND_MESSAGE.set_token('API_NAME', l_api_name);
341 FND_MESSAGE.set_token ('VALUE', 'Regular');
342 FND_MESSAGE.set_token('DAY', 'THURSDAY');
343 FND_MSG_PUB.add;
344 RAISE FND_API.g_exc_error;
345 ELSE ---Call Function To validate Thursday Table
346 VALIDATE_OVERLAP
347 (p_tm_tbl_type => lv_tm_thurs_reg_tbl_type,
348 x_return_status => lv_return_status
349 );
350 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
351 RAISE fnd_api.g_exc_error;
352 END IF;
353 END IF;
354
355 -- Validate Friday Regular Table for Overlap
356 IF lv_tm_fri_reg_tbl_type.count = 0 THEN
357 --error
358 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
359 FND_MESSAGE.set_token('API_NAME', l_api_name);
360 FND_MESSAGE.set_token ('VALUE', 'Regular');
361 FND_MESSAGE.set_token('DAY', 'FRIDAY');
362 FND_MSG_PUB.add;
363 RAISE FND_API.g_exc_error;
364 ELSE ---Call Function To validate Friday Table
365 VALIDATE_OVERLAP
366 (p_tm_tbl_type => lv_tm_fri_reg_tbl_type,
367 x_return_status => lv_return_status
368 );
369 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
370 RAISE fnd_api.g_exc_error;
371 END IF;
372 END IF;
373
374 -- Validate Saturday Regular Table for Overlap
375 IF lv_tm_sat_reg_tbl_type.count = 0 THEN
376 --error
377 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
378 FND_MESSAGE.set_token('API_NAME', l_api_name);
379 FND_MESSAGE.set_token ('VALUE', 'Regular');
380 FND_MESSAGE.set_token('DAY', 'SATURDAY');
381 FND_MSG_PUB.add;
382 RAISE FND_API.g_exc_error;
383 ELSE ---Call Function To validate Saturday Table
384 VALIDATE_OVERLAP
385 (p_tm_tbl_type => lv_tm_sat_reg_tbl_type,
386 x_return_status => lv_return_status
387 );
388 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
389 RAISE fnd_api.g_exc_error;
390 END IF;
391 END IF;
392
393 -- Validate Sunday Regular Table for Overlap
394 IF lv_tm_sun_reg_tbl_type.count = 0 THEN
395 --error
396 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
397 FND_MESSAGE.set_token('API_NAME', l_api_name);
398 FND_MESSAGE.set_token ('VALUE', 'Regular');
399 FND_MESSAGE.set_token('DAY', 'SUNDAY');
400 FND_MSG_PUB.add;
401 RAISE FND_API.g_exc_error;
402 ELSE ---Call Function To validate Sunday Table
403 VALIDATE_OVERLAP
404 (p_tm_tbl_type => lv_tm_sun_reg_tbl_type,
405 x_return_status => lv_return_status
406 );
407 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
408 RAISE fnd_api.g_exc_error;
409 END IF;
410 END IF;
411
412 -- Validate Monday Holiday Table for Overlap
413 -- Following commented out for 11.5.9 Release.
414 /* IF lv_tm_mon_hol_tbl_type.count = 0 THEN
415 --error
416 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
417 FND_MESSAGE.set_token('API_NAME', l_api_name);
418 FND_MESSAGE.set_token ('VALUE', 'Holiday');
419 FND_MESSAGE.set_token('DAY', 'MONDAY');
420 FND_MSG_PUB.add;
421 RAISE FND_API.g_exc_error;
425 x_return_status => lv_return_status
422 ELSE ---Call Function To validate Monday Holiday Table
423 VALIDATE_OVERLAP
424 (p_tm_tbl_type => lv_tm_mon_hol_tbl_type,
426 );
427 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
428 RAISE fnd_api.g_exc_error;
429 END IF;
430 END IF;
431
432 -- Validate Tuesday Holiday Table for Overlap
433 IF lv_tm_tue_hol_tbl_type.count = 0 THEN
434 --error
435 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
436 FND_MESSAGE.set_token('API_NAME', l_api_name);
437 FND_MESSAGE.set_token ('VALUE', 'Holiday');
438 FND_MESSAGE.set_token('DAY', 'TUESDAY');
439 FND_MSG_PUB.add;
440 RAISE FND_API.g_exc_error;
441 ELSE ---Call Function To validate Tuesday Holiday Table
442 VALIDATE_OVERLAP
443 (p_tm_tbl_type => lv_tm_tue_hol_tbl_type,
444 x_return_status => lv_return_status
445 );
446 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
447 RAISE fnd_api.g_exc_error;
448 END IF;
449 END IF;
450
451 -- Validate Wednesday Holiday Table for Overlap
452 IF lv_tm_wed_hol_tbl_type.count = 0 THEN
453 --error
454 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
455 FND_MESSAGE.set_token('API_NAME', l_api_name);
456 FND_MESSAGE.set_token ('VALUE', 'Holiday');
457 FND_MESSAGE.set_token('DAY', 'WEDNESDAY');
458 FND_MSG_PUB.add;
459 RAISE FND_API.g_exc_error;
460 ELSE ---Call Function To validate Wednesday Holiday Table
461 VALIDATE_OVERLAP
462 (p_tm_tbl_type => lv_tm_wed_hol_tbl_type,
463 x_return_status => lv_return_status
464 );
465 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
466 RAISE fnd_api.g_exc_error;
467 END IF;
468 END IF;
469
470 -- Validate Thursday Holiday Table for Overlap
471 IF lv_tm_thurs_hol_tbl_type.count = 0 THEN
472 --error
473 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
474 FND_MESSAGE.set_token('API_NAME', l_api_name);
475 FND_MESSAGE.set_token ('VALUE', 'Holiday');
476 FND_MESSAGE.set_token('DAY', 'THURSDAY');
477 FND_MSG_PUB.add;
478 RAISE FND_API.g_exc_error;
479 ELSE ---Call Function To validate Thursday Holiday Table
480 VALIDATE_OVERLAP
481 (p_tm_tbl_type => lv_tm_thurs_hol_tbl_type,
482 x_return_status => lv_return_status
483 );
484 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
485 RAISE fnd_api.g_exc_error;
486 END IF;
487 END IF;
488
489 -- Validate Friday Holiday Table for Overlap
490 IF lv_tm_fri_hol_tbl_type.count = 0 THEN
491 --error
492 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
493 FND_MESSAGE.set_token('API_NAME', l_api_name);
494 FND_MESSAGE.set_token ('VALUE', 'Holiday');
495 FND_MESSAGE.set_token('DAY', 'FRIDAY');
496 FND_MSG_PUB.add;
497 RAISE FND_API.g_exc_error;
498 ELSE ---Call Function To validate Friday Holiday Table
499 VALIDATE_OVERLAP
500 (p_tm_tbl_type => lv_tm_fri_hol_tbl_type,
501 x_return_status => lv_return_status
502 );
503 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
504 RAISE fnd_api.g_exc_error;
505 END IF;
506 END IF;
507
508 -- Validate Saturday Holiday Table for Overlap
509 IF lv_tm_sat_hol_tbl_type.count = 0 THEN
510 --error
511 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
512 FND_MESSAGE.set_token('API_NAME', l_api_name);
513 FND_MESSAGE.set_token ('VALUE', 'Holiday');
514 FND_MESSAGE.set_token('DAY', 'SATURDAY');
515 FND_MSG_PUB.add;
516 RAISE FND_API.g_exc_error;
517 ELSE ---Call Function To validate Saturday Holiday Table
518 VALIDATE_OVERLAP
519 (p_tm_tbl_type => lv_tm_sat_hol_tbl_type,
520 x_return_status => lv_return_status
521 );
522 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
523 RAISE fnd_api.g_exc_error;
524 END IF;
525 END IF;
526
527 -- Validate Sunday Holiday Table for Overlap
528 IF lv_tm_sun_hol_tbl_type.count = 0 THEN
529 --error
530 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
531 FND_MESSAGE.set_token('API_NAME', l_api_name);
532 FND_MESSAGE.set_token ('VALUE', 'Holiday');
533 FND_MESSAGE.set_token('DAY', 'SUNDAY');
534 FND_MSG_PUB.add;
535 RAISE FND_API.g_exc_error;
536 ELSE ---Call Function To validate Friday Holiday Table
537 VALIDATE_OVERLAP
538 (p_tm_tbl_type => lv_tm_sun_hol_tbl_type,
539 x_return_status => lv_return_status
540 );
541 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
542 RAISE fnd_api.g_exc_error;
543 END IF;
544 END IF;
545
546 */
547
548 EXCEPTION
549
550 WHEN fnd_api.g_exc_error THEN
551 x_return_status := fnd_api.g_ret_sts_error;
552 x_msg_data := fnd_msg_pub.get;
553
554
555 WHEN OTHERS THEN
556 x_return_status := fnd_api.g_ret_sts_error;
557
558
559
560 END VALIDATE_SCHEDULE_OVERLAP; -- End of Procedure Validate_Schedule_Overlap
561
562 --************************************************************************
563 -- Procedure to validate if there is a complete labor schedule defined
564 -- for a specific business process
565 -- Defination of the Validate_Schedule_Overlap API
569 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
566 --************************************************************************
567 PROCEDURE VALIDATE_SCHEDULE_MISSING(
568 P_LABOR_SCHEDULE_TBL IN TM_SCHEDULE_TBL_TYPE,
570 X_MSG_COUNT OUT NOCOPY NUMBER,
571 X_MSG_DATA OUT NOCOPY VARCHAR2,
572 P_API_VERSION IN NUMBER,
573 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
574 ) IS
575
576 -- assuming the the record will be sorted by holiday_flag and start_time
577
578 lv_return_status VARCHAR2(1);
579 lv_return_message VARCHAR2(2000);
580
581 -- declaring counters for populating the individual
582 lv_counter1 NUMBER := 0;
583 lv_counter2 NUMBER := 0;
584 lv_counter3 NUMBER := 0;
585 lv_counter4 NUMBER := 0;
586 lv_counter5 NUMBER := 0;
587 lv_counter6 NUMBER := 0;
588 lv_counter7 NUMBER := 0;
589 lv_counter8 NUMBER := 0;
590 lv_counter9 NUMBER := 0;
591 lv_counter10 NUMBER := 0;
592 lv_counter11 NUMBER := 0;
593 lv_counter12 NUMBER := 0;
594 lv_counter13 NUMBER := 0;
595 lv_counter14 NUMBER := 0;
596
597 -- declaring the tables for the Regular and Holidays Days
598 lv_tm_mon_reg_tbl_type TM_TBL_TYPE;
599 lv_tm_tue_reg_tbl_type TM_TBL_TYPE;
600 lv_tm_wed_reg_tbl_type TM_TBL_TYPE;
601 lv_tm_thurs_reg_tbl_type TM_TBL_TYPE;
602 lv_tm_fri_reg_tbl_type TM_TBL_TYPE;
603 lv_tm_sat_reg_tbl_type TM_TBL_TYPE;
604 lv_tm_sun_reg_tbl_type TM_TBL_TYPE;
605 lv_tm_mon_hol_tbl_type TM_TBL_TYPE;
606 lv_tm_tue_hol_tbl_type TM_TBL_TYPE;
607 lv_tm_wed_hol_tbl_type TM_TBL_TYPE;
608 lv_tm_thurs_hol_tbl_type TM_TBL_TYPE;
609 lv_tm_fri_hoL_tbl_type TM_TBL_TYPE;
610 lv_tm_sat_hol_tbl_type TM_TBL_TYPE;
611 lv_tm_sun_hol_tbl_type TM_TBL_TYPE;
612
613 -- DEBUG
614 l_errm VARCHAR2(200);
615
616 l_api_version NUMBER := 1.0;
617 l_api_name VARCHAR2(30) := 'Validate_Schedule_Missing';
618 l_api_name_full VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
619
620
621 BEGIN
622
623 -- Standard call to check for call compatibility
624 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
625 G_PKG_NAME) THEN
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
627 END IF;
628
629 /* -- Initialize message list if p_init_msg_list is set to TRUE
630 IF FND_API.To_Boolean(p_init_msg_list) THEN
631 FND_MSG_PUB.Initialize;
632 END IF;
633 */
634
635 -- Initialize API return status to success
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638 FOR lv_temp IN 1..P_LABOR_SCHEDULE_TBL.COUNT LOOP
639 IF ((p_labor_schedule_tbl(lv_temp).monday_flag = 'Y') AND
640 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
641 -- The Schedule is for Monday - Regular Day
642 -- Populate The Monday Regular Table
643 -- initialize the lv_counter to 1
644 lv_counter1 := lv_counter1 + 1;
645 lv_tm_mon_reg_tbl_type(lv_counter1).start_time := p_labor_schedule_tbl(lv_temp).start_time;
646 lv_tm_mon_reg_tbl_type(lv_counter1).end_time := p_labor_schedule_tbl(lv_temp).end_time;
647 lv_tm_mon_reg_tbl_type(lv_counter1).day := 'MONDAY';
648 lv_tm_mon_reg_tbl_type(lv_counter1).holiday_flag := 'N';
649 END IF;
650
651 IF ((p_labor_schedule_tbl(lv_temp).tuesday_flag = 'Y') AND
652 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
653 -- The Schedule is for Tuesday - Regular Day
654 -- Populate The Tuesday Regular Table
655 -- Initialize lv_counter2 to 1
656 lv_counter2 := lv_counter2 + 1;
657 lv_tm_tue_reg_tbl_type(lv_counter2).start_time := p_labor_schedule_tbl(lv_temp).start_time;
658 lv_tm_tue_reg_tbl_type(lv_counter2).end_time := p_labor_schedule_tbl(lv_temp).end_time;
659 lv_tm_tue_reg_tbl_type(lv_counter2).day := 'TUESDAY';
660 lv_tm_tue_reg_tbl_type(lv_counter2).holiday_flag := 'N';
661 END IF;
662
663
664 IF ((p_labor_schedule_tbl(lv_temp).wednesday_flag = 'Y') AND
665 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
666 -- The Schedule is for Wednesday - Regular Day
667 -- Populate The Wednesday Regular Table
668 -- Initialize lv_counter3 to 1
669 lv_counter3 := lv_counter3 + 1;
670 lv_tm_wed_reg_tbl_type(lv_counter3).start_time := p_labor_schedule_tbl(lv_temp).start_time;
671 lv_tm_wed_reg_tbl_type(lv_counter3).end_time := p_labor_schedule_tbl(lv_temp).end_time;
672 lv_tm_wed_reg_tbl_type(lv_counter3).day := 'WEDNESDAY';
673 lv_tm_wed_reg_tbl_type(lv_counter3).holiday_flag := 'N';
674 END IF;
675
676 IF ((p_labor_schedule_tbl(lv_temp).thursday_flag = 'Y') AND
677 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
678 -- The Schedule is for Thursday - Regular Day
679 -- Populate The Thursday Regular Table
680 -- Initialize lv_counter4 to 1
681 lv_counter4 := lv_counter4 + 1;
682 lv_tm_thurs_reg_tbl_type(lv_counter4).start_time := p_labor_schedule_tbl(lv_temp).start_time;
683 lv_tm_thurs_reg_tbl_type(lv_counter4).end_time := p_labor_schedule_tbl(lv_temp).end_time;
684 lv_tm_thurs_reg_tbl_type(lv_counter4).day := 'THURSDAY';
685 lv_tm_thurs_reg_tbl_type(lv_counter4).holiday_flag := 'N';
686 END IF;
687
688 IF ((p_labor_schedule_tbl(lv_temp).friday_flag = 'Y') AND
689 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
690 -- The Schedule is for Friday - Regular Day
691 -- Populate The Friday Regular Table
692 -- Initialize lv_counter5 to 1
693 lv_counter5 := lv_counter5 + 1;
694 lv_tm_fri_reg_tbl_type(lv_counter5).start_time := p_labor_schedule_tbl(lv_temp).start_time;
698 END IF;
695 lv_tm_fri_reg_tbl_type(lv_counter5).end_time := p_labor_schedule_tbl(lv_temp).end_time;
696 lv_tm_thurs_reg_tbl_type(lv_counter5).day := 'FRIDAY';
697 lv_tm_thurs_reg_tbl_type(lv_counter5).holiday_flag := 'N';
699
700 IF ((p_labor_schedule_tbl(lv_temp).saturday_flag = 'Y') AND
701 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
702 -- The Schedule is for Saturday - Regular Day
703 -- Populate The Saturday Regular Table
704 -- Initialize the lv_counter6 to 1
705 lv_counter6 := lv_counter6 + 1;
706 lv_tm_sat_reg_tbl_type(lv_counter6).start_time := p_labor_schedule_tbl(lv_temp).start_time;
707 lv_tm_sat_reg_tbl_type(lv_counter6).end_time := p_labor_schedule_tbl(lv_temp).end_time;
708 lv_tm_sat_reg_tbl_type(lv_counter6).day := 'SATURDAY';
709 lv_tm_sat_reg_tbl_type(lv_counter6).holiday_flag := 'N';
710 END IF;
711
712 IF ((p_labor_schedule_tbl(lv_temp).sunday_flag = 'Y') AND
713 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'N')) THEN
714 -- The Schedule is for Sunday - Regular Day
715 -- Populate The Sunday Regular Table
716 -- Initialize the lv_counter7 to 1
717 lv_counter7 := lv_counter7 + 1;
718 lv_tm_sun_reg_tbl_type(lv_counter7).start_time := p_labor_schedule_tbl(lv_temp).start_time;
719 lv_tm_sun_reg_tbl_type(lv_counter7).end_time := p_labor_schedule_tbl(lv_temp).end_time;
720 lv_tm_sun_reg_tbl_type(lv_counter7).day := 'SUNDAY';
721 lv_tm_sun_reg_tbl_type(lv_counter7).holiday_flag := 'N';
722 END IF;
723
724 IF ((p_labor_schedule_tbl(lv_temp).monday_flag = 'Y') AND
725 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
726 -- The Schedule is for Monday - Holiday Day
727 -- Populate The Monday Holiday Table
728 -- Initialize lv_counter8 to 1
729 lv_counter8 := lv_counter8 + 1;
730 lv_tm_mon_hol_tbl_type(lv_counter8).start_time := p_labor_schedule_tbl(lv_temp).start_time;
731 lv_tm_mon_hol_tbl_type(lv_counter8).end_time := p_labor_schedule_tbl(lv_temp).end_time;
732 lv_tm_mon_hol_tbl_type(lv_counter8).day := 'MONDAY';
733 lv_tm_mon_hol_tbl_type(lv_counter8).holiday_flag := 'Y';
734 END IF;
735
736 IF ((p_labor_schedule_tbl(lv_temp).tuesday_flag = 'Y') AND
737 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
738 -- The Schedule is for Tuesday - Holiday Day
739 -- Populate The Tuesady Holiday Table
740 -- Initialize lv_counter9 to 1
741 lv_counter9 := lv_counter9 + 1;
742 lv_tm_tue_hol_tbl_type(lv_counter9).start_time := p_labor_schedule_tbl(lv_temp).start_time;
743 lv_tm_tue_hol_tbl_type(lv_counter9).end_time := p_labor_schedule_tbl(lv_temp).end_time;
744 lv_tm_mon_hol_tbl_type(lv_counter9).day := 'TUESDAY';
745 lv_tm_mon_hol_tbl_type(lv_counter9).holiday_flag := 'Y';
746
747 END IF;
748
749 IF ((p_labor_schedule_tbl(lv_temp).wednesday_flag = 'Y') AND
750 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
751 -- The Schedule is for Wednesday - Holiday Day
752 -- Populate the Wednesday Holiday Table
753 -- Initialize the lv_counter10 to 1
754 lv_counter10 := lv_counter10 + 1;
755 lv_tm_wed_hol_tbl_type(lv_counter10).start_time := p_labor_schedule_tbl(lv_temp).start_time;
756 lv_tm_wed_hol_tbl_type(lv_counter10).end_time := p_labor_schedule_tbl(lv_temp).end_time;
757 lv_tm_wed_hol_tbl_type(lv_counter10).day := 'WEDNESDAY';
758 lv_tm_wed_hol_tbl_type(lv_counter10).holiday_flag := 'Y';
759 END IF;
760
761 IF ((p_labor_schedule_tbl(lv_temp).thursday_flag = 'Y') AND
762 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
763 -- The Schedule is for Thursday - Holiday Day
764 -- Populate The Thursday Holiday Table
765 -- Initialize the lv_counter11 to 1
766 lv_counter11 := lv_counter11 + 1;
767 lv_tm_thurs_hol_tbl_type(lv_counter11).start_time := p_labor_schedule_tbl(lv_temp).start_time;
768 lv_tm_thurs_hol_tbl_type(lv_counter11).end_time := p_labor_schedule_tbl(lv_temp).end_time;
769 lv_tm_thurs_hol_tbl_type(lv_counter11).day := 'THURSDAY';
770 lv_tm_thurs_hol_tbl_type(lv_counter11).holiday_flag := 'Y';
771 END IF;
772
773 IF ((p_labor_schedule_tbl(lv_temp).friday_flag = 'Y') AND
774 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
775 -- The Schedule is for Friday - Holiday Day
776 -- Populate The Friday Holiday Table
777 -- Initialize lv_counter12 to 1
778 lv_counter12 := lv_counter12 + 1;
779 lv_tm_fri_hol_tbl_type(lv_counter12).start_time := p_labor_schedule_tbl(lv_temp).start_time;
780 lv_tm_fri_hol_tbl_type(lv_counter12).end_time := p_labor_schedule_tbl(lv_temp).end_time;
781 lv_tm_fri_hol_tbl_type(lv_counter12).day := 'FRIDAY';
782 lv_tm_fri_hol_tbl_type(lv_counter12).holiday_flag := 'Y';
783 END IF;
784
785 IF ((p_labor_schedule_tbl(lv_temp).saturday_flag = 'Y') AND
786 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
787 -- The Schedule is for Saturday - Holiday Day
788 -- Populate The Saturday Holiday Table
789 -- Initialize lv_counter13 to 1;
790 lv_counter13 := lv_counter13 + 1;
791 lv_tm_sat_hol_tbl_type(lv_counter13).start_time := p_labor_schedule_tbl(lv_temp).start_time;
792 lv_tm_sat_hol_tbl_type(lv_counter13).end_time := p_labor_schedule_tbl(lv_temp).end_time;
793 lv_tm_sat_hol_tbl_type(lv_counter12).day := 'SATURDAY';
794 lv_tm_sat_hol_tbl_type(lv_counter12).holiday_flag := 'Y';
795 END IF;
796
797 IF ((p_labor_schedule_tbl(lv_temp).sunday_flag = 'Y') AND
798 (p_labor_schedule_tbl(lv_temp).holiday_flag = 'Y')) THEN
799 -- The Schedule is for Sunday - Holiday Day
800 -- Populate The Sunday Holiday Table
804 lv_tm_sun_hol_tbl_type(lv_counter14).end_time := p_labor_schedule_tbl(lv_temp).end_time;
801 -- Initialize lv_counter13 to 1;
802 lv_counter14 := lv_counter14 + 1;
803 lv_tm_sun_hol_tbl_type(lv_counter14).start_time := p_labor_schedule_tbl(lv_temp).start_time;
805 lv_tm_sun_hol_tbl_type(lv_counter14).day := 'SUNDAY';
806 lv_tm_sun_hol_tbl_type(lv_counter14).holiday_flag := 'Y';
807 END IF;
808
809 END LOOP;
810
811 -- Validate Monday Regular Table for Completeness
812 IF lv_tm_mon_reg_tbl_type.count = 0 THEN
813 --error
814 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
815 FND_MESSAGE.set_token('API_NAME', l_api_name);
816 FND_MESSAGE.set_token('VALUE', 'Regular');
817 FND_MESSAGE.set_token('DAY', 'MONDAY');
818 FND_MSG_PUB.add;
819 RAISE FND_API.g_exc_error;
820 ELSE ---Call Function To validate Monday Table
821 VALIDATE_COMPLETE_CHECK
822 (p_tm_tbl_type => lv_tm_mon_reg_tbl_type,
823 x_return_status => lv_return_status
824 );
825 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
826 RAISE fnd_api.g_exc_error;
827 END IF;
828 END IF;
829
830 -- Validate Tuesday Regular Table for Completeness
831 IF lv_TM_TUE_REG_TBL_TYPE.COUNT = 0 THEN
832 -- error
833 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
834 FND_MESSAGE.set_token('API_NAME', l_api_name);
835 FND_MESSAGE.set_token('VALUE', 'Regular');
836 FND_MESSAGE.set_token('DAY', 'TUESDAY');
837 FND_MSG_PUB.add;
838 RAISE FND_API.g_exc_error;
839 ELSE -- Call Function to validate the Tuesday Table
840 VALIDATE_COMPLETE_CHECK
841 (p_tm_tbl_type => lv_tm_tue_reg_tbl_type,
842 x_return_status => lv_return_status
843 );
844 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
845 RAISE fnd_api.g_exc_error;
846 END IF;
847 END IF;
848
849 -- Validate Wednesday Regular Table for Completeness
850 IF lv_TM_WED_REG_TBL_TYPE.COUNT = 0 THEN
851 --error
852 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
853 FND_MESSAGE.set_token('API_NAME', l_api_name);
854 FND_MESSAGE.set_token('VALUE', 'Regular');
855 FND_MESSAGE.set_token('DAY', 'Wednesday');
856 FND_MSG_PUB.add;
857 RAISE FND_API.g_exc_error;
858
859 ELSE -- Call Function to validate
860 VALIDATE_COMPLETE_CHECK
861 (p_tm_tbl_type => lv_tm_wed_reg_tbl_type,
862 x_return_status => lv_return_status
863 );
864 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
865 RAISE fnd_api.g_exc_error;
866 END IF;
867 END IF;
868
869 -- Validate Thursday Regular Table for Completeness
870 IF lv_TM_THURS_REG_TBL_TYPE.COUNT = 0 THEN
871 --error
872 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
873 FND_MESSAGE.set_token('API_NAME', l_api_name);
874 FND_MESSAGE.set_token('VALUE', 'Regular');
875 FND_MESSAGE.set_token('DAY', 'THURSDAY');
876 FND_MSG_PUB.add;
877 RAISE FND_API.g_exc_error;
878 ELSE -- Call Function to validate
879 VALIDATE_COMPLETE_CHECK
880 (p_tm_tbl_type => lv_tm_thurs_reg_tbl_type,
881 x_return_status => lv_return_status
882 );
883 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
884 RAISE fnd_api.g_exc_error;
885 END IF;
886 END IF;
887
888 -- Validate Friday Regular Table for Completeness
889 IF lv_TM_FRI_REG_TBL_TYPE.COUNT = 0 THEN
890 --error
891 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
892 FND_MESSAGE.set_token('API_NAME', l_api_name);
893 FND_MESSAGE.set_token('VALUE', 'Regular');
894 FND_MESSAGE.set_token('DAY', 'FRIDAY');
895 FND_MSG_PUB.add;
896 RAISE FND_API.g_exc_error;
897 ELSE -- Call Function to validate
898 VALIDATE_COMPLETE_CHECK
899 (p_tm_tbl_type => lv_tm_fri_reg_tbl_type,
900 x_return_status => lv_return_status
901 );
902 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
903 RAISE fnd_api.g_exc_error;
904 END IF;
905 END IF;
906
907 -- Validate Saturday Regular Table for Completeness
908 IF lv_TM_SAT_REG_TBL_TYPE.COUNT = 0 THEN
909 --error
910 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
911 FND_MESSAGE.set_token('API_NAME', l_api_name);
912 FND_MESSAGE.set_token('VALUE', 'Regular');
913 FND_MESSAGE.set_token('DAY', 'SATURDAY');
914 FND_MSG_PUB.add;
915 RAISE FND_API.g_exc_error;
916 ELSE -- Call Function to validate
917 VALIDATE_COMPLETE_CHECK
918 (p_tm_tbl_type => lv_tm_sat_reg_tbl_type,
919 x_return_status => lv_return_status
920 );
921 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
922 RAISE fnd_api.g_exc_error;
923 END IF;
924 END IF;
925
926 -- Validate Sunday Regular Table for Completeness
927 IF lv_TM_SUN_REG_TBL_TYPE.COUNT = 0 THEN
928 --error
929 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
930 FND_MESSAGE.set_token('API_NAME', l_api_name);
931 FND_MESSAGE.set_token('VALUE', 'Regular');
932 FND_MESSAGE.set_token('DAY', 'SUNDAY');
933 FND_MSG_PUB.add;
934 RAISE FND_API.g_exc_error;
935 ELSE -- Call Function to validate
936 VALIDATE_COMPLETE_CHECK
937 (p_tm_tbl_type => lv_tm_sun_reg_tbl_type,
938 x_return_status => lv_return_status
939 );
943 END IF;
940 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
941 RAISE fnd_api.g_exc_error;
942 END IF;
944
945 -- Validate Monday Holiday Table for Completeness
946 -- Following Commented out for 11.5.9 release.
947 /* IF lv_tm_mon_hol_tbl_type.count = 0 THEN
948 --error
949 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
950 FND_MESSAGE.set_token('API_NAME', l_api_name);
951 FND_MESSAGE.set_token('VALUE', 'Holiday');
952 FND_MESSAGE.set_token('DAY', 'MONDAY');
953 FND_MSG_PUB.add;
954 RAISE FND_API.g_exc_error;
955 ELSE ---Call Function To validate Monday Holiday Table
956 VALIDATE_COMPLETE_CHECK
957 (p_tm_tbl_type => lv_tm_mon_hol_tbl_type,
958 x_return_status => lv_return_status
959 );
960 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
961 RAISE fnd_api.g_exc_error;
962 END IF;
963 END IF;
964
965 -- Validate Tuesday Holiday Table for Completeness
966 IF lv_tm_tue_hol_tbl_type.count = 0 THEN
967 --error
968 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
969 FND_MESSAGE.set_token('API_NAME', l_api_name);
970 FND_MESSAGE.set_token('VALUE', 'Holiday');
971 FND_MESSAGE.set_token('DAY', 'TUESDAY');
972 FND_MSG_PUB.add;
973 RAISE FND_API.g_exc_error;
974 ELSE ---Call Function To validate Tuesday Holiday Table
975 VALIDATE_COMPLETE_CHECK
976 (p_tm_tbl_type => lv_tm_tue_hol_tbl_type,
977 x_return_status => lv_return_status
978 );
979 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
980 RAISE fnd_api.g_exc_error;
981 END IF;
982 END IF;
983
984 -- Validate Wednesday Holiday Table for Completeness
985 IF lv_tm_wed_hol_tbl_type.count = 0 THEN
986 --error
987 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
988 FND_MESSAGE.set_token('API_NAME', l_api_name);
989 FND_MESSAGE.set_token('VALUE', 'Holiday');
990 FND_MESSAGE.set_token('DAY', 'WEDNESDAY');
991 FND_MSG_PUB.add;
992 RAISE FND_API.g_exc_error;
993 ELSE ---Call Function To validate Wednesday Holiday Table
994 VALIDATE_COMPLETE_CHECK
995 (p_tm_tbl_type => lv_tm_wed_hol_tbl_type,
996 x_return_status => lv_return_status
997 );
998 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
999 RAISE fnd_api.g_exc_error;
1000 END IF;
1001 END IF;
1002
1003 -- Validate Thursday Holiday Table for Completeness
1004 IF lv_tm_thurs_hol_tbl_type.count = 0 THEN
1005 --error
1006 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
1007 FND_MESSAGE.set_token('API_NAME', l_api_name);
1008 FND_MESSAGE.set_token('VALUE', 'Holiday');
1009 FND_MESSAGE.set_token('DAY', 'THURSDAY');
1010 FND_MSG_PUB.add;
1011 RAISE FND_API.g_exc_error;
1012 ELSE ---Call Function To validate Thursday Holiday Table
1013 VALIDATE_COMPLETE_CHECK
1014 (p_tm_tbl_type => lv_tm_thurs_hol_tbl_type,
1015 x_return_status => lv_return_status
1016 );
1017 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
1018 RAISE fnd_api.g_exc_error;
1019 END IF;
1020 END IF;
1021
1022 -- Validate Friday Holiday Table for Completeness
1023 IF lv_tm_fri_hol_tbl_type.count = 0 THEN
1024 --error
1025 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
1026 FND_MESSAGE.set_token('API_NAME', l_api_name);
1027 FND_MESSAGE.set_token('VALUE', 'Holiday');
1028 FND_MESSAGE.set_token('DAY', 'FRIDAY');
1029 FND_MSG_PUB.add;
1030 RAISE FND_API.g_exc_error;
1031 ELSE ---Call Function To validate Friday Holiday Table
1032 VALIDATE_COMPLETE_CHECK
1033 (p_tm_tbl_type => lv_tm_fri_hol_tbl_type,
1034 x_return_status => lv_return_status
1035 );
1036 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
1037 RAISE fnd_api.g_exc_error;
1038 END IF;
1039 END IF;
1040
1041 -- Validate Saturday Holiday Table for Completeness
1042 IF lv_tm_sat_hol_tbl_type.count = 0 THEN
1043 --error
1044 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
1045 FND_MESSAGE.set_token('API_NAME', l_api_name);
1046 FND_MESSAGE.set_token('VALUE', 'Holiday');
1047 FND_MESSAGE.set_token('DAY', 'SATURDAY');
1048 FND_MSG_PUB.add;
1049 RAISE FND_API.g_exc_error;
1050 ELSE ---Call Function To validate Saturday Holiday Table
1051 VALIDATE_COMPLETE_CHECK
1052 (p_tm_tbl_type => lv_tm_sat_hol_tbl_type,
1053 x_return_status => lv_return_status
1054 );
1055
1056 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
1057 RAISE fnd_api.g_exc_error;
1058 END IF;
1059 END IF;
1060
1061 -- Validate Sunday Holiday Table for Completeness
1062 IF lv_tm_sun_hol_tbl_type.count = 0 THEN
1063 --error
1064 FND_MESSAGE.set_name('CS','CS_CHG_API_DAY_NOT_SETUP');
1065 FND_MESSAGE.set_token('API_NAME', l_api_name);
1066 FND_MESSAGE.set_token('VALUE', 'Holiday');
1067 FND_MESSAGE.set_token('DAY', 'SUNDAY');
1068 FND_MSG_PUB.add;
1069 RAISE FND_API.g_exc_error;
1070 ELSE ---Call Function To validate Friday Holiday Table
1071 VALIDATE_COMPLETE_CHECK
1072 (p_tm_tbl_type => lv_tm_sun_hol_tbl_type,
1073 x_return_status => lv_return_status
1074 );
1075 IF lv_return_status <> fnd_api.g_ret_sts_success THEN
1076 RAISE fnd_api.g_exc_error;
1077 END IF;
1078 END IF;
1082
1079 */
1080
1081 EXCEPTION
1083 WHEN fnd_api.g_exc_error THEN
1084 x_return_status := fnd_api.g_ret_sts_error;
1085 x_msg_data := fnd_msg_pub.get;
1086
1087 WHEN OTHERS THEN
1088
1089 l_errm := SQLERRM;
1090
1091 END VALIDATE_SCHEDULE_MISSING;
1092
1093 --************************************************************************
1094 -- Procedure to Process an input debrief labor activity, break in into
1095 -- several Time and Material labor coverage time segments based on
1096 -- the Time and Material Schedule table. This procedure will return a set of
1097 -- records which contain several labor covergae time that represents the complete
1098 -- input activity time.
1099 -- Defination of the Get_labor_Coverages API
1100 --************************************************************************
1101
1102 PROCEDURE GET_LABOR_COVERAGES(
1103 P_BUSINESS_PROCESS_ID IN NUMBER,
1104 P_ACTIVITY_START_DATE_TIME IN DATE,
1105 P_ACTIVITY_END_DATE_TIME IN DATE,
1106 X_LABOR_COVERAGE_TBL OUT NOCOPY TM_COVERAGE_TBL_TYPE,
1107 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1108 X_MSG_COUNT OUT NOCOPY NUMBER,
1109 X_MSG_DATA OUT NOCOPY VARCHAR2,
1110 P_API_VERSION IN NUMBER,
1111 P_INIT_MSG_LIST IN VARCHAR2
1112 ) IS
1113
1114 --Time Card Record
1115 TYPE TM_CARD_REC IS RECORD (START_TIME DATE,
1116 END_TIME DATE,
1117 DAY VARCHAR2(10),
1118 HOLIDAY_FLAG VARCHAR2(1) DEFAULT 'N');
1119
1120 time_card_table TM_CARD_REC;
1121 TYPE ref_cursor IS REF CURSOR;
1122 tmpl_csr ref_cursor;
1123 l_start_time DATE;
1124 l_inv_item_id NUMBER;
1125 l_end_time DATE;
1126 --Added to Fix Bug # 3269347
1127 l_end_time2 DATE;
1128 l_api_name CONSTANT VARCHAR2(30) := 'Get_Labor_Coverages';
1129 l_api_name_full CONSTANT VARCHAR2(80) := G_PKG_NAME ||'.'||l_api_name;
1130 l_api_version CONSTANT NUMBER := 1;
1131 l_labor_coverages_tbl cs_tm_labor_schedule_pvt.tm_coverage_tbl_type;
1132 i BINARY_INTEGER := 1;
1133 j BINARY_INTEGER := 0;
1134 l_stmt VARCHAR2(4000);
1135 l_proc VARCHAR2(200);
1136 l_hol VARCHAR2(200);
1137 l_order VARCHAR2(200);
1138 l_day VARCHAR2(100);
1139 l_days_gap NUMBER;
1140 l_found VARCHAR2(1) := 'N';
1141 l_day_number NUMBER;
1142
1143 l_tm_sched_complete_flag VARCHAR2(1);
1144
1145 -- DEBUG ONLY
1146 l_errm varchar2(200);
1147
1148 -- Added to resolve bug # 2933203 - mviswana
1149 CURSOR tm_validate_csr(p_business_process_id number) IS
1150 SELECT NVL(tm_sched_complete_flag, 'N')
1151 FROM cs_business_processes
1152 WHERE business_process_id = p_business_process_id;
1153
1154
1155 BEGIN
1156
1157 -- Standard Call to check API Compatibility
1158 IF NOT FND_API.Compatible_API_CALL(l_api_version,
1159 p_api_version,
1160 l_api_name,
1161 G_PKG_NAME) THEN
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163 END IF;
1164
1165 -- Initialize the message list if p_init_msg_list is set to TRUE
1166 IF FND_API.To_Boolean(p_init_msg_list) THEN
1167 FND_MSG_PUB.Initialize;
1168 END IF;
1169
1170 -- Initialize the API Return Success to TRUE
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 -- Added to resolve bug # 2933203 - mviswana
1174
1175 -- Check if the Time and Material Labor Schedule is valid for the business
1176 -- process that is passed
1177 -- Retrieve the tm_sched_complete_flag for the business_process_id.
1178
1179 OPEN tm_validate_csr(p_business_process_id);
1180 FETCH tm_validate_csr into l_tm_sched_complete_flag;
1181 IF tm_validate_csr%NOTFOUND THEN
1182 CLOSE tm_validate_csr;
1183
1184 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1185 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1186 FND_MESSAGE.Set_Token('VALUE', p_business_process_id);
1187 FND_MESSAGE.Set_Token('PARAMETER', 'p_business_process_id');
1188 FND_MSG_PUB.Add;
1189 RAISE FND_API.g_exc_error;
1190
1191 END IF;
1192 CLOSE tm_validate_csr;
1193
1194 -- If the Tm_sched_complete_flag is 'N' then return error;
1195
1196 IF l_tm_sched_complete_flag <> 'Y' THEN
1197 --raise error
1198 FND_MESSAGE.Set_Name('CS', 'CS_CHG_API_TM_SCHED_INCOMPLETE');
1199 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1200 FND_MESSAGE.Set_Token('BUSINESS_PROC_ID', p_business_process_id);
1201 FND_MSG_PUB.Add;
1202 RAISE FND_API.g_exc_error;
1203 END IF;
1204
1205 -- Check to see if activity occurs in the same day
1206 IF trunc(p_activity_end_date_time) - trunc(p_activity_start_date_time) = 0 THEN
1207
1208 --If activity on the same day then populate time card record with in parameters
1209 time_card_table.start_time := trunc(sysdate) +
1210 (p_activity_start_date_time - trunc(p_activity_start_date_time));
1211 time_card_table.end_time := trunc(sysdate) +
1212 (p_activity_end_date_time - trunc(p_activity_end_date_time));
1213
1214 -- Added to fix Fixed Bug # 3528586
1215 -- l_day_number required for getting the day of the week (eg 'D' comes out as 1)
1219 IF l_day_number = 1 THEN
1216 l_day_number := to_char( P_ACTIVITY_START_DATE_TIME, 'D');
1217
1218 --Do the decode of the l_day_number
1220 time_card_table.day := 'SUNDAY';
1221 ELSIF l_day_number = 2 THEN
1222 time_card_table.day := 'MONDAY';
1223 ELSIF l_day_number = 3 THEN
1224 time_card_table.day := 'TUESDAY';
1225 ELSIF l_day_number = 4 THEN
1226 time_card_table.day := 'WEDNESDAY';
1227 ELSIF l_day_number = 5 THEN
1228 time_card_table.day := 'THURSDAY';
1229 ELSIF l_day_number = 6 THEN
1230 time_card_table.day := 'FRIDAY';
1231 ELSIF l_day_number = 7 THEN
1232 time_card_table.day := 'SATURDAY';
1233 END IF;
1234
1235 -- Call ATG program with p_start_acitivty
1236 -- Time_card_table.holiday_flag := ATG(P_ACTIVITY_START_DATE_TIME);
1237 time_card_table.holiday_flag := 'N';
1238
1239 ELSE
1240 -- END activity does not end on the same day as START activity (outside the scope of this program)
1241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1242 END IF;
1243
1244
1245 l_stmt := 'SELECT trunc(sysdate)+(start_hour/24)+(start_minute/(24*60)) START_TIME, ';
1246 l_stmt := l_stmt||'trunc(sysdate)+(end_hour/24)+(end_minute/(24*60)) END_TIME, ';
1247 l_stmt := l_stmt||'inventory_item_id FROM cs_tm_labor_schedules WHERE 1 = 1 AND ';
1248 l_proc := 'Business_process_id = '||P_BUSINESS_PROCESS_ID||' AND ';
1249 l_hol := 'NVL(HOLIDAY_FLAG, ''N'') = '||''''||Time_card_table.holiday_flag||''''||' AND ';
1250 l_day := Time_card_table.day||'_FLAG = ''Y'' ';
1251 l_order := 'order by trunc(sysdate) + (start_time - trunc(start_time))';
1252 l_stmt := l_stmt || l_proc || l_hol || l_day || l_order;
1253
1254 OPEN tmpl_csr FOR l_stmt;
1255 -- Used to restore retain the date component of the OUT records
1256 l_days_gap := TRUNC(p_activity_start_date_time) - TRUNC(SYSDATE);
1257 LOOP
1258
1259 -- infinite loop not possible because each day is fully defined
1260
1261 FETCH tmpl_csr INTO
1262 l_start_time,
1263 l_end_time,
1264 l_inv_item_id;
1265
1266 IF tmpl_csr%FOUND THEN
1267 IF l_found = 'N' THEN
1268 --Added to Fix Bug # 3269347
1269 l_end_time2 := l_end_time - (1/86400);
1270
1271 IF Time_card_table.start_time BETWEEN l_start_time AND l_end_time2 THEN
1272 j := j + 1;
1273 l_found := 'Y';
1274 X_LABOR_COVERAGE_TBL(j).labor_start_date_time := (Time_card_table.start_time + l_days_gap);
1275 X_LABOR_COVERAGE_TBL(j).inventory_item_id := l_inv_item_id;
1276
1277 --Changed from <= to < to Fix Bug # 3269347
1278
1279 IF l_end_time < Time_card_table.end_time THEN
1280 X_LABOR_COVERAGE_TBL(j).labor_end_date_time := (l_end_time + l_days_gap);
1281 ELSE
1282 X_LABOR_COVERAGE_TBL(j).labor_end_date_time := (time_card_table.end_time + l_days_gap);
1283 EXIT;
1284 END IF;
1285 END IF;
1286 --l_found = 'Y'
1287 ELSE
1288 j := j + 1;
1289
1290 X_LABOR_COVERAGE_TBL(j).labor_start_date_time := (l_start_time + l_days_gap);
1291 X_LABOR_COVERAGE_TBL(j).inventory_item_id := l_inv_item_id;
1292
1293 --Changed from >= to > to Fix Bug # 3269347
1294
1295 IF Time_card_table.end_time > l_end_time THEN
1296 X_LABOR_COVERAGE_TBL(j).labor_end_date_time := (l_end_time + l_days_gap);
1297 ELSE
1298 X_LABOR_COVERAGE_TBL(j).labor_end_date_time := (Time_card_table.end_time + l_days_gap);
1299 EXIT;
1300 END IF;
1301 END IF;
1302 ELSE
1303 EXIT;
1304 END IF;
1305 END LOOP;
1306 CLOSE tmpl_csr;
1307
1308 EXCEPTION
1309
1310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1311 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1312
1313 WHEN FND_API.G_EXC_ERROR THEN
1314 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1315
1316 WHEN OTHERS THEN
1317 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1318
1319 END GET_LABOR_COVERAGES;
1320
1321
1322 --************************************************************************
1323 -- Private Procedure Validate_Complete_Check
1324 --************************************************************************
1325
1326 PROCEDURE VALIDATE_COMPLETE_CHECK
1327 (P_TM_TBL_TYPE IN TM_TBL_TYPE,
1328 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1329 ) IS
1330
1331 lv_index BINARY_INTEGER := 0;
1332 lv_sum NUMBER := 0;
1333
1334 e_gap_in_schedule EXCEPTION;
1335 e_comp_chk_failure EXCEPTION;
1336
1337 l_api_name VARCHAR2(30) := 'Validate_Schedule_Missing';
1338 l_api_name_full VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
1339
1340
1341 -- DEBUG
1342 l_errm VARCHAR2(200);
1343
1344 BEGIN
1345
1346 lv_index := P_TM_TBL_TYPE.FIRST;
1347
1348 -- FOR lv_temp IN 1..P_TM_TBL_TYPE.COUNT LOOP
1349
1350 LOOP
1351 IF lv_index = P_TM_TBL_TYPE.FIRST THEN
1352 lv_sum := lv_sum + (P_TM_TBL_TYPE(lv_index).end_time - P_TM_TBL_TYPE(lv_index).start_time);
1353 ELSE
1354 IF P_TM_TBL_TYPE(lv_index).start_time <> P_TM_TBL_TYPE(lv_index - 1).end_time THEN
1355 --error
1356 fnd_message.set_name('CS','CS_CHG_API_GAP_IN_SCHEDULE');
1357 fnd_message.set_token('API_NAME', l_api_name);
1358 fnd_message.set_token('DAY', P_TM_TBL_TYPE(lv_index).day);
1359 fnd_message.set_token('START_TIME', TO_CHAR(P_TM_TBL_TYPE(lv_index).start_time,'HH24:MI'));
1363 RAISE fnd_api.g_exc_error;
1360 fnd_message.set_token('PREVIOUS_END_TIME', TO_CHAR(P_TM_TBL_TYPE(lv_index - 1).end_time, 'HH24:MI'));
1361 --fnd_message.set_token('HOLIDAY_FLAG', P_TM_TBL_TYPE(lv_index).holiday_flag);
1362 fnd_msg_pub.add;
1364 ELSE
1365 lv_sum := lv_sum + (P_TM_TBL_TYPE(lv_index).end_time - P_TM_TBL_TYPE(lv_index).start_time);
1366 END IF;
1367 END IF;
1368
1369 EXIT WHEN lv_index = P_TM_TBL_TYPE.LAST;
1370 lv_index := P_TM_TBL_TYPE.NEXT(lv_index);
1371
1372 END LOOP;
1373
1374 IF lv_sum < .9993 THEN
1375 --error
1376 --RAISE e_comp_chk_failure;
1377 fnd_message.set_name('CS','CS_CHG_API_COMPLETE_CHECK_FAIL');
1378 fnd_message.set_token('API_NAME', l_api_name);
1379 fnd_msg_pub.add;
1380 RAISE fnd_api.g_exc_error;
1381
1382 END IF;
1383
1384 EXCEPTION
1385
1386 WHEN fnd_api.g_exc_error THEN
1387 x_return_status := fnd_api.g_ret_sts_error;
1388
1389 WHEN OTHERS THEN
1390 l_errm := SQLERRM;
1391 END VALIDATE_COMPLETE_CHECK;
1392
1393 --************************************************************************
1394 -- Private Procedure Validate_Complete_Check
1395 --************************************************************************
1396
1397 PROCEDURE VALIDATE_OVERLAP
1398 (P_TM_TBL_TYPE IN TM_TBL_TYPE,
1399 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1400 ) IS
1401
1402 lv_index BINARY_INTEGER := 0;
1403
1404 -- DEBUG
1405 l_errm VARCHAR2(200);
1406
1407 l_api_version NUMBER := 1.0;
1408 l_api_name VARCHAR2(30) := 'Validate_Schedule_Overlap';
1409 l_api_name_full VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
1410
1411
1412 BEGIN
1413
1414 lv_index := P_TM_TBL_TYPE.FIRST;
1415
1416 -- FOR lv_temp IN 1..P_TM_TBL_TYPE.COUNT LOOP
1417
1418 LOOP
1419 IF lv_index <> P_TM_TBL_TYPE.FIRST THEN
1420 IF P_TM_TBL_TYPE(lv_index).start_time < P_TM_TBL_TYPE(lv_index - 1).end_time THEN
1421 --error
1422 fnd_message.set_name('CS','CS_CHG_API_OVERLAP_FOUND');
1423 fnd_message.set_token('API_NAME', l_api_name);
1424 fnd_message.set_token('DAY', P_TM_TBL_TYPE(lv_index).day);
1425 fnd_message.set_token('START_TIME', TO_CHAR(P_TM_TBL_TYPE(lv_index).start_time,'HH24:MI'));
1426 fnd_message.set_token('PREVIOUS_END_TIME', TO_CHAR(P_TM_TBL_TYPE(lv_index - 1).end_time, 'HH24:MI'));
1427 --fnd_message.set_token('HOLIDAY_FLAG', P_TM_TBL_TYPE(lv_index).holiday_flag);
1428 fnd_msg_pub.add;
1429 RAISE fnd_api.g_exc_error;
1430 ELSE
1431 null;
1432 END IF;
1433 END IF;
1434 EXIT WHEN lv_index = P_TM_TBL_TYPE.LAST;
1435 lv_index := P_TM_TBL_TYPE.NEXT(lv_index);
1436
1437 END LOOP;
1438
1439 EXCEPTION
1440
1441 WHEN fnd_api.g_exc_error THEN
1442 x_return_status := fnd_api.g_ret_sts_error;
1443
1444 WHEN NO_DATA_FOUND THEN
1445 x_return_status := fnd_api.g_ret_sts_error;
1446
1447 WHEN OTHERS THEN
1448 --l_errm := SQLERRM;
1449 x_return_status := fnd_api.g_ret_sts_error;
1450
1451 END VALIDATE_OVERLAP;
1452
1453 END CS_TM_LABOR_SCHEDULE_PVT;