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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\op\op_as.imr
TITLE,op_as.imr
BEGIN SQL
select c1 as c1,
	   c2 as c2,
	   c3 as c3,
	   c4 as c4,
	   c5 as c5,
	   c6 as c6,
	   c7 as c7,
	   c8 as c8,
	   c9 as c9,
	   c10 as c10,
	   c11 as c11,
	   c12 as c12,
	   c13 as c13,
	   c14 as c14,
	   c15 as c15,
	   c16 as c16,
	   c17 as c17,
	   c18 as c18,
	   c19 as c19,
	   c20 as c20,
	   c21 as c21,
	   c22 as c22,
	   c23 as c23,
	   c24 as c24,
	   c25 as c25,
	   c26 as c26,
	   c27 as c27,
	   c28 as c28,
	   c29 as c29,
	   c30 as c30,
	   c31 as c31,
	   c32 as c32,
	   c33 as c33,
	   c34 as c34,
	   c35 as c35,
	   c36 as c36,
	   c37 as c37,
	   c38 as c38,
	   c39 as c39,
	   c40 as c40,
	   c41 as c41,
	   c42 as c42,
	   c43 as c43,
	   c44 as c44,
	   c45 as c45,
	   c46 as c46,
	   c47 as c47,
	   c48 as c48,
	   c49 as c49,
	   c50 as c50,
	   c51 as c51,
	   c52 as c52,
	   c53 as c53,
	   c54 as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59,
	   c60 as c60,
	   c61 as c61,
	   RSUM(c61) as c62,
	   RSUM(c61) as c63,
	   c64 as c64,
	   c65 as c65,
	   c66 as c66,
	   c67 as c67,
	   c68 as c68,
	   c69 as c69,
	   c70 as c70,
	   c71 as c71,
	   c72 as c72,
	   c73 as c73,
	   c74 as c74,
	   c75 as c75,
	   c76 as c76,
	   c77 as c77,
	   c78 as c78,
	   c79 as c79,
	   c80 as c80,
	   c81 as c81,
	   c82 as c82,
	   c83 as c83,
	   c84 as c84,
	   c85 as c85,
	   c86 as c86,
	   c87 as c87,
	   c88 as c88,
	   c89 as c89,
	   c90 as c90,
	   c91 as c91,
	   c92 as c92,
	   c93 as c93,
	   c94 as c94,
	   c95 as c95
from 
(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 50)) 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 c64,
	   T3."STATUS" as c65,
	   T3."DEBIT_ACCOUNT" as c66,
	   T3."INVOICE_NUMBER" as c67,
	   T3."DELIVERY_ACCOUNT" as c68,
	   T3."DEPARTMENT" as c69,
	   T3."INVOICE_DATE" as c70,
	   T3."ORDERS_GROSSVALUE" as c71,
	   T3."SALESMAN" as c72,
	   T4."DEPARTMENT_TYPE_ID" as c73,
	   T4."DESCRIPTION" as c74,
	   T5."SELLER_CODE" as c75,
	   T5."SEL_NAME" as c76,
	   T5."SEL_DEPARTMENT" as c77,
	   '1' as c78,
	   (substring(T3."DEPARTMENT" from 1 for 2)) as c79,
	   CASE WHEN (T1."STATUS" LIKE '3%') THEN ('3') ELSE ('4') END as c80,
	   T6."CUSTOMER_NUMBER" as c81,
	   T6."NAME" as c82,
	   (getdate()) as c83,
	   (extract(DAY FROM ((getdate())) - T1."BOOKKEEP_DATE")) as c84,
	   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 c85,
	   T2."NAME" || ' - ' || T1."CUSTOMER_NUMBER" as c86,
	   T6."NAME" || ' - ' || T6."CUSTOMER_NUMBER" as c87,
	   (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 c88,
	   (ascii(T3."INVOICE_NUMBER")) as c89,
	   (substring((substring(((ascii(T3."INVOICE_NUMBER"))) from 1 for 7)) || ' - ' || ((substring(T1."COMMENT_CU" from 1 for 50))) || ' - ' || ((asciiz(extract(YEAR FROM T1."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."BOOKKEEP_DATE"),2))) || ' - MS: ' || T1."DUNTIMES" from 1 for 100)) as c90,
	   '1' as c91,
	   'Bergneustadt Ley' as c92,
	   CASE WHEN ((T7."Standort_ID" IS NULL) and (T1."CLIENT_DB" = '1')) THEN ('01') WHEN ((T7."Standort_ID" IS NULL) and (T1."CLIENT_DB" = '2')) THEN ('02') ELSE (T7."Standort_ID") END as c93,
	   CASE WHEN ((T7."Standort_Name" IS NULL) and (T1."CLIENT_DB" IN ('1'))) THEN ('Bergneustadt') WHEN ((T7."Standort_Name" IS NULL) and (T1."CLIENT_DB" IN ('2'))) THEN ('Verwaltung') ELSE (T7."Standort_Name") END as c94,
	   CASE WHEN (((substring(T1."COMMENT_CU" from 1 for 50))) LIKE 'VS%') THEN ('Versicherung') ELSE ('Kundenforderungen') END as c95
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"."CUSTOMER" T6 on (T3."DELIVERY_ACCOUNT" = T6."CUSTOMER_NUMBER") and (T3."CLIENT_DB" = T6."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T7 on ((substring(T3."DEPARTMENT" from 1 for 2)) = T7."Standort") and (T3."CLIENT_DB" = T7."Hauptbetrieb"))
where (((T1."SUM_C_U" + T1."PAID_C_U") <> .00) and (T3."ORDER_NUMBER" IS NOT NULL))
order by c6 asc
) D1

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,Summe  (Saldo_Beleg) Nr.2
COLUMN,62,Summe  (Saldo_Beleg) Nr.1
COLUMN,63,Order Number
COLUMN,64,Status
COLUMN,65,Debit Account
COLUMN,66,Invoice Number
COLUMN,67,Delivery Account
COLUMN,68,Department
COLUMN,69,Invoice Date
COLUMN,70,Orders Grossvalue
COLUMN,71,Salesman
COLUMN,72,Department Type Id
COLUMN,73,Description
COLUMN,74,Seller Code
COLUMN,75,Sel Name
COLUMN,76,Sel Department
COLUMN,77,Hauptbetrieb
COLUMN,78,Standort
COLUMN,79,Kostenstelle
COLUMN,80,Customer Number_Deliv
COLUMN,81,Name_Deliv
COLUMN,82,Heute
COLUMN,83,Tage
COLUMN,84,Staffel
COLUMN,85,Kunde
COLUMN,86,Lieferkunde
COLUMN,87,Datum_Zeichen
COLUMN,88,Voucher_Zeichen
COLUMN,89,Beleg
COLUMN,90,Hauptbetrieb_ID
COLUMN,91,Hauptbetrieb_Name
COLUMN,92,Standort_ID
COLUMN,93,Standort_Name
COLUMN,94,Forderungsart