Sales Pack - Invoice questions

[qpoint]

Sandbox code for sending out Pending invoices as they become due.
Provided as an example.

// Iterate overall all Pending Invoices in the SalesOrder, check if any are due to be sent.

// Get id of this SalesOrder
$salesOrderId= id;

// Get ids of all Pending Invoices associated with this SalesOrder. Related is the Relationship 'invoices'.
// OrderBy=dateInvoiced, ascending.
// Filter: Pending
$invoiceIds= record\findRelatedMany('SalesOrder', $salesOrderId, 'invoices', 16, 'dateInvoiced', 'asc', 'status=', 'Pending');
$invoiceCnt= array\length($invoiceIds);

$traceMsg= string\concatenate('Invoices Pending: ', $invoiceCnt);  
output\printLine($traceMsg);

$invoiceIndex= 0;
while ($invoiceIndex < $invoiceCnt)
{
  // Get next Invoice
  $invoiceId= array\at($invoiceIds, $invoiceIndex);
  $invoiceStatus= record\attribute('Invoice', $invoiceId, 'status');
  $invoiceName= record\attribute('Invoice', $invoiceId, 'name');
  $invoiceDueDate= record\attribute('Invoice', $invoiceId, 'dateInvoiced');

  // Trace
  $traceMsg= string\concatenate('Invoice: ', $invoiceName, ' Status: ', $invoiceStatus, ' Due: ', $invoiceDueDate);  
  output\printLine($traceMsg);

  // See if it's time to send
  ifThen(
    datetime\diff(datetime\today(), $invoiceDueDate, 'days') >= 0,
    // then
    // Generate Pdf.
    $attachmentId= ext\pdf\generate(
      'Invoice',              // entity type for template
      $invoiceId,
      '6604227b7b7454967',    // invoice template id
      'invoice.pdf'           // file name - name shown in attachments list
    );

    // Email the invoice
    $toStr= billingContact.emailAddress;
    $sender= 'accounting@yourdomain.com';
    $subject= 'none';
    $body= 'none';
    $emailId = record\create(
      'Email',
      'to', $toStr,
      'cc', $sender,
      'from', $sender,
      'subject', $subject,
      'body', $body,
      'isHtml', true,
      'status', 'Sending',
      'attachmentsIds', list($attachmentId),      
      'parentId', $invoiceId,
      'parentType', 'Invoice'
    );

    // Email Template - Invoice
    // (Note, when using a template, the following parameters described above do not need to be defined: 'subject', 'body', 'is Html')
    ext\email\applyTemplate($emailId, '6605db6b9360232ca');

    // Send the email
    ext\email\send($emailId);    


    // Update Invoice Status to Sent. This removes it from processing.
    record\update('Invoice', $invoiceId, 'status', 'Sent');

    $traceMsg= string\concatenate('Invoice Sent: ', $invoiceName, ' Status: ', $invoiceStatus, ' Due: ', $invoiceDueDate);
    output\printLine($traceMsg);

    // Option to limit to max of 1 per day.
    // break;
    
    $dummyStatement=''
    
  ); // end Invoice Due

  $invoiceIndex= $invoiceIndex + 1;
} ​ 

[qpoint]

Here is the sandbox formula script (sandbox is awesome, how did I ever live without this?!) for generating all the invoices associated with a SalesOrder.

The approach is to generate all invoices for each BillingGroup. Where a BillingGroup is the set of Products that billed together on invoice(s). For example, we may bill software and services separately. And non-recurring items are typically billed separately. The user specifies the BillingGroup codes (int) as part of SalesOrder creation.

All invoices are created up front, with their respective invoice due dates (dateInvoice) and prorated installment amounts.

A common use case is a prepaid annual software subscription, with quarterly services. In this model, it generates 5 separate invoices.

// Generates all the invoices associated with this SalesOrder.
// Algorithm:
// - Iterate over all product items, queried in order of BillingGroup (1..N).
// - For each product item in the billing group, generate a separate invoice for
//   each BillingPeriod. e.g. Annual contract with 3 mos BillingPeriod= 4 invoices.
//   Each invoice to have appropriate InvoiceDate and prorated product item cost amounts.
// - Product items sharing the same Billing Group go on the same invoice(s).
// - Repeat for each BillingGroup.

// Get id of this SalesOrder
$salesOrderId= id;
$subscriptionStartDate= subscriptionStartDateC;

// Get ids of all SalesOrderItems associated with this SalesOrder.
// OrderBy=BillingGroup, ascending.
$salesOrderItemIds= record\findRelatedMany('SalesOrder', $salesOrderId, 'items', 10, 'billingGroupC', 'asc');
$salesOrderItemCnt= array\length($salesOrderItemIds);

$traceMsg= string\concatenate('SalesOrder Item Cnt: ', $salesOrderItemCnt);  
output\printLine($traceMsg);

// Iterate over all SalesOrderItems
$invoiceList= list();               // list of invoices created, element is id.
$invoiceId= '';
$i= 0;
$billingGroupId= -1;
$billingGroupInvoiceIndexStart= 0;  // tracks the first invoice in the current BillingGroup.

while ($i < $salesOrderItemCnt)
{
  // Get next SalesOrderItem (product). Ordered by BillingGroup.
  $salesOrderItemId= array\at($salesOrderItemIds, $i);
  //output\printLine($salesOrderItemId);
  $billingPeriodMos= record\attribute('SalesOrderItem', $salesOrderItemId, 'billingPeriodMosC');
  $billingOffsetMos= record\attribute('SalesOrderItem', $salesOrderItemId, 'billingOffsetMosC');
  $billingGroup= record\attribute('SalesOrderItem', $salesOrderItemId, 'billingGroupC');
  $partNumber= record\attribute('SalesOrderItem', $salesOrderItemId, 'partNumberC');
  $productName= record\attribute('SalesOrderItem', $salesOrderItemId, 'name');
  $productUnitPrice= record\attribute('SalesOrderItem', $salesOrderItemId, 'unitPrice');
  $productQty= record\attribute('SalesOrderItem', $salesOrderItemId, 'quantity');
  $productAmount= record\attribute('SalesOrderItem', $salesOrderItemId, 'amount');
  
  
  // Is this the next billing group?
  ifThen(
    $billingGroup != $billingGroupId,
    output\printLine('New Billing Group');
    $billingGroupId= $billingGroup;
    $billingGroupInvoiceIndexStart= array\length($invoiceList)
  );
  
  // Determine # of invoices for this Product.
  $itemInvoiceCnt= subscriptionTermMosC / $billingPeriodMos;
  $billingFactor= 1.0 / $itemInvoiceCnt;
  
  $traceMsg= string\concatenate($partNumber, ' has ', $itemInvoiceCnt, ' invoices');  
  output\printLine($traceMsg);
  
  // Invoice Generation - note that invoices are generated in order, all invoices for each billing group,
  //   then all invoices for next billing group, etc.
  // Prep for generation of each Invoice in the BillingGroup.
  $j= $billingGroupInvoiceIndexStart;  // index into current invoice to create/update.
  $servicePeriodStartDate= $subscriptionStartDate;

  // Generate invoice date.
  $invoiceDate= datetime\addMonths($subscriptionStartDate, number\floor($billingOffsetMos));
  $billingOffsetDays= number\round(($billingOffsetMos - number\floor($billingOffsetMos)) * (365/12)); // fractional mos to days
  $invoiceDate= datetime\addDays($invoiceDate, $billingOffsetDays);

  while ($j < $billingGroupInvoiceIndexStart + $itemInvoiceCnt)
  { // Next BillingPeriod (invoice) for the SalesOrderItem in this BillingGroup.
  
    // Create the Invoice if it does not yet exist.
    ifThenElse(
      $j >= array\length($invoiceList),
      
      // Create the Invoice Record
      $invoiceName= string\concatenate(name, ' - Invoice', $j + 1);
      // Invoice Number of the form CustomerId-SalesOrderNumber-InvoiceNumber. e.g. 24137-05378-1
      $invoiceNumber= string\concatenate(account.idC, '-', string\substring(number, 3, 5), '-', $j + 1);  
      $invoiceDescription= string\concatenate('Service Period: ', $servicePeriodStartDate, ' - ', datetime\addMonths($servicePeriodStartDate, $billingPeriodMos));  

      $invoiceId= record\create('Invoice',
        'name', $invoiceName,
        'number', $invoiceNumber,
        'dateInvoiced', $invoiceDate,
        'purchaseOrderNumberC', purchaseOrderNumberC,
        'internalReferenceC', 'GI 50.0',
        'description', $invoiceDescription);
        
      // Relate Invoice to this SalesOrder
      record\relate('SalesOrder', $salesOrderId, 'invoices', $invoiceId);
      // Relate Invoice to the Account
      record\relate('Invoice', $invoiceId, 'account', accountId);
      // Relate Invoice.billingContact to the SalesOrder.billingContact
      record\relate('Invoice', $invoiceId, 'billingContact', billingContactId);
      
      $traceMsg= string\concatenate('Created invoice ', $invoiceNumber, ' Date: ', $invoiceDate, ' ServicePeriodStart: ', $servicePeriodStartDate);  
      output\printLine($traceMsg);
      
      $invoiceList= array\push($invoiceList, $invoiceId),   // add to the list of invoiceIds.

      // else - invoice already exists.
      $invoiceId= array\at($invoiceList, $j)
    );

    // Invoice exists at this point, indicated by $invoiceId.
    
    // Create InvoiceItem - add this Product to the Invoice.
    $invoiceItemAmount= $billingFactor * $productAmount;

    $invoiceItemId= record\create('InvoiceItem',  
      'amount', $invoiceItemAmount,    // ideally this would be auto-calculated.
      'name', $productName,
      'partNumberC', $partNumber,
      'quantity', $productQty,
      'unitPrice', $productUnitPrice,
      'billingFactorC', $billingFactor
      );    
    record\relate('Invoice', $invoiceId, 'items', $invoiceItemId);
    
    // Update invoice with latest grandTotalAmount, amount (hack).
    $invoiceTotalAmount= record\attribute('Invoice', $invoiceId, 'grandTotalAmount') + $invoiceItemAmount;
    record\update('Invoice', $invoiceId, 'grandTotalAmount', $invoiceTotalAmount);
    record\update('Invoice', $invoiceId, 'amount', $invoiceTotalAmount);
    
    $traceMsg= string\concatenate('Invoice: ', $j, ' Group: ', $billingGroup,' Add Product: ', $partNumber, ' ItemAmt: ', $invoiceItemAmount, ' InvoiceAmt: ', $invoiceTotalAmount);  
    output\printLine($traceMsg);

    // Prep for next BillingPeriod.
    $servicePeriodStartDate= datetime\addMonths($servicePeriodStartDate, $billingPeriodMos);
    $invoiceDate= datetime\addMonths($invoiceDate, $billingPeriodMos);    
    $j= $j + 1;
  }
  
  // All Invoices for this SalesOrderItem completed.
 
  $i= $i + 1;
}

// All SalesOrderItems processed.

$traceMsg= string\concatenate('Total Invoice Count: ', array\length($invoiceList));  
output\printLine($traceMsg); 

Next step will be to utilize this as part of an overall SalesOrder receipt bpm flowchart to:
- review and approve SalesOrder
- notify accounting via email and include the customer Purchase Order.
- generate and schedule all invoices
- email invoices as pdf using template, as they come due
- terminate flow once the last invoice is sent.

[qpoint]

Got the InvoiceItem.amount calculating correctly using a hook, to take into account the custom field billingFactorC, which represents the portion of the product item to bill in this invoice.

Here are steps for others that are new to this.

Note that we're using docker install on Ubuntu.

Create the directory and hook file. You may first have to create the Hooks directory if it's not present.

cd /dockerdata/espocrm/custom/Espo/Custom/Hooks
mkdir InvoiceItem
touch InvoiceItem/InvoiceItemCalculate.php
chown -R --changes www-data:www-data ./InvoiceItem
chmod -R -c g+w ./InvoiceItem​

Edit InvoiceItemCalculate.php

<?php
/*  03/24: Custom File added for hook. Entity: InvoiceItem */
namespace Espo\Custom\Hooks\InvoiceItem;
use Espo\ORM\Entity;

class InvoiceItemCalculate
{
  /* (optional, dflt 9) defines in which order hooks will be processed,
     Smaller values called sooner.      
     Refer to https://docs.espocrm.com/development/hooks/#hook-order */
  public static int $order = 10;

  public function __construct(
      // Define needed dependencies.
  ) {}

  public function beforeSave(Entity $entity, array $options): void
  {
    /* Calculate amount based on BillingFactor. */
    $amount= $entity->get('unitPrice') * $entity->get('quantity') * $entity->get('billingFactorC');
    $entity->set('amount', $amount);
  }
}
?>  ​

After saving the file, clear the espo cache: *Administration*, *Clear Cache*.

[qpoint]

Thanks, got a server side hook implemented on Invoice entity.
Note that setting grandTotalAmount takes care of the amount field. Not sure the purpose of the separate fields.
It still needs a manual edit to update the info.
Will need to figure out how to do this at the InvoiceItem level too for manual edits so the InvoiceItem.amount is correct.

As for getting InvoiceItem.amount, Invoice.grandTotalAmount, Invoice.amount to be correct when using a formula script, the only way I can see is to set the fields explicitly in the formula. Important since the goal is to generate the invoices fully automatically (no manual intervention).

Getting closer.

[victor]

You can try the following solution from the documentation: https://docs.espocrm.com/development...r-quote-totals.

[qpoint]

Have worked out a design approach for this with custom fields.
SalesOrder entity:
- SubscriptionTerm: length of the subscription in months. e.g. 12= annual subscription

SalesOrderItem:
- BillingPeriodMos: the billing period in months for a product item. e.g. 3= quarterly billing. 12= annual billing
- BillingGroup: identifies how product items are to be joined together in invoices. Those in the same BillingGroup are put in the same invoice
- BillingOffset: relative time in months when invoice is to be issued. e.g. 3=end of quarter, 0=start of quarter.

Have sandboxed a rather lengthy formula that walks through the SalesOrder items (ProductItems), and generates all the invoices with their scheduled future invoice dates. Will then create a workflow flowchart that sends invoices out as they become due.

It handles all of our invoicing use cases for:
- prepaid annual software subscription
- prepaid annual software subscription with quarterly services
- software and services paid quarterly
- one-year and multi-year contracts with various installment payment options, e.g. once a year for 3 years.
- payment at start, mid, or end of installment periods. e.g. mid-quarter invoicing
- non-recurring charges. e.g. customer training

Will post up the details once finalized.


Issue: the InvoiceItems amount fields (amount, grand total amount) are not getting calculated as Invoices are being created by formula.
A follow-on record\update() on the Invoice after initial create does not fix this. Only a manual edit and save will trigger the calculation.
Is there a way to make this happen via formula?

Question: as it stands now, the installment amount is being done by prorating the UnitPrice. Ideally I'd prefer a field for installment percentage, e.g. 25% for quarterly payments on annual contract. Is there a way to tie this into the Amount calculation in the InvoiceItems? i.e. Qty * UnitPrice * InstallmentPercent

[qpoint]

Product Price Books are supposed to be extended in the future with the ability to define rules applied to multiple products rather than one.

That will be very nice.

[yuri]

Product Price Books are supposed to be extended in the future with the ability to define rules applied to multiple products rather than one.

[qpoint]

Got our instance up to v7.4 latest. Docker 7.5 upgrade attempt failed with "Upgrade server is currently unavailable. Please try again later."...hopefully that is temporary.

Sales Pack installed successfully now. Looks nice. Created a basic Product and Price Book.

Am now looking into how to specify the installment invoicing method. Considering adding a custom field on the Product to specify the Billing Period (e.g. Annual, Quarterly, Monthly, etc) and some formula scheme in the Invoice Item to utilize this, but that may mean separate Product entries for each and every Billing Period choice. Not desirable since the Price table quantity discount tiers are lengthy and not practical for cloning and maintaining in multiple product entries.

So maybe Billing Period lives in the Sales Order.

[yuri]

PHP 8.0 has been already on its end of life for a 4 months. PHP 7.4 has been there for 1.5 year. These versions are not officially supported by the vendor, there are no security updates.

In Espo, we support only officially supported PHP versions. With extensions, we usually have a bit greater range of supported versions.

> which means Espo v7.1+.

The latest Sales Pack is compatible with EspoCRM v7.3. Inventory Management features require EspoCRM v8.0, but v8.1 is recommended. There's been a lot of small improvements in resent EspoCRM versions, that we needed to utilize.

[qpoint]

Thanks! I'm still working through the espo upgrades required for SalesPack.

For anyone looking at Sales Pack, v2.1.5 needs Php 8, which means Espo v7.1+.

[victor]

qpoint

These are general recommendations, I will try to show specific examples later.

I am attaching two working BPMNs that fit your request. They differ only in the Conditional Start Event (in one of them practice takes place every 2 minutes).

[qpoint]

Thanks for your detailed response, most helpful. I will study this. The ability to generate invoices in an automated way makes this look feasible enough to proceed with Sales Pack.

A customer has a subscription period defined (currently lives in Account, but maybe moves to Sales Order), and for the majority with quarterly invoicing, it's just a matter of calculating and scheduling invoices +3,+6,+9,+12 months, whether generated on a schedule or all generated in advance. Assuming api access to all the entities, for sure the fallback can be our existing Python integration server that is already set up for other activity (e.g. integration to our website, crm integrity checks, sales rep reports, etc), since it can handle more sophisticated programs.

[victor]

Hi qpoint,

Regarding the periodicity, this uses Trigger Type Scheduled if you are using Workflow. Or Timer Start Event: https://docs.espocrm.com/administrat...er-start-event if using BPMN. https://crontab.guru/ will be a good helper when setting up a scheduled event. In your case, it will most likely work 30 9 30 */2 *.

For the automated generation of an invoice from the Sales Order, create a workflow with Action: Execute Formula Script, using the formula from the post to transfer the items of the letter: https://forum.espocrm.com/forum/gene...267#post102267.
This post shows an example for moving from Quote to Invoice, but the principle is the same for Sales Order.
Also note that the workflow must contain fields for copying values into the Invoice itself, not just into the Invoice Items.

This formula can be applied in BPMN. It will suit you better, as it has the ability to create a task for the user to approve. More about it: https://docs.espocrm.com/administrat...ies/#user-task.

once approved, proceeds to email the invoice

Here you can use Run Service Action (screenshot 1).

These are general recommendations, I will try to show specific examples later.

[qpoint]

We're looking at Sales Pack to see if it can handle our invoicing process. I've gone through the documentation and forum posts and the remaining gaps in understanding are mostly related to invoice automation.

Background: our company offerings are subscription software and services, sold typically as annual subscriptions. Invoices for items are most often issued quarterly. So an order will have 4 invoices (at 25% each) issued during the period.

Question 1 - Is this invoicing model supported in Sales Pack?

https://docs.espocrm.com/user-guide/invoices/#invoice-items

I'm not clear on how one would do this, e.g. working from a Sales Order. Clarification would be most helpful.


Question 2 - invoice automation

The desire is to have invoices generated automatically in pdf, based on a schedule, reviewed for approval, and sent via email.
Can this be done in a workflow? It seems that the generation itself is a manual step. Can it be automated?

Example - Sales order for an annual subscription and quarterly invoicing is entered in espo. At the end of the first quarter, a workflow (or some automation) starts automatically, generates the invoice. It goes to a review and approval step, and once approved, the workflow proceeds to email the invoice. This repeats for each quarter.

Note that we are quite proficient now with the espo api, so that is a viable option.

Appreciate any comments on what is and is not possible.


Thanks