/* Descr: find all Irs with spec. pdatasettype_name
   #New:16.03.2010/J.Fes
   #Upd:24.01.2012/J.Fes trans to join */
   
   -- Listet alle Datasets eines bestimmten types
SELECT DISTINCT
  pd.puid               AS DataSet_Puid,
  pdt.pdatasettype_name AS DataSet_TYPE, 
  wso.pobject_name      As DataSet_Name,
  wso.pobject_desc      As DataSet_Description,   
--  rn.pval_0,
  i.puid               AS ItemPuid,
  i.pitem_id           AS ItemId,
--  paoI.plast_mod_date  AS ItemLastModDate,
--  ir.puid              AS IrPuid,
--  ir.pitem_revision_id AS IrId,
--  paoIr.plast_mod_date AS IrLastModDate,
  s.psite_id           AS OwningSite_TC_pSite_ID
FROM       infodba.PDATASET                 pd
 LEFT JOIN infodba.PIMANRELATION            pr ON (pr.rsecondary_objectu = pd.puid)
 LEFT JOIN infodba.PITEMREVISION            ir ON (ir.puid = pr.rprimary_objectu)
 LEFT JOIN infodba.PITEM                    i ON (i.puid = ir.ritems_tagu)
 LEFT JOIN infodba.PPOM_OBJECT              o ON (ir.puid = o.puid)
 FULL JOIN infodba.PPOM_IMC                 s ON (o.rowning_siteu = s.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT  paoIr ON (paoIr.puid = ir.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT  paoI  ON (i.puid = paoI.puid)
 LEFT JOIN infodba.PDATASETTYPE             pdt ON (pdt.puid = pd.rdataset_typeu)
 LEFT JOIN infodba.PREVISIONANCHOR          a   ON (a.puid = pd.rrev_chain_anchoru)
 LEFT JOIN infodba.PREVISIONS               rev ON (rev.pvalu_0 = pd.puid)

 LEFT JOIN infodba.PREF_LIST_0              rl  On (rl.puid = pd.puid)
 JOIN infodba.PREF_NAMES_0                  rn  On (rl.puid = rn.puid And rl.pseq = rn.pseq)
 
 Inner Join infodba.PWORKSPACEOBJECT wso On (wso.puid = pd.puid) 
 
WHERE
 pdt.pdatasettype_name IN ('UGALTREP','UGPART')
 AND rev.pseq = 0
 ;
 
 
/* DS.FS 04.03.2014 J.Fes */

-- Listet von DataSet alle NamedRef's
-- #Example:TC83 SfN9QuVdTzDfjB 000013-A-dwg10 -> ERR APPUID-OBJ is missing
-- #Example:TC83 xUMlg0$FTzDfjB 000004-A-alt1  -> OK APPUID-OBJ  exist's
SELECT 
   pd.puid As DataSet_Puid
From
 infodba.PDATASET               pd
WHERE
pd.puid in ('xUMlg0$FTzDfjB','SfN9QuVdTzDfjB') 
AND
pd.puid  NOT IN (
Select -- DISTINCT
   pd.puid As DataSet_Puid
 --, rn.pval_0 AS NameREf_REF
 --, rn2.pval_0 AS NameREf_REF2
From
 infodba.PDATASET               pd
 Left 
 Join infodba.PREF_LIST_0  rl   On (pd.puid = rl.puid)
 Left outer 
 Join infodba.PREF_NAMES_0 rn   On (rl.puid = rn.puid And rl.pseq = rn.pseq and rn.pval_0 In ('APPUID-OBJ'))
 -- right Join infodba.PREF_NAMES_0 rn   On (rl.puid = rn.puid And rl.pseq = rn.pseq and rn.pval_0 In ('UGPART')) 
 Left outer 
 Join infodba.PREF_NAMES_0 rn2  On (rl.puid = rn2.puid And rl.pseq = rn2.pseq)
 -- And rn.pval_0 In ('APPUID-OBJ')
where
 pd.puid in ('xUMlg0$FTzDfjB','SfN9QuVdTzDfjB') -- tc83 
 -- and rn.pval_0 In ('UGPART') 
-- and rn.pval_0 In ('APPUID-OBJ')
 -- and rn.puid IS NULL 
 -- And rn.pval_0 In ('APPUID-OBJ')
)
;


Select -- DISTINCT
   pd.puid As DataSet_Puid
 --, rn.pval_0 AS NameREf_REF
 --, rn2.pval_0 AS NameREf_REF2
From
 infodba.PDATASET               pd
 Left 
 Join infodba.PREF_LIST_0  rl   On (pd.puid = rl.puid)
 Left outer 
 Join infodba.PREF_NAMES_0 rn   On (rl.puid = rn.puid And rl.pseq = rn.pseq and rn.pval_0 In ('APPUID-OBJ'))
 -- right Join infodba.PREF_NAMES_0 rn   On (rl.puid = rn.puid And rl.pseq = rn.pseq and rn.pval_0 In ('UGPART')) 
 Left outer 
 Join infodba.PREF_NAMES_0 rn2  On (rl.puid = rn2.puid And rl.pseq = rn2.pseq)
 -- And rn.pval_0 In ('APPUID-OBJ')
where
 pd.puid in ('xUMlg0$FTzDfjB','SfN9QuVdTzDfjB') -- tc83 
;

Select 
 i.puid               AS ItemPuid,
 i.pitem_id           AS ItemId,
 ir.puid              AS IrPuid,
 ir.pitem_revision_id AS IrId,
 pd.puid              As DataSet_Puid,
 wso.pobject_name     AS DataSetName,
 refs.pval_0          AS NameREf_REF
 -- refs.*
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)
 LEFT 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)
 
where
 -- pd.puid = :para_puid
 -- pd.puid in ('xUMlg0$FTzDfjB','SfN9QuVdTzDfjB') -- tc83 
     pdt.pdatasettype_name IN ('UGALTREP','UGPART')
 AND rev.pseq = 0
 AND refs.puid IS NULL
 
;
 

