[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_WF_SUBSCRIBE_PVT
Source
1 PACKAGE BODY jtf_task_wf_subscribe_pvt AS
2 /* $Header: jtftkwkb.pls 120.1.12000000.2 2007/10/04 14:16:11 venjayar ship $ */
3 FUNCTION create_task_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
4 RETURN VARCHAR2 IS
5 l_task_id jtf_tasks_b.task_id%TYPE;
6 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
7 l_enable_workflow VARCHAR2(1);
8 l_abort_workflow VARCHAR2(1);
9 x_return_status VARCHAR2(200);
10 x_msg_count NUMBER;
11 x_msg_data VARCHAR2(1000);
12 BEGIN
13 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
14 l_enable_workflow := wf_event.getvalueforparameter('ENABLE_WORKFLOW', p_event.parameter_list);
15 l_abort_workflow := wf_event.getvalueforparameter('ABORT_WORKFLOW', p_event.parameter_list);
16 l_source_object_type_code := wf_event.getvalueforparameter('SOURCE_OBJECT_TYPE_CODE', p_event.parameter_list);
17
18 IF (l_source_object_type_code <> 'APPOINTMENT') THEN
19 IF (l_enable_workflow = 'Y') THEN
20 IF (jtf_task_wf_util.do_notification(l_task_id)) THEN
21 jtf_task_wf_util.create_notification(
22 p_event => 'CREATE_TASK'
23 , p_task_id => l_task_id
24 , p_abort_workflow => l_abort_workflow
25 , x_return_status => x_return_status
26 , x_msg_count => x_msg_count
27 , x_msg_data => x_msg_data
28 );
29 END IF;
30 END IF;
31 END IF;
32
33 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
34 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'create_task_notif_subs', p_event.event_name, p_subscription_guid);
35 wf_event.seterrorinfo(p_event, 'WARNING');
36 RETURN 'WARNING';
37 END IF;
38
39 RETURN 'SUCCESS';
40 EXCEPTION
41 WHEN OTHERS THEN
42 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'create_task_notif_subs', p_event.event_name, p_subscription_guid);
43 wf_event.seterrorinfo(p_event, 'WARNING');
44 RETURN 'WARNING';
45 END create_task_notif_subs;
46
47 FUNCTION update_task_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
48 RETURN VARCHAR2 IS
49 l_task_id jtf_tasks_b.task_id%TYPE;
50 l_task_audit_id jtf_task_audits_b.task_audit_id%TYPE;
51 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
52 l_enable_workflow VARCHAR2(1);
53 l_abort_workflow VARCHAR2(1);
54 x_return_status VARCHAR2(200);
55 x_msg_count NUMBER;
56 x_msg_data VARCHAR2(1000);
57
58 CURSOR c_task_detail(b_task_audit_id IN NUMBER) IS
59 SELECT new_task_type_id
60 , old_task_type_id
61 , new_task_status_id
62 , old_task_status_id
63 , new_description
64 , old_description
65 , new_task_priority_id
66 , old_task_priority_id
67 , new_planned_start_date
68 , old_planned_start_date
69 , new_planned_end_date
70 , old_planned_end_date
71 , new_scheduled_start_date
72 , old_scheduled_start_date
73 , new_scheduled_end_date
74 , old_scheduled_end_date
75 , new_actual_start_date
76 , old_actual_start_date
77 , new_actual_end_date
78 , old_actual_end_date
79 , new_owner_id
80 , old_owner_id
81 , new_owner_type_code
82 , old_owner_type_code
83 FROM jtf_task_audits_vl
84 WHERE task_audit_id = b_task_audit_id;
85
86 rec_task c_task_detail%ROWTYPE;
87 BEGIN
88 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
89 l_task_audit_id := wf_event.getvalueforparameter('TASK_AUDIT_ID', p_event.parameter_list);
90 l_enable_workflow := wf_event.getvalueforparameter('ENABLE_WORKFLOW', p_event.parameter_list);
91 l_abort_workflow := wf_event.getvalueforparameter('ABORT_WORKFLOW', p_event.parameter_list);
92 l_source_object_type_code := wf_event.getvalueforparameter('SOURCE_OBJECT_TYPE_CODE', p_event.parameter_list);
93
94 OPEN c_task_detail(l_task_audit_id);
95
96 FETCH c_task_detail
97 INTO rec_task;
98
99 CLOSE c_task_detail;
100
101 IF (l_source_object_type_code <> 'APPOINTMENT') THEN
102 IF (l_enable_workflow = 'Y') THEN
103 IF compare_old_new_param(rec_task.new_task_type_id, rec_task.old_task_type_id)
104 OR compare_old_new_param(rec_task.new_task_status_id, rec_task.old_task_status_id)
105 OR compare_old_new_param(rec_task.new_description, rec_task.old_description)
106 OR compare_old_new_param(rec_task.new_task_priority_id, rec_task.old_task_priority_id)
107 OR compare_old_new_param(rec_task.new_planned_start_date, rec_task.old_planned_start_date)
108 OR compare_old_new_param(rec_task.new_planned_end_date, rec_task.old_planned_end_date)
109 OR compare_old_new_param(rec_task.new_scheduled_start_date, rec_task.old_scheduled_start_date)
110 OR compare_old_new_param(rec_task.new_scheduled_end_date, rec_task.old_scheduled_end_date)
111 OR compare_old_new_param(rec_task.new_actual_start_date, rec_task.old_actual_start_date)
112 OR compare_old_new_param(rec_task.new_actual_end_date, rec_task.old_actual_end_date)
113 THEN
114 IF (jtf_task_wf_util.do_notification(l_task_id)) THEN
115 jtf_task_wf_util.create_notification(
116 p_event => 'CHANGE_TASK_DETAILS'
117 , p_task_id => l_task_id
118 , p_old_type => rec_task.old_task_type_id
119 , p_old_priority => rec_task.old_task_priority_id
120 , p_old_status => rec_task.old_task_status_id
121 , p_old_planned_start_date => rec_task.old_planned_start_date
122 , p_old_planned_end_date => rec_task.old_planned_end_date
123 , p_old_scheduled_start_date => rec_task.old_scheduled_start_date
124 , p_old_scheduled_end_date => rec_task.old_scheduled_end_date
125 , p_old_actual_start_date => rec_task.old_actual_start_date
126 , p_old_actual_end_date => rec_task.old_actual_end_date
127 , p_old_description => rec_task.old_description
128 , p_old_owner_id => rec_task.old_owner_id
129 , p_old_owner_code => rec_task.old_owner_type_code
130 , p_abort_workflow => l_abort_workflow
131 , x_return_status => x_return_status
132 , x_msg_count => x_msg_count
133 , x_msg_data => x_msg_data
134 );
135 END IF;
136 END IF;
137 END IF;
138 END IF;
139
140 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
141 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'update_task_notif_subs', p_event.event_name, p_subscription_guid);
142 wf_event.seterrorinfo(p_event, 'WARNING');
143 RETURN 'WARNING';
144 END IF;
145
146 RETURN 'SUCCESS';
147 EXCEPTION
148 WHEN OTHERS THEN
149 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'update_task_notif_subs', p_event.event_name, p_subscription_guid);
150 wf_event.seterrorinfo(p_event, 'WARNING');
151 RETURN 'WARNING';
152 END update_task_notif_subs;
153
154 FUNCTION delete_task_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
155 RETURN VARCHAR2 IS
156 l_task_id jtf_tasks_b.task_id%TYPE;
157 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
158 l_enable_workflow VARCHAR2(1);
159 l_abort_workflow VARCHAR2(1);
160 x_return_status VARCHAR2(200);
161 x_msg_count NUMBER;
162 x_msg_data VARCHAR2(1000);
163 BEGIN
164 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
165 l_enable_workflow := wf_event.getvalueforparameter('ENABLE_WORKFLOW', p_event.parameter_list);
166 l_abort_workflow := wf_event.getvalueforparameter('ABORT_WORKFLOW', p_event.parameter_list);
167 l_source_object_type_code := wf_event.getvalueforparameter('SOURCE_OBJECT_TYPE_CODE', p_event.parameter_list);
168
169 IF (l_source_object_type_code <> 'APPOINTMENT') THEN
170 IF (l_enable_workflow = 'Y') THEN
171 IF (jtf_task_wf_util.do_notification(l_task_id)) THEN
172 jtf_task_wf_util.create_notification(
173 p_event => 'DELETE_TASK'
174 , p_task_id => l_task_id
175 , p_abort_workflow => l_abort_workflow
176 , x_return_status => x_return_status
177 , x_msg_count => x_msg_count
178 , x_msg_data => x_msg_data
179 );
180 END IF;
181 END IF;
182 END IF;
183
184 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
185 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'delete_task_notif_subs', p_event.event_name, p_subscription_guid);
186 wf_event.seterrorinfo(p_event, 'WARNING');
187 RETURN 'WARNING';
188 END IF;
189
190 RETURN 'SUCCESS';
191 EXCEPTION
192 WHEN OTHERS THEN
193 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'delete_task_notif_subs', p_event.event_name, p_subscription_guid);
194 wf_event.seterrorinfo(p_event, 'WARNING');
195 RETURN 'WARNING';
196 END delete_task_notif_subs;
197
198 FUNCTION create_assg_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
199 RETURN VARCHAR2 IS
200 l_enable_workflow VARCHAR2(1);
201 l_abort_workflow VARCHAR2(1);
202 x_return_status VARCHAR2(200);
203 x_msg_count NUMBER;
204 x_msg_data VARCHAR2(1000);
205 l_task_id jtf_tasks_b.task_id%TYPE;
206 l_resource_type_code jtf_task_all_assignments.resource_type_code%TYPE;
207 l_resource_id jtf_task_all_assignments.resource_id%TYPE;
208 l_assignee_role jtf_task_all_assignments.assignee_role%TYPE;
209 BEGIN
210 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
211 l_resource_type_code := wf_event.getvalueforparameter('RESOURCE_TYPE_CODE', p_event.parameter_list);
212 l_resource_id := wf_event.getvalueforparameter('RESOURCE_ID', p_event.parameter_list);
213 l_enable_workflow := wf_event.getvalueforparameter('ENABLE_WORKFLOW', p_event.parameter_list);
214 l_abort_workflow := wf_event.getvalueforparameter('ABORT_WORKFLOW', p_event.parameter_list);
215 l_assignee_role := wf_event.getvalueforparameter('ASSIGNEE_ROLE', p_event.parameter_list);
216
217 IF (l_assignee_role <> 'OWNER') AND(l_enable_workflow = 'Y') THEN
218 IF (jtf_task_wf_util.do_notification(l_task_id)) THEN
219 jtf_task_wf_util.create_notification(
220 p_event => 'ADD_ASSIGNEE'
221 , p_task_id => l_task_id
222 , p_new_assignee_id => l_resource_id
223 , p_new_assignee_code => l_resource_type_code
224 , p_abort_workflow => l_abort_workflow
225 , x_return_status => x_return_status
226 , x_msg_count => x_msg_count
227 , x_msg_data => x_msg_data
228 );
229 END IF;
230 END IF;
231
232 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
233 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'create_assg_notif_subs', p_event.event_name, p_subscription_guid);
234 wf_event.seterrorinfo(p_event, 'WARNING');
235 RETURN 'WARNING';
236 END IF;
237
238 RETURN 'SUCCESS';
239 EXCEPTION
240 WHEN OTHERS THEN
241 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'create_assg_notif_subs', p_event.event_name, p_subscription_guid);
242 wf_event.seterrorinfo(p_event, 'WARNING');
243 RETURN 'WARNING';
244 END create_assg_notif_subs;
245
246 FUNCTION update_assg_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
247 RETURN VARCHAR2 IS
248 l_enable_workflow VARCHAR2(1);
249 l_abort_workflow VARCHAR2(1);
250 l_assignee_role_db jtf_task_all_assignments.assignee_role%TYPE;
251 x_return_status VARCHAR2(200);
252 x_msg_count NUMBER;
253 x_msg_data VARCHAR2(1000);
254 l_task_assignment_id jtf_task_all_assignments.task_assignment_id%TYPE;
255 l_task_id jtf_tasks_b.task_id%TYPE;
256 l_resource_type_code jtf_task_all_assignments.resource_type_code%TYPE;
257 l_resource_id jtf_task_all_assignments.resource_id%TYPE;
258 l_orig_resource_type_code jtf_task_all_assignments.resource_type_code%TYPE;
259 l_orig_resource_id jtf_task_all_assignments.resource_id%TYPE;
260 BEGIN
261 l_task_assignment_id := wf_event.getvalueforparameter('TASK_ASSIGNMENT_ID', p_event.parameter_list);
262 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
263 l_resource_type_code := wf_event.getvalueforparameter('NEW_RESOURCE_TYPE_CODE', p_event.parameter_list);
264 l_resource_id := wf_event.getvalueforparameter('NEW_RESOURCE_ID', p_event.parameter_list);
265 l_orig_resource_type_code := wf_event.getvalueforparameter('OLD_RESOURCE_TYPE_CODE', p_event.parameter_list);
266 l_orig_resource_id := wf_event.getvalueforparameter('OLD_RESOURCE_ID', p_event.parameter_list);
267 l_enable_workflow := wf_event.getvalueforparameter('ENABLE_WORKFLOW', p_event.parameter_list);
268 l_abort_workflow := wf_event.getvalueforparameter('ABORT_WORKFLOW', p_event.parameter_list);
269 l_assignee_role_db := wf_event.getvalueforparameter('ASSIGNEE_ROLE', p_event.parameter_list);
270
271 IF (l_assignee_role_db IS NULL) THEN
272 l_assignee_role_db := wf_event.getvalueforparameter('NEW_ASSIGNEE_ROLE', p_event.parameter_list);
273 END IF;
274
275 IF (l_assignee_role_db IS NOT NULL) THEN
276 IF (l_enable_workflow = 'Y') THEN
277 IF (NVL(l_resource_id, 0) <> fnd_api.g_miss_num AND NVL(l_resource_id, 0) <> NVL(l_orig_resource_id, 0))
278 OR (NVL(l_resource_type_code, fnd_api.g_miss_char) <> NVL(l_orig_resource_type_code, fnd_api.g_miss_char)) THEN
279 IF (jtf_task_wf_util.do_notification(l_task_id)) THEN
280 IF (l_assignee_role_db = 'OWNER') THEN
281 jtf_task_wf_util.create_notification(
282 p_event => 'CHANGE_OWNER'
283 , p_task_id => l_task_id
284 , p_old_owner_id => l_orig_resource_id
285 , p_old_owner_code => l_orig_resource_type_code
286 , p_abort_workflow => l_abort_workflow
287 , x_return_status => x_return_status
288 , x_msg_count => x_msg_count
289 , x_msg_data => x_msg_data
290 );
291 ELSE
292 jtf_task_wf_util.create_notification(
293 p_event => 'CHANGE_ASSIGNEE'
294 , p_task_id => l_task_id
295 , p_old_assignee_id => l_orig_resource_id
296 , p_old_assignee_code => l_orig_resource_type_code
297 , p_new_assignee_id => l_resource_id
298 , p_new_assignee_code => l_resource_type_code
299 , p_abort_workflow => l_abort_workflow
300 , x_return_status => x_return_status
301 , x_msg_count => x_msg_count
302 , x_msg_data => x_msg_data
306 END IF;
303 );
304 END IF;
305 END IF;
307 END IF;
308 END IF;
309
310 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
311 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'update_assg_notif_subs', p_event.event_name, p_subscription_guid);
312 wf_event.seterrorinfo(p_event, 'WARNING');
313 RETURN 'WARNING';
314 END IF;
315
316 RETURN 'SUCCESS';
317 EXCEPTION
318 WHEN OTHERS THEN
319 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'update_assg_notif_subs', p_event.event_name, p_subscription_guid);
320 wf_event.seterrorinfo(p_event, 'WARNING');
321 RETURN 'WARNING';
322 END update_assg_notif_subs;
323
324 FUNCTION delete_assg_notif_subs(p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
325 RETURN VARCHAR2 IS
326 l_enable_workflow VARCHAR2(1);
327 l_abort_workflow VARCHAR2(1);
328 x_return_status VARCHAR2(200);
329 x_msg_count NUMBER;
330 x_msg_data VARCHAR2(1000);
331 l_task_assignment_id jtf_task_all_assignments.task_assignment_id%TYPE;
332 l_task_id jtf_tasks_b.task_id%TYPE;
333 l_resource_type_code jtf_task_all_assignments.resource_type_code%TYPE;
334 l_resource_id jtf_task_all_assignments.resource_id%TYPE;
335 l_assignee_role jtf_task_all_assignments.assignee_role%TYPE;
336 BEGIN
337 l_task_assignment_id := wf_event.getvalueforparameter('TASK_ASSIGNMENT_ID', p_event.parameter_list);
338 l_task_id := wf_event.getvalueforparameter('TASK_ID', p_event.parameter_list);
339 l_resource_type_code := wf_event.getvalueforparameter('RESOURCE_TYPE_CODE', p_event.parameter_list);
340 l_resource_id := wf_event.getvalueforparameter('RESOURCE_ID', p_event.parameter_list);
341 l_enable_workflow := wf_event.getvalueforparameter('ENABLE_WORKFLOW', p_event.parameter_list);
342 l_abort_workflow := wf_event.getvalueforparameter('ABORT_WORKFLOW', p_event.parameter_list);
343 l_assignee_role := wf_event.getvalueforparameter('ASSIGNEE_ROLE', p_event.parameter_list);
344
345 IF (l_assignee_role <> 'OWNER') AND(l_enable_workflow = 'Y') THEN
346 IF (jtf_task_wf_util.do_notification(l_task_id)) THEN
347 jtf_task_wf_util.create_notification(
348 p_event => 'DELETE_ASSIGNEE'
349 , p_task_id => l_task_id
350 , p_old_assignee_id => l_resource_id
351 , p_old_assignee_code => l_resource_type_code
352 , p_abort_workflow => l_abort_workflow
353 , x_return_status => x_return_status
354 , x_msg_count => x_msg_count
355 , x_msg_data => x_msg_data
356 );
357 END IF;
358 END IF;
359
360 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
361 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'delete_assg_notif_subs', p_event.event_name, p_subscription_guid);
362 wf_event.seterrorinfo(p_event, 'WARNING');
363 RETURN 'WARNING';
364 END IF;
365
366 RETURN 'SUCCESS';
367 EXCEPTION
368 WHEN OTHERS THEN
369 wf_core.CONTEXT('jtf_task_wf_subscribe_pvt', 'delete_assg_notif_subs', p_event.event_name, p_subscription_guid);
370 wf_event.seterrorinfo(p_event, 'WARNING');
371 RETURN 'WARNING';
372 END delete_assg_notif_subs;
373
374 FUNCTION compare_old_new_param(p_new_param IN VARCHAR2, p_old_param IN VARCHAR2)
375 RETURN BOOLEAN IS
376 l_old_param VARCHAR2(4000) := p_old_param;
377 l_new_param VARCHAR2(4000) := p_new_param;
378 BEGIN
379 IF (l_old_param IS NULL) THEN
380 l_old_param := fnd_api.g_miss_char;
381 END IF;
382
383 IF (l_new_param IS NULL) THEN
384 l_new_param := fnd_api.g_miss_char;
385 END IF;
386
387 RETURN(l_old_param <> l_new_param);
388 END compare_old_new_param;
389
390 FUNCTION compare_old_new_param(p_new_param IN NUMBER, p_old_param IN NUMBER)
391 RETURN BOOLEAN IS
392 BEGIN
393 RETURN compare_old_new_param(TO_CHAR(p_new_param), TO_CHAR(p_old_param));
394 END compare_old_new_param;
395
396 FUNCTION compare_old_new_param(p_new_param IN DATE, p_old_param IN DATE)
397 RETURN BOOLEAN IS
398 BEGIN
399 RETURN compare_old_new_param(
400 TO_CHAR(p_new_param, 'YYYY-MM-DD HH24:MI:SS')
401 , TO_CHAR(p_old_param, 'YYYY-MM-DD HH24:MI:SS')
402 );
403 END compare_old_new_param;
404 END;