Downloading CDPOS Data via RFC

CDPOS clustered tables can be large and time-consuming to download, and they often require filters so that the tables can be downloaded in smaller sections over a period of time. Typical design structures and join logic can cause syntax errors in Collect for these large, clustered CDPOS tables. 

You must use the UDATE column to filter on, but it only exists in CDHDR and not in CDPOS. These steps ensure that CDHDR downloads completely before CDPOS begins, filtering on UDATE (updated with the Before Rule). Then an assumption is made that the CHANGENR is increasing with UDATE when we update the filter on CDPOS. This is necessary because RFC downloads are very limited in the Where Clause Override setting. The necessary JOIN would use too many characters (the setting is limited to 70 characters).  

To avoid syntax errors and long download times, you can use the following steps to set up the download for a filtered CDPOS table.

To setup the download for a filtered CDPOS table in Collect: 

1. Click Tables in the Navigation pane.
2. Click Vertical View for each table.
3. Add both CDHDR and CDPOS to the same Schedule Group.
   NOTE:  "SingleThread" used in this case.
4. Click the Schedule Single Thread check box for both tables.
   
   
   
   
5. Register Before and After rules on the CDHDR table
   
   
   
   
   
   
6. Create the rules in SSMS (remember to replace object names, as in replace 'P16' with your [Source] name):
   

   USE [DataGarage]
   GO

   
   SET ANSI_NULLS ON
   GO

   SET QUOTED_IDENTIFIER ON
   GO

   
   CREATE PROCEDURE [dbo].[irUpdateCDHDRWhereClause]

   AS
   BEGIN
   – SET NOCOUNT ON added to prevent extra result sets from
   – interfering with SELECT statements.
   SET NOCOUNT ON;

   – Insert statements for procedure here

   – Set how many days you want to go back
   DECLARE @NumberOfDays INT = 7

   DECLARE @FilterDate NVARCHAR(8) = CONVERT(NVARCHAR(8),(GETDATE() - @NumberOfDays),112)

   UPDATE DataGarage.dbo.dgTargetSourceTable
   SET WhereClauseOverride = 'WHERE UDATE > ''' + @FilterDate + ''''
   WHERE [Target] = 'dgSAP'
   AND [Source] = 'P16'
   AND [Table] = 'CDHDR'

   UPDATE CranPort.dbo.CPPackage
   SET SourceSQL = 'Select "MANDANT", "OBJECTCLAS", "OBJECTID", "CHANGENR", "USERNAME", "UDATE", "UTIME", "TCODE", "PLANCHNGNR", "ACT_CHNGNO", "WAS_PLANND", "CHANGE_IND", "LANGU", "VERSION" From "SAPSR3"."CDHDR" WHERE UDATE > ''' + @FilterDate + ''' and "MANDANT" = ''500'''
   WHERE PackageName = 'dgSAP.P16.CDHDR.imp'

   END

   GO

   USE [DataGarage]
   GO

   
   SET ANSI_NULLS ON
   GO

   SET QUOTED_IDENTIFIER ON
   GO

   
   CREATE PROCEDURE [dbo].[irUpdateCDPOSWhereClause]

   AS
   BEGIN
   – SET NOCOUNT ON added to prevent extra result sets from
   – interfering with SELECT statements.
   SET NOCOUNT ON;

   – Insert statements for procedure here

   DECLARE @FilterNumber NVARCHAR(20)
   SET @FilterNumber = (SELECT MIN(CHANGENR) FROM dgSAP.dbo.CDHDR)

   UPDATE DataGarage.dbo.dgTargetSourceTable
   SET WhereClauseOverride = 'CHANGENR >= ''' + @FilterNumber + ''''
   WHERE [Target] = 'dgSAP'
   AND [Source] = 'P16'
   AND [Table] = 'CDPOS'

   END

   GO

7. In SQL Server Management Studio, set the default timeout to 3600 directly in the CranPort.dbo.CPParam.Timeout column.

Consider the possibility of registering an After rule on the CDPOS download that would insert the CDPOS records into a dated table such as dgSAP.dbo.CDPOS_20160812.

If you try these steps and still have trouble downloading your clustered CDPOS table via BOA RFC, please contact our support team at https://support.syniti.com.