ETL_DWH_ARI/schema/GC_ETL_ARI/views/transform.OP_ARI.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform].[OP_ARI] AS

--OP_ARI
SELECT [Client_DB]
     ,
     --[Entry No] AS [Entry No],
     --[Customer No] AS [Customer No],
     --[Posting Date] AS [Posting Date],
    [Document Type] AS [Document Type]
     , [Document No] AS [Document No]
     ,
     --[Description] AS [Description],
     --[Sales (lcy)] AS [Sales (lcy)],
     --[Profit (lcy)] AS [Profit (lcy)],
     --[Sell-to Customer No] AS [Sell-to Customer No],
     --[Customer Posting Group] AS [Customer Posting Group],
     --[Global Dimension 1 Code] AS [Global Dimension 1 Code],
     --[Global Dimension 2 Code] AS [Global Dimension 2 Code],
     --[Salesperson Code] AS [Salesperson Code],
    [User Id] AS [User Id]
     ,
     --[Source Code] AS [Source Code],
     --[On Hold_ori] AS [On Hold_ori],
    [On Hold] AS [On Hold]
     ,
     --[Applies-to Doc Type] AS [Applies-to Doc Type],
     --[Applies-to Doc No] AS [Applies-to Doc No],
     --[Open] AS [Open],
     --[Due Date] AS [Due Date],
     --[Pmt Discount Date] AS [Pmt Discount Date],
     --[Positive] AS [Positive],
     --[Closed By Entry No] AS [Closed By Entry No],
     --[Closed At Date] AS [Closed At Date],
     --[Closed By Amount] AS [Closed By Amount],
     --[Journal Batch Name] AS [Journal Batch Name],
     --[Closed By Amount (lcy)] AS [Closed By Amount (lcy)],
     --[Document Date] AS [Document Date],
     --[No Series] AS [No Series],
     --[Last Issued Reminder Level] AS [Last Issued Reminder Level],
     --[Dimension Set Id] AS [Dimension Set Id],
     --[Direct Debit Mandate Id] AS [Direct Debit Mandate Id],
     --[Customer Group Code] AS [Customer Group Code],
    [Branch Code] AS [Branch Code]
     ,
     --[Main Area] AS [Main Area],
     --[Pmt Disc Base] AS [Pmt Disc Base],
    [Vin] AS [Vin]
     ,
     --[Factory No] AS [Factory No],
     --[Cash Reg Receipt No] AS [Cash Reg Receipt No],
    [Comment] AS [Comment]
     ,
     --[Service Advisor No] AS [Service Advisor No],
     --[Salesperson Code 2] AS [Salesperson Code 2],
     --[Cust Ledger Entry No] AS [Cust Ledger Entry No],
     --[Entry Type] AS [Entry Type],
     --[Document Type_detail_cust] AS [Document Type_detail_cust],
     --[Document No_detail] AS [Document No_detail],
     --[User Id_detail_cust] AS [User Id_detail_cust],
     --[Amount] AS [Amount],
     --[Debit Amount] AS [Debit Amount],
     --[Credit Amount] AS [Credit Amount],
     --[Initial Entry Due Date] AS [Initial Entry Due Date],
     --[Initial Entry Global Dim 1] AS [Initial Entry Global Dim 1],
     --[Initial Entry Global Dim 2] AS [Initial Entry Global Dim 2],
     --[Gen Bus Posting Group] AS [Gen Bus Posting Group],
     --[Gen Prod Posting Group] AS [Gen Prod Posting Group],
     --[Initial Document Type] AS [Initial Document Type],
     --[Saldo_Beleg_1] AS [Saldo_Beleg_1],
     --[Anzahl_Sätze_Entry_No] AS [Anzahl_Sätze_Entry_No],
    [offen] AS [offen]
     , [Gesamt offen KD (Info)] AS [Gesamt offen KD (Info)]
     --[Sel Name_alt] AS [Sel Name_alt],
     , [Invoice Date] AS [Invoice Date]
     , [Hauptbetrieb] AS [Hauptbetrieb]
     , [Standort] AS [Standort]
     , [Sel Name] AS [Sel Name]
     ,
     --[No_Customer] AS [No_Customer],
     --[Name_Customer] AS [Name_Customer],
     --[Last Name_Customer] AS [Last Name_Customer],
     --[First Name_Customer] AS [First Name_Customer],
    [Kunde] AS [Kunde]
	 , [Kunde_Dashboard] AS [Kunde_Dashboard]
     , [Beleg] AS [Beleg]
     , [Bereich] AS [Bereich]
     , [Tage] AS [Tage]
     , [Staffel] AS [Staffel]
     ,
     --[Salesperson Code_Sales_Inv_Header] AS [Salesperson Code_Sales_Inv_Header],
     --[Service Advisor No_Sales_Inv_Header] AS [Service Advisor No_Sales_Inv_Header],
     --[Code_Salesperson_Purchaser_Verkauf] AS [Code_Salesperson_Purchaser_Verkauf],
     --[Name_Salesperson_Purchaser_Verkauf] AS [Name_Salesperson_Purchaser_Verkauf],
     --[Code_Salesperson_Purchaser_Service] AS [Code_Salesperson_Purchaser_Service],
     --[Name_Salesperson_Purchaser_Service] AS [Name_Salesperson_Purchaser_Service],
     --[No_Employee_Service_Advisor] AS [No_Employee_Service_Advisor],
     --[First Name_Employee_Service_Advisor] AS [First Name_Employee_Service_Advisor],
     --[Last Name_Employee_Service_Advisor] AS [Last Name_Employee_Service_Advisor],
     --[No_Employee_Salesperson_Code] AS [No_Employee_Salesperson_Code],
     --[First Name_Employee_Salesperson_Code] AS [First Name_Employee_Salesperson_Code],
     --[Last Name_Employee_Salesperson_Code] AS [Last Name_Employee_Salesperson_Code],
     --[Sel Name_ori] AS [Sel Name_ori],
    [Mahnstufe] AS [Mahnstufe]
     --[Betrag Rg.] AS [Betrag Rg.],
     , [Forderungsart] AS [Forderungsart]
     , [Abwarten] AS [Abwarten]
     --[Leasing] AS [Leasing],
     , [Message To Recipient] AS [Message To Recipient]
     , [Haft_Kasko] AS [Haft_Kasko]
FROM (SELECT [Client_DB]
           ,
--           "Entry No_2" AS "Entry No_2"
--            , "Customer No_2" AS "Customer No_2"
--            , "Posting Date_2" AS "Posting Date_2"
          "Document Type" AS "Document Type"
           , "Document No" AS "Document No"
--            , "Description" AS "Description"
--            , "Sales (lcy)" AS "Sales (lcy)"
--            , "Profit (lcy)" AS "Profit (lcy)"
--            , "Sell-to Customer No" AS "Sell-to Customer No"
--            , "Customer Posting Group" AS "Customer Posting Group"
--            , "Global Dimension 1 Code" AS "Global Dimension 1 Code"
--            , "Global Dimension 2 Code" AS "Global Dimension 2 Code"
--            , "Salesperson Code" AS "Salesperson Code"
           , "User Id" AS "User Id"
--            , "Source Code_2" AS "Source Code_2"
--            , "On Hold_ori" AS "On Hold_ori"
--            , "Applies-to Doc Type" AS "Applies-to Doc Type"
--            , "Applies-to Doc No" AS "Applies-to Doc No"
--            , "Open" AS "Open"
--            , "Due Date" AS "Due Date"
--            , "Pmt Discount Date" AS "Pmt Discount Date"
--            , "Positive" AS "Positive"
--            , "Closed By Entry No" AS "Closed By Entry No"
--            , "Closed At Date" AS "Closed At Date"
--            , "Closed By Amount" AS "Closed By Amount"
--            , "Journal Batch Name" AS "Journal Batch Name"
--            , "Closed By Amount (lcy)" AS "Closed By Amount (lcy)"
--            , "Document Date" AS "Document Date"
--            , "No Series" AS "No Series"
--            , "Last Issued Reminder Level" AS "Last Issued Reminder Level"
--            , "Dimension Set Id" AS "Dimension Set Id"
--            , "Direct Debit Mandate Id" AS "Direct Debit Mandate Id"
--            , "Customer Group Code" AS "Customer Group Code"
           , "Branch Code" AS "Branch Code"
--            , "Main Area" AS "Main Area"
--            , "Pmt Disc Base" AS "Pmt Disc Base"
           , "Vin" AS "Vin"
--            , "Factory No" AS "Factory No"
--            , "Cash Reg Receipt No" AS "Cash Reg Receipt No"
           , "Comment" AS "Comment"
--            , "Service Advisor No" AS "Service Advisor No"
--            , "Salesperson Code 2" AS "Salesperson Code 2"
--            , "Entry No" AS "Entry No"
--            , "Cust Ledger Entry No" AS "Cust Ledger Entry No"
--            , "Entry Type" AS "Entry Type"
--            , "Posting Date" AS "Posting Date"
--            , "Document Type_detail_cust" AS "Document Type_detail_cust"
--            , "Document No_detail" AS "Document No_detail"
--            , "Amount" AS "Amount"
--            , "Customer No" AS "Customer No"
--            , "User Id_detail_cust" AS "User Id_detail_cust"
--            , "Source Code" AS "Source Code"
--            , "Debit Amount" AS "Debit Amount"
--            , "Credit Amount" AS "Credit Amount"
--            , "Initial Entry Due Date" AS "Initial Entry Due Date"
--            , "Initial Entry Global Dim 1" AS "Initial Entry Global Dim 1"
--            , "Initial Entry Global Dim 2" AS "Initial Entry Global Dim 2"
--            , "Gen Bus Posting Group" AS "Gen Bus Posting Group"
--            , "Gen Prod Posting Group" AS "Gen Prod Posting Group"
--            , "Initial Document Type" AS "Initial Document Type"
--            , "Saldo_Beleg_1" AS "Saldo_Beleg_1"
--            , "Anzahl_Sätze_Entry_No" AS "Anzahl_Sätze_Entry_No"
           , "offen" AS "offen"
           , sum("offen") OVER (PARTITION BY "Customer No_2") AS "Gesamt offen KD (Info)"
           , "Invoice Date" AS "Invoice Date"
           , "Hauptbetrieb" AS "Hauptbetrieb"
           , "Standort" AS "Standort"
--            , "Sel Name_alt" AS "Sel Name_alt"
--            , "No_Customer" AS "No_Customer"
--            , "Name_Customer" AS "Name_Customer"
--            , "Last Name_Customer" AS "Last Name_Customer"
--            , "First Name_Customer" AS "First Name_Customer"
           , "Kunde" AS "Kunde"
		   , "Kunde_Dashboard" AS "Kunde_Dashboard"
           , "Beleg" AS "Beleg"
           , "Bereich" AS "Bereich"
           , "Tage" AS "Tage"
           , "Staffel" AS "Staffel"
           , "Mahnstufe" AS "Mahnstufe"
           , "Forderungsart" AS "Forderungsart"
           , "Abwarten" AS "Abwarten"
--            , "Leasing" AS "Leasing"
--            , "Salesperson Code_Sales_Inv_Header" AS "Salesperson Code_Sales_Inv_Header"
--            , "Service Advisor No_Sales_Inv_Header" AS "Service Advisor No_Sales_Inv_Header"
--            , "Code_Salesperson_Purchaser_Verkauf" AS "Code_Salesperson_Purchaser_Verkauf"
--            , "Name_Salesperson_Purchaser_Verkauf" AS "Name_Salesperson_Purchaser_Verkauf"
--            , "Code_Salesperson_Purchaser_Service" AS "Code_Salesperson_Purchaser_Service"
--            , "Name_Salesperson_Purchaser_Service" AS "Name_Salesperson_Purchaser_Service"
--            , "No_Employee_Service_Advisor" AS "No_Employee_Service_Advisor"
--            , "First Name_Employee_Service_Advisor" AS "First Name_Employee_Service_Advisor"
--            , "Last Name_Employee_Service_Advisor" AS "Last Name_Employee_Service_Advisor"
--            , "No_Employee_Salesperson_Code" AS "No_Employee_Salesperson_Code"
--            , "First Name_Employee_Salesperson_Code" AS "First Name_Employee_Salesperson_Code"
--            , "Last Name_Employee_Salesperson_Code" AS "Last Name_Employee_Salesperson_Code"
--            , "Sel Name_ori" AS "Sel Name_ori"
           , "Sel Name" AS "Sel Name"
--            , "Betrag Rg." AS "Betrag Rg."
           , "Message To Recipient" AS "Message To Recipient"
           , "Haft_Kasko" AS "Haft_Kasko"
           , "On Hold" AS "On Hold"
      FROM (SELECT [Client_DB]
                 , "Entry No_2"
                 , "Customer No_2"
--                  , "Posting Date_2"
                 , "Document Type"
                 , "Document No"
--                  , "Description"
--                  , "Sales (lcy)" AS "Sales (lcy)"
--                  , "Profit (lcy)" AS "Profit (lcy)"
--                  , "Sell-to Customer No"
--                  , "Customer Posting Group"
--                  , "Global Dimension 1 Code"
--                  , "Global Dimension 2 Code"
--                  , "Salesperson Code"
                 , "User Id"
--                  , "Source Code_2"
--                  , "On Hold_ori"
--                  , "Applies-to Doc Type"
--                  , "Applies-to Doc No"
--                  , "Open"
                 , "Due Date"
--                  , "Pmt Discount Date"
--                  , "Positive"
--                  , "Closed By Entry No"
--                  , "Closed At Date"
--                  , "Closed By Amount"
--                  , "Journal Batch Name"
--                  , "Closed By Amount (lcy)" AS "Closed By Amount (lcy)"
--                  , "Document Date"
--                  , "No Series"
                 , "Last Issued Reminder Level"
--                  , "Dimension Set Id"
--                  , "Direct Debit Mandate Id"
--                  , "Customer Group Code"
                 , "Branch Code"
--                  , "Main Area"
--                  , "Pmt Disc Base"
                 , "Vin"
--                  , "Factory No"
--                  , "Cash Reg Receipt No"
                 , "Comment"
--                  , "Service Advisor No"
--                  , "Salesperson Code 2"
--                  , "Entry No"
--                  , "Cust Ledger Entry No"
--                  , "Entry Type"
--                  , "Posting Date"
--                  , "Document Type_detail_cust"
--                  , "Document No_detail"
                 , "Amount"
--                  , "Customer No"
--                  , "User Id_detail_cust"
--                  , "Source Code"
--                  , "Debit Amount"
--                  , "Credit Amount"
--                  , "Initial Entry Due Date"
--                  , "Initial Entry Global Dim 1"
--                  , "Initial Entry Global Dim 2"
--                  , "Gen Bus Posting Group"
--                  , "Gen Prod Posting Group"
--                  , "Initial Document Type"
--                  , sum("Amount") OVER (PARTITION BY "Entry No_2") AS "Saldo_Beleg_1"
--                  , count("Customer No_2") OVER (PARTITION BY "Entry No_2") AS "Anzahl_Sätze_Entry_No"
                 , (sum("Amount") OVER (PARTITION BY "Entry No_2")) / (count("Customer No_2") OVER (PARTITION BY "Entry No_2")) AS "offen"
                 , "Due Date" AS "Invoice Date"
                 , '1' AS "Hauptbetrieb"
                 , "Standort"
--                  , "User Id" AS "Sel Name_alt"
--                  , "No_Customer"
--                  , "Name_Customer"
--                  , "Last Name_Customer"
--                  , "First Name_Customer"
                 , "Kunde"
				 , "Kunde_Dashboard"
                 , "Beleg"
                 , "Bereich"
                 , "Tage"
                 , "Staffel"
                 , "Last Issued Reminder Level" AS "Mahnstufe"
                 , "Forderungsart"
                 , "Abwarten"
--                  , '' AS "Leasing"
--                  , "Salesperson Code_Sales_Inv_Header"
--                  , "Service Advisor No_Sales_Inv_Header"
--                  , "Code_Salesperson_Purchaser_Verkauf"
--                  , "Name_Salesperson_Purchaser_Verkauf"
--                  , "Code_Salesperson_Purchaser_Service"
--                  , "Name_Salesperson_Purchaser_Service"
--                  , "No_Employee_Service_Advisor"
--                  , "First Name_Employee_Service_Advisor"
--                  , "Last Name_Employee_Service_Advisor"
--                  , "No_Employee_Salesperson_Code"
--                  , "First Name_Employee_Salesperson_Code"
--                  , "Last Name_Employee_Salesperson_Code"
--                  , "Sel Name_ori"
                 , "Sel Name"
--                  , ("Sales (lcy)") / (count("Customer No_2") OVER (PARTITION BY "Entry No_2")) AS "Betrag Rg."
                 , "Message To Recipient"
                 , "Haft_Kasko"
                 , "On Hold"
            FROM (SELECT [T1].[Client_DB]
                       , [T1]."Entry No_" AS "Entry No_2"
                       , CASE WHEN ([T1]."On Hold" IN ('HAF', 'KAS', 'UNF')) THEN ('UNF') ELSE ([T1]."On Hold") END AS "On Hold"
                       , CASE WHEN ([T1]."On Hold" = 'HAF') THEN ('HAFT') WHEN ([T1]."On Hold" = 'KAS') THEN ('KASKO') WHEN ([T1]."On Hold" = 'WSS') THEN ('WSS') END AS "Haft_Kasko"
                       , [T1]."Message to Recipient" AS "Message To Recipient"
--                        , (convert(float, [T1]."Sales (LCY)")) AS "Sales (lcy)"
                       , CASE
                             WHEN ((CASE
                                        WHEN ((CASE
                                                   WHEN ([T1]."Main Area" = 0) THEN ('Sonstige')
                                                   WHEN ([T1]."Main Area" = 1) THEN ('TZ')
                                                   WHEN ([T1]."Main Area" = 2) THEN ('Verkauf')
                                                   WHEN ([T1]."Main Area" = 3) THEN ('Service')
                                                   WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung')
                                                   ELSE ('nicht zuzuordnen') END) IN ('Service')) THEN ([T7]."No_" + ' - ' + [T7]."First Name" + ' ' + [T7]."Last Name")
                                        WHEN ((CASE
                                                   WHEN ([T1]."Main Area" = 0) THEN ('Sonstige')
                                                   WHEN ([T1]."Main Area" = 1) THEN ('TZ')
                                                   WHEN ([T1]."Main Area" = 2) THEN ('Verkauf')
                                                   WHEN ([T1]."Main Area" = 3) THEN ('Service')
                                                   WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung')
                                                   ELSE ('nicht zuzuordnen') END) IN ('Verkauf', 'Kassenbuchung', 'Sonstige', 'TZ')) THEN ([T5]."Code" + ' - ' + [T5]."Name")
                                        ELSE ('N.N.') END) IS NULL) THEN ('N.N.')
                             ELSE ((CASE
                                        WHEN ((CASE
                                                   WHEN ([T1]."Main Area" = 0) THEN ('Sonstige')
                                                   WHEN ([T1]."Main Area" = 1) THEN ('TZ')
                                                   WHEN ([T1]."Main Area" = 2) THEN ('Verkauf')
                                                   WHEN ([T1]."Main Area" = 3) THEN ('Service')
                                                   WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung')
                                                   ELSE ('nicht zuzuordnen') END) IN ('Service')) THEN ([T7]."No_" + ' - ' + [T7]."First Name" + ' ' + [T7]."Last Name")
                                        WHEN ((CASE
                                                   WHEN ([T1]."Main Area" = 0) THEN ('Sonstige')
                                                   WHEN ([T1]."Main Area" = 1) THEN ('TZ')
                                                   WHEN ([T1]."Main Area" = 2) THEN ('Verkauf')
                                                   WHEN ([T1]."Main Area" = 3) THEN ('Service')
                                                   WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung')
                                                   ELSE ('nicht zuzuordnen') END) IN ('Verkauf', 'Kassenbuchung', 'Sonstige', 'TZ')) THEN ([T5]."Code" + ' - ' + [T5]."Name")
                                        ELSE ('N.N.') END)) END AS "Sel Name"
--                        , CASE
--                              WHEN (
--                                  (CASE WHEN ([T1]."Main Area" = 0) THEN ('Sonstige') WHEN ([T1]."Main Area" = 1) THEN ('TZ') WHEN ([T1]."Main Area" = 2) THEN ('Verkauf') WHEN ([T1]."Main Area" = 3) THEN ('Service') WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung') ELSE ('nicht zuzuordnen') END) IN
--                                  ('Service')) THEN ([T7]."No_" + ' - ' + [T7]."First Name" + ' ' + [T7]."Last Name")
--                              WHEN (
--                                  (CASE WHEN ([T1]."Main Area" = 0) THEN ('Sonstige') WHEN ([T1]."Main Area" = 1) THEN ('TZ') WHEN ([T1]."Main Area" = 2) THEN ('Verkauf') WHEN ([T1]."Main Area" = 3) THEN ('Service') WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung') ELSE ('nicht zuzuordnen') END) IN
--                                  ('Verkauf', 'Kassenbuchung', 'Sonstige', 'TZ')) THEN ([T5]."Code" + ' - ' + [T5]."Name")
--                              ELSE ('N.N.') END AS "Sel Name_ori"
--                        , [T8]."Last Name" AS "Last Name_Employee_Salesperson_Code"
--                        , [T8]."First Name" AS "First Name_Employee_Salesperson_Code"
--                        , [T8]."No_" AS "No_Employee_Salesperson_Code"
--                        , [T7]."Last Name" AS "Last Name_Employee_Service_Advisor"
--                        , [T7]."First Name" AS "First Name_Employee_Service_Advisor"
--                        , [T7]."No_" AS "No_Employee_Service_Advisor"
--                        , [T6]."Name" AS "Name_Salesperson_Purchaser_Service"
--                        , [T6]."Code" AS "Code_Salesperson_Purchaser_Service"
--                        , [T5]."Name" AS "Name_Salesperson_Purchaser_Verkauf"
--                        , [T5]."Code" AS "Code_Salesperson_Purchaser_Verkauf"
--                        , [T4]."Service Advisor No_" AS "Service Advisor No_Sales_Inv_Header"
--                        , [T4]."Salesperson Code" AS "Salesperson Code_Sales_Inv_Header"
                       , CASE
                             WHEN ([T1]."Customer No_" LIKE '%Garan%') THEN ('Garantie')
                             WHEN ((CASE WHEN ([T3]."First Name" <> '') THEN ([T3]."Last Name" + ', ' + [T3]."First Name" + ' - ' + [T3]."No_") ELSE ([T3]."Name" + ' - ' + [T3]."No_") END) LIKE '%BMW%')
                                 THEN ((CASE WHEN ([T3]."First Name" <> '') THEN ([T3]."Last Name" + ', ' + [T3]."First Name" + ' - ' + [T3]."No_") ELSE ([T3]."Name" + ' - ' + [T3]."No_") END))
                             WHEN ([T1]."Customer No_" LIKE '%ASI%') THEN ((CASE WHEN ([T3]."First Name" <> '') THEN ([T3]."Last Name" + ', ' + [T3]."First Name" + ' - ' + [T3]."No_") ELSE ([T3]."Name" + ' - ' + [T3]."No_") END))
                             WHEN ((CASE WHEN ([T3]."First Name" <> '') THEN ([T3]."Last Name" + ', ' + [T3]."First Name" + ' - ' + [T3]."No_") ELSE ([T3]."Name" + ' - ' + [T3]."No_") END) LIKE '%Becker-Tiemann%') THEN ('BT Debitoren')
                             ELSE ('Kundenforderungen') END AS "Abwarten"
                       , CASE WHEN ([T1]."Customer No_" LIKE '%Garan%') THEN ('Garantie') ELSE ('Kundenforderungen') END AS "Forderungsart"
                       , [T1]."Last Issued Reminder Level" AS "Last Issued Reminder Level"
                       , CASE
                             WHEN (((-1 * datediff(DAY, (getdate()), [T1]."Due Date"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen')
                             WHEN (((-1 * datediff(DAY, (getdate()), [T1]."Due Date"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen')
                             WHEN (((-1 * datediff(DAY, (getdate()), [T1]."Due Date"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen')
                             WHEN (((-1 * datediff(DAY, (getdate()), [T1]."Due Date"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen')
                             WHEN (((-1 * datediff(DAY, (getdate()), [T1]."Due Date"))) > 84) THEN ('> 12 Wochen')
                             WHEN (((-1 * datediff(DAY, (getdate()), [T1]."Due Date"))) < 0) THEN (N'noch nicht fällig')
                    END AS "Staffel"
                       , (-1 * datediff(DAY, (getdate()), [T1]."Due Date")) AS "Tage"
                       , CASE WHEN ([T1]."Main Area" = 0) THEN ('Sonstige') WHEN ([T1]."Main Area" = 1) THEN ('TZ') WHEN ([T1]."Main Area" = 2) THEN ('Verkauf') WHEN ([T1]."Main Area" = 3) THEN ('Service') WHEN ([T1]."Main Area" = 5) THEN ('Kassenbuchung') ELSE ('nicht zuzuordnen') END AS "Bereich"
                       , CASE
                             WHEN ([T1]."Comment" <> '') THEN ([T1]."Document No_" + ' - ' + [T1]."Description" + ' - ' + [T1]."Comment" + ' - ' + [T1]."User ID" + ' - MS:' + ((convert(varchar(10), [T1]."Last Issued Reminder Level"))))
                             ELSE ([T1]."Document No_" + ' - ' + [T1]."Description" + ' - ' + [T1]."User ID" + ' - MS:' + ((convert(varchar(10), [T1]."Last Issued Reminder Level")))) END AS "Beleg"
                       , CASE WHEN ([T3]."First Name" <> '') THEN ([T3]."Last Name" + ', ' + [T3]."First Name" + ' - ' + [T3]."No_") ELSE ([T3]."Name" + ' - ' + [T3]."No_") END AS "Kunde"
					   , ([T3]."No_" + ' - ' + [T3]."Name") AS "Kunde_Dashboard"
--                        , [T3]."First Name" AS "First Name_Customer"
--                        , [T3]."Last Name" AS "Last Name_Customer"
--                        , [T3]."Name" AS "Name_Customer"
--                        , [T3]."No_" AS "No_Customer"
                       , [T1]."User ID" AS "User Id"
                       , CASE
                             WHEN ([T1]."Branch Code" IN ('01BSPKW')) THEN ('10')
                             WHEN ([T1]."Branch Code" IN ('02BSMOT')) THEN ('20')
                             WHEN ([T1]."Branch Code" IN ('04SFH')) THEN ('40')
                             WHEN ([T1]."Branch Code" IN ('05WT')) THEN ('50')
                             WHEN ([T1]."Branch Code" IN ('06BI')) THEN ('60')
                             WHEN ([T1]."Branch Code" IN ('07TR')) THEN ('70')
                             ELSE ([T1]."Branch Code") END AS "Standort"
                       , [T1]."Due Date" AS "Due Date"
--                        , [T2]."Initial Document Type" AS "Initial Document Type"
--                        , [T2]."Gen_ Prod_ Posting Group" AS "Gen Prod Posting Group"
--                        , [T2]."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group"
--                        , [T2]."Initial Entry Global Dim_ 2" AS "Initial Entry Global Dim 2"
--                        , [T2]."Initial Entry Global Dim_ 1" AS "Initial Entry Global Dim 1"
--                        , [T2]."Initial Entry Due Date" AS "Initial Entry Due Date"
--                        , (convert(float, [T2]."Credit Amount")) AS "Credit Amount"
--                        , (convert(float, [T2]."Debit Amount")) AS "Debit Amount"
--                        , [T2]."Source Code" AS "Source Code"
--                        , [T2]."User ID" AS "User Id_detail_cust"
--                        , [T2]."Customer No_" AS "Customer No"
                       , (convert(float, [T2]."Amount")) AS "Amount"
--                        , [T2]."Document No_" AS "Document No_detail"
--                        , [T2]."Document Type" AS "Document Type_detail_cust"
--                        , [T2]."Posting Date" AS "Posting Date"
--                        , [T2]."Entry Type" AS "Entry Type"
--                        , [T2]."Cust_ Ledger Entry No_" AS "Cust Ledger Entry No"
--                        , [T2]."Entry No_" AS "Entry No"
--                        , [T1]."Salesperson Code 2" AS "Salesperson Code 2"
--                        , [T1]."Service Advisor No_" AS "Service Advisor No"
                       , [T1]."Comment" AS "Comment"
--                        , [T1]."Cash Reg_ Receipt No_" AS "Cash Reg Receipt No"
--                        , [T1]."Factory No_" AS "Factory No"
                       , [T1]."VIN" AS "Vin"
--                        , [T1]."Pmt_ Disc_ Base" AS "Pmt Disc Base"
--                        , [T1]."Main Area" AS "Main Area"
                       , [T1]."Branch Code" AS "Branch Code"
--                        , [T1]."Customer Group Code" AS "Customer Group Code"
--                        , [T1]."Direct Debit Mandate ID" AS "Direct Debit Mandate Id"
--                        , [T1]."Dimension Set ID" AS "Dimension Set Id"
--                        , [T1]."No_ Series" AS "No Series"
--                        , [T1]."Document Date" AS "Document Date"
--                        , [T1]."Closed by Amount (LCY)" AS "Closed By Amount (lcy)"
--                        , [T1]."Journal Batch Name" AS "Journal Batch Name"
--                        , [T1]."Closed by Amount" AS "Closed By Amount"
--                        , [T1]."Closed at Date" AS "Closed At Date"
--                        , [T1]."Closed by Entry No_" AS "Closed By Entry No"
--                        , [T1]."Positive" AS "Positive"
--                        , [T1]."Pmt_ Discount Date" AS "Pmt Discount Date"
--                        , [T1]."Open" AS "Open"
--                        , [T1]."Applies-to Doc_ No_" AS "Applies-to Doc No"
--                        , [T1]."Applies-to Doc_ Type" AS "Applies-to Doc Type"
--                        , [T1]."On Hold" AS "On Hold_ori"
--                        , [T1]."Source Code" AS "Source Code_2"
--                        , [T1]."Salesperson Code" AS "Salesperson Code"
--                        , [T1]."Global Dimension 2 Code" AS "Global Dimension 2 Code"
--                        , [T1]."Global Dimension 1 Code" AS "Global Dimension 1 Code"
--                        , [T1]."Customer Posting Group" AS "Customer Posting Group"
--                        , [T1]."Sell-to Customer No_" AS "Sell-to Customer No"
--                        , (convert(float, [T1]."Profit (LCY)")) AS "Profit (lcy)"
--                        , [T1]."Description" AS "Description"
                       , [T1]."Document No_" AS "Document No"
                       , [T1]."Document Type" AS "Document Type"
--                        , [T1]."Posting Date" AS "Posting Date_2"
                       , [T1]."Customer No_" AS "Customer No_2"
                  FROM "xtract"."Detailed_Cust_Ledg_Entry" [T2]
                           INNER JOIN "xtract"."Cust_Ledger_Entry" [T1] ON [T1]."Entry No_" = [T2]."Cust_ Ledger Entry No_" AND [T1].[Client_DB] = [T2].[Client_DB]
                           LEFT OUTER JOIN "xtract"."Customer" [T3] ON [T1]."Customer No_" = [T3]."No_" AND [T1].[Client_DB] = [T3].[Client_DB]
                           LEFT OUTER JOIN "xtract"."Sales_Invoice_Header" [T4] ON [T1]."Document No_" = [T4]."No_" AND [T1].[Client_DB] = [T4].[Client_DB]
                           LEFT OUTER JOIN "xtract"."Salesperson_Purchaser" [T5] ON [T1]."Salesperson Code" = [T5]."Code" AND [T1].[Client_DB] = [T5].[Client_DB]
                           LEFT OUTER JOIN "xtract"."Salesperson_Purchaser" [T6] ON [T1]."Service Advisor No_" = [T6]."Code" AND [T1].[Client_DB] = [T6].[Client_DB]
                           LEFT OUTER JOIN "xtract"."Employee" [T7] ON [T4]."Service Advisor No_" = [T7]."No_" AND [T4].[Client_DB] = [T7].[Client_DB]
                           LEFT OUTER JOIN "xtract"."Employee" [T8] ON [T4]."Salesperson Code" = [T8]."No_" AND [T4].[Client_DB] = [T8].[Client_DB]
                  WHERE [T1]."Open" = 1) [D2]) [D1]) [D5]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO