UFN_PLANNEDGIFTHISTORY_PLANNEDGIFTCHILDTABLES_AUDIT

CREATE function dbo.UFN_PLANNEDGIFTHISTORY_PLANNEDGIFTCHILDTABLES_AUDIT
(
  @PLANNEDGIFTID uniqueidentifier,
  @PLANNEDGIFTDATEADDED datetime,
  @ACTIONTYPECODE tinyint
)
  returns @RESULTS table
  (
    RECORDID uniqueidentifier,
    AUDITDATE datetime,
    CHANGEDBYUSER nvarchar(128),
    PROCESS nvarchar(255),
    FIELD nvarchar(128),
    OLD nvarchar(4000),
    NEW nvarchar(4000),
    SEQUENCE int,
    [ACTION] nvarchar(8),
    CHANGEDBYAPP nvarchar(200),
    GROUPONVALUE uniqueidentifier
  )
as
begin

  with CTE_REVENUEPAYMENT as 
  (
    select 
      FTLI.ID as FINANCIALTRANSACTIONLINEITEMID,
      'Payment ' + convert(nvarchar(max), row_number() over(partition by PLANNEDGIFTREVENUE.ID order by FTLI.DATEADDED)) as PAYMENTSTRING,
      FTLI.FINANCIALTRANSACTIONID
    from
      dbo.PLANNEDGIFTREVENUE
    inner join
      dbo.INSTALLMENTPAYMENT on INSTALLMENTPAYMENT.PLEDGEID = PLANNEDGIFTREVENUE.REVENUEID
    inner join
      dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = INSTALLMENTPAYMENT.PAYMENTID
    where
      PLANNEDGIFTREVENUE.ID = @PLANNEDGIFTID and
      FTLI.DELETEDON is null
  )
  insert into
    @RESULTS
  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    30 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('DESIGNATIONID, AMOUNT', 'PLANNEDGIFTDESIGNATION', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Designation', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    40 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('PLANNEDGIFTLETTERCODEID, ACKNOWLEDGEDATE, PROCESSDATE, PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID', 'PLANNEDGIFTLETTER', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Letter', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    50 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('ASSETTYPECODEID, DESCRIPTION, VALUE, COSTBASIS, ASSETVALUATIONMETHODCODEID, VALUATIONSOURCE, ADDRESSID', 'PLANNEDGIFTASSET', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Asset', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    60 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('BENEFICIARYCONSTITUENTID, BENEFICIARYTYPECODEID, PRIORITYCODE', 'PLANNEDGIFTBENEFICIARY', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Beneficiary', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,  CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    60 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null  as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('RELATIONSHIPID', 'PLANNEDGIFTRELATIONSHIP', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Relationship', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    60 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('REVENUESPLITID', 'PLANNEDGIFTREVENUESPLIT', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Payment revenue', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    60 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    RECORDID as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('REVENUEID', 'PLANNEDGIFTREVENUE', 'ID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Revenue', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    70 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null  as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('SITEID', 'PLANNEDGIFTSITE', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Site', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    80 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null  as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('AUTHORID,PLANNEDGIFTNOTETYPECODEID,DATEENTERED,TEXTNOTE,TITLE', 'PLANNEDGIFTNOTE', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Note', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    90 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null  as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('AUTHORID,PLANNEDGIFTMEDIALINKTYPECODEID,DATEENTERED,MEDIAURL,TITLE', 'PLANNEDGIFTMEDIALINK', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Media link', '')

  union all

  select
    RECORDID,
    case
      when [ACTION] = 'Insert'
        then DATEADDED
      when [ACTION] = 'Update'
        then DATECHANGED
      else
        AUDITDATE
    end as AUDITDATE,
    CHANGEDBYUSER,
    PROCESS,
    FIELD,
    OLD,
    NEW,
    100 as SEQUENCE,
    [ACTION],
    CHANGEDBYAPP,
    null as GROUPONVALUE
  from
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('AUTHORID,PLANNEDGIFTATTACHMENTTYPECODEID,DATEENTERED,FILENAME,TITLE', 'PLANNEDGIFTATTACHMENT', 'PLANNEDGIFTID', @PLANNEDGIFTID, @PLANNEDGIFTDATEADDED, 'Attachment', '')

  union all

  select
    PAYMENTS.RECORDID,
    case
      when PAYMENTS.[ACTION] = 'Insert'
        then PAYMENTS.DATEADDED
      when PAYMENTS.[ACTION] = 'Update'
        then PAYMENTS.DATECHANGED
      else
        PAYMENTS.AUDITDATE
    end as AUDITDATE,
    PAYMENTS.CHANGEDBYUSER,
    PAYMENTS.PROCESS,
    case
      --Compatibility with old REVENUE table column name

      when PAYMENTS.FIELD like '%BASEAMOUNT'
        then replace(PAYMENTS.FIELD, 'BASEAMOUNT', 'AMOUNT')
      else
        PAYMENTS.FIELD
    end as FIELD,
    PAYMENTS.OLD,
    PAYMENTS.NEW,
    110 as SEQUENCE,
    PAYMENTS.[ACTION],
    PAYMENTS.CHANGEDBYAPP,
    CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID as GROUPONVALUE
  from
    CTE_REVENUEPAYMENT
  cross apply
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('BASEAMOUNT', 'FINANCIALTRANSACTIONLINEITEM', 'ID', CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONLINEITEMID, @PLANNEDGIFTDATEADDED, CTE_REVENUEPAYMENT.PAYMENTSTRING, '') PAYMENTS
  where
    PAYMENTS.RECORDID = CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONLINEITEMID

  union all

  select
    PAYMENTS.RECORDID,
    case
      when PAYMENTS.[ACTION] = 'Insert'
        then PAYMENTS.DATEADDED
      when PAYMENTS.[ACTION] = 'Update'
        then PAYMENTS.DATECHANGED
      else
        PAYMENTS.AUDITDATE
    end as AUDITDATE,
    PAYMENTS.CHANGEDBYUSER,
    PAYMENTS.PROCESS,
    PAYMENTS.FIELD,
    PAYMENTS.OLD,
    PAYMENTS.NEW,
    111 as SEQUENCE,
    PAYMENTS.[ACTION],
    PAYMENTS.CHANGEDBYAPP,
    CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID as GROUPONVALUE
  from
    CTE_REVENUEPAYMENT
  cross apply
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CONSTITUENTID', 'FINANCIALTRANSACTION', 'ID', CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID, @PLANNEDGIFTDATEADDED, CTE_REVENUEPAYMENT.PAYMENTSTRING, '') PAYMENTS
  where
    PAYMENTS.RECORDID = CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID

  union all

  select
    PAYMENTS.RECORDID,
    case
      when PAYMENTS.[ACTION] = 'Insert'
        then PAYMENTS.DATEADDED
      when PAYMENTS.[ACTION] = 'Update'
        then PAYMENTS.DATECHANGED
      else
        PAYMENTS.AUDITDATE
    end as AUDITDATE,
    PAYMENTS.CHANGEDBYUSER,
    PAYMENTS.PROCESS,
    PAYMENTS.FIELD,
    PAYMENTS.OLD,
    PAYMENTS.NEW,
    112 as SEQUENCE,
    PAYMENTS.[ACTION],
    PAYMENTS.CHANGEDBYAPP,
    CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID as GROUPONVALUE
  from
    CTE_REVENUEPAYMENT
  inner join
    dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID
  cross apply
    dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('PAYMENTMETHODCODE', 'REVENUEPAYMENTMETHOD', 'REVENUEID', CTE_REVENUEPAYMENT.FINANCIALTRANSACTIONID, @PLANNEDGIFTDATEADDED, CTE_REVENUEPAYMENT.PAYMENTSTRING, '') PAYMENTS
  where
    PAYMENTS.RECORDID = REVENUEPAYMENTMETHOD.ID

  declare @ASSETID uniqueidentifier;
  declare @ASSETDESCRIPTION nvarchar(128);

  declare ASSETCURSOR cursor local fast_forward for
  select
    ID,
    [DESCRIPTION]
  from
    dbo.PLANNEDGIFTASSET
  where
    PLANNEDGIFTID = @PLANNEDGIFTID;

  open ASSETCURSOR;
  fetch next from ASSETCURSOR into @ASSETID, @ASSETDESCRIPTION;

  while @@FETCH_STATUS = 0
  begin
    insert into
      @RESULTS
    select 
      RECORDID,
      case
        when [ACTION] = 'Insert'
          then DATEADDED
        when [ACTION] = 'Update'
          then DATECHANGED
        else
          AUDITDATE
      end as AUDITDATE,
      CHANGEDBYUSER,
      PROCESS,
      FIELD,
      OLD,
      NEW,
      51 as SEQUENCE,
      [ACTION],
      CHANGEDBYAPP,
      null as GROUPONVALUE
    from
      dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('HOLDERCONSTITUENTID', 'PLANNEDGIFTASSETHOLDER', 'PLANNEDGIFTASSETID', @ASSETID, @PLANNEDGIFTDATEADDED, 'Asset holder (' + @ASSETDESCRIPTION + ')', '');

    fetch next from ASSETCURSOR into @ASSETID, @ASSETDESCRIPTION;
  end

  close ASSETCURSOR;
  deallocate ASSETCURSOR;

  declare @DESIGNATIONID uniqueidentifier;

  declare DESIGNATIONCURSOR cursor local fast_forward for
  select
    ID
  from
    dbo.PLANNEDGIFTDESIGNATION
  where
    PLANNEDGIFTID = @PLANNEDGIFTID;

  open DESIGNATIONCURSOR;
  fetch next from DESIGNATIONCURSOR into @DESIGNATIONID;

  while @@FETCH_STATUS = 0
  begin
    insert into
      @RESULTS
    select 
      RECORDID,
      case
   when [ACTION] = 'Insert'
          then DATEADDED
        when [ACTION] = 'Update'
          then DATECHANGED
        else
          AUDITDATE
      end as AUDITDATE,
      CHANGEDBYUSER,
      PROCESS,
      FIELD,
      OLD,
      NEW,
      70 as SEQUENCE,
      [ACTION],
      CHANGEDBYAPP,
      null as GROUPONVALUE
    from
      dbo.UFN_AUDIT_GETDETAILFORCHILDTABLE_BYFIELDLIST('CAMPAIGNID, CAMPAIGNSUBPRIORITYID', 'PLANNEDGIFTDESIGNATIONCAMPAIGN', 'PLANNEDGIFTDESIGNATIONID', @DESIGNATIONID, @PLANNEDGIFTDATEADDED, 'Campaign', '');

    fetch next from DESIGNATIONCURSOR into @DESIGNATIONID;
  end

  close DESIGNATIONCURSOR;
  deallocate DESIGNATIONCURSOR;

  if @ACTIONTYPECODE = 1
    delete from @RESULTS where [ACTION] = 'Delete' or [ACTION] = 'Update';

  if @ACTIONTYPECODE = 2
    delete from @RESULTS where [ACTION] = 'Insert' or [ACTION] = 'Delete';

  if @ACTIONTYPECODE = 3
    delete from @RESULTS where [ACTION] = 'Insert' or [ACTION] = 'Update';

  return;
end