Automated IRN Error Monitoring and Email Notification in D365FO

 

Purpose

The purpose of this document is to explain the functionality of the MyAutoMailForIRNErrorLog batch class in Microsoft Dynamics 365 Finance and Operations (D365FO). This class is designed to automatically identify invoices for which IRN (Invoice Reference Number) generation has failed, generate an Excel report containing the error details, and send the report to configured business users via email.

This functionality can be useful for the following scenarios:

• Automatically monitoring failed IRN generation transactions.
• Providing daily visibility of e-Invoice processing errors to Finance and GST teams.
• Reducing manual effort required to review IRN error logs.
• Enabling faster resolution of e-Invoice validation and integration issues.
• Maintaining compliance by ensuring failed IRN transactions are promptly addressed.

 

Product

Microsoft Dynamics 365 Finance and Operations (D365FO)

Development Approach

The solution is implemented as a RunBaseBatch class, allowing it to be executed manually or scheduled as a batch job.

 final class  MyAutoMailForIRNErrorLog extends RunBaseBatch

{

    #define.CurrentVersion(1)

}

    public static void main(Args _args)

    {

         MyAutoMailForIRNErrorLog    AutoMailForIRNError = new   MyAutoMailForIRNErrorLog();

        if(AutoMailForIRNError.prompt())

        {

            AutoMailForIRNError.run();

        }

        

    }

    public ClassDescription caption()

    {

        return "@MY:IRNErrorAutoMail";

    }

    public container pack()

    {

        return [#CurrentVersion];

    }

    public boolean canGoBatch()

    {

        return true;

    }

    public boolean unpack(container packedClass)

    {

        return true;

    }

    public boolean runsImpersonated()

    {

        return true;

    }

    public void run()

    {

        this.sendMailIRNError();

    }

public str mailBody()

    {

        System.Text.StringBuilder htmlTable;

        PurchLine purchLine;

        htmlTable = new System.Text.StringBuilder();

        htmlTable.Append("<html>");

        htmlTable.Append("<head>");

        htmlTable.Append("<style>");

        htmlTable.Append("table, th, td { border: 1px solid black; border-collapse: collapse; }");

        htmlTable.Append("</style>");

        htmlTable.Append("</head>");

        htmlTable.Append("<body>");

        htmlTable.Append("<p>");

        htmlTable.Append("Dear Team,");

        htmlTable.Append("</p>");

        htmlTable.Append(strFmt("Please note the Error log for IRN not generated cases for date %1",date2Str(today(),1 25, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4)));

        htmlTable.Append("</br>");

        htmlTable.Append("</br>");

        htmlTable.Append(strFmt("Regards "));

        htmlTable.Append("</br>");

        htmlTable.Append("D365 Notifications");

        htmlTable.Append("</br>");

        htmlTable.Append("</br>");

        htmlTable.Append("</body>");

        htmlTable.Append("</html>");

        return htmlTable.ToString();

    }

public void sendMailIRNError()

    {

        SysMailerMessageBuilder         mailer          = new SysMailerMessageBuilder();;

        SysMailerExchange               smtp            = new SysMailerExchange();

        ProjInvoiceJour                 projInvoiceJour;

         MyEInvoiceTable               eInvoiceTable, eInvoiceTableUpdate;

        CustInvoiceJour                 custInvoiceJour;

        DirPartyTable                   dirPartyTable;

        CustTable                       custTable;

         MyEinvoiceErrorAutoMailToCC   einvoiceErrorAutoMailToCC; // store email ids 

        RowNumber                       row             = 1 ;

         MyEmailIds                    to, cc;

        str                             fileName        = strFmt("IRN Error log for %1",today());

        boolean                         sendMail        = false;

        System.IO.MemoryStream          memoryStream    = new System.IO.MemoryStream();

        //< My-0607>< MyAutoMailForIRNErrorReport><26-10-20 25><Start>

        FiscalCalendarRecId fiscalCalendar = Ledger::fiscalCalendar(CompanyInfo::current());

        date        startDate,endDate;

        //< My-0607>< MyAutoMailForIRNErrorReport><26-10-20 25><End>

        while select einvoiceErrorAutoMailToCC

        {

            if(einvoiceErrorAutoMailToCC.EmailType ==  MyEmailType::To)

            {

                if(to == "")

                {

                    to = einvoiceErrorAutoMailToCC.EmailId;

                }

                else

                {

                    to += ";" + einvoiceErrorAutoMailToCC.EmailId;

                }

            }

            else

            {

                if(cc == "")

                {

                    cc = einvoiceErrorAutoMailToCC.EmailId;

                }

                else

                {

                    cc += ";" + einvoiceErrorAutoMailToCC.EmailId;

                }

            }

        }

        mailer.setSubject(strFmt(strUpr(curExt()) + ": IRN Error Log for %1.",date2Str(today(),1 25, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4)));

        mailer.setFrom(ProjParameters::find().IRNErrorLogMail_ My);

        mailer.setBody(this.mailBody());

        mailer.addTo(to);

        mailer.addCc(cc);

        using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))

        {

            var worksheets = package.get_Workbook().get_Worksheets();

            var worksheet = worksheets.Add("@SYS16445");

            var cells = worksheet.get_Cells();

            var cell = cells.get_Item(1,1);

            cell.set_Value("Proposal Id");

            cell = cells.get_Item(1,2);

            cell.set_Value("Invoice Id");

            cell = cells.get_Item(1,3);

            cell.set_Value("Supply Type Code");

            cell = cells.get_Item(1,4);

            cell.set_Value("Date");

            cell = cells.get_Item(1,5);

            cell.set_Value("Invoice Account");

            cell = cells.get_Item(1,6);

            cell.set_Value("Name");

            cell = cells.get_Item(1,7);

            cell.set_Value("Sales Currency");

            cell = cells.get_Item(1,8);

            cell.set_Value("Invoice Amount");

            cell = cells.get_Item(1,9);

            cell.set_Value("Due Date");

            cell = cells.get_Item(1,10);

            cell.set_Value("Error Log");

            //< My-0607>< MyAutoMailForIRNErrorReport><26-10-20 25><Start>

            endDate = FiscalCalendarYear::findYearByCalendarDate(fiscalCalendar,today()).EndDate;

            startDate = FiscalCalendarYear::findYearByCalendarDate(fiscalCalendar,today()).StartDate;

            

            while select ProposalId,InvoiceAmount,InvoiceDate,CurrencyId,InvoiceAccount,ProjInvoiceId,DueDate from projInvoiceJour

                join SupplyTypeCode,ErrorLog,RecId from eInvoiceTable

                    where eInvoiceTable.projInvoiceJour == projInvoiceJour.RecId

                && eInvoiceTable.IsError ==   MyEInvoiceNoYes::Y

                && eInvoiceTable.DocumentDate <= endDate

                && eInvoiceTable.DocumentDate >= startDate

              //< My-0607>< MyAutoMailForIRNErrorReport><26-10-20 25><End>

            {

                select Name from dirPartyTable

                    exists join custTable

                        where custTable.Party == dirPartyTable.RecId

                        && custTable.AccountNum == projInvoiceJour.InvoiceAccount;

                row++;

                cell = cells.get_item(row, 1);

                cell.set_Value(projInvoiceJour.ProposalId);

                cell = cells.get_item(row, 2);

                cell.set_Value(projInvoiceJour.ProjInvoiceId);

                cell = cells.get_item(row, 3);

                cell.set_Value(enum2Str(eInvoiceTable.SupplyTypeCode));

                cell = cells.get_item(row, 4);

                cell.set_Value(date2Str(projInvoiceJour.InvoiceDate,1 25,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4));

                cell = cells.get_item(row, 5);

                cell.set_Value(projInvoiceJour.InvoiceAccount);

                cell = cells.get_item(row, 6);

                cell.set_Value(dirPartyTable.Name);

                cell = cells.get_item(row, 7);

                cell.set_Value(projInvoiceJour.CurrencyId);

                cell = cells.get_item(row, 8);

                cell.set_Value(int2Str(projInvoiceJour.InvoiceAmount));

                cell = cells.get_item(row, 9);

                cell.set_Value(date2Str(projInvoiceJour.DueDate,1 25,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4));

                cell = cells.get_item(row, 10);

                cell.set_Value(eInvoiceTable.ErrorLog);

                sendMail =  true;

            }

            while select InvoiceAmount,InvoiceDate,CurrencyCode,InvoiceAccount,InvoiceId,DueDate from custInvoiceJour

                join SupplyTypeCode,ErrorLog,RecId from eInvoiceTable

                    where eInvoiceTable.CustInvoiceJour == custInvoiceJour.RecId

                && eInvoiceTable.IsError ==   MyEInvoiceNoYes::Y

                && eInvoiceTable.DocumentDate <= endDate

                && eInvoiceTable.DocumentDate >= startDate

              //< My-0607>< MyAutoMailForIRNErrorReport><26-10-20 25><End>

            {

                select Name from dirPartyTable

                    exists join custTable

                        where custTable.Party == dirPartyTable.RecId

                        && custTable.AccountNum == custInvoiceJour.InvoiceAccount;

                row++;

                //cell = cells.get_item(row, 1);

                //cell.set_Value(projInvoiceJour.ProposalId);

                cell = cells.get_item(row, 2);

                cell.set_Value(custInvoiceJour.InvoiceId);

                cell = cells.get_item(row, 3);

                cell.set_Value(enum2Str(eInvoiceTable.SupplyTypeCode));

                cell = cells.get_item(row, 4);

                cell.set_Value(date2Str(custInvoiceJour.InvoiceDate,1 25,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4));

                cell = cells.get_item(row, 5);

                cell.set_Value(custInvoiceJour.InvoiceAccount);

                cell = cells.get_item(row, 6);

                cell.set_Value(dirPartyTable.Name);

                cell = cells.get_item(row, 7);

                cell.set_Value(custInvoiceJour.CurrencyCode);

                cell = cells.get_item(row, 8);

                cell.set_Value(int2Str(custInvoiceJour.InvoiceAmount));

                cell = cells.get_item(row, 9);

                cell.set_Value(date2Str(custInvoiceJour.DueDate,1 25,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2,DateSeparator::Hyphen,DateYear::Digits4));

                cell = cells.get_item(row, 10);

                cell.set_Value(eInvoiceTable.ErrorLog);

                sendMail =  true;

            }

            package.Save();

        }

        if(sendMail)

        {

            memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

            //The below line used to attach excel file to email.

            mailer.addAttachment(memoryStream, fileName +'.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

            try

            {

                smtp.sendNonInteractive(mailer.getMessage());

            }

            catch(Exception::CLRError)

            {

                error(CLRInterop::getLastException().toString());

            }

        }

    }