Message warn for oracle

g9g4z5bf4v

--===================================================================
-- create type
--===================================================================
create or replace type ty_str_split is table of varchar2 (4000);
/

--===================================================================
-- create function
--===================================================================
create or replace function fn_split (
p_str in varchar2,        --
p_delimiter in varchar2)  --
    return ty_str_split
is
    j int := 0;
    i int := 1;
    len int := 0;
    len1 int := 0;
    str varchar2 (4000);
    str_split ty_str_split := ty_str_split ();
begin
    len := length (p_str);
    len1 := length (p_delimiter);

    while j < len
    loop
        j := instr (p_str, p_delimiter, i);

        if j = 0
        then
            j := len;
            str := substr (p_str, i);
            str_split.extend;
            str_split (str_split.count) := str;

            if i >= len
            then
                exit;
            end if;
        else
            str := substr (p_str, i, j - i);
            i := j + len1;
            str_split.extend;
            str_split (str_split.count) := str;
        end if;
    end loop;

    return str_split;
end fn_split;
/
--================================================================
-- pr_callreasonwarn
--================================================================
create or replace procedure pr_callreasonwarn
as
v_phones_cfg               varchar(2000);
v_hour_cfg                 int;
v_hour_sendmsg_cfg         int;
v_sendmsgnum_every_cfg     int;
v_sendmsgnum_all_cfg       int;
v_sms_warn_content         varchar(4000);
v_rownum_atype             int;
v_rownum_alltype           int;
v_intsessionid             int;
v_vcid                     int;
begin
  v_vcid:=4;
  --Get config info.
   begin
      select strvalue into v_phones_cfg
      from cc_systemparam
      where strkey = 'CallReasonWarnPhones';
   -- scan cr_slavelog4sms table N hour
      select strvalue into v_hour_cfg
      from cc_systemparam
      where strkey = 'CallReasonScanHour';
  exception
          when no_data_found then
          begin
            return;
          end;  
    end;

    begin
      select strvalue into v_hour_sendmsg_cfg
      from cc_systemparam
      where strkey='SendMsgLogHourInterval';
  exception
          when no_data_found then
          begin
             v_hour_sendmsg_cfg:=24;
          end;  
    end;

    begin
      select strvalue into v_sendmsgnum_every_cfg
      from cc_systemparam
      where strkey='SendMsgCountEvery';
  exception
          when no_data_found then
          begin
             v_sendmsgnum_every_cfg:=10;
          end;  
end;

    begin
      select strvalue into v_sendmsgnum_all_cfg
      from cc_systemparam
      where strkey='SendMsgCountAll';
  exception
          when no_data_found then
          begin
             v_sendmsgnum_all_cfg:=50;
          end;  
    end;

  
  for cur_warndata in (
      select a.titleid,c.strname,b.reasonid,b.sms_warn_content
           from (           
       select  a.titleid,
                    count(1) cnt
      from cr_slavelog4sms a
      where  a.callingtime>=sysdate-v_hour_cfg/24
      and  a.callingtime<=sysdate
      group by a.titleid
      ) a,cr_title_cfg b,cc_systemdict c
      where b.titleid=c.dictid
      and c.dictid = a.titleid
  and b.sms_warn_val is not null
      and a.cnt>nvl(b.sms_warn_val,0)
  ) loop
      
   for cur_phones in(
    select *
    from table(cast(fn_split(v_phones_cfg, ',') as
            ty_str_split))  
   )   
     loop     
         --each call reason
         select count(1) into v_rownum_atype
         from cr_warn_smslog
         where send_time>=sysdate-v_hour_sendmsg_cfg/24
           and send_time<=sysdate
           and receiver=cur_phones.column_value
           and titleid=cur_warndata.titleid;
         --all call reason
         select count(1) into v_rownum_alltype
           from cr_warn_smslog
         where send_time>=sysdate-v_hour_sendmsg_cfg/24
           and send_time<=sysdate
           and receiver=cur_phones.column_value;
      
     if v_rownum_atype>= v_sendmsgnum_every_cfg then
       null;
     else
           if v_rownum_alltype >=v_sendmsgnum_all_cfg then
             null;   
           else
               v_sms_warn_content:='The '||cur_warndata.strname||' call reason exceeds the default times, the remark:'||cur_warndata.sms_warn_content;
               zxdb_kf.PR_GetSessionID('HF_FAXMISSION',v_intsessionid);
   
                insert into zxdb_kf.hf_faxmission(intsessionid, strcustomerno, strsender, strfaxnotice, intstatus, strnotice)
                   values (v_intsessionid, cur_phones.column_value, '994', '03', 0, v_sms_warn_content);
--Create log
insert into  cr_warn_smslog(  
                  vcid,
                  titleid,
                  reasonid,
                  receiver   
                 )
                values(
                  v_vcid,
                  cur_warndata.titleid,
  cur_warndata.reasonid,
  cur_phones.column_value
                );
              
           end if;
     
     end if;
     end loop;
     commit;
  end loop;   
end;
/