[Home] [Help]
PACKAGE BODY: APPS.CSM_NOTES_PKG
Source
1 PACKAGE BODY CSM_NOTES_PKG AS
2 /* $Header: csmunotb.pls 120.4.12020000.2 2013/04/09 11:04:25 saradhak ship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- Anurag 06/12/02 Created
7 -- --------- ------ ------------------------------------------
8 -- Enter procedure, function bodies as shown below
9
10
11 /*** Globals ***/
15
12 g_object_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_PKG'; -- package name
13 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_M_NOTES'; -- publication item name
14 G_DEBUG_LEVEL NUMBER; -- debug level
16 CURSOR c_notes( b_user_name VARCHAR2, b_tranid NUMBER) is
17 SELECT *
18 FROM csf_m_notes_inq
19 WHERE tranid$$ = b_tranid
20 AND clid$$cs = b_user_name;
21
22
23 /***
24 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
25 ***/
26 PROCEDURE APPLY_INSERT
27 (
28 p_record IN c_notes%ROWTYPE,
29 p_error_msg OUT NOCOPY VARCHAR2,
30 x_return_status IN OUT NOCOPY VARCHAR2
31 ) IS
32
33 l_msg_count number;
34 l_msg_data varchar2(1024);
35
36 l_jtf_note_id number;
37
38 BEGIN
39
40 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_INSERT:'
41 || ' for PK ' || p_record.jtf_note_id ,
42 'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
43
44 --create the note
45 CSM_UTIL_PKG.log( 'Creating Note ' || g_object_name || '.APPLY_INSERT:'
46 || ' for PK ' || p_record.jtf_note_id ,
47 'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_EVENT);
48 jtf_notes_pub.Create_note
49 ( p_api_version => 1.0
50 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
51 , p_init_msg_list => FND_API.G_TRUE
52 , p_commit => FND_API.G_FALSE
53 , x_return_status => x_return_status
54 , x_msg_count => l_msg_count
55 , x_msg_data => l_msg_data
56 , p_jtf_note_id => p_record.jtf_note_id
57 , p_source_object_id => p_record.source_object_id
58 , p_source_object_code => p_record.source_object_code
59 , p_notes => p_record.notes
60 , p_notes_detail => p_record.notes_detail
61 , p_note_status => p_record.note_status
62 , p_note_type => p_record.note_type
63 , p_entered_by => p_record.entered_by
64 , p_entered_date => p_record.entered_date
65 , p_created_by => NVL(p_record.created_by,FND_GLOBAL.USER_ID) --12.1
66 , p_creation_date => SYSDATE
67 , p_last_updated_by => NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
68 , p_last_update_date => SYSDATE
69 , p_last_update_login => FND_GLOBAL.LOGIN_ID
70 , x_jtf_note_id => l_jtf_note_id
71 );
72
73 if x_return_status <> FND_API.G_RET_STS_SUCCESS
74 then
75 /*** exception occurred in API -> return errmsg ***/
76 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
77 (
78 p_api_error => TRUE
79 );
80 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
81 || ' ROOT ERROR: jtf_notes_pub.create_note'
82 || ' for PK ' || p_record.JTF_NOTE_ID ,'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
83
84 x_return_status := FND_API.G_RET_STS_ERROR;
85 else
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87 end if;
88
89 exception
90 when others then
91 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
92 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
93 (
94 p_api_error => TRUE
95 );
96 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
97 || ' for PK ' || p_record.jtf_note_id , 'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
98 x_return_status := FND_API.G_RET_STS_ERROR;
99
100 END APPLY_INSERT;
101
102
103 /***
104 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
105 ***/
106 PROCEDURE APPLY_UPDATE
107 (
108 p_record IN c_notes%ROWTYPE,
109 p_error_msg OUT NOCOPY VARCHAR2,
110 x_return_status IN OUT NOCOPY VARCHAR2
111 ) IS
112
113 l_msg_count number;
114 l_msg_data varchar2(1024);
115
116 l_server_last_update_date date;
117 CURSOR l_server_last_update_date_csr(p_jtf_note_id jtf_notes_b.jtf_note_id%TYPE)
118 IS
119 select last_update_date from jtf_notes_b
120 where jtf_note_id = p_jtf_note_id;
121
122 BEGIN
123 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_UPDATE:'
124 || ' for PK ' || p_record.jtf_note_id,
125 'CSM_NOTES_PKG.APPLY_UPDATE',
126 FND_LOG.LEVEL_PROCEDURE );
127 --get the last update date of the note
128
129 --select last_update_date into l_server_last_update_date
130 --from jtf_notes_b
131 --where jtf_note_id = p_record.jtf_note_id;
132
133 OPEN l_server_last_update_date_csr(p_record.jtf_note_id);
134 FETCH l_server_last_update_date_csr INTO l_server_last_update_date;
135 CLOSE l_server_last_update_date_csr;
136
137 --check for the stale data
138 -- SERVER_WINS profile value
139 if(fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE)
140 = csm_profile_pkg.g_SERVER_WINS) then
141 if(l_server_last_update_date <> p_record.server_last_update_date) then
142 x_return_status := FND_API.G_RET_STS_ERROR;
143 p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_NOTES_PKG.APPLY_UPDATE: P_KEY = '
144 || p_record.jtf_note_id;
145 csm_util_pkg.log(p_error_msg,'CSM_NOTES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EVENT );
146 return;
147 end if;
148 end if;
149
150 --CLIENT_WINS (or client is allowd to update the record)
151
152 --update the note
153 jtf_notes_pub.Update_note
157 , p_commit => FND_API.G_FALSE
154 ( p_api_version => 1.0
155 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
156 , p_init_msg_list => FND_API.G_TRUE
158 , x_return_status => x_return_status
159 , x_msg_count => l_msg_count
160 , x_msg_data => l_msg_data
161 , p_jtf_note_id => p_record.jtf_note_id
162 , p_notes => p_record.notes
163 , p_notes_detail => p_record.notes_detail
164 , p_note_status => p_record.note_status
165 , p_note_type => p_record.note_type
166 , p_entered_by => p_record.entered_by
167 , p_last_updated_by => NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
168 , p_last_update_date => SYSDATE
169 , p_last_update_login => FND_GLOBAL.LOGIN_ID
170 );
171
172 if x_return_status <> FND_API.G_RET_STS_SUCCESS
173 then
174 /*** exception occurred in API -> return errmsg ***/
175 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
176 (
177 p_api_error => TRUE
178 );
179 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
180 || ' ROOT ERROR: jtf_notes_pub.update_note'
181 || ' for PK ' || p_record.JTF_NOTE_ID ,'CSM_NOTES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
182
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 else
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186 end if;
187
188 CSM_UTIL_PKG.log( 'Exiting ' || g_object_name || '.APPLY_UPDATE:'
189 || ' for PK ' || p_record.jtf_note_id,
190 'CSM_NOTES_PKG.APPLY_UPDATE',
191 FND_LOG.LEVEL_PROCEDURE );
192 exception
193 when others then
194 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
195 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
196 (
197 p_api_error => TRUE
198 );
199
200 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
201 || ' for PK ' || p_record.jtf_note_id,
202 'CSM_NOTES_PKG.APPLY_UPDATE',
203 FND_LOG.LEVEL_ERROR );
204
205 x_return_status := FND_API.G_RET_STS_ERROR;
206 END APPLY_UPDATE;
207
208 /***
209 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
210 ***/
211 PROCEDURE APPLY_RECORD
212 (
213 p_record IN c_notes%ROWTYPE,
214 p_error_msg OUT NOCOPY VARCHAR2,
215 x_return_status IN OUT NOCOPY VARCHAR2
216 ) IS
217 BEGIN
218 /*** initialize return status and message list ***/
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220 FND_MSG_PUB.INITIALIZE;
221
222 IF p_record.dmltype$$='I' THEN
223 -- Process insert
224 APPLY_INSERT
225 (
226 p_record,
227 p_error_msg,
228 x_return_status
229 );
230 ELSE /*IF p_record.dmltype$$='U' THEN
231 -- Process update
232 APPLY_UPDATE
233 (
234 p_record,
235 p_error_msg,
236 x_return_status
237 );
238 ELSE*/
239 -- Process delete; not supported for this entity
240 CSM_UTIL_PKG.LOG
241 ( 'Update and Delete is not supported for this entity'
242 || ' for PK ' || p_record.jtf_note_id,
243 'CSM_NOTES_PKG.APPLY_RECORD',
244 FND_LOG.LEVEL_EVENT );
245
246 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
247 (
248 p_message => 'CSM_DML_OPERATION'
249 , p_token_name1 => 'DML'
250 , p_token_value1 => p_record.dmltype$$
251 );
252
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 END IF;
255
256 EXCEPTION WHEN OTHERS THEN
257 /*** defer record when any process exception occurs ***/
258 CSM_UTIL_PKG.LOG
259 ( 'Exception occurred in CSM_notes_PKG.APPLY_RECORD:' || ' ' || sqlerrm
260 || ' for PK ' || p_record.jtf_note_id,
261 'CSM_NOTES_PKG.APPLY_RECORD',
262 FND_LOG.LEVEL_ERROR );
263
264 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
265 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
266 (
267 p_api_error => TRUE
268 );
269
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 END APPLY_RECORD;
272
273 /***
274 This procedure is called by CSM_UTIL_PKG when publication item <replace>
275 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
276 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
277 public APIs.
278 ***/
279 PROCEDURE APPLY_CLIENT_CHANGES
280 (
281 p_user_name IN VARCHAR2,
282 p_tranid IN NUMBER,
283 p_debug_level IN NUMBER,
284 x_return_status IN OUT NOCOPY VARCHAR2
285 ) IS
286
287 l_process_status VARCHAR2(1);
288 l_error_msg VARCHAR2(4000);
289 BEGIN
290 g_debug_level := p_debug_level;
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292
293
294
295 /*** loop through debrief labor records in inqueue ***/
296 FOR r_notes IN c_notes( p_user_name, p_tranid) LOOP
297
298 SAVEPOINT save_rec;
299
300 /*** apply record ***/
301 APPLY_RECORD
302 (
303 r_notes
304 , l_error_msg
305 , l_process_status
306 );
307
308 /*** was record processed successfully? ***/
309 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
310 /*** Yes -> delete record from inqueue ***/
311
312 CSM_UTIL_PKG.DELETE_RECORD
313 (
314 p_user_name,
315 p_tranid,
316 r_notes.seqno$$,
317 r_notes.jtf_note_id,
318 g_object_name,
319 g_pub_name,
320 l_error_msg,
321 l_process_status
322 );
323
324 /*** was delete successful? ***/
325 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
326 /*** no -> rollback ***/
327 CSM_UTIL_PKG.LOG
328 ( 'Deleting from inqueue failed, rolling back to savepoint'
329 || ' for PK ' || r_notes.jtf_note_id,
330 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
331 FND_LOG.LEVEL_EVENT ); -- put PK column here
332 ROLLBACK TO save_rec;
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 END IF;
335 END IF;
336
337 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
338 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
339 CSM_UTIL_PKG.LOG
340 ( 'Record not processed successfully, deferring and rejecting record'
341 || ' for PK ' || r_notes.jtf_note_id,
342 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
343 FND_LOG.LEVEL_EVENT); -- put PK column here
344
345 CSM_UTIL_PKG.DEFER_RECORD
346 (
347 p_user_name
348 , p_tranid
349 , r_notes.seqno$$
350 , r_notes.jtf_note_id
351 , g_object_name
352 , g_pub_name
353 , l_error_msg
354 , l_process_status
355 , r_notes.dmltype$$
356 );
357
358 /*** Was defer successful? ***/
359 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
360 /*** no -> rollback ***/
361 CSM_UTIL_PKG.LOG
362 ( 'Defer record failed, rolling back to savepoint'
363 || ' for PK ' || r_notes.jtf_note_id,
364 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
365 FND_LOG.LEVEL_EVENT ); -- put PK column here
366 ROLLBACK TO save_rec;
367 x_return_status := FND_API.G_RET_STS_ERROR;
368 END IF;
369 END IF;
370
371 END LOOP;
372
373 EXCEPTION WHEN OTHERS THEN
374 /*** catch and log exceptions ***/
375 CSM_UTIL_PKG.LOG
376 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm ,
377 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
378 FND_LOG.LEVEL_ERROR
379 );
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 END APPLY_CLIENT_CHANGES;
382
383 END CSM_NOTES_PKG;
384