(Ver 10.0.0.5) [PMID Ref No 14020]
The TimeCard Worksheet Screen has been updated with new functionality to apply business rules to the time card entries based on a tailored set of SQL Script which is unique for each company. This new framework allows for specific processes to be applied to the timecard entries for updating existing records and / or adding new timecard entries for such things as field changes, identifying overtime calculations etc.
This new feature requires a custom set of SQL Script to be designed for each company which defines the business rules to be executed. A new Rules Button has been added to the TimeCard Worksheet Screen which is used to trigger the new business rules process. The outline below explains how to enable and use this new feature:
Enable New Setting |
|
Setup Custom SQL Script for Business Rules |
|
Business Rules Process Overview |
|
•Enable New Setting: Go to File > System Administrator > Configuration > Advanced Property Settings Button. Select the 'EnableTCWorksheetBusinessRulesBtn' option and then click the Add Advanced Company Settings button.
•Setup Custom SQL Script for Business Rules: A special Script Class 121700 is used to create the create the SQL Script that will define the company business rules. Our Professional Services team is available to design and implement a set of business rules that work for your specific company needs. Please contact AccuBuild Support for more details and a price quote.
Technical Notes: The SQL script MUST be designed to only work on the timecard worksheet records for the date range shown on the timecard worksheet screen AND Only the Open Records. We don’t want business rules applied to other records in the table, only the ones we are dealing with in the current screen. Also note that the SQL will NOT respect and Grid Filters but will be applied to ALL OPEN records within the Begin Date and End Date:
The following sample SQL code shows the proper WHERE clause for use when dealing with the pmpersonnel (time card worksheet records) table for the Business Rules:
Declare @StartDate Date;
Declare @EndDate Date;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// NOTE: the new Rules Button will generate the UserMARReportParametersTable just like any MAR Report does
// and this table will contain the Begin Date and End Date from the TimeCard Worksheet Screen which can
// then be used to query the proper set of records as shown below
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
@StartDate = (select convert(ReportStartDate,sql_date) from <<URP:UserMARReportParametersTable>>);
@EndDate = (select convert(ReportEndDate,sql_Date) from <<URP:UserMARReportParametersTable>>);
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Sample Select Clause with proper WHERE Clause to use when dealing with any records from the timecard worksheet
// Using the above cutoff dates @StartDate and @EndDate
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
select * from colink.pmpersonnel
WHERE coalesce(ImportedToAccounting,false) = false // Non Processed Records (OPEN)
and coalesce(e_no,0) > 0 // Valid Employee Numbers Only
and coalesce(miscinteger1,0) > 0 // Created By User ID NOT Null (Not Blank)
and coalesce(visitor,false) = false // Employee Records from DFR - NO Visitor Records
and coalesce(ratetype,0) in (1,2,3,4,21,22,23,24,121,122,123,124) // Only Valid Trans Types
and convert(MiscTimeStamp1,sql_date) >= @StartDate // Filtered for the Cutoff Date Range from the Screen
and convert(MiscTImeStamp1,sql_Date) <= @EndDate
NOTE: Sample Script is supplied in the Library Script for class 121700 to use as a guide in designing custom company script solutions.
If you want to display any result tables after the business rules are applied, you will need to create a results table in the user folder with the following structure and then populate this table with the result tables you would like to show the end user:
Table Name: TCW_BusinessRulesTableList
•TableName Character 200 Name of the table (no extension) to be displayed
•TableNo Integer Order of appearance in the Table Select List
➢Note: The TCW_RulesProcessResults table should be the first table listed to communicate to the user the results of the process. The following is a sample script to create the table list:
try
drop table <<URP:TCW_BusinessRulesTableList>>;
catch all
end;
// create table structure
select space(200) as TableName, 0 as TableNo
into <<URP:TCW_BusinessRulesTableList>>
from system.iota;
// empty blank record
delete from <<URP:TCW_BusinessRulesTableList>>;
// add tables to be displayed
insert into <<URP:TCW_BusinessRulesTableList>>
select 'TCW_RulesProcessResults' as TableName,1 as TableNo from system.iota;
insert into <<URP:TCW_BusinessRulesTableList>>
select 'TCW_OTAdjustmentRecs' as TableName,2 as TableNo from system.iota;
insert into <<URP:TCW_BusinessRulesTableList>>
select 'TCW_WeeklySundayOT' as TableName,3 as TableNo from system.iota;
Business Rules Process Overview
Business Rules Process Overview: The TimeCard Worksheet Screen now contains a new Rules Button as shown below which is used to execute the custom SQL script for the company business rules:
Each time the Rules Button is clicked, the SQL Script will be run and will include all OPEN records in the TimeCard Worksheet table (pmpersonnel) for the date range defined by the Begin Date and End Date settings.
➢Important Note: The Business rules will NOT respect any grid filters on the current screen, the only filtering for the records will be OPEN records within the displayed date range. In other words, if you have a grid filter set for a single employee or a single union code etc., the business rules script will NOT respect those filters when the rules are applied. Refer to the Technical Notes above for more details.
A new field has also been added to the TimeCard Worksheet Screen which is used to identify any NEW records that are added by the Rules Process called RulesMasterRecID (MiscFloat4 Field). The tailored SQL script should be designed to update this field with the record ID of the timecard record (parent record) that was used to create the new record. The new record will contain all of the settings of the source record such as employee number, job, phase etc. This makes it much easier to identify any new records that were created automatically by the Rules Button process as shown:
In the above example, 3 adjusting records were created using the parent record where personnelID = 5796 and all 3 records now contain the parent record number in the new RulesMasterRecID.
Rules Button Process: Each time the Rules button is clicked, the following processes are triggered to implement the business rules on the timecard worksheet records:
•A confirmation screen will be displayed to confirm that the business rules should be applied. At this point you can exit the process with a ‘NO' response
•If any filters are set on the grid, they will be cleared in order to view any record changes in the grid after the business rules have been applied. If filters do exist, you will be prompted again with another opportunity to exit the process BEFORE the filters are cleared.
•Before applying the business rules, the PMPersonnel table will be backed up in the User Folder cycling through 10 backup file names PMPersonnel_Backup_1 through PMPersonnel_Backup_10. The backup file name will be logged in the File > User Activity Log.
•After the rules have been applied, a confirmation screen will be displayed with the message 'Business Rules Applied. Result Tables will be displayed if applicable'. Click OK.
•If the SQL script created user tables for displaying the results of the process, then a grid will be displayed with one or more tables to view. The first table in the list will be displayed automatically. If additional tables are created in the script, they will be available in the Results drop down list - Select another table and click the Open Button:
•Close the Results screen (when applicable) to return to the TimeCard Worksheet Screen to review any record changes or additions to the timecard entries.
[Version 10.1.0.1] - The backup process for the timecard worksheet company table (PMPERSONNEL) has been updated to use a new backup process which will now maintain the original values for the autoinc field (PERSONNELID) in the backup file. This backup process takes place each time the Rules Button is clicked on the TimeCard Worksheet Screen. NOTE: The backup file will set the autoinc field to an integer field type in order to preserve the original values from the company table.