From The Community #5 – Match Invoice Receipt To PO Line Item

A question in the Dynamics GP forums was posted on the following issue:

“I have been working on making a way in SQL to match the invoice line receipt to the PO line receipt. My boss wants me to have a report that can compare an item’s receipt with the date we received the receipt. GP does this on a line-by-line basis so there must be a connection somewhere but I’m having trouble consistently getting it right.“

https://community.dynamics.com/forums/t/33716.aspx

Sagi88 provides the following script that shows the receipt number, invoice number, date an item was received, and then subsequently invoiced, the quantity invoiced, quantity shipped, and quantity rejected.

/*******************************************************************
Created on May 1, 2010 by Sagi88 (https://community.dynamics.com/forums/t/33716.aspx)
Posted By Ron Wilson
For updates visit http://rldu.wordpress.com/sql-scripts-views/
*******************************************************************/

Select  a.POPIVCNO,
        a.IVCLINNO,
        a.POPRCTNM,
        a.RCPTLNNM,
        b.VENDORID,
        b.VENDNAME,
        b.VNDDOCNM AS [Invoice No],
        b.receiptdate as [Invoice Date],
        c.VNDDOCNM as [Bill of Lading],
        c.receiptdate as [Date Received],
        d.ITEMNMBR,
        d.ITEMDESC,
        d.VNDITNUM,
        d.VNDITDSC,
        a.QTYINVCD,
        e.QTYSHPPD,
        e.QTYREJ
From    POP10600 a
INNER JOIN POP30300 b ON a.POPIVCNO = b.POPRCTNM
INNER JOIN POP30300 c ON a.POPRCTNM = c.POPRCTNM
INNER JOIN POP30310 d on a.POPRCTNM = d.POPRCTNM
                         and a.RCPTLNNM = d.RCPTLNNM
INNER JOIN POP10500 e on a.POPRCTNM = e.POPRCTNM
                         and a.RCPTLNNM = e.RCPTLNNM

Thanks Sagi88 for a nicely written script.