[Home] [Help]
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;
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:
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);
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;
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
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')
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);
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 ***/
407:
408: /*** apply record ***/
409: APPLY_RECORD
410: (
411: r_FND_LOBS
412: , l_error_msg
413: , l_process_status
414: );
415:
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;
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,
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
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;
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;
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
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
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
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;