[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT m4u_dmd_msgid_s.nextval
INTO l_msg_id
FROM DUAL;
m4u_dmd_utils.log('insert into m4u_dmd_messages' ,1);
INSERT INTO m4u_dmd_messages
(
msg_id, ref_msg_id, type, direction, status, retry_count,
orig_msg_id, sender_gln, receiver_gln, rep_party_gln,
user_gln,user_id, msg_timestamp, payload_id,
last_update_date, last_updated_by,creation_date, created_by,last_update_login
)
VALUES
(
l_msg_id, p_ref_msg_id,p_type, p_direction, p_status, 0,
l_orig_msg_id, p_sender_gln, p_receiver_gln, p_rep_party_gln,
p_user_gln,p_user_id, p_msg_timstamp, p_payload_id,
sysdate, FND_GLOBAL.user_id,sysdate, FND_GLOBAL.user_id, FND_GLOBAL.login_id
);
SELECT m4u_dmd_payloadid_s.NEXTVAL
INTO l_payload_id
FROM dual;
m4u_dmd_utils.log('insert into m4u_dmd_payloads' ,1);
INSERT INTO m4u_dmd_payloads
(
payload_id, payload, type, direction,
last_update_date, last_updated_by,creation_date,created_by, last_update_login
)
VALUES
(
l_payload_id, p_xml, p_type,p_dir, sysdate, FND_GLOBAL.user_id,
sysdate,FND_GLOBAL.user_id, FND_GLOBAL.login_id
);
SELECT m4u_dmd_docid_s.NEXTVAL
INTO l_doc_id
FROM dual;
m4u_dmd_utils.log('insert into m4u_dmd_documents' ,1);
INSERT INTO m4u_dmd_documents
(
msg_id,doc_id,type ,action, retry_count,
doc_status, functional_status,processing_message,
ref_doc_id,orig_doc_id,doc_timestamp,
top_gtin,info_provider_gln,data_recepient_gln,
target_market_country,target_market_sub_div,
parameter1,parameter2,parameter3,parameter4,parameter5,
lparameter1,lparameter2,lparameter3,lparameter4,lparameter5,
last_update_date,last_updated_by,creation_date, created_by,
last_update_login)
VALUES
( p_msg_id,l_doc_id,p_type,p_action,0,
p_doc_status, p_func_status,p_processing_msg,
p_ref_doc_id,l_orig_doc_id,p_timestamp,
p_top_gtin,p_info_provider_gln,p_data_rcpt_gln,
p_tgt_mkt_ctry,p_tgt_mkt_div,
p_param1,p_param2,p_param3,p_param4,p_param5,
p_lparam1,p_lparam2,p_lparam3,p_lparam4,p_lparam5,
sysdate,FND_GLOBAL.user_id,sysdate, FND_GLOBAL.user_id,
FND_GLOBAL.login_id
);
SELECT *
FROM m4u_dmd_documents
WHERE msg_id = p_msg_id;
SELECT retry_count
INTO l_retry_count
FROM m4u_dmd_messages
WHERE msg_id = p_msg_id;
UPDATE m4u_dmd_messages
SET status = m4u_dmd_utils.c_sts_ready,
retry_count = l_retry_count,
orig_msg_id = type || '.' || sysdate || '.' ||
l_retry_count || '.' || p_msg_id
WHERE msg_id = p_msg_id;
m4u_dmd_utils.log('Call update_document - ' || l_doc_rec.doc_id,1);
(l_doc_rec.last_update_date - nvl(p_time,sysdate) < 0))
OR (p_mode = m4u_dmd_utils.c_retry_err AND
l_doc_rec.doc_status = m4u_dmd_utils.c_sts_error)) THEN
m4u_dmd_requests.update_document
(
p_doc_id => l_doc_rec.doc_id,
p_retry_count => l_retry_count,
p_orig_doc_id => 'GENERATE',
p_doc_status => m4u_dmd_utils.c_sts_ready,
p_func_status => null,
x_ret_sts => l_ret_sts,
x_ret_msg => l_ret_msg
);
PROCEDURE update_request
(
p_msg_id IN VARCHAR2,
p_status IN VARCHAR2,
p_update_doc_flag IN VARCHAR2,
p_retry_count IN NUMBER,
p_ref_msg_id IN VARCHAR2 := NULL,
p_orig_msg_id IN VARCHAR2 := NULL,
p_msg_timstamp IN DATE := NULL,
p_sender_gln IN VARCHAR2 := NULL,
p_receiver_gln IN VARCHAR2 := NULL,
p_rep_party_gln IN VARCHAR2 := NULL,
p_user_id IN VARCHAR2 := NULL,
p_user_gln IN VARCHAR2 := NULL,
p_bpel_instance_id IN VARCHAR2 := NULL,
p_bpel_process_id IN VARCHAR2 := NULL,
p_doc_type IN VARCHAR2,
p_doc_status IN VARCHAR2 := NULL,
p_func_status IN VARCHAR2 := NULL,
p_processing_msg IN VARCHAR2 := NULL,
p_payload IN CLOB := NULL,
p_payload_dir IN VARCHAR2 := NULL,
p_payload_type IN VARCHAR2 := NULL,
x_ret_sts OUT NOCOPY VARCHAR2,
x_ret_msg OUT NOCOPY VARCHAR2
)
IS
CURSOR docs_for_msg(p_msg_id IN VARCHAR2, p_retry_count IN NUMBER) IS
SELECT *
FROM m4u_dmd_documents
WHERE msg_id = p_msg_id
AND retry_count = p_retry_count;
l_err_api VARCHAR2(50) := 'm4u_dmd_requests.update_request';
m4u_dmd_utils.log('Entering m4u_dmd_request.update_request',2);
SELECT msg_id, retry_count
INTO l_msg_id, l_retry_count
FROM m4u_dmd_messages
WHERE orig_msg_id = p_orig_msg_id
OR msg_id = p_msg_id;
m4u_dmd_utils.log('UPDATE m4u_dmd_messages' ,1);
UPDATE m4u_dmd_messages
SET
ref_msg_id = NVL(p_ref_msg_id, ref_msg_id ),
orig_msg_id = NVL(p_orig_msg_id, orig_msg_id ),
status = NVL(p_status, status ),
msg_timestamp = NVL(p_msg_timstamp, msg_timestamp ),
bpel_process_id = NVL(p_bpel_process_id, bpel_process_id),
bpel_instance_id = NVL(p_bpel_instance_id, bpel_instance_id),
sender_gln = NVL(p_sender_gln, sender_gln ),
receiver_gln = NVL(p_receiver_gln, receiver_gln ),
rep_party_gln = NVL(p_rep_party_gln, rep_party_gln ),
user_id = NVL(p_user_id, user_id ),
user_gln = NVL(p_user_gln, user_gln )
WHERE msg_id = l_msg_id;
m4u_dmd_utils.log('Update SQL%ROWCOUNT - '||SQL%rowcount ,1);
IF p_update_doc_flag = 'Y' THEN
-- loop and update all req documents
m4u_dmd_utils.log('Update documents',1);
m4u_dmd_utils.log('Call update_document - ' || l_doc_rec.doc_id,1);
m4u_dmd_requests.update_document
(
p_doc_id => l_doc_rec.doc_id,
p_ref_doc_id => l_doc_rec.ref_doc_id,
p_doc_status => p_doc_status,
p_func_status => null,
p_retry_count => l_retry_count,
p_processing_msg => p_processing_msg,
p_payload_id => l_payload_id,
p_payload_dir => p_payload_dir,
p_payload_type => p_payload_type,
x_ret_sts => l_ret_sts,
x_ret_msg => l_ret_msg
);
m4u_dmd_utils.log('Exiting m4u_dmd_request.update_request - Success',2);
END update_request;
PROCEDURE update_document
(
p_doc_id IN VARCHAR2,
p_doc_status IN VARCHAR2,
p_func_status IN VARCHAR2,
p_retry_count IN NUMBER,
p_processing_msg IN VARCHAR2 := NULL,
p_ref_doc_id IN VARCHAR2 := NULL,
p_orig_doc_id IN VARCHAR2 := NULL,
p_timestamp IN VARCHAR2 := NULL,
p_top_gtin IN VARCHAR2 := NULL,
p_info_provider_gln IN VARCHAR2 := NULL,
p_data_recepient_gln IN VARCHAR2 := NULL,
p_tgt_mkt_cntry IN VARCHAR2 := NULL,
p_tgt_mkt_subdiv IN VARCHAR2 := NULL,
p_param1 IN VARCHAR2 := NULL,
p_param2 IN VARCHAR2 := NULL,
p_param3 IN VARCHAR2 := NULL,
p_param4 IN VARCHAR2 := NULL,
p_param5 IN VARCHAR2 := NULL,
p_lparam1 IN VARCHAR2 := NULL,
p_lparam2 IN VARCHAR2 := NULL,
p_lparam3 IN VARCHAR2 := NULL,
p_lparam4 IN VARCHAR2 := NULL,
p_lparam5 IN VARCHAR2 := NULL,
p_payload_id IN VARCHAR2 := NULL,
p_payload_dir IN VARCHAR2 := NULL,
p_payload_type IN VARCHAR2 := NULL,
x_ret_sts OUT NOCOPY VARCHAR2,
x_ret_msg OUT NOCOPY VARCHAR2
)
IS
l_cln_evt_params wf_parameter_list_t;
l_err_api VARCHAR2(50) := 'm4u_dmd_requests.update_document';
m4u_dmd_utils.log('Entering m4u_dmd_request.update_document',2);
SELECT doc_id , retry_count
INTO l_doc_id, l_retry_count
FROM m4u_dmd_documents
WHERE orig_doc_id = p_orig_doc_id;
SELECT *
INTO l_doc_rec
FROM m4u_dmd_documents
WHERE doc_id = l_doc_id;
m4u_dmd_utils.log('UPDATE m4u_dmd_documents' ,1);
UPDATE m4u_dmd_documents
SET ref_doc_id = NVL(p_ref_doc_id, ref_doc_id ),
orig_doc_id = NVL(l_orig_doc_id, orig_doc_id ),
retry_count = NVL(l_retry_count,retry_count ),
doc_status = NVL(p_doc_status,doc_status ),
processing_message = NVL(p_processing_msg,processing_message ),
functional_status = NVL(p_func_status,functional_status ),
doc_timestamp = NVL(p_timestamp, doc_timestamp ),
top_gtin = NVL(p_top_gtin, top_gtin ),
info_provider_gln = NVL(p_info_provider_gln , info_provider_gln ),
data_recepient_gln = NVL(p_data_recepient_gln, data_recepient_gln ),
target_market_country = NVL(target_market_country, p_tgt_mkt_cntry ),
target_market_sub_div = NVL(target_market_sub_div, p_tgt_mkt_subdiv ),
parameter1 = NVL(p_param1 , parameter1 ),
parameter2 = NVL(p_param2 , parameter2 ),
parameter3 = NVL(p_param3 , parameter3 ),
parameter4 = NVL(p_param4 , parameter4 ),
parameter5 = NVL(p_param5 , parameter5 ),
lparameter1 = NVL(p_lparam1 , lparameter1),
lparameter2 = NVL(p_lparam2 , lparameter2),
lparameter3 = NVL(p_lparam3 , lparameter3),
lparameter4 = NVL(p_lparam4 , lparameter4),
lparameter5 = NVL(p_lparam5 , lparameter5),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE doc_id = l_doc_id;
m4u_dmd_utils.log('Update SQL%ROWCOUNT - ' ||SQL%rowcount,1);
m4u_dmd_utils.log('Update CIS record ',1);
SELECT status
INTO l_cis_status
FROM m4u_dmd_subscriptions
WHERE subscription_name = l_doc_rec.lparameter1;
ELSIF l_cis_status = 'DELETE_IN_PROGRESS' THEN
IF p_doc_status = m4u_dmd_utils.c_sts_success THEN
l_cis_status := 'UNSUBSCRIBED';
l_cis_status := 'DELETE_FAILED';
UPDATE m4u_dmd_subscriptions
SET status = l_cis_status
WHERE subscription_name = l_doc_rec.lparameter1;
m4u_dmd_utils.log('Update SQL%ROWCOUNT - ' ||SQL%rowcount,1);
m4u_dmd_utils.log('Exiting m4u_dmd_request.update_document - Success',2);
END update_document;