From Process To Peoplesoft Page

Last week we discussed finding the best Peoplesoft page, and the menu path to that page, for viewing and updating a particular database table.  We have a similar need to find the menu path to the run control page for a particular batch process.

Find The Component

When we create a process definition, we specify one or more components on which it should appear.  The SQL to find the component(s) that support the process is simple.

select B.PNLGRPNAME from PS_PRCSDEFNPNL B where B.PRCSNAME = 'PROCESS_ABC'
 

Find The Path

We discussed the method for finding the portal hierarchy path to a component last week.

select lpad(' ', 10*level-10) || A.PORTAL_LABEL
from PSPRSMDEFN A
start with A.PORTAL_URI_SEG2 = 'COMPONENT_ABC'
and A.PORTAL_URI_SEG3 in ('GBL','USA')
connect by prior A.PORTAL_PRNTOBJNAME = A.PORTAL_OBJNAME
and A.PORTAL_NAME = 'EMPLOYEE'

Combine them to get this SQL.

select lpad(' ', 10*level-10) || A.PORTAL_LABEL
from PSPRSMDEFN A
start with A.PORTAL_URI_SEG2 in
(select B.PNLGRPNAME from PS_PRCSDEFNPNL B where B.PRCSNAME = 'PROCESS_ABC')
and A.PORTAL_URI_SEG3 in ('GBL','USA')
connect by prior A.PORTAL_PRNTOBJNAME = A.PORTAL_OBJNAME
and A.PORTAL_NAME = 'EMPLOYEE'

2 Comments

  1. meghana says:

    question: ihave b_unit accordingly the report is generated. I need to writ the data to xcel sheet, need to break acc to dept id also
    2.

    • administrator says:

      Meghana, please tell me more about this situation, and I’ll try to answer your question.