PROCEDURE call_web_service(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_enent_rec IN cux_pub_push_iface_dtls%ROWTYPE,
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 := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://service.common.qiku.kmss.landray.com/">
';
-- l_soap_content VARCHAR2(32767);
l_soap_request CLOB;
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_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);
CURSOR cs_info IS
SELECT pla.po_line_id, --行id
pha.org_id, --ou_id
hou.name, --ou名称
pv.vendor_name, --供应商名称--
pv.segment1 vendor_code, --供应商简码--
pha.agent_id, --采购员id
(SELECT ppf.employee_number
FROM per_people_f ppf
WHERE ppf.person_id = pha.agent_id
AND rownum = 1) agent_name, --采购员名称
pha.terms_id,
atv.name term_name, --付款条件--
pha.segment1 po_number, --po号--
pha.revision_num, --版本号--
pla.line_num, --行号--
pla.item_id, --物料id--
msib.segment1 item_code, --物料编码--
msib.description item_desc, --物料名称--
pla.quantity, --数量--
pla.unit_price no_tax_unit_price, --未含税单价--
NULL tax_unit_price, --含税单价--
nvl((SELECT MAX(a.tax_rate) / 100
FROM zx_lines a,
po_line_locations_all pll
WHERE a.application_id = 201
AND a.entity_code = 'PURCHASE_ORDER'
AND a.event_class_code = 'PO_PA'
AND a.trx_id = pll.po_header_id
AND a.trx_line_id = pll.line_location_id
AND pll.po_line_id = pla.po_line_id
AND a.tax_rate IS NOT NULL
/* AND nvl(a.cancel_flag,
'N') = 'N' */
AND (nvl(pll.cancel_flag,
'N') = 'Y' OR nvl(a.cancel_flag,
'N') = 'N')
),
0) tax_rate, --税率--
pla.quantity * pla.unit_price no_tax_total_amount, --未含税总额--
NULL tax_total_amount, --含税总金额--
pha.currency_code, --币种--
nvl(pha.rate,
1) rate, --汇率--
to_char(pha.rate_date,
'yyyy-mm-dd') rate_date, --汇率日期--
0 is_invoice, --是否已开票--
(SELECT nvl(ppf.FULL_NAME,fu.user_name)
FROM per_people_f ppf,
fnd_user fu
WHERE fu.employee_id = ppf.person_id(+)
AND fu.user_id = pha.created_by
AND rownum = 1) creator, --创建人--
to_char(SYSDATE,
'yyyy-mm-dd') create_date, --创建时间--
decode(nvl(pla.cancel_flag,
'N'),
'Y',
'0',
decode(nvl(pha.cancel_flag,
'N'),
'Y',
'0',
decode(nvl(pha.authorization_status,
'INCOMPLETE'),
'APPROVED',
'1',
'0'))) syn_status, --状态--
pha.comments remark --备注--
FROM po_headers_all pha,
po_lines_all pla,
hr_operating_units hou,
po_vendors pv,
ap_terms_vl atv,
mtl_system_items_b msib,
financials_system_params_all fspa
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = hou.organization_id
AND pha.vendor_id = pv.vendor_id
AND atv.term_id = pha.terms_id
AND fspa.org_id = pha.org_id
AND pla.item_id = msib.inventory_item_id
AND fspa.inventory_organization_id = msib.organization_id
AND pha.po_header_id = get_number_from_varchar2(p_enent_rec.key_value1);
--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_put_flag VARCHAR2(1) := 'N';
--
l_req_txt_tbl dbms_sql.varchar2_table;
l_lengthb NUMBER;
l_round_flag varchar2(10);
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 = p_enent_rec.system_code;
--0.判断库存组织是否满足同步条件，不满足则退出执行
--自行补充逻辑
--1.根据iface_system_code获取web服务地址
--l_webservice_url:=;
--2.获取WMS参数
get_imis_sec_info(p_iface_system_code => p_enent_rec.system_code,
x_sec_content => l_sec_content);
/* cux_pub_iface_comm_api_utl.get_wms_sec_info(p_iface_system_code => p_enent_rec.system_code,
x_sec_content => l_sec_content,
x_param_content => l_param_content,
x_warehouseid => l_warehouseid);*/
--3.封装WMS安全性
l_soap_content := l_soap_content || l_sec_content;
l_soap_content := l_soap_content || ' <soapenv:Body>
<ser:getOrderInfo> ';
select max(get_round_flag(pha.org_id)) into l_round_flag from po_headers_all pha
where pha.po_header_id = get_number_from_varchar2(p_enent_rec.key_value1);
--4.按照业务需求获取单据信息并安装WS的要求封装报文
-- l_soap_content := l_soap_content || output_xml('<poInfo>');
FOR rec_doc_info IN cs_info
LOOP
l_soap_content := l_soap_content || '
<arg0>';
--业务实体--
l_soap_content := l_soap_content || output_xml(rec_doc_info.name,
'businessEntity');
--创建时间--
l_soap_content := l_soap_content || output_xml(rec_doc_info.create_date,
'createTime');
--币种--
l_soap_content := l_soap_content || output_xml(rec_doc_info.currency_code,
'currency');
--创建人--
l_soap_content := l_soap_content || output_xml( rec_doc_info.creator ,
'docCreator');
--汇率--
l_soap_content := l_soap_content || output_xml(rec_doc_info.rate,
'exchangeRate');
--汇率日期--
l_soap_content := l_soap_content || output_xml(rec_doc_info.rate_date,
'exchangeRateTime');
--行id--
l_soap_content := l_soap_content || output_xml(rec_doc_info.po_line_id,
'fdId');
--是否开过票--
l_soap_content := l_soap_content || output_xml(rec_doc_info.is_invoice,
'isGeneratePay');
--物料编码--
l_soap_content := l_soap_content || output_xml(rec_doc_info.item_code,
'materialCode');
--物料名称--
l_soap_content := l_soap_content || output_xml(rec_doc_info.item_desc,
'materialName');
--未含税单价--
l_soap_content := l_soap_content || output_xml(rec_doc_info.no_tax_unit_price,
'noTaxPrice');
--数量--
l_soap_content := l_soap_content || output_xml(rec_doc_info.quantity,
'orderCount');
--订单行号--
l_soap_content := l_soap_content || output_xml(rec_doc_info.line_num,
'orderLine');
--订单行id--
l_soap_content := l_soap_content || output_xml(rec_doc_info.po_line_id,
'orderLineid');
--订单号--
l_soap_content := l_soap_content || output_xml(rec_doc_info.po_number,
'orderNumber');
--版本号--
l_soap_content := l_soap_content || output_xml(rec_doc_info.revision_num,
'orderVersion');
--付款条件--
l_soap_content := l_soap_content || output_xml(rec_doc_info.term_name,
'payConditions');
--采购员--
l_soap_content := l_soap_content || output_xml( rec_doc_info.agent_name ,
'purchaser');
--备注--
l_soap_content := l_soap_content || output_xml(rec_doc_info.remark,
'remark');
--供应商区域--
l_soap_content := l_soap_content || output_xml(NULL,
'supplierArea');
--供应商代码--
l_soap_content := l_soap_content || output_xml(rec_doc_info.vendor_code,
'supplierCode');
--供应商名称--
l_soap_content := l_soap_content || output_xml(rec_doc_info.vendor_name,
'supplierName');
--状态--
l_soap_content := l_soap_content || output_xml(rec_doc_info.syn_status,
'synStatus');
--含税价格--
IF NVL(l_round_flag,'N')='Y' THEN
l_soap_content := l_soap_content || output_xml(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
0)),2),
'taxPrice');
ELSE
l_soap_content := l_soap_content || output_xml(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
0)),6),
'taxPrice');
END IF;
--税率--
l_soap_content := l_soap_content || output_xml(rec_doc_info.tax_rate,
'taxRate');
--含税金额--
IF NVL(l_round_flag,'N')='Y' THEN
l_soap_content := l_soap_content || output_xml(round(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
0)),2) * rec_doc_info.quantity,2),
'taxTotalPrice');
ELSE
l_soap_content := l_soap_content || output_xml(round(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
0)),6) * rec_doc_info.quantity,2),
'taxTotalPrice');
END IF;
l_soap_content := l_soap_content || '
</arg0>';
END LOOP;
/* l_soap_content := l_soap_content || output_xml('</poInfo>');
--5.封装WMS参数值
l_soap_content := l_soap_content || '
' || l_param_content;*/
--6.封装报文尾
l_soap_content := l_soap_content || output_xml(' </ser:getOrderInfo>
</soapenv:Body>
</soapenv:Envelope>');
-- DBMS_OUTPUT.put_line(l_soap_content);
--7.发起服务请求
l_soap_request := l_soap_content;
x_request_xmltype := xmltype.createxml(l_soap_request);
/* IF l_put_flag = 'N' THEN
GOTO end_call;
END IF;*/
--CLOB转varchar2数组
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_header(l_http_req,
'Content-Type',
'text/xml;charset=utf-8');
utl_http.set_header(l_http_req,
'Content-Length',
l_lengthb);
utl_http.set_header(l_http_req,
'SOAPAction',
'');
/* utl_http.write_text(l_http_req,
l_soap_request);*/
--字符数组循环写入报文
IF l_req_txt_tbl.count > 0 THEN
FOR i IN l_req_txt_tbl.first .. l_req_txt_tbl.last
LOOP
utl_http.write_text(l_http_req,
l_req_txt_tbl(i));
END LOOP;
END IF;
--End 20160509
--8.获取响应结果
l_http_resp := utl_http.get_response(l_http_req);
utl_http.read_text(l_http_resp,
l_soap_respond);
utl_http.end_response(l_http_resp);
-- utl_http.end_request(l_http_req);
l_resp_xml := xmltype.createxml(l_soap_respond);
x_response_xmltype := l_resp_xml;
/*l_doc := dbms_xmldom.newdomdocument(l_resp_xml);
l_docelem := dbms_xmldom.getdocumentelement(l_doc);
--9.解析响应报文
\* l_nodelist := dbms_xmldom.getelementsbytagname(l_docelem,
'return');
l_length := dbms_xmldom.getlength(l_nodelist);*\
l_nodelist := dbms_xmldom.getelementsbytagname(l_docelem,
'return');
l_length := dbms_xmldom.getlength(l_nodelist);
FOR i IN 0 .. (l_length - 1)
LOOP
l_n := dbms_xmldom.item(l_nodelist,i);
DBMS_OUTPUT.put_line(l_n.ID);
dbms_xslprocessor.valueof(l_n,
'text()',
l_resultinfo);
\* dbms_xslprocessor.valueof(l_n,
'returnCode/text()',
l_returncode);
dbms_xslprocessor.valueof(l_n,
'returnDesc/text()',
l_returndesc);
dbms_xslprocessor.valueof(l_n,
'returnFlag/text()',
l_returnflag);*\
END LOOP;
*/
BEGIN
SELECT xmltype(l_soap_respond).extract('//return/child::text()').getstringval()
INTO l_resultinfo
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_resultinfo := NULL;
END;
--10.调用出错，报错抛出异常退出
IF nvl(l_resultinfo,
'0') <> '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 || '-' || l_resultinfo);
x_return_status := fnd_api.g_ret_sts_error;
raise_exception(x_return_status);
ELSE
update_po_hearder(get_number_from_varchar2(p_enent_rec.key_value1));
END IF;
--11.调用成功则更新PO订单的attribute15为Y
--逻辑自行补充，注意同时更新last字段
-- <<end_call>>
-- API end body
-- 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
BEGIN
utl_http.end_response(l_http_resp);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
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
BEGIN
utl_http.end_response(l_http_resp);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
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
BEGIN
utl_http.end_response(l_http_resp);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
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;
扫一扫
1101
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
