ERA Technical Conversion Plan

Document Sample
ERA Technical Conversion Plan Powered By Docstoc
					Enforcement Action,
Administrative Review and Appeal
Tracking System (ERA)
Detailed Data Conversion Plan


Prepared for:
Ministry of Forests
Compliance and Enforcement Branch

Document Revision 1




                                    Prepared by:
Ministry of Forests                                             Detailed Data Conversion Plan
Compliance and Enforcement Branch




                              Document Change Control



REVISION NUMBER      DATE OF ISSUE       AUTHOR(S)              BRIEF DESCRIPTION OF CHANGE

1                    2002-12-13      Graham       Parker,   Original
                                     Duane Lecky and Tim
                                     Murray
2                    2002-12-16      Tim Murray             Updated values for missing codes.
3                    2003-01-24      Tim Murray             Revisions based on comments from Mark
                                                            Ismay, Ian Wood and Cassandra Mann.




                                                                                                Page 2
 Ministry of Forests                                      Detailed Data Conversion Plan
 Compliance and Enforcement Branch




                                     Document Sign-Off



The undersigned have read and agree with the content of this document.




Tim Murray                                        Cassandra Mann
Project Manager                                   Project Manager
VIVID Solutions Inc.                              Ministry of Forests
                                                  Compliance and Enforcement Branch

Date:                                             Date:




                                                                                          Page 3
  Ministry of Forests                                                              Detailed Data Conversion Plan
  Compliance and Enforcement Branch




                                               Table of Contents

1. DOCUMENT OVERVIEW ................................................................................................. 5

2. OUTSTANDING ISSUES .................................................................................................. 6

3. CONVERSION OVERVIEW .............................................................................................. 12

    3.1      ERA 3.0 TABLES ............................................................................................... 12
    3.2      EXTERNAL TABLES .......................................................................................... 12
    3.3      APPROACH ................................................................................................... 13
    3.4      ASSUMPTIONS ................................................................................................... 14


4. CONVERSION DETAILS ..................................................................................................... 16

    4.1      CONVERT LOOK UP TABLES ..................................................................................... 16
    4.2      CONVERT GENERAL CASE DATA ................................................................................. 20
    4.3      CONVERT CONTRAVENTION DATA ............................................................................... 27
    4.4      CONVERT DETERMINATION DATA................................................................................ 29
    4.5      CONVERT APPEAL DATA ........................................................................................ 30
    4.6      CONVERT CONTRAVENTION DECISION LINK DATA (DETERMINATIONS ONLY) ..................................... 36
    4.7      CONVERT ENFORCEMENT ACTION DATA ......................................................................... 37
    4.8      CONVERT COMPLIANCE ACTION DATA........................................................................... 46
    4.9      CLONE DATA FOR APPEALS ..................................................................................... 47
    4.10     SET CONTRAVENTION_ENFORCEMENT CURRENT_IND ......................................................... 48
    4.11     CONVERT SEIZURE DATA ........................................................................................ 48
    4.12     SET ERA SEQUENCES ........................................................................................... 49

5. POST CONVERSION DATA VALIDATION .............................................................................. 49

6. ROLLBACK ................................................................................................................. 49

7. APPENDIX A: ERA CONVERSION ERROR TABLE .................................................................... 51




                                                                                                                       Page 4
 Ministry of Forests                                     Detailed Data Conversion Plan
 Compliance and Enforcement Branch




1. DOCUMENT OVERVIEW

The purpose of this document is to describe the approach and tasks required to complete a
successful conversion of the existing ERA 2.1 VM database to the new ERA 3.0 Oracle
database.    Steps required prior to conversion, specific details about the data to be
converted, and post conversion tasks are described. There are a number of outstanding
issues that must be resolved before the conversion scripts can be developed.




                                                                                         Page 5
 Ministry of Forests                                        Detailed Data Conversion Plan
 Compliance and Enforcement Branch




2. OUTSTANDING ISSUES

The following issues have been identified as requiring direction from MoF before
development of the conversion procedures can be started. All outstanding issues have been
ranked with one of two severities:

 SEVERE        If not resolved, the conversion will not be able to complete successfully.
               Either there is no work around known at this time, or the problematic data
               must be completely ignored.
 MODERATE      If not resolved, the conversion can still be completed, but some data may be
               left incomplete. A work around for the issue exists that may or may not
               require ignoring the data.

1. (Severe) Several Admin cases have been identified within ERA2.1 that do not have
   violation entries. Previous discussions stated that Violations contain the linkages to
   determine which Act/Reg was under review for an admin case. Unless these cases have
   been mis-entered (only 15 of 225 were Entered in Error), the aforementioned Act/Reg
   connection is incorrect. The SQL used to identify this issue is provided below.

     SELECT COUNT(case_skey) FROM (
     SELECT e.case_skey, COUNT(v.VIOLATION_NUMBER) cnt
     FROM ERA_CASE e, VIOLATION v
     WHERE e.case_skey = v.case_skey (+)
       AND e.CASE_TYPE_CD = 'A'
       AND e.case_Status_st   <> 'ERR'
     GROUP BY e.case_skey
     ) WHERE cnt = 0

   Resolution:
      o Prior to converstion update the Admin Cases in ERA 2.1 so that there are no
          violations on the cases. (MoF)
      o Provide the information required to populate the COMPLIANCE_ARTICLE table
          with an entry for the “Unknown” article. (MoF)
      o In the ERA Implementation, add the “Unknown” article to the
          COMPLIANCE_ARTICLE table. (Vivid)
      o In the conversion, link an Admin Review or Appeal of an Admin Review to the
          “Unknown” article. (Vivid)
      o In the conversion, flag as an error, any violations that are linked to Admin Cases.
          (Vivid)
      o The Business Rule for building the list of Act/Decisions (COMPLIANCE_ARTICLE)
          that can be selected for an Admin Review has been changed to the following:
          SELECT FROM COMPLIANCE_ARTICLE
          WHERE ADMIN_REVIEW_IND = ‘Y’
               AND ADMIN_REVIEW.REVIEW_REQUEST_DATE
                    BETWEEN EFFECTIVE_DATE AND EXPIRY_DATE (Vivid)

2. (Severe) Several incident cases have been identified as having at least one appeal that
   does not have a determination. While the data model will support this, the exact
   definition of what this represents requires clarification. The SQL used to identify this
   issue is provided below.



                                                                                            Page 6
  Ministry of Forests                                          Detailed Data Conversion Plan
  Compliance and Enforcement Branch


     SELECT COUNT(*),case_type_cd
     FROM appeal, era_case
     WHERE appeal.case_skey = era_case.case_skey
       AND (APPEAL_STATUS_ST <> 'ERR'
       AND CASE_STATUS_ST <> 'ERR')
       AND determination_num = 0
     GROUP BY case_type_cd

           Resolution:
              o Prior to converstion, update the Admin Cases in ERA 2.1 so that there are no
                  violations on the cases. (MoF).

3. (Severe) There are ERA2.1 Determinations that do not have corresponding Violations.
   The purpose of these entries is unknown and will need, at least, to be defined to ensure
   data integrity in ERA 3.0.

           Resolution:
              o Prior to converstion, update the        Determinations     so   that    they    have
                  corresponding Violations. (MoF).

4.    (Severe)    It is not clear how an APPEAL (using ERA_CASE.CASE_TYPE_CD,
     APPEAL.HEARD_BY_AUTH_CD, and APPEAL.APPEAL_TYPE_CD) should be mapped to one
     of ADMIN_REVIEW, MOF_REVIEW, or OTHER_APPEAL. Guidance in this area is required.
     See Page 31.

           Resolution:
              o Prior to converstion, clean up any invalid data in the Appeals area. (MoF).
              o The mapping to Appeal type is defined by the table below. (Vivid)

 CASE_TYPE_CD           APPEAL_TYPE_CD       HEARD_BY_AUTH_CD             ERA 3 APPEAL TYPE

Admin                 Review               n/a                      ADMIN_REVIEW
Admin                 Appeal               n/a                      OTHER_APPEAL
Incident              Review               n/a                      MOF_REVIEW
Incident              Appeal               n/a                      OTHER_APPEAL


5. (Severe) How can a CASE_CLIENT_ID be generated for an appeal that does not link
   back to a Determination (ADMIN APPEAL)? See page 31.

           Resolution:
              o Look up CASE_CLIENT.CASE_CLIENT_ID using APPEAL.CLIENT_NUMBER and
                  APPEAL.CLIENT_LOCN_CODE. (Vivid).

6. (Moderate) The information contained in Appendix E does not uniquely identify a
   COMPLIANCE_ARTICLE.   MoF needs to provide guidance on how the information
   contained  in    Appendix     E     is    to    be     used     to     populate
   COMPLIANCE_ARTICLE.MAXIMUM_PENALTY. See page 12.

           Resolution:
              o The ARTICLE table will be updated with MAXIMUM_PENALTY amounts. This
                  data will be replicated to COMPLIANCE_ARTICLE. (MoF).
              o As part of the conversion, all values of $1 in the MAXIMUM_PENALTY column
                  will be converted to the NULL value. (Vivid)


                                                                                               Page 7
 Ministry of Forests                                     Detailed Data Conversion Plan
 Compliance and Enforcement Branch



7. (Moderate)      How should INVESTIGATION.CONTACT_NAME be broken into
   CONTACT_FIRST_NAME and CONTACT_LAST_NAME? See Page Error! Bookmark not
   defined.Error! Bookmark not defined..

      Resolution:
         o CONTACT_FIRST_NAME receives all text before the first space in
             INVESTIGATION.CONTACT_NAME. (Vivid)
         o CONTACT_LAST_NAME receives all text after the first space in
             INVESTIGATION.CONTACT_NAME. (Vivid)
         o Manual clean up of “messy” ones to be performed by Administrators in ERA
             3.0 after conversion is complete. (MoF)

8. (Moderate) How is the character data in INVESTIGATION.CONTACT_PHONE to be
   converted to a numeric CONTACT_PHONE_NO?     See Page Error! Bookmark not
   defined.Error! Bookmark not defined..

      Resolution:
         o Parse out the "-" and convert to a number. (Vivid).

9. (Moderate)      How does INVESTIGATION.CC_CONTACT_NAME get broken into
   COUNSEL_FIRST_NAME and COUNSEL_LAST_NAME? See Page Error! Bookmark not
   defined.Error! Bookmark not defined..

      Resolution:
         o COUNSEL_FIRST_NAME receives all text before the first space in
             INVESTIGATION.CC_CONTACT_NAME. (Vivid)
         o COUNSEL_LAST_NAME receives all text after the first space in
             INVESTIGATION.CC_CONTACT_NAME. (Vivid)
         o Manual clean up of “messy” ones to be performed by Administrators in ERA
             3.0 after conversion is complete. (MoF)

10. (Moderate) How does INVESTIGATION.CC_PHONE_NUMBER (character) get converted
    into COUNSEL_PHONE_NO (number)? See Page 28.

      Resolution:
         o Parse out the "-" and convert to a number. (Vivid)

11. (Moderate)    How does ERA_CASE.XFER_CONTACT_NAME get                      broken      into
    TRANSFER_FIRST_NAME AND TRANSFER_LAST_NAME? See Page 28.

      Resolution:
         o TRANSFER_FIRST_NAME receives all text before the first space in
             ERA_CASE.XFER_CONTACT_NAME. (Vivid)
         o TRANSFER_LAST_NAME receives all text after the first space in
             ERA_CASE.XFER_CONTACT_NAME. (Vivid)
         o Manual clean up of “messy” ones to be performed by Administrators in ERA
             3.0 after conversion is complete. (MoF)

12. (Moderate)      Business  Requirements  identify   non-existent  column
    "UNAUTH_HARVEST/VOL_DTRMNTN_CODE" as source.     What is the source for
    UAH_MEASUREMENT_TYPE_CODE? See Page 29.



                                                                                         Page 8
 Ministry of Forests                                      Detailed Data Conversion Plan
 Compliance and Enforcement Branch


      Resolution:
         o Use the code value for “Other” (Vivid).

13. (Moderate) Code mappings are required for APPEAL_AUTHORITY_CODE. See Page 32.

Outstanding.

14. (Moderate) The HEARING_START_TIME is a character field that does not seem to have
    a consistent TIME format. How should this field be converted into a time format? See
    Page Error! Bookmark not defined.Error! Bookmark not defined..

      Resolution:
         o The entries in APPEAL.HEARING_START_TIME will be converted to HH:MM 24
             hr format. (MoF)

15. (Moderate) PARTICIPANT_NAME must be broken into FIRST_NAME and LAST_NAME.
    What is the rule for doing this? See Page Error! Bookmark not defined.Error!
    Bookmark not defined..

      Resolution:
         o FIRST_NAME receives all text before the first space in PARTICIPANT_NAME.
             (Vivid)
         o LAST_NAME receives all text after the first space in PARTICIPANT_NAME.
             (Vivid)
         o Manual clean up of “messy” ones to be performed by Administrators in ERA
             3.0 after conversion is complete. (MoF)

16. (Moderate) PHONE_NUMBER is a character field that must be converted to PHONE_NO,
    which is a numeric field. What is the rule for converting the phone number? See Page
    Error! Bookmark not defined.Error! Bookmark not defined..

      Resolution:
         o Parse out the "-" and convert to a number. (Vivid)

17. (Moderate) MOF_REVIEW.PANEL_IDENTIFICATION_DATE is a new mandatory field.
    What value should be used to populate this field? See Page 35.

          o    Outstanding Issue

18. (Moderate) MOF_REVIEW.PANEL_LATEST_NOTICE_DATE is a new mandatory field.
    What value should be used to populate this field? See Page 35.

      Resolution:
         o Outstanding Issue

19. (Moderate) OTHER_REVIEW.LT_LITIGATION_STATUS_CODE is a new mandatory field.
    What value should be used to populate this field? See Page 36.

      Resolution:
         o Outstanding Issue
         o MoF to determine if this data item is required in the system.




                                                                                          Page 9
 Ministry of Forests                                     Detailed Data Conversion Plan
 Compliance and Enforcement Branch


20. (Moderate) OTHER_APPEAL.LTRACK_FILE_ID is a new mandatory field. What value
    should be used to populate this field? See Page Error! Bookmark not defined.Error!
    Bookmark not defined..

      Resolution:
         o Use a NULL value. (Vivid)

21. (Moderate) OTHER_APPEAL.PROCEEDING_NAME is a new mandatory field. What value
    should be used to populate this field? See Page 36.

      Resolution:
         o Outstanding Issue
         o MoF to determine if this data item is required in the system

22. (Moderate) It is unknown what a TICKET_TYPE_CD of “ “ (single space) means.
    Guidance is required. See Page Error! Bookmark not defined.Error! Bookmark not
    defined..

      Resolution:
         o Prior to conversion a data clean up of the TICKET_TYPE_CD of “ “ will be
             done. (MoF)
         o A TICKET_TYPE_CD OF “ “ will be flagged as an error. (Vivid)

23. (Moderate) Code mappings are required for ENFORCEMENT_ACTION.VALID_IND and
    ENFORCEMENT_ACTION.ENFORCEMENT_OUTCOME_CODE. See Page 38.

      Resolution:
         o The mappings for ENFORCEMENT_OUTCOME _CODE are still outstanding.
         o The mappings for ENFORCEMENT_ACTION.VALID_IND are found in the table
             below. (Vivid)
                      ENFRCMNT_ACTION.         ENFORCEMENT_ACTION.
                      ENF_ACTION_STS_ST             VALID.IND

                   INV                     N
                   UST                     Y
                   VAL                     Y
                   VAR                     Y



24. (Moderate) Code mappings are required for VIOLATION_TICKET_STATUS_CODE. See
    Page 39.

      Resolution:
         o The mappings have been provided and are in section 4.7.3. (Vivid)

25. (Moderate)       What    value   should    be    used to                populate   the
    ENFORCEMENT_STOP_WORK_ORDER.STOP_WORK_ORDER_NO field?                  See Page Error!
    Bookmark not defined.Error! Bookmark not defined..

      Resolution:
         o Zero. (Vivid)




                                                                                         Page 10
 Ministry of Forests                                     Detailed Data Conversion Plan
 Compliance and Enforcement Branch


26. (Moderate) ENFORCEMENT_ORDER_TO_VACATE.ORDER_TO_VACATE_SITE_CODE is a
    new mandatory field. What value should be used to populate this field? See Page
    Error! Bookmark not defined.Error! Bookmark not defined..

      Resolution:
         o The value for “Other” will be used. (Vivid)

27. (Moderate) What value is to be used to populate ENFORCEMENT_AAC_REDUCTION.
    AAC_REDUCTION_TYPE_CODE See Page Error! Bookmark not defined.Error!
    Bookmark not defined..

      Resolution:
         o The value for these will be provided on an instance by instance basis. (MoF)

28. (Moderate) Code mappings are required for PENALTY_TYPE_CODE. See Page 45 and
    Page16.

      Resolution:
         o Outstanding.

29. (Moderate)    Outstanding Issue – What value         should   be    used    to   populate
    SEIZURE.SEIZURE_REASON_CODE? See Page 48.

      Resolution:
         o Outstanding.

30. (Severe) Outstanding Issue – MoF had some concerns about the values to be used for
    ENFORCEMENT_CASE.OPENED_BY_USER_ID,            ENFORCEMENT_CASE.ERA_USER_ID
    (owner/lead investigator), INVESTIGATOR.ERA_USER_ID. MoF to provide direction on
    how these values are to be set.

      Resolution:
         o Outstanding.

31. (Moderate) Outstanding Issue – When converting ERA_USERs, MOF would like the
   USER_ID column to contain a valid id in the IDIR domain, if possible. MoF must supply
   the method for converting the VM id into a valid IDIR id.

      Resolution:
         o Outstanding.




                                                                                         Page 11
 Ministry of Forests                                      Detailed Data Conversion Plan
 Compliance and Enforcement Branch




3. CONVERSION OVERVIEW


3.1   ERA 3.0 TABLES
The following tables in ERA 3.0 will be populated as part of the   conversion process. The
majority of the data will come from the ERA 2.1 data.
        ADMIN_REVIEW                                             APPEAL_HEARING
        APPEAL_PARTICIPANT                                       APPEAL_PERIOD
        CASE_APPEAL                                              CASE_CLIENT
        CASE_COMMENT                                             CASE_CONTRAVENTION
        CASE_DECISION                                            CASE_DETERMINATION
        CASE_INVESTIGATION                                       CASE_LETTER
        CASE_SITE                                                COMPLIANCE_ACTION
        CONTRAVENTION_DECISION                                   CONTRAVENTION_DISPOSITION
        CONTRAVENTION_ENFORCEMENT                                DELETED_CASE
        ENFORCEMENT_AAC_REDUCTION                                ENFORCEMENT_ACTION
        ENFORCEMENT_CANCELLATION                                 ENFORCEMENT_CASE
        ENFORCEMENT_DEREGISTRATION                               ENFORCEMENT_FORFEITURE
        ENFORCEMENT_ORDER_TO_VACATE                              ENFORCEMENT_PENALTY
        ENFORCEMENT_REMEDIATION                                  ENFORCEMENT_STOP_WORK_ORDER
        ENFORCEMENT_SUSPENSION                                   ENFORCEMENT_TICKET
        ERA_USER                                                 ERA_USER_ORG_UNIT
        INVESTIGATION_COMMENT                                    INVESTIGATOR
        INVOICE_PENALTY                                          MOF_REVIEW
        OTHER_APPEAL                                             REVENUE_ACCOUNT
        SEIZURE                                                  UNAUTHORIZED_HARVEST
        VIOLATION_TICKET

The following tables in ERA 3.0 do not need to be converted and as such, do not form part
of the conversion process:

         ERA_USER_ROLE
         ICBC_TICKET_TRANSACTION
         JOINT_VENTURE_CLIENT

The ERA 3.0 COMPLIANCE_ARTICLE table is somewhat unique. This table already exists in
DBP01 and is owned by the CIMS application. This table requires some additional columns.
Once the modifications are complete, the table ownership will transfer to ERA. $1.00 is a
special value in the MAXIMUM_PENALTY column. It means there is no limit. The conversion
will convert any $1.00 values in the MAXIMUM_PENALTY column to NULL.



3.2   EXTERNAL TABLES
      The following external tables must be populated in DBP01 and accessible before the
      conversion begins, as they will be used to look up values:

         CLIENT_LOCATION                  ORG_UNIT
         FOREST_CLIENT                    PROV_FOREST_USE



                                                                                          Page 12
 Ministry of Forests                                         Detailed Data Conversion Plan
 Compliance and Enforcement Branch


           HARVEST_FILE_TYPE_CODE            ROAD_FILE_TYPE_CODE


3.3     APPROACH
        The steps required to perform the ERA 2.1 to ERA 3.0 conversion can be broken
        down into three phases: pre-conversion, conversion, and post-conversion.


3.3.1       P RE - CONVERSION
               Turn off/disable the VM based ERA 2.1 so that the application can not make
                any more modifications (inserts, updates, or deletes) to the ERA 2.1 data.
                (Mof has decided to accomplish this by deleting the security groups that allow
                updates in SQL_PROG_AUTH. This will allow ERA 2.1 to be used in inquiry-
                only mode.)
               Take a backup of the ERA 2.1 data in SQLP1.
               Replicate the ERA 2.1 data in SQLP1 to the Oracle instance DBR01.
               Turn off the replication process that replicates the ERA 2.1 data from SQLP1
                to DBR01.
               Replicate the data in SQLP1 to the Oracle instance DBP01 for the following
                tables: FOREST_CLIENT, CLIENT_LOCATION, PROV_FOREST_USE, and
                ORG_UNIT.
               During the conversion process, there should be no access to the ERA data
                other than by tasks directly related to the conversion process. If possible,
                suspend all access to the ERA data in DBR01 other than that needed for the
                conversion process.
               Create the ERA 3.0 code tables in DBP01.
               Load the ERA 3.0 tables with data.
               Create the ERA 3.0 database in DBP01.
               Apply the modifications to the COMPLIANCE_ARTICLE table.
               Update     the   COMPLIANCE_ARTICLE         table    with  values    for   the
                MAXIMUM_PENALTY.
               Install the conversion objects in DBP01. This will invlolve granting read
                access to the ERA 2.1 data in DBR01.



3.3.2       C ONVERSION
            The items below will be explained in greater detail (including column mappings
            from the ERA 2.1 data) in the section Conversion Details.
                Convert Look Up Tables
                Convert General Case Data
                Convert Contravention Data
                Convert Determination Data
                Convert Appeal Data
                Convert Contravention Decision Link Data (Determinations Only)
                Convert Enforcement Action Data
                Convert Compliance Action Data
                Clone Data for Appeals
                Convert Seizure Data
                Set ERA Sequences




                                                                                             Page 13
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch


3.3.3      P OST -C ONVERSION
                  Validate Converted Data
                  Take a back up the ERA 3.0 Database
                  Remove the conversion objects in DBP01. This will invlolve revoking read
                   access to the ERA 2.1 data in DBR01.
                  If necessary, rollback the conversion.


3.4     ASSUMPTIONS
      The following assumptions have been made during development of the conversion
      process. Unless identified otherwise, these assumptions will be assumed to be valid
      during the actual implementation and execution.

       All preconversion tasks have been performed in accordance with this document.

       The ERA 2.1 data model supplied to Vivid is correct.

       All ERA 2.1 data that is to be converted is currently valid and will continue to be valid
        at the time of conversion. If any data is invalid, it is expected that MoF staff will
        correct it prior to conversion.

       MoF statff have made Vivid aware of all data issues and anomalies in the ERA 2.1
        data.

       During the conversion process, the ERA 3.0 (DBP01) and ERA 2.1 (DBR01) data will
        be available for querying to individuals (both Vivid and MoF staff) for the purpose of
        data validation.

       MoF staff will be available for Vivid conversion developers to answer questions
        relevant to conversion and ERA data, as well as for data exports and back ups if
        required.

       All outstanding issues identified in this document will be resolved by MoF staff prior
        to the start of the conversion development.

       Access to the ERA 2.1 data in DBR01 is in the form of Oracle views. These views will
        not be modified in any way from now and until after the conversion is complete.

       The conversion will be implemented using a combination of PL/SQL and basic SQL
        statements.

       Any record with a table-specific state code of ERR represents a record entered in
        error by the user. Any record with this status will not be copied or converted, and
        will not be used to determine state or path of execution during the conversion.
        Note: ERA_CASE records with a status of ERR are an exception to this rule. These
        records will be converted to the DELETED_CASE table.

       Any new or pending requests that have not been resolved by the initiation of the
        conversion development may impact the delivery date of the conversion.




                                                                                               Page 14
Ministry of Forests                                   Detailed Data Conversion Plan
Compliance and Enforcement Branch


    If any unexpected errors occur during the conversion that cannot be immediately
     identified and resolved, the entire conversion may be rolled back, reviewed and
     rescheduled.




                                                                                      Page 15
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch




4. CONVERSION DETAILS


4.1     CONVERT LOOK UP TABL ES
        The following ERA 3.0 look up tables will be converted. The table mappings follow:
       APPEAL_PERIOD
       REVENUE_ACCOUNT
       ERA_USER
       ERA_USER_ORG_UNIT



4.1.1      APPEAL_PERIOD

                APPEAL_PERIOD                                 VALUE TO USE

         APPEAL_PERIOD_ID              Sequence APPEAL_PERIOD_SEQ
         RIGHT_TO_APPEAL_PERIOD        ERA_APL_PERIOD.RGHT_TO_APL_PER
         RIGHT_TO_REVIEW_PERIOD        ERA_APL_PERIOD.RGHT_TO_REV_PER
         APPEAL_PANEL_IDENT_PERIOD     ERA_APL_PERIOD.APL_PANEL_ID_PER
         REVIEW_PANEL_IDENT_PERIOD     ERA_APL_PERIOD.REV_PANEL_ID_PER
         APPEAL_PANEL_ID_NOTICE_PERIOD ERA_APL_PERIOD.APL_PNL_NTC_PER
         REVIEW_PANEL_ID_NOTICE_PERIOD ERA_APL_PERIOD.REV_PNL_NTC_PER
         APPEAL_PANEL_HEARING_PERIOD   ERA_APL_PERIOD.APL_PNL_HRNG_PR
         REVIEW_PANEL_HEARING_PERIOD   ERA_APL_PERIOD.REV_PNL_HRNG_PR
         APPEAL_OUTCOME_NOTICE_PERIOD ERA_APL_PERIOD.APL_OTCM_NTC_PR
         REVIEW_OUTCOME_NOTICE_PERIOD ERA_APL_PERIOD.REV_OTCM_NTC_PR
         EFFECTIVE_DATE                ERA_APL_PERIOD.PER_EFFECTIVE_DATE
         EXPIRY_DATE                   ERA_APL_PERIOD.PER_EXPIRY_DATE
         UPDATE_TIMESTAMP              ERA_APL_PERIOD.UPDATE_TIMESTAMP
         UPDATE_USERID                 ERA_APL_PERIOD.UPDATE_USERID


4.1.2      REVENUE_ACCOUNT

               REVENUE ACCOUNT                                VALUE TO USE

         REVENUE_ACCOUNT_ID            Sequence REVENUE_ACCOUNT_SEQ
         PENALTY_TYPE_CODE             ERA_REV_ACCTS.PENALTY_TYPE_CD – see mapping below
         ENFORCEMENT_TYPE_CODE         ERA_REV_ACCTS.ENFRCMNT_TYPE_CD
         ACK_MASK_ACODE                ERA_REV_ACCTS.REVENUE_CLASS_CD
         ENTRY_TIMESTAMP               SYSDATE
         ENTRY_USERID                  “ERA 3.0 CONVERSION”
         UPDATE_TIMESTAMP              SYSDATE
         UPDATE_USERID                 “ERA 3.0 CONVERSION”


           PENALTY_TYPE_CODE




                                                                                               Page 16
 Ministry of Forests                                                 Detailed Data Conversion Plan
 Compliance and Enforcement Branch



                      ERA 3.0 PENALTY_TYPE_CODE                             ERA 2.1 PENALTY_TYPE_CD

        117                   Section 117 of FPC                      117                Sec 117 of FPC
        119(3)                Section 119(3) of FPC                   MAPPING
                                                                      REQUIRED
        PRE                   Pre-FPC Penalty under Forest Act        MAPPING
                                                                      REQUIRED
        117(pnlty)            Section 117 of FPC (penalty)            MAPPING
                                                                      REQUIRED
        118(4)                Section 118 (4) of FPC (remediation)    MAPPING
                                                                      REQUIRED
        118(5)                Section 118 (5) of FPC (remediation)    MAPPING
                                                                      REQUIRED
        119(UAH)              Section 119 of FPC (penalty UAH)        MAPPING
                                                                      REQUIRED
        MAPPING REQUIRED                                              118                Sec 118 of FPC
        MAPPING REQUIRED                                              119                Sec 119 of FPC
        MAPPING REQUIRED                                              162                Sec 162 of FPC-
                                                                                         Demand Note
        MAPPING REQUIRED                                              BTH                Both Sec 117 and
                                                                                         119
        MAPPING REQUIRED                                              FAP                Forest Act Penalty
        MAPPING REQUIRED                                              PRE                Pre-FPC      Penalty
                                                                                         under FA




4.1.3     ERA_USER
          Referential integrity in the ERA 3.0 system requires that there be ERA_USER
          records existing prior to the population of a number of ERA 3.0 tables. The query
          below will be used to generate a list of ERA users that currently have links to the
          ERA 2.1 data (other than ENTRY_USERID and UPDATE_USERID fields). This list
          will be used to populate the ERA_USER table.

SELECT ACTIVE_USER.USER_ID     AS USER_ID,
       CASE
            WHEN FL_USER.FL_USER_NO IS NULL THEN ACTIVE_USER.USER_ID
            ELSE SURNAME
      END                      AS LAST_NAME,
      CASE
            WHEN FL_USER.FL_USER_NO IS NULL THEN ‘EXPIRED USER’
            ELSE FIRST_NAME
      END                      AS FIRST_NAME,
      SUBSTR(MIDDLE_NAME,1,1) AS MIDDLE_INITIAL,
      BUSINESS_PHONE           AS PHONE_NUMBER,
      ACTIVE_USER.USER_ID      AS EMAIL_ADDRESS,
      ACTIVE_USER.USER_ID      AS ENROLLED_BY,
      TO_DATE('1995-01-01','YYYY-MM-DD') AS ENROLLED_DATE,
      CASE
            WHEN FL_USER.FL_USER_NO IS NULL THEN
                 TO_DATE('1995-01-01','YYYY-MM-DD')
            ELSE TO_DATE('9999-12-31','YYYY-MM-DD')
      END                      AS ENROLLED_EXPIRY_DATE,
      TO_DATE('1995-01-01','YYYY-MM-DD') AS EFFECTIVE_DATE,


                                                                                                     Page 17
 Ministry of Forests                               Detailed Data Conversion Plan
 Compliance and Enforcement Branch


      CASE
             WHEN FL_USER.FL_USER_NO IS NULL THEN
                  TO_DATE('1995-01-01','YYYY-MM-DD')
             ELSE TO_DATE('9999-12-31','YYYY-MM-DD')
       END                                AS EXPIRY_DATE
FROM (       SELECT OPENED_BY_USERID       AS USER_ID FROM ERA_CASE
       UNION SELECT ASSIGNED_TO_USERID     AS USER_ID FROM ERA_CASE
       UNION SELECT MADE_BY_USERID         AS USER_ID FROM DETERMINATION
       UNION SELECT REV_OFFICIAL_ID        AS USER_ID FROM APPEAL
       UNION SELECT REMARK_USERID          AS            USER_ID           FROM
INVESTIGATION_REMARK
       UNION SELECT REMARK_USERID          AS USER_ID FROM CASE_REMARK
       UNION SELECT ENFRCMNT_OFCR_UID      AS USER_ID FROM ENFRCMNT_ACTION
       UNION SELECT LIFTED_BY_USERID       AS USER_ID FROM ENFRCMNT_ACTION
       ) ACTIVE_USER,
       FL_USER,
       FL_USERID
 WHERE ACTIVE_USER.USER_ID = FL_USERID.USERID(+)
   AND FL_USERID.FL_USER_NO = FL_USER.FL_USER_NO(+)




                                                                                   Page 18
 Ministry of Forests                                              Detailed Data Conversion Plan
 Compliance and Enforcement Branch




              ERA_USER FIELD                                   VALUE TO USE

         ERA_USER_ID                  Sequence ERA_USER_SEQ
         USER_ID                      USER_ID from query – IDIR ISSUE – CAN WE CREATE AN IDIR
                                      ACCOUNT (MOF TO PROVIDE DETAILS IF POSSIBLE).
         LAST_NAME                    LAST_NAME from query
         FIRST_NAME                   FIRST_NAME from query
         MIDDLE_INITIAL               MIDDLE_INITIAL from query
         PHONE_NUMBER                 PHONE_NUMBER from query
         EMAIL_ADDRESS                EMAIL_ADDRESS from query
         ENROLLED_BY                  ENROLLED_BY from query
         ENROLLED_DATE                ENROLLED_DATE from query
         ENROLLED_EXPIRY_DATE         ENROLLED_EXPIRY_DATE from query
         EFFECTIVE_DATE               EFFECTIVE_DATE from query
         EXPIRY_DATE                  EXPIRY_DATE from query
         UPDATE_TIMESTAMP             SYSDATE
         UPDATE_USERID                “ERA 3.0 CONVERSION”
         ENTRY_TIMESTAMP              SYSDATE
         ENTRY_USERID                 “ERA 3.0 CONVERSION”




4.1.4      ERA_USER_ORG_UNIT
        After populating ERA_USER, join together ERA_USER, FL_USER, and FL_USERID to
        retrieve an ORG_UNIT_NO for all users that now exist in both ERA 2.1 and ERA 3.0.

           ERA_USER_ORG_UNIT                                   VALUE TO USE

         EFFECTIVE_DATE              1995-01-01
         ENTRY_TIMESTAMP             SYSDATE
         ENTRY_USERID                "ERA 3.0 CONVERSION"
         ERA_USER_ORG_UNIT_ID        Sequence ERA_USER_ORG_UNIT_SEQ
         EXPIRY_DATE                 9999-12-31
         ORG_UNIT_NO                 FL_USER.ORG_UNIT_NO
         ERA_USER_ID                 ERA_USER.ERA_USER_ID
         UPDATE_TIMESTAMP            SYSDATE
         UPDATE_USERID               "ERA 3.0 CONVERSION"




                                                                                                  Page 19
 Ministry of Forests                                               Detailed Data Conversion Plan
 Compliance and Enforcement Branch




4.2     CONVERT GENERAL CASE DATA
        The following ERA 3.0 look up tables will be converted. The table mappings follow:
                 DELETED_CASE
                 ENFORCEMENT_CASE
                 CASE_INVESTIGATION
                 CASE_COMMENT
                 CASE_CLIENT
                 CASE_SITE
                 INVESTIGATOR
                 INVESTIGATION_COMMENT

        Note: Except for DELETED_CASE, data will only be converted where the related
        ERA_CASE record has a CASE_STATUS_ST <> “ERR”.


4.2.1      DELETED_CASE
        Populate DELETED_CASE with all ERA_CASE records where the CASE_STATUS_ST =
        “ERR”.
              DELETED_CASE                                     VALUE TO USE

         CASE_ID                     ERA_CASE.CASE_SKEY
         CASE_NO                     ORG_UNIT.ORG_UNIT_CODE            (via          lookup            by
                                     ERA_CASE.GEO_ORG_UNIT_NO) || “-“ || ERA_CASE.CASE_FISCAL_YEAR
                                     || “-“ || ERA_CASE.CASE_ID_NUMBER (left padded with zeroes so that 4
                                     digits are displayed)
         ENTRY_TIMESTAMP             SYSDATE
         ENTRY_USER_ID               “ERA 3.0 CONVERSION”




                                                                                                   Page 20
 Ministry of Forests                                                      Detailed Data Conversion Plan
 Compliance and Enforcement Branch




4.2.2        ENFORCEMENT_CASE
        Populate ENFORCEMENT_CASE                      with    all     ERA_CASE        records             where    the
        CASE_STATUS_ST <> “ERR”.
               ENFORCEMENT_CASE                                           VALUE TO USE

        CASE_CLOSED_IND                          IF ERA_CASE.CASE_STATUS_ST           IN        (‘OPN’,’PND’)   THEN   ‘N’
                                                 ELSE ‘Y’
        ENFORCEMENT_CASE_ID                      ERA_CASE.CASE_SKEY
        CASE_NO                                  ORG_UNIT.ORG_UNIT_CODE         (via       lookup        by
                                                 ERA_CASE.GEO_ORG_UNIT_NO)           ||       “-“         ||
                                                 ERA_CASE.CASE_FISCAL_YEAR          ||        “-“         ||
                                                 ERA_CASE.CASE_ID_NUMBER (left padded with zeroes so that 4
                                                 digits are displayed)
        CASE_SOURCE_CODE                         ERA_CASE.CASE_SOURCE_CD – see mapping below
        CASE_TYPE_CODE                           ERA_CASE.CASE_TYPE_CD
        DESCRIPTION                              ERA_CASE.CASE_DESCRIPTION                 ||          “        “         ||
                                                 ERA_CASE.INCIDENT_DESC
        DISCOVERY_DATE                           ERA_CASE.DISCOVERY_DATE
        ENTRY_TIMESTAMP                          ERA_CASE.ENTRY_TIMESTAMP
        ENTRY_USERID                             ERA_CASE.ENTRY_USERID
        FPB_FILE_NO                              NULL
        INSPECTION_ID                            ERA_CASE.INSPECTION_ID
        ORG_UNIT_NO                              ERA_CASE.ADMIN_ORG_UNIT_NO
        GEOGRAPHIC_ORG_UNIT_NO                   ERA_CASE.GEO_ORG_UNIT_NO
        PROSECUTION_TEST_DATE                    NULL
        PROSECUTION_TEST_IND                     “N”
        SOURCE_REFERENCE_NO                      ERA_CASE.SRCE_REFERENCE_NO
        UPDATE_TIMESTAMP                         ERA_CASE.UPDATE_TIMESTAMP
        UPDATE_USERID                            ERA_CASE.UPDATE_USERID
        ERA_USER_ID                              ERA_CASE.ASSIGNED_TO
        OPENED_BY_USER_ID                        ERA_CASE.OPENED_BY_USERID


             CASE_SOURCE_CODE

                  ERA 3.0 CASE_SOURCE_CODE                                ERA 2.1 CASE_SOURCE_CD

        BCFS           BC Forest Service                        BCFS         BC Forest Service
        DFO            Dept of Fisheries & Oceans               DFO          Dept. of Fisheries and Oceans
        FPB            Forest Practices Board                   FPB          Forest Practices Board
        LIC            Licensee or self-report                  LIC          Licensee or Representative
        EM             Energy & Mines                           EMPR         Energy, Mines, Petroleum Resources
        WLAP           Water, Land, and Air Protection          MELP         Environment, Lands, Parks
        OTH            Other Agency, including police forces    OTH          Other Government Agency
        PUB            Public/Individual                        PUB          Public
        RCMP           RCMP                                     RCMP         RCMP




                                                                                                                Page 21
 Ministry of Forests                                                        Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.2.3        CASE_INVESTIGATION
             Populate CASE_INVESTIGATION with all INVESTIGATION records as long as the
             associated ERA_CASE has a CASE_STATUS_ST <> “ERR”.
               CASE_INVESTIGATION                                           VALUE TO USE

        AGENCY_CODE                            INVESTIGATION.OTHER_AGENCY_CODE              –   see   mapping     for
                                               AGENCY_CODE below
        PRIMARY_AGENCY_CODE                    INVESTIGATION.LEAD_AGENCY_CODE              –    see   mapping     for
                                               AGENCY_CODE below
        ENFORCEMENT_CASE_ID                    INVESTIGATION.CASE_SKEY
        CONTACT_FIRST_NAME                     INVESTIGATION.CONTACT_NAME – All characters prior to first
                                               space
        CONTACT_LAST_NAME                      INVESTIGATION.CONTACT_NAME – All characters after the first
                                               space
        CONTACT_PHONE_NO                       INVESTIGATION.CONTACT_PHONE – Parce out all ‘-‘ and convert
                                               to a number.
        ENTRY_TIMESTAMP                        INVESTIGATION.ENTRY_TIMESTAMP
        ENTRY_USERID                           INVESTIGATION.ENTRY_USERID
        JOINT_INVESTIGATION_IND                INVESTIGATION.JOINT_INVSTGTN_IND
        OTHER_AGENCY                           NULL
        OTHER_AGENCY_CASE_ID                   NULL
        START_DATE                             INVESTIGATION.INVESTIGATION_DATE
        UPDATE_TIMESTAMP                       INVESTIGATION.UPDATE_TIMESTAMP
        UPDATE_USERID                          INVESTIGATION.UPDATE_USERID


             AGENCY_CODE

                      ERA 3.0 AGENCY_CODE                                   ERA 2.1 XFER_TO_AUTH_CD

        BCFS           BC Forest Service                             BCFS      BC Forest Service
        DFO            Dept. of Fisheries & Oceans                   DFO       Dep Fisheries and Oceans
        EM             Energy, Mines & Petroleum Resources           EMPR      Energy, Mines, Petroleum Resources
                       Ministry of   Water,   Land    and      Air
        WLAP           Protection                                    MELP      Environment, Lands, Parks
        OTH            Other Agency, including police forces         OTH       Other Government Agency
        RCMP           RCMP                                          RCMP      RCMP




                                                                                                            Page 22
 Ministry of Forests                                         Detailed Data Conversion Plan
 Compliance and Enforcement Branch




4.2.4      CASE_COMMENT
           Populate CASE_COMMENT with all CASE_REMARK records as long as the
           associated ERA_CASE has a CASE_STATUS_ST <> “ERR”.
                CASE_COMMENT                                 VALUE TO USE

        CASE_COMMENT_ID                Sequence CASE_COMMENT_SEQ
        ENFORCEMENT_CASE_ID            CASE_REMARK.CASE_SKEY
        COMMENTS                       CASE_REMARK_TXT.LINE_TEXT – Refer to the Pseudo code below
                                       as the COMMENTS field may be composed of many
                                       CASE_REMARK_TXT.LINE_TEXT entries.
        ENTRY_TIMESTAMP                CASE_REMARK.REMARK_DATE
        ENTRY_USERID                   CASE_REMARK.REMARK_USERID
        UPDATE_TIMESTAMP               CASE_REMARK.REMARK_DATE
        UPDATE_USERID                  CASE_REMARK.REMARK_USERID
        ERA_USER_ID                    Lookup CASE_REMARK.REMARK_USERID in ERA_USER.USER_ID to
                                       find the ERA_USER_ID.


        Note that a single case can have multiple remarks made up of multiple lines. Each
        remark will result in at least one comment, which will be composed of a
        concatenation of the remark’s multiple lines. However, if the total number of
        characters that make up a single remark exceeds the number of characters in the
        comment, then the single remark will result in two or more comments in the new
        system. The following pseudo code will be used to create the COMMENTS field of the
        CASE_COMMENT records:

        For each CASE_REMARK
             COMMENT = NULL
             For each CASE_REMARK_TXT in the CASE_REMARK
                 If length(COMMENT) + length(CASE_REMARK_TXT.LINE_TEXT) < 2000
        Then
                      COMMENT = COMMENT || ‘ ‘ || CASE_REMARK_TXT.LINE_TEXT
                 Else
                      Insert a CASE_COMMENT record using the value in COMMENT
                      COMMENT = CASE_REMARK_TXT.LINE_TEXT
                 End If
             End For
             Insert a CASE_COMMENT record using the value in COMMENT
        End For



4.2.5      CASE_CLIENT
           Populate CASE_CLIENT with all CLIENT_CASE_LNK records as long as the
           associated ERA_CASE has a CASE_STATUS_ST <> “ERR”.
             CASE_INVESTIGATION                              VALUE TO USE

        CASE_CLIENT_ID                 Use Sequence CASE_CLIENT_SEQ
        ENFORCEMENT_CASE_ID            CLIENT_CASE_LNK.CASE_SKEY
        CLIENT_LOCN_CODE               CLIENT_CASE_LNK.CLIENT_LOCN_CODE
        CLIENT_NUMBER                  CLIENT_CASE_LNK.CLIENT_NUMBER



                                                                                             Page 23
 Ministry of Forests                                            Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        ENTRY_TIMESTAMP                  SYSDATE
        ENTRY_USERID                     "ERA 3.0 CONVERSION"
        JOINT_VENTURE_IND                “N”
        UPDATE_TIMESTAMP                 SYSDATE
        UPDATE_USERID                    "ERA 3.0 CONVERSION"


        The following SQL will be used to join the tables to extract the data:

        SELECT CLIENT_CASE_LINK.CASE_SKEY,
              CLIENT_CASE_LINK.CLIENT_LOCN_CODE,
              CLIENT_CASE_LINK.CLIENT_NUMBER,
              CLIENT_LOCATION.CITY,
              CLIENT_LOCATION.ADDRESS_1,
              CLIENT_LOCATION.ADDRESS_2,
              CLIENT_LOCATION.ADDRESS_3,
              FOREST_CLIENT.CLIENT_NAME,
              FOREST_CLIENT.LEGAL_FIRST_NAME,
              FOREST_CLIENT.LEGAL_MIDDLE_NAME,
              CLIENT_LOCATION.POSTAL_CODE,
              CLIENT_LOCATION.PROVINCE
        FROM CLIENT_CASE_LINK,
              CLIENT_LOCATION,
              FOREST_CLIENT
        WHERE CLIENT_CASE_LINK.CLIENT_NUMBER = CLIENT_LOCATION.CLIENT_NUMBER
        AND   CLIENT_CASE_LINK.CL_LOCN_CODE = CLIENT_LOCATION.CLIENT_LOCN_CODE
        AND   CLIENT_LOCATION_NUMBER.CLIENT_NUMBER
        =FOREST_CLIENT.CLIENT_NUMBER;



4.2.6      CASE_SITE
        Create a CASE_SITE record for all ERA_CASE records where the CASE_STATUS_ST
        <> “ERR”.
                     CASE_SITE                                  VALUE TO USE

        ENFORCEMENT_CASE_ID              ERA_CASE.CASE_SKEY
        CASE_SITE_ID                     ERA_CASE.CASE_SKEY
        CUT_BLOCK_ID                     ERA_CASE.CUT_BLOCK_ID
        CUTTING_PERMIT_ID                ERA_CASE.CUTTING_PERMIT_ID
        DESCRIPTION                      ERA_CASE.INCIDENT_LOCATION
        ENTRY_TIMESTAMP                  ERA_CASE.ENTRY_TIMESTAMP
        ENTRY_USERID                     ERA_CASE.ENTRY_USERID
        FOREST_FILE_ID                   ERA_CASE.FOREST_FILE_ID
        LATITUDE                         NULL
        LONGITUDE                        NULL
        BLK_CUTTING_PERMIT_ID            NULL
        BLK_FOREST_FILE_ID               NULL
        OPENING_SKEY                     ERA_CASE.OPENING_SKEY
        PASTURE_ID                       NULL
        PRESCRIPTION_NO                  NULL
        REC_PROJECT_SKEY                 ERA_CASE.REC_PROJECT_SKEY
        ROAD_SECTION_ID                  NULL



                                                                                                Page 24
Ministry of Forests                                           Detailed Data Conversion Plan
Compliance and Enforcement Branch


      SITE_ID                       IF ERA_CASE.FOREST_FILE_IS IS NULL AND
                                      ERA_CASE.OPENING_SKEY IS NULL AND
                                      ERA_CASE.REC_PROJECT_SKEY IS NULL THEN
                                    “No Site Specified in ERA 2.1”
                                    ELSE NULL
      CIMS_BUSINESS_AREA_CODE       See CIMS_BUSINESS_AREA_CODE mapping rule below.
      TENURE_CNT                    ERA_CASE.TENURE_CNT
      UPDATE_TIMESTAMP              ERA_CASE.UPDATE_TIMESTAMP
      UPDATE_USERID                 ERA_CASE.UPDATE_USERID


         CIMS_BUSINESS_AREA_CODE

     CASE WHEN ERA_CASE.REC_PROJECT_SKEY IS NOT NULL THEN
                CASE_SITE.CIMS_BUSINESS_AREA_CODE = ‘REC’
          WHEN ERA_CASE.OPENING_SKEY IS NOT NULL THEN
                CASE_SITE.CIMS_BUSINESS_AREA_CODE = ‘SILV’
          WHEN ERA_CASE.FOREST_FILE_ID IS NOT NULL THEN
                Look up the PROV_FOREST_USE.FILE_TYPE_CD using
                 ERA_CASE.FOREST_FILE_ID
                IF FILE_TYPE_CD IN HARVEST_FILE_TYPE_CODE THEN
                    CASE_SITE.CIMS_BUSINESS_AREA_CODE = ‘HARV’
                ELSIF FILE_TYPE_CD IN ROAD_FILE_TYPE_CODE THEN
                    CASE_SITE.CIMS_BUSINESS_AREA_CODE = ‘ROAD’
                ELSIF FILE_TYPE_CD IN (‘H01’, ‘H02’, ‘H03’, ‘E01’, ‘E02’,
     ‘E03’, ‘I01’, ‘I02’, ‘I03’) THEN
                    CASE_SITE.CIMS_BUSINESS_AREA_CODE = ‘RNGE’ END-IF
          ELSE
               CASE_SITE.CIMS_BUSINESS_AREA_CODE = ‘GEN’
     END




                                                                                              Page 25
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch




4.2.7      INVESTIGATOR
           Create a single INVESTIGATOR record for each unique REMARK_USERID that
           exists in INVESTN_REMARK.
                 INVESTIGATOR                                  VALUE TO USE

        INVESTIGATOR_ID                 Sequence
        ERA_USER_ID                     Lookup        INVESTN_REMARK.REMARK_USERID                       in
                                        ERA_USER.USER_ID to find the ERA_USER_ID.
        ENFORCEMENT_CASE_ID             INVESTN_REMARK.CASE_SKEY
        WRITE_IND                       “Y”
        EFFECTIVE_DATE                  1995-01-01
        EXPIRY_DATE                     9999-12-31
        ENTRY_TIMESTAMP                 SYSDATE
        ENTRY_USERID                    “ERA 3.0 CONVERSION”
        UPDATE_TIMESTAMP                SYSDATE
        UPDATE_USERID                   “ERA 3.0 CONVERSION”
        ERA_USER_ID                     ERA_USER.ERA_USER_ID     -     Lookup                      using
                                        INVESTIGATION_REMARK.REMARK_USERID




4.2.8      INVESTIGATION_COMMENT

           INVESTIGATION_COMMENT                               VALUE TO USE

        ACTIVITY_DATE                   INVESTN_REMARK.REMARK_DATE
        COMMENTS                        INVESTN_RMK_TXT.LINE_TEXT CASE_REMARK_TXT.LINE_TEXT –
                                        Refer to the Pseudo code below as the COMMENTS field may be
                                        composed of many CASE_REMARK_TXT.LINE_TEXT entries.
        ENTRY_TIMESTAMP                 INVESTN_REMARK.REMARK_DATE
        ENTRY_USERID                    INVESTN_REMARK.REMARK_USERID
        INVESTIGATION_COMMENT_ID        Sequence INVESTIGATION_COMMENT_SEQ
        INVESTIGATOR_ID                 INVESTIGATOR.INVESTIGATOR_ID         -     Lookup        using
                                        INVESTN_REMARK.REMARK_USERID in ERA_USER.USER_ID and
                                        then     in     INVESTIGATOR.ERA_USER_ID    to     find    the
                                        INVESTIGATOR_ID. The investigator must belong to the case that
                                        this remark is for.
        UPDATE_TIMESTAMP                INVESTN_REMARK.REMARK_DATE
        UPDATE_USERID                   INVESTN_REMARK.REMARK_USERID


        Note that a single investigation can have multiple remarks made up of multiple lines.
        Each remark will result in at least one comment, which will be composed of a
        concatenation of the remark’s multiple lines. However, if the total number of
        characters that make up a single remark exceeds the number of characters in the
        comment, then the single remark will result in two or more comments in the new
        system. The following pseudo code will be used to create the COMMENTS field of the
        INVESTIGATION_COMMENT records:




                                                                                               Page 26
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch




        For each INVESTN_REMARK
             COMMENT = NULL
             For each INVESTN_REMARK _TXT in the INVESTN_REMARK
                 If length(COMMENT) + length(INVESTN_REMARK_TXT.LINE_TEXT) <
        2000                                                                   Then
                      COMMENT = COMMENT || ‘ ‘ || INVESTN_REMARK_TXT.LINE_TEXT
                 Else
                      Insert a INVESTIGATION_COMMENT record using the value in
        COMMENT
                      COMMENT = INVESTN_REMARK_TXT.LINE_TEXT
                 End If
             End For
             Insert a INVESTIGATION_COMMENT record using the value in COMMENT
        End For


4.3     CONVERT CONTRAVENTIO N DATA
        All valid VIOLATION records (VIOLATION_STS_ST <> “ERR”) for valid Incident
        ERA_CASEs will be converted to CASE_CONTRAVENTION. Any VIOLATION records
        for Admin ERA_CASEs are in error and will be flagged as such. At the time the
        CASE_CONTRAVENTION record is created a single CONTRAVENTION_DISPOSITION
        record will be created, if required. At the time the CASE_CONTRAVENTION record is
        created a single UNAUTHORIZED_HARVEST recored will be created, if required.


4.3.1      CASE_CONTRAVENTION

              CASE_CONTRAVENTION                               VALUE TO USE

        CASE_CONTRAVENTION_ID         Sequence CASE_CONTRAVENTION_SEQ
        ENFORCEMENT_CASE_ID           VIOLATION.CASE_SKEY
        CASE_SITE_ID                  VIOLATION.CASE_SKEY
        COMPLIANCE_ARTICLE_ID         VIOLATION.ARTICLE_SKEY
        CONTRAVENTION_NO              VIOLATION.VIOLATION_NUMBER
        CONTRAVENTION_STATUS_CODE     VIOLATION.VIOLATION_STS_ST
        ENTRY_TIMESTAMP               VIOLATION.ENTRY_TIMESTAMP
        ENTRY_USERID                  VIOLATION.UPDATE_USERID
        CASE_CLIENT_ID                CASE_CLIENT.CASE_CLIENT_ID     –    Look     up    using
                                      VIOLATION.CLIENT_NUMBER and VIOLATION.CLIENT_LOCN_CODE
        INCIDENT_DATE                 VIOLATION.EST_INCDNT_STRT_DT
        INSPECTION_ID                 NULL
        INVESTIGATION_COST            NULL
        UPDATE_TIMESTAMP              VIOLATION.UPDATE_TIMESTAMP
        UPDATE_USERID                 VIOLATION.UPDATE_USERID




            ERA 3.0 CONTRAVENTION_STATUS_CODE               ERA 2.1 VIOLATION_STS_ST

        ALG            Alleged                    ALG                Alleged
        ALG            Alleged                    PRB                Probable
        DEF            Deferred                   Not   used    in


                                                                                               Page 27
 Ministry of Forests                                                      Detailed Data Conversion Plan
 Compliance and Enforcement Branch


                                                               ERA 2.1
                                                               Not used    in
        NCN              No Contravention Found                ERA 2.1
        DTR              Determined                            DTR              Determined
                                                               Not used    in
        CA               Compliance Action                     ERA 2.1
        Records with
        this    status
        code       are
        ignored.                                               ERR              Error




4.3.2        CONTRAVENTION_DISPOSITION

         CONTRAVENTION_DISPOSITION                                        VALUE TO USE

        AGENCY_CODE                               ERA_CASE.XFER_TO_AUTH_CD
        CONTRAVENTION_DISPOSITION_ID              Sequence CONTRAVENTION_DISPOSITION_SEQ
        COUNSEL_FIRST_NAME                        INVESTIGATION.CC_CONTACT_NAME           (default   NULL)    –   All
                                                  characters prior to first space
        COUNSEL_LAST_NAME                         INVESTIGATION.CC_CONTACT_NAME           (default   NULL)    –   All
                                                  characters after the first space
        COUNSEL_PHONE_NO                          CC_PHONE_NUMBER (default NULL) – parse out all ‘-‘ and then
                                                  convert to a number
        CROWN_COUNSEL_REFFERRED_IND               INVESTIGATION.REFERED_TO_CC_IND (default “N”)
        ENTRY_TIMESTAMP                           SYSDATE
        ENTRY_USERID                              “ERA 3.0 CONVERSION”
        CASE_CONTRAVENTION_ID                     CASE_CONTRAVENTION.CASE_CONTRAVENTION_ID
        PROSECUTING_AGENCY_CODE                   NULL
        PROSECUTION_DATE                          NULL
        PROSECUTION_PACKAGE_SENT_DATE NULL
        PROSECUTION_SUCCESS_IND                   “N”
        SDM_PACKAGE_SENT_DATE                     NULL
        TRANSFER_AGENCY_NAME                      NULL
        TRANSFER_DATE                             ERA_CASE.XFER_DATE
        TRANSFER_FIRST_NAME                       ERA_CASE.XFER_CONTACT_NAME - All characters prior to first
                                                  space
        TRANSFER_LAST_NAME                        ERA_CASE.XFER_CONTACT_NAME- All characters after the first
                                                  space
        TRANSFER_LOCATION                         ERA_CASE.XFER_OFFICE
        TRANSFER_PHONE_NO                         ERA_CASE.XFER_CONTACT_PHONE
        UPDATE_TIMESTAMP                          SYSDATE
        UPDATE_USERID                             “ERA 3.0 CONVERSION”
        WARRANT_OBTAINED_IND                      INVESTIGATION.SEARCH_WARRANT_IND (default “N”)



             AGENCY_CODE

                         ERA 3.0 AGENCY_CODE                             ERA 2.1 XFER_TO_AUTH_CD

        BCFS             BC Forest Service                     BCFS             BC Forest Service
        DFO              Dept. of Fisheries & Oceans           DFO              Dep Fisheries and Oceans



                                                                                                           Page 28
 Ministry of Forests                                                        Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        EM             Energy, Mines & Petroleum Resources           EMPR      Energy, Mines, Petroleum Resources
                       Ministry of   Water,   Land     and     Air
        WLAP           Protection                                    MELP      Environment, Lands, Parks
        OTH            Other Agency, including police forces         OTH       Other Government Agency
        RCMP           RCMP                                          RCMP      RCMP




4.3.3        UNAUTHORIZED_HARVEST
        Insert into UNAUTHORIZED_HARVEST if a record exists in the UNAUTH_HARVEST for
        the current VIOLATION.
             UNAUTHORIZED_HARVEST                                           VALUE TO USE

        AREA                                   UNAUTH_HARVEST.MERCH_AREA +
                                               UNAUTH_HARVEST.NON_MERCH_AREA
        VOLUME                                 UNAUTH_HARVEST.TOTAL_UH_VOLUME
        CASE_CONTRAVENTION_ID                  CASE_CONTRAVENTION.CASE_CONTRAVENTION_ID
        ENTRY_TIMESTAMP                        UNAUTH_HARVEST.ENTRY_TIMESTAMP
        ENTRY_USERID                           UNAUTH_HARVEST.ENTRY_USERID
        NO_OF_TREES                            NULL
        OTHER_MEASUREMENT_TYPE                 NULL
        UAH_MEASUREMENT_TYPE_CODE              “OTH”
        UPDATE_TIMESTAMP                       UNAUTH_HARVEST.UPDATE_TIMESTAMP
        UPDATE_USERID                          UNAUTH_HARVEST.UPDATE_USERID




4.4     CONVERT DETERMINATIO N DATA
        All valid DETERMINATION records (DTRMNTN_STATUS_ST <> “ERR”) for valid
        ERA_CASEs will be converted. Each valid DETERMINATION record will cause one
        CASE_DECISION record and one CASE_DETERMINATION record to be created.


4.4.1        CASE_DECISION

                 CASE_DECISION                                              VALUE TO USE

        CASE_DECISION_ID                       Sequence DECISION_SEQ
        CASE_OUTCOME_DECISION_ID               NULL
        ENFORCEMENT_CASE_ID                    DETERMINATION.CASE_SKEY
        ENTRY_TIMESTAMP                        DETERMINATION.ENTRY_TIMESTAMP
        ENTRY_USERID                           DETERMINATION.ENTRY_USERID
        CASE_CLIENT_ID                         CASE_CLIENT.CASE_CLIENT_ID   -                Look      up       using
                                               DETERMINATION.CLIENT_NUMBER,
                                               DETERMINATION.CLIENT_LOCN_CODE                                    and
                                               DETERMINATION.CASE_SKEY
        UPDATE_TIMESTAMP                       DETERMINATION.UPDATE_TIMESTAMP
        UPDATE_USERID                          DETERMINATION.UPDATE_USERID




                                                                                                            Page 29
 Ministry of Forests                                                    Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.4.2        CASE_DETERMINATION

               CASE_DETERMINATION                                       VALUE TO USE

        CASE_DECISION_ID                       CASE_DECISION_ID.CASE_DECISION_ID
        CASE_DETERMINATION_ID                  Sequence CASE_DETERMINATION_SEQ
        DETERMINATION_AGENCY_CODE              DETERMINATION.AGENCY_CODE – See mapping below
        DETERMINATION_DATE                     DETERMINATION.DETERMINATION_DATE
        DETERMINATION_NO                       DETERMINATION.DETERMINATION_NUM
        DETERMINATION_STATUS_CODE              DETERMINATION.DTRMNTN_STATUS_ST – See mapping below
        ENTRY_TIMESTAMP                        DETERMINATION.ENTRY_TIMESTAMP
        ENTRY_USERID                           DETERMINATION.ENTRY_USERID
        NO_OTBH_OFFERED_COMMENTS               NULL
        OTBH_ACCEPTED_IND                      “N”
        OTBH_DATE                              NULL
        OTBH_OFFERED_IND                       “N”
        SERVICE_DATE                           DETERMINATION.SERVICE_DATE
        UPDATE_TIMESTAMP                       DETERMINATION.UPDATE_TIMESTAMP
        UPDATE_USERID                          DETERMINATION.UPDATE_USERID
        ERA_USER_ID                            ERA_USER.ERA_USER_ID     –                 Look     up         using
                                               DETERMINATION.MADE_BY_USERID


             DETERMINATION_STATUS_CODE

             ERA 3.0 DETERMINATION_STATUS_CODE                      ERA 2.1 DTRMNTN_STATUS_ST

        OHS            Sent for Opportunity to be Heard      PND              Pending
                                                             Not used    in
        OHC            Opportunity to be Heard Complete      ERA 2.1
        COM            Complete                              CON              Confirmed
        COM                                                  NEW              New Decision
        COM            Complete                              RSC              Rescinded
        COM            Complete                              VAL              Valid
        COM            Complete                              VAR              Varied


             DETERMINATION_AGENCY_CODE

             ERA 3.0 DETERMINATION_AGENCY_CODE                            ERA 2.1 AGENCY_CODE

        BCFS           BC Forest Service                     BCFS             BC Forest Service
        EM             Energy, Mines & Petroleum Resources   EMPR             Energy, Mines, Petroleum Resources
        WLAP           Water, Land and Air Protection        MELP             Environment, Lands, Parks




4.5     CONVERT APPEAL DATA
        All valid APPEAL records (APPEAL_STATUS_ST <> “ERR”) for valid ERA_CASEs will
        be converted. Each valid APPEAL record will result in one CASE_DECISION record ,
        one CASE_APPEAL, CASE_CONTRAVENTION, and one CONTRAVENTION_DECISION
        record being created.    If required APPEAL_HEARING and APPEAL_PARTICIPANT



                                                                                                          Page 30
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        records will be created. For each APPEAL, one and only one record of
        ADMIN_APPEAL, MOF_REVIEW, or OTHER_APPEAL will be created.


4.5.1      CASE_DECISION

                CASE_DECISION                                 VALUE TO USE

        CASE_DECISION_ID               Sequence DECISION_SEQ
        CASE_OUTCOME_DECISION_ID       CASE_DECISION.CASE_DECISION_ID    –   Look    up   using
                                       APPEAL.DETERMINATION_NO       and APPEAL.CASE_SKEY     in
                                       CASE_DETERMINATION and CASE_DECISION. If no value can be
                                       found set this field to NULL.
        ENFORCEMENT_CASE_ID            APPEAL.CASE_SKEY
        ENTRY_TIMESTAMP                APPEAL.ENTRY_TIMESTAMP
        ENTRY_USERID                   APPEAL.ENTRY_USERID
        CASE_CLIENT_ID                 CASE_CLIENT.CASE_CLIENT_ID       -     Look      up    using
                                       APPEAL.DETERMINATION_NO      and     APPEAL.CASE_SKEY      in
                                       CASE_DETERMINATION,                           CASE_DECISION
                                        and                                            CASE_CLIENT.
                                       If   a    Client  can    not     be     found     then   use
                                       CASE_CLIENT.CASE_CLIENT_ID          -     Look    up   using
                                       APPEAL.CLIENT_NUMBER and APPEAL.CLIENT_LOCN_CODE.
        UPDATE_TIMESTAMP               APPEAL.UPDATE_TIMESTAMP
        UPDATE_USERID                  APPEAL.UPDATE_USERID




4.5.2      CASE_APPEAL

                    CASE_APPEAL                               VALUE TO USE

        APPEAL_AUTHORITY_CODE          APPEAL.HEARD_BY_AUTH_CD – See mapping below
        APPEAL_NO                      APPEAL.APPEAL_NUMBER
        APPEAL_OUTCOME_CODE            APPEAL.APPEAL_OUTCOME_CD
        APPEAL_STATUS_CODE             APPEAL.APPEAL_STATUS_ST – See mapping below
        CASE_APPEAL_ID                 Sequence CASE_APPEAL_SEQ
        COMMENTS                       APEL_REMARK_TXT.LINE_TEXT
        CASE_DECISION_ID               CASE_DECISION.CASE_DECISION
        ENTRY_TIMESTAMP                APPEAL.ENTRY_TIMESTAMP
        ENTRY_USERID                   APPEAL.ENTRY_USERID
        CASE_CLIENT_ID                 CASE_CLIENT.CASE_CLIENT_ID        -   Look   up             using
                                       APPEAL.CLIENT_NUMBER and APPEAL.CLIENT_LOCN_CODE.
        UPDATE_TIMESTAMP               APPEAL.UPDATE_TIMESTAMP
        UPDATE_USERID                  APPEAL.UPDATE_USERID


        Note that a single appeal can have multiple remarks made up of multiple lines. All
        remarks will be placed in a single COMMENTS field. The following pseudo code will
        be used to create the COMMENTS field of the CASE_APPEAL record:

        For each APPEAL_REMARK for the APPEAL
            COMMENT = NULL
            For each APEL_REMARK_TXT in the APPEAL_REMARK
               COMMENT = COMMENT || ‘ ‘ || APEL_REMARK_TXT.LINE_TEXT
            End For


                                                                                               Page 31
Ministry of Forests                                           Detailed Data Conversion Plan
Compliance and Enforcement Branch


         If length(COMMENT) < 2000 Then
             Insert the CASE_APPEAL record using the value in COMMENT
         Else
             Log an Error
         End If
     End For

            APPEAL_STATUS_CODE

               ERA 3.0 APPEAL_STATUS_CODE                     ERA 2.1 APPEAL_STATUS_ST

       UAP            Under Appeal                     PND        Pending
       COM            Complete                         COM        Complete
       REJ            Rejected                         REJ        Rejected
       WDN            Withdrawn                        WDN        Withdrawn
       Not   to  be
       converted                                       ERR        Error


            APPEAL_AUTHORITY_CODE

              ERA 3.0 APPEAL_AUTHORITY_CODE                  ERA 2.1 HEARD_BY_AUTH_CD

       DM             District Manager                 DM         Dist Manager
       RM             Regional Manager or Delegate     RM         Reg Manager
                                                       MAPPING
       ROF            Review Official                  REQUIRED
       CF             Chief Forester or Delegate       CF         Chief Forester
       CF             Chief Forester or Delegate       DC         Deputy Chief Forester
       MN             Minister                         MN         Minister
       AC             Forest Appeals Commission        AC         Forest Appeal Commission
       CA             BC Court of Appeal               CA         Court Of Appeal
       PC             BC Provincial Court              PC         Provincial Court
       SC             BC Supreme Court                 SC         BC Supreme Court
                                                       MAPPING
       FCA            Federal Court of Appeal          REQUIRED
                                                       MAPPING
       FCT            Federal Court – Trial Division   REQUIRED
       AB             Environmental Appeal Board       AB         An Appeal Board
       CC             Supreme Court of Canada          CC         Supreme Court Canada
       OT             Other Authority                  OT         Other Authority
       MAPPING
       REQUIRED                                        AP         Appeal Panel
       MAPPING                                                    MELP Panel Chair (MELP official as
       REQUIRED                                        EN         chair of panel)
       MAPPING                                                    MEMPR Chair (MEMPR official as chair
       REQUIRED                                        MI         of panel
       MAPPING
       REQUIRED                                        RP         Review Panel




                                                                                              Page 32
 Ministry of Forests                                         Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.5.3      CASE_CONTRAVENTION


             CASE_CONTRAVENTION                              VALUE TO USE

        CASE_CONTRAVENTION_ID        Sequence CASE_CONTRAVENTION_SEQ
        ENFORCEMENT_CASE_ID          APPEAL.CASE_SKEY
        CASE_SITE_ID                 NULL
        COMPLIANCE_ARTICLE_ID        Value of the “Unknown” Admin article. Exact value TBD.
        CONTRAVENTION_NO             1
        CONTRAVENTION_STATUS_CODE    “DTR” - Determined
        ENTRY_TIMESTAMP              SYSDATE
        ENTRY_USERID                 “ERA 3.0 CONVERSION”
        CASE_CLIENT_ID               CASE_CLIENT.CASE_CLIENT_ID     –     Look    up              using
                                     APPEAL.CLIENT_NUMBER and APPEAL.CLIENT_LOCN_CODE
        INCIDENT_DATE                APPEAL.APL_RQST_RECVD_DT
        INSPECTION_ID                NULL
        INVESTIGATION_COST           NULL
        UPDATE_TIMESTAMP             SYSDATE
        UPDATE_USERID                “ERA 3.0 CONVERSION”


4.5.4      CONTRAVENTION_DECISION

           CONTRAVENTION_DECISION                            VALUE TO USE

        COMMENTS                     NULL
        CONTRAVENTION_DECISION_ID    Sequence CONTRAVENTION_DECISION_SEQ
        CASE_CONTRAVENTION_ID        CASE_CONTRAVENTION.CASE_CONTRAVENTION_ID
        CONTRAVENTION_STATUS_CODE    “DTR” – Determined
        CASE_DECISION_ID             CASE_DECISION.CASE_DECISION_ID
        ENTRY_TIMESTAMP              SYSDATE
        ENTRY_USERID                 “ERA 3.0 CONVERSION”
        UPDATE_TIMESTAMP             SYSDATE
        UPDATE_USERID                “ERA 3.0 CONVERSION”




4.5.5      APPEAL_HEARING
        An   APPEAL_HEARING    record     will    be    created             if    the         current
        APPEAL.HEARING_START_DATE is prior to ‘9999-12-31’.
               APPEAL_HEARING                                VALUE TO USE

        APPEAL_HEARING_ID            Sequence APPEAL_HEARING_SEQ
        CASE_APPEAL_ID               CASE_APPEAL.CASE_APPEAL_ID
        COMPLETION_DATE              APPEAL.HEARING_END_DATE
        ENTRY_TIMESTAMP              APPEAL.ENTRY_TIMESTAMP
        ENTRY_USERID                 APPEAL.ENTRY_USERID
        LOCATION                     APPEAL.HEARING_LOCATION
        START_DATE                   APPEAL.HEARING_START_DATE || APPEAL.HEARING_START_TIME
        UPDATE_TIMESTAMP             APPEAL.UPDATE_TIMESTAMP




                                                                                              Page 33
 Ministry of Forests                                                      Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        UPDATE_USERID                             APPEAL.UPDATE_USERID


4.5.6      APPEAL_PARTICIPANT
        For  each  PARTICIPANT    record     related                 to     the       current          APPEAL,   an
        APPEAL_PARTICIPANT record will be created.
              APPEAL_PARTICIPANT                                          VALUE TO USE

        ADDRESS                                   PARTICIPANT.ADDRESS_LINE_1                         ||”          “||
                                                  PARTICIPANT.ADDRESS_LINE_2
        CASE_APPEAL_ID                            CASE_APPEAL.CASE_APPEAL_ID
        APPEAL_PARTICIPANT_ID                     PARTICIPANT.PARTICIPANT_NO
        ENTRY_TIMESTAMP                           SYSDATE
        ENTRY_USERID                              “ERA 3.0 CONVERSION”
        FIRM_NAME                                 PARTICIPANT.FIRM_NAME
        FIRST_NAME                                PARTICIPANT.PARTICIPANT_NAME - All characters prior to first
                                                  space
        LAST_NAME                                 PARTICIPANT.PARTICIPANT_NAME - All characters after the first
                                                  space
        PARTICIPANT_TYPE_CODE                     PARTICIPANT.PRTCPNT_TYPE_CD – see mapping below
        PHONE_NO                                  PARTICIPANT.PHONE_NUMBER – parse out all ‘-‘ and then convert
                                                  to a number
        UPDATE_TIMESTAMP                          SYSDATE
        UPDATE_USERID                             “ERA 3.0 CONVERSION”


           PARTICIPANT_TYPE_CODE

              ERA 3.0 PARTICIPANT_TYPE_CODE                               ERA 2.1 PRTCPNT_TYPE_CD

        AOF            Appellant Official                      AOF              Appellant Official
        APC            Appeal Coordinator                      APC              Appeal Co-ordinator
        CFA            Counsel for Appellant                   CFA              Counsel for Appellant
        CFM            Counsel for Ministry                    CFM              Counsel for Ministry
        PCR            Panel Chair                             PCR              Panel Chair
        PMC            Primary Ministry Contact                PMC              Primary Ministry Contact
        PMR            Panel Member                            PMR              Panel Member
        ROF            Review Official                         ROF              Review Official
                                                               No value    in
        ADL            Ad Hoc Lawyer                           ERA 2.1
        WIT            Witness                                 WIT              Witness
        AOF            Appellant Official                      AOF              Appellant Official
        APC            Appeal Coordinator                      APC              Appeal Co-ordinator
        CFA            Counsel for Appellant                   CFA              Counsel for Appellant


4.5.7      ADMIN_REVIEW
           Insert an ADMIN_REVIEW record if the Case Type is Admin and the Appeal Type
           is Review.
                  ADMIN_REVIEW                                            VALUE TO USE




                                                                                                            Page 34
 Ministry of Forests                                                        Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        CASE_APPEAL_ID                              CASE_APPEAL.CASE_APPEAL_ID
        DECISION_NOTIFICATION_DATE                  NULL
        ENTRY_TIMESTAMP                             APPEAL.ENTRY_TIMESTAMP
        ENTRY_USERID                                APPEAL.ENTRY_USERID
        REVIEW_REQUEST_DATE                         APPEAL.APL_RQST_RECVD_DT
        SERVICE_DATE                                APPEAL.OUTCOME_NOTICE_DT
        UPDATE_TIMESTAMP                            APPEAL.UPDATE_TIMESTAMP
        UPDATE_USERID                               APPEAL.UPDATE_USERID
        ERA_USER_ID                                 ERA_USER.ERA_USER_ID              –          Lookup          using
                                                    APPEAL.REV_OFFICIAL_ID


4.5.8      MOF_REVIEW
           Insert a MOF_REVIEW record if the Case Type is Incident and the Appeal Type is
           Review.
                  MOF_REVIEW                                                VALUE TO USE

        ACTUAL_OUTCOME_DATE                         APPEAL.OUTCOME_NOTICE_DT
        CASE_APPEAL_ID                              CASE_APPEAL.CASE_APPEAL_ID
        APPELLANT_NOTIFICATION_DATE                 APPEAL.PANEL_ID_NOTICE_DT
        ENTRY_TIMESTAMP                             APPEAL.ENTRY_TIMESTAMP
        ENTRY_USERID                                APPEAL.ENTRY_USERID
        GROUNDS_FOR_REVIEW                          APPEAL.GROUNDS_FOR_APPEAL
        LATEST_OUTCOME_DATE                         NULL
        OTHER_REJECTION_REASON                      NULL
        PANEL_ACTUAL_NOTICE_DATE                    APPEAL.PANEL_ID_DATE
        PANEL_IDENTIFICATION_DATE                   Oustanding Issue: MOF_REVIEW.PANEL_IDENTIFICATION_DATE is
                                                    a new mandatory field. What value should be used to populate this
                                                    field?
        PANEL_LATEST_NOTICE_DATE                    Oustanding Issue: MOF_REVIEW.PANEL_LATEST_NOTICE_DATE is
                                                    a new mandatory field. What value should be used to populate this
                                                    field?
        PERIOD_END_DATE                             APPEAL.APL_PER_START_DT + APPEAL.APPEAL_PERIOD
        PERIOD_START_DATE                           APPEAL.APL_PER_START_DT
        REJECTION_REASON_CODE                       APPEAL.REJECTION_RSN_ST – see mapping below
        REQUEST_RECEIVED_DATE                       APPEAL.APL_RQST_RECVD_DT
        REQUEST_RESUBMISSION_DATE                   NULL
        UPDATE_TIMESTAMP                            APPEAL.UPDATE_TIMESTAMP
        UPDATE_USERID                               APPEAL.UPDATE_USERID
        WITHDRAWAL_DATE                             APPEAL.WITHDRAWN_DATE
        WITHDRAWAL_REASON                           NULL


           REJECTION_REASON_CODE

              ERA 3.0 REJECTION_REASON_CODE                                 ERA 2.1 REJECTION_RSN_ST

        EXP            Eligibility Period Expired                 EXP             Expired
        UAA            Unauthorized Applicant/Appellant           UAA             Unauthorized Applicant/Appellant
                                                                   Not value in
        OTH            Other                                      ERA 2.1




                                                                                                            Page 35
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.5.9      OTHER_APPEAL
           Insert an OTHER_APPEAL record if the Appeal Type is Appeal.
                 OTHER_APPEAL                                  VALUE TO USE

        CASE_APPEAL_ID                CASE_APPEAL.CASE_APPEAL_ID
        ENTRY_TIMESTAMP               APPEAL.ENTRY_TIMESTAMP
        ENTRY_USERID                  APPEAL.ENTRY_USERID
        LT_LITIGATION_STATUS_CODE     Outstanding                Issue:            OTHER_REVIEW.
                                      LT_LITIGATION_STATUS_CODE is a new mandatory field. What
                                      value should be used to populate this field?
        LTRACK_FILE_ID                NULL
        PROCEEDING_NAME               Outstanding Issue: OTHER_REVIEW.PROCEEDING_NAME is a new
                                      mandatory field. What value should be used to populate this field?
        OTHER_AUTHORITY_DESCRIPTION   NULL
        UPDATE_TIMESTAMP              APPEAL.UPDATE_TIMESTAMP
        UPDATE_USERID                 APPEAL.UPDATE_USERID




4.6     CONVERT CONTRAVENTIO N DECISION LINK DATA
      (DETERMINATIONS ONLY )


4.6.1      CONTRAVENTION_DECISION
        The CONTRAVENTION_DECISION will be populated using the information contained
        in DTRM_VLTN_V_LNK. This section only deals with Determinations, Appeals will be
        dealt with later.
           CONTRAVENTION_DECISION                              VALUE TO USE

        COMMENTS                      NULL
        CONTRAVENTION_DECISION_ID     Sequence CONTRAVENTION_DECISION_SEQ
        CASE_CONTRAVENTION_ID         CASE_CONTRAVENTION.CASE_CONTRAVENTION_ID              - Look up
                                      using         DTRM_VLTN_V_LNK.CASE_SKEY                     and
                                      DTRM_VLTN_V_LNK.VIOLATION_NUMBER
        CONTRAVENTION_STATUS_CODE     IF VALIDITY_IND = “Y” THEN
                                        “DTR” -- Determined
                                      ELSE
                                        “NCN” -- No Contravention Found
        CASE_DECISION_ID              CASE_DECISION.CASE_DECISION_ID  –             Look    up   using
                                      DTRM_VLTN_V_LNK.CASE_SKEY                                   and
                                      DTRM_VLTN_V_LNK.DETERMINATION_NUM            in   CASE_DECISION
                                      and CASE_DETERMINATION.
        ENTRY_TIMESTAMP               SYSDATE
        ENTRY_USERID                  “ERA 3.0 CONVERSION”
        UPDATE_TIMESTAMP              SYSDATE
        UPDATE_USERID                 “ERA 3.0 CONVERSION”




                                                                                               Page 36
 Ministry of Forests                                       Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.7     CONVERT ENFORCEMENT ACTION DATA
        This section deals with ERA 3.0 Enforcment Actions. The ERA 2.1 Enforcement
        Actions that map to ERA 3.0 Compliance Actions are dealt with later.

        Create an ENFORCEMENT_ACTION record for each valid ENFRCMNT_ACTION.
        Depending on the ENFRCEMNT_TYPE_CD, zero or one of the Enforcement Action
        subtype tables will be populated. Then the CONTRAVENTION_ENFORCEMENT table
        will be populated for the current ENFORCEMENT_ACTION.

        A valid ENFRCMNT_ACTION is defined as follows:
           WHERE ENFRCMNT_ACTION.ENF_ACTION_STS_ST <> 'ERR' AND
                  ENFRCMNT_TYPE_CD <> ‘INS’ AND
                  NOT (ENFRCMNT_TYPE_CD = ‘NAC’ and DETERMINATION_NUM = 0) AND
                  NOT (ENFRCMNT_TYPE_CD = ‘TIC’ and TICKET_TYPE_CD = ‘W’)

        The following is used to determine the Enforcement Action subtype table that should
        be populated.

CASE WHEN ENFRCMNT_TYPE_CD = ‘CAN’ THEN
         Insert into ENFORCEMENT_CANCELLATION
WHEN ENFRCMNT_TYPE_CD = ‘SUS’ THEN
         Insert into ENFORCEMENT_SUSPENSION
WHEN ENFRCMNT_TYPE_CD = ‘NAC’ Then
         Continue – there is no subtype table for a No Action
WHEN ENFRCMNT_TYPE_CD = ‘OTV’ Then
         Insert into ENFORCEMENT_ORDER_TO_VACATE
WHEN ENFRCMNT_TYPE_CD = ‘PEN’ Then
         Insert into ENFORCEMENT_PENALTY
         Insert into INVOICE_PENALTY
      WHEN ENFRCMNT_TYPE_CD = ‘RDN’ THEN
   Insert into ENFORCEMENT_AAC_REDUCTION
WHEN ENFRCMNT_TYPE_CD = ‘REM’ Then
   Insert into ENFORCEMENT_REMEDIATION
      WHEN ENFRCMNT_TYPE_CD = ‘SBD’ Then
         Insert into ENFORCEMENT_DEREGISTRATION
WHEN ENFRCMNT_TYPE_CD = ‘FOR’ Then
         Insert into ENFORCEMENT_FORFEITURE
      WHEN ENFRCMNT_TYPE_CD = ‘STP’ Then
         Insert into ENFORCEMENT_STOP_WORK_ORDER
      WHEN ENFRCMNT_TYPE_CD = ‘TIC’ Then
         Insert into ENFORCEMENT_TICKET
   Insert into VIOLATION_TICKET, if a VIOLATION_TICKET record                       does       not
already exist.
END CASE




4.7.1      ENFORCEMENT_ACTION

             ENFORCEMENT_ACTION                            VALUE TO USE

        ACTIVITY_COMPLETE_IND          ENFRCMNT_ACTION.COMPLETE_IND
        DESCRIPTION                    ENFRCMNT_ACTION.REMEDY_DESCRIPTION



                                                                                           Page 37
Ministry of Forests                                                 Detailed Data Conversion Plan
Compliance and Enforcement Branch


      ENFORCEMENT_ACTION_ID                  Sequence ENFORCEMENT_ACTION_SEQ
      ENFORCEMENT_ACTION_NO                  ENFRCMNT_ACTION.ENFRCMNT_ACTION_NO
      ENFORCEMENT_OUTCOME_CODE               DETERMINATION.DTRMNTN_STATUS_ST – see mapping below
                                             Look up using ENFRCMNT_ACTION.DETERMINATION_NUM and
                                             ENFRCMNT_ACTION.CASE_SKEY.
      ENFORCEMENT_TYPE_CODE                  ENFRCMNT_ACTION.ENFRCMNT_TYPE_CD
      ENTRY_TIMESTAMP                        ENFRCMNT_ACTION.ENTRY_TIMESTAMP
      ENTRY_USERID                           ENFRCMNT_ACTION.ENTRY_USERID
      PROCESS_IND                            ENFRCMNT_ACTION.COMPLETE_IND
      UPDATE_TIMESTAMP                       ENFRCMNT_ACTION.UPDATE_TIMESTAMP
      UPDATE_USERID                          ENFRCMNT_ACTION.UPDATE_USERID
      ERA_USER_ID                            ERA_USER.ERA_USER_ID     –     Look               up        using
                                             ENFRCMNT_ACTION.ENFRCMNT_OFCR_UID
      VALID_IND                              ENFRCMNT_ACTION.ENF_ACTION_STS_ST - See mapping below
      CASE_DECISION_ID                       CASE_DECISION.CASE_DECISION_ID  –          Look        up   using
                                             ENFRCMNT_ACTION.DETERMINATION_NUM                            and
                                             ENFRCMNT_ACTION.CASE_SKEY.



           VALID_IND

                      ERA 3.0 VALID_IND                          ERA 2.1 ENF_ACTION_STS_ST

       Not                                               ERR
       Converted                                                       Error
       N             Invalid                             INV           Invalid
       Y                                                 UST           Action Not Stayed
       Y             Valid                               VAL           Valid
       Y             Valid                               VAR           Varied


           ENFORCEMENT_OUTCOME_CODE

             ERA 3.0 ENFORCEMENT_OUTCOME_CODE                   ERA 2.1 DTRMNTN_STATUS_ST

                                                         MAPPING
       DTR           Determined                          REQUIRED
       CON           Confirmed                           CON           Confirmed
       NEW           New Determination                   NEW           New Decision
       MAPPING
       REQUIRED                                          PND           Pending
       RSC           Rescinded                           RSC           Rescinded
       MAPPING
       REQUIRED                                          VAL           Valid
       VAR           Varied                              VAR           Varied
                                                         MAPPING
       BWD           Referred Back with Directions       REQUIRED
                                                         MAPPING
       BWO           Referred Back without Directions    REQUIRED




                                                                                                     Page 38
 Ministry of Forests                                                 Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.7.2      CONTRAVENTION_ENFORCEMENT
           For each ENF_VIOLATN_LNK of the current ENFRCMNT_ACTION, create a
           CONTRAVENTION_ENFORCMENT record.
         CONTRAVENTION_ENFORCEMENT                                   VALUE TO USE

        CONTRAVENTION_ENFORCEMENT_ID          Sequence CONTRAVENTION_ENFORCEMENT_SEQ
        CASE_CONTRAVENTION_ID                 CASE_CONTRAVENTION.CASE_CONTRAVENTION_ID           - Look up
                                              using          ENF_VIOLATN_LNK.CASE_SKEY                 and
                                              ENF_VIOLATN_LNK.VIOLATION_NUMBER.
        CURRENT_IND                           “N”
        ENFORCEMENT_ACTION_ID                 ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ENTRY_TIMESTAMP                       SYSDATE
        ENTRY_USERID                          “ERA 3.0 CONVERSION”
        UPDATE_TIMESTAMP                      SYSDATE
        UPDATE_USERID                         “ERA 3.0 CONVERSION”




4.7.3      ENFORCEMENT_TICKET
        Prior to the creation of the ENFORCEMENT_TICKET record a read will be done on the
        VIOLATION_TICKET table to determine if a record exists for the current ticket
        (lookup using TICKET_NUMBER.TICKET_NUMBER). If a record does not exist, then a
        VIOLATION_TICKET record will be created. If a record does exist, no further action
        is required.
               ENFORCEMENT_TICKET                                    VALUE TO USE

        ENFORCEMENT_ACTION_ID                 ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ROW_NUMBER                            ENFRCMNT_ACTION.ENFRCMNT_ACTION_NO
        ENTRY_TIMESTAMP                       ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                          ENFRCMNT_ACTION.ENTRY_USERID
        FINE_AMOUNT                           COMPLIANCE_ARTICLE.TICKETABLE_FINE_AMOUNT – Look up
                                              using ENFORCEMENT_ACTION, CONTRAVENTION_ENFORCEMENT,
                                              CASE_CONTRAVENTION and COMPLIANCE_ARTICLE
        TICKET_AMOUNT                         ENFRCMNT_ACTION.ENFRCMNT_AMOUNT
        UPDATE_TIMESTAMP                      ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                         ENFRCMNT_ACTION.UPDATE_USERID
        VIOLATION_TICKET_ID                   VIOLATION_TICKET.VIOLATION_TICKET_ID –       either from the
                                              select  on  VIOLATION_TICKET    or  the       newly    created
                                              VIOLATION_TICKET record
        VIOLATION_TICKET_STATUS_CODE          ENFRCMNT_ACTION.TICKET_STATUS_ST – see mapping below


           VIOLATION_TICKET_STATUS_CODE

          ERA 3.0 VIOLATION_TICKET_STATUS_CODE                       ERA 2.1 TICKET_STATUS_ST

                                                          Not used    in
         APP           Under Appeal                       ERA 2.1.
                                                          Not used    in
         AQU           Acquitted                          ERA 2.1.
                                                          Not used    in
         CAN           Cancelled by Enforcement Officer   ERA 2.1.



                                                                                                     Page 39
 Ministry of Forests                                                    Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        CAS            Cancelled by ICBC Ticket Admin        CAN              Cancelled
        DIS            In Dispute                            IDP              In dispute
        GEX            Guilty by Expiry                      O/S              Outstanding
        GJU            Guilty by Judgement                   DGY              Disputed-Guilty
        GPY            Guilty by Payment                     PAI              Paid
        IP             In Progress                           OPN              Open
        IP             In Progress                           CLS              Closed
        NGU            Not Guilty                            DNG              Dispute-Not-Guilty
                                                             Not used    in
        QUA            Quashed                               ERA 2.1.
                                                             Not used    in
        RET            Retrial                               ERA 2.1.
                                                             Not used    in
        RPL            Replacement Issued                    ERA 2.1.
        SOP            Stay of Proceedings                   STA              Stayed
                                                             Not used    in
        WDL            Withdrawn                             ERA 2.1.
        n/a            Not converted to ERA 3.0              ERR              Error


4.7.4        VIOLATION_TICKET

                VIOLATION_TICKET                                        VALUE TO USE

        APPEAL_PERIOD                         ENFRCMNT_ACTION.RESPONSE_PERIOD
        ENTRY_TIMESTAMP                       ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                          ENFRCMNT_ACTION.ENTRY_USERID
        CASE_CLIENT_ID                        CASE_CLIENT.CASE_CLIENT_ID   –     Look   up       in
                                              ENFORCEMENT_ACTION, CONTRAVENTION_ENFORCEMENT, AND
                                              CASE_CONTRAVENTION                             using
                                              ENFORCEMENT_ACTION.ENFORCMENT_ACTION_ID.
        LICENCE_JURISDICTION_CODE             NULL
        SERVICE_DATE                          ENFRCMNT_ACTION.OFFENDER_NOTICE_DT
        TICKET_NO                             TICKET_NUMBER.TICKET_NUMBER
        UPDATE_TIMESTAMP                      ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                         ENFRCMNT_ACTION.UPDATE_USERID
        ISSUED_BY_USER_ID                     ERA_USER.ERA_USER_ID     –     Look                     up       using
                                              ENFRCMNT_ACTION.ENFRCMNT_OFCR_UID
        SERVED_BY_USER_ID                     Outstanding Issue: How is this field to be populated?
        VEHICLE_PLATE_NO                      NULL
        VIOLATION_TICKET_ID                   Sequence VIOLATION_TICKET_SEQ
        YOUNG_OFFENDER_IND                    “N”




4.7.5        ENFORCEMENT_FORFEITURE

             ENFORCEMENT_FORFEITURE                                     VALUE TO USE

        ENFORCEMENT_ACTION_ID                 ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ENTRY_TIMESTAMP                       ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                          ENFRCMNT_ACTION.ENTRY_USERID


                                                                                                           Page 40
 Ministry of Forests                                       Detailed Data Conversion Plan
 Compliance and Enforcement Branch


        RECOVERABLE_COSTS            ENFRCMNT_ACTION.RECOVERABLE_COSTS
        UPDATE_TIMESTAMP             ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                ENFRCMNT_ACTION.UPDATE_USERID


4.7.6      ENFORCEMENT_REMEDIATION

          ENFORCEMENT_REMEDIATION                          VALUE TO USE

        ACTUAL_COMPLETION_DATE       ENFRCMNT_ACTION.ACTUAL_CMPLTN_DATE
        CRITICAL_COMPLETION_IND      ENFRCMNT_ACTION.CRITICL_CMPLTN_IND
        ENFORCEMENT_ACTION_ID        ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ENTRY_TIMESTAMP              ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                 ENFRCMNT_ACTION.ENTRY_USERID
        INSPECTION_ID                INSPECTION.INSPECTION_NO - Lookup MAX(INSPECTION_NO) in
                                     INSPECTION    using   ENFRCMNT_ACTION.CASE_SKEY    and
                                     ENFRCMNT_ACTION.ENFRCMNT_ACTION_NO.
        MINISTRY_ASSUMED_IND         ENFRCMNT_ACTION.ASMD_BY_MSTRY_IND
        PLAN_APPROVED_IND            ENFRCMNT_ACTION.PLAN_APPROVED_IND
        PLAN_REQUIRED_IND            ENFRCMNT_ACTION.PLAN_REQUIRED_IND
        SCHEDULED_COMPLETION_DATE    ENFRCMNT_ACTION.SCHED_CMPLTN_DATE
        SUBMISSION_DATE              ENFRCMNT_ACTION.PLAN_SUBMITTED_DT
        EXTERNAL_INSPECTION_IND      If INSPECTION_ID IS NULL THEN
                                       “N”
                                     ELSE
                                       “Y”
        UPDATE_TIMESTAMP             ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                ENFRCMNT_ACTION.UPDATE_USERID


4.7.7




                                                                                           Page 41
 Ministry of Forests                                         Detailed Data Conversion Plan
 Compliance and Enforcement Branch




4.7.8      ENFORCEMENT_STOP_WORK_ORDER

          ENFORCEMENT_REMEDIATION                            VALUE TO USE

        ENFORCEMENT_ACTION_ID        ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ENTRY_TIMESTAMP              ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                 ENFRCMNT_ACTION.ENTRY_USERID
        CASE_CLIENT_ID               CASE_CLIENT.CASE_CLIENT_ID – Look up by accessing
                                     ENFORCEMENT_ACTION, CONTRAVENTION_ENFORCEMENT,
                                     CASE_CONTRAVENTION, and CASE_CLIENT.
        EXTINGUISHED_DATE            If LIFTED_REASON_CD = “CWC” THEN
                                         ENFRCMNT_ACTION.WRK_ORDR_LIFTED_DT
                                     Else
                                          NULL
        RESCINDED_DATE               If LIFTED_REASON_CD = “RSC” THEN
                                         ENFRCMNT_ACTION.WRK_ORDR_LIFTED_DT
                                     Else
                                          NULL ENFRCMNT_ACTION.WRK_ORDR_LIFTED_DT
        EXTINGUISHED_REASON          If LIFTED_REASON_CD = “CWC” THEN
                                         ENF_REMARK_TEXT.LINE_TEXT
                                     Else
                                          NULL
        RESCINDED_REASON             If LIFTED_REASON_CD = “RSC” THEN
                                         ENF_REMARK_TEXT.LINE_TEXT
                                     Else
                                          NULL
        REVIEW_DATE                  ENFRCMNT_ACTION.OFFENDER_NOTICE_DT
        SERVICE_DATE                 ENFRCMNT_ACTION.OFFENDER_NOTICE_DT
        STOP_WORK_ORDER_NO           0
        STOP_WORK_STATUS_CODE        ENFRCMNT_ACTION.LIFTED_REASON_CD – see mapping below
        UPDATE_TIMESTAMP             ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                ENFRCMNT_ACTION.UPDATE_USERID
        ISSUED_BY_USERID             ERA_USER.ERA_USER_ID - Look Up by
                                     ENFRCMNT_ACTION.ENFRCMNT_OFCR_UID
        EXTINGUISHED_BY_USERID       If LIFTED_REASON_CD = “CWC” THEN
                                         ERA_USER.ERA_USER_ID
                                     Else
                                           NULL ERA_USER.ERA_USER_ID
                                         - Look Up by ENFRCMNT_ACTION.LIFTED_BY_USERID
        RESCINDED_BY_USERID          If LIFTED_REASON_CD = “RSC” THEN
                                         ERA_USER.ERA_USER_ID
                                     Else
                                           NULL ERA_USER.ERA_USER_ID
                                         - Look Up by ENFRCMNT_ACTION.LIFTED_BY_USERID
        VERBAL_NOTICE_DATE           NULL




           The RESCINDED_REASON or EXTINGUSIHED_REASON will be built from the
           ERMFORCEMENT_REMARK and ENFORCEMENT_REMARK_TXT tables.         The
           following pseudo code explains how to create a single reason:



                                                                                             Page 42
 Ministry of Forests                                        Detailed Data Conversion Plan
 Compliance and Enforcement Branch


           For each remark in ENFORCEMENT_REMARK (for the current enforcement
           action)
                REASON = NULL
                For each line in ENFORCEMENT_REMARK_TXT
                     REASON = REASON || “ “ || ENFORCEMENT_REMARK_TXT.TEXT_LINE
                End For
           End For
           If length(REASON) < 2000
                log an error
           Else
                Use   REASON  to   populate   either     EXTINGUISHED_REASON    or
           RESCINDED_REASON
           End If

           STOP_WORK_STATUS_CODE

              ERA 3.0 STOP_WORK_STATUS_CODE              ERA 2.1 LIFTED_REASON_CD

                                                 Not used    in
        INE            In Effect                 ERA 2.1.
        EXT            Extinguished              CWC              Complied with Conditions
        RSC            Rescinded                 RSC              Rescinded




4.7.9      ENFORCEMENT_DEREGISTRATION

        ENFORCEMENT_DEREGISTRATION                          VALUE TO USE

        CONTRACT_NO                   NULL
        DEREGISTRATION_PERIOD         ENFRCMNT_ACTION.SB_SUSP_PERIOD
        EFFECTIVE_DATE                ENFRCMNT_ACTION.ENFRCMNT_EFCTV_DT
        ENFORCEMENT_ACTION_ID         ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ENTRY_TIMESTAMP               ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                  ENFRCMNT_ACTION.ENTRY_USERID
        UPDATE_TIMESTAMP              ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                 ENFRCMNT_ACTION.UPDATE_USERID




4.7.10     ENFORCEMENT_ORDER_TO_VACATE

         ENFORCEMENT_ORDER_TO_VACATE                         VALUE TO USE

        ALTERNATE_LOCATION               NULL
        ENFORCEMENT_ACTION_ID            ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
        ENTRY_TIMESTAMP                  ENFRCMNT_ACTION.ENTRY_TIMESTAMP
        ENTRY_USERID                     ENFRCMNT_ACTION.ENTRY_USERID
        ORDER_TO_VACATE_SITE_CODE        “OTH”
        SERVICE_DATE                     ENFRCMNT_ACTION.OFFENDER_NOTICE_DT
        UPDATE_TIMESTAMP                 ENFRCMNT_ACTION.UPDATE_TIMESTAMP
        UPDATE_USERID                    ENFRCMNT_ACTION.UPDATE_USERID




                                                                                             Page 43
 Ministry of Forests                                            Detailed Data Conversion Plan
 Compliance and Enforcement Branch




             ORDER_TO_VACATE_SITE_CODE

                      ORDER_TO_VACATE_SITE_CODE

        RSI                    Recreation Site
        TR                     Trail
        IFS                    Interpretive Forest Site
        WDA                    Wilderness Area
        OTH                    Other




4.7.11       ENFORCEMENT_SUSPENSION

          ENFORCEMENT_SUSPENSION                                VALUE TO USE

       CUTTING_PERMIT_ID                    ENFRCMNT_ACTION.CUTTING_PERMIT_ID
       EFFECTIVE_DATE                       ENFRCMNT_ACTION.ENFRCMNT_EFCTV_DT
       ENFORCEMENT_ACTION_ID                ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
       ENTRY_TIMESTAMP                      ENFRCMNT_ACTION.ENTRY_TIMESTAMP
       ENTRY_USERID                         ENFRCMNT_ACTION.ENTRY_USERID
       FOREST_FILE_ID                       ENFRCMNT_ACTION.FOREST_FILE_ID
       SUSPENSION_PERIOD                    ENFRCMNT_ACTION.SB_SUSP_PERIOD
       TIMBER_MARK                          ENFRCMNT_ACTION.TIMBER_MARK
       UPDATE_TIMESTAMP                     ENFRCMNT_ACTION.UPDATE_TIMESTAMP
       UPDATE_USERID                        ENFRCMNT_ACTION.UPDATE_USERID


4.7.12       ENFORCEMENT_CANCELLATION

         ENFORCEMENT_ CANCELLATION                              VALUE TO USE

       EFFECTIVE_DATE                       ENFRCMNT_ACTION.ENFRCMNT_EFCTV_DT
       ENFORCEMENT_ACTION_ID                ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
       ENTRY_TIMESTAMP                      ENFRCMNT_ACTION.ENTRY_TIMESTAMP
       ENTRY_USERID                         ENFRCMNT_ACTION.ENTRY_USERID
       FOREST_FILE_ID                       ENFRCMNT_ACTION.FOREST_FILE_ID
       UPDATE_TIMESTAMP                     ENFRCMNT_ACTION.UPDATE_TIMESTAMP
       UPDATE_USERID                        ENFRCMNT_ACTION.UPDATE_USERID


4.7.13       ENFORCEMENT_AAC_REDUCTION

         ENFORCEMENT_AAC_REDUCTION                              VALUE TO USE

       EFFECTIVE_DATE                       ENFRCMNT_ACTION.ENFRCMNT_EFCTV_DT
       ENFORCEMENT_ACTION_ID                ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
       ENTRY_TIMESTAMP                      ENFRCMNT_ACTION.ENTRY_TIMESTAMP
       ENTRY_USERID                         ENFRCMNT_ACTION.ENTRY_USERID
       FOREST_FILE_ID                       ENFRCMNT_ACTION.FOREST_FILE_ID
       REDUCTION_AMOUNT                     ENFRCMNT_ACTION.REDUCTION_AMOUNT



                                                                                                Page 44
 Ministry of Forests                                                 Detailed Data Conversion Plan
 Compliance and Enforcement Branch


       AAC_REDUCTION_TYPE_CODE              To be provided by MOF for each AAC Reduction.
       UPDATE_TIMESTAMP                     ENFRCMNT_ACTION.UPDATE_TIMESTAMP
       UPDATE_USERID                        ENFRCMNT_ACTION.UPDATE_USERID




4.7.14     ENFORCEMENT_PENALTY
      When an ENFORCEMENT_PENALTY record is created, an INVOICE_PENALTY record
      will also be created if ENFRCMNT_ACTION.INVOICE_NUMBER is not NULL.

               ENFORCEMENT_ PENALTY                                  VALUE TO USE

       COMPENSATORY_PENALTY_AMOUNT          ENFRCMNT_ACTION.COMPENSATORY_AMT
       DETERRENT_PENALTY_AMOUNT             ENFRCMNT_ACTION.DETERRENT_AMT
       ENFORCEMENT_ACTION_ID                ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
       ENTRY_TIMESTAMP                      ENFRCMNT_ACTION.ENTRY_TIMESTAMP
       ENTRY_USERID                         ENFRCMNT_ACTION.ENTRY_USERID
       PENALTY_TYPE_CODE                    ENFRCMNT_ACTION.PENALTY_TYPE_CD – see below
       UPDATE_TIMESTAMP                     ENFRCMNT_ACTION.UPDATE_TIMESTAMP
       UPDATE_USERID                        ENFRCMNT_ACTION.UPDATE_USERID




           PENALTY_TYPE_CODE

                       ERA 3.0 PENALTY_TYPE_CODE                            ERA 2.1 PENALTY_TYPE_CD

         117                  Section 117 of FPC                      117                Sec 117 of FPC
         119(3)               Section 119(3) of FPC                   MAPPING
                                                                      REQUIRED
         PRE                  Pre-FPC Penalty under Forest Act        MAPPING
                                                                      REQUIRED
         117(pnlty)           Section 117 of FPC (penalty)            MAPPING
                                                                      REQUIRED
         118(4)               Section 118 (4) of FPC (remediation)    MAPPING
                                                                      REQUIRED
         118(5)               Section 118 (5) of FPC (remediation)    MAPPING
                                                                      REQUIRED
         119(UAH)             Section 119 of FPC (penalty UAH)        MAPPING
                                                                      REQUIRED
         MAPPING REQUIRED                                             118                Sec 118 of FPC
         MAPPING REQUIRED                                             119                Sec 119 of FPC
         MAPPING REQUIRED                                             162                Sec 162 of FPC-
                                                                                         Demand Note
         MAPPING REQUIRED                                             BTH                Both Sec 117 and
                                                                                         119
         MAPPING REQUIRED                                             FAP                Forest Act Penalty
         MAPPING REQUIRED                                             PRE                Pre-FPC      Penalty
                                                                                         under FA




                                                                                                     Page 45
 Ministry of Forests                                       Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.7.15    INVOICE_PENALTY

               INVOICE_PENALTY                            VALUE TO USE

       ENFORCEMENT_ACTION_ID          ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID
       CANCELLED_IND                  “N”
       ENTRY_TIMESTAMP                ENFRCMNT_ACTION.ENTRY_TIMESTAMP
       ENTRY_USERID                   ENFRCMNT_ACTION.ENTRY_USERID
       INVOICE_NUMBER                 ENFRCMNT_ACTION.INVOICE_NUMBER
       INVOICE_PENALTY_ID             Sequence INVOICE_PENALTY_SEQ
       UPDATE_TIMESTAMP               ENFRCMNT_ACTION.UPDATE_TIMESTAMP
       UPDATE_USERID                  ENFRCMNT_ACTION.UPDATE_USERID




4.8   CONVERT COMPLIANC E ACTION DATA
      This section deals with ERA 3.0 Compliance Actions. The ERA 2.1 Enforcement
      Actions that map to ERA 3.0 Enforcment Actions were dealt with earlier.

      Create an COMPLIANCE_ACTION record for each valid ENFRCMNT_ACTION.

      A valid ENFRCMNT_ACTION is defined as follows:
         WHERE ENFRCMNT_ACTION.ENF_ACTION_STS_ST <> 'ERR' AND
                (ENFRCMNT_TYPE_CD = ‘INS’ OR
                 (ENFRCMNT_TYPE_CD = ‘NAC’ AND DETERMINATION_NUM = 0)OR
                 (ENFRCMNT_TYPE_CD = ‘TIC’ AND TICKET_TYPE_CD = ‘W’))



             COMPLIANCE_ACTION                            VALUE TO USE

       COMPLETION_FLAG_IND            ENFRCMNT_ACTION.COMPLETE_IND
       COMPLIANCE_ACTION_ID           Sequence COMPLIANCE_ACTION_SEQ
       COMPLIANCE_ACTION_TYPE_CODE    ENFRCMNT_ACTION.ENFRCMNT_TYPE_CD       –   see   below     for
                                      mappings
       EFFECTIVE_DATE                 ENFRCMNT_ACTION.ENFRCMNT_EFCTV_DT
       ENFORCEMENT_ACTION_NO          ENFRCMNT_ACTION.ENFRCMNT_ACTION_NO
       ENFORCEMENT_ACTION_STATUS_ST   ENFRCMNT_ACTION.ENF_ACTION_STS_ST
       ENFORCEMENT_OFFICER            ENFRCMNT_ACTION.ENFRCMNT_OFCR_UID
       ENFORCEMENT_TYPE_CD            ENFRCMNT_ACTION.ENFRCMNT_TYPE_CD
       ENTRY_TIMESTAMP                ENFRCMNT_ACTION.ENTRY_TIMESTAMP
       ENTRY_USERID                   ENFRCMNT_ACTION.ENTRY_USERID
       INS_DECISION_DATE              ENFRCMNT_ACTION.INS_DECISION_DATE
       NAC_DECISION_DATE              ENFRCMNT_ACTION.NAC_DECISION_DATE
       OFFENDER_NOTICE_DATE           ENFRCMNT_ACTION.OFFENDER_NOTICE_DT
       TICKET_NO                      TICKET_NUMBER.TICKET_NUMBER      –     Look   up   using
                                      ENFRCMNT_ACTION.CASE_SKEY       and     ENFRCMNT_ACTION.
                                      ENFRCMNT_ACTION_NO.
       TICKET_STATUS_ST               ENFRCMNT_ACTION.TICKET_STATUS_ST
       UPDATE_TIMESTAMP               ENFRCMNT_ACTION.UPDATE_TIMESTAMP




                                                                                           Page 46
 Ministry of Forests                                        Detailed Data Conversion Plan
 Compliance and Enforcement Branch


       CASE_CONTRAVENTION_ID            CASE_CONTRAVENTION.CASE_CONTRAVENTION_ID – Look up in
                                        ENF_VIOLATN_LNK    and    CASE_CONTRAVENTION    using
                                        ENFRCMNT_ACTION.CASE_SKEY     and   ENFRCMNT_ACTION.
                                        ENFRCMNT_ACTION_NO.
       UPDATE_USERID                    ENFRCMNT_ACTION.UPDATE_USERID


          COMPLIANCE_ACTION_TYPE_CODE

              ERA 3.0 COMPLIANCE_ACTION_TYPE_CODE               ERA 2.1 ENFRCMNT_TYPE_CD

        INS       Instructions Issued                     INS     Instructions Issued
        NAC       Informal No Action                      NAC     No Action
        TIC       Warning Ticket                          TIC     Ticket




4.9   CLONE DATA FOR APPEA LS
      To align the ERA 2.1 data with the ERA 3.0 data model, the Appeal data will require
      some tweaking. In ERA 2.1, when appeals were created, no history was maintained.
      The original Determination was updated. However, the ERA 3.0 data model requires
      that history be kept. In order to support the ERA 3.0 data model, the Enforcement
      Action data and Contravention Decision data from a Determination will be cloned for
      any Appeal that references the Determination. The following pseudo code outlines
      how this will be accomplished.

For each Incident ENFORCEMENT_CASE
    For each CASE_APPEAL
        Loop thru each ENFORCEMENT_ACTION for the CASE_APPEAL’s associated
CASE_DETERMINATION
            Create a copy of the current ENFORCEMENT_ACTION with the
following changes:
               - ENFORCEMENT_ACTION_ID = Sequence ENFORCEMENT_ACTION_SEQ
               - CASE_DECISION_ID = CASE_DECISION_ID associated with the
CASE_APPEAL
            Create a copy of each CONTRAVENTION_ENFORCEMENT related to the
current ENFORCEMENT_ACTION
            Create a copy of the Enforcement Action Sub Type record (if one
exists) for the current ENFORCEMENT_ACTION
        End Loop

        Create a copy of the CONTRAVENTION_DECISION associated with the
CASE_APPEAL’s associated CASE_DETERMINATION.     The following change is
required:
            -  CASE_DECISION_ID =  CASE_DECISION_ID  associated with  the
CASE_APPEAL

    End For
End For




                                                                                            Page 47
 Ministry of Forests                                         Detailed Data Conversion Plan
 Compliance and Enforcement Branch


4.10 SET CONTRAVENTION_EN FORCEMENT CURRENT_IN D
      Once the Enforcement Action data is cloned for Appeals there is a need to identify for
      a Contravention, which instance of a particular Enforcement Action is the current
      Enforcement Action. This update will be accomplished as follows:

For each CONTRAVENTION
    For Each Distinct ENFORCEMENT_ACTION for the current CONTRAVENTION
      Find the most recent instance of the ENFORCEMENT_ACTION
      UPDATE CONTRAVENTION_ENFORCEMENT
                SET CURRENT_IND = “Y”
            WHERE CONTRAVENTION_ID = current contravention
            AND ENFORCEMENT_ACTION_ID = most recent ENFORCEMENT_ACTION
    End For
End For

      Note: The most recent instance of an ENFORCEMENT_ACTION is identified by the
      ENFORCEMENT_ACTION with the maximum DECISION_ID. When Decisions are
      created for a Case, they are entered sequentially by Determinations first and then
      sequentially by Appeals.


4.11 CONVERT SEIZU RE DATA
      All SEIZED_GOODS records associated with a valid ERA_CASE will cause a single
      SEIZURE record to be created.
                    SEIZURE                                  VALUE TO USE

       AMOUNT_FROM_SALE               SEIZED_GOODS.SALE_AMOUNT
       ASSOCIATED_COSTS               SEIZED_GOODS.ASSOCIATED_COSTS
       ENFORCEMENT_CASE_ID            SEIZED_GOODS.CASE_SKEY
       DISPOSITION_CODE               SEIZED_GOODS.DISPOSITION_CODE
       DISPOSITION_DATE               SEIZED_GOODS.DISPOSITION_DATE
       ENFORCEMENT_ACTION_ID          ENFORCEMENT_ACTION.ENFORCEMENT_ACTION_ID – Look up
                                      using SEIZED_GOODS.ENFRCMNT_ACTION_NO for the Current
                                      Enforcement Action.
       ENTRY_TIMESTAMP                SEIZED_GOODS.ENTRY_TIMESTAMP
       ENTRY_USERID                   SEIZED_GOODS.ENTRY_USERID
       CASE_CLIENT_ID                 NULL
       IMPOUND_AREA                   SEIZED_GOODS.IMPOUND_AREA
       LOCATION                       SEIZED_GOODS.SEIZURE_LOCATION
       SEIZED_GOODS_AMOUNT            0
       SEIZED_GOODS_CODE              SEIZED_GOODS.SZD_GDS_TYPE_CD
       SEIZED_GOODS_DESCRIPTION       SEIZED_GOODS.DESCR_OF_GOODS
       SEIZED_GOODS_VALUE             0
       SEIZURE_DATE                   SEIZED_GOODS.SEIZED_DATE
       SEIZURE_DESCRIPTION            SEIZED_GOODS.DESCRIPTION_OF_SEIZED_GOODS
       SEIZURE_ID                     Sequence SEIZURE_SEQ
       SEIZURE_NO                     SEIZED_GOODS.SEIZURE_NUMBER
       SEIZURE_REASON_CODE            Outstanding Issue – What value should be used to populate
                                      SEIZURE.SEIZURE_REASON_CODE?
       UPDATE_TIMESTAMP               SEIZED_GOODS.UPDATE_TIMESTAMP




                                                                                             Page 48
 Ministry of Forests                                         Detailed Data Conversion Plan
 Compliance and Enforcement Branch


       UPDATE_USERID                   SEIZED_GOODS.UPDATE_USERID
       ERA_USER_ID                     ERA_USER.ERA_USER_ID SEIZED_GOODS.INVESTIGATD_BY_UID




4.12 SET ERA SEQUEN CES
       The following sequences will have to be reset after the data has been converted.

                   SEQUENCE                           NEW VALUE FOR SEQUENCE

       CASE_ID_SEQ                     SELECT MAX(CASE_ID) + 1 FROM ENFORCEMENT_CASE;
       CASE_SITE_SEQ                   SELECT MAX(CASE_SITE_ID) + 1 FROM CASE_SITE;




5. POST CONVERSION DATA VALIDATION

    In order to qualify the conversion as a success, the data in the ERA 3.0 database will
    have to be checked for correctness. Several data validation techniques will be used,
    including (but not limited to): record counts for tables, comparison of reports from ERA
    2.1 to values from ERA 3.0, and ocular comparison.

    To assist with validation, MOF is to provide a detailed report report of all
    contraventions, enforcement actions, determinations and appeals on a case. This
    should be done for a number of cases in ERA 2.1. The cases should include simple
    cases, complex cases, and cases with unusual information.

    The objective of generating these reports is to ensure that the data coming out of the
    new system is as close of a match to that from the old system as is possible.
    Formatting and layout is not a priority, as the reports are being generated solely for the
    purpose of data validation.



6. ROLLBACK

In the unlikely event that the conversion is not successful and a rollback is required, the
following steps will be performed:
            Turn on/enable the VM based ERA 2.1 so that the application once again
              provides full functionality.
            Turn on the replication process that replicates the ERA 2.1 data from SQLP1
              to DBR01.

Note: The ERA 3.0 database will remain in its unsuccessful conversion state. This may be
useful for determining what went wrong with the conversion. Prior to the next conversion
attempt, the ERA 3.0 database will have to be cleared of all data populated in the previous
conversion attempt.



                                                                                             Page 49
Ministry of Forests                 Detailed Data Conversion Plan
Compliance and Enforcement Branch




                                                                    Page 50
 Ministry of Forests                                           Detailed Data Conversion Plan
 Compliance and Enforcement Branch




7. APPENDIX A: ERA CONVERSION ERROR TABL E

During the conversion process, conversion errors and warnings will be logged into a the
temporary conversion error table ERA_CONVERSION_ERROR, created within the ERA 3.0
database instance. This table will provide sufficient detail so that the reason for the error or
warning can be investigated.

The process for inserting into this table will run in an independent transaction, to ensure
that errors in the conversion process will not interfere with error logging. Once the
conversion is complete and the release is ready for use, the table will be archived and
removed.

 ERA_CONVERSION_ERROR

           COLUMN              NULLS          TYPE                        CONTAINS

 ERA_CONVERSION_ERROR_ID      NOT NULL   NUMBER           Primary key
 CASE_ID                      NOT NULL   NUMBER           Relevant Case    Id   number   in    ERA3.0
                                                          environment
 LEVEL                        NOT NULL   VARCHAR2(20)     ‘ERROR’, ‘WARNING’
 CONTEXT                      NOT NULL   VARCHAR2(2000)   Name of current procedure, any available id
                                                          numbers or surrogate keys
 COMMENT                      NOT NULL   VARCHAR2(2000)   Error message




                                                                                               Page 51

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:8/8/2012
language:English
pages:51