USP_DATAFORMTEMPLATE_VIEW_GLACCOUNTCODEPROFILE

The load procedure used by the view dataform template "Account Code 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.
@SHORTID	nvarchar(100)	INOUT	Account code
@DESCRIPTION	nvarchar(60)	INOUT	Description
@CATEGORYCODE	tinyint	INOUT	Category
@SUBCATEGORYCODE	tinyint	INOUT	Subcategory
@ACTIVE	bit	INOUT	Active
@CONTRAACCOUNT	bit	INOUT	Contra account
@CONTROLACCOUNT	bit	INOUT	Control account
@PREVENTDATAENTRY	bit	INOUT	Prevent data entry
@PREVENTDATAENTRYBEFOREDATE	datetime	INOUT	Before
@PREVENTDATAENTRYAFTERDATE	datetime	INOUT	After
@PREVENTPOST	bit	INOUT	Prevent posting
@PREVENTPOSTBEFOREDATE	datetime	INOUT	Before
@PREVENTPOSTAFTERDATE	datetime	INOUT	After
@BALANCETYPE	tinyint	INOUT	Balance type

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GLACCOUNTCODEPROFILE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SHORTID nvarchar(100) = null  output,
    @DESCRIPTION nvarchar(60) = null output,
    @CATEGORYCODE tinyint = null output,
    @SUBCATEGORYCODE tinyint = null output,
  @ACTIVE bit = null output,
  @CONTRAACCOUNT bit = null output,
    @CONTROLACCOUNT bit = null output,
 @PREVENTDATAENTRY bit = null output,
  @PREVENTDATAENTRYBEFOREDATE datetime = null output,
  @PREVENTDATAENTRYAFTERDATE datetime = null output,
  @PREVENTPOST bit = null output,
  @PREVENTPOSTBEFOREDATE datetime = null output,
  @PREVENTPOSTAFTERDATE datetime = null output,
  @BALANCETYPE tinyint = null  output

    )
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows

    set @DATALOADED = 0  

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency

    -- can be considered.

    select
        @DATALOADED = 1,
        @SHORTID = T2.SHORTDESCRIPTION,
        @DESCRIPTION = T2.DESCRIPTION,
        @CATEGORYCODE = T1.CATEGORYCODE,
    @ACTIVE = T2.ISACTIVE,
    @CONTRAACCOUNT = T1.CONTRAACCOUNT,
    @SUBCATEGORYCODE = T1.SUBCATEGORYCODE,
        @CONTROLACCOUNT = case when T2.ID in (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) 
      then 1 else 0 end,
    @PREVENTDATAENTRY = T2.PREVENTDATAENTRY,
    @PREVENTDATAENTRYBEFOREDATE = T2.PREVENTDATAENTRYBEFOREDATE,
    @PREVENTDATAENTRYAFTERDATE = T2.PREVENTDATAENTRYAFTERDATE,
    @PREVENTPOST = T2.PREVENTPOST,
    @PREVENTPOSTBEFOREDATE = T2.PREVENTPOSTBEFOREDATE,
    @PREVENTPOSTAFTERDATE = T2.PREVENTPOSTAFTERDATE,
    @BALANCETYPE = dbo.UFN_GETCATEGORYBALANCEFROMID(@ID)


  from dbo.ACCOUNTCODE as T1
    inner join dbo.PDACCOUNTSEGMENTVALUE as T2 on T1.ID = T2.ID
  where T1.ID = @ID

    return 0;