Skip Headers

Agile Product Lifecycle Management AIS Developer Guide Release 9.3.3 E39309-01 |
| Contents

Previous |

# 4 Importing Data

This chapter includes:

- Overview

- Understanding the Web Service Import Feature

- Using the importData Web Service Operation

- Importing Data Values

## 4.1 Overview

You can use the `importData` Web service operation of AIS to import data into the Agile PLM databases. The source for the import data can be an Agile database, a third party Product Data Management (PDM) system, or an Enterprise Resource Planning (ERP) system. The Agile server stores information about customer-specific items, such as parts that the company uses to build its products. It also maintains the relationships that assembly parts have with BOM components and that parent items have with approved manufacturers.

For more information on importing data into the Agile PLM system, refer to the Agile PLM Import/Export User Guide.

## 4.2 Understanding the Web Service Import Feature

The following Web service import operations are delivered as part of the AIS:

- `importData` - A Web service operation that imports data into the Agile PLM system.

- `importSupplierResponse` - A Web service operation that imports an RFQ response from a supplier.

Note The `ImportSupplierResponse` Web service operation is deprecated as of Agile 9.0 SP1. Instead, invoke the importData Web service operation and construct a valid `importSupplierResponseRequestType` XML data structure. For more information, see "Importing Supplier Responses." Although the `oldImportSupplierResponse` Web service operation is supported for this release, Oracle recommends migrating your code to the new API.

- `validateData` - A Web service operation that validates source data with compliance rules

## 4.3 Using the importData Web Service Operation

The importData Web service operation exposes all Import Server functionality through a Web service interface that you can access programmatically. This section documents formatting an XML request in order to use the importData Web service operation. For more information on the XML schema that describes an importData request, refer to the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

The `importDataRequest` XML element describes the XML format you must use when submitting an importData request to AIS. It supports the XML data structure types.

Example 4-1 Supported data structure types

    <importDataRequest xsi:type="importDataRequestType">...</importDataRequest><importDataRequest xsi:type="importSupplierResponseRequestType">...</importDataRequest>

### 4.3.1 Specifying Data Types

The importDataRequest XML element allows you to specify several different types of data, including:

- Data Source - The source of the data to be imported.

- Operations - Which import operations should be performed.

- Mapping - How incoming data should be mapped into the Agile PLM system.

- Transformation - How incoming data should be transformed before importing into the Agile PLM system.

- Options - Other options that affect the behavior of the import server.

### 4.3.2 Working with Data Sources

A data source is defined by two pieces of information: the URL that references the data to be imported and a data type that defines what kind of data is being imported. The URL specified can be a reference to either an attachment sent along with the SOAP request, or an external resource. If the URL references an attachment, then the SOAP request can follow either the SwA (SOAP With Attachments) or DIME (Direct Internet Message Encapsulation) encoding rules. For more information on these parameters, refer to the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

The following XML example illustrates how to specify a PDX data source that is sent along with the SOAP request.

Example 4-2 Specifying a PDX data source sent with a SOAP request

    <importDataRequest xsi:type="importDataRequestType"><dataSource><attachmentRef href="cid:E36C913548344EDA1B7FC20CEDCEDEB3"/>typeIPC2571</type></dataSource>...

In the above snippet, the value of the HREF attribute is not intuitive, but it is of the form expected when referencing an attachment sent as part of the SOAP request.

Note: The HREF value is generated by the stub.

### 4.3.3 Working with Operations

By specifying one or more import operations, you can define what data is imported into the Agile PLM system. The following table lists valid import operations.

Operation | Child Attributes
--- | ---
currencyConversion | n/a
customers | n/a
declarations | items, manufacturerParts, partFamilies, itemSubstances, mfrpartSubstances, partFamilySubstances, specifications,attachments
items | aml, bom, sites, attachments, composition, substances, suppliers, specifications, relationships
manufacturerParts | attachments, composition, substances, suppliers, specifications, relationships
manufacturers | attachments, relationships
partgroups | parts, suppliers, specifications, relationships, attachments
prices | priceLines, attachments
productServiceRequests | affectedItems, relatedPSR, relationships, attachments
projectItems | aml, bom, attachments
qualityChangeRequests | affectedItems, relationships, attachments
quoteHistories | quoteHistoryLines
specifications | substances, attachments
substances | materialCompositions, attachments
suppliers | supplier, manufacturerOfferings, commodityOfferings
users | usergroup
usergroups | user

Depending on what you specify, the import server performs the desired import operations and ignores data that is not relevant to the selected import operation. For more information on import operations, see the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

The following code snippet illustrates how to import manufacturers, manufacturer parts, and items. For items, the BOM and AML tables are also imported.

Example 4-3 Importing manufacturers, manufacturer parts, and items

    <operations><manufacturers attachments="false"/><manufacturerParts attachments="false"/><items aml="true" bom="true" sites="false" attachments="false"/></operations>

    ...

### 4.3.4 Working with Mappings

The specified mappings determine how the incoming data is mapped into the Agile PLM system. You can specify mappings either by referencing a previously defined mapping definition file, or by specifying the mappings via the submitted XML data structure. Referencing a previously defined mapping definition file occurs in much the same way as a data source is referenced (that is, via an HREF attribute on the appropriate element). Specifying a mapping via the XML data structure requires specifying the source and target attributes in the appropriate format.

For more information on these parameters, see the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

The following snippet illustrates how to map a field from the incoming PDX package onto the TitleBlock of an item.

Example 4-4 Mapping a field from incoming PDX package to Item's TitleBlock

    ...<mapping><entry><source>/ProductDataeXchangePackage/Items/Item@itemIdentifier</source><target>Part.Title Block.Number</target></entry></mapping>...The following snippet illustrates how you can reference a previously defined mapping definition file....<mapping><attachmentRef href="cid:E36C913548344EDA1B7FC20CEDCEBEEF"/></mapping>...

In the above snippet, the HREF attribute which is generated by the stub is not very intuitive, but it is of the form expected when referencing an attachment sent as part of the SOAP request.

Note: Agile PLM allows you to define an unlimited number of new flex fields for each type of business object. Both the Agile Import wizard and AIS now support user-defined flex fields. Therefore, you can import data to user-defined flex fields.

### 4.3.5 Working with Transforms

Transforms are used to specify the way data is transformed as it is imported into the Agile PLM system. To specify Transforms, use the previously defined transformation definition files as shown in the following example.

Example 4-5 Specifying a Transform

    ...<transform href="cid:E36C913548344EDA1B7FC20CEDCE0123"/>...

In the above snippet, the HREF attribute which is generated by the stub is not very intuitive, but it is of the form expected when referencing an attachment sent as part of the SOAP request. For more information on this parameter, see the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

### 4.3.6 Working with Options

The import server provides several options that you can set in order to alter the behavior of the import server. These options are grouped together into related option groups, which makes it easier to distinguish the purpose of the related options. For more information on these parameters, see the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

The following snippet illustrates how to set several Business Rule, Parsing and Validation options:

Example 4-6 Setting usiness Rule, Parsing and Validation options

    ...<options>    <BusinessRuleOptions>    <ChangeMode value="Authoring"/>    <MultiRowUpdateMode value="AddUpdateOnly"/>   </BusinessRuleOptions>   <ParsingAndValidationOptions>   <CaseValidationAction value="Convert"/>   </ParsingAndValidationOptions></options>

#### 4.3.6.1 ChangeType and ChangeAutoNumber Options

The import server supports setting the following `ChangeType` and `ChangeAutoNumber` options when importing items in the Redline mode. This in addition to setting the same for a ChangeAutoNumber. You have the option to specify a non-existing change in AIS, and the Import server generates the change for the affected ChangeType, `ChangeNumber` or ChangeAutoNumber. When a change order is initiated, the server records a message that includes the type and number of the change in the AIS log file. For more information on these parameters, see the Import XML Schema documentation in "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

- `ChangeType` - This option supports specifying the subclass name or ID of the change order for the ECO,SCO, or MCO. If the change type is invalid, the Import server will reject the entire Import operation and will record a fatal message in the AIS log file.

- ChangeAutoNumber - This option supports generating change numbers with the specified Autonumber. If the specified ChangeAutoNumber is invalid, the Import server will reject the entire import operation and will record a fatal message in the AIS log file.

Note: Do not set the `ChangeNumber` option if you have already invoked the ChangeAutoNumber option.

The following example illustrates how to set the `ChangeMode`, ChangeType, and ChangeAutoNumber options in the aXML file.

Example 4-7 ChangeMode, ChangeType, and ChangeAutoNumber settings in aXML

    ...<options>   <BusinessRuleOptions>   <ChangeMode value="Redline"/>   <ChangeType value="ECO"/>   <ChangeAutoNumber value="ECO AutoNumber"/>   <MultiRowUpdateMode value="AddUpdateOnly"/>   </BusinessRuleOptions>   <ParsingAndValidationOptions>   <CaseValidationAction value="Convert"/>   </ParsingAndValidationOptions></options>...

The parameter in the client-side code that is submitted to generate this XML must contain:

    -n "BusinessRuleOptions|ChangeMode=Redline" "BusinessRuleOptions|ChangeType=ECO" "BusinessRuleOptions|ChangeAutoNumber=ECO AutoNumber" "BusinessRuleOptions|MultiRowUpdateMode=AddUpdateOnly" "ParsingAndValidationOptions|CaseValidationAction=Convert"

#### 4.3.6.2 Options to Import Non-Existing Objects

You have the option to accept or reject importing non-existing objects during an import operation. This behavior is supported by the `BehaviorUponNonExistingObjects` option. This option has two values, Accept and Reject. Accept creates the non-existing objects during import and Reject skips creating these objects.

You can find detailed information about `BehaviorUponNonExistingObjects` in the documentation folder in the AIS_samples.zip file. To access this file, see "AIS Folders." To view the information, select `documentation > schemas > import.htm`.

### 4.3.7 Invoking the ImportDataRequest Operation

The following is a complete example of invoking `importDataRequest`. It shows what a fully configured `importData` operation request will resemble.

Example 4-8 An ImportData Example

    <importDataRequest xsi:type="importDataRequestType">
    <dataSource>

    <attachmentRef href="cid:E36C913548344EDA1B7FC20CEDCEDEB3"/><type>IPC2571</type></dataSource>

    <operations>
    <manufacturers attachments="false"/>
        <manufacturerParts attachments="false"/>
        <items aml="true" bom="true" sites="false" attachments="false"/>
      </operations>
      <mapping>
        <attachmentRef href="cid:E36C913548344EDA1B7FC20CEDCEBEEF"/>
      </mapping>
      <options>
        <BusinessRuleOptions>
          <ChangeMode value="Authoring"/>
          <MultiRowUpdateMode value="AddUpdateOnly"/>
        </BusinessRuleOptions>
        <ParsingAndValidationOptions>
          <CaseValidationAction value="Convert"/>
        </ParsingAndValidationOptions>
      </options>
    </importDataRequest>

The above XML document is not a complete or valid SOAP request. Rather, this XML document represents the contents of a SOAP request body. Generally, you do not need to generate the above XML document by manually. Instead, the client-side stubs generated by a Web-Service-aware code library will usually create an appropriately formatted XML document and places it within a SOAP request. The above sample is simply an illustration of what the XML request generated by client-side stubs might look like.

Following is the client-side code that is submitted to generate this XML. For additional examples, see "importer.ImportSupplierResponse Usage."

    runner importer.ImportData -p http -h localhost -e web -u admin -p agile -l 8888 -f ExcelFile -i D: \source.xls -a D: \mapFile.xml -t items.aml items.bom manufacturers manufacturerParts -n "BusinessRuleOptions|ChangeMode=Authoring" "BusinessRuleOptions|MultiRowUpdateMode=AddUpdateOnly" "ParsingAndValidationOptions|CaseValidationAction=Convert"

### 4.3.8 Using the validData Web Service Operation

This operation exposes the validation service through a Web service interface that you can invoke programmatically. This operation validates the source data for compliance with server rules that govern length, size, and other formats before importing them into the Agile PLM system. For information on programmatic support, refer to the Agile PLM SDK Developer Guide. For information on the UI implementation, refer to Agile PLM Import/Export User Guide.

The `validateData` operation uses the same `importDataRequestType` used by the `importData` Web service operation. For procedures to specify the importDataRequestType, see "Using the importData Web Service Operation." For more information on the XML schema that describes the importData request, refer to the Import XML Schema.

Note: The `validateDataReqeustType` is a subclass of the importDataRequestType, but it does not define any additional methods. In that way, the two are exactly the same. In future releases, the validateData operation will use validateDataReqeustType instead of importDataRequestType.

### 4.3.9 Importing Supplier Responses

To import supplier responses using the `importDataRequest` Web service operation, specify "`importSupplierResponseRequestType`" for the `xsi:type element`. The importSupplierResponseRequestType is much simpler than `importDataRequestType` because it is much more constrained. You don't need to specify import operations, mapping files, transformation files, or options to import an RFQ response. The importSupplierResponseRequestType XML element allows you to specify three types of data:

- Data Source - This is the source of the data to be imported.

- RFQ Number - This is the alphanumeric identifier of the RFQ that is associated with the response.

- Supplier Number - This is the supplier number is needed only when a buyer imports an RFQ response for an off-line supplier. If the supplier number is not specified, the import server retrieves the supplier number from the specified input file.

For more information on importSupplierResponseRequestType parameters, see the Import XML Schema documentation in "AIS Folders."

The following is a complete sample for `importSupplierResponseRequest`. It demonstrates how a fully configured `importSupplierResponse` operation request can appear.

Example 4-9 A fully configured importSupplierResponse request

    <importDataRequest xsi:type="importSupplierResponseRequestType">   <dataSource>      <attachmentRef href="cid:E36C913548344EDA1B7FC20CEDCEDEB3"/>   </dataSource>     <rfqNumber value="RFQ00123"/></importDataRequest>

The above XML document is not a complete or valid SOAP request. It is a depiction of the XML request that is generated by the client-side stubs.

### 4.3.10 Working with Tables in Import

The Import operation supports Importing Job Function and Functional Team attributes and Functional Teams class tables in aXML, Text (csv), and Excel (xls) formats. The only exception is the PDX format which the Import operation does not support.

Example 4-10 Importing a Job Functional Team class Table - Job Functions

    Source file: imp_jobfunctions.txt with the following contents:Name,Type,Status,JobName,Users/User Groups,AIS_KFUNCTEAM1,Functional Team,Active,Product Manager,kuser1,AIS_KFUNCTEAM2,Functional Team,Active,Lead Developer,kuser2,AIS_KFUNCTEAM3,Functional Team,Active,Developer,kuser3,

Solution

    runner importer.ImportData -h server -l port -e virtualpath -u username -p password -i D:\imp_jobfunctions.txt -f DelimitedTextFile -t usergroups.jobfunction -n "TextParser|FieldDelimiter=," -o D:\result_jf.xml -a D:\mapping_jf.xml

Example 4-11 Importing a Functional Team class Table - Discussion

    Source file: imp_discussion.txt with the following contents:Name,Type,Status,Subject,NumberAIS_KFUNCTEAM1,Functional Team,Active,DISC1,D00001AIS_KFUNCTEAM2,Functional Team,Active,DISC1,D00001AIS_KFUNCTEAM3,Functional Team,Active,DISC1,D00001

Solution

    runner importer.ImportData -h server -l port -e virtualpath -u username -p password -i D:\imp_discussions.txt -f DelimitedTextFile -t usergroups.discussion -n "TextParser|FieldDelimiter=," -o D:\result_disc.xml -a D:\mapping_disc.xm

Example 4-12 Importing a Functional Team class Table - Action Items

    Source file: imp_actionitems.txt with the following contents:Name,Type,Status,Subject,AssignedToAIS_KFUNCTEAM1,Functional Team,Active,ActionItem1,adminAIS_KFUNCTEAM2,Functional Team,Active,ActionItem2,adminAIS_KFUNCTEAM3,Functional Team,Active,ActionItem3,admin

Solution

    runner importer.ImportData -h server -l port -e virtualpath -u username -p password -i D:\imp_actionitems.txt -f DelimitedTextFile -t usergroups.actionitem -n "TextParser|FieldDelimiter=," -o D:\result_action.xml -a D:\mapping_action.xml

## 4.4 Importing Data Values

The Import Web service supports a variety of date formats based on several different criteria, including user preferences and locale.

Note: The upper limit for dates is today's date + 150 years. Date values later than that are invalid and cannot be imported.

### 4.4.1 Setting the Preferred Date Format and Time Zone

Each Agile user can select a preferred date format.

To change date format preferences for your Agile account:

1. In Agile Web Client, select Settings > User Profile > Preferences > Edit.

2. Select the desired date format in the Preferred Date Format field.

3. Select a GMT time zone in the Time Zone field.

4. Click Save.

### 4.4.2 Supported Date Formats

The Import Web service supports all combinations of date and time formats available in the java.text.DateFormat class as well as additional formats. DateFormat provides many date and time formatting styles based on locale. The following table shows date formats available for the U.S. locale, evaluated in order:

Date Format | Example
--- | ---
MMM-dd-yyyy HH:mm:ss | Jul-10-2001 14:08:35
MMM-dd-yyyy HH:mm | Jul-10-2001 14:08
MMM-dd-yyyy hh:mm:ss a | Jul-10-2001 02:08:35 PM
MMM-dd-yyyy hh:mm a | Jul-10-2001 02:08 PM
MMM-dd-yyyy | Jul-10-2001
dd-MMM-yyyy HH:mm:ss | 10-Jul-2001 14:08:35
dd-MMM-yyyy HH:mm | 10-Jul-2001 14:08
dd-MMM-yyyy hh:mm:ss a | 10-Jul-2001 02:08:35 PM
dd-MMM-yyyy hh:mm a | 10-Jul-2001 02:08 PM
dd-MMM-yyyy | 10-Jul-2001
EEEE, MMMM d, yyyy | Thursday, June 25, 1998
EEEE, MMMM d, yyyy h:mm a | Thursday, June 25, 1998 1:32 PM
EEEE, MMMM d, yyyy h:mm:ss a | Thursday, June 25, 1998 1:32:19 PM
EEEE, MMMM d, yyyy h:mm:ss a z | Thursday, June 25, 1998 1:32:19 PM GMT-05:00
MMMM d, yyyy | June 25, 1998
MMMM d, yyyy h:mm a | June 25, 1998 1:32 PM
MMMM d, yyyy h:mm:ss a | June 25, 1998 1:32:19 PM
MMMM d, yyyy h:mm:ss a z | June 25, 1998 1:32:19 PM GMT-05:00
MMM d, yyyy | Jun 25, 1998
MMM d, yyyy h:mm a | Jun 25, 1998 1:32 PM
MMM d, yyyy h:mm:ss a | Jun 25, 1998 1:32:19 PM
MMM d, yyyy h:mm:ss a z | Jun 25, 1998 1:32:19 PM GMT-05:00
M/d/yy | 6/25/98
M/d/yy h:mm a | 6/25/98 1:32 PM
M/d/yy h:mm:ss a | 6/25/98 1:32:19 PM
M/d/yy h:mm:ss a z | 6/25/98 1:32:19 PM GMT-05:00

Each date format is specified using a time pattern string where

y = year M = month in year d = day in month h = hour in AM/PM (1~12) m = minute in hour s = second in minute E = day in week a = AM/PM marker z = time zone ' = escape for text '' = single quote

The count of each letter such as "M" in the time pattern determines the format. For example, three "M" characters indicate that the month is represented as text instead of a number; less than three "M" characters means that the month is represented by a number.

For more information about Java date formats and time pattern syntax, see Oracle documentation for the `SimpleDateFormat` and `DateFormat` classes at: `http://docs.oracle.com/javase/1.5/docs/`

### 4.4.3 Specifying Time Zones

Date values can specify a GMT time zone. If a date value omits the time zone, the user's time zone preference is used. Time zones must be entered in the following format:

GMT Sign hh:mm

where:

GMT = Greenwich Mean Time

Sign = + or -

h = hour in AM/PM (1 to12)

m = minute in hour

For example, "GMT-05:00" and "GMT+02:00" are valid time zones.

Note: Do not use three-character codes (such as PST or PDT) to specify time zones. Three-character time zone codes are unreliable because some are used for multiple time zones. Consequently, the Agile server might resolve a three-character time zone code to an incorrect time zone.

### 4.4.4 aXML and PDX Package Date Formats

For aXML and PDX packages, the Import Web service operation supports a subset of the ISO String date format: yyyy/MM/ddTHH:mm:ssZ

Note: The T and Z characters are optional.

### 4.4.5 Importing XLSX File Formats

The AIS Import function supports importing XLSX or Microsoft 2007/2010 Excel file types by using a command similar to the one shown below:

Example 4-13 A sample command to import XLSX file types using AIS

    runner importer.ImportData -h server -l port -evirtualpath -u username -p password -iD:\Item_BOM_LT_2010.xlsx -f ExcelXLSXFile -t items-n "BusinessRuleOptions|ChangeMode=Authoring""ExcelFileParser|SelectWorksheet=1"