Peoplesoft Data Migration to Fusion HCM

Peoplesoft Extraction process:

Peoplesoft Extraction AE process includes below 6 sections, it may be more also based on how you are processing data.

Populate state record
Flush staging records
Call Init section (or Dynamic call sections for Full/incremental)

Clean the snapshot record until we are maintaining history.
SQls to extract full data for the population selected from JOB data.
Eg: If it is salary extraction then all the salary record will be join with JOB recod to fetch the population and put the cutoff date check with effective date in JOB data.
Modify the data in staging/ create end dates based on HDL file you are creating.
Once all the data is extracted in temp records and modifications are done then load the staging record with all the fields which you need in HDL file, hardcode the values in fields wherever required.
Do the same extract as in FULLL section for the base population

Do the same extract as in FULLL section for the base population
Once all the data is modified in temp record then populate the snapshot record by comparing it with
Snapshot record with the Temp record data (full data till date). This compare should give you only changed data which is not already in snapshot record or any changes which are not already updated in Snapshot record.
Once staging data is populated then next step is to set the flag for the rows in the staging record (which rows to insert in snapshot record or which rows to update).
You can also set the flags for data validation that you can use while writing log files.

In this section you can use file layout and generate the HDL file. Why we are recommended to use the File layout as it will validate the data for the correct format of field values.

This piece of code will write all the staging record data into file as we have mapped the staging record field in file layout.

In this section we can insert/update the data into snapshot table.
Snapshot table should be updated with the new or latest data which is already sent to fusion system, so that next incremental run we can compare with what we have in fusion and what is the change data coming in Peoplesoft. While compare in incremental load section we will have latest data available in snapshot table.
In this section we can insert all the data into snapshot record if it is run for Full extract, as in full extract we are going with one off data load.
If it is run for incremental load mode then we can insert/update data based on the flag we set in staging table.

Integration section we are using to generate the zip file and to run the automation script to transfer data form HDL file to fusion system.

In above code first we are zipping the extracted file and then we are executing the script to run the fusion load process.
The automation script will pick the generated file from Peoplesoft server where we generated, and transfer the file to UCM server from where it will go to fusion loader.

Steps to Setup A Record Level Audit in PeopleSoft

Steps to Setup A Record Level Audit in PeopleSoft
As PeopleSoft maintains critical enterprise data, having an audit trail on critical data is of paramount importance. This post will help you setup Record Level Auditing in 7 simple steps.
Record level Auditing
As the name suggests, record level auditing is the auditing mechanism enforced on a Record. To set up record level auditing, follow the steps below.
1. Choose the Record
Not all records in PeopleSoft need auditing. When there is such a business requirement, choose the record(s) that needs to be audited.
2. Create the Audit Record
The Audit record is essentially a copy of the record being audited. The easiest way to create a copy of your record is to do a Save As. Name the new record under standard record naming convention prefixed with AUDIT_
3. Remove the unwanted
An Audit record doesn’t need keys, so remove them. The same rule goes with Related Language Records, Query security records and PeopleCode. Also, remove all other fields that you are not interested in. Just remove them all.
4. Add Audit Fields
Insert the below fields (also known as audit fields) at the top of the new record in the below order
AUDIT_OPRID (Captures the User who performed an action)
AUDIT_STAMP (Captures the date and time when the action was performed)
AUDIT_ACTN (Captures the type of action performed)
AUDIT_RECNAME (Captures the name of the audited record definition. This field is used only when the same audit record is used for auditing multiple records)
Audit Fields
Audit Fields
On the Record Field Properties for AUDIT_STAMP, we need to check the Auto-Update checkbox. This is essential to correctly populate the date-time stamp.
Auto Update property
Auto Update property
AUDIT_ACTN can take up any of the below values based on the type of the action performed.
A – Row inserted
D – Row deleted.
C – Row changed (updated), but no key fields changed.
K – Row changed (updated), and at least one key field changed.
N – Row changed (updated), and at least one key field changed.
In case of C and K the system writes old values to the audit table. While for N, the new values are written into the audit table.
5. Build the audit record
Your Audit record must be a Database record and you should be able to access it using Query.
6. Link them up
Open the Base Record that is to be audited. Open its properties. On the Use tab, under the Record Audit, specify the name of the audit record that we just created as the Record Name. Choose Audit Options based on your requirement.
Audit Record Mapping
Audit Record Mapping
Add – An Audit table row is inserted when a row is inserted in the base record
Delete – An Audit table row is inserted when a row is deleted from the base record
Change – Audit table row(s) is inserted when a row changes in the base record
Selective – Audit table row(s) is inserted when common field (present in both base record and audit record) changes
7. Test the Auditing
Based on the Audit option that you have chosen, perform some transaction that can trigger the audit. Now query the audit record and check for new rows. Try to infer the meaning of each row that got added.
A delivered page to test this is the Department page. Navigate to Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Department. Add a new value.
Examine the audit rows.

That’s it on Audit Records in PeopleSoft!

SendMail – Sending Email in Peoplecode

---SendMail – Sending Email through peoplecode send mail function

Local string &NL = "<br>"; /* The new line character in html */
/* Setting the parameters for sendmail */
&MAIL_TO = "";
/* &MAIL_CC = "";
&MAIL_BCC = ""; */
&MAIL_SUBJECT = "Test Email";
/* Getting page field values */
&f_name = R9_EMP_NAME.FNAME.Value;
&l_name = R9_EMP_NAME.LNAME.Value;
/* Making the email body */
&MAIL_GREET = "<font face ='Arial'>Dear " | &f_name | " " | &l_name | "," | &NL | &NL;
&MAIL_TEXT1 = "This is a test email that contains an HTML table." | &NL |  &NL;
&MAIL_TEXT2 = "<table border='1'><tr bgcolor = '#AAAAAA' ><th>Name</th><th>State</th><th>Country</th></tr><tr><td>Rojer Alex</td><td>VA</td><td>USA</td></tr></table>" | &NL;
&TEXT1 = "Your footer line(s) goes here." | &NL | &NL;
&TEXT2 = "Regards," | &NL | "The PSoftSearch team";
&MAIL_FOOTER = &TEXT1 | &TEXT2 | "</font>";
/* Sending the email */
&RET_CODE = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT_BODY, &MAIL_FILES, &MAIL_TITLES, "", ";", "Content-type: text/html; charset=utf8");
/* Evaluating the return value of sendmail and showing the appropriate message */
If (&RET_CODE = 0) Then
MessageBox(0, "Alert", 0, 0, "Email has been sent.");
MessageBox(0, "Alert", 0, 0, "Unable to send email.");

Sending Mails using Generic Templates

Send Mail using Generic Templates

import PT_WF_NOTIFICATION:NotificationAddress;
import PT_WF_NOTIFICATION:Notification;
import PT_WF_NOTIFICATION:NotificationTemplate;
import PT_MCF_MAIL:*;
import PT_MCF_MAIL:MCFOutboundEmail;
import PT_MCF_MAIL:MCFEmail;

Local array of string &aryValues;
Local array of PT_WF_NOTIFICATION:NotificationAddress &mynotifyto;
Local PT_WF_NOTIFICATION:NotificationAddress &mynotifyaddress;
Local PT_WF_NOTIFICATION:Notification &mynotification;
Local string &emailid, &Template_Name;

/* Email Applicant START*/
&mynotifyto = CreateArrayRept(&mynotifyaddress, 0);
&mynotifyaddress = create PT_WF_NOTIFICATION:NotificationAddress("", "", "", &emailid, "Email");
If All(&emailid) Then
   &Template_Name = "N_STDNT_AWARD";
   &mynotifytemplate = create PT_WF_NOTIFICATION:NotificationTemplate("", "", &Template_Name, "G");
   /* Populate an array to contain the values needed by the template */
   &aryValues = CreateArrayRept("", 0);
   &xmlVars = &mynotifytemplate.SetupGenericVars(&aryValues);
   &mynotifytemplate.GetAndExpandTemplate(%Language, &xmlVars);
   &mynotification = create PT_WF_NOTIFICATION:Notification(" ", %Date + %PerfTime, %Language);
   &mynotification.NotifyTo = &mynotifyto;
   &mynotification.ContentType = "Content-type: text/html; charset=US-ASCII";
   &mynotification.Subject = &mynotifytemplate.Subject;
   &mynotification.Message = &mynotifytemplate.Text;

Working with flat files in peoplesoft

Working with flat files in peoplesoft

1) Using Filelayout
2) Using Array

Using Array:

Declare Function ClearData PeopleCode N_SR_RUNCNTL.N_SELECTION_TYPE FieldFormula;

Declare Function add_attachment PeopleCode N_SR_ATTACH_WRK.ATTACHADD FieldChange;
Declare Function display_attachment_buttons PeopleCode N_SR_ATTACH_WRK.ATTACHADD RowInit;
Local number &CurRow, &RETCODE;
Local string &Guid, &URL_ID;
Local Rowset &RS1, &Rs_Std;
Local Rowset &Level1;

rem local record &RS1;

/* Call the correct database record where your file attachment  */
/*    will be stored 

N_MOODLE_INTFC_R2_ATTACH                                           */
&URL_ID = "URL.N_MOODLE_INTFC_R2_ATTACH"; /************************ */
/* In order to make sure the attachmentname is unique           */
/*     you can add a GUID in front of your File name            */
/*************************************************************** */

&Level1 = GetLevel0()(1).GetRowset(Scroll.N_NINT09_R2_TMP);

add_attachment(@&URL_ID, "", &Guid, 0, True, "Record.N_NINT09_R2_RC", N_NINT09_R2_RC.ATTACHSYSFILENAME, N_NINT09_R2_RC.ATTACHUSERFILE, 2, &RETCODE);
If &RETCODE = %Attachment_Success Then
   rem display_attachment_buttons("Success");
   &filepath = GetCwd();
   &InputfileName = N_NINT09_R2_RC.ATTACHSYSFILENAME.Value;
   &M = GetAttachment(URL.N_MOODLE_INTFC_R2_ATTACH, &InputfileName, &filepath | &InputfileName);
   &N_File_Input = GetFile(&filepath | &InputfileName, "r", "a", %FilePath_Absolute);
   rem &RS1 = CreateRecord(RECORD.N_NINT09_R2_TMP);
   &num = 0;
   &RS1 = &N_File_Input.CreateRowset();
   &RS1 = &N_File_Input.ReadRowset();
   &RS1 = &N_File_Input.ReadRowset();
   rem ClearData();
   /*************************START READING FROM INPUT FILE**************************************/
   While &RS1 <> Null;
      /*****************************INSERT INTO TEMPORARY RECORD*********************************/
      &Row = &Level1.ActiveRowCount;
      rem MessageBox(0, "", 0, 0, "Row :" | &Row);
      rem MessageBox(0, "", 0, 0, "Runc Cntl :" | N_NINT09_R2_RC.RUN_CNTL_ID.Value);
      rem MessageBox(0, "", 0, 0, "Emplid :" | &RS1(1).GetRecord(1).EMPLID.Value);
      &Level1(&Row + 1).N_NINT09_R2_TMP.OPRID.Value = %OperatorId;
      &Level1(&Row + 1).N_NINT09_R2_TMP.RUN_CNTL_ID.Value = N_NINT09_R2_RC.RUN_CNTL_ID.Value;
      &Level1(&Row + 1).N_NINT09_R2_TMP.EMPLID.Value = &RS1(1).GetRecord(1).EMPLID.Value;
      &Level1(&Row + 1).N_NINT09_R2_TMP.STRM.Value = &RS1(1).GetRecord(1).STRM.Value;
      &Level1(&Row + 1).N_NINT09_R2_TMP.CLASS_NBR.Value = &RS1(1).GetRecord(1).CLASS_NBR.Value;
      &Level1(&Row + 1).N_NINT09_R2_TMP.CRSE_GRADE_INPUT.Value = &RS1(1).GetRecord(1).CRSE_GRADE_INPUT.Value;
      &RS1 = &N_File_Input.ReadRowset();
   End-While; /*Read Lines inside a file*/
   For &I = &Level1.ActiveRowCount To 1 Step - 1
      If None(&Level1.GetRow(&I).N_NINT09_R2_TMP.OPRID.Value) Then

Step by step setting up Elements of Academic Structure

Step by step setting up Elements of Academic Structure 

1.    Overview of Academic Structure

Academic structure is the foundation of the PeopleSoft Student Administration modules and their functions. It encompasses the institution’s schools and colleges, academic programs and departments, and subject areas. The Academic Structure serves as the basis for the course catalog and schedule, grading scheme, and all related tables that must be created prior to implementation.

The academic structure and its elements are the building blocks for an academic institution. This diagram illustrates the institution’s academic structure at high level.

Figure 1: Academic Structure Elements

An academic institution can have many campuses.

Academic programs are part of academic careers and the academic institution.

Subject areas are part of academic organizations and the academic institution.

Academic plans and academic sub-plans are subdivisions of academic programs.

Degree records are directly linked to academic plans.

Courses and classes are subdivisions of subject areas and directly linked to terms and sessions.

2.    Basic Elements of Academic Structure

 Academic Institution: Institution is a highest level of the academic structure. Institutions are assigned by SetId. SetId allows the sharing of common codes, structures, and facilities between colleges.

Academic Group:The academic group is the college or school with in the university. These groups are attached to the catalog.

 Academic Career: This is a level of study in university or institution like Undergraduate, Graduate etc. This also refers to a grouping of all academic work undertaken by a student that is grouped into a single record (Transcript).

 Academic Programs: An academic entity to which the student applies to., is admitted into, and ultimately graduates from. Programs offer plans and sub-plans.

 Academic Plan: Plans refers to an area of study within an academic program. The academic plan is a formal course of study that a student pursues in order to receive a degree.

 Academic Sub-Plans: Sub-plans refers to specializations or concentrations that are directly linked to the academic plan. Example: A student may be enrolled in the classical studies plan with a sub-plan of Greek and Latin.

Academic Structure Elements
Sample Values
Academic Institution
University of Alberta
Academic Group
Arts & Science
Academic Career
Academic Program
UGENG: English
Academic Plan
BA-CLAS: Classical Studies
Academic Sub-Plan
Table 1: Example of Academic Structure

This structure will impact the development of the tables used in defining campus locations, academic organizations, subjects, catalog, and course schedule.

Locations and Campuses: Campuses belongs to single institution and use the same course catalog, Produce a common transcript, and are usually associated with separate physical locations.

 Academic Organization: Defines how an institution is organized from a administrative perspective. The academic organization information is used to create a Tree Manager. The tree controls security levels and access to certain Peoplesoft panels and functions.

 Subjects: An academic subject is aspecific area of institution within an academic organization. For example, if the course is MATH 155, Math is a subject area. This is tied back to the academic organization Tree (Department). Academic subjects are also linked with course catalog.

 Catalog: Before the course catalog is developed, the institution codes, academic group, subject, campuses, academic organizations, and academic career must be defined. The course catalog panel group contains panels that specify everything you need to enter for a course: the definition, number of credits, contact hours, topic prerequisites, etc. The course catalog information provides the template for scheduling classes. The information entered rolls forward to the class schedules.

 Schedules of class: The class schedule is generated using information from the course catalog. Peoplesoft will allow roll classes from term to term, adding new courses to the schedule, and revising those already scheduled. Creating a schedule include Term and Sessions. Term is defined as approved start and end dates with career. Sessions are created within a term for art and end dates of course. Each term is associated with an academic year.

 Academic Term: The Term is defined as the start and end dates of the semester. These dates are approved by university and affect financial aid enrolment verification, and financial process.

 Sessions: Sessions are created within term and may have different start and end dates from the term. Academic dates, such as last day of drop and add and refund of dates are determined by the session. Each course must be attached to the session and term.

Academic Institution is a highest level in academic structure and also a common key field in all the major components in campus solutions. Academic institutions are assigned with SetId’s. The SetId allows sharing of common codes.

3.1 Setting up Grading Scheme

     n   Define the name and location of the academic institution.
     n   Set academic institution defaults and options.
     n   Set additional institutional defaults and option.
     n   Activate instructor workload.
     n   Set repeat checking controls.

3.2 Defining Repeat Scheme

     n   Define repeat schemes and repeat codes.
     n   Define repeat rules.
     n   Set up repeat checking for academic institutions.
     n   Set up repeat checking for academic careers.
     n   Set up repeat checking for academic programs.

3.3 Defining Academic Institutions

     n   Define the name and location of the academic institution.
     n   Set academic institution defaults and options.
     n   Set additional institutional defaults and option.
     n   Activate instructor workload.
     n   Set repeat checking controls.

4.    Setting Up Academic Groups and Careers

4.1 Defining Academic Groups

     n   Describe academic groups.
     n   Link academic career catalog numbers to academic groups.
     n   Define standard class meeting patterns.

4.2 Defining Academic Careers

     n   Describe academic career parameters.
     n    Set additional academic career parameters.
     n    Set up academic career pointers.
     n    Set repeat checking controls for academic careers.
     n    Set up self-service options.

4.3 Setting Up Holiday Schedules Values

     n   Define holiday schedule values on the Holiday Schedule Table page in HRMS.

5.    Setting Up Academic Programs, Plans and Sub-Plans

5.1 Defining an Academic Calendar

     n   Describe academic calendars.
     n   Set up term landmark dates.
     n   Define self-service graduation terms.
     n   Set up session cancellation and withdrawal dates.
     n   Set up session drop dates.

5.2 Defining Academic Level and Load Rules

     n   Define academic level and load determination values.
     n   Define level rules.
     n   Define load rules.
     n   Define contiguous term load rules.

5.3 Academic Programmes

     n   Set up defaults for academic programs.
     n   Set up academic standing parameters for academic programs.
     n   Set up honours and award parameters for academic programs.
     n   Establish academic organization ownership for academic programs.
     n   Set taxonomy and repeat checking options for academic programs.
     n   Define campuses and business units for academic programs.
     n   Define grade lapse rules for academic programs.
     n   Set up term enrollment limits for academic programs.
     n   Set up session enrollment limits for academic programs.
     n   Set up course count limits for academic programs.

5.4 Academic Plans

     n   Describe academic plans.
     n   Set up print options.
     n   Set up taxonomy.
     n   Establish academic organization ownership.

5.5 Academic Sub-Plans

     n   Describe academic subplans.
     n   Set up taxonomy.

6.    Establishing Academic Terms and Sessions

6.1 Defining Tem Values

Set up term values and their descriptions. You use these term values for all academic institutions and careers throughout Campus Solutions, regardless of the structure of the terms that you define.
Access the Term Values Table page (Set Up SACR, Foundation Tables, Term Setup, Term Values Table, Term Values Table).

6.2 Defining Time Periods

     n   Use the Time Period Table component (TIME_PERIOD_TABLE) to set up Time Period.
     n   Define the time periods, or critical points in time, that are valid for each academic career within a setID.
     n   You can add more time period translate values, provided that you attach your own coding to them.

6.3 Defining Academic Terms and Sessions

     n   Define terms within academic careers. Different academic careers at an institution can have different term structures.
     n   Define the sessions of a term, including the significant dates within the session. Sessions subdivide a term into multiple time periods in which to offer classes.
     n   Define landmark time periods within each session of a term. The system uses time periods for enrollment security purposes.

7.    Defining Academic Organizations

7.1 Setting up Organisations

     n   Add/Modify academic organizations.
     n   Designate financial ownership for academic organizations.
     n   Designate human resource ownership for academic organizations.

7.2 Setting Up Academic Subjects

     n   Describe subject areas.
     n   Define subject area taxonomy.
     n   Define subject and component multipliers from Subject workload page.