[Home] [Help]
PACKAGE BODY: APPS.CSF_ACCESS_PKG
Source
1 PACKAGE BODY CSF_ACCESS_PKG AS
2 /* $Header: csfvaccb.pls 115.3.1157.1 2002/03/22 17:21:08 pkm ship $ */
3
4 PROCEDURE RUN_COMMAND
5 ( p_command IN VARCHAR2
6 )
7 is
8 /********************************************************
9 Name:
10 Run_Command
11
12 Purpose:
13 Execute a dynamic SQL command.
14
15 Arguments:
16 p_command The dynamic SQL command to be executed.
17
18 Known Limitations:
19
20 Notes:
21
22 History:
23 ??-???-???? ? Created
24
25 ********************************************************/
26 v_cursor_id INTEGER;
27 v_dummy INTEGER;
28 v_sqlcode NUMBER;
29 BEGIN
30 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
31 DBMS_SQL.PARSE( v_cursor_id, p_command, DBMS_SQL.V7 );
32 v_dummy := DBMS_SQL.EXECUTE( v_cursor_id );
33 DBMS_SQL.CLOSE_CURSOR( v_cursor_id );
34 EXCEPTION
35 WHEN OTHERS
36 THEN
37 v_sqlcode := SQLCODE;
38 DBMS_SQL.CLOSE_CURSOR( v_cursor_id );
39 RAISE;
40 END RUN_COMMAND;
41
42 FUNCTION IsExisting
43 ( x_acc IN VARCHAR2
44 , x_pk IN VARCHAR2
45 , x_pk_id IN NUMBER
46 , x_server_id IN NUMBER
47 ) RETURN NUMBER
48 IS
49 /********************************************************
50 Name:
51 IsExisting
52
53 Purpose:
54 Check whether access record exists and return the
55 number of users interested in this access record.
56
57 Arguments:
58 x_acc Name of the access table.
59 x_pk Name of the PK column.
60 x_pk_id Value of the PK.
61 x_server_id Id of the MDG server.
62
63 Known Limitations:
64
65 Notes:
66
67 History:
68 ??-???-???? ? Created
69
70 ********************************************************/
71 v_id NUMBER;
72 v_dummy NUMBER;
73 v_statement VARCHAR2(1000);
74 v_counter NUMBER;
75 BEGIN
76 v_counter := 0;
77 v_statement := 'select counter'
78 ||' from '||x_acc
79 ||' where '||x_pk||' = '||x_pk_id
80 ||' and server_id = '||x_server_id;
81 v_id := DBMS_SQL.OPEN_CURSOR;
82
83 DBMS_SQL.PARSE
84 ( v_id
85 , v_statement
86 , DBMS_SQL.V7
87 );
88
89 DBMS_SQL.DEFINE_COLUMN
90 ( v_id
91 , 1
92 , v_counter
93 );
94
95 v_dummy := DBMS_SQL.EXECUTE(v_id);
96
97 IF DBMS_SQL.FETCH_ROWS(v_id) <> 0
98 THEN
99 DBMS_SQL.COLUMN_VALUE(v_id, 1, v_counter);
100 END IF;
101 DBMS_SQL.CLOSE_CURSOR(v_id);
102
103 RETURN v_counter;
104 END IsExisting;
105
106 PROCEDURE InsertAcc
107 ( x_acc IN VARCHAR2
108 , x_pk IN VARCHAR2
109 , x_pk_id IN NUMBER
110 , x_server_id IN NUMBER
111 )
112 IS
113 /********************************************************
114 Name:
115 InsertAcc
116
117 Purpose:
118 Insert access record.
119
120 Arguments:
121 x_acc Name of the access table.
122 x_pk Name of the PK column.
123 x_pk_id Value of the PK.
124 x_server_id Id of the MDG server.
125
126 Known Limitations:
127
128 Notes:
129
130 History:
131 ??-???-???? ? Created
132
133 ********************************************************/
134 v_statement varchar2(1000);
135 BEGIN
136 v_statement := 'insert into '||x_acc
137 ||'('||x_pk||',server_id, last_update_date, last_updated_by, '
138 ||'creation_date, created_by, counter) '
139 ||'values('||x_pk_id||','||x_server_id
140 ||',sysdate,1,sysdate,1,1)';
141 RUN_COMMAND
142 ( p_command => v_statement
143 );
144 END InsertAcc;
145
146 FUNCTION UpdateAcc
147 ( x_acc IN VARCHAR2
148 , x_pk IN VARCHAR2
149 , x_pk_id IN NUMBER
150 , x_server_id IN NUMBER
151 , x_op IN VARCHAR2
152 ) RETURN NUMBER
153 IS
154 /********************************************************
155 Name:
156 UpdateAcc
157
158 Purpose:
159 Update the access record. If the access record does
160 not exist, insert the access record, else increase the
161 counter. If it is a deletion, decrease the counter. If
162 last record, delete the access record.
163
164 Return-value 0 means the record has been inserted.
165 Return-value 1 means the record has been updated.
166
167 Arguments:
168 x_acc Name of the access table.
169 x_pk Name of the PK column.
170 x_pk_id Value of the PK.
171 x_server_id Id of the MDG server.
172 x_op Operation to be performed: '+' for
173 increase, '-' for decrease.
174
175 Known Limitations:
176
177 Notes:
178
179 History:
180 ??-???-???? ? Created
181
182 ********************************************************/
183 v_id NUMBER;
184 v_dummy NUMBER;
185 v_statement VARCHAR2(1000);
186 v_return NUMBER;
187 v_counter NUMBER;
188 BEGIN
189 v_return := 0;
190 v_counter := IsExisting
191 ( x_acc => x_acc
192 , x_pk => x_pk
193 , x_pk_id => x_pk_id
194 , x_server_id => x_server_id
195 );
196
197 IF v_counter = 0
198 THEN
199 IF x_op = '+'
200 THEN
201 InsertAcc
202 ( x_acc => x_acc
203 , x_pk => x_pk
204 , x_pk_id => x_pk_id
205 , x_server_id => x_server_id
206 );
207 END IF;
208 RETURN 0;
209 END IF;
210
211 IF x_op = '+'
212 THEN
213 v_counter := v_counter + 1;
214 ELSIF x_op = '-'
215 THEN
216 v_counter := v_counter - 1;
217 END IF;
218
219 IF v_counter > 0
220 THEN
221 v_statement := 'update '||x_acc
222 ||' set counter = '||v_counter
223 ||' where '||x_pk||'='||x_pk_id
224 || ' and server_id ='||x_server_id;
225 ELSE
226 v_statement := 'delete from '||x_acc
227 ||' where '||x_pk||'='||x_pk_id
228 ||' and server_id ='||x_server_id;
229 END IF;
230
231 RUN_COMMAND
232 ( p_command => v_statement
233 );
234
235 RETURN 0;
236 END UpdateAcc;
237
238 Function IsMobileUser
239 ( x_resource_id IN NUMBER
240 ) RETURN NUMBER
241 IS
242 /********************************************************
243 Name:
244 IsMobileUser
245
246 Purpose:
247 Check if it is a mobile user. Return 0 if it's not,
248 return 1 if it is.
249
250 Arguments:
251 x_resource_id The resource_id of the user to be
252 checked.
253
254 Known Limitations:
255
256 Notes:
257
258 History:
259 ??-???-???? ? Created
260
261 ********************************************************/
262 CURSOR c_mobile_user
263 ( x_resource_id NUMBER
264 )
265 IS
266 SELECT 1
267 FROM asg_device_users
268 WHERE resource_id = x_resource_id;
269
270 v_dummy NUMBER;
271
272 BEGIN
273 OPEN c_mobile_user
274 ( x_resource_id => x_resource_id
275 );
276 FETCH c_mobile_user
277 into v_dummy;
278 IF c_mobile_user%NOTFOUND
279 THEN
280 CLOSE c_mobile_user;
281 RETURN 0;
282 END IF;
283
284 CLOSE c_mobile_user;
285 RETURN 1;
286
287 END IsMobileUser;
288
289 Function GetServerId
290 ( x_resource_id IN NUMBER
291 , x_server_id OUT NUMBER
292 ) RETURN NUMBER
293 IS
294 /********************************************************
295 Name:
296 GetServerId
297
298 Purpose:
299 Get Server id of mobile user.
300
301 Return-value -1 means server_id not found.
302 Return-value 0 means server_id found.
303
304 Arguments:
305 x_resource_id The resource_id of the user to be
306 checked.
307 x_server_id The retrieved server_id (if found).
308
309 Known Limitations:
310
311 Notes:
312
313 History:
314 ??-???-???? ? Created
315
316 ********************************************************/
317 CURSOR c_server_id
318 ( x_resource_id NUMBER
319 )
320 IS
321 SELECT server_id
322 FROM asg_server_resources
323 WHERE resource_id = x_resource_id;
324
325 BEGIN
326 OPEN c_server_id
327 ( x_resource_id => x_resource_id
328 );
329 FETCH c_server_id
330 INTO x_server_id;
331 IF c_server_id%NOTFOUND
332 THEN
333 CLOSE c_server_id;
334 RETURN -1;
335 END IF;
336
337 CLOSE c_server_id;
338 RETURN 0;
339 END GetServerId;
340
341 Procedure UpdateAccesses_Partyid
342 ( x_party_id IN NUMBER
343 , x_server_id IN NUMBER
344 , x_op IN VARCHAR2
345 )
346 IS
347 /********************************************************
348 Name:
349 UpdateAccesses_Partyid
350
351 Purpose:
352 Update Service Access records based on party_id.
353
354 Arguments:
355 x_party_id The id of the party for which the access
356 record must be updated.
357 x_server_id Id of the MDG server.
358 x_op Operation to be performed: '+' for
359 increase, '-' for decrease.
360
361 Known Limitations:
362
363 Notes:
364
365 History:
366 ??-???-???? ? Created
367
368 ********************************************************/
369 v_ret NUMBER;
370 BEGIN
371 -- Update access table for this party_id
372 v_ret := UpdateAcc
373 ( x_acc => 'ASG_PARTY_ACC'
374 , x_pk => 'PARTY_ID'
375 , x_pk_id => x_party_id
376 , x_server_id => x_server_id
377 , x_op => x_op
378 );
379 END UpdateAccesses_Partyid;
380
381 Procedure UpdateAccesses_Incidentid
382 ( x_incident_id IN NUMBER
383 , x_server_id IN NUMBER
384 , x_op IN VARCHAR2
385 )
386 IS
387 /********************************************************
388 Name:
389 UpdateAccesses_Incidentid
390
391 Purpose:
392 Update Service Access records based on incident_id.
393
394 Arguments:
395 x_incident_id The id of the incident for which the
396 access record must be updated.
397 x_server_id Id of the MDG server.
398 x_op Operation to be performed: '+' for
399 increase, '-' for decrease.
400
401 Known Limitations:
402
403 Notes:
404
405 History:
406 ??-???-???? ? Created
407 29-OCT-2001 MRAAP Added cursor to retrieve
408 party_id of Installed At address
409 of Task. This is needed, because
410 this customer may differ from
411 the Service Request customer.
412
413 This is part of the fix for
414 bug 1931013.
415 ********************************************************/
416 CURSOR c_customer
417 ( x_incident_id NUMBER
418 )
419 IS
420 SELECT customer_id
421 FROM cs_incidents_all_b
422 WHERE incident_id = x_incident_id;
423
424 CURSOR c_primary_contact
425 ( x_incident_id NUMBER
426 )
427 IS
428 SELECT party_id
429 FROM cs_hz_sr_contact_points contact
430 WHERE contact.incident_id = x_incident_id
431 AND contact.primary_flag = 'Y';
432
433 CURSOR c_installed_at_party
434 ( x_incident_id NUMBER
435 )
436 IS
437 SELECT hps.party_id
438 FROM hz_party_sites hps
439 , jtf_tasks_b jt_b
440 WHERE jt_b.source_object_id = x_incident_id
441 AND jt_b.source_object_type_code = 'SR'
442 AND jt_b.address_id = hps.party_site_id;
443
444 v_party_id NUMBER;
445 v_ret NUMBER;
446 BEGIN
447 -- Update access table for this incident_id
448 v_ret := UpdateAcc
449 ( x_acc => 'ASG_INCIDENT_ACC'
450 , x_pk => 'INCIDENT_ID'
451 , x_pk_id => x_incident_id
452 , x_server_id => x_server_id
453 , x_op => x_op
454 );
455
456 -- Find corresponding Customer party_id
457 OPEN c_customer
458 ( x_incident_id => x_incident_id
459 );
460 FETCH c_customer
461 INTO v_party_id;
462 IF c_customer%NOTFOUND
463 OR v_party_id IS NULL
464 THEN
465 CLOSE c_customer;
466 ELSE
467 -- Call to update access table for this party_id
468 UpdateAccesses_Partyid
469 ( x_party_id => v_party_id
470 , x_server_id => x_server_id
471 , x_op => x_op
472 );
473 CLOSE c_customer;
474 END IF;
475
476 -- Find corresponding Primary Contact party_id
477 OPEN c_primary_contact
478 ( x_incident_id => x_incident_id
479 );
480 FETCH c_primary_contact
481 INTO v_party_id;
482 IF c_primary_contact%NOTFOUND
483 OR v_party_id IS NULL
484 THEN
485 CLOSE c_primary_contact;
486 ELSE
487 -- Call to update access table for this party_id
488 UpdateAccesses_Partyid
489 ( x_party_id => v_party_id
490 , x_server_id => x_server_id
491 , x_op => x_op
492 );
493 CLOSE c_primary_contact;
494 END IF;
495
496 -- Find corresponding Installed At party_id
497 OPEN c_installed_at_party
498 ( x_incident_id
499 );
500 FETCH c_installed_at_party
501 INTO v_party_id;
502 IF c_installed_at_party%NOTFOUND
503 OR v_party_id IS NULL
504 THEN
505 CLOSE c_installed_at_party;
506 ELSE
507 -- Call to update access table for this party_id
508 UpdateAccesses_Partyid
509 ( x_party_id => v_party_id
510 , x_server_id => x_server_id
511 , x_op => x_op
512 );
513 CLOSE c_installed_at_party;
514 END IF;
515
516 END UpdateAccesses_Incidentid;
517
518 Procedure UpdateAccesses_Taskid
519 ( x_task_id IN NUMBER
520 , x_server_id IN NUMBER
521 , x_op IN VARCHAR2
522 )
523 IS
524 /********************************************************
525 Name:
526 UpdateAccesses_Taskid
527
528 Purpose:
529 Update Service Access records based on task_id.
530
531 Determine if the task needs to be replicated to the
532 mobile client, according to the following conditions:
533
534 - the task is not deleted (deleted_flag is not 'Y')
535 - the type of the task is 'Dispatch' or the task is
536 private or the task is an departure or arrival
537 task.
538
539 If the conditions apply, proceed with inserting/updating
540 the access record in ASG_TASK_ACC and make a call to
541 see if the Service Request related to the task needs
542 to be replicated as well.
543
544 Arguments:
545 x_task_id The id of the task for which the access
546 record must be updated.
547 x_server_id Id of the MDG server.
548 x_op Operation to be performed: '+' for
549 increase, '-' for decrease.
550
551 Known Limitations:
552
553 Notes:
554
555 History:
556 ??-???-???? ? Created
557 16-OCT-2001 MRAAP Modified WHERE-clause of cursor
558 c_task to include task with
559 type_id = 21 (arrival task).
560 This is a fix for bug 2055402.
561
562 ********************************************************/
563 CURSOR c_service_req
564 ( x_task_id NUMBER
565 )
566 IS
567 SELECT source_object_id
568 FROM jtf_tasks_b
569 WHERE source_object_type_code = 'SR'
570 AND task_id = x_task_id;
571
572 CURSOR c_task
573 ( x_task_id NUMBER
574 )
575 IS
576 SELECT task_id
577 FROM jtf_tasks_b jt_b
578 , jtf_task_types_b jtt_b
579 WHERE jt_b.task_id = x_task_id
580 AND jt_b.task_type_id = jtt_b.task_type_id
581 AND NVL(jt_b.deleted_flag, 'N') <> 'Y'
582 AND ( jtt_b.rule = 'DISPATCH'
583 OR jt_b.private_flag = 'Y'
584 OR jt_b.task_type_id IN (20, 21)
585 );
586
587 v_incident_id NUMBER;
588 v_task_id NUMBER;
589
590 v_ret NUMBER;
591 BEGIN
592 OPEN c_task
593 ( x_task_id => x_task_id
594 );
595 FETCH c_task
596 INTO v_task_id;
597 IF (c_task%FOUND)
598 THEN
599 -- Update access table for this task_id
600 v_ret := UpdateAcc
601 ( x_acc => 'ASG_TASK_ACC'
602 , x_pk => 'TASK_ID'
603 , x_pk_id => x_task_id
604 , x_server_id => x_server_id
605 , x_op => x_op
606 );
607
608 -- Fetch corresponding Service Request incident_id
609 OPEN c_service_req
610 ( x_task_id => x_task_id
611 );
612 FETCH c_service_req
613 INTO v_incident_id;
614 IF c_service_req%NOTFOUND
615 THEN
616 CLOSE c_service_req;
617 ELSE
618 -- Call to update access table for this incident_id
619 UpdateAccesses_Incidentid
620 ( x_incident_id => v_incident_id
621 , x_server_id => x_server_id
622 , x_op => x_op
623 );
624 CLOSE c_service_req;
625
626 END IF;
627 END IF;
628
629 CLOSE c_task;
630
631 END UpdateAccesses_Taskid;
632
633 PROCEDURE UpdateMobileUserAcc
634 ( x_resource_id IN NUMBER
635 , x_server_id IN NUMBER
636 , x_op IN VARCHAR2
637 )
638 IS
639 /********************************************************
640 Name:
641 UpdateMobileUserAcc
642
643 Purpose:
644 Add/Delete all accesses related to a mobile User
645 This procedure is called from Create/Delete Mobile
646 User.
647
648 Arguments:
649 x_resource_id Resource_id of the mobile user.
650 x_server_id Id of the MDG server.
651 x_op Operation to be performed: '+' for
652 increase, '-' for decrease.
653
654 Known Limitations:
655
656 Notes:
657
658 History:
659 ??-???-???? ? Created
660
661 ********************************************************/
662 CURSOR c_tasks
663 ( x_resource_id number
664 )
665 IS
666 SELECT task_id
667 FROM jtf_task_assignments
668 WHERE resource_id = x_resource_id;
669
670 v_task_id NUMBER;
671
672 BEGIN
673 OPEN c_tasks
674 ( x_resource_id => x_resource_id
675 );
676 LOOP
677 FETCH c_tasks
678 INTO v_task_id;
679 EXIT WHEN c_tasks%NOTFOUND;
680 UpdateAccesses_Taskid
681 ( x_task_id => v_task_id
682 , x_server_id => x_Server_id
683 , x_op => x_op
684 );
685 END LOOP;
686 CLOSE c_tasks;
687
688 EXCEPTION
689 WHEN OTHERS
690 THEN
691 RAISE_APPLICATION_ERROR(-20000,'Mobile: Failed in updating ' || v_task_id);
692 END UpdateMobileUserAcc;
693
694 PROCEDURE INCIDENT_POST_INSERT
695 ( x_return_status OUT VARCHAR2
696 )
697 IS
698 BEGIN
699 x_return_status := 'S';
700 END INCIDENT_POST_INSERT;
701
702 PROCEDURE INCIDENT_PRE_UPDATE
703 ( x_return_status OUT VARCHAR2
704 )
705 IS
706 /********************************************************
707 Name:
708 INCIDENT_PRE_UPDATE
709
710 Purpose:
711 Retrieve more info about the incident (old and new values)
712 and call the sr-contact-trigger-handler.
713
714 Arguments:
715 x_return_status 'S' indicates successfull completion.
716 Any other value indicates an error.
717
718 Known Limitations:
719
720 Notes:
721
722 History:
723 ??-???-???? ? Created
724
725 ********************************************************/
726 CURSOR c_customer_id
727 ( b_incident_id NUMBER
728 )
729 IS
730 SELECT customer_id
731 FROM cs_incidents_all
732 WHERE incident_id = b_incident_id;
733
734 incident_id NUMBER;
735
736 o_customer_id NUMBER;
737 n_customer_id NUMBER;
738 BEGIN
739 incident_id := CS_ServiceRequest_Pvt.user_hooks_rec.Request_ID;
740 o_customer_id := CS_ServiceRequest_Pvt.user_hooks_rec.customer_id;
741
742 OPEN c_customer_id
743 ( b_incident_id => incident_id
744 );
745 FETCH c_customer_id
746 INTO o_customer_id;
747 CLOSE c_customer_id;
748
749 Incident_Trigger_Handler
750 ( incident_id => incident_id
751 , o_customer_id => o_customer_id
752 , n_customer_id => n_customer_id
753 , trigger_mode => 'ON-UPDATE'
754 );
755
756 Sr_Contact_Trigger_Handler
757 ( x_incident_id => incident_id
758 , x_op => '-'
759 );
760
761 x_return_status := 'S';
762
763 END INCIDENT_PRE_UPDATE;
764
765 PROCEDURE INCIDENT_POST_UPDATE
766 ( x_return_status OUT VARCHAR2
767 )
768 IS
769 /********************************************************
770 Name:
771 INCIDENT_POST_UPDATE
772
773 Purpose:
774 Retrieve the incident_id
775 and call the sr-contact-trigger-handler.
776
777 Arguments:
778 x_return_status 'S' indicates successfull completion.
779 Any other value indicates an error.
780
781 Known Limitations:
782
783 Notes:
784
785 History:
786 ??-???-???? ? Created
787
788 ********************************************************/
789 incident_id NUMBER;
790 BEGIN
791 incident_id := CS_ServiceRequest_Pvt.user_hooks_rec.Request_ID;
792
793 Sr_Contact_Trigger_Handler
794 ( x_incident_id => incident_id
795 , x_op => '+'
796 );
797
798 x_return_status := 'S';
799
800 END INCIDENT_POST_UPDATE;
801
802 PROCEDURE TASKS_POST_INSERT
803 ( x_return_status OUT VARCHAR2
804 )
805 IS
806 /********************************************************
807 Name:
808 TASKS_POST_INSERT
809
810 Purpose:
811 Retrieve more info about the task (new values)
812 and call the task-trigger-handler.
813
814 Arguments:
815 x_return_status 'S' indicates successfull completion.
816 Any other value indicates an error.
817
818 Known Limitations:
819
820 Notes:
821
822 History:
823 29-NOV-2001 MRAAP Created
824
825 ********************************************************/
826 n_task_id NUMBER;
827 n_source_object_id NUMBER;
828 n_source_object_name VARCHAR2(80);
829 n_source_object_type_code VARCHAR2(30);
830 BEGIN
831 n_task_id := Jtf_Tasks_Pub.p_task_user_hooks.Task_Id;
832 n_source_object_id := Jtf_Tasks_Pub.p_task_user_hooks.Source_Object_Id;
833 n_source_object_name := Jtf_Tasks_Pub.p_task_user_hooks.Source_Object_Name;
834 n_source_object_type_code := Jtf_Tasks_Pub.p_task_user_hooks.Source_Object_Type_Code;
835
836 Tasks_Trigger_Handler
837 ( o_task_id => NULL
838 , o_source_object_id => NULL
839 , o_source_object_name => NULL
840 , o_source_object_type_code => NULL
841 , n_task_id => n_task_id
842 , n_source_object_id => n_source_object_id
843 , n_source_object_name => n_source_object_name
844 , n_source_object_type_code => n_source_object_type_code
845 , trigger_mode => 'ON-INSERT'
846 );
847
848 x_return_status := 'S';
849 END TASKS_POST_INSERT;
850
851 PROCEDURE TASKS_PRE_UPDATE
852 ( x_return_status OUT VARCHAR2
853 )
854 IS
855 /********************************************************
856 Name:
857 TASKS_PRE_UPDATE
858
859 Purpose:
860 Retrieve more info about the task (old and new values)
861 and call the task-trigger-handler.
862
863 Arguments:
864 x_return_status 'S' indicates successfull completion.
865 Any other value indicates an error.
866
867 Known Limitations:
868
869 Notes:
870
871 History:
872 ??-???-???? ? Created
873
874 ********************************************************/
875 CURSOR c_task
876 ( b_task_id NUMBER
877 )
878 IS
879 SELECT source_object_id
880 , source_object_name
881 , source_object_type_code
882 FROM jtf_tasks_b
883 WHERE task_id = b_task_id;
884
885 task_id NUMBER;
886
887 n_source_object_id NUMBER;
888 o_source_object_id NUMBER;
889 n_source_object_name VARCHAR2(80);
890 o_source_object_name VARCHAR2(80);
891 n_source_object_type_code VARCHAR2(30);
892 o_source_object_type_code VARCHAR2(30);
893 BEGIN
894 task_id := Jtf_Tasks_Pub.p_task_user_hooks.Task_Id;
895 n_source_object_id := Jtf_Tasks_Pub.p_task_user_hooks.Source_Object_Id;
896 n_source_object_name := Jtf_Tasks_Pub.p_task_user_hooks.Source_Object_Name;
897 n_source_object_type_code := Jtf_Tasks_Pub.p_task_user_hooks.Source_Object_Type_Code;
898
899 OPEN c_task
900 ( b_task_id => task_id
901 );
902 FETCH c_task
903 INTO o_source_object_id
904 , o_source_object_name
905 , o_source_object_type_code;
906 CLOSE c_task;
907
908 Tasks_Trigger_Handler
909 ( o_task_id => task_id
910 , o_source_object_id => o_source_object_id
911 , o_source_object_name => o_source_object_name
912 , o_source_object_type_code => o_source_object_type_code
913 , n_task_id => task_id
914 , n_source_object_id => n_source_object_id
915 , n_source_object_name => n_source_object_name
916 , n_source_object_type_code => n_source_object_type_code
917 , trigger_mode => 'ON-UPDATE'
918 );
919
920 x_return_status := 'S';
921
922 END TASKS_PRE_UPDATE;
923
924 PROCEDURE TASKS_POST_UPDATE
925 ( x_return_status OUT VARCHAR2
926 )
927 IS
928 BEGIN
929 x_return_status := 'S';
930 END TASKS_POST_UPDATE;
931
932 PROCEDURE TASKS_PRE_DELETE
933 ( x_return_status OUT VARCHAR2
934 )
935 IS
936 BEGIN
937 x_return_status := 'S';
938 END TASKS_PRE_DELETE;
939
940 PROCEDURE TASK_ASSIGN_POST_INSERT
941 ( x_return_status OUT VARCHAR2
942 )
943 IS
944 /********************************************************
945 Name:
946 TASK_ASSIGN_POST_INSERT
947
948 Purpose:
949 Retrieve more info about the task_assignment (new values)
950 and call the task-assignment-trigger-handler.
951
952 Arguments:
953 x_return_status 'S' indicates successfull completion.
954 Any other value indicates an error.
955
956 Known Limitations:
957
958 Notes:
959
960 History:
961 ??-???-???? ? Created
962
963 ********************************************************/
964 n_task_assignment_id NUMBER;
965 n_task_id NUMBER;
966 n_resource_id NUMBER;
967 BEGIN
968 n_task_assignment_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Task_Assignment_Id;
969 n_task_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Task_Id;
970 n_resource_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Resource_Id;
971
972 Task_Assign_Trigger_Handler
973 ( o_task_assignment_id => n_task_assignment_id
974 , o_task_id => n_task_id
975 , o_resource_id => n_resource_id
976 , n_task_assignment_id => n_task_assignment_id
977 , n_task_id => n_task_id
978 , n_resource_id => n_resource_id
979 , Trigger_Mode => 'ON-INSERT'
980 );
981
982 x_return_status :='S';
983 END TASK_ASSIGN_POST_INSERT;
984
985 PROCEDURE TASK_ASSIGN_PRE_UPDATE
986 ( x_return_status OUT VARCHAR2
987 )
988 IS
989 /********************************************************
990 Name:
991 TASK_ASSIGN_PRE_UPDATE
992
993 Purpose:
994 Retrieve more info about the task_assignment (old and new values)
995 and call the task-assignment-trigger-handler.
996
997 Arguments:
998 x_return_status 'S' indicates successfull completion.
999 Any other value indicates an error.
1000
1001 Known Limitations:
1002
1003 Notes:
1004
1005 History:
1006 ??-???-???? ? Created
1007
1008 ********************************************************/
1009 CURSOR c_task_assign
1010 ( b_task_assignment_id NUMBER
1011 )
1012 IS
1013 SELECT task_id
1014 , resource_id
1015 FROM jtf_task_assignments
1016 WHERE task_assignment_id = b_task_assignment_id;
1017
1018 task_assignment_id NUMBER;
1019
1020 o_task_id NUMBER;
1021 n_task_id NUMBER;
1022 o_resource_id NUMBER;
1023 n_resource_id NUMBER;
1024 BEGIN
1025 task_assignment_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Task_Assignment_Id;
1026 n_task_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Task_Id;
1027 n_resource_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Resource_Id;
1028
1029 OPEN c_task_assign
1030 ( b_task_assignment_id => task_assignment_id
1031 );
1032 FETCH c_task_assign
1033 INTO o_task_id
1034 , o_resource_id;
1035 CLOSE c_task_assign;
1036
1037 Task_Assign_Trigger_Handler
1038 ( o_task_assignment_id => task_assignment_id
1039 , o_task_id => o_task_id
1040 , o_resource_id => o_resource_id
1041 , n_task_assignment_id => task_assignment_id
1042 , n_task_id => n_task_id
1043 , n_resource_id => n_resource_id
1044 , trigger_mode => 'ON-UPDATE'
1045 );
1046
1047 x_return_status := 'S';
1048 END TASK_ASSIGN_PRE_UPDATE;
1049
1050 PROCEDURE TASK_ASSIGN_POST_UPDATE
1051 ( x_return_status OUT VARCHAR2
1052 )
1053 IS
1054 BEGIN
1055 x_return_status := 'S';
1056 END TASK_ASSIGN_POST_UPDATE;
1057
1058 PROCEDURE TASK_ASSIGN_PRE_DELETE
1059 ( x_return_status OUT VARCHAR2
1060 )
1061 IS
1062 /********************************************************
1063 Name:
1064 TASK_ASSIGN_PRE_DELETE
1065
1066 Purpose:
1067 Retrieve more info about the task_assignment (old values)
1068 and call the task-assignment-trigger-handler.
1069
1070 Arguments:
1071 x_return_status 'S' indicates successfull completion.
1072 Any other value indicates an error.
1073
1074 Known Limitations:
1075
1076 Notes:
1077
1078 History:
1079 ??-???-???? ? Created
1080
1081 ********************************************************/
1082 CURSOR c_task_assign
1083 ( b_task_assignment_id NUMBER
1084 )
1085 IS
1086 SELECT task_id
1087 , resource_id
1088 FROM jtf_task_assignments jta
1089 WHERE task_assignment_id = b_task_assignment_id;
1090
1091 r_task_assign c_task_assign%ROWTYPE;
1092
1093 o_task_assignment_id NUMBER;
1094 o_task_id NUMBER;
1095 o_resource_id NUMBER;
1096 BEGIN
1097 o_task_assignment_id := Jtf_Task_Assignments_Pub.p_task_assignments_user_hooks.Task_Assignment_Id;
1098
1099 OPEN c_task_assign
1100 ( b_task_assignment_id => o_task_assignment_id
1101 );
1102 FETCH c_task_assign
1103 INTO r_task_assign;
1104 CLOSE c_task_assign;
1105
1106 o_task_id := r_task_assign.task_id;
1107 o_resource_id := r_task_assign.resource_id;
1108
1109 Task_Assign_Trigger_Handler
1110 ( o_task_assignment_id => o_task_assignment_id
1111 , o_task_id => o_task_id
1112 , o_resource_id => o_resource_id
1113 , n_task_assignment_id => o_task_assignment_id
1114 , n_task_id => o_task_id
1115 , n_resource_id => o_resource_id
1116 , trigger_mode => 'ON-DELETE'
1117 );
1118
1119 x_return_status := 'S';
1120 END TASK_ASSIGN_PRE_DELETE;
1121
1122 PROCEDURE CUST_RELATIONS_POST_INSERT
1123 ( x_return_status OUT VARCHAR2
1124 )
1125 IS
1126 /********************************************************
1127 Name:
1128 CUST_RELATIONS_POST_INSERT
1129
1130 Purpose:
1131 Retrieve more info about the ship_to_address(new values)
1132 and call the ship-to-address-trigger-handler.
1133
1134 Arguments:
1135 x_return_status 'S' indicates successfull completion.
1136 Any other value indicates an error.
1137
1138 Known Limitations:
1139
1140 Notes:
1141
1142 History:
1143 21-JAN-2002 ASOYKAN Created
1144
1145 ********************************************************/
1146 CURSOR c_cust_relations
1147 ( b_rs_cust_relation_id NUMBER
1148 )
1149 IS
1150 SELECT crcr.resource_id
1151 , hps.party_id
1152 FROM csp_rs_cust_relations crcr
1153 , hz_cust_acct_sites_all hcas_all
1154 , hz_party_sites hps
1155 WHERE crcr.customer_id = hcas_all.cust_account_id
1156 AND hcas_all.party_site_id = hps.party_site_id
1157 AND rs_cust_relation_id = b_rs_cust_relation_id
1158 AND crcr.resource_type = 'RS_EMPLOYEE';
1159
1160 r_cust_relations c_cust_relations%ROWTYPE;
1161
1162 rs_cust_relation_id csp_rs_cust_relations.rs_cust_relation_id%TYPE;
1163 BEGIN
1164 rs_cust_relation_id := csp_ship_to_address_pvt.g_rs_cust_relation_id;
1165
1166 OPEN c_cust_relations
1167 ( b_rs_cust_relation_id => rs_cust_relation_id
1168 );
1169 FETCH c_cust_relations
1170 INTO r_cust_relations;
1171 CLOSE c_cust_relations;
1172
1173 Cust_Relations_Trigger_Handler
1174 ( rs_cust_relation_id => rs_cust_relation_id
1175 , o_party_id => r_cust_relations.party_id
1176 , n_party_id => r_cust_relations.party_id
1177 , resource_id => r_cust_relations.resource_id
1178 , trigger_mode => 'ON-INSERT'
1179 );
1180
1181 x_return_status := 'S';
1182 END CUST_RELATIONS_POST_INSERT;
1183
1184 PROCEDURE INCIDENT_TRIGGER_HANDLER
1185 ( incident_id NUMBER
1186 , o_customer_id NUMBER
1187 , n_customer_id NUMBER
1188 , trigger_mode VARCHAR2
1189 )
1190 IS
1191 /********************************************************
1192 Name:
1193 INCIDENT_TRIGGER_HANDLER
1194
1195 Purpose:
1196 This procedure acts as a trigger on CS_INCIDENTS_ALL
1197 and is fired in case of insert, update or delete.
1198
1199 Arguments:
1200
1201
1202 Known Limitations:
1203
1204 Notes:
1205
1206 History:
1207 ??-???-???? ? Created
1208 ********************************************************/
1209 CURSOR c_incident
1210 ( v_incident_id NUMBER
1211 )
1212 IS
1213 SELECT server_id
1214 FROM asg_incident_acc
1215 WHERE incident_id = v_incident_id;
1216
1217 v_server_id NUMBER;
1218 BEGIN
1219 -- customer associated with incident is changed
1220 IF trigger_mode = 'ON-UPDATE'
1221 AND o_customer_id <> n_customer_id
1222 THEN
1223 OPEN c_incident
1224 ( v_incident_id => incident_id
1225 );
1226
1227 -- Find all the Middle-tiers associated with this SR.
1228 -- Remove the old Party associated with all these middle-tiers
1229 -- Add the new Party associated with all these middle-tiers
1230 LOOP
1231 FETCH c_incident
1232 INTO v_server_id;
1233 IF c_incident%NOTFOUND
1234 THEN
1235 EXIT;
1236 END IF;
1237
1238 -- Remove the old customer and add the new one
1239 UpdateAccesses_Partyid
1240 ( x_party_id => o_customer_id
1241 , x_server_id => v_server_id
1242 , x_op => '-'
1243 );
1244 UpdateAccesses_Partyid
1245 ( x_party_id => n_customer_id
1246 , x_server_id => v_server_id
1247 , x_op => '+'
1248 );
1249
1250 END LOOP;
1251 CLOSE c_incident;
1252 END IF;
1253 END INCIDENT_TRIGGER_HANDLER;
1254
1255 PROCEDURE TASKS_TRIGGER_HANDLER
1256 ( o_task_id NUMBER
1257 , o_source_object_id NUMBER
1258 , o_source_object_name VARCHAR2
1259 , o_source_object_type_code VARCHAR2
1260 , n_task_id NUMBER
1261 , n_source_object_id NUMBER
1262 , n_source_object_name VARCHAR2
1263 , n_source_object_type_code VARCHAR2
1264 , trigger_mode VARCHAR2
1265 )
1266 IS
1267 /********************************************************
1268 Name:
1269 TASKS_TRIGGER_HANDLER
1270
1271 Purpose:
1272 This procedure acts as a trigger on JTF_TASKS_B
1273 and is fired in case of insert, update or delete.
1274
1275 Arguments:
1276
1277
1278 Known Limitations:
1279
1280 Notes:
1281
1282 History:
1283 ??-???-???? ? Created
1284 ********************************************************/
1285 CURSOR c_task
1286 ( v_task_id NUMBER
1287 )
1288 IS
1289 SELECT server_id
1290 FROM asg_task_acc
1291 WHERE task_id = v_task_id;
1292
1293 v_server_id NUMBER;
1294
1295 BEGIN
1296 -- service request associated with task is changed
1297 IF trigger_mode = 'ON-UPDATE'
1298 AND o_source_object_id <> n_source_object_id
1299 THEN
1300 OPEN c_task
1301 ( v_task_id => n_task_id
1302 );
1303
1304 -- Find all the Middle-tiers associated with this task.
1305 -- Remove the old SR associated with all these middle-tiers
1306 -- Add the new SR associated with all these middle-tiers
1307 LOOP
1308 FETCH c_task
1309 INTO v_server_id;
1310 IF c_task%NOTFOUND
1311 THEN
1312 EXIT;
1313 END IF;
1314
1315 -- Remove the old incident and add the new one
1316 IF o_source_object_type_code = 'SR'
1317 THEN
1318 UpdateAccesses_Incidentid
1319 ( x_incident_id => o_source_object_id
1320 , x_server_id => v_server_id
1321 , x_op => '-'
1322 );
1323 END IF;
1324
1325 IF n_source_object_type_code = 'SR'
1326 THEN
1327 UpdateAccesses_Incidentid
1328 ( n_source_object_id
1329 , v_server_id
1330 , '+'
1331 );
1332 END IF;
1333
1334 END LOOP;
1335 CLOSE c_task;
1336 END IF;
1337 END TASKS_TRIGGER_HANDLER;
1338
1339 PROCEDURE TASK_ASSIGN_TRIGGER_HANDLER
1340 ( o_task_assignment_id NUMBER
1341 , o_task_id NUMBER
1342 , o_resource_id NUMBER
1343 , n_task_assignment_id NUMBER
1344 , n_task_id NUMBER
1345 , n_resource_id NUMBER
1346 , trigger_mode VARCHAR2
1347 )
1348 IS
1349 /********************************************************
1350 Name:
1351 TASK_ASSIGN_TRIGGER_HANDLER
1352
1353 Purpose:
1354 This procedure acts as a trigger on JTF_TASK_ASSIGNMENTS
1355 and is fired in case of insert, update or delete.
1356
1357 Arguments:
1358
1359
1360 Known Limitations:
1361
1362 Notes:
1363
1364 History:
1365 ??-???-???? ? Created
1366 ********************************************************/
1367 CURSOR c_device_users
1368 ( v_resource_id number
1369 )
1370 IS
1371 SELECT server_id
1372 FROM asg_server_resources
1373 WHERE resource_id = v_resource_id;
1374
1375 v_server_id NUMBER;
1376 v_old_server_id NUMBER;
1377
1378 BEGIN
1379
1380 IF trigger_mode = 'ON-INSERT'
1381 THEN
1382 -- Add this task to all the middle tiers for the resource
1383 OPEN c_device_users
1384 ( v_resource_id => n_resource_id
1385 );
1386 LOOP
1387 FETCH c_device_users
1388 INTO v_server_id;
1389 IF c_device_users%NOTFOUND
1390 THEN
1391 EXIT;
1392 END IF;
1393 UpdateAccesses_Taskid
1394 ( x_task_id => o_task_id
1395 , x_server_id => v_server_id
1396 , x_op => '+'
1397 );
1398 END LOOP;
1399 CLOSE c_device_users;
1400
1401 ELSIF trigger_mode = 'ON-UPDATE'
1402 THEN
1403 IF n_resource_id <> o_resource_id
1404 THEN
1405 -- Remove the task from all the middle tiers for this old resource
1406 OPEN c_device_users
1407 ( v_resource_id => o_resource_id
1408 );
1409 LOOP
1410 FETCH c_device_users
1411 into v_old_server_id;
1412 IF c_device_users%NOTFOUND
1413 THEN
1414 EXIT;
1415 END IF;
1416 UpdateAccesses_Taskid
1417 ( x_task_id => o_task_id
1418 , x_server_id => v_old_server_id
1419 , x_op => '-'
1420 );
1421 END LOOP;
1422 CLOSE c_device_users;
1423
1424 -- Add the task to all the middle tiers for this new resource
1425 OPEN c_device_users
1426 ( v_resource_id => n_resource_id
1427 );
1428 LOOP
1429 FETCH c_device_users
1430 INTO v_server_id;
1431 IF c_device_users%NOTFOUND
1432 THEN
1433 EXIT;
1434 END IF;
1435 UpdateAccesses_Taskid
1436 ( x_task_id => n_task_id
1437 , x_server_id => v_server_id
1438 , x_op => '+'
1439 );
1440 END LOOP;
1441 CLOSE c_device_users;
1442 END IF;
1443
1444 ELSIF Trigger_Mode = 'ON-DELETE'
1445 THEN
1446 -- Delete this task from all the middle tiers for this resource
1447 OPEN c_device_users
1448 ( v_resource_id => o_resource_id
1449 );
1450 LOOP
1451 FETCH C_DEVICE_USERS
1452 INTO v_old_server_id;
1453 IF c_device_users%NOTFOUND
1454 THEN
1455 EXIT;
1456 END IF;
1457 UpdateAccesses_Taskid
1458 ( x_task_id => o_task_id
1459 , x_server_id => v_old_server_id
1460 , x_op => '-'
1461 );
1462 END LOOP;
1463 CLOSE c_device_users;
1464 END IF;
1465 END TASK_ASSIGN_TRIGGER_HANDLER;
1466
1467 PROCEDURE SR_CONTACT_TRIGGER_HANDLER
1468 ( x_incident_id NUMBER
1469 , x_op VARCHAR2
1470 )
1471 IS
1472 /********************************************************
1473 Name:
1474 SR_CONTACT_TRIGGER_HANDLER
1475
1476 Purpose:
1477 This procedure acts as a trigger on ???
1478 and is fired in case of insert, update or delete.
1479
1480 Arguments:
1481
1482
1483 Known Limitations:
1484
1485 Notes:
1486
1487 History:
1488 ??-???-???? ? Created
1489 ********************************************************/
1490 CURSOR c_primary_contact
1491 ( x_incident_id NUMBER
1492 )
1493 IS
1494 SELECT party_id
1495 FROM cs_hz_sr_contact_points contact
1496 WHERE contact.incident_id = x_incident_id
1497 AND contact.primary_flag = 'Y'
1498 AND EXISTS (SELECT incident_id
1499 FROM asg_incident_acc acc
1500 WHERE acc.incident_id = x_incident_id
1501 );
1502
1503 CURSOR c_server
1504 ( x_incident_id NUMBER
1505 )
1506 IS
1507 SELECT server_id
1508 FROM asg_incident_acc
1509 WHERE incident_id = x_incident_id;
1510
1511 l_party_id NUMBER;
1512 l_server_id NUMBER;
1513
1514 BEGIN
1515 OPEN c_primary_contact
1516 ( x_incident_id => x_incident_id
1517 );
1518 IF c_primary_contact%FOUND
1519 THEN
1520 FETCH c_primary_contact
1521 INTO l_party_id;
1522 OPEN c_server
1523 ( x_incident_id => x_incident_id
1524 );
1525 LOOP
1526 FETCH c_server
1527 INTO l_server_id;
1528 IF c_server%NOTFOUND
1529 THEN
1530 EXIT;
1531 END IF;
1532 UpdateAccesses_Partyid
1533 ( x_party_id => l_party_id
1534 , x_server_id => l_server_id
1535 , x_op => x_op
1536 );
1537 END LOOP;
1538 CLOSE c_server;
1539 END IF;
1540 CLOSE c_primary_contact;
1541 END SR_CONTACT_TRIGGER_HANDLER;
1542
1543 PROCEDURE CUST_RELATIONS_TRIGGER_HANDLER
1544 ( rs_cust_relation_id NUMBER
1545 , o_party_id NUMBER
1546 , n_party_id NUMBER
1547 , resource_id NUMBER
1548 , trigger_mode VARCHAR2
1549 )
1550 IS
1551 /********************************************************
1552 Name:
1553 CUST_RELATIONS_TRIGGER_HANDLER
1554
1555 Purpose:
1556 This procedure acts as a trigger on CSP_RS_CUST_RELATIONS
1557 and is fired in case of insert, update or delete.
1558
1559 Arguments:
1560
1561
1562 Known Limitations:
1563
1564 Notes:
1565
1566 History:
1567 21-JAN-2002 ASOYKAN Created
1568 ********************************************************/
1569 CURSOR c_device_users
1570 ( v_resource_id number
1571 )
1572 IS
1573 SELECT server_id
1574 FROM asg_server_resources
1575 WHERE resource_id = v_resource_id;
1576
1577 CURSOR c_party
1578 ( v_party_id NUMBER
1579 )
1580 IS
1581 SELECT server_id
1582 FROM asg_party_acc
1583 WHERE party_id = v_party_id;
1584
1585 v_server_id NUMBER;
1586 BEGIN
1587 IF trigger_mode = 'ON-INSERT'
1588 THEN
1589 -- Add this party to all the middle tiers for the resource
1590 OPEN c_device_users
1591 ( v_resource_id => resource_id
1592 );
1593 LOOP
1594 FETCH c_device_users
1595 INTO v_server_id;
1596 IF c_device_users%NOTFOUND
1597 THEN
1598 EXIT;
1599 END IF;
1600 UpdateAccesses_Partyid
1601 ( x_party_id => o_party_id
1602 , x_server_id => v_server_id
1603 , x_op => '+'
1604 );
1605 END LOOP;
1606 CLOSE c_device_users;
1607
1608 -- update not possible
1609 ELSIF trigger_mode = 'ON-UPDATE'
1610 THEN
1611 NULL;
1612 END IF;
1613
1614 END CUST_RELATIONS_TRIGGER_HANDLER;
1615 END CSF_ACCESS_PKG;