Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to get all Purchas...
Microsoft Dynamics AX (Archived)

How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

(0) ShareShare
ReportReport
Posted on by Rana Anees 1,811

Hii,

What tables are involved and relation among these entities...

Vendors -> List of Purchase Orders -> All Invoices on a Purchase Order | Prepayments -> Invoice Settled / Unsettled.

We want to get a vendor and its purchase orders and invoices on PO and invoices settled or open in one temp table to show on SSRS report. 

How we can get above results in a query or x++ and what tables should be joined.

Thanks,

*This post is locked for comments

  • Kalpna K Profile Picture
    Kalpna K 30 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    Hi ,

    Thanks for revert.

    I am still looking help on this .

    I am unable to establish relations for finding all GL accounts and PO information.

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    Hello Rana Anees,

    Is there any update on this issue?

    Would be great if you could let us know whether you have been able to get this issue resolved in the meantime or whether you still need some help from the community.

    Many thanks and best regards,

    Ludwig

  • Kalpna K Profile Picture
    Kalpna K 30 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    Hi Rana,

    if you were able to generate this report can you please share what relations you chose?

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    Actually on the report I want to show.

    Vendor ID | Name | Invoice | PO       | PO Amount | Currency Code | Amount | INV | Balance

    3040           ABC      INV-22   PO- 98 |  1000             | USD                 |  500       | 0     |  500

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    Hii Chaitanya Golla,

    Your reply is helpful. But what I am struggling for is to get prepayments on a purchase order.

    Means to get all purchase orders that have prepayments, invoices and payments for prepayments and final settlements for vendors.

    Struggling in writing a query....I have another post regarding this topic....if you could help... community.dynamics.com/.../237465

    Thanks

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    Hi Rana,

    As per my understanding, you want to fetch data for all invoiced PO's for a given vendor.

    On UI, it's visible on vendor transactions form:

    Settled transactions will have balance column zero value and unsettled transactions will show some value(generally negative).

    For a selected transaction, when clicked on history button, vendor settlement form gets opened with details on settlement tab and transactions tab.

    Tables that can be joined to get the data:

    VENDTRANS & VENDSETTLEMENT:

    vendTrans.Voucher is the settlment voucher

    vendSettlement.OffsetTransVoucher is the invoice voucher

    VendTrans.Invoice -- invoice number

    vendTrans.dataAreaId == vendSettlement.TransCompany
    vendTrans.RecId == vendSettlement.TransRecId
    VendTrans.AccountNum == VendSettlement.AccountNum

    VendTrans.Transtype == payment

    GENERAL JOURNAL POSTINGS:

    SubledgerVoucherGeneralJournalEntry.AccountIngDate == vendTrans.TransDate

    SubledgerVoucherGeneralJournalEntry.Voucher == vendTrans.voucher
    SubledgerVoucherGeneralJournalEntry.VoucherDataAreaId == vendTrans.dataAreaId SubledgerVoucherGeneralJournalEntry.GeneralJournalEntry == GeneralJournalEntry.RecId

    GeneralJournalAccountEntry.GeneralJournalEntry == GeneralJournalEntry.RecId

    Hope this information is useful.

    Thanks,

    Chaitanya Golla

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    any idea or alternative to get the required results... Thanks

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: How to get all Purchase Orders, invoices, prepayments, settled or open for a vendor in a Query? AX 2012 R2

    help please....

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,602 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,340 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans