When I discuss data with my colleagues in the Human Resources and Payroll departments, we speak different languages. I talk about tables and columns. They talk about menus, tabs, and pages. “You need to change EEO4CODE in PS_JOBCODE_TBL,” I’ll say. They’ll reply, “You mean, select Setup HRMS, Foundation Tables, Job Attributes, Job Code Table, and the Job Code Profile tab?” There are pages I’ve never visited and they’ve never used a SQL browser.
Find The Pages
The first step in breaking the language barrier is to find the page on which we enter the data. Application Designer stores pages in tables, so we can find every page that contains a field from the JOBCODE_TBL record with this SQL.
select PNLNAME, FIELDNAME
from PSPNLFIELD
where RECNAME = 'JOBCODE_TBL'
and FIELDNAME <> ' '
Note that pages were called panels (pnl) before Peoplesoft Internet Architecture was introduced with version 8.
This SQL returns far too many irrelevent rows (464 rows with 199 distinct PNLNAME in my installation). The jobcode table is so interesting that many pages show data from it (175 pages show DESCR, 37 pages show DESCRSHORT, 12 pages show SETID, etc.). We want the pages that support data entry to PS_JOBCODE_TBL, not the ones that display its contents.
Next we consider the FIELDUSE. This column of PSPNLFIELD gives us information about how the field is used on the page. FIELDUSE is a numeric field that Application Designer uses as a bit map. It adds all the values that we, the designers, chose for that field.
1 – Display Only
2 – Invisible
4 – Display Only Appearance is Text Only
8 – Display Control Field
16 – Related Field
32 – Multicurrency Field
1024 – Show Label
2048 – Freeze Grid Column
262144 – Hyperlink
If a field is “display only,” we cannot enter data to it. Therefore we’re only interested in even-numbered values of FIELDUSE.
select PNLNAME, FIELDNAME
from PSPNLFIELD
where RECNAME = 'JOBCODE_TBL'
and FIELDNAME <> ' '
and mod(FIELDUSE, 2) = 0
This SQL returns 125 rows with seven distinct PNLNAME. We’ve narrowed the selection and improved its quality; going from 2.3 JOBCODE_TBL fields per page (464 / 199) to 17.9 fields per page (125 / 7).
Find The Components
The first step in breaking the language barrier is to find the component that contains the page or pages on which we enter the data. Application Designer stores components in tables, so we can find the components that contain these pages with this SQL.
select distinct PG.PNLGRPNAME
from PSPNLGROUP PG, PSPNLFIELD PF
where PG.MARKET in ('GBL','USA')
and PF.PNLNAME = PG.PNLNAME
and PF.RECNAME = 'JOBCODE_TBL'
and PF.FIELDNAME <> ' '
and mod(PF.FIELDUSE, 2) = 0
Note that components were were called panel groups (pnlgrp) before Peoplesoft Internet Architecture was introduced with version 8. Also, I restrict the components to the markets I use. U.S. federal government users might choose GBL, USA, and USF. Canadian users might choose GBL and CAN.
This SQL returns ten rows, which illustrates that the some components have more than one page and some pages belong to more than one component. Let’s check the results more closely with SQL that counts the number of fields offered by each component.
select PG.PNLGRPNAME, count(*)
from PSPNLGROUP PG, PSPNLFIELD PF
where PG.MARKET in ('GBL','USA')
and PF.PNLNAME = PG.PNLNAME
and PF.RECNAME = 'JOBCODE_TBL'
and PF.FIELDNAME <> ' '
and mod(PF.FIELDUSE, 2) = 0
group by PG.PNLGRPNAME
Nine of the components have one row each. Component JOB_CODE_TBL stands out with 122 rows. That’s the one we want, but how to we get it?
Find The Best Components
We all know how to put the rows with the current effective dates using SQL like this.
select *
from PS_JOBCODE_TBL JT
where JT.EFFDT = (select max(EFFDT)
from PS_JOBCODE_TBL
where SETID = JT.SETID
and JOBCODE = JT.JOBCODE
and EFFDT <= sysdate)
We can select the maximum count similarly using a having clause in SQL like this.
select PG1.PNLGRPNAME
from PSPNLGROUP PG1, PSPNLFIELD PF1
where PG1.MARKET in ('GBL','USA')
and PF1.PNLNAME = PG1.PNLNAME
and PF1.RECNAME = 'JOBCODE_TBL'
and PF1.FIELDNAME <> ' '
and mod(PF1.FIELDUSE, 2) = 0
group by PG1.PNLGRPNAME
having count(*) = (select max(count(*))
from PSPNLGROUP PG2, PSPNLFIELD PF2
where PG2.MARKET in ('GBL','USA')
and PF2.PNLNAME = PG2.PNLNAME
and PF2.RECNAME = 'JOBCODE_TBL'
and PF2.FIELDNAME <> ' '
and mod(PF2.FIELDUSE, 2) = 0
group by PG2.PNLGRPNAME)
That’s a lot of typing for an interim result; we’re looking for the menu path, not just the component, remember? I don’t like the hardcoding either. Typing GBL and USA twice is bad enough, but those choices won’t change much at any given Peoplesoft site. Typing the search RECNAME twice, with the potential of misspellings or only remembering one of them, is more troublesome. Let’s make a encapsulate our setup work in a view.
Define a record (REC2PNLGRP) with three fields, RECNAME, PNLGRPNAME, and FIELDNUM. Peoplesoft uses FIELDNUM for the sequence number of a field in a record layout. We’ll use it for the number of fields from a particular record definition found on a particular component. This view will use the following SQL.
select PF.RECNAME, PG.PNLGRPNAME, count(*)
from PSPNLGROUP PG, PSPNLFIELD PF, PSRECDEFN RD
where PG.MARKET in ('GBL','USA')
and PF.PNLNAME = PG.PNLNAME
and MOD(PF.FIELDUSE, 2) = 0
and PF.FIELDNAME <> ' '
and RD.RECNAME = PF.RECNAME
and RD.RECTYPE = 0
group by PG.PNLGRPNAME, PF.RECNAME
Note that this view will support all records, not specifically JOBCODE_TBL. However, it checks RECTYPE in PSRECDEFN to restrict the selection to SQL table records; it excludes views, dynamic view, derived records, state tables, temporary tables, and query views.
Now we can use this view for any record like this.
select *
from PS_REC2PNLGRP R
where RECNAME = 'JOBCODE_TBL'
and FIELDNUM = (select max(FIELDNUM)
from PS_REC2PNLGRP
where RECNAME = R.RECNAME)
You may be concerned that more than one component has the same, maximum number of data entry fields from our record. You can let this SQL return all of them, or pick one arbitrarily by adding this subselection at the end of this SQL.
and PNLGRPNAME = (select max(PNLGRPNAME)
from PS_REC2PNLGRP
where RECNAME = R.RECNAME
and FIELDNUM = R.FIELDNUM)
Find The Menu Paths
In my January 10, 2010 blog entry, Little Known SQL Syntax, I wrote about using recursion in Oracle’s database. I gave an example of a select statement that returns the entire menu hierarchy of a portal. Here is a modified version of that SQL that finds the menu paths for a particular component.
select lpad(' ', 10*level-10) || A.PORTAL_LABEL
from PSPRSMDEFN A
start with A.PORTAL_URI_SEG2 = 'JOB_CODE_TBL'
and A.PORTAL_URI_SEG3 in ('GBL','USA')
connect by prior A.PORTAL_PRNTOBJNAME = A.PORTAL_OBJNAME
and A.PORTAL_NAME = 'EMPLOYEE'
We combine the menu paths with the selection of components as follows.
select lpad(' ', 10*level-10) || A.PORTAL_LABEL
from PSPRSMDEFN A
start with A.PORTAL_URI_SEG2 in
(select R.PNLGRPNAME from PS_REC2PNLGRP R
where RECNAME = 'JOBCODE_TBL'
and FIELDNUM = (select max(FIELDNUM)
from PS_REC2PNLGRP
where RECNAME = R.RECNAME))
and A.PORTAL_URI_SEG3 in ('GBL','USA')
connect by prior A.PORTAL_PRNTOBJNAME = A.PORTAL_OBJNAME
and A.PORTAL_NAME = 'EMPLOYEE'
This is still a lot of typing, so I’ll leave you with suggestions for two more simplifying views. The first view is a subset of PSPRSMDEFN with only the rows that have the appropriate PORTAL_NAME. The second view is a subset of PS_REC2PNLGRP with only the RECNAME and PNLGRPNAME fields, and only the rows with the maximum FIELDNUM values. Unfortunately, I have not found a way to encapsulate the “start with … connect by prior …” code in a view.
Recent Comments