1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

procedure using utl.http package

Discussion in 'SQL PL/SQL' started by neelambhumij, Jul 1, 2014.

  1. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi Guys..

    Please help me decoding this procedure...


    procedure get_rates_from_oanda (errbuf in out varchar2,
    errcode in out number,
    p_base_currency in varchar2,
    p_exchange_rate_date_from in varchar2,
    p_exchange_rate_date_to in varchar2)
    is

    v_exchange_rate_date_from date := trunc(to_date(p_exchange_rate_date_from,'YYYY/MM/DD HH24:MI:SS'));
    v_exchange_rate_date_to date := trunc(to_date(p_exchange_rate_date_to,'YYYY/MM/DD HH24:MI:SS'));
    v_count_cur1 number;
    v_count_cur2 number;

    v_msg VARCHAR2(80);
    v_url VARCHAR2(32767);
    v_start_date date;
    v_end_date date;
    v_nprices number;

    name VARCHAR2(255);
    value VARCHAR2(1023);

    v_num_of_conversions number;

    v_date varchar2(20) := '01/01/2011';

    v_oanda_webservice varchar2(240);

    Req utl_http.req;
    Resp utl_http.resp;

    v_Value varchar2(2000);

    v_Param varchar2(240); -- := g_oanda_params;
    v_Param_Length number; -- := length(v_Param);

    v_file_id number;
    i number := 0;
    v_currency_exists number;

    r_gl_daily_rates gl_daily_rates_interface%rowtype;
    HTML_FILE VARCHAR(32767);

    e_invalid_date exception;

    cursor c_exchange_currencies (l_base_curr varchar2,
    l_exch_date date) is
    select fc.currency_code, fc.description, fc.issuing_territory_code,
    fc.precision, fc.extended_precision, fc.symbol
    from fnd_currencies fc
    where fc.currency_code != l_base_curr
    and nvl(fc.enabled_flag,'N') = 'Y'
    and nvl(fc.currency_flag,'N') = 'Y'
    and trunc(nvl(l_exch_date,sysdate)) between trunc(nvl(fc.start_date_active,nvl(l_exch_date,sysdate)))
    and nvl(fc.end_date_active,nvl(l_exch_date,sysdate));
    -- and not exists (
    -- select '1'
    -- from gl_daily_rates gr
    -- where gr.from_currency = l_base_curr
    -- and gr.to_currency = fc.currency_code
    -- and trunc(gr.conversion_date) = trunc(l_exch_date)
    -- and gr.conversion_type = g_user_conversion_type);

    cursor c_exchange_rate_files is
    select erf.file_id
    from xxgl_daily_exchange_rate_files erf
    where nvl(erf.status,'N') = 'N'
    -- and nvl(erf.org_id,fnd_global.org_id) = fnd_global.org_id;
    and erf.org_id = fnd_global.org_id;

    cursor c_exchange_rates (l_file_id number) is
    select er.rowid row_id, er.*
    from xxgl_daily_exchange_rates er
    where er.file_id = l_file_id
    and nvl(er.status,'N') = 'N'
    -- and nvl(er.org_id,fnd_global.org_id) = fnd_global.org_id;
    and er.org_id = fnd_global.org_id;

    Begin

    g_procedure_name := 'get_rates_from_oanda';

    -- select fnd_profile.value ('APPS_FRAMEWORK_AGENT')
    -- into v_framework_agent
    -- from dual;
    /*
    if v_exchange_rate_date_from - trunc(sysdate) >= 0 then
    errbuf := 'From Date = Today''s Date. Oanda cannot deliver rates on this date. ' ||
    'Please specify a Date earlier than today.';
    raise e_invalid_date;
    end if; -- v_exchange_rate_date_from - trunc(sysdate) >= 0

    if v_exchange_rate_date_to - trunc(sysdate) >= 0 then
    errbuf := 'To Date = Today''s Date. Oanda cannot deliver rates on this date. ' ||
    'Please specify a Date earlier than today.';
    raise e_invalid_date;
    end if; -- v_exchange_rate_date_to - trunc(sysdate) >= 0

    if v_exchange_rate_date_from > v_exchange_rate_date_to then
    errbuf := 'From Date cannot be greater than the To Date. ' ||
    'Please specify valid date ranges.';
    raise e_invalid_date;
    end if; -- v_exchange_rate_date_to - trunc(sysdate) = 0
    */

    -- v_oanda_webservice := g_framework_agent || '/' || g_oanda_webservice;

    --for c in c_exchange_currencies (p_base_currency, v_exchange_rate_date_to) loop

    Begin

    delete from gl_daily_rates_interface;
    commit;

    -- assign date
    v_start_date := v_exchange_rate_date_from;
    --v_end_date := v_exchange_rate_date_to; --Changed by Paul Kheswa with a line below to get rates less a day to the current date with reference to CRF # 0313
    v_end_date := v_exchange_rate_date_to - 1;

    -- get the Number of Coversions from the difference of start and end date

    v_nprices := v_end_date - v_start_date + 1;

    if v_nprices = 1 then
    v_end_date := v_end_date + 1;
    --v_url := 'http://lelo.bateman.co.za:8080/Bateman/sucessful.jsp?date1=' || to_char(v_end_date,'MM/DD/YYYY') || '&base_currency=' || p_base_currency || '&nprices=' || v_nprices; --Changed by Paul Kheswa with a line below to get the ask_rate with reference to CRF # 0313
    v_url := 'http://helen.bateman.com:8080/Bateman/sucessful.jsp?date1=' || to_char(v_end_date,'MM/DD/YYYY') || '&base_currency=' || p_base_currency || '&nprices=' || v_nprices;
    else
    v_nprices := v_nprices + 1;
    -- v_url := 'http://lelo.bateman.co.za:8080/Bateman/sucessful.jsp?date1=' || to_char(v_start_date,'MM/DD/YYYY') || '&date=' || to_char(v_end_date,'MM/DD/YYYY') || '&base_currency=' || p_base_currency || '&nprices=' || v_nprices; --Changed by Paul Kheswa with a line below to get the ask_rate with reference to CRF # 0313
    v_url := 'http://helen.bateman.com:8080/Bateman/sucessful.jsp?date1=' || to_char(v_start_date,'MM/DD/YYYY') || '&date=' || to_char(v_end_date,'MM/DD/YYYY') || '&base_currency=' || p_base_currency || '&nprices=' || v_nprices;
    end if;

    log ('URL = ' ||v_url);--nb

    req := Utl_Http.Begin_Request ( url => v_url, method => 'GET' );
    resp := Utl_Http.Get_Response ( r => req );
    log ( 'Status code: ' || resp.status_code );--nb
    log ( 'Reason phrase: ' || resp.reason_phrase );--nb
    FOR i IN 1..Utl_Http.Get_Header_Count ( r => resp )
    LOOP
    Utl_Http.Get_Header ( r => resp, n => i, name => name, value => value );
    log( name || ': ' || value);--nb
    END LOOP;
    BEGIN
    LOOP
    Utl_Http.Read_Text ( r => resp, data => v_msg );
    HTML_FILE := v_msg || v_msg;
    log ( v_msg );
    END LOOP;--nb
    EXCEPTION
    WHEN Utl_Http.End_Of_Body THEN
    NULL;
    END;
    Utl_Http.End_Response ( r => resp );
    --return HTML_FILE;
    EXCEPTION
    WHEN Utl_Http.Request_Failed THEN
    log ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm );
    WHEN Utl_Http.Http_Server_Error THEN
    log ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
    WHEN Utl_Http.Http_Client_Error THEN
    log ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
    --WHEN OTHERS THEN
    --log (SQLERRM);

    End;

    update xxgl_daily_exchange_rate_files
    set status = 'N',
    status_message = null,
    org_id = fnd_global.org_id,
    created_by = fnd_global.user_id,
    creation_date = sysdate,
    last_updated_by = fnd_global.user_id,
    last_update_date = sysdate,
    last_update_login = fnd_global.login_id
    where created_by is null;

    update xxgl_daily_exchange_rates
    set status = 'N',
    status_message = null,
    org_id = fnd_global.org_id,
    created_by = fnd_global.user_id,
    creation_date = sysdate,
    last_updated_by = fnd_global.user_id,
    last_update_date = sysdate,
    last_update_login = fnd_global.login_id
    where created_by is null;

    --end loop; -- c in c_exchange_currencies (v_base_currency,

    for f in c_exchange_rate_files loop

    for r in c_exchange_rates (f.file_id) loop

    r_gl_daily_rates := null;

    r_gl_daily_rates.from_currency := r.base_currency;
    r_gl_daily_rates.to_currency := r.quote_currency;
    r_gl_daily_rates.from_conversion_date := r.exchange_rate_date + 1;
    r_gl_daily_rates.to_conversion_date := r.exchange_rate_date + 1;
    r_gl_daily_rates.user_conversion_type := g_user_conversion_type;
    --r_gl_daily_rates.conversion_rate := r.bid_rate; --Changed by Paul Kheswa with a line below to get the ask_rate with reference to CRF # 0313
    r_gl_daily_rates.conversion_rate := r.ask_rate;
    --r_gl_daily_rates.inverse_conversion_rate := 1 / r.bid_rate; --Changed by Paul Kheswa with a line below to get the ask_rate with reference to CRF # 0313
    r_gl_daily_rates.inverse_conversion_rate := 1 / r.ask_rate;
    r_gl_daily_rates.mode_flag := 'I';
    r_gl_daily_rates.user_id := r.created_by;

    v_currency_exists := 0;

    select count(*) into v_currency_exists
    from dual
    where r.base_currency in (select currency_code from fnd_currencies_vl where ENABLED_FLAG = 'Y' and END_DATE_ACTIVE is null)
    and r.quote_currency in (select currency_code from fnd_currencies_vl where ENABLED_FLAG = 'Y' and END_DATE_ACTIVE is null);

    if v_currency_exists > 0 then

    if r.base_currency <> r.quote_currency then

    --if (r.bid_rate <> 1) and (1/r.bid_rate <> 1) then --Changed by Paul Kheswa with a line below to check against the ask_rate with reference to CRF # 0313
    if (r.ask_rate <> 1) and (1/r.ask_rate <> 1) then

    -- log ( 'Passed all checks - inserting into interface');

    /* Check if the currency combo already exists */
    select count (*)
    into v_count_cur1
    from gl.gl_daily_rates_interface
    where from_currency = r_gl_daily_rates.from_currency
    and to_currency = r_gl_daily_rates.to_currency
    and from_conversion_date = r_gl_daily_rates.from_conversion_date
    and to_conversion_date = r_gl_daily_rates.to_conversion_date;

    /* Check if the reverse currency combo exists */
    select count (*)
    into v_count_cur2
    from gl.gl_daily_rates_interface
    where from_currency = r_gl_daily_rates.to_currency
    and to_currency = r_gl_daily_rates.from_currency
    and from_conversion_date = r_gl_daily_rates.from_conversion_date
    and to_conversion_date = r_gl_daily_rates.to_conversion_date;

    /* Only insert if currency combo does not exist */
    if v_count_cur1 = 0 and v_count_cur2 = 0
    then
    insert into gl_daily_rates_interface
    values r_gl_daily_rates;

    r_gl_daily_rates.user_conversion_type := 'Spot';

    insert into gl_daily_rates_interface
    values r_gl_daily_rates;

    update xxgl_daily_exchange_rates
    set status = 'S',
    status_message = 'Rate Loaded Succesfully'
    where rowid = r.row_id;

    end if;

    end if;

    end if;

    end if;

    end loop; -- r in c_exchange_rates (f.file_id)

    update xxgl_daily_exchange_rate_files
    set status = 'S',
    status_message = 'File Loaded Succesfully'
    where file_id = f.file_id;

    end loop; -- f in c_exchange_rate_files

    --output_results(p_base_currency, v_exchange_rate_date_from, v_exchange_rate_date_to);

    exception
    when e_invalid_date then
    errbuf := errbuf;
    errcode := '2';
    /* when others then
    errbuf := UTL_HTTP.get_detailed_sqlerrm;
    errcode := 2;
    log ('errbuf: ' || errbuf, g_tog);
    UTL_HTTP.end_response(Resp);
    raise;*/

    end get_rates_from_oanda;


    Can anyone assist me here.. unable to find how the http response is connected to the database tables ??
    OR y utl.http used here??

    Thanks and Regards
    Neelam
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What means to help to decode procedure?
    Everything is very simple:
    on units you study this procedure and you make the description of algorithm of this procedure...
     
  3. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi Sergey

    I just want to know is there any statement that is pulling data into the oracle tables from http response.

    Thanks and Regards
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi ,Neelam .

    This block processes the response from the http-server
    Code (SQL):

    .......
    req := Utl_Http.Begin_Request ( url => v_url, method => 'GET' );
     resp := Utl_Http.Get_Response ( r => req );
     log ( 'Status code: ' || resp.status_code );--nb
     log ( 'Reason phrase: ' || resp.reason_phrase );--nb
     FOR i IN 1..Utl_Http.Get_Header_Count ( r => resp )
     LOOP
     Utl_Http.Get_Header ( r => resp, n => i, name => name, VALUE => VALUE );
     log( name || ': ' || VALUE);--nb
     END LOOP;
     BEGIN
     LOOP
     Utl_Http.Read_Text ( r => resp, DATA => v_msg );
     HTML_FILE := v_msg || v_msg;
     log ( v_msg );
     END LOOP;--nb
     EXCEPTION
     WHEN Utl_Http.End_Of_Body THEN
     NULL;
     END;
     Utl_Http.End_Response ( r => resp );
     --return HTML_FILE;
     EXCEPTION
     WHEN Utl_Http.Request_Failed THEN
     log ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm );
     WHEN Utl_Http.Http_Server_Error THEN
     log ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
     WHEN Utl_Http.Http_Client_Error THEN
     log ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
     --WHEN OTHERS THEN
     --log (SQLERRM);
     ......
     END;
     
     
    neelambhumij likes this.
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Neelam,

    You're right in your diagnostics !

    As Sergey pointed out above, this portion processes the response from the Begin_Request/Get_Response commands.
    However, there is definitely something missing which will extract the proper tags from the HTML response and assign to appropriate variables which will in turn be used to feed the staging table - which I guess is "xxgl_daily_exchange_rates".

    There is certainly a call to another procedure which will perform above task and has been "removed" (???) from this portion of code.
     
    neelambhumij likes this.
  6. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi Rajen

    I just looked into the installation guide and found that the staging tables have been configured with the web application using a java coding.(dnt have much knowledge of that).
    But... definitely with the specified http request from the above code ... the tables get updated with new values..

    Thanks !!!!


    Kind Regards
    Neelam
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Neelam,

    Thanks for sharing, appreciated :).

    Now, I understand the process.

    When the link "'http://helen.bateman.com:8080/Bateman/sucessful.jsp..." (most probably on the intranet) is invoked by the UTL_HTTP routines it reads (and implicitly executes) whatever piece of code is incorporated in the jsp and we certainly can INSERT/UPDATE an Oracle table based on a remote query (by OANDA APIs or Web Services from what I understand from the code & comments).

    This is a pretty good idea (which forum viewers can make use of !) where you have intelligently 'modularized' the two layers / sub-processes: one which reads the staging table and updates EBS and the other which updates the staging table. The latter can tomorrow change it's source and technology without any impact on EBS side.
     
  8. Shravan

    Shravan Guest

    Hi Neelam,

    Thanks for the package, I am also working on similar requirement currently able to load to column clob in staging table,

    In you program please help me to understand how did you able to load to staging table reading from http response.

    Thanks in advance,
    Shravan.