Style Sheet Gotchas

So, you've created your own style sheet class to change the border colour of a Frame or Group Box to red. But, when you view the page rather than a red border you get no border. What is going wrong?
If you look at the HTML source you'll see your frame, which is actually a HTML Table, written there with your new class. Something like:

[table class="REDBORDER" style="border:none"]

Why is it hiding your borders?
Look above and you'll see another table:

[table class="REDBORDERWBO"...

What is this?

On the page definition in Application Designer you can turn a Frame's border on/off. It is in the properties for each Frame object. PeopleSoft adds another Table around your Frame's Table that is used to handle the on/off (hidden) state of the border. If the Frame border is on you'll see the wrapper table with class xxxWBO (with border) and if off you'll see the wraper table with class xxxNBO (no border).
Your custom class definition, in this example, also needs the classes REDBORDERWBO and REDBORDERNBO to handle the on/off state of the Frame border. Same goes for Group Boxes.

Who is SamDestroy?

To get around the SamDestroy error when comparing projects from file go to Config Manager, Remote Call/AE Tab, and check the Shared Flags "Disable DB Stats" checkbox and you should be fine.

This seems to only be an issue for DB2.

James Court Medina Apartments, Canberra

As an I.T. contractor I am required to work in different cities, sometimes 3 different contracts/year and 3 different cities.

I am writing a short review of James Court Medina apartments in Canberra to partially vent my frustration, and I know Google will probably pick up this post : )

I did some thinking last night, all the places I had ever lived in, including my first slum/sharehouse as a teenager. James Court Medina is actually the worst apartment I have ever lived in, ever. Including addresses in Russia. DO NOT LIVE HERE. At least my slums had one or two appealing factors, like the beach being across the road or price. They all had a working shower.

1. Location. You will not sleep. Take a look at the location on a map. That is the intersection of two major roads. The car noise is actually fine, it is the trucks that will rob you of any sleep. They compression brake in to those traffic lights all morning, starting at about 4am. BRRRRRRR-rrrrrr-RRRRRR-SQUEEEEEEEEK. You will jump awake the first few times. It's best to go to bed around 8pm in anticipation of being woken very early.

2. Heating. I arrived here in Spring and even then the heater in the living room, a split system from the late 80's, was insufficient to heat the room. I spent weeks watching television in thermals and a beanie. I'd hate to see what it's like in winter. You could turn the fridge off I'm sure.

3. Condition. Nothing has been updated since it was built in the late 80's early 90's. I opened my windows one afternoon and now two weeks later they are still stuck open. I'm still waiting for them to be fixed. This is just lovely at night when a 6 degree wind blows through, My lounge room is now like a fridge. Also, as I mentioned, NO maintenance has been done since the aprtments were built. I had to decalcify the shower heads myself.

4. Price. Seriously WTF? Look elsewhere. I know for a fact that for an extra $50/week you can have lake views and a marble kitchen. The only reason I moved in here was because I was extremely unlucky with my timing, and there was nothing else available. I regret my decision.

To Summarise: LOOK ELSEWHERE. DO NOT LIVE AT James Court Medina, Canberra.

P.S. The fire alarm went off yesterday afternoon. I could hear it from the lounge room but I could not hear it from the bedroom, even though I knew it was ringing. Dodgy.

Inserting Random Test Data

INSERT INTO PS_SOME_TEST_TBL
SELECT EMPLID, 'Test ID' 
FROM
    (SELECT EMPLID FROM PS_JOB
    ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM < 101;

Component Arrays - Caching

One Application Engine performance tip I found was to place the results of commonly used queries into Component level arrays that could be searched through on the Application Server instead of making a call to the database. Some code was triggered that many times the DB calls numberd in millions and all the round-trips were expensive.

The following creates component arrays that store the results for a common query, in this case PIN_NUM and PIN_NM from the GP_PIN table.

/* Create a set of global look-up arrays. 
   This will remove the need to call the db many, many times. 

  See CC_FUNCLIB_UOA.LOOKUP.FieldFormula for how to call/use. */

Component array of string &arrPinNumIndex;
Component array of number &arrPinNumVal;

Component array of number &arrPinNmIndex;
Component array of string &arrPinNmVal;

Local SQL &sql;
Local string &pinNm;
Local integer &pinNum, &i;

/* PIN_NUM Lookup: */
&arrPinNumIndex = CreateArrayRept("", 0);
&arrPinNumVal = CreateArrayRept(0, 0);

&sql = CreateSQL("SELECT PIN_NM, PIN_NUM FROM %Table(GP_PIN) ORDER BY 1");
&i = 1;
While &sql.Fetch(&pinNm, &pinNum)
   &arrPinNumIndex [&i] = &pinNm;
   &arrPinNumVal [&i] = &pinNum;
   &i = &i + 1;
End-While;


/* PIN_NM Lookup: */
&arrPinNmIndex = CreateArrayRept(0, 0);
&arrPinNmVal = CreateArrayRept("", 0);

&sql = CreateSQL("SELECT PIN_NUM, PIN_NM FROM %Table(GP_PIN) ORDER BY 1");
&i = 1;
While &sql.Fetch(&pinNum, &pinNm)
   &arrPinNmIndex [&i] = &pinNum;
   &arrPinNmVal [&i] = &pinNm;
   &i = &i + 1;
End-While;

There is a Function that can be called to interrogate the arrays for the value you are searching for:

<* Search the global array that stores cached lookup values from database.
   This removes (a lot of) roundtrips between app server and db server. 

   Example of use:

     /* previously populated arrays - index and value. */
     Component array of string &arrPinNumIndex;
     Component array of number &arrPinNumVal;

     Declare Function GetCached PeopleCode CC_FUNCLIB_UOA.LOOKUP FieldFormula;
     &x = GetCached("ZACC ALA", &arrPinNumIndex, &arrPinNumVal);

   i.e. Pass it a pin name and two, synchronised arrays.
   The first array contains the index, the second contains values that are returned.
   Obviously only works on "2 dimensional arrays"

*>



Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
   
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
   
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
   
End-Function;

Function GetCached(&srch, &arrIndex, &arrVal) Returns any
   
   &i = BinarySearch(&arrIndex, &srch, 1, &arrIndex.len);
   If &i = - 1 Then
      Return 0; /* not found */
   Else
      Return &arrVal [&i];
   End-If;
   
End-Function;



To use them try the following code:

/* Lookup array arrays: */
Component array of string &arrPinNumIndex;
Component array of number &arrPinNumVal;

&x = GetCached("BASE PAY", &arrPinNumIndex, &arrPinNumVal);

Commitments / Encumbrances

After building this twice now, both while specs were being written (sigh) I suggest the following approach.

Multiple App Engines.

Main: Contains main outer loop(s)
Grouping Code: Contains calls to Element AEs
Element: Contains Section for each Element calculation.
Functions: Sections for populating component arrays and the like
Population: AE that deals only with splitting, account code mapping and population of temp tables.

... something lik ethat. Would make things more legible, pluggable.

The need for an environment that has accurate pay run data to compare against cannot be stressed enough, preferably dev. The lag between code changes and migrations and runs in downstream envs that have comparable data can be days, very slow and confusing.

Application Engine Performance

1. Reduce writing to log or trace files. I've seen this add 100%+ to processing times. Really.

2. Reduce calls to DB. If possible use Commponent arrays instead of calling the db 1,000,000 times. Searching throug hsmall arrays i far quicker, usually because of the network, not the DB.

Progress Bar for Processes

1. Create an HTML area on a page where you are going to query the progress of a Process.

2. You need to setup a "progress" table for your process. You will need to update the values from within the process while it is running.

e.g. fields EMPLOYEES_TOTAL | EMPLOYEES_PROCESSED etc.

You will probably have to run a precursory SQL to count how many rows are expected to be processed up front, and then update the progress as each row or set of rows is processed.

3. Here is some sample code to update the HTML area with details of how the process is progressing. CC_DERIVED_UOA.DESCRLONG is the HTML Area on the page.

Function UpdateProgressBar(&title, &complete, &total) Returns string
   
   Local string &str = "";
   &str = &str | "   <tr><td style=""font-size:small"">" | &title | ":&nbsp;</td>";
   
   /* Calc pct complete. */
   If &total > 0 Then
      &pct = (&complete / &total) * 10;
   Else
      &pct = 10;
   End-If;
   
   /* Update bars. */
   For &i = 1 To 10
      If &pct >= &i Then
         If &total = 0 Then
            &str = &str | "      <td BGCOLOR=""#CCCCCC"">&nbsp;</td>"; /* grey */
         Else
            &str = &str | "      <td BGCOLOR=""#AAFFAA"">&nbsp;</td>"; /* green */
         End-If;
      Else
         &str = &str | "      <td BGCOLOR=""#FFAAD4"">&nbsp;</td>"; /* red */
      End-If;
   End-For;
   
   &str = &str | "      <td style=""font-size:x-small"">&nbsp;&nbsp;" | &complete | " / " | &total | "</td>";
   &str = &str | "   </tr>";
   
   Return &str;
   
End-Function;



Local Rowset &rsCC_PROGRESS_UOA = CreateRowset(Record.CC_PROGRESS_UOA);
&rsCC_PROGRESS_UOA.Fill(); /* only ever one row */
Local Record &recCC_PROGRESS_UOA = &rsCC_PROGRESS_UOA(1).GetRecord(Record.CC_PROGRESS_UOA);

&strl_progbar = &strl_progbar | "<table>";
&strl_progbar = &strl_progbar | UpdateProgressBar("Employees", &recCC_PROGRESS_UOA.CC_FULLTM_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_FULLTM_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Casuals", &recCC_PROGRESS_UOA.CC_CASUAL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_CASUAL_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Scholarships", &recCC_PROGRESS_UOA.CC_SCHOL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_SCHOL_TOTAL.Value);
&strl_progbar = &strl_progbar | "</table><br>";

CC_DERIVED_UOA.DESCRLONG.Value = &strl_progbar;

Filter Retro Payments


SELECT
 (select pin_nm from ps_gp_pin where pin_num = a.pin_num) AS PIN_NM
, A.EMPLID
, A.EMPL_RCD
, A.PIN_NUM
, Sum(A.CALC_RSLT_VAL)
, A.RATE_RSLT_VAL
, Sum(A.UNIT_RSLT_VAL)
, Sum(A.PCT_RSLT_VAL)

FROM 
  PS_GP_RSLT_ERN_DED A
, PS_GP_PYE_PRC_STAT C

WHERE A.PIN_NUM = 13265 --#PinNum
AND A.EMPLID = '1000269' --$Emplid
AND A.EMPL_RCD = 0 --#Empl_Rcd
AND A.CAL_RUN_ID = '2010F12' --$Calendar_ID

AND C.EMPLID = A.EMPLID
AND C.CAL_RUN_ID = A.CAL_RUN_ID
AND C.EMPL_RCD = A.EMPL_RCD
AND C.GP_PAYGROUP = A.GP_PAYGROUP
AND C.CAL_ID = A.CAL_ID
AND C.ORIG_CAL_RUN_ID = A.ORIG_CAL_RUN_ID
AND C.PRD_TYPE = 'C'

As Of Date in Oracle SQL - Back to the Future

This works:


select *
from
   (select * from ps_job where emplid = '123')
AS OF TIMESTAMP SYSDATE - 1;

Binary Search

Example of:
Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
  
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
  
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
  
End-Function;
I don't often post random snippets of SQL, but I thought this was a good one. Rare enough to be able to forget it easily, and perhaps will be required again.

This returns schedules based on the schedule id(s) that is/are current for a given employee during a given payment period. If there is no schedule id default to 'UOA01'.

The first part was tricky as sch_assign is an effective dated table, and has no required end date, but each row does end when the next effective date comes into effect. I solved that using a subselect to derive the end_dt. Easy enough.

The next part, defaulting in 'UOA01' was a little trickier. A union to always bring in UOA01, but then a not exists subselect to only bring in where part A of the union returned nothing.

Perhaps easy when you see the solution first, this one took me quite a few attempts, and half the afternoon. Saved for prosperity and that time in the future when I ask myself "I remember doing similar...".

Apart from doing some tricky things with dates this SQL always returns a value. There is a default value in the SQL.

select
  %DateOut(dur)
, b.sched_hrs
from
    (select
      a.schedule_id
    , a.effdt as bgn_dt
    ,
        (select min(x.effdt) - 1
        from ps_sch_assign x
        where x.emplid = a.emplid
        and x.empl_rcd = a.empl_rcd
        and x.effdt < a.effdt) as end_dt
    from %Table(sch_assign) a
    where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
    and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
    and a.schedule_id <> ' '
    and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt)
    union
    select 
      'UOA01'
    , to_date('01/01/2000', 'dd/mm/yyyy') as bgn_dt
    , to_date('31/12/9999', 'dd/mm/yyyy') as end_dt
    from dual
    where not exists
        (select 1
        from %Table(sch_assign) a
        where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
        and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
        and a.schedule_id <> ' '
        and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt))) a
, %Table(sch_clnd_vw) b
where b.schedule_id = a.schedule_id
and b.dur between %Bind(cc_a20_uoa_aet.prd_bgn_dt) and %Bind(cc_a20_uoa_aet.prd_end_dt)
and (b.dur <= a.end_dt or a.end_dt is null)
and b.sched_hrs > 0
order by dur

HP Quality Center (QC)

In case I'm ever asked to export from QC again.


/*
Built for Jira 26921
Michael Nitschke 30/03/2010

Examples:
define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\SYS1 SA'

define testPlanRoot = 'MTP Testing'
define testLabRoot = 'MTP Testing\MTP 13'

Hint: you can optionally add a trailing back-slash on the testLabRoot variable if you
need to clearly define it as seperate from and similarly named folder,
e.g. 'SYSTEM' and 'SYSTEM1'.
*/

/* notes:
Test Lab runs do not neccessarily line up in the same folders in Test Lab as the folders that are in Test Plan ...if that makes sense.
Joined by TEST_ID but the Test Lab folder Name can be changed, so the lab results for a given test plan may be in a test lab folder that has a different name to the plan.
hard to explain.
*/

define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\System Test Phase 1 - SA\'


select * from (

SELECT
  substr(path, 2, instr(path,'\',1,2) - 2) as lev1
, substr(path, instr(path,'\',1,2) + 1, instr(path,'\',1,3) - instr(path,'\',1,2) - 1) as lev2
, substr(path, instr(path,'\',1,3) + 1, instr(path,'\',1,4) - instr(path,'\',1,3) - 1) as lev3
, substr(path, instr(path,'\',1,4) + 1, instr(path,'\',1,5) - instr(path,'\',1,4) - 1) as lev4
, substr(path, instr(path,'\',1,5) + 1, instr(path,'\',1,6) - instr(path,'\',1,5) - 1) as lev5
, substr(path, instr(path,'\',1,6) + 1, instr(path,'\',1,7) - instr(path,'\',1,6) - 1) as lev6
, nvl(users.full_name, 'anon.') || ' (' || test.ts_responsible || ')' as sweet_fa
, test.ts_name as test_script_name
, test.ts_status
, run.rn_status

from (
 select
 al_father_id, al_item_id, sys_connect_by_path(al_description, '\') || '\' as path
 FROM (SELECT * from all_lists where al_father_id <> 2329) -- 2329 is a 'bad', invisible folder. Yes really.
 connect by prior al_item_id = al_father_id
) path
, test
, users
,
 (select run.rn_test_id, run.rn_status
 from run
 where exists
  (select 1
  from cycle
  where cycle.cy_cycle_id = run.rn_cycle_id
  and cycle.cy_folder_id in
   (select cf_item_id from
    (select cf_item_id, sys_connect_by_path(cf_item_name, '\') as path
    from cycl_fold a
    connect by prior cf_item_id = cf_father_id)
   where path like '\' || &testLabRoot || '%'))
 and run.rn_execution_date =
  (select max(r.rn_execution_date)
  from run r
  where r.rn_test_id = run.rn_test_id)
 and run.rn_execution_time =
  (select max(rn_execution_time)
  from run r
  where r.rn_test_id = run.rn_test_id
  and r.rn_execution_date = run.rn_execution_date)) run


where path.path like '\' || &testPlanRoot || '%'

and test.ts_subject (+) = path.al_item_id
and users.user_name (+) = test.ts_responsible

and run.rn_test_id (+) = test.ts_test_id

)
where lev1 = &testPlanRoot -- Removes data where same folder name exists on another branch.
and not lev2 is null -- Don't need base folder only in data (blanks).
and not lev3 is null
order by 1, 2, 3, 4, 5, 6, 8
;


%DateIn or %DateOut

Sometimes you need both:

%Select(CC_DAY_UOA_AET.DATE_DAY, CC_DAY_UOA_AET.SCHED_HRS)

SELECT
%DateOut(%DateIn(%Bind(CC_PYDT_UOA_AET.PYMT_DT)) + daynum)
, sched_hrs
FROM ps_sch_defn_dtl a
... 

Extract Data from Quality Center (QC) 9.2 - SQL

Quality Center is a testing suite used at a few installations. It comes with a collection of reports, including its own report generator.

Nonetheless it is nice to be able to get the RAW data and do with it what you will.

-- Extract ALL Test Plan Data:
SELECT
Replace(A.PATH, ' \\Subject\System Tests\SA System Test\', '') AS PATH
, B.TS_NAME
, C.DS_STEP_NAME
, REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(C.DS_DESCRIPTION, '
', Chr(13)), '<(.|\n)+?>', ''), '<', '<'), '>', '>'), '"', Chr(34)) AS DS_DESCRIPTION
, REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(C.DS_EXPECTED, '
', Chr(13)), '<(.|\n)+?>', ''), '<', '<'), '>', '>'), '"', Chr(34)) AS DS_EXPECTED
FROM
(SELECT AL_ITEM_ID, LPAD(' ', 2 * LEVEL-1) || SYS_CONNECT_BY_PATH(AL_DESCRIPTION, '\') AS PATH
FROM ALL_LISTS
CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID) A
, TEST B
, DESSTEPS C
WHERE A.PATH LIKE '%\\Subject\System Tests\SA System Test\GRADUATIONS%'
AND B.TS_SUBJECT = A.AL_ITEM_ID
AND C.DS_TEST_ID = B.TS_TEST_ID
ORDER BY A.PATH, B.TS_NAME, C.DS_STEP_ORDER
;


Note I've done some stripping of HTML tags using a combination of Replace() and a regular expression.

We dumped a larger set of the above results into a spreadsheet, connected to it through Access and wrote out all our UAT scripts as a report.

You can also join to the Test Lab to get the Test Results out. Note that it is joined by TEST_ID so the Lab folders may not line up with the Plan folders, you can change folder names etc.

/*
Built for Jira 26921
Michael Nitschke 30/03/2010

Examples:
define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\SYS1 SA'

define testPlanRoot = 'MTP Testing'
define testLabRoot = 'MTP Testing\MTP 13'

Hint: you can optionally add a trailing back-slash on the testLabRoot variable if you
need to clearly define it as seperate from and similarly named folder,
e.g. 'SYSTEM' and 'SYSTEM1'.
*/

/* notes:
Test Lab runs do not neccessarily line up in the same folders in Test Lab as the folders that are in Test Plan ...if that makes sense.
Joined by TEST_ID but the Test Lab folder Name can be changed, so the lab results for a given test plan may be in a test lab folder that has a different name to the plan.
hard to explain.
*/

select * from (

SELECT
  substr(path, 2, instr(path,'\',1,2) - 2) as lev1
, substr(path, instr(path,'\',1,2) + 1, instr(path,'\',1,3) - instr(path,'\',1,2) - 1) as lev2
, substr(path, instr(path,'\',1,3) + 1, instr(path,'\',1,4) - instr(path,'\',1,3) - 1) as lev3
, substr(path, instr(path,'\',1,4) + 1, instr(path,'\',1,5) - instr(path,'\',1,4) - 1) as lev4
, substr(path, instr(path,'\',1,5) + 1, instr(path,'\',1,6) - instr(path,'\',1,5) - 1) as lev5
, substr(path, instr(path,'\',1,6) + 1, instr(path,'\',1,7) - instr(path,'\',1,6) - 1) as lev6
, nvl(users.full_name, 'anon.') || ' (' || test.ts_responsible || ')' as sweet_fa
, test.ts_name as test_script_name
, test.ts_status
, run.rn_status

, test.ts_user_06 as Business_Owner
, test.ts_user_07 as Technical_Owner
, test.ts_user_04 as HR_Func_Area
, test.ts_user_05 as Tester
, test.ts_user_09 as Date_Time
, test.ts_user_08 as Location
, test.ts_user_02 as CBS_Test_Effort
, test.ts_user_01 as Summary
, test.ts_user_03 as CR_ID

from (
 select
 al_father_id, al_item_id, sys_connect_by_path(al_description, '\') || '\' as path
 FROM (SELECT * from all_lists where al_father_id <> 2329) -- 2329 is a 'bad', invisible folder. Yes really.
 connect by prior al_item_id = al_father_id
) path
, test
, users
,
 (select run.rn_test_id, run.rn_status
 from run
 where exists
  (select 1
  from cycle
  where cycle.cy_cycle_id = run.rn_cycle_id
  and cycle.cy_folder_id in
   (select cf_item_id from
    (select cf_item_id, sys_connect_by_path(cf_item_name, '\') as path
    from cycl_fold a
    connect by prior cf_item_id = cf_father_id)
   where path like '\' || &testLabRoot || '%'))
 and run.rn_execution_date =
  (select max(r.rn_execution_date)
  from run r
  where r.rn_test_id = run.rn_test_id)
 and run.rn_execution_time =
  (select max(rn_execution_time)
  from run r
  where r.rn_test_id = run.rn_test_id
  and r.rn_execution_date = run.rn_execution_date)) run


where path.path like '\' || &testPlanRoot || '%'

and test.ts_subject (+) = path.al_item_id
and users.user_name (+) = test.ts_responsible

and run.rn_test_id (+) = test.ts_test_id

)
where lev1 = &testPlanRoot -- Removes data where same folder name exists on another branch.
and not lev2 is null -- Don't need base folder only in data (blanks).
and not lev3 is null
order by 1, 2, 3, 4, 5, 6, 8
;


SQL - Recursive Heirarchy Trees

Assuming a table something like this:
PERSON_ID
PARENT_ID
NAME
Start with this.
SELECT
LPAD(' ', 2*level-1) || SYS_CONNECT_BY_PATH(NAME, '\') "Path"
FROM TABLE_NAME
CONNECT BY PRIOR PERSON_ID = PARENT_ID;
Google "Connect By" for more information.

Page data is inconsistent with database (18,1)

"Page data is inconsistent with database (18,1)"

I recently came across this during a call to a Component Interface, CI.HCR_PERSONAL_DATA_SRV, that interfaced with a rather large Component, Component.PERSONAL_DATA.

(Interestingly when I tested the CI stand-alone it worked fine, never figured out why/no time.
)

I created a Project that contained *all the objects for Component.PERSONAL_DATA. This can take some time if each page has sub-pages, and in turn they too have sub-pages. Be sure to include each record for each page, and each record's associated PeopleCode.
See this post about how to do a lot of this via a SQL script or similar.

I first checked that all the tables and views for the component had been built correctly after a recent patch. They were fine.

Next I checked PeopleCode, namely SavePreChange and SavePostChange as these are the events most likely to cause the given error. Recall that this error happens when data in the buffer pre save and data in the database post save do not match. I simply went through each piece of code and remarked out likely looking code or entire sections...

...until the error stopped occuring. This pointed me to some custom code on SavePost that was updating a row via SQLExec on table x, new delivered code from the patch was updating table x in the buffer in SavePre. Thus the error. All-in-all took me a lot longer than it took to write this post.

Inserting PeopleCode into Project via Script

Sometimes you want to do big compares between environments, and you've got a rather large component, say Component.PERSONAL_DATA, and you've gone through and manually inserted all the pages, subpages, subpages-subpages and so on, including all the Records.

But doing this hasn't automatically inserted all the Record PeopleCode! Yuck, you have to go through each record... and ... no!!!

Now I've written this I can see it wouldn't be much of a leap to expand on this script to be able to just pass in a Component name. I'll leave that to you.

This script will insert all Record PeopleCode for all the Records in a given Project. Note you may have to clear you PeopleTools cache after you've run the script to see it in the given Project.


INSERT INTO PSPROJECTITEM
(SELECT DISTINCT
'PROJECT_NAME', 8
, OBJECTID1, OBJECTVALUE1, OBJECTID2, OBJECTVALUE2, OBJECTID3, OBJECTVALUE3, OBJECTID4, OBJECTVALUE4
, 0, 0, 0, 0, 1, 0
FROM PSPCMPROG
WHERE OBJECTVALUE1 IN
(SELECT OBJECTVALUE1
FROM PSPROJECTITEM
WHERE PROJECTNAME = 'PROJECT_NAME'
AND OBJECTTYPE=0));

PeopleSoft 9.1 and PeopleTools 8.50

What's new? Quite a lot. The first ten minutes is a summary of how well Oracle/PeopleSoft is doing. Skip this for a demonstration of the new technology, namely DHTML and AJAX. It is really nice to finally see some examples in an ERP. Doesn't appear to be much in the way of PeopleCode though, not like the jump to 8 for example.