1 PACKAGE BODY CSM_LOBS_PKG AS
2 /* $Header: csmulobb.pls 120.15.12020000.2 2012/11/29 06:57:55 ravir 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 dba_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 /* Bug 15932265
145 CURSOR l_get_datatype (c_schema IN FND_ORACLE_USERID.ORACLE_USERNAME%TYPE)
146 IS
147 select data_type from all_tab_columns
148 where table_name = 'CSF_M_LOBS_INQ'
149 and column_name = 'FILE_DATA'
150 and OWNER = c_schema;
151 */
152
153 CURSOR l_get_datatype (c_schema IN FND_ORACLE_USERID.ORACLE_USERNAME%TYPE)
154 IS
155 select data_type from user_synonyms syn, dba_tab_columns col
156 where syn.table_owner = c_schema
157 and syn.synonym_name = 'CSF_M_LOBS_INQ'
158 and col.owner = syn.table_owner
159 and col.table_name = syn.table_name
160 and col.column_name = 'FILE_DATA';
161
162 BEGIN
163
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165 l_dodirty := FALSE;--mark dirty for datatypeid =6 is not necessary
166
167 l_error_msg := 'Entering ' || g_object_name || '.APPLY_INSERT'|| ' for PK ' || to_char( p_record.file_id);
168 CSM_UTIL_PKG.LOG ( l_error_msg ,
169 'CSM_LOBS_PKG.APPLY_INSERT',
170 FND_LOG.LEVEL_PROCEDURE );
171
172 --if attachments are not part of what we support then we leave
173 IF p_record.entity_name NOT IN('JTF_TASKS_B','CS_INCIDENTS','CSF_DEBRIEF_HEADERS') THEN
174
175 l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'||
176 ' as we are not supporting Attachment for the entity' || TO_CHAR(p_record.entity_name);
177 CSM_UTIL_PKG.LOG ( l_error_msg ,'CSM_LOBS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE );
178 RETURN;
179
180 END IF;
181
182 l_title:=p_record.title;
183 l_data_type_id :=p_record.data_type_id;
184 -- get the language of the user
185 OPEN l_get_language(p_record.clid$$cs);
186 FETCH l_get_language INTO l_language,l_user_id;
187 CLOSE l_get_language;
188
189 IF p_record.entity_name = 'JTF_TASKS_B' OR p_record.entity_name = 'CS_INCIDENTS' THEN
190 l_category_name := 'MISC';
191 l_file_name := p_record.file_name;
192 l_file_content_type := p_record.file_content_type;
193 l_entity_name := p_record.entity_name;
194 l_task_assignment_id := TO_NUMBER (p_record.pk1_value);
195
196 IF p_record.entity_name = 'JTF_TASKS_B' THEN --function name is the Name of the Form in which the attachment is displayed
197 l_function_name := 'JTFTKMAN';
198 ELSIF p_record.entity_name = 'CS_INCIDENTS' THEN
199 l_function_name := 'CSXSRISR';
200 END IF;
201
202 ELSIF p_record.entity_name = 'CSF_DEBRIEF_HEADERS' THEN
203 --Else is for signature upload for debrief
204 l_category_name := 'SIGNATURE';
205 l_file_name := 'INTERNAL';
206 l_file_content_type := 'image/bmp';
207 l_entity_name := 'CSF_DEBRIEF_HEADERS';
208 l_function_name := 'CSFFEDBF';
209 -- Bug 5726888
210 l_data_type_id :=6;
211 --required only for signature...
212 -- if debrief_header_id is not passed get the debrief header id using task assignment id
213 l_task_assignment_id:=p_record.pk1_value;
214
215 IF ( (p_record.pk1_value IS NULL) AND
216 (p_record.task_assignment_id IS NOT NULL) )
217 THEN
218 OPEN l_debrief_header_id_csr(p_record.task_assignment_id);
219 FETCH l_debrief_header_id_csr INTO l_task_assignment_id;
220 CLOSE l_debrief_header_id_csr;
221 END IF;
222 END IF;
223
224
225 --setting file id
226 --For lobs we require file id to insert data into fnd_lobs.So we get the file_id from the client
227 --For LOBS we give fileid to the server so that we can avoid the lobs getting downloaded in the client
228 --twice when its uploaded
229 IF l_data_type_id = 6 THEN
230 l_file_id := p_record.file_id;
231
232 -- Bug 5726888
233 IF l_file_id IS NULL AND l_category_name = 'SIGNATURE' THEN
234 l_file_id := p_record.document_id;
235 END IF;
236 --verify that the record does not already exist.If already present then pass null so that the api will create the own id
237 OPEN l_lobs_fileid_csr(l_file_id) ;
238 FETCH l_lobs_fileid_csr into l_dummy;
239
240 IF l_lobs_fileid_csr%FOUND THEN
241 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';
242 CSM_UTIL_PKG.LOG( p_error_msg ,'CSM_LOBS_PKG.UPLOAD_SR_TASK_LOB', FND_LOG.LEVEL_ERROR );
243 l_file_id := NULL;
244 --get the file id from the sequence diretly.As the client sent file id is not proper.
245 SELECT fnd_lobs_s.nextval
246 INTO l_file_id
247 FROM dual;
248 l_dodirty := TRUE;
249 END IF;
250
251 CLOSE l_lobs_fileid_csr;
252 ELSE
253 l_file_id := NULL;
254 END IF;
255
256 -- get the max seq no
257 open l_max_seq_no_csr(l_task_assignment_id, l_language,l_category_name,l_entity_name);
258 FETCH l_max_seq_no_csr INTO l_seq_num;
259 CLOSE l_max_seq_no_csr;
260
261 -- get the category id for Signature
262 OPEN l_category_id_csr(l_language,l_category_name);
263 FETCH l_category_id_csr INTO l_category_id;
264 CLOSE l_category_id_csr;
265
266 IF l_data_type_id = 6 THEN
267
268 BEGIN
269 -- get shema
270 OPEN l_get_schema;
271 FETCH l_get_schema INTO l_schema_name;
272 CLOSE l_get_schema;
273
274 -- get datatype
275 OPEN l_get_datatype(l_schema_name);
276 FETCH l_get_datatype INTO l_data_type;
277 CLOSE l_get_datatype;
278
279 /* INSERT INTO fnd_lobs(file_id,
280 file_name,
281 file_content_type,
282 file_data,
283 upload_date,
284 language,
285 file_format)
286 SELECT l_file_id AS FILE_ID,
287 l_file_name AS FILE_NAME,
288 l_file_content_type AS FILE_CONTENT_TYPE,
289 file_data AS FILE_DATA,
290 SYSDATE as UPLOAD_DATE,
291 l_language as LANGUAGE,
292 'binary' AS FILE_FORMAT
293 FROM csf_m_lobs_inq
294 WHERE file_id = l_file_id
295 AND tranid$$ = p_record.tranid$$
296 AND clid$$cs = p_record.clid$$cs;*/
297 IF l_data_type ='BLOB' THEN
298 l_dsql := 'INSERT INTO fnd_lobs(file_id,'
299 || 'file_name,'
300 || 'file_content_type,'
301 || 'file_data,'
302 || 'upload_date,'
303 || 'language, '
304 || 'file_format)'
305 || 'SELECT ' || l_file_id || ' AS FILE_ID,'
306 || '''' || l_file_name || ''' AS FILE_NAME,'
307 || '''' || l_file_content_type || ''' AS FILE_CONTENT_TYPE, '
308 || 'file_data AS FILE_DATA, '
309 ||' SYSDATE AS UPLOAD_DATE,'
310 ||'''' || l_language || ''' AS LANGUAGE,'
311 ||'''binary''' || ' AS FILE_FORMAT '
312 ||' FROM csf_m_lobs_inq'
313 || ' WHERE file_id = ' || l_file_id
314 || ' AND tranid$$ = ' || p_record.tranid$$
315 || ' AND clid$$cs = ''' || p_record.clid$$cs || '''';
316
317 ELSE
318 l_dsql := 'INSERT INTO fnd_lobs(file_id,'
319 || 'file_name,'
320 || 'file_content_type,'
321 || 'file_data,'
322 || 'upload_date,'
323 || 'language, '
324 || 'file_format)'
325 || 'SELECT ' || l_file_id || ' AS FILE_ID,'
326 || '''' || l_file_name || ''' AS FILE_NAME,'
327 || '''' || l_file_content_type || ''' AS FILE_CONTENT_TYPE, '
328 || 'TO_LOB(file_data) AS FILE_DATA, '
329 ||' SYSDATE AS UPLOAD_DATE,'
330 ||'''' || l_language || ''' AS LANGUAGE,'
331 ||'''binary''' || ' AS FILE_FORMAT '
332 ||' FROM csf_m_lobs_inq'
333 || ' WHERE file_id = ' || l_file_id
334 || ' AND tranid$$ = ' || p_record.tranid$$
335 || ' AND clid$$cs = ''' || p_record.clid$$cs || '''';
336
337 END IF;
338
339 l_cursorid := DBMS_SQL.open_cursor;
340 --parse and execute the sql
341 DBMS_SQL.parse(l_cursorid, l_dsql, DBMS_SQL.v7);
342 l_result := DBMS_SQL.execute(l_cursorid);
343 DBMS_SQL.close_cursor (l_cursorid);
344
345
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 -- check if the record exists
350 OPEN l_lobs_fileid_csr(l_file_id) ;
351 FETCH l_lobs_fileid_csr into l_dummy;
352 IF l_lobs_fileid_csr%found THEN
353 --the record exists. Dont show any error.
354 null;
355 ELSE
356 --record could not be inserted, throw the exception
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 raise;
359 END IF;
360 CLOSE l_lobs_fileid_csr;
361 END;
362
363 END IF;--this is execulted only for lobs
364
365 --After inserting the lobs into fnd_lobs table the attachment is added to the corresponding
366 --Entity ie to task or Debrief or SR we need to call the following API
367 fnd_webattch.add_attachment(
368 seq_num => l_seq_num,
369 category_id => l_category_id,
370 document_description=> p_record.description, --l_signed_date||' '||l_signed_by,
371 datatype_id => l_data_type_id,
372 text => p_record.short_text,
373 file_name => l_file_name,
374 url => p_record.url,
375 function_name => l_function_name,
376 entity_name => l_entity_name,
377 pk1_value => l_task_assignment_id,
378 pk2_value => NULL,
379 pk3_value => NULL,
380 pk4_value => NULL,
381 pk5_value => NULL,
382 media_id => l_file_id,
383 user_id => l_user_id, --fnd_global.login_id
384 title => l_title);
385
386 --Inserting data into the Access table to have the record in the client immd.Without running JTM progmram
387
388 CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD(l_task_assignment_id,l_user_id,l_entity_name,l_data_type_id,l_dodirty);
389
390 l_error_msg := 'The record is going to get rejected as its successfully inserted into the Base Table.';
391
392 CSM_UTIL_PKG.LOG ( l_error_msg , 'CSM_LOBS_PKG.APPLY_INSERT',
393 FND_LOG.LEVEL_PROCEDURE );
394
395 --The attachment is added successfully to the apps.so deferring the original record
396 IF l_data_type_id =5 OR l_data_type_id =1 OR (l_data_type_id = 6 AND l_dodirty = TRUE) THEN
397 --Reject the record for url,short text and for lobs which has new file id
398 --from server instead of one from the client
399 CSM_UTIL_PKG.REJECT_RECORD (
400 p_user_name => p_record.CLID$$CS,
401 p_tranid => p_record.TRANID$$,
402 p_seqno => p_record.SEQNO$$,
403 p_pk => p_record.document_id,
404 p_object_name => g_object_name,
405 p_pub_name => g_pub_name,
406 p_error_msg => l_error_msg,
407 x_return_status => x_return_status
408 );
409
410 END IF;
411
412 l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char (p_record.file_id);
413 CSM_UTIL_PKG.LOG ( l_error_msg , 'CSM_LOBS_PKG.APPLY_INSERT',
414 FND_LOG.LEVEL_PROCEDURE );
415
416
417 EXCEPTION WHEN OTHERS THEN
418
419 l_error_msg := 'Exception occurred in ' || g_object_name || '.APPLY_INSERT and hence leaving it:' || ' ' || sqlerrm
420 || ' for PK ' || to_char (p_record.file_id );
421 CSM_UTIL_PKG.LOG ( l_error_msg ,'CSM_LOBS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
422 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
423
424 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT(p_api_error => TRUE );
425 x_return_status := FND_API.G_RET_STS_ERROR;
426
427 END APPLY_INSERT;
428
429 /***
430 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
431 ***/
432 PROCEDURE APPLY_RECORD
433 (
434 p_record IN c_FND_LOBS%ROWTYPE,
435 p_error_msg OUT NOCOPY VARCHAR2,
436 x_return_status IN OUT NOCOPY VARCHAR2
437 ) IS
438 l_error_msg varchar(1024);
439 BEGIN
440 /*** initialize return status and message list ***/
441 x_return_status := FND_API.G_RET_STS_SUCCESS;
442 FND_MSG_PUB.INITIALIZE;
443
444
445 l_error_msg := 'Entering ' || g_object_name || '.APPLY_RECORD'
446 || ' for PK ' || to_char (p_record.FILE_ID );
447 CSM_UTIL_PKG.LOG ( l_error_msg ,
448 'CSM_LOBS_PKG.APPLY_RECORD',
449 FND_LOG.LEVEL_PROCEDURE );
450
451 l_error_msg := 'Processing ' || g_object_name || ' for PK ' || to_char (p_record.FILE_ID) || ' ' ||
452 'DMLTYPE = ' || p_record.dmltype$$ ;
453
454 CSM_UTIL_PKG.LOG ( l_error_msg ,
455 'CSM_LOBS_PKG.APPLY_RECORD',
456 FND_LOG.LEVEL_EVENT );
457
458 IF p_record.dmltype$$='I' THEN
459 -- Process insert
460 APPLY_INSERT
461 (
462 p_record,
463 p_error_msg,
464 x_return_status
465 );
466 ELSIF p_record.dmltype$$='U' THEN
467
468 -- Process update; not supported for this entity
469
470 l_error_msg := 'Update is not supported for this entity ' || g_object_name;
471 CSM_UTIL_PKG.LOG (l_error_msg,
472 'CSM_LOBS_PKG.APPLY_RECORD',
473 FND_LOG.LEVEL_EVENT );
474
475 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
476 (
477 p_message => 'CSM_DML_OPERATION'
478 , p_token_name1 => 'DML'
479 , p_token_value1 => p_record.dmltype$$
480 );
481
482 x_return_status := FND_API.G_RET_STS_ERROR;
483
484 ELSIF p_record.dmltype$$='D' THEN
485 -- Process delete; not supported for this entity
486
487 l_error_msg := 'Delete is not supported for this entity ' || g_object_name ;
488 CSM_UTIL_PKG.LOG( l_error_msg,
489 'CSM_LOBS_PKG.APPLY_RECORD',
490 FND_LOG.LEVEL_EVENT );
491
492
493 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
494 (
495 p_message => 'CSM_DML_OPERATION'
496 , p_token_name1 => 'DML'
497 , p_token_value1 => p_record.dmltype$$
498 );
499
500 x_return_status := FND_API.G_RET_STS_ERROR;
501 ELSE
502 -- invalid dml type
503
504 CSM_UTIL_PKG.LOG
505 ( 'Invalid DML type: ' || p_record.dmltype$$ || ' for this entity '
506 || g_object_name, 'CSM_LOBS_PKG.APPLY_RECORD', FND_LOG.LEVEL_ERROR);
507
508 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
509 (
510 p_message => 'CSM_DML_OPERATION'
511 , p_token_name1 => 'DML'
512 , p_token_value1 => p_record.dmltype$$
513 );
514
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 END IF;
517
518 CSM_UTIL_PKG.LOG ( 'Leaving ' || g_object_name || '.APPLY_RECORD' || ' for PK ' || p_record.FILE_ID,
519 'CSM_LOBS_LOBS.APPLY_RECORD',
520 FND_LOG.LEVEL_PROCEDURE );
521
522
523 EXCEPTION WHEN OTHERS THEN
524 /*** defer record when any process exception occurs ***/
525
526 CSM_UTIL_PKG.LOG
527 ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || sqlerrm
528 || ' for PK ' || p_record.FILE_ID,'CSM_LOBS_LOBS.APPLY_RECORD',FND_LOG.LEVEL_ERROR );
529
530
531 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
532 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
533 (
534 p_api_error => TRUE
535 );
536
537
538 CSM_UTIL_PKG.LOG
539 ( 'Leaving ' || g_object_name || '.APPLY_RECORD'|| ' for PK ' || p_record.FILE_ID,
540 'CSM_LOBS_LOBS.APPLY_RECORD',
541 FND_LOG.LEVEL_ERROR );
542
543
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 END APPLY_RECORD;
546
547 /***
548 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSF_M_LOBS
549 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
550 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
551 public APIs.
552 ***/
553 PROCEDURE APPLY_CLIENT_CHANGES
554 (
555 p_user_name IN VARCHAR2,
556 p_tranid IN NUMBER,
557 p_debug_level IN NUMBER,
558 x_return_status IN OUT NOCOPY VARCHAR2
559 ) IS
560
561 l_process_status VARCHAR2(1);
562 l_error_msg VARCHAR2(4000);
563 BEGIN
564 g_debug_level := p_debug_level;
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566
567 CSM_UTIL_PKG.LOG ( 'Entering ' || g_object_name || '.Apply_Client_Changes',
568 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
569 FND_LOG.LEVEL_PROCEDURE);
570
571
572 /*** loop through CSF_M_LOBS records in inqueue ***/
573 FOR r_FND_LOBS IN c_FND_LOBS( p_user_name, p_tranid) LOOP
574
575 SAVEPOINT save_rec;
576
577 /*** apply record ***/
578 APPLY_RECORD
579 (
580 r_FND_LOBS
581 , l_error_msg
582 , l_process_status
583 );
584
585
586 /*** was record processed successfully? ***/
587 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
588 /*** Yes -> delete record from inqueue ***/
589 CSM_UTIL_PKG.LOG
590 ( 'Record successfully processed, deleting from inqueue ' || g_object_name
591 || ' for PK ' || r_FND_LOBS.FILE_ID,
592 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
593 FND_LOG.LEVEL_EVENT );
594
595
596 CSM_UTIL_PKG.DELETE_RECORD
597 (
598 p_user_name,
599 p_tranid,
600 r_FND_LOBS.seqno$$,
601 r_FND_LOBS.FILE_ID, -- put PK column here
602 g_object_name,
603 g_pub_name,
604 l_error_msg,
605 l_process_status
606 );
607
608 /*** was delete successful? ***/
609 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
610 /*** no -> rollback ***/
611 CSM_UTIL_PKG.LOG
612 ( 'Deleting from inqueue failed, rolling back to savepoint for entity ' || g_object_name
613 || ' and PK ' || r_FND_LOBS.FILE_ID,
614 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES' ,
615 FND_LOG.LEVEL_EVENT);
616
617 ROLLBACK TO save_rec;
618 END IF;
619 END IF;
620
621 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
622 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
623
624 CSM_UTIL_PKG.LOG
625 ( 'Record not processed successfully, deferring and rejecting record for entity ' || g_object_name
626 || ' and PK ' || r_FND_LOBS.FILE_ID,
627 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
628 FND_LOG.LEVEL_EVENT );
629
630
631 CSM_UTIL_PKG.DEFER_RECORD
632 (
633 p_user_name
634 , p_tranid
635 , r_FND_LOBS.seqno$$
636 , r_FND_LOBS.FILE_ID -- put PK column here
637 , g_object_name
638 , g_pub_name
639 , l_error_msg
640 , l_process_status
641 , r_FND_LOBS.dmltype$$
642 );
643
644 /*** Was defer successful? ***/
645 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
646 /*** no -> rollback ***/
647 CSM_UTIL_PKG.LOG
648 ( 'Defer record failed, rolling back to savepoint for entity ' || g_object_name
649 || ' and PK ' || r_FND_LOBS.FILE_ID,
650 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
651 FND_LOG.LEVEL_EVENT );
652
653 ROLLBACK TO save_rec;
654 END IF;
655 END IF;
656
657 END LOOP;
658
659
660 CSM_UTIL_PKG.LOG( 'Leaving ' || g_object_name || '.Apply_Client_Changes',
661 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
662 FND_LOG.LEVEL_PROCEDURE);
663
664
665 EXCEPTION WHEN OTHERS THEN
666 /*** catch and log exceptions ***/
667 CSM_UTIL_PKG.LOG ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm ,
668 'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES', FND_LOG.LEVEL_ERROR);
669
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 END APPLY_CLIENT_CHANGES;
672
673
674 END CSM_LOBS_PKG;
[Home] [Help]