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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\OP\Verbindlichkeiten_fSpez_Lieferanten.imr
TITLE,Verbindlichkeiten_fSpez_Lieferanten.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,
	   RSUM(c53) as c54,
	   c55 as c55,
	   c56 as c56,
	   c57 as c57,
	   c58 as c58,
	   c59 as c59
from 
(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" as c53,
	   T1."SALDO_C_U_SUPP" as c55,
	   T1."SUPPLIER_CODE" || ' - ' || T1."NAME" as c56,
	   '1' as c57,
	   (od_left(T2."VOUCHER_NO",10)) || ' - ' || T2."COMMENT_SU" || ' - ' || ((asciiz(extract(YEAR FROM T2."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T2."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T2."BOOKKEEP_DATE"),2))) as c58,
	   (asciiz(extract(YEAR FROM T2."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T2."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T2."BOOKKEEP_DATE"),2)) as c59
from ("deop01"."dbo"."SUPPLIER_TRANSACT" T2 left outer join "deop01"."dbo"."SUPPLIER" T1 on T1."SUPPLIER_CODE" = T2."SUPPLIER_CODE")
where (T1."SUPPLIER_CODE" IN ('6228','807280','7286'))
order by c4 asc,c16 asc
) D1

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
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,Lieferant
COLUMN,56,Hauptbetrieb
COLUMN,57,Beleg
COLUMN,58,Dateum_Zeichen