Portal Content Reference Navigation Path

Very often you need to find out what is the Navigation Path for a given component name in PeopleSoft portal or HRMS or Financials or CRM?

If you are using 8.50 and 9.1 then you can find this information using Enterprise Components –> Find Object Navigation. You can search by component name, page name, Secondary Page name or Content Reference Name. Please note that you must be using application version 9.1 or higher. Just upgrading peopletools to 8.50 or higher will not enable this functionality.

 

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2,
                                              l_portal_reftype VARCHAR2,
                                              l_portal_objname VARCHAR2,
                                              l_level          NUMBER,
                                              l_type           VARCHAR2,
l_count_max      INTEGER DEFAULT 10)
RETURN VARCHAR2
IS
  pl_count              INTEGER := 0;
  pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname;
  pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0;
  pl_portal_label       psprsmdefn.portal_label%TYPE := ' ';
  pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' ';
  CURSOR cur_1 IS
    SELECT portal_prntobjname,
           portal_label,
           portal_seq_num
    FROM   psprsmdefn
    WHERE  portal_name = l_portal_name
           AND portal_reftype = l_portal_reftype
           AND portal_objname = pl_portal_objname;
BEGIN
  WHILE pl_count <> l_level LOOP
      pl_count := pl_count + 1;

      EXIT WHEN pl_count > l_count_max;

      OPEN cur_1;

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num
      ;

      IF cur_1%found THEN
        pl_portal_objname := pl_portal_prntobjname;
      ELSE
        pl_portal_label := ' ';

        pl_portal_seq_num := 0;

        EXIT WHEN cur_1%notfound;
      END IF;

      CLOSE cur_1;
  END LOOP;

  IF l_type = 'S' THEN
    RETURN pl_portal_seq_num;
  ELSE
    RETURN pl_portal_label;
  END IF;
END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname,
       a.portal_linkobjname,
       a.portal_seq_num                              seq,
       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')
             ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L')
       || Decode(a.portal_label, ' ', '',
                                 ' > '
                                 || a.portal_label)) navigation,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7,
       a.portal_uri_seg1                             menuname,
       a.portal_uri_seg2                             component,
       a.portal_uri_seg3                             market,
       a.portal_urltext
FROM   psprsmdefn a
WHERE  a.portal_name = 'EMPLOYEE'
       AND a.portal_reftype = 'C'
       AND a.portal_prntobjname <> ' '
--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME'
ORDER  BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S')
          ||
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')