USP_DATAFORMTEMPLATE_VIEW_LEDGERACCOUNTPROFILE

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ACCOUNT	nvarchar(max)	INOUT	Account
@DESCRIPTION	nvarchar(400)	INOUT	Description
@CATEGORYCODE	tinyint	INOUT	Category
@SUBCATEGORYCODE	tinyint	INOUT	Sub Category
@CONTRAACCOUNT	bit	INOUT	Contra account
@BALANCETYPE	tinyint	INOUT	Balance type
@ACTIVE	bit	INOUT	Status
@HASINACTIVELEMENTS	bit	INOUT	Has inactive elements
@DATAELEMENTS	xml	INOUT	Segments
@SEPARATOR	nvarchar(1)	INOUT	Separator
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT	PDACCOUNTSYSTEMID
@CONTROLACCOUNT	bit	INOUT	Control account

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_LEDGERACCOUNTPROFILE
(
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@ACCOUNT nvarchar(max) = null output
  ,@DESCRIPTION nvarchar(400) = null output
  ,@CATEGORYCODE tinyint = null output
  ,@SUBCATEGORYCODE tinyint = null output
  ,@CONTRAACCOUNT bit = null output
  ,@BALANCETYPE tinyint = null output
  ,@ACTIVE bit = null output
  ,@HASINACTIVELEMENTS bit = null output
  ,@DATAELEMENTS xml = null output
  ,@SEPARATOR nvarchar(1) = null output
  ,@PDACCOUNTSYSTEMID uniqueidentifier = null output
  ,@CONTROLACCOUNT bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  declare @DATAELEMENTSTABLE table
  (DATAELEMENTID uniqueidentifier)

  insert into @DATAELEMENTSTABLE(DATAELEMENTID)
  select unPvt.DATAELEMENTID
  from
  (select ID, 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)) as unPvt

  select @DATALOADED = 1,
      @ACCOUNT = GLACCOUNT.ACCOUNTSTRING,
      @DESCRIPTION = GLACCOUNT.ACCOUNTDESCRIPTION,
      @CATEGORYCODE = ACCOUNTCODE.CATEGORYCODE,
      @SUBCATEGORYCODE = ACCOUNTCODE.SUBCATEGORYCODE,
      @CONTRAACCOUNT = ACCOUNTCODE.CONTRAACCOUNT,
      @BALANCETYPE = dbo.UFN_GETCATEGORYBALANCEFROMID(GLACCOUNT.ACCOUNTCODEID),
      @ACTIVE = ACTIVE
    ,@PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID,
    @CONTROLACCOUNT = case when ACCOUNTCODEID in (select DATAELEMENTID from @DATAELEMENTSTABLE) then 1 else 0 end
  from
      dbo.GLACCOUNT
  left join dbo.ACCOUNTCODE on ACCOUNTCODE.ID = GLACCOUNT.ACCOUNTCODEID
  where GLACCOUNT.ID = @ID

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

  if exists 
  (select 1 from
    dbo.UFN_LEDGERACCOUNT_DATAELEMENTS(@ID) DE
    inner join dbo.PDACCOUNTSEGMENTVALUE on DE.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
  where
    PDACCOUNTSEGMENTVALUE.ISACTIVE = 0)
    set @HASINACTIVELEMENTS = 1;

  set @DATAELEMENTS = (
  select unpvt.DATAELEMENTID, DE.SHORTDESCRIPTION, DE.ISACTIVE, SEGMENTSEQUENCE, ELEMENTDEFINITIONCODE
  from
    (select ID, 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 GLACCOUNT.ID = @ID) l
    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)
      ) as unPvt
      inner join dbo.PDACCOUNTSEGMENTVALUE DE on DATAELEMENTID = DE.ID
      inner join dbo.PDACCOUNTSTRUCTURE on DE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
      order by SEGMENTSEQUENCE
    for xml raw('ITEM'),type,elements,root('DATAELEMENTS'),BINARY BASE64)

  select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and ISBASICGL=0;

  return 0;