NOTE: These instructions have been updated to reflect revisions made to the original cleanup issued previously.  Updates/corrections are highlighted.

SQL’s TO FIX EFFECTIVE DATE ISSUES FOR CONVERSION

1. What the scripts are looking for (PeopleSoft conversion requirement):
2. Names – Primary name (PRI) must be the minimum effective date for Preferred Name type ‘PRF’  
3. Address – Type ‘Home’ minimum effdt must not be less than ‘PRI’ Name minimum effdt
4. Pers_Data_effdt must not be less than ‘PRI’ Name minimum effdt
5. Pers_data_USA minimum effdt must not be less than ‘PRI’ Name minimum effdt
6. Job minimum effdt must not be less that ‘PRI’ Name minimum effdt

The update scripts are using the following rules to comply with PeopleSoft requirement during conversion:

1. Never change job’s minimum effective date and minimum effective date for job must have action of Hire (HIR)
2. Primary name  effdt must be less than or equal to Job min effdt  
3. Primary name  effdt must be less than or equal to Preferred Name type ‘PRF’  effdt
4. Pers_data_Effdt must be equal to Primary Name min effdt  
5. Home address type must be equal to Primary Name min effdt  
6. Mail address must be greater than or equal to Home address min effdt.    
7. Pers_data_USA needs to be equal to or greater than the min effdt of Primary Name 

SQL files can also be accessed to view and save:

1. Click on specific SQL filename hyperlinks in blue.
2. Once you download and open up the PDF file: On the left side of the document, click on the arrow icon then the attachment icon and it will display all the SQL files.

1. Run SQL INSERT_INTO_PS_NAMES_TMP 

   This SQL will create a Temporary Names table that includes the maximum effdt row from the Names table for the PRI and PRF values.

2. Run the Select SQL EFFDT_SYNC_LOOKUP  

   If you do not have access to SQL – ask your developer to run the lookup SQL and send you the results. 

   This SQL is a select statement that will give you the results of all the EMPLIDs that need updating and includes all minimum effective dates from the five tables. EMPLIDs that do not have an issue will not be included in the results. See example below: 

   

   1. Review output and if necessary, correct any EMPLIDs that you think need manual cleanup.   

       

3. Run the SQL Update scripts EFFDT_SYNC _UPDATE 

    

4. Rerun the Select SQL EFFDT_SYNC_LOOKUP 

    

   1. Review the results.  The remaining rows with an effective date less than 1/1/1903 will be deleted in the upcoming step.  To prevent that from occurring on certain rows, make any necessary manual changes then proceed to Step 4.  

       

5. Run Delete SQL EFFDT_SYNC_DELETE – this will delete any remaining effective dated rows between 1/1/1901 and 12/31/1902. 

    

6. Rerun the select SQL EFFDT_SYNC_LOOKUP again.  If there are no remaining rows, all have been updated properly. 

    

   1. Any remaining rows will require some type of correction before automatic update can be completed.   

       

7. Using the original lookup SQL EFFDT_SYNC_LOOKUP, choose a few records in Personal data (Modify a Person, Personal Org Summary, etc.) to validate that no issues have occurred.   

    

8. Run SQL VALIDATE_NAMES_DATA_TO_BE_UPDATED 

   This SQL will compare the maximum dated name after running the SQLS from Step 3 and Step 4, with the temporary names table (which is prior to Steps 3 & 4) and will show what current names have been changed. Review these results.

   1. Save this file for future use if needed 

       

9. Run SQL UPDATE_ NAMES_TABLE 

   This will change the current names table to match the temp table on the names included in previous step.

10. Validate the data thoroughly – reviewing the data from Step 8 output in the names table 

    Check a few of the names in database to make sure Step 9 worked correctly.   

    End of Article