ORACLE EBS WebService推送报文例子 XML格式

2022-07-17 18:39:12

 

 
  1. PROCEDURE call_web_service(x_return_status OUT NOCOPY VARCHAR2,
  2.   x_msg_count OUT NOCOPY NUMBER,
  3.   x_msg_data OUT NOCOPY VARCHAR2,
  4.   p_enent_rec IN cux_pub_push_iface_dtls%ROWTYPE,
  5.   x_request_xmltype OUT NOCOPY sys.xmltype,
  6.   x_response_xmltype OUT NOCOPY sys.xmltype) IS
  7.   l_api_name CONSTANT VARCHAR2(30) := 'call_web_service';
  8.   l_savepoint_name CONSTANT VARCHAR2(30) := 'SP01_call_ws';
  9.    
  10.   l_soap_content CLOB := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://service.common.qiku.kmss.landray.com/">
  11.   ';
  12.    
  13.   -- l_soap_content VARCHAR2(32767);
  14.   l_soap_request CLOB;
  15.    
  16.   l_http_req utl_http.req;
  17.   l_http_resp utl_http.resp;
  18.   l_soap_respond VARCHAR2(32767);
  19.   l_resp_xml xmltype;
  20.    
  21.   l_doc dbms_xmldom.domdocument;
  22.   l_docelem dbms_xmldom.domelement;
  23.   l_nodelist dbms_xmldom.domnodelist;
  24.    
  25.   l_length NUMBER;
  26.   l_n dbms_xmldom.domnode;
  27.   l_case_num VARCHAR2(30);
  28.   l_flag VARCHAR2(30);
  29.   l_err_msg VARCHAR2(3000);
  30.   l_webservice_url VARCHAR2(3000);
  31.   CURSOR cs_info IS
  32.   SELECT pla.po_line_id, --行id
  33.   pha.org_id, --ou_id
  34.   hou.name, --ou名称
  35.   pv.vendor_name, --供应商名称--
  36.   pv.segment1 vendor_code, --供应商简码--
  37.   pha.agent_id, --采购员id
  38.   (SELECT ppf.employee_number
  39.   FROM per_people_f ppf
  40.   WHERE ppf.person_id = pha.agent_id
  41.   AND rownum = 1) agent_name, --采购员名称
  42.   pha.terms_id,
  43.   atv.name term_name, --付款条件--
  44.   pha.segment1 po_number, --po号--
  45.   pha.revision_num, --版本号--
  46.   pla.line_num, --行号--
  47.   pla.item_id, --物料id--
  48.   msib.segment1 item_code, --物料编码--
  49.   msib.description item_desc, --物料名称--
  50.   pla.quantity, --数量--
  51.   pla.unit_price no_tax_unit_price, --未含税单价--
  52.   NULL tax_unit_price, --含税单价--
  53.   nvl((SELECT MAX(a.tax_rate) / 100
  54.   FROM zx_lines a,
  55.   po_line_locations_all pll
  56.   WHERE a.application_id = 201
  57.   AND a.entity_code = 'PURCHASE_ORDER'
  58.   AND a.event_class_code = 'PO_PA'
  59.   AND a.trx_id = pll.po_header_id
  60.   AND a.trx_line_id = pll.line_location_id
  61.   AND pll.po_line_id = pla.po_line_id
  62.   AND a.tax_rate IS NOT NULL
  63.   /* AND nvl(a.cancel_flag,
  64.   'N') = 'N' */
  65.   AND (nvl(pll.cancel_flag,
  66.   'N') = 'Y' OR nvl(a.cancel_flag,
  67.   'N') = 'N')
  68.   ),
  69.   0) tax_rate, --税率--
  70.   pla.quantity * pla.unit_price no_tax_total_amount, --未含税总额--
  71.   NULL tax_total_amount, --含税总金额--
  72.   pha.currency_code, --币种--
  73.   nvl(pha.rate,
  74.   1) rate, --汇率--
  75.   to_char(pha.rate_date,
  76.   'yyyy-mm-dd') rate_date, --汇率日期--
  77.   0 is_invoice, --是否已开票--
  78.   (SELECT nvl(ppf.FULL_NAME,fu.user_name)
  79.   FROM per_people_f ppf,
  80.   fnd_user fu
  81.   WHERE fu.employee_id = ppf.person_id(+)
  82.   AND fu.user_id = pha.created_by
  83.   AND rownum = 1) creator, --创建人--
  84.   to_char(SYSDATE,
  85.   'yyyy-mm-dd') create_date, --创建时间--
  86.   decode(nvl(pla.cancel_flag,
  87.   'N'),
  88.   'Y',
  89.   '0',
  90.   decode(nvl(pha.cancel_flag,
  91.   'N'),
  92.   'Y',
  93.   '0',
  94.   decode(nvl(pha.authorization_status,
  95.   'INCOMPLETE'),
  96.   'APPROVED',
  97.   '1',
  98.   '0'))) syn_status, --状态--
  99.   pha.comments remark --备注--
  100.   FROM po_headers_all pha,
  101.   po_lines_all pla,
  102.   hr_operating_units hou,
  103.   po_vendors pv,
  104.   ap_terms_vl atv,
  105.   mtl_system_items_b msib,
  106.   financials_system_params_all fspa
  107.   WHERE pha.po_header_id = pla.po_header_id
  108.   AND pha.org_id = hou.organization_id
  109.   AND pha.vendor_id = pv.vendor_id
  110.   AND atv.term_id = pha.terms_id
  111.   AND fspa.org_id = pha.org_id
  112.   AND pla.item_id = msib.inventory_item_id
  113.   AND fspa.inventory_organization_id = msib.organization_id
  114.   AND pha.po_header_id = get_number_from_varchar2(p_enent_rec.key_value1);
  115.    
  116.   --l_webservice_url VARCHAR2(3000);
  117.   l_sec_content VARCHAR2(20000);
  118.   l_param_content VARCHAR2(20000);
  119.   l_warehouseid VARCHAR2(2000);
  120.    
  121.   l_returncode VARCHAR2(2000);
  122.   l_returndesc VARCHAR2(2000);
  123.   l_returnflag NUMBER;
  124.   l_resultinfo VARCHAR2(2000);
  125.    
  126.   l_put_flag VARCHAR2(1) := 'N';
  127.    
  128.   --
  129.   l_req_txt_tbl dbms_sql.varchar2_table;
  130.   l_lengthb NUMBER;
  131.   l_round_flag varchar2(10);
  132.    
  133.   BEGIN
  134.   -- start activity to create savepoint, check compatibility
  135.   -- and initialize message list, include debug message hint to enter api
  136.   x_return_status := hss_api.start_activity(p_pkg_name => g_pkg_name,
  137.   p_api_name => l_api_name,
  138.   p_savepoint_name => l_savepoint_name,
  139.   p_init_msg_list => fnd_api.g_false);
  140.   raise_exception(x_return_status);
  141.   -- API body
  142.    
  143.   -- logging parameters
  144.   IF g_debug = 'Y' THEN
  145.   NULL;
  146.   END IF;
  147.    
  148.   SELECT MAX(def.addition_info)
  149.   INTO l_webservice_url
  150.   FROM cux_00_imp_iface_system_def def
  151.   WHERE def.system_code = p_enent_rec.system_code;
  152.    
  153.   --0.判断库存组织是否满足同步条件，不满足则退出执行
  154.   --自行补充逻辑
  155.    
  156.   --1.根据iface_system_code获取web服务地址
  157.   --l_webservice_url:=;
  158.    
  159.   --2.获取WMS参数
  160.    
  161.   get_imis_sec_info(p_iface_system_code => p_enent_rec.system_code,
  162.   x_sec_content => l_sec_content);
  163.    
  164.   /* cux_pub_iface_comm_api_utl.get_wms_sec_info(p_iface_system_code => p_enent_rec.system_code,
  165.   x_sec_content => l_sec_content,
  166.   x_param_content => l_param_content,
  167.   x_warehouseid => l_warehouseid);*/
  168.    
  169.   --3.封装WMS安全性
  170.   l_soap_content := l_soap_content || l_sec_content;
  171.    
  172.   l_soap_content := l_soap_content || ' <soapenv:Body>
  173.   <ser:getOrderInfo> ';
  174.    
  175.   select max(get_round_flag(pha.org_id)) into l_round_flag from po_headers_all pha
  176.   where pha.po_header_id = get_number_from_varchar2(p_enent_rec.key_value1);
  177.    
  178.   --4.按照业务需求获取单据信息并安装WS的要求封装报文
  179.   -- l_soap_content := l_soap_content || output_xml('<poInfo>');
  180.   FOR rec_doc_info IN cs_info
  181.   LOOP
  182.   l_soap_content := l_soap_content || '
  183.   <arg0>';
  184.   --业务实体--
  185.   l_soap_content := l_soap_content || output_xml(rec_doc_info.name,
  186.   'businessEntity');
  187.   --创建时间--
  188.   l_soap_content := l_soap_content || output_xml(rec_doc_info.create_date,
  189.   'createTime');
  190.   --币种--
  191.   l_soap_content := l_soap_content || output_xml(rec_doc_info.currency_code,
  192.   'currency');
  193.   --创建人--
  194.   l_soap_content := l_soap_content || output_xml( rec_doc_info.creator ,
  195.   'docCreator');
  196.   --汇率--
  197.   l_soap_content := l_soap_content || output_xml(rec_doc_info.rate,
  198.   'exchangeRate');
  199.   --汇率日期--
  200.   l_soap_content := l_soap_content || output_xml(rec_doc_info.rate_date,
  201.   'exchangeRateTime');
  202.   --行id--
  203.   l_soap_content := l_soap_content || output_xml(rec_doc_info.po_line_id,
  204.   'fdId');
  205.   --是否开过票--
  206.   l_soap_content := l_soap_content || output_xml(rec_doc_info.is_invoice,
  207.   'isGeneratePay');
  208.   --物料编码--
  209.   l_soap_content := l_soap_content || output_xml(rec_doc_info.item_code,
  210.   'materialCode');
  211.   --物料名称--
  212.   l_soap_content := l_soap_content || output_xml(rec_doc_info.item_desc,
  213.   'materialName');
  214.   --未含税单价--
  215.   l_soap_content := l_soap_content || output_xml(rec_doc_info.no_tax_unit_price,
  216.   'noTaxPrice');
  217.   --数量--
  218.   l_soap_content := l_soap_content || output_xml(rec_doc_info.quantity,
  219.   'orderCount');
  220.   --订单行号--
  221.   l_soap_content := l_soap_content || output_xml(rec_doc_info.line_num,
  222.   'orderLine');
  223.   --订单行id--
  224.   l_soap_content := l_soap_content || output_xml(rec_doc_info.po_line_id,
  225.   'orderLineid');
  226.   --订单号--
  227.   l_soap_content := l_soap_content || output_xml(rec_doc_info.po_number,
  228.   'orderNumber');
  229.   --版本号--
  230.   l_soap_content := l_soap_content || output_xml(rec_doc_info.revision_num,
  231.   'orderVersion');
  232.   --付款条件--
  233.   l_soap_content := l_soap_content || output_xml(rec_doc_info.term_name,
  234.   'payConditions');
  235.   --采购员--
  236.   l_soap_content := l_soap_content || output_xml( rec_doc_info.agent_name ,
  237.   'purchaser');
  238.   --备注--
  239.   l_soap_content := l_soap_content || output_xml(rec_doc_info.remark,
  240.   'remark');
  241.   --供应商区域--
  242.   l_soap_content := l_soap_content || output_xml(NULL,
  243.   'supplierArea');
  244.   --供应商代码--
  245.   l_soap_content := l_soap_content || output_xml(rec_doc_info.vendor_code,
  246.   'supplierCode');
  247.   --供应商名称--
  248.   l_soap_content := l_soap_content || output_xml(rec_doc_info.vendor_name,
  249.   'supplierName');
  250.   --状态--
  251.   l_soap_content := l_soap_content || output_xml(rec_doc_info.syn_status,
  252.   'synStatus');
  253.   --含税价格--
  254.   IF NVL(l_round_flag,'N')='Y' THEN
  255.   l_soap_content := l_soap_content || output_xml(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
  256.   0)),2),
  257.   'taxPrice');
  258.   ELSE
  259.   l_soap_content := l_soap_content || output_xml(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
  260.   0)),6),
  261.    
  262.   'taxPrice');
  263.   END IF;
  264.   --税率--
  265.   l_soap_content := l_soap_content || output_xml(rec_doc_info.tax_rate,
  266.   'taxRate');
  267.   --含税金额--
  268.   IF NVL(l_round_flag,'N')='Y' THEN
  269.   l_soap_content := l_soap_content || output_xml(round(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
  270.   0)),2) * rec_doc_info.quantity,2),
  271.   'taxTotalPrice');
  272.   ELSE
  273.   l_soap_content := l_soap_content || output_xml(round(round(rec_doc_info.no_tax_unit_price * (1 + nvl(rec_doc_info.tax_rate,
  274.   0)),6) * rec_doc_info.quantity,2),
  275.   'taxTotalPrice');
  276.   END IF;
  277.   l_soap_content := l_soap_content || '
  278.   </arg0>';
  279.   END LOOP;
  280.   /* l_soap_content := l_soap_content || output_xml('</poInfo>');
  281.    
  282.   --5.封装WMS参数值
  283.   l_soap_content := l_soap_content || '
  284.   ' || l_param_content;*/
  285.    
  286.   --6.封装报文尾
  287.   l_soap_content := l_soap_content || output_xml(' </ser:getOrderInfo>
  288.   </soapenv:Body>
  289.   </soapenv:Envelope>');
  290.   -- DBMS_OUTPUT.put_line(l_soap_content);
  291.    
  292.   --7.发起服务请求
  293.   l_soap_request := l_soap_content;
  294.   x_request_xmltype := xmltype.createxml(l_soap_request);
  295.    
  296.   /* IF l_put_flag = 'N' THEN
  297.   GOTO end_call;
  298.   END IF;*/
  299.   --CLOB转varchar2数组
  300.   l_req_txt_tbl.delete;
  301.   l_lengthb := 0;
  302.   cux_pub_common_utl.get_clob2varchartbl(p_clob => l_soap_request,
  303.   x_varchartbl => l_req_txt_tbl,
  304.   x_lengthb => l_lengthb);
  305.   l_http_req := utl_http.begin_request(l_webservice_url,
  306.   'POST',
  307.   sys.utl_http.http_version_1_1);
  308.    
  309.   utl_http.set_header(l_http_req,
  310.   'Content-Type',
  311.   'text/xml;charset=utf-8');
  312.    
  313.   utl_http.set_header(l_http_req,
  314.   'Content-Length',
  315.   l_lengthb);
  316.   utl_http.set_header(l_http_req,
  317.   'SOAPAction',
  318.   '');
  319.   /* utl_http.write_text(l_http_req,
  320.   l_soap_request);*/
  321.    
  322.   --字符数组循环写入报文
  323.   IF l_req_txt_tbl.count > 0 THEN
  324.   FOR i IN l_req_txt_tbl.first .. l_req_txt_tbl.last
  325.   LOOP
  326.    
  327.   utl_http.write_text(l_http_req,
  328.   l_req_txt_tbl(i));
  329.   END LOOP;
  330.   END IF;
  331.   --End 20160509
  332.    
  333.   --8.获取响应结果
  334.    
  335.   l_http_resp := utl_http.get_response(l_http_req);
  336.   utl_http.read_text(l_http_resp,
  337.   l_soap_respond);
  338.   utl_http.end_response(l_http_resp);
  339.   -- utl_http.end_request(l_http_req);
  340.   l_resp_xml := xmltype.createxml(l_soap_respond);
  341.   x_response_xmltype := l_resp_xml;
  342.   /*l_doc := dbms_xmldom.newdomdocument(l_resp_xml);
  343.   l_docelem := dbms_xmldom.getdocumentelement(l_doc);
  344.   --9.解析响应报文
  345.   \* l_nodelist := dbms_xmldom.getelementsbytagname(l_docelem,
  346.   'return');
  347.   l_length := dbms_xmldom.getlength(l_nodelist);*\
  348.   l_nodelist := dbms_xmldom.getelementsbytagname(l_docelem,
  349.   'return');
  350.   l_length := dbms_xmldom.getlength(l_nodelist);
  351.    
  352.   FOR i IN 0 .. (l_length - 1)
  353.   LOOP
  354.    
  355.   l_n := dbms_xmldom.item(l_nodelist,i);
  356.   DBMS_OUTPUT.put_line(l_n.ID);
  357.   dbms_xslprocessor.valueof(l_n,
  358.   'text()',
  359.   l_resultinfo);
  360.   \* dbms_xslprocessor.valueof(l_n,
  361.   'returnCode/text()',
  362.   l_returncode);
  363.   dbms_xslprocessor.valueof(l_n,
  364.   'returnDesc/text()',
  365.   l_returndesc);
  366.   dbms_xslprocessor.valueof(l_n,
  367.   'returnFlag/text()',
  368.   l_returnflag);*\
  369.   END LOOP;
  370.   */
  371.    
  372.   BEGIN
  373.   SELECT xmltype(l_soap_respond).extract('//return/child::text()').getstringval()
  374.   INTO l_resultinfo
  375.   FROM dual;
  376.   EXCEPTION
  377.   WHEN OTHERS THEN
  378.   l_resultinfo := NULL;
  379.   END;
  380.    
  381.   --10.调用出错，报错抛出异常退出
  382.   IF nvl(l_resultinfo,
  383.   '0') <> 'success' THEN
  384.   hss_api.set_message(p_app_name => 'CUX',
  385.   p_msg_name => 'CUX_WIP_CALL_PATS_WS_FAIL',
  386.   p_token1 => 'ERROR',
  387.   p_token1_value => l_returncode || '-' || l_returndesc || '-' || l_resultinfo);
  388.   x_return_status := fnd_api.g_ret_sts_error;
  389.   raise_exception(x_return_status);
  390.   ELSE
  391.   update_po_hearder(get_number_from_varchar2(p_enent_rec.key_value1));
  392.   END IF;
  393.    
  394.   --11.调用成功则更新PO订单的attribute15为Y
  395.   --逻辑自行补充，注意同时更新last字段
  396.    
  397.   -- <<end_call>>
  398.    
  399.   -- API end body
  400.   -- end activity, include debug message hint to exit api
  401.   x_return_status := hss_api.end_activity(p_pkg_name => g_pkg_name,
  402.   p_api_name => l_api_name,
  403.   p_commit => fnd_api.g_false,
  404.   x_msg_count => x_msg_count,
  405.   x_msg_data => x_msg_data);
  406.   EXCEPTION
  407.   WHEN fnd_api.g_exc_error THEN
  408.   BEGIN
  409.   utl_http.end_response(l_http_resp);
  410.   EXCEPTION
  411.   WHEN OTHERS THEN
  412.   NULL;
  413.   END;
  414.   x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
  415.   p_api_name => l_api_name,
  416.   p_savepoint_name => l_savepoint_name,
  417.   p_exc_name => hss_api.g_exc_name_error,
  418.   x_msg_count => x_msg_count,
  419.   x_msg_data => x_msg_data);
  420.   WHEN fnd_api.g_exc_unexpected_error THEN
  421.   BEGIN
  422.   utl_http.end_response(l_http_resp);
  423.   EXCEPTION
  424.   WHEN OTHERS THEN
  425.   NULL;
  426.   END;
  427.   x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
  428.   p_api_name => l_api_name,
  429.   p_savepoint_name => l_savepoint_name,
  430.   p_exc_name => hss_api.g_exc_name_unexp,
  431.   x_msg_count => x_msg_count,
  432.   x_msg_data => x_msg_data);
  433.   WHEN OTHERS THEN
  434.   BEGIN
  435.   utl_http.end_response(l_http_resp);
  436.   EXCEPTION
  437.   WHEN OTHERS THEN
  438.   NULL;
  439.   END;
  440.   x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name,
  441.   p_api_name => l_api_name,
  442.   p_savepoint_name => l_savepoint_name,
  443.   p_exc_name => hss_api.g_exc_name_others,
  444.   x_msg_count => x_msg_count,
  445.   x_msg_data => x_msg_data);
  446.  
上一篇：ArcGIS 字段计算器python


下一篇：php数据连接