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

 

 

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.