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

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