create or replace PROCEDURE call_rest_from_plsql_with_params_pwd(p_username varchar2,p_password varchar2)
is
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(200) := 'http://140.238.163.115:9898/alfupload/services/user/passwordUpdtInst';
-- Post Parameters
v_param VARCHAR2(500) := 'username='||p_username||'&password='||p_password;
--not working '{"username":"123","password":"TEJA123#"}';
--'username=123\&password=TEJA123#'; --'pwd=password123\&core_id=12223\&type=PK\&reason=Test reason';
-- '{"username":"' || p_username || '","password":"' || p_password || '"}';
-- l_param_list := 'msisdn=somenumbers&amount=-100&PreBalance=&partycode=XXXXXX&BillID=&BillNbr=&AccountID=&AccountCode=&SN=';
v_param_length NUMBER := length(v_param);
BEGIN
-- Set up proxy servers if required
-- UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
-- UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_TEXT (r => req,
data => v_param); resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END call_rest_from_plsql_with_params_pwd;
/
PROCEDURE einvoice_json_response(p_inv_json varchar2,p_inv_cntrl_no number,p_ugandatime varchar2) --,p_data out varchar2)
is
v_responce VARCHAR2(10000);
v_returnCode varchar2(10);
v_req utl_http.req;
v_res utl_http.resp;
v_url varchar2(4000) := 'http://20.20.20.6:9880/efristcs/ws/tcsapp/getInformation';
--v_url varchar2(4000) := 'http://172.16.1.23:9880/efristcs/ws/tcsapp/getInformation';
--v_url varchar2(4000) := 'http://127.0.0.1:9880/efristcs/ws/tcsapp/getInformation';
v_name varchar2(4000);
v_buffer varchar2(4000);
v_data VARCHAR2(20024);
v_einv_no VARCHAR2(50);
v_einv_id VARCHAR2(100);
--v_content varchar2(4000) := '{"comp_code":"'||p_comp||'", "main_code":"'||p_main||'", "doc_no":"'||p_doc_no||'","deviceId":"'||p_deviceId||'", "Name":"'||'vijay-MSG'||'","topicbodytitlemsg":"'
-- ||p_bodytitle||'","topicbodymsg":"'||p_bodymsg||'"}';
v_encoded_content varchar2(32000):= to_base64_encode(p_inv_json) ;--(einvoice_json_content_obj(p_inv_cntrl_no)) ;
/**
latest device no updated --"deviceNo": "TCSd0a2ca1134269031",
*/
v_content varchar2(30000):= '{
"data": {
"content": ''"'||v_encoded_content||'"'',
"dataDescription": {
"codeType": "0",
"encryptCode": "1",
"zipCode": "0"
}
},
"globalInfo": {
"appId": "AP01",
"version": "1.1.20191201",
"dataExchangeId": "72738743847307",
"interfaceCode": "T109",
"requestCode": "TP",
"requestTime":"'||p_ugandatime||'",
"responseCode": "TA",
"userName": "admin",
"deviceMAC": "Ffffffff",
"deviceNo": "TCS179f59889740109",
"tin": "1000072627",
"brn": "",
"taxpayerID": "489774308230155677",
"longitude": "116.397128",
"latitude": "39.916527",
"extendField": {
"responseDateFormat": "dd\/MM\/yyyy",
"responseTimeFormat": "dd\/MM\/yyyy HH:mm:ss"
}
},
"returnStateInfo": {
"returnCode": "",
"returnMessage": ""
}
}';
-- "requestTime": "2021-08-03 04:34:00",
--"'||p_ugandatime||'"'',
v_result varchar2(20);
v_res_cnt number := 0;
c number := 0;
v_error varchar2(1000);
v_cnt number;
v_loopcnt number;
http_failure exception;
pragma exception_init(http_failure, -29273);
v_dummy varchar2(32000);
v_decoded_content varchar2(32000):=null;
Begin
dbms_output.put_line('v_content@@'||v_content);
--v_dummy:=einvoice_json_content_obj(p_inv_cntrl_no);
--dbms_output.put_line(v_dummy);
--for i in 1..12 loop
Begin
v_req := utl_http.begin_request(v_url, 'POST',' HTTP/1.1');
utl_http.set_transfer_timeout(15);
utl_http.set_header(v_req, 'content-type', 'application/json');
utl_http.set_header(v_req, 'Content-Length', length(v_content));
utl_http.write_text(v_req, v_content);
v_res := utl_http.get_response(v_req);
utl_http.read_text(v_res, v_data);
utl_http.end_response(v_res);
--dbms_output.put_line(i);
dbms_output.put_line(v_data);
v_decoded_content := from_base64_decode(v_data);
--dbms_output.put_line(from_base64_decode(v_data));
--dbms_output.put_line(v_decoded_content);
--p_data :=v_decoded_content;
EXCEPTION
WHEN UTL_HTTP.transfer_timeout
THEN
DBMS_OUTPUT.put_line ('Transfer timeout');
WHEN http_failure
THEN
DBMS_OUTPUT.put_line ('HTTP failure - timeout?');
-- RAISE;
END;
--end loop;
Begin
INSERT INTO E_invoice_error_log(UNIT_CODE,DOC_NO,ERR_STR,CRTD_DT,JSON_STR)
VALUES('INV', p_inv_cntrl_no, v_data, sysdate,p_inv_json);
Exception When Others Then
null;
End;
Begin
select to_char(regexp_substr(v_data,'"returnCode":\s*"([^"]*?)"',1,1,'',1))
into v_returnCode
from dual;
Exception when others then
null;
End;
If v_returnCode ='00' then
Begin
--select to_char(regexp_substr(inv_doc,'"invoiceNo":\s*"([^"]*?)"',1,1,'',1)) as einv_no
--,to_char(regexp_substr(inv_doc,'"invoiceId":\s*"([^"]*?)"',1,1,'',1)) as einv_id from Pos_Einv_Doc;
insert into Pos_Einv_Doc (einv_doc_id,inv_cntrl_no,inv_doc,crtd_by,crtd_dt)
values (Pos_Einv_Doc_seq.nextval,p_inv_cntrl_no,v_decoded_content,'SATHGURU',sysdate);
Exception When Others Then
dbms_output.put_line(' Error occured while insertion '||sqlerrm);
End;
Begin
Begin
/select to_char(regexp_substr(inv_doc,'"invoiceNo":\s"([^"]*?)"',1,1,'',1)) --einv_no
,to_char(regexp_substr(inv_doc,'"invoiceId":\s*"([^"]*?)"',1,1,'',1)) -- einv_id
into v_einv_no,v_einv_id
from Pos_Einv_Doc
where inv_cntrl_no = p_inv_cntrl_no;*/
select to_char(regexp_substr(v_decoded_content,'"invoiceNo":\s*"([^"]*?)"',1,1,'',1))
,to_char(regexp_substr(v_decoded_content,'"invoiceId":\s*"([^"]*?)"',1,1,'',1))
into v_einv_no,v_einv_id
from dual;
Exception when others then
null;
End;
update pos_invoice_hdr a set a.einv_no= v_einv_no ,a.einv_id = v_einv_id
where inv_cntrl_no = p_inv_cntrl_no;
End;
/*Begin
--select to_char(regexp_substr(inv_doc,'"invoiceNo":\s*"([^"]*?)"',1,1,'',1)) as einv_no
--,to_char(regexp_substr(inv_doc,'"invoiceId":\s*"([^"]*?)"',1,1,'',1)) as einv_id from Pos_Einv_Doc;
insert into CE_INV_EINVOICE (einv_doc_id,inv_cntrl_no,inv_doc,crtd_by,crtd_dt)
INSERT INTO CE_INV_EINVOICE(COMP_CODE, MAIN_CODE, UNIT_CODE, FIN_YEAR, INV_NO, INV_DT, CRTD_BY, CRTD_DT, TRNHDR_ID
values (Pos_Einv_Doc_seq.nextval,p_inv_cntrl_no,v_decoded_content,'SATHGURU',sysdate);
Exception When Others Then
dbms_output.put_line(' Error occured while insertion '||sqlerrm);
End;*/
End If; -- return code =00 ends
commit;
End einvoice_json_response;
