Amber Bell
Forum Replies Created
-
I would make sure you keep on the latest supported SQL Version/Windows Server version especially as we get closer to the end of support date for GP. This will help since those will be supported longer than GP. If you are using Payroll for GP, that will see the first issues since the new tax tables won’t be provided. Also, you’ll want to keep current with the other Microsoft tools so that you will be on the newest supported versions that still work with the last version of GP.
-
I just finished a test…when I created an AP Invoice under one User ID it showed that User ID as the Posted User ID. When I created a credit memo and applied it to that invoice, the Posted User ID updated to that User ID. I also tested partially applying to the Invoice. In that case, the original User ID stayed.
My conclusion: That field is updating to the User that was responsible for moving it from the Open Table to the History table. Since you are closing out the balance on the transactions, you are the one moving it to history…so it changes to your name. It might be better named “User Who Wrote the transaction to the current table” vs. User Who Posted.
-
I thought you might want the Journal Entry number and date. I added that table. I limited it to just pull the first journal entry number and then create a column to let you know how many journal entries are part of that Transaction Source. Also…I made a query…not a view. If you need it as a view, let me know.
WITH GLData AS ( SELECT ORGNTSRC, JRNENTRY, TRXDATE, ROW_NUMBER() OVER (PARTITION BY ORGNTSRC ORDER BY TRXDATE) AS rn FROM GL20000 ) SELECT PM10100.TRXSORCE AS [Transaction Source], PM10100.VCHRNMBR AS [Voucher Number], PM10100.VENDORID AS [Vendor ID], PM10100.DEBITAMT AS [Debit Amount], PM10100.CRDTAMNT AS [Credit Amount], GL00100.ACTDESCR AS [GL Account Description], RTRIM( ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_10), '') ) AS [Account Number], GLData.JRNENTRY AS [Journal Entry], GLData.TRXDATE AS [GL Trx Date], (SELECT COUNT(DISTINCT JRNENTRY) FROM GL20000 WHERE ORGNTSRC = PM10100.TRXSORCE) AS [Unique Journal Entry Count] FROM PM10100 JOIN GL00100 ON PM10100.DSTINDX = GL00100.ACTINDX LEFT JOIN GLData ON PM10100.TRXSORCE = GLData.ORGNTSRC AND GLData.rn = 1 UNION ALL SELECT PM30600.TRXSORCE AS [Transaction Source], PM30600.VCHRNMBR AS [Voucher Number], PM30600.VENDORID AS [Vendor ID], PM30600.DEBITAMT AS [Debit Amount], PM30600.CRDTAMNT AS [Credit Amount], GL00100.ACTDESCR AS [GL Account Description], RTRIM( ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_10), '') ) AS [Account Number], GLData.JRNENTRY AS [Journal Entry], GLData.TRXDATE AS [GL Trx Date], (SELECT COUNT(DISTINCT JRNENTRY) FROM GL20000 WHERE ORGNTSRC = PM30600.TRXSORCE) AS [Unique Journal Entry Count] FROM PM30600 JOIN GL00100 ON PM30600.DSTINDX = GL00100.ACTINDX LEFT JOIN GLData ON PM30600.TRXSORCE = GLData.ORGNTSRC AND GLData.rn = 1; -
Hello Thomas, I worked with Microsoft Copilot to create a SQL View to help you! I included all 10 Account segment columns. You can edit that out:
SELECT PM10100.TRXSORCE AS [Transaction Source], PM10100.VCHRNMBR AS [Voucher Number], PM10100.VENDORID AS [Vendor ID], PM10100.DEBITAMT AS [Debit Amount], PM10100.CRDTAMNT AS [Credit Amount], GL00100.ACTDESCR AS [GL Account Description], RTRIM( ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_10), '') ) AS [Account Number] FROM PM10100 JOIN GL00100 ON PM10100.DSTINDX = GL00100.ACTINDX UNION ALL SELECT PM30600.TRXSORCE AS [Transaction Source], PM30600.VCHRNMBR AS [Voucher Number], PM30600.VENDORID AS [Vendor ID], PM30600.DEBITAMT AS [Debit Amount], PM30600.CRDTAMNT AS [Credit Amount], GL00100.ACTDESCR AS [GL Account Description], RTRIM( ISNULL(RTRIM(GL00100.ACTNUMBR_1) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_2) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_3) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_4) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_5) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_6) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_7) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_8) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_9) + '-', '') + ISNULL(RTRIM(GL00100.ACTNUMBR_10), '') ) AS [Account Number] FROM PM30600 JOIN GL00100 ON PM30600.DSTINDX = GL00100.ACTINDX;
