Auto Insert New Hires as PS Users

The following is a piece of App Engine code that will take new hires and auto insert them as a Peoplesoft User Profile with default data and component permission lists so they can use self service automatically.

Local string &UserID, &UserDesc;
Local SQL &OPRDEFN_SQL;
Local string &Text;

Declare Function CreateNewUser PeopleCode wherever.you.store.it FieldFormula;

&OPRDEFN_SQL = GetSQL(SQL.OPR_ADD);
&cntr = 0;
While &OPRDEFN_SQL.Fetch(&UserID, &UserDesc);
CreateNewUser(&UserID, &UserDesc);
&cntr = &cntr + 1;
End-While;
/**************************************************************/


Function:

Function CreateNewUser(&UserID As string, &UserDesc As string);
&OperatorToCopy = "MODEL";
&UserEmail = " ";
/* May need to collect personal data or whatever you'll use for a default password */
&userpw = whatever your algorithm is for a default

&OPRDEFN_REC = CreateRecord(Record.PSOPRDEFN);
&OPRDEFN_SQL = CreateSQL("%selectall(:1) where oprid = :2");
&OPRDEFN_SQL.Execute(&OPRDEFN_REC, &OperatorToCopy);
If &OPRDEFN_SQL.IsOpen Then
While &OPRDEFN_SQL.Fetch(&OPRDEFN_REC);
&Session = GetSession();
&Session.Connect(1, "EXISTING", "", "", 0);

/* Create new User ID */
&NewUserBC = &Session.GetCompINTFC(CompIntfc.USER_PROFILE);
&NewUserBC.InteractiveMode = False;
&NewUserBC.UserID = &UserID;
SQLExec("select oprid, emplid from psoprdefn where oprid = :1", &UserID, &oprid, &emplid);
If All(&oprid) Then
MessageBox(0, "Error", 0, 0, "User ID already exists for this emplid. Contact HRIS");
Else
&NewUserBC.Create();
&NewUserBC.Userdescription = &UserDesc;
&NewUserBC.EmailAddress = &UserEmail;
&NewUserBC.SymbolicID = &OPRDEFN_REC.SYMBOLICID.Value;
&NewUserBC.setpassword(&userpw, &userpw);
&NewUserBC.PrimaryPermissionList = &OPRDEFN_REC.OPRCLASS.Value;
&NewUserBC.RowSecurityPermissionList = &OPRDEFN_REC.ROWSECCLASS.Value;
&NewUserBC.ProcessProfilePermissionList = &OPRDEFN_REC.PRCSPRFLCLS.Value;
&NewUserBC.NavigatorHomePermissionList = &OPRDEFN_REC.DEFAULTNAVHP.Value;

&OPRALIAS_REC = CreateRecord(Record.PSOPRALIAS);
&OPRALIAS_SQL = CreateSQL("%selectall(:1) where oprid = :2");
&OPRALIAS_REC = CreateRecord(Record.PSOPRALIAS);
&NewIDType = &NewUserBC.IDTypes;
&IDTypeNum = &IDTypeNum + 1;
&NewIDRow = &NewIDType.item(&IDTypeNum);
&NewIDRow.IDType = "EMP";
&NewAttributes = &NewIDRow.Attributes;
&AttributesNum = 1;
&NewAttrRow = &NewAttributes.item(&AttributesNum);
&NewAttrRow.AttributeValue = &UserID;

/* We don't carry aliases forward in any fancy way so I've commented this out */
/* &OPRALIAS_SQL = CreateSQL("%selectall(:1) where oprid = :2");
&OPRALIAS_SQL.Execute(&OPRALIAS_REC, &OperatorToCopy);
&IDTypeNum = 0;
If &OPRALIAS_SQL.IsOpen Then
While &OPRALIAS_SQL.Fetch(&OPRALIAS_REC);
If &IDTypeNum > 0 Then
&NewIDType.insertitem(&IDTypeNum);
End-If;
&NewIDType = &NewUserBC.IDTypes;
&IDTypeNum = &IDTypeNum + 1;
&NewIDRow = &NewIDType.item(&IDTypeNum);
&NewIDRow.IDType = &OPRALIAS_REC.OPRALIASTYPE.Value;
&NewAttributes = &NewIDRow.Attributes;
&AttributesNum = 0;
If All(&OPRALIAS_REC.SETID.Value) Then
&AttributesNum = &AttributesNum + 1;
&NewAttrRow = &NewAttributes.item(&AttributesNum);
&NewAttrRow.AttributeValue = &OPRALIAS_REC.SETID.Value;
End-If;
If All(&OPRALIAS_REC.CUST_ID.Value) Then
&AttributesNum = &AttributesNum + 1;
&NewAttrRow = &NewAttributes.item(&AttributesNum);
&NewAttrRow.AttributeValue = &OPRALIAS_REC.CUST_ID.Value;
End-If;
If All(&OPRALIAS_REC.EMPLID.Value) Then
&AttributesNum = &AttributesNum + 1;
&NewAttrRow = &NewAttributes.item(&AttributesNum);
&NewAttrRow.AttributeValue = &UserID;
End-If;
If All(&OPRALIAS_REC.VENDOR_ID.Value) Then
&AttributesNum = &AttributesNum + 1;
&NewAttrRow = &NewAttributes.item(&AttributesNum);
&NewAttrRow.AttributeValue = &OPRALIAS_REC.VENDOR_ID.Value;
End-If;
If All(&OPRALIAS_REC.CONTACT_ID.Value) Then
&AttributesNum = &AttributesNum + 1;
&NewAttrRow = &NewAttributes.item(&AttributesNum);
&NewAttrRow.AttributeValue = &OPRALIAS_REC.CONTACT_ID.Value;
End-If;
End-While;
End-If;
&OPRALIAS_SQL.Close();
*/

/* Add all roles for user */
&ROLEUSER_REC = CreateRecord(Record.PSROLEUSER);
&ROLEUSER_SQL = CreateSQL("%selectall(:1) where roleuser = :2");
&ROLEUSER_SQL.Execute(&ROLEUSER_REC, &OperatorToCopy);

&RoleNum = 0;
If &ROLEUSER_SQL.IsOpen Then
While &ROLEUSER_SQL.Fetch(&ROLEUSER_REC)
If &RoleNum > 0 Then
&NewRole.insertitem(&RoleNum);
End-If;
&RoleNum = &RoleNum + 1;
&NewRole = &NewUserBC.Roles;
&NewRoleRow = &NewRole.item(&RoleNum);
rem &NewRoleRow.Roleuser = &UserID;
&NewRoleRow.RoleName = &ROLEUSER_REC.ROLENAME.Value;
If Not &ROLEUSER_SQL.IsOpen Then
Break;
End-If;
End-While;

End-If;
&ROLEUSER_SQL.Close();

/* Save new User ID */
If Not &NewUserBC.save() Then
&MsgCollection = &Session.PSMessages;
For &I = 1 To &MsgCollection.Count
&ErrorObj = &MsgCollection.Item(&I);
&Text = &ErrorObj.Text;
MessageBox(0, "Error", 0, 0, " Could not create User " | &ErrorObj | &Text | " ");
End-For;
&MsgCollection.DeleteAll();
End-If;
&NewUserBC.Cancel();
End-If;
End-While;
End-If;
&OPRDEFN_SQL.Close();
End-Function;
/**************************************************************/


SQL:

SELECT a.emplid 
, a.name 
FROM ps_personal_data a 
WHERE EXISTS ( 
SELECT 'x' 
FROM PS_Job B 
WHERE a.emplid = b.emplid 
AND b.empl_status = 'A' 
AND b.effdt = ( 
SELECT MAX(c.effdt) 
FROM ps_job c 
WHERE c.emplid = b.emplid 
AND c.empl_rcd = b.empl_rcd) 
AND b.effseq = ( 
SELECT MAX(d.effseq) 
FROM ps_job d 
WHERE d.emplid = b.emplid 
AND d.empl_rcd = b.empl_rcd 
AND d.effdt = b.effdt)) 
AND NOT EXISTS ( 
SELECT 'x' 
FROM psoprdefn o 
WHERE o.oprid = a.emplid)