1 PACKAGE BODY CAC_SR_OBJECT_CAPACITY_PUB AS
2 /* $Header: cacsrocpb.pls 120.1 2005/07/02 02:18:34 appldev noship $ */
3
4 -- package identification
5 g_pkg_name constant varchar2(30) := 'CAC_SR_OBJECT_CAPACITY_PUB';
6 g_default_capacity_status constant number := 1;
7
8 /*******************************************************************************
9 ** Private APIs
10 *******************************************************************************/
11
12 FUNCTION get_new_object_capacity_id
13 /*******************************************************************************
14 ** get_new_object_capacity_id
15 **
16 ** This API returns the next value of the sequence cac_sr_object_capacity_s
17 **
18 *******************************************************************************/
19 RETURN NUMBER IS
20
21 CURSOR get_object_capacity_id IS
22 SELECT cac_sr_object_capacity_s.nextval
23 FROM dual;
24
25 l_return NUMBER;
26
27 BEGIN
28
29 OPEN get_object_capacity_id;
30 FETCH get_object_capacity_id
31 INTO l_return;
32 CLOSE get_object_capacity_id;
33
34 RETURN l_return;
35
36 END get_new_object_capacity_id;
37
38 FUNCTION validate_object_capacity
39 /*******************************************************************************
40 ** validate_object_capacity
41 **
42 ** This API checks if all the attributes of the object capacity record have
43 ** been entered correctly. If any validation fails then it adds a message
44 ** in the error stack and return false.
45 **
46 *******************************************************************************/
47 (
48 p_object_capacity_rec IN OBJECT_CAPACITY_REC_TYPE
49 ) RETURN BOOLEAN IS
50
51 BEGIN
52 IF ((p_object_capacity_rec.OBJECT_TYPE IS NULL) OR
53 (p_object_capacity_rec.OBJECT_ID IS NULL) OR
54 (p_object_capacity_rec.START_DATE_TIME IS NULL) OR
55 (p_object_capacity_rec.END_DATE_TIME IS NULL) OR
56 (p_object_capacity_rec.AVAILABLE_HOURS IS NULL))
57 THEN
58 fnd_message.set_name('JTF', 'JTF_CAL_REQUIRED_PARAMETERS');
59 fnd_message.set_token('P_PARAMETER','OBJECT_TYPE, OBJECT_ID, START_DATE_TIME, END_DATE_TIME, and AVAILABLE_HOURS');
60 fnd_msg_pub.add;
61 RETURN FALSE;
62 END IF;
63
64 RETURN TRUE;
65
66 END validate_object_capacity;
67
68 /*******************************************************************************
69 ** Public APIs
70 *******************************************************************************/
71
72 PROCEDURE generate_object_capacity
73 /*******************************************************************************
74 ** generate_object_capacity
75 **
76 ** This API calls JTF_CALENDAR_PUB_24HR.Get_Resource_Shifts API and builds the
77 ** pl/sql table with object capacity records. It populates object_capacity_id
78 ** for each record with the sequence cac_sr_object_capacity_s value if the
79 ** parameter p_PopulateID is 'T'.
80 ** It will return a list of tasks also if the p_FetchTasks parameter is set to
81 ** true. This is needed only if you want to fetch all the tasks at one shot as
82 ** it will be more performant than fetching tasks for each object capacity
83 ** record one by one.
84 **
85 *******************************************************************************/
86 ( p_api_version IN NUMBER -- API version you coded against
87 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
88 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
89 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
90 , p_Start_Date IN DATE -- start date and time of period of interest
91 , p_End_Date IN DATE -- end date and time of period of interest
92 , p_Populate_ID IN VARCHAR2 DEFAULT 'F' -- Populate the object_capacity_id of the record?
93 -- Should be set to 'T' if the ids are needed for foreign key reference
94 -- 'F' means the ids will be genrated while inserting these records in the table
95 , p_Fetch_Tasks IN VARCHAR2 DEFAULT 'F' -- Fetch tasks for the time period too?
96 , x_Object_Capacity OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
97 -- return table of object capacity records
98 , x_Object_Tasks OUT NOCOPY OBJECT_TASKS_TBL_TYPE
99 -- return table of object task records
100 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
101 -- 'E': API completed with recoverable errors; explanation on errorstack
102 -- 'U': API completed with UN recoverable errors: error message on error stack
103 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
104 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
105 ) IS
106
107 CURSOR c_tasks
108 ( b_resource_type varchar2
109 , b_resource_id number
110 , b_date_min date
111 , b_date_max date
112 )
113 is
114 select jta.task_assignment_id
115 , jta.object_version_number asn_ovn
116 , jta.assignment_status_id
117 , jta.free_busy_type
118 , jtb.task_id
119 , jtb.object_version_number task_ovn
120 , jtb.task_status_id
121 , jtb.task_type_id
122 , jtb.scheduled_start_date
123 , jtb.scheduled_end_date
124 , jtb.planned_start_date
125 , jtb.planned_end_date
126 , jtb.address_id
127 , jtb.customer_id
128 from jtf_task_statuses_b jtsb
129 , jtf_task_assignments jta
130 , jtf_tasks_b jtb
131 where nvl(jtsb.closed_flag, 'N') = 'N'
132 and nvl(jtsb.completed_flag, 'N') = 'N'
133 and nvl(jtsb.cancelled_flag, 'N') = 'N'
134 and jtsb.task_status_id = jta.assignment_status_id
135 and jta.resource_type_code = b_resource_type
136 and jta.resource_id = b_resource_id
137 and jta.task_id = jtb.task_id
138 and nvl(jtb.deleted_flag, 'N') = 'N'
139 and nvl(jtb.open_flag, 'Y') = 'Y'
140 and jtb.scheduled_start_date BETWEEN b_date_min AND b_date_max
141 and jtb.scheduled_end_date BETWEEN b_date_min AND b_date_max
142 order by jtb.scheduled_start_date;
143
144 l_api_name constant varchar2(30) := 'GENERATE_OBJECT_CAPACITY';
145 l_api_version constant number := 1.0;
146 l_return_status varchar2(1);
147 l_msg_count number;
148 l_msg_data varchar2(2000);
149 l_shift JTF_CALENDAR_PUB_24HR.SHIFT_TBL_TYPE;
150 l_idx number;
151 l_tbl_count number;
152 l_Populate_ID boolean;
153 l_Fetch_Tasks boolean;
154
155 BEGIN
156
157 -- check version compatibility
158 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
159 l_api_name, g_pkg_name)
160 THEN
161 RAISE fnd_api.g_exc_unexpected_error;
162 END IF;
163 -- initialize message stack if required
164 IF fnd_api.to_boolean(p_init_msg_list)
165 THEN
166 fnd_msg_pub.initialize;
167 END IF;
168 -- initialize return status
169 x_return_status := fnd_api.g_ret_sts_success;
170
171 -- call current shift api to get resource shifts
172 JTF_CALENDAR_PUB_24HR.GET_RESOURCE_SHIFTS
173 ( p_api_version => 1.0
174 , p_resource_id => p_object_id
175 , p_resource_type => p_object_type
176 , p_start_date => p_start_date
177 , p_end_date => p_end_date
178 , x_return_status => l_return_status
179 , x_msg_count => l_msg_count
180 , x_msg_data => l_msg_data
181 , x_shift => l_shift
182 );
183 IF l_return_status <> fnd_api.g_ret_sts_success
184 THEN
185 IF l_return_status = fnd_api.g_ret_sts_error
186 THEN
187 RAISE fnd_api.g_exc_error;
188 ELSE
189 RAISE fnd_api.g_exc_unexpected_error;
190 END IF;
191 END IF;
192
193 l_tbl_count := 0;
194 l_Populate_ID := fnd_api.to_boolean(p_Populate_ID);
195 l_idx := l_shift.FIRST;
196
197 WHILE (l_idx IS NOT NULL)
198 LOOP
199 l_tbl_count := l_tbl_count + 1;
200 IF l_Populate_ID
201 THEN
202 x_object_capacity(l_tbl_count).OBJECT_CAPACITY_ID := get_new_object_capacity_id;
203 ELSE
204 x_object_capacity(l_tbl_count).OBJECT_CAPACITY_ID := NULL;
205 END IF;
206 x_object_capacity(l_tbl_count).OBJECT_TYPE := p_object_type;
207 x_object_capacity(l_tbl_count).OBJECT_ID := p_object_id;
208 x_object_capacity(l_tbl_count).START_DATE_TIME := l_shift(l_idx).START_TIME;
209 x_object_capacity(l_tbl_count).END_DATE_TIME := l_shift(l_idx).END_TIME;
210 x_object_capacity(l_tbl_count).AVAILABLE_HOURS := (l_shift(l_idx).END_TIME -
211 l_shift(l_idx).START_TIME) * 24.0;
212 x_object_capacity(l_tbl_count).AVAILABLE_HOURS_BEFORE := NULL;
213 x_object_capacity(l_tbl_count).AVAILABLE_HOURS_AFTER := NULL;
214 x_object_capacity(l_tbl_count).SCHEDULE_DETAIL_ID := 0 - l_shift(l_idx).shift_construct_id;
215 x_object_capacity(l_tbl_count).STATUS := g_default_capacity_status;
216
217 l_idx := l_shift.NEXT(l_idx);
218 END LOOP;
219
220 -- Fetch tasks now
221 l_tbl_count := 0;
222 l_idx := x_object_capacity.FIRST;
223 IF fnd_api.to_boolean(p_Fetch_Tasks)
224 THEN
225 -- to take care of any boundry condition fetch tasks with -+ 1 days of the start and end
226 FOR ref_tasks IN c_tasks(p_object_type,p_object_id,p_start_date-1,p_end_date+1)
227 LOOP
228 -- First step is to find if this task lies in any object capacity record
229 -- Note that the l_idx is not reset in the for loop. The reason being
230 -- since both the capacity table and ref_tasks are sorted the new task
231 -- fetched will always be either in the current capacity record or in
232 -- the ones which are after that
233 WHILE l_idx IS NOT NULL
234 LOOP
235 -- Either the task which ends in this capacity record or which starts
236 -- here are considered to be the ones connected to this shift.
237 IF ((ref_tasks.scheduled_start_date BETWEEN x_object_capacity(l_idx).START_DATE_TIME
238 AND x_object_capacity(l_idx).END_DATE_TIME) OR (ref_tasks.scheduled_end_date
239 BETWEEN x_object_capacity(l_idx).START_DATE_TIME AND x_object_capacity(l_idx).END_DATE_TIME))
240 THEN
241 -- This is the right one
242 l_tbl_count := l_tbl_count + 1;
243 -- Create a new entry in the object tasks table
244 x_Object_Tasks(l_tbl_count).OBJECT_CAPACITY_TBL_IDX := l_idx;
245 x_Object_Tasks(l_tbl_count).TASK_ASSIGNMENT_ID := ref_tasks.task_assignment_id;
246 x_Object_Tasks(l_tbl_count).TASK_ASSIGNMENT_OVN := ref_tasks.asn_ovn;
247 x_Object_Tasks(l_tbl_count).ASSIGNMENT_STATUS_ID := ref_tasks.assignment_status_id;
248 x_Object_Tasks(l_tbl_count).FREE_BUSY_TYPE := ref_tasks.FREE_BUSY_TYPE;
249 x_Object_Tasks(l_tbl_count).TASK_ID := ref_tasks.task_id;
250 x_Object_Tasks(l_tbl_count).TASK_OVN := ref_tasks.task_ovn;
251 x_Object_Tasks(l_tbl_count).TASK_TYPE_ID := ref_tasks.task_type_id;
252 x_Object_Tasks(l_tbl_count).TASK_STATUS_ID := ref_tasks.task_status_id;
253 x_Object_Tasks(l_tbl_count).SCHEDULED_START_DATE := ref_tasks.scheduled_start_date;
254 x_Object_Tasks(l_tbl_count).SCHEDULED_END_DATE := ref_tasks.scheduled_end_date;
255 x_Object_Tasks(l_tbl_count).PLANNED_START_DATE := ref_tasks.planned_start_date;
256 x_Object_Tasks(l_tbl_count).PLANNED_END_DATE := ref_tasks.planned_end_date;
257 x_Object_Tasks(l_tbl_count).CUSTOMER_ID := ref_tasks.customer_id;
258 x_Object_Tasks(l_tbl_count).ADDRESS_ID := ref_tasks.address_id;
259 -- Task record updated, so exit the object capacity loop
260 EXIT;
261 ELSE
262 -- now check if this task is before the current object capacity record
263 -- if it is then there is no need to loop through the object capacity
264 -- records since the next one would be even after the current one.
265 -- which means that the fetched task doesn't qualify in any object
266 -- capacity record and so should be ignored.
267 IF (ref_tasks.scheduled_end_date < x_object_capacity(l_idx).START_DATE_TIME)
268 THEN
269 EXIT; -- exit the loop
270 END IF;
271 -- if the task is after the current object capacity record then continue
272 -- with the loop as this task can be in the next one.
273 END IF;
274 -- go to the next one
275 l_idx := x_object_capacity.NEXT(l_idx);
276 END LOOP;
277 -- if we have already reached the end of object capacity table then there
278 -- is no need to continue fetching tasks, so exit
279 IF l_idx IS NULL
280 THEN
281 EXIT; -- exit the tasks loop
282 END IF;
283 END LOOP;
284 END IF;
285
286 fnd_msg_pub.count_and_get( p_encoded => 'F'
287 , p_count => x_msg_count
288 , p_data => x_msg_data
289 );
290 EXCEPTION
291
292 WHEN fnd_api.g_exc_error THEN
293 x_return_status := fnd_api.g_ret_sts_error;
294 fnd_msg_pub.count_and_get( p_encoded => 'F'
295 , p_count => x_msg_count
296 , p_data => x_msg_data
297 );
298
299 WHEN fnd_api.g_exc_unexpected_error THEN
300 x_return_status := fnd_api.g_ret_sts_unexp_error;
301 fnd_msg_pub.count_and_get( p_encoded => 'F'
302 , p_count => x_msg_count
303 , p_data => x_msg_data
304 );
305
306 WHEN OTHERS THEN
307 x_return_status := fnd_api.g_ret_sts_unexp_error;
308 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
309 THEN
310 fnd_msg_pub.add_exc_msg( g_pkg_name
311 , l_api_name
312 );
313 END IF;
314 fnd_msg_pub.count_and_get( p_encoded => 'F'
315 , p_count => x_msg_count
316 , p_data => x_msg_data
317 );
318
319 END generate_object_capacity;
320
321 PROCEDURE insert_object_capacity
322 /*******************************************************************************
323 ** insert_object_capacity
324 **
325 ** This API calls table handler to insert data into cac_sr_object_capacity
326 ** using pl/sql table passed. It populates object_capacity_id for each record
327 ** if with the sequence cac_sr_object_capacity_s value if it is NULL in the
328 ** record.
329 ** It updates the task assignment with the corresponding object_capacity_id
330 ** if the p_Update_Tasks parameter is set to 'T'.
331 **
332 *******************************************************************************/
333 ( p_api_version IN NUMBER -- API version you coded against
334 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
335 , p_Object_Capacity IN OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
336 -- table of object capacity records which should be inserte
337 , p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Update task assignments too?
338 , p_Object_Tasks IN OBJECT_TASKS_TBL_TYPE
339 -- table of object task records to be updated
340 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
341 -- 'E': API completed with recoverable errors; explanation on errorstack
342 -- 'U': API completed with UN recoverable errors: error message on error stack
343 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
344 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
345 ) IS
346
347 l_api_name constant varchar2(30) := 'INSERT_OBJECT_CAPACITY';
348 l_api_version constant number := 1.0;
349 l_idx number;
350 l_return_status varchar2(1);
351 l_msg_count number;
352 l_msg_data varchar2(2000);
353 l_current_date date;
354 l_user number;
355 l_login number;
356 l_rowid VARCHAR2(255);
357 l_ovn number;
358
359 BEGIN
360
361 -- check version compatibility
362 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
363 l_api_name, g_pkg_name)
364 THEN
365 RAISE fnd_api.g_exc_unexpected_error;
366 END IF;
367 -- initialize message stack if required
368 IF fnd_api.to_boolean(p_init_msg_list)
369 THEN
370 fnd_msg_pub.initialize;
371 END IF;
372 -- initialize return status
373 x_return_status := fnd_api.g_ret_sts_success;
374
375 l_current_date := SYSDATE;
376 l_user := FND_GLOBAL.USER_ID;
377 l_login := FND_GLOBAL.LOGIN_ID;
378
379 l_idx := p_Object_Capacity.FIRST;
380
381 WHILE l_idx IS NOT NULL
382 LOOP
383 -- validate if the record is correct
384 IF NOT validate_object_capacity(p_Object_Capacity(l_idx))
385 THEN
386 RAISE fnd_api.g_exc_unexpected_error;
387 END IF;
388 -- populate the primary key if needed
389 IF p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID IS NULL
390 THEN
391 p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID := get_new_object_capacity_id;
392 END IF;
393 IF p_Object_Capacity(l_idx).STATUS IS NULL
394 THEN
395 p_Object_Capacity(l_idx).STATUS := g_default_capacity_status;
396 END IF;
397 p_Object_Capacity(l_idx).OBJECT_VERSION_NUMBER := 1;
398 -- call the table handler to insert data
399 CAC_SR_OBJECT_CAPACITY_PKG.INSERT_ROW
400 (
401 X_ROWID => l_rowid,
402 X_OBJECT_CAPACITY_ID => p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID,
403 X_OBJECT_VERSION_NUMBER => p_Object_Capacity(l_idx).OBJECT_VERSION_NUMBER,
404 X_OBJECT_TYPE => p_Object_Capacity(l_idx).OBJECT_TYPE,
405 X_OBJECT_ID => p_Object_Capacity(l_idx).OBJECT_ID,
406 X_START_DATE_TIME => p_Object_Capacity(l_idx).START_DATE_TIME,
407 X_END_DATE_TIME => p_Object_Capacity(l_idx).END_DATE_TIME,
408 X_AVAILABLE_HOURS => p_Object_Capacity(l_idx).AVAILABLE_HOURS,
409 X_AVAILABLE_HOURS_BEFORE => p_Object_Capacity(l_idx).AVAILABLE_HOURS_BEFORE,
410 X_AVAILABLE_HOURS_AFTER => p_Object_Capacity(l_idx).AVAILABLE_HOURS_AFTER,
411 X_SCHEDULE_DETAIL_ID => p_Object_Capacity(l_idx).SCHEDULE_DETAIL_ID,
412 X_STATUS => p_Object_Capacity(l_idx).STATUS,
413 X_CREATION_DATE => l_current_date,
414 X_CREATED_BY => l_user,
415 X_LAST_UPDATE_DATE => l_current_date,
416 X_LAST_UPDATED_BY => l_user,
417 X_LAST_UPDATE_LOGIN => l_login
418 );
419 l_idx := p_Object_Capacity.NEXT(l_idx);
420 END LOOP;
421
422 -- check if task assignments need to be updated
423 IF fnd_api.to_boolean(p_Update_Tasks)
424 THEN
425 l_idx := p_Object_Tasks.FIRST;
426 WHILE l_idx IS NOT NULL
427 LOOP
428 -- find out if there is an object capacity id for this task assignment
429 IF ((p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX IS NOT NULL)
430 AND p_Object_Capacity.EXISTS(p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX))
431 THEN
432 -- Call assignments api to update object capacity id
433 l_ovn := p_Object_Tasks(l_idx).TASK_ASSIGNMENT_OVN;
434 JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT
435 (
436 p_api_version => 1.0,
437 p_commit => fnd_api.G_FALSE,
438 p_object_version_number => l_ovn,
439 p_task_assignment_id => p_Object_Tasks(l_idx).TASK_ASSIGNMENT_ID,
440 p_enable_workflow => NULL,
441 p_abort_workflow => NULL,
442 p_object_capacity_id => p_Object_Capacity(p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX).OBJECT_CAPACITY_ID,
443 x_return_status => l_return_status,
444 x_msg_count => l_msg_count,
445 x_msg_data => l_msg_data
446 );
447 IF l_return_status <> fnd_api.g_ret_sts_success
448 THEN
449 IF l_return_status = fnd_api.g_ret_sts_error
450 THEN
451 RAISE fnd_api.g_exc_error;
452 ELSE
453 RAISE fnd_api.g_exc_unexpected_error;
454 END IF;
455 END IF;
456 END IF;
457 l_idx := p_Object_Tasks.NEXT(l_idx);
458 END LOOP;
459 END IF;
460
461 fnd_msg_pub.count_and_get( p_encoded => 'F'
462 , p_count => x_msg_count
463 , p_data => x_msg_data
464 );
465 EXCEPTION
466
467 WHEN fnd_api.g_exc_error THEN
468 x_return_status := fnd_api.g_ret_sts_error;
469 fnd_msg_pub.count_and_get( p_encoded => 'F'
470 , p_count => x_msg_count
471 , p_data => x_msg_data
472 );
473
474 WHEN fnd_api.g_exc_unexpected_error THEN
475 x_return_status := fnd_api.g_ret_sts_unexp_error;
476 fnd_msg_pub.count_and_get( p_encoded => 'F'
477 , p_count => x_msg_count
478 , p_data => x_msg_data
479 );
480
481 WHEN OTHERS THEN
482 x_return_status := fnd_api.g_ret_sts_unexp_error;
483 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
484 THEN
485 fnd_msg_pub.add_exc_msg( g_pkg_name
486 , l_api_name
487 );
488 END IF;
489 fnd_msg_pub.count_and_get( p_encoded => 'F'
490 , p_count => x_msg_count
491 , p_data => x_msg_data
492 );
493
494 END insert_object_capacity;
495
496 PROCEDURE update_object_capacity
497 /*******************************************************************************
498 ** update_object_capacity
499 **
500 ** This API calls table handler to update data into cac_sr_object_capacity.
501 ** Only the available hours fields and status can be updated.
502 **
503 *******************************************************************************/
504 ( p_api_version IN NUMBER -- API version you coded against
505 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
506 , p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
507 , p_object_version_number IN NUMBER -- Object Version of the row to be updated
508 -- If this doesn't match the database value then that means someone else has updated the same row
509 , p_available_hours IN NUMBER DEFAULT NULL -- The new value of available hours
510 -- If it is NULL then no change is done to the existing data
511 , p_available_hours_before IN NUMBER DEFAULT NULL -- The new value of available before hours.
512 -- If it is NULL then no change is done to the existing data
513 -- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
514 , p_available_hours_after IN NUMBER DEFAULT NULL -- The new value of available before hours.
515 -- If it is NULL then no change is done to the existing data
516 -- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
517 , p_status IN NUMBER DEFAULT NULL -- The new value of the status
518 -- If it is NULL then no change is done to the existing data
519 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
520 -- 'E': API completed with recoverable errors; explanation on errorstack
521 -- 'U': API completed with UN recoverable errors: error message on error stack
522 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
523 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
524 ) IS
525
526 CURSOR C_ObjCap
527 (
528 b_object_capacity_id NUMBER
529 ) IS SELECT
530 OBJECT_VERSION_NUMBER,
531 OBJECT_TYPE,
532 OBJECT_ID,
533 START_DATE_TIME,
534 END_DATE_TIME,
535 AVAILABLE_HOURS,
536 AVAILABLE_HOURS_BEFORE,
537 AVAILABLE_HOURS_AFTER,
538 SCHEDULE_DETAIL_ID,
539 STATUS
540 FROM CAC_SR_OBJECT_CAPACITY
541 WHERE OBJECT_CAPACITY_ID = b_object_capacity_id
542 FOR UPDATE OF OBJECT_CAPACITY_ID NOWAIT;
543
544 l_api_name constant varchar2(30) := 'UPDATE_OBJECT_CAPACITY';
545 l_api_version constant number := 1.0;
546 l_current_date date;
547 l_user number;
548 l_login number;
549 l_Object_Capacity OBJECT_CAPACITY_REC_TYPE;
550
551 BEGIN
552
553 -- check version compatibility
554 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
555 l_api_name, g_pkg_name)
556 THEN
557 RAISE fnd_api.g_exc_unexpected_error;
558 END IF;
559 -- initialize message stack if required
560 IF fnd_api.to_boolean(p_init_msg_list)
561 THEN
562 fnd_msg_pub.initialize;
563 END IF;
564 -- initialize return status
565 x_return_status := fnd_api.g_ret_sts_success;
566
567 l_Object_Capacity.OBJECT_CAPACITY_ID := p_object_capacity_id;
568
569 OPEN C_ObjCap(l_Object_Capacity.OBJECT_CAPACITY_ID);
570 FETCH C_ObjCap
571 INTO l_Object_Capacity.OBJECT_VERSION_NUMBER,
572 l_Object_Capacity.OBJECT_TYPE,
573 l_Object_Capacity.OBJECT_ID,
574 l_Object_Capacity.START_DATE_TIME,
575 l_Object_Capacity.END_DATE_TIME,
576 l_Object_Capacity.AVAILABLE_HOURS,
577 l_Object_Capacity.AVAILABLE_HOURS_BEFORE,
578 l_Object_Capacity.AVAILABLE_HOURS_AFTER,
579 l_Object_Capacity.SCHEDULE_DETAIL_ID,
580 l_Object_Capacity.STATUS;
581 IF C_ObjCap%NOTFOUND
582 THEN
583 CLOSE C_ObjCap;
584 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
585 fnd_msg_pub.add;
586 RAISE fnd_api.g_exc_unexpected_error;
587 END IF;
588 CLOSE C_ObjCap;
589
590 -- check if record updated by another user
591 IF (NVL(p_object_version_number, -1) <>
592 l_Object_Capacity.OBJECT_VERSION_NUMBER)
593 THEN
594 fnd_message.set_name('FND', 'FND_RECORD_CHANGED_ERROR');
595 fnd_msg_pub.add;
596 RAISE fnd_api.g_exc_unexpected_error;
597 END IF;
598
599 -- Copy the changed values
600
601 -- Available hours is mandatory field, so update only if a new valid
602 -- value is passed.
603 IF ((p_available_hours IS NULL) OR (p_available_hours = FND_API.G_MISS_NUM))
604 THEN
605 NULL;
606 ELSE
607 l_Object_Capacity.AVAILABLE_HOURS := p_available_hours;
608 END IF;
609
610 -- Available hours before can be set to NULL or a new value
611 IF (p_available_hours_before IS NULL)
612 THEN
613 NULL;
614 ELSIF (p_available_hours_before = FND_API.G_MISS_NUM)
615 THEN
616 l_Object_Capacity.AVAILABLE_HOURS_BEFORE := NULL;
617 ELSE
618 l_Object_Capacity.AVAILABLE_HOURS_BEFORE := p_available_hours_before;
619 END IF;
620
621 -- Available hours after can be set to NULL or a new value
622 IF (p_available_hours_after IS NULL)
623 THEN
624 NULL;
625 ELSIF (p_available_hours_after = FND_API.G_MISS_NUM)
626 THEN
627 l_Object_Capacity.AVAILABLE_HOURS_AFTER := NULL;
628 ELSE
629 l_Object_Capacity.AVAILABLE_HOURS_AFTER := p_available_hours_after;
630 END IF;
631
632 -- Status is mandatory field, so update only if a new valid value is passed.
633 IF ((p_status IS NULL) OR (p_status = FND_API.G_MISS_NUM))
634 THEN
635 NULL;
636 ELSE
637 l_Object_Capacity.STATUS := p_status;
638 END IF;
639
640 -- Now increment the object version number by one
641 l_Object_Capacity.OBJECT_VERSION_NUMBER := l_Object_Capacity.OBJECT_VERSION_NUMBER + 1;
642
643 l_current_date := SYSDATE;
644 l_user := FND_GLOBAL.USER_ID;
645 l_login := FND_GLOBAL.LOGIN_ID;
646
647 -- call the table handler to update data
648 CAC_SR_OBJECT_CAPACITY_PKG.UPDATE_ROW
649 (
650 X_OBJECT_CAPACITY_ID => l_Object_Capacity.OBJECT_CAPACITY_ID,
651 X_OBJECT_VERSION_NUMBER => l_Object_Capacity.OBJECT_VERSION_NUMBER,
652 X_OBJECT_TYPE => l_Object_Capacity.OBJECT_TYPE,
653 X_OBJECT_ID => l_Object_Capacity.OBJECT_ID,
654 X_START_DATE_TIME => l_Object_Capacity.START_DATE_TIME,
655 X_END_DATE_TIME => l_Object_Capacity.END_DATE_TIME,
656 X_AVAILABLE_HOURS => l_Object_Capacity.AVAILABLE_HOURS,
657 X_AVAILABLE_HOURS_BEFORE => l_Object_Capacity.AVAILABLE_HOURS_BEFORE,
658 X_AVAILABLE_HOURS_AFTER => l_Object_Capacity.AVAILABLE_HOURS_AFTER,
659 X_SCHEDULE_DETAIL_ID => l_Object_Capacity.SCHEDULE_DETAIL_ID,
660 X_STATUS => l_Object_Capacity.STATUS,
661 X_CREATION_DATE => l_current_date,
662 X_CREATED_BY => l_user,
663 X_LAST_UPDATE_DATE => l_current_date,
664 X_LAST_UPDATED_BY => l_user,
665 X_LAST_UPDATE_LOGIN => l_login
666 );
667
668 fnd_msg_pub.count_and_get( p_encoded => 'F'
669 , p_count => x_msg_count
670 , p_data => x_msg_data
671 );
672 EXCEPTION
673
674 WHEN fnd_api.g_exc_error THEN
675 x_return_status := fnd_api.g_ret_sts_error;
676 fnd_msg_pub.count_and_get( p_encoded => 'F'
677 , p_count => x_msg_count
678 , p_data => x_msg_data
679 );
680
681 WHEN fnd_api.g_exc_unexpected_error THEN
682 x_return_status := fnd_api.g_ret_sts_unexp_error;
683 fnd_msg_pub.count_and_get( p_encoded => 'F'
684 , p_count => x_msg_count
685 , p_data => x_msg_data
686 );
687
688 WHEN OTHERS THEN
689 x_return_status := fnd_api.g_ret_sts_unexp_error;
690 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
691 THEN
692 fnd_msg_pub.add_exc_msg( g_pkg_name
693 , l_api_name
694 );
695 END IF;
696 fnd_msg_pub.count_and_get( p_encoded => 'F'
697 , p_count => x_msg_count
698 , p_data => x_msg_data
699 );
700
701 END update_object_capacity;
702
703 PROCEDURE delete_object_capacity
704 /*******************************************************************************
705 ** delete_object_capacity
706 **
707 ** This API calls table handler to delete data from cac_sr_object_capacity.
708 ** It will also update the task assignments and remove the object capacity id
709 ** if the p_update_tasks is true
710 **
711 *******************************************************************************/
712 ( p_api_version IN NUMBER -- API version you coded against
713 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
714 , p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
715 , p_object_version_number IN NUMBER -- Object Version of the row to be updated
716 -- If this doesn't match the database value then that means someone else has updated the same row
717 , p_Update_Tasks IN VARCHAR2 DEFAULT 'T' -- Update task assignments too?
718 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
719 -- 'E': API completed with recoverable errors; explanation on errorstack
720 -- 'U': API completed with UN recoverable errors: error message on error stack
721 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
722 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
723 ) IS
724
725 CURSOR C_ObjCap
726 (
727 b_object_capacity_id NUMBER
728 ) IS SELECT
729 OBJECT_VERSION_NUMBER
730 FROM CAC_SR_OBJECT_CAPACITY
731 WHERE OBJECT_CAPACITY_ID = b_object_capacity_id
732 FOR UPDATE OF OBJECT_CAPACITY_ID NOWAIT;
733
734 CURSOR c_tasks
735 (
736 b_object_capacity_id number
737 )
738 is
739 select jta.task_assignment_id
740 , jta.object_version_number
741 from jtf_task_statuses_b jtsb
742 , jtf_task_assignments jta
743 , jtf_tasks_b jtb
744 where jtsb.task_status_id = jta.assignment_status_id
745 and jta.object_capacity_id = b_object_capacity_id
746 and jta.task_id = jtb.task_id
747 and nvl(jtb.deleted_flag, 'N') = 'N'
748 order by jtb.scheduled_start_date;
749
750 l_api_name constant varchar2(30) := 'DELETE_OBJECT_CAPACITY';
751 l_api_version constant number := 1.0;
752 l_return_status varchar2(1);
753 l_msg_count number;
754 l_msg_data varchar2(2000);
755 l_object_version_number number;
756
757 BEGIN
758
759 -- check version compatibility
760 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
761 l_api_name, g_pkg_name)
762 THEN
763 RAISE fnd_api.g_exc_unexpected_error;
764 END IF;
765 -- initialize message stack if required
766 IF fnd_api.to_boolean(p_init_msg_list)
767 THEN
768 fnd_msg_pub.initialize;
769 END IF;
770 -- initialize return status
771 x_return_status := fnd_api.g_ret_sts_success;
772
773 OPEN C_ObjCap(p_object_capacity_id);
774 FETCH C_ObjCap
775 INTO l_object_version_number;
776 IF C_ObjCap%NOTFOUND
777 THEN
778 CLOSE C_ObjCap;
779 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
780 fnd_msg_pub.add;
781 RAISE fnd_api.g_exc_unexpected_error;
782 END IF;
783 CLOSE C_ObjCap;
784
785 -- check if record updated by another user
786 IF (NVL(p_object_version_number, -1) <> l_object_version_number)
787 THEN
788 fnd_message.set_name('FND', 'FND_RECORD_CHANGED_ERROR');
789 fnd_msg_pub.add;
790 RAISE fnd_api.g_exc_unexpected_error;
791 END IF;
792
793 -- check if task assignments need to be updated
794 IF fnd_api.to_boolean(p_Update_Tasks)
795 THEN
796 -- First get all the open task assignments and remove the object capacity
797 FOR ref_tasks IN c_tasks(p_object_capacity_id)
798 LOOP
799 -- Call assignments api to update object capacity id
800 JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT
801 (
802 p_api_version => 1.0,
803 p_commit => fnd_api.G_FALSE,
804 p_object_version_number => ref_tasks.OBJECT_VERSION_NUMBER,
805 p_task_assignment_id => ref_tasks.TASK_ASSIGNMENT_ID,
806 p_enable_workflow => NULL,
807 p_abort_workflow => NULL,
808 p_object_capacity_id => NULL,
809 x_return_status => l_return_status,
810 x_msg_count => l_msg_count,
811 x_msg_data => l_msg_data
812 );
813 IF l_return_status <> fnd_api.g_ret_sts_success
814 THEN
815 IF l_return_status = fnd_api.g_ret_sts_error
816 THEN
817 RAISE fnd_api.g_exc_error;
818 ELSE
819 RAISE fnd_api.g_exc_unexpected_error;
820 END IF;
821 END IF;
822 END LOOP;
823 END IF;
824
825 -- call the table handler to delete data
826 CAC_SR_OBJECT_CAPACITY_PKG.DELETE_ROW
827 (
828 X_OBJECT_CAPACITY_ID => p_object_capacity_id
829 );
830
831 fnd_msg_pub.count_and_get( p_encoded => 'F'
832 , p_count => x_msg_count
833 , p_data => x_msg_data
834 );
835 EXCEPTION
836
837 WHEN fnd_api.g_exc_error THEN
838 x_return_status := fnd_api.g_ret_sts_error;
839 fnd_msg_pub.count_and_get( p_encoded => 'F'
840 , p_count => x_msg_count
841 , p_data => x_msg_data
842 );
843
844 WHEN fnd_api.g_exc_unexpected_error THEN
845 x_return_status := fnd_api.g_ret_sts_unexp_error;
846 fnd_msg_pub.count_and_get( p_encoded => 'F'
847 , p_count => x_msg_count
848 , p_data => x_msg_data
849 );
850
851 WHEN OTHERS THEN
852 x_return_status := fnd_api.g_ret_sts_unexp_error;
853 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
854 THEN
855 fnd_msg_pub.add_exc_msg( g_pkg_name
856 , l_api_name
857 );
858 END IF;
859 fnd_msg_pub.count_and_get( p_encoded => 'F'
860 , p_count => x_msg_count
861 , p_data => x_msg_data
862 );
863
864 END delete_object_capacity;
865
866 PROCEDURE build_object_capacity
867 /*******************************************************************************
868 ** build_object_capacity
869 **
870 ** This API calls generate_object_capacity to get the object capacity records.
871 ** It then checks if there are overlapping data in the database for the same
872 ** period and then calls insert_object_capacity to insert data.
873 **
874 *******************************************************************************/
875 ( p_api_version IN NUMBER -- API version you coded against
876 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
877 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
878 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
879 , p_Start_Date_Time IN DATE -- start date and time of period of interest
880 , p_End_Date_Time IN DATE -- end date and time of period of interest
881 , p_Build_Mode IN VARCHAR2 -- operation mode of the build
882 -- 'ADD' - New object capacity records are generated and inserted
883 -- 'REPLACE' - Existing object capacity records are deleted and new ones are inserted
884 -- 'DELETE' - Existing object capacity records are deleted
885 , p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Should the existing task assignments be updated with the object capacity ids?
886 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
887 -- 'E': API completed with recoverable errors; explanation on errorstack
888 -- 'U': API completed with UN recoverable errors: error message on error stack
889 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
890 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
891 ) IS
892
893 l_api_name constant varchar2(30) := 'BUILD_OBJECT_CAPACITY';
894 l_api_version constant number := 1.0;
895
896 BEGIN
897
898 -- check version compatibility
899 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
900 l_api_name, g_pkg_name)
901 THEN
902 RAISE fnd_api.g_exc_unexpected_error;
903 END IF;
904 -- initialize message stack if required
905 IF fnd_api.to_boolean(p_init_msg_list)
906 THEN
907 fnd_msg_pub.initialize;
908 END IF;
909 -- initialize return status
910 x_return_status := fnd_api.g_ret_sts_success;
911
912 fnd_msg_pub.count_and_get( p_encoded => 'F'
913 , p_count => x_msg_count
914 , p_data => x_msg_data
915 );
916 EXCEPTION
917
918 WHEN fnd_api.g_exc_error THEN
919 x_return_status := fnd_api.g_ret_sts_error;
920 fnd_msg_pub.count_and_get( p_encoded => 'F'
921 , p_count => x_msg_count
922 , p_data => x_msg_data
923 );
924
925 WHEN fnd_api.g_exc_unexpected_error THEN
926 x_return_status := fnd_api.g_ret_sts_unexp_error;
927 fnd_msg_pub.count_and_get( p_encoded => 'F'
928 , p_count => x_msg_count
929 , p_data => x_msg_data
930 );
931
932 WHEN OTHERS THEN
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
935 THEN
936 fnd_msg_pub.add_exc_msg( g_pkg_name
937 , l_api_name
938 );
939 END IF;
940 fnd_msg_pub.count_and_get( p_encoded => 'F'
941 , p_count => x_msg_count
942 , p_data => x_msg_data
943 );
944
945 END build_object_capacity;
946
947
948 END CAC_SR_OBJECT_CAPACITY_PUB;
[Home] [Help]