Thanks for your response. I want to have all the transactions and I verified and the fields SubledgerVoucher and SubledgerVoucherDataAreaID some of them are blank too. I am being a bit greedy but can you Tell me the query for the correct joining from the SubledgerTrans table and to avoid data gap and for the field ledger link . As I earlier told you i want these data for auditors which company gives them for their verification and all. This is the Query I prepared and most of them matches but i think i m getting extra date (select GJE.SUBLEDGERVOUCHER,dc.DISPLAYVALUE as LEDGERACCOUNT,m.MAINACCOUNTID,m.NAME ,gje.ACCOUNTINGDATE as ACCOUNTINGDATE, GJAE.REPORTINGCURRENCYAMOUNT,
com.NAME as SUBLEDGERVOUCHERDATAAREAID,
GJAE.POSTINGTYPE,
CASE
WHEN GJAE.POSTINGTYPE = 3 THEN 'Intercompany Accounting'
WHEN GJAE.POSTINGTYPE = 4 THEN 'Tax'
WHEN GJAE.POSTINGTYPE = 14 THEN 'Ledger Journal'
WHEN GJAE.POSTINGTYPE = 20 THEN 'Bank'
WHEN GJAE.POSTINGTYPE = 31 THEN 'Customer Balance'
WHEN GJAE.POSTINGTYPE = 41 THEN 'Vendor Balance'
WHEN GJAE.POSTINGTYPE = 45 THEN 'Vendor Cash Discount'
WHEN GJAE.POSTINGTYPE = 48 THEN 'Vendor Settlement'
WHEN GJAE.POSTINGTYPE = 49 THEN 'InterCompanySettlement'
WHEN GJAE.POSTINGTYPE = 55 THEN 'Order,Freight'
WHEN GJAE.POSTINGTYPE = 56 THEN 'Order Fee'
WHEN GJAE.POSTINGTYPE = 71 THEN 'Purchase Expenditure For Product'
WHEN GJAE.POSTINGTYPE = 72 THEN 'Purchase,Discount'
WHEN GJAE.POSTINGTYPE = 83 THEN 'Purchase Expenditure,Un-inoviced'
WHEN GJAE.POSTINGTYPE = 121 THEN 'Project-cost'
WHEN GJAE.POSTINGTYPE = 123 THEN 'Project-WIPCost'
WHEN GJAE.POSTINGTYPE = 201 THEN 'Purchase -charge'
WHEN GJAE.POSTINGTYPE = 203 THEN 'Purchase-accural'
END AS POSTINGTYPE,
GJAE.TEXT as Description,
gjae.ACCOUNTINGCURRENCYAMOUNT
,GJAE.TRANSACTIONCURRENCYCODE, GJAE.TRANSACTIONCURRENCYAMOUNT,
VT.ACCOUNTNUM as VendorAccount,
DPT.NAME as VendorName
from GENERALJOURNALENTRY gje
inner join GENERALJOURNALACCOUNTENTRY gjae on gje.RECID = gjae.GENERALJOURNALENTRY
inner join DIMENSIONATTRIBUTEVALUECOMBINATION dc on gjae.LEDGERDIMENSION = dc.RECID
inner join MAINACCOUNT m on dc.MAINACCOUNT = m.RECID
inner join ledger com on gje.LEDGER = com.RECID
left outer join VENDTRANS VTR on GJE.DOCUMENTDATE = VTR.DOCUMENTDATE and GJE.SUBLEDGERVOUCHER = VTR.VOUCHER
left outer join VENDTABLE VT on VTR.ACCOUNTNUM = VT.ACCOUNTNUM
Join DIRPARTYTABLE DPT on DPT.RECID = VT.PARTY
left outer join DIMENSIONATTRIBUTELEVELVALUEVIEW dcv on dc.recid= dcv.VALUECOMBINATIONRECID --and DIMENSIONATTRIBUTE = '5637146076'
where GJE.SUBLEDGERVOUCHERDATAAREAID='PLA'and (gje.ACCOUNTINGDATE >= '2023-01-01 00:00:00.000' and gje.ACCOUNTINGDATE <= '2023-10-31 00:00:00.000')
order by GJE.ACCOUNTINGDATE
)
