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

COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21_SKR51_defi01
DATASOURCENAME,C:\GAPS\Portal\System\IQD\NW\NW_GW_VK_SKR51_defi01.imr
TITLE,NW_GW_VK_SKR51_defi01.imr
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,
	   (substring(T1."SALES_DEPARTMENT" from 2 for 1)) 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,
	   T12."UNIT_NUMBER" as c100,
	   T12."LINE_TYPE" as c101,
	   T12."LINE_NO" as c102,
	   T12."TRANSACT_DATE" as c103,
	   T12."HANDLER" as c104,
	   T12."PROGRAM" as c105,
	   T12."FUNCTION_CODE" as c106,
	   T12."DOCUMENT_DATE" as c107,
	   T12."DOCUMENT_NR" as c108,
	   T12."ORDER_DATE" as c109,
	   T12."ORDER_NUMBER" as c110,
	   T12."TRANSACTION_CODE" as c111,
	   T13."TRANSACTION_CODE" as c112,
	   T13."SPECIFY" as c113,
	   T12."REFERENCE" as c114,
	   T12."COSTS" as c115,
	   T12."ORDERS_GROSSVALUE" as c116,
	   T12."DISCOUNT" as c117,
	   T12."CLASSIFICATION_DTE" as c118,
	   T12."BOOK_KEEPING_CODE" as c119,
	   T12."DEPARTMENT" as c120,
	   T12."DESTINATION" as c121,
	   T12."INT_VOUCHER_NO" as c122,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('10','14','24')) THEN (T12."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c123,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('19','22','16')) THEN (T12."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c124,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('12')) THEN (T12."ORDERS_GROSSVALUE" * -1) ELSE (0) END as c125,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('11 ','17','15')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END as c126,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('13','90')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END as c127,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('25','18','27','28','29','26','23')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END as c128,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('30','31','32','33','34','35','36','37','38','39','46','47','48','49','40','41','44','45')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END as c129,
	   (CASE WHEN (T12."TRANSACTION_CODE" IN ('25','18','27','28','29','26','23')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END) + (CASE WHEN (T12."TRANSACTION_CODE" IN ('30','31','32','33','34','35','36','37','38','39','46','47','48','49','40','41','44','45')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END) as c130,
	   CASE WHEN (T12."TRANSACTION_CODE" IN ('18','27','28','29')) THEN (T12."ORDERS_GROSSVALUE") ELSE (0) END as c131,
	   0 as c132,
	   (database()) as c133,
	   (rtrim(T12."TRANSACTION_CODE")) || ' - ' || T13."SPECIFY" as c134,
	   CASE WHEN (T10."DESCRIPTION" IN ('Fiat','Jeep','Abarth')) THEN (T10."DESCRIPTION") ELSE ('Fremdfabrikat') END as c135,
	   T6."E_MAIL_ADDRESS" as c136,
	   T6."E_MAIL_ADDRESS_2" as c137,
	   (((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 c138,
	   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 c139,
	   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 c140,
	   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 c141,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."SALE_DATE")) <= 60) THEN (T8."MODEL_TEXT") ELSE ('Summe Fzg älter 60 Tage') END as c142,
	   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 c143,
	   T6."PHONE_1" as c144,
	   T6."PHONE_2" as c145,
	   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 c146,
	   T10."SKR51_MAKE_CD" as c147,
	   T10."BRAND_CODE" as c148,
	   (od_left(T1."SALES_DEPARTMENT",1)) as c149,
	   (substring(T1."SALES_DEPARTMENT" from 3 for 1)) as c150,
	   (substring(T1."SALES_DEPARTMENT" from 3 for 2)) as c151
from (((((((((((("defi1140"."dbo"."UNIT_FILE" T1 left outer join "defi1140"."dbo"."vPP5R" T2 on T1."VEHICLE_TYPE" = T2."VEHICLE_TYPE") left outer join "defi1140"."dbo"."vPP5M" T3 on T1."BOOK_KEEPING_CODE" = T3."BOOK_KEEPING_CODE") left outer join "defi1140"."dbo"."DEPARTMENT_TYPE" T4 on T1."SALES_DEPARTMENT" = T4."DEPARTMENT_TYPE_ID") left outer join "defi1140"."dbo"."vPP43" T5 on T1."SALE_SALESMAN" = T5."SELLER_CODE") left outer join "defi1140"."dbo"."CUSTOMER" T6 on T1."ACCOUNT_SALES" = T6."CUSTOMER_NUMBER") left outer join "defi1140"."dbo"."vPP48" T7 on T6."CUSTOMER_GROUP" = T7."CUSTOMER_GROUP") left outer join "defi1140"."dbo"."VEHICLE" T8 on T1."BASIS_NUMBER" = T8."BASIS_NUMBER") left outer join "defi1140"."dbo"."vPP5Q" T9 on (T8."MODEL_LINE" = T9."MODEL_LINE") and (T8."MAKE_CD" = T9."MAKE_CD")) left outer join "defi1140"."dbo"."GLOBAL_MAKE" T10 on T8."MAKE_CD" = T10."GLOBAL_MAKE_CD") left outer join "defi1140"."dbo"."vPP43" T11 on T1."PURCH_SALSMAN_CODE" = T11."SELLER_CODE") left outer join "defi1140"."dbo"."UNIT_HISTORY" T12 on T1."UNIT_NUMBER" = T12."UNIT_NUMBER") left outer join "defi1140"."dbo"."vPP5A" T13 on T12."TRANSACTION_CODE" = T13."TRANSACTION_CODE")
where ((T1."ECC_STATUS" = '64') and (not T12."TRANSACTION_CODE" IN ('76 ','77 ')))
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,Unit Number_Unit_Hist
COLUMN,100,Line Type
COLUMN,101,Line No
COLUMN,102,Transact Date
COLUMN,103,Handler
COLUMN,104,Program
COLUMN,105,Function Code
COLUMN,106,Document Date
COLUMN,107,Document Nr
COLUMN,108,Order Date
COLUMN,109,Order Number
COLUMN,110,Transaction Code
COLUMN,111,Transaction Code_vpp5a
COLUMN,112,Specify_vpp5a
COLUMN,113,Reference
COLUMN,114,Costs
COLUMN,115,Orders Grossvalue
COLUMN,116,Discount
COLUMN,117,Classification Dte
COLUMN,118,Book Keeping Code
COLUMN,119,Department
COLUMN,120,Destination
COLUMN,121,Int Voucher No
COLUMN,122,Erlös
COLUMN,123,Sonst. Erlöse
COLUMN,124,Ums. Fracht und Überf.
COLUMN,125,Nachlass
COLUMN,126,Provisionen
COLUMN,127,EK Fahrzeug
COLUMN,128,FZG-Kosten
COLUMN,129,Einsatz
COLUMN,130,VK_Hilfen
COLUMN,131,Standtage
COLUMN,132,Mandant
COLUMN,133,Trans_code
COLUMN,134,Fabrikat
COLUMN,135,E Mail Address
COLUMN,136,E Mail Address 2
COLUMN,137,FZG_Liste_4_mit_Mail
COLUMN,138,EMail_Adressen
COLUMN,139,FZG_Liste_1
COLUMN,140,FZG_Liste_2
COLUMN,141,FZG_Liste_3
COLUMN,142,FZG_Liste_4
COLUMN,143,Phone 1
COLUMN,144,Phone 2
COLUMN,145,Telefon
COLUMN,146,Skr51 Make Cd
COLUMN,147,Brand Code
COLUMN,148,Marke
COLUMN,149,Kostenstelle_1
COLUMN,150,Kostenstelle