USP_LEDGERACCOUNT_INACTIVE_INUSE

Returns information regarding whether the given account is in use.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@ACCOUNTSTRING	nvarchar(130)	INOUT
@HASACTIVITY	bit	INOUT
@INCONTROLACCOUNT	bit	INOUT
@INBALANCINGENTRY	bit	INOUT
@INCLOSINGREQUIREMENT	bit	INOUT
Definition

Copy


CREATE procedure dbo.USP_LEDGERACCOUNT_INACTIVE_INUSE
(
  @ID uniqueidentifier,
  @ACCOUNTSTRING nvarchar(130)=null output,
  @HASACTIVITY bit = 0 output,
  @INCONTROLACCOUNT bit = 0 output,
  @INBALANCINGENTRY bit = 0 output,
  @INCLOSINGREQUIREMENT bit = 0 output
)
as

set nocount on;

declare @SEGMENTCOUNT int
select @SEGMENTCOUNT = COUNT(ID) from dbo.PDACCOUNTSTRUCTURE
where ELEMENTTYPECODE = 1

select @ACCOUNTSTRING = ACCOUNTSTRING
from dbo.GLACCOUNT where ID = @ID

declare @ELEMENTCOUNT int
select @ELEMENTCOUNT = COUNT(distinct LA.DATAELEMENTID) from
  (select ID LEDGERACCOUNTID,
    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.GLACCOUNT
    where 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)
  ) LA
inner join
  (select
    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.JOURNALENTRY JE
      inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
      left join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
    where FTLI.POSTSTATUSCODE = 1
      and FTLI.DELETEDON is null
      and (FT.ID is null or FT.DELETEDON IS NULL)
  ) 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)
  ) T2
    on LA.DATAELEMENTID = T2.DATAELEMENTID
group by LEDGERACCOUNTID
set @HASACTIVITY = case when @SEGMENTCOUNT = @ELEMENTCOUNT then 1 else 0 end

set @ELEMENTCOUNT = 0
select @ELEMENTCOUNT = COUNT(distinct LA.DATAELEMENTID) from
  (select ID LEDGERACCOUNTID,
    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.GLACCOUNT
    where 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)
  ) LA
inner join
  (select
    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
  ) 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)
  ) T2
    on LA.DATAELEMENTID = T2.DATAELEMENTID
group by LEDGERACCOUNTID
set @INCONTROLACCOUNT = case when @SEGMENTCOUNT = @ELEMENTCOUNT then 1 else 0 end

set @ELEMENTCOUNT = 0
select @ELEMENTCOUNT = COUNT(distinct LA.DATAELEMENTID) from
  (select ID LEDGERACCOUNTID,
    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.GLACCOUNT
    where 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)
  ) LA
inner join
  (select
    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
  ) 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)
  ) T2
    on LA.DATAELEMENTID = T2.DATAELEMENTID
group by LEDGERACCOUNTID
set @INBALANCINGENTRY = case when @SEGMENTCOUNT = @ELEMENTCOUNT then 1 else 0 end

select @INCLOSINGREQUIREMENT = COUNT(1)
from dbo.CLOSINGREQUIREMENT CR
where CR.LEDGERACCOUNTID = @ID

return 0;