[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_AUDITS_PVT
Source
1 PACKAGE BODY jtf_task_audits_pvt AS
2 /* $Header: jtftktub.pls 120.3 2006/01/19 06:22:21 sbarat ship $ */
3 PROCEDURE process_task_audits (
4 p_api_version IN NUMBER,
5 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
6 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
7 p_object_version_number IN NUMBER ,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_old_billable_flag IN VARCHAR2 DEFAULT NULL,
12 p_old_device1_flag IN VARCHAR2 DEFAULT NULL,
13 p_old_device2_flag IN VARCHAR2 DEFAULT NULL,
14 p_old_device3_flag IN VARCHAR2 DEFAULT NULL,
15 p_old_esc_flag IN VARCHAR2 DEFAULT NULL,
16 p_old_holiday_flag IN VARCHAR2 DEFAULT NULL,
17 p_old_laptop_flag IN VARCHAR2 DEFAULT NULL,
18 p_old_milestone_flag IN VARCHAR2 DEFAULT NULL,
19 p_old_multi_booked_flag IN VARCHAR2 DEFAULT NULL,
20 p_old_not_flag IN VARCHAR2 DEFAULT NULL,
21 p_old_palm_flag IN VARCHAR2 DEFAULT NULL,
22 p_old_private_flag IN VARCHAR2 DEFAULT NULL,
23 p_old_publish_flag IN VARCHAR2 DEFAULT NULL,
24 p_old_restrict_closure_flag IN VARCHAR2 DEFAULT NULL,
25 p_old_wince_flag IN VARCHAR2 DEFAULT NULL,
26 p_old_soft_bound_flag IN VARCHAR2 DEFAULT NULL,
27 p_new_billable_flag IN VARCHAR2 DEFAULT NULL,
28 p_new_device1_flag IN VARCHAR2 DEFAULT NULL,
29 p_new_device2_flag IN VARCHAR2 DEFAULT NULL,
30 p_new_device3_flag IN VARCHAR2 DEFAULT NULL,
31 p_new_esc_flag IN VARCHAR2 DEFAULT NULL,
32 p_new_holiday_flag IN VARCHAR2 DEFAULT NULL,
33 p_new_laptop_flag IN VARCHAR2 DEFAULT NULL,
34 p_new_milestone_flag IN VARCHAR2 DEFAULT NULL,
35 p_new_multi_booked_flag IN VARCHAR2 DEFAULT NULL,
36 p_new_not_flag IN VARCHAR2 DEFAULT NULL,
37 p_new_palm_flag IN VARCHAR2 DEFAULT NULL,
38 p_new_private_flag IN VARCHAR2 DEFAULT NULL,
39 p_new_publish_flag IN VARCHAR2 DEFAULT NULL,
40 p_new_restrict_closure_flag IN VARCHAR2 DEFAULT NULL,
41 p_new_wince_flag IN VARCHAR2 DEFAULT NULL,
42 p_new_soft_bound_flag IN VARCHAR2 DEFAULT NULL,
43 p_new_actual_effort IN NUMBER DEFAULT NULL,
44 p_new_actual_effort_uom IN VARCHAR2 DEFAULT NULL,
45 p_new_actual_end_date IN DATE DEFAULT NULL,
46 p_new_actual_start_date IN DATE DEFAULT NULL,
47 p_new_address_id IN NUMBER DEFAULT NULL,
48 p_new_assigned_by_id IN NUMBER DEFAULT NULL,
49 p_new_bound_mode_code IN VARCHAR2 DEFAULT NULL,
50 p_new_costs IN NUMBER DEFAULT NULL,
51 p_new_currency_code IN VARCHAR2 DEFAULT NULL,
52 p_new_customer_id IN NUMBER DEFAULT NULL,
53 p_new_cust_account_id IN NUMBER DEFAULT NULL,
54 p_new_duration IN NUMBER DEFAULT NULL,
55 p_new_duration_uom IN VARCHAR2 DEFAULT NULL,
56 p_new_esc_owner_id IN NUMBER DEFAULT NULL,
57 p_new_esc_terr_id IN NUMBER DEFAULT NULL,
58 p_new_not_period IN NUMBER DEFAULT NULL,
59 p_new_not_period_uom IN VARCHAR2 DEFAULT NULL,
60 p_new_org_id IN NUMBER DEFAULT NULL,
61 p_new_owner_id IN NUMBER DEFAULT NULL,
62 p_new_owner_type_code IN VARCHAR2 DEFAULT NULL,
63 p_new_parent_task_id IN NUMBER DEFAULT NULL,
64 p_new_per_complete IN NUMBER DEFAULT NULL,
65 p_new_planned_effort IN NUMBER DEFAULT NULL,
66 p_new_planned_effort_uom IN VARCHAR2 DEFAULT NULL,
67 p_new_planned_end_date IN DATE DEFAULT NULL,
68 p_new_planned_start_date IN DATE DEFAULT NULL,
69 p_new_reason_code IN VARCHAR2 DEFAULT NULL,
70 p_new_recurrence_rule_id IN NUMBER DEFAULT NULL,
71 p_new_sched_end_date IN DATE DEFAULT NULL,
72 p_new_sched_start_date IN DATE DEFAULT NULL,
73 p_new_src_obj_id IN NUMBER DEFAULT NULL,
74 p_new_src_obj_name IN VARCHAR2 DEFAULT NULL,
75 p_new_src_obj_type_code IN VARCHAR2 DEFAULT NULL,
76 p_new_task_priority_id IN NUMBER DEFAULT NULL,
77 p_new_task_status_id IN NUMBER DEFAULT NULL,
78 p_new_task_type_id IN NUMBER DEFAULT NULL,
79 p_new_timezone_id IN NUMBER DEFAULT NULL,
80 p_new_workflow_process_id IN NUMBER DEFAULT NULL,
81 p_not_chan_flag IN VARCHAR2 DEFAULT NULL,
82 p_old_actual_effort IN NUMBER DEFAULT NULL,
83 p_old_actual_effort_uom IN VARCHAR2 DEFAULT NULL,
84 p_old_actual_end_date IN DATE DEFAULT NULL,
85 p_old_actual_start_date IN DATE DEFAULT NULL,
86 p_old_address_id IN NUMBER DEFAULT NULL,
87 p_old_assigned_by_id IN NUMBER DEFAULT NULL,
88 p_old_bound_mode_code IN VARCHAR2 DEFAULT NULL,
89 p_old_costs IN NUMBER DEFAULT NULL,
90 p_old_currency_code IN VARCHAR2 DEFAULT NULL,
91 p_old_customer_id IN NUMBER DEFAULT NULL,
92 p_old_cust_account_id IN NUMBER DEFAULT NULL,
93 p_old_duration IN NUMBER DEFAULT NULL,
94 p_old_duration_uom IN VARCHAR2 DEFAULT NULL,
95 p_old_esc_owner_id IN NUMBER DEFAULT NULL,
96 p_old_esc_terr_id IN NUMBER DEFAULT NULL,
97 p_old_not_period IN NUMBER DEFAULT NULL,
98 p_old_not_period_uom IN VARCHAR2 DEFAULT NULL,
99 p_old_org_id IN NUMBER DEFAULT NULL,
100 p_old_owner_id IN NUMBER DEFAULT NULL,
101 p_old_owner_type_code IN VARCHAR2 DEFAULT NULL,
102 p_old_parent_task_id IN NUMBER DEFAULT NULL,
103 p_old_per_complete IN NUMBER DEFAULT NULL,
104 p_old_planned_effort IN NUMBER DEFAULT NULL,
105 p_old_planned_effort_uom IN VARCHAR2 DEFAULT NULL,
106 p_old_planned_end_date IN DATE DEFAULT NULL,
107 p_old_planned_start_date IN DATE DEFAULT NULL,
108 p_old_reason_code IN VARCHAR2 DEFAULT NULL,
109 p_old_recurrence_rule_id IN NUMBER DEFAULT NULL,
110 p_old_sched_end_date IN DATE DEFAULT NULL,
111 p_old_sched_start_date IN DATE DEFAULT NULL,
112 p_old_src_obj_id IN NUMBER DEFAULT NULL,
113 p_old_src_obj_name IN VARCHAR2 DEFAULT NULL,
114 p_old_src_obj_type_code IN VARCHAR2 DEFAULT NULL,
115 p_old_task_priority_id IN NUMBER DEFAULT NULL,
116 p_old_task_status_id IN NUMBER DEFAULT NULL,
117 p_old_task_type_id IN NUMBER DEFAULT NULL,
118 p_old_timezone_id IN NUMBER DEFAULT NULL,
119 p_old_workflow_process_id IN NUMBER DEFAULT NULL,
120 p_task_id IN NUMBER,
121 p_new_description IN VARCHAR2 DEFAULT NULL,
122 p_new_task_name IN VARCHAR2 DEFAULT NULL,
123 p_old_description IN VARCHAR2 DEFAULT NULL,
124 p_old_task_name IN VARCHAR2 DEFAULT NULL,
125 p_old_escalation_level IN VARCHAR2 DEFAULT NULL,
126 p_new_escalation_level IN VARCHAR2 DEFAULT NULL,
127 p_old_owner_territory_id IN NUMBER DEFAULT NULL,
128 p_new_owner_territory_id IN NUMBER DEFAULT NULL,
129 P_OLD_DATE_SELECTED IN VARCHAR2 DEFAULT NULL ,
130 P_NEW_DATE_SELECTED IN VARCHAR2 DEFAULT NULL ,
131 p_old_location_id IN NUMBER DEFAULT NULL,
132 p_new_location_id IN NUMBER DEFAULT NULL,
133 x_task_audit_id OUT NOCOPY NUMBER
134 )
135 IS
136 l_api_name CONSTANT VARCHAR2(30) := 'JTF_TASK_AUDITS_PVT';
137 l_api_version CONSTANT NUMBER := 1.0;
138 l_rowid ROWID;
139 l_init_msg_list VARCHAR2(10) := fnd_api.g_false;
140 l_commit VARCHAR2(10) := fnd_api.g_false;
141 l_last_update_date DATE;
142 l_last_updated_by NUMBER;
143 l_creation_date DATE := SYSDATE;
144 l_task_audit_id NUMBER;
145 l_task_id NUMBER := p_task_id;
146 l_new_actual_effort NUMBER := p_new_actual_effort;
147 l_new_actual_effort_uom VARCHAR2(3) := p_new_actual_effort_uom;
148 l_new_actual_end_date DATE := p_new_actual_end_date;
149 l_new_actual_start_date DATE := p_new_actual_start_date;
150 l_new_address_id NUMBER := p_new_address_id;
151 l_new_assigned_by_id NUMBER := p_new_assigned_by_id;
152 l_new_bound_mode_code VARCHAR2(30) := p_new_bound_mode_code;
153 l_new_costs NUMBER := p_new_costs;
154 l_new_currency_code VARCHAR2(15) := p_new_currency_code;
155 l_new_customer_id NUMBER := p_new_customer_id;
156 l_new_cust_account_id NUMBER := p_new_cust_account_id;
157 l_new_duration NUMBER := p_new_duration;
158 l_new_duration_uom VARCHAR2(3) := p_new_duration_uom;
159 l_new_esc_owner_id NUMBER := p_new_esc_owner_id;
160 l_new_esc_terr_id NUMBER := p_new_esc_terr_id;
161 l_new_not_period NUMBER := p_new_not_period;
162 l_new_not_period_uom VARCHAR2(3) := p_new_not_period_uom;
163 l_new_org_id NUMBER := p_new_org_id;
164 l_new_owner_id NUMBER := p_new_owner_id;
165 l_new_owner_type_code VARCHAR2(30) := p_new_owner_type_code;
166 l_new_parent_task_id NUMBER := p_new_parent_task_id;
167 l_new_per_complete NUMBER := p_new_per_complete;
168 l_new_planned_effort NUMBER := p_new_planned_effort;
169 l_new_planned_effort_uom VARCHAR2(3) := p_new_planned_effort_uom;
170 l_new_planned_end_date DATE := p_new_planned_end_date;
171 l_new_planned_start_date DATE := p_new_planned_start_date;
172 l_new_reason_code VARCHAR2(30) := p_new_reason_code;
173 l_new_recurrence_rule_id NUMBER := p_new_recurrence_rule_id;
174 l_new_sched_end_date DATE := p_new_sched_end_date;
175 l_new_sched_start_date DATE := p_new_sched_start_date;
176 l_new_src_obj_id NUMBER := p_new_src_obj_id;
177 l_new_src_obj_name VARCHAR2(80) := p_new_src_obj_name;
178 l_new_src_obj_type_code VARCHAR2(30) := p_new_src_obj_type_code;
179 l_new_task_priority_id NUMBER := p_new_task_priority_id;
180 l_new_task_status_id NUMBER := p_new_task_status_id;
181 l_new_task_type_id NUMBER := p_new_task_type_id;
182 l_new_timezone_id NUMBER := p_new_timezone_id;
183 l_new_workflow_process_id NUMBER := p_new_workflow_process_id;
184 l_new_description VARCHAR2(4000) := p_new_description;
185 l_old_description VARCHAR2(4000) := p_old_description;
186 l_old_billable_flag VARCHAR2(1) := p_old_billable_flag;
187 l_old_device1_flag VARCHAR2(1) := p_old_device1_flag;
188 l_old_device2_flag VARCHAR2(1) := p_old_device2_flag;
189 l_old_device3_flag VARCHAR2(1) := p_old_device3_flag;
190 l_old_esc_flag VARCHAR2(1) := p_old_esc_flag;
191 l_old_holiday_flag VARCHAR2(1) := p_old_holiday_flag;
192 l_old_laptop_flag VARCHAR2(1) := p_old_laptop_flag;
193 l_old_milestone_flag VARCHAR2(1) := p_old_milestone_flag;
194 l_old_multi_booked_flag VARCHAR2(1) := p_old_multi_booked_flag;
195 l_old_not_flag VARCHAR2(1) := p_old_not_flag;
196 l_old_palm_flag VARCHAR2(1) := p_old_palm_flag;
197 l_old_private_flag VARCHAR2(1) := p_old_private_flag;
198 l_old_publish_flag VARCHAR2(1) := p_old_publish_flag;
199 l_old_restrict_closure_flag VARCHAR2(1) := p_old_restrict_closure_flag;
200 l_old_wince_flag VARCHAR2(1) := p_old_wince_flag;
201 l_old_soft_bound_flag VARCHAR2(1) := p_old_soft_bound_flag;
202 l_old_actual_effort NUMBER := p_old_actual_effort;
203 l_old_actual_effort_uom VARCHAR2(3) := p_old_actual_effort_uom;
204 l_old_actual_end_date DATE := p_old_actual_end_date;
205 l_old_actual_start_date DATE := p_old_actual_start_date;
206 l_old_address_id NUMBER := p_old_address_id;
207 l_old_assigned_by_id NUMBER := p_old_assigned_by_id;
208 l_old_bound_mode_code VARCHAR2(30) := p_old_bound_mode_code;
209 l_old_costs NUMBER := p_old_costs;
210 l_old_currency_code VARCHAR2(15) := p_old_currency_code;
211 l_old_customer_id NUMBER := p_old_customer_id;
212 l_old_cust_account_id NUMBER := p_old_cust_account_id;
213 l_old_duration NUMBER := p_old_duration;
214 l_old_duration_uom VARCHAR2(3) := p_old_duration_uom;
215 l_old_esc_owner_id NUMBER := p_old_esc_owner_id;
216 l_old_esc_terr_id NUMBER := p_old_esc_terr_id;
217 l_old_not_period NUMBER := p_old_not_period;
218 l_old_not_period_uom VARCHAR2(3) := p_old_not_period_uom;
219 l_old_org_id NUMBER := p_old_org_id;
220 l_old_owner_id NUMBER := p_old_owner_id;
221 l_old_owner_type_code VARCHAR2(30) := p_old_owner_type_code;
222 l_old_parent_task_id NUMBER := p_old_parent_task_id;
223 l_old_per_complete NUMBER := p_old_per_complete;
224 l_old_planned_effort NUMBER := p_old_planned_effort;
225 l_old_planned_effort_uom VARCHAR2(3) := p_old_planned_effort_uom;
226 l_old_planned_end_date DATE := p_old_planned_end_date;
227 l_old_planned_start_date DATE := p_old_planned_start_date;
228 l_old_reason_code VARCHAR2(30) := p_old_reason_code;
229 l_old_recurrence_rule_id NUMBER := p_old_recurrence_rule_id;
230 l_old_sched_end_date DATE := p_old_sched_end_date;
231 l_old_sched_start_date DATE := p_old_sched_start_date;
232 l_old_src_obj_id NUMBER := p_old_src_obj_id;
233 l_old_src_obj_name VARCHAR2(80) := p_old_src_obj_name;
234 l_old_src_obj_type_code VARCHAR2(30) := p_old_src_obj_type_code;
235 l_old_task_priority_id NUMBER := p_old_task_priority_id;
236 l_old_task_status_id NUMBER := p_old_task_status_id;
237 l_old_task_type_id NUMBER := p_old_task_type_id;
238 l_old_timezone_id NUMBER := p_old_timezone_id;
239 l_old_workflow_process_id NUMBER := p_old_workflow_process_id;
240 l_new_task_name VARCHAR2(80) := p_new_task_name;
241 l_old_task_name VARCHAR2(80) := p_old_task_name;
242 l_new_billable_flag VARCHAR2(1) := p_new_billable_flag;
243 l_new_device1_flag VARCHAR2(1) := p_new_device1_flag;
244 l_new_device2_flag VARCHAR2(1) := p_new_device2_flag;
245 l_new_device3_flag VARCHAR2(1) := p_new_device3_flag;
246 l_new_esc_flag VARCHAR2(1) := p_new_esc_flag;
247 l_new_holiday_flag VARCHAR2(1) := p_new_holiday_flag;
248 l_new_laptop_flag VARCHAR2(1) := p_new_laptop_flag;
249 l_new_milestone_flag VARCHAR2(1) := p_new_milestone_flag;
250 l_new_multi_booked_flag VARCHAR2(1) := p_new_multi_booked_flag;
251 l_new_not_flag VARCHAR2(1) := p_new_not_flag;
252 l_new_palm_flag VARCHAR2(1) := p_new_palm_flag;
253 l_new_private_flag VARCHAR2(1) := p_new_private_flag;
254 l_new_publish_flag VARCHAR2(1) := p_new_publish_flag;
255 l_new_restrict_closure_flag VARCHAR2(1) := p_new_restrict_closure_flag;
256 l_new_wince_flag VARCHAR2(1) := p_new_wince_flag;
257 l_new_soft_bound_flag VARCHAR2(1) := p_new_soft_bound_flag;
258 l_object_version_number NUMBER := p_object_version_number;
259 --l_owner_territory_id NUMBER := P_OWNER_TERRITORY_ID;
260 l_address_chan_flag CHAR(1) := 'N';
261 l_status_chan_flag CHAR(1) := 'N';
262 l_bound_chan_flag CHAR(1) := 'N';
263 l_costs_chan_flag CHAR(1) := 'N';
264 l_currency_code_chan_flag CHAR(1) := 'N';
265 l_customer_id_chan_flag CHAR(1) := 'N';
266 l_cust_account_chan_flag CHAR(1) := 'N';
267 l_duration_chan_flag CHAR(1) := 'N';
268 l_duration_uom_chan_flag CHAR(1) := 'N';
269 l_workflow_chan_flag CHAR(1) := 'N';
270 l_not_chan_flag CHAR(1) := 'N';
271 l_palm_chan_flag CHAR(1) := 'N';
272 l_private_chan_flag CHAR(1) := 'N';
273 l_publish_chan_flag CHAR(1) := 'N';
274 l_restrict_closure_chan_flag CHAR(1) := 'N';
275 l_wince_chan_flag CHAR(1) := 'N';
276 l_soft_bound_chan_flag CHAR(1) := 'N';
277 l_billable_chan_flag CHAR(1) := 'N';
278 l_device1_chan_flag CHAR(1) := 'N';
279 l_device2_chan_flag CHAR(1) := 'N';
280 l_device3_chan_flag CHAR(1) := 'N';
281 l_esc_chan_flag CHAR(1) := 'N';
282 l_holiday_chan_flag CHAR(1) := 'N';
283 l_laptop_chan_flag CHAR(1) := 'N';
284 l_milestone_chan_flag CHAR(1) := 'N';
285 l_multi_booked_chan_flag CHAR(1) := 'N';
286 l_esc_owner_id_chan_flag CHAR(1) := 'N';
287 l_esc_terr_id_chan_flag CHAR(1) := 'N';
288 l_not_period_chan_flag CHAR(1) := 'N';
289 l_not_period_uom_chan_flag CHAR(1) := 'N';
290 l_owner_id_chan_flag CHAR(1) := 'N';
291 l_owner_type_code_chan_flag CHAR(1) := 'N';
292 l_parent_task_id_chan_flag CHAR(1) := 'N';
293 l_per_complete_chan_flag CHAR(1) := 'N';
294 l_planned_effort_chan_flag CHAR(1) := 'N';
295 l_planned_effort_uom_chan_flag CHAR(1) := 'N';
296 l_planned_end_date_chan_flag CHAR(1) := 'N';
297 l_planned_start_date_chan_flag CHAR(1) := 'N';
298 l_reason_code_chan_flag CHAR(1) := 'N';
299 l_recurrence_rule_id_chan_flag CHAR(1) := 'N';
300 l_sched_end_date_chan_flag CHAR(1) := 'N';
301 l_sched_start_date_chan_flag CHAR(1) := 'N';
302 l_src_obj_id_chan_flag CHAR(1) := 'N';
303 l_src_obj_name_chan_flag CHAR(1) := 'N';
304 l_src_obj_type_code_chan_flag CHAR(1) := 'N';
305 l_task_priority_id_chan_flag CHAR(1) := 'N';
306 l_task_status_id_chan_flag CHAR(1) := 'N';
307 l_task_type_id_chan_flag CHAR(1) := 'N';
308 l_timezone_id_chan_flag CHAR(1) := 'N';
309 l_task_name_chan_flag CHAR(1) := 'N';
310 l_owner_territory_id_chan_flag CHAR(1) := 'N';
311 l_escalation_level_chan_flag CHAR(1) := 'N';
312 l_description_chan_flag CHAR(1) := 'N';
313 l_date_selected_chan_flag CHAR(1) := 'N';
314 l_location_id_chan_flag CHAR(1) := 'N';
315 l_old_owner_territory_id NUMBER := p_old_owner_territory_id;
316 l_new_owner_territory_id NUMBER := p_new_owner_territory_id;
317 l_new_escalation_level VARCHAR2(5) := p_new_escalation_level;
318 l_old_escalation_level VARCHAR2(5) := p_old_escalation_level;
319 l_OLD_DATE_SELECTED VARCHAR2(1) := p_OLD_DATE_SELECTED;
320 l_NEW_DATE_SELECTED VARCHAR2(1) := p_NEW_DATE_SELECTED;
321 l_old_location_id NUMBER := p_old_location_id;
322 l_new_location_id NUMBER := p_new_location_id;
323
324 x CHAR;
325 l_creation_date DATE;
326 l_created_by NUMBER(15);
327 l_last_update_date DATE;
328 x_commit VARCHAR2(1);
329 l_last_updated_by NUMBER(15);
330 l_last_update_login NUMBER(15);
331
332 CURSOR ta_cur1 (l_rowid IN ROWID)
333 IS
334 SELECT 1
335 FROM jtf_task_audits_b
336 WHERE ROWID = l_rowid;
337
338 CURSOR c_audit
339 IS
340 SELECT jtf_task_audits_s.nextval
341 FROM dual;
342 BEGIN
343 -- ---------------------------------------
344 -- Standard API stuff
345 -- ---------------------------------------
346
347 -- Establish savepoint
348 SAVEPOINT process_task_audit_pvt;
349
350 -- Check version number
351 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
352 THEN
353 RAISE fnd_api.g_exc_unexpected_error;
354 END IF;
355
356 -- Initialize message list if requested
357 IF fnd_api.to_boolean (p_init_msg_list)
358 THEN
359 fnd_msg_pub.initialize;
360 END IF;
361
362 -- Initialize return status to SUCCESS
363 x_return_status := fnd_api.g_ret_sts_success;
364
365 --dbms_output.put_line(' start of process task ');
366
367 -- ----------------------------------------------
368 -- Check if the audited fields have been changed
369 -- ----------------------------------------------
370 IF (NOT ( p_new_address_id IS NULL
371 AND p_old_address_id IS NULL))
372 AND ( p_new_address_id IS NULL
373 OR p_old_address_id IS NULL
374 OR p_new_address_id <> p_old_address_id)
375 THEN
376 l_address_chan_flag := 'Y';
377 l_new_address_id := p_new_address_id;
378 l_old_address_id := p_old_address_id;
379 END IF;
380
381 IF (NOT ( p_new_assigned_by_id IS NULL
382 AND p_old_assigned_by_id IS NULL))
383 AND ( p_new_assigned_by_id IS NULL
384 OR p_old_assigned_by_id IS NULL
385 OR p_new_assigned_by_id <> p_old_assigned_by_id)
386 THEN
387 l_status_chan_flag := 'Y';
388 l_new_assigned_by_id := p_new_assigned_by_id;
389 l_old_assigned_by_id := p_old_assigned_by_id;
390 END IF;
391
392 IF (NOT ( p_new_bound_mode_code IS NULL
393 AND p_old_bound_mode_code IS NULL))
394 AND ( p_new_bound_mode_code IS NULL
395 OR p_old_bound_mode_code IS NULL
396 OR p_new_bound_mode_code <> p_old_bound_mode_code)
397 THEN
398 l_bound_chan_flag := 'Y';
399 l_new_bound_mode_code := p_new_bound_mode_code;
400 l_old_bound_mode_code := p_old_bound_mode_code;
401 END IF;
402
403 IF (NOT ( p_new_costs IS NULL
404 AND p_old_costs IS NULL))
405 AND ( p_new_costs IS NULL
406 OR p_old_costs IS NULL
407 OR p_new_costs <> p_old_costs)
408 THEN
409 l_costs_chan_flag := 'Y';
410 l_new_costs := p_new_costs;
411 l_old_costs := p_old_costs;
412 END IF;
413
414 IF (NOT ( p_new_currency_code IS NULL
415 AND p_old_currency_code IS NULL))
416 AND ( p_new_currency_code IS NULL
417 OR p_old_currency_code IS NULL
418 OR p_new_currency_code <> p_old_currency_code)
419 THEN
420 l_currency_code_chan_flag := 'Y';
421 l_new_currency_code := p_new_currency_code;
422 l_old_currency_code := p_old_currency_code;
423 END IF;
424
425 IF (NOT ( p_new_customer_id IS NULL
426 AND p_old_customer_id IS NULL))
427 AND ( p_new_customer_id IS NULL
428 OR p_old_customer_id IS NULL
429 OR p_new_customer_id <> p_old_customer_id)
430 THEN
431 l_customer_id_chan_flag := 'Y';
432 l_new_customer_id := p_new_customer_id;
433 l_old_customer_id := p_old_customer_id;
434 END IF;
435
436 IF (NOT ( p_new_cust_account_id IS NULL
437 AND p_old_cust_account_id IS NULL))
438 AND ( p_new_cust_account_id IS NULL
439 OR p_old_cust_account_id IS NULL
440 OR p_new_cust_account_id <> p_old_cust_account_id)
441 THEN
442 l_cust_account_chan_flag := 'Y';
443 l_new_cust_account_id := p_new_cust_account_id;
444 l_old_cust_account_id := p_old_cust_account_id;
445 END IF;
446
447 IF (NOT ( p_new_duration IS NULL
448 AND p_old_duration IS NULL))
449 AND ( p_new_duration IS NULL
450 OR p_old_duration IS NULL
451 OR p_new_duration <> p_old_duration)
452 THEN
453 l_duration_chan_flag := 'Y';
454 l_new_duration := p_new_duration;
455 l_old_duration := p_old_duration;
456 END IF;
457
458 IF (NOT ( p_new_duration_uom IS NULL
459 AND p_old_duration_uom IS NULL))
460 AND ( p_new_duration_uom IS NULL
461 OR p_old_duration_uom IS NULL
462 OR p_new_duration_uom <> p_old_duration_uom)
463 THEN
464 l_duration_uom_chan_flag := 'Y';
465 l_new_duration_uom := p_new_duration_uom;
466 l_old_duration_uom := p_old_duration_uom;
467 END IF;
468
469 IF (NOT ( p_new_esc_owner_id IS NULL
470 AND p_old_esc_owner_id IS NULL))
471 AND ( p_new_esc_owner_id IS NULL
472 OR p_old_esc_owner_id IS NULL
473 OR p_new_esc_owner_id <> p_old_esc_owner_id)
474 THEN
475 l_esc_owner_id_chan_flag := 'Y';
476 l_new_esc_owner_id := p_new_esc_owner_id;
477 l_old_esc_owner_id := p_old_esc_owner_id;
478 END IF;
479
480 IF (NOT ( p_new_esc_terr_id IS NULL
481 AND p_old_esc_terr_id IS NULL))
482 AND ( p_new_esc_terr_id IS NULL
483 OR p_old_esc_terr_id IS NULL
484 OR p_new_esc_terr_id <> p_old_esc_terr_id)
485 THEN
486 l_esc_terr_id_chan_flag := 'Y';
487 l_new_esc_terr_id := p_new_esc_terr_id;
488 l_old_esc_terr_id := p_old_esc_terr_id;
489 END IF;
490
491 IF (NOT ( p_new_not_period IS NULL
492 AND p_old_not_period IS NULL))
493 AND ( p_new_not_period IS NULL
494 OR p_old_not_period IS NULL
495 OR p_new_not_period <> p_old_not_period)
496 THEN
497 l_not_period_chan_flag := 'Y';
498 l_new_not_period := p_new_not_period;
499 l_old_not_period := p_old_not_period;
500 END IF;
501
502 IF (NOT ( p_new_not_period_uom IS NULL
503 AND p_old_not_period_uom IS NULL))
504 AND ( p_new_not_period_uom IS NULL
505 OR p_old_not_period_uom IS NULL
506 OR p_new_not_period_uom <> p_old_not_period_uom)
507 THEN
508 l_not_period_uom_chan_flag := 'Y';
509 l_new_not_period_uom := p_new_not_period_uom;
510 l_old_not_period_uom := p_old_not_period_uom;
511 END IF;
512
513 IF (NOT ( p_new_owner_id IS NULL
514 AND p_old_owner_id IS NULL))
515 AND ( p_new_owner_id IS NULL
516 OR p_old_owner_id IS NULL
517 OR p_new_owner_id <> p_old_owner_id)
518 THEN
519 l_owner_id_chan_flag := 'Y';
520 l_new_owner_id := p_new_owner_id;
521 l_old_owner_id := p_old_owner_id;
522 END IF;
523
524 IF (NOT ( p_new_owner_type_code IS NULL
525 AND p_old_owner_type_code IS NULL))
526 AND ( p_new_owner_type_code IS NULL
527 OR p_old_owner_type_code IS NULL
528 OR p_new_owner_type_code <> p_old_owner_type_code)
529 THEN
530 l_owner_type_code_chan_flag := 'Y';
531 l_new_owner_type_code := p_new_owner_type_code;
532 l_old_owner_type_code := p_old_owner_type_code;
533 END IF;
534
535 IF (NOT ( p_new_parent_task_id IS NULL
536 AND p_old_parent_task_id IS NULL))
537 AND ( p_new_parent_task_id IS NULL
538 OR p_old_parent_task_id IS NULL
539 OR p_new_parent_task_id <> p_old_parent_task_id)
540 THEN
541 l_parent_task_id_chan_flag := 'Y';
542 l_new_parent_task_id := p_new_parent_task_id;
543 l_old_parent_task_id := p_old_parent_task_id;
544 END IF;
545
546 IF (NOT ( p_new_task_name IS NULL
547 AND p_old_task_name IS NULL))
548 AND ( p_new_task_name IS NULL
549 OR p_old_task_name IS NULL
550 OR p_new_task_name <> p_old_task_name)
551 THEN
552 l_task_name_chan_flag := 'Y';
553 l_new_task_name := p_new_task_name;
554 l_old_task_name := p_old_task_name;
555 END IF;
556
557 IF (NOT ( p_new_per_complete IS NULL
558 AND p_old_per_complete IS NULL))
559 AND ( p_new_per_complete IS NULL
560 OR p_old_per_complete IS NULL
561 OR p_new_per_complete <> p_old_per_complete)
562 THEN
563 l_per_complete_chan_flag := 'Y';
564 l_new_per_complete := p_new_per_complete;
565 l_old_per_complete := p_old_per_complete;
566 END IF;
567
568 IF (NOT ( p_new_planned_effort IS NULL
569 AND p_old_planned_effort IS NULL))
570 AND ( p_new_planned_effort IS NULL
571 OR p_old_planned_effort IS NULL
572 OR p_new_planned_effort <> p_old_planned_effort)
573 THEN
574 l_planned_effort_chan_flag := 'Y';
575 l_new_planned_effort := p_new_planned_effort;
576 l_old_planned_effort := p_old_planned_effort;
577 END IF;
578
579 IF (NOT ( p_new_planned_effort_uom IS NULL
580 AND p_old_planned_effort_uom IS NULL))
581 AND ( p_new_planned_effort_uom IS NULL
582 OR p_old_planned_effort_uom IS NULL
583 OR p_new_planned_effort_uom <> p_old_planned_effort_uom)
584 THEN
585 l_planned_effort_uom_chan_flag := 'Y';
586 l_new_planned_effort_uom := p_new_planned_effort_uom;
587 l_old_planned_effort_uom := p_old_planned_effort_uom;
588 END IF;
589
590 IF (NOT ( p_new_planned_end_date IS NULL
591 AND p_old_planned_end_date IS NULL))
592 AND ( p_new_planned_end_date IS NULL
593 OR p_old_planned_end_date IS NULL
594 OR p_new_planned_end_date <> p_old_planned_end_date)
595 THEN
596 l_planned_end_date_chan_flag := 'Y';
597 l_new_planned_end_date := p_new_planned_end_date;
598 l_old_planned_end_date := p_old_planned_end_date;
599 END IF;
600
601 IF (NOT ( p_new_planned_start_date IS NULL
602 AND p_old_planned_start_date IS NULL))
603 AND ( p_new_planned_start_date IS NULL
604 OR p_old_planned_start_date IS NULL
605 OR p_new_planned_start_date <> p_old_planned_start_date)
606 THEN
607 l_planned_start_date_chan_flag := 'Y';
608 l_new_planned_start_date := p_new_planned_start_date;
609 l_old_planned_start_date := p_old_planned_start_date;
610 END IF;
611
612 IF (NOT ( p_new_reason_code IS NULL
613 AND p_old_reason_code IS NULL))
614 AND ( p_new_reason_code IS NULL
615 OR p_old_reason_code IS NULL
616 OR p_new_reason_code <> p_old_reason_code)
617 THEN
618 l_reason_code_chan_flag := 'Y';
619 l_new_reason_code := p_new_reason_code;
620 l_old_reason_code := p_old_reason_code;
621 END IF;
622
623 IF (NOT ( p_new_recurrence_rule_id IS NULL
624 AND p_old_recurrence_rule_id IS NULL))
625 AND ( p_new_recurrence_rule_id IS NULL
626 OR p_old_recurrence_rule_id IS NULL
627 OR p_new_recurrence_rule_id <> p_old_recurrence_rule_id)
628 THEN
629 l_recurrence_rule_id_chan_flag := 'Y';
630 l_new_recurrence_rule_id := p_new_recurrence_rule_id;
631 l_old_recurrence_rule_id := p_old_recurrence_rule_id;
632 END IF;
633
634 IF (NOT ( p_new_sched_end_date IS NULL
635 AND p_old_sched_end_date IS NULL))
636 AND ( p_new_sched_end_date IS NULL
637 OR p_old_sched_end_date IS NULL
638 OR p_new_sched_end_date <> p_old_sched_end_date)
639 THEN
640 l_sched_end_date_chan_flag := 'Y';
641 l_new_sched_end_date := p_new_sched_end_date;
642 l_old_sched_end_date := p_old_sched_end_date;
643 END IF;
644
645 IF (NOT ( p_new_sched_start_date IS NULL
646 AND p_old_sched_start_date IS NULL))
647 AND ( p_new_sched_start_date IS NULL
648 OR p_old_sched_start_date IS NULL
649 OR p_new_sched_start_date <> p_old_sched_start_date)
650 THEN
651 l_sched_start_date_chan_flag := 'Y';
652 l_new_sched_start_date := p_new_sched_start_date;
653 l_old_sched_start_date := p_old_sched_start_date;
654 END IF;
655
656 IF (NOT ( p_new_src_obj_id IS NULL
657 AND p_old_src_obj_id IS NULL))
658 AND ( p_new_src_obj_id IS NULL
659 OR p_old_src_obj_id IS NULL
660 OR p_new_src_obj_id <> p_old_src_obj_id)
661 THEN
662 l_src_obj_id_chan_flag := 'Y';
663 l_new_src_obj_id := p_new_src_obj_id;
664 l_old_src_obj_id := p_old_src_obj_id;
665 END IF;
666
667 IF (NOT ( p_new_src_obj_name IS NULL
668 AND p_old_src_obj_name IS NULL))
669 AND ( p_new_src_obj_name IS NULL
670 OR p_old_src_obj_name IS NULL
671 OR p_new_src_obj_name <> p_old_src_obj_name)
672 THEN
673 l_src_obj_name_chan_flag := 'Y';
674 l_new_src_obj_name := p_new_src_obj_name;
675 l_old_src_obj_name := p_old_src_obj_name;
676 END IF;
677
678 IF (NOT ( p_new_src_obj_type_code IS NULL
679 AND p_old_src_obj_type_code IS NULL))
680 AND ( p_new_src_obj_type_code IS NULL
681 OR p_old_src_obj_type_code IS NULL
682 OR p_new_src_obj_type_code <> p_old_src_obj_type_code)
683 THEN
684 l_src_obj_type_code_chan_flag := 'Y';
685 l_new_src_obj_type_code := p_new_src_obj_type_code;
686 l_old_src_obj_type_code := p_old_src_obj_type_code;
687 END IF;
688
689 IF (NOT ( p_new_task_priority_id IS NULL
690 AND p_old_task_priority_id IS NULL))
691 AND ( p_new_task_priority_id IS NULL
692 OR p_old_task_priority_id IS NULL
693 OR p_new_task_priority_id <> p_old_task_priority_id)
694 THEN
695 l_task_priority_id_chan_flag := 'Y';
696 l_new_task_priority_id := p_new_task_priority_id;
697 l_old_task_priority_id := p_old_task_priority_id;
698 END IF;
699
700 IF (NOT ( p_new_task_status_id IS NULL
701 AND p_old_task_status_id IS NULL))
702 AND ( p_new_task_status_id IS NULL
703 OR p_old_task_status_id IS NULL
704 OR p_new_task_status_id <> p_old_task_status_id)
705 THEN
706 l_task_status_id_chan_flag := 'Y';
707 l_new_task_status_id := p_new_task_status_id;
708 l_old_task_status_id := p_old_task_status_id;
709 END IF;
710
711 IF (NOT ( p_new_task_type_id IS NULL
712 AND p_old_task_type_id IS NULL))
713 AND ( p_new_task_type_id IS NULL
714 OR p_old_task_type_id IS NULL
715 OR p_new_task_type_id <> p_old_task_type_id)
716 THEN
717 l_task_type_id_chan_flag := 'Y';
718 l_new_task_type_id := p_new_task_type_id;
719 l_old_task_type_id := p_old_task_type_id;
720 END IF;
721
722 IF (NOT ( p_new_timezone_id IS NULL
723 AND p_old_timezone_id IS NULL))
724 AND ( p_new_timezone_id IS NULL
725 OR p_old_timezone_id IS NULL
726 OR p_new_timezone_id <> p_old_timezone_id)
727 THEN
728 l_timezone_id_chan_flag := 'Y';
729 l_new_timezone_id := p_new_timezone_id;
730 l_old_timezone_id := p_old_timezone_id;
731 END IF;
732
733 IF (NOT ( p_new_workflow_process_id IS NULL
734 AND p_old_workflow_process_id IS NULL))
735 AND ( p_new_workflow_process_id IS NULL
736 OR p_old_workflow_process_id IS NULL
737 OR p_new_workflow_process_id <> p_old_workflow_process_id)
738 THEN
739 l_workflow_chan_flag := 'Y';
740 l_new_workflow_process_id := p_new_workflow_process_id;
741 l_old_workflow_process_id := p_old_workflow_process_id;
742 END IF;
743
744 IF (NOT ( p_new_owner_territory_id IS NULL
745 AND p_old_owner_territory_id IS NULL))
746 AND ( p_new_owner_territory_id IS NULL
747 OR p_old_owner_territory_id IS NULL
748 OR p_new_owner_territory_id <> p_old_owner_territory_id)
749 THEN
750 l_owner_territory_id_chan_flag := 'Y';
751 l_new_owner_territory_id := p_new_owner_territory_id;
752 l_old_owner_territory_id := p_old_owner_territory_id;
753 END IF;
754
755 IF (NOT ( p_new_escalation_level IS NULL
756 AND p_old_escalation_level IS NULL))
757 AND ( p_new_escalation_level IS NULL
758 OR p_old_escalation_level IS NULL
759 OR p_new_escalation_level <> p_old_escalation_level)
760 THEN
761 l_escalation_level_chan_flag := 'Y';
762 l_new_escalation_level := p_new_escalation_level;
763 l_old_escalation_level := p_old_escalation_level;
764 END IF;
765
766 IF (NOT ( p_new_description IS NULL
767 AND p_old_description IS NULL))
768 AND ( p_new_description IS NULL
769 OR p_old_description IS NULL
770 OR p_new_description <> p_old_description)
771 THEN
772 l_description_chan_flag := 'Y';
773 l_new_description := p_new_description;
774 l_old_description := p_old_description;
775 END IF;
776
777 IF (NOT ( p_new_date_selected IS NULL
778 AND p_old_date_selected IS NULL))
779 AND ( p_new_date_selected IS NULL
780 OR p_old_date_selected IS NULL
781 OR p_new_date_selected <> p_old_date_selected)
782 THEN
783 l_date_selected_chan_flag := 'Y';
784 l_new_date_selected := p_new_date_selected;
785 l_old_date_selected := p_old_date_selected;
786 END IF;
787
788 IF (NOT ( p_new_location_id IS NULL
789 AND p_old_location_id IS NULL))
790 AND ( p_new_location_id IS NULL
791 OR p_old_location_id IS NULL
792 OR p_new_location_id <> p_old_location_id)
793 THEN
794 l_location_id_chan_flag := 'Y';
795 l_new_location_id := p_new_location_id;
796 l_old_location_id := p_old_location_id;
797 END IF;
798
799 -- ---------------------------------------
800 -- Call to Flags
801 -- ---------------------------------------
802 IF (NOT ( p_new_billable_flag IS NULL
803 AND p_old_billable_flag IS NULL))
804 AND ( p_new_billable_flag IS NULL
805 OR p_old_billable_flag IS NULL
806 OR p_new_billable_flag <> p_old_billable_flag)
807 THEN
808 l_billable_chan_flag := 'Y';
809 END IF;
810
811 IF (NOT ( p_new_device1_flag IS NULL
812 AND p_old_device1_flag IS NULL))
813 AND ( p_new_device1_flag IS NULL
814 OR p_old_device1_flag IS NULL
815 OR p_new_device1_flag <> p_old_device1_flag)
816 THEN
817 l_device1_chan_flag := 'Y';
818 END IF;
819
820 IF (NOT ( p_new_device2_flag IS NULL
821 AND p_old_device2_flag IS NULL))
822 AND ( p_new_device2_flag IS NULL
823 OR p_old_device2_flag IS NULL
824 OR p_new_device2_flag <> p_old_device2_flag)
825 THEN
826 l_device2_chan_flag := 'Y';
827 END IF;
828
829 IF (NOT ( p_new_device3_flag IS NULL
830 AND p_old_device3_flag IS NULL))
831 AND ( p_new_device3_flag IS NULL
832 OR p_old_device3_flag IS NULL
833 OR p_new_device3_flag <> p_old_device3_flag)
834 THEN
835 l_device3_chan_flag := 'Y';
836 END IF;
837
838 IF (NOT ( p_new_esc_flag IS NULL
839 AND p_old_esc_flag IS NULL))
840 AND ( p_new_esc_flag IS NULL
841 OR p_old_esc_flag IS NULL
842 OR p_new_esc_flag <> p_old_esc_flag)
843 THEN
844 l_esc_chan_flag := 'Y';
845 END IF;
846
847 IF (NOT ( p_new_holiday_flag IS NULL
848 AND p_old_holiday_flag IS NULL))
849 AND ( p_new_holiday_flag IS NULL
850 OR p_old_holiday_flag IS NULL
851 OR p_new_holiday_flag <> p_old_holiday_flag)
852 THEN
853 l_holiday_chan_flag := 'Y';
854 END IF;
855
856 IF (NOT ( p_new_laptop_flag IS NULL
857 AND p_old_laptop_flag IS NULL))
858 AND ( p_new_laptop_flag IS NULL
859 OR p_old_laptop_flag IS NULL
860 OR p_new_laptop_flag <> p_old_laptop_flag)
861 THEN
862 l_laptop_chan_flag := 'Y';
863 END IF;
864
865 IF (NOT ( p_new_milestone_flag IS NULL
866 AND p_old_milestone_flag IS NULL))
867 AND ( p_new_milestone_flag IS NULL
868 OR p_old_milestone_flag IS NULL
869 OR p_new_milestone_flag <> p_old_milestone_flag)
870 THEN
871 l_milestone_chan_flag := 'Y';
872 END IF;
873
874 IF (NOT ( p_new_multi_booked_flag IS NULL
875 AND p_old_multi_booked_flag IS NULL))
876 AND ( p_new_multi_booked_flag IS NULL
877 OR p_old_multi_booked_flag IS NULL
878 OR p_new_multi_booked_flag <> p_old_multi_booked_flag)
879 THEN
880 l_multi_booked_chan_flag := 'Y';
881 END IF;
882
883 IF (NOT ( p_new_palm_flag IS NULL
884 AND p_old_palm_flag IS NULL))
885 AND ( p_new_palm_flag IS NULL
886 OR p_old_palm_flag IS NULL
887 OR p_new_palm_flag <> p_old_palm_flag)
888 THEN
889 l_palm_chan_flag := 'Y';
890 END IF;
891
892 IF (NOT ( p_new_private_flag IS NULL
893 AND p_old_private_flag IS NULL))
894 AND ( p_new_private_flag IS NULL
895 OR p_old_private_flag IS NULL
896 OR p_new_private_flag <> p_old_private_flag)
897 THEN
898 l_private_chan_flag := 'Y';
899 END IF;
900
901 IF (NOT ( p_new_publish_flag IS NULL
902 AND p_old_publish_flag IS NULL))
903 AND ( p_new_publish_flag IS NULL
904 OR p_old_publish_flag IS NULL
905 OR p_new_publish_flag <> p_old_publish_flag)
906 THEN
907 l_publish_chan_flag := 'Y';
908 END IF;
909
910 IF (NOT ( p_new_restrict_closure_flag IS NULL
911 AND p_old_restrict_closure_flag IS NULL))
912 AND ( p_new_restrict_closure_flag IS NULL
913 OR p_old_restrict_closure_flag IS NULL
914 OR p_new_restrict_closure_flag <> p_old_restrict_closure_flag)
915 THEN
916 l_restrict_closure_chan_flag := 'Y';
917 END IF;
918
919 IF (NOT ( p_new_multi_booked_flag IS NULL
920 AND p_old_multi_booked_flag IS NULL))
921 AND ( p_new_multi_booked_flag IS NULL
922 OR p_old_multi_booked_flag IS NULL
923 OR p_new_multi_booked_flag <> p_old_multi_booked_flag)
924 THEN
925 l_multi_booked_chan_flag := 'Y';
926 END IF;
927
928 IF (NOT ( p_new_wince_flag IS NULL
929 AND p_old_wince_flag IS NULL))
930 AND ( p_new_wince_flag IS NULL
931 OR p_old_wince_flag IS NULL
932 OR p_new_wince_flag <> p_old_wince_flag)
933 THEN
934 l_wince_chan_flag := 'Y';
935 END IF;
936
937 IF (NOT ( p_new_soft_bound_flag IS NULL
938 AND p_old_soft_bound_flag IS NULL))
939 AND ( p_new_soft_bound_flag IS NULL
940 OR p_old_soft_bound_flag IS NULL
941 OR p_new_soft_bound_flag <> p_old_soft_bound_flag)
942 THEN
943 l_soft_bound_chan_flag := 'Y';
944 END IF;
945
946 IF (NOT ( p_new_not_flag IS NULL
947 AND p_old_not_flag IS NULL))
948 AND ( p_new_not_flag IS NULL
949 OR p_old_not_flag IS NULL
950 OR p_new_not_flag <> p_old_not_flag)
951 THEN
952 l_not_chan_flag := 'Y';
953 END IF;
954
955 --dbms_output.put_line(' after flags changed ');
956
957 -- ---------------------------------------
958 -- Call to Table Handler
959 -- ---------------------------------------
960 /* IF l_address_chan_flag = 'Y'
961 OR l_status_chan_flag = 'Y'
962 OR l_bound_chan_flag = 'Y'
963 OR l_costs_chan_flag = 'Y'
964 OR l_currency_code_chan_flag = 'Y'
965 OR l_customer_id_chan_flag = 'Y'
966 OR l_cust_account_chan_flag = 'Y'
967 OR l_duration_chan_flag = 'Y'
968 OR l_duration_uom_chan_flag = 'Y'
969 OR l_workflow_chan_flag = 'Y'
970 OR l_billable_chan_flag = 'Y'
971 OR l_device1_chan_flag = 'Y'
972 OR l_esc_owner_id_chan_flag = 'Y'
973 OR l_esc_terr_id_chan_flag = 'Y'
974 OR l_not_period_chan_flag = 'Y'
975 OR l_not_period_uom_chan_flag = 'Y'
976 OR l_owner_id_chan_flag = 'Y'
977 OR l_owner_type_code_chan_flag = 'Y'
978 OR l_parent_task_id_chan_flag = 'Y'
979 OR l_per_complete_chan_flag = 'Y'
980 OR l_planned_effort_chan_flag = 'Y'
981 OR l_planned_effort_uom_chan_flag = 'Y'
982 OR l_planned_end_date_chan_flag = 'Y'
983 OR l_planned_start_date_chan_flag = 'Y'
984 OR l_reason_code_chan_flag = 'Y'
985 OR l_recurrence_rule_id_chan_flag = 'Y'
986 OR l_sched_end_date_chan_flag = 'Y'
987 OR l_sched_start_date_chan_flag = 'Y'
988 OR l_src_obj_id_chan_flag = 'Y'
989 OR l_src_obj_name_chan_flag = 'Y'
990 OR l_src_obj_type_code_chan_flag = 'Y'
991 OR l_task_priority_id_chan_flag = 'Y'
992 OR l_task_status_id_chan_flag = 'Y'
993 OR l_task_type_id_chan_flag = 'Y'
994 OR l_timezone_id_chan_flag = 'Y'
995 OR l_not_chan_flag = 'Y'
996 OR l_palm_chan_flag = 'Y'
997 OR l_private_chan_flag = 'Y'
998 OR l_publish_chan_flag = 'Y'
999 OR l_restrict_closure_chan_flag = 'Y'
1000 OR l_wince_chan_flag = 'Y'
1001 OR l_soft_bound_chan_flag = 'Y'
1002 OR l_billable_chan_flag = 'Y'
1003 OR l_device1_chan_flag = 'Y'
1004 OR l_device2_chan_flag = 'Y'
1005 OR l_device3_chan_flag = 'Y'
1006 OR l_esc_chan_flag = 'Y'
1007 OR l_holiday_chan_flag = 'Y'
1008 OR l_laptop_chan_flag = 'Y'
1009 OR l_milestone_chan_flag = 'Y'
1010 OR l_multi_booked_chan_flag = 'Y'
1011 OR l_task_name_chan_flag = 'Y'
1012 OR l_owner_territory_id_chan_flag = 'Y'
1013 OR l_escalation_level_chan_flag = 'Y'
1014 OR l_description_chan_flag = 'Y'
1015 OR l_date_selected_chan_flag = 'Y'
1016 THEN
1017 */
1018
1019 OPEN c_audit;
1020
1021 FETCH c_audit INTO l_task_audit_id;
1022
1023 CLOSE c_audit;
1024
1025 jtf_task_audits_pvt.insert_row (
1026 x_rowid => l_rowid,
1027 x_task_audit_id => l_task_audit_id,
1028 x_new_notification_period => l_new_not_period,
1029 x_old_notification_period_uom => l_old_not_period,
1030 x_new_notification_period_uom => l_new_not_period_uom,
1031 x_old_parent_task_id => l_old_parent_task_id,
1032 x_new_parent_task_id => l_new_parent_task_id,
1033 x_old_recurrence_rule_id => l_old_recurrence_rule_id,
1034 x_new_recurrence_rule_id => l_new_recurrence_rule_id,
1035 x_palm_changed_flag => l_palm_chan_flag,
1036 x_wince_changed_flag => l_wince_chan_flag,
1037 x_laptop_changed_flag => l_laptop_chan_flag,
1038 x_device1_changed_flag => l_device1_chan_flag,
1039 x_device2_changed_flag => l_device2_chan_flag,
1040 x_device3_changed_flag => l_device3_chan_flag,
1041 x_old_currency_code => l_old_currency_code,
1042 x_new_currency_code => l_new_currency_code,
1043 x_old_costs => l_old_costs,
1044 x_new_costs => l_new_costs,
1045 x_task_id => l_task_id,
1046 x_old_task_type_id => l_old_task_type_id,
1047 x_new_task_type_id => l_new_task_type_id,
1048 x_old_task_status_id => l_old_task_status_id,
1049 x_new_task_status_id => l_new_task_status_id,
1050 x_old_task_priority_id => l_old_task_priority_id,
1051 x_new_task_priority_id => l_new_task_priority_id,
1052 x_old_owner_id => l_old_owner_id,
1053 x_new_owner_id => l_new_owner_id,
1054 x_old_owner_type_code => l_old_owner_type_code,
1055 x_new_owner_type_code => l_new_owner_type_code,
1056 x_old_assigned_by_id => l_old_assigned_by_id,
1057 x_new_assigned_by_id => l_new_assigned_by_id,
1058 x_old_cust_account_id => l_old_cust_account_id,
1059 x_new_cust_account_id => l_new_cust_account_id,
1060 x_old_customer_id => l_old_customer_id,
1061 x_new_customer_id => l_new_customer_id,
1062 x_old_address_id => l_old_address_id,
1063 x_new_address_id => l_new_address_id,
1064 x_old_planned_start_date => l_old_planned_start_date,
1065 x_new_planned_start_date => l_new_planned_start_date,
1066 x_old_planned_end_date => l_old_planned_end_date,
1067 x_new_planned_end_date => l_new_planned_end_date,
1068 x_old_scheduled_start_date => l_old_sched_start_date,
1069 x_new_scheduled_start_date => l_new_sched_start_date,
1070 x_old_scheduled_end_date => l_old_sched_end_date,
1071 x_new_scheduled_end_date => l_new_sched_end_date,
1072 x_old_actual_start_date => l_old_actual_start_date,
1073 x_new_actual_start_date => l_new_actual_start_date,
1074 x_old_actual_end_date => l_old_actual_end_date,
1075 x_new_actual_end_date => l_new_actual_end_date,
1076 x_old_source_object_type_code => l_old_src_obj_type_code,
1077 x_new_source_object_type_code => l_new_src_obj_type_code,
1078 x_old_timezone_id => l_old_timezone_id,
1079 x_new_timezone_id => l_new_timezone_id,
1080 x_old_source_object_id => l_old_src_obj_id,
1081 x_new_source_object_id => l_new_src_obj_id,
1082 x_old_source_object_name => l_old_src_obj_name,
1083 x_new_source_object_name => l_new_src_obj_name,
1084 x_old_duration => l_old_duration,
1085 x_new_duration => l_new_duration,
1086 x_old_duration_uom => l_old_duration_uom,
1087 x_new_duration_uom => l_new_duration_uom,
1088 x_old_planned_effort => l_old_planned_effort,
1089 x_new_planned_effort => l_new_planned_effort,
1090 x_old_planned_effort_uom => l_old_planned_effort_uom,
1091 x_new_planned_effort_uom => l_new_planned_effort_uom,
1092 x_old_actual_effort => l_old_actual_effort,
1093 x_new_actual_effort => l_new_actual_effort,
1094 x_old_actual_effort_uom => l_old_actual_effort_uom,
1095 x_new_actual_effort_uom => l_new_actual_effort_uom,
1096 x_old_percentage_complete => l_old_per_complete,
1097 x_new_percentage_complete => l_new_per_complete,
1098 x_old_reason_code => l_old_reason_code,
1099 x_new_reason_code => l_new_reason_code,
1100 x_private_changed_flag => l_private_chan_flag,
1101 x_publish_changed_flag => l_publish_chan_flag,
1102 x_restrict_closure_change_flag => l_restrict_closure_chan_flag,
1103 x_multi_booked_changed_flag => l_multi_booked_chan_flag,
1104 x_milestone_changed_flag => l_milestone_chan_flag,
1105 x_holiday_changed_flag => l_holiday_chan_flag,
1106 x_billable_changed_flag => l_billable_chan_flag,
1107 x_old_bound_mode_code => l_old_bound_mode_code,
1108 x_new_bound_mode_code => l_new_bound_mode_code,
1109 x_soft_bound_changed_flag => l_soft_bound_chan_flag,
1110 x_old_workflow_process_id => l_old_workflow_process_id,
1111 x_new_workflow_process_id => l_new_workflow_process_id,
1112 x_notification_changed_flag => l_not_chan_flag,
1113 x_old_notification_period => l_old_not_period,
1114 x_old_task_name => l_old_task_name,
1115 x_new_task_name => l_new_task_name,
1116 x_old_description => l_old_description,
1117 x_new_description => l_new_description,
1118 x_creation_date => SYSDATE,
1119 x_created_by => jtf_task_utl.created_by,
1120 x_last_update_date => SYSDATE,
1121 x_last_updated_by => jtf_task_utl.updated_by,
1122 x_last_update_login => jtf_task_utl.login_id,
1123 x_object_version_number => p_object_version_number ,
1124 x_old_owner_territory_id => l_old_owner_territory_id,
1125 x_new_owner_territory_id => l_new_owner_territory_id,
1126 x_new_escalation_level => l_new_escalation_level,
1127 x_old_escalation_level => l_old_escalation_level,
1128 x_new_date_selected => l_new_date_selected,
1129 x_old_date_selected => l_old_date_selected,
1130 x_new_location_id => l_new_location_id,
1131 x_old_location_id => l_old_location_id
1132 );
1133
1134 OPEN ta_cur1 (l_rowid);
1135 FETCH ta_cur1 INTO x;
1136
1137 IF ta_cur1%NOTFOUND
1138 THEN
1139 x_return_status := fnd_api.g_ret_sts_unexp_error;
1140 RAISE fnd_api.g_exc_unexpected_error;
1141 ELSE
1142 NULL;
1143 END IF;
1144
1145 x_task_audit_id := l_task_audit_id ;
1146
1147 IF fnd_api.to_boolean (p_commit)
1148 THEN
1149 COMMIT WORK;
1150 END IF;
1151
1152 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1153 EXCEPTION
1154 WHEN fnd_api.g_exc_error
1155 THEN
1156 ROLLBACK TO process_task_audit_pvt;
1157 x_return_status := fnd_api.g_ret_sts_error;
1158 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1159 WHEN fnd_api.g_exc_unexpected_error
1160 THEN
1161 ROLLBACK TO process_task_audit_pvt;
1162 x_return_status := fnd_api.g_ret_sts_unexp_error;
1163 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1164 WHEN OTHERS
1165 THEN
1166 ROLLBACK TO process_task_audit_pvt;
1167 x_return_status := fnd_api.g_ret_sts_unexp_error;
1168
1169 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1170 THEN
1171 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1172 END IF;
1173
1174 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1175 END;
1176
1177 PROCEDURE create_task_audits (
1178 p_api_version IN NUMBER,
1179 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1180 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1181 p_object_version_number IN NUMBER,
1182 p_task_id IN NUMBER,
1183 p_new_billable_flag IN VARCHAR2 DEFAULT NULL,
1184 p_new_device1_flag IN VARCHAR2 DEFAULT NULL,
1185 p_new_device2_flag IN VARCHAR2 DEFAULT NULL,
1186 p_new_device3_flag IN VARCHAR2 DEFAULT NULL,
1187 p_new_esc_flag IN VARCHAR2 DEFAULT NULL,
1188 p_new_holiday_flag IN VARCHAR2 DEFAULT NULL,
1189 p_new_laptop_flag IN VARCHAR2 DEFAULT NULL,
1190 p_new_milestone_flag IN VARCHAR2 DEFAULT NULL,
1191 p_new_multi_booked_flag IN VARCHAR2 DEFAULT NULL,
1192 p_new_not_flag IN VARCHAR2 DEFAULT NULL,
1193 p_new_palm_flag IN VARCHAR2 DEFAULT NULL,
1194 p_new_private_flag IN VARCHAR2 DEFAULT NULL,
1195 p_new_publish_flag IN VARCHAR2 DEFAULT NULL,
1196 p_new_restrict_closure_flag IN VARCHAR2 DEFAULT NULL,
1197 p_new_wince_flag IN VARCHAR2 DEFAULT NULL,
1198 p_new_soft_bound_flag IN VARCHAR2 DEFAULT NULL,
1199 p_new_actual_effort IN NUMBER DEFAULT NULL,
1200 p_new_actual_effort_uom IN VARCHAR2 DEFAULT NULL,
1201 p_new_actual_end_date IN DATE DEFAULT NULL,
1202 p_new_actual_start_date IN DATE DEFAULT NULL,
1203 p_new_address_id IN NUMBER DEFAULT NULL,
1204 p_new_assigned_by_id IN NUMBER DEFAULT NULL,
1205 p_new_bound_mode_code IN VARCHAR2 DEFAULT NULL,
1206 p_new_costs IN NUMBER DEFAULT NULL,
1207 p_new_currency_code IN VARCHAR2 DEFAULT NULL,
1208 p_new_customer_id IN NUMBER DEFAULT NULL,
1209 p_new_cust_account_id IN NUMBER DEFAULT NULL,
1210 p_new_duration IN NUMBER DEFAULT NULL,
1211 p_new_duration_uom IN VARCHAR2 DEFAULT NULL,
1212 p_new_esc_owner_id IN NUMBER DEFAULT NULL,
1213 p_new_esc_terr_id IN NUMBER DEFAULT NULL,
1214 p_new_not_period IN NUMBER DEFAULT NULL,
1215 p_new_not_period_uom IN VARCHAR2 DEFAULT NULL,
1216 p_new_org_id IN NUMBER DEFAULT NULL,
1217 p_new_owner_id IN NUMBER DEFAULT NULL,
1218 p_new_owner_type_code IN VARCHAR2 DEFAULT NULL,
1219 p_new_parent_task_id IN NUMBER DEFAULT NULL,
1220 p_new_per_complete IN NUMBER DEFAULT NULL,
1221 p_new_planned_effort IN NUMBER DEFAULT NULL,
1222 p_new_planned_effort_uom IN VARCHAR2 DEFAULT NULL,
1223 p_new_planned_end_date IN DATE DEFAULT NULL,
1224 p_new_planned_start_date IN DATE DEFAULT NULL,
1225 p_new_reason_code IN VARCHAR2 DEFAULT NULL,
1226 p_new_recurrence_rule_id IN NUMBER DEFAULT NULL,
1227 p_new_sched_end_date IN DATE DEFAULT NULL,
1228 p_new_sched_start_date IN DATE DEFAULT NULL,
1229 p_new_src_obj_id IN NUMBER DEFAULT NULL,
1230 p_new_src_obj_name IN VARCHAR2 DEFAULT NULL,
1231 p_new_src_obj_type_code IN VARCHAR2 DEFAULT NULL,
1232 p_new_task_priority_id IN NUMBER DEFAULT NULL,
1233 p_new_task_status_id IN NUMBER DEFAULT NULL,
1234 p_new_task_type_id IN NUMBER DEFAULT NULL,
1235 p_new_timezone_id IN NUMBER DEFAULT NULL,
1236 p_new_workflow_process_id IN NUMBER DEFAULT NULL,
1237 p_not_chan_flag IN VARCHAR2 DEFAULT NULL,
1238 p_new_description IN VARCHAR2 DEFAULT NULL,
1239 p_new_task_name IN VARCHAR2 DEFAULT NULL,
1240 p_new_escalation_level IN VARCHAR2 DEFAULT NULL,
1241 p_new_owner_territory_id IN NUMBER DEFAULT NULL,
1242 p_new_date_selected IN VARCHAR2 DEFAULT NULL,
1243 p_new_location_id IN NUMBER DEFAULT NULL,
1244 x_return_status OUT NOCOPY VARCHAR2,
1245 x_msg_count OUT NOCOPY NUMBER,
1246 x_msg_data OUT NOCOPY VARCHAR2,
1247 x_task_audit_id OUT NOCOPY NUMBER
1248 )
1249 IS
1250 l_api_name CONSTANT VARCHAR2(30) := 'JTF_TASK_AUDITS_PVT';
1251 l_api_version CONSTANT NUMBER := 1.0;
1252 l_rowid ROWID;
1253 l_init_msg_list VARCHAR2(10) := fnd_api.g_false;
1254 l_commit VARCHAR2(10) := fnd_api.g_false;
1255 l_last_update_date DATE := SYSDATE;
1256 l_last_updated_by NUMBER := -1;
1257 l_creation_date DATE := SYSDATE;
1258 l_task_audit_id NUMBER;
1259 l_new_actual_effort NUMBER := p_new_actual_effort;
1260 l_new_actual_effort_uom VARCHAR2(3) := p_new_actual_effort_uom;
1261 l_new_actual_end_date DATE := p_new_actual_end_date;
1262 l_new_actual_start_date DATE := p_new_actual_start_date;
1263 l_new_address_id NUMBER := p_new_address_id;
1264 l_new_assigned_by_id NUMBER := p_new_assigned_by_id;
1265 l_new_bound_mode_code VARCHAR2(30) := p_new_bound_mode_code;
1266 l_new_costs NUMBER := p_new_costs;
1267 l_new_currency_code VARCHAR2(15) := p_new_currency_code;
1268 l_new_customer_id NUMBER := p_new_customer_id;
1269 l_new_cust_account_id NUMBER := p_new_cust_account_id;
1270 l_new_duration NUMBER := p_new_duration;
1271 l_new_duration_uom VARCHAR2(3) := p_new_duration_uom;
1272 l_new_esc_owner_id NUMBER := p_new_esc_owner_id;
1273 l_new_esc_terr_id NUMBER := p_new_esc_terr_id;
1274 l_new_not_period NUMBER := p_new_not_period;
1275 l_new_not_period_uom VARCHAR2(3) := p_new_not_period_uom;
1276 l_new_org_id NUMBER := p_new_org_id;
1277 l_new_owner_id NUMBER := p_new_owner_id;
1278 l_new_owner_type_code VARCHAR2(30) := p_new_owner_type_code;
1279 l_new_parent_task_id NUMBER := p_new_parent_task_id;
1280 l_new_per_complete NUMBER := p_new_per_complete;
1281 l_new_planned_effort NUMBER := p_new_planned_effort;
1282 l_new_planned_effort_uom VARCHAR2(3) := p_new_planned_effort_uom;
1283 l_new_planned_end_date DATE := p_new_planned_end_date;
1284 l_new_planned_start_date DATE := p_new_planned_start_date;
1285 l_new_reason_code VARCHAR2(30) := p_new_reason_code;
1286 l_new_recurrence_rule_id NUMBER := p_new_recurrence_rule_id;
1287 l_new_sched_end_date DATE := p_new_sched_end_date;
1288 l_new_sched_start_date DATE := p_new_sched_start_date;
1289 l_new_src_obj_id NUMBER := p_new_src_obj_id;
1290 l_new_src_obj_name VARCHAR2(80) := p_new_src_obj_name;
1291 l_new_src_obj_type_code VARCHAR2(30) := p_new_src_obj_type_code;
1292 l_new_task_priority_id NUMBER := p_new_task_priority_id;
1293 l_new_task_status_id NUMBER := p_new_task_status_id;
1294 l_new_task_type_id NUMBER := p_new_task_type_id;
1295 l_new_timezone_id NUMBER := p_new_timezone_id;
1296 l_new_workflow_process_id NUMBER := p_new_workflow_process_id;
1297 l_new_description VARCHAR2(4000) := p_new_description;
1298 l_new_task_name VARCHAR2(80) := p_new_task_name;
1299 l_new_billable_flag VARCHAR2(1) := p_new_billable_flag;
1300 l_new_device1_flag VARCHAR2(1) := p_new_device1_flag;
1301 l_new_device2_flag VARCHAR2(1) := p_new_device2_flag;
1302 l_new_device3_flag VARCHAR2(1) := p_new_device3_flag;
1303 l_new_esc_flag VARCHAR2(1) := p_new_esc_flag;
1304 l_new_holiday_flag VARCHAR2(1) := p_new_holiday_flag;
1305 l_new_laptop_flag VARCHAR2(1) := p_new_laptop_flag;
1306 l_new_milestone_flag VARCHAR2(1) := p_new_milestone_flag;
1307 l_new_multi_booked_flag VARCHAR2(1) := p_new_multi_booked_flag;
1308 l_new_not_flag VARCHAR2(1) := p_new_not_flag;
1309 l_new_palm_flag VARCHAR2(1) := p_new_palm_flag;
1310 l_new_private_flag VARCHAR2(1) := p_new_private_flag;
1311 l_new_publish_flag VARCHAR2(1) := p_new_publish_flag;
1312 l_new_restrict_closure_flag VARCHAR2(1) := p_new_restrict_closure_flag;
1313 l_new_wince_flag VARCHAR2(1) := p_new_wince_flag;
1314 l_new_soft_bound_flag VARCHAR2(1) := p_new_soft_bound_flag;
1315 l_object_version_number NUMBER := p_object_version_number;
1316 l_new_owner_territory_id NUMBER := p_new_owner_territory_id;
1317 l_new_escalation_level VARCHAR2(30) := p_new_escalation_level;
1318 l_new_date_selected VARCHAR2(1) := p_new_date_selected;
1319 l_new_location_id NUMBER := p_new_location_id;
1320 l_old_description VARCHAR2(4000);
1321 l_old_billable_flag VARCHAR2(1);
1322 l_old_device1_flag VARCHAR2(1);
1323 l_old_device2_flag VARCHAR2(1);
1324 l_old_device3_flag VARCHAR2(1);
1325 l_old_esc_flag VARCHAR2(1);
1326 l_old_holiday_flag VARCHAR2(1);
1327 l_old_laptop_flag VARCHAR2(1);
1328 l_old_milestone_flag VARCHAR2(1);
1329 l_old_multi_booked_flag VARCHAR2(1);
1330 l_old_not_flag VARCHAR2(1);
1331 l_old_palm_flag VARCHAR2(1);
1332 l_old_private_flag VARCHAR2(1);
1333 l_old_publish_flag VARCHAR2(1);
1334 l_old_restrict_closure_flag VARCHAR2(1);
1335 l_old_wince_flag VARCHAR2(1);
1336 l_old_soft_bound_flag VARCHAR2(1);
1337 l_old_actual_effort NUMBER;
1338 l_old_actual_effort_uom VARCHAR2(3);
1339 l_old_actual_end_date DATE;
1340 l_old_actual_start_date DATE;
1341 l_old_address_id NUMBER;
1342 l_old_assigned_by_id NUMBER;
1343 l_old_bound_mode_code VARCHAR2(30);
1344 l_old_costs NUMBER;
1345 l_old_currency_code VARCHAR2(15);
1346 l_old_customer_id NUMBER;
1347 l_old_cust_account_id NUMBER;
1348 l_old_duration NUMBER;
1349 l_old_duration_uom VARCHAR2(3);
1350 l_old_esc_owner_id NUMBER;
1351 l_old_esc_terr_id NUMBER;
1352 l_old_not_period NUMBER;
1353 l_old_not_period_uom VARCHAR2(3);
1354 l_old_org_id NUMBER;
1355 l_old_owner_id NUMBER;
1356 l_old_owner_type_code VARCHAR2(30);
1357 l_old_parent_task_id NUMBER;
1358 l_old_per_complete NUMBER;
1359 l_old_planned_effort NUMBER;
1360 l_old_planned_effort_uom VARCHAR2(3);
1361 l_old_planned_end_date DATE;
1362 l_old_planned_start_date DATE;
1363 l_old_reason_code VARCHAR2(30);
1364 l_old_recurrence_rule_id NUMBER;
1365 l_old_sched_end_date DATE;
1366 l_old_sched_start_date DATE;
1367 l_old_src_obj_id NUMBER;
1368 l_old_src_obj_name VARCHAR2(80);
1369 l_old_src_obj_type_code VARCHAR2(30);
1370 l_old_task_priority_id NUMBER;
1371 l_old_task_status_id NUMBER;
1372 l_old_task_type_id NUMBER;
1373 l_old_timezone_id NUMBER;
1374 l_old_workflow_process_id NUMBER;
1375 l_old_task_name VARCHAR2(80);
1376 l_old_owner_territory_id NUMBER;
1377 l_old_escalation_level VARCHAR2(5);
1378 l_old_date_selected VARCHAR2(1);
1379 l_old_location_id NUMBER;
1380 x CHAR;
1381 l_creation_date DATE;
1382 l_created_by NUMBER(15);
1383 l_last_update_date DATE;
1384 x_commit VARCHAR2(1);
1385 l_last_updated_by NUMBER(15);
1386 l_last_update_login NUMBER(15);
1387 l_not_chan_flag CHAR(1) := 'N';
1388
1389 CURSOR ta_cur1 (l_rowid IN ROWID)
1390 IS
1391 SELECT 1
1392 FROM jtf_task_audits_b
1393 WHERE ROWID = l_rowid;
1394
1395 CURSOR tsk_aud (p_task_id IN NUMBER)
1396 IS
1397 SELECT attribute4,
1398 attribute5,
1399 attribute6,
1400 attribute7,
1401 attribute8,
1402 attribute9,
1403 attribute10,
1404 attribute11,
1405 attribute12,
1406 attribute13,
1407 attribute14,
1408 attribute15,
1409 attribute_category,
1410 task_id,
1411 created_by,
1412 creation_date,
1413 last_updated_by,
1414 last_update_date,
1415 last_update_login,
1416 task_number,
1417 task_type_id,
1418 task_status_id,
1419 task_priority_id,
1420 owner_id,
1421 owner_type_code,
1422 assigned_by_id,
1423 cust_account_id,
1424 customer_id,
1425 address_id,
1426 planned_start_date,
1427 palm_flag,
1428 wince_flag,
1429 laptop_flag,
1430 device1_flag,
1431 device2_flag,
1432 device3_flag,
1433 costs,
1434 currency_code,
1435 attribute1,
1436 attribute2,
1437 attribute3,
1438 notification_period,
1439 notification_period_uom,
1440 parent_task_id,
1441 recurrence_rule_id,
1442 alarm_start,
1443 alarm_start_uom,
1444 alarm_on,
1445 alarm_count,
1446 alarm_fired_count,
1447 alarm_interval,
1448 alarm_interval_uom,
1449 deleted_flag,
1450 actual_start_date,
1451 actual_end_date,
1452 source_object_type_code,
1453 timezone_id,
1454 source_object_id,
1455 source_object_name,
1456 duration,
1457 duration_uom,
1458 planned_effort,
1459 planned_effort_uom,
1460 actual_effort,
1461 actual_effort_uom,
1462 percentage_complete,
1463 reason_code,
1464 private_flag,
1465 publish_flag,
1466 restrict_closure_flag,
1467 multi_booked_flag,
1468 milestone_flag,
1469 holiday_flag,
1470 billable_flag,
1471 bound_mode_code,
1472 soft_bound_flag,
1473 workflow_process_id,
1474 notification_flag,
1475 planned_end_date,
1476 scheduled_start_date,
1477 scheduled_end_date,
1478 task_name,
1479 description,
1480 object_version_number,
1481 owner_territory_id,
1482 escalation_level,
1483 date_selected,
1484 location_id
1485 FROM jtf_tasks_vl
1486 WHERE task_id = p_task_id;
1487
1488 CURSOR c_audit
1489 IS
1490 SELECT jtf_task_audits_s.nextval
1491 FROM dual;
1492
1493 aud_rec tsk_aud%ROWTYPE;
1494 BEGIN
1495 SAVEPOINT process_task_audit_pvt;
1496
1497 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
1498 THEN
1499 RAISE fnd_api.g_exc_unexpected_error;
1500 END IF;
1501
1502 IF fnd_api.to_boolean (p_init_msg_list)
1503 THEN
1504 fnd_msg_pub.initialize;
1505 END IF;
1506
1507 x_return_status := fnd_api.g_ret_sts_success;
1508
1509 OPEN tsk_aud (p_task_id);
1510
1511 --dbms_output.put_line(' opening cursor....');
1512
1513 FETCH tsk_aud INTO aud_rec;
1514 l_old_description := aud_rec.description;
1515 l_old_billable_flag := aud_rec.billable_flag;
1516 l_old_device1_flag := aud_rec.device1_flag;
1517 l_old_device2_flag := aud_rec.device2_flag;
1518 l_old_device3_flag := aud_rec.device3_flag;
1519 l_old_esc_flag := NULL;
1520 l_old_holiday_flag := aud_rec.holiday_flag;
1521 l_old_laptop_flag := aud_rec.laptop_flag;
1522 l_old_milestone_flag := aud_rec.milestone_flag;
1523 l_old_multi_booked_flag := aud_rec.multi_booked_flag;
1524 l_old_not_flag := aud_rec.notification_flag;
1525 l_old_palm_flag := aud_rec.palm_flag;
1526 l_old_private_flag := aud_rec.private_flag;
1527 l_old_publish_flag := aud_rec.publish_flag;
1528 l_old_restrict_closure_flag := aud_rec.restrict_closure_flag;
1529 l_old_wince_flag := aud_rec.wince_flag;
1530 l_old_soft_bound_flag := aud_rec.soft_bound_flag;
1531 l_old_actual_effort := aud_rec.actual_effort;
1532 l_old_actual_effort_uom := aud_rec.actual_effort_uom;
1533 l_old_actual_end_date := aud_rec.actual_end_date;
1534 l_old_actual_start_date := aud_rec.actual_start_date;
1535 l_old_address_id := aud_rec.address_id;
1536 l_old_assigned_by_id := aud_rec.assigned_by_id;
1537 l_old_bound_mode_code := aud_rec.bound_mode_code;
1538 l_old_costs := aud_rec.costs;
1539 l_old_currency_code := aud_rec.currency_code;
1540 l_old_customer_id := aud_rec.customer_id;
1541 l_old_cust_account_id := aud_rec.cust_account_id;
1542 l_old_duration := aud_rec.duration;
1543 l_old_duration_uom := aud_rec.duration_uom;
1544 l_old_esc_owner_id := aud_rec.owner_id;
1545 l_old_esc_owner_id := NULL;
1546 l_old_esc_terr_id := aud_rec.owner_territory_id;
1547 l_old_not_period := aud_rec.notification_period;
1548 l_old_not_period_uom := aud_rec.notification_period_uom;
1549 l_old_owner_id := aud_rec.owner_id;
1550 l_old_owner_type_code := aud_rec.owner_type_code;
1551 l_old_parent_task_id := aud_rec.parent_task_id;
1552 l_old_per_complete := aud_rec.percentage_complete;
1553 l_old_planned_effort := aud_rec.planned_effort;
1554 l_old_planned_effort_uom := aud_rec.planned_effort_uom;
1555 l_old_planned_end_date := aud_rec.planned_end_date;
1556 l_old_planned_start_date := aud_rec.planned_start_date;
1557 l_old_reason_code := aud_rec.reason_code;
1558 l_old_recurrence_rule_id := aud_rec.recurrence_rule_id;
1559 l_old_sched_end_date := aud_rec.scheduled_end_date;
1560 l_old_sched_start_date := aud_rec.scheduled_start_date;
1561 l_old_src_obj_id := aud_rec.source_object_id;
1562 l_old_src_obj_name := aud_rec.source_object_name;
1563 l_old_src_obj_type_code := aud_rec.source_object_type_code;
1564 l_old_task_priority_id := aud_rec.task_priority_id;
1565 l_old_task_status_id := aud_rec.task_status_id;
1566 l_old_task_type_id := aud_rec.task_type_id;
1567 l_old_timezone_id := aud_rec.timezone_id;
1568 l_old_workflow_process_id := aud_rec.workflow_process_id;
1569 l_old_task_name := aud_rec.task_name;
1570 l_old_owner_territory_id := aud_rec.owner_territory_id;
1571 l_old_escalation_level := aud_rec.escalation_level;
1572 l_old_date_selected := aud_rec.date_selected;
1573 l_old_location_id := aud_rec.location_id;
1574 CLOSE tsk_aud;
1575
1576 --dbms_output.put_line(' calling process tasks...... ');
1577 jtf_task_audits_pvt.process_task_audits (
1578 p_api_version => 1.0,
1579 p_init_msg_list => fnd_api.g_false,
1580 p_commit => fnd_api.g_false,
1581 p_object_version_number => p_object_version_number ,
1582 x_return_status => x_return_status,
1583 x_msg_count => x_msg_count,
1584 x_msg_data => x_msg_data,
1585 p_old_billable_flag => l_old_billable_flag,
1586 p_old_device1_flag => l_old_device1_flag,
1587 p_old_device2_flag => l_old_device2_flag,
1588 p_old_device3_flag => l_old_device3_flag,
1589 p_old_esc_flag => l_old_esc_flag,
1590 p_old_holiday_flag => l_old_holiday_flag,
1591 p_old_laptop_flag => l_old_laptop_flag,
1592 p_old_milestone_flag => l_old_milestone_flag,
1593 p_old_multi_booked_flag => l_old_multi_booked_flag,
1594 p_old_not_flag => l_old_not_flag,
1595 p_old_palm_flag => l_old_palm_flag,
1596 p_old_private_flag => l_old_private_flag,
1597 p_old_publish_flag => l_old_publish_flag,
1598 p_old_restrict_closure_flag => l_old_restrict_closure_flag,
1599 p_old_wince_flag => l_old_wince_flag,
1600 p_old_soft_bound_flag => l_old_soft_bound_flag,
1601 p_new_billable_flag => l_new_billable_flag,
1602 p_new_device1_flag => l_new_device1_flag,
1603 p_new_device2_flag => l_new_device2_flag,
1604 p_new_device3_flag => l_new_device3_flag,
1605 p_new_esc_flag => l_new_esc_flag,
1606 p_new_holiday_flag => l_new_holiday_flag,
1607 p_new_laptop_flag => l_new_laptop_flag,
1608 p_new_milestone_flag => l_new_milestone_flag,
1609 p_new_multi_booked_flag => l_new_multi_booked_flag,
1610 p_new_not_flag => l_new_not_flag,
1611 p_new_palm_flag => l_new_palm_flag,
1612 p_new_private_flag => l_new_private_flag,
1613 p_new_publish_flag => l_new_publish_flag,
1614 p_new_restrict_closure_flag => l_new_restrict_closure_flag,
1615 p_new_wince_flag => l_new_wince_flag,
1616 p_new_soft_bound_flag => l_new_soft_bound_flag,
1617 p_new_actual_effort => l_new_actual_effort,
1618 p_new_actual_effort_uom => l_new_actual_effort_uom,
1619 p_new_actual_end_date => l_new_actual_end_date,
1620 p_new_actual_start_date => l_new_actual_start_date,
1621 p_new_address_id => l_new_address_id,
1622 p_new_assigned_by_id => l_new_assigned_by_id,
1623 p_new_bound_mode_code => l_new_bound_mode_code,
1624 p_new_costs => l_new_costs,
1625 p_new_currency_code => l_new_currency_code,
1626 p_new_customer_id => l_new_customer_id,
1627 p_new_cust_account_id => l_new_cust_account_id,
1628 p_new_duration => l_new_duration,
1629 p_new_duration_uom => l_new_duration_uom,
1630 p_new_esc_owner_id => l_new_esc_owner_id,
1631 p_new_esc_terr_id => l_new_esc_terr_id,
1632 p_new_not_period => l_new_not_period,
1633 p_new_not_period_uom => l_new_not_period_uom,
1634 p_new_org_id => l_new_org_id,
1635 p_new_owner_id => l_new_owner_id,
1636 p_new_owner_type_code => l_new_owner_type_code,
1637 p_new_parent_task_id => l_new_parent_task_id,
1638 p_new_per_complete => l_new_per_complete,
1639 p_new_planned_effort => l_new_planned_effort,
1640 p_new_planned_effort_uom => l_new_planned_effort_uom,
1641 p_new_planned_end_date => l_new_planned_end_date,
1642 p_new_planned_start_date => l_new_planned_start_date,
1643 p_new_reason_code => l_new_reason_code,
1644 p_new_recurrence_rule_id => l_new_recurrence_rule_id,
1645 p_new_sched_end_date => l_new_sched_end_date,
1646 p_new_sched_start_date => l_new_sched_start_date,
1647 p_new_src_obj_id => l_new_src_obj_id,
1648 p_new_src_obj_name => l_new_src_obj_name,
1649 p_new_src_obj_type_code => l_new_src_obj_type_code,
1650 p_new_task_priority_id => l_new_task_priority_id,
1651 p_new_task_status_id => l_new_task_status_id,
1652 p_new_task_type_id => l_new_task_type_id,
1653 p_new_timezone_id => l_new_timezone_id,
1654 p_new_workflow_process_id => l_new_workflow_process_id,
1655 p_not_chan_flag => l_not_chan_flag,
1656 p_old_actual_effort => l_old_actual_effort,
1657 p_old_actual_effort_uom => l_old_actual_effort_uom,
1658 p_old_actual_end_date => l_old_actual_end_date,
1659 p_old_actual_start_date => l_old_actual_start_date,
1660 p_old_address_id => l_old_address_id,
1661 p_old_assigned_by_id => l_old_assigned_by_id,
1662 p_old_bound_mode_code => l_old_bound_mode_code,
1663 p_old_costs => l_old_costs,
1664 p_old_currency_code => l_old_currency_code,
1665 p_old_customer_id => l_old_customer_id,
1666 p_old_cust_account_id => l_old_cust_account_id,
1667 p_old_duration => l_old_duration,
1668 p_old_duration_uom => l_old_duration_uom,
1669 p_old_esc_owner_id => NULL,
1670 p_old_esc_terr_id => l_old_esc_terr_id,
1671 p_old_not_period => l_old_not_period,
1672 p_old_not_period_uom => l_old_not_period_uom,
1673 p_old_org_id => NULL,
1674 p_old_owner_id => l_old_owner_id,
1675 p_old_owner_type_code => l_old_owner_type_code,
1676 p_old_parent_task_id => l_old_parent_task_id,
1677 p_old_per_complete => l_old_per_complete,
1678 p_old_planned_effort => l_old_planned_effort,
1679 p_old_planned_effort_uom => l_old_planned_effort_uom,
1680 p_old_planned_end_date => l_old_planned_end_date,
1681 p_old_planned_start_date => l_old_planned_start_date,
1682 p_old_reason_code => l_old_reason_code,
1683 p_old_recurrence_rule_id => l_old_recurrence_rule_id,
1684 p_old_sched_end_date => l_old_sched_end_date,
1685 p_old_sched_start_date => l_old_sched_start_date,
1686 p_old_src_obj_id => l_old_src_obj_id,
1687 p_old_src_obj_name => l_old_src_obj_name,
1688 p_old_src_obj_type_code => l_old_src_obj_type_code,
1689 p_old_task_priority_id => l_old_task_priority_id,
1690 p_old_task_status_id => l_old_task_status_id,
1691 p_old_task_type_id => l_old_task_type_id,
1692 p_old_timezone_id => l_old_timezone_id,
1693 p_old_workflow_process_id => l_old_workflow_process_id,
1694 p_task_id => p_task_id,
1695 p_new_description => l_new_description,
1696 p_new_task_name => l_new_task_name,
1697 p_old_description => l_old_description,
1698 p_old_task_name => l_old_task_name,
1699 p_old_escalation_level => l_old_escalation_level,
1700 p_new_escalation_level => l_new_escalation_level,
1701 p_old_owner_territory_id => l_old_owner_territory_id,
1702 p_new_owner_territory_id => l_new_owner_territory_id,
1703 p_old_date_selected => l_old_date_selected,
1704 p_new_date_selected => l_new_date_selected,
1705 p_old_location_id => l_old_location_id,
1706 p_new_location_id => l_new_location_id,
1707 x_task_audit_id => x_task_audit_id
1708 );
1709
1710 IF fnd_api.to_boolean (p_commit)
1711 THEN
1712 COMMIT WORK;
1713 END IF;
1714
1715 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1716 EXCEPTION
1717 WHEN fnd_api.g_exc_error
1718 THEN
1719 ROLLBACK TO process_task_audit_pvt;
1720 x_return_status := fnd_api.g_ret_sts_error;
1721 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1722 WHEN fnd_api.g_exc_unexpected_error
1723 THEN
1724 ROLLBACK TO process_task_audit_pvt;
1725 x_return_status := fnd_api.g_ret_sts_unexp_error;
1726 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1727 WHEN OTHERS
1728 THEN
1729 ROLLBACK TO process_task_audit_pvt;
1730 x_return_status := fnd_api.g_ret_sts_unexp_error;
1731
1732 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1733 THEN
1734 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1735 END IF;
1736
1737 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1738 END;
1739
1740 procedure INSERT_ROW (
1741 X_ROWID in out NOCOPY VARCHAR2,
1742 X_TASK_AUDIT_ID in NUMBER,
1743 X_NEW_NOTIFICATION_PERIOD in NUMBER,
1744 X_OLD_NOTIFICATION_PERIOD_UOM in VARCHAR2,
1745 X_NEW_NOTIFICATION_PERIOD_UOM in VARCHAR2,
1746 X_OLD_PARENT_TASK_ID in NUMBER,
1747 X_NEW_PARENT_TASK_ID in NUMBER,
1748 X_OLD_RECURRENCE_RULE_ID in NUMBER,
1749 X_NEW_RECURRENCE_RULE_ID in NUMBER,
1750 X_PALM_CHANGED_FLAG in VARCHAR2,
1751 X_WINCE_CHANGED_FLAG in VARCHAR2,
1752 X_LAPTOP_CHANGED_FLAG in VARCHAR2,
1753 X_DEVICE1_CHANGED_FLAG in VARCHAR2,
1754 X_DEVICE2_CHANGED_FLAG in VARCHAR2,
1755 X_DEVICE3_CHANGED_FLAG in VARCHAR2,
1756 X_OLD_CURRENCY_CODE in VARCHAR2,
1757 X_NEW_CURRENCY_CODE in VARCHAR2,
1758 X_OLD_COSTS in NUMBER,
1759 X_NEW_COSTS in NUMBER,
1760 X_TASK_ID in NUMBER,
1761 X_OLD_TASK_TYPE_ID in NUMBER,
1762 X_NEW_TASK_TYPE_ID in NUMBER,
1763 X_OLD_TASK_STATUS_ID in NUMBER,
1764 X_NEW_TASK_STATUS_ID in NUMBER,
1765 X_OLD_TASK_PRIORITY_ID in NUMBER,
1766 X_NEW_TASK_PRIORITY_ID in NUMBER,
1767 X_OLD_OWNER_ID in NUMBER,
1768 X_NEW_OWNER_ID in NUMBER,
1769 X_OLD_OWNER_TYPE_CODE in VARCHAR2,
1770 X_NEW_OWNER_TYPE_CODE in VARCHAR2,
1771 X_OLD_ASSIGNED_BY_ID in NUMBER,
1772 X_NEW_ASSIGNED_BY_ID in NUMBER,
1773 X_OLD_CUST_ACCOUNT_ID in NUMBER,
1774 X_NEW_CUST_ACCOUNT_ID in NUMBER,
1775 X_OLD_CUSTOMER_ID in NUMBER,
1776 X_NEW_CUSTOMER_ID in NUMBER,
1777 X_OLD_ADDRESS_ID in NUMBER,
1778 X_NEW_ADDRESS_ID in NUMBER,
1779 X_OLD_PLANNED_START_DATE in DATE,
1780 X_NEW_PLANNED_START_DATE in DATE,
1781 X_OLD_PLANNED_END_DATE in DATE,
1782 X_NEW_PLANNED_END_DATE in DATE,
1783 X_OLD_SCHEDULED_START_DATE in DATE,
1784 X_NEW_SCHEDULED_START_DATE in DATE,
1785 X_OLD_SCHEDULED_END_DATE in DATE,
1786 X_NEW_SCHEDULED_END_DATE in DATE,
1787 X_OLD_ACTUAL_START_DATE in DATE,
1788 X_NEW_ACTUAL_START_DATE in DATE,
1789 X_OLD_ACTUAL_END_DATE in DATE,
1790 X_NEW_ACTUAL_END_DATE in DATE,
1791 X_OLD_SOURCE_OBJECT_TYPE_CODE in VARCHAR2,
1792 X_NEW_SOURCE_OBJECT_TYPE_CODE in VARCHAR2,
1793 X_OLD_TIMEZONE_ID in NUMBER,
1794 X_NEW_TIMEZONE_ID in NUMBER,
1795 X_OLD_SOURCE_OBJECT_ID in NUMBER,
1796 X_NEW_SOURCE_OBJECT_ID in NUMBER,
1797 X_OLD_SOURCE_OBJECT_NAME in VARCHAR2,
1798 X_NEW_SOURCE_OBJECT_NAME in VARCHAR2,
1799 X_OLD_DURATION in NUMBER,
1800 X_NEW_DURATION in NUMBER,
1801 X_OLD_DURATION_UOM in VARCHAR2,
1802 X_NEW_DURATION_UOM in VARCHAR2,
1803 X_OLD_PLANNED_EFFORT in NUMBER,
1804 X_NEW_PLANNED_EFFORT in NUMBER,
1805 X_OLD_PLANNED_EFFORT_UOM in VARCHAR2,
1806 X_NEW_PLANNED_EFFORT_UOM in VARCHAR2,
1807 X_OLD_ACTUAL_EFFORT in NUMBER,
1808 X_NEW_ACTUAL_EFFORT in NUMBER,
1809 X_OLD_ACTUAL_EFFORT_UOM in VARCHAR2,
1810 X_NEW_ACTUAL_EFFORT_UOM in VARCHAR2,
1811 X_OLD_PERCENTAGE_COMPLETE in NUMBER,
1812 X_NEW_PERCENTAGE_COMPLETE in NUMBER,
1813 X_OLD_REASON_CODE in VARCHAR2,
1814 X_NEW_REASON_CODE in VARCHAR2,
1815 X_PRIVATE_CHANGED_FLAG in VARCHAR2,
1816 X_PUBLISH_CHANGED_FLAG in VARCHAR2,
1817 X_RESTRICT_CLOSURE_CHANGE_FLAG in VARCHAR2,
1818 X_MULTI_BOOKED_CHANGED_FLAG in VARCHAR2,
1819 X_MILESTONE_CHANGED_FLAG in VARCHAR2,
1820 X_HOLIDAY_CHANGED_FLAG in VARCHAR2,
1821 X_BILLABLE_CHANGED_FLAG in VARCHAR2,
1822 X_OLD_BOUND_MODE_CODE in VARCHAR2,
1823 X_NEW_BOUND_MODE_CODE in VARCHAR2,
1824 X_SOFT_BOUND_CHANGED_FLAG in VARCHAR2,
1825 X_OLD_WORKFLOW_PROCESS_ID in NUMBER,
1826 X_NEW_WORKFLOW_PROCESS_ID in NUMBER,
1827 X_NOTIFICATION_CHANGED_FLAG in VARCHAR2,
1828 X_OLD_NOTIFICATION_PERIOD in NUMBER,
1829 X_OLD_TASK_NAME in VARCHAR2,
1830 X_NEW_TASK_NAME in VARCHAR2,
1831 X_OLD_DESCRIPTION in VARCHAR2,
1832 X_NEW_DESCRIPTION in VARCHAR2,
1833 X_CREATION_DATE in DATE,
1834 X_CREATED_BY in NUMBER,
1835 X_LAST_UPDATE_DATE in DATE,
1836 X_LAST_UPDATED_BY in NUMBER,
1837 X_LAST_UPDATE_LOGIN in NUMBER,
1838 X_OBJECT_VERSION_NUMBER in NUMBER,
1839 X_OLD_OWNER_TERRITORY_ID in NUMBER,
1840 X_NEW_OWNER_TERRITORY_ID in NUMBER,
1841 X_NEW_ESCALATION_LEVEL in VARCHAR2,
1842 X_OLD_ESCALATION_LEVEL in VARCHAR2,
1843 X_OLD_DATE_SELECTED in VARCHAR2,
1844 X_NEW_DATE_SELECTED in VARCHAR2,
1845 X_OLD_LOCATION_ID in NUMBER,
1846 X_NEW_LOCATION_ID in NUMBER
1847 ) is
1848 cursor C is select ROWID from JTF_TASK_AUDITS_B
1849 where TASK_AUDIT_ID = X_TASK_AUDIT_ID
1850 ;
1851 begin
1852 insert into JTF_TASK_AUDITS_B (
1853 NEW_NOTIFICATION_PERIOD,
1854 OLD_NOTIFICATION_PERIOD_UOM,
1855 NEW_NOTIFICATION_PERIOD_UOM,
1856 OLD_PARENT_TASK_ID,
1857 NEW_PARENT_TASK_ID,
1858 OLD_RECURRENCE_RULE_ID,
1859 NEW_RECURRENCE_RULE_ID,
1860 PALM_CHANGED_FLAG,
1861 WINCE_CHANGED_FLAG,
1862 LAPTOP_CHANGED_FLAG,
1863 DEVICE1_CHANGED_FLAG,
1864 DEVICE2_CHANGED_FLAG,
1865 DEVICE3_CHANGED_FLAG,
1866 OLD_CURRENCY_CODE,
1867 NEW_CURRENCY_CODE,
1868 OLD_COSTS,
1869 NEW_COSTS,
1870 TASK_AUDIT_ID,
1871 TASK_ID,
1872 OLD_TASK_TYPE_ID,
1873 NEW_TASK_TYPE_ID,
1874 OLD_TASK_STATUS_ID,
1875 NEW_TASK_STATUS_ID,
1876 OLD_TASK_PRIORITY_ID,
1877 NEW_TASK_PRIORITY_ID,
1878 OLD_OWNER_ID,
1879 NEW_OWNER_ID,
1880 OLD_OWNER_TYPE_CODE,
1881 NEW_OWNER_TYPE_CODE,
1882 OLD_ASSIGNED_BY_ID,
1883 NEW_ASSIGNED_BY_ID,
1884 OLD_CUST_ACCOUNT_ID,
1885 NEW_CUST_ACCOUNT_ID,
1886 OLD_CUSTOMER_ID,
1887 NEW_CUSTOMER_ID,
1888 OLD_ADDRESS_ID,
1889 NEW_ADDRESS_ID,
1890 OLD_PLANNED_START_DATE,
1891 NEW_PLANNED_START_DATE,
1892 OLD_PLANNED_END_DATE,
1893 NEW_PLANNED_END_DATE,
1894 OLD_SCHEDULED_START_DATE,
1895 NEW_SCHEDULED_START_DATE,
1896 OLD_SCHEDULED_END_DATE,
1897 NEW_SCHEDULED_END_DATE,
1898 OLD_ACTUAL_START_DATE,
1899 NEW_ACTUAL_START_DATE,
1900 OLD_ACTUAL_END_DATE,
1901 NEW_ACTUAL_END_DATE,
1902 OLD_SOURCE_OBJECT_TYPE_CODE,
1903 NEW_SOURCE_OBJECT_TYPE_CODE,
1904 OLD_TIMEZONE_ID,
1905 NEW_TIMEZONE_ID,
1906 OLD_SOURCE_OBJECT_ID,
1907 NEW_SOURCE_OBJECT_ID,
1908 OLD_SOURCE_OBJECT_NAME,
1909 NEW_SOURCE_OBJECT_NAME,
1910 OLD_DURATION,
1911 NEW_DURATION,
1912 OLD_DURATION_UOM,
1913 NEW_DURATION_UOM,
1914 OLD_PLANNED_EFFORT,
1915 NEW_PLANNED_EFFORT,
1916 OLD_PLANNED_EFFORT_UOM,
1917 NEW_PLANNED_EFFORT_UOM,
1918 OLD_ACTUAL_EFFORT,
1919 NEW_ACTUAL_EFFORT,
1920 OLD_ACTUAL_EFFORT_UOM,
1921 NEW_ACTUAL_EFFORT_UOM,
1922 OLD_PERCENTAGE_COMPLETE,
1923 NEW_PERCENTAGE_COMPLETE,
1924 OLD_REASON_CODE,
1925 NEW_REASON_CODE,
1926 PRIVATE_CHANGED_FLAG,
1927 PUBLISH_CHANGED_FLAG,
1928 RESTRICT_CLOSURE_CHANGE_FLAG,
1929 MULTI_BOOKED_CHANGED_FLAG,
1930 MILESTONE_CHANGED_FLAG,
1931 HOLIDAY_CHANGED_FLAG,
1932 BILLABLE_CHANGED_FLAG,
1933 OLD_BOUND_MODE_CODE,
1934 NEW_BOUND_MODE_CODE,
1935 SOFT_BOUND_CHANGED_FLAG,
1936 OLD_WORKFLOW_PROCESS_ID,
1937 NEW_WORKFLOW_PROCESS_ID,
1938 NOTIFICATION_CHANGED_FLAG,
1939 OLD_NOTIFICATION_PERIOD,
1940 CREATION_DATE,
1941 CREATED_BY,
1942 LAST_UPDATE_DATE,
1943 LAST_UPDATED_BY,
1944 LAST_UPDATE_LOGIN,
1945 OBJECT_VERSION_NUMBER,
1946 OLD_OWNER_TERRITORY_ID,
1947 NEW_OWNER_TERRITORY_ID,
1948 NEW_ESCALATION_LEVEL,
1949 OLD_ESCALATION_LEVEL,
1950 NEW_DATE_SELECTED,
1951 OLD_DATE_SELECTED
1952 ) values (
1953 X_NEW_NOTIFICATION_PERIOD,
1954 X_OLD_NOTIFICATION_PERIOD_UOM,
1955 X_NEW_NOTIFICATION_PERIOD_UOM,
1956 X_OLD_PARENT_TASK_ID,
1957 X_NEW_PARENT_TASK_ID,
1958 X_OLD_RECURRENCE_RULE_ID,
1959 X_NEW_RECURRENCE_RULE_ID,
1960 X_PALM_CHANGED_FLAG,
1961 X_WINCE_CHANGED_FLAG,
1962 X_LAPTOP_CHANGED_FLAG,
1963 X_DEVICE1_CHANGED_FLAG,
1964 X_DEVICE2_CHANGED_FLAG,
1965 X_DEVICE3_CHANGED_FLAG,
1966 X_OLD_CURRENCY_CODE,
1967 X_NEW_CURRENCY_CODE,
1968 X_OLD_COSTS,
1969 X_NEW_COSTS,
1970 X_TASK_AUDIT_ID,
1971 X_TASK_ID,
1972 X_OLD_TASK_TYPE_ID,
1973 X_NEW_TASK_TYPE_ID,
1974 X_OLD_TASK_STATUS_ID,
1975 X_NEW_TASK_STATUS_ID,
1976 X_OLD_TASK_PRIORITY_ID,
1977 X_NEW_TASK_PRIORITY_ID,
1978 X_OLD_OWNER_ID,
1979 X_NEW_OWNER_ID,
1980 X_OLD_OWNER_TYPE_CODE,
1981 X_NEW_OWNER_TYPE_CODE,
1982 X_OLD_ASSIGNED_BY_ID,
1983 X_NEW_ASSIGNED_BY_ID,
1984 X_OLD_CUST_ACCOUNT_ID,
1985 X_NEW_CUST_ACCOUNT_ID,
1986 X_OLD_CUSTOMER_ID,
1987 X_NEW_CUSTOMER_ID,
1988 X_OLD_ADDRESS_ID,
1989 X_NEW_ADDRESS_ID,
1990 X_OLD_PLANNED_START_DATE,
1991 X_NEW_PLANNED_START_DATE,
1992 X_OLD_PLANNED_END_DATE,
1993 X_NEW_PLANNED_END_DATE,
1994 X_OLD_SCHEDULED_START_DATE,
1995 X_NEW_SCHEDULED_START_DATE,
1996 X_OLD_SCHEDULED_END_DATE,
1997 X_NEW_SCHEDULED_END_DATE,
1998 X_OLD_ACTUAL_START_DATE,
1999 X_NEW_ACTUAL_START_DATE,
2000 X_OLD_ACTUAL_END_DATE,
2001 X_NEW_ACTUAL_END_DATE,
2002 X_OLD_SOURCE_OBJECT_TYPE_CODE,
2003 X_NEW_SOURCE_OBJECT_TYPE_CODE,
2004 X_OLD_TIMEZONE_ID,
2005 X_NEW_TIMEZONE_ID,
2006 X_OLD_SOURCE_OBJECT_ID,
2007 X_NEW_SOURCE_OBJECT_ID,
2008 X_OLD_SOURCE_OBJECT_NAME,
2009 X_NEW_SOURCE_OBJECT_NAME,
2010 X_OLD_DURATION,
2011 X_NEW_DURATION,
2012 X_OLD_DURATION_UOM,
2013 X_NEW_DURATION_UOM,
2014 X_OLD_PLANNED_EFFORT,
2015 X_NEW_PLANNED_EFFORT,
2016 X_OLD_PLANNED_EFFORT_UOM,
2017 X_NEW_PLANNED_EFFORT_UOM,
2018 X_OLD_ACTUAL_EFFORT,
2019 X_NEW_ACTUAL_EFFORT,
2020 X_OLD_ACTUAL_EFFORT_UOM,
2021 X_NEW_ACTUAL_EFFORT_UOM,
2022 X_OLD_PERCENTAGE_COMPLETE,
2023 X_NEW_PERCENTAGE_COMPLETE,
2024 X_OLD_REASON_CODE,
2025 X_NEW_REASON_CODE,
2026 X_PRIVATE_CHANGED_FLAG,
2027 X_PUBLISH_CHANGED_FLAG,
2028 X_RESTRICT_CLOSURE_CHANGE_FLAG,
2029 X_MULTI_BOOKED_CHANGED_FLAG,
2030 X_MILESTONE_CHANGED_FLAG,
2031 X_HOLIDAY_CHANGED_FLAG,
2032 X_BILLABLE_CHANGED_FLAG,
2033 X_OLD_BOUND_MODE_CODE,
2034 X_NEW_BOUND_MODE_CODE,
2035 X_SOFT_BOUND_CHANGED_FLAG,
2036 X_OLD_WORKFLOW_PROCESS_ID,
2037 X_NEW_WORKFLOW_PROCESS_ID,
2038 X_NOTIFICATION_CHANGED_FLAG,
2039 X_OLD_NOTIFICATION_PERIOD,
2040 X_CREATION_DATE,
2041 X_CREATED_BY,
2042 X_LAST_UPDATE_DATE,
2043 X_LAST_UPDATED_BY,
2044 X_LAST_UPDATE_LOGIN,
2045 X_OBJECT_VERSION_NUMBER,
2046 X_OLD_OWNER_TERRITORY_ID,
2047 X_NEW_OWNER_TERRITORY_ID,
2048 X_NEW_ESCALATION_LEVEL,
2049 X_OLD_ESCALATION_LEVEL,
2050 X_NEW_DATE_SELECTED,
2051 X_OLD_DATE_SELECTED
2052 );
2053
2054 insert into JTF_TASK_AUDITS_TL (
2055 TASK_AUDIT_ID,
2056 OLD_TASK_NAME,
2057 NEW_TASK_NAME,
2058 OLD_DESCRIPTION,
2059 NEW_DESCRIPTION,
2060 CREATED_BY,
2061 CREATION_DATE,
2062 LAST_UPDATED_BY,
2063 LAST_UPDATE_DATE,
2064 LAST_UPDATE_LOGIN,
2065 LANGUAGE,
2066 SOURCE_LANG
2067 ) select
2068 X_TASK_AUDIT_ID,
2069 X_OLD_TASK_NAME,
2070 X_NEW_TASK_NAME,
2071 X_OLD_DESCRIPTION,
2072 X_NEW_DESCRIPTION,
2073 X_CREATED_BY,
2074 X_CREATION_DATE,
2075 X_LAST_UPDATED_BY,
2076 X_LAST_UPDATE_DATE,
2077 X_LAST_UPDATE_LOGIN,
2078 L.LANGUAGE_CODE,
2079 userenv('LANG')
2080 from FND_LANGUAGES L
2081 where L.INSTALLED_FLAG in ('I', 'B')
2082 and not exists
2083 (select NULL
2084 from JTF_TASK_AUDITS_TL T
2085 where T.TASK_AUDIT_ID = X_TASK_AUDIT_ID
2086 and T.LANGUAGE = L.LANGUAGE_CODE);
2087
2088 open c;
2089 fetch c into X_ROWID;
2090 if (c%notfound) then
2091 close c;
2092 raise no_data_found;
2093 end if;
2094 close c;
2095
2096 end INSERT_ROW;
2097
2098 procedure ADD_LANGUAGE
2099 is
2100 begin
2101
2102 /* Solving Perf. Bug 3723927 */
2103 /* The following delete and update statements are commented out */
2104 /* as a quick workaround to fix the time-consuming table handler issue */
2105 /*
2106
2107 delete from JTF_TASK_AUDITS_TL T
2108 where not exists
2109 (select NULL
2110 from JTF_TASK_AUDITS_B B
2111 where B.TASK_AUDIT_ID = T.TASK_AUDIT_ID
2112 );
2113
2114 update JTF_TASK_AUDITS_TL T set (
2115 OLD_TASK_NAME,
2116 NEW_TASK_NAME,
2117 OLD_DESCRIPTION,
2118 NEW_DESCRIPTION
2119 ) = (select
2120 B.OLD_TASK_NAME,
2121 B.NEW_TASK_NAME,
2122 B.OLD_DESCRIPTION,
2123 B.NEW_DESCRIPTION
2124 from JTF_TASK_AUDITS_TL B
2125 where B.TASK_AUDIT_ID = T.TASK_AUDIT_ID
2126 and B.LANGUAGE = T.SOURCE_LANG)
2127 where (
2128 T.TASK_AUDIT_ID,
2129 T.LANGUAGE
2130 ) in (select
2131 SUBT.TASK_AUDIT_ID,
2132 SUBT.LANGUAGE
2133 from JTF_TASK_AUDITS_TL SUBB, JTF_TASK_AUDITS_TL SUBT
2134 where SUBB.TASK_AUDIT_ID = SUBT.TASK_AUDIT_ID
2135 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2136 and (SUBB.OLD_TASK_NAME <> SUBT.OLD_TASK_NAME
2137 or (SUBB.OLD_TASK_NAME is null and SUBT.OLD_TASK_NAME is not null)
2138 or (SUBB.OLD_TASK_NAME is not null and SUBT.OLD_TASK_NAME is null)
2139 or SUBB.NEW_TASK_NAME <> SUBT.NEW_TASK_NAME
2140 or (SUBB.NEW_TASK_NAME is null and SUBT.NEW_TASK_NAME is not null)
2141 or (SUBB.NEW_TASK_NAME is not null and SUBT.NEW_TASK_NAME is null)
2142 or SUBB.OLD_DESCRIPTION <> SUBT.OLD_DESCRIPTION
2143 or (SUBB.OLD_DESCRIPTION is null and SUBT.OLD_DESCRIPTION is not null)
2144 or (SUBB.OLD_DESCRIPTION is not null and SUBT.OLD_DESCRIPTION is null)
2145 or SUBB.NEW_DESCRIPTION <> SUBT.NEW_DESCRIPTION
2146 or (SUBB.NEW_DESCRIPTION is null and SUBT.NEW_DESCRIPTION is not null)
2147 or (SUBB.NEW_DESCRIPTION is not null and SUBT.NEW_DESCRIPTION is null)
2148 )); */
2149
2150 -- Added hint 'parallel' by SBARAT on 19/01/2006 for perf bug# 4888496
2151
2152 insert into JTF_TASK_AUDITS_TL (
2153 SECURITY_GROUP_ID,
2154 TASK_AUDIT_ID,
2155 OLD_TASK_NAME,
2156 NEW_TASK_NAME,
2157 OLD_DESCRIPTION,
2158 NEW_DESCRIPTION,
2159 CREATED_BY,
2160 CREATION_DATE,
2161 LAST_UPDATED_BY,
2162 LAST_UPDATE_DATE,
2163 LAST_UPDATE_LOGIN,
2164 LANGUAGE,
2165 SOURCE_LANG
2166 ) select /*+ parallel(B) parallel(L) */
2167 B.SECURITY_GROUP_ID,
2168 B.TASK_AUDIT_ID,
2169 B.OLD_TASK_NAME,
2170 B.NEW_TASK_NAME,
2171 B.OLD_DESCRIPTION,
2172 B.NEW_DESCRIPTION,
2173 B.CREATED_BY,
2174 B.CREATION_DATE,
2175 B.LAST_UPDATED_BY,
2176 B.LAST_UPDATE_DATE,
2177 B.LAST_UPDATE_LOGIN,
2178 L.LANGUAGE_CODE,
2179 B.SOURCE_LANG
2180 from JTF_TASK_AUDITS_TL B, FND_LANGUAGES L
2181 where L.INSTALLED_FLAG in ('I', 'B')
2182 and B.LANGUAGE = userenv('LANG')
2183 and not exists
2184 (select /*+ parallel(T) */ NULL
2185 from JTF_TASK_AUDITS_TL T
2186 where T.TASK_AUDIT_ID = B.TASK_AUDIT_ID
2187 and T.LANGUAGE = L.LANGUAGE_CODE);
2188 end ADD_LANGUAGE;
2189
2190 procedure translate_row(
2191 x_task_audit_id in number,
2192 x_old_task_name in varchar2,
2193 x_new_task_name in varchar2,
2194 x_old_description in varchar2,
2195 x_new_description in varchar2,
2196 x_owner in varchar2
2197 )
2198 as
2199 begin
2200 update jtf_task_audits_tl set
2201 old_task_name = nvl(x_old_task_name,old_task_name),
2202 new_task_name = nvl(x_new_task_name,new_task_name),
2203 old_description = nvl(x_old_description,old_description),
2204 new_description = nvl(x_new_description,new_description),
2205 LAST_UPDATE_DATE = sysdate,
2206 LAST_UPDATEd_by = decode(x_owner,'SEED',1,0),
2207 LAST_UPDATE_LOGIN = 0,
2208 SOURCE_LANG = userenv('LANG')
2209 where task_audit_id = X_task_audit_id
2210 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2211
2212 if (sql%notfound) then
2213 raise no_data_found;
2214 end if;
2215
2216 end ;
2217
2218 END jtf_task_audits_pvt;