C11/System/ARIntelligence/IQD/Zeit/fakt_Stunden_neu_2_Ryma.iqd

COGNOS QUERY
STRUCTURE,1,1
DATABASE,Navision2
DATASOURCENAME,C:\GAPS_BMW\Portal\System\IQD\Zeit\fakt_Stunden_neu_2_Ryma.imr
TITLE,fakt_Stunden_neu_2_Ryma.imr
BEGIN SQL
select T1."Entry No_" as c1,
	   T1."Corrected" as c2,
	   T1."Document No_" as c3,
	   T1."Document Date" as c4,
	   T1."Document Type" as c5,
	   T1."Service Document Type" as c6,
	   T1."Service Document No_" as c7,
	   T1."Service Document Line No_" as c8,
	   T1."Ass_ Serv_ Ledger Entry No_" as c9,
	   T1."Customer No_" as c10,
	   T1."Resource No_" as c11,
	   T1."Portion %" as c12,
	   T1."Rated Qty_ (Ind_ time)" as c13,
	   T1."Qty_ (Hour)" as c14,
	   T1."Clock In_Out Date" as c15,
	   T1."Service Job No_" as c16,
	   T1."No_" as c17,
	   T1."Make Code" as c18,
	   T1."VIN" as c19,
	   T1."Invoiced Time (Ind_ time)" as c20,
	   T1."Standard Time (Ind_ time)" as c21,
	   T1."Labor per Hour" as c22,
	   T1."Labor Split Rate" as c23,
	   T1."Standard Time (hrs_)" as c24,
	   T1."Standard Time labor" as c25,
	   T1."Efficiency % Position" as c26,
	   T1."Efficiency % Resource" as c27,
	   T1."Work Type" as c28,
	   T1."Invoiced Time Exported" as c29,
	   T1."Split Rate (hrs_)" as c30,
	   T1."Labor Standard Time Type" as c31,
	   (od_left(T1."Split Rate (hrs_)",1)) as c32,
	   CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) <> '-') THEN ((cast_float(T1."Invoiced Time (Ind_ time)"))) ELSE (0) END as c33,
	   (cast_float(T1."Labor Split Rate")) / (cast_float(T1."Labor per Hour")) as c34,
	   T1."Document Date" as c35,
	   '1' as c36,
	   CASE WHEN ((od_left(T2."Department No_",2)) = '10') THEN ('LBS') WHEN ((od_left(T2."Department No_",2)) = '20') THEN ('WLS') ELSE null END as c37,
	   T2."No_" as c38,
	   T2."Last Name" as c39,
	   T2."First Name" as c40,
	   T2."First Name" || ' ' || T2."Last Name" as c41,
	   T1."Service Document No_" as c42,
	   T2."Leaving Date" as c43,
	   T2."Group No_ 1" as c44,
	   T2."Group No_ 2" as c45,
	   T2."Group No_ 3" as c46,
	   T2."Group No_ 2" as c47,
	   (extract(DAY FROM (now()) - T2."Leaving Date")) as c48,
	   CASE WHEN ((((extract(DAY FROM (now()) - T2."Leaving Date"))) > 0) and (T2."Leaving Date" <> TIMESTAMP '1753-01-01 00:00:00.000')) THEN ('ausgetretene Mitarbeiter') ELSE (T2."Group No_ 2") END as c49,
	   CASE WHEN (T2."Group No_ 3" IN ('KAR','WER')) THEN ('prod. Personal') WHEN (T2."Group No_ 3" IN ('SON')) THEN ('unprod. Personal') ELSE null END as c50,
	   (cdatetime(T1."Document Date" - cinterval(extract(DAY FROM T1."Document Date") - 1))) as c51,
	   (cdatetime(lastday(cdate(T1."Document Date")))) as c52,
	   (now()) - INTERVAL '001 10:00:00.000' as c53,
	   CASE WHEN (((now()) - INTERVAL '001 10:00:00.000') BETWEEN ((cdatetime(T1."Document Date" - cinterval(extract(DAY FROM T1."Document Date") - 1)))) AND ((cdatetime(lastday(cdate(T1."Document Date")))))) THEN (T1."Document Date") ELSE null END as c54,
	   T2."Employment Date" as c55,
	   (cast_float(T1."Rated Qty_ (Ind_ time)")) as c56,
	   ((cast_float(T1."Rated Qty_ (Ind_ time)"))) * 12 as c57,
	   (cast_float(T1."Standard Time (Ind_ time)")) as c58,
	   CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END as c59,
	   (CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END) * 12 as c60,
	   CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END as c61,
	   (CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END) as c62,
	   T3."Quantity Disc_ %" as c63,
	   T3."Line Discount %" as c64,
	   T3."Line Discount Amount" as c65,
	   (cast_float(T3."Line Discount %")) / 100 as c66,
	   CASE WHEN (((cast_float(T3."Line Discount %")) / 100) = 0) THEN ((CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) <> '-') THEN ((cast_float(T1."Invoiced Time (Ind_ time)"))) ELSE (0) END)) ELSE (0) END as c67,
	   1 - ((cast_float(T3."Line Discount %")) / 100) as c68,
	   ((cast_float(T1."Invoiced Time (Ind_ time)"))) * (1 - ((cast_float(T3."Line Discount %")) / 100)) as c69,
	   (cast_float(T1."Invoiced Time (Ind_ time)")) as c70,
	   CASE WHEN (T1."Corrected" <> 1) THEN ((((cast_float(T1."Invoiced Time (Ind_ time)"))) * (1 - ((cast_float(T3."Line Discount %")) / 100)))) ELSE (0) END as c71,
	   CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) = '-') THEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END) * -1) ELSE ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END)) END as c72,
	   T2."Department No_" as c73,
	   CASE WHEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((((cast_float(T1."Invoiced Time (Ind_ time)"))) * (1 - ((cast_float(T3."Line Discount %")) / 100)))) ELSE (0) END) < 0) THEN ((CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) = '-') THEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END) * -1) ELSE ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END)) END) * -1) ELSE ((CASE WHEN (((od_left(T1."Split Rate (hrs_)",1))) = '-') THEN ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END) * -1) ELSE ((CASE WHEN (T1."Corrected" <> 1) THEN ((CASE WHEN ((((cast_float(T1."Standard Time (Ind_ time)"))) = 0) and (((cast_float(T1."Rated Qty_ (Ind_ time)"))) <> 0)) THEN (((cast_float(T1."Rated Qty_ (Ind_ time)")))) ELSE (((cast_float(T1."Standard Time (Ind_ time)")))) END)) ELSE (0) END)) END)) END as c74,
	   T4."Bill-to Name" as c75,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Document Date")) <= 63) THEN (T1."Document Date") ELSE null END as c76,
	   CASE WHEN ((extract(DAY FROM (now()) - T1."Document Date")) <= 63) THEN (T1."Service Document No_" || ' - ' || T4."Bill-to Name") ELSE null END as c77
from ((("DMS1"."dbo"."Automag GmbH$Clock In_Out Split LedgEntry" T1 left outer join "DMS1"."dbo"."Automag GmbH$Employee_T" T2 on T1."Resource No_" = T2."No_") left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Line" T3 on (T1."Service Document No_" = T3."Document No_") and (T1."Service Document Line No_" = T3."Line No_")) left outer join "DMS1"."dbo"."Automag GmbH$Archived Service Header" T4 on T1."Service Document No_" = T4."No_")
where ((T1."Document Date" >= T2."Employment Date") and (T1."Document Date" >= TIMESTAMP '2013-01-01 00:00:00.000'))

END SQL
COLUMN,0,Entry No
COLUMN,1,Corrected
COLUMN,2,Document No
COLUMN,3,Document Date
COLUMN,4,Document Type
COLUMN,5,Service Document Type
COLUMN,6,Service Document No
COLUMN,7,Service Document Line No
COLUMN,8,Ass Serv Ledger Entry No
COLUMN,9,Customer No
COLUMN,10,Resource No
COLUMN,11,Portion %
COLUMN,12,Rated Qty (ind Time)
COLUMN,13,Qty (hour)
COLUMN,14,Clock In Out Date
COLUMN,15,Service Job No
COLUMN,16,No
COLUMN,17,Make Code
COLUMN,18,Vin
COLUMN,19,Invoiced Time (ind Time)
COLUMN,20,Standard Time (ind Time)
COLUMN,21,Labor Per Hour
COLUMN,22,Labor Split Rate
COLUMN,23,Standard Time (hrs )
COLUMN,24,Standard Time Labor
COLUMN,25,Efficiency % Position
COLUMN,26,Efficiency % Resource
COLUMN,27,Work Type
COLUMN,28,Invoiced Time Exported
COLUMN,29,Split Rate (hrs )
COLUMN,30,Labor Standard Time Type
COLUMN,31,Stunde
COLUMN,32,fakt. Stunden_ori_falsch
COLUMN,33,Labor Split Rate_Zeit
COLUMN,34,Datum
COLUMN,35,Hauptbetrieb
COLUMN,36,Standort
COLUMN,37,No
COLUMN,38,Last Name
COLUMN,39,First Name
COLUMN,40,Monteur
COLUMN,41,Order Number
COLUMN,42,Leaving Date
COLUMN,43,Group No 1
COLUMN,44,Group No 2
COLUMN,45,Group No 3
COLUMN,46,Monteur_Gruppe_ori
COLUMN,47,Tage Heute Leaving Date
COLUMN,48,Monteur_Gruppe
COLUMN,49,produktiv/unproduktiv
COLUMN,50,Monatserster
COLUMN,51,Monatsletzter
COLUMN,52,Heute
COLUMN,53,Datum Tagesbericht
COLUMN,54,Employment Date
COLUMN,55,Rated Qty
COLUMN,56,Rated Qty in AW
COLUMN,57,Vorgabezeit
COLUMN,58,Vorgabezeit incl. AZ_Basis
COLUMN,59,Vorgabezeit incl. AZ in AW
COLUMN,60,Vorgabezeit incl. AZ_mit corrected <>1_ohne_Stunde-
COLUMN,61,Vorgabezeit incl. AZ
COLUMN,62,Quantity Disc %
COLUMN,63,Line Discount %
COLUMN,64,Line Discount Amount
COLUMN,65,Mengenrabatt
COLUMN,66,fakt. Stunden_falsch
COLUMN,67,Rabattberechnungszahl
COLUMN,68,fakt. Stunden_ohne_Corrected
COLUMN,69,fakt. Stunden_ori
COLUMN,70,fakt. Stunden
COLUMN,71,Vorgabezeit incl. AZ_mit corrected <>1
COLUMN,72,Department No
COLUMN,73,Vorgabezeit incl. AZ_mit corrected <>1_neu
COLUMN,74,Kunde
COLUMN,75,Datum_Monteurlisten
COLUMN,76,Auftrag_Kunde