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
;

Collections - SQL to create new from old

For Office of Research at Griffith University - Code to rollover an existing Collection to a new Collection keeping the existing one's Questions and Responses.

-- GUR_COLL_RES
insert into sysadm.ps_gur_coll_res
select
'RQF_2007'
, to_date('08/05/2007', 'dd/mm/yyyy')
, EMPLID
, 'NEW'
, ' '
, 0
, ''
from sysadm.ps_gur_coll_res
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy')
;


-- GUR_COLL_RESDTL
DECLARE
CURSOR PS_CURSOR IS
SELECT
'RQF_2007' as GUR_COLLECT_CD,
to_date('08/05/2007', 'dd/mm/yyyy') as GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
GUR_FLD_EVIDENCE,
GUR_FLD_RETAINED,
GUR_FLD_EVI_RET,
GUR_RESPONSE_LNG
FROM sysadm.PS_GUR_COLL_RESDTL
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy');
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO sysadm.PS_GUR_COLL_RESDTL (
GUR_COLLECT_CD,
GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
GUR_FLD_EVIDENCE,
GUR_FLD_RETAINED,
GUR_FLD_EVI_RET,
GUR_RESPONSE_LNG)
VALUES (
PS_REC.GUR_COLLECT_CD,
PS_REC.GUR_OPEN_DATE,
PS_REC.EMPLID,
PS_REC.GUR_SECTION_NR,
PS_REC.GUR_QUESTION_NBR,
PS_REC.GUR_FLD_EVIDENCE,
PS_REC.GUR_FLD_RETAINED,
PS_REC.GUR_FLD_EVI_RET,
PS_REC.GUR_RESPONSE_LNG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;


-- GUR_COLL_FILE
/
DECLARE
CURSOR PS_CURSOR IS
SELECT
'RQF_2007' as GUR_COLLECT_CD,
to_date('08/05/2007', 'dd/mm/yyyy') as GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
SEQNO,
ATTACHSYSFILENAME,
ATTACHUSERFILE,
GUR_PRE_POST_VERS,
GUR_EMPLID,
DATETIME_STAMP,
GUR_EP_STATUS,
GUR_EP_URL,
GUR_ADD_EPRINTS,
GUR_DEL_EPRINTS,
GUR_ALLOW_PUBLSH,
GUR_CONF_EMAIL,
GUR_AUTHORISED,
GUR_VERIFIED,
GUR_DECLINED,
GUR_REFORMATTED,
GUR_ENTBY_EP_STAFF,
GUR_PROCESS_FLAG,
DESCRLONG
FROM sysadm.PS_GUR_COLL_FILE
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy');
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO sysadm.PS_GUR_COLL_FILE (
GUR_COLLECT_CD,
GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
SEQNO,
ATTACHSYSFILENAME,
ATTACHUSERFILE,
GUR_PRE_POST_VERS,
GUR_EMPLID,
DATETIME_STAMP,
GUR_EP_STATUS,
GUR_EP_URL,
GUR_ADD_EPRINTS,
GUR_DEL_EPRINTS,
GUR_ALLOW_PUBLSH,
GUR_CONF_EMAIL,
GUR_AUTHORISED,
GUR_VERIFIED,
GUR_DECLINED,
GUR_REFORMATTED,
GUR_ENTBY_EP_STAFF,
GUR_PROCESS_FLAG,
DESCRLONG)
VALUES (
PS_REC.GUR_COLLECT_CD,
PS_REC.GUR_OPEN_DATE,
PS_REC.EMPLID,
PS_REC.GUR_SECTION_NR,
PS_REC.GUR_QUESTION_NBR,
PS_REC.SEQNO,
PS_REC.ATTACHSYSFILENAME,
PS_REC.ATTACHUSERFILE,
PS_REC.GUR_PRE_POST_VERS,
PS_REC.GUR_EMPLID,
PS_REC.DATETIME_STAMP,
PS_REC.GUR_EP_STATUS,
PS_REC.GUR_EP_URL,
PS_REC.GUR_ADD_EPRINTS,
PS_REC.GUR_DEL_EPRINTS,
PS_REC.GUR_ALLOW_PUBLSH,
PS_REC.GUR_CONF_EMAIL,
PS_REC.GUR_AUTHORISED,
PS_REC.GUR_VERIFIED,
PS_REC.GUR_DECLINED,
PS_REC.GUR_REFORMATTED,
PS_REC.GUR_ENTBY_EP_STAFF,
PS_REC.GUR_PROCESS_FLAG,
PS_REC.DESCRLONG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;
/


-- GUR_COLL_RFCD
insert into sysadm.ps_gur_coll_rfcd
select
'RQF_2007'
, to_date('08/05/2007', 'dd/mm/yyyy')
, EMPLID
, GUR_SECTION_NR
, GUR_QUESTION_NBR
, GUR_PBLCTN_REF_NR
, EFFDT
, GUR_FLD_RSRCH
, GUR_PERCENT
from sysadm.ps_gur_coll_rfcd
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy')
;

When "Page data is inconsistent with database"

How we hate this one. PeopleSoft politely tells you that there is some data (being updated) that is inconsistent with what is in the database. It is not polite enough to tell you which data however.

Here is some code to give you a clue. It returns all data that has changed on the Component.


/* Place in the Component.SavePreChange event.
Finds all data that is being updated in a component when saved. */

Local integer &i, &j, &k, &r, &f;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

Function CheckRowset(&level as integer, &rs As Rowset)

For &i = 1 to &rs.ActiveRowCount

/* Loop through all Records in the RowSet. */
For &r = 1 To &rs(&i).RecordCount

/* Find Records that have changed. */
Local Record &rec = &rs(&i).GetRecord(&r);
If &rec.IsChanged Then

/* Loop through all the Fields in the Record. */
For &f = 1 To &rec.FieldCount

/* Find Fields that have changed. */
If &rec.GetField(&f).IsChanged Then

Local Field &fld = &rec.GetField(&f);
WinMessage("FIELD CHANGED - Level: " | &level | ", Row: 1, Record: " | &rec.Name | ", Field: " | &fld.Name | ", OriginalValue= " | &fld.OriginalValue | ", Value= " | &fld.Value,0);

End-If;

End-For;

End-If;

End-For;

End-For;

End-Function;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

/* Level 0. */
Local Rowset &rowset0 = GetLevel0();
CheckRowset(0,&rowset0);

/* Level 1. */
For &i = 1 To &rowset0(1).ChildCount

Local Rowset &rowset1 = &rowset0(1).GetRowset(&i);
CheckRowset(1,&rowset1);

/* Level 2. */
For &j = 1 To &rowset1(1).ChildCount

Local Rowset &rowset2 = &rowset1(1).GetRowset(&j);
CheckRowset(2,&rowset2);

/* Level 3 (maximum). */
For &k = 1 To &rowset2(1).ChildCount

Local Rowset &rowset3 = &rowset2(1).GetRowset(&k);
CheckRowset(3,&rowset3);

End-For;

End-For;

End-For;


DMS for Research Publications

Useful script for myself to move a Research Publications data around from environment to environment:


SET OUTPUT c:\temp\temp.dat;
SET LOG c:\temp\tmp.log;
export gur_pblctn where gur_pblctn_ref_nr = '17581';
export gur_gu_authors where gur_pblctn_ref_nr = '17581';
export gur_oth_authors where gur_pblctn_ref_nr = '17581';
export gur_pbl_curator where gur_pblctn_ref_nr = '17581';
export gur_pblctn_exbt where gur_pblctn_ref_nr = '17581';
export gur_pbl_rfcd where gur_pblctn_ref_nr = '17581';
export gur_verifctn where gur_pblctn_ref_nr = '17581';
export gur_pbl_file where gur_pblctn_ref_nr = '17581';
export gur_ver_status where gur_pblctn_ref_nr = '17581';
export gur_pbl_email where gur_pblctn_ref_nr = '17581';
export gur_pbl_emailto where gur_pblctn_ref_nr = '17581';
export gur_pbl_emailcc where gur_pblctn_ref_nr = '17581';

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SET INPUT c:\temp\temp.dat;
SET LOG c:\temp\tmp.log;
delete from ps_gur_pblctn where gur_pblctn_ref_nr = '17581';
delete from ps_gur_gu_authors where gur_pblctn_ref_nr = '17581';
delete from ps_gur_oth_authors where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pbl_curator where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pblctn_exbt where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pbl_rfcd where gur_pblctn_ref_nr = '17581';
delete from ps_gur_verifctn where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pbl_file where gur_pblctn_ref_nr = '17581';
delete from ps_gur_ver_status where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pbl_email where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pbl_emailto where gur_pblctn_ref_nr = '17581';
delete from ps_gur_pbl_emailcc where gur_pblctn_ref_nr = '17581';

import *;