Home Home » Peoplesoft BI Publisher

How to display BLOB image in XML Publisher

Posted on January 5, 2022January 5, 2022 by JointLantic

How to display BLOB image in XML BI Publisher

BI Publisher – Retrieving images from BLOB data in RFT template

If results XML contains image data that had been stored as a BLOB in the database, then use the following syntax in a form field inserted in the template where you want the image to render at runtime:

Syntax:

&lt;fo:instream-foreign-object content-type="<span class="italic">image/jpg</span>"&gt;
&lt;xsl:value-of select=".//<span class="italic">IMAGE_ELEMENT</span>"/&gt;
&lt;/fo:instream-foreign-object&gt;

<fo:instream-foreign-object content-type="<span class="italic">image/jpg</span>">

<xsl:value-of select=".//<span class="italic">IMAGE_ELEMENT</span>"/>

</fo:instream-foreign-object>

Example:

<fo:instream-foreign-object content-type=”image/jpg”>
<xsl:value-of select=”.//B.SIGN_TT_IMAGE”/>
</fo:instream-foreign-object>

display BLOB image in XML BI Publisher

Reference 1 :https://www.youtube.com/watch?v=5lUBMxx0pd0
Reference 2 :https://docs.oracle.com/middleware/11119/bip/BIPRD/create_rtf_tmpl.htm#iamge_and_chart

How to attach BI Publisher Report to an email Peoplecode

Posted on October 1, 2018October 1, 2018 by JointLantic

How to attach BI Publisher Report to an email PeopleCode

How to attach BI Publisher Report to an email PeopleCode of PeopleSoft. Some requirement from user wants to send mail with attach file pdf, doc, and other from bi publisher report. You can use this code.

/*General code to print BI Publisher Report*/
import PSXP_RPTDEFNMANAGER:*;

/* get report definition object */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn (&sRptDefn);
&oRptDefn.Get();
&oRptDefn.OutDestination = "/" | GetCwd(); /*We can change this path, if needed*/

/* fill query runtime prompt record */
&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
End-If;
&oRptDefn.ProcessInstance = &prcsInstId;

/*generate report*/
&oRptDefn.ProcessReport (&sTmpltID, &sLangCd, &AsOfDate, &sOutFormat);

/*Create URL ID before proceeding, use this link for the steps to URL ID */
&URL_ID = "record://RECORD_NAME";

/*We are changing the name of the report just make it possible to locate it*/
&oRptDefn.ReportFileName = "Report_Name";

/*Adding the extension of the file*/
&filename = "Report_Name"|"."|&sOutFormat;
&source_file = &oRptDefn.OutDestination | &filename;
&return = PutAttachment(&URL_ID, &filename, &source_file);

/*publish report */
&oRptDefn.Publish(&sPrcsServerName,"",&sFolder, &prcsInstId); 
/*&sPrcsServerName and &sFolder can be "" (i.e. blank) and &prcsInstId is process instance of your process*/
/*As now we have the path for the report (i.e. &source_file), 
we can now use this to add this report as an attachment in our email, as shown below code*/
&dest_file = &source_file;

/*SendMail() can directly access this file if application server and ftp server are at same location. 
If they are at different location, use GetAttachment() to get file from ftp server to application server 
and then mention the path of application server*/

&RET =SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC,&MAIL_SUBJECT, &MAIL_TEXT, &dest_file, &filename,&MAIL_FROM,&MAIL_SEP,&CONTTYPE,&REPLYTO, &SENDER);

/*General code to print BI Publisher Report*/

import PSXP_RPTDEFNMANAGER:*;

/* get report definition object */

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

&oRptDefn.Get();

&oRptDefn.OutDestination = "/" | GetCwd(); /*We can change this path, if needed*/

/* fill query runtime prompt record */

&rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();

If Not &rcdQryPrompts = Null Then

&oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);

End-If;

&oRptDefn.ProcessInstance = &prcsInstId;

/*generate report*/

&oRptDefn.ProcessReport (&sTmpltID, &sLangCd, &AsOfDate, &sOutFormat);

/*Create URL ID before proceeding, use this link for the steps to URL ID */

&URL_ID = "record://RECORD_NAME";

/*We are changing the name of the report just make it possible to locate it*/

&oRptDefn.ReportFileName = "Report_Name";

/*Adding the extension of the file*/

&filename = "Report_Name"|"."|&sOutFormat;

&source_file = &oRptDefn.OutDestination | &filename;

&return = PutAttachment(&URL_ID, &filename, &source_file);

/*publish report */

&oRptDefn.Publish(&sPrcsServerName,"",&sFolder, &prcsInstId);

/*&sPrcsServerName and &sFolder can be "" (i.e. blank) and &prcsInstId is process instance of your process*/

/*As now we have the path for the report (i.e. &source_file),

we can now use this to add this report as an attachment in our email, as shown below code*/

&dest_file = &source_file;

/*SendMail() can directly access this file if application server and ftp server are at same location.

If they are at different location, use GetAttachment() to get file from ftp server to application server

and then mention the path of application server*/

&RET =SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC,&MAIL_SUBJECT, &MAIL_TEXT, &dest_file, &filename,&MAIL_FROM,&MAIL_SEP,&CONTTYPE,&REPLYTO, &SENDER);

Reference : http://techatpeoplesoft.blogspot.com/2016/04/how-to-attach-bi-publisher-report-to.html

How to Make a Private PeopleSoft Query to Public using Update SQL

Posted on June 15, 2018June 15, 2018 by JointLantic

How to Make a Private PeopleSoft Query to Public using Update SQL

How to Make a Private PeopleSoft Query to Public using Update SQL. You can update Oprid is blank of all record relation

UPDATE PSQRYBIND SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYBINDLANG SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYCRITERIA SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYDEFN SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYDEFNLANG SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYEXPR SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYFIELD SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYFIELDLANG SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYRECORD SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;
UPDATE PSQRYSELECT SET OPRID = ‘ ‘WHERE OPRID = ‘OLD_USER’ AND QRYNAME = ‘NEW_USER’;

More suggestion:
– You Create a PUBLIC ps query by clone of the PRIVATE ps query
– Rename ps query from PRIVATE or PUBLIC query
– Delete a query

Reference:https://hexaware.com/blogs/how-to-make-a-peoplesoft-private-query-public/

BI Publisher add in for Word 2016 not Showing

Posted on April 19, 2018May 14, 2018 by JointLantic

BI Publisher add in for Word 2016 not Showing

BI Publisher add in for Word 2016 not showing when you install BI Publisher of PeopleSoft (Download at: Reporting Tools > BI Publisher > Setup > Design Helper)

You should Check about Java version and System Type of Word 32/64 Bit

and more detail at below link

https://mani2web.wordpress.com/2016/06/18/issues-with-oracle-bi-publisher-with-microsoft-word-2016/

How to Install BI Publisher for Word

Posted on April 19, 2018May 14, 2018 by JointLantic

How to Install BI Publisher for Word

How to Install BI Publisher for Word. You can download bi publisher at menu : Reporting Tools > BI Publisher > Setup > Design Helper and select type of bit 32 or 64 and download and install it. After install BI Publisher tab should appear in the ribbon of word and Excel.

You can check version of your word before download at File > Account > About Word

BI Publisher in Word 2016

BI Publisher in Excel 2016

How to change xml publisher default Output Template

Posted on September 8, 2017May 14, 2018 by JointLantic

How to change xml publisher default Output Template

You can change output file of xml publisher in Peoplesoft by go to menu: Report Definition and search your report and click Output tab and choose defualt format and save

defualt format has pdf, html, rtf (doc), xls

Example use defualt format is PFD

How to Run PS Query Report in Application Engine PeopleCode

Posted on July 27, 2017May 14, 2018 by JointLantic

How to Run PS Query Report in Application Engine PeopleCode

Local ApiObject &aRunQry;

&PSQry = %Session.GetQuery();

&qryName = “PS Query Name”;

/* State Record is Prompt field in PS Query*/
&process_instance = PT_TEST_AET.PROCESS_INSTANCE;

If (&PSQry.Open(&qryName, True, True) <> 0) Then

&fileLogLM.WriteLine(“Cannot Open the Query”);

Else
&aQryPromptRec = &PSQry.PromptRecord;
&aQryPromptRec.PROCESS_INSTANCE.Value = &process_instance;
&Date_Format = DateTimeToLocalizedString(%Date, “dd-MMM-yyyy”);

/* Output File */
&strFile = “FILE_” | &process_instance;

/* Use the RunToFile method to execute the Query and return the result to the file specified with Destination.*/
&outStrFile = %FilePath | &strFile;
MessageBox(0, “”, 0, 0, “Query Out File : ” | &outStrFile);

If (&PSQry.RunToFile(&aQryPromptRec, &outStrFile, %Query_XLS, 0) = 0) Then

MessageBox(0, “”, 0, 0, “ResultSet saved into file successfully.”);
Else
MessageBox(0, “”, 0, 0, “Failed to save Resultset into file.”);
End-If;
End-If;

How to Generate XML Publisher from Record Page and Run BI Publisher in PeopleCode

Posted on June 2, 2017May 17, 2018 by JointLantic

How to Generate XML Publisher from Record Page and Run BI Publisher in PeopleCode

How to select data on scroll area or data grid or data level 0 from page in peoplesoft.

Page for get data for create xml file and run bi publisher by peoplecod.

Create Link or Button and use FieldChange Event : print pdf

2. Create PeopleCode for Run BI Publisher.

2.1 Create XML file for Data of RTF Template (Other Template)

2.1.1 Create Datasource is XML type before create RTF Template go to Reporting tool > BI Publisher > Data Source

Data Source Type =XML

Data Source = Create Name

2.1.2 Upload xml file

2.1.3 Create Report Definition

2.2 Generate Report By Peoplecode

import PSXP_RPTDEFNMANAGER:*;
import EP_FUNCTIONS:EP_Utilities;

Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetFileExtension PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

&EP_Utilities = create EP_FUNCTIONS:EP_Utilities();

Local XmlDoc &xmldocRoot = CreateXmlDoc(“<?xml version=’1.0′ encoding = ‘UTF-8’?><root></root>”);
Local XmlNode &nodeRoot = &xmldocRoot.DocumentElement;
Local XmlNode &nodeCurrent, &nodeParent, &nodeEvaluation, &nodeSection, &nodeItem, &nodeSubItem, &nodeEmployee;
/***********************************************************************************/

/*—- 2.1 Create XML file for Create RTF Template (Other Template) —*/

/*— Selete Data from Page —*/

&nodeCurrent = &nodeRoot.AddElement(“EMPLID”);
&nodeCurrent.NodeValue = TEST_DERIVED.EMPLID;
&nodeCurrent = &nodeRoot.AddElement(“NAME”);
&nodeCurrent.NodeValue = TEST_DERIVED.NAME;
&nodeCurrent = &nodeRoot.AddElement(“NATIONAL_ID”);
&nodeCurrent.NodeValue = TEST_DERIVED.NATIONAL_ID;
&nodeCurrent = &nodeRoot.AddElement(“DEPT_DESCR”);
&nodeCurrent.NodeValue = DEPT_TBL.DESCR254;

/*********Add list Element Scroll Left ***********/
Local Rowset &RS_LEV0, &RS_SECT1, &RS_SECT2;
Local Row &ROW1, &ROW2;

&RS_LEV0 = GetLevel0();
&listNode = &nodeRoot.AddElement(“LIST_ITEM”);

/*—– SCROLL LEFT —-*/
&RS_SECT1 = &RS_LEV0(1).GetRowset(Scroll.TEST_PSVARIABLE);

For &i = 1 To &RS_SECT1.ActiveRowCount

/*– Where condition data in scroll left of page–*/
If (&RS_SECT1(&i).TEST_PSVARIABLE.TEST_PS_FIX.Value = “Y”) Then

&subNodeCurrent = &listNode.AddElement(“DESCRIPTION” | &i);
&subNodeCurrent.NodeValue = &RS_SECT1(&i).TEST_PSVARIABLE.DESCRIPTION.Value;

&subNodeCurrent = &listNode.AddElement(“DESCR_CALC” | &i);
&subNodeCurrent.NodeValue = &RS_SECT1(&i).TEST_PSVARIABLE.DESCR_CALC.Value;

End-If;

End-For;

/*—– SCROLL RIGHT —-*/

&RS_SECT2 = &RS_LEV0(1).GetRowset(Scroll.TEST_PSVARIABLE2);

For &i = 1 To &RS_SECT2.ActiveRowCount

/*– Where condition data in scroll right of page–*/
If (&RS_SECT2(&i).TEST_PSVARIABLE2.TEST_PS_FIX.Value = “Y”) Then

&subNodeCurrent = &listNode.AddElement(“DESCRIPTION2” | &i);
&subNodeCurrent.NodeValue = &RS_SECT1(&i).TEST_PSVARIABLE2.DESCRIPTION.Value;

&subNodeCurrent = &listNode.AddElement(“DESCR_CALC2” | &i);
&subNodeCurrent.NodeValue = &RS_SECT2(&i).TEST_PSVARIABLE2.DESCR_CALC.Value;

End-If;

End-For;

&sFormattedXML = &xmldocRoot.GenFormattedXmlString();

/*– WinMessage for Get XML File for Data Source of this report –*/
WinMessage(&sFormattedXML);

/***********************************************************************************/
/*— (2.2) Generate Report —*/
Local date &AsOfDate;
Local string &LanguageCd, &MyReportName, &OutFormat, &sTemplateId;
try

/* detect system directory separator */
rem &sDirSep = GetDirSeparator();
/* create process directory */
rem WinMessage(“%FilePath_Relative : ” | %FilePath_Relative, 0);
CreateDirectory(“XMLP”, %FilePath_Relative);

/* Set XML Publisher report required parameters */
&LanguageCd = %Language_User;
&AsOfDate = %Date;
&MyReportName = “COACHING”;

/* create report defn object */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&MyReportName);
&oRptDefn.Get();

&oRptDefn.SetRuntimeDataXMLDoc(&xmldocRoot);
&oRptDefn.ProcessReport(&sTemplateId, &LanguageCd, &AsOfDate, “”);
&sFileExt = GetFileExtension(&sOutputFormat);
CommitWork();

/* display the output */
&oRptDefn.DisplayOutput();

catch Exception &Err
Local string &sSub1, &sSub2, &sSub3, &sSub4, &sSub5;
Evaluate &Err.SubstitutionCount
When > 4
&sSub5 = &Err.GetSubstitution(5);
When > 3
&sSub4 = &Err.GetSubstitution(4);
When > 2
&sSub3 = &Err.GetSubstitution(3);
When > 1
&sSub2 = &Err.GetSubstitution(2);
When > 0
&sSub1 = &Err.GetSubstitution(1);
End-Evaluate;
Error MsgGet(&Err.MessageSetNumber, &Err.MessageNumber, &Err.ToString(), &sSub1, &sSub2, &sSub3, &sSub4, &sSub5);
end-try;

How to Run Report Definition of BI Publisher in PeopleCode

Posted on May 16, 2017May 14, 2018 by JointLantic

How to Run Report Definition of BI Publisher in PeopleCode

/*————————————————————————————– */
/* You must reference an application package at the beginning of the event */
/*————————————————————————————– */
import PSXP_RPTDEFNMANAGER:*;

Local Record &rcdQryPrompts;
Local string &LanguageCd, &MyTemplate, &MyReportName, &OutFormat, &State;
Local date &AsOfDate;

/*– Set XML Publisher report required parameters –*/
&LanguageCd = “THA”;
&AsOfDate = %Date;
&OutFormat = “PDF”;

/* ————————————————— */
/* Create a PDF using XML Report Definition */
/* —————————————————- */
&MyReportName = “Report Name”;
&MyTemplate = “TemplateName”;

/* ————————————————————————————- */
/* Hard code my State value – would normally pull from a field on the page */
/* ————————————————————————————- */
&EMPLID = Record.EMPLID;
&YEAR = Record.YEAR;
&SEQ = Record.SEQ;
/* —————————————————————————– */
/* Declare and Instantiate (construct) your Report Definition Object */
/* —————————————————————————– */
Local PSXP_RPTDEFNMANAGER:ReportDefn &oReportDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&MyReportName);

/* —————————————————————————– */
/* Get a handle on your Report Definition */
/* —————————————————————————– */
&oReportDefn.Get();

/* —————————————————————————– */
/* Since there is a prompt to the query used in this report, you need to */
/* provide the value for the prompt */
/* —————————————————————————– */
&rcdQryPrompts = &oReportDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
&oReportDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
/* —————————————————————————– */
/* Provide a value to the State Prompt */
/* —————————————————————————– */
rem &rcdQryPrompts.PROCESS_INSTANCE.Value = &ProcessInstane;
&rcdQryPrompts.EMPLID.Value = &EMPLID;
&rcdQryPrompts.YEAR.Value = &YEAR;
&rcdQryPrompts.SEQ.Value = &SEQ;
End-If;

/* —————————————————————————– */
/* Kick of the report process */
/* —————————————————————————– */
&oReportDefn.ProcessReport(&MyTemplate, &LanguageCd, &AsOfDate, &OutFormat);

/* —————————————————————————– */
/* CommitWork must be called prior to displaying the output since the */
/* application package performed work and SQL statements. If you do */
/* not commit the work performed to this point you will receive an */
/* error like “Think-time PeopleCode event (ViewAttachment), but a SQL */
/* update has occurred in the commit interval. (2, 148)” */
/* —————————————————————————– */
CommitWork();

/* —————————————————————————– */
/* Display Report to the user */
/* —————————————————————————– */
&oReportDefn.DisplayOutput();