Peoplesoft: July 2018

Follow the following steps to implement the Excel To CI Utility

1) Grant access to the WEBLIB_SOAPTOCI iScript

Grant the above mentioned iscript in one of the permission lists of the user who running it.

2) Enable Developer Menu in Microsoft Excel.

· Go to Excel Options. Click on the checkbox of Show Developer tab in the Ribbon.

3) Make sure the macros are enabled (Microsoft Excel 2007).

· Open ExcelToCI.xls file which is located in the Excel folder of the PS_HOME.

· Save As the ExcelToCI.xls Into Excel Macro-Enabled Workbook (xlsm format).

· The workbook name must have .xslm extension .

4) Now open the Excel sheet , Need to supply the connection information and CI name associated with the component in further tabs

Open the connect information tab from the Excel sheet.

Most of the time above mentioned details you can get from the URL of the PIA of the any random page , some need to collect from the DBA.

Web Server Machine Name - Name of the web server connected to

Protocol - HTTP/HTTPS

HTTP Port - Port Number Web server is using

Portal - Portal name using , Default is EMPLOYEE

PeopleSoft Site Name - PeopleSoft Site Name that was given at the time of installation of PIA ,Default one is ps

Node - PeoplSoft node name , Default one is PT_LOCAL

Language Code -ENG

Chunking Factor -No of rows of data to be transmitted to DB at one time , Default one is 40

Error Threshold - It can be set if we want to stop the import into DB after certain no of errors reached

Action - This will be automatically set when the CI is set

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.

Most of the time you can fill the data by the PIA login URL

i.e http://hrms90v:9180/psp/HRMS90/?cmd=login

Web Server : hrms90v
Protocol : http
Port : 9180
PeopleSoft Site Name : HRMS90

5)Connect to Database to create template and submit data
Now go to Template tab and supply the Username ,password and CI name

Click on Add-Ins From menu and select New Template

Prompt will appear

Fill the details of ID, PWD of Application Designer that you created CI and CI name, Which you want to Import the data through your component .

Once submitting this utility will import the structure of the CI in the following way into Template tab.

Collection : Scroll Data of the underlying component

Property : Fields on the page

Record Type : Indicates the number related to scroll

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.

Field Type :Data Type of the field

Field Length : Length of the field

Key : Indicates the key structure of the field , This information comes from the record structure it self

Sequence : The sequence number represents the property order in the template

Now make the fields into Gray by selecting them and click on Select Input Cell from menu , Here gray indicates that those are fields that you are including in your Template , Template looks like in the following way.

Now Click on New Data Input from Menu to make the final template which accepts data from user.

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

Once after clicking it , It prompts you in the following way

Clc

on Yes , now we will enter into Data Input tab

6) Sample data is presented in the following way

Once the data entry is done proceed with clicking Stage Data for Submission button from the menu

7) Finally system will automatically enters into final tab Staging & Submission tab

8) Finally click on Submit Data , It prompts for ID,Pwd .Supply the values & click on OK . It automatically populates the data into DB.

In general we use Component Interfaces to perform the following actions

Create a new Instance of data
Get existing Instance of data
Retrieve a list of instances of data

Creating new instance of data:

1. Get a session object
2. Get a Component Interface
3. Set the Create keys

4. Use the Create() method to create an instance of the data
5. Use Save() method , So the instance of the data will be saved to the database.

In order to create new set of data the code can be modified in the following way

1) Get a session object

A session has to declared with a data type (i.e. API Type) & Need to get current PeopleSoft Session.From the above coding, it is

Local ApiObject&oSession, &oSampleCi;

rem ***** Get current PeopleSoft Session *****;

&oSession = %Session;

2) Get a Component Interface

Use the GetCompIntfc method with a session object to get the Component Interface.

Make sure that you are inputting valid CI name or else you will receive an error

rem ***** Get the Component Interface *****;

&oSampleCi = &oSession.GetCompIntfc(CompIntfc.SAMPLE_CI);

If &oSampleCi = Null Then

errorHandler();

throw CreateException(0, 0, "GetCompIntfc failed");

End-If;

At this point you have the structure of the Component Interface it is not yet populated with any data

3) Set the Create Keys

These values will make a new instance of data that has to be populated into db

Make sure you are inputting unique key values

rem ***** Set Component Interface Get/Create Keys *****;

&oSampleCi.EMPLID = “2344”;

4) Use the Create() method to instantiate the data

Need to remove [rem] to the Create method

Add [rem] to Get () method as we are not getting any instances of the data from db

I.e. it looks like

rem ***** Execute Get *****;

rem If Not &oSampleCi.Get() Then

rem ***** No rows exist for the specified keys.*****;

rem errorHandler();

rem throw CreateException(0, 0, "Get failed");

rem End-If;

rem ***** Execute Create ******;

If Not &oSampleCi.Create() Then;

rem ***** Unable to Create Component Interface for the Add keys provided. *****;

errorHandler();

throw CreateException(0, 0, "Create failed");

End-If;

This successfully creates the new instance of the data , It’s not yet saved into DB

5) Set the values for the rest of the fields and execute Save() Method to successfully save the new instance of the data into DB

Remove [rem] from all the individual fields and assign the value

Remove [rem] for the Save () method

rem ***** Begin: Get/Set Component Interface Properties *****;

rem ***** Get/Set Level 0 Field Properties *****;

&fileLog.WriteLine("&oSampleCi.EMPLID = " | "2234");

&oSampleCi.EMPLID = "2234";

&fileLog.WriteLine("&oSampleCi.MOBILE_PHONE = " | "9985");

&oSampleCi.MOBILE_PHONE = "9985";

&fileLog.WriteLine("&oSampleCi.NAME = " | "Varma,penumatsa");

&oSampleCi.NAME = "Varma,penumatsa";

&fileLog.WriteLine("&oSampleCi.SEX = " | "M");

&oSampleCi.SEX = "M";

rem ***** Set/Get SAMPLECI_1 Collection Field Properties -- Parent: PS_ROOT Collection *****;

&oSampleci1Collection = &oSampleCi.SAMPLECI_1;

Local integer &i17;

For &i17 = 1 To &oSampleci1Collection.Count;

&oSampleci1 = &oSampleci1Collection.Item(&i17);

&fileLog.WriteLine("&oSampleci1.DEPTID = " | "110");

&oSampleci1.DEPTID = "110";

&fileLog.WriteLine("&oSampleci1.DEPTNAME = " | "Finance");

&oSampleci1.DEPTNAME = "Finance";

End-For;

rem ***** End: Get/Set Component Interface Properties *****;

rem ***** Execute Save *****;

If Not &oSampleCi.Save() Then;

errorHandler();

throw CreateException(0, 0, "Save failed");

End-If;

After running this, automatically new set of data will be uploaded into db.

Get an existing Instance of data

In order to Get a new set of data the code can be modified in the following way

1) Get a session object

Session has to declared with a data type (i.e. API Type) & Need to get current PeopleSoft Session .From the above coding it is

Local ApiObject&oSession, &oSampleCi;

rem ***** Get current PeopleSoft Session *****;

&oSession = %Session;

2) Get a Component Interface

Use the GetCompIntfc method with a session object to get the Component Interface.

Make sure that you are inputting valid CI name or else you will receive an error

rem ***** Get the Component Interface *****;

&oSampleCi = &oSession.GetCompIntfc(CompIntfc.SAMPLE_CI);

If &oSampleCi = Null Then

errorHandler();

throw CreateException(0, 0, "GetCompIntfc failed");

End-If;

At this point you have the structure of the Component Interface it is not yet populated with any data

3) Set the GET Keys

These values will make a new instance of data that has to be populated into db

Make sure you are inputting unique key values

rem ***** Set Component Interface Get/Create Keys *****;

&oSampleCi.EMPLID = "2234";

4) Use the GET() method to instantiate the data

I.e. it looks like

rem ***** Execute Get *****;

If Not &oSampleCi.Get() Then

rem ***** No rows exist for the specified keys.*****;

errorHandler();

throw CreateException(0, 0, "Get failed");

End-If;

rem ***** Execute Create ******;

rem If Not &oSampleCi.Create() Then;

rem ***** Unable to Create Component Interface for the Add keys provided. *****;

rem errorHandler();

rem throw CreateException(0, 0, "Create failed");

rem End-If;

5) Set the values for the rest of the fields and execute Save() Method to successfully get the new instance of the data from DB

Remove [rem] from all the individual fields and assign the value

Remove [rem] for the Save () method

rem ***** Begin: Get/Set Component Interface Properties *****;

rem ***** Get/Set Level 0 Field Properties *****;

&fileLog.WriteLine("&oSampleCi.EMPLID = " | &oSampleCi.EMPLID);

&oSampleCi.EMPLID = &oSampleCi.EMPLID;

&fileLog.WriteLine("&oSampleCi.MOBILE_PHONE = "&oSampleCi.MOBILE_PHONE);

&oSampleCi.MOBILE_PHONE = &oSampleCi.MOBILE_PHONE;

&fileLog.WriteLine("&oSampleCi.NAME = " | &oSampleCi.NAME);

&oSampleCi.NAME = &oSampleCi.NAME;

&fileLog.WriteLine("&oSampleCi.SEX = " | &oSampleCi.SEX);

&oSampleCi.SEX = &oSampleCi.SEX;

rem ***** Set/Get SAMPLECI_1 Collection Field Properties -- Parent: PS_ROOT Collection *****;

&oSampleci1Collection = &oSampleCi.SAMPLECI_1;

Local integer &i17;

For &i17 = 1 To &oSampleci1Collection.Count;

&oSampleci1 = &oSampleci1Collection.Item(&i17);

&fileLog.WriteLine("&oSampleci1.DEPTID = " | &oSampleci1.DEPTID);

&oSampleci1.DEPTID = &oSampleci1.DEPTID;

&fileLog.WriteLine("&oSampleci1.DEPTNAME = " | &oSampleci1.DEPTNAME);

&oSampleci1.DEPTNAME = &oSampleci1.DEPTNAME;

End-For;

rem ***** End: Get/Set Component Interface Properties *****;

rem ***** Execute Save *****;

If Not &oSampleCi.Save() Then;

errorHandler();

throw CreateException(0, 0, "Save failed");

End-If;

Peoplesoft

EXCEL To CI

Implementing a Component Interface