Tags: Collections, Customer account statement, D365FO, Emailing, Free Edition, Invoice 
In Dynamics 365 for Finance and Operations (D365FO), the standard Customer account statement report summarizes financial transactions for a customer over a specified period, providing a clear overview of the outstanding balance, payments, credits, and recent transactions to ensure transparency and facilitate timely payments.
Usually, Customer account statements are emailed to customers, but the standard emailing in D365FO isn't very flexible. Outgoing emails have a fixed subject and body, lacking details about unsettled invoices and due dates.
This article will show you how to customize and improve this process with Docentric. You'll learn how to send professional-looking emails with attached Customer account statements and corresponding open invoices, thereby improving your customer communication & satisfaction.
Try Docentric AX Free edition to use your data, logos, and attachments in your outgoing emails (e.g., email subject or body) and improve your customer experience.
Standard D365FO versus Docentric emailing
When emailing a Customer account statement report with the standard D365FO functionality, you run into a few issues:
- Fixed email subject and body: You have to manually change the email subject and body every time you send the report.
- Limited email body editor: The small editor introduced in version D365FO 10.0.39 makes it hard to see the full content and doesn’t allow adding dynamic report data.
- Fixed report attachment: You can only use fixed text for the report attachment.
- Complex print management: To use multiple templates for email print destinations, you need a print management setup for each customer account, leading to many print management overrides with the same issues.
Docentric solves these problems by letting you customize email subjects, bodies, and attachments dynamically using all the report data. This improves email communication and helps avoid mistakes.
As you can see, emails generated with Docentric are richer and more informative.
Can we send other documents in the same email
Unfortunately, the standard Email print destination in D365FO doesn't allow you to send other documents in the same email. But if you switch to the Docentric Email print destination, you can set up rules to add extra attachments.
Learn more about attachment rules >>
What if you want to include attachments that can’t be collected using the additional attachment rules, like open customer invoices in PDF format along with the Customer account statement report? No worries! With a few minor adjustments, you can do this too.
How can Docentric help here
Besides setting up Additional attachment rules in the Docentric Email print destination, you can also include attachments in the outgoing email through code.
If you want to email a customer account statement with open invoices attached, the first step is to figure out how to get these invoices. Here are a couple of ways to get them:
- Generate ad-hoc attachments: You could generate open invoice attachments on the fly when the report is emailed. However, this would require running the invoice report in X++, which can significantly slow down the process. So, we’ll skip this method.
- Pre-saved invoices: A better option is to have open invoices already generated and saved somewhere in D365FO, like the invoice journal record attachements. This way, they are easy to find and attach to the email.
Learn how to print reports to Attachments in D365FO using Docentric >>
You can also save open invoices in the print archive when you email or print them.
Learn more about Print archive improvements by Docentric >>
Once the invoices are stored in the invoice journal or print archive, you can easily include them in the outgoing email when printing the customer account statement report. With minimal code changes, you can collect and attach these open invoices.
If you choose to attach invoices from the invoice journal, be aware that the most recent attachment is selected. This means it might not be the open invoice document itself, but any PDF file attached to the invoice journal record.
To make this work with Docentric, we’ve introduced a new report dialog for the customer account statement with extra parameters.
When you email a customer account statement with the open invoices attached, the generated email will look like this:
It’s super simple! Just configure the additional parameters, and all open invoices will be attached to the outgoing email. 😉
For simplicity in this POC, we created two custom placeholders: Total Amount Due and Currency. However, if your Customer Account Statement includes amounts in multiple currencies, you'll need to update the example code in the DSP class to handle this scenario.
How does the solution work
Let’s break down the customizations needed to make sure you can easily collect and attach all open invoices to the outgoing email with the customer's statement.
New parameters in the report dialog
First, we need to configure where the open invoices come from and how they should be added to the email. This involves adding new parameters to the report dialog by extending the standard report contract class CustAccountStatementExtContract.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
/// <summary> /// Extension class CustAccountStatementExtContract. /// NOTE: This is possible from January 2019 in so-called “4th extension wave” Platform update 23. /// SEE: https://erconsult.eu/blog/extending-sysoperation-contracts-with-datamemberattribute/ /// SEE: https://community.dynamics.com/365/financeandoperations/b/d365fo-blog-of-er-consult/posts/extending-sysoperation-contracts-with-datamemberattribute /// </summary> [ExtensionOf(classStr(CustAccountStatementExtContract))] final class CustAccountStatementExtContract_DCAS_Extension { #define.DocAttachOpenInvoicesGroup('DocentricAttachOpenInvoicesGroup') private NoYes attachOpenInvoices = NoYes::No; private DocCustAccountStatementAttachOpenInvoicesAs attachOpenInvoicesAs = DocCustAccountStatementAttachOpenInvoicesAs::ZipFile; private DocCustAccountStatementOpenInvoiceSource openInvoiceSource = DocCustAccountStatementOpenInvoiceSource::Both; private DocCustAccountStatementEmailOpenInvoiceMissingAction openInvoiceMissingAction = DocCustAccountStatementEmailOpenInvoiceMissingAction::EmailWithWarning; /// <summary> /// Gets/Sets the flag that specifies whether the related open invoices /// should be attached to an email or downloaded as a ZIP file in the browser /// when the customer account statement report is run. /// </summary> /// <param name = "_attachOpenInvoices">Should the related open invoices be attached or downloaded</param> /// <returns>True if the related open invoices should be attached or downloaded; otherwise false</returns> [ DataMember('AttachOpenInvoices'), SysOperationLabel("@DocentricAX_WE_DCAS:CustAccountStatementAttachOpenInvoices"), SysOperationHelpText("@DocentricAX_WE_DCAS:CustAccountStatementAttachOpenInvoicesHelpText"), SysOperationGroupMember(#DocAttachOpenInvoicesGroup) ] public NoYes parmAttachOpenInvoices(NoYes _attachOpenInvoices = attachOpenInvoices) { attachOpenInvoices = _attachOpenInvoices; return attachOpenInvoices; } /// <summary> /// Gets/Sets the flag that specifies how the related open invoices should be attached to an email. /// </summary> /// <param name = "_attachOpenInvoicesAs">Defines how the related open invoices should be attached to an email</param> /// <returns>SeparateFiles, ZipFile or MergedPDF</returns> [ DataMember('AttachOpenInvoicesAs'), SysOperationLabel("@DocentricAX_WE_DCAS:CustAccountStatementAttachOpenInvoicesAs"), SysOperationHelpText("@DocentricAX_WE_DCAS:CustAccountStatementAttachOpenInvoicesAsHelpText"), SysOperationGroupMember(#DocAttachOpenInvoicesGroup) ] public DocCustAccountStatementAttachOpenInvoicesAs parmAttachOpenInvoicesAs(DocCustAccountStatementAttachOpenInvoicesAs _attachOpenInvoicesAs = attachOpenInvoicesAs) { attachOpenInvoicesAs = _attachOpenInvoicesAs; return attachOpenInvoicesAs; } /// <summary> /// Gets/Sets the source location of the related open invoices. /// </summary> /// <param name = "_openInvoiceSource">The source location of the related open invoices</param> /// <returns>The source location of the related open invoices</returns> [ DataMember('OpenInvoiceSource'), SysOperationLabel("@DocentricAX_WE_DCAS:CustAccountStatementOpenInvoiceSource"), SysOperationHelpText("@DocentricAX_WE_DCAS:CustAccountStatementOpenInvoiceSourceHelpText"), SysOperationGroupMember(#DocAttachOpenInvoicesGroup) ] public DocCustAccountStatementOpenInvoiceSource parmOpenInvoiceSource(DocCustAccountStatementOpenInvoiceSource _openInvoiceSource = openInvoiceSource) { openInvoiceSource = _openInvoiceSource; return openInvoiceSource; } /// <summary> /// Gets/Sets the flag that specifies what type of action should be taken if the related /// open invoice is missing when the customer account statement is emailed. /// </summary> /// <param name = "_openInvoiceMissingAction">Type of action</param> /// <returns>Type of action</returns> [ DataMember('EmailOpenInvoiceMissingAction'), SysOperationLabel("@DocentricAX_WE_DCAS:CustAccountStatementEmailOpenInvoiceMissingAction"), SysOperationHelpText("@DocentricAX_WE_DCAS:CustAccountStatementEmailOpenInvoiceMissingActionHelpText"), SysOperationGroupMember(#DocAttachOpenInvoicesGroup) ] public DocCustAccountStatementEmailOpenInvoiceMissingAction parmOpenInvoiceMissingAction(DocCustAccountStatementEmailOpenInvoiceMissingAction _openInvoiceMissingAction = openInvoiceMissingAction) { openInvoiceMissingAction = _openInvoiceMissingAction; return openInvoiceMissingAction; } } |
Next, extend the CustAccountStatementExtUIBuilder class to organize these new parameters into a suitable dialog group.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
/// <summary> /// Extension of <c>CustAccountStatementExtUIBuilder</c> to build a dialog with additional dialog groups and fields /// </summary> [ExtensionOf(classStr(CustAccountStatementExtUIBuilder))] final class CustAcountStatementExtUIBuilder_DCAS_Extension { private DialogField attachOpenInvoicesField; private DialogField attachOpenInvoicesAsField; private DialogField openInvoiceSourceField; private DialogField openInvoiceMissingActionField; /// <summary> /// Dialog build handler. Extending custom dialog /// </summary> public void build() { // Initializing data contract object CustAccountStatementExtContract custAccountStatementExtContract = this.dataContractObject() as CustAccountStatementExtContract; next build(); // After the from build we need to add extended form options if (this.controller() is DocCustAccountStatementExtController) { // Get the main dialog group Dialog mainDialog = this.dialog(); // Adding dialog fields to override the behaviour of SysOperation // Adding PRINT OPTIONS group DialogGroup docPrintGroup = mainDialog.addGroup("@DocentricAX_WE_DCAS:CustAccountStatementPrintGroup"); this.addDialogField(methodStr(CustAccountStatementExtContract, parmAttachOpenInvoices), custAccountStatementExtContract); // Adding SOURCE LOCATION INFORMATION group this.addDialogField(methodStr(CustAccountStatementExtContract, parmOpenInvoiceSource), custAccountStatementExtContract); //// Adding EMAIL OPTIONS group mainDialog.addGroup("@DocentricAX_WE_DCAS:CustAccountStatementPrintGroupEmail", docPrintGroup); this.addDialogField(methodStr(CustAccountStatementExtContract, parmAttachOpenInvoicesAs), custAccountStatementExtContract); this.addDialogField(methodStr(CustAccountStatementExtContract, parmOpenInvoiceMissingAction), custAccountStatementExtContract); } } /// <summary> /// Enable dialog Print option fields depending on the options /// </summary> private void enableFields() { boolean enable = attachOpenInvoicesField.value(); openInvoiceSourceField.enabled(enable); attachOpenInvoicesAsField.enabled(enable); openInvoiceMissingActionField.enabled(enable); } /// <summary> /// Extending post /// </summary> public void postBuild() { // Initializing data contract object CustAccountStatementExtContract custAccountStatementExtContract = this.dataContractObject() as CustAccountStatementExtContract; // Get the parameter controls from dialog attachOpenInvoicesField = this.bindInfo().getDialogField(custAccountStatementExtContract, methodStr(CustAccountStatementExtContract, parmAttachOpenInvoices)); attachOpenInvoicesAsField = this.bindInfo().getDialogField(custAccountStatementExtContract, methodStr(CustAccountStatementExtContract, parmAttachOpenInvoicesAs)); openInvoiceSourceField = this.bindInfo().getDialogField(custAccountStatementExtContract, methodStr(CustAccountStatementExtContract, parmOpenInvoiceSource)); openInvoiceMissingActionField = this.bindInfo().getDialogField(custAccountStatementExtContract, methodStr(CustAccountStatementExtContract, parmOpenInvoiceMissingAction)); // We are changing the form only if this was called from the following controller if (this.controller() is DocCustAccountStatementExtController) { this.enableFields(); // Register events attachOpenInvoicesField.registerOverrideMethod(methodStr(FormCheckBoxControl, modified), methodStr(CustAccountStatementExtUIBuilder, attachOpenInvoicesOnModified), this); } next postBuild(); } /// <summary> /// Option Print Open Invoices on Modified event handler /// </summary> /// <param name = "_checkBoxControl">Source CheckBox control</param> /// <returns>True if event was handled.</returns> public boolean attachOpenInvoicesOnModified(FormCheckBoxControl _checkBoxControl) { this.enableFields(); return true; } } |
Once you've made these changes, the report dialog will look like this:
How to access the new report dialog
To keep the enhanced report separate from the standard one, we’ve added a new menu item under the Accounts Receivable module:
- Path 1: Customers > All customers > Collect > Customer balances > Statements with attached invoices
- Path 2: Inquiries and reports > Customers > Customer account statement with attached invoices
This makes it easy to find and use the new report dialog.
This new menu item is accessible through standard roles. So, if you can access the standard report, you can also run the enhanced one. We achieved this by extending the standard security duties.
How to collect the open invoices
The key requirement is that invoices are already saved in Print archive or Invoice journal attachments. Once saved, we create a report-specific DSP class to search for these stored invoices and add them as email attachments.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 |
/// <summary> /// Extended DocCustAccountStatementExtReportDSP /// NOTE: We only need to change overrideReportRunSettings as we are manipulating attachments (Open Invoices). /// </summary> public class DocCustAccountStatementExtReportWithAttachmentsDSP extends DocCustAccountStatementExtReportDSP { private Map openInvoicePdfs = new Map(Types::String, Types::Container); private CustAccountStatementExtTmp custAccountStatementExtTmp; private const int maximumAllowedOpenInvoices = 50; private int numOfOpenInvoicePdfs = 0; private boolean onlyOpen; /* Variables for custom placeholders */ real placeholder_totalAmountDue; str placeholder_curency; /// <summary> /// Description of the DSP class. /// </summary> /// <returns>Class description.</returns> public ClassDescription description() { return 'Customer account statement with open invoices attached DSP'; } /// <summary> /// Overridden method to get values for custom placeholders. /// </summary> /// <param name="_recordBuilder">An instance of the record builder</param> protected void generateXmlDataSource(DocXmlRecordBuilder _recordBuilder) { super(_recordBuilder); str sqlTempDbName; [sqlTempDbName, createdTransactionId] = this.getSqlTempDbNameAndCreatedTransactionId(); CustAccountStatementExtTmp casTmpTable; casTmpTable.useExistingTempDBTable(sqlTempDbName); // Select the first customer - in the case of a multi-company customer. // Otherwise, this will be the only customer. select firstonly AccountNum from casTmpTable where casTmpTable.CreatedTransactionId == createdTransactionId; str firstToShowCustomer = casTmpTable.AccountNum; // We use the first customer currency as the value for the placeholder - in the case of a multi-company customer. select firstonly casTmpTable where casTmpTable.AccountNum == firstToShowCustomer && casTmpTable.FlagMainData == 16 && casTmpTable.CreatedTransactionId == createdTransactionId; placeholder_curency = casTmpTable.CustTable_Currency; // As this is a POC solution, we add up all closing balance amounts without paying attention to the currency. // Multi-company customers may have different currencies, so it may be necessary to split this amount. select sum(ClosingBalance) from casTmpTable where casTmpTable.AccountNum == firstToShowCustomer && casTmpTable.FlagMainData == 18 && casTmpTable.Flag != 4 && casTmpTable.Flag != 7 && casTmpTable.CreatedTransactionId == createdTransactionId; placeholder_totalAmountDue = casTmpTable.ClosingBalance; } // List of defined custom placeholders for the report: #define.TotalAmountDue('TotalAmountDue') #define.Currency('Currency') [DocPlaceholderAttribute(#TotalAmountDue, 'CASWOI - Total Amount Due'), DocPlaceholderAttribute(#Currency, 'CASWOI - Currency')] /// <summary> /// Overridden method to collect and attach related invoices from the Print archive or invoice journal attachments. /// </summary> /// <param name = "_reportRunContext">Report run context.</param> /// <param name = "_replaceStandardPlaceholders">Should be standard placeholders replaced.</param> /// <returns>DocPlaceholderManager instance to enable replacing of custom placeholders if the method is overridden.</returns> public DocPlaceholderManager overrideReportRunSettings(DocReportRunContext _reportRunContext, boolean _replaceStandardPlaceholders = true) { DocPlaceholderManager placeholderMng = super(_reportRunContext, _replaceStandardPlaceholders); // -- Placeholder @TotalAmountDue@ placeholderMng.replacePlaceholderInCurrentPrintDest(#TotalAmountDue, placeholder_totalAmountDue); // -- Placeholder @Currency@ placeholderMng.replacePlaceholderInCurrentPrintDest(#Currency, placeholder_curency); this.collectAndAttachInvoices(_reportRunContext); return placeholderMng; } /// <summary> /// The method collects open invoices in the customer account statement report as PDF and processes the attach process. /// </summary> /// <param name = "_reportRunContext">Report run context.</param> private void collectAndAttachInvoices(DocReportRunContext _reportRunContext) { // Get the report table context custAccountStatementExtTmp = this.getReportDataTableContext(tableStr(CustAccountStatementExtTmp)); boolean attachOpenInvoices = this.reportContract.parmAttachOpenInvoices(); onlyOpen = this.reportContract.parmOnlyOpen(); // We need to get all open invoice documents but in case if supported print destination is selected if (attachOpenInvoices) { // Count all open invoices in the customer account statement int64 numberOfAllOpenInvoices = 0; if (onlyOpen) { select count(RecId) from custAccountStatementExtTmp where custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19; numberOfAllOpenInvoices = custAccountStatementExtTmp.RecId; } else { while select AccountNum,CustTrans_Invoice, CustTrans_DataAreaId, CustTrans_Debit, CustTrans_Credit, ToDate from custAccountStatementExtTmp where custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19 { if (this.isInvoiceOpen(custAccountStatementExtTmp)) { numberOfAllOpenInvoices++; } } } // Collect open invoices this.searchForAttachments(this.reportContract.parmOpenInvoiceSource()); if (_reportRunContext.parmPrintDestination() != DocPrintDestination::Email) { if (_reportRunContext.isExecutingInBatch() == false) { if (numberOfAllOpenInvoices > numOfOpenInvoicePdfs) { // Only showing warning DocGlobalHelper::handleWarning(strFmt("@DocentricAX_WE_DCAS:CustAccountStatementMissingSourcePDFFilesOnDownloadWarning", numOfOpenInvoicePdfs, numberOfAllOpenInvoices)); // Download the log file with missing open invoice files this.downloadMissingOpenInvoiceReportAsTxt(); } // Download collected open invoices in a ZIP file this.downloadAsZIP(); } else { DocGlobalHelper::handleError("@DocentricAX_WE_DCAS:CustAccountStatementDownloadInBatchModeNotAllowedError"); } } else // if (_reportRunContext.parmPrintDestination() == DocPrintDestination::Email) { // If we have less attachments then invoices // We need to handle this if (numberOfAllOpenInvoices > numOfOpenInvoicePdfs) { if (this.reportContract.parmOpenInvoiceMissingAction() == DocCustAccountStatementEmailOpenInvoiceMissingAction::EmailWithWarning) { // Only showing warning DocGlobalHelper::handleWarning(strFmt("@DocentricAX_WE_DCAS:CustAccountStatementMissingSourcePDFFilesOnEmailWarning", numOfOpenInvoicePdfs, numberOfAllOpenInvoices)); } else { // We need to throw exception in order to terminate email sending DocGlobalHelper::handleException(funcName(), "@DocentricAX_WE_DCAS:CustAccountStatementNotSentMissingSourcePDFFilesError"); } } switch (this.reportContract.parmAttachOpenInvoicesAs()) { case DocCustAccountStatementAttachOpenInvoicesAs::MergedPDF: // Sending attachments as Merged PDF this.attachToEmailAsMergedPDF(_reportRunContext); break; case DocCustAccountStatementAttachOpenInvoicesAs::SeparateFiles: // Sending attachments as separate files this.attachToEmailAsSeparateFiles(_reportRunContext); break; case DocCustAccountStatementAttachOpenInvoicesAs::ZipFile: // Sending attachments as ZIP this.attachToEmailAsZIP(_reportRunContext); break; } } } } /// <summary> /// Method used to search for attachments in the specified source. /// </summary> /// <param name = "_openInvoiceSource">Open invoice source.</param> private void searchForAttachments(DocCustAccountStatementOpenInvoiceSource _openInvoiceSource) { try { if (_openInvoiceSource == DocCustAccountStatementOpenInvoiceSource::PrintArchive || _openInvoiceSource == DocCustAccountStatementOpenInvoiceSource::Both) { // Here we will find FreeText and Customer Invoices this.searchForAttachmentsInPrintArchiveOfCustomerInvoiceJournal(); // Here we will find Project invoices this.searchForAttachmentsInPrintArchiveOfProjectInvoiceJournal(); } if (_openInvoiceSource == DocCustAccountStatementOpenInvoiceSource::InvoiceJournal || _openInvoiceSource == DocCustAccountStatementOpenInvoiceSource::Both) { // Here we will find FreeText and Customer Invoices this.searchForAttachmentsInCustomerInvoiceJournal(); // Here we will find Project invoices this.searchForAttachmentsInProjectInvoiceJournal(); } } catch (Exception::CLRError) { DocGlobalHelper::handleClrException(funcName(), "@DocentricAX_WE_DCAS:CustAccountStatementFetchingOpenInvoicePDFFilesError"); } catch { DocGlobalHelper::handleException(funcName(), "@DocentricAX_WE_DCAS:CustAccountStatementFetchingOpenInvoicePDFFilesError"); } } /// <summary> /// Downloads collected invoices as a ZIP file in the browser. /// This method is called when the report is not printed to Email print destination. /// </summary> private void downloadAsZIP() { InvoiceId invoiceId; DataAreaId dataAreaId; str documentName; DocuRef docuRef; if (numOfOpenInvoicePdfs > 0) { List invoicePdfStreams = new List(Types::Container); MapEnumerator me = openInvoicePdfs.getEnumerator(); while (me.Movenext()) { [invoiceId, dataAreaId, documentName, docuRef] = me.currentValue(); var documentStream = DocumentManagement::getAttachmentStream(docuRef); invoicePdfStreams.addEnd([documentName, documentStream]); } using (System.IO.Stream zipStream = DocDocumentHelper::documents2Zip(invoicePdfStreams, /*dispose input streams*/ true)) { Filename destinationFilename = this.getDestinationFilename(' - Open invoices.zip'); DocFileMngHelper::sendFileToUser(zipStream, destinationFilename, '', '', '', classStr(DocFileUploadTemporaryStorageStrategy), true, true, false); } } } /// <summary> /// Merges collected invoices into single PDF and adds it as additional email attachment. /// </summary> /// <param name = "_reportRunContext">Report run context.</param> private void attachToEmailAsMergedPDF(DocReportRunContext _reportRunContext) { InvoiceId invoiceId; DataAreaId dataAreaId; str documentName; DocuRef docuRef; if (numOfOpenInvoicePdfs > 0) { List invoicePdfStreams = new List(Types::AnyType); MapEnumerator me = openInvoicePdfs.getEnumerator(); while (me.Movenext()) { [invoiceId, dataAreaId, documentName, docuRef] = me.currentValue(); var documentStream = Binary::constructFromContainer(DocumentManagement::getAttachmentAsContainer(docuRef)).getMemoryStream() as System.IO.MemoryStream; invoicePdfStreams.addEnd(documentStream.ToArray()); } // Create the merged PDF file from the document contents saved in the list using (System.IO.MemoryStream mergedPDFStream = DocDocumentHelper::mergePdfDocuments(invoicePdfStreams)) { Filename destinationFilename = this.getDestinationFilename(' - Open invoices.pdf'); _reportRunContext.emailPrintDestSettings().addAdditionalAttachment( destinationFilename, DocGlobalHelper::convertMemoryStreamToContainer(mergedPDFStream)); } } } /// <summary> /// Adds collected invoices as separate additional email attachment files. /// </summary> /// <param name = "_reportRunContext">Report run context.</param> private void attachToEmailAsSeparateFiles(DocReportRunContext _reportRunContext) { InvoiceId invoiceId; DataAreaId dataAreaId; str documentName; DocuRef docuRef; if (numOfOpenInvoicePdfs > 0) { MapEnumerator me = openInvoicePdfs.getEnumerator(); while (me.Movenext()) { [invoiceId, dataAreaId, documentName, docuRef] = me.currentValue(); var documentContainer = DocumentManagement::getAttachmentAsContainer(docuRef); _reportRunContext.emailPrintDestSettings().addAdditionalAttachment('Invoice ' + documentName, documentContainer); } } } /// <summary> /// ZIPs collected invoices and adds the ZIP file as additional email attachment. /// </summary> /// <param name = "_reportRunContext">Report run context.</param> private void attachToEmailAsZIP(DocReportRunContext _reportRunContext) { InvoiceId invoiceId; DataAreaId dataAreaId; str documentName; DocuRef docuRef; if (numOfOpenInvoicePdfs > 0) { List invoicePdfStreams = new List(Types::Container); MapEnumerator me = openInvoicePdfs.getEnumerator(); while (me.Movenext()) { [invoiceId, dataAreaId, documentName, docuRef] = me.currentValue(); var documentStream = DocumentManagement::getAttachmentStream(docuRef); invoicePdfStreams.addEnd([documentName, documentStream]); } using (System.IO.Stream zipStream = DocDocumentHelper::documents2Zip(invoicePdfStreams, true)) { Filename destinationFilename = this.getDestinationFilename(' - Open invoices.zip'); _reportRunContext.emailPrintDestSettings().addAdditionalAttachment( destinationFilename, DocGlobalHelper::convertMemoryStreamToContainer(zipStream)); } } } /// <summary> /// Creates filename for mergedPDF and ZIP file. /// </summary> /// <param name = "_fileExtension">File extension, PDF or ZIP.</param> /// <returns>Filename with the corresponding extension.</returns> private Filename getDestinationFilename(str _fileExtension) { return strFmt('%1%2', 'Customer Account Statement', _fileExtension); } /// <summary> /// Downloads in the browser a .txt file with the list of missing open invoices. /// </summary> private void downloadMissingOpenInvoiceReportAsTxt() { System.Text.StringBuilder missingInvoiceNumbers = new System.Text.StringBuilder(); boolean isHeaderLine = true; while select firstonly1000 custAccountStatementExtTmp where custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19 { // We need to list only Open invoices in the missing invoices report if (!onlyOpen && !this.isInvoiceOpen(custAccountStatementExtTmp)) { continue; } if (!this.openInvoicePdfExists(custAccountStatementExtTmp.CustTrans_Invoice, custAccountStatementExtTmp.CustTrans_DataAreaId)) { if (isHeaderLine) { missingInvoiceNumbers.AppendLine('Customer account statement'); missingInvoiceNumbers.AppendLine(''); missingInvoiceNumbers.AppendLine('The following open invoices are missing PDF files:'); missingInvoiceNumbers.AppendLine(''); missingInvoiceNumbers.AppendLine('Invoice ID;Invoice Date;Company'); isHeaderLine = false; } missingInvoiceNumbers.AppendLine(strFmt('%1;%2;%3', custAccountStatementExtTmp.CustTrans_Invoice, custAccountStatementExtTmp.CustTrans_TransDate, custAccountStatementExtTmp.CustTrans_DataAreaId)); } } if (missingInvoiceNumbers.Length > 0) { using (System.IO.MemoryStream missingOpenInvoiceFile = new System.IO.MemoryStream(System.Text.Encoding::UTF8.GetBytes(missingInvoiceNumbers.ToString()))) { missingOpenInvoiceFile.Position = 0; Filename destinationFilename = this.getDestinationFilename(' - Missing open invoices.txt'); DocFileMngHelper::sendFileToUser(missingOpenInvoiceFile, destinationFilename, '', '', '', classStr(DocFileUploadTemporaryStorageStrategy), true, true, false); } } } /// <summary> /// Search for most recent attachments on invoices in Customer Invoice Journal. /// </summary> /// <remarks>Here we will find only FreeText and Customer Invoice.</remarks> private void searchForAttachmentsInCustomerInvoiceJournal() { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { return; } CustInvoiceJour custInvoiceJour; DocuRef docuRef; DocuValue docuValue; // Search for PDF documents in Customer Invoice Journal while select firstonly1000 InvoiceId, DataAreaId from custInvoiceJour order by custInvoiceJour.InvoiceId asc, docuRef.RecId desc join AccountNum,CustTrans_Invoice, CustTrans_DataAreaId, CustTrans_Debit, CustTrans_Credit, ToDate from custAccountStatementExtTmp where custAccountStatementExtTmp.CustTrans_Invoice == custInvoiceJour.InvoiceId && custAccountStatementExtTmp.CustTrans_DataAreaId == custInvoiceJour.DataAreaId && custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19 join docuRef where docuRef.RefTableId == tableNum(CustInvoiceJour) && docuRef.RefRecId == custInvoiceJour.RecId && docuRef.RefCompanyId == custInvoiceJour.DataAreaId join docuValue where docuValue.RecId == docuRef.ValueRecId && docuValue.FileType == 'pdf' { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { break; } // We need to collect only Open invoices and add them as email attachments if (!onlyOpen && !this.isInvoiceOpen(custAccountStatementExtTmp)) { continue; } this.addOpenInvoicePdf(custInvoiceJour.InvoiceId, custInvoiceJour.DataAreaId, docuRef); } } /// <summary> /// Search for most recent attachments on invoices in Project Invoice Journal. /// </summary> /// <remarks>Here we will find only Project Invoice.</remarks> private void searchForAttachmentsInProjectInvoiceJournal() { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { return; } ProjInvoiceJour projInvoiceJour; DocuRef docuRef; DocuValue docuValue; // Search for PDF documents in Project Invoice Journal while select firstonly1000 ProjInvoiceId, DataAreaId from projInvoiceJour order by projInvoiceJour.ProjInvoiceId asc, docuRef.RecId desc join AccountNum,CustTrans_Invoice, CustTrans_DataAreaId, CustTrans_Debit, CustTrans_Credit, ToDate from custAccountStatementExtTmp where custAccountStatementExtTmp.CustTrans_Invoice == projInvoiceJour.ProjInvoiceId && custAccountStatementExtTmp.CustTrans_DataAreaId == projInvoiceJour.DataAreaId && custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19 join docuRef where docuRef.RefTableId == tableNum(ProjInvoiceJour) && docuRef.RefRecId == projInvoiceJour.RecId && docuRef.RefCompanyId == projInvoiceJour.DataAreaId join docuValue where docuValue.RecId == docuRef.ValueRecId && docuValue.FileType == 'pdf' { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { break; } // We need to collect only Open invoices and add them as email attachments if (!onlyOpen && !this.isInvoiceOpen(custAccountStatementExtTmp)) { continue; } this.addOpenInvoicePdf(projInvoiceJour.ProjInvoiceId, projInvoiceJour.DataAreaId, docuRef); } } /// <summary> /// Search for most recent attachments on invoices in Print Archive. /// </summary> /// <remarks> /// Print Archive only contains PDFs, if they were saved through print destination. /// Print Archive may contain multiple copies, so most recent is taken. /// Here we will find only Project Invoice. /// </remarks> private void searchForAttachmentsInPrintArchiveOfCustomerInvoiceJournal() { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { return; } CustInvoiceJour custInvoiceJour; // Print Archive Table PrintJobHeader printJobHeader; DocPrintJobHeader docPrintJobHeader; // Reference to attachment DocuRef docuRef; DocuValue docuValue; // Search for PDF documents in Print Archive of CustInvoiceJour while select firstonly1000 InvoiceId, DataAreaId from custInvoiceJour order by custInvoiceJour.InvoiceId asc join AccountNum,CustTrans_Invoice, CustTrans_DataAreaId, CustTrans_Debit, CustTrans_Credit, ToDate from custAccountStatementExtTmp where custAccountStatementExtTmp.CustTrans_Invoice == custInvoiceJour.InvoiceId && custAccountStatementExtTmp.CustTrans_DataAreaId == custInvoiceJour.DataAreaId && custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19 { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { break; } // We need to collect only Open invoices and add them as email attachments if (!onlyOpen && !this.isInvoiceOpen(custAccountStatementExtTmp)) { continue; } // Adding only in case that attachment stream was not already fetched if (!this.openInvoicePdfExists(custInvoiceJour.InvoiceId, custInvoiceJour.DataAreaId)) { // Now we have to find the latest Print Archive record select firstonly RecId from docPrintJobHeader order by docPrintJobHeader.RecId desc where docPrintJobHeader.JournalRecId == custInvoiceJour.RecId && docPrintJobHeader.JournalType == DocJournalType::CustInvoiceJour join printJobHeader where printJobHeader.RecId == docPrintJobHeader.PrintJobHeaderRecId join docuRef where ((docuRef.RefRecId == printJobHeader.RecId && docuRef.RefTableId == tableNum(PrintJobHeader) && docuRef.RefCompanyId == printJobHeader.DataAreaId) || (docuRef.RefRecId == docPrintJobHeader.RecId && docuRef.RefTableId == tableNum(DocPrintJobHeader) && docuRef.RefCompanyId == docPrintJobHeader.DataAreaId)) join docuValue where docuValue.RecId == docuRef.ValueRecId && docuValue.FileType == 'pdf'; // If we received back a record we add it if (docPrintJobHeader.RecId && docuRef.RecId) { this.addOpenInvoicePdf(custInvoiceJour.InvoiceId, custInvoiceJour.DataAreaId, docuRef); } } } } /// <summary> /// Search for most recent attachments on invoices in Print Archive. /// </summary> /// <remarks> /// Print Archive only contains PDFs, if they were saved through print destination. /// Print Archive may contain multiple copies, so most recent is taken. /// Here we will find only Project Invoice. /// </remarks> private void searchForAttachmentsInPrintArchiveOfProjectInvoiceJournal() { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { return; } ProjInvoiceJour projInvoiceJour; // Print Archive Table PrintJobHeader printJobHeader; DocPrintJobHeader docPrintJobHeader; // Reference to attachment DocuRef docuRef; DocuValue docuValue; // Search for PDF documents in Print Archive of ProjInvoiceJour while select firstonly1000 ProjInvoiceId, InvoiceDate from projInvoiceJour order by projInvoiceJour.ProjInvoiceId asc join AccountNum,CustTrans_Invoice, CustTrans_DataAreaId, CustTrans_Debit, CustTrans_Credit, ToDate from custAccountStatementExtTmp where custAccountStatementExtTmp.CustTrans_Invoice == projInvoiceJour.ProjInvoiceId && custAccountStatementExtTmp.CustTrans_DataAreaId == projInvoiceJour.DataAreaId && custAccountStatementExtTmp.CreatedTransactionId == this.createdTransactionId && custAccountStatementExtTmp.CustTrans_Invoice != '' && custAccountStatementExtTmp.FlagMainData == 19 { // If we already have maximum allowed attachment streams we need to stop // We need at least one item more than max to be able to produce a Warning to user if (numOfOpenInvoicePdfs > maximumAllowedOpenInvoices) { break; } // We need to collect only Open invoices and add them as email attachments if (!onlyOpen && !this.isInvoiceOpen(custAccountStatementExtTmp)) { continue; } // Adding only in case that attachment stream was not already fetched if (!this.openInvoicePdfExists(projInvoiceJour.ProjInvoiceId, projInvoiceJour.DataAreaId)) { // Now we have to find the latest Print Archive record select firstonly RecId from docPrintJobHeader order by docPrintJobHeader.RecId desc where docPrintJobHeader.JournalRecId == projInvoiceJour.RecId && docPrintJobHeader.JournalType == DocJournalType::ProjInvoiceJour join printJobHeader where docPrintJobHeader.PrintJobHeaderRecId == printJobHeader.RecId join docuRef where ((docuRef.RefRecId == printJobHeader.RecId && docuRef.RefTableId == tableNum(PrintJobHeader) && docuRef.RefCompanyId == printJobHeader.DataAreaId) || (docuRef.RefRecId == docPrintJobHeader.RecId && docuRef.RefTableId == tableNum(DocPrintJobHeader) && docuRef.RefCompanyId == docPrintJobHeader.DataAreaId)) join docuValue where docuValue.RecId == docuRef.ValueRecId && docuValue.FileType == 'pdf'; // If we received back a record we add it if (docPrintJobHeader.RecId && docuRef.RecId) { this.addOpenInvoicePdf(projInvoiceJour.ProjInvoiceId, projInvoiceJour.DataAreaId, docuRef); } } } } /// <summary> /// Gets the flag indicating whether the specified invoice is still open in the provided date period. /// </summary> /// <param name = "_custAccountStatementExtTmp">The CustAccountStatementExtTmp table buffer</param> /// <returns>True if the invoice is still open; otherwise false</returns> private boolean isInvoiceOpen(CustAccountStatementExtTmp _custAccountStatementExtTmp) { CustTransSettleHistView custTransSettleHistView; select sum(SettleAmountCur) from custTransSettleHistView where custTransSettleHistView.AccountNum == _custAccountStatementExtTmp.AccountNum && custTransSettleHistView.Invoice == _custAccountStatementExtTmp.CustTrans_Invoice && custTransSettleHistView.DataAreaId == _custAccountStatementExtTmp.CustTrans_DataAreaId && custTransSettleHistView.SettlementDate <= _custAccountStatementExtTmp.ToDate; if (_custAccountStatementExtTmp.CustTrans_Debit + _custAccountStatementExtTmp.CustTrans_Credit == custTransSettleHistView.SettleAmountCur) { return false; } return true; } /// <summary> /// Adds found open invoices to the map. /// </summary> /// <param name = "_invoiceId">Invoice Id.</param> /// <param name = "_dataAreaId">Legal entity Id.</param> /// <param name = "_docuRef">DOcuRef table buffer.</param> private void addOpenInvoicePdf(InvoiceId _invoiceId, DataAreaId _dataAreaId, DocuRef _docuRef) { str documentName = _invoiceId + '_' + _dataAreaId + '.pdf'; if (!openInvoicePdfs.exists(documentName)) { openInvoicePdfs.insert(documentName, [_invoiceId, _dataAreaId, documentName, _docuRef]); numOfOpenInvoicePdfs++; } } /// <summary> /// Checks if the open invoice is already added to the map. /// </summary> /// <param name = "_invoiceId">Invoice Id.</param> /// <param name = "_dataAreaId">Legal entity Id.</param> /// <returns>True if the invoice is already in the map, otherwise false.</returns> private boolean openInvoicePdfExists(InvoiceId _invoiceId, DataAreaId _dataAreaId) { str documentName = _invoiceId + '_' + _dataAreaId + '.pdf'; return openInvoicePdfs.exists(documentName); } } |
Last steps before trying the solution
After making all the previous changes, we’re almost ready to run the enhanced Customer Account Statement report and retrieve all the open invoices as additional attachments. But before we do, we need to adjust the report settings in Docentric setup and select the correct DSP class.
You can also customize how the outgoing email looks. Use the Docentric Email print destination to define and use Report Email templates, which can even be resolved in another language.
Learn more about Docentric Report Email Templates >>
Finally, after making all these changes and settings, you’re ready to try the improved solution!
Final thoughts
This proof of concept (POC) will help you get more value from printing Customer account statement reports, especially when emailing them to customers. The enhancements we've discussed can also be used with the Docentric AX Free edition, making it even more worthwhile to give it a try. 😉
Resources
Download the solution project >>
Download the solution model >>
Download the sample email body source >>
- -
RELEASE NOTES
Version: 1.0.0 (Initial version)
Released: June 28, 2024
See Also
Send Collection Letters with Overdue Invoices - Functionality Overview >>
Send Collection Letters with Overdue Invoices - Technical Solution >>
Improved Collections Process Automation Emailing >>