gctools/cognos7/data/mdl/S_Aftersales_queries.sql

CREATE
	OR

ALTER VIEW [load].[current_date_Prognose_operativ]
AS
SELECT --[Bundeslaender Id] AS [Bundeslaender Id],
	[Invoice Date] AS [Invoice Date],
	--[Wochentage Id] AS [Wochentage Id],
	[Arbeitstag Mofr] AS [Arbeitstag Mofr],
	--[Zaehler Mofr] AS [Zaehler Mofr],
	--[Summe Mofr] AS [Summe Mofr],
	--[Arbeitstag Mosa] AS [Arbeitstag Mosa],
	--[Zaehler Mosa] AS [Zaehler Mosa],
	--[Summe Mosa] AS [Summe Mosa],
	--[Feiertage Id] AS [Feiertage Id],
	--[Jahr] AS [Jahr],
	--[Arbeitstag Nr Jahr] AS [Arbeitstag Nr Jahr],
	--[Gesamt Arbeitstage] AS [Gesamt Arbeitstage] 
FROM [export_csv].[current_date_Prognose_operativ]
GO

CREATE
	OR

ALTER VIEW [load].[current_date_Prognose_operativ_VJ]
AS
SELECT --[Bundeslaender Id] AS [Bundeslaender Id],
	[Invoice Date] AS [Invoice Date],
	--[Wochentage Id] AS [Wochentage Id],
	[Arbeitstag Mofr] AS [Arbeitstag Mofr],
	--[Zaehler Mofr] AS [Zaehler Mofr],
	--[Summe Mofr] AS [Summe Mofr],
	--[Arbeitstag Mosa] AS [Arbeitstag Mosa],
	--[Zaehler Mosa] AS [Zaehler Mosa],
	--[Summe Mosa] AS [Summe Mosa],
	--[Feiertage Id] AS [Feiertage Id],
	--[Jahr] AS [Jahr],
	--[Arbeitstag Nr Jahr] AS [Arbeitstag Nr Jahr],
	--[Gesamt Arbeitstage] AS [Gesamt Arbeitstage] 
FROM [export_csv].[current_date_Prognose_operativ_VJ]
GO

CREATE
	OR

ALTER VIEW [load].[Aftersales_Rechnungen_ben_AW_final]
AS
SELECT [Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	[Hauptbetrieb_Name] AS [Hauptbetrieb_Name],
	[Standort_ID] AS [Standort_ID],
	[Standort_Name] AS [Standort_Name],
	[Employee_Function] AS [Employee_Function],
	[Serviceberater] AS [Serviceberater],
	[Order Number] AS [Order Number],
	[Invoice_Desc_100] AS [Invoice_Desc_100],
	[Fabrikat] AS [Fabrikat],
	[Model] AS [Model],
	[Fahrzeug] AS [Fahrzeug],
	[Kostenstelle] AS [Kostenstelle],
	--[Marke] AS [Marke],
	[Umsatzart] AS [Umsatzart],
	[Auftragsart] AS [Auftragsart],
	[Auftragsposition] AS [Auftragsposition],
	[Kundenart] AS [Kundenart],
	[Kunde] AS [Kunde],
	--[Order_Desc_30] AS [Order_Desc_30],
	[Invoice_Desc_30] AS [Invoice_Desc_30],
	[Zuordnung_Produktbuchungsgruppe] AS [Zuordnung_Produktbuchungsgruppe],
	[Produktbuchungsgruppe] AS [Produktbuchungsgruppe],
	[FZG-Altersstaffel] AS [FZG-Altersstaffel],
	[Repair_Group_Desc] AS [Repair_Group_Desc],
	[DB1_><_EK] AS [DB1_><_EK],
	[Rechnung_Gutschrift] AS [Rechnung_Gutschrift],
	[Parts_Focus_Group] AS [Parts_Focus_Group],
	[Parts_Make_Desc] AS [Parts_Make_Desc],
	[Parts_Group_Desc] AS [Parts_Group_Desc],
	[PLZ_1_Stelle] AS [PLZ_1_Stelle],
	[PLZ_2_Stelle] AS [PLZ_2_Stelle],
	[PLZ_3_Stelle] AS [PLZ_3_Stelle],
	[PLZ_4_Stelle] AS [PLZ_4_Stelle],
	[PLZ] AS [PLZ],
	[Zuordnung_Funktion] AS [Zuordnung_Funktion],
	[Order_Desc_100] AS [Order_Desc_100],
	[Lohn] AS [Lohn_Umsatz],
	[Teile] AS [Teile_Umsatz],
	[EW Teile] AS [EW Teile],
	[Sonst.] AS [Sonst.],
	[TÜV] AS [TÜV],
	[Fremdl.] AS [Fremdl.],
	[Mietw.] AS [Mietw.],
	[Rädereinl.] AS [Rädereinl.],
	[verk. Std.] AS [verk. Std.],
	[ben. Std.] AS [ben. Std.],
	[NL Lohn] AS [NL Lohn],
	[NL Teile] AS [NL Teile],
	[NL Sonst.] AS [NL Sonst.],
	[DG] AS [DG],
	[NL Sonst %] AS [NL Sonst %],
	--[Anz. AT] AS [Anz. AT],
	[Anwesenheit Mech Karo Lack] AS [Anwesenheit Mech Karo Lack],
	[Anwesenheit Meister] AS [Anwesenheit Meister],
	[Invoice Date] AS [Invoice Date],
	[Rechnungsausgang] AS [Rechnungsausgang],
	[Fabrikat_Order_By] AS [Fabrikat_Order_By],
	[Serviceberater_Rg_Steller] AS [Serviceberater_Rg_Steller] 
FROM [export_csv].[Aftersales_Rechnungen_ben_AW_final]
GO

CREATE
	OR

ALTER VIEW [load].[Aftersales_Rechnungen_neu]
AS
SELECT [Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	[Hauptbetrieb_Name] AS [Hauptbetrieb_Name],
	[Standort_ID] AS [Standort_ID],
	[Standort_Name] AS [Standort_Name],
	[Employee_Function] AS [Employee_Function],
	[Serviceberater] AS [Serviceberater],
	[Order Number] AS [Order Number],
	[Invoice_Desc_100] AS [Invoice_Desc_100],
	[Fabrikat] AS [Fabrikat],
	[Fabrikat_Order_By] AS [Fabrikat_Order_By],
	[Model] AS [Model],
	[Fahrzeug] AS [Fahrzeug],
	[Kostenstelle] AS [Kostenstelle],
	--[Marke] AS [Marke],
	[Umsatzart] AS [Umsatzart],
	[Auftragsart] AS [Auftragsart],
	[Auftragsposition] AS [Auftragsposition],
	[Kundenart] AS [Kundenart],
	[Kunde] AS [Kunde],
	--[Order_Desc_30] AS [Order_Desc_30],
	[Invoice_Desc_30] AS [Invoice_Desc_30],
	[Zuordnung_Produktbuchungsgruppe] AS [Zuordnung_Produktbuchungsgruppe],
	[Produktbuchungsgruppe] AS [Produktbuchungsgruppe],
	[FZG-Altersstaffel] AS [FZG-Altersstaffel],
	[Repair_Group_Desc] AS [Repair_Group_Desc],
	[DB1_><_EK] AS [DB1_><_EK],
	[Rechnung_Gutschrift] AS [Rechnung_Gutschrift],
	[Parts_Focus_Group] AS [Parts_Focus_Group],
	[Parts_Make_Desc] AS [Parts_Make_Desc],
	[Parts_Group_Desc] AS [Parts_Group_Desc],
	[PLZ_1_Stelle] AS [PLZ_1_Stelle],
	[PLZ_2_Stelle] AS [PLZ_2_Stelle],
	[PLZ_3_Stelle] AS [PLZ_3_Stelle],
	[PLZ_4_Stelle] AS [PLZ_4_Stelle],
	[PLZ] AS [PLZ],
	[Zuordnung_Funktion] AS [Zuordnung_Funktion],
	[Order_Desc_100] AS [Order_Desc_100],
	[Lohn] AS [Lohn_Umsatz],
	[Teile] AS [Teile_Umsatz],
	[EW Teile] AS [EW Teile],
	[Sonst.] AS [Sonst.],
	[TÜV] AS [TÜV],
	[Fremdl.] AS [Fremdl.],
	[Mietw.] AS [Mietw.],
	[Rädereinl.] AS [Rädereinl.],
	[verk. Std.] AS [verk. Std.],
	[ben. Std.] AS [ben. Std.],
	[NL Lohn] AS [NL Lohn],
	[NL Teile] AS [NL Teile],
	[NL Sonst.] AS [NL Sonst.],
	[DG] AS [DG],
	[NL Sonst %] AS [NL Sonst %],
	--[Anz. AT] AS [Anz. AT],
	[Anwesenheit Mech Karo Lack] AS [Anwesenheit Mech Karo Lack],
	[Anwesenheit Meister] AS [Anwesenheit Meister],
	[Invoice Date] AS [Invoice Date],
	[Rechnungsausgang] AS [Rechnungsausgang],
	[EW Fremdl.] AS [EW Fremdl.],
	[Serviceberater_Rg_Steller] AS [Serviceberater_Rg_Steller] 
FROM [export_csv].[Aftersales_Rechnungen_neu]
GO

CREATE
	OR

ALTER VIEW [load].[Aftersales_Rechnungen_verk_AW_final]
AS
SELECT [Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	[Hauptbetrieb_Name] AS [Hauptbetrieb_Name],
	[Standort_ID] AS [Standort_ID],
	[Standort_Name] AS [Standort_Name],
	[Employee_Function] AS [Employee_Function],
	[Serviceberater] AS [Serviceberater],
	[Order Number] AS [Order Number],
	[Invoice_Desc_100] AS [Invoice_Desc_100],
	[Fabrikat] AS [Fabrikat],
	[Model] AS [Model],
	[Fahrzeug] AS [Fahrzeug],
	[Kostenstelle] AS [Kostenstelle],
	--[Marke] AS [Marke],
	[Umsatzart] AS [Umsatzart],
	[Auftragsart] AS [Auftragsart],
	[Auftragsposition] AS [Auftragsposition],
	[Kundenart] AS [Kundenart],
	[Kunde] AS [Kunde],
	--[Order_Desc_30] AS [Order_Desc_30],
	[Invoice_Desc_30] AS [Invoice_Desc_30],
	[Zuordnung_Produktbuchungsgruppe] AS [Zuordnung_Produktbuchungsgruppe],
	[Produktbuchungsgruppe] AS [Produktbuchungsgruppe],
	[FZG-Altersstaffel] AS [FZG-Altersstaffel],
	[Repair_Group_Desc] AS [Repair_Group_Desc],
	[DB1_><_EK] AS [DB1_><_EK],
	[Rechnung_Gutschrift] AS [Rechnung_Gutschrift],
	[Parts_Focus_Group] AS [Parts_Focus_Group],
	[Parts_Make_Desc] AS [Parts_Make_Desc],
	[Parts_Group_Desc] AS [Parts_Group_Desc],
	[PLZ_1_Stelle] AS [PLZ_1_Stelle],
	[PLZ_2_Stelle] AS [PLZ_2_Stelle],
	[PLZ_3_Stelle] AS [PLZ_3_Stelle],
	[PLZ_4_Stelle] AS [PLZ_4_Stelle],
	[PLZ] AS [PLZ],
	[Zuordnung_Funktion] AS [Zuordnung_Funktion],
	[Order_Desc_100] AS [Order_Desc_100],
	[Lohn] AS [Lohn_Umsatz],
	[Teile] AS [Teile_Umsatz],
	[EW Teile] AS [EW Teile],
	[Sonst.] AS [Sonst.],
	[TÜV] AS [TÜV],
	[Fremdl.] AS [Fremdl.],
	[Mietw.] AS [Mietw.],
	[Rädereinl.] AS [Rädereinl.],
	[verk. Std.] AS [verk. Std.],
	[ben. Std.] AS [ben. Std.],
	[NL Lohn] AS [NL Lohn],
	[NL Teile] AS [NL Teile],
	[NL Sonst.] AS [NL Sonst.],
	[DG] AS [DG],
	[NL Sonst %] AS [NL Sonst %],
	--[Anz. AT] AS [Anz. AT],
	[Anwesenheit Mech Karo Lack] AS [Anwesenheit Mech Karo Lack],
	[Anwesenheit Meister] AS [Anwesenheit Meister],
	[Invoice Date] AS [Invoice Date],
	[Rechnungsausgang] AS [Rechnungsausgang],
	[Fabrikat_Order_By] AS [Fabrikat_Order_By],
	[Serviceberater_Rg_Steller] AS [Serviceberater_Rg_Steller] 
FROM [export_csv].[Aftersales_Rechnungen_verk_AW_final]
GO