SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_imr].[loc_belege]
AS
SELECT journal_accountings."accounting_date" AS "Accounting Date",
	journal_accountings."document_type" AS "Document Type",
	journal_accountings."document_number" AS "Document Number",
	journal_accountings."position_in_document" AS "Position In Document",
	journal_accountings."customer_number" AS "Customer Number",
	journal_accountings."nominal_account_number" AS "Nominal Account Number",
	journal_accountings."is_balanced" AS "Is Balanced",
	journal_accountings."clearing_number" AS "Clearing Number",
	journal_accountings."document_date" AS "Document Date",
	journal_accountings."posted_value" AS "Posted Value",
	journal_accountings."debit_or_credit" AS "Debit Or Credit",
	journal_accountings."posted_count" AS "Posted Count",
	journal_accountings."branch_number" AS "Branch Number",
	journal_accountings."customer_contra_account" AS "Customer Contra Account",
	journal_accountings."nominal_contra_account" AS "Nominal Contra Account",
	journal_accountings."contra_account_text" AS "Contra Account Text",
	journal_accountings."account_form_page_number" AS "Account Form Page Number",
	journal_accountings."account_form_page_line" AS "Account Form Page Line",
	journal_accountings."serial_number_each_month" AS "Serial Number Each Month",
	journal_accountings."employee_number" AS "Employee Number",
	try_convert(datetime, journal_accountings."invoice_date") AS "Invoice Date",
	journal_accountings."invoice_number" AS "Invoice Number",
	journal_accountings."dunning_level" AS "Dunning Level",
	journal_accountings."last_dunning_date" AS "Last Dunning Date",
	journal_accountings."journal_page" AS "Journal Page",
	journal_accountings."journal_line" AS "Journal Line",
	journal_accountings."cash_discount" AS "Cash Discount",
	journal_accountings."term_of_payment" AS "Term Of Payment",
	journal_accountings."posting_text" AS "Posting Text",
	journal_accountings."vehicle_reference" AS "Vehicle Reference",
	journal_accountings."vat_id_number" AS "Vat Id Number",
	journal_accountings."account_statement_number" AS "Account Statement Number",
	journal_accountings."account_statement_page" AS "Account Statement Page",
	journal_accountings."vat_key" AS "Vat Key",
	journal_accountings."days_for_cash_discount" AS "Days For Cash Discount",
	journal_accountings."day_of_actual_accounting" AS "Day Of Actual Accounting",
	journal_accountings."skr51_branch" AS "Skr51 Branch",
	convert(varchar, journal_accountings."skr51_make") AS "Skr51 Make",
	journal_accountings."skr51_cost_center" AS "Skr51 Cost Center",
	journal_accountings."skr51_sales_channel" AS "Skr51 Sales Channel",
	journal_accountings."skr51_cost_unit" AS "Skr51 Cost Unit",
	journal_accountings."previously_used_account_no" AS "Previously Used Account No",
	journal_accountings."free_form_accounting_text" AS "Free Form Accounting Text",
	CASE 
		WHEN (journal_accountings."skr51_cost_unit" BETWEEN 1 AND 49)
			THEN ('Neuwagen')
		WHEN (journal_accountings."skr51_cost_unit" BETWEEN 50 AND 59)
			THEN ('Gebrauchtwagen')
		WHEN (journal_accountings."skr51_cost_unit" BETWEEN 60 AND 69)
			THEN (N'Teile & Zubehör')
		WHEN (journal_accountings."skr51_cost_unit" BETWEEN 70 AND 79)
			THEN ('Service')
		WHEN (journal_accountings."skr51_cost_unit" = 0)
			THEN (N'Ohne Kostenträger')
		END AS "Free Form Document Text",
	nominal_accounts."is_profit_loss_account" AS "Nom_Account_Is Profit Loss Account",
	convert(varchar, journal_accountings."subsidiary_to_company_ref") AS "Rechtseinheit",
	convert(VARCHAR, journal_accountings."skr51_branch") AS "Betrieb",
	CASE 
		WHEN (
				(journal_accountings."posting_text" = N'Saldenübernahme CDK')
				OR (journal_accountings."accounting_date" = convert(DATETIME, '2019-01-01T00:00:00'))
				)
			THEN (convert(DATETIME, '2019-12-01T00:00:00'))
		ELSE (journal_accountings."accounting_date")
		END AS "Bookkeep Date",
	(rtrim((convert(VARCHAR(50), journal_accountings."skr51_make")))) + ' - ' + accounts_characteristics."skr51_make_description" AS "Marke",
	CASE 
		WHEN (
				(
					datediff(day, (
							convert(DATETIME, (
									CASE 
										WHEN (
												(journal_accountings."posting_text" = N'Saldenübernahme CDK')
												OR (journal_accountings."accounting_date" = convert(DATETIME, '2019-01-01T00:00:00'))
												)
											THEN (convert(DATETIME, '2019-12-01T00:00:00'))
										ELSE (journal_accountings."accounting_date")
										END
									))
							), (getdate()))
					 <= 90
				))
			THEN (journal_accountings."invoice_number" + ' - ' + journal_accountings."posting_text" + '/' + journal_accountings."vehicle_reference" + ' - ' + (substring((convert(VARCHAR(50), journal_accountings."employee_number")), 1, 4)))
		ELSE (N'Buchungen älter 90 Tage')
		END AS "Text",
	'1' AS "Mandant",
	CASE 
		WHEN (journal_accountings."debit_or_credit" = 'H')
			THEN (journal_accountings."posted_value" / 100 * - 1)
		ELSE (journal_accountings."posted_value" / 100)
		END AS "Betrag",
	(substring((convert(VARCHAR(50), journal_accountings."nominal_account_number")), 1, 4)) + ' - ' + nominal_accounts."account_description" AS "Konto_mit_Bezeichnung",
	CASE 
		WHEN (journal_accountings."debit_or_credit" = 'H')
			THEN (journal_accountings."posted_count" / 100 * - 1)
		ELSE (journal_accountings."posted_count" / 100)
		END AS "Menge",
	(len((convert(VARCHAR(50), journal_accountings."skr51_cost_center")))) AS "Stellen Cost Center",
	(rtrim((convert(VARCHAR(50), journal_accountings."skr51_cost_center")))) + ' - ' + accounts_characteristics."skr51_cost_center_name" AS "KST",
	(len((convert(VARCHAR(50), journal_accountings."skr51_sales_channel")))) AS "Stellen Sales Channel",
	(rtrim((convert(VARCHAR(50), journal_accountings."skr51_sales_channel")))) + ' - ' + accounts_characteristics."skr51_sales_channel_name" AS "Absatzkanal",
	(len((convert(VARCHAR(50), journal_accountings."skr51_cost_unit")))) AS "Stellen Cost Unit",
	(rtrim((convert(VARCHAR(50), journal_accountings."skr51_make")))) + ' - ' + (rtrim((convert(VARCHAR(50), journal_accountings."skr51_cost_unit")))) + ' - ' + (accounts_characteristics."skr51_cost_unit_name") AS "Kostenträger_mit_Null",
	CASE 
		WHEN (journal_accountings."skr51_cost_unit" BETWEEN 1 AND 49)
			THEN (((rtrim((convert(VARCHAR(50), journal_accountings."skr51_make")))) + ' - ' + (rtrim((convert(VARCHAR(50), journal_accountings."skr51_cost_unit")))) + ' - ' + (accounts_characteristics."skr51_cost_unit_name")))
		ELSE ((rtrim((convert(VARCHAR(50), journal_accountings."skr51_cost_unit")))) + ' - ' + (accounts_characteristics."skr51_cost_unit_name"))
		END AS "Kostenträger",
	CASE 
		WHEN (nominal_accounts."is_profit_loss_account" = 'J')
			THEN ('2')
		ELSE ('1')
		END AS "GuV_Bilanz",
	(substring((convert(VARCHAR(50), journal_accountings."nominal_account_number")), 1, 1)) AS "Susa",
	CASE 
		WHEN (journal_accountings."skr51_cost_center" <> 0)
			THEN ((rtrim((convert(VARCHAR(50), journal_accountings."nominal_account_number")))) + '_' + (rtrim((convert(VARCHAR(50), journal_accountings."skr51_cost_center")))))
		ELSE ((convert(VARCHAR(50), journal_accountings."nominal_account_number")))
		END AS "Acct Nr"
FROM LOCOSOFT."dbo"."nominal_accounts" nominal_accounts,
	(
		LOCOSOFT."dbo"."journal_accountings" journal_accountings LEFT OUTER JOIN LOCOSOFT."dbo"."accounts_characteristics" accounts_characteristics ON (
				(
					(
						(
							(accounts_characteristics."subsidiary_to_company_ref" = journal_accountings."subsidiary_to_company_ref")
							AND (accounts_characteristics."skr51_branch" = journal_accountings."skr51_branch")
							)
						AND (accounts_characteristics."skr51_make" = journal_accountings."skr51_make")
						)
					AND (accounts_characteristics."skr51_cost_center" = journal_accountings."skr51_cost_center")
					)
				AND (accounts_characteristics."skr51_sales_channel" = journal_accountings."skr51_sales_channel")
				)
			AND (accounts_characteristics."skr51_cost_unit" = journal_accountings."skr51_cost_unit")
		)
WHERE (
		(nominal_accounts."nominal_account_number" = journal_accountings."nominal_account_number")
		AND (nominal_accounts."subsidiary_to_company_ref" = journal_accountings."subsidiary_to_company_ref")
		)
	AND (
		(nominal_accounts."is_profit_loss_account" = 'J')
		AND (NOT journal_accountings."posting_text" IN (N'G&V-Abschlussbuchung'))
		)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
