[Home] [Help]
PACKAGE BODY: APPS.CSL_JTF_RESOURCE_EXTNS_ACC_PKG
Source
1 PACKAGE BODY CSL_JTF_RESOURCE_EXTNS_ACC_PKG AS
2 /* $Header: cslreacb.pls 115.5 2003/01/06 07:01:04 vekrishn ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30)
6 := 'JTM_JTF_RS_RESOURCE_EXTNS_ACC';
7 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list
8 := JTM_HOOK_UTIL_PKG.t_publication_item_list('JTF_RS_RESOURCE_EXTNS');
9 g_table_name CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_EXTNS';
10 g_pk_name CONSTANT VARCHAR2(30) := 'RESOURCE_PK';
11 --Workaround for resource_id
12 g_debug_level NUMBER; -- debug level
13
14
15
16 /*** Function that checks if user should be replicated.
17 Returns TRUE if it should ***/
18 FUNCTION Replicate_Record
19 ( p_resource_extn_id NUMBER
20 )
21 RETURN BOOLEAN
22 IS
23 CURSOR c_user (b_resource_extn_id NUMBER) IS
24 SELECT resource_id -- Fix for Sql Performance
25 FROM JTF_RS_RESOURCE_EXTNS
26 WHERE resource_id = b_resource_extn_id;
27 r_user c_user%ROWTYPE;
28
29 BEGIN
30 /*** get debug level ***/
31 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
32
33 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
34 jtm_message_log_pkg.Log_Msg
35 ( p_resource_extn_id
36 , g_table_name
37 , 'Entering Replicate_Record'
38 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
39 END IF;
40
41 OPEN c_user( p_resource_extn_id );
42 FETCH c_user INTO r_user;
43 IF c_user%NOTFOUND THEN
44 /*** could not find user record -> exit ***/
45 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
46 jtm_message_log_pkg.Log_Msg
47 ( p_resource_extn_id
48 , g_table_name
49 , 'Replicate_Record error: Could not find resource_extn_id ' || p_resource_extn_id
50 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
51 END IF;
52 CLOSE c_user;
53 RETURN FALSE;
54 END IF;
55 CLOSE c_user;
56
57 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
58 jtm_message_log_pkg.Log_Msg
59 ( p_resource_extn_id
60 , g_table_name
61 , 'Replicate_Record returned TRUE'
62 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
63 END IF;
64
65 /** Record matched criteria -> return true ***/
66 RETURN TRUE;
67 END Replicate_Record;
68
69
70 /*** Private procedure that replicates given user related data for resource ***/
71 PROCEDURE Insert_ACC_Record
72 ( p_resource_extn_id IN NUMBER
73 ,p_resource_id IN NUMBER
74 )
75 IS
76 BEGIN
77 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
78 jtm_message_log_pkg.Log_Msg
79 ( p_resource_extn_id
80 , g_table_name
81 , 'Entering Insert_ACC_Record'
82 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
83 END IF;
84
85 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
86 jtm_message_log_pkg.Log_Msg
87 ( p_resource_extn_id
88 , g_table_name
89 , 'Inserting ACC record for resource_id = ' || p_resource_id
90 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
91 END IF;
92
93 /*** Insert user ACC record ***/
94 JTM_HOOK_UTIL_PKG.Insert_Acc
95 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
96 ,P_ACC_TABLE_NAME => g_acc_table_name
97 ,P_PK1_NAME => g_pk_name
98 ,P_PK1_NUM_VALUE => p_resource_extn_id
99 ,P_RESOURCE_ID => p_resource_id
100 );
101
102 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
103 jtm_message_log_pkg.Log_Msg
104 ( p_resource_extn_id
105 , g_table_name
106 , 'Leaving Insert_ACC_Record'
107 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
108 END IF;
109 END Insert_ACC_Record;
110
111 /*** Private procedure that re-sends given user to mobile ***/
112 PROCEDURE Update_ACC_Record
113 ( p_resource_extn_id IN NUMBER
114 ,p_resource_id IN NUMBER
115 ,p_acc_id IN NUMBER
116 )
117 IS
118 BEGIN
119 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
120 jtm_message_log_pkg.Log_Msg
121 ( p_resource_extn_id
122 , g_table_name
123 , 'Entering Update_ACC_Record'
124 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
125 END IF;
126
127 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
128 jtm_message_log_pkg.Log_Msg
129 ( p_resource_extn_id
130 , g_table_name
131 , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) || 'access_id = ' || p_acc_id
132 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
133 END IF;
134
135 /*** Update user ACC record ***/
136 JTM_HOOK_UTIL_PKG.Update_Acc
137 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
138 ,P_ACC_TABLE_NAME => g_acc_table_name
139 ,P_RESOURCE_ID => p_resource_id
140 ,P_ACCESS_ID => p_acc_id
141 );
142
143 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
144 jtm_message_log_pkg.Log_Msg
145 ( p_resource_extn_id
146 , g_table_name
147 , 'Leaving Update_ACC_Record'
148 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
149 END IF;
150 END Update_ACC_Record;
151
152 /*** Private procedure that deletes user for resource from acc table ***/
153 PROCEDURE Delete_ACC_Record
154 ( p_resource_extn_id IN NUMBER
155 ,p_resource_id IN NUMBER
156 )
157 IS
158 BEGIN
159 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
160 jtm_message_log_pkg.Log_Msg
161 ( p_resource_extn_id
162 , g_table_name
163 , 'Entering Delete_ACC_Record'
164 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
165 END IF;
166
167 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
168 jtm_message_log_pkg.Log_Msg
169 ( p_resource_extn_id
170 , g_table_name
171 , 'Deleting ACC record for resource_id = ' || p_resource_id
172 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
173 END IF;
174
175 /*** Delete user ACC record ***/
176 /*
177 removed because of bug 2539352
178
179 JTM_HOOK_UTIL_PKG.Delete_Acc
180 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
181 ,P_ACC_TABLE_NAME => g_acc_table_name
182 ,P_PK1_NAME => g_pk_name
183 ,P_PK1_NUM_VALUE => p_resource_extn_id
184 ,P_RESOURCE_ID => p_resource_id
185 );
186 */
187
188 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
189 jtm_message_log_pkg.Log_Msg
190 ( p_resource_extn_id
191 , g_table_name
192 , 'Leaving Delete_ACC_Record'
193 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
194 END IF;
195 END Delete_ACC_Record;
196
197 /***
198 Public procedure that gets called when a user needs to be inserted into ACC table.
199 ***/
200 PROCEDURE Insert_Resource_Extns
201 ( p_resource_extn_id IN NUMBER
202 ,p_resource_id IN NUMBER
203 )
204 IS
205 BEGIN
206 /*** get debug level ***/
207 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
208
209 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
210 jtm_message_log_pkg.Log_Msg
211 ( p_resource_extn_id
212 , g_table_name
213 , 'Entering Insert_Resource_Extns procedure'
214 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
215 END IF;
216
217 /*** no -> does record match criteria? ***/
218 IF Replicate_Record( p_resource_extn_id ) THEN
219 /*** yes -> insert user acc record ***/
220 Insert_ACC_Record
221 ( p_resource_extn_id
222 ,p_resource_id
223 );
224
225 END IF;
226
227 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
228 jtm_message_log_pkg.Log_Msg
229 ( p_resource_extn_id
230 , g_table_name
231 , 'Leaving Insert_Resource_Extns procedure'
232 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
233 END IF;
234
235 END Insert_Resource_Extns;
236
237 /***
238 Public procedure that gets called when a user needs to be updated into ACC table.
239 ***/
240 PROCEDURE Update_Resource_Extns
241 ( p_resource_extn_id IN NUMBER
242 ,p_resource_id IN NUMBER
243 )
244 IS
245 l_acc_id NUMBER;
246 BEGIN
247 /*** get debug level ***/
248 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
249
250 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
251 jtm_message_log_pkg.Log_Msg
252 ( p_resource_extn_id
253 , g_table_name
254 , 'Entering Update_Resource_Extns procedure'
255 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
256 END IF;
257
258 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
259 P_ACC_TABLE_NAME => g_acc_table_name
260 ,P_PK1_NAME => g_pk_name
261 ,P_PK1_NUM_VALUE => p_resource_extn_id
262 ,P_RESOURCE_ID => p_resource_id);
263
264 /*** is record already in ACC table? ***/
265 IF l_acc_id <> -1 THEN
266 /*** no -> does record match criteria? ***/
267 IF Replicate_Record( p_resource_extn_id ) THEN
268 /*** yes -> update user acc record ***/
269 Update_ACC_Record
270 ( p_resource_extn_id
271 ,p_resource_id
272 ,l_acc_id
273 );
274
275 END IF;
276 END IF;
277
278 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
279 jtm_message_log_pkg.Log_Msg
280 ( p_resource_extn_id
281 , g_table_name
282 , 'Leaving Update_Resource_Extns procedure'
283 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
284 END IF;
285
286 END Update_Resource_Extns;
287
288 /***
289 Public procedure that gets called when a user needs to be deleted from ACC table.
290 ***/
291 PROCEDURE Delete_Resource_Extns
292 ( p_resource_extn_id IN NUMBER
293 ,p_resource_id IN NUMBER
294 )
295 IS
296 l_acc_id NUMBER;
297 l_success BOOLEAN;
298 BEGIN
299 /*** get debug level ***/
300 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
301
302 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
303 jtm_message_log_pkg.Log_Msg
304 ( p_resource_extn_id
305 , g_table_name
306 , 'Entering Delete_Resource_Extns'
307 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
308 END IF;
309
310 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id(
311 P_ACC_TABLE_NAME => g_acc_table_name
312 ,P_PK1_NAME => g_pk_name
313 ,P_PK1_NUM_VALUE => p_resource_extn_id
314 ,P_RESOURCE_ID => p_resource_id);
315
316 /*** is record already in ACC table? ***/
317 IF l_acc_id <> -1 THEN
318 /*** yes -> update user acc record ***/
319 Delete_ACC_Record
320 ( p_resource_extn_id
321 ,p_resource_id);
322
323 END IF;
324
325 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
326 jtm_message_log_pkg.Log_Msg
327 ( p_resource_extn_id
328 , g_table_name
329 , 'Leaving Delete_Resource_Extns'
330 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
331 END IF;
332 END Delete_Resource_Extns;
333
334 END CSL_JTF_RESOURCE_EXTNS_ACC_PKG;