/* Descr: Count all Datasets Group by Type for TCUA
 #New:12.12.2022/J.Fes  */
SELECT DISTINCT
-- i.pitem_id AS ItemId,
-- ir.pitem_revision_id AS IrId,
 pdt.pdatasettype_name AS DatasetType,
 count(*) AS nDatasets
--  i.puid AS ItemPuid,
-- paoI.plast_mod_date AS ItemLastModDate,
-- ir.puid AS IrPuid,
-- 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.PREVISIONANCHOR a ON (a.puid = pd.rrev_chain_anchoru)
 LEFT JOIN infodba.PREVISIONS rev ON (rev.pvalu_0 = pd.puid)
 LEFT JOIN infodba.PWORKSPACEOBJECT pwo On (ir.puid = pwo.puid)
WHERE
 pdt.pdatasettype_name in ('UGMASTER','UGPART','UGALTREP','UGSCENARIO','UGMANIFESTATION','NXSIMULATION','CAEGEOM','CAEMesh','CAESolution')
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
 AND ROWNUM <= 100
  -- AND i.pitem_id = 'RW71064'
  -- AND ir.pitem_revision_id = '1'
  group by pdt.pdatasettype_name
  -- i.pitem_id, ir.pitem_revision_id,
  -- order by i.pitem_id, ir.pitem_revision_id
;
