SQLs useful for developers / testers

1. SQL to find the list of roles attached to a page.

select rc.rolename, rd.descr , a.classid, c.classdefndesc
from psauthitem a, psclassdefn c, psroleclass rc, psroledefn rd
where a.pnlitemname = --Enter the page Name Here
and a.baritemname= -- Enter the component name/bar_name
and a.classid = c.classid
and a.classid = rc.classid
and rc.rolename = rd.rolename
Order by rc.rolename;

2. select distinct r.recname from pspnlgroup pg,pspnlfield p,psrecdefn r where
pg.pnlgrpname ='JOB_DATA'
and pg.pnlname = p.pnlname
and p.recname = r.recname
and r.rectype = '0'
and p.recname like '%'; 

Relation between Job Actions and Effective Date

Peoplesoft HCM Relation between Job Actions and Effective Date.

Personnel actions that begin new stages of employment (hire, promotion, reassignment, transfer, disciplinary action, and pay rate changes) are effective
at the beginning of the business day.

Example: Employee gets promoted as of 15-Jan-2012. That means effective 15-Jan-2012, employee enters his new role.

Personnel actions such as removal and termination are effective at the end of the business day.

Example: Employee gets terminated as of 12-Aug-2012. That means employee last working day is 11-Aug-2012 and from 12-Aug he is no more with the company.

XML Publisher Passing Run Control values to PS Query as Data Source

XML Publisher.
Data Source:  PS query

Below is the code to pass values from Application Engine State Record to PS Query prompt record.
Input values are FromDate and ThruDate.

The XML Publisher then runs the report based on input values and generates the output.

import PSXP_RPTDEFNMANAGER:*;

local string &sRptDefn = ‘PAYROLL_RPTDFN’;

/* get report definition object */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn (&sRptDefn);

&oRptDefn.Get();

/* fill query runtime prompt record */

&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();

&FieldName1 = &rcdQryPrompts.GetField(1).Name;
&FieldName2 = &rcdQryPrompts.GetField(2).Name;

/* MessageBox(0, "", 0, 0, "The First field name is : " | &FieldName1); */

/* MessageBox(0, "", 0, 0, "The Second field name is : " | &FieldName2); */

&rcdQryPrompts.GetField(1).Value = PS_PAYROLL_AET.FROM_DATE;

&rcdQryPrompts.GetField(2).Value = PS_PAYROLL_AET.TO_DATE;

If Not &rcdQryPrompts = Null Then

  &oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);

End-If;

/*generate report*/

&oRptDefn.ProcessReport (&sTmpltID, &sLangCd, &AsOfDate, &sOutFormat);

/*publish report */