V_QUERY_ACCOUNTCODE
This provides the ability to query for account codes.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SHORTID | nvarchar(100) | ID | |
| DESCRIPTION | nvarchar(60) | Description | |
| CONTROLACCOUNT | int | Control account | |
| CONTRAACCOUNT | bit | Contra account | |
| CATEGORY | nvarchar(9) | yes | Category |
| SUBCATEGORY | nvarchar(8) | yes | Subcategory |
| BALANCETYPE | varchar(6) | Balance type | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| ELEMENTTYPE | nvarchar(26) | yes | Element type |
| SEGMENTTYPE | nvarchar(12) | yes | Element definition |
| NAME | nvarchar(100) | Element name | |
| ISACTIVE | bit | Active | |
| PREVENTDATAENTRY | bit | Prevent data entry | |
| PREVENTDATAENTRYBEFOREDATE | datetime | yes | Prevent data entry before |
| PREVENTDATAENTRYAFTERDATE | datetime | yes | Prevent data entry after |
| PREVENTPOST | bit | Prevent posting | |
| PREVENTPOSTBEFOREDATE | datetime | yes | Prevent posting before |
| PREVENTPOSTAFTERDATE | datetime | yes | Prevent posting after |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/30/2010 11:27:57 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_ACCOUNTCODE AS
select
ACCOUNTCODE.ID,
PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION as [SHORTID],
PDACCOUNTSEGMENTVALUE.DESCRIPTION,
case when PDACCOUNTSEGMENTVALUE.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 as CONTROLACCOUNT,
ACCOUNTCODE.CONTRAACCOUNT,
ACCOUNTCODE.CATEGORY as CATEGORY,
ACCOUNTCODE.SUBCATEGORY as SUBCATEGORY,
(case dbo.UFN_GETCATEGORYBALANCEFROMID(ACCOUNTCODE.ID) when 1 then 'Debit' else 'Credit' end) as BALANCETYPE,
ACCOUNTCODE.DATEADDED,
ACCOUNTCODE.DATECHANGED,
ACCOUNTCODE.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
PDACCOUNTSTRUCTURE.ELEMENTTYPE,
PDACCOUNTSTRUCTURE.ELEMENTDEFINITION as[SEGMENTTYPE],
PDACCOUNTSTRUCTURE.DESCRIPTION as [NAME],
PDACCOUNTSEGMENTVALUE.ISACTIVE,
PDACCOUNTSEGMENTVALUE.PREVENTDATAENTRY,
PDACCOUNTSEGMENTVALUE.PREVENTDATAENTRYBEFOREDATE,
PDACCOUNTSEGMENTVALUE.PREVENTDATAENTRYAFTERDATE,
PDACCOUNTSEGMENTVALUE.PREVENTPOST,
PDACCOUNTSEGMENTVALUE.PREVENTPOSTBEFOREDATE,
PDACCOUNTSEGMENTVALUE.PREVENTPOSTAFTERDATE
from dbo.ACCOUNTCODE
inner join dbo.PDACCOUNTSEGMENTVALUE on ACCOUNTCODE.ID = PDACCOUNTSEGMENTVALUE.ID
inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSTRUCTURE.ID = PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID
left join dbo.CASHFLOWCODE ON ACCOUNTCODE.CASHFLOWCODEID = CASHFLOWCODE.ID
left join dbo.WORKINGCAPITALCODE ON ACCOUNTCODE.WORKINGCAPITALCODEID = WORKINGCAPITALCODE.ID
left join dbo.ACCOUNTCODESTATUSCODE ON ACCOUNTCODE.STATUSCODEID = ACCOUNTCODESTATUSCODE.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = PDACCOUNTSEGMENTVALUE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = PDACCOUNTSEGMENTVALUE.CHANGEDBYID