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