C11/System/ARI/IQD/Service/SRD_Teile_neu_3.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ARIntelligence
DATASOURCENAME,C:\GlobalCube\System\ARI\IQD\Service\SRD_Teile_neu_3.imr
TITLE,SRD_Teile_neu_3.imr
BEGIN SQL
select c157 as c1,
	   c156 as c2,
	   c155 as c3,
	   c154 as c4,
	   c153 as c5,
	   c132 as c6,
	   c152 as c7,
	   c151 as c8,
	   c150 as c9,
	   c149 as c10,
	   c148 as c11,
	   c147 as c12,
	   c146 as c13,
	   c145 as c14,
	   c144 as c15,
	   c111 as c16,
	   c143 as c17,
	   c142 as c18,
	   c141 as c19,
	   c140 as c20,
	   c139 as c21,
	   c138 as c22,
	   c137 as c23,
	   c136 as c24,
	   c135 as c25,
	   c134 as c26,
	   '1' as c27,
	   c133 as c28,
	   c132 as c29,
	   c131 as c30,
	   c130 as c31,
	   c129 as c32,
	   c128 as c33,
	   c127 as c34,
	   c126 as c35,
	   c125 as c36,
	   c124 as c37,
	   c123 as c38,
	   c122 as c39,
	   c121 as c40,
	   c120 as c41,
	   c119 as c42,
	   c116 as c43,
	   c118 as c44,
	   c117 as c45,
	   c116 as c46,
	   c115 as c47,
	   c114 as c48,
	   c113 as c49,
	   c112 as c50,
	   c111 as c51,
	   c110 as c52,
	   c109 as c53,
	   c95 as c54,
	   c108 as c55,
	   c107 as c56,
	   c106 as c57,
	   c91 as c58,
	   c93 as c59,
	   c92 as c60,
	   c105 as c61,
	   c104 as c62,
	   c103 as c63,
	   c102 as c64,
	   c101 as c65,
	   c100 as c66,
	   c99 as c67,
	   c98 as c68,
	   c97 as c69,
	   c96 as c70,
	   c94 as c71,
	   XCOUNT(c155 for c85) as c72,
	   (c95) / (XCOUNT(c155 for c85)) as c73,
	   CASE WHEN ((c94) IS NOT NULL) THEN ((c94) / (XCOUNT(c155 for c85))) ELSE (0) END as c74,
	   (c93) / (XCOUNT(c155 for c85)) as c75,
	   (c92) / (XCOUNT(c155 for c85)) as c76,
	   (c91) / (XCOUNT(c155 for c85)) as c77,
	   c90 as c78,
	   c89 as c79,
	   c88 as c80,
	   c87 as c81,
	   c86 as c82
from 
(select ((cast_numberToString(cast_integer(T1."Entry No_"))) || T1."Document No_") as c85,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 3) THEN ((CASE WHEN (T3."No_" IS NOT NULL) THEN (T1."Document No_" || ' / ' || (CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" || ' - ' || T3."Name") ELSE null END)) ELSE (T1."Document No_") END)) ELSE null END as c86,
	   (od_left(T1."Document No_",4)) as c87,
	   CASE WHEN (T4."Item Group Code" BETWEEN '00' AND '22') THEN ('Artikelgruppe 0 - 22') WHEN (T4."Item Group Code" BETWEEN '23' AND '33') THEN ('Artikelgruppe 23 - 33') WHEN (T4."Item Group Code" BETWEEN '34' AND '99') THEN ('Artikelgruppe 34 - 99') ELSE ('Teileart fehlt/Rest') END as c88,
	   CASE WHEN (T3."Extra Code 3" = 'EXPORT') THEN ('Anteil Export') ELSE ('Anteil Inland') END as c89,
	   T3."Extra Code 3" as c90,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Einkauf') THEN (((cast_float(T1."Quantity"))) * ((cast_float(T1."Unit Price")))) ELSE (0) END as c91,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Einkauf') THEN (((cast_float(T1."Quantity")))) ELSE (0) END as c92,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Verkauf') THEN (((cast_float(T1."Quantity")))) ELSE (0) END as c93,
	   CASE WHEN (((CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Verkauf') THEN (((cast_float(T1."Quantity"))) * ((cast_float(T1."Average Cost")))) ELSE (0) END) < 0) and (((cast_float(T6."Adjusted Cost"))) > 0)) THEN (((cast_float(T6."Adjusted Cost"))) * -1) WHEN (((CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Verkauf') THEN (((cast_float(T1."Quantity"))) * ((cast_float(T1."Average Cost")))) ELSE (0) END) > 0) and (((cast_float(T6."Adjusted Cost"))) < 0)) THEN (((cast_float(T6."Adjusted Cost"))) * -1) ELSE (((cast_float(T6."Adjusted Cost")))) END as c94,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Verkauf') THEN (((cast_float(T1."Quantity"))) * ((cast_float(T1."Unit Price")))) ELSE (0) END as c95,
	   (cast_float(T6."Cost Posted to G_L")) as c96,
	   (cast_float(T6."Adjusted Cost")) as c97,
	   (cast_float(T6."Amount")) as c98,
	   CASE WHEN (T4."Make Code" IN ('BMW','BMW-C1','BMW-MOT','BMWI')) THEN ('BMW') WHEN (T4."Make Code" IN ('BMW-MINI')) THEN ('MINI') ELSE ('Andere') END as c99,
	   T4."Make Code" as c100,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 180) THEN ((CASE WHEN (T3."No_" IS NOT NULL) THEN (T1."Document No_" || ' / ' || (CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" || ' - ' || T3."Name") ELSE null END)) ELSE (T1."Document No_") END)) ELSE null END as c101,
	   CASE WHEN (T3."No_" IS NOT NULL) THEN (T1."Document No_" || ' / ' || (CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" || ' - ' || T3."Name") ELSE null END)) ELSE (T1."Document No_") END as c102,
	   CASE WHEN (T1."Area" = 1) THEN ('T & Z') WHEN (T1."Area" = 4) THEN ('Service') WHEN (T1."Area" = 0) THEN ('Sonstige') ELSE null END as c103,
	   T1."Item No_" || ' - ' || T4."Description" as c104,
	   T4."Description" as c105,
	   T5."Description" as c106,
	   T1."Transaction Type Specification" || ' - ' || T5."Description" as c107,
	   CASE WHEN ((CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END) = 'Verkauf') THEN (((cast_float(T1."Quantity"))) * ((cast_float(T1."Average Cost")))) ELSE (0) END as c108,
	   CASE WHEN (T1."Transaction Type" = 'BA') THEN ('BA - Bestellung') WHEN (T1."Transaction Type" = 'RA') THEN ('RA - Rückgaben') WHEN (T1."Transaction Type" = 'LB') THEN ('LB - Lagerzugänge maschinell') WHEN (T1."Transaction Type" = 'LC') THEN ('LC - Lagerabgänge') WHEN (T1."Transaction Type" = 'LA') THEN ('LA - Lagerzugänge manuell') ELSE null END as c109,
	   CASE WHEN (T1."Transaction Type" IN ('LC')) THEN ('Verkauf') ELSE ('Einkauf') END as c110,
	   T1."Posting Date" as c111,
	   (cast_float(T1."Average Cost")) as c112,
	   (cast_float(T1."Unit Price")) as c113,
	   (cast_float(T1."Quantity")) as c114,
	   CASE WHEN (T4."Parts Category" = '1') THEN ('1 - Teile') WHEN (T4."Parts Category" = '2') THEN ('2 - Tauschteile') WHEN (T4."Parts Category" = '3') THEN ('3 - Nachrüstteile') WHEN (T4."Parts Category" = '4') THEN ('4 - ') WHEN (T4."Parts Category" = '5') THEN ('5 - Räder, Felgen') WHEN (T4."Parts Category" = '6') THEN ('6 - ') WHEN (T4."Parts Category" = '7') THEN ('7 - Accessoires') WHEN (T4."Parts Category" = '8') THEN ('8 - Reifen') WHEN (T4."Parts Category" = '9') THEN ('9 - Öle, Sonstiges') ELSE null END as c115,
	   T4."Item Group Code" as c116,
	   CASE WHEN (T4."Item Group Code" IN ('B','C','A')) THEN ('Artikelgruppe A - C') WHEN (T4."Item Group Code" IN ('E','K','H','F','I','G','D','J')) THEN ('Artikelgruppe D - K') WHEN (T4."Item Group Code" BETWEEN '23' AND '33') THEN ('Artikelgruppe D + E neu') WHEN (T4."Item Group Code" BETWEEN '34' AND '99') THEN ('Artikelgruppe F - K neu') ELSE ('Teileart fehlt/Rest') END as c117,
	   CASE WHEN (T4."Parts Category" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T4."Parts Category" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END as c118,
	   T4."Parts Category" as c119,
	   CASE WHEN (T3."No_" IS NOT NULL) THEN (T3."No_" || ' - ' || T3."Name") ELSE null END as c120,
	   T3."Gen_ Bus_ Posting Group" as c121,
	   CASE WHEN (T3."No_" LIKE 'I%') THEN ('Intern') WHEN (T3."No_" LIKE 'G%') THEN ('GWL') WHEN (((not T3."No_" LIKE 'I%') and (not T3."No_" LIKE 'G%')) and (T3."No_" IS NOT NULL)) THEN ('Extern') ELSE null END as c122,
	   T3."Customer Type" as c123,
	   T3."Location Code" as c124,
	   T3."Customer Posting Group" as c125,
	   T3."Customer Group Code" as c126,
	   T3."Name" as c127,
	   T3."No_" as c128,
	   T2."First Name" || ' ' || T2."Last Name" as c129,
	   T2."Last Name" as c130,
	   T2."First Name" as c131,
	   T1."Transaction Type Specification" as c132,
	   CASE WHEN (T1."Dealer No_" IN ('00357','29682','22075','28303')) THEN ('10') WHEN (T1."Dealer No_" IN ('00557','29619','40119')) THEN ('20') ELSE null END as c133,
	   T1."Item No_" as c134,
	   T1."Salesperson Code" as c135,
	   T1."Mileage" as c136,
	   T1."VIN" as c137,
	   T1."Customer No_" as c138,
	   T1."Customer Text" as c139,
	   T1."Customer Order No_" as c140,
	   T1."Unit Price" as c141,
	   T1."Gross Price" as c142,
	   T1."Quantity" as c143,
	   T1."Document No_" as c144,
	   T1."Document Type" as c145,
	   T1."Area" as c146,
	   T1."Enable for Requisition" as c147,
	   T1."Customer Backorder" as c148,
	   T1."Average Cost" as c149,
	   T1."Open Order Quantity" as c150,
	   T1."Current Qty_ On-Hand" as c151,
	   T1."xTimestamp" as c152,
	   T1."Transaction Type" as c153,
	   T1."Dealer No_" as c154,
	   T1."SRD Item No_" as c155,
	   T1."Entry No_" as c156,
	   T1."Register No_" as c157
from ((((("Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Transaction" T1 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T2 on T1."Salesperson Code" = T2."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T3 on T1."Customer No_" = T3."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Item" T4 on T1."Item No_" = T4."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$BMW SRD Movement Type" T5 on (T1."Transaction Type" = T5."Movement Type Code") and (T1."Transaction Type Specification" = T5."Movement Type Specification")) left outer join "Vogl7x"."dbo"."BMW AH Vogl$Value Entry" T6 on ((T6."Item No_" = T1."SRD Item No_") and (T6."Document No_" = T1."Document No_")) and (T6."Posting Date" = T1."Posting Date"))
where ((T1."Posting Date" >= TIMESTAMP '2020-01-01 00:00:00.000') and (not T1."Document No_" LIKE 'FILAG%'))
) D1
order by c7 asc

END SQL
COLUMN,0,Register No
COLUMN,1,Entry No
COLUMN,2,Srd Item No
COLUMN,3,Dealer No
COLUMN,4,Transaction Type
COLUMN,5,Transaction Type Specification
COLUMN,6,Xtimestamp
COLUMN,7,Current Qty On-hand
COLUMN,8,Open Order Quantity
COLUMN,9,Average Cost
COLUMN,10,Customer Backorder
COLUMN,11,Enable For Requisition
COLUMN,12,Area
COLUMN,13,Document Type
COLUMN,14,Document No
COLUMN,15,Posting Date
COLUMN,16,Quantity
COLUMN,17,Gross Price
COLUMN,18,Unit Price
COLUMN,19,Customer Order No
COLUMN,20,Customer Text
COLUMN,21,Customer No
COLUMN,22,Vin
COLUMN,23,Mileage
COLUMN,24,Salesperson Code
COLUMN,25,Item No
COLUMN,26,Hauptbetrieb
COLUMN,27,Standort
COLUMN,28,BA Spezifikation
COLUMN,29,First Name
COLUMN,30,Last Name
COLUMN,31,Verkäufer
COLUMN,32,No_Customer
COLUMN,33,Name_Customer
COLUMN,34,Customer Group Code
COLUMN,35,Customer Posting Group
COLUMN,36,Location Code_Customer
COLUMN,37,Customer Type
COLUMN,38,Umsatzart
COLUMN,39,Kundenart
COLUMN,40,Kunde
COLUMN,41,Bmw Parts Type
COLUMN,42,Item Group Code
COLUMN,43,Teileart-Gruppe
COLUMN,44,Artikelgruppe-Gruppe
COLUMN,45,Artikelgruppe
COLUMN,46,Teileart
COLUMN,47,Menge
COLUMN,48,VK
COLUMN,49,EK
COLUMN,50,Invoice Date
COLUMN,51,Verkauf / Einkauf
COLUMN,52,Bewegungsart
COLUMN,53,Umsatz_alt
COLUMN,54,Einsatz_alt
COLUMN,55,Bewegungsart Detail
COLUMN,56,Description
COLUMN,57,Betrag Lagerzugang_alt
COLUMN,58,Menge VK_alt
COLUMN,59,Menge Lagerzugang_alt
COLUMN,60,Description_Artikel
COLUMN,61,Teil
COLUMN,62,Abteilung
COLUMN,63,Beleg_ori
COLUMN,64,Beleg
COLUMN,65,Make Code
COLUMN,66,Marke
COLUMN,67,Amount
COLUMN,68,Adjusted Cost
COLUMN,69,Cost Posted To G L
COLUMN,70,Einsatz_neu
COLUMN,71,Anzahl Datensätze
COLUMN,72,Umsatz
COLUMN,73,Einsatz
COLUMN,74,Menge VK
COLUMN,75,Menge Lagerzugang
COLUMN,76,Betrag Lagerzugang
COLUMN,77,Extra Code 3
COLUMN,78,Inland/Export
COLUMN,79,Artikelgruppe-Gruppe numerisch
COLUMN,80,4 Stellen Document No
COLUMN,81,Beleg_Tagesbericht