Linking a PO to its Voucher

This query demonstrates linking a PO through the receipts to the voucher. It's only meant as a starting point, normally we'd do some grouping along the way

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

SELECT po.PONUMBER, po.POStatus, pol.ITEMNMBR, pol.qty, rlq.QTYSHPPD, rlq.QTYINVCD, rlq.VCHRNMBR, rlq.DOCAMNT, rlq.CURTRXAM
    FROM (
        SELECT ponumber, 'OPEN' AS POStatus FROM pop10100
        UNION ALL
        SELECT ponumber, 'HIST' AS POStatus FROM pop30100
        ) PO
        JOIN (
            SELECT ponumber, ord, ITEMNMBR, QTYORDER - QTYCANCE AS qty FROM pop10110
            UNION ALL
            SELECT ponumber, ORD, itemnmbr, QTYORDER - QTYCANCE AS qty FROM pop30110
            ) pol ON pol.PONUMBER = po.PONUMBER
        LEFT JOIN (
            SELECT rlq.ponumber, POLNENUM, QTYSHPPD, QTYINVCD, rctl.ITEMNMBR, rct.VCHRNMBR, pt.DOCAMNT, pt.CURTRXAM
                FROM POP10500 rlq
                    JOIN pop30310 rctl ON rctl.POPRCTNM = rlq.POPRCTNM AND rctl.RCPTLNNM = rlq.RCPTLNNM
                    JOIN pop30300 rct ON rct.POPRCTNM = rctl.POPRCTNM
                    LEFT JOIN (
                        SELECT pt.VCHRNMBR, pt.DOCAMNT, pt.CURTRXAM FROM PM20000 pt
                        UNION ALL
                        SELECT vchrnmbr, pt.DOCAMNT, pt.CURTRXAM FROM pm30200 pt
                        ) pt ON pt.VCHRNMBR = rct.VCHRNMBR
            ) rlq ON rlq.PONUMBER = pol.PONUMBER AND rlq.POLNENUM = pol.ORD
    WHERE po.PONUMBER = '89487            '

The problem is that one PO can have many receipts. The receipt table might have multiple lines for shipping and invoicing. A receipt for two items goes on one voucher. This makes grouping very cumbersome. 

Note in the image below that one PO has two items, each item has several receipts, and that two items go on one voucher. Difficult to group. 

 

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences