%Bind [NOQUOTES]

You might have used %Bind ([record name.]Fieldname [, NOQUOTES]) while you code. It might be a confusing part for some people, on why NOQUOTES is used with %bind.

If you are selecting the character field from the database, it will be enclosed in quotes. Use NOQUOTES to remove that quotes while selecting.

For example say you are selecting the record PS_TEST from a table to use it in the AE SQL action. While selecting it the record name will be enclosed in quotes, which will error out the SQL action. There you can use NOQUOTES for removing the quotes. This is a typical example for information.

Anoop Savio

PS Meta tables

You might have came across this info previously. Its good to refresh it at times.

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: Contains 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.

CASE FIELDTYPE
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 <> TO_DATE('03-DEC-07', 'DD-MON-YY') AND LAST_UPDATE_DTTM <>> 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 objects
1 = Application engine SQL
2 = 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 Activity

PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

Tino Simon

Custom Navigation Collection and Pagelet

When we are working on PIA we often feel that we are visiting the same areas within the system regularly.

So to make our life easier we can create a Pagelet based on a custom Navigation Collection containing our often used components. This greatly reduces the number of clicks required for day-to-day work and could be really useful for end users.

Creating the Navigation Collection

First, let’s create the Navigation Collection. You can have whatever menus/components you want in your collection.

Go to PeopleTools > Portal > Portal Utilities > Navigation Collections

Add a Collection with an appropriate Name and Description Click Add Folder Select Menu Folder, from the Employee Portal. Use the prompt to locate the folder to add, click the plus folder next to a high level menu to expand it and choose a child menu. Highlight the top level, and click Add Folder again. Repeat the process until you have added all your folders.

We can also add links directly to the Components we used most often, like Process Monitor, and moved them to the top of the list. Click Save (and remember the name of your new Collection). Publishing the Navigation Collection as a Pagelet

Go to PeopleTools > Portal > Pagelet Wizard > Pagelet Wizard

Add a New Pagelet and fill out the Title etc Choose a Data Type of Navigation Collection Choose a Portal of Employee and prompt to find your Navigation Collection. We normally reduce the Max Child Links to 5. I choose the Menu layout. I set the ‘Parent Images’ = Small, Turn off ‘Parent Descriptions’ and set the ‘Child Display Type’ = Simple List.

On the final page, check HomePage Pagelet, choose a folder, and set the security required.That’s your Pagelet done. Now add it to your Homepage.

Adding a Pagelet to your Homepage From the PIA Homepage, select the ‘Personalize Content’ link. Place a Check in the Checkbox next to the Pagelet you want, then click Save. Click the Personalize Layout link. Select whether you want a 2 or 3 column layout, and move the new Pagelet to the column you wish it to appear in, click Save.

That’s how you can create your own Custom Navigation Collection and Pagelet.

Original link: PeopleSoft Tipster

TREE generation using PeopleCode

Creating the tree for security related purpose; you might have came across the same, in the course of development.

We create it under: Menu > Tree manager > Tree manager

Note: For getting an overview of tree manager: PeopleBooks > PeopleTools 8.51 PeopleBooks: PeopleSoft Tree Manager > Introduction to PeopleSoft Tree Manager

Note: Download page and record design document to see the corresponding structures

Note: Download peoplecode and SQL for the tree generation

Opening any tree you can see the nodes. Parent node, child nodes, sibling nodes and whole other tree properties.

Every time there happens a department structure change, when we take the example of department tree as an example, when the numbers of employees are more in the organization, it is pretty much tough affair to structure all the employees under the new management structure. What if we have a code to fetch the employees reporting to a manager/supervisor of a department and assign them through peoplecode?

Below is the code to create a tree using peoplecode. This one is tested and verified code. If you have any doubts related to the same, you can drop a e-mail.

Note: Keep the Tree API open before you go through the code below: PeopleBooks > PeopleTools 8.51 PeopleBook: PeopleCode API Reference > Tree Classes

Note: Keep this tip in the mind. The parent node is similar to the level0 fields, the child rows are similar to level1, level2 fields. We can have only upto 3 levels for a component, but can have n number of levels in case of tree. I have used upto 12 levels in this code.

Consider the tree creation using peoplecode as one part. The tree will be created in the database. How to display this tree in a custom page, where when a person enters, consider a supervisor comes to this page, he should be able to view the details of his subordinates only. This cannot be achieved using row level security. Can you tell the reason? (Just think about it.)

The code given below meets the above two requirements.

The application engine peoplecode is to create the tree structure using the tree api objects.
The component level peoplecode is to render the tree structure, based on the employee who logs in to the custom page. When a supervisor logs in, the tree structure data starting from his position is fetched from the database and displayed. Hence the supervisor becomes the parent node and all the subordinates are the child rows.

You can see one HTML area (refer to the record AS_DERIVED_HR in the design doc). That while embedded in the custom page, renders the tree structure as we can see it in the tree manager page. TREECLTEVENT (refer to the record AS_DERIVED_HR in the design doc), is like the push button which get the control from the HTML area while expanding/collapsing the area.

Go through the code. Feel free to comment to this post, in case of any doubts. It will be answered.

See the peoplecode and SQL below:

Appendix 1: PeopleCode
Field: OPRID
Event: ROWINIT
AS_SUPER_RUN.OPRID = %OperatorId

Field: RUN_CNTL_ID
Event: SAVEEDIT
&POS = Find(" ", AS_SUPER_RUN.RUN_CNTL_ID);
If &POS > 0 Then
Error MsgGet(65, 33, "Run Control Ids may not contain embedded spaces");
End-If;
&POS = Find("%",AS_SUPER_RUN.RUN_CNTL_ID);
If &POS > 0 Then
Error MsgGet(99, 99, "Run Control Ids may not contain %");
End-If;

Component: AS_SUPVTREE
Event: PostBuild
Component Rowset &rsTree;
Local Rowset &rsNode;
Local ApiObject &apiRootNode;
Local ApiObject &apiSession;
Local ApiObject &apiTree;
Local Record &recTree;
Local boolean &boolRes;
Local string &boolFlag;
Local date &dtEffDt;
&rsNode = CreateRowset(Record.AS_SUPVTREE_DTL);
&rsTree = CreateRowset(Record.AS_SUPVTREE_HDR, &rsNode);
&rsTree.InsertRow(1);
&recTree = &rsTree.GetRow(2).GetRecord(1);
&recTree.GetField(Field.PAGE_NAME).Value = "AS_SUPVTREE";
&recTree.GetField(Field.PAGE_FIELD_NAME).Value = "TREECTLEVENT";
&recTree.GetField(Field.PAGE_SIZE).Value = 500;
&recTree.GetField(Field.DISPLAY_LEVELS).Value = 12;
&recTree.GetField(Field.COLLAPSED_IMAGE).Value = "PT_TREE_COLLAPSED";
&recTree.GetField(Field.EXPANDED_IMAGE).Value = "PT_TREE_EXPANDED";
&recTree.GetField(Field.END_NODE_IMAGE).Value = "PT_TREE_END_NODE";
&recTree.GetField(Field.LEAF_IMAGE).Value = "PT_TREE_LEAF";
&recTree.GetField(Field.IMAGE_WIDTH).Value = 15;
&recTree.GetField(Field.IMAGE_HEIGHT).Value = 12;
&recTree.GetField(Field.INDENT_PIXELS).Value = 20;
&apiSession = %Session;
&apiTree = &apiSession.GetTree();
SQLExec("SELECT %DateOut(A.EFFDT) FROM PSTREEDEFN_VW A WHERE
A.TREE_NAME=:1", "AS_SUPERVISOR1", &dtEffDt);
&boolRes = &apiTree.Open("", "", "AS_SUPERVISOR1", &dtEffDt, "", False);
SQLExec("SELECT TREE_NODE FROM PS_AS_NODE_EMPL WHERE EMPLID=:1",
%EmployeeId, &strTreeNode);
rem &apiRootNode = &apiTree.FindnODE(&strTreeNode, "");
&apiRootNode = &apiTree.FindnODE(%EmployeeId, "");
If All(&apiRootNode) Then
If &apiRootNode.HasChildren Then
&strFlag = "X";
Else
&strFlag = "N";
End-If;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&rsNode.InsertRow(1);
&recTree = &rsNode.GetRow(2).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "N";
SQLExec("SELECT DEPTID FROM PS_JOB_CURR_VW WHERE EMPLID=:1",
&apiRootNode.NAME, &strTreeNode);
&recTree.GetField(Field.TREE_NODE).Value = &strTreeNode;
&recTree.GetField(Field.TREE_NODE_NEW).Value = &apiRootNode.NAME;
&recTree.GetField(Field.DESCR).Value = &apiRootNode.DESCRIPTION;
&recTree.GetField(Field.RANGE_FROM).Value = "";
&recTree.GetField(Field.RANGE_TO).Value = "";
&recTree.GetField(Field.DYNAMIC_FLAG).Value = "N";
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = &strFlag;
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = 0;
&apiTree.Close();
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree);
Else
MessageBox(0, "", 20006, 30, "Message not Found");
End-If;

Record: AS_DERIVED_HR
Field: TREECTLEVENT
Event: FIELDCHANGE
Component Rowset &rsTree;
Local Rowset &rsNode;
Local Record &recTreeParent, &recTree;
Local integer &intRow, &intParentLevel, &intLevelOffset;
Local ApiObject &apiSession, &apiTree, &apiParentNode, &apiChildLeaf, &apiChildNode;
Local date &dtEffDt;
Local boolean &boolRes, &boolFirst;
Local string &strRangeFrom, &strRangeTo, &strDynamicRange, &strParentFlag, &strTemp;
If Left(AS_DERIVED_HR.TREECTLEVENT, 1) = "X" Then
&intRow = Value(Right(AS_DERIVED_HR.TREECTLEVENT,
Len(AS_DERIVED_HR.TREECTLEVENT) - 1)) + 1;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&recTreeParent = &rsNode.GetRow(&intRow).GetRecord(1);
&intParentLevel = &recTreeParent.GetField(Field.TREE_LEVEL_NUM).Value;
&intRow = &intRow + 1;
&apiSession = %Session;
&apiTree = &apiSession.GetTree();
SQLExec("SELECT %DateOut(A.EFFDT) FROM PSTREEDEFN_VW A WHERE
A.TREE_NAME=:1", "AS_SUPERVISOR1", &dtEffDt);
&boolRes = &apiTree.Open("", "", "AS_SUPERVISOR1", &dtEffDt, "", False);
&apiParentNode =
&apiTree.FindNode(&recTreeParent.GetField(Field.TREE_NODE_NEW).Value, "");
If &apiParentNode.HasChildren Then
&apiParentNode.Expand(2);
If &apiParentNode.HasChildLeaves Then
&boolFirst = True;
&apiChildLeaf = &apiParentNode.FirstChildLeaf;
While &boolFirst Or
&apiChildLeaf.HasNextSib
If &boolFirst Then
&boolFirst = False;
Else
&apiChildLeaf = &apiChildLeaf.NextSib;
End-If;
If &apiChildLeaf.Dynamic = True Then
&strRangeFrom = "";
&strRangeTo = "";
&strDynamicRange = "Y";
Else
&strRangeFrom = &apiChildLeaf.RangeFrom;
&strRangeTo = &apiChildLeaf.RangeTo;
&strDynamicRange = "N";
End-If;
&rsNode.InsertRow(&intRow - 1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "Y";
&recTree.GetField(Field.TREE_NODE).Value = "";
&recTree.GetField(Field.DESCR).Value = "";
SQLExec("SELECT DEPTNAME,JOBCODE_DESCR FROM PS_AS_SUPER_VW WHERE
SUPERVISOR_ID=:1", &strRangeFrom, &strDescrFrom, &strDescrTo);
&recTree.GetField(Field.RANGE_FROM).Value = &strDescrFrom;
&recTree.GetField(Field.RANGE_TO).Value = &strDescrTo;
&recTree.GetField(Field.DYNAMIC_FLAG).Value = &strDynamicRange;
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = "N";
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = &intParentLevel + 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = 0;
&intRow = &intRow + 1;
End-While;
End-If;
If &apiParentNode.HasChildNodes Then
&boolFirst = True;
&apiChildNode = &apiParentNode.FirstChildNode;
While &boolFirst Or
&apiChildNode.HasNextSib
If &boolFirst Then
&boolFirst = False;
Else
&apiChildNode = &apiChildNode.NextSib;
End-If;
If &apiChildNode.HasChildren Then
&strParentFlag = "X";
Else
&strParentFlag = "N";
End-If;
If &apiTree.LevelUse = "S" Then
&intLevelOffset = &apiChildNode.LevelNumber - &apiParentNode.LevelNumber - 1;
Else
&intLevelOffset = 0;
End-If;
&rsNode.InsertRow(&intRow - 1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "N";
SQLExec("SELECT DEPTID FROM PS_AS_SUPER_VW WHERE SUPERVISOR_ID=:1",
&apiChildNode.Name, &strTreeNode);
&recTree.GetField(Field.TREE_NODE).Value = &strTreeNode;
&recTree.GetField(Field.TREE_NODE_NEW).Value = &apiChildNode.Name;
&recTree.GetField(Field.DESCR).Value = &apiChildNode.Description;
&recTree.GetField(Field.RANGE_FROM).Value = "";
&recTree.GetField(Field.RANGE_TO).Value = "";
&recTree.GetField(Field.DYNAMIC_FLAG).Value = "N";
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = &strParentFlag;
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = &intParentLevel + 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = &intLevelOffset;
&intRow = &intRow + 1;
End-While;
End-If;
&recTreeParent.GetField(Field.PARENT_FLAG).Value = "Y";
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree,
AS_DERIVED_HR.TREECTLEVENT);
End-If;
&apiTree.Close();
Else
If Left(AS_DERIVED_HR.TREECTLEVENT, 1) = "S" Then
&intRow = Value(Right(AS_DERIVED_HR.TREECTLEVENT,
Len(AS_DERIVED_HR.TREECTLEVENT) - 1)) + 1;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
If &recTree.GetField(Field.LEAF_FLAG).Value = "N" Then
SQLExec("SELECT NAME_DISPLAY'-'DESCR100 FROM PS_AS_SUPER_VW A WHERE
A.SUPERVISOR_ID=:1", String(&recTree.GetField(Field.TREE_NODE_NEW).Value),
&strDescr100);
MessageBox(0, "", 0, 0, "The selected node is %1.", &strDescr100);
Else
If &recTree.GetField(Field.DYNAMIC_FLAG).Value = "N" Then
SQLExec("SELECT DESCR100 FROM PS_AS_SUPER_VW A WHERE
A.SUPERVISOR_ID=:1", String(&recTree.GetField(Field.TREE_NODE).Value),
&strDescr100);
REM &recTree.GetField(Field.RANGE_FROM).Value = &strDescr100;
REM &recTree.GetField(Field.RANGE_TO).Value = &strDescr100;
If &recTree.GetField(Field.RANGE_FROM).Value =
&recTree.GetField(Field.RANGE_TO).Value Then
&strTemp = "[" &recTree.GetField(Field.RANGE_FROM).Value "]";
Else
&strTemp = "[" &recTree.GetField(Field.RANGE_FROM).Value " - "
&recTree.GetField(Field.RANGE_TO).Value "]";
End-If;
REM &strTemp = &strDescr100;
Else
&strTemp = "[ ]";
End-If;
MessageBox(0, "", 0, 0, "The selected leaf is %1.", &strTemp);
End-If;
Else
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree,
AS_DERIVED_HR.TREECTLEVENT);
End-If;
End-If;
AS_DERIVED_HR.TREECTLEVENT = "";

Appendix 2: SQL
SQL ID: AS_EMPRPTEMP_SQL
SELECT A.EMPLID
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID =B.EMPLID
AND A.SUPERVISOR_ID=A.EMPLID
AND EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
PeopleCode and SQL’s mytechspeak.com
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=%CurrentDateIn) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND SUPERVISOR_ID<>' '
AND A.SUPERVISOR_ID=A.EMPLID
AND A.HR_STATUS='A'

SQL ID: AS_SUPER_EMP_SQL
SELECT A.EMPLID
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID=B.EMPLID
AND SUPERVISOR_ID=:1
AND EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=%CurrentDateIn) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND A.EMPLID<>A.SUPERVISOR_ID
AND SUPERVISOR_ID<>' '
AND A.HR_STATUS='A'
ORDER BY A.EMPLID

View: AS_SUPER_VW
SELECT DISTINCT A.EMPLID
,B.FIRST_NAME
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID=B.EMPLID
AND A.EFFDT= (
SELECT MAX (EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=SYSDATE) AND A.EFFSEQ= ( SELECT MAX (EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND SUPERVISOR_ID<>' '
UNION
SELECT 'SUPER'
,'super'
FROM DUAL

SQL ID: AS_EMP_SUP_SQL
SELECT DISTINCT EMPLID
, SUPERVISOR_ID
,LEVEL
FROM PS_AS_EMP_SUP_TBL START WITH SUPERVISOR_ID=:1 CONNECT BY NOCYCLE
PRIOR EMPLID=SUPERVISOR_ID
ORDER BY LEVEL

SQL ID: AS_INSERT_SUP
INSERT INTO PS_AS_EMP_SUP_TBL
SELECT EMPLID
,SUPERVISOR_ID
FROM PS_JOB A
WHERE EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=SYSDATE) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND A.HR_STATUS='A' AND A.PER_ORG='EMP'

Appendix D: AppEngine
Action: PeopleCode
Local ApiObject &apiSession, &apiLevel;
Component ApiObject &treeSupervisor;
Local SQL &sqlSuperEmployees, &sqlSwSupEmpSql;
Local number &numReturnDelete, &counter, &numLevel;
Local string &strEmplid, &strSupervisor;
SQLExec(SQL.AS_EMPRPTEMP_SQL, &strEmployeeSupervisor);
&apiSession = %Session;
&treeSupervisor = &apiSession.GetTree("", "", "", "");
&treeSupervisor.Delete("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE");
If Not &treeSupervisor.Create("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE") = 0 Then
&numReturnDelete = &treeSupervisor.Open("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE");
Else
&treeSupervisor.Description = "Supervisor Tree";
&treeSupervisor.Category = "DEFAULT";
&apiLevelCollection = &treeSupervisor.levels;
For &numI = 1 To 12
&apiLevel = &apiLevelCollection.add("SUPLVL:" &numI);
&apiLevel.description = "Level" &numI;
End-For;
End-If;
SQLExec("DELETE FROM PS_AS_SUPER_TBL");
SQLExec("INSERT INTO PS_AS_SUPER_TBL SELECT
SUPERVISOR_ID,substr(NAME_DISPLAY,1,30) FROM PS_AS_SUPER_VW");
SQLExec("DELETE FROM PS_AS_EMP_SUP_TBL");
SQLExec(SQL.AS_INSERT_SUP);
&nodeRootNode1 = &treeSupervisor.InsertRoot(&strEmployeeSupervisor);
&nodeRootNode1.InsertChildLeaf(&strEmployeeSupervisor, &strEmployeeSupervisor);
&treeSupervisor.Save();
AS_SUPER_AET.SUPERVISOR_ID = &strEmployeeSupervisor;

Step: SupTree2
Action: DoSelect
%Select(EMPLID,SUPERVISOR_ID ,LEVELS)
SELECT DISTINCT EMPLID
, SUPERVISOR_ID
,LEVEL
FROM PS_AS_EMP_SUP_TBL START WITH SUPERVISOR_ID=%Bind(SUPERVISOR_ID)
CONNECT BY NOCYCLE PRIOR EMPLID=SUPERVISOR_ID
ORDER BY LEVEL

Step: SupTree2
Action: Peoplecode

Component ApiObject &treeSupervisor;
&nodeRootNode1 = &treeSupervisor.FindNode(AS_SUPER_AET.SUPERVISOR_ID, "");
&counter = &counter + 1;
If All(&nodeRootNode1) And
&treeSupervisor.NodeExists(AS_SUPER_AET.EMPLID) Then
&nodeRootNode1 = &nodeRootNode1.InsertChildNode(AS_SUPER_AET.EMPLID);
&nodeRootNode1.InsertChildLeaf(AS_SUPER_AET.EMPLID, AS_SUPER_AET.EMPLID);
&treeSupervisor.Save();
SQLExec("commit");End-If;


Manivannan Ramar

%SQLrows

While executing INSERT/DELETE/UPDATE statements you can see the number of rows effected displayed at the bottom of the sqldeveloper, as shown in the screenshot below.


To capture the number of effected rows, during conversion/interface run, to properly audit the actual number of rows effected, %SQLrows can be used.

PeopleBooks Definition:

%SQLRows returns the number of rows affected by the most recent UPDATE, DELETE, or INSERT executed through the SQLExec function.

%SQLRows can also be used after SELECT. It returns 0 if no rows are returned, a non-zero value if one or more rows are returned. In this case, the non-zero value does not indicate the total number of rows returned.

This will be useful and handy at times.

Manivannan Ramar

Implementing Search/Match

To use the full functionality of your system, you must maintain the integrity of your database. With users from many departments entering data, you want to minimize the entry of duplicate or multiple records. Search/Match enables you to define criteria to check for duplicate or multiple ID records. The searchable ID types (called Search Types) are:

• Person (emplID).
• Organization (organization IDs within PeopleSoft Enterprise Campus Solutions).
• Applicant (HRS_PERSON_ID within PeopleSoft Enterprise HRMS Talent Acquisition Manager).

People books have reference to the search match codes already delivered in the system. They are found in the following application engine sections.

• Search/Match/Post Test Scores (SAD_TEST_PST).
- See the SrchMtch section in the program
• CBAP Post (SAD_CPST_AE).
- See the SrchMtch section in the program, Step 01

Prior to implementing the code, make sure that you have created the Search/Match rules and Search/Match Parameters, to meet your business requirement.

Navigations:
Set Up SACR, System Administration, Utilities, Search/Match, Search/Match Rules
Set Up SACR, System Administration, Utilities, Search/Match, Search/Match Parameters, Search Parameters

Being setup the above two, you can also define permissions and exceptions.

Refer the Search/Match feature in PeopleBooks, for more information on the same.
So you have seen the delivered code for the searchmatch

The code has the object &PersonSrchType, which is used to compare the columns that are being passed for the search/match.

SEARCH_ADD_PARM & SEARCH_ORDR_PRM: The first record has the Search/Match parameter used for a particular job and the second search/match, has the parameter order number.

HCR_SM_CRIT_VW table has the parameter and the rule mapping, which is done in the Search/Match Parameters

Now how to implement that code in a better way.

You can create a function using the field formulae and put this code, by modifying it according to your search parameter and the rules.

Then call that function using the Declare function:

Declare Function USTSearchMatch PeopleCode UST_DERIVED_112.FIELDNAME FieldFormula; /* search match function to find whether Person exists in the PeopleSoft system*/

Then pass the parameters to this function by creating a derived record and assigning the values to it one by one.

/* Search Match */

&fileLog.WriteLine("Selected student For Search Match: " UST_I112_AET.UST_EMPLID.Value);
&SearchMatchTblRec = CreateRecord(Record.UST_DERIVED_SM);
&SearchMatchTblRec.FIRST_NAME_SRCH.VALUE = Upper(&First_Name_srch);
&SearchMatchTblRec.LAST_NAME_SRCH.VALUE = Upper(&Last_Name_srch);
&SearchMatchTblRec.BIRTHDATE.VALUE = &BirthDate;
If All(&National_ID) And
&Flag = "R" Then
&SearchMatchTblRec.NATIONAL_ID.VALUE = &National_ID;
End-If;
&SearchMatchTblRec.EXTERNAL_SYSTEM_ID.VALUE = &National_ID;
&SearchMatchTblRec.EXTERNAL_SYSTEM.VALUE = "LID";
&rs_PersonSrchRules = CreateRowset(Record.HCR_SM_CRIT_VW); /* delivered method/row set */
&PARM_CD = UST_I112_AET.SM_PARM_CD.Value;
SQLExec("SELECT DISTINCT SM_TYPE FROM PS_HCR_SM_ORDR_TBL WHERE SM_PARM_CD=:1", &PARM_CD, &SMTYPE);
&rs_PersonSrchRules.Fill("where SM_TYPE = :1 and SM_PARM_CD = :2", &SMTYPE, &PARM_CD);
&rs_PersonSrchRules.Sort(HCR_SM_CRIT_VW.SEARCH_RULE_NBR, "A", HCR_SM_CRIT_VW.SEQNO, "A");
&Processing_Code = USTSearchMatch(&rs_PersonSrchRules, &SearchMatchTblRec); /* search match function */

The &Processing_Code returns the value from the function call, which tells the result, whether the person exist in the system or not.

Write an evaluate statement to assess the &Processing_Code variable as given below.

Evaluate &Processing_Code
When = "A" /* ADD */
/* The person doesn’t exist in the system. Write the code to add the person */
Break;

When = "U" /* UPDATE */
/* The person exist in the system. Write the code to update the person information */
Break;

When = "S" /* SUSPEND */
/* The person with the details already exist in the system. Write the code to update the flag for that row to suspend the processing */
Break;

When-Other /* IGNORE */
/* Invalid person ID. Write the code to write the respective details into a log file */
Break;

End-Evaluate;

Hurray! So you have done implementing the Search/Match feature. If you suggestions to make the code even more better do mail us or post comment for this post.

Anoop Savio

Messages with Style

I have often wanted to display an informational message at the top of the page. Rather than just slap a Long Edit Box at the top of the screen, I decided to look for a nice looking way to do it. One of the great things about PeopleSoft, it always has an example somewhere in the huge product for what you are trying to do.

Here are some of the messages that I found:

Information Message:

Example Page: SSS_STDNTCTR_SR_SP

Here are the different pieces:

• Stylesheet of the page: SSS_STYLESHEET
• Group Box:
  • Label Stylesheet: PAGROUPBOXLABELINVISIBLE
  • Body Stylesheet: SSSMSGINFOFRAME
• Static Image:
  • Image: PS_CS_MESSAGE_INFO_ICN
  • Size: 23×23
• Static Text:
  • Style: SSSMSGINFOTEXT

Alert Message:

Example Page: SSF_SS_ERRORMSG

Here are the different pieces:

• Page Stylesheet: SSS_STYLESHEET
• Group Box
  • Label Stylesheet: PAGROUPBOXLABELINVISIBLE
  • Body Stylesheet: SSSMSGALERTFRAME
• Push Button (for the image, you could use a static icon instead)
  • Image: PS_CS_MESSAGE_ALERT_ICN
  • Size: Large Icon
• Long Edit Box (works the same as the static textbox, but lets you change it from code)
  • Stylesheet: SSSMSGALERTTEXT
  • No Label

Ganesh M

Useful SQL's

General:

Alter session - so you do not have to preface everything with user.table
alter session set current schema=;

Example: If I needed to access a table(booklist) under the js schema and I was logged in at mj I could do the following.

* select count(*) from js.booklist;

or

* alter session set current schema=js;
* select count(*) from booklist;

Alter session works very well when you are working constantly in another schema like peoplesoft.

Load Specific:

I use some oracle specific sql some times.

Process Scheduler:

Rundates with the number of processes that were queued when an item was scheduled to run

select to_char(a.rundttm,'yyyy-mm-dd hh24:mi:ss') rundate, count(*) from ( select distinct rundttm from sysadm.ps_pmn_prcslist) a join sysadm.ps_pmn_prcslist b on a.rundttm between b.rundttm and b.enddttm group by a.rundttm;

How long processes are talking to run

select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || (a.ENDDTTM-a.BEGINDTTM) * 1440 as ProcessIDProcessNameMinutesTaken from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;

select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || floor(((((a.ENDDTTM-a.BEGINDTTM)*24*60*60)/3600)*3600)/60) as IdNameMinutes from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;

CSV export of the process scheduler table

select OPRID || ',' || PRCSINSTANCE || ',' || PRCSTYPE || ',' || PRCSNAME || ',' || to_char(rundttm,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(BEGINDTTM,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(ENDDTTM,'yyyy-mm-dd hh24:mi:ss') from sysadm.ps_pmn_prcslist;

Tino Simon

Peopletools 8.51

About one year after Peopletools 8.50, the next generation, Peopletools 8.51 is now available.

As usual, download it from EDelivery and don’t forget the first patch 8.51.01 to be applied, to be download from FTP website.

The documentation is also available online on the OTN hosted Peoplebooks.

Original Link: http://gasparotto.blogspot.com/

Peoplecode Trace in a File.

Local File &fle;
&fle = GetFile(GetCwd() | "/files/Test.xml", "W", %FilePath_Absolute);
&fle.WriteLine("Hi");
&fle.Close();

Test.xml can Found in...

Web Server: psoft > appserv > DATABASENAME > files > Test.xml

Lokesh Madnavat

Campus Solutions: Financial Aid overview

While the knowledge transfer section was going for the Financial Aid module, I realized the fact to know the functional side of Financial aid provision would add to the knowledge. And, my manager was insisting on the same, which made me to have a seek on the FA provisioning.

Student financial aid refers to funding intended to help students pay educational expenses including tuition and fees, room and board, books and supplies, etc. for education at a college, university, or private school. General governmental funding for public education is not called financial aid, which refers to awards to specific individual students. Certain governments, e.g. Nordic countries, provide student benefit. A scholarship is sometimes used as a synonym for a financial aid award, although grants and student loans are also components of financial aid packages from students' intended colleges.

Financial aid may be classified into two types based on the criteria through which the financial aid is awarded: merit-based or need-based.

Student aid is awarded as grants and scholarships, low-interest, government-subsidized loans, and education tax benefits, and nearly everyone is eligible for some of it.

In the U.S. to apply for most student aid, a student must first complete the Free Application for Federal Student Aid (FAFSA) by submitting the application electronically to the U. S. Department of Education's using the Department of Education's Web site, or as the law also authorizes, by getting professional assistance from a fee-based preparer. A student's aid application (FAFSA) may be submitted to the Department of Education as early as January 1 before the summer or fall when the student enrolls and must be re-submitted with updated income, asset, and dependency information each year.

The Department of Education processes each request and tells a student how much the federal government expects your family to contribute towards paying for college - the Expected Family Contribution (EFC). However, an EFC is not necessarily how much a student will pay for college - aid can reduce an individual's cost. Then, the post-secondary institutions to which a student applies determine how much federal, state, and college-specific aid a student will receive. An individual's student aid award is likely to vary from institution to institution.

Most student aid is federal aid – people's tax dollars working for students. Most federal student aid is awarded as grants and low-interest loans. Grant programs include the Pell Grant, the Academic Competitiveness Grant, the TEACH Grant, and the SMART Grant. Grants are best because they are "free money" – they don't have to be repaid as long as a student meets any obligations they may have.

The federal loan programs include the Federal Direct Subsidized and Federal Direct Unsubsidized Loans, the Perkins Loan, and the Parent PLUS (Parental Loan for Undergraduate Students) Loan and Graduate PLUS (a loan for Graduate students). Unlike with federal grants, a borrower must repay the loan amount and any interest. Federal loans offer lower interest rates and better repayment terms than private student loans from banks and other financial institutions.

Students (or their parents/guardians) can take advantage of education tax benefits to ease the financial burden of attending college. Tax-based education programs include the American Opportunity Tax Credit and the Lifetime Learning Tax Credit. These programs reduce a student's (or his or her parents'/guardians') taxable income while the student attends college.
In addition to federal student aid, students may be eligible for state-based aid. States provide students more than $10.2 billion of aid every year. Each state aid program is different. Usually, a student must reside and attend college in the state providing his/her aid. In some cases, a student can spend state aid on colleges in neighboring states.

Merit-based:

Merit-based grants or scholarships include both scholarships awarded by the individual college or university and those awarded by outside organizations. Merit-based scholarships are typically awarded for outstanding academic achievements and minimum SAT or ACT scores, although some merit scholarships can be awarded for special talents, leadership potential and other personal characteristics. Scholarships may also be given because of group affiliation (such as YMCA, Boys Club, etc.). Merit scholarships are sometimes awarded without regard for the financial need of the applicant. At many colleges, every admitted student is automatically considered for merit scholarships. At other institutions, however, a separate application process is required. Scholarships do not need to be repaid as long as all scholarship requirements are met.

Athletic scholarships are a form of merit aid that takes athletic talent into account.

Need-based:

Need-based financial aid is awarded on the basis of the financial need of the student. The Free Application for Federal Student Aid application (FAFSA) is generally used for determining federal, state, and institutional need-based aid eligibility. At private institutions, a supplemental application may be necessary for institutional need-based aid.

Determining Your Student Aid:

Post-secondary institutions post a Cost of Attendance or Price of Attendance, also known as a "sticker price."

However that price is not how much an institution will cost an individual student. To make higher education costs more transparent before a student actually applies to college, federal law requires all post-secondary institutions receiving Title IV funds (federal funds for student aid) to post net price calculators on their Web sites by October 29, 2011.

As defined in The Higher Education Opportunity Act of 2008, the net price calculator’s purpose is:

“…to help current and prospective students, families, and other consumers estimate the individual net price of an institution of higher education for a student. The [net price] calculator shall be developed in a manner that enables current and prospective students, families, and consumers to determine an estimate of a current or prospective student’s individual net price at a particular institution.”

The law defines estimated net price as the difference between an institution’s average total Price of Attendance (the sum of tuition and fees, room and board, books and supplies, and other expenses including personal expenses and transportation for a first-time, full-time undergraduate students who receive aid) and the institution’s median need- and merit-based grant aid awarded.

In the United States:

The United States government and all U.S. state governments provide merit and need-based student aid including grants, work-study, and loans. As of 2010 there are nine federal and 605 state student aid programs and many of the nearly 7,000 post-secondary institutions provide merit aid.

Major federal grants include the Pell Grants, Federal SEOG Grants, SMART Grants, Academic Competitiveness Grants (ACG Grant), Federal Work-Study Program, Federal Stafford Loans (in subsidized and unsubsidized forms), State Student Incentive Grants and Federal PLUS Loans. Federal Perkins Loans are made by participating schools per annual appropriations from the U.S. Department of Education. Federal Stafford Loans and Federal PLUS Loans are made by the U.S. Department of Education. As of April 2010, Congress voted to eliminate the Federal Family Education Loan Program (FFELP) which had allowed private lenders to make student loans guaranteed by the federal government.

To qualify for federal, state, and institutional aid, a student must prepare a Free Application for Federal Student Aid (FAFSA) every year. The earliest filing date is January 1 for the upcoming academic year. Federal law authorizes that students have two choices when preparing their federal student aid application, either prepare the application themselves on the Department of Education's Web site, or use the services of a fee-based, professional aid advisory firm.

Most aid is provided on a first-come, first-served basis so it is essential that students prepare and submit their aid applications in as close to January 1 as possible. The aid "window" stays open 18 months in case student's financial circumstances change and require adjustment to their aid application.

The application - approximately 130 questions each year - considers household size, income, assets, the number in college and other financial factors to determine a student's aid eligibility and an expected family contribution (EFC). Institutions use EFC to guide their decision about how much need-based financial aid to award a student.

The EFC also takes into consideration any participation in college savings or pre-paid tuition plans. In the past, financial aid officers weighed pre-paid tuition plans more heavily than other 529 college savings plans when determining a student’s eligibility. In February 2006, Congress passed legislation to treat both types of plans evenly.

State governments also typically provide some types of need- and non-need-based aid, consisting of grants, loans, work-study programs, tuition waivers, and scholarships. Individual colleges and universities may provide grants and need- and merit-based scholarships. Students requiring financial aid beyond what is offered by their institution may consider a private (alternative) education loan, available from most large lending institutions. Typically, education loans obtained through the federal government have lower interest rates than private education loans.

Institutions may also offer their own student financial assistance, in the form of need- or merit-based aid, as well as endowed scholarships (with varying need and/or merit-based criteria). Some institutions may only require the FAFSA; some may also require an additional need-based analysis document, such as the CSS/Profile, to apply for such funds to apply a more stringent need analysis for the rationalization of institutional funds.

Anoop Savio

Photo Upload interface code

Use the code below in an application engine, peoplecode step, as per your requirement.

Function PutPhotoInit:

This function initializes employee photo processing. It initializes PSOPTIONS.MAXCHUNKSIZE. The point of the initialization is to select a chunk size that is larger than the largest photo that will be processed. The processing chunksize is currently set to 1MB. The program would need to be updated if a larger photo is to be loaded. The original chunksize should be noted as it is possible for the program to abend without restoring the original chunksize (28K as of this writing).

Function PutPhotoInit()
SQLExec("SELECT MAXCHUNKSIZE FROM PSOPTIONS", &iOriginalChunkSize);
&iPhotoChunkSize = 1000000;
If &iOriginalChunkSize < &iPhotoChunkSize Then SQLExec("UPDATE PSOPTIONS set MAXCHUNKSIZE = :1", &iPhotoChunkSize); End-If; &recEMPL_PHOTO = CreateRecord(Record.EMPL_PHOTO); End-Function;

Function PutPhotoTerm:

This function terminates employee photo processing. It restores PSOPTIONS.MAXCHUNKSIZE to the value that it had when processing began. The original chunksize should be noted as it is possible for the program to abend without restoring the original chunksize (28K as of this writing).

Function PutPhotoTerm()
If &iOriginalChunkSize < &iPhotoChunkSize Then SQLExec("UPDATE PSOPTIONS set MAXCHUNKSIZE = :1", &iOriginalChunkSize); End-If; MessageBox(0, "", 0, 0, &iPhotoCnt " employee photos committed."); CommitWork(); End-Function;

Function PutPhoto:

This function actually loads the photo.
1)The photo file is first loaded to PSFILE_ATTDET using the PutAttachment function.
2)The blob data of the photo is then selected into a variable using SQLExec();
3)The photo file row is then deleted from PSFILE_ATTDET.
4)PS_EMPL_PHOTO.PSIMAGEVER is set to the number of seconds since 01-JAN-2000. This method was found in sample code obtained by Brady Dunn from an Oracle mailing list.
5)The row is is updated or inserted on PS_EMPL_PHOTO depending on whether the employee ID already exists.
6)A commit is performed every 50 rows.

Function PutPhoto()
Local integer &iVersion, &iResult;
Local string &strAttachFileName;
Local string &strMsg;
Local any &anyPhotoBlob;
Local boolean &bFound;

&recEMPL_PHOTO.SetDefault();
&strAttachFileName = &strImageEmplid ".txt";
&iResult = PutAttachment("record://PSFILE_ATTDET", &strAttachFileName, &strImageFileFullName);
If &iResult <> 0 Then
MessageBox(0, "", 0, 0, "*** Start Message ***");
MessageBox(0, "", 0, 0, "PutAttachement failed");
MessageBox(0, "", 0, 0, "Return Code=" &iResult);
MessageBox(0, "", 0, 0, "Image File=" &strImageFileFullName);
MessageBox(0, "", 0, 0, "Blob File=" &strAttachFileName);
MessageBox(0, "", 0, 0, "*** End Message ***");
CommitWork();
End-If;
SQLExec("SELECT FILE_DATA FROM PSFILE_ATTDET WHERE ATTACHSYSFILENAME = :1 AND VERSION = 1", &strAttachFileName, &anyPhotoBlob);
SQLExec("DELETE FROM PSFILE_ATTDET WHERE ATTACHSYSFILENAME = :1 AND VERSION = 1", &strAttachFileName);
REM MessageBox(0, "", 0, 0, "&strImageEmplid=" &strImageEmplid);
&recEMPL_PHOTO.EMPLID.Value = &strImageEmplid;
&bFound = &recEMPL_PHOTO.SelectByKey();
&recEMPL_PHOTO.EMPLID.Value = &strImageEmplid;
&recEMPL_PHOTO.EMPLOYEE_PHOTO.Value = &anyPhotoBlob;
/* Set the version to seconds from year 2000 */
&recEMPL_PHOTO.PSIMAGEVER.Value = (Days365(Date3(1999, 12, 31), %Date) * 86400) + (%Time - Time3(0, 0, 0));

If &bFound Then
&recEMPL_PHOTO.Update();
Else
&recEMPL_PHOTO.Insert();
End-If;
If Mod(&iPhotoCnt, 50) = 0 Then
MessageBox(0, "", 0, 0, &iPhotoCnt " employee photos committed.");
CommitWork();
End-If;
End-Function;

Function GetEmplidFromFileName:

This function is passed a filename. As mentioned earlier, that filename contains the first and last name of the employee
1) The extension (.jpg) is removed from the file name.
2) The remainder is separated into first and last name;
3) For Japan, we are unsure whether the name was in firstname-lastname or lastname-firstname format. So the PS_NAMES table is searched for a match with either format.
4) If one and only one match is found on PS_NAMES, the corresponding EMPLID is returned.

Function GetEmplidFromFileName(&fileName As string) Returns string
Local integer &iSepPos, &iRowCnt;
Local string &strFirstNameFirst, &strLastNameFirst, &strLeftPart, &strRightPart, &strPersonName, &strFirstNameSrch, &strLastNameSrch;
Local string &strSQL, &strEmplID, &strDebug;
&strPersonName = LTrim(RTrim(&fileName));
REM MessageBox(0, "", 0, 0, "&strPersonName=" &strPersonName);
If None(&strPersonName) Then
MessageBox(0, "", 0, 0, "NO EMPLID:&strPersonName (from &fileName) is blank");
Return "";
End-If;
<* chop off file extension *>
&strPersonName = Left(&strPersonName, (Len(&strPersonName) - 4));
If None(&strPersonName) Then
MessageBox(0, "", 0, 0, "NO EMPLID:&strPersonName (chop extension) is blank, &fileName=" &fileName);
Return "";
End-If;
<* find the position of the separator, underscore, blank, or dot *>
&iSepPos = Find(" ", &strPersonName);
If &iSepPos <= 0 Then &iSepPos = Find(".", &strPersonName); End-If; If &iSepPos <= 0 Then &iSepPos = Find("_", &strPersonName); End-If; If &iSepPos <= 0 Then &iSepPos = Find("-", &strPersonName); End-If; If &iSepPos <= 0 Then MessageBox(0, "", 0, 0, "NO EMPLID:&iSepPos=" &iSepPos ", separator not found, &strPersonName=" &strPersonName); Return ""; End-If; <* separate the name into left and right hand parts *>
&strLeftPart = Left(&strPersonName, (&iSepPos - 1));
&strRightPart = Right(&strPersonName, (Len(&strPersonName) - &iSepPos));
<* find emplid by first and last name, assume incoming file name was in last name first format *>
/*
&strLastNameFirst = Upper(&strLeftPart &strRightPart);
&strFirstNameFirst = Upper(&strRightPart &strLeftPart);
&strSQL = "select count(1), EMPLID from PS_NAMES where LAST_NAME_SRCH in (:1, :2) and FIRST_NAME_SRCH in (:1, :2) group by EMPLID";
SQLExec(&strSQL, &strLastNameFirst, &strFirstNameFirst, &iRowCnt, &strEmplID);
*/
&strLastNameSrch = Upper(&strRightPart);
&strFirstNameSrch = Upper(&strLeftPart);
&strSQL = "select count(1), EMPLID from PS_NAMES where ((LAST_NAME_SRCH = :1 and FIRST_NAME_SRCH = :2) or (LAST_NAME_SRCH = :3 and FIRST_NAME_SRCH = :4)) group by EMPLID";
SQLExec(&strSQL, &strLastNameSrch, &strFirstNameSrch, &strFirstNameSrch, &strLastNameSrch, &iRowCnt, &strEmplID);
&strDebug = "DEBUG: ";
&strDebug = &strDebug "&iRowCnt=" &iRowCnt ", ";
&strDebug = &strDebug "&strEmplID=" &strEmplID ", ";
&strDebug = &strDebug "&strPersonName=" &strPersonName ", ";
&strDebug = &strDebug "&strLastNameSrch=" &strLastNameSrch ", ";
&strDebug = &strDebug "&strFirstNameSrch=" &strFirstNameSrch;
If &iRowCnt <> 1 Then
MessageBox(0, "", 0, 0, "NO EMPLID:&iRowCnt=" &iRowCnt ", should equal 1");
MessageBox(0, "", 0, 0, &strDebug);
Return "";
End-If;
Return &strEmplID;End-Function;

Main processing (not within any function):

This code drives the program. It
1) Retrieves an array of the filenames in C:\EMPL_PHOTO that have a (.jpg) extension.
2) If no files are found the processing is complete.
3) On the first loop iteration, PutPhotoInit() is called to prepare for images to be inserted.
4) For each file name in the array
a) The array entries contain the full path, e.g., C:\EMPL_PHOTO\John_Doe.jpg. The directory is removed leaving only the filename John_Doe.jpg.
b) The filename is passed to the function GetEmplidFromFileName().
c) If an employee ID is returned the image file is saved to the database by calling PutPhoto();
5) After the loop is complete PutPhotoTerm() is called to reset back to pre-run state.


<* Load list of image file names into array *>
&arrImageFileList = FindFiles("C:\EMPL_PHOTO\*.JPG", %FilePath_Absolute);
If &arrImageFileList = Null Then
MessageBox(0, "", 0, 0, "No files found");
Else
MessageBox(0, "", 0, 0, "found " &arrImageFileList.Len " photo files.");
For &iIdx = 1 To &arrImageFileList.Len
&strImageFileFullName = &arrImageFileList [&iIdx];
&strImageFileName = &strImageFileFullName;
If &iPhotoCnt <= 0 Then PutPhotoInit(); &iPhotoCnt = 1; Else &iPhotoCnt = &iPhotoCnt + 1; End-If; MessageBox(0, "", 0, 0, "Processing file " &iPhotoCnt ":" &strImageFileFullName); &strImageEmplid = ""; <* remove the path to extract the simple file name *>
&iStartPos = Find("\", &strImageFileName);
If &iStartPos > 0 Then
&iLastSlashFoundPos = &iStartPos;
&iStartPos = &iStartPos + 1;
Else
&iLastSlashFoundPos = 0;
End-If;
While &iStartPos > 0
&iStartPos = Find("\", &strImageFileName, &iStartPos);
If &iStartPos > 0 Then
&iLastSlashFoundPos = &iStartPos;
&iStartPos = &iStartPos + 1;
Else
Break;
End-If;
End-While;
If &iLastSlashFoundPos > 0 Then
&strImageFileName = Substring(&strImageFileFullName, (&iLastSlashFoundPos + 1), Len(&strImageFileFullName));
&strImageDir = Left(&strImageFileFullName, (&iLastSlashFoundPos - 1));
Else
&strImageFileName = &strImageFileFullName;
&strImageDir = "";
End-If;
&strImageEmplid = GetEmplidFromFileName(&strImageFileName);
If All(&strImageEmplid) Then
PutPhoto();
End-If;
<* not sure that we are going to want to delete the files &fImage = GetFile(&strImageFileFullName, "E", %FilePath_Absolute); &fImage.Delete(); &fImage.Close(); *>
End-For;
PutPhotoTerm();
End-If;

Lokesh Madnavat

Peoplesoft Branding : Changing the Login Page

The PeopleSoft branding mainly deals with the look and feel of your PeopleSoft system. Oracle delivers the PeopleSoft with the original Oracle Logo and a standard look. The system gives the maintenance team the flexibility to customize the system’s overall look.

Branding of Login Page and WebServer pages

HTML FILES :

The FileServer psftdocs consist of various .html files which are used by webserver to host various pages of PeopleSoft on web. The details of the files are as follows :

1. signin.html

Purpose : Front Page used as Login Page for Peoplesoft

Path : $PS_HOME/webserv//applications/peoplesoft/PORTAL/WEB-INF/psftdocs// signin.html

(In my case : :/apps/crminstall/crm88/webserv/DEVPORT_DEVPORT_dcrmapp2PIAServer:

/apps/crminstall/crm88/webserv/DEVPORT_DEVPORT_dcrmapp2PIAServers

/dcrmapp.ear/PORTAL/WEB-INF/psftdocs/ps/signin.html)

2.expire.html

Purpose : Html code for the page that appears when the session expires
Path :$PS_HOME/webserv//applications/peoplesoft/PORTAL/WEB-INF/psftdocs// expire.html

(In my case : :/apps/crminstall/crm88/webserv/DEVPORT_DEVPORT_dcrmapp2PIAServer:

/apps/crminstall/crm88/webserv/DEVPORT_DEVPORT_dcrmapp2PIAServers

/dcrmapp.ear/PORTAL/WEB-INF/psftdocs/ps/ expire.html)

3.signintrace.html

Purpose : Front Page with the check boxes for trace options.
Path :$PS_HOME/webserv//applications/peoplesoft/PORTAL/WEB-INF/psftdocs// signintrace.html

(In my case : :/apps/crminstall/crm88/webserv/DEVPORT_DEVPORT_dcrmapp2PIAServer:

/apps/crminstall/crm88/webserv/DEVPORT_DEVPORT_dcrmapp2PIAServers

/dcrmapp.ear/PORTAL/WEB-INF/psftdocs/ps/ signintrace.html)
The appropriate changes can be done to above files to achieve the required look for the changes.

IMAGES :
The images related to these pages are present in the webserver in images folder.
The path for images folder is as under :
$PS_HOME/webserv//applications/peoplesoft/PORTAL //images

The Oracle Delivers its logo that appears on the front page of Peoplesoft Application by the name :

“OPSE_logo.gif”
This logo is referred in signin.html and expire.html.

Thus the login page (.html) file can be scripted and placed in place of signin.html (with same name).

Also if you are referring any other images in your html file, all the refeered imges must be placed in images folder reffered above.
PSHOME/webserv/DOMAIN/bin/startPIA.sh (stopPIA.sh).

Tino Simon

Forgot my password Implementation

View the Forgot my password Implementation paper

I have seen lot of documents on the Forgot password implementation on web. Nothing seems to be accurate or complete in detail. This document has all the relevant details to implement the same.

Tino Simon, PeopleSoft Consultant

Resolve MetaSQL

For the people who haven’t noticed this yet. How to see the resolved SQL, If you come across metasSQL while you code.

Right click on the SQL, then select the Resolve meta SQL option as shown in the screenshot below.

Date validation function: REGEXP_LIKE

You might have used this function earlier for data validation, while working with conversions and interfaces. I came across this function while the KT section was going on. So effective to validate the incoming data from the inbound files. Further searching for the same, I landed in my colleagues blog: peoplesoft learnings blog. Below is what Ganesh has posted in his blog.

In Peoplesoft where we read data from the files, especially for inbound interface , conversion and one time data load. Date is usually read as character field in different format and converted to date. We need to validate the date for further processing. It will be good if we can validate date field in SQL (set processing) than the validating using people code (row by row). Oracle doesn't have very useful function IsDate() not sure why.

Lets see how we can do set processing for the date validation.

1) Regular expression came to my mind to check the pattern and validate the date easily.

Select 'X' from dual
Where REGEXP_LIKE ('1999-10-10','^(1920)\d\d[- /.](0[1-9]1[012])[- /.](0[1-9][12][0-9]3[01])$')
This is simple regular expression which matches only the date format. It doesn't check for leap years and 30,31 days for all the months.

2) Select 'X' from dual
Where NOT REGEXP_LIKE ('2009-2-29','^((((1920)(([02468][048])([13579][26]))-02-29))((20[0-9][0-9])(19[0-9][0-9]))-((((0[1-9])(1[0-2]))-((0[1-9])(1\d)(2[0-8])))((((0[13578])(1[02]))-31)(((0[1,3-9])(1[0-2]))-(2930)))))$')

This regular expression is quite big difficult right every time correctly. Need to copy paste where ever it is requried.

3) if the regular expression is getting difficult, then can create a PL/SQL function and use that function in the sql to validate.
we can use the above sql itself in the function. or use the following.

function IsDate (str long) return boolean is the_Date date;
begin
the_date := to_date(str,'dd-mon-yyyy'); -- note the correction to date mask from what you had below
RETURN true; -- conversion success
exception
when others then RETURN false; -- conversion failed
end;

In the Peoplesoft Implementation project we can create such function once and can be used in many SQL/Peoplecode/AE's. It is difficult to maintain this procedure in different instances like Dev,FIT,SIT,UAT,PPROD,PROD, but is it worth the effort.

Oracle SQL has Regular Expression support from 2003. Many people doesn't know about it and has not been used by many of the Peoplesoft developers and legacy SQR and Peoplecode.

These functions can simplify a lot of pattern matching work in the SQL and AE / SQR program.

REGEXP_LIKE Determine whether pattern matches
REGEXP_SUBSTR Determine what string matches the pattern
REGEXP_INSTR Determine where the match occurred in the string
REGEXP_REPLACE Search and replace a pattern

contains the details of using the regular expression iPublish Postn Oracle SQL.

http://www.oracle.com/technetwork/database/features/application-development/twp-regular-expressions-133133.pdf

Eg:-SELECT * FROM TABLENAME WHERE regexp_like (FIELDTOBECHECKED,'^[A-Za-z0-9]+$')

Can be used to select fields containing only alpha numeric character.

Pattern matching for Phone number , Postal code , Email id , National Identifier , Employee ID , Name , Addresss , Bank account number , credit card number , different identifier.