SQL to lock all objects in a Project

How to lock all the objects in a project, very useful when migrating mega projects.

INSERT INTO SYSADM.PSCHGCTLLOCK
SELECT
'yourID'
, A.OBJECTTYPE
, A.OBJECTID1
, A.OBJECTVALUE1
, A.OBJECTID2
, A.OBJECTVALUE2
, A.OBJECTID3
, A.OBJECTVALUE3
, A.OBJECTID4
, A.OBJECTVALUE4
, SYSDATE
, A.PROJECTNAME
, ' '
, ' '
FROM SYSADM.PSPROJECTITEM A
WHERE PROJECTNAME = 'projectName'
AND NOT EXISTS
(SELECT 'X'
FROM SYSADM.PSCHGCTLLOCK B
WHERE B.OBJECTTYPE = A.OBJECTTYPE
AND B.OBJECTID1 = A.OBJECTID1
AND B.OBJECTVALUE1 = A.OBJECTVALUE1
AND B.OBJECTID2 = A.OBJECTID2
AND B.OBJECTVALUE2 = A.OBJECTVALUE2)
AND OBJECTTYPE <> 30 -- (SQL for views)

Finding a Component in the Portal Registry

define Component_Name = 'GU_EX_SCH_DOWNLOAD';

select distinct LEVEL, PORTAL_LABEL
from PSPRSMDEFN
where PORTAL_LABEL <> 'Root'
connect by PORTAL_OBJNAME = prior PORTAL_PRNTOBJNAME
start with PORTAL_URI_SEG2 = &Component_Name
order by LEVEL desc;

undefine Component_Name;

Fun with the DoModalComponent function.

The example given in PeopleBooks is misleading. Read the description carefully -

The first three parameters are straighforward, and can be drag-dropped from the menu definition in your project (shortcut hint there).

The fourth parameter is a little different:

component_item_name

The component item name of the page to be displayed on top of the modal component when it displays. The component item name is specified in the component definition.

Note "The component item name is specified in the component definition"? Open up the component and find the page you want to navigate to.

For this parameter use the page's "Item Name", not the "Page Name".

It's always a bit nasty when the example is misleading and the explanation slightly obscured, I mean who reads the doco really, really carefully? ; )

Synchronising Delivered & Customised 1-1 tables

UPDATE (years later): I ask myself why not just use another scroll level below the delivered record/scroll containing the sibling record? PeopleSoft will handle the inserts and updates automagically.

Following is likely wrong, but left for reference.

Rather than add custom fields to a PeopleSoft delivered record it is better to create a new record with the same keys that will serve to store new, custom fields. This table and the delivered table will have a 1-1 relationship and there will be less/no work for future upgrades.

To display and synchronise the data on this new table a work record is used on the page. The example below is using Griffith University (GU) records and delivered PS records.

1. Set default values on this derived record so that there will always be a row in the new table that matches the original table. (Someone will try to join to the new table and have no data at some stage). That is default the key values of the work record to those of the delivered record.
   
   
2. RowInit PeopleCode (GU Work Rec.RI) displays the synchronised data on the Work Record:
   
   SQLExec("SELECT SOME_FIELD1, SOME_FIELD2 FROM GU_RECORD WHERE KEYFIELD1 = :1", PS_RECORD.KEYFIELD1.Value, GU_WORK_RECORD.SOME_FIELD1.Value);
   
   
3. SavePostChange PeopleCode (Component.PS_RECORD.SPC) then synchronises the tables with a 1-1 relationship (updated 22.10.2007):
   
   Local Record &recGU_RECORD = CreateRecord(Record.GU_RECORD);
   
   /* Populate. */
   GetRecord(Record.GU_WORK_RECORD).CopyFieldsTo(&recGU_RECORD);
   GetRecord().CopyFieldsTo(&recGU_RECORD);
   
   /* Delete if required. */
   If GetRecord().IsDeleted Then
   &recGU_RECORD.Delete();
   Else
   
   /* Default (specific) values if required. */
   If None(&recGU_RECORD.SOME_FIELD.Value) Then
   &recGU_RECORD.GU_RECORD.SetDefault();
   End-If;
   
   /* Insert/update. */
   &saved = &recGU_RECORD.Save();
   
   End-If;
   
   
   

Setup and run 3C Communications

Document on how to setup and run the new v9 3C communications. (Written by Kevin Spletter)

http://docs.google.com/Doc?id=df48r26b_3ffkjg4

Constrained

just what is the restraint talking about?

select * from dba/user_constraints
where constraint_name like 'nnn';

SQL to find where a record is used

This SQL will return all pages, including secondary/subpages, and components that use a given record:

-- all panels that have record and the associated panelgroup
select distinct
a.pnlname as panel, 'subpage' as type
, c.pnlgrpname as component
, a.recname as record
from
sysadm.pspnlfield a
, sysadm.psrecdefn b
, sysadm.pspnlgroup c
where a.recname = 'GU_EX_WORK'
and b.recname = a.recname
--and b.rectype = 2
and c.pnlname = a.pnlname
union
select distinct
a.pnlname as page, 'subpage' as type
, c.pnlgrpname as component
, a.recname as record
from
sysadm.pspnlfield a
, sysadm.pspnlfield b
, sysadm.pspnlgroup c
where a.recname = 'GU_EX_WORK'
and b.subpnlname = a.pnlname
and c.pnlname = b.pnlname
;