Method and system for migrating data

Abstract

Method and system of the invention migrate data from one or more ASCII files and/or from one or more relational databases to one or more relational database tables without the need to write code. In doing so, the invention allows the user to define mapping templates and conditionals to assist in translating and transforming data values. The invention also enforces referential integrity, data dependencies, order of operations, and uniqueness constraints using a predefined set of migration rules templates that are based on the principles of relational design. The invention uses these mapping and migration rules templates to intelligently generate instructions for updating or populating relational database destination tables. The instructions control the data transfer, data translation, data transformation, data validation, foreign key insertion, and the addition of required codes and flags in the destination tables. A migration engine of the system includes a data map architect and an update processor which spawns the templates and migrates the data dynamically, utilizing the data definitions for the destination tables. In addition to supporting conversion efforts, the invention provides support for performing consolidation, restoration from an archive, migration to new instances, upgrading to a new release, adding bolt-ons and enhancements, and changing business requirements. The audit trail in the invention is complete enough that the entire migration process can be reversed without compromising the integrity of either the source or the destination application.

Claims

What is claimed is:

1. An automated computer-implemented method for migrating source data from at least one source to at least one destination table of a database having a schema without a user having to write computer code, the method comprising the steps of:

defining patterns which describe format and content of the source data;

applying the patterns to the source data to create transformed data;

associating migration rules based on the schema with the patterns to generate a set of instructions that define migration paths; and

loading the transformed data in a sequence into the at least one destination table based on the set of instructions, the at least one destination table having a defined format and destination fields, wherein the step of loading is automatically sequenced based on the migration rules so that referential integrity is maintained.

2. The method as claimed in claim 1 further comprising the step of loading the data from the at least one source into at least one temporary table having source fields.

3. The method as claimed in claim 2 wherein the at least one temporary table is a relational database table.

4. The method as claimed in claim 2 wherein the at least one source is an ASCII file and wherein the method further comprises the steps of creating an extract table with the data from the at least one source and loading the extract table into the at least one temporary table.

5. The method as claimed in claim 2 wherein the at least one source is a relational database table and wherein the step of loading the data from the at least one source includes the step of automatically loading the relational database table into the at least one temporary table.

6. The method as claimed in claim 2 further comprising the step of associating the source fields with the destination fields.

7. The method as claimed in claim 6 further comprising the step of creating templates for transforming or translating the data from the at least one source to obtain the transformed data.

8. The method as claimed in claim 7 further comprising the step of utilizing a computer to generate user information and wherein the step of creating is based on the computer-generated user information.

9. The method as claimed in claim 8 further comprising the step of automatically inserting logical and arithmetic operators in universal or conditional statements.

10. The method as claimed in claim 6 further comprising the steps of creating at least one intermediate table having a format and fields and linking the at least one temporary table and its fields with the at least one intermediate table and its fields wherein the format and the fields of the at least one intermediate table are substantially identical to the format and fields of the at least one destination table and wherein the transformed or translated data is utilized to populate the at least one intermediate table.

11. The method as claimed in claim 7 wherein the step of creating the templates is performed interactively with the user.

12. The method as claimed in claim 7 wherein the templates include data migration patterns having conditionals with logical operators to provide instructions for translating and transforming the data from the at least one source.

13. The method as claimed in claim 12 wherein the patterns include a transfer mapping pattern.

14. The method as claimed in claim 12 wherein the patterns include a monovariate translation pattern.

15. The method as claimed in claim 12 wherein the patterns include a multivariate concatenation translation pattern.

16. The method as claimed in claim 12 wherein the patterns include a multivariate reverse concatenation translation pattern.

17. The method as claimed in claim 12 wherein the patterns include a multivariate transformation pattern.

18. The method as claimed in claim 7 wherein the set of instructions are generated based on the templates and the schema.

19. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for uniqueness constraints.

20. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for verifying data dependencies.

21. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for enforcing referential integrity.

22. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for moving the data into the at least one destination table.

23. The method as claimed in claim 7 further comprising the step of utilizing the templates to validate the data in the at least one temporary table for checking that mandatory destination fields are filled.

24. The method as claimed in claim 7 further comprising the step of grouping the templates into batches.

25. The method as claimed in claim 1 wherein the database is a relational database and the at least one destination table is a relational database table.

26. The method as claimed in claim 1 wherein the data is migrated from a plurality of sources.

27. The method as claimed in claim 1 wherein the transformed data loaded into the at least one destination table updates or adds to records already in the at least one destination table.

28. The method as claimed in claim 27 wherein the data is migrated from a plurality of sources.

29. The method as claimed in claim 1 wherein the transformed data loaded into the at least one destination table changes the data already in the at least one destination table.

30. The method as claimed in claim 29 further comprising the step of changing base configurations including flex fields.

31. The method as claimed in claim 18 further comprising the step of adding bolt-ons or other modifications to a relational application.

32. The method as claimed in claim 1 wherein archival data is restored.

33. The method as claimed in claim 1 wherein the source data is migrated for one installation or instance of the database to another.

34. The method as claimed in claim 1 wherein the source data is migrated from one version of the database to another.

35. The method as claimed in claim 1 wherein the source data is migrated across environments such as from a development environment to a test environment, a training environment or a production environment without changing setup parameters.

36. The method as claimed in claim 1 wherein the source data is converted from a legacy system to a new system.

37. The method as claimed in claim 1 wherein multiple instances of a subledger are consolidated in a multiorg environment.

38. The method as claimed in claim 1 wherein systems in support of mergers or acquisitions are consolidated.

39. The method as claimed in claim 1 wherein the transformed data is loaded into multiple destination tables substantially simultaneously and wherein the multiple tables includes at least one table which might later be loaded onto a separate system.

40. A computer-implemented system including a migration engine for migrating data from at least one source to at least one destination table of a database having a schema without a user having to write computer code, the migration engine comprising:

a data map architect for creating a set of migration rules based on the schema; and

an update processor for:

generating a set of instructions that define migration paths based on the migration rules;

transforming the data based on the set of instructions to obtain transformed data; and

loading the transformed data in the at least one destination table based on the set of instructions, the at least one destination table having a format and fields, wherein the update processor loads the transformed data in an automatic sequence so that referential integrity is maintained.

41. The system as claimed in claim 40 wherein the data map architect loads the data from the at least one source into at least one temporary table having source and destination fields.

42. The system as claimed in claim 41 wherein the at least one temporary table is a relational database table.

43. The system as claimed in claim 41 wherein the at least one source is an ASCII file and wherein the data map architect also creates an extract table with the data from the at least one source and loads the extract table into the at least one temporary table.

44. The system as claimed in claim 41 wherein the at least one source is a relational database table and wherein the data map architect automatically loads the relational database table into the at least one temporary table.

45. The system as claimed in claim 41 wherein the data map architect also associates the source fields with the destination fields based on the schema.

46. The system as claimed in claim 45 wherein the data map architect loads the data from the at least one source into the at least one temporary table and verifies data dependencies without the user having to write computer code.

47. The system as claimed in claim 45 wherein the data map architect creates templates for transforming the data from the at least one source based on the schema to obtain the transformed data.

48. The system as claimed in claim 45 further comprising at least one intermediate table having a format and fields and wherein the update processor links the at least one temporary table and its fields with the at least one intermediate table and its fields wherein the format and the field of the at least one intermediate table are substantially identical to the format and fields of the at least one destination table and wherein the update processor populates the at least one intermediate table with the transformed data.

49. The system as claimed in claim 47 wherein the data map architect creates the templates interactively with the user.

50. The system as claimed in claim 47 wherein the templates include data migration patterns and logical operators to provide instructions for translating and transferring the data from the at least one source.

51. The system as claimed in claim 50 wherein the patterns include a transfer mapping pattern.

52. The system as claimed in claim 50 wherein the patterns include a monovariate translation pattern.

53. The system as claimed in claim 50 wherein the patterns include a multivariate concatenation translation pattern.

54. The system as claimed in claim 50 wherein the patterns include a multivariate reverse concatenation translation pattern.

55. The system as claimed in claim 50 wherein the pattern includes a multivariate transformation pattern.

56. The system as claimed in claim 47 wherein the update processor generates the set of instructions based on the templates and the schema.

57. The system as claimed in claim 41 wherein the update processor moves the data from the at least one source to the at least one temporary table.

58. The system as claimed in claim 57 wherein the update processor enforces uniqueness constraints without the user having to write computer code.

59. The system as claimed in claim 57 wherein the update processor enforces referential integrity without the user having to write computer code.

60. The system as claimed in claim 57 wherein the update process specifies order of operations for moving data into the at least one destination table without the user having to write computer code.

61. The system as claimed in claim 57 wherein the update processor checks that mandatory fields are populated without the user having to write computer code.

62. The system as claimed in claim 48 wherein the update processor moves the data from the at least one source first to the at least one temporary table where it is transformed and then to the at least one intermediate table for correction to obtain corrected data.

63. The system as claimed in claim 47 wherein the update processor groups the templates into batches.

64. The system as claimed in claim 40 wherein the data base is a relational data base and the at least one destination table is a relational database table.

65. The system as claimed in claim 40 wherein the migration engine migrates the data from a plurality of sources.

66. The system as claimed in claim 40 wherein the transformed data loaded into the at least one destination table by the update processor updates or adds to records already in the at least one destination table.

Description

TECHNICAL FIELD

This invention relates to methods and systems for migrating data and, in particular, to automated methods and systems for migrating data without having to write code.

BACKGROUND ART

The prior art limits its application to a single function (either data conversion or, to a limited extent, data migration among similar databases). There is no prior art defined that is generic enough to cover multiple applications and yet specific enough to allow for tasks such as consolidation, restoration of archived data, and changing configurations. The majority of the prior work only deals with the conversion of data.

There are generally four methods for converting data. The first method, and most traditional approach, begins by extracting data from an original system (source) to an ASCII file. The second step in the approach is to create a script (usually written in SQL) and use a program (such as SQL*Loader.TM.) to populate an Oracle table (destination). The destination table may be either a table or an application programmatic interface which is effectively a standard intermediate table provided by Oracle which performs an update routine to load data into the final application tables. FIG. 1 illustrates the traditional conversion process. The SQL code is used to map the data into the appropriate field in the destination database and to translate the data from the source format to the destination format. In addition, a programmer might add required fields, enforce any data dependencies (e.g. if the value in field 1 is A, then the value in field 2 must be B) and validate the data (e.g. if a unit of measure for an item is dozen, then the value dozen must be present in a related table or list of values as a valid unit of measure). This code is usually extremely complex to write and maintain. Appendix A shows a sample portion of a SQL*Loader script that might be used to load vendor information. Appendix B is an extract of sample SQL code used to check for data dependencies prior to loading accounts receivable data. A typical company will have more than 100 of these SQL scripts to code and maintain to convert their data for just financial applications.

A second method to load data is to cut and paste that data from a spreadsheet. Records copied from a spreadsheet and using a Microsoft Windows.RTM. paste function are inserted into an Oracle table. Subsequent manipulation of the data similar to that of the traditional method (FIG. 1) is performed by a programmer using SQL code to add additional required fields, validate the data, and enforce dependencies. Again, an intermediate table or application programmatic interfaces may be used prior to loading the data into the final application tables.

The third method of converting data is to utilize a tool to populate Oracle tables. The tools currently on the market generate the SQL code to map source fields to destination fields. Some of these tools allow for adding required fields or check for data validation or enforce data dependencies, but these checks are hard-coded into the SQL script for specified tables. Very few of the tools enforce referential integrity and maintain foreign key relationships. Prior art utilizes programs (generally in SQL) to move data from an old database to a new one. These tools generate the SQL code interactively (e.g. table by table, and, in many cases, instruction by instruction).

The final method is to manually enter data into the new application tables usually from a print out or report of the source data.

The following table indicates products of different companies, their functionality and their limitations:

    ______________________________________
    Company
           Product   Functionality
                                 Limitations
    ______________________________________
    SMART  Smart DB  Transforms and
                                 Performs no other function
      Corp.  maps legacy except conversion, and then
        data to new only translates and
        application. transforms the values of the
        Generates data.
        and executes The product maps table to
        SQL code to table, rather than
        insert, update, understanding the
        or delete relationships in the entire
        data. Graphi- application. It can only
        cally joins accommodate data that
        tables and originates in one table with
        views. Manipu- a single destination table.
        lates data val- Does not enforce referential
        ues among integrity, data dependen-
        multiple tables. cies, uniqueness con-
         straints. Does not provide
         patterns, pre-defined migra-
         tion rules. Audit Trail not
         complete enough to reverse
         transactions.
         Some pre-defined templates
         are provided for Oracle
         Applications .TM.. Requires
         recoding, and new template
         definition for new or
         updated versions of the
         destination tables.
      Constellar Constellar Ongoing moving Requires expert knowledge
      Corp. Hub of transaction of programming languages
        and operational (Cobol, SQL, C) and
        data to and from Oracle7 .RTM. architecture.
        legacy systems Does not appear to enforce
        into databases referential integrity, data
        (Oracle, DB/2, dependencies, uniqueness
        Informix, IDMS) constraints. Performs no
        and files. Popu- functions other than
        lating and refresh- conversion and data
        ing of data ware- mining. Does not provide
        houses. Generates patterns, pre-defined migra-
        C, Cobol and tion rules. Requires recod-
        Pro*C source ing for new or updated ver-
        code. Uses Trans- sions of the destination
        formation Defini- tables. Does not come
        tion Language to seeded with data particular
        create business to Oracle Applications .TM.,
        rules to filter or with predefined migra-
        data. Supports tion rules.
        data dictionary
        and CASE reposi-
        tories in the
        transformation of
        data. Constantly
        sits on network
        and manages data
        flow.
      Oracle Data Stores data in a DCT has no drivers or
      Corp. Conversion central repository. control files specific to
       Tool (part of Manages data Oracle Applications .TM..
       Enterprise conversions by DCT has no predefined
       Data generating code rules for migrating data and
       Management to map data maintaining relational and
       System) from legacy data integrity. The user
       (DCT) systems to must specify the order of
        Oracle .RTM. data- the conversion execution.
        bases or from DCT alone does not enforce
        Oracle to a flat referential integrity, data
        file. DCT mines dependencies, or unique-
        transactions for ness constraints.
        data warehouse DCT requires extensive
        and data marts programming expertise in
        and performs an SQL and PL/SQL to define
        actual extract of the conditions of data
        legacy data. Uses transfer, and to create the
        gateways for non- load packages.
        Oracle systems to DCT requires knowledge of
        create extracts of relational database
        legacy data. DCT architecture and the design
        can convert flat of the destination system,
        files to relational and knowledge of the
        tables, or tables to SQL*Loader utility's
        flat files. concepts and features to
         define the migration
         process. Performs no other
         function except conversion.
         Does not provide patterns,
         pre-defined migration rules.
         Only supports conversion to
         a single destination table
         for each result. In other
         words, for a source to load
         more than one destination
         table would require the user
         to define separate conver-
         sion groups and create sep-
         arate SQL scripts. Does not
         support one-to-one or
         many-to-one transforma-
         tions within a single con-
         version group. The DCT
         user must write and test his
         own SQL scripts and
         PL/SQL packages for
         transformation of the
         data. The user must
         also create the required
         cursors for each group. The
         user creates a script that
         executes the stored
         procedures and passes the
         needed parameters to
         schedule and control the
         migration process. For a
         file based conversion, the
         control script must also
         FTP the files to the desti-
         nation system and then exe-
         cute a SQL*Loader process
         to perform the data load.
      Chain Chain Link Manages multiple Only migrates a small set of
      Link Object instances of the Oracle Application Object
      Tech- Migrator Application Ob- Library .TM. tables.
      nologies, Chain Link ject Library .TM. Performs no other function
                                  Inc. Migration tables across mul- except
                                 migration to differ-
       Manager tiple Oracle .RTM. ent database instances.
        RDBMS environ- Does not provide patterns,
        ments (produc- pre-defined migration rules,
        tion, test, deve- translation, or
        lopment, etc.) transformation of data.
        Migrates data Requires recoding for new
        from Application or updated versions of the
        Object Library .TM. destination tables.
        tables to different
        Oracle database
        instances.
      Rockport Conversion The Conversion Conversion Toolbox does
      Software, Toolbox Toolbox performs not provide for generation
      Inc.  conversion of of data translation or trans-
        data from a formation instructions. It
        legacy system in- allows a user to change val-
        to selected Oracle ues (e.g. from doz. to doz-
        Applications .TM. en), but does not provide
        tables. The pro- any rules or conditionals for
        duct also claims doing so. The user merely
        to be able to sup- indicates the new value
        port two way in- (field-by-field) to be moved
        terfaces, and sup- to the destination tables.
        port changes to The mapping facility of the
        the accounting Conversion Toolbox is lim-
        flexfield, al- ited and allows only for sin-
        though the latter gle source to a single desti-
        functionality was nation table translation. The
        not complete in actual values in the source
        the version of the are not displayed on the
        software evalu- screen within the software.
        ated. The soft- The user must define the
        ware dynamically translated values based on
        generates driver prior knowledge of the leg-
        tables to migrate acy system's data. The
        the data and vali- Conversion Toolbox can
        date data depen- only be used for convers
        dencies. It allows ions and table-to-table map-
        for translation of ping. Conversion Toolbox
        values and addi- has no predefined tem-
        tion of new re- plates. Does not provide
        quired data to in- patterns, predefined migra-
        termediate tables. tion rules. Audit Trail not
        These driver complete enough to reverse
        tables provide transactions. Does not en-
        generic migration force referential integrity,
        instructions to uniqueness constraints
        the toolbox. In without significant user
        other words, the input. Performs no other
        Conversion Tool- function except conversion.
        box allows the
        user to create
        templates to gen-
        erate the migra-
        tion instructions.
        These migration
        instructions use
        predefined driver
        tables to move
        the data from the
        source to the des-
        tination. The
        Conversion Tool-
        box uses a single
        intermediate table
        to translate and
        transform the



        data. This use of
        a single table
        limits the migra-
        tion to a single
        destination table
        and makes the en-
        forcement of ref-
        erential integrity
        depend on the
        user's knowledge
        of the precise
        migration order
        that must be fol-
        lowed to move
        the data. Using
        the Conversion
        Toolbox, the user
        might have to
        define thousands
        of templates to
        migrate an entire
        application and
        keep track of the
        order of migration
        in order to ensure
        that all of the
        templates are
        applied in the
        proper sequence.
    ______________________________________

    ______________________________________
    Definitions
    ______________________________________
    Batch        A group of template-generated data and
       migration rules that are uploaded into
       the intermediate or destination tables
       simultaneously.
      Conditionals Rules that define how data is to be
       translated or transformed prior to load-
       ing in destination tables.
      Data The value of the data in one field is
      Dependency dependent on the value of the data in
       another field.
      Data Map The part of the invention that prepares
      Architect the source data and establishes the
       mapping constraints on the format and
       content of the data to be migrated from
       the source to the destination.
      Data Migration Pre-defined templates based on the knowl-
      Rules edge of the Destination tables that
       enforce template order, migration load
       sequence, referential integrity, unique-
       ness constraints, and data dependencies.
      Destination A group of destination tables linked
      Application through data relationships. Pointers in
       the application designate locations of
       related destination tables through use of
       the unique identifiers of those tables.
      Destination A table within a database such as an
      Table Oracle database to which data is migrated
       using the invention. The characteristics
       of the Destination Tables dictate the
       migration rules the invention follows to
       populate the data.
      Intermediate A database table such as an Oracle table
      Table into which the source data is loaded in
       order for the invention to add migration
       or transformation batch control fields.
       The invention uses an Intermediate Table
       to manipulate the data prior to moving
       that data into the Destination Tables.
       The format and fields in the Intermediate
       Table are identical to those of the
       destination table.
      Level Coding Process to locate and update records by
       which the tables with no foreign key
       relationships are populated first, then
       the table with a single foreign key to
       the first table is populated, building up
       until the tables with multiple different
       foreign key relationships are populated
       and the relationships are maintained
       throughout the destination application.
      Pattern Description of the relationship type that
       the Migration Engine uses to create rules
       that enforce referential and data integ-
       rity as it moves data from the Source to
       the Destination.
      Mapping The process used by the invention to
       match the data values, the data format,
       and the context of the data, between the
       source data and the destination table.
      Migration Transfers, translates, and transforms the
      Engine format and content of data values from a
       Source Application to the format and
       characteristics of the values required by
       the Destination Application. Applies
       mapping templates and utilizes intelli-
       gence about the destination tables, the
       relationships among the data, the unique
       identifiers of the data, the validation,
       and data dependencies required in the
       destination tables to create a set of
       migration rules. Populates destination
       tables by generating defined migration
       paths.
      Monovariate The value A in Source Field is translated
      Translation into a different value in Destination
      Pattern Field. Monovariate Translation Pattern
       changes the format of a value or a con-
       stant in the Source to a new format or
       value in the Destination. This transla-
       tion occurs 100% of the time.
      Multivariate A Multivariate Concatenation Translation
      Concatenation Pattern derives a single Destination
      Translation Value from multiple Source Values.
      Pattern
      Multivariate Multivariate Reverse Concatenation pat-
      Reverse terns take one field from the Source and
      Concatenation create many fields in the Destination
      Translation Tables. This Pattern usually occurs when
      Pattern a value represents a concatenated value
       in the Source.
      Multivariate A Multivariate Transformation derives
      Transformation multiple Destination Values from multiple
      Pattern Source Values.
      Referential Maintenance of the foreign key informa-
      Integrity tion for a relational database. FIG. 2
       shows a foreign key relationship.
      Schema The definition or description of the
       logical structure of a database such as
       attributes (fields) and domains and
       parameters of the attributes. The schema
       determines the migration characteristics
       of the destination.
      Sequence The order in which update instructions
      Rules and templates are applied to a destina-
       tion table by the invention. Since tem-
       plates can overwrite the previous value
       in a field, applying templates out of
       order can affect data integrity. Se-
       quencing allows the user to control the
       ending values.
      Source Data The original data either in an ASCII file
       or a table of a database such as an
       Oracle table. This data will either be
       migrated to or transformed into the
       destination table by the invention.
      Template Groups of values, patterns and rules that
       provide instructions to the update Pro-
       cessor. The invention copies template-
       generated values to corresponding fields
       in a destination table. The invention
       allows for application of templates
       across multiple records simultaneously.
      Template The invention orders the application of
      Order the generated templates using principles
       of relational database design in a se-
       quence to maintain the referential and
       data integrity of the migration process.
       (Also see Sequence Rules.)
      Transfer The value A in Source Field is inserted
      Mapping into the value A in Destination Field.
      Pattern This data is in the same format in both
       the Source and Destination. No changes
       are required.
      Update Utilizes the templates created with the
      Processor Data Map Architect to generate a set of
       instructions that define migration paths
       from the source data to the destination
       tables and populate the destination
       tables.
      Validation The process used by the invention to make
       sure that resulting data values are among
       the range of valid values in the destina-
       tion application.
    ______________________________________

    ______________________________________
            Migration
      Relationship Engine Pattern
      Type Name Explanation/Example
    ______________________________________
    One-to-One
            Transfer   The value A in Source Field is inserted as
      (1:1) Mapping the value A in Destination Field. This data
        is in the same format in both the Source
        and Destination. No changes are required.
        Ven-Name in Source = VENDOR.sub.--
        NAME in Destination.
        Source Value "Smith" Destination Value
        "Smith"
      One-to-One Monovariate The value A in Source Field is translated
                        (1:1) Translation into a different value in Destinatio
                       n Field.
        Monovariate Translation Pattern changes
        the format of a value or a constant in the
        Source to a new format or value in the
        Destination. This translation occurs 100%
        of the time.
        Unit-of-Measure in Source =
        UNIT.sub.-- OF.sub.-- MEASURE in Destination.
        "DZ" in Source Field "DOZ" in
        Destination Field.
      Many-to- Multivariate A Multivariate Concatenation Translation
      One (M:1) Concatenation Pattern derives a single Destination Value
                         Translation from multiple Source Values.
        "Accounting Code Combination" in the
        Destination might be derived from the
        combination of "Segment.sub.-- 1",
        "Segment.sub.-- 2, " and "Segment.sub.-- 3" in the
        Source Table.
      One-to- Multivariate Multivariate Reverse Concatenation
      Many (1:M) Reverse patterns take one field from the Source
       Concatenation and create many fields in the Destination
       Translation Tables. Source Item Number "R075"
        might create a color code value of "Red"
        in the Destination Field "Color" and a
        Item Number of "075" in the Destination
        Field "Item Number"). This Pattern
        usually occurs when a value represents a
        concatenated value in the Source.
      Many-to- Multivariate A Multivariate Transformation derives
      Many (M:M) Transformation multiple Destination Values from multiple
                          Source Values. A vendor having multiple
        addresses that is listed as separate vendor
        records in a Source application may be
        translated to a single vendor record in the
        Destination with multiple sites. This
        Pattern can also be used for many-to-one
        transformations instead of Multivariate
        Concatenation Translation when the
        Source fields interact to derive the
        Destination data, such as multiplying
        number of units by unit price in the Source
        to get a total cost value in the Destination.
    ______________________________________

                                      APPENDIX A
    __________________________________________________________________________
    SQL*Loader script for vendor contact data file
    __________________________________________________________________________
    Vendor.sub.-- row
              MD.sub.-- Vendors%rowtype;
    BIS.sub.-- Vendor.sub.-- ID
                number(15);
    BIS.sub.-- Site.sub.-- ID
              number(15);
    BIS.sub.-- Contact.sub.-- ID
                number(15);
    Found.sub.-- Match
                Boolean;
    Found.sub.-- Site
              Boolean;
    Already.sub.-- Found
                Boolean;
    t.sub.-- cnt
            number;
    PROCEDURE Create.sub.-- Vendor.sub.-- Contact (M.sub.-- rowid ROWID) IS
    t.sub.-- contact.sub.-- name
              varchar2(40);
      Begin
    SELECT first.sub.-- name.vertline..vertline.last.sub.-- name
        INTO t.sub.-- contact.sub.-- name
      FROM POX.sub.-- VENDORS M
      WHERE M.rowid = M.sub.-- rowid;
    If NOT(t.sub.-- contact.sub.-- name IS NULL) then
     Get next id from unique sequence generator
    SELECT PO.sub.-- VENDOR.sub.-- CONTACTS.sub.-- S.nextval
        INTO BIS.sub.-- contact.sub.-- id
      FROM DUAL;
     insert FFIN contact information into BIS table
    INSERT INTO PO.sub.-- VENDOR.sub.-- CONTACTS (
        vendor.sub.-- contact.sub.-- id,
      last.sub.-- update.sub.-- date,
      last.sub.-- updated.sub.-- by,
      vendor.sub.-- site.sub.-- id,
      creation.sub.-- date,
      created.sub.-- by,
      first.sub.-- name,
      last.sub.-- name,
      area.sub.-- code,
      phone)
    SELECT
        BIS.sub.-- Contact.sub.-- id
     vendor
    .sub.-- contact.sub.-- id
    sysdate,
     last
    .sub.-- update.sub.-- date
    `2`,
     last
    .sub.-- updated.sub.-- by
    BIS.sub.-- site.sub.-- id,
     vendor
    .sub.-- site.sub.-- id
    sysdate,
     creation
    .sub.-- date
    `2`,
     created
    .sub.-- by
    substr(M.last.sub.-- name,1,decode(instr(M.last.sub.-- name,` `,1,1),0,
              1,
               instr(M.last.sub.-- name,` `,1,1)),--first.sub.-- name
    substr(M.last.sub.-- name,decode(instr(M.last.sub.-- name,` `,1,1),0,
              1,
               instr(M.last.sub.-- name,` `,1,1))+1), -- last.sub.-- name
    M.area.sub.-- code,
     area
    .sub.-- code
    M.phone
     phone
    FROM POX.sub.-- VENDORS M
      WHERE M.rowid = M.sub.-- rowid;
      End If;
    EXCEPTION
      WHEN DUP.sub.-- VAL.sub.-- ON.sub.-- INDEX THEN
        UPDATE POX.sub.-- VENDORS M
          SET error.sub.-- flag = `Y`,
            error.sub.-- reason = `Duplicate Contact Key`
    __________________________________________________________________________


                                  APPENDIX B
    __________________________________________________________________________
    SQL Script to Check for Data Dependencies
    __________________________________________________________________________
    Check CUST.sub.-- TRX.sub.-- TYPE.sub.-- NAME for UNKNOWN
    if lines.sub.-- rec.cust.sub.-- trx.sub.-- type.sub.-- name not in (`FRSI
    OPEN INVOICE`,
                             `FRSI OPEN CREDIT MEM`,
                              `FRSI OPEN DEBIT MEMO`
      ) then
    lines.sub.-- rec.cust.sub.-- trx.sub.-- type.sub.-- name:=`FRSI OPEN
    ADJ`;
      end if;
     Check for Duplicate Transaction Flexfield errors - append Description to
    Line Number
     if lines.sub.-- rec.interface.sub.-- line.sub.-- attribute1 = v.sub.--
    invoice.sub.-- number and
    lines.sub.-- rec.interface.sub.-- line.sub.-- attribute2 = v.sub.--
    line.sub.-- number then
      lines.sub.-- rec.interface.sub.-- line.sub.-- attribute2 := lines.sub.--
     rec.interface.sub.-- line.sub.-- attribute2
          .vertline..vertline. substr(lines.sub.-- rec.description,1,10);
           duplicate.sub.-- count := duplicate.sub.-- count + 1;
    end if;
      v.sub.-- line.sub.-- number  := lines.sub.-- rec.interface.sub.--
    line.sub.-- attribute2;
     ****Validate Bill to Customer
    v.sub.-- bill.sub.-- address := lines.sub.-- rec.orig.sub.-- system.sub.--
     bill.sub.-- address.sub.-- ref;
     BEGIN
        SELECT addr.sub.-- cust.sub.-- no
          into v.sub.-- bill.sub.-- customer
      FROM frs.sub.-- cust.sub.-- addresses
    WHERE addr.sub.-- orig.sub.-- cust.sub.-- ref.vertline..vertline.addr.sub.
    -- orig.sub.-- addr.sub.-- ref.vertline..vertline.addr.sub.-- site.sub.--
    use.sub.-- code = lines.sub.-- rec.orig.sub.-- system.sub.-- bill.sub.--
    address.sub.-- ref;
      EXCEPTION
          WHEN NO.sub.-- DATA.sub.-- FOUND THEN
            dbms.sub.-- output.put.sub.-- line(`BILL-TO Customer NOT FOUND on
            Mapping Table`.vertline..vertline.lines.sub.-- rec.orig.sub.--
            system.sub.-- bill.sub.-- address.sub.-- ref);
      v.sub.-- bill.sub.-- customer := `01568`;
      v.sub.-- bill.sub.-- address := `0156800B`;
    END;
     ****Validate Ship to
    v.sub.-- ship.sub.-- address := lines.sub.-- rec.orig.sub.-- system.sub.--
     ship.sub.-- address.sub.-- ref;
     BEGIN
        SELECT addr.sub.-- cust.sub.-- no
         INTO v.sub.-- ship.sub.-- customer
          FROM frs.sub.-- cust.sub.-- addresses
    WHERE addr.sub.-- orig.sub.-- cust.sub.-- ref.vertline..vertline.addr.sub.
    -- orig.sub.-- addr.sub.-- ref.vertline..vertline.addr.sub.-- site.sub.--
    use.sub.-- code = lines.sub.-- rec.orig.sub.-- system.sub.-- ship.sub.--
    address.sub.-- ref;
      EXCEPTION
          WHEN NO.sub.-- DATA.sub.-- FOUND THEN
            v.sub.-- ship.sub.-- customer := `01568`;
      v.sub.-- ship.sub.-- address := `0156800S`;
    END;
     ****UPDATE ***********************************************************
    UPDATE ra.sub.-- interface.sub.-- lines
     SET
        attribute15  = `Lookup Processed`,
    cust.sub.-- trx.sub.-- type.sub.-- name
                     = lines .sub.-- rec.cust.sub.-- trx.sub.-- type.sub.--
                     name,
      term.sub.-- name = lines.sub.-- rec.term.sub.-- name,
      interface.sub.-- line.sub.-- attribute2 = lines.sub.-- rec.interface.sub
                     .-- line.sub.-- attribute2,
    orig.sub.-- system.sub.-- bill.sub.-- customer.sub.-- ref = v.sub.--
    bill.sub.-- customer,
        orig.sub.-- system.sub.-- bill.sub.-- address.sub.-- ref = v.sub.--
        bill.sub.-- address,
      orig.sub.-- system.sub.-- ship.sub.-- customer.sub.-- ref = v.sub.--
        ship.sub.-- customer,
      orig.sub.-- system.sub.-- ship.sub.-- address.sub.-- ref = v.sub.--
        ship.sub.-- address,
      ong.sub.-- system.sub.-- sold.sub.-- customer.sub.-- ref = v.sub.--
        bill.sub.-- customer,
    created.sub.-- by
                    = v.sub.-- user.sub.-- id,
    last.sub.-- updated.sub.-- by
                      = v.sub.-- user.sub.-- id
    WHERE rowid = lines.sub.-- rec.rowid;
      v.sub.-- commit.sub.-- counter := v.sub.-- commit.sub.-- counter + 1;
        total.sub.-- counter := total.sub.-- counter + 1;
    IF v.sub.-- commit.sub.-- counter > 10 THEN
        COMMIT;
      v.sub.-- commit.sub.-- counter := 0;
          dbms.sub.-- output.put.sub.-- line(`Total rows committed in
          RA.sub.-- INTERFACE.sub.-- LINES: ` .vertline..vertline. total.sub.-
          - counter);
    END IF;
        END LOOP;
    dbms.sub.-- output.put.sub.-- line(`Total rows committed in RA.sub.--
    INTERFACE.sub.-- LINES: ` .vertline..vertline. total.sub.-- counter);
     dbms.sub.-- output.put.sub.-- line(`Total duplicate flexfields in
    RA.sub.-- INTERFACE.sub.-- LINES:`
        .vertline..vertline. duplicate.sub.-- count);
    update ra.sub.-- interface.sub.-- lines
      set tax.sub.-- code = `Conversion Tax`
      where trx.sub.-- number in (select trx.sub.-- number
              from ra.sub.-- interface.sub.-- lines
    where tax.sub.-- code = `Conversion Tax`);
    __________________________________________________________________________

• Inventors
  Abrams, Helene G.;
• Assignee
  Crystallize, Inc. (Ann Arbor, MI)
• Published
  Nov-21-2000
• Current US Classes:
  707/100
  707/200
  707/204
• Application #
  056360
• International Classes
  G06F 017/30
• Field of Search
  707/204 707/200 707/100 707/202 707/4 707/10 707/205 707/101
• Examiner
  Breene; John
• Agent
  Brooks & Kushman P.C.
• US Patent References:
  5278978
  5379419
  5416917
  5566332
  5596746
  5642505
  5708828
  5745703
  5966704
  5970490