Challenge Overview
This project provides a tool that allows the end-user to import three GLUI user export files (*.xls) and automatically run a reconciliation of users. It will compare the three given input files, find out discrepancies, and write report to separate file with any discrepancies. It also provides a GUI for user to be able to interact with the tool.
We have completed the first version of GLUI application, now our client has new updates for the GLUI application.
Final Submission Guidelines
The new changes are:
There will be 3 input files - GLUI security table XLS file, OneTEAM XLS file and the GLUI Invalid ID Report XLS file.
The GLUI security table XLS file will remain in the same format.
The OneTEAM XLS file will still have the same format but there will be additional values available in the Status column in addition to the 'GRANTED' status. The other status values can now be 'PENDING' and 'DELETE INITIATED'.
The reconciliation tool will still print the OneTEAM and GLUI tabs as before in the result file. Now in the updates competition, we need also print the new imported GLUI Invalid ID Report XLS in a new sheet which should has the some content and style with the imported GLUI Invalid ID Report XLS file.
The tool will continue to look for the initial discrepancies in the same way but using ONLY records in Granted status. Before the results tab is created on the export results sheet, it will run through the 2 items below before printing the final results to the results sheet. During this additional logic, it will consider the other statuses.
1. If the discrepancy is in OneTEAM, check if the user email of the OneTEAM report is found in the Invalid ID report. If found, then check if the status is equal to "INVALID EMP DELETE INITIATED", then drop the record from the Discrepancies sheet. If the status is equal to "INVALID EMP DELETED", then keep the record in the discrepancies report but add the status "INVALID EMP DELETED" in the column "H" [Set the Heading of this column as 'Action'] (in the Discrepancies sheet, new column after column G "Business Justification").
2. If the discrepancy is in GLUI, check the status of the request id in OneTeam file (if none is found, then keep the discrepancy). If the status is equal to DELETE INITIATED, then look up for a new request id (for the same user email) in status PENDING (the creation date of th