PeopleSoft Data Conversion Process

When converting data from a legacy system to PeopleSoft, we use a three-step approach: Legacy Evaluation, Data Mapping and Conversion.

Our PeopleSoft data conversion process is proven. Organizations benefit from our approach, which offers technical proficiency and minimal internal resources.

Data conversion is an iterative process. Often you will find that data did not convert properly and you will then need to investigate the reason, correct the data map and rerun the process. A detail to accuracy and patience are key factors when converting data.

PeopleSoft Installation:-

1. Initial planning of the installation which involves defining the servers, including the file server, database server, application and batch servers and the web server.
2. Identify the best infrastructure layout and we advise on load balancing of both the middle tier (application server) and the web server.
3. Understand the pitfalls and bottle necks that can be avoided as related to performance and capacity.

A Typical PeopleSoft Installation Involves The Following:

• PeopleTools
• PeopleSoft Enterprise products, referred to as PeopleSoft Tuxedo for application server and process scheduler
• Web server products like Web Logic, Web sphere or Oracle application servers
• Pure Internet Architecture
• Change Assistant
• Change Impact Analyzer

PeopleSoft Customization:-

PeopleSoft is a robust application sometimes the system needs to be enhanced to meet specific business needs or to adapt to the way you do business.  Make your PeopleSoft application function in a way that best suits your business needs.

The most common global implementation projects adopt one of the two deployment models.

PeopleSoft Integration:-

Using PeopleSoft’s Integration Broker provides the ability to produce and consume industry standard Web services and is comprised of four key elements: Packaged Connectors, Intelligent Routing, Transformation and a Development and Monitoring Environment.

We also provide expertise in the following additional PeopleTools Technologies:

• Application Messaging
• Component Interface
• File Interfaces

If your integration needs are unique we have the experience and expertise to develop a completely custom process for your integration using PSQuery, SQR or Application Engine.

3 Data Conversion Strategy Options for Oracle Fusion Data Loading

1. SQR. In PeopleSoft, an SQR (or a series of them) could be built to extract and write the data to FBL files.

This approach offers a lot of flexibility in how the data is extracted and formatted, which may be necessary for organizations planning to convert a large amount of complex data, and especially if historical data is to be included. (For the record, I recommend minimizing converting historical data as much as possible.) SQR also allows for the ability to create the correct type of files, minimizing the amount of manual manipulation that would need to occur. One downside to the SQR approach is the overhead involved in creating one or more new programs that need to be maintained and migrated across various code trees. Keep in mind that you will likely make frequent modifications to the SQRs as you are testing, troubleshooting and running them ad-hoc, often for variously filtered data sets. If the complexity level of your data conversion effort falls on the lower end, you may want to consider a simpler approach.

2. Queries. The PeopleSoft Query tool can extract your data as well.

With this approach, you would need to create a series of queries (one or more per data file) and then download the results to Excel. The advantages are that (a) queries are easy to maintain, (b) are self-contained within PeopleSoft, and (c) can be written by either technical or functional analysts. The disadvantages are that (a) you would have to perform some manual file manipulation to download the query results to Excel and then convert them to data files for FBL, and (b) Query isn't as powerful as SQR and may not be able to extract all the data you need in the way you need it, especially if your data is complex and includes history.

If you don’t think Query can accommodate your organization’s conversion effort and you don’t want to create SQRs.

3. SQL Scripts. A series of scripts at least one per data file could be written to retrieve your data and format it appropriately.

Some manual file manipulation will be required with this approach as well, but it offers greater flexibility than Query because you can write the SQL yourself, allowing for more complex data extractions. Also, you could simplify your scripts by creating PeopleSoft views to handle the main part of the selects and then write your scripts to pull from those views and apply whatever filters you may need on the fly in the scripts. This allows you to quickly create files for any subset of data you may need at any given time. A client I'm currently working with used this approach and it served them well. So, these options to help you to think about how the best approach your data conversion efforts. Every organization is different. No matter which option you choose whether it’s one of these three, some combination of them, or something else entirely you will greatly improve your chances for a successful conversion if you plan ahead and then execute a structured approach to extracting your data to files compatible with FBL. And by extension, as you minimize potential risk and reduce time spent on troubleshooting conversion issues when it could be better spent on other areas (such as configuration and writing interfaces), you will also greatly improve your chances for a successful Fusion implementation overall.

Performing Data Conversion during PeopleSoft Upgrades

People that do not understand the complexity of an upgrade typically consider an upgrade to be nothing more than running some scripts. In fact, once the Upgrade Assistant came on the scene, the stereotype became more evident: if an upgrade is simply running scripts, then the Upgrade Assistant must allow you to just push a few buttons and the upgrade will automatically complete by itself! Nothing could be further from the truth. An upgrade is a complex project that involves nearly every technical PeopleSoft area, along with involvement from functional team members, middle management, and executive management sponsorship. So, for such a complex project to be successfully executed, a methodology must be followed. PeopleSoft has devised such a methodology. First and foremost, check for New Updates. Check the Customer Connection website again for any new Required For Upgrade patches, especially those related to data conversion. If any are found, apply them now, especially if this is the initial upgrade pass. However, for test/final move to production passes, be careful; it is normally not recommended to apply updates past the initial upgrade pass, as it requires further round(s) of testing. Decide how data conversion will be executed. Decide which method will be used to execute the data conversion scripts: 1) Locally on client computer, using Upgrade Assistant (UA) 2) Configure Upgrade Assistant to execute data conversion on server 3) Create scripts to run data conversion, then execute directly on the server Running data conversion locally thru Upgrade Assistant (UA) should only be used if (a) your PeopleSoft database is small & performance is top-notch; AND (b) you are unable to successfully configure UA to execute data conversion on the server. Running data conversion thru UA, but configure UA to execute data conversion directly on the server is an option. The good side is that performance is better since data conversion is executed directly on the server; however, UA does not have a successful history of properly executing the scripts. Most times, it is quicker and you have better control if you write the scripts yourself. Running data conversion directly on the server by executing scripts you have created is an overall good option, especially if the client doesn’t require you to use UA exclusively. This is the option presented here; therefore, ignore any tasks in the Upgrade Guide which specify how to configure data conversion to execute on the server through UA. If executing data conversion App Engine programs on the server, the application server box must be properly configured as follows: The application server domain must be properly configured and must be running The environment must be set to properly recognize execution of App Engine programs, such as PS_SERVER_DIR, etc (see PeopleSoft Installation Guide for current PeopleTools version) A good idea, although not a requirement, is to follow the Upgrade Guide instructions for building the component interface. Perform pre-data conversion backup. Make sure that you perform a backup before running data conversion. That way, if unrecoverable errors occur during data conversion, the database can be reverted to this point in time. Assuming you decided to write the data conversion scripts, then in most cases some of the data conversion scripts may be run concurrently. Refer to the Upgrade Guide for specific instructions as to what each data conversion program actually does, but typically the first one is run as a pre-requisite to the others; therefore, it cannot run concurrently with others. However, usually all the other data conversion programs, other than the last one, can be run concurrently. Like the first one, the last one typically must be run consecutively. Keep in mind that during the initial pass, data conversion may fail. This could happen either because PeopleSoft delivered records has been customized by the customer or duplicate data in the tables cause an error with new/changed indexes. Also, if the customer has non-PeopleSoft (i.e. “Bolt-On”) records, you may want to assist them in creating an Application Engine library so the data conversion upgrade driver programs will pick them up as well. Using the Application Designer “Find In” option can help determine which Application Engine programs affect customized records. Typically, specific instructions on this can be found in the Upgrade Guide for your upgrade path. If you would like to learn the step by step approach and methods that will teach you all about Performing a Successful Upgrade you can download the eBook A step-by-step guide to performing successful upgrades

How to design a file based inbound interface or data conversion process in PeopleSoft?

In our professional life as PeopleSoft consultant, many a time we come across requirement for a data conversion process or an inbound interface where we need to read an input file and then load the data into one or more PeopleSoft components. 

In this blog we list the steps of a very simple but working design approach. 

The requirement based on which this design is illustrated is as follows

• Develop a run control page with file attachment functionality, so that end user can attach the input CSV file before running the Inbound Interface
•  Develop an Application Engine Program to insert or update data to the department component using component interface.
•  The input file can consists of the following data

1. New department information
2. New effective dated row for an already existing department
3. Changed values for non-key fields for an existing department and effective date combination

Please note in real world for Department component we may not even write an upload process and can make use of Excel to CI.

 

1.	Create a custom run control page with file attachment functionality (custom run control record, page, component & menu), so that user can attach a file and system physically stores the file to a pre-defined location.
2	Develop an application engine program that performs the following steps.
	·         Delete data from custom staging (STG) table. The structure of this SQL Table will be similar to the input file with additional fields like PROCESS, ERROR_FLG and ERROR_NOS ·         Select the attached file from custom run control record and store that to state record ·         Load the data from CSV file to STG table, and update the PROCESS field of the STG table to “N” and ERROR_FLG to "N" ·         Perform SQL Data validation (mostly referential integrity validations) in the Staging table based on the requirement. Mark the rows which have errors by updating the ERROR_FLG to "Y". ·         Read the rows of data from the staging table where ERROR_FLG="N", and execute the CI and insert data to PeopleSoft component. Mark the PROCESS field value for the successfully processed row to "Y". In case of CI errors, mark the ERROR_FLG to "Y" for those rows in the STG table. ·         Generate Reports by reading the data stored in the Staging table where ERROR_FLG = "Y"

 

PeopleSoft Data Conversion Requirements Gathering Checklist

Based on your requirement and design you can pick the right questions that is more appropriate to complete your specifications document.

Checklist Items
1	Detail Description of the data conversion
2	Description of the source and Target systems involved
3	Technology to develop this interface (if there is an preference) - AE/SQR/ ETL/ExcelToCI/CI
4	Sample conversion data file
5	PeopleSoft Modules that are impacted
Data Mapping
6	Mapping between source system table or File to PeopleSoft Tables
7	Source System data attributes ( Column type, length, format etc) for each column
8	Default values for the required columns (if any) when no value is provided
9	Required Columns list (Rules should state what has to be done if any required column is missing)
10	Mapping rules for the columns that needs transformation
11	List of PeopleSoft Tables involved in conversion
12	Information on PeopleSoft Component and Pages involved (if needed)
13	Error/Exception handling strategy
Business Processing Logic
14	Whether data that will be provided for conversion will be raw or processed data? If raw require processing logic for converting this raw data
15	If data for conversion has to selected by program then require information on selection criteria from the source system.
16	Any Business specific execution logic's that has to be included in the conversion program
17	PreLoad business validations
18	PostLoad business validations
19	Transformation Logic's
Conversion Data
20	Whether history data is converted ? If so how old history data ?
21	Data mapping that has to be used for converting history data (if any)
22	Whether there is any Prerequisite conversion or PS data setup needed for this conversion? If yes whether those information's are available in the document ?
23	How conversion data will be sent to PeopleSoft ? (File, Database Link etc)
24	Any setup data that has to be configured for this conversion
25	Whether this conversion has dependency on online customization ? If so require information on the conversion approach based on this customization
26	Whether Conversion data will be received in single file or Multiple files ? If multiple file order in which files has to be loaded.
27	Order in which PeopleSoft Tables has to be loaded
28	Input file name format
29	Input file layout and file delimiter
30	Expected volume of data that has to be migrated
31	Expected time for the process to load the data

 

Sample PeopleCode for Data Conversion

 Local File &MYFILE, &MYFILE1;

Local Record &REC;

Local array of string &ARRAY;

Local string &FILE_DIRECTORY, &FileName, &FileNameErr;

Local integer &COUNT, &I, &nbrSlash, &nbrSlash1, &nbrDoubleQuot, &nbrSpace;

Local string &strDate, &strYear, &strMonth, &strDay, &strTime, &strAMPM, &strDateTime, &strEmplid;;

Local string &strDayYear, &strText, &strText1, &strText2, &strDoubleQuot1, &strDoubleQuot2;

Local string &strFilePath, &strFileName, &strRecName;

&strFilePath = Record.PATHNAME.Value;

&strFileName = Record.FILENAME.Value;

&strRecName = RECORDNAME.Value;

&FileName = &strFilePath | "/" | &strFileName;

&FileNameErr = &strFilePath | "/" | "Error.log";

 

&MYFILE = GetFile(&FileName, "R", %FilePath_Absolute);

&MYFILE1 = GetFile(&FileNameErr, "W", %FilePath_Absolute);

rem &REC = CreateRecord(Record.RecordName);

&REC = CreateRecord(@("Record." | &strRecName));

&ARRAY = CreateArrayRept("", 0);

 

&COUNT = 0;

If &MYFILE.IsOpen Then

   While &MYFILE.ReadLine(&STRING);

      If &COUNT <> 0 Then

         rem &ARRAY = Split(&STRING, Char(9));  /**For Tab separated***/

         &ARRAY = Split(&STRING, ",");  /*** for Comma Seperated***/

         For &I = 1 To &REC.FieldCount

            &MYFILE1.WriteLine("RowNum: " | &COUNT | "  " | RTrim(LTrim(&ARRAY [&I])) | " Field Type:" | &REC.GetField(&I).Type | " Field Name:" | &REC.GetField(&I).Name);

            If &REC.GetField(&I).Type = "DATE" Then

               &strDate = (RTrim(LTrim(&ARRAY [&I])));

               &nbrSlash = Find("/", &strDate);

               If &nbrSlash > 0 Then

                  &strMonth = Substring(&strDate, 1, &nbrSlash - 1);

                  If Len(&strMonth) = 1 Then

                     &strMonth = "0" | &strMonth;

                  End-If;

                  &strDayYear = Substring(&strDate, &nbrSlash + 1, Len(&strDate));

                  &nbrSlash1 = Find("/", &strDayYear);

                  &strDay = Substring(&strDayYear, 1, &nbrSlash1 - 1);

                  If Len(&strDay) = 1 Then

                     &strDay = "0" | &strDay;

                  End-If;

                  &strYear = Substring(&strDayYear, &nbrSlash1 + 1, Len(&strDayYear));

                  If Len(LTrim(RTrim(&strYear, " "), " ")) = 2 Then;

                     If Value(&strYear) > 40 Then

                        &strYear = "19" | &strYear

                     Else

                        &strYear = "20" | &strYear

                     End-If;

                  End-If;

                  rem    &MYFILE1.WriteLine("RowNum: " | &COUNT | " &strDate:" | &strDate | "  &strDayYear:" | &strDayYear | " &strMonth:" | &strMonth | " &strDay:" | &strDay | " &strYear:" | &strYear);

                  &REC.GetField(&I).Value = Date3(Value(&strYear), Value(&strMonth), Value(&strDay));

               Else

                  If RTrim(LTrim(&ARRAY [&I])) = "" Then

                     &REC.GetField(&I).Value = Date(0);

                  Else

                     &REC.GetField(&I).Value = Date(RTrim(LTrim(&ARRAY [&I])));

                  End-If;

               End-If;

            Else

               If &REC.GetField(&I).Type = "DATETIME" Then

                  &strDate = (RTrim(LTrim(&ARRAY [&I])));

                  &nbrSlash = Find("/", &strDate);

                  If &nbrSlash > 0 Then

                     &strMonth = Substring(&strDate, 1, &nbrSlash - 1);

                     If Len(&strMonth) = 1 Then

                        &strMonth = "0" | &strMonth;

                     End-If;

                     &strDayYear = Substring(&strDate, &nbrSlash + 1, Len(&strDate));

                     &nbrSlash1 = Find("/", &strDayYear);

                     &strDay = Substring(&strDayYear, 1, &nbrSlash1 - 1);

                     If Len(&strDay) = 1 Then

                        &strDay = "0" | &strDay;

                     End-If;

                     &nbrSpace = Find(" ", LTrim(RTrim(&strDayYear, " "), " "));

                     &strYear = Substring(&strDayYear, &nbrSlash1 + 1, &nbrSpace - 3);

                     If Len(LTrim(RTrim(&strYear, " "), " ")) = 2 Then;

                        If Value(&strYear) > 40 Then

                           &strYear = "19" | &strYear;

                        Else

                           &strYear = "20" | &strYear;

                        End-If;

                     End-If;

                    

                     &strTime = LTrim(RTrim(Substring(&strDayYear, &nbrSpace + 1, Len(&strDayYear)), " "), " ");

                     &strAMPM = Substring(&strTime, 9, 10);

                     &strTime = Substring(&strTime, 1, 8);

                     rem &strDateTime = LTrim(RTrim(&strYear, " "), " ") | "-" | &strMonth | "-" | &strDay | " " | LTrim(RTrim(&strTime, " "), " ");

                     &strDateTime = &strMonth | "/" | &strDay | "/" | &strYear | " " | &strTime | " " | &strAMPM;

                     rem   &MYFILE1.WriteLine("RowNum: " | &COUNT | " &strDate:" | &strDate | "  &strDayYear:" | &strDayYear | " &strMonth:" | &strMonth | " &strDay:" | &strDay | " &strYear:" | &strYear | " &strTime:" | &strTime | " &strAMPM:" | &strAMPM | " &strDateTime:" | &strDateTime);

                     &REC.GetField(&I).Value = DateTimeValue(LTrim(RTrim(&strDateTime, " "), " "));

                  Else

                     &REC.GetField(&I).Value = Date(RTrim(LTrim(&ARRAY [&I])));

                  End-If;

               Else

                  If &REC.GetField(&I).Type = "CHAR" Or

                        &REC.GetField(&I).Type = "LONGCHAR" Then

                     &strText = RTrim(LTrim(&ARRAY [&I]));

                     If LTrim(RTrim(&REC.GetField(&I).Name, " "), " ") = "EMPLID" Then

                        &strEmplid = Rept("0", 9 - Len(&strText)) | &strText;

                        &REC.GetField(&I).Value = &strEmplid;

                     Else

                        If &strText <> "" Then

                           &REC.GetField(&I).Value = &strText;

                        Else

                           &REC.GetField(&I).Value = " ";

                        End-If;

                     End-If;

                  Else

                     If &REC.GetField(&I).Type = "NUMBER" Or

                           &REC.GetField(&I).Type = "SIGNEDNUMBER" Then

                        &strText = RTrim(LTrim(&ARRAY [&I]));

                        If &strText <> "" Then

                           &REC.GetField(&I).Value = Value(&strText);

                        Else

                           &REC.GetField(&I).Value = 0;

                        End-If;

                     End-If;

                  End-If;

               End-If;

            End-If;

         End-For;

         &REC.Insert();

      End-If;

      &COUNT = &COUNT + 1;

   End-While;

Else

End-If;

 

PeopleCode to Import Data into Record from Multiple .txt files.

Local File &FILE1;

Local Record &REC1;

Local SQL &SQL1;

Local Rowset &RS1, &RS2;

Local string  &path;

 

Local array of string &ARRAY;

Local string &STRING;

 

Local array of string &aFileNames = CreateArrayRept(“”””, 0);

Local File &fSourceFile;

&path = “D:\InboundFiles\*.txt”;

 

&aFileNames = FindFiles(&path, %FilePath_Absolute);

 

MessageBox(0, “”, 0, 0, “&aFileNames.Len:” | &aFileNames.Len);

While &aFileNames.Len > 0

 

&fSourceFile = GetFile(&aFileNames.Shift(), “R”, %FilePath_Absolute);

 

&REC = CreateRecord(Record.SY_SYSTEM_INFO);

&ARRAY = CreateArrayRept(“”, 0);

 

If &fSourceFile.IsOpen Then

 

If &fSourceFile.SetFileLayout(FileLayout.SY_SYSTEM_INFO_FLO) Then

While &fSourceFile.ReadLine(&STRING);

&ARRAY = Split(&STRING, “;”);

For &I = 1 To &REC.FieldCount

&REC.GetField(&I).Value = &ARRAY [&I];

End-For;

/* do additional processing here for converting values */

&REC.Insert();

End-While;

Else

/* filelayout not correct */

End-If;

Else

/* file not open */

End-If;

 

&fSourceFile.Close();

 

End-While;

PeopleSoft - Navigation Collections

 Navigation Collections means collection of content references (menu items) and content reference folders in one centralized location.

Navigating: Main Menu> People Tools > Portal > Portal Utilities > Navigation Collections.

Add collection as shown below.

Here, Provide your Navigation Collection Name and Description.

Next, Click on “Add Link” Button in “Override Default Options” Section to add menu item links to the Navigation Collection.

Next, Provide your Menu Item as shown below.

In this Example am going to add “Structure and Content” menu item to this navigation Collection.

And add one more link “Navigation Collections” to this Navigation Collection by clicking on “Add Link” button.

Next,for this Navigation collection itself am going to add one more folder “Pagelet Wizard” by clicking on “add a folder” button in “Navigation Collections” main page.

In the below page you have to provide your folder details like folder type, source portal and menu folder.

In this example added menu folder as “Pagelet Wizard” then click on “OK” button.

Next, save the component.