ERS 9.1+: Assign Resource Functionality Retrieving Incorrect Holiday Schedule From PS_JOB When Creating New Appointments Into Employee Calendar (Doc ID 1581022.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

If an Employee has been linked to more than one different Holiday Schedule among all its PS_JOB Employment History rows, the incorrect Holidays may be picked up at the time of creating a brand new Assignment at the Assign Resource level (Page RS_ASSIGN_MAIN). When saving the new Assignment, the system will update the Resource's Calendar, but when doing so, it will be not taking the Holiday Schedule of the most current effective dated row in PS_JOB, instead it takes the first Holiday Schedule it finds chronologically in the system.

The Application Engine Program RS_APPTUPD.BLDDATES calls Function bChkIfaHolidayTobeSkipped to test the dates in the range between the new Assignment's Start Date to End Date. This function calls the following SQL:

  SQLExec("SELECT COUNT(*) FROM %TABLE(JOB)  A, %TABLE(HOLIDAY_DATE)  B WHERE A.HOLIDAY_SCHEDULE=B.HOLIDAY_SCHEDULE AND A.EMPLID=:1 AND B.HOLIDAY=ÚTEIN(:2)"

Which finds a match between PS_JOB.HOLIDAY_SCHEDULE and PS_HOLIDAY_DATE, without regard to the effective dated nature of the rows in PS_JOB.

REPLICATION STEPS:

   - Log into the FSCM Online Application as User ID VP1
   - Navigate to: Set Up Financials/Supply Chain > Common Definitions > Resources Data > Holidays
   - Add a brand new Holiday Schedule named NYC, and place a Public Holiday for October 7th 2013
   - Add a second new Holiday Schedule named CAL, and place a Public Holiday for March 11th 2013
   - Navigate to: Resource Management > Establish Resources > Update Employee Data
   - Open existing Employee ID RS00000001, and go to the Employee Job tab
   - Have one first Effective Date row on October 19th 2003, with BU US001, and Holiday Schedule to be NYC
   - Add a new Effective Date Job row for June 27th 2012, with BU US003, and Holiday Schedule to be CAL
   - Navigate to: Set Up Financials/Supply Chain > Common Definitions > Resources Data > Load Holidays
   - Launch the Application Engine Program RS_HOLIDAYS to completion
   - At this stage, Employee ID RS00000001 has in his Monthly Schedule a working day on October 7th, and a public holiday on March 11th 2013, because the most current effective date PS_JOB row has a Holiday Schedule of CAL
   - Navigate to: Project Costing > Projects Definition > General Information
   - Create a brand new Project ID PROJ_HOLIDAY under BU US003, and Start Date of January 1st 2013, and End Date of December 31st 2013
   - Navigate to: Resource Management > Assign Resource
   - Add a brand new Assignment with the following information:
        - Assignment ID = NEXT
        - Employee ID = RS00000001
        - Project Role = Project Consultant
        - Business Unit = US003
        - Project ID = PROJ_HOLIDAY
        - Customer = Alliance Group
        - Start Date = October 1st 2013
        - End Date = October 31st 2013
        - Status = Assign
   - Save the changes of this new Assignment ID, so that Work History and Calendar are updated properly
   - Navigate to: Resource Management > Maintain Schedules > Monthly Schedules
   - Open Employee ID RS00000001
   - Scroll to the October 2013 monthly schedule, to see that all working dates have been updated with the Assignment in question, except for Monday 7th, which is left in blank

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

Checking the Resource's Monthly Schedule it can be seen that for particular dates falling within the Assignment's period, no appointment has been created, even though it is a working day. The system instead is taking the affected date as a public holiday because in another Holiday Schedule, which no longer applies to the Resource's current PS_JOB row, it had been listed as a Public Holiday. Leaving that date as blank, will still play a role when performing searches against that Resource, because Availability will still be set as free when trying to fulfill other assignments.

At the time of saving the new Assignment, and the Calendar Entries be created, the system should validate the Holiday Schedule that is defined in the PS_JOB row that will be the most effective date current row during the period of the Assignment in question, and apply any established Public Holidays for that Holiday Schedule, and not another.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.