Benennung aus dem Workspace Object

SELECT
  ROWNUM               AS "count",
  WSO.PUID             AS "wso_uid",
  WSO.POBJECT_NAME     AS "Name",
  WSO.POBJECT_TYPE     AS "Typ",
  WSO.POBJECT_DESC     AS "DESC"
FROM  
  INFODBA.PIMANRELATION REL ,  
  INFODBA.PWORKSPACEOBJECT WSO,  
  INFODBA.PIMANTYPE TYP, 
  INFODBA.PDATASET DS,    
  INFODBA.PIMANRESERVATION RES
WHERE
       REL.RRELATION_TYPEU=TYP.PUID 
  AND  TYP.PTYPE_NAME = 'IMAN_RES_checkout'  
  AND  REL.RSECONDARY_OBJECTU=RES.PUID 
  AND  RES.PRESERVATION_STATE = 0 
  AND  REL.RPRIMARY_OBJECTU = DS.RREV_CHAIN_ANCHORU
  AND  WSO.PUID = DS.PUID
  AND  ROWNUM < 1000
--------------------------------------------------------------------------------
Aktueller Status neu 18.11.2008 11:10 von Thomas
  select
     s.pname
    from
     infodba.PRELEASE_STATUS_LIST_0 sl,
     infodba.PRELEASESTATUS s
    where
         sl.puid         = ?  ''
     and sl.pvalu_0      = s.puid

'000821BG                         A                                wVBlwSJcVe1piD
--------------------------------------------------------------------------------
-- Finden Mutterteil (get Item) ausgehend von einem PFM-Part Item
				select
					i.pitem_id,
					i.puid
				from
					infodba.pitem i,
					infodba.pimanrelation r,
					infodba.pfolder f,
					infodba.pworkspaceobject wso,
					infodba.pcontents c
				where
					    c.pvalu_0       = 'REDlidw4Ve1piD'
					and wso.puid        = f.puid
					and c.puid          = f.puid
					and wso.pobject_name    = 'Family Members'
					and r.rsecondary_objectu  = f.puid
					and i.puid          = r.rprimary_objectu

	--				'RcMlidw4Ve1piD' -> DatasetId
	--				'REKlidw4Ve1piD' -> IrPuid     : PFMPart-1/A
 --     pcontents_0     -> pcontents
 
-- Finden Family Members (get Items) eines Mutterteil's (Start Item)
    select
   		i.pitem_id,
   		i.puid
   	from
   		infodba.pitem    itf,
   		infodba.pfolder f,
   		infodba.pcontents c,
   		infodba.pworkspaceobject wso,
   		infodba.pitem i,
   		infodba.pimanrelation pr
   	where
   		itf.puid            = 'QZFlidw4Ve1piD' -- -- Bspl 'QZFlidw4Ve1piD'  PFMMaster
   		and itf.puid        = pr.rprimary_objectu
   		and pr.rsecondary_objectu = f.puid
   		and f.puid          = wso.puid
   		and upper(wso.pobject_name) = 'FAMILY MEMBERS'
   		and c.puid          = f.puid
   		and i.puid          = c.pvalu_0
  
  
--------------------------------------------------------------------------------
Finden alle Versionen eines DataSets
				select
					rev.pvalu_0,
					rev.pseq
				from
					infodba.pdataset pd,
					infodba.previsions_0 rev
				where
					pd.puid             = ?
					and pd.RREV_CHAIN_ANCHORU = rev.puid

Finden alle checked out DataSets

				select
					res.ruser_objectu,
					res.rgroup_objectu,
					TO_CHAR(res.pdate,'dd.mm.yyyy hh:mi:ss') pdate
				from
					INFODBA.PIMANRELATION rel,
					INFODBA.PIMANTYPE typ,
					INFODBA.PIMANRESERVATION res,
					infodba.pdataset ds
				where
					     rel.rprimary_objectu   = ds.rrev_chain_anchoru
					and  rel.rrelation_typeu    = typ.puid
					and  typ.ptype_name         = 'IMAN_RES_checkout'
					and  rel.rsecondary_objectu = res.PUID

--------------------------------------------------------------------------------
BOM precise?

				select
					bvr.puid,
					bvr.pis_precise       		-- kann nur 0 = imprecise oder 1 = precise sein
				from
					infodba.PPSBOMVIEWREVISION bvr,
					infodba.PSTRUCTURE_REVISIONS sr
				where
					sr.puid          = ?  		-- PUID der ItemRevision
					and bvr.puid     = sr.pvalu_0

Ermitteln BOM-Lines presice

            select
              bo.puid,
              bo.pseq_no,
              bo.pqty_value
            from
              infodba.PPSBOMVIEWREVISION bvr,
              infodba.PPSOCCURRENCE bo
            where
              bvr.puid            = ?
              and bvr.puid        = bo.rparent_bvru

Inhalt BOM-Lines (occurrences notes) für präzise BOM's

		select
      i.pitem_id,
      i.puid I_PUID,
      ir.pitem_revision_id,
      ir.puid IR_PUID,
      i.ruom_tagu,
		    pt.pname,
		    ptt.pval_0,
		    ptt.pseq,
		    ptt.puid PUID_PTT
		from
      infodba.PITEM i,
      infodba.pitemrevision ir,
		    infodba.PPSOCCURRENCE bo,
		    infodba.pnote_texts_0 ptt,
		    infodba.pnote_types_0 pty,
		    infodba.pnotetype pt
		where
          bo.puid         = ? -- ir_puid ?
      and ir.puid     = bo.rchild_itemu
      and ptt.puid    = bo.rnotes_refu
		    and pty.puid    = bo.rnotes_refu
		    and pty.pseq    = ptt.pseq
		    and pt.puid     = pty.pvalu_0
		order by
		    ptt.pseq
		    
----------
Inhalt alle BOM Lines 
Dim SQL_STL_IMPRECISE_QTY as String = _

  select
    ir.puid  
  FROM   
    infodba.pom_backpointer bp, 
    infodba.pitemrevision ir, 
    infodba.pitem i, 
    infodba.PPSBOMVIEWREVISION bvr, 
    infodba.PPSOCCURRENCE bo 
  WHERE  
        bp.from_uid = ?  
    and bvr.puid    = bp.to_uid  
    and bvr.puid    = bo.rparent_bvru
    and i.puid      = bo.rchild_itemu 
    and i.puid      = bo.rchild_itemu 
    and ir.ritems_tagu=i.puid
  order by ir.pitem_revision_id


Inhalt BOM-Lines (occurrences notes) für impräzise BOM's

            select
              i.pitem_id,
              i.puid I_PUID,
              i.ruom_tagu,
              pt.pname,
              ptt.pval_0,
              ptt.pseq,
              ptt.puid PUID_PTT
            from
              infodba.PITEM i,
              infodba.PPSOCCURRENCE bo,
              infodba.pnote_texts_0 ptt,
              infodba.pnote_types_0 pty,
              infodba.pnotetype pt
            where
                  bo.puid         = ?
              and i.puid          = bo.rchild_itemu
              and ptt.puid        = bo.rnotes_refu
              and pty.puid        = bo.rnotes_refu
              and pty.pseq        = ptt.pseq
              and pt.puid         = pty.pvalu_0
            order by
              ptt.pseq
              
---------------
/*
-- BOM über Item und IrId ermitteln
SELECT
 ir1.puid, i.pitem_id , ir1.pitem_revision_id
FROM
 infodba.pstructure_revisions ps,
 infodba.pitem i,
 infodba.pitem i1,
 infodba.pitemrevision ir1,
 infodba.ppsbomviewrevision bvr,
 infodba.ppsoccurrence bo
WHERE
     i1.pitem_id = :para_i_pitem_id
 AND ir1.pitem_revision_id = :para_ir_pitem_revision_id
 AND i1.puid = ir1.ritems_tagu
 AND ps.puid = ir1.puid
 AND ps.pvalu_0 = bvr.puid
 AND bvr.puid = bo.rparent_bvru
 AND i.puid = bo.rchild_itemu
*/

/* listet alle verbauten items einer Baugruppe 
   mit hoechster Revision nach Buchstabe Jopps Stand vom 25.11.2007 */
SELECT
 ir.puid ir_puid ,
 i.pitem_id As i ,
 ir.PITEM_REVISION_ID as ir
 -- , ir2.puid, i2.pitem_id,  ir2.pitem_revision_id, i.puid
FROM
 infodba.pstructure_revisions ps,
 infodba.pitem i,
 infodba.pitem i2,
 infodba.pitemrevision ir,
 infodba.pitemrevision ir2,
 infodba.ppsbomviewrevision bvr,
 infodba.ppsoccurrence bo
WHERE
     i2.pitem_id = :para_i_pitem_id -- 'impraeziese1'
 AND ir2.pitem_revision_id = :para_ir_pitem_revision_id -- 'A'
 AND i2.puid = ir2.ritems_tagu
 AND ps.puid = ir2.puid
 AND ps.pvalu_0 = bvr.puid
 AND bvr.puid = bo.rparent_bvru
 AND i.puid = bo.rchild_itemu
 AND ir.PITEM_REVISION_ID = (
   select max(ir3.PITEM_REVISION_ID) as ITEM_REVISION_ID
   from infodba.pitem i3, infodba.pitemrevision ir3
   where i3.puid = i.puid AND ir3.ritems_tagu = i3.puid
   group by i3.PUID)
 AND ir.ritems_tagu = i.puid
order by i.pitem_id , ir.PITEM_REVISION_ID


/* Stand von Thomas 25.11.2007 */
Probier das mal --> listet alle BOM Lines aller IR's des Items
 
SELECT
 i.PITEM_ID As I_PITEM_ID , bo.pseq_no, i1.pitem_id 
FROM
 infodba.pitem i
 ,infodba.pitem i1
 ,infodba.pbom_view_tags_0 bvt
 ,infodba.PPSBOMVIEWREVISION bvr
 ,infodba.ppsoccurrence bo
WHERE
     i.pitem_id = 'M8631000'
 AND bvt.puid  = i.puid 
 and bvt.pvalu_0   = bvr.rbom_viewu
 AND bvr.puid= bo.rparent_bvru
 --AND bo.rchild_bvu = bv.puid
 and i1.puid=bo.rchild_itemu

---------------------------
/* liefert letzte IR eines Items */
/*
select i.PUID,  max(ir.PITEM_REVISION_ID) as ITEM_REVISION_ID
from infodba.pitem i, infodba.pitemrevision ir
where i.puid = 'RFOdDv3nVe1piD' AND ir.ritems_tagu = i.puid
group by i.PUID

select  max(ir.PITEM_REVISION_ID) as ITEM_REVISION_ID
from infodba.pitem i, infodba.pitemrevision ir
where i.puid = 'RFOdDv3nVe1piD' AND ir.ritems_tagu = i.puid
group by i.PUID
*/

---------------------------
Auflisten aller User die in der Db eingeloogt sind:

select
  distinct s.puser name, s.pnode name
from 
  infodba.PPOM SESSION s
---------------------------
Auflisten aller User:

select distinct
 pu1.puser id PUSER ID OWNER,
 pg1.pname PNAME OWNER
from
 infodba.PPOM APPLICATION OBJECT pao,
 infodba.PPOM GROUP pg1,
 infodba.PPOM USER pu1
where
 pao.rowning useru       = pu1.puid
 and pao.rowning groupu  = pg1.puid

