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",
	   (day(((getdate())) - T1."BOOKKEEP_DATE")) as "Tage",
	   CASE WHEN (((day(((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((day(((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((day(((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((day(((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((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 outer join "OPTIMA"."import"."CUSTOMER" T2 on (T1."CUSTOMER_NUMBER" = T2."CUSTOMER_NUMBER") and (T1."CLIENT_DB" = T2."CLIENT_DB")) left outer join "OPTIMA"."import"."ORDER_HEADER" T3 on (T1."DOCUMENT_NO" = T3."ORDER_NUMBER") and (T1."CLIENT_DB" = T3."CLIENT_DB")) left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on (T3."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") and (T3."CLIENT_DB" = T4."CLIENT_DB")) left outer join "OPTIMA"."import"."VPP43" T5 on (T3."SALESMAN" = T5."SELLER_CODE") and (T3."CLIENT_DB" = T5."CLIENT_DB")) left outer join "OPTIMA"."import"."VEH_ORDER_HEADER" T6 on (T1."DOCUMENT_NO" = T6."ORDER_NUMBER") and (T1."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T7 on (substring(T6."DEPARTMENT", 1, 2)) = 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