Custom Cost centers Report in SAP B1

[former_member209762]

Hello Gurus,

I am trying to build a custom cost centers report in SAP B1 9.1 version. I use JDT1, OOCR, OCR1 tables for direct DR and JDT1, OMDR and MDR1 fort indirect DR.

Here are the two queries used::

Direct DR

SELECT

T2.[Account],

T3.[AcctName],

SUM(T2.[Debit]-T2.[Credit]) [Account Balance],

CASE T2.[OcrCode2]

WHEN '10000000' THEN 'Site A'

WHEN '10010000' THEN 'Site B'

WHEN '10020000' THEN 'Site C'

END

AS [SITES],

CASE T2.[OcrCode4]

WHEN 'PRODB0001' THEN 'Product A'

WHEN 'PRODB0002' THEN 'Product B'

WHEN 'PRODB0003' THEN 'Product C'

WHEN 'PRODB0004' THEN 'Product D'

END

AS [PRODUCTS]

FROM OOCR T0 INNER JOIN OCR1 T1 ON T0.[OcrCode] = T1.[OcrCode] INNER JOIN JDT1 T2 ON (T0.[OcrCode] = T2.[ProfitCode] OR T0.[OcrCode]=T2.[OcrCode2] OR T0.[OcrCode]=T2.[OcrCode4]) INNER JOIN OACT T3 ON T2.[Account] = T3.[AcctCode] WHERE T2.[RefDate] >='01.01.2017' AND T2.[RefDate]<='31.12.2017' AND (ISNULL(T2.[OcrCode2],'')<>'' OR ISNULL(T2.[OcrCode2],'')<>'')

GROUP BY T2.[Account], T3.[AcctName], T2.[OcrCode2], T2.[OcrCode4], T2.TransId, T2.Debit, T2.Credit

HAVING SUM(T2.[Debit]-T2.[Credit])<>0

Indirect DR

SELECT

T1.[Account],

T3.[AcctName],

SUM(T1.[Debit]-T1.[Credit]) [Account Balance],

CASE T2.PrcCode

WHEN '10000000' THEN 'Site A'

WHEN '10010000' THEN 'Site B'

WHEN '10020000' THEN 'Site C'

WHEN 'PRODB0001' THEN 'Product A'

WHEN 'PRODB0002' THEN 'Product B'

WHEN 'PRODB0003' THEN 'Product C'

WHEN 'PRODB0004' THEN 'Product D'

END

AS [Activities/Products],

T2.PrcAmount [Amount Distributed]

FROM OMDR T0 INNER JOIN JDT1 T1 ON (T0.[OcrCode] = T1.[OcrCode2] OR T0.[OcrCode] = T1.[OcrCode4]) INNER JOIN MDR1 T2 ON T0.[OcrCode] = T2.OcrCode INNER JOIN OACT T3 ON T1.[Account] = T3.[AcctCode]

WHERE T1.[RefDate] >='01.01.2017' AND T1.[RefDate] <='31.12.2017' AND (ISNULL(T1.[OcrCode2],'')<>'' OR ISNULL(T1.[OcrCode2],'')<>'') GROUP BY T1.[Account], T3.[AcctName], T2.PrcCode, T2.PrcAmount

HAVING SUM(T1.[Debit]-T1.[Credit])<>0

What I would like is to display such a report using one single query.

Account AccountName AccountBalance CostCenter AmountDistributed

Please, any help would be appreciated.

Thanks in advance.

Isaac N.