GAPS/Portal/System/IQD/OP/OP_ohne Auftrag_O21_SKR51.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,D:\Gaps\Portal\System\IQD\OP\OP_ohne Auftrag_O21_SKR51.imr
TITLE,OP_ohne Auftrag_O21_SKR51.imr
BEGIN SQL
select T1."CUSTOMER_NUMBER" as c1,
	   (intdiv(T1."VOUCHER_NO",1)) as c2,
	   T1."LINE_NO_CU" as c3,
	   T1."TRANSACTION_STATUS" as c4,
	   T1."DUE_DATE" as c5,
	   T1."BOOKKEEP_DATE" as c6,
	   T1."STATE_CODE" 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,
	   T1."COMMENT_CU" as c31,
	   T1."SETOFF_ACCT" as c32,
	   T1."COLLECT_ACCT" as c33,
	   T1."INTEREST_CALC" as c34,
	   T1."INT_VOUCHER_NO" as c35,
	   T1."SUM_C_U" + T1."PAID_C_U" as c36,
	   T2."CUSTOMER_NUMBER" as c37,
	   T2."STATE_CODE_CUST" as c38,
	   T2."SALDO_C_U_CUST" as c39,
	   '1' as c40,
	   'nicht zuzuordnen' as c41,
	   'nicht zuzuordnen' as c42,
	   (now()) as c43,
	   (extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE")) as c44,
	   CASE WHEN (((extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE"))) BETWEEN 43 AND 84) THEN ('6 - 12 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE"))) > 84) THEN ('> 12 Wochen') ELSE null END as c45,
	   T2."NAME" as c46,
	   T2."NAME" || ' - ' || T1."CUSTOMER_NUMBER" as c47,
	   (char_length((ascii(((intdiv(T1."VOUCHER_NO",1))))))) as c48,
	   (ascii(((intdiv(T1."VOUCHER_NO",1))))) as c49,
	   (substring(((ascii(((intdiv(T1."VOUCHER_NO",1)))))) from 7 for 1)) as c50,
	   (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 c51,
	   (substring(((ascii(((intdiv(T1."VOUCHER_NO",1)))))) from 1 for 7)) || ' - ' || T1."COMMENT_CU" || ' - ' || ((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 c52,
	   'ZZ' as c53,
	   (asciiz(extract(YEAR FROM T1."DUE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."DUE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."DUE_DATE"),2)) as c54,
	   (substring(((asciiz(extract(YEAR FROM T1."DUE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."DUE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."DUE_DATE"),2))) from 9 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM T1."DUE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."DUE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."DUE_DATE"),2))) from 6 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM T1."DUE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T1."DUE_DATE"),2) || '-' || asciiz(extract(DAY FROM T1."DUE_DATE"),2))) from 1 for 4)) as c55,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."DUE_DATE")) >= 0) THEN ('fällig') ELSE ('nicht fällig') END as c56,
	   (extract(DAY FROM ((now())) - T1."DUE_DATE")) as c57,
	   CASE WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) < -43) THEN ('fällig in > 6 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN -42 AND -29) THEN ('fällig in 4 - 6 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN -28 AND -15) THEN ('fällig in 2 - 4 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN -14 AND -1) THEN ('fällig in < 2 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN 0 AND 14) THEN ('fällig seit < 2 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN 15 AND 28) THEN ('fällig seit 2 - 4 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN 29 AND 42) THEN ('fällig seit 4 - 6 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) BETWEEN 43 AND 84) THEN ('fällig seit 6 - 12 Wochen') WHEN (((extract(DAY FROM ((now())) - T1."DUE_DATE"))) > 84) THEN ('fällig seit > 12 Wochen') ELSE null END as c58,
	   CASE WHEN (T1."PMT_TERM" IN ('AL','B ','BE','BO','BR','IN','L ','OA','OP','RE','SP')) THEN ('Bankeinzug') ELSE ('Bareinzahlung') END as c59,
	   'nicht zuzuordnen' as c60,
	   'nicht zuzuordnen' as c61
from "deop01"."dbo"."CUSTOMER_TRANSACT" T1,
	"deop01"."dbo"."CUSTOMER" T2
where (T1."CUSTOMER_NUMBER" = T2."CUSTOMER_NUMBER")
and (((T1."SUM_C_U" + T1."PAID_C_U") <> .00) and (not T1."STATUS" IN ('35','36','37','39','47','49','FG','FR')))

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,Saldo_Beleg
COLUMN,36,Customer Number
COLUMN,37,State Code Cust
COLUMN,38,Saldo C U Cust
COLUMN,39,Hauptbetrieb
COLUMN,40,Standort
COLUMN,41,Kostenstelle
COLUMN,42,Heute
COLUMN,43,Tage
COLUMN,44,Staffel
COLUMN,45,Name
COLUMN,46,Kunde
COLUMN,47,Anzahl Stellen Rg
COLUMN,48,Voucher_Zeichen
COLUMN,49,7_Stelle
COLUMN,50,Datum_Zeichen
COLUMN,51,Beleg
COLUMN,52,Sel Name
COLUMN,53,Due Date Text
COLUMN,54,Due Date_Datum
COLUMN,55,Due Date faellig
COLUMN,56,Tage_Fälligkeit
COLUMN,57,Staffel_Fälligkeit
COLUMN,58,Zahlungscode
COLUMN,59,Marke
COLUMN,60,Kostenstelle_Detail