[Home] [Help]
PACKAGE BODY: APPS.CSM_WF_PKG
Source
1 PACKAGE BODY CSM_WF_PKG AS
2 /* $Header: csmewfb.pls 120.19.12020000.5 2013/04/09 11:01:44 saradhak ship $ */
3 /*--------------------------------------------------
4 Description:
5 Acts as the entry point to CSM logic from user and
6 vertical hooks as well as from concurrent programs.
7
8 12/16/02 ANURAG Workflow threshold value is now restored
9 ----------------------------------------------------*/
10
11 -- stores the old organization_id and old_subinventory_code for
12 -- the upd on csp_inv_loc_assignments
13 g_old_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
14 g_old_organization_id csp_inv_loc_assignments.organization_id%TYPE;
15 g_old_eff_date_start date;
16 g_old_eff_date_end date;
17 g_old_default_code csp_inv_loc_assignments.default_code%TYPE;
18 g_task_downloaded_to_owner BOOLEAN;
19
20 TYPE g_task_ass_pre_upd_typ IS RECORD(
21 resource_id NUMBER,
22 assignment_status_id NUMBER,
23 task_id NUMBER
24 );
25
26 g_task_ass_pre_upd_rec g_task_ass_pre_upd_typ;
27 g_null_task_ass_pre_upd_typ g_task_ass_pre_upd_typ;
28
29 TYPE g_task_pre_upd_typ IS RECORD(
30 task_status_id NUMBER,
31 task_type_id NUMBER,
32 scheduled_start_date DATE,
33 scheduled_end_date DATE,
34 task_id NUMBER
35 );
36
37 g_task_pre_upd_rec g_task_pre_upd_typ;
38 g_null_task_pre_upd_typ g_task_pre_upd_typ;
39
40 TYPE g_sr_pre_upd_typ IS RECORD(
41 INCIDENT_ID NUMBER,
42 CUSTOMER_ID NUMBER,
43 INSTALL_SITE_ID NUMBER,
44 CUSTOMER_PRODUCT_ID NUMBER,
45 INVENTORY_ITEM_ID NUMBER,
46 INV_ORGANIZATION_ID NUMBER,
47 CONTRACT_SERVICE_ID NUMBER,
48 PARTY_ID NUMBER,
49 LOCATION_ID NUMBER,
50 INCIDENT_LOCATION_ID NUMBER,
51 OWNER_GROUP_ID NUMBER
52 );
53
54 g_sr_pre_upd_rec g_sr_pre_upd_typ;
55 g_null_sr_pre_upd_rec g_sr_pre_upd_typ;
56
57 TYPE g_debrief_line_pre_upd_typ IS RECORD(
58 DEBRIEF_LINE_ID NUMBER,
59 INVENTORY_ITEM_ID NUMBER,
60 INSTANCE_ID NUMBER
61 );
62
63 g_debrief_line_pre_upd_rec g_debrief_line_pre_upd_typ;
64 g_null_debrief_ln_pre_upd_rec g_debrief_line_pre_upd_typ;
65
66 --
67 --This table will hold the contacts and contact information
68 --for a Service Request customer
69 --
70 TYPE contacts_rec IS RECORD (
71 SR_CONTACT_POINT_ID NUMBER ,
72 PARTY_ID NUMBER ,
73 CONTACT_POINT_ID NUMBER ,
74 PRIMARY_FLAG VARCHAR2(1) ,
75 CONTACT_POINT_TYPE VARCHAR2(30) ,
76 CONTACT_TYPE VARCHAR2(30) ,
77 LAST_UPDATE_DATE DATE ,
78 CREATION_DATE DATE
79 );
80
81 TYPE contacts_table_typ IS TABLE OF contacts_rec INDEX BY BINARY_INTEGER;
82
83 g_sr_cont_points_pre_upd_tbl contacts_table_typ;
84 g_sr_cont_points_post_upd_tbl contacts_table_typ;
85 g_null_sr_cont_points_tbl contacts_table_typ;
86
87 /*
88 * Private function
89 */
90 /*
91 * IS_TASK_STATUS_DOWNLOADABLE
92 * --------------------------
93 * Function to test whether the task status makes the task/task assignment to be downloaded
94 * according to the criteria for Field Service / Palm.
95 */
96
97 FUNCTION IS_TASK_STATUS_DOWNLOADABLE(
98 p_task_id IN NUMBER,
99 p_status_id IN NUMBER
100 ) RETURN BOOLEAN
101 IS
102 CURSOR c_task_status (p_task_id in NUMBER, b_status_id NUMBER)
103 IS
104 SELECT 1
105 FROM jtf_tasks_b jt
106 WHERE jt.task_id = p_task_id
107 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL)
108 UNION
109 SELECT 1
110 FROM jtf_task_statuses_b jts
111 WHERE jts.task_status_id = b_status_id
112 AND ( jts.ASSIGNED_FLAG = 'Y'
113 OR jts.COMPLETED_FLAG = 'Y'
114 OR jts.CLOSED_FLAG = 'Y');
115
116 l_status_id NUMBER;
117 BEGIN
118 -- CSM_UTIL_PKG.pvt_log('Enter IS_TASK_STATUS_DOWNLOADABLE ' || p_status_id);
119 OPEN c_task_status(p_task_id, p_status_id);
120 FETCH c_task_status INTO l_status_id;
121 IF c_task_status%FOUND THEN
122 CLOSE c_task_status;
123 RETURN TRUE;
124 END IF;
125 CLOSE c_task_status;
126 RETURN FALSE;
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF c_task_status%ISOPEN THEN
130 CLOSE c_task_status;
131 END IF;
132 RETURN FALSE;
133 END IS_TASK_STATUS_DOWNLOADABLE;
134
135 /*
136 * IS_TASK_DOWNLOADABLE
137 * --------------------------
138 * Function to test whether the task status, type,
139 * schedule_start_date, scheduled_end_date make the task/task assignment to be downloaded
140 * according to the criteria for Field Service / Palm.
141 */
142
143 FUNCTION IS_TASK_DOWNLOADABLE(
144 p_task_id IN NUMBER,
145 p_status_id IN NUMBER,
146 p_type_id IN NUMBER,
147 p_schedule_start_date IN DATE,
148 p_schedule_end_date IN DATE
149 ) RETURN BOOLEAN
150 IS
151 CURSOR c_task_status (p_task_id IN NUMBER, b_status_id NUMBER) IS
152 SELECT 1
153 FROM jtf_tasks_b jt
154 WHERE jt.task_id = p_task_id
155 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL)
156 UNION
157 SELECT 1
158 FROM jtf_task_statuses_b jts
159 WHERE jts.task_status_id = b_status_id
160 AND ( jts.ASSIGNED_FLAG = 'Y'
161 OR jts.COMPLETED_FLAG = 'Y'
162 OR jts.CLOSED_FLAG = 'Y');
163
164 CURSOR c_task_type (b_type_id NUMBER) IS
165 SELECT 1
166 FROM JTF_TASK_TYPES_B
167 WHERE TASK_TYPE_ID = b_type_id
168 AND (RULE = 'DISPATCH' OR private_flag = 'Y');
169
170 l_temp NUMBER := NULL;
171
172 BEGIN
173 OPEN c_task_status(p_task_id, p_status_id);
174 FETCH c_task_status INTO l_temp;
175 CLOSE c_task_status;
176
177 IF l_temp IS NULL THEN
178 -- CSM_UTIL_PKG.pvt_log('IS_TASK_STATUS_DOWNLOADABLE(' || p_status_id || ') = FALSE' );
179 RETURN FALSE;
180 END IF;
181
182 l_temp := NULL;
183 OPEN c_task_type(p_type_id);
184 FETCH c_task_type INTO l_temp;
185 CLOSE c_task_type;
186
187 -- IF c_task_type%NOTFOUND THEN
188 IF l_temp IS NULL THEN
189 -- CSM_UTIL_PKG.pvt_log('IS_TASK_TYPE_DOWNLOADABLE(' || p_type_id || ') = FALSE' );
190 RETURN FALSE;
191 END IF;
192 IF ( p_schedule_start_date IS NULL OR p_schedule_end_date IS NULL ) THEN
193 -- CSM_UTIL_PKG.pvt_log('IS_TASK_SCHEDULED_DATE_DOWNLOADABLE(' || p_schedule_start_date || ', ' || p_schedule_end_date || ') = FALSE' );
194 RETURN FALSE;
195 END IF;
196
197 -- CSM_UTIL_PKG.pvt_log('IS_TASK_DOWNLOADABLE = TRUE' );
198 RETURN TRUE;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 -- CSM_UTIL_PKG.pvt_log('EXCEPTION IN IS_TASK_DOWNLOADABLE. Return FALSE' );
203 RETURN FALSE;
204 END IS_TASK_DOWNLOADABLE;
205
206 /*
207 * public functions
208 */
209 /* Starts TASK_INS workflow, Should be called when new TASK is created */
210 /* If the task is an escalated task, check if it is an escalated task for existing mobile task.
211 If yes, we call task upd
212 */
213 --12.1
214 Procedure TASK_Post_Ins(
215 x_return_status OUT NOCOPY VARCHAR2
216 )
217 IS
218 l_sqlerrno VARCHAR2(20);
219 l_sqlerrmsg VARCHAR2(4000);
220 l_error_msg VARCHAR2(4000);
221 l_return_status VARCHAR2(2000);
222 l_user_id number;
223 l_task_id JTF_TASKS_B.TASK_ID%TYPE;
224
225 CURSOR l_task_csr (b_task_id NUMBER) IS
226 SELECT jt.CREATED_BY, jtt.private_flag, jt.source_object_type_code -- 22 means Escalation task
227 FROM JTF_TASKS_B jt,
228 jtf_task_types_b jtt
229 WHERE jt.TASK_ID = b_task_id
230 AND jtt.task_type_id = jt.task_type_id;
231
232 l_task_rec l_task_csr%ROWTYPE;
233
234 --12.1XB7
235 CURSOR c_sr_grp_owner(b_task_id number) IS
236 SELECT USER_ID
237 FROM ASG_USER usr,
238 CS_INCIDENTS_ALL_B inc,
239 JTF_TASKS_B tsk
240 WHERE tsk.TASK_ID=b_task_id
241 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
242 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
243 AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
244 AND usr.USER_ID=usr.OWNER_ID
245 UNION ALL
246 SELECT USER_ID --not mfs grp, get SR created by
247 FROM ASG_USER usr,
248 CS_INCIDENTS_ALL_B inc,
249 JTF_TASKS_B tsk
250 WHERE tsk.TASK_ID=b_task_id
251 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
252 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
253 AND usr.USER_ID=inc.created_by;
254
255
256 BEGIN
257 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
258
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260
261 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
262 RETURN;
263 END IF;
264
265 IF CSM_SR_EVENT_PKG.is_sr_downloaded_to_owner(l_task_id) THEN
266 OPEN c_sr_grp_owner(l_task_id);
267 FETCH c_sr_grp_owner INTO l_user_id;
268 CLOSE c_sr_grp_owner;
269
270 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(l_user_id) THEN
271 csm_task_event_pkg.acc_insert(p_task_id=>l_task_id,p_user_id=>l_user_id);
272 END IF;
273 END IF;
274
275 csm_task_event_pkg.task_ins_init(p_task_id=>l_task_id);
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 l_sqlerrno := to_char(SQLCODE);
281 l_sqlerrmsg := substr(SQLERRM, 1,2000);
282 l_error_msg := ' Exception in TASK_POST_INS for task_id:'
283 || to_char(l_task_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
284 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.TASK_POST_INS',FND_LOG.LEVEL_EXCEPTION);
285 END TASK_POST_INS;
286
287
288 /* Starts TASK_DEL workflow, Should be called when new SR is created */
289 Procedure TASK_Post_DEL(
290 x_return_status OUT NOCOPY VARCHAR2
291 )
292 IS
293 l_sqlerrno VARCHAR2(20);
294 l_sqlerrmsg VARCHAR2(4000);
295 l_error_msg VARCHAR2(4000);
296 l_return_status VARCHAR2(2000);
297
298 l_task_id JTF_TASKS_B.TASK_ID%TYPE;
299
300 BEGIN
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
304 RETURN;
305 END IF;
306
307 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
308
309 csm_task_event_pkg.task_del_init(p_task_id=>l_task_id);
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 l_sqlerrno := to_char(SQLCODE);
315 l_sqlerrmsg := substr(SQLERRM, 1,2000);
316 l_error_msg := ' Exception in TASK_POST_DEL for task_id:'
317 || to_char(l_task_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
318 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.TASK_POST_DEL',FND_LOG.LEVEL_EXCEPTION);
319 END TASK_POST_DEL;
320
321 /* Task_Assignment_Post_Ins
322 */
323 --12.1
324 Procedure Task_Assignment_Post_Ins(
325 x_return_status OUT NOCOPY VARCHAR2
326 )
327 IS
328 l_task_assignment_id number(15);
329 l_sqlerrno VARCHAR2(20);
330 l_sqlerrmsg VARCHAR2(4000);
331 l_error_msg VARCHAR2(4000);
332 l_return_status VARCHAR2(2000);
333 l_task_id NUMBER;
334 l_user_id NUMBER;
335 l_assignee_id NUMBER;
336 l_owner_resource_id number;
337
338 --12.1XB7
339 CURSOR c_sr_grp_owner(b_task_id number) IS
340 SELECT USER_ID
341 FROM ASG_USER usr,
342 CS_INCIDENTS_ALL_B inc,
343 JTF_TASKS_B tsk
344 WHERE tsk.TASK_ID=b_task_id
345 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
346 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
347 AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
348 AND usr.USER_ID=usr.OWNER_ID
349 UNION ALL
350 SELECT USER_ID --not mfs grp, get SR created by
351 FROM ASG_USER usr,
352 CS_INCIDENTS_ALL_B inc,
353 JTF_TASKS_B tsk
354 WHERE tsk.TASK_ID=b_task_id
355 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
356 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
357 AND usr.USER_ID=inc.created_by;
358
359
360 CURSOR c_task(b_task_assignment_id NUMBER)
361 IS
362 select task_id,resource_id
363 from JTF_TASK_ASSIGNMENTS
364 where TASK_ASSIGNMENT_ID=b_task_assignment_id;
365
366
367
368 CURSOR c_resource_id(b_user_id NUMBER) IS
369 SELECT RESOURCE_ID
370 FROM JTF_RS_RESOURCE_EXTNS
371 WHERE USER_ID=b_user_id;
372
373 --assignee bug
374 CURSOR c_user_id(b_resource_id NUMBER) IS
375 SELECT USER_ID
376 FROM JTF_RS_RESOURCE_EXTNS
377 WHERE RESOURCE_ID=b_resource_id;
378
379 l_assignee_user_id NUMBER;
380
381 BEGIN
382
383 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
384
385 x_return_status := FND_API.G_RET_STS_SUCCESS;
386
387 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
388 RETURN;
389 END IF;
390
391 open c_task(l_task_assignment_id);
392 fetch c_task into l_task_id,l_assignee_id;
393 close c_task;
394
395 IF CSM_SR_EVENT_PKG.is_sr_downloaded_to_owner(l_task_id) THEN
396 OPEN c_sr_grp_owner(l_task_id);
397 FETCH c_sr_grp_owner INTO l_user_id;
398 CLOSE c_sr_grp_owner;
399
400 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(l_user_id) THEN
401
402 csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>l_task_assignment_id,p_user_id=>l_user_id);
403
404 -- to download other grp's resource if required
405 OPEN c_resource_id(l_user_id);
406 FETCH c_resource_id INTO l_owner_resource_id;
407 CLOSE c_resource_id;
408 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,l_assignee_id) THEN
409 --assignee bug
410 OPEN c_user_id(l_assignee_id);
411 FETCH c_user_id INTO l_assignee_user_id;
412 CLOSE c_user_id;
413 IF l_assignee_user_id IS NOT NULL THEN
414 CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_user_id);
415 END IF;
416 END IF;
417 END IF;
418 END IF;
419
420 csm_task_assignment_event_pkg.task_assignment_initializer(p_task_assignment_id=>l_task_assignment_id,
421 p_error_msg=>l_error_msg,
422 x_return_status=>l_return_status);
423
424 --Notify user of Task Assignment Insert with Start Sync Event
425 RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_id),'NEW');
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 x_return_status := FND_API.G_RET_STS_ERROR;
430 l_sqlerrno := to_char(SQLCODE);
431 l_sqlerrmsg := substr(SQLERRM, 1,2000);
432 l_error_msg := l_error_msg || '-Exception for Task_Assignment_Post_Ins :' || TO_CHAR(l_task_assignment_id)
433 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
434 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.Task_Assignment_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
435 END Task_Assignment_Post_Ins;
436
437 /*-----------------------------------------------------------------
438 Description:
439 Start the TASK_ASSIGNMENT_UPD process. Called by
440 JTF_TASK_ASSIGNMENTS_IUHK.update_task_assignment_pre
441 We retrieve the old record by selecting from db with task_assignment_id
442 Then, we compare the old resource id and new resource id for whether the resource has changed.
443 Parameter(s):
444 x_return_status
445 ------------------------------------------------------------------*/
446 Procedure Task_Assignment_Pre_Upd(x_return_status OUT NOCOPY VARCHAR2)
447 IS
448 l_task_assignment_id NUMBER;
449 l_user_id NUMBER;
450 l_dummy NUMBER;
451 l_access_id NUMBER;
452 l_sqlerrno VARCHAR2(20);
453 l_sqlerrmsg VARCHAR2(4000);
454 l_error_msg VARCHAR2(4000);
455 l_return_status VARCHAR2(2000);
456
457 CURSOR l_task_ass_pre_csr (b_task_assignment_id NUMBER) IS
458 SELECT RESOURCE_ID, assignment_status_id, task_id
459 FROM JTF_TASK_ASSIGNMENTS
460 WHERE TASK_ASSIGNMENT_ID = b_task_assignment_id;
461
462 CURSOR l_get_user_id(p_resource_id IN NUMBER)
463 IS
464 SELECT jtrs.user_id
465 FROM jtf_rs_resource_extns jtrs
466 WHERE jtrs.resource_id = p_resource_id;
467
468 BEGIN
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470
471 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
472 RETURN;
473 END IF;
474
475 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
476
477 -- initialize the rec
478 g_task_ass_pre_upd_rec := g_null_task_ass_pre_upd_typ;
479
480 -- retrieve the old resource_id.
481 OPEN l_task_ass_pre_csr (l_task_assignment_id);
482 FETCH l_task_ass_pre_csr INTO g_task_ass_pre_upd_rec;
483 IF l_task_ass_pre_csr%NOTFOUND
484 THEN
485 close l_task_ass_pre_csr;
486 return;
487 END IF;
488 CLOSE l_task_ass_pre_csr;
489
490 -- if old_resource_id and new resource_id are not mobile users then return
491 IF (( NOT CSM_UTIL_PKG.is_palm_resource(g_task_ass_pre_upd_rec.resource_id)) AND
492 ( NOT CSM_UTIL_PKG.is_palm_resource(jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id))) THEN
493 RETURN;
494 END IF;
495
496 -- And check if resource id has changed
497 IF NVL(g_task_ass_pre_upd_rec.resource_id, -1) <> NVL(jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id, -1)
498 THEN
499 -- do the purge for the old resource in the pre-hook if its a mobile resource
500 IF CSM_UTIL_PKG.is_palm_resource(g_task_ass_pre_upd_rec.resource_id) THEN
501 csm_task_assignment_event_pkg.TASK_ASSIGNMENT_PURGE_INIT(p_task_assignment_id=>l_task_assignment_id,
502 p_error_msg=>l_error_msg,
503 x_return_status=>l_return_status);
504 END IF;
505 END IF;
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 l_sqlerrno := to_char(SQLCODE);
511 l_sqlerrmsg := substr(SQLERRM, 1,2000);
512 l_error_msg := l_error_msg || '-Exception for Task_Assignment_Pre_Upd :' || TO_CHAR(l_task_assignment_id)
513 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
514 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.Task_Assignment_Pre_Upd',FND_LOG.LEVEL_EXCEPTION);
515 END Task_Assignment_Pre_Upd;
516
517 PROCEDURE Task_Assignment_Post_Upd(
518 x_return_status OUT NOCOPY VARCHAR2
519 )
520 IS
521 l_task_assignment_id NUMBER;
522 l_is_resource_updated VARCHAR2(1);
523 l_is_assg_status_updated VARCHAR2(1);
524 l_dummy NUMBER;
525 l_dml VARCHAR2(1);
526 l_timestamp DATE;
527 l_publicationitemname VARCHAR2(50);
528 l_markdirty BOOLEAN;
529 l_access_id NUMBER;
530 l_sqlerrno VARCHAR2(20);
531 l_sqlerrmsg VARCHAR2(4000);
532 l_error_msg VARCHAR2(4000);
533 l_return_status VARCHAR2(2000);
534
535 CURSOR l_task_ass_post_csr (b_task_assignment_id NUMBER)
536 IS
537 SELECT RESOURCE_ID, assignment_status_id, task_id
538 FROM JTF_TASK_ASSIGNMENTS
539 WHERE TASK_ASSIGNMENT_ID = b_task_assignment_id;
540
541 l_task_ass_post_upd_rec l_task_ass_post_csr%ROWTYPE;
542 l_null_task_ass_post_upd_rec l_task_ass_post_csr%ROWTYPE;
543
544 CURSOR l_check_acc_exists(p_task_assignment_id IN number)
545 IS
546 SELECT access_id
547 FROM csm_task_assignments_acc
548 WHERE task_assignment_id = p_task_assignment_id;
549
550 l_task_id NUMBER;
551 l_owner_id NUMBER;
552 l_owner_resource_id number;
553
554 --12.1XB7
555 CURSOR c_sr_grp_owner(b_task_id number) IS
556 SELECT USER_ID
557 FROM ASG_USER usr,
558 CS_INCIDENTS_ALL_B inc,
559 JTF_TASKS_B tsk
560 WHERE tsk.TASK_ID=b_task_id
561 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
562 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
563 AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
564 AND usr.USER_ID=usr.OWNER_ID
565 UNION ALL
566 SELECT USER_ID --not mfs grp, get SR created by
567 FROM ASG_USER usr,
568 CS_INCIDENTS_ALL_B inc,
569 JTF_TASKS_B tsk
570 WHERE tsk.TASK_ID=b_task_id
571 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
572 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
573 AND usr.USER_ID=inc.created_by;
574
575
576 CURSOR c_task(b_task_assignment_id NUMBER)
577 IS
578 select task_id
579 from JTF_TASK_ASSIGNMENTS
580 where TASK_ASSIGNMENT_ID=b_task_assignment_id;
581
582
583 CURSOR c_resource_id(b_user_id NUMBER) IS
584 SELECT RESOURCE_ID
585 FROM JTF_RS_RESOURCE_EXTNS
586 WHERE USER_ID=b_user_id;
587
588 CURSOR c_access(b_task_assignment_id NUMBER,b_user_id NUMBER)
589 IS
590 SELECT ACCESS_ID
591 FROM CSM_TASK_ASSIGNMENTS_ACC
592 WHERE TASK_ASSIGNMENT_ID=b_task_assignment_id
593 AND USER_ID=b_user_id;
594
595 --assignee bug
596 CURSOR c_user_id(b_resource_id NUMBER) IS
597 SELECT USER_ID
598 FROM JTF_RS_RESOURCE_EXTNS
599 WHERE RESOURCE_ID=b_resource_id;
600
601 l_assignee_user_id NUMBER;
602
603 BEGIN
604
605 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
606
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608
609 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
610 RETURN;
611 END IF;
612
613 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.Task_Assignment_Post_Upd for task_assg_id:' || l_task_assignment_id ,
614 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
615
616 -- initialise the flags
617 l_is_resource_updated := 'N';
618 l_is_assg_status_updated := 'N';
619 l_publicationitemname := 'CSM_TASK_ASSIGNMENTS';
620 l_dml := 'U';
621 l_task_ass_post_upd_rec := l_null_task_ass_post_upd_rec;
622
623 -- retrieve the new resource_id, assignment_status_id
624 OPEN l_task_ass_post_csr (l_task_assignment_id);
625 FETCH l_task_ass_post_csr INTO l_task_ass_post_upd_rec;
626 IF l_task_ass_post_csr%NOTFOUND
627 THEN
628 CLOSE l_task_ass_post_csr;
629 RETURN;
630 END IF;
631 CLOSE l_task_ass_post_csr;
632
633 /********12.1: FOR SR GROUP OWNER - Start of PROCESSING******/
634 open c_task(l_task_assignment_id);
635 fetch c_task into l_task_id;
636 close c_task;
637
638 IF CSM_SR_EVENT_PKG.is_sr_downloaded_to_owner(l_task_id) THEN
639 OPEN c_sr_grp_owner(l_task_id);
640 FETCH c_sr_grp_owner INTO l_owner_id;
641 CLOSE c_sr_grp_owner;
642
643 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(l_owner_id) THEN
644
645 IF g_task_ass_pre_upd_rec.resource_id<>l_task_ass_post_upd_rec.resource_id THEN
646 -- delete old resource_id from acc if he's not in any of his grp.
647 OPEN c_resource_id(l_owner_id);
648 FETCH c_resource_id INTO l_owner_resource_id;
649 CLOSE c_resource_id;
650 --old assignee
651 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,g_task_ass_pre_upd_rec.resource_id) THEN
652 --assignee bug
653 l_assignee_user_id:=NULL;
654 OPEN c_user_id(g_task_ass_pre_upd_rec.resource_id);
655 FETCH c_user_id INTO l_assignee_user_id;
656 CLOSE c_user_id;
657 IF l_assignee_user_id IS NOT NULL THEN
658 CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
659 END IF;
660 END IF;
661
662 --new assignee
663 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,l_task_ass_post_upd_rec.resource_id) THEN
664 --assignee bug
665 l_assignee_user_id:=NULL;
666 OPEN c_user_id(l_task_ass_post_upd_rec.resource_id);
667 FETCH c_user_id INTO l_assignee_user_id;
668 CLOSE c_user_id;
669 IF l_assignee_user_id IS NOT NULL THEN
670 CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
671 END IF;
672 END IF;
673 END IF;
674
675 OPEN c_access(l_task_assignment_id,l_owner_id);
676 FETCH c_access INTO l_access_id;
677 CLOSE c_access;
678
679 l_markdirty:=CSM_UTIL_PKG.MakeDirtyForUser('CSM_TASK_ASSIGNMENTS',l_access_id,l_owner_id,'U',sysdate);
680 END IF; --htm5
681
682 END IF;
683
684 /******** FOR SR GROUP OWNER - END of PROCESSING******/
685
686
687 -- if old_resource_id and new resource_id are not mobile users then return
688 IF (( NOT CSM_UTIL_PKG.is_palm_resource(g_task_ass_pre_upd_rec.resource_id)) AND
689 ( NOT CSM_UTIL_PKG.is_palm_resource(l_task_ass_post_upd_rec.resource_id))) THEN
690 CSM_UTIL_PKG.LOG('No mobile resource for task_assg_id:' || l_task_assignment_id ,
691 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
692 RETURN;
693 END IF;
694
695 -- And check if resource id has changed
696 IF g_task_ass_pre_upd_rec.resource_id <> l_task_ass_post_upd_rec.resource_id
697 THEN
698 CSM_UTIL_PKG.LOG('Resource updated for task_assg_id:' || l_task_assignment_id ,
699 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
700
701 l_is_resource_updated := 'Y';
702 OPEN l_check_acc_exists(l_task_assignment_id);
703 FETCH l_check_acc_exists INTO l_dummy;
704 IF l_check_acc_exists%NOTFOUND THEN
705 -- csm_wf_pkg.Task_Assignment_Post_Ins(l_return_status);
706 csm_task_assignment_event_pkg.task_assignment_initializer(p_task_assignment_id=>l_task_assignment_id,
707 p_error_msg=>l_error_msg,
708 x_return_status=>l_return_status);
709 --call Start Sync event - Notify Client
710 RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_id),'NEW');
711
712 CLOSE l_check_acc_exists;
713
714 RETURN;
715 END IF;
716 CLOSE l_check_acc_exists;
717 ELSE
718 l_is_resource_updated := 'N';
719 END IF;
720
721 -- And check if task_assignment_status has changed
722 IF g_task_ass_pre_upd_rec.assignment_status_id <> l_task_ass_post_upd_rec.assignment_status_id
723 THEN
724 CSM_UTIL_PKG.LOG('Status updated for task_assg_id:' || l_task_assignment_id ,
725 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
726
727 l_is_assg_status_updated := 'Y';
728
729 -- if both are not downloadable, or both are downloadable, UPDATE process
730 -- if old status is not downloadable, and new status is downloadable,
731 -- if l_is_resource_updated = 'N', INSERT process
732 -- if l_is_resource_updated := 'Y', UPDATE process which will call INSERT process
733 IF ( ( NOT IS_TASK_STATUS_DOWNLOADABLE(g_task_ass_pre_upd_rec.task_id, g_task_ass_pre_upd_rec.assignment_status_id))
734 AND IS_TASK_STATUS_DOWNLOADABLE(l_task_ass_post_upd_rec.task_id, l_task_ass_post_upd_rec.assignment_status_id)
735 AND l_is_resource_updated = 'N') THEN
736 CSM_UTIL_PKG.LOG('Status updated to downloadable for task_assg_id:' || l_task_assignment_id ,
737 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
738
739 -- Task_Assignment_Post_Ins( x_return_status );
740 csm_task_assignment_event_pkg.task_assignment_initializer(p_task_assignment_id=>l_task_assignment_id,
741 p_error_msg=>l_error_msg,
742 x_return_status=>l_return_status);
743 RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_id),'NEW');
744 RETURN;
745 END IF;
746
747 -- if old status is downloadable, and new status NOT,
748 -- if l_is_resource_updated = 'N' and l_resource_id is mobile, PURGE/DELETE process
749 -- if l_is_resource_updated := 'Y', UPDATE process which will call PURGE process
750 IF ( IS_TASK_STATUS_DOWNLOADABLE(g_task_ass_pre_upd_rec.task_id, g_task_ass_pre_upd_rec.assignment_status_id )
751 AND (NOT IS_TASK_STATUS_DOWNLOADABLE(l_task_ass_post_upd_rec.task_id, l_task_ass_post_upd_rec.assignment_status_id))
752 AND l_is_resource_updated = 'N') THEN
753 CSM_UTIL_PKG.LOG('Status updated to non-downloadable for task_assg_id:' || l_task_assignment_id ,
754 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
755
756 Task_Assignment_Post_Del( x_return_status );
757 RETURN;
758 END IF;
759 ELSE
760 l_is_assg_status_updated := 'N';
761 END IF;
762
763 --Bug 5182470
764 -- MP: If resource is not updated, then record should be there in the access table
765 IF l_is_resource_updated = 'N' THEN
766 OPEN l_check_acc_exists(l_task_assignment_id);
767 FETCH l_check_acc_exists INTO l_access_id;
768 IF l_check_acc_exists%NOTFOUND THEN
769 CLOSE l_check_acc_exists;
770 RETURN;
771 END IF;
772 CLOSE l_check_acc_exists;
773 l_markdirty := csm_util_pkg.MakeDirtyForResource(l_publicationitemname,
774 l_access_id,
775 l_task_ass_post_upd_rec.resource_id,
776 l_dml,
777 sysdate);
778 RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_id),'UPDATE');
779
780 ELSIF l_is_resource_updated = 'Y' THEN
781 -- check if the new resource is a mobile resource; if it is then do a insert
782 -- the old resource is dropped in the pre-upd hook
783 IF CSM_UTIL_PKG.is_palm_resource(l_task_ass_post_upd_rec.resource_id) THEN
784 -- csm_wf_pkg.Task_Assignment_Post_Ins(l_return_status);
785 csm_task_assignment_event_pkg.task_assignment_initializer(p_task_assignment_id=>l_task_assignment_id,
786 p_error_msg=>l_error_msg,
787 x_return_status=>l_return_status);
788 RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_id),'NEW');
789 END IF;
790 END IF;
791
792 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.Task_Assignment_Post_Upd for task_assg_id:' || l_task_assignment_id ,
793 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 l_sqlerrno := to_char(SQLCODE);
799 l_sqlerrmsg := substr(SQLERRM, 1,2000);
800 l_error_msg := l_error_msg || '-Exception for Task_Assignment_Post_Upd :' || TO_CHAR(l_task_assignment_id)
801 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
802 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.Task_Assignment_Post_Upd',FND_LOG.LEVEL_EXCEPTION);
803 END Task_Assignment_Post_Upd;
804
805 /*--------------------------------------------------------
806 Description:
807 Start the workflow process TASK_ASSIGNMENT_PURGE.
808 Invoked by JTF_TASK_ASSIGNMENTS_IUHK.delete_task_assignment_post
809 and by concurrent program to purge closed task assignments
810 older than specified in profile: CSF_M_HISTORY.
811 Parameter(s):
812 x_return_status
813 --------------------------------------------------------*/
814 PROCEDURE Task_Assignment_Post_Del(
815 x_return_status OUT NOCOPY VARCHAR2
816 )
817 IS
818 -- define the primary key and assign value from the global variable
819 l_task_assignment_id number(15);
820 l_sqlerrno VARCHAR2(20);
821 l_sqlerrmsg VARCHAR2(4000);
822 l_error_msg VARCHAR2(4000);
823 l_return_status VARCHAR2(2000);
824
825 BEGIN
826
827 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
828
829 x_return_status := FND_API.G_RET_STS_SUCCESS;
830
831 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
832 RETURN;
833 END IF;
834
835 csm_task_assignment_event_pkg.TASK_ASSIGNMENT_PURGE_INIT(p_task_assignment_id=>l_task_assignment_id,
836 p_error_msg=>l_error_msg,
837 x_return_status=>l_return_status);
838 EXCEPTION
839 WHEN OTHERS THEN
840 x_return_status := FND_API.G_RET_STS_ERROR ;
841 l_sqlerrno := to_char(SQLCODE);
842 l_sqlerrmsg := substr(SQLERRM, 1,2000);
843 l_error_msg := l_error_msg || '-Exception for Task_Assignment_Post_Del :' || TO_CHAR(l_task_assignment_id)
844 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
845 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.Task_Assignment_Post_Del',FND_LOG.LEVEL_EXCEPTION);
846 END Task_Assignment_Post_Del;
847
848 /*-----------------------------------------------------------------
849 Description:
850 Start the workflow process TASK_UPD_USERLOOP.
851 Invoked by jtf_tasks_iuhk.update_task_pre
852 The global variable for IUHK is: jtf_tasks_pub.p_task_user_hooks(.task_id)
853
854 we check all the task_assignments, whether they are mobile users.
855 No - nothing
856 Yes - check task_status. Whether status changed
857 No - if (task_assignment_acc record exists ) do UPDATE
858 Yes - check old status and new status
859 old downloadable, new NOT, TASK_ASSIGNMENT_PURGE
860 old new both not downloadable, do nothing
861 old new both downloadable, (if acc record exists) do UPDATE
862 old NOT, new downloadable, TASK_ASSIGNMENT_INS
863
864
865 Parameter(s):
866 x_return_status
867 ------------------------------------------------------------------*/
868 Procedure Task_Pre_Upd ( x_return_status OUT NOCOPY VARCHAR2)
869 IS
870 l_jtf_task_id NUMBER;
871 l_err_msg VARCHAR2(4000);
872 l_return_status VARCHAR2(4000);
873 l_sqlerrno VARCHAR2(20);
874 l_sqlerrmsg VARCHAR2(4000);
875 l_error_msg VARCHAR2(4000);
876 l_return_status VARCHAR2(2000);
877
878 CURSOR l_task_pre_upd_csr(b_task_id NUMBER)
879 IS
880 SELECT TASK_STATUS_ID, TASK_TYPE_ID, SCHEDULED_START_DATE, SCHEDULED_END_DATE, task_id
881 FROM JTF_TASKS_B
882 WHERE TASK_ID = b_task_id;
883
884
885 BEGIN
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887
888 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
889 RETURN;
890 END IF;
891
892 l_jtf_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
893
894
895 --used for TASK_TYPE CHANGE
896 g_task_downloaded_to_owner:=CSM_SR_EVENT_PKG.is_sr_downloaded_to_owner(l_jtf_task_id);
897
898 -- initialize the rec
899 g_task_pre_upd_rec := g_null_task_pre_upd_typ;
900
901 -- retrieve the record prior update.
902 OPEN l_task_pre_upd_csr (l_jtf_task_id);
903 FETCH l_task_pre_upd_csr INTO g_task_pre_upd_rec;
904 IF l_task_pre_upd_csr%NOTFOUND
905 THEN
906 CLOSE l_task_pre_upd_csr;
907 RETURN;
908 END IF;
909 CLOSE l_task_pre_upd_csr;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 x_return_status := FND_API.G_RET_STS_ERROR;
914 l_sqlerrno := to_char(SQLCODE);
915 l_sqlerrmsg := substr(SQLERRM, 1,2000);
916 l_error_msg := 'Exception in Task_Pre_Upd for task_id:' || TO_CHAR(l_jtf_task_id)
917 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
918 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.Task_Pre_Upd',FND_LOG.LEVEL_EXCEPTION);
919 END Task_Pre_Upd;
920
921 Procedure Task_Post_Upd (
922 x_return_status OUT NOCOPY VARCHAR2
923 )
924 IS
925 TYPE id_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
926 l_jtf_task_id NUMBER;
927 l_task_assignment_list id_list;
928 l_user_list id_list;
929 l_resource_list id_list;
930 l_error_msg VARCHAR2(4000);
931 l_return_status VARCHAR2(4000);
932 l_sqlerrno VARCHAR2(20);
933 l_sqlerrmsg VARCHAR2(4000);
934
935 CURSOR c_task_ass_csr (b_task_id NUMBER) IS
936 SELECT rs.RESOURCE_ID, rs.USER_ID, tas.TASK_ASSIGNMENT_ID
937 FROM JTF_TASK_ASSIGNMENTS tas, JTF_RS_RESOURCE_EXTNS rs
938 WHERE TASK_ID = b_task_id
939 AND tas.RESOURCE_ID = rs.RESOURCE_ID;
940
941 CURSOR l_task_post_upd_csr(b_task_id NUMBER)
942 IS
943 SELECT TASK_STATUS_ID, TASK_TYPE_ID, SCHEDULED_START_DATE, SCHEDULED_END_DATE, task_id
944 FROM JTF_TASKS_B
945 WHERE TASK_ID = b_task_id;
946
947 --12.1XB7
948 CURSOR c_sr_grp_owner(b_task_id number) IS
949 SELECT USER_ID
950 FROM ASG_USER usr,
951 CS_INCIDENTS_ALL_B inc,
952 JTF_TASKS_B tsk
953 WHERE tsk.TASK_ID=b_task_id
954 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
955 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
956 AND usr.GROUP_ID=inc.owner_group_id --is_mfs_grp
957 AND usr.USER_ID=usr.OWNER_ID
958 UNION ALL
959 SELECT USER_ID --not mfs grp, get SR created by
960 FROM ASG_USER usr,
961 CS_INCIDENTS_ALL_B inc,
962 JTF_TASKS_B tsk
963 WHERE tsk.TASK_ID=b_task_id
964 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
965 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
966 AND usr.USER_ID=inc.created_by;
967
968 CURSOR c_task_assignments(b_task_id NUMBER)
969 IS
970 SELECT TASK_ASSIGNMENT_ID,resource_id
971 FROM JTF_TASK_ASSIGNMENTS
972 WHERE TASK_ID=b_task_id;
973
974 CURSOR c_resource_id(b_user_id NUMBER) IS
975 SELECT RESOURCE_ID
976 FROM JTF_RS_RESOURCE_EXTNS
977 WHERE USER_ID=b_user_id;
978
979 --assignee bug
980 CURSOR c_user_id(b_resource_id NUMBER) IS
981 SELECT USER_ID
982 FROM JTF_RS_RESOURCE_EXTNS
983 WHERE RESOURCE_ID=b_resource_id;
984
985 l_assignee_user_id NUMBER;
986
987 l_task_post_upd_rec l_task_post_upd_csr%ROWTYPE;
988 l_null_task_post_upd_rec l_task_post_upd_csr%ROWTYPE;
989 l_task_downloadable_to_owner BOOLEAN;
990 l_owner_id NUMBER;
991 l_owner_resource_id NUMBER;
992
993 BEGIN
994 l_jtf_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
995
996 x_return_status := FND_API.G_RET_STS_SUCCESS;
997
998 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
999 RETURN;
1000 END IF;
1001
1002 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.Task_Post_Upd for task_id:' || l_jtf_task_id ,
1003 'CSM_WF_PKG.Task_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
1004
1005 /*12.1: TASK_TYPE CHANGE should be captured*/
1006 l_task_downloadable_to_owner := CSM_SR_EVENT_PKG.is_sr_downloaded_to_owner(l_jtf_task_id);
1007
1008 IF (NOT g_task_downloaded_to_owner AND l_task_downloadable_to_owner) THEN
1009
1010 OPEN c_sr_grp_owner(l_jtf_task_id);
1011 FETCH c_sr_grp_owner INTO l_owner_id;
1012 CLOSE c_sr_grp_owner;
1013
1014 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(l_owner_id) THEN
1015 csm_task_event_pkg.acc_insert(p_task_id=>l_jtf_task_id,p_user_id=>l_owner_id);
1016
1017 for assign_rec in c_task_assignments(l_jtf_task_id)
1018 loop
1019 csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
1020
1021 -- to download other grp's resource if required
1022 OPEN c_resource_id(l_owner_id);
1023 FETCH c_resource_id INTO l_owner_resource_id;
1024 CLOSE c_resource_id;
1025 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,assign_rec.resource_id) THEN
1026 --assignee bug
1027 OPEN c_user_id(assign_rec.resource_id);
1028 FETCH c_user_id INTO l_assignee_user_id;
1029 CLOSE c_user_id;
1030 IF l_assignee_user_id IS NOT NULL THEN
1031 CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
1032 END IF;
1033 END IF;
1034 --to decide if LOBS shd be downloaded for task assignment and also service history
1035 end loop;
1036 END IF;
1037 ELSIF (g_task_downloaded_to_owner AND NOT l_task_downloadable_to_owner) THEN
1038 OPEN c_sr_grp_owner(l_jtf_task_id);
1039 FETCH c_sr_grp_owner INTO l_owner_id;
1040 CLOSE c_sr_grp_owner;
1041
1042 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(l_owner_id) THEN
1043
1044 csm_task_event_pkg.acc_delete(p_task_id=>l_jtf_task_id,p_user_id=>l_owner_id);
1045
1046 for assign_rec in c_task_assignments(l_jtf_task_id)
1047 loop
1048
1049 csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
1050
1051 /*Other grp's member Resource to be deleted from acc if assigned to him*/
1052 OPEN c_resource_id(l_owner_id);
1053 FETCH c_resource_id INTO l_owner_resource_id;
1054 CLOSE c_resource_id;
1055 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,assign_rec.resource_id) THEN
1056 --assignee bug
1057 OPEN c_user_id(assign_rec.resource_id);
1058 FETCH c_user_id INTO l_assignee_user_id;
1059 CLOSE c_user_id;
1060 IF l_assignee_user_id IS NOT NULL THEN
1061 CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
1062 END IF;
1063 END IF;
1064 end loop;
1065 END IF;
1066 END IF;
1067
1068 -- initialize the rec
1069 l_task_post_upd_rec := l_null_task_post_upd_rec;
1070
1071 -- retrieve the record prior update.
1072 OPEN l_task_post_upd_csr (l_jtf_task_id);
1073 FETCH l_task_post_upd_csr INTO l_task_post_upd_rec;
1074 IF l_task_post_upd_csr%NOTFOUND
1075 THEN
1076 CLOSE l_task_post_upd_csr;
1077 RETURN;
1078 END IF;
1079 CLOSE l_task_post_upd_csr;
1080
1081 -- re-initialize the table and count
1082 IF l_task_assignment_list.COUNT > 0 THEN
1083 l_task_assignment_list.DELETE;
1084 l_user_list.DELETE;
1085 l_resource_list.DELETE;
1086 END IF;
1087
1088 -- get the assignee's for the task
1089 OPEN c_task_ass_csr(l_jtf_task_id);
1090 FETCH c_task_ass_csr BULK COLLECT INTO l_resource_list, l_user_list, l_task_assignment_list;
1091 CLOSE c_task_ass_csr;
1092
1093 IF ( l_task_assignment_list.COUNT > 0 ) THEN
1094 /*
1095 check old data and new data
1096 -- old downloadable, new NOT, TASK_ASSIGNMENT_PURGE
1097 old new both not downloadable, do nothing
1098 old new both downloadable, (if acc record exists) do UPDATE
1099 -- old NOT, new downloadable, TASK_ASSIGNMENT_INS
1100 */
1101 -- CSM_UTIL_PKG.pvt_log('Compare old and new data');
1102 -- old NOT downloadable, new downloadable, TASK_ASSIGNMENT_INS
1103 IF ( ( NOT IS_TASK_DOWNLOADABLE(g_task_pre_upd_rec.task_id,
1104 g_task_pre_upd_rec.task_status_id,
1105 g_task_pre_upd_rec.task_type_id,
1106 g_task_pre_upd_rec.scheduled_start_date,
1107 g_task_pre_upd_rec.scheduled_end_date)
1108 )
1109 AND
1110 IS_TASK_DOWNLOADABLE(l_task_post_upd_rec.task_id,
1111 l_task_post_upd_rec.task_status_id,
1112 l_task_post_upd_rec.task_type_id,
1113 l_task_post_upd_rec.scheduled_start_date,
1114 l_task_post_upd_rec.scheduled_end_date)
1115 )
1116 THEN
1117 CSM_UTIL_PKG.LOG('old NOT downloadable, new downloadable for task_id:' || l_jtf_task_id ,
1118 'CSM_WF_PKG.Task_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
1119
1120 FOR i IN l_task_assignment_list.FIRST..l_task_assignment_list.LAST LOOP
1121 IF (CSM_UTIL_PKG.is_palm_resource(l_resource_list(i))) THEN
1122 csm_task_assignment_event_pkg.task_assignment_initializer(p_task_assignment_id=>l_task_assignment_list(i),
1123 p_error_msg=>l_error_msg,
1124 x_return_status=>l_return_status);
1125 /*call Start Sync event - NOTIFY CLIENT*/
1126 RAISE_START_AUTO_SYNC_EVENT('CSM_TASK_ASSIGNMENTS',to_char(l_task_assignment_list(i)),'NEW');
1127
1128 END IF;
1129 END LOOP;
1130
1131 -- old downloadable, new NOT, TASK_ASSIGNMENT_PURGE
1132 ELSIF ( ( NOT IS_TASK_DOWNLOADABLE(l_task_post_upd_rec.task_id,
1133 l_task_post_upd_rec.task_status_id,
1134 l_task_post_upd_rec.task_type_id,
1135 l_task_post_upd_rec.scheduled_start_date,
1136 l_task_post_upd_rec.scheduled_end_date)
1137 )
1138 AND IS_TASK_DOWNLOADABLE(g_task_pre_upd_rec.task_id,
1139 g_task_pre_upd_rec.task_status_id,
1140 g_task_pre_upd_rec.task_type_id,
1141 g_task_pre_upd_rec.scheduled_start_date,
1142 g_task_pre_upd_rec.scheduled_end_date)
1143 )
1144 THEN
1145 CSM_UTIL_PKG.LOG('old downloadable, new NOT downloadable for task_id:' || l_jtf_task_id ,
1146 'CSM_WF_PKG.Task_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
1147
1148 FOR i IN l_task_assignment_list.FIRST..l_task_assignment_list.LAST LOOP
1149 IF (CSM_UTIL_PKG.is_palm_resource(l_resource_list(i))) THEN
1150 csm_task_assignment_event_pkg.TASK_ASSIGNMENT_PURGE_INIT
1151 (p_task_assignment_id=>l_task_assignment_list(i),
1152 p_error_msg=>l_error_msg,
1153 x_return_status=>l_return_status);
1154 END IF;
1155 END LOOP;
1156
1157 END IF;
1158 END IF ; -- count > 0
1159
1160 --do the updates for the task
1161 CSM_UTIL_PKG.LOG('Do updates for task_id:' || l_jtf_task_id ,
1162 'CSM_WF_PKG.Task_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
1163
1164 csm_task_event_pkg.TASK_MAKE_DIRTY_U_FOREACHUSER(l_jtf_task_id,l_error_msg, l_return_status);
1165 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1166 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.Task_Pre_Upd', FND_LOG.LEVEL_ERROR);
1167 END IF;
1168
1169 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.Task_Post_Upd for task_id:' || l_jtf_task_id ,
1170 'CSM_WF_PKG.Task_Post_Upd',FND_LOG.LEVEL_PROCEDURE);
1171
1172 /*call Start Sync event - NOTIFY CLIENT*/
1173 RAISE_START_AUTO_SYNC_EVENT('CSM_TASKS',to_char(l_jtf_task_id),'UPDATE');
1174
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 l_sqlerrno := to_char(SQLCODE);
1180 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1181 l_error_msg := l_error_msg || '-' || 'Exception in Task_Post_Upd for task_id:' || l_jtf_task_id
1182 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1183 csm_util_pkg.LOG(l_error_msg,'CSM_WF_PKG.Task_Post_Upd', FND_LOG.LEVEL_EXCEPTION);
1184 END Task_Post_Upd;
1185
1186 /********************************************************
1187 Starts the USER_RESP_INS workflow. Should be called when new
1188 responsibility is assigned to a user
1189
1190 Arguments:
1191 p_user_id: User to which responsibility has been assigned
1192 p_responsibility_id: The responsibility assigned
1193 *********************************************************/
1194 PROCEDURE User_Resp_Post_Ins(p_user_id IN NUMBER, p_responsibility_id IN NUMBER)
1195 IS
1196 l_sqlerrno VARCHAR2(20);
1197 l_sqlerrmsg VARCHAR2(4000);
1198 l_error_msg VARCHAR2(4000);
1199 l_return_status VARCHAR2(2000);
1200
1201 BEGIN
1202 CSM_UTIL_PKG.LOG('Entering User_Resp_Post_Ins for user_id:' || p_user_id,
1203 'csm_wf_pkg.User_Resp_Post_Ins',FND_LOG.LEVEL_PROCEDURE);
1204
1205 csm_user_event_pkg.user_resp_ins_initializer(p_user_id=>p_user_id,
1206 p_responsibility_id=>p_responsibility_id);
1207
1208 CSM_UTIL_PKG.LOG('Leaving User_Resp_Post_Ins for user_id:' || p_user_id,
1209 'csm_wf_pkg.User_Resp_Post_Ins',FND_LOG.LEVEL_PROCEDURE);
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 l_sqlerrno := to_char(SQLCODE);
1213 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1214 l_error_msg := ' Exception in User_Resp_Post_Ins for user_id:'
1215 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1216 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_wf_pkg.User_Resp_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
1217 RAISE;
1218 END User_Resp_Post_Ins;
1219
1220 --12.1XB6
1221 /* Starts SR_INS workflow, Should be called when new SR is created */
1222 Procedure SR_Post_Ins(
1223 x_return_status OUT NOCOPY VARCHAR2
1224 )
1225 IS
1226 l_sqlerrno VARCHAR2(20);
1227 l_sqlerrmsg VARCHAR2(4000);
1228 l_error_msg VARCHAR2(4000);
1229 l_return_status VARCHAR2(2000);
1230 l_incident_id cs_incidents_all.incident_id%TYPE;
1231 l_owner_group_id NUMBER;
1232 l_user_id NUMBER;
1233
1234 CURSOR l_sr_csr(p_incident_id IN NUMBER)
1235 IS
1236 SELECT owner_group_id,created_by
1237 FROM cs_incidents_all_b
1238 WHERE incident_id = p_incident_id;
1239
1240 BEGIN
1241
1242 l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
1243
1244 x_return_status := FND_API.G_RET_STS_SUCCESS;
1245
1246 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1247 RETURN;
1248 END IF;
1249
1250
1251
1252 OPEN l_sr_csr(l_incident_id);
1253 FETCH l_sr_csr INTO l_owner_group_id,l_user_id;
1254 IF l_sr_csr%FOUND THEN
1255 IF ( NOT CSM_UTIL_PKG.is_mfs_group(l_owner_group_id) AND NOT CSM_UTIL_PKG.is_palm_user(l_user_id)) THEN
1256 CLOSE l_sr_csr;
1257 RETURN;
1258 END IF;
1259 END IF;
1260 CLOSE l_sr_csr;
1261
1262 csm_sr_event_pkg.sr_ins_init(l_incident_id);
1263
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 x_return_status := FND_API.G_RET_STS_ERROR ;
1267 l_sqlerrno := to_char(SQLCODE);
1268 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1269 l_error_msg := l_error_msg || '- Exception in SR_Post_Ins for incident_id:'
1270 || TO_CHAR(l_incident_id) || l_sqlerrno || ':' || l_sqlerrmsg;
1271 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.SR_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
1272 END SR_Post_Ins;
1273
1274 /*
1275 * The user hook interface for SR pre_update
1276 */
1277 --12.1
1278 PROCEDURE SR_Pre_Upd( x_return_status OUT NOCOPY VARCHAR2)
1279 IS
1280 l_incident_id cs_incidents_all.incident_id%TYPE;
1281 l_rec_count NUMBER;
1282 l_sqlerrno VARCHAR2(20);
1283 l_sqlerrmsg VARCHAR2(4000);
1284 l_error_msg VARCHAR2(4000);
1285 l_return_status VARCHAR2(2000);
1286 l_location_id JTF_TASKS_B.LOCATION_ID%TYPE;
1287
1288 --R12 Asset
1289 CURSOR l_sr_pre_upd_csr (p_incident_id IN number)
1290 IS
1291 SELECT incident_id,
1292 customer_id,
1293 install_site_id,
1294 customer_product_id,
1295 inventory_item_id,
1296 inv_organization_id,
1297 contract_service_id,
1298 incident_location_id,
1299 customer_id,
1300 incident_location_id,
1301 owner_group_id
1302 FROM cs_incidents_all_b csi
1303 WHERE incident_id = p_incident_id;
1304
1305 CURSOR l_addr_id_csr (b_incident_id IN NUMBER)
1306 IS
1307 SELECT ADDRESS_ID
1308 FROM JTF_TASKS_B
1309 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
1310 AND SOURCE_OBJECT_ID = b_incident_id;
1311
1312 --R12 Asset
1313 CURSOR l_location_id_csr (b_incident_id IN NUMBER)
1314 IS
1315 SELECT ADDRESS_ID,LOCATION_ID
1316 FROM JTF_TASKS_B
1317 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
1318 AND SOURCE_OBJECT_ID = b_incident_id;
1319
1320 -- get all the contacts for the SR; this is used in sr_post_upd
1321 -- to verify if the contact has been updated
1322 CURSOR l_sr_cont_pts_pre_upd_csr(p_incident_id IN number)
1323 IS
1324 SELECT sr_contact_point_id,
1325 party_id,
1326 contact_point_id,
1327 primary_flag,
1328 contact_point_type,
1329 contact_type,
1330 last_update_date,
1331 creation_date
1332 FROM cs_hz_sr_contact_points
1333 WHERE incident_id = p_incident_id;
1334
1335 l_sr_cont_pts_pre_upd_rec l_sr_cont_pts_pre_upd_csr%ROWTYPE;
1336
1337 BEGIN
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339
1340 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1341 RETURN;
1342 END IF;
1343
1344 l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
1345
1346 -- nullify the global rec
1347 g_sr_pre_upd_rec := g_null_sr_pre_upd_rec;
1348
1349 OPEN l_sr_pre_upd_csr( l_incident_id );
1350 FETCH l_sr_pre_upd_csr INTO g_sr_pre_upd_rec;
1351 IF l_sr_pre_upd_csr%NOTFOUND THEN
1352 close l_sr_pre_upd_csr;
1353 return;
1354 END IF;
1355 CLOSE l_sr_pre_upd_csr;
1356
1357 IF g_sr_pre_upd_rec.INSTALL_SITE_ID IS NULL THEN
1358 OPEN l_addr_id_csr(l_incident_id);
1359 FETCH l_addr_id_csr INTO g_sr_pre_upd_rec.INSTALL_SITE_ID;
1360 CLOSE l_addr_id_csr;
1361 END IF;
1362
1363 IF g_sr_pre_upd_rec.INCIDENT_LOCATION_ID IS NULL THEN
1364 OPEN l_location_id_csr(l_incident_id);
1365 FETCH l_location_id_csr INTO g_sr_pre_upd_rec.INCIDENT_LOCATION_ID,l_location_id;
1366 CLOSE l_location_id_csr;
1367 IF l_location_id IS NOT NULL THEN--r12 Asset
1368 g_sr_pre_upd_rec.INCIDENT_LOCATION_ID := l_location_id;
1369 END IF;
1370 END IF;
1371
1372 -- nullify the global contact rec
1373 g_sr_cont_points_pre_upd_tbl := g_null_sr_cont_points_tbl;
1374 l_rec_count := 0;
1375 -- get all the pre-upd contacts for the SR
1376 OPEN l_sr_cont_pts_pre_upd_csr(l_incident_id);
1377 LOOP
1378 FETCH l_sr_cont_pts_pre_upd_csr INTO l_sr_cont_pts_pre_upd_rec;
1379 IF l_sr_cont_pts_pre_upd_csr%NOTFOUND THEN
1380 EXIT;
1381 ELSE
1382 l_rec_count := l_rec_count + 1;
1383 g_sr_cont_points_pre_upd_tbl(l_rec_count).SR_CONTACT_POINT_ID := l_sr_cont_pts_pre_upd_rec.sr_contact_point_id;
1384 g_sr_cont_points_pre_upd_tbl(l_rec_count).PARTY_ID := l_sr_cont_pts_pre_upd_rec.party_id;
1385 g_sr_cont_points_pre_upd_tbl(l_rec_count).contact_point_id := l_sr_cont_pts_pre_upd_rec.contact_point_id;
1386 g_sr_cont_points_pre_upd_tbl(l_rec_count).primary_flag := l_sr_cont_pts_pre_upd_rec.primary_flag;
1387 g_sr_cont_points_pre_upd_tbl(l_rec_count).contact_point_type := l_sr_cont_pts_pre_upd_rec.contact_point_type;
1388 g_sr_cont_points_pre_upd_tbl(l_rec_count).CONTACT_TYPE := l_sr_cont_pts_pre_upd_rec.contact_type;
1389 g_sr_cont_points_pre_upd_tbl(l_rec_count).last_update_date := l_sr_cont_pts_pre_upd_rec.last_update_date;
1390 g_sr_cont_points_pre_upd_tbl(l_rec_count).creation_date := l_sr_cont_pts_pre_upd_rec.creation_date;
1391 END IF;
1392 END LOOP;
1393 CLOSE l_sr_cont_pts_pre_upd_csr;
1394
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 l_sqlerrno := to_char(SQLCODE);
1399 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1400 l_error_msg := 'Exception in sr_pre_upd for incident_id:' || TO_CHAR(l_incident_id)
1401 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1402 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.sr_pre_upd',FND_LOG.LEVEL_EXCEPTION);
1403 END sr_pre_upd;
1404
1405 /********************************************************
1406 Starts the SR_UPD workflow. Should be called when new
1407 task assignment is made
1408
1409 Arguments:
1410 p_incident_id: INCIDENT_ID of the SR updated
1411 p_old_install_site_id: Old value of the INSTALL_SITE_ID
1412 p_is_sr_customer_updated: true, if the customer has been updated, false otherwise
1413 p_old_sr_customer_id: Old value of the CUSTOMER_ID
1414 p_is_sr_instance_updated: true, if the instance has been updated, false otherwise
1415 p_old_instance_id: Old value of the INSTANCE_ID
1416 p_is_inventory_item_updated: true, if the inventory item has been updated, false otherwise
1417 p_old_inventory_item_id: Old value of the INVENTORY_ITEM_ID
1418 *********************************************************/
1419 --12.1
1420 Procedure SR_Post_Upd( x_return_status OUT NOCOPY VARCHAR2)
1421 IS
1422 l_incident_id cs_incidents_all.incident_id%TYPE;
1423 l_install_site_id cs_incidents_all_b.install_site_id%TYPE;
1424 l_is_install_site_updated char(1);
1425 l_sr_customer_id cs_incidents_all_b.customer_id%TYPE;
1426 l_is_sr_customer_updated char(1);
1427 l_inventory_item_id cs_incidents_all_b.inventory_item_id%TYPE;
1428 l_is_inventory_item_updated char(1);
1429 l_instance_id cs_incidents_all_b.customer_product_id%TYPE;
1430 l_is_sr_instance_updated char(1);
1431 l_rec_count number;
1432 l_is_sr_contact_ins char(1);
1433 l_is_sr_contact_upd char(1);
1434 l_is_sr_contact_del char(1);
1435 l_is_contr_service_id_updated char(1);
1436 l_is_mobile_sr number :=0;
1437 l_sqlerrno VARCHAR2(20);
1438 l_sqlerrmsg VARCHAR2(4000);
1439 l_error_msg VARCHAR2(4000);
1440 l_return_status VARCHAR2(2000);
1441 l_incident_location_id cs_incidents_all_b.incident_location_id%TYPE;
1442 l_is_incident_location_updated char(1);
1443 l_location_id JTF_TASKS_B.LOCATION_ID%TYPE;
1444 l_is_owner_changed char(1);
1445 l_old_owner NUMBER;
1446 l_current_owner NUMBER;
1447
1448 CURSOR l_sr_post_upd_csr (p_incident_id IN number)
1449 IS
1450 SELECT incident_id,
1451 customer_id,
1452 install_site_id,
1453 customer_product_id,
1454 inventory_item_id,
1455 inv_organization_id,
1456 contract_service_id,
1457 incident_location_id,
1458 owner_group_id,
1459 created_by
1460 FROM cs_incidents_all_b
1461 WHERE incident_id = p_incident_id;
1462
1463 l_sr_post_upd_rec l_sr_post_upd_csr%ROWTYPE;
1464
1465 CURSOR l_addr_id_csr (b_incident_id IN NUMBER)
1466 IS
1467 SELECT ADDRESS_ID
1468 FROM JTF_TASKS_B
1469 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
1470 AND SOURCE_OBJECT_ID = b_incident_id;
1471
1472 CURSOR l_location_id_csr (b_incident_id IN NUMBER)
1473 IS
1474 SELECT ADDRESS_ID,LOCATION_ID
1475 FROM JTF_TASKS_B
1476 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
1477 AND SOURCE_OBJECT_ID = b_incident_id;
1478
1479 -- get all the post_upd contacts for the SR;
1480 CURSOR l_sr_cont_pts_post_upd_csr(p_incident_id IN number)
1481 IS
1482 SELECT sr_contact_point_id,
1483 party_id,
1484 contact_point_id,
1485 primary_flag,
1486 contact_point_type,
1487 contact_type,
1488 last_update_date,
1489 creation_date
1490 FROM cs_hz_sr_contact_points
1491 WHERE incident_id = p_incident_id;
1492
1493 l_sr_cont_pts_post_upd_rec l_sr_cont_pts_post_upd_csr%ROWTYPE;
1494
1495 -- cursor to check if SR is owned by a mobile grp, assigned to a mobile user or
1496 -- has a task created by a mobile user
1497 --12.1XB6
1498 CURSOR l_is_mobile_sr_csr(p_incident_id IN NUMBER)
1499 IS
1500 SELECT 1
1501 FROM cs_incidents_all_b csa
1502 WHERE csa.incident_id = p_incident_id
1503 AND EXISTS(SELECT 1 FROM ASG_USER WHERE GROUP_ID=csa.OWNER_GROUP_ID AND NVL(MULTI_PLATFORM,'N')='N')
1504 UNION ALL
1505 SELECT 1
1506 FROM cs_incidents_all_b csa
1507 WHERE csa.incident_id = p_incident_id
1508 AND EXISTS(SELECT 1 FROM ASG_USER WHERE USER_ID=csa.created_by AND NVL(MULTI_PLATFORM,'N')='N')
1509 UNION ALL
1510 SELECT 1
1511 FROM jtf_tasks_b jt
1512 WHERE jt.source_object_id = p_incident_id
1513 AND jt.source_object_type_code = 'SR'
1514 AND EXISTS(SELECT 1 FROM ASG_USER WHERE USER_ID=jt.created_by AND NVL(MULTI_PLATFORM,'N')='N')
1515 UNION ALL
1516 SELECT 1
1517 FROM jtf_task_assignments jta,
1518 jtf_tasks_b jt
1519 WHERE jt.source_object_id = p_incident_id
1520 AND jt.source_object_type_code = 'SR'
1521 AND jta.task_id = jt.task_id
1522 AND jta.resource_type_code='RS_EMPLOYEE'
1523 AND EXISTS(SELECT 1 FROM ASG_USER WHERE resource_id = jta.resource_id);
1524
1525 BEGIN
1526
1527 l_incident_id := cs_servicerequest_pvt.user_hooks_rec.request_id;
1528
1529 x_return_status := FND_API.G_RET_STS_SUCCESS;
1530
1531
1532 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1533 RETURN;
1534 END IF;
1535
1536 --owner change SHD BE CAPTURED TO DOWNLOAD ONCE THE COLUMN OWNER_GRP_ID IS ADDED
1537 l_is_install_site_updated := 'N';
1538 l_is_sr_customer_updated := 'N';
1539 l_is_inventory_item_updated := 'N';
1540 l_is_sr_instance_updated := 'N';
1541 l_is_contr_service_id_updated := 'N';
1542 l_is_incident_location_updated := 'N';
1543
1544 --IF OWNER_CHANGED REASSIGN
1545
1546 OPEN l_sr_post_upd_csr(l_incident_id);
1547 FETCH l_sr_post_upd_csr INTO l_sr_post_upd_rec;
1548
1549 l_old_owner :=CSM_UTIL_PKG.get_group_owner(g_sr_pre_upd_rec.owner_group_id);
1550 l_current_owner:=CSM_UTIL_PKG.get_group_owner(l_sr_post_upd_rec.owner_group_id);
1551
1552 --12.1XB6 --to avoid update if created_by and group owner are from same group
1553 -- and owner_group_id in SR is updated from NULL
1554 IF l_old_owner=-1 THEN
1555 l_old_owner :=csm_util_pkg.get_owner(l_sr_post_upd_rec.created_by);
1556 END IF;
1557 IF l_current_owner = -1 THEN
1558 l_current_owner :=csm_util_pkg.get_owner(l_sr_post_upd_rec.created_by);
1559 END IF;
1560
1561 IF l_old_owner <> l_current_owner THEN
1562 CSM_SR_EVENT_PKG.sr_del_init(l_incident_id,l_old_owner);
1563 CSM_SR_EVENT_PKG.sr_ins_init(l_incident_id);
1564 END IF;
1565
1566 -- only start the SR upd WF if it is linked to a mobile user
1567 OPEN l_is_mobile_sr_csr(l_incident_id);
1568 FETCH l_is_mobile_sr_csr INTO l_is_mobile_sr;
1569 CLOSE l_is_mobile_sr_csr;
1570
1571 IF (NVL(l_is_mobile_sr,0) <> 1) THEN
1572 RETURN;
1573 END IF;
1574
1575
1576 IF l_sr_post_upd_csr%NOTFOUND THEN
1577 CLOSE l_sr_post_upd_csr;
1578 RETURN;
1579 ELSE
1580 IF l_sr_post_upd_rec.INSTALL_SITE_ID IS NULL THEN
1581 OPEN l_addr_id_csr(l_incident_id);
1582 FETCH l_addr_id_csr INTO l_sr_post_upd_rec.INSTALL_SITE_ID;
1583 CLOSE l_addr_id_csr;
1584 END IF;
1585
1586 IF l_sr_post_upd_rec.INCIDENT_LOCATION_ID IS NULL THEN
1587 OPEN l_location_id_csr(l_incident_id);
1588 FETCH l_location_id_csr INTO l_sr_post_upd_rec.INCIDENT_LOCATION_ID,l_location_id;
1589 CLOSE l_location_id_csr;
1590 IF l_location_id IS NOT NULL THEN
1591 l_sr_post_upd_rec.INCIDENT_LOCATION_ID := l_location_id;
1592 END IF;
1593
1594 END IF;
1595
1596 IF nvl(g_sr_pre_upd_rec.install_site_id, -1) <> nvl(l_sr_post_upd_rec.install_site_id,-1) THEN
1597 l_is_install_site_updated := 'Y';
1598 END IF;
1599
1600 IF nvl(g_sr_pre_upd_rec.incident_location_id, -1) <> nvl(l_sr_post_upd_rec.incident_location_id,-1) THEN
1601 l_is_incident_location_updated := 'Y';
1602 END IF;
1603
1604 IF nvl(g_sr_pre_upd_rec.customer_id, -1) <> nvl(l_sr_post_upd_rec.customer_id,-1) THEN
1605 l_is_sr_customer_updated := 'Y';
1606 END IF;
1607
1608 IF (nvl(g_sr_pre_upd_rec.inventory_item_id, -1) <> nvl(l_sr_post_upd_rec.inventory_item_id,-1))
1609 OR (nvl(g_sr_pre_upd_rec.inv_organization_id, -1) <> nvl(l_sr_post_upd_rec.inv_organization_id,-1)) THEN
1610 l_is_inventory_item_updated := 'Y';
1611 END IF;
1612
1613 IF nvl(g_sr_pre_upd_rec.customer_product_id,-1) <> nvl(l_sr_post_upd_rec.customer_product_id,-1) THEN
1614 l_is_sr_instance_updated := 'Y';
1615 END IF;
1616
1617 IF nvl(g_sr_pre_upd_rec.contract_service_id,-1) <> nvl(l_sr_post_upd_rec.contract_service_id,-1) THEN
1618 l_is_contr_service_id_updated := 'Y';
1619 END IF;
1620
1621 -- call the sr upd
1622 -- pass old_install_site_id though it is not used in the called procedure
1623 csm_sr_event_pkg.sr_upd_init(p_incident_id=> l_incident_id,
1624 p_is_incident_location_updated=>l_is_incident_location_updated,
1625 p_old_incident_location_id=> g_sr_pre_upd_rec.incident_location_id,
1626 p_is_install_site_updated=>l_is_install_site_updated,
1627 p_old_install_site_id=>g_sr_pre_upd_rec.install_site_id,
1628 p_is_sr_customer_updated=>l_is_sr_customer_updated,
1629 p_old_sr_customer_id=>g_sr_pre_upd_rec.customer_id,
1630 p_is_sr_instance_updated=>l_is_sr_instance_updated,
1631 p_old_instance_id=>g_sr_pre_upd_rec.customer_product_id,
1632 p_is_inventory_item_updated=>l_is_inventory_item_updated,
1633 p_old_inventory_item_id=>g_sr_pre_upd_rec.inventory_item_id,
1634 p_old_organization_id=>g_sr_pre_upd_rec.inv_organization_id,
1635 p_old_party_id=>g_sr_pre_upd_rec.party_id,
1636 p_old_location_id=>g_sr_pre_upd_rec.location_id,
1637 p_is_contr_service_id_updated=>l_is_contr_service_id_updated,
1638 p_old_contr_service_id=>g_sr_pre_upd_rec.contract_service_id);
1639 END IF;
1640 CLOSE l_sr_post_upd_csr;
1641
1642 -- nullify the global contact rec
1643 g_sr_cont_points_post_upd_tbl := g_null_sr_cont_points_tbl;
1644 l_rec_count := 0;
1645
1646 -- check for contacts inserted, updated or deleted
1647 OPEN l_sr_cont_pts_post_upd_csr(l_incident_id);
1648 LOOP
1649 FETCH l_sr_cont_pts_post_upd_csr INTO l_sr_cont_pts_post_upd_rec;
1650 IF l_sr_cont_pts_post_upd_csr%NOTFOUND THEN
1651 EXIT;
1652 ELSE
1653 l_rec_count := l_rec_count + 1;
1654 g_sr_cont_points_post_upd_tbl(l_rec_count).SR_CONTACT_POINT_ID := l_sr_cont_pts_post_upd_rec.sr_contact_point_id;
1655 g_sr_cont_points_post_upd_tbl(l_rec_count).PARTY_ID := l_sr_cont_pts_post_upd_rec.party_id;
1656 g_sr_cont_points_post_upd_tbl(l_rec_count).contact_point_id := l_sr_cont_pts_post_upd_rec.contact_point_id;
1657 g_sr_cont_points_post_upd_tbl(l_rec_count).primary_flag := l_sr_cont_pts_post_upd_rec.primary_flag;
1658 g_sr_cont_points_post_upd_tbl(l_rec_count).contact_point_type := l_sr_cont_pts_post_upd_rec.contact_point_type;
1659 g_sr_cont_points_post_upd_tbl(l_rec_count).CONTACT_TYPE := l_sr_cont_pts_post_upd_rec.contact_type;
1660 g_sr_cont_points_post_upd_tbl(l_rec_count).last_update_date := l_sr_cont_pts_post_upd_rec.last_update_date;
1661 g_sr_cont_points_post_upd_tbl(l_rec_count).creation_date := l_sr_cont_pts_post_upd_rec.creation_date;
1662 END IF;
1663 END LOOP;
1664 CLOSE l_sr_cont_pts_post_upd_csr;
1665
1666 -- initialize
1667 l_is_sr_contact_ins := 'Y';
1668 l_is_sr_contact_upd := 'N';
1669 l_is_sr_contact_del := 'Y';
1670
1671 <<postupd>>
1672 FOR post IN 1..g_sr_cont_points_post_upd_tbl.count LOOP
1673 l_is_sr_contact_ins := 'Y';
1674 l_is_sr_contact_upd := 'N';
1675 <<preupd>>
1676 FOR pre IN 1..g_sr_cont_points_pre_upd_tbl.count LOOP
1677 IF g_sr_cont_points_post_upd_tbl(post).sr_contact_point_id = g_sr_cont_points_pre_upd_tbl(pre).sr_contact_point_id THEN
1678 -- record exists; set insert_flag to N
1679 l_is_sr_contact_ins := 'N';
1680
1681 -- check if record is updated
1682 IF g_sr_cont_points_post_upd_tbl(post).last_update_date > g_sr_cont_points_pre_upd_tbl(pre).last_update_date
1683 AND g_sr_cont_points_post_upd_tbl(post).creation_date = g_sr_cont_points_pre_upd_tbl(pre).creation_date THEN
1684 -- record has been updated
1685 -- SR_Contact_Pre_Upd(g_sr_cont_points_pre_upd_tbl(pre).sr_contact_point_id);
1686 csm_sr_contact_event_pkg.sr_cntact_mdirty_u_foreachuser
1687 (p_sr_contact_point_id=>g_sr_cont_points_pre_upd_tbl(pre).sr_contact_point_id);
1688 END IF;
1689 END IF;
1690 END LOOP; -- preupd
1691
1692 -- if record does not exist insert the new SR
1693 IF l_is_sr_contact_ins = 'Y' THEN
1694 csm_sr_contact_event_pkg.spawn_userloop_sr_contact_ins
1695 (p_sr_contact_point_id=>g_sr_cont_points_post_upd_tbl(post).sr_contact_point_id);
1696 -- SR_Contact_Post_Ins(g_sr_cont_points_post_upd_tbl(post).sr_contact_point_id);
1697 END IF;
1698 END LOOP; -- postupd
1699
1700 -- check for sr contact point deletes
1701 <<predel>>
1702 FOR pre IN 1..g_sr_cont_points_pre_upd_tbl.count LOOP
1703 l_is_sr_contact_del := 'Y';
1704 FOR post IN 1..g_sr_cont_points_post_upd_tbl.count LOOP
1705 IF g_sr_cont_points_post_upd_tbl(post).sr_contact_point_id = g_sr_cont_points_pre_upd_tbl(pre).sr_contact_point_id THEN
1706 l_is_sr_contact_del := 'N';
1707 END IF;
1708 END LOOP;
1709
1710 -- delete sr contact if not found
1711 IF l_is_sr_contact_del = 'Y' THEN
1712 csm_sr_contact_event_pkg.spawn_userloop_sr_contact_del
1713 (p_sr_contact_point_id=>g_sr_cont_points_pre_upd_tbl(pre).sr_contact_point_id);
1714 END IF;
1715 END LOOP;
1716
1717 /*call Start Sync event - NOTIFY CLIENT*/
1718 RAISE_START_AUTO_SYNC_EVENT('CSM_INCIDENTS_ALL',to_char(l_incident_id),'UPDATE');
1719
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 x_return_status := FND_API.G_RET_STS_ERROR;
1723 l_sqlerrno := to_char(SQLCODE);
1724 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1725 l_error_msg := 'Exception in sr_post_upd for incident_id:' || TO_CHAR(l_incident_id)
1726 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1727 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.sr_post_upd',FND_LOG.LEVEL_EXCEPTION);
1728 END sr_post_upd;
1729
1730 /*--------------------------------------------------
1731 Description:
1732 Starts the RS_GROUP_MEMBER_INS_USERLOOP workflow. Should be called when new
1733 group member is created.
1734 Invoked by JTM_RS_GROUP_MEMBER_VUHK.create_group_members_post
1735 Parameter(s):
1736 p_group_member_id,
1737 p_group_id,
1738 p_resource_id,
1739 x_return_status
1740 ----------------------------------------------------*/
1741
1742 PROCEDURE JTF_RS_Group_Member_Post_Ins(p_group_member_id IN jtf_rs_group_members.group_member_id%TYPE,
1743 p_group_id IN jtf_rs_group_members.group_id%TYPE,
1744 p_resource_id IN jtf_rs_group_members.resource_id%TYPE,
1745 x_return_status OUT nocopy VARCHAR2)
1746 IS
1747 l_sqlerrno VARCHAR2(20);
1748 l_sqlerrmsg VARCHAR2(2000);
1749
1750 BEGIN
1751 x_return_status := FND_API.G_RET_STS_SUCCESS;
1752
1753 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1754 RETURN;
1755 END IF;
1756
1757 csm_resource_extns_event_pkg.rs_group_members_ins_init(p_resource_id=>p_resource_id,
1758 p_group_id=>p_group_id);
1759 CSM_PO_LOCATIONS_EVENT_PKG.GRP_ADDRESS_I(p_group_id,p_resource_id);
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 x_return_status := FND_API.G_RET_STS_ERROR;
1763 l_sqlerrno := to_char(SQLCODE);
1764 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1765 CSM_UTIL_PKG.LOG('Exception in JTF_RS_Group_Member_Post_Ins for resource_id: ' || TO_CHAR(p_resource_id)
1766 || ' and group_id: ' || TO_CHAR(p_group_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg,
1767 'CSM_WF_PKG.JTF_RS_Group_Member_Post_Ins', FND_LOG.LEVEL_EXCEPTION);
1768 END JTF_RS_Group_Member_Post_Ins;
1769
1770 Procedure JTF_RS_Group_Member_Pre_Upd(p_user_id in number,
1771 p_jtf_rs_group_memb jtf_rs_group_members%rowtype)
1772 IS
1773 BEGIN
1774 null;
1775 END;
1776 /*
1777 Procedure JTF_RS_Group_Member_Post_Del(p_user_id in number,
1778 p_jtf_rs_group_memb jtf_rs_group_members%rowtype)
1779 IS
1780 BEGIN
1781 null;
1782 END;
1783 */
1784 /*--------------------------------------------------
1785 Description:
1786 Starts the RS_GROUP_MEMBER_DEL_USERLOOP workflow. Should be called when new
1787 group resource member is deleted.
1788 Invoked by JTM_RS_GROUP_MEMBER_VUHK.delete_group_members_pre
1789 Parameter(s):
1790 p_group_id,
1791 p_resource_id,
1792 x_return_status
1793 ----------------------------------------------------*/
1794
1795 PROCEDURE JTF_RS_Group_Member_Pre_Del(p_group_id IN jtf_rs_group_members.group_id%TYPE,
1796 p_resource_id IN jtf_rs_group_members.resource_id%TYPE,
1797 x_return_status OUT nocopy VARCHAR2)
1798 IS
1799 l_sqlerrno VARCHAR2(20);
1800 l_sqlerrmsg VARCHAR2(2000);
1801
1802 BEGIN
1803
1804 x_return_status := FND_API.G_RET_STS_SUCCESS;
1805
1806 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1807 RETURN;
1808 END IF;
1809
1810 csm_resource_extns_event_pkg.rs_group_members_del_init(p_resource_id=>p_resource_id,
1811 p_group_id=>p_group_id);
1812 CSM_PO_LOCATIONS_EVENT_PKG.GRP_ADDRESS_D(p_group_id,p_resource_id);
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 x_return_status := FND_API.G_RET_STS_ERROR;
1816 l_sqlerrno := to_char(SQLCODE);
1817 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1818 CSM_UTIL_PKG.LOG('Exception in JTF_RS_Group_Member_Pre_Del for resource_id: ' || TO_CHAR(p_resource_id)
1819 || ' and group_id: ' || TO_CHAR(p_group_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg,
1820 'CSM_WF_PKG.JTF_RS_Group_Member_Pre_Del', FND_LOG.LEVEL_EXCEPTION);
1821 END JTF_RS_Group_Member_Pre_Del;
1822
1823 /********************************************************
1824 Starts the DEBRIEF_HEADER_INS workflow. Should be called when new
1825 debrief line is added on the backend
1826
1827 Arguments:
1828 DEBRIEF_HEADER_ID of the new debrief line
1829 *********************************************************/
1830
1831 PROCEDURE CSF_Debrief_Header_Post_Ins(x_return_status OUT NOCOPY VARCHAR2)
1832 IS
1833 l_debrief_header_id csf_debrief_lines.debrief_header_id%TYPE;
1834 l_sqlerrno VARCHAR2(20);
1835 l_sqlerrmsg VARCHAR2(4000);
1836 l_error_msg VARCHAR2(4000);
1837 l_return_status VARCHAR2(2000);
1838
1839 BEGIN
1840 l_debrief_header_id := CSF_DEBRIEF_HEADERS_PKG.user_hooks_rec.debrief_header_id;
1841
1842 x_return_status := FND_API.G_RET_STS_SUCCESS;
1843
1844 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1845 RETURN;
1846 END IF;
1847
1848 csm_debrief_header_event_pkg.debrief_header_ins_init(p_debrief_header_id=>l_debrief_header_id,
1849 p_h_user_id=>NULL,
1850 p_flow_type=>NULL);
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 x_return_status := FND_API.G_RET_STS_ERROR;
1854 l_sqlerrno := to_char(SQLCODE);
1855 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1856 l_error_msg := 'Exception in CSF_Debrief_Header_Post_Ins for debrief_header_id:' || TO_CHAR(l_debrief_header_id)
1857 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1858 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Header_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
1859 END CSF_Debrief_Header_Post_Ins;
1860
1861 PROCEDURE CSF_Debrief_Header_Pre_Upd(x_return_status OUT NOCOPY VARCHAR2)
1862 IS
1863 BEGIN
1864 x_return_status := FND_API.G_RET_STS_SUCCESS;
1865 END CSF_Debrief_Header_Pre_Upd;
1866
1867 PROCEDURE CSF_Debrief_Header_Post_Upd(x_return_status OUT NOCOPY VARCHAR2)
1868 IS
1869 l_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE;
1870 l_sqlerrno VARCHAR2(20);
1871 l_sqlerrmsg VARCHAR2(4000);
1872 l_error_msg VARCHAR2(4000);
1873 l_return_status VARCHAR2(2000);
1874
1875 CURSOR l_debrief_hdr_csr(p_debrief_header_id IN NUMBER )
1876 IS
1877 SELECT acc.debrief_header_id,
1878 acc.user_id
1879 FROM csm_debrief_headers_acc acc
1880 WHERE acc.debrief_header_id = p_debrief_header_id;
1881
1882 BEGIN
1883 x_return_status := FND_API.G_RET_STS_SUCCESS;
1884
1885 l_debrief_header_id := CSF_DEBRIEF_Headers_PKG.user_hooks_rec.debrief_header_id;
1886
1887 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1888 RETURN;
1889 END IF;
1890
1891 CSM_UTIL_PKG.LOG('CSM_WF_PKG.CSF_Debrief_Header_Post_Upd: IS_FIELD_SERVICE_PALM_ENABLED' );
1892
1893 FOR r_debrief_hdr_rec IN l_debrief_hdr_csr(l_debrief_header_id) LOOP
1894 csm_debrief_header_event_pkg.debrief_header_mdirty_u(p_debrief_header_id=>l_debrief_header_id,
1895 p_user_id=>r_debrief_hdr_rec.user_id);
1896 END LOOP;
1897
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900 x_return_status := FND_API.G_RET_STS_ERROR;
1901 l_sqlerrno := to_char(SQLCODE);
1902 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1903 l_error_msg := 'Exception in CSF_Debrief_Header_Post_Upd for debrief_header_id:' || TO_CHAR(l_debrief_header_id)
1904 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1905 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Header_Post_Upd',FND_LOG.LEVEL_EXCEPTION);
1906 END CSF_Debrief_Header_Post_Upd;
1907
1908 PROCEDURE CSF_Debrief_Header_Post_Del(x_return_status OUT NOCOPY VARCHAR2)
1909 IS
1910 l_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE;
1911 l_task_assignment_id csf_debrief_headers.task_assignment_id%type;
1912 l_user_id fnd_user.user_id%type;
1913 l_resource_id jtf_rs_resource_extns.resource_id%type;
1914
1915 CURSOR l_csm_debrfdel_csr (p_debrief_header_id csf_debrief_headers.debrief_header_id%type) IS
1916 SELECT dhdr.task_assignment_id, jtrs.user_id, jta.resource_id
1917 FROM csf_debrief_headers dhdr,
1918 jtf_task_assignments jta,
1919 jtf_rs_resource_extns jtrs
1920 WHERE dhdr.debrief_header_id = p_debrief_header_id
1921 AND jta.task_assignment_id = dhdr.task_assignment_id
1922 AND jtrs.resource_id = jta.resource_id
1923 ;
1924
1925 l_sqlerrno VARCHAR2(20);
1926 l_sqlerrmsg VARCHAR2(4000);
1927 l_error_msg VARCHAR2(4000);
1928 l_return_status VARCHAR2(2000);
1929
1930 CURSOR l_csm_debrfHdDel_csr (p_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE)
1931 IS
1932 SELECT dhdr.debrief_header_id, acc.user_id
1933 FROM csf_debrief_headers dhdr,
1934 csm_debrief_headers_acc acc
1935 WHERE dhdr.debrief_header_id = p_debrief_header_id
1936 AND acc.debrief_header_id = dhdr.debrief_header_id;
1937
1938 l_csm_debrfHdDel_rec l_csm_debrfHdDel_csr%ROWTYPE;
1939
1940 BEGIN
1941 x_return_status := FND_API.G_RET_STS_SUCCESS;
1942
1943 l_debrief_header_id := CSF_DEBRIEF_HEADERS_PKG.user_hooks_rec.debrief_header_id;
1944
1945 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1946 RETURN;
1947 END IF;
1948
1949 OPEN l_csm_debrfHdDel_csr(l_debrief_header_id);
1950 FETCH l_csm_debrfHdDel_csr INTO l_csm_debrfHdDel_rec;
1951 IF l_csm_debrfHdDel_csr%NOTFOUND THEN
1952 CLOSE l_csm_debrfHdDel_csr;
1953 RETURN;
1954 END IF;
1955 CLOSE l_csm_debrfHdDel_csr;
1956
1957 csm_debrief_header_event_pkg.debrief_header_del_init(p_debrief_header_id=>l_debrief_header_id,
1958 p_user_id=>l_csm_debrfHdDel_rec.user_id,
1959 p_flow_type=>NULL);
1960 EXCEPTION
1961 WHEN OTHERS THEN
1962 x_return_status := FND_API.G_RET_STS_ERROR;
1963 l_sqlerrno := to_char(SQLCODE);
1964 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1965 l_error_msg := 'Exception in CSF_Debrief_Header_Post_Del for debrief_header_id:' || TO_CHAR(l_debrief_header_id)
1966 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1967 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Header_Post_Del',FND_LOG.LEVEL_EXCEPTION);
1968 END CSF_Debrief_Header_Post_Del;
1969
1970 /********************************************************
1971 Starts the DEBRIEF_LINE_INS workflow. Should be called when new
1972 debrief line is added on the backend
1973
1974 Arguments:
1975 p_task_assignment_id: DEBRIEF_LINE_ID of the new debrief line
1976 *********************************************************/
1977 PROCEDURE CSF_Debrief_Line_Post_Ins (x_return_status OUT NOCOPY VARCHAR2)
1978 IS
1979 l_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE;
1980 l_sqlerrno VARCHAR2(20);
1981 l_sqlerrmsg VARCHAR2(4000);
1982 l_error_msg VARCHAR2(4000);
1983 l_return_status VARCHAR2(2000);
1984
1985 BEGIN
1986 x_return_status := FND_API.G_RET_STS_SUCCESS;
1987
1988 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
1989
1990 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
1991 RETURN;
1992 END IF;
1993
1994 csm_debrief_event_pkg.debrief_line_ins_init(p_debrief_line_id=>l_debrief_line_id,
1995 p_h_user_id=>NULL,
1996 p_flow_type=>NULL);
1997
1998 EXCEPTION
1999 WHEN OTHERS THEN
2000 x_return_status := FND_API.G_RET_STS_ERROR;
2001 l_sqlerrno := to_char(SQLCODE);
2002 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2003 l_error_msg := 'Exception in CSF_Debrief_Line_Post_Ins for debrief_Line_id:' || TO_CHAR(l_debrief_line_id)
2004 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
2005 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Line_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
2006 END CSF_Debrief_Line_Post_Ins;
2007
2008 /********************************************************
2009 Captures the old inventory_item_id before the upd
2010 Arguments:
2011 p_task_assignment_id: DEBRIEF_LINE_ID of the debrief line
2012 *********************************************************/
2013 Procedure CSF_Debrief_Line_Pre_Upd(x_return_status OUT NOCOPY VARCHAR2)
2014 IS
2015 l_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE;
2016 l_sqlerrno VARCHAR2(20);
2017 l_sqlerrmsg VARCHAR2(4000);
2018 l_error_msg VARCHAR2(4000);
2019 l_return_status VARCHAR2(2000);
2020
2021 CURSOR l_debrief_line_pre_upd (p_debrief_line_id csf_debrief_lines.debrief_line_id%type)
2022 IS
2023 SELECT debrief_line_id,
2024 inventory_item_id,
2025 instance_id
2026 FROM csf_debrief_lines
2027 WHERE debrief_line_id = p_debrief_line_id;
2028
2029 BEGIN
2030 x_return_status := FND_API.G_RET_STS_SUCCESS;
2031
2032 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2033 RETURN;
2034 END IF;
2035
2036 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
2037
2038 OPEN l_debrief_line_pre_upd(l_debrief_line_id);
2039 FETCH l_debrief_line_pre_upd INTO g_debrief_line_pre_upd_rec;
2040 IF l_debrief_line_pre_upd%NOTFOUND THEN
2041 CLOSE l_debrief_line_pre_upd;
2042 RETURN;
2043 END IF;
2044 CLOSE l_debrief_line_pre_upd;
2045
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 x_return_status := FND_API.G_RET_STS_ERROR;
2049 l_sqlerrno := to_char(SQLCODE);
2050 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2051 l_error_msg := 'Exception in CSF_Debrief_Line_Pre_Upd for debrief_Line_id:' || TO_CHAR(l_debrief_line_id)
2052 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
2053 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Line_Pre_Upd',FND_LOG.LEVEL_EXCEPTION);
2054 END CSF_Debrief_Line_Pre_Upd;
2055
2056
2057 Procedure CSF_Debrief_Line_Post_Upd(x_return_status OUT NOCOPY VARCHAR2)
2058 IS
2059 l_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE;
2060 l_is_inventory_item_updated varchar2(1);
2061 l_is_debrief_instance_updated varchar2(1);
2062 l_sqlerrno VARCHAR2(20);
2063 l_sqlerrmsg VARCHAR2(4000);
2064 l_error_msg VARCHAR2(4000);
2065 l_return_status VARCHAR2(2000);
2066
2067 CURSOR l_debrief_line_post_upd (p_debrief_line_id csf_debrief_lines.debrief_line_id%type)
2068 IS
2069 SELECT debrief_line_id,
2070 inventory_item_id,
2071 instance_id
2072 FROM csf_debrief_lines
2073 WHERE debrief_line_id = p_debrief_line_id;
2074
2075 r_debrief_line_post_upd_rec l_debrief_line_post_upd%ROWTYPE;
2076
2077 BEGIN
2078 x_return_status := FND_API.G_RET_STS_SUCCESS;
2079
2080 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
2081
2082 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2083 RETURN;
2084 END IF;
2085
2086 l_is_inventory_item_updated := 'N';
2087 l_is_debrief_instance_updated := 'N';
2088
2089 OPEN l_debrief_line_post_upd(l_debrief_line_id);
2090 FETCH l_debrief_line_post_upd INTO r_debrief_line_post_upd_rec;
2091 IF l_debrief_line_post_upd%NOTFOUND THEN
2092 CLOSE l_debrief_line_post_upd;
2093 RETURN;
2094 END IF;
2095 CLOSE l_debrief_line_post_upd;
2096
2097 -- compare the inventory_item_id with the value in pre_upd
2098 IF NVL(r_debrief_line_post_upd_rec.inventory_item_id, -1) <> NVL(g_debrief_line_pre_upd_rec.inventory_item_id, -1) THEN
2099 l_is_inventory_item_updated := 'Y';
2100 END IF;
2101
2102 IF NVL(r_debrief_line_post_upd_rec.instance_id, -1) <> NVL(g_debrief_line_pre_upd_rec.instance_id, -1) THEN
2103 l_is_debrief_instance_updated := 'Y';
2104 END IF;
2105
2106 csm_debrief_event_pkg.debrief_line_upd_init
2107 (p_debrief_line_id=>l_debrief_line_id,
2108 p_old_inventory_item_id=>g_debrief_line_pre_upd_rec.inventory_item_id,
2109 p_is_inventory_item_updated=>l_is_inventory_item_updated,
2110 p_old_instance_id=>g_debrief_line_pre_upd_rec.instance_id,
2111 p_is_instance_updated=>l_is_debrief_instance_updated);
2112
2113
2114 EXCEPTION
2115 WHEN OTHERS THEN
2116 x_return_status := FND_API.G_RET_STS_ERROR;
2117 l_sqlerrno := to_char(SQLCODE);
2118 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2119 l_error_msg := 'Exception in CSF_Debrief_Line_Post_Upd for debrief_Line_id:' || TO_CHAR(l_debrief_line_id)
2120 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
2121 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Line_Post_Upd',FND_LOG.LEVEL_EXCEPTION);
2122 END CSF_Debrief_Line_Post_Upd;
2123
2124
2125 /*
2126 * Post Delete of CS_DEBRIEF_LINES records.
2127 */
2128 Procedure CSF_Debrief_Line_Post_Del (x_return_status OUT NOCOPY VARCHAR2)
2129 IS
2130 l_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE;
2131 l_sqlerrno VARCHAR2(20);
2132 l_sqlerrmsg VARCHAR2(4000);
2133 l_error_msg VARCHAR2(4000);
2134 l_return_status VARCHAR2(2000);
2135
2136 CURSOR l_csm_debrfLnDel_csr (p_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE)
2137 IS
2138 SELECT acc.user_id, dbl.debrief_line_id
2139 FROM csf_debrief_lines dbl,
2140 csm_debrief_lines_acc acc
2141 WHERE dbl.debrief_line_id = p_debrief_line_id
2142 AND acc.debrief_line_id = dbl.debrief_line_id;
2143
2144 l_csm_debrfLnDel_rec l_csm_debrfLnDel_csr%ROWTYPE;
2145
2146 BEGIN
2147 x_return_status := FND_API.G_RET_STS_SUCCESS;
2148
2149 l_debrief_line_id := CSF_DEBRIEF_LINES_PKG.user_hooks_rec.debrief_line_id;
2150
2151 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2152 RETURN;
2153 END IF;
2154
2155 OPEN l_csm_debrfLnDel_csr(l_debrief_line_id);
2156 FETCH l_csm_debrfLnDel_csr INTO l_csm_debrfLnDel_rec;
2157 IF l_csm_debrfLnDel_csr%NOTFOUND THEN
2158 CLOSE l_csm_debrfLnDel_csr;
2159 RETURN;
2160 END IF;
2161 CLOSE l_csm_debrfLnDel_csr;
2162
2163 csm_debrief_event_pkg.debrief_line_del_init(p_debrief_line_id=>l_debrief_line_id,
2164 p_user_id=>l_csm_debrfLnDel_rec.user_id,
2165 p_flow_type=>NULL);
2166 EXCEPTION
2167 WHEN OTHERS THEN
2168 x_return_status := FND_API.G_RET_STS_ERROR;
2169 l_sqlerrno := to_char(SQLCODE);
2170 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2171 l_error_msg := 'Exception in CSF_Debrief_Line_Post_Del for debrief_Line_id:' || TO_CHAR(l_debrief_line_id)
2172 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
2173 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSF_Debrief_Line_Post_Del',FND_LOG.LEVEL_EXCEPTION);
2174 END CSF_Debrief_Line_Post_Del;
2175
2176 PROCEDURE CSP_Inv_Loc_Assignmnt_Post_Ins(x_return_status OUT NOCOPY VARCHAR2)
2177 IS
2178 l_csp_inv_loc_assignment_id csp_inv_loc_assignments.csp_inv_loc_assignment_id%TYPE;
2179 l_sqlerrno VARCHAR2(20);
2180 l_sqlerrmsg VARCHAR2(2000);
2181
2182 BEGIN
2183 x_return_status := FND_API.G_RET_STS_SUCCESS;
2184
2185 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2186 RETURN;
2187 END IF;
2188
2189 l_csp_inv_loc_assignment_id := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
2190
2191 CSM_UTIL_PKG.LOG('Entering CSP_Inv_Loc_Assignmnt_Post_Ins for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id),
2192 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Post_Ins', FND_LOG.LEVEL_PROCEDURE );
2193
2194 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT(p_csp_inv_loc_assignment_id=>l_csp_inv_loc_assignment_id);
2195
2196 CSM_UTIL_PKG.LOG('Leaving CSP_Inv_Loc_Assignmnt_Post_Ins for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id),
2197 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Post_Ins', FND_LOG.LEVEL_PROCEDURE );
2198
2199 EXCEPTION
2200 WHEN OTHERS THEN
2201 x_return_status := FND_API.G_RET_STS_ERROR;
2202 l_sqlerrno := to_char(SQLCODE);
2203 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2204 CSM_UTIL_PKG.LOG('Exception in CSP_Inv_Loc_Assignmnt_Post_Ins for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id)
2205 || ':' || l_sqlerrno || ':' || l_sqlerrmsg,
2206 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Post_Ins', FND_LOG.LEVEL_EXCEPTION);
2207 END CSP_Inv_Loc_Assignmnt_Post_Ins;
2208
2209 PROCEDURE CSP_Inv_Loc_Assignmnt_Pre_Upd(x_return_status OUT NOCOPY VARCHAR2)
2210 IS
2211 l_csp_inv_loc_assignment_id csp_inv_loc_assignments.csp_inv_loc_assignment_id%TYPE;
2212 l_old_organization_id csp_inv_loc_assignments.organization_id%TYPE;
2213 l_old_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
2214 l_old_eff_date_start date;
2215 l_old_eff_date_end date;
2216 l_old_default_code csp_inv_loc_assignments.default_code%TYPE;
2217 l_sqlerrno VARCHAR2(20);
2218 l_sqlerrmsg VARCHAR2(2000);
2219
2220 CURSOR l_cila_pre_upd_csr(p_csp_inv_loc_assignment_id IN NUMBER)
2221 IS
2222 SELECT organization_id,
2223 subinventory_code,
2224 effective_date_start,
2225 effective_date_end,
2226 default_code
2227 FROM csp_inv_loc_assignments cila,
2228 asg_user au
2229 WHERE cila.csp_inv_loc_assignment_id = p_csp_inv_loc_assignment_id
2230 AND au.resource_id = cila.resource_id;
2231
2232 BEGIN
2233 x_return_status := FND_API.G_RET_STS_SUCCESS;
2234
2235 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2236 RETURN;
2237 END IF;
2238
2239 l_csp_inv_loc_assignment_id := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
2240
2241 CSM_UTIL_PKG.LOG('Entering CSP_Inv_Loc_Assignmnt_Pre_Upd for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id),
2242 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Pre_Upd', FND_LOG.LEVEL_PROCEDURE );
2243
2244 g_old_subinventory_code := NULL;
2245 g_old_organization_id := NULL;
2246 g_old_eff_date_start := NULL;
2247 g_old_eff_date_end := NULL;
2248 g_old_default_code := NULL;
2249
2250 OPEN l_cila_pre_upd_csr(l_csp_inv_loc_assignment_id);
2251 FETCH l_cila_pre_upd_csr INTO l_old_organization_id, l_old_subinventory_code,
2252 l_old_eff_date_start, l_old_eff_date_end, l_old_default_code;
2253
2254 IF l_cila_pre_upd_csr%NOTFOUND THEN
2255 CLOSE l_cila_pre_upd_csr;
2256 RETURN;
2257 ELSE
2258 g_old_organization_id := l_old_organization_id;
2259 g_old_subinventory_code := l_old_subinventory_code;
2260 g_old_eff_date_start := l_old_eff_date_start;
2261 g_old_eff_date_end := l_old_eff_date_end;
2262 g_old_default_code := l_old_default_code;
2263 END IF;
2264 CLOSE l_cila_pre_upd_csr;
2265
2266 CSM_UTIL_PKG.LOG('Leaving CSP_Inv_Loc_Assignmnt_Pre_Upd for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id),
2267 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Pre_Upd', FND_LOG.LEVEL_PROCEDURE );
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 x_return_status := FND_API.G_RET_STS_ERROR;
2271 l_sqlerrno := to_char(SQLCODE);
2272 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2273 CSM_UTIL_PKG.LOG('Exception in CSP_Inv_Loc_Assignmnt_Pre_Upd for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id)
2274 || ':' || l_sqlerrno || ':' || l_sqlerrmsg,
2275 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Pre_Upd', FND_LOG.LEVEL_EXCEPTION);
2276 END CSP_Inv_Loc_Assignmnt_Pre_Upd;
2277
2278 PROCEDURE CSP_Inv_Loc_Assignmnt_Post_Upd(x_return_status OUT NOCOPY VARCHAR2)
2279 IS
2280 l_csp_inv_loc_assignment_id csp_inv_loc_assignments.csp_inv_loc_assignment_id%TYPE;
2281 l_organization_id csp_inv_loc_assignments.organization_id%TYPE;
2282 l_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
2283 l_eff_date_start DATE;
2284 l_eff_date_end DATE;
2285 l_user_id fnd_user.user_id%TYPE;
2286 l_resource_id csp_inv_loc_assignments.resource_id%TYPE;
2287 l_default_code csp_inv_loc_assignments.default_code%TYPE;
2288 l_sqlerrno VARCHAR2(20);
2289 l_sqlerrmsg VARCHAR2(2000);
2290
2291 CURSOR l_cila_post_upd_csr(p_csp_inv_loc_assignment_id IN NUMBER)
2292 IS
2293 SELECT cila.organization_id,
2294 cila.subinventory_code,
2295 cila.effective_date_start,
2296 cila.effective_date_end,
2297 cila.resource_id,
2298 au.user_id,
2299 cila.default_code
2300 FROM csp_inv_loc_assignments cila,
2301 asg_user au
2302 WHERE cila.csp_inv_loc_assignment_id = p_csp_inv_loc_assignment_id
2303 AND au.resource_id = cila.resource_id;
2304
2305 BEGIN
2306 x_return_status := FND_API.G_RET_STS_SUCCESS;
2307
2308 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2309 RETURN;
2310 END IF;
2311
2312 l_csp_inv_loc_assignment_id := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
2313
2314 OPEN l_cila_post_upd_csr(l_csp_inv_loc_assignment_id);
2315 FETCH l_cila_post_upd_csr INTO l_organization_id, l_subinventory_code, l_eff_date_start,
2316 l_eff_date_end, l_resource_id, l_user_id, l_default_code;
2317 IF l_cila_post_upd_csr%NOTFOUND THEN
2318 CLOSE l_cila_post_upd_csr;
2319 RETURN;
2320 END IF;
2321 CLOSE l_cila_post_upd_csr;
2322
2323 -- spawn the del process if sysdate not between eff start date and eff end date
2324 IF ((SYSDATE NOT BETWEEN nvl(l_eff_date_start,sysdate) AND nvl(l_eff_date_end, sysdate)) AND
2325 (SYSDATE BETWEEN nvl(g_old_eff_date_start,sysdate) AND nvl(g_old_eff_date_end, sysdate) ) AND
2326 (l_organization_id = g_old_organization_id AND l_subinventory_code = g_old_subinventory_code)) THEN
2327
2328 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT(p_csp_inv_loc_assignment_id=>l_csp_inv_loc_assignment_id);
2329
2330 -- spawn the ins process if sysdate between eff start date and eff end date
2331 ELSIF ((SYSDATE NOT BETWEEN nvl(g_old_eff_date_start,sysdate) AND nvl(g_old_eff_date_end, sysdate)) AND
2332 (SYSDATE BETWEEN nvl(l_eff_date_start,sysdate) AND nvl(l_eff_date_end, sysdate) ) AND
2333 (l_organization_id = g_old_organization_id AND l_subinventory_code = g_old_subinventory_code)) THEN
2334
2335 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT(p_csp_inv_loc_assignment_id=>l_csp_inv_loc_assignment_id);
2336
2337 ELSE -- update pub item if org/subinventory/default code is updated
2338 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT(p_csp_inv_loc_assignment_id=>l_csp_inv_loc_assignment_id);
2339 END IF;
2340
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 x_return_status := FND_API.G_RET_STS_ERROR;
2344 l_sqlerrno := to_char(SQLCODE);
2345 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2346 CSM_UTIL_PKG.LOG('Exception in CSP_Inv_Loc_Assignmnt_Post_Upd for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id)
2347 || ':' || l_sqlerrno || ':' || l_sqlerrmsg,
2348 'CSM_WF_PKG.CSP_Inv_Loc_Assignmnt_Post_Upd', FND_LOG.LEVEL_EXCEPTION);
2349 END CSP_Inv_Loc_Assignmnt_Post_Upd;
2350
2351 PROCEDURE CSP_Inv_Loc_Assg_Post_Del(x_return_status OUT NOCOPY VARCHAR2)
2352 IS
2353 l_csp_inv_loc_assignment_id csp_inv_loc_assignments.csp_inv_loc_assignment_id%TYPE;
2354 l_sqlerrno VARCHAR2(20);
2355 l_sqlerrmsg VARCHAR2(2000);
2356
2357 BEGIN
2358 x_return_status := FND_API.G_RET_STS_SUCCESS;
2359
2360 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2361 RETURN;
2362 END IF;
2363
2364 l_csp_inv_loc_assignment_id := CSP_INV_LOC_ASSIGNMENTS_PKG.user_hooks_rec.CSP_INV_LOC_ASSIGNMENT_ID;
2365
2366 CSM_UTIL_PKG.LOG('Entering CSP_Inv_Loc_Assg_Post_Del for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id),
2367 'CSM_WF_PKG.CSP_Inv_Loc_Assg_Post_Del', FND_LOG.LEVEL_PROCEDURE );
2368
2369 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT(p_csp_inv_loc_assignment_id=>l_csp_inv_loc_assignment_id);
2370
2371 CSM_UTIL_PKG.LOG('Leaving CSP_Inv_Loc_Assg_Post_Del for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id),
2372 'CSM_WF_PKG.CSP_Inv_Loc_Assg_Post_Del', FND_LOG.LEVEL_PROCEDURE );
2373
2374 EXCEPTION
2375 WHEN OTHERS THEN
2376 x_return_status := FND_API.G_RET_STS_ERROR;
2377 l_sqlerrno := to_char(SQLCODE);
2378 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2379 CSM_UTIL_PKG.LOG('Exception in CSP_Inv_Loc_Assg_Post_Del for csp_inv_loc_assignment_id: ' || TO_CHAR(l_csp_inv_loc_assignment_id)
2380 || ':' || l_sqlerrno || ':' || l_sqlerrmsg,
2381 'CSM_WF_PKG.CSP_Inv_Loc_Assg_Post_Del', FND_LOG.LEVEL_EXCEPTION);
2382 END CSP_Inv_Loc_Assg_Post_Del;
2383
2384 PROCEDURE CSP_SHIP_TO_ADDRESS_POST_INS(x_return_status OUT NOCOPY VARCHAR2)
2385 IS
2386 l_location_id po_location_associations_all.location_id%TYPE;
2387 l_sqlerrno varchar2(20);
2388 l_sqlerrmsg varchar2(2000);
2389 l_error_msg VARCHAR2(4000);
2390 l_return_status VARCHAR2(2000);
2391
2392 CURSOR l_ship_locations_ins_csr (p_locationid NUMBER) IS
2393 SELECT pla.location_id ,
2394 pla.org_id ,
2395 decode(rcr.resource_type,'RS_GROUP','G','E') resource_type,
2396 au.user_id user_id,
2397 cas.party_site_id party_site_id
2398 FROM po_location_associations_all pla,
2399 hz_cust_site_uses_all csu,
2400 hz_cust_acct_sites_all cas,
2401 csp_rs_cust_relations rcr,
2402 asg_user au
2403 WHERE pla.location_id = p_locationid
2404 AND csu.site_use_id = pla.site_use_id
2405 AND csu.site_use_code = 'SHIP_TO'
2406 AND csu.cust_acct_site_id = cas.cust_acct_site_id
2407 AND cas.cust_account_id = rcr.customer_id
2408 AND cas.status = 'A' -- only active sites
2409 AND ((rcr.resource_type='RS_EMPLOYEE' AND au.resource_id = rcr.resource_id)
2410 OR
2411 (rcr.resource_type='RS_GROUP' AND rcr.resource_id IN (SELECT group_id FROM jtf_rs_group_members_vl WHERE category='EMPLOYEE' AND resource_id = au.resource_id)))
2412 AND au.enabled='Y'
2413 AND NOT EXISTS
2414 (SELECT 1
2415 FROM csm_po_loc_ass_all_acc acc
2416 WHERE acc.user_id = au.user_id
2417 AND acc.location_id = pla.location_id
2418 AND acc.org_id = pla.org_id
2419 AND acc.RESOURCE_TYPE= decode(rcr.resource_type,'RS_GROUP','G','E'));
2420
2421 BEGIN
2422 x_return_status := FND_API.G_RET_STS_SUCCESS;
2423
2424 l_location_id := csp_ship_to_address_pvt.g_inv_loc_id;
2425
2426 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2427 RETURN;
2428 END IF;
2429
2430 FOR r_ship_locations_ins_rec IN l_ship_locations_ins_csr(l_location_id) LOOP
2431 csm_party_site_event_pkg.party_sites_acc_i
2432 (p_party_site_id=>r_ship_locations_ins_rec.party_site_id,
2433 p_user_id=>r_ship_locations_ins_rec.user_id,
2434 p_flowtype=>NULL,
2435 p_error_msg=>l_error_msg,
2436 x_return_status=>l_return_status);
2437
2438 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_i
2439 (p_location_id=>r_ship_locations_ins_rec.location_id,
2440 p_org_id=>r_ship_locations_ins_rec.org_id,
2441 p_res_type=>r_ship_locations_ins_rec.resource_type,
2442 p_user_id=>r_ship_locations_ins_rec.user_id);
2443 END LOOP;
2444 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_INS', 'CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_INS',
2445 FND_LOG.LEVEL_PROCEDURE );
2446
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 x_return_status := FND_API.G_RET_STS_ERROR;
2450 l_sqlerrno := to_char(SQLCODE);
2451 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2452 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_INS:' || l_sqlerrno || ':' || l_sqlerrmsg,
2453 'CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_INS', FND_LOG.LEVEL_EXCEPTION);
2454 END CSP_SHIP_TO_ADDRESS_POST_INS;
2455
2456 PROCEDURE CSP_SHIP_TO_ADDRESS_POST_UPD(x_return_status OUT NOCOPY varchar2)
2457 IS
2458 l_location_id po_location_associations_all.location_id%TYPE;
2459 l_sqlerrno varchar2(20);
2460 l_sqlerrmsg varchar2(2000);
2461 l_error_msg VARCHAR2(4000);
2462 l_return_status VARCHAR2(2000);
2463
2464 CURSOR l_ship_locations_upd_csr (p_locationid NUMBER) IS
2465 SELECT acc.location_id,
2466 acc.org_id,
2467 acc.resource_type,
2468 acc.user_id
2469 FROM csm_po_loc_ass_all_acc acc
2470 WHERE acc.location_id = p_locationid;
2471
2472 BEGIN
2473 x_return_status := FND_API.G_RET_STS_SUCCESS;
2474
2475 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2476 RETURN;
2477 END IF;
2478
2479 l_location_id := csp_ship_to_address_pvt.g_inv_loc_id;
2480
2481 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_INS', 'CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_UPD',
2482 FND_LOG.LEVEL_PROCEDURE );
2483
2484 FOR r_ship_locations_upd_rec IN l_ship_locations_upd_csr(l_location_id) LOOP
2485
2486 /* --Party Sites need not be updated
2487 csm_party_site_event_pkg.party_sites_acc_u
2488 (p_party_site_id=>r_ship_locations_upd_rec.party_site_id,
2489 p_user_id=>r_ship_locations_upd_rec.user_id,
2490 p_error_msg=>l_error_msg,
2491 x_return_status=>l_return_status);*/
2492
2493
2494 csm_po_locations_event_pkg.csp_ship_to_addr_mdirty_u
2495 (p_location_id=>r_ship_locations_upd_rec.location_id,
2496 p_org_id=>r_ship_locations_upd_rec.org_id,
2497 p_res_type=>r_ship_locations_upd_rec.resource_type,
2498 p_user_id=>r_ship_locations_upd_rec.user_id);
2499 END LOOP;
2500
2501 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_UPD', 'CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_UPD',
2502 FND_LOG.LEVEL_PROCEDURE );
2503
2504 EXCEPTION
2505 WHEN OTHERS THEN
2506 x_return_status := FND_API.G_RET_STS_ERROR;
2507 l_sqlerrno := to_char(SQLCODE);
2508 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2509 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_UPD:' || l_sqlerrno || ':' || l_sqlerrmsg,
2510 'CSM_WF_PKG.CSP_SHIP_TO_ADDRESS_POST_UPD', FND_LOG.LEVEL_EXCEPTION);
2511 END CSP_SHIP_TO_ADDRESS_POST_UPD;
2512
2513 PROCEDURE CSP_REQ_HEADERS_POST_INS(x_return_status OUT NOCOPY varchar2)
2514 IS
2515 l_req_header_id csp_requirement_headers.requirement_header_id%TYPE;
2516 l_sqlerrno varchar2(20);
2517 l_sqlerrmsg varchar2(2000);
2518
2519 CURSOR l_req_hdr_csr(p_req_header_id IN NUMBER ) IS
2520 SELECT hdr.resource_id,
2521 jtrs.user_id
2522 FROM csp_requirement_headers hdr,
2523 jtf_rs_resource_extns jtrs
2524 WHERE hdr.requirement_header_id = p_req_header_id
2525 AND jtrs.resource_id = hdr.resource_id
2526 UNION
2527 SELECT ta.resource_id,
2528 jtrs.user_id
2529 FROM csp_requirement_headers hdr,
2530 jtf_tasks_b jt,
2531 jtf_task_assignments ta,
2532 jtf_rs_resource_extns jtrs
2533 WHERE hdr.requirement_header_id = p_req_header_id
2534 AND jt.task_id = hdr.task_id
2535 AND ta.task_id = jt.task_id
2536 AND jtrs.resource_id = ta.resource_id
2537 ;
2538
2539 BEGIN
2540 x_return_status := FND_API.G_RET_STS_SUCCESS;
2541
2542 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2543 RETURN;
2544 END IF;
2545
2546 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS', 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS',
2547 FND_LOG.LEVEL_PROCEDURE );
2548
2549 l_req_header_id := CSP_REQUIREMENT_HEADERS_PKG.user_hooks_rec.REQUIREMENT_HEADER_ID;
2550
2551 FOR r_req_hdr_rec IN l_req_hdr_csr(l_req_header_id) LOOP
2552 IF csm_util_pkg.is_palm_resource(r_req_hdr_rec.resource_id) THEN
2553 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_i(p_requirement_header_id=>l_req_header_id,
2554 p_user_id=>r_req_hdr_rec.user_id);
2555 ELSE
2556 CSM_UTIL_PKG.LOG('Resource:' || r_req_hdr_rec.resource_id || ' not a mobile resource',
2557 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS', FND_LOG.LEVEL_ERROR );
2558 END IF;
2559
2560 END LOOP;
2561 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS', 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS',
2562 FND_LOG.LEVEL_PROCEDURE );
2563
2564 EXCEPTION
2565 WHEN OTHERS THEN
2566 x_return_status := FND_API.G_RET_STS_ERROR;
2567 l_sqlerrno := to_char(SQLCODE);
2568 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2569 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS:' || l_sqlerrno || ':' || l_sqlerrmsg,
2570 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_INS', FND_LOG.LEVEL_EXCEPTION);
2571 END CSP_REQ_HEADERS_POST_INS;
2572
2573 PROCEDURE CSP_REQ_HEADERS_POST_UPD(x_return_status OUT NOCOPY varchar2)
2574 IS
2575 l_req_header_id csp_requirement_headers.requirement_header_id%TYPE;
2576 l_sqlerrno varchar2(20);
2577 l_sqlerrmsg varchar2(2000);
2578
2579 CURSOR l_req_hdr_csr(p_req_header_id IN NUMBER )
2580 IS
2581 SELECT acc.requirement_header_id,
2582 acc.user_id
2583 FROM csm_req_headers_acc acc
2584 WHERE acc.requirement_header_id = p_req_header_id;
2585
2586 BEGIN
2587 x_return_status := FND_API.G_RET_STS_SUCCESS;
2588
2589 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2590 RETURN;
2591 END IF;
2592
2593 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_HEADERS_POST_UPD', 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_UPD',
2594 FND_LOG.LEVEL_PROCEDURE );
2595
2596 l_req_header_id := CSP_REQUIREMENT_HEADERS_PKG.user_hooks_rec.REQUIREMENT_HEADER_ID;
2597
2598 FOR r_req_hdr_rec IN l_req_hdr_csr(l_req_header_id) LOOP
2599 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_u(p_requirement_header_id=>l_req_header_id,
2600 p_user_id=>r_req_hdr_rec.user_id);
2601
2602 END LOOP;
2603 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_HEADERS_POST_UPD', 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_UPD',
2604 FND_LOG.LEVEL_PROCEDURE );
2605
2606 EXCEPTION
2607 WHEN OTHERS THEN
2608 x_return_status := FND_API.G_RET_STS_ERROR;
2609 l_sqlerrno := to_char(SQLCODE);
2610 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2611 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_HEADERS_POST_UPD:' || l_sqlerrno || ':' || l_sqlerrmsg,
2612 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_UPD', FND_LOG.LEVEL_EXCEPTION);
2613 END CSP_REQ_HEADERS_POST_UPD;
2614
2615 PROCEDURE CSP_REQ_HEADERS_POST_DEL(x_return_status OUT NOCOPY varchar2)
2616 IS
2617 l_req_header_id csp_requirement_headers.requirement_header_id%TYPE;
2618 l_sqlerrno varchar2(20);
2619 l_sqlerrmsg varchar2(2000);
2620
2621 CURSOR l_req_hdr_acc_csr(p_req_header_id IN NUMBER ) IS
2622 SELECT acc.requirement_header_id,
2623 acc.user_id
2624 FROM csm_req_headers_acc acc
2625 WHERE acc.requirement_header_id = p_req_header_id;
2626
2627 BEGIN
2628 x_return_status := FND_API.G_RET_STS_SUCCESS;
2629
2630 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2631 RETURN;
2632 END IF;
2633
2634 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_HEADERS_POST_DEL', 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_DEL',
2635 FND_LOG.LEVEL_PROCEDURE );
2636
2637 l_req_header_id := CSP_REQUIREMENT_HEADERS_PKG.user_hooks_rec.REQUIREMENT_HEADER_ID;
2638
2639 FOR r_req_hdr_acc_rec IN l_req_hdr_acc_csr(l_req_header_id) LOOP
2640 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_d(p_requirement_header_id=>l_req_header_id,
2641 p_user_id=>r_req_hdr_acc_rec.user_id);
2642
2643 END LOOP;
2644 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_HEADERS_POST_DEL', 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_DEL',
2645 FND_LOG.LEVEL_PROCEDURE );
2646
2647 EXCEPTION
2648 WHEN OTHERS THEN
2649 x_return_status := FND_API.G_RET_STS_ERROR;
2650 l_sqlerrno := to_char(SQLCODE);
2651 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2652 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_HEADERS_POST_DEL:' || l_sqlerrno || ':' || l_sqlerrmsg,
2653 'CSM_WF_PKG.CSP_REQ_HEADERS_POST_DEL', FND_LOG.LEVEL_EXCEPTION);
2654 END CSP_REQ_HEADERS_POST_DEL;
2655
2656 PROCEDURE CSP_REQ_LINES_POST_INS(x_return_status OUT NOCOPY varchar2)
2657 IS
2658 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
2659 l_sqlerrno varchar2(20);
2660 l_sqlerrmsg varchar2(2000);
2661
2662 CURSOR l_req_line_csr(p_req_line_id IN NUMBER ) IS
2663 SELECT hdr.requirement_header_id,
2664 hdr.resource_id,
2665 jtrs.user_id
2666 FROM csp_requirement_headers hdr,
2667 csp_requirement_lines line,
2668 jtf_rs_resource_extns jtrs
2669 WHERE hdr.requirement_header_id = line.requirement_header_id
2670 AND line.requirement_line_id = p_req_line_id
2671 AND jtrs.resource_id = hdr.resource_id
2672 UNION
2673 SELECT hdr.requirement_header_id,
2674 ta.resource_id,
2675 jtrs.user_id
2676 FROM csp_requirement_headers hdr,
2677 csp_requirement_lines line,
2678 jtf_tasks_b jt,
2679 jtf_task_assignments ta,
2680 jtf_rs_resource_extns jtrs
2681 WHERE hdr.requirement_header_id = line.requirement_header_id
2682 AND line.requirement_line_id = p_req_line_id
2683 AND jt.task_id = hdr.task_id
2684 AND ta.task_id = jt.task_id
2685 AND jtrs.resource_id = ta.resource_id
2686 ;
2687
2688 BEGIN
2689 x_return_status := FND_API.G_RET_STS_SUCCESS;
2690
2691 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2692 RETURN;
2693 END IF;
2694
2695 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_LINES_POST_INS', 'CSM_WF_PKG.CSP_REQ_LINES_POST_INS',
2696 FND_LOG.LEVEL_PROCEDURE );
2697
2698 l_req_line_id := CSP_REQUIREMENT_LINES_PKG.user_hook_rec.REQUIREMENT_LINE_ID;
2699
2700 FOR r_req_line_rec IN l_req_line_csr(l_req_line_id) LOOP
2701 IF csm_util_pkg.is_palm_resource(r_req_line_rec.resource_id) THEN
2702 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_i(p_requirement_line_id=>l_req_line_id,
2703 p_user_id=>r_req_line_rec.user_id);
2704 ELSE
2705 CSM_UTIL_PKG.LOG('Resource:' || r_req_line_rec.resource_id || ' not a mobile resource',
2706 'CSM_WF_PKG.CSP_REQ_LINES_POST_INS', FND_LOG.LEVEL_ERROR );
2707 END IF;
2708
2709 END LOOP;
2710 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_LINES_POST_INS', 'CSM_WF_PKG.CSP_REQ_LINES_POST_INS',
2711 FND_LOG.LEVEL_PROCEDURE );
2712
2713 EXCEPTION
2714 WHEN OTHERS THEN
2715 x_return_status := FND_API.G_RET_STS_ERROR;
2716 l_sqlerrno := to_char(SQLCODE);
2717 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2718 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_LINES_POST_INS:' || l_sqlerrno || ':' || l_sqlerrmsg,
2719 'CSM_WF_PKG.CSP_REQ_LINES_POST_INS', FND_LOG.LEVEL_EXCEPTION);
2720 END CSP_REQ_LINES_POST_INS;
2721
2722 PROCEDURE CSP_REQ_LINES_POST_UPD(x_return_status OUT NOCOPY varchar2)
2723 IS
2724 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
2725 l_sqlerrno varchar2(20);
2726 l_sqlerrmsg varchar2(2000);
2727
2728 CURSOR l_req_line_csr(p_req_line_id IN NUMBER ) IS
2729 SELECT acc.requirement_line_id,
2730 acc.user_id
2731 FROM csm_req_lines_acc acc
2732 WHERE acc.requirement_line_id = p_req_line_id;
2733
2734 BEGIN
2735 x_return_status := FND_API.G_RET_STS_SUCCESS;
2736
2737 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2738 RETURN;
2739 END IF;
2740
2741 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_LINES_POST_UPD', 'CSM_WF_PKG.CSP_REQ_LINES_POST_UPD',
2742 FND_LOG.LEVEL_PROCEDURE );
2743
2744 l_req_line_id := CSP_REQUIREMENT_LINES_PKG.user_hook_rec.REQUIREMENT_LINE_ID;
2745
2746 FOR r_req_line_rec IN l_req_line_csr(l_req_line_id) LOOP
2747 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_u(p_requirement_line_id=>l_req_line_id,
2748 p_user_id=>r_req_line_rec.user_id);
2749 END LOOP;
2750
2751 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_LINES_POST_UPD', 'CSM_WF_PKG.CSP_REQ_LINES_POST_UPD',
2752 FND_LOG.LEVEL_PROCEDURE );
2753
2754 EXCEPTION
2755 WHEN OTHERS THEN
2756 x_return_status := FND_API.G_RET_STS_ERROR;
2757 l_sqlerrno := to_char(SQLCODE);
2758 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2759 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_LINES_POST_UPD:' || l_sqlerrno || ':' || l_sqlerrmsg,
2760 'CSM_WF_PKG.CSP_REQ_LINES_POST_UPD', FND_LOG.LEVEL_EXCEPTION);
2761 END CSP_REQ_LINES_POST_UPD;
2762
2763 PROCEDURE CSP_REQ_LINES_POST_DEL(x_return_status OUT NOCOPY varchar2)
2764 IS
2765 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
2766 l_sqlerrno varchar2(20);
2767 l_sqlerrmsg varchar2(2000);
2768
2769 CURSOR l_req_line_acc_csr(p_req_line_id IN NUMBER ) IS
2770 SELECT acc.requirement_line_id,
2771 acc.user_id
2772 FROM csm_req_lines_acc acc
2773 WHERE acc.requirement_line_id = p_req_line_id;
2774
2775 BEGIN
2776 x_return_status := FND_API.G_RET_STS_SUCCESS;
2777
2778 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2779 RETURN;
2780 END IF;
2781
2782 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_LINES_POST_DEL', 'CSM_WF_PKG.CSP_REQ_LINES_POST_DEL',
2783 FND_LOG.LEVEL_PROCEDURE );
2784
2785 l_req_line_id := CSP_REQUIREMENT_LINES_PKG.user_hook_rec.REQUIREMENT_LINE_ID;
2786
2787 FOR r_req_line_acc_rec IN l_req_line_acc_csr(l_req_line_id) LOOP
2788 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_d(p_requirement_line_id=>l_req_line_id,
2789 p_user_id=>r_req_line_acc_rec.user_id);
2790
2791 END LOOP;
2792
2793 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_LINES_POST_DEL', 'CSM_WF_PKG.CSP_REQ_LINES_POST_DEL',
2794 FND_LOG.LEVEL_PROCEDURE );
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 x_return_status := FND_API.G_RET_STS_ERROR;
2798 l_sqlerrno := to_char(SQLCODE);
2799 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2800 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_LINES_POST_DEL:' || l_sqlerrno || ':' || l_sqlerrmsg,
2801 'CSM_WF_PKG.CSP_REQ_LINES_POST_DEL', FND_LOG.LEVEL_EXCEPTION);
2802 END CSP_REQ_LINES_POST_DEL;
2803
2804 PROCEDURE CSP_REQ_LINE_DETAILS_POST_INS(x_return_status OUT NOCOPY varchar2)
2805 IS
2806 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
2807 l_req_line_detail_id csp_req_line_details.req_line_detail_id%TYPE;
2808 l_sqlerrno varchar2(20);
2809 l_sqlerrmsg varchar2(2000);
2810
2811 BEGIN
2812 x_return_status := FND_API.G_RET_STS_SUCCESS;
2813
2814 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2815 RETURN;
2816 END IF;
2817
2818 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_INS', 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_INS',
2819 FND_LOG.LEVEL_PROCEDURE );
2820
2821 l_req_line_id :=CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.REQUIREMENT_LINE_ID; --CSF tech portal seems to pass this wrongly
2822 l_req_line_detail_id := CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.req_line_detail_id; --only this is passed correctly from all src
2823
2824 -- l_src:=CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.source_type; --remains NULL thru iUhk
2825 -- l_src_id:=CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.source_id;
2826
2827 csm_csp_req_lines_event_pkg.REQ_LINE_DTL_IUD(l_req_line_detail_id,'I');
2828
2829 --Notify User of new Order placed
2830 RAISE_START_AUTO_SYNC_EVENT('CSM_REQ_LINE_DETAILS',to_char(l_req_line_detail_id),'NEW');
2831
2832 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_INS', 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_INS',
2833 FND_LOG.LEVEL_PROCEDURE );
2834
2835 EXCEPTION
2836 WHEN OTHERS THEN
2837 x_return_status := FND_API.G_RET_STS_ERROR;
2838 l_sqlerrno := to_char(SQLCODE);
2839 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2840 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_INS:' || l_sqlerrno || ':' || l_sqlerrmsg,
2841 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_INS', FND_LOG.LEVEL_EXCEPTION);
2842 x_return_status := FND_API.G_RET_STS_ERROR;
2843 END CSP_REQ_LINE_DETAILS_POST_INS;
2844
2845 PROCEDURE CSP_REQ_LINE_DETAILS_POST_UPD(x_return_status OUT NOCOPY varchar2)
2846 IS
2847 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
2848 l_req_line_detail_id csp_req_line_details.req_line_detail_id%TYPE;
2849 l_sqlerrno varchar2(20);
2850 l_sqlerrmsg varchar2(2000);
2851
2852 BEGIN
2853 x_return_status := FND_API.G_RET_STS_SUCCESS;
2854
2855 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2856 RETURN;
2857 END IF;
2858
2859 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_UPD', 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_UPD',
2860 FND_LOG.LEVEL_PROCEDURE );
2861
2862 l_req_line_id :=CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.REQUIREMENT_LINE_ID; --CSF tech portal seems to pass this wrongly
2863 l_req_line_detail_id := CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.req_line_detail_id; --only this is passed correctly from all src
2864
2865 csm_csp_req_lines_event_pkg.REQ_LINE_DTL_IUD(l_req_line_detail_id,'U');
2866
2867 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_UPD', 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_UPD',
2868 FND_LOG.LEVEL_PROCEDURE );
2869
2870 EXCEPTION
2871 WHEN OTHERS THEN
2872 x_return_status := FND_API.G_RET_STS_ERROR;
2873 l_sqlerrno := to_char(SQLCODE);
2874 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2875 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_UPD:' || l_sqlerrno || ':' || l_sqlerrmsg,
2876 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_POST_UPD', FND_LOG.LEVEL_EXCEPTION);
2877 x_return_status := FND_API.G_RET_STS_ERROR;
2878 END CSP_REQ_LINE_DETAILS_POST_UPD;
2879
2880 PROCEDURE CSP_REQ_LINE_DETAILS_PRE_DEL(x_return_status OUT NOCOPY varchar2)
2881 IS
2882 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
2883 l_req_line_detail_id csp_req_line_details.req_line_detail_id%TYPE;
2884 l_sqlerrno varchar2(20);
2885 l_sqlerrmsg varchar2(2000);
2886
2887 BEGIN
2888 x_return_status := FND_API.G_RET_STS_SUCCESS;
2889
2890 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2891 RETURN;
2892 END IF;
2893
2894 CSM_UTIL_PKG.LOG('Entering CSM_WF_PKG.CSP_REQ_LINE_DETAILS_PRE_DEL', 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_PRE_DEL',
2895 FND_LOG.LEVEL_PROCEDURE );
2896
2897 l_req_line_id :=CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.REQUIREMENT_LINE_ID; --CSF tech portal seems to pass this wrongly
2898 l_req_line_detail_id := CSP_REQ_LINE_DETAILS_PKG.user_hook_rec.req_line_detail_id; --only this is passed correctly from all src
2899
2900 csm_csp_req_lines_event_pkg.REQ_LINE_DTL_IUD(l_req_line_detail_id,'D');
2901
2902 CSM_UTIL_PKG.LOG('Leaving CSM_WF_PKG.CSP_REQ_LINE_DETAILS_PRE_DEL', 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_PRE_DEL',
2903 FND_LOG.LEVEL_PROCEDURE );
2904
2905 EXCEPTION
2906 WHEN OTHERS THEN
2907 x_return_status := FND_API.G_RET_STS_ERROR;
2908 l_sqlerrno := to_char(SQLCODE);
2909 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2910 CSM_UTIL_PKG.LOG('Exception in CSM_WF_PKG.CSP_REQ_LINE_DETAILS_PRE_DEL:' || l_sqlerrno || ':' || l_sqlerrmsg,
2911 'CSM_WF_PKG.CSP_REQ_LINE_DETAILS_PRE_DEL', FND_LOG.LEVEL_EXCEPTION);
2912 END CSP_REQ_LINE_DETAILS_PRE_DEL;
2913
2914 /*--------------------------------------------------
2915 Description:
2916 Starts the NOTES_INS_USERLOOP workflow. Should be called when new
2917 NOTE is created.
2918 Invoked by JTM_NOTES_VUHK.create_note_post
2919 Parameter(s):
2920 p_api_version
2921 , p_init_msg_list
2922 , p_commit
2923 , p_validation_level
2924 , x_msg_count
2925 , x_msg_data
2926 , x_return_status
2927 ,p_jtf_note_id
2928 ----------------------------------------------------*/
2929 PROCEDURE JTF_Note_Post_Ins(p_api_version IN NUMBER
2930 , p_init_msg_list IN VARCHAR2
2931 , p_commit IN VARCHAR2
2932 , p_validation_level IN NUMBER
2933 , x_msg_count OUT NOCOPY NUMBER
2934 , x_msg_data OUT NOCOPY VARCHAR2
2935 , x_return_status OUT NOCOPY VARCHAR2
2936 ,p_jtf_note_id in jtf_notes_b.jtf_note_id%type)
2937 IS
2938 l_sqlerrno VARCHAR2(20);
2939 l_sqlerrmsg VARCHAR2(4000);
2940 l_error_msg VARCHAR2(4000);
2941 l_return_status VARCHAR2(2000);
2942
2943 BEGIN
2944 x_return_status := FND_API.G_RET_STS_SUCCESS;
2945
2946 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2947 RETURN;
2948 END IF;
2949
2950 csm_notes_event_pkg.notes_make_dirty_i_foreachuser(p_jtf_note_id,l_error_msg,l_return_status);
2951 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2952 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.JTF_Note_Post_Ins', FND_LOG.LEVEL_ERROR);
2953 END IF;
2954
2955 EXCEPTION
2956 WHEN OTHERS THEN
2957 x_return_status := FND_API.G_RET_STS_ERROR ;
2958 l_sqlerrno := to_char(SQLCODE);
2959 l_sqlerrmsg := substr(SQLERRM, 1,2000);
2960 l_error_msg := l_error_msg || '- Exception in JTF_Note_Post_Ins for note_id:'
2961 || TO_CHAR(p_jtf_note_id) || l_sqlerrno || ':' || l_sqlerrmsg;
2962 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.JTF_Note_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
2963 END JTF_Note_Post_Ins;
2964
2965 /*--------------------------------------------------
2966 Description:
2967 Starts the NOTES_UPD_USERLOOP workflow. Should be called when new
2968 NOTE is updated.
2969 Invoked by JTM_NOTES_VUHK.update_note_post
2970 Parameter(s):
2971 p_api_version
2972 , p_init_msg_list
2973 , p_commit
2974 , p_validation_level
2975 , x_msg_count
2976 , x_msg_data
2977 , x_return_status
2978 ,p_jtf_note_id
2979 ----------------------------------------------------*/
2980 PROCEDURE JTF_Note_PRE_Upd(p_api_version IN NUMBER
2981 , p_init_msg_list IN VARCHAR2
2982 , p_commit IN VARCHAR2
2983 , p_validation_level IN NUMBER
2984 , x_msg_count OUT NOCOPY NUMBER
2985 , x_msg_data OUT NOCOPY VARCHAR2
2986 , x_return_status OUT NOCOPY VARCHAR2
2987 ,p_jtf_note_id in jtf_notes_b.jtf_note_id%type)
2988 IS
2989 l_sqlerrno VARCHAR2(20);
2990 l_sqlerrmsg VARCHAR2(4000);
2991 l_error_msg VARCHAR2(4000);
2992 l_return_status VARCHAR2(4000);
2993
2994 BEGIN
2995 x_return_status := FND_API.G_RET_STS_SUCCESS;
2996
2997 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
2998 RETURN;
2999 END IF;
3000
3001 csm_notes_event_pkg.notes_make_dirty_i_foreachuser(p_jtf_note_id, l_error_msg, l_return_status);
3002 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3003 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.JTF_Note_Pre_Upd', FND_LOG.LEVEL_ERROR);
3004 END IF;
3005
3006 EXCEPTION
3007 WHEN OTHERS THEN
3008 x_return_status := FND_API.G_RET_STS_ERROR ;
3009 l_sqlerrno := to_char(SQLCODE);
3010 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3011 l_error_msg := l_error_msg || '- Exception in JTF_Note_PRE_Upd for note_id:'
3012 || TO_CHAR(p_jtf_note_id) || l_sqlerrno || ':' || l_sqlerrmsg;
3013 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.JTF_Note_PRE_Upd',FND_LOG.LEVEL_EXCEPTION);
3014 END JTF_Note_PRE_Upd;
3015
3016 PROCEDURE JTF_Note_POST_Upd(p_api_version IN NUMBER
3017 , p_init_msg_list IN VARCHAR2
3018 , p_commit IN VARCHAR2
3019 , p_validation_level IN NUMBER
3020 , x_msg_count OUT NOCOPY NUMBER
3021 , x_msg_data OUT NOCOPY VARCHAR2
3022 , x_return_status OUT NOCOPY VARCHAR2
3023 ,p_jtf_note_id in jtf_notes_b.jtf_note_id%type)
3024 IS
3025 l_sqlerrno VARCHAR2(20);
3026 l_sqlerrmsg VARCHAR2(4000);
3027 l_error_msg VARCHAR2(4000);
3028 l_return_status VARCHAR2(4000);
3029 BEGIN
3030 x_return_status := FND_API.G_RET_STS_SUCCESS;
3031 EXCEPTION
3032 WHEN OTHERS THEN
3033 x_return_status := FND_API.G_RET_STS_ERROR ;
3034 l_sqlerrno := to_char(SQLCODE);
3035 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3036 l_error_msg := l_error_msg || '- Exception in JTF_Note_POST_Upd for note_id:'
3037 || TO_CHAR(p_jtf_note_id) || l_sqlerrno || ':' || l_sqlerrmsg;
3038 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.JTF_Note_POST_Upd',FND_LOG.LEVEL_EXCEPTION);
3039 END JTF_Note_POST_Upd;
3040
3041 Procedure CSI_Item_Instance_Post_Ins(p_api_version IN NUMBER
3042 , p_init_msg_list IN VARCHAR2
3043 , p_commit IN VARCHAR2
3044 , p_validation_level IN NUMBER
3045 , p_instance_id IN NUMBER
3046 , x_return_status OUT NOCOPY VARCHAR2
3047 , x_msg_count OUT NOCOPY NUMBER
3048 , x_msg_data OUT NOCOPY VARCHAR2)
3049 IS
3050 l_old_wf_threshold number;
3051 l_itemtype varchar2(30);
3052 l_itemkey varchar2(30);
3053 l_seq_val number(15);
3054
3055 CURSOR l_seq_val_csr IS
3056 SELECT CSM_ACTIVITY_SEQ.nextval
3057 FROM dual;
3058
3059 BEGIN
3060 -- l_old_wf_threshold := wf_engine.threshold;
3061 NULL;
3062
3063 /* OPEN l_seq_val_csr;
3064 FETCH l_seq_val_csr INTO l_seq_val;
3065 CLOSE l_seq_val_csr;
3066
3067 --use the CSMTYPE3 itemtype
3068 l_itemtype := 'CSMTYPE3';
3069 --generate a unique itemkey
3070 l_itemkey := 'ITEM_I' || '_' || to_char(p_instance_id) || '_' || l_seq_val;
3071
3072 --create the process
3073 wf_engine.CreateProcess(itemtype => l_itemtype,
3074 itemkey => l_itemkey,
3075 process => 'ITEM_INSTANCE_INS');
3076
3077 --set the activity threshold value to negatiev value, so that the
3078 --process gets defered to background
3079 wf_engine.threshold := -1;
3080
3081 -- set jtf_note_id
3082 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'INSTANCE_ID',
3083 p_instance_id );
3084
3085 wf_engine.StartProcess(l_itemtype, l_itemkey);
3086 wf_engine.threshold := l_old_wf_threshold;
3087 */
3088 EXCEPTION
3089 WHEN OTHERS THEN
3090 csm_util_pkg.log('ERROR: CSI_ITEM_INSTANCE_POST_INS => '
3091 || to_char(p_instance_id));
3092 END CSI_Item_Instance_Post_Ins;
3093
3094 Procedure CSI_Item_Instance_Pre_Upd(p_api_version IN NUMBER
3095 , p_init_msg_list IN VARCHAR2
3096 , p_commit IN VARCHAR2
3097 , p_validation_level IN NUMBER
3098 , p_instance_id IN NUMBER
3099 , x_return_status OUT NOCOPY VARCHAR2
3100 , x_msg_count OUT NOCOPY NUMBER
3101 , x_msg_data OUT NOCOPY VARCHAR2)
3102 IS
3103 l_sqlerrno VARCHAR2(20);
3104 l_sqlerrmsg VARCHAR2(4000);
3105 l_error_msg VARCHAR2(4000);
3106 l_return_status VARCHAR2(2000);
3107 l_instance_id csi_item_instances.instance_id%TYPE;
3108
3109 BEGIN
3110 x_return_status := FND_API.G_RET_STS_SUCCESS;
3111
3112 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3113 RETURN;
3114 END IF;
3115
3116 CSM_ITEM_INSTANCE_EVENT_PKG.ITEM_INSTANCE_MDIRTY_U_ECHUSER(p_instance_id, l_error_msg, l_return_status);
3117 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3118 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.CSI_Item_Instance_Pre_Upd', FND_LOG.LEVEL_ERROR);
3119 END IF;
3120
3121 EXCEPTION
3122 WHEN OTHERS THEN
3123 x_return_status := FND_API.G_RET_STS_ERROR;
3124 l_sqlerrno := to_char(SQLCODE);
3125 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3126 l_error_msg := 'Exception in CSI_Item_Instance_Pre_Upd for instance_id:' || TO_CHAR(p_instance_id)
3127 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3128 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CSI_Item_Instance_Pre_Upd',FND_LOG.LEVEL_EXCEPTION);
3129 END CSI_Item_Instance_Pre_Upd;
3130
3131 Procedure CS_Counter_Post_Ins(
3132 P_Api_Version IN NUMBER,
3133 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3134 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3135 X_Return_Status OUT NOCOPY VARCHAR2,
3136 X_Msg_Count OUT NOCOPY NUMBER,
3137 X_Msg_Data OUT NOCOPY VARCHAR2,
3138 p_counter_id IN NUMBER,
3139 x_object_version_number OUT NOCOPY NUMBER
3140 -- p_user_id in number,
3141 -- p_counter_id cs_counters.counter_id%type
3142 )
3143 IS
3144 l_sqlerrno VARCHAR2(20);
3145 l_sqlerrmsg VARCHAR2(4000);
3146 l_error_msg VARCHAR2(4000);
3147 l_return_status VARCHAR2(2000);
3148
3149 BEGIN
3150 x_return_status := FND_API.G_RET_STS_SUCCESS;
3151
3152 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3153 RETURN;
3154 END IF;
3155
3156 csm_counter_event_pkg.CTR_MAKE_DIRTY_I_FOREACHUSER(p_counter_id, l_error_msg, l_return_status);
3157 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3158 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Post_Ins', FND_LOG.LEVEL_ERROR);
3159 END IF;
3160
3161 EXCEPTION
3162 WHEN OTHERS THEN
3163 x_return_status := FND_API.G_RET_STS_ERROR;
3164 l_sqlerrno := to_char(SQLCODE);
3165 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3166 l_error_msg := 'Exception in CS_Counter_Post_Ins for counter_id:' || TO_CHAR(p_counter_id)
3167 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3168 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
3169 END CS_Counter_Post_Ins;
3170
3171 Procedure CS_Counter_Pre_Del(
3172 P_Api_Version IN NUMBER,
3173 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3174 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3175 X_Return_Status OUT NOCOPY VARCHAR2,
3176 X_Msg_Count OUT NOCOPY NUMBER,
3177 X_Msg_Data OUT NOCOPY VARCHAR2,
3178 p_counter_id IN NUMBER
3179 )
3180 IS
3181 l_sqlerrno VARCHAR2(20);
3182 l_sqlerrmsg VARCHAR2(4000);
3183 l_error_msg VARCHAR2(4000);
3184 l_return_status VARCHAR2(2000);
3185
3186 BEGIN
3187 x_return_status := FND_API.G_RET_STS_SUCCESS;
3188
3189 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3190 RETURN;
3191 END IF;
3192
3193 csm_counter_event_pkg.COUNTER_MDIRTY_D(p_counter_id, l_error_msg, l_return_status);
3194 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3195 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Pre_Del', FND_LOG.LEVEL_ERROR);
3196 END IF;
3197
3198 EXCEPTION
3199 WHEN OTHERS THEN
3200 x_return_status := FND_API.G_RET_STS_ERROR;
3201 l_sqlerrno := to_char(SQLCODE);
3202 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3203 l_error_msg := 'Exception in CS_Counter_Pre_Del for counter_id:' || TO_CHAR(p_counter_id)
3204 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3205 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Pre_Del',FND_LOG.LEVEL_EXCEPTION);
3206 END CS_Counter_Pre_Del;
3207
3208 PROCEDURE CS_CTR_GRP_INSTANCE_CRE_POST(
3209 P_Api_Version IN NUMBER,
3210 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3211 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3212 X_Return_Status OUT NOCOPY VARCHAR2,
3213 X_Msg_Count OUT NOCOPY NUMBER,
3214 X_Msg_Data OUT NOCOPY VARCHAR2,
3215 p_source_object_cd IN VARCHAR2,
3216 p_source_object_id IN NUMBER,
3217 p_ctr_grp_id IN NUMBER,
3218 x_object_version_number OUT NOCOPY NUMBER
3219 )
3220 IS
3221 CURSOR l_counter_csr ( b_ctr_grp_id NUMBER)
3222 IS
3223 SELECT counters.COUNTER_ID
3224 FROM CS_COUNTERS counters
3225 , CS_COUNTER_GROUPS counter_groups
3226 WHERE counters.counter_group_id = counter_groups.counter_group_id
3227 AND counter_groups.counter_group_id = b_ctr_grp_id
3228 AND counters.TYPE = 'REGULAR';
3229
3230 CURSOR l_acc_csr (b_source_object_cd VARCHAR2
3231 ,b_source_object_id NUMBER
3232 ,b_ctr_grp_id NUMBER)
3233 IS
3234 SELECT COUNT(1)
3235 FROM CS_COUNTER_GROUPS counter_groups
3236 , csm_item_instances_acc acc
3237 WHERE b_source_object_cd = 'CP'
3238 AND counter_groups.counter_group_id = b_ctr_grp_id
3239 AND counter_groups.source_object_code = b_source_object_cd
3240 AND acc.instance_id = counter_groups.source_object_id
3241 AND counter_groups.source_object_id = b_source_object_id;
3242
3243 l_acc_cnt NUMBER;
3244 l_counter_rec l_counter_csr%ROWTYPE;
3245 l_sqlerrno VARCHAR2(20);
3246 l_sqlerrmsg VARCHAR2(4000);
3247 l_error_msg VARCHAR2(4000);
3248 l_return_status VARCHAR2(2000);
3249
3250 BEGIN
3251 x_return_status := FND_API.G_RET_STS_SUCCESS;
3252
3253 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3254 RETURN;
3255 END IF;
3256
3257 IF p_source_object_cd <> 'CP' THEN
3258 RETURN;
3259 END IF;
3260
3261 -- check if the p_counter_id is belongs to a mobile item_instance.
3262 -- If not, return right here.
3263 OPEN l_acc_csr(p_source_object_cd, p_source_object_id, p_ctr_grp_id);
3264 FETCH l_acc_csr INTO l_acc_cnt;
3265 IF l_acc_cnt = 0 THEN
3266 CLOSE l_acc_csr;
3267 RETURN;
3268 END IF;
3269 CLOSE l_acc_csr;
3270
3271 FOR l_counter_rec IN l_counter_csr(p_ctr_grp_id) LOOP
3272 CS_Counter_Post_Ins(
3273 P_Api_Version ,
3274 P_Init_Msg_List ,
3275 P_Commit ,
3276 X_Return_Status ,
3277 X_Msg_Count ,
3278 X_Msg_Data ,
3279 l_counter_rec.COUNTER_ID , -- p_counter_id ,
3280 x_object_version_number
3281 -- p_user_id in number,
3282 -- p_counter_id cs_counters.counter_id%type
3283 );
3284 END LOOP;
3285
3286 EXCEPTION
3287 WHEN OTHERS THEN
3288 x_return_status := FND_API.G_RET_STS_ERROR;
3289 l_sqlerrno := to_char(SQLCODE);
3290 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3291 l_error_msg := 'Exception in CS_CTR_GRP_INSTANCE_CRE_POST for COUNTER_GROUP_ID:' || TO_CHAR(p_ctr_grp_id)
3292 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3293 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_CTR_GRP_INSTANCE_CRE_POST',FND_LOG.LEVEL_EXCEPTION);
3294 END CS_CTR_GRP_INSTANCE_CRE_POST;
3295
3296 PROCEDURE CS_CTR_GRP_INSTANCE_PRE_DEL(
3297 P_Api_Version IN NUMBER,
3298 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3299 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3300 X_Return_Status OUT NOCOPY VARCHAR2,
3301 X_Msg_Count OUT NOCOPY NUMBER,
3302 X_Msg_Data OUT NOCOPY VARCHAR2,
3303 p_source_object_cd IN VARCHAR2,
3304 p_source_object_id IN NUMBER
3305 )
3306 IS
3307 l_sqlerrno VARCHAR2(20);
3308 l_sqlerrmsg VARCHAR2(4000);
3309 l_error_msg VARCHAR2(4000);
3310 l_return_status VARCHAR2(2000);
3311
3312 CURSOR l_acc_csr (b_source_object_cd VARCHAR2
3313 ,b_source_object_id NUMBER)
3314 IS
3315 SELECT counters.counter_id
3316 FROM CS_COUNTER_GROUPS counter_groups,
3317 cs_counters counters,
3318 csm_item_instances_acc acc
3319 WHERE counter_groups.source_object_code = b_source_object_cd
3320 AND counter_groups.source_object_id = b_source_object_id
3321 AND acc.instance_id = counter_groups.source_object_id
3322 AND counters.counter_group_id = counter_groups.counter_group_id
3323 AND counters.TYPE = 'REGULAR';
3324
3325 BEGIN
3326 x_return_status := FND_API.G_RET_STS_SUCCESS;
3327
3328 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3329 RETURN;
3330 END IF;
3331
3332 IF p_source_object_cd <> 'CP' THEN
3333 RETURN;
3334 END IF;
3335
3336 FOR r_acc_rec IN l_acc_csr(p_source_object_cd, p_source_object_id) LOOP
3337 CS_Counter_Pre_Del(
3338 P_Api_Version ,
3339 P_Init_Msg_List ,
3340 P_Commit ,
3341 X_Return_Status ,
3342 X_Msg_Count ,
3343 X_Msg_Data ,
3344 r_acc_rec.COUNTER_ID -- p_counter_id ,
3345 );
3346 END LOOP;
3347
3348 EXCEPTION
3349 WHEN OTHERS THEN
3350 x_return_status := FND_API.G_RET_STS_ERROR;
3351 l_sqlerrno := to_char(SQLCODE);
3352 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3353 l_error_msg := 'Exception in CS_CTR_GRP_INSTANCE_PRE_DEL for source_object_id:' || TO_CHAR(p_source_object_id)
3354 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3355 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_CTR_GRP_INSTANCE_PRE_DEL',FND_LOG.LEVEL_EXCEPTION);
3356 END CS_CTR_GRP_INSTANCE_PRE_DEL;
3357
3358 PROCEDURE CS_COUNTER_GRP_Post_Upd( P_Api_Version IN NUMBER
3359 , P_Init_Msg_List IN VARCHAR2
3360 , P_Commit IN VARCHAR2
3361 , X_Return_Status OUT NOCOPY VARCHAR2
3362 , X_Msg_Count OUT NOCOPY NUMBER
3363 , X_Msg_Data OUT NOCOPY VARCHAR2
3364 , p_ctr_grp_id IN NUMBER
3365 , p_object_version_number IN NUMBER
3366 , p_cascade_upd_to_instances IN VARCHAR2
3367 , x_object_version_number OUT NOCOPY NUMBER )
3368 IS
3369 l_sqlerrno VARCHAR2(20);
3370 l_sqlerrmsg VARCHAR2(4000);
3371 l_error_msg VARCHAR2(4000);
3372 l_return_status VARCHAR2(2000);
3373
3374 CURSOR l_counter_csr ( b_ctr_grp_id NUMBER)
3375 IS
3376 SELECT counters.counter_id
3377 FROM CS_COUNTERS counters
3378 , CS_COUNTER_GROUPS counter_groups
3379 , csm_item_instances_acc acc
3380 WHERE counter_groups.counter_group_id = b_ctr_grp_id
3381 AND counters.counter_group_id = counter_groups.counter_group_id
3382 AND counter_groups.source_object_code = 'CP'
3383 AND acc.instance_id = counter_groups.source_object_id;
3384
3385 BEGIN
3386 x_return_status := FND_API.G_RET_STS_SUCCESS;
3387
3388 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3389 RETURN;
3390 END IF;
3391
3392 FOR r_counter_csr IN l_counter_csr(p_ctr_grp_id) LOOP
3393 CS_Counter_Post_Upd(
3394 P_Api_Version,
3395 P_Init_Msg_List,
3396 P_Commit,
3397 X_Return_Status,
3398 X_Msg_Count,
3399 X_Msg_Data,
3400 r_counter_csr.counter_id,
3401 p_object_version_number,
3402 p_cascade_upd_to_instances,
3403 x_object_version_number);
3404 END LOOP;
3405
3406 EXCEPTION
3407 WHEN OTHERS THEN
3408 x_return_status := FND_API.G_RET_STS_ERROR;
3409 l_sqlerrno := to_char(SQLCODE);
3410 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3411 l_error_msg := 'Exception in CS_COUNTER_GRP_Post_Upd for counter_grp_id:' || TO_CHAR(p_ctr_grp_id)
3412 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3413 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_COUNTER_GRP_Post_Upd',FND_LOG.LEVEL_EXCEPTION);
3414 END CS_COUNTER_GRP_Post_Upd;
3415
3416 PROCEDURE CS_COUNTERS_INSTANTIATE_POST(
3417 P_Api_Version IN NUMBER,
3418 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3419 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3420 X_Return_Status OUT NOCOPY VARCHAR2,
3421 X_Msg_Count OUT NOCOPY NUMBER,
3422 X_Msg_Data OUT NOCOPY VARCHAR2,
3423 p_counter_group_id_template IN NUMBER,
3424 p_source_object_cd IN VARCHAR2,
3425 p_source_object_id IN NUMBER,
3426 x_ctr_grp_id_template IN NUMBER,
3427 p_ctr_grp_id IN NUMBER
3428 )
3429 IS
3430 l_object_version_number NUMBER;
3431 BEGIN
3432 x_return_status := FND_API.G_RET_STS_SUCCESS;
3433
3434 CS_CTR_GRP_INSTANCE_CRE_POST(
3435 P_Api_Version,
3436 P_Init_Msg_List,
3437 P_Commit ,
3438 X_Return_Status ,
3439 X_Msg_Count ,
3440 X_Msg_Data ,
3441 p_source_object_cd ,
3442 p_source_object_id ,
3443 p_ctr_grp_id ,
3444 l_object_version_number
3445 );
3446 EXCEPTION
3447 --log the error
3448 WHEN OTHERS THEN
3449 csm_util_pkg.log('ERROR: COUNTER_GROUP_ID => ' || to_char(p_ctr_grp_id));
3450 END CS_COUNTERS_INSTANTIATE_POST;
3451
3452
3453
3454 /*------------
3455 Check if the counter_id is a mobile valid id. If not return.
3456 If yes, do update
3457 ------------*/
3458 Procedure CS_Counter_Pre_Upd(
3459 P_Api_Version IN NUMBER,
3460 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3461 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3462 X_Return_Status OUT NOCOPY VARCHAR2,
3463 X_Msg_Count OUT NOCOPY NUMBER,
3464 X_Msg_Data OUT NOCOPY VARCHAR2,
3465 p_counter_id IN NUMBER,
3466 p_object_version_number IN NUMBER,
3467 p_cascade_upd_to_instances IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3468 x_object_version_number OUT NOCOPY NUMBER
3469 -- p_user_id in number,
3470 -- p_cs_counters cs_counters%rowtype
3471 )
3472 IS
3473
3474 /* CURSOR l_acc_del_csr(b_counter_id NUMBER) IS
3475 SELECT acc.user_id
3476 FROM CS_COUNTERS counters
3477 , CS_COUNTER_GROUPS counter_groups
3478 , csm_item_instances_acc acc
3479 WHERE counters.counter_group_id = counter_groups.counter_group_id
3480 AND counter_groups.source_object_code = 'CP'
3481 AND SYSDATE NOT BETWEEN NVL(counters.start_date_active, SYSDATE) AND NVL(counters.end_date_active, SYSDATE)
3482 and acc.instance_id = counter_groups.source_object_id
3483 and counters.counter_id = b_counter_id;
3484
3485 CURSOR l_acc_upd_csr(b_counter_id NUMBER) IS
3486 SELECT acc.user_id
3487 FROM CS_COUNTERS counters
3488 , CS_COUNTER_GROUPS counter_groups
3489 , csm_item_instances_acc acc
3490 WHERE counters.counter_group_id = counter_groups.counter_group_id
3491 AND counter_groups.source_object_code = 'CP'
3492 AND SYSDATE BETWEEN NVL(counters.start_date_active, SYSDATE) AND NVL(counters.end_date_active, SYSDATE)
3493 and acc.instance_id = counter_groups.source_object_id
3494 and counters.counter_id = b_counter_id;
3495
3496 l_acc_rec l_acc_upd_csr%ROWTYPE;
3497 */
3498 CURSOR l_seq_val_csr IS
3499 SELECT CSM_ACTIVITY_SEQ.nextval
3500 FROM dual;
3501
3502 BEGIN
3503 -- csm_util_pkg.log('csm_wf_pkg.cs_counter_pre_upd eneterd');
3504 x_return_status := FND_API.G_RET_STS_SUCCESS;
3505
3506 EXCEPTION
3507 --log the error
3508 WHEN OTHERS THEN
3509 csm_util_pkg.log('ERROR: COUNTER_ID => ' || to_char(p_counter_id));
3510 END CS_Counter_Pre_Upd;
3511
3512 PROCEDURE CS_Counter_Post_Upd(
3513 P_Api_Version IN NUMBER,
3514 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3515 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3516 X_Return_Status OUT NOCOPY VARCHAR2,
3517 X_Msg_Count OUT NOCOPY NUMBER,
3518 X_Msg_Data OUT NOCOPY VARCHAR2,
3519 p_counter_id IN NUMBER,
3520 p_object_version_number IN NUMBER,
3521 p_cascade_upd_to_instances IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3522 x_object_version_number OUT NOCOPY NUMBER)
3523 IS
3524 l_sqlerrno VARCHAR2(20);
3525 l_sqlerrmsg VARCHAR2(4000);
3526 l_error_msg VARCHAR2(4000);
3527 l_return_status VARCHAR2(2000);
3528
3529 CURSOR l_acc_csr(b_counter_id NUMBER) IS
3530 SELECT COUNT(1)
3531 FROM CS_COUNTERS counters
3532 , CS_COUNTER_GROUPS counter_groups
3533 , csm_item_instances_acc acc
3534 WHERE counters.counter_group_id = counter_groups.counter_group_id
3535 AND counter_groups.source_object_code = 'CP'
3536 AND acc.instance_id = counter_groups.source_object_id
3537 AND counters.counter_id = b_counter_id;
3538
3539 l_acc_cnt NUMBER;
3540
3541 BEGIN
3542 x_return_status := FND_API.G_RET_STS_SUCCESS;
3543
3544 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3545 RETURN;
3546 END IF;
3547
3548 -- check if the p_counter_id is belongs to a mobile item_instance.
3549 -- If not, return right here.
3550 OPEN l_acc_csr(p_counter_id);
3551 FETCH l_acc_csr INTO l_acc_cnt;
3552 IF l_acc_cnt = 0 THEN
3553 CLOSE l_acc_csr;
3554 RETURN;
3555 END IF;
3556 CLOSE l_acc_csr;
3557
3558 csm_counter_event_pkg.CTR_MAKE_DIRTY_U_FOREACHUSER(p_counter_id, l_error_msg, l_return_status);
3559 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3560 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Post_Upd', FND_LOG.LEVEL_ERROR);
3561 END IF;
3562
3563 EXCEPTION
3564 WHEN OTHERS THEN
3565 x_return_status := FND_API.G_RET_STS_ERROR;
3566 l_sqlerrno := to_char(SQLCODE);
3567 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3568 l_error_msg := 'Exception in CS_Counter_Post_Upd for counter_id:' || TO_CHAR(p_counter_id)
3569 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3570 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Post_Upd',FND_LOG.LEVEL_EXCEPTION);
3571 END CS_Counter_Post_Upd;
3572
3573
3574 /********************************************************
3575 Starts the COUNTER_VALUE_INS_USERLOOP workflow. Should be called when new
3576 counter value is added
3577
3578 Arguments:
3579 p_counter_value_id: COUNTER_VALUE_ID corresponding to the new counter
3580 value added
3581 *********************************************************/
3582 Procedure CS_Counter_Value_Post_Ins(p_api_version IN NUMBER
3583 , p_init_msg_list IN VARCHAR2
3584 , p_commit IN VARCHAR2
3585 , p_validation_level IN NUMBER
3586 , p_counter_grp_log_id IN NUMBER
3587 , x_return_status OUT NOCOPY VARCHAR2
3588 , x_msg_count OUT NOCOPY NUMBER
3589 , x_msg_data OUT NOCOPY VARCHAR2)
3590 IS
3591 l_counter_grp_log_id cs_counter_grp_log.counter_grp_log_id%TYPE;
3592 l_sqlerrno VARCHAR2(20);
3593 l_sqlerrmsg VARCHAR2(4000);
3594 l_error_msg VARCHAR2(4000);
3595 l_return_status VARCHAR2(2000);
3596
3597 BEGIN
3598 x_return_status := FND_API.G_RET_STS_SUCCESS;
3599
3600 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3601 RETURN;
3602 END IF;
3603
3604 l_counter_grp_log_id := p_counter_grp_log_id;
3605
3606 csm_counter_event_pkg.CTR_VAL_MAKE_DIRTY_FOREACHUSER(l_counter_grp_log_id, l_error_msg, l_return_status);
3607 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3608 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Value_Post_Ins', FND_LOG.LEVEL_ERROR);
3609 END IF;
3610
3611 EXCEPTION
3612 WHEN OTHERS THEN
3613 x_return_status := FND_API.G_RET_STS_ERROR;
3614 l_sqlerrno := to_char(SQLCODE);
3615 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3616 l_error_msg := 'Exception in CS_Counter_Value_Post_Ins for counter_grp_log_id:' || TO_CHAR(l_counter_grp_log_id)
3617 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3618 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Value_Post_Ins',FND_LOG.LEVEL_EXCEPTION);
3619 END CS_Counter_Value_Post_Ins;
3620
3621 /********************************************************
3622 Starts the COUNTER_VALUE_UPD_USERLOOP workflow. Should be called when new
3623 counter value is updated
3624
3625 Arguments:
3626 *********************************************************/
3627 PROCEDURE CS_Counter_Value_Pre_Upd(p_api_version IN NUMBER
3628 , p_init_msg_list IN VARCHAR2
3629 , p_commit IN VARCHAR2
3630 , p_validation_level IN NUMBER
3631 , p_counter_grp_log_id IN NUMBER
3632 , p_object_version_number IN NUMBER
3633 , x_return_status OUT NOCOPY VARCHAR2
3634 , x_msg_count OUT NOCOPY NUMBER
3635 , x_msg_data OUT NOCOPY VARCHAR2 )
3636 IS
3637 l_counter_grp_log_id cs_counter_grp_log.counter_grp_log_id%TYPE;
3638 l_sqlerrno VARCHAR2(20);
3639 l_sqlerrmsg VARCHAR2(4000);
3640 l_error_msg VARCHAR2(4000);
3641 l_return_status VARCHAR2(2000);
3642
3643 BEGIN
3644 x_return_status := FND_API.G_RET_STS_SUCCESS;
3645
3646 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
3647 RETURN;
3648 END IF;
3649
3650 l_counter_grp_log_id := p_counter_grp_log_id;
3651
3652 csm_counter_event_pkg.CTR_VAL_MAKE_DIRTY_FOREACHUSER(l_counter_grp_log_id, l_error_msg, l_return_status);
3653 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3654 csm_util_pkg.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Value_Pre_Upd', FND_LOG.LEVEL_ERROR);
3655 END IF;
3656
3657 EXCEPTION
3658 WHEN OTHERS THEN
3659 x_return_status := FND_API.G_RET_STS_ERROR;
3660 l_sqlerrno := to_char(SQLCODE);
3661 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3662 l_error_msg := 'Exception in CS_Counter_Value_Pre_Upd for counter_grp_log_id:' || TO_CHAR(l_counter_grp_log_id)
3663 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3664 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_WF_PKG.CS_Counter_Value_Pre_Upd',FND_LOG.LEVEL_EXCEPTION);
3665 END CS_Counter_Value_Pre_Upd;
3666
3667 Procedure CS_Counter_Property_Post_Ins(p_user_id in number,
3668 p_cs_counter_prop cs_counter_properties%rowtype)
3669 IS
3670 BEGIN
3671 null;
3672 END;
3673
3674 Procedure CS_Counter_Property_Pre_Upd(p_user_id in number,
3675 p_cs_counter_prop cs_counter_properties%rowtype)
3676 IS
3677 BEGIN
3678 null;
3679 END;
3680
3681 Procedure CS_Counter_Prop_Val_Post_Ins(p_user_id in number,
3682 p_cs_counter_prop_val cs_counter_prop_values%rowtype)
3683 IS
3684 BEGIN
3685 null;
3686 END;
3687
3688 Procedure CS_Counter_Prop_Val_Pre_Upd(p_user_id in number,
3689 p_cs_counter_prop_val cs_counter_prop_values%rowtype)
3690 IS
3691 BEGIN
3692 null;
3693 END;
3694
3695 /*--------------------------------------------------------
3696 Description:
3697 It starts the USER_DEL workflow process.
3698 Called when a Field Service Palm user is deleted
3699 Parameter(s):
3700 User_ID
3701 --------------------------------------------------------*/
3702 Procedure User_Del(p_user_id IN NUMBER)
3703 IS
3704 l_sqlerrno VARCHAR2(20);
3705 l_sqlerrmsg VARCHAR2(4000);
3706 l_error_msg VARCHAR2(4000);
3707 l_return_status VARCHAR2(2000);
3708
3709 BEGIN
3710 CSM_UTIL_PKG.LOG('Entering User_Del for user_id: ' || p_user_id, 'csm_wf_pkg.User_Del',FND_LOG.LEVEL_PROCEDURE);
3711
3712 csm_user_event_pkg.user_del_init(p_user_id=>p_user_id);
3713
3714 CSM_UTIL_PKG.LOG('Leaving User_Del for user_id: ' || p_user_id, 'csm_wf_pkg.User_Del',FND_LOG.LEVEL_PROCEDURE);
3715 EXCEPTION
3716 WHEN OTHERS THEN
3717 l_sqlerrno := to_char(SQLCODE);
3718 l_sqlerrmsg := substr(SQLERRM, 1,2000);
3719 l_error_msg := ' Exception in User_Del for user_id:'
3720 || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
3721 CSM_UTIL_PKG.LOG(l_error_msg, 'csm_wf_pkg.User_Del',FND_LOG.LEVEL_EXCEPTION);
3722 RAISE;
3723 END User_Del;
3724
3725 PROCEDURE RAISE_START_AUTO_SYNC_EVENT(l_entity VARCHAR2 , l_pk_value VARCHAR2, l_mode VARCHAR2)
3726 IS
3727 l_wf_param wf_event_t;
3728 l_e NUMBER:=-100;
3729
3730 CURSOR c_access(b_task_assignment_id NUMBER)
3731 IS
3732 SELECT ACCESS_ID
3733 FROM CSM_TASK_ASSIGNMENTS_ACC
3734 WHERE TASK_ASSIGNMENT_ID=b_task_assignment_id;
3735
3736 BEGIN
3737
3738 /*FS creates dummy task in jtf_task_assignments_all that needs to be skipped*/
3739 IF l_entity='CSM_TASK_ASSIGNMENTS' AND l_mode = 'NEW' THEN
3740 open c_access(to_number(l_pk_value));
3741 fetch c_access into l_e;
3742 close c_access;
3743
3744 IF l_e is NULL OR (l_e < 0) THEN
3745 CSM_UTIL_PKG.LOG('Returning since Task Assignment Id '||l_pk_value ||' is not downloaded to any mfs user',
3746 'CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT',FND_LOG.LEVEL_PROCEDURE);
3747 RETURN;
3748 END IF;
3749 END IF;
3750
3751 --Notify user with Start Sync Event
3752 wf_event_t.initialize(l_wf_param);
3753 l_wf_param.AddParameterToList('ENTITY',l_entity);
3754 l_wf_param.AddParameterToList('PK_VALUE',l_pk_value);
3755 l_wf_param.AddParameterToList('MODE',l_mode);
3756 -- csm_notification_event_pkg.notify_user(l_wf_param);
3757 wf_event.raise(p_event_name=>'oracle.apps.csm.download.startsync',
3758 p_event_key=>l_pk_value,p_parameters=>l_wf_param.getParameterList,
3759 p_event_data=>null,p_send_date=>null);
3760
3761 CSM_UTIL_PKG.LOG('Raised auto sync event for : '||l_entity||'-'||l_pk_value||'-'||l_mode, 'CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT',FND_LOG.LEVEL_PROCEDURE);
3762 EXCEPTION
3763 WHEN Others THEN
3764 CSM_UTIL_PKG.LOG('Returning with exception:'||substr(SQLERRM, 1,2000),'CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT',FND_LOG.LEVEL_PROCEDURE);
3765 END RAISE_START_AUTO_SYNC_EVENT;
3766
3767 END CSM_WF_PKG;