GAPS/Portal/System/IQD/Belege/GuV_8_O21_9.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O_21_9
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\GuV_8_O21_9.imr
TITLE,GuV_8_O21_9.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,
	   CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END as c50,
	   CASE WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",1)) IS NULL) THEN ('Renault / Dacia') WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",1)) IN ('0','1')) THEN ('Renault / Dacia') WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",1)) IN ('3','4')) THEN ('Fiat / Jeep') WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",2)) IN ('20','21','22','24','25','26','62','65','66','68','69','70','71','72','73','74','80')) THEN ('Renault / Dacia') WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",2)) IN ('23','61','63','64','67','75','78')) THEN ('Fiat / Jeep') WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",2)) IN ('76','77','79')) THEN ('Kia') WHEN ((od_left(T4."DEPARTMENT_TYPE_ID",2)) IN ('50')) THEN ('Vespa') ELSE ('Marke fehlt') END as c51,
	   T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT" as c52,
	   (database()) as c53,
	   T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY" as c54,
	   (od_left(T1."ACCT_NR",1)) as c55,
	   T6."ORDER_NUMBER" as c56,
	   T6."LINE_NUMBER" as c57,
	   T6."INV_TIME" as c58,
	   T6."INV_TIME_INT" as c59,
	   T6."MAKE_TIME_UNIT" as c60,
	   CASE WHEN (T2."INT_VOUCHER_NO" = 4787129) THEN ('0') WHEN ((extract(DAY FROM (now()) - T2."BOOKKEEP_DATE")) <= 30) THEN ((truncate(((ascii((CASE WHEN (T2."INT_VOUCHER_NO" IN (4787129)) THEN (0) ELSE (T2."DOCUMENT_NO") END)))))) || ' - ' || (T3."COMMENT")) ELSE null END as c61,
	   CASE WHEN ((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) = '34') THEN ('34') WHEN ((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) = '48') THEN ('19') WHEN ((substring((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) from 1 for 1)) IN ('3')) THEN ('0' || (substring((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) from 2 for 1))) WHEN ((substring((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) from 1 for 1)) IN ('4')) THEN ('1' || (substring((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) from 2 for 1))) WHEN ((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) IN ('23')) THEN ('22') WHEN ((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) IN ('76')) THEN ('67') WHEN ((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END) IN ('78')) THEN ('77') ELSE ((CASE WHEN (((od_left(T1."ACCT_NR",1)) IN ('2','3')) and (T4."DEPARTMENT_TYPE_ID" IS NULL)) THEN ('01') WHEN (T4."DEPARTMENT_TYPE_ID" IS NULL) THEN ('dere09 - Info fehlt') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END)) END as c62,
	   CASE WHEN ((T5."AA_TRTYPE_ID" IN (5102,5202)) or (T1."ACCT_NR" IN ('71000','71001'))) THEN (T2."DOCUMENT_NO") ELSE (0) END as c63,
	   CASE WHEN ((CASE WHEN ((T5."AA_TRTYPE_ID" IN (5102,5202)) or (T1."ACCT_NR" IN ('71000','71001'))) THEN (T2."DOCUMENT_NO") ELSE (0) END) IN (0)) THEN ('kein Boni') ELSE (T3."COMMENT") END as c64,
	   CASE WHEN (T2."INT_VOUCHER_NO" IN (4787129)) THEN (0) ELSE (T2."DOCUMENT_NO") END as c65
from "dere1143"."dbo"."ACCOUNT_INFO" T1,
	(("dere1143"."dbo"."ACCT_DOC_KEY" T2 left outer join "dere1143"."dbo"."DEPARTMENT_TYPE" T4 on T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "dere1143"."dbo"."AA_TRTYPE" T5 on T2."AA_TRTYPE" = T5."AA_TRTYPE_ID"),
	(("dere1143"."dbo"."ACCT_DOC_DATA" T3 left outer join "dere1143"."dbo"."ACCT_DOC_SALESCLAS" T7 on T3."REFERENCE_IDENT" = T7."REFERENCE_IDENT") left outer join "dere1143"."dbo"."ORDER_LINE" T6 on (T6."ORDER_NUMBER" = T7."ORDER_NUMBER") and (T6."LINE_NUMBER" = T7."ORDER_LINE_NUMBER"))
where (T1."ACCT_NR" = T2."ACCT_NO") and (T2."UNIQUE_IDENT" = T3."REFERENCE_IDENT")
and ((T1."TYPE_ACCTT" = '2') and (T2."BOOKKEEP_DATE" >= TIMESTAMP '2019-09-01 00:00:00.000'))

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
COLUMN,52,Mandant
COLUMN,53,Menge
COLUMN,54,Susa
COLUMN,55,Order Number
COLUMN,56,Line Number
COLUMN,57,Inv Time
COLUMN,58,Inv Time Int
COLUMN,59,Make Time Unit
COLUMN,60,Text
COLUMN,61,Betrieb_Konsolidiert
COLUMN,62,Document_No_Boni
COLUMN,63,Comment_Boni
COLUMN,64,Document No_neu