[Home] [Help]
PACKAGE BODY: APPS.CSM_CLIENT_NFN_LOG_PKG
Source
1 PACKAGE BODY CSM_CLIENT_NFN_LOG_PKG AS
2 /* $Header: csmucnlb.pls 120.1.12010000.1 2009/08/03 06:33:08 appldev noship $ */
3
4 /*
5 * The function is to upload csm_clinet_nfn_log auto sync table to base table
6 */
7
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- HBEERAM 29-APR-2009 Created
12 --
13 -- --------- ------------------- ------------------------------------------
14 -- Enter procedure, function bodies as shown below
15
16 /*** Globals ***/
17 g_object_name CONSTANT VARCHAR2(30) := 'CSM_CLIENT_NFN_LOG_PKG'; -- package name
18 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_CLIENT_NFN_LOG'; -- publication item name
19 g_debug_level NUMBER; -- debug level
20
21
25 g_cl_nfn_log_pk1_name CONSTANT VARCHAR2(30) := 'NOTIFICATION_ID';
22 g_cl_nfn_log_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_CLIENT_NFN_LOG_ACC';
23 g_cl_nfn_log_table_name CONSTANT VARCHAR2(30) := 'CSM_CLIENT_NFN_LOG';
24 g_cl_nfn_log_seq_name CONSTANT VARCHAR2(30) := 'CSM_CLIENT_NFN_LOG_ACC_S';
26 g_cl_nfn_log_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
27 CSM_ACC_PKG.t_publication_item_list('CSM_CLIENT_NFN_LOG');
28
29
30 CURSOR c_client_nfn_log_inq( b_user_name VARCHAR2, b_tranid NUMBER) IS
31 SELECT *
32 FROM csm_client_nfn_log_inq
33 WHERE tranid$$ = b_tranid
34 AND clid$$cs = b_user_name;
35
36 -----------------------------------------------------------------------------------------------------------
37 PROCEDURE INSERT_CLIENT_NFN_LOG_ACC (p_notification_id IN wf_notifications.notification_id%TYPE,
38 p_user_id IN fnd_user.user_id%TYPE)
39 IS
40 l_sysdate DATE;
41 l_count NUMBER;
42 BEGIN
43 CSM_ACC_PKG.Insert_Acc
44 ( P_PUBLICATION_ITEM_NAMES => g_cl_nfn_log_pubi_name
45 ,P_ACC_TABLE_NAME => g_cl_nfn_log_acc_table_name
46 ,P_SEQ_NAME => g_cl_nfn_log_seq_name
47 ,P_PK1_NAME => g_cl_nfn_log_pk1_name
48 ,P_PK1_NUM_VALUE => p_notification_id
49 ,P_USER_ID => p_user_id
50 );
51 EXCEPTION WHEN OTHERS THEN
52 CSM_UTIL_PKG.LOG
53 ( 'Exception occurred in ' || g_object_name || '.INSERT_CLIENT_NFN_LOG_ACC: ' || SQLERRM,' for PK ' || to_char(p_notification_id) ||
54 g_object_name || '.INSERT_CLIENT_NFN_LOG_ACC',FND_LOG.LEVEL_EXCEPTION);
55
56 RAISE;
57 END INSERT_CLIENT_NFN_LOG_ACC;-- end INSERT_CLIENT_NFN_LOG_ACC
58 -------------------------------------------------------------------------------------------------
59
60 PROCEDURE CLIENT_NFN_LOG_ACC_PROCESSOR(p_user_id IN fnd_user.user_id%TYPE)
61 IS
62 l_sqlerrno VARCHAR2(20);
63 l_sqlerrmsg VARCHAR2(4000);
64 l_error_msg VARCHAR2(4000);
65 l_return_status VARCHAR2(2000);
66
67 -- get all notifications in which user is a recipient
68 CURSOR c_notf(b_user_id fnd_user.user_id%TYPE) IS
69 SELECT DISTINCT notification_id
70 FROM CSM_CLIENT_NFN_LOG_ACC ACC;
71
72 BEGIN
73 /* CSM_UTIL_PKG.LOG('Entering CLIENT_NFN_LOG_ACC_PROCESSOR for user_id: ' || p_user_id,
74 g_object_name || '.CLIENT_NFN_LOG_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
75
76
77 -- get all notifications in which user is a recipient
78 FOR l_notf_rec IN c_notf(p_user_id)
79 LOOP
80 INSERT_CLIENT_NFN_LOG_ACC (l_notf_rec.notification_id, p_user_id);
81 END LOOP;
82
83 CSM_UTIL_PKG.LOG('Leaving CLIENT_NFN_LOG_ACC_PROCESSOR for user_id: ' || p_user_id,
84 g_object_name || '.CLIENT_NFN_LOG_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
85 EXCEPTION
86 WHEN OTHERS THEN
87 l_sqlerrno := to_char(SQLCODE);
88 l_sqlerrmsg := substr(SQLERRM, 1,2000);
89 l_error_msg := ' Exception in CLIENT_NFN_LOG_ACC_PROCESSOR for for user_id: ' || p_user_id
90 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
91 CSM_UTIL_PKG.LOG(l_error_msg, g_object_name || '.CLIENT_NFN_LOG_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
92 RAISE;
93 */
94 RETURN;
95 END CLIENT_NFN_LOG_ACC_PROCESSOR;
96
97 -------
98
99 /***
100 This procedure is called by APPLY_RECORD when
101 an inserted record is to be processed.
102 ***/
103 PROCEDURE APPLY_INSERT
104 (
105 p_record IN c_client_nfn_log_inq%ROWTYPE,
106 p_error_msg OUT NOCOPY VARCHAR2,
107 x_return_status IN OUT NOCOPY VARCHAR2,
108 x_reject_row OUT NOCOPY BOOLEAN
109 ) IS
110
111
112 l_notification_id csm_client_nfn_log.notification_id%TYPE ;
113 l_object_id csm_client_nfn_log.object_id%TYPE;
114 l_object_name csm_client_nfn_log.object_name%TYPE;
115 l_user_name asg_user.user_name%TYPE;
116
117 l_rowid ROWID;
118
119 CURSOR c_record_exists
120 (b_notification_id NUMBER
121 )IS SELECT ROWID
122 FROM CSM_CLIENT_NFN_LOG
123 WHERE notification_id = b_notification_id;
124
125
126
127 BEGIN
128
129
130 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_INSERT:'
131 || ' for PK ' || p_record.notification_id,
132 'CSM_CLIENT_NFN_LOG.APPLY_INSERT',
133 FND_LOG.LEVEL_PROCEDURE );
134
135 /***************************************************************************
136 ** Initialize API return status to success
137 ***************************************************************************/
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139 l_notification_id := p_record.notification_id;
140 l_user_name := p_record.CLID$$CS;
141 l_object_id := p_record.object_id;
142 l_object_name := p_record.object_name;
143
144 insert into csm_client_nfn_log(NOTIFICATION_ID,
145 OBJECT_ID ,
146 OBJECT_NAME,
147 MESSAGE,
148 STATUS,
149 REPLYTOEMAIL,
150 NID,
151 CREATION_DATE ,
152 CREATED_BY,
153 LAST_UPDATE_DATE,
154 LAST_UPDATED_BY)
155 values (l_notification_id,l_object_id ,l_object_name,p_record.message ,p_record.status,p_record.replytoemail,p_record.nid,p_record.creation_date ,p_record.created_by,p_record.last_update_date,p_record.last_updated_by)
156 RETURNING NOTIFICATION_ID INTO l_notification_id;
157
158 /***************************************************************************
159 ** Check whether the insert was succesfull
160 ***************************************************************************/
161 IF (c_record_exists%ISOPEN)THEN
165 OPEN c_record_exists(l_notification_id);
162 CLOSE c_record_exists;
163 END IF;
164
166 FETCH c_record_exists INTO l_rowid;
167 IF (c_record_exists%NOTFOUND)THEN
168 IF (c_record_exists%ISOPEN)
169 THEN
170 CLOSE c_record_exists;
171 END IF;
172 RAISE no_data_found;
173 END IF;
174
175 IF (c_record_exists%ISOPEN) THEN
176 CLOSE c_record_exists;
177 END IF;
178
179
180
181 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
182 THEN
183 x_return_status := FND_API.G_RET_STS_ERROR;
184
185 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
186 || ' ROOT ERROR: create statement'
187 || ' for PK ' || p_record.NOTIFICATION_ID,
188 g_object_name || '.APPLY_INSERT',
189 FND_LOG.LEVEL_ERROR );
190 RETURN ;
191 END IF;
192
193 --delete csm_client_nfn_log_inq where clid$$cs = l_user_name and notification_id = l_notification_id;
194 -- success
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196 COMMIT;
197
198 EXCEPTION
199 WHEN others THEN
200 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
201 || ' for PK ' || p_record.notification_id,
202 g_object_name || '.APPLY_INSERT',
203 FND_LOG.LEVEL_EXCEPTION );
204
205 x_return_status := FND_API.G_RET_STS_ERROR;
206
207 END APPLY_INSERT;
208
209
210 /***
211 This procedure is called by APPLY_RECORD when
212 an updated record is to be processed.
213 For CSM 11583, we support updates on the DFF columns
214 ***/
215 PROCEDURE APPLY_UPDATE
216 (
217 p_record IN c_client_nfn_log_inq%ROWTYPE,
218 p_error_msg OUT NOCOPY VARCHAR2,
219 x_return_status IN OUT NOCOPY VARCHAR2
220 )
221 IS
222 CURSOR c_client_nfn_log ( b_notification_id NUMBER ) IS
223 SELECT *
224 FROM csm_client_nfn_log
225 WHERE notification_id = b_notification_id;
226
227 CURSOR c_last_update_date ( b_notification_id NUMBER)
228 IS
229 SELECT LAST_UPDATE_DATE
230 FROM csm_client_nfn_log
231 WHERE notification_id = b_notification_id;
232
233
234 --variable declarations
235
236 l_item_rec c_client_nfn_log%ROWTYPE;
237
238 l_notification_id csm_client_nfn_log.notification_id%TYPE ;
239 l_object_id csm_client_nfn_log.object_id%TYPE;
240 l_object_name csm_client_nfn_log.object_name%TYPE;
241 l_user_name asg_user.user_name%TYPE;
242
243 l_rowid ROWID;
244
245 BEGIN
246
247
248 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_UPDATE:'
249 || ' for PK ' || p_record.notification_id,
250 'CSM_CLIENT_NFN_LOG.APPLY_UPDATE',
251 FND_LOG.LEVEL_PROCEDURE );
252 /***************************************************************************
253 ** Initialize API return status to success
254 ***************************************************************************/
255 x_return_status := FND_API.G_RET_STS_SUCCESS;
256 l_notification_id := p_record.notification_id;
257 l_user_name := p_record.CLID$$CS;
258
259 IF (c_client_nfn_log%ISOPEN) THEN
260 CLOSE c_client_nfn_log;
261 END IF;
262
263 OPEN c_client_nfn_log(p_record.notification_id);
264 FETCH c_client_nfn_log INTO l_item_rec;
265
266 IF (c_client_nfn_log%NOTFOUND) THEN
267
268 IF (c_client_nfn_log%ISOPEN) THEN
269 CLOSE c_client_nfn_log;
270 END IF;
271
272 fnd_message.set_name ('CSM', 'CSM_CLIENT_NFN_LOG');
273 fnd_message.set_token ('NOTIFICATION_ID', p_record.notification_id);
274 fnd_msg_pub.add;
275 RAISE no_data_found;
276 END IF;
277
278 IF (c_client_nfn_log%ISOPEN)THEN
279 CLOSE c_client_nfn_log;
280 END IF;
281
282 /***************************************************************************
283 ** Update the record
284 ***************************************************************************/
285
286 UPDATE csm_client_nfn_log
287 SET NOTIFICATION_ID = p_record.notification_id,
288 OBJECT_ID = p_record.object_id ,
289 OBJECT_NAME = p_record.object_name,
290 MESSAGE = p_record.message,
291 STATUS = p_record.status,
292 REPLYTOEMAIL = p_record.replytoemail,
293 NID = p_record.nid,
294 CREATION_DATE = p_record.creation_date ,
295 CREATED_BY = p_record.created_by,
296 LAST_UPDATE_DATE = p_record.last_update_date,
297 LAST_UPDATED_BY = p_record.last_updated_by
298 where NOTIFICATION_ID = p_record.notification_id;
299
300
301
302 /***************************************************************************
303 ** Check if the update was succesful
304 ***************************************************************************/
305
306 IF (SQL%NOTFOUND)
307 THEN
308 RAISE NO_DATA_FOUND;
309 END IF;
310
311 -- success
312 -- delete csm_client_nfn_log_inq where clid$$cs = l_user_name and notification_id = l_notification_id;
313 x_return_status := FND_API.G_RET_STS_SUCCESS;
314
315 EXCEPTION
316
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 IF (c_client_nfn_log%ISOPEN) THEN
319 CLOSE c_client_nfn_log;
320 END IF;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322
323 WHEN others THEN
324 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
325 || ' for PK ' || p_record.notification_id,
326 g_object_name || '.APPLY_UPDATE',
327 FND_LOG.LEVEL_EXCEPTION );
328
329 x_return_status := FND_API.G_RET_STS_ERROR;
330
331 END APPLY_UPDATE;
332
333 PROCEDURE APPLY_RECORD
334 (
335 p_record IN c_client_nfn_log_inq%ROWTYPE,
336 p_error_msg OUT NOCOPY VARCHAR2,
337 x_return_status IN OUT NOCOPY VARCHAR2,
338 x_reject_row OUT NOCOPY BOOLEAN
339 ) IS
340 BEGIN
341
342 /*** initialize return status and message list ***/
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344 FND_MSG_PUB.INITIALIZE;
345
346
347 IF p_record.dmltype$$='I' THEN
348 -- Process insert
349 APPLY_INSERT
350 (
351 p_record,
352 p_error_msg,
353 x_return_status,
354 x_reject_row --Bug 5288413
355 );
356 ELSIF p_record.dmltype$$='U' THEN -- YLIAO: for 11583, we do support UPDATE
357 -- Process update
358 APPLY_UPDATE
359 (
360 p_record,
361 p_error_msg,
362 x_return_status
363 );
364 ELSE
365 -- Process delete and insert;
366 -- Not supported for this entity
367 CSM_UTIL_PKG.LOG
368 ( 'Delete and Update is not supported for this entity'
369 || ' for PK ' || p_record.notification_id ,
370 g_object_name || '.APPLY_RECORD',
371 FND_LOG.LEVEL_ERROR);
372
373 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
374 (
375 p_message => 'CSM_DML_OPERATION'
376 , p_token_name1 => 'DML'
377 , p_token_value1 => p_record.dmltype$$
378 );
379
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 END IF;
382
383 EXCEPTION
384 WHEN OTHERS THEN
385 /*** defer record when any process exception occurs ***/
386 CSM_UTIL_PKG.LOG
387 ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || SQLERRM
388 || ' for PK ' || p_record.notification_id ,
389 g_object_name || '.APPLY_RECORD',
390 FND_LOG.LEVEL_EXCEPTION);
391 -- temp -- find more detail --remove comment
392 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', SQLERRM);
393 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
394 (
395 p_api_error => TRUE
396 );
397
398 x_return_status := FND_API.G_RET_STS_ERROR;
399
400 END APPLY_RECORD;
401 ----------------------------------------------------------------------------------------------------------------------
402
403 /***
404 APPLY_CLIENT_CHANGE procedure is called by CSM_SERVICEP_WRAPPER_PKG, for upward sync of
405 publication item CSM_CLIENT_NFN_LOG
406 ***/
407 PROCEDURE APPLY_CLIENT_CHANGES
408 (
409 p_user_name IN VARCHAR2,
410 p_tranid IN NUMBER,
411 p_debug_level IN NUMBER,
412 x_return_status IN OUT NOCOPY VARCHAR2
413 )
414 IS
415 l_process_status VARCHAR2(1);
416 l_return_status VARCHAR2(1);
417 l_error_msg VARCHAR2(4000);
418 l_reject_row boolean;
419 BEGIN
420 csm_util_pkg.log
421 ( g_object_name || '.APPLY_CLIENT_CHANGES entered',
422 g_object_name || '.APPLY_CLIENT_CHANGES',
423 FND_LOG.LEVEL_PROCEDURE);
424 g_debug_level := p_debug_level;
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426
427 /*** loop through records in inqueue ***/
428 FOR r_client_nfn_log IN c_client_nfn_log_inq( p_user_name, p_tranid) LOOP
429
430 SAVEPOINT save_rec;
431
432 /*** apply record ***/
433 APPLY_RECORD
434 (
435 r_client_nfn_log
436 , l_error_msg
437 , l_process_status
438 , l_reject_row
439 );
440
441 /*** was record processed successfully? ***/
442 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
443 /*** If Yes -> delete record from inqueue ***/
444 IF l_reject_row THEN
445 CSM_UTIL_PKG.REJECT_RECORD
446 (
447 p_user_name,
448 p_tranid,
449 r_client_nfn_log.seqno$$,
450 r_client_nfn_log.notification_id,
451 g_object_name,
452 g_pub_name,
453 l_error_msg,
454 l_return_status
455 );
456 ELSE
457 CSM_UTIL_PKG.DELETE_RECORD
458 (
459 p_user_name,
460 p_tranid,
461 r_client_nfn_log.seqno$$,
462 r_client_nfn_log.notification_id,
463 g_object_name,
464 g_pub_name,
465 l_error_msg,
466 l_return_status --Introduced new variable l_return_status since Defer
467 ); --process doesn't depend on this delete_record API
468 END IF;
469
470 /*** was delete/reject successful? ***/
471 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
472 /*** If No -> rollback ***/
473 CSM_UTIL_PKG.LOG
474 ( 'Deleting from inqueue failed, rolling back to savepoint'
475 || ' for PK ' || r_client_nfn_log.notification_id ,
476 g_object_name || '.APPLY_CLIENT_CHANGES',
477 FND_LOG.LEVEL_ERROR); -- put PK column here
478 ROLLBACK TO save_rec;
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 END IF;
481 END IF; /*** end of IF l_process_status = FND_API.G_RET_STS_SUCCESS ***/
482
483 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
484 /*** Record was not processed successfully or delete failed
485 -> defer and reject record ***/
486 CSM_UTIL_PKG.LOG
487 ( 'Record not processed successfully, deferring and rejecting record'
488 || ' for PK ' || r_client_nfn_log.notification_id ,
489 g_object_name || '.APPLY_CLIENT_CHANGES',
490 FND_LOG.LEVEL_ERROR); -- put PK column here
491
492 CSM_UTIL_PKG.DEFER_RECORD
493 ( p_user_name
494 , p_tranid
495 , r_client_nfn_log.seqno$$
496 , r_client_nfn_log.notification_id
497 , g_object_name
498 , g_pub_name
499 , l_error_msg
500 , l_process_status
501 , r_client_nfn_log.dmltype$$
502 );
503
504 /*** Was defer successful? ***/
505 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
506 /*** no -> rollback ***/
507 CSM_UTIL_PKG.LOG
508 ( 'Defer record failed, rolling back to savepoint'
509 || ' for PK ' || r_client_nfn_log.notification_id ,
510 g_object_name || '.APPLY_CLIENT_CHANGES',
511 FND_LOG.LEVEL_ERROR); -- put PK column here
512 ROLLBACK TO save_rec;
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 END IF;
515 END IF;
516
517 END LOOP;
518
519 EXCEPTION WHEN OTHERS THEN
520 /*** catch and log exceptions ***/
521 CSM_UTIL_PKG.LOG
522 ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
523 g_object_name || '.APPLY_CLIENT_CHANGES',
524 FND_LOG.LEVEL_EXCEPTION);
525 x_return_status := FND_API.G_RET_STS_ERROR;
526
527 END APPLY_CLIENT_CHANGES;
528
529
530 END CSM_CLIENT_NFN_LOG_PKG;