SET ansi_warnings OFF DECLARE @DATE VARCHAR(255) SET @DATE = (select MAX(FileDate) from [SABC_LastRun]) --DECLARE @Lday VARCHAR(255) --DECLARE @Dday VARCHAR(255) --DECLARE @Lmnth VARCHAR(255) --DECLARE @month VARCHAR(255) --DECLARE @day VARCHAR(255) --DECLARE @curdate datetime; --DECLARE @year VARCHAR(255) --DECLARE @LENT VARCHAR(255) --DECLARE @MLENT VARCHAR(255) --set @year = (SELECT YEAR(@curdate)) --set @month = (SELECT MONTH(GETDATE()-1)); --set @day = (SELECT MONTH(GETDATE())); --SET @curdate = GETDATE() --SET @Lday =(SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@curdate),1)); --SET @Lday = (SELECT DAY(@curdate)) --set @month = (SELECT MONTH(@curdate)); --SET @Dday =(SELECT --REPLICATE('0', 1) + @Lday) ----Day end --SET @Dday = (SELECT DAY(@curdate)) --if(@Dday != 01 ) --BEGIN --set @year = (SELECT YEAR(@curdate)) --SET @Dday = (SELECT DAY(@curdate)-1) --set @month = (SELECT MONTH(@curdate)) --set @year = (SELECT YEAR(@curdate)) --SET @LENT =(SELECT LEN(@Dday)) --SET @MLENT =(SELECT LEN(@month)) --IF(@LENT=1) --SET @Dday =(SELECT --REPLICATE('0', 2-(@Dday)) + @Dday) --IF(@MLENT=1) --SET @month =(SELECT --REPLICATE('0', 2-LEN(@month)) + @month) --ELSE --SET @DATE= @year+'-'+@month+'-'+@Dday --END ----Month end --if(@Lday = 01 AND @month !=01 ) --BEGIN --set @year = (SELECT YEAR(@curdate)) --SET @Lday =(SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),1)); --SET @Lday = (SELECT DAY(@curdate)) --set @month = (SELECT MONTH(@curdate)-1); --SET @LENT =(SELECT LEN(@Dday)) --IF(@LENT=1) --SET @Lday =(SELECT --REPLICATE('0', 1) + @Lday) --IF(@MLENT=1) --SET @month =(SELECT --REPLICATE('0', 1) + @month) --ELSE --SET @Lday = (SELECT DAY(@curdate)) --SELECT @date = @year+'-'+@month+'-'+@Lday --SET @date =(SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))) --SET @Lday = (SELECT DAY(@date)) --SET @DATE = @year+'-'+@month+'-'+@Lday --END ----Year end --DECLARE @CYEAR VARCHAR(255) --DECLARE @LYEAR VARCHAR(255) --DECLARE @FYEAR VARCHAR(255) --DECLARE @FDATE VARCHAR(255) --DECLARE @YDATE VARCHAR(255) --SET @LYEAR = (SELECT YEAR(GETDATE())) --SET @CYEAR = (SELECT YEAR(@curdate)) --SET @YDATE = @CYEAR+'-'+@month+'-'+@Lday --if(@YDATE = @LYEAR+'-'+'1'+'-'+'1') --BEGIN --SET @CYEAR = (SELECT YEAR(@curdate)-1); --set @month = 12 ; --SET @DATE = @CYEAR+'-'+'12'+'-'+'31'; --END update [dbo].[A_TEST_ZSRS_POS_FILE_DS_JOHANNES] set ValidateReg = case when rtrim(ltrim(isnull(TVLIC_NUM,'0'))) = '0' then 'TVLIC_NUM' when rtrim(ltrim(isnull(DEAL_LICNUM,'0'))) = '0' then 'DEAL_LICNUM' when rtrim(ltrim(isnull(ID_NUMBER,''))) = '' then 'ID_NUMBER' when rtrim(ltrim(isnull(PUR_DATE,'1900-01-01'))) <= '1900-01-01' then 'PUR_DATE' when rtrim(ltrim(isnull(TV_QTY,'0'))) = '0' then 'TV_QTY' --when rtrim(ltrim(isnull(EASYPAYNUM,'0'))) = '0' then 'EASYPAYNUM' when rtrim(ltrim(isnull(REF_NUM,''))) = '' then 'REF_NUM' --when TVLIC_TYPE in ('3','5') --and rtrim(ltrim(isnull(PAY_RCTNO,'0'))) = '0' -- then 'PAY_RCTNO' when TVLIC_TYPE in ('4','5') and (rtrim(ltrim(isnull(DOC_NUM,''))) = '' and ltrim(rtrim(isnull(PAY_RCTNO,''))) = '' ) then 'DOC_NUM' when (TVLIC_TYPE in ('4','5') or (TVLIC_TYPE = '3'))-- and PRODUCTCODE in ('07','40'))) and rtrim(ltrim(isnull(TITLE,''))) = '' and rtrim(ltrim(isnull(INITIALS, ''))) = '' and rtrim(ltrim(isnull(CUST_NAME, ''))) = '' then 'Title, Initials and Name' when ( rtrim(ltrim(isnull(ADD_BLDG,''))) = '' and rtrim(ltrim(isnull(ADD_BLDGNM,''))) = '' and rtrim(ltrim(isnull(ADD_STRTNAM,''))) = '' and rtrim(ltrim(isnull(ADD_STRTNUM,''))) = '' and rtrim(ltrim(isnull(ADD_CITY,''))) = '' and rtrim(ltrim(isnull(ADD_RPCODE,''))) = '' ) and ( rtrim(ltrim(isnull(ADD_POST1,''))) = '' and rtrim(ltrim(isnull(ADD_POST2,''))) = '' and rtrim(ltrim(isnull(ADD_POST3,''))) = '' and rtrim(ltrim(isnull(ADD_POST4,''))) = '' and rtrim(ltrim(isnull(ADD_PPCODE,''))) = '' ) then 'Addresses' else 'Success' end where isnull([REGProcessed],0) = 0 -- Only process records previously not processed and isnull(Validatereg,'') <> 'Success' -- that validated unsuccessfully on business rules and ZPAY_DATE >= @DATE
... View more