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