Transform the PSQuery

You might have already noticed a new tab on the PSQuery Manager - Transformations.

This is the place where you can place your custom XSL code and render the query in a much better way, than displaying it in a traditional way.

The best part I loved about this is that, you can create excellent report from the query, without using any existing reporting tools, but with just few lines of XSL + HML fused together. That's awesome!

To add a new XSL, click on the Add XSLT button.

It opens the data entry window for you to place the XSL.

To view the XSL effect on the query, click on the Preview button.

The XSL which I placed, transforms the query into a  tabular structure and easy to go through.

If you want to generate the transformed report, then schedule the query and you have it!

Get a value from the XML

Okay so this is the scenario. You've got a response from the XML request you places via Integration Broker and you want to extract the data from a specific tag for further processing. The below code will help you to accomplish the same.

&lmsgGoRequest = CreateMessage(Operation.OPERATION_NAME, %IntBroker_Request);

&lmsgGoRequest.SetXmlDoc(&lxmlDoc);

&lmsgGoResponse = %IntBroker.SyncRequest(&lmsgGoRequest);

&MyDoc = CreateXmlDoc(&lmsgGoResponse.GenXMLString());

&field1List = &MyDoc.GetElementsByTagName("EMPLID");

WinMessage(&field1List [1].NodeValue);

&field1List is the array of the tags fetched from the XML (which you have specified) and NodeValue gives you the value of the tag.

Hope it helps!

Creating XML's with SOAP Envelop using IB

I was actually facing this requirement for the first time, when one of my colleague emailed asking for how to accomplish the same. PeopleBooks has clue how to do it, but i found a couple of links which can help further on the same.

Oracle Blog: IB Out Bound Soap Messages with WS-Security

Using PeopleSoft SOAP Classes

If anyone has a pre-experience on the same, you can share the code snippets/article here.

Writing If statement in XML Publisher: An example

Consider there is a field which will display negative and positive amount while the XML report is rendered. Now we have to insert a condition to make it to display Zero, when the amount is a negative value.

Before inserting the If condition to cater the above requirement:
<br /><?xdoxslt:set_variable($_XDOCTX, ’TotalVar’, fld_PRIOR_BALANCE)?><?xdoxslt:get_variable($_XDOCTX, 'TotalVar') + sum(fld_ITEM_AMT)?> <br />

After inserting the If condition:
<br /><?xdoxslt:set_variable($_XDOCTX, ’TotalVar’, fld_PRIOR_BALANCE)?><?xdoxslt:set_variable($_XDOCTX, ’TotalVar’,xdoxslt:get_variable($_XDOCTX, 'TotalVar') + sum(fld_ITEM_AMT))?>))?><?if:xdoxslt:get_variable($_XDOCTX, 'TotalVar')<0?>0<?end if?> <br />

Try to avoid spaces while writing the code. Main concepts to understand here is the Get_Variable and Set_Variable.

XMLP using PS Query Source

This code explains the usage of PSQuery as datasource. The report is generated in the appserver and it is transferred to the FTP  path prior to displaying it.

Creating PeopleSoft Reports Using XML Publisher

XML Publisher is now also called "Oracle Business Intelligence Publisher" (BI Publisher). It is a template-based reporting tool that leverages standard technologies for data extraction and display. XML Publisher segregates the Data Logic from the Presentation Layout during development of the reports and combines them at run time. As a result, although a technical consultant might independently design generalized data extraction logic (using Application Engines/PeopleCode or PSQuery), a functional consultant or end user can design the Presentation using common desktop tools such as Microsoft Word based on the data source created by the technical consultant. Because queries can also be used as a data source, XML Publisher extends the flexibility of the tool essentially to the same people who need to view the reports (end users). This means that end users with minimal training are able to design templates and reports based on generally designed data sources, thus greatly reducing the cost of ownership.

This tutorial covers the fundamental features of XML Publisher when it is integrated with Oracle PeopleSoft. You learn how to create templates and reports as well as how to publish your reports.

Link

XML Publisher - Dynamic Parameter passing from the Peoplecode

How to pass Dynamic parameter value to XML publisher Template using people code ?

How to pass Dynamic Parameter Value to XML Publisher from the runcontrol page ?

You need to pass these as runtime Properties through PeopleCode.The ReportDefn class has setRuntimeProperties() method that can be used for this purpose. So the PeopleCode behind your page will need to get the values from the page and pass them to the ReportDefn object.

Your PeopleCode will include something like this.

&asPropName = CreateArrayRept("", 0);&asPropValue = CreateArrayRept("", 0);

&asPropName.Push("xslt.ParameterName");

&asPropValue.Push("Parametervalue");You can pass more parameters here

&oRptDefn.SetRuntimeProperties(&asPropName, &asPropValue);

Process and publisher/print your ReptDefn here In your template, you need to have something like:Declares the parameter InvThresh use the Parameter value by prefixing $ to the Parameter Name.

PeopleBooks includes an example on setting Runtime properties. PeopleTools passes the following parameters by default:

ReportID

ReportTitle

RunDate

RunTime

XML Publisher Development

You can create a simple XMP Publisher report by answering these following questions:

How to setup XML Publisher?

How to create data sources?

How to create report templates?

How to define reports?

For starters, you need to add the XMLP Report Developer to your User Role. That will give your user access/security to Report Category, Design Helper, Data Source, Report Definition, Content Library, Template Translations, Query Report Viewer, Query Report Scheduler, and Report Repository. You also need to download the template design helper which is located to Reporting Tools > XML Publisher > Setup > Design Helper

Define report categories; this is for row level security of the data. Located to Reporting Tools > XML Publisher > Setup > Report Category.

Now you must understand that XML Publisher retrieves data from different source (e.g. PS Query, RowSet, XML File, and XMLDoc Objects). In this article we will used the simplest form, the PS Query.

Assuming that you already have a query, you need to specify the query to create a data source. Fill in all the required fields, generate Sample Data File and Schema File by clicking the Generate link.

You can now create your Report Definitions. There are five pages in the Report Definition component, only the first three pages are required the last two pages are for more complex reporting (Reporting Tools > XML Publisher > Report Definition).

Now you’re ready to run the report thru Query Report Viewer by clicking View Report link (Reporting Tools > XML Publisher > Query Report Viewer).

Using Rowset

The concept are like SQR, retrieving data from database via SQL SELECT, formating data and displaying data to a report usually in the form of PDF, CSV, etc.

In order to retrieve data using PeopleCode you need to populate a rowset.

import PSXP_XMLGEN:*;

/*Create Rowset*/

&rs = CreateRowset(Record.PERSONAl_DATA);

You need to fill this rowset with data by doing this;

/*Fill Rowset*/

&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

You'll notice that I have an import statement on top. That is an delivered Application Package, inside that package are methods that we will use in our code.

We will now create our Sample Data File and Schema File by running the code above and the code below.

/*Create Schema*/

&rds = create PSXP_XMLGEN:RowSetDS(); /*example package method*/

&mySchema = &rds.GetXSDSchema(&rs);

&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);

&f1.WriteLine(&mySchema);

&f1.Close();

/*Create Sample XML File*/

&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");

&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);

&f2.WriteLine(&myXMLFile);

&f2.Close();

This code will generate two files, an schema file with extension .xsd and sample data file with extension .xml. You need to upload the files in the report category page within PeopleSoft, create Data Source Definition (apply the things you learned from Part 1) choose Rowset data source type instead of PS Query. Also create the Report Definition and Process definition. Add few more lines to the Peoplecode and you will able to run and produce report. Your code should look like this;

import PSXP_RPTDEFNMANAGER:*;

import PSXP_XMLGEN:*;

&sRptDefn = "JOB_DEFN";

&sTemplateId = "JOB_TEMP";

&sLangCode = "";

&dtAsOfDate = %Date;

&sOutputFmt = "PDF";

&RptOutputDir = "c:\temp\" "XMLP";

&ReportDef.OutDestination = &RptOutputDir;

/*Set-Up Report*/

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

&ReportDef.Get();

/*Create Rowset*/

&rs = CreateRowset(Record.PERSONAl_DATA);

/*Fill Rowset*/

&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

/*Create Schema*/

&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/

&mySchema = &rds.GetXSDSchema(&rs);

&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);

&f1.WriteLine(&mySchema);

&f1.Close();

/*Create Sample XML File*/

&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");

&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);

&f2.WriteLine(&myXMLFile);

&f2.Close();

/* output format */

&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/

&ReportDef.SetRuntimeDataRowset(&rs);

/*Generate the Report*/

&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/

&ReportDef.Publish("", &RptOutputDir, "XMLP", JOB_AET.PROCESS_INSTANCE);

&sFileExt = GetFileExtension(&sOutputFormat);

Sending e-mail

Customize the above code to send email with the XMLP report output as an attachment

import PSXP_RPTDEFNMANAGER:*;

import PSXP_XMLGEN:*;

import PT_MCF_MAIL:*;

/*Create an email object by setting individual parameters*/

Local PT_MCF_MAIL:MCFOutboundEmail &eMail = create PT_MCF_MAIL:MCFOutboundEmail();

&sRptDefn = "JOB_DEFN";

&sTemplateId = "JOB_TEMP";

&sLangCode = "";

&dtAsOfDate = %Date;

&sOutputFmt = "PDF";

&RptOutputDir = "c:\temp\" "XMLP";

&ReportDef.OutDestination = &RptOutputDir;

/*Set-Up Report*/

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

&ReportDef.Get();

/*Create Rowset*/

&rs = CreateRowset(Record.PERSONAl_DATA);

/*Fill Rowset*/

&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

/*Create Schema*/

&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/

&mySchema = &rds.GetXSDSchema(&rs);

&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);

&f1.WriteLine(&mySchema);

&f1.Close();

/*Create Sample XML File*/

&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");

&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);

&f2.WriteLine(&myXMLFile);

&f2.Close();

/* output format */

&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/

&ReportDef.SetRuntimeDataRowset(&rs);

/*Generate the Report*/

&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/

&ReportDef.Publish("", &RptOutputDir, "XMLP", JOB_AET.PROCESS_INSTANCE);

&sFileExt = GetFileExtension(&sOutputFormat);

/*Send Mail*/

&ToList = "to_user@yahoo.com";

&FromList = "from_user@acme.com";

&ReplyToList = "from_user@acme.com";

&Subject = "Batch Run Email";

&eMail.Recipients = &ToList; /*comma separeted list of email addresses*/

&eMail.From = &FromList; /*from email address*/

&eMail.ReplyTo = &ReplyToList; /*in case the reply is to be sent to a different email address*/

&eMail.Subject = &Subject;

/*Body for multiple parts*/

Local string &plain_text = "Test for XML Email from PeopleSoft";

Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();

&text.Text = &plain_text;

Local PT_MCF_MAIL:MCFBodyPart &attach = create PT_MCF_MAIL:MCFBodyPart();

&attach.SetAttachmentContent(&RptOutputDir "JOB_DEFN.pdf", %FilePath_Absolute, "JOB_DEFN.pdf", "JOB_DEFN", "", "");

Local PT_MCF_MAIL:MCFMultiPart &mp = create PT_MCF_MAIL:MCFMultiPart();

&mp.AddBodyPart(&text);

&mp.AddBodyPart(&attach);

&eMail.Multipart = ∓

/*Override the default SMTP parameters specified in app server configuration file*/

&eMail.SMTPServer = "smtp.service.acme.com"; /*just an example*/

&eMail.SMTPPort = 25; /*usually this is 25 by default*/

Local integer &resp = &eMail.Send();

/*now check &resp for the result*/

Local boolean &done;

Evaluate &resp

When %ObEmail_Delivered

/*every thing ok*/

&done = True;

Break;

When %ObEmail_NotDelivered

/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/

&done = False;

Break;

When %ObEmail_PartiallyDelivered

/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/

&done = True;

Break;

When %ObEmail_FailedBeforeSending

/*get the formatted messages from &eMail.ErrorDescription, &eMail.ErrorDetails*/

&done = False;

Break;

End-Evaluate;

CommitWork();

App engine+XMLP+PS Query:

Just replace the Rowset code in the with this code;

/*fill prompt record*/

&rcdQryPrompts = &ReportDef.GetPSQueryPromptRecord();

If Not &rcdQryPrompts = Null Then

If Not Prompt(&ReportDef.GetDataSource().Name, "", &rcdQryPrompts) Then

Exit;

End-If;

&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

End-If;

Service Oriented Architecture

Most common demand while implementing software application for a client is to have a seamless integration betweent the various applications they use. Say, the client is using five different applications for his day to day operation. He want the information from one appplication to be injected into the second while some modification/change of data happens in the first application.

Most of the applications serves this requirement by providing an interface for integration so called the integration point. Through that facility the application can send or consume information from any thrid party applications or from within itself. And they are built on a Service oriented Architecture model.

What mostly shared between the applications, will be the functionalities/actions that they serve. In SOA perspective, those functtionalities are called Services, which are a shared among the applications.

XML (Extensible Markup Language) is widely used for ccommunications between the applications which boosted the concept of SOA.