USP_GLACCOUNTCODE_EDITLOAD

The load procedure used by the edit dataform template "Account Code Edit 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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@SHORTID	nvarchar(100)	INOUT	ID
@DESCRIPTION	nvarchar(60)	INOUT	Description
@ACTIVE	bit	INOUT	Active
@CONTRAACCOUNT	bit	INOUT	Contra account
@CASHFLOW	uniqueidentifier	INOUT	Cash Flow
@WORKINGCAPITAL	uniqueidentifier	INOUT	Working Capital
@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
@ELEMENTLENGTH	smallint	INOUT
@ELEMENTNAME	nvarchar(100)	INOUT
@CATEGORYCODE	tinyint	INOUT	Category
@SUBCATEGORYCODE	tinyint	INOUT	Subcategory
@HASACTIVITY	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_GLACCOUNTCODE_EDITLOAD(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
    @SHORTID nvarchar(100) = null  output,
    @DESCRIPTION nvarchar(60) = null output,
    @ACTIVE bit = null output,
    @CONTRAACCOUNT bit = null output,
    @CASHFLOW uniqueidentifier = null output,
    @WORKINGCAPITAL uniqueidentifier = 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,
    @ELEMENTLENGTH smallint = null output,
    @ELEMENTNAME nvarchar(100) = null output,
    @CATEGORYCODE tinyint = null output,
  @SUBCATEGORYCODE tinyint = null output,
  @HASACTIVITY bit = null output)
as

    set nocount on;

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

    set @DATALOADED = 0
    set @TSLONG = 0

    select
        @DATALOADED = 1,
        @TSLONG = T1.TSLONG,
        @SHORTID = T2.SHORTDESCRIPTION,
        @DESCRIPTION = T2.DESCRIPTION,
        @ACTIVE = T2.ISACTIVE,
    @CONTRAACCOUNT = T1.CONTRAACCOUNT,
        @CASHFLOW = T1.CASHFLOWCODEID,
        @WORKINGCAPITAL = T1.WORKINGCAPITALCODEID,
    @PREVENTDATAENTRY = T2.PREVENTDATAENTRY,
    @PREVENTDATAENTRYBEFOREDATE = T2.PREVENTDATAENTRYBEFOREDATE,
    @PREVENTDATAENTRYAFTERDATE = T2.PREVENTDATAENTRYAFTERDATE,
    @PREVENTPOST = T2.PREVENTPOST,
    @PREVENTPOSTBEFOREDATE = T2.PREVENTPOSTBEFOREDATE,
    @PREVENTPOSTAFTERDATE = T2.PREVENTPOSTAFTERDATE,
        @ELEMENTNAME = T3.DESCRIPTION,
        @ELEMENTLENGTH = (case T3.LENGTH when 0 then 100 else T3.LENGTH end) ,
    @CATEGORYCODE = T1.CATEGORYCODE,
    @SUBCATEGORYCODE = T1.SUBCATEGORYCODE
    from dbo.ACCOUNTCODE as T1
    inner join dbo.PDACCOUNTSEGMENTVALUE as T2 on T1.ID = T2.ID
    inner join dbo.PDACCOUNTSTRUCTURE as T3 on T2.PDACCOUNTSTRUCTUREID = T3.ID
    where T1.ID = @ID

  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

    return 0;