I am trying to create a report that shows all transactions posted to all accounts in specific period. Because I need detailed information from each module I am picking up the same information from PI/PIC/SI/SIC and linking it JE transactions. I then excluding the 4 tranasctions types from JE to avoid duplications.

 

Looks like it is working but PI/PIC/SI/SIC and their JE quivelent transactions posted to control accounts are completley omitted. 

 

I am not sure why my control accounts do not any PI/PIC/SI/SIC tranactions or their JE equivelent.

 

 

####

SELECT 'JE ' + RTRIM(c.TransId) AS DocNum, c.RefDate, c.Account, c.Debit-c.Credit AS LineTotal, c.VatAmount, c.FCDebit-c.FCCredit AS FCLineTotal, c.SYSDeb-c.SYSCred AS SysTotal, c.FCCurrency AS Currency, c.LineMemo AS RowDescription,c.TransType,  d.Ref2, d.Memo AS JournalRemarks, d.TaxDate,
c.TransId, c.FinncPriod, f.Year, e.SubNum, e.Name, e.F_RefDate, e.T_RefDate, f.F_RefDate as F_YDate, f.T_RefDate as T_YDate,
g.AcctName, g.ActType,g.AcctCode, g.FatherNum, g.Levels,g.GrpLine, g.GroupMask, g.AccntntCod, h.AcctName AS Father, OADM.CompnyName, OADM.MainCurncy, OADM.SysCurrncy, c.ProfitCode, b.OcrName,   ISNULL(k.DimCode,0) DimCode, ISNULL(k.DimDesc,'Blank') DimDesc
From JDT1 c
INNER JOIN OJDT d on c.TransId = d.TransId
INNER JOIN OFPR e on e.AbsEntry=c.FinncPriod
INNER JOIN OACP f on f.PeriodCat=e.Category
INNER JOIN OACT g on g.AcctCode=c.Account
INNER JOIN OACT h on h.AcctCode=g.FatherNum
LEFT OUTER JOIN OOCR b ON c.ProfitCode=b.OcrCode LEFT OUTER JOIN OCR1 j ON j.OcrCode=b.OcrCode LEFT OUTER JOIN OPRC i ON i.PrcCode = j.PrcCode LEFT OUTER JOIN ODIM k ON b.DimCode=k.DimCode, OADM
WHERE d.TransTYpe not in (13,14,18,19,-3)

UNION ALL
SELECT 'IN ' + RTRIM(d.DocNum) AS DocNum, c.DocDate, c.AcctCode, (c.LineTotal)*-1 AS LineTotal,0,  (c.TotalFrgn)*-1 AS FCGrossLineTotal, (c.TotalSumSy)*-1 AS SysGrossTotal, d.DocCur AS Currency, c.Dscription AS RowDescription,0, d.NumAtCard AS Ref2, d.JrnlMemo AS JournalRemarks, d.TaxDate,
d.TransId, c.FinncPriod, f.Year, e.SubNum, e.Name, e.F_RefDate, e.T_RefDate, f.F_RefDate as F_YDate, f.T_RefDate as T_YDate,
g.AcctName, g.ActType,g.AcctCode, g.FatherNum, g.Levels,g.GrpLine, g.GroupMask, g.AccntntCod, h.AcctName AS Father, OADM.CompnyName, OADM.MainCurncy, OADM.SysCurrncy, c.OcrCode, b.OcrName, ISNULL(k.DimCode,0) DimCode, ISNULL(k.DimDesc,'Blank') DimDesc
From INV1 c
INNER JOIN OINV d on d.DocEntry = c.DocEntry
INNER JOIN OFPR e on e.AbsEntry=c.FinncPriod
INNER JOIN OACP f on f.PeriodCat=e.Category
INNER JOIN OACT g on g.AcctCode=c.AcctCode
INNER JOIN OACT h on h.AcctCode=g.FatherNum
LEFT OUTER JOIN OOCR b ON c.OcrCode=b.OcrCode LEFT OUTER JOIN OCR1 j ON j.OcrCode=b.OcrCode LEFT OUTER JOIN OPRC i ON i.PrcCode = j.PrcCode LEFT OUTER JOIN ODIM k ON b.DimCode=k.DimCode, OADM

 

UNION

(3 OTHER MODULES).....

 

 

 

Many thanks for your help