1 2 Previous Next 19 Replies Latest reply on Oct 28, 2019 5:14 AM by vikas yadav 250190-Oracle

    Unable to handle Brazilian characters in a webservice call through plsql

    vikas yadav 250190-Oracle

      Hi,

       

      I am facing issue in passing a JSON which has Brazilian characters in it, even though, I am passing UTF-8 character set to read it.

      This JSON is passed through PLSQL procedure.

       

      utl_http.set_body_charset(v_http_request,'UTF-8');

       

      JSON under consideration is :

       

       

      "remit-to-addresses": [

       

        {

         "id": 23438,

         "created-at": "2018-09-15T06:58:02-04:00",

         "updated-at": "2019-10-15T03:17:00-04:00",

         "remit-to-code": "WIRE XXXX",

         "name": "65279",

         "street1": "Alameda Rio Negro, 585",

         "street2": " Edifício Demini, 1º, 2º, 11º e 12º andar",

         "city": "Alphaville",

         "state": "Barueri/SP",

         "postal-code": "06454-000",

         "active": true,

         "vat-number": null,

         "local-tax-number": null,

         "external-src-ref": null,

         "external-src-name": null,

         "gst-id": "",

         "jpmc-check-priority": "",

         "country": {

         "id": 31,

         "code": "BR",

         "name": "Brazil"

        }

        },

       

      If i remove the degree from degree from the field Street2, JSON is consumed perfectly.

      Any suggestions on what character set should be used to pass this JSON?

       

      Thanks,

      Vikas Yadav

        • 1. Re: Unable to handle Brazilian characters in a webservice call through plsql
          cormaco

          UTF-8 is the correct characterset for transmitting JSON.

          The question is: Are actually sending UTF-8? Is the file you have created in UTF-8?

          What is the error message from the receiving server?

          What is the version and nls_characterset of your database?

          • 2. Re: Unable to handle Brazilian characters in a webservice call through plsql
            vikas yadav 250190-Oracle

            Hi,

             

            DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

            NLS_CHARACTERSET: UTF8

             

            Yes, we are passing the file as utf, we are setting utl_http.set_body_charset(v_http_request,'UTF-8');

            And then we are passing the JSON, v_http_response := UTL_HTTP.get_response(v_http_request);

             

            We a have loop to read the content of json.

             

            WHILE v_offset < v_length

                    LOOP

                          DBMS_LOB.READ (p_content, v_data_bytes,v_offset, v_chunk_string);

                          UTL_HTTP.write_text(v_http_request, v_chunk_string );

                          v_offset := v_offset + v_data_bytes;

                    END LOOP;

             

            p_content has the json data in it.

             

            Return status from server is  - 400, which means json could not be read properly. Our custom error message tells us its issue with the field street2 as mentioned in my question.

            Hope this helps.

             

            Thanks,

            Vikas Yadav

            • 3. Re: Unable to handle Brazilian characters in a webservice call through plsql
              cormaco

              Try adding this to your header, if you haven't already done so:

              utl_http.set_header(req, 'content-type', 'application/json;charset=UTF-8');
              
              • 4. Re: Unable to handle Brazilian characters in a webservice call through plsql
                vikas yadav 250190-Oracle

                I already have UTL_HTTP.set_header(v_http_request, 'Content-Length', NVL(length(p_content),0));

                How do you suggest to modify it?

                • 5. Re: Unable to handle Brazilian characters in a webservice call through plsql
                  vikas yadav 250190-Oracle

                  I have modified the code ,

                   

                       UTL_HTTP.set_header(v_http_request, 'Content-Length', NVL(length(p_content),0));

                       UTL_HTTP.set_header(v_http_request, 'ACCEPT', 'application/json');

                       utl_http.set_header(v_http_request, 'Content-Type', 'application/json;charset=UTF-8');  

                       utl_http.set_body_charset(v_http_request,'UTF-8');

                   

                  Still got server -400 error.

                   

                  Thanks,

                  Vikas Yadav

                  • 6. Re: Unable to handle Brazilian characters in a webservice call through plsql
                    cormaco

                    vikas yadav 250190-Oracle schrieb:

                     

                    I already have UTL_HTTP.set_header(v_http_request, 'Content-Length', NVL(length(p_content),0));

                    How do you suggest to modify it?

                    No, you can call set_header multiple times. Each call creates another header line in your request. You can have many header lines in a request.

                    Edit: Nevermind, I didn't see your previous post then I wrote this.

                    • 7. Re: Unable to handle Brazilian characters in a webservice call through plsql
                      vikas yadav 250190-Oracle

                      json used,

                       

                      {

                      "active": true,

                      "city": "Alphaville",

                      "country": [

                      {

                      "code": "BR"

                      }

                      ],

                      "name": 78726,

                      "postal-code": "06454-000",

                      "remit-to-code": "AMEX V2 BRL 0923",

                      "state": "Barueri/SP",

                      "street1": "Alameda Rio Negro, 585",

                      "street2": "Edifício Demini, 1º, 2º, 11º e 12º andar"

                      }

                      • 8. Re: Unable to handle Brazilian characters in a webservice call through plsql
                        cormaco

                        Can you get a logfile from the server what has been exactly received?

                        Especially the problematic characters, what bytesequence has been transmitted?

                        Other than that can you post your code?

                        • 9. Re: Unable to handle Brazilian characters in a webservice call through plsql
                          vikas yadav 250190-Oracle

                          I have attached the code to put json content and its calling procedure along with log file generated.

                          Unfortunately,we dont have a mechanism to keep track of the content passed byte by byte.

                          I have already shared the json content creating issue. On removing the special characters from the json, the content is passed successfully to next system.

                           

                          Thanks,

                          Vikas Yadav

                          • 10. Re: Unable to handle Brazilian characters in a webservice call through plsql
                            cormaco

                            I have attached the code to put json content and its calling procedure along with log file generated.

                            Don't use zip files for posting, most people here consider them a security risc.

                            Post your files a text, you can use the extended editor for better readability

                            • 11. Re: Unable to handle Brazilian characters in a webservice call through plsql
                              vikas yadav 250190-Oracle

                              log file details:

                              ------------------------------------------------

                               

                              +---------------------------------------------------------------------------+

                              Business Online: Version : 12.1

                               

                               

                              Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

                               

                               

                              BLK_COUPA_LOAD_SUP_SITES: BLK Coupa Load Supplier Sites

                              +---------------------------------------------------------------------------+

                               

                               

                              Current system time is 16-OCT-2019 05:55:02

                               

                               

                              +---------------------------------------------------------------------------+

                               

                               

                              **Starts**16-OCT-2019 05:55:02

                              **Ends**16-OCT-2019 05:55:06

                              +---------------------------------------------------------------------------+

                              Start of log messages from FND_FILE

                              +---------------------------------------------------------------------------+

                              * ***************************************************************************

                              * ********************Beginning Coupa remit-to-address api.******************

                              *

                              * Last run date of this program is: 16-OCT-2019 05:53:00

                              * Current run date of this program is: 16-OCT-2019 05:55:02

                              *

                              *

                              * In get_url routine...

                              * Started processing of supplier:    "BANCO BRADESCO CARTOES"

                              * Supplier number:                   "69531"

                              *

                              *

                              * Checking if supplier exists on Coupa or not.

                              * Calling procedure GET_CONTENT to check supplier on Coupa

                              *

                              * In get_application_code...

                              * In Get_Key_Value routine...

                              * In get_proxy...

                              * GET event start...

                              * Set Oracle Wallet...

                              * In get_wallet_loc routine...

                              * In get_wallet_pwd routine...

                              * In get_wallet_loc routine...

                              * wallet loc: file:/dbro1i/oracle/product/12102/owm/wallets/ordbro1i

                              * Set Proxy...omcs-proxy.oracleoutsourcing.com:80

                              * GET event - Set URL... https://blackrock-test.coupahost.com/api/suppliers?number=69531

                              * GET event - retrieve values

                              * GET Event - status is Success

                              * GET event - Add content to table for end point id...13

                              * ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure: 526040

                              * Processing of GET_CONTENT completed

                              *

                              * Supplier-BANCO BRADESCO CARTOES exists on Coupa, now remit-to-address can be uploaded.

                              * Now checking if address for the supplier- BANCO BRADESCO CARTOES exists on Coupa or not.

                              * In get_url routine...

                              * In get_application_code...

                              * In Get_Key_Value routine...

                              * In get_proxy...

                              * GET event start...

                              * Set Oracle Wallet...

                              * In get_wallet_loc routine...

                              * In get_wallet_pwd routine...

                              * In get_wallet_loc routine...

                              * wallet loc: file:/dbro1i/oracle/product/12102/owm/wallets/ordbro1i

                              * Set Proxy...omcs-proxy.oracleoutsourcing.com:80

                              * GET event - Set URL... https://blackrock-test.coupahost.com/api/suppliers/3452/addresses?name=65279

                              * GET event - retrieve values

                              * GET Event - status is Success

                              * GET event - Add content to table for end point id...13

                              * ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure for remit-to-address: 526041

                              *

                              * Address for supplier-BANCO BRADESCO CARTOES with site-id-65279 already exists on Coupa. Proceeding to update

                              *

                              * Calling procedure INSERT_CONTENT to save the data in custom table for future reference

                              *

                              * Processing of INSERT_CONTENT completed

                              *

                              * l_clob : {"active":true,"city":"Alphaville","country":[{"code":"BR"}],"name":65279,"postal-code":"06454-000","remit-to-code":"WIRE XXXX","state":"Barueri/SP","street1":"Alameda Rio Negro, 585","street2":"Edifício Demini, 1º, 2º, 11º e 12º andar"}

                               

                               

                              * In get_application_code...

                              * In get_application_code...

                              * In Get_Key_Value routine...

                              * In get_proxy...

                              * PUT event ...

                              * Set Oracle Wallet...

                              * In get_wallet_loc routine...

                              * In get_wallet_pwd routine...

                              * Set Proxy...omcs-proxy.oracleoutsourcing.com:80

                              PUT event Set URL...https://blackrock-test.coupahost.com/api/suppliers/3452/addresses/23438

                              PUT event - loop thru CLOB and write to url body...

                              * PUT event error - return status - 400

                              * Into put content end of body exception

                              * Raise bad put

                              * Error while putting data on Coupa, Error is -

                              * Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa

                              *

                              * Processing of PUT_CONTENT completed

                              *

                              *

                              * Program processed one vendor site-65279

                              * ***************************************************************************

                              *

                              *

                              * Now checking if address for the supplier- BANCO BRADESCO CARTOES exists on Coupa or not.

                              * In get_url routine...

                              * In get_application_code...

                              * In Get_Key_Value routine...

                              * In get_proxy...

                              * GET event start...

                              * Set Oracle Wallet...

                              * In get_wallet_loc routine...

                              * In get_wallet_pwd routine...

                              * In get_wallet_loc routine...

                              * wallet loc: file:/dbro1i/oracle/product/12102/owm/wallets/ordbro1i

                              * Set Proxy...omcs-proxy.oracleoutsourcing.com:80

                              * GET event - Set URL... https://blackrock-test.coupahost.com/api/suppliers/3452/addresses?name=78726

                              * GET event - retrieve values

                              * GET Event - status is Success

                              * GET event - Add content to table for end point id...13

                              * ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure for remit-to-address: 526043

                              *

                              * Address for supplier-BANCO BRADESCO CARTOES with site-id-78726 already exists on Coupa. Proceeding to update

                              *

                              * Calling procedure INSERT_CONTENT to save the data in custom table for future reference

                              *

                              * Processing of INSERT_CONTENT completed

                              *

                              * l_clob : {"active":true,"city":"Alphaville","country":[{"code":"BR"}],"name":78726,"postal-code":"06454-000","remit-to-code":"AMEX V2 BRL 0923","state":"Barueri/SP","street1":"Alameda Rio Negro, 585","street2":"Edifício Demini, 1º, 2º, 11º e 12º andar"}

                               

                               

                              * In get_application_code...

                              * In get_application_code...

                              * In Get_Key_Value routine...

                              * In get_proxy...

                              * PUT event ...

                              * Set Oracle Wallet...

                              * In get_wallet_loc routine...

                              * In get_wallet_pwd routine...

                              * Set Proxy...omcs-proxy.oracleoutsourcing.com:80

                              PUT event Set URL...https://blackrock-test.coupahost.com/api/suppliers/3452/addresses/23757

                              PUT event - loop thru CLOB and write to url body...

                              * PUT event error - return status - 400

                              * Into put content end of body exception

                              * Raise bad put

                              * Error while putting data on Coupa, Error is -

                              * Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa

                              *

                              * Processing of PUT_CONTENT completed

                              *

                              *

                              * Program processed one vendor site-78726

                              * ***************************************************************************

                              *

                              *

                              * Total remit-to-address got processed:                 1

                              * Total new remit-to-address created on Coupa:          0

                              * Total existing remit-to-address updated on Coupa:-    2

                              * Total remit-to-address got rejected:                  -1

                              *

                              *

                              * **********************End Coupa remit-to-address api. **********************

                              * ***************************************************************************

                              +---------------------------------------------------------------------------+

                              End of log messages from FND_FILE

                              +---------------------------------------------------------------------------+

                               

                               

                               

                               

                              +---------------------------------------------------------------------------+

                              No completion options were requested.

                               

                               

                              Output file size:

                              0

                               

                               

                              +---------------------------------------------------------------------------+

                              Concurrent request completed successfully

                              Current system time is 16-OCT-2019 05:55:06

                               

                               

                              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                               

                               

                              Put content code:

                               

                               

                              PROCEDURE put_content(p_end_point_id IN NUMBER,p_url IN VARCHAR2, p_content CLOB, p_method IN VARCHAR2 ) AS

                               

                               

                              v_http_request                   UTL_HTTP.req;

                              v_http_response                  UTL_HTTP.resp;

                              v_text                           VARCHAR2(32767);

                              v_clob                           CLOB;

                               

                               

                              v_length                    NUMBER := LENGTH(p_content) ;

                              v_Data_bytes                NUMBER := 32000;

                              v_offset                    NUMBER := 1;

                              v_chunk_string              VARCHAR2(32000);

                              -- v_put_url                   VARCHAR2(5000) := p_url; --Commented as part of Jira#26955

                              v_put_url                   VARCHAR2(32000) := p_url; --Added as part of Jira#26955

                              v_application               VARCHAR2(10)  := get_application_code(p_end_point_id);

                              v_key                       VARCHAR2(100) := get_key_value (get_application_code(p_end_point_id));

                               

                               

                              v_proxy                     VARCHAR2(200); -- Jira#22630

                              bad_put                     EXCEPTION;

                              server_exception            EXCEPTION;

                               

                               

                               

                               

                              BEGIN

                               

                               

                              v_proxy            := get_proxy(v_application);  -- Jira#22630

                               

                               

                              -- Make a HTTP request and get the response.

                                  print_log('PUT event ...');

                                  --Read the wallet location and password and set the wallet .

                                  print_log('Set Oracle Wallet...');

                                  utl_http.set_wallet(get_wallet_loc('ORACLE_EBS'), get_wallet_pwd('ORACLE_EBS'));

                               

                               

                                  -- Jira#22630

                                IF v_proxy IS NOT NULL

                                THEN

                                      print_log('Set Proxy...'||v_proxy);

                                      utl_http.set_proxy(v_proxy);

                                END IF;

                               

                               

                                 --URL encoding needed for Parameters to be passed otherwise results in bad syntax - online tools add this by default.

                               

                               

                                  IF p_method = 'PUT' THEN

                                        v_put_url := SUBSTR(v_put_url,1,INSTR(v_put_url,'?')) || UTL_URL.ESCAPE(SUBSTR(v_put_url,INSTR(v_put_url,'?')+1));

                                  END IF;

                               

                               

                               

                               

                                   fnd_file.put_line(apps.fnd_file.log,'PUT event Set URL...'||p_url);

                                   v_http_request  := utl_http.begin_request(v_put_url,p_method);

                               

                               

                                   IF v_application = 'COUPA' then

                                     UTL_HTTP.set_header(v_http_request, 'X-COUPA-API-KEY',v_key);

                                   END IF;

                               

                               

                               

                               

                                  --Setting length mandatory, this will length of the CLOB, if null add 0

                               

                               

                                   UTL_HTTP.set_header(v_http_request, 'Content-Length', NVL(length(p_content),0));

                               

                                   UTL_HTTP.set_header(v_http_request, 'ACCEPT', 'application/json');

                              utl_http.set_header(v_http_request, 'Content-Type', 'application/json;charset=UTF-8'); 

                                   utl_http.set_body_charset(v_http_request,'UTF-8'); --Jira#23773

                               

                               

                                  --Since CLOB can be more that 32K in length, take 32000 bytes , write to body and loop till end of CLOB.

                                  fnd_file.put_line(apps.fnd_file.log,'PUT event - loop thru CLOB and write to url body...');

                               

                               

                                  WHILE v_offset < v_length

                                      LOOP

                                            DBMS_LOB.READ (p_content, v_data_bytes,v_offset, v_chunk_string);

                                            UTL_HTTP.write_text(v_http_request, v_chunk_string );

                                            v_offset := v_offset + v_data_bytes;

                                      END LOOP;

                               

                               

                                  v_http_response := UTL_HTTP.get_response(v_http_request);

                               

                               

                              --201 is record created

                              --200 is success query or update

                              --4xx client error

                              --5xx server error

                               

                               

                                  IF substr(v_http_response.status_code,1,1) = '2' then

                                  -- in (200,201) then

                                      print_log('PUT event - status Success...');

                                        BEGIN

                                          LOOP

                               

                               

                                              UTL_HTTP.read_text(v_http_response, v_text,LENGTH(v_text));

                                              v_clob := v_clob || v_text ;

                               

                               

                                          END LOOP;

                               

                               

                                        EXCEPTION WHEN UTL_HTTP.end_of_body THEN

                                            UTL_HTTP.end_response(v_http_response);

                                        END;

                               

                               

                                        UTL_HTTP.end_response(v_http_response);

                               

                               

                                   ELSE IF  substr(v_http_response.status_code,1,1) = '5' THEN

                                      print_log('PUT event server error - return status - '|| v_http_response.status_code );

                                      Raise server_exception;

                                   ELSE IF substr(v_http_response.status_code,1,1) = '4'   THEN

                                     print_log('PUT event error - return status - '|| v_http_response.status_code );

                                      BEGIN

                                        LOOP

                               

                               

                                             UTL_HTTP.read_text(v_http_response, v_text,LENGTH(v_text));

                                             print_log(v_text);

                                             v_clob := v_clob || v_text ;

                               

                               

                                        END LOOP;

                                        EXCEPTION WHEN utl_http.end_of_body THEN

                                            utl_http.end_response(v_http_response);

                                            print_log('Into put content end of body exception');

                                      END;

                                        UTL_HTTP.end_response(v_http_response);

                               

                               

                                       If   v_clob = '{"errors":{"request":["This approval is not the currently pending approval."]}}' then

                                            Null; --this can be fyi candidate for exceptions recording

                                            print_log('Into approval condition');

                                       Else

                                          print_log('Raise bad put ');

                                           RAISE bad_put; --do we need this for 400 code or it should be only for 500 (server error)

                                                          -- or mark entire batch as processed so next run fectches new recordsfrom Coupa or other apps.

                                       End if;

                               

                               

                                   END IF; --code = 4

                               

                               

                                   END IF; --code = 5

                               

                               

                                  END IF; --code is 2

                               

                               

                               

                               

                              EXCEPTION WHEN bad_put THEN

                                         print_log( 'Error while putting data on Coupa, Error is - '|| v_clob);

                                         --insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob);

                                         --insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob,p_end_point_id,v_http_response.status_code);

                                         --CSS-22047

                                         /*Below IF condition on v_clob is added under CSS-23276*/

                                       IF v_clob like '%Parent %have a different Lookup than parent%' THEN

                                          g_parent_err_flag :='Y';

                                       ELSE

                                       insert_audit (

                                          'PUT CONTENT',

                                          'Error while putting data on Coupa, Error is - ' || v_clob,

                                          p_end_point_id,

                                          v_http_response.status_code,

                                          g_reference_details);

                                       --CSS-22047 enhancement

                                       END IF;

                                         --CSS-22047 enhancement

                                         UTL_HTTP.end_response(v_http_response);

                                        WHEN server_exception THEN

                                         print_log( 'Error while putting data on Coupa, Error is - '|| v_clob);

                                         --insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob);

                                         --insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob,p_end_point_id,v_http_response.status_code);

                                         --CSS-22047

                                         insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob,p_end_point_id,v_http_response.status_code,g_reference_details);

                                         --CSS-22047 enhancement

                                         UTL_HTTP.end_response(v_http_response);

                                        WHEN OTHERS THEN

                                         print_log( 'Error while putting data on Coupa, Error is - '|| SQLERRM);

                                         print_log('PUT event Exception - '||SQLERRM);

                                         --insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM);

                                          --insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM,p_end_point_id,v_http_response.status_code);

                                       --Added If below for Jira#25131

                                      IF SQLERRM LIKE '%ORA-29024%' --AND g_reference_details IS NULL

                                      THEN

                                        insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM,p_end_point_id,v_http_response.status_code,'Certificate validation failure');

                               

                               

                                      ELSE

                                         --CSS-22047

                                         insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM,p_end_point_id,v_http_response.status_code,g_reference_details);

                                         --CSS-22047 enhancement

                                      END IF;

                                         UTL_HTTP.end_response(v_http_response);

                              END;

                              -------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                               

                              CALLING PROCEDURE CODE:

                               

                              PROCEDURE coupa_supplier_remits_api (errbuf OUT VARCHAR2, retcode OUT NUMBER, p_last_run_date IN VARCHAR2) as

                               

                               

                              l_clob                CLOB;

                              l_cursor              SYS_REFCURSOR;

                              v_id                  NUMBER;

                              v_json_id             NUMBER;

                              v_json_id1            NUMBER;

                              v_url                 VARCHAR2(500);

                              v_method              VARCHAR2(5);

                              v_flag                NUMBER:=0; -- Flag to check if there is any records eligible for processing

                              v_flag_create         NUMBER:=0; -- Flag to count how many remit-to-address got created

                              v_flag_update         NUMBER:=0; -- Flag to count how many remit-to-address got updated

                               

                               

                              v_last_run_date       DATE;

                              l_vendor_temp         ap_suppliers.segment1%TYPE; --Added for CSS-24813

                               

                               

                               

                               

                              CURSOR cur_ready_adrs (p_last_run DATE,p_current_run DATE) IS --Modified, version 5.1

                              WITH bank_data

                                   AS (SELECT ieba.bank_account_num bank_account, iepa.supplier_site_id

                                         FROM ar.hz_parties hp,

                                              iby.iby_ext_bank_accounts ieba,

                                              iby.iby_external_payees_all iepa,

                                              iby.iby_pmt_instr_uses_all ipiua

                                        WHERE 1 = 1

                                              AND hp.party_id = ieba.bank_id

                                              AND ipiua.instrument_id = ieba.ext_bank_account_id

                                              AND ipiua.ext_pmt_party_id = iepa.ext_payee_id

                                              AND ipiua.instrument_type = 'BANKACCOUNT'

                                              AND ipiua.payment_flow = 'DISBURSEMENTS'

                                              AND ipiua.order_of_preference = 1)

                              SELECT apsa.vendor_site_id

                                        name,

                                     /*(CASE

                                         WHEN TRUNC (apsa.inactive_date) < TRUNC (SYSDATE) THEN 'FALSE'               -- KBS 20-Feb changed to FALSE

                                         WHEN TRUNC (apsa.inactive_date) >= TRUNC (SYSDATE) THEN 'TRUE'             -- KBS 20-Feb changed to TRUE

                                         ELSE 'TRUE'

                                      END)

                                        active, */

                                     DECODE(apsa.inactive_date,NULL,'TRUE','FALSE') active,

                                     DECODE (aps1.vendor_type_lookup_code,

                                             'EMPLOYEE', '',

                                             apsa.address_line1)

                                        line1,

                                     DECODE (aps1.vendor_type_lookup_code,

                                             'EMPLOYEE', '',

                                             apsa.address_line2)

                                        line2,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.city) city,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.state)

                                        state,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.zip)

                                        postal_code,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.country)

                                      country_code,

                                     'RemitToAddress' address_type,

                                     aps1.segment1 supplier_number,

                                     aps1.vendor_id,

                                     aps1.vendor_name,

                                        apsa.vendor_site_code

                                     ||' '|| NVL (SUBSTR (bank_temp.bank_account, -4), 'XXXX')

                                        remit_to_code,

                                     apsa.attribute5 JPMC_check_priority,

                                     /*(SELECT req.registration_number

                                        FROM ap_supplier_sites_all apsa1,

                                             Zx_party_tax_profile taxp,

                                             zx_registrations req

                                       WHERE apsa1.vendor_site_id = apsa.vendor_site_id

                                             AND apsa1.party_site_id = taxp.party_id

                                             AND taxp.party_tax_profile_id = req.party_tax_profile_id

                                             AND NVL (TRUNC (req.effective_to), SYSDATE + 1) >=

                                                    TRUNC (SYSDATE)) */ --CSS-21554 Manjit's suggestion

                                     apsa.vat_registration_num GST_ID,

                                     'N' Supplier_update --22833

                                FROM ap.ap_supplier_sites_all apsa

                                    ,bank_data bank_temp

                                    ,ap.ap_suppliers aps1

                              WHERE 1 = 1

                                     AND aps1.attribute8 <= fnd_profile.value('BLK_COUPA_PHASE')--'1','2' --ver5.2

                                     AND apsa.vendor_site_id = bank_temp.supplier_site_id(+)

                                     AND apsa.vendor_id = aps1.vendor_id

                                     -- KBS 19-MAR-2018 added line below to stop records trying to go to coupa with no email address

                                     AND aps1.ATTRIBUTE6||aps1.attribute7 != 'email'

                                     AND TRUNC (NVL(apsa.inactive_date,SYSDATE)) >= TRUNC (SYSDATE)

                                     --AND aps1.last_update_date BETWEEN p_last_run AND SYSDATE;

                                     --AND apsa.last_update_date BETWEEN NVL(v_last_run_date, p_last_run) AND SYSDATE; --we need to check last update date on supplier site not supplier

                                     AND apsa.last_update_date BETWEEN NVL(v_last_run_date, p_last_run) AND p_current_run --Modified, version 5.1

                                     --AND aps1.segment1 = '55760';

                              UNION  --Added Union as part of JIRA#22833

                              SELECT apsa.vendor_site_id

                                        name,

                                     DECODE(apsa.inactive_date,NULL,'TRUE','FALSE') active,

                                     DECODE (aps1.vendor_type_lookup_code,

                                             'EMPLOYEE', '',

                                             apsa.address_line1)

                                        line1,

                                     DECODE (aps1.vendor_type_lookup_code,

                                             'EMPLOYEE', '',

                                             apsa.address_line2)

                                        line2,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.city) city,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.state)

                                        state,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.zip)

                                        postal_code,

                                     DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.country)

                                      country_code,

                                     'RemitToAddress' address_type,

                                     aps1.segment1 supplier_number,

                                     aps1.vendor_id,

                                     aps1.vendor_name,

                                        apsa.vendor_site_code

                                     ||' '|| NVL (SUBSTR (bank_temp.bank_account, -4), 'XXXX')

                                        remit_to_code,

                                     apsa.attribute5 JPMC_check_priority,

                                     apsa.vat_registration_num GST_ID,

                                     'Y' Supplier_update

                                FROM ap.ap_supplier_sites_all apsa

                                    ,bank_data bank_temp

                                    ,ap.ap_suppliers aps1

                              WHERE 1 = 1

                                         AND aps1.attribute8 <= fnd_profile.value('BLK_COUPA_PHASE')

                                     AND apsa.vendor_site_id = bank_temp.supplier_site_id(+)

                                     AND apsa.vendor_id = aps1.vendor_id

                                     AND aps1.ATTRIBUTE6||aps1.attribute7 != 'email'

                                     AND TRUNC (NVL(apsa.inactive_date,SYSDATE)) >= TRUNC (SYSDATE)

                                     AND aps1.last_update_date BETWEEN NVL(v_last_run_date, p_last_run) AND p_current_run

                                     AND apsa.last_update_date < NVL(v_last_run_date, p_last_run)

                                     ORDER BY supplier_number; --Added for CSS-24813;

                                     /*Above ORDER BY should not be changed. This 'order by' is used to reduce the number of calls made to

                                    -- coupa supplier api while updating the supplier site information. For any chance order by clause is changed, then requirement under jira#84813 must be tested. */

                               

                               

                               

                               

                              BEGIN

                               

                               

                              v_last_run_date :=  fnd_date.canonical_to_date (p_last_run_date);

                               

                               

                                  print_log ('***************************************************************************');

                                  print_log ('********************Beginning Coupa remit-to-address api.******************');

                                  print_log(' ');

                                  --print_log ('Last run date of this program is: ' ||get_last_run_date(fnd_global.conc_request_id));

                                  print_log ('Last run date of this program is: ' ||to_char(get_last_run_date(fnd_global.conc_request_id),'DD-MON-YYYY HH24:MI:SS'));

                                  print_log ('Current run date of this program is: ' ||to_char(get_current_run_date(fnd_global.conc_request_id),'DD-MON-YYYY HH24:MI:SS')); --22833

                                  --more friendly log

                                  print_log(' ');

                                  print_log(' ');

                               

                               

                                  --------------------------------------------------------------------------------------

                                  ---  NEW SECTION

                                  --------------------------------------------------------------------------------------

                               

                               

                                  -- First go and collect the data and store it as a JSON into the BLK_API_CONTENT table

                               

                               

                               

                               

                                  FOR cur_rec IN cur_ready_adrs (get_last_run_date(fnd_global.conc_request_id),get_current_run_date(fnd_global.conc_request_id)) LOOP --Modified, version 5.1

                               

                               

                                   IF NVL(l_vendor_temp,'xyz') <> cur_rec.supplier_number THEN --Added for CSS-24813. Check if currecnt supplier is different from last processed supplier

                                       v_json_id := NULL; -- Added for CSS-24813, re-initialization of variable only when supplier number is different than last fetched supplier number

                                    g_reference_details:= 'supplier#'||cur_rec.supplier_number; --CSS-22047 report enhancement

                                    v_flag := v_flag+1; --increment the flag

                                    v_url := get_url('COUPA','COUPA_SUPPLIER','GET');

                               

                               

                                        print_log('Started processing of supplier:    '||'"'|| cur_rec.vendor_name||'"');

                                        print_log('Supplier number:                   '||'"'|| cur_rec.supplier_number||'"');

                               

                               

                                        print_log(' ');

                                        print_log(' ');

                               

                               

                                        print_log('Checking if supplier exists on Coupa or not.');

                                        print_log('Calling procedure GET_CONTENT to check supplier on Coupa');

                               

                               

                                        print_log(' ');

                               

                               

                               

                               

                                        BEGIN

                                          get_content(13, v_url||cur_rec.supplier_number,v_id);

                                        EXCEPTION WHEN OTHERS THEN

                                        print_log('Error while calling get_content from copa_supplier_remit_api proc while checking supplier, Error is - '|| SQLERRM);

                                        -- /*KSS*/ -- below IF-ELSE to decide status of prog and whether to continue or exit

                                          /*IF g_retcode = 2 THEN

                                              retcode:= 2;

                                              EXIT;

                                          ELSE

                                            retcode:=1;

                                          END IF;*/

                                        END;

                               

                               

                                        print_log('ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure: '||v_id);

                               

                               

                                        print_log('Processing of GET_CONTENT completed');

                               

                               

                                        print_log(' ');

                               

                               

                                        BEGIN

                               

                               

                                           SELECT JSON_VALUE(data_content, '$.id')

                                             INTO v_json_id

                                             FROM BOLINF.BLK_API_CONTENT

                                            WHERE id = v_id;

                               

                               

                                        EXCEPTION WHEN OTHERS THEN

                                          print_log('Error while fetching json value ,ID is - '|| v_id || '.Error is-'|| SQLERRM);

                                          v_json_id := NULL;

                               

                               

                               

                               

                                        END;

                               

                               

                                            IF v_json_id IS NULL THEN

                                              print_log ('Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.' );

                                              print_log(' ');

                                              --retcode:= 1; --warning

                                              insert_audit('GET CONTENT', 'Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.',13,NULL,g_reference_details);

                                               --CSS-22047 enhancement

                                            ELSE

                                              print_log ('Supplier-'||cur_rec.vendor_name|| ' exists on Coupa, now remit-to-address can be uploaded.' );  --Added for CSS-24813.

                               

                               

                                           END IF;

                                    END IF;--- Added for CSS-24813

                                --Below IF-ELSE block has been shifted above, CSS-24813

                               

                               

                                   /*IF v_json_id IS NULL THEN

                                              print_log ('Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.' );

                                              print_log(' ');

                                              --retcode:= 1; --warning

                                              insert_audit('GET CONTENT', 'Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.',13,NULL,g_reference_details);

                                               --CSS-22047 enhancement

                                            ELSE

                                            */

                                      IF v_json_id IS NOT NULL THEN -- Added IF condition for CSS-24813

                                          g_reference_details:= 'remit address:'||cur_rec.name; --CSS-22047 report enhancement

                                          print_log('Now checking if address for the supplier- '||cur_rec.vendor_name||' exists on Coupa or not.');

                                          v_url := get_url('COUPA','COUPA_SUPPLIER','POST'); --no need to make a new table entry for supplier remit-to-address

                                              BEGIN

                                                  get_content(13, v_url||'/'||v_json_id||'/'||'addresses?name='||cur_rec.name,v_id);

                                              EXCEPTION WHEN OTHERS THEN

                                                  print_log('Error while calling get_content from copa_supplier_remit_api proc while checking remit-address, Error is - '|| SQLERRM);

                                                  -- /*KSS*/ -- below IF-ELSE to decide status of prog and whether to continue or exit

                                                  /*IF g_retcode = 2 THEN

                                                      retcode:= 2;

                                                      EXIT;

                                                  ELSE

                                                      retcode:=1;

                                                  END IF;*/

                                              END;

                                          print_log('ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure for remit-to-address: '||v_id);

                               

                               

                                          print_log(' ');

                               

                               

                                          BEGIN

                               

                               

                                           SELECT JSON_VALUE(data_content, '$.id')

                                             INTO v_json_id1

                                             FROM BOLINF.BLK_API_CONTENT

                                            WHERE id = v_id;

                               

                               

                                          EXCEPTION WHEN OTHERS THEN

                                          print_log('Error while fetching json value for remit-to-address ,ID is - '|| v_id || '.Error is-'|| SQLERRM);

                                          v_json_id1 := NULL;

                                          END;

                               

                               

                                          v_method := NULL; --22833

                               

                               

                                          IF v_json_id1 IS NULL THEN

                                           v_method := 'POST';

                                           v_url :=  v_url||'/'||v_json_id||'/addresses';

                                           print_log ('Address for supplier-'||cur_rec.vendor_name||' with site-id-'||cur_rec.name||' does not already exist on Coupa. Proceeding to create');

                                          ELSE

                                            IF cur_rec.supplier_update = 'N' --If Condition Added as part of JIRA#22833. To Ensure if supplier is updated again, it should not try to update the Supplier Site

                                            THEN

                                           v_url :=  v_url||'/'||v_json_id||'/addresses/'||v_json_id1;

                                           v_method := 'PUT';

                                           print_log ('Address for supplier-'||cur_rec.vendor_name||' with site-id-'||cur_rec.name||' already exists on Coupa. Proceeding to update');

                                            END IF;

                                          END IF;

                               

                               

                                          IF v_method IS NULL -- If Block Added as part of JIRA#22833

                                          THEN

                                          CONTINUE;

                                          END IF;

                               

                               

                               

                               

                                           OPEN l_cursor FOR

                                              SELECT cur_rec.active "active",

                                                     cur_rec.city "city",

                                                     CURSOR(SELECT cur_rec.country_code "code"

                                                              FROM dual) "country",

                                                     cur_rec.name "name",

                                                     cur_rec.postal_code "postal-code",

                                                     cur_rec.remit_to_code "remit-to-code",

                                                     cur_rec.state "state",

                                                     cur_rec.line1 "street1",

                                                     cur_rec.line2 "street2",

                                                     /*CURSOR(SELECT cur_rec.country_code "code"

                                                              FROM dual) "vat-country",*/

                                            cur_rec.jpmc_check_priority "custom-field-2",

                                            cur_rec.gst_id "custom-field-1"

                                              FROM dual;

                               

                               

                               

                               

                                      APEX_JSON.initialize_clob_output;

                                      APEX_JSON.write( l_cursor);

                                      l_clob := REGEXP_REPLACE(REGEXP_REPLACE(APEX_JSON.get_clob_output,'^\[',NULL),'\]$',NULL);     -- Find a better way to do this

                                      APEX_JSON.free_output;

                               

                               

                               

                               

                               

                               

                                      -- Call proc to insert into the BLK_API_CONTENT

                               

                               

                                        print_log(' ');

                               

                               

                                      print_log('Calling procedure INSERT_CONTENT to save the data in custom table for future reference');

                               

                               

                                      BEGIN

                                          insert_content (13,l_clob);

                                      EXCEPTION WHEN OTHERS THEN

                                          print_log('Error while call insert_content, Error is - '|| SQLERRM);

                                          --retcode:=1;

                                      END;

                               

                               

                               

                               

                                      print_log(' ');

                                      print_log('Processing of INSERT_CONTENT completed');

                               

                               

                                      print_log(' ');

                               

                               

                                    /* IF v_method = 'PUT' THEN

                                        print_log('Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa');

                               

                               

                                      ELSE -- v_method = 'POST'

                                        print_log('Calling procedure PUT_CONTENT to create new remit-to-address on Coupa');

                               

                               

                                      END IF; */ --Commented as part of JIRA#22833, Same is handled below.

                               

                               

                                      BEGIN

                              print_log('l_clob : '||l_clob);

                                          put_content(13,v_url,  l_clob, v_method);

                               

                               

                                              IF v_method = 'PUT' THEN

                                                print_log('Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa'); --22833

                                                v_flag_update:= v_flag_update+1;

                                              ELSE --v_method = 'POST'

                                                print_log('Calling procedure PUT_CONTENT to create new remit-to-address on Coupa');  --22833

                                                v_flag_create:= v_flag_create+1;

                                              END IF;

                               

                               

                                      EXCEPTION WHEN OTHERS THEN

                                        print_log('Error while calling put_content from copa_supplier_remit_api proc, Error is - '|| SQLERRM);

                                        -- /*KSS*/ -- below IF-ELSE to decide status of prog and whether to continue or exit

                                          /*IF g_retcode = 2 THEN

                                              retcode:= 2;

                                              EXIT;

                                          ELSE

                                              retcode:=1;

                                          END IF;*/

                               

                               

                                      END;

                               

                               

                               

                               

                                        print_log(' ');

                                        print_log('Processing of PUT_CONTENT completed');

                               

                               

                               

                               

                                      UPDATE BOLINF.BLK_API_CONTENT

                                         SET status = 'S'

                                       WHERE request_id =  fnd_global.conc_request_id;

                               

                               

                               

                               

                                        print_log(' ');

                                        print_log(' ');

                                        print_log('Program processed one vendor site-'|| cur_rec.name);

                                        print_log ('***************************************************************************');

                                        print_log(' ');

                                        print_log(' ');

                                  END IF;

                                  l_vendor_temp := cur_rec.supplier_number; --Added for CSS-24813, so that we can compare current fetch of cursor with last fetched value at the start of loop

                                END LOOP;

                               

                               

                               

                               

                                  IF v_flag = 0 THEN

                                    print_log('There was no remit-to-address to process. Kindly re-check if any address was created/updated in system.' );

                                  ELSE

                                    print_log ('Total remit-to-address got processed:                 '||v_flag);

                                    print_log ('Total new remit-to-address created on Coupa:          '||v_flag_create);

                                    print_log ('Total existing remit-to-address updated on Coupa:-    '||v_flag_update);

                                    print_log ('Total remit-to-address got rejected:                  '||(v_flag-v_flag_create-v_flag_update));

                                  END IF;

                               

                               

                               

                              Hope this helps.

                               

                                  print_log(' ');

                                  print_log(' ');

                                  print_log('**********************End Coupa remit-to-address api. **********************');

                                  print_log ('***************************************************************************');

                               

                               

                              END;

                               

                              +---------------------------------------------------------------------------+

                              • 12. Re: Unable to handle Brazilian characters in a webservice call through plsql
                                cormaco

                                I could't see anything obvious in your code.

                                I have one other suggestion:

                                Convert your v_chunk_string to RAW after DBMS_LOB.READ and then write this to the body using UTL_HTTP.WRITE_RAW

                                 

                                If this doesn't help either, consider setting up a mock-service you can send your request to so you can see exactly what gets transmitted.

                                SoapUI is one tool that can do that.

                                • 13. Re: Unable to handle Brazilian characters in a webservice call through plsql
                                  vikas yadav 250190-Oracle

                                  I am using POSTMAN to test the json. It sent perfectly to the system.

                                  Converted v_chunk_string to raw, got the same server error 400

                                   

                                  UTL_HTTP.write_text(v_http_request, utl_raw.cast_to_raw(v_chunk_string) );

                                   

                                  Thanks,

                                  Vikas Yadav

                                  • 14. Re: Unable to handle Brazilian characters in a webservice call through plsql
                                    vikas yadav 250190-Oracle

                                    sorry I meant

                                     

                                    UTL_HTTP.write_RAW(v_http_request, utl_raw.cast_to_raw(v_chunk_string) );

                                    1 2 Previous Next