Add-in: Integration Adaptor (CA PPM SaaS Only)

Use the Integration Adaptor to import structured resource information with a cappm job. The Integration Adaptor is available automatically after a new installation or upgrade.
ccppmod153
Use the Integration Adaptor to import structured resource information with a 
Clarity PPM
 job. The Integration Adaptor is available automatically after a new installation or upgrade.
  1. To verify that the Integration Adaptor is available, view the list of add-ins.
  2. Click
    Administration
    Studio
    ,
    Content Add-ins
    .
You can add new resources and update information for existing resources. Updating existing resource information updates all attributes fields the delimited file contains. Because all fields in the file are updated in 
Clarity PPM
, you can overwrite existing information. When you update existing resource information, consider limiting the fields in the file to only those fields that you want to update in
Clarity PPM
.
2
 
: Do not use this utility to create new CA PPM resources (users) that you expect to also be able to log in through the CA On Demand portal. Create new users who can log into
Clarity PPM
through the portal. Create them manually, using ODUM, or the WSDL to update the portal.  When you assign the user to one of your development, test, or production SaaS systems, the user is automatically created in
Clarity PPM
. You can use the integration adaptor to update resource (user) information that cannot be brought in through the portal.
Import Resource Data Using the Integration Adaptor
The following steps outline the process to import resource information using the Integration Adaptor:
  1. Create and format a delimited file. You can export a file or can create a custom report from your existing resource or user repository.
  2. Upload the file to your secure FTP directory.
  3. Run the Integration Data Loader job.
  4. To verify that your data was updated, view the error messages and logs.
Integration Adaptor Prerequisites
Complete these tasks to use the Integration Adaptor:
  • Work with
    Clarity PPM
     Support to ensure that you have access to your secure FTP folders.
    When you can access the secure FTP site, the following folders are available:
    • iad
      • completed
      • error
      • input
      • processing
  • Assign the following access rights to the administrator who runs the Integration Data Loader job:
    • Jobs - Access
      . Allows you to access to jobs pages. The instance access right to run the Integration Data Loader job is required in addition to this right.
      Type:
      Global
    • Job - Run - Integration Data Loader Job
      . Allows you to run the Integration Data Loader job.
      Type
      : Instance
    • Resource - XOG Access
      . Allows you to import and export resource information using the XML Open Gateway (XOG) interface. The Integration Data Loader job uses the XOG interface which verifies that the user running the job has the appropriate resource access rights.
      Type
      : Global
Integration Adaptor Import Constraints
You can use the Integration Adaptor to import the following resource information for new and existing resources:
  • Primary resource attributes, including user name, resource ID, employment type, and other basic setup information
  • Management information such as availability and primary role
  • Contact information
  • Custom resource attributes
  • OBS information for OBS structures that are available for the resource
You cannot import the following resource information:
  • Skills
  • Allocations
  • Document Manager
  • Calendar
  • Custom subobjects defined on the Resource object
  • Multivalued lookups
  • Time-sliced values
  • Attachment data types
Integration Adaptor Primary Resource Attributes
The following attributes that provide primary information about a resource can be imported using the Integration Adaptor:
  • username
    . Resolves to the username as seen on the resource administration page.
    Data type: String. Size: 32.
  • resourceId
    . Resolves to the 
    Resource ID
     field.
    ID: unique_name.
    Data type: String. Size: 32.
  • employmentType
    . Resolves to the 
    Person Type
     field through the lookup SRM_RESOURCE_TYPE.
    ID: person_type.
    Values: Employee, Contractor.
  • bookingManagerUserName
    . Resolves to the 
    Booking Manager
     field through the associated lookup.
    ID: book_manager_id.
    Values: A valid, pre-existing user name.
  • resourceType
    . Resolves to the 
    Resource Type
     field through the lookup RESOURCE_TYPE.
    ID: resource_type.
    Values: Labor, Material, Equipment, Expense. The value defaults to Labor if the field is included but the value is left blank.
  • hireDate
    . Resolves to the 
    Date of Hire
     field.
    ID: date_of_hire.
    Data Type: Date.
  • terminationDate
    . Resolves to the 
    Date of Termination
     field.
    ID: date_of_termination.
    Data Type: Date.
  • managerUserName
    . Resolves to the 
    Manager 
    field through the lookup LOOKUP_SEC_RESMGR.
    ID: manager_id.
    Values: A valid, pre-existing user name.
  • isActive
    . Resolves to the 
    Active 
    field.
    ID: is_active.
    Data Type: Boolean.
    Values: true, false.
  • isExternal
    . Resolves to the 
    External 
    field.
    ID: is_external.
    Data Type: Boolean.
    Values: true, false.
  • includeInDatamart
    . Resolves to the 
    Include in Datamart
     field. 
    ID: include_flag.
    Values: true, false.
  • displayName
    . Resolves to the name fields for nonlabor resources. This field cannot be edited in the user interface for labor resources. 
    ID: full_name.
    If you are importing nonlabor resources, this field must be included with a value; the import fails otherwise.
    Data Type: String. Size: 100.
  • emailAddress
    . Resolves to the 
    Email Address
     field.
    ID: email.
    Data Type: String. Size: 255.
  • firstName
    . Resolves to the 
    First Name
     field.
    ID: first_name.
    Data Type: String. Size: 32.
  • lastName
    . Resolves to the Last Name field.
    ID: last_name.
    Data Type: String. Size: 32.
Integration Adaptor Resource Management Attributes
The following fields that provide resource management information about a resource can be imported using the Integration Adaptor:
  • openForTimeEntry
    . Resolves to the 
    Open for Time Entry
     field.
    ID: prisopen.
    Values: true, false.
  • availability
    . Resolves to the 
    Availability Rate
     field.
    ID: availability.
    Data Type: Float.
  • category
    . Resolves to the 
    Category 
    field.
    ID: prcategory.
    Data Type: String. Size: 32.
  • primaryRoleId
    . Resolves to the 
    Primary Role
     field through the lookup PRIMARY_ROLE_HIER_BROWSE.
    ID: prprimaryroleid.
    Value: An ID of a pre-existing role.
  • trackMode
    . Resolves to the 
    Track Mode
     field through the lookup prTrackMode. The value defaults to PPM if the field is left blank.
    ID: prtrackmode.
    Values: PPM, Other, None.
  • inputTypeCode
    . Resolves to the 
    Input Type Code
     field through the lookup LOOKUP_INPUT_TYPES.
    ID: prtypecodeid.
    Value: A pre-existing ID of a Type Code.
  • userFlag1
    . Resolves to the 
    User Flag 1
     field.
    ID: pruserflag1.
    Values: true, false.
  • userFlag2
    . Resolves to the 
    User Flag 2
     field.
    ID: pruserflag2.
    Values: true, false.
Integration Adaptor Resource Contact Attributes
The following fields that provide contact information about a resource can be imported using the Integration Adaptor. All the following fields resolve to the contact fields on the Resource record.
  • mobilePhone
    Data Type: String. Size: 20.
  • jobTitle
    Data Type: String. Size: 32.
  • state
    Data Type: String. Size: 40.
  • webAddress
    Data Type: String. Size: 80.
  • workPhone
    Data Type: String. Size: 20.
  • pager
    Data Type: String. Size: 20.
  • postalCode
    Data Type: String. Size: 30.
  • address1
    Data Type: String. Size: 80.
  • address2
    Data Type: String. Size: 80.
  • address3
    Data Type: String. Size: 80.
  • fax
    Data Type: String. Size: 20.
  • city
    Data Type: String. Size: 40.
  • country
    Values: A valid country ID.
  • homePhone
    Data Type: String. Size: 20.
Integration Adaptor Resource Custom Attributes
You can import custom attribute information except for multivalued look-ups, time-scaled values, and attachment data types.
  • To import a custom attribute value
    , add the following prefix to the attribute ID: 
    ~cust_
    For example, to import data for the resource object attribute 
    myCustomString, 
    include 
    ~cust_myCustomString
     in the header row for that column.
  • To associate a currency type with a custom money attribute
    , include the currency attribute in addition to your money attribute. 
    For example, 
    ~cust_myMoney
     defines the header row for the money attribute, and 
    ~cust_myMoney_currency
     defines the header row for the currency attribute.
  • To specify a custom partition
    , include 
    ~cust_partition_code
     as the header attribute.
Integration Adaptor Resource OBS Associations
Organization Breakdown Structure (OBS) associations are supported for any OBS that is available to the resource object.
  • To associate a resource instance to an OBS
    , add the following prefix to the ID of the target OBS: 
    ~OBSAssoc_
    For example, if your resource OBS has an ID of 
    my_resource_obs
    , the header row attribute is 
    ~OBSAssoc_my_resource_obs
    .
  • To create the value for an association
    , use forward slashes (/) to start and separate the units. The value for the association is the full path of the target unit using the name values of the OBS units. 
    For example: /Top Unit Name/Next Level Unit Name/Target Unit Name.
Integration Adaptor Formatting Requirements
Required Header Row Attributes
The following attributes are required for any import and must appear in the header row:
  • resourceId
  • firstName
  • lastName
  • emailAddress
The header row contains the attributes that define the columns for the data being imported. The attributes that you enter on the header row are case-sensitive. The attributes must be entered exactly as they appear in this documentation.
For example, to import the resource ID, First Name, Last Name, and email address (Primary Resource attributes), create the header row as follows:
  resourceID, firstName, lastName, emailAddress
The following table shows the header row of attributes and a row of data with a value for each attribute.
resourceID
firstName
lastName
emailAddress
resource0001
Gwendolyn
Fitzpatrik
Required Delimiters
One of the following delimiters must be used to separate items in the file:
  • Tab
  • Comma
  • Semi-colon
  • Colon
  • Vertical bar or pipe ( | )
  • Space
The following example shows a file that is delimited using vertical bars (pipes).
resourceId|firstName|lastName|emailAddress|~OBSAssoc_res.obs.id resource0001|Gwendolyn|Fitzpatrik|[email protected]|/IT/Development/Dublin
If a field value contains the chosen delimiter, enclose the field in quote symbols to avoid errors. Either double quotes or single quotes can be used.
The following comma-delimited example has a custom string attribute that is named 
myImpression
 that has a field value with a comma. Because the delimiter is a comma, having a comma in the field value introduces errors. Double quotes are placed around the field value. The field value is then read as a single value rather than two values separated by a comma.
resourceId,firstName,lastName,emailAddress,~cust_myImpression resource0001,Gwendolyn,Fitzpatrik,[email protected],"Overall, I had a great time"
You can add new values to the delimiters and text quotes in two lookups. The name of the delimiter lookup is 
Integration Data Loader Delimiters
. The name of the text quote symbol lookup is 
Integration Data Loader Quote Symbols
. For information about how to manage lookups, see 
Administrating
.
Extended Characters
If your data has multi-byte data or non-ANSI characters, ensure that you are using an editor that can interpret these characters and can save your file using UTF-8 encoding.
For example, note the accent characters in the following name fields:
resourceId,firstName,lastName,emailAddress resource0003,Amérigo,Cástor,[email protected]
If the file is not saved in UTF-8 format, the extended characters are not preserved. The result is that the data is imported with incorrect formatting. For example, the first and last names would be imported incorrectly as shown here:
resourceId,firstName,lastName,emailAddress resource0003,Amérigo,Cástor,[email protected]
Integration Adaptor Import Examples
The following examples show the different types of information you can include and the format. The first row in each example is a header row.
Example: An extra attribute added to the required set
To set the availability of the resource, include the availability attribute as shown in the following table:
resourceID
firstName
lastName
emailAddress
availability
resource0001
Gwendolyn
Fitzpatrik
7.2
Example: A custom attribute added
To add a custom attribute with the ID 
resourceExtension, 
add the 
~cust_
 prefix as shown in the following table:
resourceID
firstName
lastName
emailAddress
~cust_resourceExtension
resource0001
Gwendolyn
Fitzpatrik
x3022
Example: An OBS association added
To add an OBS association with the ID 
res.obs.id
, add the 
~OBSAssoc_
 prefix as shown in the following table:
resourceID
firstName
lastName
emailAddress
~OBSAssoc_res.obs.id
resource0001
Gwendolyn
Fitzpatrik
/IT/Development/Dublin
The examples show delimited files as they may appear in Microsoft Excel. However, the files are stored in a delimited format. The following example shows how the file might appear when viewed with a text editor:
resourceId,firstName,lastName,emailAddress,~OBSAssoc_res.obs.it resource0001,Gwendolyn,Fitzpatrik,[email protected],/IT/Development/Dublin
Load the File and Run the Job
When the information in the delimited file is complete, you are ready to run the Integration Data Loader job to update resources.
Note
: Review the Integration Adaptor Prerequisites. You are only able to see and select the job if you have the appropriate access rights.
You cannot run this job while the Time Slice job is running. A large import file creates and updates so many records that system performance can be affected. We recommend running the Integration Data Loader job in off hours.
Follow these steps:
  1. Place the delimited file in your secure FTP iad/input folder.
  2. In 
    Clarity PPM
    , click
    Home
    ,
    Personal
    ,
    Reports and Jobs
    . (In
    Clarity PPM
     14.4, 15.1, and 15.2, click
    Home
    ,
    Personal
    ,
    Jobs
    .)
  3. In the
    Job Type
    column, open the
    Integration Data Loader
    job.
  4. Complete the fields in the
    Parameters
    section:
    • File Name
      . Specifies the name of the delimited file to be imported. Type the file name exactly as it is stored in the input folder, including the file extension. 
      For example: resourcesInput.csv
    • Delimiter
      . Specifies the character to separate data in the file.
    • Text Quote Symbol
      . Specifies the character to escape instances where a delimiter appears in text.
    • Date Format
      . Specifies the date format to be used for date attributes.
    • Resources
      . Represents the object data (resources) that is being imported. The field is selected and read-only.
  5. (Optional) To save your parameters for reuse, click
    Save Parameters.
  6. Schedule the job by completing the fields in the
    When
    section.
  7. Select any groups or individuals that you want notified on job completion or job failure in the
    Notify
    section. Any individuals that you select require access rights to this job and the secure FTP site.
  8. Click
    Submit
    .
  9. (Optional) You can monitor the progress of the job. Click the down arrow on the
    Jobs
    tab and select
    Log
    in the resulting menu. To see information about the number of records that are processed, warnings, and errors, click the job name in the list.
Integration Adaptor Job Date Formats
The following date formats are available for selection from the Date Format field when you run the job. The format determines how the job processes date attributes.
  • yyyy-MM-dd (default)
  • dd-MMM-yyyy
  • dd/MM/yyyy
  • MM-dd-yyyy
  • MM/dd/yyyy
If your delimited file has a different date format, you can add a format to the Date Format lookup. The name of the lookup is 
Integration Data Loader Date Formats
. For information about how to manage lookups, see 
Administrating
.
Before adding a new date format to the lookup, carefully review the
Simple Date Format
documentation available from Oracle. The format characters are case-sensitive so you must be exact. For example, M = month, but m = minute.
Verify the Uploaded Data
When the Integration Data Loader job completes, review the logs to ensure that your data loaded correctly. The following image describes how the Integration Adaptor stores the input file after processing.
Image describing how the Integration Adaptor stores the input file after processing
Image describing how the Integration Adaptor stores the input file after processing
 
As the job runs, the input file is moved from the Input folder into the Processing folder. When the job finishes, the file is moved to one of the following folders:
  • Error folder 
    If there are errors, the processing was not successful. In this case, the input file is renamed and prefaced with the job name and run ID. The file is copied to the Error folder. The error log is also placed in the Error folder. You can edit the file to correct any problems, place the corrected file back into the Input folder, and rerun the job.
    Here are some examples of file names in the Error folder:
    Integration_Data_Loader_5000024_error.log
    Integration_Data_Loader_5000024_resourcesOOTBNoDates.csv
  • Completed folder
    If the processing was successful, a completed job log and the input file are stored in the Completed folder. Examine the log file. If there are warnings in the job log, some data can require correction in 
    Clarity PPM
    . A warning can also indicate data that was not imported due to a failure; however, the failure was not enough to stop the import. You can correct any data issues and can rerun the job.
    Here are some examples of file names in the Completed folder:
      Integration_Data_Loader_5003730.log
      Integration_Data_Loader_5003730_resourcesInsert1.csv
As a precaution, save and store any files from the Error and Completed folders that you want to keep indefinitely. Data in these folders is retained based on an on-demand schedule for secure FTP folder data.