USP_ACCOUNTINGELEMENT_INUSE

Returns information about whether an accounting element is used in transactions, ledger accounts, subledger accounts, or balancing entries.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@ELEMENTDEFINITION	nvarchar(100)	INOUT
@ELEMENTVALUE	nvarchar(100)	INOUT
@HASACTIVITY	bit	INOUT
@INLEDGERACCOUNT	bit	INOUT
@INCONTROLACCOUNT	bit	INOUT
@INBALANCINGENTRY	bit	INOUT
@INCLOSINGREQUIREMENT	bit	INOUT
Definition

Copy


CREATE procedure dbo.USP_ACCOUNTINGELEMENT_INUSE
(
    @ID uniqueidentifier,
  @ELEMENTDEFINITION nvarchar(100) = null output,
  @ELEMENTVALUE nvarchar(100) = null output,
  @HASACTIVITY bit = null output,
  @INLEDGERACCOUNT bit = null output,
  @INCONTROLACCOUNT bit = null output,
  @INBALANCINGENTRY bit = null output,
  @INCLOSINGREQUIREMENT bit = null output
)
as
    set nocount on;

  select @ELEMENTDEFINITION = PDACCOUNTSTRUCTURE.DESCRIPTION,
    @ELEMENTVALUE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
  from dbo.PDACCOUNTSTRUCTURE 
    inner join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  where PDACCOUNTSEGMENTVALUE.ID = @ID

  -- Journal entry

    select @HASACTIVITY = 1
  from
    (select 
     JE.DATAELEMENT1ID, JE.DATAELEMENT2ID, JE.DATAELEMENT3ID, JE.DATAELEMENT4ID, JE.DATAELEMENT5ID
        ,JE.DATAELEMENT6ID, JE.DATAELEMENT7ID, JE.DATAELEMENT8ID, JE.DATAELEMENT9ID, JE.DATAELEMENT10ID
        ,JE.DATAELEMENT11ID, JE.DATAELEMENT12ID, JE.DATAELEMENT13ID, JE.DATAELEMENT14ID, JE.DATAELEMENT15ID
        ,JE.DATAELEMENT16ID, JE.DATAELEMENT17ID, JE.DATAELEMENT18ID, JE.DATAELEMENT19ID, JE.DATAELEMENT20ID
        ,JE.DATAELEMENT21ID, JE.DATAELEMENT22ID, JE.DATAELEMENT23ID, JE.DATAELEMENT24ID, JE.DATAELEMENT25ID
        ,JE.DATAELEMENT26ID, JE.DATAELEMENT27ID, JE.DATAELEMENT28ID, JE.DATAELEMENT29ID, JE.DATAELEMENT30ID    
      from dbo.JOURNALENTRY JE
        inner join FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
        left join 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)
    ) UNPVT
  where DATAELEMENTID = @ID

  -- Ledger account

  select @INLEDGERACCOUNT = 1 from
    (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.GLACCOUNT
    ) 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)
    ) UNPVT
  where DATAELEMENTID = @ID

  -- Control account

  select @INCONTROLACCOUNT = 1 from
    (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)
    ) UNPVT
  where DATAELEMENTID = @ID

  -- Balancing entry

  select @INBALANCINGENTRY = 1 from
    (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)
    ) UNPVT
  where DATAELEMENTID = @ID

  -- Closing element

  declare @ISCLOSINGELEMENT bit = 0
  select @ISCLOSINGELEMENT = ACS.CLOSINGELEMENT
  from dbo.PDACCOUNTSEGMENTVALUE DE
    inner join dbo.PDACCOUNTSTRUCTURE ACS on DE.PDACCOUNTSTRUCTUREID = ACS.ID

  set @INCLOSINGREQUIREMENT = 0
  if @ISCLOSINGELEMENT > 0
  begin
    --Check to see if the element is used as a specific closing requirement element

    select @INCLOSINGREQUIREMENT = 1
    from dbo.CLOSINGREQUIREMENT
    where DATAELEMENTID = @ID

    --If not, check to see if it is included in any selections used for selected closing elements

    if @INCLOSINGREQUIREMENT <> 1
      select @INCLOSINGREQUIREMENT = 1
      from dbo.CLOSINGREQUIREMENT
        cross apply dbo.UFN_IDSETREADER_GETRESULTS_GUID(CLOSINGREQUIREMENT.DATAELEMENTQUERYID) IDSET
      where IDSET.ID = @ID
  end

    return 0;