USP_DISBURSEMENTPROCESS_REMOVEHOLD_UPDATE

CREATE procedure dbo.USP_DISBURSEMENTPROCESS_REMOVEHOLD_UPDATE
(
    @ID nvarchar(72)
    ,@CHANGEAGENTID uniqueidentifier
)
as begin
    -- Note - this SP takes a string that is two GUIDS connected together, in the order of 

    -- a Disbursement Process ID followed by a Financial Transaction Schedule ID.

    declare @FTSID uniqueidentifier;
    declare @DPID uniqueidentifier;
    declare @DPDID uniqueidentifier;
    declare @INVOICEID uniqueidentifier;
    declare @HELD tinyint = 0;
    declare @CURRENTDATE datetime;

    -- DP settings

    declare @DISBURSEMENTDATE datetime;
    declare @POSTDATE datetime;
    declare @POSTSTATUSCODE tinyint;
    declare @DISCOUNTASOFDATE datetime;
  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @PDACCOUNTSYSTEMID uniqueidentifier;

    -- Temp table for transaction info

    declare @TRANSACTIONS as TABLE ( 
        FTSID uniqueidentifier
        ,[AMOUNT] money 
        ,[FULLAMOUNT] money
        ,[TYPECODE] tinyint
        ,[STATUSCODE] tinyint
        ,[DISCOUNTDATE] datetime
    );

    set @CURRENTDATE = getdate();
    set @DPID = CONVERT(uniqueidentifier,LEFT(@ID, 36));
    set @FTSID = CONVERT(uniqueidentifier,RIGHT(@ID, 36));

    -- validate parameters

    if not exists(select * from dbo.DISBURSEMENTPROCESS where ID = @DPID)
    begin
        exec dbo.USP_RAISE_ERROR;
        return 3;
    end

    if not exists(select * from dbo.FINANCIALTRANSACTIONSCHEDULE where ID = @FTSID and DELETED = 0)
    begin
        exec dbo.USP_RAISE_ERROR;
        return 4;
    end

    -- get DP settings for future use

    select
        @DISBURSEMENTDATE = DP.DISBURSEMENTDATE
        ,@POSTDATE = CASE WHEN DP.POSTSTATUSCODE = 1 THEN DP.POSTDATE ELSE NULL END
        ,@POSTSTATUSCODE = DP.POSTSTATUSCODE
        ,@DISCOUNTASOFDATE = DP.DISCOUNTASOFDATE
    ,@TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
    ,@PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
    from 
        dbo.DISBURSEMENTPROCESS AS DP
    inner join dbo.BANKACCOUNT as BA on DP.BANKACCOUNTID = BA.ID
    where
        DP.ID = @DPID;

    -- remove hold via SP

    select 
        @INVOICEID = I.ID
        ,@HELD = I.HOLDPAYMENT
    from dbo.FINANCIALTRANSACTIONSCHEDULE FTS
        inner join dbo.INVOICE I on FTS.FINANCIALTRANSACTIONID = I.ID
    where
        I.HOLDPAYMENT = 1 
        and FTS.ID = @FTSID and FTS.DELETED = 0;

    if @INVOICEID is null
        begin
            exec dbo.USP_RAISE_ERROR;
            return 1;
        end

    if @HELD = 0
        begin
            exec dbo.USP_RAISE_ERROR;
            return 2;
        end

    -- Security problem here??

    exec USP_INVOICE_RELEASEPAYMENT @INVOICEID, @CHANGEAGENTID;

    ------ Add to the disbursement process. ------


    -- update Invoice and lock it to a DP

    update dbo.INVOICE set 
        DISBURSEMENTPROCESSID = @DPID
        ,DATECHANGED = @CURRENTDATE
      ,CHANGEDBYID = @CHANGEAGENTID
    where
        ID = @INVOICEID
        and DISBURSEMENTPROCESSID is null;

    ----- Create the FTAs required for the invoice. -----


    -- Insert a row into the temp table for the discount, if any applicable

    -- Currently only applicable if there is one and only one FTS

    insert into @TRANSACTIONS (
        FTSID
        ,AMOUNT
        ,STATUSCODE
        ,TYPECODE
        ,DISCOUNTDATE
    ) 
    select
        FTS.ID
        ,I.DISCOUNTAMOUNT
        ,0
        ,1
        ,I.DISCOUNTEXPIRATIONDATE
    from dbo.INVOICE I
        inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.FINANCIALTRANSACTIONID = I.ID
    where 
        I.ID = @INVOICEID and FTS.DELETED = 0
        and I.DISCOUNTAMOUNT > 0
        and (
            select count(*) from dbo.INVOICE I
            inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.FINANCIALTRANSACTIONID = I.ID and FTS.DELETED = 0
            where I.ID = @INVOICEID
        ) = 1;

    -- insert in the actual transaction information 

    insert into @TRANSACTIONS (
        FTSID
        ,AMOUNT
        ,STATUSCODE
        ,TYPECODE
    ) 
    select
        FTS.ID
        ,FTS.AMOUNT
        ,0
        ,0
    from 
        dbo.FINANCIALTRANSACTIONSCHEDULE FTS
    where 
        FTS.ID = @FTSID and FTS.DELETED = 0;

    -- check if there's a DPD this can go with. if not, create a new one.

    -- Currently relying that the data from the exclusion grid is correct. This might not be right... 

    -- ought to validate this against the DP criteria... crap.

    select 
        @DPDID = DPD.ID
    from dbo.INVOICE I
        inner join dbo.FINANCIALTRANSACTION FT on I.ID = FT.ID
        inner join dbo.VENDOR V on FT.CONSTITUENTID = V.ID
        inner join dbo.FINANCIALTRANSACTION FTDPD on V.ID = FTDPD.CONSTITUENTID and FTDPD.TYPECODE = 255
        inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FTDPD.ID
    where 
        DPD.DISBURSEMENTPROCESSID = @DPID
        and I.ID = @INVOICEID
        and I.SEPARATEPAYMENT = 0
        and (DPD.REMITADDRESSID is null or DPD.REMITADDRESSID = coalesce(I.REMITADDRESSID, V.REMITADDRESSID));

    if @DPDID is null
        begin
            set @DPDID = newid();
            -- create a new DPD for the DP for this Transaction

            -- first the FT

            insert into dbo.FINANCIALTRANSACTION
            ( 
                ID
                , TRANSACTIONAMOUNT
                , CONSTITUENTID
                -- From the process

                ,[DATE]
                , POSTDATE
                , POSTSTATUSCODE
        ,PDACCOUNTSYSTEMID
        ,TRANSACTIONCURRENCYID
                -- Standard stuff

                , DATEADDED
                , DATECHANGED
                , CHANGEDBYID
                , ADDEDBYID
                -- Constants

                , DELETEDON
                , TYPECODE
            ) 
            select
                @DPDID
                ,SUM(T.AMOUNT)
                ,FT.CONSTITUENTID
                -- From the process

                , @DISBURSEMENTDATE
                , @POSTDATE
                , @POSTSTATUSCODE
        ,@PDACCOUNTSYSTEMID
        ,@TRANSACTIONCURRENCYID
                -- Standard stuff

                , @CURRENTDATE DATECHANGED
                , @CURRENTDATE DATEADDED
                , @CHANGEAGENTID CHANGEDYID
                , @CHANGEAGENTID ADDEDBYID
                -- Constants

                , NULL
                , 255 -- System transaction    

            from dbo.INVOICE I
                inner join dbo.FINANCIALTRANSACTION FT on I.ID = FT.ID
                inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.FINANCIALTRANSACTIONID = FT.ID and FTS.DELETED = 0
                inner join @TRANSACTIONS T on T.FTSID = FTS.ID
            group by 
                FT.CONSTITUENTID;

            -- Link those transaction to my process

            insert into dbo.DISBURSEMENTPROCESSDISBURSEMENT
            (
                ID
                ,DISBURSEMENTPROCESSID    
                ,REMITADDRESSID
                -- Standard stuff

                , DATEADDED
                , DATECHANGED
                , CHANGEDBYID
                , ADDEDBYID
            )
            select
                @DPDID
                ,@DPID
                ,coalesce(I.REMITADDRESSID, V.REMITADDRESSID)
                -- Standard stuff

                , @CURRENTDATE DATECHANGED
                , @CURRENTDATE DATEADDED
                , @CHANGEAGENTID CHANGEDYID
                , @CHANGEAGENTID ADDEDBYID
            from @TRANSACTIONS as T
                inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on T.FTSID = FTS.ID and FTS.DELETED = 0
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTS.FINANCIALTRANSACTIONID
                inner join dbo.INVOICE I on FT.ID = I.ID
                inner join dbo.VENDOR V on FT.CONSTITUENTID = V.ID
            where T.TYPECODE = 0;
        end

    -- insert FTA for discount information -- May insert a blank 0 value row if the discount can't be taken yet.

    insert into dbo.FINANCIALTRANSACTIONAPPLICATION (
        FINANCIALTRANSACTIONSCHEDULEID
        ,FINANCIALTRANSACTIONID
        ,STATUSCODE
        ,TYPECODE
        ,AMOUNT
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
    ) 
    select
        T.FTSID
        ,@DPDID
        ,T.STATUSCODE
        ,T.TYPECODE
        ,case when (T.DISCOUNTDATE is null or T.DISCOUNTDATE >= @DISCOUNTASOFDATE)
            then T.AMOUNT
            else 0
        end [AMOUNT]
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
    from 
        @TRANSACTIONS T
    where T.TYPECODE = 1;

    -- insert FTA for rest of the scheduled amount

    insert into dbo.FINANCIALTRANSACTIONAPPLICATION (
        FINANCIALTRANSACTIONSCHEDULEID
        ,FINANCIALTRANSACTIONID
        ,STATUSCODE
        ,TYPECODE
        ,AMOUNT
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
    ) 
    select
        T.FTSID
        ,@DPDID
        ,T.STATUSCODE
        ,T.TYPECODE
        ,T.AMOUNT - coalesce(APPLIED.[AMOUNT], 0)
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
    from 
        @TRANSACTIONS T
        left outer join (
            select 
                FTA.FINANCIALTRANSACTIONSCHEDULEID [FTSID]
                , sum(FTA.AMOUNT) [AMOUNT]
            from 
                dbo.FINANCIALTRANSACTIONAPPLICATION FTA
            group by FTA.FINANCIALTRANSACTIONSCHEDULEID
        ) as APPLIED on APPLIED.FTSID = T.FTSID
    where T.TYPECODE = 0;
    return 0;

end