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 *;


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:



&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.

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;