Rem drv: <create type="post_creation"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/masking/dm_post_creation.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $
Rem
Rem dm_post_creation.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dm_post_creation.sql - Data Masking post-repository creation script.
Rem
Rem    DESCRIPTION
Rem      This file contains a set of pre-defined formats to use with Data Masking.
Rem      These formats will appear in the Data Masking Format Library and can be
Rem      imported into data masking definitions.  Our goal with this is to provide 
Rem      a set of well-defined masking formats out-of-the-box for customers.   
Rem
Rem    NOTES
Rem      If this file is modified, the same modifications must be made to 
Rem      dm_data_upgrade.sql in the ../../11.2.0.0/masking directory
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kmckeen     10/28/08 - Created
Rem

-- --------------------------------------------------+
-- For debugging only. Use with caution. These deletes will remove 
-- all the formats from the format library, along with any formats
-- defined for existing masking definitions.
-- --------------------------------------------------+
-- delete SYSMAN.MGMT_DM_ALITEMS;
-- delete SYSMAN.MGMT_DM_RULEENTRY;
-- delete SYSMAN.MGMT_DM_RULETEMPLATES;
-- --------------------------------------------------+

BEGIN
  DECLARE
    -- --------------------------------------------------+
    -- The names and descriptions are provided here in English for the 
    -- 10.2.0.5 out-of-the-box formats. In a later release, we may be able 
    -- to provide translated names and descriptions.
    --
    -- If you would like to have the names and descriptions in some other
    -- language for the out-of-the-box format library formats, change them 
    -- here and run this script on your repository database in the SYSMAN 
    -- account. If you no longer want to see the English versions, you can
    -- remove them from the format library using the Data Masking Format 
    -- Library UI from EM Grid Control.
    -- --------------------------------------------------+
    l_amex_name       CHAR(40)   := 'American Express Credit Card Number';
    l_amex_desc       CHAR(2000) := '~10 billion unique American Express credit card numbers';
    l_disc_name       CHAR(40)   := 'Discover Card Credit Card Number';
    l_disc_desc       CHAR(2000) := '~10 billion unique Discover Card credit card numbers';
    l_mcrd_name       CHAR(40)   := 'MasterCard Credit Card Number';
    l_mcrd_desc       CHAR(2000) := '~10 billion unique MasterCard credit card numbers';
    l_visa_name       CHAR(40)   := 'Visa Credit Card Number';
    l_visa_desc       CHAR(2000) := '~10 billion unique Visa credit card numbers';
    l_gencc_name      CHAR(40)   := 'Generic Credit Card Number';
    l_gencc_desc      CHAR(2000) := '~10 billion unique generic credit card numbers';
    l_gencc_name_fmt  CHAR(40)   := 'Generic Credit Card Number Formatted';
    l_gencc_desc_fmt  CHAR(2000) := '~10 billion unique generic credit card numbers';
    l_nin_name_fmt    CHAR(40)   := 'National Insurance Number Formatted';
    l_nin_desc_fmt    CHAR(2000) := 'Generates unique UK National Insurance Numbers';
    l_sin_name        CHAR(40)   := 'Social Insurance Number';
    l_sin_desc        CHAR(2000) := '~1 billion unique Canadian Social Insurance Numbers';
    l_sin_name_fmt    CHAR(40)   := 'Social Insurance Number Formatted';
    l_sin_desc_fmt    CHAR(2000) := '~1 billion unique Canadian Social Insurance Numbers';
    l_ssn_name        CHAR(40)   := 'Social Security Number';
    l_ssn_desc        CHAR(2000) := '~718 million unique US Social Security Numbers';
    l_ssn_name_fmt    CHAR(40)   := 'Social Security Number Formatted';
    l_ssn_desc_fmt    CHAR(2000) := '~718 million unique US Social Security Numbers';
    l_isbn10_name     CHAR(40)   := 'ISBN (Ten Digit)';
    l_isbn10_desc     CHAR(2000) := '~1 billion unique ISBN numbers';
    l_isbn10_name_fmt CHAR(40)   := 'ISBN (Ten Digit) Formatted';
    l_isbn10_desc_fmt CHAR(2000) := '~1 billion unique ISBN numbers';
    l_isbn13_name     CHAR(40)   := 'ISBN (Thirteen Digit)';
    l_isbn13_desc     CHAR(2000) := '~2 billion unique ISBN numbers';
    l_isbn13_name_fmt CHAR(40)   := 'ISBN (Thirteen Digit) Formatted';
    l_isbn13_desc_fmt CHAR(2000) := '~2 billion unique ISBN numbers';
    l_upc_name        CHAR(40)   := 'UPC Number';
    l_upc_desc        CHAR(2000) := '~100 billion UPC numbers';
    l_upc_name_fmt    CHAR(40)   := 'UPC Number Formatted';
    l_upc_desc_fmt    CHAR(2000) := '~100 billion UPC numbers';
    l_usaph_name      CHAR(40)   := 'USA Phone Number';
    l_usaph_desc      CHAR(2000) := '~2.7 billion unique USA phone numbers';
    l_usaph_name_fmt  CHAR(40)   := 'USA Phone Number Formatted';
    l_usaph_desc_fmt  CHAR(2000) := '~2.7 billion unique USA phone numbers';

    -- --------------------------------------------------+
    l_fmt_guid    VARCHAR2(100);
    l_fmt_owner   CHAR(6)  := 'SYSMAN';
    l_udf_owner   CHAR(6)  := 'DBSNMP';
    l_udf_package CHAR(9)  := 'DM_FMTLIB';
    l_udf_prefix  CHAR(17) := l_udf_owner||'.'||l_udf_package||'.';
    l_count       INTEGER;
    -- --------------------------------------------------+

  BEGIN
    -- --------------------------------------------------+
    -- American Express Credit Card Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_amex_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_amex_name), trim(l_amex_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1,'RD', ' ', 10, 10, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_AC');
    END IF;

    -- --------------------------------------------------+
    -- Discover Card Credit Card Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_disc_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_disc_name), trim(l_disc_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1,'RD', ' ', 10, 10, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_DISC');
    END IF;

    -- --------------------------------------------------+
    -- MasterCard Credit Card Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_mcrd_name);

    IF (l_count = 0) 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_mcrd_name), trim(l_mcrd_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 10, 10, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null, 
          l_udf_prefix||'MGMT_DM_GEN_MC');
    END IF;

    -- --------------------------------------------------+
    -- Visa Credit Card Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_visa_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_visa_name), trim(l_visa_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 10, 10, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_VC');
    END IF;

    -- --------------------------------------------------+
    -- Generic Credit Card Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_gencc_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_gencc_name), trim(l_gencc_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 10, 10, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_ANYC');
    END IF;

    -- --------------------------------------------------+
    -- Generic Credit Card Number Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_gencc_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_gencc_name_fmt), trim(l_gencc_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 10, 10, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_ANYC_FH');
    END IF;

    -- --------------------------------------------------+
    -- National Insurance Number Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_nin_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_nin_name_fmt), trim(l_nin_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'AL',' ', null, null, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'AL',' ', null, null, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 3, 'RD',' ', 6, 6, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 4, 'AL',' ', null, null, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 5, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_UK_NIN_FH');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'A');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'B');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'C');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'E');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'G');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'H');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'J');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'K');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'L');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'M');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'N');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'O');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'P');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'R');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'S');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'T');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'W');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'X');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'Y');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, 'Z');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'A');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'B');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'C');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'E');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'G');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'H');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'J');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'K');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'L');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'M');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'N');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'O');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'P');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'R');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'S');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'T');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'W');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'X');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'Y');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 2, 'Z');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 4, 'A');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 4, 'B');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 4, 'C');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 4, 'D');
    END IF;

    -- --------------------------------------------------+
    -- Social Insurance Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_sin_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_sin_name), trim(l_sin_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD',' ', 8, 8, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_CN_SIN');
    END IF;

    -- --------------------------------------------------+
    -- Social Insurance Number Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_sin_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_sin_name_fmt), trim(l_sin_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD',' ', 8, 8, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_CN_SIN_FH');
    END IF;

    -- --------------------------------------------------+
    -- Social Security Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_ssn_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_ssn_name), trim(l_ssn_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RN',' ', 020000000, 738999999, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_SSN');
    END IF;

    -- --------------------------------------------------+
    -- Social Security Number Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_ssn_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_ssn_name_fmt), trim(l_ssn_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RN',' ', 020000000, 738999999, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_SSN_FH');
    END IF;

    -- --------------------------------------------------+
    -- ISBN (Ten Digit)
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_isbn10_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_isbn10_name), trim(l_isbn10_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 9, 9, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_ISBN10');
    END IF;

    -- --------------------------------------------------+
    -- ISBN (Ten Digit) Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_isbn10_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_isbn10_name_fmt), trim(l_isbn10_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 9, 9, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_ISBN10_FH');
    END IF;

    -- --------------------------------------------------+
    -- ISBN (Thirteen Digit)
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_isbn13_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_isbn13_name), trim(l_isbn13_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'AL', ' ', null, null, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'RD', ' ', 9, 9, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 3, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_ISBN13');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, '978');
      insert into sysman.mgmt_dm_alitems values 
          (l_fmt_guid, 1, '979');
    END IF;

    -- --------------------------------------------------+
    -- ISBN (Thirteen Digit) Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_isbn13_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_isbn13_name_fmt), trim(l_isbn13_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'AL',' ', null, null, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'RD', ' ', 9, 9, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 3, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_ISBN13_FH');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, '978');
      insert into sysman.mgmt_dm_alitems values
          (l_fmt_guid, 1, '979');
    END IF;

    -- --------------------------------------------------+
    -- UPC Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_upc_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_upc_name), trim(l_upc_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 11, 11, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_UPC');
    END IF;

    -- --------------------------------------------------+
    -- UPC Number Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_upc_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_upc_name_fmt), trim(l_upc_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RD', ' ', 11, 11, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values 
          (l_fmt_guid, 2, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_UPC_FH');
    END IF;
    -- --------------------------------------------------+

    -- --------------------------------------------------+
    -- USA Phone Number
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_usaph_name);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_usaph_name), trim(l_usaph_desc), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RN', ' ', 001, 279, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'RD', ' ', 7, 7, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 3, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_PH_USA');
    END IF;

    -- --------------------------------------------------+
    -- USA Phone Number Formatted
    -- --------------------------------------------------+
    SELECT COUNT(*) INTO l_count FROM sysman.mgmt_dm_ruletemplates
    WHERE rule_owner = l_fmt_owner AND rule_name = trim(l_usaph_name_fmt);

    IF l_count = 0 
    THEN
      l_fmt_guid := SYS_GUID();
      insert into sysman.mgmt_dm_ruletemplates values
          (l_fmt_guid, l_fmt_owner, trim(l_usaph_name_fmt), trim(l_usaph_desc_fmt), 1, 1, 1, '1=1');
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 1, 'RN', ' ', 001, 279, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 2, 'RD', ' ', 7, 7, null, null, null, null, null, null, null, null);
      insert into sysman.mgmt_dm_ruleentry values
          (l_fmt_guid, 3, 'UT', ' ', null, null, null, null, '-', null, null, null, null,
          l_udf_prefix||'MGMT_DM_GEN_PH_USA_FH');
    END IF;

  END;
END;
/
COMMIT;
SHOW ERRORS;