COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_op27
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\Garantie_deop27.imr
TITLE,Garantie_deop27.imr
BEGIN SQL
select T1."ACCT_NR" as c1,
	   T1."LEDGER_ACCTS_NAME" as c2,
	   T1."LEDGER_ACCTS_NAME2" as c3,
	   T1."HANDLER" as c4,
	   T1."DEPT_SPLIT" as c5,
	   T1."TYPE_ACCTT" as c6,
	   T2."ACCT_NO" as c7,
	   T2."BOOKKEEP_DATE" as c8,
	   T2."BOOKKEEP_PERIOD" as c9,
	   T2."DOCUMENT_NO" as c10,
	   T2."ORIGIN" as c11,
	   T2."STATUS" as c12,
	   T2."DEBIT_AMOUNT" as c13,
	   T2."CREDIT_AMOUNT" as c14,
	   T2."DEBIT_QUANTITY" as c15,
	   T2."CREDIT_QUANTITY" as c16,
	   T2."AA_TRTYPE" as c17,
	   T2."DEPARTMENT" as c18,
	   T2."STOCK" as c19,
	   T2."MAKE_FAMILY" as c20,
	   T2."MAKE" as c21,
	   T2."VEHICLE_TYPE" as c22,
	   T2."MODEL_LINE" as c23,
	   T2."FACTORY_MODEL" as c24,
	   T2."WORKSHOP_MODEL" as c25,
	   T2."PRODUCT_GROUP" as c26,
	   T2."REPAIR_GROUP" as c27,
	   T2."KIT_GROUP" as c28,
	   T2."TIME_CODE" as c29,
	   T2."INT_VOUCHER_NO" as c30,
	   T2."BALANCING_MARK" as c31,
	   T2."USED_VEH_DEST_CODE" as c32,
	   T2."USE_OF_VEHICLE" as c33,
	   T2."ACCT_NO_NEXT_CHART" as c34,
	   T3."REFERENCE_IDENT" as c35,
	   T3."TRANSACT_DATE" as c36,
	   T3."HANDLER" as c37,
	   T3."PROGRAM" as c38,
	   T3."FUNCTION_CODE" as c39,
	   T3."MODUL" as c40,
	   T3."DOCUMENT_KEY" as c41,
	   T3."COMMENT" as c42,
	   T4."DEPARTMENT_TYPE_ID" as c43,
	   T4."DESCRIPTION" as c44,
	   T4."DEPARTMENT_GROUP" as c45,
	   T5."AA_TRTYPE_ID" as c46,
	   T5."DESCRIPTION" as c47,
	   T5."OWN_DESCRIPTION" as c48,
	   '1' as c49,
	   ((od_left(T4."DEPARTMENT_TYPE_ID",2))) as c50,
	   (substring(T4."DEPARTMENT_TYPE_ID" from 3 for 1)) as c51,
	   CASE WHEN ((((T1."ACCT_NR" LIKE '8%') and (not T1."ACCT_NR" BETWEEN '89050000' AND '89500001')) and (not T1."ACCT_NR" BETWEEN '8812000' AND '8812002')) or ((T1."ACCT_NR" LIKE '9%') and (T1."ACCT_NR" <> '99999999'))) THEN ((T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") * -1) ELSE (T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") END as c52,
	   (database()) as c53,
	   '' as c54,
	   T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY" as c55,
	   CASE WHEN (T3."COMMENT" LIKE '%Eröffnungs-Transaktion%') THEN ('') ELSE ((substring(T3."COMMENT" from 16 for 8))) END as c56,
	   T6."UNIT_NUMBER" as c57,
	   T7."CHASSIS_NUMBER" as c58,
	   T7."FIRST_REG_DATE" as c59,
	   T6."ACCOUNT_SALES" as c60,
	   (rtrim(T8."NAME")) as c61,
	   T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6) as c62,
	   CASE WHEN ((CASE WHEN (T7."CHASSIS_NUMBER" IS NULL) THEN (1) ELSE (0) END) = 1) THEN (((rtrim(((cast_numberToString(cast_integer(T2."DOCUMENT_NO")))))) || ' - ' || T3."COMMENT")) ELSE ((rtrim(T6."ACCOUNT_SALES")) || ' - ' || (rtrim(((rtrim(T8."NAME")))))) END as c63,
	   (cdate(T7."FIRST_REG_DATE")) as c64,
	   (cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))) as c65,
	   (asciiz(extract(YEAR FROM ((cdate(T7."FIRST_REG_DATE")))),4) || '-' || asciiz(extract(MONTH FROM ((cdate(T7."FIRST_REG_DATE")))),2) || '-' || asciiz(extract(DAY FROM ((cdate(T7."FIRST_REG_DATE")))),2)) as c66,
	   (substring(((asciiz(extract(YEAR FROM ((cdate(T7."FIRST_REG_DATE")))),4) || '-' || asciiz(extract(MONTH FROM ((cdate(T7."FIRST_REG_DATE")))),2) || '-' || asciiz(extract(DAY FROM ((cdate(T7."FIRST_REG_DATE")))),2))) from 9 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM ((cdate(T7."FIRST_REG_DATE")))),4) || '-' || asciiz(extract(MONTH FROM ((cdate(T7."FIRST_REG_DATE")))),2) || '-' || asciiz(extract(DAY FROM ((cdate(T7."FIRST_REG_DATE")))),2))) from 6 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM ((cdate(T7."FIRST_REG_DATE")))),4) || '-' || asciiz(extract(MONTH FROM ((cdate(T7."FIRST_REG_DATE")))),2) || '-' || asciiz(extract(DAY FROM ((cdate(T7."FIRST_REG_DATE")))),2))) from 1 for 4)) as c67,
	   CASE WHEN (T7."CHASSIS_NUMBER" IS NULL) THEN (1) ELSE (0) END as c68,
	   (asciiz(extract(YEAR FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),4) || '-' || asciiz(extract(MONTH FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2) || '-' || asciiz(extract(DAY FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2)) as c69,
	   (substring(((asciiz(extract(YEAR FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),4) || '-' || asciiz(extract(MONTH FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2) || '-' || asciiz(extract(DAY FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2))) from 9 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),4) || '-' || asciiz(extract(MONTH FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2) || '-' || asciiz(extract(DAY FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2))) from 6 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),4) || '-' || asciiz(extract(MONTH FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2) || '-' || asciiz(extract(DAY FROM ((cdate((T7."FIRST_REG_DATE" + (INTERVAL '365 00:00:00.000' * 6)))))),2))) from 1 for 4)) as c70,
	   (CASE WHEN ((((T1."ACCT_NR" LIKE '8%') and (not T1."ACCT_NR" BETWEEN '89050000' AND '89500001')) and (not T1."ACCT_NR" BETWEEN '8812000' AND '8812002')) or ((T1."ACCT_NR" LIKE '9%') and (T1."ACCT_NR" <> '99999999'))) THEN ((T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") * -1) ELSE (T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT") END) * -1 as c71,
	   (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 c72,
	   (substring(((asciiz(extract(YEAR FROM T2."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T2."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T2."BOOKKEEP_DATE"),2))) from 9 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM T2."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T2."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T2."BOOKKEEP_DATE"),2))) from 6 for 2)) || '.' || (substring(((asciiz(extract(YEAR FROM T2."BOOKKEEP_DATE"),4) || '-' || asciiz(extract(MONTH FROM T2."BOOKKEEP_DATE"),2) || '-' || asciiz(extract(DAY FROM T2."BOOKKEEP_DATE"),2))) from 1 for 4)) as c73,
	   (cast_numberToString(cast_integer(T2."DOCUMENT_NO"))) as c74,
	   (rtrim(((cast_numberToString(cast_integer(T2."DOCUMENT_NO")))))) || ' - ' || T3."COMMENT" as c75
from "deop27"."dbo"."ACCOUNT_INFO" T1,
	(("deop27"."dbo"."ACCT_DOC_KEY" T2 left outer join "deop27"."dbo"."DEPARTMENT_TYPE" T4 on T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "deop27"."dbo"."AA_TRTYPE" T5 on T2."AA_TRTYPE" = T5."AA_TRTYPE_ID"),
	((("deop27"."dbo"."ACCT_DOC_DATA" T3 full outer join "deop27"."dbo"."UNIT_FILE" T6 on T6."UNIT_NUMBER" = (substring(T3."COMMENT" from 16 for 8))) left outer join "deop27"."dbo"."VEHICLE" T7 on T6."BASIS_NUMBER" = T7."BASIS_NUMBER") left outer join "deop27"."dbo"."CUSTOMER" T8 on T6."ACCOUNT_SALES" = T8."CUSTOMER_NUMBER")
where (T1."ACCT_NR" = T2."ACCT_NO") and (T2."UNIQUE_IDENT" = T3."REFERENCE_IDENT")
and ((T1."ACCT_NR" = '09090003') and (T2."BOOKKEEP_DATE" >= TIMESTAMP '2020-01-01 00:00:00.000'))
order by c1 asc

END SQL
COLUMN,0,Acct Nr
COLUMN,1,Ledger Accts Name
COLUMN,2,Ledger Accts Name2
COLUMN,3,Handler
COLUMN,4,Dept Split
COLUMN,5,Type Acctt
COLUMN,6,Acct No
COLUMN,7,Bookkeep Date
COLUMN,8,Bookkeep Period
COLUMN,9,Document No
COLUMN,10,Origin
COLUMN,11,Status
COLUMN,12,Debit Amount
COLUMN,13,Credit Amount
COLUMN,14,Debit Quantity
COLUMN,15,Credit Quantity
COLUMN,16,Aa Trtype
COLUMN,17,Department
COLUMN,18,Stock
COLUMN,19,Make Family
COLUMN,20,Make
COLUMN,21,Vehicle Type
COLUMN,22,Model Line
COLUMN,23,Factory Model
COLUMN,24,Workshop Model
COLUMN,25,Product Group
COLUMN,26,Repair Group
COLUMN,27,Kit Group
COLUMN,28,Time Code
COLUMN,29,Int Voucher No
COLUMN,30,Balancing Mark
COLUMN,31,Used Veh Dest Code
COLUMN,32,Use Of Vehicle
COLUMN,33,Acct No Next Chart
COLUMN,34,Reference Ident
COLUMN,35,Transact Date
COLUMN,36,Handler
COLUMN,37,Program
COLUMN,38,Function Code
COLUMN,39,Modul
COLUMN,40,Document Key
COLUMN,41,Comment
COLUMN,42,Department Type Id
COLUMN,43,Description
COLUMN,44,Department Group
COLUMN,45,Aa Trtype Id
COLUMN,46,Description
COLUMN,47,Own Description
COLUMN,48,Rechtseinheit
COLUMN,49,Betrieb
COLUMN,50,Marke
COLUMN,51,Betrag_ori
COLUMN,52,Mandant
COLUMN,53,Text
COLUMN,54,Stk
COLUMN,55,Fzgnr
COLUMN,56,Unit Number
COLUMN,57,Chassis Number
COLUMN,58,First Reg Date
COLUMN,59,Account Sales
COLUMN,60,Name
COLUMN,61,Garantieende
COLUMN,62,Kunde
COLUMN,63,Garantiebeginn
COLUMN,64,Garantieende Datum
COLUMN,65,Garantiebeginn Zeichenkette
COLUMN,66,Garantie Beginn
COLUMN,67,Eröffnungs Transaktion
COLUMN,68,Garantieende Zeichenkette
COLUMN,69,Garantie Ende
COLUMN,70,Betrag
COLUMN,71,Buchungsdatum Zeichenkette
COLUMN,72,Buchungsdatum
COLUMN,73,Document Number Text
COLUMN,74,Text_Fzg_ohne_Bezug