-- =====================================================
-- UserWipUserDangle.sql
-- Keep in step with 'WorkNotInProgressByUser.sql'
--
--   Used by index.asp to list UnRelease Package Versions 
--   modified by the user, but not in use anywhere
-- =====================================================
SELECT pkg.PKG_NAME,
       rel.PKG_VERSION,
       rel.PV_ID,
       rel.PKG_ID,
       rel.MODIFIED_STAMP,
       SUBSTR(rel.COMMENTS, 1, 60) AS COMMENTS
  FROM (
        /* Get Dangling Packages */
        SELECT pv.PV_ID, pv.PKG_ID, pv.PKG_VERSION, pv.COMMENTS, pv.MODIFIED_STAMP
          FROM PACKAGE_VERSIONS pv
		 WHERE pv.pv_id not in (select pv_id from PLANNED )
           AND pv.pv_id not in (select pv_id from WORK_IN_PROGRESS )
           AND pv.pv_id not in (select pv_id from RELEASE_CONTENT )
           AND pv.pv_id not in (select rtd_id from RUNTIME_DEPENDENCIES)
           AND pv.pv_id not in ( SELECT pv_id FROM PACKAGE_PROCESSES )
           AND pv.pv_id not in (select prod_id from DEPLOYMENT_MANAGER.OS_CONTENTS )
           AND pv.pv_id not in (select prod_id from DEPLOYMENT_MANAGER.BASE_ENV_CONTENTS )
           AND pv.MODIFIER_ID = :USER_ID
           AND pv.dlocked        != 'Y'
         
       ) rel,
       PACKAGES pkg
 WHERE rel.PKG_ID = pkg.PKG_ID
ORDER BY UPPER(pkg.PKG_NAME)    

