C11/System/LOCOSOFT/IQD/Belege/loc_belege_bilanz.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",
	'-' 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",
	(left(T1."free_form_accounting_text", 100)) 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",
	T1."accounting_date" AS "Bookkeep Date",
	(rtrim((((T1."skr51_make"))))) AS "Marke",
	'' 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",
	(
		rtrim((
				CASE 
					WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 1)
						THEN ('000' + (rtrim((((T1."nominal_account_number"))))))
					WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 2)
						THEN ('00' + (rtrim((((T1."nominal_account_number"))))))
					WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 3)
						THEN ('0' + (rtrim((((T1."nominal_account_number"))))))
					ELSE ((rtrim((((T1."nominal_account_number"))))))
					END
				))
		) + ' - ' + 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",
	(
		substring((
				CASE 
					WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 1)
						THEN ('000' + (rtrim((((T1."nominal_account_number"))))))
					WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 2)
						THEN ('00' + (rtrim((((T1."nominal_account_number"))))))
					WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 3)
						THEN ('0' + (rtrim((((T1."nominal_account_number"))))))
					ELSE ((rtrim((((T1."nominal_account_number"))))))
					END
				), 1, 1)
		) AS "Susa",
	CASE 
		WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 1)
			THEN ('000' + (rtrim((((T1."nominal_account_number"))))))
		WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 2)
			THEN ('00' + (rtrim((((T1."nominal_account_number"))))))
		WHEN (((len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1)) = 3)
			THEN ('0' + (rtrim((((T1."nominal_account_number"))))))
		ELSE ((rtrim((((T1."nominal_account_number"))))))
		END AS "Acct Nr",
	(len((rtrim((((T1."nominal_account_number"))))) + 'Z') - 1) AS "Stellen_Konto_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" = 'N')
	-- order by "Nominal Account Number" asc