[Amber Bell]

Up

0

Down

::

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;