Sunday, 28 December 2014

Excepted Batch do not contains any data

Blackbaud CRM 

I made a custom batch and the good thing was that it was running fine without any errors, At one day, the client complained that when he got some exceptions in the batch at the time of commit then he was not getting any data in the excepted batch. We all know that whenever any batch committed then if that batch contains exception in any row then that row would not commit and only those rows committed which are successfully pass the validation test. Now, if you would get exception then another batch created and all those rows which contains any error then that row would jump into the excepted batch. 

So my client told me that he was not getting any data in the excepted batch, this was serious concerned for me. So tried to made my research on this issue and somehow with few tricks, i achieved this. 

First you need to create additional parameter in all the Add,Edit and Commit spec which is
"@ORIGINATINGBATCHROWGUID uniqueidentifier = null" . You would also create a formfield in all the three spec

<common:FormField FieldID="ORIGINATINGBATCHROWGUID" DataType="Guid" Hidden="true" />

Then load all the spec. You don’t need to add any business logic based on this ORIGINATINGBATCHROWGUID. Also you need to create this variable in staging table as well, please see the below mentioned line

<ComputedColumnField Description="Originating Batch Row GUID" Name="ORIGINATINGBATCHROWGUID" Expression="ID"/>

Now load all the spec, this ORIGINATINGBATCHID basically contains in OOB BATCH table, please see the below screen shot taken from SQL SERVER .

Picture (A)

If you see after adding “@ORIGINATINGBATCHROWGUID” parameter, you would see the above column ORIGINATINGBATCHID gets populated in OOB BATCH table. See the above highlighted rows . First highlighted row identified that this batch row contains exception and this ORIGINATINGBATCHID contains the below highlighted row BATCH ID. This is fine up till now.

We have staging table shown below
Picture (B)

So we need to pick that BATCH ID from PICTURE(A) which contains ORIGINATINGBATCHID not null, because this row contains the exception. So pick that BATCH ID and replace this in staging table so that whenever user will opened the excepted batch then it will open that batch which contains exception. Please see the below trigger which is the deciding point of this complex issue, with the help of this batch, I replaced the BATCH ID in staging table.
create trigger [dbo].[TR_BATCH_COMMITMENT_EXCEPTION_UPDATE] on [dbo].[BATCH] after insert not for replication
            if exists (Select 1 from INSERTED where  ORIGINATINGBATCHID is not null)
                        declare @NEWBATCHID uniqueidentifier = (select ID from INSERTED);
                        declare @ORIGINATINGBATCHID uniqueidentifier = (select ORIGINATINGBATCHID from INSERTED);
                if exists(select 1 from dbo.USR_BATCHCOMMITMENT where BATCHID = @ORIGINATINGBATCHID)
                                    declare @TempTbl table ( 
                                       [ID] uniqueidentifier) 
                                    insert into @TempTbl
                                    select BC.ID from dbo.USR_BATCHCOMMITMENT  BC
                                    where BATCHID = @ORIGINATINGBATCHID
                                    update dbo.USR_BATCHCOMMITMENT
                                    set BATCHID = (select ID from INSERTED)
                                    where ID = (select ID from @TempTbl)
                        else if  exists(select 1 from dbo.USR_BATCHCOMMITMENTPROGRESSREPORT  where BATCHID = @ORIGINATINGBATCHID)
                                    declare @TempTbl1 table ( 
                           [ID] uniqueidentifier) 
                        insert into @TempTbl1
                        select BC.ID from dbo.USR_BATCHCOMMITMENTPROGRESSREPORT BC
                        where BATCHID = @ORIGINATINGBATCHID
                        update dbo.USR_BATCHCOMMITMENTPROGRESSREPORT
                        set BATCHID = (select ID from INSERTED)
                        where ID = (select ID from @TempTbl1)

In above trigger, you would see 2 staging table highlighted in yellow color because I had this issue in 2 custom batches. So I used the checks on this trigger.

No comments:

Post a Comment