lsw_project

gistfile1.sql

-- select Process Application...
select PROJECT_ID, NAME from LSW_PROJECT where IS_TOOLKIT = 'F' and IS_ARCHIVED = 'F' and IS_HIDDEN = 'F' order by name;
-- 00bffc67-8997-4042-bd49-3150bc7b7d72 (Project ID)


-- select Snapshot and Branch...
select * from LSW_SNAPSHOT where PROJECT_ID = '00bffc67-8997-4042-bd49-3150bc7b7d72' and name is not null ORDER BY CREATED_ON DESC;
-- 4ea36473-5a07-44d1-a331-f1cc2ffa19ab -> 293648c3-b432-4178-8e29-d068d44daa58 (Snapshot IDs)
-- 55fbf4a8-1847-427e-9fdb-efda57d9533c (Branch ID)

 
-- select Modified Artefacts...
select
'BPD' as TYPE, BPD.BPD_ID as ID, BPD.NAME as NAME, BPD.LAST_MODIFIED as LAST_MODIFIED, BPD.LAST_MODIFIED_BY_USER_ID as USER_ID
from
LSW_BPD BPD
inner join lsw_po_versions v_service on BPD.version_id = v_service.po_version_id
inner join lsw_branch b on v_service.branch_id = b.branch_id
inner join lsw_project p on b.project_id = p.project_id and p.project_id = '81813bb0-24f0-4eb7-9917-a03597b7d903'
inner join lsw_snapshot s on s.branch_id = b.branch_id and s.snapshot_id = '293648c3-b432-4178-8e29-d068d44daa58' and v_service.start_seq_num <= s.seq_num and (v_service.end_seq_num is null or v_service.end_seq_num > s.seq_num)
where BPD.LAST_MODIFIED >= (select CREATED_ON from LSW_SNAPSHOT where snapshot_id = '4ea36473-5a07-44d1-a331-f1cc2ffa19ab') -- START
  and BPD.LAST_MODIFIED <= (select CREATED_ON from LSW_SNAPSHOT where snapshot_id = '293648c3-b432-4178-8e29-d068d44daa58') -- END
  and b.branch_id = (select branch_id from LSW_SNAPSHOT where snapshot_id = '293648c3-b432-4178-8e29-d068d44daa58')
 
UNION
 
select
  'SERVICE' as TYPE, service.PROCESS_ID as ID, service.NAME as NAME, service.LAST_MODIFIED as LAST_MODIFIED, service.LAST_MODIFIED_BY_USER_ID as USER_ID
from
LSW_PROCESS service
inner join lsw_po_versions v_service on service.version_id = v_service.po_version_id
inner join lsw_branch b on v_service.branch_id = b.branch_id
inner join lsw_project p on b.project_id = p.project_id and p.project_id = '81813bb0-24f0-4eb7-9917-a03597b7d903'
inner join lsw_snapshot s on s.branch_id = b.branch_id and s.snapshot_id = '293648c3-b432-4178-8e29-d068d44daa58' and v_service.start_seq_num <= s.seq_num and (v_service.end_seq_num is null or v_service.end_seq_num > s.seq_num)
where service.LAST_MODIFIED >= (select CREATED_ON from LSW_SNAPSHOT where snapshot_id = '4ea36473-5a07-44d1-a331-f1cc2ffa19ab') -- START
  and service.LAST_MODIFIED <= (select CREATED_ON from LSW_SNAPSHOT where snapshot_id = '293648c3-b432-4178-8e29-d068d44daa58') -- END
  and b.branch_id = (select branch_id from LSW_SNAPSHOT where snapshot_id = '293648c3-b432-4178-8e29-d068d44daa58');