Tuesday, April 13, 2010

PeopleSoft XML Publisher

This article explains how to create XML Publisher also called BI Publisher reports in PeopleSoft. XML Publisher is the only Reporting platform in the Fusion Application. Before we talk about how to create an XML Publisher report in PeopleSoft, I would like to give a brief overview.

Overview:
XML Publisher consists of the following components:

  • Data Source
  • Template
  • XML Publisher Engine
  • Report

Data source can be defined using in the following forms:

  1. PS Query
  2. Rowset
  3. XML File
  4. XMLDoc

ObjectReport template can be created using MS Word. A plug-in for MS Word can be downloaded from Oracle’s website or PeopleSoft application.XML Publisher Engine takes data source and template and creates a report.
Steps to create XML Publisher Report:

Step 1 – Data Source:
Go to Reporting Tools -> XML Publisher -> Setup -> Data Source. Add a new Data Source. Select either PS Query or Rowset. If you selected PS Query, first create a Query that you wish to use in your report. Youc can click on ‘Generate’ button to create Sample XML file to be used in report template. Save XML file on your PC.If you wish to use Rowset then write an Application Engine program that writes Rowset in the form of XML data and XSD schema. Use functions GetXMLData and GetXSDSchema to generate XML file and XSD file for rowset.


Step 2 – Report Template
Let’s create an RTF template using XML file that you created in step 1. Open MS Word after you have installed the plug-in. You will notice that there are five new buttons. Click on the first button i.e. Data and Load XML file that you created in step 1. Now your sample data is loaded in the template. You can format your report and test it using the loaded data. Save the template.


Step 3 – Report Definition
You are ready to create report definition.

Go to Reporting Tools -> XML Publisher -> Setup -> Report Definition and add a value. Specify the report name, data source type and data source name.

There are five tabs in Report Definition.

  1. Definition – Specify description and category of report.
  2. Template – Upload the template file created in Step 2.
  3. Output – Specify the output report format and set a default format.
  4. Security – Specify the Role or User who should have access to the report.
  5. Bursting – Specify the criteria if you need to burst the report. Leave it blank if bursting is not needed.Save the report definition.


Step 4 – Process Definition
Write a small Application Engine program that runs the above report definition. Now create a new process definition that runs this Application Engine process. Your first XML Publisher report is ready. Remember the output of XML Publisher report does not go to Process Monitor. In order to see the report, you need to go to Report Manager.

Try it out and email me if you have any questions

Thursday, April 8, 2010

How to use Verity Search to find applicants using the keywords

Find the resumes applicants based on the keywords. Search the resumes in peoplesoft.

Recruiting > Administration > Build Applicant Index

An application engine program can be executed to run the Verity search Index to search the applicants. under the applicants status select the group that needs to be indexed. Select the index C:\tmp for NT and /temp for UNIX scheduler.

Execute the process HRS_SRCH_IDX to create the search index in the PS_HOME/data/search/HRS_ResumeText/

If the application servers and process schedulers are on separate boxes then additional steps need to be taken so that the application servers have access the files created under the PS_HOME\data\search\HRS_ResumeText\%DB NAME% directory
These files can be copied to app server as well.

To find the applicants Recruiting > Find Applicants
In the Resume and Application Search section enter word that you are looking for in the resume in Enter Keywords field.

Wednesday, April 7, 2010

Peoplesoft Meta-Tables along with Description

This is an attempt to list PeopleSoft meta-tables along with some kind of description for every table. The list will be broken into categories (pages, records, components, and so forth). Please feel free to omment on the article.

PeopleSoft Projects
PSPROJECTDEFN table stores information about projects created in Application Designer.Try it out:
SELECT * FROM PSPROJECTDEFN

WHERE PROJECTNAME = 'Your_Project_name';

PSPROJECTITEM table stores objects inserted into your Application Designer project.Try it out:
SELECT * FROM PSPROJECTITEM

WHERE PROJECTNAME = 'Your_Project_name';


Portal Structure
PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component. PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.


XLAT Tables
XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4). PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values. PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).


Record & Field Tables
PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.
CASE RECTYPE WHEN 0 THEN 'Table'

WHEN 1 THEN 'View'

WHEN 2 THEN 'Derived'

WHEN 3 THEN 'Sub Record'

WHEN 5 THEN 'Dynamic View'

WHEN 6 THEN 'Query View'

WHEN 7 THEN 'Temporary Table'

ELSE TO_CHAR(RECTYPE)

END CASE

PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Containes 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.

CASE FIELD TYPE

WHEN 0 THEN 'Character'

WHEN 1 THEN 'Long Character'

WHEN 2 THEN 'Number'

WHEN 3 THEN 'Signed Number'

WHEN 4 THEN 'Date'

WHEN 5 THEN 'Time'

WHEN 6 THEN 'DateTime'

WHEN 8 THEN 'Image'

WHEN 9 THEN 'Image Reference'

ELSE TO_CHAR(FIELDTYPE)

END CASE

PSDBFLDLABL: Stores field label information.

Process Definition Table(s)
PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.
PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.


Message Catalog Tables
PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: language table.
Example: SELECT * FROM PSMSGCATDEFN

WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')

AND LAST_UPDATE_DTTM <>

This will return messages that has been last update/added between 2 specific dates.Previous PeopleSoft message catalog tables:PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.

MESSAGE_SET_TBL: Message set description table.
Example: SELECT * FROM PS_MESSAGE_CATALOG

WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')

AND LAST_UPDATE_DTTM <>


Menu Tables
PSMENUDEFN: Store Menu related information. No related component info on this table.
PSMENUITEM: List the menu with all components attached to it.


Component Tables
PSPNLGRPDEFN: Stores component related information only.
PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.


Pages
PSPNLDEFN: Stores pages definitions.
PSPNLFIELD: Stores all items used by each page definition.


Security
PSPRSMPERM: Portal Structure Permissions.
PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.
PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.
PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.
PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.
PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.
PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.
PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.A User gets these permission lists indirectly through the roles which are attached to the user

URL Definitions
PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> PeopleTools >> Utilities >> Administration >> URLs


Application Classes
PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.
PeopleSoft Query Tables
PSQRYDEFN: Stores query related info.
PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).
PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.
PSQRYEXPR: Stores query expressions.
PSQRYBIND: Stores query bind variables.
PSQRYRECORD: Stores all records used in all aspects of query creation
PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.
PSQRYLINK: Stores the relationships to child queries.
PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)
PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).


SQL Objects
PSSQLDEFN: Stores SQL object definitions.
PSSQLDESCR: Stores SQL objects descriptions, and description long.
PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.
-- When SQL type is:0 = Stand alone SQL objects1 = Application engine SQL2 = Views SQLs


Application Engines
PSAEAPPLDEFN: Table that stores Application Engine program definitions.
PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.
PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.
PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.
PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.
PSAESTEPDEFN: Steps in application engines are stored in this table.
PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.
PSAESTEPMSGDEFN: Application engine message action definition table.
AEREQUESTTBL: Application Engine request table behind the AE run control page.
AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.


PeopleCode Tables
PSPCMNAME: PeopleCode Reference table.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
Process Request Tables
PSPRCSQUE: This record contains the process request information to run a process request.
PSPRCSRQST: This record contains the process request information to run a process request.
PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.
Other Useful Tables
PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.
PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."Visit this post to see how could you make use of this table.
PSMAPFIELD: Stores Field mapping of ActivityPS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

Tuesday, April 6, 2010

How to Prevent Passwords from Expiring in Peoplesoft

One of the more often customer requirements regarding security is to implement password expiration. PeopleSoft provides this functionality using the Password Controls component under:PeopleTools > Security > Password Configuration > Password ControlsThis component provides the alternative to enable or disable password expiration controls for all users. Now, what happens if we want a certain user's password to never expire?There are plenty of situations where we might want this to happen, for instance:
The password for the user set in the Process Scheduler or Application Server configuration should not expire or otherwise the system may not work.
Same happens with if a user is set as a Guest in a Web Profile.
Also, you may want to disable password expiration for PTWEBSERVER, the user set by default to let the Web Server recover Web Profiles from PeopleSoft environment.Unfortunately, PeopleSoft does not provide the ability of disabling password expiration for a given user. Good news are that it is quite easy to do by setting the last password change date to a future date using the following SQL sentence:

update PSOPRDEFN

set LASTPSWDCHANGE = '2050-01-01'

where OPRID in ('SOLICITANTE', 'PS', 'PTWEBSERVER')

Wednesday, March 31, 2010

About Resume Template

About Resume Template:

We use resume templates to define the content that you want applicants to enter when they apply for jobs online.
To decide what the layout should be, think about these questions:
· How many pages do you want an applicant to complete when they apply for a job opening?
· What information do you want the applicant to provide regarding the job opening?
· In what order do you want the information presented to the applicant?

To set up resume templates, we use the Setup Resume Template (HRS_RES_TEMPLATE) component.
For the first page of the resume template, PeopleSoft delivers the following sections from which you can choose:
· Apply without using a resume
· Copy and paste resume text
· Upload a resume

Real time scenarios:
Scenario 1:
Suppose you have a Job opening and you want both Internal and External applicants to apply for the opening. But you want to present different set of screening questions to different groups. How can be it done?
Solution for this scenario would be to tie screening questions to Resume Template instead of Job Opening template and we set up different Resume templates to internal and external applicants.

Scenario 2:
A question of safety always comes when we allow applicants to upload their Resume through Candidate gateway. How to protect the system from any possible virus attack?
There are many ways to ensure the system is protected against any virus attack.
1. Continuously scanning while the Resume is being uploaded.
2. Creating a different web server for external applicants.
3. By using third party tools to monitor the actual attachments that are being sent via FTP connection.

Tuesday, March 30, 2010

Talent Acquisition Manager (TAM) Configuartion

Setting up Recruiting Processes
To use any module in peoplesoft, we have to do the one-time initial set up.
The one time initial setup of any module involves the set up of its related
1. Foundation Tables
2. Common Definitions
3. Other Products Related Tables(If your Module uses them)
4. Its own tables

1. Foundation Tables
You must set up these HR tables before you establish any other tables in the system. Following HR tables are used in TAM.
· Address information
· Business units
· Company
· Citizen Status
· Currency codes
· Departments
· Establishments
· Job Codes
· Job Families
· Locations
· Name information
· National IDs

2. Common Definitions
Recruiting Solutions uses the following common definition tables:
· Approvals
· Text Catalog

3. Product Related
Recruiting Solutions uses some of the following product related tables:
· Profile Management
· Organizational Development
· Contract Administration
· Labor Administration

4. TAM Tables set up
Following is the TAM related setup

a. Set up implementation defaults.
Initially we have to turn on Recruiting related modules of the Peoplesoft.
Recruitment related modules are
· Talent Acquisition Manager Module
· Candidate Gateway Module.

b. Set up Talent Acquisition Manager Implementation defaults.
This involves setting up
· Default template to be used
· If approvals are required
· To specify starting numerical numbers for Job Opening Id, Applicant Id, Vendor Id, etc

c. Set up job openings.
· Define recruiting locations.
We define recruiting locations because they will be used while searching for a job. For example, if an applicant is only interested in finding jobs in a specific region, you can create a recruiting location for that particular region and assign it to the job opening. The job search looks at the recruiting location associated to that job opening, and if a match is found, it displays that job opening to the applicant.
· Define recruiter roles.
Select the roles that you want to be available when assigning a recruiter to a job opening
· Create teams.
Teams are used to associate a group of recruiters, hiring manager, interviewers, or interested parties to a job opening.

d. Set up job postings.
Job Posting is nothing but building the text that makes up Job opening.
It Comprises of
· Defining the Posting description Types
Some of the Job description types are How to apply Information, Qualification, Roles and Responsibilities, Equal Employment opportunities or Department marketing statement.
Object Used: HRS_JO_PST_DSC_TBL
· Creating the Posting Description
The posting descriptions library provides the content that will go into each of the posting description types. You can enter the content at the time you create a job opening, but using the posting description library enables you to standardized the content and help the organization to provide some standardization between similar job postings.
Object Used: HRS_JO_PST_LIB_TBL

e. Set up applications.
Application set up involves defining the documents that can be accepted by the system from applicants and also limiting the number of documents that can be submitted. Some of the delivered attachment types are Resume, Reference, transcripts, cover letters, Personal Letters and Professional references.
Object Used: HRS_ATCHTYPE_TBL

f. Set up applicant communications.
It involves defining the applicant contact method Types, like Phone, fax, email, letters, web or others.
Peoplesoft uses XML Publisher to generate letters. There are 3 types of letters that can be printed. Applicant letter, Interview Letter and Offer Letter.
Object Used: HRS_CNTCT_MTHD

g. Set up questionnaires.
Questionnaires are used for screening the applicants. These questionnaires are completed by applicants when they apply for a job opening online.
It Comprises of
· Set up answer definitions.
We define the expected answer definition for each answer.
Object Used: HRS_ANSWER_DEF

· Set up question definitions.
We Create a question definition for each question and link answers to the question.
Object Used: HRS_QSTN_DEF

· Set up question set definitions.
Create a question set definition and link questions to the question set.
Object Used: HRS_QSTN_SET_DEF

h. Set up applicant screening.
Screening applicants is the process of identifying applicants whose qualifications match the requirements of a job opening, placing them in some sort of ranking order, narrowing the list, and then routing the best qualified applicants for interviews.
Object Used: HRS_SCREEN_TBL

i. Set up interview evaluations.
Three criteria make up an interview evaluation:
· Interview categories define the areas in which you want to evaluate applicants.
PeopleSoft delivers four categories: Communication Skills, Education/Training, Work Experience, and Technical skills. You can add as many categories as necessary.
Object Used: HRS_INT_CTG_TBL

· Interview ratings define how you rate the applicant within the category and associate a score to that rating.
PeopleSoft delivers three ratings: Excellent, which has a score of 2; Average, which has a score of 1; and Not Qualified, which has a score of 0. You can add as many ratings as necessary.
Object Used: HRS_INT_RTG_TBL

· Interview types define how you conduct the interview.
PeopleSoft delivers five interview types: Inhouse1, Inhouse2, Inhouse3, Campus, and Phone.
Object Used: HRS_INT_LVL_TBL

j. Set up job offers.
· Enter job offer component information.
Often, job offers comprise multiple elements, of which the base salary is just one component. You may offer applicants a bonus, stock options, or car allowances in addition to a salary. To track all elements of job offers, you need to set up job offer components and job offer component types for your organization.
Object Used:
· Create job offer component types
We use job offer component types to group similar offer components.
Object Used: HRS_OFF_TYPE_TBL

k. Set up the hiring process.
The Manage Hire Setup page enables you to select the information you want to transfer from the recruiting tables to the HR tables.
Object Used: HRS_MNG_HIR_STP


Overview of Talent Acquisition Manager (TAM)

Overview:
Talent acquisition manger (TAM) is the recruiting piece of peoplesoft.

This module enables us to create, capture and track the following:

1. Create a Job opening which consists of Job requirement description, Location of the Job posting and Hiring Company which can appear on the Intranet site.

2. Capture Applicant information like Resume, Cover letter, Transcripts, Personal references and others which are required as per Job opening set up.

3. Create a process of screening applicants to identify the best qualified applicants for a job opening, placing them in a ranking order and then route the best qualified applicants for the interviews.

4. Create Interview Evaluation Criteria which consists of areas in which you want to evaluate the applicants like communication skills, work experience and technical skills and then rate them based on the exhibited skill set at the interview.

5. Make a Job offer which consists of base salary and/or other benefits like bonus, stocks and other allowances.

6. Manage Hire process by selecting the information to be transferred from Recruiting tables to HR tables.

This diagram outlines the recruitment process flow used by Talent Acquisition Manager.