Rem   NAME
Rem     mixen.sql - Data Masking utilities
Rem   DESCRIPTION
Rem     The miXen package contains several data masking utilities.
Rem     It uses base tables to generate "intelligent" data sets.
Rem     miXen functions can be invoked by Data Pump.
Rem     miXen can generate or check: names, cities, SSN, IBAN, SIN, CF, ...
Rem   NOTES
Rem     Copyright (c) 2017 by mail AT meo.bogliolo.name. All Rights Reserved.
Rem
Rem   VERSION
Rem     v.0.0.1
Rem   MODIFIED     (MM/DD/YY)
Rem     fbot       01/10/17  - Created.
Rem     meob       10/10/17  - Full function list.
Rem

/*
  Function prefix describes how the output value is generated:
   - rnd: Random Values (sometimes based on lists)
   - msk: Masked Values
   - add: The output values is a combination between input and random values
   - lst: Pseudo-Random repeteable values
*/


create or replace
PACKAGE miXen AUTHID CURRENT_USER AS
   FUNCTION rnd_string (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_smart (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_char8 (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_number_1m (oldnum IN number) RETURN number;
   FUNCTION rnd_int_100 (oldnum IN number) RETURN number; 
   FUNCTION rnd_date_1970 (olddate IN date) RETURN date; 
   FUNCTION rnd_surname_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_name_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_city_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_surname_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_name_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_city_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_PIVA_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_SSN_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_SIN_ca (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_ccard_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION rnd_IBAN_it (oldstr IN varchar2) RETURN varchar2;

   FUNCTION msk_string_last3 (oldstr IN varchar2) RETURN varchar2;
   FUNCTION msk_number_last3 (oldnum IN number) RETURN number;
   FUNCTION add_number_1k (oldnum IN number) RETURN number; 
   FUNCTION add_int_100 (oldnum IN number) RETURN number; 
   FUNCTION add_date_1y (olddate IN date) RETURN date; 

   FUNCTION lst_string (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_smart (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_surname_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_name_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_city_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_surname_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_name_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION lst_city_us (oldstr IN varchar2) RETURN varchar2;

   FUNCTION bld_CF_it (surname IN varchar2, name IN varchar2, birthdate IN date, city IN varchar2) RETURN varchar2;

   FUNCTION fix_surname_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_name_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_city_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_surname_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_name_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_city_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_CF_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_PIVA_it (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_SSN_us (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_SIN_ca (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_ccard (oldstr IN varchar2) RETURN varchar2;
   FUNCTION fix_IBAN (oldstr IN varchar2) RETURN varchar2;

   FUNCTION chk_surname_it (str IN varchar2) RETURN INT;
   FUNCTION chk_name_it (str IN varchar2) RETURN INT;
   FUNCTION chk_city_it (str IN varchar2) RETURN INT;
   FUNCTION chk_surname_us (str IN varchar2) RETURN INT;
   FUNCTION chk_name_us (str IN varchar2) RETURN INT;
   FUNCTION chk_city_us (str IN varchar2) RETURN INT;  
   FUNCTION chk_CF_it (str IN VARCHAR2) RETURN INT;
   FUNCTION chk_PIVA_it (str IN VARCHAR2) RETURN INT;
   FUNCTION chk_SSN_us (str IN VARCHAR2) RETURN INT;
   FUNCTION chk_SIN_ca (str IN VARCHAR2) RETURN INT;
   FUNCTION chk_ccard (str IN VARCHAR2) RETURN INT;
   FUNCTION chk_IBAN (str IN VARCHAR2) RETURN INT;
   FUNCTION is_version (ver IN number) RETURN INT;
   FUNCTION is_number (str IN VARCHAR2) RETURN INT;
   FUNCTION is_luhn (str IN VARCHAR2) RETURN INT;
END miXen;
/