2 Replies Latest reply: Oct 31, 2012 1:18 AM by 597001 RSS

    oracle hrms

    956650
      how to find number of business days i.e monday to friday of an employee in oracle hrms table
        • 1. Re: oracle hrms
          Sunthar Tharmalingam
          http://docs.oracle.com/cd/A60725_05/html/comnls/us/per/orgdf07.htm
          Thanks
          • 2. Re: oracle hrms
            597001
            You can get the working days from per_periods_of_service table and join it with per_all_people_f with person_id to get the employee info. Use the following query to calcualte the period of service in business days.
            SELECT date_start start_date
                 , actual_termination_date termination_date
                 ,   (actual_termination_date - date_start)
                   - 2 * FLOOR ((actual_termination_date - date_start) / 7)
                   - DECODE (SIGN (  TO_CHAR (actual_termination_date, 'D')
                                   - TO_CHAR (date_start, 'D')), -1, 2, 0)
                   + DECODE (TO_CHAR (date_start, 'D'), 7, 1, 0)
                   - DECODE (TO_CHAR (actual_termination_date, 'D'), 7, 1, 0
                            ) AS "Periods of Service (In Days)"
              FROM per_periods_of_service
             WHERE person_id = :person_id
            Cheers,
            ND
            Use the "helpful" or "correct" buttons to award points to replies / Mark the thread as answered, if your question is answered.