Customer payment journal and settlement of invoices

There are lot of customers who pay multiple invoices via a single payment. The payment can be made via check, wire transfer or any other method of payment. Along with the payment, there is usually a breakdown provided of invoices being paid, how much amount is being paid. In case of EDI on single customer payment 100’s of invoices can be paid.

When such a payment is received, AR user would create a customer payment journal line based on total amount received. Based on breakdown of invoices and amount provided, multiple invoices need to be marked, updated for amount paid etc. This process is very time consuming and error prone.

D365 allows a functionality via Data management to upload breakdown of payments. The input is list of invoices and amount paid on the invoice by the customer. The upload process will settle the invoice/payment automatically.

Data Entity to be used “Customer payment journal line settled invoice”

Scenario for this blogpost:

There are two customers who paid multiple invoices.

Customer “US-001” paid 3 invoices for a total of $568.13.

Customer “US-008” paid 7 invoices for a total of $1827.70

Both customers paid some invoices in full and some partial invoices. It is not necessary to know during upload which invoices are paid in fully and which are partial.

Steps to follow in D365FO:

Create a payment journal

Go to lines and create two lines based on payment received:

Once this is done, let’s prepare the template that we can upload into D365FO so we can mark the invoices against these payments.

Template preparation for upload:

To prepare the template, use Data Management, create export project and export the entity “Customer payment journal line settled invoice”. This will help get the upload template that can be used to import the template.

Once the template is ready, here is how it will look like:

• JournalLineCompany – Company where payment journal is created.
• JournalBatchNumber – Journal number where you created the payment line.
• JournalLineNumber – Line number of the payment journal against which you want to settle the invoices.
• InvoiceNumber – Invoice being paid.
• InvoiceCompany – Company to which invoice belong to.
• InvoiceDueDate – Due date of the invoice. If not available, then need to retrieve this from D365 using a different entity.
• AccountDisplayValue – Customer account number.
• SettlementAmountInvoiceCurrency – Amount paid for the invoice.

Upload the template in D365FO:

Create an import project under “Data Management” and upload the file.

Once the upload is finished, you should see message like below.

If there are any error messages, they will be shown on the screen. Click on Infolog to review the error details.

View journal after settlement upload:

To view the impact of upload on the payment journal, go back to payment journal > Lines.

Notice how “*” shows up in invoice column, that denotes that there are multiple invoices settled with this payment. To verify the settlement via import, click on settle transactions. This is just for verification and not required every time you upload the invoice settlement.

Post the journal after verification and you are all set.

Few considerations during the entire process:

1. Journal line creation
   1. In this example, the payment journal lines were created manually prior to upload. However, “customer payment journal line” entity can be used to import multiple payment lines at once and subsequently “customer payment journal line settled invoice” entity can be used to settle the invoices.
2. File creation
   1. If your customer number uses the same delimiter as financial dimension, add “\” to the customer number, else it’s not required.
   2. Line number is required which comes from payment journal, if line number is not visible in journal line, personalize the form and add the line number field.
   3. Invoice due date is required. If invoice due date is not mentioned, during import there will be an error and import will not succeed.
3. Entity “Customer payment journal line settled invoice” only help settle the invoices, it doesn’t update anything in payment journal lines. That’s why we created the lines prior to importing of the settlement file. Tables CustTransOpen and LedgerjournalTrans are considered read-only from import standpoint as part of the standard entity.

Overall, the multi-step process may not be ideal, but this is out of the box solutions that can save customers lot of time if they are getting lot of invoices paid over a single payment. Also use your judgement when it is beneficial to use upload process vs. when to settle invoices manually. To streamline this further, a custom entity can be created that can take a single file as input and automatically creates payment journal lines and settle the invoices.