I am trying to join the PORDER table to the SINVOICED table

SOLVED

Good morning Sage City,

I am creating an invoices tables with the base table SINVOICED, I need to JOIN PORDER table to my base table but am unsure of how to do so. How can I go about doing this?

  • 0
    SUGGESTED

    This could be done only in case if you use Back-to-back orders, otherwise this is not possible.
    One of possible links could be like SINVOICED -> SORDERD -> PORDERD -> PORDER.

  • 0
    SUGGESTED

    This is possible for stock products that are lot controlled outside where the back to back orders process is not being used.

    You can create a SQL view that links the SINVOICED table to SDELIVERYD to gain the stock movement reference, linking that to the STOJOU table to obtain the lot number. Then add another instance of STOJOU to the same view, linked via the product code and lot number to pick up the receipt number that generated the stock originally and then link via PRECEIPTD to the PORDER table.

  • 0 in reply to Nick Brown CPiO
    verified answer

    Beware in such scenario you can have multiple Sales invoice lines matching multiple Purchase order lines (even if it rare to receive twice the same lot, it is a possibility). If you are using project, you can have a link as well between purchase documents and sales documents.