Latest Entries »

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

Working with Background Colors in PeopleSoft

If you are on Grid and want to change Background color or Cell or Row, you can use below code.

&ROW = &RS.GetRow(&A);

&ROW.Style = “PSGRIDCOLUMNHDR”;
&RS(&A).RECORDNAME.FIELDNAME.Style = “PSGRIDCOLUMNHDR”;

For identifying the Style Value, you might want to try below logic.

When adding fields to pages, we select a “style” for each new field. The style maps to a CSS class that is contained within a PeopleSoft stylesheet object. In most cases, we simply take the default value for the style and that gives us a consistent appearance on our pages. But sometimes we want to modify the visual appearance of a field.

To select a style, simply choose it from the dropdown list in the field properties. (There is a style for the field and one for the label.) Simple enough. But what do all the choices mean? Do you know what “PAFRAMELEVEL3WBO” is? Also, the style as it appears in App Designer is often just an approximation of what will appear in the browser. Do we need to use a trial-and-error approach to see what each style is?

Instead of that method, try displaying samples of all of the styles in the browser. 90% of them are duplicates or very specialized styles that are rarely used. But among the rest you might find good choices for your page design.

It’s pretty easy to do. Write some SQL that generates HTML:

   SELECT
   '
' || STYLECLASSNAME || '
' FROM PSSTYLECLASS WHERE STYLESHEETNAME = 'PTSTYLEDEF'

Insert an HTML Area on your page. Run the SQL and copy the results into the HTML Area (as a “constant” value instead of a field value). There is a limit to how much text you can put there, so you may need to restrict your selection in the WHERE clause of the SQL (in this example, I just picked one stylesheet). Save the page and navigate to it, and you should see something like this:

Pick the appropriate style and apply it to your field, then just delete the HTML Area and you should be all set. One thing to be aware of, though, is that most of the styles with backgrounds do not fill in more than the area taken by the text. You can see how this works by testing it. For the styles that do fill in larger areas, do a View Source in the browser and look for “width:100%”.

Using “If Loop” in XML Publisher reporting

Usually conditional loops are used in any programming language.

Below is an example to control a message on RTF template based XML Publisher report using IF Loop.

Author is trying to display a message if there are no rows to be displayed on report.

Example:

<?xdoxslt:ifelse(number(@rownumber)!=0,’’,’There are no data’)?>

Enjoy for the day!

People Code to Update or Insert values to a record

People Code to Update or Insert values to a record

&Rec = CreateRecord(Record.ABC_JOB);

&Rec.EMPLID.Value = “1”; /*Assign values to all Key fields*/
&result = &Rec.SelectByKey();
&Rec.STATUS.Value = “Active”;
If &result Then
&Rec.update();
Else
&Rec.Insert();
End-if;

PeopleCode With Yes No Message Box On Page

There might be a requirement where we need to perform some action based on return value of Message Box.

For example: Save the component after user confirms by clicking Yes.

We can use below PeopleCode in Field Change Event.
&Answer = MessageBox(4, “”, 0, 0, “Please confirm if you want to Save”);
If &Answer = 6 Then
DoSave();
Else
End-If;

&Answer = 6 for response of “Yes”

&Answer = 7 for response of “No”

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);