/* Descr: find all Irs with spec. pdatasettype_name for TCUA IEC on Volume ..
   #New:17.09.2013/J.Fes 
   #LUp:27.05.2014/J.Fes + pwo */
SELECT DISTINCT
--  imv.pvolume_name ,
  pwo.pobject_type AS IrType,
  pd.puid AS PD_PUID, 
  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)
 LEFT 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.PREVISIONS rev ON (rev.pvalu_0 = pd.puid)
 LEFT JOIN infodba.PWORKSPACEOBJECT pwo On (ir.puid = pwo.puid)
 
 Left Join infodba.PREF_LIST  rl  On (rl.puid = pd.puid)
 Left Join infodba.PREF_NAMES rn  On (rl.puid = rn.puid And rl.pseq = rn.pseq)
 Left Join infodba.PIMANFILE    imf On (imf.puid = rl.pvalu_0)
 Left Join infodba.PIMANVOLUME  imv On (imf.rvolume_tagu = imv.puid)
 
WHERE
--     pdt.pdatasettype_name IN ('UGMASTER','UGPART','UGALTREP','UGMANIFESTATION','UGSCENARIO','NXSIMULATION','CAEGEOM','CAEMesh','CAESolution','IdeasPart','IdeasAssembly','IdeasDrawing','ProPrt','ProAsm','ProDrw')
     pdt.pdatasettype_name = 'UGMASTER'
-- AND upper(pwo.pobject_type) = 'S_DESIGN REVISION'
 AND upper(pwo.pobject_type) = 'ITEMREVISION'
 AND rn.pval_0 In ('UGPART','IdeasPart','IdeasAssembly','IdeasDrawing','AsmFile','PrtFile','DrwFile','catpart','catproduct','catdrawing')
 AND upper(imv.pvolume_name) = 'VOLUME2'
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
 AND ROWNUM <= 10
-- AND imv_pvolume_name = 'volume1'
-- AND rl.puid = 'SmA9BERJTzDfjB'
 -- AND i.pitem_id = '000080'
;

 
