krishnamraju

  SELECT A.EMPLID , EXTRACT(YEAR FROM (Last_Day(Trunc(SYSDATE)) - LAST_HIRE_DT) YEAR TO MONTH ) EXYR       , EXTRACT(MONTH FROM (Last_Day(Trunc(SYSDATE)) - LAST_HIRE_DT) YEAR TO MONTH ) EXMON ,A.EFFDT, A.EMPL_RCD, A.EFFDT, A.EFFSEQ, A.JOBCODE, A.deptid, A.HR_STATUS, A.action, A.action_reason, A.SUPERVISOR_ID    FROM PS_JOB A     WHERE a.effdt =(select Max(b.effdt) from ps_job b where a.emplid= b.emplid) AND per_org='EMP' and A.effseq = (select Max(c.effseq) from ps_job c where c.emplid = a.emplid and c.effdt =a.effdt) and a.empl_rcd =(Select Max(d.empl_rcd) from ps_job d where d.emplid =a.emplid ) and emplid ='***********' 

SELECT P.PROJECTNAME, P.PROJECTDESCR, P.LASTUPDDTTM, P.LASTUPDOPRID,(SELECT COUNT(*) FROM PSPROJECTITEM I WHERE I.PROJECTNAME = P.PROJECTNAME AND I.OBJECTVALUE1 NOT LIKE '%XXX%') AS PS_OBJECTS_CUSTOMISED FROM PSPROJECTDEFN P WHERE P.LASTUPDOPRID NOT IN ('PS','PPLTOOLS') ORDER BY P.LASTUPDDTTM DESC;

SQL to find the Navigation of a PeopleSoft Process SQL to find the navigation to the run control page by Process Name in PeopleSoft. SELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || ' > ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2 AND E.PRCSTYPE = F.PRCSTYPE AND E.PRCSNAME = F.PRCSNAME AND D.PORTAL_NAME = 'EMPLOYEE' AND D.PORTAL_REFTYPE = 'C' AND E.PNLGRPNAME > ' ' AND A.PORTAL_NAME = 'EMPLOYEE' AND A.PORTAL_OBJNAME = D...

.  SELECT DISTINCT RECNAME FROM PSRECDEFN      WHERE (    RECNAME IN (SELECT RECNAME FROM PSPNLFIELD WHERE PNLNAME IN (SELECT DISTINCT B.PNLNAME                 FROM PSPNLGROUP A, PSPNLFIELD B WHERE (A.PNLNAME = B.PNLNAME OR A.PNLNAME = B.SUBPNLNAME)                  AND A.PNLGRPNAME = 'JOB_DATA'))              OR RECNAME IN (SELECT RECNAME FROM PSPNLFIELD WHERE PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME                 FROM PSPNLGROUP A, PSPNLFIELD B WHERE (A.PNLNAME = B.PNLNAME OR A.PNLNAME = B.SUBPNLNAME)                 AND A.PNLGRPNAME = 'JOB_DATA'))            )

SELECT SEARCHRECNAME, ADDSRCHRECNAME FROM PSBCDEFN WHERE BCNAME = 'XXXX_CI';

      SELECT distinct RECNAME         FROM PSBCITEM        WHERE BCNAME = 'XXXX_CI';

      SELECT BCPGNAME,MARKET,MENUNAME        FROM PSBCDEFN        WHERE BCNAME = 'XXXX_CI';