Trying to find link between sales invoice and AA code for query

Hi all,

 

I need to create a report that will allow me to run a receivables trial balance by AA code (as they are classified on the sales invoice). I have been able to identify the table RM00103 to give me the outstanding balances in each aging category as a summary however I need the open transaction detail and am using table RM20101 for this. I cannot find however what table holds the information so that I can link the AA Trn dimension and Trn dimension code back to the document or invoice number.

Any ideas?

Regards,

Nathan

  • Hello,

    RM00103 is a Customer summary table. So you find the details of the balances per vendor and you don't find the transaction details here.

    To get the transaction details you have to locate the tables in RM Module.
    You can find the list of tables involved in RM here
    victoriayudin.com/.../

    The Analytical Accounting(AA) table structure is as follows
    AAG00200 - Account master
    AAG00400 - Transaction Dimension
    AAG00401 - Transaction Dimension Code

    For transaction created in GL
    AAG10000 GL Work Header
    AAG10001 GL Work Distribution
    AAG10002 GL Work Assign
    AAG10001 GL Work Code

    For transaction posted from sub ledgers RM,PM etc
    AAG20000 Sub Ledger Header
    AAG20001 Sub Ledger Work Distribution
    AAG20002 Sub Ledger Work Assign
    AAG20001 Sub Ledger Work Code

    For transaction posted from GL (Open Year)
    AAG30000 General Ledger Header
    AAG30001 General Ledger Work Distribution
    AAG30002 General Ledger Work Assign
    AAG30001 General Ledger Work Code

    For transaction posted from GL (History Year)
    AAG40000 General Ledger Header - History
    AAG40001 General Ledger Work Distribution - History
    AAG40002 General Ledger Work Assign - History
    AAG40001 General Ledger Work Code - History

    For more info analyse the info from below link
    community.dynamics.com/.../83049
  • In reply to guruk2k5:

    Hi Guru,

    So I've spent most of the day trying to get this to work with no luck still! I can get the transaction detail from RM20101 of which I can join the docnumber fields to the AAG2000 table for RM sub ledger. I cannot however find a join to the AAG00400 and AAG00401 tables so that i can return the Transaction dimension code.
  • In reply to nhardman:

    Hello,

    Can you share the code which you tried to achieve. I could help you in my free time.

    Thanks,
    Gururaj
  • In reply to guruk2k5:

    Hi Gururaj,

    Apologies as I'm no programmer, I have simply been using the Microsoft Query tool. I have made some progress however I cannot return the correct data. Here is the code;

    SELECT RM20101.CUSTNMBR, RM00101.CUSTNAME, RM20101.DOCNUMBR, RM20101.CHEKNMBR, RM20101.BACHNUMB, RM20101.BCHSOURC, RM20101.TRXSORCE, RM20101.DUEDATE, RM20101.DOCDATE, RM20101.POSTDATE, RM20101.PSTUSRID, RM20101.ORTRXAMT, RM00401.RMDTYPAL, AAG00400.aaTrxDim, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr
    FROM LIFES.dbo.AAG00400 AAG00400, LIFES.dbo.AAG00401 AAG00401, LIFES.dbo.AAG20000 AAG20000, LIFES.dbo.AAG20003 AAG20003, LIFES.dbo.RM00101 RM00101, LIFES.dbo.RM00401 RM00401, LIFES.dbo.RM20101 RM20101
    WHERE RM00101.CUSTNMBR = RM20101.CUSTNMBR AND RM00401.DOCNUMBR = RM20101.DOCNUMBR AND AAG00400.aaTrxDimID = AAG20003.aaTrxDimID AND AAG20000.aaSubLedgerHdrID = AAG20003.aaSubLedgerHdrID AND AAG20000.DOCNUMBR = RM00401.DOCNUMBR AND AAG20000.DOCNUMBR = RM20101.DOCNUMBR AND AAG00401.aaTrxDimID = AAG00400.aaTrxDimID AND AAG00401.aaTrxDimID = AAG20003.aaTrxDimID AND ((RM20101.CUSTNMBR='accn01'))

    Any light you could shed wold be greatly appreciated.

    Regards,

    Nathan
Related
Recommended