COGNOS QUERY STRUCTURE,1,1 DATABASE,O_21_9 DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\GuV_8_O21_9_hist.imr TITLE,GuV_8_O21_9_hist.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, T6."MAKE_TIME_UNIT" as c54, T6."INV_TIME" as c55, CASE WHEN (T1."ACCT_NR" LIKE '8%') THEN (T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") ELSE null END as c56, T6."ORDER_NUMBER" as c57, T6."LINE_NUMBER" as c58, 'Summe Belege älter 30 Tage' as c59, (od_left(T1."ACCT_NR",1)) as c60, T6."USED_TIME" as c61, T6."USED_TIME_INT" as c62, (cast_numberToString(cast_integer(T2."DOCUMENT_NO"))) as c63, 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 c64, CASE WHEN (T5."AA_TRTYPE_ID" IN (5102,5202)) THEN (T2."DOCUMENT_NO") ELSE (0) END as c65, CASE WHEN ((CASE WHEN (T5."AA_TRTYPE_ID" IN (5102,5202)) THEN (T2."DOCUMENT_NO") ELSE (0) END) IN (0)) THEN ('kein Boni') ELSE (T3."COMMENT") END as c66 from "dere09"."dbo"."ACCOUNT_INFO" T1, (("dere09"."dbo"."ACCT_DOC_KEY_01" T2 left outer join "dere09"."dbo"."DEPARTMENT_TYPE" T4 on T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "dere09"."dbo"."AA_TRTYPE" T5 on T2."AA_TRTYPE" = T5."AA_TRTYPE_ID"), (("dere09"."dbo"."ACCT_DOC_DATA_01" T3 left outer join "dere09"."dbo"."ACCT_DOC_SALESCLAS" T7 on T3."REFERENCE_IDENT" = T7."REFERENCE_IDENT") left outer join "dere09"."dbo"."ORDER_LINE" T6 on (T6."ORDER_NUMBER" = T7."ORDER_NUMBER") and (T6."LINE_NUMBER" = T7."ORDER_LINE_NUMBER")) where (T2."ACCT_NO" = T1."ACCT_NR") and (T2."UNIQUE_IDENT" = T3."REFERENCE_IDENT") and ((T1."TYPE_ACCTT" = '2') and (T2."BOOKKEEP_DATE" >= TIMESTAMP '2018-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,Make Time Unit COLUMN,54,Inv Time COLUMN,55,Menge COLUMN,56,Order Number COLUMN,57,Line Number COLUMN,58,Text COLUMN,59,Susa COLUMN,60,Used Time COLUMN,61,Used Time Int COLUMN,62,Document No Text COLUMN,63,Betrieb_Konsolidiert COLUMN,64,Document_No_Boni COLUMN,65,Comment_Boni