[Home] [Help]
PACKAGE BODY: APPS.CSL_NOTIFICATION_ATTR_PKG
Source
1 PACKAGE BODY CSL_NOTIFICATION_ATTR_PKG AS
2 /* $Header: cslvnoab.pls 115.12 2002/11/08 14:00:28 asiegers ship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_NOTIFICATION_ATTR_PKG';
8 g_pub_name CONSTANT VARCHAR2(30) := 'WF_NOTIFICATION_ATTR';
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR c_not_attr( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_WF_NOTIFICATION_ATTR_inq
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 /***
18 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
19 ***/
20 PROCEDURE APPLY_INSERT
21 (
22 p_record IN c_not_attr%ROWTYPE,
23 p_error_msg OUT NOCOPY VARCHAR2,
24 x_return_status IN OUT NOCOPY VARCHAR2
25 ) IS
26 CURSOR c_attr( b_notification_id NUMBER, b_name VARCHAR2 ) IS
27 SELECT *
28 FROM WF_NOTIFICATION_ATTRIBUTES
29 WHERE NOTIFICATION_ID = b_notification_id
30 AND NAME = b_name;
31 r_attr c_attr%ROWTYPE;
32 BEGIN
33 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
34 jtm_message_log_pkg.Log_Msg
35 ( v_object_id => p_record.notification_id
36 , v_object_name => g_object_name
37 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
38 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
39 END IF;
40
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42
43 OPEN c_attr( b_notification_id => p_record.notification_id
44 , b_name => p_record.name );
45 FETCH c_attr INTO r_attr;
46 IF c_attr%NOTFOUND THEN
47 -- Create the attribute
48 WF_NOTIFICATION.AddAttr
49 ( nid => p_record.notification_id
50 , aname => p_record.name );
51 END IF;
52 CLOSE c_attr;
53
54 -- Set the attribute
55 WF_NOTIFICATION.SetAttrText
56 ( nid => p_record.notification_id
57 , aname => p_record.name
58 , avalue => p_record.text_value );
59
60 /*** Call Concurrent Program to push new Notification Attribute to mobile ***/
61 CSL_CONC_NOTIFICATION_PKG.RUN_CONCURRENT_NOTIFICATIONS;
62
63 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
64 jtm_message_log_pkg.Log_Msg
65 ( v_object_id => p_record.notification_id
66 , v_object_name => g_object_name
67 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
68 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
69 END IF;
70
71 EXCEPTION WHEN OTHERS THEN
72 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
73 jtm_message_log_pkg.Log_Msg
74 ( v_object_id => p_record.notification_id
75 , v_object_name => g_object_name
76 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
77 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
78 END IF;
79
80 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
81 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
82 (
83 p_api_error => TRUE
84 );
85
86 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
87 jtm_message_log_pkg.Log_Msg
88 ( v_object_id => p_record.notification_id
89 , v_object_name => g_object_name
90 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
91 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
92 END IF;
93
94 x_return_status := FND_API.G_RET_STS_ERROR;
95 END APPLY_INSERT;
96
97 /***
98 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
99 ***/
100 PROCEDURE APPLY_UPDATE
101 (
102 p_record IN c_not_attr%ROWTYPE,
103 p_error_msg OUT NOCOPY VARCHAR2,
104 x_return_status IN OUT NOCOPY VARCHAR2
105 ) IS
106 BEGIN
107 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
108 jtm_message_log_pkg.Log_Msg
109 ( v_object_id => p_record.notification_id
110 , v_object_name => g_object_name
111 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
112 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
113 END IF;
114
115 /*** Updating is done by inserting the READ or DELETE _FLAG attribute ***/
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
119 /*** exception occurred in API -> return errmsg ***/
120 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
121 (
122 p_api_error => TRUE
123 );
124 END IF;
125
126 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
127 jtm_message_log_pkg.Log_Msg
128 ( v_object_id => p_record.notification_id
129 , v_object_name => g_object_name
130 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
131 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
132 END IF;
133
134 EXCEPTION WHEN OTHERS THEN
135 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
136 jtm_message_log_pkg.Log_Msg
137 ( v_object_id => p_record.notification_id
138 , v_object_name => g_object_name
139 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
140 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
141 END IF;
142
143 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
144 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
145 (
146 p_api_error => TRUE
147 );
148
149 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
150 jtm_message_log_pkg.Log_Msg
151 ( v_object_id => p_record.notification_id
152 , v_object_name => g_object_name
153 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
154 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
155 END IF;
156
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 END APPLY_UPDATE;
159
160 /***
161 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
162 ***/
163 PROCEDURE APPLY_RECORD
164 (
165 p_record IN c_not_attr%ROWTYPE,
166 p_error_msg OUT NOCOPY VARCHAR2,
167 x_return_status IN OUT NOCOPY VARCHAR2
168 ) IS
169 BEGIN
170 /*** initialize return status and message list ***/
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 FND_MSG_PUB.INITIALIZE;
173
174 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
175 jtm_message_log_pkg.Log_Msg
176 ( v_object_id => p_record.notification_id
177 , v_object_name => g_object_name
178 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
179 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
180 END IF;
181
182 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
183 jtm_message_log_pkg.Log_Msg
184 ( v_object_id => p_record.notification_id
185 , v_object_name => g_object_name
186 , v_message => 'Processing NOTIFICATION_ID = ' || p_record.notification_id || fnd_global.local_chr(10) ||
187 'DMLTYPE = ' || p_record.dmltype$$
188 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
189 END IF;
190
191 IF p_record.dmltype$$='I' THEN
192 -- Process insert
193 APPLY_INSERT
194 (
195 p_record,
196 p_error_msg,
197 x_return_status
198 );
199 ELSIF p_record.dmltype$$='U' THEN
200 -- Process update
201 APPLY_UPDATE
202 (
203 p_record,
204 p_error_msg,
205 x_return_status
206 );
207 ELSIF p_record.dmltype$$='D' THEN
208 -- Process delete; not supported for this entity
209 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
210 jtm_message_log_pkg.Log_Msg
211 ( v_object_id => p_record.notification_id
212 , v_object_name => g_object_name
213 , v_message => 'Delete is not supported for this entity'
214 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
215 END IF;
216
217 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
218 (
219 p_message => 'CSL_DML_OPERATION'
220 , p_token_name1 => 'DML'
221 , p_token_value1 => p_record.dmltype$$
222 );
223
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 ELSE
226 -- invalid dml type
227 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
228 jtm_message_log_pkg.Log_Msg
229 ( v_object_id => p_record.notification_id
230 , v_object_name => g_object_name
231 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
232 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
233 END IF;
234
235 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
236 (
237 p_message => 'CSL_DML_OPERATION'
238 , p_token_name1 => 'DML'
239 , p_token_value1 => p_record.dmltype$$
240 );
241
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 END IF;
244
245 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
246 jtm_message_log_pkg.Log_Msg
247 ( v_object_id => p_record.notification_id
248 , v_object_name => g_object_name
249 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
250 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
251 END IF;
252 EXCEPTION WHEN OTHERS THEN
253 /*** defer record when any process exception occurs ***/
254 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
255 jtm_message_log_pkg.Log_Msg
256 ( v_object_id => p_record.notification_id
257 , v_object_name => g_object_name
258 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
259 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
260 END IF;
261
262 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
263 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
264 (
265 p_api_error => TRUE
266 );
267
268 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
269 jtm_message_log_pkg.Log_Msg
270 ( v_object_id => p_record.notification_id
271 , v_object_name => g_object_name
272 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
273 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
274 END IF;
275
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 END APPLY_RECORD;
278
279 /***
280 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item WF_NOTIFICATION_ATTR
281 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
282 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
283 public APIs.
284 ***/
285 PROCEDURE APPLY_CLIENT_CHANGES
286 (
287 p_user_name IN VARCHAR2,
288 p_tranid IN NUMBER,
289 p_debug_level IN NUMBER,
290 x_return_status IN OUT NOCOPY VARCHAR2
291 ) IS
292
293 l_process_status VARCHAR2(1);
294 l_error_msg VARCHAR2(4000);
295 BEGIN
296 g_debug_level := p_debug_level;
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
300 jtm_message_log_pkg.Log_Msg
301 ( v_object_id => null
302 , v_object_name => g_object_name
303 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
304 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
305 END IF;
306
307 /*** loop through WF_NOTIFICATION_ATTR records in inqueue ***/
308 FOR r_not_attr IN c_not_attr( p_user_name, p_tranid) LOOP
309
310 SAVEPOINT save_rec;
311
312 /*** apply record ***/
313 APPLY_RECORD
314 (
315 r_not_attr
316 , l_error_msg
317 , l_process_status
318 );
319
320 /*** was record processed successfully? ***/
321 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
322 /*** Yes -> delete record from inqueue ***/
323 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
324 jtm_message_log_pkg.Log_Msg
325 ( v_object_id => r_not_attr.notification_id
326 , v_object_name => g_object_name
327 , v_message => 'Record successfully processed, deleting from inqueue'
328 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
329 END IF;
330
331 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
332 (
333 p_user_name,
334 p_tranid,
335 r_not_attr.seqno$$,
336 r_not_attr.notification_id,
337 g_object_name,
338 g_pub_name,
339 l_error_msg,
340 l_process_status
341 );
342
343 /*** was delete successful? ***/
344 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
345 /*** no -> rollback ***/
346 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
347 jtm_message_log_pkg.Log_Msg
348 ( v_object_id => r_not_attr.notification_id
349 , v_object_name => g_object_name
350 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
351 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
352 END IF;
353 ROLLBACK TO save_rec;
354 END IF;
355 END IF;
356
357 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
358 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
359 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
360 jtm_message_log_pkg.Log_Msg
361 ( v_object_id => r_not_attr.notification_id
362 , v_object_name => g_object_name
363 , v_message => 'Record not processed successfully, deferring and rejecting record'
364 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
365 END IF;
366
367 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
368 (
369 p_user_name
370 , p_tranid
371 , r_not_attr.seqno$$
372 , r_not_attr.notification_id
373 , g_object_name
374 , g_pub_name
375 , l_error_msg
376 , l_process_status
377 );
378
379 /*** Was defer successful? ***/
380 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
381 /*** no -> rollback ***/
382 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
383 jtm_message_log_pkg.Log_Msg
384 ( v_object_id => r_not_attr.notification_id
385 , v_object_name => g_object_name
386 , v_message => 'Defer record failed, rolling back to savepoint'
387 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
388 END IF;
389 ROLLBACK TO save_rec;
390 END IF;
391 END IF;
392
393 END LOOP;
394
395 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
396 jtm_message_log_pkg.Log_Msg
397 ( v_object_id => null
398 , v_object_name => g_object_name
399 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
400 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
401 END IF;
402
403 EXCEPTION WHEN OTHERS THEN
404 /*** catch and log exceptions ***/
405 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
406 jtm_message_log_pkg.Log_Msg
407 ( v_object_id => null
408 , v_object_name => g_object_name
409 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
410 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
411 END IF;
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 END APPLY_CLIENT_CHANGES;
414
415 END CSL_NOTIFICATION_ATTR_PKG;