C11/System/OPTIMA/IQD/Belege/GuV_8_O21_SKR_STK.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GlobalCube\SYSTEM\OPTIMA\IQD\belege\GuV_8_O21_SKR_STK.imr
TITLE,GuV_8_O21_SKR_STK.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,
	   (rtrim(T2."ACCT_NO")) || '_STK' 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,
	   '01' as c50,
	   CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END as c51,
	   T2."DEBIT_AMOUNT" + T2."CREDIT_AMOUNT" as c52,
	   '1' as c53,
	   (T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY") as c54,
	   CASE WHEN ((extract(DAY FROM (now()) - T2."BOOKKEEP_DATE")) <= 120) THEN ((od_left((cast_numberToString(cast_integer(T2."DOCUMENT_NO"))),7)) || ' - ' || T3."COMMENT") ELSE null END as c55,
	   (od_left(((rtrim(T2."ACCT_NO")) || '_STK'),1)) as c56,
	   T6."ORDER_NUMBER" as c57,
	   T6."LINE_NUMBER" as c58,
	   T6."INV_TIME" as c59,
	   T6."INV_TIME_INT" as c60,
	   T6."MAKE_TIME_UNIT" as c61,
	   T2."SITE" as c62,
	   CASE WHEN (T2."DEPARTMENT" = '    ') THEN ('00') ELSE (T2."DEPARTMENT") END as c63,
	   T7."MODEL_LINE" as c64,
	   T7."MOD_LIN_SPECIFY" as c65,
	   CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END as c66,
	   T8."CUSTOMER_NUMBER" as c67,
	   (substring((CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END) from 1 for 2)) as c68,
	   T9."CUSTOMER_GROUP" as c69,
	   CASE WHEN ((T2."DESTINATION" = '00') and (T2."PRICE_CODE" <> '')) THEN (T2."PRICE_CODE") WHEN (((T2."DESTINATION" = '00') and (T2."PRICE_CODE" = '')) and (T9."CUSTOMER_GROUP" <> '')) THEN (T9."CUSTOMER_GROUP") WHEN ((rtrim(T2."DESTINATION")) = '') THEN ('00') ELSE (T2."DESTINATION") END as c70,
	   '3' as c71,
	   'STK' as c72,
	   CASE WHEN (((substring((CASE WHEN (T2."DEPARTMENT" = '    ') THEN ('00') ELSE (T2."DEPARTMENT") END) from 1 for 1)) = '1') and (not ((substring((CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END) from 1 for 2))) IN ('00'))) THEN ('Neuwagen') WHEN (((substring((CASE WHEN (T2."DEPARTMENT" = '    ') THEN ('00') ELSE (T2."DEPARTMENT") END) from 1 for 1)) = '2') and (not ((substring((CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END) from 1 for 2))) IN ('00'))) THEN ('Gebrauchtwagen') ELSE ('Ohne Kostenträger') END as c73,
	   CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END || '-' || (CASE WHEN ((((rtrim(T1."ACCT_NR"))) LIKE '2%') and (T2."SITE" IS NULL)) THEN ('01') ELSE ((od_right('00' || (od_left(T2."SITE",2)),2))) END) || '-' || ((rtrim(T1."ACCT_NR"))) || '-' || (CASE WHEN ((T2."DEPARTMENT" IS NULL) or ((rtrim(T2."DEPARTMENT")) = '')) THEN ('00') ELSE ((rtrim(T2."DEPARTMENT"))) END) || '-' || (CASE WHEN ((T2."DESTINATION" IS NULL) or ((rtrim(T2."DESTINATION")) = '')) THEN ('00') ELSE ((od_right('00' || (rtrim(T2."DESTINATION")),2))) END) || '-' || ((truncate(CASE WHEN ((T7."MODEL_LINE" IS NOT NULL) and (T7."MODEL_LINE" <> '')) THEN (T7."MODEL_LINE") WHEN ((T2."PRODUCT_GROUP" IS NOT NULL) and (T2."PRODUCT_GROUP" <> '')) THEN (T2."PRODUCT_GROUP") WHEN ((T2."REPAIR_GROUP" IS NOT NULL) and (T2."REPAIR_GROUP" <> '')) THEN (T2."REPAIR_GROUP") ELSE ('00') END))) || '_STK' as c74,
	   CASE WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) IN ('OP')) THEN ('OP') ELSE null END as c75,
	   CASE WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) IN ('OP')) THEN ((CASE WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) IN ('OP')) THEN ('OP') ELSE null END) || (CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END)) WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) IN ('VW')) THEN ((CASE WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) IN ('OP')) THEN ('OP') ELSE null END) || (CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END)) ELSE ((CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END)) END as c76,
	   (substring((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END || '-' || (CASE WHEN ((((rtrim(T1."ACCT_NR"))) LIKE '2%') and (T2."SITE" IS NULL)) THEN ('01') ELSE ((od_right('00' || (od_left(T2."SITE",2)),2))) END) || '-' || ((rtrim(T1."ACCT_NR"))) || '-' || (CASE WHEN ((T2."DEPARTMENT" IS NULL) or ((rtrim(T2."DEPARTMENT")) = '')) THEN ('00') ELSE ((rtrim(T2."DEPARTMENT"))) END) || '-' || (CASE WHEN ((T2."DESTINATION" IS NULL) or ((rtrim(T2."DESTINATION")) = '')) THEN ('00') ELSE ((od_right('00' || (rtrim(T2."DESTINATION")),2))) END) || '-' || ((truncate(CASE WHEN ((T7."MODEL_LINE" IS NOT NULL) and (T7."MODEL_LINE" <> '')) THEN (T7."MODEL_LINE") WHEN ((T2."PRODUCT_GROUP" IS NOT NULL) and (T2."PRODUCT_GROUP" <> '')) THEN (T2."PRODUCT_GROUP") WHEN ((T2."REPAIR_GROUP" IS NOT NULL) and (T2."REPAIR_GROUP" <> '')) THEN (T2."REPAIR_GROUP") ELSE ('00') END))) || '_STK') from 1 for 8)) || '-' || (CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) || (od_left(T2."SITE",2)) || (substring((CASE WHEN (T2."DEPARTMENT" = '    ') THEN ('00') ELSE (T2."DEPARTMENT") END) from 1 for 2)) || (CASE WHEN ((T2."DESTINATION" = '00') and (T2."PRICE_CODE" <> '')) THEN (T2."PRICE_CODE") WHEN (((T2."DESTINATION" = '00') and (T2."PRICE_CODE" = '')) and (T9."CUSTOMER_GROUP" <> '')) THEN (T9."CUSTOMER_GROUP") WHEN ((rtrim(T2."DESTINATION")) = '') THEN ('00') ELSE (T2."DESTINATION") END) || ((substring((CASE WHEN (T7."MODEL_LINE" IS NOT NULL) THEN (T7."MODEL_LINE" || ' - ' || T7."MOD_LIN_SPECIFY") ELSE ('00 - ohne') END) from 1 for 2))) as c77,
	   CASE WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) = '00') THEN ('ohne Marke') ELSE ('mit Marke') END as c78,
	   CASE WHEN ((CASE WHEN (T2."DEPARTMENT" = '    ') THEN ('00') ELSE (T2."DEPARTMENT") END) = '00') THEN ('ohne KST') ELSE ('mit KST') END as c79,
	   CASE WHEN ((CASE WHEN ((T2."DESTINATION" = '00') and (T2."PRICE_CODE" <> '')) THEN (T2."PRICE_CODE") WHEN (((T2."DESTINATION" = '00') and (T2."PRICE_CODE" = '')) and (T9."CUSTOMER_GROUP" <> '')) THEN (T9."CUSTOMER_GROUP") WHEN ((rtrim(T2."DESTINATION")) = '') THEN ('00') ELSE (T2."DESTINATION") END) = '00') THEN (('ohne Absatzkanal')) ELSE ('mit Absatzkanal') END as c80,
	   T2."DESTINATION" as c81,
	   CASE WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) = 'OP') THEN ('OP - Opel') WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) = 'PE') THEN ('PE - Peugeot') WHEN ((CASE WHEN (((not T2."MAKE" IN ('OP','PE')) or (T2."MAKE" IS NULL)) or (T2."MAKE" = ' ')) THEN ((CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END)) ELSE (T2."MAKE") END) = 'AU') THEN ('00 - ohne Marke') ELSE ('Marke fehlt') END as c82,
	   T10."Hauptbetrieb_ID" as c83,
	   T10."Hauptbetrieb_Name" as c84,
	   T10."Standort_ID" as c85,
	   T10."Standort_Name" as c86,
	   CASE WHEN (T2."STRATEGIC_AREA" IN ('AUTO      ','Opel      ','OPEL      ')) THEN ('OP') WHEN (T2."STRATEGIC_AREA" IN ('PEUGEOT   ')) THEN ('PE') ELSE ('00') END as c87,
	   T2."PRICE_CODE" as c88
from "OPTIMA"."import"."ACCOUNT_INFO" T1,
	(((("OPTIMA"."import"."ACCT_DOC_KEY" T2 left outer join "OPTIMA"."import"."DEPARTMENT_TYPE" T4 on T2."DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "OPTIMA"."import"."AA_TRTYPE" T5 on T2."AA_TRTYPE" = T5."AA_TRTYPE_ID") left outer join "OPTIMA"."import"."vPP5Q" T7 on (T2."MODEL_LINE" = T7."MODEL_LINE") and (T2."MAKE" = T7."MAKE_CD")) left outer join "OPTIMA"."data"."GC_Department" T10 on (T2."CLIENT_DB" = T10."Hauptbetrieb") and (T2."SITE" = T10."Site")),
	((("OPTIMA"."import"."ACCT_DOC_DATA" T3 left outer join "OPTIMA"."import"."ACCT_DOC_SALESCLAS" T8 on T3."REFERENCE_IDENT" = T8."REFERENCE_IDENT") left outer join "OPTIMA"."import"."ORDER_LINE" T6 on (T6."ORDER_NUMBER" = T8."ORDER_NUMBER") and (T6."LINE_NUMBER" = T8."ORDER_LINE_NUMBER")) left outer join "OPTIMA"."import"."CUSTOMER" T9 on T9."CUSTOMER_NUMBER" = T8."CUSTOMER_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')) and (T1."ACCT_NR" IN ('8000','8005','8006','8010','8015','8016','8040','8041','8100','8105','8110','8115','8116','8400','8410','8411','5701','8420','8924','8928'))) and (((T2."DEBIT_QUANTITY" + T2."CREDIT_QUANTITY")) <> 0))

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
COLUMN,50,Marke
COLUMN,51,Betrag_Euro
COLUMN,52,Mandant
COLUMN,53,Betrag
COLUMN,54,Text
COLUMN,55,Susa
COLUMN,56,Order Number
COLUMN,57,Line Number
COLUMN,58,Inv Time
COLUMN,59,Inv Time Int
COLUMN,60,Make Time Unit
COLUMN,61,Site
COLUMN,62,KST
COLUMN,63,Model Line_vpp5q
COLUMN,64,Mod Lin Specify_vpp5q
COLUMN,65,Kostenträger_mit_Bez_
COLUMN,66,Customer Number
COLUMN,67,Kostenträger
COLUMN,68,Customer Group
COLUMN,69,Absatzkanal
COLUMN,70,GuV_Bilanz
COLUMN,71,Susa
COLUMN,72,Ebene31
COLUMN,73,Acct Nr
COLUMN,74,Marke für Kostenträger
COLUMN,75,Kostenträger_mit_Bez
COLUMN,76,ACCT_Detail
COLUMN,77,Buchungen ohne Marke
COLUMN,78,Buchungen ohne KST
COLUMN,79,Buchungen ohne Absatzkanal
COLUMN,80,Destination
COLUMN,81,Marke_mit_Bez
COLUMN,82,Hauptbetrieb Id
COLUMN,83,Hauptbetrieb Name
COLUMN,84,Standort Id
COLUMN,85,Standort Name
COLUMN,86,Marke_Strategic_Area
COLUMN,87,Price Code