Orgcode- Position Data

If your organization is Utilizing Position Management and running the POS006A.sqr, then you have seen the way PeopleSoft populates the ORGCODE field on Position Data.  This field is only populated for the current effective dated row on the positions. 

So if you wanted to select the current effective dated row for a position, you could simply do it like this:

SELECT * FROM PS_POSITION_DATA X WHERE X.POSITION_NBR = ? AND X.ORGCODE > ' '

But that is not the true intent of this field,  this field is used to store the organizational value for creating an Org Chart, but how can you use this field to your advantage?


Well if you run the POS006A.SQR daily then you could use this field to select all the positions that report directly and indirectly to a position.  Without using a recursive query.  You could still use a recursive query, as they are kind of fun.  But why would you...


Try writing a query like this:


  SELECT * FROM PS_POSITION_DATA WHERE ORGCODE LIKE (SELECT ORGCODE||'%' FROM PS_POSITION_DATA X WHERE X.POSITION_NBR = ? AND X.ORGCODE > ' ')  ORDER BY ORGCODE;



This will return the Position number in the ? and all the positions that report directly up through it.