ALM 中要查看某个 test 更改历史， 需要下面两个表：

AUDIT_LOG and AUDIT_PROPERTIES

------- Get Test modification history --------

---- In ALM, 857, if filter out test case named '26169502', check its History. In the history, for the node of date '20-AUG-18', can see 6 items, changer is: asbopann

---- following is the steps to retrive the 6 records in ALM DB.

select * from tools_peopletools_857_qa_db.test where TS_USER_03 = 'Application Designer' and TS_Name = '26169502' and TS_USER_08 = 'Ready to Automate' and TS_Creation_Date > TO_DATE ('MAY-04-2017','MON-DD-YYYY');

--TS_TEST_ID = 84826

select * from tools_peopletools_857_qa_db.AUDIT_LOG where au_entity_id = 84826;

-- This returns all the modification history of test case '26169502'. Many items

-- Among the result, we interested in the one happened on 20-AUG-18, which is changing case to 'Ready to Automate'

-- AU_ACTION_ID = 21774729

select * from tools_peopletools_857_qa_db.AUDIT_PROPERTIES where AP_ACTION_ID = 21774729;

-- Now you can see 6 records which are the ones we want.

select * from tools_peopletools_857_qa_db.AUDIT_PROPERTIES where AP_OLD_VALUE = 'Planned' and AP_NEW_VALUE = 'Ready to Automate' and AP_ACTION_ID = 21774729;

-- With this one, we can just filter the record of changed to 'Ready to Automate'

最终整合的 sql:

select tt.ts_name, tt.TS_TEST_ID, ll.AU_TIME, ll.AU_USER, pp.AP_ACTION_ID, pp.AP_OLD_VALUE, pp.AP_NEW_VALUE from tools_peopletools_857_qa_db.test tt, tools_peopletools_857_qa_db.AUDIT_LOG ll, tools_peopletools_857_qa_db.AUDIT_PROPERTIES pp
where tt.TS_USER_03 = 'Application Designer' and tt.TS_USER_08 = 'Ready to Automate' and tt.TS_Creation_Date > TO_DATE ('MAY-04-2017','MON-DD-YYYY')
and tt.TS_TEST_ID = ll.au_entity_id and ll.au_action = 'UPDATE'
and pp.AP_OLD_VALUE = 'Planned' and pp.AP_NEW_VALUE = 'Ready to Automate'
and ll.AU_ACTION_ID = pp.AP_ACTION_ID order by tt.ts_name;