C11/System/ARI/IQD/Belege/Belege.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\System\ARI\IQD\Belege\Belege.imr
TITLE,Belege.imr
BEGIN SQL
select T1."No_" as c1,
	   T1."Name" as c2,
	   T1."Account Type" as c3,
	   T1."Income_Balance" as c4,
	   T1."Last Date Modified" as c5,
	   T2."Entry No_" as c6,
	   T2."G_L Account No_" as c7,
	   T2."Posting Date" as c8,
	   T2."Document Type" as c9,
	   T2."Document No_" as c10,
	   T2."Description" as c11,
	   T2."Bal_ Account No_" as c12,
	   T2."Global Dimension 1 Code" as c13,
	   T2."Global Dimension 2 Code" as c14,
	   T2."User ID" as c15,
	   T2."Source Code" as c16,
	   CASE WHEN ((T2."Document No_" LIKE 'VRGGFZ%') or (T2."Document No_" LIKE 'VRGF%')) THEN (T2."Veh_ Source Code") WHEN (((T2."Document No_" LIKE 'VGUGFZ%') or (T2."Document No_" LIKE 'VGGF%')) and (T2."Veh_ Source Code" <> 0)) THEN (-1) ELSE (0) END as c17,
	   T2."Reason Code" as c18,
	   T2."Gen_ Posting Type" as c19,
	   T2."Gen_ Bus_ Posting Group" as c20,
	   T2."Gen_ Prod_ Posting Group" as c21,
	   T2."Bal_ Account Type" as c22,
	   T2."Transaction No_" as c23,
	   T2."Document Date" as c24,
	   T2."External Document No_" as c25,
	   T2."Source Type" as c26,
	   T2."Source No_" as c27,
	   T2."No_ Series" as c28,
	   T2."Branch Code" as c29,
	   T2."Main Area" as c30,
	   T2."VIN" as c31,
	   T2."Book No_" as c32,
	   T2."Veh_ Source Code" as c33,
	   T2."Reposted" as c34,
	   T2."Corrected" as c35,
	   T2."Correction to curr_ No_" as c36,
	   T2."Reposting to curr_ No_" as c37,
	   T3."Code" as c38,
	   T3."Name" as c39,
	   T2."Posting Date" as c40,
	   CASE WHEN (T2."Branch Code" IN ('01BSPKW')) THEN ('10') WHEN (T2."Branch Code" IN ('02BSMOT')) THEN ('20') WHEN (T2."Branch Code" IN ('03RHF')) THEN ('30') WHEN (T2."Branch Code" IN ('04SFH')) THEN ('40') WHEN (T2."Branch Code" IN ('05WT')) THEN ('50') WHEN (T2."Branch Code" IN ('06BI')) THEN ('60') WHEN (T2."Branch Code" IN ('07TR')) THEN ('70') ELSE ((od_left(T2."Global Dimension 1 Code",2))) END as c41,
	   T2."G_L Account No_" as c42,
	   ((cast_float(T2."Amount"))) as c43,
	   (cast_float(T2."Amount")) as c44,
	   CASE WHEN ((extract(DAY FROM (now()) - T2."Posting Date")) <= 120) THEN (T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."User ID") ELSE ('Belege älter 120 Tage') END as c45,
	   '1' as c46,
	   CASE WHEN (T2."Global Dimension 2 Code" IN ('BMW','BMW I','BMW-C1','BMWI','BMW-MINI','BMW-MOT','BMWVW')) THEN (T2."Global Dimension 2 Code") ELSE ('Fremdfabrikat') END as c47
from "Gottstein7x"."dbo"."AH Gottstein$G_L Account" T1,
	("Gottstein7x"."dbo"."AH Gottstein$G_L Entry" T2 left outer join "Gottstein7x"."dbo"."AH Gottstein$Department" T3 on T2."Global Dimension 1 Code" = T3."Code")
where (T1."No_" = T2."G_L Account No_")
and ((((T1."Income_Balance" = 0) and (not T2."Description" IN ('GuV-Konten Nullstellung','GuV Konten Nullstellung'))) and (T2."Posting Date" >= TIMESTAMP '2020-01-01 00:00:00.000')) and ((od_year(T2."Posting Date")) <= (od_year((now())))))

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Account Type
COLUMN,3,Income Balance
COLUMN,4,Last Date Modified
COLUMN,5,Entry No
COLUMN,6,G L Account No
COLUMN,7,Posting Date
COLUMN,8,Document Type
COLUMN,9,Document No
COLUMN,10,Description
COLUMN,11,Bal Account No
COLUMN,12,Department Code
COLUMN,13,Make Code
COLUMN,14,User Id
COLUMN,15,Source Code
COLUMN,16,Quantity
COLUMN,17,Reason Code
COLUMN,18,Gen Posting Type
COLUMN,19,Gen Bus Posting Group
COLUMN,20,Gen Prod Posting Group
COLUMN,21,Bal Account Type
COLUMN,22,Transaction No
COLUMN,23,Document Date
COLUMN,24,External Document No
COLUMN,25,Source Type
COLUMN,26,Source No
COLUMN,27,No Series
COLUMN,28,Branch Code
COLUMN,29,Main Area
COLUMN,30,Vin
COLUMN,31,Book No
COLUMN,32,Veh Source Code
COLUMN,33,Reposted
COLUMN,34,Corrected
COLUMN,35,Correction To Curr No
COLUMN,36,Reposting To Curr No
COLUMN,37,Code
COLUMN,38,Name
COLUMN,39,Bookkeep Date
COLUMN,40,Betrieb
COLUMN,41,Acct Nr
COLUMN,42,Betrag
COLUMN,43,Amount_1
COLUMN,44,Text
COLUMN,45,Hauptbetrieb
COLUMN,46,Marke