Query to search for a given string(Code) in Peoplesoft People code

SELECT * FROM PSPCMTXT WHERE PCTEXT LIKE '%code xyz%' AND PCTEXT LIKE  '%code xyz%'

Custom SORT in SQLs

SELECT A.EMPLID,A.EMPL_RCD,(SELECT PER.NAME_DISPLAY FROM PS_PERSONAL_DATA PER WHERE PER.EMPLID=A.EMPLID) AS 'NAME',
A.GP_PAYGROUP,(SELECT TOP 1 XLATLONGNAME FROM PSXLATITEM WHERE EFF_STATUS='A' AND FIELDNAME ='BAS_PROCESS_STATUS' AND FIELDVALUE=PAR.BAS_PROCESS_STATUS ORDER BY EFFDT DESC)'CURRENT STATUS'
FROM PS_JOB A  JOIN PS_BAS_PARTIC PAR ON A.EMPLID=PAR.EMPLID AND
A.EMPL_RCD=PAR.EMPL_RCD WHERE A.BENEFIT_SYSTEM='BA'
AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID
                     AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT<='01-JAN-2016'
                     )
AND A.EFFSEQ =(SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.HR_STATUS ='A'
AND A.BAS_GROUP_ID ='XYZ'
AND A.PER_ORG='EMP'
AND A.GP_PAYGROUP LIKE 'XYZ%'
AND PAR.SCHED_ID ='XYZ'
ORDER BY
CASE WHEN PAR.BAS_PROCESS_STATUS = 'AS' THEN 1  
     WHEN PAR.BAS_PROCESS_STATUS = 'PR' THEN 2
     WHEN PAR.BAS_PROCESS_STATUS = 'NT' THEN 3
      WHEN PAR.BAS_PROCESS_STATUS = 'ET' THEN 4
     WHEN PAR.BAS_PROCESS_STATUS = 'FE' THEN 5
END ASC

Creating a temporary table and inserting data from other table in SQL SERVER

Syntax:
SELECT * INTO #TEMP FROM TABLE;
Example:
SELECT * INTO #PERSON  FROM PS_PERSON;

SELECT * FROM PSPCMTXT WHERE PCTEXT LIKE '%95%' AND PCTEXT LIKE  '%9139%'

Creating a North America Pay Calendar automatically through process

To Create a North America Pay calendars in Peoplesoft navigate to

Main Menu --> Setup HRMS --> Product Related --> Payroll for North America --> Payroll Processing Controls --> Create Pay calendars

and provide company, pay group and period end date of the first pay cycle for that year and then click run.

Use of PS_BAS_ENR_RUNCTL record in Benefits Administration

PS_BAS_ENR_RUNCTL is a record used by People soft Benefits Administration  COBOL process PSPBARUN.
System will update this record when we run Benefits Administration  online from Run Automated Event Processing Page.

To run the Benefits Administration process from custom page or from Application engine we need to insert the relevant data into this record and then call the PSPBARUN COBOL process.

INSERT INTO PS_BAS_ENR_RUNCTL (OPRID,RUN_CNTL_ID,SCHED_ID,BAS_EM_MODE,PROCESS_DT,DEBUG_ELIG,PARTIC_LIST_IND,PARTIC_NEW_IND,PASSIVE_EVENT_IND,REPROCESS_IND,FINALIZE_ENROLL,PLAN_LIST_IND,CHKPT_INTERVAL,PROCESS_PHASE,BENEFIT_RCD_NBR,EVENT_ID,BENEFIT_PROGRAM,EMPLID,PASSIVE_EVENT_ID,BAS_PROCESS_STATUS,PROCESS_IND)  VALUES (%OperatorId  ,'PSPBARUN'  , 'SCHEID'  , 'N'  , %AsOfDate  , 'N'  , 'N'  , 'N'  , 'N'  , 'N'  , 'N'  , 'E'  , 0  , 'R'  , 0  , 0  , ''  , ''  , ''  , ''  , '');

 To call the Cobol Process using Peoplecode

Local ProcessRequest &RQST_PSPBARUN;

&RQST_PSPBARUN = CreateProcessRequest();
&RQST_PSPBARUN.ProcessType = "COBOL SQL";
&RQST_PSPBARUN.ProcessName = "PSPBARUN";
&RQST_PSPBARUN.RunControlID = "PSPBARUN";
&RQST_PSPBARUN.RunDateTime = %Datetime;
&RQST_PSPBARUN.TimeZone = %ServerTimeZone;
&RQST_PSPBARUN.Schedule();

Lauching Process through Peoplecode

This Code written in the field change of the field.

&RQST = CreateProcessRequest();
&RQST.ProcessType = "SQR";
&RQST.ProcessName = "XYZ";
&RQST.RunControlID = "TEST";
&RQST.RunLocation = "XYZ1";/*SERVER NAME EX:PSUNX
&RQST.RunDateTime = %Datetime;
&RQST.Schedule();

Keeping the Process in Sleep mode until other Process Success.

This code written in the Application engine (Process to wait) at the beginning

SQLExec("SELECT PRCSINSTANCE  FROM PSPRCSRQST WHERE PRCSNAME ='XYZ'  AND RUNSTATUS IN(5 ,6,7)  AND OPRID =:1 AND RUNCNTLID =:2 ", &oprid, &runcntlid, &ProcInst); /* getting the process instance of process to get success */

&RunStatus = 5;  /* status in queued state */
If All(&ProcInst) Then
   /*This loop will be executed still process went to success*/
   While &RunStatus <> 9
    
      GetJavaClass("java.lang.Thread").sleep(60000);  /* milliseconds */ 
    
      &ret = SQLExec("select RUNSTATUS from PSPRCSRQST where PRCSINSTANCE = :1", &ProcInst, &RunStatus);
   End-While; 
Else
End-If;

Deletion of rows in the grid.

This code should be written on the level 0 record (parent record) Save edit event or Save prechange.


For &i = &rs.ActiveRowCount To 1 Step - 1/*get the grid active rowcount */
   &flag = &rs.GetRow(&i).GetRecord(Record.XYZ_WRK).GetField(Field.DELETE).Value;
   If &flag = "Y" Then
      &Ret = MessageBox(4, "", 23002, 1, "selected will be deleted. Are you sure? Yes / No", 0);
/* if user clicks on yes button then only  it will  delete the row */
      If &Ret = 6 Then
         &rs.DeleteRow(&i);
      Else
       
      End-If;
   End-If;
End-For;

Note: We cannot write the code to deletee or hide rows in the same level. You should write the code in the parent record of the child record.

Writing Exception Handling for Select statement in SQL Server

BEGIN TRY INSERT INTO #VIEWCOUNT EXEC SP_EXECUTESQL N'SELECT ''TABELENAME'',COUNT(*) FROM  TABLENAME'; END TRY    BEGIN CATCH     INSERT INTO #ERRORRECORD VALUES('TABLENAME');  END CATCH

The Benefit Plan is not defined in HEALTH_PLAN_TBL(3000, 643)

This error will get fired if the the plan was defined under Plans and Provider  Benefit Plans and not defined under plan attributes health plans.

To resolve identify the plan which was not defined under plan attributes and create and reprocess the Ben admin process

Length of variables exceeds record length in SQR

Open $File_Out As 1 for-writing record=800:Vary status=#Filestat