SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [xtract].[accounts_characteristics] AS

WITH
    "accounts_chars"
        AS (SELECT [client_db]
                 , [subsidiary_to_company_ref]
                 , [skr51_branch]
                 , [skr51_make]
                 , [skr51_cost_center]
                 , [skr51_sales_channel]
                 , [skr51_cost_unit]
                 , [skr51_brach_name]
                 , [skr51_make_description]
                 , [skr51_cost_center_name]
                 , [skr51_sales_channel_name]
                 , [skr51_cost_unit_name]
                 , convert(varchar(10), [skr51_branch]) AS [Standort_Code]
                 , convert(varchar(10), [skr51_make]) AS [Marke_Code]
                 , right('0' + convert(varchar(10), [skr51_cost_center]), 2) AS [Kostenstelle_Code]
                 , right('0' + convert(varchar(10), [skr51_sales_channel]), 2) AS [Absatzkanal_Code]
                 , right('0' + convert(varchar(10), [skr51_cost_unit]), 2) AS [Kostenträger_Code]
            FROM [LOCOSOFT].[dbo].[accounts_characteristics])

SELECT [client_db]
     , [subsidiary_to_company_ref]
     , [skr51_branch]
     , [skr51_make]
     , [skr51_cost_center]
     , [skr51_sales_channel]
     , [skr51_cost_unit]
     , [skr51_brach_name]
     , [skr51_make_description]
     , [skr51_cost_center_name]
     , [skr51_sales_channel_name]
     , [skr51_cost_unit_name]
     , [Standort_Code]
     , [skr51_brach_name] AS [Standort_Name]
     , [Standort_Code] + ' - ' + [skr51_brach_name] AS [Standort_mit_Bez]
     , [Marke_Code]
     , [skr51_make_description] AS [Marke_Name]
     , + ' - ' + [skr51_make_description] AS [Marke_mit_Bez]
     , [Kostenstelle_Code]
     , left([Kostenstelle_Code], 1) AS [Kostenstelle_1_Stelle]
     , [skr51_cost_center_name] AS [Kostenstelle_Name]
     , [Kostenstelle_Code] + ' - ' + [skr51_cost_center_name] AS [Kostenstelle_mit_Bez]
     , [Absatzkanal_Code]
     , left([Absatzkanal_Code], 1) AS [Absatzkanal_1_Stelle]
     , [skr51_sales_channel_name] AS [Absatzkanal_Name]
     , [Absatzkanal_Code] + ' - ' + [skr51_sales_channel_name] AS [Absatzkanal_mit_Bez]
     , [Kostenträger_Code]
     , left([Kostenträger_Code], 1) AS [Kostenträger_1_Stelle]
     , [skr51_cost_unit_name] AS [Kostenträger_Name]
     , [Kostenträger_Code] + ' - ' + [skr51_cost_unit_name] AS [Kostenträger_mit_Bez]
     , [Marke_Code] + ' - ' + [Kostenträger_Code] + ' - ' + [skr51_cost_unit_name] AS [Kostenträger_Marke_mit_Bez]

FROM [accounts_chars]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
