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