Add-in: Integration Adaptor (Clarity 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.
Use the Integration Adaptor to import structured resource information with a
Clarity PPMjob. The Integration Adaptor is available automatically after a new installation or upgrade.
- To verify that the Integration Adaptor is available, view the list of add-ins.
- ClickAdministration,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
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 PPMthrough 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:
- Create and format a delimited file. You can export a file or can create a custom report from your existing resource or user repository.
- Upload the file to your secure FTP directory.
- Run the Integration Data Loader job.
- 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 withClarity PPMSupport to ensure that you have access to your secure FTP folders.When you can access the secure FTP site, the following folders are available:
- 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:
- Document Manager
- 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 theResource IDfield.ID: unique_name.Data type: String. Size: 32.
- employmentType. Resolves to thePerson Typefield through the lookup SRM_RESOURCE_TYPE.ID: person_type.Values: Employee, Contractor.
- bookingManagerUserName. Resolves to theBooking Managerfield through the associated lookup.ID: book_manager_id.Values: A valid, pre-existing user name.
- resourceType. Resolves to theResource Typefield 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 theDate of Hirefield.ID: date_of_hire.Data Type: Date.
- terminationDate. Resolves to theDate of Terminationfield.ID: date_of_termination.Data Type: Date.
- managerUserName. Resolves to theManagerfield through the lookup LOOKUP_SEC_RESMGR.ID: manager_id.Values: A valid, pre-existing user name.
- isActive. Resolves to theActivefield.ID: is_active.Data Type: Boolean.Values: true, false.
- isExternal. Resolves to theExternalfield.ID: is_external.Data Type: Boolean.Values: true, false.
- includeInDatamart. Resolves to theInclude in Datamartfield.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 theEmail Addressfield.ID: email.Data Type: String. Size: 255.
- firstName. Resolves to theFirst Namefield.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 theOpen for Time Entryfield.ID: prisopen.Values: true, false.
- availability. Resolves to theAvailability Ratefield.ID: availability.Data Type: Float.
- category. Resolves to theCategoryfield.ID: prcategory.Data Type: String. Size: 32.
- primaryRoleId. Resolves to thePrimary Rolefield through the lookup PRIMARY_ROLE_HIER_BROWSE.ID: prprimaryroleid.Value: An ID of a pre-existing role.
- trackMode. Resolves to theTrack Modefield through the lookup prTrackMode. The value defaults to PPM if the field is left blank.ID: prtrackmode.Values: PPM, Other, None.
- inputTypeCode. Resolves to theInput Type Codefield through the lookup LOOKUP_INPUT_TYPES.ID: prtypecodeid.Value: A pre-existing ID of a Type Code.
- userFlag1. Resolves to theUser Flag 1field.ID: pruserflag1.Values: true, false.
- userFlag2. Resolves to theUser Flag 2field.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.
- mobilePhoneData Type: String. Size: 20.
- jobTitleData Type: String. Size: 32.
- stateData Type: String. Size: 40.
- webAddressData Type: String. Size: 80.
- workPhoneData Type: String. Size: 20.
- pagerData Type: String. Size: 20.
- postalCodeData Type: String. Size: 30.
- address1Data Type: String. Size: 80.
- address2Data Type: String. Size: 80.
- address3Data Type: String. Size: 80.
- faxData Type: String. Size: 20.
- cityData Type: String. Size: 40.
- countryValues: A valid country ID.
- homePhoneData 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 attributemyCustomString,include~cust_myCustomStringin 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_myMoneydefines the header row for the money attribute, and~cust_myMoney_currencydefines the header row for the currency attribute.
- To specify a custom partition, include~cust_partition_codeas 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 ofmy_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:
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.
One of the following delimiters must be used to separate items in the file:
- Vertical bar or pipe ( | )
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
myImpressionthat 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
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:
Example: A custom attribute added
To add a custom attribute with the ID
~cust_prefix as shown in the following table:
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:
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:
- Place the delimited file in your secure FTP iad/input folder.
- InClarity PPM, clickHome,Personal,Reports and Jobs. (InClarity PPM14.4, 15.1, and 15.2, clickHome,Personal,Jobs.)
- In theJob Typecolumn, open theIntegration Data Loaderjob.
- Complete the fields in theParameterssection:
- 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.
- (Optional) To save your parameters for reuse, clickSave Parameters.
- Schedule the job by completing the fields in theWhensection.
- Select any groups or individuals that you want notified on job completion or job failure in theNotifysection. Any individuals that you select require access rights to this job and the secure FTP site.
- (Optional) You can monitor the progress of the job. Click the down arrow on theJobstab and selectLogin 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)
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
Before adding a new date format to the lookup, carefully review the
Simple Date Formatdocumentation 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.
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 folderIf 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.logIntegration_Data_Loader_5000024_resourcesOOTBNoDates.csv
- Completed folderIf 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 inClarity 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.logIntegration_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.