C11/System/LOCOSOFT/SQL/schema/LOCOSOFT/views/locosoft.LOC_Belege.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW locosoft.LOC_Belege AS

select 
    T1."accounting_date" as "Accounting Date",
    T1."document_type" as "Document Type",
    T1."document_number" as "Document Number",
    T1."position_in_document" as "Position In Document",
    T1."customer_number" as "Customer Number",
    T1."nominal_account_number" as "Nominal Account Number",
    T1."is_balanced" as "Is Balanced",
    T1."clearing_number" as "Clearing Number",
    T1."document_date" as "Document Date",
    T1."posted_value" as "Posted Value",
    T1."debit_or_credit" as "Debit Or Credit",
    T1."posted_count" as "Posted Count",
    T1."branch_number" as "Branch Number",
    T1."customer_contra_account" as "Customer Contra Account",
    T1."nominal_contra_account" as "Nominal Contra Account", 
    T1."contra_account_text" as "Contra Account Text",
    T1."account_form_page_number" as "Account Form Page Number",
    T1."account_form_page_line" as "Account Form Page Line",
    T1."serial_number_each_month" as "Serial Number Each Month",
    T1."employee_number" as "Employee Number",
    T1."invoice_date" as "Invoice Date",
    T1."invoice_number" as "Invoice Number",
    T1."dunning_level" as "Dunning Level",   
    T1."last_dunning_date" as "Last Dunning Date",
    T1."journal_page" as "Journal Page",
    T1."journal_line" as "Journal Line",
    T1."cash_discount" as "Cash Discount",
    T1."term_of_payment" as "Term Of Payment",
    T1."posting_text" as "Posting Text",
    T1."vehicle_reference" as "Vehicle Reference",
    T1."vat_id_number" as "Vat Id Number",
    T1."account_statement_number" as "Account Statement Number",
    T1."account_statement_page" as "Account Statement Page",
    T1."vat_key" as "Vat Key",
    T1."days_for_cash_discount" as "Days For Cash Discount",
    T1."day_of_actual_accounting" as "Day Of Actual Accounting",
    T1."skr51_branch" as "Skr51 Branch",
    T1."skr51_make" as "Skr51 Make",
    T1."skr51_cost_center" as "Skr51 Cost Center",
    T1."skr51_sales_channel" as "Skr51 Sales Channel",
    T1."skr51_cost_unit" as "Skr51 Cost Unit",
    T1."previously_used_account_no" as "Previously Used Account No",
    T1."free_form_accounting_text" as "Free Form Accounting Text",

    case
        when T1."skr51_cost_unit" between 1 and 49 then 'Neuwagen'
        when T1."skr51_cost_unit" between 50 and 59 then 'Gebrauchtwagen'
        when T1."skr51_cost_unit" between 60 and 69 then 'Teile & Zubehör'
        when T1."skr51_cost_unit" between 70 and 79 then 'Service'
        when T1."skr51_cost_unit" = 0 then 'Ohne Kostenträger'
        else NULL
    end as "Free Form Document Text",
    T2."is_profit_loss_account" as "Nom_Account_Is Profit Loss Account",
    '1' as "Rechtseinheit",
    { fn CONCAT(
        '0',
        CAST(
            CAST(T1."subsidiary_to_company_ref" AS INTEGER) AS CHAR(254)
        )
    ) } as "Betrieb",
    case
        when T1."posting_text" = 'Saldenübernahme CDK'
        or T1."accounting_date" = '2019-01-01' then convert(datetime, '2019-12-01')
        else T1."accounting_date"
    end as "Bookkeep Date",
    { fn CONCAT(
        { fn CONCAT(
            { fn RTRIM(
                CAST(CAST(T1."skr51_make" AS INTEGER) AS CHAR(254))
            ) },
            ' - '
        ) },
        T3."skr51_make_description"
    ) } as "Marke",
    
    
    isnull(CAST(T1."invoice_number" as VARCHAR(100)), '') + ' - ' + T1."posting_text" + '/' + isnull(T1."vehicle_reference",'') + ' - ' + isnull(CAST(T1."employee_number" as VARCHAR(50)), '') as "Text",


    '1' as "Mandant",
    case
        when T1."debit_or_credit" = 'H' then (CAST(T1."posted_value" AS FLOAT) / 100) * -1
        else CAST(T1."posted_value" AS FLOAT) / 100
    end as "Betrag",
    { fn CONCAT(
        { fn CONCAT(
            { fn LEFT(
                CAST(
                    CAST(T1."nominal_account_number" AS INTEGER) AS CHAR(254)
                ),
                4
            ) },
            ' - '
        ) },
        T2."account_description"
    ) } as "Konto_mit_Bezeichnung",
    case
        when T1."debit_or_credit" = 'H' then (CAST(T1."posted_count" AS FLOAT) / 100) * -1
        else CAST(T1."posted_count" AS FLOAT) / 100
    end as "Menge",
    { fn LENGTH(
        { fn CONCAT(
            CAST(
                CAST(T1."skr51_cost_center" AS INTEGER) AS CHAR(254)
            ),
            'Z'
        ) }
    ) } - 1 as "Stellen Cost Center",
    { fn CONCAT(
        { fn CONCAT(
            { fn RTRIM(
                CAST(
                    CAST(T1."skr51_cost_center" AS INTEGER) AS CHAR(254)
                )
            ) },
            ' - '
        ) },
        T3."skr51_cost_center_name"
    ) } as "KST",
    { fn LENGTH(
        { fn CONCAT(
            CAST(
                CAST(T1."skr51_sales_channel" AS INTEGER) AS CHAR(254)
            ),
            'Z'
        ) }
    ) } - 1 as "Stellen Sales Channel",

    { fn CONCAT(
        { fn CONCAT(
            { fn RTRIM(
                CAST(
                    CAST(T1."skr51_sales_channel" AS INTEGER) AS CHAR(254)
                )
            ) },
            ' - '
        ) },
        T3."skr51_sales_channel_name"
    ) } as "Absatzkanal",
    { fn LENGTH(
        { fn CONCAT(
            CAST(
                CAST(T1."skr51_cost_unit" AS INTEGER) AS CHAR(254)
            ),
            'Z'
        ) }
    ) } - 1 as "Stellen Cost Unit",
    { fn CONCAT(
        { fn CONCAT(
            { fn CONCAT(
                { fn CONCAT(
                    { fn RTRIM(
                        CAST(CAST(T1."skr51_make" AS INTEGER) AS CHAR(254))
                    ) },
                    ' - '
                ) },
                { fn RTRIM(
                    CAST(
                        CAST(T1."skr51_cost_unit" AS INTEGER) AS CHAR(254)
                    )
                ) }
            ) },
            ' - '
        ) },
        T3."skr51_cost_unit_name"
    ) } as "Kostenträger_mit_Null",
    case
        when T1."skr51_cost_unit" between 1 and 49 then { fn CONCAT(
            { fn CONCAT(
                { fn CONCAT(
                    { fn CONCAT(
                        { fn RTRIM(
                            CAST(CAST(T1."skr51_make" AS INTEGER) AS CHAR(254))
                        ) },
                        ' - '
                    ) },
                    { fn RTRIM(
                        CAST(
                            CAST(T1."skr51_cost_unit" AS INTEGER) AS CHAR(254)
                        )
                    ) }
                ) },
                ' - '
            ) },
            T3."skr51_cost_unit_name"
        ) }
        else { fn CONCAT(
            { fn CONCAT(
                { fn RTRIM(
                    CAST(
                        CAST(T1."skr51_cost_unit" AS INTEGER) AS CHAR(254)
                    )
                ) },
                ' - '
            ) },
            T3."skr51_cost_unit_name"
        ) }
    end as "Kostenträger",
    case
        when T2."is_profit_loss_account" = 'J' then '2'
        else '1'
    end as "GuV_Bilanz",
    { fn LEFT(
        CAST(
            CAST(T1."nominal_account_number" AS INTEGER) AS CHAR(254)
        ),
        1
    ) } as "Susa",
    case
        when T1."skr51_cost_center" <> 0 then { fn CONCAT(
            { fn CONCAT(
                { fn RTRIM(
                    CAST(
                        CAST(T1."nominal_account_number" AS INTEGER) AS CHAR(254)
                    )
                ) },
                '_'
            ) },
            { fn RTRIM(
                CAST(
                    CAST(T1."skr51_cost_center" AS INTEGER) AS CHAR(254)
                )
            ) }
        ) }
        else CAST(
            CAST(T1."nominal_account_number" AS INTEGER) AS CHAR(254)
        )
    end as "Acct Nr"


from "dbo"."journal_accountings" T1
    INNER JOIN "dbo"."nominal_accounts" T2 ON T2."nominal_account_number" = T1."nominal_account_number"
    and T2."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref"
    LEFT OUTER JOIN "dbo"."accounts_characteristics" T3 on T1."subsidiary_to_company_ref" = T3."subsidiary_to_company_ref"
    and T1."skr51_branch" = T3."skr51_branch"
    and T1."skr51_make" = T3."skr51_make"
    and T1."skr51_cost_center" = T3."skr51_cost_center"
    and T1."skr51_sales_channel" = T3."skr51_sales_channel"
    and T1."skr51_cost_unit" = T3."skr51_cost_unit"
where 
    T2."is_profit_loss_account" = 'J'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO