Codes and Statuses [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Codes and Statuses ======
<WRAP center round tip>
Hand-maintained business narrative copied from the source documentation. Keep this separate from regenerated schema pages.
</WRAP>

====== Codes and Statuses Used in IQX ======

This document collects lookup codes, statuses, display groups, selector requirements, system variables, and related conventions that are not fully represented by database comments or lookup tables.

It complements `README_Relationship_Skeleton.md` and the per-table SQL files generated from the SQL Anywhere structure unload.

===== Display Groups =====

^ Questionnaire ^ Display Group ^ Meaning ^
| Company | 1 | Appears ONLY on Questionnaire view |
|  | 0 | Repeated on Headline (specified as *0) |
|  | -10 | Appears at BOTTOM of Accounts / Miscellaneous views |
|  | -11 | Appears at TOP of Accounts / Miscellaneous views |
|  | -19 | Appears at BOTTOM of Accounts - BUT NOT AT ALL on the Old Style Miscellaneous |
|  | -20 | Appears on Company AWR view |
|  | -50 | DO NOT USE Pulse #Company only |
|  | -100 | Sales Consultant Questionnaire view |
|  | -700 | Switchable View 1 |
|  | -1700 | Switchable View 2 |
| Person | 1 | Appears ONLY on Questionnaire view |
|  | 0 | Repeated on Headline (specified as *0) |
|  | -2 | Appears in Headline And questionnaire views. Editable by Compliance only |
|  | -10 | Appears at BOTTOM of Payroll view |
|  | -11 | Appears at TOP of Payroll view |
|  | -12 | Appears at TOP of Payroll view. Editable by Accounts only |
|  | -19 | Appears in P45 wizard |
|  | -20 to -199 | Customer use only |
|  | -200 to -290 | Various Clearance Views |
|  | -300 | Care Recipient Questionnaire and Headline |
|  | -310 | Care Recipient Questionnaire only |
|  | -400 | CIS 07 view |
|  | -700 | Switchable View 1 |
|  | -710 | Switchable View 2 |
|  | -1700 | Switchable View 3 |
|  | -1710 | Switchable View 4 |
|  | -1720 | Switchable View 5 |
|  | -2000 | New person wizard only |
| Placement | -3 | Departmental only. Appears at top of questionnaire, editable by accounts only |
|  | -10 | Departmental only. Appears second top of questionnaire, editable by manager role only |
| Vacancy | 1 | Appear in Questionnaire ONLY. |
|  | 0 | Appear in Questionnaire AND top section. |
|  | -10 | Appears in BOTTOM of Miscellaneous view. |
|  | -20 | Appears on Vacancy AWR view |
|  | -50 | Appears on Vacancy Web Publish view (of enabled) |
|  | -200 | Appears on Vacancy Compliance Questions view. Editable by Compliance only |
| Applies Widely | >=0 | if * specified |
|  | <0 | only appears if individually specified (e.g. *-30 for a group or else the tagid of an individual item) |
|  | <= -1000 | -1000 only appear in **search criteria** if individually specified in a role - this is for the highly sensitive stuff |



===== Blobstore Class =====

^ Code ^ Type ^ Meaning of ID ^
| B | Branch Image | AgencyDetailsID or ALLBRANCHES |
| D | Brand Images | BrandImage.BrandImageID |
| E | Html Document Images | htmlimage.htmlimageid |
| G | Global Document | globaldocument.globaldocumentID |
| I | Invoice PDF | iqacDocument.documentid |
| J | Photo | Person.PersonID |
| 2 | Thumbnail Photo | Person.PersonID |
| L | Letters | ContactEvent.ContactEventID |
| M | MergeLetters | MailMerge.MailMergeID |
| N | Consultant signature JPEG | Staff.StaffID |
| O | OLE Documents (ie attached documents) | OleDocument.OLEDocumentID |
| P | Payroll Documents (Payslips, P60, P45) | PayrollDocument.PayrollDocumentID |
| R | Reports and jobs, also Report etc Images | Report.ReportID |
| S | Secure Document for sending by SecureDocumentLink mechanism | SecureDocumentLink.SecureDocumentLinkID |
| T | CV & Letter Templates | TemplateStore.TemplateID |
| V | CVs | Person.PersonID |
| W | Web Reports | WebReport.WebReportID |
| Z | Compliance Document Stamp or Watermark | - |
| 8 | Broadbean CVs | broadbeancandidate.broadbeancandidateid |
| 5 | Temporary Stream Storage between jobs | - |



===== OLEDocument OwnerType =====

^ Code ^ Meaning ^
| P | Person |
| C | Company |
| V | Vacancy |
| R | Progress |
| L | Placement |
| Q | TagCompliance |
| TI | Timesheet Image (no ownerid - stored with timesheet) Description MUST be unique for extracting to add to invoices - oledocumentid |
| TU | Timesheet Unlinked Image. Description will be the unlinked filename |
| TE | Timesheet Extra Documents. |
| ~ | Temporary Document, stored temporarily for opening in viewer or similar reason |


===== DocumentType Class =====

^ Class ^ Meaning ^
| C | Company |
| G | Web |
| L | Placement |
| P | Person |
| R | Progress |
| V | Vacancy |


===== TemplateStore TemplateType =====

^ Code ^ Meaning ^
| L | Letter |
| F | Fax Header |
| V | CV |


===== Tag Tables TagLocation =====

^ Code ^ Meaning ^ Meaning of ID field in `TagValue` ^
| A% | Departmental Candidate | PersonID |
| C | Company | CompanyID |
| E | Contact | EmploymentID |
| E% | Departmental Contact | EmploymentID |
| L | Placement | PlacementID |
| L% | Departmental Placement | PlacementID |
| N | General Contact Event | ContactEventID |
| N% | Departmental Contact Event | ContactEventID |
| P | General Candidate | PersonID |
| V | General Vacancy | VacancyID |
| V% | Departmental Vacancy | VacancyID |
| U | Staff | StaffID |
| I | Purchase Account | AccountCode |
| R% | Reference Departmental | ReferenceRequestID |
| S | Temp Shift Plan | TempShiftPlanID |
| S% | DepartmentalTemp Shift Plan | TempShiftPlanID |
| T | Timesheet | TempTimesheetID or TempProvTimesheetID |
| T% | Departmental Timesheet | TempTimesheetID or TempProvTimesheetID |
| X | Agency | AgencyID |
| JXX | Collections | CollectionID |


`XX` indicates the `collection question link field`

`%` indicates the `departmentid`

===== Tag Tables TagType =====

^ Code ^ Meaning ^
| S | Scored |
| L | Multiple Selection |
| - | Heading |
| M | Single Selection |
| N | Numeric |
| G | Graded |
| T | Text |
| U | Upper Text |
| D | Date |
| Q | Sub-Menu Selection |




===== Person MaritalStatus =====

^ Code ^ Meaning ^
| M | Married |
| S | Single |
| D | Divorced |
| X | Separated |
| W | Widowed |
| P | Partnered |
| E | Engaged |


===== ContactEvent Who =====

^ Code ^ Meaning ^
| A | Candidate |
| C | Client |
| B | Both |


==== Phone.Who ====

^ Code ^ Meaning ^
| C | Company |
| P | Person |
| CP | Contact |
| U | Additional staff emails used by EWS |


===== Person.Sex =====

^ Code ^ Meaning ^
| M | Male |
| F | Female |


===== Return Codes in Validation Functions =====

For all: empty string indicates validation passed
**Note:** NB the string must be entirely empty so NOT `string(char(9),empty string)`
 
For pre-validation failures format is:

<code>
string('X','YYYYYYYYYYYYYYYYYYYY',char(9),'Message Text to be displayed in dialogue box')
</code>

where X is:

^ Code ^ Record opened ^
| P | Person record to be opened |
| V | Vacancy record to be opened |
| L | Placement record to be opened |
| C | Company record to be opened |
| E | Employment record to be opened |
| T | Timesheet record to be opened |
| I | Invoice/credit note record to be opened |

and YYYYYYYYYYYYYYYYYYYY is the ID of the record to be opened  
To open a record with a specified view insert >>VIEWNAME before the char(9)

<code>
string('X','YYYYYYYYYYYYYYYYYYYY','>>VIEWNAME',char(9),'Message Text to be displayed in dialogue box')
</code>

**Note:** ONLY supported in v2.16.10 or later - will cause errors in older versions
 
For error message without opening a record:

<code>
string(char(9),'Message Text to be displayed in dialogue box')
</code>
 
For warning message without opening a record:

<code>
string('^',char(9),'Message Text to be displayed in dialogue box')
</code>

The ^ before the X value means it is a warning and the user is given the choice to continue or cancel.

===== Return Codes from EmployeeAcceptable =====

^ Code ^ Meaning ^
| NULL | Person Acceptable for this Vacancy |
| W | Person withheld from this Company |
| V | Person withheld from this Vacancy |
| X | Person exclusive to a different Vacancy |
| C | Person currently permanently employed by Company |
| R | Person permanently employed by Company within last year |


===== Minimum Table Lists for Reports from Selectors =====

^ Selector ^ Tables ^
| Miscellaneous | None |
| Person | Person |
| Company | Company |
| Contact | Person key join Employment key join Company |
| Vacancy | Vacancy key join Employment key join (Company,Person), Vacancy key join Staff, Vacancy join Status on Vacancy.Status = Status.Status and Status.Type = 'V' |
| Placement | Placement key join (Staff, Employment key join (Person, Company)), Placement key left outer join (Vacancy key join Employment cemployment key join Person cperson) |
| Progress | Progress key join (Person, Vacancy key join ( Department, Staff, Employment key join (Person cperson, Company))), Progress join Status on Status.Status = Progress.Status and Status.Type = 'R' |
| ContactEvent | ContactView |
| Accounts | None |
| TempDesk | None - but essential parameters (in order): Desk (string) [TempDeskID], WorM (string) [W for weekly period or M for Monthly], Week (number), Year (number), StartDate (date), EndDate (date) |
| User | Staff |



===== Staff Rights in Staff Table =====

^ Field/value ^ Meaning ^
| Access1 | Maint rights: 1 = all, 2 = partial |
| Access2 | Edit rights 0 - None, 1 - Own, 2 - All |
| Access3 | Edit Letters 0 - No 1, Yes |
| Access4 | Accounts 0 - None, 1 - Primary Function, 2 - Yes |
| Wpletters | create letters |
| Caneedittemplates = 1 | doc templates (needs access1 = 2) |
| Departmentmaint=1 | all depts, 2 selected depts (needs access1 = 2) see department maint table |
| Empquestmain = 1 | contact questionnaire (needs access1 = 2) |
| Rateschemmaint = 1 | temp rate schemes (needs access1 = 2) |



===== Fixed Placement Element Tokens =====

^ Token ^ Meaning ^
| {MANAGER} | The user has Manager Status |
| {PLACEMENTID} |  |
| {VACANCYID} |  |
| {SALARY} |  |




===== IQX System Variables =====

^ Name ^ Data Type ^ Meaning ^
| UserStaffID | char(20) | contains the StaffID of the logged on User |
| UniqueRoll | integer | used as a seed by the UniqueKey function |
| UserAgencyID | char(20) | AgencyBranch.AgencyID of logged on User |
| HomeCurrency | char(3) | Home Currency of system |
| DefaultCurrency | char(3) | Default Currency |



===== IQX System Tables =====

^ Name ^ Columns ^ Meaning ^
| DivisionsAllowed | DivisionID char(20) | Populated on login or if a specific division is selected, with list of divisions user is currently allowed / wishes to view |


===== Stored Selection Type =====

^ Code ^ Meaning ^ Record required in `StoredSelectionMember` ^
| A | Secondary Agency | CompanyID |
| C | Company | CompanyID |
| P | Person | PersonID |
| S | Company Contact | EmploymentID |
| V | Vacancy | VacancyID |




===== Report Type =====

^ Code ^ Meaning ^
| AX | Accounts |
| CC | Company |
| CP | Company Contact |
| D | Desktop |
| E | Contact Event |
| IMG | Email, Web & Report Images |
| J | Collection |
| L | Placement |
| P | Person |
| R | Progress |
| T | Temp Desk |
| U | User |
| V | Vacancy |
| XX | Miscellaneous |
| XZ | System Jobs & Reports |
| Z | Compliance |



===== Shift States =====

^ State ^ Meaning ^
| C | Cancelled |
| P | Provisional |
| B | Booked |
| W | Worked |
| A | Available |
| H | Holiday |
| U | Unavailable |



===== Trigger Order =====

^ Usage ^ Order range ^
| Standard IQX | < 1000 |
| SB | 1000-1999 |
| GJW | 2000-2499 |
| JB | 2500-2999 |
| MHS | 3000-3999 |
| Clients | 4000 > |


===== iqacJournal XRef Meanings =====

^ XRef column ^ XRefID column contents ^ Usage ^
| T | TimesheetLineID | TS Line |
| F | TimesheetLineID | Fixed NI |
| W | TimesheetLineID | Fixed WTR |
| I | TimesheetLineID | ERNI Recharge (*per* Timesheet Line) |
| N | TimesheetID | ERNI Recharge (*per* Timesheet) |


===== iqacDocument XRef Meanings =====

^ XRef column ^ XRefID column contents ^ Usage ^
| T | TimesheetID | Timesheet |
| P | PlacementID | Placement |
| V | VacancyID | Vacancy |
| D | TempDeskID | Tempdesk |


===== Data Dictionary Areas =====

^ DictionaryLocation ^ Meaning ^
| C | Company |
| E | Client Contact |
| P | Person |
| V | Vacancy |
| L | Placement |
| N | Contact Event |
| I | Care Recipient |
| U | User |


===== Data Dictionary Tokens =====

==== used in Database Item and Complex Expression ====

^ Token ^ Meaning ^
| `<D1>` | the Start Date for a date range - if displayed but left blank by the searcher a default of 1899-12-30 is assumed |
| `<D2>` | the End Date for a date range - default as `<D1>` |
| `<SVALUE>` | if specified a text entry box is displayed and the token returns the raw string typed in by the searcher - not typed, escaped or quoted so care required to ensure a valid SQL expression is formed |
| `<QVALUE>` | a safely quoted version of `<SVALUE>` |
| `<X1>` | the primary key of the calling record. eg in the case of a search for candidates from a vacancy, the VacancyID. Typed as string so no additional quotes needed |
| `<EXPRESSION>` | a SQL fragment generated from the Database Item and conditions set by the searcher - if used with an Indexed Text dictionary type it indicated that Indexing Services (ie external) rather than internal text indexes should be used |
| `<N1>` | the Start Value for a numeric range- if displayed but left blank by the searcher a default of 0 is assumed |
| `<N2>` | the End Value for a numeric range - default as `<N1>` |

The presence of unexpected tokens, such as date tokens in a numeric question, will cause additional data entry fields of that type to be displayed in addition to the automatically included ones related to the question type. This does not apply to `<X1>` or `<EXPRESSION>`, which do not generate their own data entry fields.