This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script creates a view on payables transactions which was created for a client who was migrating from Dynamics GP to another ERP system.
/*
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).
*/
CREATE VIEW uv_AZRCRV_SupplierTransactionExtract AS
SELECT
DB_NAME() AS Database_Name,
SY.CMPNYNAM AS Division,
P.VENDORID AS Vendor_ID,
PM.VENDNAME AS Vendor_Name,
case P.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
else ''
end AS Document_Type,
P.DOCDATE AS Document_Date,
P.VCHRNMBR AS Voucher_Number,
P.DOCNUMBR AS Document_Number,
P.DOCAMNT AS Document_Amount,
P.PRCHAMNT AS Document_Net,
P.TAXAMNT AS Document_Tax,
coalesce(PA.VCHRNMBR,'') AS Payment_Voucher_Number,
coalesce(P2.DOCNUMBR,'') AS Payment_Doc_Number,
coalesce(P2.DOCDATE,'1/1/1900') AS Payment_Date,
coalesce(PA.DATE1,'1/1/1900') AS Apply_Date,
PA.APPLDAMT AS Apply_Amount,
case PA.DOCTYPE
when 1 then 'Invoice'
when 2 then 'Finance Charge'
when 3 then 'Misc Charge'
when 4 then 'Return'
when 5 then 'Credit Memo'
when 6 then 'Payment'
else ''
end AS Payment_Type
from
(select
VENDORID,
DOCTYPE,
DOCDATE,
VCHRNMBR,
DOCNUMBR,
DOCAMNT,
PRCHAMNT,
TAXAMNT,
VOIDED
from dbo.PM30200
union all
select
VENDORID,
DOCTYPE,
DOCDATE,
VCHRNMBR,
DOCNUMBR,
DOCAMNT,
PRCHAMNT,
TAXAMNT,
VOIDED
from dbo.PM20000) P
left outer join
(select
VENDORID,
APTVCHNM,
APTODCTY,
APFRDCNM,
DOCTYPE,
DOCDATE,
APPLDAMT,
VCHRNMBR,
DATE1,
GLPOSTDT,
case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
from dbo.PM10200
UNION
select
VENDORID,
APTVCHNM,
APTODCTY,
APFRDCNM,
DOCTYPE,
DOCDATE,
APPLDAMT,
VCHRNMBR,
DATE1,
GLPOSTDT,
'Posted' AS POSTED
from dbo.PM30300
UNION
select
VENDORID,
VCHRNMBR AS APTVCHNM,
DOCTYPE AS APTODCTY,
APTODCNM AS APFRDCNM,
APTODCTY AS DOCTYPE,
APTODCDT AS DOCDATE,
APPLDAMT,
APTVCHNM AS VCHRNMBR,
DATE1,
GLPOSTDT,
case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
from dbo.PM10200
union
select
VENDORID,
VCHRNMBR AS APTVCHNM,
DOCTYPE AS APTODCTY,
APTODCNM AS APFRDCNM,
APTODCTY AS DOCTYPE,
APTODCDT AS DOCDATE,
APPLDAMT,
APTVCHNM AS VCHRNMBR,
DATE1,
GLPOSTDT,
'Posted' AS POSTED
from dbo.PM30300) PA
on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
and P.DOCTYPE = PA.APTODCTY
left outer join
(select
VCHRNMBR,
DOCTYPE,
DOCNUMBR,
DOCDATE
from dbo.PM20000
union all
select
VCHRNMBR,
DOCTYPE,
DOCNUMBR,
DOCDATE
from dbo.PM30200) P2
on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE
left outer join dbo.PM00200 PM
on P.VENDORID = PM.VENDORID
INNER JOIN
D20..SY01500 AS SY WITH (NOLOCK)
ON INTERID = DB_NAME()
where P.DOCTYPE in (1,2,3,4,5,6) and P.VOIDED = 0 AND (P.VENDORID NOT LIKE '5%' AND P.VENDORID NOT LIKE '3%')
GO
GRANT SELECT ON uv_AZRCRV_SupplierTransactionExtract TO DYNGRP
GO
SELECT * FROM uv_AZRCRV_SupplierTransactionExtract
GO