COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_Umsätze_archieved_Service_Header.imr
TITLE,Service_Umsätze_archieved_Service_Header.imr
BEGIN SQL
select 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,
	   T2."EBENE3" as c14,
	   (substring(T2."EBENE3" from 1 for 4)) as c15,
	   CASE WHEN (((substring(T2."EBENE3" from 1 for 4))) BETWEEN '6010' AND '6140') THEN (((cast_float(T1."Amount")) * -1)) ELSE (0) END as c16,
	   CASE WHEN (((substring(T2."EBENE3" from 1 for 4))) BETWEEN '6220' AND '6270') THEN (((cast_float(T1."Amount")) * -1)) ELSE (0) END as c17,
	   T3."No_" 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 "DMS1"."dbo"."Automag GmbH$G_L Entry" T1,
	QSS."C:\GAPS_BMW\Portal\System\IQD\Service\GCStruct.ims" T2,
	"DMS1"."dbo"."Automag GmbH$Archived Service Header" T3
where (T1."G_L Account No_" = T2."KONTO_NR") and (T1."Document No_" = T3."No_")
and (((T1."Posting Date" >= TIMESTAMP '2019-01-01 00:00:00.000') and (((substring(T2."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."Description" IN ('GuV-Konten Nullstellung','GuV Konten Nullstellung')))

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,Ebene3
COLUMN,14,Zeile
COLUMN,15,Umsatz Lohn
COLUMN,16,Umsatz Sonstiges
COLUMN,17,Order No
COLUMN,18,Make Code
COLUMN,19,Marke