C11/System/OPTIMA/IQD/OP/OP_ohne_Auftrag_O21.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\op\op_ohne_auftrag_O21.imr
TITLE,op_ohne_auftrag_O21.imr
BEGIN SQL
select T1."CUSTOMER_NUMBER" as c1,
	   T1."VOUCHER_NO" as c2,
	   T1."LINE_NO_CU" as c3,
	   T1."TRANSACTION_STATUS" as c4,
	   T1."DUE_DATE" as c5,
	   T1."BOOKKEEP_DATE" as c6,
	   CASE WHEN (T2."E_MAIL_ADDRESS" IS NOT NULL) THEN ((substring(T2."ADDR_2" || '/' || (substring(T2."ZIPCODE" from 1 for 5)) || ' ' || T2."MAIL_ADDR" || '/' || T2."PHONE_1" || '/' || T2."E_MAIL_ADDRESS" from 1 for 100))) ELSE ((substring(T2."ADDR_2" || '/' || (substring(T2."ZIPCODE" from 1 for 5)) || ' ' || T2."MAIL_ADDR" || '/' || T2."PHONE_1" from 1 for 100))) END as c7,
	   T1."TRANSACT_DATE" as c8,
	   T1."HANDLER" as c9,
	   T1."CREATION_DATE" as c10,
	   T1."CREATION_TIME_8" as c11,
	   T1."STATUS" as c12,
	   T1."MODUL" as c13,
	   T1."BATCH_NUMBER" as c14,
	   T1."JOURNAL_NO" as c15,
	   T1."DOCUMENT_NO" as c16,
	   T1."DOCUMENT_DATE" as c17,
	   T1."DISCOUNT_DATE" as c18,
	   T1."DUNNING_DATE" as c19,
	   T1."START_INT_DATE" as c20,
	   T1."LAST_INT_DATE" as c21,
	   T1."PMT_TERM" as c22,
	   T1."DUNTIMES" as c23,
	   T1."TAX_CODE" as c24,
	   T1."REMINDER_CODE" as c25,
	   T1."SUM_C_U" as c26,
	   T1."TAX_C_U" as c27,
	   T1."CASH_DISCOUNT_C_U" as c28,
	   T1."INTEREST_PERC" as c29,
	   T1."PAID_C_U" as c30,
	   (substring(T1."COMMENT_CU" from 1 for 100)) as c31,
	   T1."SETOFF_ACCT" as c32,
	   T1."COLLECT_ACCT" as c33,
	   T1."INTEREST_CALC" as c34,
	   T1."INT_VOUCHER_NO" as c35,
	   T2."CUSTOMER_NUMBER" as c36,
	   T2."STATE_CODE_CUST" as c37,
	   T2."TRANSACT_DATE" as c38,
	   T2."HANDLER" as c39,
	   T2."CUST_ALT_KEY" as c40,
	   T2."NAME" as c41,
	   T2."STREET_ADDR" as c42,
	   T2."ZIPCODE" as c43,
	   T2."DEBIT_CUSTOMER" as c44,
	   T2."TITLE_CODE" as c45,
	   T2."TITLE" as c46,
	   T2."PMT_TERM" as c47,
	   T2."WORKSHOP_PRICECODE" as c48,
	   T2."VAT_REGNO" as c49,
	   T2."SALESMAN" as c50,
	   T2."GROSS_DISCOUNT" as c51,
	   T2."DELIVERY_STOP_CODE" as c52,
	   T2."CREDIT_LIMIT" as c53,
	   T2."REMINDER_CODE" as c54,
	   T2."CUSTOMER_GROUP" as c55,
	   '' as c56,
	   '' as c57,
	   T2."SALDO_C_U_CUST" as c58,
	   T2."REMINDER_GROUP" as c59,
	   T2."REMINDER_DATE" as c60,
	   T1."SUM_C_U" + T1."PAID_C_U" as c61,
	   T3."ORDER_NUMBER" as c62,
	   T3."STATUS" as c63,
	   T3."DEBIT_ACCOUNT" as c64,
	   T3."INVOICE_NUMBER" as c65,
	   T3."DELIVERY_ACCOUNT" as c66,
	   T3."DEPARTMENT" as c67,
	   T3."INVOICE_DATE" as c68,
	   T3."ORDERS_GROSSVALUE" as c69,
	   T3."SALESMAN" as c70,
	   T4."DEPARTMENT_TYPE_ID" as c71,
	   T4."DESCRIPTION" as c72,
	   T5."SELLER_CODE" as c73,
	   T5."SEL_NAME" as c74,
	   T5."SEL_DEPARTMENT" as c75,
	   '1' as c76,
	   'nicht zuzuordnen' as c77,
	   'nicht zuzuordnen' as c78,
	   T6."ORDER_NUMBER" as c79,
	   (getdate()) as c80,
	   (extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE")) as c81,
	   CASE WHEN (((extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE"))) > 84) THEN ('> 12 Wochen') ELSE null END as c82,
	   T2."NAME" || ' - ' || T1."CUSTOMER_NUMBER" as c83,
	   '' as c84,
	   (char_length((ascii(T1."INT_VOUCHER_NO")))) as c85,
	   (ascii(T1."INT_VOUCHER_NO")) as c86,
	   (substring(((ascii(T1."INT_VOUCHER_NO"))) from 7 for 1)) as c87,
	   'nicht zuzuordnen' as c88,
	   (asciiz(extract(YEAR FROM T1."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."BOOKKEEP_DATE"),2)) as c89,
	   (ascii(T1."INT_VOUCHER_NO")) as c90,
	   (substring((substring(((ascii(T1."INT_VOUCHER_NO"))) from 1 for 7)) || ' - ' || ((substring(T1."COMMENT_CU" from 1 for 100))) || ' - ' || ((asciiz(extract(YEAR FROM T1."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."BOOKKEEP_DATE"),2))) from 1 for 100)) as c91,
	   '1' as c92,
	   'Bergneustadt Ley' as c93,
	   'nicht zuzuordnen' as c94,
	   'nicht zuzuordnen' as c95,
	   CASE WHEN (((substring(T1."COMMENT_CU" from 1 for 100))) LIKE 'VS%') THEN ('Versicherung') ELSE ('Kundenforderungen') END as c96
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"))
where ((((T1."SUM_C_U" + T1."PAID_C_U") <> .00) and (T3."ORDER_NUMBER" IS NULL)) and (T6."ORDER_NUMBER" IS NULL))
order by c6 asc

END SQL
COLUMN,0,Customer Number
COLUMN,1,Voucher No
COLUMN,2,Line No Cu
COLUMN,3,Transaction Status
COLUMN,4,Due Date
COLUMN,5,Bookkeep Date
COLUMN,6,State Code
COLUMN,7,Transact Date
COLUMN,8,Handler
COLUMN,9,Creation Date
COLUMN,10,Creation Time 8
COLUMN,11,Status
COLUMN,12,Modul
COLUMN,13,Batch Number
COLUMN,14,Journal No
COLUMN,15,Document No
COLUMN,16,Document Date
COLUMN,17,Discount Date
COLUMN,18,Dunning Date
COLUMN,19,Start Int Date
COLUMN,20,Last Int Date
COLUMN,21,Pmt Term
COLUMN,22,Duntimes
COLUMN,23,Tax Code
COLUMN,24,Reminder Code
COLUMN,25,Sum C U
COLUMN,26,Tax C U
COLUMN,27,Cash Discount C U
COLUMN,28,Interest Perc
COLUMN,29,Paid C U
COLUMN,30,Comment Cu
COLUMN,31,Setoff Acct
COLUMN,32,Collect Acct
COLUMN,33,Interest Calc
COLUMN,34,Int Voucher No
COLUMN,35,Customer Number
COLUMN,36,State Code Cust
COLUMN,37,Transact Date
COLUMN,38,Handler
COLUMN,39,Cust Alt Key
COLUMN,40,Name
COLUMN,41,Street Addr
COLUMN,42,Zipcode
COLUMN,43,Debit Customer
COLUMN,44,Title Code
COLUMN,45,Title
COLUMN,46,Pmt Term
COLUMN,47,Workshop Pricecode
COLUMN,48,Vat Regno
COLUMN,49,Salesman
COLUMN,50,Gross Discount
COLUMN,51,Delivery Stop Code
COLUMN,52,Credit Limit
COLUMN,53,Reminder Code
COLUMN,54,Customer Group
COLUMN,55,Comment 1
COLUMN,56,Comment 2
COLUMN,57,Saldo C U Cust
COLUMN,58,Reminder Group
COLUMN,59,Reminder Date
COLUMN,60,Saldo_Beleg
COLUMN,61,Order Number_AS
COLUMN,62,Status
COLUMN,63,Debit Account
COLUMN,64,Invoice Number
COLUMN,65,Delivery Account
COLUMN,66,Department
COLUMN,67,Invoice Date
COLUMN,68,Orders Grossvalue
COLUMN,69,Salesman
COLUMN,70,Department Type Id
COLUMN,71,Description
COLUMN,72,Seller Code
COLUMN,73,Sel Name
COLUMN,74,Sel Department
COLUMN,75,Hauptbetrieb
COLUMN,76,Standort_ber
COLUMN,77,Kostenstelle
COLUMN,78,Order Number_V
COLUMN,79,Heute
COLUMN,80,Tage
COLUMN,81,Staffel
COLUMN,82,Kunde
COLUMN,83,Lieferkunde
COLUMN,84,Anzahl Stellem Rg
COLUMN,85,Voucher_Zeichen
COLUMN,86,7_Stelle
COLUMN,87,Standort
COLUMN,88,Datum_Zeichen
COLUMN,89,Voucher_Zeichen
COLUMN,90,Beleg
COLUMN,91,Hauptbetrieb_ID
COLUMN,92,Hauptbetrieb_Name
COLUMN,93,Standort_ID
COLUMN,94,Standort_Name
COLUMN,95,Forderungsart