Category: PeopleSoft


PS JOB – Master SQL

Job and relevant details in single query

SELECT A.EMPLID,
E.NAME,
A.EFFDT,
A.DEPTID,
C.DESCR AS DepartmentName,
A.JOBCODE,
B.DESCR AS JobCdName,
A.EMPL_STATUS,
A.ACTION,
A.ACTION_DT,
A.ACTION_REASON,
A.LOCATION,
D.DESCR AS LOCATIONNAME,
A.FLSA_STATUS,
DECODE(A.FULL_PART_TIME,’F’,’Fulltime’,’P’,’Parttime’) As FUll_PART_TIME,
A.COMPANY,
A.PAYGROUP,
A.EMPL_TYPE,
A.EMPL_CLASS,
A.SUPERVISOR_ID,
F.NAME
FROM PS_JOB A ,
sysadm.PS_JOBCODE_TBL_VW B,
sysadm.PS_DEPT_TBL C,
sysadm.PS_LOCATION_TBL D ,
sysadm.PS_NAMES E,
sysadm.PS_NAMES F
WHERE LENGTH (A.EMPLID) > 7
AND A.EFFDT =
(SELECT MAX (A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX (A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.JOBCODE = B.JOBCODE
AND A.DEPTID = C.DEPTID
AND C.EFF_STATUS = ‘A’
AND C.EFFDT =
(SELECT MAX (C1.EFFDT)
FROM PS_DEPT_TBL C1
WHERE C1.DEPTID = C.DEPTID
AND C1.EFFDT <= SYSDATE
AND C.SETID = C1.SETID)
AND A.LOCATION = D.LOCATION
AND D.EFF_STATUS = ‘A’
AND D.EFFDT =
(SELECT MAX (D1.EFFDT)
FROM PS_LOCATION_TBL D1
WHERE D1.LOCATION = D.LOCATION
AND D1.EFFDT <= SYSDATE
AND D.SETID = D1.SETID)
AND A.EMPLID = E.EMPLID
AND E.EFFDT =
(SELECT MAX (E1.EFFDT)
FROM PS_NAMES E1
WHERE E1.EMPLID = E.EMPLID
AND E1.EFFDT <= SYSDATE)
AND A.EMPLID(+) = F.EMPLID
AND F.EFFDT =
(SELECT MAX (F1.EFFDT)
FROM PS_NAMES F1
WHERE F1.EMPLID = F.EMPLID
AND F1.EFFDT <= SYSDATE)

Advertisements

Shorthand keys on date field- PeopleSoft

Have you ever wondered how to get today’s date without selecting in People Soft

When we enter “T” or “t” on a date field, system will default to today’s date. This functionality has been coded at PT_EDITSCRIPT (HTML Object).

 

How to modify PeopleSoft Style Sheets?

Working on a People Soft style sheet is very challenging and interesting.

There might be several scenarios where in Companies want to have there own style sheets to represent there brand. People Soft provides limited choice to address most common styles.

PS Stylesheet can be created from scratch  or can even  be modified to reflect with our own styles.

To extend a default style sheet in PS , first we need to know details of stylesheet currently been used in our environment. It can be found from following location

PeopleTools >  Utilities  > Administration >  PeopleTools Options

You can then open the stylesheet object from Peoplesoft and add your custom style class to have your own effect in place.

Let me know your comments….Till then happy reading!.

 

 

To convert datetime to a formatted date time character in Oracle, we use TO_CHAR(Column,Format) function.

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:

  • DATE values are converted to values in the default date format.
  • TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
  • TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.

SELECT  TO_CHAR(A.AUDIT_STAMP, ‘DD-MON-YYYY HH24:MI:SS’) FROM PSAUDIT A

PSAUDIT – SQL Issue

Problem Statement: While doing Field Level Audit in People Soft, we might come across below issue

“INSERT INTO PSAUDIT RESULTS IN SQL ERROR FOR VALUES LONGER THAN 65
CHARACTERS”

This issue does not exists in version 8.53 and above.

Solution:

Do impact analysis to find if PSAUDIT is been used across any modules.

Use App Designer to check the database for the use of PSAUDIT anywhere in metadata objects (Find Definition References) and SQL or peoplecode (Find In).
If PSAUDIT is not in the results, i.e. not being used at all, then the table can be dropped from the database (You will see a warning when running DDDAUDIT in the future on this though).

Vice versa, if you want to keep PSAUDIT and drop PSAUDITEXT, do the same above but search for PSAUDITEXT.

Starting in 8.52, PeopleTools internal code checks whether the tables exist before attempting to write to them, so there are no issues if one of them is
deleted.

Either way data exists in both records.
For reference, here is the actual data field definition difference between the two fields:

PSAUDITEXT
PTOLDVALUE VARCHAR2(254) NOT NULL Old Value
PTNEWVALUE VARCHAR2(254) NOT NULL New Value
PSAUDIT
OLDVALUE VARCHAR2(65) NOT NULL Old Value
NEWVALUE VARCHAR2(65) NOT NULL New Value

HTML Area highlighted

We are writing the validation for long edit box which is placed besides the highlighted box.

Please paste below code in HTML Area object on level 0 of PeopleSoft page as highlighted. This object will reflect current count. Example above shows validation for 250 characters. Below code will work only for level zero elements/fields.

<input readonly type=”text” id=”countChar” name=”counter_field1″ size=”3″ maxlength=”3″ value=”100″>

function CharCounter()
{
if (document.getElementById(“LVL0PSRECORD_PSFIELD“).value.length > 100)
document.getElementById(“LVL0PSRECORD_PSFIELD“).value = document.getElementById(“LVL0PSRECORD_PSFIELD“).value.substring(0, 100);
else
document.getElementById(“countChar”).value = 100 – document.getElementById(“LVL0PSRECORD_PSFIELD“).value.length;
}
function addEvent(obj, evType, fn, useCapture){
if (obj.addEventListener){
obj.addEventListener(evType, fn, useCapture);
return true;
} else if (obj.attachEvent){
var r = obj.attachEvent(“on”+evType, fn);
return r;
} else {
alert(“Handler could not be attached”);
}
}
document.getElementById(“countChar”).value=100-document.getElementById(“LVL0PSRECORD_PSFIELD“).value.length;
addEvent(document.getElementById(“LVL0PSRECORD_PSFIELD“), “keyup”, CharCounter, false);

Call PeopleSoft XML Publisher Reports Using PeopleCode

Local CUSTOM_APPPKG &objReportData;
Local File &XMLFile;
Local string &XMLFilename;
Local PSXP_RPTDEFNMANAGER:ReportDefn &report;

&XMLFilename = “/tmp/InputFileName.xml”;

&XMLFile = GetFile(&XMLFilename, “W”, %FilePath_Absolute);

&objReportData = CreateObject(“CUSTOM_APPPKG”);

/*This will assign input parameters, for example StartDate and End Date*/
&objReportData.DS_WK_START_DT = &WK_START_DT;
&objReportData.DS_WK_END_DT = &WK_END_DT;

/*This will form the XML File*/
&objReportData.buildResponse();

&XMLFile.WriteLine(&objReportData.ResponseXml.GenXmlString());
&XMLFile.Close();

&report = create PSXP_RPTDEFNMANAGER:ReportDefn(“ReportID”);
&report.Get();
&report.SetRuntimeDataXMLFile(&XMLFilename);
&report.ProcessReport(“TemplateName”, “ENG”, %Date, “PDF”);
CommitWork();

&report.DisplayOutput();

PeopleSoft Tip: Unique way to get filename from file path

Here’s a simple peoplecode snippet to get filename from given file path. The file name returned for below code is “Phani.csv”

1) For Unix File Path

&UnixFilePath = “/psoft/hr91/devbox/data/stage_in/arch/Phani.csv”;

Local Array of String  &FilePath;
   &FilePath = Split(&UnixFilePath, “/”);
   &Filename = &FilePath [&FilePath.Len];

Or

2) For Windows File Path

&WindowsFilePath =  “\\psoft\hr91\devbox\data\stage_in\arch\Phani.csv”; 

Local Array of String  &FilePath;
   &FilePath = Split(&WindowsFilePath, “\”);
   &Filename = &FilePath [&FilePath.Len];

Sometimes it will be necessary to control display of field formats through peoplecode.

Example: In PeopleSoft ,a signed number needs to be displayed as integer on a page.That is remove decimals from page display using peoplecode.

We will be using FieldClass for modifying such field properties.

Example:

Write Below Code on Component Record RowInit PeopleCode event

Record.Field.DecimalPosition = 0; {Here 0 is the decimal position which is required to be displayed on page}

JOB.AMT1.DecimalPosition = 0;

I have a record with 2 keyfields and 4 required fields.
One keyfield is at level0 and all the other fields are at level 1. All the fields are editable.
When im trying to key in some information and save the component, i’m getting error which says “XXX field is required”.

This is even if the value is entered in the field.

When i made the level 0 keyfield as display only, then the error is solved.

After research i found that it is a peoplesoft delivered functionality.
We cannot have more than one record on the same level. But we can have same record on level 0 and level 1.
But the requirement to have the same record on level 1 and 0 is that ,level 0 fields should be display only.

when you have same record on level 0 and 1 then it tries to insert 2 rows in the database with same
value of level 0 fields and in level 1 for one row the values you have given. And in second row same level 0 field values but blank level 1
values.