Small and medium business | Business Central, N...

Relating a purchase invoice to its many purchase receipts

(0) Share

Report

Posted on by Ibu

I'm querying BC for a report presenting all Purchase Orders with their Receipts and Invoices. I cannot find how to relate an invoice line to its receipt lines, when that invoice lines pays for multiple receipt lines.

I built the following data model to connect order lines, receipt lines & invoice lines together. Note the PurchaseOrderRowKey, PurchaseReceiptRowKey columns that serve as foreign keys and enabling the 1:many relationships.

I then noticed that not every invoice line has a value for [Receipt No_] in the Purch_ Inv_ Line table. This means the report shows orders & receipts without their corresponding (but existing) invoices. I identified 2 cases when this happens:

An order line was received in 2+ parts, and each receipt line still has its own invoice line. In this case, the [Receipt No_] column is empty, but I can backfill it by looking in the Purch_ Rcpt_ Line table based on the [Order No_] value.
An order line was received in 2+ parts, but 2+ receipt lines share the same invoice. In this case, it makes sense that there is no single value for [Receipt No_]. Yet, viewing the receipt line in BC and clicking "Item Invoice Lines" correctly presents the invoice. Likewise, viewing the invoice line and clicking "Item Receipt Lines" also presents the multiple receipts.

I am looking for how to correctly establish the relationship between an invoice line and its multiple receipt lines for case (2), like BC appears able to do.

A potential approach could be to duplicate the invoice line into as many lines as there are corresponding receipt lines. The quantity of each invoice line would need to be adjusted to reflect the corresponding receipt line quantity, until the invoiced quantity is exhausted (i.e., there is not necessarily enough invoiced quantity to cover all receipt line quantities). That said, it leaves up to chance the way to select which receipt lines get chosen first. Is there a better way?

Alternatively, I can change the data model to have Purchase Receipt and Purchase Invoice both relate directly to Purchase. With this, every receipt and invoice get counted, although I am not able to relate Invoices to Receipts which is highly desirable.

All responses (6)

Answers (0)

Suggested answer

Manan_Shah 1,457 on at

Like (0)

Report

RE: Relating a purchase invoice to its many purchase receipts

Hi IBU,

To get Purchase Order of Posted Purchase Invoice.

Please follow below steps.

1) On Value Entry Page.

-> For Document type as "Purchase Invoice" on this line you can fine Item Ledger entry no.

-> Use that no. and put filter on Item Ledger entry table on Entry no. field you will get line where Document type as "Purchase Receipt".

-> On this Posted Purchase Receipt header you will get Purchase Order no.

Hope this will help.

Many Thanks,
Manan
Suggested answer

Dallefeld 11,425 User Group Leader on at

Like (0)

Report

RE: Relating a purchase invoice to its many purchase receipts

Build a list of POs numbers from Posted Purchase Receipts, then to connect the receipts to the invoices you have to go through the value entry table. Value Entry table filtering for the item ledger entry type of Purchase, Document Type of Purchase Receipt.Value Entry table filtering for the item ledger entry type of Purchase, Document Type of Purchase Invoice.

What is the purpose of such a report?
Ibu 25 on at

Like (0)

Report

RE: Relating a purchase invoice to its many purchase receipts

Kim Dallefeld Amit_Sharma I took a look at ILE and VE and I don't see how they can be used here. As a cursory check, I queried ILE for [Document Type] = 6 (Purchase Invoice), and there are no rows. There's also no Document Type] enum value for Purchase/Order.

To restate my goal, it is to build a visual like this, where for each Vendor it shows the list of Purchase Orders (CAxxx), and for each Purchase Order, the list of Receipts (RCxxx, if any), and for each Receipt, the list of Invoices (FAxxx, if any). This is after filtering all 3 types of documents based on their "Document Date".

So far the Purchase Line/Purchase Receipt Line/Purchase Invoice Line tables have gotten me 99% there, I just noticed some Invoice Lines don't have a relation to the receipt, and as such are excluded from the report.
Suggested answer

Amit_Sharma 2,545 on at

Like (0)

Report

RE: Relating a purchase invoice to its many purchase receipts

Hi,

Use ILE and VE for achieving this.

Regards

Amit Sharma

www.erpconsultors.com
Ibu 25 on at

Like (0)

Report

RE: Relating a purchase invoice to its many purchase receipts

Thanks Kim Dallefeld, I will take a look at those tables and reply back.

I must be able to present the Ordered / Received / Invoiced data from the point of view of any date in the past. Will using those tables you mention allow to reconstruct totals as of a specific date?

When I did this for Accounts Receivable/Payable, I had to use the Detailed Customer/Vendor Ledger Entry tables to recalculate totals from any point in time (vs. using the "current" calculated amounts in the regular C/V Ledger Entry tables). Wondering if there's a similar distinction here.
Suggested answer

Dallefeld 11,425 User Group Leader on at

Like (1)

Report

RE: Relating a purchase invoice to its many purchase receipts

I would suggest using item ledger and value entries. Using receipt lines and invoice lines is virtually impossible. With Business Central automatically doing the matching at the item ledger and associated value entries, why reinvent the wheel.