Conversions are program used to load legacy data into Oracle database/tables. An organization using a legacy system planning to move onto Oracle platform can validate and load all the master and transactional data into Oracle tables using conversion programs. Following are the basic steps to perform a conversion: –
- Export data into Flat file
- Prepare SqlLdr script and create staging table.
- Load data using SqlLdr utility into staging table.
- Run the conversion program to validate and load data into Oracle base tables using module specific APIS.
Conversions Specific to Oracle HRMS
Organization HR foundation is built upon organization structure, employee details, compensation & payroll configuration.
HR_API_MODULE table gives details of all the available APIs in HRMS. Remember below key points around conversions: –
- Always have 6 addition columns for ever conversion
CONV_PROCESS_FLAG, CONV_ERROR_MESSAGE, CONV_CREATED_BY, CONV_CREATION_DATE, CONV_LAST_UPDATED_BY, CONV_LAST_UPDATE_DATE, NEW_HRMS_ID(Primary key generated after successful completion of API).
- Populate data in staging table with
‘N’, update it as
‘P’for all success records and
Efor all error records.
- Always, look for date format in excel file, ensure to have
DD-MON-YYYYformat. For ‘DD-MON-YY’ erroneous results may come up for Oracle end date
- Try to enclose column values with double quotes
(“)where chances are to have a comma in the value itself
Original Data Himanshu, Goyal 01-JAN-1991 MALE CSV file “Himanshu,Goyal”,’01-JAN-1991’,’MALE’
- Notepad++ is your real friend. “Junk Characters”, this phrase is used frequently in conversion, you need notepad++ whenever you come across one. Use show symbol option to see the hidden evils.
à Show Symbol
à End of line
- Ensure to format the column where table data type is a number with decimals.
- Try not to hard code the file name in
.CTLfile, always pass the file name with directory path in loader program parameter.
- Search for
$CUSTOM_TOPor take help from your
DBAand place the
.CTLfile in that directory path only.
- Use optionally enclosed by
‘”’for data fields where values may contain comma.
- It’s not necessary to provide values for all the columns in staging table, you can load data for any number of columns. Column sequence is the key.
- Initialize the variables and set them up in start of for loop.
- Create a separate procedure to update staging table.
- Always user
ASCorder for historical data to maintain data integrity and consistency.
APIin the program.
- For every record, staging table process flag and error message must be updated. Use Pragma autonomous to commit in its scope to commit without fail.
- Publish relevant error messages to the fnd_log for debugging.
R12to update WHO columns with history data.