create or replace procedure Submit_Order (
  p_our_ref    in integer,

  p_vendor_id  in integer,
  p_scu        in integer,
  p_quantity   in integer )
is
  v_url                 varchar2(255);

  v_user                varchar2(80);
  v_password            varchar2(80);
  v_msg                 varchar2(32767);
  v_verified_msg        varchar2(32767);
  v_our_ref_confirmed   integer;
  v_order_date          date;

  our_ref_not_confirmed exception;

  procedure Create_Order_Message
  is
  begin
    v_msg :=
      Tags.ordr

||    Tags.customer_id || To_Char ( 1 )          || Tags.Close ( Tags.customer_id )
||    Tags.our_ref     || To_Char ( p_our_ref )  || Tags.Close ( Tags.our_ref )
||    Tags.scu         || To_Char ( p_scu )      || Tags.Close ( Tags.scu )
||    Tags.quantity    || To_Char ( p_quantity ) || Tags.Close ( Tags.quantity )
||    Tags.Close ( Tags.ordr );
  end Create_Order_Message;

  procedure Report_Exception is

  begin
    update customer_orders
    set order_date = sysdate, status = 'failed', err_msg = v_msg
    where order_ref = p_our_ref;
    commit;
    Send_Error_Mail ( p_our_ref, v_msg );
  end Report_Exception;

begin

  select url, the_user, password into v_url, v_user, v_password from vendors
    where vendor_id = p_vendor_id;

  Create_Order_Message;

  v_url := v_url || '?p_order=' || v_msg;

  v_msg := Get_Http_Request (
    p_url => v_url, p_user => v_user, p_password => v_password );


  v_verified_msg := Parse_Message.Verify_Format ( v_msg, Tags.confirmation );

  v_our_ref_confirmed := To_Number (
    Parse_Message.Extract_Value ( v_verified_msg, Tags.your_ref )
    );
  if v_our_ref_confirmed <> p_our_ref
    then raise our_ref_not_confirmed; end if;

  v_order_date := To_Date (
    Parse_Message.Extract_Value ( v_verified_msg, Tags.date_received ),
    'hh:mi:ss::DD-Mon-YYYY'

    );

  update customer_orders
    set order_date = sysdate,
    status = 'submitted'
    where order_ref = p_our_ref;
  commit;
exception
  when our_ref_not_confirmed then
    v_msg :=
     'our_ref_confirmed:'
||   To_Char(v_our_ref_confirmed)

||   ' <> our_ref:'
||   To_Char(p_our_ref);
    Report_Exception;

  when Parse_Message.Bad_Format then
    Report_Exception;

  when others then
    if SQLCODE = -29273
    then
      v_msg := Utl_Http.Get_Detailed_Sqlerrm;
    else
      v_msg := SQLERRM ;

    end if;
    Report_Exception;
end Submit_Order;
/
Show Errors