Subversion Repositories DevTools

 --  UsedBySBOMSummary.sql
--  Locate package versions that use the package specified by :PV_ID
--  Match control via :MATCH
--    :MATCH 0  - Match any  package name
--    :MATCH 1  - Match Package name and Project Suffix
--    :MATCH 2  - Match exactly the package. Name, Version and Suffix
with 
  aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID ),
  bb AS ( select pkg_id from package_versions where pv_id = :PV_ID )
SELECT DISTINCT proj.proj_name, br.branch_name, b.branch_id, count (b.branch_id) as COUNT
FROM package_versions pv,
  packages pkg,
  DEPLOYMENT_MANAGER.os_contents osc,
  DEPLOYMENT_MANAGER.operating_systems os,
  DEPLOYMENT_MANAGER.network_nodes nn,
  DEPLOYMENT_MANAGER.bom_contents bc,
  DEPLOYMENT_MANAGER.boms b,
  DEPLOYMENT_MANAGER.bom_names bn,
  DEPLOYMENT_MANAGER.branches br,
  DEPLOYMENT_MANAGER.dm_projects proj,
  aa,
  bb
WHERE pv.pkg_id    = pkg.pkg_id
AND osc.prod_id    = pv.pv_id
AND os.os_id       = osc.os_id
AND nn.node_id     = os.node_id
AND bc.node_id     = nn.node_id
AND b.bom_id       = bc.bom_id
AND bn.bom_name_id = b.bom_name_id
AND br.branch_id   = b.branch_id
AND proj.proj_id   = br.proj_id
AND br.is_hidden  IS NULL
AND (osc.prod_id  = :PV_ID OR :MATCH != 2)
AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
AND (NVL(pv.v_ext,'LINK') in (  aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
GROUP BY proj.proj_name, br.branch_name, b.branch_id
ORDER BY UPPER(proj.proj_name), UPPER(br.branch_name)