PO line detail on General Ledger table (LedgerTrans)

Hi

I drive the majority of my reporting using ODBC to extract data from SQL.  I am really struggling however to get line item detail from purchase orders onto the my general ledger transactions, when a delivery note (picking list) is posted.

Has anyone experienced this / knows how to link this data?

Many thanks

Ade

  • Adrian Worrall
    I am really struggling however to get line item detail from purchase orders onto the my general ledger transactions, when a delivery note (picking list) is posted

    So you have a field ItemId on the ledgerTrans and you want to update it from purchase order line? If so why cannot use the financial dimensions for that purpose? If that is not the requirement, please elaborate. 

  • In reply to Kranthi:

    Thanks for the reply

    So say I have a packing slip transaction for eg below. What I need is the detail / text from the line that this packing slip related to on the purchase order! I've tried trimming the last 8 digits from the text but this obviously only gives me a link to the PO header..

    Voucher = INR002488 Text = Invoice 043-277542; AP 2100473; PO 46020063
  • In reply to Adrian Worrall:

    Adrian Worrall
    Voucher = INR002488 Text = Invoice 043-277542; AP 2100473; PO 46020063

    So you trying to retrieve data from AX? How you are getting that text?

    To go into PO line you need to know the InventTransId of that line.

  • In reply to Kranthi:

    Hi - I'm getting that straight out of the LedgerTrans table, using ODBC to rip it directly out of SQL. The trouble is that the LedgerTrans table doesn't hold the InventTransID. Do you know how I can get that relationship. The accountants like to be able to drill down on a specific GL code to see what makes up their spend for eg
  • In reply to Adrian Worrall:

    There could be multiple items per single voucher.
    You can look into vendPackingSlipJour by using from there you can get vendPackingSlipTrans (this has inventTransId)
    select vendPackingSlipJour
    where vendPackingSlipJour.LedgerVoucher == ledgerTrans.Voucher &&
    vendPackingSlipJour.DeliveryDate == ledgerTrans.TransDate
    (or) you can directly look into inventTrans and get inventTransId's
    select inventTrans
    where inventTrans.VoucherPhysical == ledgerTrans.Voucher &&
    inventTrans.DatePhysical== ledgerTrans.TransDate
  • In reply to Kranthi:

    I will give that a try and let you know if it works.

    Thank you very much for your help, it is very much appreciated
Related
Recommended