USP_LEDGERACCOUNT_INUSE

CREATE procedure dbo.USP_LEDGERACCOUNT_INUSE(@LEDGERACCOUNTID uniqueidentifier)
as
begin
  set nocount on;
  declare @CONTROLINUSE bit = 0;
  declare @BALANCINGENTRYINUSE bit = 0;

  select
    @CONTROLINUSE = 1
  from 
    dbo.CONTROLACCOUNT C
  inner join dbo.GLACCOUNT L on
    (L.DATAELEMENT1ID is null or L.DATAELEMENT1ID = C.DATAELEMENT1ID) and
    (L.DATAELEMENT2ID is null or L.DATAELEMENT2ID = C.DATAELEMENT2ID) and
    (L.DATAELEMENT3ID is null or L.DATAELEMENT3ID = C.DATAELEMENT3ID) and
    (L.DATAELEMENT4ID is null or L.DATAELEMENT4ID = C.DATAELEMENT4ID) and
    (L.DATAELEMENT5ID is null or L.DATAELEMENT5ID = C.DATAELEMENT5ID) and
    (L.DATAELEMENT6ID is null or L.DATAELEMENT6ID = C.DATAELEMENT6ID) and
    (L.DATAELEMENT7ID is null or L.DATAELEMENT7ID = C.DATAELEMENT7ID) and
    (L.DATAELEMENT8ID is null or L.DATAELEMENT8ID = C.DATAELEMENT8ID) and
    (L.DATAELEMENT9ID is null or L.DATAELEMENT9ID = C.DATAELEMENT9ID) and
    (L.DATAELEMENT10ID is null or L.DATAELEMENT10ID = C.DATAELEMENT10ID) and
    (L.DATAELEMENT11ID is null or L.DATAELEMENT11ID = C.DATAELEMENT11ID) and
    (L.DATAELEMENT12ID is null or L.DATAELEMENT12ID = C.DATAELEMENT12ID) and
    (L.DATAELEMENT13ID is null or L.DATAELEMENT13ID = C.DATAELEMENT13ID) and
    (L.DATAELEMENT14ID is null or L.DATAELEMENT14ID = C.DATAELEMENT14ID) and
    (L.DATAELEMENT15ID is null or L.DATAELEMENT15ID = C.DATAELEMENT15ID) and
    (L.DATAELEMENT16ID is null or L.DATAELEMENT16ID = C.DATAELEMENT16ID) and
    (L.DATAELEMENT17ID is null or L.DATAELEMENT17ID = C.DATAELEMENT17ID) and
    (L.DATAELEMENT18ID is null or L.DATAELEMENT18ID = C.DATAELEMENT18ID) and
    (L.DATAELEMENT19ID is null or L.DATAELEMENT19ID = C.DATAELEMENT19ID) and
    (L.DATAELEMENT20ID is null or L.DATAELEMENT20ID = C.DATAELEMENT20ID) and
    (L.DATAELEMENT21ID is null or L.DATAELEMENT21ID = C.DATAELEMENT21ID) and
    (L.DATAELEMENT22ID is null or L.DATAELEMENT22ID = C.DATAELEMENT22ID) and
    (L.DATAELEMENT23ID is null or L.DATAELEMENT23ID = C.DATAELEMENT23ID) and
    (L.DATAELEMENT24ID is null or L.DATAELEMENT24ID = C.DATAELEMENT24ID) and
    (L.DATAELEMENT25ID is null or L.DATAELEMENT25ID = C.DATAELEMENT25ID) and
    (L.DATAELEMENT26ID is null or L.DATAELEMENT26ID = C.DATAELEMENT26ID) and
    (L.DATAELEMENT27ID is null or L.DATAELEMENT27ID = C.DATAELEMENT27ID) and
    (L.DATAELEMENT28ID is null or L.DATAELEMENT28ID = C.DATAELEMENT28ID) and
    (L.DATAELEMENT29ID is null or L.DATAELEMENT29ID = C.DATAELEMENT29ID) and
    (L.DATAELEMENT30ID is null or L.DATAELEMENT30ID = C.DATAELEMENT30ID)
  where L.ID = @LEDGERACCOUNTID

  if @CONTROLINUSE = 1
  begin
    raiserror('ERR_LEDGERACCOUNT_INUSE_CONTROLACCOUNT',13,1)
    return 1;
  end

  select
    @BALANCINGENTRYINUSE = 1
  from 
    dbo.BALANCINGENTRYELEMENT B
  inner join dbo.GLACCOUNT L on
    (L.DATAELEMENT1ID is null or L.DATAELEMENT1ID = B.DATAELEMENT1ID) and
    (L.DATAELEMENT2ID is null or L.DATAELEMENT2ID = B.DATAELEMENT2ID) and
    (L.DATAELEMENT3ID is null or L.DATAELEMENT3ID = B.DATAELEMENT3ID) and
    (L.DATAELEMENT4ID is null or L.DATAELEMENT4ID = B.DATAELEMENT4ID) and
    (L.DATAELEMENT5ID is null or L.DATAELEMENT5ID = B.DATAELEMENT5ID) and
    (L.DATAELEMENT6ID is null or L.DATAELEMENT6ID = B.DATAELEMENT6ID) and
    (L.DATAELEMENT7ID is null or L.DATAELEMENT7ID = B.DATAELEMENT7ID) and
    (L.DATAELEMENT8ID is null or L.DATAELEMENT8ID = B.DATAELEMENT8ID) and
    (L.DATAELEMENT9ID is null or L.DATAELEMENT9ID = B.DATAELEMENT9ID) and
    (L.DATAELEMENT10ID is null or L.DATAELEMENT10ID = B.DATAELEMENT10ID) and
    (L.DATAELEMENT11ID is null or L.DATAELEMENT11ID = B.DATAELEMENT11ID) and
    (L.DATAELEMENT12ID is null or L.DATAELEMENT12ID = B.DATAELEMENT12ID) and
    (L.DATAELEMENT13ID is null or L.DATAELEMENT13ID = B.DATAELEMENT13ID) and
    (L.DATAELEMENT14ID is null or L.DATAELEMENT14ID = B.DATAELEMENT14ID) and
    (L.DATAELEMENT15ID is null or L.DATAELEMENT15ID = B.DATAELEMENT15ID) and
    (L.DATAELEMENT16ID is null or L.DATAELEMENT16ID = B.DATAELEMENT16ID) and
    (L.DATAELEMENT17ID is null or L.DATAELEMENT17ID = B.DATAELEMENT17ID) and
    (L.DATAELEMENT18ID is null or L.DATAELEMENT18ID = B.DATAELEMENT18ID) and
    (L.DATAELEMENT19ID is null or L.DATAELEMENT19ID = B.DATAELEMENT19ID) and
    (L.DATAELEMENT20ID is null or L.DATAELEMENT20ID = B.DATAELEMENT20ID) and
    (L.DATAELEMENT21ID is null or L.DATAELEMENT21ID = B.DATAELEMENT21ID) and
    (L.DATAELEMENT22ID is null or L.DATAELEMENT22ID = B.DATAELEMENT22ID) and
    (L.DATAELEMENT23ID is null or L.DATAELEMENT23ID = B.DATAELEMENT23ID) and
    (L.DATAELEMENT24ID is null or L.DATAELEMENT24ID = B.DATAELEMENT24ID) and
    (L.DATAELEMENT25ID is null or L.DATAELEMENT25ID = B.DATAELEMENT25ID) and
    (L.DATAELEMENT26ID is null or L.DATAELEMENT26ID = B.DATAELEMENT26ID) and
    (L.DATAELEMENT27ID is null or L.DATAELEMENT27ID = B.DATAELEMENT27ID) and
    (L.DATAELEMENT28ID is null or L.DATAELEMENT28ID = B.DATAELEMENT28ID) and
    (L.DATAELEMENT29ID is null or L.DATAELEMENT29ID = B.DATAELEMENT29ID) and
    (L.DATAELEMENT30ID is null or L.DATAELEMENT30ID = B.DATAELEMENT30ID)
  where L.ID = @LEDGERACCOUNTID

  if @BALANCINGENTRYINUSE = 1
  begin
    raiserror('ERR_LEDGERACCOUNT_INUSE_BALANCINGENTRY',13,1)
    return 1;
  end

  return 0;
end