USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @GLDISTRIBUTION xml
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
      set @CURRENTDATE = getdate();

  declare @DEBITAMOUNT money = 0;
  declare @CREDITAMOUNT money = 0;

  declare @DISTRIBUTIONS table (
    ID uniqueidentifier,
    ACCOUNT nvarchar(100),
    GLACCOUNTID uniqueidentifier,
    AMOUNT money,
    DEBITCREDIT nvarchar(50),
    PROJECT nvarchar(100),
    REFERENCE nvarchar(100),
    TRANSACTIONTYPECODE tinyint)

  insert into @DISTRIBUTIONS (ID, ACCOUNT, GLACCOUNTID, AMOUNT, DEBITCREDIT, PROJECT, REFERENCE, TRANSACTIONTYPECODE)
  select
    D.ID, A.ACCOUNTNUMBER, D.GLACCOUNTID, D.AMOUNT, D.DEBITCREDIT, D.PROJECT, D.REFERENCE, D.TRANSACTIONTYPECODE
  from dbo.UFN_BANKACCOUNTDEPOSITCORRECTION_GETGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) D
  inner join dbo.GLACCOUNT A on A.ID = D.GLACCOUNTID;

  select @DEBITAMOUNT = isnull(SUM(AMOUNT), 0) from @DISTRIBUTIONS where TRANSACTIONTYPECODE = 0;
  select @CREDITAMOUNT = isnull(SUM(AMOUNT), 0) from @DISTRIBUTIONS where TRANSACTIONTYPECODE = 1;

    begin try
        if @DEBITAMOUNT <> @CREDITAMOUNT
            raiserror('The sum of the debit accounts must equal the sum of the credit accounts.', 13, 1)

        if (select COUNT(*) from @DISTRIBUTIONS) = 0
            raiserror('At least one GL Distribution is required.', 13, 1)

        if @DEBITAMOUNT <> (select TRANSACTIONAMOUNT from dbo.BANKACCOUNTDEPOSITCORRECTION where ID = @ID)
            raiserror('The distribution amount must equal the correction amount.', 13, 1)

        declare @POSTDATE datetime;
        declare @CORRECTIONTYPECODE tinyint;
        select @POSTDATE = POSTDATE 
            ,@CORRECTIONTYPECODE = CORRECTIONTYPECODE
        from dbo.BANKACCOUNTDEPOSITCORRECTION
        inner join dbo.BANKACCOUNTTRANSACTION ON BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
        where BANKACCOUNTDEPOSITCORRECTION.ID = @ID

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
        declare @DEPOSITBASEEXCHANGERATEID uniqueidentifier;
        declare @DEPOSITORGANIZATIONEXCHANGERATEID uniqueidentifier;

    select @TRANSACTIONCURRENCYID = BAD.TRANSACTIONCURRENCYID
      ,@BASECURRENCYID = D.BASECURRENCYID
      ,@BASEEXCHANGERATEID = C.BASEEXCHANGERATEID
      ,@ORGANIZATIONEXCHANGERATEID = C.ORGANIZATIONEXCHANGERATEID
            ,@DEPOSITBASEEXCHANGERATEID = CASE WHEN BAD.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID THEN D.BASEEXCHANGERATEID ELSE C.BASEEXCHANGERATEID END
            ,@DEPOSITORGANIZATIONEXCHANGERATEID = CASE WHEN BAD.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID THEN D.ORGANIZATIONEXCHANGERATEID ELSE C.ORGANIZATIONEXCHANGERATEID END
    from dbo.BANKACCOUNTDEPOSITCORRECTION C
    inner join dbo.BANKACCOUNTTRANSACTION D ON D.ID = C.DEPOSITID
    inner join dbo.BANKACCOUNTDEPOSIT BAD on BAD.ID = D.ID
    inner join dbo.BANKACCOUNT BA on BA.ID = D.BANKACCOUNTID
    where C.ID = @ID

    select @GLDISTRIBUTION = dbo.UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML
    (
      @GLDISTRIBUTION
      ,@BASECURRENCYID
            ,CASE WHEN @CORRECTIONTYPECODE in (0, 2) THEN @ORGANIZATIONEXCHANGERATEID ELSE @DEPOSITORGANIZATIONEXCHANGERATEID END
            ,@TRANSACTIONCURRENCYID
            ,CASE WHEN @CORRECTIONTYPECODE in (0, 2) THEN @BASEEXCHANGERATEID ELSE @DEPOSITBASEEXCHANGERATEID END
            ,CASE WHEN @CORRECTIONTYPECODE in (1, 3) THEN @ORGANIZATIONEXCHANGERATEID ELSE @DEPOSITORGANIZATIONEXCHANGERATEID END
            ,CASE WHEN @CORRECTIONTYPECODE in (1, 3) THEN @BASEEXCHANGERATEID ELSE @DEPOSITBASEEXCHANGERATEID END
    )

        exec dbo.USP_BANKACCOUNTDEPOSITCORRECTION_GLDISTRIBUTION_MULTICURRENCY_UPDATEFROMXML @ID, @GLDISTRIBUTION, @POSTDATE, @CHANGEAGENTID, @CURRENTDATE
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
    return 1
    end catch

  return 0;