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.
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.
Suggest you check the standard reports of SAGE Accounting, there you find the relation within Crystal.
In particular this report "GLANA2"
Please can you describe how to navigate to the GLANA2 report. I am having troubles locating it.
Thank you
It is a standard report in the reports folder. You need to be a Crystal Reports user.
Abayomi Ojamomi Kindly see the accounting model table relation on this link: online-help.sageerpx3.com/.../Accounting_gif_1.htm
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'
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';
You are heaven sent. this worked perfectly. thanks a lot
Hi @Nzou 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
SELECT * FROM GACCENTRY WHERE CAT_0<5
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
*Community Hub is the new name for Sage City