Run PS Query Report in Application Engine and 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;

 

PS/Query to a file using PeopleCode

1. Invoke the GetQuery method on the PeopleSoft session object to get a query.

/*create a query API object*/

Local ApiObject &aRunQry;

/*create a session API object*/

Local Session &MySession;

 

/*The current session*/

&MySession = %Session;

If &MySession <> Null Then

/*The GetQuery method returns an empty query object. After you have an empty query object, you can use it to open an existing query*/

    &aRunQry= &MySession.GetQuery();

End-If;

 

2. Open the specific query you want using Open method.

&aRunQry.Open("MY_TEST_QUERY", True, False);

 

3. Adding runtime prompt record to the query as an instance of a PeopleCode record object (Optional)

/* Obtain the PromptRecord for the query*/

Local Record &aQryPromptRec;

&aQryPromptRec = &aRunQry.PromptRecord;

 

This instance of the PromptRecord can be passed to the PeopleCode Prompt function to prompt the user for the runtime values, as follows:

 

&nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec);

 

/* Populate the runtime parameters */

If &aQryPromptRec <> Null Then

&nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec);

End-If;

 

4. Run the query to a file

/* Run the query output for txt in CSV format */

If (&aRunQry.RunToFile(&aQryPromptRec, "c:\temp\" | &aRunQry.Name, %Query_TXT, 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;

 

PS Query Example – With Prompts

Example-1

Local Object &Session = %Session;

Local Object &PageQuery = &Session.GetQuery();

Local Rowset &Results_RS;

 

/* Check the user has access to the query */     

If &PageQuery.Open("QUERY_NAME", True, False) = 0 Then

   /* Set query prompts */

   &queryPrompts = &PageQuery.PromptRecord;

   &queryPrompts.OPRID.Value = %OperatorId;

   &queryPrompts.MENUNAME.Value = "MENU_NAME";

   &queryPrompts.PNLNAME.Value = "PAGE_NAME";

 

   &Results_RS = &PageQuery.RunToRowset(&queryPrompts, 0);

Else

   Error "No access to query QUERY_NAME";

End-If;

 

Example-2

Local ApiObject &aRunQry;

&aRunQry = %Session.GetQuery();
If (&aRunQry.Open(“KP_AFWIJKENDE_ROLLEN”, True, False) <> 0) Then
   MessageBox(0, “”, 0, 0, “Error in opening query”);
Else
   If (&aRunQry.RunToFile(&aQryPromptRec, &aRunQry.Name, %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;