Could someone help with building a Hierarchy with History from Peoplesoft table.
PSFT TABLE structure
Employee | Manager | Effective Date |
ABC | NAT | 1-Jan-15 |
DEF | ANN | 1-Jan-13 |
DEF | NAT | 1-Apr-16 |
NAT | JEN | 1-Jan-10 |
ANN | JEN | 1-Jan-05 |
The hierarchy should be like
ABC-->NAT-->JEN 1-JAN-2015 1-JAN-2100
DEF-->ANN-->JEN 1-JAN-2013 31-MAR-2016
DEF-->NAT-->JEN 1-APR-2016 1-JAN-2100
NAT-->NAT-->JEN 1-JAN-2010 1-JAN-2100
ANN-->ANN-->JEN 1-JAN-2005 1-JAN-2100
I am trying to create a reporting hierarchy with dates and preserving history.