Trigger ARInvoice Generation from API - not totally getting it

[krum (Kevin Krumwiede)]

You can read a report PDF as a stream simply by making a HTTP request to SSRS. Something like this:

string URL = "http://host/path/Pages/ReportViewer.aspx?%2freports%2fCustomReports%2fReportName&rs:Command=Render&rs:Format=PDF&OrderNum=" + row.OrderNum;
System.Net.HttpWebRequest Req = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(URL);
Req.Credentials = System.Net.CredentialCache.DefaultCredentials;
Req.Method = "GET";
System.Net.WebResponse objResponse = Req.GetResponse();

The problem is, most of the stock SSRS reports are designed to take the GUID of a report dataset as their only parameter, not domain values like an OrderNum or InvoiceNum. I’d like to know how you

Ask Epicor to Generate an invoice for that invoice number

If the report style is set to output to database rather than XML, running the report should result in a GUID being generated and a set of tables with that GUID in their names being created in the EpicorEnvironmentName.Reports database (e.g., Live.Reports). But I’m not sure how you’d obtain that GUID to pass it to SSRS.

[Mark_Wonsil]

The System Monitor should be able to get the GUID but not the Document number since you can be printing more than one document per print session.

One might be able to put the document ID in the User Description and then you would know both.

But once you’ve done that work, you have what you need to get the byte stream from the Monitor…

[krum (Kevin Krumwiede)]

You can also get report GUIDs from SQL:

select UserID, RptDescription, CreatedOn, PurgeDate, right(FileName, 32) as ReportGuid
from Test.Ice.SysRptLst
where Archived = 1
and UserId = 'joeblow'
order by CreatedOn desc

It’s easy enough for a human to recognize the report they just ran. Maybe you could put some kind of ID in RptDescription for an integration to recognize specific runs of a report.

[Rich (Richard Riley)]

So going back to the original request - ERP 10 stores the completed report in the Database in the SysRptLst Table, RptData Field. The ReportMonitor Service has a Method GetReportBytes, that takes a single parameter of the SysRowID value for the SysRptLst record and it returns the completed report stored in the RptData field for that record.

I believe that the GetRows Method of the SysMonitorTasks Service will return the information you need to select the report that you want to return (use client tracing to see the filter parameters to send in) and if you set the User Description field when you submit the report, that value will be in the UserDescription field returned in the GetRows call.

[josecgomez]

I sound like a broken record but I use a UBAQ. UBAQ UBAQ UBAQ UBAQ why fiddle with all this stuff when you don’t have to. In 10.2.500 change UBAQ to Func but same concept

Simply write a BAQ which takes in a Parameter (Invoice Number?)
Then in the UBAQ Post Processing on GetList() you can do this

foreach(var x in ttResults)
{
using (var ar = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.ARInvFormSvcContract>(Db))
  {
    ARInvFormTableset ARts = null;
    ARts =ar.GetNewParameters();
    ar.PackNumDefaults(ref ARts);
    ARts.ARInvFormParam[0].AgentID = "SystemTaskAgent";
    ARts.ARInvFormParam[0].AutoAction = "SSRSGenerate";
    ARts.ARInvFormParam[0].CalledFrom = "Erp.UI.ARInvoiceTracker";
    ARts.ARInvFormParam[0].DateFormat = "m/d/yyyy";
    ARts.ARInvFormParam[0].InvoiceNum = x.InvcHead_InvoiceNum;
    ARts.ARInvFormParam[0].NumericFormat = ",.";
    ARts.ARInvFormParam[0].ReportCultureCode = "en-US";
    ARts.ARInvFormParam[0].ReportCurrencyCode = "USD";
    ARts.ARInvFormParam[0].SSRSRenderFormat = "PDF";
     
    ARts.ARInvFormParam[0].TaskNote = taskNote.ToString();
    x.Calculated_ReportGUID = taskNote.ToString();
    ar.RunDirect(ARts); //This runs the report right away but it hangs until it finishes...
   
    //ar.SubmitToAgent( ARts, "SystemTaskAgent",0,0,"Erp.UIRpt.ARInvForm"); this uses the system agent and returns right away but you'll have to POLL until your Report is Ready
  }
}

Note that I put a GUID (my own GUID) in TaskNotes and return that back with my UBAQ Response Data in Calculated_ReportGUID Field

Now the BAQ will return back (VIA REST) the GUID of the Report that I just ran.
Then Simply you can use another UBAQ to get the Report Data out of the table once the Task is Finished.
The ReportBytes (after it has run) are stored in SysRptList (SSRS Only)
So your other BAQ should be pretty simple (see below) is just a query to SysRptList where the Data is Filtered by the TaskNote of my generated GUID (as a Parameter)

So in other words,
You make a simple
GET Request to your first UBAQ with InvoiceNum as a Paramter
It returns a GUID in Calculated_ReportGuid field
Which you then use to make another
GET Request to BAQ #2 which will return the Report Bytes

[Mark_Wonsil]

So, you’re saying we should use a UBAQ?

In the future, and I’m sure that @josecgomez would agree, when you get to 10.2.500 the UBAQ can be replaced with an Epicor Function. Same idea, just a little cleaner.

[josecgomez]

Here’s a quick walk-through of the above solution

[EpiDan (Dan)]

As with all my weird issues, Jose has the exactly correct answer. Thank you I will watch this video after lunch and the code you posted looks like exactly what I needed.

Dan Flood.

[EpiDan (Dan)]

I can access the correct RptData field- Not sure how it’s encoded however. I’d like to just snag that hex string and convert it to bytes in C# and File.WriteAllBytes(“myPDF.pdf”, ); Not sure how to convert that data though…

[josecgomez]

 byte[] pdfAry = System.Convert.FromBase64String("Hex String");

File.WriteAllBytes(path, pdfAry );

[EpiDan (Dan)]

Thanks this works like a charm now

string exampleGUID = "9f0bdbf60e774aca8bed4846fdeedf08";
string strConnect = $"server={s};user id={u};password={p};database={d}";
SqlConnection conn = new SqlConnection(strConnect);
conn.Open();
SqlCommand cmd = new SqlCommand($"SELECT rptdata from Ice.SysRptLst WHERE RIGHT(filename,32)='{exampleGUID}';", conn);
byte[] results = (byte[])cmd.ExecuteScalar();
System.IO.File.WriteAllBytes(@"C:\PDF\test3.pdf", results);

However I am just realizing I don’t know what references to make, this is my first E10 project. I used dotPEEK to seach for Ice.Assemblies but this computer is so slow it’s been searching for two hours.

So Far I’ve included:

Ice.Core.Session.dll
Erp.Common.ContractInterfaces.dll
Erp.Contracts.BO.ARInvoice.dll

[josecgomez]

References to make to what? I thought you were using REST?

[EpiDan (Dan)]

Next project - too many people waiting for this on my back so a new new lines of code and it’s back in business (just an external .net 472 exe file) My ambition was to rework it using the new REST stuff but too much other stuff to carry at the moment.

I just need to reach into Epicor ,trigger the invoice generation for invoice x, add my own guid to the UserDescription field, then find the Epicor report GUID in the filename field.

Here’s my 9.05 code - code explains better than me:

foreach (WorkLoadClass w in wl)
{
    // Submit for processing.
    try
    {
        ds.Tables["ARInvFormParam"].Rows[0]["InvoiceNum"] = w.getInvoiceNumber();
        ds.Tables["ARInvFormParam"].Rows[0]["PrinterName"] = "";
        ds.Tables["ARInvFormParam"].Rows[0]["ReportStyleNum"] = vars.epReportStyle;
        ds.Tables["ARInvFormParam"].Rows[0]["AgentID"] = "SystemTaskAgent";
        epiARInvForm.SubmitToAgent(ds, "SystemTaskAgent", runnow, newtask, "Epicor.Mfg.UI.Rpt.ARInvForm");
        mf.at($"Submited Invoice {w.getInvoiceNumber()} for processing.", colorinfo, true);  // Populates textbox on main thread with whats going on.
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"ds.tabled error: {ex.Message}");
        }
        WaitForXMLFileName(w);    // wait for Epicor to populate field, error out after a few seconds.
}

[josecgomez]

The easiest way to do that from .net is to use

EpicorRestAPI 2.1.0.3

Make the Rest call as outlined and use the UBAQ approach outlined here

Still all .net and you can use it directly in your external project.

[josecgomez]

Alternatively you could use WCF

Epicor Switches from TableSet to DataSet ... mid service

I can respond to the "WTF Epicor" question (it's something I do frequently).  Tablesets are just POCOs (plain old class objects).  We decided not to use Datasets on the server because they do not interoperate or serialize as well as a regular class.  We also annotate the types and members with WCF attributes to optimize communications and (attempt to) maximize interoperability with non-Windows SOAP stacks.  Also, POCOs work great with Lync (better than Datasets, IMO). But the client controls …

[EpiDan (Dan)]

@josecgomez

ARts.ARInvFormParam[0].DateFormat = “m/d/yyyy”;

I’m really looking for the correct path to your line of code that says this:

Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.ARInvFormSvcContract>

I cannot find the correct ‘using’ statement to include to locate ‘Ice.Assemblies’

[EpiDan (Dan)]

Even when i use your (amazing and priceless - thank you!) plugin I can’t find it:

[josecgomez]

Server renderer hmm that’s on the BPM server side in the UBAQ

If you wanna do it client side/ external you’ll have to instanciate your WCF proxy (as outlined in the post above )
Or use REST

If I have some time I’ll try to give you a more concrete example tomorro
Been slammed today

[EpiDan (Dan)]

No problem – I found the correct DLL file so that’s ok… I’m plowing ahead with my old way and trying to get REST going at the same time… Lots to learn lol

[ShawnLeenerts]

Could you not do the same with this query?

I’m only wondering why you would need to inner join with SysTask.