/* #New: 30.01.2014/J.Fes 
   #Doc. Listet alle CAD DS einer Precise BOM 
   #Doc: Param = ir.puid 
   #Doc: template: Bom_Precise_QTY_BomLineUgGometryNo_DoCountYES.SQL
   */
SELECT  
 i.pitem_id             AS ItemId,
 ir.pitem_revision_id   AS IrId,
 ir.puid                AS IrPuid,
 pdt.pdatasettype_name  AS DataSetType,
 wso.pobject_name       AS DataSetName,
 count(*)               AS DataSetsNTimesAdd
FROM
 infodba.POM_BACKPOINTER bp
 INNER JOIN infodba.PPSBOMVIEWREVISION bvr ON (bvr.puid = bp.to_uid)
 INNER JOIN infodba.PPSOCCURRENCE      bo ON (bo.rparent_bvru = bvr.puid)
 INNER JOIN infodba.PITEMREVISION      ir ON (ir.puid = bo.rchild_itemu)
 INNER JOIN infodba.PITEM              i ON (i.puid = ir.ritems_tagu)

 LEFT JOIN infodba.PPSBOMVIEW       bv  on (bv.puid = bvr.rbom_viewu)
 LEFT JOIN infodba.PPSVIEWTYPE      bvt on (bvt.puid = bv.rview_typeu)
 LEFT JOIN infodba.PIMANRELATION    pir on (pir.rprimary_objectu = ir.puid)
 LEFT JOIN infodba.PDATASET         pd  on (pd.puid = pir.rsecondary_objectu)
 LEFT JOIN infodba.PDATASETTYPE     pdt on (pdt.puid = pd.rdataset_typeu)
 LEFT JOIN infodba.PWORKSPACEOBJECT wso on (wso.puid = pd.puid)
 LEFT JOIN infodba.PREF_LIST_0      rl  On (rl.puid = pd.puid)
 LEFT Join infodba.PREF_NAMES_0     rn  On (rn.pseq = rl.pseq) and (rn.puid = rl.puid)
 LEFT Join infodba.PREVISIONS       rev On (rev.puid = pd.rrev_chain_anchoru) and (rev.pvalu_0 = rn.puid) 
WHERE
 (upper(bvt.pname) = 'VIEW' OR upper(bvt.pname) = 'CATIA_MULTI_MODEL' OR upper(bvt.pname) = 'CATIA')
 and bp.from_uid = :para_ir_puid
 and rn.pval_0 in ('UGPART','IdeasPart','IdeasAssembly','IdeasDrawing','AsmFile','PrtFile','DrwFile','catpart','catproduct','catdrawing')
 and rev.pseq = 0
 GROUP BY i.pitem_id, ir.pitem_revision_id, ir.puid, pdt.pdatasettype_name, wso.pobject_name
order by DataSetsNTimesAdd desc
