C11/System/ARI/IQD/Service/Service_Ausgangsrechnung_ab_2011_ben_AW.sql

select distinct T1."No_" as "No",
	   T1."Sell-to Customer No_" as "Sell-to Customer No",
	   T1."Bill-to Customer No_" as "Bill-to Customer No",
	   T1."Bill-to Name" as "Bill-to Name",
	   T1."Bill-to Address" as "Bill-to Address",
	   T1."Bill-to City" as "Bill-to City",
	   T1."Order Date" as "Order Date",
	   T1."Posting Date" as "Posting Date",
	   T1."Payment Terms Code" as "Payment Terms Code",
	   T1."Location Code" as "Location Code",
	   T1."Shortcut Dimension 1 Code" as "Department Code",
	   T1."Shortcut Dimension 2 Code" as "Make Code",
	   T1."Customer Posting Group" as "Customer Posting Group",
	   T1."Price Group Code" as "Price Group Code",
	   T1."Prices Including VAT" as "Prices Including Vat",
	   T1."Allow Quantity Disc_" as "Allow Quantity Disc",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Order No_" as "Order No",
	   T1."On Hold" as "On Hold",
	   T1."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T1."Transaction Type" as "Transaction Type",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."Sell-to Address" as "Sell-to Address",
	   T1."Sell-to City" as "Sell-to City",
	   T1."Correction" as "Correction",
	   T1."Document Date" as "Document Date",
	   T1."External Document No_" as "External Document No",
	   T1."Area" as "Area",
	   T1."Shipping Agent Code" as "Shipping Agent Code",
	   T1."No_ Series" as "No Series",
	   T1."Order No_ Series" as "Order No Series",
	   T1."User ID" as "User Id",
	   T1."Order Type" as "Order Type",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END as "Service Order No",
	   T1."Customer Group Code" as "Customer Group Code",
	   T1."Branch Code" as "Branch Code",
	   T2."VIN" as "Vin",
	   T2."Model" as "Model_ori",
	   T3."Document No_" as "Document No",
	   T3."Shortcut Dimension 1 Code" as "Department Code",
	   T3."Shortcut Dimension 2 Code" as "Make Code",
	   T3."Order No_" as "Order No",
	   T3."Order Type" as "Order Type",
	   T3."VIN" as "Vin",
	   T3."Vehicle Status" as "Vehicle Status",
	   T3."Registration Date" as "Registration Date",
	   T3."Mileage" as "Mileage",
	   T3."Customer Group Code" as "Customer Group Code",
	   T4."Service Advisor No_" as "Service Advisor No_Archiv",
	   T5."Service Advisor No_" as "Service Advisor No_oA",
	   T6."No_" as "No_für_Archiv",
	   T6."First Name" as "First Name_für_Archiv",
	   T6."Last Name" as "Last Name_für_Archiv",
	   T7."No_" as "No",
	   T7."First Name" as "First Name",
	   T7."Last Name" as "Last Name",
	   CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END as "Serviceberater",
	   '1' as "Hauptbetrieb",
	   CASE WHEN (T1."Location Code" = 'BUR') THEN ('10') WHEN (T1."Location Code" = 'MUE') THEN ('20') ELSE null END as "Standort",
	   CASE WHEN (T1."Customer Posting Group" IN ('PKW_GWL')) THEN ('GWL') WHEN (T1."No_ Series" LIKE 'I%') THEN ('Intern') ELSE ('Extern') END as "Umsatzart",
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW','BMW I','BMW-C1','BMWI','BMW-MINI','BMW-MOT','BMWVW')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as "Fabrikat",
	   T2."Model" as "Model",
	   T2."VIN" + ' - ' + T2."Model" as "Fahrzeug",
	   CASE WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW','BMW I','BMW-C1','BMWI','BMW-MINI','BMW-MOT','BMWVW')) THEN (T1."Shortcut Dimension 2 Code") ELSE ('Fremdfabrikat') END as "Marke",
	   T4."Service Posting Group" as "Service Posting Group_für_Archiv",
	   T5."Service Posting Group" as "Service Posting Group",
	   CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END as "Auftragsart",
	   T8."Code" as "Cust_Gr_Code",
	   T8."Description" as "Cust_Gr_Description",
	   CASE WHEN (T1."Bill-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T8."Description") END as "Kundenart",
	   T9."No_" as "Cust_No",
	   T9."Name" as "Cust_Name",
	   T9."No_" + ' - ' + T9."Name" as "Kunde",
	   '' as "Auftragsart_1",
	   '' as "Function Code",
	   '' as "Monteur",
	   T1."Posting Date" as "Invoice Date",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 180) THEN (T1."No_" + ' - ' + (CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) + ' - ' + T9."Name") ELSE ('Aufträge älter 180 Tage') END as "Order Number",
	   CASE WHEN (((day((now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date")))) WHEN (((day((now()) - T1."Posting Date")) <= 30) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T9."Name" + ' - ' + (convert(varchar(50), year(T1."Posting Date")) + '-' + convert(varchar(50), month(T1."Posting Date")) + '-' + convert(varchar(50), day(T1."Posting Date")))) ELSE ('Rechnungen älter 30 Tage') END as "Order Number_Rg_Ausg",
	   CASE WHEN (((day((now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NOT NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END) + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name") WHEN (((day((now()) - T1."Posting Date")) <= 4) and ((CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) IS NULL)) THEN (T1."No_" + ' - ' + (CASE WHEN (T4."Service Posting Group" IS NULL) THEN (T5."Service Posting Group") ELSE (T4."Service Posting Group") END) + ' - ' + T9."Name") ELSE null END as "Order Number_Rg_Ausg_2",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 4) THEN ((CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END)) ELSE null END as "Order Number_Rg_Ausg_1",
	   T10."No_" as "Cust_No_Verkaufskunde",
	   T10."Name" as "Cust_Name_Verkaufskunde",
	   T11."Description" as "Cust_Group_Description_Verkaufskunde",
	   CASE WHEN (T1."Sell-to Customer No_" LIKE 'INT%') THEN ('Intern') ELSE (T11."Description") END as "Kundenart_Verkaufskunde",
	   T10."No_" + ' - ' + T10."Name" as "Kunde_Verkaufskunde",
	   T12."Duration_Time_Clock" as "Duration Time Clock_Add_Service_Time_Clock_ims",
	   T12."Monteur" as "Monteur",
	   T12."Monteur" as "Auftragsposition",
	   T12."Duration_Time_Clock" * 12 as "ben. AW_Time_Clock",
	   (T12."Duration_Time_Clock" * 12) as "ben Zeit",
	   '1' as "Rechtseinheit_ID",
	   (CASE WHEN (T1."Location Code" = 'BUR') THEN ('10') WHEN (T1."Location Code" = 'MUE') THEN ('20') ELSE null END) as "Standort_ID",
	   'Serviceberater' as "Zuordnung_Funktion",
	   T1."Shortcut Dimension 1 Code" as "Cost_Centre_ID",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 100) THEN ((CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) + ' - ' + T9."Name") ELSE ('Aufträge älter 100 Tage') END as "Order_Desc_100",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 100) THEN (T1."No_" + ' - ' + T9."Name") ELSE ('Rechnungen älter 100 Tage') END as "Invoice_Desc_100",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 30) THEN ((CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END)) ELSE ('Aufträge älter 30 Tage') END as "Order_Desc_30",
	   CASE WHEN ((day((now()) - T1."Posting Date")) <= 30) THEN (T1."No_" + ' - ' + (CASE WHEN (T6."No_" IS NULL) THEN (T7."First Name" + ' ' + T7."Last Name") ELSE (T6."First Name" + ' ' + T6."Last Name") END) + ' - ' + T9."Name") ELSE ('Rechnungen älter 30 Tage') END as "Invoice_Desc_30",
	   T1."Gen_ Bus_ Posting Group" as "Customer_Group_Owner",
	   T9."Name" as "Customer_Name_Owner",
	   T2."VIN" as "Fahrgestellnummer",
	   T2."Model" as "Model_Desc",
	   T1."Gen_ Bus_ Posting Group" as "Produktbuchungsgruppe",
	   CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END as "Fahrzeugalter_Tage",
	   (CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 as "Fahrzeugalter",
	   CASE WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 0.01 AND 0.99) THEN ('1') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 1.00 AND 1.99) THEN ('2') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 2.00 AND 2.99) THEN ('3') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 3.00 AND 3.99) THEN ('4') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 4.00 AND 4.99) THEN ('5') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 5.00 AND 5.99) THEN ('6') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 6.00 AND 6.99) THEN ('7') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 7.00 AND 7.99) THEN ('8') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 8.00 AND 8.99) THEN ('9') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 BETWEEN 9.00 AND 9.99) THEN ('10') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 > 9.99) THEN ('> 10') WHEN ((CASE WHEN (T3."Registration Date" <> convert(datetime, '1753-01-01 00:00:00.000')) THEN ((day(T1."Posting Date" - T3."Registration Date"))) ELSE (0) END) / 365 = 0) THEN ('keine Angabe') ELSE null END as "FZG-Altersstaffel",
	   CASE WHEN (T1."Gen_ Bus_ Posting Group" LIKE '%LOHN%') THEN (T1."Gen_ Bus_ Posting Group") ELSE null END as "Repair_Group_Desc",
	   'Rechnung' as "Rechnung_Gutschrift",
	   '' as "Parts_Focus_Group",
	   '' as "Parts_Make_Desc",
	   '' as "Parts_Group_Desc"
from "ims"."Add_Service_ledger_mit_Time_Clock_Entry_fuer_Service_Rg_Ausg_ims" T12,
	((((("Vogl7x"."dbo"."BMW AH Vogl$Sales Invoice Header" T1 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Vehicle" T2 on T1."Supply VIN" = T2."VIN") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T9 on T9."No_" = T1."Bill-to Customer No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer Group" T8 on T9."Customer Group Code" = T8."Code") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer" T10 on T10."No_" = T1."Sell-to Customer No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Customer Group" T11 on T10."Customer Group Code" = T11."Code"),
	(((("Vogl7x"."dbo"."BMW AH Vogl$Sales Invoice Line" T3 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Archived Service Header" T4 on T3."Service Order No_" = T4."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Service Header" T5 on T3."Service Order No_" = T5."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T6 on T4."Service Advisor No_" = T6."No_") left outer join "Vogl7x"."dbo"."BMW AH Vogl$Employee" T7 on T5."Service Advisor No_" = T7."No_")
where (T1."No_" = T3."Document No_") and ((T1."No_" = T12."Document No_") and (T1."Service Order No_" = T12."Order No_"))
and (((((left(T1."No_",1)) IN ('I','W')) and (not T3."Type" IN (0,11,12))) and (T1."Posting Date" >= convert(datetime, '2020-01-01 00:00:00.000'))) and (not (CASE WHEN ((day((now()) - T1."Posting Date")) <= 60) THEN (T1."Service Order No_") ELSE ('Aufträge älter 60 Tage') END) IN ('NASISPA')))
-- order by "No" asc