/* TCUA:Item IRs DS L.Up:02.3.15
   Offene Punkte: Export Recodes, Project's
*/

SELECT
  -- Item
  i.pitem_id             AS I_Id,
  i.puid                 AS I_puid,
  paoI.pcreation_date    AS I_CreationDate,
  paoI.plast_mod_date    AS I_LastModDate,
  oI.plsd                AS I_LastSavedDate,
  pgI.pname              AS I_Group,

  -- Ir
  ir.pitem_revision_id   AS Ir_Id,
  ir.puid                AS Ir_puid,
  paoIr.pcreation_date   AS Ir_CreationDate,
  paoIr.plast_mod_date   AS Ir_LastModDate,
  oIr.plsd               AS Ir_LastSavedDate,
  pgIr.pname             AS Ir_Group,

  -- Ds
  -- wso.pobject_name      As DataSetName,
 wsoDS.pobject_name      AS DataSetName,
 pdtDS.pdatasettype_name AS DataSetType,
 pd.puid                 AS DataSetPuid,
 sDS.psite_id            AS OwningSite_TC_pSite_ID,
 ptypeDS.ptype_name      AS DataSetptypeDS_NAME,
 wsoDS.pobject_desc      AS DataSetDescription,
-- pd.rrev_chain_anchoru As pd_rrev_chain_anchoru,

-- rev.pvalu_0           As rev_pvalue_0,
-- rl.pvalu_0            As rl_pvalu_0,
 pu_owni_DS.puser_id     AS DataSetOwningUser,
 pg_owni_DS.pname        AS DataSetOwningGroup,
 paoDS.pcreation_date    AS DataSetCreateDate,
 pu_lmod_DS.puser_id     AS DataSetLastModUser,
 paoDS.plast_mod_date    AS DataSetLastModDate,
 
 imv.pmachine_type     As imv_pmachine_type,
 imv.pvolume_name      As imv_pvolume_name,
 imv.punix_path_name   As imv_punix_path_name,
 imv.pwnt_path_name    As imv_pwnt_path_name,
 imf.psd_path_name     As imf_psd_path_name,
 imf.pfile_name        As imf_pfile_name,
 pao.plast_mod_date    As imf_lastmoddat 


FROM
 -- OP: Site / Export to
 -- Item I/i
           infodba.PITEM                   i
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoI  ON (i.puid = paoI.puid)
 LEFT JOIN infodba.PPOM_OBJECT             oI    ON (i.puid = oI.puid)
 LEFT JOIN infodba.PPOM_GROUP              pgI   ON (pgI.puid = paoI.rowning_groupu)

 -- Revision ir/Ir
 LEFT JOIN infodba.PITEMREVISION           ir    ON (i.puid = ir.ritems_tagu)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr ON (ir.puid = paoIr.puid)
 LEFT JOIN infodba.PPOM_OBJECT             oIr   ON (ir.puid = oIr.puid)
 LEFT JOIN infodba.PPOM_GROUP              pgIr  ON (pgIr.puid = paoIr.rowning_groupu)
 LEFT JOIN infodba.PWORKSPACEOBJECT        pwo   ON (ir.puid = pwo.puid)

 -- Datasets ds/Ds
 LEFT JOIN infodba.PIMANRELATION           pr          ON (pr.rprimary_objectu = ir.puid)
 LEFT JOIN infodba.PDATASET                pd          ON (pd.puid = pr.rsecondary_objectu)

 LEFT JOIN infodba.PREF_LIST_0             rl          ON (rl.puid = pd.puid) and (rl.pseq = 0)
 LEFT JOIN infodba.PDATASETTYPE            pdtDS       ON (pd.rdataset_typeu = pdtDS.puid)
 LEFT JOIN infodba.PIMANTYPE               ptypeDS     ON (ptypeDS.puid = pr.rrelation_typeu)
 LEFT JOIN infodba.PWORKSPACEOBJECT        wsoDS       ON (wsoDS.puid = pd.puid)
 LEFT JOIN infodba.PPOM_OBJECT             oDS         ON (oDS.puid = pd.puid)
 LEFT JOIN infodba.PPOM_IMC                sDS         ON (sDS.puid = oDS.rowning_siteu) -- muss left Join sein
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoDS       ON (paoDS.puid = pd.puid)
 LEFT JOIN infodba.PPOM_USER               pu_owni_DS  ON (pu_owni_DS.puid = paoDS.rowning_useru)
 LEFT JOIN infodba.PPOM_USER               pu_lmod_DS  ON (pu_lmod_DS.puid = paoDS.rlast_mod_useru)
 LEFT JOIN infodba.PPOM_GROUP              pg_owni_DS  ON (pg_owni_DS.puid = paoDS.rowning_groupu)

 -- Dataset Files Fo = File Object
 LEFT JOIN infodba.PREF_LIST_0             rlFo        ON (rlFo.puid = pd.puid)
 LEFT JOIN infodba.PREF_NAMES              rnFo        ON (rlFo.puid = rnFo.puid And rlFo.pseq = rnFO.pseq)
 LEFT JOIN infodba.PIMANFILE               imf         ON (imf.puid = rlFo.pvalu_0)
 LEFT JOIN infodba.PIMANVOLUME             imv         ON (imf.rvolume_tagu = imv.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT pao         ON (pao.puid = imf.puid)

WHERE
 pwo.pactive_seq = 1
 AND UPPER(i.pitem_id) = UPPER('BG-Einzelteil-01')
 OR UPPER(i.pitem_id) = UPPER('ItemNoDs')
-- AND ir.pitem_revision_id = 'D' 
order by I_Id, Ir_CreationDate