C11/System/ARIntelligence/IQD/Service/Service_Ausgangsrechnung_EW_FL_EK_Rg.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_Ausgangsrechnung_EW_FL_EK_Rg.imr
TITLE,Service_Ausgangsrechnung_EW_FL_EK_Rg.imr
BEGIN SQL
select c98 as c1,
	   c181 as c2,
	   c180 as c3,
	   c179 as c4,
	   c178 as c5,
	   c177 as c6,
	   c176 as c7,
	   c175 as c8,
	   c174 as c9,
	   c173 as c10,
	   c172 as c11,
	   c171 as c12,
	   c124 as c13,
	   c170 as c14,
	   c169 as c15,
	   c168 as c16,
	   c167 as c17,
	   c166 as c18,
	   c165 as c19,
	   c164 as c20,
	   c163 as c21,
	   c162 as c22,
	   c161 as c23,
	   c160 as c24,
	   c159 as c25,
	   c158 as c26,
	   c157 as c27,
	   c156 as c28,
	   c155 as c29,
	   c154 as c30,
	   c153 as c31,
	   c152 as c32,
	   c151 as c33,
	   c150 as c34,
	   c149 as c35,
	   c148 as c36,
	   c147 as c37,
	   c146 as c38,
	   c145 as c39,
	   c144 as c40,
	   c143 as c41,
	   c142 as c42,
	   c141 as c43,
	   c140 as c44,
	   c139 as c45,
	   c126 as c46,
	   c138 as c47,
	   c137 as c48,
	   c136 as c49,
	   c135 as c50,
	   c134 as c51,
	   c133 as c52,
	   c132 as c53,
	   c131 as c54,
	   c130 as c55,
	   c129 as c56,
	   '1' as c57,
	   c128 as c58,
	   c127 as c59,
	   c124 as c60,
	   c126 as c61,
	   c125 as c62,
	   c124 as c63,
	   c123 as c64,
	   c122 as c65,
	   c121 as c66,
	   c120 as c67,
	   c119 as c68,
	   c118 as c69,
	   c117 as c70,
	   c116 as c71,
	   c115 as c72,
	   c114 as c73,
	   c113 as c74,
	   c112 as c75,
	   c111 as c76,
	   c110 as c77,
	   c109 as c78,
	   c108 as c79,
	   c104 as c80,
	   c107 as c81,
	   XCOUNT(c107 for c98) as c82,
	   c106 as c83,
	   XSUM(c106 for c98) as c84,
	   c105 as c85,
	   XSUM(c105 for c98) as c86,
	   CASE WHEN ((XSUM(c105 for c98)) = 0) THEN (0) ELSE (c104) END as c87,
	   CASE WHEN ((XSUM(c105 for c98)) <> 0) THEN ((CASE WHEN ((XSUM(c105 for c98)) = 0) THEN (0) ELSE (c104) END) / (XCOUNT(c107 for c98))) ELSE (0) END as c88,
	   c103 as c89,
	   c102 as c90,
	   c101 as c91,
	   c100 as c92,
	   c99 as c93
from 
(select (T1."No_") as c98,
	   T10."No_" || ' - ' || T10."Name" as c99,
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T11."Description") END as c100,
	   T11."Description" as c101,
	   T10."Name" as c102,
	   T10."No_" as c103,
	   T8."Summe_EW_FL_EK_Rechnung" as c104,
	   CASE WHEN ((T9."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T9."Gen_ Prod_ Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN ((cast_float(T9."Amount"))) ELSE (0) END as c105,
	   CASE WHEN (CASE WHEN ((T9."Gen_ Prod_ Posting Group" LIKE '%FZG%') or (T9."Gen_ Prod_ Posting Group" IN ('822_SONST','824_SONST','827_SONST','821_SONST','823_SONST','825_SONST','826_SONST'))) THEN ((cast_float(T9."Amount"))) ELSE (0) END <> 0) THEN ((cast_float(T9."Quantity")) * (cast_float(T9."Unit Cost"))) ELSE (0) END as c106,
	   T8."Auftragsnummer" as c107,
	   T8."Posting Date" as c108,
	   T8."Order Date" as c109,
	   T8."Your Reference" as c110,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 5) THEN (T1."Service Order No_") ELSE null END as c111,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 5) and ((CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T2."Service Posting Group" IS NULL) THEN (T3."Service Posting Group") ELSE (T2."Service Posting Group") END) || ' - ' || (CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END) || ' - ' || T7."Name") WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 5) and ((CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || (CASE WHEN (T2."Service Posting Group" IS NULL) THEN (T3."Service Posting Group") ELSE (T2."Service Posting Group") END) || ' - ' || T7."Name") ELSE null END as c112,
	   CASE WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END) IS NOT NULL)) THEN (T1."No_" || ' - ' || T1."Service Order No_" || ' - ' || (CASE WHEN (T2."Service Posting Group" IS NULL) THEN (T3."Service Posting Group") ELSE (T2."Service Posting Group") END) || ' - ' || (CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END) || ' - ' || T7."Name") WHEN (((extract(DAY FROM (now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END) IS NULL)) THEN (T1."No_" || ' - ' || T1."Service Order No_" || ' - ' || (CASE WHEN (T2."Service Posting Group" IS NULL) THEN (T3."Service Posting Group") ELSE (T2."Service Posting Group") END) || ' - ' || T7."Name") ELSE null END as c113,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Posting Date")) <= 180) THEN (T1."No_" || ' - ' || T1."Service Order No_" || ' - ' || T7."Name") ELSE ('Aufträge älter 180 Tage') END as c114,
	   T7."No_" || ' - ' || T7."Name" as c115,
	   T7."Name" as c116,
	   T7."No_" as c117,
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T6."Description") END as c118,
	   T6."Description" as c119,
	   T6."Code" as c120,
	   CASE WHEN (T2."Service Posting Group" IS NULL) THEN (T3."Service Posting Group") ELSE (T2."Service Posting Group") END as c121,
	   T3."Service Posting Group" as c122,
	   T2."Service Posting Group" as c123,
	   T1."Make Code" as c124,
	   T1."VIN" || ' - ' || T1."Model" as c125,
	   T1."Model" as c126,
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') ELSE ('Extern') END as c127,
	   CASE WHEN (T1."Location Code" = 'ESS') THEN ('10') WHEN (T1."Location Code" = 'OST') THEN ('20') WHEN (T1."Location Code" = 'KIR') THEN ('30') WHEN (T1."Location Code" = 'GÖP') THEN ('40') ELSE null END as c128,
	   CASE WHEN (T4."No_" IS NULL) THEN (T5."First Name" || ' ' || T5."Last Name") ELSE (T4."First Name" || ' ' || T4."Last Name") END as c129,
	   T5."Last Name" as c130,
	   T5."First Name" as c131,
	   T5."No_" as c132,
	   T4."Last Name" as c133,
	   T4."First Name" as c134,
	   T4."No_" as c135,
	   T3."Service Advisor No_" as c136,
	   T2."Service Advisor No_" as c137,
	   T1."Type" as c138,
	   T1."Prod_ Year" as c139,
	   T1."Model No_" as c140,
	   T1."Model Code" as c141,
	   T1."Mileage" as c142,
	   T1."VIN" as c143,
	   T1."Branch Code" as c144,
	   T1."Service Order Line No_" as c145,
	   T1."Customer Group Code" as c146,
	   T1."Inv_ Discount %" as c147,
	   T1."Service Order No_" as c148,
	   T1."Order Type" as c149,
	   T1."User ID" as c150,
	   T1."Order No_ Series" as c151,
	   T1."No_ Series" as c152,
	   T1."Shipping Agent Code" as c153,
	   T1."Area" as c154,
	   T1."External Document No_" as c155,
	   T1."Document Date" as c156,
	   T1."Correction" as c157,
	   T1."Sell-to City" as c158,
	   T1."Sell-to Address" as c159,
	   T1."Sell-to Customer Name" as c160,
	   T1."Transaction Type" as c161,
	   T1."Gen_ Bus_ Posting Group" as c162,
	   T1."Job No_" as c163,
	   T1."On Hold" as c164,
	   T1."Order No_" as c165,
	   T1."Salesperson Code" as c166,
	   T1."Allow Quantity Disc_" as c167,
	   T1."Prices Including VAT" as c168,
	   T1."Price Group Code" as c169,
	   T1."Customer Posting Group" as c170,
	   T1."Department Code" as c171,
	   T1."Location Code" as c172,
	   T1."Payment Discount %" as c173,
	   T1."Payment Terms Code" as c174,
	   T1."Posting Date" as c175,
	   T1."Order Date" as c176,
	   T1."Bill-to City" as c177,
	   T1."Bill-to Address" as c178,
	   T1."Bill-to Name" as c179,
	   T1."Bill-to Customer No_" as c180,
	   T1."Sell-to Customer No_" as c181
from QSS."C:\GAPS_BMW\Portal\System\IQD\Service\Einkaufsrechnungen_FL_Summe.ims" T8,
	(((("DMS1"."dbo"."Automag GmbH$Sales Invoice Header" T1 left outer join "DMS1"."dbo"."Automag GmbH$Customer" T7 on T7."No_" = T1."Bill-to Customer No_") left outer join "DMS1"."dbo"."Automag GmbH$Customer Group" T6 on T7."Customer Group Code" = T6."Code") left outer join "DMS1"."dbo"."Automag GmbH$Customer" T10 on T1."Sell-to Customer No_" = T10."No_") left outer join "DMS1"."dbo"."Automag GmbH$Customer Group" T11 on T10."Customer Group Code" = T11."Code"),
	(((("DMS1"."dbo"."Automag GmbH$Sales Invoice Line" T9 left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Header" T2 on T9."Service Order No_" = T2."No_") left outer join "DMS1"."dbo"."Automag GmbH$Service Header" T3 on T9."Service Order No_" = T3."No_") left outer join "DMS1"."dbo"."Automag GmbH$Employee" T4 on T2."Service Advisor No_" = T4."No_") left outer join "DMS1"."dbo"."Automag GmbH$Employee" T5 on T3."Service Advisor No_" = T5."No_")
where (T1."No_" = T9."Document No_") and (T1."Service Order No_" = T8."Auftragsnummer")
and ((((T1."No_" LIKE 'W%') and (not T9."Type" IN (0,11,12))) and (T1."Posting Date" >= TIMESTAMP '2017-01-01 00:00:00.000')) and (not T1."Service Order No_" IN ('NASISPA')))
order by c98 asc
) D1

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 Address
COLUMN,5,Bill-to City
COLUMN,6,Order Date
COLUMN,7,Posting Date
COLUMN,8,Payment Terms Code
COLUMN,9,Payment Discount %
COLUMN,10,Location Code
COLUMN,11,Department Code
COLUMN,12,Make Code
COLUMN,13,Customer Posting Group
COLUMN,14,Price Group Code
COLUMN,15,Prices Including Vat
COLUMN,16,Allow Quantity Disc
COLUMN,17,Salesperson Code
COLUMN,18,Order No
COLUMN,19,On Hold
COLUMN,20,Job No
COLUMN,21,Gen Bus Posting Group
COLUMN,22,Transaction Type
COLUMN,23,Sell-to Customer Name
COLUMN,24,Sell-to Address
COLUMN,25,Sell-to City
COLUMN,26,Correction
COLUMN,27,Document Date
COLUMN,28,External Document No
COLUMN,29,Area
COLUMN,30,Shipping Agent Code
COLUMN,31,No Series
COLUMN,32,Order No Series
COLUMN,33,User Id
COLUMN,34,Order Type
COLUMN,35,Service Order No
COLUMN,36,Inv Discount %
COLUMN,37,Customer Group Code
COLUMN,38,Service Order Line No
COLUMN,39,Branch Code
COLUMN,40,Vin
COLUMN,41,Mileage
COLUMN,42,Model Code
COLUMN,43,Model No
COLUMN,44,Prod Year
COLUMN,45,Model_ori
COLUMN,46,Type_Header
COLUMN,47,Service Advisor No_Archiv
COLUMN,48,Service Advisor No_oA
COLUMN,49,No_für_Archiv
COLUMN,50,First Name_für_Archiv
COLUMN,51,Last Name_für_Archiv
COLUMN,52,No
COLUMN,53,First Name
COLUMN,54,Last Name
COLUMN,55,Serviceberater
COLUMN,56,Hauptbetrieb
COLUMN,57,Standort
COLUMN,58,Umsatzart
COLUMN,59,Fabrikat
COLUMN,60,Model
COLUMN,61,Fahrzeug
COLUMN,62,Marke
COLUMN,63,Service Posting Group_für_Archiv
COLUMN,64,Service Posting Group
COLUMN,65,Auftragsart
COLUMN,66,Cust_Gr_Code
COLUMN,67,Cust_Gr_Description
COLUMN,68,Kundenart
COLUMN,69,Cust_No
COLUMN,70,Cust_Name
COLUMN,71,Kunde
COLUMN,72,Order Number
COLUMN,73,Order Number_Rg_Ausg
COLUMN,74,Order Number_Rg_Ausg_2
COLUMN,75,Order Number_Rg_Ausg_1
COLUMN,76,Your Reference
COLUMN,77,Order Date
COLUMN,78,Posting Date
COLUMN,79,Summe Ew Fl Ek Rechnung
COLUMN,80,Auftragsnummer
COLUMN,81,Anzahl Datensätze
COLUMN,82,Einsatz FL_ori
COLUMN,83,Summe Einsatz FL_ori
COLUMN,84,Umsatz FL_ori
COLUMN,85,Summe Umsatz FL_ori
COLUMN,86,Summe Ew Fl Ek Rechnung_bereinigt
COLUMN,87,EW FL final
COLUMN,88,Cust_No_Verkaufskunde
COLUMN,89,Cust_Name_Verkaufskunde
COLUMN,90,Cust_Group_Description_Verkaufskunde
COLUMN,91,Kundenart_Verkaufskunde
COLUMN,92,Kunde_Verkaufskunde