1. Prepare Data sheet in .txt or .csv or any other compatible file format as needed.
2. For maintaining the error records, we can create different table or can be identified with the help of status mentioned below:
3. There are 5 types of Status we can use to understand the process while conversion.
a. P – Data loaded from excel sheet to custom table.
b. S- Data got validated with custom validation
c. E – Error in data while custom validation
d. SE – Custom data validation is done but there is an error in API validation
e. C – Completed Data validated and loaded in to database.
4. Load data into Custom table via Sql Loader or any other method. Most preferred one is Sql loader for huge count of data.
5. When all the rows got loaded from Excel sheet to Custom table, Status for all the rows will be P.
6. Then Fetch data into cursor and start validating data with custom validation as per requirement.
7. After all custom validation, status will be S if there is no error in data or Status E for erroneous record in data.
8. Correct the erroneous record and again do the validation until and unless all records status would be S.
9. Then start with dry run of data by passing API value P_validate =’TRUE’.
10. It will just validate the record but do not insert into database as there is no concept like Transaction tables into HRMS.
11. IF all the records are validated correctly, the status will be as it is ‘S’ but in case of any API error, status will change to ‘SE’.
12. Change the record content and re-run the Custom validation and API validation process until all records gets error free.
13. Once API and Custom validation is completed, run the program again with API value P_VALIDATE=’FALSE’ and change the record status as ‘C’.
14. It shows the records got updated into Database without any error.