Finance | Project Operations, Human Resources, ...

Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

(0) Share

Report

Posted on by Magnified

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

All responses (4)

Answers (0)

MARC_ 620 on at

Like (0)

Report

RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

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.
Suggested answer

John_Joyce 1,040 on at

Like (0)

Report

RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

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)],
MARC_ 620 on at

Like (0)

Report

RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

join to account table to use accttype in case statement
Magnified 35 on at

Like (0)

Report

RE: Dynamics SL - SQL Extraction - "Net Value" of Transactional Data

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