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

2021-07-16 05:46:24

 

 
  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.  

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

上一篇：JS010. 三元运算符扩展运用（多层判断语句 / 多条表达式）


下一篇：c# CLI托管工程开启调试c++库工程代码