[Home] [Help]
PACKAGE BODY: APPS.CSL_NOTES_PKG
Source
1 PACKAGE BODY CSL_NOTES_PKG AS
2 /* $Header: cslvntsb.pls 115.19 2002/11/08 14:00:18 asiegers ship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_NOTES_PKG'; -- package name
8 g_pub_name CONSTANT VARCHAR2(30) := 'JTF_NOTES_VL'; -- publication item name
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR c_note( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_JTF_NOTES_VL_inq
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 /***
18 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
19 ***/
20 PROCEDURE APPLY_INSERT
21 (
22 p_record IN c_note%ROWTYPE,
23 p_error_msg OUT NOCOPY VARCHAR2,
24 x_return_status IN OUT NOCOPY VARCHAR2
25 ) IS
26 l_note_id NUMBER;
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(240);
29 BEGIN
30 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
31 jtm_message_log_pkg.Log_Msg
32 ( v_object_id => p_record.jtf_note_id
33 , v_object_name => g_object_name
34 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
35 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
36 END IF;
37
38 -- Create a note
39 jtf_notes_pub.Create_note
40 ( p_api_version => 1.0
41 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
42 , p_init_msg_list => FND_API.G_TRUE
43 , p_commit => FND_API.G_FALSE
44 , x_return_status => x_return_status
45 , x_msg_count => l_msg_count
46 , x_msg_data => l_msg_data
47 , p_jtf_note_id => p_record.jtf_note_id
48 , p_parent_note_id => p_record.parent_note_id
49 , p_source_object_id => p_record.source_object_id
50 , p_source_object_code => p_record.source_object_code
51 , p_notes => p_record.notes
52 , p_note_status => p_record.note_status
53 , p_note_type => p_record.note_type
54 , p_entered_by => p_record.entered_by
55 , p_entered_date => p_record.entered_date
56 , p_created_by => p_record.entered_by
57 , p_creation_date => SYSDATE
58 , p_last_updated_by => FND_GLOBAL.USER_ID
59 , p_last_update_date => SYSDATE
60 , p_last_update_login => FND_GLOBAL.LOGIN_ID
61 , x_jtf_note_id => l_note_id
62 );
63
64 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
65 /*** exception occurred in API -> return errmsg ***/
66 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
67 (
68 p_api_error => TRUE
69 );
70 END IF;
71
72 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
73 jtm_message_log_pkg.Log_Msg
74 ( v_object_id => p_record.jtf_note_id
75 , v_object_name => g_object_name
76 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
77 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
78 END IF;
79
80 EXCEPTION WHEN OTHERS THEN
81 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
82 jtm_message_log_pkg.Log_Msg
83 ( v_object_id => p_record.jtf_note_id
84 , v_object_name => g_object_name
85 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
86 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
87 END IF;
88
89 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
90 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
91 (
92 p_api_error => TRUE
93 );
94
95 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
96 jtm_message_log_pkg.Log_Msg
97 ( v_object_id => p_record.jtf_note_id
98 , v_object_name => g_object_name
99 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
100 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
101 END IF;
102
103 x_return_status := FND_API.G_RET_STS_ERROR;
104 END APPLY_INSERT;
105
106 /***
107 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
108 ***/
109 PROCEDURE APPLY_UPDATE
110 (
111 p_record IN c_note%ROWTYPE,
112 p_error_msg OUT NOCOPY VARCHAR2,
113 x_return_status IN OUT NOCOPY VARCHAR2
114 ) IS
115 l_msg_count NUMBER;
116 l_msg_data VARCHAR2(240);
117 BEGIN
118 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
119 jtm_message_log_pkg.Log_Msg
120 ( v_object_id => p_record.jtf_note_id
121 , v_object_name => g_object_name
122 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
123 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
124 END IF;
125
126 -- Update the note
127 jtf_notes_pub.Update_note
128 ( p_api_version => 1.0
129 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
130 , p_init_msg_list => FND_API.G_TRUE
131 , p_commit => FND_API.G_FALSE
132 , x_return_status => x_return_status
133 , x_msg_count => l_msg_count
134 , x_msg_data => l_msg_data
135 , p_jtf_note_id => p_record.jtf_note_id
136 , p_notes => p_record.notes
137 , p_note_status => p_record.note_status
138 , p_note_type => p_record.note_type
139 , p_entered_by => p_record.entered_by
140 , p_last_updated_by => FND_GLOBAL.USER_ID
141 , p_last_update_date => SYSDATE
142 , p_last_update_login => FND_GLOBAL.LOGIN_ID
143 );
144
145 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
146 /*** exception occurred in API -> return errmsg ***/
147 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
148 (
149 p_api_error => TRUE
150 );
151 END IF;
152
153 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
154 jtm_message_log_pkg.Log_Msg
155 ( v_object_id => p_record.jtf_note_id
156 , v_object_name => g_object_name
157 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
158 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
159 END IF;
160
161 EXCEPTION WHEN OTHERS THEN
162 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
163 jtm_message_log_pkg.Log_Msg
164 ( v_object_id => p_record.jtf_note_id
165 , v_object_name => g_object_name
166 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
167 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
168 END IF;
169
170 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
171 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
172 (
173 p_api_error => TRUE
174 );
175
176 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
177 jtm_message_log_pkg.Log_Msg
178 ( v_object_id => p_record.jtf_note_id
179 , v_object_name => g_object_name
180 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
181 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
182 END IF;
183
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 END APPLY_UPDATE;
186
187 /***
188 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
189 ***/
190 PROCEDURE APPLY_RECORD
191 (
192 p_record IN c_note%ROWTYPE,
193 p_error_msg OUT NOCOPY VARCHAR2,
194 x_return_status IN OUT NOCOPY VARCHAR2
195 ) IS
196 BEGIN
197 /*** initialize return status and message list ***/
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199 FND_MSG_PUB.INITIALIZE;
200
201 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
202 jtm_message_log_pkg.Log_Msg
203 ( v_object_id => p_record.jtf_note_id
204 , v_object_name => g_object_name
205 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
206 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
207 END IF;
208
209 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
210 jtm_message_log_pkg.Log_Msg
211 ( v_object_id => p_record.jtf_note_id
212 , v_object_name => g_object_name
213 , v_message => 'Processing JTF_NOTE_ID = ' || p_record.jtf_note_id || fnd_global.local_chr(10) ||
214 'DMLTYPE = ' || p_record.dmltype$$
215 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
216 END IF;
217
218 IF p_record.dmltype$$='I' THEN
219 -- Process insert
220 APPLY_INSERT
221 (
222 p_record,
223 p_error_msg,
224 x_return_status
225 );
226 ELSIF p_record.dmltype$$='U' THEN
227 -- Process update
228 APPLY_UPDATE
229 (
230 p_record,
231 p_error_msg,
232 x_return_status
233 );
234 ELSIF p_record.dmltype$$='D' THEN
235 -- Process delete; not supported for this entity
236 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
237 jtm_message_log_pkg.Log_Msg
238 ( v_object_id => p_record.jtf_note_id
239 , v_object_name => g_object_name
240 , v_message => 'Delete is not supported for this entity'
241 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
242 END IF;
243
244 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
245 (
246 p_message => 'CSL_DML_OPERATION'
247 , p_token_name1 => 'DML'
248 , p_token_value1 => p_record.dmltype$$
249 );
250
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 ELSE
253 -- invalid dml type
254 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
255 jtm_message_log_pkg.Log_Msg
256 ( v_object_id => p_record.jtf_note_id
257 , v_object_name => g_object_name
258 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
259 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
260 END IF;
261
262 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
263 (
264 p_message => 'CSL_DML_OPERATION'
265 , p_token_name1 => 'DML'
266 , p_token_value1 => p_record.dmltype$$
267 );
268
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 END IF;
271
272 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
273 jtm_message_log_pkg.Log_Msg
274 ( v_object_id => p_record.jtf_note_id
275 , v_object_name => g_object_name
276 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
277 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
278 END IF;
279 EXCEPTION WHEN OTHERS THEN
280 /*** defer record when any process exception occurs ***/
281 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
282 jtm_message_log_pkg.Log_Msg
283 ( v_object_id => p_record.jtf_note_id
284 , v_object_name => g_object_name
285 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
286 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
287 END IF;
288
289 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
290 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
291 (
292 p_api_error => TRUE
293 );
294
295 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
296 jtm_message_log_pkg.Log_Msg
297 ( v_object_id => p_record.jtf_note_id
298 , v_object_name => g_object_name
299 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
300 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
301 END IF;
302
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 END APPLY_RECORD;
305
306 /***
307 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item JTF_NOTES_VL
308 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
309 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
310 public APIs.
311 ***/
312 PROCEDURE APPLY_CLIENT_CHANGES
313 (
314 p_user_name IN VARCHAR2,
315 p_tranid IN NUMBER,
316 p_debug_level IN NUMBER,
317 x_return_status IN OUT NOCOPY VARCHAR2
318 ) IS
319
320 l_process_status VARCHAR2(1);
321 l_error_msg VARCHAR2(4000);
322 BEGIN
323 g_debug_level := p_debug_level;
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
327 jtm_message_log_pkg.Log_Msg
328 ( v_object_id => null
329 , v_object_name => g_object_name
330 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
331 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
332 END IF;
333
334 /*** loop through jtf_notes_vl records in inqueue ***/
335 FOR r_note IN c_note( p_user_name, p_tranid) LOOP
336
337 SAVEPOINT save_rec;
338
339 /*** apply record ***/
340 APPLY_RECORD
341 (
342 r_note
343 , l_error_msg
344 , l_process_status
345 );
346
347 /*** was record processed successfully? ***/
351 jtm_message_log_pkg.Log_Msg
348 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
349 /*** Yes -> delete record from inqueue ***/
350 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
352 ( v_object_id => r_note.jtf_note_id
353 , v_object_name => g_object_name
354 , v_message => 'Record successfully processed, deleting from inqueue'
355 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
356 END IF;
357
358 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
359 (
360 p_user_name,
361 p_tranid,
362 r_note.seqno$$,
363 r_note.jtf_note_id,
364 g_object_name,
365 g_pub_name,
366 l_error_msg,
367 l_process_status
368 );
369
370 /*** was delete successful? ***/
371 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
372 /*** no -> rollback ***/
373 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
374 jtm_message_log_pkg.Log_Msg
375 ( v_object_id => r_note.jtf_note_id
376 , v_object_name => g_object_name
377 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
378 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
379 END IF;
380 ROLLBACK TO save_rec;
381 END IF;
382 END IF;
383
384 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
385 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
386 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
387 jtm_message_log_pkg.Log_Msg
388 ( v_object_id => r_note.jtf_note_id
389 , v_object_name => g_object_name
390 , v_message => 'Record not processed successfully, deferring and rejecting record'
391 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
392 END IF;
393
394 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
395 (
396 p_user_name
397 , p_tranid
398 , r_note.seqno$$
399 , r_note.jtf_note_id
400 , g_object_name
401 , g_pub_name
402 , l_error_msg
403 , l_process_status
404 );
405
406 /*** Was defer successful? ***/
407 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
408 /*** no -> rollback ***/
409 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
410 jtm_message_log_pkg.Log_Msg
411 ( v_object_id => r_note.jtf_note_id
412 , v_object_name => g_object_name
413 , v_message => 'Defer record failed, rolling back to savepoint'
414 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
415 END IF;
416 ROLLBACK TO save_rec;
417 END IF;
418 END IF;
419
420 END LOOP;
421
422 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
423 jtm_message_log_pkg.Log_Msg
424 ( v_object_id => null
425 , v_object_name => g_object_name
426 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
427 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
428 END IF;
429
430 EXCEPTION WHEN OTHERS THEN
431 /*** catch and log exceptions ***/
432 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
433 jtm_message_log_pkg.Log_Msg
434 ( v_object_id => null
435 , v_object_name => g_object_name
436 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
437 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
438 END IF;
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 END APPLY_CLIENT_CHANGES;
441
442 END CSL_NOTES_PKG;