C11/System/LOCOSOFT/IQD/Belege/loc_belege.sql

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",
	   CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END as "Posting Text",
	   CASE WHEN (T1."vehicle_reference" IS NULL) THEN (' - ') ELSE (T1."vehicle_reference") END 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",
	   T1."subsidiary_to_company_ref" as "Rechtseinheit",
	   '0' + ((convert(varchar(50), T1."skr51_branch"))) as "Betrieb",
	   CASE WHEN (((CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) = 'Saldenübernahme CDK') or (T1."accounting_date" = convert(date, '2019-01-01'))) THEN (convert(date, '2019-12-01')) ELSE (T1."accounting_date") END as "Bookkeep Date",
	   (rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + T3."skr51_make_description" as "Marke",
	   CASE WHEN ((day((getdate()) - (convert(datetime, (CASE WHEN (((CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) = 'Saldenübernahme CDK') or (T1."accounting_date" = convert(date, '2019-01-01'))) THEN (convert(date, '2019-12-01')) ELSE (T1."accounting_date") END))))) <= 90) THEN ((convert(varchar(50), ((T1."document_number")))) + ' - ' + (CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) + '/' + (CASE WHEN (T1."vehicle_reference" IS NULL) THEN (' - ') ELSE (T1."vehicle_reference") END) + ' - ' + (left(((convert(varchar(50), T1."employee_number")))))) ELSE ('Buchungen älter 90 Tage') END as "Text",
	   '1' as "Mandant",
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_value" / 100 * -1) ELSE (T1."posted_value" / 100) END as "Betrag",
	   (left(((convert(varchar(50), T1."nominal_account_number"))))) + ' - ' + T2."account_description" as "Konto_mit_Bezeichnung",
	   CASE WHEN (T1."debit_or_credit" = 'H') THEN (T1."posted_count" / 100 * -1) ELSE (T1."posted_count" / 100) END as "Menge",
	   (len(((convert(varchar(50), T1."skr51_cost_center"))) + 'Z') - 1) as "Stellen Cost Center",
	   (rtrim(((convert(varchar(50), T1."skr51_cost_center"))))) + ' - ' + T3."skr51_cost_center_name" as "KST",
	   (len(((convert(varchar(50), T1."skr51_sales_channel"))) + 'Z') - 1) as "Stellen Sales Channel",
	   (rtrim(((convert(varchar(50), T1."skr51_sales_channel"))))) + ' - ' + T3."skr51_sales_channel_name" as "Absatzkanal",
	   (len(((convert(varchar(50), T1."skr51_cost_unit"))) + 'Z') - 1) as "Stellen Cost Unit",
	   (rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + (rtrim(((convert(varchar(50), T1."skr51_cost_unit"))))) + ' - ' + (T3."skr51_cost_unit_name") as "Kostenträger_mit_Null",
	   CASE WHEN (T1."skr51_cost_unit" BETWEEN 1 AND 49) THEN (((rtrim(((convert(varchar(50), T1."skr51_make"))))) + ' - ' + (rtrim(((convert(varchar(50), T1."skr51_cost_unit"))))) + ' - ' + (T3."skr51_cost_unit_name"))) ELSE ((rtrim(((convert(varchar(50), T1."skr51_cost_unit"))))) + ' - ' + (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",
	   (left(((convert(varchar(50), T1."nominal_account_number"))))) as "Susa",
	   CASE WHEN (T1."skr51_cost_center" <> 0) THEN ((rtrim(((convert(varchar(50), T1."nominal_account_number"))))) + '_' + (rtrim(((convert(varchar(50), T1."skr51_cost_center")))))) ELSE (((convert(varchar(50), T1."nominal_account_number")))) END as "Acct Nr"
from "dbo"."nominal_accounts" T2,
	("dbo"."journal_accountings" T1 left outer join "dbo"."accounts_characteristics" T3 on (((((T3."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref") and (T3."skr51_branch" = T1."skr51_branch")) and (T3."skr51_make" = T1."skr51_make")) and (T3."skr51_cost_center" = T1."skr51_cost_center")) and (T3."skr51_sales_channel" = T1."skr51_sales_channel")) and (T3."skr51_cost_unit" = T1."skr51_cost_unit"))
where ((T2."nominal_account_number" = T1."nominal_account_number") and (T2."subsidiary_to_company_ref" = T1."subsidiary_to_company_ref"))
and ((T2."is_profit_loss_account" = 'J') and (not (CASE WHEN (T1."posting_text" IS NULL) THEN (' - ') ELSE (T1."posting_text") END) IN ('G&V-Abschlussbuchung')))
-- order by "Nominal Account Number" asc