Wednesday, November 10, 2010

Peoplesoft Navigation of the setup components. - In simple query.


PS_PTLT_COMP_NAV – Define Components Navigation Setup record. - The record stores the PTLT_TASK_CODE which is the component name followed by the market name “.GBL”/Global extension

There is a catch –It doesn’t work for the transaction components like JOB_DATA.
This record is used in setup manager and hence doesn’t have details for the transactional components.

Ganesh Muralidhar

Below query gives for all tables (including hidden).. based the level of navigation (for example Main Menu --> Careers, for this two tables should be joined.. so if you are not getting the results, you have to keep reducing the no of joins :)) it is configured the self join table's count should be increased/decreased...

Oracle------
SELECT D.PORTAL_LABEL ' -->' C.PORTAL_LABEL' -->' B.PORTAL_LABEL ' -->' A.PORTAL_LABEL
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C,PSPRSMDEFN DWHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAMEAND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAMEAND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAMEAND A.PORTAL_URI_SEG2 ='JOB_DATA_HIRE'


MSSQL-----
SELECT D.PORTAL_LABEL+ ' -->'+C.PORTAL_LABEL+' -->'+ B.PORTAL_LABEL +' -->' + A.PORTAL_LABEL
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C,PSPRSMDEFN DWHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAMEAND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAMEAND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAMEAND A.PORTAL_URI_SEG2 ='JOB_DATA_HIRE'


Manivannan Ramar

2 comments:

  1. Lovely sharp post. Never thought that it was this easy. Extolment to you!

    ReplyDelete
  2. Awesome posting. Benar-benar menikmati membaca posting blog anda.

    ReplyDelete

Total Pageviews

Copyright © Tech & Management Notes