COGNOS QUERY STRUCTURE,1,1 DATABASE,Locosoft_GC DATASOURCENAME,C:\GlobalCube\System\LOCOSOFT\IQD\serv_teile\Aftersales_Rechnungen_verk_AW_final.imr TITLE,Aftersales_Rechnungen_verk_AW_final.imr BEGIN SQL select T1."invoice_type" as c1, T1."invoice_number" as c2, T1."subsidiary" as c3, T1."invoice_date" as c4, T1."service_date" as c5, T1."is_canceled" as c6, T1."vehicle_number" as c7, (od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || '_' || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) as c8, '1' as c9, '0' || (cast_numberToString(cast_integer(T1."subsidiary"))) as c10, T2."Serviceberater" as c11, CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T3."customer_number" = 100001)) THEN ('intern') ELSE ('extern') END as c12, T4."description" as c13, T4."description" as c14, T5."description" as c15, (od_left((ucase(T5."description")),3)) as c16, (od_left((od_left((cast_numberToString(cast_integer(T1."vehicle_number"))),5)) || ' - ' || T5."description",100)) as c17, CASE WHEN ((od_left((cast_numberToString(cast_integer(T6."type"))),1)) IN ('1','6')) THEN ('41 - After Sales Kundendienst eigene Werkstatt') WHEN (T6."type" IN (40,41,44,45,46,47,48,49,88,89,50,90)) THEN ('41 - After Sales Kundendienst eigene Werkstatt') WHEN ((od_left((cast_numberToString(cast_integer(T6."type"))),1)) IN ('2')) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN (T6."type" IN (42,92)) THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung') WHEN ((od_left((cast_numberToString(cast_integer(T6."type"))),1)) IN ('3')) THEN ('45 - After Sales Kundendienst eigene Lackiererei') WHEN (T6."type" IN (43,93)) THEN ('45 - After Sales Kundendienst eigene Lackiererei') ELSE null END as c18, CASE WHEN (T4."description" = 'Ford') THEN ('1') WHEN (T4."description" = 'Nissan') THEN ('3') WHEN (T4."description" = 'Suzuki') THEN ('2') ELSE ('9') END as c19, 'Service' as c20, T3."zip_code" as c21, CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END as c22, T7."time_units" as c23, (cast_float(T7."time_units")) as c24, 10 as c25, ((cast_float(T7."time_units"))) / 10 as c26, CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) || ' - ' || T2."Serviceberater" || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) ELSE ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) END as c27, CASE WHEN ((extract(DAY FROM (now()) - (cdatetime(T1."invoice_date")))) <= 4) THEN ((CASE WHEN (T2."Serviceberater" IS NOT NULL) THEN ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) || ' - ' || T2."Serviceberater" || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) ELSE ((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) || ' - ' || (CASE WHEN (T3."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T3."customer_number"))),7)) || ' - ' || T3."first_name" || ' ' || T3."family_name") END)) END)) ELSE null END as c28, (od_left((cast_numberToString(cast_integer(T8."employee_number"))),4)) || ' - ' || T8."name" as c29 from (((((((("dbo"."invoices" T1 left outer join "dbo"."customers_suppliers" T3 on T3."customer_number" = T1."paying_customer") left outer join "dbo"."vehicles" T9 on T1."vehicle_number" = T9."internal_number") left outer join "dbo"."makes" T4 on T9."make_number" = T4."make_number") left outer join "dbo"."models" T5 on (T9."make_number" = T5."make_number") and (T9."model_code" = T5."model_code")) left outer join "dbo"."labours" T7 on (T7."invoice_number" = T1."invoice_number") and (T7."invoice_type" = T1."invoice_type")) left outer join "dbo"."charge_type_descriptions" T6 on T6."type" = T7."charge_type") left outer join "dbo"."employees" T8 on T1."creating_employee" = T8."employee_number") left outer join QSS."C:\GlobalCube\System\LOCOSOFT\Catalogs\..\IQD\Serv_Teile\Serviceberater_Rechnung.ims" T2 on T1."invtype_invnr" = T2."invtype_invnr") where (((((T1."invoice_type" BETWEEN 2 AND 6) and (T1."is_canceled" <> 1)) and (T1."invoice_date" >= DATE '2017-01-01')) and ((((cast_float(T7."time_units"))) <> 0) and (((cast_float(T7."time_units"))) IS NOT NULL))) and (T7."mechanic_no" IS NOT NULL)) order by c8 asc,c2 asc END SQL COLUMN,0,Invoice Type COLUMN,1,Invoice Number COLUMN,2,Subsidiary COLUMN,3,Invoice Date COLUMN,4,Service Date COLUMN,5,Is Canceled COLUMN,6,Vehicle Number COLUMN,7,Invoice_Type_Invoice_Number COLUMN,8,Hauptbetrieb COLUMN,9,Standort COLUMN,10,Serviceberater COLUMN,11,Umsatzart COLUMN,12,Fabrikat COLUMN,13,Description_Makes COLUMN,14,Description_Models COLUMN,15,Model COLUMN,16,Fahrzeug COLUMN,17,Kostenstelle COLUMN,18,Marke COLUMN,19,Auftragsart COLUMN,20,Geschäftsart COLUMN,21,Kunde COLUMN,22,Time Units COLUMN,23,Time Units_Zahl COLUMN,24,AW/Std. COLUMN,25,verk. Std. COLUMN,26,Order Number COLUMN,27,Order Number_Rg_Ausg COLUMN,28,Kundenart