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

SELECT "Customer Number_2" AS "Customer Number_2",
	"Voucher No" AS "Voucher No",
	"Line No Cu" AS "Line No Cu",
	"Transaction Status" AS "Transaction Status",
	"Due Date" AS "Due Date",
	"Bookkeep Date" AS "Bookkeep Date",
	"State Code" AS "State Code",
	"Transact Date_2" AS "Transact Date_2",
	"Handler_2" AS "Handler_2",
	"Creation Date" AS "Creation Date",
	"Creation Time 8" AS "Creation Time 8",
	"Status_2" AS "Status_2",
	"Modul" AS "Modul",
	"Batch Number" AS "Batch Number",
	"Journal No" AS "Journal No",
	"Document No" AS "Document No",
	"Document Date" AS "Document Date",
	"Discount Date" AS "Discount Date",
	"Dunning Date" AS "Dunning Date",
	"Start Int Date" AS "Start Int Date",
	"Last Int Date" AS "Last Int Date",
	"Pmt Term_2" AS "Pmt Term_2",
	"Duntimes" AS "Duntimes",
	"Tax Code" AS "Tax Code",
	"Reminder Code_2" AS "Reminder Code_2",
	"Sum C U" AS "Sum C U",
	"Tax C U" AS "Tax C U",
	"Cash Discount C U" AS "Cash Discount C U",
	"Interest Perc" AS "Interest Perc",
	"Paid C U" AS "Paid C U",
	"Comment Cu" AS "Comment Cu",
	"Setoff Acct" AS "Setoff Acct",
	"Collect Acct" AS "Collect Acct",
	"Interest Calc" AS "Interest Calc",
	"Int Voucher No" AS "Int Voucher No",
	"Customer Number" AS "Customer Number",
	"State Code Cust" AS "State Code Cust",
	"Transact Date" AS "Transact Date",
	"Handler" AS "Handler",
	"Cust Alt Key" AS "Cust Alt Key",
	"Name" AS "Name",
	"Street Addr" AS "Street Addr",
	"Zipcode" AS "Zipcode",
	"Debit Customer" AS "Debit Customer",
	"Title Code" AS "Title Code",
	"Title" AS "Title",
	"Pmt Term" AS "Pmt Term",
	"Workshop Pricecode" AS "Workshop Pricecode",
	"Vat Regno" AS "Vat Regno",
	"Salesman_2" AS "Salesman_2",
	"Gross Discount" AS "Gross Discount",
	"Delivery Stop Code" AS "Delivery Stop Code",
	"Credit Limit" AS "Credit Limit",
	"Reminder Code" AS "Reminder Code",
	"Customer Group" AS "Customer Group",
	"Comment 1" AS "Comment 1",
	"Comment 2" AS "Comment 2",
	"Saldo C U Cust" AS "Saldo C U Cust",
	"Reminder Group" AS "Reminder Group",
	"Reminder Date" AS "Reminder Date",
	"Saldo_Beleg" AS "Saldo_Beleg",
	RSUM("Saldo_Beleg") AS "Summe  (Saldo_Beleg) Nr.2",
	RSUM("Saldo_Beleg") AS "Summe  (Saldo_Beleg) Nr.1",
	"Order Number" AS "Order Number",
	"Status" AS "Status",
	"Debit Account" AS "Debit Account",
	"Invoice Number" AS "Invoice Number",
	"Delivery Account" AS "Delivery Account",
	"Department" AS "Department",
	"Invoice Date" AS "Invoice Date",
	"Orders Grossvalue" AS "Orders Grossvalue",
	"Salesman" AS "Salesman",
	"Department Type Id" AS "Department Type Id",
	"Description" AS "Description",
	"Seller Code" AS "Seller Code",
	"Sel Name" AS "Sel Name",
	"Sel Department" AS "Sel Department",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Standort" AS "Standort",
	"Kostenstelle" AS "Kostenstelle",
	"Customer Number_Deliv" AS "Customer Number_Deliv",
	"Name_Deliv" AS "Name_Deliv",
	"Heute" AS "Heute",
	"Tage" AS "Tage",
	"Staffel" AS "Staffel",
	"Kunde" AS "Kunde",
	"Lieferkunde" AS "Lieferkunde",
	"Datum_Zeichen" AS "Datum_Zeichen",
	"Voucher_Zeichen" AS "Voucher_Zeichen",
	"Beleg" AS "Beleg",
	"Hauptbetrieb_ID" AS "Hauptbetrieb_ID",
	"Hauptbetrieb_Name" AS "Hauptbetrieb_Name",
	"Standort_ID_" AS "Standort_ID_",
	"Standort_Name_" AS "Standort_Name_",
	"Standort_ID" AS "Standort_ID",
	"Standort_Name" AS "Standort_Name"
FROM (
	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", 50)) 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",
		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",
		(left(T3."DEPARTMENT", 2)) AS "Standort",
		CASE 
			WHEN (T1."STATUS" LIKE '3%')
				THEN ('3')
			ELSE ('4')
			END 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",
		(((T3."INVOICE_NUMBER"))) AS "Voucher_Zeichen",
		(substring((left(((((T3."INVOICE_NUMBER")))), 7)) + ' - ' + ((left(T1."COMMENT_CU", 50))) + ' - ' + ((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)) 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"."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, 2)) = T7."Standort")
			OR (((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
	) D1