Structure of Integration Broker

PeopleSoft Integration Broker provides a mechanism for communicating with the outside world using XML files. It can be PeopleSoft to PeopleSoft or P2Other system by Synchronous/Asynchronous.

Parts:-

Message: A Message is a container for the data that goes into the XML. It contains basic structural information, such as records and fields. The Message must be in an Active status to send the XML file

Message Channel: The Message Channel is a mechanism for structuring records into logical groupings. Each Message can belong to only one Message Channel. The Message Channel must be in an Active (Run) status for the Message to be delivered.

Message Node: The Message Node is to specify which Gateway receives the messages.Each Message Node can route messages to only one Gateway Connector.

Integration Gateway: The Integration Gateway is a program that runs on the PeopleSoft Web Server. It is the physical hub between PeopleSoft and the third-party system.

Target Connector/Handler: Connectors are Java programs that run under the control of the Integration Gateway and control the final output destination of the XML file. PeopleSoft release 8.4 comes with several connectors including HTTP, FTP, SMTP, JMS, POP3, and a Simple File connector that places the file in a directory on the Web Server. This section discusses the Simple File connector.

PeopleCode: PeopleCode is the programming tool provided with PeopleTools that enables you to create complex application functionality. A message can only be initiated using specific PeopleCode instructions. This code is typically triggered by an application event, such as creating a new database entry through an online panel or through a batch job.

Perform asynchronous and synchronous messaging among internal systems and third-party systems.
Expose PeopleSoft business logic as web services to PeopleSoft and third-party systems.
Consume and invoke web services from third-party and PeopleSoft systems.

CI Based webservices creates an inbound any-to-local routing for the service operation and Handlers for each methods you choose to include in the service.

Component Interface Method	Service Operation Name	Message Name	Component Interface Handler	Request Message Shape	Response Message Shape
Create	<service_name>_C	Mxxxxxx	ONREQUESTHDLR	CI Buffer	Key
Find	<service_name>_F	Mxxxxxx	ONREQUESTHDLR	Get Keys	CI Buffer
Get	<service_name>_G	Mxxxxxx	ONREQUESTHDLR	Find Keys	Key Collection
Update	<service_name>_UP	Mxxxxxx	ONREQUESTHDLR	CI Buffer	Notification
Updatedata	<service_name>_UD	Mxxxxxx	ONREQUESTHDLR	CI Buffer	Notification
User-defined	<service_name>_<method_name>	Mxxxxxx	ONREQUESTHDLR	CI Buffer	Method Return Type

Integration Broker Monitoring:-

The PeopleTools Integration Broker has some built in tables and pages that allow you to inspect the content of messages and respond to errors. The PeopleSoft components to view this data are broken down by:

Asynchronous

Overview Page: PeopleTools > Integration Broker > Service Operations Monitor > Monitoring > Asynchronous Services

Detail Page: PeopleTools > Integration Broker > Service Operations Monitor > Monitoring > Asynchronous Details

Synchronous

Overview Page: PeopleTools > Integration Broker > Service Operations Monitor > Monitoring > Synchronous Services

Detail Page: PeopleTools > Integration Broker > Service Operations Monitor > Monitoring > Synchronous Details

Peoplesoft Integration Broker - Debugging Subscription PeopleCode

Generally, Handler tester being used to debug IB subscription code. In development environments write a simple AE in which will call the IB message transaction ID directly and do debug for each line (just like any other AE debug). In this way, you can get to know the code (if not developed by you) thoroughly.

1. Create an AE program

2. Add a step and select peoplecode action

3. Write the following code in Peoplecode action:-

import IB_EXAMPLE:IBSubscriptionTest (example);

Local Message &ibMsg;

Local IB_EXAMPLE:IBSubscriptionTest &ibSubTest =

create IB_EXAMPLE:IBSubscriptionTest();

Local string &TransactionId = "Transaction ID from Operation Instance";

&ibMsg = %IntBroker.GetMessage(&TransactionId, %IntBroker_BRK);

&ibSubTest.OnNotify(&ibMsg);

4. Run the AE in debugger mode.

Troubleshooting

All operations are in “NEW” Status"
- Make sure the queues are not paused
- Make sure the pub/subs are active. See the Administration Section
I am not seeing anything in the Subscription contracts for a message that as posted.
- Make sure your handlers are active and make sure you have inbound or local to local routings.
I am not seeing the message get into the publication contracts
- Make sure your publication contracts are active
I am not seeing any messages in the Synchronous monitor
- The service operation does not have any logging turn on. Check the routing to ensure there is at least header logging

Debugging:-

Enable DEBUG on the Integration Broker, as follows:

Go into integration Gateway.properties and set ig.log.level=5

Boot the Peopleoft application-

Go to the deployment page, undeploy one of the indexes, i.e. one real index, not the round trip test, one of the real indexes,

e.g. PTPORTALREGISTRY that hasthe crawler issue.

Run Report/Synch, did it give any errors?

Run Audit, did it give any errors?

Now try to deploy this index and crawl.

Enable extended DEBUG on the SES Server guided by the below solution:

How to Turn on Debug Level Logging for Secure Enterprise Search (Doc ID 737515.1)

After replicating the issue, please collect the below logs so I can take a closer look:

PEOPLESOFT INTEGRATION BROKER

===============================

1. errorLog.html (in <PS_CFG>/webserv/<DOMAIN_NAME>/applications/peoplesoft/PSIGW.war)

2. msgLog.html (in <PS_CFG>/webserv/<DOMAIN_NAME>/applications/peoplesoft/PSIGW.war)

(need to go into integrationGateway.properties and set ig.log.level=5 for this to work)

3. PIA_stdout.log (or NTService log on Windows) (in <PS_CFG>/webserv/<DOMAIN_NAME>/servers/PIA/logs)

4. APPSRV_MM_DD.log (in <PS_CFG>/appserv/<DOMAIN_NAME>/LOGS

DATABASE LOGS

==============

1. listener.log (in <SES_HOME>/log/diag/tnslsnr/<MACHINE-NAME>/listener/trace)

2. database alert log

SES 11.2.2.2 LOGS

======================

MID-TIER LOGS (WebLogic)

------------------------------------------------------

1. search_server1_yyyy_MM_dd_hh_mm.log and search_server

1-diagnostic.log(in <SES_MIDDLETIER_HOME>/ user_projects/domains/search_domain/servers/search_server1/logs)

2. access.log (in <SES_MIDDLETIER_HOME>/ user_projects/domains/search_domain/servers/search_server1/logs)

CRAWLER/INDEX BUILD LOGS

-------------------------------------------------------------

1. search.crawler.i*.MMDDHHMM.log (in <SES MIDDLETIER_HOME>/logs/crawler/crawler)

ExcelToCI Troubleshooting Guide

Introduction

The purpose of this document is to provide basic information about ExcelToCI, and assistance with troubleshooting problems when using ExcelToCI. Although there are no prerequisites to using ExcelToCI it is very helpful to have an understanding of Component Interface (CI) technology. For additional information on CIs and ExcelToCI see the PeopleTools Component Interface PeopleBooks. You may also notice that the spreadsheet screenshots are not all identical, and may look different from your spreadsheet. The reason for this is that the document was developed and updated over multiple PeopleTools releases.

Some things to remember:

Not all CIs are suitable for ExcelToCI.

The SOAPTOCI Peoplecode sets the CI properties in the same order in which they appear in Application Designer. This will be a problem in cases where properties need to be set out of sequence in order for Peoplecode validations to work correctly.

Avoid using large complex CIs. However, if you elect to use them, send them in smaller chunks to avoid running into problems such as timeouts, and to improve the application server throughput.

Ensure that you use a copy of the ExcelToCI workbook found in <PS_HOME>\excel for the PeopleTools release you’re on. Also, make sure you don’t open multiple Excel workbooks at the same time, either multiple ExcelToCI workbooks or a combination of ExcelToCI workbooks and other Excel workbooks to avoid seeing strange behavior. Note the following about the Excel spreadsheet (and macro file).

a. PT 8.49 - ExcelToCI2007.xlsm - Both the spreadsheet and macro are in a single file. This file was released in the PT 8.49.30 or PT 8.49.31 PeopleTools patch.

b. PT 8.50 and later - Both ExcelToCI2007.xlsm and RelLangMcro.xla (macro file) are used. Before opening the ExcelToCI spreadsheet, copy both files to the Excel client machine’s test/working directory to avoid errors.

NOTE: The following sections cover some of the available topics, not everything. See the ExcelToCI documentation in the PeopleSoft Component Interface PeopleTools PeopleBooks – PeopleSoft PeopleTools 8.53 PeopleBook > PeopleSoft Component Interfaces > Using the Excel To Component Interface Utility)

Tool Basics

Connection Page

Web Server machine name: Provide the web server machine name. If you created auth token domain, then include it in the name. The name should match the PIA URL server name (use the same name as when logging into PIA via the browser), port and site name, and you should be able to log into PIA using the web server, port and site name used here.

Protocol: Default is http. If you want to use https, ensure that the SSL certificates are properly installed, including any required client certificates. One way of testing whether SSL is properly installed is to open a browser and try logging into PeopleSoft using the https port. If the certificates are invalid, you will usually get a pop up message indicating possible problems with the certificates. However, if you clicked Ok to continue in spite of the questionable certificates, you won’t see this popup on subsequent attempts to log into PeopleSoft.

NOTE: The default SSL setting (In WebLogic the setting is “Two Way Client Cert Behavior”, and the default setting is 'Client Certs Not Requested') is supported. If client cert setting is “Client certs requested, but not required” or more stringent, uploads from Excel will not work.

HTTP/HTTPS Port: Provide the web server’s port number. The default http port is 80. The default https port is 443. Even though these are the standard defaults, verify that the port number is correct in the case where the port number does not appear in the PIA login URL because your system administrator can set up the environment such that even though the port number is resolved without it being specified when logging into PIA the port number might not be 80 or 443.

Portal: The name of the portal you are using. EMPLOYEE is the 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. The site name can be determined from the PIA browser URL, as it is the second field after the port number in the URL. In sample below it is E910G21U. Also remember this field is case sensitive.

http://sla.us.oracle.com:8200/psp/E910G21U/EMPLOYEE/HRMS/h/?tab=DEFAULT

Node: The PeopleSoft default local node name. The default is PT_LOCAL. To determine the default local node name, log into PIA and navigate to PeopleTools > Integration Broker > Node Definitions. Then press the search button without providing any search criteria. The default local node will have a 1 in the Local Node column and a “Y” in the Default Local Node column.

Language Code: The code for the language that the data is submitted to the database in and the template is created in. If no language code is specified, the base language is used.

Chunking Factor: The number of rows of data to be transmitted to the database at one time. The default is 40. If the CIs being used are large and/or have much Peoplecode, a smaller value will probably result in better throughput by the application server. Testing will help determine a suitable value.

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. You should specify a valid number instead of leaving it blank.

Submit Blanks As Input: This option is available in PT 8.51 and later. 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.

Optional Keys: This option is available in PT 8.53.03 and later, and in PT 8.52.15 and later. This option is provided for use by Oracle Applications development, not for customer use. Use the default value of No.

Action: The value for the Action field is populated by the system when the component interface is retrieved from the database. However, you can change the populated 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 different actions are:

Create: If the component interface has create keys. Use this mode when new keys are being added at level 0.

Update: Use this mode if you are adding new children to an existing parent (e.g. inserting new rows into existing collections).

UpdateData: This mode is used to update specific non-key values that already exist but need to be updated. 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.

NOTE on effective dated processing: When inserting effective dated collection rows remember the following. First, “History” is included in the data set. This means that both historic data (in the past), and future data are included in the existing collection data. Secondly, the data used to initially populate the new row is copied forward from the existing row at index 1. This means that if the existing Level 1 collection row at index 1 has child collections containing data, those child collection rows are copied forward to the new Level 1 row’s children, but with the effective date of the new row. The reason this behavior is present is due to the following settings in the Peoplecode processing the request.

1. History – The GetHistoryItems and EditHistoryItems flags are set to “True” in the PeopleCode processing the data. This means that both historic data (in the past) and future data are included in the data set. This would be similar to Correction Mode when using the online component.

2. Index passed to InsertItem() and Item() methods - The index passed to the method being used (InsertItem or Item) is always 1. This means that the data used to initially populate the new row will be taken from the existing row at index 1, even if the existing row is a future dated row. For EFFDT (effective dated data), if you want to insert at other indexes, see the two solutions below for more information on how this can be accomplished.

Doc ID 973020.1 E-ExcelToCI - Can the Default Values of the newly inserted EFFDT Row be Copied forward from a Row other than Row 1?

Doc ID 1270589.1 E-ExcelToCI: EFFSEQ Key Is Not Being Used to Determine Insert Index using EFFDT Data

Template Page

New Template: Use this button to download and create a new template based on a component interface.

Select Input Cell: Use this button to select fields to be included on the Input page. The selected cells will have a pink background (as shown above).

Include For Submission: Use this button to include 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. Determine which fields you want to supply. Then enter the value into each of these fields on the Template page. These properties and values will show up on the Staging & Submission page, not the Data Input page.

Insert New Child: Use this button to Copy 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. If you want to upload multiple collection rows, see the next section, Handling Multiple Child Collection Rows.

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.

Handling Multiple Child Collection Rows

Simple scenario

This is the case where the “multiple row” collection does not have any children, so you don’t need to maintain a hierarchy. For this case you can simply use the Insert New Child button as shown below. The example below uses the USER_PROFILE CI. The Roles collection is a Level 1 collection, and RouteControls is the Level 2 child collection of Roles. Note: This is simply used for illustration purposes.

A. Before inserting second L2 collection row

B. After inserting second L2 collection row

C. Data Input page (note that both L2 rows are “flattened”)

D. Staging & Submission page (note the 2 L2 rows, hierarchy is restored)

Complex scenario

Using the above example, suppose we need to be able to have multiple L1 (Roles) and L2 (RouteControls) collections. In this case we’ll just illustrate inserting two L1 and L2 rows. You will need to use the same technique for additional rows up to the maximum number of rows you expect to upload. When uploading, fill in the data for the number of rows from the left side, leaving any blank rows to the right on the Data Input page.

A. Template page

1. Click on column 1 of the highest-level cell (in this case it is the L1 row), and select “Insert New Child”. Repeat this for the maximum number of rows you intend to upload. For this example we’re going to use three L1 and three L2 child rows, so we need to insert a total of 6 rows. Since there’s already one L2 child row we want to end up with five L1 rows and one L2 row initially, as shown in the screenshots below.

2. Right-click on column 1 of row 21 above, copy and paste (Excel menu) the 310 row contents into the alternating 300 rows so you have the desired hierarchy, as shown below. This will allow us to insert up to three L1 collection rows, each with a L2 child row.

3. Press the “New Data Input” button to create a new Data Input page.

B. Data Input page: Note that the hierarchy is flattened and only two L1 rows have data. After entering the data press the “Stage Data for Submission” button.

C. Staging & Submission page: Note that the third L1 row and its child row are empty. When the data is uploaded the blank row will not be submitted*.

* Note: On PT 8.49 and earlier the behavior is as described above (e.g. the blank row will not be submitted). The 8.50 and 8.51 VB macro code logic has a modification, which uploads the blank row. This can cause issues in cases involving collection keys with default values. See Doc 758609.1 and 1288146.1 for additional information.

Testing/Troubleshooting

In terms of testing, first test the CI’s underlying component online to ensure that it works properly. Then test the CI using the CI Tester as described in section A below before testing from ExcelToCI. It’s a good idea to use this testing sequence, especially when developing custom CIs (CIs built from custom or delivered components). For additional information on developing and testing CIs see “Doc ID 1509602.1 - E-CI: Developing and Testing Component Interfaces (CIs)”. This document has helpful information on developing and testing CIs, as well as links to other related solution documents.

A. CI Tester

1. Open Application Designer in 3-tier (that is through the application server’s WSL port (see screenshots of Configuration Manager below). Note: Prior to PT 8.48, the WSL process was automatically enabled to run, but in PT 8.48 later, it must be explicitly enabled from the psadmin menu.

2. Once in Application Designer open the CI to be tested.

3. Open the CI Tester (right-click on the CI and select “Test Component Interface” or select “Test Component Interface” from the Tools menu. See the Component Interface PeopleTools PeopleBooks for additional information on using the CI Tester.

4. Test using the same scenario as from Excel (Create, Update, or UpdateData).

Note: For items iii (Update) and iv (UpdateData) below, ensure that both GetHitoryItems and EditHistoryItems flags (checkboxes in CI Tester) are set to True (checkboxes are selected in CI Tester).

i. Enter the values for the properties in the sequence in which the CI properties appear. While doing so pay attention the behavior (for example a value that was accepted by the system (no edit error) being blanked after some subsequent properties’ values are entered).

ii. For the Create Action enter the create keys, press the “Create New” button and then enter the other properties’ values.

iii. For Update, enter the get key values, press the “Get Existing” button, and then navigate to collection(s) into which you’re inserting new row(s). Then right-click on the collection (cube shaped) icon and select the InsertItem() method and supply an index of 1. Then enter the correct values for the new row. When processing the request from Excel, the SOAPTOCI PeopleCode uses a value of 1 for the InsertItem() method.

iv. For UpdateData, enter the get key values, press the “Get Existing” button, and then navigate to the properties being modified and edit the value.

v. Once you’ve performed item ii, iii, or iv above, select the topmost row in CI Tester (row with the CI icon), right-click and select Save. A return value of 1 indicates success, while a value of 0 indicates that the changes were not successful. If 0 was returned you’ll see error messages at the bottom of the tester utility.

vi. Use # 5 below to get a SQL and PeopleCode trace of the execution path. Use # 6 below to step through the underlying PeopleCode.

5. SQL/PeopleCode tracing - Test with SQL and PeopleCode tracing enabled on the application server to get a better understanding of the execution path. I use tracing values of 127 (SQL) and 3596 (PeopleCode). To enable tracing do the following.

i. Open the psappsrv.cfg file.

ii. Locate the following bitfield settings and set them to the desired values (the values I use are shown below).

TraceSql=127

TracePC=3596

iii. Save the psappsrv.cfg file.

iv. Restart the application server. Note: If you have the flag shown below set to Y (as shown below), you don’t need to restart the application server each time you want to enable/disable tracing. Simply make the bitfield changes, save the psappsrv.cfg file, and the application server will accept the changes while it’s running. Also, if this flag is set to Y, you’ll have better control over what is traced and avoid a slow starting application server. You’ll also minimize the amount of tracing, and avoid generating and looking through unnecessary and irrelevant trace information. When using CI Tester for example, first open the CI Tester in application designer, and then set the above trace bitfield settings to enable tracing.

Allow Dynamic Changes=Y

6. PeopleCode debugger - Test with the PeopleCode debugger if you want to step through the underlying PeopleCode. For this do the following.

i. Shut down the application server. Reconfigure the domain so that a PeopleCode debugger application server process is started. Then restart the application server.

ii. Open application designer in 3-tier through the same application server.

iii. Open the CI to be tested.

iv. Select “Debug” > Enter Debug Mode from the menu. Then select “Break at start” from the Debug menu selection.

v. Open the CI Tester and begin testing. The debugger will stop at the first segment of PeopleCode that fires, and you can step through it to follow the execution path.

B. ExcelToCI

1. ExcelToCI and SOAPTOCI logs – Generating/reviewing these two logs for a transaction is sometimes sufficient to find the source of the problem. When submitting the data check the Generate Log checkbox as shown below.

The Generate Log selection causes the following two log files to be created. Note that a new log file is creating each time you submit data.

i. SOAPTOCIxxx.log – By default this log is created in the app server's "files" directory (below the app server domain). The xxx is the timestamp appended to the file name. This log shows the PeopleCode execution path in the SOAPTOCI application package, and it can help you quickly determine the cause of the behavior you’re seeing.

ii. ExcelToCIxxx.log – By default this log is created in user’s Temp directory. The xxx is the timestamp appended to the file name. To determine the location where your Temp directory is mapped to do the following. On the Excel client machine’s desktop right-click on the “My Computer” icon and select Properties. Then select the “Advanced” tab and click on “Environment Variables”. Locate the Temp (or tmp) variable mappings (it may be C:\Temp or some other location). A quick alternative is to open Windows Explorer, enter %Temp% in the address bar and press the Enter key. This will resolve to your Temp directory location. This is normally where the ExcelToCIxxx log is placed. This log simply contains the SOAP format message containing the Excel data, and is useful in verifying that the uploaded data is the same as in the Excel spreadsheet.

2. SQL/PeopleCode tracing – After submitting the data locate and review the SOAPTOCI log. If the log does not provide sufficient detail to narrow down the source of the problem, enable SQL and PeopleCode tracing on the application server and upload another transaction from Excel with the Generate Log checkbox checked.

3. Comparing ExcelToCI to CI Tester results – If the results from ExcelToCI are different from those using CI Tester, test using CI Tester (in 3-tier) using the same values as from Excel with PeopleCode and SQL tracing enabled on the application server. Save the trace file and then run another transaction using ExcelToCI (with PeopleCode and SQL tracing enabled on the application server). Save the second trace file. Compare the execution path and values in the two trace files.

4. Debug from VBA - In some cases (for example, where values are not being correctly handled in Excel, or other Excel related processing), you can step through the VBA code to try and pin point the source of the problem. To step through the VB code do the following:

i. From Excel, press Alt-F11 to open the VB Editor.

ii. Locate an appropriate location/line of code for the Breakpoint, and press F9 to enable the Breakpoint (press F9 to disable it as well). Save the setting in VB.

iii. Go back to Excel, and perform the operation (such as submit the data) to hit the VB Breakpoint you set above. Then step through the VB code using the appropriate debugger functions (such as Step Into, Step Over, etc.).

C. Additional information/resources

1. Uploading large volumes of data using complex CIs.

a. ExcelToCI was not designed for batch/large volume processing

b. You may see strange error messages when using ExcelToCI to upload large volumes using large, complex CIs. Also, the errors may not occur all the time. For example, you may see messages in sequence such as “The response text is not valid XML. Login Data cleared”, “Error occurred in routine sendSOAPRequest_SubmitToDB… The parameter is incorrect”, and “The XML string contains invalid characters”. One of the reasons for the errors is that the application server is handling multiple long running transactions, resulting in timeouts by the web server.

c. Suggestions:

i. Avoid using large complex CIs. Build and use streamlined (CIs that are not large, complex, and do not have a lot of Peoplecode in the underlying layers.

ii. Use a smaller Chunking Factor (default is 40). You can drop the value to 1, and then increase it until you find a reasonable value that works. Also remember that the error probably won’t occur all the time, and will depend on different factors such as the available hardware resources (web server and application server), amount of load on the system at processing time, the distance between the Excel client and the web server (client is miles away from the server), etc. So, you don’t want to use an aggressive (large) value. Using a smaller value means more data uploads from the client. But it also means that uploads should be more evenly distributed among the application server instances, improve application server throughput, and make web server timeouts less likely.

iii. If you are on PeopleTools 8.50 or later, and are using the Microsoft XML (parser) v. 6.0 (which you should be), you can try to extend the timeout values in Excel. To do so, see Document 1509108.1 for step by step instructions.

2. Additional note about the Chunking Factor – The “transaction boundary” begins with the Iscript invoked once the upload gets to the web server and encompasses the number of records in the Chunking Factor. There are scenarios where if you use a Chunking Factor larger than one, you can have some of the cells show up “Ok – green cell color” even though the record was not saved to the database. The SOAPTOCI application class was not able to tell if a rollback has occurred when the CI’s save() method was called. This is due to the complexity of the underlying layers below the Peoplecode. If a CI rollback occurs for one of these records, the SOAPTOCI application class would not know the rollback occurred. And it would return an OK status for the previously saved rows (except the current row in error) back to ExceltoCI. This would cause ExcelToCI to display incorrect results. This is due to a combination of the CI’s underlying component’s behavior, and how the transaction boundaries are defined.

3. Changing Environments – It is sometimes necessary to “migrate” an existing spreadsheet to a new environment. When doing so, it is important pay close attention to the connection information, and to ensure that the CI is the same (structure and properties) in both environments. Recommendations include the following:

a. Connection Information - Carefully go through the Connection tab entries (see pages 3-5 for more information on the Connection tab entries). Two common mistakes made are (a) Using the wrong port number in the new environment; and (b) Using the wrong Protocol (http/https). It’s easy to miss the fact that one environment may be using http, while the other is using https. Mistakes such as these can result in errors such as the following:

“Error occurred in routine sendSOAPRequest_SubmitToDb

The error:

Number: -2147012744

The server returned an invalid or unrecognized response”

“The XML string contains invalid characters”

…

b. CI/Component structure/meta data differences – Prior to uploading data in the new environment, verify that the CI and its underlying component are the same in both environments. This includes the properties, pages, records, fields, underlying PeopleCode, etc.

4. ExcelToCI and SSO (Single Signon) - The key to incorporating SSO with ExcelToCI begins with an understanding of the architecture of ExcelToCI and the manner in which SSO is handled. Below is a brief description of the ExcelToCI architecture and a brief discussion of how SSO may be handled.

a. ExcelToCI Architecture - The upload process begins with the user providing a valid PeopleSoft Userid and password in the upload dialog window. The underlying VB macro then posts the data and user credentials in a SOAP envelope to the PeopleSoft web server. The upload process is non-interactive, meaning that once the user enters his credentials on the upload window and presses the “Ok” button, there is no further user interaction.

b. SSO Architecture - Typically this entails knowing whether the user is authenticated prior to the request hitting the web server or after hitting the web server. Regardless of when the user is authenticated (before or after hitting the web server) user credentials need to be provided in the request from Excel. If the user is authenticated prior to the upload reaching the web server, it is up to you (and your SSO vendor if applicable) to determine how to authenticate the user, and add the credentials into the upload so they get to the web server. When the signon Peoplecode executes you will also need to ensure that it authenticate the user's "credentials" that were provided. For example, if a user is re-directed to an alternate site when attempting to sign on into PIA (via the browser) to provide his SSO credentials, you (or your SSO solution vendor) will need to customize the VB macro code so the payload goes to the appropriate site, the user is authenticated, and some form of a cookie (or the PeopleSoft userid) is included with the package that is sent to the PeopleSoft web server. Additionally, if you want to avoid having a dialog window for the user to provide credentials, you will need to extract them from the appropriate location in time for the upload and ensure that they are present in the upload's http header.

5. It is a best practice to only have one PeopleSoft Excel upload utility spreadsheet open at one time. Having more than one spreadsheet open at the same time can corrupt the macros, and cause undesired side effects.

6. Search the Solutions available in “my Oracle Support”. Typically ExcelToCI solutions have “E-ExcelToCI” in the summary.

7. For additional information on developing and testing CIs see the document below: This document has helpful information on developing and testing CIs, as well as links to other CI related solution documents.

Doc ID 1509602.1 - E-CI: Developing and Testing Component Interfaces (CIs)

8. Numeric Keys – Avoid the use of numeric keys, especially those with decimal values. Using them can result in rows not being found, or possibly the wrong row being found because of differences in the decimal value. Additionally, using a decimal numeric key that is less than 1 but greater than or equal to 0 (or greater than -1 but less than or equal to 0 for a signed number) will result in a “No key match found” error because the SOAPTOCI libraries perform a string comparison of the incoming value to the value in the database. For example, suppose a key value of 0.01 is uploaded. When the Excel key value is compared to the value in the database (.01), the comparison will fail, resulting in the above error. If you still want to use such as key, you will need to customize the processing PeopleCode so the match is based on the value (and the strings match). One way of doing this would be to strip off the leading zero using one of the PeopleCode Built-In functions.

9. Open a case with the PeopleSoft GCS (Global Customer Support).

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 msxml6.dll. Right-click the DLL and select Register COM Server. The message `DLLRegisterServer in c:\winnt\system32\msxml6.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\msxml6.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\system32msxml6.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.

PeopleSoft Tips & Tricks