select
gje.SUBLEDGERVOUCHER "Voucher",
gje.ACCOUNTINGDATE,
m.MAINACCOUNTID "Account",
m.NAME "AccountName",
gjae.TEXT as Description,CHARINDEX(0x02, CAST(TEXT AS varbinary(30))) Position,
gjae.*
from GENERALJOURNALENTRY gje
inner join ledger com on gje.LEDGER = com.RECID --and gje.[PARTITION] = com.[PARTITION]
inner join GENERALJOURNALACCOUNTENTRY gjae on gje.RECID = gjae.GENERALJOURNALENTRY --and gje.[PARTITION] = gjae.[PARTITION]
inner join DIMENSIONATTRIBUTEVALUECOMBINATION davc on gjae.LEDGERDIMENSION = davc.RECID --and gje.[PARTITION] = davc.[PARTITION]
inner join MAINACCOUNT m on davc.MAINACCOUNT = m.RECID --and davc.[PARTITION] = m.[PARTITION]
inner join [FISCALCALENDARPERIOD] FCP ON gje.[FISCALCALENDARPERIOD] = FCP.[RECID] --and gje.[PARTITION] = FCP.[PARTITION]
inner join [FISCALCALENDARYEAR] FCY ON gje.FISCALCALENDARYEAR = FCY.RECID and gje.[PARTITION] = FCY.[PARTITION] -- AND FCY.FISCALCALENDAR = FCP.FISCALCALENDAR
left join MainAccountCategory mc ON mc.ACCOUNTCATEGORYREF = m.ACCOUNTCATEGORYREF --and mc.[PARTITION] = m.[PARTITION]
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalvd on davc.recid= dalvd.VALUECOMBINATIONRECID and dalvd.DIMENSIONATTRIBUTE = 5637144826
left join [DIMENSIONATTRIBUTEVALUE] dav on dav.DIMENSIONATTRIBUTE = dalvd.DIMENSIONATTRIBUTE and dav.ENTITYINSTANCE = dalvd.ENTITYINSTANCE
left join [DIMATTRIBUTEOMDEPARTMENT] daod on -- dalvd.DISPLAYVALUE = daod.VALUE
daod.[KEY_] = dav.ENTITYINSTANCE
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalvf on davc.recid= dalvf.VALUECOMBINATIONRECID and dalvf.DIMENSIONATTRIBUTE = 5637145326
left join [DIMATTRTRANSLFINANCIALTAG] daftf on dalvf.DISPLAYVALUE = daftf.VALUE and daftf.CATEGORY = 5637144577 --and daftf.LANGUAGEID = 'en-us'
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalvp on davc.recid= dalvp.VALUECOMBINATIONRECID and dalvp.DIMENSIONATTRIBUTE = 5637145327
left join [DIMATTRTRANSLFINANCIALTAG] daftp on dalvp.DISPLAYVALUE = daftp.VALUE and daftp.CATEGORY = 5637144576 --and daftp.LANGUAGEID = 'en-us'
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalve on davc.recid= dalve.VALUECOMBINATIONRECID and dalve.DIMENSIONATTRIBUTE = 5637145576
left join SRSANALYSISENUMS enumLPT ON enumLPT.ENUMNAME = 'LedgerPostingType' AND enumLPT.ENUMITEMVALUE = gjae.POSTINGTYPE
left join subledgerVoucherGeneralJournalEntry svgje ON svgje.GENERALJOURNALENTRY = gje.RECID AND svgje.VOUCHERDATAAREAID = gje.SUBLEDGERVOUCHERDATAAREAID AND svgje.VOUCHER = gje.SUBLEDGERVOUCHER
where
gje.ACCOUNTINGDATE BETWEEN '08/01/2020' AND '12/31/2020'
and com.NAME in ('EF')
--and CHARINDEX(0x02, CAST(TEXT AS varbinary(30))) > 0 --Add this line to discover errors.
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.