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

Parents
  • 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

Reply Children
No Data