USP_GETTREASURY_GAINLOSSDISTRIBUTIONS

CREATE procedure dbo.USP_GETTREASURY_GAINLOSSDISTRIBUTIONS
(
  @BASEAMOUNT1 money
  ,@BASEAMOUNT2 money
  ,@ORGANIZATIONAMOUNT1 money
  ,@ORGANIZATIONAMOUNT2 money
  ,@TRANSACTIONCURRENCYID uniqueidentifier
  ,@PDACCOUNTSYSTEMID uniqueidentifier
)
as 
begin
    declare @DISTRIBUTION TABLE
    (
        GLTRANSACTIONID uniqueidentifier
        ,ACCOUNT nvarchar(100)
        ,GLACCOUNTID uniqueidentifier
        ,TRANSACTIONTYPECODE tinyint
        ,BASEAMOUNT money
        ,ORGANIZATIONAMOUNT money
        ,REFERENCE nvarchar(100)
    )

    if @BASEAMOUNT1 <> @BASEAMOUNT2
    begin
        declare @BASEGLACCOUNTID uniqueidentifier;
        declare @BASEGLACCOUNT nvarchar(100);
        declare @BASEPDACCOUNTSEGMENTVALUEID uniqueidentifier;
        select @BASEPDACCOUNTSEGMENTVALUEID = M.CREDITPDACCOUNTSEGMENTVALUEID, @BASEGLACCOUNTID = M.CREDITGLACCOUNTID
        from dbo.PDACCOUNTCODEMAPPING M
        inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE ST on M.ID = ST.PDACCOUNTCODEMAPPINGID
        where M.OFFICEID = 16 
            and (M.PAYMENTMETHOD & 
                CASE
                    WHEN @BASEAMOUNT1 < @BASEAMOUNT2 THEN 1
                    WHEN @BASEAMOUNT1 > @BASEAMOUNT2 THEN 2
                END) > 0
            and (ST.SUBTYPEID = '99999999-9999-9999-9999-999999999999'
                or ST.SUBTYPEID = @TRANSACTIONCURRENCYID)

        declare @GAINLOSS nvarchar(100);
        select @GAINLOSS = TYPENAME from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE where OFFICEID = 16 and NAMEID = 
            CASE
                WHEN @BASEAMOUNT1 < @BASEAMOUNT2 THEN 1
                WHEN @BASEAMOUNT1 > @BASEAMOUNT2 THEN 2
            END

        if @BASEGLACCOUNTID is null and @BASEPDACCOUNTSEGMENTVALUEID is null
        begin
            declare @CURRENCYNAME nvarchar(100);
            declare @OFFICENAME nvarchar(100);
            select @OFFICENAME = NAME from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = 16;
            select @CURRENCYNAME = NAME from dbo.CURRENCY where ID = @TRANSACTIONCURRENCYID;
            raiserror('An account code has not been defined for this mapping of Office: %s, %s, Currency: %s', 13, 1, @OFFICENAME, @GAINLOSS, @CURRENCYNAME)
        end
        if @BASEGLACCOUNTID is null
        begin
            select @BASEGLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@BASEPDACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
            select @BASEGLACCOUNTID = ID from GLACCOUNT where ACCOUNTNUMBER = @BASEGLACCOUNT;
            if @BASEGLACCOUNTID is null
                raiserror('The account %s does not exist. The action could not be completed. ', 13, 1, @BASEGLACCOUNT);
        end
        else
            select @BASEGLACCOUNT = ACCOUNTNUMBER from GLACCOUNT where ID = @BASEGLACCOUNTID;

        insert into @DISTRIBUTION
            (GLTRANSACTIONID
            ,ACCOUNT
            ,GLACCOUNTID 
            ,TRANSACTIONTYPECODE 
            ,BASEAMOUNT 
            ,ORGANIZATIONAMOUNT
            ,REFERENCE)
        values
            (NEWID()
            ,@BASEGLACCOUNT
            ,@BASEGLACCOUNTID
            ,CASE
                WHEN @BASEAMOUNT1 < @BASEAMOUNT2 THEN 0
                WHEN @BASEAMOUNT1 > @BASEAMOUNT2 THEN 1
            END
            ,ABS(@BASEAMOUNT1 - @BASEAMOUNT2)
            ,0
            ,@GAINLOSS)
    end
    if @ORGANIZATIONAMOUNT1 <> @ORGANIZATIONAMOUNT2
    begin
        declare @ORGGLACCOUNTID uniqueidentifier;
        declare @ORGGLACCOUNT nvarchar(100);
        declare @ORGPDACCOUNTSEGMENTVALUEID uniqueidentifier;
        select @ORGPDACCOUNTSEGMENTVALUEID = M.CREDITPDACCOUNTSEGMENTVALUEID, @ORGGLACCOUNTID = M.CREDITGLACCOUNTID
        from dbo.PDACCOUNTCODEMAPPING M
        inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE ST on M.ID = ST.PDACCOUNTCODEMAPPINGID
        where M.OFFICEID = 16 
            and (M.PAYMENTMETHOD & 
                CASE
                    WHEN @ORGANIZATIONAMOUNT1 < @ORGANIZATIONAMOUNT2 THEN 1
                    WHEN @ORGANIZATIONAMOUNT1 > @ORGANIZATIONAMOUNT2 THEN 2
                END) > 0
            and (ST.SUBTYPEID = '99999999-9999-9999-9999-999999999999'
                or ST.SUBTYPEID = @TRANSACTIONCURRENCYID)

        select @GAINLOSS = TYPENAME from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE where OFFICEID = 16 and NAMEID = 
            CASE
                WHEN @ORGANIZATIONAMOUNT1 < @ORGANIZATIONAMOUNT2 THEN 1
                WHEN @ORGANIZATIONAMOUNT1 > @ORGANIZATIONAMOUNT2 THEN 2
            END
        if @ORGGLACCOUNTID is null and @ORGPDACCOUNTSEGMENTVALUEID is null
        begin
            select @OFFICENAME = NAME from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = 16;
            select @CURRENCYNAME = NAME from dbo.CURRENCY where ID = @TRANSACTIONCURRENCYID;
            raiserror('An account code has not been defined for this mapping of Office: %s, %s, Currency: %s', 13, 1, @OFFICENAME, @GAINLOSS, @CURRENCYNAME)
        end
        if @ORGGLACCOUNTID is null
        begin
            select @ORGGLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@ORGPDACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
            select @ORGGLACCOUNTID = ID from GLACCOUNT where ACCOUNTNUMBER = @ORGGLACCOUNT;
            if @ORGGLACCOUNTID is null
                raiserror('The account %s does not exist. The action could not be completed. ', 13, 1, @ORGGLACCOUNT);
        end
        else
            select @ORGGLACCOUNT = ACCOUNTNUMBER from GLACCOUNT where ID = @ORGGLACCOUNTID;

        declare @TRANSACTIONTYPECODE tinyint;
        set @TRANSACTIONTYPECODE = 
            CASE
                WHEN @ORGANIZATIONAMOUNT1 < @ORGANIZATIONAMOUNT2 THEN 0
                WHEN @ORGANIZATIONAMOUNT1 > @ORGANIZATIONAMOUNT2 THEN 1
            END 

        if exists(select GLTRANSACTIONID from @DISTRIBUTION where GLACCOUNTID = @ORGGLACCOUNTID and TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE)
            update @DISTRIBUTION
                set ORGANIZATIONAMOUNT = ABS(@ORGANIZATIONAMOUNT1 - @ORGANIZATIONAMOUNT2)
            where GLACCOUNTID = @ORGGLACCOUNTID and TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE;
        else
            insert into @DISTRIBUTION
                (GLTRANSACTIONID
                ,ACCOUNT
                ,GLACCOUNTID 
                ,TRANSACTIONTYPECODE 
                ,BASEAMOUNT 
                ,ORGANIZATIONAMOUNT
                ,REFERENCE)
            values
                (NEWID()
                ,@ORGGLACCOUNT
                ,@ORGGLACCOUNTID
                ,@TRANSACTIONTYPECODE
                ,0
                ,ABS(@ORGANIZATIONAMOUNT1 - @ORGANIZATIONAMOUNT2)
                ,@GAINLOSS)
    end

    select GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, BASEAMOUNT, ORGANIZATIONAMOUNT, REFERENCE
    from @DISTRIBUTION
end