C11/System/ARI/SQL/schema/GC_ETL_ARI/views/transform.Belege.sql

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

--Belege
SELECT [Client_DB]
     ,
     --[No] AS [No],
     --[Name] AS [Name],
     --[Account Type] AS [Account Type],
     --[Income Balance] AS [Income Balance],
     --[Last Date Modified] AS [Last Date Modified],
     --[Entry No] AS [Entry No],
     --[G L Account No] AS [G L Account No],
     --[Posting Date] AS [Posting Date],
     --[Document Type] AS [Document Type],
     --[Document No] AS [Document No],
     --[Description] AS [Description],
     --[Bal Account No] AS [Bal Account No],
     [Department Code] AS [Department Code],
     [Make Code] AS [Make Code],
     --[User Id] AS [User Id],
     --[Source Code] AS [Source Code],
     --[Quantity] AS [Quantity],
     --[Reason Code] AS [Reason Code],
     --[Gen Posting Type] AS [Gen Posting Type],
     --[Gen Bus Posting Group] AS [Gen Bus Posting Group],
     --[Gen Prod Posting Group] AS [Gen Prod Posting Group],
     --[Bal Account Type] AS [Bal Account Type],
     --[Transaction No] AS [Transaction No],
     --[Document Date] AS [Document Date],
     --[External Document No] AS [External Document No],
     --[Source Type] AS [Source Type],
     --[Source No] AS [Source No],
     --[No Series] AS [No Series],
     [Branch Code] AS [Branch Code],
     --[Main Area] AS [Main Area],
     [Vin] AS [Vin],
     --[Book No] AS [Book No],
     --[Veh Source Code] AS [Veh Source Code],
     --[Reposted] AS [Reposted],
     --[Corrected] AS [Corrected],
     --[Correction To Curr No] AS [Correction To Curr No],
     --[Reposting To Curr No] AS [Reposting To Curr No],
     --[Code] AS [Code],
    [Bookkeep Date] AS [Bookkeep Date]
     , [Betrieb] AS [Betrieb Nr]
     , [Acct Nr] AS [Acct Nr]
     , [Betrag] AS [Betrag]
     --[Amount_1] AS [Amount_1],
     , [Text] AS [Text]
	 , [Beleg] AS [Beleg]
     , [Hauptbetrieb] AS [Hauptbetrieb]
     , [Marke] AS [Marke]
FROM (SELECT [T1].[Client_DB]
           ,
--           [T1]."No_" AS "No"
--            , [T1]."Name" AS "Name_2"
--            , [T1]."Account Type" AS "Account Type"
--            , [T1]."Income_Balance" AS "Income Balance"
--            , [T1]."Last Date Modified" AS "Last Date Modified"
--            , [T2]."Entry No_" AS "Entry No"
--            , [T2]."G_L Account No_" AS "G L Account No"
--            , [T2]."Posting Date" AS "Posting Date"
--            , [T2]."Document Type" AS "Document Type"
--            , [T2]."Document No_" AS "Document No"
--            , [T2]."Description" AS "Description"
--            , [T2]."Bal_ Account No_" AS "Bal Account No"
             [T2]."Global Dimension 1 Code" AS "Department Code"
            , [T2]."Global Dimension 2 Code" AS "Make Code"
--            , [T2]."User ID" AS "User Id"
--            , [T2]."Source Code" AS "Source Code"
--            , CASE WHEN (([T2]."Document No_" LIKE 'VRGGFZ%') OR ([T2]."Document No_" LIKE 'VRGF%')) THEN ([T2]."Veh_ Source Code") WHEN ((([T2]."Document No_" LIKE 'VGUGFZ%') OR ([T2]."Document No_" LIKE 'VGGF%')) AND ([T2]."Veh_ Source Code" <> 0)) THEN (-1) ELSE (0) END AS "Quantity"
--            , [T2]."Reason Code" AS "Reason Code"
--            , [T2]."Gen_ Posting Type" AS "Gen Posting Type"
--            , [T2]."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group"
--            , [T2]."Gen_ Prod_ Posting Group" AS "Gen Prod Posting Group"
--            , [T2]."Bal_ Account Type" AS "Bal Account Type"
--            , [T2]."Transaction No_" AS "Transaction No"
--            , [T2]."Document Date" AS "Document Date"
--            , [T2]."External Document No_" AS "External Document No"
--            , [T2]."Source Type" AS "Source Type"
--            , [T2]."Source No_" AS "Source No"
--            , [T2]."No_ Series" AS "No Series"
            , [T2]."Branch Code" AS "Branch Code"
--            , [T2]."Main Area" AS "Main Area"
            , [T2]."VIN" AS "Vin"
--            , [T2]."Book No_" AS "Book No"
--            , [T2]."Veh_ Source Code" AS "Veh Source Code"
--            , [T2]."Reposted" AS "Reposted"
--            , [T2]."Corrected" AS "Corrected"
--            , [T2]."Correction to curr_ No_" AS "Correction To Curr No"
--            , [T2]."Reposting to curr_ No_" AS "Reposting To Curr No"
--            , [T3]."Code" AS "Code"
--            , [T3]."Name" AS "Name"
          ,[T2]."Posting Date" AS "Bookkeep Date"
 --          , CASE
--                 WHEN ([T2]."Branch Code" IN ('01BSPKW')) THEN ('10')
--                 WHEN ([T2]."Branch Code" IN ('02BSMOT')) THEN ('20')
--                 WHEN ([T2]."Branch Code" IN ('03RHF')) THEN ('30')
--                 WHEN ([T2]."Branch Code" IN ('04SFH')) THEN ('40')
--                 WHEN ([T2]."Branch Code" IN ('05WT')) THEN ('50')
--                 WHEN ([T2]."Branch Code" IN ('06BI')) THEN ('60')
--                 WHEN ([T2]."Branch Code" IN ('07TR')) THEN ('70')
--                 ELSE ((left([T2]."Global Dimension 1 Code", 2))) END AS "Betrieb"
		   , [T2]."Branch Code" AS "Betrieb"
           , [T2]."G_L Account No_" AS "Acct Nr"
           , ((convert(float, [T2]."Amount"))) AS "Betrag"
--            , (convert(float, [T2]."Amount")) AS "Amount_1"
           , CASE WHEN ((-1 * datediff(DAY, (getdate()), [T2]."Posting Date")) <= 60) THEN ([T2]."Document No_" + ' - ' + [T2]."Description" + ' - ' + [T2]."User ID") ELSE (N'Belege älter 60 Tage') END AS "Text"
           , CASE WHEN ((-1 * datediff(DAY, (getdate()), [T2]."Posting Date")) <= 1850) THEN ([T2]."Document No_" + ' - ' + [T2]."Description" + ' - ' + [T2]."User ID") ELSE (N'Belege älter 120 Tage') END AS "Beleg"
           , '1' AS "Hauptbetrieb"
           , CASE WHEN ([T2]."Global Dimension 2 Code" IN ('BMW', 'BMW I', 'BMW-C1', 'BMWI', 'BMW-MINI', 'BMW-MOT', 'BMWVW')) THEN ([T2]."Global Dimension 2 Code") ELSE ('Fremdfabrikat') END AS "Marke"
      FROM "xtract"."G_L_Account" [T1]
               INNER JOIN "xtract"."G_L_Entry" [T2] ON [T1]."No_" = [T2]."G_L Account No_" AND [T1].[Client_DB] = [T2].[Client_DB]
      --LEFT OUTER JOIN "xtract"."Department" [T3] ON [T2]."Global Dimension 1 Code" = [T3]."Code" AND T2.[Client_DB] = T3.[Client_DB]
      WHERE [T1]."Income_Balance" = 0
 --       AND [T2]."Description" NOT like ('%GuV-Konten Nullstellung%', '%GuV Konten Nullstellung%')
		AND [T2]."Description" NOT LIKE '%GuV-Konten Nullstellung%'
		AND [T2]."Description" NOT LIKE '%GuV Konten Nullstellung%'
        AND [T2]."Posting Date" >= convert(datetime, '2020-01-01T00:00:00.000')
        AND year([T2]."Posting Date") <= year(getdate())) [D5]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO