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