C11/System/LOCOSOFT/IQD/Serv_Teile/Aftersales_Rechnungen_neu.sql

select "Invoice Type",
	   "Type_Invoice_Types",
	   "Description_Invoice_Types",
	   "Invoice Number_2",
	   "Subsidiary",
	   "Paying Customer",
	   "Order Customer",
	   "Invoice Date",
	   "Service Date",
	   "Is Canceled",
	   "Cancelation Number",
	   "Cancelation Date",
	   "Cancelation Employee",
	   "Is Own Vehicle",
	   "Is Credit",
	   "Credit Invoice Type",
	   "Credit Invoice Number",
	   "Odometer Reading",
	   "Creating Employee",
	   "Internal Cost Account",
	   "Vehicle Number",
	   "Full Vat Basevalue",
	   "Full Vat Percentage",
	   "Full Vat Value",
	   "Reduced Vat Basevalue",
	   "Reduced Vat Percentage",
	   "Reduced Vat Value",
	   "Used Part Vat Value",
	   "Job Amount Net",
	   "Job Amount Gross",
	   "Job Rebate",
	   "Part Amount Net",
	   "Part Amount Gross",
	   "Part Rebate",
	   "Part Disposal",
	   "Total Gross",
	   "Total Net",
	   "Inv_Type_Invoice_Number",
	   "Accounting Date",
	   "Document Type",
	   "Document Number",
	   "Position In Document",
	   "Customer Number",
	   "Nominal Account Number",
	   "Is Balanced",
	   "Clearing Number",
	   "Document Date",
	   "Posted Value",
	   "Debit Or Credit",
	   "Posted Count",
	   "Branch Number",
	   "Customer Contra Account",
	   "Nominal Contra Account",
	   "Contra Account Text",
	   "Account Form Page Number",
	   "Account Form Page Line",
	   "Serial Number Each Month",
	   "Employee Number",
	   "Invoice Date_falsch",
	   "Invoice Number",
	   "Dunning Level",
	   "Last Dunning Date",
	   "Journal Page",
	   "Journal Line",
	   "Cash Discount",
	   "Term Of Payment",
	   "Posting Text",
	   "Vehicle Reference",
	   "Vat Id Number",
	   "Account Statement Number",
	   "Account Statement Page",
	   "Vat Key",
	   "Days For Cash Discount",
	   "Day Of Actual Accounting",
	   "Skr51 Branch",
	   "Skr51 Make",
	   "Skr51 Cost Center",
	   "Skr51 Sales Channel",
	   "Skr51 Cost Unit",
	   "Previously Used Account No",
	   "Free Form Accounting Text",
	   "Free Form Document Text",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "Accounting Date" as "Invoice Date_accounting",
	   "Employee Number_Employees",
	   "Name_Employees",
	   "Mechanic Number_Employees",
	   "Salesman Number_Employees",
	   "Is Business Executive_Employees",
	   "Is Master Craftsman_Employees",
	   "Serviceberater_Rg_Steller",
	   "Customer Number_Customers_Suppliers",
	   "First Name_Customers_Suppliers",
	   "Family Name_Customers_Suppliers",
	   "Kunde",
	   "Betrag",
	   "Kostenstelle",
	   "Skr51 Make" as "Marke",
	   "Kundenart",
	   "KST_1_Stelle",
	   "Umsatzart",
	   "Auftragsart",
	   '' as "Geschäftsart",
	   "Make Number",
	   "Free Form Make Text",
	   "Model Code",
	   "Free Form Model Text",
	   "Description_Makes",
	   "Description_Models",
	   "Description_Makes" as "Fabrikat",
	   "Description_Models" as "Model_Detail",
	   "Model",
	   (0) as "Umsatz Lohn",
	   (0) as "Umsatz FL",
	   (0) as "Einsatz FL",
	   (0) as "Umsatz Teile",
	   (0) as "Einsatz Teile",
	   (0) as "Umsatz MW",
	   (0) as "Einsatz MW",
	   (0) as "Umsatz Sonst.",
	   (0) as "Einsatz Sonst.",
	   1 as "DG_1",
	   COUNT("Invoice Type") OVER (partition by "Order Number") as "DG_2",
	   1 / (COUNT("Invoice Type") OVER (partition by "Order Number")) as "DG",
	   "Fahrzeug",
	   "Order Number",
	   "Invoice_Date_Uhrzeit",
	   "Order Number_Rg_Ausg",
	   '' as "Konto Nr",
	   '' as "Konto Bezeichnung",
	   '' as "Ebene1",
	   '' as "Ebene2",
	   '' as "Ebene3",
	   '' as "Ebene4",
	   "Serviceberater_order_pos",
	   "Serviceberater"
from 
(select (CASE WHEN ((CASE WHEN (T1."invoice_type" = 5) THEN (((left((((T5."employee_number"))),4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) IS NOT NULL) THEN (((left((((T1."invoice_type"))),1)) + (left((((T1."invoice_number"))),9))) + ' - ' + (CASE WHEN (T1."invoice_type" = 5) THEN (((left((((T5."employee_number"))),4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left((((T4."customer_number"))),7)) + ' - ' + T4."family_name") ELSE ((left((((T4."customer_number"))),7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) ELSE (((left((((T1."invoice_type"))),1)) + (left((((T1."invoice_number"))),9))) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left((((T4."customer_number"))),7)) + ' - ' + T4."family_name") ELSE ((left((((T4."customer_number"))),7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) END) as "Order Number",
	   CASE WHEN (T1."invoice_type" = 5) THEN (((left((((T5."employee_number"))),4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END as "Serviceberater",
	   T10."Serviceberater" as "Serviceberater_order_pos",
	   CASE WHEN ((-1 * datediff(day, (getdate()), ((convert(datetime, T1."invoice_date"))))) <= 4) THEN ((CASE WHEN ((CASE WHEN (T1."invoice_type" = 5) THEN (((left((((T5."employee_number"))),4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) IS NOT NULL) THEN (((left((((T1."invoice_type"))),1)) + (left((((T1."invoice_number"))),9))) + ' - ' + (CASE WHEN (T1."invoice_type" = 5) THEN (((left((((T5."employee_number"))),4)) + ' - ' + T5."name")) ELSE (T10."Serviceberater") END) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left((((T4."customer_number"))),7)) + ' - ' + T4."family_name") ELSE ((left((((T4."customer_number"))),7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) ELSE (((left((((T1."invoice_type"))),1)) + (left((((T1."invoice_number"))),9))) + ' - ' + (CASE WHEN (T4."first_name" IS NULL) THEN ((left((((T4."customer_number"))),7)) + ' - ' + T4."family_name") ELSE ((left((((T4."customer_number"))),7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END)) END)) ELSE null END as "Order Number_Rg_Ausg",
	   (convert(datetime, T1."invoice_date")) as "Invoice_Date_Uhrzeit",
	   (left((left((((T1."vehicle_number"))),5)) + ' - ' + T9."description",100)) as "Fahrzeug",
	   (left((ucase(T9."description")),3)) as "Model",
	   T9."description" as "Description_Models",
	   T8."description" as "Description_Makes",
	   T7."free_form_model_text" as "Free Form Model Text",
	   T7."model_code" as "Model Code",
	   T7."free_form_make_text" as "Free Form Make Text",
	   T7."make_number" as "Make Number",
	   CASE WHEN (T1."invoice_type" = 5) THEN ('Teile') ELSE ('Service') END as "Auftragsart",
	   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 "Umsatzart",
	   (left(((rtrim((((T3."skr51_cost_center"))))) + ' - ' + T6."skr51_cost_center_name"),1)) as "KST_1_Stelle",
	   (rtrim((((T3."skr51_sales_channel"))))) + ' - ' + T6."skr51_sales_channel_name" as "Kundenart",
	   T3."skr51_make" as "Skr51 Make",
	   (rtrim((((T3."skr51_cost_center"))))) + ' - ' + T6."skr51_cost_center_name" as "Kostenstelle",
	   CASE WHEN (T3."debit_or_credit" = 'H') THEN (((convert(float, T3."posted_value"))) / 100 * -1) ELSE (((convert(float, T3."posted_value"))) / 100) END as "Betrag",
	   CASE WHEN (T4."first_name" IS NULL) THEN ((left((((T4."customer_number"))),7)) + ' - ' + T4."family_name") ELSE ((left((((T4."customer_number"))),7)) + ' - ' + T4."first_name" + ' ' + T4."family_name") END as "Kunde",
	   T4."family_name" as "Family Name_Customers_Suppliers",
	   T4."first_name" as "First Name_Customers_Suppliers",
	   T4."customer_number" as "Customer Number_Customers_Suppliers",
	   (left((((T5."employee_number"))),4)) + ' - ' + T5."name" as "Serviceberater_Rg_Steller",
	   T5."is_master_craftsman" as "Is Master Craftsman_Employees",
	   T5."is_business_executive" as "Is Business Executive_Employees",
	   T5."salesman_number" as "Salesman Number_Employees",
	   T5."mechanic_number" as "Mechanic Number_Employees",
	   T5."name" as "Name_Employees",
	   T5."employee_number" as "Employee Number_Employees",
	   T3."accounting_date" as "Accounting Date",
	   '0' + (((T3."branch_number"))) as "Standort",
	   CASE WHEN (T3."skr51_cost_unit" BETWEEN 1 AND 49) THEN ('Neuwagen') WHEN (T3."skr51_cost_unit" BETWEEN 50 AND 59) THEN ('Gebrauchtwagen') WHEN (T3."skr51_cost_unit" BETWEEN 60 AND 69) THEN ('Teile & Zubehör') WHEN (T3."skr51_cost_unit" BETWEEN 70 AND 79) THEN ('Service') WHEN (T3."skr51_cost_unit" = 0) THEN ('Ohne Kostenträger') ELSE null END as "Free Form Document Text",
	   T4."zip_code" as "Free Form Accounting Text",
	   T3."previously_used_account_no" as "Previously Used Account No",
	   T3."skr51_cost_unit" as "Skr51 Cost Unit",
	   T3."skr51_sales_channel" as "Skr51 Sales Channel",
	   T3."skr51_cost_center" as "Skr51 Cost Center",
	   T3."skr51_branch" as "Skr51 Branch",
	   T3."day_of_actual_accounting" as "Day Of Actual Accounting",
	   T3."days_for_cash_discount" as "Days For Cash Discount",
	   T3."vat_key" as "Vat Key",
	   T3."account_statement_page" as "Account Statement Page",
	   T3."account_statement_number" as "Account Statement Number",
	   T3."vat_id_number" as "Vat Id Number",
	   T3."vehicle_reference" as "Vehicle Reference",
	   T3."posting_text" as "Posting Text",
	   T3."term_of_payment" as "Term Of Payment",
	   T3."cash_discount" as "Cash Discount",
	   T3."journal_line" as "Journal Line",
	   T3."journal_page" as "Journal Page",
	   T3."last_dunning_date" as "Last Dunning Date",
	   T3."dunning_level" as "Dunning Level",
	   T3."invoice_number" as "Invoice Number",
	   T3."invoice_date" as "Invoice Date_falsch",
	   T3."employee_number" as "Employee Number",
	   T3."serial_number_each_month" as "Serial Number Each Month",
	   T3."account_form_page_line" as "Account Form Page Line",
	   T3."account_form_page_number" as "Account Form Page Number",
	   CASE WHEN (T3."nominal_account_number" = 5701) THEN ((rtrim((((T3."nominal_account_number"))))) + '_' + (rtrim((((T3."skr51_cost_center")))))) ELSE ((rtrim((((T3."nominal_account_number")))))) END as "Contra Account Text",
	   T3."nominal_contra_account" as "Nominal Contra Account",
	   T3."customer_contra_account" as "Customer Contra Account",
	   T3."branch_number" as "Branch Number",
	   T3."posted_count" as "Posted Count",
	   T3."debit_or_credit" as "Debit Or Credit",
	   (convert(float, T3."posted_value")) as "Posted Value",
	   T3."document_date" as "Document Date",
	   T3."clearing_number" as "Clearing Number",
	   T3."is_balanced" as "Is Balanced",
	   T3."nominal_account_number" as "Nominal Account Number",
	   T3."customer_number" as "Customer Number",
	   T3."position_in_document" as "Position In Document",
	   T3."document_number" as "Document Number",
	   T3."document_type" as "Document Type",
	   (left((((T1."invoice_type"))),1)) + (left((((T1."invoice_number"))),9)) as "Inv_Type_Invoice_Number",
	   T1."total_net" as "Total Net",
	   T1."total_gross" as "Total Gross",
	   T1."part_disposal" as "Part Disposal",
	   T1."part_rebate" as "Part Rebate",
	   T1."part_amount_gross" as "Part Amount Gross",
	   T1."part_amount_net" as "Part Amount Net",
	   T1."job_rebate" as "Job Rebate",
	   T1."job_amount_gross" as "Job Amount Gross",
	   T1."job_amount_net" as "Job Amount Net",
	   T1."used_part_vat_value" as "Used Part Vat Value",
	   T1."reduced_vat_value" as "Reduced Vat Value",
	   T1."reduced_vat_percentage" as "Reduced Vat Percentage",
	   T1."reduced_vat_basevalue" as "Reduced Vat Basevalue",
	   T1."full_vat_value" as "Full Vat Value",
	   T1."full_vat_percentage" as "Full Vat Percentage",
	   T1."full_vat_basevalue" as "Full Vat Basevalue",
	   T1."vehicle_number" as "Vehicle Number",
	   T1."internal_cost_account" as "Internal Cost Account",
	   T1."creating_employee" as "Creating Employee",
	   T1."odometer_reading" as "Odometer Reading",
	   T1."credit_invoice_number" as "Credit Invoice Number",
	   T1."credit_invoice_type" as "Credit Invoice Type",
	   T1."is_credit" as "Is Credit",
	   T1."is_own_vehicle" as "Is Own Vehicle",
	   T1."cancelation_employee" as "Cancelation Employee",
	   T1."cancelation_date" as "Cancelation Date",
	   T1."cancelation_number" as "Cancelation Number",
	   T1."is_canceled" as "Is Canceled",
	   T1."service_date" as "Service Date",
	   T1."invoice_date" as "Invoice Date",
	   T1."order_customer" as "Order Customer",
	   T1."paying_customer" as "Paying Customer",
	   T1."subsidiary" as "Subsidiary",
	   T1."invoice_number" as "Invoice Number_2",
	   T2."description" as "Description_Invoice_Types",
	   T2."type" as "Type_Invoice_Types",
	   T1."invoice_type" as "Invoice Type"
from ((((((((("dbo"."invoices" T1 left outer join "dbo"."invoice_types" T2 on T2."type" = T1."invoice_type") left outer join "dbo"."journal_accountings" T3 on T3."invoice_number" = T1."invtype_invnr") left outer join "dbo"."customers_suppliers" T4 on T4."customer_number" = T1."paying_customer") left outer join "dbo"."employees" T5 on T1."creating_employee" = T5."employee_number") left outer join "dbo"."accounts_characteristics" T6 on (((((T6."skr51_branch" = T3."skr51_branch") and (T6."skr51_make" = T3."skr51_make")) and (T6."skr51_cost_center" = T3."skr51_cost_center")) and (T6."skr51_sales_channel" = T3."skr51_sales_channel")) and (T6."skr51_cost_unit" = T3."skr51_cost_unit")) and (T6."subsidiary_to_company_ref" = T3."subsidiary_to_company_ref")) left outer join "dbo"."vehicles" T7 on T1."vehicle_number" = T7."internal_number") left outer join "dbo"."makes" T8 on T7."make_number" = T8."make_number") left outer join "dbo"."models" T9 on (T7."make_number" = T9."make_number") and (T7."model_code" = T9."model_code")) left outer join "ims"."Serviceberater_Rechnung" T10 on T1."invtype_invnr" = T10."invtype_invnr")
where (((((T1."invoice_type" BETWEEN 2 AND 6) and ((left((((T3."nominal_account_number"))),1)) IN ('4','5','7','8'))) and (T1."invoice_date" >= convert(date, '2019-01-01'))) and (T1."is_canceled" <> 1)) and (((len((rtrim((((T3."nominal_account_number"))))) + 'Z') - 1)) = 4))
-- order by "Order Number" asc,"Document Number" asc,"Position In Document" asc,"Inv_Type_Invoice_Number" asc
) D1