Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/masking/dm_fmtlib_pkgbody.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $
Rem
Rem dm_fmtlib_pkgbody.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dm_fmtlib_pkgbody.sql - Data Masking Format Library
Rem
Rem    DESCRIPTION
Rem      This file creates a package that contains UDFs and PPFs used in
Rem      Defining common data masking formats.   
Rem      Ex: Functions to generate valid credit card numbers adhering to
Rem          Luhn check.
Rem      This file in conjunction with 'dm_fmtlib.xml' forms the data masking
Rem      library. 
Rem
Rem    NOTES
Rem      Most of the 'user defined functions' defined in this package are
Rem      mainly for illustration purposes. Some of these functions needed
Rem      to be customized further to suit individual customer specific needs.  
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bkuchibh    11/04/08 - cleanup
Rem    kmckeen     10/29/08 - Update for 10.2.0.5
Rem    bkuchibh    01/09/08 - Created
Rem

GRANT EXECUTE ON DBSNMP.DM_FMTLIB TO PUBLIC;

CREATE OR REPLACE PACKAGE BODY DBSNMP.DM_FMTLIB
IS
-- ************************************************************************** --
--                                                                            --
--           Package PRIVATE constants, procedures, and functions             --
--                                                                            --
-- ************************************************************************** --

-------------------------------------------------------------------------------
-- Data Masking Format Library Constants
-------------------------------------------------------------------------------

-- maximum lengths than can be inputted to post process functions 
DM_MAX_BUF_LEN_CC_GEN     constant number   := 10;
DM_MAX_BUF_LEN_ISBN10_GEN constant number   := 9;
DM_MAX_BUF_LEN_ISBN13_GEN constant number   := 12;
DM_MAX_BUF_LEN_UPC_GEN    constant number   := 11;
DM_MAX_BUF_LEN_SSN_GEN    constant number   := 9;
DM_MAX_BUF_LEN_CN_SIN_GEN constant number   := 8;
DM_MAX_BUF_LEN_PH_GEN     constant number   := 7;

-- possible card lengths
DM_CARD_LEN_16        constant number := 16;
DM_CARD_LEN_19        constant number := 19;
DM_CARD_LEN_15        constant number := 15;
DM_CARD_MIN_LEN       constant number := 13;
DM_CARD_MAX_DT_RETAIN constant number := 4;

-- enums for various card types 
DM_FIRST_CARD    constant number := 0;

DM_MASTER_CARD   constant number := 1;
DM_VISA_CARD     constant number := 2;
DM_AMEX16_CARD   constant number := 3;
DM_AMEX15_CARD   constant number := 4;
DM_DISCOVER_CARD constant number := 5;
DM_DINER_CARD    constant number := 6;
DM_ENROUTE_CARD  constant number := 7;
DM_JCB16_CARD    constant number := 8;
DM_JCB15_CARD    constant number := 9;

DM_LAST_CARD     constant number := 10;
DM_ANY_CARD      constant number := 99;
DM_CC_FORMAT     varchar2(4)     := NULL;

-- North America/US/Canada phone codes 
DM_PH_NA_ANY             constant number := 0;
DM_PH_USA_ANY            constant number := 1;
DM_PH_CANADA_ANY         constant number := 2;

-- US state start
DM_USA_PH_START          constant number := 3;
DM_USA_PH_AL             constant number := 3;

DM_USA_PH_AK             constant number := 4;
DM_USA_PH_AZ             constant number := 5;
DM_USA_PH_AR             constant number := 6;
DM_USA_PH_CA             constant number := 7;
DM_USA_PH_CO             constant number := 8;
DM_USA_PH_CT             constant number := 9;
DM_USA_PH_DE             constant number := 10;
DM_USA_PH_DC             constant number := 11;
DM_USA_PH_FL             constant number := 12;
DM_USA_PH_GA             constant number := 13;
DM_USA_PH_HI             constant number := 14;
DM_USA_PH_ID             constant number := 15;
DM_USA_PH_IL             constant number := 16;
DM_USA_PH_IN             constant number := 17;
DM_USA_PH_IA             constant number := 18;
DM_USA_PH_KS             constant number := 19;
DM_USA_PH_KY             constant number := 20;
DM_USA_PH_LA             constant number := 21;
DM_USA_PH_ME             constant number := 22;
DM_USA_PH_MD             constant number := 23;
DM_USA_PH_MA             constant number := 24;
DM_USA_PH_MI             constant number := 25;
DM_USA_PH_MN             constant number := 26;
DM_USA_PH_MS             constant number := 27;
DM_USA_PH_MO             constant number := 28;
DM_USA_PH_MT             constant number := 29;
DM_USA_PH_NE             constant number := 30;
DM_USA_PH_NV             constant number := 31;
DM_USA_PH_NH             constant number := 32;
DM_USA_PH_NJ             constant number := 33;
DM_USA_PH_NM             constant number := 34;
DM_USA_PH_NY             constant number := 35;
DM_USA_PH_NC             constant number := 36;
DM_USA_PH_ND             constant number := 37;
DM_USA_PH_OH             constant number := 38;
DM_USA_PH_OK             constant number := 39;
DM_USA_PH_OR             constant number := 40;
DM_USA_PH_PA             constant number := 41;
DM_USA_PH_RI             constant number := 42;
DM_USA_PH_SC             constant number := 43;
DM_USA_PH_SD             constant number := 44;
DM_USA_PH_TN             constant number := 45;
DM_USA_PH_TX             constant number := 46;
DM_USA_PH_UT             constant number := 47;
DM_USA_PH_VT             constant number := 48;
DM_USA_PH_VA             constant number := 49;
DM_USA_PH_WA             constant number := 50;
DM_USA_PH_WV             constant number := 51;
DM_USA_PH_WI             constant number := 52;

DM_USA_PH_WY             constant number := 53;
DM_USA_PH_END            constant number := 53;
-- US state end

-- Canada state start
DM_CAN_PH_START          constant number := 54;
DM_CAN_PH_AB             constant number := 54;

DM_CAN_PH_BC             constant number := 55;
DM_CAN_PH_MB             constant number := 56;
DM_CAN_PH_NB             constant number := 57;
DM_CAN_PH_NL             constant number := 58;
DM_CAN_PH_NS             constant number := 59;
DM_CAN_PH_NT             constant number := 60;
DM_CAN_PH_NU             constant number := 61;
DM_CAN_PH_ON             constant number := 62;
DM_CAN_PH_PE             constant number := 63;
DM_CAN_PH_QC             constant number := 64;
DM_CAN_PH_SK             constant number := 65;

DM_CAN_PH_YT             constant number := 66;
DM_CAN_PH_END            constant number := 66;
-- Canada state end

-- Error numbers
DM_FL_E_BUFFER_TOO_LONG     constant number := -20001;
DM_FL_E_BUFFER_NON_DIGITS   constant number := -20002;
DM_FL_E_WRONG_SSN_INPUT     constant number := -20003;
DM_FL_E_INVALID_PH_CODE     constant number := -20004;
DM_FL_E_INVALID_BUF_LEN     constant number := -20005;
DM_FL_E_INVALID_PH_BUFFER   constant number := -20006;
DM_FL_E_INTERNAL_ERROR      constant number := -20007;
DM_FL_E_BUFFER_TOO_SHORT    constant number := -20008;
DM_FL_E_INVALID_REMAP_KEY   constant number := -20009;

-- North American Phone processing constants 
-- North American Area codes have 3 digits  
  TYPE dm_ph_prefix_array is TABLE of VARCHAR2(3);
  TYPE dm_ph_stnb_array is TABLE of NUMBER;

-- Credit Card Prefix Array: we confine to max of 4 
  TYPE dm_cc_prefix_array is TABLE of VARCHAR2(4);
  TYPE dm_cc_brand_array  is TABLE of NUMBER;

-----------------------------------------------------------------------------
---- Credit Card Prefix Tables ----------------------------------------------
-----------------------------------------------------------------------------

-- VISA prefixes
  dm_visa_prefix_tab dm_cc_prefix_array
                        := dm_cc_prefix_array('4','4539','4556','4916','4532',
                                              '4929','4485','4716');
-- MASTER prefixes
  dm_master_prefix_tab dm_cc_prefix_array
                        := dm_cc_prefix_array('51','52','53','54','55');
-- AMEX prefixes
  dm_amex_prefix_tab dm_cc_prefix_array 
                        := dm_cc_prefix_array('34','37');
-- DISCOVER prefixes
  dm_discover_prefix_tab dm_cc_prefix_array 
                        := dm_cc_prefix_array('6011');
-- DINER prefixes
  dm_diner_prefix_tab dm_cc_prefix_array
                        := dm_cc_prefix_array('201','301','302','303',
                                              '36','38');
-- ENROUTE prefixes ? should we provide it
  dm_enroute_prefix_tab dm_cc_prefix_array
                        := dm_cc_prefix_array('2014', '2149');
-- JCB16 prefixes
  dm_jcb16_prefix_tab dm_cc_prefix_array
                        := dm_cc_prefix_array('3088', '3096', '3112','3158',
                                              '3337','3528'); 
-- JCB15 prefixes
  dm_jcb15_prefix_tab dm_cc_prefix_array
                        := dm_cc_prefix_array('2100', '1800');
-- VOYAGER prefixes
  dm_voyager_prefix_tab dm_cc_prefix_array 
                        := dm_cc_prefix_array('8669');

-----------------------------------------------------------------------------
---- North America Phone Area Codes -----------------------------------------
-----------------------------------------------------------------------------

  dm_all_ph_prefix_tab dm_ph_prefix_array
           := dm_ph_prefix_array('205','251','256','334',
--AL END :4
                                 '907',
--AK END :1
                                 '480','520','602','623','928',
--AZ END :5
                                 '479','501','870',
--AR END :3
                                 '209','213','310','323','408',  
                                 '415','424','510','530','559',
                                 '562','619','626','650','661',
                                 '707','714','760','805','818',
                                 '831','858','909','916','925',
                                 '949','951',
--CA END :27
                                 '303','719','720','970', 
--CO END :4
                                 '203', '475', '860', '959', 
--CT END :4
                                 '302',
--DE END :1
                                 '202', 
--DC END :1
                                 '239', '305', '321', '352', 
                                 '386', '407', '561', '727',
                                 '754', '772', '786', '813',
                                 '850', '863', '904', '941',
                                 '954',
--FL END :17
                                 '229', '404', '470', '478', 
                                 '678', '706', '762', '770',
                                 '912', 
--GA END :9
                                 '808', 
--HI END :1 
                                 '208', 
--ID END :1
                                 '217', '224', '309', '312',
                                 '618', '630', '708', '773',
                                 '779', '815', '847', 
--IL END :11
                                 '219', '260', '317', '574',
                                 '765', '812',
--IN END :6
                                 '319', '515', '563', '641',
                                 '712',
--IA END :5
                                 '316', '620', '785', '913',
--KS END :4
                                 '270', '364', '502', '606',
                                 '859',
--KY END :5 Kentucky
                                 '225', '318', '337', '504',
                                 '985',
--LA END :5 Louisiana
                                 '207',
--ME END :1 Maine
                                 '240', '301', '410', '443',
--MD END :4 Maryland
                                 '339', '351', '413', '508',
                                 '617', '774', '781', '857',
                                 '978',
--MA END :9  Massachusetts
                                 '231', '248', '269', '313',
                                 '517', '586', '616', '734',
                                 '810', '906', '947', '989',
--MI END :12 Michigan
                                 '218', '320', '507', '612',
                                 '651', '763', '952',
--MN END :7  Minnesota
                                 '228', '601', '662', '769',
--MS END :4  Mississippi
                                 '314', '417', '573', '636',
                                 '660', '816',
--MO END :6  Missouri
                                 '406',
--MT END :1  Montana
                                 '308', '402',
--NE END :2  Nebraska
                                 '702', '775',
--NV END :2  Nevada
                                 '603',
--NH END :1  New Hampshire
                                 '201', '551', '609', '732',
                                 '848', '856', '862', '908',
                                 '973',
--NJ END :9  New Jersey
                                 '505', '575',
--NM END :2  New Mexico
                                 '212', '315', '347', '516',
                                 '518', '585', '607', '631',
                                 '646', '716', '718', '845',
                                 '914', '917',
--NY END :14 New York
                                 '252', '336', '704', '828',
                                 '910', '919', '980',
--NC END :6  North Carolina
                                 '701',
--ND END :1  North Dakota
                                 '216', '234', '283', '330',
                                 '419', '440', '513', '567',
                                 '614', '740', '937',
--OH END :11 Ohio
                                 '405', '580', '918',
--OK END :3  Oklahoma
                                 '503', '541', '971',
--OR END :3  Oregon
                                 '215', '267', '412', '445',
                                 '484', '570', '610', '717',
                                 '724', '814', '835', '878',
--PA END :12 Pennsylvania
                                 '401',
--RI END :1  Rhode Island
                                 '803', '843', '864',
--SC END :3  South Carolina
                                 '605',
--SD END :1  South Dakota
                                 '423', '615', '731', '865',
                                 '901', '931',
--TN END :6  Tennessee
                                 '210', '214', '254', '281',
                                 '325', '361', '409', '430',
                                 '432', '469', '512', '682',
                                 '713', '806', '817', '830',
                                 '832', '903', '915', '936',
                                 '940', '956', '972', '979',
--TX END :24 Texas
                                 '385', '435', '801',
--UT END :3  Utah
                                 '802',
--VT END :1  Vermont
                                 '276', '434', '540', '571',
                                 '703', '757', '804',
--VA END :7  Virginia
                                 '206', '253', '360', '425',
                                 '509', '564',
--WA END :6  Washington
                                 '304',
--WV END :1  West Virginia
                                 '262', '414', '608', '715', 
                                 '920',
--WI END :5  Wisconsin
                                 '307',
--WY END :1  Wyoming
                                 '403', '780',
--AB END :2  Canada/Alberta
                                 '250', '604', '778',
--BC END :3  Canada/British Columbia
                                 '204',
--MB END :1  Canada/Manitoba
                                 '506',
--NB END :1  Canada/New Brunswick
                                 '709',
--NL END :1  Canada/Newfoundland and Labrador
                                 '902',
--NS END :1  Canada/Nova Scotia
--PE END :   Canada/Prince Edward Island
                                 '226', '289', '416', '519',
                                 '613', '647', '705', '807',
                                 '905',
--ON END :9  Canada/Ontario
                                 '418', '438', '450', '514',
                                 '581', '819',
--QC END :6  Canada/Quebec 
                                 '306',
--SK END :1  Canada/Saskatchewan
                                 '867'
--NT END :1   Canada/Northwest Territories
--YT END :    Canada/Yukon
--NU END :    Canada/Nunavut      
                                     );

--area code start indexes for different states
  dm_us_al_ph_ac_st  number := 1; 
  dm_us_al_ph_ac_nb  number := 4; 
  dm_us_ak_ph_ac_st  number := dm_us_al_ph_ac_st + dm_us_al_ph_ac_nb; 
  dm_us_ak_ph_ac_nb  number := 1; 
  dm_us_az_ph_ac_st  number := dm_us_ak_ph_ac_st + dm_us_ak_ph_ac_nb; 
  dm_us_az_ph_ac_nb  number := 5; 
  dm_us_ar_ph_ac_st  number := dm_us_az_ph_ac_st + dm_us_az_ph_ac_nb; 
  dm_us_ar_ph_ac_nb  number := 3; 
  dm_us_ca_ph_ac_st  number := dm_us_ar_ph_ac_st + dm_us_ar_ph_ac_nb; 
  dm_us_ca_ph_ac_nb  number := 27; 
  dm_us_co_ph_ac_st  number := dm_us_ca_ph_ac_st + dm_us_ca_ph_ac_nb; 
  dm_us_co_ph_ac_nb  number := 4; 
  dm_us_ct_ph_ac_st  number := dm_us_co_ph_ac_st + dm_us_co_ph_ac_nb; 
  dm_us_ct_ph_ac_nb  number := 4; 
  dm_us_de_ph_ac_st  number := dm_us_ct_ph_ac_st + dm_us_ct_ph_ac_nb; 
  dm_us_de_ph_ac_nb  number := 1; 
  dm_us_dc_ph_ac_st  number := dm_us_de_ph_ac_st + dm_us_de_ph_ac_nb; 
  dm_us_dc_ph_ac_nb  number := 1; 
  dm_us_fl_ph_ac_st  number := dm_us_dc_ph_ac_st + dm_us_dc_ph_ac_nb; 
  dm_us_fl_ph_ac_nb  number := 17; 
  dm_us_ga_ph_ac_st  number := dm_us_fl_ph_ac_st + dm_us_fl_ph_ac_nb; 
  dm_us_ga_ph_ac_nb  number := 9; 
  dm_us_hi_ph_ac_st  number := dm_us_ga_ph_ac_st + dm_us_ga_ph_ac_nb; 
  dm_us_hi_ph_ac_nb  number := 1; 
  dm_us_id_ph_ac_st  number := dm_us_hi_ph_ac_st + dm_us_hi_ph_ac_nb; 
  dm_us_id_ph_ac_nb  number := 1; 
  dm_us_il_ph_ac_st  number := dm_us_id_ph_ac_st + dm_us_id_ph_ac_nb; 
  dm_us_il_ph_ac_nb  number := 11; 
  dm_us_in_ph_ac_st  number := dm_us_il_ph_ac_st + dm_us_il_ph_ac_nb; 
  dm_us_in_ph_ac_nb  number := 6; 
  dm_us_ia_ph_ac_st  number := dm_us_in_ph_ac_st + dm_us_in_ph_ac_nb; 
  dm_us_ia_ph_ac_nb  number := 5; 
  dm_us_ks_ph_ac_st  number := dm_us_ia_ph_ac_st + dm_us_ia_ph_ac_nb; 
  dm_us_ks_ph_ac_nb  number := 4; 
  dm_us_ky_ph_ac_st  number := dm_us_ks_ph_ac_st + dm_us_ks_ph_ac_nb; 
  dm_us_ky_ph_ac_nb  number := 5; 
  dm_us_la_ph_ac_st  number := dm_us_ky_ph_ac_st + dm_us_ky_ph_ac_nb; 
  dm_us_la_ph_ac_nb  number := 5; 
  dm_us_me_ph_ac_st  number := dm_us_la_ph_ac_st + dm_us_la_ph_ac_nb; 
  dm_us_me_ph_ac_nb  number := 1; 
  dm_us_md_ph_ac_st  number := dm_us_me_ph_ac_st + dm_us_me_ph_ac_nb; 
  dm_us_md_ph_ac_nb  number := 4; 
  dm_us_ma_ph_ac_st  number := dm_us_md_ph_ac_st + dm_us_md_ph_ac_nb; 
  dm_us_ma_ph_ac_nb  number := 9; 
  dm_us_mi_ph_ac_st  number := dm_us_ma_ph_ac_st + dm_us_ma_ph_ac_nb; 
  dm_us_mi_ph_ac_nb  number := 12; 
  dm_us_mn_ph_ac_st  number := dm_us_mi_ph_ac_st + dm_us_mi_ph_ac_nb; 
  dm_us_mn_ph_ac_nb  number := 7; 
  dm_us_ms_ph_ac_st  number := dm_us_mn_ph_ac_st + dm_us_mn_ph_ac_nb; 
  dm_us_ms_ph_ac_nb  number := 4; 
  dm_us_mo_ph_ac_st  number := dm_us_ms_ph_ac_st + dm_us_ms_ph_ac_nb; 
  dm_us_mo_ph_ac_nb  number := 6; 
  dm_us_mt_ph_ac_st  number := dm_us_mo_ph_ac_st + dm_us_mo_ph_ac_nb; 
  dm_us_mt_ph_ac_nb  number := 1; 
  dm_us_ne_ph_ac_st  number := dm_us_mt_ph_ac_st + dm_us_mt_ph_ac_nb; 
  dm_us_ne_ph_ac_nb  number := 2; 
  dm_us_nv_ph_ac_st  number := dm_us_ne_ph_ac_st + dm_us_ne_ph_ac_nb; 
  dm_us_nv_ph_ac_nb  number := 2; 
  dm_us_nh_ph_ac_st  number := dm_us_nv_ph_ac_st + dm_us_nv_ph_ac_nb; 
  dm_us_nh_ph_ac_nb  number := 1; 
  dm_us_nj_ph_ac_st  number := dm_us_nh_ph_ac_st + dm_us_nh_ph_ac_nb; 
  dm_us_nj_ph_ac_nb  number := 9; 
  dm_us_nm_ph_ac_st  number := dm_us_nj_ph_ac_st + dm_us_nj_ph_ac_nb; 
  dm_us_nm_ph_ac_nb  number := 2; 
  dm_us_ny_ph_ac_st  number := dm_us_nm_ph_ac_st + dm_us_nm_ph_ac_nb; 
  dm_us_ny_ph_ac_nb  number := 14; 
  dm_us_nc_ph_ac_st  number := dm_us_ny_ph_ac_st + dm_us_ny_ph_ac_nb; 
  dm_us_nc_ph_ac_nb  number := 7; 
  dm_us_nd_ph_ac_st  number := dm_us_nc_ph_ac_st + dm_us_nc_ph_ac_nb; 
  dm_us_nd_ph_ac_nb  number := 1; 
  dm_us_oh_ph_ac_st  number := dm_us_nd_ph_ac_st + dm_us_nd_ph_ac_nb; 
  dm_us_oh_ph_ac_nb  number := 11; 
  dm_us_ok_ph_ac_st  number := dm_us_oh_ph_ac_st + dm_us_oh_ph_ac_nb; 
  dm_us_ok_ph_ac_nb  number := 3; 
  dm_us_or_ph_ac_st  number := dm_us_ok_ph_ac_st + dm_us_ok_ph_ac_nb; 
  dm_us_or_ph_ac_nb  number := 3; 
  dm_us_pa_ph_ac_st  number := dm_us_or_ph_ac_st + dm_us_or_ph_ac_nb; 
  dm_us_pa_ph_ac_nb  number := 12; 
  dm_us_ri_ph_ac_st  number := dm_us_pa_ph_ac_st + dm_us_pa_ph_ac_nb; 
  dm_us_ri_ph_ac_nb  number := 1; 
  dm_us_sc_ph_ac_st  number := dm_us_ri_ph_ac_st + dm_us_ri_ph_ac_nb; 
  dm_us_sc_ph_ac_nb  number := 3; 
  dm_us_sd_ph_ac_st  number := dm_us_sc_ph_ac_st + dm_us_sc_ph_ac_nb; 
  dm_us_sd_ph_ac_nb  number := 1; 
  dm_us_tn_ph_ac_st  number := dm_us_sd_ph_ac_st + dm_us_sd_ph_ac_nb; 
  dm_us_tn_ph_ac_nb  number := 6; 
  dm_us_tx_ph_ac_st  number := dm_us_tn_ph_ac_st + dm_us_tn_ph_ac_nb; 
  dm_us_tx_ph_ac_nb  number := 24; 
  dm_us_ut_ph_ac_st  number := dm_us_tx_ph_ac_st + dm_us_tx_ph_ac_nb; 
  dm_us_ut_ph_ac_nb  number := 3; 
  dm_us_vt_ph_ac_st  number := dm_us_ut_ph_ac_st + dm_us_ut_ph_ac_nb; 
  dm_us_vt_ph_ac_nb  number := 1; 
  dm_us_va_ph_ac_st  number := dm_us_vt_ph_ac_st + dm_us_vt_ph_ac_nb; 
  dm_us_va_ph_ac_nb  number := 7; 
  dm_us_wa_ph_ac_st  number := dm_us_va_ph_ac_st + dm_us_va_ph_ac_nb; 
  dm_us_wa_ph_ac_nb  number := 6; 
  dm_us_wv_ph_ac_st  number := dm_us_wa_ph_ac_st + dm_us_wa_ph_ac_nb; 
  dm_us_wv_ph_ac_nb  number := 1; 
  dm_us_wi_ph_ac_st  number := dm_us_wv_ph_ac_st + dm_us_wv_ph_ac_nb; 
  dm_us_wi_ph_ac_nb  number := 5; 
  dm_us_wy_ph_ac_st  number := dm_us_wi_ph_ac_st + dm_us_wi_ph_ac_nb; 
  dm_us_wy_ph_ac_nb  number := 1; 

-- Canada states
  dm_cn_ab_ph_ac_st  number := dm_us_wy_ph_ac_st + dm_us_wy_ph_ac_nb; 
  dm_cn_ab_ph_ac_nb  number := 2; 
  dm_cn_bc_ph_ac_st  number := dm_cn_ab_ph_ac_st + dm_cn_ab_ph_ac_nb; 
  dm_cn_bc_ph_ac_nb  number := 3; 
  dm_cn_mb_ph_ac_st  number := dm_cn_bc_ph_ac_st + dm_cn_bc_ph_ac_nb; 
  dm_cn_mb_ph_ac_nb  number := 1; 
  dm_cn_nb_ph_ac_st  number := dm_cn_mb_ph_ac_st + dm_cn_mb_ph_ac_nb; 
  dm_cn_nb_ph_ac_nb  number := 1; 
  dm_cn_nl_ph_ac_st  number := dm_cn_nb_ph_ac_st + dm_cn_nb_ph_ac_nb; 
  dm_cn_nl_ph_ac_nb  number := 1; 
-- ns, pe share same area code 
  dm_cn_ns_ph_ac_st  number := dm_cn_nl_ph_ac_st + dm_cn_nl_ph_ac_nb; 
  dm_cn_ns_ph_ac_nb  number := 1; 
  dm_cn_pe_ph_ac_st  number := dm_cn_ns_ph_ac_st;
  dm_cn_pe_ph_ac_nb  number := 1; 
  dm_cn_on_ph_ac_st  number := dm_cn_pe_ph_ac_st + dm_cn_pe_ph_ac_nb; 
  dm_cn_on_ph_ac_nb  number := 9; 
  dm_cn_qc_ph_ac_st  number := dm_cn_on_ph_ac_st + dm_cn_on_ph_ac_nb; 
  dm_cn_qc_ph_ac_nb  number := 6; 
  dm_cn_sk_ph_ac_st  number := dm_cn_qc_ph_ac_st + dm_cn_qc_ph_ac_nb; 
  dm_cn_sk_ph_ac_nb  number := 1; 
-- nt, yt, nu share same area code;
  dm_cn_nt_ph_ac_st  number := dm_cn_sk_ph_ac_st + dm_cn_sk_ph_ac_nb; 
  dm_cn_nt_ph_ac_nb  number := 1; 
  dm_cn_yt_ph_ac_st  number := dm_cn_nt_ph_ac_st; 
  dm_cn_yt_ph_ac_nb  number := 1; 
  dm_cn_nu_ph_ac_st  number := dm_cn_nt_ph_ac_st; 
  dm_cn_nu_ph_ac_nb  number := 1; 

-- for all US
  dm_us_ph_ac_st     number := dm_us_al_ph_ac_st; 
  dm_us_ph_ac_nb     number := dm_us_wy_ph_ac_st + dm_us_wy_ph_ac_nb
                               -dm_us_ph_ac_st; 
-- for all CN 
  dm_cn_ph_ac_st     number := dm_cn_ab_ph_ac_st; 
  dm_cn_ph_ac_nb     number := dm_cn_nu_ph_ac_st + dm_cn_nu_ph_ac_nb
                               -dm_cn_ph_ac_st; 
-- for entire NA(north america) 
  dm_na_ph_ac_st     number := dm_us_al_ph_ac_st; 
  dm_na_ph_ac_nb     number := dm_cn_nu_ph_ac_st + dm_cn_nu_ph_ac_nb
                               -dm_na_ph_ac_st; 

--build an array for the above values
--mainly done for code maintenance (avoid big case stmts in phone gen code) 

  dm_all_ph_stnb_tab dm_ph_stnb_array
           := dm_ph_stnb_array(dm_na_ph_ac_st,    dm_na_ph_ac_nb,     /*2*/
                               dm_us_ph_ac_st,    dm_us_ph_ac_nb,
                               dm_cn_ph_ac_st,    dm_cn_ph_ac_nb,
                               dm_us_al_ph_ac_st, dm_us_al_ph_ac_nb,
                               dm_us_ak_ph_ac_st, dm_us_ak_ph_ac_nb,  /*10*/
                               dm_us_az_ph_ac_st, dm_us_az_ph_ac_nb,
                               dm_us_ar_ph_ac_st, dm_us_ar_ph_ac_nb,
                               dm_us_ca_ph_ac_st, dm_us_ca_ph_ac_nb,
                               dm_us_co_ph_ac_st, dm_us_co_ph_ac_nb,
                               dm_us_ct_ph_ac_st, dm_us_ct_ph_ac_nb,  /*20*/
                               dm_us_de_ph_ac_st, dm_us_de_ph_ac_nb,
                               dm_us_dc_ph_ac_st, dm_us_dc_ph_ac_nb,
                               dm_us_fl_ph_ac_st, dm_us_fl_ph_ac_nb,
                               dm_us_ga_ph_ac_st, dm_us_ga_ph_ac_nb, 
                               dm_us_hi_ph_ac_st, dm_us_hi_ph_ac_nb,  /*30*/ 
                               dm_us_id_ph_ac_st, dm_us_id_ph_ac_nb, 
                               dm_us_il_ph_ac_st, dm_us_il_ph_ac_nb,  
                               dm_us_in_ph_ac_st, dm_us_in_ph_ac_nb, 
                               dm_us_ia_ph_ac_st, dm_us_ia_ph_ac_nb, 
                               dm_us_ks_ph_ac_st, dm_us_ks_ph_ac_nb,  /*40*/
                               dm_us_ky_ph_ac_st, dm_us_ky_ph_ac_nb, 
                               dm_us_la_ph_ac_st, dm_us_la_ph_ac_nb, 
                               dm_us_me_ph_ac_st, dm_us_me_ph_ac_nb, 
                               dm_us_md_ph_ac_st, dm_us_md_ph_ac_nb, 
                               dm_us_ma_ph_ac_st, dm_us_ma_ph_ac_nb,  /*50*/ 
                               dm_us_mi_ph_ac_st, dm_us_mi_ph_ac_nb,  
                               dm_us_mn_ph_ac_st, dm_us_mn_ph_ac_nb, 
                               dm_us_ms_ph_ac_st, dm_us_ms_ph_ac_nb, 
                               dm_us_mo_ph_ac_st, dm_us_mo_ph_ac_nb, 
                               dm_us_mt_ph_ac_st, dm_us_mt_ph_ac_nb,  /*60*/ 
                               dm_us_ne_ph_ac_st, dm_us_ne_ph_ac_nb, 
                               dm_us_nv_ph_ac_st, dm_us_nv_ph_ac_nb, 
                               dm_us_nh_ph_ac_st, dm_us_nh_ph_ac_nb, 
                               dm_us_nj_ph_ac_st, dm_us_nj_ph_ac_nb, 
                               dm_us_nm_ph_ac_st, dm_us_nm_ph_ac_nb,  /*70*/ 
                               dm_us_ny_ph_ac_st, dm_us_ny_ph_ac_nb,   
                               dm_us_nc_ph_ac_st, dm_us_nc_ph_ac_nb, 
                               dm_us_nd_ph_ac_st, dm_us_nd_ph_ac_nb, 
                               dm_us_oh_ph_ac_st, dm_us_oh_ph_ac_nb,   
                               dm_us_ok_ph_ac_st, dm_us_ok_ph_ac_nb,  /*80*/ 
                               dm_us_or_ph_ac_st, dm_us_or_ph_ac_nb, 
                               dm_us_pa_ph_ac_st, dm_us_pa_ph_ac_nb, 
                               dm_us_ri_ph_ac_st, dm_us_ri_ph_ac_nb, 
                               dm_us_sc_ph_ac_st, dm_us_sc_ph_ac_nb, 
                               dm_us_sd_ph_ac_st, dm_us_sd_ph_ac_nb,  /*90*/ 
                               dm_us_tn_ph_ac_st, dm_us_tn_ph_ac_nb, 
                               dm_us_tx_ph_ac_st, dm_us_tx_ph_ac_nb, 
                               dm_us_ut_ph_ac_st, dm_us_ut_ph_ac_nb, 
                               dm_us_vt_ph_ac_st, dm_us_vt_ph_ac_nb, 
                               dm_us_va_ph_ac_st, dm_us_va_ph_ac_nb, /*100*/ 
                               dm_us_wa_ph_ac_st, dm_us_wa_ph_ac_nb, 
                               dm_us_wv_ph_ac_st, dm_us_wv_ph_ac_nb, 
                               dm_us_wi_ph_ac_st, dm_us_wi_ph_ac_nb, 
                               dm_us_wy_ph_ac_st, dm_us_wy_ph_ac_nb, /*108*/ 
                               dm_cn_ab_ph_ac_st, dm_cn_ab_ph_ac_nb, 
                               dm_cn_bc_ph_ac_st, dm_cn_bc_ph_ac_nb, 
                               dm_cn_mb_ph_ac_st, dm_cn_mb_ph_ac_nb, 
                               dm_cn_nb_ph_ac_st, dm_cn_nb_ph_ac_nb, 
                               dm_cn_nl_ph_ac_st, dm_cn_nl_ph_ac_nb, 
                               dm_cn_ns_ph_ac_st, dm_cn_ns_ph_ac_nb, 
                               dm_cn_pe_ph_ac_st, dm_cn_pe_ph_ac_nb, 
                               dm_cn_on_ph_ac_st, dm_cn_on_ph_ac_nb, 
                               dm_cn_qc_ph_ac_st, dm_cn_qc_ph_ac_nb, 
                               dm_cn_sk_ph_ac_st, dm_cn_sk_ph_ac_nb, 
                               dm_cn_nt_ph_ac_st, dm_cn_nt_ph_ac_nb, 
                               dm_cn_yt_ph_ac_st, dm_cn_yt_ph_ac_nb, 
                               dm_cn_nu_ph_ac_st, dm_cn_nu_ph_ac_nb); 


-- validate the input buffer for all digits  
procedure mgmt_dm_buf_ad_validate(p_buff in varchar2, p_buflen in number)
is
  buf_len      number := 0;
  dummy_num    number; 
  digits_only  boolean := true;
begin
  buf_len := NVL(LENGTH(p_buff), 0);
-- check for buffer overflow of length 
  if(buf_len > p_buflen) then
    DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_BUFFER_TOO_LONG,
                            'Input buffer:' || p_buff ||
                            ': length should be less than or equal to: ' ||
                             (p_buflen)); 
  end if;

-- check for all digits in the buffer
  begin
    dummy_num := TO_NUMBER(p_buff); 
  exception
    when others then
      digits_only := false; 
  end;

  if(digits_only = false) then
    DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_BUFFER_NON_DIGITS,
                            'Input buffer:' || p_buff ||
                            ': should contain only digits');
    
  end if; 
end mgmt_dm_buf_ad_validate;

-- compute the USA SSN number
-- Note: At present,this post process function gets used when
-- user generates RNs from 020000000 to 738999999 
--
-- Back ground: SSN has following form
--          XXX XX XXXX (no individual part should be zero !!)
-- Now let us we generates random digits[0-9] for all these nine places
-- We can have following possibilities 
-- 000-xx-xxxxx : There will be 1M-1  possible numbers in this combination 
-- xxx-00-xxxxx : There (1K-1)*(10K-1), i.e.  ~10M possible numbers
--                in this combination   
-- xxx-xx-00000 : 100K-1 numbers in this combination
--
-- Strategy: 1) We are treating first 20M numbers as special numbers
--              and use them to re-mapping purposes 
--           2) We are not addressing 000-xx-xxxxx as we will have 
--              restriction on the input be bigger than 020000000 (20M) 
--           3) For the xxx-00-xxxxx combination we use
--              the re-map base to be 00501xxxx (5M)
--              As we need 10M numbers we consume upto ~15M 
--              The max numbers will be: 014899999 for 999009999 
--           4) For the xxx-xx-0000 combination we use
--              re-map base to be 019010001 (19M)  
--              As we need only 100K the max num will be
--              019108009 for 99999000
-- Final Note: Though we are generating numbers from 020000000 to 738999999
--             we can use this post process function as long as the number
--             is at least as big as 020000000 to 999-99-9999 (futuristic) 

function mgmt_dm_compute_ssn_number( mask_value IN varchar2,
                                     format     IN varchar2 := null)
return varchar2
is
  ssn_number    varchar2(9);
  fssn_number   varchar2(30);
  area_code     number;
  mid_num       number;
  final_four    number;
  final_four_st varchar2(4);
  first_five    number;
  base_code1    number := 00501;
  base_code2    number := 019010001;
  rem_num       number := 0;
  new_num       number := 0;
  i             number;      
  j             number;      
  k             number;      
begin
-- validate the input buffer 
  mgmt_dm_buf_ad_validate(mask_value, 9);
  ssn_number := mask_value;

--generate random digts upto the 9 digits 
--only if input buffer doesn't have 9 digits(useful for testing with 
--out requiring DM java test) 
  --dbms_output.put_line('mask_length is ' || NVL(LENGTH(mask_value), 0));
  j := DM_MAX_BUF_LEN_SSN_GEN - (NVL(LENGTH(mask_value), 0)) ;
  for i in 1 .. j loop
    k := FLOOR(DBMS_RANDOM.VALUE * 10);
    ssn_number := ssn_number || k;
  end loop;

-- break it into parts 
  area_code     := TO_NUMBER(SUBSTR(ssn_number,1,3));
  mid_num       := TO_NUMBER(SUBSTR(ssn_number,4,2));
  final_four    := TO_NUMBER(SUBSTR(ssn_number,6,4));
  final_four_st := SUBSTR(ssn_number,6,4);
  first_five    := TO_NUMBER(SUBSTR(ssn_number,1,5));

-- throw error if the are_code is < 020 
  if(area_code < 020) then
    DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_WRONG_SSN_INPUT,
                            'Input ssn should be at least:' || '020000000' ||
                            ': but the input given was' ||
                             ssn_number); 
  end if;
     
-- re map numbers of format 
-- (IF case) first we check for xxxxxx0000 combination
-- note: here mid number also could be 0 
-- (ELSIF) later we check for xxx00xxxx 
-- note: Here we guarantee final_four is not zero
  if (final_four = 0) then
    rem_num := FLOOR((first_five - 02000)/9999);
    new_num := base_code2 + ((first_five) - 02000) + rem_num; 
    ssn_number := 0 || new_num;
  elsif (mid_num = 0) then
    rem_num := FLOOR((area_code-020)/99);
    new_num := base_code1 + (area_code-020)+rem_num;

    if(new_num < 01000) then
      ssn_number := '00' || new_num || final_four_st;
    else
      ssn_number := '0' || new_num || final_four_st;
    end if;
  end if;

-- Format the number
    fssn_number := substr(ssn_number, 1, 3);
    fssn_number := fssn_number || format; 
    fssn_number := fssn_number || substr(ssn_number, 4, 2); 
    fssn_number := fssn_number || format; 
    fssn_number := fssn_number || substr(ssn_number, 6, 4); 

  return fssn_number; 
end;

-- compute the ISBN13 number
function mgmt_dm_compute_isbn13_number( mask_value IN varchar2,
                                        format     IN varchar2 := null)
return varchar2
is
  isbn_number  varchar2(13);
  fisbn_number varchar2(30);
  final_digit  number;
  digit_value  number;
  total        number := 0;
  rem_value    number;
  pos          number;
  i            number;      
  j            number;      
  k            number;      
  wf           number := 1;
begin
-- validate the input buffer 
  mgmt_dm_buf_ad_validate(mask_value, 13);
  isbn_number := mask_value;

--generate random digts upto the 12 digits (MAX_ISBN13_DIGITS-1) 
  j := DM_MAX_BUF_LEN_ISBN13_GEN - (NVL(LENGTH(mask_value), 0)) ;
  for i in 1 .. j loop
    k := FLOOR(DBMS_RANDOM.VALUE * 10);
    isbn_number := isbn_number || k;
  end loop;

-- compute the sum
  for pos in 1 .. 12 loop 
    if mod(pos, 2) = 0 then
      wf := 3; 
    else
      wf := 1; 
    end if;
    digit_value := SUBSTR(isbn_number,pos,1);
    total := total + (digit_value * (wf));
  end loop;

-- compute the final digit/char and append
  rem_value := mod(total, 10); 

  if(rem_value != 0) then
    final_digit := 10 - rem_value;
  else
    final_digit := 0;
  end if;

  isbn_number := isbn_number || final_digit;

-- Format the number
    fisbn_number := substr(isbn_number, 1, 3);
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 4, 1); 
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 5, 6); 
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 11, 2); 
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 13, 1); 

  return fisbn_number;
end;

-- compute the ISBN10 number
function mgmt_dm_compute_isbn10_number( mask_value IN varchar2,
                                        format     IN varchar2 := null)
return varchar2
is
  isbn_number  varchar2(10);
  fisbn_number varchar2(30);
  final_digit  number;
  final_char   varchar2(1) := 'X';
  digit_value  number;
  total        number := 0;
  rem_value    number;
  pos          number;
  i            number;      
  j            number;      
  k            number;      
begin
-- validate the input buffer 
  mgmt_dm_buf_ad_validate(mask_value, 9);
  isbn_number := mask_value;

--generate random digts upto the 9 digits (MAX_ISBN_DIGITS-1) 
  j := DM_MAX_BUF_LEN_ISBN10_GEN - (NVL(LENGTH(mask_value), 0)) ;
  for i in 1 .. j loop
    k := FLOOR(DBMS_RANDOM.VALUE * 10);
    isbn_number := isbn_number || k;
  end loop;

-- compute the sum
  for pos in 1 .. 9 loop 
    digit_value := SUBSTR(isbn_number,pos,1);
    total := total + (digit_value * (10-pos+1));
  end loop;

-- compute the final digit/char and append
  rem_value := mod(total, 11); 

  if(rem_value != 0) then
    final_digit := 11 - rem_value;
  else
    final_digit := rem_value;
  end if;

  if(final_digit = 10) then
    isbn_number := isbn_number || final_char;
  else
    isbn_number := isbn_number || final_digit;
  end if;

-- Format the number
    fisbn_number := substr(isbn_number, 1, 1);
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 2, 2); 
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 4, 6); 
    fisbn_number := fisbn_number || format; 
    fisbn_number := fisbn_number || substr(isbn_number, 10, 1); 

  return fisbn_number;
end;
    
-- compute the UPC number
function mgmt_dm_compute_upc_number( mask_value IN varchar2,
                                     format     IN varchar2 := null)
return varchar2
is
  upc_number  varchar2(12);
  fupc_number varchar2(30);
  final_digit number;
  digit_value number;
  odd_total   number := 0;
  even_total  number := 0;
  rem_value   number;
  pos         number;
  i           number;      
  j           number;      
  k           number;      
begin
-- validate the input buffer 
  mgmt_dm_buf_ad_validate(mask_value, 11);
  upc_number := mask_value;

--generate random digts upto the 11 digits  
  j := DM_MAX_BUF_LEN_UPC_GEN - (NVL(LENGTH(mask_value), 0)) ;
  for i in 1 .. j loop
    k := FLOOR(DBMS_RANDOM.VALUE * 10);
    upc_number := upc_number || k;
  end loop;

-- compute the odd digits sum and multiply it by 3 
  for pos in 1 .. 6 loop 
    digit_value := SUBSTR(upc_number,((2*pos)-1),1);
    odd_total := odd_total + digit_value;
  end loop;
  odd_total := odd_total * 3;

-- compute the even digits sum 
  for pos in 1 .. 5 loop 
    digit_value := SUBSTR(upc_number,(2*pos),1);
    even_total := even_total + digit_value;
  end loop;

-- compute the final digit and append
  rem_value := mod((odd_total+even_total), 10); 
  if(rem_value != 0) then
    final_digit := 10 - rem_value;
  else
    final_digit := rem_value;
  end if;

-- append the final digit to get the upc number
  upc_number := upc_number || final_digit;

-- Format
-- A universal code consists of 12 digits
-- first digit (from left) indicates the UPC type
-- next 5 digits indicate manufacturer code
-- next 5 digits indicate product code assigned by the manufacturer 
-- final digit is the 'check digit' which is computed as above
    fupc_number := substr(upc_number, 1, 1);
    fupc_number := fupc_number || format; 
    fupc_number := fupc_number || substr(upc_number, 2, 5); 
    fupc_number := fupc_number || format; 
    fupc_number := fupc_number || substr(upc_number, 7, 5); 
    fupc_number := fupc_number || format; 
    fupc_number := fupc_number || substr(upc_number, 12, 1); 

  return fupc_number;
end;

-- compute the Canada SIN number
function mgmt_dm_compute_cn_sin_number( mask_value IN varchar2,
                                        format     IN varchar2 := null)
return varchar2
is
  sin_number  varchar2(12);
  fsin_number varchar2(30);
  final_digit number;
  digit_value number;
  odd_total   number := 0;
  even_total  number := 0;
  rem_value   number;
  pos         number;
  i           number;      
  j           number;      
  k           number;      
begin
-- validate the input buffer 
  mgmt_dm_buf_ad_validate(mask_value, DM_MAX_BUF_LEN_CN_SIN_GEN);
  sin_number := mask_value;

--generate random digts upto the 8 digits  
  j := DM_MAX_BUF_LEN_CN_SIN_GEN - (NVL(LENGTH(mask_value), 0)) ;
  for i in 1 .. j loop
    k := FLOOR(DBMS_RANDOM.VALUE * 10);
    sin_number := sin_number || k;
  end loop;

  k := FLOOR(DM_MAX_BUF_LEN_CN_SIN_GEN/2);

-- multiply the even digits by 2 
-- and sum (!!) the indivdual digits of resultant numbers  
  for pos in 1 .. k loop 
    digit_value := SUBSTR(sin_number,(2*pos),1);
    digit_value := (digit_value*2);
--  if we have say 15 (1+5 is 6 OR just do 15-9)
    if(digit_value > 9) then
      digit_value := digit_value - 9;
    end if;
    even_total := even_total + digit_value;
  end loop;

-- compute the odd digits sum 
  for pos in 1 .. k loop 
    digit_value := SUBSTR(sin_number,((2*pos)-1),1);
    odd_total := odd_total + digit_value;
  end loop;

-- compute the final digit and append
  rem_value := mod((odd_total+even_total), 10); 
  if(rem_value != 0) then
    final_digit := 10 - rem_value;
  else
    final_digit := rem_value;
  end if;

-- append the final digit to get the upc number
  sin_number := sin_number || final_digit;

-- Format
-- Canada SIN is of format XXX-XXX-XXX 
    fsin_number := substr(sin_number, 1, 3);
    fsin_number := fsin_number || format; 
    fsin_number := fsin_number || substr(sin_number, 4, 3); 
    fsin_number := fsin_number || format; 
    fsin_number := fsin_number || substr(sin_number, 7, 3); 

  return fsin_number;
end;

-- This function generates a valid Credit Card Number.  
-- It is the central routine for all DM Credit Card related post process 
-- functions. It takes 'card type', 'exsting masked value','format' and
-- length of credit card number to be generated.
-- The mask format of all Credit Cards will be acheived as follows
-- By default we generate 10 digit random number (this guarantees
--    at least 10 exp 10 unique numbers. DM has a unique random number
--    generator !!) as part of Format Rule Entry. This masked value
--    will be used by this function to generate final credit card number.
-- 
-- 1) We prefix Card specifix prefix the number generated in step 1
-- 2) If needed generate additional digits upto the 'length -1'.  
-- 3) Compute the final digit using Luhn's check
-- 4) Format the number and return it.
-- Luhn's check:
--                a) starting with the final(check) digit, double the value
--                   the value of every second digit.For example: for 16 digit
--                   number double 15, 13, 11,....etc
--                b) If doubling a number results in two digit number
--                   subtract 9 from it (or just add both digits). This will
--                   result in single digit.
--                c) Now,replace odd places with these new single
--                   digit numbers
--                d) Add up all the digits. If the final sum is pefectly
--                   divisible by 10, then it is a valid credit card number!!
-- Example: 4  5  5   2  7  2  0  4  1  2  3  4  5  6  7  8
--          8     10     14    0     2     6     10    14 -- double the odd
--          8  5  1   2  5  2  0  4  2  2  6  4  1  6  5  8 --- new number
--          8+5+1+2+5+2+0+4+2+2+6+4+1+6+5+8 = 61 not pefectly divisible by 10
--          So this number is a correct credit card number.
--          To make it valid credit card number last digit should be '7'.
--

function mgmt_dm_compute_cc_number(card_type  IN NUMBER, 
                                   mask_value IN varchar2,
                                   format     IN varchar2 := null,
                                   len        IN number := 16) 
return varchar2
is

  cc_number   varchar2(16) := null;
  fcc_number  varchar2(30) := null;
  c_type      number := card_type;
  tab_slot    number;
  i           number := 0;
  j           number := 0;
  k           number;
  total       number;
  value       number;
  pos         number;
  final_digit number;
begin

-- validate the buffer
  mgmt_dm_buf_ad_validate(mask_value,10);

--get the prefix and prefix it to mask value
  if card_type = DM_ANY_CARD then
     c_type := FLOOR(DBMS_RANDOM.value(DM_FIRST_CARD+1, DM_LAST_CARD));
  end if;

  case c_type
  when DM_MASTER_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_master_prefix_tab.count+1)));
    cc_number := (dm_master_prefix_tab(tab_slot) || mask_value); 
  when DM_VISA_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_visa_prefix_tab.count+1)));
    cc_number := (dm_visa_prefix_tab(tab_slot) || mask_value); 
  when DM_AMEX16_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_amex_prefix_tab.count+1)));
    cc_number := (dm_amex_prefix_tab(tab_slot) || mask_value); 
  when DM_AMEX15_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_amex_prefix_tab.count+1)));
    cc_number := (dm_amex_prefix_tab(tab_slot) || mask_value); 
  when DM_DISCOVER_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_discover_prefix_tab.count+1)));
    cc_number := (dm_discover_prefix_tab(tab_slot) || mask_value); 
  when DM_DINER_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_diner_prefix_tab.count+1)));
    cc_number := (dm_diner_prefix_tab(tab_slot) || mask_value); 
  when DM_ENROUTE_CARD then
    tab_slot := FLOOR(DBMS_RANDOM.value(1,(dm_enroute_prefix_tab.count+1)));
    cc_number := (dm_enroute_prefix_tab(tab_slot) || mask_value); 
  when DM_JCB16_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_jcb16_prefix_tab.count+1)));
    cc_number := (dm_jcb16_prefix_tab(tab_slot) || mask_value); 
  when DM_JCB15_CARD then
    tab_slot  := FLOOR(DBMS_RANDOM.value(1,(dm_jcb15_prefix_tab.count+1)));
    cc_number := (dm_jcb15_prefix_tab(tab_slot) || mask_value); 
  else
    -- this case should not arise, ideally should raise error here
    cc_number := (dm_master_prefix_tab(1) || mask_value); 
  end case; 

--generate random digts upto the last digit
  j := len - (LENGTH(cc_number) + 1);
  for i in 1 .. j loop
    k := FLOOR(DBMS_RANDOM.VALUE * 10);
    cc_number := cc_number || k;
  end loop;

--compute the sum: as per luhn's check
  pos := LENGTH(cc_number);
  total := 0;
  WHILE pos >= 1
  loop
    value := SUBSTR(cc_number,pos,1);
    value := value * 2;
    if value > 9 then
      value := value - 9;
    end if;

    total := total+value;

    if pos != 1 then
      value := SUBSTR(cc_number,pos-1, 1);
      total := total + value;
    end if;

    pos := pos - 2;
  end loop;

--compute the final digit and append to the cc_number 
  final_digit := ((FLOOR(total/10) + 1) * 10 - total) mod 10;
  cc_number := cc_number || final_digit;

--format it
    fcc_number := substr(cc_number, 1, 4);
    fcc_number := fcc_number || format; 
    fcc_number := fcc_number || substr(cc_number, 5, 4); 
    fcc_number := fcc_number || format; 
    fcc_number := fcc_number || substr(cc_number, 9, 4); 
    fcc_number := fcc_number || format; 
    fcc_number := fcc_number || substr(cc_number, 13, 4); 

  return fcc_number;
end;

-- ph area code validation
-- this function gets used by the mgmt_dm_na_ph_number function
-- to validate the area code (ph_code) for a given state type 
procedure mgmt_dm_ph_code_validate(ph_code     in number, 
                                   max_allowed in number,
                                   ph_buffer   in varchar2)
is
begin
  if(ph_code <= 0 or ph_code > max_allowed) then    
  DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_INVALID_PH_CODE,
                          'Invalid area code1:' || ph_code ||
                          ': in buffer ' || ph_buffer);
  end if;
end mgmt_dm_ph_code_validate;

-- Post process function to generate valid phone number
-- works for US, Canada, North America and all US and Canadian states
-- 1) Usa, Canada and North Ameirca are special 'st_codes'.
-- 2) The primary purpose of this function is to act as 'post process
--    function' and modify the input (mask_value) into a valid phone
--    number for the given 'st_code'.
-- 3) To explain this function, let us consider 'USA' as our  st_code
-- 4) At the time of writing this function USA has '284' valid area  codes
--    i.e. phone numbers starting with 510-xxx-xxxx OR 408-xxx-xxxx etc.
-- 5) The 'mask_value' is a Random Number generated by DM masking format.
--    For st_code 'USA', we enforce the mask_value to be between
--    001-000-0000 to 279-999-9999: approximately 2.8B numbers.   
--    We will explain shortly on why restricted the max relative area code
--    to 279 when we have 284 valid area codes. 
-- 6) The first three digits indicate the relative area code. Using this 
--    relative area code, we map it into acutual area codes such as 510 etc
-- 7) The next 3 digits are exchange area codes (EAC)
--    Some of the EAC are invalid numbers: 000, 555, [0-9]11
--    For example: we can guaranteely say a number like 5109111415 is not a
--    valid phone number.  
--    So for every valid are code such as 510: we lose about 12*10K numbers.
-- 8) As our current RN mechanism doesn't have capabilities to avoid the 
--    generation of these invalid numbers, the RN mechanism 
--    001-000-0000 to 279-999-9999 will generate some invalid phone numbers
-- 9) This necessiates us to post process this RN output and remap the input
--    to valid phone numbers. 
--    The remapping has two parts: a) map the 'relative area code' to actual
--    area code.  
--    b) map the invalid phone numbers such as 510-911-1415 to valid phone
--       numbers.   
-- 10)The above information leads us to have some 'reserve' area codes
--    so that we can use them for remapping purposes.
-- 11)As we observed: each valid area code has 120K invalid phone number 
--    combinations   
-- 12)Our goal is to map: every 'invalid' phone number of a valid area code
--    to a valid phone number in one of our 'reserved' area codes.   
-- Strategy: consider phone number digits as [xyz]-[uvw]-[abcd]
--         1)For every valid [xyz] we need '12' numbers in 'uvw' of a reserved
--           area code.
--         2)We add additional '4' numbers to make  coding complexity simpler,
--           So in total we need 16 numbers 'uvw' of a reserved area code
--           for every valid 'xyz'.
--         3)The above fact implies we can pack '60' valid area code's invalid
--           phone number combinations into a  resereved area code and turn
--           those invalid phone numbers to valid phone numbers.
--         4) For 279 valid area codes we need '5' reserved area codes.    
--            This should explain why we restricted USA RN generation to
--            279-999-9999  
-- Remapping: 1) relative area code to actual area code is simple enough
--            2) Invalid phone number of a valid area code
--               for example: 001-011-2345 (*11 in the middle is invalid)   
--            3) Usually 001 relative code maps to '205' 
--               (look at dm_all_ph_prefix_tab for relative to actual mapping)
--            4) But now that we have a invalid phone number 011-2345
--               We remap this generated input to a number in 'reserved' area
--            5) As mentioned in strategy: the first '60' valid area codes
--               gets mapped to first 'reserved' area code. i.e., 
--               'relative area code: 284 which is in turn equal to 
--               actual area code '307'.
--            6) Now we map the rest of the number, 011-2345.
--            7) As mentioned in strategy, we use first 16 dgits
--               001-015 in uvw portion of this reserved area code to map
--               the invalid phone numbers of 001 area code.
--               i.e. 001-011-0001 becomes 284-001-0001
--                    001-111-0001 becomes 284-002-0001 etc.
--                    001-911-0001 becomes 284-010-0001 etc.
--            8) More example: let us say RN generated a number
--               like: 002-011-0001: it will be mapped to 284-017-0001   
--               note: 001 has reserved 16 digits in 284-001 to 284-016
--            9) corner cases: While mapping invalid phone numbers
--                     to valid phone numbers in reserved area codes
--                     we might encounter invalid numbers (!!) in 
--                     reserved areas. To over come this: We use the
--                     spare digits (16-12: 4)
--               for [0-9]11 combination: we use 11th digit as spare digit
--                    511 combination: default uses 12 the digit as remap.
--                        but if that lands us into a invalid combination
--                        we use the 13 th digit.
--                    000 combination we use 14 digit as default remap  
--                        but if that lands us into trouble we use 15 the digit

function mgmt_dm_na_ph_number(st_code     IN NUMBER, 
                              mask_value  IN varchar2,
                              mask_val_ml IN NUMBER,
                              format      IN varchar2)
return varchar2
is
  ph_number            varchar2(10);
  fph_number           varchar2(30);
  len_mv               number;
  ph_code              number;
  tab_slot             number;
  new_mv               varchar2(10);
  i                    number := 1;
  j                    number := 1;
  mid_three            number;
  new_mid_three        number;
  last_four            number;
  max_ac               number;
  remap_ac             number;
  start_ac             number;
  remap_three_base     number;
  remap_ac_index       number;
  remap_ac_rel         number;
  new_rm_thr_base      number;
  ph_code_rem          number;
  rd_7digit            number;
  

begin
-- validate the input buffer 
  mgmt_dm_buf_ad_validate(mask_value, mask_val_ml);

-- Even if users just use this post processing function 
-- with out creating RNs,  they will still get good phone numbers
-- But using just this post process function may not guarantee 
-- unique generation of numbers. If users want unique phone numbers
-- they have to use one of pre-defined formats, which uses RN scheme to
-- guarantee unique numbers, before getting processed further by this 
-- function 
  len_mv := NVL(LENGTH(mask_value), 0);

-- Generate a number between 0130000 and 9999999
  if(len_mv = 0) then
    rd_7digit := FLOOR(DBMS_RANDOM.value(0130000,9999999));
    if(rd_7digit < 1000000) then
      new_mv := '0' || rd_7digit;
    else
      new_mv := rd_7digit;
    end if; 
  elsif ( len_mv >= 7 and len_mv <= mask_val_ml) then
    new_mv := mask_value;
  else
    DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_INVALID_BUF_LEN,
                            'Input buffer:' || mask_value ||
                            ': length should be between ' || '7' || 'and'||
                             mask_val_ml || 'inclusive');
  end if;

-- compute starting and maximum area codes for a given st_code 
  start_ac := dm_all_ph_stnb_tab((2*st_code)+1);
  max_ac   := dm_all_ph_stnb_tab((2*st_code)+2);
  remap_ac := FLOOR((max_ac)/60)+1;
  remap_three_base := 001;

--get ph code   
--if we generated all the last 7 digits in this function 
--then we choose area randomly
--else; we expect the user to pass in the 'relative' ph area code
--that will be converted to actual phone area code
  if(len_mv = 0) then
    if(max_ac > 1) then
      ph_code := FLOOR(DBMS_RANDOM.value(1,(max_ac-remap_ac+1)));
      new_mv  := (ph_code) || new_mv;
    else
      new_mv  := '0' || new_mv;
    end if;
  end if;

-- now that we have a full phone (phcode+7 digit number)
-- parse it and perform the remap operation 
  len_mv  := LENGTH(new_mv);
  ph_code := NVL(SUBSTR(new_mv, 0,(len_mv-7)), 0); 
  mid_three := SUBSTR(new_mv,(len_mv-7)+1, 3);
  last_four := SUBSTR(new_mv,(len_mv-7)+3+1, 4);

-- remap black magic !!  
  if(max_ac > 1) then
--  check if the ph_code is in range for us to proceed
    mgmt_dm_ph_code_validate(ph_code,max_ac-remap_ac,new_mv);

    if(mid_three = 011 or mid_three = 111 or mid_three = 211 or
       mid_three = 311 or mid_three = 411 or mid_three = 511 or
       mid_three = 611 or mid_three = 711 or mid_three = 811 or
       mid_three = 911 or mid_three = 555 or mid_three = 000) then
-- first we determine the new ph area  code
-- Each remap(reserved) area code serves '60' phone area codes
-- remap_ac_index
       remap_ac_index := FLOOR((ph_code-1)/60) + 1; 
       tab_slot := (start_ac + max_ac - remap_ac_index); 
-- Each work area ph.code gets 16 digits in the mid three of
-- 011,111,211,... 911 needs 10 digits for every area code
-- if base+digit gets into *11 or 555 patten we need an extra digit   
-- so total 11
-- now 555 needs 1 + 1 (buffer): 2 digits
-- now 000 needs 1 + 1 (buffer): 2 digits
-- Total: 15 digits: 1 bit is reserved (just in case!!)  
-- remapped area code.
       remap_ac_rel := MOD((ph_code-1), 60);
       new_rm_thr_base := remap_three_base + (remap_ac_rel*16);  
       if(mid_three = 555) then
         new_mid_three := new_rm_thr_base + 11;
         if(new_mid_three = 011 or new_mid_three = 111 or new_mid_three = 211 or
           new_mid_three = 311 or new_mid_three = 411 or new_mid_three = 511 or
           new_mid_three = 611 or new_mid_three = 711 or new_mid_three = 811 or
           new_mid_three = 911 or new_mid_three = 555) then

           new_mid_three := new_rm_thr_base + 12;
         end if;
       elsif(mid_three = 000) then
         new_mid_three := new_rm_thr_base + 13;
         if(new_mid_three = 011 or new_mid_three = 111 or new_mid_three = 211 or
           new_mid_three = 311 or new_mid_three = 411 or new_mid_three = 511 or
           new_mid_three = 611 or new_mid_three = 711 or new_mid_three = 811 or
           new_mid_three = 911 or new_mid_three = 555 or 
           new_mid_three = 000) then

           new_mid_three := new_rm_thr_base + 14;
         end if;
       else 
         new_mid_three := new_rm_thr_base + FLOOR(mid_three/100);
         if(new_mid_three = 011 or new_mid_three = 111 or new_mid_three = 211 or
           new_mid_three = 311 or new_mid_three = 411 or new_mid_three = 511 or
           new_mid_three = 611 or new_mid_three = 711 or new_mid_three = 811 or
           new_mid_three = 911 or new_mid_three = 555) then

           new_mid_three := new_rm_thr_base + 10;
          end if;
       end if; 
    else 
       tab_slot := (start_ac + ph_code - 1); 
       new_mid_three := mid_three;
    end if;
  else
--  error checks for input buffer 
    if(ph_code != 0) then
      DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_INVALID_PH_CODE,
                          'Invalid area code2:' || ph_code ||
                          ': in buffer ' || new_mv);
    end if;
    if(mid_three < 013) then
      DBMS_STANDARD.RAISE_APPLICATION_ERROR(DM_FL_E_INVALID_PH_BUFFER,
                          'Invalid input buffer:' || new_mv ||
                          ': it should be greater than ' || '0130000');
    end if;

    if(mid_three = 011 or mid_three = 111 or mid_three = 211 or
       mid_three = 311 or mid_three = 411 or mid_three = 511 or
       mid_three = 611 or mid_three = 711 or mid_three = 811 or
       mid_three = 911) then
       tab_slot := start_ac; 
       ph_code_rem := FLOOR(mid_three/100);
       new_mid_three := remap_three_base + ph_code_rem;
    elsif(mid_three = 555) then
       tab_slot := start_ac;
       new_mid_three := 012;
    else 
       tab_slot := (start_ac); 
       new_mid_three := mid_three;
    end if;
  end if;

--remap and get the number
  if(new_mid_three < 10) then
    ph_number := (dm_all_ph_prefix_tab(tab_slot) || '00' || new_mid_three); 
  elsif(new_mid_three < 100) then
    ph_number := (dm_all_ph_prefix_tab(tab_slot) || '0' || new_mid_three); 
  else
    ph_number := (dm_all_ph_prefix_tab(tab_slot) || new_mid_three); 
  end if;
 
  if(last_four < 10) then
    ph_number := ph_number || '000' || last_four;
  elsif(last_four < 100) then
    ph_number := ph_number || '00' || last_four;
  elsif(last_four < 1000) then
    ph_number := ph_number || '0' || last_four;
  else
    ph_number := ph_number || last_four;
  end if;

-- format the number 
    fph_number := substr(ph_number, 1, 3);
    fph_number := fph_number || format; 
    fph_number := fph_number || substr(ph_number, 4, 3); 
    fph_number := fph_number || format; 
    fph_number := fph_number || substr(ph_number, 7, 4); 

  return fph_number;
end;

-- ************************************************************************** --
--                                                                            --
--          Package PUBLIC procedure and function implementations             --
--                                                                            --
-- ************************************************************************** --

-- Generate a Proper SSN
function mgmt_dm_gen_ssn(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ssn_number varchar2(30);
begin
  ssn_number := mgmt_dm_compute_ssn_number(p_buff, DM_SSN_FORMAT);
  return ssn_number;
end;

function mgmt_dm_gen_ssn_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ssn_number varchar2(30);
begin
  ssn_number := mgmt_dm_compute_ssn_number(p_buff, '-');
  return ssn_number;
end;

-- Generate Master card
function mgmt_dm_gen_mc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_MASTER_CARD,
                                         p_buff,
                                         DM_CC_FORMAT,
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Master card with ' ' (space) as format as space 
function mgmt_dm_gen_mc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_MASTER_CARD,
                                         p_buff,
                                         ' ',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Master card with format as hyphen ('-') 
function mgmt_dm_gen_mc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_MASTER_CARD,
                                         p_buff,
                                         '-',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate VISA card
function mgmt_dm_gen_vc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);
begin
  cc_number := mgmt_dm_compute_cc_number(DM_VISA_CARD,
                                         p_buff,
                                         DM_CC_FORMAT,
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate VISA card with 'space' as format
function mgmt_dm_gen_vc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  DM_VISA_CARD     constant number := 2;
  DM_CARD_LEN_16   constant number := 16;  
  cc_number varchar2(30);
begin
  cc_number := mgmt_dm_compute_cc_number(DM_VISA_CARD,
                                         p_buff,
                                         ' ',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate VISA card with 'hyphen' as format
function mgmt_dm_gen_vc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  DM_VISA_CARD     constant number := 2;
  DM_CARD_LEN_16   constant number := 16;  
  cc_number varchar2(30);
begin
  cc_number := mgmt_dm_compute_cc_number(DM_VISA_CARD,
                                         p_buff,
                                         '-',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate AMEX card
function mgmt_dm_gen_ac(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);
begin
  cc_number := mgmt_dm_compute_cc_number(DM_AMEX16_CARD,
                                         p_buff,
                                         DM_CC_FORMAT,
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate AMEX card in 'space' format
function mgmt_dm_gen_ac_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);
begin
  cc_number := mgmt_dm_compute_cc_number(DM_AMEX16_CARD,
                                         p_buff,
                                         ' ',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate AMEX card in 'hyphen' format
function mgmt_dm_gen_ac_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);
begin
  cc_number := mgmt_dm_compute_cc_number(DM_AMEX16_CARD,
                                         p_buff,
                                         '-',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate DISCOVER card
function mgmt_dm_gen_disc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_DISCOVER_CARD,
                                         p_buff,
                                         DM_CC_FORMAT,
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate DISCOVER card with 'space' format
function mgmt_dm_gen_disc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_DISCOVER_CARD,
                                         p_buff,
                                         ' ',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate DISCOVER card with 'hyphen' format
function mgmt_dm_gen_disc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_DISCOVER_CARD,
                                         p_buff,
                                         '-',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate DINER card
function mgmt_dm_gen_dinc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_DINER_CARD,
                                         p_buff,
                                         DM_CC_FORMAT,
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate DINER card with 'space' as format
function mgmt_dm_gen_dinc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_DINER_CARD,
                                         p_buff,
                                         ' ',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate DINER card with 'hyphen' as format
function mgmt_dm_gen_dinc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_DINER_CARD,
                                         p_buff,
                                         '-',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate ANY card
function mgmt_dm_gen_anyc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_ANY_CARD,
                                         p_buff,
                                         DM_CC_FORMAT,
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate ANY card with space as its format
function mgmt_dm_gen_anyc_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_ANY_CARD,
                                         p_buff,
                                         ' ',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate ANY card with hyphen as its format
function mgmt_dm_gen_anyc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  cc_number varchar2(30);

begin
  cc_number := mgmt_dm_compute_cc_number(DM_ANY_CARD,
                                         p_buff,
                                         '-',
                                         DM_CARD_LEN_16); 
  return cc_number;
end;

-- Generate North America phone numbers
function mgmt_dm_gen_ph_na(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_PH_NA_ANY, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

-- Generate North America phone numbers with '-' format 
function mgmt_dm_gen_ph_na_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);
  buf_len   number:=0;

begin
  ph_number := mgmt_dm_na_ph_number(DM_PH_NA_ANY, p_buff, 10,  '-'); 
  return ph_number;
end;

-- Generate US phone numbers
function mgmt_dm_gen_ph_usa(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_PH_USA_ANY, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

-- Generate US phone numbers
function mgmt_dm_gen_ph_usa_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_PH_USA_ANY, p_buff, 10, '-'); 
  return ph_number;
end;

-- Generate USA state phone numbers
--AL
function mgmt_dm_gen_ph_usa_al(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AL, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--AK
function mgmt_dm_gen_ph_usa_ak(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AK, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--AZ
function mgmt_dm_gen_ph_usa_az(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AZ, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--AR
function mgmt_dm_gen_ph_usa_ar(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AR, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--CO
function mgmt_dm_gen_ph_usa_co(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_CO, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--CA
function mgmt_dm_gen_ph_usa_ca(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_CA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--CT
function mgmt_dm_gen_ph_usa_ct(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_CT, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--DE
function mgmt_dm_gen_ph_usa_de(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_DE, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--DC
function mgmt_dm_gen_ph_usa_dc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_DC, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--FL
function mgmt_dm_gen_ph_usa_fl(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_FL, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--GA
function mgmt_dm_gen_ph_usa_ga(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_GA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--HI
function mgmt_dm_gen_ph_usa_hi(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_HI, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--ID
function mgmt_dm_gen_ph_usa_id(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_ID, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--IL
function mgmt_dm_gen_ph_usa_il(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_IL, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--IN
function mgmt_dm_gen_ph_usa_in(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_IN, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--IA
function mgmt_dm_gen_ph_usa_ia(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_IA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--KS
function mgmt_dm_gen_ph_usa_ks(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_KS, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--KY
function mgmt_dm_gen_ph_usa_ky(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_KY, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--LA
function mgmt_dm_gen_ph_usa_la(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_LA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--ME
function mgmt_dm_gen_ph_usa_me(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_ME, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MD
function mgmt_dm_gen_ph_usa_md(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MD, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MA
function mgmt_dm_gen_ph_usa_ma(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MI
function mgmt_dm_gen_ph_usa_mi(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MI, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MN
function mgmt_dm_gen_ph_usa_mn(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MN, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MS
function mgmt_dm_gen_ph_usa_ms(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MS, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MO
function mgmt_dm_gen_ph_usa_mo(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MO, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--MT
function mgmt_dm_gen_ph_usa_mt(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MT, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NE
function mgmt_dm_gen_ph_usa_ne(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NE, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NH
function mgmt_dm_gen_ph_usa_nh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NH, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NJ
function mgmt_dm_gen_ph_usa_nj(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NJ, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NM
function mgmt_dm_gen_ph_usa_nm(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NM, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NV
function mgmt_dm_gen_ph_usa_nv(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NV, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NY
function mgmt_dm_gen_ph_usa_ny(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NY, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--NC
function mgmt_dm_gen_ph_usa_nc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NC, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--ND
function mgmt_dm_gen_ph_usa_nd(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_ND, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--OH
function mgmt_dm_gen_ph_usa_oh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_OH, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--OK
function mgmt_dm_gen_ph_usa_ok(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_OK, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--OR
function mgmt_dm_gen_ph_usa_or(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_OR, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--PA
function mgmt_dm_gen_ph_usa_pa(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_PA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--RI
function mgmt_dm_gen_ph_usa_ri(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_RI, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--SC
function mgmt_dm_gen_ph_usa_sc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_SC, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--SD
function mgmt_dm_gen_ph_usa_sd(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_SD, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--TN
function mgmt_dm_gen_ph_usa_tn(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_TN, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--TX
function mgmt_dm_gen_ph_usa_tx(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_TX, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--UT
function mgmt_dm_gen_ph_usa_ut(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_UT, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--VT
function mgmt_dm_gen_ph_usa_vt(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_VT, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--VA
function mgmt_dm_gen_ph_usa_va(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_VA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--WA
function mgmt_dm_gen_ph_usa_wa(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WA, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--WV
function mgmt_dm_gen_ph_usa_wv(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WV, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--WI
function mgmt_dm_gen_ph_usa_wi(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WI, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

--WY
function mgmt_dm_gen_ph_usa_wy(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WY, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

-- US states with Format
--AL
function mgmt_dm_gen_ph_usa_al_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AL, p_buff, 10, '-'); 
  return ph_number;
end;

--AK
function mgmt_dm_gen_ph_usa_ak_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AK, p_buff, 10, '-'); 
  return ph_number;
end;

--AZ
function mgmt_dm_gen_ph_usa_az_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AZ, p_buff, 10, '-'); 
  return ph_number;
end;

--AR
function mgmt_dm_gen_ph_usa_ar_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_AR, p_buff, 10, '-'); 
  return ph_number;
end;

--CO
function mgmt_dm_gen_ph_usa_co_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_CO, p_buff, 10, '-'); 
  return ph_number;
end;

--CA
function mgmt_dm_gen_ph_usa_ca_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_CA, p_buff, 10, '-'); 
  return ph_number;
end;

--CT
function mgmt_dm_gen_ph_usa_ct_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_CT, p_buff, 10, '-'); 
  return ph_number;
end;

--DE
function mgmt_dm_gen_ph_usa_de_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_DE, p_buff, 10, '-'); 
  return ph_number;
end;

--DC
function mgmt_dm_gen_ph_usa_dc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_DC, p_buff, 10, '-'); 
  return ph_number;
end;

--FL
function mgmt_dm_gen_ph_usa_fl_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_FL, p_buff, 10, '-'); 
  return ph_number;
end;

--GA
function mgmt_dm_gen_ph_usa_ga_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_GA, p_buff, 10, '-'); 
  return ph_number;
end;

--HI
function mgmt_dm_gen_ph_usa_hi_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_HI, p_buff, 10, '-'); 
  return ph_number;
end;

--ID
function mgmt_dm_gen_ph_usa_id_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_ID, p_buff, 10, '-'); 
  return ph_number;
end;

--IL
function mgmt_dm_gen_ph_usa_il_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_IL, p_buff, 10, '-'); 
  return ph_number;
end;

--IN
function mgmt_dm_gen_ph_usa_in_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_IN, p_buff, 10, '-'); 
  return ph_number;
end;

--IA
function mgmt_dm_gen_ph_usa_ia_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_IA, p_buff, 10, '-'); 
  return ph_number;
end;

--KS
function mgmt_dm_gen_ph_usa_ks_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_KS, p_buff, 10, '-'); 
  return ph_number;
end;

--KY
function mgmt_dm_gen_ph_usa_ky_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_KY, p_buff, 10, '-'); 
  return ph_number;
end;

--LA
function mgmt_dm_gen_ph_usa_la_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_LA, p_buff, 10, '-'); 
  return ph_number;
end;

--ME
function mgmt_dm_gen_ph_usa_me_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_ME, p_buff, 10, '-'); 
  return ph_number;
end;

--MD
function mgmt_dm_gen_ph_usa_md_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MD, p_buff, 10, '-'); 
  return ph_number;
end;

--MA
function mgmt_dm_gen_ph_usa_ma_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MA, p_buff, 10, '-'); 
  return ph_number;
end;

--MI
function mgmt_dm_gen_ph_usa_mi_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MI, p_buff, 10, '-'); 
  return ph_number;
end;

--MN
function mgmt_dm_gen_ph_usa_mn_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MN, p_buff, 10, '-'); 
  return ph_number;
end;

--MS
function mgmt_dm_gen_ph_usa_ms_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MS, p_buff, 10, '-'); 
  return ph_number;
end;

--MO
function mgmt_dm_gen_ph_usa_mo_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MO, p_buff, 10, '-'); 
  return ph_number;
end;

--MT
function mgmt_dm_gen_ph_usa_mt_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_MT, p_buff, 10, '-'); 
  return ph_number;
end;

--NE
function mgmt_dm_gen_ph_usa_ne_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NE, p_buff, 10, '-'); 
  return ph_number;
end;

--NH
function mgmt_dm_gen_ph_usa_nh_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NH, p_buff, 10, '-'); 
  return ph_number;
end;

--NJ
function mgmt_dm_gen_ph_usa_nj_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NJ, p_buff, 10, '-'); 
  return ph_number;
end;

--NM
function mgmt_dm_gen_ph_usa_nm_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NM, p_buff, 10, '-'); 
  return ph_number;
end;

--NV
function mgmt_dm_gen_ph_usa_nv_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NV, p_buff, 10, '-'); 
  return ph_number;
end;

--NY
function mgmt_dm_gen_ph_usa_ny_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NY, p_buff, 10, '-'); 
  return ph_number;
end;

--NC
function mgmt_dm_gen_ph_usa_nc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_NC, p_buff, 10, '-'); 
  return ph_number;
end;

--ND
function mgmt_dm_gen_ph_usa_nd_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_ND, p_buff, 10, '-'); 
  return ph_number;
end;

--OH
function mgmt_dm_gen_ph_usa_oh_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_OH, p_buff, 10, '-'); 
  return ph_number;
end;

--OK
function mgmt_dm_gen_ph_usa_ok_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_OK, p_buff, 10, '-'); 
  return ph_number;
end;

--OR
function mgmt_dm_gen_ph_usa_or_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_OR, p_buff, 10, '-'); 
  return ph_number;
end;

--PA
function mgmt_dm_gen_ph_usa_pa_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_PA, p_buff, 10, '-'); 
  return ph_number;
end;

--RI
function mgmt_dm_gen_ph_usa_ri_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_RI, p_buff, 10, '-'); 
  return ph_number;
end;

--SC
function mgmt_dm_gen_ph_usa_sc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_SC, p_buff, 10, '-'); 
  return ph_number;
end;

--SD
function mgmt_dm_gen_ph_usa_sd_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_SD, p_buff, 10, '-'); 
  return ph_number;
end;

--TN
function mgmt_dm_gen_ph_usa_tn_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_TN, p_buff, 10, '-'); 
  return ph_number;
end;

--TX
function mgmt_dm_gen_ph_usa_tx_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_TX, p_buff, 10, '-'); 
  return ph_number;
end;

--UT
function mgmt_dm_gen_ph_usa_ut_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_UT, p_buff, 10, '-'); 
  return ph_number;
end;

--VT
function mgmt_dm_gen_ph_usa_vt_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_VT, p_buff, 10, '-'); 
  return ph_number;
end;

--VA
function mgmt_dm_gen_ph_usa_va_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_VA, p_buff, 10, '-'); 
  return ph_number;
end;

--WA
function mgmt_dm_gen_ph_usa_wa_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WA, p_buff, 10, '-'); 
  return ph_number;
end;

--WV
function mgmt_dm_gen_ph_usa_wv_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WV, p_buff, 10, '-'); 
  return ph_number;
end;

--WI
function mgmt_dm_gen_ph_usa_wi_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WI, p_buff, 10, '-'); 
  return ph_number;
end;

--WY
function mgmt_dm_gen_ph_usa_wy_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_USA_PH_WY, p_buff, 10, '-'); 
  return ph_number;
end;

-- Generate Canada phone numbers
function mgmt_dm_gen_ph_canada(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_PH_CANADA_ANY, p_buff, 10, DM_PH_FORMAT); 
  return ph_number;
end;

function mgmt_dm_gen_ph_canada_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  ph_number varchar2(30);

begin
  ph_number := mgmt_dm_na_ph_number(DM_PH_CANADA_ANY, p_buff, 10, '-'); 
  return ph_number;
end;

-- Generate ISBN10 numbers
function mgmt_dm_gen_isbn10(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  isbn_number varchar2(30);
begin
  isbn_number := mgmt_dm_compute_isbn10_number(p_buff, DM_ISBN_FORMAT); 
  return isbn_number;
end;

-- Generate ISBN numbers with Format
function mgmt_dm_gen_isbn10_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  isbn_number varchar2(30);
begin
  isbn_number := mgmt_dm_compute_isbn10_number(p_buff, '-'); 
  return isbn_number;
end;

-- Generate ISBN13 numbers
function mgmt_dm_gen_isbn13(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  isbn_number varchar2(30);
begin
  isbn_number := mgmt_dm_compute_isbn13_number(p_buff, DM_ISBN_FORMAT); 
  return isbn_number;
end;

-- Generate ISBN numbers with Format
function mgmt_dm_gen_isbn13_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  isbn_number varchar2(30);
begin
  isbn_number := mgmt_dm_compute_isbn13_number(p_buff, '-'); 
  return isbn_number;
end;

-- Generate UPC numbers
function mgmt_dm_gen_upc(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  upc_number varchar2(30);
begin
  upc_number := mgmt_dm_compute_upc_number(p_buff, DM_UPC_FORMAT); 
  return upc_number;
end;

-- Generate UPC numbers with Format
function mgmt_dm_gen_upc_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  upc_number varchar2(30);
begin
  upc_number := mgmt_dm_compute_upc_number(p_buff, '-'); 
  return upc_number;
end;

-- Generate Canda SIN numbers 
function mgmt_dm_gen_cn_sin(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  sin_number varchar2(30);
begin
  sin_number := mgmt_dm_compute_cn_sin_number(p_buff, DM_CN_SIN_FORMAT); 
  return sin_number;
end;

-- Generate Canda SIN numbers with Format
function mgmt_dm_gen_cn_sin_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  sin_number varchar2(30);
begin
  sin_number := mgmt_dm_compute_cn_sin_number(p_buff, '-'); 
  return sin_number;
end;

-- Generate Canda SIN numbers with Space Format
function mgmt_dm_gen_cn_sin_fs(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
  sin_number varchar2(30);
begin
  sin_number := mgmt_dm_compute_cn_sin_number(p_buff, ' '); 
  return sin_number;
end;

-- Generate UK SIN numbers 
function mgmt_dm_gen_uk_nin_fh(rid varchar2, column_name varchar2, p_buff varchar2) return varchar2
is
begin
    return substr(p_buff,1,2) || ' ' || substr(p_buff,3,2) || ' ' || substr(p_buff,5,2) || ' ' || substr(p_buff,7,2) || ' ' || substr(p_buff,9,1);
end;

END DM_FMTLIB;
/
SHOW ERRORS;