Primary job / Peoplesoft - dealing with multiple jobs - JOB_INDICATOR

This solution is a great one to set the JOB_INDICATOR to primary. This logic is based upon max(annual_rt) and min empl_rcd if the annual_rt is the same for the jobs.

Currently, we have a AE process that will set all max current rows to N, and all future dated rows to N. Then we evaluate which jobs are primary.
Here is a sample:

/*** Update rows to N ***/

UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
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)


/*** Update future dated rows ***/
UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
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)

/*** Insert values into temp table for processing ***/
%InsertSelect(ASU_HR0022_TBL, JOB A, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE), EMPLID = A.EMPLID, EMPL_RCD = A.EMPL_RCD, EFFDT = A.EFFDT, EFFSEQ = A.EFFSEQ, EMPL_STATUS = A.EMPL_STATUS, FTE = A.FTE,ANNUAL_RT = A.ANNUAL_RT, JOB_INDICATOR = 'P', ASU_OLD_JOB_IND = A.JOB_INDICATOR)
FROM PS_JOB A
WHERE 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.EMPL_STATUS NOT IN ('T','R')
AND A.ANNUAL_RT = (
SELECT MAX(A3.ANNUAL_RT)
FROM PS_JOB A3
WHERE A3.EMPLID = A.EMPLID
AND a3.empl_status NOT IN ('T','R')
AND a3.effdt = (
SELECT MAX(a3x.effdt)
FROM ps_job a3x
WHERE a3x.emplid = a3.emplid
AND a3x.empl_rcd = a3.empl_rcd
AND a3x.effdt <= sysdate)
AND a3.effseq = (
SELECT MAX(a3y.effseq)
FROM ps_job a3y
WHERE a3y.emplid = a3.emplid
AND a3y.empl_rcd = a3.empl_rcd
AND a3y.effdt = a3.effdt))
AND A.EMPL_RCD = (
SELECT MIN(A4.EMPL_RCD)
FROM PS_JOB A4
WHERE A4.EMPLID = A.EMPLID
AND a4.empl_status NOT IN ('T','R')
AND a4.annual_rt = a.annual_rt
AND a4.effdt = (
SELECT MAX(a4x.effdt)
FROM ps_job a4x
WHERE a4x.emplid = a4.emplid
AND a4x.empl_rcd = a4.empl_rcd
AND a4x.effdt <= sysdate)
AND a4.effseq = (
SELECT MAX(a4y.effseq)
FROM ps_job a4y
WHERE a4y.emplid = a4.emplid
AND a4y.empl_rcd = a4.empl_rcd
AND a4y.effdt = a4.effdt))

/*** Perform update ***/

UPDATE PS_JOB A
SET A.job_indicator = 'P', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE EXISTS (
SELECT 'X'
FROM PS_ASU_HR0022_TBL B
WHERE B.emplid = A.emplid
AND B.EMPL_RCD = A.EMPL_RCD
AND B.EFFDT = A.EFFDT
AND B.EFFSEQ = A.EFFSEQ
AND B.JOB_INDICATOR = 'P')
AND A.JOB_INDICATOR <> 'P'


/*** Peoplecode for report ***/

/*** Keep in mind, you will need to create a function for your own environment's path - J.McMahon ***/

Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

Local Record &Readrec, &RecLine, &Readrec2, &RecLine2;
Local File &MYFILE;
Local SQL &SQL2, &SQL3;
Local string &Filename;

GetFilePathServer(&outFilePath, &inFilePath, ASU_HR0022_AET.DBNAME);

If ASU_HR0022_AET.PROCESS_INSTANCE > 0 Then
If ASU_HR0022_AET.DBNAME = "ASUSAPRD" Then
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB_PRD.txt";
Else
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB.txt";
End-If;
Else
&Filename = "R:\temp\ASU_PRIM_JOB.txt";
End-If;

&MYFILE = GetFile(&Filename, "W", %FilePath_Absolute);

If &MYFILE.IsOpen Then
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("Process Instance: " | ASU_HR0022_AET.PROCESS_INSTANCE | " - ASU_HR0022 - Primary Job Status Report");

Local number &ErrorCount;
&ErrorCount = 0;

SQLExec("select ASU_FILLER_N5 from SYSADM.PS_ASU_HR0022_VW", &ErrorCount);

&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Error Report */
&MYFILE.WriteLine(&ErrorCount | " - Errors found during this run");
&MYFILE.WriteLine(" ");

If &ErrorCount > 0 Then
&MYFILE.WriteLine(" EMPLID " | Char(44) | " Count ");
&Readrec = CreateRecord(Record.ASU_HR0022_VW_T);
&SQL2 = CreateSQL("SELECT * FROM SYSADM.PS_ASU_HR0022_VW");
While &SQL2.Fetch(&Readrec)
&MYFILE.WriteLine(&Readrec.EMPLID.Value | Char(44) | &Readrec.ASU_FILLER_N5.Value);
End-While;
&MYFILE.WriteLine(" ");

End-If;


&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Success Report */

Local number &changedCount;
&changedCount = 0;

SQLExec("select COUNT(*) FROM PS_ASU_HR0022_TBL where process_instance = :1", ASU_HR0022_AET.PROCESS_INSTANCE, &changedCount);

&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(&changedCount | " - JOB records updated during this run - (INTO Temp Table - PS_ASU_HR0022_TBL");


&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("JOB DETAILS:");
&MYFILE.WriteLine(" ");

&MYFILE.WriteLine("Process Instance" | Char(44) | " EMPLID " | Char(44) | " Empl Rcd " | Char(44) | " Effdt " | Char(44) | " Effseq " | Char(44) | "Empl Status" | Char(44) | " FTE " | Char(44) | " Annual Rt " | Char(44) | " Job Indicator " | Char(44) | "Old Job Ind");
&Readrec2 = CreateRecord(Record.ASU_HR0022_TMP);
&SQL3 = CreateSQL("SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, %DateOut(A.EFFDT), A.EFFSEQ, A.EMPL_STATUS, A.FTE, A.ANNUAL_RT, A.JOB_INDICATOR, A.ASU_OLD_JOB_IND FROM PS_ASU_HR0022_TBL A WHERE A.PROCESS_INSTANCE = :1", ASU_HR0022_AET.PROCESS_INSTANCE);
While &SQL3.Fetch(&Readrec2)
&MYFILE.WriteLine(&Readrec2.PROCESS_INSTANCE.Value | Char(44) | &Readrec2.EMPLID.Value | Char(44) | &Readrec2.EMPL_RCD.Value | Char(44) | &Readrec2.EFFDT.Value | Char(44) | &Readrec2.EFFSEQ.Value | Char(44) | &Readrec2.EMPL_STATUS.Value | Char(44) | &Readrec2.FTE.Value | Char(44) | &Readrec2.ANNUAL_RT.Value | Char(44) | &Readrec2.JOB_INDICATOR.Value | Char(44) | &Readrec2.ASU_OLD_JOB_IND.Value);
End-While;
End-If;

&MYFILE.Close();