C11/System/ARIntelligence/IQD/Service/Service_offene_Auftraege_schon_mal_abgerechnet.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\gaps_bmw\Portal\System\IQD\Service\Service_offene_Auftraege_schon_mal_abgerechnet.imr
TITLE,Service_offene_Auftraege_schon_mal_abgerechnet.imr
BEGIN SQL
select T1."Document Type" as c1,
	   T1."No_" as c2,
	   T1."Sell-to Customer No_" as c3,
	   T1."Bill-to Customer No_" as c4,
	   T1."Bill-to Name" as c5,
	   T1."Order Date" as c6,
	   T1."Posting Date" as c7,
	   T1."Posting Description" as c8,
	   T1."Due Date" as c9,
	   T1."Location Code" as c10,
	   T1."Department Code" as c11,
	   T1."Make Code" as c12,
	   T1."Salesperson Code" as c13,
	   T1."On Hold" as c14,
	   T1."Transaction Type" as c15,
	   T1."Transport Method" as c16,
	   T1."Correction" as c17,
	   T1."Document Date" as c18,
	   T1."Area" as c19,
	   T1."Reserve" as c20,
	   T1."Option Code" as c21,
	   T1."Service Posting Group" as c22,
	   T1."VIN" as c23,
	   T1."Model Code" as c24,
	   T1."Model No_" as c25,
	   T1."Model" as c26,
	   T1."Type" as c27,
	   T1."Initial Registration" as c28,
	   T1."Order Limit" as c29,
	   T1."Time of Order" as c30,
	   T1."Pickup Date" as c31,
	   T1."Pickup Time" as c32,
	   T1."Completion Date" as c33,
	   T1."Completion Time" as c34,
	   T1."Labor Type" as c35,
	   T1."Labor Charging Code" as c36,
	   T1."Book No_" as c37,
	   T1."Branch Book No_" as c38,
	   T1."Sales Department Code" as c39,
	   T1."Fixed Date" as c40,
	   T1."Fixed Instruction" as c41,
	   T1."Current Instruction Type" as c42,
	   T1."Service Advisor No__Scheduling" as c43,
	   T1."Work Completed" as c44,
	   T1."Status Code" as c45,
	   T1."Vehicle Status" as c46,
	   T1."Service Advisor No_" as c47,
	   T2."Document No_" as c48,
	   T2."Line No_" as c49,
	   T2."No_" as c50,
	   T2."Order Date" as c51,
	   T2."Description" as c52,
	   T2."Quantity" as c53,
	   T2."Outstanding Quantity" as c54,
	   T2."Qty_ to Invoice" as c55,
	   T2."Unit Price" as c56,
	   T2."Unit Cost (LCY)" as c57,
	   T2."Amount" as c58,
	   T2."Unit Cost" as c59,
	   T2."Book No_" as c60,
	   T2."Planned" as c61,
	   T2."Quantity (Base)" as c62,
	   T2."Outstanding Qty_ (Base)" as c63,
	   T2."Standard Time" as c64,
	   T2."Qty_ per Hour" as c65,
	   T2."Qty_ (Hour)" as c66,
	   T2."Outstanding Qty_ (Hour)" as c67,
	   T2."Qty_ Rcd_ Not Invoiced (Hour)" as c68,
	   T2."Service Posting Group" as c69,
	   T2."Fixed Price" as c70,
	   T2."Standard Time Type" as c71,
	   T2."Post with Book No_" as c72,
	   T2."Line Status" as c73,
	   T2."Labor Standard Time Type" as c74,
	   '1' as c75,
	   (od_left(T1."Department Code",2)) as c76,
	   CASE WHEN (T1."Location Code" = 'LBS') THEN ('10') WHEN (T1."Location Code" = 'WLS') THEN ('20') WHEN (T1."Location Code" = 'STA') THEN ('30') WHEN (T1."Location Code" = 'GER') THEN ('40') WHEN (T1."Location Code" = 'LL') THEN ('50') ELSE null END as c77,
	   T3."No_" as c78,
	   T3."First Name" as c79,
	   T3."Last Name" as c80,
	   T3."First Name" || ' ' || T3."Last Name" as c81,
	   CASE WHEN (T4."Source No_ (Payment)" LIKE 'INT%') THEN ('Intern') WHEN (T4."Source No_ (Payment)" LIKE 'GARA%') THEN ('GWL') ELSE ('Extern') END as c82,
	   T1."Bill-to Customer No_" || ' - ' || T1."Bill-to Name" as c83,
	   T1."No_" || ' / ' || T4."Document No_" || ' - ' || T1."Bill-to Name" as c84,
	   (cast_float(T2."Quantity")) as c85,
	   (cast_float(T2."Unit Price")) as c86,
	   (cast_float(T2."Unit Cost")) as c87,
	   CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END as c88,
	   T2."Type" as c89,
	   (cast_float(T2."Outstanding Quantity")) as c90,
	   CASE WHEN ((T2."Type" = 3) and (not (od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('8','9'))) THEN ((CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END)) ELSE (0) END as c91,
	   CASE WHEN ((T2."Type" = 2) and (not (od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('6'))) THEN ((CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END)) WHEN ((T2."Type" = 3) and ((od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('9'))) THEN ((CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END)) ELSE (0) END as c92,
	   T4."Order No_" as c93,
	   T4."Posting Date" as c94,
	   T4."Amt_ to Post to G_L" as c95,
	   T4."Amt_ Posted to G_L" as c96,
	   T4."Entry Type" as c97,
	   T4."Positive" as c98,
	   T4."Document No_" as c99,
	   T2."Gen_ Prod_ Posting Group" as c100,
	   CASE WHEN (T2."Type" = 4) THEN ((CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END)) WHEN ((T2."Type" = 3) and ((od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('8'))) THEN ((CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END)) WHEN ((T2."Type" = 2) and ((od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('6'))) THEN ((CASE WHEN (T4."Positive" = 1) THEN ((cast_float(T2."Amount" * -1))) ELSE ((cast_float(T2."Amount"))) END)) ELSE (0) END as c101,
	   T4."Posting Date" as c102,
	   CASE WHEN (((T2."Type" = 3) and (not (od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('8','9'))) and (T4."Positive" = 1)) THEN ((cast_float(T2."Qty_ (Hour)")) * -1) WHEN (((T2."Type" = 3) and (not (od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('8','9'))) and (T4."Positive" = 0)) THEN ((cast_float(T2."Qty_ (Hour)"))) ELSE (0) END as c103,
	   (CASE WHEN (((T2."Type" = 3) and (not (od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('8','9'))) and (T4."Positive" = 1)) THEN ((cast_float(T2."Qty_ (Hour)")) * -1) WHEN (((T2."Type" = 3) and (not (od_left(T2."Gen_ Prod_ Posting Group",1)) IN ('8','9'))) and (T4."Positive" = 0)) THEN ((cast_float(T2."Qty_ (Hour)"))) ELSE (0) END) * (cast_float(T2."Qty_ per Hour")) as c104,
	   T4."Source No_ (Payment)" as c105,
	   CASE WHEN (T1."Make Code" IN ('BMW','BMW-MINI')) THEN (T1."Make Code") ELSE ('Fremdfabrikat') END as c106,
	   T1."VIN" || ' - ' || T1."Model" as c107,
	   CASE WHEN (T1."Make Code" IN ('BMW-MINI')) THEN (T1."Make Code") ELSE ('BMW') END as c108,
	   T1."Service Posting Group" as c109,
	   CASE WHEN (T5."No_" LIKE 'INT%') THEN ('Intern') ELSE (T6."Description") END as c110,
	   '' as c111,
	   '' as c112,
	   T1."Gen_ Prod_ Posting Group" as c113,
	   '' as c114,
	   '' as c115,
	   T7."Resource Group No_" as c116,
	   CASE WHEN (T7."Resource Group No_" = 'SB') THEN ('SB') ELSE ('Rest') END as c117
from (((("DMS1"."dbo"."Automag GmbH$Service Header" T1 left outer join "DMS1"."dbo"."Automag GmbH$Employee" T3 on T3."No_" = T1."Service Advisor No_") left outer join "DMS1"."dbo"."Automag GmbH$Customer" T5 on T5."No_" = T1."Sell-to Customer No_") left outer join "DMS1"."dbo"."Automag GmbH$Customer Group" T6 on T5."Customer Group Code" = T6."Code") left outer join "DMS1"."dbo"."Automag GmbH$Resource" T7 on T1."Service Advisor No_" = T7."No_"),
	("DMS1"."dbo"."Automag GmbH$Service Line" T2 left outer join "DMS1"."dbo"."Automag GmbH$Service Ledger Entry" T4 on (T2."Document No_" = T4."Order No_") and (T2."Line No_" = T4."Order Line No_"))
where (T1."No_" = T2."Document No_")
and (((T1."Document Type" <> 0) and (T4."Entry Type" = 1)) and (not T4."No_" IN ('TÜV_EXT','TÜV_KENNZEICHEN','TÜV_NACHU_EXT','DEKRA-EXT','DEKRA_NACHU_EXT')))
order by c84 asc,c2 asc

END SQL
COLUMN,0,Document Type
COLUMN,1,No
COLUMN,2,Sell-to Customer No
COLUMN,3,Bill-to Customer No
COLUMN,4,Bill-to Name
COLUMN,5,Order Date
COLUMN,6,Posting Date
COLUMN,7,Posting Description
COLUMN,8,Due Date
COLUMN,9,Location Code
COLUMN,10,Department Code
COLUMN,11,Make Code
COLUMN,12,Salesperson Code
COLUMN,13,On Hold
COLUMN,14,Transaction Type
COLUMN,15,Transport Method
COLUMN,16,Correction
COLUMN,17,Document Date
COLUMN,18,Area
COLUMN,19,Reserve
COLUMN,20,Option Code
COLUMN,21,Service Posting Group
COLUMN,22,Vin
COLUMN,23,Model Code
COLUMN,24,Model No
COLUMN,25,Model
COLUMN,26,Type_FZG
COLUMN,27,Initial Registration
COLUMN,28,Order Limit
COLUMN,29,Time Of Order
COLUMN,30,Pickup Date
COLUMN,31,Pickup Time
COLUMN,32,Completion Date
COLUMN,33,Completion Time
COLUMN,34,Labor Type
COLUMN,35,Labor Charging Code
COLUMN,36,Book No
COLUMN,37,Branch Book No
COLUMN,38,Sales Department Code
COLUMN,39,Fixed Date
COLUMN,40,Fixed Instruction
COLUMN,41,Current Instruction Type
COLUMN,42,Service Advisor No Scheduling
COLUMN,43,Work Completed
COLUMN,44,Status Code
COLUMN,45,Vehicle Status
COLUMN,46,Service Advisor No
COLUMN,47,Document No
COLUMN,48,Line No
COLUMN,49,No
COLUMN,50,Order Date
COLUMN,51,Description
COLUMN,52,Quantity
COLUMN,53,Outstanding Quantity
COLUMN,54,Qty To Invoice
COLUMN,55,Unit Price
COLUMN,56,Unit Cost (lcy)
COLUMN,57,Amount
COLUMN,58,Unit Cost
COLUMN,59,Book No
COLUMN,60,Planned
COLUMN,61,Quantity (base)
COLUMN,62,Outstanding Qty (base)
COLUMN,63,Standard Time
COLUMN,64,Qty Per Hour
COLUMN,65,Qty (hour)
COLUMN,66,Outstanding Qty (hour)
COLUMN,67,Qty Rcd Not Invoiced (hour)
COLUMN,68,Service Posting Group
COLUMN,69,Fixed Price
COLUMN,70,Standard Time Type
COLUMN,71,Post With Book No
COLUMN,72,Line Status
COLUMN,73,Labor Standard Time Type
COLUMN,74,Hauptbetrieb
COLUMN,75,Standort_Department
COLUMN,76,Standort
COLUMN,77,No_Employee
COLUMN,78,First Name_Employee
COLUMN,79,Last Name_Employee
COLUMN,80,Serviceberater
COLUMN,81,Umsatzart
COLUMN,82,Kunde
COLUMN,83,Order Number
COLUMN,84,Menge
COLUMN,85,Stückpreis
COLUMN,86,Stückkosten
COLUMN,87,Betrag
COLUMN,88,Type
COLUMN,89,ausstehende Menge
COLUMN,90,Umsatz Lohn
COLUMN,91,Umsatz Teile Service
COLUMN,92,Order No_Serv_ledge_entry
COLUMN,93,Posting Date_serv_ledge_entry
COLUMN,94,Amt To Post To G L
COLUMN,95,Amt Posted To G L
COLUMN,96,Entry Type
COLUMN,97,Positive
COLUMN,98,Document No_Serv_ledge_Entry
COLUMN,99,Gen Prod Posting Group
COLUMN,100,Umsatz Sonstiges
COLUMN,101,Invoice Date
COLUMN,102,verk. Stunden
COLUMN,103,verk. AW
COLUMN,104,Source No (payment)
COLUMN,105,Fabrikat
COLUMN,106,Fahrzeug
COLUMN,107,Marke
COLUMN,108,Auftragsart
COLUMN,109,Kundenart
COLUMN,110,Function Code
COLUMN,111,Monteur
COLUMN,112,Auftragsart_1
COLUMN,113,Order Number_Rg_Ausg
COLUMN,114,Arbeitsgang
COLUMN,115,Resource Group No
COLUMN,116,SB_Gruppe