Before reading this article you must understand the basics of component interface Understanding Componenet Interface in PeopleSoft
When a business is run through PeopleSoft or in any other enterprise application for that matter and if you are providing PeopleSoft support to that business, you get endless requirements of different kind as part of your work. I would like to bring up an example of the two requirements to upload the data in PeopleSoft system.
- The first requirement is to upload the fringe benefit tax information sent by third party system into PeopleSoft on daily basis.
- The second requirement is to upload 200 promotions in the system through Job Data component.
How do you think these requirements can be catered to. Well, whenever we hear upload word in PeopleSoft the first think which comes in mind is Component Interface (CI) but can both of the above requirements be catered through CI ?
The answer would be yes because after all its about upload and with the CI being so powerful tool there is no deny to it but if you observe closely, there is a basic difference between these two requirements and that is - the first one is a daily upload activity but the second one is a one time upload activity. Hence, its quite obvious that for first requirement we will be creating the CI of the target component where Fringe Benefit Tax information is gonna be stored and call this CI from a new Application Engine program which can be scheduled as a process on daily basis.
But what about second one ? should we really create an entire Application Engine which would work in conjunction with CI when its a one time exercise ?
But what about second one ? should we really create an entire Application Engine which would work in conjunction with CI when its a one time exercise ?
I would simply say No ..! we should rather be using Excel to CI (ExceltoCI) utility to do the same however, in this case also we need to create CI for target component.
PeopleSoft Excel to CI or ExceltoCI is a Microsoft excel based application which is used to upload the data from Microsoft Excel workbook to PeopleSoft system. So basically, Excel to CI tool will take the data as input in excel format and pass it to CI which will ultimately load the data into database through underlying component.
There are four simple steps to be followed in order to configure the ExceltoCI (Excel to CI) tool and then load the data through it.
Before we begin, first we need to get this utility either from someone who already have it or from the PeopleSoft directory <PS_HOME>/excel. Once you have it with you, just open it. You will see 5 tabs in this excel as shown below and it also represents 4 step process in ExceltoCI utility:
As mentioned earlier, its a four step process:
PeopleSoft Excel to CI or ExceltoCI is a Microsoft excel based application which is used to upload the data from Microsoft Excel workbook to PeopleSoft system. So basically, Excel to CI tool will take the data as input in excel format and pass it to CI which will ultimately load the data into database through underlying component.
There are four simple steps to be followed in order to configure the ExceltoCI (Excel to CI) tool and then load the data through it.
Before we begin, first we need to get this utility either from someone who already have it or from the PeopleSoft directory <PS_HOME>/excel. Once you have it with you, just open it. You will see 5 tabs in this excel as shown below and it also represents 4 step process in ExceltoCI utility:
As mentioned earlier, its a four step process:
- Connection
- Template
- Data Input
- Staging and Submission
Connection
First we need to establish connection between ExceltoCI utility and the desired component interface (CI) and to begin with, first open the target PeopleSoft instance into which you wish to upload the data. Let's assume that instance name is HCMDEV so open it in the web browser and copy different element of the URL displayed in the web browser. We will be using these elements to establish connection between ExceltoCI and the CI. Go to the 'Connection Information' tab at the bottom of the ExceltoCI utility and paste copied elements as shown below:
Its quite clear with the highlighted colors that which element from URL needs to be copied where in the Connection Information page. There are couple of things I would like to point out here:
If the protocol is https then the HTTP Port would be 443 but if the protocol is http then HTTP Port will be 80. The field Action dictates whether a new row will be created through the component or an existing one will be updated similar to what we do in correct history mode.
Leave rest of the fields as shown above.
Template
This is the step where we select the CI through which the data is going to be uploaded. Click on the tab Template as below:
Enter the User ID, Password and the CI name (For example 'CI_JOB_DATA') as shown above and then click on OK. It will open the CI definition:
Our goal here is to select all the required fields from the component interface which are mandatory in order for the transaction to be saved. For example, if we are intending to add promotion rows for employees then we will be requiring EMPLID, EMPL_RCD, EFFDT, EFFSEQ, ACTION, ACTION_REASON, POSITION_NBR etc... The field selection here will be based on what are all the fields need to be filled in when promotion row is added through Job Data component in PIA. Highlight the field you want to select and then click on the option 'Select Input Cell' at the top. Do the same for all the fields and finally click on the option 'New Data Input' available on the top which will open the Data Input page.
All the selected fields have been displayed in this page and since they might be at different levels in the component hence they are shown in different colors as shown above.
Till now, we established connection, created template for the CI and selected the input fields. Now we are all set to fill in these selected fields with input data that we are planning to upload into the database.
Add all the input data rows in excel form and click on the option 'Stage Data for Submission' available on the top which will open the next page.
In this page you will also see errors and warnings generated by the underlying component. All the errors needs to be fixed before the data can be submitted further but the warnings can be ignored. Once all the errors are fixed then click on the option 'Submit' displayed on the top which will finally load the data into database.
Enter the User ID, Password and the CI name (For example 'CI_JOB_DATA') as shown above and then click on OK. It will open the CI definition:
Our goal here is to select all the required fields from the component interface which are mandatory in order for the transaction to be saved. For example, if we are intending to add promotion rows for employees then we will be requiring EMPLID, EMPL_RCD, EFFDT, EFFSEQ, ACTION, ACTION_REASON, POSITION_NBR etc... The field selection here will be based on what are all the fields need to be filled in when promotion row is added through Job Data component in PIA. Highlight the field you want to select and then click on the option 'Select Input Cell' at the top. Do the same for all the fields and finally click on the option 'New Data Input' available on the top which will open the Data Input page.
Data Input
All the selected fields have been displayed in this page and since they might be at different levels in the component hence they are shown in different colors as shown above.
Till now, we established connection, created template for the CI and selected the input fields. Now we are all set to fill in these selected fields with input data that we are planning to upload into the database.
Add all the input data rows in excel form and click on the option 'Stage Data for Submission' available on the top which will open the next page.
Staging and Submission
In this page you will also see errors and warnings generated by the underlying component. All the errors needs to be fixed before the data can be submitted further but the warnings can be ignored. Once all the errors are fixed then click on the option 'Submit' displayed on the top which will finally load the data into database.
You can gain in-depth knowledge on CI with a live example by just paying below amount
I have a complete session in two parts which explains:
- How CI works - Overview, all the elements of CI etc..
- Creating an Inbound interface, that loads data on a multi level component using CI and App Engine
CI Part 1
CI Part 2
Click here to know how it works
its nice document for all beginners thanks.
ReplyDeleteI have Level 0, in level 1 i have one field EFFDT and level 2 many fields.
Upto Level 1 am fine to load multiple data, Please tell me how to load data into level 2
Very Nice
ReplyDelete