GAPS/Portal/System/IQD/NW/NW_GW_VK_Stk_09.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O_21_9
DATASOURCENAME,C:\GAPS\Portal\System\IQD\NW\NW_GW_VK_Stk_09.imr
TITLE,NW_GW_VK_Stk_09
BEGIN SQL
select T1."UNIT_NUMBER" as c1,
	   T1."BASIS_NUMBER" as c2,
	   T1."ECC_STATUS" as c3,
	   T1."VEHICLE_TYPE" as c4,
	   T2."VEHICLE_TYPE" as c5,
	   T2."VEHICLE_TYPE_TEXT" as c6,
	   T1."CAR_CREATION_DATE" as c7,
	   T1."DEPRECIATION" as c8,
	   T1."BOOK_KEEPING_CODE" as c9,
	   T3."BOOK_KEEPING_CODE" as c10,
	   T3."SPECIFY" as c11,
	   T1."MILEAGE" as c12,
	   T1."PURCH_DEPARTMENT" as c13,
	   T1."PURCH_SALSMAN_CODE" as c14,
	   T1."PURCH_DATE" as c15,
	   T1."RECEPTION_DATE" as c16,
	   T1."SALES_DEPARTMENT" as c17,
	   T4."DEPARTMENT_TYPE_ID" as c18,
	   T4."DESCRIPTION" as c19,
	   T1."SALE_SALESMAN" as c20,
	   T5."SELLER_CODE" as c21,
	   T5."SEL_NAME" as c22,
	   T5."SEL_FAMILY_NAME" as c23,
	   T1."SALE_INV_NUMBER" as c24,
	   T1."SALE_DATE" as c25,
	   T1."BUD_SALE_DET" as c26,
	   T1."BUD_PURCH_PRICE" as c27,
	   T1."BUD_REG_FEE" as c28,
	   T1."BUD_DEDUCT_FEE" as c29,
	   T1."BUD_COST" as c30,
	   T1."PURCH_PRICE_UNIT" as c31,
	   T1."COST_UNIT" as c32,
	   T1."SALES_PRICE_UNIT" as c33,
	   T1."REG_FEE" as c34,
	   T1."STOCK_DAYS" as c35,
	   T1."BUYING_ORDER_NO" as c36,
	   T1."BUYING_ORDER_DATE" as c37,
	   T1."PA_NUMBER" as c38,
	   T1."LOCATION_CODE" as c39,
	   T1."OWNER" as c40,
	   T1."PREV_OWNER" as c41,
	   T1."ACCOUNT_PURCHASE" as c42,
	   T1."ACCOUNT_SALES" as c43,
	   T6."CUSTOMER_NUMBER" as c44,
	   T6."NAME" as c45,
	   T6."CUSTOMER_GROUP" as c46,
	   T7."CUSTOMER_GROUP" as c47,
	   T7."CUST_GROUP_SPECIFY" as c48,
	   T1."ORDER_ARRIVAL_DATE" as c49,
	   T1."ORDER_CONFIRM_DATE" as c50,
	   T1."CONTRACT_DATE" as c51,
	   T8."REGISTER_NUMBER" as c52,
	   T8."CHASSIS_NUMBER" as c53,
	   T8."OWNER_CODE" as c54,
	   T8."FIRST_REG_DATE" as c55,
	   T8."LATEST_REG_DATE" as c56,
	   T8."ARRIVAL_DATE" as c57,
	   T8."SALESMAN_BUY" as c58,
	   T8."SALESMAN_SALE" as c59,
	   T8."MODEL_TEXT" as c60,
	   T8."COLOUR_CF" as c61,
	   T8."MODEL_LINE" as c62,
	   T9."MODEL_LINE" as c63,
	   T9."MOD_LIN_SPECIFY" as c64,
	   T8."WORKSHOP_MODEL" as c65,
	   T8."MILEAGE" as c66,
	   T8."MOTOR_CODE" as c67,
	   T8."DRIVE_CODE" as c68,
	   T8."COLOUR_CODE" as c69,
	   T8."MAKE" as c70,
	   T8."MAKE_CD" as c71,
	   T10."GLOBAL_MAKE_CD" as c72,
	   T10."DESCRIPTION" as c73,
	   T8."ORIG_INV_DATE" as c74,
	   T8."LATEST_INV_DATE" as c75,
	   T1."SALE_DATE" as c76,
	   '1' as c77,
	   (od_left(T1."SALES_DEPARTMENT",2)) as c78,
	   (rtrim(T5."SEL_NAME")) as c79,
	   T10."DESCRIPTION" as c80,
	   T9."MOD_LIN_SPECIFY" as c81,
	   T8."MODEL_TEXT" as c82,
	   T8."CHASSIS_NUMBER" as c83,
	   CASE WHEN (T1."VEHICLE_TYPE" IN ('N','T','V','M')) THEN ('Neuwagen') WHEN (T1."VEHICLE_TYPE" IN ('D','R','G')) THEN ('Gebrauchtwagen') ELSE null END as c84,
	   T1."VEHICLE_TYPE" || ' - ' || T2."VEHICLE_TYPE_TEXT" as c85,
	   T6."CUSTOMER_NUMBER" || '- ' || T6."NAME" as c86,
	   T6."CUSTOMER_GROUP" || ' - ' || T7."CUST_GROUP_SPECIFY" as c87,
	   '' as c88,
	   T8."COLOUR_CODE" || ' - ' || T8."COLOUR_CF" as c89,
	   CASE WHEN (T1."ECC_STATUS" = '21') THEN ('21 - Vorlauf') WHEN (T1."ECC_STATUS" = '25') THEN ('25 - für Kd best. FZG') WHEN (T1."ECC_STATUS" = '41') THEN ('41 - Bestand') WHEN (T1."ECC_STATUS" = '44') THEN ('44 - am Hof nicht fakt.') WHEN (T1."ECC_STATUS" = '64') THEN ('64 - verkauft') ELSE null END as c90,
	   T1."UNIT_NUMBER" || ' - ' || T8."CHASSIS_NUMBER" as c91,
	   T1."UNIT_NUMBER" || ' - ' || T1."OWNER" || ' - ' || (asciiz(extract(YEAR FROM T8."ORIG_INV_DATE"),4) || '-' || asciiz(extract(MONTH FROM T8."ORIG_INV_DATE"),2) || '-' || asciiz(extract(DAY FROM T8."ORIG_INV_DATE"),2)) as c92,
	   (T1."UNIT_NUMBER" || ' - ' || T8."CHASSIS_NUMBER") || ' - ' || T1."VEHICLE_TYPE" as c93,
	   T11."SEL_NAME" as c94,
	   T11."SEL_NAME" as c95,
	   T1."PREV_OWNER" as c96,
	   CASE WHEN (T11."SEL_NAME" IS NULL) THEN (' - ' || ' / ' || T1."PREV_OWNER") ELSE (T11."SEL_NAME" || ' / ' || T1."PREV_OWNER") END as c97,
	   T8."MODEL_TEXT" as c98,
	   ((rtrim(T5."SEL_NAME"))) || ' / ' || (T6."CUSTOMER_NUMBER" || '- ' || T6."NAME") as c99,
	   1 as c100,
	   CASE WHEN (T1."RECEPTION_DATE" IS NULL) THEN ((extract(DAY FROM T1."SALE_DATE" - T1."PURCH_DATE"))) ELSE ((extract(DAY FROM T1."SALE_DATE" - T1."RECEPTION_DATE"))) END as c101,
	   (database()) as c102,
	   'Menge' as c103,
	   CASE WHEN (T10."DESCRIPTION" IN ('Renault','Dacia','Fiat','Jeep','Kia','Piaggio (Vespa)')) THEN (T10."DESCRIPTION") ELSE ('Fremdfabrikat') END as c104,
	   T6."E_MAIL_ADDRESS" as c105,
	   T6."E_MAIL_ADDRESS_2" as c106,
	   CASE WHEN ((T6."E_MAIL_ADDRESS" IS NULL) and (T6."E_MAIL_ADDRESS_2" IS NULL)) THEN ('keine Mailadresse') WHEN ((T6."E_MAIL_ADDRESS" IS NOT NULL) and (T6."E_MAIL_ADDRESS_2" IS NULL)) THEN (T6."E_MAIL_ADDRESS") WHEN ((T6."E_MAIL_ADDRESS" IS NULL) and (T6."E_MAIL_ADDRESS_2" IS NOT NULL)) THEN (T6."E_MAIL_ADDRESS_2") ELSE (T6."E_MAIL_ADDRESS" || ' / ' || T6."E_MAIL_ADDRESS_2") END as c107,
	   (((rtrim(T5."SEL_NAME"))) || ' / ' || (T6."CUSTOMER_NUMBER" || '- ' || T6."NAME")) || ' / ' || (CASE WHEN ((T6."E_MAIL_ADDRESS" IS NULL) and (T6."E_MAIL_ADDRESS_2" IS NULL)) THEN ('keine Mailadresse') WHEN ((T6."E_MAIL_ADDRESS" IS NOT NULL) and (T6."E_MAIL_ADDRESS_2" IS NULL)) THEN (T6."E_MAIL_ADDRESS") WHEN ((T6."E_MAIL_ADDRESS" IS NULL) and (T6."E_MAIL_ADDRESS_2" IS NOT NULL)) THEN (T6."E_MAIL_ADDRESS_2") ELSE (T6."E_MAIL_ADDRESS" || ' / ' || T6."E_MAIL_ADDRESS_2") END) || ' / ' || (CASE WHEN ((T6."PHONE_1" IS NULL) and (T6."PHONE_2" IS NULL)) THEN ('keine Tel.-Nr.') WHEN ((T6."PHONE_1" IS NOT NULL) and (T6."PHONE_2" IS NULL)) THEN (T6."PHONE_1") WHEN ((T6."PHONE_1" IS NULL) and (T6."PHONE_2" IS NOT NULL)) THEN (T6."PHONE_2") ELSE (T6."PHONE_1" || ' / ' || T6."PHONE_2") END) as c108,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."SALE_DATE")) <= 60) THEN (((T1."UNIT_NUMBER" || ' - ' || T8."CHASSIS_NUMBER") || ' - ' || T1."VEHICLE_TYPE")) ELSE ('Summe Fzg älter 60 Tage') END as c109,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."SALE_DATE")) <= 60) THEN ((CASE WHEN (T11."SEL_NAME" IS NULL) THEN (' - ' || ' / ' || T1."PREV_OWNER") ELSE (T11."SEL_NAME" || ' / ' || T1."PREV_OWNER") END)) ELSE ('Summe Fzg älter 60 Tage') END as c110,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."SALE_DATE")) <= 60) THEN (T8."MODEL_TEXT") ELSE ('Summe Fzg älter 60 Tage') END as c111,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."SALE_DATE")) <= 60) THEN ((((rtrim(T5."SEL_NAME"))) || ' / ' || (T6."CUSTOMER_NUMBER" || '- ' || T6."NAME"))) ELSE ('Summe Fzg älter 60 Tage') END as c112,
	   T6."PHONE_1" as c113,
	   T6."PHONE_2" as c114,
	   CASE WHEN ((T6."PHONE_1" IS NULL) and (T6."PHONE_2" IS NULL)) THEN ('keine Tel.-Nr.') WHEN ((T6."PHONE_1" IS NOT NULL) and (T6."PHONE_2" IS NULL)) THEN (T6."PHONE_1") WHEN ((T6."PHONE_1" IS NULL) and (T6."PHONE_2" IS NOT NULL)) THEN (T6."PHONE_2") ELSE (T6."PHONE_1" || ' / ' || T6."PHONE_2") END as c115
from (((((((((("dere1143"."dbo"."UNIT_FILE" T1 left outer join "dere1143"."dbo"."vPP5R" T2 on T1."VEHICLE_TYPE" = T2."VEHICLE_TYPE") left outer join "dere1143"."dbo"."vPP5M" T3 on T1."BOOK_KEEPING_CODE" = T3."BOOK_KEEPING_CODE") left outer join "dere1143"."dbo"."DEPARTMENT_TYPE" T4 on T1."SALES_DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "dere1143"."dbo"."vPP43" T5 on T1."SALE_SALESMAN" = T5."SELLER_CODE") left outer join "dere1143"."dbo"."CUSTOMER" T6 on T1."ACCOUNT_SALES" = T6."CUSTOMER_NUMBER") left outer join "dere1143"."dbo"."vPP48" T7 on T6."CUSTOMER_GROUP" = T7."CUSTOMER_GROUP") left outer join "dere1143"."dbo"."VEHICLE" T8 on T1."BASIS_NUMBER" = T8."BASIS_NUMBER") left outer join "dere1143"."dbo"."vPP5Q" T9 on (T8."MODEL_LINE" = T9."MODEL_LINE") and (T8."MAKE_CD" = T9."MAKE_CD")) left outer join "dere1143"."dbo"."GLOBAL_MAKE" T10 on T8."MAKE_CD" = T10."GLOBAL_MAKE_CD") left outer join "dere1143"."dbo"."vPP43" T11 on T1."PURCH_SALSMAN_CODE" = T11."SELLER_CODE")
where ((T1."ECC_STATUS" = '64') and (T1."SALE_DATE" >= TIMESTAMP '2020-09-01 00:00:00.000'))
order by c1 asc

END SQL
COLUMN,0,Unit Number
COLUMN,1,Basis Number
COLUMN,2,Ecc Status
COLUMN,3,Vehicle Type
COLUMN,4,Vehicle Type_vpp5r
COLUMN,5,Vehicle Type Text_vpp5r
COLUMN,6,Car Creation Date
COLUMN,7,Depreciation
COLUMN,8,Book Keeping Code
COLUMN,9,Book Keeping Code_vpp5m
COLUMN,10,Specify_vpp5m
COLUMN,11,Mileage
COLUMN,12,Purch Department
COLUMN,13,Purch Salsman Code
COLUMN,14,Purch Date
COLUMN,15,Reception Date
COLUMN,16,Sales Department
COLUMN,17,Department Type Id_Dep
COLUMN,18,Description_Dep
COLUMN,19,Sale Salesman
COLUMN,20,Seller Code_vpp43
COLUMN,21,Sel Name_vpp43
COLUMN,22,Sel Family Name_vpp43
COLUMN,23,Sale Inv Number
COLUMN,24,Sale Date
COLUMN,25,Bud Sale Det
COLUMN,26,Bud Purch Price
COLUMN,27,Bud Reg Fee
COLUMN,28,Bud Deduct Fee
COLUMN,29,Bud Cost
COLUMN,30,Purch Price Unit
COLUMN,31,Cost Unit
COLUMN,32,Sales Price Unit
COLUMN,33,Reg Fee
COLUMN,34,Stock Days
COLUMN,35,Buying Order No
COLUMN,36,Buying Order Date
COLUMN,37,Pa Number
COLUMN,38,Location Code
COLUMN,39,Owner
COLUMN,40,Prev Owner
COLUMN,41,Account Purchase
COLUMN,42,Account Sales
COLUMN,43,Customer Number_Cust
COLUMN,44,Name_Cust
COLUMN,45,Customer Group_Cust
COLUMN,46,Customer Group_vpp48
COLUMN,47,Cust Group Specify_vpp48
COLUMN,48,Order Arrival Date
COLUMN,49,Order Confirm Date
COLUMN,50,Contract Date
COLUMN,51,Register Number
COLUMN,52,Chassis Number
COLUMN,53,Owner Code
COLUMN,54,First Reg Date
COLUMN,55,Latest Reg Date
COLUMN,56,Arrival Date
COLUMN,57,Salesman Buy
COLUMN,58,Salesman Sale
COLUMN,59,Model Text
COLUMN,60,Colour Cf
COLUMN,61,Model Line
COLUMN,62,Model Line_vpp5q
COLUMN,63,Mod Lin Specify_vpp5q
COLUMN,64,Workshop Model
COLUMN,65,Mileage
COLUMN,66,Motor Code
COLUMN,67,Drive Code
COLUMN,68,Colour Code
COLUMN,69,Make
COLUMN,70,Make Cd
COLUMN,71,Global Make Cd
COLUMN,72,Description_Global_Make
COLUMN,73,Orig Inv Date
COLUMN,74,Latest Inv Date
COLUMN,75,Invoice Date
COLUMN,76,Hauptbetrieb
COLUMN,77,Standort
COLUMN,78,Verkäufer
COLUMN,79,Fabrikat_ori
COLUMN,80,Model
COLUMN,81,Modellbez
COLUMN,82,Fahrgestellnr
COLUMN,83,Fahrzeugart
COLUMN,84,Fahrzeugtyp
COLUMN,85,Kunde
COLUMN,86,Kundenart
COLUMN,87,Geschäftsart
COLUMN,88,Farbe
COLUMN,89,ECC_Status_Text
COLUMN,90,FZG
COLUMN,91,FZG_1
COLUMN,92,FZG_Liste_1_gesamt
COLUMN,93,Sel Name_EK_vpp43
COLUMN,94,Einkäufer
COLUMN,95,Vorbesitzer
COLUMN,96,FZG_Liste_2_gesamt
COLUMN,97,FZG_Liste_3_gesamt
COLUMN,98,FZG_Liste_4_gesamt
COLUMN,99,Menge
COLUMN,100,Standtage
COLUMN,101,Mandant
COLUMN,102,Trans_code
COLUMN,103,Fabrikat
COLUMN,104,E Mail Address
COLUMN,105,E Mail Address 2
COLUMN,106,EMail_Adressen
COLUMN,107,FZG_Liste_4_mit_Mail
COLUMN,108,FZG_Liste_1
COLUMN,109,FZG_Liste_2
COLUMN,110,FZG_Liste_3
COLUMN,111,FZG_Liste_4
COLUMN,112,Phone 1
COLUMN,113,Phone 2
COLUMN,114,Telefon