-- #New:30.04.2019/J.Fes
-- #LUp:03.05.2019/J.Fes first release
-- Descr: find all Irs
-- #Doc: finde IRs >= Ir.RelDate >= Ir.LastMod
-- #Doc: finde Ds  >= Ds.RelDate

-- Declare @para_plast_mod_date_min datetime,
--         @para_plast_mod_date_max datetime,
--         @para_DS_plast_mod_date_min datetime,
--         @para_plast_rel_date_min datetime,
--         @para_plast_rel_date_max datetime
--
--
-- set @para_plast_mod_date_min = '2019-05-04 06:56:00'
-- set @para_plast_rel_date_min = '2019-05-02 06:56:00'
-- set @para_DS_plast_mod_date_min = @para_plast_mod_date_min
-- set @para_plast_mod_date_max = '2020-05-05 21:20'

SELECT DISTINCT
*
FROM (
SELECT DISTINCT
 i.puid AS ItemPuid,
 ir.puid AS IrPuid,
 i.pitem_id AS ItemId,
 ir.pitem_revision_id AS IrId,
 paoIr.plast_mod_date AS IrLastModDate,
 sl.pseq,
 'IR' AS TYPE,
 rs.pdate_released As ObjReleaseDate,
 rs.pname As ObjReleaseState,
 paoI.plast_mod_date AS ItemLastModDate,
 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)
 -- get release
 LEFT JOIN infodba.PRELEASE_STATUS_LIST sl ON (sl.puid = ir.puid)
 LEFT JOIN infodba.PRELEASESTATUS rs ON (sl.pvalu_0 = rs.puid)
WHERE
 pdt.pdatasettype_name IN ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#')
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
 AND ir.puid Is Not Null
 AND (sl.pseq =
    (select Max(sl.pseq) AS MaxWert
      FROM infodba.PRELEASE_STATUS_LIST
      LEFT JOIN infodba.PRELEASE_STATUS_LIST sl ON (sl.puid = ir.puid)
      LEFT JOIN infodba.PRELEASESTATUS rs ON (sl.pvalu_0 = rs.puid)
     )  or sl.pseq is NULL) -- #Doc ) or sl.pseq is NULL) dadurch werden auch die Last Mod gefunden
 AND (
       paoIr.plast_mod_date > TO_DATE(:para_plast_mod_date_min, 'DD.MM.YYYY HH24:MI') -- @para_plast_mod_date_min
         OR
       rs.pdate_released > TO_DATE(:para_plast_rel_date_min, 'DD.MM.YYYY HH24:MI') -- @para_plast_rel_date_min
      )
 AND paoIr.plast_mod_date <= @para_plast_mod_date_max
UNION ALL
SELECT DISTINCT
 i.puid AS ItemPuid,
 ir.puid AS IrPuid,
 i.pitem_id AS ItemId,
 ir.pitem_revision_id AS IrId,
 paoIr.plast_mod_date AS IrLastModDate,
 sl.pseq,
 'DS' AS TYPE,
 rs.pdate_released As ObjReleaseDate,
 rs.pname As ObjReleaseState,
 paoI.plast_mod_date AS ItemLastModDate,
 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)
 -- get release
 LEFT JOIN infodba.PRELEASE_STATUS_LIST sl ON (sl.puid = pd.puid)
 LEFT JOIN infodba.PRELEASESTATUS rs ON (sl.pvalu_0 = rs.puid)
WHERE
 pdt.pdatasettype_name IN ('UGMASTER','UGPART','UGALTREP','#TCSQL_PDATASETTYPE_pdatasettype_name#')
 AND rev.pseq = 0
 AND pwo.pactive_seq = 1
 AND ir.puid Is Not Null
 AND (sl.pseq =
    (select Max(sl.pseq) AS MaxWert
      FROM infodba.PRELEASE_STATUS_LIST
      LEFT JOIN infodba.PRELEASE_STATUS_LIST sl ON (sl.puid = pd.puid)
      LEFT JOIN infodba.PRELEASESTATUS rs ON (sl.pvalu_0 = rs.puid)
     )
     )
     -- or sl.pseq is NULL) -- #Doc ) or sl.pseq is NULL) dadurch werden auch die Last Mod gefunden
 AND (
      -- paoDS.plast_mod_date >= @para_DS_plast_mod_date_min
      --   OR
       rs.pdate_released > TO_DATE(:para_plast_rel_date_min, 'DD.MM.YYYY HH24:MI') -- @para_plast_rel_date_min
      )
 AND paoIr.plast_mod_date <= TO_DATE(:para_plast_mod_date_max, 'DD.MM.YYYY HH24:MI') -- @para_plast_mod_date_max
)
AS DataTbl

