Two examples of evaluating 2 values and returning a given value based on which is earlier/bigger:
DECODE((SYSDATE - A.DUE_DT) - ABS(SYSDATE - A.DUE_DT), 0, 'OVERDUE', A.DUE_DT) AS DUE_DATE
CASE WHEN SYSDATE > A.DUE_DT THEN 'OVERDUE' ELSE to_char(A.DUE_DT) END AS DUE_DATE
Both do the same thing.
Related links:
http://www.4guysfromrolla.com/webtech/102704-1.shtml
http://www.sqlteam.com/article/the-case-for-case
Passing Parameters to SQR via the Process
10.01.2008, update: seems Blogger has lost the uplaoded images for this post. Perhaps they will come back?
Update the Parameter List of your process definition. I this case I have used a Record.Field value.
PeopleCode to set the value of the Field (GU_GRDLPS_RC_WK.DESCR).
In your SQR you pick up the parameter by using the Input statement:
input $procAcadOrg 'Academic Organisation to distribute report to: (Optional, Press ENTER to continue)'
Update the Parameter List of your process definition. I this case I have used a Record.Field value.
PeopleCode to set the value of the Field (GU_GRDLPS_RC_WK.DESCR).
In your SQR you pick up the parameter by using the Input statement:
input $procAcadOrg 'Academic Organisation to distribute report to: (Optional, Press ENTER to continue)'
Errors running Application Engines
If you get the following error:
"Message:
All Processing Suspended: Restart OPRID=S390765, RUNID=31-01-03112240, PI=2988
Description:
A pending process was found for the current Operator id, Run Control id, and Application Engine Program. This process either is running now or has abended. Please restart the pending process if it has abended, and allow it to run to completion, before submitting a new process request."
...you need to cleanup the dead rows in sysadm.ps_aeruncontrol.
PeopleTools > Application Engine > Manage Abends
"Message:
All Processing Suspended: Restart OPRID=S390765, RUNID=31-01-03112240, PI=2988
Description:
A pending process was found for the current Operator id, Run Control id, and Application Engine Program. This process either is running now or has abended. Please restart the pending process if it has abended, and allow it to run to completion, before submitting a new process request."
...you need to cleanup the dead rows in sysadm.ps_aeruncontrol.
PeopleTools > Application Engine > Manage Abends
Downloading from Grids - IE settings
Problem:
We found a problem when tring to download to spreadsheets from grids. IE wouldn't open the spreadsheet, just refreshed a few times and then nothing.
Resolution:
Tools/Internet Options/
Security/Custom Level/
Downloading/Automatic prompting for downloads - tick enable.
We found a problem when tring to download to spreadsheets from grids. IE wouldn't open the spreadsheet, just refreshed a few times and then nothing.
Resolution:
Tools/Internet Options/
Security/Custom Level/
Downloading/Automatic prompting for downloads - tick enable.
Bad CSV layout from SQR
I found that some CSVs had layout problems where the file being produced contained unwanted line breaks. This of course mucked up the layout when the CSV was viewed in Excel or whatever.
I added the following to the top of the SQR:
begin-setup
page-size 79 600
end-setup
The page-size command sets the number of rows and columns, respectively (rows weren't an issue).
I added the following to the top of the SQR:
begin-setup
page-size 79 600
end-setup
The page-size command sets the number of rows and columns, respectively (rows weren't an issue).
Edit: In latest version of SQR use the following:
Declare-Layout my-layout
paper-size=(a4)
orientation=landscape
MAX-COLUMNS=600
End-Declare
Declare-Report THIS_REPORT
Printer-Type=POSTSCRIPT
LayOut=my-layout
End-Declare
Prompts driven by other prompts
Sometimes you want to be able to filter the values returned by a prompt based on a value you have entered on another prompt on the page. For example you have selected 'Australia' in the first prompt and now you want the second prompt to only list states for that country.
This example uses Pay Groups and Run Types:
When the user has selected a 'Pay Group' the prompt for 'Run Type' will only return run types that are in that pay group:
The first record stores the values, in this case it has no keys.
Note the key structure and list items on the two prompt records:
This example uses Pay Groups and Run Types:
When the user has selected a 'Pay Group' the prompt for 'Run Type' will only return run types that are in that pay group:
The first record stores the values, in this case it has no keys.
Note the key structure and list items on the two prompt records:
Component Interfaces - What they can't do.
The DoSaveNow() PeopleCode function does not trigger when run from a Component Interface. There is no indication, error or warning. There may be other misleading errors or warnings caused by the DoSaveNow() not being triggered.
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)
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;
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:
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? ; )
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.
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.
- 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.
- 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); - 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
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';
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
;
-- 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')
;
-- 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.
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, &amp;amp;amp;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 &amp;amp;fld = &rec.GetField(&f);
WinMessage("FIELD CHANGED - Level: " | &level | ", Row: 1, Record: " | &amp;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 *;
Data Mover Script - Example
I do these so rarely I forget the syntax:
set output c:\temp\temp.dat;
set log c:\temp\temp.log;
export table_name where key_field = '123';
________________________________________________________
set input c:\temp\temp.dat;
set log c:\temp\tmp.log;
delete from ps_table_name where key_field = '123';
import *;
Cool way of getting the year, month or day
select
extract(year from sysdate) as year
, extract(month from sysdate) as month
, extract(day from sysdate) as day
from dual;
How to handle a million children - Inserting into child tables the easy way.
There are some situations where you want to insert a new EFFDT programatically into a parent row and also all of its child rows. Note you could also blend this with another article on this blog: SQL - Finding all child records for a given record.
Rather than write out a bunch of SQLExec statements complete with a list of fields that have to be maintainted each time you add a field to a child record you can get PeopleCode to do all the work, like this:
You could use the other post to dynamically create the list of child tables if you wish.
Rather than write out a bunch of SQLExec statements complete with a list of fields that have to be maintainted each time you add a field to a child record you can get PeopleCode to do all the work, like this:
&arrRecs = Split("TABLE_A,TABLE_B,TABLE_C", ",");
For &i = 1 To &arrRecs.len
Local string &recName = &arrRecs [&i];
/* Get all rows for the parent/child record and process them. */
Local Rowset &rs = CreateRowset(@("Record." | &recName));
&rs.Fill("WHERE KEY_FIELD_1 = :1
AND FILL.EFFDT =
(SELECT MAX(F_ED.EFFDT)
FROM %Table(" | &recName | ") F_ED
WHERE F_ED.KEY_FIELD_1 = FILL.KEY_FIELD_1)"
, &keyValue);
For &j = 1 To &rs.ActiveRowCount
/* New Effective date for the row and insert. */
Local Record &rec = &rs(&j).GetRecord(@("Record." | &recName));
&rec.EFFDT.Value = AddToDate(%Date, 0, 0, 1);
&rec.Insert();
End-For;
End-For;
You could use the other post to dynamically create the list of child tables if you wish.
Using Unions in a Dynamic View
When using a dynamic view as a prompt PeopleSoft passes the search keys through automatically and appends to he view's SQL. It is not entirely clear sometimes exactly how it is appending to the SQL but it attempts to determine where to add the brackets, sometimes unsuccessfully.
This is largely unsuccessful when the dynamic view is a union, it attempts to put brackets in, somewhere.
The trick is to wrap the entire union/clause as a kind of subselect:
SELECT FIELD_A, FIELD_B, FIELD_C
FROM (
SELECT A.FIELD_A, A.FIELD_B, B.FIELD_C
FROM TABLE_A A, TABLE B B
WHERE B.FIELD_A = A.FIELD_A
UNION
SELECT C.FIELD_A, C.FIELD_B, C.FIELD_C
FROM TABLE C
WHERE C.FIELD_A = 'X'
AND C.OPRID = %OperatorID
)
Note the entire statement is wrapped in brackets making it simple for PeopleSoft to append to the SQL.
This is largely unsuccessful when the dynamic view is a union, it attempts to put brackets in, somewhere.
The trick is to wrap the entire union/clause as a kind of subselect:
SELECT FIELD_A, FIELD_B, FIELD_C
FROM (
SELECT A.FIELD_A, A.FIELD_B, B.FIELD_C
FROM TABLE_A A, TABLE B B
WHERE B.FIELD_A = A.FIELD_A
UNION
SELECT C.FIELD_A, C.FIELD_B, C.FIELD_C
FROM TABLE C
WHERE C.FIELD_A = 'X'
AND C.OPRID = %OperatorID
)
Note the entire statement is wrapped in brackets making it simple for PeopleSoft to append to the SQL.
Performance across Database Links
When pulling a lot of data through a database link you can speed up the process enormously by hinting at which table is on a remote site. This table will be processed at that site (or something similar):
SELECT /*+DRIVING_SITE(C)*/
A.FIELD
, B.FIELD
, C.FIELD
FROM
PS_TABLE_A A
, PS_TABLE_B B
, PS_TABLE_C@RA C
WHERE A.KEY_FIELD = x
AND A.KEY_FIELD = B.KEY_FIELD
AND C.KEY_FIELD2 = C.KEY_FIELD2;
SELECT /*+DRIVING_SITE(C)*/
A.FIELD
, B.FIELD
, C.FIELD
FROM
PS_TABLE_A A
, PS_TABLE_B B
, PS_TABLE_C@RA C
WHERE A.KEY_FIELD = x
AND A.KEY_FIELD = B.KEY_FIELD
AND C.KEY_FIELD2 = C.KEY_FIELD2;
SQL - Finding all child records for a given record
Using the keys of the parent find all the child records:
-- Tables that are associated with a publication.
SELECT DISTINCT RECNAME
FROM sysadm.PSRECDEFN A
WHERE RECNAME LIKE 'GUR%'
AND RECTYPE = 0
-- Key 1.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'GUR_PBLCTN_REF_NR')
-- Key 2.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'EFFDT')
SQL - Multiple rows into one. Transpose SQL Results.
I've got Table.Person and Table.Pet. Each person has multiple pets.
How do I return one row for each person with all their pets comma delimited in one column?
How do I return one row for each person with all their pets comma delimited in one column?
SELECT
PERSON_ID
, TRIM(',' FROM TRIM(' ' FROM
MAX(DECODE(SEQ, 1, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 2, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 3, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 4, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 5, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 6, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 7, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 8, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 9, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 10, PET_NAME || ', '))
)) AS PET_NAMES
FROM
(SELECT
A.PERSON_ID
, A.NAME AS PERSON_NAME
, B.NAME AS PET_NAME
, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY PERSON_ID DESC) SEQ
FROM
PERSON A
, PET B
WHERE B.PERSON_ID = A.PERSON_ID)
GROUP BY PERSON_IDAll Scroll Levels & Records that are in a PeopleSoft Component.
This SQL returns all Scroll Levels and Records for a given PeopleSoft component. Handy.
DEFINE componentName = 'your_component';
SELECT DISTINCT B.PNLNAME, B.RECNAME, B.OCCURSLEVEL, B.FIELDNUM
FROM
PSPNLGROUP A
, PSPNLFIELD B
, PSRECDEFN C
WHERE A.PNLGRPNAME = &componentName
AND B.PNLNAME = A.PNLNAME
AND B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM =
(SELECT MIN(D.FIELDNUM)
FROM PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
AND C.RECTYPE = 0
UNION
SELECT DISTINCT B.PNLNAME, B.RECNAME, B.OCCURSLEVEL + D.OCCURSLEVEL, B.FIELDNUM
FROM
PSPNLFIELD B
, PSRECDEFN C
, (SELECT E.SUBPNLNAME, E.OCCURSLEVEL
FROM PSPNLFIELD E
WHERE E.SUBPNLNAME <> ' '
AND EXISTS
(SELECT 'X'
FROM PSPNLGROUP F
WHERE F.PNLGRPNAME = &componentName
AND F.PNLNAME = E.PNLNAME)) D
WHERE B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM =
(SELECT MIN(D.FIELDNUM)
FROM PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
AND C.RECTYPE = 0
AND D.SUBPNLNAME = B.PNLNAME ORDER BY 1,3,4
How to compare objects between projects
Sometimes you just want to see the differences between 2 projects, what objects are in one and not the other or what objects are in both. Here is some SQL to do just that:
select * from (
SELECT
a.projectname,
A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.')|| A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 as Object,
decode(a.objecttype,
'0', 'Record',
'1', 'Index',
'2', 'Field',
'3', 'Field Format',
'4', 'Translate value',
'5', 'Page',
'6', 'Menu',
'7', 'Component',
'8', 'Record Peoplecode',
'9', 'Unknown Object Type',
'10','Query',
'11','Tree Structure',
'12','Tree',
'13','Access Group',
'14','Colours',
'15','Style',
'16','Unknown Object Type',
'17','Business Processes',
'18','Activities',
'19','Roles',
'20','Process Defintions',
'21','Server Definition',
'22','Process Type Definitions',
'23','Job Definitions',
'24','Recurrence Definition',
'25','Message Catalog Entries',
'26','Dimensions',
'27','Cube Definitions',
'28','Cube Instance Definitions',
'29','Business Interlink',
'30','Record SQL',
'31','File Layout Definitions',
'32','Component Interface',
'33','Application Engine Programs',
'34','Application Engine Sections',
'35','Message Node',
'36','Message Channels',
'37','Messages',
'38','Approval Rule Sets',
'39','Unknown Object Type',
'40','Unknown Object Type',
'41','Unknown Object Type',
'42','Unknown Object Type',
'43','Unknown Object Type',
'44','Page Peoplecode',
'45','Unknown Object Type',
'46','Component Peoplecode',
'47','Unknown Object Type',
'48','Component Rec Fld Peoplecode',
'49','Images',
'50','Style Sheet',
'51','HTML',
'52','File Reference',
'53','Permission Lists',
'54','Portal Registry Definitions',
'55','Portal Registry Structures',
'56','URL Definition',
'57','Application Packages',
'58','Unknown Object Type',
'59','Unknown Object Type',
'60','Unknown Object Type',
'61','Archive Template',
'62','Unknown Object Type',
'63','Portal registry User Favourites',
'64','Mobile page',
'PeopleSoft are being smart again with - ' || a.objecttype ) as Type,
decode(a.UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype <> '52'
and not (a.objecttype=0 and a.objectid2 = 2)
and not (a.objecttype=2 and a.objectid2 = 102)
-- objects from this project...
and a.projectname = 'GUR_ETHICS_ALL'
-- ... that do/do not exist...
and not exists
(select 'x'
from sysadm.PSPROJECTITEM B
where a.projectname <> b.projectname
and A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.') || A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 = b.OBJECTVALUE1 || decode(b.OBJECTVALUE2,' ','','.'||b.OBJECTVALUE2) || decode(b.OBJECTVALUE3,' ','','.') || b.OBJECTVALUE3 || decode(b.OBJECTVALUE4,' ','','.') || b.OBJECTVALUE4
and a.objecttype = b.objecttype
-- ...in this project.
and b.projectname = 'GUR_RESEARCH_ALL')
)
order by 3,2
Help! No Matching Buffer Error - How to start.
Definately the most irritating error I come across, the "No matching buffer" error. I find this especially irritating as PeopleTools has found the error but refuses to give you any clue as to which scroll/data combination is causing it.
Sometimes the problem can be quickly identified by looking through the data in the component, for example a drop down box may be showing 'invalid value', which could lead you quickly to the offending data.
But how to actually get into the component to take a look around? That damn error message is stopping you from opening the component.
Ctrl + J. Pressing this combination displays information about the component. The magic bit is that by clicking 'continue', or Ctrl + J again, the component will open complete with the data that is valid.
A simple, obvious but useful start to tracking down the cause of the dreaded "no matching buffer" error. If you can quickly identify where the problem may be by looking for clues in the component you can save a lot of time, frustration and especially boredom by not having to troll through a potentially large and complex component, page-by-page, scroll-by-scroll.
Sometimes the problem can be quickly identified by looking through the data in the component, for example a drop down box may be showing 'invalid value', which could lead you quickly to the offending data.
But how to actually get into the component to take a look around? That damn error message is stopping you from opening the component.
Ctrl + J. Pressing this combination displays information about the component. The magic bit is that by clicking 'continue', or Ctrl + J again, the component will open complete with the data that is valid.
A simple, obvious but useful start to tracking down the cause of the dreaded "no matching buffer" error. If you can quickly identify where the problem may be by looking for clues in the component you can save a lot of time, frustration and especially boredom by not having to troll through a potentially large and complex component, page-by-page, scroll-by-scroll.
Posts
