Number2word in pl/sql conversion

PACKAGE Pkg_number2word is

Function Num2Word_million (pCompCode varchar2, pAmount Number) return varchar2 ;

Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 ;

Function Interprt (pAmount Number) return Varchar2 ;

Function Numbertext (pAmount Number) return Varchar2 ;

End;

/

 PACKAGE BODY pkg_number2word is

Function Num2Word_million(pCompCode varchar2, pAmount Number) return Varchar2 is

    vWord                   Varchar2(200) ;

    vAmount                 Number(15,2) ;

    vBillionHundred         Number(15) Default 0 ;

    vBillion                Number(15) Default 0 ;

    vMillionHundred         Number(15) Default 0 ;

    vMillion                Number(10) Default 0 ;

    vHundredThousand        Number(12) Default 0 ;

    vThousand               Number(10) Default 0 ;

    vHundred                Number(10) Default 0 ;

    vOnes                   Number(2)  Default 0 ;

    vDecimal                Number(2)  Default 0 ;

    vCurrCode               Cm004_comp_mst.curr_code%type ;

    vCurrSubUnit            Cm001_currency_mst.subunit%type ;

   -- vCurrMainUnit           Cm001_currency_mst.mainunit%type ;

   vCurrMainUnit          varchar2(500);

BEGIN

    vAmount := Abs(pAmount) ;

    Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;

    If vBillionHundred >= 1 then

        vAmount := vAmount-(vBillionHundred*100000000000) ;

    End if;

    Select Floor(vAmount/1000000000) Into vBillion From Dual ;

    If vBillion >= 1 then

        vAmount := vAmount-(vBillion*1000000000) ;

    End if;

    Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;

    If vMillionHundred >= 1 then

        vAmount := vAmount-(vMillionHundred*100000000) ;

    End if;

    Select Floor(vAmount/1000000) Into vMillion From Dual ;

    If vMillion >= 1 then

        vAmount := vAmount-(vMillion*1000000) ;

    End if;

    Select Floor(vAmount/100000) Into vHundredThousand From Dual ;

    If vHundredThousand >= 1 then

        vAmount := vAmount-(vHundredThousand*100000) ;

    End if;

    Select Floor(vAmount/1000) Into vThousand From Dual ;

    If vThousand >= 1 then

        vAmount := vAmount-(vThousand*1000) ;

    End if;

    Select Floor(vAmount/100) Into vHundred From Dual ;

    If vHundred >= 1 then

        vAmount := vAmount-(vHundred*100) ;

    End if;

    Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;

    vOnes := vAmount - (vDecimal/100) ;

    vWord := '' ;

    If vBillionHundred >= 1 then

        vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;

        If vBillion = 0 then

            vWord := vWord || Interprt(vBillion)||' BILLION ' ;

        End if ;

    End if ;

    If vBillion >= 1 then

        vWord := vWord || Interprt(vBillion)||' BILLION ' ;

    End if ;

    If vMillionHundred >= 1 then

        vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;

        -----if million is 0 then appends the million

        If vMillion = 0 then

            vWord := vWord || Interprt(vMillion)||' MILLION ' ;

        End if ;

    End if ;

    If vMillion >= 1 then

        vWord := vWord || Interprt(vMillion)||' MILLION ' ;

    End if ;

    ---------prints the hundred thousand

    If vHundredThousand >= 1 then

        vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;

        -----if thousand is 0 then appends the thousand

        If vThousand = 0 then

            vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

        End if ;

        ---------

    End if ;

    If vThousand >= 1 then

        vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

    End if ;

    If vHundred >= 1 then

        vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;

    End if ;

    Begin

        SELECT a.curr_code INTO vCurrCode

          FROM Cm004_comp_mst a

         WHERE a.comp_code = pCompCode ;

    Exception

        When Others Then

            vCurrCode := null;

    End;

    If vCurrCode is not null Then

        Begin

            SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit

              FROM Cm001_currency_mst a

             WHERE a.curr_code = vCurrCode ;

        Exception

            When Others Then

                vCurrMainUnit := null ;

                vCurrSubUnit := null ;

        End ;

    End If;

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'DOLLARS') ;

    If vDecimal > 0 then

        If vWord is not null then

           -- vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;

           If trim(vWord) ='DOLLARS' then   -- Naren added on 20/06/2022 

            dbms_output.put_line('entered in if dollars');

             vWord :=pkg_number2word.Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;   

            Else

            vWord := vWord ||' AND '|| pkg_number2word.Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;   

            End If; 

        Else

            vWord :=  Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;

        End if;

    Else

        If vWord is not null Then

            vWord := vWord || ' ONLY' ;

        End If;

    End if ;

    Return (vWord);

Exception

    When Value_Error then

        return null ;

End;

Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 IS

  vWord                   Varchar2(200) ;

    vAmount                 Number(15,2) ;

    vBillionHundred         Number(15) Default 0 ;

    vBillion                Number(15) Default 0 ;

    vMillionHundred         Number(15) Default 0 ;

    vMillion                Number(10) Default 0 ;

    vHundredThousand        Number(12) Default 0 ;

    vThousand               Number(10) Default 0 ;

    vHundred                Number(10) Default 0 ;

    vOnes                   Number(2)  Default 0 ;

    vDecimal                Number(2)  Default 0 ;

    vCurrCode               Cm004_comp_mst.curr_code%type ;

    vCurrSubUnit            Cm001_currency_mst.subunit%type ;

   -- vCurrMainUnit           Cm001_currency_mst.mainunit%type ;

   vCurrMainUnit          varchar2(500);

BEGIN

    vAmount := Abs(pAmount) ;

    Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;

    If vBillionHundred >= 1 then

        vAmount := vAmount-(vBillionHundred*100000000000) ;

    End if;

    Select Floor(vAmount/1000000000) Into vBillion From Dual ;

    If vBillion >= 1 then

        vAmount := vAmount-(vBillion*1000000000) ;

    End if;

    Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;

    If vMillionHundred >= 1 then

        vAmount := vAmount-(vMillionHundred*100000000) ;

    End if;

    Select Floor(vAmount/1000000) Into vMillion From Dual ;

    If vMillion >= 1 then

        vAmount := vAmount-(vMillion*1000000) ;

    End if;

    Select Floor(vAmount/100000) Into vHundredThousand From Dual ;

    If vHundredThousand >= 1 then

        vAmount := vAmount-(vHundredThousand*100000) ;

    End if;

    Select Floor(vAmount/1000) Into vThousand From Dual ;

    If vThousand >= 1 then

        vAmount := vAmount-(vThousand*1000) ;

    End if;

    Select Floor(vAmount/100) Into vHundred From Dual ;

    If vHundred >= 1 then

        vAmount := vAmount-(vHundred*100) ;

    End if;

    Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;

    vOnes := vAmount - (vDecimal/100) ;

    vWord := '' ;

    If vBillionHundred >= 1 then

        vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;

        If vBillion = 0 then

            vWord := vWord || Interprt(vBillion)||' BILLION ' ;

        End if ;

    End if ;

    If vBillion >= 1 then

        vWord := vWord || Interprt(vBillion)||' BILLION ' ;

    End if ;

    If vMillionHundred >= 1 then

        vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;

        -----if million is 0 then appends the million

        If vMillion = 0 then

            vWord := vWord || Interprt(vMillion)||' MILLION ' ;

        End if ;

    End if ;

    If vMillion >= 1 then

        vWord := vWord || Interprt(vMillion)||' MILLION ' ;

    End if ;

    ---------prints the hundred thousand

    If vHundredThousand >= 1 then

        vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;

        -----if thousand is 0 then appends the thousand

        If vThousand = 0 then

            vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

        End if ;

        ---------

    End if ;

    If vThousand >= 1 then

        vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

    End if ;

    If vHundred >= 1 then

        vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;

    End if ;

    Begin

        SELECT a.curr_code INTO vCurrCode

          FROM Cm004_comp_mst a

         WHERE a.comp_code = pCompCode ;

    Exception

        When Others Then

            vCurrCode := null;

    End;

    If vCurrCode is not null Then

        Begin

            SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit

              FROM Cm001_currency_mst a

             WHERE a.curr_code = vCurrCode ;

        Exception

            When Others Then

                vCurrMainUnit := null ;

                vCurrSubUnit := null ;

        End ;

    End If;

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'DOLLARS') ;

    If vDecimal > 0 then

        If vWord is not null then

            vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;

        Else

            vWord :=  Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;

        End if;

    Else

        If vWord is not null Then

            vWord := vWord || ' ONLY' ;

        End If;

    End if ;

    Return (vWord);

Exception

    When Value_Error then

        return null ;

        

        

   /* vWord                   Varchar2(100) ;

    vAmount                 Number(13,2) ;

    vCrore                  Number(10) Default 0 ;

    vLakhs                  Number(10) Default 0 ;

    vThousand               Number(10) Default 0 ;

    vHundred                Number(10) Default 0 ;

    vOnes                   Number(2)  Default 0 ;

    vDecimal                Number(2)  Default 0 ;

    vCurrCode               Cm004_comp_mst.curr_code%type ;

    vCurrSubUnit            Cm001_currency_mst.subunit%type ;

    vCurrMainUnit           Cm001_currency_mst.mainunit%type ;

BEGIN

    vAmount := Abs(pAmount) ;

    Select Floor(vAmount/10000000) Into vCrore From Dual ;

    If vCrore >= 1 then

        vAmount := vAmount-(vCrore*10000000) ;

    End if;

    Select Floor(vAmount/100000) Into vLakhs From Dual ;

    If vLakhs >= 1 then

        vAmount := vAmount-(vLakhs*100000) ;

    End if;

    Select Floor(vAmount/1000) Into vThousand From Dual ;

    If vThousand >= 1 then

        vAmount := vAmount-(vThousand*1000) ;

    End if;

    Select Floor(vAmount/100) Into vHundred From Dual ;

    If vHundred >= 1 then

        vAmount := vAmount-(vHundred*100) ;

    End if;

    Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;

    vOnes := vAmount - (vDecimal/100) ;

    vWord := '' ;

    If vCrore >= 1 then

        vWord := vWord || Interprt(vCrore)||' Crores ' ;

    End if ;

    If vLakhs >= 1 then

        vWord := vWord || Interprt(vLakhs)||' Lakhs ' ;

    End if ;

    If vThousand >= 1 then

        vWord := vWord || Interprt(vThousand)||' Thousand ' ;

    End if ;

    If vHundred >= 1 then

        vWord := vWord || Interprt(vHundred)||' Hundred ' ;

    End if ;

    Begin

        SELECT a.curr_code INTO vCurrCode

          FROM Cm004_comp_mst a

         WHERE a.comp_code = pCompCode ;

    Exception

        When Others Then

            vCurrCode := null;

    End;

    If vCurrCode is not null Then

        Begin

            SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit

              FROM Cm001_currency_mst a

             WHERE a.curr_code = vCurrCode ;

        Exception

            When Others Then

                vCurrMainUnit := null ;

                vCurrSubUnit := null ;

        End ;

    End If;

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'Rupees') ;

    If vDecimal > 0 then

        vWord := vWord || ' And ' || Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'Paise') || ' Only' ;

    Else

        If vWord is not null Then

            vWord := vWord || ' ONLY' ;

        End If;

    End if ;

    Return(vWord);

Exception

    When Value_Error then

        return null ;*/

END;

Function Interprt(pAmount Number) return Varchar2 is

    vText        Varchar2(1000);

BEGIN

    vText := '' ;

    If pAmount > 20 then

        vText := NumberText(trunc(pAmount,-1)) ;

        vText := vText || ' ' || NumberText(pAmount - trunc(pAmount,-1)) ;

    Else

        vText := NumberText(pAmount) ;

    End if ;

    Return vText ;

END;

Function Numbertext(pAmount Number) return Varchar2 IS

    vText Varchar2(100) ;

BEGIN

   Select Decode(pAmount,

   1, 'ONE',                   

   2, 'TWO',

   3, 'THREE',

   4, 'FOUR',

   5, 'FIVE',

   6, 'SIX',

   7, 'SEVEN',

   8, 'EIGHT',

   9, 'NINE',

   10,'TEN',

   11,'ELEVEN',

   12,'TWELVE',

   13,'THIRTEEN',

   14,'FOURTEEN',

   15,'FIFTEEN',

   16,'SIXTEEN',

   17,'SEVENTEEN',

   18,'EIGHTEEN',

   19,'NINETEEN',

   20,'TWENTY',

   30,'THIRTY',

   40,'FORTY',

   50,'FIFTY',

   60,'SIXTY',

   70,'SEVENTY',

   80,'EIGHTY',

   90,'NINETY',100,'HUNDRED' ) Into vText From Dual ;

   Return vText ;

END;

End;

How to call webservice from PLSQL Procedure

 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;

dba scheduler jobs

begin

dbms_scheduler.create_job (

   job_name           =>  'Pos_acct_prod_dtl_vu_mv',

   job_type           =>  'PLSQL_BLOCK',

   job_action         =>  'Begin DBMS_MVIEW.REFRESH(''schemaname.Pos_acct_prod_dtl_vu_mv'',atomic_refresh=>false); End;',

   start_date         =>  '14-feb-2022 09:00:00 AM',

   repeat_interval    =>  'FREQ=DAILY;INTERVAL=30',

   enabled            =>  TRUE);

END;

/

every 30 min

begin

dbms_scheduler.create_job (

   job_name           =>  'Pos_acct_prod_dtl_vu_mv',

   job_type           =>  'PLSQL_BLOCK',

   job_action         =>  'Begin DBMS_MVIEW.REFRESH(''schema_name.Pos_acct_prod_dtl_vu_mv'',atomic_refresh=>false); End;',

   start_date         =>  '14-feb-2022 09:07:00 AM',

   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=30',

   enabled            =>  TRUE);

END;

/

-- daily 3AM job

begin
dbms_scheduler.create_job (
   job_name           =>  'run_load_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'system.load_sales',
   start_date         =>  '01-MAR-2010 03:00:00 AM',
   repeat_interval    =>  'FREQ=DAILY',
   enabled            =>  TRUE);
END;

--- 9pm 

Begin

DBMS_SCHEDULER.drop_job (job_name => 'SEND_PRICE_ALERT_9PM');   

End;  

/

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'SEND_PRICE_ALERT_9PM',

job_type => 'PLSQL_BLOCK',

job_action => 'Begin automail_price_alert_pkg.JREP_SEND_PRICE_ALERT_9PM(''55'',trunc(sysdate)); 

                     automail_price_alert_pkg.JREP_SEND_PRICE_ALERT_ALL_UNITS_9PM(''55'',trunc(sysdate));

         END;',

start_date => '15-JUL-2022 09:00:00 PM',

repeat_interval => 'FREQ=DAILY',

end_date => NULL,

enabled => TRUE,

comments => '');

END;    

----------week---

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'AUTO_POCOSTPIRCE_REP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SEND_PO_COSTPIRCE_CHANGE_EXCEL; end;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY;byday=MON;BYHOUR=6;byminute=00;bysecond=0;',
enabled => TRUE);
END;

/

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'auto_stktrf_req_daily_4pm_octdec22_1',

job_type => 'PLSQL_BLOCK',

job_action => 'begin

               auto_stktrf_req_daily_4pm_octdec22;

                end;',

start_date => '04-OCT-2022 04:00:00 PM',

repeat_interval => 'FREQ=DAILY',

end_date => '31-DEC-2022 06:00:00 PM',

enabled => TRUE,

comments => '');

END;  

/

------ to change time only--

begin

dbms_scheduler.set_attribute (

name               =>  'AUTO_STKTRF_REQ_DAILY_JOB',

attribute          =>  'repeat_interval',

value              =>  'freq=daily; byhour=22');

end;

/

exec dbms_scheduler.enable('SEND_PRICE_ALERT_4PM');

unique constraint on a column with duplicate values

 CREATE INDEX PIPURINV_COMPSUPINVNO_IN ON oa_jk_purinv.pi_pur_invoice_hdr ( comp_code,sup_code,purinv_no)

/

ALTER TABLE oa_jk_purinv.pi_pur_invoice_hdr

ADD CONSTRAINT PIPURINV_COMPSUPINVNO_UK UNIQUE  ( comp_code,sup_code,purinv_no) ENABLE NOVALIDATE 

one time dba scheduler job creation

 PROCEDURE proc_oo_mst_xml_export_cntrl(p_rowid varchar2)

as

v_cnt number;

begin

select count(1) into v_cnt from user_scheduler_running_jobs;

If nvl(v_cnt,0) < 50 then

del_xml_oo_mst(p_rowid);

dbms_scheduler.create_job

   (

     job_name      =>  'OTJ_REQ_'||to_char(SYSTIMESTAMP,'yyyyMMddhh24missFF'),

     job_type      =>  'PLSQL_BLOCK',

     job_action    =>  'begin proc_oo_mst_xml_export('''||p_rowid||'''); end;',

     start_date    =>   sysdate,

     enabled       =>  TRUE,

     auto_drop     =>  TRUE,

     comments      =>  'one-time job');

     commit;

End if;

end;

num2word

 PACKAGE BODY pkg_number2word is

Function Num2Word_million(pCompCode varchar2, pAmount Number) return Varchar2 is

    vWord                   Varchar2(200) ;

    vAmount                 Number(15,2) ;

    vBillionHundred         Number(15) Default 0 ;

    vBillion                Number(15) Default 0 ;

    vMillionHundred         Number(15) Default 0 ;

    vMillion                Number(10) Default 0 ;

    vHundredThousand        Number(12) Default 0 ;

    vThousand               Number(10) Default 0 ;

    vHundred                Number(10) Default 0 ;

    vOnes                   Number(2)  Default 0 ;

    vDecimal                Number(2)  Default 0 ;

    vCurrCode               Cm004_comp_mst.curr_code%type ;

    vCurrSubUnit            Cm001_currency_mst.subunit%type ;

    vCurrMainUnit           Cm001_currency_mst.mainunit%type ;

BEGIN

    vAmount := Abs(pAmount) ;

    Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;

    If vBillionHundred >= 1 then

        vAmount := vAmount-(vBillionHundred*100000000000) ;

    End if;

    Select Floor(vAmount/1000000000) Into vBillion From Dual ;

    If vBillion >= 1 then

        vAmount := vAmount-(vBillion*1000000000) ;

    End if;

    Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;

    If vMillionHundred >= 1 then

        vAmount := vAmount-(vMillionHundred*100000000) ;

    End if;

    Select Floor(vAmount/1000000) Into vMillion From Dual ;

    If vMillion >= 1 then

        vAmount := vAmount-(vMillion*1000000) ;

    End if;

    Select Floor(vAmount/100000) Into vHundredThousand From Dual ;

    If vHundredThousand >= 1 then

        vAmount := vAmount-(vHundredThousand*100000) ;

    End if;

    Select Floor(vAmount/1000) Into vThousand From Dual ;

    If vThousand >= 1 then

        vAmount := vAmount-(vThousand*1000) ;

    End if;

    Select Floor(vAmount/100) Into vHundred From Dual ;

    If vHundred >= 1 then

        vAmount := vAmount-(vHundred*100) ;

    End if;

    Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;

    vOnes := vAmount - (vDecimal/100) ;

    vWord := '' ;

    If vBillionHundred >= 1 then

        vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;

        If vBillion = 0 then

            vWord := vWord || Interprt(vBillion)||' BILLION ' ;

        End if ;

    End if ;

    If vBillion >= 1 then

        vWord := vWord || Interprt(vBillion)||' BILLION ' ;

    End if ;

    If vMillionHundred >= 1 then

        vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;

        -----if million is 0 then appends the million

        If vMillion = 0 then

            vWord := vWord || Interprt(vMillion)||' MILLION ' ;

        End if ;

    End if ;

    If vMillion >= 1 then

        vWord := vWord || Interprt(vMillion)||' MILLION ' ;

    End if ;

    ---------prints the hundred thousand

    If vHundredThousand >= 1 then

        vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;

        -----if thousand is 0 then appends the thousand

        If vThousand = 0 then

            vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

        End if ;

        ---------

    End if ;

    If vThousand >= 1 then

        vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

    End if ;

    If vHundred >= 1 then

        vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;

    End if ;

    Begin

        SELECT a.curr_code INTO vCurrCode

          FROM Cm004_comp_mst a

         WHERE a.comp_code = pCompCode ;

    Exception

        When Others Then

            vCurrCode := null;

    End;

    If vCurrCode is not null Then

        Begin

            SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit

              FROM Cm001_currency_mst a

             WHERE a.curr_code = vCurrCode ;

        Exception

            When Others Then

                vCurrMainUnit := null ;

                vCurrSubUnit := null ;

        End ;

    End If;

    If pCompCode <> '004' Then

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'RUPEES') ;

    Else

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'TAKA') ;

    End If ;

    --vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'RUPEES') ;

    If vDecimal > 0 then

        If vWord is not null then

            vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;

        Else

            vWord :=  Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;

        End if;

    Else

        If vWord is not null Then

            vWord := vWord || ' ONLY' ;

        End If;

    End if ;

    Return(vWord);

Exception

    When Value_Error then

        return null ;

End;

Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 IS

    vWord                   Varchar2(100) ;

    vAmount                 Number(13,2) ;

    vCrore                  Number(10) Default 0 ;

    vLakhs                  Number(10) Default 0 ;

    vThousand               Number(10) Default 0 ;

    vHundred                Number(10) Default 0 ;

    vOnes                   Number(2)  Default 0 ;

    vDecimal                Number(2)  Default 0 ;

    vCurrCode               Cm004_comp_mst.curr_code%type ;

    vCurrSubUnit            Cm001_currency_mst.subunit%type ;

    vCurrMainUnit           Cm001_currency_mst.mainunit%type ;

BEGIN

    vAmount := Abs(pAmount) ;

    Select Floor(vAmount/10000000) Into vCrore From Dual ;

    If vCrore >= 1 then

        vAmount := vAmount-(vCrore*10000000) ;

    End if;

    Select Floor(vAmount/100000) Into vLakhs From Dual ;

    If vLakhs >= 1 then

        vAmount := vAmount-(vLakhs*100000) ;

    End if;

    Select Floor(vAmount/1000) Into vThousand From Dual ;

    If vThousand >= 1 then

        vAmount := vAmount-(vThousand*1000) ;

    End if;

    Select Floor(vAmount/100) Into vHundred From Dual ;

    If vHundred >= 1 then

        vAmount := vAmount-(vHundred*100) ;

    End if;

    Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;

    vOnes := vAmount - (vDecimal/100) ;

    vWord := '' ;

    If vCrore >= 1 then

        vWord := vWord || Interprt(vCrore)||' CRORES ' ;

    End if ;

    If vLakhs >= 1 then

        vWord := vWord || Interprt(vLakhs)||' LAKHS ' ;

    End if ;

    If vThousand >= 1 then

        vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;

    End if ;

    If vHundred >= 1 then

        vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;

    End if ;

    Begin

        SELECT a.curr_code INTO vCurrCode

          FROM Cm004_comp_mst a

         WHERE a.comp_code = pCompCode ;

    Exception

        When Others Then

            vCurrCode := null;

    End;

    If vCurrCode is not null Then

        Begin

            SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit

              FROM Cm001_currency_mst a

             WHERE a.curr_code = vCurrCode ;

        Exception

            When Others Then

                vCurrMainUnit := null ;

                vCurrSubUnit := null ;

        End ;

    End If;

    If pCompCode <> '004' Then

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'RUPEES') ;

    Else

    vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'TAKA') ;

    End If ;

    If vDecimal > 0 then

        vWord := vWord || ' And ' || Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'Paise') || ' ONLY' ;

    Else

        If vWord is not null Then

            vWord := vWord || ' ONLY' ;

        End If;

    End if ;

    Return(vWord);

Exception

    When Value_Error then

        return null ;

END;

Function Interprt(pAmount Number) return Varchar2 is

    vText        Varchar2(1000);

BEGIN

    vText := '' ;

    If pAmount > 20 then

        vText := NumberText(trunc(pAmount,-1)) ;

        vText := vText || ' ' || NumberText(pAmount - trunc(pAmount,-1)) ;

    Else

        vText := NumberText(pAmount) ;

    End if ;

    Return vText ;

END;

Function Numbertext(pAmount Number) return Varchar2 IS

    vText Varchar2(100) ;

BEGIN

   Select Decode(pAmount, 1, 'ONE',

                        2, 'TWO',

   3, 'THREE',

   4, 'FOUR',

   5, 'FIVE',

   6, 'SIX',

   7, 'SEVEN',

   8, 'EIGHT',

   9, 'NINE',

   10,'TEN',

   11,'ELEVEN',

   12,'TWELVE',

   13,'THIRTEEN',

   14,'FOURTEEN',

   15,'FIFTEEN',

   16,'SIXTEEN',

   17,'SEVENTEEN',

   18,'EIGHTEEN',

   19,'NINETEEN',

   20,'TWENTY',

   30,'THIRTY',

   40,'FORTY',

   50,'FIFTY',

   60,'SIXTY',

   70,'SEVENTY',

   80,'EIGHTY',

   90,'NINETY',100,'HUNDRED' ) Into vText From Dual ;

   Return vText ;

END;

End;

session monitor

 select * from locked_objects_info_vu where (owner like 'OA_CH_%' OR owner like 'OA_VIS_%') 

--and blocking_session is not null

order by object_name, sid_serial#

/

select * from blocked_objects_hist

where trunc(crtd_dt)=trunc(sysdate)

/

select to_char(logon_time, 'DD-MON-YYYY HH24:MI:SS') from v$session where sid||','||serial# = '1905,30488'

/

alter system kill session '1905,30488'

/

/

SELECT * FROM V$SQL 

WHERE SQL_ID = '6t4ds9ggj005u'

/

SELECT TRUNC(CREATION_DATE), COUNT(*)  FROM PS_TXN GROUP BY TRUNC(CREATION_DATE)

/

sELECT * FROM PS_TXN

-- check resources

/

select * from v$resource_limit

/

select to_char(crtd_dt,'yyyymmdd hh24:mi:ss') dtime, current_utilization, max_utilization, limit_value  

from v$resource_limit_test 

where resource_name ='processes'

and crtd_dt>trunc(sysdate)

order by current_utilization desc,crtd_dt desc

/

-- Check TOP_SESSIONS by CPU Usage

Select * from TOP_SESSIONS

WHERE username like 'OA_%'

and status ='ACTIVE'

--and sql_id is not null

/

-- Check for BLOCKING_SESSION

select * from cpu_usage_vu

/

select * from locked_objects_info_vu

--where BLOCKING_SESSION is not null

order by SECONDS_IN_WAIT desc

/

select * from v$session 

where status='ACTIVE'

/

-- KILL SESSION

--alter system kill session ' ' immediate

--/

-- Check TOP_SQLS BIG per execution time

SELECT * FROM TOP_SQLS

WHERE  MODULE like 'JDBC%'  --w3wp

AND per_exec > 3  -- >2SECS

--and LAST_LOAD_TIME>trunc(sysdate)

ORDER BY per_exec  desc  --executions

/

-- Check TOP_SQLS by execution time

SELECT * FROM TOP_SQLS

WHERE  MODULE like 'JDBC%'

AND per_exec > 0.5  -- >2SECS

--and sql_text like '%FIRST_ROWS%'

--and executions>9

ORDER BY executions  desc  --

/

select * from dba_constraints

where constraint_name ='SYS_C0025355'

/

select * from DBA_CONS_COLUMNS

where constraint_name ='SYS_C0025355'

/

SELECT  sql_fulltext FROM v$sqlarea

WHERE sql_id ='g6px76dmjv1jy'

/

select * from dba_hist_sqltext

/

-- accessed between a given time

select function_desc,Count(*) 

from FUNCTION_HIST f, log_hist l

where f.log_id=l.log_id

and l.accessed_link like '%7011'

--and l.accessed_link like '%7013'

--and to_date(Substr(start_time,1,11),'dd-Mon-yyyy')=trunc(sysdate)

and to_date(start_time,'dd-Mon-yyyy hh24:mi;ss')> sysdate-5/1440 --  5 min

--and to_date(start_time,'dd-Mon-yyyy hh24:mi;ss') between to_date('20-jul-2020 18:30:00','dd-mon-yyyy hh24:mi:ss') and to_date('20-jul-2020 19:20:00','dd-mon-yyyy hh24:mi:ss')

group by function_id,function_desc

order by 2 desc

/

select to_char(sysdate-5/1440,'dd-mon-yyyy hh24:mi:ss') dt from dual

/

-- total active users connected on each port

select accessed_link,count(*) 

from (

select distinct user_id,accessed_link from log_hist

where to_date(log_in_time,'dd-Mon-yyyy hh24:mi:ss') >= trunc(sysdate)

and user_id>0

and log_out_time is null  -- enable for today active users

)

group by accessed_link

order by 2 desc

/

-- pga

select name, value/1024/1024 in_mb from v$pgastat

where name in (

'aggregate PGA auto target',

'maximum PGA allocated',    

'total PGA allocated',      

'total PGA inuse',          

'total freeable PGA memory'

)

/

-- Temporary tablespace free

SELECT TABLESPACE_NAME,  TABLESPACE_SIZE/1024/1024/1024 "TBS Size(GB)" , 

ALLOCATED_SPACE/1024/1024/1024"Used Size(GB)",FREE_SPACE/1024/1024/1024" Free Size(GB)" 

FROM dba_temp_free_space

/

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,

(used_blocks*8)/1024/1024  UsedSpaceInGB,

(total_blocks*8)/1024/1024  TotalSpaceInGB

from v$sort_segment where tablespace_name like '%TEMP%'

/

-- undo tablespace free  --  takes around 29/50 secs

select a.tablespace_name, SIZEGB, USAGEGB, (SIZEGB - USAGEGB) FREEGB

from (select sum(bytes) / 1024 / 1024 /1024 SIZEGB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 /1024 USAGEGB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name

/

-- total memory sizes

SELECT  component, current_size, min_size, max_size

FROM    v$memory_dynamic_components

WHERE   current_size != 0

/

-- Active sessions PGA used

select s.inst_id, s.sid,s.username,s.status, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM, s.sql_id

from gv$session s

, gv$process p

Where s.paddr = p.addr

and s.inst_id = p.inst_id

and s.status ='ACTIVE'

and PGA_USED_MEM/1024/1024 > 20  -- pga_used memory over 20mb

order by PGA_USED_MEM desc

/

-- session wise pga used

select total_size/1024/1024 PGA_GB,awr_flush_emergency_count from v$ash_info;

/

-- uga & Pga Memmory

select name, sum(value/1024/1024) "Value - MB"

from v$statname n,

v$session s,

v$sesstat t

where s.sid=t.sid

and n.statistic# = t.statistic#

and s.type = 'USER'

and s.username is not NULL

and n.name in ('session pga memory', 'session pga memory max',

'session uga memory', 'session uga memory max')

group by name

/

/

select * from locked_objects_info_vu

/

-- pga advisor

select pga_target_for_estimate, pga_target_factor, estd_time

from v$pga_target_advice

/

-- SID wise PGA used

SELECT SID,SUM(VAL) VALINMB FROM (

select sid,name,value/1024/1024 VAL

from  v$statname n,v$sesstat s

where n.STATISTIC# = s.STATISTIC# and

name like 'session%memory%max')

GROUP BY SID

order by 2 desc

/