| 6126 |
dpurdie |
1 |
-- UsedBySBOMDetail.sql
|
|
|
2 |
-- Locate package versions that use the package specified by :PV_ID
|
|
|
3 |
-- Within an SBOM Branch specified by :BRANCH_ID
|
|
|
4 |
-- Match control via :MATCH
|
|
|
5 |
-- :MATCH 0 - Match any package name
|
|
|
6 |
-- :MATCH 1 - Match Package name and Project Suffix
|
|
|
7 |
-- :MATCH 2 - Match exactly the package. Name, Version and Suffix
|
|
|
8 |
with
|
|
|
9 |
aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID ),
|
|
|
10 |
bb AS ( select pkg_id from package_versions where pv_id = :PV_ID )
|
|
|
11 |
|
|
|
12 |
SELECT DISTINCT b.bom_id, nn.node_name, os.os_name, bn.bom_name, b.BOM_VERSION ||'.' || b.BOM_LIFECYCLE AS VERSION,
|
|
|
13 |
os.os_id,
|
|
|
14 |
os.node_id,
|
|
|
15 |
br.proj_id,
|
|
|
16 |
pkg.pkg_name,
|
|
|
17 |
pv.PKG_VERSION,
|
|
|
18 |
pv.pv_id,
|
|
|
19 |
pv.MODIFIER_ID,
|
|
|
20 |
pv.MODIFIED_STAMP,
|
|
|
21 |
usr.FULL_NAME,
|
|
|
22 |
usr.USER_EMAIL,
|
|
|
23 |
CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
|
|
|
24 |
FROM package_versions pv,
|
|
|
25 |
packages pkg,
|
|
|
26 |
DEPLOYMENT_MANAGER.os_contents osc,
|
|
|
27 |
DEPLOYMENT_MANAGER.operating_systems os,
|
|
|
28 |
DEPLOYMENT_MANAGER.network_nodes nn,
|
|
|
29 |
DEPLOYMENT_MANAGER.bom_contents bc,
|
|
|
30 |
DEPLOYMENT_MANAGER.boms b,
|
|
|
31 |
DEPLOYMENT_MANAGER.bom_names bn,
|
|
|
32 |
DEPLOYMENT_MANAGER.branches br,
|
|
|
33 |
DEPLOYMENT_MANAGER.dm_projects proj,
|
|
|
34 |
USERS usr,
|
|
|
35 |
aa,
|
|
|
36 |
bb
|
|
|
37 |
WHERE pv.pkg_id = pkg.pkg_id
|
|
|
38 |
AND osc.prod_id = pv.pv_id
|
|
|
39 |
AND os.os_id = osc.os_id
|
|
|
40 |
AND nn.node_id = os.node_id
|
|
|
41 |
AND bc.node_id = nn.node_id
|
|
|
42 |
AND b.bom_id = bc.bom_id
|
|
|
43 |
AND bn.bom_name_id = b.bom_name_id
|
|
|
44 |
AND br.branch_id = b.branch_id
|
|
|
45 |
AND proj.proj_id = br.proj_id
|
|
|
46 |
AND br.is_hidden IS NULL
|
|
|
47 |
AND (osc.prod_id = :PV_ID OR :MATCH != 2)
|
|
|
48 |
AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
|
|
|
49 |
AND (NVL(pv.v_ext,'LINK') in ( aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
|
|
|
50 |
AND b.branch_id = :BRANCH_ID
|
|
|
51 |
AND pv.MODIFIER_ID = usr.USER_ID(+)
|
|
|
52 |
ORDER BY UPPER(bn.bom_name), UPPER(version), UPPER(nn.node_name), UPPER(os.os_name)
|
|
|
53 |
|
|
|
54 |
|