Friday, 3 July 2015

Custom PTGL process

Blackbaud CRM 
I had a scenario from one my client that in custom Post to GL process, they want those rows not included in the csv file which contains Zero amount. This can be easily achievable if we have custom class running behind PTGL process which would be calling via Javascript file. But my client only had custom Query view and nothing else and they downloaded their Post to GL file using OOB download file. So that was the problem which i was facing. So, i ran the SQL pro-filer and found one OOB SQL function which solved my issue. That function is called "UFN_POSTTOGLPROCESS_CUSTOMIZE". The original body of this function is

CREATE function [dbo].[UFN_POSTTOGLPROCESS_CUSTOMIZE]   
    (@InRecords UDT_GENERICIDANDBIT readonly, @ID uniqueidentifier)   
    returns @WORKTABLE table   
     (   
     GLTRANSACTIONID uniqueidentifier   
     )      
    with execute as caller   
    as   
    begin   
    /* Products - DO NOT EDIT THIS FUNCTION!  It is only for users to customize their post process*/   
    /* This is a default that just returns the input records */   
       
    insert into @WORKTABLE (GLTRANSACTIONID)   
    select ID from @InRecords   
   
    return   
    end    

So if you analyze above SQL function which is OOB, it only selects all the transaction and return those via table. So I picked that SQL function and alter this SQL function and written my custom logic

ALTER  function [dbo].[UFN_POSTTOGLPROCESS_CUSTOMIZE] 
    (@InRecords UDT_GENERICIDANDBIT readonly, @ID uniqueidentifier) 
    returns @WORKTABLE table 
     ( 
     GLTRANSACTIONID uniqueidentifier 
     )    
    with execute as caller 
    as 
    begin 
    /* Products - DO NOT EDIT THIS FUNCTION!  It is only for users to customize their post process*/ 
    /* This is a default that just returns the input records */ 
   
                insert into @WORKTABLE (GLTRANSACTIONID) 
             select ID from @InRecords  R
                where R.ID in (select ID from GLTRANSACTION where ID = R.ID and AMOUNT <> 0)
                 
 
    return 
    end  


I had filtered only those rows in which amount is not equal to 0 that discarded those rows which contains Zero amount. Because we don’t have any control only if we make a query view, other solution would offcourse a custom class library and filtered all those rows which contains amount equal to zero

1 comment:

  1. Nowadays, most of the businesses rely on cloud based CRM tool to power their business process. They want to access the business from anywhere and anytime. In such scenarios, salesforce CRM will ensure massive advantage to the business owners. Salesforce Training | Salesforce Training in Chennai|Cloud computing course in Chennai

    ReplyDelete