PeopleSoft Query Classes..

PS Query is one of the user-friendly reporting techniques delivered by PeopleSoft (PS).Many PS application end-users are capable of creating simple PS queries by themselves and make advantage of this tool.

Apart from mere reporting purpose, PS Queries can be used in few other cases.
PS Query in this discussion serves as a SQL place holder. The advantage of PS Query in playing a role of SQL place holder is obviously for its configurability through PIA. One of the scenarios where PS queries could be used little differently (than for their core reporting purpose is discussed below

The high level requirement is defined as follows:
"A process needs to be developed which would perform XYZ functionality on ABC (dynamic) set of employees. This process should not be scheduled but should be made available to run by end-users. End users should also have the previlege to create their own pool of people (ABC set) every time to perform or run the process"

Based on the above requirement, our tecnical design is to create an Application Engine with required run parameters. Obviously, the crux of the AE consists of the XYZ functionality coded. But the scope of discussion is about PS query usage. So, for the above requirement, our design can have a PS Query as one of the run control paramaters field(with a prompt record ex: PS_QRY_NAME_VW view). Lets assume that the security part is taken care of.

Here, the end users can create PS queries themselves by the criteria they want. And that they can select the query as one of the run control parameters while running the AE process.

Further, the AE can have a temp table which would have the results (rowset) of the query results. So here, we need to understand or learn how to deal with the Query objects/classes.

We need to understand the life cycle of the Query object(described in PeopleSoft API Reference Peoplebook). Worth showing the life cycle diagram.



Lets look a simple sample code which shows the usage of query classes:



Some of the points to note while dealing with Query objects or classes are:

1. The query classes expose all of the attributes and methods needed by the PeopleSoft Query Manager and Query Viewer applications.
2. Query classes are facilitate us to create / modify / run / monitor queries(by Peoplecode).
3. Easy way of accessing simple information about a query is using metadata property. [Ex:
4. Error handling of queries is achieved by simple code

5. Query results can be run to 4 differnt types:
> Run to File - use RunToFile method
> Run to Rowset - use RunToRowset method
> Run to String - use RunToString method
> Run to Template - use RunToTemplate method ((to generate the XML for a query as a feed)
I guess RuntoString and RuntoTemplate methods came up with Tools version 8.50. I have not seen them in Tools 8.49.X
5. "RuntoRowset" can have performance issues at times when the query results huge data. Hence this option has to be carefully chosen.

10 essential tasks a PeopleSoft Team Lead is expected to do..

This Post is little different from my other posts. Its different in my style of writing. This post has no beautiful descriptions, no hi-fi words used, no similies or metaphors. It is simple and straight so that the intent is communicated and received correctly. This post gives thumb rules to be a successful Team Lead in IT (rather my scope is PeopleSoft here) Services. We could rather call these "Essential tasks for a Team Lead"...

I have come up with the below tasks to be done by team lead after my little research at work and especially after making root-cause analysis of the failed projects or not-so-successful projects. I believe that the below tasks have to be made mandatory for the team leads so that after few projects, these would get imbibed in them and help them.


Assuming the general case - Team Lead is provided with the human resources (i.e. he/she has no option in selecting his/her team members, a resource allocator has already made up the team - with a decent combination of different levels of people with different relevant skill sets)
Apart from being techincally strong enough, a team lead should also have these as part of his duties:

1. The first time the team lead meets the team, he/she should have a general introduction of each team member - this gives him/her an opportunity to understand little about the attitude, the communication skills, the depth of the thought process the team members can get into.

2. After knowing the team members in general terms, it is good to meet the team again, may be next day and meet with an agenda of knowing the relevant technical(or functional) skills - every ones' strengths and weaknesses in domain. Thumb Rule is - Prepare an Excel sheet with the skill set matrix. Some of you might already be doing this but trust me there are many many team leads who donot carry out this task.

3. By doing task 2 above, the team lead knows his/her team members and their strengths and weaknesses. Now from the project output point of view, the strengths and the tasks should be mapped i.e. the task should be mapped to that member whose strength is solving that particular kind of task or issue.
But let us pause here. We also should look at increasing the learning and growth of the team members.
So lets do this - lets look at the timeslot for the deliverbles.
> If for a task, the deadline is not aggressive, lets give that task to a member who actually has little exposure on that task. This way, it gives the team member an opportunity to work on a task which he/she is not great at, which contributes to his learning and ultimately growth. If the time elapses and the deliverable is not completed by that team member, there is always another team member who has the strength of doing that. Remember, its ultimately the team work. But this loss of time should be predicted little earlier so that we dont end up losing all the time for that task or deliverable.

> And for the tasks which have aggressive time lines, lets map them to the team members with relevant skills as strengths.
Fair Enough.

4. Now the tasks are assigned accordingly. Next is the development. The tasks are allocated to the developers (team members). So they would start working on them but before they do this, there needs to be small activity done by the team lead - it does not matter if it takes some time.
This activity is very crucial in
a) avoiding re-work
b) avoiding losing track
c) making the team members understand the best practices, optimum solutions.

The activity is:
The team lead should give some time to the developers to think and arrive at different possible solutions to the given task/issue.
Then the team lead would discuss those possible solutions - the pros and corns of the suggested solutions by developer and ultimately choose or give an optimum solution to the problem.

5. Another thumb rule i would say is - Have official team meetings at least twice in a week. This helps the individual team members with a big picture of the project and gives an opportunity to think the impact of their designs of solution on other tasks.

6. The team lead should share the revised project plans periodically. This acquaints the team members with the MPP tool (if used for the project) and also aligns themselves to the time lines and makes them responsible in delivering their tasks.

7. The cushion or buffer time if exists should be used for peer reviews. Peer reviews should be encouraged as much as possible. this helps in two ways:
a) enhances quality - could avoid rare case bugs
b) eases the review work of team lead.

8. "A Stitch in time saves Nine" - Team Lead should keep check points much before delivery dates and have the code/design reviews. This discovers wrong approaches if any followed by developers and helps correcting them much ahead. The review by team lead should be thorough enough. Reviewing periodically much before is very essential.

9. The team lead should always check for the standards followed by the team members. If any deviation from standards are found, they should immediately modified.

10. As team lead has the vision and the big picture of the project, he/she should try to have maximum re-use of the solutions/code, etc.

There are many other duties or tasks of a team lead. But these are the ones which i think really can avoid escalations and makes the project lose out of control any time.

SQL for converting a number to words in crores, lakhs,..

Requirement:
I had a requirement that a number field/variable value has to be spelled in words in Indian System i.e. in crores, lakhs,etc.
Generic Solution:
Ex:
select initcap(to_char(to_date(1234567,'J'),'JSP')) from dual
This would produce the following output:
One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven
This usual approach matches with Indian system till tens of thousands.

Suggested Solution:
My approach was to substring the lakhs, crores and then hardcode the words 'crore' , 'lakh' and concatenate them. I used case statement because the might be dynamic and it should not have 'crore' or 'lakh' word harcoded when the number has no crores or lakhs in it.
The following SQL can be used to retrieve the o/p for numbers upto thousands of crores.. The SQL below has a constant value 1234567892 as the example, you can have your number fields from your tables in its place. Try it..

select CASE WHEN LENGTH(1234567892) >= 10 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,3),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 9 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,2),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 8 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,1),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 7 THEN initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 6 THEN initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,1),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) < class="blsp-spelling-error" id="SPELLING_ERROR_47">initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) END as words FROM DUAL

Checklist -- Requirements of Client

Few of the clarifications needed for setup data (HCM) :
I will keep adding more list items as and when found..

1. understand the number of and values of SETIDs and BUs
2. no.of companies
3. no. of countries
4. is it a multi lingual implementation?
5. aprroval hierarchy
6. when managers are in LOA, what will be the route for approvals
7. security
8. portal changes needed? (like Logo, header level changes, etc)
9. current no.of employees in the organisation (can be an estimated value)
10. third party/ vendors

Inserting files in Database record from a folder in appserver..

Requirement:

This is a part of data migration requirement. There is a folder in appserver which contains all CVs of applicants. These have to be inserted into database tables which store applicants' resumae. This should be a job like an App Engine.

Note: As per the specifications, one CV per applicant is given by the client.



Technical Design:

1) Initailly identify the tables which store appl CVs -- HRS_APP_RES

2) Create an AE with one section having a PeopleCode.

3) The folder path on appserver has to be identified.

4) The design needs two things --

• First loop through the Folder containing CVs


• Insert each file into the table

5) Here the CV files naming convention that is suggested as ApplicantID.doc. This makes the job easy in mapping the CVs against the applicants.

6) The peoplecode in the peoplecode event is as follows. This is very much reusable.

Local array of string &FNAMES;

Local File &MYFILE;

Local string &CurrFile;

Local Record &REC_RES_UPLD = CreateRecord(Record.ICI_RES_UPLD);

Local Record &REC_HRS_RES = CreateRecord(Record.HRS_APP_RES);
&pwd = "file://xyz.xyz.com/PQRS/ABCD/appserv/ASDFDEV/files/CV/";

&FNAMES = FindFiles(&pwd "*.doc", %FilePath_Absolute);

If &FNAMES.Len = 0 Then

/* No files in this directory - this must be a file instead */

MessageBox(0, "", 0, 0, "NO FILES IN THE DIRECTORY");

Else

&NEXT_RESUME_ID = GetNextNumberWithGapsCommit(HRS_APP_RES.HRS_RESUME_ID, 2147483647, 1);

&FILEEXTENSION = ".doc";

While &FNAMES.Len > 0

&CurrFile = &FNAMES.Shift();

&POS1 = Find("CV\", &CurrFile) + 3;

rem &POS2 = Find(".doc", &CurrFile) + 3;

&POS2 = Find(".doc", &CurrFile);

rem &applicant_id = substring(&CurrFile,&POS1,&POS2-&POS1+1);

&applicant_id = Substring(&CurrFile, &POS1, &POS2 - &POS1);

MessageBox(0, "", 0, 0, "EACH FILE NAME = " &CurrFile);

MessageBox(0, "", 0, 0, "EACH APPLICANT ID = " &applicant_id); &ATTACHSYSFILENAME = &applicant_id ".doc";

&ATTACHUSERFILE = &ATTACHSYSFILENAME;

&RETCODE = PutAttachment("record://ICI_RES_DOC_STR", &ATTACHSYSFILENAME, &CurrFile);

If &RETCODE = 0 Then

MessageBox(0, "", 0, 0, "File " &ATTACHSYSFILENAME " is inserted successfully.");

Else

MessageBox(0, "", 0, 0, "File " &ATTACHSYSFILENAME " is NOT inserted.");

End-If;

&REC_RES_UPLD.HRS_PERSON_ID.Value = &applicant_id; &REC_RES_UPLD.HRS_RESUME_ID.Value = &NEXT_RESUME_ID; &REC_RES_UPLD.ATTACHSYSFILENAME.Value = &ATTACHSYSFILENAME; &REC_RES_UPLD.ATTACHUSERFILE.Value = &ATTACHUSERFILE; &REC_RES_UPLD.Insert();

&REC_HRS_RES.HRS_PERSON_ID.Value = &applicant_id; &REC_HRS_RES.HRS_RESUME_ID.Value = &NEXT_RESUME_ID; &REC_HRS_RES.HRS_RESUME_TITLE.Value = "TEST"; &REC_HRS_RES.LANG_CD.Value = "ENG"; &REC_HRS_RES.ATTACHSYSFILENAME.Value = &ATTACHSYSFILENAME; &REC_HRS_RES.ATTACHUSERFILE.Value = &ATTACHUSERFILE; &REC_HRS_RES.HRS_ROW_ADD_DTTM.Value = %Datetime; &REC_HRS_RES.HRS_ROW_ADD_OPRID.Value = "PS"; &REC_HRS_RES.HRS_ROW_UPD_DTTM.Value = %Datetime; &REC_HRS_RES.HRS_ROW_UPD_OPRID.Value = "PS";

&REC_HRS_RES.Insert();

&NEXT_RESUME_ID = &NEXT_RESUME_ID + 1;

rem SQLExec("COMMIT");

End-While;

End-If;

Transactions which need approval -- Technical Design

Requirement:
A simple process is needed which allows employee to make a transaction with options of saving for later and submitting the transaction. This triggers a notification to next level for approval, say his supervisor. The supervisor should be able to approve/deny this transaction. It may have another level of approval which we don't discuss now..

Solution approach:
You can use an Application Workflow Engine(AWE) for this. But understanding and effort needed for this is slightly high. So let us look at the process which does not use an AWE.
One important Point: Understand what should happen when the employee 'Saves' the transaction and when he/she 'Submits' the transaction.
The following is the design technique.

1. Designing the records: First understand the transaction -- if it has to have child levels as well -- and design the tables accordingly -- Believe me this is very very important.. Designing the tables is very crucial. If the tables are not normalised, it definitely leads to rework later.
a) If Saving the transactions demands to store data in some intermediate SQL tables, then define them and expose those tables in the page.
b) Also try to have a field in level zero record which acts as flag for 'Save' or 'Submit' action. You can assign the flag field value accordingly when save button is clicked or submit button is clicked is its field change (preferably in component-record-field level Field change). This flag can be used in Search record views for filtering the data.
c) Have a status translate field in Level Zero record to track the transaction statuses like 'Saved by Employee', 'Submitted by Employee', 'Approved by Manager', 'Rejected by Manager', etc...

2. Designing Components: If it is one level approval, then there are two ways of doing it..
a) You will need two components with two search records -- one for employee and one for approving authority and make reuse of the page which u have created for employee and hide the approve/deny buttons or rename the lables accordingly for both the users. You will keep one component in Self Service and give access to Employees and other in Manager Self Service(MSS) and give permissions of manager.
b) You may reuse both page and component, and add the same component to Self Service as well as MSS menus with different search records defined at menu level.

If we look at second approach, whenever we have to add peoplecode at component/component-rec-field/component-rec levels, we always have to use conditions like
Evaluate %menu
when = "ROLE_EMPLOYEE"
.....
break;
when = "ROLE_MANAGER"
.....
break;
.....
end-evaluate;

So every validation that is put, will have to be in the above conditions.
So make a choice between the above.. I prefer the first method. Though it has a n extra component, it avoids hard-coding of menu names.

Also decide if delivered Saving process is needed or you have your own customised Save button. Because this requirement demands Save and Submit processes, it makes sense to have a cutomised Save as well as Submit buttons.

Modes of component(s):
Self Service component should have Add as well as Update /Display modes -- mandatory (other modes are optional)
Manager Self Service component should be in any of Update modes but only not in Add mode.

3. Search Records: The next step will be properly defining the search records. Again very important as this determines data security.

• For the Self Service component, we can have a view on Level Zero record such that the employee picked up by the view will be %employeeid or u can some times have Level Zero table itself as the search record. But now we have Save button also for employee, it is better to have Level Zero record as Add Search Record and a view which shows Saved transactions and Approved/Denied transactions as Search Record.
• For the MSS component, the search record should definitely be a view (makes life easy). This view can have the level zero table as well as a filter on the status like submitted ones only (not saved ones), or don't include approved and(or) rejected transactions, etc.

4. Validations/Considerations: The most common validations might be --

• In the Post Build event of SelfService component -- Dont forget to add the statement -- AllowEmplidChng(); [Refer PeopleBooks]
• In page activate if you have some code to hide/unhide some page controls then dont forget to write it in an 'If' condition for component (i.e. if %component = "...." then ... ) because same page is exposed in both the component.
• Logically speaking -- after employee Submits a transaction (i.e. after he/she clicks the 'Submit' button) the entire page should be in display only(disabled) mode. So that the submitted transaction is not allowed to modify. For this, the best way is to use 'Transfer' function and transfer back to the current page with proper key values. This piece of code can be written in fieldchange (component-rec-field level) of the Submit button field.
• You can reuse the same Save and Submit button fields as Approve and Deny buttons. All that you need is to change the label name in may be Post build event. [Refer field class properties/methods in PeopleBooks]
• In the search mode for an employee, if you are showing Submitted/Approved/Denied transactions, make sure that you write make the page Display only in Post Build event of the component. [Ex. If Flag = "X" then GetPage(Page.XXXXX).Displayonly = true; ..........]
• Dont forget to change the status field using code or allow the user to change the status according to his/her role.
• Code to trigger email notifications should be written at component-rec-field level field change or in workflow events of the component. If there is no delivered Save button, then make sure you trigger the workflow event (by using DoSave() or DoSaveNow() functions).

5.Email Notifications: The Email notifications might have to be sent to approving authorities and employee also. This can be achieved by using WorkfFlows or by calling an application package with a mail template defined. This part is discussed in detail in the blog 'Different ways of trigerring Notifications'.

Steps in Implementation - An overview

Implementing PeopleSoft (implementing not upgrading) generally includes the following types of technical tasks:
> Convertions
> Extensions (customizations)
> Interfaces
> Reports and
> Migration

Convertions:
Broadly, this includes the transfer of entire legacy data (or data from third party) of client to PeopleSoft database. This data will basically be application data. In layman's term, it is dumping of existing client's data into PeopleSoft system. This is usually a one time activity as a part of implementation.

Extensions:
This deals with customizing all the gaps in PeopleSoft. These gaps may demand either bolt-on (new feature/enhancement) solution or customizing the delivered feature.

Interfaces:
Interfaces may be needed for communicating ( data tranfer) from PeopleSoft aplication to third party or vice versa. These may be maintainable objects and may also have to be monitored.

Reports:
Reports are hot favorities mainly for client's managers. These reports can be for audit purpose, for planning purpose. Take care about their layouts and consolidation (if any) of data.

Migration:
Migration involves moving of projects from one environment to the other viz dev to config, config to test/UAT, test /UAT to SIT/production.