[Home] [Help]
PACKAGE BODY: APPS.CSL_LOBS_PKG
Source
1 PACKAGE BODY CSL_LOBS_PKG AS
2 /* $Header: cslvlobb.pls 120.0 2005/05/24 17:51:22 appldev noship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_LOBS_PKG'; -- package name
8 g_pub_name CONSTANT VARCHAR2(30) := 'CSL_LOBS'; -- publication item name
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR C_FND_LOBS( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_LOBS_INQ
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 /***
18 This procedure is called by APPLY_RECORD when an inserted record is to be
19 processed.
20 ***/
21
22 PROCEDURE APPLY_INSERT
23 (
24 p_record IN C_FND_LOBS%ROWTYPE,
25 p_error_msg OUT NOCOPY VARCHAR2,
26 x_return_status IN OUT NOCOPY VARCHAR2
27 ) IS
28
29 -- Variables needed for public API
30 l_seq_num number;
31 l_category_id number;
32 l_file_id number;
33 l_debrief_header_id number;
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(240);
36 l_file_access_id number;
37 l_error_msg varchar(1024);
38
39 -- get the debrief header id for the task_assignment
40 -- use the first debrief header id
41 CURSOR l_debrief_header_id_csr(p_task_assignment_id IN number) IS
42 SELECT debrief_header_id FROM csf_debrief_headers
43 WHERE task_assignment_id = p_task_assignment_id
44 ORDER BY debrief_header_id;
45
46 -- get max seq no for the debrief_header_id
47 CURSOR l_max_seq_no_csr(p_debrief_header_id IN number)
48 IS
49 SELECT nvl(max(fad.seq_num),0)+10
50 FROM fnd_attached_documents fad, fnd_documents fd
51 WHERE fad.pk1_value=to_char(p_debrief_header_id)
52 AND fd.document_id = fad.document_id
53 AND EXISTS
54 (SELECT 1
55 FROM fnd_document_categories_tl cat_tl
56 WHERE cat_tl.category_id = fd.category_id
57 AND cat_tl.user_name = 'Signature'
58 );
59
60 -- get the category_id
61 CURSOR l_category_id_csr IS
62 SELECT category_id FROM fnd_document_categories_tl
63 WHERE user_name = 'Signature';
64
65 l_dummy number;
66 -- to check if the record exists in the database
67 CURSOR l_lobs_fileid_csr (p_file_id fnd_lobs.file_id%TYPE) IS
68 SELECT 1 FROM fnd_lobs WHERE file_id = p_file_id;
69
70 l_signature_loc blob;
71 l_signature_raw raw(32767);
72 l_signature_size number;
73
74 BEGIN
75
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 l_error_msg := 'Entering ' || g_object_name || '.APPLY_INSERT'
79 || ' for PK ' || to_char( p_record.FILE_ID);
80
81 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
82 jtm_message_log_pkg.Log_Msg
83 ( v_object_id => p_record.file_id
84 , v_object_name => g_object_name
85 , v_message => l_error_msg
86 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
87 END IF;
88
89 -- if debrief_header_id is not passed get the debrief header id
90 IF ( (p_record.pk1_value IS NULL) AND
91 (p_record.task_assignment_id IS NOT NULL) )
92 THEN
93 OPEN l_debrief_header_id_csr(p_record.task_assignment_id);
94 FETCH l_debrief_header_id_csr INTO l_debrief_header_id;
95 CLOSE l_debrief_header_id_csr;
96 ELSE
97 l_debrief_header_id := TO_NUMBER (p_record.pk1_value);
98 END IF;
99
100 -- get the max seq no
101 OPEN l_max_seq_no_csr(l_debrief_header_id);
102 FETCH l_max_seq_no_csr INTO l_seq_num;
103 CLOSE l_max_seq_no_csr;
104
105 -- get the category id for Signature
106 OPEN l_category_id_csr;
107 FETCH l_category_id_csr INTO l_category_id;
108 CLOSE l_category_id_csr;
109
110 -- API to create an attachment
111
112 --verify that the record does not already exist
113 OPEN l_lobs_fileid_csr(p_record.file_id) ;
114 FETCH l_lobs_fileid_csr into l_dummy;
115 IF l_lobs_fileid_csr%found THEN
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 p_error_msg := 'Duplicate Record: File id ' || to_char(p_record.file_id)
118 || ' already exists in fnd_lobs table';
119
120 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
121 jtm_message_log_pkg.Log_Msg
122 ( v_object_id => p_record.file_id
123 , v_object_name => g_object_name
124 , v_message => p_error_msg
125 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
126 END IF;
127
128 CLOSE l_lobs_fileid_csr;
129 RETURN;
130 END IF;
131 CLOSE l_lobs_fileid_csr;
132
133 BEGIN
134 INSERT INTO fnd_lobs(
135 file_id, file_name, file_content_type, file_data,
136 upload_date, language, file_format)
137 VALUES (p_record.file_id, 'INTERNAL', 'image/bmp', empty_blob(),
138 SYSDATE, p_record.language, 'binary')
139 RETURN file_data into l_signature_loc;
140
141 l_signature_size := dbms_lob.getLength(p_record.file_data);
142 dbms_lob.read(p_record.file_data, l_signature_size, 1, l_signature_raw);
143
144 dbms_lob.write(l_signature_loc, l_signature_size, 1, l_signature_raw);
145 EXCEPTION
146 WHEN OTHERS THEN
147 -- check if the record exists
148 open l_lobs_fileid_csr(p_record.file_id) ;
149 fetch l_lobs_fileid_csr into l_dummy;
150 if l_lobs_fileid_csr%found then
151 --the record exists. Dont show any error.
152 null;
153 else
154 --record could not be inserted, throw the exception
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 raise;
157 end if;
158 close l_lobs_fileid_csr;
159
160 END;
161 fnd_webattch.add_attachment(
162 seq_num => l_seq_num,
163 category_id => l_category_id,
164 document_description => p_record.description,
165 datatype_id => 6,
166 text => NULL,
167 file_name => 'INTERNAL',
168 url => NULL,
169 function_name => 'CSFFEDBF',
170 entity_name => 'CSF_DEBRIEF_HEADERS',
171 pk1_value => l_debrief_header_id,
172 pk2_value => NULL,
173 pk3_value => NULL,
174 pk4_value => NULL,
175 pk5_value => NULL,
176 media_id => p_record.file_id,
177 user_id => fnd_global.login_id);
178
179
180 l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'
181 || ' for PK ' || to_char (p_record.FILE_ID );
182
183 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
184 jtm_message_log_pkg.Log_Msg
185 ( v_object_id => p_record.file_id
186 , v_object_name => g_object_name
187 , v_message => l_error_msg
188 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
189 END IF;
190
191
192 EXCEPTION
193 WHEN OTHERS THEN
194
195 l_error_msg := 'Exception occurred in ' || g_object_name
196 || '.APPLY_INSERT:' || ' ' || sqlerrm
197 || ' for PK ' || to_char (p_record.FILE_ID );
198
199 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
200 jtm_message_log_pkg.Log_Msg
201 ( v_object_id => p_record.file_id
202 , v_object_name => g_object_name
203 , v_message => l_error_msg
204 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
205 END IF;
206
207 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
208
209 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
210 (
211 p_api_error => TRUE
212 );
213
214 l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'
215 || ' for PK ' || to_char (p_record.FILE_ID );
216
217 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
218 jtm_message_log_pkg.Log_Msg
219 ( v_object_id => p_record.file_id
220 , v_object_name => g_object_name
221 , v_message => l_error_msg
222 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
223 END IF;
224
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 END APPLY_INSERT;
227
228
229 /***
230 This procedure is called by APPLY_CLIENT_CHANGES for every record in
231 in-queue that needs to be processed.
232 ***/
233
234 PROCEDURE APPLY_RECORD
235 (
236 p_record IN c_FND_LOBS%ROWTYPE,
237 p_error_msg OUT NOCOPY VARCHAR2,
238 x_return_status IN OUT NOCOPY VARCHAR2
239 ) IS
240 l_error_msg varchar(1024);
241 BEGIN
242 /*** initialize return status and message list ***/
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244 FND_MSG_PUB.INITIALIZE;
245
246
247 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
248 jtm_message_log_pkg.Log_Msg
249 ( v_object_id => p_record.file_id
250 , v_object_name => g_object_name
251 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
252 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
253 END IF;
254
255 l_error_msg := 'Processing ' || g_object_name || ' for PK '
256 || to_char (p_record.FILE_ID) || ' ' || 'DMLTYPE = '
257 || p_record.dmltype$$ ;
258
259 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
260 jtm_message_log_pkg.Log_Msg
261 ( v_object_id => p_record.file_id
262 , v_object_name => g_object_name
263 , v_message => l_error_msg
264 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
265 END IF;
266
267 IF p_record.dmltype$$='I' THEN
268 -- Process insert
269 APPLY_INSERT
270 (
271 p_record,
272 p_error_msg,
273 x_return_status
274 );
275 ELSIF p_record.dmltype$$='U' THEN
276
277 -- Process update; not supported for this entity
278
279 l_error_msg := 'Update is not supported for this entity ' || g_object_name;
280
281 jtm_message_log_pkg.Log_Msg
282 ( v_object_id => p_record.file_id
283 , v_object_name => g_object_name
284 , v_message => l_error_msg
285 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
286
287 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
288 (
289 p_message => 'CSL_DML_OPERATION'
290 , p_token_name1 => 'DML'
291 , p_token_value1 => p_record.dmltype$$
292 );
293
294 x_return_status := FND_API.G_RET_STS_ERROR;
295
296 ELSIF p_record.dmltype$$='D' THEN
297 -- Process delete; not supported for this entity
298
299 l_error_msg := 'Delete is not supported for this entity ' || g_object_name ;
300
301 jtm_message_log_pkg.Log_Msg
302 ( v_object_id => p_record.file_id
303 , v_object_name => g_object_name
304 , v_message => l_error_msg
305 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
306
307 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
308 (
309 p_message => 'CSL_DML_OPERATION'
310 , p_token_name1 => 'DML'
311 , p_token_value1 => p_record.dmltype$$
312 );
313
314 x_return_status := FND_API.G_RET_STS_ERROR;
315 ELSE
316 -- invalid dml type
317
318 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
319 jtm_message_log_pkg.Log_Msg
320 ( v_object_id => p_record.task_assignment_id
321 , v_object_name => g_object_name
322 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
323 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
324 END IF;
325
326 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
327 (
328 p_message => 'CSL_DML_OPERATION'
329 , p_token_name1 => 'DML'
330 , p_token_value1 => p_record.dmltype$$
331 );
332
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 END IF;
335
336 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
337 jtm_message_log_pkg.Log_Msg
338 ( v_object_id => p_record.file_id
339 , v_object_name => g_object_name
340 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
341 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
342 END IF;
343
344
345 EXCEPTION WHEN OTHERS THEN
346 /*** defer record when any process exception occurs ***/
347
348 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
349 jtm_message_log_pkg.Log_Msg
350 ( v_object_id => p_record.file_id
351 , v_object_name => g_object_name
352 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
353 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
354 END IF;
355
356 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
357 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
358 (
359 p_api_error => TRUE
360 );
361
362 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
363 jtm_message_log_pkg.Log_Msg
364 ( v_object_id => p_record.file_id
365 , v_object_name => g_object_name
366 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
367 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
368 END IF;
369
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 END APPLY_RECORD;
372
373
374 /***
375 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication
376 item CSL_LOBS is dirty. This happens when a mobile field service device
377 executed DML on an updatable table and did a fast sync. This procedure
378 will insert the data that came from mobile into the backend tables using
379 public APIs.
380 ***/
381
382 PROCEDURE APPLY_CLIENT_CHANGES
383 (
384 p_user_name IN VARCHAR2,
385 p_tranid IN NUMBER,
386 p_debug_level IN NUMBER,
387 x_return_status IN OUT NOCOPY VARCHAR2
388 ) IS
389
390 l_process_status VARCHAR2(1);
391 l_error_msg VARCHAR2(4000);
392 BEGIN
393 g_debug_level := p_debug_level;
394 x_return_status := FND_API.G_RET_STS_SUCCESS;
395
396 jtm_message_log_pkg.Log_Msg
397 ( v_object_id => null
398 , v_object_name => g_object_name
399 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
400 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
401 );
402
403 /*** loop through CSL_LOBS records in inqueue ***/
404 FOR r_FND_LOBS IN C_FND_LOBS( p_user_name, p_tranid) LOOP
405
406 SAVEPOINT save_rec;
407
408 /*** apply record ***/
409 APPLY_RECORD
410 (
411 r_FND_LOBS
412 , l_error_msg
413 , l_process_status
414 );
415
416 /*** was record processed successfully? ***/
417 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
418 /*** Yes -> delete record from inqueue ***/
419 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
420 jtm_message_log_pkg.Log_Msg
421 ( v_object_id => r_fnd_lobs.task_assignment_id
422 , v_object_name => g_object_name
423 , v_message => 'Record successfully processed, deleting from inqueue'
424 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
425 END IF;
426
427
428 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
429 (
430 p_user_name,
431 p_tranid,
432 r_FND_LOBS.seqno$$,
433 r_FND_LOBS.FILE_ID, -- put PK column here
434 g_object_name,
435 g_pub_name,
436 l_error_msg,
437 l_process_status
438 );
439
440 /*** was delete successful? ***/
441 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
442 /*** no -> rollback ***/
443 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
444 jtm_message_log_pkg.Log_Msg
445 ( v_object_id => r_FND_LOBS.task_assignment_id
446 , v_object_name => g_object_name
447 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
448 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
449 END IF;
450
451 ROLLBACK TO save_rec;
452 END IF;
453 END IF;
454
455 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
456 /*** Record was not processed successfully or delete failed
457 -> defer and reject record ***/
458
459 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
460 jtm_message_log_pkg.Log_Msg
461 ( v_object_id => r_FND_LOBS.task_assignment_id
462 , v_object_name => g_object_name
463 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
464 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
465 END IF;
466
467 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
468 (
469 p_user_name
470 , p_tranid
471 , r_FND_LOBS.seqno$$
472 , r_FND_LOBS.FILE_ID -- put PK column here
473 , g_object_name
474 , g_pub_name
475 , l_error_msg
476 , l_process_status
477 , r_FND_LOBS.dmltype$$
478 );
479
480 /*** Was defer successful? ***/
481 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
482 /*** no -> rollback ***/
483 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
484 jtm_message_log_pkg.Log_Msg
485 ( v_object_id => r_FND_LOBS.task_assignment_id
486 , v_object_name => g_object_name
487 , v_message => 'Defer record failed, rolling back to savepoint'
488 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
489 END IF;
490
491 ROLLBACK TO save_rec;
492 END IF;
493 END IF;
494
495 END LOOP;
496
497
498 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
499 jtm_message_log_pkg.Log_Msg
500 ( v_object_id => null
501 , v_object_name => g_object_name
502 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
503 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
504 END IF;
505
506
507 EXCEPTION WHEN OTHERS THEN
508 /*** catch and log exceptions ***/
509 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
510 jtm_message_log_pkg.Log_Msg
511 ( v_object_id => null
512 , v_object_name => g_object_name
513 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
514 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
515 END IF;
516
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 END APPLY_CLIENT_CHANGES;
519
520 END CSL_LOBS_PKG;