USP_GET_GAINLOSSDISTRIBUTIONS

CREATE procedure dbo.USP_GET_GAINLOSSDISTRIBUTIONS
(
    @AMOUNTS UDT_GAINLOSS_AMOUNTS readonly
    ,@DEBITSIDE bit
    ,@TRANSACTIONCURRENCYID uniqueidentifier
    ,@PDACCOUNTSYSTEMID uniqueidentifier
)
as begin
    declare @DISTRIBUTION TABLE
    (
        ID uniqueidentifier
        ,GLTRANSACTIONID uniqueidentifier
        ,ACCOUNT nvarchar(100)
        ,GLACCOUNTID uniqueidentifier
        ,TRANSACTIONTYPECODE tinyint
        ,BASEAMOUNT money
        ,ORGANIZATIONAMOUNT money
        ,REFERENCE nvarchar(100)
        ,ERRORMESSAGE nvarchar(255)
        ,MAPPEDVALUES XML
    )

    declare @DefaultAccountID uniqueidentifier;
    declare @DefaultAccount nvarchar(100);
    declare @ErrorMessage nvarchar(255);
    declare @LOSSGLACCOUNTID uniqueidentifier;
    declare @LOSSGLACCOUNT nvarchar(100);
    declare @ACCOUNTSYSTEM nvarchar(50);
    declare @MAPPEDVALUES XML;
    declare @PAYMENTMETHODTYPEID uniqueidentifier;

    select @DefaultAccountID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID, @DefaultAccount = GLACCOUNT.ACCOUNTNUMBER, @ACCOUNTSYSTEM = PDACCOUNTSYSTEM.NAME
    from dbo.PDACCOUNTSYSTEM
    left join dbo.GLACCOUNT on PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID = GLACCOUNT.ID
    where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID

    --Check if we need the loss account

    if exists(select ID from @AMOUNTS where 
        (@DEBITSIDE = 1 and (NEWBASEAMOUNT < ORIGINALBASEAMOUNT or NEWORGANIZATIONAMOUNT < ORIGINALORGANIZATIONAMOUNT))
        or (@DEBITSIDE = 0 and (NEWBASEAMOUNT > ORIGINALBASEAMOUNT or NEWORGANIZATIONAMOUNT > ORIGINALORGANIZATIONAMOUNT)))
    begin
        declare @LOSSPDACCOUNTSEGMENTVALUEID uniqueidentifier;
        declare @LOSSACCOUNTCODE nvarchar(30);
        select @LOSSPDACCOUNTSEGMENTVALUEID = M.CREDITPDACCOUNTSEGMENTVALUEID
            ,@LOSSGLACCOUNTID = M.CREDITGLACCOUNTID
            ,@LOSSGLACCOUNT = A.ACCOUNTNUMBER
            ,@LOSSACCOUNTCODE = V.SHORTDESCRIPTION
        from dbo.PDACCOUNTCODEMAPPING M
        inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE ST on M.ID = ST.PDACCOUNTCODEMAPPINGID
        left outer join dbo.GLACCOUNT A on A.ID = M.CREDITGLACCOUNTID
        left outer join dbo.PDACCOUNTSEGMENTVALUE V on V.ID = M.CREDITPDACCOUNTSEGMENTVALUEID
        where M.OFFICEID = 16
            and (M.PAYMENTMETHOD & 2) > 0
            and (ST.SUBTYPEID = '99999999-9999-9999-9999-999999999999'
                or ST.SUBTYPEID = @TRANSACTIONCURRENCYID)
            and M.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

        declare @REFERENCE nvarchar(100);
        set @REFERENCE = 'Currency Exchange Loss';

        if @LOSSGLACCOUNTID is null and @LOSSPDACCOUNTSEGMENTVALUEID is null
        begin
            select @PAYMENTMETHODTYPEID = MT.ID from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE MT where MT.OFFICEID = 16 and MT.NAMEID = 2;

            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;
            set @ErrorMessage = 'In account system "' + @ACCOUNTSYSTEM + '", a mapping does not exist for Office: ' + @OFFICENAME + ', ' + @REFERENCE + ', Currency: ' + @CURRENCYNAME
            set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 1 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 16, null, null, @PAYMENTMETHODTYPEID, @TRANSACTIONCURRENCYID) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
            if @DefaultAccountID is null
                raiserror(@ErrorMessage, 13, 1);
            else
            begin
                set @LOSSGLACCOUNTID = @DefaultAccountID;
                set @LOSSGLACCOUNT = @DefaultAccount;
            end
        end

        if @LOSSGLACCOUNTID is null and exists(select ID from @AMOUNTS where GLACCOUNTID is null)
        begin
            select @LOSSGLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@LOSSPDACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
            select @LOSSGLACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @LOSSGLACCOUNT and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;
            if @LOSSGLACCOUNTID is null
            begin
                set @ErrorMessage = 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + @LOSSGLACCOUNT + '" does not exist. The action could not be completed.'
                set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 16, null, null, @PAYMENTMETHODTYPEID, @TRANSACTIONCURRENCYID) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
                if @DefaultAccountID is null
                    raiserror(@ErrorMessage, 13, 1);
                else
                begin
                    set @LOSSGLACCOUNTID = @DefaultAccountID;
                    set @LOSSGLACCOUNT = @DefaultAccount;
                end
            end
        end

        --Insert the loss distributions

        insert into @DISTRIBUTION
            (ID
            ,GLTRANSACTIONID
            ,ACCOUNT
            ,GLACCOUNTID
            ,TRANSACTIONTYPECODE
            ,BASEAMOUNT
            ,ORGANIZATIONAMOUNT
            ,REFERENCE
            ,ERRORMESSAGE
            ,MAPPEDVALUES)
        select
            ID
            ,NEWID()
            ,ISNULL(@LOSSGLACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(GLACCOUNTID, @LOSSACCOUNTCODE, @PDACCOUNTSYSTEMID))
            ,@LOSSGLACCOUNTID
            ,0 --Losses are always marked as a debit

            ,CASE WHEN (@DEBITSIDE = 1 and NEWBASEAMOUNT < ORIGINALBASEAMOUNT) or (@DEBITSIDE = 0 and NEWBASEAMOUNT > ORIGINALBASEAMOUNT) then ABS(NEWBASEAMOUNT - ORIGINALBASEAMOUNT) else 0 END
            ,CASE WHEN (@DEBITSIDE = 1 and NEWORGANIZATIONAMOUNT < ORIGINALORGANIZATIONAMOUNT) or (@DEBITSIDE = 0 and NEWORGANIZATIONAMOUNT > ORIGINALORGANIZATIONAMOUNT) then ABS(NEWORGANIZATIONAMOUNT - ORIGINALORGANIZATIONAMOUNT) else 0 END
            ,@REFERENCE
            ,@ErrorMessage
            ,@MAPPEDVALUES
        from @AMOUNTS
        where ((@DEBITSIDE = 1 and NEWBASEAMOUNT < ORIGINALBASEAMOUNT) or (@DEBITSIDE = 0 and NEWBASEAMOUNT > ORIGINALBASEAMOUNT))
            or ((@DEBITSIDE = 1 and NEWORGANIZATIONAMOUNT < ORIGINALORGANIZATIONAMOUNT) or (@DEBITSIDE = 0 and NEWORGANIZATIONAMOUNT > ORIGINALORGANIZATIONAMOUNT))
    end
    set @ErrorMessage = null;
    declare @GAINGLACCOUNTID uniqueidentifier;
    declare @GAINGLACCOUNT nvarchar(100);
    --Check if we need the gain account

    if exists(select ID from @AMOUNTS where
        (@DEBITSIDE = 1 and (NEWBASEAMOUNT > ORIGINALBASEAMOUNT or NEWORGANIZATIONAMOUNT > ORIGINALORGANIZATIONAMOUNT))
        or (@DEBITSIDE = 0 and (NEWBASEAMOUNT < ORIGINALBASEAMOUNT or NEWORGANIZATIONAMOUNT < ORIGINALORGANIZATIONAMOUNT)))
    begin
        declare @GAINPDACCOUNTSEGMENTVALUEID uniqueidentifier;
        declare @GAINACCOUNTCODE nvarchar(30);
        select @GAINPDACCOUNTSEGMENTVALUEID = M.CREDITPDACCOUNTSEGMENTVALUEID
            ,@GAINGLACCOUNTID = M.CREDITGLACCOUNTID
            ,@GAINGLACCOUNT = A.ACCOUNTNUMBER
            ,@GAINACCOUNTCODE = V.SHORTDESCRIPTION
        from dbo.PDACCOUNTCODEMAPPING M
        inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE ST on M.ID = ST.PDACCOUNTCODEMAPPINGID
        left outer join dbo.GLACCOUNT A on A.ID = M.CREDITGLACCOUNTID
        left outer join dbo.PDACCOUNTSEGMENTVALUE V on V.ID = M.CREDITPDACCOUNTSEGMENTVALUEID
        where M.OFFICEID = 16
            and (M.PAYMENTMETHOD & 1) > 0
            and (ST.SUBTYPEID = '99999999-9999-9999-9999-999999999999'
                or ST.SUBTYPEID = @TRANSACTIONCURRENCYID)
            and M.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

        set @REFERENCE = 'Currency Exchange Gain';

        if @GAINGLACCOUNTID is null and @GAINPDACCOUNTSEGMENTVALUEID is null
        begin
            select @PAYMENTMETHODTYPEID = MT.ID from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE MT where MT.OFFICEID = 16 and MT.NAMEID = 1;
            select @OFFICENAME = NAME from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = 16;
            select @CURRENCYNAME = NAME from dbo.CURRENCY where ID = @TRANSACTIONCURRENCYID;
            set @ErrorMessage = 'In account system "' + @ACCOUNTSYSTEM + '", a mapping does not exist for Office: ' + @OFFICENAME + ', ' + @REFERENCE + ', Currency: ' + @CURRENCYNAME
            set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 1 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 16, null, null, @PAYMENTMETHODTYPEID, @TRANSACTIONCURRENCYID) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
            if @DefaultAccountID is null
                raiserror(@ErrorMessage, 13, 1);
            else
            begin
                set @GAINGLACCOUNTID = @DefaultAccountID;
                set @GAINGLACCOUNT = @DefaultAccount;
            end
        end

        if @GAINGLACCOUNTID is null and exists(select ID from @AMOUNTS where GLACCOUNTID is null)
        begin
            select @GAINGLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@GAINPDACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
            select @GAINGLACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @GAINGLACCOUNT and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;
            if @GAINGLACCOUNTID is null
            begin
                set @ErrorMessage = 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + @GAINGLACCOUNT + '" does not exist. The action could not be completed.'
                set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 16, null, null, @PAYMENTMETHODTYPEID, @TRANSACTIONCURRENCYID) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
                if @DefaultAccountID is null
                    raiserror(@ErrorMessage, 13, 1);
                else
                begin
                    set @GAINGLACCOUNTID = @DefaultAccountID;
                    set @GAINGLACCOUNT = @DefaultAccount;
                end
            end
        end

        --Insert the gain distributions

        insert into @DISTRIBUTION
            (ID
            ,GLTRANSACTIONID
            ,ACCOUNT
            ,GLACCOUNTID
            ,TRANSACTIONTYPECODE
            ,BASEAMOUNT
            ,ORGANIZATIONAMOUNT
            ,REFERENCE
            ,ERRORMESSAGE
            ,MAPPEDVALUES)
        select
            ID
            ,NEWID()
            ,ISNULL(@GAINGLACCOUNT, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(GLACCOUNTID, @GAINACCOUNTCODE, @PDACCOUNTSYSTEMID))
            ,@GAINGLACCOUNTID
            ,1 --Gains are always marked as a credit

            ,CASE WHEN (@DEBITSIDE = 1 and NEWBASEAMOUNT > ORIGINALBASEAMOUNT) or (@DEBITSIDE = 0 and NEWBASEAMOUNT < ORIGINALBASEAMOUNT) then ABS(NEWBASEAMOUNT - ORIGINALBASEAMOUNT) else 0 END
            ,CASE WHEN (@DEBITSIDE = 1 and NEWORGANIZATIONAMOUNT > ORIGINALORGANIZATIONAMOUNT) or (@DEBITSIDE = 0 and NEWORGANIZATIONAMOUNT < ORIGINALORGANIZATIONAMOUNT) then ABS(NEWORGANIZATIONAMOUNT - ORIGINALORGANIZATIONAMOUNT) else 0 END
            ,@REFERENCE
            ,@ErrorMessage
            ,@MAPPEDVALUES
        from @AMOUNTS
        where ((@DEBITSIDE = 1 and NEWBASEAMOUNT > ORIGINALBASEAMOUNT) or (@DEBITSIDE = 0 and NEWBASEAMOUNT < ORIGINALBASEAMOUNT))
            or ((@DEBITSIDE = 1 and NEWORGANIZATIONAMOUNT > ORIGINALORGANIZATIONAMOUNT) or (@DEBITSIDE = 0 and NEWORGANIZATIONAMOUNT < ORIGINALORGANIZATIONAMOUNT))
    end

    update @DISTRIBUTION set
        GLACCOUNTID = GLACCOUNT.ID
    from dbo.GLACCOUNT
    inner join @DISTRIBUTION T on T.ACCOUNT = GLACCOUNT.ACCOUNTNUMBER
    where GLACCOUNTID is null and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

    if exists(select ID from @DISTRIBUTION where GLACCOUNTID is null)
    begin
        if @DefaultAccountID is null
        begin
            select top 1 @LOSSGLACCOUNT = ACCOUNT from @DISTRIBUTION where GLACCOUNTID is null
            raiserror('In account system "%s", the account "%s" does not exist. The action could not be completed.', 13, 1, @ACCOUNTSYSTEM, @LOSSGLACCOUNT)
        end
        else
            update @DISTRIBUTION set
                GLACCOUNTID = @DefaultAccountID
                ,ACCOUNT = @DefaultAccount
                ,ERRORMESSAGE = 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + ACCOUNT + '" does not exist. The action could not be completed.'
                ,MAPPEDVALUES = (select tv1.*, 'In account system "' + @ACCOUNTSYSTEM + '", the account "' + ACCOUNT + '" does not exist. The action could not be completed.' as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 16, null, null, @PAYMENTMETHODTYPEID, @TRANSACTIONCURRENCYID) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
            where GLACCOUNTID is null;
    end

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