COGNOS QUERY
STRUCTURE,1,1
DATABASE,GC_Navision
DATASOURCENAME,C:\GlobalCube\System\NAVISION\IQD\serv_teile\teile_archiv_rg_ausgang_365t.imr
TITLE,teile_archiv_rg_ausgang_365t.imr
BEGIN SQL
select c217 as c1,
	   c216 as c2,
	   c215 as c3,
	   c214 as c4,
	   c213 as c5,
	   c212 as c6,
	   c133 as c7,
	   c211 as c8,
	   c210 as c9,
	   c209 as c10,
	   c208 as c11,
	   c207 as c12,
	   c206 as c13,
	   c205 as c14,
	   c204 as c15,
	   c203 as c16,
	   c202 as c17,
	   c201 as c18,
	   c200 as c19,
	   c199 as c20,
	   c198 as c21,
	   c197 as c22,
	   c196 as c23,
	   c195 as c24,
	   c194 as c25,
	   c193 as c26,
	   c192 as c27,
	   c191 as c28,
	   c190 as c29,
	   c189 as c30,
	   c188 as c31,
	   c187 as c32,
	   c186 as c33,
	   c185 as c34,
	   c184 as c35,
	   c183 as c36,
	   c182 as c37,
	   c181 as c38,
	   c180 as c39,
	   c179 as c40,
	   c178 as c41,
	   c177 as c42,
	   c176 as c43,
	   c175 as c44,
	   c174 as c45,
	   c173 as c46,
	   c172 as c47,
	   c171 as c48,
	   c170 as c49,
	   c169 as c50,
	   c168 as c51,
	   c167 as c52,
	   c166 as c53,
	   c165 as c54,
	   c164 as c55,
	   c163 as c56,
	   c162 as c57,
	   c161 as c58,
	   c160 as c59,
	   c159 as c60,
	   c127 as c61,
	   c158 as c62,
	   c157 as c63,
	   c156 as c64,
	   c155 as c65,
	   c154 as c66,
	   c153 as c67,
	   c152 as c68,
	   c151 as c69,
	   c150 as c70,
	   c149 as c71,
	   c148 as c72,
	   c147 as c73,
	   c146 as c74,
	   c145 as c75,
	   c144 as c76,
	   c143 as c77,
	   c142 as c78,
	   c141 as c79,
	   c140 as c80,
	   c139 as c81,
	   c138 as c82,
	   c137 as c83,
	   c135 as c84,
	   c136 as c85,
	   c135 as c86,
	   c134 as c87,
	   c133 as c88,
	   '1' as c89,
	   c121 as c90,
	   c132 as c91,
	   c131 as c92,
	   c130 as c93,
	   c129 as c94,
	   c128 as c95,
	   '' as c96,
	   '' as c97,
	   '' as c98,
	   c127 as c99,
	   'Teile' as c100,
	   'Teile' as c101,
	   c126 as c102,
	   c125 as c103,
	   '' as c104,
	   '' as c105,
	   'Teile' as c106,
	   c124 as c107,
	   1 as c108,
	   XCOUNT(c188 for c119) as c109,
	   1 / (XCOUNT(c188 for c119)) as c110,
	   '' as c111,
	   c123 as c112,
	   c122 as c113,
	   c121 as c114,
	   c120 as c115
from 
(select ((T1."Service Order No_" || ' - ' || T1."Bill-to Name")) as c119,
	   CASE WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END)) IN ('80')) THEN ('WTB') ELSE null END as c120,
	   (CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') WHEN (T1."Location Code" = 'WTB') THEN ('80') ELSE null END) as c121,
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as c122,
	   T1."Client_DB" as c123,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) and ((T3."First Name" || ' ' || T3."Last Name") IS NOT NULL)) THEN (T1."Service Order No_" || ' - ' || (T3."First Name" || ' ' || T3."Last Name") || ' - ' || (asciiz(extract(YEAR FROM T1."Posting Date"),4) || '-' || asciiz(extract(MONTH FROM T1."Posting Date"),2) || '-' || asciiz(extract(DAY FROM T1."Posting Date"),2)) || ' - ' || T1."Bill-to Name") WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 30) and ((T3."First Name" || ' ' || T3."Last Name") IS NULL)) THEN (T1."Service Order No_" || ' - ' || (asciiz(extract(YEAR FROM T1."Posting Date"),4) || '-' || asciiz(extract(MONTH FROM T1."Posting Date"),2) || '-' || asciiz(extract(DAY FROM T1."Posting Date"),2)) || ' - ' || T1."Bill-to Name") ELSE null END as c124,
	   T1."Sell-to Customer No_" || ' - ' || T1."Bill-to Name" as c125,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T4."Description") END as c126,
	   T2."Make Code" as c127,
	   T1."Service Order No_" || ' - ' || T1."Bill-to Name" as c128,
	   T3."Department Code" as c129,
	   T3."First Name" || ' ' || T3."Last Name" as c130,
	   T3."Last Name" as c131,
	   T3."First Name" as c132,
	   T1."Posting Date" as c133,
	   ((cast_float(T2."Quantity"))) * ((cast_float(T2."Unit Cost"))) as c134,
	   ((cast_float(T2."Amount"))) as c135,
	   (cast_float(T2."Unit Cost")) as c136,
	   (cast_float(T2."Line Discount Amount")) as c137,
	   (cast_float(T2."Unit Cost (LCY)")) as c138,
	   (cast_float(T2."Unit Price")) as c139,
	   (cast_float(T2."Quantity")) as c140,
	   T2."Item Group Code" as c141,
	   T2."Service Order Line No_" as c142,
	   T2."Service Order No_" as c143,
	   T2."Item Type" as c144,
	   T2."Order Type" as c145,
	   T2."Branch Book No_" as c146,
	   T2."Posting Date" as c147,
	   T2."Order Line No_" as c148,
	   T2."Order No_" as c149,
	   T2."Quantity (Base)" as c150,
	   T2."Unit of Measure Code" as c151,
	   T2."Qty_ per Unit of Measure" as c152,
	   T2."Variant Code" as c153,
	   T2."Book No_" as c154,
	   T2."Unit Cost" as c155,
	   T2."Area" as c156,
	   T2."Allow Quantity Disc_" as c157,
	   T2."Price Group Code" as c158,
	   T2."Department Code" as c159,
	   T2."Allow Invoice Disc_" as c160,
	   T2."Amount Including VAT" as c161,
	   T2."Amount" as c162,
	   T2."Line Discount Amount" as c163,
	   T2."Line Discount %" as c164,
	   T2."Quantity Disc_ %" as c165,
	   T2."VAT %" as c166,
	   T2."Unit Cost (LCY)" as c167,
	   T2."Unit Price" as c168,
	   T2."Quantity" as c169,
	   T2."Unit of Measure" as c170,
	   T2."Description 2" as c171,
	   T2."Description" as c172,
	   T2."Quantity Disc_ Code" as c173,
	   T2."Posting Group" as c174,
	   T2."Location Code" as c175,
	   T2."No_" as c176,
	   T2."Type" as c177,
	   T2."Sell-to Customer No_" as c178,
	   T2."Line No_" as c179,
	   T2."Document No_" as c180,
	   T1."VIN" as c181,
	   T1."Branch Code" as c182,
	   T1."Service Order Line No_" as c183,
	   T1."Service Order No_ 2" as c184,
	   T1."Customer Group Code" as c185,
	   T1."Show Discount" as c186,
	   T1."Item Sales Price Group" as c187,
	   T1."Service Order No_" as c188,
	   T1."Order Type" as c189,
	   T1."User ID" as c190,
	   T1."Order No_ Series" as c191,
	   T1."No_ Series" as c192,
	   T1."Payment Method Code" as c193,
	   T1."Area" as c194,
	   T1."Document Date" as c195,
	   T1."Sell-to Customer Name 2" as c196,
	   T1."Sell-to Customer Name" as c197,
	   T1."Gen_ Bus_ Posting Group" as c198,
	   T1."On Hold" as c199,
	   T1."Order No_" as c200,
	   T1."Salesperson Code" as c201,
	   T1."Cust__Item Disc_ Gr_" as c202,
	   T1."Invoice Disc_ Code" as c203,
	   T1."Customer Posting Group" as c204,
	   T1."Make Code" as c205,
	   T1."Department Code" as c206,
	   T1."Location Code" as c207,
	   T1."Pmt_ Discount Date" as c208,
	   T1."Payment Discount %" as c209,
	   T1."Due Date" as c210,
	   T1."Posting Description" as c211,
	   T1."Order Date" as c212,
	   T1."Bill-to Name 2" as c213,
	   T1."Bill-to Name" as c214,
	   T1."Bill-to Customer No_" as c215,
	   T1."Sell-to Customer No_" as c216,
	   T1."No_" as c217
from "NAVISION"."import"."Sales_Invoice_Line" T2,
	((("NAVISION"."import"."Sales_Invoice_Header" T1 left outer join "NAVISION"."import"."Employee" T3 on (T1."Salesperson Code" = T3."No_") and (T1."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Customer" T5 on (T1."Bill-to Customer No_" = T5."No_") and (T1."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Customer_Group" T4 on (T5."Customer Group Code" = T4."Code") and (T5."Client_DB" = T4."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and (((od_year(T1."Posting Date")) >= (od_year((now()))) - 1) and ((T1."No_" LIKE 'VRT%') or (T1."No_" LIKE 'VRGT%')))
) D1
order by c18 asc,c31 asc,c93 asc,c1 asc

END SQL
COLUMN,0,No
COLUMN,1,Sell-to Customer No
COLUMN,2,Bill-to Customer No
COLUMN,3,Bill-to Name
COLUMN,4,Bill-to Name 2
COLUMN,5,Order Date
COLUMN,6,Posting Date
COLUMN,7,Posting Description
COLUMN,8,Due Date
COLUMN,9,Payment Discount %
COLUMN,10,Pmt Discount Date
COLUMN,11,Location Code
COLUMN,12,Department Code
COLUMN,13,Make Code
COLUMN,14,Customer Posting Group
COLUMN,15,Invoice Disc Code
COLUMN,16,Cust Item Disc Gr
COLUMN,17,Salesperson Code
COLUMN,18,Order No
COLUMN,19,On Hold
COLUMN,20,Gen Bus Posting Group
COLUMN,21,Sell-to Customer Name
COLUMN,22,Sell-to Customer Name 2
COLUMN,23,Document Date
COLUMN,24,Area
COLUMN,25,Payment Method Code
COLUMN,26,No Series
COLUMN,27,Order No Series
COLUMN,28,User Id
COLUMN,29,Order Type
COLUMN,30,Service Order No
COLUMN,31,Item Sales Price Group
COLUMN,32,Show Discount
COLUMN,33,Customer Group Code
COLUMN,34,Service Order No 2
COLUMN,35,Service Order Line No
COLUMN,36,Branch Code
COLUMN,37,Vin
COLUMN,38,Document No
COLUMN,39,Line No
COLUMN,40,Sell-to Customer No
COLUMN,41,Type
COLUMN,42,No
COLUMN,43,Location Code
COLUMN,44,Posting Group
COLUMN,45,Quantity Disc Code
COLUMN,46,Description
COLUMN,47,Description 2
COLUMN,48,Unit Of Measure
COLUMN,49,Quantity
COLUMN,50,Unit Price
COLUMN,51,Unit Cost (lcy)
COLUMN,52,Vat %
COLUMN,53,Quantity Disc %
COLUMN,54,Line Discount %
COLUMN,55,Line Discount Amount
COLUMN,56,Amount
COLUMN,57,Amount Including Vat
COLUMN,58,Allow Invoice Disc
COLUMN,59,Department Code
COLUMN,60,Make Code
COLUMN,61,Price Group Code
COLUMN,62,Allow Quantity Disc
COLUMN,63,Area
COLUMN,64,Unit Cost
COLUMN,65,Book No
COLUMN,66,Variant Code
COLUMN,67,Qty Per Unit Of Measure
COLUMN,68,Unit Of Measure Code
COLUMN,69,Quantity (base)
COLUMN,70,Order No
COLUMN,71,Order Line No
COLUMN,72,Posting Date
COLUMN,73,Branch Book No
COLUMN,74,Order Type
COLUMN,75,Item Type
COLUMN,76,Service Order No
COLUMN,77,Service Order Line No
COLUMN,78,Item Group Code
COLUMN,79,Menge
COLUMN,80,Unit Preis
COLUMN,81,Unit Kosten (LCY)
COLUMN,82,Line Rabatt Betrag
COLUMN,83,Betrag
COLUMN,84,Kosten
COLUMN,85,Umsatz Teile Service
COLUMN,86,Einsatz Teile Service
COLUMN,87,Invoice Date
COLUMN,88,Hauptbetrieb
COLUMN,89,Standort
COLUMN,90,First Name
COLUMN,91,Last Name
COLUMN,92,Serviceberater
COLUMN,93,Department Code
COLUMN,94,Order Number
COLUMN,95,Fabrikat
COLUMN,96,Model
COLUMN,97,Fahrzeug
COLUMN,98,Marke
COLUMN,99,Umsatzart
COLUMN,100,Auftragsart
COLUMN,101,Kundenart
COLUMN,102,Kunde
COLUMN,103,Function Code
COLUMN,104,Monteur
COLUMN,105,Auftragsart_1
COLUMN,106,Order Number_Rg_Ausg
COLUMN,107,DG_1
COLUMN,108,DG_2
COLUMN,109,Durchgänge
COLUMN,110,Rg_Ausgang_Arb_Pos
COLUMN,111,Hauptbetrieb_ID
COLUMN,112,Hauptbetrieb_Name
COLUMN,113,Standort_ID
COLUMN,114,Standort_Name