USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTTRANSACTIONGLDISTRIBUTION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BANKACCOUNTTRANSACTIONGLDISTRIBUTION (
    @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;

    select @ID = CASE WHEN A.ID is null or A.ISORIGINALADJUSTMENT = 1 THEN BAT.ID ELSE A.TRANSFERADJUSTMENTID END
    from dbo.BANKACCOUNTTRANSACTION BAT
    left outer join dbo.BANKACCOUNTADJUSTMENT A on BAT.ID = A.ID
    where BAT.ID = @ID

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

  insert into @DISTRIBUTIONS 
  (
   ID, 
   TRANSACTIONTYPECODE, 
   ACCOUNT, 
   GLACCOUNTID, 
   PROJECT, 
   AMOUNT,    
   REFERENCE,
   TRANSACTIONCURRENCYID,
   GLTRANSACTIONID
  )
  select
    D.ID, 
    D.TRANSACTIONTYPECODE,
    D.ACCOUNT, 
    D.GLACCOUNTID, 
    D.PROJECT, 
    D.AMOUNT,    
    D.REFERENCE,
    D.TRANSACTIONCURRENCYID,
    D.GLTRANSACTIONID    
  from dbo.UFN_BANKACCOUNTTRANSACTION_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;

  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.BANKACCOUNTTRANSACTION where ID = @ID)
    raiserror('The distribution amount must equal the transaction amount.', 13, 1)

  declare @POSTDATE datetime;
  select @POSTDATE = POSTDATE from dbo.BANKACCOUNTTRANSACTION where ID = @ID

  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @BASEEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier; 
  declare @TRANSFERBASEEXCHANGERATEID uniqueidentifier;
  declare @TRANSFERORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @TYPECODE tinyint;

  select 
    @TYPECODE = BAT.TRANSACTIONTYPECODE,
    @TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID,
    @BASECURRENCYID = BAT.BASECURRENCYID,
    @BASEEXCHANGERATEID = BAT.BASEEXCHANGERATEID,
    @ORGANIZATIONEXCHANGERATEID = BAT.ORGANIZATIONEXCHANGERATEID
    ,@TRANSFERBASEEXCHANGERATEID = CASE WHEN BA1.TRANSACTIONCURRENCYID is not null and BA1.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID THEN BAT1.BASEEXCHANGERATEID ELSE BAT.BASEEXCHANGERATEID END
    ,@TRANSFERORGANIZATIONEXCHANGERATEID = CASE WHEN BA1.TRANSACTIONCURRENCYID is not null and BA1.TRANSACTIONCURRENCYID = BA.TRANSACTIONCURRENCYID THEN BAT1.ORGANIZATIONEXCHANGERATEID ELSE BAT.ORGANIZATIONEXCHANGERATEID END
  from dbo.BANKACCOUNTTRANSACTION as BAT 
  left outer join dbo.BANKACCOUNTADJUSTMENT A on BAT.ID = A.ID
  left outer join dbo.BANKACCOUNTTRANSACTION BAT1 on A.TRANSFERADJUSTMENTID = BAT1.ID
  inner join dbo.BANKACCOUNT as BA on BA.ID = BAT.BANKACCOUNTID
  left outer join dbo.BANKACCOUNT BA1 on BA1.ID = BAT1.BANKACCOUNTID
    where BAT.ID = @ID;

  select @GLDISTRIBUTION = dbo.UFN_GLDISTRIBUTION_CONVERTAMOUNTSINXML
  (
    @GLDISTRIBUTION
    ,@BASECURRENCYID
      ,CASE WHEN @TYPECODE in (16, 33) THEN @ORGANIZATIONEXCHANGERATEID ELSE @TRANSFERORGANIZATIONEXCHANGERATEID END
      ,@TRANSACTIONCURRENCYID
      ,CASE WHEN @TYPECODE in (16, 33) THEN @BASEEXCHANGERATEID ELSE @TRANSFERBASEEXCHANGERATEID END
      ,CASE WHEN @TYPECODE in (17, 32) THEN @ORGANIZATIONEXCHANGERATEID ELSE @TRANSFERORGANIZATIONEXCHANGERATEID END
      ,CASE WHEN @TYPECODE in (17, 32) THEN @BASEEXCHANGERATEID ELSE @TRANSFERBASEEXCHANGERATEID END
  )

  exec dbo.USP_BANKACCOUNTTRANSACTION_GLDISTRIBUTION_MULTICURRENCY_UPDATEFROMXML @ID, @GLDISTRIBUTION, @POSTDATE, @CHANGEAGENTID, @CURRENTDATE

return 0;