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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\op\op_fzg.imr
TITLE,op_fzg.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."DEBIT_ACCOUNT" as c63,
	   T3."STATUS" as c64,
	   T3."INVOICE_NUMBER" as c65,
	   T3."DEPARTMENT" as c66,
	   T3."DELIVERY_ACCOUNT" as c67,
	   T3."SALESMAN" as c68,
	   T3."INVOICE_DATE" as c69,
	   T3."PMT_TERM" as c70,
	   T3."VEHICLE_SOLD" as c71,
	   T3."VEHICLE_SOLD_SUM" 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,
	   (substring(T3."DEPARTMENT" from 3 for 1)) 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(T1."VOUCHER_NO")) as c89,
	   CASE WHEN ((T2."NAME" || ' - ' || T1."CUSTOMER_NUMBER") = (T6."NAME" || ' - ' || T6."CUSTOMER_NUMBER")) THEN ((substring((substring(((ascii(T1."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))) || ' - MS: ' || T1."DUNTIMES" from 1 for 100))) ELSE ((substring((truncate(T6."NAME")) || ' - ' || (substring(((ascii(T1."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))) || ' - MS: ' || T1."DUNTIMES" from 1 for 100))) END 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 100))) 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"."VEH_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"."import"."ORDER_HEADER" T8 on (T1."DOCUMENT_NO" = T8."ORDER_NUMBER") and (T1."CLIENT_DB" = T8."CLIENT_DB")) left outer join "OPTIMA"."data"."GC_Department" T7 on ((substring(T8."DEPARTMENT" from 1 for 2)) = T7."Standort") and (T8."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

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
COLUMN,62,Debit Account
COLUMN,63,Status
COLUMN,64,Invoice Number
COLUMN,65,Department
COLUMN,66,Delivery Account
COLUMN,67,Salesman
COLUMN,68,Invoice Date
COLUMN,69,Pmt Term
COLUMN,70,Vehicle Sold
COLUMN,71,Vehicle Sold Sum
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