POST TO FBR ( DB Level for POS Invoicing Using PRAL Service )

 CREATE TABLE SGI_MACHINES

(

  PC_NAME          VARCHAR2(50 BYTE),

  TILL_NO          VARCHAR2(50 BYTE),

  STATUS           VARCHAR2(50 BYTE),

  UNIT_ID          NUMBER,

  UNIT_NAME        VARCHAR2(100 BYTE),

  POS_ID           NUMBER,

  TILLE_STATUS_NO  NUMBER,

  IP_ADR           VARCHAR2(100 BYTE)

);

/

CREATE OR REPLACE PROCEDURE post_to_fbr (

  p_salref IN NUMBER

) IS

  l_json      CLOB;

  l_request   UTL_HTTP.req;

  l_response  UTL_HTTP.resp;

  l_buffer    VARCHAR2(32767);

  l_response_text CLOB;

  response_str varchar2(2000);

  ip_location varchar2(100)  ; --:= '192.168.50.74:8524' ;

BEGIN

  -- Build JSON

  SELECT IP_ADR||':8524' INTO IP_LOCATION FROM SGI_MACHINES WHERE PC_NAME = GET_MACHINE_NAME;

SELECT

  '{' ||

  '"InvoiceNumber":"' || '",'||

  '"POSID":"' || m.posid || '",' ||

  '"USIN":"' || m.inv_num || '",' ||

  '"DateTime":"' ||TO_CHAR(m.sal_dat,'YYYY-MM-DD HH:MI:SS') || '",' ||

  '"BuyerNTN":"' || '12345678' || '",' ||

  '"BuyerCNIC":"' || '1111111111111' || '",' ||

  '"BuyerName":"' || 'CUSTOMER NAME' || '",' ||

  '"BuyerPhoneNumber":' ||'123456789' || ',' ||

  '"PaymentMode":"' || 1 || '",' ||

  '"TotalSaleValue":"' || m.gst_sale_amount || '",' ||  

  '"TotalQuantity":"' || m.gst_qty || '",' ||

  '"TotalBillAmount":"' ||  m.gst_bill_amount || '",' ||   

  '"TotalTaxCharged":' || m.gst_stax_amount || ',' ||

  '"Discount":"' || 0 || '",' ||

  '"FurtherTax":"' || 0 || '",' ||    

  '"InvoiceType":"' || 1|| '",' ||  

  '"items":[' ||

     LISTAGG(

       '{' ||

       '"ItemCode":"' || d.icode || '",' ||

       '"ItemName":"' || d.iname || '",' ||

       '"Quantity":"' || d.qty   || '",' ||

       '"PCTCode":"'  || substr(d.gst_pct_code,1,4)||substr(d.gst_pct_code,6,9) || '",' ||

       '"TaxRate":"' || 18 || '",' ||

       '"SaleValue":"' || (d.qty * d.tp_rate)|| '",' ||

       '"TotalAmount":"' || (((d.tp_rate*18) / 100 ) * d.qty ) + (d.qty * d.tp_rate) || '",' ||

       '"TaxCharged":"' || (((d.tp_rate*18) / 100 ) * d.qty ) || '",' ||

       '"Discount":"' || 10 || '",' ||

       '"FurtherTax":"' || 0 || '",' ||

       '"InvoiceType":"' || 1 || '",' ||

       '"RefUSIN":"' || '"' ||

       '}', ','

     ) WITHIN GROUP (ORDER BY d.icode)

  || ']}' AS json_data into l_json

FROM sgi_sal_00 m

JOIN sgi_sal_01 d ON d.sal_ref = m.sal_ref

WHERE m.sal_ref = P_SALREF

GROUP BY

  m.inv_num,

  m.sal_dat,

  m.pty_cod,

  m.iamount,

  m.posid 

  ; 

  -- HTTP Request

  l_request := UTL_HTTP.begin_request(

                  'http://'||ip_location||'/api/IMSFiscal/GetInvoiceNumberByModel',

                  'POST',

                  'HTTP/1.1'

               );

  UTL_HTTP.set_header(l_request, 'Content-Type', 'application/json');

  UTL_HTTP.set_header(l_request, 'Content-Length', LENGTH(l_json));

  UTL_HTTP.write_text(l_request, l_json);

  l_response := UTL_HTTP.get_response(l_request);

  -- Read Response

  LOOP

    UTL_HTTP.read_text(l_response, l_buffer);

    l_response_text := l_response_text || l_buffer;

  END LOOP;

EXCEPTION

  WHEN UTL_HTTP.end_of_body THEN

    UTL_HTTP.end_response(l_response);

    -- Save response number if needed

   -- DBMS_OUTPUT.put_line(l_response_text);

    --response_str := l_response_text;

    --DBMS_OUTPUT.put_line(response_str);

   -- DBMS_OUTPUT.put_line(REGEXP_SUBSTR(REGEXP_SUBSTR(l_response_text, '"InvoiceNumber":"([^"]+)"', 1, 1, NULL, 1),'^\d+',1, 1));    

    update sgi_sal_00 set gov_inv_no = REGEXP_SUBSTR(REGEXP_SUBSTR(l_response_text, '"InvoiceNumber":"([^"]+)"', 1, 1, NULL, 1),'^\d+',1, 1) where sal_ref =  P_SALREF; commit;

    UPDATE SGI_SAL_00 SET QRCODE = MAKE_QR.QR_BIN(REGEXP_SUBSTR(REGEXP_SUBSTR(l_response_text, '"InvoiceNumber":"([^"]+)"', 1, 1, NULL, 1),'^\d+',1, 1)) WHERE SAL_REF = P_SALREF; commit;

END;

/