We help IT Professionals succeed at work.
Get Started

pl/sql supervisor query -- need effective dates for changes

Last Modified: 2013-12-07
We have a supervisor hierarchy query which returns the Executive each employee runs up to.  Our current version is pulling the effective date of the employee while we really need the effective date of the change for the supervisor hierarchy.  If an employee's at the 7th level and somone moved or inserted at the 3rd level, it still wants to use the employee's effective dates.  How can we get around this and use the effective date of the 3rd level change? Or the effective date someone's hierarchy changes so we can correctly join this table with another table?

        SELECT person_id
           , rec_month.oracle_date
           , effective_start_date
           , effective_end_date
           , supervisor_id
           , hier_level
           , cycle
           , leaf
           , PathByID
           , substr(path, instr(path,'/',1,1)+1, instr(path,'/',1,2)-instr(path,'/',1,1)-1) Executive1
           , substr(path, instr(path,'/',1,2)+1, instr(path,'/',1,3)-instr(path,'/',1,2)-1) Executive2
           , substr(path, instr(path,'/',1,3)+1, instr(path,'/',1,4)-instr(path,'/',1,3)-1) Executive3
           , substr(path, instr(path,'/',1,4)+1, instr(path,'/',1,5)-instr(path,'/',1,4)-1) Executive4
           , substr(path, instr(path,'/',1,5)+1, instr(path,'/',1,6)-instr(path,'/',1,5)-1) Executive5
           , substr(path, instr(path,'/',1,6)+1, instr(path,'/',1,7)-instr(path,'/',1,6)-1) Executive6
           , substr(path, instr(path,'/',1,7)+1, instr(path,'/',1,8)-instr(path,'/',1,7)-1) Executive7
           , substr(path, instr(path,'/',1,8)+1, instr(path,'/',1,9)-instr(path,'/',1,8)-1) Executive8
           , substr(path, instr(path,'/',1,9)+1, instr(path,'/',1,10)-instr(path,'/',1,9)-1) Executive9
           , substr(path, instr(path,'/',1,10)+1, instr(path,'/',1,11)-instr(path,'/',1,10)-1) Executive10
           , substr(path, instr(path,'/',1,11)+1, instr(path,'/',1,12)-instr(path,'/',1,11)-1) Executive11
        FROM (      
               select x.*, level hier_level, CONNECT_BY_ISCYCLE Cycle,
               CONNECT_BY_ISLEAF Leaf, SYS_CONNECT_BY_PATH(x.person_id, '|') PathByID,
               SYS_CONNECT_BY_PATH((select papf.person_id from xxobi_person_names_ds_mv papf where papf.person_id = x.person_id  and rec_month.oracle_date between papf.effective_start_date and papf.effective_end_date), '/') Path  
               from distinct_super_path2 x
               connect by nocycle prior x.person_id = x.supervisor_id
Watch Question
Production Manager / Application Support Manager
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE