Day: September 28, 2023

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