C11/System/LOCOSOFT/IQD/OP/Kreditorenbewegungen.sql

SELECT "Accounting Date",
	"Document Type",
	"Document Number",
	"Position In Document",
	"Nominal Account Number",
	"Customer Number",
	"Is Balanced",
	"Clearing Number",
	"Document Date",
	"Posted Value",
	"Debit Or Credit",
	"Posted Count",
	"Branch Number",
	"Customer Contra Account",
	"Nominal Contra Account",
	"Contra Account Text",
	"Account Form Page Number",
	"Account Form Page Line",
	"Serial Number Each Month",
	"Employee Number",
	"Invoice Date",
	"Invoice Number",
	"Dunning Level",
	"Last Dunning Date",
	"Journal Page",
	"Journal Line",
	"Cash Discount",
	"Term Of Payment",
	"Posting Text",
	"Vehicle Reference",
	"Vat Id Number",
	"Account Statement Number",
	"Account Statement Page",
	"Vat Key",
	"Days For Cash Discount",
	"Day Of Actual Accounting",
	"Skr51 Branch",
	"Skr51 Make",
	"Skr51 Cost Center",
	"Skr51 Sales Channel",
	"Skr51 Cost Unit",
	"Previously Used Account No",
	"Free Form Accounting Text",
	"Free Form Document Text",
	"Rechtseinheit",
	"Betrieb",
	"Accounting Date" AS "Bookkeep Date",
	"Text",
	'1' AS "Mandant",
	"Betrag",
	"Menge",
	"Nominal Account Number" AS "Acct Nr",
	"Customer Number_suctomer_supplier",
	"Is Supplier",
	"First Name",
	"Family Name",
	"Document Type In Journal",
	"Document Type Description",
	SUM("Betrag") OVER (PARTITION BY "Customer Number_suctomer_supplier") AS "Debitorsaldo_",
	"Invoice Type_Invoices",
	"Invoice Number_Invoices",
	"Subsidiary_Invoices",
	"Invoice Date_Invoices",
	"Rechtseinheit" AS "Hauptbetrieb",
	"Standort_ori",
	"Sel Name",
	"Betrag" AS "OP-Saldo",
	"Kostenstelle",
	"Tage",
	"Staffel",
	"Kunde",
	"Beleg",
	"Employee Number_Employees_journal_accountings",
	"Name_Employees_journal_accountings",
	"Anzahl_Stellen_Invoice_Number",
	"Forderungsart_aus_erste_Stelle_Inv_Number",
	"Forderungsart",
	"Standort_aus_2_Stelle_Invoice_Number",
	"Betrieb" AS "Standort",
	"Status Rechnung"
FROM (
	SELECT T3."customer_number" AS "Customer Number_suctomer_supplier",
		CASE 
			WHEN (T1."clearing_number" = 0)
				THEN ('offen')
			ELSE ('bezahlt')
			END AS "Status Rechnung",
		((((T1."branch_number")))) AS "Betrieb",
		CASE 
			WHEN (
					(((len(T1."invoice_number" + 'Z') - 1)) = 7)
					AND ((substring(T1."invoice_number", 2, 1)) BETWEEN '1' AND '4')
					)
				THEN ('0' + (substring(T1."invoice_number", 2, 1)))
			ELSE NULL
			END AS "Standort_aus_2_Stelle_Invoice_Number",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (T5."invoice_type" IN (2, 4, 6, 3))
								THEN ('3')
							WHEN (T5."invoice_type" IN (5))
								THEN ('6')
							WHEN (T5."invoice_type" IN (7))
								THEN ('1')
							WHEN (T5."invoice_type" IN (8))
								THEN ('2')
							WHEN (T4."document_type_in_journal" = 'R')
								THEN ('7')
							ELSE NULL
							END
						) = '1'
					)
				THEN ('Neuwagen')
			WHEN (
					(
						CASE 
							WHEN (T5."invoice_type" IN (2, 4, 6, 3))
								THEN ('3')
							WHEN (T5."invoice_type" IN (5))
								THEN ('6')
							WHEN (T5."invoice_type" IN (7))
								THEN ('1')
							WHEN (T5."invoice_type" IN (8))
								THEN ('2')
							WHEN (T4."document_type_in_journal" = 'R')
								THEN ('7')
							ELSE NULL
							END
						) = '2'
					)
				THEN ('Gebrauchtwagen')
			WHEN (
					(
						CASE 
							WHEN (T5."invoice_type" IN (2, 4, 6, 3))
								THEN ('3')
							WHEN (T5."invoice_type" IN (5))
								THEN ('6')
							WHEN (T5."invoice_type" IN (7))
								THEN ('1')
							WHEN (T5."invoice_type" IN (8))
								THEN ('2')
							WHEN (T4."document_type_in_journal" = 'R')
								THEN ('7')
							ELSE NULL
							END
						) = '3'
					)
				THEN ('Service')
			WHEN (
					(
						CASE 
							WHEN (T5."invoice_type" IN (2, 4, 6, 3))
								THEN ('3')
							WHEN (T5."invoice_type" IN (5))
								THEN ('6')
							WHEN (T5."invoice_type" IN (7))
								THEN ('1')
							WHEN (T5."invoice_type" IN (8))
								THEN ('2')
							WHEN (T4."document_type_in_journal" = 'R')
								THEN ('7')
							ELSE NULL
							END
						) = '6'
					)
				THEN ('Teile')
			ELSE ('nicht zuzuordnen')
			END AS "Forderungsart",
		CASE 
			WHEN (
					(((len(T1."invoice_number" + 'Z') - 1)) = 7)
					AND ((left(T1."invoice_number", 1)) IN ('2', '3', '4'))
					)
				THEN ('Service')
			WHEN (
					(((len(T1."invoice_number" + 'Z') - 1)) = 7)
					AND ((left(T1."invoice_number", 1)) IN ('5'))
					)
				THEN ('Teile')
			WHEN (
					(((len(T1."invoice_number" + 'Z') - 1)) = 7)
					AND ((left(T1."invoice_number", 1)) IN ('6'))
					)
				THEN ('Garantie')
			WHEN (
					(((len(T1."invoice_number" + 'Z') - 1)) = 7)
					AND ((left(T1."invoice_number", 1)) IN ('7'))
					)
				THEN ('Neuwagen')
			WHEN (
					(((len(T1."invoice_number" + 'Z') - 1)) = 7)
					AND ((left(T1."invoice_number", 1)) IN ('8'))
					)
				THEN ('Gebrauchtwagen')
			WHEN (T1."document_type" = 'R')
				THEN ('Rent')
			ELSE ('nicht zuzuordnen')
			END AS "Forderungsart_aus_erste_Stelle_Inv_Number",
		(len(T1."invoice_number" + 'Z') - 1) AS "Anzahl_Stellen_Invoice_Number",
		T2."name" AS "Name_Employees_journal_accountings",
		T2."employee_number" AS "Employee Number_Employees_journal_accountings",
		CASE 
			WHEN (T1."posting_text" IS NOT NULL)
				THEN ((left((((T1."document_number"))), 7)) + ' - ' + T1."posting_text" + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))
			ELSE ((convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))
			END AS "Beleg",
		((left((((T3."customer_number"))), 7)) + ' - ' + T3."first_name" + ' ' + T3."family_name") AS "Kunde",
		CASE 
			WHEN (((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date"))))) BETWEEN 0 AND 14)
				THEN ('< 2 Wochen')
			WHEN (((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date"))))) BETWEEN 15 AND 28)
				THEN ('2 - 4 Wochen')
			WHEN (((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date"))))) BETWEEN 29 AND 42)
				THEN ('4 - 6 Wochen')
			WHEN (((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date"))))) BETWEEN 43 AND 84)
				THEN ('6 - 12 Wochen')
			WHEN (((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date"))))) > 84)
				THEN ('> 12 Wochen')
			ELSE NULL
			END AS "Staffel",
		(- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."accounting_date")))) AS "Tage",
		CASE 
			WHEN (T5."invoice_type" IN (2, 4, 6, 3))
				THEN ('3')
			WHEN (T5."invoice_type" IN (5))
				THEN ('6')
			WHEN (T5."invoice_type" IN (7))
				THEN ('1')
			WHEN (T5."invoice_type" IN (8))
				THEN ('2')
			WHEN (T4."document_type_in_journal" = 'R')
				THEN ('7')
			ELSE NULL
			END AS "Kostenstelle",
		(
			CASE 
				WHEN (T1."debit_or_credit" = 'H')
					THEN (T1."posted_value" / 100)
				ELSE (T1."posted_value" / 100 * - 1)
				END
			) AS "Betrag",
		(left((((T2."employee_number"))), 4)) + ' - ' + T2."name" AS "Sel Name",
		CASE 
			WHEN (T5."subsidiary" IS NOT NULL)
				THEN ('0' + (left((((T5."subsidiary"))), 1)))
			ELSE ('nicht zuzuordnen')
			END AS "Standort_ori",
		T1."subsidiary_to_company_ref" AS "Rechtseinheit",
		T5."invoice_date" AS "Invoice Date_Invoices",
		T5."subsidiary" AS "Subsidiary_Invoices",
		T5."invoice_number" AS "Invoice Number_Invoices",
		T5."invoice_type" AS "Invoice Type_Invoices",
		T4."document_type_description" AS "Document Type Description",
		T4."document_type_in_journal" AS "Document Type In Journal",
		T3."family_name" AS "Family Name",
		T3."first_name" AS "First Name",
		T3."is_supplier" AS "Is Supplier",
		T1."nominal_account_number" AS "Nominal Account Number",
		CASE 
			WHEN (T1."debit_or_credit" = 'H')
				THEN (T1."posted_count" / 100 * - 1)
			ELSE (T1."posted_count" / 100)
			END AS "Menge",
		((rtrim((((T1."document_number"))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left((((T2."employee_number"))), 4)) + ' - ' + T2."name") + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date")))) AS "Text",
		T1."accounting_date" AS "Accounting Date",
		CASE 
			WHEN (
					(
						(
							(
								(
									(((rtrim((((T1."document_number"))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left((((T2."employee_number"))), 4)) + ' - ' + T2."name") + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))
										) LIKE '%RG-BONUS%'
									)
								OR (
									(((rtrim((((T1."document_number"))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left((((T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))
										) LIKE '%GEBURTSTAGS%'
									)
								)
							OR ((((rtrim((((T1."document_number"))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left((((T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))) LIKE '%STARTGUTHABEN%'
								)
							)
						OR ((((rtrim((((T1."document_number"))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left((((T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))) LIKE '%BONUS F%')
						)
					OR ((((rtrim((((T1."document_number"))))) + ' - ' + T1."invoice_number" + ' - ' + T1."posting_text" + '/' + T1."vehicle_reference" + ' - ' + ((left((((T2."employee_number"))))) + ' - ' + T2."name") + ' - ' + (convert(VARCHAR(50), year(T1."document_date")) + '-' + convert(VARCHAR(50), month(T1."document_date")) + '-' + convert(VARCHAR(50), day(T1."document_date"))))) LIKE '%RG-ABZUG%')
					)
				THEN ('Bonus/Startguthaben')
			ELSE ('ohne Bonus/Startguthaben')
			END AS "Free Form Document Text",
		T1."free_form_accounting_text" AS "Free Form Accounting Text",
		T1."previously_used_account_no" AS "Previously Used Account No",
		T1."skr51_cost_unit" AS "Skr51 Cost Unit",
		T1."skr51_sales_channel" AS "Skr51 Sales Channel",
		T1."skr51_cost_center" AS "Skr51 Cost Center",
		T1."skr51_make" AS "Skr51 Make",
		T1."skr51_branch" AS "Skr51 Branch",
		T1."day_of_actual_accounting" AS "Day Of Actual Accounting",
		T1."days_for_cash_discount" AS "Days For Cash Discount",
		T1."vat_key" AS "Vat Key",
		T1."account_statement_page" AS "Account Statement Page",
		T1."account_statement_number" AS "Account Statement Number",
		T1."vat_id_number" AS "Vat Id Number",
		T1."vehicle_reference" AS "Vehicle Reference",
		T1."posting_text" AS "Posting Text",
		T1."term_of_payment" AS "Term Of Payment",
		T1."cash_discount" AS "Cash Discount",
		T1."journal_line" AS "Journal Line",
		T1."journal_page" AS "Journal Page",
		T1."last_dunning_date" AS "Last Dunning Date",
		T1."dunning_level" AS "Dunning Level",
		T1."invoice_number" AS "Invoice Number",
		T1."invoice_date" AS "Invoice Date",
		T1."employee_number" AS "Employee Number",
		T1."serial_number_each_month" AS "Serial Number Each Month",
		T1."account_form_page_line" AS "Account Form Page Line",
		T1."account_form_page_number" AS "Account Form Page Number",
		T1."contra_account_text" AS "Contra Account Text",
		T1."nominal_contra_account" AS "Nominal Contra Account",
		T1."customer_contra_account" AS "Customer Contra Account",
		T1."branch_number" AS "Branch Number",
		T1."posted_count" AS "Posted Count",
		T1."debit_or_credit" AS "Debit Or Credit",
		T1."posted_value" AS "Posted Value",
		T1."document_date" AS "Document Date",
		T1."clearing_number" AS "Clearing Number",
		T1."is_balanced" AS "Is Balanced",
		T1."customer_number" AS "Customer Number",
		T1."position_in_document" AS "Position In Document",
		T1."document_number" AS "Document Number",
		T1."document_type" AS "Document Type"
	FROM (
		(
			(
				(
					"journal_accountings" T1 LEFT JOIN "LOCOSOFT"."dbo"."employees" T2 ON T1."employee_number" = T2."employee_number"
					) LEFT JOIN "LOCOSOFT"."dbo"."customers_suppliers" T3 ON T3."customer_number" = T1."customer_number"
				) LEFT JOIN "document_types" T4 ON T4."document_type_in_journal" = T1."document_type"
			) LEFT JOIN "invoices" T5 ON T5."invtype_invnr" = T1."invoice_number"
		)
	WHERE (
			(
				(
					(T1."nominal_account_number" IN (1600, 1610))
					AND (T1."customer_number" IS NOT NULL)
					)
				AND (T3."customer_number" IS NOT NULL)
				)
			AND (T1."document_type" IN ('W', 'E', 'L'))
			)
	) D1
	-- order by "Customer Number" asc,"Invoice Number" asc,"Document Type In Journal" asc,"Nominal Account Number" asc