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

SELECT (
		CASE 
			WHEN (
					(T1."Make" IS NULL)
					OR ((rtrim(T1."Make")) = '')
					)
				THEN ('00')
			ELSE ((rtrim(T1."Make")))
			END
		) + '-' + (
		CASE 
			WHEN (
					(((rtrim(T2."ACCT_NR"))) LIKE '2%')
					AND (T3."SITE" IS NULL)
					)
				THEN ('01')
			ELSE ((right('00' + (left(T3."SITE", 2)), 2)))
			END
		) + '-' + ((rtrim(T2."ACCT_NR"))) + '-' + (
		CASE 
			WHEN (
					(T3."DEPARTMENT" IS NULL)
					OR ((rtrim(T3."DEPARTMENT")) = '')
					)
				THEN ('00')
			ELSE ((rtrim(T3."DEPARTMENT")))
			END
		) + '-' + 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 + '-' + (
		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 "Konto_Nr_H�ndler",
	CASE 
		WHEN (T6."FUNCTION_CODE" = '4740')
			THEN ((year(T3."BOOKKEEP_DATE")) * 100)
		ELSE (T3."BOOKKEEP_PERIOD")
		END AS "Bookkeep Period",
	SUM(T3."DEBIT_AMOUNT") AS "Debit Amount",
	SUM(T3."CREDIT_AMOUNT") AS "Credit Amount",
	SUM(T3."DEBIT_QUANTITY") AS "Debit Quantity",
	SUM(T3."CREDIT_QUANTITY") AS "Credit Quantity"
FROM "OPTIMA"."import"."ACCOUNT_INFO" T2,
	(
		(
			"OPTIMA"."import"."ACCT_DOC_KEY" T3 LEFT JOIN "OPTIMA"."data"."GC_Marken" T1 ON T1."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 (T2."ACCT_NR" = T3."ACCT_NO")
	AND (T3."UNIQUE_IDENT" = T6."REFERENCE_IDENT")
	AND (T3."BOOKKEEP_DATE" >= convert(DATETIME, '2021-01-01 00:00:00.000'))
GROUP BY (
		CASE 
			WHEN (
					(T1."Make" IS NULL)
					OR ((rtrim(T1."Make")) = '')
					)
				THEN ('00')
			ELSE ((rtrim(T1."Make")))
			END
		) + '-' + (
		CASE 
			WHEN (
					(((rtrim(T2."ACCT_NR"))) LIKE '2%')
					AND (T3."SITE" IS NULL)
					)
				THEN ('01')
			ELSE ((right('00' + (left(T3."SITE", 2)), 2)))
			END
		) + '-' + ((rtrim(T2."ACCT_NR"))) + '-' + (
		CASE 
			WHEN (
					(T3."DEPARTMENT" IS NULL)
					OR ((rtrim(T3."DEPARTMENT")) = '')
					)
				THEN ('00')
			ELSE ((rtrim(T3."DEPARTMENT")))
			END
		) + '-' + 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 + '-' + (
		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)
					)
				))
		),
	CASE 
		WHEN (T6."FUNCTION_CODE" = '4740')
			THEN ((year(T3."BOOKKEEP_DATE")) * 100)
		ELSE (T3."BOOKKEEP_PERIOD")
		END
	-- order by "Konto_Nr_H�ndler" asc,"Bookkeep Period" asc