Tag: r12


Conversions in Oracle apps R12

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: –



  1. Export data into Flat file
  2. Prepare SqlLdr script and create staging table.
  3. Load data using SqlLdr utility into staging table.
  4. 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: –


Flat File

  • 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 CONV_PROCESS_FLAG as ‘N’, update it as ‘P’ for all success records and E for all error records.
  • Always, look for date format in excel file, ensure to have DD-MON-YYYY format. For ‘DD-MON-YY’ erroneous results may come up for Oracle end date (31-DEC-4712) records.
  • 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.

i.e. View à 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 .CTL file, always pass the file name with directory path in loader program parameter.
  • Search for $CUSTOM_TOP or take help from your DBA and place the .CTL file 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.

Conversion Program

  • Initialize the variables and set them up in start of for loop.
  • Create a separate procedure to update staging table.
  • Always user ORDER_BY in ASC order for historical data to maintain data integrity and consistency.
  • Don’t COMMIT after every API in 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.
  • Use HR_General.data_migrator_mode in R12 to update WHO columns with history data.