web
You’re offline. This is a read only version of the page.
close
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 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,813

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

I have the same question (0)
  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    help please....

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

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

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    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,813 on at

    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

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    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

  • Kalpna K Profile Picture
    Kalpna K 30 on at

    Hi Rana,

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

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    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

    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.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Scott_itD Profile Picture

Scott_itD 2 Community Manager

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans