[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CLID$$CS,
FILE_ID,
TASK_ASSIGNMENT_ID,
DESCRIPTION,
LANGUAGE,
DMLTYPE$$,
SEQNO$$,
TRANID$$,
VERSION$$,
ENTITY_NAME,
PK1_VALUE,
PK2_VALUE,
SEQ_NUM,
FILE_NAME,
FILE_CONTENT_TYPE,
USER_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
UPDATE_USER,
CATEGORY,
USAGE_TYPE,
DATA_TYPE,
URL,
SHORT_TEXT,
DATA_TYPE_ID,
DOCUMENT_ID,
TITLE
FROM
csf_m_lobs_inq
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
This procedure is called by APPLY_RECORD when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_FND_LOBS%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
-- Variables needed for public API
l_seq_num NUMBER;
SELECT debrief_header_id
FROM csf_debrief_headers
WHERE task_assignment_id = p_task_assignment_id
ORDER BY debrief_header_id;
SELECT nvl(max(fad.seq_num),0)+10
FROM fnd_attached_documents fad,
fnd_documents fd
WHERE fad.pk1_value = to_char(p_task_assignment_id)
AND fd.document_id = fad.document_id
AND fad.entity_name = c_entity_name
AND EXISTS
(SELECT 1
FROM fnd_document_categories_tl cat_tl
WHERE cat_tl.category_id = fd.category_id
AND cat_tl.name = p_category_name
AND cat_tl.LANGUAGE = p_language
);
SELECT category_id
FROM fnd_document_categories_tl
WHERE name = p_category_name
AND LANGUAGE = p_language;
SELECT 1
FROM fnd_lobs
WHERE file_id = p_file_id;
SELECT au.LANGUAGE,au.USER_ID
FROM asg_user au
WHERE au.user_name = p_user_name;
SELECT ORACLE_USERNAME from FND_ORACLE_USERID
WHERE ORACLE_ID =883;
select data_type from all_tab_columns
where table_name = 'CSF_M_LOBS_INQ'
and column_name = 'FILE_DATA'
and OWNER = c_schema;
select data_type from user_synonyms syn, dba_tab_columns col
where syn.table_owner = c_schema
and syn.synonym_name = 'CSF_M_LOBS_INQ'
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name = 'FILE_DATA';
l_error_msg := 'Entering ' || g_object_name || '.APPLY_INSERT'|| ' for PK ' || to_char( p_record.file_id);
'CSM_LOBS_PKG.APPLY_INSERT',
FND_LOG.LEVEL_PROCEDURE );
l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'||
' as we are not supporting Attachment for the entity' || TO_CHAR(p_record.entity_name);
CSM_UTIL_PKG.LOG ( l_error_msg ,'CSM_LOBS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE );
SELECT fnd_lobs_s.nextval
INTO l_file_id
FROM dual;
/* INSERT INTO fnd_lobs(file_id,
file_name,
file_content_type,
file_data,
upload_date,
language,
file_format)
SELECT l_file_id AS FILE_ID,
l_file_name AS FILE_NAME,
l_file_content_type AS FILE_CONTENT_TYPE,
file_data AS FILE_DATA,
SYSDATE as UPLOAD_DATE,
l_language as LANGUAGE,
'binary' AS FILE_FORMAT
FROM csf_m_lobs_inq
WHERE file_id = l_file_id
AND tranid$$ = p_record.tranid$$
AND clid$$cs = p_record.clid$$cs;*/
l_dsql := 'INSERT INTO fnd_lobs(file_id,'
|| 'file_name,'
|| 'file_content_type,'
|| 'file_data,'
|| 'upload_date,'
|| 'language, '
|| 'file_format)'
|| 'SELECT ' || l_file_id || ' AS FILE_ID,'
|| '''' || l_file_name || ''' AS FILE_NAME,'
|| '''' || l_file_content_type || ''' AS FILE_CONTENT_TYPE, '
|| 'file_data AS FILE_DATA, '
||' SYSDATE AS UPLOAD_DATE,'
||'''' || l_language || ''' AS LANGUAGE,'
||'''binary''' || ' AS FILE_FORMAT '
||' FROM csf_m_lobs_inq'
|| ' WHERE file_id = ' || l_file_id
|| ' AND tranid$$ = ' || p_record.tranid$$
|| ' AND clid$$cs = ''' || p_record.clid$$cs || '''';
l_dsql := 'INSERT INTO fnd_lobs(file_id,'
|| 'file_name,'
|| 'file_content_type,'
|| 'file_data,'
|| 'upload_date,'
|| 'language, '
|| 'file_format)'
|| 'SELECT ' || l_file_id || ' AS FILE_ID,'
|| '''' || l_file_name || ''' AS FILE_NAME,'
|| '''' || l_file_content_type || ''' AS FILE_CONTENT_TYPE, '
|| 'TO_LOB(file_data) AS FILE_DATA, '
||' SYSDATE AS UPLOAD_DATE,'
||'''' || l_language || ''' AS LANGUAGE,'
||'''binary''' || ' AS FILE_FORMAT '
||' FROM csf_m_lobs_inq'
|| ' WHERE file_id = ' || l_file_id
|| ' AND tranid$$ = ' || p_record.tranid$$
|| ' AND clid$$cs = ''' || p_record.clid$$cs || '''';
--Inserting data into the Access table to have the record in the client immd.Without running JTM progmram
CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD(l_task_assignment_id,l_user_id,l_entity_name,l_data_type_id,l_dodirty);
l_error_msg := 'The record is going to get rejected as its successfully inserted into the Base Table.';
CSM_UTIL_PKG.LOG ( l_error_msg , 'CSM_LOBS_PKG.APPLY_INSERT',
FND_LOG.LEVEL_PROCEDURE );
l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char (p_record.file_id);
CSM_UTIL_PKG.LOG ( l_error_msg , 'CSM_LOBS_PKG.APPLY_INSERT',
FND_LOG.LEVEL_PROCEDURE );
l_error_msg := 'Exception occurred in ' || g_object_name || '.APPLY_INSERT and hence leaving it:' || ' ' || sqlerrm
|| ' for PK ' || to_char (p_record.file_id );
CSM_UTIL_PKG.LOG ( l_error_msg ,'CSM_LOBS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
END APPLY_INSERT;
APPLY_INSERT
(
p_record,
p_error_msg,
x_return_status
);
l_error_msg := 'Update is not supported for this entity ' || g_object_name;
l_error_msg := 'Delete is not supported for this entity ' || g_object_name ;
a fast sync. This procedure will insert the data that came from mobile into the backend tables using
public APIs.
***/
PROCEDURE APPLY_CLIENT_CHANGES
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_debug_level IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_process_status VARCHAR2(1);
/*** Yes -> delete record from inqueue ***/
CSM_UTIL_PKG.LOG
( 'Record successfully processed, deleting from inqueue ' || g_object_name
|| ' for PK ' || r_FND_LOBS.FILE_ID,
'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
FND_LOG.LEVEL_EVENT );
CSM_UTIL_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_FND_LOBS.seqno$$,
r_FND_LOBS.FILE_ID, -- put PK column here
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
CSM_UTIL_PKG.LOG
( 'Deleting from inqueue failed, rolling back to savepoint for entity ' || g_object_name
|| ' and PK ' || r_FND_LOBS.FILE_ID,
'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES' ,
FND_LOG.LEVEL_EVENT);
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
CSM_UTIL_PKG.LOG
( 'Record not processed successfully, deferring and rejecting record for entity ' || g_object_name
|| ' and PK ' || r_FND_LOBS.FILE_ID,
'CSM_LOBS_LOBS.APPLY_CLIENT_CHANGES',
FND_LOG.LEVEL_EVENT );