USP_CONTROLACCOUNT_INUSE

CREATE procedure dbo.USP_CONTROLACCOUNT_INUSE(@CONTROLACCOUNTID uniqueidentifier)
as
begin
  set nocount on;
  declare @CONTROLINUSE bit = 0;

  select
    @CONTROLINUSE = 1
  from dbo.CONTROLACCOUNT C
  inner join dbo.JOURNALENTRY J on
    (C.DATAELEMENT1ID is null or J.DATAELEMENT1ID = C.DATAELEMENT1ID) and
    (C.DATAELEMENT2ID is null or J.DATAELEMENT2ID = C.DATAELEMENT2ID) and
    (C.DATAELEMENT3ID is null or J.DATAELEMENT3ID = C.DATAELEMENT3ID) and
    (C.DATAELEMENT4ID is null or J.DATAELEMENT4ID = C.DATAELEMENT4ID) and
    (C.DATAELEMENT5ID is null or J.DATAELEMENT5ID = C.DATAELEMENT5ID) and
    (C.DATAELEMENT6ID is null or J.DATAELEMENT6ID = C.DATAELEMENT6ID) and
    (C.DATAELEMENT7ID is null or J.DATAELEMENT7ID = C.DATAELEMENT7ID) and
    (C.DATAELEMENT8ID is null or J.DATAELEMENT8ID = C.DATAELEMENT8ID) and
    (C.DATAELEMENT9ID is null or J.DATAELEMENT9ID = C.DATAELEMENT9ID) and
    (C.DATAELEMENT10ID is null or J.DATAELEMENT10ID = C.DATAELEMENT10ID) and
    (C.DATAELEMENT11ID is null or J.DATAELEMENT11ID = C.DATAELEMENT11ID) and
    (C.DATAELEMENT12ID is null or J.DATAELEMENT12ID = C.DATAELEMENT12ID) and
    (C.DATAELEMENT13ID is null or J.DATAELEMENT13ID = C.DATAELEMENT13ID) and
    (C.DATAELEMENT14ID is null or J.DATAELEMENT14ID = C.DATAELEMENT14ID) and
    (C.DATAELEMENT15ID is null or J.DATAELEMENT15ID = C.DATAELEMENT15ID) and
    (C.DATAELEMENT16ID is null or J.DATAELEMENT16ID = C.DATAELEMENT16ID) and
    (C.DATAELEMENT17ID is null or J.DATAELEMENT17ID = C.DATAELEMENT17ID) and
    (C.DATAELEMENT18ID is null or J.DATAELEMENT18ID = C.DATAELEMENT18ID) and
    (C.DATAELEMENT19ID is null or J.DATAELEMENT19ID = C.DATAELEMENT19ID) and
    (C.DATAELEMENT20ID is null or J.DATAELEMENT20ID = C.DATAELEMENT20ID) and
    (C.DATAELEMENT21ID is null or J.DATAELEMENT21ID = C.DATAELEMENT21ID) and
    (C.DATAELEMENT22ID is null or J.DATAELEMENT22ID = C.DATAELEMENT22ID) and
    (C.DATAELEMENT23ID is null or J.DATAELEMENT23ID = C.DATAELEMENT23ID) and
    (C.DATAELEMENT24ID is null or J.DATAELEMENT24ID = C.DATAELEMENT24ID) and
    (C.DATAELEMENT25ID is null or J.DATAELEMENT25ID = C.DATAELEMENT25ID) and
    (C.DATAELEMENT26ID is null or J.DATAELEMENT26ID = C.DATAELEMENT26ID) and
    (C.DATAELEMENT27ID is null or J.DATAELEMENT27ID = C.DATAELEMENT27ID) and
    (C.DATAELEMENT28ID is null or J.DATAELEMENT28ID = C.DATAELEMENT28ID) and
    (C.DATAELEMENT29ID is null or J.DATAELEMENT29ID = C.DATAELEMENT29ID) and
    (C.DATAELEMENT30ID is null or J.DATAELEMENT30ID = C.DATAELEMENT30ID)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on J.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
  inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
  where C.ID = @CONTROLACCOUNTID
  and FT.DELETEDON IS NULL
  and FTLI.POSTSTATUSCODE = 1

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

  return 0;        
end