GL Query For Posted and Unposted GL Transactions

View Trial Balance for Posted and Unposted GL Transactions:

SELECT
'HISTORY' POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL30000.JRNENTRY,
DBO.GL30000.ACTINDX,
DBO.GL30000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL30000.XCHGRATE END AS ORDBTAMT,
CASE WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL30000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL30000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL30000.XCHGRATE END AS ORCRDAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
DBO.DTA10100.TRXDATE
FROM DBO.GL30000
INNER JOIN DBO.DTA10100 ON DBO.GL30000.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL30000.JRNENTRY = DBO.DTA10100.JRNENTRY
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID
INNER JOIN DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX ON DBO.GL30000.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID


UNION ALL

SELECT
'POSTED' AS POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL10001.JRNENTRY,
DBO.GL10001.ACTINDX,
DBO.GL10000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL10001.XCHGRATE END AS ORCRDAMT,
CASE WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL10001.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL10001.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL10001.XCHGRATE END AS ORDBTAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
GL10000.TRXDATE
FROM DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.GL10001 ON DBO.GL00105.ACTINDX = DBO.GL10001.ACTINDX
INNER JOIN DBO.DTA10100
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID AND DBO.DTA10100.DOCNUMBR = DBO.DTA10200.DOCNUMBR AND DBO.DTA10100.RMDTYPAL = DBO.DTA10200.RMDTYPAL ON DBO.GL10001.JRNENTRY = DBO.DTA10100.JRNENTRY AND DBO.GL10001.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL10001.SQNCLINE = DBO.DTA10100.SEQNUMBR
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID
INNER JOIN DBO.GL10000 ON DBO.GL10001.JRNENTRY = DBO.GL10000.JRNENTRY

/*WHERE DBO.GL10000.TRXDATE BETWEEN '2006/6/10' AND '2007/6/10' AND DBO.GL00100.ACTINDX IN (5)"*/

UNION ALL

SELECT
'UNPOSTED' POSTINGSTATUS,
DBO.GL00100.ACTDESCR,
DBO.GL00105.ACTNUMST,
DBO.GL20000.JRNENTRY,
DBO.GL20000.ACTINDX,
DBO.GL20000.REFRENCE,
CASE WHEN DBO.DTA10200.CODEAMT>=0 THEN 0 WHEN DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT END AS CRDTAMNT,
CASE WHEN DBO.DTA10200.CODEAMT<=0 THEN 0 WHEN DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT END AS DEBITAMT,
DBO.DTA10200.CODEAMT AS BALANCE,
CASE
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<=0 THEN 0
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>0 THEN DBO.DTA10200.CODEAMT / DBO.GL20000.XCHGRATE END AS ORDBTAMT,
CASE WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL20000.XCHGRATE =0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT>=0 THEN 0
WHEN DBO.GL20000.XCHGRATE <>0 AND DBO.DTA10200.CODEAMT<0 THEN DBO.DTA10200.CODEAMT / DBO.GL20000.XCHGRATE END AS ORCRDAMT,
DBO.DTA10200.CODEID,
DBO.DTA10100.GROUPID,
DBO.DTA10200.POSTDESC,
DBO.DTA00200.CODEDESC,
DBO.GL00100.TPCLBLNC,
DBO.DTA10100.TRXDATE
FROM DBO.GL20000
INNER JOIN DBO.DTA10100 ON DBO.GL20000.ACTINDX = DBO.DTA10100.ACTINDX AND DBO.GL20000.JRNENTRY = DBO.DTA10100.JRNENTRY
INNER JOIN DBO.DTA10200 ON DBO.DTA10100.DTASERIES = DBO.DTA10200.DTASERIES AND DBO.DTA10100.DTAREF = DBO.DTA10200.DTAREF AND DBO.DTA10100.ACTINDX = DBO.DTA10200.ACTINDX AND DBO.DTA10100.SEQNUMBR = DBO.DTA10200.SEQNUMBR AND DBO.DTA10100.GROUPID = DBO.DTA10200.GROUPID
INNER JOIN DBO.GL00100
INNER JOIN DBO.GL00105 ON DBO.GL00100.ACTINDX = DBO.GL00105.ACTINDX ON DBO.GL20000.ACTINDX = DBO.GL00105.ACTINDX
INNER JOIN DBO.DTA00200 ON DBO.DTA10200.CODEID = DBO.DTA00200.CODEID

/* WHERE DBO.GL20000.TRXDATE BETWEEN '2006/6/10' AND '2007/6/10' AND DBO.GL20000.ACTINDX IN (5) */


Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Dynamics Innovations
daoudm@dynamicsinnovations.com
http://www.dynamicsinnovations.com/