C11/System/ARI/Models/mdl/Z_Monteure_queries.sql

CREATE
	OR

ALTER VIEW [load].[Zuordnung_Task_Type~1]
AS
SELECT --[Code] AS [Code],
	--[Description] AS [Description],
	[Activity_Desc] AS [Activity_Desc],
	[Activity_Codes_Group1] AS [Activity_Codes_Group1],
	[Activity_Codes_Group2] AS [Activity_Codes_Group2] 
FROM [export_csv].[Zuordnung_Task_Type~1]
GO

CREATE
	OR

ALTER VIEW [load].[Zuordnung_Time_Account_Abw~1]
AS
SELECT --[No_] AS [No_],
	--[Description] AS [Description],
	[Activity_Desc] AS [Activity_Desc],
	[Activity_Codes_Group1] AS [Activity_Codes_Group1],
	[Activity_Codes_Group2] AS [Activity_Codes_Group2] 
FROM [export_csv].[Zuordnung_Time_Account_Abw~1]
GO

CREATE
	OR

ALTER VIEW [load].[current_date_Prognose]
AS
SELECT --[Bundeslaender Id] AS [Bundeslaender Id],
	[Datum] AS [Datum],
	--[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]
GO

CREATE
	OR

ALTER VIEW [load].[current_date_Prognose_VJ]
AS
SELECT --[Bundeslaender Id] AS [Bundeslaender Id],
	[Datum] AS [Datum],
	--[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_VJ]
GO

CREATE
	OR

ALTER VIEW [load].[Time_Clock_Entry]
AS
SELECT --[Entry No] AS [Entry No],
	--[Employee No] AS [Employee No],
	--[Resource No] AS [Resource No],
	--[Address No] AS [Address No],
	--[Date] AS [Date],
	--[Time] AS [Time],
	--[Sorting] AS [Sorting],
	--[Duration] AS [Duration],
	--[Task Type Code] AS [Task Type Code],
	--[Type] AS [Type],
	--[Linked To Entry No] AS [Linked To Entry No],
	--[Leaving] AS [Leaving],
	--[Service Order No] AS [Service Order No],
	--[Service Job No] AS [Service Job No],
	--[Service Line No] AS [Service Line No],
	--[Labor Standard Time Type] AS [Labor Standard Time Type],
	--[Approved] AS [Approved],
	--[Approved By Employee No] AS [Approved By Employee No],
	--[Approval Timestamp] AS [Approval Timestamp],
	--[Canceled] AS [Canceled],
	--[Canceled By User Id] AS [Canceled By User Id],
	--[Cancelation Timestamp] AS [Cancelation Timestamp],
	--[Created By User Id] AS [Created By User Id],
	--[Creation Timestamp] AS [Creation Timestamp],
	--[Productive] AS [Productive],
	--[Statistics Group] AS [Statistics Group],
	--[Considered As Working] AS [Considered As Working],
	--[Order Location Code] AS [Order Location Code],
	--[Resource Location Code] AS [Resource Location Code],
	--[Make Code] AS [Make Code],
	--[Branch Code] AS [Branch Code],
	--[Labor No] AS [Labor No],
	--[Description] AS [Description],
	--[Source Code] AS [Source Code],
	--[Efficiency] AS [Efficiency],
	--[Automatic] AS [Automatic],
	--[Passed] AS [Passed],
	--[Code_Task_Type] AS [Code_Task_Type],
	--[Description_Task_Type] AS [Description_Task_Type],
	--[No_Employee_T] AS [No_Employee_T],
	--[Name_Employee_T] AS [Name_Employee_T],
	--[Last Name_Employee_T] AS [Last Name_Employee_T],
	--[First Name_Employee_T] AS [First Name_Employee_T],
	--[Group No 1_Employee_T] AS [Group No 1_Employee_T],
	--[Group No 2_Employee_T] AS [Group No 2_Employee_T],
	--[Group No 3_Employee_T] AS [Group No 3_Employee_T],
	--[Leaving Date_Employee_T] AS [Leaving Date_Employee_T],
	--[produktiv/unproduktiv] AS [produktiv/unproduktiv],
	[Monteur] AS [Monteur],
	--[Monteur_Gruppe_ori] AS [Monteur_Gruppe_ori],
	--[Tage Heute Leaving Date] AS [Tage Heute Leaving Date],
	[Monteur_Gruppe_2] AS [Monteur_Gruppe_2],
	[Datum] AS [Datum],
	--[Monatserster] AS [Monatserster],
	--[Monatsletzter] AS [Monatsletzter],
	--[Heute] AS [Heute],
	--[Datum Tagesbericht] AS [Datum Tagesbericht],
	[Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	--[Standort] AS [Standort],
	[prod.] AS [produktiv],
	[unprod.] AS [unproduktiv],
	[Abw.] AS [abwesend],
	--[produktiv_für_Berechnung_LG] AS [produktiv_für_Berechnung_LG],
	--[Servicemobil] AS [Servicemobil],
	--[krank] AS [krank],
	--[Meistervertr.] AS [Meistervertr.],
	[Activity_Desc] AS [Activity_Desc],
	[Standort_ID] AS [Standort_ID],
	[Monteur_Gruppe] AS [Monteur_Gruppe],
	[Department No] AS [Department No],
	[Order Number] AS [Order Number],
	[Task Type Group] AS [Task Type Group],
	[Extern] AS [Extern],
	[Intern] AS [Intern],
	[Home Page 2] AS [Home Page 2],
	[Employment Date] AS [Employment Date],
	left ( [Department No] , 2 ) AS [Standort_Monteur] 
FROM [export_csv].[Time_Clock_Entry]
GO

CREATE
	OR

ALTER VIEW [load].[Zeit_2_Ryma]
AS
SELECT --[Employee No] AS [Employee No],
	[Datum] AS [Datum],
	--[Department No_ori] AS [Department No_ori],
	--[Time Account No] AS [Time Account No],
	--[Time Account Value] AS [Time Account Value],
	--[Ta Class (general)] AS [Ta Class (general)],
	--[Ta Class (statistics)] AS [Ta Class (statistics)],
	--[Ta Class (absent Days)] AS [Ta Class (absent Days)],
	--[Ta Class (time Processing)] AS [Ta Class (time Processing)],
	--[Ta Class (individual)] AS [Ta Class (individual)],
	--[Ta Class (employee Info)] AS [Ta Class (employee Info)],
	--[Ta Class (vacation Reduction)] AS [Ta Class (vacation Reduction)],
	--[Ta Formatting] AS [Ta Formatting],
	--[Generating Function] AS [Generating Function],
	--[Record Protected] AS [Record Protected],
	--[No] AS [No],
	--[Description] AS [Description],
	--[Description 2] AS [Description 2],
	--[Zeitdauer_ori] AS [Zeitdauer_ori],
	[Zeitdauer] AS [Zeitdauer],
	--[gesamt Stunden] AS [gesamt Stunden],
	--[krank] AS [krank],
	--[Wehr- /Zivildienst] AS [Wehr- /Zivildienst],
	--[Sonderurlaub] AS [Sonderurlaub],
	--[Berufsschule] AS [Berufsschule],
	--[Innung Azubi] AS [Innung Azubi],
	--[Feiertag] AS [Feiertag],
	--[Schulung extern] AS [Schulung extern],
	--[Urlaub] AS [Urlaub],
	--[W-fix Stunden] AS [W-fix Stunden],
	[Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	--[Standort] AS [Standort],
	--[First Name] AS [First Name],
	[Last Name] AS [Last Name],
	--[Monteur_Gruppe_ori] AS [Monteur_Gruppe_ori],
	--[Abteilung] AS [Abteilung],
	[Order Number] AS [Order Number],
	[Monteur] AS [Monteur],
	--[Zeitausgleich] AS [Zeitausgleich],
	--[Überstunden] AS [Überstunden],
	--[Leaving Date] AS [Leaving Date],
	--[Group No 1] AS [Group No 1],
	--[Group No 2] AS [Group No 2],
	--[Group No 3] AS [Group No 3],
	--[Tage Heute Leaving Date] AS [Tage Heute Leaving Date],
	[Monteur_Gruppe] AS [Monteur_Gruppe],
	--[produktiv/unproduktiv] AS [produktiv/unproduktiv],
	--[Monatserster] AS [Monatserster],
	--[Monatsletzter] AS [Monatsletzter],
	--[Heute] AS [Heute],
	--[Datum Tagesbericht] AS [Datum Tagesbericht],
	[Employment Date] AS [Employment Date],
	[Department No] AS [Department No],
	--[Datum_Monteurlisten] AS [Datum_Monteurlisten],
	--[Fehlzeiten genehm./ungenem.] AS [Fehlzeiten genehm./ungenem.],
	[Activity Desc] AS [Activity_Desc],
	[Monteur_Gruppe_2] AS [Monteur_Gruppe_2],
	[Standort_ID] AS [Standort_ID],
	left ( [Department No] , 2 ) AS [Standort_Monteur],
	[Task Type Group] AS [Task Type Group],
	[Sollzeit] AS [Sollzeit],
	[abwesend] AS [abwesend],
	[Home Page 2] AS [Home Page 2],
	--[Summe Sollzeit] AS [Summe Sollzeit] 
FROM [export_csv].[Zeit_2_Ryma]
GO

CREATE
	OR

ALTER VIEW [load].[Add_Serv_Ledger_Entry_fakt_AW_Monteur]
AS
SELECT --[Order No] AS [Order No],
	--[Document No] AS [Document No],
	--[No] AS [No],
	--[Description] AS [Description],
	--[Res Efficiency Level] AS [Res Efficiency Level],
	--[Source Code] AS [Source Code],
	--[Qty Evaluated] AS [Qty Evaluated],
	--[Portion] AS [Portion],
	--[Cust Split Portion] AS [Cust Split Portion],
	--[Service Job No] AS [Service Job No],
	--[Quantity] AS [Quantity],
	--[Entry No] AS [Entry No],
	[fakt. Stunden] AS [fakt Stunden],
	--[Qty (hour)] AS [Qty (hour)],
	--[Standard Time] AS [Standard Time],
	--[Standard Time Type] AS [Standard Time Type],
	--[Standard_Time_Portion] AS [Standard_Time_Portion],
	--[Vorgabezeit incl. AZ] AS [Vorgabezeit incl. AZ],
	[Monteur] AS [Monteur],
	[Order Number] AS [Order Number],
	--[Bill-to Name] AS [Bill-to Name],
	--[Posting Date] AS [Posting Date],
	[Datum] AS [Datum],
	--[Auftrag_Kunde] AS [Auftrag_Kunde],
	--[Minimum_Datum_Auftrag] AS [Minimum_Datum_Auftrag],
	--[Datum_Monteurlisten] AS [Datum_Monteurlisten],
	--[Group No 1] AS [Group No 1],
	--[Group No 2] AS [Group No 2],
	--[Group No 3] AS [Group No 3],
	--[produktiv/unproduktiv] AS [produktiv/unproduktiv],
	--[Monteur_Gruppe_ori] AS [Monteur_Gruppe_ori],
	--[Leaving Date] AS [Leaving Date],
	[Monteur_Gruppe] AS [Monteur_Gruppe],
	[Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	--[Location Code] AS [Location Code],
	--[Standort] AS [Standort],
	--[Department No_Employee_T] AS [Department No_Employee_T],
	[Standort_ID] AS [Standort_ID],
	--[Function Code] AS [Function Code],
	[Task Type Group] AS [Task Type Group],
	[Monteur_Gruppe_2] AS [Monteur_Gruppe_2],
	--[Quantity_Service_Ledger_Entry] AS [Quantity_Service_Ledger_Entry],
	--[Unit Price_Service_Ledger_Entry] AS [Unit Price_Service_Ledger_Entry],
	--[Total Price_Service_Ledger_Entry] AS [Total Price_Service_Ledger_Entry],
	--[Umsatz Lohn_zwischen] AS [Umsatz Lohn_zwischen],
	[Umsatz Lohn] AS [Umsatz Lohn],
	--[Discount Amount] AS [Discount Amount],
	--[Nachlass_zwischen] AS [Nachlass_zwischen],
	[Nachlass] AS [Nachlass],
	[Home Page 2] AS [Home Page 2],
	[Employment Date] AS [Employment Date],
	left ( [Department No_Employee_T] , 2 ) AS [Standort_Monteur],
	[Department No_Employee_T] AS [Department No] 
FROM [export_csv].[Add_Serv_Ledger_Entry_fakt_AW_Monteur]
GO

CREATE
	OR

ALTER VIEW [load].[Stempelzeiten_Monteurlisten_aus_ims]
AS
SELECT --[Entry No] AS [Entry No],
	--[Employee No] AS [Employee No],
	--[Resource No] AS [Resource No],
	--[Address No] AS [Address No],
	--[Date] AS [Date],
	--[Time] AS [Time],
	--[Sorting] AS [Sorting],
	--[Duration] AS [Duration],
	--[Task Type Code] AS [Task Type Code],
	--[Type] AS [Type],
	--[Linked To Entry No] AS [Linked To Entry No],
	--[Leaving] AS [Leaving],
	--[Service Order No] AS [Service Order No],
	--[Service Job No] AS [Service Job No],
	--[Service Line No] AS [Service Line No],
	--[Labor Standard Time Type] AS [Labor Standard Time Type],
	--[Approved] AS [Approved],
	--[Approved By Employee No] AS [Approved By Employee No],
	--[Approval Timestamp] AS [Approval Timestamp],
	--[Canceled] AS [Canceled],
	--[Canceled By User Id] AS [Canceled By User Id],
	--[Cancelation Timestamp] AS [Cancelation Timestamp],
	--[Created By User Id] AS [Created By User Id],
	--[Creation Timestamp] AS [Creation Timestamp],
	--[Productive] AS [Productive],
	--[Statistics Group] AS [Statistics Group],
	--[Considered As Working] AS [Considered As Working],
	--[Order Location Code] AS [Order Location Code],
	--[Resource Location Code] AS [Resource Location Code],
	--[Make Code] AS [Make Code],
	--[Branch Code] AS [Branch Code],
	--[Labor No] AS [Labor No],
	--[Description] AS [Description],
	--[Source Code] AS [Source Code],
	--[Efficiency] AS [Efficiency],
	--[Automatic] AS [Automatic],
	--[Passed] AS [Passed],
	--[Code] AS [Code],
	--[Description2] AS [Description2],
	--[No] AS [No],
	--[Name] AS [Name],
	[Last Name] AS [Last Name],
	--[First Name] AS [First Name],
	--[Group No 1] AS [Group No 1],
	--[Group No 2] AS [Group No 2],
	--[Leaving Date] AS [Leaving Date],
	--[Produktiv/unproduktiv] AS [Produktiv/unproduktiv],
	[Monteur] AS [Monteur],
	[Monteur_Gruppe_2] AS [Monteur_Gruppe_2],
	--[Tage Heute Leaving Date] AS [Tage Heute Leaving Date],
	--[Datum_Stempelung] AS [Datum_Stempelung],
	--[Monatserster] AS [Monatserster],
	--[Monatsletzter] AS [Monatsletzter],
	--[Heute] AS [Heute],
	--[Datum Tagesbericht] AS [Datum Tagesbericht],
	[Hauptbetrieb_ID] AS [Hauptbetrieb_ID],
	[Standort_ID] AS [Standort_ID],
	[Produktiv Für Berechnung Lg_Monteurlisten] AS [Summe Produktiv Monteur Auftrag],
	[Monteur_Gruppe] AS [Monteur_Gruppe],
	[Datum] AS [Datum],
	[Department No] AS [Department No],
	[Order Number] AS [Order Number],
	[Task Type Group] AS [Task Type Group],
	[Home Page 2] AS [Home Page 2],
	[Employment Date] AS [Employment Date],
	left ( [Department No] , 2 ) AS [Standort_Monteur] 
FROM [export_csv].[Stempelzeiten_Monteurlisten_aus_ims]
GO