-- Descr: find all Irs with spec. pdatasettype_name
-- #New:16.03.2010/T.Nestler + J.Fes
-- #LUp:27.05.2014/J.Fes + pwo
-- #LUp:27.07.2018/J.Fes +  paoDS.plast_mod_date >= TO_DATE ..
-- #LUp.10.08.2018/J.Fes

SELECT DISTINCT
*
FROM (

-- #Doc: 10.08.2018/J.Fes Data from IR select
SELECT DISTINCT /* #ORA.HINT# */
  i.puid               AS ItemPuid,
  i.pitem_id           AS ItemId,
  paoI.plast_mod_date  AS ItemLastModDate,
  ir.puid              AS IrPuid,
  ir.pitem_revision_id AS IrId,
  paoIr.plast_mod_date AS IrLastModDate,
  s.psite_id           AS OwningSite_TC_pSite_ID
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 o ON (ir.puid = o.puid)
 LEFT JOIN infodba.PPOM_IMC s ON (o.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.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 (ir.puid = pwo.puid)
WHERE
     pdt.pdatasettype_name IN ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#')
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
 AND paoIr.plast_mod_date >= TO_DATE(:para_plast_mod_date_min, 'DD.MM.YYYY HH24:MI')
 AND paoIr.plast_mod_date <= TO_DATE(:para_plast_mod_date_max, 'DD.MM.YYYY HH24:MI')
 AND ir.puid Is Not Null

-- #Doc: 10.08.2018/J.Fes add Data from DS select
UNION ALL

SELECT DISTINCT /* #ORA.HINT# */
  i.puid               AS ItemPuid,
  i.pitem_id           AS ItemId,
  paoI.plast_mod_date  AS ItemLastModDate,
  ir.puid              AS IrPuid,
  ir.pitem_revision_id AS IrId,
  paoIr.plast_mod_date AS IrLastModDate,
  s.psite_id           AS OwningSite_TC_pSite_ID
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 o ON (ir.puid = o.puid)
 LEFT JOIN infodba.PPOM_IMC s ON (o.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_APPLICATION_OBJECT paoDS ON (paoDS.puid = pd.puid)
 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 (ir.puid = pwo.puid)
WHERE
     pdt.pdatasettype_name IN ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#')
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
 AND paoDS.plast_mod_date >= TO_DATE(:para_DS_plast_mod_date_min, 'DD.MM.YYYY HH24:MI')
 AND paoDS.plast_mod_date <= TO_DATE(:para_plast_mod_date_max, 'DD.MM.YYYY HH24:MI')
 AND ir.puid Is Not Null
)
/*MsSQL AS DataTbl MsSQL*/
