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