C11/System/ARIntelligence/IQD/Service/Service_Umsätze.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_Umsätze.imr
TITLE,Service_Umsätze.imr
BEGIN SQL
select distinct T1."G_L Account No_" as c1,
	   T1."Posting Date" as c2,
	   T1."Document Type" as c3,
	   T1."Document No_" as c4,
	   T1."Description" as c5,
	   (cast_float(T1."Amount")) * -1 as c6,
	   T1."Gen_ Bus_ Posting Group" as c7,
	   T1."Service Ledger Entry No_" as c8,
	   T1."Posting Date" as c9,
	   '1' as c10,
	   T1."Branch Code" as c11,
	   CASE WHEN ((T1."Branch Code" = 'LBS') or (T1."Branch Code" = '')) THEN ('10') WHEN (T1."Branch Code" = 'WLS') THEN ('20') ELSE null END as c12,
	   T1."Entry No_" as c13,
	   CASE WHEN (T2."Order No_" IS NULL) THEN ('FIBU ohne Auftrag') ELSE (T2."Order No_") END as c14,
	   T3."EBENE3" as c15,
	   (substring(T3."EBENE3" from 1 for 4)) as c16,
	   CASE WHEN (((substring(T3."EBENE3" from 1 for 4))) BETWEEN '6000' AND '6140') THEN (((cast_float(T1."Amount")) * -1)) ELSE (0) END as c17,
	   CASE WHEN (((substring(T3."EBENE3" from 1 for 4))) BETWEEN '6220' AND '6270') THEN (((cast_float(T1."Amount")) * -1)) ELSE (0) END as c18,
	   T1."Make Code" as c19,
	   CASE WHEN (T1."Make Code" IN ('BMW-MINI')) THEN (T1."Make Code") ELSE ('BMW') END as c20
from QSS."C:\GAPS_BMW\Portal\System\IQD\Service\GCStruct.ims" T3,
	("DMS1"."dbo"."Automag GmbH$G_L Entry" T1 left outer join "DMS1"."dbo"."Automag GmbH$Service Ledger Entry" T2 on (T1."Document No_" = T2."Document No_") and (T1."Gen_ Bus_ Posting Group" = T2."Gen_ Bus_ Posting Group"))
where (T1."G_L Account No_" = T3."KONTO_NR")
and ((((T1."Posting Date" >= TIMESTAMP '2019-01-01 00:00:00.000') and (((substring(T3."EBENE3" from 1 for 4))) IN ('6010','6020','6030','6036','6037','6038','6170','6180','6190','6090','6100','6110','6040','6130','6140','6150','6050','6060','6070','6270','6240','6220','6250','6200','6230','6000','6035','6076','6077','6078'))) and (not T1."Document No_" LIKE 'WA%')) and (not T1."Description" IN ('GuV-Konten Nullstellung','GuV Konten Nullstellung')))
order by c1 asc

END SQL
COLUMN,0,G L Account No
COLUMN,1,Posting Date
COLUMN,2,Document Type
COLUMN,3,Document No
COLUMN,4,Description
COLUMN,5,Amount
COLUMN,6,Gen Bus Posting Group
COLUMN,7,Service Ledger Entry No
COLUMN,8,Invoice Date
COLUMN,9,Hauptbetrieb
COLUMN,10,Branch Code
COLUMN,11,Standort
COLUMN,12,Entry No
COLUMN,13,Order No
COLUMN,14,Ebene3
COLUMN,15,Zeile
COLUMN,16,Umsatz Lohn
COLUMN,17,Umsatz Sonstiges
COLUMN,18,Make Code
COLUMN,19,Marke