eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSM_AUTO_SYNC_PKG

Source


1 PACKAGE BODY CSM_AUTO_SYNC_PKG AS
2 /* $Header: csmuasb.pls 120.6 2009/08/06 06:32:45 hbeeram noship $ */
3 
4   /*
5    * The function is to upload csm_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_PKG';  -- package name
18 g_pub_name     CONSTANT VARCHAR2(30) := 'CSM_AUTO_SYNC';  -- publication item name
19 g_debug_level           NUMBER; -- debug level
20 
21 g_auto_sync_acc_table_name        CONSTANT VARCHAR2(30) := 'CSM_AUTO_SYNC_ACC';
22 g_auto_sync_table_name            CONSTANT VARCHAR2(30) := 'CSM_AUTO_SYNC';
23 g_auto_sync_seq_name              CONSTANT VARCHAR2(30) := 'CSM_AUTO_SYNC_ACC_S';
24 g_auto_sync_pk1_name              CONSTANT VARCHAR2(30) := 'ID';
25 g_auto_sync_pk2_name              CONSTANT VARCHAR2(30) := 'AUTO_SYNC_NUM';
26 g_auto_sync_pubi_name             CONSTANT CSM_ACC_PKG.t_publication_item_list :=
27   CSM_ACC_PKG.t_publication_item_list('CSM_AUTO_SYNC');
28 
29 
30 CURSOR c_auto_sync_inq( b_user_name VARCHAR2, b_tranid NUMBER) IS
31   SELECT *
32   FROM  csm_auto_sync_inq
33   WHERE tranid$$ = b_tranid
34   AND   clid$$cs = b_user_name;
35 
36 
37 PROCEDURE INSERT_AUTO_SYNC_ACC (p_id NUMBER, p_user_id   IN fnd_user.user_id%TYPE,p_auto_sync_num NUMBER)
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_pubi_name
44      ,P_ACC_TABLE_NAME         => g_auto_sync_acc_table_name
45      ,P_SEQ_NAME               => g_auto_sync_seq_name
46      ,P_PK1_NAME               => g_auto_sync_pk1_name
47      ,P_PK1_NUM_VALUE          => p_id
48      ,P_USER_ID                => p_user_id
49      ,P_PK2_NAME               => g_auto_sync_pk2_name
50      ,P_PK2_NUM_VALUE          => p_auto_sync_num
51     );
52 EXCEPTION WHEN OTHERS THEN
53   CSM_UTIL_PKG.LOG( 'Exception occurred in ' || g_object_name || '.INSERT_AUTO_SYNC_ACC: '
54 || SQLERRM,' for PK ' || to_char(p_id) || g_object_name || '.INSERT_AUTO_SYNC_ACC',FND_LOG.LEVEL_EXCEPTION);
55 
56   RAISE;
57 END INSERT_AUTO_SYNC_ACC;-- end INSERT_AUTO_SYNC_ACC
58 
59 /******************************************************************************************************************/
60 PROCEDURE AUTO_SYNC_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_id(b_user_id fnd_user.user_id%TYPE) IS
69  SELECT id, auto_sync_num
70  FROM   csm_auto_sync;
71 
72 BEGIN
73    CSM_UTIL_PKG.LOG('Entering AUTO_SYNC_ACC_PROCESSOR for user_id: ' || p_user_id,
74                                    g_object_name || '.AUTO_SYNC_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
75 
76 
77   -- get all notifications in which user is a recipient
78   FOR l_rec IN c_id(p_user_id)
79   LOOP
80    INSERT_AUTO_SYNC_ACC (l_rec.id, p_user_id,l_rec.auto_sync_num);
81   END LOOP;
82 
83 
84     CSM_UTIL_PKG.LOG('Leaving AUTO_SYNC_ACC_PROCESSOR for user_id: ' || p_user_id,
85                                    g_object_name || '.AUTO_SYNC_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
86 EXCEPTION
87   	WHEN OTHERS THEN
88         l_sqlerrno := to_char(SQLCODE);
89         l_sqlerrmsg := substr(SQLERRM, 1,2000);
90         l_error_msg := ' Exception in  AUTO_SYNC_ACC_PROCESSOR for for user_id: ' || p_user_id
91                        || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
92         CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_AUTO_SYNC_PKG.AUTO_SYNC_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
93         RAISE;
94 
95 END AUTO_SYNC_ACC_PROCESSOR;
96 /******************************************************************************************************************/
97 
98 -------
99 
100 /***
101   This procedure is called by APPLY_CRECORD when
102   an inserted record is to be processed.
103 ***/
104 PROCEDURE APPLY_INSERT
105          (
106            p_record        IN c_auto_sync_inq%ROWTYPE,
107            p_error_msg     OUT NOCOPY    VARCHAR2,
108            x_return_status IN OUT NOCOPY VARCHAR2,
109            x_reject_row    OUT NOCOPY BOOLEAN
110          ) IS
111 
112 
113   l_id    csm_auto_sync.id%TYPE  ;
114   l_flag  csm_auto_sync.flag%TYPE;
115   l_rowid ROWID;
116 
117  CURSOR c_record_exists
118   (b_id NUMBER
119   )IS SELECT ROWID
120       FROM   CSM_AUTO_SYNC
121       WHERE  id = b_id;
122 
123 
124 
125 BEGIN
126 
127 
128 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_INSERT:'
129                || ' for PK ' || p_record.id,
130                'CSM_AUTO_SYNC.APPLY_INSERT',
131                FND_LOG.LEVEL_PROCEDURE );
132 
133 /***************************************************************************
134   ** Initialize API return status to success
135   ***************************************************************************/
136   x_return_status := FND_API.G_RET_STS_SUCCESS;
137 
138 /*
139 insert into csm_auto_sync(ID,FLAG,AUTO_SYNC_NUM,DOWNLOAD_ONLY,CREATION_DATE ,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY)
140  values (p_record.id,p_record.flag,p_record.auto_sync_num,p_record.download_only,p_record.creation_date ,p_record.created_by,p_record.last_update_date,p_record.last_updated_by)
141 RETURNING ID INTO l_id;
142 */
143 /***************************************************************************
144   ** Check whether the insert was succesfull
145   ***************************************************************************/
146   IF (c_record_exists%ISOPEN)THEN
147     CLOSE c_record_exists;
148   END IF;
149 
150   OPEN c_record_exists(l_id);
151   FETCH c_record_exists INTO l_rowid;
152   IF (c_record_exists%NOTFOUND)THEN
153     IF (c_record_exists%ISOPEN)
154     THEN
155       CLOSE c_record_exists;
156     END IF;
157     RAISE no_data_found;
158   END IF;
159 
160   IF (c_record_exists%ISOPEN) THEN
161     CLOSE c_record_exists;
162   END IF;
163 
164 
165 
166   IF x_return_status <> FND_API.G_RET_STS_SUCCESS
167   THEN
168     x_return_status := FND_API.G_RET_STS_ERROR;
169 
170     CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
171       || ' ROOT ERROR: create statement'
172       || ' for PK ' || p_record.ID,
173       g_object_name || '.APPLY_INSERT',
174       FND_LOG.LEVEL_ERROR );
175     RETURN ;
176   END IF;
177 
178   -- success
179   x_return_status := FND_API.G_RET_STS_SUCCESS;
180 
181 EXCEPTION
182   WHEN others THEN
183      CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
184        || ' for PK ' || p_record.id,
185        g_object_name || '.APPLY_INSERT',
186        FND_LOG.LEVEL_EXCEPTION );
187 
188      x_return_status := FND_API.G_RET_STS_ERROR;
189 
190 END APPLY_INSERT;
191 
192 
193 /***
194   This procedure is called by APPLY_CRECORD when
195   an updated record is to be processed.
196   For CSM 11583, we support updates on the DFF columns
197 ***/
198 PROCEDURE APPLY_UPDATE
199          (
200            p_record        IN c_auto_sync_inq%ROWTYPE,
201            p_error_msg     OUT NOCOPY    VARCHAR2,
202            x_return_status IN OUT NOCOPY VARCHAR2
203          )
204 IS
205 --variable declarations
206 
207   l_id    csm_auto_sync.id%TYPE  ;
208   l_auto_sync_num csm_auto_sync.auto_sync_num%TYPE;
209   l_user_id asg_user.user_id%TYPE;
210 
211   l_rowid ROWID;
212 
213 BEGIN
214 
215 
216 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_UPDATE:'
217                || ' for PK ' || p_record.id,
218                'CSM_AUTO_SYNC.APPLY_UPDATE',
219                FND_LOG.LEVEL_PROCEDURE );
220   /***************************************************************************
221   ** Initialize API return status to success
222   ***************************************************************************/
223   x_return_status := FND_API.G_RET_STS_SUCCESS;
224 
225 l_user_id := asg_base.get_user_id(p_record.clid$$cs);
226 l_auto_sync_num := p_record.auto_sync_num;
227 l_id := p_record.id;
228 
229 /***************************************************************************
230   ** Update the record
231   ***************************************************************************/
232 
233 UPDATE csm_auto_sync_acc
234 SET AUTO_SYNC_NUM = l_auto_sync_num,
235 LAST_UPDATE_DATE = sysdate,
236 LAST_UPDATED_BY	= l_user_id
237 where ID=l_id
238 and USER_ID = l_user_id;
239 
240 
241 
242  /***************************************************************************
243   ** Check if the update was succesful
244   ***************************************************************************/
245 
246   IF (SQL%NOTFOUND)
247   THEN
248     RAISE NO_DATA_FOUND;
249   END IF;
250 
251   -- success
252   x_return_status := FND_API.G_RET_STS_SUCCESS;
253 
254 EXCEPTION
255 
256     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
257     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 
259   WHEN others THEN
260      CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
261        || ' for PK ' || p_record.id,
262        g_object_name || '.APPLY_UPDATE',
263        FND_LOG.LEVEL_EXCEPTION );
264 
265      x_return_status := FND_API.G_RET_STS_ERROR;
266 
267 END APPLY_UPDATE;
268 
269 
270 
271 /*****************************************************************************************/
272 
273 
274 
275 PROCEDURE APPLY_RECORD
276          (
277            p_record        IN     c_auto_sync_inq%ROWTYPE,
278            p_error_msg     OUT NOCOPY    VARCHAR2,
279            x_return_status IN OUT NOCOPY VARCHAR2,
280            x_reject_row    OUT NOCOPY BOOLEAN
281          ) IS
282 BEGIN
283 
284   /*** initialize return status and message list ***/
285   x_return_status := FND_API.G_RET_STS_SUCCESS;
286 
287   FND_MSG_PUB.INITIALIZE;
288 
289 
290   IF p_record.dmltype$$='I' THEN
291     -- Process insert
292     APPLY_INSERT
293       (
294         p_record,
295         p_error_msg,
296         x_return_status,
297         x_reject_row         --Bug 5288413
298       );
299   ELSIF p_record.dmltype$$='U' THEN -- YLIAO: for 11583, we do support UPDATE
300     -- Process update
301     APPLY_UPDATE
302       (
303         p_record,
304         p_error_msg,
305         x_return_status
306       );
307   ELSE
308     -- Process delete and insert;
309     -- Not supported for this entity
310     CSM_UTIL_PKG.LOG
311       ( 'Delete and Update is not supported for this entity'
312         || ' for PK ' || p_record.id ,
313         g_object_name || '.APPLY_RECORD',
314         FND_LOG.LEVEL_ERROR);
315 
316     p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
317       (
318         p_message        => 'CSM_DML_OPERATION'
319       , p_token_name1    => 'DML'
320       , p_token_value1   => p_record.dmltype$$
321       );
322 
323     x_return_status := FND_API.G_RET_STS_ERROR;
324   END IF;
325 
326 EXCEPTION
327 WHEN OTHERS THEN
328   /*** defer record when any process exception occurs ***/
329   CSM_UTIL_PKG.LOG
330     ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || SQLERRM
331       || ' for PK ' || p_record.id ,
332       g_object_name || '.APPLY_RECORD',
333       FND_LOG.LEVEL_EXCEPTION);
334   -- temp -- find more detail --remove comment
335   fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', SQLERRM);
336   p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
337     (
338       p_api_error      => TRUE
339     );
340 
341   x_return_status := FND_API.G_RET_STS_ERROR;
342 
343 END APPLY_RECORD;
344 ----------------------------------------------------------------------------------------------------------------------
345 
346 /***
347   APPLY_CLIENT_CHANGE procedure is called by CSM_SERVICEP_WRAPPER_PKG, for upward sync of
348   publication item CSM_AUTO_SYNC
349 ***/
350 PROCEDURE APPLY_CLIENT_CHANGES
351          (
352            p_user_name     IN VARCHAR2,
353            p_tranid        IN NUMBER,
354            p_debug_level   IN NUMBER,
355            x_return_status IN OUT NOCOPY VARCHAR2
356          )
357   IS
358   l_process_status VARCHAR2(1);
359   l_return_status  VARCHAR2(1);
360   l_error_msg      VARCHAR2(4000);
361   l_reject_row     boolean;
362 BEGIN
363   csm_util_pkg.log ( g_object_name || '.APPLY_CLIENT_CHANGES entered',
364     FND_LOG.LEVEL_PROCEDURE);
365   g_debug_level := p_debug_level;
366   x_return_status := FND_API.G_RET_STS_SUCCESS;
367 
368   /*** loop through records in inqueue ***/
369   FOR r_auto_sync IN c_auto_sync_inq( p_user_name, p_tranid) LOOP
370 
371     SAVEPOINT save_rec;
372 /**--------------------------------------------**/
373     /*** apply record ***/
374     APPLY_RECORD
375       (
376         r_auto_sync
377       , l_error_msg
378       , l_process_status
379       , l_reject_row
380       );
381 /***--------------------------------------------------------------**/
382     /*** was record processed successfully? ***/
383     IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
384       /*** If Yes -> delete record from inqueue ***/
385       IF l_reject_row THEN
386        CSM_UTIL_PKG.REJECT_RECORD
387         (
388           p_user_name,
389           p_tranid,
390           r_auto_sync.seqno$$,
391           r_auto_sync.id,
392           g_object_name,
393           g_pub_name,
394           l_error_msg,
395           l_return_status
396         );
397       ELSE
398        CSM_UTIL_PKG.DELETE_RECORD
399         (
400           p_user_name,
401           p_tranid,
402           r_auto_sync.seqno$$,
403           r_auto_sync.id,
404           g_object_name,
405           g_pub_name,
406           l_error_msg,
407           l_return_status --Introduced new variable l_return_status since Defer
408         );                --process doesn't depend on this delete_record API
409       END IF;
410 
411       /*** was delete/reject successful? ***/
412       IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
413         /*** If No -> rollback ***/
414         CSM_UTIL_PKG.LOG
415         ( 'Deleting from inqueue failed, rolling back to savepoint'
416           || ' for PK ' || r_auto_sync.id ,
417           g_object_name || '.APPLY_CLIENT_CHANGES',
418           FND_LOG.LEVEL_ERROR); -- put PK column here
419         ROLLBACK TO save_rec;
420         x_return_status := FND_API.G_RET_STS_ERROR;
421       END IF;
422     END IF; /*** end of IF l_process_status = FND_API.G_RET_STS_SUCCESS  ***/
423 
424     IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
425       /*** Record was not processed successfully or delete failed
426       -> defer and reject record ***/
427       CSM_UTIL_PKG.LOG
428       ( 'Record not processed successfully, deferring and rejecting record'
429         || ' for PK ' || r_auto_sync.id ,
430         g_object_name || '.APPLY_CLIENT_CHANGES',
431         FND_LOG.LEVEL_ERROR); -- put PK column here
432 
433       CSM_UTIL_PKG.DEFER_RECORD
434        ( p_user_name
435        , p_tranid
436        , r_auto_sync.seqno$$
437        , r_auto_sync.id
438        , g_object_name
439        , g_pub_name
440        , l_error_msg
441        , l_process_status
442        , r_auto_sync.dmltype$$
443        );
444 
445       /*** Was defer successful? ***/
446       IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
447         /*** no -> rollback ***/
448         CSM_UTIL_PKG.LOG
449         ( 'Defer record failed, rolling back to savepoint'
450           || ' for PK ' || r_auto_sync.id ,
451           g_object_name || '.APPLY_CLIENT_CHANGES',
452           FND_LOG.LEVEL_ERROR); -- put PK column here
453         ROLLBACK TO save_rec;
454         x_return_status := FND_API.G_RET_STS_ERROR;
455       END IF;
456     END IF;
457 
458   END LOOP;
459 
460 EXCEPTION WHEN OTHERS THEN
461   /*** catch and log exceptions ***/
462   CSM_UTIL_PKG.LOG
463   ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
464     g_object_name || '.APPLY_CLIENT_CHANGES',
465     FND_LOG.LEVEL_EXCEPTION);
466   x_return_status := FND_API.G_RET_STS_ERROR;
467 
468 END APPLY_CLIENT_CHANGES;
469 
470 
471 END CSM_AUTO_SYNC_PKG;