COGNOS QUERY STRUCTURE,1,1 DATABASE,GC_LOCOSOFT DATASOURCENAME,C:\GlobalCube\System\LOCOSOFT\IQD\serv_teile\Aftersales_Rechnungen_neu_Fokus.imr TITLE,Aftersales_Rechnungen_neu_Fokus.imr BEGIN SQL select T1."invoice_type" as c1, T2."type" as c2, T2."description" as c3, T1."invoice_number" as c4, T1."subsidiary" as c5, T1."paying_customer" as c6, T1."order_customer" as c7, T1."invoice_date" as c8, T1."service_date" as c9, T1."is_canceled" as c10, T1."cancelation_number" as c11, T1."cancelation_date" as c12, T1."cancelation_employee" as c13, T1."is_own_vehicle" as c14, T1."is_credit" as c15, T1."credit_invoice_type" as c16, T1."credit_invoice_number" as c17, T1."odometer_reading" as c18, T1."creating_employee" as c19, T1."internal_cost_account" as c20, T1."vehicle_number" as c21, T1."full_vat_basevalue" as c22, T1."full_vat_percentage" as c23, T1."full_vat_value" as c24, T1."reduced_vat_basevalue" as c25, T1."reduced_vat_percentage" as c26, T1."reduced_vat_value" as c27, T1."used_part_vat_value" as c28, T1."job_amount_net" as c29, T1."job_amount_gross" as c30, T1."job_rebate" as c31, T1."part_amount_net" as c32, T1."part_amount_gross" as c33, T1."part_rebate" as c34, T1."part_disposal" as c35, T1."total_gross" as c36, T1."total_net" as c37, (od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9)) as c38, '1' as c39, (cast_numberToString(cast_integer(T1."subsidiary"))) as c40, T3."employee_number" as c41, T3."name" as c42, T3."mechanic_number" as c43, T3."salesman_number" as c44, T3."is_business_executive" as c45, T3."is_master_craftsman" as c46, (od_left((cast_numberToString(cast_integer(T3."employee_number"))),4)) || ' - ' || T3."name" as c47, T4."customer_number" as c48, T4."first_name" as c49, T4."family_name" as c50, CASE WHEN (T4."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."first_name" || ' ' || T4."family_name") END as c51, T5."net_price_in_order" as c52, CASE WHEN (T1."invoice_type" = 6) THEN ('GWL') WHEN ((T1."invoice_type" = 4) or (T4."customer_number" IN (1000000,1000001,1000002,1000003,1000010))) THEN ('intern') ELSE ('extern') END as c53, CASE WHEN (T1."invoice_type" = 5) THEN ('Teile') ELSE ('Service') END as c54, '' as c55, T6."make_number" as c56, T6."free_form_make_text" as c57, T6."model_code" as c58, T6."free_form_model_text" as c59, T7."description" as c60, T8."description" as c61, T7."description" as c62, T8."description" as c63, (od_left((ucase(T8."description")),3)) as c64, (od_left((od_left((cast_numberToString(cast_integer(T1."vehicle_number"))),5)) || ' - ' || T8."description",100)) as c65, CASE WHEN ((CASE WHEN (T1."invoice_type" = 5) THEN (((od_left((cast_numberToString(cast_integer(T3."employee_number"))),4)) || ' - ' || T3."name")) ELSE (T9."Serviceberater") END) IS NOT NULL) THEN (((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9))) || ' - ' || (CASE WHEN (T1."invoice_type" = 5) THEN (((od_left((cast_numberToString(cast_integer(T3."employee_number"))),4)) || ' - ' || T3."name")) ELSE (T9."Serviceberater") END) || ' - ' || (CASE WHEN (T4."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."first_name" || ' ' || T4."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 (T4."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."first_name" || ' ' || T4."family_name") END)) END as c66, (cdatetime(T1."invoice_date")) as c67, CASE WHEN ((extract(DAY FROM (now()) - ((cdatetime(T1."invoice_date"))))) <= 4) THEN ((CASE WHEN ((CASE WHEN (T1."invoice_type" = 5) THEN (((od_left((cast_numberToString(cast_integer(T3."employee_number"))),4)) || ' - ' || T3."name")) ELSE (T9."Serviceberater") END) IS NOT NULL) THEN (((od_left((cast_numberToString(cast_integer(T1."invoice_type"))),1)) || (od_left((cast_numberToString(cast_integer(T1."invoice_number"))),9))) || ' - ' || (CASE WHEN (T1."invoice_type" = 5) THEN (((od_left((cast_numberToString(cast_integer(T3."employee_number"))),4)) || ' - ' || T3."name")) ELSE (T9."Serviceberater") END) || ' - ' || (CASE WHEN (T4."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."first_name" || ' ' || T4."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 (T4."first_name" IS NULL) THEN ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."family_name") ELSE ((od_left((cast_numberToString(cast_integer(T4."customer_number"))),7)) || ' - ' || T4."first_name" || ' ' || T4."family_name") END)) END)) ELSE null END as c68, T9."Serviceberater" as c69, CASE WHEN (T1."invoice_type" = 5) THEN (((od_left((cast_numberToString(cast_integer(T3."employee_number"))),4)) || ' - ' || T3."name")) ELSE (T9."Serviceberater") END as c70, T5."order_number" as c71, T5."order_position" as c72, T5."order_position_line" as c73, T5."subsidiary" as c74, T5."is_invoiced" as c75, T5."invoice_type" as c76, T5."invoice_number" as c77, T5."employee_no" as c78, T5."mechanic_no" as c79, T5."labour_operation_id" as c80, T5."is_nominal" as c81, T5."net_price_in_order" as c82, T5."rebate_percent" as c83, T5."goodwill_percent" as c84, T5."charge_type" as c85, T5."time_units" as c86, T5."text_line" as c87, 1 as c88, (ltrim(T5."labour_operation_id")) as c89 from (((((((("dbo"."invoices" T1 left outer join "dbo"."invoice_types" T2 on T2."type" = T1."invoice_type") left outer join "dbo"."employees" T3 on T1."creating_employee" = T3."employee_number") left outer join "dbo"."customers_suppliers" T4 on T4."customer_number" = T1."paying_customer") left outer join "dbo"."labours" T5 on (T5."invoice_number" = T1."invoice_number") and (T5."invoice_type" = T1."invoice_type")) left outer join "dbo"."vehicles" T6 on T1."vehicle_number" = T6."internal_number") left outer join "dbo"."makes" T7 on T6."make_number" = T7."make_number") left outer join "dbo"."models" T8 on (T6."make_number" = T8."make_number") and (T6."model_code" = T8."model_code")) left outer join QSS."C:\GlobalCube\System\LOCOSOFT\Catalogs\..\IQD\Serv_Teile\Serviceberater_Rechnung.ims" T9 on T1."invtype_invnr" = T9."invtype_invnr") where ((((T1."invoice_type" BETWEEN 2 AND 6) and (T1."invoice_date" >= DATE '2019-01-01')) and (T1."is_canceled" <> 1)) and ((ltrim(T5."labour_operation_id")) IN ('KC1','KC2','BZ','KLIMA1','KLIMA2'))) order by c66 asc,c72 asc,c38 asc END SQL COLUMN,0,Invoice Type COLUMN,1,Type_Invoice_Types COLUMN,2,Description_Invoice_Types COLUMN,3,Invoice Number COLUMN,4,Subsidiary COLUMN,5,Paying Customer COLUMN,6,Order Customer COLUMN,7,Invoice Date COLUMN,8,Service Date COLUMN,9,Is Canceled COLUMN,10,Cancelation Number COLUMN,11,Cancelation Date COLUMN,12,Cancelation Employee COLUMN,13,Is Own Vehicle COLUMN,14,Is Credit COLUMN,15,Credit Invoice Type COLUMN,16,Credit Invoice Number COLUMN,17,Odometer Reading COLUMN,18,Creating Employee COLUMN,19,Internal Cost Account COLUMN,20,Vehicle Number COLUMN,21,Full Vat Basevalue COLUMN,22,Full Vat Percentage COLUMN,23,Full Vat Value COLUMN,24,Reduced Vat Basevalue COLUMN,25,Reduced Vat Percentage COLUMN,26,Reduced Vat Value COLUMN,27,Used Part Vat Value COLUMN,28,Job Amount Net COLUMN,29,Job Amount Gross COLUMN,30,Job Rebate COLUMN,31,Part Amount Net COLUMN,32,Part Amount Gross COLUMN,33,Part Rebate COLUMN,34,Part Disposal COLUMN,35,Total Gross COLUMN,36,Total Net COLUMN,37,Inv_Type_Invoice_Number COLUMN,38,Hauptbetrieb COLUMN,39,Standort COLUMN,40,Employee Number_Employees COLUMN,41,Name_Employees COLUMN,42,Mechanic Number_Employees COLUMN,43,Salesman Number_Employees COLUMN,44,Is Business Executive_Employees COLUMN,45,Is Master Craftsman_Employees COLUMN,46,Serviceberater_Rg_Steller COLUMN,47,Customer Number_Customers_Suppliers COLUMN,48,First Name_Customers_Suppliers COLUMN,49,Family Name_Customers_Suppliers COLUMN,50,Kunde COLUMN,51,Betrag COLUMN,52,Umsatzart COLUMN,53,Auftragsart COLUMN,54,Geschäftsart COLUMN,55,Make Number COLUMN,56,Free Form Make Text COLUMN,57,Model Code COLUMN,58,Free Form Model Text COLUMN,59,Description_Makes COLUMN,60,Description_Models COLUMN,61,Fabrikat COLUMN,62,Model_Detail COLUMN,63,Model COLUMN,64,Fahrzeug COLUMN,65,Order Number COLUMN,66,Invoice_Date_Uhrzeit COLUMN,67,Order Number_Rg_Ausg COLUMN,68,Serviceberater_order_pos COLUMN,69,Serviceberater COLUMN,70,Order Number COLUMN,71,Order Position COLUMN,72,Order Position Line COLUMN,73,Subsidiary COLUMN,74,Is Invoiced COLUMN,75,Invoice Type COLUMN,76,Invoice Number COLUMN,77,Employee No COLUMN,78,Mechanic No COLUMN,79,Labour Operation Id COLUMN,80,Is Nominal COLUMN,81,Net Price In Order COLUMN,82,Rebate Percent COLUMN,83,Goodwill Percent COLUMN,84,Charge Type COLUMN,85,Time Units COLUMN,86,Text Line COLUMN,87,Menge Fokus COLUMN,88,Service_Fokus_Gruppe