USP_DEPOSIT_CREATEDISTRIBUTIONSFORALL_BANKACCOUNTDEPOSITCORRECTIONS

CREATE procedure dbo.USP_DEPOSIT_CREATEDISTRIBUTIONSFORALL_BANKACCOUNTDEPOSITCORRECTIONS
(
  @DEPOSITID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@CURRENTDATE datetime = null
)
as
begin
    declare @JOURNAL nvarchar(50) = 'Blackbaud Enterprise';
    declare @REFERENCE nvarchar(100) = 'Bank Account Deposit Correction';
    declare @DEPOSITREFERENCE nvarchar(100) = 'Bank Account Deposit Correction';
    declare @NUMBEROFCORRECTIONS integer;

    select @NUMBEROFCORRECTIONS = COUNT(ID)
    from dbo.BANKACCOUNTDEPOSITCORRECTION
    where DEPOSITID = @DEPOSITID;

    if @NUMBEROFCORRECTIONS > 0
    begin
        if @CHANGEAGENTID is null
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

        declare @DISTRIBUTION table(
            CORRECTIONID uniqueidentifier
            ,GLTRANSACTIONID uniqueidentifier
            ,ACCOUNT nvarchar(100)
            ,GLACCOUNTID uniqueidentifier
            ,AMOUNT money
            ,PROJECT nvarchar(100)
            ,REFERENCE nvarchar(255)
            ,TRANSACTIONTYPECODE tinyint
            ,TRANSACTIONAMOUNT money
            ,ORGANIZATIONAMOUNT money
            ,TRANSACTIONCURRENCYID uniqueidentifier
            ,BASECURRENCYID uniqueidentifier
            ,BASEEXCHANGERATEID uniqueidentifier
            ,ORGANIZATIONEXCHANGERATEID uniqueidentifier);

        declare @BANKGLACCOUNTID uniqueidentifier;
        declare @BANKGLACCOUNT nvarchar(100);
        declare @BANKPDACCOUNTSEGMENTVALUEID uniqueidentifier;
        declare @TRANSACTIONCURRENCYID uniqueidentifier;
        declare @BASECURRENCYID uniqueidentifier;
        declare @BASEEXCHANGERATEID uniqueidentifier;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
        declare @PDACCOUNTSYSTEMID uniqueidentifier;
        declare @POSTSTATUSCODE tinyint;
        declare @POSTDATE datetime;

        SELECT @BANKGLACCOUNT = A.ACCOUNTNUMBER
            ,@BANKGLACCOUNTID = BA.GLACCOUNTID
            ,@BANKPDACCOUNTSEGMENTVALUEID = BA.PDACCOUNTSEGMENTVALUEID
            ,@TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
            ,@PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
            ,@BASECURRENCYID = BAT.BASECURRENCYID
            ,@BASEEXCHANGERATEID = BAT.BASEEXCHANGERATEID
            ,@ORGANIZATIONEXCHANGERATEID = BAT.ORGANIZATIONEXCHANGERATEID
            ,@POSTSTATUSCODE = BAT.POSTSTATUSCODE
            ,@POSTDATE = BAT.POSTDATE
        FROM dbo.BANKACCOUNTTRANSACTION BAT
        inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
        left outer join dbo.GLACCOUNT A on A.ID = BA.GLACCOUNTID
        where BAT.ID = @DEPOSITID;

        if @BANKGLACCOUNTID is null
        begin
            select @BANKGLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@BANKPDACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
            select @BANKGLACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @BANKGLACCOUNT and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;
            if @BANKGLACCOUNTID is null
                raiserror('The account %s does not exist. The action could not be completed.', 13, 1, @BANKGLACCOUNT);
        end

        declare @ACCOUNTS table (
            CORRECTIONID uniqueidentifier
            ,GLACCOUNTID uniqueidentifier
            ,PDACCOUNTSEGMENTVALUEID uniqueidentifier
            ,GLACCOUNT nvarchar(100));

        insert into @ACCOUNTS
        select C.ID, M.CREDITGLACCOUNTID, M.CREDITPDACCOUNTSEGMENTVALUEID, A.ACCOUNTNUMBER
        from dbo.PDACCOUNTCODEMAPPING M
        inner join dbo.BANKACCOUNTDEPOSITCORRECTION C on C.DEPOSITID = @DEPOSITID
        left outer join dbo.GLACCOUNT A on A.ID = M.CREDITGLACCOUNTID
        where M.OFFICEID = 10
            and M.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
            and (M.PAYMENTMETHOD & CASE C.CORRECTIONTYPECODE WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 4 WHEN 3 THEN 8 END) > 0
            and (M.REVENUETYPE & CASE C.PAYMENTMETHODCODE WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 4 WHEN 3 THEN 8 END) > 0;

        if (select COUNT(*) from @ACCOUNTS) < @NUMBEROFCORRECTIONS
            raiserror('An account code has not been defined for a mapping of one or more of the deposit corrections', 13, 1)

        update @ACCOUNTS
            set GLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(PDACCOUNTSEGMENTVALUEID, @PDACCOUNTSYSTEMID)
        where GLACCOUNTID is null;

        update @ACCOUNTS
            set GLACCOUNTID = GLA.ID
        from @ACCOUNTS A
        inner join dbo.GLACCOUNT GLA on GLA.ACCOUNTNUMBER = A.GLACCOUNT and GLA.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
        where A.GLACCOUNTID is null

        if exists(select * from @ACCOUNTS where GLACCOUNTID is null)
            raiserror('An expected account does not exist. The action could not be completed.', 13, 1)

        insert into @DISTRIBUTION (
            CORRECTIONID
            ,GLTRANSACTIONID
            ,ACCOUNT
            ,GLACCOUNTID
            ,AMOUNT
            ,PROJECT
            ,TRANSACTIONTYPECODE
            ,REFERENCE
            ,TRANSACTIONAMOUNT
            ,ORGANIZATIONAMOUNT 
            ,TRANSACTIONCURRENCYID 
            ,BASECURRENCYID 
            ,BASEEXCHANGERATEID
            ,ORGANIZATIONEXCHANGERATEID)
        select
            C.ID 
            ,NEWID()
            ,@BANKGLACCOUNT
            ,@BANKGLACCOUNTID
            ,C.AMOUNT
            ,' '
            ,CASE WHEN C.CORRECTIONTYPECODE in (0, 2) THEN 1 ELSE 0 END
            ,CASE WHEN Len(T.REFERENCE) > 0 THEN T.REFERENCE ELSE CASE WHEN Len(C.REFERENCE) > 0 THEN C.REFERENCE ELSE @REFERENCE END END
            ,C.TRANSACTIONAMOUNT
            ,C.ORGANIZATIONAMOUNT
            ,BA.TRANSACTIONCURRENCYID
            ,T.BASECURRENCYID
            ,T.BASEEXCHANGERATEID
            ,T.ORGANIZATIONEXCHANGERATEID
        from dbo.BANKACCOUNTDEPOSITCORRECTION C
        inner join dbo.BANKACCOUNTTRANSACTION T on C.DEPOSITID = T.ID
        inner join dbo.BANKACCOUNT BA on BA.ID = T.BANKACCOUNTID
        where C.DEPOSITID = @DEPOSITID

        insert into @DISTRIBUTION (
            CORRECTIONID 
            ,GLTRANSACTIONID
            ,ACCOUNT
            ,GLACCOUNTID
            ,AMOUNT
            ,PROJECT
            ,TRANSACTIONTYPECODE
            ,REFERENCE
            ,TRANSACTIONAMOUNT
            ,ORGANIZATIONAMOUNT 
            ,TRANSACTIONCURRENCYID 
            ,BASECURRENCYID 
            ,BASEEXCHANGERATEID 
            ,ORGANIZATIONEXCHANGERATEID)
        select
            C.ID 
            ,NEWID()
            ,A.GLACCOUNT
            ,A.GLACCOUNTID
            ,C.AMOUNT
            ,' '
            ,CASE WHEN C.CORRECTIONTYPECODE in (0, 2) THEN 0 ELSE 1 END
            ,CASE WHEN Len(C.REFERENCE) > 0 THEN C.REFERENCE ELSE @REFERENCE END
            ,C.TRANSACTIONAMOUNT
            ,C.ORGANIZATIONAMOUNT
            ,@TRANSACTIONCURRENCYID
            ,@BASECURRENCYID
            ,@BASEEXCHANGERATEID
            ,@ORGANIZATIONEXCHANGERATEID
        from dbo.BANKACCOUNTDEPOSITCORRECTION C
        inner join @ACCOUNTS A on A.CORRECTIONID = C.ID
        where C.DEPOSITID = @DEPOSITID

        insert into dbo.GLTRANSACTION(
            ID
            ,TRANSACTIONTYPECODE
            ,ACCOUNT
            ,AMOUNT
            ,PROJECT
            ,REFERENCE
            ,POSTSTATUSCODE
            ,POSTDATE
            ,JOURNAL
            ,GLACCOUNTID
            ,TRANSACTIONAMOUNT
            ,ORGANIZATIONAMOUNT 
            ,TRANSACTIONCURRENCYID 
            ,BASECURRENCYID 
            ,BASEEXCHANGERATEID 
            ,ORGANIZATIONEXCHANGERATEID
            ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select
            GLTRANSACTIONID
            ,TRANSACTIONTYPECODE
            ,ACCOUNT
            ,AMOUNT
            ,PROJECT
            ,REFERENCE
            ,@POSTSTATUSCODE
            ,@POSTDATE
            ,@JOURNAL
            ,GLACCOUNTID
            ,TRANSACTIONAMOUNT
            ,ORGANIZATIONAMOUNT 
            ,TRANSACTIONCURRENCYID 
            ,BASECURRENCYID 
            ,BASEEXCHANGERATEID 
            ,ORGANIZATIONEXCHANGERATEID
            ,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
            from @DISTRIBUTION;

        insert into dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION(
            ID
            ,PROJECT
            ,REFERENCE
            ,AMOUNT
            ,ACCOUNT
            ,TRANSACTIONTYPECODE
            ,GLTRANSACTIONID
            ,BANKACCOUNTDEPOSITCORRECTIONID
            ,TRANSACTIONAMOUNT
            ,ORGANIZATIONAMOUNT 
            ,TRANSACTIONCURRENCYID 
            ,BASECURRENCYID 
            ,BASEEXCHANGERATEID 
            ,ORGANIZATIONEXCHANGERATEID
            , ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select
            NEWID()
            ,PROJECT
            ,REFERENCE
            ,AMOUNT
            ,ACCOUNT
            ,TRANSACTIONTYPECODE
            ,GLTRANSACTIONID
            ,CORRECTIONID
            ,TRANSACTIONAMOUNT
            ,ORGANIZATIONAMOUNT 
            ,TRANSACTIONCURRENCYID 
            ,BASECURRENCYID 
            ,BASEEXCHANGERATEID 
            ,ORGANIZATIONEXCHANGERATEID
            ,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
        from @DISTRIBUTION;
    end
end