C11/System/OPTIMA/IQD/Belege/Kontenrahmen_kombiniert.sql

SELECT DISTINCT (rtrim(T1."ACCT_NR")) AS "Konto_Nr",
	CASE 
		WHEN (T2."Make" IS NULL)
			THEN ('00')
		ELSE ((ucase(T2."Make")))
		END AS "Marke",
	CASE 
		WHEN (
				(((rtrim(T1."ACCT_NR"))) LIKE '2%')
				AND (T3."SITE" IS NULL)
				)
			THEN ('01')
		ELSE ((right('00' + (left(T3."SITE", 2)), 2)))
		END AS "Standort",
	CASE 
		WHEN (
				(T3."DEPARTMENT" IS NULL)
				OR ((rtrim(T3."DEPARTMENT")) = '')
				)
			THEN ('00')
		ELSE (T3."DEPARTMENT")
		END AS "Kostenstelle",
	CASE 
		WHEN (
				(T3."DESTINATION" = '00')
				AND (T3."PRICE_CODE" = '#')
				)
			THEN ('99')
		WHEN (
				(T3."DESTINATION" = '00')
				AND (T3."PRICE_CODE" <> '')
				)
			THEN (T3."PRICE_CODE")
		WHEN (
				(
					(T3."DESTINATION" = '00')
					AND (T3."PRICE_CODE" = '')
					)
				AND (T4."CUSTOMER_GROUP" <> '')
				)
			THEN (T4."CUSTOMER_GROUP")
		WHEN ((rtrim(T3."DESTINATION")) = '')
			THEN ('00')
		WHEN (T3."DESTINATION" = 'FZ')
			THEN ('11')
		ELSE (T3."DESTINATION")
		END AS "Absatzkanal",
	(
		upper((
				rtrim(CASE 
						WHEN (
								(T5."MODEL_LINE" IS NOT NULL)
								AND (T5."MODEL_LINE" <> '')
								)
							THEN (T5."MODEL_LINE")
						WHEN (
								(T3."MODEL_LINE" IS NOT NULL)
								AND (T3."MODEL_LINE" <> '')
								)
							THEN (T3."MODEL_LINE")
						WHEN (
								(T3."PRODUCT_GROUP" IS NOT NULL)
								AND (T3."PRODUCT_GROUP" <> '')
								)
							THEN (T3."PRODUCT_GROUP")
						WHEN (
								(T3."REPAIR_GROUP" IS NOT NULL)
								AND (T3."REPAIR_GROUP" <> '')
								)
							THEN (T3."REPAIR_GROUP")
						ELSE ('00')
						END)
				))
		) AS "Kostentr�ger",
	CASE 
		WHEN (
				(T5."MODEL_LINE" IS NOT NULL)
				AND (T5."MODEL_LINE" <> '')
				)
			THEN ('NW')
		WHEN (
				(T3."MODEL_LINE" IS NOT NULL)
				AND (T3."MODEL_LINE" <> '')
				)
			THEN ('NW')
		WHEN (
				(T3."PRODUCT_GROUP" IS NOT NULL)
				AND (T3."PRODUCT_GROUP" <> '')
				)
			THEN ('TZ')
		WHEN (
				(T3."REPAIR_GROUP" IS NOT NULL)
				AND (T3."REPAIR_GROUP" <> '')
				)
			THEN ('SC')
		ELSE ('')
		END AS "Kostentr�ger_Quelle"
FROM "OPTIMA"."import"."ACCOUNT_INFO" T1,
	(
		(
			"OPTIMA"."import"."ACCT_DOC_KEY" T3 LEFT JOIN "OPTIMA"."data"."GC_Marken" T2 ON T2."Strategic_Area" = T3."STRATEGIC_AREA"
			) LEFT JOIN "OPTIMA"."import"."vPP5Q" T5 ON (T3."MODEL_LINE" = T5."MODEL_LINE")
			AND (T3."MAKE" = T5."MAKE_CD")
		),
	(
		(
			"OPTIMA"."import"."ACCT_DOC_DATA" T6 LEFT JOIN "OPTIMA"."import"."ACCT_DOC_SALESCLAS" T7 ON T6."REFERENCE_IDENT" = T7."REFERENCE_IDENT"
			) LEFT JOIN "OPTIMA"."import"."CUSTOMER" T4 ON T4."CUSTOMER_NUMBER" = T7."CUSTOMER_NUMBER"
		)
WHERE (T1."ACCT_NR" = T3."ACCT_NO")
	AND (T3."UNIQUE_IDENT" = T6."REFERENCE_IDENT")
	AND (T3."BOOKKEEP_DATE" >= convert(DATETIME, '2020-01-01 00:00:00.000'))
	-- order by "Konto_Nr" asc,"Standort" asc,"Kostenstelle" asc,"Marke" asc