--
-- SQL statement to count and list all UGALTREP and UGPART missing APPUID-OBJ
-- Teamcenter version 9.1.2.8_a01_5
--
--  17.02.2016 Duszkiewicz Krzysztof  initial creation
--

--COUNT
SELECT
  pdt.pdatasettype_name AS DatasetType,
  imc.pname AS OwningSite,
  COUNT(pdt.pdatasettype_name) AS Counter
FROM
 infodba.PDATASET pd
 LEFT JOIN
 (
   SELECT rli.puid, rni.pval_0
   FROM infodba.PREF_LIST_0 rli
   INNER JOIN infodba.PREF_NAMES_0 rni ON (rli.puid = rni.puid AND rli.pseq = rni.pseq)
   WHERE rni.pval_0 = 'APPUID-OBJ'
 ) refs ON (refs.puid = pd.puid)

 LEFT  JOIN infodba.PDATASETTYPE   pdt ON (pdt.puid = pd.rdataset_typeu)
 LEFT  JOIN infodba.PREVISIONS     rev ON (rev.pvalu_0 = pd.puid)
 LEFT  JOIN infodba.PPOM_OBJECT    obj ON (obj.puid = pd.puid)
 LEFT  JOIN infodba.PPOM_IMC       imc ON (imc.puid = obj.rowning_siteu)

WHERE
 --pdt.pdatasettype_name IN ('UGALTREP','UGPART')
 --pdt.pdatasettype_name = 'UGALTREP'
 pdt.pdatasettype_name = 'UGPART'
 AND rev.pseq = 0
 AND refs.puid IS NULL
 GROUP BY pdt.pdatasettype_name,imc.pname
 ORDER BY DatasetType,OwningSite,Counter
 ;

 --LIST
 --
 -- Only Owning Site datasets will be listed
  SELECT
  i.puid                AS ItemPuid,
  i.pitem_id            AS ItemId,
  ir.puid               AS IrPuid,
  ir.pitem_revision_id  AS IrId,
  pd.puid               AS DatasetPuid,
  wso.pobject_name      AS DatasetName,
  pdt.pdatasettype_name AS DatasetType,
  refs.pval_0           AS NamedRef_REF,
  imc.pname             AS OwningSite

FROM
 infodba.PDATASET pd
 LEFT JOIN
 (
   SELECT rli.puid, rni.pval_0
   FROM infodba.PREF_LIST_0 rli
   INNER JOIN infodba.PREF_NAMES_0 rni ON (rli.puid = rni.puid AND rli.pseq = rni.pseq)
   WHERE rni.pval_0 = 'APPUID-OBJ'
 ) refs ON (refs.puid = pd.puid)

 LEFT  JOIN infodba.PDATASETTYPE      pdt ON (pdt.puid = pd.rdataset_typeu)
 LEFT  JOIN infodba.PREVISIONS        rev ON (rev.pvalu_0 = pd.puid)
 LEFT  JOIN infodba.PIMANRELATION      pr ON (pr.rsecondary_objectu = pd.puid)
 RIGHT JOIN infodba.PITEMREVISION      ir ON (ir.puid = pr.rprimary_objectu)
 LEFT  JOIN infodba.PITEM               i ON (i.puid = ir.ritems_tagu)
 LEFT  JOIN infodba.PWORKSPACEOBJECT  wso ON (wso.puid = pd.puid)
 LEFT  JOIN infodba.PPOM_OBJECT       obj ON (obj.puid = wso.puid)
 LEFT  JOIN infodba.PPOM_IMC          imc ON (imc.puid = obj.rowning_siteu)

WHERE
 pdt.pdatasettype_name IN ('UGALTREP','UGPART')
 --pdt.pdatasettype_name = 'UGALTREP'
 --pdt.pdatasettype_name = 'UGPART'
 AND rev.pseq = 0
 AND refs.puid IS NULL
 AND obj.rowning_siteu IS NULL
 ;
