Running Connected Query BIP / XMLP through Peoplecode / AE Process

Hi All,

Most of us tend to find the Answer for this Question "How to I execute Connected Query XMLP report through Peoplecode or AE Peoplecode?"

Here it is, what you are seeking for,

import PSXP_RPTDEFNMANAGER:ReportDefn;
import PT_CONQRS:QUERYITEMPROMPT:*; 
import PT_CONQRS:CONQRSMGR:*; 

Global string &BU, &Invoice; 

Local array of PT_CONQRS:QUERYITEMPROMPT &prompts; 
Local File &xmlFile; &reportDefinition = "XX_CONNQRY"; 
&connQueryName = "BI_INV_PRNT_CQRY_V1";

 /* Start by creating the Connected Query Object */ 
&connQueryObject = create PT_CONQRS:CONQRSMGR("", &connQueryName); 
If Not (&connQueryObject.Open( False)) Then 
/* If it can't be opened, throw an error */ 
 Error ("Failed to open Connected Query."); 
End-If;

If Not (&connQueryObject.Validate()) Then 
/* Validate that all PSQueries within the Connected Query Exist */ 
 Error ("Unable to validate Connected Query."); End-If; 
 /* Can only be executed in Online pages */ 
<*If Not &connQueryObject.SetRunControlData("", False, False) Then 
 Warning "Unable to set the RunControl Data for Connected Query"; 
End-If;*> 

 &prompts = &connQueryObject.QueriesPromptsArray; 
/* This will return an Array of Records with the prompts for each Query within the Connected Query */ 
&promptRecord = &prompts [1].QueryPromptRecord; 
/* Set the key fields */ 
&promptRecord.BUSINESS_UNIT.Value = &BU; 
&promptRecord.INVOICE.Value = &Invoice; 

 /* Run the Connected Query to a String object */ 
&xmlString = &connQueryObject.RunToXMLFormattedString(&prompts); 

/* However, since it is "formatted" XML Publisher will insert Carriage Return and Line Feeds in the report. * We have to strip these out */ 
&xmlString = Substitute(&xmlString, Char(13), ""); /*13 = Carriage Return */
&xmlString = Substitute(&xmlString, Char(10), "");/*10 = Line Feed*/ 
&xmlString = Substitute(&xmlString, Char(9), ""); /*9 = Tab*/ 

 /* Now that the string is without formatting, write it out to an XML file. * Make sure to use a unique filename here, just in case it's ran from multiple locations */ 
&xmlFileName = &connQueryName | "_" | &BU | "_" | &Invoice | ".xml"; 
&xmlFile = GetFile(&xmlFileName, "W"); 
&xmlFile.WriteString(&xmlString); 
&xmlFilePath = &xmlFile.Name; 
/* Might be useful for Opening the XML File */ 
&xmlFile.Close(); 

 /* Now it's time for XML Publisher to do it's work. Create the Report Definition Object and Get() the report*/ 
&reportDefnObject = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportDefinition); 
&reportDefnObject.Get(); 

 /* Set this if you want the output file with Dynamic Name (filetype is not required) */ 
&OutputFile = &BU | "_" | &Invoice; 
&reportDefnObject.ReportFileName = &OutputFile; 

 /* Since the Report Definition's Data Source is an XML file, we can pass in the file we created at runtime */ 
&reportDefnObject.SetRuntimeDataXMLFile("C:\temp\" | &xmlFileName);

/* Process the report, thus running XML Publisher against the XML file that was created from the Connected Query */
&reportDefnObject.ProcessReport("", "", %Date, "PDF");

/* Execute this if you are in AE to publish output in Report Manager */
&reportDefnObject.Publish("", "", "", &ProcessInstance);

/* Use the DisplayOutput() to open Output Directly */
/* &reportDefnObject.DisplayOutput(); */

/* Now to clean up the file on the App Server, don't want any junk lying around out there.
* If you need to look at the file for debugging purposes, obviously comment this out.
* The file, by default, is placed on the $PS_HOME/appserv//files directory */
&xmlFile = GetFile(&xmlFileName, "W");
&xmlFile.Delete();


Please mark a comment below, if you need any clarity..