Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Objects_tbl_Debug]'
Print ' Creation Date : 30.08.2023'
Print ' Autor         : J.Feuerstein'
Print '-------------------------------------------------------------------------'

use [#DB_N#]


Print '-- [Objects_tbl_Get_IRs_forSync] [#New: 30.08.2023/J.Fes] ---------------'
If Exists (select Name
           from sysobjects
           where Name = "Objects_tbl_Get_IRs_forSync" and type = "P")
Drop Procedure Objects_tbl_Get_IRs_forSync
GO


Create Procedure Objects_tbl_Get_IRs_forSync
 @ObjectFromSiteID nvarchar(32),
 @ObjectLastModDate datetime = null,
 @ObjectLastSavDate datetime = null
-- #New:30.08.2023/J.Fes
-- Returns for @ObjectFromSiteID all IR and ItemData
As
BEGIN

declare @ExecuteStr nvarchar(max)

set @ExecuteStr='SELECT
  Item_tbl.ObjectPuid AS ItemObjectPuid,
  Item_tbl.ObjectName AS ItemObjectName,
  Item_tbl.ObjectClass AS ItemObjectClass,
  Item_tbl.ObjectLastModDate AS ItemObjectLastModDate,
  Item_tbl.Id AS ItemJobMgrId,
  Ir_tbl.Id,
  Ir_tbl.ObjectPuid,
  Ir_tbl.ObjectName,
  Ir_tbl.ObjectCliName,
  Ir_tbl.ObjectFromSiteID,
  Ir_tbl.ObjectOwningSiteID,
  Ir_tbl.ObjectGroup,
  Ir_tbl.ObjectLastModUser,
  Ir_tbl.ObjectClass,
  Ir_tbl.ObjectType,
  Ir_tbl.ObjectOwner,
  Ir_tbl.ObjectLastModDate,
  Ir_tbl.ObjectLastSavDate,
  Ir_tbl.ObjectCreateDate,
  Ir_tbl.ObjectExportRecord,
  Ir_tbl.ObjectExportRecordDate
FROM
  dbo.Objects_Ir_Rel_tbl IrToItem
  RIGHT OUTER JOIN dbo.Objects_tbl Ir_tbl WITH (nolock) ON (Ir_tbl.Id = IrToItem.ObjectId)
  INNER JOIN dbo.Objects_tbl Item_tbl ON (IrToItem.IrId = Item_tbl.Id)
WHERE
  Ir_tbl.ObjectClass = ''IR'''

  -- #Doc: add @ObjectFromSiteID
  set @ExecuteStr = @ExecuteStr + ' AND Ir_tbl.ObjectFromSiteID = ''' + @ObjectFromSiteID + ''''

  -- #Doc: add where condition for @ObjectLastModDate
  If @ObjectLastModDate IS NOT NULL
    set @ExecuteStr = @ExecuteStr + ' AND Ir_tbl.ObjectLastModDate >= ''' + Convert(varchar(120), @ObjectLastModDate) + ''''

  -- #Doc: add where condition for @ObjectLastSavDate
  If @ObjectLastSavDate IS NOT NULL
    set @ExecuteStr = @ExecuteStr + ' AND Ir_tbl.ObjectLastSavDate >= ''' + Convert(varchar(120), @ObjectLastSavDate) + ''''


--  print ('execute ' + @ExecuteStr)
 execute (@ExecuteStr)

END

GO

  -- #Test ----------
  declare @ObjectLastModDate datetime
  set @ObjectLastModDate = Convert(datetime,'2021-09-04 08:36:39',120)

  declare @ObjectLastSavDate datetime
  set @ObjectLastSavDate = Convert(datetime,'2019-09-04 08:36:39',120)

  execute Objects_tbl_Get_IRs_forSync 'TC11ADDPLM', NULL, NULL
  execute Objects_tbl_Get_IRs_forSync 'TC11ADDPLM', @ObjectLastModDate , @ObjectLastSavDate
  execute Objects_tbl_Get_IRs_forSync 'TC11ADDPLM', NULL , @ObjectLastSavDate
Go

