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' + (((T1."branch_number"))) 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((((T1."skr51_make"))))) AS "Marke",
	CASE 
		WHEN (
				(
					- 1 * datediff(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((((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((((T1."nominal_account_number"))), 4)) + ' - ' + 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((((T1."skr51_cost_center"))) + 'Z') - 1) AS "Stellen Cost Center",
	(rtrim((((T1."skr51_cost_center"))))) + ' - ' + T3."skr51_cost_center_name" AS "KST",
	(len((((T1."skr51_sales_channel"))) + 'Z') - 1) AS "Stellen Sales Channel",
	(rtrim((((T1."skr51_sales_channel"))))) + ' - ' + T3."skr51_sales_channel_name" AS "Absatzkanal",
	(len((((T1."skr51_cost_unit"))) + 'Z') - 1) AS "Stellen Cost Unit",
	(rtrim((((T1."skr51_make"))))) + ' - ' + (rtrim((((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((((T1."skr51_make"))))) + ' - ' + (rtrim((((T1."skr51_cost_unit"))))) + ' - ' + (T3."skr51_cost_unit_name")))
		ELSE ((rtrim((((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((((T1."nominal_account_number"))), 1)) AS "Susa",
	CASE 
		WHEN (NOT T1."skr51_cost_center" IN (0, 2))
			THEN ((rtrim((((T1."nominal_account_number"))))) + '_' + (rtrim((((T1."skr51_cost_center"))))))
		ELSE ((((T1."nominal_account_number"))))
		END AS "Acct Nr"
FROM "dbo"."nominal_accounts" T2,
	(
		"dbo"."journal_accountings" T1 LEFT 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