Send an Email containing output of an SSRS template using Data Directive

Dear Epicor Community,

I am looking for a way to send an email containing POForm report style’s output, when the PO is approved.

Can you suggest a sample piece of code?
I am able to send the email without the PDF, my issue is generating and attaching the POForm’s output to the email.

I have tried AutoPrint however it only contains POHeader data definition and does not contain the rest of the required datasets. BAQ reports are sent flawlessly this way however SSRS reports all face this issue:
I get this error using AutoPrint:


Many thanks in advance
Shizar

@klincecum maybe you have some idea?

@Mark_Wonsil Maybe you can suggest a solution?

Don’t ping me when I’m taking a dump?

Bored Number 2 GIF by Pudgy Penguins

Run a trace and see how the POForm report is being called.

I think I have some relevant thread on getting the data out.
I know Jose does.

Search for “tasknote”

1 Like

Thank you very much for this, seems a very promising lead.

I am having difficulty finding the last two tables, am I doing it wrong?
I cannot see them in the list, many thanks in advance :slight_smile: :

I am also getting two errors:

Yes, I just mentioned “ref” as in literally referring to the tables used.

Db.SysRptLst etc

Yes, the example was not specific to POForm, you will have to trace and interpret how to do it from there. I would assist, but I do not have the time.

:beers:

1 Like

No worries
Hopefully someone else can assist

Check to see if you have the Advanced Print Routing module.

APR was designed to break a print run up into individual segments and then email to the appropriate address(es).

1 Like

I have my POForm report style set up to email using the Advanced Print Routing (APR).

This is resolved with @klincecum 's input, however I had to make it work since it was not quite working.

First I added these references:
image

Then I added these Assemblies:
image
The last 2 are not required, I forgot to remove them since I used a different method to send the email.

And this is the code:


//using Erp.Tablesets;
//using Newtonsoft.Json;

//ref: Assembly -> Newtonsoft.Json
//ref: Libraries -> EmailLibrary
//ref: Services -> ERP.Rpt.SalesOrderAck
//ref: Tables -> ICE.SysRptLst (Read Only)
//ref: Tables -> ICE.SysTask (Read Only)

string company = "";

int custnum = 0;
int btcustnum = 0;
int shiptocustnum = 0;
string shiptonum = "";

string billto = "";
string soldto = "";
string shipto = "";

int ordernum = 0;
int ponum = 0;

string shipviacomment = "";


Func<int, byte[]> GetASalesOrderPDF = (poNum) =>
{
    byte[] reportPDF = null;
    
    // Correct service call usage
    var serviceAgent = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.POFormSvcContract>(Db);
    
    string taskNote = Guid.NewGuid().ToString(); // Unique identifier for this task
    
    var poAckTS = serviceAgent.GetNewParameters();
    var paramRow = poAckTS.POFormParam.First();

    paramRow.PONum = poNum;
    paramRow.AgentID = "SystemTaskAgent";
    paramRow.AutoAction = "SSRSGENERATE";
    paramRow.ReportStyleNum = 1006;
    paramRow.TaskNote = taskNote;
    
    // Execute the service operation
    serviceAgent.RunDirect(poAckTS);

    // Fetch the report data
    reportPDF = (from sysRpt in Db.SysRptLst
                 join sysTask in Db.SysTask on
                 new { sysRpt.Company, sysRpt.SysTaskNum } equals
                 new { sysTask.Company, sysTask.SysTaskNum }
                 where sysTask.TaskNote == taskNote
                 select sysRpt.RptData).FirstOrDefault();

    return reportPDF;
};

foreach(var poheaderrow in ttPOHeader){
        company = poheaderrow.Company;
        ponum= poheaderrow.PONum;
        }
            
            
foreach (var PORelRow in (from PORel_Row in Db.PORel
  where  PORel_Row.Company == company && PORel_Row.PONum == ponum 
  select PORel_Row))
            {
              ordernum = PORelRow.BTOOrderNum;
            }
            
            
foreach (var OrderHedRow in (from OrderHed_Row in Db.OrderHed
  where  OrderHed_Row.Company == company && OrderHed_Row.OrderNum == ordernum 
  select OrderHed_Row))
            {
              custnum = OrderHedRow.CustNum;
              btcustnum = OrderHedRow.BTCustNum;
              shiptocustnum = OrderHedRow.ShipToCustNum;
              shiptonum = OrderHedRow.ShipToNum;
              shipviacomment = OrderHedRow["DefaultShipViaComment_c"].ToString();
              
            }
            

foreach (var CustomerRow in (from Customer_Row in Db.Customer
  where  Customer_Row.Company == company && Customer_Row.CustNum == custnum 
  select Customer_Row))
            {
              soldto = CustomerRow.Name;
            }
            
foreach (var CustomerRow in (from Customer_Row in Db.Customer
  where  Customer_Row.Company == company && Customer_Row.CustNum == btcustnum 
  select Customer_Row))
            {
              billto = CustomerRow.Name;
            }

foreach (var ShipToRow in (from ShipTo_Row in Db.ShipTo
  where  ShipTo_Row.Company == company && ShipTo_Row.CustNum == shiptocustnum &&  ShipTo_Row.ShipToNum == shiptonum
  select ShipTo_Row))
            {
              shipto = ShipToRow.Name;
            }
            

try{
Dictionary<string, byte[]> dicReports = new Dictionary<string, byte[]>();

int salesOrder1 = ponum;
//int salesOrder2 = 26680;

dicReports.Add($"Report_{salesOrder1}.pdf", GetASalesOrderPDF(salesOrder1));
//dicReports.Add($"Report_{salesOrder2}.pdf", GetASalesOrderPDF(salesOrder2));

string jsonDicReports = JsonConvert.SerializeObject(dicReports);


//this.EfxLib.EmailLibrary.Email(fromEmail, toEmail, "Test 2 Report Attachments", "Hello, here are two reports", jsonDicReports, false, false, "", "", "", "", "");
string body = "Please accept this PO for parts purchase.<br><br><br>"+ "Sold To:"+soldto+"<br>"+"Bill To To:"+billto+"<br>"+"Ship To:"+shipto+"<br>"+"<br>"    +"ShipVia Default Comment:"+shipviacomment+"<br><br>"  +"Please add sales@ABC.com to UPS Tracking<br>"+"Please add to Reference Number 3:"+ponum.ToString()+"<br>"+"Please add to Reference Number 4:"+ordernum+"<br>";
string subject = "Part Order Approved" ;
//var mailer = this.GetMailer(async: true);
//var message = new Ice.Mail.SmtpMail();
var mailer = this.GetMailer(async: true);
var message = new Ice.Mail.SmtpMail();
message.SetFrom("epicor@ABC.com");
message.SetTo(body);
message.SetTo("shizar@ABC.com;");
message.SetCC("shizar@ABC.com;");
//message.SetBcc() 
//message.SetBody("Test");
message.SetBody(body);
message.SetSubject(subject);
message.IsBodyHtml = true;
mailer.Send(message,dicReports);
}
catch(Exception e)
{
string body = e.ToString();
    this.PublishInfoMessage(body, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar"); 
    }

I also caught the trigger on Pre and transferred it to Post (should be Method Directive, Update) and this code is in Post.