Home > Tutorials

PowerForce ™

Input Tables

The following SQL tables are programatically to create the necessary TIMESHEET tables.

• "PRM_MASTER__EMPLOYEE"
  "PRS_SYSTEM__PAY_GROUP" 
  "PRS_SYSTEM__DEPARTMENT"
  "PRS_SYSTEM__PAY"
   
  "EQM_MASTER__EQUIPMENT"
  "EQM_MASTER__COST_CODE"
  "EQM_MASTER__EQUIPMENT_TYPE"
  "EQM_MASTER__REVENUE_CODE"
  
•  
  "RTM_MASTER__JOB"
  "RTM_MASTER__COST_CODE"
  
• "JCM_MASTER__CATEGORY" 
  

Extract Logic

The following meta code details the data extraction on a per table basis :

PRM_MASTER__EMPLOYEE

sql := "INSERT INTO [TimesheetGeorgiouGroup].[dbo].[EMPLOYEES]"
    sql := " ([id]"
    sql := " ,[first_name]" [Employee_Name]
    sql := " ,[middle_name]" [Employee_Name]
    sql := " ,[surname]" [Employee_Name]
    sql := " ,[pay_class]"[Union_Local]
    sql := " ,[occupation]" [Occupation]
    sql := " ,[employment_type]" [Union_Class]
    sql := " ,[department]" [Department]

.

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Employee]
,[Employee_Name]
,[Title]
,[Occupation]
,[Country]
,[Address_1]
,[Address_2]
,[City]
,[State] 10
,[Zip_Code]
,[Foreign_State]
,[Foreign_Postal_Code]
,[Phone_Number]
,[Tax_File_Number]
,[Pay_Group]
,[SM_Employee]
,[Gender]
,[Skill_Level]
,[Birth_Date] 20
,[Resident_State]
,[Work_State]
,[Use_Employee_Work_State]
,[Filing_Status]
,[Personal_Exemptions]
,[Misc_Tax_Code]
,[Medicare_Levy]
,[Spouse]
,[Dependant_Rebate]
,[Mortgage_Rebate] 30
,[Zone_Rebate]
,[Group_Cert_Number]
,[Pay_ID]
,[Job]
,[JC_Cost_Code]
,[Equipment]
,[EQ_Cost_Code]
,[Union_ID]
,[Union_Local]
,[Union_Class] .. 40
,[Department]
,[Certified_Class]
,[Shift]
,[Not_Used]
,[Misc_1]
,[Misc_2]
,[Auto_Bill_Revenue]
,[Hire_Date]
,[Rehire_Date]
,[Termination_Date]
,[Entitlement_Date]
,[Custom_Sort]
,[Max_Check_Amount]
,[Last_Check_Number]
,[Last_Check_Date]
,[Last_Check_Amount]
,[No_Pay_Slip]
,[Perm_Part_Time]
,[Part_Time]
,[Indigenous]
,[Trainee]
,[Apprentice]
,[Casual]
,[Check_List_8]
,[Check_List_9]
,[Def_Super_Fund]
,[Payment_Date]
,[Taxable_Component]
,[Tax_Free_Component]
,[Trans_Term_Payment]
,[Prior_Payment_Same_Term]
,[Death_Benefit]
,[Death_Benefit_Type]
,[Lump_Sum_Pmt_A_Type]
,[Prior_Year_Regular_Pay]
,[YTD_Regular_Pay]
,[Prior_Year_Overtime_Pay]
,[YTD_Overtime_Pay]
,[Prior_Year_Other_Pay]
,[YTD_Other_Pay]
,[Prior_Year_Gross_Pay]
,[YTD_Gross_Pay]
,[Prior_Year_Employee_Taxes]
,[YTD_Employee_Taxes]
,[Prior_Yr_Misc_Deductions]
,[YTD_Misc_Deductions]
,[Prior_Yr_Direct_Deposits]
,[YTD_Direct_Deposits]
,[Prior_Year_Net_Pay]
,[YTD_Net_Pay]
,[Prior_Year_Regular_Hours]
,[YTD_Regular_Hours]
,[Prior_Year_Overtime_Hours]
,[YTD_Overtime_Hours]
,[Prior_Year_Employer_Taxes]
,[YTD_Employer_Taxes]
,[Prior_Year_Fringes]
,[YTD_Fringes]
,[Prior_Qtr_Regular_Pay]
,[QTD_Regular_Pay]
,[Prior_Qtr_Overtime_Pay]
,[QTD_Overtime_Pay]
,[Prior_Qtr_Other_Pay]
,[QTD_Other_Pay]
,[Prior_Qtr_Gross_Pay]
,[QTD_Gross_Pay]
,[Prior_Qtr_Employee_Taxes]
,[QTD_Employee_Taxes]
,[Prior_Qtr_Misc_Deductions]
,[QTD_Misc_Deductions]
,[Prior_Qtr_Direct_Deposits]
,[QTD_Direct_Deposits]
,[Prior_Qtr_Net_Pay]
,[QTD_Net_Pay]
,[Prior_Qtr_Regular_Hours]
,[QTD_Regular_Hours]
,[Prior_Qtr_Overtime_Hours]
,[QTD_Overtime_Hours]
,[Prior_Qtr_Employer_Taxes]
,[QTD_Employer_Taxes]
,[Prior_Qtr_Fringes]
,[QTD_Fringes]
,[Prior_Month_Regular_Pay]
,[MTD_Regular_Pay]
,[Prior_Month_Overtime_Pay]
,[MTD_Overtime_Pay]
,[Prior_Month_Other_Pay]
,[MTD_Other_Pay]
,[Prior_Month_Gross_Pay]
,[MTD_Gross_Pay]
,[Prior_Mth_Employee_Taxes]
,[MTD_Employee_Taxes]
,[Prior_Mth_Misc_Deductions]
,[MTD_Misc_Deductions]
,[Prior_Mth_Direct_Deposits]
,[MTD_Direct_Deposits]
,[Prior_Month_Net_Pay]
,[MTD_Net_Pay]
,[Prior_Month_Regular_Hours]
,[MTD_Regular_Hours]
,[Prior_Mth_Overtime_Hours]
,[MTD_Overtime_Hours]
,[Prior_Mth_Employer_Taxes]
,[MTD_Employer_Taxes]
,[Prior_Month_Fringes]
,[MTD_Fringes]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Employee_Notes]
,[Employee_File_Links]
,[First_Aid]
FROM [TimesheetGeorgiouGroup].[dbo].[PRM_MASTER__EMPLOYEE]

PRS_SYSTEM__PAY_GROUP

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[CODES]"
    sql := "           ([id]" [Pay_Group]
    sql := "           ,[description]"[Pay_Group_Name]
    sql := "           ,[user_1]"
    sql := "           ,[category])" " 236 "

SELECT TOP 1000 [DBID]
,[Pay_Group]
,[Pay_Group_Name]
,[Auto_Salary]
,[Earnings_Expense_Account]
,[Burden_Expense_Account]
,[Pay_Frequency]
,[Days]
,[Weeks]
,[Salary_Hours]
,[Bank_Account]
,[Custom_Sort]
,[Round_Net]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Pay_Group_Notes]
,[Pay_Group_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[PRS_SYSTEM__PAY_GROUP]

PRS_SYSTEM__DEPARTMENT

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[CODES]"
    sql := "           ([id]" [Department]
    sql := "           ,[description]" [Department_Name]
    sql := "           ,[user_1]"
    sql := "           ,[category])" " 237 "

SELECT TOP 1000 [DBID]
,[Department]
,[Department_Name]
,[Earnings_Expense_Account]
,[Fringe_Expense_Account]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
FROM [TimesheetGeorgiouGroup].[dbo].[PRS_SYSTEM__DEPARTMENT]

PRS_SYSTEM__PAY

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[CODES]"
    sql := "           ([id]" [Pay_ID]
    sql := "           ,[description]" [Description]
    sql := "           ,[user_1]"
    sql := "           ,[category])" " 238 "

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Pay_ID]
,[Description]
,[Pay_Type]
,[InOut_Pay]
,[Salary]
,[Units]
,[Weighted_Average_OT]
,[Straight_Pay]
,[Retain_YTD]
,[Printed_Description]
,[Distribute_To_GL]
,[Distribute_To_JC]
,[Distribute_To_EQ]
,[Distribute_to_BL]
,[GL_Expense_Account]
,[GL_Liability_Account]
,[JC_Category]
,[Calc_Method]
,[Factor]
,[Reg_Pay_ID]
,[Amount]
,[Formula]
,[FWH_Exempt]
,[Not_Used]
,[Not_Used_176]
,[Not_Used_177]
,[Emplr_SWH_Exempt]
,[Not_Used_184]
,[Not_Used_185]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Pay_Notes]
,[Pay_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[PRS_SYSTEM__PAY]

EQM_MASTER__EQUIPMENT

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[EQUIPMENT]"
    sql := "           ([id]" [Equipment]
    sql := "           ,[description]" [Description]
    sql := "           ,[type]" [Equipment_Type]
    sql := "           ,[revenue_code]" [GL_Prefix]
    sql := "           ,[misc_code_2])"

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Equipment]
,[Description]
,[Serial_Number]
,[Make]
,[Model]
,[Year_Built]
,[Weight]
,[Tare_Weight]
,[Length] 10
,[Width]
,[Height]
,[Wheelbase]
,[Equipment_Type]
,[Misc_Code_1]
,[Misc_Code_2]
,[Misc_Code_3]
,[Job]
,[Employee]
,[Current_Location]..20
,[Last_Location_Update_Date]
,[Attached_to]
,[Status]
,[Status_Date]
,[Status_Time]
,[MeterOdometer]
,[Current_Reading_Date]
,[Total_Reading]
,[Initial_Reading]
,[Configuration_Informat]..30
,[Last_Maintenance_Date]
,[Last_Maintenance_Activity]
,[GL_Prefix]
,[Revenue_Code]
,[Cost_Code]
,[Category]
,[Fixed_Asset_ID]
,[Component]
,[Fuel_Consumption_Factor]
,[Week_To_Date_Cost]
,[Next_Week_Cost]
,[Month_To_Date_Cost]
,[Next_Month_Cost]
,[Year_To_Date_Cost]
,[Next_Year_Cost]
,[Life_To_Date_Cost]
,[Last_Cost_Update_Date]
,[Week_to_Date_Rev_Units]
,[Week_To_Date_Revenue]
,[Next_Week_Rev_Units]
,[Next_Week_Revenue]
,[Month_To_Date_Rev_Units]
,[Month_To_Date_Revenue]
,[Next_Month_Rev_Units]
,[Next_Month_Revenue]
,[Year_To_Date_Rev_Units]
,[Year_To_Date_Revenue]
,[Next_Year_Rev_Units]
,[Next_Year_Revenue]
,[Life_To_Date_Rev_Units]
,[Life_To_Date_Revenue]
,[Last_Revenue_Update_Date]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Equipment_Notes]
,[Equipment_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[EQM_MASTER__EQUIPMENT]

EQM_MASTER__COST_CODE

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[EQUIPMENT_COST_CODES]"
    sql := "([id]" [Cost_Code]
    sql := ",[description]" [Description]
    sql := ",[equipment_id])" [Equipment]

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Equipment]
,[Cost_Code]
,[Description]
,[Unit_Description]
,[Memo_Cost]
,[Cost_Type]
,[Week_To_Date_Units]
,[Week_To_Date_Amount]
,[Next_Week_Units]
,[Next_Week_Amount]
,[Est_Oper_Cost_MTD_Unts]
,[Est_Oper_Cost_MTD_Amt]
,[Month_To_Date_Units]
,[Month_To_Date_Amount]
,[Next_Month_Units]
,[Next_Month_Amount]
,[Est_Oper_Cost_YTD_Unts]
,[Est_Oper_Cost_YTD_Amt]
,[Year_To_Date_Units]
,[Year_To_Date_Amount]
,[Next_Year_Units]
,[Next_Year_Amount]
,[Est_Oper_Cost_LTD_Unts]
,[Est_Oper_Cost_LTD_Amt]
,[Life_To_Date_Units]
,[Life_To_Date_Amount]
,[Last_Update_Date]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Cost_Code_Notes]
,[Cost_Code_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[EQM_MASTER__COST_CODE]

EQM_MASTER__EQUIPMENT_TYPE

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[CODES]"
    sql := "           ([id]" [Equipment_Type]
    sql := "           ,[description]" [Description]
    sql := "           ,[user_1]"
    sql := "           ,[category])" " 232 "

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Equipment_Type]
,[Description]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Equipment_Type_Notes]
,[Equipment_Type_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[EQM_MASTER__EQUIPMENT_TYPE]

EQM_MASTER__REVENUE_CODE

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[EQUIPMENT_REVENUE_CODES]"
sql := "    ([id]" [Equipment]
sql := "    ,[revenue_code]" [Revenue_Code]
sql := "    ,[description])"

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Equipment]
,[Revenue_Code]
,[Description]
,[Unit_Description]
,[Week_To_Date_Units]
,[Week_To_Date_Amount]
,[Next_Week_Units]
,[Next_Week_Amount]
,[Est_Usage_MTD_Units]
,[Est_Usage_MTD_Amt]
,[Month_To_Date_Units]
,[Month_To_Date_Amount]
,[Next_Month_Units]
,[Next_Month_Amount]
,[Est_Usage_YTD_Units]
,[Est_Usage_YTD_Amt]
,[Year_To_Date_Units]
,[Year_To_Date_Amount]
,[Next_Year_Units]
,[Next_Year_Amount]
,[Est_Usage_LTD_Units]
,[Est_Usage_LTD_Amt]
,[Life_To_Date_Units]
,[Life_To_Date_Amount]
,[Last_Update_Date]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Revenue_Code_Notes]
,[Revenue_Code_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[EQM_MASTER__REVENUE_CODE]

RTM_MASTER__JOB

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[JOBS]"
    sql := " ([id]" [Job]
    sql := " ,[description]" [Job_Description]
    sql := " ,[user_1]"
    sql := " ,[user_2])"

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Job]
,[Job_Description]
,[Job_Status]
,[Job_Manager]
,[Job_Notes]
,[Job_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[RTM_MASTER__JOB]

RTM_MASTER__COST_CODE

"INSERT INTO [TimesheetGeorgiouGroup].[dbo].[JOB_COST_CODES]"
    sql := "([id]"[Cost_Code]
    sql := ",[description]" [Cost_Code_Description]
    sql := ",[job_code])" " [Job]

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DBID]
,[Job]
,[Extra]
,[Cost_Code]
,[Cost_Code_Description]
,[Cost_Code_Status]
,[Cost_Code_Manager]
,[Cost_Code_Notes]
,[Cost_Code_File_Links]
FROM [TimesheetGeorgiouGroup].[dbo].[RTM_MASTER__COST_CODE]

JCM_MASTER__CATEGORY

x

Article updated by PowerForce Software on Wednesday, March 14, 2012.

Copyright ©2007-2013 Envizion Systems - All Rights Reserved - Terms of Use.