How to use DIAPI to create Down Payment Invoice and A/R Invoice so that related Journal Entries can auto-reconcile

[Former Member]

I create Down Payment and A/R invoices using the DI-API but I can't figure out how to affect the creation of the related Journal Entries so that they can be auto-reconciled.  Any suggestions?

Thanks in advance,

Brad Thomas

[Johan_H]

Hi Brad,

I have never used (down) payments myself, so I can only theorize a bit. So for what it is worth:

As I understand the basic idea, and how B1 usually works, you first create and add an invoice, and then a down payment. In the down payment object you (probably) first connect the invoice with the DocInternalID property, and then you determine the amount with the AmountToDraw property.

Then, assuming that you determine as few other properties as possible, so you only set the absolutely mandatory ones, you add the down payment object, and  that should already take care of the journal entry / reconciliation bit.

So the question is, what actually happens when you test this method ? Or perhaps the better question, what doesn't happen that should happen ?

Regards,

Johan

[Former Member]

John,

I think the problem is better described with an example using the B1 app.

For a little background, we take orders from our website and charge the customer's credit card when the customer confirms the order.  That order is then pushed into Business One using the DIAPI.

But, the same issues occur when I use the B1 GUI like this.

I create a Sales Order.  Next I create a Down Payment Invoice using the CopyTo button on the Sales Order.

From the Down Payment Invoice I create an Incoming Payment for the credit card charge.

Back on the Sales Order I create a Delivery using the CopyTo button on the Sales Order.

Finally, I create an Invoice using the CopyTo button on the Delivery.  The Invoice automatically uses up the balance available on the Down Payment Invoice.

Each of these steps automaitcally creates a Journal Entry. 

Creating the Down Payment Invoice creates a Journal Entry that shows a debit on the Business Partner and a credit in our Customer Prepayment account.

Creating the Incoming Payment creates a Journal Entry that shows a credit on the Business Partner (that balances the debit) and a debit on the Incoming Payment Clearing account.

Creating the A/R Invoice creates a Journal Entrys that shows a debit on the Customer Prepayment account (tha balances the credit) and various credits to Gross Saless and Shipping Revenue.

So, the accounting all balances out but the Journal Entries from the Down Payment Invoice and the A/R invoice have to be reconciled manually.

Automatic reconciliation can occur if there is some common value between the two Journal Entries.  Candidates are Ref1, Ref2 and some other columns.  Ref1 is initialized by the process that creates the Journal Entries as the Down Payment and A/R invoices are created but the values are different.  I thought if I could set Ref2 somehow as I create the Down Payment and A/R invoices then I could set things up for auto-reconcile.

[Johan_H]

Hi Brad,

Okay, I am starting to see the challenge here. Just a couple of questions, as a matter of brainstorming, if you will:

• Do I understand correctly that the work flow you described, is formed by the idea that you first want to confirm that the customer has paid at least the down payment, before you deliver?

• Did I understand correctly, that when you perform this process / work flow in the B1 client, you have to perform the reconciliation between invoice and down payment manually ? Or did you mean that you only have to do that when you perform the process with the DI API ?

Regards,

Johan

[Former Member]

Hi John,

Our workflow goes like this.

Customer places order on our website and the website charges their credit card.

A DI API application synchs the order from the website to B1, and in that process creates the Sales Order, then a Down Payment Invoice and then a Payment Means.

Some time later, we have a DI API program that creates a Delivery for that Order.

Some time after that, we have a DI API program that creates an Invoice for that Delivery.

At that point, there are a couple of Journal Entries, one for the Down Payment Invoice and one for the A/R Invoice that have to be reconciled.  The number are correct but the Journal Entries have to manually reconciled.

This is the case whether all of the B1 objects (Sales Order, Down Payment Invoice, Delivery and A/R Invoice) are created through the DI API or the B1 Application.

From what I understand so far is that this is normal.  The trick is to be able to set up the Journal Entries so that they can be automatically reconciled.  To do that, it seems that if I must figure out how to set Reference2 or Reference3 in the Journal Entries so that there is common date to match on.

I was hoping that if I set Reference2 in the Down Payment Invoice and the A/R Invoice as they were created that that value would be initialize in reference2 of the Journal Entries that get created.  That doesn't seem to be the case, though.  i tried that using the B1 Application.

Now I'm thinking that I will have to modify my applications to update the Journal Entry immediately after I create a Down Payment Invoice or an A/R Invoice.  I've been experimenting with the auto-reconcile functionality in the B1 Application to understand how that works.

To answer your first question specifically, I think the point of the Down Payment Invoice is to book the income when it is received.  It is possible that a customer orders something a month or more in advance but pays immediately.

To answer your second question, ot seems that unreconciled Journal Entries are created by the B1 Application the same as a DI API application.

Brad

[Johan_H]

Hi Brad,

Considering that the DI API and the B1 client behave in the same way, I think that you are on the right track with the idea to access the relevant journal entries right away. Although I have no idea if there is any reliable way to do so. Are there any methods to retrieve the journal entry related to a (freshly added) document or payment object?

regards,

Johan

[Former Member]

Johan,

It looks like the Journal Entry gets created with the Ref1 field initialized to the DocEntry of the related document (Down Payment Invoice, A/R Invoice).  Based on that, I'm working with the assumption that I can select each Journal Entry by the value in Ref1 and update the Ref2 value with some common value.  I think the common value will be the DocEntry of the Sales Order.

I verified that the Automatic Reconciliation of Journal Entries works when I manually update the ref2 field of the Journal Entries that I want to auto-reconcile.

I don't know of a way to trigger the automatic reconciliation of the Journal Entries through the API but it won't be too much of a hardship on my accounting people to perform the task with the B1 Application.  Right now they are having to manually reconcile hundreds of Journal Entries every week.  During the holidays they have to reconcile thousands per week.

Thanks for discussing this with me.  It really helps to write my thoughts out like this.

Brad

[Former Member]

Johan,

I finally found the answer to initializing the Ref2 field of the Journal Entry in the B1 Application Help (go figure).

This is a snippet from the Help document Journal Entry Window section regarding Ref. 2

Displays different information, depending on the
document through which the journal entry was created:

• 
  Displays the customer/vendor reference number assigned to the
  document, if the journal entry resulted from an A/R Invoice, Incoming Payment,
  and so on.

So, if I set the Customer Ref. No. (ORDR.NumAtCard) field to something unique (like the DocNum of the Order) as I create the Sales Order then this value will carry forward through the Down Payment Invoice and A/R Invoice.  And, this value will be used to initialize the Ref2 field of the Journal Entry records that are associated with the Down Payment and A/R invoices.

With that common value in the Ref2 field of the Journal Entries I can use the B1 Application to automatically reconcile the Journal Entries.

I think this will solve my problem.

Thanks again for your thoughts and comments.

Brad

[Johan_H]

Hi Brad,

You are welcome. I know how valuable some simple sparring can be

Regards,

Johan