Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / vendor ledger report
Microsoft Dynamics GP (Archived)

vendor ledger report

(0) ShareShare
ReportReport
Posted on by Rashed Alhakimi 2,095
Hi All ,

As you know that the vendor ledger report available in GP doesn’t show the payable balance along with the exact detail of payable invoices with date option. I need a report in which in one column (Cr.) all the invoices (invoice values) should appear based on date sequence and in other column (Dr.) all the payment details should be appear. In third column (Balance) the unpaid value of respective invoices should be appear which shows net payable balance of any vendor at any date.

is there any way to solve this problem ?

 

Thank you

Rashed 

 

 

*This post is locked for comments

  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at
    Re: Re: Re: Re: vendor ledger report
    Yes Ruel, just for hint I share this code, I also recommend that instead of using view convert it a Table valued parameterized function, moreover, in my case I kept apply information separately because of some unexpected scenarios.
  • Community Member Profile Picture
    Community Member on at
    Re: Re: Re: vendor ledger report

    Well and good Rashid, although I respectfully would like to add some notes to this. First of all I don't see the use of linking in the Notes Master (SY03900) as you only need the note index and that is already available from the originating table.

    Also, it is lacking apply information, there are no apply tables in sight. He would need the apply tables to be able to calculate the current transaction amount at a given date. Granted the transactions are classified as invoice or payment via the DocAbrev field, but the payment(s) aren't correlated to the invoice they are applied to, so you wouldn't know how to subtract which from which.

     

  • Dencio Profile Picture
    Dencio 2,172 on at
    Re: Re: vendor ledger report

    Thanks Rashid! This is good stuff and enough for Rashed to start a custom report either CR or SSRS.

    Cheers,

    Dencio 

  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at
    Re: vendor ledger report

    I am sharing a view here and hope it could help you. Just minor changes required for writeoff case in DocAbrev field if you are not using default Payable Setup Document (Microsoft Dynamics GP-->Tools->Setup->Inventory->Inventory Control->Option).

    <----------------------------------------------------------------------------------------------------------- >

    CREATE VIEW [dbo].[vw_VendorLedger]

    WITH SCHEMABINDING

    AS --Posted(History) Manual Payment

    SELECT SeqNo = CASE WHEN dbo.PM30200.DocType IN ( 1, 2, 3 ) THEN 1

    WHEN dbo.PM30200.DocType IN ( 4, 5, 6, 7 ) THEN 2

    ELSE 3

    END,

    VendorID,

    PM30200.DocDate,

    DocAbrev AS TransType,

    RTRIM(PM30200.DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = CASE WHEN dbo.PM30200.DocType IN ( 4, 5, 6, 7 ) THEN DocAmnt ELSE 0

    END,

    Credit = CASE WHEN dbo.PM30200.DocType IN ( 1, 2, 3 ) THEN DocAmnt ELSE 0

    END,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN PPSAmDed ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    PM30200.VOIDED,

    'Posted' AS Status

    FROM dbo.PM30200

    INNER JOIN dbo.PM40102 ON dbo.PM30200.DocType = dbo.PM40102.DocType LEFT JOIN dbo.CM00100 ON PM30200.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM30200.NOTEINDX = A.NOTEINDX

    UNION ALL

    SELECT SeqNo = CASE WHEN dbo.PM30200.DocType IN ( 1, 2, 3 ) THEN 1

    WHEN dbo.PM30200.DocType IN ( 4, 5, 6, 7 ) THEN 2

    ELSE 3

    END,

    VendorID,

    PM30200.DocDate,

    DocAbrev AS TransType,

    RTRIM(PM30200.DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = 0,

    Credit = 0,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN WROFAMNT ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    PM30200.VOIDED,

    'Posted' AS Status

    FROM dbo.PM30200

    INNER JOIN dbo.PM40102 ON dbo.PM30200.DocType = dbo.PM40102.DocType LEFT JOIN dbo.CM00100 ON PM30200.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM30200.NOTEINDX = A.NOTEINDX

    UNION ALL

    SELECT SeqNo = CASE WHEN dbo.PM30200.DocType IN ( 1, 2, 3 ) THEN 1

    WHEN dbo.PM30200.DocType IN ( 4, 5, 6, 7 ) THEN 2

    ELSE 3

    END,

    PM30200.VendorID,

    PM30200.DocDate,

    DocAbrev AS TransType,

    RTRIM(PM30200.DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = 0,

    Credit = 0,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' )

    THEN dbo.PM30600.CRDTAMNT ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    PM30200.VOIDED,

    'Posted' AS Status

    FROM dbo.PM30200

    INNER JOIN dbo.PM40102 ON dbo.PM30200.DocType = dbo.PM40102.DocType

    INNER JOIN dbo.PM30600 ON PM30200.VCHRNMBR = dbo.PM30600.VCHRNMBR

    LEFT JOIN dbo.CM00100 ON PM30200.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM30200.NOTEINDX = A.NOTEINDX

    WHERE dbo.PM30600.DISTTYPE = 2

    AND dbo.PM30600.CRDTAMNT <> 0

    AND PM30200.DOCTYPE = 6

    AND PM30200.VOIDED = 0

    UNION ALL

    --All Posted Documents Here!

    SELECT SeqNo = CASE WHEN Voided = 1 THEN 8 ELSE CASE WHEN dbo.PM20000.DocType IN ( 1, 2, 3 ) THEN 3

    WHEN dbo.PM20000.DocType IN ( 4, 5, 6, 7 )

    THEN 4 ELSE 3

    END

    END,

    VendorID,

    PM20000.DocDate,

    DocAbrev AS TransType,

    RTRIM(DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = CASE WHEN dbo.PM20000.DocType IN ( 4, 5, 6, 7 ) THEN DocAmnt ELSE 0

    END,

    Credit = CASE WHEN dbo.PM20000.DocType IN ( 1, 2, 3 ) THEN DocAmnt ELSE 0

    END,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN PPSAmDed ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    PM20000.VOIDED,

    'Posted' AS Status

    FROM dbo.PM20000

    INNER JOIN dbo.PM40102 ON dbo.PM20000.DocType = dbo.PM40102.DocType LEFT JOIN dbo.CM00100 ON PM20000.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM20000.NOTEINDX = A.NOTEINDX

    UNION ALL

    SELECT SeqNo = CASE WHEN Voided = 1 THEN 8 ELSE CASE WHEN dbo.PM20000.DocType IN ( 1, 2, 3 ) THEN 3

    WHEN dbo.PM20000.DocType IN ( 4, 5, 6, 7 )

    THEN 4 ELSE 3

    END

    END,

    VendorID,

    PM20000.DocDate,

    DocAbrev AS TransType,

    RTRIM(DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = 0,

    Credit = 0,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN WROFAMNT ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    PM20000.VOIDED,

    'Posted' AS Status

    FROM dbo.PM20000

    INNER JOIN dbo.PM40102 ON dbo.PM20000.DocType = dbo.PM40102.DocType LEFT JOIN dbo.CM00100 ON PM20000.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM20000.NOTEINDX = A.NOTEINDX

    UNION ALL

    --Unposted Payable Transactions

    SELECT SeqNo = CASE WHEN dbo.PM10000.DocType IN ( 1, 2, 3 ) THEN 5

    WHEN dbo.PM10000.DocType IN ( 4, 5, 6, 7 ) THEN 6

    ELSE 3

    END,

    VendorID,

    PM10000.DocDate,

    DocAbrev AS TransType,

    RTRIM(DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = CASE WHEN dbo.PM10000.DocType IN ( 4, 5, 6, 7 ) THEN DocAmnt ELSE 0

    END,

    Credit = CASE WHEN dbo.PM10000.DocType IN ( 1, 2, 3 ) THEN DocAmnt ELSE 0

    END,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN PPSAmDed ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    0 AS VOIDED,

    'Unposted' AS Status

    FROM dbo.PM10000

    INNER JOIN dbo.PM40102 ON dbo.PM10000.DocType = dbo.PM40102.DocType LEFT JOIN dbo.CM00100 ON PM10000.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM10000.NOTEINDX = A.NOTEINDX

    UNION ALL

    --Unposted Manual Payment Transactions

    SELECT SeqNo = CASE WHEN dbo.PM10400.DocType IN ( 1, 2, 3 ) THEN 7

    WHEN dbo.PM10400.DocType IN ( 4, 5, 6, 7 ) THEN 8

    ELSE 3

    END,

    VendorID,

    PM10400.DocDate,

    DocAbrev AS TransType,

    RTRIM(PM10400.DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = CASE WHEN dbo.PM10400.DocType IN ( 4, 5, 6, 7 ) THEN DocAmnt ELSE 0

    END,

    Credit = CASE WHEN dbo.PM10400.DocType IN ( 1, 2, 3 ) THEN DocAmnt --- DistKNAm - WrOfAmnt

    ELSE 0

    END,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN PPSAmDed ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    0 AS VOIDED,

    'Unposted' AS Status

    FROM dbo.PM10400

    INNER JOIN dbo.PM40102 ON dbo.PM10400.DocType = dbo.PM40102.DocType LEFT JOIN dbo.CM00100 ON PM10400.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM10400.NOTEINDX = A.NOTEINDX

    UNION ALL

    SELECT SeqNo = CASE WHEN dbo.PM10400.DocType IN ( 1, 2, 3 ) THEN 7

    WHEN dbo.PM10400.DocType IN ( 4, 5, 6, 7 ) THEN 8

    ELSE 3

    END,

    VendorID,

    PM10400.DocDate,

    DocAbrev AS TransType,

    RTRIM(PM10400.DocNumbr) AS DocNumbr,

    ISNULL(RTRIM(CM00100.DSCRIPTN), '') AS OtherDocNo,

    TrxDscrn AS Description,

    Debit = 0,

    Credit = 0,

    WriteOff = CASE WHEN DocAbrev IN ( 'PMT', 'RET' ) THEN WROFAMNT ELSE 0

    END,

    JournalEntry = 0,

    A.NOTEINDX,

    0 AS VOIDED,

    'Unposted' AS Status

    FROM dbo.PM10400

    INNER JOIN dbo.PM40102 ON dbo.PM10400.DocType = dbo.PM40102.DocType

    LEFT JOIN dbo.CM00100 ON PM10400.CHEKBKID = CM00100.CHEKBKID

    LEFT JOIN dbo.SY03900 A ON PM10400.NOTEINDX = A.NOTEINDX

     <----------------------------------------------------------------------------------------------------------- >

  • Dencio Profile Picture
    Dencio 2,172 on at
    Re: Re: Re: Re: vendor ledger report

    Hi Rashed,

    Ruel's SQL script is pretty good and will follow his advice when the requirements is to cover all Payables Transactions regardless of document's state (work, open, history).  Most reports only require listing outstanding amounts and personally will concentrate on PM20XXX series, please refer to GP SDK to aid you in identifying associated tables.

    I am also interested in Ian's follow up query on 'Missing vendor Accounts', if you are referring to payables posting account associated with the transactions, you can always link the same in GP Report Writer.

    Cheers,

    Dencio

  • Community Member Profile Picture
    Community Member on at
    Re: Re: Re: vendor ledger report

    HI, what do you mean when you say 'doesn't show the Vendor accounts' ?

    What accounts are you describing?

  • Community Member Profile Picture
    Community Member on at
    Re: Re: Re: vendor ledger report

    Rashed,

    Yes, it can certainly be done.

    You basically need to link the transaction file (PM20000) with the apply files (PM10200,PM20100).

    I am assuming you are familiar with SQL and I hope you can try the query below on the company DB:

    select PM20000.DOCDATE as INVDOCDATE,DOCNUMBR,DOCAMNT,a.* from PM20000 left join
    (select APTVCHNM,APFRDCNM,APTODCTY,APPLDAMT,DOCDATE from PM10200
    UNION ALL
    select APTVCHNM,APFRDCNM,APTODCTY,APPLDAMT,DOCDATE from PM20100) a
    on a.APTVCHNM=PM20000.VCHRNMBR and a.APTODCTY=PM20000.DOCTYPE
     

    You can package this into a stored procedure that has a date parameter that limits the records via a WHERE clause comparing the parameter with the INVDOCDATE and a.DOCDATE.

    Also, obviously you would need to group by DOCNUMBR (Invoice Number) on the report level as there would be a one invoice to many payments relationship. After that you can easily get the balance per invoice at the specified date simply by subtracting the APPLDAMTs from the DOCAMNT.

    Come to think of it, you probably also need to make another UNION with the historical transaction and apply tables (PM30200 and PM30300).

    Ruel

     

     

     

  • Rashed Alhakimi Profile Picture
    Rashed Alhakimi 2,095 on at
    Re: Re: vendor ledger report

    Dear All ,

    Thank you for your reply ,

    Dear Dencio , appriciates your help but the payables trial balance doesn't show the vendor accounts so i don't think it will work.

    but in case i am going to custom a report for the same do you think its easy to knew which tables i have to use and which columns .

    any help on that ..

     Thank you in advance


     

  • Dencio Profile Picture
    Dencio 2,172 on at
    Re: vendor ledger report

    Describing your exact requirements, I agree with Frank that this is a custom report using Crystal or SSRS.  Alternatively, Payables Trial Balance with Options (in detail, not summary) gives you out-of-box report that is essentially reporting  the same information.  Slight modification using Report Writer will allow sort the output differently and showing the current transaction amount (outstanding amount) will give you the balance of the invoice.

    Cheers,

    Dencio

  • Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,321 Super User 2025 Season 1 on at
    Re: vendor ledger report

    Yep - create a custom report in either SSRS or Crystal.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans