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.

Click here for webinar.