PROCEDURE call_web_service(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
P_ORGANIZATION_ID IN NUMBER,
P_PERIOD VARCHAR2,
x_request_xmltype OUT NOCOPY sys.xmltype,
x_response_xmltype OUT NOCOPY sys.xmltype) IS
l_api_name CONSTANT VARCHAR2(30) := 'call_web_service';
l_savepoint_name CONSTANT VARCHAR2(30) := 'SP01_call_ws';
l_soap_content CLOB := '';
-- l_soap_content VARCHAR2(32767);
l_soap_request CLOB;
l_soap_request_sign varchar2(2048);
l_http_req utl_http.req;
l_http_resp utl_http.resp;
l_soap_respond VARCHAR2(32767);
l_resp_xml xmltype;
l_doc dbms_xmldom.domdocument;
l_docelem dbms_xmldom.domelement;
l_nodelist dbms_xmldom.domnodelist;
l_return_status VARCHAR2(10);
l_length NUMBER;
l_n dbms_xmldom.domnode;
l_case_num VARCHAR2(30);
l_flag VARCHAR2(30);
l_err_msg VARCHAR2(3000);
l_webservice_url VARCHAR2(3000);
jsonobj json;
--l_webservice_url VARCHAR2(3000);
l_sec_content VARCHAR2(20000);
l_param_content VARCHAR2(20000);
l_warehouseid VARCHAR2(2000);
l_returncode VARCHAR2(2000);
l_returndesc VARCHAR2(2000);
l_returnflag NUMBER;
l_resultinfo VARCHAR2(2000);
l_raw RAW(32767);
l_put_flag VARCHAR2(1) := 'N';
--
l_req_txt_tbl dbms_sql.varchar2_table;
l_lengthb NUMBER;
l_count NUMBER := 0;
l_start_date VARCHAR2(100);
l_end_date VARCHAR2(100);
l_resp CLOB;
l_raw_content RAW(1024);
ajsonobj json;
srcclob CLOB;
-- desclob CLOB;
list_value json_list;
code_value VARCHAR2(1000);
-- l_interface cux_pick_iface%ROWTYPE;
l_version_number VARCHAR2(500);
-- l_his cux_cst_put_history%ROWTYPE;
l_period_name VARCHAR2(240);
l_cnt NUMBER := 0;
l_row_count NUMBER := 0;
l_event_attrs_rec cux_pub_push_iface_comm_pub.event_attrs_rec_type;
l_event_key VARCHAR2(2000);
l_event_name VARCHAR2(120);
-- l_request_xmltype sys.xmltype;
-- l_response_xmltype sys.xmltype;
CURSOR cur_all IS
SELECT ood.organization_name,
ood.ORGANIZATION_CODE,
substr(pp.period_name,4,4) years,
substr(pp.period_name,1,2) months,
msi.segment1 item_code,
round(pic.item_cost, 5) item_cost,
pic.total_layer_quantity qty,
round(pic.item_cost, 5) * pic.total_layer_quantity amount
FROM cst_pac_item_costs pic,
cst_pac_periods pp,
mtl_system_items_b msi,
org_organization_definitions ood,
cst_cost_group_assignments ccga
WHERE pic.cost_group_id = ccga.cost_group_id
AND pp.pac_period_id = pic.pac_period_id
AND msi.inventory_item_id = pic.inventory_item_id
AND ood.organization_id = ccga.organization_id
AND msi.organization_id = ccga.organization_id
and pp.period_name = P_PERIOD
and ccga.organization_id = P_ORGANIZATION_ID
;
BEGIN
-- start activity to create savepoint, check compatibility
-- and initialize message list, include debug message hint to enter api
x_return_status := hss_api.start_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_init_msg_list => fnd_api.g_false);
raise_exception(x_return_status);
-- API body
-- logging parameters
IF g_debug = 'Y' THEN
NULL;
END IF;
SELECT MAX(def.addition_info)
INTO l_webservice_url
FROM cux_00_imp_iface_system_def def
WHERE def.system_code = 'BMSITEMCST';
dbms_output.put_line('l_webservice_url:' || l_webservice_url);
raise_exception(x_return_status);
--获取数量--
SELECT count(*) into l_count
FROM cst_pac_item_costs pic,
cst_pac_periods pp,
cst_cost_group_assignments ccga
where pic.cost_group_id = ccga.cost_group_id
AND pp.pac_period_id = pic.pac_period_id
and pp.period_name = P_PERIOD
and ccga.organization_id = P_ORGANIZATION_ID ;
SELECT ceil(nvl(l_count,
0) / 5000)
INTO l_cnt
FROM dual;
dbms_output.put_line('l_count:' || l_count);
dbms_output.put_line('l_cnt:' || l_cnt);
FOR i IN 1 .. l_cnt
LOOP
dbms_output.put_line('i:' || i);
if i=2 then
dbms_output.put_line('wwww');
end if;
l_soap_content := '[';--'text=[';
l_row_count := 0;
FOR rec IN cur_all
LOOP
IF nvl(l_row_count,
0) > 0 THEN
l_soap_content := l_soap_content || ',';
END IF;
l_row_count := l_row_count + 1;
--报文正文--
l_soap_content := l_soap_content || '{';
--采购单--
/* l_soap_content := l_soap_content || '"orgId"' || ':' || '"' || rec.organization_id || '"' || ',';\*utl_url.escape(output_xml(rec.organization_id,
'orgId') || ',');*\
l_soap_content := l_soap_content || '"itemNum"' || ':' || '"' || rec.item_number || '"' || ',';
l_soap_content := l_soap_content || '"locationCode"' || ':' || '"' || rec.subinventory || '"' ; */
--
l_soap_content := l_soap_content || output_xml(rec.organization_name,
'corpName') || ',';
--
l_soap_content := l_soap_content || output_xml(rec.organization_code,
'corpCode') || ',';
l_soap_content := l_soap_content || output_xml(rec.years,
'year') || ',';
--
l_soap_content := l_soap_content || output_xml(rec.months,
'moth') || ',';
l_soap_content := l_soap_content || output_xml(rec.item_code,
'itemId') || ',';
l_soap_content := l_soap_content || output_xml(rec.item_cost,
'price') || ',';
--
l_soap_content := l_soap_content || output_xml(rec.qty,
'num') || ',';
--采购单行号--
l_soap_content := l_soap_content || output_xml(rec.amount,
'totalPrice') ;
/*l_soap_content := l_soap_content || utl_url.escape(output_xml2(rec.item_cost,
'price'));*/
l_soap_content := l_soap_content || '}';
END LOOP;
l_soap_content := l_soap_content || output_xml(']');
l_soap_request := l_soap_content;
-- l_soap_request_sign := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => l_soap_content || '+erp001'));
-- l_soap_request_sign := ',sign=' || l_soap_request || '+erp001' ;
x_request_xmltype := xmltype.createxml('<tag>{"body":' || l_soap_request || ',' || output_xml('aa1eeca152385d91072ca3c7d38709c5',
'sign') || '}</tag>');
--dbms_output.put_line('l_soap_content:' || l_soap_content);
dbms_output.put_line('l_soap_content:' || dbms_lob.substr(l_soap_content,
1000));
l_req_txt_tbl.delete;
l_lengthb := 0;
cux_pub_common_utl.get_clob2varchartbl(p_clob => l_soap_request,
x_varchartbl => l_req_txt_tbl,
x_lengthb => l_lengthb);
l_http_req := utl_http.begin_request(l_webservice_url,
'POST',
sys.utl_http.http_version_1_1);
utl_http.set_body_charset('UTF-8');
utl_http.set_header(l_http_req,
'Content-Type',
-- 'application/x-www-form-urlencoded');
'application/json');
utl_http.set_header(r => l_http_req,
NAME => 'Content-Length',
VALUE => l_lengthb);
--将报文循环写入--
IF l_req_txt_tbl.count > 0 THEN
-- dbms_output.put_line('count:' || l_req_txt_tbl.count);
FOR i IN l_req_txt_tbl.first .. l_req_txt_tbl.last
LOOP
utl_http.write_raw(l_http_req,
utl_raw.cast_to_raw(l_req_txt_tbl(i)));
-- dbms_output.put_line(l_req_txt_tbl(i));
END LOOP;
END IF;
l_http_resp := utl_http.get_response(l_http_req);
dbms_output.put_line('status_code:' || l_http_resp.status_code);
l_return_status := 'S';
IF l_http_resp.status_code = 200 THEN
l_resp:=null;
LOOP
l_raw_content := null;
BEGIN
utl_http.read_raw(l_http_resp,
l_raw_content,
1024);
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
l_resp := l_resp || utl_raw.cast_to_varchar2(l_raw_content);
END LOOP;
dbms_output.put_line('l_resp:' || dbms_lob.substr(l_resp,
1000));
l_resp_xml := xmltype.createxml('<tag>' || l_resp || '</tag>');
x_response_xmltype := l_resp_xml;
jsonobj := json(l_resp);
l_returncode := json_ext.get_string(jsonobj,
'code');
l_returndesc := json_ext.get_string(jsonobj,
'msg');
/* l_data := json_ext.get_json(jsonobj,
'data');*/
/*
l_return_data := json_ext.get_string(l_data,
'errorMsg');*/
utl_http.end_response(l_http_resp);
-- dbms_lob.freetemporary(l_resp);
ELSE
l_resp_xml := xmltype.createxml('<tag>访问失败：status_code:' || l_http_resp.status_code || '</tag>');
x_response_xmltype := l_resp_xml;
utl_http.end_response(l_http_resp);
hss_api.set_message(p_app_name => 'CUX',
p_msg_name => 'CUX_WIP_CALL_PATS_WS_FAIL',
p_token1 => 'ERROR',
p_token1_value => l_returncode || '-' || l_returndesc);
l_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
END IF;
IF (nvl(l_returncode,
'0') <> '1' or l_returndesc is not null) THEN
hss_api.set_message(p_app_name => 'CUX',
p_msg_name => 'CUX_WIP_CALL_PATS_WS_FAIL',
p_token1 => 'ERROR',
p_token1_value => l_returncode || '-' || l_returndesc);
l_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
END IF;
--raise_exception(x_return_status);
--log(l_request_xmltype);
--log('l_response_xmltype:' || l_response_xmltype);
raise_exception(x_return_status);
END LOOP;
/* --10.调用出错，报错抛出异常退出
IF nvl(l_returncode,
'error') <> 'success' THEN
hss_api.set_message(p_app_name => 'CUX',
p_msg_name => 'CUX_WIP_CALL_PATS_WS_FAIL',
p_token1 => 'ERROR',
p_token1_value => l_returncode || '-' || l_returndesc);
x_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
END IF;*/
--11.调用成功则更新PO订单的attribute15为Y
--逻辑自行补充，注意同时更新last字段
<<end_call>>
-- end activity, include debug message hint to exit api
x_return_status := hss_api.end_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_commit => fnd_api.g_false,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
EXCEPTION
WHEN fnd_api.g_exc_error THEN
utl_http.end_response(l_http_resp);
x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name => hss_api.g_exc_name_error,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WHEN fnd_api.g_exc_unexpected_error THEN
utl_http.end_response(l_http_resp);
x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name => hss_api.g_exc_name_unexp,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
WHEN OTHERS THEN
-- dbms_output.put_line(SQLCODE || SQLERRM);
dbms_output.put_line(sqlerrm);
utl_http.end_response(l_http_resp);
x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name => hss_api.g_exc_name_others,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END call_web_service;
扫一扫
314
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
