PeopleSoft Interaction Hub

Steps to Implement Data Archive Manager

Step1: Create Archive Object

Archive Record: Record from data need to archive.

Base Record: The record is selected is the base record of this archive object. This is a check box and only one can be base record.

History Record: History Record to which transaction data is needed to archive.

History Record Structure (Example):

1. Save as HR_ACCTG_LINE record to HRACTLN_HST and don’t copy any peoplecode associated with record HR_ACCTG_LINE.

2. Insert Subrecord PSARCHIVE_SBR at top in the record as shown in below image.

3. Build the table.

Step2: Create Archive Query

Archive Query contain Archive record name and all field selected.

Criteria: This is required to add criteria which filter the data for archiving.

Step3: Archive Template

Step4: Running Archive Process

Process Type:

1. Selection: Copy data from Archiving Record to History Record.

2. Delete: Deleting data from Archiving Record.

3. Remove from History: Deleting data from History Record.

4. Rollback: Copy data from History Record to Archiving record.

Note: Select the process type and run the process.

Absence Management White Papers to Assist with Implementations by Oracle

Oracle has recently provided Absence Management White Papers. Below are the documents can be find at Oracle support. You need login for these documents.

1. Absence Management Entitlement and Take Setup:

Document ID 1493866.1: This document (Document ID 1493866.1) provides an overview of how to set up the main components of Absence Management, such as Absence Entitlement and Take elements, as well as other supporting elements relevant to your Absence Management implementation.

2. Absence Management System Elements:

Document ID 1493879.1: This document (Document ID 1493879.1) provides an overview of the system elements related to Absence Management. System elements are building blocks used during the design and construction of your Absence Rules. Knowing how they work and when to use them should help you expedite the implementation of your Absence Policy rules in your company

3. Absence Management Self Service Setup :

Document ID 1493867.1: This document (Document ID 1493867.1) provides an overview and guidance on some of the important areas when setting up Absence Self Service. Throughout this document we are providing examples of different configurations supported in Self Service. 

Source: Oracle Support centre.

External People(non–employee / Applicant) Access for PeopleSoft Pages

Below steps helps to give access to components for external people.

This is required for many scenarios. Some Examples are:

1. Forgot password – Helps employee to retrieve login password.

2. External Candidate – Helps to apply  job for the company.

3. External candidate to register course – Helps external candidates to apply course. etc..

Steps to to give access for external people:

Step 1: Create a user id as EXTERNAL and set a password(PS) for it.Assign "External Applicant" & "Recruitment User"(optional) roles to this user-id. Save it.

Important : Do not attach any other Role to this user id.

Screens of roles

Step2: go to PeopleTools --> Webprofile --> Webprofile Configuration.

Then choose the profile (E.g. DEV)

Navigate to the "Security" tab and check "allow public access" and give the userid "EXTERNAL" and password "PS" and save it.

Step3: To access page use the below format.

"http://Your IP:Port/psc/ps/EMPLOYEE/HRMS/c/ComponentName.Market"

In Scroll Area, Clicking from "View All" to "View 1" Creates Empty Space

Problem Description:

In some scroll areas, when clicking from "View All" to "View 1" link, the page doesn't remove the extra space that was used from "View All" mode.

Solution:

For PeopleTools 8.50: This bug has been fixed on Patch 8.50.03 and higher.

For PeopleTools 8.52: This bug has been fixed on Patch 8.52.10 and higher. In normal case, we have tested for this issue on patch 10 and its working good for View all and View 1 action but some scenarios this is broken.

 

Reference document from oracle support: [Doc ID 1062053.1]

Release Value Proposition: PeopleTools 8.53 feature analysis

Oracle has planned to enhance/add below features in PeopleTools 8.53.

1. User Interface
2. PeopleSoft Search Framework
3. Lifecycle Management
4. Server Administration and Infrastructure
5. Security Administration
6. Integration Technology
7. Reporting and Analysis Tools
8. Development Tools

1. User Interface

a) Dramatic New Look and Feel: New style sheet for good look and feel. These changes also lay the groundwork for future user interface changes.

b) Related Actions/Related Content: PeopleTools 8.53 brings Related Actions to Pivot Grids, allowing users to take actions directly on data displayed in a pivot grid.

                                                         A new Related Actions widget can be used to display the Related Actions menu. This feature eliminates the need for a developer to write code for displaying the optional menu and for defining HTML areas on pages to display Related Actions.

                                                         Related Services can be configured in groups, which make it simpler to configure the same services that appear in multiple places.

c) WorkCentres and Dashboards: Application developers and designers can deploy dashboard-like pagelets within WorkCenters to provide better decision support for WorkCenter transactions. This capability combines some of the features and benefits of Dashboards with WorkCenters.

                                                    Applications can now consume remote pagelets in WorkCenters, Homepages, and Dashboards.

d) Activity Guide: In 8.53, the Activity Guide enables application developers to define a list of action items tied to transactional, collaborative, or manual tasks. The action item represents a task that can be tracked for assignment and completion, and is secured by roles and user lists. Activity Guides also enable a hierarchical list of action items.

                            A new Activity Guide WorkCenter template is available for use with building and configuring Activity Guides. This template provides a frame with the navigation controls used to traverse an Activity Guide. The WorkCenter template also provides the following features:

• A status bar to indicate the progress of the Activity Guide, and the Active Action item is highlighted.
• Ability to do pre-processing and post-processing for an action item.
• Ability to assign an action item to a role or a user.
• Ability to create dependency rules for action items.

2. PeopleSoft Search Framework

PeopleSoft Application Search framework features include:

• A search experience that functions similarly to the internet, providing a look and feel that users will find both familiar and intuitive.
• An Application Search that is available at any point in an application and can be configured to span multiple PeopleSoft applications. It enables users to initiate transactions or navigate to key information without using the PeopleSoft application menu or returning to the homepage to do so.
• The ability to use filters and facets to narrow result sets, further enabling users to identify and navigate to specific application content.
• Search results that display in prioritized order by relevance based on the provided search terms.
• Related Action menus that are embedded directly in the search results, allowing users to navigate straight to specific related transactions, which are prepopulated with the selected search results data.
• A Component Search function that enables users to search by keyword, thereby extending the versatility of virtually all existing search pages.

Key PeopleSoft Search Framework Enhancements:

• Performance
• New source types
• Oracle SES instance sharing
• Multiple FTP credential support
• Monitoring and diagnostics
• Duplicate search results
• Increased operator support

3. Lifecycle Management

a) PeopleSoft Update Manager

b) Configuration Management with the Data Migration Workbench(A brand new tool): The Data Migration Workbench is a new tool that helps users manage the process of moving configuration data from one system to another.

c) PeopleSoft Test Framework: Enhanced to support a number of new PeopleTools features and capabilities.

d) Three-Way Merge Support

4.  Server Administration and Infrastructure

a) Platform Components

b) Extending 64-bit Support to the Windows Server Platform: The application server uses 64-bit binaries on the Windows Server platform. The 32-bit Windows code for the application server will no longer be available.

c) A New Home for Customizations - PS_CUST_HOME : This change is optional; customers may continue to use a traditional PS_HOME if desired.

d) PeopleSoft Application Management Pack for Oracle Enterprise Manager 12c

e) Offload PS/nVision Workloads with Active Data Guard and Golden Gate

f) Support for Firefox Extended Support Releases: Firefox 17 is the anticipated minimum ESR to be certified with PeopleTools 8.53.

5. Security Administration

a) Secure by Default Initiative:

• Deliver all UserIDs with a password that must be set during Installation.
• Salt-enabled user Passwords.

6. Integration Technology

a) JavaScript Object Notation (JSON) from Documents

b) REST Web Services

c) Integration Network Enhancements

• Automated Network Status and Real-Time Notification of Errors
• Improved Life Cycle Utilities
• Bulk Update of Permission for Service Operations

7. Reporting and Analysis Tools

a) Reporting Console Enhancements:

• Drag-and-drop of folders and Process Bookmark as a sibling.
• Drag-and-drop of folders and Process Bookmark as a child of an existing folder.
• Rename of folders.
• Import folders and Process Bookmark of existing user.
• Dynamic Context Menu option on folder and Process Bookmark row for add, edit, rename, delete options.

b) New My Reports Pagelet:

• Display list of different systems/databases containing the reports and providing functionality for filtering reports based on a particular database.
• Opening of the actual report directly instead of a page to display a list of files.
• Grouping of multiple instances of the same report.

c) Chart Enhancements: PeopleTools 8.53 introduces a number of Chart Enhancements, including making use HTML, CSS3, and JavaScript instead of the server-based chart engine. The new charts incorporate improvements in scrolling and display and more closely match the overall PeopleTools look and feel.

d) Pivot Grid Enhancements:

• Multi Select Filters that provide the ability to filter data on multiple values instead of a single value.
• Progressive Filtering that shows only valid values for the current filter selection when a filter value is modified.
• Dynamic passing of prompt values into the Pivot Grid being displayed in a Related Content frame or in Related Actions.
• Chart-only view allows users to change layout using grid.
• Enabling of Pivot Grid pagelets to interact with each other and with transaction pages. Prompt and filter value changes can be published and subscribed to in real-time.
• Enhancements to the user interface.
• Pivot Grid Administration Component provides module to clone and delete Pivot Grid Models and personalization.
• Enhancements to the Pivot Grid Wizard.

8. Development Tools

PeopleTools 8.53 introduces a new feature in Application Designer that allows developers to define the field label alignment for new or modified pages. A configuration property can be set that defines whether new field labels should be right or left aligned. This feature greatly streamlines the otherwise manual process of field label alignment. Once a field is placed on a page, the developer retains the ability to change the label alignment where necessary.

 

Key PeopleTools 8.53 features Executive Summary:

PeopleSoft Styles and User Interaction Model

• New styles for a contemporary appearance.
• More flexibility in WorkCenters and Dashboards.
• Improvements to the Related Content Framework, Related Actions, and Activity Guides.

PeopleSoft Data Migration Workbench

PeopleSoft Update Manager (NEW)

• The customer does not have to be current on maintenance.

• The customer can select a single fix and find out if requisite fixes are available that must be applied with it.

• Dependencies are calculated by area of system affected rather than time, so fewer dependencies are required.

PeopleSoft Secure by Default Initiative

• Safeguards for ensuring that passwords for default user accounts are set during installation.

• Additional mitigation for brute force and denial-of-service attempts.

• Additional security for file attachments and the Report Repository.

Round Pie Chart Slice Labels

Slice the labels of pie chart to required(example 1) decimal place.

<SliceLabel>

<ViewFormat decimalDigit="1" decimalSeparator="." decimalDigitUsed="true" decimalSeparatorUsed="true" />

</SliceLabel>

Related content link in Dropdown

As shown in Image to create related content link in Dropdown we need to follow below steps.

Assumption: Component is created and Related content setup is done.

Below steps need to be done to use Related content in drop down.

Step1: Adding HTML Area to page.

Place a HTML area in the page to the created component.

Here “N_RELATED_ACTNS” is a custom Long char field.

In General Tab provide “RELATEDACTIONS” as page field name as shown in the image

Step 2: Hiding the Related Action Page field and making it modified by JavaScript.

Here People ID is the Related action Field defied in Related action setup page.

Make this field is hidden and set the page field property as below Image.

Note: Modified by JavaScript should be enabled.

Step 3: Code to set Related action to HTML area.

/*****************Start - Related Actions*************************/
For &i = 1 To &rs_var.ActiveRowCount
   &sActionShortLabel = &rs_var(&i).N_HR_EE_DB_WRK.N_RELATED_ACTNS.GetShortLabel("RELATED_ACTION");
   &sPopUpMenuJavaScript = GetHTMLText(HTML.N_GET_POPUPMENU);
   &rs_var(&i).N_HR_EE_DB_WRK.N_RELATED_ACTNS.Value = &sPopUpMenuJavaScript | GetHTMLText(HTML.N_REL_ACTIONS, String(&i - 1), "N_PEOPLE_ID_DR", "%Image(PT_BTN_ARROW)", &rs_var(&i).N_MSSACTION_EMP.N_PEOPLE_ID.Value, &rs_var(&i).N_MSSACTION_EMP.N_PEOPLE_ID.Value);
   
End-For;
/*****************End - Related Actions*************************/

Note: “ String(&i - 1)” is required if this is used in grid/scroll else this can be eliminated.

Custom HTML object Definitions used in above code:

1. N_GET_POPUPMENU  - (Object Type is HTML)

<script language="JavaScript">
function ShowPopUpMenu(sFieldName,nRow, sActionFieldName)
{
       CreateContextMenu(sFieldName,nRow, 3,"", 0, 0,"","%FormName",sActionFieldName);
       return ;
}
</script>

2. N_GET_POPUPMENU - (Object Type is HTML)

<a id="relatedActionsRow$%BIND(:1)"  class="PSHYPERLINKNOUL" href="javascript:ShowPopUpMenu('%BIND(:2)','%BIND(:1)','relatedActionsRow$%BIND(:1)');" title="%BIND(:5)" ac="1"  class="PTSESRSLTRLTDINFO">

<img border="0" src="%BIND(:3)" style="width: 12px; height: 8px; cursor: pointer; vertical-align:middle: text-bottom;" ac="1" noc="1">%BIND(:4)</a>

Analysing “PSQRYCRITERIA” Table

 

CONDTYPE and EXPRTYPE stores below code in PSQRYCRITERIA Table.

Field: Condition Type (CONDTYPE)
Code	Description	Return Code
2	equal to	EQU
3	not equal to	EQU-
4	greater than	GRE
5	not greater than	GRE-
6	less than	LES
7	not less than	LES-
8	in list	LIS
9	not in list	LIS-
10	Between	BET
11	not between	BET-
12	Exists	EXI
13	does not exist	EXI-
14	Like	LIK
15	not like	LIK-
16	is null	NUL
17	is not null	NUL-
18	in tree	TRE
19	not in tree	TRE-
20	eff dt <=	EDLE
21	eff dt >=	EDGE
22	eff dt <	EDLT
23	eff dt >	EDGT
24	first eff dt	EDFS
25	last eff dt	EDLS

 

Field: Expression Type (EXPRTYPE)
Code	Description	Return Code
0	effective sequence	ES
1	constant	CN
2	field	FD
3	Expression	EX
4	Subquery	SQ
5	list	LS
6	current date	CD
7	tree	TR
8	Prompt	PR
9	between const-const	CC
10	between const-field	CF
11	between const-expr	CE
12	between field-const	FC
13	between field-field	FF
14	between field-expr	FE
15	between expr-const	EC
16	between expr-field	EF
17	between expr-expr	EE
18	Tree prompt	TP

Detailed Information:

Record Field Name: QRYFUNCTIONS - QRYTRANSFUNCS

PeopleTools iPad Certification

Error on Javascript in PeopleTools 8.52: "hAction_win0 is not defined"

Below code may not work from PeopleTools 8.52. But this will work on below version of tools release (i.e. 8.50, 8.51).

hAction_win0(document.win0,pageFldName,0,0,inlabel,false,true); 

Reason:

"hAction_win0" is no longer used in PeopleTools 8.52.

Solution:

"hAction_win0" can be replaced with "submitAction_win0" for 8.52 PeopleTools.

Sample code:

submitAction_win0(document.%FormName,pageFldName,0,0,inlabel,false,true); 

Rowset FILL Method with JOIN / UNION Keyword

FILL method helps to fill stand alone rowset from database directly.

But sometimes it is required to fill rowset Using JOIN / UNION Keyword.

1. Using JOIN Keyword.

Local Rowset &rs = Createrowset(Record.TL_RPTD_TIME);

&rs.Fill("INNER JOIN PS_TL_RPTD_TIME TM ON TM.EMPLID = FILL.EMPLID WHERE FILL.PUNCH_TYPE = '0' AND FILL.EMPLID = :1", &emplid);

2. Using UNION Keyword.

Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM) FROM PS_PERSON WHERE EMPLID = :1", &emplid);

Configuring Report Manager

 

Steps to configure and get Reports in Report manager.

1. Enable getting Reports in Report Manager.

2. Add New Folders.

3. Get new files to Process Monitor.

4. Assign/Create required Permission Lists/Roles to Users.

5. Configure the process.

6. Create a new run control id.

7. Run it.

8. Verify it.

 

For any reports to be visible to other people in Report Manager we need to do the following:

1. Activate the following Service Operations

• · Check Active (in Default Service Operation Version)

2. To Add New Folders, add them here. This is just to group by folders and has no security tied to it.

•   Verify they come up

3.  To get new files to Process Monitor

· Add PRL in System Settings –> Distribution File Options –> Add PRL & Description

• Bounce the Process Scheduler
• After running the report verify that the file is pulled up

4.  Create new permission list

• Add Report Manager access and Report Viewer access.

• ·Click Select All to give the Report Viewer Page access.

• Add other Reports access. This gives the option to run the reports. If the users don’t want/need to run the reports this may be skipped.

5.  Add new Role

• Add this role to users

• Verify required users got the Role

6.  Change the Process Destination, set the Destination Source, Folder Name and Distribute To.

7.   Run the process

• Create a new run control id

• Chose the process

• To ensure everything is correct, Click on the Job (Description)

• Click on Distribution of required Process, we must be able to see the given Folder Name and the Role.

8.  Verify the distribution by going to Report Manager –> List

 

Thanks a lot to my friend Ranjan Patro (http://in.linkedin.com/in/ranjanpatro) for creating this document.

An error has occurred that has stopped this transaction from continuing

Reason 1:

This message appears due to 0 rows in PS_INSTALLATION Table.

Solution:

Insert a row in PS_INSTALLATION table and  restart the appservers.

Reason 2:

some error code in the events PreBuild, Activate, PostBuild or any FieldDefault.

Sending Multiple attachment using MCF Send mail method.

Below is the sample code to send multiple attachment in mail using MCF Send() mail method.

Local any &FromURL = "record://PV_ATT_DB_SRV/";
Local string &FILENAME;
Local Rowset &rwln1 = CreateRowset(Record.PV_ATTACHMENTS);
Local SQL &Sql1 = CreateSQL("select attachsysfilename from PS_PV_ATTACHMENTS a,ps_SPF_RESUME_ATT b where a.scm_attach_id=b.scm_attach_id  and b.person_id=:1", "000000000000025", &FILENAME);

While &Sql1.Fetch(&FILENAME)
   &attach = create PT_MCF_MAIL:MCFBodyPart();
   Local string &ToFile = "C:/user/" | &FILENAME;
   Local any &errorCode = GetAttachment(&FromURL, &FILENAME, &ToFile);

   If &i = 1 Then
      &multiattach = CreateArray(&attach);
      &multiattach [&i] = &attach;
   Else
      &multiattach [&i] = &attach;
   End-If;
   &multiattach [&i].SetAttachmentContent(&ToFile, %FilePath_Absolute, &FILENAME, " ", "", "");
   &mp.AddBodyPart(&multiattach [&i]);
End-While;

Local PT_MCF_MAIL:MCFBodyPart &test1 = create PT_MCF_MAIL:MCFBodyPart();
Local PT_MCF_MAIL:MCFBodyPart &eMail1 = create PT_MCF_MAIL:MCFBodyPart();

&eMail1.Text = &Message;
&mp.AddBodyPart(&eMail1);
&eMail.MultiPart = ∓
&res = &eMail.Send();

Local boolean &done;

Evaluate &res
When %ObEmail_Delivered
   /* every thing ok */
   &done = True;
   MessageBox(0, "", 0, 0, "Email Sent Successfully");
   Break;
When %ObEmail_NotDelivered
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Check &email.InvalidAddresses, &email.ValidSentAddresses */
   /*   and &email.ValidUnsentAddresses                        */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   &done = False;
   MessageBox(0, "", 0, 0, "Email Not delivered" | &eMail.InvalidAddresses | &eMail.ValidSentAddresses | &eMail.ValidUnsentAddresses);
   Break;
When %ObEmail_PartiallyDelivered
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Check &email.InvalidAddresses, &email.ValidSentAddresses */
   /*   and &email.ValidUnsentAddresses                        */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   &done = True;
   MessageBox(0, "", 0, 0, "Email Partially delivered" | &eMail.InvalidAddresses | &eMail.ValidSentAddresses | &eMail.ValidUnsentAddresses);
   Break;
When %ObEmail_FailedBeforeSending
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Get the Message Set Number, message number;              */
   /*   Or just get the formatted messages from                */
   /*   &email.ErrorDescription, email.ErrorDetails;           */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   &done = False;
   MessageBox(0, "", 0, 0, "Email Failed Before Sending" | &eMail.ErrorDescription | &eMail.ErrorDetails);
   Break;
End-Evaluate;

If &done = True Then
   While &Sql1.Fetch(&FILENAME)
      Local string &deleteFile = "C:/user/" | &FILENAME;
      Local JavaObject &f = CreateJavaObject("java.io.File", &deleteFile);
      &f.delete();
  End-While;
End-If;

Note: This is sample code. This required to include necessary application package and to declare objects.

Move and Delete files using PeopleCode

We can use the below java object and code to move or deleting files from some location.

Moving file:

Local JavaObject &source = CreateJavaObject("java.io.File", "/source/file.txt");
Local JavaObject &target = CreateJavaObject("java.io.File", "/target/file.txt");

&source.renameTo(&target); 

Deleting file:

Local JavaObject &DelLoc = CreateJavaObject("java.io.File", "/source/file.txt");

&DelLoc.delete(); 

Alternate code for deleting file:

&tmpfile = GetFile(“c:\temp\file.txt”, “W”, “A”, %FilePath_Absolute);
&tmpfile.Delete();

Add duration(number value) to Time field.

People Code to add duration to time field.

Example:

Here START_TIME, END_TIME is time field and DURATION is number field.

InPut: StartTime(START_TIME ) = 8:00AM and Duration(DURATION ) = 12

OutPut: EndTime(END_TIME) = 8:00PM

Sample Code:

Declare Function Return_DateTime_value PeopleCode TIME_TBL.START_TIME FieldFormula;
Declare Function Return_Hour_Min_Part PeopleCode TIME_TBL.START_TIME FieldFormula;

Local datetime &TIME_VALUE;
Local integer &MINUTES;

&DUR_VALUE = TIME_TBL.DURATION.Value;
&StartTime = TIME_TBL.START_TIME.Value;

Return_DateTime_value(&StartTime, &DUR_VALUE, &TIME_VALUE);
Return_Hour_Min_Part(&DUR_VALUE, &HOUR_PART, &MINUTES);

TIME_TBL.END_TIME.Value = TimePart(AddToDateTime(&TIME_VALUE, 0, 0, 0, &HOUR_PART, &MINUTES, 0));

Defining Functions:

Function Return_DateTime_value(&StartTime, &DUR_VALUE, &TIME_VALUE As datetime);
   Local date &RefDate;
   &RefDate = Date3(2000, 1, 1);
   &TIME_VALUE = &RefDate + &StartTime;
End-Function;

Function Return_Hour_Min_Part(&DUR_VALUE, &iHour, &iMinutes As integer)
   &temp = String(&DUR_VALUE);
   &iHour = Value(Substring(&temp, 1, 2));
   &iPos = (Find(".", &temp));
   If &iPos > 0 Then
      &sMinutes = Substring(&temp, &iPos + 1, 2);
      If Substring(&sMinutes, 1, 1) = "0" Or
            Len(&sMinutes) > 1 Then
         &iMinutes = Value(&sMinutes);
         &iMinutes = ((&iMinutes / 100) * 60)
      Else
         &iMinutes = Value(&sMinutes);
         &iMinutes = ((&iMinutes / 10) * 60)
      End-If;
   Else
      &iMinutes = 0;
   End-If;
End-Function;

XSL function to convert number to word

Below code is used to convert Word from number in XSL.

Sample Code: <?xdoxslt:toWordsAmt(12345.98)?>

Output:  “Twelve Thousand Three Hundred Fourty Five and paise Ninety Eight”.

The code can be used in xml publisher document to convert number to word.

Talent Summary Dashboard

Manager Dashboard

Pivot Grid Overview

AWE Tables and Migration script

AWE 9.1 Application Tables:

-- Approval Process Definitions

set output EOAW_Systbls_export.dat;
set log EOAW_Systbls_export.log;

-- Export Transaction Registry
Export EOAW_ADMMON_CFG;
Export EOAW_MONDIS_DTL;
Export EOAW_MONDIS_HDR;
Export EOAW_USRMON_CFG;
Export EOAW_USRMON_LNG;
Export EOAW_TXN;
Export EOAW_TXN_CFG;
Export EOAW_TXN_COMP;
Export EOAW_TXN_LBL;
Export EOAW_TXN_LNG;
Export EOAW_TXN_LVL;


--Export Transaction Notifications 
Export EOAW_NOTIFY;
Export EOAW_NOTIFYDEF;
Export EOAW_NOT_HDR;
Export EOAW_NOT_USER;
Export EOAW_NOT_USRDEF;


-- Export User Lists
Export EOAWUSER_LIST;
Export EOAWUSER_LNG;

-- Export Process Definitions
Export EOAW_IDS where EOAWCOUNTERNAME = 'STAGE_ID';
Export EOAW_PATH;
Export EOAW_PATH_LNG;
Export EOAW_PRCS;
Export EOAW_PRCS_LNG;
Export EOAW_STAGE;
Export EOAW_STEP;
Export EOAW_STEP_LNG;
Export EOAW_STG_LNG;
Export EOAW_TIMEOUT;
Export EOAW_TIMEOUTDEF;

Export EOAW_AUTH;
Export EOAW_AUTH_DTL;
Export EOAWCRTA;
Export EOAWCRTA_LNG;
Export EOAWCRTA_REC;
Export EOAWCRTA_RECLNG;
Export EOAWCRTA_VAL;

-- Export NEM (Notification and Escalations)
Export EOAWNEM;
Export EOAWNEM_EMAIL;
Export EOAWNEM_EVENTS;

-- Export EMC (Email Collaboration)
Export EOAWMCLTLN_LNG;
Export EOAWEMC_LYT_HDR;
Export EOAWEMC_LYT_LIN;
Export EOAWEMC_MSGHDR;
Export EOAWFIELD_LIST;
Export EOAWRMINPT_HDR;
Export EOAWRMINPT_LIN;
Export EOAWXLAT_SYMBOL;

-- Export RunTime 
rem Because the user's Cross Reference Tables need to be exported as well, this should not be done by AWE;
rem Leaving them in for now in case we change this position. 
rem Export EOAW_STEPINST;
rem Export EOAW_USERINST;
rem Export EOAW_WL;

AWE 9.0 Application Tables:

-- Approval Process Definitions

set output PTAFAW_Systbls_export.dat;
set log PTAFAW_Systbls_export.log;

-- Export Transaction Registry
Export PTAFAW_ADMMON_CFG;
Export PTAFAW_MONDIS_DTL;
Export PTAFAW_MONDIS_HDR;
Export PTAFAW_USRMON_CFG;
Export PTAFAW_USRMON_LNG;
Export PTAFAW_TXN;
Export PTAFAW_TXN_CFG;
Export PTAFAW_TXN_COMP;
Export PTAFAW_TXN_LBL;
Export PTAFAW_TXN_LNG;
Export PTAFAW_TXN_LVL;


--Export Transaction Notifications 
Export PTAFAW_NOTIFY;
Export PTAFAW_NOTIFYDEF;
Export PTAFAW_NOT_HDR;
Export PTAFAW_NOT_USER;
Export PTAFAW_NOT_USRDEF;


-- Export User Lists
Export PTAFUSER_LIST;
Export PTAFUSER_LNG;

-- Export Process Definitions
Export PTAFAW_IDS where PTAFAWCOUNTERNAME = 'STAGE_ID';
Export PTAFAW_PATH;
Export PTAFAW_PATH_LNG;
Export PTAFAW_PRCS;
Export PTAFAW_PRCS_LNG;
Export PTAFAW_STAGE;
Export PTAFAW_STEP;
Export PTAFAW_STEP_LNG;
Export PTAFAW_STG_LNG;
Export PTAFAW_TIMEOUT;
Export PTAFAW_TIMEOUTDEF;

Export PTAFAW_AUTH;
Export PTAFAW_AUTH_DTL;
Export PTAFCRTA;
Export PTAFCRTA_LNG;
Export PTAFCRTA_REC;
Export PTAFCRTA_RECLNG;
Export PTAFCRTA_VAL;

-- Export NEM (Notification and Escalations)
Export PTAFAWNEM;
Export PTAFAWNEM_EMAIL;
Export PTAFAWNEM_EVENTS;

-- Export EMC (Email Collaboration)
Export PTAFMCLTLN_LNG;
Export PTAFEMC_LYT_HDR;
Export PTAFEMC_LYT_LIN;
Export PTAFEMC_MSGHDR;
Export PTAFFIELD_LIST;
Export PTAFRMINPT_HDR;
Export PTAFRMINPT_LIN;
Export PTAFXLAT_SYMBOL;

-- Export RunTime 
rem Because the user's Cross Reference Tables need to be exported as well, this should not be done by AWE;
rem Leaving them in for now in case we change this position. 
rem Export PTAFAW_STEPINST;
rem Export PTAFAW_USERINST;
rem Export PTAFAW_WL;

Approval Tables 8.9:

-- Approval Process Definitions
-- Export script for work orders

set output c:\temp\ApprovalsUserData.dat;
set log c:\temp\ApprovalsUserData.log;

EXPORT SAC_USER_LIST;


EXPORT WL_TEMPLATE_GEN;
EXPORT WL_TEMPL_GEN_TK;
EXPORT WL_TEMPL_GEN_RS;

rem Exporting Transaction Registry Information;
EXPORT SAC_AW_TXN;
EXPORT SAC_AW_TXN_COMP;
EXPORT SAC_AW_TXN_LNG;
EXPORT SAC_AW_TXN_LVL;
EXPORT SAC_AW_TXN_LBL;
EXPORT SAC_AW_NOTIFY;
EXPORT SAC_AW_TXN_CFG;
EXPORT SAC_AW_NOT_USER;

REM Exporting Process Definitions;
EXPORT SAC_AW_PRCS;
EXPORT SAC_AW_PRCS_LNG;
EXPORT SAC_AW_STAGE;
EXPORT SAC_AW_PATH;
EXPORT SAC_AW_PATH_LNG
EXPORT SAC_AW_STEP;
EXPORT SAC_AW_STEP_LNG;

REM Exporting Authorized Approver settings;
EXPORT SAC_AW_AUTH;
EXPORT SAC_AW_AUTH_DTL;

REM Exporting Criteria Details;
EXPORT SAC_CRTA;
EXPORT SAC_CRTA_LNG
EXPORT SAC_CRTA_REC;
EXPORT SAC_CRTA_RECLNG;
EXPORT SAC_CRTA_VAL;

Sample Script: 

EXPORT PS_PTAFAW_TXN where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_TXN_LBL where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_TXN_LVL where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_NOTIFY where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_NOT_USER where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_TXN_CFG where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFUSER_LIST;

EXPORT PS_N_WF_TRANS where N_TRAN_CATEGORY = 'ABC';

EXPORT PS_N_WF_TRAN_CAT where N_TRAN_CATEGORY = 'ABC';

EXPORT PS_PTAFAW_PATH  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_PATH B where B.PTAFPRCS_ID = PS_PTAFAW_PATH.PTAFPRCS_ID and B.EFFDT <= SYSDATE );

EXPORT PS_PTAFAW_PRCS  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_PRCS B where B.PTAFPRCS_ID = PS_PTAFAW_PRCS.PTAFPRCS_ID and B.EFFDT <= SYSDATE);

EXPORT PS_PTAFAW_STAGE  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STAGE B where B.PTAFPRCS_ID = PS_PTAFAW_STAGE.PTAFPRCS_ID and B.EFFDT <= SYSDATE);

EXPORT PS_PTAFAW_STEP  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = PS_PTAFAW_STEP.PTAFPRCS_ID and B.EFFDT <= SYSDATE);

EXPORT PS_PTAFCRTA  where PTAFCRTA_ID = (select MAX(PTAFCRTA_ID) from PS_PTAFAW_STEP A where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = A.PTAFPRCS_ID and B.EFFDT <= SYSDATE));

EXPORT PS_PTAFCRTA_REC where PTAFCRTA_ID = (select MAX(PTAFCRTA_ID) from PS_PTAFAW_STEP A where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = A.PTAFPRCS_ID and B.EFFDT <= SYSDATE));

EXPORT PS_PTAFCRTA_VAL where PTAFCRTA_ID = (select MAX(PTAFCRTA_ID) from PS_PTAFAW_STEP A where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = A.PTAFPRCS_ID and B.EFFDT <= SYSDATE));

 

Change Access Password

The symbolic ID is the account used by PeopleSoft to link users (operators) to the system account which is stored as ACCESSID in the PSACCESSPRFL record and encrypted.
Syntax to change or update password is:
CHANGE_ACCESS_PASSWORD [SymbolicID] [Password]
Alternative Method:
If user(“SYSADM” for Oracle and “SA” for MSS) is altered by the below query,

ALTER USER username IDENTIFIED BY password

then below scripts need to execute.

UPDATE PSSTATUS SET OWNERID = 'SYSADM';
UPDATE PSOPRDEFN SET SYMBOLICID = 'SYSADM1', OPERPSWD = OPRID, ENCRYPTED = 0;
UPDATE PSACCESSPRFL SET ACCESSID = 'SYSADM', SYMBOLICID = 'SYSADM1', ACCESSPSWD = 'SYSADM', VERSION = 0, ENCRYPTED = 0;
GRANT SELECT ON PSSTATUS TO people;
GRANT SELECT ON PSOPRDEFN TO people;
GRANT SELECT ON PSACCESSPRFL TO people;

Now login to Data-Mover in bootstrap mode and execute the below script.

 
REM - ENCRYPT PASSWORD
/
SET LOG d:\dbsetup_log\encrypt.log;
ENCRYPT_PASSWORD *;
/

Note: Make sure Connect-ID (people) account is not locked and correctly entered to configuration manager.

Summary of the requested processes by process status

Query to get summary of the requested process.

select

    RQST.RUNSTATUS,

    RQST.PRCSTYPE,

    (

        select XLAT.XLATLONGNAME

        from PSXLATITEM XLAT

        where XLAT.EFFDT = (

            select max(XLAT_ED.EFFDT)

            from PSXLATITEM XLAT_ED

            where XLAT_ED.FIELDNAME = XLAT.FIELDNAME

            and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE

        ) and XLAT.FIELDNAME = 'RUNSTATUS'

        and XLAT.FIELDVALUE = RQST.RUNSTATUS

    ) as RUNSTATUS_XLAT,

    count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,

    min(RUNDTTM) as FIRST_OCCURRED,

    max(RUNDTTM) as LAST_OCCURRED

from PSPRCSRQST RQST

group by RQST.RUNSTATUS, RQST.PRCSTYPE

order by RUNSTATUS_XLAT, RQST.PRCSTYPE

How To Delete an AWE Approval Process ID, Definition and Related Data

Below scripts helps to delete AWE related data.

Each Delete statement should be specific and include Key values.  This Example uses PrcsName & DefnName, which should be replaced with correct values, as well as the specific Effective Date (EFFDT) be used.

 

The Step Table;
DELETE FROM PS_EOAW_STEP
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT = '05-SEP-11'

The first of three Criteria Tables;
DELETE FROM PS_EOAWCRTA
WHERE EOAWCRTA_ID LIKE '%PrcsNameDefnName2011-09-05%'

The second of three Criteria Tables.
DELETE FROM PS_EOAWCRTA_REC
WHERE EOAWCRTA_ID LIKE '%PrcsNameDefnName2011-09-05%'

The thrid of three Criteria Tables.
DELETE FROM PS_EOAWCRTA_VAL
WHERE EOAWCRTA_ID LIKE '%PrcsNameDefnName2011-09-05%'

The Table used to hold Timeout information for Notification and Escalation processing;
DELETE FROM PS_EOAW_TIMEOUT
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11'

The Path Table;
DELETE FROM PS_EOAW_PATH
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11'

The Stage Table;
DELETE FROM PS_EOAW_STAGE
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11'

And finally the AWE Process table;
DELETE FROM PS_EOAW_PRCS
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11' `

Image URL from PeopleCode

Below code helps  to get Image URL.

%Response.GetImageURL(Image.PT_PIXEL);

Component Toolbar Actions

Below table contains component action button and associated Action ID.

Action ID	Button Name
#ICSave	Save | OK
#ICCancel	Cancel | Return
#ICList	Return to Search
#ICNextInList	Next in List
#ICPrevInList	Previous in List
#ICSendNotify	Notify
#ICRefresh	Refresh
#ICNext	Next Page in Component
#ICPrev	Previous Page in Component
#ICSpellCheck	Spell Check
#ICAdd	Add
#ICUpdate	Update/Display
#ICUpdateAll	Include History
#ICCorrection	Correct History
#ICExpertEntry	Expert Entry

Unable to retrieve Application Engine step

Problem:
    Unable to retrieve Application Engine step

Solution:
    State record field that is used in program is not present in the state record.

Accessing Application Package class object using CreateObject method.

Let’s say we have 2 application package.

1. UserID and

2. Notification.

Here Notification application package uses dynamic changing application package say based on setup.

For our example we are using UserID application package as setup driven application package and it has UserID as Class.

Structure of UserID application package.

class UserID
   method GetOpridsByApplicationPack() Returns array of string;
   property array of string oprid_arr;
end-class;

method UserID
  
end-method;

method GetOpridsByApplicationPack
   /+ Returns Array of String +/
   
   %This.oprid_arr = CreateArrayRept("", 0);
   Local string &SupervisorId, &ReportsTo;
    
   %This.oprid_arr.push('PS'); /* Logic to populate data in array. */

   Return %This.oprid_arr;

end-method;

Below is “Notification” application package structure.

In this application package we are using CreateObject to access “GetOpridsByApplicationPack” method of UserID class of UserID application package .

We are passing Package name and Class Name while calling so they are variable for us. (In this case its UserID and UserID).

class NotificationManager
method GetOpridsByApplicationPack(&PkgRoot As string, &Appcls_Path As string) Returns array of string;

end-class;

method NotificationManager

end-method;

method GetOpridsByApplicationPack
   /+ &PkgRoot as String, +/
   /+ &Appcls_Path as String +/
   /+ Returns Array of String +/
   Local array of string &aryOpridTo;
   Local string &Classname;
   Local object &u;
   &aryOpridTo = CreateArrayRept(" ", 0);
   &Classname = &PkgRoot | ":" | &Appcls_Path;
   &u = CreateObject(&Classname);
   ObjectDoMethod(&u, "GetOpridsByApplicationPack");
   &aryOpridTo = &u.oprid_arr;
   Return &aryOpridTo;
end-method;

Data Mover SQL Error ORA-12899: Value Too Large for Column

Error:
Data MoverSQL Error. Stmt #: 0 Error Position: 851 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_GP_FORMULA_DTL".&q uot;FM_COMMENT" (actual: 255, maximum: 254)

Data Mover Release: 8.52
Database: HRMS91 (ENG)
Input file: C:\PT8.52\data\hcengs.db (ENG)
Importing GP_FORMULA_DTL
Import GP_FORMULA_DTL 0
Updating statistics for GP_FORMULA_DTL
Records remaining: 11949
Importing GP_FORMULA_DTL
File: Data MoverSQL error. Stmt #: 0 Error Position: 851 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_GP_FORMULA_DTL".&q uot;FM_COMMENT" (actual: 255, maximum: 254)
Failed SQL stmt:INSERT INTO PS_GP_FORMULA_DTL (PIN_NUM, EFFDT, SEQ_NUM5, FRML_FUNC_OPTN, FRML_LEFT_PAREN, ENTRY_TYPE_FRML1, PIN_FRML_FLD1_NUM, FRML_FLD1_DAT_TYP, FRML_FLD1_DEC_VAL, FRML_FLD1_DATE_VAL, FRML_FLD1_CHAR_VAL, FRML_FLD1_ATRB_IND, PIN_RND1_NUM, FRML_FLD1_USE_OPTN, FRML_OPER1, FRML_FLD1_OLDV_IND, ENTRY_TYPE_FRML2, PIN_FRML_FLD2_NUM, FRML_FLD2_DAT_TYP, FRML_FLD2_DEC_VAL, FRML_FLD2_DATE_VAL, FRML_FLD2_CHAR_VAL, FRML_FLD2_ATRB_IND, PIN_RND2_NUM, FRML_FLD2_USE_OPTN, FRML_FLD2_OLDV_IND, FRML_RIGHT_PAREN, ENTRY_TYPE_FRML3, PIN_FRML_FLD3_NUM, FRML_FLD3_DAT_TYP, FRML_FLD3_ATRB_IND, PIN_RND3_NUM, FRML_FLD3_USE_OPTN, FM_COMMENT) VALUES (:1, TO_DATE(:2,'YYYY-MM-DD'), :3, :4, :5, :6, :7, :8, :9, TO_DATE(:10,'YYYY-MM-DD'), :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, TO_DATE(:21,'YYYY-MM-DD'), :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :
Error: Unable to insert row 5091
Error: SQL execute error for GP_FORMULA_DTL
Unsuccessful completion

Solution:
Changes need to done before starting of installation.
Change NLS_LENGTH_SEMANTICS to CHAR which is default as BYTE.
Below are the steps:

SQL> alter system set NLS_LENGTH_SEMANTICS=char scope=both;   

System altered.   

SQL> select value from v$parameter where upper(name)='NLS_LENGTH_SEMANTICS';   

VALUE
-------------------------------------------------------------------------------
CHAR   SQL> select value from v$nls_parameters where parameter='NLS_LENGTH_SEMANTICS';   

VALUE
----------------------------------------------------------------
BYTE   

SQL> startup force
ORACLE instance started.   

Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             381684836 bytes
Database Buffers          146800640 bytes
Redo Buffers                5828608 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where upper(name)='NLS_LENGTH_SEMANTICS';   

VALUE
-------------------------------------------------------------------------------
CHAR   

SQL> select value from v$nls_parameters where parameter='NLS_LENGTH_SEMANTICS';   

VALUE
----------------------------------------------------------------
CHAR   

SQL>