GAPS/Portal/System/IQD/OP/OP_AS_SKR51.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,D:\Gaps\Portal\System\IQD\OP\OP_AS_SKR51.imr
TITLE,OP_AS_SKR51.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,
	   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,
	   T3."ORDER_NUMBER" as c40,
	   T3."STATUS" as c41,
	   T3."DEBIT_ACCOUNT" as c42,
	   T3."INVOICE_NUMBER" as c43,
	   T3."STATE_CODE" as c44,
	   T3."DELIVERY_ACCOUNT" as c45,
	   T3."DEPARTMENT" as c46,
	   T3."SALESMAN" as c47,
	   T4."SELLER_CODE" as c48,
	   T4."SEL_NAME" as c49,
	   '1' as c50,
	   (od_left(T3."DEPARTMENT",1)) as c51,
	   (substring(T3."DEPARTMENT" from 3 for 1)) as c52,
	   (now()) as c53,
	   (extract(DAY FROM ((now())) - T1."BOOKKEEP_DATE")) as c54,
	   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 c55,
	   T2."NAME" as c56,
	   T2."NAME" || ' - ' || T1."CUSTOMER_NUMBER" as c57,
	   (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 c58,
	   (cast_numberToString(cast_integer(T1."VOUCHER_NO"))) as c59,
	   (od_left(((cast_numberToString(cast_integer(T1."VOUCHER_NO")))),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 c60,
	   (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 c61,
	   (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 c62,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."DUE_DATE")) >= 0) THEN ('fällig') ELSE ('nicht fällig') END as c63,
	   (extract(DAY FROM ((now())) - T1."DUE_DATE")) as c64,
	   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 c65,
	   CASE WHEN (T1."PMT_TERM" IN ('AL','B ','BE','BO','BR','IN','L ','OA','OP','RE','SP')) THEN ('Bankeinzug') ELSE ('Bareinzahlung') END as c66,
	   (substring(T3."DEPARTMENT" from 2 for 1)) as c67,
	   (substring(T3."DEPARTMENT" from 3 for 2)) as c68
from "deop01"."dbo"."CUSTOMER_TRANSACT" T1,
	"deop01"."dbo"."CUSTOMER" T2,
	"deop01"."dbo"."ORDER_HEADER" T3,
	"deop01"."dbo"."vPP43" T4
where (T1."CUSTOMER_NUMBER" = T2."CUSTOMER_NUMBER") and (T1."DOCUMENT_NO" = T3."ORDER_NUMBER") and (T3."SALESMAN" = T4."SELLER_CODE")
and ((T1."SUM_C_U" + T1."PAID_C_U") <> .00)

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,Order Number
COLUMN,40,Status
COLUMN,41,Debit Account
COLUMN,42,Invoice Number
COLUMN,43,State Code
COLUMN,44,Delivery Account
COLUMN,45,Department
COLUMN,46,Salesman
COLUMN,47,Seller Code
COLUMN,48,Sel Name
COLUMN,49,Hauptbetrieb
COLUMN,50,Standort
COLUMN,51,Kostenstelle
COLUMN,52,Heute
COLUMN,53,Tage
COLUMN,54,Staffel
COLUMN,55,Name
COLUMN,56,Kunde
COLUMN,57,Datum_Zeichen
COLUMN,58,Voucher_Zeichen
COLUMN,59,Beleg
COLUMN,60,Due Date Text
COLUMN,61,Due Date_Datum
COLUMN,62,Due Date faellig
COLUMN,63,Tage_Fälligkeit
COLUMN,64,Staffel_Fälligkeit
COLUMN,65,Zahlungscode
COLUMN,66,Marke
COLUMN,67,Kostenstelle_Detail