Announcements
Hey there.
We're working towards extracting the transaction data out of Dynamics SL to be used in a Oracle Hyperion.
I've successfully written the query out of Dynamics to give me a list of all the Transactions - no problem. This data matches the transactional data coming out of Dynamics/Trial Balance - win.
Hyperion would prefer to have the "Net" value of each transaction, not separate columns for Debit/Credit.
Is there a way to know if an account is normally a debit or credit account such that I can calculate the "Net" value of the transaction?
Do you have any guidance in this area? - Or have some sample SQL code to help?
Would appreciate any direction you may have.
Thank you.
Mike
An easier way to write the case may be like this:
select t.acct, a.accttype, t.perpost, t.dramt, t.cramt,
CASE WHEN a.AcctType LIKE '_[AE]' THEN t.DrAmt - t.CrAMt ELSE t.CrAmt - t.DrAmt END as 'Net'
from gltran t
join account a on a.acct = t.acct
cross join glsetup s
where t.LedgerID = s.LedgerID AND Posted = 'P'
use SUM() and group by if you don't need all the detail lines, but just the net number by account, period, etc.
Easy script:
CASE WHEN GLTran.CrAmt <> '0' THEN (GLTran.CrAmt *-1)
WHEN GLTran.DrAmt <> '0' THEN (GLTran.DrAmt)
ELSE '0' END AS [Natural Amt DR(CR)]
More detailed based on Marc D's reply:
A. = Account table
G. = GLTran table
CASE WHEN A.AcctType in ('1A','4E') and G.DrAmt > '0' then (G.DrAmt)
WHEN A.AcctType in ('1A','4E') and G.DrAmt < '0' then (G.DrAmt)
WHEN A.AcctType in ('2L','3I') and G.DrAmt > '0' then (G.DrAmt)
WHEN A.AcctType in ('2L','3I') and G.DrAmt < '0' then (G.DrAmt)
WHEN A.AcctType in ('1A','4E') and G.CrAmt > '0' then (G.CrAmt * -1)
WHEN A.AcctType in ('1A','4E') and G.CrAmt < '0' then (G.CrAmt * -1)
WHEN A.AcctType in ('2L','3I') and G.CrAmt > '0' then (G.CrAmt * -1)
WHEN A.AcctType in ('2L','3I') and G.CrAmt < '0' then (G.CrAmt * -1)
ELSE '0' END AS [Natural Amount DR(CR)],
join to account table to use accttype in case statement
Essentially, this is the expected output:
1) Assets - Debits positives, Credits Negative
2) Liabilites – Debits negatives, Credits positives
3) Revenues – Debits negatives, Credits positives
4) Expenses – Debits positives, credits negatives
5) Equity – Debits negatives, credits positives
André Arnaud de Cal... 291,359 Super User 2024 Season 2
Martin Dráb 230,370 Most Valuable Professional
nmaenpaa 101,156