XML Publisher / BI error in HCM 9.1

PeopleTools - 8.52.10
Origin of the error - Self service - View paycheck
One of the possible causes is Report Category setup is not complete for the underlying report definition.
Report category might be tied with the Roles which the current user does not have access to.

error description:
Unable to generate PDF output - 2002,580
make sure XML data file is in the attachment table and in the right format.

XML publisher report from online button and SQL cursor

XML All in One Desk Reference for Dummies

An Introduction to Object COBOL

Requirement:

Build and run xml publisher report from online button

Solution:

Button fieldchange event Peoplecode written

Xmlp Report uses PS Query which uses three tables joined together and builds output

One of the tables in the query is temporarily populated using the scroll and rowset properties.

Below is the whole logic.

  

import PSXP_RPTDEFNMANAGER:*;

Component string &c_transfer_sodca;

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local Record &rcdQryPrompts, &rcdQryRunParms, &REC;

Local SQL &sqlSelectQryParms, &SQL;

Local string &reportid, &sDirName, &print, &ssn;

Local number &j;

SQLExec("delete from ps_nc_sodca_tmp1");

&SQL = CreateSQL("%INSERT(:1)");

&REC = CreateRecord(Record.<temp table>);

&SQL.BulkMode = True;

For &j = 1 To ActiveRowCount(Scroll.<view name>)

  

   &ssn = FetchValue(Scroll.<view name>, &j, <view name>.SSN);

   &print = FetchValue(Scroll.<view name>, &j, <view name>.NC_PRINT);

  

   rem MessageBox(0, "", 0, 0, "ssn = %1", &ssn);

   rem MessageBox(0, "", 0, 0, "Print = %1", &print);

   &REC.OPRID.Value = %OperatorId;

   &REC.SSN.Value = &ssn;

   &REC.NC_PRINT.Value = &print;

   &REC.DATETIME_STAMP.Value = %Datetime;

   &SQL.Execute(&REC);

  

   

End-For;

&SQL.Close(); /*crucial line*/

&reportid = <xmlp report name>;

/* create report definition */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportid);

&oRptDefn.Get();

&oRptDefn.ProcessReport("xmlp report name", "ENG", %Date, "PDF");

&sDirName = &oRptDefn.OutDestination;

&oRptDefn.Publish("", &sDirName, "", 0);

CommitWork();

&oRptDefn.DisplayOutput();

Issues faced:

=è &SQL.Close(); line was missing in the original code and when I click on the print button, fieldchange event used to throw error that ‘think time function viewattachment() can not work while database transaction update happening’

Apparently &oRptDefn.DisplayOutput(); internally calls viewattachment() which checks if there are any open cursors present, so closing the SQL object did the trick.

It was a good learning experience. Always close the SQL cursor though they are usually harmless unless some situation like this occurs.