-- #Doc: read all DS from IR filterd by pdt.pdatasettype_name ...
-- #LUp: 17.04.2018 J.Fes add DataSetReleasedDateWSO
-- #LUp: 10.09.2021 S.Gueth+J.Fes add case for DataSetType
SELECT
 pd.puid               As DataSetPuid,
 s.psite_id            As OwningSite_TC_pSite_ID,
 ptype.ptype_name      As DatasetRelation,
 case when pdt.pdatasettype_name is not NULL then pdt.pdatasettype_name
                       else wso.pobject_type end
                       As DataSetType,
 wso.pobject_name      As DataSetName,
 wso.pobject_desc      As DataSetDescription,
 -- #Doc: 23.07.2024/J.Fes if required extend to return DataSetApplication Info such as Teamcenter,NXPartFamilyTemplate ..
 -- #Doc: 23.07.2024/J.Fes 32 Chars
 -- wso.pobject_application      As DataSetApplication,
 rev.pseq              As pd_pseq,
 ra.phighest_rev       As pd_highest_rev,
 ra.pkeep_limit        As pd_keep_limit,
 rl.pvalu_0            As rl_pvalu_0,
 pu_owni_ds.puser_id   As DataSetOwningUser,
 pg_owni_ds.pname      As DataSetOwningGroup,
 pg_owni_ds.puid       As DataSetOwningGroupPuid,
 pao.pcreation_date    As DataSetCreateDate,
 pu_lmod_ds.puser_id   As DataSetLastModUser,
 pao.plast_mod_date    As DataSetLastModDate,
 -- o.plsd                As DataSetLastSavDate,
 wso.pdate_released    AS DataSetReleasedDateWSO,
 pd.rrev_chain_anchoru AS DataSetRevChainAnchorPuid
FROM
 infodba.PDATASET pd
 Left Join infodba.PREVISIONS       rev   On (rev.puid = pd.rrev_chain_anchoru)
 Left Join infodba.PREVISIONANCHOR  ra    On (ra.puid =  pd.rrev_chain_anchoru)
 Left Join infodba.PREF_LIST        rl    On (rl.puid = rev.pvalu_0) and (rl.pseq = 0)
 Left Join infodba.PREF_NAMES       rn    On (rn.puid = rl.puid And rl.pseq = rn.pseq)
 Left Join infodba.PDATASETTYPE     pdt   On (pdt.puid = pd.rdataset_typeu)
 Left Join infodba.PIMANRELATION    pr    On (pr.rsecondary_objectu = pd.puid)
 Left Join infodba.PIMANTYPE        ptype On (ptype.puid = pr.rrelation_typeu)
 Left Join infodba.PWORKSPACEOBJECT wso   On (wso.puid = pd.puid)
 Left Join infodba.PPOM_OBJECT      o     On (o.puid = pd.puid)
 Left Join infodba.PPOM_IMC         s     On (s.puid = o.rowning_siteu)
 Left Join infodba.PPOM_APPLICATION_OBJECT pao On (pao.puid = rev.pvalu_0)
 Left Join infodba.PPOM_USER     pu_owni_ds On (pu_owni_ds.puid = pao.rowning_useru)
 Left Join infodba.PPOM_USER     pu_lmod_ds On (pu_lmod_ds.puid = pao.rlast_mod_useru)
 Left Join infodba.PPOM_GROUP    pg_owni_ds On (pg_owni_ds.puid = pao.rowning_groupu)
WHERE
     pr.rprimary_objectu = :para_puid
 and rev.pseq <= :para_pseq
/* #SQL.HINT.ImportAllDatasets# */ and (pdt.pdatasettype_name in ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#') or wso.pobject_type in ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#'))
ORDER BY
 pd.puid, rev.pseq

