1 PACKAGE BODY CSL_WF_NOTIFICATIONS_ACC_PKG AS
2 /* $Header: cslwnacb.pls 115.5 2002/08/21 07:50:03 rrademak ship $ */
3
4
5 /*** Globals for notifications ***/
6 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_WF_NOTIFICATIONS_ACC';
7 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8 JTM_HOOK_UTIL_PKG.t_publication_item_list('WF_NOTIFICATIONS');
9 g_table_name CONSTANT VARCHAR2(30) := 'WF_NOTIFICATIONS';
10 g_pk1_name CONSTANT VARCHAR2(30) := 'NOTIFICATION_ID';
11 g_debug_level NUMBER;
12
13 FUNCTION REPLICATE_RECORD( p_notification_id IN NUMBER )
14 RETURN BOOLEAN
15 IS
16 CURSOR c_notification( b_notification_id NUMBER ) IS
17 SELECT wn.RECIPIENT_ROLE
18 , wna.TEXT_VALUE
19 FROM WF_NOTIFICATIONS wn
20 , WF_NOTIFICATION_ATTRIBUTES wna
21 WHERE wn.NOTIFICATION_ID = b_notification_id
22 AND wn.MESSAGE_TYPE = 'CS_MSGS'
23 AND wn.MESSAGE_NAME = 'FYI_MESSAGE'
24 AND wn.STATUS = 'OPEN'
25 AND wn.NOTIFICATION_ID = wna.NOTIFICATION_ID
26 AND wna.NAME = 'SENDER';
27
28 r_notification c_notification%ROWTYPE;
29
30 CURSOR c_mobile( b_user_name VARCHAR2 ) IS
31 SELECT jre.RESOURCE_ID
32 FROM JTF_RS_RESOURCE_EXTNS jre
33 , FND_USER usr
34 WHERE usr.USER_NAME = b_user_name
35 AND usr.user_id = jre.user_id;
36
37 r_mobile c_mobile%ROWTYPE;
38
39 l_ret_value BOOLEAN;
40 BEGIN
41 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
42
43 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
44 jtm_message_log_pkg.Log_Msg
45 ( p_notification_id
46 , g_table_name
47 , 'Entering function REPLICATE_RECORD'
48 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
49 END IF;
50
51 l_ret_value := FALSE;
52 OPEN c_notification( p_notification_id );
53 FETCH c_notification INTO r_notification;
54 IF c_notification%FOUND THEN
55 /*check if recipient is a valid mobile user*/
56 OPEN c_mobile( r_notification.recipient_role );
57 FETCH c_mobile INTO r_mobile;
58 IF c_mobile%FOUND THEN
59 l_ret_value := JTM_HOOK_UTIL_PKG.isMobileFSresource(r_mobile.resource_id);
60 END IF;
61 CLOSE c_mobile;
62
63 /*If recipient is not mobile, check if sender is*/
64 IF l_ret_value = FALSE THEN
65 OPEN c_mobile( r_notification.text_value );
66 FETCH c_mobile INTO r_mobile;
67 IF c_mobile%FOUND THEN
68 /*Check if sender is a valid mobile user*/
69 l_ret_value := JTM_HOOK_UTIL_PKG.isMobileFSresource(r_mobile.resource_id);
70 END IF;
71 CLOSE c_mobile;
72 IF l_ret_value = FALSE THEN
73 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
74 jtm_message_log_pkg.Log_Msg
75 ( p_notification_id
76 , g_table_name
77 , 'Notification '||p_notification_id||' is not for a valid mobile field service user.'
78 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
79 END IF;
80 END IF;
81 END IF;
82 ELSE
83 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
84 jtm_message_log_pkg.Log_Msg
85 ( p_notification_id
86 , g_table_name
87 , 'Notification '||p_notification_id||' is not an open CS_MSGS/FYI_MESSAGE'
88 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
89 END IF;
90 END IF;
91 CLOSE c_notification;
92
93 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
94 jtm_message_log_pkg.Log_Msg
95 ( p_notification_id
96 , g_table_name
97 , 'Leaving function REPLICATE_RECORD'
98 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
99 END IF;
100
101 RETURN l_ret_value;
102 END REPLICATE_RECORD;
103
104 PROCEDURE INSERT_NOTIFICATION( p_notification_id IN NUMBER )
105 IS
106 l_replicate BOOLEAN;
107
108 /*** Cursor to get user id which will be used to check if its a mobile resource ***/
109 CURSOR c_get_recipient
110 ( b_notification_id NUMBER
111 )
112 IS
113 SELECT jre.resource_id
114 , usr.user_id
115 FROM jtf_rs_resource_extns jre
116 , fnd_user usr
117 , wf_notifications wfn
118 WHERE wfn.recipient_role = usr.user_name
119 AND usr.user_id = jre.user_id
120 AND jre.category = 'EMPLOYEE'
121 AND wfn.notification_id = b_notification_id;
122
123 r_get_recipient c_get_recipient%ROWTYPE;
124
125 CURSOR c_get_sender( b_notification_id NUMBER ) IS
126 SELECT jre.resource_id
127 FROM jtf_rs_resource_extns jre
128 , fnd_user usr
129 , wf_notification_attributes wna
130 WHERE wna.name = 'SENDER'
131 AND wna.text_value = usr.user_name
132 AND usr.user_id = jre.user_id
133 AND jre.category = 'EMPLOYEE'
134 AND wna.notification_id = b_notification_id;
135
136 r_get_sender c_get_sender%ROWTYPE;
137
138 BEGIN
139 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
140 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
141 jtm_message_log_pkg.Log_Msg
142 ( p_notification_id
143 , g_table_name
144 , 'Entering Procedure INSERT_NOTIFICATION'
145 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
146 END IF;
147
148 l_replicate := REPLICATE_RECORD( p_notification_id );
149 IF l_replicate THEN
150 /*First check if mail is for mobile user */
151 OPEN c_get_recipient( p_notification_id );
152 FETCH c_get_recipient INTO r_get_recipient;
153 IF c_get_recipient%FOUND THEN
154 IF JTM_HOOK_UTIL_PKG.isMobileFSresource(r_get_recipient.resource_id) THEN
155 JTM_HOOK_UTIL_PKG.Insert_Acc
156 ( p_publication_item_names => g_publication_item_name
157 , p_acc_table_name => g_acc_table_name
158 , p_pk1_name => g_pk1_name
159 , p_pk1_num_value => p_notification_id
160 , p_resource_id => r_get_recipient.resource_id
161 );
162 /*** Call FND_USER Hook to make sure the recipient gets replicated as well. ***/
163 CSL_FND_USER_ACC_PKG.Insert_User (r_get_recipient.user_id, r_get_recipient.resource_id);
164 END IF;
165 END IF;
166 CLOSE c_get_recipient;
167 /*Check if sender also needs this record*/
168 OPEN c_get_sender( p_notification_id );
169 FETCH c_get_sender INTO r_get_sender;
170 IF c_get_sender%FOUND THEN
171 IF JTM_HOOK_UTIL_PKG.isMobileFSresource(r_get_sender.resource_id) THEN
172 JTM_HOOK_UTIL_PKG.Insert_Acc
173 ( p_publication_item_names => g_publication_item_name
174 , p_acc_table_name => g_acc_table_name
175 , p_pk1_name => g_pk1_name
176 , p_pk1_num_value => p_notification_id
177 , p_resource_id => r_get_sender.resource_id
178 );
179 /*Record should ge to the sender, user is user of notification record*/
180 /*** Call FND_USER Hook to make sure the recipient gets replicated as well. ***/
181 CSL_FND_USER_ACC_PKG.Insert_User (r_get_recipient.user_id, r_get_sender.resource_id);
182 END IF;
183 END IF;
184 CLOSE c_get_sender;
185 END IF;
186
187 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
188 jtm_message_log_pkg.LOG_MSG
189 ( p_notification_id
190 , g_table_name
191 , 'Leaving Procedure INSERT_NOTIFICATION'
192 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
193 END IF;
194
195 END INSERT_NOTIFICATION;
196
197 END CSL_WF_NOTIFICATIONS_ACC_PKG;
[Home] [Help]