SQL view to show open AP invoices from multiple companies in GP


If you have multiple companies in GP and want to create a quick view to show a combined list of all the open AP invoices, you can use the example below.  I am not doing anything fancy here, just hard coding the company names and database ID’s.  In my experience this is a set list for most companies.  For this example, I am using 3 GP companies, but this can easily be adapted to more or less as needed.  You can copy the script below into SQL Query Analyzer or SQL Server Management Studio and then use the Replace feature to do the following:

  • Replace Company1 with 1st company name
  • Replace Company2 with 2nd company name
  • Replace Company3 with 3rd company name
  • Replace Comp1 with 1st company database ID
  • Replace Comp2 with 2nd company database ID
  • Replace Comp3 with 3rd company database ID

Of course, you can also do this manually – just follow the green ‘prompts’ in the code.

create view view_MultiCo_AP_Open
as
/** view_MultiCo_AP_Open
Shows all open AP invoices for multiple companies.
Created on 11/08/2008 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Does not take Multicurrency into account.
Assumes hard coding the companies. **/

/***** Replace Company1 with 1st company name *****/
SELECT 'Company1' as Company,
C1.VENDORID as 'Vendor ID',
C1M.VENDNAME as 'Vendor Name', C1.DOCDATE as 'Invoice Date',
C1.DOCNUMBR as 'Document Number',
CASE C1.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END AS 'Document Type', C1.VCHRNMBR as 'Voucher Number',
C1.DOCAMNT as 'Document Amount', C1.CURTRXAM as 'Current Trx Amount',
C1.TRXDSCRN as 'Transaction Description', C1.DUEDATE as 'Due Date'

/***** Replace Comp1 with 1st company database ID *****/
FROM Comp1..PM20000 C1
LEFT OUTER JOIN

/***** Replace Comp1 with 1st company database ID *****/
Comp1..PM00200 C1M
ON C1.VENDORID = C1M.VENDORID
WHERE C1.DOCTYPE in (1,2,3)
UNION ALL

/***** Replace Company2 with 2nd company name *****/
SELECT 'Company2' as Company,
C2.VENDORID as 'Vendor ID',
C2M.VENDNAME as 'Vendor Name', C2.DOCDATE as 'Invoice Date',
C2.DOCNUMBR as 'Document Number',
CASE C2.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END AS 'Document Type', C2.VCHRNMBR as 'Voucher Number',
C2.DOCAMNT as 'Document Amount', C2.CURTRXAM as 'Current Trx Amount',
C2.TRXDSCRN as 'Transaction Description', C2.DUEDATE as 'Due Date'

/***** Replace Comp2 with 2nd company database ID *****/
FROM Comp2..PM20000 C2
LEFT OUTER JOIN

/***** Replace Comp2 with 2nd company database ID *****/
Comp2..PM00200 C2M
ON C2.VENDORID = C2M.VENDORID
WHERE C2.DOCTYPE in (1,2,3)
UNION ALL

/***** Replace Company3 with 3rd company name *****/
SELECT 'Company3' as Company,
C3.VENDORID as 'Vendor ID',
C3M.VENDNAME as 'Vendor Name', C3.DOCDATE as 'Invoice Date',
C3.DOCNUMBR as 'Document Number',
CASE C3.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END AS 'Document Type', C3.VCHRNMBR as 'Voucher Number',
C3.DOCAMNT as 'Document Amount', C3.CURTRXAM as 'Current Trx Amount',
C3.TRXDSCRN as 'Transaction Description', C3.DUEDATE as 'Due Date'

/***** Replace Comp3 with 3rd company database ID *****/
FROM Comp3..PM20000 C3
LEFT OUTER JOIN

/***** Replace Comp3 with 3rd company database ID *****/
Comp3..PM00200 C3M
ON C3.VENDORID = C3M.VENDORID
WHERE C3.DOCTYPE in (1,2,3)

/**the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions**/
GO
GRANT SELECT ON view_MultiCo_AP_Open TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Note: if you’re looking to use this with Crystal Reports, use this slightly modified version that takes out spaces which Crystal does not like. 

— updated 11.15.2008

No comments yet... Be the first to leave a reply!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: