Relationships between tables Purch_ Inv_ + Sales Inv

mazheika
mazheika Member Posts: 5
in SQL General
Please, help me.  I need information on the sales invoice regarding quantity, price, etc., but in the report, I want to see the price at which the item was purchased, the currency used, and the purchase date.
How can I track this or combine sales invoice and purchase invoice?

Answers

  • vaprog
    vaprog Member Posts: 1,163
    Unless you work with drop shipment or special order, you can use Item Ledger Entry and Item Application Entry tables in order to figure this out. This depends on the costing method used by the item.

    As an alternative, you can use item tracking. Assing a lot on receipt and record the lot again on shipment. Or you can use Costing Methode ::Specific to establish a link.

    If you used any transfers between locations between purchase and sales, anything that relies on Item Application Entry migth get cumbersome very fast.
  • mazheika
    mazheika Member Posts: 5
    vaprog wrote: »
    Unless you work with drop shipment or special order, you can use Item Ledger Entry and Item Application Entry tables in order to figure this out. This depends on the costing method used by the item.

    As an alternative, you can use item tracking. Assing a lot on receipt and record the lot again on shipment. Or you can use Costing Methode ::Specific to establish a link.

    If you used any transfers between locations between purchase and sales, anything that relies on Item Application Entry migth get cumbersome very fast.

    I do not understand how to complete this chain here and go to the purchase invoice
    if you help, I will be grateful
    SELECT *
    FROM [Training$Sales Invoice Header$437] AS sih
    LEFT JOIN [Training$Sales Invoice Line$437] AS sil ON sil.[Document No_] = sih.[No_]
    JOIN [Training$Value Entry$437] AS ve ON ve.[Document No_] = sih.[No_] AND sil.[Line No_] = ve.[Document Line No_]
    JOIN [dbo].[Training$Item Ledger Entry$437] AS ile ON ve.[Item Ledger Entry No_] = ile.[Entry No_]
    WHERE ve.[Source Code] != 'INVTADJMT'

Categories