Accounting Tables on SAGE X3

Hi Guys,

Please how do I relate the tables GACCENTRY, GACCENTRYA and GACCENTRYD together in sql correctly.

I have been stuck trying to do this.

Top Replies

  • Suggest you check the standard reports of SAGE Accounting, there you find the relation within Crystal.

  • in reply to Martins de Almeida

    In particular this report "GLANA2"

  • in reply to Martins de Almeida

    Please can you describe how to navigate to the GLANA2 report. I am having troubles locating it.

    Thank you

  • in reply to Abayomi Ojamomi

    It is a standard report in the reports folder. You need to be a Crystal Reports user.

  • Kindly see the accounting model table relation on this link: online-help.sageerpx3.com/.../Accounting_gif_1.htm

  • in reply to Liberty.petja

    Thanks a lot Liberty. Actually i am trying to get the sql queries that handles the joins across these entities. I will appreciate if you could assist in that regards also.

    Thank you

  • SELECT
    D.TYP_0,
    E.NUM_0,
    D.DES_0,
    D.LEDTYP_0,
    D.LIN_0,
    A.ANALIN_0,
    D.ACC_0,
    AMTCUR_0=ISNULL(A.AMTCUR_0,D.AMTCUR_0),
    AMTLED_0=ISNULL(A.AMTLED_0,D.AMTLED_0),
    A.CCE_0,
    A.CCE_1,
    A.CCE_2,
    D.SNS_0
    FROM SEED.GACCENTRY E
    INNER JOIN SEED.GACCENTRYD D ON E.NUM_0=D.NUM_0
    LEFT OUTER JOIN SEED.GACCENTRYA A ON D.NUM_0=A.NUM_0 AND D.LIN_0=A.LIN_0 and D.LEDTYP_0=A.LEDTYP_0 AND D.TYP_0=A.TYP_0
    WHERE D.LEDTYP_0=1 And E.NUM_0='ATC-ES01213-000001'

  • in reply to Nzou

    Thanks a lot for this.  But can I join GACCOUNT table to this query directly to get the dimension account name itself. The below query is what i wrote using the previous SAGE Evolution tables. Now i want to replicate same logic using these new tables. I understand how the Analytical dimensioning works but i just done want to miss out any important field in the joins

    SELECT a.*
    ,CASE
    WHEN b.account LIKE '40%'
    THEN total_credit - total_debit
    WHEN b.account LIKE '41%'
    THEN total_credit - total_debit
    WHEN b.account LIKE '50%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '51%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '52%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '53%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '54%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '45%'
    THEN total_credit - total_debit
    WHEN b.account LIKE '6%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '7%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '8%'
    THEN total_debit - total_credit
    WHEN b.account LIKE '9%'
    THEN total_debit - total_credit
    ELSE 0
    END AS net_amount
    ,b.account
    ,CASE
    WHEN b.account LIKE '40%'
    THEN 'Revenue'
    WHEN b.account LIKE '41%'
    THEN 'Revenue'
    WHEN b.account LIKE '50%'
    THEN 'Cost of Sales'
    WHEN b.account LIKE '51%'
    THEN 'Cost of Sales'
    WHEN b.account LIKE '52%'
    THEN 'Cost of Sales'
    WHEN b.account LIKE '53%'
    THEN 'Cost of Sales'
    WHEN b.account LIKE '54%'
    THEN 'Cost of Sales'
    WHEN b.account LIKE '45%'
    THEN 'Other Income'
    WHEN b.account LIKE '6%'
    THEN 'Other Expense'
    WHEN b.account LIKE '7%'
    THEN 'Other Expense'
    WHEN b.account LIKE '8%'
    THEN 'Other Expense'
    WHEN b.account LIKE '9%'
    THEN 'Other Expense'
    ELSE 'Unknown'
    END AS category
    ,b.description
    FROM (
    SELECT TxDate
    ,accountlink
    ,trcodeid
    ,sum(debit) AS total_debit
    ,sum(credit) AS total_credit
    ,drcraccount
    ,project
    --sum(credit) - sum(debit) as net_amount
    FROM PostGL
    WHERE txdate >= '2020-01-01'
    --and AccountLink like '40%'
    GROUP BY TxDate
    ,AccountLink
    ,TrCodeID
    ,DrCrAccount
    ,Project
    ) a
    LEFT JOIN Accounts b ON a.accountlink = b.accountlink
    WHERE b.account BETWEEN '4000'
    AND '9998';

  • in reply to Nzou

    You are heaven sent. this worked perfectly. thanks a lot

  • in reply to Nzou

    Hi @ Please I need your advise again. I noticed that the templates journal entries are are being stored on the GACCENTRYD  table. These entries are not usually recognized when we are computing our reports as they are templates and hence it introduces duplicates into the records. Please how can i implement this exclusion from my query as its the only thing affecting my result. Thank you

  • in reply to Abayomi Ojamomi

    SELECT * FROM GACCENTRY WHERE CAT_0<5

  • in reply to Nzou

    Thanks a lot. 

  • in reply to Abayomi Ojamomi

    Hi Abayomi

    We see that the provided answer helped, please mark it as verified (by clicking the "more button on the response) for the benefit of others in the forum.

    Thank you!

    If you have a minute, tell us about your great experience at Sage City, take this short 3 questions survey: take this short survey