Skip Headers

Agile Recipe Management for Pharmaceuticals Import/Export Guide Release 9.3.3 E39291-01 |
| Contents

Previous | Next |

# 3 Schema, Criteria, and Criteria Properties Files

In the Export/Import operation, every business object must be associated to a Schema template file, a Criteria template file, and a Criteria Properties template file. The Criteria Properties template file must be modified before the export operation.

## 3.1 Schema Files

For the Export/Import tool, every business object must have an associated schema file. A schema file defines the structure of a business object. It is an XML representation of the business object's data model. A data model consists of tables and relationships that are part of a business object.

Each schema file contains one primary category and all other categories are directly or indirectly related to this category. The export program processes the schema file and determines the tables from which data needs to be exported for a particular business object.

### 3.1.1 When to Change Values in the Schema File

You may need to change values in the schema file in the following instances:

- When a new category should be included as part of the business object to be exported.

- An existing relationship has changed for a business object.

### 3.1.2 Example Schema File

This is an example of a schema file for exporting the Site business object. The Entity Relationship model for the Site business object is shown.

The schema file to export data from the tables for a Site business object contains the following information:

    <?xml version="1.0" encoding="UTF-8"?>
    <!--
      Definition file for Site Business Object, including all the related objects
    -->
    <cfmXML xsi:noNamespaceSchemaLocation="../cfmxml.xsd"
      xmlns:xsi="http://www.w3.org/2010/XMLSchema-instance">
      <SearchInfo>
        <SearchCriteria id="1">
          <CategoryName/>
          <CategoryDBName>D_PPL_ADDRESS</CategoryDBName>
          <!-- Get 'Address' from D_ORG_SITE using searchCriteria 1  -->
          <Relationship name="Address" type="inbound" refid="2">
            <CategoryName/>
            <CategoryDBName/>
            <ObjectKey/>
          </Relationship>
        </SearchCriteria>
        <SearchCriteria id="2" isPrimaryCategory="yes">
          <CategoryName/>
          <CategoryDBName>D_ORG_SITE</CategoryDBName>
        </SearchCriteria>
        <SearchCriteria id="3">
          <CategoryName/>
          <CategoryDBName>D_ORG_SITE_STORAGE</CategoryDBName>
          <Relationship name="Site" refid="2">
            <CategoryName/>
            <CategoryDBName/>
            <ObjectKey/>
          </Relationship>
        </SearchCriteria>
        <SearchCriteria id="4">
          <CategoryName/>
          <CategoryDBName>D_ORG_SITE_EQUIPMENT_LK</CategoryDBName>
          <Relationship name="Site" refid="2">
            <CategoryName/>
            <CategoryDBName/>
            <ObjectKey/>
          </Relationship>
        </SearchCriteria>
        <SearchCriteria id="5">
          <CategoryName/>
          <CategoryDBName>D_ORG_SITE_MATERIAL_TYPE</CategoryDBName>
          <Relationship name="Site" refid="2">
            <CategoryName/>
            <CategoryDBName/>
            <ObjectKey/>
          </Relationship>
        </SearchCriteria>
      </SearchInfo>
    </cfmXML>

### 3.1.3 Schema File Details

The following XML indicates that the schema file belongs to cfmXML schema.

    <?xml version="1.0" encoding="UTF-8"?>
    <!--
      Definition file for Site Business Object, including all the related objects
    -->
    <cfmXML xsi:noNamespaceSchemaLocation="../cfmxml.xsd"
      xmlns:xsi="http://www.w3.org/2010/XMLSchema-instance">

Details of XML elements present in the Schema file are listed in the table.

XML Element | Synopsis | Description | Example
--- | --- | --- | ---
Search Info | <SearchInfo> | A SearchInfo tag is defined for the business object that needs to be exported. SearchInfo can contain one or more SearchCriteria tags. (Details of SearchCriteria is described separately in further sections.) | Based on the Site ER model, Site has an outbound relationship with address. So in the XML you see address category having refid=1, which is the ID of the site category. <SearchCriteria id="1">
Category Name | <CategoryName> | This tag does not contain any value but required for the cfmXML schema. | <CategoryName />
Category DB Name | <CategoryDB Name> | This tag contains the database table name of the category. | <CategoryDBName>D_PPL_ADDRESS</CategoryDBName>
Relationship | <Relationship> | The <Relationship> tag specifies the relationship between categories, this information is specified in the reference ID as an attribute. The type attribute specifies if the relationship is of type inbound or outbound. An inbound relationship is an incoming relationship and an outbound relationship refers to an outgoing relationship. Each category should have only one level of relationship information which is not nested. All the tags under Relationship should be left blank as they are required fields in the cfmXML schema. | <Relationship name= "Address" refid="2" type="inbound"> <CategoryName> </CategoryName> <CategoryDBName> </CategoryDBName> <ObjectKey></ObjectKey> </Relationship>
Search Criteria | <Search Criteria> | Every table or category in a schema file has search criteria. Search criteria can have one or more relationships. Every category in the schema file should have a direct or an indirect relationship with a primary category. Every search criteria should have a unique ID. The relationship can be inbound or outbound. | The schema file could have one more categories including one primary category and the primary category should be marked as isPrimaryCategory="yes". <SearchCriteria id="2" isPrimaryCategory="yes"> <CategoryName/> <CategoryDBName> D_ORG_SITE </CategoryDBName> </SearchCriteria>
Additional Info | <Additional Info> | The schema file can have Additional information. The Additional information tag has the Refresh Cache attribute which can be set to yes or no. By setting it to yes or no, you are specifying if the cache has to be refreshed or not after importing. You must set this value to yes or no only in the exported file. | <AdditionalInfo> <Attribute name= "refreshCache"> <Value dataType= "boolean">no</Value> </Attribute> </AdditionalInfo>

## 3.2 Updating Template Files

For exporting specific information, you must specify search criteria values in the search Criteria Properties template file. This update is required to restrict the export to selected data, and to exclude other records associated with the business object.

Template files are available at the location /exim/templates.

The templates folder contains three subordinate folders called Schema, Criteria, and Criteria Properties.

To provide search criteria values for the export operation:

1. Open the criteria properties file that is related to the data you intend to export; the criteria properties files are available at the following location:

exim/templates/criteriaProperties

1. Enter the search criteria value.

### 3.2.1 Optional: Updating the Schema Template File for Cache Refresh

For some imported information to correctly display in RMW, you must do a cache refresh after you perform the update, which is an import option. To refresh cache, update the output file that is created after a successful export operation.

Some business objects that require cache refresh are roles, category, views, subviews, and so on.

To refresh the cache:

1. Edit the output file.

2. In the output file, set the Attribute value to "yes" as shown in the following xml: … - <AdditionalInfo> - <Attribute name="refreshCache"> <Value dataType="boolean">yes</Value> </Attribute> </AdditionalInfo> …

3. Save and close the output file.

## 3.3 Criteria Files

A Criteria file is a collection of search criteria for retrieving a subset of information as part of the export operation. It contains the search criteria to be used when extracting business objects from the database. This file is a template for performing search.

### 3.3.1 Example Criteria File

A sample criteria file for exporting the Site business object is shown here:

    <? xml version="1.0" encoding="UTF-8"?>
    <cfmXML xsi:noNamespaceSchemaLocation="../cfmxml.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <SearchInfo>
        <SearchCriteria id="2">
          <CategoryName />
          <CategoryDBName>D_ORG_SITE</CategoryDBName>
          <ViewName></ViewName>
          <Attribute name="Site ID">
            <Value dataType="String">$$_SITE_ID</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Company">
            <Value dataType="String">$$_COMPANY_NAME</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Parent Site ID">
            <Value dataType="String">$$_PARENT_SITE_ID</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Name">
            <Value dataType="String">$$_SITE_NAME</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Site Type">
            <Value dataType="String">$$_SITE_TYPE</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Is Storage Area">
            <Value dataType="boolean">$$_IS_STORAGE_AREA</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Is Manufacturing Area">
            <Value dataType="boolean">$$_IS_MANUFACTURING</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="Has Privilege">
            <Value dataType="boolean">$$_HAS_PRIVILEGE</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Attribute name="GMP">
            <Value dataType="boolean">$$_GMP</Value>
            <UnitOfMeasure></UnitOfMeasure>
          </Attribute>
          <Relationship name="Environmental Condition">
            <CategoryName/>
            <CategoryDBName>D_ORG_ENVIRONMENT</CategoryDBName>
            <ObjectKey>
              <Attribute name="Name">
                <Value dataType="String">$$_ENV_COND</Value>
              </Attribute>
            </ObjectKey>
          </Relationship>
        </SearchCriteria>
      </SearchInfo>
      <AdditionalInfo>
        <Attribute name="schema">
          <Value dataType="file">../schema/site_schema</Value>
        </Attribute>
        <Attribute name="values">
          <Value dataType="file">../criteriaProperties/site_criteria</Value>
        </Attribute>
      </AdditionalInfo>
    </cfmXML>

### 3.3.2 Criteria File Details

Details of XML elements present in the Criteria file are listed in the table.

XML Element | Synopsis | Description | Example
--- | --- | --- | ---
Search Info | <Search Criteria> | For every search criteria in the schema file you can have a search criteria in the criteria file with all the attributes for that category. Search criteria can have a view name, which is optional. The criteria file corresponds to a view in R&MW but does not contain any search values. Instead of search values, it contains a place holder starting with $$_ substring. If there is no $$, then the substring is treated as a value and not as a placeholder. Syntax: placeholder=value (no space after the "=" symbol) A search criteria has an ID. It should match with the ID in the schema file. | <SearchCriteria id="2"> … <Attribute name="Site ID"> <Value dataType= "String">$$_SITE_ID </Value> <UnitOfMeasure> </UnitOfMeasure> </Attribute> … </SearchCriteria>
Additional Info | <AdditionalInfo> | Criteria file can contain an Additional Info section to provide schema and/or values file details for exporting records. The <AdditionalInfo> tag is used to specify the location and name of the schema and criteria properties file. So you need to specify only the criteria file name at the command line. The file names in this tag should not have an extension as the program automatically adds the extension. Also, the file names are optional. | <AdditionalInfo> <Attribute name="schema"> <Value dataType= "file">../schema/Site_ schema</Value> </Attribute> <Attribute name="values"> <Value dataType= "file">../criteria Properties/Site_criteria </Value> </Attribute>

## 3.4 Criteria Properties Files

The Criteria Properties file is used to specify search values to be used for extracting a subset of data as part of an export operation. Before exporting, the search values in this file must be set. The export operation is based on the values you set in this file.

This file contains values for parameters specified in the criteria file. A parameter may or may not have a value. If it does not have a value in this file then it is not used for searching. Values for the placeholders in the criteria file are given in the criteria properties file.

### 3.4.1 Example Criteria Properties File

A sample criteria properties file is shown here:

Note # is used to comment information in the criteria properties file.

    #
    # Export all Site using site id
    # Values = valid site id
    # eg. $$_SITE_ID=LD005
    $$_SITE_ID=

    # Export all Site belong to a company
    # Values = valid company name
    # eg. $$_COMPANY_NAME=Dragerwerk
    $$_COMPANY_NAME=Dragerwerk

    # Export all Site which belong to one parent site
    # Values = valid site name
    # eg. $$_PARENT_SITE_ID=Global Site
    $$_PARENT_SITE_ID=

    # Export all Site with given name
    # Values = valid site name
    # eg. $$_SITE_NAME=GMP
    $$_SITE_NAME=

    # Export site for the give site type
    # Values = valid site type
    # eg. $$_SITE_TYPE=Storage
    $$_SITE_TYPE=

    # Export All non-storage/storage sites
    # Values = boolean [yes/no]
    # eg. $$_IS_STORAGE_AREA=yes
    $$_IS_STORAGE_AREA=

    # Export All non-manufacturing/manufacturing sites
    # Values = boolean [yes/no]
    # eg. $$_IS_MANUFACTURING=yes
    $$_IS_MANUFACTURING=

    # Export All privileged and non-privileged
    # Values = boolean [yes/no]
    # eg. $$_HAS_PRIVILEGE=yes
    $$_HAS_PRIVILEGE=

    # Export All GMP/non-GMP sites
    # Values = boolean [yes/no]
    # eg. $$_GMP=yes
    $$_GMP=

    # Export All sites with different environment conditions
    # Values = environment types
    # eg. $$_ENV_COND=Refrigerated
    $$_ENV_COND=

### 3.4.2 Criteria Properties File Details

The criteria properties file contains the following information to enable modification and edits:

    #
    # Variable values for exporting site definition
    # One or more of these variables can be used to export Sites(s)
    # You can also use operator along with these values
    # possible operators are
    # > Greater than
    # < Less than
    # & and
    # | Or
    # *,? wild cards
    # User @ symbol for NULL values, eg $$_CREATED_DATE=@
    #
    # All the above wild card can be used with any of the Search attributes other than date
    # like, created date, modified date etc. Date need to be treated special
    # All date values should be inside  '(' and ')' brackets
    # eg. $$_CREATED_DATE=(01/12/2010)
    # you can also give the date format you have used, date format is given inside '{' '}' braces
    # eg. $$_CREATED_DATE=(01/12/2010 {dd/MM/yyyy})
    # Valid operations in dates are.
    # BETWEEN (first date {format}),(second date {format})
    # get all records between two date
    # eg $$_MODIFIED_DATE=BETWEEN (01/01/2010 {MM/dd/yyyy}),(02/08/2010 {MM/dd/yyyy})
    #
    # Greater than/less than/not equal to  ' >/</!= (date {format})'
    # get all records between two date
    # eg $$_MODIFIED_DATE= > (01/01/2010 {MM/dd/yyyy})
    #
    # Possible date formats
    #    Date and Time Pattern                                 Result
    #-----------------------------------------------------------------------
    # yyyy.MM.dd G 'at' HH:mm:ss z           2010.07.04 AD at 12:08:56 PDT
    # EEE, MMM d, ''yy                             Wed, Jul 4, '10
    # h:mm a                                       12:08 PM
    # hh 'o''clock' a, zzzz                  12 o'clock PM, Pacific Daylight Time
    # K:mm a, z                                    0:08 PM, PDT
    # yyyyy.MMMMM.dd GGG hh:mm aaa           02010.July.04 AD 12:08 PM
    # EEE, d MMM yyyy HH:mm:ss Z             Wed, 4 Jul 2010 12:08:56 -0700
    # yyMMddHHmmssZ                          100704120856-0700
    # MM/dd/yyyy                                   04/30/2010
    # MM-dd-yyyy                                   04-30-2010
    #-----------------------------------------------------------------------