Posts Tagged ‘PLSQL’
PeopleSoft Quick-Tip: Find all objects in a project
Just a SQL quick-tip for an overview of all the objects in your project.
This is one possible SQL statement to get the PeopleSoft objects from your PeopleSoft project:
SELECT (CASE OBJECTTYPE WHEN 0 THEN 'Record'
WHEN 1 THEN 'Index'
WHEN 2 THEN 'Field'
WHEN 3 THEN 'Field Format'
WHEN 4 THEN 'Translate Value'
WHEN 5 THEN 'Pages'
WHEN 6 THEN 'Menus'
WHEN 7 THEN 'Components'
WHEN 8 THEN 'Record PeopleCode'
WHEN 9 THEN 'Menu PeopleCode'
WHEN 10 THEN 'Query'
WHEN 11 THEN 'Tree Structures'
WHEN 12 THEN 'Trees'
WHEN 13 THEN 'Access group'
WHEN 14 THEN 'Color'
WHEN 15 THEN 'Style'
WHEN 16 THEN 'N/A'
WHEN 17 THEN 'Business process'
WHEN 18 THEN 'Activity'
WHEN 19 THEN 'Role'
WHEN 20 THEN 'Process Definition'
WHEN 21 THEN 'Server Definition'
WHEN 22 THEN 'Process Type Definition'
WHEN 23 THEN 'Job Definitions'
WHEN 24 THEN 'Recurrence Definition'
WHEN 25 THEN 'Message Catalog'
WHEN 26 THEN 'Dimension'
WHEN 27 THEN 'Cube Definitions'
WHEN 28 THEN 'Cube Instance Definitions'
WHEN 29 THEN 'Business Interlink'
WHEN 30 THEN 'SQL'
WHEN 31 THEN 'File Layout Definition'
WHEN 32 THEN 'Component Interfaces'
WHEN 33 THEN 'AE program'
WHEN 34 THEN 'AE section'
WHEN 35 THEN 'Message Node'
WHEN 36 THEN 'Message Channel'
WHEN 37 THEN 'Message'
WHEN 38 THEN 'Approval rule set'
WHEN 39 THEN 'Message PeopleCode'
WHEN 40 THEN 'Subscription PeopleCode'
WHEN 41 THEN 'N/A'
WHEN 42 THEN 'Component Interface PeopleCode'
WHEN 43 THEN 'AE PeopleCode'
WHEN 44 THEN 'Page PeopleCode'
WHEN 45 THEN 'Page Field PeopleCode'
WHEN 46 THEN 'Component PeopleCode'
WHEN 47 THEN 'Component Record PeopleCode'
WHEN 48 THEN 'Component Rec Fld PeopleCode'
WHEN 49 THEN 'Image'
WHEN 50 THEN 'Style sheet'
WHEN 51 THEN 'HTML'
WHEN 52 THEN 'Not used'
WHEN 53 THEN 'Permission List'
WHEN 54 THEN 'Portal Registry Definitions'
WHEN 55 THEN 'Portal Registry Structures'
WHEN 56 THEN 'URL Definitions'
WHEN 57 THEN 'Application Packages'
WHEN 58 THEN 'Application Package Peoplecode'
WHEN 59 THEN 'Portal Registry User Homepage'
WHEN 60 THEN 'Problem Type'
WHEN 61 THEN 'Archive Templates'
WHEN 62 THEN 'XSLT'
WHEN 63 THEN 'Portal Registry User Favorite'
WHEN 64 THEN 'Mobile Page'
WHEN 65 THEN 'Relationships'
WHEN 66 THEN 'Component Interface Property Peoplecode'
WHEN 67 THEN 'Optimization Models'
WHEN 68 THEN 'File References'
WHEN 69 THEN 'File Type Codes'
WHEN 70 THEN 'Archive Object Definitions'
WHEN 71 THEN 'Archive Templates (Type 2)'
WHEN 72 THEN 'Diagnostic Plug In'
WHEN 73 THEN 'Analytic Model'
ELSE 'UNKNOWN OBJECT TYPE' END) AS OBJECTTYPE
, RTRIM(RTRIM(OBJECTVALUE1) || '.' || RTRIM(OBJECTVALUE2) || '.' || RTRIM(OBJECTVALUE3) || '.' || RTRIM(OBJECTVALUE4),'.') OBJECTNAAM
FROM PSPROJECTITEM
WHERE PROJECTNAME = 'RFC123456' --- Replace with your project name...
ORDER BY OBJECTTYPE, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3;
Just note the following line and replace it with the desired project name
WHERE PROJECTNAME = 'RFC123456' --- Replace with your project name...
The output could be something like this, for a small project:
OBJECTTYPE OBJECTNAAM
AE program TEMP
AE section TEMP.MAIN
Job Definitions TEMP
Process Definition Application Engine.TEMP
Recurrence Definition Something about TEMP
AE PeopleCode TEMP.MAIN GBLdefault 1900-01-01.Step01.OnExecute
Very useful for documenting purposes, for example!
Disclaimer: this code is in use with us for a long time, but the origin of this code may very well be the interwebs…so hereby credit where credit’s due.
What technology should we be using for our clients?
As an attendant to the latest LOUD meeting, during the speeches we were confronted with 2 opposing statements. The first speaker stated we should go back to the roots of application development using the rich technology databases nowadays possess, like PL/SQL and triggers. The second speech was about the new development tool from Oracle, WebCenter and Web 2.0 which has all functionality in the application instead of the database.
n
PeopleSoft Quick-Tip: Find navigationpath of a component
Getting lost while searching for the correct navigation path of a (custom) page or component?
Here is a quick tip to get a a breadcrumbs description of the navigation path of a specific component:
DECLARE
CURSOR c_comp
(p_comp IN VARCHAR2)
IS
SELECT DISTINCT portal_prntobjname
FROM psprsmdefn
WHERE portal_uri_seg2 = p_comp
AND portal_cref_usgt = 'TARG';
CURSOR c_object
( p_comp IN VARCHAR2
, p_object IN VARCHAR2)
IS
SELECT portal_objname
FROM psprsmdefn
WHERE portal_uri_seg2 = p_comp
AND portal_cref_usgt = 'TARG'
AND portal_prntobjname = p_object
AND portal_objname <> 'PORTAL_ROOT_OBJECT';
g_pad VARCHAR2(20000);
g_lan ps_portal_obj_lng.LANGUAGE_CD%TYPE := 'DUT';
g_object psprsmdefn.portal_objname%TYPE := 'RC_CASE_HD_SS_RPT'; --Replace component name...
l_object psprsmdefn.portal_objname%TYPE;
FUNCTION find_portal_name(p_portal IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR c_object_name
(p_object IN VARCHAR2)
IS
SELECT *
FROM ps_portal_objects
WHERE portal_objname = p_object;
CURSOR c_object_name_lng
( p_object IN VARCHAR2
, p_lan IN VARCHAR2)
IS
SELECT *
FROM ps_portal_obj_lng
WHERE portal_objname = p_object
AND language_cd = p_lan;
l_portal_name ps_portal_objects.portal_label%TYPE;
BEGIN
FOR r_object_name_lng IN c_object_name_lng(p_portal, g_lan)
LOOP
l_portal_name := r_object_name_lng.portal_label;
END LOOP;
IF l_portal_name IS NULL THEN
FOR r_object_name IN c_object_name(p_portal)
LOOP
l_portal_name := r_object_name.portal_label;
END LOOP;
END IF;
RETURN(l_portal_name);
END;
PROCEDURE search_and_find
(p_object IN VARCHAR2)
IS
CURSOR c_object
(p_object IN VARCHAR2)
IS
SELECT DISTINCT portal_objname
, portal_prntobjname
FROM psprsmdefn
WHERE portal_objname = p_object
AND portal_objname <> 'PORTAL_ROOT_OBJECT';
BEGIN
FOR r_object IN c_object(p_object)
LOOP
IF g_pad IS NULL THEN
g_pad := find_portal_name(r_object.portal_objname);
ELSE
g_pad := find_portal_name(r_object.portal_objname) || ' - ' || g_pad;
END IF;
search_and_find(r_object.portal_prntobjname);
END LOOP;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Path(s) found for component: ' || g_object || ', Language: ' || g_lan);
FOR r_comp IN c_comp(g_object)
LOOP
g_pad := NULL;
OPEN c_object(g_object, r_comp.portal_prntobjname);
FETCH c_object
INTO l_object;
CLOSE c_object;
search_and_find(l_object);
DBMS_OUTPUT.PUT_LINE('Root - ' || g_pad);
END LOOP;
END;
Just note the following line and replace it with the desired component name
g_object psprsmdefn.portal_objname%TYPE := 'RC_CASE_HD_SS_RPT'; --Replace component name...
Note: We’re using this code for PopelTools 8.47 and it’s working fine. For PeopleSoft 9.1 and above you can use the vanilla feature as described here: Path to in PeopleSoft 9.1 with tools 8.50 and higher