5 Replies Latest reply: Jun 10, 2014 4:25 AM by Vigneswar Battu RSS

    How to fetch new joinees and previous employees from per_all_people_f for a given Year?

    Shahzad.H.Magsi

      Dear All,

       

      Need your help in find the total count for new joinees and previous employees from per_all_people_f for a given Year?, Any help will be great.

       

      Regards,
      Shahzad Hussain

        • 1. Re: How to fetch new joinees and previous employees from per_all_people_f for a given Year?
          Frank Kulash

          Hi,

          Shahzad.H.Magsi wrote:

           

          ... Any help will be great.

           

          You got that right.

           

          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas.  Also post the results you want from that data, and an explanation of how you get those results from that data.

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          If this question is specifically about some off-the-shelf product, then post a question in a forum devoted to that product, not here.

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: How to fetch new joinees and previous employees from per_all_people_f for a given Year?
            Shahzad.H.Magsi

            Thanks Frank,

             

            For the guidelines, but I have clearly mentioned from which table (per_all_people_f) which is of course a main table Oracle Apps HRMS module.

             

            I have this query for the count of total effective employees for a year, now I what is needed to be added t below query that it also give count for new joinees and employees who left the company for that given year.

             

            SELECT COUNT(peo.EMPLOYEE_NUMBER) AS "current total"

            FROM hr.per_all_people_f peo

            WHERE peo.current_employee_flag = 'Y'

            and peo.person_type_id =  9

            and peo.effective_start_date <= to_date('2013/01/01 00:00:00','RRRR/MM/DD HH24:MI:SS')

            and peo.EFFECTIVE_END_DATE >= to_date('2013/12/31 00:00:00','RRRR/MM/DD HH24:MI:SS')

             

            hope the question is clear now.

             

            Regard,

            Shahzad

            • 3. Re: How to fetch new joinees and previous employees from per_all_people_f for a given Year?
              Frank Kulash

              Hi, Shahzad,

              Shahzad.H.Magsi wrote:

               

              For the guidelines, but I have clearly mentioned from which table (per_all_people_f) which is of course a main table Oracle Apps HRMS module.

               

               

              Right.  If that was a table that Oracle distributed with the database, or if this were a forum devoted to the Oracle Apps HRMS module, then you wouldn't need to post a CREATE TABLE statement, and you might not even need to post INSERT statements.  However, since that table is not distributed with every Oracle database, and since this is a general SQL and PL/SQL forum, not a forum devoted to any particular application, you do need to post CREATE TABLE and INSERT statements if you ask the question in the SQL and PL/SQL forum.

               

              You can use a GROUP BY clause to get a separate COUNT for each year, and you can use the analytic SUM function to get a cumulative total of those COUNTs.

               

              Message was edited by: Frank Kulash

              I see that somebody moved this thread to a different forum.

              The messages above were posted in the SQL and PL/SQL forum.

              • 4. Re: How to fetch new joinees and previous employees from per_all_people_f for a given Year?
                1115101

                Hi Shahjad,

                First of all your query conditions are wrong

                "peo.effective_start_date <= to_date('2013/01/01 00:00:00','RRRR/MM/DD HH24:MI:SS')

                and peo.EFFECTIVE_END_DATE >= to_date('2013/12/31 00:00:00','RRRR/MM/DD HH24:MI:SS')"

                 

                it should be:

                 

                "peo.effective_start_date >= to_date('2013/01/01 00:00:00','RRRR/MM/DD HH24:MI:SS')

                and peo.EFFECTIVE_END_DATE <= to_date('2013/12/31 00:00:00','RRRR/MM/DD HH24:MI:SS')"

                 

                check for the >= and <= sign in both the conditions. In your case they are opposite.

                 

                Now the query that will return the number of employees joined the company in same year:

                 

                SELECT  count (employee_number)

                FROM    per_all_people_f peo

                       ,per_person_types ppt

                WHERE   peo.current_employee_flag = 'Y'

                AND     ppt.person_type_id = peo.person_type_id

                AND     ppt.business_group_id = peo.business_group_id

                AND     ppt.system_person_type = 'EMP'

                AND     ppt.active_flag = 'Y'

                AND     ppt.default_flag = 'Y'

                AND     peo.original_date_of_hire BETWEEN to_date ('2013/01/01'

                                                                  ,'RRRR/MM/DD')

                                                  AND     to_date ('2013/12/31'

                                                                  ,'RRRR/MM/DD');

                 

                Next thing is the query that will return the employees left the company in the same year:

                 

                SELECT  *

                FROM    per_all_people_f peo

                       ,per_person_types ppt

                WHERE   ppt.person_type_id = peo.person_type_id

                AND     ppt.business_group_id = peo.business_group_id

                AND     ppt.system_person_type = 'EMP'

                AND     peo.effective_end_date BETWEEN to_date ('2013/01/01'

                                                               ,'RRRR/MM/DD')

                                               AND     to_date ('2013/12/31'

                                                               ,'RRRR/MM/DD');

                • 5. Re: How to fetch new joinees and previous employees from per_all_people_f for a given Year?
                  Vigneswar Battu

                  Check these -

                  Change the BG_Id and dates accordingly.

                   

                  New Hires -

                  select  pps.date_start start_date

                  ,ppf.employee_number,ppf.full_name

                  from

                  per_people_f ppf

                  ,per_assignments_f paf

                  ,per_periods_of_service pps

                  where ppf.business_group_id = 0

                  and ppf.person_id = paf.person_id

                  and pps.person_id = ppf.person_id

                  and pps.date_start between '01-Jan-2013' and '31-dec-2013'

                  and pps.date_start between paf.effective_start_date and paf.effective_end_date

                  and pps.date_start between ppf.effective_start_date and ppf.effective_end_date

                  order by 1;

                   

                  Terminations -

                  select   pps.actual_termination_date

                  ,ppf.employee_number,ppf.full_name

                  from

                  per_people_f ppf

                  ,per_assignments_f paf

                  ,per_periods_of_service pps

                  where ppf.business_group_id = 0

                  and ppf.person_id = paf.person_id

                  and pps.person_id = ppf.person_id

                  and pps.actual_termination_date between '01-Jan-2013' and '31-dec-2013'

                  and pps.actual_termination_date between paf.effective_start_date and paf.effective_end_date

                  and pps.actual_termination_date between ppf.effective_start_date and ppf.effective_end_date

                  order by 1;