USP_BANKACCOUNT_UPDATECASHACCOUNTFORTRANSACTIONS

CREATE procedure dbo.USP_BANKACCOUNT_UPDATECASHACCOUNTFORTRANSACTIONS
(
  @BANKACCOUNTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CHANGEDATE datetime = null
)
as begin
  set nocount on;

  if @CHANGEAGENTID is null
      exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  if @CHANGEDATE is null
      set @CHANGEDATE = getdate();

  declare @GLACCOUNTID uniqueidentifier = null;
  declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier = null;
  declare @ACCOUNT nvarchar(100) = '';
  declare @ACCOUNTCODE nvarchar(30) = '';
  declare @STATUSCODE tinyint = 0;
  declare @PDACCOUNTSYSTEMID uniqueidentifier = null;



  select @GLACCOUNTID = BANKACCOUNT.GLACCOUNTID,
         @PDACCOUNTSEGMENTVALUEID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID, 
         @ACCOUNT = GLACCOUNT.ACCOUNTNUMBER,
         @ACCOUNTCODE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
         @STATUSCODE = BANKACCOUNT.STATUSCODE,
         @PDACCOUNTSYSTEMID = BANKACCOUNT.PDACCOUNTSYSTEMID
  from dbo.BANKACCOUNT
  left outer join dbo.GLACCOUNT on GLACCOUNT.ID = BANKACCOUNT.GLACCOUNTID
  left outer join PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID
  where BANKACCOUNT.ID = @BANKACCOUNTID;

  declare @NewAccounts table (ID uniqueidentifier,
                              TRANID uniqueidentifier, 
                              TRANSACTIONTYPECODE tinyint,
                              GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier, 
                              PROJECT nvarchar(100),
                              REFERENCE nvarchar(255),
                              GLACCOUNTID uniqueidentifier, 
                              ACCOUNTNUMBERS nvarchar(100), 
                              AMOUNT money,
                              JOURNAL nvarchar(255),
                              POSTDATE datetime,
                              STATUSCODE tinyint);

  --This section updates the payments (revenue records) with the new cash account.

  insert into @NewAccounts(ID, TRANSACTIONTYPECODE, TRANID, PROJECT, REFERENCE, AMOUNT, JOURNAL, POSTDATE, ACCOUNTNUMBERS, GLACCOUNTID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, STATUSCODE)
  select NEWID(), 0, R.REVENUEID, R.PROJECT, R.REFERENCE, R.AMOUNT, T.JOURNAL, T.POSTDATE,
         isnull(@ACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(T.GLACCOUNTID, @ACCOUNTCODE,@PDACCOUNTSYSTEMID)), @GLACCOUNTID,
         MAP1.ID, @STATUSCODE
  from dbo.REVENUEGLDISTRIBUTION R
  inner join dbo.GLTRANSACTION T on R.GLTRANSACTIONID = T.ID
  inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on MAP.ID = R.GLPAYMENTMETHODREVENUETYPEMAPPINGID
  inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP1 on MAP.PAYMENTMETHODCODE = MAP1.PAYMENTMETHODCODE and MAP.REVENUETRANSACTIONTYPECODE = MAP1.REVENUETRANSACTIONTYPECODE and MAP.REVENUESPLITTYPECODE = MAP1.REVENUESPLITTYPECODE and MAP.APPLICATIONCODE = MAP1.APPLICATIONCODE and MAP1.TRANSACTIONTYPECODE = 0
  inner join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = R.REVENUEID
  inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
  where BANKACCOUNTTRANSACTION.BANKACCOUNTID = @BANKACCOUNTID and R.TRANSACTIONTYPECODE = 1
    and T.POSTSTATUSCODE != 0;

  if @GLACCOUNTID is null 
   begin
    -- if it's closed allow the glaccountid to be null

    if @STATUSCODE = 1 
     begin
        update @NewAccounts set
          GLACCOUNTID = (select A.ID from GLACCOUNT A where A.ACCOUNTNUMBER = ACCOUNTNUMBERS);

        if exists(select ID from @NewAccounts where GLACCOUNTID is null)
         begin
          select TOP 1 @ACCOUNT = ACCOUNTNUMBERS from @NewAccounts where GLACCOUNTID is null;
          raiserror('The account %s does not exist. The action could not be completed.', 13, 1, @ACCOUNT);
         end
    end
    else return
   end

  --delete all the distributions that we are replacing

  delete from dbo.GLTRANSACTION
    where GLTRANSACTION.ID in
      (select R.GLTRANSACTIONID 
       from dbo.REVENUEGLDISTRIBUTION R
       inner join dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = R.REVENUEID
       inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
       where BANKACCOUNTTRANSACTION.BANKACCOUNTID = @BANKACCOUNTID and R.TRANSACTIONTYPECODE = 0)
       and GLTRANSACTION.TRANSACTIONTYPECODE = 0 and GLTRANSACTION.POSTSTATUSCODE != 0;

  --insert new distributions

  insert into dbo.GLTRANSACTION
    (ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID)
  select
    NEW.ID,
    NEW.TRANSACTIONTYPECODE,
    NEW.ACCOUNTNUMBERS,
    NEW.AMOUNT,
    NEW.PROJECT,
    new.REFERENCE,
    NEW.POSTDATE,
    NEW.JOURNAL,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE,
    NEW.GLACCOUNTID
  from @NewAccounts as NEW;

  insert into dbo.REVENUEGLDISTRIBUTION
    (ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    NEWID(),
    NEW.TRANID,
    NEW.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
    NEW.PROJECT,
    NEW.REFERENCE,
    NEW.AMOUNT,
    NEW.ACCOUNTNUMBERS,
    NEW.TRANSACTIONTYPECODE,
    NEW.ID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from @NewAccounts as NEW;

  delete from @NewAccounts;

  --Update the adjustment distributions

  insert into @NewAccounts(ID, TRANSACTIONTYPECODE, TRANID, PROJECT, REFERENCE, AMOUNT, JOURNAL, POSTDATE, ACCOUNTNUMBERS, GLACCOUNTID)
  select NEWID(), 
    CASE WHEN BT.TRANSACTIONTYPECODE in (16, 33) 
      THEN CASE WHEN BT.BANKACCOUNTID = @BANKACCOUNTID
        THEN 0
      ELSE 1 END
    WHEN BT.TRANSACTIONTYPECODE in (17, 32) 
      THEN CASE WHEN BT.BANKACCOUNTID = @BANKACCOUNTID
        THEN 1
      ELSE 0 END
    END, 
    D.BANKACCOUNTTRANSACTIONID, T.PROJECT, T.REFERENCE, T.AMOUNT, T.JOURNAL, T.POSTDATE,
    isnull(@ACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(T.GLACCOUNTID, @ACCOUNTCODE,@PDACCOUNTSYSTEMID)), @GLACCOUNTID
  from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D
  inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
  inner join dbo.BANKACCOUNTTRANSACTION BT on BT.ID = D.BANKACCOUNTTRANSACTIONID
  where ((D.TRANSACTIONTYPECODE = 1 and BT.TRANSACTIONTYPECODE in (16, 33)) or (D.TRANSACTIONTYPECODE = 0 and BT.TRANSACTIONTYPECODE in (17, 32)))
    and (BT.BANKACCOUNTID = @BANKACCOUNTID or BT.TRANSFERBANKACCOUNTID = @BANKACCOUNTID) and T.POSTSTATUSCODE != 0;

  if @GLACCOUNTID is null 
   begin
    -- if it's closed allow the glaccountid to be null

    if @STATUSCODE = 1 
     begin
        update @NewAccounts set
          GLACCOUNTID = (select A.ID from GLACCOUNT A where A.ACCOUNTNUMBER = ACCOUNTNUMBERS);

        if exists(select ID from @NewAccounts where GLACCOUNTID is null)
         begin
          select TOP 1 @ACCOUNT = ACCOUNTNUMBERS from @NewAccounts where GLACCOUNTID is null;
          raiserror('The account %s does not exist. The action could not be completed.', 13, 1, @ACCOUNT);
         end
    end        
    else return
   end

  delete from dbo.GLTRANSACTION
    where GLTRANSACTION.ID in
      (select D.GLTRANSACTIONID 
       from dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION D
       inner join dbo.BANKACCOUNTTRANSACTION BT on BT.ID = D.BANKACCOUNTTRANSACTIONID
       where (BT.BANKACCOUNTID = @BANKACCOUNTID
            and ((D.TRANSACTIONTYPECODE = 0 and BT.TRANSACTIONTYPECODE in (16, 33)) 
              or (D.TRANSACTIONTYPECODE = 1 and BT.TRANSACTIONTYPECODE in (17, 32))))
         or (BT.TRANSFERBANKACCOUNTID = @BANKACCOUNTID
            and ((D.TRANSACTIONTYPECODE = 1 and BT.TRANSACTIONTYPECODE in (16, 33)) 
              or (D.TRANSACTIONTYPECODE = 0 and BT.TRANSACTIONTYPECODE in (17, 32)))))
      and GLTRANSACTION.POSTSTATUSCODE != 0;

  --insert new distributions

  insert into dbo.GLTRANSACTION
    (ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID)
  select
    NEW.ID,
    NEW.TRANSACTIONTYPECODE,
    NEW.ACCOUNTNUMBERS,
    NEW.AMOUNT,
    NEW.PROJECT,
    NEW.REFERENCE,
    NEW.POSTDATE,
    NEW.JOURNAL,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE,
    NEW.GLACCOUNTID
  from @NewAccounts as NEW;

  insert into dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION
    (ID, BANKACCOUNTTRANSACTIONID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    NEWID(),
    NEW.TRANID,
    NEW.PROJECT,
    NEW.REFERENCE,
    NEW.AMOUNT,
    NEW.ACCOUNTNUMBERS,
    NEW.TRANSACTIONTYPECODE,
    NEW.ID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from @NewAccounts as NEW;

  delete from @NewAccounts;

  --Update Deposit Correction distributions

  insert into @NewAccounts(ID, TRANSACTIONTYPECODE, TRANID, PROJECT, REFERENCE, AMOUNT, JOURNAL, POSTDATE, ACCOUNTNUMBERS, GLACCOUNTID)
    select NEWID(), 
    CASE WHEN C.CORRECTIONTYPECODE in (0, 2, 4) 
      THEN 1
    WHEN C.CORRECTIONTYPECODE in (1, 3) 
      THEN 0
    END,
    D.BANKACCOUNTDEPOSITCORRECTIONID, T.PROJECT, T.REFERENCE, T.AMOUNT, T.JOURNAL, T.POSTDATE,
    isnull(@ACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(T.GLACCOUNTID, @ACCOUNTCODE, @PDACCOUNTSYSTEMID)), @GLACCOUNTID
  from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION D
  inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
  inner join dbo.BANKACCOUNTDEPOSITCORRECTION C on C.ID = D.BANKACCOUNTDEPOSITCORRECTIONID
  inner join dbo.BANKACCOUNTTRANSACTION BT on BT.ID = C.DEPOSITID
  where ((D.TRANSACTIONTYPECODE = 0 and C.CORRECTIONTYPECODE in (0, 2, 4)) or (D.TRANSACTIONTYPECODE = 1 and C.CORRECTIONTYPECODE in (1, 3)))
    and BT.BANKACCOUNTID = @BANKACCOUNTID and T.POSTSTATUSCODE != 0;

 if @GLACCOUNTID is null 
   begin
    -- if it's closed allow the glaccountid to be null

    if @STATUSCODE = 1 
     begin
        update @NewAccounts set
          GLACCOUNTID = (select A.ID from GLACCOUNT A where A.ACCOUNTNUMBER = ACCOUNTNUMBERS);

        if exists(select ID from @NewAccounts where GLACCOUNTID is null)
         begin
          select TOP 1 @ACCOUNT = ACCOUNTNUMBERS from @NewAccounts where GLACCOUNTID is null;
          raiserror('The account %s does not exist. The action could not be completed.', 13, 1, @ACCOUNT);
         end
    end
    else return
   end

  delete from dbo.GLTRANSACTION
    where GLTRANSACTION.ID in
      (select D.GLTRANSACTIONID 
       from dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION D
       inner join dbo.BANKACCOUNTDEPOSITCORRECTION C on C.ID = D.BANKACCOUNTDEPOSITCORRECTIONID
       inner join dbo.BANKACCOUNTTRANSACTION BT on BT.ID = C.DEPOSITID
       where BT.BANKACCOUNTID = @BANKACCOUNTID
            and ((D.TRANSACTIONTYPECODE = 1 and C.CORRECTIONTYPECODE in (0, 2, 4)) 
              or (D.TRANSACTIONTYPECODE = 0 and C.CORRECTIONTYPECODE in (1, 3))))
      and GLTRANSACTION.POSTSTATUSCODE != 0;

  --insert new distributions

  insert into dbo.GLTRANSACTION
    (ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID)
  select
    NEW.ID,
    NEW.TRANSACTIONTYPECODE,
    NEW.ACCOUNTNUMBERS,
    NEW.AMOUNT,
    NEW.PROJECT,
    NEW.REFERENCE,
    NEW.POSTDATE,
    NEW.JOURNAL,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE,
    NEW.GLACCOUNTID
  from @NewAccounts as NEW;

  insert into dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION
    (ID, BANKACCOUNTDEPOSITCORRECTIONID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select
    NEWID(),
    NEW.TRANID,
    NEW.PROJECT,
    NEW.REFERENCE,
    NEW.AMOUNT,
    NEW.ACCOUNTNUMBERS,
    NEW.TRANSACTIONTYPECODE,
    NEW.ID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from @NewAccounts as NEW;

  delete from @NewAccounts;
end