Server Database Tables
User Management Tables
Tables: VY_USER, VY_USERGROUP, VY_USERSETTING, VY_PROTECTION, VY_LICENSE
Column Name | Data Type |
| Description |
ID | Number(10) | M | The same as the ID of the corresponding person |
Status | Char(1) | M | Status of the entry: |
NAME | NVARCHAR2(32) | M | Oracle username, e.g. ‘SCOTT’ |
LASTWEBLOGINDATE | DATE | R | Updated in Web and Vine and used in CalendarUpdates to show events with Changedate>lastweblogindate |
| PHONE | NVARCHAR2(40) | ||
| NVARCHAR2(100) | |||
| HASHKEY | NVARCHAR2(30) | used to replicate password changes to other db nodes |
Note: Here and further, in the descriptions of the tables, fields that cannot be empty are marked by the letter 'M' (mandatory) in the third column. Fields that are just recommended to have some values are marked by the letter 'R' (recommended).
Column Name | Data Type |
| Description |
ID | Number | M | ID of the user group |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
Status | Char(1) | M | Status of the entry: |
NAME | NVARCHAR2(32) | M | Oracle username e.g. ‘SCOTT’ |
LicenseID | Number |
| For a license group LICENSEID = VY_LICENSE.ID, otherwise NULL |
GroupTYPE | NUMBER |
| 1 - product (license) group 2 - organization in ASP mode 3 - not used 4 - normal group 5 - settings group in ASP mode (used to select parameters, forms, language, etc and then copy the settings to that organization) 6 – license group role for ASP usage. These groups are visible to all organizations and they can add users & groups to those but they cannot delete or change those groups. and they see only members who are from their organization. in myvine there is one group for each role page: "Vine Sales Role", "Vine Marketing Role", .... and one for specifying administrators "Vine Admin Role" |
ORGANIZATION | NUMBER |
| by default – organization = 0 If the group belongs to some organization then the field must store the id of vy_usergroup (grouptype 2) which forms the organization Groups of type=2 (organizations) have this field set to their id |
GroupRole | NVARCHAR2(30) |
| The name of the Oracle role for a product license group, e.g. VINTAGEROLE, otherwise NULL |
ShowInClient | Number(1) |
| 1 - if this group is visible in the client product; |
Description | NVARCHAR2 | r | Description of the object. |
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
FromObjectID | Number | M | ID of a user group |
TargetObjectID | Number | M | ID of a person assigned to the group |
FromObjectType | NVARCHAR2(20) | M | VY_USERGROUP |
TargetObjectType | NVARCHAR2(20) | M | VY_PERSON |
Status | Char(1) | M | Status of the entry: |
ConnectionType | Number | M | 15 |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
The VY_USERSETTING table is used to store Vine global system settings and some client user settings for Vine for Windows. See also the Using System Settings section in the SQL API chapter.
Column Name | Data Type |
| Description |
CreateDate | Date | r | The time of the setting's creation |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last update; |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
SHOWGROUP | Number | M | Object protection (visibility): - The ID of a user specifies that this is a user setting; |
EDITGROUP | Number | M | Object protection (editing) |
Status | Char(1) | M | Status of the entry: 'B' = normal status, ‘D’ = marked as deleted |
MACHINEID | Number |
| Not used yet |
SETTINGID | Number |
| The ID of the setting. It is generated automatically. |
NUMBERVALUE | NUMBER |
| If the stored setting is a number then it is stored here. |
STRINGVALUE | NVARCHAR2 |
| If the stored setting is a text string then it is stored here. |
BINARYVALUE | BLOB |
| If the stored setting is a binary string then it is stored here. |
NAME | NVARCHAR2 |
| The unique name of the setting. Necessary for the new system settings. Older settings depend on SETTINGID only. |
SETTINGTYPE | NVARCHAR2 |
| The type of the setting. Currently it can be the name of a database table, such as VY_PERSON or VY_EVENTTYPE or STATIC. |
SETTINGAREA | NVARCHAR2 |
| these areas should be used to group and see the specific settings easier in the admin console. The areas are stored in VS_USERSETTING_AREA |
Key(ID) Management
Column Name | Data Type |
| Description |
| ID | VARCHAR2 (32) | ||
| SHOWGROUP | Number(10) | ||
VineyardID | Number(10) | M | ID of an object in the Vine database |
ObjectType | VARCHAR2(20) | M | Type of the object, e.g. ‘PERSON’ |
ExternalID | VARCHAR2(512) | M | ID of the object in an external system |
ExternalDB | VARCHAR2(30) | M | Name of the external system |
Standard Object Tables
The following object tables include only object specific fields. For generic fields present in every object table, please refer to the Standard Objects and Fields chapter.
Column Name | Data Type |
| Description |
NAME | NVarchar2(50) | M | Object's name used internally |
Realname | NVarchar2(50) | M | Official name of the company |
Location | NVarchar2(50) |
| Extension to the name. Shown to the user as "Site" |
Phone | NVarchar2(40) |
| Telephone number |
Fax | NVarchar2(40) |
| Fax number |
NVarchar2(50) |
| Email address | |
WWWpage | NVarchar2(250) |
| Company's Web site |
Address | NVARCHAR2(50) |
| Mailing address |
Zip | NVARCHAR2(20) |
| Postal code |
City | NVARCHAR2(30) |
| City name |
State | NVARCHAR2(30) |
| State |
StreetAddress | NVARCHAR2(50) |
| Street address if the mailing address |
Country | NVARCHAR2 (100) |
| Country name. VP_COUNTRY |
Description | NCLOB | R | Description of the object. |
ZIP & City | VIRTUAL(50) |
|
|
Streetcity | NVARCHAR2(30) |
|
|
StreetZIP | NVARCHAR2(20) |
|
|
BUSINESSID | NVARCHAR2(20) |
|
|
CUSTOMERID | NVARCHAR2(30) |
|
|
DATASERVICEID | NVARCHAR2(30) |
|
|
COMPANYTYPE | NUMBER(9) |
| VP_COMPANYTYPE |
OFFICETYPE | NUMBER(9) |
| VY_OFFICETYPE |
| EDI | NVARCHAR2(50) | ||
| LEADSCORE | NUMBER(9) | ||
| TURNOVER | NUMBER(9) | ||
| PROFIT | NUMBER(9) | ||
| PERSONNEL | NUMBER(9) | ||
| SALESTHISYEAR | NUMBER(9) | ||
| SALESLASTYEAR | NUMBER(9) | ||
| INDUSTRYGROUP | NUMBER(9) | ||
| INDUSTRYCODE | NUMBER(9) | VP_INDUSTRYGROUP | |
| COMPANYFORM | NUMBER(9) | VP_COMPANYFORM | |
LONGITUDE/LATITUDE | NUMBER(11,8)) |
| A special Vine oracle job finds and stores these company geographical coordinates based on their addresses in the Vine database. The Sales meeting tool uses these. |
Column Name | Data Type |
| Description |
LastName | NVARCHAR2(100) | M | Last name |
FirstName | NVARCHAR2(100) | r | First name |
Title | NVARCHAR2(40) | r | Title, e.g. job description |
Department | NVARCHAR2(30) |
| Department, unit in the company/location |
Phone | NVARCHAR2(40) |
| Telephone number of the person |
Phone2 | NVARCHAR2(40) |
| Additional phone, e.g. mobile |
Fax | NVARCHAR2(40) |
| Fax number |
NVARCHAR2(50) |
| E-mail address | |
HomePHONE | NVARCHAR2(40) |
| Home phone |
HomeAddress | NVARCHAR2(50) |
| Home address |
HomeZip | NVARCHAR2(20) |
| Postal code |
HomeCity | NVARCHAR2(30) |
| City name |
HomeState | NVARCHAR2(30) |
| State |
HomeCountry | NVARCHAR2(30) |
| Country name |
Description | NCLOB | r | Description of the object. |
| SKYPE | NVARCHAR2 (50) | ||
| ISUSER | NUMBER (10) | ||
| DECISIONMAKER | NUMBER (9) | VS_PERSONTYPE | |
| COUNTRYNAME | NUMBER (9) | VP_COUNTRY | |
| LEADSCORE | NUMBER (9) | ||
| LASTMARKETINGOPERATIONTYPE | NUMBER (9) | VP_MARKETINGOP_TYPE | |
| LASTMARKETINGOPERATIONDATE | DATE | ||
| WEBPAGE | NVARCHAR2 (250) | ||
| DATASERVICEID | NVARCHAR2 (30) | ||
| MAINREPOSPONSIBLITY | NUMBER (9) | VP_MAIN_RESP_TYPE |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) | M | Name of the project |
ProjectPhase | NUMBER (9) | M | ID of the project's phase: see VY_PROJECTPHASE |
ProjectType | NUMBER (9) | M | ID of the project's type: see VY_PROJECTTYPE |
StartDate | Date |
| Project start date (or date with time) |
EndDate | Date |
| Project end date (or date with time) |
ProjectSum | Number(9) |
| Project total (currency and actual meaning are up to customers) |
Budget | Number(9) |
| Budget of the project (currency and actual meaning are up to customers) |
Amount | Number(9) |
| Currency and actual meaning are up to customers |
ProjectLength | NVARCHAR2(32) |
| Duration of the project (units and actual meaning are up to customer) |
Description | NCLOB | r | Description of the object. |
| SHOWINWEB | NUMBER (9) |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2(200) | M | Name of the event |
StartDate | Date | M | Event start date or date with time |
StartDateGMT | Date | M | GMT time |
EndDate | Date | M | Event end date or date with time |
EndDateGMT | Date | M | GMT time |
EventPhase | Number(9) | M | Phase of the event: 1 = 'not done', 2 = 'done' |
EventType | Number(9) | M | ID of the event's type: see VY_EVENTTYPE |
RemindTime | Number(9) |
| How many seconds before the event the reminder message is given (0 if no reminder) |
RemindType | Number(9) |
| Remind type popup (0= dialog, 1=gsm) |
RepeatID | Number(14) |
| ID of the first event in the repeat sequence. |
RepeatType | Number(9) |
| How the event is repeated 9999 = It is repeating event from outlook and in normal Vine clients (Vine for Windows, Vine Web, etc.) it does not allow editing of start/end dates (in edit form & in calendar). In calendar you should give a warning: "This is repeating event made in Outlook. Event start and end dates cannot be modified in Vine. Please change the date in Outlook." do not allow editing of repeattype. instead show “Outlook” in disabled combo do not allow editing of repeatenddate. instead keep the field disabled |
RepeatEndDate | Date |
| End of the repeat sequence: date or date with time |
Description | NCLOB | r | Description of the object. |
RemindStatus | Number(9) |
| (Used only by the SMS Server) |
Location | NVARCHAR2(100) |
| To store information about where the event happens |
Priority | Number(9) |
| (Not used in 5.1) priority for tasks, so that it would be possible to have priority sorted list of tasks |
MaxAttendees | Number(9) |
| (Not used in 5.1) how many persons can register in extranet to this event |
Publicity | Number(9) |
| (Not used in 5.1) related to extranet registrations to events. is the event visible there to all or not |
AllDayEvent | Number(1) |
| hidden field set in Vine clients to 1 if event is all-day/untimed.
|
| REPORTED | NUMBER (9) | VY_EVENT_REPORTED |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) | M | Name of the collection |
ContentsType | Number(9) | M | ID of the type of a connection which FROMOBJECTTYPE field sets the type of collection members, see VY_CONNECTIONTYPE.ID |
Description | NCLOB | r | Description of the object |
TOPLEVEL | NUMBER(9) | r | Set to 1 if the collection is at the top of the collection hierarchy, 2 - otherwise. VY_COLL_TOPLEVEL_VIEW |
EXCLUDING | NUMBER(9) | r | Set to 1 if the collection is excluding, 2 - otherwise. VY_COLL_EXCLUDING |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) | M | Name of the resource |
ResourceType | number | M | ID of the resource's type, see VY_RESOURCETYPE.ID |
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) | M | Name of the document |
WindowsPath | NVARCHAR2(500) |
| Windows pathname for the document |
DOCUMENTTYPE | NUMBER(9) |
| Type parameter field. VY_DOCUMENTTYPE |
DOCUMENTPHASE | NUMBER(9) |
| Phase parameter field. VY_DOCUMENTPHASE |
KEEPOLDVERSIONS | NUMBER(9) |
| Flag to keep document history in vy_document_version. VY_DOC_KEEPVERSIONS |
SAVETOSERVER | NUMBER(9) |
| Flag to save document to database to vy_document_version VY_DOC_SAVETOSERVER |
Description | NCLOB | r | Description of the object. |
| DOCUMENTDATE | DATE |
Column Name | Data Type |
| Description |
DocumentID | NUMBER(10) |
| ID of the document in vy_document |
VersionID | NUMBER(9) |
| Document version |
FILEDATA | BLOB |
| Document file contents |
HASH | VARCHAR2 (32 Byte) |
| Unique hash code to not store same copies of the document |
FILETEXT | NCLOB |
| Stores cached value of the document text. Updated by Server Setup. |
Description | NCLOB |
| Description of the object |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (2000) | M | URL, e.g. https://vine.eu |
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) | M | Name of the map |
Locked | Number(9) | M | Edit lock: 0 = not locked, 1 = locked |
MapData | BLOB | M | Graphical data of map |
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) |
| Object Name |
salesunit | Number(9) |
| VY_SALESUNIT |
salesphase | Number(9) |
| VY_SALESPHASE |
startdate | date |
|
|
enddate | date |
|
|
offerdate | date |
|
|
salestype | Number(9) |
| VY_SALESTYPE |
sales | Number(9) |
|
|
Sales2 | Number(9) |
|
|
Sales3 | Number(9) |
|
|
salesarea | Number(9) |
| VY_SALES_AREA |
subject | Nvarchar2(40) |
|
|
decisionphase | Number(9) |
| VY_SALES_DECIS_PHASE |
Financestatus | Number(9) |
| VY_SALES_FINA_STATUS |
Internalcompetition | Number(9) |
| VY_SALESINTERNCOMPET |
Totalprobability | Number(9) |
|
|
salesprobability | Number(9) |
| VY_SALESPROBABILITY |
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) |
| Object Name |
salesarea | Number(9) |
| VY_SALES_AREA |
Businessunit | Number(9) |
| VY_SALESUNIT |
Wellbeing | Number(9) |
| VS_SALESACC_BEING |
accountphase | Number(9) |
| VS_SALESACCOUNTPHASE |
Details | NCLOB |
|
|
Comments | NCLOB |
|
|
Target | NCLOB |
|
|
Challenges | NCLOB |
|
|
Plan | NCLOB |
|
|
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) |
| Object Name |
Caseid | Number(9) |
|
|
component | Nvarchar2(50) |
|
|
startdate | Date |
|
|
enddate | Date |
|
|
note | NVARCHAR2(2000) |
|
|
OtherInfo | Nvarchar2(50) |
|
|
ServReqPhase | Number(9) |
| VS_SERVREQ_PHASE |
PlatformType | Number(9) |
| VS_PLATFORMTYPE |
PlatFormVersion | Nvarchar2(50) |
|
|
PriorityType | Number(9) |
| VS_PRIORITYTYPE |
ProductType | Number(9) |
| VS_HDPRODUCTTYPE |
ProductVersion | Nvarchar2(50) |
|
|
Published | Number(9) |
| VS_SERVREQ_PUBLISHED |
Repeating | Number(9) |
| VS_SERVREQ_REPEATING |
ResponsibleType | Number(9) |
| VS_RESPONSIBLETYPE |
Severity | Number(9) |
| VS_SERVREQ_SEVERITY |
Solution | NVARCHAR2(2000) |
|
|
SolutionType | Number(9) |
| VS_SOLUTIONTYPE |
ServReqType | Number(9) |
| VS_SERVREQ_TYPE |
Version | Nvarchar2(50) |
|
|
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
NAME | NVARCHAR2 (200) |
| Object Name |
KNOWLEDGEBASEType | Number(9) |
| VS_KNOWBASETYPE |
| KNOWLEDGEBASEPHASE | NUMBER (9) | VS_KNOWBASEPHASE | |
component | Nvarchar2(50) |
|
|
startdate | Date |
|
|
enddate | Date |
|
|
note | NVARCHAR2(2000) |
|
|
PlatFormVersion | Nvarchar2(50) |
|
|
ProductType | Number(9) |
| VS_HDPRODUCTTYPE |
Published | Number(9) |
| VS_KNOWBASEPUBLISHED |
Solution | NVARCHAR2(2000) |
|
|
Version | Nvarchar2(50) |
|
|
| VERSIONFIXED | NVARCHAR2 (50) | ||
| EVALUATIONDATE | DATE | ||
Description | NCLOB | r | Description of the object. |
Column Name | Data Type |
| Description |
| NAME | NVARCHAR2 (200) | ||
| ANSWERTEXT | NCLOB | ||
| ANSWERSELECTION | NCLOB |
Column Name | Data Type |
| Description |
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| QUESTIONTYPE | NUMBER (9) | VS_QUESTION_TYPE | |
| ANSWEROPTIONS | NCLOB | ||
| INFOTEXT | NCLOB | ||
| DESCRIPTION | NCLOB | ||
| POSITION | NUMBER (9) | ||
| MANDATORY | NUMBER (9) | VS_QUESTION_MANDATOR | |
| ANSWERTYPE | NUMBER (9) | VS_QUESTION_ANS_TYPE |
Column Name | Data Type |
| Description |
| ENDDATE | DATE | ||
| PHASE | NUMBER (9) | VS_QUESTIONN_PHASE | |
| DESCRIPTION | NCLOB | ||
| NAME | NVARCHAR2 (200) | ||
| STARTDATE | DATE | ||
| COMMON | NUMBER (1) | ||
| DEFAULTQUESTIONNAIRE | NUMBER (9) | VS_QUESTIONN_DEFAULT | |
| EVENTTYPE | NUMBER (9) | VY_EVENTTYPE |
Used in Sales Case edit form - for the Turnover page.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| ADDCONNECTIONGROUP | NUMBER (10) | ||
| DELETECONNECTIONGROUP | NUMBER (10) | ||
| OBJECTIMAGE | BLOB | ||
| INCOMEDATE | DATE | ||
| INCOME | NUMBER (9) | ||
| XMLDATA | XMLTYPE | ||
| ACTIVEUNTIL | DATE |
Used to store a sales person's sales goals.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| ADDCONNECTIONGROUP | NUMBER (10) | ||
| DELETECONNECTIONGROUP | NUMBER (10) | ||
| OBJECTIMAGE | BLOB | ||
| PERIOD | DATE | ||
| SALES | NUMBER (9) | ||
| XMLDATA | XMLTYPE | ||
| VISITS | NUMBER (9) | ||
| ACTIVEUNTIL | DATE | ||
| CALLS | NUMBER (9) |
Stores account budget values.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| ADDCONNECTIONGROUP | NUMBER (10) | ||
| DELETECONNECTIONGROUP | NUMBER (10) | ||
| OBJECTIMAGE | BLOB | ||
| INCOMEDATE | DATE | ||
| INCOME | NUMBER (9) | ||
| XMLDATA | XMLTYPE | ||
| VISITS | NUMBER (9) | ||
| PERIOD | DATE | ||
| SALES | NUMBER (9) | ||
| ACTIVEUNTIL | DATE |
Stores sales leads.
VS_LEAD UsageColumn Name | Data Type |
| Description |
| ID | NUMBER (10) | M | |
| CREATEDATE | DATE | M | |
| CREATEUSER | VARCHAR2 (32 Byte) | M | |
| CHANGEDATE | DATE | M | |
| CHANGEUSER | VARCHAR2 (32 Byte) | M | |
| STATUS | CHAR (1 Byte) | M | |
| SHOWGROUP | NUMBER (10) | M | |
| EDITGROUP | NUMBER (10) | M | |
| EVENTID | NUMBER (10) | ||
| LEADPERSONID | NUMBER (10) | Lead (Doug Moore in the sample lead above) | |
| RESPONSIBLEPERSONID | NUMBER (10) | Responsible persons (Lisa Moore in the sample lead) | |
| LASTFREETEXT | NCLOB | currently not used | |
| DESCRIPTION | NCLOB | ||
| PHASE | NUMBER (9) | vp_lead_phase User sets this for a lead | |
| ORGANIZATIONID | NUMBER (10) | M | |
| XMLDATA | XMLTYPE | ||
| DELETEGROUP | NUMBER (10) | M | |
| ADDCONNECTIONGROUP | NUMBER (10) | M | |
| DELETECONNECTIONGROUP | NUMBER (10) | M | |
| NAME | NVARCHAR2 (200) | Lead name | |
| FAVORITE | NUMBER (1) | currently not used | |
| NEXTACTIONDATEGMT | DATE | The user sets the date of the next lead action in this field.
| |
| INPROGRESSPHASE | NUMBER (9) | subphase of the "in progress" lead phase stored in vp_lead_inprogress | |
| NODEALREASON | NUMBER (9) | subphase of the "no deal" lead phase: vp_lead_nodeal | |
| CONTRACTREASON | NUMBER (9) | subphase of the "contract" lead phase: | |
| BADLEADREASON | NUMBER (9) | subphase of the "rejected" lead phase: | |
| ALLDAYNEXTACTION | NUMBER (1) | currently not used | |
| LEADVALUE | NUMBER (9) | Using this field the user can enter the deal value in the Sales application. | |
| RERAISE | NUMBER (1) | 1 to 6 values correspond to the lead flags from the following visual set from left to right:
The value 1 (the leftmost flag) is given to a lead when it is raised again ( to avoid creating duplicate leads) |
Stores automation rules.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| ADDCONNECTIONGROUP | NUMBER (10) | ||
| DELETECONNECTIONGROUP | NUMBER (10) | ||
| OBJECTIMAGE | BLOB | ||
| ENABLED | NUMBER (9) | VP_LEADACT_ENABLED | |
| TRIGGERPOINTS | NUMBER (9) | ||
| TRIGGERTYPE | NUMBER (9) | VP_LEADACT_TRIGTYPE | |
| ACTIONTYPE | NUMBER (9) | VP_LEADACT_TYPE | |
| XMLDATA | XMLTYPE | ||
| ACTIVEUNTIL | DATE | ||
| LASTPERSONID | NUMBER (10) | ||
| SAVEDSTATE | XMLTYPE | ||
| AUTOMATIONTYPE | NUMBER (9) | VP_AUTOMATIONTYPE |
Stores email statistics.
Column Name | Data Type |
| Description |
| EVENTID | NUMBER (10) | ||
| PERSONID | NUMBER (10) | ||
| STATTYPE | NUMBER (9) | VS_EMAIL_STATISTICSTYPE | |
| CREATEDATE | DATE | ||
| SHOWGROUP | NUMBER (10) | ||
| INFO | NVARCHAR2 (100) | ||
| ROWGUID | VARCHAR2 (32 Byte) | ||
| ID | VARCHAR2 (32 Byte) | ||
| DURATION | NUMBER (9) | ||
| LEADSCORE | NUMBER (9) | ||
| ANONYMOUSPERSONID | VARCHAR2 (32 Byte) |
Stores automation rules to be executed.
Column Name | Data Type |
| Description |
| ID | VARCHAR2 (32 Byte) | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CREATEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| RULEID | NUMBER (10) | ||
| SCHEDULEDTIME | DATE | ||
| CONTINUEFROMSTEP | VARCHAR2 (32 Byte) | ||
| PROCESSREPORT | VARCHAR2 (2000 Byte) | ||
| OBJECTS | XMLTYPE | ||
| PARENTID | VARCHAR2 (32 Byte) |
Tables of server services.
Column Name | Data Type |
| Description |
| NODE_NAME | VARCHAR2 (200 Byte) | ||
| JOB_NAME | VARCHAR2 (200 Byte) | ||
| ENABLED | NUMBER (1) | ||
| ID | VARCHAR2 (32 Byte) |
Tables of server locks.
Column Name | Data Type |
| Description |
| LOCKING_NODE_NAME | VARCHAR2 (200 Byte) | ||
| LOCK_NAME | VARCHAR2 (50 Byte) | ||
| LOCK_END | TIMESTAMP(6) WITH TIME ZONE | ||
| LAST_ERROR | TIMESTAMP(6) WITH TIME ZONE | ||
| ID | VARCHAR2 (32 Byte) |
Object Connections Tables
Tables: VY_CONNECTION, VY_CONNECTIONTYPE
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
FromObjectID | Number | M | ID of the "FROM" object |
TargetObjectID | Number | M | ID of the "TARGET" object |
FromObjectType | VARCHAR2(20) | M | Type of the "FROM" object, e.g. VY_COMPANY |
TargetObjectType | VARCHAR2(20) | M | Type of the "TARGET" object, e.g. VY_PERSON |
ConnectionType | Number | M | ID of the connection's type |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
Column Name | Data Type |
| Description |
ID | Number | M | Unique connection type number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
SHOWGROUP | Number |
| Mostly used for organizations |
FromName | NVARCHAR2(30) | M | Name of the connection on the FromObject side, e.g. 'Employee' |
TargetName | NVARCHAR2(30) | M | Name of the connection on the TargetObject side, e.g. 'Employer' |
FromObjectType | VARCHAR2(20) | M | Type of the "from" object, e.g. VY_COMPANY |
TargetObjectType | VARCHAR2(20) | M | Type of the "target" object, e.g. VY_PERSON |
FromCount | Number | M | Limits connections to "from" objects: |
TargetCount | Number | M | Limits connections to "target" objects: |
DefaultConnectionType | Number | M | Sets this connection type as the default one between the objects having the FROMOBJECTTYPE and TARGETOBJECTTYPE types: |
COLOR | NUMBER |
| Used to set object’s color attribute. Vine tools such as Calendar, can check this attribute to apply color formatting to the object. |
SHOWINCALENDAR | NUMBER(1) |
| Used to pick con types to be shown on the Calendar. If = 2, then events connected with connections of this type are NOT to be shown on the Calendar |
PROTECTED | NUMBER(1) |
| Set for connection types which can be modified only using tools |
DeleteTargetObjects | NUMBER(10) |
| If set to “1” then all “target” end objects connected to the deleted object will be removed if they are not connected to other objects with this connection type. For example: VY_EVENT -> VS_ANSWER |
DeleteFromObjects | NUMBER(10) |
| If set to “1” then all “from” end objects connected to the deleted object will be removed if they are not connected to other objects with this connection type. For example: VS_QUESTION <- VS_QUESTIONNAIRE |
| DELETEDCONNECTIONTYPE | NUMBER (9) | when the user deletes a connection of this type, instead of deleting it will be converted to the type specified in this field good use for example emlpoyer => ex-employer | |
| PRIMARYFROM | NUMBER (1) | if 1 then connections of this type are shown in the Primary Connections panel in Summary View for objects of the type set in FromObjectType . otherwise 0. | |
| PRIMARYTARGET | NUMBER (1) | if 1 then connections of this type are shown in the Primary Connections panel in Summary View for objects of the type set in TargetObjectType. otherwise 0. | |
Database Structure Tables
Tables: VY_CLIENTSQL, VY_HOLIDAY, VY_ICON, VY_OBJECT, VY_OBJECT_FIELD, VY_PACKAGE_VERSION, VY_PLUGIN, VY_TABLE, VY_TABLE_FIELD, VY_WINDOW
VS_AREA table defines top-level areas used for arranging tools. This is parameter table.
Column Name | Data Type | Description |
ID | See data types for standard database fields. | See description for standard database fields. |
CREATEDATE | ||
CREATEUSER | ||
CHANGEDATE | ||
CHANGEUSER | ||
STATUS | ||
NAME | ||
SHOWGROUP | ||
ICON | BLOB | Area icon displayed in user interface |
| ICONACTIVE60 | BLOB | icon used in versions from 6.0 |
| ICONINACTIVE60 | BLOB | icon used in versions from 6.0 |
COLOR | See data types for standard database fields. | See description for standard database fields. |
OBSOLETE | ||
DESCRIPTION |
VS_SUBAREA table defines top-level areas used for arranging tools. This is parameter table. Table columns are similar to those of VS_AREA .
Its default values: 1 – “My Tools”
3 – “Company”
Column Name | Data Type | Description |
| ID | NUMBER (9) | |
| CREATEDATE | DATE | |
| CREATEUSER | VARCHAR2 (32 Byte) | |
| CHANGEDATE | DATE | |
| CHANGEUSER | VARCHAR2 (32 Byte) | |
| STATUS | CHAR (1 Byte) | |
| NAME | NVARCHAR2 (200) | |
| SHOWGROUP | NUMBER (10) | |
| COLOR | NUMBER (10) | |
| ICON | BLOB | |
| ICON60 | BLOB | |
| OBSOLETE | NUMBER (1) | |
| DESCRIPTION | NVARCHAR2 (2000) | |
| XMLDATA | XMLTYPE | |
| POSITION | NUMBER (9) |
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
SHOWGROUP | Number | M | Object protection (visibility): (in VY_URL_VIEW -1 is used for "Self" objects) |
EDITGROUP | Number | M | Object protection (editing): (in VY_URL_VIEW -1 is used for "Self" objects and –2 is used for non-granted objects) |
DELETEGROUP | Number |
| Object protection: (in VY_URL_VIEW -1 is used for "Self" objects and –2 is used for non-granted objects) |
AddConnectionGroup, DeleteConnectionGroup | Number |
| Connection protection: > 0 –granted user or user group can manage connections. -3 – connections can be managed only by connected persons. In view -1 is used for “Self” and –2 is used for non-granted objects. |
AREA | NUMBER |
| ID of the area: see VS_AREA |
SUBAREA | NUMBER |
| ID of the sub-area: see VS_SUBAREA |
TOOLORDER | NUMBER(3) |
| Defines order of tools in sub-area, tools are sorted in ascending order |
PROPERTIES | NCLOB |
| XML containing tool’s properties. |
CLIENTVERSION_MIN | NUMBER |
| Minimum Vine for Windows version needed to run the tool, e.g. 50000 is version 5.0. |
CLIENTVERSION_MAX | NUMBER |
| Maximum Vine for Windows version that can be used to run the tool. |
NAME | NVARCHAR2(50) | M | Name of the tool |
ACTIVEUNTIL | DATE | r | this field should be used in all tools to filter non-active objects out from regular usage |
XMLDATA | XMLTYPE | r |
|
CLIENTTYPE | NUMBER(3) | r | 1 = Vine for Windows 2 = Web 3 = Vine for Windows and Web |
| HELP | NCLOB | stores xml file defining help topics shown as a tooltip for the tool | |
COLOR | NUMBER |
| Used to set object’s color attribute. Vine tools such as Calendar, can check this attribute to apply color formatting to the object. |
The fields of the VY_PACKAGE_VERSION table are imported from the OM_OBJECTS.txt file during the database setup.
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
SHOWGROUP | Number | M | Defines which group can use the set. |
NAME | NVARCHAR2(50) | M | Name of the set |
Locked | Number(1) |
| 0 = not locked 1 = locked. The API set is always locked regardless of its LOCKED setting. None of its tables or table fields can be deleted or altered in any way. |
DESCRIPTION | NVARCHAR2 (2000) |
|
|
VY_CLIENTSQL table contains SQL Commands which Vine client applications need.
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
NAME | VARCHAR2(50) |
| The name of an SQL command, e.g. “SAVE_VY_COMPANY" |
PACKAGENAME | VARCHAR2(50) |
| The name of a Vine object set. New sets can be created by Vine Object Manager |
SqlStr | NCLOB |
| SQL clause |
The fields can be edited using Vine Object Manager .
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
NAME | NVARCHAR2(50) | M | The object's name shown in the Vine Win client ("use name"), e.g. “Person” |
ManyStr | NVARCHAR2(50) | M | Text string naming a set of objects, e.g. “Persons” |
NewStr | NVARCHAR2(50) | M | Object creation string, e.g. “New Person” |
OpenStr | NVARCHAR2(50) | M | Object opening string, e.g. “Open Person” |
EditStr | NVARCHAR2(50) | M | Object editing string, e.g. “Edit Person” |
DeleteStr | NVARCHAR2(50) | M | Object deletion string, e.g. “Delete Person” |
Synctype | Number(9) | M | 1 = All objects, 2 = Loaded object 3 = Objects set by a specific WHERE clause, see the WHERECLAUSE field |
DuplicateStr | NVARCHAR2(50) | M | Object duplication string, e.g. “Duplicate Person” |
WhereClause | NVARCHAR2(2000) | M | The “WHERE” part of the SELECT clause used for choosing the objects for the synchronization |
TableID | Number(9) | M | Link to the corresponding entry in VY_TABLE |
PackageVersionID | Number(9) | M | Link to the corresponding entry in VY_PACKAGE_VERSION |
Description | NVARCHAR2(2000) | M |
|
MENU_HIDDEN | Number(1) | M | If =1 then objecttype hidden from Vine Win/Web New Object menu, etc. can be set using Vine Object Manager. Now it is recommended to use vy_table_menu_hidden table for this. |
Column Name | Data Type | Description |
TABLEID | Number(10) | ID of object table. All object types based on this table will be hidden. |
SHOWGROUP | NUMBER(10) | Table is hidden based on the condition if the current user can see this record. Meaning that if it iz: - 0 (zero), then the object type is hidden for all users; - ID of a user or a user group - the object type is hidden for that user or user group. - ID of an organization group - the object type is hidden for all users of that organization. This logic is implemented in vy_object_table_view |
CREATEDATE | Date |
|
CREATEUSER | VARCHAR2(32) |
|
CHANGEDATE | Date |
|
CHANGEUSER | VARCHAR2(32) |
|
| ID | VARCHAR2 (32 Byte) |
Object Synchronization Settings (SYNCTYPE)
When you edit an object type in Object Manager you can choose from the following sync options for the SYNCTYPE field:
- All objects. That means that all the objects of that type will be loaded to a local database during its creation. After that, the results of any operation on such objects (creation, modification or deletion) will be synchronized to/from the local database.
- Loaded objects. When a Vine for Windows user opens an object for the first time, it is loaded to the local database and, from that time, it will be always in sync with the corresponding object from the server database. This is the default setting for most standard objects, e.g. company, person, project etc.
- Objects with WHERE clause. If you choose this setting then use the WHERECLAUSE field to enter the WHERE part of the SELECT clause that will be used to retrieve needed objects.
For example, here is the default WHERE clause which is actually used for the Loaded objects choice:
WHERE ( <<TABLE_NAME>>.CHANGEDATE >= myLastSync and exists (select objectid from VY_ACCOUNT where objectid = <<TABLE_NAME>>.ID and ACCOUNTID = myAccountID))
Here <<TABLE_NAME>> is a tag which is substituted during generation with the name of the table and VY_ACCOUNT is a table that keeps track of objects copied to local databases.
The next section, Customizing Sync Options (Example), gives more detail about the mechanism of synchronization between the server and local databases and shows an example of using a custom WHERE clause.
Customizing Sync Options (Example)
Suppose that you want to give end users more control over the synchronization of the person objects. This can be done by adding auxiliary fields to the VY_PERSON table and creating a custom 'WHERE' clause that will fetch persons based on the values of those fields.
A situation may arise (for instance, for performance reasons or in an environment where users often work off-line) when the users would like to mark the persons that should be automatically copied locally. In this case, you can add a special number field, SYNCTOALL, to the VY_PERSON table.
Then you need to add a checkbox, 'Preload', to the edit form. The check box will permit the users to set the value of the SYNCTOALL field. When the checkbox is selected, 1 will be stored in the field, otherwise 0 will be stored.
Next, you need to edit the custom WHERE part of the SELECT command that will fetch persons from the server database. Open the Update Object window and select Objects with WHERE clause from the Synchronize pull-down menu.
The default WHERE clause (corresponding to the Loaded Objects option) will initially appear. You need to modify the WHERE condition so that it will check for objects that should be synced. Note that you can use two local variables: myAccountID and myLastSync. These variables are passed as input parameters to the cursor that will contain the modified SELECT statement. myAccountID is just a user account ID (see the description of the VY_USERACCOUNT table in the Vine Developers’ Guide), myLastSync is the date of the latest synchronization of the table to the user's local database; this date is taken from the VY_SYNCACCOUNT table.
In our case the modified WHERE clause can look as follows:
WHERE <<TABLE_NAME>>.CHANGEDATE >= NVL(myLastSync,
to_date('000101010100','YYYYMMDDHH24MI'))
AND
(exists (select objectid from VY_ACCOUNT where objectid = <<TABLE_NAME>>.ID and ACCOUNTID = myAccountID) OR <<TABLE_NAME>>.SyncToAll = 1);
Here note the use of the NVL function which is needed for new objects whose myLastSync value is NULL.
Save your changes and start the generation procedure. The CURSOR shown below will be generated to fetch needed objects from the server database.
/*--------- FIXED PART-------------------------------------------*/
cursor syncVY_PERSONS(myAccountID number,myLastSync date) return VY_PERSONRecType is
select VY_PERSON.id, status, createDate, createUser, changeDate, changeUser, showgroup, editgroup, lastname, firstname, title, department, phone, phone2, fax, email, homePhone, homeAddress, homeZip, homeCity, HomeState, homeCountry, Description, SYNCTOALL
from VY_PERSON VY_PERSON
/*--------- CUSTOM WHERE CLAUSE---------------------*/
WHERE
-- fetch persons changed or new after the last sync if ...
VY_PERSON.CHANGEDATE >=
NVL(myLastSync, to_date('000101010100','YYYYMMDDHH24MI'))
AND
-- ... and only if they are already present in the local base or ...
(exists (select objectid from VY_ACCOUNT where objectid = VY_PERSON.ID and ACCOUNTID = myAccountID)
-- ... or they are marked to be synced (SyncToAll = 1)
or VY_PERSON.SyncToAll = 1);
Now, when the user creates/updates a person, the following edit form will appear:
If the user selects the Preload check box then this person will be automatically synced to any Vine Win user at the first Vine session. Please note the difference: in our case the object is first synced to the local database even if the user has not opened the object. On the contrary, for the Loaded objects option the object is first synced only when the user has opened the object.
The fields can not be edited using Vine Object Manager but new entries can be added if new object types are added to the VY_OBJECT table.
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
NAME | VARCHAR2(50) | M | The name of a table, e.g. “VY_PERSON” |
Locked | Number(1) |
| 0 = not locked 1 = locked - cannot be deleted |
Hidden | Number(1) |
| 0 = normal object 1 = structure object (not shown in standard mode of Vine Object Manager), 2 = normal hidden object (not visible in Vine Windows/Web, otherwise same as “normal” objects) |
TRACE | Number(1) |
|
|
Description | NVARCHAR2(2000) | M |
|
Position | Number(9) | M | Sets the relative position of objects in the list returned by the standard Find tool. An object type with a lower position value comes before one with a higher value. |
The NAME column can be directly edited using Vine Object Manager.
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
NAME | NVARCHAR2(50) | M | Name of the object field ("use name"), e.g. “Last name” |
ObjectID | Number(9) | M | Link to the corresponding entry in VY_OBJECT |
TableFieldID | Number(9) | M | Link to the corresponding entry in VY_TABLE_FIELD |
Description | NVARCHAR2(2000) |
|
|
Hidden | Number(1) |
| Allows to hide the field |
DefaultValue | NVARCHAR2(2000) |
| Sets the default value for the field. For parameter fields this can be an expression returning id of param value. |
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
NAME | VARCHAR2(50) | M | Name of the table field (column), e.g. “LASTNAME” |
TYPEPHASENAME |
|
| Name of the table that provides a set of choices for pop-up (parameter) fields, i.e. TYPE/PHASE fields. |
Datatype | VARCHAR2(20) | M | Type of the field (column): - REAL set in Object Manager |
DataLength | Number | M | Length of the field's value. |
| DataScale | NUMBER (9) | sets the amount of decimal digits for REAL type used in Object Manager | |
Locked | Number(1) | M | 0 = not locked 1 = locked - cannot be deleted |
Required | Number(1) | M | 0 = normal 1 = column is a mandatory object field (M), e.g. ID, CHANGEDATE, STATUS |
ClientFilter | Number(1) | M | 0 = normal |
TableID | Number | M | Link to the corresponding entry in VY_TABLE |
VISIBILITY | Number(1) | M | 1 if the field's visibility setting is set to "ObjectSetGroup" in ObjectManager |
| DESCRIPTION | NVARCHAR2 (2000) |
Column Name | Data Type |
| Description |
| TABLEFIELDID | NUMBER (10) | ||
| SHOWGROUP | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| ID | VARCHAR2 (32 Byte) |
Column Name | Data Type |
| Description |
| TABLEFIELDID | NUMBER (10) | ||
| SHOWGROUP | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| ID | VARCHAR2 (32 Byte) |
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Normal ‘B’ |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
PackageVersionID | Number(9) | M | Link to the corresponding entry in VY_PACKAGE_VERSION |
ObjectType | VARCHAR2(32) | M | Type of the corresponding object, e.g. “VY_PERSON” |
WindowData | BLOB | M | Window data in binary format. It can be imported/saved from/to a file using Object Manager. |
WindowType | Number(1) | M | Type of the window: Vine Windows & Vine Web standard forms are supplied with their installers. These forms are used to work with the API object set. |
SHOWGROUP | Number(9) | M | Defines which window is visible to user |
| CLIENTVERSION_MIN | Number(9) | ||
| CLIENTVERSION_MAX | Number(9) | ||
| DESCRIPTION | NVARCHAR2 (2000) |
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Normal ‘B’ |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
TableID | Number(9) | M | Link to VY_TABLE |
IconData | BLOB | M | Icon data in binary format (.ICO file contents). It can be imported by Object Manager. The .ICO file must include the standard 16x16 and 32x32 formats. |
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: ‘X’ – plugin disbled |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
NAME | NVARCHAR2(50) | M | Name of the plug-in as it is shown in the Vine Plug-In menu |
SHOWGROUP | Number(9) | M | Defines a group that can use the plug-in |
Author | NVARCHAR2(50) | r | Author of the plug-in |
Objecttype | VARCHAR2(50) | M | Vine object type which the plug-in is assigned to, e.g. VY_PERSON or “ALL” if the plug-in can be used with all object types. |
Plugintype | Number(9) | M | Plug-in type: 1 = Application 5 = V4 Tool Tab 6 = V4 Component 7 = Vintage 4.0 Report (*.rpt/*.rep) 8 = Crystal Report (*.rpt) 9 = .Net control 10 = .Net V4 Tool Tab 11 = Mail Wizard Template 12 = Web Plug-in 13 = Word MailMerge |
Serverneeded | Number(1) | M | 0 = Server connection is not needed for the plug-in 1 = Server connection is needed |
Filesize | Number(9) | M | Size of the plug-in file (in bytes). |
Filename | NVARCHAR2(255) | M | Name of the plug-in file |
Plugin | BLOB | M | Plug-in data in binary format (contents of the plug-in file) |
PluginVersion | VARCHAR2(50) | R | Plug-in version |
ClientVersion_min | Number(9) | M | Minimum Vine Windows client version needed to run the plug-in: starts from 31000 = version 3.1 |
| CLIENTVERSION_MAX | NUMBER (9) | ||
Classname | VARCHAR2(500) | M | Class name needed for ActiveX plug-ins. This field is also used to store the web address of a URL plug-in. |
Description | NCLOB(2000) | R | Description of the plug-in |
Autolaunch | Number(1) | R | 0 = plug-in is automatically launched when Vine Windows starts 1 = plug-in is not automatically launched |
Hidden | Number(1) | R | 0 = plug-in is visible to end users |
WindowPositionX | Number(9) | R | The location (in pixels) of the upper left corner of the plug-in window along horizontal (X) axis. Valid for ActiveX and URL plug-ins only. |
WindowPositionY | Number(9) | R | The location (in pixels) of the upper left corner of the plug-in window along vertical (Y) axis. Valid for ActiveX and URL plug-ins only. |
WindowSizeX | Number(9) | R | Initial size (width) of the plug-in window along X-axis. Valid for ActiveX and URL plug-ins only. |
WindowSizeY | Number(9) | R | Initial size (height) of the plug-in window along Y-axis. Valid for ActiveX and URL plug-ins only. |
WindowSizeFixed | Number(1) | R | 0 = plug-in window is resizable 1 = plug-in window size is fixed |
PLUGIN_CATEGORY | Number(9) | R | Plug-in categories are used to group plug-ins on the Tools tab in Vine for Windows and Vine Web. |
| ICON | BLOB |
Column Name | Data Type |
| Description |
ID | Number | M | Unique ID number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
SHOWGROUP | Number | M | Object protection (visibility): |
EDITGROUP | Number | M | Object protection (editing): |
NAME | VARCHAR2(50) | M | Name of the holiday how it is shown in the Vine Calendar |
Year | Number(9) | M | The year of the holiday: |
Day | Number(1) | M | The day of the holiday: 1-31 |
Month | Number(9) | M | The month of the holiday: 1 - 12 |
Color | Number(1) | M | The color of the holiday name and day in the Vine Calendar: 0 = Black 1 = Red |
COUNTRYTYPE | Number(1) | M | Holds country codes from the parameter table vp_countrytype |
Used to store custom table view layouts.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (50) | ||
| OBJECTTYPE | NVARCHAR2 (32) | ||
| FORMDATA | BLOB |
Used to store mailing label layouts.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| NAME | NVARCHAR2 (50) | ||
| LAYOUTTYPE | NUMBER (9) | ||
| DESCRIPTION | CHAR (0 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| SHOWGROUP | NUMBER | ||
| EDITGROUP | NUMBER | ||
| COLUMNCOUNT | NUMBER (9) | ||
| ROWCOUNT | NUMBER (9) | ||
| TOPMARGIN | NUMBER (9) | ||
| BOTTOMMARGIN | NUMBER (9) | ||
| LEFTMARGIN | NUMBER (9) | ||
| RIGHTMARGIN | NUMBER (9) | ||
| BETWEENCOLUMNS | NUMBER (9) | ||
| BETWEENROWS | NUMBER (9) | ||
| COLUMNLIST | BLOB | ||
| SKIPLASTROW | NUMBER (1) | ||
| STARTLABEL | NUMBER (9) | ||
| SORTCOLUMN1 | NUMBER (9) | ||
| SORTCOLUMN2 | NUMBER (9) | ||
| LABELMULTIPLIER | NUMBER (9) | ||
| USEHOMEADDRESS | NUMBER (1) | ||
| USECOMPANYADDRESS | NUMBER (1) | ||
| PREFERADDRESS | NUMBER (1) |
Contains web forms.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| ADDCONNECTIONGROUP | NUMBER (10) | ||
| DELETECONNECTIONGROUP | NUMBER (10) | ||
| OBJECTIMAGE | BLOB | ||
| XMLDATA | XMLTYPE | ||
| FORMSTRUCTURE | NCLOB | ||
| ACTIVEUNTIL | DATE | ||
| EDITORMODEL | NCLOB |
Contains the scores set for each web page.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| EDITGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (200) | ||
| COLOR | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| ADDCONNECTIONGROUP | NUMBER (10) | ||
| DELETECONNECTIONGROUP | NUMBER (10) | ||
| OBJECTIMAGE | BLOB | ||
| TRACKURL | NVARCHAR2 (250) | ||
| URLSCORE | NUMBER (9) | ||
| URLTIME | NUMBER (9,2) | ||
| XMLDATA | XMLTYPE | ||
| ACTIVEUNTIL | DATE |
Stores RSS feeds.
Column Name | Data Type |
| Description |
| ID | NUMBER (10) | ||
| CREATEDATE | DATE | ||
| CREATEUSER | VARCHAR2 (32 Byte) | ||
| LASTUSEDATE | DATE | ||
| STATUS | CHAR (1 Byte) | ||
| SHOWGROUP | NUMBER (10) | ||
| DELETEGROUP | NUMBER (10) | ||
| NAME | NVARCHAR2 (50) | ||
| OBJECTTYPE | VARCHAR2 (20 Byte) | ||
| CONDITION | NCLOB | ||
| DAYLIMIT | NUMBER | ||
| CHANGEDATE | DATE | ||
| CHANGEUSER | VARCHAR2 (32 Byte) | ||
| EDITGROUP | NUMBER (10) | ||
| RSSAREA | NUMBER (9) | VS_RSSAREA |
Objects Catalog Tables
Tables: VY_CONTENTS_MV, VY_INDEX
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
STATUS | Char(1) | M | Status of the entry: |
SHOWGROUP | Number |
| Object protection (visibility): |
OBJECTTYPE | VARCHAR2(50) | M | Type of the object, e.g. VY_PERSON |
NAME | NVARCHAR2(50) | M | Name of the object, e.g. "Lastname Firstname" for a person |
HELP | NVARCHAR2(50) | R | Additional information about the object, e.g. a company name for a person or a client's name for a project |
COLOR | NUMBER |
| Used to set object’s color attribute. |
SORTNAME | NVARCHAR2(50) | M | Sorting name (in uppercase) of the object |
ACTIVEUNTIL | Date |
|
|
INFO | NVARCHAR2(1000) |
|
|
Column Name | Data Type |
| Description |
ID | Number | M | Object ID |
INDEXTYPE | NVARCHAR2(20) | M | Defines the type of the index entry (WORD) |
WORD | NVARCHAR2(50) | M | Index entry (in uppercase). Phone numbers are spelled in reverse order. |
ROWGUID | VARCHAR2(32) | M | Unique primary key used for database replication. |
Object Parameter Tables
The fields of parameter tables are explained and listed in the Parameter Table Fields chapter.
For all parameter fields in tables the Description column includes the name of the referenced parameter table.
For example, this means that the EVENTTYPE field references the VY_EVENTTYPE parameter table.
| EVENTTYPE | NUMBER (9) | VY_EVENTTYPE |
Additional Tables
Tables: VY_MAPLINK, VY_MAPOBJECT, VS_TRANSLATION, VY_STRUCTURE_CHANGE, VY_LOG
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
mapID | Number | M | ID of a map, that contains the object |
TargetObjectID | Number | M | ID of the object |
TargetObjectType | NVARCHAR2(20) | M | Type of the object, e.g. VY_PERSON, VY_CONNECTION... |
positionX | Number | M | The position of the object in pixels from the left of the map window. |
positionY | Number | M | The position of the object in pixels from the top of the map window. |
Note: For a connection bent into an angular form, the POSITIONX, POSITIONY fields store the coordinates of the vertex of the angle. If a connection is straight, these fields are not used.
Column Name | Data Type |
| Description |
ID | Number | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
mapID | Number | M | ID of a map, that contains the link |
fromObjectID | Number | M | ID of the "FROM" object |
TargetObjectID | Number | M | ID of the "TARGET" object |
FromObjectType | NVARCHAR2(20) | M | Type of the "FROM" object |
TargetObjectType | NVARCHAR2(20) | M | Type of the "TARGET" object |
positionX | Number | M | The X coordinate of the vertex of the angle formed when the connection is bent. |
positionY | number | M | The Y coordinate of the vertex of the angle formed when the connection is bent. |
For custom objects, VS_TRANSLATION table is used to store localization texts (previously LNG files were used). You can create and edit localization texts in Object Manager and you can change object field localizations using Object Field Editor in the Web Admin Console. It is possible to have different translations for same named fields in different tables.
Note: default.lng file used, if no relevant translations were found in VS_TRANSLATION.
VS_TRANSLATION is synced to Vine Windows, and is similar to regular object tables. When a new translation entry is created in Object Manager, it gets a negative id and only when stored to the database then a positive id is assigned from VY_OBJECT_SEQUENCE
As VS_TRANSLATION table allows to specify different translations for the same string for different object types, the following rules are used which define what object type must be used in Vine:
- Names of tools in Toolbox – object type not specified
- Labels in charts, filters, HTML text in XML tools - object type not specified
- Names of columns in grids in XML tools – type of objects displayed in grid specified
- Names of commands for Collection tools (static and dynamic collections) – type of collection members specified
Column Name | Data Type |
| Description |
ID | Number(9) | M | Unique number |
Status | Char(1) | M | Status of the entry: |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
CHANGEDATE | Date | M | The timestamp of the last save operation; by default SYSDATE is stored |
CHANGEUSER | VARCHAR2(32) | r | Username in uppercase |
SHOWGROUP | Number | M | Object protection (visibility): |
EDITGROUP | Number | M | Object protection (editing): |
DELETEROUP | Number |
| Object protection: |
AddConnectionGroup, DeleteConnectionGroup | Number |
| Connection protection: > 0 –granted user or user group can manage connections. -3 – connections can be managed only by connected persons. In view -1 is used for “Self” and –2 is used for non-granted objects. |
LANGUAGE | NVARCHAR2(3) |
|
|
ORIGIN | NVARCHAR2(20000) |
|
|
RESULT | NVARCHAR2(20000) |
|
|
AREA | NVARCHAR2(50) |
|
|
OBJECT | NUMBER(9) |
|
|
ACTIVEUNTIL | DATE |
| this field should be used in all tools to filter non-active objects out from regular usage |
Column Name | Data Type |
| Description |
CreateDate | Date | r | The timestamp of the object's creation; by default SYSDATE is stored |
CREATEUSER | VARCHAR2(32) | r | Username in uppercase |
PACKAGENAME | NVARCHAR2(50) | M | Name of the used object set |
OBJECTNAME | NVARCHAR2(50) | M | Name of the object type |
LOGSTR | NVARCHAR2(2000) | M | Log message |
Column Name | Data Type |
| Description |
CONTENT | NCLOB | r | Object Manager creates this table and saves here all the db changes when those are saved to “Draft”. When using OM as the administrator, you can save your db changes either to the database or to “Draft”. When working in the project manager mode, you can only save your changes to “Draft”. When Object Manager starts, it automatically loads the draft changes if it finds any. |
Column Name | Data Type | Description |
| ID | NUMBER (10) | |
| CREATEDATE | DATE | |
| CREATEUSER | VARCHAR2 (32 Byte) | |
| CHANGEDATE | DATE | |
| CHANGEUSER | VARCHAR2 (32 Byte) | |
| STATUS | CHAR (1 Byte) | |
| SHOWGROUP | NUMBER (10) | |
| EDITGROUP | NUMBER (10) | |
| NAME | NVARCHAR2 (500) | |
| ERRORTYPE | NUMBER (9) | |
| ERRORNUMBER | NUMBER (10) | |
| NAME635095112807726278 | VARCHAR2 (500 Byte) |
OBJECT_TABLE and TABLE Views
Field of the View | Source Table |
object_ID | VY_OBJect |
OBJECT_CREATEDATE | VY_OBJect |
OBJECT_createuser | VY_OBJect |
OBJECT_changedate | VY_OBJect |
OBJECT_changeuser | VY_OBJect |
object_status | VY_OBJect |
object_manystr | VY_OBJect |
object_newstr | VY_OBJect |
object_openstr | VY_OBJect |
object_editstr | VY_OBJect |
object_deletestr | VY_OBJect |
object_duplicateSTR | VY_OBJect |
object_name | VY_OBJect |
| MENU_HIDDEN | VY_OBJect |
table_id | VY_table |
table_createdate | VY_table |
table_createuser | VY_table |
table_changedate | VY_table |
table_changeuser | VY_table |
table_status | VY_table |
table_name | VY_table |
| POSITION | VY_table |
packageversion_ID | VY_package_version |
packageversion_name | VY_package_version |
Table VY_OBJECT_TABLE_FIELD_VIEW
Field of the View | Source Table |
objectFIELD_ID | VY_OBJect_FIELD |
OBJECTFIELD_CREATEDATE | VY_OBJect_FIELD |
OBJECTFIELD_createuser | VY_OBJect_FIELD |
OBJECTFIELD_changedate | VY_OBJect_FIELD |
OBJECTFIELD_changeuser | VY_OBJect_FIELD |
objectFIELD_status | VY_OBJect_FIELD |
objectFIELD_name | VY_OBJect_FIELD |
| OBJECTFIELD_HIDDEN | VY_OBJect_FIELD |
tablefield_id | VY_table_field |
tablefield_createdate | VY_table_field |
tablefield_createuser | VY_table_field |
tablefield_changedate | VY_table_field |
tablefield_changeuser | VY_table_field |
tablefield_status | VY_table_field |
tablefield_name | VY_table_field |
tablefield_datatype | VY_table_field |
tablefield_dataLENGTH | VY_table_field |
tablefield_typephasename | VY_table_field |
tablefield_clientfilter | VY_table_field |
table_id | VY_table |
table_name | VY_table |
OBJECt_id | VY_object |
object_name | VY_object |
packageversion_ID | VY_package_version |
packageversion_name | VY_package_version |
Field of the View | Source Field |
ID | VY_OBJect.ID |
tid | VY_table.ID |
status | VY_OBJect.Status |
CREATEDATE | VY_OBJect.CREATEDATE |
createuser | VY_OBJect.createuser |
changedate | VY_OBJect.changedate |
changeuser | VY_OBJect.changeuser |
tname | VY_table.Name |
manystr | VY_OBJect.manystr |
newstr | VY_OBJect.newstr |
openstr | VY_OBJect.openstr |
editstr | VY_OBJect.editstr |
deletestr | VY_OBJect.deletestr |
duplicateSTR | VY_OBJect.duplicateSTR |
oname | VY_OBJect.oname |
Field of the View | Source Field |
oOFID | VY_OBJect_FIELD.ID |
oOFstatus | VY_OBJect_FIELD.status |
oofCREDATE | VY_OBJect_FIELD.CREATEDATE |
Oofcreuser | VY_OBJect_FIELD.createuser |
Oofchadate | VY_OBJect_FIELD.changedate |
Oofchauser | VY_OBJect_FIELD.changeuser |
tfname | VY_table_field.name |
tftype | VY_table_field.datatype |
| TFLENGTH | VY_table_field.datalength |
| TFSCALE | VY_table_field.datascale |
ooFname | VY_OBJect_FIELD.name |
tname | VY_table.name |
tpname | Upper( VY_table_field. |
tffilter | VY_table_FIELD.clientfilter |
| TABLEHIDDEN | vy_table.hidden |
Indexes
Most tables have the following indexes.
Name | Table | Indexed Fields | U | A | Usage notes |
PK_VY_###### | VY_###### | ID, | U |
| Support primary key constraints |
VY_######_CDATE | VY_###### | CHANGEDATE |
|
| Synchronization |
UIndex is unique
Comments
0 comments
Please sign in to leave a comment.