Journal Entry Data dump

 0 Replies
 0 Subscribed to this topic
 43 Subscribed to this forum
Sort:
Author
Messages
MarirajSankar
New Member Send Private Message
Posts: 1
New Member
Hi All,

I need query for a data dump of all the GL transactions for 2013 for company X and Y for the year end audit for 2013. I tried but got confused about tables. Here i used only two tables GLCONTROL and GLTRANS Please anyone help on this query.

select CONVERT(VARCHAR(4),GLC.COMPANY)+'-'+CONVERT(VARCHAR(4),GLC.FISCAL_YEAR)+'-'+CONVERT(VARCHAR(4),GLC.ACCT_PERIOD)
+'-'+CONVERT(VARCHAR(4),GLC.SYSTEM)+'-'+CONVERT(VARCHAR(4),GLC.JE_TYPE)+'-'+CONVERT(VARCHAR(4),GLC.CONTROL_GROUP)
as JE,CONVERT(VARCHAR(4),GLC.COMPANY)+'-'+CONVERT(VARCHAR(4),GLT.ACCOUNT)+'-'+CONVERT(VARCHAR(4),GLT.SUB_ACCOUNT)
AS GeneralLedgerAccountNumber,GLT.TRAN_AMOUNT AS AMOUNT,CONVERT(VARCHAR(4),GLC.COMPANY)+'-'+CONVERT(VARCHAR(6),GLT.ACCT_UNIT)AS BusinessUnit,
GLC.ACCT_PERIOD AS PERIOD,CAST(GLT.POSTING_DATE AS DATE) AS EntryDate,CAST(GLT.EFFECT_DATE AS DATE) AS EffectiveDate,
CONVERT(VARCHAR(4),GLC.SYSTEM)+'-'+CONVERT(VARCHAR(4),GLC.JE_TYPE) AS "Source",
GLT.DESCRIPTION AS JournalEntryDescription FROM chuprod.dbo.GLCONTROL GLC,chuprod.dbo.GLTRANS GLT
WHERE GLT.COMPANY =GLC.COMPANY
AND GLT.FISCAL_YEAR= GLC.FISCAL_YEAR
AND GLT.ACCT_PERIOD = GLC.ACCT_PERIOD
AND GLT.SYSTEM = GLC.SYSTEM
AND GLT.JE_TYPE = GLC.JE_TYPE
AND GLT.CONTROL_GROUP = GLC.CONTROL_GROUP
AND GLC.FISCAL_YEAR=2012
AND GLT.ACCT_PERIOD=1
AND GLT.CONTROL_GROUP=1
AND GLT.COMPANY IN (400)
ORDER BY GLC.COMPANY,GLC.FISCAL_YEAR,GLC.ACCT_PERIOD,GLC.SYSTEM,
GLC.JE_TYPE,GLC.CONTROL_GROUP


Thanks in Advance.

Regards,
Mariraj