1 PACKAGE BODY CSF_ACCESS_HOURS_PUB as
2 /* $Header: CSFPACHB.pls 120.2 2006/05/10 02:44:40 srengana noship $ */
3 -- Start of Comments
4 -- Package name : CSF_ACCESS_HOURS_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_ACCESS_HOUR_PUB';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'CSFPACHB.pls';
11 -- ---------------------------------
12 -- private global package variables
13 -- ---------------------------------
14 g_user_id number;
15 g_login_id number;
16 -----------------------------------
17 --private api's
18 -----------------------------------
19 PROCEDURE LOCK_ACCESS_HOURS
20 ( p_access_hour_id number,
21 p_API_VERSION NUMBER,
22 p_init_msg_list varchar2 default null,
23 p_object_version_number number,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_data OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER);
27
28 function get_task_status_flag (p_task_id in number) return varchar2;
29
30 PROCEDURE CHECK_PARAMETERS(
31 p_CALLING_ROUTINE VARCHAR2 ,
32 p_TASK_ID NUMBER,
33 p_ACCESS_HOUR_REQD VARCHAR2 default null,
34 p_AFTER_HOURS_FLAG VARCHAR2 default null,
35 p_MONDAY_FIRST_START DATE default TO_DATE(NULL),
36 p_MONDAY_FIRST_END DATE default TO_DATE(NULL),
37 p_TUESDAY_FIRST_START DATE default TO_DATE(NULL),
38 p_TUESDAY_FIRST_END DATE default TO_DATE(NULL) ,
39 p_WEDNESDAY_FIRST_START DATE default TO_DATE(NULL),
40 p_WEDNESDAY_FIRST_END DATE default TO_DATE(NULL),
41 p_THURSDAY_FIRST_START DATE default TO_DATE(NULL),
42 p_THURSDAY_FIRST_END DATE default TO_DATE(NULL),
43 p_FRIDAY_FIRST_START DATE default TO_DATE(NULL),
44 p_FRIDAY_FIRST_END DATE default TO_DATE(NULL),
45 p_SATURDAY_FIRST_START DATE default TO_DATE(NULL),
46 p_SATURDAY_FIRST_END DATE default TO_DATE(NULL),
47 p_SUNDAY_FIRST_START DATE default TO_DATE(NULL),
48 p_SUNDAY_FIRST_END DATE default TO_DATE(NULL),
49 p_MONDAY_SECOND_START DATE default TO_DATE(NULL) ,
50 p_MONDAY_SECOND_END DATE default TO_DATE(NULL),
51 p_TUESDAY_SECOND_START DATE default TO_DATE(NULL),
52 p_TUESDAY_SECOND_END DATE default TO_DATE(NULL) ,
53 p_WEDNESDAY_SECOND_START DATE default TO_DATE(NULL),
54 p_WEDNESDAY_SECOND_END DATE default TO_DATE(NULL),
55 p_THURSDAY_SECOND_START DATE default TO_DATE(NULL),
56 p_THURSDAY_SECOND_END DATE default TO_DATE(NULL),
57 p_FRIDAY_SECOND_START DATE default TO_DATE(NULL),
58 p_FRIDAY_SECOND_END DATE default TO_DATE(NULL),
59 p_SATURDAY_SECOND_START DATE default TO_DATE(NULL),
60 p_SATURDAY_SECOND_END DATE default TO_DATE(NULL),
61 p_SUNDAY_SECOND_START DATE default TO_DATE(NULL),
62 p_SUNDAY_SECOND_END DATE default TO_DATE(NULL),
63 p_DESCRIPTION VARCHAR2 DEFAULT null,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER
67
68
69 );
70
71
72 PROCEDURE LOCK_ACCESS_HOURS
73 ( p_access_hour_id in number,
74 p_API_VERSION NUMBER,
75 p_init_msg_list varchar2 default NULL,
76 -- p_commit in varchar2 default fnd_api.g_false,
77 p_object_version_number in number,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER)
81
82
83 IS
84
85 l_return_status varchar2(100);
86 l_msg_count NUMBER;
87 l_msg_data varchar2(1000);
88 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PUB.LOCK_ACCESS_HOURS';
89 l_sta varchar2(1);
90 l_cnt number;
91 l_msg varchar2(2000);
92 l_api_version CONSTANT Number := 1.0 ;
93
94
95 BEGIN
96
97 SAVEPOINT lock_access_hours_pub;
98 x_return_status := fnd_api.g_ret_sts_success;
99
100 IF NOT fnd_api.compatible_api_call (
101 l_api_version,
102 p_api_version,
103 l_api_name_full,
104 g_pkg_name
105 )
106 then
107 raise fnd_Api.g_exc_unexpected_error;
108 end if;
109
110 IF fnd_api.to_boolean( nvl(p_init_msg_list,fnd_api.g_false)) then
111 fnd_msg_pub.initialize;
112 end if;
113
114
115
116 If p_ACCESS_HOUR_ID is NULL then
117 fnd_message.set_name ('CSF','CSF_ACCESS_INVALID_PARAMETER');--message required
118 fnd_msg_pub.add;
119 raise fnd_api.g_exc_error;
120 end if;
121
122 CSF_ACCESS_HOURS_PVT.LOCK_ACCESS_HOURS(
123
124 p_API_VERSION => 1.0,
125 p_INIT_MSG_LIST => 'F',
126 p_ACCESS_HOUR_ID =>p_ACCESS_HOUR_ID,
127 p_object_version_number=>p_OBJECT_VERSION_NUMBER,
128 x_return_status => l_sta,
129 x_msg_count => l_cnt,
130 x_msg_data => l_msg
131 );
132
133 If l_return_status = fnd_api.g_ret_sts_error then
134 raise fnd_api.g_exc_error;
135 end if;
136
137 If l_return_status = fnd_api.g_ret_sts_unexp_error then
138 raise fnd_Api.g_exc_unexpected_error;
139 end if;
140
141 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
142
143 EXCEPTION
144
145 WHEN fnd_api.g_exc_error then
146 ROLLBACK TO lock_access_hours_pub;
147 x_return_status :=fnd_api.g_ret_sts_error;
148 /*x_msg_count := l_msg_count;
149 x_msg_data := l_msg_data;*/
150 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
151
152 WHEN fnd_api.g_exc_unexpected_error then
153 ROLLBACK TO lock_access_hours_pub;
154 x_return_status := fnd_api.g_ret_sts_unexp_error;
155 /*x_msg_count := l_msg_count;
156 x_msg_data := l_msg_data;*/
157 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
158
159 WHEN OTHERS then
160 ROLLBACK TO lock_access_hours_pub;
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162 /*x_msg_count := l_msg_count;
163 x_msg_data := l_msg_data;*/
164 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
165
166
167
168
169 END LOCK_ACCESS_HOURS;
170
171 function get_task_status_flag (p_task_id number) return varchar2
172 is
173 cursor c_task_status(p_task_id number) is
174 select
175 ASSIGNED_FLAG,
176 WORKING_FLAG ,
177 APPROVED_FLAG ,
178 COMPLETED_FLAG,
179 CANCELLED_FLAG,
180 REJECTED_FLAG,
181 ACCEPTED_FLAG,
182 ON_HOLD_FLAG ,
183 SCHEDULABLE_FLAG,
184 CLOSED_FLAG,
185 DELETE_ALLOWED_FLAG,
186 TASK_STATUS_FLAG ,
187 ASSIGNMENT_STATUS_FLAG,
188 SCHEDULED_START_DATE,
189 SCHEDULED_END_DATE,
190 START_DATE_TYPE,
191 END_DATE_TYPE,
192 ACTUAL_START_DATE,
193 ACTUAL_END_DATE
194 from
195 jtf_task_statuses_vl jtsv,jtf_tasks_b jtb
196 where
197 jtb.task_id=p_task_id
198 and
199 jtsv.task_status_id=jtb.task_status_id ;
200
201 l_ASSIGNED_FLAG varchar2(1);
202 l_WORKING_FLAG varchar2(1);
203 l_APPROVED_FLAG varchar2(1);
204 l_CANCELLED_FLAG varchar2(1);
205 l_COMPLETED_FLAG varchar2(1);
206 l_REJECTED_FLAG varchar2(1);
207 l_ACCEPTED_FLAG varchar2(1);
208 l_ON_HOLD_FLAG varchar2(1);
209 l_SCHEDULABLE_FLAG varchar2(1);
210 l_CLOSED_FLAG varchar2(1);
211 l_DELETE_ALLOWED_FLAG varchar2(1);
212 l_TASK_STATUS_FLAG varchar2(1);
213 l_ASSIGNMENT_STATUS_FLAG varchar2(1);
214 l_scheduled_start_date date;
215 l_scheduled_end_date date;
216 l_actual_start_date date;
217 l_actual_end_date date;
218 l_start_date_type varchar2(80);
219 l_end_date_type varchar2(80);
220
221 begin
222 open c_task_status(p_task_id);
223 fetch c_task_status into l_ASSIGNED_FLAG,l_WORKING_FLAG ,l_APPROVED_FLAG ,l_COMPLETED_FLAG,
224 l_CANCELLED_FLAG,l_REJECTED_FLAG,l_ACCEPTED_FLAG,l_ON_HOLD_FLAG ,l_SCHEDULABLE_FLAG,l_CLOSED_FLAG,
225 l_DELETE_ALLOWED_FLAG,l_TASK_STATUS_FLAG ,l_ASSIGNMENT_STATUS_FLAG ,l_scheduled_start_date,l_scheduled_end_date,l_start_date_type, l_end_date_type,l_actual_start_date,l_actual_end_date;
226 close c_task_status;
227
228 if l_actual_start_date is not null or l_actual_end_date is not null
229 or l_working_flag ='Y' or l_rejected_flag='Y' or l_completed_flag='Y' or l_closed_flag='Y'
230 then
231 return 'W';
232 elsif l_scheduled_start_date is NULL and l_scheduled_end_date is NULL and (l_SCHEDULABLE_FLAG ='Y' OR l_ON_HOLD_FLAG='Y')
233 then
234 return 'S'; -- for tasks not yet scheduled
235 elsif (l_SCHEDULABLE_FLAG ='Y'or l_ASSIGNED_FLAG = 'Y') and l_scheduled_start_date is not NULL and l_scheduled_end_date is not NULL then
236 return 'A'; --for Planned /Assigned task
237 end if;
238 return 'X';
239
240 end get_task_status_flag;
241
242 PROCEDURE CHECK_PARAMETERS(
243 p_CALLING_ROUTINE VARCHAR2,
244 p_TASK_ID NUMBER,
245 p_ACCESS_HOUR_REQD VARCHAR2 ,
246 p_AFTER_HOURS_FLAG VARCHAR2,
247 p_MONDAY_FIRST_START DATE ,
248 p_MONDAY_FIRST_END DATE ,
249 p_TUESDAY_FIRST_START DATE ,
250 p_TUESDAY_FIRST_END DATE ,
251 p_WEDNESDAY_FIRST_START DATE ,
252 p_WEDNESDAY_FIRST_END DATE ,
253 p_THURSDAY_FIRST_START DATE ,
254 p_THURSDAY_FIRST_END DATE ,
255 p_FRIDAY_FIRST_START DATE ,
256 p_FRIDAY_FIRST_END DATE ,
257 p_SATURDAY_FIRST_START DATE ,
258 p_SATURDAY_FIRST_END DATE ,
259 p_SUNDAY_FIRST_START DATE ,
260 p_SUNDAY_FIRST_END DATE,
261 p_MONDAY_SECOND_START DATE ,
262 p_MONDAY_SECOND_END DATE ,
263 p_TUESDAY_SECOND_START DATE ,
264 p_TUESDAY_SECOND_END DATE ,
265 p_WEDNESDAY_SECOND_START DATE,
266 p_WEDNESDAY_SECOND_END DATE ,
267 p_THURSDAY_SECOND_START DATE,
268 p_THURSDAY_SECOND_END DATE ,
269 p_FRIDAY_SECOND_START DATE,
270 p_FRIDAY_SECOND_END DATE,
271 p_SATURDAY_SECOND_START DATE,
272 p_SATURDAY_SECOND_END DATE ,
273 p_SUNDAY_SECOND_START DATE,
274 p_SUNDAY_SECOND_END DATE ,
275 p_DESCRIPTION VARCHAR2,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_data OUT NOCOPY VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER
279
280
281 )
282
283 IS
284 l_task_status varchar2(1);
285
286 cursor c_existing_values(p_task_id number) is
287 select
288 ACCESSHOUR_REQUIRED,
289 AFTER_HOURS_FLAG,
290 MONDAY_FIRST_START,
291 MONDAY_FIRST_END,
292 MONDAY_SECOND_START,
293 MONDAY_SECOND_END,
294 TUESDAY_FIRST_START,
295 TUESDAY_FIRST_END,
296 TUESDAY_SECOND_START,
297 TUESDAY_SECOND_END,
298 WEDNESDAY_FIRST_START,
299 WEDNESDAY_FIRST_END,
300 WEDNESDAY_SECOND_START,
301 WEDNESDAY_SECOND_END,
302 THURSDAY_FIRST_START,
303 THURSDAY_FIRST_END,
304 THURSDAY_SECOND_START,
305 THURSDAY_SECOND_END,
306 FRIDAY_FIRST_START,
307 FRIDAY_FIRST_END,
308 FRIDAY_SECOND_START,
309 FRIDAY_SECOND_END,
310 SATURDAY_FIRST_START,
311 SATURDAY_FIRST_END,
312 SATURDAY_SECOND_START,
316 SUNDAY_SECOND_START,
313 SATURDAY_SECOND_END,
314 SUNDAY_FIRST_START,
315 SUNDAY_FIRST_END,
317 SUNDAY_SECOND_END,
318 DESCRIPTION
319 from csf_access_hours_vl
320 where task_id=p_task_id;
321
322 l_accesshour_required varchar2(1);
323 l_after_hours_flag varchar2(1);
324 l_MONDAY_FIRST_START date;
325 l_MONDAY_FIRST_END date;
326 l_MONDAY_SECOND_START date;
327 l_MONDAY_SECOND_END date;
328 l_TUESDAY_FIRST_START date;
329 l_TUESDAY_FIRST_END date;
330 l_TUESDAY_SECOND_START date;
331 l_TUESDAY_SECOND_END date;
332 l_WEDNESDAY_FIRST_START date;
333 l_WEDNESDAY_FIRST_END date;
334 l_WEDNESDAY_SECOND_START date;
335 l_WEDNESDAY_SECOND_END date;
336 l_THURSDAY_FIRST_START date;
337 l_THURSDAY_FIRST_END date;
338 l_THURSDAY_SECOND_START date;
339 l_THURSDAY_SECOND_END date;
340 l_FRIDAY_FIRST_START date;
341 l_FRIDAY_FIRST_END date;
342 l_FRIDAY_SECOND_START date;
343 l_FRIDAY_SECOND_END date;
344 l_SATURDAY_FIRST_START date;
345 l_SATURDAY_FIRST_END date;
346 l_SATURDAY_SECOND_START date;
347 l_SATURDAY_SECOND_END date;
348 l_SUNDAY_FIRST_START date;
349 l_SUNDAY_FIRST_END date;
350 l_SUNDAY_SECOND_START date;
351 l_SUNDAY_SECOND_END date;
352 l_DESCRIPTION varchar2(240);
353
354 BEGIN
355
356
357 l_task_status := get_task_status_flag (p_TASK_ID);
358 -- Insertion of a new record is allowed only when the status of the task is IN PLANNING or ON HOLD or PLANNED
359 -- For PLANNED status task only description field can be entered when inserting a record for a task
360
361 IF l_task_status ='W' or l_task_status='X' or (l_task_status='A' and p_calling_routine='DELETE_ROW') then
362 fnd_message.set_name('CSF','CSF_ACCESS_INVALID_STATUS');-- require message
363 fnd_msg_pub.add;
364 raise fnd_api.g_exc_error;
365 end if;
366
367 IF l_task_status ='A' and p_CALLING_ROUTINE='INSERT_ROW' then
368 IF p_MONDAY_FIRST_START is not NULL
369 OR p_MONDAY_FIRST_END is not NULL
370 OR p_TUESDAY_FIRST_START is not NULL
371 OR p_TUESDAY_FIRST_END is not NULL
372 OR p_WEDNESDAY_FIRST_START is not NULL
373 OR p_WEDNESDAY_FIRST_END is not NULL
374 OR p_THURSDAY_FIRST_START is not NULL
375 OR p_THURSDAY_FIRST_END is not NULL
376 OR p_FRIDAY_FIRST_START is not NULL
377 OR p_FRIDAY_FIRST_END is not NULL
378 OR p_SATURDAY_FIRST_START is not NULL
379 OR p_SATURDAY_FIRST_END is not NULL
380 OR p_SUNDAY_FIRST_START is not NULL
381 OR p_SUNDAY_FIRST_END is not NULL
382 OR p_MONDAY_SECOND_START is not NULL
383 OR p_MONDAY_SECOND_END is not NULL
384 OR p_TUESDAY_SECOND_START is not NULL
385 OR p_TUESDAY_SECOND_END is not NULL
386 OR p_WEDNESDAY_SECOND_START is not NULL
387 OR p_WEDNESDAY_SECOND_END is not NULL
388 OR p_THURSDAY_SECOND_START is not NULL
389 OR p_THURSDAY_SECOND_END is not NULL
390 OR p_FRIDAY_SECOND_START is not NULL
391 OR p_FRIDAY_SECOND_END is not NULL
392 OR p_SATURDAY_SECOND_START is not NULL
393 OR p_SATURDAY_SECOND_END is not NULL
394 OR p_SUNDAY_SECOND_START is not NULL
395 OR p_SUNDAY_SECOND_END is not NULL
396 OR p_DESCRIPTION is null
397 OR nvl(p_ACCESS_HOUR_REQD,'N')='Y'
398 OR nvl(p_AFTER_HOURS_FLAG,'N') ='Y'
399 then
400 fnd_message.set_name('CSF','CSF_ACCESS_INVALID_INSERT');---require message
401 fnd_msg_pub.add;
402 raise fnd_api.g_exc_error;
403 end if;
404 end if;
405
406 -- in case of UPDATE_ROW function
407 -- for tasks with status PLANNED,no changes to time slots are allowed
408 -- description can be changed
409 -- access hour and after hour flags can be changed from Y to N only
410 IF p_CALLING_ROUTINE='UPDATE_ROW' then
411 /*open c_existing_values(p_TASK_ID);
412 fetch c_existing_values into l_ACCESSHOUR_REQUIRED,
413 l_AFTER_HOURS_FLAG,
414 l_MONDAY_FIRST_START,
415 l_MONDAY_FIRST_END,
416 l_MONDAY_SECOND_START,
417 l_MONDAY_SECOND_END,
418 l_TUESDAY_FIRST_START,
419 l_TUESDAY_FIRST_END,
420 l_TUESDAY_SECOND_START,
421 l_TUESDAY_SECOND_END,
422 l_WEDNESDAY_FIRST_START,
423 l_WEDNESDAY_FIRST_END,
424 l_WEDNESDAY_SECOND_START,
425 l_WEDNESDAY_SECOND_END,
426 l_THURSDAY_FIRST_START,
427 l_THURSDAY_FIRST_END,
428 l_THURSDAY_SECOND_START,
429 l_THURSDAY_SECOND_END,
430 l_FRIDAY_FIRST_START,
431 l_FRIDAY_FIRST_END,
432 l_FRIDAY_SECOND_START,
433 l_FRIDAY_SECOND_END,
434 l_SATURDAY_FIRST_START,
435 l_SATURDAY_FIRST_END,
436 l_SATURDAY_SECOND_START,
437 l_SATURDAY_SECOND_END,
438 l_SUNDAY_FIRST_START,
439 l_SUNDAY_FIRST_END,
440 l_SUNDAY_SECOND_START,
441 l_SUNDAY_SECOND_END,
442 l_DESCRIPTION;
443 close c_existing_values;*/
444 null;
445
446
447 end if;
448
449 IF p_CALLING_ROUTINE='INSERT_ROW' or p_CALLING_ROUTINE='UPDATE_ROW' then
450
451 -- both flags should not be set to Y together
452
453 if nvl(p_ACCESS_HOUR_REQD,'N') ='Y' and nvl(p_AFTER_HOURS_FLAG,'N') ='Y' then
454 fnd_message.set_name('CSF','CSF_ACCESS_BOTH_FLAGS_INV');-- require message
455 fnd_msg_pub.add;
456 raise fnd_api.g_exc_error;
457 end if;
458
459
460
461 --When start time is entered ,end time should also be entered
462
463 IF (p_MONDAY_FIRST_START is not null and p_MONDAY_FIRST_END is null)
464 OR (p_MONDAY_SECOND_START is not null and p_MONDAY_SECOND_END is null)
465 OR (p_TUESDAY_FIRST_START is not null and p_TUESDAY_FIRST_END is null)
466 OR (p_TUESDAY_SECOND_START is not null and p_TUESDAY_SECOND_END is null)
470 OR (p_THURSDAY_SECOND_START is not null and p_THURSDAY_SECOND_END is null)
467 OR (p_WEDNESDAY_FIRST_START is not null and p_WEDNESDAY_FIRST_END is null)
468 OR (p_WEDNESDAY_SECOND_START is not null and p_WEDNESDAY_SECOND_END is null)
469 OR (p_THURSDAY_FIRST_START is not null and p_THURSDAY_FIRST_END is null)
471 OR (p_FRIDAY_FIRST_START is not null and p_FRIDAY_FIRST_END is null)
472 OR (p_FRIDAY_SECOND_START is not null and p_FRIDAY_SECOND_END is null)
473 OR (p_SATURDAY_FIRST_START is not null and p_SATURDAY_FIRST_END is null)
474 OR (p_SATURDAY_SECOND_START is not null and p_SATURDAY_SECOND_END is null)
475 OR (p_SUNDAY_FIRST_START is not null and p_SUNDAY_FIRST_END is null)
476 OR (p_SUNDAY_SECOND_START is not null and p_SUNDAY_SECOND_END is null)
477
478 then
479 fnd_message.set_name('CSF','CSF_INV_START_END_NOT_NULL');
480 fnd_msg_pub.add;
481 raise fnd_api.g_exc_error;
482 end if;
483
484 -- when access_hours flag is set atleast one slot should be entered
485
486 if nvl(p_ACCESS_HOUR_REQD,'N') ='Y'
487 and p_MONDAY_FIRST_START is null
488 and p_TUESDAY_FIRST_START is null
489 and p_WEDNESDAY_FIRST_START is null
490 and p_THURSDAY_FIRST_START is null
491 and p_FRIDAY_FIRST_START is null
492 and p_SATURDAY_FIRST_START is null
493 and p_SUNDAY_FIRST_START is null
494 then
495 fnd_message.set_name('CSF','CSF_ENTER_ATLEAST_ONE_SLOT');-- require message here
496 fnd_msg_pub.add;
497 raise fnd_api.g_exc_error;
498 end if;
499
500 -- start to be enetered first and then the end
501
502 IF (p_MONDAY_FIRST_START is null and p_MONDAY_FIRST_END is NOT null)
503 OR (p_MONDAY_SECOND_START is null and p_MONDAY_SECOND_END is not null)
504 OR (p_TUESDAY_FIRST_START is null and p_TUESDAY_FIRST_END is not null)
505 OR (p_TUESDAY_SECOND_START is null and p_TUESDAY_SECOND_END is not null)
506 OR (p_WEDNESDAY_FIRST_START is null and p_WEDNESDAY_FIRST_END is not null)
507 OR (p_WEDNESDAY_SECOND_START is null and p_WEDNESDAY_SECOND_END is not null)
508 OR (p_THURSDAY_FIRST_START is null and p_THURSDAY_FIRST_END is not null)
509 OR (p_THURSDAY_SECOND_START is null and p_THURSDAY_SECOND_END is not null)
510 OR (p_FRIDAY_FIRST_START is null and p_FRIDAY_FIRST_END is not null)
511 OR (p_FRIDAY_SECOND_START is null and p_FRIDAY_SECOND_END is not null)
512 OR (p_SATURDAY_FIRST_START is null and p_SATURDAY_FIRST_END is not null)
513 OR (p_SATURDAY_SECOND_START is null and p_SATURDAY_SECOND_END is not null)
514 OR (p_SUNDAY_FIRST_START is null and p_SUNDAY_FIRST_END is not null)
515 OR (p_SUNDAY_SECOND_START is null and p_SUNDAY_SECOND_END is not null)
516
517 then
518 fnd_message.set_name('CSF','CSF_ACCESS_START_FIRST');-- need new messgae for this
519 fnd_msg_pub.add;
520 raise fnd_api.g_exc_error;
521 end if;
522 -- second slots for a day to be entered only after end times are entered
523
524 IF( (p_MONDAY_FIRST_START is null and p_MONDAY_FIRST_END is null)
525 and (p_MONDAY_SECOND_START is not null or p_MONDAY_SECOND_END is not null) )
526 OR( (p_TUESDAY_FIRST_START is null and p_TUESDAY_FIRST_END is null)
527 and (p_TUESDAY_SECOND_START is not null or p_TUESDAY_SECOND_END is not null))
528 OR( (p_WEDNESDAY_FIRST_START is null and p_WEDNESDAY_FIRST_END is null)
529 and (p_WEDNESDAY_SECOND_START is not null or p_WEDNESDAY_SECOND_END is not null))
530 OR( (p_THURSDAY_FIRST_START is null and p_THURSDAY_FIRST_END is null)
531 and (p_THURSDAY_SECOND_START is not null or p_THURSDAY_SECOND_END is not null))
532 OR( (p_FRIDAY_FIRST_START is null and p_FRIDAY_FIRST_END is null)
533 and (p_FRIDAY_SECOND_START is not null or p_FRIDAY_SECOND_END is not null))
534 OR( (p_SATURDAY_FIRST_START is null and p_SATURDAY_FIRST_END is null)
535 and (p_SATURDAY_SECOND_START is not null or p_SATURDAY_SECOND_END is not null))
536 OR( (p_SUNDAY_FIRST_START is null and p_SUNDAY_FIRST_END is null)
537 and (p_SUNDAY_SECOND_START is not null and p_SUNDAY_SECOND_END is not null))
538
539 then
540 fnd_message.set_name('CSF','CSF_SECOND_SLOT_FIRST_SLOT');-- need new messgae for this
541 fnd_msg_pub.add;
542 raise fnd_api.g_exc_error;
543 end if;
544
545 --- start time should be less than the end time for each slot
546
547 IF (p_MONDAY_FIRST_START > p_MONDAY_FIRST_END )
548 OR (p_MONDAY_SECOND_START >p_MONDAY_SECOND_END )
549 OR (p_TUESDAY_FIRST_START > p_TUESDAY_FIRST_END)
550 OR (p_TUESDAY_SECOND_START >p_TUESDAY_SECOND_END)
551 OR (p_WEDNESDAY_FIRST_START > p_WEDNESDAY_FIRST_END )
552 OR (p_WEDNESDAY_SECOND_START >p_WEDNESDAY_SECOND_END )
553 OR (p_THURSDAY_FIRST_START >p_THURSDAY_FIRST_END)
554 OR (p_THURSDAY_SECOND_START >p_THURSDAY_SECOND_END)
555 OR (p_FRIDAY_FIRST_START >p_FRIDAY_FIRST_END)
556 OR (p_FRIDAY_SECOND_START > p_FRIDAY_SECOND_END)
557 OR (p_SATURDAY_FIRST_START > p_SATURDAY_FIRST_END )
558 OR (p_SATURDAY_SECOND_START > p_SATURDAY_SECOND_END)
559 OR (p_SUNDAY_FIRST_START >p_SUNDAY_FIRST_END )
560 OR (p_SUNDAY_SECOND_START >p_SUNDAY_SECOND_END)
561
562 then
563 fnd_message.set_name('CSF','CSF_INV_START_END');
564 fnd_msg_pub.add;
565 raise fnd_api.g_exc_error;
566 end if;
567
568 -- start time of the second slot should be greater than the end time of the first slot for a day
569 IF (p_MONDAY_FIRST_END > p_MONDAY_SECOND_START )
570 OR (p_TUESDAY_FIRST_END > p_TUESDAY_SECOND_START)
571 OR (p_WEDNESDAY_FIRST_END >p_WEDNESDAY_SECOND_START )
572 OR (p_THURSDAY_FIRST_END >p_THURSDAY_SECOND_START)
573 OR (p_FRIDAY_FIRST_END > p_FRIDAY_SECOND_START)
574 OR (p_SATURDAY_FIRST_END > p_SATURDAY_SECOND_START)
575 OR (p_SUNDAY_FIRST_END >p_SUNDAY_SECOND_START)
576
577 then
578 fnd_message.set_name('CSF','CSF_INV_SLOTS');
579 fnd_msg_pub.add;
580 raise fnd_api.g_exc_error;
581 end if;
582
583 /*-- both flags should not be set to Y together
584
585 if nvl(p_ACCESS_HOUR_REQD,'N') ='Y' and nvl(p_AFTER_HOURS_FLAG,'N') ='Y' then
586 fnd_message.set_name('CSF','CSF_INV_SLOTS');-- require message
587 fnd_msg_pub.add;
588 raise fnd_api.g_exc_error;
592
589 end if;
590
591 -- when access_hours flag is set atleast one slot should be entered
593 if nvl(p_ACCESS_HOUR_REQD,'N') ='Y'
594 and p_MONDAY_FIRST_START is null
595 and p_TUESDAY_FIRST_START is null
596 and p_WEDNESDAY_FIRST_START is null
597 and p_THURSDAY_FIRST_START is null
598 and p_FRIDAY_FIRST_START is null
599 and p_SATURDAY_FIRST_START is null
600 and p_SUNDAY_FIRST_START is null
601 then
602 fnd_message.set_name('CSF','CSF_INV_START_END_NOT_NULL');-- require message here
603 fnd_msg_pub.add;
604 raise fnd_api.g_exc_error;
605 end if;*/
606
607 end if;
608
609 EXCEPTION
610 WHEN fnd_api.g_exc_error then
611 x_return_status := fnd_api.g_ret_sts_error;
612 --fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
613
614 WHEN fnd_api.g_exc_unexpected_error then
615 x_return_status :=fnd_api.g_ret_sts_unexp_error;
616 --fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
617
618 WHEN others then
619 x_return_status :=fnd_api.g_ret_sts_unexp_error;
620 --fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
621
622
623 END CHECK_PARAMETERS;
624
625 PROCEDURE CREATE_ACCESS_HOURS(
626 x_ACCESS_HOUR_ID OUT NOCOPY NUMBER,
627 p_API_VERSION NUMBER,
628 p_init_msg_list varchar2 ,
629 p_TASK_ID NUMBER,
630 p_ACCESS_HOUR_REQD VARCHAR2,
631 p_AFTER_HOURS_FLAG VARCHAR2 ,
632 p_MONDAY_FIRST_START DATE ,
633 p_MONDAY_FIRST_END DATE ,
634 p_MONDAY_SECOND_START DATE ,
635 p_MONDAY_SECOND_END DATE ,
636 p_TUESDAY_FIRST_START DATE ,
637 p_TUESDAY_FIRST_END DATE ,
638 p_TUESDAY_SECOND_START DATE,
639 p_TUESDAY_SECOND_END DATE ,
640 p_WEDNESDAY_FIRST_START DATE ,
641 p_WEDNESDAY_FIRST_END DATE ,
642 p_WEDNESDAY_SECOND_START DATE ,
643 p_WEDNESDAY_SECOND_END DATE ,
644 p_THURSDAY_FIRST_START DATE ,
645 p_THURSDAY_FIRST_END DATE ,
646 p_THURSDAY_SECOND_START DATE ,
647 p_THURSDAY_SECOND_END DATE ,
648 p_FRIDAY_FIRST_START DATE,
649 p_FRIDAY_FIRST_END DATE ,
650 p_FRIDAY_SECOND_START DATE ,
651 p_FRIDAY_SECOND_END DATE,
652 p_SATURDAY_FIRST_START DATE ,
653 p_SATURDAY_FIRST_END DATE ,
654 p_SATURDAY_SECOND_START DATE ,
655 p_SATURDAY_SECOND_END DATE ,
656 p_SUNDAY_FIRST_START DATE ,
657 p_SUNDAY_FIRST_END DATE,
658 p_SUNDAY_SECOND_START DATE ,
659 p_SUNDAY_SECOND_END DATE,
660 p_DESCRIPTION VARCHAR2,
661 px_object_version_number in out nocopy number,
662 p_CREATED_BY NUMBER ,
663 p_CREATION_DATE DATE ,
664 p_LAST_UPDATED_BY NUMBER ,
665 p_LAST_UPDATE_DATE DATE,
666 p_LAST_UPDATE_LOGIN NUMBER ,
667 -- p_commit in varchar2 default fnd_api.g_false,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_msg_data OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER
671
672
673 )
674
675 IS
676
677 l_api_version CONSTANT Number := 1.0 ;
678 l_api_name_full CONSTANT varchar2(50) := 'CSF_ACCESS_HOURS_PUB.CREATE_ACCESS_HOURS';
679 l_return_status varchar2(100);
680 l_msg_count NUMBER;
681 l_msg_data varchar2(1000);
682 l_sta varchar2(1);
683 l_cnt number;
684 l_msg varchar2(2000);
685 l_temp varchar2(1);
686
687 CURSOR C_EXISTS(p_task_id number) is select 'Y' from csf_access_hours_vl where task_id=p_task_id;
688
689 BEGIN
690 SAVEPOINT create_access_hours_pub;
691 x_return_status := fnd_api.g_ret_sts_success;
692
693 IF NOT fnd_api.compatible_api_call (
694 l_api_version,
695 p_api_version,
696 l_api_name_full,
697 g_pkg_name
698 )
699 then
700 raise fnd_Api.g_exc_unexpected_error;
701 end if;
702
703 IF fnd_api.to_boolean( nvl(p_init_msg_list,fnd_Api.g_false)) then
704 fnd_msg_pub.initialize;
705 end if;
706
707 If p_task_id is NULL then
708 fnd_message.set_name ('CSF','CSF_ACCESS_INVALID_PARAMETER');--message required
709 fnd_msg_pub.add;
710 raise fnd_api.g_exc_error;
711 end if;
712
713 open c_exists(p_task_id);
714 fetch c_exists into l_temp;
715 if c_exists%notfound then
716 close c_exists;
717 else
718 close c_exists;
719 fnd_message.set_name('CSF','CSF_ACCESS_ROW_EXISTS');
720 fnd_msg_pub.add;
721 raise fnd_api.g_exc_error;
722 end if;
723
724 check_parameters
725 (
726 p_CALLING_ROUTINE => 'INSERT_ROW',
727 p_TASK_ID => p_TASK_ID,
728 p_ACCESS_HOUR_REQD=>nvl(p_ACCESS_HOUR_REQD,'N'),
729 p_AFTER_HOURS_FLAG =>nvl(p_AFTER_HOURS_FLAG,'N'),
730 p_MONDAY_FIRST_START =>p_MONDAY_FIRST_START,
731 p_MONDAY_FIRST_END =>p_MONDAY_FIRST_END,
732 p_TUESDAY_FIRST_START =>p_TUESDAY_FIRST_START,
733 p_TUESDAY_FIRST_END =>p_TUESDAY_FIRST_END,
734 p_WEDNESDAY_FIRST_START =>p_WEDNESDAY_FIRST_START,
735 p_WEDNESDAY_FIRST_END =>p_WEDNESDAY_FIRST_END ,
736 p_THURSDAY_FIRST_START =>p_THURSDAY_FIRST_START ,
737 p_THURSDAY_FIRST_END =>p_THURSDAY_FIRST_END,
738 p_FRIDAY_FIRST_START =>p_FRIDAY_FIRST_START,
739 p_FRIDAY_FIRST_END =>p_FRIDAY_FIRST_END ,
740 p_SATURDAY_FIRST_START =>p_SATURDAY_FIRST_START,
741 p_SATURDAY_FIRST_END => p_SATURDAY_FIRST_END ,
742 p_SUNDAY_FIRST_START =>p_SUNDAY_FIRST_START ,
743 p_SUNDAY_FIRST_END =>p_SUNDAY_FIRST_END ,
744 p_MONDAY_SECOND_START => p_MONDAY_SECOND_START,
748 p_WEDNESDAY_SECOND_START =>p_WEDNESDAY_SECOND_START,
745 p_MONDAY_SECOND_END =>p_MONDAY_SECOND_END ,
746 p_TUESDAY_SECOND_START =>p_TUESDAY_SECOND_START ,
747 p_TUESDAY_SECOND_END =>p_TUESDAY_SECOND_END,
749 p_WEDNESDAY_SECOND_END => p_WEDNESDAY_SECOND_END ,
750 p_THURSDAY_SECOND_START =>p_THURSDAY_SECOND_START ,
751 p_THURSDAY_SECOND_END => p_THURSDAY_SECOND_END ,
752 p_FRIDAY_SECOND_START => p_FRIDAY_SECOND_START,
753 p_FRIDAY_SECOND_END => p_FRIDAY_SECOND_END ,
754 p_SATURDAY_SECOND_START =>p_SATURDAY_SECOND_START ,
755 p_SATURDAY_SECOND_END =>p_SATURDAY_SECOND_END ,
756 p_SUNDAY_SECOND_START =>p_SUNDAY_SECOND_START ,
757 p_SUNDAY_SECOND_END =>p_SUNDAY_SECOND_END ,
758 p_DESCRIPTION => p_DESCRIPTION,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data
762
763 );
764
765 If l_return_status = fnd_api.g_ret_sts_error then
766 raise fnd_api.g_exc_error;
767 end if;
768
769 If l_return_status = fnd_api.g_ret_sts_unexp_error then
770 raise fnd_Api.g_exc_unexpected_error;
771 end if;
772
773 CSF_ACCESS_HOURS_PVT.CREATE_ACCESS_HOURS
774 (
775 p_API_VERSION => 1.0,
776 p_INIT_MSG_LIST => 'F',
777 x_ACCESS_HOUR_ID =>x_ACCESS_HOUR_ID,
778 p_TASK_ID=>p_TASK_ID,
779 p_ACCESS_HOUR_REQD=>nvl(p_ACCESS_HOUR_REQD,'N'),
780 p_AFTER_HOURS_FLAG =>nvl(p_AFTER_HOURS_FLAG,'N'),
781 p_MONDAY_FIRST_START =>p_MONDAY_FIRST_START,
782 p_MONDAY_FIRST_END =>p_MONDAY_FIRST_END,
783 p_TUESDAY_FIRST_START =>p_TUESDAY_FIRST_START,
784 p_TUESDAY_FIRST_END =>p_TUESDAY_FIRST_END,
785 p_WEDNESDAY_FIRST_START =>p_WEDNESDAY_FIRST_START,
786 p_WEDNESDAY_FIRST_END =>p_WEDNESDAY_FIRST_END ,
787 p_THURSDAY_FIRST_START =>p_THURSDAY_FIRST_START ,
788 p_THURSDAY_FIRST_END =>p_THURSDAY_FIRST_END,
789 p_FRIDAY_FIRST_START =>p_FRIDAY_FIRST_START,
790 p_FRIDAY_FIRST_END =>p_FRIDAY_FIRST_END ,
791 p_SATURDAY_FIRST_START =>p_SATURDAY_FIRST_START,
792 p_SATURDAY_FIRST_END => p_SATURDAY_FIRST_END ,
793 p_SUNDAY_FIRST_START =>p_SUNDAY_FIRST_START ,
794 p_SUNDAY_FIRST_END =>p_SUNDAY_FIRST_END ,
795 p_MONDAY_SECOND_START => p_MONDAY_SECOND_START,
796 p_MONDAY_SECOND_END =>p_MONDAY_SECOND_END ,
797 p_TUESDAY_SECOND_START =>p_TUESDAY_SECOND_START ,
798 p_TUESDAY_SECOND_END =>p_TUESDAY_SECOND_END,
799 p_WEDNESDAY_SECOND_START =>p_WEDNESDAY_SECOND_START,
800 p_WEDNESDAY_SECOND_END => p_WEDNESDAY_SECOND_END ,
801 p_THURSDAY_SECOND_START =>p_THURSDAY_SECOND_START ,
802 p_THURSDAY_SECOND_END => p_THURSDAY_SECOND_END ,
803 p_FRIDAY_SECOND_START => p_FRIDAY_SECOND_START,
804 p_FRIDAY_SECOND_END => p_FRIDAY_SECOND_END ,
805 p_SATURDAY_SECOND_START =>p_SATURDAY_SECOND_START ,
806 p_SATURDAY_SECOND_END =>p_SATURDAY_SECOND_END ,
807 p_SUNDAY_SECOND_START =>p_SUNDAY_SECOND_START ,
808 p_SUNDAY_SECOND_END =>p_SUNDAY_SECOND_END ,
809 p_DESCRIPTION => p_DESCRIPTION,
810 px_object_version_number => px_object_version_number,
811 p_commit =>fnd_api.g_true,--p_commit
812 x_return_status => l_return_status,
813 x_msg_count => l_msg_count,
814 x_msg_data => l_msg_data
815
816 );
817
818
819 If l_return_status = fnd_api.g_ret_sts_error then
820 raise fnd_api.g_exc_error;
821 end if;
822
823 If l_return_status = fnd_api.g_ret_sts_unexp_error then
824 raise fnd_Api.g_exc_unexpected_error;
825 end if;
826
827 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
828
829 EXCEPTION
830 WHEN fnd_api.g_exc_error then
831 ROLLBACK TO create_access_hours_pub;
832 x_return_status := fnd_api.g_ret_sts_error;
833 /*x_msg_count := l_msg_count;
834 x_msg_data := l_msg_data;*/
835 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
836
837 WHEN fnd_api.g_exc_unexpected_error then
838 ROLLBACK TO create_access_hours_pub;
839 x_return_status :=fnd_api.g_ret_sts_unexp_error ;
840 /*x_msg_count := l_msg_count;
841 x_msg_data := l_msg_data;*/
842 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
843
844 WHEN OTHERS then
845 ROLLBACK TO create_task_assignment_pub;
846 x_return_status := fnd_api.g_ret_sts_unexp_error;
847 /*x_msg_count := l_msg_count;
848 x_msg_data := l_msg_data;*/
849 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
850
851
852 End CREATE_ACCESS_HOURS;
853
854 PROCEDURE UPDATE_ACCESS_HOURS(
855 p_ACCESS_HOUR_ID IN NUMBER,
856 p_TASK_ID NUMBER,
857 p_API_VERSION NUMBER,
858 p_init_msg_list varchar2 ,
859 p_commit varchar2 ,
860 p_ACCESS_HOUR_REQD VARCHAR2 ,
861 p_AFTER_HOURS_FLAG VARCHAR2 ,
862 p_MONDAY_FIRST_START DATE, --default TO_DATE(NULL),
863 p_MONDAY_FIRST_END DATE, --default TO_DATE(NULL),
864 p_MONDAY_SECOND_START DATE, --default TO_DATE(NULL),
865 p_MONDAY_SECOND_END DATE, --default TO_DATE(NULL),
866 p_TUESDAY_FIRST_START DATE, --default TO_DATE(NULL),
867 p_TUESDAY_FIRST_END DATE, --default TO_DATE(NULL) ,
868 p_TUESDAY_SECOND_START DATE, --default TO_DATE(NULL),
869 p_TUESDAY_SECOND_END DATE, --default TO_DATE(NULL) ,
870 p_WEDNESDAY_FIRST_START DATE, --default TO_DATE(NULL),
871 p_WEDNESDAY_FIRST_END DATE, -- default TO_DATE(NULL),
872 p_WEDNESDAY_SECOND_START DATE, --default TO_DATE(NULL),
873 p_WEDNESDAY_SECOND_END DATE,-- default TO_DATE(NULL),
874 p_THURSDAY_FIRST_START DATE, --default TO_DATE(NULL),
875 p_THURSDAY_FIRST_END DATE, --default TO_DATE(NULL),
879 p_FRIDAY_FIRST_END DATE,-- default TO_DATE(NULL),
876 p_THURSDAY_SECOND_START DATE, --default TO_DATE(NULL),
877 p_THURSDAY_SECOND_END DATE, --default TO_DATE(NULL),
878 p_FRIDAY_FIRST_START DATE,-- default TO_DATE(NULL),
880 p_FRIDAY_SECOND_START DATE, --default TO_DATE(NULL),
881 p_FRIDAY_SECOND_END DATE,-- default TO_DATE(NULL),
882 p_SATURDAY_FIRST_START DATE, --default TO_DATE(NULL),
883 p_SATURDAY_FIRST_END DATE, --default TO_DATE(NULL),
884 p_SATURDAY_SECOND_START DATE,-- default TO_DATE(NULL),
885 p_SATURDAY_SECOND_END DATE, --default TO_DATE(NULL),
886 p_SUNDAY_FIRST_START DATE, --default TO_DATE(NULL),
887 p_SUNDAY_FIRST_END DATE, -- default TO_DATE(NULL),
888 p_SUNDAY_SECOND_START DATE, --default TO_DATE(NULL),
889 p_SUNDAY_SECOND_END DATE, --default TO_DATE(NULL),
890 p_DESCRIPTION VARCHAR2 DEFAULT null,
891 px_object_version_number in out nocopy number,
892 p_CREATED_BY NUMBER default null,
893 p_CREATION_DATE DATE default null,
894 p_LAST_UPDATED_BY NUMBER default null,
895 p_LAST_UPDATE_DATE DATE default null,
896 p_LAST_UPDATE_LOGIN NUMBER default null,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 x_msg_count OUT NOCOPY NUMBER
900
901 )
902
903 IS
904 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PUB.UPDATE_ACCESS_HOURS';
905 l_api_version CONSTANT Number := 1.0 ;
906 l_return_status varchar2(100);
907 l_msg_count NUMBER;
908 l_msg_data varchar2(1000);
909 l_sta varchar2(1);
910 l_cnt number;
911 l_msg varchar2(2000);
912
913
914
915
916 cursor c_existing_values(p_task_id number) is
917 select
918 ACCESSHOUR_REQUIRED,
919 AFTER_HOURS_FLAG,
920 MONDAY_FIRST_START,
921 MONDAY_FIRST_END,
922 MONDAY_SECOND_START,
923 MONDAY_SECOND_END,
924 TUESDAY_FIRST_START,
925 TUESDAY_FIRST_END,
926 TUESDAY_SECOND_START,
927 TUESDAY_SECOND_END,
928 WEDNESDAY_FIRST_START,
929 WEDNESDAY_FIRST_END,
930 WEDNESDAY_SECOND_START,
931 WEDNESDAY_SECOND_END,
932 THURSDAY_FIRST_START,
933 THURSDAY_FIRST_END,
934 THURSDAY_SECOND_START,
935 THURSDAY_SECOND_END,
936 FRIDAY_FIRST_START,
937 FRIDAY_FIRST_END,
938 FRIDAY_SECOND_START,
939 FRIDAY_SECOND_END,
940 SATURDAY_FIRST_START,
941 SATURDAY_FIRST_END,
942 SATURDAY_SECOND_START,
943 SATURDAY_SECOND_END,
944 SUNDAY_FIRST_START,
945 SUNDAY_FIRST_END,
946 SUNDAY_SECOND_START,
947 SUNDAY_SECOND_END,
948 DESCRIPTION,
949 object_version_number
950 from csf_access_hours_vl
951 where task_id=p_task_id;
952
953 l_ACCESS_HOUR_REQD VARCHAR2(1);
954
955 l_after_hours_flag varchar2(1);
956 l_MONDAY_FIRST_START date;
957 l_MONDAY_FIRST_END date;
958 l_MONDAY_SECOND_START date;
959 l_MONDAY_SECOND_END date;
960 l_TUESDAY_FIRST_START date;
961 l_TUESDAY_FIRST_END date;
962 l_TUESDAY_SECOND_START date;
963 l_TUESDAY_SECOND_END date;
964 l_WEDNESDAY_FIRST_START date;
965 l_WEDNESDAY_FIRST_END date;
966 l_WEDNESDAY_SECOND_START date;
967 l_WEDNESDAY_SECOND_END date;
968 l_THURSDAY_FIRST_START date;
969 l_THURSDAY_FIRST_END date;
970 l_THURSDAY_SECOND_START date;
971 l_THURSDAY_SECOND_END date;
972 l_FRIDAY_FIRST_START date;
973 l_FRIDAY_FIRST_END date;
974 l_FRIDAY_SECOND_START date;
975 l_FRIDAY_SECOND_END date;
976 l_SATURDAY_FIRST_START date;
977 l_SATURDAY_FIRST_END date;
978 l_SATURDAY_SECOND_START date;
979 l_SATURDAY_SECOND_END date;
980 l_SUNDAY_FIRST_START date;
981 l_SUNDAY_FIRST_END date;
982 l_SUNDAY_SECOND_START date;
983 l_SUNDAY_SECOND_END date;
984 l_DESCRIPTION varchar2(240);
985 --l_object_version_number number;
986
987 l_task_status varchar2(1);
988 l_temp varchar2(1);
989
990 CURSOR C_EXISTS(p_task_id number) is select 'Y' from csf_access_hours_vl where task_id=p_task_id;
991
992 BEGIN
993 SAVEPOINT update_access_hours_pub;
994 x_return_status := fnd_api.g_ret_sts_success;
995
996 IF NOT fnd_api.compatible_api_call (
997 l_api_version,
998 p_api_version,
999 l_api_name_full,
1000 g_pkg_name
1001 )
1002 then
1003 raise fnd_Api.g_exc_unexpected_error;
1004 end if;
1005
1006 IF fnd_api.to_boolean(nvl(p_init_msg_list,fnd_Api.g_false)) then
1007 fnd_msg_pub.initialize;
1008 end if;
1009
1010
1011 If p_task_id is NULL or p_ACCESS_HOUR_ID is NULL then
1012 fnd_message.set_name ('CSF','CSF_ACCESS_INVALID_PARAMETER');--message required
1013 fnd_msg_pub.add;
1014 raise fnd_api.g_exc_error;
1015 end if;
1016
1017 open c_exists(p_task_id);
1018 fetch c_exists into l_temp;
1019 if c_exists%notfound then
1020 close c_exists;
1021 fnd_message.set_name('CSF','CSF_ACCESS_NO_ROW_EXISTS');
1022 fnd_msg_pub.add;
1023 raise fnd_api.g_exc_error;
1024 else
1025 close c_exists;
1026 end if;
1027
1028 open c_existing_values(p_TASK_ID);
1029 fetch c_existing_values into l_ACCESS_HOUR_REQD,
1030 l_AFTER_HOURS_FLAG,
1031 l_MONDAY_FIRST_START,
1032 l_MONDAY_FIRST_END,
1033 l_MONDAY_SECOND_START,
1034 l_MONDAY_SECOND_END,
1035 l_TUESDAY_FIRST_START,
1036 l_TUESDAY_FIRST_END,
1040 l_WEDNESDAY_FIRST_END,
1037 l_TUESDAY_SECOND_START,
1038 l_TUESDAY_SECOND_END,
1039 l_WEDNESDAY_FIRST_START,
1041 l_WEDNESDAY_SECOND_START,
1042 l_WEDNESDAY_SECOND_END,
1043 l_THURSDAY_FIRST_START,
1044 l_THURSDAY_FIRST_END,
1045 l_THURSDAY_SECOND_START,
1046 l_THURSDAY_SECOND_END,
1047 l_FRIDAY_FIRST_START,
1048 l_FRIDAY_FIRST_END,
1049 l_FRIDAY_SECOND_START,
1050 l_FRIDAY_SECOND_END,
1051 l_SATURDAY_FIRST_START,
1052 l_SATURDAY_FIRST_END,
1053 l_SATURDAY_SECOND_START,
1054 l_SATURDAY_SECOND_END,
1055 l_SUNDAY_FIRST_START,
1056 l_SUNDAY_FIRST_END,
1057 l_SUNDAY_SECOND_START,
1058 l_SUNDAY_SECOND_END,
1059 l_DESCRIPTION,
1060 px_object_version_number;
1061 close c_existing_values;
1062 l_task_status:=get_task_status_flag(p_task_id);
1063
1064 IF l_task_status='A' then
1065
1066 IF ((l_ACCESS_HOUR_REQD ='N' and p_ACCESS_HOUR_REQD ='Y')
1067 OR (l_AFTER_HOURS_FLAG ='N' and p_AFTER_HOURS_FLAG='Y'))
1068 then
1069 fnd_message.set_name ('CSF','CSF_INV_FLAG_CHANGE');--message required
1070 fnd_msg_pub.add;
1071 raise fnd_api.g_exc_error;
1072 end if;
1073
1074 IF p_MONDAY_FIRST_START is not null
1075 OR p_MONDAY_FIRST_END is not null
1076 OR p_TUESDAY_FIRST_START is not null
1077 OR p_TUESDAY_FIRST_END is not null
1078 OR p_WEDNESDAY_FIRST_START is not null
1079 OR p_WEDNESDAY_FIRST_END is not null
1080 OR p_THURSDAY_FIRST_START is not null
1081 OR p_THURSDAY_FIRST_END is not null
1082 OR p_FRIDAY_FIRST_START is not null
1083 OR p_FRIDAY_FIRST_END is not null
1084 OR p_SATURDAY_FIRST_START is not null
1085 OR p_SATURDAY_FIRST_END is not null
1086 OR p_SUNDAY_FIRST_START is not null
1087 OR p_SUNDAY_FIRST_END is not null
1088 OR p_MONDAY_SECOND_START is not null
1089 OR p_MONDAY_SECOND_END is not null
1090 OR p_TUESDAY_SECOND_START is not null
1091 OR p_TUESDAY_SECOND_END is not null
1092 OR p_WEDNESDAY_SECOND_START is not null
1093 OR p_WEDNESDAY_SECOND_END is not null
1094 OR p_THURSDAY_SECOND_START is not null
1095 OR p_THURSDAY_SECOND_END is not null
1096 OR p_FRIDAY_SECOND_START is not null
1097 OR p_FRIDAY_SECOND_END is not null
1098 OR p_SATURDAY_SECOND_START is not null
1099 OR p_SATURDAY_SECOND_END is not null
1100 OR p_SUNDAY_SECOND_START is not null
1101 OR p_SUNDAY_SECOND_END is not null then
1102
1103 fnd_message.set_name ('CSF','CSF_INV_DATE_CHANGE');--message required
1104 fnd_msg_pub.add;
1105 raise fnd_api.g_exc_error;
1106 end if;
1107 end if;
1108
1109 --- now copy all existing values and the new values to the local variable b4 calling the update_row procedure of csf_access_hours_pvt
1110 IF p_ACCESS_HOUR_REQD is NOT NULL then
1111 l_ACCESS_HOUR_REQD :=p_ACCESS_HOUR_REQD;
1112 END IF;
1113
1114 IF p_AFTER_HOURS_FLAG is NOT NULL then
1115 l_AFTER_HOURS_FLAG := p_AFTER_HOURS_FLAG;
1116 END IF;
1117
1118 --IF p_MONDAY_FIRST_START is NOT NULL and
1119 IF l_task_status <> 'A' then
1120 l_MONDAY_FIRST_START := p_MONDAY_FIRST_START;
1121 END IF;
1122
1123 --IF p_MONDAY_FIRST_END is NOT NULL and
1124 IF l_task_status <> 'A' then
1125 l_MONDAY_FIRST_END := p_MONDAY_FIRST_END;
1126 END IF;
1127
1128 --IF p_MONDAY_SECOND_START is NOT NULL and
1129 IF l_task_status <> 'A' then
1130 l_MONDAY_SECOND_START := p_MONDAY_SECOND_START;
1131 END IF;
1132
1133 --IF p_MONDAY_SECOND_END is NOT NULL and
1134 IF l_task_status<> 'A' then
1135 l_MONDAY_SECOND_END := p_MONDAY_SECOND_END;
1136 END IF;
1137
1138 --IF p_TUESDAY_FIRST_START is NOT NULL and
1139 IF l_task_status<>'A' then
1140 l_TUESDAY_FIRST_START := p_TUESDAY_FIRST_START;
1141 END IF;
1142
1143 --IF p_TUESDAY_FIRST_END is NOT NULL and
1144 IF l_task_status<>'A' then
1145 l_TUESDAY_FIRST_END := p_TUESDAY_FIRST_END;
1146 END IF;
1147
1148 --IF p_TUESDAY_SECOND_START is NOT NULL and
1149 IF l_task_status<>'A' then
1150 l_TUESDAY_SECOND_START := p_TUESDAY_SECOND_START;
1151 END IF;
1152
1153 --IF p_TUESDAY_SECOND_END is NOT NULL and
1154 IF l_task_status<>'A' then
1155 l_TUESDAY_SECOND_END := p_TUESDAY_SECOND_END;
1156 END IF;
1157
1158 --IF p_WEDNESDAY_FIRST_START is NOT NULL and
1159 IF l_task_status<>'A' then
1160 l_WEDNESDAY_FIRST_START := p_WEDNESDAY_FIRST_START;
1161 END IF;
1162
1163 --IF p_WEDNESDAY_FIRST_END is NOT NULL and
1164 IF l_task_status<>'A' then
1165 l_WEDNESDAY_FIRST_END := p_WEDNESDAY_FIRST_END;
1166 END IF;
1167
1168 --IF p_WEDNESDAY_SECOND_START is NOT NULL and
1169 IF l_task_status<>'A' then
1170 l_WEDNESDAY_SECOND_START := p_WEDNESDAY_SECOND_START;
1171 END IF;
1172
1173 --IF p_WEDNESDAY_SECOND_END is NOT NULL and
1174 IF l_task_status<>'A' then
1175 l_WEDNESDAY_SECOND_END := p_WEDNESDAY_SECOND_END;
1176 END IF;
1177
1178 --IF p_THURSDAY_FIRST_START is NOT NULL and
1179 IF l_task_status<>'A' then
1180 l_THURSDAY_FIRST_START := p_THURSDAY_FIRST_START;
1181 END IF;
1182
1183 --IF p_THURSDAY_FIRST_END is NOT NULL and
1184 IF l_task_status<>'A' then
1185 l_THURSDAY_FIRST_END := p_THURSDAY_FIRST_END;
1186 END IF;
1187
1188 --IF p_THURSDAY_SECOND_START is NOT NULL and
1189 IF l_task_status<>'A' then
1190 l_THURSDAY_SECOND_START := p_THURSDAY_SECOND_START;
1191 END IF;
1192
1193 --IF p_THURSDAY_SECOND_END is NOT NULL and
1194 IF l_task_status<>'A' then
1195 l_THURSDAY_SECOND_END := p_THURSDAY_SECOND_END;
1196 END IF;
1197
1198 --IF p_FRIDAY_FIRST_START is NOT NULL and
1199 IF l_task_status<>'A' then
1200 l_FRIDAY_FIRST_START := p_FRIDAY_FIRST_START;
1201 END IF;
1202
1203 --IF p_FRIDAY_FIRST_END is NOT NULL and
1204 IF l_task_status<>'A' then
1205 l_FRIDAY_FIRST_END := p_FRIDAY_FIRST_END;
1206 END IF;
1207
1208 --IF p_FRIDAY_SECOND_START is NOT NULL and
1212
1209 IF l_task_status<>'A' then
1210 l_FRIDAY_SECOND_START := p_FRIDAY_SECOND_START;
1211 END IF;
1213 --IF p_FRIDAY_SECOND_END is NOT NULL and
1214 IF l_task_status<>'A' then
1215 l_FRIDAY_SECOND_END := p_FRIDAY_SECOND_END;
1216 END IF;
1217
1218 --IF p_SATURDAY_FIRST_START is NOT NULL and
1219 IF l_task_status<>'A' then
1220 l_SATURDAY_FIRST_START := p_SATURDAY_FIRST_START;
1221 END IF;
1222
1223 --IF p_SATURDAY_FIRST_END is NOT NULL and
1224 IF l_task_status<>'A' then
1225 l_SATURDAY_FIRST_END := p_SATURDAY_FIRST_END;
1226 END IF;
1227
1228 --IF p_SATURDAY_SECOND_START is NOT NULL and
1229 IF l_task_status<>'A' then
1230 l_SATURDAY_SECOND_START := p_SATURDAY_SECOND_START;
1231 END IF;
1232
1233 --IF p_SATURDAY_SECOND_END is NOT NULL and
1234 IF l_task_status<>'A' then
1235 l_SATURDAY_SECOND_END := p_SATURDAY_SECOND_END;
1236 END IF;
1237
1238 --IF p_SUNDAY_FIRST_START is NOT NULL and
1239 IF l_task_status<>'A' then
1240 l_SUNDAY_FIRST_START := p_SUNDAY_FIRST_START;
1241 END IF;
1242
1243 --IF p_SUNDAY_FIRST_END is NOT NULL and
1244 IF l_task_status<>'A' then
1245 l_SUNDAY_FIRST_END := p_SUNDAY_FIRST_END;
1246 END IF;
1247
1248 --IF p_SUNDAY_SECOND_START is NOT NULL and
1249 IF l_task_status<>'A' then
1250 l_SUNDAY_SECOND_START := p_SUNDAY_SECOND_START;
1251 END IF;
1252
1253 --IF p_SUNDAY_SECOND_END is NOT NULL and
1254 IF l_task_status<>'A' then
1255 l_SUNDAY_SECOND_END := p_SUNDAY_SECOND_END;
1256 END IF;
1257
1258 IF p_description is NOT NULL then
1259 l_description := p_description;
1260 END IF;
1261
1262 check_parameters
1263 (
1264 p_CALLING_ROUTINE => 'UPDATE_ROW',
1265 p_TASK_ID => p_TASK_ID,
1266 p_ACCESS_HOUR_REQD=>l_ACCESS_HOUR_REQD,
1267 p_AFTER_HOURS_FLAG =>l_AFTER_HOURS_FLAG,
1268 p_MONDAY_FIRST_START =>l_MONDAY_FIRST_START,
1269 p_MONDAY_FIRST_END =>l_MONDAY_FIRST_END,
1270 p_TUESDAY_FIRST_START =>l_TUESDAY_FIRST_START,
1271 p_TUESDAY_FIRST_END =>l_TUESDAY_FIRST_END,
1272 p_WEDNESDAY_FIRST_START =>l_WEDNESDAY_FIRST_START,
1273 p_WEDNESDAY_FIRST_END =>l_WEDNESDAY_FIRST_END ,
1274 p_THURSDAY_FIRST_START =>l_THURSDAY_FIRST_START ,
1275 p_THURSDAY_FIRST_END =>l_THURSDAY_FIRST_END,
1276 p_FRIDAY_FIRST_START =>l_FRIDAY_FIRST_START,
1277 p_FRIDAY_FIRST_END =>l_FRIDAY_FIRST_END ,
1278 p_SATURDAY_FIRST_START =>l_SATURDAY_FIRST_START,
1279 p_SATURDAY_FIRST_END => l_SATURDAY_FIRST_END ,
1280 p_SUNDAY_FIRST_START =>l_SUNDAY_FIRST_START ,
1281 p_SUNDAY_FIRST_END =>l_SUNDAY_FIRST_END ,
1282 p_MONDAY_SECOND_START => l_MONDAY_SECOND_START,
1283 p_MONDAY_SECOND_END =>l_MONDAY_SECOND_END ,
1284 p_TUESDAY_SECOND_START =>l_TUESDAY_SECOND_START ,
1285 p_TUESDAY_SECOND_END =>l_TUESDAY_SECOND_END,
1286 p_WEDNESDAY_SECOND_START =>l_WEDNESDAY_SECOND_START,
1287 p_WEDNESDAY_SECOND_END => l_WEDNESDAY_SECOND_END ,
1288 p_THURSDAY_SECOND_START =>l_THURSDAY_SECOND_START ,
1289 p_THURSDAY_SECOND_END => l_THURSDAY_SECOND_END ,
1290 p_FRIDAY_SECOND_START => l_FRIDAY_SECOND_START,
1291 p_FRIDAY_SECOND_END => l_FRIDAY_SECOND_END ,
1292 p_SATURDAY_SECOND_START =>l_SATURDAY_SECOND_START ,
1293 p_SATURDAY_SECOND_END =>l_SATURDAY_SECOND_END ,
1294 p_SUNDAY_SECOND_START =>l_SUNDAY_SECOND_START ,
1295 p_SUNDAY_SECOND_END =>l_SUNDAY_SECOND_END ,
1296 p_DESCRIPTION => l_DESCRIPTION,
1297 x_return_status => l_return_status,
1298 x_msg_count => l_msg_count,
1299 x_msg_data => l_msg_data
1300
1301 );
1302
1303 If l_return_status = fnd_api.g_ret_sts_error then
1304 raise fnd_api.g_exc_error;
1305 end if;
1306
1307 If l_return_status = fnd_api.g_ret_sts_unexp_error then
1308 raise fnd_Api.g_exc_unexpected_error;
1309 end if;
1310
1311
1312 CSF_ACCESS_HOURS_PVT.UPDATE_ACCESS_HOURS(
1313 p_API_VERSION => 1.0,
1314 p_INIT_MSG_LIST => 'F',
1315 p_ACCESS_HOUR_ID =>p_ACCESS_HOUR_ID,
1316 p_TASK_ID=>p_TASK_ID,
1317 p_ACCESS_HOUR_REQD=>l_ACCESS_HOUR_REQD,
1318 p_AFTER_HOURS_FLAG =>l_AFTER_HOURS_FLAG,
1319 p_MONDAY_FIRST_START =>l_MONDAY_FIRST_START,
1320 p_MONDAY_FIRST_END =>l_MONDAY_FIRST_END,
1321 p_TUESDAY_FIRST_START =>l_TUESDAY_FIRST_START,
1322 p_TUESDAY_FIRST_END =>l_TUESDAY_FIRST_END,
1323 p_WEDNESDAY_FIRST_START =>l_WEDNESDAY_FIRST_START,
1324 p_WEDNESDAY_FIRST_END =>l_WEDNESDAY_FIRST_END ,
1325 p_THURSDAY_FIRST_START =>l_THURSDAY_FIRST_START ,
1326 p_THURSDAY_FIRST_END =>l_THURSDAY_FIRST_END,
1327 p_FRIDAY_FIRST_START =>l_FRIDAY_FIRST_START,
1328 p_FRIDAY_FIRST_END =>l_FRIDAY_FIRST_END ,
1329 p_SATURDAY_FIRST_START =>l_SATURDAY_FIRST_START,
1330 p_SATURDAY_FIRST_END => l_SATURDAY_FIRST_END ,
1331 p_SUNDAY_FIRST_START =>l_SUNDAY_FIRST_START ,
1332 p_SUNDAY_FIRST_END =>l_SUNDAY_FIRST_END ,
1333 p_MONDAY_SECOND_START => l_MONDAY_SECOND_START,
1334 p_MONDAY_SECOND_END =>l_MONDAY_SECOND_END ,
1335 p_TUESDAY_SECOND_START =>l_TUESDAY_SECOND_START ,
1336 p_TUESDAY_SECOND_END =>l_TUESDAY_SECOND_END,
1337 p_WEDNESDAY_SECOND_START =>l_WEDNESDAY_SECOND_START,
1338 p_WEDNESDAY_SECOND_END => l_WEDNESDAY_SECOND_END ,
1339 p_THURSDAY_SECOND_START =>l_THURSDAY_SECOND_START ,
1340 p_THURSDAY_SECOND_END => l_THURSDAY_SECOND_END ,
1341 p_FRIDAY_SECOND_START => l_FRIDAY_SECOND_START,
1342 p_FRIDAY_SECOND_END => l_FRIDAY_SECOND_END ,
1343 p_SATURDAY_SECOND_START =>l_SATURDAY_SECOND_START ,
1344 p_SATURDAY_SECOND_END =>l_SATURDAY_SECOND_END ,
1345 p_SUNDAY_SECOND_START =>l_SUNDAY_SECOND_START ,
1346 p_SUNDAY_SECOND_END =>l_SUNDAY_SECOND_END ,
1347 p_DESCRIPTION => l_DESCRIPTION,
1348 px_object_version_number => px_object_version_number,
1349 p_commit => nvl(p_commit,fnd_Api.g_false),
1350 x_return_status => l_return_status,
1351 x_msg_count => l_msg_count,
1352 x_msg_data => l_msg_data
1353
1354 );
1355
1356 If l_return_status = fnd_api.g_ret_sts_error then
1360 If l_return_status = fnd_api.g_ret_sts_unexp_error then
1357 raise fnd_api.g_exc_error;
1358 end if;
1359
1361 raise fnd_api.g_exc_unexpected_error;
1362 end if;
1363
1364 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1365
1366
1367 EXCEPTION
1368 WHEN fnd_api.g_exc_error then
1369 ROLLBACK TO update_access_hours_pub;
1370 x_return_status := fnd_api.g_ret_sts_error;
1371 /*x_msg_count := l_msg_count;
1372 x_msg_data := l_msg_data;*/
1373 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1374
1375 WHEN fnd_api.g_exc_unexpected_error then
1376 ROLLBACK TO update_access_hours_pub;
1377 x_return_status := fnd_api.g_ret_sts_unexp_error;
1378 /*x_msg_count := l_msg_count;
1379 x_msg_data := l_msg_data;*/
1380 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1381
1382 WHEN OTHERS then
1383 ROLLBACK TO update_access_hours_pub;
1384 x_return_status := fnd_api.g_ret_sts_unexp_error;
1385 /*x_msg_count := l_msg_count;
1386 x_msg_data := l_msg_data;*/
1387 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1388
1389
1390 END UPDATE_ACCESS_HOURS;
1391
1392 PROCEDURE DELETE_ACCESS_HOURS(
1393 p_TASK_ID NUMBER,
1394 p_ACCESS_HOUR_ID NUMBER,
1395 p_API_VERSION NUMBER,
1396 p_init_msg_list varchar2 default null,
1397 -- p_commit in varchar2 default fnd_api.g_false,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_data OUT NOCOPY VARCHAR2,
1400 x_msg_count OUT NOCOPY NUMBER)
1401 IS
1402 l_return_status varchar2(100);
1403 l_msg_count NUMBER;
1404 l_msg_data varchar2(1000);
1405 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PUB.DELETE_ACCESS_HOURS';
1406 l_api_version CONSTANT Number := 1.0 ;
1407 l_sta varchar2(1);
1408 l_cnt number;
1409 l_msg varchar2(2000);
1410 l_task_status varchar2(1);
1411 l_temp varchar2(1);
1412
1413 CURSOR C_EXISTS(p_task_id number) is select 'Y' from csf_access_hours_vl where task_id=p_task_id;
1414
1415 BEGIN
1416 SAVEPOINT delete_access_hours_pub;
1417 x_return_status := fnd_api.g_ret_sts_success;
1418
1419 IF NOT fnd_api.compatible_api_call (
1420 l_api_version,
1421 p_api_version,
1422 l_api_name_full,
1423 g_pkg_name
1424 )
1425 then
1426 raise fnd_Api.g_exc_unexpected_error;
1427 end if;
1428
1429 IF fnd_api.to_boolean( nvl(p_init_msg_list,fnd_Api.g_false)) then
1430 fnd_msg_pub.initialize;
1431 end if;
1432
1433 If p_task_id is NULL or p_ACCESS_HOUR_ID is NULL then
1434 fnd_message.set_name ('CSF','CSF_ACCESS_INVALID_PARAMETER');--message required
1435 fnd_msg_pub.add;
1436 raise fnd_api.g_exc_error;
1437 end if;
1438
1439 open c_exists(p_task_id);
1440 fetch c_exists into l_temp;
1441 if c_exists%notfound then
1442 close c_exists;
1443 fnd_message.set_name('CSF','CSF_ACCESS_NO_ROW_EXISTS');
1444 fnd_msg_pub.add;
1445 raise fnd_api.g_exc_error;
1446 else
1447 close c_exists;
1448 end if;
1449
1450
1451 check_parameters(
1452 p_CALLING_ROUTINE => 'DELETE_ROW',
1453 p_TASK_ID => p_TASK_ID,
1454 x_return_status => l_return_status,
1455 x_msg_count => l_msg_count,
1456 x_msg_data => l_msg_data
1457
1458 );
1459
1460 If l_return_status = fnd_api.g_ret_sts_error then
1461 raise fnd_api.g_exc_error;
1462 end if;
1463
1464 If l_return_status = fnd_api.g_ret_sts_unexp_error then
1465 raise fnd_Api.g_exc_unexpected_error;
1466 end if;
1467
1468
1469 CSF_ACCESS_HOURS_PVT.DELETE_ACCESS_HOURS(
1470 p_API_VERSION => 1.0,
1471 p_INIT_MSG_LIST => 'F',
1472 p_ACCESS_HOUR_ID =>p_ACCESS_HOUR_ID,
1473 p_commit =>fnd_api.g_true, --p_commit,--fnd_api.g_true,
1474 x_return_status => l_return_status,
1475 x_msg_count => l_msg_count,
1476 x_msg_data => l_msg_data
1477 );
1478
1479 /* If (SQL%NOTFOUND) then
1480 RAISE NO_DATA_FOUND;
1481 end if; */
1482
1483 /* DELETE FROM CSF_ACCESS_HOURS_B
1484 WHERE ACCESS_HOUR_ID=p_ACCESS_HOUR_ID;*/
1485
1486 /* If (SQL%NOTFOUND) then
1487 RAISE NO_DATA_FOUND;
1488 end if;*/
1489
1490 If l_return_status = fnd_api.g_ret_sts_error then
1491 raise fnd_api.g_exc_error;
1492 end if;
1493
1494 If l_return_status = fnd_api.g_ret_sts_unexp_error then
1495 raise fnd_Api.g_exc_unexpected_error;
1496 end if;
1497
1498 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1499
1500 EXCEPTION
1501 WHEN fnd_api.g_exc_error then
1502 ROLLBACK TO delete_access_hours_pub;
1503 x_return_status := fnd_api.g_ret_sts_error;
1504 /*x_msg_count := l_msg_count;
1505 x_msg_data := l_msg_data;*/
1506 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1507
1508 WHEN fnd_api.g_exc_unexpected_error then
1509 ROLLBACK TO delete_access_hours_pub;
1510 x_return_status := fnd_api.g_ret_sts_unexp_error;
1511 /*x_msg_count := l_msg_count;
1512 x_msg_data := l_msg_data;*/
1513 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1514
1515 WHEN OTHERS then
1516 ROLLBACK TO delete_access_hours_pub;
1517 x_return_status := fnd_api.g_ret_sts_unexp_error;
1518 /*x_msg_count := l_msg_count;
1519 x_msg_data := l_msg_data;*/
1520 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1521
1522 END DELETE_ACCESS_HOURS;
1523
1524 PROCEDURE update_access_hours(
1528 , p_task_id number
1525 p_api_version number
1526 , p_init_msg_list varchar2
1527 , p_commit varchar2
1529 , p_access_hour_reqd varchar2
1530 , x_object_version_number in out nocopy number
1531 , x_return_status out nocopy varchar2
1532 , x_msg_data out nocopy varchar2
1533 , x_msg_count out nocopy number
1534 )
1535 IS
1536 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PUB.UPDATE_ACCESS_HOURS-2';
1537 l_api_version CONSTANT Number := 1.0 ;
1538 l_return_status varchar2(100);
1539 l_msg_count NUMBER;
1540 l_msg_data varchar2(2000);
1541
1542 cursor c_existing_values(p_task_id number) is
1543 select
1544 ACCESS_HOUR_ID,
1545 ACCESSHOUR_REQUIRED,
1546 AFTER_HOURS_FLAG,
1547 DESCRIPTION,
1548 object_version_number
1549 from csf_access_hours_vl
1550 where task_id=p_task_id;
1551
1552 l_rec c_existing_values%rowtype;
1553 l_object_version_number number;
1554 l_task_status varchar2(1);
1555
1556
1557
1558
1559 BEGIN
1560
1561 SAVEPOINT update_access_hours_pub2;
1562 x_return_status := fnd_api.g_ret_sts_success;
1563
1564 IF NOT fnd_api.compatible_api_call (
1565 l_api_version,
1566 p_api_version,
1567 l_api_name_full,
1568 g_pkg_name
1569 )
1570 then
1571 raise fnd_Api.g_exc_unexpected_error;
1572 end if;
1573 IF fnd_api.to_boolean(nvl(p_init_msg_list,fnd_api.g_false)) then
1574 fnd_msg_pub.initialize;
1575 end if;
1576
1577
1578 If p_task_id is NULL then
1579 fnd_message.set_name ('CSF','CSF_ACCESS_INVALID_PARAMETER');--message required
1580 fnd_msg_pub.add;
1581 raise fnd_api.g_exc_error;
1582 end if;
1583
1584
1585
1586 open c_existing_values(p_TASK_ID);
1587 fetch c_existing_values into l_rec;
1588 if c_existing_values%notfound then
1589 close c_existing_values;
1590 fnd_message.set_name('CSF','CSF_ACCESS_NO_ROW_EXISTS');
1591 fnd_msg_pub.add;
1592 raise fnd_api.g_exc_error;
1593 end if;
1594 close c_existing_values;
1595
1596 l_object_version_number := l_rec.object_version_number;
1597
1598 UPDATE_ACCESS_HOURS(
1599 p_ACCESS_HOUR_ID => l_rec.access_hour_id,
1600 p_TASK_ID =>P_TASK_ID,
1601 p_API_VERSION =>l_api_version,
1602 p_init_msg_list =>nvl(p_init_msg_list,fnd_api.g_false),
1603 p_commit => nvl(p_commit,fnd_api.g_false),
1604 p_ACCESS_HOUR_REQD=>p_Access_hour_reqd ,
1605 p_AFTER_HOURS_FLAG=> l_rec.after_hours_flag,
1606 p_MONDAY_FIRST_START =>NULL,
1607 p_MONDAY_FIRST_END =>NULL,
1608 p_MONDAY_SECOND_START =>NULL,
1609 p_MONDAY_SECOND_END =>NULL,
1610 p_TUESDAY_FIRST_START =>NULL,
1611 p_TUESDAY_FIRST_END => NULL,
1612 p_TUESDAY_SECOND_START =>NULL,
1613 p_TUESDAY_SECOND_END => NULL,
1614 p_WEDNESDAY_FIRST_START => NULL,
1615 p_WEDNESDAY_FIRST_END =>NULL,
1616 p_WEDNESDAY_SECOND_START => NULL,
1617 p_WEDNESDAY_SECOND_END =>NULL,
1618 p_THURSDAY_FIRST_START =>NULL,
1619 p_THURSDAY_FIRST_END =>NULL,
1620 p_THURSDAY_SECOND_START =>NULL,
1621 p_THURSDAY_SECOND_END =>NULL,
1622 p_FRIDAY_FIRST_START =>NULL,
1623 p_FRIDAY_FIRST_END => NULL,
1624 p_FRIDAY_SECOND_START =>NULL,
1625 p_FRIDAY_SECOND_END =>NULL,
1626 p_SATURDAY_FIRST_START =>NULL,
1627 p_SATURDAY_FIRST_END =>NULL,
1628 p_SATURDAY_SECOND_START => NULL,
1629 p_SATURDAY_SECOND_END =>NULL,
1630 p_SUNDAY_FIRST_START =>NULL,
1631 p_SUNDAY_FIRST_END => NULL,
1632 p_SUNDAY_SECOND_START =>NULL,
1633 p_SUNDAY_SECOND_END =>NULL,
1634 p_DESCRIPTION=> l_rec.description,
1635 px_object_version_number=>l_object_version_number,
1636 x_return_status=>x_return_status,
1637 x_msg_data => x_msg_data,
1638 x_msg_count=> x_msg_count
1639 );
1640 x_object_version_number := l_object_version_number;
1641
1642 EXCEPTION
1643 WHEN fnd_api.g_exc_error then
1644 ROLLBACK TO update_access_hours_pub2;
1645 x_return_status := fnd_api.g_ret_sts_error;
1646 /*x_msg_count := l_msg_count;
1647 x_msg_data := l_msg_data;*/
1648 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1649
1650 WHEN fnd_api.g_exc_unexpected_error then
1651 ROLLBACK TO update_access_hours_pub2;
1652 x_return_status := fnd_api.g_ret_sts_unexp_error;
1653 /*x_msg_count := l_msg_count;
1654 x_msg_data := l_msg_data;*/
1655 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1656
1657 WHEN OTHERS then
1658 ROLLBACK TO update_access_hours_pub2;
1659 x_return_status := fnd_api.g_ret_sts_unexp_error;
1660 /*x_msg_count := l_msg_count;
1661 x_msg_data := l_msg_data;*/
1662 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1663
1664
1665 END UPDATE_ACCESS_HOURS;
1666
1667 PROCEDURE ADD_LANGUAGE
1668 IS
1669 BEGIN
1670 NULL;
1671 END ADD_LANGUAGE;
1672
1673 --BEGIN
1674 -- ADD SESSION INFO
1675 --g_user_id := fnd_global.user_id;
1676 --g_login_id := fnd_global.login_id;
1677
1678 END CSF_ACCESS_HOURS_PUB;
[Home] [Help]