PeopleTools 8.51 PeopleBook: PeopleSoft Component Interfaces

Using the Excel-to-Component Interface Utility

This chapter provides an overview of the Excel-to-Component Interface utility and discusses how to:

Run the Excel to Component Interface utility.
Set up connection information.
Create templates.
Enter data on the data input sheet.
View staged data.
Create SOAP/XML requests.
Send requests.
Receive responses.
Diagnose and resolve errors.
Add new languages.

Understanding the Excel-to-Component Interface Utility

Use the Excel to Component Interface utility and component interfaces to upload data from Microsoft Excel into PeopleSoft databases. Each source workbook contains both worksheets and Excel Visual Basic code modules that execute business logic for each transaction.

Use the Microsoft Excel workbooks as a template to create worksheets that are specific to the business logic that you need to use when you are uploading data to the PeopleSoft system. You can copy the data input sheet to other workbooks for distribution without copying the code modules.

The code formats spreadsheet data into a PeopleSoft readable Document Object Model (DOM) structure, and submits it to the PeopleSoft database. Next a PeopleCode program parses the DOM structure and uses the component interface to create entries in the PeopleSoft database, validating the data submitted against the business logic that is built into the PeopleSoft component. Because the component interface is a wrapper around the component, all logic applied during data entry is applied when you are loading data through this tool.

The component interface executes all the necessary PeopleCode events and the field-level edits. Based upon results from saving the component interface, another DOM is created in the PeopleCode that returns success, warnings, errors, or a combination of the three to the Microsoft Excel document. Records in error can be corrected and resubmitted.

Prerequisites for Using the Excel to CI Utility

To use the Excel to CI utility you must have the following software installed.

Check the My Oracle Support web site for the currently certified versions of software supported.

Microsoft Excel.
Microsoft Visual Basic 6.0 SP5: Run-Time Redistribution Pack.

You can download this software from the Microsoft website.

See http://www.microsoft.com/downloads/Search.aspx?displaylang=en
Microsoft Core XML Services (MSXML) 6.0 or higher.

You can download this software from the Microsoft website.

See http://www.microsoft.com/downloads/Search.aspx?displaylang=en

Understanding Building Component Interfaces for the Excel to Component Interface Utility

To use the Excel to Component Interface utility effectively, you must have a complete understanding of the component that you are using and the component interface that is built around it. In addition, you should know what data needs to be entered and which fields on the component need to be exposed as component interface properties. Fields that are not relevant for data input should not be exposed on the component interface. This reduces processing time when you are loading data, as well as saving time when you are building the template because no need to delete unnecessary properties on the template will exist.

Some component interface structures will need to be modified before they can be used to load data through the utility. Components that have logic to insert multiple rows in child collections, and then require more values to be set on those collections, will need modification to the component to work with the Excel to Component Interface utility. Change the component so that the logic to insert and partially populate these rows does not happen by default through the component interface.

%CompIntfc and %CompIntfcName can be used so that this logic does not fire either from any component interface or from the component interface that you created for use with the Excel to Component Interface utility.

Additionally, components that have no keys at level 0, but rely on logic at level 0 to load the level 1 collection, cannot be loaded by using the Excel to Component Interface utility.

Component interfaces that rely on CommitWork to save the data cannot be used in the Excel to Component Interface utility.

Prompt and translate table values are validated when data is saved and submitted to the database through the Excel to Component Interface utility. This is different from the behavior on the page when prompts and translates are validated interactively. Some components may use prompts that are dynamically populated. For those situations, you must know what the valid values for the prompt will be.

Note. Remember that any changes made to the structure of a component interface will also need to be reflected in the template. Always ensure that the component interface and the template in the Excel to Component Interface utility are in sync. Structural changes made in only the component interface will cause an error in the Excel to Component Interface utility when data is submitted to the database.

Testing Component Interfaces

Before using the Excel to Component Interface utility run the component interface through the component interface tester in three-tier mode. Testing the component interface enables you to troubleshoot any problems before running the component interface through the utility. If the component interface does not work in the tester, it will not work in the Excel to Component Interface utility either. The component interface tester is located on the Tools menu in PeopleSoft Application Designer.

See Testing Component Interfaces.

Performance Expectations

The performance of a component interface depends entirely upon the underlying component. If the component has a complex user interface with many pages and scrolls, the component interface generally will have a slower processing time. The best performance times are found with small and medium-complexity component interfaces.

PeopleCode Behavior and Limitations

Certain PeopleCode functions and events that are specific to the user interface do not execute through the component interface. You will need to modify PeopleCode for the component, pages, and records when you build the component interface for the component.

PeopleCode events and functions that relate exclusively to the page interface and online processing cannot be used by component interfaces. These include:

Search dialog processing.
Menu PeopleCode and pop-up menus.
Transfers between components, including modal transfers.
Dynamic tree controls.
ActiveX controls.
DoSave and DoSaveNow.
Functions that are ignored in a component interface call.

See Understanding PeopleCode Behavior and Limitations, Understanding Component Interface Class.

Default Properties

When you create a new component interface in PeopleSoft Application Designer, the system can create default properties for all the fields exposed on the component interface that meet certain criteria.

When you are creating a new component interface, the following requirements must be met to qualify as a default property.

The fields should be of the following types:

Character
Long character
Number
Signed number
Date
Time
Datetime

The field should be one of the following page control types and must be exposed on the page:

Edit box
Drop-down list box
Check box
Radio button

See Creating New Component Interfaces.

Running the Excel to Component Interface Utility

This section discusses how to:

Grant access to the WEBLIB_SOAPTOCI iScript.
Enable the Developer menu in Microsoft Excel 2007.
Enable macros in Microsoft Excel.
Start the Excel to Component Interface utility.
Converting Excel to Component Interface utility templates to the current Excel version.
View the Excel to Component Interface utility coversheet.

Granting Access to the WEBLIB_SOAPTOCI iScript

To use the Excel to Component Interface utility, you must grant access to the iScript WEBLIB_SOAPTOCI in the permission list of the user who is building the template.

See Setting Up Permission Lists.

Enabling the Developer Menu in Microsoft Excel 2007

The Developer menu in Microsoft Excel contains options to work with Microsoft Visual Basic, macros, sheet properties, and so on.

In Microsoft Excel 2007 the Developer menu is not automatically enabled and does not appear on the menu ribbon in the default view of the Excel workspace. In the other versions of Microsoft Excel supported for use with the Excel to Component Interface utility, the Developer menu appears by default.

The following example shows the menu ribbon that appears in the default Microsoft Excel 2007 workspace view:

To use the Excel to Component Interface utility, you need access to some of the features accessed through via the Developer menu, and therefore you must enable the menu. The following example shows Microsoft Excel 2007 workspace with the Developer menu enabled on the menu ribbon:

Once enabled, the Developer menu appears on the far right on the menu ribbon.

To enable the Developer menu in Microsoft Excel 2007:

Launch Microsoft Excel 2007.
In the upper left corner of the workspace, click the circular Microsoft Office icon.

The Recent Documents menu appears.
Click the Excel Options button at the bottom of the menu.

The Excel Options page appears.
In the Top Options For Working with Excel section, check the Show Developer Tab in the Ribbon option.
Click the OK button.

The Microsoft Excel 2007 workspace appears and the Developer menu appears on the menu ribbon.

Enabling Macros in Microsoft Excel

The Excel to Component Interface utility relies on macros; therefore, you must enable macros in Microsoft Excel for the utility to work. When a Microsoft Excel spreadsheet is opened, the system displays a dialog box asking you to select whether to enable macros on the spreadsheet. Always select Enable Macros so that the macros delivered with the Excel to Component Interface utility can function.

To ensure that the macros are available to run, you must set the security level in Microsoft Excel to allow macros to open.

To enable macros in Microsoft Excel:

Open the Excel to Component Interface utility.
From the Excel menu, select Tools, Macros, Security.
Select either Medium or Low to enable the macros.
Select OK.

Starting the Excel to Component Interface Utility

The Excel to CI utility spreadsheet is located in the PS_HOME/excel directory. The file name is ExcelToCI2007.xlsm.

Converting Excel to Component Interface Utility Templates to the Current Excel Version

You can use customized Excel-to-CI templates based on versions of Microsoft Excel released previous to Excel 2007. To preserve the macros embedded in your customized Excel-to-CI templates, you must convert the templates to Excel 2007 format.

Excel 2007 files have the extension .xlsm.

To convert an Excel-to-CI template to Excel 2007 format:

Open a template in Excel 2007.
Click the Microsoft Office Button and choose Save As.
Click Excel Macro-enabled Workbook.

A Save As dialog box appears.
Choose a save location and enter a name for the workbook.
The workbook name must have the extension xlsm, such as myworkbook.xlsm
Click the Save button.

Viewing the Excel to Component Interface Coversheet

The coversheet of the Excel to Component Interface utility workbook gives a brief overview of the process flow and functionality of the tool.

Access the Coversheet tab in ExcelToCI2007.xlsm:

Setting Up Connection Information

This section discusses how to:

Enter connection information.
Connect to the database to create a template and submit data.

Entering Connection Information

Access the Connect Information tab in ExcelToCI2007.xlsm by clicking the Connection Information tab:

The information on this page is required to create a new template or to submit data to the database. You will need to specify environment information as well as information about how data should be transmitted. The Action field will be populated automatically based on your setup and the component interface that the template is associated with.

The initial connection settings will be the PeopleSoft default values. You will need to modify these values for your specific implementation of PeopleSoft. If you are unsure what to enter for these values, check with your system administrator.

The connection options are:

Web Server Machine Name	The name of the PeopleSoft web server to which you are connecting.
Protocol	The protocol used to access the web server. The default is HTTP. The preferred protocol is HTTPS.
HTTP Port	The HTTP port number that the web server uses. The default is 80.
Portal	The name of the portal you are using. EMPLOYEE is a default portal shipped with PeopleSoft.
PeopleSoft Site Name	The PeopleSoft site name that you entered when you installed the PeopleSoft Internet Architecture. The default is ps.
Node	The PeopleSoft default local node name. The default is PT_LOCAL.
Language Code	The code for the language in which the data is submitted to the database. If no language code is specified, the base language is used. See Translations and Multilingual Support.
Chunking Factor	The number of rows of data to be transmitted to the database at one time. The default is 40.
Error Threshold	The total number of errors that are permitted before submission to the database ceases. When the error threshold is exceeded, an error message appears and submission to the database stops.
Submit Blanks as Input	When this option is set to Yes and a character input field selected for input contains only blank spaces, the field will be included for submission instead of being ignored. This option is set to No by default, for backwards compatibility. If full-width blank space Unicode characters are entered as an input value in ExcelToCI, (this is achieved by using an encoding that supports such Unicode characters) the field will be submitted, the blanks will be sent, and the value will not be trimmed before it is saved to the database. If regular ASCII blank spaces (also known as half-width characters) are entered as a value for a character field, the field will be submitted, but the value will be trimmed, so an empty string will be saved. In essence, the field value will be cleared.
Action	The value for this field is supplied by the system when the component interface is retrieved from the database. However, you can change the supplied value by selecting it from the Action drop-down list. The types of actions available are based on the structure of the component interface. The actions are: Create. This option is available if the component interface has create keys. Use this mode when new keys are being added at level 0. Update. This option is available if the component interface does not have create keys. Use this mode if you are adding new children to an existing parent. UpdateData. Use this option to update specific non-key values that already exist. The system uses the keys to locate the row, and when a match is found, the row is updated with new data. If a key match is not found by the system, it displays an error message indicating which collection was missing a key match. When using the UpdateData action, you must provide all keys for the collection for the system to modify the data. Note. If you want to insert an effective-dated collection at Level 1 containing a child collection at Level 2, you may need to use UPDATE to insert the parent row at level 1 and then use UPDATEDATA to insert the child row at level 2. This is because child rows are copied forward from the current effective-dated collection as a result of the insertion of a new effective-dated parent row. These child rows will be updated by the component processor with the new effective date, and may have the same level 2 keys as the Level 2 child row that you are trying to submit from ExcelToCI.

See Understanding PeopleSoft Pure Internet Architecture, Understanding PeopleSoft Security.

Error Thresholds and Chunking

A running error count is kept for each chunk of data that is being submitted to the database. When the total error count exceeds the error threshold that you specified on the Connection Information tab, submission to the database stops and the system displays an error message. Rows that errored out will have a status of Error on the data input page and should be corrected. The data submitted to the database before the error threshold was reached will remain in the target database. Rows not yet submitted will be submitted when the data is restaged and submitted.

Translations and Multilingual Support

You can use the Excel to Component Interface utility to upload data from any installed language. The Excel to CI utility delivers separate Excel macros for each delivered language. The macros contain the translated strings used as labels on the main spreadsheet. The macros are located in the appropriate language directory found in the PS_HOME/EXCEL directory.

Enabling Non-English Languages

To enable a non-English language in the Excel to CI Utility:

Change the language code on the spreadsheet Connect Information tab to the language to the language you want to use.
Change the reference to the related language macro to be used, as the default macro contains English language strings.

To change the related language macro, in Excel select the Tools, Macro and right-click on the Visual Basic Editor option. Once in Visual Basic, select Tools, References, and click on the RelLangMcro entry. Change the file to be used to the one with the same name but located inside the translated language directory of your choice. Click OK and then save the change.
If you are using a language in which a different character set or numeric formatting is used, you need to set the locale of your client machine to match that language. To do so, open Control Panel, Regional settings, and select the correct language and input locale.

Connecting to the Database to Create a Template and Submit Data

Your PeopleSoft login information is needed for both creating the template and submitting data to the database.

Access the Login dialog box by selecting the Template tab and then clicking the New Template button, or by clicking the Submit Data button on the Staging and Submission tab:

The system uses your user ID and password to ensure that you have the correct permissions to access the component interface that you are creating the template on. You must be granted permission to access the component interface that you are using.

User ID/Password

Enter your PeopleSoft user ID and password.

Component Interface Name

Enter the name of the component interface for which you want the template created.

Generate Log

Select the Generate Log check box to create one log file for ExcelToCI2007.xlsm and one for the SOAPTOCI Web Library.

Note. Unless you are troubleshooting errors, you should run the Excel to Component Interface utility without creating log files. Logs should be generated for debugging purposes only.

See Viewing Log Files.

Creating Templates

The template page is a graphical representation of the component interface structure that you will be using to load data. The structure of the component interface is retrieved from the database when a new template is built. All of the fields that are exposed through the component interface appear on the template page. Fields that are read-only on the component interface will not appear on the template.

The new template macro builds the parent-child relationship within Microsoft Excel based upon the component interface scroll-level definition. The system adds a new row for each scroll level and assigns a unique identifier to it.

Access the Template tab in ExcelToCI.xlsm to create your template:

Collection

The name of the component interface collection. A collection is a property that points to a scroll, rather than a field, in the underlying component for a component interface.

Property

The component interface property name. Typically, this is also the name of the field on the page.

Record Type

This number represents the parent/child relationship of the records. The level 0 scroll record is always represented by 000. Level 1 scroll records appear with numbers that start with 100 and always have 00 as the last two numbers.

Level 2 scrolls are identified by numbers that start with the identifier of their level 1 parent and end with a 0.

Level 3 scrolls are identified by the first number from the level 1 parent, the second number from its level 2 parent, and then the third number from its own position in the list.

The numbers for each scroll level are incremented based on the number of records that exist at that level. For example, level 0 would be 000, level 1 would be 100, level 2 would be 110, and so on.

Note. Component interfaces that have more than 10 collections at a given level will be incremented with alphabetic identifiers. For example, 800, 900, A00, and so on.

Field Type

The standard PeopleSoft type for the field, for example, Date, Character, and so on.

Field Length

The length of the field as defined by PeopleSoft. For numeric fields and signed number fields, the length is broken down into integer and decimal positions. For example, a length of 15.3 indicates 15 integer positions and three decimal positions.

Key/Required

If the field is a key field, the system will display a Y to the left of the forward slash. When the field is not a key, it will be blank. If the field is a required field, the system will display an R to the right of the forward slash. When the field is not required, it will be blank. This information comes from the record definition itself.

Note. Fields that are either keys or required must be set in order to submit data successfully.

Sequence

The sequence number represents the property order in the template.

Status

This field displays the load status on the Staging and Submission page.

Line No line number

This corresponds to the line number on the Input Data and the Staging and Submission pages.

Understanding the Template Actions Toolbar

The template actions toolbar is made up of buttons that you use to create and modify a template, as well as create a data input sheet. You can resize the toolbar and move it to any location on the page or even drag it onto the existing standard Microsoft Excel toolbar. When you have moved the toolbar to a location, it will remain there until you move it again. You do not need to move the toolbar each time you open the workbook.

Each button on the toolbar has help text that describes the purpose and use of each of the buttons when you place the cursor over the button.

New Template	Builds a new template based upon a component interface. The New Template macro builds the parent-child relationship within Microsoft Excel based upon the component interface structure. When you build a new template, the system prompts you for your sign in information.
New Data Input	Builds a new data input sheet based upon the selected input cells. When you build a new data input sheet, the system prompts you as to whether you want to overwrite the existing sheet. If you select Yes, a new data input sheet is created, overwriting the former one.
Select Input Cell	Selects an individual cell to be included in the data input sheet. Cells that have been selected as input cells are highlighted in pink.
Select All Input Cells	Selects all properties to be included in the data input as input cells. When a cell is selected as an input cell, it is highlighted in pink.
Restore Input Cells	Restores the template to its original state and clears default values. The fields in the template will be highlighted in gray, indicating that nothing is included for submission.
Insert New Child	Copies the selected row to be inserted as a new child. This creates multiple occurrences of the same record type. For example, if the selected row has a template identifier of 100, a new row is inserted that also has an identifier of 100 and is an exact duplicate of the selected row. Note. Use Insert New Child when multiple children must be submitted under the same parent record. Multiple children should not be created at identifier 000.
Include All for Submission	Includes all properties on the spreadsheet to be included for submission to the database. Cells that are included for submission appear only on the Staging and Submission sheet and do not appear on the data input sheet. Properties that are included for submission are highlighted in blue.
Include for Submission	Includes a single property to be included on the Staging and Submission sheet. Properties that use default values from the template must be included for submission. Cells that are included for submission generally are properties that contain default values or properties that you would like to see in the structure of the Staging and Submission sheet. Properties that are included for submission are highlighted in blue.
Deselect Input Cell	Changes a cell that was previously selected as an input cell to a cell that is included for submission. The cell is no longer included on the data input sheet but appears as part of the structure on the Staging and Submission sheet.
Clear Template	Clears all the data and structures on this sheet.
Do Not Include for Submission	Does not include the selected property for submission to the database. If a property is not included for submission, it will not appear in the structure that is submitted to the database on the Staging and Submission sheet. Properties that are not included for submission will appear only on the template worksheet and are not submitted to the database. Properties that are not included for submission are highlighted in gray.

Note. When you create a new template or a new data input sheet, the system clears the existing worksheet of all existing information. If you have a template or data input sheet that you need to save from previous uploads, save a copy of the worksheet before you create a new template or data input sheet.

Entering Data into the Template

When determining which properties to include as input cells and which properties to include for submission, remember that the component interface uses the same business logic and executes the same PeopleCode as if the record were entered online using the page in your PeopleSoft application. To provide the minimal data necessary, these fields must be provided either with default (hard-coded) values or values that you provide using the data input sheet.

Note. You should unit test the template that you created with a few sample entries, and then verify your results before using the interface for mass input. For example, if you forgot to select a property, you will need to build a new data input sheet. If the results of the submission are satisfactory, continue entering data.

Adding a New Child Record

By default, each collection is represented once on the template. To insert copies of a given collection, select that collection and click the Insert New Child button to create a copy of the selected row. The collection that you selected is copied so that you can have two rows under the same parent.

Note. On the data input sheet (when the hierarchy is flattened) you will see duplicate columns where multiple children exist.

Adding Default Values

Some fields have default values associated with them, either in the record definition or at runtime when the record is created on the database. Additionally, many components trigger PeopleCode, which supplies default values, as well. To accept the database default, include the property for submission and the system default will be used.

Some fields may exist for which you want to create your own default. For example, if you want to set the value of a field named Status as of Effective Date to A for every row that you submit, enter that value for the field in the template. Then include the cell for submission on the template. The field will not appear on the data input page, but the value will appear in the field on the Submit to Database page. This is useful for effective dates, status fields, set IDs for simple imports, and so on.

When providing values for translate fields or prompt tables, provide the field value rather than the short or long description for the translate value. If you are unsure of the field values, check in the record or field definition in PeopleSoft Application Designer.

Entering Data on the Data Input Sheet

The data input sheet enables you to enter data into the Excel to Component Interface utility so that it can be loaded to the database by using the component interface that you've selected. You can enter data manually or you can cut and paste it from another spreadsheet or third-party application.

Using the Data Input Sheet

Access the data input tab to enter data:

The field labels that appear on the data input sheet are those properties that you selected as input cells on the template. Each scroll level is identified by color. The record type from the template is also displayed for each property.

The system creates default date, datetime, and number formats when it creates the template. You can modify this format by using default cell formatting of Microsoft Excel when entering data, with the exception of the d/m/yy format for dates and datetimes. Instead, always use a d/m/yyyy format for dates and datetimes. To access the formatting feature, select Format, Cells from the Excel menu.

The data input sheet is also used to correct data that failed to submit to the database. Errors that are flagged on the Submit to Database page are posted to the data input page, and when you have corrected them, the items marked in error can be staged again to the Staging and Submission sheet.

Data Input Actions

The data input Actions toolbar contains the Stage Data for Submission button, which takes the data that you entered on the data input sheet and stages it for submission to the database. When the data is staged, it appears on the Staging and Submission sheet in the hierarchical template structure. At this point, you should check that all fields are populated as expected. When the data is staged, it displays both the data on the data input sheet and the data that you specified as default values.

Viewing the Staged Data

Access the Staging and Submission tab:

Staging and Submission Actions Toolbar

Post Results

The results of the submission are copied to the data input sheet, where you can view the status of each row that is submitted and make any necessary corrections to rows that have the status of Error.

Submit Data

The login dialog box appears. You must specify your user ID and password.

The system submits the data to the database in the chunks that you specified on the Connection Information sheet.

After correcting any errors on the data input sheet, you can submit the data again. The items that had been marked as Error will be resubmitted, whereas those marked OK and Warning will be ignored.

Error When Submitting Existing Keys

If you receive the error message Row already exists with the specified keys and you are in CREATE mode, the key already exists at level 0 or is part of the search record.

To verify that the key exists:

Open the component interface in PeopleSoft Application Designer.
Launch the component interface tester by selecting Tools, Test Component Interface.

The component interface Tester search dialog box appears. This dialog box displays the keys (in the left-hand columns) for getting, creating, or finding an instance of the component interface.
Enter the value for the key that you are testing.
Click Get Existing for the key that you are about to add, using the Excel to component interface utility.

If the Get Keys command returns the key, the key already exists and you must add data by using UPDATE mode.

If you receive a message that no row exists for the key, then the key does not exist at level 0 and the data should be added by using CREATE mode.

See Testing Component Interfaces.

Correcting and Resubmitting Data

After you submit the data to the database, results of the process appear on the Staging and Submission sheet. If a submission had an error, the errored status appears on the Staging and Submission sheet. Use the data input page to correct the data and then resubmit it to the database. Continue this process of correcting errors and resubmitting the data until no errors remain.

Note. Data that was not submitted because the error threshold was reached will have no status. When the data that created the error is corrected on the data input sheet, the data that was not submitted will be staged to the database.

Submission Statuses

Errors received for each record submitted appear in a comment field when you move the cursor over the status column. The records marked OK in green have been successfully submitted and cannot be restaged for submission and can be kept as a record of work completed.

One of the following three status values can appear when you submit data to the database:

The submission to the database finished successfully. The field is highlighted in green.

These records cannot be restaged for submission.

Warning

The data was saved to the database successfully, but a warning was generated in the process. The field is highlighted in yellow.

Error

The data was not saved to the database due to an error. This field is highlighted in red.

To see the error message that the component interface generated, place your cursor over the Status field. This message indicates how the data needs to be corrected.

Creating SOAP/XML Requests

You can construct a SOAP/XML (Simple Object Access Protocol/Extensible Markup Language) request to create, update, or get component interface rows. The request and response contain component interface data in a SOAP/XML format.

Request Format

The following example shows the request format:

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"  ⇒
 xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP-ENV:Body>
    <Action__CompIntfc__CIName>
      Tags and Data
    </Action__CompIntfc__CIName>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Valid actions are Create, Get, Update, and Updatedata.

CIname is the name of the component interface.

Tags and Data contains the tags and data for the component interface row or rows.

Sample Create Request

The following example shows a Create request:

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
 <SOAP-ENV:Body>
  <CREATE__CompIntfc__SUPPORT_DOC_TBL>
    <SUPPORT_DOC_ID>POLICE</SUPPORT_DOC_ID>
    <SUPPORT_DOC>
      <DESCR>Police Report</DESCR>
      <DESCRSHORT>Police</DESCRSHORT>
    </SUPPORT_DOC>
  </CREATE__CompIntfc__SUPPORT_DOC_TBL>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Sample Get Request

The following example shows a Get request:

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
 <SOAP-ENV:Body>
  <Get__CompIntfc__SDK_BUS_EXP>
    <SDK_EMPLID>8052</SDK_EMPLID>
  </Get__CompIntfc__SDK_BUS_EXP>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Sample Update Request

The following example shows an Update request:

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
 <SOAP-ENV:Body>
  <UPDATE__CompIntfc__SDK_BUS_EXP>
    <SDK_EMPLID>8001</SDK_EMPLID>
    <SDK_BUS_EXP_PER>
      <SDK_EXP_PER_DT>08/14/2002</SDK_EXP_PER_DT>
        <SDK_BUS_EXP_DTL>
          <SDK_CHARGE_DT>08/14/2002</SDK_CHARGE_DT>
          <SDK_EXPENSE_CD>01</SDK_EXPENSE_CD>
          <SDK_EXPENSE_AMT>1234.56</SDK_EXPENSE_AMT>
          <SDK_CURRENCY_CD>USD</SDK_CURRENCY_CD>
          <SDK_BUS_PURPOSE>Client Visit</SDK_BUS_PURPOSE>
          <SDK_DEPTID>10100</SDK_DEPTID>
        </SDK_BUS_EXP_DTL>
    </SDK_BUS_EXP_PER>
  </UPDATE__CompIntfc__SDK_BUS_EXP>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Sample Updatedata Request

The following example shows an Updatedata request:

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Body>
        <UPDATEDATA__CompIntfc__USER_PROFILE>
            <UserID>VP1</UserID>
            <UserDescription>updated description</UserDescription>
            <EmailAddresses>
                <EmailType>BUS</EmailType>
                <EmailAddress>Updated@updated.com</EmailAddress>
            </EmailAddresses>
        </UPDATEDATA__CompIntfc__USER_PROFILE>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Sending Requests

To send the request, post the XML code to the URL of the PeopleSoft Pure Internet Architecture server with the appropriate path to the iScript on the server.

Note. The PeopleSoft user ID and password must be sent in the SOAP request header with the identifiers of userid and pwd. You should send the request on a secure site.

Use this format:

Protocol(http or https)>://<WebServerMachineName>:<HTTPPort>/psc/ps/<Portal>⇒
/<Node>/s/
WEBLIB_SOAPTOCI.SOAPTOCI.FieldFormula.IScript_SOAPToCI?&disconnect=y&postDataBin=y

WebServerMachineName	Machine name of the server.
HTTPPort	Port of the server.
Portal	Portal defined on the PeopleSoft Pure Internet Architecture server.
Node	Node defined on the PeopleSoft Pure Internet Architecture server.

For example,

http://MyWebServer:80/psc/ps/EMPLOYEE/PT_LOCAL/s/WEBLIB_SOAPTOCI.SOAPTOCI.FieldFormula.IScript_SOAPToCI?disconnect=y&postDataBin=y

Receiving Responses

This section provides examples of response types.

Viewing a Response if a Row Already Exists

This is one example of the error response. The messages vary depending on the type of error.

<?xml version="1.0" encoding="UTF-8" ?> 
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> 
  <SOAP-ENV:Body> 
    <USER_PROFILE> 
      <Error-Warning> 
        <Message> 
          <Type>Error</Type> 
          <MessageSetNumber>91</MessageSetNumber> 
          <MessageNumber>49</MessageNumber> 
          <MessageText>Row already exists with the specified keys.   
             {USER_PROFILE} (91,49)</MessageText> 
          <ExplainText>A rows already exists in the database with the 
           ⇒
 specifiedkeys. 
             </ExplainText> 
        </Message> 
      </Error-Warning> 
      <Key_information> 
        <UserID>PTDMO10</UserID> 
      </Key_information> 
    </USER_PROFILE> 
</SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

Viewing a Sample Get Request and Response

The following XML code gets an SDK_BUS_EXP component interface row for an employee with an employee ID of 8052:

<?xml version="1.0"?>
<SDK_BUS_EXP action="GET">
         <SDK_EMPLID key="Y">8052</SDK_EMPLID>
      </SDK_BUS_EXP>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

The XML response for this employee is:

<?xml version="1.0" encoding="UTF-8" ?> 
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
   <SOAP-ENV:Body>
    <SDK_BUS_EXP> 
      <SDK_BUS_EXP_PER> 
        <SDK_EMPLID>8052</SDK_EMPLID> 
        <SDK_EXP_PER_DT>2000-11-09</SDK_EXP_PER_DT> 
        <SDK_BUS_EXP_DTL> 
          <SDK_EMPLID>8052</SDK_EMPLID> 
          <SDK_EXP_PER_DT>2000-11-09</SDK_EXP_PER_DT> 
          <SDK_CHARGE_DT /> 
          <SDK_EXPENSE_CD /> 
          <SDK_EXPENSE_AMT>0</SDK_EXPENSE_AMT> 
          <SDK_CURRENCY_CD>USD</SDK_CURRENCY_CD> 
          <SDK_BUS_PURPOSE /> 
          <SDK_DEPTID /> 
        </SDK_BUS_EXP_DTL> 
      </SDK_BUS_EXP_PER> 
    </SDK_BUS_EXP> 
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Diagnosing and Resolving Errors

This section discusses how to:

View the log files.
Resolve error messages for client environments.

Viewing Log Files

If you select the check box to create log files when building a template or submitting to the database, two log files are created—one that logs the activity of ExcelToCI2007.xlsm and the other that logs the SOAPTOCI Web Library.

The log for ExcelToCI2007.xlsm is created in the temp directory on the workstation running the Microsoft Excel spreadsheet.

The log for the Web Library, SOAPTOCI<unique_number>.log, is created on the application server in the <PS_CFG_HOME> directory. This file contains both the SOAP request and the SOAP response.

Log files are written for each chunk of data that is sent to the database.

Resolving Error Messages for Client Environments

The following table lists common errors and error messages and their possible resolutions.

Error Message	Possible Resolution
Component not correctly registered	Reinstall Visual Basic 6.0 SP5: Run-Time Redistribution Pack found on the Microsoft download site.
ActiveX component not correctly registered (Error 336)	Reinstall Visual Basic 6.0 SP5: Run-Time Redistribution Pack found at the Microsoft downloads website.
Error Number: -2147024770 Description: Automation error. The specified module could not be found.	Perform the following steps: Open Windows Explorer. Navigate to c:\winnt\system32 directory and locate msxml4.dll. Right-click the DLL and select Register COM Server. The message DLLRegisterServer in c:\winnt\system32\msxml4.dll succeeded. will appear. Click OK.
Error Number: 429 Description: ActiveX component can't create object.	Perform the following steps: Open Windows Explorer. Navigate to c:\winnt\system32 directory and locate msxml4.dll. Right-click the DLL and select Register COM Server. The message DLLRegisterServer in c:\winnt\system32\msxml4.dll succeeded. will appear. Click OK.
Error Number -214722099 Description: Automation error in the dll.	Perform the following steps: Location and open the file ExcelToCI2007.xlsm. Press Alt + F11 to open the Microsoft Visual Basic Editor. Select Tools, Add references. Deselect anything that begins with Microsoft XML. Browse for c:\winnt\system32msxml4.dll and click OK. Select that version of msxml and click OK. Click Save.
Not Authorized (90,6)	The user who is trying to access the component interface from ExcelToCI does not have access to the component interface. Please provide access using PeopleTools Security.

Collection	The name of the component interface collection. A collection is a property that points to a scroll, rather than a field, in the underlying component for a component interface.
Property	The component interface property name. Typically, this is also the name of the field on the page.
Record Type	This number represents the parent/child relationship of the records. The level 0 scroll record is always represented by 000. Level 1 scroll records appear with numbers that start with 100 and always have 00 as the last two numbers. Level 2 scrolls are identified by numbers that start with the identifier of their level 1 parent and end with a 0. Level 3 scrolls are identified by the first number from the level 1 parent, the second number from its level 2 parent, and then the third number from its own position in the list. The numbers for each scroll level are incremented based on the number of records that exist at that level. For example, level 0 would be 000, level 1 would be 100, level 2 would be 110, and so on. Note. Component interfaces that have more than 10 collections at a given level will be incremented with alphabetic identifiers. For example, 800, 900, A00, and so on.
Field Type	The standard PeopleSoft type for the field, for example, Date, Character, and so on.
Field Length	The length of the field as defined by PeopleSoft. For numeric fields and signed number fields, the length is broken down into integer and decimal positions. For example, a length of 15.3 indicates 15 integer positions and three decimal positions.
Key/Required	If the field is a key field, the system will display a Y to the left of the forward slash. When the field is not a key, it will be blank. If the field is a required field, the system will display an R to the right of the forward slash. When the field is not required, it will be blank. This information comes from the record definition itself. Note. Fields that are either keys or required must be set in order to submit data successfully.
Sequence	The sequence number represents the property order in the template.
Status	This field displays the load status on the Staging and Submission page.
Line No line number	This corresponds to the line number on the Input Data and the Staging and Submission pages.