USP_BALANCINGENTRY_VALIDATE

CREATE procedure dbo.USP_BALANCINGENTRY_VALIDATE(
@DEBITXML xml,@CREDITXML xml,@DEBITTOXML xml,@CREDITTOXML xml,@BALANCINGENTRYID uniqueidentifier)
as
begin

  declare @TempTbl table (
     [BALANCINGENTRYTYPECODE] tinyint,
     [DATAELEMENTID] uniqueidentifier,
     [ID] uniqueidentifier)

  insert into @TempTbl select 
      1,
      [DATAELEMENTID],
      [ID] 
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSDEBITFROM_FROMITEMLISTXML(@DEBITXML)

  insert into @TempTbl select 
      2,
      [DATAELEMENTID],
      [ID] 
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSCREDITFROM_FROMITEMLISTXML(@CREDITXML)

  insert into @TempTbl select 
      3,
      [DATAELEMENTID],
      [ID] 
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSDEBITTO_FROMITEMLISTXML(@DEBITTOXML)

  insert into @TempTbl select 
      4,
      [DATAELEMENTID],
      [ID] 
  from dbo.UFN_BALANCINGENTRY_GETELEMENTSCREDITTO_FROMITEMLISTXML(@CREDITTOXML)

  declare @COUNT int

  select @Count =  COUNT(distinct(a.DATAELEMENTID))
  from @TempTbl as a join 
    dbo.PDACCOUNTSEGMENTVALUE as b on a.DATAELEMENTID = b.ID
    join dbo.PDACCOUNTSTRUCTURE as c on c.ID = b.PDACCOUNTSTRUCTUREID
  where CLOSINGELEMENT = 1 and BALANCINGENTRYTYPECODE in (1,2) 

 if @COUNT <> 2
     begin
        raiserror('ERR_UNIQUE_CLOSINGELEMENT',13,1);
        return 1;
    end

  --make sure this isn't a duplicate

  declare @TempTbl2 table (
   [BALANCINGENTRYTYPECODE] tinyint,
   [BALANCINGENTRYID] uniqueidentifier,
   [DATAELEMENTID] uniqueidentifier)

  declare @DEBITELEMENTCOUNT int
  declare @CREDITELEMENTCOUNT int
  set @DEBITELEMENTCOUNT = (select count(1) from @TempTbl where BALANCINGENTRYTYPECODE = 1)
  set @CREDITELEMENTCOUNT = (select count(1) from @TempTbl where BALANCINGENTRYTYPECODE = 2)

  insert into @TempTbl2 (BALANCINGENTRYTYPECODE, BALANCINGENTRYID, DATAELEMENTID)
  select unPvt.BALANCINGENTRYTYPECODE, unPvt.BALANCINGENTRYID, unPvt.DATAELEMENTID
  from
      (select BALANCINGENTRYTYPECODE, BALANCINGENTRYID, 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.BALANCINGENTRYELEMENT 
    where BALANCINGENTRYID <> @BALANCINGENTRYID
    and BALANCINGENTRYTYPECODE in (1,2)) 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

  --remove balancing entries that don't have the same number of elements defined as the debit/credit being saved

  delete from @TempTbl2
  where BALANCINGENTRYID in 
    (select BALANCINGENTRYID from (select BALANCINGENTRYID, count(1) as ElementCount
    from @TempTbl2
    where BALANCINGENTRYTYPECODE = 1
    group by BALANCINGENTRYID) t
    where t.ElementCount <> @DEBITELEMENTCOUNT)
  or BALANCINGENTRYID in
    (select BALANCINGENTRYID from (select BALANCINGENTRYID, count(1) as ElementCount
    from @TempTbl2
    where BALANCINGENTRYTYPECODE = 2
    group by BALANCINGENTRYID) t
    where t.ElementCount <> @CREDITELEMENTCOUNT)

  if exists (select 1 
    from
    (select BALANCINGENTRYID from 
      (select BALANCINGENTRYID, count(1) as MatchingDataElementCount
       from @TempTbl2
       where DATAELEMENTID in (select DATAELEMENTID from @TempTbl where BALANCINGENTRYTYPECODE = 1)
       and BALANCINGENTRYTYPECODE = 1
       group by BALANCINGENTRYID) t
       where MatchingDataElementCount = @DEBITELEMENTCOUNT) debit
    inner join 
    (select BALANCINGENTRYID from 
      (select BALANCINGENTRYID, count(1) as MatchingDataElementCount
       from @TempTbl2
       where DATAELEMENTID in (select DATAELEMENTID from @TempTbl where BALANCINGENTRYTYPECODE = 2)
       and BALANCINGENTRYTYPECODE = 2
       group by BALANCINGENTRYID) t
       where MatchingDataElementCount = @CREDITELEMENTCOUNT) credit
    on debit.BALANCINGENTRYID = credit.BALANCINGENTRYID)
  begin
        raiserror('ERR_UNIQUE_BALANCINGENTRY',13,1);
        return 1;      
  end

  declare @SEPARATOR nvarchar(1);
  select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE;

  declare @ACCOUNTSTRUCTURECOUNT int
  select @ACCOUNTSTRUCTURECOUNT = count(1) from dbo.PDACCOUNTSTRUCTURE where ELEMENTTYPECODE = 1
  --if an account is fully defined make sure that account exists

  if @ACCOUNTSTRUCTURECOUNT = 
      (select count(1) from @TempTbl T1
      inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
      where PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and T1.BALANCINGENTRYTYPECODE = 1)
  begin
    declare @DEBITACCOUNTSTRING nvarchar(130)
    set @DEBITACCOUNTSTRING =
    (select
      coalesce(SHORTDESCRIPTION+@SEPARATOR, '')
    from 
      @TempTbl T1
      inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
    where
      PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and T1.BALANCINGENTRYTYPECODE = 1
    order by
      PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
    for xml path(''),type).value('.','nvarchar(130)')
    set @DEBITACCOUNTSTRING = substring(@DEBITACCOUNTSTRING, 1, len(@DEBITACCOUNTSTRING)-1)

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

  --if an account is fully defined make sure that account exists

  if @ACCOUNTSTRUCTURECOUNT = 
      (select count(1) from @TempTbl T1
      inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
      where PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and T1.BALANCINGENTRYTYPECODE = 2)
  begin
    declare @CREDITACCOUNTSTRING nvarchar(130)
    set @CREDITACCOUNTSTRING =
    (select
      coalesce(SHORTDESCRIPTION+@SEPARATOR, '')
    from 
      @TempTbl T1
      inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
    where
      PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and T1.BALANCINGENTRYTYPECODE = 2
    order by
      PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
    for xml path(''),type).value('.','nvarchar(130)')
    set @CREDITACCOUNTSTRING = substring(@CREDITACCOUNTSTRING, 1, len(@CREDITACCOUNTSTRING)-1)

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

  --make sure the account specified exists

  declare @DEBITTOACCOUNTSTRING nvarchar(130)
  set @DEBITTOACCOUNTSTRING =
  (select
    coalesce(SHORTDESCRIPTION+@SEPARATOR, '')
  from 
    @TempTbl T1
    inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  where
    PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and T1.BALANCINGENTRYTYPECODE = 3
  order by
    PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  for xml path(''),type).value('.','nvarchar(130)')
  set @DEBITTOACCOUNTSTRING = substring(@DEBITTOACCOUNTSTRING, 1, len(@DEBITTOACCOUNTSTRING)-1)

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

  --make sure the account specified exists

  declare @CREDITTOACCOUNTSTRING nvarchar(130)
  set @CREDITTOACCOUNTSTRING =
  (select
    coalesce(SHORTDESCRIPTION+@SEPARATOR, '')
  from 
    @TempTbl T1
    inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  where
    PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and T1.BALANCINGENTRYTYPECODE = 4
  order by
    PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE
  for xml path(''),type).value('.','nvarchar(130)')
  set @CREDITTOACCOUNTSTRING = substring(@CREDITTOACCOUNTSTRING, 1, len(@CREDITTOACCOUNTSTRING)-1)

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

  -- Verify account codes are in the same category

  declare @DEBITCATEGORY tinyint, @CREDITCATEGORY tinyint, @VALID bit
  select
    @DEBITCATEGORY = CATEGORYCODE
  from
    dbo.UFN_BALANCINGENTRY_GETELEMENTSDEBITTO_FROMITEMLISTXML(@DEBITTOXML) T1
    inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID and PDACCOUNTSEGMENTVALUE.TYPECODE=1
    inner join dbo.ACCOUNTCODE on ACCOUNTCODE.ID = PDACCOUNTSEGMENTVALUE.ID

  select
    @CREDITCATEGORY = CATEGORYCODE
  from
    dbo.UFN_BALANCINGENTRY_GETELEMENTSCREDITTO_FROMITEMLISTXML(@CREDITTOXML) T1
    inner join dbo.PDACCOUNTSEGMENTVALUE on T1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID and PDACCOUNTSEGMENTVALUE.TYPECODE=1
    inner join dbo.ACCOUNTCODE on ACCOUNTCODE.ID = PDACCOUNTSEGMENTVALUE.ID

  set @VALID = 
  case 
    when @DEBITCATEGORY in (1,2,3) then 
      case when @CREDITCATEGORY in (1,2,3) then 1 else 0 end
    when @DEBITCATEGORY in (4,5) then
      case when @CREDITCATEGORY in (4,5) then 1 else 0 end
    else 0
  end

  if @VALID <> 1
  begin
    raiserror('ERR_BALANCINGENTRY_ACCOUNTCATEGORIES',13,1);
    return 1;
  end

  return 0;
end