Challenge
The customer requested a solution for its existing payroll application that involved lower annual costs.
This was going to be achieved with a future-proof MSSQL database migrated from the existing Oracle DB and customization of the back-end application to run on the new database.
The final application was to calculate personnel salaries each month based on parameters in the timesheets which were in large number and with many decimals. The payroll calculation algorithm was also going to run over 100 stored procedures and functions per employee, some of them reaching 20.000 code lines.
Solution Implemented
To fit the same functionality as Oracle, our engineers proceeded to the correction/adaption and refactoring of the calculation process in the MSSQL-migrated database.
Our team managed tens of thousands of rows consisting in the large amount of input data coming from the very high number of employees (1500+), each employee having a large volume of input data that had to be processed. 100+ functions and stored procedures are called on, resulting in the end into around 40.000 rows in the report (table). Our engineers successfully coped with this by implementing highly complex calculation algorithms.
For this, we capitalized all the MSSQL functionalities such as cursors, complex data type conversions. Beside the default MSSQL functions and methods, we customized procedures and functions that replaced native Oracle methods and functions having no correspondent in MSSQL. Achieving this came rather naturally to our team owing to its 20+ experience in SQL development, but still the efforts were considerable because of the database size.
This complex calculation process has been designed and implemented according to local working time regulations referring to: travel time, night shifts, week-ends and holidays, full-time/part-time contracts, break hours, compensation hours, drivers’ hours, overtime percentages, leaves and many more, resulting in various calculation methods.
As there is no exact data correlation between Oracle and MSSQL Server, handling values with many decimals in the context of a different working-time system involved high calculation precision and diligence from our engineers.
Technologies
SQL Server Migration Assistant (SSMA) was used as tool for Oracle to migrate the database design and the data itself.
VB. NET – was used to build the application adapted to the new SQL Server database
Crystal Reports – used to create hundreds of reports, adapted to the new MSSQL Server database
Oracle DB – initial database system
SQL Server – final database system
Benefits
Beside the complex algorithm shown above, the payroll application runs faster, more accurately and securely.
The process can be executed for one or all of the employee groups during a given period (current, previous). The application delivers a series of reports successfully updated for the new database.
After database migration and implementation into the new application, the customer enjoyed significant cost savings while sticking to quality standards.
SPECIFIC SERVICES
Software Consulting
Process management
Software Development
Implementation
Software test
Software Services
Software maintenance
Performance tuning
Testing & QA
Functional testing
Load/Performance testing
NEARSHORING ADVANTAGES
- Quality custom software development
- End -user satisfaction
- Short time to market
- Quantifiable value
- Guaranteed performance level
- Reduced and controlled costs
- Managed risks
- Good value-for-money
- Flexibility