How to run a PeopleSoft PS Query and generate output file from Application Engine.

The content of this doodle is an  unusual thing which doesn't look meaningful because you can run the PS Query through PeopleSoft tool to just generate the output. Sometimes you may come across situation where you might have to generate the output in different formats for different group of users. In this case this approach will be helpful.

Below is the PeopleCode snippet that can be used in your AE to achieve this requirement. For easy reading I have shown the description of some of the functions and methods from PeopleBooks.


&PSQry1 = %Session;

&PSQry = &PSQry1.GetQuery();   /*The GetQuery method returns an empty query object.*/

/*The Open method opens the query object specified by the parameters. The Open method can be used only with a closed query, it cannot be used on an open query. You cannot read or set any properties of a query until after you open it.*/

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

   &fileLogLM.WriteLine("Cannot Open the Query");

Else

   &aQryPromptRec = &PSQry.PromptRecord;

   &aQryPromptRec.EFFDT.Value = %Date;

   &Date_Format = DateTimeToLocalizedString(%Date, "dd-MMM-yyyy");

/* Use the RunToFile method to execute the Query and return the result to the file specified with Destination.*/

   If (&PSQry.RunToFile(&aQryPromptRec, "\\temp\PSQUERY_NAME_Output" | &Date_Format | "", %Query_XLS, 0) = 0) Then

      &fileLogLM.WriteLine("Report is generated successfully");

  Else

      Error ("Unable to open the Query");

   End-If;