Subversion Repositories DevTools

--  UsedBySBOMDetail.sql
--  Locate package versions that use the package specified by :PV_ID
--  Within an SBOM Branch specified by :BRANCH_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 b.bom_id, nn.node_name, os.os_name, bn.bom_name,  b.BOM_VERSION  ||'.'  || b.BOM_LIFECYCLE AS VERSION,
                os.os_id,
                os.node_id,
                br.proj_id,
                pkg.pkg_name, 
                pv.PKG_VERSION, 
                pv.pv_id,
                pv.MODIFIER_ID, 
                pv.MODIFIED_STAMP,
                usr.FULL_NAME, 
                usr.USER_EMAIL,
                CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
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,
  USERS usr,
  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)
AND b.branch_id = :BRANCH_ID
AND pv.MODIFIER_ID = usr.USER_ID(+)
ORDER BY UPPER(bn.bom_name), UPPER(version), UPPER(nn.node_name), UPPER(os.os_name)