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