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

Epicor ERP 10
1

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:

image
image1212×266 46.7 KB

image
image1491×989 112 KB

Many thanks in advance
Shizar

2

@klincecum maybe you have some idea?

3

@Mark_Wonsil Maybe you can suggest a solution?

4

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

Bored Number 2 GIF by Pudgy Penguins

5

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”

6
1 Like
7

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: :

image
image2025×947 385 KB

I am also getting two errors:

image
image1888×622 147 KB

8

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

Db.SysRptLst etc

9

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
10

No worries
Hopefully someone else can assist

11

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
12

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

13

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.

image
image937×363 59.8 KB