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

I have the same question (0)

All responses (4)

Answers (0)

Magnified 35 on at

Like (0)

Report

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

Was this reply helpful? Yes No
MARC_ 626 on at

Like (0)

Report

join to account table to use accttype in case statement

Was this reply helpful? Yes No
Suggested answer

John_Joyce 1,042 on at

Like (0)

Report

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)],

Was this reply helpful? Yes No
MARC_ 626 on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates