create or replace package body pkg_nkw_std timestamp '2006-07-07:10:10:10' is ------------------------------------------------------------------ -- 2006 DataPRO Developers - m@urelio ------------------------------------------------------------------ -- Version: 2.0.2 ------------------------------------------------------------------ -- Collections, Records, Variables, Constants, Exceptions, Cursors ------------------------------------------------------------------ gv_split pkg_nkw_env.gv_char%TYPE; type tab_split is table of gv_split%TYPE index by binary_integer; gt_split tab_split; gv_bin_indx constant varchar2(2) := '01'; gv_bin_base constant integer := length(gv_bin_indx); gv_bin_exp constant integer := 32; gv_oct_indx constant varchar2(8) := '01234567'; gv_oct_base constant integer := length(gv_oct_indx); gv_oct_exp constant integer := 10; gv_hex_indx constant varchar2(16) := '0123456789ABCDEF'; gv_hex_base constant integer := length(gv_hex_indx); gv_hex_exp constant integer := 8; gv_asc_indx constant varchar2(32) := '0123456789ABCDEFGHJKLNPQRSTUVXYZ'; gv_asc_base constant integer := length(gv_asc_indx); gv_asc_exp constant integer := 32; ------------------------------------------------------------------ ----------------------- Private Session -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ ------------------------ Public Session -------------------------- ------------------------------------------------------------------ -- ON_RANGE ------------------------------------------------------------------ function on_range(fv_date in date, fv_low in date, fv_high in date) return integer is retval integer; begin if (fv_date between fv_low and fv_high) then retval := 1; else retval := 0; end if; return retval; exception when others then raise; end on_range; ------------------------------------------------------------------ function on_range(fv_number in number, fv_low in number, fv_high in number) return integer is retval integer; begin if (fv_number between fv_low and fv_high) then retval := 1; else retval := 0; end if; return retval; exception when others then raise; end on_range; ------------------------------------------------------------------ function on_range(fv_string in varchar2, fv_low in varchar2, fv_high in varchar2) return integer is retval integer; begin if (fv_string between fv_low and fv_high) then retval := 1; else retval := 0; end if; return retval; exception when others then raise; end on_range; ------------------------------------------------------------------ -- GET_MINUTES ------------------------------------------------------------------ function get_minutes(fv_minutes in number) return number is retval number; begin if (fv_minutes is not null) then retval := (trunc(fv_minutes, -2) * 0.60) + (fv_minutes - trunc(fv_minutes, -2)); end if; return retval; exception when others then raise; end get_minutes; ------------------------------------------------------------------ -- GET_HOURS ------------------------------------------------------------------ function get_hours(fv_hours in number) return number is retval number; begin if (fv_hours is not null) then retval := (trunc(fv_hours / 60) * 100) + (fv_hours - (trunc(fv_hours / 60) * 60)); end if; return retval; exception when others then raise; end get_hours; ------------------------------------------------------------------ -- FMT_INPUT ------------------------------------------------------------------ function fmt_input(fv_minutes in number) return varchar2 is retval varchar2(22) := ltrim(to_char(nvl(fv_minutes,0), '0000')); begin retval := substr(retval, 1, 2) || ':' || substr(retval, 3, 2); return retval; exception when others then raise; end fmt_input; ------------------------------------------------------------------ -- FMT_HOURS ------------------------------------------------------------------ function fmt_hours(fv_minutes in number) return varchar2 is retval varchar2(32); lv_hours number; lv_minutes number; begin lv_hours := trunc(nvl(fv_minutes,0) / 60); lv_minutes := nvl(fv_minutes,0) - (trunc(nvl(fv_minutes,0) / 60) * 60); retval := ltrim(to_char(nvl(lv_hours,0),'00')) || ':' || ltrim(to_char(nvl(lv_minutes,0),'00')); return retval; exception when others then raise; end fmt_hours; ------------------------------------------------------------------ -- FMT_SUM_HOURS ------------------------------------------------------------------ function fmt_sum_hours(fv_minutes in number) return varchar2 is retval varchar2(32); lv_hours number; lv_minutes number; begin lv_hours := trunc(nvl(fv_minutes,0) / 60); lv_minutes := nvl(fv_minutes,0) - (trunc(nvl(fv_minutes,0) / 60) * 60); retval := ltrim(to_char(nvl(lv_hours,0),'9900')) || ':' || ltrim(to_char(nvl(lv_minutes,0),'00')); return retval; exception when others then raise; end fmt_sum_hours; ------------------------------------------------------------------ -- IS_TIME ------------------------------------------------------------------ function is_time(fv_time in number) return boolean is retval boolean := (TRUE); begin if (fv_time is not null) then retval := (fv_time between 0 and 2400 and fv_time - trunc(fv_time, -2) between 0 and 59); end if; return retval; exception when others then raise; end is_time; ------------------------------------------------------------------ -- IS_DATE ------------------------------------------------------------------ function is_date(fv_string in varchar2 := null, fv_format in varchar2 := null) return boolean is lv_date date; begin lv_date := to_date(fv_string, fv_format); return (TRUE); exception when others then return (FALSE); end is_date; ------------------------------------------------------------------ -- IS_NUMBER ------------------------------------------------------------------ function is_number(fv_string in varchar2 := null) return boolean is lv_number number; begin lv_number := to_number(fv_string); return (TRUE); exception when others then return (FALSE); end is_number; ------------------------------------------------------------------ -- IS_INTEGER ------------------------------------------------------------------ function is_integer(fv_string in varchar2 := null) return boolean is lv_number number; begin lv_number := to_number(fv_string); if (lv_number <> trunc(lv_number)) then return (FALSE); end if; return (TRUE); exception when others then return (FALSE); end is_integer; ------------------------------------------------------------------ -- IS_UF ------------------------------------------------------------------ function is_uf(fv_uf in varchar2 := null) return boolean is lv_ufs varchar2(100) := 'AC|AL|AM|AP|BA|CE|DF|ES|GO|MA|MG|MS|MT|PA|PB|PE|PI|PR|RJ|RN|RS|RO|RR|SE|SC|SP|TO'; retval boolean := (instr(lv_ufs, fv_uf) > 0); begin return retval; exception when others then return (FALSE); end is_uf; ------------------------------------------------------------------ -- IS_PASSWD ------------------------------------------------------------------ function is_passwd(fv_passwd in varchar2 := null) return boolean is lv_chars varchar2(512) := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; lv_gchar varchar(1) := substr(lv_chars, 1, 1); lv_bchars varchar2(512) := replace(translate(fv_passwd, lv_chars, lv_gchar), lv_gchar, ''); lv_bchar varchar(1) := substr(lv_bchars, 1, 1); begin if (lv_bchars is null) then return (TRUE); else return (FALSE); end if; exception when others then return (FALSE); end is_passwd; ------------------------------------------------------- -- IS_CNPJ ------------------------------------------------------- function is_cnpj(fv_cnpj in number) return boolean is string varchar2(14); sumd01 number; sumd02 number; rest01 number; rest02 number; retval boolean := (FALSE); begin if (length(to_char(fv_cnpj)) > 14) then retval := (FALSE); else string := ltrim(to_char(nvl(fv_cnpj,0),'00000000000000')); for i in 1 .. (length(string) - 2) loop if (i <= 4) then sumd01 := nvl(sumd01,0) + ((6 - i) * substr(string,i,1)); else sumd01 := nvl(sumd01,0) + ((14 - i) * substr(string,i,1)); end if; end loop; rest01 := (nvl(sumd01,0) - (trunc(sumd01 / 11) * 11)); if (rest01 in (0,1)) then rest01 := 0; else rest01 := (11 - rest01); end if; for i in 1 .. (length(string) - 2) loop if (i <= 5) then sumd02 := nvl(sumd02,0) + ((7 - i) * substr(string,i,1)); else sumd02 := nvl(sumd02,0) + ((15 - i) * substr(string,i,1)); end if; end loop; sumd02 := (nvl(sumd02,0) + (2 * nvl(rest01,0))); rest02 := (nvl(sumd02,0) - (trunc(sumd02 / 11) * 11)); if (rest02 in (0,1)) then rest02 := 0; else rest02 := (11 - rest02); end if; if (rest02 <> substr(string,14,1)) or (rest01 <> substr(string,13,1)) then retval := (FALSE); else retval := (TRUE); end if; end if; return retval; exception when others then return (FALSE); end is_cnpj; ------------------------------------------------------- -- IS_CPF ------------------------------------------------------- function is_cpf(fv_cpf in number) return boolean is string varchar2(11); sumd01 number; sumd02 number; rest01 number; rest02 number; retval boolean := (FALSE); begin if (length(to_char(fv_cpf)) > 11) then retval := (FALSE); else string := ltrim(to_char(nvl(fv_cpf,0),'00000000000')); for i in 1 .. (length(string) - 2) loop sumd01 := nvl(sumd01,0) + ((11 - i) * substr(string,i,1)); end loop; rest01 := (nvl(sumd01,0) - (trunc(sumd01 / 11) * 11)); if (rest01 in (0,1)) then rest01 := 0; else rest01 := (11 - rest01); end if; for i in 1 .. (length(string) - 2) loop sumd02 := nvl(sumd02,0) + ((12 - i) * substr(string,i,1)); end loop; sumd02 := (nvl(sumd02,0) + (2 * nvl(rest01,0))); rest02 := (nvl(sumd02,0) - (trunc(sumd02 / 11) * 11)); if (rest02 in (0,1)) then rest02 := 0; else rest02 := (11 - rest02); end if; if (rest02 <> substr(string,11,1)) or (rest01 <> substr(string,10,1)) then retval := (FALSE); else retval := (TRUE); end if; end if; return retval; exception when others then return (FALSE); end is_cpf; ------------------------------------------------------------------ -- IS_CGC ------------------------------------------------------------------ function is_cgc(fv_num in number, fv_pessoa in varchar2) return boolean is retval boolean := (FALSE); begin if (upper(fv_pessoa) = 'F') then retval := is_cpf(fv_num); else retval := is_cnpj(fv_num); end if; return retval; exception when others then return (FALSE); end is_cgc; ------------------------------------------------------------------ -- TO_INTEGER ------------------------------------------------------------------ function to_integer(fv_string in varchar2) return number is lv_mask varchar2(255); begin if (fv_string is not null) then for i in 1 .. length(fv_string) loop if (ascii(substr(fv_string, i, 1)) between 48 and 57) then lv_mask := lv_mask || substr(fv_string, i, 1); end if; end loop; end if; return to_number(lv_mask); exception when others then return (NULL); end to_integer; ------------------------------------------------------------------ -- TO_FEIRA ------------------------------------------------------------------ function to_feira(fv_date in date) return varchar2 is retval varchar2(255); begin if (fv_date is not null) then retval := initcap(ltrim(rtrim(to_char(fv_date, 'day')))) || ', ' || to_char(fv_date, 'dd') || ' de ' || initcap(ltrim(rtrim(to_char(fv_date, 'month')))) || ' de ' || to_char(fv_date, 'yyyy'); end if; return retval; exception when others then return (NULL); end to_feira; ------------------------------------------------------------------ -- CIAO ------------------------------------------------------------------ function ciao return varchar2 is lv_when number(2) := to_char(sysdate,'hh24'); retval varchar2(20); begin if (lv_when < 12) then retval := 'Bom Dia'; elsif (lv_when < 19) then retval := 'Boa Tarde'; else retval := 'Boa Noite'; end if; return retval; exception when others then return (NULL); end ciao; ------------------------------------------------------------------ -- PRENOM ------------------------------------------------------------------ function prenom(fv_name in varchar2) return varchar2 is retval varchar2(100); begin retval := nvl(substr(fv_name, 1, (instr(fv_name, ' ', 1) - 1)), fv_name); return retval; exception when others then return (NULL); end prenom; ------------------------------------------------------- -- FMT_CGC ------------------------------------------------------- function fmt_cgc(fv_cgc in number, fv_pessoa in varchar2 default 'F') return varchar2 is retval varchar2(20); begin if (fv_cgc is not null) then if (fv_pessoa = 'F') then retval := substr(ltrim(to_char(fv_cgc,'00000000000')),1,3) || '.' || substr(ltrim(to_char(fv_cgc,'00000000000')),4,3) || '.' || substr(ltrim(to_char(fv_cgc,'00000000000')),7,3) || '-' || substr(ltrim(to_char(fv_cgc,'00000000000')),10,2); elsif (fv_pessoa = 'J') then retval := substr(ltrim(to_char(fv_cgc,'00000000000000')),1,2) || '.' || substr(ltrim(to_char(fv_cgc,'00000000000000')),3,3) || '.' || substr(ltrim(to_char(fv_cgc,'00000000000000')),6,3) || '/' || substr(ltrim(to_char(fv_cgc,'00000000000000')),9,4) || '-' || substr(ltrim(to_char(fv_cgc,'00000000000000')),13,2); end if; end if; return retval; exception when others then raise; end fmt_cgc; ------------------------------------------------------- -- FMT_CNPJ ------------------------------------------------------- function fmt_cnpj(fv_cnpj in number) return varchar2 is retval varchar2(80); begin retval := fmt_cgc(fv_cnpj, 'J'); return retval; exception when others then raise; end fmt_cnpj; ------------------------------------------------------- -- FMT_CPF ------------------------------------------------------- function fmt_cpf(fv_cpf in number) return varchar2 is retval varchar2(80); begin retval := fmt_cgc(fv_cpf, 'F'); return retval; exception when others then raise; end fmt_cpf; ------------------------------------------------------- -- FMT_CEP ------------------------------------------------------- function fmt_cep(fv_cep in number) return varchar2 is retval varchar2(20); begin if (fv_cep is not null) then retval := substr(ltrim(to_char(fv_cep,'00000000')),1,5) || '-' || substr(ltrim(to_char(fv_cep,'00000000')),6,3); end if; return retval; exception when others then raise; end fmt_cep; ------------------------------------------------------- -- FMT_LIKE ------------------------------------------------------- function fmt_like(fv_char in varchar2) return varchar2 is retval varchar2(1024); begin if (fv_char is not null) then retval := rtrim(ltrim(replace(replace(fv_char, '%'), '_'))); while (instr(retval, ' ', 1) <> 0) loop retval := replace(retval, ' ', ' '); end loop; retval := translate(retval, ' ', '%'); retval := retval || '%'; end if; return retval; exception when others then raise; end fmt_like; ------------------------------------------------------- -- FMT_TEXT ------------------------------------------------------- function fmt_text(fv_text in varchar2) return varchar2 is retval varchar2(32767); lv_chars constant varchar2(512) := 'abcdefghijklmnopqrstuvwxyz' || 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '~!@#$%^&*()_+-=/|\?,[.]{;}<:>0123456789' || 'ÀÁÂÃÄàáâãäÈÉÊËèéêëÌÍÎÏìíîïÒÓÔÕÖòóôõöÙÚÛÜùúûü' || 'ÇçÑñªº®" ' || chr(10); lv_gchar char(1) := substr(lv_chars, 1, 1); lv_bchars varchar2(512) := replace(translate(fv_text, lv_chars, lv_gchar), lv_gchar, ''); lv_bchar char(1) := substr(lv_bchars, 1, 1); begin if (lv_bchars is null) then retval := fv_text; else retval := replace(translate(fv_text, lv_bchars, rpad(lv_bchar, length(lv_bchars), lv_bchar)), lv_bchar, ''); end if; return retval; exception when others then raise; end fmt_text; ------------------------------------------------------- -- FMT_CHAR ------------------------------------------------------- function fmt_char(fv_char in varchar2, fv_length in number := 32767) return varchar2 is retval varchar2(32767); begin if (fv_char is not null) then retval := fmt_text(fv_char); while (instr(retval, ' ', 1) <> 0) loop retval := replace(retval, ' ', ' '); end loop; retval := substr(upper(rtrim(ltrim(retval))), 1, fv_length); end if; return retval; exception when others then raise; end fmt_char; ------------------------------------------------------------------ -- GET_SYSDATE ------------------------------------------------------------------ function get_sysdate return date is retval date; begin retval := sysdate; return retval; exception when others then raise; end get_sysdate; ------------------------------------------------------------------ -- SPLIT ------------------------------------------------------------------ function split(fv_string in varchar2, fv_sep in varchar2, fv_field in number) return varchar2 is lv_string varchar2(1024) := fv_string; i number := 1; begin if (lv_string is null) then return null; end if; if (gv_split = lv_string) then return gt_split(fv_field); end if; gt_split.delete; gv_split := lv_string; if (instr(lv_string, fv_sep) = 0) then lv_string := lv_string || fv_sep; end if; while (instr(lv_string, fv_sep) > 0) loop gt_split(i) := substr(lv_string, 1, greatest((instr(lv_string, fv_sep) - 1),1)); lv_string := substr(lv_string, instr(lv_string, fv_sep) + 1); i := i + 1; end loop; return gt_split(fv_field); exception when others then raise; end split; ------------------------------------------------------------------ -- DEC2BIN ------------------------------------------------------------------ function dec2bin(fv_value in integer) return integer is retval varchar2(32); lv_value integer := abs(fv_value); begin if (lv_value > (gv_bin_base ** gv_bin_exp)) then raise value_error; end if; loop retval := substr(gv_bin_indx, trunc(mod(lv_value, gv_bin_base)) + 1, 1) || retval; lv_value := trunc(lv_value / gv_bin_base); if (lv_value < gv_bin_base) then retval := substr(gv_bin_indx, lv_value + 1, 1) || retval; exit; end if; end loop; return to_number(retval); exception when others then raise; end dec2bin; ------------------------------------------------------------------ -- BIN2DEC ------------------------------------------------------------------ function bin2dec(fv_value in integer) return integer is retval integer := 0; lv_pos integer; begin if (fv_value < 0) then raise value_error; end if; for i in 1 .. length(fv_value) loop lv_pos := instr(gv_bin_indx, substr(fv_value, (length(fv_value) - i + 1), 1)); retval := retval + ((nvl(lv_pos,0) - 1) * (gv_bin_base ** (i - 1))); end loop; return retval; exception when others then raise; end bin2dec; ------------------------------------------------------------------ -- DEC2OCT ------------------------------------------------------------------ function dec2oct(fv_value in integer) return integer is retval varchar2(10); lv_value integer := abs(fv_value); begin if (lv_value > (gv_oct_base ** gv_oct_exp)) then raise value_error; end if; loop retval := substr(gv_oct_indx, trunc(mod(lv_value, gv_oct_base)) + 1, 1) || retval; lv_value := trunc(lv_value / gv_oct_base); if (lv_value < gv_oct_base) then retval := substr(gv_oct_indx, lv_value + 1, 1) || retval; exit; end if; end loop; return to_number(retval); exception when others then raise; end dec2oct; ------------------------------------------------------------------ -- OCT2DEC ------------------------------------------------------------------ function oct2dec(fv_value in integer) return integer is retval integer := 0; lv_pos integer; begin if (fv_value < 0) then raise value_error; end if; for i in 1 .. length(fv_value) loop lv_pos := instr(gv_oct_indx, substr(fv_value, (length(fv_value) - i + 1), 1)); retval := retval + ((nvl(lv_pos,0) - 1) * (gv_oct_base ** (i - 1))); end loop; return retval; exception when others then raise; end oct2dec; ------------------------------------------------------------------ -- DEC2HEX ------------------------------------------------------------------ function dec2hex(fv_value in integer) return varchar2 is retval varchar2(8); lv_value integer := abs(fv_value); begin if (lv_value > (gv_hex_base ** gv_hex_exp)) then raise value_error; end if; loop retval := substr(gv_hex_indx, trunc(mod(lv_value, gv_hex_base)) + 1, 1) || retval; lv_value := trunc(lv_value / gv_hex_base); if (lv_value < gv_hex_base) then retval := substr(gv_hex_indx, lv_value + 1, 1) || retval; exit; end if; end loop; return lpad(retval, gv_hex_exp, '0'); exception when others then raise; end dec2hex; ------------------------------------------------------------------ -- HEX2DEC ------------------------------------------------------------------ function hex2dec(fv_value in varchar2) return integer is retval integer := 0; lv_pos integer; begin if (nvl(length(replace(translate(fv_value, gv_hex_indx, '0'),'0','')),0) <> 0) then raise value_error; end if; for i in 1 .. length(fv_value) loop lv_pos := instr(gv_hex_indx, substr(fv_value, (length(fv_value) - i + 1), 1)); retval := retval + ((nvl(lv_pos,0) - 1) * (gv_hex_base ** (i - 1))); end loop; return retval; exception when others then raise; end hex2dec; ------------------------------------------------------------------ -- DEC2ASC ------------------------------------------------------------------ function dec2asc(fv_value in integer) return varchar2 is retval varchar2(64); lv_value integer := abs(fv_value); begin if (lv_value > (gv_asc_base ** gv_asc_exp)) then raise value_error; end if; loop retval := substr(gv_asc_indx, trunc(mod(lv_value, gv_asc_base)) + 1, 1) || retval; lv_value := trunc(lv_value / gv_asc_base); if (lv_value < gv_asc_base) then retval := substr(gv_asc_indx, lv_value + 1, 1) || retval; exit; end if; end loop; return retval; exception when others then raise; end dec2asc; ------------------------------------------------------------------ -- ASC2DEC ------------------------------------------------------------------ function asc2dec(fv_value in varchar2) return integer is retval integer := 0; lv_pos integer; begin if (nvl(length(replace(translate(fv_value, gv_asc_indx, '0'),'0','')),0) <> 0) then raise value_error; end if; for i in 1 .. length(fv_value) loop lv_pos := instr(gv_asc_indx, substr(fv_value, (length(fv_value) - i + 1), 1)); retval := retval + ((nvl(lv_pos,0) - 1) * (gv_asc_base ** (i - 1))); end loop; return retval; exception when others then raise; end asc2dec; ------------------------------------------------------------------ -- CREATE_DV ------------------------------------------------------------------ function create_dv(fv_int in integer) return integer is retval integer; lv_mask varchar2(64); lv_sum number; lv_mod number; begin lv_mask := ltrim(to_char(nvl(fv_int,0),'00000000')); for i in 1 .. length(lv_mask) loop lv_sum := nvl(lv_sum,0) + ((10 - i) * substr(lv_mask,i,1)); end loop; lv_mod := (nvl(lv_sum,0) - (trunc(lv_sum / 11) * 11)); if (lv_mod in (0,1)) then lv_mod := 0; else lv_mod := (11 - lv_mod); end if; retval := to_number(lv_mask || lv_mod); return retval; exception when others then raise; end create_dv; ------------------------------------------------------------------ -- CHECK_DV ------------------------------------------------------------------ function check_dv(fv_int in integer) return boolean is retval boolean := (FALSE); lv_mask varchar2(64); lv_sum number; lv_mod number; begin lv_mask := ltrim(to_char(nvl(fv_int,0),'000000000')); for i in 1 .. (length(lv_mask) - 1) loop lv_sum := nvl(lv_sum,0) + ((10 - i) * substr(lv_mask,i,1)); end loop; lv_mod := (nvl(lv_sum,0) - (trunc(lv_sum / 11) * 11)); if (lv_mod in (0,1)) then lv_mod := 0; else lv_mod := (11 - lv_mod); end if; if (lv_mod = substr(lv_mask,9,1)) then retval := (TRUE); end if; return retval; exception when others then raise; end check_dv; ------------------------------------------------------------------ -- FMT_DV ------------------------------------------------------------------ function fmt_dv(fv_int in integer, fv_digits in integer := 6) return varchar2 is retval varchar2(20); lv_mask varchar2(20) := ltrim(to_char(fv_int, rpad('0', fv_digits, '0'))); begin retval := substr(lv_mask, 1, (length(lv_mask) - 1)) || '-' || substr(lv_mask, length(lv_mask), 1); return retval; exception when others then raise; end fmt_dv; end pkg_nkw_std; / show errors