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