SQL

Identify Records with a Specific Subrecord

A subrecord in PeopleSoft is a way to group fields together and place them on a record, view, or a temp table. Here is a query you can use to identify records that have a specific subrecord.

SELECT a.recname, a.fieldname, b.rectype, b.objectownerid
  FROM psrecfield a, psrecdefn b
 WHERE a.recname = b.recname
   AND a.fieldname = 'SUB-RECORD-NAME';

PeopleSoft Permission List Queries

A section to collect and organize PeopleSoft permission lists related queries.

User IDs assigned to a Permission List

A query to list all user IDs that are assigned to a specific permission list.

SELECT   c.roleuser AS USER_IDs
    FROM psclassdefn a, psroleclass b, psroleuser c
   WHERE a.classid = b.classid
     AND b.rolename = c.rolename
     AND a.classid = :permissionlist
GROUP BY c.roleuser;

Database Table Space Statistics (Free, Used, and Percent Used)

An SQL to help gathering storage tablespace statistics. The SQL will list all tablespace names in a particular database along with available, used and percent used storage space in MB.

SELECT   a.tablespace_name, (a.BYTES / 1024) || 'MB' AS used,
         (b.BYTES / 1024) || 'MB' AS free,
         (b.largest / 1024) || 'MB' AS largest_block,
         ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used
    FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) a,

Oracle Database Version

A Very simple SQL to return Oracle's database version you are currently in.

SELECT *
  FROM v$version
 WHERE ROWNUM = 1;

BANNER
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

PeopleCode to AutoIncrement an AlphaNumeric Transaction Number

I have code to auto-increment an alphanumeric field, using SQL calls. It is specific to a 4-character field, but can easily be adapted to different length fields.

SQLExec("SELECT decode(substr(oldvalue,2,3),'ZZZ', TRANSLATE(UPPER(SUBSTR(oldvalue,1,1)),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,1,1)) ||decode(substr(oldvalue,3,2),'ZZ',TRANSLATE(UPPER(SUBSTR(oldvalue,2,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,2,1))||decode(substr(oldvalue,4,1),'Z', TRANSLATE(UPPER(SUBSTR(oldvalue,3,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0'),substr(oldvalue,3,1))||TRANSLATE(UPPER(SUBSTR(oldvalue,4,1)), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0') FROM dual, (select :1 as oldvalue from dual)", &priorvalue, &newvalue);
If &newvalue = "0000" Then
   &bOverrun_Error = True;
End-If;

List Dates in a month

Here is an SQL that will list all dates for a particular month.

SELECT       TRUNC (TO_DATE (:target_date, 'MM/DD/YYYY'),
                    'MM'
                   )
           + LEVEL
           - 1 AS days_in_month
      FROM DUAL
CONNECT BY LEVEL <=
              EXTRACT (DAY FROM LAST_DAY (TRUNC (TO_DATE (:target_date,
                                                          'MM/DD/YYYY'
                                                         ),
                                                 'MM'
                                                )

Find Navigation of Page from Component Name

Many times we came across situation where we have Page name but we dont know the navigation for this page in Portal.

Following SQL makes the job easy for us.
Execute the following SQL in Database.

SELECT REPLACE(navigation,'',' > ') "PIA Navigation"
, url
, MENU_NAME
, COMPONENT_NAME
, portal_objname
, portal_prntobjname
, portal_uri_seg3
,portal_label
FROM (SELECT SYS_CONNECT_BY_PATH (a.portal_label,'>>') navigation
, '/EMPLOYEE/ERP/c/' || a.portal_uri_seg1 || '.' || a.portal_uri_seg2 || '.' || a.portal_uri_seg3 url
,  a.portal_uri_seg1  MENU_NAME

Component Interface Field Details

This SQL returns details of the fields like DESCRIPTION, FIELD TYPE, FIELD LENGTH, KEY TYPE , REQUIRED, DEFAULT VALUE, EDIT , PROMPT TABLE etc.
in CI just replace ': COMPONENT INTERFACE NAME' with your Component Interface Name

SELECT
  c.bcname AS component_interface,
  a.recname AS record,
  a.fieldname AS FIELD,
  d.shortname AS description,
  CASE
WHEN b.fieldtype = 0 THEN
  'CHARACTER'
WHEN b.fieldtype = 1 THEN
  'LONG_CHARACTER'
WHEN b.fieldtype = 2 THEN
  'NUMBER'
WHEN b.fieldtype = 3 THEN
  'SIGNED_NBR'
WHEN b.fieldtype = 4 THEN

Component Permission List Query

An SQL that will identify permission lists along with permission lists description assigned to a specific component.

SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
         CLASS.classdefndesc permission_desc
    FROM psauthitem auth,
         psmenudefn menu,
         psmenuitem menuitm,
         pspnlgroup comp,
         pspnlgrpdefn compdfn,
         psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname