USP_FINANCIALTRANSACTION_APPLIACTION_CREATE_1099DISTRIBUTION

CREATE procedure dbo.USP_FINANCIALTRANSACTION_APPLIACTION_CREATE_1099DISTRIBUTION(
    @DISBURSEMENTPROCESSID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier
)
as
    set nocount on; 

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate() 

  declare @FTD table(
    ID uniqueidentifier
    ,AMOUNT money
    ,BOXNUMBER1099ID uniqueidentifier
    ,STATEID uniqueidentifier
    ,FINANCIALTRANSACTIONID uniqueidentifier
    ,TYPECODE int
  )

  insert into @FTD
  select RESULTS.FTAID, RESULTS.AMOUNT, FTD.BOXNUMBER1099ID, FTD.STATEID, FTD.FINANCIALTRANSACTIONID, RESULTS.TYPECODE
  from (
      select FTD.ID, FTA.ID [FTAID], FT.TYPECODE
    ,CASE WHEN FT.TYPECODE = 101 THEN 1 ELSE -1 END * ROUND((FTD.AMOUNT * SUM(FTA.AMOUNT) / FT.TRANSACTIONAMOUNT), 2) [AMOUNT]
      from dbo.FINANCIALTRANSACTION1099DISTRIBUTION FTD
      inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTD.FINANCIALTRANSACTIONID
      inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.FINANCIALTRANSACTIONID = FT.ID and FTS.DELETED = 0
      inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
      inner join dbo.BANKACCOUNTTRANSACTION_EXT BATEXT on BATEXT.ID = FTA.FINANCIALTRANSACTIONID
      where BATEXT.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID and FTA.STATUSCODE = 1
      group by FTA.ID, FTD.ID, FTD.AMOUNT, FT.TRANSACTIONAMOUNT, FT.TYPECODE) RESULTS
  inner join dbo.FINANCIALTRANSACTION1099DISTRIBUTION FTD on FTD.ID = RESULTS.ID

    insert into dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION
    (
        ID
        ,FINANCIALTRANSACTIONAPPLICATIONID
        ,BOXNUMBER1099ID
        ,STATEID
        ,AMOUNT
        ,SEQUENCE
        ,DATEADDED
        ,DATECHANGED
        ,CHANGEDBYID
        ,ADDEDBYID
    )
    SELECT
        NEWID()
        ,FTA.ID
        ,FTD.BOXNUMBER1099ID
        ,FTD.STATEID
        ,ROUND(FTD.AMOUNT, 2)
        ,ROW_NUMBER() over (partition by FTD.BOXNUMBER1099ID, FTD.STATEID, FTA.FINANCIALTRANSACTIONID order by FTA.ID)
        ,@CURRENTDATE
        ,@CURRENTDATE
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
    FROM dbo.BANKACCOUNTTRANSACTION_EXT BATE
    inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on BATE.ID = FTA.FINANCIALTRANSACTIONID
    inner join @FTD FTD on FTD.ID = FTA.ID
    where BATE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID and FTA.STATUSCODE = 1 and ((FTD.TYPECODE = 101 and FTD.AMOUNT > 0) or (FTD.TYPECODE = 102))

    declare @DIFFERENCE table 
    (
        FINANCIALTRANSACTIONID uniqueidentifier
        ,DIFF money
        ,BOXNUMBER1099ID uniqueidentifier
        ,STATEID uniqueidentifier
    );

    --Get the amount that the 1099 distribution needs to be adjusted by for each disbursement based on box number and state

    insert into @DIFFERENCE
    (
        FINANCIALTRANSACTIONID
        ,DIFF
        ,BOXNUMBER1099ID
        ,STATEID
    )
    select FTS.FINANCIALTRANSACTIONID, ISNULL(DISTRIBUTION.AMOUNT, 0) - CASE WHEN DISTRIBUTION.TYPECODE = 101 THEN 1 ELSE -1 END * SUM(FTAD.AMOUNT) DIFF, DISTRIBUTION.BOXNUMBER1099ID, DISTRIBUTION.STATEID
    from dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION FTAD
    inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.ID = FTAD.FINANCIALTRANSACTIONAPPLICATIONID
    inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID and FTS.DELETED = 0
    inner join (select FT.ID, DISTRIBUTIONAMOUNT.AMOUNT, DISTRIBUTIONAMOUNT.BOXNUMBER1099ID, DISTRIBUTIONAMOUNT.STATEID, FT.TYPECODE
    from dbo.FINANCIALTRANSACTION FT
    inner join (select FT.ID, CASE WHEN FT.TYPECODE = 101 THEN 1 ELSE -1 END * SUM(FTD.AMOUNT) [AMOUNT], FTD.BOXNUMBER1099ID, FTD.STATEID
        from dbo.FINANCIALTRANSACTION FT
        inner join dbo.FINANCIALTRANSACTION1099DISTRIBUTION FTD on FT.ID = FTD.FINANCIALTRANSACTIONID
        left outer join dbo.INVOICE I on I.ID = FT.ID
        left outer join dbo.CREDITMEMO CM on CM.ID = FT.ID
        where ISNULL(I.DISBURSEMENTPROCESSID, CM.DISBURSEMENTPROCESSID) = @DISBURSEMENTPROCESSID 
        group by FT.ID, FTD.BOXNUMBER1099ID, FTD.STATEID, FT.TYPECODE
            ) DISTRIBUTIONAMOUNT on DISTRIBUTIONAMOUNT.ID = FT.ID
    inner join (select FT.ID
        from dbo.FINANCIALTRANSACTIONAPPLICATION FTA
        inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID and FTS.DELETED = 0
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTS.FINANCIALTRANSACTIONID
        left outer join dbo.INVOICE I on I.ID = FT.ID
        left outer join dbo.CREDITMEMO CM on CM.ID = FT.ID
        where ISNULL(I.DISBURSEMENTPROCESSID, CM.DISBURSEMENTPROCESSID) = @DISBURSEMENTPROCESSID and FTA.STATUSCODE = 1
        group by FT.ID, FT.TRANSACTIONAMOUNT
        having (SUM(FTA.AMOUNT) = FT.TRANSACTIONAMOUNT)
            ) APPLICATIONS on APPLICATIONS.ID = FT.ID
    ) DISTRIBUTION on DISTRIBUTION.ID = FTS.FINANCIALTRANSACTIONID and FTAD.BOXNUMBER1099ID = DISTRIBUTION.BOXNUMBER1099ID and (FTAD.STATEID = DISTRIBUTION.STATEID or (FTAD.STATEID is null and DISTRIBUTION.STATEID is null))
    group by FTS.FINANCIALTRANSACTIONID, DISTRIBUTION.AMOUNT, DISTRIBUTION.BOXNUMBER1099ID, DISTRIBUTION.STATEID, DISTRIBUTION.TYPECODE
    HAVING SUM(FTAD.AMOUNT) != DISTRIBUTION.AMOUNT

    update dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION
    set AMOUNT = FTAD.AMOUNT + isnull(DIFFERENCE.DIFF, 0)
    from FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION FTAD
    inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.ID = FTAD.FINANCIALTRANSACTIONAPPLICATIONID
    inner join dbo.BANKACCOUNTTRANSACTION_EXT BATE on FTA.FINANCIALTRANSACTIONID = BATE.ID
    inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID and FTS.DELETED = 0
    inner join @DIFFERENCE DIFFERENCE on DIFFERENCE.FINANCIALTRANSACTIONID = FTS.FINANCIALTRANSACTIONID and DIFFERENCE.BOXNUMBER1099ID = FTAD.BOXNUMBER1099ID and (DIFFERENCE.STATEID = FTAD.STATEID or (DIFFERENCE.STATEID is null and FTAD.STATEID is null))
    where FTAD.SEQUENCE = 1 and BATE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID and (FTAD.AMOUNT + isnull(DIFFERENCE.DIFF, 0)) > 0

    delete from dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION
    where ID in (
        select FTAD.ID
        from dbo.FINANCIALTRANSACTIONAPPLICATION1099DISTRIBUTION FTAD
        inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.ID = FTAD.FINANCIALTRANSACTIONAPPLICATIONID
        inner join dbo.BANKACCOUNTTRANSACTION_EXT BATE on FTA.FINANCIALTRANSACTIONID = BATE.ID
        inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID and FTS.DELETED = 0
        inner join @DIFFERENCE DIFFERENCE on DIFFERENCE.FINANCIALTRANSACTIONID = FTS.FINANCIALTRANSACTIONID and DIFFERENCE.BOXNUMBER1099ID = FTAD.BOXNUMBER1099ID and (DIFFERENCE.STATEID = FTAD.STATEID or (DIFFERENCE.STATEID is null and FTAD.STATEID is null))
        where FTAD.SEQUENCE = 1 and BATE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID and (FTAD.AMOUNT + isnull(DIFFERENCE.DIFF, 0)) <= 0)