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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\OP\Verbindlichkeiten.imr
TITLE,Verbindlichkeiten.imr
BEGIN SQL
select T1."SUPPLIER_CODE" as c1,
	   T1."NAME" as c2,
	   T2."SUPPLIER_CODE" as c3,
	   T2."VOUCHER_NO" as c4,
	   T2."LINE_NO_SU" as c5,
	   T2."DOCUMENT_NO_SU" as c6,
	   T2."TRANSACT_STATUS_SU" as c7,
	   T2."DUE_DATE" as c8,
	   T2."BOOKKEEP_DATE" as c9,
	   T2."STATE_CODE_SU" as c10,
	   T2."TRANSACT_DATE" as c11,
	   T2."HANDLER" as c12,
	   T2."CREATION_DATE" as c13,
	   T2."CREATION_TIME" as c14,
	   T2."TRANSACT_CODE_SU" as c15,
	   T2."MODUL" as c16,
	   T2."BATCH_NUMBER" as c17,
	   T2."JOURNAL_NO" as c18,
	   T2."DOCUMENT_DATE" as c19,
	   T2."DISCOUNT_DATE" as c20,
	   T2."PMT_TERM" as c21,
	   T2."SUM_C_U" as c22,
	   T2."TAX_C_U" as c23,
	   T2."CASH_DISCOUNT_C_U" as c24,
	   T2."PAID_C_U" as c25,
	   T2."COMMENT_SU" as c26,
	   T2."SETOFF_ACCT" as c27,
	   T2."COLLECT_ACCT" as c28,
	   T2."PMT_WAY" as c29,
	   T2."APPROVED" as c30,
	   T2."PMT_PROPOSAL" as c31,
	   T2."ACCEPT_PERSON" as c32,
	   T2."ACCEPT_DATE" as c33,
	   T2."ARCHIVE_NO" as c34,
	   T2."REMARK_1" as c35,
	   T2."REMARK_2" as c36,
	   T2."REMARK_3" as c37,
	   T2."REFERENCE_TYPE_SU" as c38,
	   T2."REFERENCE_SU" as c39,
	   T2."EEC_TAX_C_U" as c40,
	   T2."EEC_INVOICE" as c41,
	   T2."CURRENCY_CODE4" as c42,
	   T2."ORG_EXCHANGE_RATE" as c43,
	   T2."CURRENCY_SUM_C_U" as c44,
	   T2."CURRENCY_PAID_C_U" as c45,
	   T2."BOOK_NO" as c46,
	   T2."CHECK_NUMBER" as c47,
	   T2."PRELIM_PURCH_ACCT" as c48,
	   T2."FINAL_BATCH_NO" as c49,
	   T2."INT_VOUCHER_NO" as c50,
	   T2."CONV_FLAG" as c51,
	   T2."UNIQUE_IDENT" as c52,
	   (T2."SUM_C_U" + T2."PAID_C_U") * -1 as c53,
	   RSUM(((T2."SUM_C_U" + T2."PAID_C_U") * -1)) as c54,
	   T1."SALDO_C_U_SUPP" * -1 as c55,
	   '1' as c56,
	   '01' as c57,
	   T1."NAME" || '- ' || T2."SUPPLIER_CODE" as c58,
	   T2."DUE_DATE" as c59,
	   (extract(DAY FROM (now()) - T2."DUE_DATE")) as c60,
	   CASE WHEN (((extract(DAY FROM (now()) - T2."DUE_DATE"))) < 0) THEN ('noch nicht fällig') WHEN (((extract(DAY FROM (now()) - T2."DUE_DATE"))) BETWEEN 0 AND 14) THEN ('< 2 Wochen') WHEN (((extract(DAY FROM (now()) - T2."DUE_DATE"))) BETWEEN 15 AND 28) THEN ('2 - 4 Wochen') WHEN (((extract(DAY FROM (now()) - T2."DUE_DATE"))) BETWEEN 29 AND 42) THEN ('4 - 6 Wochen') WHEN (((extract(DAY FROM (now()) - T2."DUE_DATE"))) > 42) THEN ('> 6 Wochen') ELSE null END as c61,
	   (od_left((cast_numberToString(cast_integer(T2."INT_VOUCHER_NO"))),7)) || ' - ' || T2."COMMENT_SU" || ' - ' || (asciiz(extract(YEAR FROM T2."DUE_DATE"),4) || '-' || asciiz(extract(MONTH FROM T2."DUE_DATE"),2) || '-' || asciiz(extract(DAY FROM T2."DUE_DATE"),2)) as c62
from ("deop01"."dbo"."SUPPLIER_TRANSACT" T2 left outer join "deop01"."dbo"."SUPPLIER" T1 on T1."SUPPLIER_CODE" = T2."SUPPLIER_CODE")
where (((T2."SUM_C_U" + T2."PAID_C_U") * -1) <> 0.00)

END SQL
COLUMN,0,Supplier Code
COLUMN,1,Name
COLUMN,2,Supplier Code
COLUMN,3,Voucher No
COLUMN,4,Line No Su
COLUMN,5,Document No Su
COLUMN,6,Transact Status Su
COLUMN,7,Due Date
COLUMN,8,Bookkeep Date_ori
COLUMN,9,State Code Su
COLUMN,10,Transact Date
COLUMN,11,Handler
COLUMN,12,Creation Date
COLUMN,13,Creation Time
COLUMN,14,Transact Code Su
COLUMN,15,Modul
COLUMN,16,Batch Number
COLUMN,17,Journal No
COLUMN,18,Document Date
COLUMN,19,Discount Date
COLUMN,20,Pmt Term
COLUMN,21,Sum C U
COLUMN,22,Tax C U
COLUMN,23,Cash Discount C U
COLUMN,24,Paid C U
COLUMN,25,Comment Su
COLUMN,26,Setoff Acct
COLUMN,27,Collect Acct
COLUMN,28,Pmt Way
COLUMN,29,Approved
COLUMN,30,Pmt Proposal
COLUMN,31,Accept Person
COLUMN,32,Accept Date
COLUMN,33,Archive No
COLUMN,34,Remark 1
COLUMN,35,Remark 2
COLUMN,36,Remark 3
COLUMN,37,Reference Type Su
COLUMN,38,Reference Su
COLUMN,39,Eec Tax C U
COLUMN,40,Eec Invoice
COLUMN,41,Currency Code4
COLUMN,42,Org Exchange Rate
COLUMN,43,Currency Sum C U
COLUMN,44,Currency Paid C U
COLUMN,45,Book No
COLUMN,46,Check Number
COLUMN,47,Prelim Purch Acct
COLUMN,48,Final Batch No
COLUMN,49,Int Voucher No
COLUMN,50,Conv Flag
COLUMN,51,Unique Ident
COLUMN,52,Saldo_Beleg
COLUMN,53,Summe  (Saldo_Beleg) Nr.1
COLUMN,54,Saldo C U Supp
COLUMN,55,Hauptbetrieb
COLUMN,56,Standort
COLUMN,57,Kunde
COLUMN,58,Bookkeep Date
COLUMN,59,Tage
COLUMN,60,Staffel
COLUMN,61,Beleg