The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT(CONTENT_NUMBER) c_num FROM JTF_FM_REQUEST_CONTENTS
WHERE REQUEST_ID = p_request_id;
SELECT DOCUMENT_TYPE FROM JTF_FM_REQUEST_CONTENTS
WHERE REQUEST_ID = p_request_id AND CONTENT_NUMBER = l_content_number;
SELECT file_name,file_content_type INTO l_file_name, l_file_content_type FROM fnd_lobs WHERE file_id = p_file_id;
SELECT file_name,file_content_type INTO l_file_name, l_file_content_type FROM fnd_lobs WHERE file_id = p_file_id;
SELECT file_name,file_content_type INTO l_file_name, l_file_content_type FROM fnd_lobs WHERE file_id = p_file_id;
select fnd_profile.value('ICX_CLIENT_IANA_ENCODING') into l_encoding from dual;
PROCEDURE INSERT_EMAIL_STATS
(
p_request_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_EMAIL_STATS';
--UPDATE JTF_FM_EMAIL_STATSwith the new information
INSERT INTO JTF_FM_EMAIL_STATS
(
REQUEST_ID,
TOTAL,
SENT,
MALFORMED,
BOUNCED,
OPENED,
UNSUBSCRIBED,
DO_NOT_CONTACT,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
RESUBMITTED_MALFORMED, RESUBMITTED_JOB_COUNT)
VALUES (
p_request_id,
0,
0,
0,
0,
0,
0,
0,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,
0,
0);
JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,'JTF_FM_REQUEST_GRP.UPDATE_REQUESTS_AQ');
END INSERT_EMAIL_STATS;
-- mpetrosi 4-oct-2001 added a.f_deletedflag is null
-- mpetrosi 15-oct-2001 added b.f_deletedflag is null
CURSOR CSERV IS
SELECT
a.server_id
FROM
jtf_fm_groups_all a,
jtf_fm_group_fnd_user b,
jtf_fm_fnd_user_v c
WHERE
a.group_id = b.group_id
AND
b.user_id = c.user_id
AND
b.user_id = p_fulfill_electronic_rec.requestor_id
AND
a.f_deletedflag is null
AND
b.f_deletedflag is null;
SELECT
count(hist_req_id)
FROM
JTF_FM_REQUEST_HISTORY
WHERE
hist_req_id = fm_pvt_rec.request_id;
SELECT
DECODE(fm_pvt_rec.queue,'M', MASS_REQUEST_Q, 'B', BATCH_REQUEST_Q,'MP',
MASS_PAUSE_Q ,'BP', BATCH_PAUSE_Q,REQUEST_QUEUE_NAME)
FROM
JTF_FM_SERVICE_ALL
WHERE
SERVER_ID = l_server_id;
SELECT Fnd_Profile.value('JTF_FM_DEFAULT_SERVER') INTO l_server_id FROM DUAL;
INSERT INTO JTF_FM_REQUEST_HISTORY_ALL
(
HIST_REQ_ID,
SUBMIT_DT_TM,
REQUEST,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_TYPE,
MEDIA_TYPE
)
VALUES
(
fm_pvt_rec.request_id,
l_submit_dt,
empty_clob(),
l_submit_dt,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_submit_dt,
FND_GLOBAL.USER_ID,
fm_pvt_rec.queue,
l_media_type
);
INSERT INTO JTF_FM_STATUS_ALL
(
REQUEST_ID,
SUBMIT_DT_TM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
VALUES
(
fm_pvt_rec.request_id,
l_submit_dt,
l_submit_dt,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
l_submit_dt,
FND_GLOBAL.USER_ID
);
JTF_FM_UTL_V.PRINT_MESSAGE('Updated record in status******',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
INSERT INTO JTF_FM_EMAIL_STATS
(
REQUEST_ID,
TOTAL,
SENT,
MALFORMED,
BOUNCED,
OPENED,UNSUBSCRIBED,DO_NOT_CONTACT,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
VALUES (fm_pvt_rec.request_id,0,0,0,0,
0,0,0,FND_GLOBAL.USER_ID,l_submit_dt,
FND_GLOBAL.USER_ID,l_submit_dt);
select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))
into l_org_id
from dual;
SELECT
REQUEST INTO l_request
FROM
JTF_FM_REQUEST_HISTORY_ALL
WHERE
HIST_REQ_ID = fm_pvt_rec.request_id
AND
SUBMIT_DT_TM = l_submit_dt
FOR UPDATE;
-- Mod to update org_id when sent
BEGIN
UPDATE JTF_FM_REQUEST_HISTORY_ALL
SET
TEMPLATE_ID = decode(p_fulfill_electronic_rec.template_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.template_id),
USER_ID = p_fulfill_electronic_rec.requestor_id,
PRIORITY = fm_pvt_rec.priority,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
SOURCE_CODE_ID = decode(p_fulfill_electronic_rec.source_code_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.source_code_id),
SOURCE_CODE = decode(p_fulfill_electronic_rec.source_code, FND_API.G_MISS_CHAR,
NULL,p_fulfill_electronic_rec.source_code),
OBJECT_TYPE = decode(p_fulfill_electronic_rec.object_type, FND_API.G_MISS_CHAR,
NULL,p_fulfill_electronic_rec.object_type),
OBJECT_ID = decode(p_fulfill_electronic_rec.object_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.object_id),
ORDER_ID = decode(p_fulfill_electronic_rec.order_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.order_id),
RESUBMIT_COUNT = 1,
SERVER_ID = l_server_id,
MESSAGE_ID = l_message_handle,
OUTCOME_CODE = l_meaning,
ORG_ID = l_org_id,
OBJECT_VERSION_NUMBER = 1,
REQUEST_TYPE = l_request_type
WHERE
HIST_REQ_ID = fm_pvt_rec.request_id;
-- Update tables with the type of request.
-- Updating the status table
BEGIN
UPDATE JTF_FM_STATUS_ALL
SET
TEMPLATE_ID = decode(p_fulfill_electronic_rec.template_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.template_id),
USER_ID = p_fulfill_electronic_rec.requestor_id,
PRIORITY = fm_pvt_rec.priority,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
SOURCE_CODE_ID = decode(p_fulfill_electronic_rec.source_code_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.source_code_id),
SOURCE_CODE = decode(p_fulfill_electronic_rec.source_code, FND_API.G_MISS_CHAR,
NULL,p_fulfill_electronic_rec.source_code),
OBJECT_TYPE = decode(p_fulfill_electronic_rec.object_type, FND_API.G_MISS_CHAR,
NULL,p_fulfill_electronic_rec.object_type),
OBJECT_ID = decode(p_fulfill_electronic_rec.object_id, FND_API.G_MISS_NUM,
NULL,p_fulfill_electronic_rec.object_id),
ORDER_ID = decode(p_fulfill_electronic_rec.order_id, FND_API.G_MISS_NUM, NULL,
p_fulfill_electronic_rec.order_id),
SERVER_ID = l_server_id,REQUEUE_COUNT = 1,
MESSAGE_ID = l_message_handle,
REQUEST_STATUS = l_meaning,
ORG_ID = l_org_id,
OBJECT_VERSION_NUMBER = 1
WHERE
REQUEST_ID = fm_pvt_rec.request_id;