USP_CONTROLACCOUNT_VALIDATE

CREATE procedure dbo.USP_CONTROLACCOUNT_VALIDATE @ID uniqueidentifier, @DATAELEMENTS xml, @ALLOWJOURNALENTRY bit, @ACCOUNTTYPECODE tinyint, @BANKACCOUNTID uniqueidentifier
as
begin
  declare @COUNT int
  set nocount on;

  declare @PDACCOUNTSYSTEMID uniqueidentifier
  set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

  declare @ACCOUNTCODESTRUCTUREID uniqueidentifier
  set @ACCOUNTCODESTRUCTUREID = (select ID from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0 and ELEMENTDEFINITIONCODE = 1)

  declare @ERRORMSG nvarchar(4000), @CLOSINGELEMENTID uniqueidentifier, @CLOSINGELEMENTVALUEID uniqueidentifier, @CLOSINGELEMENTNAME nvarchar(100)
  select @CLOSINGELEMENTID = ID, @CLOSINGELEMENTNAME = DESCRIPTION from dbo.PDACCOUNTSTRUCTURE where CLOSINGELEMENT = 1 and ISBASICGL = 0 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

  create table #DE (ID uniqueidentifier, DATAELEMENTID uniqueidentifier, ACCOUNTSTRUCTUREID uniqueidentifier);
  insert #DE select ID, DATAELEMENTID, ACCOUNTSTRUCTUREID from
    dbo.UFN_CONTROLACCOUNT_GETDATAELEMENTS_FROMITEMLISTXML(@DATAELEMENTS);

  select @CLOSINGELEMENTVALUEID = DATAELEMENTID from #DE where ACCOUNTSTRUCTUREID = @CLOSINGELEMENTID

  declare @SEPARATOR nvarchar(1);
  select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0;

  --make sure at least one ledger account exists that matches the subsidiary ledger account    

  declare @ACCOUNTSTRING nvarchar(130)
  set @ACCOUNTSTRING =
  (select
      isnull(SHORTDESCRIPTION+@SEPARATOR,'%'+@SEPARATOR)
  from 
    dbo.PDACCOUNTSTRUCTURE
    left join #DE T1 on T1.ACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
    left join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
  where
    PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1
    and ISBASICGL = 0
    and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
  order by
      PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  for xml path(''),type).value('.','nvarchar(130)')
  set @ACCOUNTSTRING = substring(@ACCOUNTSTRING, 1, len(@ACCOUNTSTRING)-1)

  if (select count(1) from dbo.GLACCOUNT where ACCOUNTSTRING like @ACCOUNTSTRING) = 0
  begin
    raiserror('ERR_CONTROLACCOUNT_ACCOUNTDOESNOTEXIST',13,1);
    return 1;
  end

  -- AP Summary and AP discount

  if @ACCOUNTTYPECODE =1 or @ACCOUNTTYPECODE = 2
  begin
    --Only one subsidiary ledger account can have the closing element masked

    if not exists (select ID from #DE where ACCOUNTSTRUCTUREID = @CLOSINGELEMENTID)
    begin
      if exists (select ID from dbo.CONTROLACCOUNT where ID <> @ID and ACCOUNTTYPECODE = @ACCOUNTTYPECODE and SYSTEMID = '777AE8A1-67F4-474F-8474-E16F41EE0EC4' and ID not in
      (select distinct unPvt.ID from
      (select ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
      from dbo.CONTROLACCOUNT
      where SYSTEMID = '777AE8A1-67F4-474F-8474-E16F41EE0EC4' and ACCOUNTTYPECODE = @ACCOUNTTYPECODE 
      and ID <> @ID) p
      unpivot
      (DATAELEMENTID for dColumn in
          (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)) as unPvt
      inner join dbo.PDACCOUNTSEGMENTVALUE on unPvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      where PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = @CLOSINGELEMENTID))
      begin
        raiserror('ERR_CONTROLACCOUNT_CLOSINGELEMENTREQUIRED',13,1);
        return 1;
      end
    end

    --If account code is closing element, only two Payables summary accounts allowed, one of which needs to be "Allow journal entry"

    if @CLOSINGELEMENTID = @ACCOUNTCODESTRUCTUREID
    begin
      select @COUNT = COUNT(1) from dbo.CONTROLACCOUNT where SYSTEMID = '777AE8A1-67F4-474F-8474-E16F41EE0EC4' and ID <> @ID and ACCOUNTTYPECODE = @ACCOUNTTYPECODE
      if @COUNT >= 2
      begin
        raiserror('ERR_CONTROLACCOUNT_PAYABLES_MAX2',13,1);
        return 1;
      end
      if @COUNT = 1
      begin
        if (select COUNT(1) from dbo.CONTROLACCOUNT where SYSTEMID = '777AE8A1-67F4-474F-8474-E16F41EE0EC4' and ID <> @ID and ALLOWJOURNALENTRY = @ALLOWJOURNALENTRY and ACCOUNTTYPECODE = @ACCOUNTTYPECODE) >= 1
        begin
          raiserror('ERR_CONTROLACCOUNT_ONEALLOWJOURNALENTRY',13,1);
          return 1;
        end
      end
    end

    --a closing value can only be used once

    set @COUNT = 0
    select @COUNT = COUNT(1) from
      (select ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
      from dbo.CONTROLACCOUNT
      where SYSTEMID = '777AE8A1-67F4-474F-8474-E16F41EE0EC4' and ACCOUNTTYPECODE = @ACCOUNTTYPECODE 
      and ID <> @ID) p
      unpivot
      (DATAELEMENTID for dColumn in
          (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)) as unPvt
     where unPvt.DATAELEMENTID = @CLOSINGELEMENTVALUEID
     if @COUNT > 0
     begin
      raiserror('ERR_CONTROLACCOUNT_CLOSINGELEMENT_ONEALLOWJOURNALENTRY',13,1);
      return 1;
     end
  end

  -- Treasury

  if @ACCOUNTTYPECODE =3
  begin     
    --Only one subsidiary ledger account can have the closing element masked per bank account

    if not exists (select ID from #DE where ACCOUNTSTRUCTUREID = @CLOSINGELEMENTID)
    begin
      if exists (select CA.ID from dbo.CONTROLACCOUNT CA join dbo.CONTROLACCOUNTTREASURY CAT on CAT.ID = CA.ID where CAT.BANKACCOUNTID = @BANKACCOUNTID and CA.ID <> @ID and ACCOUNTTYPECODE = @ACCOUNTTYPECODE and SYSTEMID = 'C276668E-26D1-4714-95B8-C0BC18E308EF' and CA.ID not in
      (select distinct unPvt.ID from
      (select CONTROLACCOUNT.ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
      from dbo.CONTROLACCOUNT
      join dbo.CONTROLACCOUNTTREASURY on CONTROLACCOUNTTREASURY.ID = CONTROLACCOUNT.ID
      where SYSTEMID = 'C276668E-26D1-4714-95B8-C0BC18E308EF' and ACCOUNTTYPECODE = @ACCOUNTTYPECODE 
      and CONTROLACCOUNTTREASURY.BANKACCOUNTID = @BANKACCOUNTID and CONTROLACCOUNT.ID <> @ID) p
      unpivot
      (DATAELEMENTID for dColumn in
          (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)) as unPvt
      inner join dbo.PDACCOUNTSEGMENTVALUE on unPvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      where PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = @CLOSINGELEMENTID))
      if exists (select CA.ID from dbo.CONTROLACCOUNT CA join dbo.CONTROLACCOUNTTREASURY CAT on CAT.ID = CA.ID where CAT.BANKACCOUNTID = @BANKACCOUNTID and CA.ID <> @ID and ACCOUNTTYPECODE = 3)
      begin
        raiserror('ERR_CONTROLACCOUNT_CLOSINGELEMENTREQUIRED',13,1);
        return 1;
      end
    end

    --a closing value can only be used once per bank account

    set @COUNT = 0
    select @COUNT = COUNT(1) from
      (select CA.ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
      from dbo.CONTROLACCOUNT CA join dbo.CONTROLACCOUNTTREASURY CAT on CAT.ID = CA.ID 
      where CAT.BANKACCOUNTID = @BANKACCOUNTID and SYSTEMID = 'C276668E-26D1-4714-95B8-C0BC18E308EF' 
      and ACCOUNTTYPECODE = 3 
      and CA.ID <> @ID) p
      unpivot
      (DATAELEMENTID for dColumn in
          (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)) as unPvt
     where unPvt.DATAELEMENTID = @CLOSINGELEMENTVALUEID
     if @COUNT > 0
     begin
      raiserror('ERR_CONTROLACCOUNT_CLOSINGELEMENT_ONEALLOWJOURNALENTRY',13,1);
      return 1;
     end

    --If account code is closing element, only two treasury accounts allowed per bank account, one of which needs to be "Allow journal entry"

    if @CLOSINGELEMENTID = @ACCOUNTCODESTRUCTUREID
    begin
      select @COUNT = COUNT(1) from dbo.CONTROLACCOUNT CA join dbo.CONTROLACCOUNTTREASURY CAT on CAT.ID = CA.ID where CAT.BANKACCOUNTID = @BANKACCOUNTID and SYSTEMID = 'C276668E-26D1-4714-95B8-C0BC18E308EF' and CA.ID <> @ID and ACCOUNTTYPECODE = 3
      if @COUNT >= 2
      begin
        raiserror('ERR_CONTROLACCOUNT_TREASURY_MAX2',13,1);
        return 1;
      end
      if @COUNT = 1
      begin
        if (select COUNT(1) from dbo.CONTROLACCOUNT CA join dbo.CONTROLACCOUNTTREASURY CAT on CAT.ID = CA.ID where CAT.BANKACCOUNTID = @BANKACCOUNTID and SYSTEMID = 'C276668E-26D1-4714-95B8-C0BC18E308EF' and CA.ID <> @ID and ALLOWJOURNALENTRY = @ALLOWJOURNALENTRY and ACCOUNTTYPECODE = 3) >= 1
 begin
          raiserror('ERR_CONTROLACCOUNT_ONEALLOWJOURNALENTRY',13,1);
          return 1;
        end
      end
      -- the same treasury account cannot be used on more than one bank account

      if (select COUNT(1) from dbo.CONTROLACCOUNT CA 
            join dbo.PDACCOUNTSEGMENTVALUE DE on CA.DATAELEMENT1ID = DE.ID  
            join dbo.CONTROLACCOUNTTREASURY CAT on CAT.ID = CA.ID 
            where CAT.BANKACCOUNTID <> @BANKACCOUNTID and SYSTEMID = 'C276668E-26D1-4714-95B8-C0BC18E308EF' 
            and DE.ID = @CLOSINGELEMENTVALUEID--CA.DATAELEMENT1ID

            and CA.ID <> @ID and ACCOUNTTYPECODE = 3) > 0
      begin
        raiserror('ERR_CONTROLACCOUNT_ACCOUNTUNIQUE',13,1);
        return 1;
      end
    end
  end

  --If account is used in uposted batch, must have "Allow use in journal entry" checked

  if  @ALLOWJOURNALENTRY = 0 and exists (select 1 from 
    dbo.JOURNALENTRY JE
    cross apply (
      select count(1) as cnt from
      (select DATAELEMENTID from dbo.UFN_JOURNALENTRYELEMENTS(je.id, 0)
      except
      select dataelementid from #DE) el1
    ) EL
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FLI on JE.FINANCIALTRANSACTIONLINEITEMID = FLI.ID
    where FLI.POSTSTATUSCODE = 1 and cnt = 0 )
  begin
    raiserror('ERR_CONTROLACCOUNT_UNCLOSEDBATCH',13,1);
    return 1;
  end

  drop table #DE
  return 0;
end