The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_REQUEST_CONTENTS(
p_request_id IN NUMBER,
p_content_id IN NUMBER,
p_content_number IN NUMBER,
p_content_name IN VARCHAR2,
p_content_type IN VARCHAR2,
p_document_type IN VARCHAR2,
p_body IN VARCHAR2,
p_user_note IN VARCHAR2,
p_quantity IN NUMBER,
p_media_type IN VARCHAR2,
p_content_source IN VARCHAR2,
p_file_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REQUEST_CONTENTS';
JTF_FM_UTL_V.PRINT_MESSAGE('Begin PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
INSERT INTO JTF_FM_REQUEST_CONTENTS (
REQUEST_ID,
CONTENT_ID,
CONTENT_NUMBER,
CONTENT_NAME,
CONTENT_TYPE,
DOCUMENT_TYPE,
BODY,
USER_NOTES,
QUANTITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MEDIA_TYPE,
CONTENT_SOURCE,
FND_FILE_ID)
VALUES (
p_request_id ,
p_content_id,
p_content_number,
p_content_name,
p_content_type,
p_document_type,
p_body,
p_user_note,
p_quantity ,
FND_GLOBAL.USER_ID ,
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE,
FND_GLOBAL.LOGIN_ID ,
p_media_type ,
p_content_source ,
p_file_id );
JTF_FM_UTL_V.PRINT_MESSAGE('End PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
JTF_FM_UTL_V.PRINT_MESSAGE('UNEXPECTED ERROR IN PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
END INSERT_REQUEST_CONTENTS;
SELECT FILE_NAME into l_file_name from fnd_lobs where file_id = p_file_id ;
SELECT attachment_file_id
FROM
ibc_content_items b,
ibc_citem_versions_vl a
WHERE
b.live_citem_version_id = a.citem_version_id
AND
b.content_item_id = p_citem_id;
SELECT attachment_file_id
FROM ibc_citem_versions_vl a,
(SELECT MAX(version_number) version_number
FROM ibc_citem_versions_b
WHERE content_item_id=p_citem_id) b
WHERE
a.content_item_id = p_citem_id AND
a.version_number = b.version_number;
SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
WHERE REQUEST_ID = p_request_id ;
SELECT ATTACH_FID INTO file_id FROM ibc_citems_v
WHERE CITEM_VER_ID = p_citem_ver_id AND item_status = 'APPROVED'
and LANGUAGE = USERENV('LANG');
PROCEDURE GET_AND_INSERT_REQUEST_DETAILS(
p_request_id IN NUMBER,
p_content_id IN NUMBER,
p_user_note IN VARCHAR2,
p_quantity IN NUMBER,
p_media_type IN VARCHAR2,
p_query_id IN NUMBER,
p_email_format IN VARCHAR2,
p_version IN NUMBER,
p_content_nm IN VARCHAR2,
rendition_file_names IN JTF_VARCHAR2_TABLE_300,
rendition_mime_names IN JTF_VARCHAR2_TABLE_100,
rendition_mime_types IN JTF_VARCHAR2_TABLE_100,
rendition_file_ids IN JTF_NUMBER_TABLE,
x_rend_xml OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT varchar2(30) := 'GET_AND_INSERT_REQUEST_DETAILS';
JTF_FM_UTL_V.PRINT_MESSAGE('GET_AND_INSERT_REQUEST_DETAILS name = ' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
-- Now begin construction of XML and insert into request contents
x_html := x_html || '
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
rtf_mime_name,
G_MIME_TBL(4),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
rtf_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
pdf_mime_name,
G_MIME_TBL(3),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
pdf_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
html_mime_name,
G_MIME_TBL(1),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
html_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
text_mime_name,
G_MIME_TBL(2),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
text_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
html_mime_name,
G_MIME_TBL(1),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
html_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
text_mime_name,
G_MIME_TBL(2),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
text_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
html_mime_name,
G_MIME_TBL(1),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
html_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
html_mime_name,
G_MIME_TBL(1),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
html_file_id);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
text_mime_name,
G_MIME_TBL(2),
'Y',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
text_file_id);
END GET_AND_INSERT_REQUEST_DETAILS;
last_updated_by NUMBER;
last_update_date DATE;
SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
WHERE REQUEST_ID = p_request_id ;
SELECT MAX(CITEM_VER_ID) INTO x_item_version_id
FROM IBC_CITEMS_V
WHERE CITEM_ID = TO_NUMBER(p_content_id)
AND LANGUAGE = USERENV('LANG') ;
SELECT live_citem_version_id INTO x_item_version_id
FROM ibc_content_items
WHERE content_item_id = p_content_id;
,last_updated_by
,last_update_date
,attribute_type_codes
,attribute_type_names
,attributes
,component_citems
,component_citem_ver_ids
,component_attrib_types
,component_citem_names
,component_owner_ids
,componenet_owner_types
,component_sort_orders
,keywords
,return_status
,msg_count
,msg_data);
INSERT_REQUEST_CONTENTS(
p_request_id,
p_content_id,
l_count_total,
l_file_name,
'ATTACHMENT',
l_doc_type,
'N',
p_user_note,
p_quantity,
p_media_type,
'ocm' ,
x_temp_file_id);
GET_AND_INSERT_REQUEST_DETAILS(
p_request_id,
p_content_id,
p_user_note,
p_quantity,
p_media_type,
x_query_file_id,
p_email_format,
p_version,
p_content_nm,
rendition_file_names,
rendition_mime_names,
rendition_mime_types,
rendition_file_ids ,
x_rend_xml,
return_status,
msg_count,
msg_data);
DELETE FROM JTF_FM_TEST_REQUESTS WHERE REQUEST_ID = p_request_id;
/* select chr(13) cr, chr(10) lf into b, c from dual;
just before calling send request we will insert a record into
history table. Reason. GET_FILE_ID should
know about REQUEST_TYPE 'T'
**/
IF (upper(p_fulfill_electronic_rec.request_type) = 'T') THEN
JTF_FM_UTL_V.PRINT_MESSAGE('THE REQUEST TYPE IS TEST',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
INSERT INTO JTF_FM_TEST_REQUESTS (REQUEST_ID) VALUES (x_request_history_id);
UPDATE JTF_FM_REQUEST_HISTORY_ALL
SET request_type = 'T'
WHERE hist_req_id = x_request_history_id;