1 PACKAGE BODY CAC_SR_OBJECT_CAPACITY_PUB AS
2 /* $Header: cacsrocpb.pls 120.1.12010000.4 2009/06/01 08:45:42 anangupt ship $ */
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 x_object_capacity(l_tbl_count).AVAILABILITY_TYPE := l_shift(l_idx).AVAILABILITY_TYPE;
217
218 l_idx := l_shift.NEXT(l_idx);
219 END LOOP;
220
221 -- Fetch tasks now
222 l_tbl_count := 0;
223 l_idx := x_object_capacity.FIRST;
224 IF fnd_api.to_boolean(p_Fetch_Tasks)
225 THEN
226 -- to take care of any boundry condition fetch tasks with -+ 1 days of the start and end
227 FOR ref_tasks IN c_tasks(p_object_type,p_object_id,p_start_date-1,p_end_date+1)
228 LOOP
229 -- First step is to find if this task lies in any object capacity record
230 -- Note that the l_idx is not reset in the for loop. The reason being
231 -- since both the capacity table and ref_tasks are sorted the new task
232 -- fetched will always be either in the current capacity record or in
233 -- the ones which are after that
234 WHILE l_idx IS NOT NULL
235 LOOP
236 -- Either the task which ends in this capacity record or which starts
237 -- here are considered to be the ones connected to this shift.
238 IF ((ref_tasks.scheduled_start_date BETWEEN x_object_capacity(l_idx).START_DATE_TIME
239 AND x_object_capacity(l_idx).END_DATE_TIME) OR (ref_tasks.scheduled_end_date
240 BETWEEN x_object_capacity(l_idx).START_DATE_TIME AND x_object_capacity(l_idx).END_DATE_TIME))
241 THEN
242 -- This is the right one
243 l_tbl_count := l_tbl_count + 1;
244 -- Create a new entry in the object tasks table
245 x_Object_Tasks(l_tbl_count).OBJECT_CAPACITY_TBL_IDX := l_idx;
246 x_Object_Tasks(l_tbl_count).TASK_ASSIGNMENT_ID := ref_tasks.task_assignment_id;
247 x_Object_Tasks(l_tbl_count).TASK_ASSIGNMENT_OVN := ref_tasks.asn_ovn;
248 x_Object_Tasks(l_tbl_count).ASSIGNMENT_STATUS_ID := ref_tasks.assignment_status_id;
249 x_Object_Tasks(l_tbl_count).FREE_BUSY_TYPE := ref_tasks.FREE_BUSY_TYPE;
250 x_Object_Tasks(l_tbl_count).TASK_ID := ref_tasks.task_id;
251 x_Object_Tasks(l_tbl_count).TASK_OVN := ref_tasks.task_ovn;
252 x_Object_Tasks(l_tbl_count).TASK_TYPE_ID := ref_tasks.task_type_id;
253 x_Object_Tasks(l_tbl_count).TASK_STATUS_ID := ref_tasks.task_status_id;
254 x_Object_Tasks(l_tbl_count).SCHEDULED_START_DATE := ref_tasks.scheduled_start_date;
255 x_Object_Tasks(l_tbl_count).SCHEDULED_END_DATE := ref_tasks.scheduled_end_date;
256 x_Object_Tasks(l_tbl_count).PLANNED_START_DATE := ref_tasks.planned_start_date;
257 x_Object_Tasks(l_tbl_count).PLANNED_END_DATE := ref_tasks.planned_end_date;
258 x_Object_Tasks(l_tbl_count).CUSTOMER_ID := ref_tasks.customer_id;
259 x_Object_Tasks(l_tbl_count).ADDRESS_ID := ref_tasks.address_id;
260 -- Task record updated, so exit the object capacity loop
261 EXIT;
262 ELSE
263 -- now check if this task is before the current object capacity record
264 -- if it is then there is no need to loop through the object capacity
265 -- records since the next one would be even after the current one.
266 -- which means that the fetched task doesn't qualify in any object
267 -- capacity record and so should be ignored.
268 IF (ref_tasks.scheduled_end_date < x_object_capacity(l_idx).START_DATE_TIME)
269 THEN
270 EXIT; -- exit the loop
271 END IF;
272 -- if the task is after the current object capacity record then continue
273 -- with the loop as this task can be in the next one.
274 END IF;
275 -- go to the next one
276 l_idx := x_object_capacity.NEXT(l_idx);
277 END LOOP;
278 -- if we have already reached the end of object capacity table then there
279 -- is no need to continue fetching tasks, so exit
280 IF l_idx IS NULL
281 THEN
282 EXIT; -- exit the tasks loop
283 END IF;
284 END LOOP;
285 END IF;
286
287 fnd_msg_pub.count_and_get( p_encoded => 'F'
288 , p_count => x_msg_count
289 , p_data => x_msg_data
290 );
291 EXCEPTION
292
293 WHEN fnd_api.g_exc_error THEN
294 x_return_status := fnd_api.g_ret_sts_error;
295 fnd_msg_pub.count_and_get( p_encoded => 'F'
296 , p_count => x_msg_count
297 , p_data => x_msg_data
298 );
299
300 WHEN fnd_api.g_exc_unexpected_error THEN
301 x_return_status := fnd_api.g_ret_sts_unexp_error;
302 fnd_msg_pub.count_and_get( p_encoded => 'F'
303 , p_count => x_msg_count
304 , p_data => x_msg_data
305 );
306
307 WHEN OTHERS THEN
308 x_return_status := fnd_api.g_ret_sts_unexp_error;
309 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
310 THEN
311 fnd_msg_pub.add_exc_msg( g_pkg_name
312 , l_api_name
313 );
314 END IF;
315 fnd_msg_pub.count_and_get( p_encoded => 'F'
316 , p_count => x_msg_count
317 , p_data => x_msg_data
318 );
319
320 END generate_object_capacity;
321
322 PROCEDURE insert_object_capacity
323 /*******************************************************************************
324 ** insert_object_capacity
325 **
326 ** This API calls table handler to insert data into cac_sr_object_capacity
327 ** using pl/sql table passed. It populates object_capacity_id for each record
328 ** if with the sequence cac_sr_object_capacity_s value if it is NULL in the
329 ** record.
330 ** It updates the task assignment with the corresponding object_capacity_id
331 ** if the p_Update_Tasks parameter is set to 'T'.
332 **
333 *******************************************************************************/
334 ( p_api_version IN NUMBER -- API version you coded against
335 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
336 , p_Object_Capacity IN OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
337 -- table of object capacity records which should be inserte
338 , p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Update task assignments too?
339 , p_Object_Tasks IN OBJECT_TASKS_TBL_TYPE
340 -- table of object task records to be updated
341 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
342 -- 'E': API completed with recoverable errors; explanation on errorstack
343 -- 'U': API completed with UN recoverable errors: error message on error stack
344 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
345 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
346 ) IS
347
348 l_api_name constant varchar2(30) := 'INSERT_OBJECT_CAPACITY';
349 l_api_version constant number := 1.0;
350 l_idx number;
351 l_return_status varchar2(1);
352 l_msg_count number;
353 l_msg_data varchar2(2000);
354 l_current_date date;
355 l_user number;
356 l_login number;
357 l_rowid VARCHAR2(255);
358 l_ovn number;
359
360 BEGIN
361
362 -- check version compatibility
363 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
364 l_api_name, g_pkg_name)
365 THEN
366 RAISE fnd_api.g_exc_unexpected_error;
367 END IF;
368 -- initialize message stack if required
369 IF fnd_api.to_boolean(p_init_msg_list)
370 THEN
371 fnd_msg_pub.initialize;
372 END IF;
373 -- initialize return status
374 x_return_status := fnd_api.g_ret_sts_success;
375
376 l_current_date := SYSDATE;
377 l_user := FND_GLOBAL.USER_ID;
378 l_login := FND_GLOBAL.LOGIN_ID;
379
380 l_idx := p_Object_Capacity.FIRST;
381
382 WHILE l_idx IS NOT NULL
383 LOOP
384 -- validate if the record is correct
385 IF NOT validate_object_capacity(p_Object_Capacity(l_idx))
386 THEN
387 RAISE fnd_api.g_exc_unexpected_error;
388 END IF;
389 -- populate the primary key if needed
390 IF p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID IS NULL
391 THEN
392 p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID := get_new_object_capacity_id;
393 END IF;
394 IF p_Object_Capacity(l_idx).STATUS IS NULL
395 THEN
396 p_Object_Capacity(l_idx).STATUS := g_default_capacity_status;
397 END IF;
398 p_Object_Capacity(l_idx).OBJECT_VERSION_NUMBER := 1;
399
400 --veirfy if shift type is populated otherwise default it
401 IF( p_Object_Capacity(l_idx).availability_type IS NULL OR
402 p_Object_Capacity(l_idx).availability_type = '') THEN
403 p_Object_Capacity(l_idx).availability_type:=DEFAULT_AVAILABILITY_TYPE;
404 END IF;
405
406 -- call the table handler to insert data
407 CAC_SR_OBJECT_CAPACITY_PKG.INSERT_ROW
408 (
409 X_ROWID => l_rowid,
410 X_OBJECT_CAPACITY_ID => p_Object_Capacity(l_idx).OBJECT_CAPACITY_ID,
411 X_OBJECT_VERSION_NUMBER => p_Object_Capacity(l_idx).OBJECT_VERSION_NUMBER,
412 X_OBJECT_TYPE => p_Object_Capacity(l_idx).OBJECT_TYPE,
413 X_OBJECT_ID => p_Object_Capacity(l_idx).OBJECT_ID,
414 X_START_DATE_TIME => p_Object_Capacity(l_idx).START_DATE_TIME,
415 X_END_DATE_TIME => p_Object_Capacity(l_idx).END_DATE_TIME,
416 X_AVAILABLE_HOURS => p_Object_Capacity(l_idx).AVAILABLE_HOURS,
417 X_AVAILABLE_HOURS_BEFORE => p_Object_Capacity(l_idx).AVAILABLE_HOURS_BEFORE,
418 X_AVAILABLE_HOURS_AFTER => p_Object_Capacity(l_idx).AVAILABLE_HOURS_AFTER,
419 X_SCHEDULE_DETAIL_ID => p_Object_Capacity(l_idx).SCHEDULE_DETAIL_ID,
420 X_STATUS => p_Object_Capacity(l_idx).STATUS,
421 X_CREATION_DATE => l_current_date,
422 X_CREATED_BY => l_user,
423 X_LAST_UPDATE_DATE => l_current_date,
424 X_LAST_UPDATED_BY => l_user,
425 X_LAST_UPDATE_LOGIN => l_login,
426 X_AVAILABILITY_TYPE => p_Object_Capacity(l_idx).AVAILABILITY_TYPE,
427 X_SOURCE_TYPE => p_Object_Capacity(l_idx).SOURCE_TYPE
428 );
429 l_idx := p_Object_Capacity.NEXT(l_idx);
430 END LOOP;
431
432 -- check if task assignments need to be updated
433 IF fnd_api.to_boolean(p_Update_Tasks)
434 THEN
435 l_idx := p_Object_Tasks.FIRST;
436 WHILE l_idx IS NOT NULL
437 LOOP
438 -- find out if there is an object capacity id for this task assignment
439 IF ((p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX IS NOT NULL)
440 AND p_Object_Capacity.EXISTS(p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX))
441 THEN
442 -- Call assignments api to update object capacity id
443 l_ovn := p_Object_Tasks(l_idx).TASK_ASSIGNMENT_OVN;
444 JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT
445 (
446 p_api_version => 1.0,
447 p_commit => fnd_api.G_FALSE,
448 p_object_version_number => l_ovn,
449 p_task_assignment_id => p_Object_Tasks(l_idx).TASK_ASSIGNMENT_ID,
450 p_enable_workflow => NULL,
451 p_abort_workflow => NULL,
452 p_object_capacity_id => p_Object_Capacity(p_Object_Tasks(l_idx).OBJECT_CAPACITY_TBL_IDX).OBJECT_CAPACITY_ID,
453 x_return_status => l_return_status,
454 x_msg_count => l_msg_count,
455 x_msg_data => l_msg_data
456 );
457 IF l_return_status <> fnd_api.g_ret_sts_success
458 THEN
459 IF l_return_status = fnd_api.g_ret_sts_error
460 THEN
461 RAISE fnd_api.g_exc_error;
462 ELSE
463 RAISE fnd_api.g_exc_unexpected_error;
464 END IF;
465 END IF;
466 END IF;
467 l_idx := p_Object_Tasks.NEXT(l_idx);
468 END LOOP;
469 END IF;
470
471 fnd_msg_pub.count_and_get( p_encoded => 'F'
472 , p_count => x_msg_count
473 , p_data => x_msg_data
474 );
475 EXCEPTION
476
477 WHEN fnd_api.g_exc_error THEN
478 x_return_status := fnd_api.g_ret_sts_error;
479 fnd_msg_pub.count_and_get( p_encoded => 'F'
480 , p_count => x_msg_count
481 , p_data => x_msg_data
482 );
483
484 WHEN fnd_api.g_exc_unexpected_error THEN
485 x_return_status := fnd_api.g_ret_sts_unexp_error;
486 fnd_msg_pub.count_and_get( p_encoded => 'F'
487 , p_count => x_msg_count
488 , p_data => x_msg_data
489 );
490
491 WHEN OTHERS THEN
492 x_return_status := fnd_api.g_ret_sts_unexp_error;
493 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
494 THEN
495 fnd_msg_pub.add_exc_msg( g_pkg_name
496 , l_api_name
497 );
498 END IF;
499 fnd_msg_pub.count_and_get( p_encoded => 'F'
500 , p_count => x_msg_count
501 , p_data => x_msg_data
502 );
503
504 END insert_object_capacity;
505
506 PROCEDURE update_object_capacity
507 /*******************************************************************************
508 ** update_object_capacity New version
509 **
510 ** This API calls table handler to update data into cac_sr_object_capacity.
511 ** Only the available hours fields and status can be updated.
512 ** This version include updation of shift type also.
513 *******************************************************************************/
514 ( p_api_version IN NUMBER -- API version you coded against
515 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
516 , p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
517 , p_object_version_number IN NUMBER -- Object Version of the row to be updated
518 -- If this doesn't match the database value then that means someone else has updated the same row
519 , p_available_hours IN NUMBER DEFAULT NULL -- The new value of available hours
520 -- If it is NULL then no change is done to the existing data
521 , p_available_hours_before IN NUMBER DEFAULT NULL -- The new value of available before hours.
522 -- If it is NULL then no change is done to the existing data
523 -- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
524 , p_available_hours_after IN NUMBER DEFAULT NULL -- The new value of available before hours.
525 -- If it is NULL then no change is done to the existing data
526 -- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
527 , p_status IN NUMBER DEFAULT NULL -- The new value of the status
528 -- If it is NULL then no change is done to the existing data
529 , p_availability_type IN VARCHAR2 DEFAULT NULL -- The new value of availability_type
530 --If it is NULL then no change is done to the existing data
531 , p_start_date_time IN DATE DEFAULT NULL -- New value for start date time
532 -- if this value is NULL then no change is done to the existing
533 -- value
534 , p_end_date_time IN DATE DEFAULT NULL -- New value for end date time
535 -- if this value is NULL then no change is done to the existing
536 -- value
537 , p_source_type IN VARCHAR2 DEFAULT NULL -- new value for source type of trip
538 -- If NULL is passed for this value then no change is done
539 -- to exsiting value
540 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
541 -- 'E': API completed with recoverable errors; explanation on errorstack
542 -- 'U': API completed with UN recoverable errors: error message on error stack
543 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
544 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
545 ) IS
546
547 CURSOR C_ObjCap
548 (
549 b_object_capacity_id NUMBER
550 ) IS SELECT
551 OBJECT_VERSION_NUMBER,
552 OBJECT_TYPE,
553 OBJECT_ID,
554 START_DATE_TIME,
555 END_DATE_TIME,
556 AVAILABLE_HOURS,
557 AVAILABLE_HOURS_BEFORE,
558 AVAILABLE_HOURS_AFTER,
559 SCHEDULE_DETAIL_ID,
560 STATUS,
561 AVAILABILITY_TYPE,
562 SOURCE_TYPE
563 FROM CAC_SR_OBJECT_CAPACITY
564 WHERE OBJECT_CAPACITY_ID = b_object_capacity_id
565 FOR UPDATE OF OBJECT_CAPACITY_ID NOWAIT;
566
567 l_api_name constant varchar2(30) := 'UPDATE_OBJECT_CAPACITY';
568 l_api_version constant number := 1.0;
569 l_current_date date;
570 l_user number;
571 l_login number;
572 l_Object_Capacity OBJECT_CAPACITY_REC_TYPE;
573
574 BEGIN
575
576 -- check version compatibility
577 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
578 l_api_name, g_pkg_name)
579 THEN
580 RAISE fnd_api.g_exc_unexpected_error;
581 END IF;
582 -- initialize message stack if required
583 IF fnd_api.to_boolean(p_init_msg_list)
584 THEN
585 fnd_msg_pub.initialize;
586 END IF;
587 -- initialize return status
588 x_return_status := fnd_api.g_ret_sts_success;
589
590 l_Object_Capacity.OBJECT_CAPACITY_ID := p_object_capacity_id;
591
592 OPEN C_ObjCap(l_Object_Capacity.OBJECT_CAPACITY_ID);
593 FETCH C_ObjCap
594 INTO l_Object_Capacity.OBJECT_VERSION_NUMBER,
595 l_Object_Capacity.OBJECT_TYPE,
596 l_Object_Capacity.OBJECT_ID,
597 l_Object_Capacity.START_DATE_TIME,
598 l_Object_Capacity.END_DATE_TIME,
599 l_Object_Capacity.AVAILABLE_HOURS,
600 l_Object_Capacity.AVAILABLE_HOURS_BEFORE,
601 l_Object_Capacity.AVAILABLE_HOURS_AFTER,
602 l_Object_Capacity.SCHEDULE_DETAIL_ID,
603 l_Object_Capacity.STATUS,
604 l_Object_Capacity.AVAILABILITY_TYPE,
605 l_Object_Capacity.SOURCE_TYPE;
606 IF C_ObjCap%NOTFOUND
607 THEN
608 CLOSE C_ObjCap;
609 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
610 fnd_msg_pub.add;
611 RAISE fnd_api.g_exc_unexpected_error;
612 END IF;
613 CLOSE C_ObjCap;
614
615 -- check if record updated by another user
616 IF (NVL(p_object_version_number, -1) <>
617 l_Object_Capacity.OBJECT_VERSION_NUMBER)
618 THEN
619 fnd_message.set_name('FND', 'FND_RECORD_CHANGED_ERROR');
620 fnd_msg_pub.add;
621 RAISE fnd_api.g_exc_unexpected_error;
622 END IF;
623
624 -- Copy the changed values
625
626 -- Available hours is mandatory field, so update only if a new valid
627 -- value is passed.
628 IF ((p_available_hours IS NULL) OR (p_available_hours = FND_API.G_MISS_NUM))
629 THEN
630 NULL;
631 ELSE
632 l_Object_Capacity.AVAILABLE_HOURS := p_available_hours;
633 END IF;
634
635 -- Available hours before can be set to NULL or a new value
636 IF (p_available_hours_before IS NULL)
637 THEN
638 NULL;
639 ELSIF (p_available_hours_before = FND_API.G_MISS_NUM)
640 THEN
641 l_Object_Capacity.AVAILABLE_HOURS_BEFORE := NULL;
642 ELSE
643 l_Object_Capacity.AVAILABLE_HOURS_BEFORE := p_available_hours_before;
644 END IF;
645
646 -- Available hours after can be set to NULL or a new value
647 IF (p_available_hours_after IS NULL)
648 THEN
649 NULL;
650 ELSIF (p_available_hours_after = FND_API.G_MISS_NUM)
651 THEN
652 l_Object_Capacity.AVAILABLE_HOURS_AFTER := NULL;
653 ELSE
654 l_Object_Capacity.AVAILABLE_HOURS_AFTER := p_available_hours_after;
655 END IF;
656
657 -- Status is mandatory field, so update only if a new valid value is passed.
658 IF ((p_status IS NULL) OR (p_status = FND_API.G_MISS_NUM))
659 THEN
660 NULL;
661 ELSE
662 l_Object_Capacity.STATUS := p_status;
663 END IF;
664
665 -- Shift type is mandatory field, so update only if a new valid value is passed.
666 IF ((p_availability_type IS NULL) OR (p_availability_type = ''))
667 THEN
668 NULL;
669 ELSE
670 l_Object_Capacity.AVAILABILITY_TYPE := p_availability_type;
671 END IF;
672
673 -- start date time is mandatory field, so update only if a new valid value is passed.
674 IF ((p_start_date_time IS NULL) OR (p_start_date_time = FND_API.G_MISS_DATE))
675 THEN
676 NULL;
677 ELSE
678 l_Object_Capacity.START_DATE_TIME := p_start_date_time;
679 END IF;
680
681 -- end date time is mandatory field, so update only if a new valid value is passed.
682 IF ((p_end_date_time IS NULL) OR (p_end_date_time = FND_API.G_MISS_DATE))
683 THEN
684 NULL;
685 ELSE
686 l_Object_Capacity.END_DATE_TIME := p_end_date_time;
687 END IF;
688
689 -- for source type field update only if a new valid value is passed.
690 IF ((p_source_type IS NULL) OR (p_source_type = FND_API.G_MISS_CHAR))
691 THEN
692 NULL;
693 ELSE
694 l_Object_Capacity.SOURCE_TYPE := p_source_type;
695 END IF;
696
697 -- Now increment the object version number by one
698 l_Object_Capacity.OBJECT_VERSION_NUMBER := l_Object_Capacity.OBJECT_VERSION_NUMBER + 1;
699
700 l_current_date := SYSDATE;
701 l_user := FND_GLOBAL.USER_ID;
702 l_login := FND_GLOBAL.LOGIN_ID;
703
704 -- call the table handler to update data
705 CAC_SR_OBJECT_CAPACITY_PKG.UPDATE_ROW
706 (
707 X_OBJECT_CAPACITY_ID => l_Object_Capacity.OBJECT_CAPACITY_ID,
708 X_OBJECT_VERSION_NUMBER => l_Object_Capacity.OBJECT_VERSION_NUMBER,
709 X_OBJECT_TYPE => l_Object_Capacity.OBJECT_TYPE,
710 X_OBJECT_ID => l_Object_Capacity.OBJECT_ID,
711 X_START_DATE_TIME => l_Object_Capacity.START_DATE_TIME,
712 X_END_DATE_TIME => l_Object_Capacity.END_DATE_TIME,
713 X_AVAILABLE_HOURS => l_Object_Capacity.AVAILABLE_HOURS,
714 X_AVAILABLE_HOURS_BEFORE => l_Object_Capacity.AVAILABLE_HOURS_BEFORE,
715 X_AVAILABLE_HOURS_AFTER => l_Object_Capacity.AVAILABLE_HOURS_AFTER,
716 X_SCHEDULE_DETAIL_ID => l_Object_Capacity.SCHEDULE_DETAIL_ID,
717 X_STATUS => l_Object_Capacity.STATUS,
718 X_AVAILABILITY_TYPE => l_Object_Capacity.AVAILABILITY_TYPE,
719 X_SOURCE_TYPE => l_Object_Capacity.SOURCE_TYPE,
720 X_CREATION_DATE => l_current_date,
721 X_CREATED_BY => l_user,
722 X_LAST_UPDATE_DATE => l_current_date,
723 X_LAST_UPDATED_BY => l_user,
724 X_LAST_UPDATE_LOGIN => l_login
725 );
726
727 fnd_msg_pub.count_and_get( p_encoded => 'F'
728 , p_count => x_msg_count
729 , p_data => x_msg_data
730 );
731 EXCEPTION
732
733 WHEN fnd_api.g_exc_error THEN
734 x_return_status := fnd_api.g_ret_sts_error;
735 fnd_msg_pub.count_and_get( p_encoded => 'F'
736 , p_count => x_msg_count
737 , p_data => x_msg_data
738 );
739
740 WHEN fnd_api.g_exc_unexpected_error THEN
741 x_return_status := fnd_api.g_ret_sts_unexp_error;
742 fnd_msg_pub.count_and_get( p_encoded => 'F'
743 , p_count => x_msg_count
744 , p_data => x_msg_data
745 );
746
747 WHEN OTHERS THEN
748 x_return_status := fnd_api.g_ret_sts_unexp_error;
749 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
750 THEN
751 fnd_msg_pub.add_exc_msg( g_pkg_name
752 , l_api_name
753 );
754 END IF;
755 fnd_msg_pub.count_and_get( p_encoded => 'F'
756 , p_count => x_msg_count
757 , p_data => x_msg_data
758 );
759
760 END update_object_capacity;
761
762 PROCEDURE delete_object_capacity
763 /*******************************************************************************
764 ** delete_object_capacity
765 **
766 ** This API calls table handler to delete data from cac_sr_object_capacity.
767 ** It will also update the task assignments and remove the object capacity id
768 ** if the p_update_tasks is true
769 **
770 *******************************************************************************/
771 ( p_api_version IN NUMBER -- API version you coded against
772 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
773 , p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
774 , p_object_version_number IN NUMBER -- Object Version of the row to be updated
775 -- If this doesn't match the database value then that means someone else has updated the same row
776 , p_Update_Tasks IN VARCHAR2 DEFAULT 'T' -- Update task assignments too?
777 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
778 -- 'E': API completed with recoverable errors; explanation on errorstack
779 -- 'U': API completed with UN recoverable errors: error message on error stack
780 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
781 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
782 ) IS
783
784 CURSOR C_ObjCap
785 (
786 b_object_capacity_id NUMBER
787 ) IS SELECT
788 OBJECT_VERSION_NUMBER
789 FROM CAC_SR_OBJECT_CAPACITY
790 WHERE OBJECT_CAPACITY_ID = b_object_capacity_id
791 FOR UPDATE OF OBJECT_CAPACITY_ID NOWAIT;
792
793 CURSOR c_tasks
794 (
795 b_object_capacity_id number
796 )
797 is
798 select jta.task_assignment_id
799 , jta.object_version_number
800 from jtf_task_statuses_b jtsb
801 , jtf_task_assignments jta
802 , jtf_tasks_b jtb
803 where jtsb.task_status_id = jta.assignment_status_id
804 and jta.object_capacity_id = b_object_capacity_id
805 and jta.task_id = jtb.task_id
806 and nvl(jtb.deleted_flag, 'N') = 'N'
807 order by jtb.scheduled_start_date;
808
809 l_api_name constant varchar2(30) := 'DELETE_OBJECT_CAPACITY';
810 l_api_version constant number := 1.0;
811 l_return_status varchar2(1);
812 l_msg_count number;
813 l_msg_data varchar2(2000);
814 l_object_version_number number;
815
816 BEGIN
817
818 -- check version compatibility
819 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
820 l_api_name, g_pkg_name)
821 THEN
822 RAISE fnd_api.g_exc_unexpected_error;
823 END IF;
824 -- initialize message stack if required
825 IF fnd_api.to_boolean(p_init_msg_list)
826 THEN
827 fnd_msg_pub.initialize;
828 END IF;
829 -- initialize return status
830 x_return_status := fnd_api.g_ret_sts_success;
831
832 OPEN C_ObjCap(p_object_capacity_id);
833 FETCH C_ObjCap
834 INTO l_object_version_number;
835 IF C_ObjCap%NOTFOUND
836 THEN
837 CLOSE C_ObjCap;
838 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
839 fnd_msg_pub.add;
840 RAISE fnd_api.g_exc_unexpected_error;
841 END IF;
842 CLOSE C_ObjCap;
843
844 -- check if record updated by another user
845 IF (NVL(p_object_version_number, -1) <> l_object_version_number)
846 THEN
847 fnd_message.set_name('FND', 'FND_RECORD_CHANGED_ERROR');
848 fnd_msg_pub.add;
849 RAISE fnd_api.g_exc_unexpected_error;
850 END IF;
851
852 -- check if task assignments need to be updated
853 IF fnd_api.to_boolean(p_Update_Tasks)
854 THEN
855 -- First get all the open task assignments and remove the object capacity
856 FOR ref_tasks IN c_tasks(p_object_capacity_id)
857 LOOP
858 -- Call assignments api to update object capacity id
859 JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT
860 (
861 p_api_version => 1.0,
862 p_commit => fnd_api.G_FALSE,
863 p_object_version_number => ref_tasks.OBJECT_VERSION_NUMBER,
864 p_task_assignment_id => ref_tasks.TASK_ASSIGNMENT_ID,
865 p_enable_workflow => NULL,
866 p_abort_workflow => NULL,
867 p_object_capacity_id => NULL,
868 x_return_status => l_return_status,
869 x_msg_count => l_msg_count,
870 x_msg_data => l_msg_data
871 );
872 IF l_return_status <> fnd_api.g_ret_sts_success
873 THEN
874 IF l_return_status = fnd_api.g_ret_sts_error
875 THEN
876 RAISE fnd_api.g_exc_error;
877 ELSE
878 RAISE fnd_api.g_exc_unexpected_error;
879 END IF;
880 END IF;
881 END LOOP;
882 END IF;
883
884 -- call the table handler to delete data
885 CAC_SR_OBJECT_CAPACITY_PKG.DELETE_ROW
886 (
887 X_OBJECT_CAPACITY_ID => p_object_capacity_id
888 );
889
890 fnd_msg_pub.count_and_get( p_encoded => 'F'
891 , p_count => x_msg_count
892 , p_data => x_msg_data
893 );
894 EXCEPTION
895
896 WHEN fnd_api.g_exc_error THEN
897 x_return_status := fnd_api.g_ret_sts_error;
898 fnd_msg_pub.count_and_get( p_encoded => 'F'
899 , p_count => x_msg_count
900 , p_data => x_msg_data
901 );
902
903 WHEN fnd_api.g_exc_unexpected_error THEN
904 x_return_status := fnd_api.g_ret_sts_unexp_error;
905 fnd_msg_pub.count_and_get( p_encoded => 'F'
906 , p_count => x_msg_count
907 , p_data => x_msg_data
908 );
909
910 WHEN OTHERS THEN
911 x_return_status := fnd_api.g_ret_sts_unexp_error;
912 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
913 THEN
914 fnd_msg_pub.add_exc_msg( g_pkg_name
915 , l_api_name
916 );
917 END IF;
918 fnd_msg_pub.count_and_get( p_encoded => 'F'
919 , p_count => x_msg_count
920 , p_data => x_msg_data
921 );
922
923 END delete_object_capacity;
924
925 PROCEDURE build_object_capacity
926 /*******************************************************************************
927 ** build_object_capacity
928 **
929 ** This API calls generate_object_capacity to get the object capacity records.
930 ** It then checks if there are overlapping data in the database for the same
931 ** period and then calls insert_object_capacity to insert data.
932 **
933 *******************************************************************************/
934 ( p_api_version IN NUMBER -- API version you coded against
935 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
936 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
937 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
938 , p_Start_Date_Time IN DATE -- start date and time of period of interest
939 , p_End_Date_Time IN DATE -- end date and time of period of interest
940 , p_Build_Mode IN VARCHAR2 -- operation mode of the build
941 -- 'ADD' - New object capacity records are generated and inserted
942 -- 'REPLACE' - Existing object capacity records are deleted and new ones are inserted
943 -- 'DELETE' - Existing object capacity records are deleted
944 , p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Should the existing task assignments be updated with the object capacity ids?
945 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
946 -- 'E': API completed with recoverable errors; explanation on errorstack
947 -- 'U': API completed with UN recoverable errors: error message on error stack
948 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
949 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
950 ) IS
951
952 l_api_name constant varchar2(30) := 'BUILD_OBJECT_CAPACITY';
953 l_api_version constant number := 1.0;
954
955 BEGIN
956
957 -- check version compatibility
958 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
959 l_api_name, g_pkg_name)
960 THEN
961 RAISE fnd_api.g_exc_unexpected_error;
962 END IF;
963 -- initialize message stack if required
964 IF fnd_api.to_boolean(p_init_msg_list)
965 THEN
966 fnd_msg_pub.initialize;
967 END IF;
968 -- initialize return status
969 x_return_status := fnd_api.g_ret_sts_success;
970
971 fnd_msg_pub.count_and_get( p_encoded => 'F'
972 , p_count => x_msg_count
973 , p_data => x_msg_data
974 );
975 EXCEPTION
976
977 WHEN fnd_api.g_exc_error THEN
978 x_return_status := fnd_api.g_ret_sts_error;
979 fnd_msg_pub.count_and_get( p_encoded => 'F'
980 , p_count => x_msg_count
981 , p_data => x_msg_data
982 );
983
984 WHEN fnd_api.g_exc_unexpected_error THEN
985 x_return_status := fnd_api.g_ret_sts_unexp_error;
986 fnd_msg_pub.count_and_get( p_encoded => 'F'
987 , p_count => x_msg_count
988 , p_data => x_msg_data
989 );
990
991 WHEN OTHERS THEN
992 x_return_status := fnd_api.g_ret_sts_unexp_error;
993 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
994 THEN
995 fnd_msg_pub.add_exc_msg( g_pkg_name
996 , l_api_name
997 );
998 END IF;
999 fnd_msg_pub.count_and_get( p_encoded => 'F'
1000 , p_count => x_msg_count
1001 , p_data => x_msg_data
1002 );
1003
1004 END build_object_capacity;
1005
1006
1007 END CAC_SR_OBJECT_CAPACITY_PUB;
[Home] [Help]