COGNOS QUERY STRUCTURE,1,1 DATABASE,O21 DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\GuV_8_O21.imr TITLE,GuV_8_O21.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 ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END as c50, CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = '')) THEN ('1') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 3 for 1))) END as c51, T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT" as c52, (database()) as c53, T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY" as c54, CASE WHEN ((extract(DAY FROM (now()) - T2."BOOKKEEP_DATE")) <= 90) THEN ((od_left((cast_numberToString(cast_integer(T2."DOCUMENT_NO"))),7)) || ' - ' || T3."COMMENT") ELSE null END as c55, (od_left(T1."ACCT_NR",1)) as c56, (od_left((ltrim(T1."ACCT_NR")),5)) || '_0' as c57, CASE WHEN (T4."DEPARTMENT_TYPE_ID" IN ('0219')) THEN ('21') WHEN ((CASE WHEN ((T1."ACCT_NR" LIKE '4%') and ((substring(T1."ACCT_NR" from 5 for 1)) = '9')) THEN ('MasterLease') ELSE null END) = 'MasterLease') THEN (('21')) ELSE ((CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = ' ')) THEN ('01') ELSE ((od_left(T4."DEPARTMENT_TYPE_ID",2))) END)) END as c58, CASE WHEN (((T3."COMMENT" IN ('Vortrag Citroen','Korr. Vortrag Citroen','Vortrag Citroen Korr.','Vortrag Fiat','Vortrag Fiat korr.')) and ((T2."BOOKKEEP_DATE" = TIMESTAMP '2016-02-29 00:00:00.000') or (T2."BOOKKEEP_DATE" = TIMESTAMP '2016-04-01 00:00:00.000')))) THEN ('Vortrag') ELSE ('kein Vortrag') END as c59, CASE WHEN ((T1."ACCT_NR" LIKE '84%') or (T1."ACCT_NR" LIKE '85%')) THEN ((od_left(T3."COMMENT",1))) ELSE null END as c60, T6."REPAIR_CODE_2" as c61, CASE WHEN (T4."DEPARTMENT_TYPE_ID" IN ('0219')) THEN ('9') ELSE ((CASE WHEN ((T1."ACCT_NR" LIKE '2%') and (T2."DEPARTMENT" = '')) THEN ('1') ELSE ((substring(T4."DEPARTMENT_TYPE_ID" from 3 for 1))) END)) END as c62, CASE WHEN ((extract(DAY FROM (now()) - T2."BOOKKEEP_DATE")) <= 90) THEN (T6."ORDER_NUMBER") ELSE (0) END as c63, T6."LINE_NUMBER" as c64, T6."REDUCTION_CODE" as c65, T6."KEY_PROD_CODE" as c66, CASE WHEN (T6."KEY_PROD_CODE" LIKE '%LF%') THEN (T6."KEY_PROD_CODE") ELSE ('andere') END as c67, CASE WHEN ((T1."ACCT_NR" LIKE '4%') and ((substring(T1."ACCT_NR" from 5 for 1)) = '9')) THEN ('MasterLease') ELSE null END as c68 from "deop01"."dbo"."ACCOUNT_INFO" T1, (("deop01"."dbo"."ACCT_DOC_KEY" T2 left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T4 on T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "deop01"."dbo"."AA_TRTYPE" T5 on T2."AA_TRTYPE" = T5."AA_TRTYPE_ID"), (("deop01"."dbo"."ACCT_DOC_DATA" T3 left outer join "deop01"."dbo"."ACCT_DOC_SALESCLAS" T7 on T3."REFERENCE_IDENT" = T7."REFERENCE_IDENT") left outer join "deop01"."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 ((CASE WHEN (((T3."COMMENT" IN ('Vortrag Citroen','Korr. Vortrag Citroen','Vortrag Citroen Korr.','Vortrag Fiat','Vortrag Fiat korr.')) and ((T2."BOOKKEEP_DATE" = TIMESTAMP '2016-02-29 00:00:00.000') or (T2."BOOKKEEP_DATE" = TIMESTAMP '2016-04-01 00:00:00.000')))) THEN ('Vortrag') ELSE ('kein Vortrag') END) = 'kein Vortrag')) and (T2."BOOKKEEP_DATE" >= TIMESTAMP '2020-01-01 00:00:00.000')) END SQL COLUMN,0,Acct Nr_ori 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_1 COLUMN,50,Marke_1 COLUMN,51,Betrag COLUMN,52,Mandant COLUMN,53,Menge COLUMN,54,Text COLUMN,55,Susa COLUMN,56,Acct Nr COLUMN,57,Betrieb COLUMN,58,Vortrag COLUMN,59,Comment_1 COLUMN,60,Repair Code 2 COLUMN,61,Marke COLUMN,62,Order Number COLUMN,63,Line Number COLUMN,64,Reduction Code COLUMN,65,Key Prod Code COLUMN,66,AO Nummer COLUMN,67,Kosten_MasterLease