1 PACKAGE BODY CSM_LOBS_PKG AS
2 /* $Header: csmulobb.pls 120.15 2008/01/25 06:53:17 saradhak ship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- Ravi 06/11/2002
7 -- --------- ------ ------------------------------------------
8 -- Enter procedure, function bodies as shown below
9
10 error EXCEPTION;
11
12 /*** Globals ***/
13 g_object_name CONSTANT VARCHAR2(30) := 'CSM_LOBS_PKG'; -- package name
14 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_M_LOBS'; -- publication item name
15 g_debug_level NUMBER; -- debug level
16
17 CURSOR c_FND_LOBS( b_user_name VARCHAR2, b_tranid NUMBER) is
18 SELECT
19 CLID$$CS,
20 FILE_ID,
21 TASK_ASSIGNMENT_ID,
22 DESCRIPTION,
23 LANGUAGE,
24 DMLTYPE$$,
25 SEQNO$$,
26 TRANID$$,
27 VERSION$$,
28 ENTITY_NAME,
29 PK1_VALUE,
30 PK2_VALUE,
31 SEQ_NUM,
32 FILE_NAME,
33 FILE_CONTENT_TYPE,
34 USER_NAME,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 UPDATE_USER,
38 CATEGORY,
39 USAGE_TYPE,
40 DATA_TYPE,
41 URL,
42 SHORT_TEXT,
43 DATA_TYPE_ID,
44 DOCUMENT_ID,
45 TITLE
46 FROM
47 csf_m_lobs_inq
48 WHERE tranid$$ = b_tranid
49 AND clid$$cs = b_user_name;
50
51 /***
52 This procedure is called by APPLY_RECORD when an inserted record is to be processed.
53 ***/
54
55 PROCEDURE APPLY_INSERT
56 (
57 p_record IN c_FND_LOBS%ROWTYPE,
58 p_error_msg OUT NOCOPY VARCHAR2,
59 x_return_status IN OUT NOCOPY VARCHAR2
60 ) IS
61
62 -- Variables needed for public API
63 l_seq_num NUMBER;
64 l_category_id NUMBER;
65 l_file_id NUMBER;
66 l_task_assignment_id NUMBER;--This column in the table has the SR or TASK id
67 l_msg_count NUMBER;
68 l_msg_data VARCHAR2(240);
69 l_file_access_id NUMBER;
70 l_error_msg VARCHAR(1024);
71 l_signature_loc BLOB;
72 l_signature_raw LONG RAW(32000);
73 l_signature_size NUMBER;
74 l_language asg_user.LANGUAGE%TYPE;
75 l_dummy NUMBER;
76 l_category_name VARCHAR2(240);--need to add value
77 l_file_name p_record.FILE_NAME%TYPE;
78 l_file_content_type p_record.FILE_CONTENT_TYPE%TYPE;
79 l_entity_name p_record.ENTITY_NAME%TYPE;
80 l_function_name VARCHAR2(240);
81 l_user_id ASG_USER.USER_ID%TYPE;
82 l_dodirty BOOLEAN;
83 l_title VARCHAR2(80);
84 l_schema_name FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
85 l_data_type all_tab_columns.data_type%TYPE;
86 l_data_type_id NUMBER;
87 l_dsql VARCHAR2(4000);
88 l_cursorid NUMBER;
89 l_result NUMBER;
90
91 -- get the debrief header id for the task_assignment
92 -- use the first debrief header id
93 CURSOR l_debrief_header_id_csr(p_task_assignment_id IN number)
94 IS
95 SELECT debrief_header_id
96 FROM csf_debrief_headers
97 WHERE task_assignment_id = p_task_assignment_id
98 ORDER BY debrief_header_id;
99
100 -- get max seq no for the debrief_header_id
101 CURSOR l_max_seq_no_csr(p_task_assignment_id IN NUMBER, p_language IN VARCHAR2,p_category_name IN VARCHAR2,c_entity_name IN VARCHAR2)
102 IS
103 SELECT nvl(max(fad.seq_num),0)+10
104 FROM fnd_attached_documents fad,
105 fnd_documents fd
106 WHERE fad.pk1_value = to_char(p_task_assignment_id)
107 AND fd.document_id = fad.document_id
108 AND fad.entity_name = c_entity_name
109 AND EXISTS
110 (SELECT 1
111 FROM fnd_document_categories_tl cat_tl
112 WHERE cat_tl.category_id = fd.category_id
113 AND cat_tl.name = p_category_name
114 AND cat_tl.LANGUAGE = p_language
115 );
116
117
118 -- get the category_id
119 CURSOR l_category_id_csr(p_language IN VARCHAR2, p_category_name IN VARCHAR2)
120 IS
121 SELECT category_id
122 FROM fnd_document_categories_tl
123 WHERE name = p_category_name
124 AND LANGUAGE = p_language;
125
126 --to check if the record exists in the database
127 CURSOR l_lobs_fileid_csr (p_file_id fnd_lobs.file_id%TYPE)
128 IS
129 SELECT 1
130 FROM fnd_lobs
131 WHERE file_id = p_file_id;
132
133 CURSOR l_get_language(p_user_name IN VARCHAR2)
134 IS
135 SELECT au.LANGUAGE,au.USER_ID
136 FROM asg_user au
137 WHERE au.user_name = p_user_name;
138
139 CURSOR l_get_schema
140 IS
141 SELECT ORACLE_USERNAME from FND_ORACLE_USERID
142 WHERE ORACLE_ID =883;
143
144 CURSOR l_get_datatype (c_schema IN FND_ORACLE_USERID.ORACLE_USERNAME%TYPE)
145 IS
146 select data_type from all_tab_columns
147 where table_name = 'CSF_M_LOBS_INQ'
148 and column_name = 'FILE_DATA'
149 and OWNER = c_schema;
150
151 BEGIN
152
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154 l_dodirty := FALSE;--mark dirty for datatypeid =6 is not necessary
155
156 l_error_msg := 'Entering ' || g_object_name || '.APPLY_INSERT'|| ' for PK ' || to_char( p_record.file_id);
157 CSM_UTIL_PKG.LOG ( l_error_msg ,
158 'CSM_LOBS_PKG.APPLY_INSERT',
159 FND_LOG.LEVEL_PROCEDURE );
160
161 --if attachments are not part of what we support then we leave
162 IF p_record.entity_name NOT IN('JTF_TASKS_B','CS_INCIDENTS','CSF_DEBRIEF_HEADERS') THEN
163
164 l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'||
165 ' as we are not supporting Attachment for the entity' || TO_CHAR(p_record.entity_name);
166 CSM_UTIL_PKG.LOG ( l_error_msg ,'CSM_LOBS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE );
167 RETURN;
168
169 END IF;
170
171 l_title:=p_record.title;
172 l_data_type_id :=p_record.data_type_id;
173 -- get the language of the user
174 OPEN l_get_language(p_record.clid$$cs);
175 FETCH l_get_language INTO l_language,l_user_id;
176 CLOSE l_get_language;
177
178 IF p_record.entity_name = 'JTF_TASKS_B' OR p_record.entity_name = 'CS_INCIDENTS' THEN
179 l_category_name := 'MISC';
180 l_file_name := p_record.file_name;
181 l_file_content_type := p_record.file_content_type;
182 l_entity_name := p_record.entity_name;
183 l_task_assignment_id := TO_NUMBER (p_record.pk1_value);
184
185 IF p_record.entity_name = 'JTF_TASKS_B' THEN --function name is the Name of the Form in which the attachment is displayed
186 l_function_name := 'JTFTKMAN';
187 ELSIF p_record.entity_name = 'CS_INCIDENTS' THEN
188 l_function_name := 'CSXSRISR';
189 END IF;
190
191 ELSIF p_record.entity_name = 'CSF_DEBRIEF_HEADERS' THEN
192 --Else is for signature upload for debrief
193 l_category_name := 'SIGNATURE';
194 l_file_name := 'INTERNAL';
195 l_file_content_type := 'image/bmp';
196 l_entity_name := 'CSF_DEBRIEF_HEADERS';
197 l_function_name := 'CSFFEDBF';
198 -- Bug 5726888
199 l_data_type_id :=6;
200 --required only for signature...
201 -- if debrief_header_id is not passed get the debrief header id using task assignment id
202 l_task_assignment_id:=p_record.pk1_value;
203
204 IF ( (p_record.pk1_value IS NULL) AND
205 (p_record.task_assignment_id IS NOT NULL) )
206 THEN
207 OPEN l_debrief_header_id_csr(p_record.task_assignment_id);
208 FETCH l_debrief_header_id_csr INTO l_task_assignment_id;
209 CLOSE l_debrief_header_id_csr;
210 END IF;
211 END IF;
212
213
214 --setting file id
215 --For lobs we require file id to insert data into fnd_lobs.So we get the file_id from the client
216 --For LOBS we give fileid to the server so that we can avoid the lobs getting downloaded in the client
217 --twice when its uploaded
218 IF l_data_type_id = 6 THEN
219 l_file_id := p_record.file_id;
220
221 -- Bug 5726888
222 IF l_file_id IS NULL AND l_category_name = 'SIGNATURE' THEN
223 l_file_id := p_record.document_id;
224 END IF;
225 --verify that the record does not already exist.If already present then pass null so that the api will create the own id
226 OPEN l_lobs_fileid_csr(l_file_id) ;
227 FETCH l_lobs_fileid_csr into l_dummy;
228
229 IF l_lobs_fileid_csr%FOUND THEN
230 p_error_msg := 'Duplicate Record: File id ' || to_char(l_file_id)|| ' already exists in fnd_lobs table.So generating a new from fnd sequence';
231 CSM_UTIL_PKG.LOG( p_error_msg ,'CSM_LOBS_PKG.UPLOAD_SR_TASK_LOB', FND_LOG.LEVEL_ERROR );
232 l_file_id := NULL;
233 --get the file id from the sequence diretly.As the client sent file id is not proper.
234 SELECT fnd_lobs_s.nextval
235 INTO l_file_id
236 FROM dual;
237 l_dodirty := TRUE;
238 END IF;
239
240 CLOSE l_lobs_fileid_csr;
241 ELSE
242 l_file_id := NULL;
243 END IF;
244
245 -- get the max seq no
246 open l_max_seq_no_csr(l_task_assignment_id, l_language,l_category_name,l_entity_name);
247 FETCH l_max_seq_no_csr INTO l_seq_num;
248 CLOSE l_max_seq_no_csr;
249
250 -- get the category id for Signature
251 OPEN l_category_id_csr(l_language,l_category_name);
252 FETCH l_category_id_csr INTO l_category_id;
253 CLOSE l_category_id_csr;
254
255 IF l_data_type_id = 6 THEN
256
257 BEGIN
258 -- get shema
259 OPEN l_get_schema;
260 FETCH l_get_schema INTO l_schema_name;
261 CLOSE l_get_schema;
262
263 -- get datatype
264 OPEN l_get_datatype(l_schema_name);
265 FETCH l_get_datatype INTO l_data_type;
266 CLOSE l_get_datatype;
267
268 /* INSERT INTO fnd_lobs(file_id,
269 file_name,
270 file_content_type,
271 file_data,
272 upload_date,
273 language,
274 file_format)
275 SELECT l_file_id AS FILE_ID,
276 l_file_name AS FILE_NAME,
277 l_file_content_type AS FILE_CONTENT_TYPE,
278 file_data AS FILE_DATA,
279 SYSDATE as UPLOAD_DATE,
280 l_language as LANGUAGE,
281 'binary' AS FILE_FORMAT
282 FROM csf_m_lobs_inq
283 WHERE file_id = l_file_id
284 AND tranid$$ = p_record.tranid$$
285 AND clid$$cs = p_record.clid$$cs;*/
286 IF l_data_type ='BLOB' THEN
287 l_dsql := 'INSERT INTO fnd_lobs(file_id,'
288 || 'file_name,'
289 || 'file_content_type,'
290 || 'file_data,'
291 || 'upload_date,'
292 || 'language, '
293 || 'file_format)'
294 || 'SELECT ' || l_file_id || ' AS FILE_ID,'
295 || '''' || l_file_name || ''' AS FILE_NAME,'
296 || '''' || l_file_content_type || ''' AS FILE_CONTENT_TYPE, '
297 || 'file_data AS FILE_DATA, '
298 ||' SYSDATE AS UPLOAD_DATE,'
299 ||'''' || l_language || ''' AS LANGUAGE,'
300 ||'''binary''' || ' AS FILE_FORMAT '
301 ||' FROM csf_m_lobs_inq'
302 || ' WHERE file_id = ' || l_file_id
303 || ' AND tranid$$ = ' || p_record.tranid$$
304 || ' AND clid$$cs = ''' || p_record.clid$$cs || '''';
305
306 ELSE
307 l_dsql := 'INSERT INTO fnd_lobs(file_id,'
308 || 'file_name,'
309 || 'file_content_type,'
310 || 'file_data,'
311 || 'upload_date,'
312 || 'language, '
313 || 'file_format)'
314 || 'SELECT ' || l_file_id || ' AS FILE_ID,'
315 || '''' || l_file_name || ''' AS FILE_NAME,'
316 || '''' || l_file_content_type || ''' AS FILE_CONTENT_TYPE, '
317 || 'TO_LOB(file_data) AS FILE_DATA, '
318 ||' SYSDATE AS UPLOAD_DATE,'
319 ||'''' || l_language || ''' AS LANGUAGE,'
320 ||'''binary''' || ' AS FILE_FORMAT '
321 ||' FROM csf_m_lobs_inq'
322 || ' WHERE file_id = ' || l_file_id
323 || ' AND tranid$$ = ' || p_record.tranid$$
324 || ' AND clid$$cs = ''' || p_record.clid$$cs || '''';
325
326 END IF;
327
328 l_cursorid := DBMS_SQL.open_cursor;
329 --parse and execute the sql
330 DBMS_SQL.parse(l_cursorid, l_dsql, DBMS_SQL.v7);
331 l_result := DBMS_SQL.execute(l_cursorid);
332 DBMS_SQL.close_cursor (l_cursorid);
333
334
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 -- check if the record exists
339 OPEN l_lobs_fileid_csr(l_file_id) ;
340 FETCH l_lobs_fileid_csr into l_dummy;
341 IF l_lobs_fileid_csr%found THEN
342 --the record exists. Dont show any error.
343 null;
344 ELSE
345 --record could not be inserted, throw the exception
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 raise;
348 END IF;
349 CLOSE l_lobs_fileid_csr;
350 END;
351
352 END IF;--this is execulted only for lobs
353
354 --After inserting the lobs into fnd_lobs table the attachment is added to the corresponding
355 --Entity ie to task or Debrief or SR we need to call the following API
356 fnd_webattch.add_attachment(
357 seq_num => l_seq_num,
358 category_id => l_category_id,
359 document_description=> p_record.description, --l_signed_date||' '||l_signed_by,
360 datatype_id => l_data_type_id,
361 text => p_record.short_text,
362 file_name => l_file_name,
363 url => p_record.url,
364 function_name => l_function_name,
365 entity_name => l_entity_name,
366 pk1_value => l_task_assignment_id,
367 pk2_value => NULL,
368 pk3_value => NULL,
369 pk4_value => NULL,
370 pk5_value => NULL,
371 media_id => l_file_id,
372 user_id => l_user_id, --fnd_global.login_id
373 title => l_title);
374
375 --Inserting data into the Access table to have the record in the client immd.Without running JTM progmram
376
377 CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD(l_task_assignment_id,l_user_id,l_entity_name,l_data_type_id,l_dodirty);
378
379 l_error_msg := 'The record is going to get rejected as its successfully inserted into the Base Table.';
380
381 CSM_UTIL_PKG.LOG ( l_error_msg , 'CSM_LOBS_PKG.APPLY_INSERT',
382 FND_LOG.LEVEL_PROCEDURE );
383
384 --The attachment is added successfully to the apps.so deferring the original record
385 IF l_data_type_id =5 OR l_data_type_id =1 OR (l_data_type_id = 6 AND l_dodirty = TRUE) THEN
386 --Reject the record for url,short text and for lobs which has new file id
387 --from server instead of one from the client
388 CSM_UTIL_PKG.REJECT_RECORD (
389 p_user_name => p_record.CLID$$CS,
390 p_tranid => p_record.TRANID$$,
391 p_seqno => p_record.SEQNO$$,
392 p_pk => p_record.document_id,
393 p_object_name => g_object_name,
394 p_pub_name => g_pub_name,
395 p_error_msg => l_error_msg,
396 x_return_status => x_return_status
397 );
398
399 END IF;
400
401 l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char (p_record.file_id);
402 CSM_UTIL_PKG.LOG ( l_error_msg , 'CSM_LOBS_PKG.APPLY_INSERT',
403 FND_LOG.LEVEL_PROCEDURE );
404
405
406 EXCEPTION WHEN OTHERS THEN
407
408 l_error_msg := 'Exception occurred in ' || g_object_name || '.APPLY_INSERT and hence leaving it:' || ' ' || sqlerrm
409 || ' for PK ' || to_char (p_record.file_id );
410 CSM_UTIL_PKG.LOG ( l_error_msg ,'CSM_LOBS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
411 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
412
413 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT(p_api_error => TRUE );
414 x_return_status := FND_API.G_RET_STS_ERROR;
415
416 END APPLY_INSERT;
417
418 /***
419 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
420 ***/
421 PROCEDURE APPLY_RECORD
422 (
423 p_record IN c_FND_LOBS%ROWTYPE,
424 p_error_msg OUT NOCOPY VARCHAR2,
425 x_return_status IN OUT NOCOPY VARCHAR2
426 ) IS
427 l_error_msg varchar(1024);
428 BEGIN
429 /*** initialize return status and message list ***/
430 x_return_status := FND_API.G_RET_STS_SUCCESS;
431 FND_MSG_PUB.INITIALIZE;
432
433
434 l_error_msg := 'Entering ' || g_object_name || '.APPLY_RECORD'
435 || ' for PK ' || to_char (p_record.FILE_ID );
436 CSM_UTIL_PKG.LOG ( l_error_msg ,
437 'CSM_LOBS_PKG.APPLY_RECORD',
438 FND_LOG.LEVEL_PROCEDURE );
439
440 l_error_msg := 'Processing ' || g_object_name || ' for PK ' || to_char (p_record.FILE_ID) || ' ' ||
441 'DMLTYPE = ' || p_record.dmltype$$ ;
442
443 CSM_UTIL_PKG.LOG ( l_error_msg ,
444 'CSM_LOBS_PKG.APPLY_RECORD',
445 FND_LOG.LEVEL_EVENT );
446
447 IF p_record.dmltype$$='I' THEN
448 -- Process insert
449 APPLY_INSERT
450 (
451 p_record,
452 p_error_msg,
453 x_return_status
454 );
455 ELSIF p_record.dmltype$$='U' THEN
456
457 -- Process update; not supported for this entity
458
459 l_error_msg := 'Update is not supported for this entity ' || g_object_name;
460 CSM_UTIL_PKG.LOG (l_error_msg,
461 'CSM_LOBS_PKG.APPLY_RECORD',
462 FND_LOG.LEVEL_EVENT );
463
464 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
465 (
466 p_message => 'CSM_DML_OPERATION'
467 , p_token_name1 => 'DML'
468 , p_token_value1 => p_record.dmltype$$
469 );
470
471 x_return_status := FND_API.G_RET_STS_ERROR;
472
473 ELSIF p_record.dmltype$$='D' THEN
474 -- Process delete; not supported for this entity
475
476 l_error_msg := 'Delete is not supported for this entity ' || g_object_name ;
477 CSM_UTIL_PKG.LOG( l_error_msg,
478 'CSM_LOBS_PKG.APPLY_RECORD',
479 FND_LOG.LEVEL_EVENT );
480
481
482 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
483 (
484 p_message => 'CSM_DML_OPERATION'
485 , p_token_name1 => 'DML'
486 , p_token_value1 => p_record.dmltype$$
487 );
488
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 ELSE
491 -- invalid dml type
492
493 CSM_UTIL_PKG.LOG
494 ( 'Invalid DML type: ' || p_record.dmltype$$ || ' for this entity '
495 || g_object_name, 'CSM_LOBS_PKG.APPLY_RECORD', FND_LOG.LEVEL_ERROR);
496
497 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
498 (
499 p_message => 'CSM_DML_OPERATION'
500 , p_token_name1 => 'DML'
501 , p_token_value1 => p_record.dmltype$$
502 );
503
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 END IF;
506
507 CSM_UTIL_PKG.LOG ( 'Leaving ' || g_object_name || '.APPLY_RECORD' || ' for PK ' || p_record.FILE_ID,
508 'CSM_LOBS_LOBS.APPLY_RECORD',
509 FND_LOG.LEVEL_PROCEDURE );
510
511
512 EXCEPTION WHEN OTHERS THEN
513 /*** defer record when any process exception occurs ***/
514
515 CSM_UTIL_PKG.LOG
516 ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || sqlerrm
517 || ' for PK ' || p_record.FILE_ID,'CSM_LOBS_LOBS.APPLY_RECORD',FND_LOG.LEVEL_ERROR );
518
519
520 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
521 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
522 (
523 p_api_error => TRUE
524 );
525
526
527 CSM_UTIL_PKG.LOG
528 ( 'Leaving ' || g_object_name || '.APPLY_RECORD'|| ' for PK ' || p_record.FILE_ID,
529 'CSM_LOBS_LOBS.APPLY_RECORD',
530 FND_LOG.LEVEL_ERROR );
531
532
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 END APPLY_RECORD;
535
536 /***
537 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSF_M_LOBS
538 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
539 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
540 public APIs.
541 ***/
542 PROCEDURE APPLY_CLIENT_CHANGES
543 (
544 p_user_name IN VARCHAR2,
545 p_tranid IN NUMBER,
546 p_debug_level IN NUMBER,
547 x_return_status IN OUT NOCOPY VARCHAR2
548 ) IS
549
550 l_process_status VARCHAR2(1);
551 l_error_msg VARCHAR2(4000);
552 BEGIN
553 g_debug_level := p_debug_level;
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555
556 CSM_UTIL_PKG.LOG ( 'Entering ' || g_object_name || '.Apply_Client_Changes',
557 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
558 FND_LOG.LEVEL_PROCEDURE);
559
560
561 /*** loop through CSF_M_LOBS records in inqueue ***/
562 FOR r_FND_LOBS IN c_FND_LOBS( p_user_name, p_tranid) LOOP
563
564 SAVEPOINT save_rec;
565
566 /*** apply record ***/
567 APPLY_RECORD
568 (
569 r_FND_LOBS
570 , l_error_msg
571 , l_process_status
572 );
573
574
575 /*** was record processed successfully? ***/
576 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
577 /*** Yes -> delete record from inqueue ***/
578 CSM_UTIL_PKG.LOG
579 ( 'Record successfully processed, deleting from inqueue ' || g_object_name
580 || ' for PK ' || r_FND_LOBS.FILE_ID,
581 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
582 FND_LOG.LEVEL_EVENT );
583
584
585 CSM_UTIL_PKG.DELETE_RECORD
586 (
587 p_user_name,
588 p_tranid,
589 r_FND_LOBS.seqno$$,
590 r_FND_LOBS.FILE_ID, -- put PK column here
591 g_object_name,
592 g_pub_name,
593 l_error_msg,
594 l_process_status
595 );
596
597 /*** was delete successful? ***/
598 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
599 /*** no -> rollback ***/
600 CSM_UTIL_PKG.LOG
601 ( 'Deleting from inqueue failed, rolling back to savepoint for entity ' || g_object_name
602 || ' and PK ' || r_FND_LOBS.FILE_ID,
603 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES' ,
604 FND_LOG.LEVEL_EVENT);
605
606 ROLLBACK TO save_rec;
607 END IF;
608 END IF;
609
610 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
611 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
612
613 CSM_UTIL_PKG.LOG
614 ( 'Record not processed successfully, deferring and rejecting record for entity ' || g_object_name
615 || ' and PK ' || r_FND_LOBS.FILE_ID,
616 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
617 FND_LOG.LEVEL_EVENT );
618
619
620 CSM_UTIL_PKG.DEFER_RECORD
621 (
622 p_user_name
623 , p_tranid
624 , r_FND_LOBS.seqno$$
625 , r_FND_LOBS.FILE_ID -- put PK column here
626 , g_object_name
627 , g_pub_name
628 , l_error_msg
629 , l_process_status
630 , r_FND_LOBS.dmltype$$
631 );
632
633 /*** Was defer successful? ***/
634 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
635 /*** no -> rollback ***/
636 CSM_UTIL_PKG.LOG
637 ( 'Defer record failed, rolling back to savepoint for entity ' || g_object_name
638 || ' and PK ' || r_FND_LOBS.FILE_ID,
639 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
640 FND_LOG.LEVEL_EVENT );
641
642 ROLLBACK TO save_rec;
643 END IF;
644 END IF;
645
646 END LOOP;
647
648
649 CSM_UTIL_PKG.LOG( 'Leaving ' || g_object_name || '.Apply_Client_Changes',
650 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
651 FND_LOG.LEVEL_PROCEDURE);
652
653
654 EXCEPTION WHEN OTHERS THEN
655 /*** catch and log exceptions ***/
656 CSM_UTIL_PKG.LOG ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm ,
657 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES', FND_LOG.LEVEL_ERROR);
658
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 END APPLY_CLIENT_CHANGES;
661
662
663 END CSM_LOBS_PKG;
[Home] [Help]