1 PACKAGE BODY CSL_CONC_NOTIFICATION_PKG AS
2 /* $Header: cslcnwfb.pls 115.5 2002/08/21 08:24:57 rrademak noship $ */
3
4 PROCEDURE RUN_CONCURRENT_NOTIFICATIONS
5 IS
6
7 CURSOR c_LastRundate IS
8 SELECT LAST_RUN_DATE
9 FROM JTM_CON_REQUEST_DATA
10 WHERE package_name = 'CSL_CONC_NOTIFICATION_PKG'
11 AND procedure_name = 'RUN_CONCURRENT_NOTIFICATIONS';
12 r_LastRundate c_LastRundate%ROWTYPE;
13
14 CURSOR c_notification( b_last_date DATE ) IS
15 SELECT NOTIFICATION_ID
16 FROM WF_NOTIFICATIONS
17 WHERE BEGIN_DATE >=NVL( b_last_date, BEGIN_DATE )
18 AND NOTIFICATION_ID NOT IN
19 ( SELECT NOTIFICATION_ID
20 FROM JTM_WF_NOTIFICATIONS_ACC );
21
22 CURSOR c_attributes( b_notification_id NUMBER ) IS
23 SELECT NAME
24 FROM WF_NOTIFICATION_ATTRIBUTES
25 WHERE NOTIFICATION_ID = b_notification_id;
26
27 /** Cursor for retrieving all Mobile Resources ***/
28 CURSOR c_all_mobile_res IS
29 SELECT asgusr.resource_id
30 FROM asg_pub pub
31 , asg_pub_responsibility pubresp
32 , fnd_user_resp_groups usrresp
33 , fnd_user usr
34 , jtf_rs_resource_extns res
35 , asg_user asgusr
36 WHERE asgusr.resource_id = res.resource_id
37 AND pub.name = 'SERVICEL'
38 AND pub.enabled = 'Y'
39 AND pub.status = 'Y'
40 AND pub.pub_id = pubresp.pub_id
41 AND pubresp.responsibility_id = usrresp.responsibility_id
42 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usrresp.start_date,sysdate))
43 AND TRUNC(NVL(usrresp.end_date,sysdate))
44 AND usrresp.user_id = usr.user_id
45 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(usr.start_date,sysdate))
46 AND TRUNC(NVL(usr.end_date,sysdate))
47 AND usr.user_id = res.user_id
48 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(res.start_date_active,sysdate))
49 AND TRUNC(NVL(res.end_date_active,sysdate));
50
51 r_all_mobile_res c_all_mobile_res%ROWTYPE;
52
53 /** Cursor for retrieving all Attributes per Notification ***/
54 CURSOR c_get_attr_per_notification (b_resource_id NUMBER) IS
55 SELECT NOTIFICATION_ID, NAME
56 FROM WF_NOTIFICATION_ATTRIBUTES
57 WHERE NAME IN ('SENDER', 'SUBJECT', 'MESSAGE_TEXT', 'PRIORITY', 'READ_FLAG', 'DELETE_FLAG')
58 AND (NOTIFICATION_ID, NAME) NOT IN
59 (
60 SELECT NOTIFICATION_ID, NAME
61 FROM JTM_WF_NOTIFICATION_AT_ACC
62 WHERE RESOURCE_ID = b_resource_id
63 )
64 AND NOTIFICATION_ID IN
65 (
66 SELECT NOTIFICATION_ID
67 FROM JTM_WF_NOTIFICATIONS_ACC
68 WHERE RESOURCE_ID = b_resource_id
69 );
70
71 r_get_attr_per_notification c_get_attr_per_notification%ROWTYPE;
72
73 BEGIN
74 /*Fetch and update last run date*/
75 OPEN c_LastRundate;
76 FETCH c_LastRundate INTO r_LastRundate;
77 IF c_LastRundate%NOTFOUND THEN
78 /*Never seeded = ERROR */
79 jtm_message_log_pkg.Log_Msg
80 ( 0
81 , 'CSL_CONC_NOTIFICATION_PKG'
82 , 'CSL_CONC_NOTIFICATION_PKG called but not seeded'
83 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
84 );
85 CLOSE c_LastRundate;
86 RETURN;
87 END IF;
88 CLOSE c_LastRundate;
89
90 /*Update the last run date*/
91 UPDATE JTM_CON_REQUEST_DATA
92 SET LAST_RUN_DATE = SYSDATE
93 WHERE package_name = 'CSL_CONC_NOTIFICATION_PKG'
94 AND procedure_name = 'RUN_CONCURRENT_NOTIFICATIONS';
95
96 FOR r_notification IN c_notification( r_LastRundate.LAST_RUN_DATE ) LOOP
97 /*We have all new notifications now call notification package*/
98 CSL_WF_NOTIFICATIONS_ACC_PKG.INSERT_NOTIFICATION( r_notification.NOTIFICATION_ID );
99
100 /*Now fetch the attributes*/
101 FOR r_attribute IN c_attributes( r_notification.NOTIFICATION_ID ) LOOP
102 CSL_WF_NOTIFICATION_AT_ACC_PKG.INSERT_NOTIFICATION_ATTRIBUTE ( r_notification.NOTIFICATION_ID
103 , r_attribute.NAME );
104 END LOOP;
105 END LOOP;
106
107 /***
108 Retrieve per Mobile Resource all Notification Attributes that are not in ACC table yet
109 but for which a Notification exists in the Notification ACC table.
110 These are (newly) created Notification Attributes that would not get pushed by the Concurrent
111 Program because the Notification itself is not new but only the Attributes are.
112 ***/
113 FOR r_all_mobile_res IN c_all_mobile_res LOOP
114 FOR r_get_attr_per_notification IN c_get_attr_per_notification (r_all_mobile_res.resource_id) LOOP
115 CSL_WF_NOTIFICATION_AT_ACC_PKG.INSERT_NOTIFICATION_ATTRIBUTE
116 ( r_get_attr_per_notification.NOTIFICATION_ID,
117 r_get_attr_per_notification.NAME );
118 END LOOP;
119 END LOOP;
120
121 EXCEPTION WHEN OTHERS THEN
122 ROLLBACK;
123 END RUN_CONCURRENT_NOTIFICATIONS;
124
125 END CSL_CONC_NOTIFICATION_PKG;
[Home] [Help]