USP_DATAFORMTEMPLATE_VIEW_LEDGERSETUP

The load procedure used by the view dataform template "Ledger Setup View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ACCOUNTSTRUCTUREDEFINED	bit	INOUT	account structure defined
@ACCOUNTSTRUCTUREDEFAULTS	bit	INOUT	account structure defaults
@ACCOUNTCODEDEFINED	bit	INOUT	account codes defined
@ACCOUNTINGELEMENTSDEFINED	bit	INOUT	accounting elements defined
@ALLACCOUNTINGELEMENTSDEFINED	bit	INOUT	all elements defined
@ALLACCOUNTSEGMENTSDEFINED	bit	INOUT	all segments defined
@ACCOUNTSDEFINED	bit	INOUT	accounts defined
@ALLACCOUNTSDEFINED	bit	INOUT	all accounts defined
@CLOSINGELEMENTDEFINED	bit	INOUT	closing element defined
@CLOSINGREQUIREMENTSDEFINED	bit	INOUT	closing requirements defined
@CLOSINGACCOUNTSDEFINED	bit	INOUT	closing requirements defined without accounts
@BALANCINGENTRIESDEFINED	bit	INOUT	balancing entries defined
@CONTROLACCOUNTSDEFINED	bit	INOUT	control accounts defined
@FISCALYEARSDEFINED	bit	INOUT	fiscal years defined
@ACCOUNTINGELEMENTRELATIONSHIPSDEFINED	bit	INOUT	accounting element relationships defined
@ACCOUNTINGELEMENTSECURITYDEFINED	bit	INOUT	accounting element security defined
@TRANSACTIONSEXIST	bit	INOUT	transactions exist
@CLOSINGELEMENTNAME	nvarchar(103)	INOUT	closing element name

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_LEDGERSETUP
(
    @DATALOADED bit = 0 output,
  @ACCOUNTSTRUCTUREDEFINED bit = null output,
  @ACCOUNTSTRUCTUREDEFAULTS bit = null output,
  @ACCOUNTCODEDEFINED bit = null output,
  @ACCOUNTINGELEMENTSDEFINED bit = null output,
  @ALLACCOUNTINGELEMENTSDEFINED bit = null output,
  @ALLACCOUNTSEGMENTSDEFINED bit = null output,
  @ACCOUNTSDEFINED bit = null output,
  @ALLACCOUNTSDEFINED bit = null output,
  @CLOSINGELEMENTDEFINED bit = null output,
  @CLOSINGREQUIREMENTSDEFINED bit = null output,
  @CLOSINGACCOUNTSDEFINED bit = null output,
  @BALANCINGENTRIESDEFINED bit = null output,
  @CONTROLACCOUNTSDEFINED bit = null output,
  @FISCALYEARSDEFINED bit = null output,
  @ACCOUNTINGELEMENTRELATIONSHIPSDEFINED bit = null output,
  @ACCOUNTINGELEMENTSECURITYDEFINED bit = null output,
  @TRANSACTIONSEXIST bit = null output,
  @CLOSINGELEMENTNAME nvarchar(103) = null output
)
as
    set nocount on;

  declare @COUNT int;
  declare @PDACCOUNTSYSTEMID uniqueidentifier;
  set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

    set @DATALOADED = 1;

  select @ACCOUNTSTRUCTUREDEFINED = 1 from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0;
  select @ACCOUNTCODEDEFINED = 1 from dbo.ACCOUNTCODE;
  select @ACCOUNTINGELEMENTSDEFINED = 1 from dbo.PDACCOUNTSEGMENTVALUE inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0;
  select @ACCOUNTSDEFINED = 1 from dbo.GLACCOUNT;
  select @CLOSINGELEMENTDEFINED = 1 from dbo.PDACCOUNTSTRUCTURE where CLOSINGELEMENT = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0;
  select @CLOSINGREQUIREMENTSDEFINED = 1 from dbo.CLOSINGREQUIREMENT;
  select @BALANCINGENTRIESDEFINED = 1 from dbo.BALANCINGENTRY;
  select @COUNT = COUNT(distinct SYSTEMID) from dbo.CONTROLACCOUNT;
  if @COUNT = 2
    set @CONTROLACCOUNTSDEFINED = 1;
  select @FISCALYEARSDEFINED = 1 from dbo.GLFISCALYEAR;
  select @ACCOUNTINGELEMENTRELATIONSHIPSDEFINED = 1 from dbo.ACCOUNTINGELEMENTRELATIONSHIP;
  select @ACCOUNTINGELEMENTSECURITYDEFINED = 1 from dbo.ACCOUNTINGELEMENTSECURITYGROUP;
  select @TRANSACTIONSEXIST = 1 from dbo.JOURNALENTRY;
  if @CLOSINGELEMENTDEFINED = 1
    select @CLOSINGELEMENTNAME = ' - ' + DESCRIPTION from dbo.PDACCOUNTSTRUCTURE where CLOSINGELEMENT = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0;
  else
    set @CLOSINGELEMENTNAME = '';

  select @COUNT = COUNT(1) from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0;
  select @ACCOUNTSTRUCTUREDEFAULTS = 
    (case @COUNT when 1 then 
      case [LENGTH] when 4 then 1 else 0 end
    else 0 end)
  from dbo.PDACCOUNTSTRUCTURE where ELEMENTDEFINITIONCODE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0

  select
    @ALLACCOUNTINGELEMENTSDEFINED = case count(id) when 0 then 1 else 0 end
  from
    (select
      PDACCOUNTSTRUCTURE.ID
    from
      dbo.PDACCOUNTSTRUCTURE
    where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0
    except
    select
      PDACCOUNTSTRUCTURE.ID
    from
      dbo.PDACCOUNTSTRUCTURE
      left join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSTRUCTURE.ID = PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
    where
      PDACCOUNTSEGMENTVALUE.ID is not null and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0
    group by PDACCOUNTSTRUCTURE.ID) T1

  select
    @ALLACCOUNTSEGMENTSDEFINED = case count(id) when 0 then 1 else 0 end
  from
    (select
      PDACCOUNTSTRUCTURE.ID
    from
      dbo.PDACCOUNTSTRUCTURE
    where
      ELEMENTTYPECODE = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0
    except
    select
      PDACCOUNTSTRUCTURE.ID
    from
      dbo.PDACCOUNTSTRUCTURE
      left join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSTRUCTURE.ID = PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
    where
      PDACCOUNTSEGMENTVALUE.ID is not null and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL = 0
    group by PDACCOUNTSTRUCTURE.ID) T1

  select @ALLACCOUNTSDEFINED = case when count(distinct AC.CATEGORYCODE) >= 5 then 1 else 0 end
  from
  dbo.GLACCOUNT
  cross apply dbo.UFN_LEDGERACCOUNT_DATAELEMENTS(GLACCOUNT.ID) DE
  inner join dbo.PDACCOUNTSEGMENTVALUE on DE.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
  inner join dbo.ACCOUNTCODE AC on PDACCOUNTSEGMENTVALUE.ID = ac.ID
  inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  where PDACCOUNTSTRUCTURE.ISBASICGL = 0 and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

  set @CLOSINGACCOUNTSDEFINED = 1;
  if @CLOSINGREQUIREMENTSDEFINED = 1
  begin
    select @CLOSINGACCOUNTSDEFINED = 0 from dbo.CLOSINGREQUIREMENT where LEDGERACCOUNTID is null;
  end

    return 0;