C11/System/NAVISION/IQD/Belege/Teile_Fremdwerkstatt_Gutschrift_FIBU_EW_Wertposten.sql

select "No_4",
	   "Sell-to Customer No_2",
	   "Bill-to Customer No",
	   "Bill-to Name",
	   "Bill-to Name 2",
	   "Posting Date",
	   "Due Date",
	   "Payment Discount %",
	   "Location Code_2",
	   "Department Code_2",
	   "Make Code_2",
	   "Customer Posting Group_2",
	   "Invoice Disc Code",
	   "Salesperson Code",
	   "On Hold",
	   "Gen Bus Posting Group_für_Kundenart",
	   "Sell-to Customer Name",
	   "Sell-to Customer Name 2",
	   "Document Date",
	   "Area_2",
	   "User Id",
	   "Order Type",
	   "Service Order No_2",
	   "Branch Code",
	   "Document No",
	   "Line No",
	   "Sell-to Customer No",
	   "Type",
	   "No_3",
	   "Location Code",
	   "Quantity Disc Code",
	   "Description_2",
	   "Description 2",
	   "Unit Of Measure",
	   "Quantity",
	   "Unit Price",
	   "Unit Cost (lcy)" as "Unit Cost (lcy)",
	   "Vat %",
	   "Quantity Disc %",
	   "Line Discount %",
	   "Line Discount Amount",
	   "Amount",
	   "Amount Including Vat",
	   "Allow Invoice Disc",
	   "Department Code",
	   "Make Code",
	   "Price Group Code",
	   "Allow Quantity Disc",
	   "Inv Discount Amount",
	   "Gen Bus Posting Group",
	   "Gen Prod Posting Group",
	   "Area",
	   "Unit Cost",
	   "Service Order No",
	   "Service Order Line No",
	   "Item Group Code_Teilestamm_2",
	   "Menge",
	   "Unit Preis",
	   "Unit Kosten (LCY)" as "Unit Kosten (LCY)",
	   "Line Rabatt Betrag",
	   "Betrag",
	   "Kosten",
	   "Umsatz Teile",
	   "Einsatz Teile Service",
	   "Posting Date" as "Invoice Date",
	   '1' as "Hauptbetrieb",
	   "Standort",
	   "First Name",
	   "Last Name",
	   "Serviceberater",
	   "No_2",
	   "Name",
	   "Customer Posting Group",
	   "Customer Type",
	   "Customer Group Code",
	   "No",
	   "Description",
	   "Class",
	   "Inventory Posting Group",
	   "Commission Group",
	   "Bmw Parts Type",
	   "Item Group Code_Teilestamm",
	   "Teileart",
	   "Artikelgruppe",
	   "Item Group Code_Teilestamm_2" as "Item Group Code",
	   "Zeile mit Bez",
	   "Standort" as "Betrieb Nr",
	   '' as "Konto",
	   "Zeile mit Bez" as "Konto Nr",
	   '' as "Text",
	   "Posting Date" as "Jahr",
	   ('Einsatz FW gesamt') as "Vstufe 1",
	   (('Einsatz FW gesamt')) as "Bereich",
	   ("Betrag") / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END) as "Umsatzerlöse",
	   ("Einsatz Teile Service") / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END) as "VAK",
	   "Entry No_Value_ledger_entry",
	   "Item No_Value_ledger_entry",
	   "Posting Date_Value_ledger_entry",
	   "Item Ledger Entry No_Value_ledger_entry",
	   "Valued Quantity_Value_ledger_entry",
	   "Invoiced Quantity_Value_ledger_entry",
	   "Cost Per Unit_Value_ledger_entry",
	   "Adjusted Cost_Value_ledger_entry",
	   "Cost Posted To G L_Value_ledger_entry",
	   "No_Item_No_Value_ledger_entry",
	   CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END as "Anzahl_Sätze_Value_ledger_entry",
	   CASE WHEN ((("Einsatz Teile Service") / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)) <> 0) THEN ((c126) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)) ELSE (0) END as "VAK_Wertposten_ori",
	   "Gen Bus Posting Group_für_Kundenart" as "Kundenart",
	   "Kunde",
	   (((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)) as "VAK_Wertposten_neu",
	   CASE WHEN (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END))) < 0) THEN (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END))) * -1) ELSE (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)))) END as "VAK_Wertposten_neu_1",
	   CASE WHEN ((("Betrag") / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)) < 0) THEN ((CASE WHEN (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END))) < 0) THEN (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END))) * -1) ELSE (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)))) END) * -1) ELSE ((CASE WHEN (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END))) < 0) THEN (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END))) * -1) ELSE (((((c120)) / (CASE WHEN ("Item No_Value_ledger_entry" IS NOT NULL) THEN (COUNT("Item No_Value_ledger_entry") OVER (partition by "No_Item_No_Value_ledger_entry")) ELSE (1) END)))) END)) END as "VAK_Wertposten",
	   "Hauptbetrieb_ID",
	   "Standort" as "Standort_ID"
from 
(select (T1."No_" + '-' + (left((((T2."Line No_"))),7)) + '-' + T6."Item No_") as "No_Item_No_Value_ledger_entry",
	   ((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') ELSE null END)) as "Standort",
	   CASE WHEN (((CASE WHEN (T1."Location Code" = 'MM') THEN ('10') WHEN (T1."Location Code" = 'VÖH') THEN ('20') WHEN (T1."Location Code" = 'KRU') THEN ('30') WHEN (T1."Location Code" = 'ULM') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') WHEN (T1."Location Code" = 'GZ') THEN ('55') WHEN (T1."Location Code" = 'AAM') THEN ('60') WHEN (T1."Location Code" = 'LEH') THEN ('70') ELSE null END)) IN ('60','70')) THEN ('2') ELSE ('1') END as "Hauptbetrieb_ID",
	   ((convert(float, T6."Valued Quantity"))) * ((convert(float, T6."Cost per Unit"))) as c120,
	   T6."Item No_" as "Item No_Value_ledger_entry",
	   (convert(float, T2."Amount")) * -1 as "Betrag",
	   T1."Sell-to Customer No_" + ' - ' + T1."Bill-to Name" as "Kunde",
	   T1."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group_für_Kundenart",
	   ((convert(float, T2."Quantity")) * -1) * ((convert(float, T2."Unit Cost"))) as "Einsatz Teile Service",
	   (((convert(float, T6."Valued Quantity"))) * ((convert(float, T6."Cost per Unit")))) * -1 as c126,
	   (convert(float, T6."Cost Posted to G_L")) as "Cost Posted To G L_Value_ledger_entry",
	   (convert(float, T6."Adjusted Cost")) as "Adjusted Cost_Value_ledger_entry",
	   (convert(float, T6."Cost per Unit")) as "Cost Per Unit_Value_ledger_entry",
	   T6."Invoiced Quantity" as "Invoiced Quantity_Value_ledger_entry",
	   (convert(float, T6."Valued Quantity")) as "Valued Quantity_Value_ledger_entry",
	   T6."Item Ledger Entry No_" as "Item Ledger Entry No_Value_ledger_entry",
	   T6."Posting Date" as "Posting Date_Value_ledger_entry",
	   T6."Entry No_" as "Entry No_Value_ledger_entry",
	   T1."Posting Date" as "Posting Date",
	   (CASE WHEN (((CASE WHEN (T5."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T5."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END) = 'Teileart 1 - 2') and (T2."Item Group Code" IN ('23','26','27','30','31','32','33','D','E','25','28','29','24'))) THEN ('Umsatz FW TA1-2 D/E') WHEN (((CASE WHEN (T5."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T5."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END) = 'Teileart 1 - 2') and (T2."Item Group Code" IN ('F','G','H','I','K','34','35','36','37','38','39','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','65','66','67','69','71','73','74','75','98','99'))) THEN ('Umsatz FW TA1-2 F-K') WHEN (T1."Item Sales Price Group" = 'LACK') THEN ('Umsatz Lack') ELSE ('Umsatz FW Rest') END) as "Zeile mit Bez",
	   T2."Item Group Code" as "Item Group Code_Teilestamm_2",
	   CASE WHEN (T5."Item Group Code" IN ('A','C','B')) THEN ('Artikelgruppe A - C') WHEN (T5."Item Group Code" IN ('E','G','H','F','D','I','K','J')) THEN ('Artikelgruppe D - K') ELSE ('Teileart fehlt/Rest') END as "Artikelgruppe",
	   CASE WHEN (T5."BMW Parts Type" IN ('1','2')) THEN ('Teileart 1 - 2') WHEN (T5."BMW Parts Type" IN ('3','4','5','6','7','8','9')) THEN ('Teileart 3 - 9') ELSE ('Teileart fehlt') END as "Teileart",
	   T5."Item Group Code" as "Item Group Code_Teilestamm",
	   T5."BMW Parts Type" as "Bmw Parts Type",
	   T5."Commission Group" as "Commission Group",
	   T5."Inventory Posting Group" as "Inventory Posting Group",
	   T5."Class" as "Class",
	   T5."Description" as "Description",
	   T5."No_" as "No",
	   T4."Customer Group Code" as "Customer Group Code",
	   T4."Customer Type" as "Customer Type",
	   T4."Customer Posting Group" as "Customer Posting Group",
	   T4."Name" as "Name",
	   T4."No_" as "No_2",
	   T3."First Name" + ' ' + T3."Last Name" as "Serviceberater",
	   T3."Last Name" as "Last Name",
	   T3."First Name" as "First Name",
	   ((convert(float, T2."Amount")) * -1) as "Umsatz Teile",
	   (convert(float, T2."Unit Cost")) as "Kosten",
	   (convert(float, T2."Line Discount Amount")) * -1 as "Line Rabatt Betrag",
	   (convert(float, T2."Unit Cost (LCY)")) as "Unit Kosten (LCY)",
	   (convert(float, T2."Unit Price")) as "Unit Preis",
	   (convert(float, T2."Quantity")) * -1 as "Menge",
	   T2."Service Order Line No_" as "Service Order Line No",
	   T2."Service Order No_" as "Service Order No",
	   T2."Unit Cost" as "Unit Cost",
	   T2."Area" as "Area",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Inv_ Discount Amount" as "Inv Discount Amount",
	   T2."Allow Quantity Disc_" as "Allow Quantity Disc",
	   T2."Price Group Code" as "Price Group Code",
	   T2."Make Code" as "Make Code",
	   T2."Department Code" as "Department Code",
	   T2."Allow Invoice Disc_" as "Allow Invoice Disc",
	   T2."Amount Including VAT" as "Amount Including Vat",
	   T2."Amount" as "Amount",
	   T2."Line Discount Amount" as "Line Discount Amount",
	   T2."Line Discount %" as "Line Discount %",
	   T2."Quantity Disc_ %" as "Quantity Disc %",
	   T2."VAT %" as "Vat %",
	   T2."Unit Cost (LCY)" as "Unit Cost (lcy)",
	   T2."Unit Price" as "Unit Price",
	   T2."Quantity" as "Quantity",
	   T2."Unit of Measure" as "Unit Of Measure",
	   T2."Description 2" as "Description 2",
	   T2."Description" as "Description_2",
	   T2."Quantity Disc_ Code" as "Quantity Disc Code",
	   T2."Location Code" as "Location Code",
	   T2."No_" as "No_3",
	   T2."Type" as "Type",
	   T2."Sell-to Customer No_" as "Sell-to Customer No",
	   T2."Line No_" as "Line No",
	   T2."Document No_" as "Document No",
	   T1."Branch Code" as "Branch Code",
	   T1."Service Order No_" as "Service Order No_2",
	   T1."Order Type" as "Order Type",
	   T1."User ID" as "User Id",
	   T1."Area" as "Area_2",
	   T1."Document Date" as "Document Date",
	   T1."Sell-to Customer Name 2" as "Sell-to Customer Name 2",
	   T1."Sell-to Customer Name" as "Sell-to Customer Name",
	   T1."On Hold" as "On Hold",
	   T1."Salesperson Code" as "Salesperson Code",
	   T1."Invoice Disc_ Code" as "Invoice Disc Code",
	   T1."Customer Posting Group" as "Customer Posting Group_2",
	   T1."Make Code" as "Make Code_2",
	   T1."Department Code" as "Department Code_2",
	   T1."Location Code" as "Location Code_2",
	   T1."Payment Discount %" as "Payment Discount %",
	   T1."Due Date" as "Due Date",
	   T1."Bill-to Name 2" as "Bill-to Name 2",
	   T1."Bill-to Name" as "Bill-to Name",
	   T1."Bill-to Customer No_" as "Bill-to Customer No",
	   T1."Sell-to Customer No_" as "Sell-to Customer No_2",
	   T1."No_" as "No_4"
from (("NAVISION"."import"."Sales_Credit_Memo_Header" T1 left outer join "NAVISION"."import"."Employee" T3 on (T1."Salesperson Code" = T3."No_") and (T1."Client_DB" = T3."Client_DB")) left outer join "NAVISION"."import"."Customer" T4 on (T1."Bill-to Customer No_" = T4."No_") and (T1."Client_DB" = T4."Client_DB")),
	(("NAVISION"."import"."Sales_Credit_Memo_Line" T2 left outer join "NAVISION"."import"."Item" T5 on (T2."No_" = T5."No_") and (T2."Client_DB" = T5."Client_DB")) left outer join "NAVISION"."import"."Value_Ledger_Entry" T6 on ((T6."Document No_" = T2."Document No_") and (T6."Item No_" = T2."No_")) and (T6."Client_DB" = T2."Client_DB"))
where ((T1."No_" = T2."Document No_") and (T1."Client_DB" = T2."Client_DB"))
and ((((T1."No_" LIKE 'VGT%') or (T1."No_" LIKE 'VGGT%')) and (T1."Posting Date" >= convert(datetime, '2020-01-01 00:00:00.000'))) and (T1."Item Sales Price Group" IN ('44','51','60','61','65','66','67','68','50','52','53','70','LACK','AL-KO','GROßKUNDE','KUNDE10','SOLOPLAN')))
) D1
-- order by "No_4" asc,"No_Item_No_Value_ledger_entry" asc,"Salesperson Code" asc,"Service Order No_2" asc