C11/System/LOCOSOFT/IQD/Serv_Teile/Aftersales_Rechnungen_neu_Fokus.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Locosoft_GC
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,
	   '0' || (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