USP_BANKACCOUNTDEPOSITCORRECTION_CREATEDISTRIBUTION

CREATE procedure dbo.USP_BANKACCOUNTDEPOSITCORRECTION_CREATEDISTRIBUTION
(
  @DEPOSITCORRECTIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTDATE datetime = null,
  @PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
begin
    set nocount on;
  declare @JOURNAL nvarchar(50) = 'Blackbaud Enterprise';
  declare @REFERENCE nvarchar(100) = 'Bank Account Deposit Correction';
    declare @DEPOSITREFERENCE nvarchar(100) = 'Bank Account Deposit Correction';

  declare @DISTRIBUTION table(
    ID 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
    ,SYSTEMDISTRIBUTION bit
        ,ERRORMESSAGE nvarchar(255)
        ,MAPPEDVALUES xml);

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

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

  declare @CORRECTIONTYPECODE tinyint;
  declare @METHODCODE tinyint;
  declare @AMOUNT money;
  declare @BANKGLACCOUNTID uniqueidentifier;
  declare @GLACCOUNTID uniqueidentifier;
  declare @BANKGLACCOUNT nvarchar(100);
  declare @GLACCOUNT nvarchar(100);
  declare @BANKPDACCOUNTSEGMENTVALUEID uniqueidentifier;
  declare @PDACCOUNTSEGMENTVALUEID uniqueidentifier;
  declare @POSTSTATUSCODE tinyint;
  declare @POSTDATE datetime;
  declare @UNMAPPEDCOMPSEGMENT tinyint;
  declare @TRANSACTIONAMOUNT money;
  declare @ORGANIZATIONAMOUNT money;
  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @BASEEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @BATRANSACTIONCURRENCYID uniqueidentifier;
  declare @BADTRANSACTIONEXCHANGERATEID uniqueidentifier;
  declare @BATBASEEXCHANGERATEID uniqueidentifier;
  declare @BATORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @DEFAULTGLACCOUNTID uniqueidentifier
  declare @DEFAULTGLACCOUNT varchar(100)
    declare @ERRORMESSAGE nvarchar(255)
    declare @MAPPEDVALUES xml;
    declare @PAYMENTMETHODTYPEID uniqueidentifier;
    declare @REVENUETYPEID uniqueidentifier;
    declare @AccountSystem nvarchar(50);

  select @CORRECTIONTYPECODE = C.CORRECTIONTYPECODE,
         @METHODCODE = C.PAYMENTMETHODCODE,
         @AMOUNT = C.AMOUNT,
         @BANKGLACCOUNTID = BA.GLACCOUNTID,
         @BANKGLACCOUNT = A.ACCOUNTNUMBER,
         @BANKPDACCOUNTSEGMENTVALUEID = BA.PDACCOUNTSEGMENTVALUEID,
         @POSTSTATUSCODE = T.POSTSTATUSCODE,
         @POSTDATE = T.POSTDATE,
         @REFERENCE = CASE WHEN Len(C.REFERENCE) > 0 THEN C.REFERENCE ELSE @REFERENCE END,
         @DEPOSITREFERENCE = CASE WHEN Len(T.REFERENCE) > 0 THEN T.REFERENCE ELSE CASE WHEN Len(C.REFERENCE) > 0 THEN C.REFERENCE ELSE @REFERENCE END END,
         @PDACCOUNTSYSTEMID = BA.PDACCOUNTSYSTEMID
         ,@TRANSACTIONAMOUNT = C.TRANSACTIONAMOUNT
         ,@ORGANIZATIONAMOUNT = C.ORGANIZATIONAMOUNT
         ,@TRANSACTIONCURRENCYID = D.TRANSACTIONCURRENCYID
         ,@BASECURRENCYID = C.BASECURRENCYID
         ,@BASEEXCHANGERATEID = C.BASEEXCHANGERATEID
         ,@ORGANIZATIONEXCHANGERATEID = C.ORGANIZATIONEXCHANGERATEID
         ,@BATRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID
         ,@BADTRANSACTIONEXCHANGERATEID = D.TRANSACTIONEXCHANGERATEID
         ,@BATBASEEXCHANGERATEID = T.BASEEXCHANGERATEID
         ,@BATORGANIZATIONEXCHANGERATEID = T.ORGANIZATIONEXCHANGERATEID
  from dbo.BANKACCOUNTDEPOSITCORRECTION C
  inner join dbo.BANKACCOUNTDEPOSIT as D on C.DEPOSITID = D.ID
  inner join dbo.BANKACCOUNTTRANSACTION T on T.ID = C.DEPOSITID
  inner join dbo.BANKACCOUNT BA on BA.ID = T.BANKACCOUNTID
  left outer join dbo.GLACCOUNT A on A.ID = BA.GLACCOUNTID
  where C.ID = @DEPOSITCORRECTIONID;

  declare @DEBITGLACCOUNTID uniqueidentifier;
  declare @CREDITGLACCOUNTID uniqueidentifier;
  declare @NAMEID int ;
  set @NAMEID = CASE @CORRECTIONTYPECODE WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 4 WHEN 3 THEN 8 END

  select @DEFAULTGLACCOUNTID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID, @DEFAULTGLACCOUNT = GLACCOUNT.ACCOUNTNUMBER, @AccountSystem = PDACCOUNTSYSTEM.NAME
  from dbo.PDACCOUNTSYSTEM left join dbo.GLACCOUNT on PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID = GLACCOUNT.ID
  where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID

  select @DEBITGLACCOUNTID = T1.DEBITGLACCOUNTID from PDACCOUNTCODEMAPPING T1 left join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE T2 on (T1.PAYMENTMETHOD & T2.NAMEID) > 0 and T1.OFFICEID = T2.OFFICEID where T1.OFFICEID = 10 and T1.PDACCOUNTSYSTEMID =  @PDACCOUNTSYSTEMID and T2.NAMEID = @NAMEID and (T1.REVENUETYPE & CASE @METHODCODE
        WHEN 0 THEN 1 --Cash

        WHEN 1 THEN 2 --Check

        WHEN 2 THEN 4 --Credit Card

        WHEN 3 THEN 8 --Other

      END) > 0;
  select @CREDITGLACCOUNTID = T1.CREDITGLACCOUNTID from PDACCOUNTCODEMAPPING T1 left join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE T2 on (T1.PAYMENTMETHOD & T2.NAMEID) > 0 and T1.OFFICEID = T2.OFFICEID where T1.OFFICEID = 10 and T1.PDACCOUNTSYSTEMID =  @PDACCOUNTSYSTEMID and T2.NAMEID = @NAMEID and (T1.REVENUETYPE & CASE @METHODCODE
        WHEN 0 THEN 1 --Cash

        WHEN 1 THEN 2 --Check

        WHEN 2 THEN 4 --Credit Card

        WHEN 3 THEN 8 --Other

      END) > 0;     

    select @PAYMENTMETHODTYPEID = MT.ID from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE MT where MT.OFFICEID = 10 and MT.NAMEID = @NAMEID;
    select @REVENUETYPEID = MT.ID from dbo.PDACCOUNTCODEMAPREVENUETYPE MT where MT.OFFICEID = 10 and MT.NAMEID = CASE @METHODCODE WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 4 WHEN 3 THEN 8 END;

    declare @SKIPACCOUNTERROR as bit = 0
    if @DEBITGLACCOUNTID is null or @CREDITGLACCOUNTID is null
    begin
        if ((select COUNT(ID) from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and SEGMENTTYPE = 3)) != (select COUNT(a.ID) from dbo.PDACCOUNTSEGMENTMAPPING as a join PDACCOUNTSTRUCTURE as b on a.PDACCOUNTSTRUCTUREID = b.ID where a.ISDEFAULT = 1 and PDACCOUNTSEGMENTVALUEID is not null and b.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        begin
            set @SKIPACCOUNTERROR = 1
            set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", default segment mappings must be defined in order to generate GL distributions for this transaction.'
            if @DEFAULTGLACCOUNTID is null
                raiserror(@ERRORMESSAGE, 13, 1);
            else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
            begin
                set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 2 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 10, @REVENUETYPEID, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

                insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
            end
        end

        select @UNMAPPEDCOMPSEGMENT = dbo.UFN_BANKACCOUNTADJUSTMENT_GETUNMAPPEDCOMPOSITESEGMENT_BYACCOUNTSYSTEM(@PDACCOUNTSYSTEMID)
        if  @UNMAPPEDCOMPSEGMENT = 1
        begin
            set @SKIPACCOUNTERROR = 1
            set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", default composite segment mappings must be defined in order to generate GL distributions for this transaction.'
            if @DEFAULTGLACCOUNTID is null
                raiserror(@ERRORMESSAGE, 13, 1);
            else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
            begin
                set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 2 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 10, @REVENUETYPEID, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

                insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
            end
        end

        select @DEBITGLACCOUNTID = isnull(@DEBITGLACCOUNTID, @DEFAULTGLACCOUNTID),  @CREDITGLACCOUNTID = isnull(@CREDITGLACCOUNTID, @DEFAULTGLACCOUNTID)
    end;

    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
        begin
            set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", the account "' + @BANKGLACCOUNT + '" does not exist. The action could not be completed.'
            if @DEFAULTGLACCOUNTID is null
                raiserror(@ERRORMESSAGE, 13, 1);
            else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE) and @SKIPACCOUNTERROR = 0
            begin
                set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 10, @REVENUETYPEID, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

                insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
            end

            select @BANKGLACCOUNT = @DEFAULTGLACCOUNT, @BANKGLACCOUNTID = @DEFAULTGLACCOUNTID 
        end
    end

  select @PDACCOUNTSEGMENTVALUEID = CREDITPDACCOUNTSEGMENTVALUEID,
                 @GLACCOUNTID = CREDITGLACCOUNTID
  from dbo.PDACCOUNTCODEMAPPING 
  where OFFICEID = 10 and PDACCOUNTSYSTEMID =  @PDACCOUNTSYSTEMID  and 
    (PAYMENTMETHOD & 
      CASE @CORRECTIONTYPECODE
        WHEN 0 THEN 1 --Sales-short

        WHEN 1 THEN 2 --Sales-over

        WHEN 2 THEN 4 --Treasury-short

        WHEN 3 THEN 8 --Treasury-over

      END) > 0 and
    (REVENUETYPE &
      CASE @METHODCODE
        WHEN 0 THEN 1 --Cash

        WHEN 1 THEN 2 --Check

        WHEN 2 THEN 4 --Credit Card

        WHEN 3 THEN 8 --Other

      END) > 0;

    if @PDACCOUNTSEGMENTVALUEID is null and @GLACCOUNTID is null
    begin
        declare @OFFICENAME nvarchar(100);
        declare @TYPE nvarchar(50);
        declare @METHOD nvarchar(50);
        select @OFFICENAME = NAME from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = 10;
        select @TYPE = CORRECTIONTYPE, @METHOD = PAYMENTMETHOD from dbo.BANKACCOUNTDEPOSITCORRECTION where ID = @DEPOSITCORRECTIONID;
        set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", a mapping does not exist for Office: ' + @OFFICENAME + ', Correction type: ' + @TYPE + ', Method: ' + @METHOD
        if @DEFAULTGLACCOUNTID is null
            raiserror(@ERRORMESSAGE, 13, 1);
        else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
        begin
            set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 1 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 10, @REVENUETYPEID, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

            insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
            values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end

        set @GLACCOUNTID = @DEFAULTGLACCOUNTID 
    end

    if @GLACCOUNTID is null
    begin
        select @GLACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@PDACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
        select @GLACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @GLACCOUNT and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;
        if @GLACCOUNTID is null
        begin
            set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", the account "' + @GLACCOUNT + '" does not exist. The action could not be completed.'
            if @DEFAULTGLACCOUNTID is null
                raiserror(@ERRORMESSAGE, 13, 1);
            else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE) and @SKIPACCOUNTERROR = 0
            begin
                set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 10, @REVENUETYPEID, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

                insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
            end

            select @GLACCOUNTID = @DEFAULTGLACCOUNTID, @GLACCOUNT = @DEFAULTGLACCOUNT
        end
    end
    else
        select @GLACCOUNT = ACCOUNTNUMBER from dbo.GLACCOUNT where ID = @GLACCOUNTID;

  --Get the intercurrency account when the transaction currencies are not the same.

  declare @CURRENCYACCOUNTID uniqueidentifier;
    declare @CURRENCYACCOUNTSEGMENTVALUEID uniqueidentifier;
  declare @CURRENCYACCOUNT nvarchar(100);
  if @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID
    begin
    exec dbo.USP_GET_INTERCURRENCY_BALANCINGACCOUNT @PDACCOUNTSYSTEMID, @CURRENCYACCOUNTID output, @CURRENCYACCOUNTSEGMENTVALUEID output, @ERRORMESSAGE output, @MAPPEDVALUES output
        if nullif(@ERRORMESSAGE, '') is not null
            if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE)
                insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        if @CURRENCYACCOUNTID is null
        begin
            select @CURRENCYACCOUNT = dbo.UFN_GLACCOUNT_GETDEFAULTACCOUNTSTRING_BYACCOUNTSYSTEM(@CURRENCYACCOUNTSEGMENTVALUEID,@PDACCOUNTSYSTEMID);
            select @CURRENCYACCOUNTID = ID from dbo.GLACCOUNT where ACCOUNTNUMBER = @CURRENCYACCOUNT and PDACCOUNTSYSTEMID=@PDACCOUNTSYSTEMID;
            if @CURRENCYACCOUNTID is null
            begin
                set @ERRORMESSAGE = 'In account system "' + @AccountSystem + '", the account "' + @CURRENCYACCOUNT + '" does not exist. The action could not be completed.'
                if @DEFAULTGLACCOUNTID is null
                    raiserror(@ERRORMESSAGE, 13, 1);
                else if not exists(select TRANSACTIONID from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0 and ERRORMESSAGE = @ERRORMESSAGE) and @SKIPACCOUNTERROR = 0
                begin
                    set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 3 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 10, @REVENUETYPEID, null, @PAYMENTMETHODTYPEID, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

                    insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                    values (@DEPOSITCORRECTIONID, 101, @ERRORMESSAGE, @MAPPEDVALUES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
                end

                select @CURRENCYACCOUNTID = @DEFAULTGLACCOUNTID, @CURRENCYACCOUNT = @DEFAULTGLACCOUNT
            end
        end
        else
            select @CURRENCYACCOUNT = ACCOUNTNUMBER from dbo.GLACCOUNT where ID = @CURRENCYACCOUNTID;
  end  

  declare @TEMPTRANSACTIONAMOUNT money;
  declare @TEMPBASEAMOUNT money;
  declare @TEMPORGANIZATIONAMOUNT money;
    declare @ORGAMOUNTORIGINCODE tinyint;
    declare @ORGCURRENCYID uniqueidentifier;
    select @ORGAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION;
    select @ORGCURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;

  set @TEMPTRANSACTIONAMOUNT = case when @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID 
                                 then
                                   dbo.UFN_CURRENCY_CONVERT(@TRANSACTIONAMOUNT, @BADTRANSACTIONEXCHANGERATEID)
                                 else
                                   @TRANSACTIONAMOUNT
                               end

  set @TEMPBASEAMOUNT = case when @BATRANSACTIONCURRENCYID <> @BASECURRENCYID 
                          then 
                            dbo.UFN_CURRENCY_CONVERT(@TEMPTRANSACTIONAMOUNT, @BATBASEEXCHANGERATEID)
                          else 
                            @TEMPTRANSACTIONAMOUNT
                        end

  set @TEMPORGANIZATIONAMOUNT = case when @ORGAMOUNTORIGINCODE = 0 
                                  then 
                                    case when @BASECURRENCYID <> @ORGCURRENCYID
                                      then
                                        dbo.UFN_CURRENCY_CONVERT(@TEMPBASEAMOUNT, @BATORGANIZATIONEXCHANGERATEID)
                                      else
                                        @TEMPBASEAMOUNT
                                    end
                                  else
                                    case when @BATRANSACTIONCURRENCYID <> @ORGCURRENCYID
                                      then
                                        dbo.UFN_CURRENCY_CONVERT(@TEMPTRANSACTIONAMOUNT, @BATORGANIZATIONEXCHANGERATEID)
                                      else
                                        @TEMPTRANSACTIONAMOUNT
                                    end
                                end

  if @AMOUNT <> @TEMPBASEAMOUNT or @ORGANIZATIONAMOUNT <> @TEMPORGANIZATIONAMOUNT
    begin
      declare @AMOUNTS UDT_GAINLOSS_AMOUNTS;
      insert into @AMOUNTS
        (ID, NEWBASEAMOUNT, ORIGINALBASEAMOUNT, NEWORGANIZATIONAMOUNT, ORIGINALORGANIZATIONAMOUNT)
      values
        (@DEPOSITCORRECTIONID, @TEMPBASEAMOUNT, @AMOUNT, @TEMPORGANIZATIONAMOUNT, @ORGANIZATIONAMOUNT)

      declare @DEBITSIDE bit;
      set @DEBITSIDE = CASE WHEN @CORRECTIONTYPECODE in (0,2) THEN 0 ELSE 1 END;

      insert into @DISTRIBUTION(ID, GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, ERRORMESSAGE, MAPPEDVALUES)
      exec dbo.USP_GET_GAINLOSSDISTRIBUTIONS
        @AMOUNTS
        ,@DEBITSIDE
        ,@TRANSACTIONCURRENCYID
        ,@PDACCOUNTSYSTEMID

            insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE],[MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
            select distinct @DEPOSITCORRECTIONID, 101, D.ERRORMESSAGE, convert(varchar(max),D.MAPPEDVALUES), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @DISTRIBUTION D
            where nullif(D.ERRORMESSAGE, '') is not null and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @DEPOSITCORRECTIONID and DELETED = 0)

      update @DISTRIBUTION
        set PROJECT = ' '
        ,TRANSACTIONAMOUNT = 0
        ,BASECURRENCYID = @BASECURRENCYID
        ,SYSTEMDISTRIBUTION = 1

      if @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID
        begin
          --Create a copy of the gain loss for the intercurrency balancing account

          insert into @DISTRIBUTION(GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, TRANSACTIONTYPECODE, AMOUNT, ORGANIZATIONAMOUNT, REFERENCE, PROJECT, TRANSACTIONAMOUNT, BASECURRENCYID, SYSTEMDISTRIBUTION)
          select
            NEWID()
            ,@CURRENCYACCOUNT
            ,@CURRENCYACCOUNTID
            ,CASE WHEN T.TRANSACTIONTYPECODE = 0 THEN 1 ELSE 0 END
            ,T.AMOUNT
            ,T.ORGANIZATIONAMOUNT
            ,'Intercurrency Balancing'
            ,T.PROJECT
            ,T.TRANSACTIONAMOUNT
            ,T.BASECURRENCYID
            ,1
          from @DISTRIBUTION T
        end
    end         

  insert into @DISTRIBUTION
    (GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, TRANSACTIONTYPECODE, REFERENCE
    ,TRANSACTIONAMOUNT
    ,ORGANIZATIONAMOUNT 
    ,TRANSACTIONCURRENCYID 
    ,BASECURRENCYID 
    ,BASEEXCHANGERATEID 
    ,ORGANIZATIONEXCHANGERATEID
    ,SYSTEMDISTRIBUTION)
  values
    (NEWID(),
    @GLACCOUNT,
    @GLACCOUNTID,
    @AMOUNT,
    ' ',
    CASE WHEN @CORRECTIONTYPECODE in (0, 2) THEN 0 ELSE 1 END
    ,@REFERENCE
    ,@TRANSACTIONAMOUNT
    ,@ORGANIZATIONAMOUNT 
    ,@TRANSACTIONCURRENCYID 
    ,@BASECURRENCYID 
    ,@BASEEXCHANGERATEID 
    ,@ORGANIZATIONEXCHANGERATEID
    ,0);  

  insert into @DISTRIBUTION
    (GLTRANSACTIONID
    ,ACCOUNT
    ,GLACCOUNTID
    ,AMOUNT
    ,PROJECT
    ,TRANSACTIONTYPECODE
    ,TRANSACTIONAMOUNT
    ,ORGANIZATIONAMOUNT 
    ,TRANSACTIONCURRENCYID 
    ,BASECURRENCYID 
    ,BASEEXCHANGERATEID 
    ,ORGANIZATIONEXCHANGERATEID
    ,REFERENCE
    ,SYSTEMDISTRIBUTION)
  values
    (NEWID(),
    @BANKGLACCOUNT,
    @BANKGLACCOUNTID,
    @TEMPBASEAMOUNT,
    ' ',
    CASE WHEN @CORRECTIONTYPECODE in (0, 2) THEN 1 ELSE 0 END
    ,@TEMPTRANSACTIONAMOUNT
    ,@TEMPORGANIZATIONAMOUNT
    ,@BATRANSACTIONCURRENCYID 
    ,@BASECURRENCYID 
    ,case when @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID then @BATBASEEXCHANGERATEID else @BASEEXCHANGERATEID end
    ,case when @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID then @BATORGANIZATIONEXCHANGERATEID else @ORGANIZATIONEXCHANGERATEID end
    ,@DEPOSITREFERENCE
    ,case when @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID then 1 else 0 end);

  if @TRANSACTIONCURRENCYID <> @BATRANSACTIONCURRENCYID
    begin     
      insert into @DISTRIBUTION
        (GLTRANSACTIONID
        ,ACCOUNT
        ,GLACCOUNTID
        ,AMOUNT
        ,PROJECT
        ,TRANSACTIONTYPECODE
        ,REFERENCE
        ,TRANSACTIONAMOUNT
        ,ORGANIZATIONAMOUNT 
        ,TRANSACTIONCURRENCYID 
        ,BASECURRENCYID 
        ,BASEEXCHANGERATEID 
        ,ORGANIZATIONEXCHANGERATEID
        ,SYSTEMDISTRIBUTION)
      values
        (NEWID(),
        @CURRENCYACCOUNT
        ,@CURRENCYACCOUNTID
        ,@AMOUNT
        ,' '
        ,CASE WHEN @CORRECTIONTYPECODE in (0, 2) THEN 1 ELSE 0 END
        ,'Intercurrency Balancing'
        ,@TRANSACTIONAMOUNT
        ,@ORGANIZATIONAMOUNT 
        ,@TRANSACTIONCURRENCYID
        ,@BASECURRENCYID 
        ,@BASEEXCHANGERATEID
        ,@ORGANIZATIONEXCHANGERATEID
        ,0)

      insert into @DISTRIBUTION
        (GLTRANSACTIONID, ACCOUNT, GLACCOUNTID, AMOUNT, PROJECT, TRANSACTIONTYPECODE
        ,TRANSACTIONAMOUNT
        ,ORGANIZATIONAMOUNT 
        ,TRANSACTIONCURRENCYID 
        ,BASECURRENCYID 
        ,BASEEXCHANGERATEID 
        ,ORGANIZATIONEXCHANGERATEID
        ,REFERENCE
        ,SYSTEMDISTRIBUTION)
      values
(NEWID(),
        @CURRENCYACCOUNT
        ,@CURRENCYACCOUNTID
        ,@TEMPBASEAMOUNT,
        ' ',
        CASE WHEN @CORRECTIONTYPECODE in (0, 2) THEN 0 ELSE 1 END
        ,@TEMPTRANSACTIONAMOUNT
        ,@TEMPORGANIZATIONAMOUNT
        ,@BATRANSACTIONCURRENCYID
        ,@BASECURRENCYID 
        ,@BATBASEEXCHANGERATEID
        ,@BATORGANIZATIONEXCHANGERATEID
        ,'Intercurrency Balancing'
        ,1)    
    end     

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

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