1 PACKAGE BODY JTF_TASK_ASSIGNMENT_AUDIT_PKG AS
2 /*$Header: jtftkaub.pls 120.0.12010000.5 2010/03/31 12:02:19 anangupt noship $*/
3
4 /**
5 * Procedure to accept call for creation of audit record for change in
6 * task assignment. This procedure validates if the update IS actual
7 * update or a dummy update by comparing values passed with the values
8 * stored for the given assignment.This procedure inturn calls
9 * INSERT_ROW() procedure to create row in database.
10 */
11
12 PROCEDURE create_task_assignment_audit (
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
18 p_task_assignment_id IN NUMBER,
15 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
16 p_object_version_number IN NUMBER,
17 p_task_id IN NUMBER,
19 p_new_resource_type_code IN VARCHAR2 DEFAULT NULL,
20 p_new_resource_id IN NUMBER DEFAULT NULL,
21 p_new_assignment_status IN NUMBER DEFAULT NULL,
22 p_new_actual_effort IN NUMBER DEFAULT NULL,
23 p_new_actual_effort_uom IN VARCHAR2 DEFAULT NULL,
24 p_new_res_territory_id IN NUMBER DEFAULT NULL,
25 p_new_assignee_role IN VARCHAR2 DEFAULT NULL,
26 p_new_schedule_flag IN VARCHAR2 DEFAULT NULL,
27 p_new_alarm_type IN VARCHAR2 DEFAULT NULL,
28 p_new_alarm_contact IN VARCHAR2 DEFAULT NULL,
29 p_new_update_status_flag IN VARCHAR2 DEFAULT NULL,
30 p_new_show_on_cal_flag IN VARCHAR2 DEFAULT NULL,
31 p_new_category_id IN NUMBER DEFAULT NULL,
32 p_new_free_busy_type IN VARCHAR2 DEFAULT NULL,
33 p_new_booking_start_date IN DATE DEFAULT NULL,
34 p_new_booking_end_date IN DATE DEFAULT NULL,
35 p_new_actual_travel_distance IN NUMBER DEFAULT NULL,
36 p_new_actual_travel_duration IN NUMBER DEFAULT NULL,
37 p_new_actual_travel_dur_uom IN VARCHAR2 DEFAULT NULL,
38 p_new_sched_travel_distance IN NUMBER DEFAULT NULL,
39 p_new_sched_travel_duration IN NUMBER DEFAULT NULL,
40 p_new_sched_travel_dur_uom IN VARCHAR2 DEFAULT NULL,
41 p_new_actual_start_date IN DATE DEFAULT NULL,
42 p_new_actual_end_date IN DATE DEFAULT NULL,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2
46 )
47 IS
48 l_api_name CONSTANT VARCHAR2(30) := 'JTF_TASK_ASSIGNMENT_AUDIT_PKG';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_init_msg_list VARCHAR2(10) := fnd_api.g_false;
51 l_commit VARCHAR2(10) := fnd_api.g_false;
52 l_old_resource_type_code VARCHAR2(30);
53 l_old_resource_id NUMBER;
54 l_old_assignment_status NUMBER;
55 l_old_actual_effort NUMBER;
56 l_old_actual_effort_uom VARCHAR2(3);
57 l_old_res_territory_id NUMBER;
58 l_old_assignee_role VARCHAR2(30);
59 l_old_schedule_flag VARCHAR2(1);
60 l_old_alarm_type VARCHAR2(30);
61 l_old_alarm_contact VARCHAR2(200);
62 l_old_update_status_flag VARCHAR2(1);
63 l_old_show_on_cal_flag VARCHAR2(1);
64 l_old_category_id NUMBER;
65 l_old_free_busy_type VARCHAR2(100);
66 l_old_booking_start_date DATE;
67 l_old_booking_end_date DATE;
68 l_old_actual_travel_distance NUMBER;
69 l_old_actual_travel_duration NUMBER;
70 l_old_actual_travel_dur_uom VARCHAR2(3);
71 l_old_sched_travel_distance NUMBER;
72 l_old_sched_travel_duration NUMBER;
73 l_old_sched_travel_dur_uom VARCHAR2(3);
74 l_old_actual_start_date DATE;
75 l_old_actual_end_date DATE;
76 l_resource_type_code_changed VARCHAR2(1) :='N';
77 l_resource_id_changed VARCHAR2(1) :='N';
78 l_assignment_status_changed VARCHAR2(1) :='N';
79 l_actual_effort_changed VARCHAR2(1) :='N';
80 l_actual_effort_uom_changed VARCHAR2(1) :='N';
81 l_res_territory_id_changed VARCHAR2(1) :='N';
82 l_assignee_role_changed VARCHAR2(1) :='N';
83 l_schedule_flag_changed VARCHAR2(1) :='N';
84 l_alarm_type_changed VARCHAR2(1) :='N';
85 l_alarm_contact_changed VARCHAR2(1) :='N';
86 l_update_status_flag_changed VARCHAR2(1) :='N';
87 l_show_on_cal_flag_changed VARCHAR2(1) :='N';
88 l_category_id_changed VARCHAR2(1) :='N';
89 l_free_busy_type_changed VARCHAR2(1) :='N';
90 l_booking_start_date_changed VARCHAR2(1) :='N';
91 l_booking_end_date_changed VARCHAR2(1) :='N';
92 l_actual_travel_dist_changed VARCHAR2(1) :='N';
93 l_actual_travel_dur_changed VARCHAR2(1) :='N';
94 l_actual_travel_uom_changed VARCHAR2(1) :='N';
95 l_sched_travel_dist_changed VARCHAR2(1) :='N';
96 l_sched_travel_dur_changed VARCHAR2(1) :='N';
97 l_sched_travel_uom_changed VARCHAR2(1) :='N';
98 l_actual_start_date_changed VARCHAR2(1) :='N';
99 l_actual_end_date_changed VARCHAR2(1) :='N';
100 x NUMBER ;
101 l_asg_create NUMBER;
102
103 CURSOR cur_asg_audit (p_task_assignment_id IN NUMBER)
104 IS
105 SELECT task_id
106 , resource_type_code
107 , resource_id
108 , assignment_status_id
109 , actual_effort
110 , actual_effort_uom
111 , resource_territory_id
112 , assignee_role
113 , schedule_flag
114 , alarm_type_code
115 , alarm_contact
116 , update_status_flag
117 , show_on_calendar
118 , category_id
119 , free_busy_type
120 , booking_start_date
121 , booking_end_date
122 , actual_travel_distance
123 , actual_travel_duration
124 , actual_travel_duration_uom
125 , sched_travel_distance
126 , sched_travel_duration
127 , sched_travel_duration_uom
128 , actual_start_date
129 , actual_end_date
130 , trim(object_version_number) as object_version_number
131 FROM jtf_task_all_assignments
132 WHERE task_assignment_id = p_task_assignment_id;
133
134 CURSOR c1 (l_asg_audit_id IN NUMBER)
135 IS
136 SELECT 1
137 FROM jtf_task_assignments_audit_b
138 WHERE Assignment_audit_id = l_asg_audit_id;
139
140 audit_rec cur_asg_audit%ROWTYPE;
144 SAVEPOINT create_asg_audit_pvt;
141 l_new_category_id NUMBER := p_new_category_id ;
142 l_curr NUMBER;
143 BEGIN
145
146 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
147 THEN
148 RAISE fnd_api.g_exc_unexpected_error;
149 END IF;
150
151 IF fnd_api.to_boolean (p_init_msg_list)
152 THEN
153 fnd_msg_pub.initialize;
154 END IF;
155
156 x_return_status := fnd_api.g_ret_sts_success;
157
158 OPEN cur_asg_audit (p_task_assignment_id);
159
160 FETCH cur_asg_audit INTO audit_rec;
161 IF(cur_asg_audit%notfound) then
162 l_old_resource_type_code := NULL;
163 l_old_resource_id := NULL;
164 l_old_assignment_status := NULL;
165 l_old_actual_effort := NULL;
166 l_old_actual_effort_uom := NULL;
167 l_old_res_territory_id := NULL;
168 l_old_assignee_role := NULL;
169 l_old_schedule_flag := NULL;
170 l_old_alarm_type := NULL;
171 l_old_alarm_contact := NULL;
172 l_old_update_status_flag := NULL;
173 l_old_show_on_cal_flag := NULL;
174 l_old_category_id := NULL;
175 l_old_free_busy_type := NULL;
176 l_old_booking_start_date := NULL;
177 l_old_booking_end_date := NULL;
178 l_old_actual_travel_distance := NULL;
179 l_old_actual_travel_duration := NULL;
180 l_old_actual_travel_dur_uom := NULL;
181 l_old_sched_travel_distance := NULL;
182 l_old_sched_travel_duration := NULL;
183 l_old_sched_travel_dur_uom := NULL;
184 l_old_actual_start_date := NULL;
185 l_old_actual_end_date := NULL;
186 l_asg_create := 0;
187 ELSE
188 l_old_resource_type_code := audit_rec.resource_type_code;
189 l_old_resource_id := audit_rec.resource_id;
190 l_old_assignment_status := audit_rec.assignment_status_id;
191 l_old_actual_effort := audit_rec.actual_effort;
192 l_old_actual_effort_uom := audit_rec.actual_effort_uom;
193 l_old_res_territory_id := audit_rec.resource_territory_id;
194 l_old_assignee_role := audit_rec.assignee_role;
195 l_old_schedule_flag := audit_rec.schedule_flag;
196 l_old_alarm_type := audit_rec.alarm_type_code;
197 l_old_alarm_contact := audit_rec.alarm_contact;
198 l_old_update_status_flag := audit_rec.update_status_flag;
199 l_old_show_on_cal_flag := audit_rec.show_on_calendar;
200 l_old_category_id := audit_rec.category_id;
201 l_old_free_busy_type := audit_rec.free_busy_type;
202 l_old_booking_start_date := audit_rec.booking_start_date;
203 l_old_booking_end_date := audit_rec.booking_end_date;
204 l_old_actual_travel_distance := audit_rec.actual_travel_distance;
205 l_old_actual_travel_duration := audit_rec.actual_travel_duration;
206 l_old_actual_travel_dur_uom := audit_rec.actual_travel_duration_UOM;
207 l_old_sched_travel_distance := audit_rec.sched_travel_distance;
208 l_old_sched_travel_duration := audit_rec.sched_travel_duration;
209 l_old_sched_travel_dur_uom := audit_rec.sched_travel_duration_UOM;
210 l_old_actual_start_date := audit_rec.actual_start_date;
211 l_old_actual_end_date := audit_rec.actual_end_date;
212 l_asg_create := 1;
213 END IF;
214 CLOSE cur_asg_audit;
215
216 IF (p_new_category_id = fnd_api.g_miss_num)
217 THEN
218 l_new_category_id:=NULL;
219 END IF;
220
221 IF ( (p_new_resource_type_code IS NULL AND l_old_resource_type_code IS NOT NULL)
222 OR (p_new_resource_type_code IS NOT NULL AND l_old_resource_type_code IS NULL)
223 OR (p_new_resource_type_code IS NOT NULL AND l_old_resource_type_code IS NOT NULL
224 AND p_new_resource_type_code <> l_old_resource_type_code))
225 THEN
226 l_resource_type_code_changed:='Y';
227 END IF;
228
229 IF ( (p_new_resource_id IS NULL AND l_old_resource_id IS NOT NULL)
230 OR (p_new_resource_id IS NOT NULL AND l_old_resource_id IS NULL)
231 OR (p_new_resource_id IS NOT NULL AND l_old_resource_id IS NOT NULL
232 AND p_new_resource_id <> l_old_resource_id))
233 THEN
234 l_resource_id_changed:='Y';
235 END IF;
236
237 IF ( (p_new_assignment_status IS NULL AND l_old_assignment_status IS NOT NULL)
238 OR (p_new_assignment_status IS NOT NULL AND l_old_assignment_status IS NULL)
239 OR (p_new_assignment_status IS NOT NULL AND l_old_assignment_status IS NOT NULL
240 AND p_new_assignment_status <> l_old_assignment_status))
241 THEN
242 l_assignment_status_changed:='Y';
243 END IF;
244
245 IF ( (p_new_actual_effort IS NULL AND l_old_actual_effort IS NOT NULL)
246 OR (p_new_actual_effort IS NOT NULL AND l_old_actual_effort IS NULL)
247 OR (p_new_actual_effort IS NOT NULL AND l_old_actual_effort IS NOT NULL
248 AND p_new_actual_effort <> l_old_actual_effort))
249 THEN
253 IF ( (p_new_actual_effort_uom IS NULL AND l_old_actual_effort_uom IS NOT NULL)
250 l_actual_effort_changed:='Y';
251 END IF;
252
254 OR (p_new_actual_effort_uom IS NOT NULL AND l_old_actual_effort_uom IS NULL)
255 OR (p_new_actual_effort_uom IS NOT NULL AND l_old_actual_effort_uom IS NOT NULL
256 AND p_new_actual_effort_uom <> l_old_actual_effort_uom))
257 THEN
258 l_actual_effort_uom_changed:='Y';
259 END IF;
260
261 IF ( (p_new_res_territory_id IS NULL AND l_old_res_territory_id IS NOT NULL)
262 OR (p_new_res_territory_id IS NOT NULL AND l_old_res_territory_id IS NULL)
263 OR (p_new_res_territory_id IS NOT NULL AND l_old_res_territory_id IS NOT NULL
264 AND p_new_res_territory_id <> l_old_res_territory_id))
265 THEN
266 l_res_territory_id_changed:='Y';
267 END IF;
268
269 IF ( (p_new_assignee_role IS NULL AND l_old_assignee_role IS NOT NULL)
270 OR (p_new_assignee_role IS NOT NULL AND l_old_assignee_role IS NULL)
271 OR (p_new_assignee_role IS NOT NULL AND l_old_assignee_role IS NOT NULL
272 AND p_new_assignee_role <> l_old_assignee_role))
273 THEN
274 l_assignee_role_changed:='Y';
275 END IF;
276
277 IF ( (p_new_schedule_flag IS NULL AND l_old_schedule_flag IS NOT NULL)
278 OR (p_new_schedule_flag IS NOT NULL AND l_old_schedule_flag IS NULL)
279 OR (p_new_schedule_flag IS NOT NULL AND l_old_schedule_flag IS NOT NULL
280 AND p_new_schedule_flag <> l_old_schedule_flag))
281 THEN
282 l_schedule_flag_changed:='Y';
283 END IF;
284
285 IF ( (p_new_alarm_type IS NULL AND l_old_alarm_type IS NOT NULL)
286 OR (p_new_alarm_type IS NOT NULL AND l_old_alarm_type IS NULL)
287 OR (p_new_alarm_type IS NOT NULL AND l_old_alarm_type IS NOT NULL
288 AND p_new_alarm_type <> l_old_alarm_type))
289 THEN
290 l_alarm_type_changed:='Y';
291 END IF;
292
293 IF ( (p_new_alarm_contact IS NULL AND l_old_alarm_contact IS NOT NULL)
294 OR (p_new_alarm_contact IS NOT NULL AND l_old_alarm_contact IS NULL)
295 OR (p_new_alarm_contact IS NOT NULL AND l_old_alarm_contact IS NOT NULL
296 AND p_new_alarm_contact <> l_old_alarm_contact))
297 THEN
298 l_alarm_contact_changed:='Y';
299 END IF;
300
301 IF ( (p_new_update_status_flag IS NULL AND l_old_update_status_flag IS NOT NULL)
302 OR (p_new_update_status_flag IS NOT NULL AND l_old_update_status_flag IS NULL)
303 OR (p_new_update_status_flag IS NOT NULL AND l_old_update_status_flag IS NOT NULL
304 AND p_new_update_status_flag <> l_old_update_status_flag) )
305 THEN
306 l_update_status_flag_changed:='Y';
307 END IF;
308
309 IF ( (p_new_show_on_cal_flag IS NULL AND l_old_show_on_cal_flag IS NOT NULL)
310 OR (p_new_show_on_cal_flag IS NOT NULL AND l_old_show_on_cal_flag IS NULL)
311 OR (p_new_show_on_cal_flag IS NOT NULL AND l_old_show_on_cal_flag IS NOT NULL
312 AND p_new_show_on_cal_flag <> l_old_show_on_cal_flag) )
313 THEN
314 l_show_on_cal_flag_changed:='Y';
315 END IF;
316
317 IF ( (l_new_category_id IS NULL AND l_old_category_id IS NOT NULL)
318 OR (l_new_category_id IS NOT NULL AND l_old_category_id IS NULL)
319 OR (l_new_category_id IS NOT NULL AND l_old_category_id IS NOT NULL
320 AND l_new_category_id <> l_old_category_id) )
321 THEN
322 l_category_id_changed:='Y';
323 END IF;
324
325 IF ( (p_new_free_busy_type IS NULL AND l_old_free_busy_type IS NOT NULL)
326 OR (p_new_free_busy_type IS NOT NULL AND l_old_free_busy_type IS NULL)
327 OR (p_new_free_busy_type IS NOT NULL AND l_old_free_busy_type IS NOT NULL
328 AND p_new_free_busy_type <> l_old_free_busy_type) )
329 THEN
330 l_free_busy_type_changed:='Y';
331 END IF;
332
333 IF ( (p_new_booking_start_date IS NULL AND l_old_booking_start_date IS NOT NULL)
334 OR (p_new_booking_start_date IS NOT NULL AND l_old_booking_start_date IS NULL)
335 OR (p_new_booking_start_date IS NOT NULL AND l_old_booking_start_date IS NOT NULL
336 AND p_new_booking_start_date <> l_old_booking_start_date) )
337 THEN
338 l_booking_start_date_changed:='Y';
339 END IF;
340
341 IF ( (p_new_booking_end_date IS NULL AND l_old_booking_end_date IS NOT NULL)
342 OR (p_new_booking_end_date IS NOT NULL AND l_old_booking_end_date IS NULL)
343 OR (p_new_booking_end_date IS NOT NULL AND l_old_booking_end_date IS NOT NULL
344 AND p_new_booking_end_date <> l_old_booking_end_date) )
345 THEN
346 l_booking_end_date_changed:='Y';
347 END IF;
348
349 IF ( (p_new_actual_travel_distance IS NULL AND l_old_actual_travel_distance IS NOT NULL)
350 OR (p_new_actual_travel_distance IS NOT NULL AND l_old_actual_travel_distance IS NULL)
351 OR (p_new_actual_travel_distance IS NOT NULL AND l_old_actual_travel_distance IS NOT NULL
352 AND p_new_actual_travel_distance <> l_old_actual_travel_distance))
353 THEN
354 l_actual_travel_dist_changed:='Y';
355 END IF;
356
357 IF ( (p_new_actual_travel_duration IS NULL AND l_old_actual_travel_duration IS NOT NULL)
358 OR (p_new_actual_travel_duration IS NOT NULL AND l_old_actual_travel_duration IS NULL)
359 OR (p_new_actual_travel_duration IS NOT NULL AND l_old_actual_travel_duration IS NOT NULL
360 AND p_new_actual_travel_duration <> l_old_actual_travel_duration) )
361 THEN
362 l_actual_travel_dur_changed:='Y';
363 END IF;
364
365 IF ( (p_new_actual_travel_dur_uom IS NULL AND l_old_actual_travel_dur_uom IS NOT NULL)
366 OR (p_new_actual_travel_dur_uom IS NOT NULL AND l_old_actual_travel_dur_uom IS NULL)
367 OR (p_new_actual_travel_dur_uom IS NOT NULL AND l_old_actual_travel_dur_uom IS NOT NULL
368 AND p_new_actual_travel_dur_uom <> l_old_actual_travel_dur_uom) )
369 THEN
370 l_actual_travel_uom_changed:='Y';
371 END IF;
372
373 IF ( (p_new_sched_travel_distance IS NULL AND l_old_sched_travel_distance IS NOT NULL)
374 OR (p_new_sched_travel_distance IS NOT NULL AND l_old_sched_travel_distance IS NULL)
375 OR (p_new_sched_travel_distance IS NOT NULL AND l_old_sched_travel_distance IS NOT NULL
376 AND p_new_sched_travel_distance <> l_old_sched_travel_distance) )
377 THEN
378 l_sched_travel_dist_changed:='Y';
379 END IF;
380
381 IF ( (p_new_sched_travel_duration IS NULL AND l_old_sched_travel_duration IS NOT NULL)
382 OR (p_new_sched_travel_duration IS NOT NULL AND l_old_sched_travel_duration IS NULL)
383 OR (p_new_sched_travel_duration IS NOT NULL AND l_old_sched_travel_duration IS NOT NULL
384 AND p_new_sched_travel_duration <> l_old_sched_travel_duration) )
385 THEN
386 l_sched_travel_dur_changed:='Y';
387 END IF;
388
389 IF ( (p_new_sched_travel_dur_uom IS NULL AND l_old_sched_travel_DUR_UOM IS NOT NULL)
390 OR (p_new_sched_travel_dur_uom IS NOT NULL AND l_old_sched_travel_DUR_UOM IS NULL)
391 OR (p_new_sched_travel_dur_uom IS NOT NULL AND l_old_sched_travel_DUR_UOM IS NOT NULL
392 AND p_new_sched_travel_dur_uom <> l_old_sched_travel_DUR_UOM) )
393 THEN
394 l_sched_travel_uom_changed:='Y';
395 END IF;
396
397 IF ( (p_new_actual_start_date IS NULL AND l_old_actual_start_date IS NOT NULL)
398 OR (p_new_actual_start_date IS NOT NULL AND l_old_actual_start_date IS NULL)
399 OR (p_new_actual_start_date IS NOT NULL AND l_old_actual_start_date IS NOT NULL
400 AND p_new_actual_start_date <> l_old_actual_start_date) )
401 THEN
402 l_actual_start_date_changed:='Y';
403 END IF;
404
405 IF ( (p_new_actual_end_date IS NULL AND l_old_actual_end_date IS NOT NULL)
406 OR (p_new_actual_end_date IS NOT NULL AND l_old_actual_end_date IS NULL)
407 OR (p_new_actual_end_date IS NOT NULL AND l_old_actual_end_date IS NOT NULL
408 AND p_new_actual_end_date <> l_old_actual_end_date) )
409 THEN
410 l_actual_end_date_changed:='Y';
411 END IF;
412
413 IF(l_asg_create='0' OR l_resource_type_code_changed='Y' OR
414 l_resource_id_changed='Y' OR
415 l_assignment_status_changed='Y' OR
416 l_actual_effort_changed='Y' OR
417 l_actual_effort_uom_changed='Y' OR
418 l_res_territory_id_changed='Y' OR
419 l_assignee_role_changed='Y' OR
420 l_schedule_flag_changed='Y' OR
421 l_alarm_type_changed='Y' OR
422 l_alarm_contact_changed='Y' OR
423 l_update_status_flag_changed='Y' OR
424 l_show_on_cal_flag_changed='Y' OR
425 l_category_id_changed='Y' OR
426 l_free_busy_type_changed='Y' OR
427 l_booking_start_date_changed='Y' OR
428 l_booking_end_date_changed='Y' OR
429 l_actual_travel_dist_changed='Y' OR
430 l_actual_travel_dur_changed='Y' OR
431 l_actual_travel_uom_changed='Y' OR
432 l_sched_travel_dist_changed='Y' OR
433 l_sched_travel_dur_changed='Y' OR
434 l_sched_travel_uom_changed='Y' OR
435 l_actual_start_date_changed='Y' OR
436 l_actual_end_date_changed='Y' )
437 THEN
438
439 SELECT jtf_task_assignments_audit_s.NEXTVAL INTO l_curr FROM dual;
440 INSERT_ROW(
441 X_ASSIGNMENT_AUDIT_ID => l_curr,
442 X_ASSIGNMENT_ID => p_task_assignment_id,
443 X_TASK_ID => p_task_id,
444 X_CREATION_DATE => SYSDATE,
445 X_CREATED_BY => jtf_task_utl.created_by,
446 X_LAST_UPDATE_DATE => SYSDATE,
447 X_LAST_UPDATED_BY => jtf_task_utl.updated_by,
448 X_LAST_UPDATE_LOGIN => jtf_task_utl.login_id,
449 X_OLD_RESOURCE_TYPE_CODE => l_old_resource_type_code,
450 X_NEW_RESOURCE_TYPE_CODE => p_new_resource_type_code,
451 X_OLD_RESOURCE_ID => l_old_resource_id,
452 X_NEW_RESOURCE_ID => p_new_resource_id,
453 X_OLD_ASSIGNMENT_STATUS_ID => l_old_assignment_status,
454 X_NEW_ASSIGNMENT_STATUS_ID => p_new_assignment_status,
455 X_OLD_ACTUAL_EFFORT => l_old_actual_effort,
456 X_NEW_ACTUAL_EFFORT => p_new_actual_effort,
457 X_OLD_ACTUAL_EFFORT_UOM => l_old_actual_effort_uom,
458 X_NEW_ACTUAL_EFFORT_UOM => p_new_actual_effort_uom,
459 X_OLD_RES_TERRITORY_ID => l_old_res_territory_id,
460 X_NEW_RES_TERRITORY_ID => p_new_res_territory_id,
461 X_OLD_ASSIGNEE_ROLE => l_old_assignee_role,
462 X_NEW_ASSIGNEE_ROLE => p_new_assignee_role,
463 X_OLD_ALARM_TYPE => l_old_alarm_type,
464 X_NEW_ALARM_TYPE => p_new_alarm_type,
465 X_OLD_ALARM_CONTACT => l_old_alarm_contact,
466 X_NEW_ALARM_CONTACT => p_new_alarm_contact,
467 X_OLD_CATEGORY_ID => l_old_category_id,
468 X_NEW_CATEGORY_ID => l_new_category_id,
469 X_OLD_BOOKING_START_DATE => l_old_booking_start_date,
470 X_NEW_BOOKING_START_DATE => p_new_booking_start_date,
471 X_OLD_BOOKING_END_DATE => l_old_booking_end_date,
472 X_NEW_BOOKING_END_DATE => p_new_booking_end_date,
473 X_OLD_ACTUAL_TRAVEL_DISTANCE => l_old_actual_travel_distance,
474 X_NEW_ACTUAL_TRAVEL_DISTANCE => p_new_actual_travel_distance,
475 X_OLD_ACTUAL_TRAVEL_DURATION => l_old_actual_travel_duration,
476 X_NEW_ACTUAL_TRAVEL_DURATION => p_new_actual_travel_duration,
477 X_OLD_ACTUAL_TRAVEL_DUR_UOM => l_old_actual_travel_dur_uom,
478 X_NEW_ACTUAL_TRAVEL_DUR_UOM => p_new_actual_travel_dur_uom,
479 X_OLD_SCHED_TRAVEL_DISTANCE => l_old_sched_travel_distance,
480 X_NEW_SCHED_TRAVEL_DISTANCE => p_new_sched_travel_distance,
481 X_OLD_SCHED_TRAVEL_DURATION => l_old_sched_travel_duration,
482 X_NEW_SCHED_TRAVEL_DURATION => p_new_sched_travel_duration,
483 X_OLD_SCHED_TRAVEL_DUR_UOM => l_old_sched_travel_dur_uom,
484 X_NEW_SCHED_TRAVEL_DUR_UOM => p_new_sched_travel_dur_uom,
485 X_OLD_ACTUAL_START_DATE => l_old_actual_start_date,
486 X_NEW_ACTUAL_START_DATE => p_new_actual_start_date,
487 X_OLD_ACTUAL_END_DATE => l_old_actual_end_date,
488 X_NEW_ACTUAL_END_DATE => p_new_actual_end_date,
489 X_FREE_BUSY_TYPE_CHANGED => l_free_busy_type_changed,
490 X_UPDATE_STATUS_FLAG_CHANGED => l_update_status_flag_changed,
491 X_SHOW_ON_CALENDAR_CHANGED => l_show_on_cal_flag_changed,
492 X_SCHEDULED_FLAG_CHANGED => l_schedule_flag_changed
493 );
494
495 END IF;
496 SELECT jtf_task_assignments_audit_s.CURRVAL INTO l_curr FROM dual;
497 OPEN c1 (l_curr);
498 FETCH c1 INTO x;
499
500 IF c1%NOTFOUND
501 THEN
502 x_return_status := fnd_api.g_ret_sts_unexp_error;
503 RAISE fnd_api.g_exc_unexpected_error;
504 ELSE
505 NULL;
506 END IF;
507
508
509 IF fnd_api.to_boolean (p_commit)
510 THEN
511 COMMIT WORK;
512 END IF;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 END create_task_assignment_audit;
518
519
520
521 PROCEDURE INSERT_ROW (
522 X_ASSIGNMENT_AUDIT_ID IN NUMBER,
523 X_ASSIGNMENT_ID IN NUMBER,
524 X_TASK_ID IN NUMBER,
525 X_CREATION_DATE in DATE,
526 X_CREATED_BY in NUMBER,
527 X_LAST_UPDATE_DATE in DATE,
528 X_LAST_UPDATED_BY in NUMBER,
529 X_LAST_UPDATE_LOGIN in NUMBER,
530 X_OLD_RESOURCE_TYPE_CODE IN VARCHAR2,
531 X_NEW_RESOURCE_TYPE_CODE IN VARCHAR2,
532 X_OLD_RESOURCE_ID IN NUMBER,
533 X_NEW_RESOURCE_ID IN NUMBER,
534 X_OLD_ASSIGNMENT_STATUS_ID IN NUMBER,
535 X_NEW_ASSIGNMENT_STATUS_ID IN NUMBER,
536 X_OLD_ACTUAL_EFFORT IN NUMBER,
537 X_NEW_ACTUAL_EFFORT IN NUMBER,
538 X_OLD_ACTUAL_EFFORT_UOM IN VARCHAR2,
539 X_NEW_ACTUAL_EFFORT_UOM IN VARCHAR2,
540 X_OLD_RES_TERRITORY_ID IN NUMBER,
541 X_NEW_RES_TERRITORY_ID IN NUMBER,
542 X_OLD_ASSIGNEE_ROLE IN VARCHAR2,
543 X_NEW_ASSIGNEE_ROLE IN VARCHAR2,
544 X_OLD_ALARM_TYPE IN VARCHAR2,
545 X_NEW_ALARM_TYPE IN VARCHAR2,
546 X_OLD_ALARM_CONTACT IN VARCHAR2,
547 X_NEW_ALARM_CONTACT IN VARCHAR2,
548 X_OLD_CATEGORY_ID IN NUMBER,
549 X_NEW_CATEGORY_ID IN NUMBER,
550 X_OLD_BOOKING_START_DATE IN DATE,
551 X_NEW_BOOKING_START_DATE IN DATE,
552 X_OLD_BOOKING_END_DATE IN DATE,
553 X_NEW_BOOKING_END_DATE IN DATE,
554 X_OLD_ACTUAL_TRAVEL_DISTANCE IN NUMBER,
555 X_NEW_ACTUAL_TRAVEL_DISTANCE IN NUMBER,
556 X_OLD_ACTUAL_TRAVEL_DURATION IN NUMBER,
557 X_NEW_ACTUAL_TRAVEL_DURATION IN NUMBER,
558 X_OLD_ACTUAL_TRAVEL_DUR_UOM IN VARCHAR2,
559 X_NEW_ACTUAL_TRAVEL_DUR_UOM IN VARCHAR2,
560 X_OLD_SCHED_TRAVEL_DISTANCE IN NUMBER,
561 X_NEW_SCHED_TRAVEL_DISTANCE IN NUMBER,
562 X_OLD_SCHED_TRAVEL_DURATION IN NUMBER,
563 X_NEW_SCHED_TRAVEL_DURATION IN NUMBER,
564 X_OLD_SCHED_TRAVEL_DUR_UOM IN VARCHAR2,
565 X_NEW_SCHED_TRAVEL_DUR_UOM IN VARCHAR2,
566 X_OLD_ACTUAL_START_DATE IN DATE,
567 X_NEW_ACTUAL_START_DATE IN DATE,
568 X_OLD_ACTUAL_END_DATE IN DATE,
569 X_NEW_ACTUAL_END_DATE IN DATE,
570 X_FREE_BUSY_TYPE_CHANGED IN VARCHAR2,
571 X_UPDATE_STATUS_FLAG_CHANGED IN VARCHAR2,
572 X_SHOW_ON_CALENDAR_CHANGED IN VARCHAR2,
573 X_SCHEDULED_FLAG_CHANGED IN VARCHAR2
574 ) IS
575 l_rowid ROWID;
576 l_enable_audit varchar2(5);
577
578 cursor C IS select ROWID from JTF_TASK_ASSIGNMENTS_AUDIT_B
579 where ASSIGNMENT_AUDIT_ID = X_ASSIGNMENT_AUDIT_ID;
580 BEGIN
581 l_enable_audit := Upper(nvl(fnd_profile.Value('JTF_TASK_ENABLE_AUDIT'),'Y'));
582 IF(l_enable_audit = 'N') THEN
583 RETURN;
584 END IF;
585
586 INSERT INTO JTF_TASK_ASSIGNMENTS_AUDIT_B (
587 ASSIGNMENT_AUDIT_ID ,
588 ASSIGNMENT_ID ,
589 OBJECT_VERSION_NUMBER,
590 TASK_ID ,
591 CREATION_DATE ,
592 CREATED_BY ,
593 LAST_UPDATE_DATE ,
594 LAST_UPDATED_BY ,
595 LAST_UPDATE_LOGIN ,
596 OLD_RESOURCE_TYPE_CODE ,
597 NEW_RESOURCE_TYPE_CODE ,
598 OLD_RESOURCE_ID ,
599 NEW_RESOURCE_ID ,
600 OLD_ASSIGNMENT_STATUS_ID ,
601 NEW_ASSIGNMENT_STATUS_ID ,
602 OLD_ACTUAL_EFFORT ,
603 NEW_ACTUAL_EFFORT ,
604 OLD_ACTUAL_EFFORT_UOM ,
605 NEW_ACTUAL_EFFORT_UOM ,
606 OLD_RES_TERRITORY_ID ,
607 NEW_RES_TERRITORY_ID ,
608 OLD_ASSIGNEE_ROLE ,
609 NEW_ASSIGNEE_ROLE ,
610 OLD_ALARM_TYPE ,
611 NEW_ALARM_TYPE ,
612 OLD_ALARM_CONTACT ,
613 NEW_ALARM_CONTACT ,
614 OLD_CATEGORY_ID ,
615 NEW_CATEGORY_ID ,
616 OLD_BOOKING_START_DATE ,
617 NEW_BOOKING_START_DATE ,
618 OLD_BOOKING_END_DATE ,
619 NEW_BOOKING_END_DATE ,
620 OLD_ACTUAL_TRAVEL_DISTANCE ,
621 NEW_ACTUAL_TRAVEL_DISTANCE ,
622 OLD_ACTUAL_TRAVEL_DURATION ,
623 NEW_ACTUAL_TRAVEL_DURATION ,
624 OLD_ACTUAL_TRAVEL_DURATION_UOM ,
625 NEW_ACTUAL_TRAVEL_DURATION_UOM ,
626 OLD_SCHED_TRAVEL_DISTANCE ,
627 NEW_SCHED_TRAVEL_DISTANCE ,
628 OLD_SCHED_TRAVEL_DURATION ,
629 NEW_SCHED_TRAVEL_DURATION ,
630 OLD_SCHED_TRAVEL_DURATION_UOM ,
631 NEW_SCHED_TRAVEL_DURATION_UOM ,
632 OLD_ACTUAL_START_DATE,
633 NEW_ACTUAL_START_DATE,
634 OLD_ACTUAL_END_DATE,
635 NEW_ACTUAL_END_DATE,
636 FREE_BUSY_TYPE_CHANGED ,
637 UPDATE_STATUS_FLAG_CHANGED ,
638 SHOW_ON_CALENDAR_CHANGED ,
639 SCHEDULE_FLAG_CHANGED ) VALUES (
640 X_ASSIGNMENT_AUDIT_ID ,
641 X_ASSIGNMENT_ID ,
642 1.0,
643 X_TASK_ID ,
644 X_CREATION_DATE ,
645 X_CREATED_BY ,
646 X_LAST_UPDATE_DATE ,
647 X_LAST_UPDATED_BY ,
648 X_LAST_UPDATE_LOGIN ,
649 X_OLD_RESOURCE_TYPE_CODE ,
650 X_NEW_RESOURCE_TYPE_CODE ,
651 X_OLD_RESOURCE_ID ,
652 X_NEW_RESOURCE_ID ,
653 X_OLD_ASSIGNMENT_STATUS_ID ,
654 X_NEW_ASSIGNMENT_STATUS_ID ,
655 X_OLD_ACTUAL_EFFORT ,
656 X_NEW_ACTUAL_EFFORT ,
657 X_OLD_ACTUAL_EFFORT_UOM ,
658 X_NEW_ACTUAL_EFFORT_UOM ,
659 X_OLD_RES_TERRITORY_ID ,
660 X_NEW_RES_TERRITORY_ID ,
661 X_OLD_ASSIGNEE_ROLE ,
662 X_NEW_ASSIGNEE_ROLE ,
663 X_OLD_ALARM_TYPE ,
664 X_NEW_ALARM_TYPE ,
665 X_OLD_ALARM_CONTACT ,
666 X_NEW_ALARM_CONTACT ,
667 X_OLD_CATEGORY_ID ,
668 X_NEW_CATEGORY_ID ,
669 X_OLD_BOOKING_START_DATE ,
670 X_NEW_BOOKING_START_DATE ,
671 X_OLD_BOOKING_END_DATE ,
672 X_NEW_BOOKING_END_DATE ,
673 X_OLD_ACTUAL_TRAVEL_DISTANCE ,
674 X_NEW_ACTUAL_TRAVEL_DISTANCE ,
675 X_OLD_ACTUAL_TRAVEL_DURATION ,
676 X_NEW_ACTUAL_TRAVEL_DURATION ,
677 X_OLD_ACTUAL_TRAVEL_DUR_UOM ,
678 X_NEW_ACTUAL_TRAVEL_DUR_UOM ,
679 X_OLD_SCHED_TRAVEL_DISTANCE ,
680 X_NEW_SCHED_TRAVEL_DISTANCE ,
681 X_OLD_SCHED_TRAVEL_DURATION ,
682 X_NEW_SCHED_TRAVEL_DURATION ,
683 X_OLD_SCHED_TRAVEL_DUR_UOM ,
684 X_NEW_SCHED_TRAVEL_DUR_UOM ,
685 X_OLD_ACTUAL_START_DATE,
686 X_NEW_ACTUAL_START_DATE,
687 X_OLD_ACTUAL_END_DATE,
688 X_NEW_ACTUAL_END_DATE,
689 X_FREE_BUSY_TYPE_CHANGED ,
690 X_UPDATE_STATUS_FLAG_CHANGED ,
691 X_SHOW_ON_CALENDAR_CHANGED ,
692 X_SCHEDULED_FLAG_CHANGED );
693
694
695 OPEN c;
696 FETCH c into l_rowid;
697 IF (c%notfound) THEN
698 close c;
699 raise no_data_found;
700 END IF;
701 CLOSE c;
702 END INSERT_ROW;
703
704 PROCEDURE DELETE_ROW(X_ASSIGNMENT_ID IN NUMBER)
705 IS
706 CURSOR C IS select ROWID from JTF_TASK_ALL_ASSIGNMENTS
707 where task_ASSIGNMENT_ID = X_ASSIGNMENT_ID;
708
709 l_rowid ROWID;
710 l_enable_audit varchar2(5);
711 BEGIN
712 l_enable_audit := Upper(nvl(fnd_profile.Value('JTF_TASK_ENABLE_AUDIT'),'Y'));
713 IF(l_enable_audit = 'N') THEN
714 RETURN;
715 END IF;
716 OPEN c;
717 FETCH c into l_rowid;
718 IF(c%notfound) THEN
719 DELETE FROM jtf_task_assignments_audit_b WHERE assignment_id = x_assignment_id;
720 ELSE
721 RAISE fnd_api.g_exc_unexpected_error;
722 END IF;
723 CLOSE c;
724 END DELETE_ROW;
725
726
727 END jtf_task_assignment_audit_pkg;
[Home] [Help]