Datamover Export and Import PeopleSoft

There are quite a few techniques available in PeopleSoft to export and import the table from the database, one of which is Datamover.
Data Mover Export
set log C:\EXPORT.log;                                                  — Place where log file is maintained
set output C:\QC146tabdmp.dat;                             — This is the place where the exported data is stored.
export FO_CSF_FIELD;                                                —  Table that needs to be exported.
export RS_ASSIGNMENT;
export RS_SO_HDR;
export CA_BI_DRIVER4;
Data Mover Import
set log C:\IMPORT.log;                                              — Place where log file is maintained
set input C:\QC146tabdmp.dat;                             — This is the place where the exported data is retrieved.
import FO_CSF_FIELD;                                             —  Table that needs to be imported from the .dat file.
import RS_ASSIGNMENT;
import RS_SO_HDR;
import CA_BI_DRIVER;
Note: PeopleSoft DataMover stores the exported file in as encrypted binary file and it cannot be reused in any other way, you can only use Datamover to export and import the dat file which the DataMover generates.
Advertisements

Accessing DOM objects in PeopleSoft pages

I was developing a custom tool  in PeopleSoft where I had to display a widget containing appraisal ratings, compensation details etc., for a particular employee. The widget itself was developed using HTML and Javascript. So I placed an htmlarea in my PeopleSoft page and poured in the widget code. The widget used a level-1 grid as data source.

Sample data in Grid

Appraisal Year Mid-Year Year-End
2007 2 2
2008 1 2
2009 3 2
2010 1 1

Before proceeeding any further, let’s have a peek into DOM. For people who are not familiar with Document Object Model (DOM), it is a cross-platform and language-independent convention for representing and interacting with objects in HTML, XHTML and XML documents. The Document Object Model is a great big hierarchy of objects and functions just waiting to be tickled by JavaScript. To identify the HTML tags behind objects in the PeopleSoft page, I used Firebug (Web Development Tool). Firebug made it simple to find HTML elements buried deep in the page.

Objects in a page can be accessed using:

    1. getElementById – This function is used to get the objects in page based on its id attribute.

For example, an Employee id field on PeopleSoft page might be represented in HTML as

<input id="“XX_APP_EMPLID”" class="PSEDITBOX" type="text" name="“Emplid”" value="“KU0007”" />

The value in “Emplid” field can be accessed by javascript as below. Note that the parameter to getElementById function has to be the value of “id” attribute.

document.getElementById("XX_APP_EMPLID”).value;
  1. getElementByTagName – This function is used to get the objects in page based on its tag name.
<input id="“XX_APP_EMPLID”" class="PSEDITBOX" type="text" name="“Emplid”" value="“KU0007”" />
document.getElementByTagName("input”).value;

getElementByTagName restricts you to access only one HTML element at a time. What if you want to iterate through all “input” tags in the page? getElementsByTagName (note the ‘s’ in Elements) functions allows you to do just that.

Now, how do I access data in a grid in PeopleSoft page?

I knew how to get values from my page fields using getElementById function, however when I tried to use the same function for reading data from my grid:

document.getElementById("GET_ROWS").onclick = function(){
alert(document.getElementById("XX_APP_RATING$3”).value);
}

With the code above, I intended to display the value of grid’s first cell (row 1, col 1). The first cell is identified by id “XX_APP_RATING$3”, which I discovered by inspecting the HTML source code using Firebug.

Instead of displaying the value the alert message returned an “undefined” value.

Cracking the Grid

All the data within the grid can be read as array elements in javascript using the getElementsByTagName function. So the onclick function is re-written as:

document.getElementById("GET_ROWS").onclick = function(){
var x = document.getElementById("XX_APP_ RATING$scroll$0").getElementsByTagName("tr");
// Note: Each row in the grid is organized in
tag of the grid object
 }

Now the variable x will contain all the rows from the grid XX_APP_DATA. To access each individual data from the grid rows,

for(var i=0;i     		// Read through each row
                    	var y=x[i].getElementsByTagName("span");
     		// Note: The  tag will contain all the individual cells in the grid
 }

Putting all the pieces together, the complete javascript code to iterate an entire grid is:

document.getElementById("GET_ROWS").onclick = function(){
var x = document.getElementById("XX_APP_ RATING$scroll$0").getElementsByTagName("tr");
  for(var i=0;i // Read through each row
              var y=x[i].getElementsByTagName("span");
// Note: The  tag will contain all the individual cells in the grid
 for(var j=0;j // Read each column in the ith row.
 var td_data = y[j].id;
 var td_data = td_data.replace("XX_APP_ RATING_","");
 var td_data = td_data.substr(0, td_data.indexOf("$"));
 alert(td_data + " = " + y[j].innerHTML);
 }
 }
}

Two truths, half-a-lie and lie

Recently, we have been quite busy working on a complex JD Edwards to PeopleSoft HR data conversion activity. The objective of conversion is to port all data from JD Edwards to PeopleSoft HR. Yes, you heard it – all HR data, some of which dates back to as early as 1952 🙂

Typically, ERP conversions start off with someone maintaining the source system fedex delivering the data extract. Consider yourselves lucky if your source system is not a hosted application, or you would be paying through your nose to get an extract of ‘your’ data! Extraction of data is rarely in the scope of an ERP conversion. I’ve seen project charters that list extraction as the first and last points under the Out-of-Scope section!

What made this undertaking rare and special for us is that we chose to extract, cleanup, transform and convert data into PeopleSoft. Yep, extraction and cleanup was on the house 🙂

Now that we stand close to handing over converted data for UAT, the whole experience was yet another re-affirmation of a few truths/lies about data conversion.

  • Truth – Quality of source data is a primordial determinant of how simple or how complex the conversion is going to be.
  • Truth –  Involve business owners very early and work out the validation strategy. Make them responsible for validation.
  • Half a lie –  Its sufficient for the project team to be masters of target system. Just a little awareness of the source system is sufficient.
  • Lie – Focus on data conversion during the initial stages and forget about it.

Let me talk about quality of source data. The saying “Garbage In, Garbage Out” may apply in everything related to computers, but never ever when converting data. In that sense, data conversion is a green endeavor, involving a lot of recycling.  Over in this world, when its “Garbage In”, you got to recycle and let “Clean data out”.

In PeopleSoft, there are strict rules around allowing hire, terminate and rehire transactions. You would not be able to rehire an employee until they are terminated. Likewise, you cannot terminate an employee who is already terminated. None of these rules (and many more) were enforced in source system, which led to poor quality of data from a PeopleSoft perspective. We had to come up with massive cleanup algorithms to make source data PeopleSoft friendly.

In our case, source data came from three primary sources, one of which was an audit table. That was enough reason to give some sleepless night during the design phase. The audit table presented us with a major “quality” challenge.  A business transaction, like hiring an employee, promoting an employee etc., results in many rows in the audit table, each row capturing data change in one data element. For example, if promoting an employee requires  the user change department, jobcode, promotion date and compensation rate, the audit table will have four extra rows, one for each data element. We decided to perform a row-to-column transformation of the entire audit table. That was the first step in our progression towards building PS-friendly data.

Devising the strategy to cleanup, transform and convert data is only half the battle. The other half was in building the validation strategy, which involved placing the right kind of checks and balances during and after conversion. Alarms should go off when an employee looks active in PeopleSoft but terminated in the source system. Without comprehensive queries and reports that juxtapose raw and converted data, there’s no proof in the pudding.

Over to “half a lie” – “Just a little awareness of the source system is sufficient”. Its only half true, because as I mentioned earlier, most ERP conversions get the source data fedex-delivered. So, its more than sufficient to put together a mapping document and proceed with the build. In our case, we had to be conversant with the source system and know its nitty-gritties, to achieve the goal.

Over to the lie – “Focus on data conversion during the initial stages and forget about it”. Nothing could be farther from truth. In an implementation, midway scope creeps and changes in business processes always warrant a change in the way data is converted. As users get to know their way around the new system, their insights will have an impact on conversion. In short, effort and cost estimation for conversion activities have to consider such scenarios, else budget revisions become indispensable.