Human Capital Management - EBS (MOSC)

MOSC Banner

R12 HCM merge assignment periods with the same status (Active/Leave)?

I'm looking for something that concatenates consecutive date ranges from per_all_assignments_f if the assignment_status_type_id has the same value and the effective_start_date and effective_end_dates are consecutive for each person_id.


For example:

Assignment ID Effective Start Date Effective_end Date assignment_status_type_id

1234 1/1/2020 1/15/2020 1

1234 1/16/2020 1/31/2020 1

1234 2/1/2020 2/20/2020 2

1234 3/1/2020 3/10/2020 1

1234 3/11/2020 12/31/4712 1

   

Would be returned as:

Assignment ID Effective Start Date Effective_end Date assignment_status_type_id

1234 1/1/2020 1/31/2020 1

1234 2/1/2020 2/20/2020 2

1234 3/1/2020 12/31/4712 1

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center