C11/System/OPTIMA/IQD/OP/op_ohne_auftrag_O21.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_2",
	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",
	(left(T1."COMMENT_CU", 100)) 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",
	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_AS",
	T3."STATUS" AS "Status",
	T3."DEBIT_ACCOUNT" AS "Debit Account",
	T3."INVOICE_NUMBER" AS "Invoice Number",
	T3."DELIVERY_ACCOUNT" AS "Delivery Account",
	T3."DEPARTMENT" AS "Department",
	T3."INVOICE_DATE" AS "Invoice Date",
	T3."ORDERS_GROSSVALUE" AS "Orders Grossvalue",
	T3."SALESMAN" AS "Salesman",
	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",
	'nicht zuzuordnen' AS "Standort_ber",
	'nicht zuzuordnen' AS "Kostenstelle",
	T6."ORDER_NUMBER" AS "Order Number_V",
	(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",
	'' AS "Lieferkunde",
	(len((((T1."INT_VOUCHER_NO"))) + 'Z') - 1) AS "Anzahl Stellem Rg",
	(((T1."INT_VOUCHER_NO"))) AS "Voucher_Zeichen_2",
	(substring(((((T1."INT_VOUCHER_NO")))), 7, 1)) AS "7_Stelle",
	'nicht zuzuordnen' AS "Standort",
	(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."INT_VOUCHER_NO"))) AS "Voucher_Zeichen",
	(substring((left(((((T1."INT_VOUCHER_NO")))), 7)) + ' - ' + ((left(T1."COMMENT_CU", 100))) + ' - ' + ((convert(VARCHAR(50), year(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), month(T1."BOOKKEEP_DATE")) + '-' + convert(VARCHAR(50), day(T1."BOOKKEEP_DATE")))), 1, 100)) AS "Beleg",
	'1' AS "Hauptbetrieb_ID",
	T7."Hauptbetrieb_Name" AS "Hauptbetrieb_Name",
	'nicht zuzuordnen' AS "Standort_ID",
	'nicht zuzuordnen' 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"."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"."VEH_ORDER_HEADER" T6 ON (T1."DOCUMENT_NO" = T6."ORDER_NUMBER")
			AND (T1."CLIENT_DB" = T6."CLIENT_DB")
		) LEFT JOIN "OPTIMA"."data"."GC_Department" T7 ON (substring(T6."DEPARTMENT", 1, 1)) = T7."Standort"
	)
WHERE (
		(
			((T1."SUM_C_U" + T1."PAID_C_U") <> .00)
			AND (T3."ORDER_NUMBER" IS NULL)
			)
		AND (T6."ORDER_NUMBER" IS NULL)
		)
	-- order by "Bookkeep Date" asc