Oracle ERP User Network Collaborate with Professionals working on Oracle ERP(Oracle Apps) including EBusiness Suite (R12, 11i), Fusion, Peoplesoft, JDE, Siebel, Hyperion, OBIEE, SOA etc.

Dear Madhu,

please take into consideration the dates in per_periods_of_service table, coz with per_all_people_f there may be n numbers of instance available for a single person_id.

per_periods_of_service, will include exact dates from when the person was employed, and of rehired, he would be having another row, with same person_id. hope it helps you...

Cheers !!!! 

Shailesh C Ghansiyal

Hi shailesh,

 Correct ...i have checked in that table per_periods_of_service.

can i take date_start and final process_date as effective dates to calculate days?

Thanks for your help.

Thanks,

Madhu

Worked hours are between hire date and ACTUAL_TERMINATION_DATE

Something like this:

SELECT ppf.full_name

, sum ( pps.actual_terminationdate - pps.date_start)

FROM per_periods_of_service pps

, per_all_people_f ppf

WHERE pps.person_id = ppf.person_id

AND   pps.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date /*Eliminate duplicate ppf records, take most recent */

NOTE: You are not considering leave of absence periods when calculating this way, this is a very rough approximation of hours worked. Better to sum timesheet hours if you have them.

R,
David. 

Hi David,

  Thanks you so much for your answer...

Thanks,

Madhu

absolutely agree with David, Madhu can you check if you are also Using OTL or Absence management for calculating the leaves of employee...

regards,

Shailesh ....

sure shailesh,

I will check once.

Thank you so much for your help shailesh..

Thanks,

Madhu

Hi sailendra/David, I have worked based on your inputs to get the employed hours of an employee.It is working fine.
Please take a look in below query..Thanks alot..
/ Formatted on 2009/10/06 14:53 (Formatter Plus v4.8.6) /
SELECT ppf.full_name, (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE)) – pps.date_start ) ) / 7 actual, ROUND ( ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE)) – pps.date_start ) ) / 7 ) – FLOOR ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ), 2 ) deci, (FLOOR ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE)) – pps.date_start ) ) / 7 ) ) integ, ( ROUND ( ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ) – FLOOR ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ), 2 ) 9 ) a, ( (FLOOR ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ) ) 45 ) b, ( ROUND ( ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ) – FLOOR ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ), 2 ) 9 ) + ( (FLOOR ( (SUM ( NVL (pps.actual_termination_date, TRUNC (SYSDATE) ) – pps.date_start ) ) / 7 ) ) 45 ) c FROM per_periods_of_service pps, per_all_people_f ppf WHERE pps.person_id = ppf.person_id AND pps.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND ppf.full_name = ‘Ahad, Mr. Abdul’
GROUP BY ppf.full_name
—————————————————————————————————
but there is one more requirement on the same scenraio..
i need to capture the folliwing details in one user defined Table.

employee_name person_id start_date employed hours Day.
—————- ————— ————— ———————— ———
shailesh 111 01-mar-2009 9 SUNDAY
shailesh 111 02-mar-2009 9 Monday
shailesh 111 03-mar-2009 9 Tuesday
David 222 05-sep-2009 9 Friday
david 222 06-sep-2009 9 saturd
——-—————————————————————————————————
In the above table i need to capture the employed hours as 9 irrespective of day from his joining date to sysdate.
Here to caputre the start date ,do we need to take sequence?and employee hours is constant as 9.

Please give me rough idea.