[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_incidents_all_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND asg.user_id=asg.owner_id
AND fnddoc_tl.document_id = fnddoc.document_id
--AND fnddoc.media_id = lobs.file_id(+)
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.incident_id)
AND asg.user_id = acc.user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'CS_INCIDENTS'
AND fnddoc.datatype_id in(1,5,6)
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_tasks_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
--AND fnddoc.media_id = lobs.file_id(+)
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.task_id)
AND asg.user_id = acc.user_id
AND asg.user_id=asg.owner_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'JTF_TASKS_B'
AND fnddoc.datatype_id in(1,5,6)
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_debrief_headers_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
-- AND fnddoc.media_id = lobs.file_id
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
AND fnddoccat_tl.name = 'SIGNATURE'
AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
AND fnddoc.datatype_id =6
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = asg.user_id
AND asg.user_id=asg.owner_id
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT MAX(HA_PAYLOAD_ID)
FROM CSM_HA_PAYLOAD_DATA
WHERE OBJECT_NAME='FND_DOCUMENTS'
AND PK_VALUE=b_pk_value;
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
/*Update the last run date*/
UPDATE jtm_con_request_data SET last_run_date = l_run_date
WHERE package_name = 'CSM_LOBS_EVENT_PKG'
AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
UPDATE jtm_con_request_data
SET last_run_date = l_run_date
WHERE package_name = 'CSM_LOBS_EVENT_PKG'
AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
PROCEDURE INSERT_ALL_ACC_RECORDS (p_user_id IN NUMBER )
IS
--Cursor to get the SR attachemnts
CURSOR c_SRAtt (b_user_id NUMBER) IS
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_incidents_all_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
-- AND fnddoc.media_id = lobs.file_id (+)
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.incident_id)
AND asg.user_id = acc.user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'CS_INCIDENTS'
AND fnddoc.datatype_id in (1,5,6)
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = b_user_id;
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_tasks_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
-- AND fnddoc.media_id = lobs.file_id (+)
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.task_id)
AND asg.user_id = acc.user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'JTF_TASKS_B'
AND fnddoc.datatype_id in (1,5,6)
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = b_user_id;
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_debrief_headers_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
-- AND fnddoc.media_id = lobs.file_id
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
AND asg.user_id = b_user_id
AND fnddoccat_tl.name = 'SIGNATURE'
AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
AND fnddoc.datatype_id =6
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = asg.user_id;
, 'Entering INSERT_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
--Empty the access table for the user before insert it freshly
--bug 5213097
DELETE FROM CSM_FND_LOBS_ACC WHERE user_id = p_user_id;
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Leaving INSERT_ALL_ACC_RECORDS - Max att download size is less than 1'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
, 'Leaving INSERT_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'INSERT_ALL_ACC_RECORDS'||fnd_global.local_chr(10)||
'Error: '||sqlerrm
, JTM_HOOK_UTIL_PKG.g_debug_level_error);
END INSERT_ALL_ACC_RECORDS;
PROCEDURE INSERT_ACC_RECORD(p_task_assignment_id IN NUMBER, p_user_id IN NUMBER)
IS
CURSOR l_signature_csr(b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
b_lang IN VARCHAR)
IS
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndatt.entity_name, fndatt.pk1_value, fndatt.pk2_value,
fndatt.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM csf_debrief_headers dbfhdr, fnd_attached_documents fndatt,
fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
-- fnd_lobs lobs,
fnd_document_categories_tl fnddoccat_tl,
csm_debrief_headers_acc acc,asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE dbfhdr.task_assignment_id = b_task_assignment_id
AND acc.debrief_header_id = dbfhdr.debrief_header_id
AND acc.user_id = b_user_id
AND asg.user_id = b_user_id
AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
AND fndatt.pk1_value = dbfhdr.debrief_header_id
AND fndatt.document_id = fnddoc_tl.document_id
AND fnddoc_tl.document_id = fnddoc.document_id
AND fnddoc_tl.language = b_lang
--AND fnddoc.media_id = lobs.file_id
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = b_lang
AND fnddoccat_tl.name = 'SIGNATURE'
AND fnddoc.datatype_id = 6
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM jtf_task_assignments jta, jtf_tasks_b jt,
fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_incidents_all_acc acc,asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE jta.task_assignment_id = b_task_assignment_id
AND jta.task_id = jt.task_id
AND jt.source_object_id = acc.incident_id
AND jt.source_object_type_code = 'SR'
AND fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = b_lang
AND fnddoc_tl.document_id = fnddoc.document_id
--AND fnddoc.media_id = lobs.file_id (+)
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = b_lang
AND fndattdoc.pk1_value = to_char(acc.incident_id)
AND acc.user_id = b_user_id
AND asg.user_id = b_user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'CS_INCIDENTS'
AND fnddoc.datatype_id in (1,5,6)
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM jtf_task_assignments jta,
fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
-- fnd_lobs lobs,
csm_tasks_acc acc,asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE jta.task_assignment_id = b_task_assignment_id
AND acc.task_id = jta.task_id
AND fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = b_lang
AND fnddoc_tl.document_id = fnddoc.document_id
--AND fnddoc.media_id = lobs.file_id (+)
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = b_lang
AND fndattdoc.pk1_value = to_char(acc.task_id)
AND acc.user_id = b_user_id
AND asg.user_id = b_user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'JTF_TASKS_B'
AND fnddoc.datatype_id in (1,5,6)
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT LANGUAGE
FROM asg_user
WHERE user_id=b_user_id;
CSM_UTIL_PKG.LOG('Entering INSERT_ACC_RECORD for task_assignment_id: ' || p_task_assignment_id,
'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_PROCEDURE);
, 'Entering CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
, 'Leaving CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD - Max att download size is less than 1'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
CSM_UTIL_PKG.LOG('Leaving INSERT_ACC_RECORD for task_assignment_id: ' || p_task_assignment_id,
'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_PROCEDURE);
l_error_msg := ' FAILED MarkDirty in INSERT_ACC_RECORD for task_assignment_id:' || to_char(p_task_assignment_id);
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
l_error_msg := ' Exception in INSERT_ACC_RECORD for task_assignment_id:'
|| to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
END INSERT_ACC_RECORD;
PROCEDURE DELETE_ACC_RECORD(p_task_assignment_id IN NUMBER, p_resource_id IN NUMBER)
IS
--Cursor for signature
CURSOR l_signature_csr(b_task_assignment_id IN number, b_user_id IN number)
IS
SELECT acc.access_id, acc.user_id
FROM csm_fnd_lobs_acc acc,
csf_debrief_headers dbh
WHERE acc.user_id = b_user_id
AND acc.entity_name = 'CSF_DEBRIEF_HEADERS'
AND dbh.task_assignment_id = b_task_assignment_id
AND acc.pk1_value = to_char(dbh.debrief_header_id);
SELECT acc.access_id, acc.user_id
FROM csm_fnd_lobs_acc acc,
jtf_task_assignments jta,
jtf_tasks_b jt
WHERE acc.user_id = b_user_id
AND acc.entity_name = 'CS_INCIDENTS'
AND jt.task_id = jta.task_id
AND jta.task_assignment_id = b_task_assignment_id
AND acc.pk1_value = to_char(jt.source_object_id);
SELECT acc.access_id, acc.user_id
FROM csm_fnd_lobs_acc acc,
jtf_task_assignments jta
WHERE acc.user_id = b_user_id
AND acc.entity_name = 'JTF_TASKS_B'
AND jta.task_assignment_id = b_task_assignment_id
AND acc.pk1_value = to_char(jta.task_id);
SELECT user_id
FROM asg_user
WHERE resource_id=b_resource_id;
, 'Entering CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
, 'Exiting CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
CSM_UTIL_PKG.LOG('Leaving DELETE_ACC_RECORD for task_assignment_id: ' || p_task_assignment_id,
'CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD',FND_LOG.LEVEL_PROCEDURE);
l_error_msg := ' FAILED MarkDirty in DELETE_ACC_RECORD for task_assignment_id:' || to_char(p_task_assignment_id);
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
l_error_msg := ' Exception in DELETE_ACC_RECORD for task_assignment_id:'
|| to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD',FND_LOG.LEVEL_EXCEPTION);
END DELETE_ACC_RECORD;
PROCEDURE INSERT_ACC_ON_UPLOAD(p_PK1_value IN NUMBER, p_user_id IN NUMBER,
p_entity_name IN VARCHAR, p_data_typeid IN NUMBER, p_dodirty BOOLEAN)
IS
-- get SR attachments
--p_dodirty is used only for lobs attachments(6).if its sets to true then mark dirty has to be done.The reason
--for this is the file id sent from client may not be proper and so we allow the server to generate the file id
CURSOR l_SRAtt_csr (b_incident_id IN NUMBER, b_user_id IN NUMBER,
b_data_typeid IN NUMBER)
IS
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num, asg.user_name, fnddoccat_tl.user_name,
fnddoc.usage_type, doctype.user_name, fnddoc.datatype_id,
fnddoc.document_id, fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl,
fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
csm_incidents_all_acc acc,
asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.incident_id)
AND fndattdoc.pk1_value = to_char(b_incident_id)
AND asg.user_id = acc.user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'CS_INCIDENTS'
AND fnddoc.datatype_id = b_data_typeid
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = b_user_id
AND NOT EXISTS (SELECT 1
FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num, asg.user_name, fnddoccat_tl.user_name,
fnddoc.usage_type, doctype.user_name, fnddoc.datatype_id,
fnddoc.document_id, fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl,
fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
csm_tasks_acc acc,
asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.task_id)
AND fndattdoc.pk1_value = to_char(b_task_id)
AND asg.user_id = acc.user_id
AND fnddoccat_tl.name = 'MISC'
AND fndattdoc.entity_name = 'JTF_TASKS_B'
AND fnddoc.datatype_id = b_data_typeid
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = b_user_id
AND NOT EXISTS (SELECT 1
FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
fnddoc.document_id,fnddoc_tl.title
FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
fnd_document_categories_tl fnddoccat_tl,
fnd_attached_documents fndattdoc,
csm_debrief_headers_acc acc, asg_user asg,
FND_DOCUMENT_DATATYPES doctype
WHERE fndattdoc.document_id = fnddoc_tl.document_id
AND fnddoc_tl.language = asg.language
AND fnddoc_tl.document_id = fnddoc.document_id
AND fnddoccat_tl.category_id = fnddoc.category_id
AND fnddoccat_tl.language = asg.language
AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
AND acc.debrief_header_id = b_debrief_id
AND fnddoccat_tl.name = 'SIGNATURE'
AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
AND fnddoc.datatype_id =b_data_typeid
AND doctype.datatype_id = fnddoc.datatype_id
AND doctype.language = asg.language
AND acc.user_id = asg.user_id
AND acc.user_id=b_user_id
AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
WHERE cflacc.document_id = fnddoc.document_id
AND cflacc.user_id = acc.user_id);
CSM_UTIL_PKG.LOG('Entering INSERT_ACC_ON_UPLOAD for task_assignment_id: ' || p_PK1_value,
'CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD',FND_LOG.LEVEL_PROCEDURE);
, 'Entering CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
, 'Leaving CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD - Max att download size is less than 1'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
);
l_tab_access_id.delete;
l_tab_user_id.delete;
l_tab_file_id.delete;
l_tab_name.delete;
l_tab_description.delete;
l_tab_language.delete;
l_tab_entity_name.delete;
l_tab_pk1_value.delete;
l_tab_pk2_value.delete;
l_tab_seq_num.delete;
l_tab_upd_user.delete;
l_tab_category.delete;
l_tab_usage_type.delete;
l_tab_data_type.delete;
l_tab_datatype_id.delete;
l_tab_document_id.delete;
l_tab_title.delete;
, 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
);
INSERT INTO CSM_FND_LOBS_ACC
(access_id, file_id, user_id, name, description,
language, entity_name, pk1_value, pk2_value, seq_num,
update_user, category, usage_type, data_type, counter,
last_update_date, last_updated_by, creation_date,created_by, data_type_id,
document_id,title)
VALUES (l_tab_access_id(i), l_tab_file_id(i), l_tab_user_id(i), l_tab_name(i), l_tab_description(i),
l_tab_language(i), l_tab_entity_name(i), l_tab_pk1_value(i), l_tab_pk2_value(i), l_tab_seq_num(i),
l_tab_upd_user(i), l_tab_category(i), l_tab_usage_type(i),l_tab_data_type(i), 1,
sysdate, 1, sysdate, 1, l_tab_datatype_id(i),
l_tab_document_id(i),l_tab_title(i));
l_error_msg := ' Exception in INSERT_ACC_ON_UPLOAD for PK1_value:'
|| to_char(p_PK1_value) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_LOBS_EVENT_PKG.INSERT_ACC_ON_UPLOAD',FND_LOG.LEVEL_EXCEPTION);
END INSERT_ACC_ON_UPLOAD;