How to launch a Connected Query XML Publisher report with parameters from PeopleCode

Launching a basic query based XML publisher report from a button or other event to window immediately for a user is very easy.  Oracle has done a good job of providing direct access to the Prompt values for the query in PeopleCode so you can supply the values you want from the page and display the report immediately.  By contrast, I have not seen such an easy interface for using a Connected Query based XML Publisher report in the same manner.  I have found a work around for accomplishing this seemingly straight forward feat, but it seems strange that Oracle hasn't provided a better way of doing this.

Unlike a Query based XMLP report, Oracle did not provide a way (or at least none that I could find) of directly supplying any query prompt values without having to use the process scheduler and the PeopleTools record they have where you write the prompt values to from a run control.  I did not want to use the process scheduler or a run control, I just wanted the user to push a button and have the report pop-up like I can with Query XMLP.

My solution:
1:  Create the connected query and run a sample of it to XML that you can save to file as sample data for your XML Publisher report.
2:  Create a Datasource for your report, but instead of making in Connected Query, make it an XML File datasource type and upload your sample file from the connected query output.
3:  Create a Report definition from the XML File datasource you just created.
4:  You can use the PeopleCode below in a push-button or other user-interactive way to immediately execute and display the report with values for query prompts you want to supply from the page.

UPDATE - 5/21/2012 - I have added in some suggested code from posts below regarding formatting issue that were being reported.  Hopefully the code example you see here now resolves those issues as well.  Thanks everyone!!

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local PSXP_RPTDEFNMANAGER:Utility &oUtil;
Local PT_CONQRS:CONQRSMGR &oConQrsInst;
Local array of PT_CONQRS:QUERYITEMPROMPT &CQPromptsArray;

Local string &Report, &Template, &sDirSep, &ServerPrefix, &WorkingFolder, &WorkingFullPath, &WorkingFile, &OutStr, &LanguageCd;
Local File &CQXmlFile;
Local date &AsOfDate;
Local number &OutDestFormat;
Local Record &PromptRec, &CQPromptRec;

&LanguageCd = %Language_User;
REM &OutDestFormat = 2; /*PDF - not actually using it in this code, just leaving it as a blank string in ProcessReport call below uses default format*/

&sDirSep = GetDirSeparator(); /* detect system directory separator */
&ServerPrefix = RTrim(GetEnv("PS_SERVDIR"));

If None(&ServerPrefix) Then
Error ("Server Prefix not found!");
End-If;

try
&WorkingFolder = UuidGen();
&WorkingFullPath = &ServerPrefix | &sDirSep | "files" | &sDirSep | "CQWRK" | &sDirSep | &WorkingFolder;
CreateDirectory(&WorkingFullPath, %FilePath_Absolute);

/*Get XMLP Report object*/

&Report = "YOUR_XMLP_RPT"
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&Report);
&oRptDefn.Get();
&oRptDefn.OutDestination = &WorkingFullPath;

&AsOfDate = %Date;

/*This is a connectected query report. We have to access and run the connected query to an XML file as a seperate operation*/
/*Create a location to place the connected query output file*/
&CQXmlFile = GetFile(&WorkingFullPath | &sDirSep | "CQData.xml", "N", %FilePath_Absolute);
/* get a reference to the Connected Query defn object */
&oConQrsInst = create PT_CONQRS:CONQRSMGR("", "YOUR_CQ_DEFN"); /*The blank "" parameter is filled in with OPRID if this is a private CQ. */
&result = &oConQrsInst.Open(&oConQrsInst.Const.InitExisting);
&CQPromptsArray = &oConQrsInst.QueriesPromptsArray;

/*Loop through the Connected Query "Queries" and fill in each query's prompts as needed*/
For &ap = 1 To &CQPromptsArray.Len
/*You will have to add selection logic if there is more than 1 query with prompts and they differ in fields*/
&CQPromptRec = &CQPromptsArray.Get(&ap).QueryPromptRecord;
&CQPromptRec.GetField(Field.YOURPROMPT1).Value = &YourValue1;
&CQPromptRec.GetField(Field.YOURPROMPT2).Value = &YourValue2;
...etc
End-For;

/*Use the RunToXMLFormattedString method to immediately execute the connected query with your parameters*/
&OutStr = &oConQrsInst.RunToXMLFormattedString(&CQPromptsArray);

/*The following 2 lines of code I added on 5/21/2012 in response to comments below indicating formatting issues caused by extra carriage returns.  Thanks DooRon for posting the suggestion.  Hopefully this fixes the reported formatting issues reported by some.*/
&OutStr = Substitute(&OutStr, ">" | Char(13) | "~", ">");
&OutStr = Substitute(&OutStr, ">" | Char(10) | "~", ">");
/*End additions 5/21/2012*/

/*Write the output to the xml file for use in the XML Publisher report*/
&CQXmlFile.WriteLine(&OutStr); &CQXmlFile.Close();
&oRptDefn.SetRuntimeDataXMLFile(&WorkingFullPath | &sDirSep | "CQData.xml"); /*Set XMLP report to use this file*/

rem &oRptDefn.ProcessReport(&Template_IN, &LanguageCd, &AsOfDate, &oRptDefn.GetOutDestFormatString(&OutDestFormat)); /*Optional way to specify format*/
&oRptDefn.ProcessReport(&Template, &LanguageCd, &AsOfDate, "");

CommitWork();

/* display the output */
REM &oRptDefn.Publish("", &WorkingFullPath, "XMLP", LC_CN1_AET.PROCESSINSTANCE); /*To publish report instead of directly view*/

&oRptDefn.DisplayOutput(); /*To view output in a window immediately*/


catch Exception &Err
Error (&Err.ToString());
end-try;

try
/* cleanup your temp files to keep the application server clean*/

RemoveDirectory(&WorkingFullPath, %FilePath_Absolute + %Remove_Subtree);

catch Exception &Dummy
Error (&Err.ToString());
end-try;
I tried for quite a while to use a straight forward Connected Query XMLP report object and access it's datasource and the subsequent parameters I knew the CQ would need.  But based on what I have seen, googled and reverse engineered thus far, I cannot see a way to provide that directly to the report object.  Accessing the connected query directly as an object provided me the way to give the prompt values I wanted and run the query immediately, but the File method was the only way I found to supply that over to the XMLP report object.  If anyone has a better way of doing this, please share.  :-)

Enhanced advice printing for 9.1 Payroll

HRMS 9.1 offers some great new features, including enhanced paycheck self service for employees with direct access to their "printer friendly" advice.  Oracle did a nice job with this feature by storing the employee advice data as XML in the system, and then providing an XML publisher report to provide presentation to the employees.  There are a couple drawbacks to how Oracle has implemented this functionality however. 

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.

Welcome

Greetings!

I am a Senior PeopleSoft developer at Lane County, Oregon.  I specialize in HR (HCM) and Financials development and support.  Lane County currently operates HRMS 9.1 and Financials 8.8.  We are upgrading to Financials 9.1.

Over the years I have developed many bolt-ons and enhancements that users find very useful.  I am pleased to be able to share what I have learned with the community out there as my time permits.  Hopefully someone will find something useful here for themselves and their organization.  Look for my first post soon!