This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script was written to return documents included in the selected payment batch (entered in the highlighted parameter) along with the apply information.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @BACHNUMB VARCHAR(15) = 'COMPUTER CHECKS'
SELECT
['Payables Document Types'].DOCTYNAM
,['PM Creditor Master'].VENDNAME
,['PM Transactions'].*
,['PM Payment Apply'].*
FROM
(
SELECT
BACHNUMB
,VCHRNMBR
,VENDORID
,DOCDATE AS PYMTDOCDATE
,CHEKBKID
,PSTGDATE
FROM
PM10300 AS ['PM Payment Work'] --PM Payment WORK (PM10300)
UNION ALL
SELECT
BACHNUMB
,VCHRNMBR
,VENDORID
,DOCDATE AS PYMTDOCDATE
,CHEKBKID
,PSTGDATE
FROM
PM30200 --PM Paid Transaction History File (PM30200)
WHERE
DOCTYPE = 6
) AS ['PM Transactions']
INNER JOIN
(
SELECT
DOCTYPE
,PMNTNMBR
,APFVCHNM
,DOCDATE
,CASE WHEN DOCTYPE >= 3 THEN
DOCAMNT * -1
ELSE
DOCAMNT
END AS DOCAMNT
,CASE WHEN DOCTYPE >= 3 THEN
0
ELSE
AMNTPAID
END AS AMNTPAID
,Net_Paid_Amount
FROM
PM10201 AS PM --PM Payment Apply To Work File (PM10201)
) AS ['PM Payment Apply']
ON
['PM Payment Apply'].PMNTNMBR = ['PM Transactions'].VCHRNMBR
INNER JOIN
PM40102 AS ['Payables Document Types'] --Payables Document Types (PM40102)
ON
['Payables Document Types'].DOCTYPE = ['PM Payment Apply'].DOCTYPE
INNER JOIN
PM00200 AS ['PM Creditor Master'] --PM Vendor Master File (PM00200)
ON
['PM Creditor Master'].VENDORID = ['PM Transactions'].VENDORID
WHERE
['PM Transactions'].BACHNUMB = @BACHNUMB
ORDER BY
['PM Payment Apply'].APFVCHNM