C11/System/ARIntelligence/IQD/Service/Service_Eröffnung_ServberamFZG.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_Eröffnung_ServberamFZG.imr
TITLE,Service_Eröffnung_ServberamFZG.imr
BEGIN SQL
select T1."Document Type" as c1,
	   T1."No_" as c2,
	   T1."Sell-to Customer No_" as c3,
	   T1."Bill-to Customer No_" as c4,
	   T1."Bill-to Name" as c5,
	   T1."Bill-to Name 2" as c6,
	   T1."Order Date" as c7,
	   T1."Location Code" as c8,
	   T1."Department Code" as c9,
	   T1."Make Code" as c10,
	   T1."Customer Posting Group" as c11,
	   T1."Salesperson Code" as c12,
	   T1."Order Class" as c13,
	   T1."Sell-to Customer Name" as c14,
	   T1."Sell-to Customer Name 2" as c15,
	   T1."Document Date" as c16,
	   T1."No_ Series" as c17,
	   T1."VIN" as c18,
	   T1."Mileage" as c19,
	   T1."Model Code" as c20,
	   T1."Model No_" as c21,
	   T1."Prod_ Year" as c22,
	   T1."Model" as c23,
	   T1."Service Advisor No__Scheduling" as c24,
	   T1."Service Advisor No__Pick-Up" as c25,
	   T1."Service Advisor No_" as c26,
	   '1' as c27,
	   (od_left(T1."Department Code",2)) as c28,
	   '' as c29,
	   CASE WHEN (T1."Make Code" IN ('BMW-MINI')) THEN (T1."Make Code") ELSE ('BMW') END as c30,
	   T2."Customer Group Code" as c31,
	   T2."Customer Group Code" as c32,
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as c33,
	   T1."Model" as c34,
	   T1."VIN" || ' - ' || T1."Model" as c35,
	   T2."No_" as c36,
	   T2."Name" as c37,
	   T2."No_" || ' - ' || T2."Name" as c38,
	   T3."Description" as c39,
	   CASE WHEN (T2."No_" LIKE 'INT%') THEN ('Intern') ELSE (T3."Description") END as c40,
	   T1."No_" || ' - ' || T2."Name" as c41,
	   T1."Order Date" as c42,
	   1 as c43,
	   CASE WHEN (T1."Location Code" = 'LBS') THEN ('10') WHEN (T1."Location Code" = 'WLS') THEN ('20') WHEN (T1."Location Code" = 'STA') THEN ('30') WHEN (T1."Location Code" = 'GER') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') ELSE null END as c44,
	   '' as c45,
	   T4."First Name" as c46,
	   T4."Last Name" as c47,
	   T4."First Name" || ' ' || T4."Last Name" as c48,
	   T1."Service Posting Group" as c49,
	   T1."Gen_ Prod_ Posting Group" as c50,
	   CASE WHEN ((T5."No_" IN ('0052050','0052051','0052052','0052054','0052053','0052055')) and (T5."Quantity" > 0)) THEN (1) WHEN ((T5."No_" IN ('0052050','0052051','0052052','0052054','0052053','0052055')) and (T5."Quantity" < 0)) THEN (-1) ELSE (0) END as c51,
	   CASE WHEN ((CASE WHEN ((T5."No_" IN ('0052050','0052051','0052052','0052054','0052053','0052055')) and (T5."Quantity" > 0)) THEN (1) WHEN ((T5."No_" IN ('0052050','0052051','0052052','0052054','0052053','0052055')) and (T5."Quantity" < 0)) THEN (-1) ELSE (0) END) <> 0) THEN (T5."No_" || ' - ' || T5."Description") ELSE null END as c52,
	   T6."Resource Group No_" as c53,
	   CASE WHEN (T6."Resource Group No_" = 'SB') THEN ('SB') ELSE ('Rest') END as c54
from "DMS1"."dbo"."Automag GmbH$Service Line" T5,
	(((("DMS1"."dbo"."Automag GmbH$Service Header" T1 left outer join "DMS1"."dbo"."Automag GmbH$Customer" T2 on T2."No_" = T1."Sell-to Customer No_") left outer join "DMS1"."dbo"."Automag GmbH$Customer Group" T3 on T2."Customer Group Code" = T3."Code") left outer join "DMS1"."dbo"."Automag GmbH$Employee" T4 on T4."No_" = T1."Service Advisor No_") left outer join "DMS1"."dbo"."Automag GmbH$Resource" T6 on T1."Service Advisor No_" = T6."No_")
where (T1."No_" = T5."Document No_")
and (T1."Document Type" <> 0)
order by c2 asc

END SQL
COLUMN,0,Document Type
COLUMN,1,No
COLUMN,2,Sell-to Customer No
COLUMN,3,Bill-to Customer No
COLUMN,4,Bill-to Name
COLUMN,5,Bill-to Name 2
COLUMN,6,Order Date
COLUMN,7,Location Code
COLUMN,8,Department Code
COLUMN,9,Make Code
COLUMN,10,Customer Posting Group
COLUMN,11,Salesperson Code
COLUMN,12,Order Class
COLUMN,13,Sell-to Customer Name
COLUMN,14,Sell-to Customer Name 2
COLUMN,15,Document Date
COLUMN,16,No Series
COLUMN,17,Vin
COLUMN,18,Mileage
COLUMN,19,Model Code
COLUMN,20,Model No
COLUMN,21,Prod Year
COLUMN,22,Model_ori
COLUMN,23,Service Advisor No Scheduling
COLUMN,24,Service Advisor No Pick-up
COLUMN,25,Service Advisor No
COLUMN,26,Hauptbetrieb
COLUMN,27,Standort_Department
COLUMN,28,Umsatzart
COLUMN,29,Marke
COLUMN,30,Customer Group Code
COLUMN,31,Kundenart_ori
COLUMN,32,Fabrikat
COLUMN,33,Model
COLUMN,34,Fahrzeug
COLUMN,35,Cust_No
COLUMN,36,Cust_Name
COLUMN,37,Kunde
COLUMN,38,Cust_Group_Description
COLUMN,39,Kundenart
COLUMN,40,Order Number
COLUMN,41,Auftragsdatum
COLUMN,42,Durchgänge eröffnet_ori
COLUMN,43,Standort
COLUMN,44,Order Number_Rg_Ausg
COLUMN,45,First Name
COLUMN,46,Last Name
COLUMN,47,Serviceberater
COLUMN,48,Auftragsart
COLUMN,49,Auftragsart_1
COLUMN,50,Serv.beratung am FZG
COLUMN,51,Arbeitsgang
COLUMN,52,Resource Group No
COLUMN,53,SB_Gruppe