Advice generation remains as an SQR process (ddp003.sqr). Oracle has added the XML generation routines to this file for the self service addition (same deal in pay003.sqr for paychecks). Unfortunately, they continue to use the SQR print routines for any batch advices/checks printed for employees by your payroll department. This leaves your organization in the predicament of having to customize two different printing mechanisms if you need to modify anything on your advices layouts.
Secondly, these two options (centralized print, or self-service print) don't provide much flexibility if you want to have your departments print advice batches themselves instead of picking up print batches from central payroll.
The solution to both of these issues: Make use of the XML advice data and XML publisher report for both operations with a bolt-on component.
What this component will do:
- Allow payroll staff to create a single PDF file with multiple employee advices in it from the XML advices already stored in the database for employee self-service. (Using the same data and report for both operations)
- Allow payroll to filter and select just the employees they want to print advices for.
- Allow payroll to sort the advices into the single PDF file by simply sorting the grid before they push the button.
- Uses row level security, so you can decentralize batch printing into the departments so they don't have to travel (or mail) printed advices to the rest of the organization.
This is what I did:
1: Create a custom component and page. (XX_ADV_BATCHPRINT)
2: Create a view of the advice self service record for all employees. (Sample SQL and pics below)
3: Retrieve the XML data for the report just like the Employee self service does, but parse multiple employees into a single file so you have a single PDF with multiple advices in it.
4: Use the new rowset MapBufRowToUserSortRow property in tools 8.5 and above to access user sort in advice generation.
5: Although for actual paycheck printing (hopefully very minimal) you still have to deal with the SQR print routines, at least for advices, you now only have to deal with the XML publisher report for customizations.
Sample SQL for the grid record:
SELECT A.PRD_END_DT
, J.DEPTID , PN.NAME_PSFORMAT, A.EMPLID, J.EMPL_RCD, J.JOBCODE, PD.LC_DEPTID_FINANCE
, A.PYMT_DT, A.NET_PAY, isnull(DD.SUPPR_DDP_ADVICE ,'N'), A.URL, A.ATTACHSYSFILENAME
, A.PY_PSLP_FILEURLID, A.PY_SSP_BURSTRPT_ID, A.BURST_TEMPLATE_ID, A.PY_PSLP_SOURCEFILE
FROM PS_PY_SS_PSLP_GDE A JOIN PS_JOB J ON (J.EMPLID = A.EMPLID
AND J.EFFDT = (
SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= getdate()) AND J.EFFSEQ = (
SELECT MAX(J2.EFFSEQ)
FROM PS_JOB J2
WHERE J2.EMPLID = J.EMPLID
AND J2.EMPL_RCD = J.EMPL_RCD
AND J2.EFFDT = J.EFFDT
AND J2.EFFSEQ >= 0)
AND ((J.JOB_INDICATOR = 'P' AND J.EMPL_STATUS IN ('A','L','P','S')) OR J.EMPL_STATUS IN ('A','L','P','S')) ) JOIN PS_PERSON_NAME PN ON (PN.EMPLID = A.EMPLID
AND PN.NAME_TYPE = 'PRI') LEFT OUTER JOIN PS_LCTC_PRFL LP ON (LP.EMPLID = A.EMPLID) LEFT OUTER JOIN PS_LCTC_PRFL_DATA PD ON (PD.LC_TMCD_USERID = LP.LC_TMCD_USERID
AND PD.LC_TMCD_USER_RCD = J.EMPL_RCD
AND PD.EFFDT = (
SELECT MAX(PD1.EFFDT)
FROM PS_LCTC_PRFL_DATA PD1
WHERE PD1.LC_TMCD_USERID = PD.LC_TMCD_USERID
AND PD1.LC_TMCD_USER_RCD = PD.LC_TMCD_USER_RCD)) LEFT OUTER JOIN PS_DIRECT_DEPOSIT DD ON (DD.EMPLID = A.EMPLID
AND DD.EFFDT = (
SELECT MAX(DD1.EFFDT)
FROM PS_DIRECT_DEPOSIT DD1
WHERE DD1.EMPLID = DD.EMPLID
AND DD1.EFFDT <= getdate()))
WHERE A.URL = 'record://PY_SSP_XML_DATA'
AND A.PY_SS_PSLP_PRV_STA = 'ORIG'
AND A.PY_SSP_BURSTRPT_ID = 'SSPUSADV'
Peoplecode for the print routine:
/*Button code to open a batch of advices in a single PDF*/
import PSXP_RPTDEFNMANAGER:*;
Declare Function Get_Field_Label PeopleCode FUNCLIB_BAS.FIELDNAME FieldFormula;
Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetFileNameFromPath PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirectoryFromPath PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetFileExtension PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Local Rowset &AdvBatRSL1;
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local PSXP_RPTDEFNMANAGER:Utility &oUtil;
Local string &sDirSep, &sOutputDir, &sOutputFile, &sDataDir, &RptOutputDir, &xmlDir, &xmlFile, &xmlFileTemp, &URL;
Local number &Rtn, &Count, &OutDestFormat, &CurUS_Num, &RowCount;
Local File &File1, &File2;
Local boolean &AdvFoundToRun, &UserSorted, &ProcessRow;
Local string &ReportName, &TemplateId, &LanguageCd;
Local date &AsOfDate;
&AdvFoundToRun = False;
&UserSorted = False;
&ProcessRow = False;
&AdvBatRSL1 = GetLevel0()(1).GetRowset(Scroll.XX_ADV_BATCHVW);
&RowCount = &AdvBatRSL1.ActiveRowCount;
For &i = 1 To &AdvBatRSL1.ActiveRowCount
If &AdvBatRSL1(&i).Selected And
All(&AdvBatRSL1(&i).XX_ADV_BATCHVW.EMPLID.Value) Then
&AdvFoundToRun = True;
&TemplateId = &AdvBatRSL1(&i).XX_ADV_BATCHVW.BURST_TEMPLATE_ID.Value;
&ReportName = &AdvBatRSL1(&i).XX_ADV_BATCHVW.PY_SSP_BURSTRPT_ID.Value;
&AsOfDate = &AdvBatRSL1(&i).XX_ADV_BATCHVW.PAY_END_DT.Value;
Break;
End-If;
End-For;
If Not &AdvFoundToRun Then
Error ("No advices were found to generate!");
End-If;
&LanguageCd = %Language_User;
&OutDestFormat = 2;
If &AdvBatRSL1.IsUserSorted(%Page | ".XX_ADV_BATCHVW_GRD") Then
&UserSorted = True;
End-If;
&Count = 0;
&CurUS_Num = 0;
While &CurUS_Num < &RowCount
For &i = 1 To &AdvBatRSL1.ActiveRowCount
If &UserSorted Then
&US_Index = &AdvBatRSL1.MapBufRowToUserSortRow(%Page | ".XX_ADV_BATCHVW_GRD", &i);
If &US_Index = &CurUS_Num + 1 Then
&ProcessRow = True;
&CurUS_Num = &US_Index;
Else
&ProcessRow = False;
End-If;
Else
&ProcessRow = True;
&CurUS_Num = &CurUS_Num + 1;
End-If;
If &AdvBatRSL1(&i).Selected And
&ProcessRow Then
&Count = &Count + 1;
If &Count = 1 Then /*First file*/
/* detect system directory separator */
&sDirSep = GetDirSeparator();
/* output directory */
&xmlDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();
CreateDirectory(&xmlDir, %FilePath_Absolute);
&xmlFile = &xmlDir | &sDirSep | "AdviceBatch.xml";
/* start logging */
WriteToLog(%ApplicationLogFence_Level1, "*** XMLP XX Batch Advice PDF: " | String(%Datetime) | " ***");
WriteToLog(%ApplicationLogFence_Level1, "XML File = " | &xmlFile);
&File1 = GetFile(&xmlFile, "N", %FilePath_Absolute);
End-If;
&URL = &AdvBatRSL1(&i).XX_ADV_BATCHVW.URL.Value;
&xmlFileTemp = &xmlDir | &sDirSep | &AdvBatRSL1(&i).XX_ADV_BATCHVW.ATTACHSYSFILENAME.Value;
REM WinMessage(&xmlFileTemp, 0);
/* Download XML data from attachment table*/
&Rtn = GetAttachment(&URL, &AdvBatRSL1(&i).XX_ADV_BATCHVW.ATTACHSYSFILENAME.Value, &xmlFileTemp);
If &Rtn <> %Attachment_Success Then
/*MessageBox(0, "", 158, 653, "Error retrieving file from database");*/
Error MsgGet(235, 5, "Error downloading file from database");
End-If;
REM WinMessage("success", 0);
&File2 = GetFile(&xmlFileTemp, "R", %FilePath_Absolute);
If &File2.IsOpen Then
While &File2.ReadLine(&FileString)
/*Strip off the opening and closing advice tags (except for first advice, keep opening tag */
If (&Count = 1 And
RTrim(&FileString) <> "</US_ADVICE>") Or
(&Count > 1 And
(RTrim(&FileString) <> "<?xml version=""1.0"" encoding=""Windows-1252""?>" And
RTrim(&FileString) <> "<US_ADVICE>" And
RTrim(&FileString) <> "</US_ADVICE>")) Then
&File1.WriteLine(&FileString);
End-If;
End-While;
End-If;
&File2.Close();
REM &File2.Delete();
try
/* cleanup */
DeleteLocalFile(&xmlFileTemp, %FilePath_Absolute);
catch Exception &Dummy2
WriteToLog(%ApplicationLogFence_Error, &Err2.ToString());
end-try;
End-If;
End-For;
End-While;
&File1.WriteLine("</US_ADVICE>"); /*Add closing tag to XML file */
&File1.Close();
try
/* get the report defn object */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportName);
&oRptDefn.Get();
/* &oRptDefn.OutDestination = &xmlDir; */
&oRptDefn.SetRuntimeDataXMLFile(&xmlFile);
&oRptDefn.ProcessReport(&TemplateId, &LanguageCd, &AsOfDate, &oRptDefn.GetOutDestFormatString(&OutDestFormat));
/* &sFileExt = GetFileExtension(&sOutputFormat); */
CommitWork();
/* display the output */
&oRptDefn.DisplayOutput();
catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());
end-try;
try
/* cleanup */
DeleteLocalFile(&xmlFile, %FilePath_Absolute);
RemoveDirectory(&xmlDir, %FilePath_Absolute + %Remove_Subtree);
catch Exception &Dummy
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());
end-try;
Hopefully this will be of some use to others. Our organization is less than 2000 people, with much less then that actually still receiving a printed advice. I don't know how performant this same process would work for massive batches, but it works great for a few hundred into a single PDF.
