Trigger ARInvoice Generation from API - not totally getting it

[EpiDan (Dan)]

Hi thanks for having me! I had a C# program in 9.05.702 that was triggered from the form that shows you a list of invoices in a given batch. Customer Service would click GO, a text file would be generated and handed to the program. Then The C# program would:

0. Grab an Epicor session.
1. Ask Epicor to Generate an invoice for that invoice number, wait for the task ID to be posted back to the task manager table then snatch up that XML file from the /reports directory.
2. Use C# to trigger Crystal to generate that report and save it as a PDF on the network.
3. Locate the correct customer number from the Invoice ID for that specific ship to address and find the correct contact in Epicor to send it to.
4. Email out the invoice and add the success or failure to a log.
5. Lather, Rinse, repeat for the list of invoices
6. Send out a final summary to customer service in an HTML email that highlighted if any invoice wasn’t sent or wasn’t generated correctly.
7. Release the license that it was using.

A bit slow at the Crystal phase but it did it’s job… But we just went live with 10.2.500.2 and have said our final goodbyes to Crystal. I think I can take a much much easier approach now and I am looking that API but I can’t find documentation with a few questions and a nudge in the right direction would really help me out. Basically if I may give an example:

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{ \ 
"ds": { \ 
"ARInvFormParam": [ \ 
   { \ 
     "InvoiceNum": 206106, \ 
     "CurGroup": "string", \ 
     "PrintNPost": true, \ 
     "Vouchering": true, \ 
     "AssignLegalNumber": true, \ 
     "Description": "string", \ 
     "DocTypeOption": "string", \ 
     "EnableAssignLegalNumber": true, \ 
     "PrintStatusOption": "string", \ 
     "TranDocTypeID": "string", \ 
     "TransOptions": "string", \ 
     "ARInvFormReportID": "string", \ 
     "THPlaceOfBusiness": "string", \ 
     "TWCompInvPart1": true, \ 
     "TWCompInvPart2": true, \ 
     "TWCompInvPart3Front": true, \ 
     "TWCompInvPart3Back": true, \ 
     "TWEInvTransDetails": true, \ 
     "TWEInvPrizeCollection": true, \ 
     "CalledFrom": "string", \ 
     "EnableUseCopyNumInARInv": true, \ 
     "DraftCopy": true, \ 
     "DspDocCopyNum": "string", \ 
     "SysRowID": "00000000-0000-0000-0000-000000000000", \ 
     "AutoAction": "SSRSPREVIEW", \ 
     "PrinterName": "string", \ 
     "AgentSchedNum": "0", \ 
     "AgentID": "SystemTaskAgent", \ 
     "AgentTaskNum": 0, \ 
     "RecurringTask": false, \ 
     "RptPageSettings": "string", \ 
     "RptPrinterSettings": "string", \ 
     "RptVersion": "string", \ 
     "ReportStyleNum": 2, \ 
     "WorkstationID": "string", \ 
     "TaskNote": "string", \ 
     "ArchiveCode": 0, \ 
     "DateFormat": "string", \ 
     "NumericFormat": "string", \ 
     "AgentCompareString": "string", \ 
     "ProcessID": "string", \ 
     "ProcessCompany": "string", \ 
     "ProcessSystemCode": "string", \ 
     "ProcessTaskNum": 0, \ 
     "DecimalsGeneral": 0, \ 
     "DecimalsCost": 0, \ 
     "DecimalsPrice": 0, \ 
     "GlbDecimalsGeneral": 0, \ 
     "GlbDecimalsCost": 0, \ 
     "GlbDecimalsPrice": 0, \ 
     "FaxSubject": "string", \ 
     "FaxTo": "string", \ 
     "FaxNumber": "string", \ 
     "EMailTo": "string", \ 
     "EMailCC": "string", \ 
     "EMailBCC": "string", \ 
     "EMailBody": "string", \ 
     "AttachmentType": "string", \ 
     "ReportCurrencyCode": "string", \ 
     "ReportCultureCode": "string", \ 
     "SSRSRenderFormat": "PDF", \ 
     "UIXml": "string", \ 
     "PrintReportParameters": true, \ 
     "SSRSEnableRouting": true, \ 
     "DesignMode": true, \ 
     "RowMod": "string" \ 
   } \ 
 ], \ 
 "ReportStyle": [ \ 
   { \ 
     "Company": "CMX100", \ 
     "ReportID": "string", \ 
     "StyleNum": 0, \ 
     "StyleDescription": "string", \ 
     "RptTypeID": "string", \ 
     "PrintProgram": "string", \ 
     "PrintProgramOptions": "string", \ 
     "RptDefID": "string", \ 
     "CompanyList": "string", \ 
     "ServerNum": 0, \ 
     "OutputLocation": "string", \ 
     "OutputEDI": "string", \ 
     "SystemFlag": true, \ 
     "CGCCode": "string", \ 
     "SysRevID": "0", \ 
     "SysRowID": "00000000-0000-0000-0000-000000000000", \ 
     "RptCriteriaSetID": "string", \ 
     "RptStructuredOutputDefID": "string", \ 
     "StructuredOutputEnabled": true, \ 
     "RequireSubmissionID": true, \ 
     "AllowResetAfterSubmit": true, \ 
     "HasBAQOrEI": true, \ 
     "BitFlag": 0, \ 
     "ReportRptDescription": "string", \ 
     "RptDefRptDescription": "string", \ 
     "RptTypeRptTypeDescription": "string", \ 
     "RowMod": "string" \ 
   } \ 
 ]}, \ 
"agentID": "SystemTaskAgent", \ 
"agentSchedNum": 0, \ 
"agentTaskNum": 0, \ 
"maintProgram": "string" \ 
 }' 'https://cmxepicorapp5/epicor102/api/v1/Erp.RPT.ARInvFormSvc/SubmitToAgent

I get a 200 response back which is nice but - can I get the PDF back? I saw another post where they were getting a datastream of the pdf back but maybe I am misunderstanding. I’d be happiest with the datastream but saving the PDF someplace works too… Also obviously there is something I haven’t read - any points to docs would be awesome. Also I know I must have extra parameters in there I don’t need, I was going from the example text in the /api/help/v1/methods stuff

Thanks in advance! So happy we’re finally 102500 and I am loving this API.

EDIT: I found v2 API and am using that now but similar results with out knowing what parameters to include

[Jason_Woods]

Do you have Advanced Print Routing? If so, you can email from Epicor instead of the outside program.
You may also be able to email it by setting the “EmailTo”…

[EpiDan (Dan)]

I don’t believe we do - unless thats the same as APM? I tried to setup APM but found it to be pretty out dated and clunky… an enter separate server so this software could read from the print spooler and OCR an email address from it at a specific spot in the page? seemed pretty 1990’s. Apologies if APR is APM it’s been a year or two since I looked at it. Also my system scans the company contacts for the correct person to send this specific invoice to instead of pulling the default (maybe that did too I am not sure)

[Jason_Woods]

APR is not APM. APR is built into Epicor and is a separate license. If you don’t own it, but you send PDFs from Epicor via email, you may want to look at it.
However, try setting the “EmailTo” value on the agent. If that fails, run a trace when manually printing from Epicor where you email the results and see what else is missing.

[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’