C11/System/OPTIMA/IQD/OP/op_fzg.sql

SELECT T1."CUSTOMER_NUMBER" AS "Customer Number_2",
	T1."VOUCHER_NO" AS "Voucher No",
	T1."LINE_NO_CU" AS "Line No Cu",
	T1."TRANSACTION_STATUS" AS "Transaction Status",
	T1."DUE_DATE" AS "Due Date",
	T1."BOOKKEEP_DATE" AS "Bookkeep Date",
	CASE 
		WHEN (T2."E_MAIL_ADDRESS" IS NOT NULL)
			THEN ((left(T2."ADDR_2" + '/' + (left(T2."ZIPCODE", 5)) + ' ' + T2."MAIL_ADDR" + '/' + T2."PHONE_1" + '/' + T2."E_MAIL_ADDRESS", 100)))
		ELSE ((left(T2."ADDR_2" + '/' + (left(T2."ZIPCODE", 5)) + ' ' + T2."MAIL_ADDR" + '/' + T2."PHONE_1", 100)))
		END AS "State Code",
	T1."TRANSACT_DATE" AS "Transact Date_2",
	T1."HANDLER" AS "Handler_2",
	T1."CREATION_DATE" AS "Creation Date",
	T1."CREATION_TIME_8" AS "Creation Time 8",
	T1."STATUS" AS "Status_2",
	T1."MODUL" AS "Modul",
	T1."BATCH_NUMBER" AS "Batch Number",
	T1."JOURNAL_NO" AS "Journal No",
	T1."DOCUMENT_NO" AS "Document No",
	T1."DOCUMENT_DATE" AS "Document Date",
	T1."DISCOUNT_DATE" AS "Discount Date",
	((T1."DUNNING_DATE")) AS "Dunning Date",
	T1."START_INT_DATE" AS "Start Int Date",
	T1."LAST_INT_DATE" AS "Last Int Date",
	T1."PMT_TERM" AS "Pmt Term_3",
	T1."DUNTIMES" AS "Duntimes",
	T1."TAX_CODE" AS "Tax Code",
	T1."REMINDER_CODE" AS "Reminder Code_2",
	T1."SUM_C_U" AS "Sum C U",
	T1."TAX_C_U" AS "Tax C U",
	T1."CASH_DISCOUNT_C_U" AS "Cash Discount C U",
	T1."INTEREST_PERC" AS "Interest Perc",
	T1."PAID_C_U" AS "Paid C U",
	CASE 
		WHEN (T1."VOUCHER_NO" = 3271990)
			THEN (' - ')
		ELSE ((left(T1."COMMENT_CU", 100)))
		END AS "Comment Cu",
	T1."SETOFF_ACCT" AS "Setoff Acct",
	T1."COLLECT_ACCT" AS "Collect Acct",
	T1."INTEREST_CALC" AS "Interest Calc",
	T1."INT_VOUCHER_NO" AS "Int Voucher No",
	T2."CUSTOMER_NUMBER" AS "Customer Number",
	T2."STATE_CODE_CUST" AS "State Code Cust",
	T2."TRANSACT_DATE" AS "Transact Date",
	T2."HANDLER" AS "Handler",
	T2."CUST_ALT_KEY" AS "Cust Alt Key",
	T2."NAME" AS "Name",
	T2."STREET_ADDR" AS "Street Addr",
	T2."ZIPCODE" AS "Zipcode",
	T2."DEBIT_CUSTOMER" AS "Debit Customer",
	T2."TITLE_CODE" AS "Title Code",
	T2."TITLE" AS "Title",
	T2."PMT_TERM" AS "Pmt Term_2",
	T2."WORKSHOP_PRICECODE" AS "Workshop Pricecode",
	T2."VAT_REGNO" AS "Vat Regno",
	T2."SALESMAN" AS "Salesman_2",
	T2."GROSS_DISCOUNT" AS "Gross Discount",
	T2."DELIVERY_STOP_CODE" AS "Delivery Stop Code",
	T2."CREDIT_LIMIT" AS "Credit Limit",
	T2."REMINDER_CODE" AS "Reminder Code",
	T2."CUSTOMER_GROUP" AS "Customer Group",
	'' AS "Comment 1",
	'' AS "Comment 2",
	T2."SALDO_C_U_CUST" AS "Saldo C U Cust",
	T2."REMINDER_GROUP" AS "Reminder Group",
	T2."REMINDER_DATE" AS "Reminder Date",
	T1."SUM_C_U" + T1."PAID_C_U" AS "Saldo_Beleg",
	T3."ORDER_NUMBER" AS "Order Number",
	T3."DEBIT_ACCOUNT" AS "Debit Account",
	T3."STATUS" AS "Status",
	T3."INVOICE_NUMBER" AS "Invoice Number",
	T3."DEPARTMENT" AS "Department",
	T3."DELIVERY_ACCOUNT" AS "Delivery Account",
	T3."SALESMAN" AS "Salesman",
	T3."INVOICE_DATE" AS "Invoice Date",
	T3."PMT_TERM" AS "Pmt Term",
	T3."VEHICLE_SOLD" AS "Vehicle Sold",
	T3."VEHICLE_SOLD_SUM" AS "Vehicle Sold Sum",
	T4."DEPARTMENT_TYPE_ID" AS "Department Type Id",
	T4."DESCRIPTION" AS "Description",
	T5."SELLER_CODE" AS "Seller Code",
	T5."SEL_NAME" AS "Sel Name",
	T5."SEL_DEPARTMENT" AS "Sel Department",
	'1' AS "Hauptbetrieb",
	(left(T3."DEPARTMENT", 2)) AS "Standort",
	(substring(T3."DEPARTMENT", 3, 1)) AS "Kostenstelle",
	T6."CUSTOMER_NUMBER" AS "Customer Number_Deliv",
	T6."NAME" AS "Name_Deliv",
	(getdate()) AS "Heute",
	(- 1 * datediff(day, ((getdate())), T1."BOOKKEEP_DATE")) AS "Tage",
	CASE 
		WHEN (((- 1 * datediff(day, ((getdate())), T1."BOOKKEEP_DATE"))) BETWEEN 0 AND 14)
			THEN ('< 2 Wochen')
		WHEN (((- 1 * datediff(day, ((getdate())), T1."BOOKKEEP_DATE"))) BETWEEN 15 AND 28)
			THEN ('2 - 4 Wochen')
		WHEN (((- 1 * datediff(day, ((getdate())), T1."BOOKKEEP_DATE"))) BETWEEN 29 AND 42)
			THEN ('4 - 6 Wochen')
		WHEN (((- 1 * datediff(day, ((getdate())), T1."BOOKKEEP_DATE"))) BETWEEN 43 AND 84)
			THEN ('6 - 12 Wochen')
		WHEN (((- 1 * datediff(day, ((getdate())), T1."BOOKKEEP_DATE"))) > 84)
			THEN ('> 12 Wochen')
		ELSE NULL
		END AS "Staffel",
	T2."NAME" + ' - ' + T1."CUSTOMER_NUMBER" AS "Kunde",
	T6."NAME" + ' - ' + T6."CUSTOMER_NUMBER" AS "Lieferkunde",
	(convert(VARCHAR(50), year(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), month(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), day(T1."BOOKKEEP_DATE"))) AS "Datum_Zeichen",
	(((T1."VOUCHER_NO"))) AS "Voucher_Zeichen",
	CASE 
		WHEN ((T2."NAME" + ' - ' + T1."CUSTOMER_NUMBER") = (T6."NAME" + ' - ' + T6."CUSTOMER_NUMBER"))
			THEN (
					(
						substring((left(((((T1."VOUCHER_NO")))), 7)) + ' - ' + (
								CASE 
									WHEN (T1."VOUCHER_NO" = 3271990)
										THEN (' - ')
									ELSE ((left(T1."COMMENT_CU", 100)))
									END
								) + ' - ' + ((convert(VARCHAR(50), year(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), month(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), day(T1."BOOKKEEP_DATE")))) + ' - MS: ' + T1."DUNTIMES", 1, 100)
						)
					)
		ELSE (
				(
					substring((rtrim(T6."NAME")) + ' - ' + (left(((((T1."VOUCHER_NO")))), 7)) + ' - ' + (
							CASE 
								WHEN (T1."VOUCHER_NO" = 3271990)
									THEN (' - ')
								ELSE ((left(T1."COMMENT_CU", 100)))
								END
							) + ' - ' + ((convert(VARCHAR(50), year(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), month(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), day(T1."BOOKKEEP_DATE")))) + ' - MS: ' + T1."DUNTIMES", 1, 100)
					)
				)
		END AS "Beleg",
	'1' AS "Hauptbetrieb_ID",
	T7."Hauptbetrieb_Name" AS "Hauptbetrieb_Name",
	T7."Standort_ID" AS "Standort_ID_",
	T7."Standort_Name" AS "Standort_Name_",
	CASE 
		WHEN (T4."DEPARTMENT_TYPE_ID" IN ('015 ', '0153', '0154'))
			THEN ('05')
		ELSE (T7."Standort_ID")
		END AS "Standort_ID",
	CASE 
		WHEN (T4."DEPARTMENT_TYPE_ID" IN ('015 ', '0153', '0154'))
			THEN ('Autofit')
		ELSE (T7."Standort_Name")
		END AS "Standort_Name"
FROM (
	(
		(
			(
				(
					(
						"OPTIMA"."import"."CUSTOMER_TRANSACT" T1 LEFT JOIN "OPTIMA"."import"."CUSTOMER" T2 ON (T1."CUSTOMER_NUMBER" = T2."CUSTOMER_NUMBER")
							AND (T1."CLIENT_DB" = T2."CLIENT_DB")
						) LEFT JOIN "OPTIMA"."import"."VEH_ORDER_HEADER" T3 ON (T1."DOCUMENT_NO" = T3."ORDER_NUMBER")
						AND (T1."CLIENT_DB" = T3."CLIENT_DB")
					) LEFT JOIN "OPTIMA"."import"."DEPARTMENT_TYPE" T4 ON (T3."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID")
					AND (T3."CLIENT_DB" = T4."CLIENT_DB")
				) LEFT JOIN "OPTIMA"."import"."VPP43" T5 ON (T3."SALESMAN" = T5."SELLER_CODE")
				AND (T3."CLIENT_DB" = T5."CLIENT_DB")
			) LEFT JOIN "OPTIMA"."import"."CUSTOMER" T6 ON (T3."DELIVERY_ACCOUNT" = T6."CUSTOMER_NUMBER")
			AND (T3."CLIENT_DB" = T6."CLIENT_DB")
		) LEFT JOIN "OPTIMA"."data"."GC_Department" T7 ON (substring(T3."DEPARTMENT", 1, 1)) = T7."Standort"
	)
WHERE (
		((T1."SUM_C_U" + T1."PAID_C_U") <> .00)
		AND (T3."ORDER_NUMBER" IS NOT NULL)
		)
	-- order by "Bookkeep Date" asc