C11/System/NAVISION/IQD/Belege/belege_stk_hol_bring_service.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\belege\belege_stk_hol_bring_service.imr
TITLE,belege_stk_hol_bring_service.imr
BEGIN SQL
select c78 as c1,
	   c113 as c2,
	   c112 as c3,
	   c111 as c4,
	   c110 as c5,
	   c109 as c6,
	   c108 as c7,
	   c107 as c8,
	   c106 as c9,
	   c73 as c10,
	   c74 as c11,
	   c105 as c12,
	   c104 as c13,
	   c103 as c14,
	   c102 as c15,
	   c101 as c16,
	   c100 as c17,
	   c99 as c18,
	   c98 as c19,
	   c97 as c20,
	   c96 as c21,
	   c95 as c22,
	   c94 as c23,
	   c93 as c24,
	   c92 as c25,
	   c91 as c26,
	   c90 as c27,
	   c89 as c28,
	   c88 as c29,
	   c87 as c30,
	   c86 as c31,
	   c85 as c32,
	   c84 as c33,
	   c83 as c34,
	   c82 as c35,
	   c81 as c36,
	   c80 as c37,
	   c79 as c38,
	   CASE WHEN ((c78 IN ('88382','88384')) and (XSUM(c72 for c62) <> 0)) THEN (1 / XCOUNT(c78 for c62)) ELSE (0) END as c39,
	   c77 as c40,
	   c76 as c41,
	   c75 as c42,
	   c74 as c43,
	   c64 as c44,
	   c73 as c45,
	   c72 as c46,
	   c71 as c47,
	   c70 as c48,
	   c69 as c49,
	   'verk. Hol-Bring-Service' as c50,
	   c68 as c51,
	   ('SC') as c52,
	   c67 as c53,
	   c62 as c54,
	   c66 as c55,
	   c65 as c56,
	   c64 as c57,
	   c63 as c58
from 
(select (T2."VIN" || '_' || ((rtrim((cast_numberToString(cast_integer((od_month(T2."Posting Date"))))))) || (rtrim((cast_numberToString(cast_integer((od_year(T2."Posting Date"))))))))) as c62,
	   CASE WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as c63,
	   (CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((od_left(T1."Department Code",2))) END) as c64,
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as c65,
	   T2."Client_DB" as c66,
	   (rtrim((cast_numberToString(cast_integer((od_month(T2."Posting Date"))))))) || (rtrim((cast_numberToString(cast_integer((od_year(T2."Posting Date"))))))) as c67,
	   CASE WHEN (T1."No_" IN ('88382','88384')) THEN ('verk. Hol-Bring-Service Stk.') ELSE null END as c68,
	   CASE WHEN ((T2."G_L Account No_" IN ('88200','88201','88202')) and (T2."Posting Date" <= TIMESTAMP '2015-04-30 00:00:00.000')) THEN (T2."G_L Account No_" || '_NA') ELSE (T2."G_L Account No_") END as c69,
	   CASE WHEN (((T1."No_" LIKE '4%') or (T1."No_" LIKE '2%')) and ((extract(DAY FROM (now()) - T2."Posting Date")) <= 365)) THEN (T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."User ID") WHEN (((not T1."No_" LIKE '4%') and (not T1."No_" LIKE '2%')) and ((extract(DAY FROM (now()) - T2."Posting Date")) <= 30)) THEN (T2."Document No_" || ' - ' || T2."Description" || ' - ' || T2."User ID") ELSE null END as c70,
	   (cast_float(T2."Amount")) as c71,
	   ((cast_float(T2."Amount"))) * -1 as c72,
	   T2."G_L Account No_" as c73,
	   T2."Posting Date" as c74,
	   T3."Name" as c75,
	   T3."Code" as c76,
	   T2."Reposting to curr_ No_" as c77,
	   T1."No_" as c78,
	   T2."Corrected" as c79,
	   T2."Reposted" as c80,
	   T2."Veh_ Source Code" as c81,
	   T2."Book No_" as c82,
	   T2."VIN" as c83,
	   T2."Main Area" as c84,
	   T2."Branch Code" as c85,
	   T2."No_ Series" as c86,
	   T2."Source No_" as c87,
	   T2."Source Type" as c88,
	   T2."External Document No_" as c89,
	   T2."Document Date" as c90,
	   T2."Transaction No_" as c91,
	   T2."Bal_ Account Type" as c92,
	   T2."Gen_ Prod_ Posting Group" as c93,
	   T2."Gen_ Bus_ Posting Group" as c94,
	   T2."Gen_ Posting Type" as c95,
	   T2."Reason Code" as c96,
	   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 c97,
	   T2."Source Code" as c98,
	   T2."User ID" as c99,
	   T2."Make Code" as c100,
	   T2."Department Code" as c101,
	   T2."Bal_ Account No_" as c102,
	   T2."Description" as c103,
	   T2."Document No_" as c104,
	   T2."Document Type" as c105,
	   T2."Entry No_" as c106,
	   T1."Last Date Modified" as c107,
	   T1."Income_Balance" as c108,
	   T1."Make Code" as c109,
	   T1."Department Code" as c110,
	   T1."Account Type" as c111,
	   T1."Search Description" as c112,
	   T1."Name" as c113
from "NAVISION"."import"."G_L_Account" T1,
	("NAVISION"."import"."G_L_Entry" T2 left outer join "NAVISION"."import"."Department" T3 on (T2."Department Code" = T3."Code") and (T2."Client_DB" = T3."Client_DB"))
where ((T1."No_" = T2."G_L Account No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((((T1."Income_Balance" = 0) and (not T1."No_" IN ('42400','43150','43250','43350','43450','44220','44230','46700','47610','48020','49860','48800','45250'))) and (T2."Document No_" <> 'ABSCHLUSS2008_1')) and (not T2."Document No_" IN ('ABSCHLUSS2009_1','ABSCHLUSS2009_2','ABSCHLUSS2009_3','ABSCHLUSS_1','ABSCHLUSS2011','ABSCHLUSS2012','ABSCHLUSS2013','ABSCHLUSS2014','ABSCHLUSS2015','ABSCHLUSS2016','ABSCHLUSS2017','ABSCHLUSS2018'))) and (T2."Source Code" <> 'JAHRABSCH')) and (T2."Posting Date" >= TIMESTAMP '2021-01-01 00:00:00.000')) and (T1."No_" IN ('88382','88384')))
order by c62 asc,c83 asc,c82 asc
) D1

END SQL
COLUMN,0,No
COLUMN,1,Name
COLUMN,2,Search Description
COLUMN,3,Account Type
COLUMN,4,Department Code
COLUMN,5,Make Code
COLUMN,6,Income Balance
COLUMN,7,Last Date Modified
COLUMN,8,Entry No
COLUMN,9,G L Account No
COLUMN,10,Posting Date
COLUMN,11,Document Type
COLUMN,12,Document No
COLUMN,13,Description
COLUMN,14,Bal Account No
COLUMN,15,Department Code
COLUMN,16,Make Code
COLUMN,17,User Id
COLUMN,18,Source Code
COLUMN,19,Quantity
COLUMN,20,Reason Code
COLUMN,21,Gen Posting Type
COLUMN,22,Gen Bus Posting Group
COLUMN,23,Gen Prod Posting Group
COLUMN,24,Bal Account Type
COLUMN,25,Transaction No
COLUMN,26,Document Date
COLUMN,27,External Document No
COLUMN,28,Source Type
COLUMN,29,Source No
COLUMN,30,No Series
COLUMN,31,Branch Code
COLUMN,32,Main Area
COLUMN,33,Vin
COLUMN,34,Book No
COLUMN,35,Veh Source Code
COLUMN,36,Reposted
COLUMN,37,Corrected
COLUMN,38,Hol-Bring-Service Stk
COLUMN,39,Reposting To Curr No
COLUMN,40,Code
COLUMN,41,Name
COLUMN,42,Jahr
COLUMN,43,Betrieb Nr
COLUMN,44,Konto Nr_ori
COLUMN,45,Betrag_alt
COLUMN,46,Amount_1
COLUMN,47,Text
COLUMN,48,Konto Nr_
COLUMN,49,Vstufe 1
COLUMN,50,Zeile mit Bez
COLUMN,51,Bereich
COLUMN,52,Monat_Jahr_Posting_Date
COLUMN,53,VIN_Monat_Jahr_Posting_Date
COLUMN,54,Hauptbetrieb_ID
COLUMN,55,Hauptbetrieb_Name
COLUMN,56,Standort_ID
COLUMN,57,Standort_Name