-- TCUA:Read all IRs Data for IR sync with filter fir IRS containing Datasets
-- #New 22.05.2025/J.Fes
SELECT DISTINCT /* #ORA.HINT# */
  i.puid               AS ItemPuid,
  i.pitem_id           AS ItemId,
  paoI.plast_mod_date  AS ItemLastModDate,
  oI.plsd              AS ItemLastSavDate,
  ir.puid              AS IrPuid,
  ir.pitem_revision_id AS IrId,
  paoIr.plast_mod_date AS IrLastModDate,
  poUs.puser_id        AS IrLastModUser,
  oIr.plsd             AS IrLastSavDate,
  pwo.pdate_released   AS IrReleaseDate,

  s.psite_id           AS OwningSite_TC_pSite_ID,
  s.pname              AS IrOwningSite,

  pgIr.pname           AS IrOwningGroup,
  pgIr.puid            AS IrOwningGroupPuid

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             oI    ON (oI.puid = i.puid)
 LEFT JOIN infodba.PPOM_OBJECT             oIr   ON (oIr.puid = ir.puid)
 LEFT JOIN infodba.PPOM_IMC                s     ON (oIr.rowning_siteu = s.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoI  ON (paoI.puid = i.puid)
 LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr ON (paoIr.puid = ir.puid)

 LEFT JOIN infodba.PPOM_GROUP              pgIr  ON (pgIr.puid = paoIr.rowning_groupu)
 LEFT JOIN infodba.PPOM_USER               poUs  ON (poUs.puid = paoIr.rlast_mod_useru)


 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 (pwo.puid = ir.puid)
WHERE
     pdt.pdatasettype_name in ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#')
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
