VaUHKt4i

1Skip to content
2This repository
3Search
4Pull requests
5Issues
6Marketplace
7Explore
8 @nisheshkalakheti
9 Sign out
105
110 0 Hodes-Inc/xca-hrlogix-xcloud-migration Private
12 Code  Issues 0  Pull requests 0  Projects 0  Wiki  Insights
13xca-hrlogix-xcloud-migration/xcloud_wardtrucking_ddl.sql
1481adfe2  on Nov 10, 2017
15@yogesh-st yogesh-st Hrlogix to xcloud migration script
16     
175829 lines (5423 sloc)  219 KB
18-- MySQL Script generated by MySQL Workbench
19-- Wed Jul  5 15:25:05 2017
20-- Model: New Model    Version: 1.0
21-- MySQL Workbench Forward Engineering
22
23SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
24SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
25SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
26
27-- -----------------------------------------------------
28-- Schema mydb
29-- -----------------------------------------------------
30
31-- -----------------------------------------------------
32-- Table `transition`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `transition` (
35  `transition_id` INT(20) NOT NULL AUTO_INCREMENT,
36  `name` CHAR(40) NOT NULL DEFAULT '',
37  PRIMARY KEY (`transition_id`))
38ENGINE = InnoDB
39AUTO_INCREMENT = 207
40DEFAULT CHARACTER SET = latin1;
41
42
43-- -----------------------------------------------------
44-- Table `resource`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `resource` (
47  `resource_id` INT(20) NOT NULL AUTO_INCREMENT,
48  `name` CHAR(40) NOT NULL DEFAULT '',
49  `method` CHAR(40) NOT NULL DEFAULT '',
50  `FILE` CHAR(40) NOT NULL DEFAULT '',
51  `address` CHAR(40) NOT NULL DEFAULT '',
52  `uname` CHAR(40) NOT NULL DEFAULT '',
53  `password` CHAR(40) NOT NULL DEFAULT '',
54  PRIMARY KEY (`resource_id`))
55ENGINE = InnoDB
56AUTO_INCREMENT = 208
57DEFAULT CHARACTER SET = latin1;
58
59
60-- -----------------------------------------------------
61-- Table `activity_resource`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `activity_resource` (
64  `transition_id` INT(20) NOT NULL DEFAULT '0',
65  `resource_id` INT(20) NOT NULL DEFAULT '0',
66  PRIMARY KEY (`transition_id`, `resource_id`),
67  INDEX `transition_asso_ndx` (`transition_id` ASC),
68  INDEX `resource_asso_ndx` (`resource_id` ASC),
69  CONSTRAINT `0_1322`
70    FOREIGN KEY (`transition_id`)
71    REFERENCES `transition` (`transition_id`)
72    ON DELETE CASCADE
73    ON UPDATE CASCADE,
74  CONSTRAINT `0_1323`
75    FOREIGN KEY (`resource_id`)
76    REFERENCES `resource` (`resource_id`)
77    ON DELETE CASCADE
78    ON UPDATE CASCADE)
79ENGINE = InnoDB
80DEFAULT CHARACTER SET = latin1;
81
82
83-- -----------------------------------------------------
84-- Table `groups`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `groups` (
87  `groups_id` INT(11) NOT NULL AUTO_INCREMENT,
88  `role` CHAR(50) NULL DEFAULT NULL,
89  PRIMARY KEY (`groups_id`))
90ENGINE = InnoDB
91AUTO_INCREMENT = 10
92DEFAULT CHARACTER SET = latin1;
93
94
95-- -----------------------------------------------------
96-- Table `hierarchy`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `hierarchy` (
99  `hierarchy_id` INT(11) NOT NULL AUTO_INCREMENT,
100  `name` CHAR(50) NULL DEFAULT NULL,
101  `parent` INT(11) NULL DEFAULT NULL,
102  PRIMARY KEY (`hierarchy_id`),
103  INDEX `parent` (`parent` ASC))
104ENGINE = InnoDB
105AUTO_INCREMENT = 114
106DEFAULT CHARACTER SET = latin1;
107
108
109-- -----------------------------------------------------
110-- Table `req_group`
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS `req_group` (
113  `req_group_id` INT(11) NOT NULL AUTO_INCREMENT,
114  `name` CHAR(40) NOT NULL DEFAULT '',
115  PRIMARY KEY (`req_group_id`))
116ENGINE = InnoDB
117AUTO_INCREMENT = 42
118DEFAULT CHARACTER SET = latin1;
119
120
121-- -----------------------------------------------------
122-- Table `users`
123-- -----------------------------------------------------
124CREATE TABLE IF NOT EXISTS `users` (
125  `users_id` INT(20) NOT NULL AUTO_INCREMENT,
126  `uname` VARCHAR(40) NOT NULL DEFAULT '',
127  `groups_id` INT(11) NOT NULL DEFAULT '0',
128  `hierarchy_id` INT(11) NOT NULL DEFAULT '0',
129  `req_group_id` INT(11) NOT NULL DEFAULT '0',
130  `password` VARCHAR(20) NOT NULL DEFAULT '',
131  `pw_change` CHAR(1) NULL DEFAULT 'f',
132  `first_name` VARCHAR(20) NULL DEFAULT NULL,
133  `last_name` VARCHAR(20) NULL DEFAULT NULL,
134  `phone` VARCHAR(15) NULL DEFAULT NULL,
135  `email` VARCHAR(75) NULL DEFAULT NULL,
136  `signature` VARCHAR(50) NULL DEFAULT NULL,
137  `status` ENUM('active', 'inactive', 'terminated') NOT NULL DEFAULT 'active',
138  `bgc_id` VARCHAR(100) NULL DEFAULT NULL,
139  `bgc_pw` VARCHAR(50) NULL DEFAULT NULL,
140  `bgc_account` VARCHAR(50) NULL DEFAULT NULL,
141  `xp_client_id` VARCHAR(255) NULL DEFAULT NULL,
142  `xp_bu` VARCHAR(255) NULL DEFAULT NULL,
143  `xp_user_id` VARCHAR(255) NULL DEFAULT NULL,
144  `wf_delegate` INT(10) NULL DEFAULT NULL,
145  `newsletter` CHAR(1) NULL DEFAULT NULL,
146  `user_prefs` TEXT NULL DEFAULT NULL,
147  PRIMARY KEY (`users_id`),
148  UNIQUE INDEX `uname` (`uname` ASC),
149  INDEX `groups_ndx` (`groups_id` ASC),
150  INDEX `hierarchy_ndx` (`hierarchy_id` ASC),
151  INDEX `req_group_id` (`req_group_id` ASC),
152  INDEX `password` (`password` ASC),
153  INDEX `status` (`status` ASC),
154  CONSTRAINT `0_3293`
155    FOREIGN KEY (`groups_id`)
156    REFERENCES `groups` (`groups_id`)
157    ON DELETE CASCADE
158    ON UPDATE CASCADE,
159  CONSTRAINT `0_3294`
160    FOREIGN KEY (`hierarchy_id`)
161    REFERENCES `hierarchy` (`hierarchy_id`)
162    ON DELETE CASCADE
163    ON UPDATE CASCADE,
164  CONSTRAINT `users_ibfk_2`
165    FOREIGN KEY (`req_group_id`)
166    REFERENCES `req_group` (`req_group_id`)
167    ON DELETE CASCADE
168    ON UPDATE CASCADE)
169ENGINE = InnoDB
170AUTO_INCREMENT = 564
171DEFAULT CHARACTER SET = latin1;
172
173
174-- -----------------------------------------------------
175-- Table `address`
176-- -----------------------------------------------------
177CREATE TABLE IF NOT EXISTS `address` (
178  `address_id` INT(10) NOT NULL AUTO_INCREMENT,
179  `users_id` INT(10) NOT NULL DEFAULT '0',
180  `address` VARCHAR(75) NULL DEFAULT NULL,
181  PRIMARY KEY (`address_id`),
182  UNIQUE INDEX `address` (`users_id` ASC, `address` ASC),
183  INDEX `users_id` (`users_id` ASC),
184  CONSTRAINT `address_ibfk_1`
185    FOREIGN KEY (`users_id`)
186    REFERENCES `users` (`users_id`)
187    ON DELETE CASCADE
188    ON UPDATE CASCADE)
189ENGINE = InnoDB
190AUTO_INCREMENT = 89
191DEFAULT CHARACTER SET = latin1;
192
193
194-- -----------------------------------------------------
195-- Table `agency`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `agency` (
198  `agency_id` INT(11) NOT NULL AUTO_INCREMENT,
199  `uname` VARCHAR(60) NOT NULL,
200  `active` VARCHAR(1) NOT NULL DEFAULT 't',
201  `password` VARCHAR(30) NOT NULL,
202  `name` VARCHAR(60) NULL DEFAULT NULL,
203  `email` VARCHAR(75) NULL DEFAULT NULL,
204  `contact_name` VARCHAR(30) NULL DEFAULT NULL,
205  `phone` VARCHAR(15) NULL DEFAULT NULL,
206  `address1` VARCHAR(30) NULL DEFAULT NULL,
207  `address2` VARCHAR(30) NULL DEFAULT NULL,
208  `city` VARCHAR(30) NULL DEFAULT NULL,
209  `state` VARCHAR(2) NULL DEFAULT NULL,
210  `zip` VARCHAR(9) NULL DEFAULT NULL,
211  `province` VARCHAR(2) NULL DEFAULT NULL,
212  `country` VARCHAR(50) NULL DEFAULT NULL,
213  PRIMARY KEY (`agency_id`),
214  UNIQUE INDEX `Key_Constraint_1` (`uname` ASC),
215  INDEX `Index_1` (`uname` ASC, `password` ASC))
216ENGINE = InnoDB
217DEFAULT CHARACTER SET = latin1;
218
219
220-- -----------------------------------------------------
221-- Table `question_group`
222-- -----------------------------------------------------
223CREATE TABLE IF NOT EXISTS `question_group` (
224  `question_group_id` INT(20) NOT NULL AUTO_INCREMENT,
225  `name` CHAR(50) NULL DEFAULT NULL,
226  `active` CHAR(1) NULL DEFAULT 't',
227  PRIMARY KEY (`question_group_id`))
228ENGINE = InnoDB
229AUTO_INCREMENT = 515
230DEFAULT CHARACTER SET = latin1;
231
232
233-- -----------------------------------------------------
234-- Table `view_group`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `view_group` (
237  `view_group_id` INT(20) NOT NULL AUTO_INCREMENT,
238  `name` CHAR(40) NULL DEFAULT NULL,
239  `question_group_id` INT(20) NULL DEFAULT NULL,
240  `isHireLogix` CHAR(1) NULL DEFAULT NULL,
241  `type` VARCHAR(30) NOT NULL,
242  PRIMARY KEY (`view_group_id`),
243  INDEX `question_group_id` (`question_group_id` ASC),
244  CONSTRAINT `view_group_ibfk_1`
245    FOREIGN KEY (`question_group_id`)
246    REFERENCES `question_group` (`question_group_id`)
247    ON DELETE SET NULL
248    ON UPDATE CASCADE)
249ENGINE = InnoDB
250AUTO_INCREMENT = 22
251DEFAULT CHARACTER SET = latin1;
252
253
254-- -----------------------------------------------------
255-- Table `requisition`
256-- -----------------------------------------------------
257CREATE TABLE IF NOT EXISTS `requisition` (
258  `requisition_id` INT(20) NOT NULL AUTO_INCREMENT,
259  `hierarchy_id` INT(20) NULL DEFAULT NULL,
260  `users_id` INT(20) NULL DEFAULT NULL,
261  `apply_stream_id` INT(11) NULL DEFAULT NULL,
262  `approval_chain_id` INT(11) NULL DEFAULT NULL,
263  `title` VARCHAR(40) NOT NULL DEFAULT '',
264  `status` VARCHAR(40) NULL DEFAULT NULL,
265  `persona` VARCHAR(16) NULL DEFAULT NULL,
266  `post_type` VARCHAR(10) NULL DEFAULT NULL,
267  `post_code` VARCHAR(10) NULL DEFAULT NULL,
268  `post_date` DATETIME NULL DEFAULT NULL,
269  `internal_dec` TEXT NULL DEFAULT NULL,
270  `external_desc` TEXT NULL DEFAULT NULL,
271  `short_desc` TEXT NULL DEFAULT NULL,
272  `requirements` TEXT NULL DEFAULT NULL,
273  `shift` VARCHAR(50) NULL DEFAULT NULL,
274  `job_city` VARCHAR(50) NULL DEFAULT NULL,
275  `job_state` CHAR(2) NULL DEFAULT NULL,
276  `job_zip` VARCHAR(10) NULL DEFAULT NULL,
277  `job_country` VARCHAR(64) NULL DEFAULT NULL,
278  `company` VARCHAR(50) NULL DEFAULT NULL,
279  `division` VARCHAR(50) NULL DEFAULT NULL,
280  `department` VARCHAR(50) NULL DEFAULT NULL,
281  `grade` VARCHAR(50) NULL DEFAULT NULL,
282  `job_code` VARCHAR(50) NULL DEFAULT NULL,
283  `cost_center` VARCHAR(50) NULL DEFAULT NULL,
284  `eeo_code` VARCHAR(10) NULL DEFAULT NULL,
285  `location` VARCHAR(50) NULL DEFAULT NULL,
286  `phone` VARCHAR(50) NULL DEFAULT NULL,
287  `work_sched` VARCHAR(50) NULL DEFAULT NULL,
288  `hours` VARCHAR(50) NULL DEFAULT NULL,
289  `travel_required` VARCHAR(50) NULL DEFAULT NULL,
290  `position_code` VARCHAR(50) NULL DEFAULT NULL,
291  `education_req` VARCHAR(50) NULL DEFAULT NULL,
292  `job_level` VARCHAR(50) NULL DEFAULT NULL,
293  `created` DATETIME NULL DEFAULT NULL,
294  `payrate` VARCHAR(50) NULL DEFAULT NULL,
295  `compensation` VARCHAR(50) NULL DEFAULT NULL,
296  `date_needed` DATE NULL DEFAULT NULL,
297  `start_date` DATE NULL DEFAULT NULL,
298  `req_reason` VARCHAR(150) NULL DEFAULT NULL,
299  `who_replacing` VARCHAR(80) NULL DEFAULT NULL,
300  `budgeted` CHAR(1) NULL DEFAULT NULL,
301  `reports_to` VARCHAR(80) NULL DEFAULT NULL,
302  `modified` DATETIME NULL DEFAULT NULL,
303  `views` INT(10) NULL DEFAULT '0',
304  `screen_job_value` INT(4) NULL DEFAULT NULL,
305  `screen_general_value` INT(4) NULL DEFAULT NULL,
306  `screen_interview_value` INT(4) NULL DEFAULT NULL,
307  `screen_total_value` INT(4) NULL DEFAULT NULL,
308  `openings` INT(5) NULL DEFAULT NULL,
309  `auto_close` CHAR(1) NULL DEFAULT NULL,
310  `advanced_scheduler` CHAR(1) NULL DEFAULT NULL,
311  `hiring_manager` INT(20) NULL DEFAULT NULL,
312  `send_to_hm` CHAR(1) NULL DEFAULT NULL,
313  `global_tpl` CHAR(1) NULL DEFAULT NULL,
314  `screen_in_msg` TEXT NULL DEFAULT NULL,
315  `screen_out_msg` TEXT NULL DEFAULT NULL,
316  `template` CHAR(1) NULL DEFAULT NULL,
317  `category` VARCHAR(50) NULL DEFAULT NULL,
318  `int_form_group_id` INT(10) NULL DEFAULT NULL,
319  `ext_form_group_id` INT(10) NULL DEFAULT NULL,
320  `view_group_id` INT(10) NULL DEFAULT '1',
321  `cross_posting_status` CHAR(1) NULL DEFAULT NULL,
322  `cross_posting_sites` BLOB NULL DEFAULT NULL,
323  `skills_testing` VARCHAR(150) NULL DEFAULT NULL,
324  `business_partner` VARCHAR(50) NULL DEFAULT NULL,
325  `add_explain` VARCHAR(255) NULL DEFAULT NULL,
326  `add_import` VARCHAR(255) NULL DEFAULT NULL,
327  `add_income` CHAR(1) NULL DEFAULT NULL,
328  `funds_available` CHAR(1) NULL DEFAULT NULL,
329  `job_type` VARCHAR(50) NULL DEFAULT NULL,
330  `property` VARCHAR(50) NULL DEFAULT NULL,
331  `tax_location_code` CHAR(25) NULL DEFAULT NULL,
332  `ob_template_id` INT(10) NULL DEFAULT NULL,
333  `assmt_position_code` VARCHAR(50) NULL DEFAULT NULL,
334  `flsa` VARCHAR(15) NULL DEFAULT NULL,
335  `offer_template_id` INT(11) NULL DEFAULT NULL,
336  `latitude` DOUBLE(10,7) NULL DEFAULT NULL,
337  `longitude` DOUBLE(10,7) NULL DEFAULT NULL,
338  `city` VARCHAR(20) NULL DEFAULT NULL,
339  `state` VARCHAR(20) NULL DEFAULT NULL,
340  `zip` VARCHAR(9) NULL DEFAULT NULL,
341  `update_date` TIMESTAMP NULL DEFAULT NULL,
342  `sec_view_group_id` INT(10) NULL DEFAULT NULL,
343  `min_experience` INT(10) NULL DEFAULT NULL,
344  `max_experience` INT(10) NULL DEFAULT NULL,
345  `number_1` INT(11) NULL DEFAULT NULL,
346  `number_2` INT(11) NULL DEFAULT NULL,
347  `number_3` INT(11) NULL DEFAULT NULL,
348  `number_4` INT(11) NULL DEFAULT NULL,
349  `number_5` INT(11) NULL DEFAULT NULL,
350  `char1_1` CHAR(1) NULL DEFAULT NULL,
351  `char1_2` CHAR(1) NULL DEFAULT NULL,
352  `char1_3` CHAR(1) NULL DEFAULT NULL,
353  `char1_4` CHAR(1) NULL DEFAULT NULL,
354  `char1_5` CHAR(1) NULL DEFAULT NULL,
355  `string64_1` VARCHAR(64) NULL DEFAULT NULL,
356  `string64_2` VARCHAR(64) NULL DEFAULT NULL,
357  `string64_3` VARCHAR(64) NULL DEFAULT NULL,
358  `string64_4` VARCHAR(64) NULL DEFAULT NULL,
359  `string64_5` VARCHAR(64) NULL DEFAULT NULL,
360  `string128_1` VARCHAR(128) NULL DEFAULT NULL,
361  `string128_2` VARCHAR(128) NULL DEFAULT NULL,
362  `string128_3` VARCHAR(128) NULL DEFAULT NULL,
363  `string128_4` VARCHAR(128) NULL DEFAULT NULL,
364  `string128_5` VARCHAR(128) NULL DEFAULT NULL,
365  `text_1` TEXT NULL DEFAULT NULL,
366  `text_2` TEXT NULL DEFAULT NULL,
367  `text_3` TEXT NULL DEFAULT NULL,
368  `text_4` TEXT NULL DEFAULT NULL,
369  `text_5` TEXT NULL DEFAULT NULL,
370  `title_adp_pos` VARCHAR(50) NULL DEFAULT NULL,
371  PRIMARY KEY (`requisition_id`),
372  INDEX `ext_form_group_id` (`ext_form_group_id` ASC),
373  INDEX `int_form_group_id` (`int_form_group_id` ASC),
374  INDEX `users_id` (`users_id` ASC),
375  INDEX `hierarchy_id` (`hierarchy_id` ASC),
376  INDEX `status` (`status` ASC),
377  INDEX `template` (`template` ASC),
378  INDEX `global_tpl` (`global_tpl` ASC),
379  INDEX `post_code` (`post_code` ASC),
380  INDEX `view_group_id` (`view_group_id` ASC),
381  INDEX `big_test` (`requisition_id` ASC, `users_id` ASC, `title` ASC, `status` ASC, `template` ASC),
382  INDEX `latitude` (`latitude` ASC),
383  INDEX `longitude` (`longitude` ASC),
384  INDEX `update_date` (`update_date` ASC),
385  CONSTRAINT `requisition_ibfk_1a`
386    FOREIGN KEY (`int_form_group_id`)
387    REFERENCES `view_group` (`view_group_id`)
388    ON DELETE SET NULL
389    ON UPDATE CASCADE,
390  CONSTRAINT `requisition_ibfk_2a`
391    FOREIGN KEY (`ext_form_group_id`)
392    REFERENCES `view_group` (`view_group_id`)
393    ON DELETE SET NULL
394    ON UPDATE CASCADE,
395  CONSTRAINT `requisition_ibfk_3a`
396    FOREIGN KEY (`users_id`)
397    REFERENCES `users` (`users_id`),
398  CONSTRAINT `requisition_ibfk_4a`
399    FOREIGN KEY (`hierarchy_id`)
400    REFERENCES `hierarchy` (`hierarchy_id`),
401  CONSTRAINT `requisition_ibfk_5a`
402    FOREIGN KEY (`view_group_id`)
403    REFERENCES `view_group` (`view_group_id`)
404    ON DELETE SET NULL
405    ON UPDATE CASCADE)
406ENGINE = InnoDB
407AUTO_INCREMENT = 40793
408DEFAULT CHARACTER SET = latin1;
409
410
411-- -----------------------------------------------------
412-- Table `candidate`
413-- -----------------------------------------------------
414CREATE TABLE IF NOT EXISTS `candidate` (
415  `candidate_id` INT(20) NOT NULL AUTO_INCREMENT,
416  `users_id` INT(20) NULL DEFAULT NULL,
417  `uname` VARCHAR(75) NOT NULL DEFAULT '',
418  `password` VARCHAR(20) NOT NULL DEFAULT '',
419  `persona` VARCHAR(16) NULL DEFAULT NULL,
420  `first_name` VARCHAR(20) NULL DEFAULT NULL,
421  `middle_name` CHAR(1) NULL DEFAULT NULL,
422  `last_name` VARCHAR(20) NULL DEFAULT NULL,
423  `suffix` VARCHAR(15) NULL DEFAULT NULL,
424  `dob` DATE NULL DEFAULT NULL,
425  `ssn` VARCHAR(10) NULL DEFAULT NULL,
426  `ssn_name` VARCHAR(100) NULL DEFAULT NULL,
427  `other_name` VARCHAR(50) NULL DEFAULT NULL,
428  `maiden_name` VARCHAR(50) NULL DEFAULT NULL,
429  `address1` VARCHAR(30) NULL DEFAULT NULL,
430  `address2` VARCHAR(30) NULL DEFAULT NULL,
431  `city` VARCHAR(30) NULL DEFAULT NULL,
432  `state` CHAR(2) NULL DEFAULT NULL,
433  `zip` VARCHAR(9) NULL DEFAULT NULL,
434  `mail_address` VARCHAR(50) NULL DEFAULT NULL,
435  `mail_city` VARCHAR(100) NULL DEFAULT NULL,
436  `mail_state` CHAR(2) NULL DEFAULT NULL,
437  `mail_zip` VARCHAR(11) NULL DEFAULT NULL,
438  `previous_address` VARCHAR(50) NULL DEFAULT NULL,
439  `previous_city` VARCHAR(100) NULL DEFAULT NULL,
440  `previous_state` CHAR(2) NULL DEFAULT NULL,
441  `previous_zip` VARCHAR(11) NULL DEFAULT NULL,
442  `phone1` VARCHAR(15) NULL DEFAULT NULL,
443  `phone_type` VARCHAR(50) NULL DEFAULT NULL,
444  `phone2` VARCHAR(15) NULL DEFAULT NULL,
445  `phone3` VARCHAR(15) NULL DEFAULT NULL,
446  `email` VARCHAR(75) NULL DEFAULT NULL,
447  `us` CHAR(2) NULL DEFAULT NULL,
448  `gender` VARCHAR(10) NULL DEFAULT NULL,
449  `ethnicity` VARCHAR(60) NULL DEFAULT NULL,
450  `disabled` CHAR(1) NULL DEFAULT NULL,
451  `veteran` CHAR(1) NULL DEFAULT NULL,
452  `relocate` CHAR(1) NULL DEFAULT NULL,
453  `relocate_state` CHAR(2) NULL DEFAULT NULL,
454  `relocate_comments` TEXT NULL DEFAULT NULL,
455  `expertise` VARCHAR(100) NULL DEFAULT NULL,
456  `compensation` VARCHAR(50) NULL DEFAULT NULL,
457  `shift` VARCHAR(50) NULL DEFAULT NULL,
458  `job_type` VARCHAR(50) NULL DEFAULT NULL,
459  `travel` VARCHAR(50) NULL DEFAULT NULL,
460  `days_off` VARCHAR(50) NULL DEFAULT NULL,
461  `country` VARCHAR(50) NULL DEFAULT NULL,
462  `description` TEXT NULL DEFAULT NULL,
463  `signature` VARCHAR(50) NULL DEFAULT NULL,
464  `agree` CHAR(1) NULL DEFAULT NULL,
465  `eligible` CHAR(1) NULL DEFAULT NULL,
466  `create_date` DATETIME NULL DEFAULT NULL,
467  `update_date` DATETIME NULL DEFAULT NULL,
468  `referral_source` VARCHAR(50) NULL DEFAULT NULL,
469  `source_detail` VARCHAR(50) NULL DEFAULT NULL,
470  `access_type` VARCHAR(50) NULL DEFAULT NULL,
471  `resume` TEXT NULL DEFAULT NULL,
472  `cover_letter` TEXT NULL DEFAULT NULL,
473  `short_description` VARCHAR(75) NULL DEFAULT NULL,
474  `drug_check` CHAR(1) NULL DEFAULT NULL,
475  `reference_check` CHAR(1) NULL DEFAULT NULL,
476  `background_check` CHAR(1) NULL DEFAULT NULL,
477  `background_explain` TEXT NULL DEFAULT NULL,
478  `warrant_check` CHAR(1) NULL DEFAULT NULL,
479  `credit_agree` CHAR(1) NULL DEFAULT NULL,
480  `credit_signature` VARCHAR(50) NULL DEFAULT NULL,
481  `send_report` CHAR(1) NULL DEFAULT NULL,
482  `question_pass` CHAR(1) NULL DEFAULT 'p',
483  `emp_number` VARCHAR(20) NULL DEFAULT NULL,
484  `current_department` VARCHAR(75) NULL DEFAULT NULL,
485  `current_position` VARCHAR(100) NULL DEFAULT NULL,
486  `current_hours` VARCHAR(50) NULL DEFAULT NULL,
487  `current_days_off` VARCHAR(50) NULL DEFAULT NULL,
488  `supervisor_name` VARCHAR(25) NULL DEFAULT NULL,
489  `employment_status` VARCHAR(25) NULL DEFAULT NULL,
490  `hire_date` DATE NULL DEFAULT NULL,
491  `date_available` DATE NULL DEFAULT NULL,
492  `convicted` CHAR(1) NULL DEFAULT NULL,
493  `sentence` CHAR(1) NULL DEFAULT NULL,
494  `probation` CHAR(1) NULL DEFAULT NULL,
495  `suspended` CHAR(1) NULL DEFAULT NULL,
496  `previous_emp` CHAR(1) NULL DEFAULT NULL,
497  `dl_state` CHAR(2) NULL DEFAULT NULL,
498  `dl_number` VARCHAR(20) NULL DEFAULT NULL,
499  `employment_agree` CHAR(1) NULL DEFAULT NULL,
500  `us_applicant` CHAR(1) NULL DEFAULT NULL,
501  `international_phone` VARCHAR(20) NULL DEFAULT NULL,
502  `sigagree` CHAR(1) NULL DEFAULT NULL,
503  `formsig` VARCHAR(50) NULL DEFAULT NULL,
504  `hiring_manager_id` INT(20) NULL DEFAULT NULL,
505  `our_tribe` CHAR(1) NULL DEFAULT NULL,
506  `tribal_member` CHAR(1) NULL DEFAULT NULL,
507  `tribe_name` VARCHAR(50) NULL DEFAULT NULL,
508  `tribe_number` VARCHAR(20) NULL DEFAULT NULL,
509  `tribe_blood` VARCHAR(20) NULL DEFAULT NULL,
510  `hmr_start_date` DATE NULL DEFAULT NULL,
511  `hmr_pay_rate` VARCHAR(20) NULL DEFAULT NULL,
512  `hmr_pay_type` VARCHAR(20) NULL DEFAULT NULL,
513  `hmr_shift` VARCHAR(40) NULL DEFAULT NULL,
514  `hmr_status` VARCHAR(40) NULL DEFAULT NULL,
515  `military` CHAR(1) NULL DEFAULT NULL,
516  `fed_tax_eligible` CHAR(1) NULL DEFAULT NULL,
517  `state_tax_eligible` CHAR(1) NULL DEFAULT NULL,
518  `tax_sent` CHAR(1) NULL DEFAULT NULL,
519  `fed_tax_sent` CHAR(1) NULL DEFAULT NULL,
520  `send_report2` CHAR(1) NULL DEFAULT NULL,
521  `us_address` CHAR(1) NULL DEFAULT NULL,
522  `work_visa` CHAR(1) NULL DEFAULT NULL,
523  `visa_type` VARCHAR(15) NULL DEFAULT NULL,
524  `visa_number` VARCHAR(30) NULL DEFAULT NULL,
525  `visa_expire` DATE NULL DEFAULT NULL,
526  `intl_state` VARCHAR(50) NULL DEFAULT NULL,
527  `previous_edate` DATE NULL DEFAULT NULL,
528  `previous_sdate` DATE NULL DEFAULT NULL,
529  `previous_applied` CHAR(1) NULL DEFAULT NULL,
530  `relatives` CHAR(1) NULL DEFAULT NULL,
531  `applied_date` DATE NULL DEFAULT NULL,
532  `latitude` DOUBLE(10,7) NULL DEFAULT NULL,
533  `longitude` DOUBLE(10,7) NULL DEFAULT NULL,
534  `import_type` VARCHAR(10) NULL DEFAULT NULL,
535  `marital_status` CHAR(1) NULL DEFAULT NULL,
536  `county` VARCHAR(50) NULL DEFAULT NULL,
537  `sent_to_client` VARCHAR(1) NULL DEFAULT NULL,
538  `sent_to_client_date` DATE NULL DEFAULT NULL,
539  `interest_card` CHAR(1) NULL DEFAULT NULL,
540  `is_agency` CHAR(1) NULL DEFAULT NULL,
541  `drug_check_date` DATETIME NULL DEFAULT NULL,
542  `reference_check_date` DATETIME NULL DEFAULT NULL,
543  `background_check_date` DATETIME NULL DEFAULT NULL,
544  `eligible_date` DATETIME NULL DEFAULT NULL,
545  `start_apply_id` INT(11) NULL DEFAULT NULL,
546  `stale` CHAR(1) NULL DEFAULT NULL,
547  `nh_info_complete` CHAR(1) NULL DEFAULT NULL,
548  `auto_pay_group` VARCHAR(50) NULL DEFAULT NULL,
549  `hours_assigned` VARCHAR(50) NULL DEFAULT NULL,
550  `nh_approved_date` DATE NULL DEFAULT NULL,
551  `pay_cycle_code` VARCHAR(50) NULL DEFAULT NULL,
552  `pay_frequency` VARCHAR(50) NULL DEFAULT NULL,
553  `client_referral_source` VARCHAR(50) NULL DEFAULT NULL,
554  `client_ethnicity` VARCHAR(50) NULL DEFAULT NULL,
555  `rhr_start_date` DATE NULL DEFAULT NULL,
556  `stale_date` DATE NULL DEFAULT NULL,
557  `crm_last_sent_date` DATETIME NULL DEFAULT NULL,
558  `crm_candidate_type` VARCHAR(50) NULL DEFAULT NULL,
559  `crm_referee` CHAR(1) NULL DEFAULT NULL,
560  `crm_student` CHAR(1) NULL DEFAULT NULL,
561  `crm_not_eligible_rehire` CHAR(1) NULL DEFAULT NULL,
562  `crm_no_contact` CHAR(1) NULL DEFAULT NULL,
563  `crm_veteran` CHAR(1) NULL DEFAULT NULL,
564  `crm_inconsideration` CHAR(1) NULL DEFAULT NULL,
565  `crm_update_date` DATE NULL DEFAULT NULL,
566  `area_interest` VARCHAR(512) NULL DEFAULT NULL,
567  `donotcontact_check` CHAR(1) NULL DEFAULT NULL,
568  `unsubscribe_check` CHAR(1) NULL DEFAULT NULL,
569  `alumni_check` CHAR(1) NULL DEFAULT NULL,
570  `student_check` CHAR(1) NULL DEFAULT NULL,
571  `referee_check` CHAR(1) NULL DEFAULT NULL,
572  `number_1` INT(11) NULL DEFAULT NULL,
573  `number_2` INT(11) NULL DEFAULT NULL,
574  `number_3` INT(11) NULL DEFAULT NULL,
575  `number_4` INT(11) NULL DEFAULT NULL,
576  `number_5` INT(11) NULL DEFAULT NULL,
577  `char1_1` CHAR(1) NULL DEFAULT NULL,
578  `char1_2` CHAR(1) NULL DEFAULT NULL,
579  `char1_3` CHAR(1) NULL DEFAULT NULL,
580  `char1_4` CHAR(1) NULL DEFAULT NULL,
581  `char1_5` CHAR(1) NULL DEFAULT NULL,
582  `char1_6` CHAR(1) NULL DEFAULT NULL,
583  `char1_7` CHAR(1) NULL DEFAULT NULL,
584  `char1_8` CHAR(1) NULL DEFAULT NULL,
585  `char1_9` CHAR(1) NULL DEFAULT NULL,
586  `char1_10` CHAR(1) NULL DEFAULT NULL,
587  `string64_1` VARCHAR(64) NULL DEFAULT NULL,
588  `string64_2` VARCHAR(64) NULL DEFAULT NULL,
589  `string64_3` VARCHAR(64) NULL DEFAULT NULL,
590  `string64_4` VARCHAR(64) NULL DEFAULT NULL,
591  `string64_5` VARCHAR(64) NULL DEFAULT NULL,
592  `string64_6` VARCHAR(64) NULL DEFAULT NULL,
593  `string64_7` VARCHAR(64) NULL DEFAULT NULL,
594  `string64_8` VARCHAR(64) NULL DEFAULT NULL,
595  `string64_9` VARCHAR(64) NULL DEFAULT NULL,
596  `string64_10` VARCHAR(64) NULL DEFAULT NULL,
597  `string512_1` VARCHAR(512) NULL DEFAULT NULL,
598  `string512_2` VARCHAR(512) NULL DEFAULT NULL,
599  `string512_3` VARCHAR(512) NULL DEFAULT NULL,
600  `string512_4` VARCHAR(512) NULL DEFAULT NULL,
601  `string512_5` VARCHAR(512) NULL DEFAULT NULL,
602  `string512_6` VARCHAR(512) NULL DEFAULT NULL,
603  `string512_7` VARCHAR(512) NULL DEFAULT NULL,
604  `string512_8` VARCHAR(512) NULL DEFAULT NULL,
605  `string512_9` VARCHAR(512) NULL DEFAULT NULL,
606  `string512_10` VARCHAR(512) NULL DEFAULT NULL,
607  `date_1` DATE NULL DEFAULT NULL,
608  `date_2` DATE NULL DEFAULT NULL,
609  `date_3` DATE NULL DEFAULT NULL,
610  `date_4` DATE NULL DEFAULT NULL,
611  `date_5` DATE NULL DEFAULT NULL,
612  `datetime_1` DATETIME NULL DEFAULT NULL,
613  `datetime_2` DATETIME NULL DEFAULT NULL,
614  `datetime_3` DATETIME NULL DEFAULT NULL,
615  `datetime_4` DATETIME NULL DEFAULT NULL,
616  `datetime_5` DATETIME NULL DEFAULT NULL,
617  `failed_count` INT(4) NULL DEFAULT NULL,
618  `locked` CHAR(1) NULL DEFAULT NULL,
619  PRIMARY KEY (`candidate_id`),
620  UNIQUE INDEX `login` (`uname` ASC, `password` ASC),
621  UNIQUE INDEX `uname` (`uname` ASC),
622  INDEX `email` (`email` ASC),
623  INDEX `first_name` (`first_name` ASC),
624  INDEX `last_name` (`last_name` ASC),
625  INDEX `latitude` (`latitude` ASC),
626  INDEX `longitude` (`longitude` ASC),
627  INDEX `cand_start_apply_id` (`start_apply_id` ASC),
628  INDEX `update_date` (`update_date` ASC),
629  CONSTRAINT `cand_start_apply_id`
630    FOREIGN KEY (`start_apply_id`)
631    REFERENCES `requisition` (`requisition_id`)
632    ON DELETE SET NULL
633    ON UPDATE CASCADE)
634ENGINE = InnoDB
635AUTO_INCREMENT = 149800
636DEFAULT CHARACTER SET = latin1;
637
638
639-- -----------------------------------------------------
640-- Table `application`
641-- -----------------------------------------------------
642CREATE TABLE IF NOT EXISTS `application` (
643  `application_id` INT(20) NOT NULL AUTO_INCREMENT,
644  `candidate_id` INT(20) NOT NULL DEFAULT '0',
645  `requisition_id` INT(20) NULL DEFAULT NULL,
646  `approval_chain_id` INT(11) NULL DEFAULT NULL,
647  `offer_template_id` INT(11) NULL DEFAULT NULL,
648  `status` CHAR(40) NULL DEFAULT NULL,
649  `apply_date` DATETIME NULL DEFAULT NULL,
650  `prospect_date` DATE NULL DEFAULT NULL,
651  `hire_date` DATE NULL DEFAULT NULL,
652  `reject_date` DATE NULL DEFAULT NULL,
653  `start_date` DATE NULL DEFAULT NULL,
654  `orientation_date` DATE NULL DEFAULT NULL,
655  `req_screen_value` INT(5) NOT NULL DEFAULT '0',
656  `general_screen_value` INT(5) NOT NULL DEFAULT '0',
657  `interview_screen_value` INT(5) NOT NULL DEFAULT '0',
658  `total_screen_value` INT(5) NOT NULL DEFAULT '0',
659  `update_date` TIMESTAMP NULL DEFAULT NULL,
660  `switch_profile` CHAR(1) NULL DEFAULT 'f',
661  PRIMARY KEY (`application_id`),
662  INDEX `app_can_asso_ndx` (`candidate_id` ASC),
663  INDEX `requisition_id` (`requisition_id` ASC),
664  INDEX `status` (`status` ASC),
665  INDEX `update_date` (`update_date` ASC),
666  CONSTRAINT `application_ibfk_1`
667    FOREIGN KEY (`requisition_id`)
668    REFERENCES `requisition` (`requisition_id`)
669    ON DELETE CASCADE
670    ON UPDATE CASCADE,
671  CONSTRAINT `candidate_fk_1`
672    FOREIGN KEY (`candidate_id`)
673    REFERENCES `candidate` (`candidate_id`)
674    ON DELETE CASCADE)
675ENGINE = InnoDB
676AUTO_INCREMENT = 629540
677DEFAULT CHARACTER SET = latin1;
678
679
680-- -----------------------------------------------------
681-- Table `agency_application`
682-- -----------------------------------------------------
683CREATE TABLE IF NOT EXISTS `agency_application` (
684  `agency_id` INT(11) NOT NULL,
685  `application_id` INT(20) NOT NULL,
686  `agency_application_id` INT(11) NOT NULL AUTO_INCREMENT,
687  PRIMARY KEY (`agency_id`, `application_id`),
688  UNIQUE INDEX `Key_Constraint_1` (`agency_application_id` ASC),
689  INDEX `agency_application_ibfk_2` (`application_id` ASC),
690  CONSTRAINT `agency_application_ibfk1`
691    FOREIGN KEY (`agency_id`)
692    REFERENCES `agency` (`agency_id`)
693    ON DELETE CASCADE,
694  CONSTRAINT `agency_application_ibfk_2`
695    FOREIGN KEY (`application_id`)
696    REFERENCES `application` (`application_id`))
697ENGINE = InnoDB
698DEFAULT CHARACTER SET = latin1;
699
700
701-- -----------------------------------------------------
702-- Table `requisition_orig`
703-- -----------------------------------------------------
704CREATE TABLE IF NOT EXISTS `requisition_orig` (
705  `requisition_id` INT(20) NOT NULL AUTO_INCREMENT,
706  `hierarchy_id` INT(20) NULL DEFAULT NULL,
707  `users_id` INT(20) NULL DEFAULT NULL,
708  `title` VARCHAR(40) NOT NULL DEFAULT '',
709  `status` VARCHAR(40) NULL DEFAULT NULL,
710  `post_type` VARCHAR(10) NULL DEFAULT NULL,
711  `post_code` VARCHAR(10) NULL DEFAULT NULL,
712  `post_date` DATETIME NULL DEFAULT NULL,
713  `internal_dec` TEXT NULL DEFAULT NULL,
714  `external_desc` TEXT NULL DEFAULT NULL,
715  `short_desc` TEXT NULL DEFAULT NULL,
716  `requirements` TEXT NULL DEFAULT NULL,
717  `shift` VARCHAR(50) NULL DEFAULT NULL,
718  `job_city` VARCHAR(50) NULL DEFAULT NULL,
719  `job_state` CHAR(2) NULL DEFAULT NULL,
720  `job_zip` VARCHAR(10) NULL DEFAULT NULL,
721  `company` VARCHAR(50) NULL DEFAULT NULL,
722  `division` VARCHAR(50) NULL DEFAULT NULL,
723  `department` VARCHAR(50) NULL DEFAULT NULL,
724  `grade` VARCHAR(50) NULL DEFAULT NULL,
725  `job_code` VARCHAR(50) NULL DEFAULT NULL,
726  `cost_center` VARCHAR(50) NULL DEFAULT NULL,
727  `eeo_code` VARCHAR(10) NULL DEFAULT NULL,
728  `location` VARCHAR(50) NULL DEFAULT NULL,
729  `phone` VARCHAR(50) NULL DEFAULT NULL,
730  `work_sched` VARCHAR(50) NULL DEFAULT NULL,
731  `hours` VARCHAR(50) NULL DEFAULT NULL,
732  `travel_required` VARCHAR(50) NULL DEFAULT NULL,
733  `position_code` VARCHAR(50) NULL DEFAULT NULL,
734  `education_req` VARCHAR(50) NULL DEFAULT NULL,
735  `job_level` VARCHAR(50) NULL DEFAULT NULL,
736  `created` DATETIME NULL DEFAULT NULL,
737  `payrate` VARCHAR(50) NULL DEFAULT NULL,
738  `compensation` VARCHAR(50) NULL DEFAULT NULL,
739  `date_needed` DATE NULL DEFAULT NULL,
740  `start_date` DATE NULL DEFAULT NULL,
741  `req_reason` VARCHAR(150) NULL DEFAULT NULL,
742  `who_replacing` VARCHAR(80) NULL DEFAULT NULL,
743  `budgeted` CHAR(1) NULL DEFAULT NULL,
744  `reports_to` VARCHAR(80) NULL DEFAULT NULL,
745  `modified` DATETIME NULL DEFAULT NULL,
746  `views` INT(10) NULL DEFAULT '0',
747  `screen_job_value` INT(4) NULL DEFAULT NULL,
748  `screen_general_value` INT(4) NULL DEFAULT NULL,
749  `screen_interview_value` INT(4) NULL DEFAULT NULL,
750  `screen_total_value` INT(4) NULL DEFAULT NULL,
751  `openings` INT(5) NULL DEFAULT NULL,
752  `auto_close` CHAR(1) NULL DEFAULT NULL,
753  `advanced_scheduler` CHAR(1) NULL DEFAULT NULL,
754  `hiring_manager` INT(20) NULL DEFAULT NULL,
755  `send_to_hm` CHAR(1) NULL DEFAULT NULL,
756  `global_tpl` CHAR(1) NULL DEFAULT NULL,
757  `screen_in_msg` TEXT NULL DEFAULT NULL,
758  `screen_out_msg` TEXT NULL DEFAULT NULL,
759  `template` CHAR(1) NULL DEFAULT NULL,
760  `category` VARCHAR(50) NULL DEFAULT NULL,
761  `int_form_group_id` INT(10) NULL DEFAULT NULL,
762  `ext_form_group_id` INT(10) NULL DEFAULT NULL,
763  `view_group_id` INT(10) NULL DEFAULT '1',
764  `cross_posting_status` CHAR(1) NULL DEFAULT NULL,
765  `cross_posting_sites` BLOB NULL DEFAULT NULL,
766  `skills_testing` VARCHAR(150) NULL DEFAULT NULL,
767  `business_partner` VARCHAR(50) NULL DEFAULT NULL,
768  `add_explain` VARCHAR(255) NULL DEFAULT NULL,
769  `add_import` VARCHAR(255) NULL DEFAULT NULL,
770  `add_income` CHAR(1) NULL DEFAULT NULL,
771  `funds_available` CHAR(1) NULL DEFAULT NULL,
772  `agency_fees` VARCHAR(20) NULL DEFAULT NULL,
773  `job_type` VARCHAR(50) NULL DEFAULT NULL,
774  `property` VARCHAR(50) NULL DEFAULT NULL,
775  `final_approver` INT(20) NULL DEFAULT NULL,
776  `tax_location_code` CHAR(25) NULL DEFAULT NULL,
777  `ob_template_id` INT(10) NULL DEFAULT NULL,
778  `assmt_position_code` VARCHAR(50) NULL DEFAULT NULL,
779  `flsa` VARCHAR(15) NULL DEFAULT NULL,
780  `offer_template_id` INT(11) NULL DEFAULT NULL,
781  `agency_keys` TEXT NULL DEFAULT NULL,
782  `agency_note` TEXT NULL DEFAULT NULL,
783  `latitude` DOUBLE(10,7) NULL DEFAULT NULL,
784  `longitude` DOUBLE(10,7) NULL DEFAULT NULL,
785  `city` VARCHAR(20) NULL DEFAULT NULL,
786  `state` VARCHAR(20) NULL DEFAULT NULL,
787  `country` VARCHAR(20) NULL DEFAULT NULL,
788  `zip` VARCHAR(9) NULL DEFAULT NULL,
789  `sl_form_group_id` INT(20) NULL DEFAULT NULL,
790  `hot_job` CHAR(1) NULL DEFAULT NULL,
791  `update_date` TIMESTAMP NULL DEFAULT NULL,
792  `address` VARCHAR(100) NULL DEFAULT NULL,
793  `sec_view_group_id` INT(10) NULL DEFAULT NULL,
794  PRIMARY KEY (`requisition_id`),
795  INDEX `ext_form_group_id` (`ext_form_group_id` ASC),
796  INDEX `int_form_group_id` (`int_form_group_id` ASC),
797  INDEX `users_id` (`users_id` ASC),
798  INDEX `hierarchy_id` (`hierarchy_id` ASC),
799  INDEX `status` (`status` ASC),
800  INDEX `template` (`template` ASC),
801  INDEX `global_tpl` (`global_tpl` ASC),
802  INDEX `post_code` (`post_code` ASC),
803  INDEX `view_group_id` (`view_group_id` ASC),
804  INDEX `big_test` (`requisition_id` ASC, `users_id` ASC, `title` ASC, `status` ASC, `template` ASC),
805  INDEX `latitude` (`latitude` ASC),
806  INDEX `longitude` (`longitude` ASC),
807  INDEX `update_date` (`update_date` ASC),
808  CONSTRAINT `requisition_orig_ibfk_1`
809    FOREIGN KEY (`int_form_group_id`)
810    REFERENCES `view_group` (`view_group_id`)
811    ON DELETE SET NULL
812    ON UPDATE CASCADE,
813  CONSTRAINT `requisition_orig_ibfk_2`
814    FOREIGN KEY (`ext_form_group_id`)
815    REFERENCES `view_group` (`view_group_id`)
816    ON DELETE SET NULL
817    ON UPDATE CASCADE,
818  CONSTRAINT `requisition_orig_ibfk_3`
819    FOREIGN KEY (`users_id`)
820    REFERENCES `users` (`users_id`),
821  CONSTRAINT `requisition_orig_ibfk_4`
822    FOREIGN KEY (`hierarchy_id`)
823    REFERENCES `hierarchy` (`hierarchy_id`),
824  CONSTRAINT `requisition_orig_ibfk_5`
825    FOREIGN KEY (`view_group_id`)
826    REFERENCES `view_group` (`view_group_id`)
827    ON DELETE SET NULL
828    ON UPDATE CASCADE)
829ENGINE = InnoDB
830AUTO_INCREMENT = 2185
831DEFAULT CHARACTER SET = latin1;
832
833
834-- -----------------------------------------------------
835-- Table `candidate_orig`
836-- -----------------------------------------------------
837CREATE TABLE IF NOT EXISTS `candidate_orig` (
838  `candidate_id` INT(20) NOT NULL AUTO_INCREMENT,
839  `users_id` INT(20) NULL DEFAULT NULL,
840  `uname` VARCHAR(40) NOT NULL DEFAULT '',
841  `password` VARCHAR(20) NOT NULL DEFAULT '',
842  `first_name` VARCHAR(20) NULL DEFAULT NULL,
843  `middle_name` CHAR(1) NULL DEFAULT NULL,
844  `last_name` VARCHAR(20) NULL DEFAULT NULL,
845  `suffix` VARCHAR(15) NULL DEFAULT NULL,
846  `dob` DATE NULL DEFAULT NULL,
847  `ssn` VARCHAR(10) NULL DEFAULT NULL,
848  `ssn_name` VARCHAR(100) NULL DEFAULT NULL,
849  `other_name` VARCHAR(50) NULL DEFAULT NULL,
850  `maiden_name` VARCHAR(50) NULL DEFAULT NULL,
851  `address1` VARCHAR(30) NULL DEFAULT NULL,
852  `address2` VARCHAR(30) NULL DEFAULT NULL,
853  `city` VARCHAR(30) NULL DEFAULT NULL,
854  `state` CHAR(2) NULL DEFAULT NULL,
855  `zip` VARCHAR(9) NULL DEFAULT NULL,
856  `mail_address` VARCHAR(50) NULL DEFAULT NULL,
857  `mail_city` VARCHAR(100) NULL DEFAULT NULL,
858  `mail_state` CHAR(2) NULL DEFAULT NULL,
859  `mail_zip` VARCHAR(11) NULL DEFAULT NULL,
860  `previous_address` VARCHAR(50) NULL DEFAULT NULL,
861  `previous_city` VARCHAR(100) NULL DEFAULT NULL,
862  `previous_state` CHAR(2) NULL DEFAULT NULL,
863  `previous_zip` VARCHAR(11) NULL DEFAULT NULL,
864  `phone1` VARCHAR(15) NULL DEFAULT NULL,
865  `phone_type` VARCHAR(50) NULL DEFAULT NULL,
866  `phone2` VARCHAR(15) NULL DEFAULT NULL,
867  `phone3` VARCHAR(15) NULL DEFAULT NULL,
868  `sms_optin` CHAR(1) NULL DEFAULT NULL,
869  `email` VARCHAR(75) NULL DEFAULT NULL,
870  `email_validated` CHAR(1) NULL DEFAULT 'f',
871  `us` CHAR(2) NULL DEFAULT NULL,
872  `gender` VARCHAR(10) NULL DEFAULT NULL,
873  `ethnicity` VARCHAR(60) NULL DEFAULT NULL,
874  `disabled` CHAR(1) NULL DEFAULT NULL,
875  `veteran` CHAR(1) NULL DEFAULT NULL,
876  `relocate` CHAR(1) NULL DEFAULT NULL,
877  `relocate_state` CHAR(2) NULL DEFAULT NULL,
878  `relocate_comments` TEXT NULL DEFAULT NULL,
879  `expertise` VARCHAR(100) NULL DEFAULT NULL,
880  `compensation` VARCHAR(50) NULL DEFAULT NULL,
881  `shift` VARCHAR(50) NULL DEFAULT NULL,
882  `job_type` VARCHAR(50) NULL DEFAULT NULL,
883  `travel` VARCHAR(50) NULL DEFAULT NULL,
884  `days_off` VARCHAR(50) NULL DEFAULT NULL,
885  `country` VARCHAR(50) NULL DEFAULT NULL,
886  `description` TEXT NULL DEFAULT NULL,
887  `signature` VARCHAR(50) NULL DEFAULT NULL,
888  `agree` CHAR(1) NULL DEFAULT NULL,
889  `eligible` CHAR(1) NULL DEFAULT NULL,
890  `create_date` DATETIME NULL DEFAULT NULL,
891  `update_date` DATETIME NULL DEFAULT NULL,
892  `referral_source` VARCHAR(50) NULL DEFAULT NULL,
893  `source_detail` VARCHAR(50) NULL DEFAULT NULL,
894  `access_type` VARCHAR(50) NULL DEFAULT NULL,
895  `resume` TEXT NULL DEFAULT NULL,
896  `cover_letter` TEXT NULL DEFAULT NULL,
897  `short_description` VARCHAR(75) NULL DEFAULT NULL,
898  `drug_check` CHAR(1) NULL DEFAULT NULL,
899  `reference_check` CHAR(1) NULL DEFAULT NULL,
900  `background_check` CHAR(1) NULL DEFAULT NULL,
901  `background_explain` TEXT NULL DEFAULT NULL,
902  `warrant_check` CHAR(1) NULL DEFAULT NULL,
903  `credit_agree` CHAR(1) NULL DEFAULT NULL,
904  `credit_signature` VARCHAR(50) NULL DEFAULT NULL,
905  `send_report` CHAR(1) NULL DEFAULT NULL,
906  `question_pass` CHAR(1) NULL DEFAULT 'p',
907  `emp_number` VARCHAR(20) NULL DEFAULT NULL,
908  `current_department` VARCHAR(75) NULL DEFAULT NULL,
909  `current_position` VARCHAR(100) NULL DEFAULT NULL,
910  `current_hours` VARCHAR(50) NULL DEFAULT NULL,
911  `current_days_off` VARCHAR(50) NULL DEFAULT NULL,
912  `supervisor_name` VARCHAR(25) NULL DEFAULT NULL,
913  `employment_status` VARCHAR(25) NULL DEFAULT NULL,
914  `hire_date` DATE NULL DEFAULT NULL,
915  `date_available` DATE NULL DEFAULT NULL,
916  `convicted` CHAR(1) NULL DEFAULT NULL,
917  `sentence` CHAR(1) NULL DEFAULT NULL,
918  `probation` CHAR(1) NULL DEFAULT NULL,
919  `suspended` CHAR(1) NULL DEFAULT NULL,
920  `previous_emp` CHAR(1) NULL DEFAULT NULL,
921  `dl_state` CHAR(2) NULL DEFAULT NULL,
922  `dl_number` VARCHAR(20) NULL DEFAULT NULL,
923  `employment_agree` CHAR(1) NULL DEFAULT NULL,
924  `us_applicant` CHAR(1) NULL DEFAULT NULL,
925  `international_phone` VARCHAR(20) NULL DEFAULT NULL,
926  `sigagree` CHAR(1) NULL DEFAULT NULL,
927  `formsig` VARCHAR(50) NULL DEFAULT NULL,
928  `hiring_manager_id` INT(20) NULL DEFAULT NULL,
929  `our_tribe` CHAR(1) NULL DEFAULT NULL,
930  `tribal_member` CHAR(1) NULL DEFAULT NULL,
931  `tribe_name` VARCHAR(50) NULL DEFAULT NULL,
932  `tribe_number` VARCHAR(20) NULL DEFAULT NULL,
933  `tribe_blood` VARCHAR(20) NULL DEFAULT NULL,
934  `hmr_start_date` DATE NULL DEFAULT NULL,
935  `hmr_pay_rate` VARCHAR(20) NULL DEFAULT NULL,
936  `hmr_pay_type` VARCHAR(20) NULL DEFAULT NULL,
937  `hmr_shift` VARCHAR(40) NULL DEFAULT NULL,
938  `hmr_status` VARCHAR(40) NULL DEFAULT NULL,
939  `military` CHAR(1) NULL DEFAULT NULL,
940  `itax_certification` CHAR(1) NULL DEFAULT NULL,
941  `itax_age_group` INT(1) NULL DEFAULT NULL,
942  `itax_aid` CHAR(1) NULL DEFAULT NULL,
943  `itax_aid_recent` CHAR(1) NULL DEFAULT NULL,
944  `itax_aid_long` CHAR(1) NULL DEFAULT NULL,
945  `itax_aid_extended` CHAR(1) NULL DEFAULT NULL,
946  `itax_aid_stopped` CHAR(1) NULL DEFAULT NULL,
947  `itax_pr_first_name` VARCHAR(50) NULL DEFAULT NULL,
948  `itax_pr_last_name` VARCHAR(50) NULL DEFAULT NULL,
949  `itax_pr_city` VARCHAR(50) NULL DEFAULT NULL,
950  `itax_pr_state` CHAR(2) NULL DEFAULT NULL,
951  `itax_pr_relationship` VARCHAR(50) NULL DEFAULT NULL,
952  `itax_military_long` CHAR(1) NULL DEFAULT NULL,
953  `itax_military_extended` CHAR(1) NULL DEFAULT NULL,
954  `itax_mfood_stamps` CHAR(1) NULL DEFAULT NULL,
955  `itax_mpr_first_name` VARCHAR(50) NULL DEFAULT NULL,
956  `itax_mpr_last_name` VARCHAR(50) NULL DEFAULT NULL,
957  `itax_mpr_city` VARCHAR(50) NULL DEFAULT NULL,
958  `itax_mpr_state` CHAR(2) NULL DEFAULT NULL,
959  `itax_mpr_relationship` VARCHAR(50) NULL DEFAULT NULL,
960  `itax_released` CHAR(1) NULL DEFAULT NULL,
961  `itax_family_members` INT(20) NULL DEFAULT NULL,
962  `itax_family_income` INT(20) NULL DEFAULT NULL,
963  `itax_recent_convicted` DATE NULL DEFAULT NULL,
964  `itax_recent_released` DATE NULL DEFAULT NULL,
965  `itax_convicted_state` CHAR(2) NULL DEFAULT NULL,
966  `itax_convicted_location` VARCHAR(50) NULL DEFAULT NULL,
967  `itax_convicted_officer` VARCHAR(50) NULL DEFAULT NULL,
968  `itax_convicted_phone` CHAR(15) NULL DEFAULT NULL,
969  `itax_food_stamps` CHAR(1) NULL DEFAULT NULL,
970  `itax_no_food_stamps` CHAR(1) NULL DEFAULT NULL,
971  `itax_fpr_first_name` VARCHAR(50) NULL DEFAULT NULL,
972  `itax_fpr_last_name` VARCHAR(50) NULL DEFAULT NULL,
973  `itax_fpr_city` VARCHAR(50) NULL DEFAULT NULL,
974  `itax_fpr_state` CHAR(2) NULL DEFAULT NULL,
975  `itax_fpr_relationship` VARCHAR(50) NULL DEFAULT NULL,
976  `itax_wotc` CHAR(1) NULL DEFAULT NULL,
977  `itax_relative` CHAR(1) NULL DEFAULT NULL,
978  `itax_own` CHAR(1) NULL DEFAULT NULL,
979  `itax_dependent` CHAR(1) NULL DEFAULT NULL,
980  `itax_referred` CHAR(1) NULL DEFAULT NULL,
981  `itax_agree` CHAR(1) NULL DEFAULT NULL,
982  `itax_sig` VARCHAR(50) NULL DEFAULT NULL,
983  `itax_date` DATE NULL DEFAULT NULL,
984  `fed_tax_eligible` CHAR(1) NULL DEFAULT NULL,
985  `state_tax_eligible` CHAR(1) NULL DEFAULT NULL,
986  `tax_sent` CHAR(1) NULL DEFAULT NULL,
987  `fed_tax_sent` CHAR(1) NULL DEFAULT NULL,
988  `send_report2` CHAR(1) NULL DEFAULT NULL,
989  `us_address` CHAR(1) NULL DEFAULT NULL,
990  `work_visa` CHAR(1) NULL DEFAULT NULL,
991  `visa_type` VARCHAR(15) NULL DEFAULT NULL,
992  `visa_number` VARCHAR(30) NULL DEFAULT NULL,
993  `visa_expire` DATE NULL DEFAULT NULL,
994  `intl_state` VARCHAR(50) NULL DEFAULT NULL,
995  `previous_edate` DATE NULL DEFAULT NULL,
996  `previous_sdate` DATE NULL DEFAULT NULL,
997  `previous_applied` CHAR(1) NULL DEFAULT NULL,
998  `relatives` CHAR(1) NULL DEFAULT NULL,
999  `applied_date` DATE NULL DEFAULT NULL,
1000  `latitude` DOUBLE(10,7) NULL DEFAULT NULL,
1001  `longitude` DOUBLE(10,7) NULL DEFAULT NULL,
1002  `import_type` VARCHAR(10) NULL DEFAULT NULL,
1003  `iaw4_emp_address1` VARCHAR(20) NULL DEFAULT NULL,
1004  `iaw4_emp_address2` VARCHAR(20) NULL DEFAULT NULL,
1005  `iaw4_emp_city` VARCHAR(20) NULL DEFAULT NULL,
1006  `iaw4_emp_fein` VARCHAR(20) NULL DEFAULT NULL,
1007  `iaw4_emp_name` VARCHAR(20) NULL DEFAULT NULL,
1008  `iaw4_emp_other_address1` VARCHAR(20) NULL DEFAULT NULL,
1009  `iaw4_emp_other_address2` VARCHAR(20) NULL DEFAULT NULL,
1010  `iaw4_emp_other_city` VARCHAR(20) NULL DEFAULT NULL,
1011  `iaw4_emp_other_state` VARCHAR(20) NULL DEFAULT NULL,
1012  `iaw4_emp_other_zip` VARCHAR(20) NULL DEFAULT NULL,
1013  `iaw4_emp_phone1` VARCHAR(20) NULL DEFAULT NULL,
1014  `iaw4_emp_state` VARCHAR(20) NULL DEFAULT NULL,
1015  `iaw4_emp_zip` VARCHAR(20) NULL DEFAULT NULL,
1016  `iaw4_health_care` VARCHAR(20) NULL DEFAULT NULL,
1017  `iaw4_qualify_date` DATE NULL DEFAULT NULL,
1018  `iaw4_start_date` DATE NULL DEFAULT NULL,
1019  `marital_status` CHAR(1) NULL DEFAULT NULL,
1020  `mainew4_marital` CHAR(2) NULL DEFAULT NULL,
1021  `county` VARCHAR(50) NULL DEFAULT NULL,
1022  `sent_to_client` VARCHAR(1) NULL DEFAULT NULL,
1023  `sent_to_client_date` DATE NULL DEFAULT NULL,
1024  `interest_card` CHAR(1) NULL DEFAULT NULL,
1025  `applycompleteurl` VARCHAR(250) NULL DEFAULT NULL,
1026  `is_agency` CHAR(1) NULL DEFAULT NULL,
1027  `drug_check_date` DATETIME NULL DEFAULT NULL,
1028  `reference_check_date` DATETIME NULL DEFAULT NULL,
1029  `background_check_date` DATETIME NULL DEFAULT NULL,
1030  `eligible_date` DATETIME NULL DEFAULT NULL,
1031  `start_apply_id` INT(11) NULL DEFAULT NULL,
1032  `stale` CHAR(1) NULL DEFAULT NULL,
1033  `hms_migrated` CHAR(1) NULL DEFAULT NULL,
1034  `hms_migrated_status` CHAR(1) NULL DEFAULT NULL,
1035  `birth_city` VARCHAR(50) NULL DEFAULT NULL,
1036  `birth_state` CHAR(2) NULL DEFAULT NULL,
1037  `citizen` CHAR(1) NULL DEFAULT NULL,
1038  `privacy_sig` VARCHAR(50) NULL DEFAULT NULL,
1039  `consumer_sig` VARCHAR(50) NULL DEFAULT NULL,
1040  `release_sig` VARCHAR(50) NULL DEFAULT NULL,
1041  `nh_info_complete` CHAR(1) NULL DEFAULT NULL,
1042  `auto_pay_group` VARCHAR(50) NULL DEFAULT NULL,
1043  `hours_assigned` VARCHAR(50) NULL DEFAULT NULL,
1044  `nh_approved_date` DATE NULL DEFAULT NULL,
1045  `pay_cycle_code` VARCHAR(50) NULL DEFAULT NULL,
1046  `pay_frequency` VARCHAR(50) NULL DEFAULT NULL,
1047  `client_referral_source` VARCHAR(50) NULL DEFAULT NULL,
1048  `client_ethnicity` VARCHAR(50) NULL DEFAULT NULL,
1049  `ec_relationship` VARCHAR(50) NULL DEFAULT NULL,
1050  `rhr_start_date` DATE NULL DEFAULT NULL,
1051  `stale_date` DATE NULL DEFAULT NULL,
1052  `paf_date` DATE NULL DEFAULT NULL,
1053  `paf_reqnum` VARCHAR(15) NULL DEFAULT NULL,
1054  `paf_hiredate` DATE NULL DEFAULT NULL,
1055  `crm_last_sent_date` DATETIME NULL DEFAULT NULL,
1056  `crm_candidate_type` VARCHAR(50) NULL DEFAULT NULL,
1057  `crm_referee` CHAR(1) NULL DEFAULT NULL,
1058  `crm_student` CHAR(1) NULL DEFAULT NULL,
1059  `crm_not_eligible_rehire` CHAR(1) NULL DEFAULT NULL,
1060  `crm_no_contact` CHAR(1) NULL DEFAULT NULL,
1061  `crm_veteran` CHAR(1) NULL DEFAULT NULL,
1062  `crm_inconsideration` CHAR(1) NULL DEFAULT NULL,
1063  `crm_update_date` DATE NULL DEFAULT NULL,
1064  PRIMARY KEY (`candidate_id`),
1065  UNIQUE INDEX `login` (`uname` ASC, `password` ASC),
1066  UNIQUE INDEX `uname` (`uname` ASC),
1067  INDEX `email` (`email` ASC),
1068  INDEX `first_name` (`first_name` ASC),
1069  INDEX `last_name` (`last_name` ASC),
1070  INDEX `latitude` (`latitude` ASC),
1071  INDEX `longitude` (`longitude` ASC),
1072  INDEX `candidate_start_apply_id` (`start_apply_id` ASC),
1073  INDEX `update_date` (`update_date` ASC),
1074  CONSTRAINT `candidate_start_apply_id`
1075    FOREIGN KEY (`start_apply_id`)
1076    REFERENCES `requisition_orig` (`requisition_id`)
1077    ON DELETE SET NULL
1078    ON UPDATE CASCADE)
1079ENGINE = InnoDB
1080AUTO_INCREMENT = 3299
1081DEFAULT CHARACTER SET = latin1;
1082
1083
1084-- -----------------------------------------------------
1085-- Table `agency_candidate`
1086-- -----------------------------------------------------
1087CREATE TABLE IF NOT EXISTS `agency_candidate` (
1088  `candidate_id` INT(20) NOT NULL,
1089  `agency_id` INT(11) NOT NULL,
1090  `agency_candidate_id` INT(11) NOT NULL AUTO_INCREMENT,
1091  PRIMARY KEY (`candidate_id`, `agency_id`),
1092  UNIQUE INDEX `agency_candidate_id` (`agency_candidate_id` ASC),
1093  INDEX `agency_candidate_ibfk1` (`agency_id` ASC),
1094  CONSTRAINT `agency_candidate_ibfk1`
1095    FOREIGN KEY (`agency_id`)
1096    REFERENCES `agency` (`agency_id`)
1097    ON DELETE CASCADE,
1098  CONSTRAINT `agency_candidate_ibfk2`
1099    FOREIGN KEY (`candidate_id`)
1100    REFERENCES `candidate_orig` (`candidate_id`)
1101    ON DELETE CASCADE)
1102ENGINE = InnoDB
1103DEFAULT CHARACTER SET = latin1;
1104
1105
1106-- -----------------------------------------------------
1107-- Table `agency_note`
1108-- -----------------------------------------------------
1109CREATE TABLE IF NOT EXISTS `agency_note` (
1110  `agency_note_id` INT(11) NOT NULL AUTO_INCREMENT,
1111  `note` TEXT NULL DEFAULT NULL,
1112  `create_date` DATETIME NULL DEFAULT NULL,
1113  `candidate_id` INT(20) NOT NULL,
1114  `agency_id` INT(11) NOT NULL,
1115  PRIMARY KEY (`agency_note_id`),
1116  INDEX `agency_note_ibfk1` (`agency_id` ASC),
1117  INDEX `agency_note_ibfk2` (`candidate_id` ASC),
1118  CONSTRAINT `agency_note_ibfk1`
1119    FOREIGN KEY (`agency_id`)
1120    REFERENCES `agency` (`agency_id`)
1121    ON DELETE CASCADE,
1122  CONSTRAINT `agency_note_ibfk2`
1123    FOREIGN KEY (`candidate_id`)
1124    REFERENCES `candidate_orig` (`candidate_id`)
1125    ON DELETE CASCADE)
1126ENGINE = InnoDB
1127DEFAULT CHARACTER SET = latin1;
1128
1129
1130-- -----------------------------------------------------
1131-- Table `agency_requisition`
1132-- -----------------------------------------------------
1133CREATE TABLE IF NOT EXISTS `agency_requisition` (
1134  `agency_id` INT(11) NOT NULL,
1135  `requisition_id` INT(11) NOT NULL,
1136  `agency_requisition_id` INT(11) NOT NULL AUTO_INCREMENT,
1137  PRIMARY KEY (`agency_id`, `requisition_id`),
1138  UNIQUE INDEX `Key_Constraint_1` (`agency_requisition_id` ASC),
1139  INDEX `agency_requisition_ibfk1` (`requisition_id` ASC),
1140  CONSTRAINT `agency_requisition_ibfk1`
1141    FOREIGN KEY (`requisition_id`)
1142    REFERENCES `requisition_orig` (`requisition_id`)
1143    ON DELETE CASCADE,
1144  CONSTRAINT `agency_requisition_ibfk2`
1145    FOREIGN KEY (`agency_id`)
1146    REFERENCES `agency` (`agency_id`)
1147    ON DELETE CASCADE)
1148ENGINE = InnoDB
1149DEFAULT CHARACTER SET = latin1;
1150
1151
1152-- -----------------------------------------------------
1153-- Table `agency_type`
1154-- -----------------------------------------------------
1155CREATE TABLE IF NOT EXISTS `agency_type` (
1156  `agency_type_id` INT(11) NOT NULL AUTO_INCREMENT,
1157  `name` VARCHAR(30) NOT NULL,
1158  PRIMARY KEY (`agency_type_id`),
1159  UNIQUE INDEX `name` (`name` ASC))
1160ENGINE = InnoDB
1161AUTO_INCREMENT = 8
1162DEFAULT CHARACTER SET = latin1;
1163
1164
1165-- -----------------------------------------------------
1166-- Table `agency_type_agency`
1167-- -----------------------------------------------------
1168CREATE TABLE IF NOT EXISTS `agency_type_agency` (
1169  `agency_type_id` INT(11) NOT NULL,
1170  `agency_id` INT(11) NOT NULL,
1171  PRIMARY KEY (`agency_type_id`, `agency_id`),
1172  INDEX `agency_type_agency_ibfk1` (`agency_id` ASC),
1173  CONSTRAINT `agency_type_agency_ibfk1`
1174    FOREIGN KEY (`agency_id`)
1175    REFERENCES `agency` (`agency_id`)
1176    ON DELETE CASCADE
1177    ON UPDATE NO ACTION,
1178  CONSTRAINT `agency_type_agency_ibfk2`
1179    FOREIGN KEY (`agency_type_id`)
1180    REFERENCES `agency_type` (`agency_type_id`)
1181    ON DELETE CASCADE
1182    ON UPDATE NO ACTION)
1183ENGINE = InnoDB
1184DEFAULT CHARACTER SET = latin1;
1185
1186
1187-- -----------------------------------------------------
1188-- Table `analytics_similarity_pivot`
1189-- -----------------------------------------------------
1190CREATE TABLE IF NOT EXISTS `analytics_similarity_pivot` (
1191  `label` VARCHAR(50) NULL DEFAULT NULL,
1192  `requisition_field` VARCHAR(100) NULL DEFAULT NULL,
1193  `list` VARCHAR(50) NULL DEFAULT NULL,
1194  `analytics_similarity_pivot_id` INT(11) NOT NULL AUTO_INCREMENT,
1195  `active` CHAR(1) NULL DEFAULT 't',
1196  PRIMARY KEY (`analytics_similarity_pivot_id`))
1197ENGINE = InnoDB
1198AUTO_INCREMENT = 4
1199DEFAULT CHARACTER SET = latin1;
1200
1201
1202-- -----------------------------------------------------
1203-- Table `general_questions`
1204-- -----------------------------------------------------
1205CREATE TABLE IF NOT EXISTS `general_questions` (
1206  `general_questions_id` INT(20) NOT NULL AUTO_INCREMENT,
1207  `question` TEXT NOT NULL,
1208  `active` VARCHAR(50) NULL DEFAULT 't',
1209  `type` VARCHAR(50) NULL DEFAULT NULL,
1210  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1211  `level` VARCHAR(50) NULL DEFAULT NULL,
1212  `required` CHAR(1) NULL DEFAULT 't',
1213  `sort_order` INT(5) NOT NULL DEFAULT '0',
1214  `SIZE` DECIMAL(3,0) NULL DEFAULT '0',
1215  PRIMARY KEY (`general_questions_id`))
1216ENGINE = InnoDB
1217AUTO_INCREMENT = 2038
1218DEFAULT CHARACTER SET = latin1;
1219
1220
1221-- -----------------------------------------------------
1222-- Table `general_option`
1223-- -----------------------------------------------------
1224CREATE TABLE IF NOT EXISTS `general_option` (
1225  `general_option_id` INT(20) NOT NULL AUTO_INCREMENT,
1226  `general_questions_id` INT(20) NOT NULL DEFAULT '0',
1227  `answer` CHAR(255) NULL DEFAULT NULL,
1228  `weight` INT(4) NOT NULL DEFAULT '0',
1229  `sort_order` INT(5) NOT NULL DEFAULT '0',
1230  `page_break` CHAR(50) NULL DEFAULT NULL,
1231  `active` CHAR(50) NULL DEFAULT NULL,
1232  PRIMARY KEY (`general_option_id`),
1233  INDEX `general_questions_ndx` (`general_questions_id` ASC),
1234  CONSTRAINT `0_278`
1235    FOREIGN KEY (`general_questions_id`)
1236    REFERENCES `general_questions` (`general_questions_id`)
1237    ON DELETE CASCADE
1238    ON UPDATE CASCADE)
1239ENGINE = InnoDB
1240AUTO_INCREMENT = 5267
1241DEFAULT CHARACTER SET = latin1;
1242
1243
1244-- -----------------------------------------------------
1245-- Table `answers`
1246-- -----------------------------------------------------
1247CREATE TABLE IF NOT EXISTS `answers` (
1248  `answers_id` INT(20) NOT NULL AUTO_INCREMENT,
1249  `candidate_id` INT(20) NOT NULL DEFAULT '0',
1250  `general_option_id` INT(20) NOT NULL DEFAULT '0',
1251  `application_id` INT(20) NULL DEFAULT NULL,
1252  `answer` VARCHAR(255) NULL DEFAULT NULL,
1253  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1254  PRIMARY KEY (`answers_id`),
1255  INDEX `general_option_ndx` (`general_option_id` ASC),
1256  INDEX `candidate_ndx` (`candidate_id` ASC),
1257  INDEX `application_id` (`application_id` ASC),
1258  CONSTRAINT `0_284`
1259    FOREIGN KEY (`general_option_id`)
1260    REFERENCES `general_option` (`general_option_id`)
1261    ON DELETE CASCADE
1262    ON UPDATE CASCADE,
1263  CONSTRAINT `0_285`
1264    FOREIGN KEY (`candidate_id`)
1265    REFERENCES `candidate` (`candidate_id`)
1266    ON DELETE CASCADE
1267    ON UPDATE CASCADE)
1268ENGINE = InnoDB
1269AUTO_INCREMENT = 34780
1270DEFAULT CHARACTER SET = latin1;
1271
1272
1273-- -----------------------------------------------------
1274-- Table `application_aux`
1275-- -----------------------------------------------------
1276CREATE TABLE IF NOT EXISTS `application_aux` (
1277  `application_id` INT(11) NOT NULL,
1278  `electronic_referral` VARCHAR(40) NULL DEFAULT NULL,
1279  `referer_url` VARCHAR(2100) NULL DEFAULT NULL,
1280  `bill_rate` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Agency Portal',
1281  `duration` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Agency Portal',
1282  `comment` TEXT NULL DEFAULT NULL COMMENT 'Agency Portal',
1283  `cost_advertising` DOUBLE NULL DEFAULT NULL,
1284  `cost_subscriptions` DOUBLE NULL DEFAULT NULL,
1285  `cost_bonuses` DOUBLE NULL DEFAULT NULL,
1286  `cost_relocation` DOUBLE NULL DEFAULT NULL,
1287  `cost_referral` DOUBLE NULL DEFAULT NULL,
1288  `cost_travel` DOUBLE NULL DEFAULT NULL,
1289  `cost_agency` DOUBLE NULL DEFAULT NULL,
1290  `cost_labor` DOUBLE NULL DEFAULT NULL,
1291  `cost_additional` DOUBLE NULL DEFAULT NULL,
1292  `cost_comments` VARCHAR(255) NULL DEFAULT NULL,
1293  `cost_trans_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1294  `cost_bonus_pay1` DATE NULL DEFAULT NULL,
1295  `cost_bonus_pay2` DATE NULL DEFAULT NULL,
1296  `cost_bonus_pay3` DATE NULL DEFAULT NULL,
1297  `cost_referral_pay` DATE NULL DEFAULT NULL,
1298  `cost_emp_name` VARCHAR(50) NULL DEFAULT NULL,
1299  `cost_bonus_amount1` DOUBLE NULL DEFAULT NULL,
1300  `cost_bonus_amount2` DOUBLE NULL DEFAULT NULL,
1301  `cost_bonus_amount3` DOUBLE NULL DEFAULT NULL,
1302  `cost_salary` DOUBLE NULL DEFAULT NULL,
1303  `cost_years_exp` VARCHAR(50) NULL DEFAULT NULL,
1304  `is_app_forms_complete` CHAR(1) NULL DEFAULT NULL,
1305  PRIMARY KEY (`application_id`),
1306  CONSTRAINT `FK_application_aux`
1307    FOREIGN KEY (`application_id`)
1308    REFERENCES `application` (`application_id`)
1309    ON DELETE CASCADE
1310    ON UPDATE CASCADE)
1311ENGINE = InnoDB
1312DEFAULT CHARACTER SET = latin1;
1313
1314
1315-- -----------------------------------------------------
1316-- Table `apply_container`
1317-- -----------------------------------------------------
1318CREATE TABLE IF NOT EXISTS `apply_container` (
1319  `apply_container_id` INT(11) NOT NULL AUTO_INCREMENT,
1320  `name` VARCHAR(64) NOT NULL,
1321  `label` VARCHAR(128) NOT NULL,
1322  `template_name` VARCHAR(256) NOT NULL,
1323  `imageable` CHAR(1) NULL DEFAULT 'f',
1324  PRIMARY KEY (`apply_container_id`))
1325ENGINE = InnoDB
1326AUTO_INCREMENT = 2
1327DEFAULT CHARACTER SET = latin1;
1328
1329
1330-- -----------------------------------------------------
1331-- Table `apply_form`
1332-- -----------------------------------------------------
1333CREATE TABLE IF NOT EXISTS `apply_form` (
1334  `apply_form_id` INT(11) NOT NULL AUTO_INCREMENT,
1335  `name` VARCHAR(64) NOT NULL,
1336  `label` VARCHAR(128) NOT NULL,
1337  `template_name` VARCHAR(256) NOT NULL,
1338  `imageable` CHAR(1) NULL DEFAULT 'f',
1339  `version` INT(11) NULL DEFAULT '0',
1340  `effective_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
1341  `anchor` CHAR(1) NULL DEFAULT 'f',
1342  PRIMARY KEY (`apply_form_id`))
1343ENGINE = InnoDB
1344AUTO_INCREMENT = 10
1345DEFAULT CHARACTER SET = latin1;
1346
1347
1348-- -----------------------------------------------------
1349-- Table `apply_stream`
1350-- -----------------------------------------------------
1351CREATE TABLE IF NOT EXISTS `apply_stream` (
1352  `apply_stream_id` INT(11) NOT NULL AUTO_INCREMENT,
1353  `name` VARCHAR(64) NOT NULL,
1354  `label` VARCHAR(128) NOT NULL,
1355  `anchor` CHAR(1) NULL DEFAULT 'f',
1356  PRIMARY KEY (`apply_stream_id`))
1357ENGINE = InnoDB
1358AUTO_INCREMENT = 16
1359DEFAULT CHARACTER SET = latin1;
1360
1361
1362-- -----------------------------------------------------
1363-- Table `apply_stream_cfg`
1364-- -----------------------------------------------------
1365CREATE TABLE IF NOT EXISTS `apply_stream_cfg` (
1366  `apply_stream_cfg_id` INT(11) NOT NULL AUTO_INCREMENT,
1367  `apply_stream_id` INT(11) NOT NULL,
1368  `apply_container_id` INT(11) NOT NULL,
1369  `sort_order` INT(11) NOT NULL DEFAULT '0',
1370  PRIMARY KEY (`apply_stream_cfg_id`),
1371  INDEX `apply_stream_id` (`apply_stream_id` ASC),
1372  INDEX `apply_container_id` (`apply_container_id` ASC),
1373  CONSTRAINT `FK_asc_container_id`
1374    FOREIGN KEY (`apply_container_id`)
1375    REFERENCES `apply_container` (`apply_container_id`)
1376    ON DELETE CASCADE
1377    ON UPDATE CASCADE,
1378  CONSTRAINT `FK_asc_stream_id`
1379    FOREIGN KEY (`apply_stream_id`)
1380    REFERENCES `apply_stream` (`apply_stream_id`)
1381    ON DELETE CASCADE
1382    ON UPDATE CASCADE)
1383ENGINE = InnoDB
1384AUTO_INCREMENT = 66
1385DEFAULT CHARACTER SET = latin1;
1386
1387
1388-- -----------------------------------------------------
1389-- Table `apply_container_cfg`
1390-- -----------------------------------------------------
1391CREATE TABLE IF NOT EXISTS `apply_container_cfg` (
1392  `apply_container_cfg_id` INT(11) NOT NULL AUTO_INCREMENT,
1393  `apply_stream_cfg_id` INT(11) NOT NULL,
1394  `apply_form_id` INT(11) NOT NULL,
1395  `sort_order` INT(11) NOT NULL DEFAULT '0',
1396  PRIMARY KEY (`apply_container_cfg_id`),
1397  INDEX `apply_stream_cfg_id` (`apply_stream_cfg_id` ASC),
1398  INDEX `apply_form_id` (`apply_form_id` ASC),
1399  CONSTRAINT `FK_asc_form_id`
1400    FOREIGN KEY (`apply_form_id`)
1401    REFERENCES `apply_form` (`apply_form_id`)
1402    ON DELETE CASCADE
1403    ON UPDATE CASCADE,
1404  CONSTRAINT `FK_asc_streamcfg_id2`
1405    FOREIGN KEY (`apply_stream_cfg_id`)
1406    REFERENCES `apply_stream_cfg` (`apply_stream_cfg_id`)
1407    ON DELETE CASCADE
1408    ON UPDATE CASCADE)
1409ENGINE = InnoDB
1410AUTO_INCREMENT = 81
1411DEFAULT CHARACTER SET = latin1;
1412
1413
1414-- -----------------------------------------------------
1415-- Table `apply_form_cfg`
1416-- -----------------------------------------------------
1417CREATE TABLE IF NOT EXISTS `apply_form_cfg` (
1418  `apply_form_cfg_id` INT(11) NOT NULL AUTO_INCREMENT,
1419  `apply_stream_id` INT(11) NOT NULL,
1420  `apply_form_id` INT(11) NOT NULL,
1421  `apply_form_config` TEXT NULL DEFAULT NULL,
1422  PRIMARY KEY (`apply_form_cfg_id`),
1423  INDEX `apply_stream_id` (`apply_stream_id` ASC),
1424  INDEX `apply_form_id` (`apply_form_id` ASC),
1425  CONSTRAINT `FK_asc_form_id2`
1426    FOREIGN KEY (`apply_form_id`)
1427    REFERENCES `apply_form` (`apply_form_id`)
1428    ON DELETE CASCADE
1429    ON UPDATE CASCADE,
1430  CONSTRAINT `FK_asc_stream_id2`
1431    FOREIGN KEY (`apply_stream_id`)
1432    REFERENCES `apply_stream` (`apply_stream_id`)
1433    ON DELETE CASCADE
1434    ON UPDATE CASCADE)
1435ENGINE = InnoDB
1436AUTO_INCREMENT = 79
1437DEFAULT CHARACTER SET = latin1;
1438
1439
1440-- -----------------------------------------------------
1441-- Table `approval_chain`
1442-- -----------------------------------------------------
1443CREATE TABLE IF NOT EXISTS `approval_chain` (
1444  `approval_chain_id` INT(11) NOT NULL AUTO_INCREMENT,
1445  `name` VARCHAR(50) NOT NULL,
1446  `description` VARCHAR(255) NULL DEFAULT 'Generic Approval Chain',
1447  `hierarchy_id` INT(11) NOT NULL,
1448  `chain_type` VARCHAR(16) NOT NULL,
1449  `active` CHAR(1) NULL DEFAULT 'f',
1450  PRIMARY KEY (`approval_chain_id`),
1451  UNIQUE INDEX `chain_name` (`approval_chain_id` ASC, `name` ASC),
1452  INDEX `ac_hid_fk` (`hierarchy_id` ASC),
1453  CONSTRAINT `ac_hid_fk`
1454    FOREIGN KEY (`hierarchy_id`)
1455    REFERENCES `hierarchy` (`hierarchy_id`)
1456    ON DELETE CASCADE)
1457ENGINE = InnoDB
1458AUTO_INCREMENT = 23
1459DEFAULT CHARACTER SET = latin1;
1460
1461
1462-- -----------------------------------------------------
1463-- Table `approval_chain_detail`
1464-- -----------------------------------------------------
1465CREATE TABLE IF NOT EXISTS `approval_chain_detail` (
1466  `approval_chain_detail_id` INT(11) NOT NULL AUTO_INCREMENT,
1467  `approval_chain_id` INT(11) NULL DEFAULT NULL,
1468  `users_id` INT(11) NULL DEFAULT NULL,
1469  `sort_order` INT(2) NULL DEFAULT NULL,
1470  PRIMARY KEY (`approval_chain_detail_id`),
1471  INDEX `acd_acid_fk1` (`approval_chain_id` ASC),
1472  CONSTRAINT `acd_acid_fk1`
1473    FOREIGN KEY (`approval_chain_id`)
1474    REFERENCES `approval_chain` (`approval_chain_id`)
1475    ON DELETE CASCADE)
1476ENGINE = InnoDB
1477AUTO_INCREMENT = 121
1478DEFAULT CHARACTER SET = latin1;
1479
1480
1481-- -----------------------------------------------------
1482-- Table `assessment`
1483-- -----------------------------------------------------
1484CREATE TABLE IF NOT EXISTS `assessment` (
1485  `assessment_id` INT(11) NOT NULL AUTO_INCREMENT,
1486  `candidate_id` INT(11) NOT NULL DEFAULT '0',
1487  `vendor_id` CHAR(100) NULL DEFAULT NULL,
1488  `vendor_record_id` CHAR(100) NULL DEFAULT NULL,
1489  `vendor_position_code` CHAR(50) NULL DEFAULT NULL,
1490  `test_status` CHAR(100) NULL DEFAULT NULL,
1491  `screening_status` CHAR(100) NULL DEFAULT NULL,
1492  `currently_screening` CHAR(1) NULL DEFAULT NULL,
1493  `employment_status` CHAR(100) NULL DEFAULT NULL,
1494  `num_tests_completed` INT(3) NULL DEFAULT NULL,
1495  `num_tests_required` INT(3) NULL DEFAULT NULL,
1496  `value` CHAR(100) NULL DEFAULT NULL,
1497  `rating` CHAR(100) NULL DEFAULT NULL,
1498  `available` CHAR(1) NULL DEFAULT NULL,
1499  `url` CHAR(255) NULL DEFAULT NULL,
1500  `last_login_date` DATE NULL DEFAULT NULL,
1501  `create_date` DATETIME NULL DEFAULT NULL,
1502  `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1503  `pending` CHAR(1) NULL DEFAULT NULL,
1504  `application_id` INT(11) NULL DEFAULT NULL,
1505  PRIMARY KEY (`assessment_id`),
1506  INDEX `can_id` (`candidate_id` ASC),
1507  INDEX `application_id` (`application_id` ASC))
1508ENGINE = InnoDB
1509AUTO_INCREMENT = 837
1510DEFAULT CHARACTER SET = latin1;
1511
1512
1513-- -----------------------------------------------------
1514-- Table `assessment_history`
1515-- -----------------------------------------------------
1516CREATE TABLE IF NOT EXISTS `assessment_history` (
1517  `assessment_history_id` INT(11) NOT NULL AUTO_INCREMENT,
1518  `history_date` DATETIME NULL DEFAULT NULL,
1519  `assessment_id` INT(11) NOT NULL,
1520  `candidate_id` INT(11) NOT NULL DEFAULT '0',
1521  `vendor_id` CHAR(100) NULL DEFAULT NULL,
1522  `vendor_record_id` CHAR(100) NULL DEFAULT NULL,
1523  `vendor_position_code` CHAR(50) NULL DEFAULT NULL,
1524  `test_status` CHAR(100) NULL DEFAULT NULL,
1525  `screening_status` CHAR(100) NULL DEFAULT NULL,
1526  `currently_screening` CHAR(1) NULL DEFAULT NULL,
1527  `employment_status` CHAR(100) NULL DEFAULT NULL,
1528  `num_tests_completed` INT(3) NULL DEFAULT NULL,
1529  `num_tests_required` INT(3) NULL DEFAULT NULL,
1530  `value` CHAR(100) NULL DEFAULT NULL,
1531  `rating` CHAR(100) NULL DEFAULT NULL,
1532  `available` CHAR(1) NULL DEFAULT NULL,
1533  `url` CHAR(255) NULL DEFAULT NULL,
1534  `last_login_date` DATE NULL DEFAULT NULL,
1535  `create_date` DATETIME NULL DEFAULT NULL,
1536  `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
1537  `pending` CHAR(1) NULL DEFAULT NULL,
1538  `application_id` INT(20) NULL DEFAULT NULL,
1539  PRIMARY KEY (`assessment_history_id`),
1540  INDEX `assessment_id` (`assessment_id` ASC),
1541  INDEX `can_id` (`candidate_id` ASC),
1542  INDEX `application_id` (`application_id` ASC))
1543ENGINE = InnoDB
1544AUTO_INCREMENT = 11
1545DEFAULT CHARACTER SET = latin1;
1546
1547
1548-- -----------------------------------------------------
1549-- Table `atsintegration`
1550-- -----------------------------------------------------
1551CREATE TABLE IF NOT EXISTS `atsintegration` (
1552  `integration_id` INT(11) NOT NULL AUTO_INCREMENT,
1553  `client_short_name` VARCHAR(50) NULL DEFAULT NULL,
1554  `intstatus` CHAR(1) NULL DEFAULT NULL,
1555  `username` VARCHAR(255) NULL DEFAULT NULL,
1556  `password` VARCHAR(255) NULL DEFAULT NULL,
1557  `file_pick_location` VARCHAR(255) NULL DEFAULT NULL,
1558  `consideration_days` INT(10) NULL DEFAULT NULL,
1559  `candidate_type` VARCHAR(50) NULL DEFAULT NULL,
1560  `referee` CHAR(1) NULL DEFAULT NULL,
1561  `student` CHAR(1) NULL DEFAULT NULL,
1562  `not_eligible_rehire` CHAR(1) NULL DEFAULT NULL,
1563  `no_contact` CHAR(1) NULL DEFAULT NULL,
1564  `veteran` CHAR(1) NULL DEFAULT NULL,
1565  `inconsideration` CHAR(1) NULL DEFAULT NULL,
1566  `s3_bucket_name` VARCHAR(150) NULL DEFAULT NULL,
1567  `s3_folder_name` VARCHAR(100) NULL DEFAULT NULL,
1568  `crm_query_limit` INT(20) NULL DEFAULT '100',
1569  PRIMARY KEY (`integration_id`))
1570ENGINE = InnoDB
1571AUTO_INCREMENT = 2
1572DEFAULT CHARACTER SET = utf8;
1573
1574
1575-- -----------------------------------------------------
1576-- Table `attachment`
1577-- -----------------------------------------------------
1578CREATE TABLE IF NOT EXISTS `attachment` (
1579  `attachment_id` INT(11) NOT NULL AUTO_INCREMENT,
1580  `candidate_id` INT(11) NOT NULL DEFAULT '0',
1581  `name` VARCHAR(60) NULL DEFAULT NULL,
1582  `type` VARCHAR(100) NULL DEFAULT NULL,
1583  `description` VARCHAR(30) NULL DEFAULT NULL,
1584  `document` MEDIUMBLOB NULL DEFAULT NULL,
1585  `create_date` DATETIME NULL DEFAULT NULL,
1586  `attachment_type` VARCHAR(16) NULL DEFAULT NULL,
1587  `is_can_upload` CHAR(1) NULL DEFAULT NULL,
1588  PRIMARY KEY (`attachment_id`),
1589  INDEX `att_candidate_ndx` (`candidate_id` ASC))
1590ENGINE = InnoDB
1591AUTO_INCREMENT = 94600
1592DEFAULT CHARACTER SET = latin1;
1593
1594
1595-- -----------------------------------------------------
1596-- Table `automated_email_configuration`
1597-- -----------------------------------------------------
1598CREATE TABLE IF NOT EXISTS `automated_email_configuration` (
1599  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
1600  `activate_automated_job_match_email` BIT(1) NULL DEFAULT NULL,
1601  `activate_country_whitelisting` BIT(1) NULL DEFAULT NULL,
1602  `country_whitelisting` VARCHAR(255) NULL DEFAULT NULL,
1603  `email_blackout_hours` INT(11) NULL DEFAULT NULL,
1604  `employee_receive_autojob_emails` BIT(1) NULL DEFAULT NULL,
1605  `job_email_delay_after_joining_xcloud_minutes` INT(11) NULL DEFAULT NULL,
1606  `job__emai_logo` VARCHAR(255) NULL DEFAULT NULL,
1607  `job_emailmap_destination_url` VARCHAR(255) NULL DEFAULT NULL,
1608  `job_emailmap_image_url` VARCHAR(255) NULL DEFAULT NULL,
1609  `job_email_organization_title` VARCHAR(255) NULL DEFAULT NULL,
1610  `job_email_social_content` VARCHAR(255) NULL DEFAULT NULL,
1611  `skilled_job_email_frequency_hours` INT(11) NULL DEFAULT NULL,
1612  `default_job_category` VARCHAR(45) NULL DEFAULT NULL,
1613  `from_address` VARCHAR(45) NULL DEFAULT NULL,
1614  `skilled_job_email_template` VARCHAR(45) NULL DEFAULT NULL,
1615  `nonskilled_job_email_template` VARCHAR(45) NULL DEFAULT NULL,
1616  `news_letter_email_template` VARCHAR(45) NULL DEFAULT NULL,
1617  `location_from_range` INT(11) NULL DEFAULT NULL,
1618  `location_to_range` INT(11) NULL DEFAULT NULL,
1619  `location_unit` VARCHAR(45) NULL DEFAULT NULL,
1620  PRIMARY KEY (`id`))
1621ENGINE = InnoDB
1622DEFAULT CHARACTER SET = latin1;
1623
1624
1625-- -----------------------------------------------------
1626-- Table `background_check`
1627-- -----------------------------------------------------
1628CREATE TABLE IF NOT EXISTS `background_check` (
1629  `background_check_id` INT(20) NOT NULL AUTO_INCREMENT,
1630  `vendor_id` VARCHAR(50) NULL DEFAULT NULL,
1631  `users_id` INT(20) NOT NULL DEFAULT '0',
1632  `candidate_id` INT(20) NOT NULL DEFAULT '0',
1633  `package_id` VARCHAR(75) NULL DEFAULT NULL,
1634  `product_id` VARCHAR(75) NULL DEFAULT NULL,
1635  `order_id` VARCHAR(75) NULL DEFAULT NULL,
1636  `order_date` DATE NULL DEFAULT NULL,
1637  `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1638  `status` VARCHAR(50) NULL DEFAULT NULL,
1639  `result_available` CHAR(1) NULL DEFAULT 'f',
1640  `result_url` VARCHAR(255) NULL DEFAULT NULL,
1641  `result_text` TEXT NULL DEFAULT NULL,
1642  `complete` CHAR(1) NULL DEFAULT NULL,
1643  `current` CHAR(1) NULL DEFAULT NULL,
1644  `custom1` VARCHAR(255) NULL DEFAULT NULL,
1645  PRIMARY KEY (`background_check_id`),
1646  INDEX `candidate_id` (`candidate_id` ASC),
1647  INDEX `users_id` (`users_id` ASC),
1648  INDEX `idx_vendor_cand_key` (`vendor_id`(25) ASC, `candidate_id` ASC),
1649  INDEX `idx_vendor_pkg_key` (`vendor_id`(25) ASC, `candidate_id` ASC, `package_id`(25) ASC))
1650ENGINE = InnoDB
1651AUTO_INCREMENT = 580
1652DEFAULT CHARACTER SET = latin1;
1653
1654
1655-- -----------------------------------------------------
1656-- Table `bulk_request`
1657-- -----------------------------------------------------
1658CREATE TABLE IF NOT EXISTS `bulk_request` (
1659  `bulk_request_id` INT(20) NOT NULL AUTO_INCREMENT,
1660  `users_id` INT(20) NOT NULL DEFAULT '0',
1661  `bulk_api` VARCHAR(128) NULL DEFAULT NULL,
1662  `request_date` DATETIME NULL DEFAULT NULL,
1663  `response_date` DATETIME NULL DEFAULT NULL,
1664  `request_response` LONGTEXT NULL DEFAULT NULL,
1665  PRIMARY KEY (`bulk_request_id`),
1666  INDEX `br_users_asso_ndx` (`users_id` ASC),
1667  CONSTRAINT `fk_br_usersid`
1668    FOREIGN KEY (`users_id`)
1669    REFERENCES `users` (`users_id`)
1670    ON DELETE CASCADE
1671    ON UPDATE CASCADE)
1672ENGINE = InnoDB
1673AUTO_INCREMENT = 15893
1674DEFAULT CHARACTER SET = latin1;
1675
1676
1677-- -----------------------------------------------------
1678-- Table `calendar`
1679-- -----------------------------------------------------
1680CREATE TABLE IF NOT EXISTS `calendar` (
1681  `calendar_id` INT(20) NOT NULL AUTO_INCREMENT,
1682  `users_id` INT(20) NOT NULL DEFAULT '0',
1683  `interview_group_id` INT(20) NULL DEFAULT NULL,
1684  `name` CHAR(50) NULL DEFAULT NULL,
1685  `DATE` DATE NULL DEFAULT NULL,
1686  `time` TIME NULL DEFAULT NULL,
1687  `NUMBER` DECIMAL(3,0) NULL DEFAULT NULL,
1688  PRIMARY KEY (`calendar_id`),
1689  INDEX `calendar_users_asso_ndx` (`users_id` ASC),
1690  INDEX `DATE` (`DATE` ASC),
1691  INDEX `date_and_time` (`DATE` ASC, `time` ASC),
1692  INDEX `interview_group_id` (`interview_group_id` ASC, `users_id` ASC),
1693  CONSTRAINT `0_2552`
1694    FOREIGN KEY (`users_id`)
1695    REFERENCES `users` (`users_id`)
1696    ON DELETE CASCADE
1697    ON UPDATE CASCADE)
1698ENGINE = InnoDB
1699AUTO_INCREMENT = 15888
1700DEFAULT CHARACTER SET = latin1;
1701
1702
1703-- -----------------------------------------------------
1704-- Table `campaign`
1705-- -----------------------------------------------------
1706CREATE TABLE IF NOT EXISTS `campaign` (
1707  `campaign_id` INT(11) NOT NULL AUTO_INCREMENT,
1708  `users_id` INT(11) NOT NULL,
1709  `folder_id` INT(11) NOT NULL DEFAULT '0',
1710  `active` CHAR(1) NULL DEFAULT 't',
1711  `name` VARCHAR(32) NOT NULL,
1712  `label` VARCHAR(255) NOT NULL,
1713  `search_criteria` TEXT NULL DEFAULT NULL,
1714  `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
1715  `updated` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
1716  PRIMARY KEY (`campaign_id`),
1717  INDEX `camp_uid_fk` (`users_id` ASC),
1718  CONSTRAINT `camp_uid_fk`
1719    FOREIGN KEY (`users_id`)
1720    REFERENCES `users` (`users_id`))
1721ENGINE = InnoDB
1722AUTO_INCREMENT = 108
1723DEFAULT CHARACTER SET = latin1;
1724
1725
1726-- -----------------------------------------------------
1727-- Table `campaign_personas`
1728-- -----------------------------------------------------
1729CREATE TABLE IF NOT EXISTS `campaign_personas` (
1730  `campaign_personas_id` INT(11) NOT NULL AUTO_INCREMENT,
1731  `campaign_id` INT(11) NOT NULL,
1732  `candidate_id` INT(11) NOT NULL,
1733  `emailed` CHAR(1) NULL DEFAULT 't',
1734  `communication_template_id` INT(11) NULL DEFAULT NULL,
1735  `last_communication` DATETIME NULL DEFAULT NULL,
1736  `last_response` DATETIME NULL DEFAULT NULL,
1737  `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
1738  `updated` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
1739  PRIMARY KEY (`campaign_personas_id`),
1740  INDEX `cp_cid_fk` (`campaign_id` ASC),
1741  INDEX `cp_candid_fk` (`candidate_id` ASC),
1742  CONSTRAINT `cp_candid_fk`
1743    FOREIGN KEY (`candidate_id`)
1744    REFERENCES `candidate` (`candidate_id`)
1745    ON DELETE CASCADE
1746    ON UPDATE CASCADE,
1747  CONSTRAINT `cp_cid_fk`
1748    FOREIGN KEY (`campaign_id`)
1749    REFERENCES `campaign` (`campaign_id`)
1750    ON DELETE CASCADE
1751    ON UPDATE CASCADE)
1752ENGINE = InnoDB
1753AUTO_INCREMENT = 2280
1754DEFAULT CHARACTER SET = latin1;
1755
1756
1757-- -----------------------------------------------------
1758-- Table `candidate_repository`
1759-- -----------------------------------------------------
1760CREATE TABLE IF NOT EXISTS `candidate_repository` (
1761  `candidate_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
1762  `candidate_id` INT(20) NOT NULL,
1763  `uname` VARCHAR(75) NOT NULL,
1764  `change_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1765  `data` TEXT NULL DEFAULT NULL,
1766  PRIMARY KEY (`candidate_repository_id`),
1767  INDEX `cr_canid_fk` (`candidate_id` ASC),
1768  CONSTRAINT `cr_canid_fk`
1769    FOREIGN KEY (`candidate_id`)
1770    REFERENCES `candidate` (`candidate_id`)
1771    ON DELETE CASCADE
1772    ON UPDATE CASCADE)
1773ENGINE = InnoDB
1774AUTO_INCREMENT = 144
1775DEFAULT CHARACTER SET = latin1;
1776
1777
1778-- -----------------------------------------------------
1779-- Table `candidate_search`
1780-- -----------------------------------------------------
1781CREATE TABLE IF NOT EXISTS `candidate_search` (
1782  `candidate_search_id` INT(10) NOT NULL AUTO_INCREMENT,
1783  `requisition_id` INT(20) NOT NULL,
1784  `users_id` INT(10) NOT NULL,
1785  `criteria` TEXT NULL DEFAULT NULL,
1786  `sdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1787  PRIMARY KEY (`candidate_search_id`),
1788  INDEX `FK_candidate_search` (`requisition_id` ASC),
1789  INDEX `FK_users_id` (`users_id` ASC))
1790ENGINE = InnoDB
1791AUTO_INCREMENT = 109
1792DEFAULT CHARACTER SET = latin1;
1793
1794
1795-- -----------------------------------------------------
1796-- Table `candidate_search_result`
1797-- -----------------------------------------------------
1798CREATE TABLE IF NOT EXISTS `candidate_search_result` (
1799  `candidate_search_id` INT(10) NOT NULL,
1800  `candidate_id` INT(20) NOT NULL,
1801  INDEX `FK_candidate_search_id` (`candidate_search_id` ASC),
1802  INDEX `FK_candidate_search_result` (`candidate_id` ASC))
1803ENGINE = InnoDB
1804DEFAULT CHARACTER SET = latin1;
1805
1806
1807-- -----------------------------------------------------
1808-- Table `certification`
1809-- -----------------------------------------------------
1810CREATE TABLE IF NOT EXISTS `certification` (
1811  `certification_id` INT(20) NOT NULL AUTO_INCREMENT,
1812  `candidate_id` INT(20) NULL DEFAULT NULL,
1813  `name` VARCHAR(150) NULL DEFAULT NULL,
1814  `cert_number` VARCHAR(50) NULL DEFAULT NULL,
1815  `country` VARCHAR(50) NULL DEFAULT NULL,
1816  `issue_date` DATE NULL DEFAULT NULL,
1817  `expire_date` DATE NULL DEFAULT NULL,
1818  `description` VARCHAR(500) NULL DEFAULT NULL,
1819  `reviewed` CHAR(1) NULL DEFAULT NULL,
1820  PRIMARY KEY (`certification_id`),
1821  INDEX `candidate_id` (`candidate_id` ASC),
1822  CONSTRAINT `certification_ibfk_1`
1823    FOREIGN KEY (`candidate_id`)
1824    REFERENCES `candidate` (`candidate_id`)
1825    ON DELETE CASCADE
1826    ON UPDATE CASCADE)
1827ENGINE = InnoDB
1828DEFAULT CHARACTER SET = latin1;
1829
1830
1831-- -----------------------------------------------------
1832-- Table `certification_repository`
1833-- -----------------------------------------------------
1834CREATE TABLE IF NOT EXISTS `certification_repository` (
1835  `certification_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
1836  `certification_id` INT(20) NOT NULL,
1837  `uname` VARCHAR(75) NOT NULL,
1838  `change_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1839  `data` TEXT NULL DEFAULT NULL,
1840  PRIMARY KEY (`certification_repository_id`),
1841  INDEX `er_ctid_fk` (`certification_id` ASC),
1842  CONSTRAINT `er_ctid_fk`
1843    FOREIGN KEY (`certification_id`)
1844    REFERENCES `certification` (`certification_id`)
1845    ON DELETE CASCADE
1846    ON UPDATE CASCADE)
1847ENGINE = InnoDB
1848DEFAULT CHARACTER SET = latin1;
1849
1850
1851-- -----------------------------------------------------
1852-- Table `columns_priv`
1853-- -----------------------------------------------------
1854CREATE TABLE IF NOT EXISTS `columns_priv` (
1855  `Host` CHAR(60) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
1856  `Db` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
1857  `User` CHAR(16) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
1858  `Table_name` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
1859  `Column_name` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
1860  `Timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1861  `Column_priv` SET('Select', 'Insert', 'Update', 'References') NOT NULL DEFAULT '',
1862  PRIMARY KEY (`Host`, `Db`, `User`, `Table_name`, `Column_name`))
1863ENGINE = MyISAM
1864DEFAULT CHARACTER SET = utf8
1865COLLATE = utf8_bin
1866COMMENT = 'Column privileges';
1867
1868
1869-- -----------------------------------------------------
1870-- Table `communication_template`
1871-- -----------------------------------------------------
1872CREATE TABLE IF NOT EXISTS `communication_template` (
1873  `communication_template_id` INT(20) NOT NULL AUTO_INCREMENT,
1874  `users_id` INT(20) NOT NULL DEFAULT '0',
1875  `type` VARCHAR(50) NULL DEFAULT NULL,
1876  `name` VARCHAR(50) NULL DEFAULT NULL,
1877  `subject` VARCHAR(50) NULL DEFAULT NULL,
1878  `form` TEXT NULL DEFAULT NULL,
1879  `global` CHAR(1) NULL DEFAULT NULL,
1880  `active` CHAR(1) NULL DEFAULT 't',
1881  PRIMARY KEY (`communication_template_id`),
1882  INDEX `comm_users_asso_ndx` (`users_id` ASC),
1883  CONSTRAINT `0_2191`
1884    FOREIGN KEY (`users_id`)
1885    REFERENCES `users` (`users_id`)
1886    ON DELETE CASCADE
1887    ON UPDATE CASCADE)
1888ENGINE = InnoDB
1889AUTO_INCREMENT = 123
1890DEFAULT CHARACTER SET = latin1;
1891
1892
1893-- -----------------------------------------------------
1894-- Table `communication_log`
1895-- -----------------------------------------------------
1896CREATE TABLE IF NOT EXISTS `communication_log` (
1897  `communication_log_id` INT(20) NOT NULL AUTO_INCREMENT,
1898  `communication_template_id` INT(11) NULL DEFAULT NULL,
1899  `candidate_id` INT(20) NULL DEFAULT NULL,
1900  `users_id` INT(20) NULL DEFAULT NULL,
1901  `ip` VARCHAR(64) NULL DEFAULT NULL,
1902  `session_info` VARCHAR(255) NULL DEFAULT NULL,
1903  `type` VARCHAR(10) NULL DEFAULT NULL,
1904  `origin` VARCHAR(10) NULL DEFAULT NULL,
1905  `vendor` VARCHAR(64) NULL DEFAULT NULL,
1906  `vendor_key` VARCHAR(64) NULL DEFAULT NULL,
1907  `communication` TEXT NULL DEFAULT NULL,
1908  `date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
1909  `notes` TEXT NULL DEFAULT NULL,
1910  `error` VARCHAR(100) NULL DEFAULT NULL,
1911  `category` VARCHAR(20) NULL DEFAULT NULL,
1912  `unread` CHAR(1) NULL DEFAULT NULL,
1913  `importance` VARCHAR(10) NULL DEFAULT NULL,
1914  `last_sendgrid_Event` VARCHAR(20) NULL DEFAULT NULL,
1915  `last_sendgrid_Event_DateTime` TIMESTAMP NULL DEFAULT NULL,
1916  PRIMARY KEY (`communication_log_id`),
1917  INDEX `communication_template_id` (`communication_template_id` ASC),
1918  INDEX `candidate_id` (`candidate_id` ASC),
1919  INDEX `users_id` (`users_id` ASC),
1920  INDEX `FK_communication_log_users_id_unread` (`users_id` ASC, `unread` ASC),
1921  CONSTRAINT `communication_log_ibfk_1`
1922    FOREIGN KEY (`communication_template_id`)
1923    REFERENCES `communication_template` (`communication_template_id`)
1924    ON DELETE CASCADE
1925    ON UPDATE CASCADE,
1926  CONSTRAINT `communication_log_ibfk_3`
1927    FOREIGN KEY (`users_id`)
1928    REFERENCES `users` (`users_id`)
1929    ON DELETE CASCADE
1930    ON UPDATE CASCADE)
1931ENGINE = InnoDB
1932AUTO_INCREMENT = 7306
1933DEFAULT CHARACTER SET = latin1;
1934
1935
1936-- -----------------------------------------------------
1937-- Table `compensation`
1938-- -----------------------------------------------------
1939CREATE TABLE IF NOT EXISTS `compensation` (
1940  `compensation_id` INT(20) NOT NULL AUTO_INCREMENT,
1941  `requisition_id` INT(20) NOT NULL DEFAULT '0',
1942  `compensation` VARCHAR(50) NULL DEFAULT NULL,
1943  PRIMARY KEY (`compensation_id`),
1944  INDEX `requisition_id` (`requisition_id` ASC))
1945ENGINE = InnoDB
1946DEFAULT CHARACTER SET = latin1;
1947
1948
1949-- -----------------------------------------------------
1950-- Table `cost_detail`
1951-- -----------------------------------------------------
1952CREATE TABLE IF NOT EXISTS `cost_detail` (
1953  `cost_detail_id` INT(11) NOT NULL AUTO_INCREMENT,
1954  `requisition_id` INT(20) NOT NULL DEFAULT '0',
1955  `users_id` INT(11) NOT NULL DEFAULT '0',
1956  `advertising` INT(10) NULL DEFAULT NULL,
1957  `subscriptions` INT(10) NULL DEFAULT NULL,
1958  `bonuses` INT(10) NULL DEFAULT NULL,
1959  `relocation` INT(10) NULL DEFAULT NULL,
1960  `referral` INT(10) NULL DEFAULT NULL,
1961  `travel` INT(10) NULL DEFAULT NULL,
1962  `agency` INT(10) NULL DEFAULT NULL,
1963  `labor` INT(10) NULL DEFAULT NULL,
1964  `additional` INT(10) NULL DEFAULT NULL,
1965  `comments` VARCHAR(255) NULL DEFAULT NULL,
1966  `trans_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
1967  PRIMARY KEY (`cost_detail_id`),
1968  INDEX `requisition_id` (`requisition_id` ASC),
1969  INDEX `users_id` (`users_id` ASC),
1970  CONSTRAINT `cost_detail_ibfk_2`
1971    FOREIGN KEY (`requisition_id`)
1972    REFERENCES `requisition_orig` (`requisition_id`)
1973    ON DELETE CASCADE
1974    ON UPDATE CASCADE,
1975  CONSTRAINT `cost_detail_ibfk_4`
1976    FOREIGN KEY (`users_id`)
1977    REFERENCES `users` (`users_id`)
1978    ON DELETE CASCADE
1979    ON UPDATE CASCADE)
1980ENGINE = InnoDB
1981AUTO_INCREMENT = 782
1982DEFAULT CHARACTER SET = latin1;
1983
1984
1985-- -----------------------------------------------------
1986-- Table `custom_field_names`
1987-- -----------------------------------------------------
1988CREATE TABLE IF NOT EXISTS `custom_field_names` (
1989  `id` INT(20) NOT NULL AUTO_INCREMENT,
1990  `table_name` VARCHAR(50) NULL DEFAULT NULL,
1991  `column_name` VARCHAR(50) NULL DEFAULT NULL,
1992  `column_label` VARCHAR(50) NULL DEFAULT NULL,
1993  `visual_label` VARCHAR(50) NULL DEFAULT NULL,
1994  `visual_prefs` TEXT NULL DEFAULT NULL,
1995  `column_datatype` varchar(50) DEFAULT NULL,    
1996  `column_exists_in_xcloud` char(1) DEFAULT 'N',
1997  `column_empty_in_clientdb` char(1) DEFAULT 'N',   
1998  PRIMARY KEY (`id`),
1999  UNIQUE INDEX `table_column` (`table_name` ASC, `column_name` ASC),
2000  UNIQUE INDEX `column_label` (`table_name` ASC, `column_label` ASC))
2001ENGINE = InnoDB
2002AUTO_INCREMENT = 17
2003DEFAULT CHARACTER SET = latin1;
2004
2005
2006-- -----------------------------------------------------
2007-- Table `data_applications`
2008-- -----------------------------------------------------
2009CREATE TABLE IF NOT EXISTS `data_applications` (
2010  `data_application_id` INT(12) NOT NULL AUTO_INCREMENT,
2011  `client` VARCHAR(50) NULL DEFAULT NULL,
2012  `reporting_date` DATE NULL DEFAULT NULL,
2013  `apply_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2014  `candidate_id` INT(11) NULL DEFAULT NULL,
2015  `application_id` INT(11) NULL DEFAULT NULL,
2016  `requisition_id` INT(11) NULL DEFAULT NULL,
2017  `status` VARCHAR(50) NULL DEFAULT NULL,
2018  `status_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
2019  `step_current` VARCHAR(50) NULL DEFAULT NULL,
2020  `hierarchy_id` INT(11) NULL DEFAULT NULL,
2021  `users_id` INT(11) NULL DEFAULT NULL,
2022  PRIMARY KEY (`data_application_id`),
2023  INDEX `reporting_date_idx` (`reporting_date` ASC))
2024ENGINE = InnoDB
2025AUTO_INCREMENT = 2914
2026DEFAULT CHARACTER SET = latin1;
2027
2028
2029-- -----------------------------------------------------
2030-- Table `data_applications_counts_timeseries`
2031-- -----------------------------------------------------
2032CREATE TABLE IF NOT EXISTS `data_applications_counts_timeseries` (
2033  `data_applications_counts_timeseries_id` INT(11) NOT NULL AUTO_INCREMENT,
2034  `total` INT(11) NULL DEFAULT NULL,
2035  `requisition_id` INT(11) NULL DEFAULT NULL,
2036  `reporting_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2037  `client` VARCHAR(50) NULL DEFAULT NULL,
2038  PRIMARY KEY (`data_applications_counts_timeseries_id`),
2039  INDEX `reporting_date_idx` (`reporting_date` ASC, `requisition_id` ASC))
2040ENGINE = InnoDB
2041AUTO_INCREMENT = 566
2042DEFAULT CHARACTER SET = latin1;
2043
2044
2045-- -----------------------------------------------------
2046-- Table `data_applications_interm_status_counts`
2047-- -----------------------------------------------------
2048CREATE TABLE IF NOT EXISTS `data_applications_interm_status_counts` (
2049  `reporting_date` DATE NULL DEFAULT NULL,
2050  `total` INT(11) NULL DEFAULT NULL,
2051  `applied_count` INT(11) NULL DEFAULT NULL,
2052  `candidate_count` INT(11) NULL DEFAULT NULL,
2053  `offer_count` INT(11) NULL DEFAULT NULL,
2054  `hired_count` INT(11) NULL DEFAULT NULL,
2055  `rejected_count` INT(11) NULL DEFAULT NULL,
2056  `requisition_id` INT(11) NULL DEFAULT NULL,
2057  `data_application_interm_status_counts_id` INT(11) NOT NULL AUTO_INCREMENT,
2058  PRIMARY KEY (`data_application_interm_status_counts_id`),
2059  INDEX `reporting_date_idx` (`reporting_date` ASC, `requisition_id` ASC))
2060ENGINE = InnoDB
2061AUTO_INCREMENT = 1535
2062DEFAULT CHARACTER SET = latin1;
2063
2064
2065-- -----------------------------------------------------
2066-- Table `data_applications_status_timeseries`
2067-- -----------------------------------------------------
2068CREATE TABLE IF NOT EXISTS `data_applications_status_timeseries` (
2069  `data_applications_status_timeseries_id` INT(11) NOT NULL AUTO_INCREMENT,
2070  `reporting_date` DATE NULL DEFAULT NULL,
2071  `requisition_id` INT(11) NULL DEFAULT NULL,
2072  `total` INT(11) NULL DEFAULT NULL,
2073  `applied_count` INT(11) NULL DEFAULT NULL,
2074  `candidate_count` INT(11) NULL DEFAULT NULL,
2075  `offer_count` INT(11) NULL DEFAULT NULL,
2076  `hired_count` INT(11) NULL DEFAULT NULL,
2077  `rejected_count` INT(11) NULL DEFAULT NULL,
2078  PRIMARY KEY (`data_applications_status_timeseries_id`),
2079  INDEX `reporting_date_idx` (`reporting_date` ASC, `requisition_id` ASC))
2080ENGINE = InnoDB
2081AUTO_INCREMENT = 707
2082DEFAULT CHARACTER SET = latin1;
2083
2084
2085-- -----------------------------------------------------
2086-- Table `data_time_in_steps`
2087-- -----------------------------------------------------
2088CREATE TABLE IF NOT EXISTS `data_time_in_steps` (
2089  `requisition_id` BIGINT(20) NULL DEFAULT NULL,
2090  `HIRED` DOUBLE NULL DEFAULT NULL,
2091  `REJECTNOSHOW` DOUBLE NULL DEFAULT NULL,
2092  `AGENCYSUBMITTEDAPPLICANT` DOUBLE NULL DEFAULT NULL,
2093  `OFFERACCEPTED` DOUBLE NULL DEFAULT NULL,
2094  `SCHEDULEINTERVIEW` DOUBLE NULL DEFAULT NULL,
2095  `MOREINFORMATIONNEEDED` DOUBLE NULL DEFAULT NULL,
2096  `PHONESCREEN` DOUBLE NULL DEFAULT NULL,
2097  `REVIEWRESUME` DOUBLE NULL DEFAULT NULL,
2098  `EXTENDOFFER` DOUBLE NULL DEFAULT NULL,
2099  `BACKGROUNDCHECK` DOUBLE NULL DEFAULT NULL,
2100  `REJECTIMPROPERLYDRESSED` DOUBLE NULL DEFAULT NULL,
2101  `REJECTFOUNDOTHERWORK` DOUBLE NULL DEFAULT NULL,
2102  `OKTOHIRE` DOUBLE NULL DEFAULT NULL,
2103  `SENDTOMANAGER` DOUBLE NULL DEFAULT NULL,
2104  `INTERVIEWING` DOUBLE NULL DEFAULT NULL,
2105  `OFFER01` DOUBLE NULL DEFAULT NULL,
2106  `NOTREVIEWED` DOUBLE NULL DEFAULT NULL,
2107  `CANDIDATE` DOUBLE NULL DEFAULT NULL,
2108  `CANDIDATEREJECTED` DOUBLE NULL DEFAULT NULL,
2109  `OFFER` DOUBLE NULL DEFAULT NULL,
2110  `REJECTNORESPONSEFORINTERVIEW` DOUBLE NULL DEFAULT NULL,
2111  `REJECTED` DOUBLE NULL DEFAULT NULL,
2112  `REJECTNOTQUALIFIED` DOUBLE NULL DEFAULT NULL)
2113ENGINE = InnoDB
2114DEFAULT CHARACTER SET = latin1;
2115
2116
2117-- -----------------------------------------------------
2118-- Table `data_time_in_steps_by_rows`
2119-- -----------------------------------------------------
2120CREATE TABLE IF NOT EXISTS `data_time_in_steps_by_rows` (
2121  `requisition_id` BIGINT(20) NULL DEFAULT NULL,
2122  `average_time_in_step` DOUBLE NULL DEFAULT NULL,
2123  `step` TEXT NULL DEFAULT NULL)
2124ENGINE = InnoDB
2125DEFAULT CHARACTER SET = latin1;
2126
2127
2128-- -----------------------------------------------------
2129-- Table `data_time_to_hire`
2130-- -----------------------------------------------------
2131CREATE TABLE IF NOT EXISTS `data_time_to_hire` (
2132  `client` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
2133  `reporting_date` DATE NULL DEFAULT NULL,
2134  `requisition_id` INT(11) NULL DEFAULT NULL,
2135  `open_date` DATETIME NULL DEFAULT NULL,
2136  `close_date` DATETIME NULL DEFAULT NULL,
2137  `most_recent_activity` DATETIME NULL DEFAULT NULL,
2138  `days_to_hire` INT(6) NULL DEFAULT NULL,
2139  `data_time_to_hire_id` INT(11) NOT NULL AUTO_INCREMENT,
2140  `hierarchy_id` INT(11) NULL DEFAULT NULL,
2141  `users_id` INT(11) NULL DEFAULT NULL,
2142  PRIMARY KEY (`data_time_to_hire_id`),
2143  INDEX `reporting_date_idx` (`reporting_date` ASC),
2144  INDEX `requisition_id_idx` (`requisition_id` ASC))
2145ENGINE = InnoDB
2146AUTO_INCREMENT = 11429
2147DEFAULT CHARACTER SET = utf8
2148COLLATE = utf8_unicode_ci;
2149
2150
2151-- -----------------------------------------------------
2152-- Table `db`
2153-- -----------------------------------------------------
2154CREATE TABLE IF NOT EXISTS `db` (
2155  `Host` CHAR(60) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2156  `Db` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2157  `User` CHAR(16) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2158  `Select_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2159  `Insert_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2160  `Update_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2161  `Delete_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2162  `Create_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2163  `Drop_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2164  `Grant_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2165  `References_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2166  `Index_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2167  `Alter_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2168  `Create_tmp_table_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2169  `Lock_tables_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2170  `Create_view_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2171  `Show_view_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2172  `Create_routine_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2173  `Alter_routine_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2174  `Execute_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2175  `Event_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2176  `Trigger_priv` ENUM('N', 'Y') CHARACTER SET 'utf8' NOT NULL DEFAULT 'N',
2177  PRIMARY KEY (`Host`, `Db`, `User`),
2178  INDEX `User` (`User` ASC))
2179ENGINE = MyISAM
2180DEFAULT CHARACTER SET = utf8
2181COLLATE = utf8_bin
2182COMMENT = 'Database privileges';
2183
2184
2185-- -----------------------------------------------------
2186-- Table `delete_request`
2187-- -----------------------------------------------------
2188CREATE TABLE IF NOT EXISTS `delete_request` (
2189  `delete_request_id` INT(11) NOT NULL AUTO_INCREMENT,
2190  `candidate_id` INT(11) NOT NULL DEFAULT '0',
2191  `users_id` INT(11) NOT NULL DEFAULT '0',
2192  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2193  PRIMARY KEY (`delete_request_id`),
2194  INDEX `dr_candidate_ndx` (`candidate_id` ASC),
2195  INDEX `dr_users_ndx` (`users_id` ASC),
2196  CONSTRAINT `0_2431`
2197    FOREIGN KEY (`candidate_id`)
2198    REFERENCES `candidate_orig` (`candidate_id`)
2199    ON DELETE CASCADE
2200    ON UPDATE CASCADE,
2201  CONSTRAINT `0_2432`
2202    FOREIGN KEY (`users_id`)
2203    REFERENCES `users` (`users_id`)
2204    ON DELETE CASCADE
2205    ON UPDATE CASCADE)
2206ENGINE = InnoDB
2207AUTO_INCREMENT = 34
2208DEFAULT CHARACTER SET = latin1;
2209
2210
2211-- -----------------------------------------------------
2212-- Table `education`
2213-- -----------------------------------------------------
2214CREATE TABLE IF NOT EXISTS `education` (
2215  `education_id` INT(11) NOT NULL AUTO_INCREMENT,
2216  `candidate_id` INT(11) NOT NULL DEFAULT '0',
2217  `institution` CHAR(80) NULL DEFAULT NULL,
2218  `city` CHAR(50) NULL DEFAULT NULL,
2219  `state` CHAR(2) NULL DEFAULT NULL,
2220  `country` CHAR(50) NULL DEFAULT NULL,
2221  `start_date` DATE NULL DEFAULT NULL,
2222  `end_date` DATE NULL DEFAULT NULL,
2223  `subject` CHAR(80) NULL DEFAULT NULL,
2224  `level_attained` CHAR(80) NULL DEFAULT NULL,
2225  `description` CHAR(150) NULL DEFAULT NULL,
2226  `completed` CHAR(3) NULL DEFAULT NULL,
2227  `ref_checked` CHAR(1) NULL DEFAULT NULL,
2228  `reviewed` CHAR(1) NULL DEFAULT NULL,
2229  PRIMARY KEY (`education_id`),
2230  INDEX `candidate_ndx` (`candidate_id` ASC),
2231  CONSTRAINT `0_3345`
2232    FOREIGN KEY (`candidate_id`)
2233    REFERENCES `candidate` (`candidate_id`)
2234    ON DELETE CASCADE
2235    ON UPDATE CASCADE)
2236ENGINE = InnoDB
2237AUTO_INCREMENT = 243979
2238DEFAULT CHARACTER SET = latin1;
2239
2240
2241-- -----------------------------------------------------
2242-- Table `education_repository`
2243-- -----------------------------------------------------
2244CREATE TABLE IF NOT EXISTS `education_repository` (
2245  `education_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
2246  `education_id` INT(20) NOT NULL,
2247  `uname` VARCHAR(75) NOT NULL,
2248  `change_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2249  `data` TEXT NULL DEFAULT NULL,
2250  PRIMARY KEY (`education_repository_id`),
2251  INDEX `er_edid_fk` (`education_id` ASC),
2252  CONSTRAINT `er_edid_fk`
2253    FOREIGN KEY (`education_id`)
2254    REFERENCES `education` (`education_id`)
2255    ON DELETE CASCADE
2256    ON UPDATE CASCADE)
2257ENGINE = InnoDB
2258AUTO_INCREMENT = 246
2259DEFAULT CHARACTER SET = latin1;
2260
2261
2262-- -----------------------------------------------------
2263-- Table `eval_questions`
2264-- -----------------------------------------------------
2265CREATE TABLE IF NOT EXISTS `eval_questions` (
2266  `requisition_id` INT(20) NOT NULL DEFAULT '0',
2267  `question_group_id` INT(20) NOT NULL DEFAULT '0',
2268  `sort_order` INT(5) NOT NULL DEFAULT '0',
2269  PRIMARY KEY (`requisition_id`, `question_group_id`),
2270  INDEX `req_eval_asso_ndx` (`requisition_id` ASC),
2271  INDEX `question_group_eval_asso_ndx` (`question_group_id` ASC),
2272  CONSTRAINT `0_3439`
2273    FOREIGN KEY (`requisition_id`)
2274    REFERENCES `requisition_orig` (`requisition_id`)
2275    ON DELETE CASCADE
2276    ON UPDATE CASCADE,
2277  CONSTRAINT `0_3440`
2278    FOREIGN KEY (`question_group_id`)
2279    REFERENCES `question_group` (`question_group_id`)
2280    ON DELETE CASCADE
2281    ON UPDATE CASCADE)
2282ENGINE = InnoDB
2283DEFAULT CHARACTER SET = latin1;
2284
2285
2286-- -----------------------------------------------------
2287-- Table `evaluation`
2288-- -----------------------------------------------------
2289CREATE TABLE IF NOT EXISTS `evaluation` (
2290  `users_id` INT(20) NOT NULL DEFAULT '0',
2291  `answers_id` INT(20) NOT NULL DEFAULT '0',
2292  `candidate_id` INT(20) NOT NULL DEFAULT '0',
2293  `application_id` INT(20) NULL DEFAULT NULL,
2294  `eval_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2295  INDEX `application_id` (`application_id` ASC),
2296  INDEX `users_id` (`users_id` ASC),
2297  INDEX `answers_id` (`answers_id` ASC),
2298  INDEX `candidate_id` (`candidate_id` ASC),
2299  CONSTRAINT `evaluation_ibfk_2`
2300    FOREIGN KEY (`users_id`)
2301    REFERENCES `users` (`users_id`)
2302    ON DELETE CASCADE
2303    ON UPDATE CASCADE,
2304  CONSTRAINT `evaluation_ibfk_3`
2305    FOREIGN KEY (`answers_id`)
2306    REFERENCES `answers` (`answers_id`)
2307    ON DELETE CASCADE
2308    ON UPDATE CASCADE)
2309ENGINE = InnoDB
2310DEFAULT CHARACTER SET = latin1;
2311
2312
2313-- -----------------------------------------------------
2314-- Table `event`
2315-- -----------------------------------------------------
2316CREATE TABLE IF NOT EXISTS `event` (
2317  `db` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2318  `name` CHAR(64) NOT NULL DEFAULT '',
2319  `body` LONGBLOB NOT NULL,
2320  `definer` CHAR(77) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2321  `execute_at` DATETIME NULL DEFAULT NULL,
2322  `interval_value` INT(11) NULL DEFAULT NULL,
2323  `interval_field` ENUM('YEAR', 'QUARTER', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'WEEK', 'SECOND', 'MICROSECOND', 'YEAR_MONTH', 'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'HOUR_MINUTE', 'HOUR_SECOND', 'MINUTE_SECOND', 'DAY_MICROSECOND', 'HOUR_MICROSECOND', 'MINUTE_MICROSECOND', 'SECOND_MICROSECOND') NULL DEFAULT NULL,
2324  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2325  `modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
2326  `last_executed` DATETIME NULL DEFAULT NULL,
2327  `starts` DATETIME NULL DEFAULT NULL,
2328  `ends` DATETIME NULL DEFAULT NULL,
2329  `status` ENUM('ENABLED', 'DISABLED', 'SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
2330  `on_completion` ENUM('DROP', 'PRESERVE') NOT NULL DEFAULT 'DROP',
2331  `sql_mode` SET('REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
2332  `comment` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2333  `originator` INT(10) UNSIGNED NOT NULL,
2334  `time_zone` CHAR(64) CHARACTER SET 'latin1' NOT NULL DEFAULT 'SYSTEM',
2335  `character_set_client` CHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
2336  `collation_connection` CHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
2337  `db_collation` CHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL,
2338  `body_utf8` LONGBLOB NULL DEFAULT NULL,
2339  PRIMARY KEY (`db`, `name`))
2340ENGINE = MyISAM
2341DEFAULT CHARACTER SET = utf8
2342COMMENT = 'Events';
2343
2344
2345-- -----------------------------------------------------
2346-- Table `event_interview`
2347-- -----------------------------------------------------
2348CREATE TABLE IF NOT EXISTS `event_interview` (
2349  `event_interview_id` INT(20) NOT NULL AUTO_INCREMENT,
2350  `type` CHAR(30) NOT NULL,
2351  `event_date` DATETIME NOT NULL,
2352  `params` TEXT NULL DEFAULT NULL,
2353  PRIMARY KEY (`event_interview_id`))
2354ENGINE = InnoDB
2355AUTO_INCREMENT = 246
2356DEFAULT CHARACTER SET = latin1;
2357
2358
2359-- -----------------------------------------------------
2360-- Table `event_option`
2361-- -----------------------------------------------------
2362CREATE TABLE IF NOT EXISTS `event_option` (
2363  `event_option_id` INT(10) NOT NULL AUTO_INCREMENT,
2364  `type` CHAR(30) NOT NULL,
2365  `config` TEXT NULL DEFAULT NULL,
2366  `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2367  `active` CHAR(1) NULL DEFAULT 'f',
2368  PRIMARY KEY (`event_option_id`))
2369ENGINE = InnoDB
2370AUTO_INCREMENT = 19
2371DEFAULT CHARACTER SET = latin1;
2372
2373
2374-- -----------------------------------------------------
2375-- Table `event_store`
2376-- -----------------------------------------------------
2377CREATE TABLE IF NOT EXISTS `event_store` (
2378  `event_store_id` INT(20) NOT NULL AUTO_INCREMENT,
2379  `type` CHAR(30) NOT NULL,
2380  `event_date` DATETIME NOT NULL,
2381  `params` TEXT NULL DEFAULT NULL,
2382  PRIMARY KEY (`event_store_id`))
2383ENGINE = InnoDB
2384DEFAULT CHARACTER SET = latin1;
2385
2386
2387-- -----------------------------------------------------
2388-- Table `event_worker`
2389-- -----------------------------------------------------
2390CREATE TABLE IF NOT EXISTS `event_worker` (
2391  `event_worker_id` INT(11) NOT NULL AUTO_INCREMENT,
2392  `event_option_id` INT(11) NOT NULL DEFAULT '0',
2393  `type` CHAR(30) NULL DEFAULT NULL,
2394  `active` CHAR(1) NULL DEFAULT NULL,
2395  PRIMARY KEY (`event_worker_id`),
2396  INDEX `event_option_ndx` (`event_option_id` ASC),
2397  CONSTRAINT `event_worker_const`
2398    FOREIGN KEY (`event_option_id`)
2399    REFERENCES `event_option` (`event_option_id`)
2400    ON DELETE CASCADE
2401    ON UPDATE CASCADE)
2402ENGINE = InnoDB
2403AUTO_INCREMENT = 61
2404DEFAULT CHARACTER SET = latin1;
2405
2406
2407-- -----------------------------------------------------
2408-- Table `expertise`
2409-- -----------------------------------------------------
2410CREATE TABLE IF NOT EXISTS `expertise` (
2411  `expertise_id` INT(20) NOT NULL AUTO_INCREMENT,
2412  `requisition_id` INT(20) NOT NULL DEFAULT '0',
2413  `expertise` VARCHAR(50) NULL DEFAULT NULL,
2414  PRIMARY KEY (`expertise_id`),
2415  INDEX `requisition_id` (`requisition_id` ASC),
2416  CONSTRAINT `expertise_ibfk_1`
2417    FOREIGN KEY (`requisition_id`)
2418    REFERENCES `requisition_orig` (`requisition_id`)
2419    ON DELETE CASCADE
2420    ON UPDATE CASCADE)
2421ENGINE = InnoDB
2422DEFAULT CHARACTER SET = latin1;
2423
2424
2425-- -----------------------------------------------------
2426-- Table `failure_log`
2427-- -----------------------------------------------------
2428CREATE TABLE IF NOT EXISTS `failure_log` (
2429  `failure_log_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Failure queue id',
2430  `date_occurred` DATETIME NOT NULL COMMENT 'Date and time failure occured',
2431  `business_unit` VARCHAR(30) NULL DEFAULT NULL COMMENT 'Company name (module)',
2432  `interface_type` VARCHAR(50) NOT NULL,
2433  `interface_name` VARCHAR(50) NOT NULL,
2434  `failure_type` VARCHAR(25) NOT NULL,
2435  `description` TEXT NULL DEFAULT NULL COMMENT 'xml',
2436  `request` VARCHAR(240) NULL DEFAULT NULL,
2437  PRIMARY KEY (`failure_log_id`))
2438ENGINE = InnoDB
2439AUTO_INCREMENT = 4769
2440DEFAULT CHARACTER SET = latin1;
2441
2442
2443-- -----------------------------------------------------
2444-- Table `failure_queue`
2445-- -----------------------------------------------------
2446CREATE TABLE IF NOT EXISTS `failure_queue` (
2447  `failure_queue_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2448  `failure_log_id` INT(10) UNSIGNED NOT NULL,
2449  `payload` MEDIUMBLOB NULL DEFAULT NULL,
2450  `status` CHAR(1) NULL DEFAULT NULL,
2451  `retry_date` DATETIME NULL DEFAULT NULL,
2452  PRIMARY KEY (`failure_queue_id`),
2453  INDEX `FK_failure_queue` (`failure_log_id` ASC),
2454  CONSTRAINT `FK_failure_queue`
2455    FOREIGN KEY (`failure_log_id`)
2456    REFERENCES `failure_log` (`failure_log_id`)
2457    ON DELETE CASCADE)
2458ENGINE = InnoDB
2459AUTO_INCREMENT = 93
2460DEFAULT CHARACTER SET = latin1;
2461
2462
2463-- -----------------------------------------------------
2464-- Table `felon`
2465-- -----------------------------------------------------
2466CREATE TABLE IF NOT EXISTS `felon` (
2467  `felon_id` INT(10) NOT NULL AUTO_INCREMENT,
2468  `candidate_id` INT(20) NULL DEFAULT NULL,
2469  `nature` VARCHAR(50) NULL DEFAULT NULL,
2470  `location` VARCHAR(50) NULL DEFAULT NULL,
2471  `city` VARCHAR(50) NULL DEFAULT NULL,
2472  `state` CHAR(2) NULL DEFAULT NULL,
2473  `county` VARCHAR(50) NULL DEFAULT NULL,
2474  `date` DATE NULL DEFAULT NULL,
2475  `court` VARCHAR(50) NULL DEFAULT NULL,
2476  `disposition` TEXT NULL DEFAULT NULL,
2477  `reviewed` CHAR(1) NULL DEFAULT NULL,
2478  PRIMARY KEY (`felon_id`),
2479  INDEX `candidate_id` (`candidate_id` ASC),
2480  CONSTRAINT `felon_ibfk_1`
2481    FOREIGN KEY (`candidate_id`)
2482    REFERENCES `candidate_orig` (`candidate_id`)
2483    ON DELETE CASCADE
2484    ON UPDATE CASCADE)
2485ENGINE = InnoDB
2486AUTO_INCREMENT = 56
2487DEFAULT CHARACTER SET = latin1;
2488
2489
2490-- -----------------------------------------------------
2491-- Table `folder`
2492-- -----------------------------------------------------
2493CREATE TABLE IF NOT EXISTS `folder` (
2494  `folder_id` INT(11) NOT NULL AUTO_INCREMENT,
2495  `users_id` INT(11) NULL DEFAULT NULL,
2496  `hierarchy_id` INT(11) NULL DEFAULT NULL,
2497  `requisition_id` INT(11) NOT NULL,
2498  `active` CHAR(1) NULL DEFAULT 't',
2499  `name` VARCHAR(32) NOT NULL,
2500  `label` VARCHAR(255) NOT NULL,
2501  `type` ENUM('live', 'persona', 'custom') NOT NULL DEFAULT 'custom',
2502  `search_criteria` TEXT NULL DEFAULT NULL,
2503  `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
2504  `updated` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
2505  PRIMARY KEY (`folder_id`),
2506  INDEX `folder_uid_fk` (`users_id` ASC),
2507  INDEX `folder_hid_fk` (`hierarchy_id` ASC),
2508  CONSTRAINT `folder_hid_fk`
2509    FOREIGN KEY (`hierarchy_id`)
2510    REFERENCES `hierarchy` (`hierarchy_id`),
2511  CONSTRAINT `folder_uid_fk`
2512    FOREIGN KEY (`users_id`)
2513    REFERENCES `users` (`users_id`))
2514ENGINE = InnoDB
2515AUTO_INCREMENT = 65610
2516DEFAULT CHARACTER SET = latin1;
2517
2518
2519-- -----------------------------------------------------
2520-- Table `folder_candidates`
2521-- -----------------------------------------------------
2522CREATE TABLE IF NOT EXISTS `folder_candidates` (
2523  `folder_cands_id` INT(11) NOT NULL AUTO_INCREMENT,
2524  `folder_id` INT(11) NOT NULL,
2525  `candidate_id` INT(11) NOT NULL,
2526  `emailed` CHAR(1) NULL DEFAULT 't',
2527  `communication_template_id` INT(11) NULL DEFAULT NULL,
2528  `last_communication` DATETIME NULL DEFAULT NULL,
2529  `last_response` DATETIME NULL DEFAULT NULL,
2530  `status` ENUM('associated', 'shortlisted') NOT NULL DEFAULT 'associated',
2531  `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
2532  `updated` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
2533  PRIMARY KEY (`folder_cands_id`),
2534  INDEX `fc_fid_fk` (`folder_id` ASC),
2535  INDEX `fc_candid_fk` (`candidate_id` ASC),
2536  CONSTRAINT `fc_candid_fk`
2537    FOREIGN KEY (`candidate_id`)
2538    REFERENCES `candidate` (`candidate_id`)
2539    ON DELETE CASCADE
2540    ON UPDATE CASCADE,
2541  CONSTRAINT `fc_fid_fk`
2542    FOREIGN KEY (`folder_id`)
2543    REFERENCES `folder` (`folder_id`)
2544    ON DELETE CASCADE
2545    ON UPDATE CASCADE)
2546ENGINE = InnoDB
2547AUTO_INCREMENT = 1993
2548DEFAULT CHARACTER SET = latin1;
2549
2550
2551-- -----------------------------------------------------
2552-- Table `form`
2553-- -----------------------------------------------------
2554CREATE TABLE IF NOT EXISTS `form` (
2555  `form_id` INT(10) NOT NULL AUTO_INCREMENT,
2556  `name` VARCHAR(50) NULL DEFAULT NULL,
2557  `form` VARCHAR(100) NULL DEFAULT NULL,
2558  PRIMARY KEY (`form_id`))
2559ENGINE = InnoDB
2560DEFAULT CHARACTER SET = latin1;
2561
2562
2563-- -----------------------------------------------------
2564-- Table `from_address_options`
2565-- -----------------------------------------------------
2566CREATE TABLE IF NOT EXISTS `from_address_options` (
2567  `id` BIGINT(20) NOT NULL,
2568  `from_address` VARCHAR(255) NULL DEFAULT NULL,
2569  PRIMARY KEY (`id`))
2570ENGINE = InnoDB
2571DEFAULT CHARACTER SET = utf8;
2572
2573
2574-- -----------------------------------------------------
2575-- Table `func`
2576-- -----------------------------------------------------
2577CREATE TABLE IF NOT EXISTS `func` (
2578  `name` CHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2579  `ret` TINYINT(1) NOT NULL DEFAULT '0',
2580  `dl` CHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL DEFAULT '',
2581  `type` ENUM('function', 'aggregate') CHARACTER SET 'utf8' NOT NULL,
2582  PRIMARY KEY (`name`))
2583ENGINE = MyISAM
2584DEFAULT CHARACTER SET = utf8
2585COLLATE = utf8_bin
2586COMMENT = 'User defined functions';
2587
2588
2589-- -----------------------------------------------------
2590-- Table `general_log`
2591-- -----------------------------------------------------
2592CREATE TABLE IF NOT EXISTS `general_log` (
2593  `event_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2594  `user_host` MEDIUMTEXT NOT NULL,
2595  `thread_id` BIGINT(21) UNSIGNED NOT NULL,
2596  `server_id` INT(10) UNSIGNED NOT NULL,
2597  `command_type` VARCHAR(64) NOT NULL,
2598  `argument` MEDIUMTEXT NOT NULL)
2599ENGINE = CSV
2600DEFAULT CHARACTER SET = utf8
2601COMMENT = 'General log';
2602
2603
2604-- -----------------------------------------------------
2605-- Table `page_info`
2606-- -----------------------------------------------------
2607CREATE TABLE IF NOT EXISTS `page_info` (
2608  `page_info_id` INT(20) NOT NULL AUTO_INCREMENT,
2609  `name` VARCHAR(50) NULL DEFAULT NULL,
2610  `page` VARCHAR(50) NULL DEFAULT NULL,
2611  `functional_area` VARCHAR(50) NULL DEFAULT NULL,
2612  `type` VARCHAR(50) NULL DEFAULT NULL,
2613  `active` CHAR(1) NULL DEFAULT NULL,
2614  PRIMARY KEY (`page_info_id`),
2615  INDEX `functional_area` (`functional_area` ASC))
2616ENGINE = InnoDB
2617AUTO_INCREMENT = 104
2618DEFAULT CHARACTER SET = latin1;
2619
2620
2621-- -----------------------------------------------------
2622-- Table `group_portals`
2623-- -----------------------------------------------------
2624CREATE TABLE IF NOT EXISTS `group_portals` (
2625  `groups_id` INT(11) NOT NULL DEFAULT '0',
2626  `page_info_id` INT(11) NOT NULL DEFAULT '0',
2627  INDEX `groups_id` (`groups_id` ASC),
2628  INDEX `page_info_id` (`page_info_id` ASC),
2629  CONSTRAINT `group_portal_ibfk_1`
2630    FOREIGN KEY (`groups_id`)
2631    REFERENCES `groups` (`groups_id`)
2632    ON DELETE CASCADE
2633    ON UPDATE CASCADE,
2634  CONSTRAINT `group_portal_ibfk_2`
2635    FOREIGN KEY (`page_info_id`)
2636    REFERENCES `page_info` (`page_info_id`)
2637    ON DELETE CASCADE
2638    ON UPDATE CASCADE)
2639ENGINE = InnoDB
2640DEFAULT CHARACTER SET = latin1;
2641
2642
2643-- -----------------------------------------------------
2644-- Table `roles`
2645-- -----------------------------------------------------
2646CREATE TABLE IF NOT EXISTS `roles` (
2647  `role_id` INT(5) NOT NULL AUTO_INCREMENT,
2648  `name` VARCHAR(40) NOT NULL,
2649  PRIMARY KEY (`role_id`),
2650  UNIQUE INDEX `name` (`name` ASC))
2651ENGINE = InnoDB
2652AUTO_INCREMENT = 25
2653DEFAULT CHARACTER SET = latin1;
2654
2655
2656-- -----------------------------------------------------
2657-- Table `group_roles`
2658-- -----------------------------------------------------
2659CREATE TABLE IF NOT EXISTS `group_roles` (
2660  `groups_id` INT(11) NOT NULL,
2661  `role_id` INT(5) NOT NULL,
2662  INDEX `gr_groupsId` (`groups_id` ASC),
2663  INDEX `gr_roleId` (`role_id` ASC),
2664  CONSTRAINT `gr_groupsId`
2665    FOREIGN KEY (`groups_id`)
2666    REFERENCES `groups` (`groups_id`)
2667    ON DELETE CASCADE
2668    ON UPDATE CASCADE,
2669  CONSTRAINT `gr_roleId`
2670    FOREIGN KEY (`role_id`)
2671    REFERENCES `roles` (`role_id`)
2672    ON DELETE CASCADE
2673    ON UPDATE CASCADE)
2674ENGINE = InnoDB
2675DEFAULT CHARACTER SET = latin1;
2676
2677
2678-- -----------------------------------------------------
2679-- Table `help_category`
2680-- -----------------------------------------------------
2681CREATE TABLE IF NOT EXISTS `help_category` (
2682  `help_category_id` SMALLINT(5) UNSIGNED NOT NULL,
2683  `name` CHAR(64) NOT NULL,
2684  `parent_category_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
2685  `url` TEXT NOT NULL,
2686  PRIMARY KEY (`help_category_id`),
2687  UNIQUE INDEX `name` (`name` ASC))
2688ENGINE = MyISAM
2689DEFAULT CHARACTER SET = utf8
2690COMMENT = 'help categories';
2691
2692
2693-- -----------------------------------------------------
2694-- Table `help_keyword`
2695-- -----------------------------------------------------
2696CREATE TABLE IF NOT EXISTS `help_keyword` (
2697  `help_keyword_id` INT(10) UNSIGNED NOT NULL,
2698  `name` CHAR(64) NOT NULL,
2699  PRIMARY KEY (`help_keyword_id`),
2700  UNIQUE INDEX `name` (`name` ASC))
2701ENGINE = MyISAM
2702DEFAULT CHARACTER SET = utf8
2703COMMENT = 'help keywords';
2704
2705
2706-- -----------------------------------------------------
2707-- Table `help_relation`
2708-- -----------------------------------------------------
2709CREATE TABLE IF NOT EXISTS `help_relation` (
2710  `help_topic_id` INT(10) UNSIGNED NOT NULL,
2711  `help_keyword_id` INT(10) UNSIGNED NOT NULL,
2712  PRIMARY KEY (`help_keyword_id`, `help_topic_id`))
2713ENGINE = MyISAM
2714DEFAULT CHARACTER SET = utf8
2715COMMENT = 'keyword-topic relation';
2716
2717
2718-- -----------------------------------------------------
2719-- Table `help_topic`
2720-- -----------------------------------------------------
2721CREATE TABLE IF NOT EXISTS `help_topic` (
2722  `help_topic_id` INT(10) UNSIGNED NOT NULL,
2723  `name` CHAR(64) NOT NULL,
2724  `help_category_id` SMALLINT(5) UNSIGNED NOT NULL,
2725  `description` TEXT NOT NULL,
2726  `example` TEXT NOT NULL,
2727  `url` TEXT NOT NULL,
2728  PRIMARY KEY (`help_topic_id`),
2729  UNIQUE INDEX `name` (`name` ASC))
2730ENGINE = MyISAM
2731DEFAULT CHARACTER SET = utf8
2732COMMENT = 'help topics';
2733
2734
2735-- -----------------------------------------------------
2736-- Table `hierarchy_tool_track`
2737-- -----------------------------------------------------
2738CREATE TABLE IF NOT EXISTS `hierarchy_tool_track` (
2739  `hierarchy_tool_track_id` INT(11) NOT NULL AUTO_INCREMENT,
2740  `action` VARCHAR(25) NOT NULL,
2741  `action_date` DATETIME NOT NULL,
2742  `admin_id` INT(11) NOT NULL,
2743  `hierarchy_id` INT(11) NULL DEFAULT NULL,
2744  `requisition_id` INT(11) NULL DEFAULT NULL,
2745  `users_id` INT(11) NULL DEFAULT NULL,
2746  `old_value` VARCHAR(50) NULL DEFAULT NULL,
2747  `new_value` VARCHAR(50) NULL DEFAULT NULL,
2748  `session_info` VARCHAR(40) NULL DEFAULT NULL,
2749  PRIMARY KEY (`hierarchy_tool_track_id`),
2750  INDEX `FK_hierarchy_tool_track_admin_id` (`admin_id` ASC),
2751  INDEX `FK_hierarchy_tool_track_users_id` (`users_id` ASC),
2752  INDEX `FK_hierarchy_tool_track_requisition_id` (`requisition_id` ASC),
2753  INDEX `FK_hierarchy_tool_track_hierarchy_id` (`hierarchy_id` ASC),
2754  CONSTRAINT `FK_hierarchy_tool_track_admin_id`
2755    FOREIGN KEY (`admin_id`)
2756    REFERENCES `users` (`users_id`)
2757    ON DELETE CASCADE
2758    ON UPDATE CASCADE,
2759  CONSTRAINT `FK_hierarchy_tool_track_hierarchy_id`
2760    FOREIGN KEY (`hierarchy_id`)
2761    REFERENCES `hierarchy` (`hierarchy_id`)
2762    ON DELETE CASCADE
2763    ON UPDATE CASCADE,
2764  CONSTRAINT `FK_hierarchy_tool_track_requisition_id`
2765    FOREIGN KEY (`requisition_id`)
2766    REFERENCES `requisition_orig` (`requisition_id`)
2767    ON DELETE CASCADE
2768    ON UPDATE CASCADE,
2769  CONSTRAINT `FK_hierarchy_tool_track_users_id`
2770    FOREIGN KEY (`users_id`)
2771    REFERENCES `users` (`users_id`)
2772    ON DELETE CASCADE
2773    ON UPDATE CASCADE)
2774ENGINE = InnoDB
2775AUTO_INCREMENT = 11
2776DEFAULT CHARACTER SET = latin1;
2777
2778
2779-- -----------------------------------------------------
2780-- Table `hl_children`
2781-- -----------------------------------------------------
2782CREATE TABLE IF NOT EXISTS `hl_children` (
2783  `hl_children_id` INT(10) NOT NULL AUTO_INCREMENT,
2784  `candidate_id` INT(20) NULL DEFAULT NULL,
2785  `name` VARCHAR(50) NULL DEFAULT NULL,
2786  `gender` VARCHAR(10) NULL DEFAULT NULL,
2787  `dob` DATE NULL DEFAULT NULL,
2788  PRIMARY KEY (`hl_children_id`),
2789  INDEX `candidate_id` (`candidate_id` ASC),
2790  CONSTRAINT `hl_children_ibfk_1`
2791    FOREIGN KEY (`candidate_id`)
2792    REFERENCES `candidate_orig` (`candidate_id`)
2793    ON DELETE CASCADE
2794    ON UPDATE CASCADE)
2795ENGINE = InnoDB
2796AUTO_INCREMENT = 2
2797DEFAULT CHARACTER SET = latin1;
2798
2799
2800-- -----------------------------------------------------
2801-- Table `hl_dd`
2802-- -----------------------------------------------------
2803CREATE TABLE IF NOT EXISTS `hl_dd` (
2804  `hl_dd_id` INT(10) NOT NULL AUTO_INCREMENT,
2805  `candidate_id` INT(20) NULL DEFAULT NULL,
2806  `bank_name` VARCHAR(50) NULL DEFAULT NULL,
2807  `bank_city` VARCHAR(50) NULL DEFAULT NULL,
2808  `bank_state` CHAR(2) NULL DEFAULT NULL,
2809  `bank_zip` VARCHAR(10) NULL DEFAULT NULL,
2810  `routing_num` VARCHAR(10) NULL DEFAULT NULL,
2811  `account_num` VARCHAR(10) NULL DEFAULT NULL,
2812  `check_num` INT(5) NULL DEFAULT NULL,
2813  `action` VARCHAR(10) NULL DEFAULT NULL,
2814  `account_type` CHAR(1) NULL DEFAULT NULL,
2815  `entire_amount` CHAR(1) NULL DEFAULT NULL,
2816  `distribution` VARCHAR(10) NULL DEFAULT NULL,
2817  PRIMARY KEY (`hl_dd_id`),
2818  INDEX `candidate_id` (`candidate_id` ASC),
2819  CONSTRAINT `hl_dd_ibfk_1`
2820    FOREIGN KEY (`candidate_id`)
2821    REFERENCES `candidate_orig` (`candidate_id`)
2822    ON DELETE CASCADE
2823    ON UPDATE CASCADE)
2824ENGINE = InnoDB
2825AUTO_INCREMENT = 22
2826DEFAULT CHARACTER SET = latin1;
2827
2828
2829-- -----------------------------------------------------
2830-- Table `hl_ec`
2831-- -----------------------------------------------------
2832CREATE TABLE IF NOT EXISTS `hl_ec` (
2833  `hl_ec_id` INT(10) NOT NULL AUTO_INCREMENT,
2834  `candidate_id` INT(20) NULL DEFAULT NULL,
2835  `name` VARCHAR(50) NULL DEFAULT NULL,
2836  `address1` VARCHAR(50) NULL DEFAULT NULL,
2837  `address2` VARCHAR(50) NULL DEFAULT NULL,
2838  `city` VARCHAR(50) NULL DEFAULT NULL,
2839  `state` CHAR(2) NULL DEFAULT NULL,
2840  `zip` VARCHAR(10) NULL DEFAULT NULL,
2841  `phone1` VARCHAR(15) NULL DEFAULT NULL,
2842  `phone2` VARCHAR(15) NULL DEFAULT NULL,
2843  `phone_type` VARCHAR(10) NULL DEFAULT NULL,
2844  `relationship` VARCHAR(50) NULL DEFAULT NULL,
2845  `company` VARCHAR(50) NULL DEFAULT NULL,
2846  PRIMARY KEY (`hl_ec_id`),
2847  INDEX `candidate_id` (`candidate_id` ASC),
2848  CONSTRAINT `hl_ec_ibfk_1`
2849    FOREIGN KEY (`candidate_id`)
2850    REFERENCES `candidate_orig` (`candidate_id`)
2851    ON DELETE CASCADE
2852    ON UPDATE CASCADE)
2853ENGINE = InnoDB
2854AUTO_INCREMENT = 10
2855DEFAULT CHARACTER SET = latin1;
2856
2857
2858-- -----------------------------------------------------
2859-- Table `page_version`
2860-- -----------------------------------------------------
2861CREATE TABLE IF NOT EXISTS `page_version` (
2862  `page_version_id` INT(11) NOT NULL AUTO_INCREMENT,
2863  `page_info_id` INT(11) NULL DEFAULT NULL,
2864  `distribution_start` DATETIME NULL DEFAULT NULL,
2865  `version_name` VARCHAR(50) NULL DEFAULT NULL,
2866  `application_page` VARCHAR(50) NULL DEFAULT NULL,
2867  `user_edit_page` VARCHAR(50) NULL DEFAULT NULL,
2868  PRIMARY KEY (`page_version_id`),
2869  INDEX `FK_page_version` (`page_info_id` ASC),
2870  CONSTRAINT `FK_page_version`
2871    FOREIGN KEY (`page_info_id`)
2872    REFERENCES `page_info` (`page_info_id`)
2873    ON DELETE CASCADE
2874    ON UPDATE CASCADE)
2875ENGINE = InnoDB
2876AUTO_INCREMENT = 38
2877DEFAULT CHARACTER SET = latin1;
2878
2879
2880-- -----------------------------------------------------
2881-- Table `hl_forms`
2882-- -----------------------------------------------------
2883CREATE TABLE IF NOT EXISTS `hl_forms` (
2884  `candidate_id` INT(20) NOT NULL DEFAULT '0',
2885  `page_version_id` INT(11) NOT NULL,
2886  `application_id` INT(20) NOT NULL DEFAULT '0',
2887  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2888  `page` BLOB NULL DEFAULT NULL,
2889  `accept` CHAR(1) NULL DEFAULT NULL,
2890  `last_imaged` DATETIME NULL DEFAULT NULL,
2891  PRIMARY KEY (`candidate_id`, `page_version_id`, `application_id`),
2892  INDEX `hl_forms_candidate_ndx` (`candidate_id` ASC),
2893  INDEX `hl_forms_application_ndx` (`application_id` ASC),
2894  INDEX `hl_forms_ibfk_2` (`page_version_id` ASC),
2895  CONSTRAINT `hl_forms_ibfk_1`
2896    FOREIGN KEY (`candidate_id`)
2897    REFERENCES `candidate_orig` (`candidate_id`)
2898    ON DELETE CASCADE
2899    ON UPDATE CASCADE,
2900  CONSTRAINT `hl_forms_ibfk_2`
2901    FOREIGN KEY (`page_version_id`)
2902    REFERENCES `page_version` (`page_version_id`)
2903    ON DELETE CASCADE
2904    ON UPDATE CASCADE)
2905ENGINE = InnoDB
2906DEFAULT CHARACTER SET = latin1;
2907
2908
2909-- -----------------------------------------------------
2910-- Table `hl_info`
2911-- -----------------------------------------------------
2912CREATE TABLE IF NOT EXISTS `hl_info` (
2913  `hl_info_id` INT(20) NOT NULL AUTO_INCREMENT,
2914  `candidate_id` INT(20) NOT NULL DEFAULT '0',
2915  `application_id` INT(11) NOT NULL,
2916  `sig_authorization` CHAR(1) NULL DEFAULT NULL,
2917  `full_name` VARCHAR(100) NULL DEFAULT NULL,
2918  `i9_citizen` CHAR(1) NULL DEFAULT NULL,
2919  `i9_resident_number` VARCHAR(30) NULL DEFAULT NULL,
2920  `i9_alien_date` DATE NULL DEFAULT NULL,
2921  `i9_alien_number` VARCHAR(30) NULL DEFAULT NULL,
2922  `i9_date` DATE NULL DEFAULT NULL,
2923  `i9_preparer_sig` VARCHAR(50) NULL DEFAULT NULL,
2924  `i9_preparer_name` VARCHAR(50) NULL DEFAULT NULL,
2925  `i9_preparer_date` DATE NULL DEFAULT NULL,
2926  `i9_preparer_address` VARCHAR(50) NULL DEFAULT NULL,
2927  `tc_date` DATE NULL DEFAULT NULL,
2928  `wc_marital_status` CHAR(1) NULL DEFAULT NULL,
2929  `wc_date` DATE NULL DEFAULT NULL,
2930  `wc_disability` CHAR(1) NULL DEFAULT NULL,
2931  `wc_injury` CHAR(1) NULL DEFAULT NULL,
2932  `wc_back_trouble` CHAR(1) NULL DEFAULT NULL,
2933  `wc_restrictions` CHAR(1) NULL DEFAULT NULL,
2934  `wc_operations` TEXT NULL DEFAULT NULL,
2935  `wc_health` CHAR(1) NULL DEFAULT NULL,
2936  `wc_disability_detail` CHAR(1) NULL DEFAULT NULL,
2937  `wc_injury_detail` CHAR(1) NULL DEFAULT NULL,
2938  `wc_back_trouble_detail` CHAR(1) NULL DEFAULT NULL,
2939  `wc_restrictions_detail` CHAR(1) NULL DEFAULT NULL,
2940  `wc_health_detail` CHAR(1) NULL DEFAULT NULL,
2941  `wc_ec_name` VARCHAR(50) NULL DEFAULT NULL,
2942  `wc_ec_address` VARCHAR(100) NULL DEFAULT NULL,
2943  `wc_ec_city` VARCHAR(50) NULL DEFAULT NULL,
2944  `wc_ec_state` CHAR(2) NULL DEFAULT NULL,
2945  `wc_ec_phone` VARCHAR(15) NULL DEFAULT NULL,
2946  `wc_ec_relationship` VARCHAR(50) NULL DEFAULT NULL,
2947  `wc_health_issues_1` VARCHAR(60) NULL DEFAULT NULL,
2948  `wc_health_issues_2` VARCHAR(60) NULL DEFAULT NULL,
2949  `wc_health_issues_detail` TEXT NULL DEFAULT NULL,
2950  `w4_fieldA` INT(2) NULL DEFAULT NULL,
2951  `w4_fieldB` INT(2) NULL DEFAULT NULL,
2952  `w4_fieldC` INT(2) NULL DEFAULT NULL,
2953  `w4_fieldD` INT(2) NULL DEFAULT NULL,
2954  `w4_fieldE` INT(2) NULL DEFAULT NULL,
2955  `w4_fieldF` INT(2) NULL DEFAULT NULL,
2956  `w4_fieldG` INT(2) NULL DEFAULT NULL,
2957  `w4_fieldH` INT(2) NULL DEFAULT NULL,
2958  `w4_marital_status` CHAR(2) NULL DEFAULT NULL,
2959  `w4_different_lname` CHAR(1) NULL DEFAULT NULL,
2960  `w4_allowances` INT(2) NULL DEFAULT NULL,
2961  `w4_withheld` VARCHAR(10) NULL DEFAULT NULL,
2962  `w4_exempt` VARCHAR(10) NULL DEFAULT NULL,
2963  `w4_employee_date` DATE NULL DEFAULT NULL,
2964  `w4_employer_info` TEXT NULL DEFAULT NULL,
2965  `w4_office_code` VARCHAR(20) NULL DEFAULT NULL,
2966  `w4_ein` VARCHAR(20) NULL DEFAULT NULL,
2967  `w4_deduction1` VARCHAR(10) NULL DEFAULT NULL,
2968  `w4_deduction2` VARCHAR(10) NULL DEFAULT NULL,
2969  `w4_deduction3` VARCHAR(10) NULL DEFAULT NULL,
2970  `w4_deduction4` VARCHAR(10) NULL DEFAULT NULL,
2971  `w4_deduction5` VARCHAR(10) NULL DEFAULT NULL,
2972  `w4_deduction6` VARCHAR(10) NULL DEFAULT NULL,
2973  `w4_deduction7` VARCHAR(10) NULL DEFAULT NULL,
2974  `w4_deduction8` VARCHAR(10) NULL DEFAULT NULL,
2975  `w4_deduction9` VARCHAR(10) NULL DEFAULT NULL,
2976  `w4_deduction10` VARCHAR(10) NULL DEFAULT NULL,
2977  `w4_two_job1` VARCHAR(10) NULL DEFAULT NULL,
2978  `w4_two_job2` VARCHAR(10) NULL DEFAULT NULL,
2979  `w4_two_job3` VARCHAR(10) NULL DEFAULT NULL,
2980  `w4_two_job4` VARCHAR(10) NULL DEFAULT NULL,
2981  `w4_two_job5` VARCHAR(10) NULL DEFAULT NULL,
2982  `w4_two_job6` VARCHAR(10) NULL DEFAULT NULL,
2983  `w4_two_job7` VARCHAR(10) NULL DEFAULT NULL,
2984  `w4_two_job8` VARCHAR(10) NULL DEFAULT NULL,
2985  `w4_two_job9` VARCHAR(10) NULL DEFAULT NULL,
2986  `con_date` DATE NULL DEFAULT NULL,
2987  `cup_date` DATE NULL DEFAULT NULL,
2988  `dc_date` DATE NULL DEFAULT NULL,
2989  `dd_date` DATE NULL DEFAULT NULL,
2990  `ec_date` DATE NULL DEFAULT NULL,
2991  `ehr_date` DATE NULL DEFAULT NULL,
2992  `nhs_department` VARCHAR(50) NULL DEFAULT NULL,
2993  `nhs_role` VARCHAR(20) NULL DEFAULT NULL,
2994  `nhs_role_detail` VARCHAR(50) NULL DEFAULT NULL,
2995  `nhs_orientation` CHAR(1) NULL DEFAULT NULL,
2996  `nhs_orient_attend` VARCHAR(500) NULL DEFAULT NULL,
2997  `nhs_orient_no_attend` VARCHAR(500) NULL DEFAULT NULL,
2998  `nhs_dept_manager` CHAR(1) NULL DEFAULT NULL,
2999  `nhs_training` CHAR(1) NULL DEFAULT NULL,
3000  `nhs_bene_info` CHAR(1) NULL DEFAULT NULL,
3001  `nhs_feedback` CHAR(1) NULL DEFAULT NULL,
3002  `nhs_teamwork` CHAR(1) NULL DEFAULT NULL,
3003  `nhs_improve` VARCHAR(500) NULL DEFAULT NULL,
3004  `i9_alien_admis_number` VARCHAR(30) NULL DEFAULT NULL,
3005  `aza4_date` DATE NULL DEFAULT NULL,
3006  `aza4_election1` VARCHAR(20) NULL DEFAULT NULL,
3007  `aza4_election1_pct` VARCHAR(20) NULL DEFAULT NULL,
3008  `aza4_election2` VARCHAR(20) NULL DEFAULT NULL,
3009  `aza4_election2_pct` VARCHAR(20) NULL DEFAULT NULL,
3010  `aza4_election3` VARCHAR(20) NULL DEFAULT NULL,
3011  `caw4_emp_account` VARCHAR(50) NULL DEFAULT NULL,
3012  `caw4_emp_info` VARCHAR(250) NULL DEFAULT NULL,
3013  `caw4_date` DATE NULL DEFAULT NULL,
3014  `caw4_line2` CHAR(30) NULL DEFAULT NULL,
3015  `caw4_line3` CHAR(30) NULL DEFAULT NULL,
3016  `caw4_line4` CHAR(30) NULL DEFAULT NULL,
3017  `caw4_line1` CHAR(30) NULL DEFAULT NULL,
3018  `caw4_filing_status` CHAR(3) NULL DEFAULT NULL,
3019  `dcw4_linea` VARCHAR(10) NULL DEFAULT NULL,
3020  `dcw4_lineb` VARCHAR(10) NULL DEFAULT NULL,
3021  `dcw4_linec` VARCHAR(10) NULL DEFAULT NULL,
3022  `dcw4_lined` VARCHAR(10) NULL DEFAULT NULL,
3023  `dcw4_linee` VARCHAR(10) NULL DEFAULT NULL,
3024  `dcw4_linef` VARCHAR(10) NULL DEFAULT NULL,
3025  `dcw4_lineg` VARCHAR(10) NULL DEFAULT NULL,
3026  `dcw4_lineh` VARCHAR(10) NULL DEFAULT NULL,
3027  `dcw4_linei` VARCHAR(10) NULL DEFAULT NULL,
3028  `dcw4_linej` VARCHAR(10) NULL DEFAULT NULL,
3029  `dcw4_linek` VARCHAR(10) NULL DEFAULT NULL,
3030  `dcw4_linel` VARCHAR(10) NULL DEFAULT NULL,
3031  `dcw4_linem` VARCHAR(10) NULL DEFAULT NULL,
3032  `dcw4_linen` VARCHAR(10) NULL DEFAULT NULL,
3033  `dcw4_lineo` VARCHAR(10) NULL DEFAULT NULL,
3034  `dcw4_date` DATE NULL DEFAULT NULL,
3035  `dcw4_line2` VARCHAR(5) NULL DEFAULT NULL,
3036  `dcw4_line3` VARCHAR(10) NULL DEFAULT NULL,
3037  `dcw4_line4` VARCHAR(10) NULL DEFAULT NULL,
3038  `dcw4_line5` CHAR(1) NULL DEFAULT NULL,
3039  `dcw4_status` CHAR(3) NULL DEFAULT NULL,
3040  `dcw4_apt` VARCHAR(10) NULL DEFAULT NULL,
3041  `gaw4_worksheet_2a` VARCHAR(20) NULL DEFAULT NULL,
3042  `gaw4_worksheet_2b` VARCHAR(20) NULL DEFAULT NULL,
3043  `gaw4_worksheet_2c` VARCHAR(20) NULL DEFAULT NULL,
3044  `gaw4_worksheet_2d` VARCHAR(20) NULL DEFAULT NULL,
3045  `gaw4_worksheet_2e` VARCHAR(20) NULL DEFAULT NULL,
3046  `gaw4_worksheet_2f` VARCHAR(20) NULL DEFAULT NULL,
3047  `gaw4_worksheet_2g` VARCHAR(20) NULL DEFAULT NULL,
3048  `gaw4_worksheet_2h` VARCHAR(20) NULL DEFAULT NULL,
3049  `gaw4_emp_fein` VARCHAR(20) NULL DEFAULT NULL,
3050  `gaw4_emp_whnum` VARCHAR(20) NULL DEFAULT NULL,
3051  `gaw4_date` DATE NULL DEFAULT NULL,
3052  `gaw4_line8` CHAR(1) NULL DEFAULT NULL,
3053  `gaw4_line7a` VARCHAR(1) NULL DEFAULT NULL,
3054  `gaw4_line7b` VARCHAR(20) NULL DEFAULT NULL,
3055  `gaw4_sd_yourself` VARCHAR(50) NULL DEFAULT NULL,
3056  `gaw4_sd_spouse` VARCHAR(50) NULL DEFAULT NULL,
3057  `gaw4_worksheet1a` VARCHAR(2) NULL DEFAULT NULL,
3058  `gaw4_worksheet1b` VARCHAR(10) NULL DEFAULT NULL,
3059  `gaw4_line3a` CHAR(1) NULL DEFAULT NULL,
3060  `gaw4_line3b` CHAR(1) NULL DEFAULT NULL,
3061  `gaw4_line3c` CHAR(1) NULL DEFAULT NULL,
3062  `gaw4_line3d` CHAR(1) NULL DEFAULT NULL,
3063  `gaw4_line3e` CHAR(1) NULL DEFAULT NULL,
3064  `gaw4_line4` CHAR(1) NULL DEFAULT NULL,
3065  `gaw4_line5` CHAR(1) NULL DEFAULT NULL,
3066  `gaw4_line6` VARCHAR(25) NULL DEFAULT NULL,
3067  `ilw4_blind` VARCHAR(20) NULL DEFAULT NULL,
3068  `ilw4_date` DATE NULL DEFAULT NULL,
3069  `ilw4_dependents` VARCHAR(20) NULL DEFAULT NULL,
3070  `ilw4_exempt` VARCHAR(20) NULL DEFAULT NULL,
3071  `ilw4_exemption1` VARCHAR(20) NULL DEFAULT NULL,
3072  `ilw4_exemption2` VARCHAR(20) NULL DEFAULT NULL,
3073  `ilw4_exemption3` VARCHAR(20) NULL DEFAULT NULL,
3074  `ilw4_exemption4` VARCHAR(20) NULL DEFAULT NULL,
3075  `ilw4_exemption5` VARCHAR(20) NULL DEFAULT NULL,
3076  `ilw4_exemption6` VARCHAR(20) NULL DEFAULT NULL,
3077  `ilw4_exemption7` VARCHAR(20) NULL DEFAULT NULL,
3078  `ilw4_exemption8` VARCHAR(20) NULL DEFAULT NULL,
3079  `ilw4_exemption9` VARCHAR(20) NULL DEFAULT NULL,
3080  `ilw4_over65` VARCHAR(20) NULL DEFAULT NULL,
3081  `ilw4_spouse_blind` VARCHAR(20) NULL DEFAULT NULL,
3082  `ilw4_spouse_over65` VARCHAR(20) NULL DEFAULT NULL,
3083  `ilw4_total1` VARCHAR(20) NULL DEFAULT NULL,
3084  `ilw4_total2` VARCHAR(20) NULL DEFAULT NULL,
3085  `ilw4_total3` VARCHAR(20) NULL DEFAULT NULL,
3086  `wh4_blind` VARCHAR(20) NULL DEFAULT NULL,
3087  `wh4_date` DATE NULL DEFAULT NULL,
3088  `wh4_employment_county` VARCHAR(20) NULL DEFAULT NULL,
3089  `wh4_exemption1` VARCHAR(20) NULL DEFAULT NULL,
3090  `wh4_exemption2` VARCHAR(20) NULL DEFAULT NULL,
3091  `wh4_exemption3` VARCHAR(20) NULL DEFAULT NULL,
3092  `wh4_exemption3a` VARCHAR(20) NULL DEFAULT NULL,
3093  `wh4_exemption3b` VARCHAR(20) NULL DEFAULT NULL,
3094  `wh4_exemption4` VARCHAR(20) NULL DEFAULT NULL,
3095  `wh4_exemption5` VARCHAR(20) NULL DEFAULT NULL,
3096  `wh4_over65` VARCHAR(20) NULL DEFAULT NULL,
3097  `wh4_residence_county` VARCHAR(20) NULL DEFAULT NULL,
3098  `wh4_spouse_blind` VARCHAR(20) NULL DEFAULT NULL,
3099  `wh4_spouse_over65` VARCHAR(20) NULL DEFAULT NULL,
3100  `wh4_withholding` VARCHAR(20) NULL DEFAULT NULL,
3101  `wh4_county_withholding` VARCHAR(20) NULL DEFAULT NULL,
3102  `iaw4_date` DATE NULL DEFAULT NULL,
3103  `iaw4_exempt` VARCHAR(20) NULL DEFAULT NULL,
3104  `iaw4_exempt_year` VARCHAR(20) NULL DEFAULT NULL,
3105  `iaw4_field1` VARCHAR(20) NULL DEFAULT NULL,
3106  `iaw4_field2` VARCHAR(20) NULL DEFAULT NULL,
3107  `iaw4_field3` VARCHAR(20) NULL DEFAULT NULL,
3108  `iaw4_field4` VARCHAR(20) NULL DEFAULT NULL,
3109  `iaw4_field5` VARCHAR(20) NULL DEFAULT NULL,
3110  `iaw4_field6` VARCHAR(20) NULL DEFAULT NULL,
3111  `iaw4_field7` VARCHAR(20) NULL DEFAULT NULL,
3112  `k4_exemption1` VARCHAR(20) NULL DEFAULT NULL,
3113  `k4_exemption2` VARCHAR(20) NULL DEFAULT NULL,
3114  `k4_exemption3a` VARCHAR(20) NULL DEFAULT NULL,
3115  `k4_exemption3b` VARCHAR(20) NULL DEFAULT NULL,
3116  `k4_exemption4` VARCHAR(20) NULL DEFAULT NULL,
3117  `k4_exemption5` VARCHAR(20) NULL DEFAULT NULL,
3118  `k4_exemption6` VARCHAR(20) NULL DEFAULT NULL,
3119  `k4_exemption7` VARCHAR(20) NULL DEFAULT NULL,
3120  `k4_withholding` VARCHAR(20) NULL DEFAULT NULL,
3121  `l4_a` VARCHAR(20) NULL DEFAULT NULL,
3122  `l4_add_withhold` VARCHAR(20) NULL DEFAULT NULL,
3123  `l4_b` VARCHAR(20) NULL DEFAULT NULL,
3124  `l4_date` DATE NULL DEFAULT NULL,
3125  `l4_exemptions` VARCHAR(20) NULL DEFAULT NULL,
3126  `l4_exempt_a` VARCHAR(20) NULL DEFAULT NULL,
3127  `l4_exempt_b` VARCHAR(20) NULL DEFAULT NULL,
3128  `mainew4_date` DATE NULL DEFAULT NULL,
3129  `mainew4_line10` VARCHAR(20) NULL DEFAULT NULL,
3130  `mainew4_line4` VARCHAR(20) NULL DEFAULT NULL,
3131  `mainew4_line5` VARCHAR(20) NULL DEFAULT NULL,
3132  `mainew4_line6a` VARCHAR(20) NULL DEFAULT NULL,
3133  `mainew4_line6b` VARCHAR(20) NULL DEFAULT NULL,
3134  `mainew4_line6c` VARCHAR(20) NULL DEFAULT NULL,
3135  `mainew4_line6d` VARCHAR(20) NULL DEFAULT NULL,
3136  `mainew4_line7` VARCHAR(20) NULL DEFAULT NULL,
3137  `mainew4_line8` VARCHAR(20) NULL DEFAULT NULL,
3138  `mainew4_line9` VARCHAR(20) NULL DEFAULT NULL,
3139  `mainew4_linea` VARCHAR(20) NULL DEFAULT NULL,
3140  `mainew4_lineb` VARCHAR(20) NULL DEFAULT NULL,
3141  `mainew4_linec` VARCHAR(20) NULL DEFAULT NULL,
3142  `miw4_date` DATE NULL DEFAULT NULL,
3143  `miw4_exemption` VARCHAR(20) NULL DEFAULT NULL,
3144  `miw4_exemptionB` VARCHAR(20) NULL DEFAULT NULL,
3145  `miw4_exemptionC` VARCHAR(20) NULL DEFAULT NULL,
3146  `miw4_field6` VARCHAR(20) NULL DEFAULT NULL,
3147  `miw4_field7` VARCHAR(20) NULL DEFAULT NULL,
3148  `miw4_new_hire` VARCHAR(20) NULL DEFAULT NULL,
3149  `nh_hire_date` DATE NULL DEFAULT NULL,
3150  `exempt_explain` VARCHAR(255) NULL DEFAULT NULL,
3151  `exempt_zone` VARCHAR(50) NULL DEFAULT NULL,
3152  `msw4_field1_check` CHAR(1) NULL DEFAULT NULL,
3153  `msw4_field1` CHAR(10) NULL DEFAULT NULL,
3154  `msw4_field2a_check` CHAR(1) NULL DEFAULT NULL,
3155  `msw4_field2b_check` CHAR(1) NULL DEFAULT NULL,
3156  `msw4_field2a` CHAR(10) NULL DEFAULT NULL,
3157  `msw4_field2b` CHAR(10) NULL DEFAULT NULL,
3158  `msw4_field3_check` CHAR(1) NULL DEFAULT NULL,
3159  `msw4_field3` CHAR(10) NULL DEFAULT NULL,
3160  `msw4_field4_claimed` CHAR(5) NULL DEFAULT NULL,
3161  `msw4_field4` CHAR(10) NULL DEFAULT NULL,
3162  `msw4_field5_age` CHAR(10) NULL DEFAULT NULL,
3163  `msw4_field5_blind` CHAR(10) NULL DEFAULT NULL,
3164  `msw4_field5` CHAR(10) NULL DEFAULT NULL,
3165  `msw4_field6` CHAR(5) NULL DEFAULT NULL,
3166  `msw4_field7` CHAR(5) NULL DEFAULT NULL,
3167  `msw4_date` DATE NULL DEFAULT NULL,
3168  `mow4_date` DATE NULL DEFAULT NULL,
3169  `mow4_field1` VARCHAR(20) NULL DEFAULT NULL,
3170  `mow4_field2a` VARCHAR(20) NULL DEFAULT NULL,
3171  `mow4_field2b` VARCHAR(20) NULL DEFAULT NULL,
3172  `mow4_field3` VARCHAR(20) NULL DEFAULT NULL,
3173  `mow4_field4` VARCHAR(20) NULL DEFAULT NULL,
3174  `mow4_field5` VARCHAR(20) NULL DEFAULT NULL,
3175  `mow4_field6` VARCHAR(20) NULL DEFAULT NULL,
3176  `mow4_field7` VARCHAR(20) NULL DEFAULT NULL,
3177  `mow4_marital_status` VARCHAR(20) NULL DEFAULT NULL,
3178  `mw5_ws_line1a` VARCHAR(12) NULL DEFAULT NULL,
3179  `mw5_ws_line1b` VARCHAR(12) NULL DEFAULT NULL,
3180  `mw5_ws_line1c` VARCHAR(12) NULL DEFAULT NULL,
3181  `mw5_ws_line1d` VARCHAR(12) NULL DEFAULT NULL,
3182  `mw5_ws_line1e` VARCHAR(12) NULL DEFAULT NULL,
3183  `mw5_ws_line1f` VARCHAR(12) NULL DEFAULT NULL,
3184  `mw5_date` DATE NULL DEFAULT NULL,
3185  `mw5_line4b` VARCHAR(10) NULL DEFAULT NULL,
3186  `mw5_line4` VARCHAR(12) NULL DEFAULT NULL,
3187  `mw5_line3c` VARCHAR(10) NULL DEFAULT NULL,
3188  `mw5_line3d` VARCHAR(10) NULL DEFAULT NULL,
3189  `mw5_line1` CHAR(1) NULL DEFAULT NULL,
3190  `mw5_line2` VARCHAR(10) NULL DEFAULT NULL,
3191  `mw5_line3a` CHAR(1) NULL DEFAULT NULL,
3192  `mw5_line3b` CHAR(1) NULL DEFAULT NULL,
3193  `mw5_withhold_1` CHAR(1) NULL DEFAULT NULL,
3194  `mw5_withhold_2` CHAR(1) NULL DEFAULT NULL,
3195  `mw5_withhold_3` CHAR(1) NULL DEFAULT NULL,
3196  `nyit_copy_reason` VARCHAR(20) NULL DEFAULT NULL,
3197  `nyit_date` DATE NULL DEFAULT NULL,
3198  `nyit_estimate` VARCHAR(20) NULL DEFAULT NULL,
3199  `nyit_field1` VARCHAR(20) NULL DEFAULT NULL,
3200  `nyit_field10` VARCHAR(20) NULL DEFAULT NULL,
3201  `nyit_field11` VARCHAR(20) NULL DEFAULT NULL,
3202  `nyit_field12` VARCHAR(20) NULL DEFAULT NULL,
3203  `nyit_field13` VARCHAR(20) NULL DEFAULT NULL,
3204  `nyit_field14` VARCHAR(20) NULL DEFAULT NULL,
3205  `nyit_field15` VARCHAR(20) NULL DEFAULT NULL,
3206  `nyit_field16` VARCHAR(20) NULL DEFAULT NULL,
3207  `nyit_field17` VARCHAR(20) NULL DEFAULT NULL,
3208  `nyit_field18` VARCHAR(20) NULL DEFAULT NULL,
3209  `nyit_field19` VARCHAR(20) NULL DEFAULT NULL,
3210  `nyit_field2` VARCHAR(20) NULL DEFAULT NULL,
3211  `nyit_field20` VARCHAR(20) NULL DEFAULT NULL,
3212  `nyit_field21` VARCHAR(20) NULL DEFAULT NULL,
3213  `nyit_field22` VARCHAR(20) NULL DEFAULT NULL,
3214  `nyit_field23` VARCHAR(20) NULL DEFAULT NULL,
3215  `nyit_field24` VARCHAR(20) NULL DEFAULT NULL,
3216  `nyit_field25` VARCHAR(20) NULL DEFAULT NULL,
3217  `nyit_field26` VARCHAR(20) NULL DEFAULT NULL,
3218  `nyit_field27` VARCHAR(20) NULL DEFAULT NULL,
3219  `nyit_field28` VARCHAR(20) NULL DEFAULT NULL,
3220  `nyit_field29` VARCHAR(20) NULL DEFAULT NULL,
3221  `nyit_field3` VARCHAR(20) NULL DEFAULT NULL,
3222  `nyit_field30` VARCHAR(20) NULL DEFAULT NULL,
3223  `nyit_field31` VARCHAR(20) NULL DEFAULT NULL,
3224  `nyit_field4` VARCHAR(20) NULL DEFAULT NULL,
3225  `nyit_field5` VARCHAR(20) NULL DEFAULT NULL,
3226  `nyit_field6` VARCHAR(20) NULL DEFAULT NULL,
3227  `nyit_field7` VARCHAR(20) NULL DEFAULT NULL,
3228  `nyit_field8` VARCHAR(20) NULL DEFAULT NULL,
3229  `nyit_field9` VARCHAR(20) NULL DEFAULT NULL,
3230  `nyit_marital_status` VARCHAR(20) NULL DEFAULT NULL,
3231  `nyit_newyork_resident` VARCHAR(20) NULL DEFAULT NULL,
3232  `nyit_yonkers_resident` VARCHAR(20) NULL DEFAULT NULL,
3233  `nc4_marital_status` CHAR(2) NULL DEFAULT NULL,
3234  `nc4_exempt` VARCHAR(10) NULL DEFAULT NULL,
3235  `nc4_county` VARCHAR(5) NULL DEFAULT NULL,
3236  `nc4_allowances` CHAR(2) NULL DEFAULT NULL,
3237  `nc4_add_amount` VARCHAR(15) NULL DEFAULT NULL,
3238  `nc4_eff_date` DATE NULL DEFAULT NULL,
3239  `nc4_paw_a` CHAR(2) NULL DEFAULT NULL,
3240  `nc4_paw_b` CHAR(2) NULL DEFAULT NULL,
3241  `nc4_paw_c` CHAR(2) NULL DEFAULT NULL,
3242  `nc4_paw_d` CHAR(2) NULL DEFAULT NULL,
3243  `nc4_paw_e` VARCHAR(10) NULL DEFAULT NULL,
3244  `nc4_paw_f` VARCHAR(10) NULL DEFAULT NULL,
3245  `nc4_datcw_1` VARCHAR(10) NULL DEFAULT NULL,
3246  `nc4_datcw_2` VARCHAR(10) NULL DEFAULT NULL,
3247  `nc4_datcw_3` VARCHAR(10) NULL DEFAULT NULL,
3248  `nc4_datcw_4` VARCHAR(10) NULL DEFAULT NULL,
3249  `nc4_datcw_5` VARCHAR(10) NULL DEFAULT NULL,
3250  `nc4_datcw_6` VARCHAR(10) NULL DEFAULT NULL,
3251  `nc4_datcw_7` VARCHAR(10) NULL DEFAULT NULL,
3252  `nc4_datcw_8` VARCHAR(10) NULL DEFAULT NULL,
3253  `nc4_datcw_9` VARCHAR(10) NULL DEFAULT NULL,
3254  `nc4_datcw_10` VARCHAR(10) NULL DEFAULT NULL,
3255  `nc4_datcw_11` VARCHAR(10) NULL DEFAULT NULL,
3256  `nc4_datcw_12` VARCHAR(10) NULL DEFAULT NULL,
3257  `nc4_datcw_13` VARCHAR(10) NULL DEFAULT NULL,
3258  `nc4_datcw_14` VARCHAR(10) NULL DEFAULT NULL,
3259  `ot4_date` DATE NULL DEFAULT NULL,
3260  `ot4_exemption1` VARCHAR(20) NULL DEFAULT NULL,
3261  `ot4_exemption2` VARCHAR(20) NULL DEFAULT NULL,
3262  `ot4_exemption3` VARCHAR(20) NULL DEFAULT NULL,
3263  `ot4_exemption4` VARCHAR(20) NULL DEFAULT NULL,
3264  `ot4_school_district` VARCHAR(20) NULL DEFAULT NULL,
3265  `ot4_school_number` VARCHAR(20) NULL DEFAULT NULL,
3266  `ot4_withholding` VARCHAR(20) NULL DEFAULT NULL,
3267  `va4_date` DATE NULL DEFAULT NULL,
3268  `va4_btm_line1a` CHAR(1) NULL DEFAULT NULL,
3269  `va4_btm_line1b` CHAR(1) NULL DEFAULT NULL,
3270  `va4_btm_line1c` CHAR(1) NULL DEFAULT NULL,
3271  `va4_btm_line2` CHAR(1) NULL DEFAULT NULL,
3272  `va4_btm_line3` CHAR(1) NULL DEFAULT NULL,
3273  `va4_line1` CHAR(1) NULL DEFAULT NULL,
3274  `va4_line2` CHAR(1) NULL DEFAULT NULL,
3275  `va4_line3` CHAR(1) NULL DEFAULT NULL,
3276  `va4_line4` CHAR(1) NULL DEFAULT NULL,
3277  `va4_line5a` CHAR(1) NULL DEFAULT NULL,
3278  `va4_line5b` CHAR(1) NULL DEFAULT NULL,
3279  `va4_line6a` CHAR(1) NULL DEFAULT NULL,
3280  `va4_line6b` CHAR(1) NULL DEFAULT NULL,
3281  `va4_line7` CHAR(1) NULL DEFAULT NULL,
3282  `va4_line8` CHAR(1) NULL DEFAULT NULL,
3283  `wvit4_1` CHAR(2) NULL DEFAULT NULL,
3284  `wvit4_2` CHAR(1) NULL DEFAULT NULL,
3285  `wvit4_3` CHAR(2) NULL DEFAULT NULL,
3286  `wvit4_4` CHAR(2) NULL DEFAULT NULL,
3287  `wvit4_5` CHAR(1) NULL DEFAULT NULL,
3288  `wvit4_6` CHAR(10) NULL DEFAULT NULL,
3289  `wvit4_date` DATE NULL DEFAULT NULL,
3290  `wiwt4_date` DATE NULL DEFAULT NULL,
3291  `wiwt4_field1a` VARCHAR(20) NULL DEFAULT NULL,
3292  `wiwt4_field1b` VARCHAR(20) NULL DEFAULT NULL,
3293  `wiwt4_field1c` VARCHAR(20) NULL DEFAULT NULL,
3294  `wiwt4_field1d` VARCHAR(20) NULL DEFAULT NULL,
3295  `wiwt4_field2` VARCHAR(20) NULL DEFAULT NULL,
3296  `wiwt4_field3` VARCHAR(20) NULL DEFAULT NULL,
3297  `wiwt4_marital_status` VARCHAR(20) NULL DEFAULT NULL,
3298  `tip_agree` CHAR(1) NULL DEFAULT NULL,
3299  `conf_agree` CHAR(1) NULL DEFAULT NULL,
3300  `i9_list` CHAR(1) NULL DEFAULT NULL,
3301  `i9_certification_date` DATE NULL DEFAULT NULL,
3302  `i9_employer_sig` VARCHAR(50) NULL DEFAULT NULL,
3303  `i9_employer_name` VARCHAR(50) NULL DEFAULT NULL,
3304  `i9_employer_title` VARCHAR(50) NULL DEFAULT NULL,
3305  `i9_employer_business` VARCHAR(50) NULL DEFAULT NULL,
3306  `i9_employer_address` VARCHAR(255) NULL DEFAULT NULL,
3307  `i9_employer_date` DATE NULL DEFAULT NULL,
3308  `i9_section3_name` VARCHAR(50) NULL DEFAULT NULL,
3309  `i9_section3_rehire_date` DATE NULL DEFAULT NULL,
3310  `i9_section3_title` VARCHAR(50) NULL DEFAULT NULL,
3311  `i9_section3_number` VARCHAR(20) NULL DEFAULT NULL,
3312  `i9_section3_date` DATE NULL DEFAULT NULL,
3313  `i9_section3_employer_sig` VARCHAR(50) NULL DEFAULT NULL,
3314  `i9_section3_employer_date` DATE NULL DEFAULT NULL,
3315  `i9_documenta_title` VARCHAR(50) NULL DEFAULT NULL,
3316  `i9_documenta_authority` VARCHAR(50) NULL DEFAULT NULL,
3317  `i9_documenta_number1` VARCHAR(20) NULL DEFAULT NULL,
3318  `i9_documenta_number2` VARCHAR(20) NULL DEFAULT NULL,
3319  `i9_documenta_date1` DATE NULL DEFAULT NULL,
3320  `i9_documenta_date2` DATE NULL DEFAULT NULL,
3321  `i9_documentb_title` VARCHAR(50) NULL DEFAULT NULL,
3322  `i9_documentb_authority` VARCHAR(50) NULL DEFAULT NULL,
3323  `i9_documentb_number` VARCHAR(20) NULL DEFAULT NULL,
3324  `i9_documentb_date` DATE NULL DEFAULT NULL,
3325  `i9_documentc_title` VARCHAR(50) NULL DEFAULT NULL,
3326  `i9_documentc_authority` VARCHAR(50) NULL DEFAULT NULL,
3327  `i9_documentc_number` VARCHAR(20) NULL DEFAULT NULL,
3328  `i9_documentc_date` DATE NULL DEFAULT NULL,
3329  `i9_alien_number_type` VARCHAR(10) NULL DEFAULT NULL,
3330  `i9_i94_number` VARCHAR(30) NULL DEFAULT NULL,
3331  `aza4_amt_wpcheck` VARCHAR(15) NULL DEFAULT NULL,
3332  `i9_documentb_dltype` CHAR(1) NULL DEFAULT NULL,
3333  `i9_documentb_dlstate` CHAR(2) NULL DEFAULT NULL,
3334  PRIMARY KEY (`hl_info_id`),
3335  INDEX `candidate_id` (`candidate_id` ASC),
3336  INDEX `hl_info_ibfk_2` (`application_id` ASC),
3337  CONSTRAINT `hl_info_ibfk_1`
3338    FOREIGN KEY (`candidate_id`)
3339    REFERENCES `candidate_orig` (`candidate_id`)
3340    ON DELETE CASCADE
3341    ON UPDATE CASCADE,
3342  CONSTRAINT `hl_info_ibfk_2`
3343    FOREIGN KEY (`application_id`)
3344    REFERENCES `application` (`application_id`)
3345    ON DELETE CASCADE
3346    ON UPDATE CASCADE)
3347ENGINE = InnoDB
3348AUTO_INCREMENT = 681
3349DEFAULT CHARACTER SET = latin1;
3350
3351
3352-- -----------------------------------------------------
3353-- Table `hl_repository`
3354-- -----------------------------------------------------
3355CREATE TABLE IF NOT EXISTS `hl_repository` (
3356  `hl_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
3357  `update_key` INT(20) NOT NULL DEFAULT '0',
3358  `is_candidate_update` CHAR(1) NOT NULL,
3359  `application_id` INT(20) NOT NULL DEFAULT '0',
3360  `page_version_id` INT(10) NOT NULL DEFAULT '0',
3361  `tdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3362  `ip_address` VARCHAR(64) NULL DEFAULT NULL,
3363  `session_info` VARCHAR(255) NULL DEFAULT NULL,
3364  `rdata` TEXT NULL DEFAULT NULL,
3365  PRIMARY KEY (`hl_repository_id`),
3366  INDEX `application_id` (`application_id` ASC),
3367  INDEX `page_version_id` (`page_version_id` ASC),
3368  CONSTRAINT `hl_repository_ibfk_1`
3369    FOREIGN KEY (`application_id`)
3370    REFERENCES `application` (`application_id`)
3371    ON DELETE CASCADE
3372    ON UPDATE CASCADE,
3373  CONSTRAINT `hl_repository_ibfk_2`
3374    FOREIGN KEY (`page_version_id`)
3375    REFERENCES `page_version` (`page_version_id`)
3376    ON DELETE CASCADE
3377    ON UPDATE CASCADE)
3378ENGINE = InnoDB
3379AUTO_INCREMENT = 327
3380DEFAULT CHARACTER SET = latin1;
3381
3382
3383-- -----------------------------------------------------
3384-- Table `hm_activity`
3385-- -----------------------------------------------------
3386CREATE TABLE IF NOT EXISTS `hm_activity` (
3387  `users_id` INT(20) NOT NULL DEFAULT '0',
3388  `application_id` INT(20) NOT NULL DEFAULT '0',
3389  `status` VARCHAR(50) NOT NULL DEFAULT '',
3390  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3391  `acknowledged` CHAR(1) NOT NULL DEFAULT '',
3392  INDEX `users_id` (`users_id` ASC),
3393  INDEX `application_id` (`application_id` ASC),
3394  CONSTRAINT `hm_activity_ibfk_2`
3395    FOREIGN KEY (`users_id`)
3396    REFERENCES `users` (`users_id`)
3397    ON DELETE CASCADE
3398    ON UPDATE CASCADE,
3399  CONSTRAINT `hm_activity_ibfk_3`
3400    FOREIGN KEY (`application_id`)
3401    REFERENCES `application` (`application_id`)
3402    ON DELETE CASCADE
3403    ON UPDATE CASCADE)
3404ENGINE = InnoDB
3405DEFAULT CHARACTER SET = latin1;
3406
3407
3408-- -----------------------------------------------------
3409-- Table `interview_group`
3410-- -----------------------------------------------------
3411CREATE TABLE IF NOT EXISTS `interview_group` (
3412  `interview_group_id` INT(20) NOT NULL AUTO_INCREMENT,
3413  `name` CHAR(50) NULL DEFAULT NULL,
3414  PRIMARY KEY (`interview_group_id`))
3415ENGINE = InnoDB
3416AUTO_INCREMENT = 43
3417DEFAULT CHARACTER SET = latin1;
3418
3419
3420-- -----------------------------------------------------
3421-- Table `int_group_detail`
3422-- -----------------------------------------------------
3423CREATE TABLE IF NOT EXISTS `int_group_detail` (
3424  `interview_group_id` INT(20) NOT NULL DEFAULT '0',
3425  `users_id` INT(20) NOT NULL DEFAULT '0',
3426  PRIMARY KEY (`interview_group_id`, `users_id`),
3427  INDEX `interview_group_asso_ndx` (`interview_group_id` ASC),
3428  INDEX `users_group_asso_ndx` (`users_id` ASC),
3429  CONSTRAINT `0_2549`
3430    FOREIGN KEY (`interview_group_id`)
3431    REFERENCES `interview_group` (`interview_group_id`)
3432    ON DELETE CASCADE
3433    ON UPDATE CASCADE,
3434  CONSTRAINT `0_2550`
3435    FOREIGN KEY (`users_id`)
3436    REFERENCES `users` (`users_id`)
3437    ON DELETE CASCADE
3438    ON UPDATE CASCADE)
3439ENGINE = InnoDB
3440DEFAULT CHARACTER SET = latin1;
3441
3442
3443-- -----------------------------------------------------
3444-- Table `interface`
3445-- -----------------------------------------------------
3446CREATE TABLE IF NOT EXISTS `interface` (
3447  `client_name` VARCHAR(30) NULL DEFAULT NULL,
3448  `interface_name` VARCHAR(60) NULL DEFAULT NULL,
3449  `config_file` VARCHAR(75) NULL DEFAULT NULL,
3450  `interface_id` VARCHAR(6) NULL DEFAULT NULL)
3451ENGINE = InnoDB
3452DEFAULT CHARACTER SET = latin1;
3453
3454
3455-- -----------------------------------------------------
3456-- Table `interface_transform`
3457-- -----------------------------------------------------
3458CREATE TABLE IF NOT EXISTS `interface_transform` (
3459  `interface_id` VARCHAR(6) NULL DEFAULT NULL,
3460  `transform_id` VARCHAR(6) NULL DEFAULT NULL,
3461  `client_name` VARCHAR(45) NULL DEFAULT NULL,
3462  `interface_name` VARCHAR(60) NULL DEFAULT NULL,
3463  `transform_name` VARCHAR(150) NULL DEFAULT NULL,
3464  `file_name` VARCHAR(150) NULL DEFAULT NULL)
3465ENGINE = InnoDB
3466DEFAULT CHARACTER SET = latin1;
3467
3468
3469-- -----------------------------------------------------
3470-- Table `interview`
3471-- -----------------------------------------------------
3472CREATE TABLE IF NOT EXISTS `interview` (
3473  `calendar_id` INT(20) NOT NULL DEFAULT '0',
3474  `application_id` INT(20) NOT NULL DEFAULT '0',
3475  `arrive_time` DATETIME NULL DEFAULT NULL,
3476  `processed` DATETIME NULL DEFAULT NULL,
3477  PRIMARY KEY (`calendar_id`, `application_id`),
3478  INDEX `cal_intv_asso_ndx` (`calendar_id` ASC),
3479  INDEX `app_intv_asso_ndx` (`application_id` ASC),
3480  CONSTRAINT `0_3366`
3481    FOREIGN KEY (`calendar_id`)
3482    REFERENCES `calendar` (`calendar_id`)
3483    ON DELETE CASCADE
3484    ON UPDATE CASCADE,
3485  CONSTRAINT `0_3367`
3486    FOREIGN KEY (`application_id`)
3487    REFERENCES `application` (`application_id`)
3488    ON DELETE CASCADE
3489    ON UPDATE CASCADE)
3490ENGINE = InnoDB
3491DEFAULT CHARACTER SET = latin1;
3492
3493
3494-- -----------------------------------------------------
3495-- Table `interview_invite`
3496-- -----------------------------------------------------
3497CREATE TABLE IF NOT EXISTS `interview_invite` (
3498  `interview_invite_id` INT(20) NOT NULL AUTO_INCREMENT,
3499  `calendar_id` INT(20) NULL DEFAULT NULL,
3500  `candidate_id` INT(20) NOT NULL DEFAULT '0',
3501  `interview_group_id` INT(20) NOT NULL DEFAULT '0',
3502  `users_id` INT(20) NOT NULL,
3503  `status` VARCHAR(7) NULL DEFAULT NULL,
3504  `type` VARCHAR(7) NULL DEFAULT NULL,
3505  `dreason` VARCHAR(7) NULL DEFAULT NULL,
3506  `create_date` DATETIME NULL DEFAULT NULL,
3507  `response_date` DATETIME NULL DEFAULT NULL,
3508  `update_date` DATETIME NULL DEFAULT NULL,
3509  `application_id` INT(20) NULL DEFAULT NULL,
3510  PRIMARY KEY (`interview_invite_id`),
3511  INDEX `can_iti_asso_ndx` (`candidate_id` ASC),
3512  INDEX `ivg_iti_asso_ndx` (`interview_group_id` ASC),
3513  INDEX `calendar_id` (`calendar_id` ASC),
3514  INDEX `users_id` (`users_id` ASC),
3515  INDEX `status` (`status` ASC),
3516  INDEX `type` (`type` ASC),
3517  INDEX `application_id` (`application_id` ASC),
3518  CONSTRAINT `0_33669`
3519    FOREIGN KEY (`candidate_id`)
3520    REFERENCES `candidate_orig` (`candidate_id`)
3521    ON DELETE CASCADE,
3522  CONSTRAINT `0_33679`
3523    FOREIGN KEY (`interview_group_id`)
3524    REFERENCES `interview_group` (`interview_group_id`)
3525    ON DELETE CASCADE,
3526  CONSTRAINT `0_33680`
3527    FOREIGN KEY (`calendar_id`)
3528    REFERENCES `calendar` (`calendar_id`)
3529    ON DELETE CASCADE,
3530  CONSTRAINT `0_33681`
3531    FOREIGN KEY (`users_id`)
3532    REFERENCES `users` (`users_id`)
3533    ON DELETE CASCADE)
3534ENGINE = InnoDB
3535AUTO_INCREMENT = 517
3536DEFAULT CHARACTER SET = latin1;
3537
3538
3539-- -----------------------------------------------------
3540-- Table `interview_times`
3541-- -----------------------------------------------------
3542CREATE TABLE IF NOT EXISTS `interview_times` (
3543  `interview_group_id` INT(20) NOT NULL DEFAULT '0',
3544  `requisition_id` INT(20) NOT NULL DEFAULT '0',
3545  PRIMARY KEY (`interview_group_id`, `requisition_id`),
3546  INDEX `interview_group_asso_ndx` (`interview_group_id` ASC),
3547  INDEX `req_group_asso_ndx` (`requisition_id` ASC),
3548  CONSTRAINT `0_2554`
3549    FOREIGN KEY (`interview_group_id`)
3550    REFERENCES `interview_group` (`interview_group_id`)
3551    ON DELETE CASCADE
3552    ON UPDATE CASCADE,
3553  CONSTRAINT `0_2555`
3554    FOREIGN KEY (`requisition_id`)
3555    REFERENCES `requisition` (`requisition_id`)
3556    ON DELETE CASCADE
3557    ON UPDATE CASCADE)
3558ENGINE = InnoDB
3559DEFAULT CHARACTER SET = latin1;
3560
3561
3562-- -----------------------------------------------------
3563-- Table `job_spy`
3564-- -----------------------------------------------------
3565CREATE TABLE IF NOT EXISTS `job_spy` (
3566  `job_spy_id` INT(11) NOT NULL AUTO_INCREMENT,
3567  `candidate_id` INT(11) NOT NULL DEFAULT '0',
3568  `name` VARCHAR(80) NULL DEFAULT NULL,
3569  `search` BLOB NULL DEFAULT NULL,
3570  `last_searched` DATE NULL DEFAULT NULL,
3571  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
3572  `active` CHAR(1) NULL DEFAULT 't',
3573  PRIMARY KEY (`job_spy_id`),
3574  INDEX `js_candidate_ndx` (`candidate_id` ASC),
3575  CONSTRAINT `0_3349`
3576    FOREIGN KEY (`candidate_id`)
3577    REFERENCES `candidate_orig` (`candidate_id`)
3578    ON DELETE CASCADE
3579    ON UPDATE CASCADE)
3580ENGINE = InnoDB
3581AUTO_INCREMENT = 276
3582DEFAULT CHARACTER SET = latin1;
3583
3584
3585-- -----------------------------------------------------
3586-- Table `license`
3587-- -----------------------------------------------------
3588CREATE TABLE IF NOT EXISTS `license` (
3589  `candidate_id` INT(20) NULL DEFAULT NULL,
3590  `license_id` INT(20) NOT NULL AUTO_INCREMENT,
3591  `name` VARCHAR(50) NULL DEFAULT NULL,
3592  `lic_number` VARCHAR(50) NULL DEFAULT NULL,
3593  `reason` VARCHAR(50) NULL DEFAULT NULL,
3594  `location` VARCHAR(50) NULL DEFAULT NULL,
3595  `issue_date` DATE NULL DEFAULT NULL,
3596  `expire_date` DATE NULL DEFAULT NULL,
3597  `reviewed` CHAR(1) NULL DEFAULT NULL,
3598  `country` VARCHAR(50) NULL DEFAULT NULL,
3599  PRIMARY KEY (`license_id`),
3600  INDEX `candidate_id` (`candidate_id` ASC),
3601  CONSTRAINT `license_ibfk_1`
3602    FOREIGN KEY (`candidate_id`)
3603    REFERENCES `candidate_orig` (`candidate_id`)
3604    ON DELETE CASCADE
3605    ON UPDATE CASCADE)
3606ENGINE = InnoDB
3607DEFAULT CHARACTER SET = latin1;
3608
3609
3610-- -----------------------------------------------------
3611-- Table `license_repository`
3612-- -----------------------------------------------------
3613CREATE TABLE IF NOT EXISTS `license_repository` (
3614  `license_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
3615  `license_id` INT(20) NOT NULL,
3616  `uname` VARCHAR(75) NOT NULL,
3617  `change_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3618  `data` TEXT NULL DEFAULT NULL,
3619  PRIMARY KEY (`license_repository_id`),
3620  INDEX `er_lcid_fk` (`license_id` ASC),
3621  CONSTRAINT `er_lcid_fk`
3622    FOREIGN KEY (`license_id`)
3623    REFERENCES `license` (`license_id`)
3624    ON DELETE CASCADE
3625    ON UPDATE CASCADE)
3626ENGINE = InnoDB
3627DEFAULT CHARACTER SET = latin1;
3628
3629
3630-- -----------------------------------------------------
3631-- Table `list`
3632-- -----------------------------------------------------
3633CREATE TABLE IF NOT EXISTS `list` (
3634  `list_id` INT(20) NOT NULL AUTO_INCREMENT,
3635  `list` VARCHAR(50) NULL DEFAULT NULL,
3636  `label` VARCHAR(150) NULL DEFAULT NULL,
3637  `value` VARCHAR(50) NULL DEFAULT NULL,
3638  `action` VARCHAR(100) NULL DEFAULT NULL,
3639  `sort_order` INT(5) NULL DEFAULT NULL,
3640  `locale` VARCHAR(50) NULL DEFAULT NULL,
3641  `active` CHAR(1) NULL DEFAULT 't',
3642  PRIMARY KEY (`list_id`),
3643  INDEX `list` (`list` ASC),
3644  INDEX `label` (`label` ASC),
3645  INDEX `value` (`value` ASC),
3646  INDEX `sort_order` (`sort_order` ASC),
3647  INDEX `active` (`active` ASC))
3648ENGINE = InnoDB
3649AUTO_INCREMENT = 8656
3650DEFAULT CHARACTER SET = latin1;
3651
3652
3653-- -----------------------------------------------------
3654-- Table `manage_list`
3655-- -----------------------------------------------------
3656CREATE TABLE IF NOT EXISTS `manage_list` (
3657  `list` VARCHAR(50) NOT NULL DEFAULT '',
3658  `manage_name` VARCHAR(50) NOT NULL DEFAULT '',
3659  PRIMARY KEY (`list`))
3660ENGINE = InnoDB
3661DEFAULT CHARACTER SET = latin1;
3662
3663
3664-- -----------------------------------------------------
3665-- Table `message_branding`
3666-- -----------------------------------------------------
3667CREATE TABLE IF NOT EXISTS `message_branding` (
3668  `id` INT(20) NOT NULL AUTO_INCREMENT,
3669  `format` ENUM('text', 'html') NOT NULL DEFAULT 'html',
3670  `name` VARCHAR(50) NULL DEFAULT NULL,
3671  `body` TEXT NULL DEFAULT NULL,
3672  `active` CHAR(1) NULL DEFAULT 't',
3673  PRIMARY KEY (`id`))
3674ENGINE = InnoDB
3675AUTO_INCREMENT = 4
3676DEFAULT CHARACTER SET = latin1;
3677
3678
3679-- -----------------------------------------------------
3680-- Table `message_template`
3681-- -----------------------------------------------------
3682CREATE TABLE IF NOT EXISTS `message_template` (
3683  `id` INT(20) NOT NULL AUTO_INCREMENT,
3684  `hierarchy_id` INT(11) NOT NULL DEFAULT '1',
3685  `user_id` INT(20) NOT NULL DEFAULT '1',
3686  `type` ENUM('system', 'global', 'applicant', 'candidate', 'rejection', 'offer', 'hired') NOT NULL DEFAULT 'global',
3687  `format` ENUM('text', 'html') NOT NULL DEFAULT 'html',
3688  `name` VARCHAR(50) NULL DEFAULT NULL,
3689  `subject` VARCHAR(50) NULL DEFAULT NULL,
3690  `body` TEXT NULL DEFAULT NULL,
3691  `private` CHAR(1) NULL DEFAULT 'f',
3692  `active` CHAR(1) NULL DEFAULT 't',
3693  PRIMARY KEY (`id`),
3694  UNIQUE INDEX `branch_name` (`hierarchy_id` ASC, `name` ASC),
3695  INDEX `msg_users_asso_ndx` (`user_id` ASC),
3696  CONSTRAINT `hierarchy_id_constraint`
3697    FOREIGN KEY (`hierarchy_id`)
3698    REFERENCES `hierarchy` (`hierarchy_id`)
3699    ON DELETE CASCADE
3700    ON UPDATE CASCADE,
3701  CONSTRAINT `user_id_constraint`
3702    FOREIGN KEY (`user_id`)
3703    REFERENCES `users` (`users_id`)
3704    ON DELETE CASCADE
3705    ON UPDATE CASCADE)
3706ENGINE = InnoDB
3707DEFAULT CHARACTER SET = latin1;
3708
3709
3710-- -----------------------------------------------------
3711-- Table `message_log`
3712-- -----------------------------------------------------
3713CREATE TABLE IF NOT EXISTS `message_log` (
3714  `id` INT(20) NOT NULL AUTO_INCREMENT,
3715  `message_template_id` INT(20) NOT NULL,
3716  `candidate_id` INT(20) NOT NULL,
3717  `requisition_id` INT(20) NULL DEFAULT '-1',
3718  `user_id` INT(20) NOT NULL,
3719  `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
3720  `type` ENUM('system', 'global', 'applicant', 'candidate', 'rejection', 'offer', 'hired') NOT NULL DEFAULT 'global',
3721  `format` ENUM('text', 'html') NOT NULL DEFAULT 'html',
3722  `origin` VARCHAR(16) NULL DEFAULT NULL,
3723  `body` TEXT NOT NULL,
3724  `notes` TEXT NULL DEFAULT NULL,
3725  `read_key` VARCHAR(128) NULL DEFAULT NULL,
3726  `read` CHAR(1) NULL DEFAULT 'f',
3727  PRIMARY KEY (`id`),
3728  INDEX `message_template_id` (`message_template_id` ASC),
3729  INDEX `candidate_id` (`candidate_id` ASC),
3730  INDEX `requisition_id` (`requisition_id` ASC),
3731  INDEX `user_id` (`user_id` ASC),
3732  INDEX `read_key` (`read_key` ASC),
3733  CONSTRAINT `message_log_ibfk_1`
3734    FOREIGN KEY (`message_template_id`)
3735    REFERENCES `message_template` (`id`)
3736    ON DELETE CASCADE
3737    ON UPDATE CASCADE,
3738  CONSTRAINT `message_log_ibfk_2`
3739    FOREIGN KEY (`candidate_id`)
3740    REFERENCES `candidate_orig` (`candidate_id`)
3741    ON DELETE CASCADE
3742    ON UPDATE CASCADE,
3743  CONSTRAINT `message_log_ibfk_3`
3744    FOREIGN KEY (`user_id`)
3745    REFERENCES `users` (`users_id`)
3746    ON DELETE CASCADE
3747    ON UPDATE CASCADE)
3748ENGINE = InnoDB
3749DEFAULT CHARACTER SET = latin1;
3750
3751
3752-- -----------------------------------------------------
3753-- Table `meta`
3754-- -----------------------------------------------------
3755CREATE TABLE IF NOT EXISTS `meta` (
3756  `meta_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key.',
3757  `hrl_type` VARCHAR(50) NULL DEFAULT NULL COMMENT 'HRL\'s data type.',
3758  `bo` VARCHAR(50) NOT NULL COMMENT 'Conceptual name (i.e. requisition, candidate, offer ...)',
3759  `boAttrib` VARCHAR(50) NOT NULL COMMENT 'Peristent store data element.',
3760  `display_name` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Users nomenclature of persistent store data element.',
3761  PRIMARY KEY (`meta_id`))
3762ENGINE = InnoDB
3763AUTO_INCREMENT = 3544
3764DEFAULT CHARACTER SET = latin1;
3765
3766
3767-- -----------------------------------------------------
3768-- Table `meta_i18n`
3769-- -----------------------------------------------------
3770CREATE TABLE IF NOT EXISTS `meta_i18n` (
3771  `meta_i18n_id` INT(11) NOT NULL AUTO_INCREMENT,
3772  `meta_id` INT(11) NOT NULL,
3773  `locale` VARCHAR(10) NULL DEFAULT NULL,
3774  `props` TEXT NULL DEFAULT NULL,
3775  PRIMARY KEY (`meta_i18n_id`),
3776  INDEX `FK_meta_i18n_meta_id` (`meta_id` ASC))
3777ENGINE = InnoDB
3778AUTO_INCREMENT = 2
3779DEFAULT CHARACTER SET = latin1;
3780
3781
3782-- -----------------------------------------------------
3783-- Table `mma`
3784-- -----------------------------------------------------
3785CREATE TABLE IF NOT EXISTS `mma` (
3786  `mma_id` INT(11) NOT NULL AUTO_INCREMENT,
3787  `mmrpo` VARCHAR(50) NULL DEFAULT NULL,
3788  `weekending` VARCHAR(50) NULL DEFAULT NULL,
3789  `clicode` CHAR(3) NULL DEFAULT NULL,
3790  `revamt` INT(11) NULL DEFAULT NULL,
3791  `cancelled` TINYINT(1) NULL DEFAULT NULL,
3792  `dnr` TINYINT(1) NULL DEFAULT NULL,
3793  `rundate1` VARCHAR(50) NULL DEFAULT NULL,
3794  `rundate7` VARCHAR(50) NULL DEFAULT NULL,
3795  `papercode` VARCHAR(5) NULL DEFAULT NULL,
3796  `name` VARCHAR(50) NULL DEFAULT NULL,
3797  `city` VARCHAR(50) NULL DEFAULT NULL,
3798  `stcode` CHAR(2) NULL DEFAULT NULL,
3799  `classification` VARCHAR(100) NULL DEFAULT NULL,
3800  `active` TINYINT(1) NOT NULL DEFAULT '1',
3801  PRIMARY KEY (`mma_id`),
3802  INDEX `mmrpo` (`mmrpo` ASC),
3803  INDEX `revamt` (`revamt` ASC))
3804ENGINE = InnoDB
3805DEFAULT CHARACTER SET = latin1;
3806
3807
3808-- -----------------------------------------------------
3809-- Table `msa`
3810-- -----------------------------------------------------
3811CREATE TABLE IF NOT EXISTS `msa` (
3812  `msa_id` INT(10) NOT NULL AUTO_INCREMENT,
3813  `ZIPCode` VARCHAR(10) NOT NULL DEFAULT '0',
3814  `ZIPCodeType` CHAR(2) NULL DEFAULT NULL,
3815  `City` VARCHAR(75) NULL DEFAULT NULL,
3816  `CityType` CHAR(1) NULL DEFAULT NULL,
3817  `County` VARCHAR(75) NULL DEFAULT NULL,
3818  `CountyFIPS` VARCHAR(10) NULL DEFAULT NULL,
3819  `State` VARCHAR(50) NULL DEFAULT NULL,
3820  `StateCode` CHAR(2) NULL DEFAULT NULL,
3821  `StateFIPS` VARCHAR(5) NULL DEFAULT NULL,
3822  `MSA` VARCHAR(10) NULL DEFAULT NULL,
3823  `AreaCode` VARCHAR(10) NULL DEFAULT NULL,
3824  `TimeZone` VARCHAR(15) NULL DEFAULT NULL,
3825  `GMTOffset` VARCHAR(5) NULL DEFAULT NULL,
3826  `DST` VARCHAR(5) NULL DEFAULT NULL,
3827  `Latitude` VARCHAR(20) NULL DEFAULT NULL,
3828  `Longitude` VARCHAR(20) NULL DEFAULT NULL,
3829  PRIMARY KEY (`msa_id`))
3830ENGINE = InnoDB
3831AUTO_INCREMENT = 70488
3832DEFAULT CHARACTER SET = latin1;
3833
3834
3835-- -----------------------------------------------------
3836-- Table `notes`
3837-- -----------------------------------------------------
3838CREATE TABLE IF NOT EXISTS `notes` (
3839  `notes_id` INT(11) NOT NULL AUTO_INCREMENT,
3840  `candidate_id` INT(11) NOT NULL DEFAULT '0',
3841  `users_id` INT(11) NOT NULL DEFAULT '0',
3842  `comment` VARCHAR(1024) NULL DEFAULT NULL,
3843  `private` CHAR(1) NULL DEFAULT NULL,
3844  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3845  PRIMARY KEY (`notes_id`),
3846  INDEX `nts_candidate_ndx` (`candidate_id` ASC),
3847  INDEX `nts_users_ndx` (`users_id` ASC),
3848  CONSTRAINT `FK_notes_candidate`
3849    FOREIGN KEY (`candidate_id`)
3850    REFERENCES `candidate` (`candidate_id`)
3851    ON DELETE CASCADE
3852    ON UPDATE CASCADE,
3853  CONSTRAINT `FK_notes_users`
3854    FOREIGN KEY (`users_id`)
3855    REFERENCES `users` (`users_id`)
3856    ON DELETE CASCADE
3857    ON UPDATE CASCADE)
3858ENGINE = InnoDB
3859AUTO_INCREMENT = 11
3860DEFAULT CHARACTER SET = latin1;
3861
3862
3863-- -----------------------------------------------------
3864-- Table `ob_group`
3865-- -----------------------------------------------------
3866CREATE TABLE IF NOT EXISTS `ob_group` (
3867  `ob_group_id` INT(20) NOT NULL AUTO_INCREMENT,
3868  `name` VARCHAR(50) NULL DEFAULT NULL,
3869  `sort_order` INT(5) NULL DEFAULT '0',
3870  `active` CHAR(1) NULL DEFAULT 't',
3871  PRIMARY KEY (`ob_group_id`),
3872  UNIQUE INDEX `name` (`name` ASC),
3873  INDEX `sort_order` (`sort_order` ASC),
3874  INDEX `active` (`active` ASC))
3875ENGINE = InnoDB
3876AUTO_INCREMENT = 12
3877DEFAULT CHARACTER SET = latin1;
3878
3879
3880-- -----------------------------------------------------
3881-- Table `ob_item`
3882-- -----------------------------------------------------
3883CREATE TABLE IF NOT EXISTS `ob_item` (
3884  `ob_item_id` INT(10) NOT NULL AUTO_INCREMENT,
3885  `ob_group_id` INT(10) NOT NULL DEFAULT '0',
3886  `name` CHAR(50) NOT NULL,
3887  `sort_order` INT(5) NULL DEFAULT '0',
3888  `active` CHAR(1) NULL DEFAULT 't',
3889  `executor` INT(10) NULL DEFAULT NULL,
3890  `send_message` VARCHAR(1024) NULL DEFAULT NULL,
3891  `task_notify` VARCHAR(10) NULL DEFAULT NULL,
3892  `tn_who` VARCHAR(75) NULL DEFAULT NULL,
3893  `tn_when` VARCHAR(255) NULL DEFAULT NULL,
3894  `notify_type` VARCHAR(10) NULL DEFAULT NULL,
3895  `send_option` VARCHAR(10) NULL DEFAULT 'message',
3896  `send_form_list` VARCHAR(50) NULL DEFAULT NULL,
3897  `approval_need` CHAR(1) NULL DEFAULT NULL,
3898  `approval_approver` VARCHAR(50) NULL DEFAULT NULL,
3899  `detail_method` VARCHAR(10) NULL DEFAULT NULL,
3900  `detail_list` VARCHAR(30) NULL DEFAULT NULL,
3901  PRIMARY KEY (`ob_item_id`),
3902  UNIQUE INDEX `name` (`name` ASC),
3903  INDEX `ob_group_id` (`ob_group_id` ASC),
3904  INDEX `sort_order` (`sort_order` ASC),
3905  CONSTRAINT `ob_group_id_ibfk_1`
3906    FOREIGN KEY (`ob_group_id`)
3907    REFERENCES `ob_group` (`ob_group_id`)
3908    ON DELETE CASCADE
3909    ON UPDATE CASCADE)
3910ENGINE = InnoDB
3911AUTO_INCREMENT = 28
3912DEFAULT CHARACTER SET = latin1;
3913
3914
3915-- -----------------------------------------------------
3916-- Table `ob_item_detail`
3917-- -----------------------------------------------------
3918CREATE TABLE IF NOT EXISTS `ob_item_detail` (
3919  `ob_item_detail_id` INT(10) NOT NULL AUTO_INCREMENT,
3920  `ob_item_id` INT(10) NOT NULL,
3921  `detail` VARCHAR(255) NULL DEFAULT NULL,
3922  `due_date` DATE NULL DEFAULT NULL,
3923  `application_id` INT(10) NOT NULL,
3924  `$ob_status` VARCHAR(20) NULL DEFAULT NULL,
3925  `workflow_token` INT(10) NULL DEFAULT NULL,
3926  `is_sent` CHAR(1) NULL DEFAULT NULL,
3927  PRIMARY KEY (`ob_item_detail_id`),
3928  INDEX `ob_item_id` (`ob_item_id` ASC),
3929  INDEX `application_id` (`application_id` ASC),
3930  CONSTRAINT `FK_ob_item_detail`
3931    FOREIGN KEY (`application_id`)
3932    REFERENCES `application` (`application_id`)
3933    ON DELETE CASCADE
3934    ON UPDATE CASCADE,
3935  CONSTRAINT `FK_ob_item_detail2`
3936    FOREIGN KEY (`ob_item_id`)
3937    REFERENCES `ob_item` (`ob_item_id`)
3938    ON DELETE CASCADE
3939    ON UPDATE CASCADE,
3940  CONSTRAINT `ob_item_detail_ibfk_1`
3941    FOREIGN KEY (`ob_item_id`)
3942    REFERENCES `ob_item` (`ob_item_id`),
3943  CONSTRAINT `ob_item_detail_ibfk_2`
3944    FOREIGN KEY (`application_id`)
3945    REFERENCES `application` (`application_id`))
3946ENGINE = InnoDB
3947DEFAULT CHARACTER SET = latin1
3948ROW_FORMAT = FIXED;
3949
3950
3951-- -----------------------------------------------------
3952-- Table `ob_approver`
3953-- -----------------------------------------------------
3954CREATE TABLE IF NOT EXISTS `ob_approver` (
3955  `ob_approver_id` INT(10) NOT NULL AUTO_INCREMENT,
3956  `ob_item_detail_id` INT(10) NOT NULL,
3957  `users_id` INT(10) NOT NULL,
3958  `comment` VARCHAR(255) NULL DEFAULT NULL,
3959  `time_in` DATETIME NULL DEFAULT NULL,
3960  `time_out` DATETIME NULL DEFAULT NULL,
3961  `progression` INT(4) NULL DEFAULT NULL,
3962  PRIMARY KEY (`ob_approver_id`),
3963  INDEX `ob_item_detail_id_asso_ndx` (`ob_item_detail_id` ASC),
3964  INDEX `users_id_asso_ndx` (`users_id` ASC),
3965  INDEX `progression` (`progression` ASC),
3966  CONSTRAINT `ob_approver_ibfk_1`
3967    FOREIGN KEY (`users_id`)
3968    REFERENCES `users` (`users_id`)
3969    ON DELETE CASCADE,
3970  CONSTRAINT `ob_approver_ibfk_2`
3971    FOREIGN KEY (`ob_item_detail_id`)
3972    REFERENCES `ob_item_detail` (`ob_item_detail_id`)
3973    ON DELETE CASCADE)
3974ENGINE = InnoDB
3975DEFAULT CHARACTER SET = latin1;
3976
3977
3978-- -----------------------------------------------------
3979-- Table `ob_item_log`
3980-- -----------------------------------------------------
3981CREATE TABLE IF NOT EXISTS `ob_item_log` (
3982  `ob_item_log_id` INT(10) NOT NULL AUTO_INCREMENT,
3983  `ob_item_detail_id` INT(10) NOT NULL,
3984  `users_id` INT(10) NOT NULL,
3985  `message` VARCHAR(255) NULL DEFAULT NULL,
3986  `log_time` DATETIME NULL DEFAULT NULL,
3987  PRIMARY KEY (`ob_item_log_id`),
3988  INDEX `ob_item_detail_id_asso_ndx` (`ob_item_detail_id` ASC),
3989  INDEX `users_id_asso_ndx` (`users_id` ASC),
3990  CONSTRAINT `ob_item_log_ibfk_1`
3991    FOREIGN KEY (`users_id`)
3992    REFERENCES `users` (`users_id`)
3993    ON DELETE CASCADE,
3994  CONSTRAINT `ob_item_log_ibfk_2`
3995    FOREIGN KEY (`ob_item_detail_id`)
3996    REFERENCES `ob_item_detail` (`ob_item_detail_id`)
3997    ON DELETE CASCADE)
3998ENGINE = InnoDB
3999DEFAULT CHARACTER SET = latin1;
4000
4001
4002-- -----------------------------------------------------
4003-- Table `ob_template`
4004-- -----------------------------------------------------
4005CREATE TABLE IF NOT EXISTS `ob_template` (
4006  `ob_template_id` INT(20) NOT NULL AUTO_INCREMENT,
4007  `name` VARCHAR(50) NULL DEFAULT NULL,
4008  `active` CHAR(1) NULL DEFAULT 't',
4009  PRIMARY KEY (`ob_template_id`),
4010  UNIQUE INDEX `name` (`name` ASC),
4011  INDEX `active` (`active` ASC))
4012ENGINE = InnoDB
4013AUTO_INCREMENT = 104
4014DEFAULT CHARACTER SET = latin1;
4015
4016
4017-- -----------------------------------------------------
4018-- Table `ob_item_template`
4019-- -----------------------------------------------------
4020CREATE TABLE IF NOT EXISTS `ob_item_template` (
4021  `ob_item_id` INT(10) NOT NULL DEFAULT '0',
4022  `ob_template_id` INT(10) NOT NULL DEFAULT '0',
4023  `active` CHAR(1) NULL DEFAULT 't',
4024  `isdefault` CHAR(1) NULL DEFAULT 'f',
4025  PRIMARY KEY (`ob_item_id`, `ob_template_id`),
4026  INDEX `ob_item_id_ndx` (`ob_item_id` ASC),
4027  INDEX `ob_template_id_ndx` (`ob_template_id` ASC),
4028  CONSTRAINT `ob_item_id_ibfk_1`
4029    FOREIGN KEY (`ob_item_id`)
4030    REFERENCES `ob_item` (`ob_item_id`)
4031    ON DELETE CASCADE
4032    ON UPDATE CASCADE,
4033  CONSTRAINT `ob_template_id_ibfk_1`
4034    FOREIGN KEY (`ob_template_id`)
4035    REFERENCES `ob_template` (`ob_template_id`)
4036    ON DELETE CASCADE
4037    ON UPDATE CASCADE)
4038ENGINE = InnoDB
4039DEFAULT CHARACTER SET = latin1;
4040
4041
4042-- -----------------------------------------------------
4043-- Table `ob_user_info`
4044-- -----------------------------------------------------
4045CREATE TABLE IF NOT EXISTS `ob_user_info` (
4046  `ob_user_info_id` INT(10) NOT NULL AUTO_INCREMENT,
4047  `ob_item_detail_id` INT(10) NOT NULL,
4048  `users_id` INT(10) NOT NULL,
4049  `data_collector` VARCHAR(255) NULL DEFAULT NULL,
4050  PRIMARY KEY (`ob_user_info_id`),
4051  INDEX `ob_item_detail_id_asso_ndx` (`ob_item_detail_id` ASC),
4052  INDEX `users_id_asso_ndx` (`users_id` ASC),
4053  CONSTRAINT `ob_user_info_ibfk_1`
4054    FOREIGN KEY (`users_id`)
4055    REFERENCES `users` (`users_id`)
4056    ON DELETE CASCADE,
4057  CONSTRAINT `ob_user_info_ibfk_2`
4058    FOREIGN KEY (`ob_item_detail_id`)
4059    REFERENCES `ob_item_detail` (`ob_item_detail_id`)
4060    ON DELETE CASCADE)
4061ENGINE = InnoDB
4062DEFAULT CHARACTER SET = latin1;
4063
4064
4065-- -----------------------------------------------------
4066-- Table `offer_template`
4067-- -----------------------------------------------------
4068CREATE TABLE IF NOT EXISTS `offer_template` (
4069  `offer_template_id` INT(11) NOT NULL AUTO_INCREMENT,
4070  `approval_chain_id` INT(11) NULL DEFAULT NULL,
4071  `communication_template_id` INT(11) NOT NULL,
4072  `notification_template_id` INT(11) NOT NULL,
4073  `rejection_template_id` INT(11) NOT NULL,
4074  `name` VARCHAR(50) NULL DEFAULT NULL,
4075  `description` VARCHAR(250) NULL DEFAULT NULL,
4076  `sort_order` INT(11) NOT NULL,
4077  `active` CHAR(1) NOT NULL,
4078  PRIMARY KEY (`offer_template_id`),
4079  INDEX `FK_approval_chain` (`approval_chain_id` ASC),
4080  INDEX `FK_communication_template` (`communication_template_id` ASC),
4081  CONSTRAINT `FK_approval_chain`
4082    FOREIGN KEY (`approval_chain_id`)
4083    REFERENCES `approval_chain` (`approval_chain_id`),
4084  CONSTRAINT `FK_communication_template`
4085    FOREIGN KEY (`communication_template_id`)
4086    REFERENCES `communication_template` (`communication_template_id`))
4087ENGINE = InnoDB
4088AUTO_INCREMENT = 18
4089DEFAULT CHARACTER SET = latin1
4090ROW_FORMAT = DYNAMIC;
4091
4092
4093-- -----------------------------------------------------
4094-- Table `offer`
4095-- -----------------------------------------------------
4096CREATE TABLE IF NOT EXISTS `offer` (
4097  `offer_id` INT(11) NOT NULL AUTO_INCREMENT,
4098  `offer_template_id` INT(11) NOT NULL,
4099  `users_id` INT(11) NULL DEFAULT NULL,
4100  `application_id` INT(11) NULL DEFAULT NULL,
4101  `status` VARCHAR(50) NULL DEFAULT NULL,
4102  `create_date` DATETIME NULL DEFAULT NULL,
4103  `extend_date` DATETIME NULL DEFAULT NULL,
4104  `accept_date` DATETIME NULL DEFAULT NULL,
4105  `reject_date` DATETIME NULL DEFAULT NULL,
4106  `esig` CHAR(1) NULL DEFAULT NULL,
4107  `number_1` INT(11) NULL DEFAULT NULL,
4108  `number_2` INT(11) NULL DEFAULT NULL,
4109  `number_3` INT(11) NULL DEFAULT NULL,
4110  `number_4` INT(11) NULL DEFAULT NULL,
4111  `number_5` INT(11) NULL DEFAULT NULL,
4112  `double_1` DOUBLE(10,2) NULL DEFAULT NULL,
4113  `double_2` DOUBLE(10,2) NULL DEFAULT NULL,
4114  `double_3` DOUBLE(10,2) NULL DEFAULT NULL,
4115  `double_4` DOUBLE(10,2) NULL DEFAULT NULL,
4116  `double_5` DOUBLE(10,2) NULL DEFAULT NULL,
4117  `date_1` DATE NULL DEFAULT NULL,
4118  `date_2` DATE NULL DEFAULT NULL,
4119  `date_3` DATE NULL DEFAULT NULL,
4120  `date_4` DATE NULL DEFAULT NULL,
4121  `date_5` DATE NULL DEFAULT NULL,
4122  `datetime_1` DATETIME NULL DEFAULT NULL,
4123  `datetime_2` DATETIME NULL DEFAULT NULL,
4124  `datetime_3` DATETIME NULL DEFAULT NULL,
4125  `datetime_4` DATETIME NULL DEFAULT NULL,
4126  `datetime_5` DATETIME NULL DEFAULT NULL,
4127  `string512_1` VARCHAR(512) NULL DEFAULT NULL,
4128  `string512_2` VARCHAR(512) NULL DEFAULT NULL,
4129  `string512_3` VARCHAR(512) NULL DEFAULT NULL,
4130  `string512_4` VARCHAR(512) NULL DEFAULT NULL,
4131  `string512_5` VARCHAR(512) NULL DEFAULT NULL,
4132  `string1024_1` VARCHAR(1024) NULL DEFAULT NULL,
4133  `string1024_2` VARCHAR(1024) NULL DEFAULT NULL,
4134  `string1024_3` VARCHAR(1024) NULL DEFAULT NULL,
4135  `string1024_4` VARCHAR(1024) NULL DEFAULT NULL,
4136  `string1024_5` VARCHAR(1024) NULL DEFAULT NULL,
4137  `text_1` TEXT NULL DEFAULT NULL,
4138  `text_2` TEXT NULL DEFAULT NULL,
4139  `text_3` TEXT NULL DEFAULT NULL,
4140  `text_4` TEXT NULL DEFAULT NULL,
4141  `text_5` TEXT NULL DEFAULT NULL,
4142  PRIMARY KEY (`offer_id`),
4143  INDEX `FK_users_id` (`users_id` ASC),
4144  INDEX `FK_application_id` (`application_id` ASC),
4145  INDEX `FK_offer_temp_id` (`offer_template_id` ASC),
4146  CONSTRAINT `FK_offer_temp_id`
4147    FOREIGN KEY (`offer_template_id`)
4148    REFERENCES `offer_template` (`offer_template_id`),
4149  CONSTRAINT `FK_users_id`
4150    FOREIGN KEY (`users_id`)
4151    REFERENCES `users` (`users_id`))
4152ENGINE = InnoDB
4153AUTO_INCREMENT = 3
4154DEFAULT CHARACTER SET = latin1;
4155
4156
4157-- -----------------------------------------------------
4158-- Table `offer_approval`
4159-- -----------------------------------------------------
4160CREATE TABLE IF NOT EXISTS `offer_approval` (
4161  `offer_approval_id` INT(11) NOT NULL AUTO_INCREMENT,
4162  `offer_id` INT(11) NOT NULL DEFAULT '0',
4163  `active` CHAR(1) NULL DEFAULT NULL,
4164  `users_id` INT(11) NOT NULL DEFAULT '0',
4165  `approver_id` INT(10) NULL DEFAULT NULL,
4166  `delegator_id` INT(10) NULL DEFAULT NULL,
4167  `approve_order` INT(3) NULL DEFAULT NULL,
4168  `result` VARCHAR(50) NULL DEFAULT NULL,
4169  `comment` TEXT NULL DEFAULT NULL,
4170  `received` DATETIME NULL DEFAULT NULL,
4171  `completed` DATETIME NULL DEFAULT NULL,
4172  PRIMARY KEY (`offer_approval_id`),
4173  INDEX `users_asso_ndx` (`users_id` ASC),
4174  INDEX `received` (`received` ASC, `completed` ASC),
4175  CONSTRAINT `offer_user_3353`
4176    FOREIGN KEY (`users_id`)
4177    REFERENCES `users` (`users_id`)
4178    ON DELETE CASCADE
4179    ON UPDATE CASCADE)
4180ENGINE = InnoDB
4181AUTO_INCREMENT = 859
4182DEFAULT CHARACTER SET = latin1;
4183
4184
4185-- -----------------------------------------------------
4186-- Table `offer_element`
4187-- -----------------------------------------------------
4188CREATE TABLE IF NOT EXISTS `offer_element` (
4189  `offer_element_id` INT(11) NOT NULL AUTO_INCREMENT,
4190  `offer_template_id` INT(11) NOT NULL,
4191  `custom_field_id` INT(20) NOT NULL,
4192  `label` VARCHAR(256) NOT NULL,
4193  `list` VARCHAR(50) NULL DEFAULT NULL,
4194  `required` CHAR(1) NULL DEFAULT 'f',
4195  `sort_order` INT(11) NULL DEFAULT NULL,
4196  PRIMARY KEY (`offer_element_id`),
4197  INDEX `FK_element_offer_template` (`offer_template_id` ASC),
4198  CONSTRAINT `FK_element_offer_template`
4199    FOREIGN KEY (`offer_template_id`)
4200    REFERENCES `offer_template` (`offer_template_id`)
4201    ON DELETE CASCADE
4202    ON UPDATE CASCADE)
4203ENGINE = InnoDB
4204AUTO_INCREMENT = 54
4205DEFAULT CHARACTER SET = latin1;
4206
4207
4208-- -----------------------------------------------------
4209-- Table `offer_old`
4210-- -----------------------------------------------------
4211CREATE TABLE IF NOT EXISTS `offer_old` (
4212  `offer_id` INT(11) NOT NULL AUTO_INCREMENT,
4213  `users_id` INT(11) NULL DEFAULT NULL,
4214  `status` VARCHAR(50) NULL DEFAULT NULL,
4215  `extend_date` DATETIME NULL DEFAULT NULL,
4216  `accept_date` DATETIME NULL DEFAULT NULL,
4217  `reject_date` DATETIME NULL DEFAULT NULL,
4218  `d1` DATETIME NULL DEFAULT NULL,
4219  `d2` DATETIME NULL DEFAULT NULL,
4220  `d3` DATETIME NULL DEFAULT NULL,
4221  `d4` DATETIME NULL DEFAULT NULL,
4222  `d5` DATETIME NULL DEFAULT NULL,
4223  `d6` DATETIME NULL DEFAULT NULL,
4224  `d7` DATETIME NULL DEFAULT NULL,
4225  `d8` DATETIME NULL DEFAULT NULL,
4226  `d9` DATETIME NULL DEFAULT NULL,
4227  `d10` DATETIME NULL DEFAULT NULL,
4228  `t1` VARCHAR(1000) NULL DEFAULT NULL,
4229  `t2` VARCHAR(1000) NULL DEFAULT NULL,
4230  `t3` VARCHAR(1000) NULL DEFAULT NULL,
4231  `t4` VARCHAR(1000) NULL DEFAULT NULL,
4232  `t5` VARCHAR(1000) NULL DEFAULT NULL,
4233  `t6` VARCHAR(1000) NULL DEFAULT NULL,
4234  `t7` VARCHAR(1000) NULL DEFAULT NULL,
4235  `t8` VARCHAR(1000) NULL DEFAULT NULL,
4236  `t9` VARCHAR(1000) NULL DEFAULT NULL,
4237  `t10` VARCHAR(1000) NULL DEFAULT NULL,
4238  `t11` VARCHAR(1000) NULL DEFAULT NULL,
4239  `t12` VARCHAR(1000) NULL DEFAULT NULL,
4240  `t13` VARCHAR(1000) NULL DEFAULT NULL,
4241  `t14` VARCHAR(1000) NULL DEFAULT NULL,
4242  `t15` VARCHAR(1000) NULL DEFAULT NULL,
4243  `t16` VARCHAR(1000) NULL DEFAULT NULL,
4244  `t17` VARCHAR(1000) NULL DEFAULT NULL,
4245  `t18` VARCHAR(1000) NULL DEFAULT NULL,
4246  `t19` VARCHAR(1000) NULL DEFAULT NULL,
4247  `t20` VARCHAR(1000) NULL DEFAULT NULL,
4248  `t21` VARCHAR(1000) NULL DEFAULT NULL,
4249  `t22` VARCHAR(1000) NULL DEFAULT NULL,
4250  `t23` VARCHAR(1000) NULL DEFAULT NULL,
4251  `t24` VARCHAR(1000) NULL DEFAULT NULL,
4252  `t25` VARCHAR(1000) NULL DEFAULT NULL,
4253  `t26` VARCHAR(1000) NULL DEFAULT NULL,
4254  `t27` VARCHAR(1000) NULL DEFAULT NULL,
4255  `t28` VARCHAR(1000) NULL DEFAULT NULL,
4256  `t29` VARCHAR(1000) NULL DEFAULT NULL,
4257  `t30` VARCHAR(1000) NULL DEFAULT NULL,
4258  `t31` VARCHAR(1000) NULL DEFAULT NULL,
4259  `t32` VARCHAR(1000) NULL DEFAULT NULL,
4260  `t33` VARCHAR(1000) NULL DEFAULT NULL,
4261  `n1` INT(11) NULL DEFAULT NULL,
4262  `n2` INT(11) NULL DEFAULT NULL,
4263  `n3` INT(11) NULL DEFAULT NULL,
4264  `n4` INT(11) NULL DEFAULT NULL,
4265  `n5` INT(11) NULL DEFAULT NULL,
4266  `application_id` INT(11) NULL DEFAULT NULL,
4267  `created` DATETIME NULL DEFAULT NULL,
4268  `esig` CHAR(1) NULL DEFAULT NULL,
4269  `t34` VARCHAR(500) NULL DEFAULT NULL,
4270  `t35` VARCHAR(500) NULL DEFAULT NULL,
4271  `t36` VARCHAR(500) NULL DEFAULT NULL,
4272  `t37` VARCHAR(500) NULL DEFAULT NULL,
4273  `t38` VARCHAR(500) NULL DEFAULT NULL,
4274  `t39` VARCHAR(500) NULL DEFAULT NULL,
4275  `t40` VARCHAR(500) NULL DEFAULT NULL,
4276  `t48` VARCHAR(500) NULL DEFAULT NULL,
4277  `t49` VARCHAR(500) NULL DEFAULT NULL,
4278  `t50` VARCHAR(500) NULL DEFAULT NULL,
4279  `t51` VARCHAR(500) NULL DEFAULT NULL,
4280  `t52` VARCHAR(500) NULL DEFAULT NULL,
4281  `t53` VARCHAR(500) NULL DEFAULT NULL,
4282  `t54` VARCHAR(500) NULL DEFAULT NULL,
4283  `t55` VARCHAR(500) NULL DEFAULT NULL,
4284  `t56` VARCHAR(500) NULL DEFAULT NULL,
4285  `t57` VARCHAR(500) NULL DEFAULT NULL,
4286  `t58` VARCHAR(500) NULL DEFAULT NULL,
4287  `t59` VARCHAR(500) NULL DEFAULT NULL,
4288  `t60` VARCHAR(500) NULL DEFAULT NULL,
4289  `t61` VARCHAR(500) NULL DEFAULT NULL,
4290  `t62` VARCHAR(500) NULL DEFAULT NULL,
4291  `t63` VARCHAR(500) NULL DEFAULT NULL,
4292  `t64` VARCHAR(500) NULL DEFAULT NULL,
4293  `t65` VARCHAR(500) NULL DEFAULT NULL,
4294  `t66` VARCHAR(500) NULL DEFAULT NULL,
4295  `t67` VARCHAR(500) NULL DEFAULT NULL,
4296  `t68` VARCHAR(500) NULL DEFAULT NULL,
4297  `t69` VARCHAR(500) NULL DEFAULT NULL,
4298  `t70` VARCHAR(500) NULL DEFAULT NULL,
4299  `t71` VARCHAR(500) NULL DEFAULT NULL,
4300  `t72` VARCHAR(500) NULL DEFAULT NULL,
4301  `d11` DATETIME NULL DEFAULT NULL,
4302  `d12` DATETIME NULL DEFAULT NULL,
4303  `d13` DATETIME NULL DEFAULT NULL,
4304  `d14` DATETIME NULL DEFAULT NULL,
4305  `d15` DATETIME NULL DEFAULT NULL,
4306  `d16` DATETIME NULL DEFAULT NULL,
4307  `d17` DATETIME NULL DEFAULT NULL,
4308  `d18` DATETIME NULL DEFAULT NULL,
4309  `d19` DATETIME NULL DEFAULT NULL,
4310  `d20` DATETIME NULL DEFAULT NULL,
4311  `d21` DATETIME NULL DEFAULT NULL,
4312  `d22` DATETIME NULL DEFAULT NULL,
4313  `d23` DATETIME NULL DEFAULT NULL,
4314  `d24` DATETIME NULL DEFAULT NULL,
4315  `d25` DATETIME NULL DEFAULT NULL,
4316  `d26` DATETIME NULL DEFAULT NULL,
4317  `d27` DATETIME NULL DEFAULT NULL,
4318  `d28` DATETIME NULL DEFAULT NULL,
4319  `d29` DATETIME NULL DEFAULT NULL,
4320  `d30` DATETIME NULL DEFAULT NULL,
4321  `n6` INT(11) NULL DEFAULT NULL,
4322  `n7` INT(11) NULL DEFAULT NULL,
4323  `n8` INT(11) NULL DEFAULT NULL,
4324  `n9` INT(11) NULL DEFAULT NULL,
4325  `n10` INT(11) NULL DEFAULT NULL,
4326  `n11` INT(11) NULL DEFAULT NULL,
4327  `n12` INT(11) NULL DEFAULT NULL,
4328  `n13` INT(11) NULL DEFAULT NULL,
4329  `n14` INT(11) NULL DEFAULT NULL,
4330  `n15` INT(11) NULL DEFAULT NULL,
4331  `n16` INT(11) NULL DEFAULT NULL,
4332  `n17` INT(11) NULL DEFAULT NULL,
4333  `n18` INT(11) NULL DEFAULT NULL,
4334  `n19` INT(11) NULL DEFAULT NULL,
4335  `n20` INT(11) NULL DEFAULT NULL,
4336  PRIMARY KEY (`offer_id`))
4337ENGINE = InnoDB
4338AUTO_INCREMENT = 461
4339DEFAULT CHARACTER SET = latin1;
4340
4341
4342-- -----------------------------------------------------
4343-- Table `view_element`
4344-- -----------------------------------------------------
4345CREATE TABLE IF NOT EXISTS `view_element` (
4346  `view_element_id` INT(11) NOT NULL AUTO_INCREMENT,
4347  `parent` INT(11) NULL DEFAULT NULL,
4348  `label` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Same as component label in the view component architecture',
4349  `short_description` VARCHAR(150) NULL DEFAULT NULL,
4350  `meta_id` INT(11) NULL DEFAULT NULL,
4351  `display_component` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Example: list has 5 possible values dropdown, vcheck, hcheck,\n \nvradio, hradion, and multi.',
4352  `sort_order` INT(11) NULL DEFAULT NULL COMMENT 'In future replace with layout coordidate.',
4353  `dc_size` VARCHAR(4) NULL DEFAULT NULL,
4354  `dc_rows` VARCHAR(4) NULL DEFAULT NULL,
4355  `dc_list` VARCHAR(50) NULL DEFAULT NULL,
4356  `dc_sec_edit` VARCHAR(150) NULL DEFAULT NULL,
4357  `dc_sec_ro` VARCHAR(150) NULL DEFAULT NULL,
4358  `required` CHAR(1) NULL DEFAULT NULL,
4359  `value` VARCHAR(150) NULL DEFAULT NULL,
4360  `update_date` DATETIME NULL DEFAULT NULL,
4361  PRIMARY KEY (`view_element_id`),
4362  INDEX `FK_view_element` (`meta_id` ASC))
4363ENGINE = InnoDB
4364AUTO_INCREMENT = 9682
4365DEFAULT CHARACTER SET = latin1;
4366
4367
4368-- -----------------------------------------------------
4369-- Table `offer_template_old`
4370-- -----------------------------------------------------
4371CREATE TABLE IF NOT EXISTS `offer_template_old` (
4372  `offer_template_id` INT(11) NOT NULL AUTO_INCREMENT,
4373  `view_element_id` INT(11) NULL DEFAULT NULL,
4374  `display_name` VARCHAR(50) NULL DEFAULT NULL,
4375  `approvers` VARCHAR(50) NULL DEFAULT NULL,
4376  `active` CHAR(1) NOT NULL,
4377  `description` VARCHAR(250) NULL DEFAULT NULL,
4378  `sort_order` INT(11) NOT NULL,
4379  `communication_template_id` INT(11) NULL DEFAULT NULL,
4380  PRIMARY KEY (`offer_template_id`),
4381  INDEX `FK_offer_template` (`view_element_id` ASC),
4382  CONSTRAINT `FK_offer_template`
4383    FOREIGN KEY (`view_element_id`)
4384    REFERENCES `view_element` (`view_element_id`))
4385ENGINE = InnoDB
4386AUTO_INCREMENT = 81
4387DEFAULT CHARACTER SET = latin1
4388ROW_FORMAT = DYNAMIC;
4389
4390
4391-- -----------------------------------------------------
4392-- Table `pers_reference`
4393-- -----------------------------------------------------
4394CREATE TABLE IF NOT EXISTS `pers_reference` (
4395  `pers_reference_id` INT(11) NOT NULL AUTO_INCREMENT,
4396  `candidate_id` INT(11) NOT NULL DEFAULT '0',
4397  `name` CHAR(60) NULL DEFAULT NULL,
4398  `title` CHAR(50) NULL DEFAULT NULL,
4399  `company` CHAR(50) NULL DEFAULT NULL,
4400  `email` CHAR(50) NULL DEFAULT NULL,
4401  `address1` CHAR(50) NULL DEFAULT NULL,
4402  `address2` CHAR(50) NULL DEFAULT NULL,
4403  `city` CHAR(50) NULL DEFAULT NULL,
4404  `state` CHAR(2) NULL DEFAULT NULL,
4405  `zip` CHAR(10) NULL DEFAULT NULL,
4406  `phone` CHAR(20) NULL DEFAULT NULL,
4407  `ref_checked` CHAR(1) NULL DEFAULT NULL,
4408  `reviewed` CHAR(1) NULL DEFAULT NULL,
4409  PRIMARY KEY (`pers_reference_id`),
4410  INDEX `candidate_ndx` (`candidate_id` ASC),
4411  CONSTRAINT `0_3339`
4412    FOREIGN KEY (`candidate_id`)
4413    REFERENCES `candidate_orig` (`candidate_id`)
4414    ON DELETE CASCADE
4415    ON UPDATE CASCADE)
4416ENGINE = InnoDB
4417DEFAULT CHARACTER SET = latin1;
4418
4419
4420-- -----------------------------------------------------
4421-- Table `persona`
4422-- -----------------------------------------------------
4423CREATE TABLE IF NOT EXISTS `persona` (
4424  `persona_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
4425  `title` VARCHAR(200) NULL DEFAULT NULL,
4426  `description` TEXT NULL DEFAULT NULL,
4427  `requirements` TEXT NULL DEFAULT NULL,
4428  `keywords` TEXT NULL DEFAULT NULL,
4429  PRIMARY KEY (`persona_id`))
4430ENGINE = InnoDB
4431AUTO_INCREMENT = 6
4432DEFAULT CHARACTER SET = latin1;
4433
4434
4435-- -----------------------------------------------------
4436-- Table `place`
4437-- -----------------------------------------------------
4438CREATE TABLE IF NOT EXISTS `place` (
4439  `place_id` INT(20) NOT NULL AUTO_INCREMENT,
4440  `name` CHAR(40) NOT NULL DEFAULT '',
4441  `next` INT(11) NOT NULL DEFAULT '0',
4442  PRIMARY KEY (`place_id`))
4443ENGINE = InnoDB
4444AUTO_INCREMENT = 208
4445DEFAULT CHARACTER SET = latin1;
4446
4447
4448-- -----------------------------------------------------
4449-- Table `pool_category`
4450-- -----------------------------------------------------
4451CREATE TABLE IF NOT EXISTS `pool_category` (
4452  `pool_category_id` INT(11) NOT NULL AUTO_INCREMENT,
4453  `name` VARCHAR(50) NOT NULL DEFAULT '',
4454  `type` VARCHAR(50) NOT NULL DEFAULT '',
4455  PRIMARY KEY (`pool_category_id`))
4456ENGINE = InnoDB
4457AUTO_INCREMENT = 23
4458DEFAULT CHARACTER SET = latin1;
4459
4460
4461-- -----------------------------------------------------
4462-- Table `pool`
4463-- -----------------------------------------------------
4464CREATE TABLE IF NOT EXISTS `pool` (
4465  `pool_id` INT(20) NOT NULL AUTO_INCREMENT,
4466  `candidate_id` INT(20) NOT NULL DEFAULT '0',
4467  `pool_category_id` INT(20) NULL DEFAULT NULL,
4468  `users_id` INT(20) NULL DEFAULT NULL,
4469  `comments` CHAR(40) NULL DEFAULT NULL,
4470  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4471  PRIMARY KEY (`pool_id`),
4472  INDEX `pool_can_asso_ndx` (`candidate_id` ASC),
4473  INDEX `pool_sk_det_asso_ndx` (`pool_category_id` ASC),
4474  CONSTRAINT `0_2389`
4475    FOREIGN KEY (`candidate_id`)
4476    REFERENCES `candidate_orig` (`candidate_id`)
4477    ON DELETE CASCADE
4478    ON UPDATE CASCADE,
4479  CONSTRAINT `0_2390`
4480    FOREIGN KEY (`pool_category_id`)
4481    REFERENCES `pool_category` (`pool_category_id`)
4482    ON DELETE CASCADE
4483    ON UPDATE CASCADE)
4484ENGINE = InnoDB
4485AUTO_INCREMENT = 103
4486DEFAULT CHARACTER SET = latin1;
4487
4488
4489-- -----------------------------------------------------
4490-- Table `prof_reference`
4491-- -----------------------------------------------------
4492CREATE TABLE IF NOT EXISTS `prof_reference` (
4493  `prof_reference_id` INT(11) NOT NULL AUTO_INCREMENT,
4494  `candidate_id` INT(11) NOT NULL DEFAULT '0',
4495  `name` VARCHAR(60) NULL DEFAULT NULL,
4496  `title` VARCHAR(50) NULL DEFAULT NULL,
4497  `company` VARCHAR(50) NULL DEFAULT NULL,
4498  `email` VARCHAR(50) NULL DEFAULT NULL,
4499  `address1` VARCHAR(50) NULL DEFAULT NULL,
4500  `address2` VARCHAR(50) NULL DEFAULT NULL,
4501  `city` VARCHAR(50) NULL DEFAULT NULL,
4502  `state` CHAR(2) NULL DEFAULT NULL,
4503  `zip` VARCHAR(10) NULL DEFAULT NULL,
4504  `phone` VARCHAR(20) NULL DEFAULT NULL,
4505  `type` VARCHAR(20) NULL DEFAULT NULL,
4506  `ref_checked` CHAR(1) NULL DEFAULT NULL,
4507  `relationship` VARCHAR(50) NULL DEFAULT NULL,
4508  PRIMARY KEY (`prof_reference_id`),
4509  INDEX `candidate_ndx` (`candidate_id` ASC),
4510  CONSTRAINT `0_3341`
4511    FOREIGN KEY (`candidate_id`)
4512    REFERENCES `candidate_orig` (`candidate_id`)
4513    ON DELETE CASCADE
4514    ON UPDATE CASCADE)
4515ENGINE = InnoDB
4516AUTO_INCREMENT = 307
4517DEFAULT CHARACTER SET = latin1;
4518
4519
4520-- -----------------------------------------------------
4521-- Table `question_group_detail`
4522-- -----------------------------------------------------
4523CREATE TABLE IF NOT EXISTS `question_group_detail` (
4524  `question_group_id` INT(20) NOT NULL DEFAULT '0',
4525  `general_questions_id` INT(20) NOT NULL DEFAULT '0',
4526  PRIMARY KEY (`question_group_id`, `general_questions_id`),
4527  INDEX `question_group_ndx` (`question_group_id` ASC),
4528  INDEX `general_questions_ndx` (`general_questions_id` ASC),
4529  CONSTRAINT `0_281`
4530    FOREIGN KEY (`question_group_id`)
4531    REFERENCES `question_group` (`question_group_id`)
4532    ON DELETE CASCADE
4533    ON UPDATE CASCADE,
4534  CONSTRAINT `0_282`
4535    FOREIGN KEY (`general_questions_id`)
4536    REFERENCES `general_questions` (`general_questions_id`)
4537    ON DELETE CASCADE
4538    ON UPDATE CASCADE)
4539ENGINE = InnoDB
4540DEFAULT CHARACTER SET = latin1;
4541
4542
4543-- -----------------------------------------------------
4544-- Table `ref_check_group`
4545-- -----------------------------------------------------
4546CREATE TABLE IF NOT EXISTS `ref_check_group` (
4547  `ref_check_group_id` INT(20) NOT NULL AUTO_INCREMENT,
4548  `name` VARCHAR(50) NULL DEFAULT NULL,
4549  `location` VARCHAR(50) NULL DEFAULT NULL,
4550  `active` CHAR(1) NULL DEFAULT NULL,
4551  `page` VARCHAR(50) NULL DEFAULT NULL,
4552  PRIMARY KEY (`ref_check_group_id`),
4553  UNIQUE INDEX `location` (`location` ASC))
4554ENGINE = InnoDB
4555AUTO_INCREMENT = 5
4556DEFAULT CHARACTER SET = latin1;
4557
4558
4559-- -----------------------------------------------------
4560-- Table `ref_check_map`
4561-- -----------------------------------------------------
4562CREATE TABLE IF NOT EXISTS `ref_check_map` (
4563  `ref_check_map_id` INT(20) NOT NULL AUTO_INCREMENT,
4564  `ref_check_group_id` INT(20) NOT NULL DEFAULT '0',
4565  `remote_location` VARCHAR(100) NULL DEFAULT NULL,
4566  `ref_chk_column` VARCHAR(100) NULL DEFAULT NULL,
4567  PRIMARY KEY (`ref_check_map_id`, `ref_check_group_id`),
4568  UNIQUE INDEX `ref_check_map_unique` (`ref_check_group_id` ASC, `remote_location` ASC),
4569  INDEX `ref_check_group_id` (`ref_check_group_id` ASC),
4570  CONSTRAINT `ref_check_map_ibfk_1`
4571    FOREIGN KEY (`ref_check_group_id`)
4572    REFERENCES `ref_check_group` (`ref_check_group_id`)
4573    ON DELETE CASCADE
4574    ON UPDATE CASCADE)
4575ENGINE = InnoDB
4576AUTO_INCREMENT = 10
4577DEFAULT CHARACTER SET = latin1;
4578
4579
4580-- -----------------------------------------------------
4581-- Table `reference_check`
4582-- -----------------------------------------------------
4583CREATE TABLE IF NOT EXISTS `reference_check` (
4584  `reference_check_id` INT(20) NOT NULL AUTO_INCREMENT,
4585  `candidate_id` INT(20) NOT NULL DEFAULT '0',
4586  `ref_check_group_id` INT(20) NULL DEFAULT NULL,
4587  `organization` VARCHAR(50) NULL DEFAULT NULL,
4588  `complete` CHAR(1) NULL DEFAULT NULL,
4589  `start_date` DATE NULL DEFAULT NULL,
4590  `end_date` DATE NULL DEFAULT NULL,
4591  `current` CHAR(1) NULL DEFAULT NULL,
4592  `address1` VARCHAR(50) NULL DEFAULT NULL,
4593  `address2` VARCHAR(50) NULL DEFAULT NULL,
4594  `manager_name` VARCHAR(50) NULL DEFAULT NULL,
4595  `manager_phone` VARCHAR(50) NULL DEFAULT NULL,
4596  `candidate_name` VARCHAR(50) NULL DEFAULT NULL,
4597  `ssn` VARCHAR(20) NULL DEFAULT NULL,
4598  `dob` DATE NULL DEFAULT NULL,
4599  `reason_for_leave` VARCHAR(255) NULL DEFAULT NULL,
4600  `rehire_eligible` VARCHAR(50) NULL DEFAULT NULL,
4601  `work_type` VARCHAR(50) NULL DEFAULT NULL,
4602  `degree` VARCHAR(50) NULL DEFAULT NULL,
4603  `graduated` VARCHAR(50) NULL DEFAULT NULL,
4604  `title` VARCHAR(50) NULL DEFAULT NULL,
4605  `description` VARCHAR(255) NULL DEFAULT NULL,
4606  `contact` CHAR(1) NULL DEFAULT NULL,
4607  `comments` TEXT NULL DEFAULT NULL,
4608  `salary` VARCHAR(20) NULL DEFAULT NULL,
4609  `work_quality` TEXT NULL DEFAULT NULL,
4610  `supervisor_responsibility` TEXT NULL DEFAULT NULL,
4611  `relates` TEXT NULL DEFAULT NULL,
4612  `strong_points` TEXT NULL DEFAULT NULL,
4613  `date` DATE NULL DEFAULT NULL,
4614  `hr_rep` VARCHAR(100) NULL DEFAULT NULL,
4615  PRIMARY KEY (`reference_check_id`, `candidate_id`),
4616  INDEX `candidate_id` (`candidate_id` ASC),
4617  INDEX `ref_check_group_id` (`ref_check_group_id` ASC),
4618  CONSTRAINT `reference_check_ibfk_1`
4619    FOREIGN KEY (`candidate_id`)
4620    REFERENCES `candidate_orig` (`candidate_id`)
4621    ON DELETE CASCADE
4622    ON UPDATE CASCADE,
4623  CONSTRAINT `reference_check_ibfk_2`
4624    FOREIGN KEY (`ref_check_group_id`)
4625    REFERENCES `ref_check_group` (`ref_check_group_id`)
4626    ON DELETE CASCADE
4627    ON UPDATE CASCADE)
4628ENGINE = InnoDB
4629AUTO_INCREMENT = 184
4630DEFAULT CHARACTER SET = latin1;
4631
4632
4633-- -----------------------------------------------------
4634-- Table `referral`
4635-- -----------------------------------------------------
4636CREATE TABLE IF NOT EXISTS `referral` (
4637  `referral_id` INT(20) NOT NULL AUTO_INCREMENT,
4638  `candidate_id` INT(20) NOT NULL,
4639  `requisition_id` INT(20) NULL DEFAULT NULL,
4640  `type` VARCHAR(20) NULL DEFAULT NULL,
4641  `source` VARCHAR(60) NULL DEFAULT NULL,
4642  `source_detail` VARCHAR(60) NULL DEFAULT NULL,
4643  `url` VARCHAR(120) NULL DEFAULT NULL,
4644  `referral_key` VARCHAR(20) NULL DEFAULT NULL,
4645  `status` VARCHAR(20) NULL DEFAULT NULL,
4646  PRIMARY KEY (`referral_id`, `candidate_id`),
4647  INDEX `candidate_id` (`candidate_id` ASC),
4648  CONSTRAINT `referral_ibfk_1`
4649    FOREIGN KEY (`candidate_id`)
4650    REFERENCES `candidate_orig` (`candidate_id`)
4651    ON DELETE CASCADE)
4652ENGINE = InnoDB
4653AUTO_INCREMENT = 22
4654DEFAULT CHARACTER SET = latin1;
4655
4656
4657-- -----------------------------------------------------
4658-- Table `relative`
4659-- -----------------------------------------------------
4660CREATE TABLE IF NOT EXISTS `relative` (
4661  `relative_id` INT(10) NOT NULL AUTO_INCREMENT,
4662  `candidate_id` INT(20) NULL DEFAULT NULL,
4663  `name` VARCHAR(50) NULL DEFAULT NULL,
4664  `address1` VARCHAR(50) NULL DEFAULT NULL,
4665  `address2` VARCHAR(50) NULL DEFAULT NULL,
4666  `city` VARCHAR(50) NULL DEFAULT NULL,
4667  `state` CHAR(2) NULL DEFAULT NULL,
4668  `zip` CHAR(9) NULL DEFAULT NULL,
4669  `country` VARCHAR(50) NULL DEFAULT NULL,
4670  `company` VARCHAR(50) NULL DEFAULT NULL,
4671  `department` VARCHAR(50) NULL DEFAULT NULL,
4672  `title` VARCHAR(50) NULL DEFAULT NULL,
4673  `relationship` VARCHAR(50) NULL DEFAULT NULL,
4674  `reviewed` CHAR(1) NULL DEFAULT NULL,
4675  PRIMARY KEY (`relative_id`),
4676  INDEX `candidate_id` (`candidate_id` ASC),
4677  CONSTRAINT `relative_ibfk_1`
4678    FOREIGN KEY (`candidate_id`)
4679    REFERENCES `candidate_orig` (`candidate_id`)
4680    ON DELETE CASCADE
4681    ON UPDATE CASCADE)
4682ENGINE = InnoDB
4683AUTO_INCREMENT = 22
4684DEFAULT CHARACTER SET = latin1;
4685
4686
4687-- -----------------------------------------------------
4688-- Table `repository`
4689-- -----------------------------------------------------
4690CREATE TABLE IF NOT EXISTS `repository` (
4691  `repository_id` INT(20) NOT NULL AUTO_INCREMENT,
4692  `candidate_id` INT(20) NOT NULL DEFAULT '0',
4693  `page_info_id` INT(10) NOT NULL DEFAULT '0',
4694  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4695  `ip_address` VARCHAR(64) NULL DEFAULT NULL,
4696  `session_info` VARCHAR(255) NULL DEFAULT NULL,
4697  `data` TEXT NULL DEFAULT NULL,
4698  PRIMARY KEY (`repository_id`),
4699  INDEX `candidate_id` (`candidate_id` ASC),
4700  INDEX `page_info_id` (`page_info_id` ASC),
4701  CONSTRAINT `repository_ibfk_2`
4702    FOREIGN KEY (`page_info_id`)
4703    REFERENCES `page_info` (`page_info_id`)
4704    ON DELETE CASCADE
4705    ON UPDATE CASCADE)
4706ENGINE = InnoDB
4707AUTO_INCREMENT = 17586
4708DEFAULT CHARACTER SET = latin1;
4709
4710
4711-- -----------------------------------------------------
4712-- Table `req_approval`
4713-- -----------------------------------------------------
4714CREATE TABLE IF NOT EXISTS `req_approval` (
4715  `req_approval_id` INT(10) NOT NULL AUTO_INCREMENT,
4716  `requisition_id` INT(11) NOT NULL DEFAULT '0',
4717  `users_id` INT(11) NOT NULL DEFAULT '0',
4718  `approve_order` INT(3) NULL DEFAULT NULL,
4719  `token_id` INT(11) NULL DEFAULT NULL,
4720  `result` VARCHAR(50) NULL DEFAULT NULL,
4721  `comment` TEXT NULL DEFAULT NULL,
4722  `received` DATETIME NULL DEFAULT NULL,
4723  `completed` DATETIME NULL DEFAULT NULL,
4724  `approver_id` INT(10) NULL DEFAULT NULL,
4725  `delegator_id` INT(10) NULL DEFAULT NULL,
4726  PRIMARY KEY (`req_approval_id`),
4727  INDEX `requisition_asso_ndx` (`requisition_id` ASC),
4728  INDEX `users_asso_ndx` (`users_id` ASC),
4729  INDEX `received` (`received` ASC, `completed` ASC),
4730  INDEX `requsition_users_ndx` (`requisition_id` ASC, `users_id` ASC),
4731  CONSTRAINT `0_3352`
4732    FOREIGN KEY (`requisition_id`)
4733    REFERENCES `requisition` (`requisition_id`)
4734    ON DELETE CASCADE
4735    ON UPDATE CASCADE,
4736  CONSTRAINT `0_3353`
4737    FOREIGN KEY (`users_id`)
4738    REFERENCES `users` (`users_id`)
4739    ON DELETE CASCADE
4740    ON UPDATE CASCADE)
4741ENGINE = InnoDB
4742AUTO_INCREMENT = 2144
4743DEFAULT CHARACTER SET = latin1;
4744
4745
4746-- -----------------------------------------------------
4747-- Table `req_group_details`
4748-- -----------------------------------------------------
4749CREATE TABLE IF NOT EXISTS `req_group_details` (
4750  `requisition_id` INT(20) NOT NULL DEFAULT '0',
4751  `req_group_id` INT(11) NOT NULL DEFAULT '0',
4752  `sort_order` INT(5) NOT NULL DEFAULT '0',
4753  PRIMARY KEY (`requisition_id`, `req_group_id`),
4754  INDEX `requisition_asso_ndx` (`requisition_id` ASC),
4755  INDEX `req_group_asso_ndx` (`req_group_id` ASC),
4756  CONSTRAINT `0_3360`
4757    FOREIGN KEY (`req_group_id`)
4758    REFERENCES `req_group` (`req_group_id`)
4759    ON DELETE CASCADE
4760    ON UPDATE CASCADE)
4761ENGINE = InnoDB
4762DEFAULT CHARACTER SET = latin1;
4763
4764
4765-- -----------------------------------------------------
4766-- Table `req_questions`
4767-- -----------------------------------------------------
4768CREATE TABLE IF NOT EXISTS `req_questions` (
4769  `requisition_id` INT(20) NOT NULL DEFAULT '0',
4770  `question_group_id` INT(20) NOT NULL DEFAULT '0',
4771  `sort_order` INT(5) NOT NULL DEFAULT '0',
4772  PRIMARY KEY (`requisition_id`, `question_group_id`),
4773  INDEX `requisition_asso_ndx` (`requisition_id` ASC),
4774  INDEX `question_group_asso_ndx` (`question_group_id` ASC),
4775  CONSTRAINT `0_3356`
4776    FOREIGN KEY (`question_group_id`)
4777    REFERENCES `question_group` (`question_group_id`)
4778    ON DELETE CASCADE
4779    ON UPDATE CASCADE)
4780ENGINE = InnoDB
4781DEFAULT CHARACTER SET = latin1;
4782
4783
4784-- -----------------------------------------------------
4785-- Table `req_status`
4786-- -----------------------------------------------------
4787CREATE TABLE IF NOT EXISTS `req_status` (
4788  `req_status_id` INT(20) NOT NULL AUTO_INCREMENT,
4789  `users_id` INT(11) NOT NULL DEFAULT '0',
4790  `requisition_id` INT(11) NOT NULL DEFAULT '0',
4791  `status` CHAR(50) NOT NULL DEFAULT '',
4792  `comments` CHAR(50) NULL DEFAULT NULL,
4793  `status_date` DATETIME NULL DEFAULT NULL,
4794  PRIMARY KEY (`req_status_id`),
4795  INDEX `iiusers_ndx` (`users_id` ASC),
4796  INDEX `requisition_ndx` (`requisition_id` ASC),
4797  INDEX `status_date` (`status_date` ASC),
4798  CONSTRAINT `0_3280`
4799    FOREIGN KEY (`users_id`)
4800    REFERENCES `users` (`users_id`)
4801    ON DELETE CASCADE
4802    ON UPDATE CASCADE)
4803ENGINE = InnoDB
4804AUTO_INCREMENT = 152476
4805DEFAULT CHARACTER SET = latin1;
4806
4807
4808-- -----------------------------------------------------
4809-- Table `req_users_access`
4810-- -----------------------------------------------------
4811CREATE TABLE IF NOT EXISTS `req_users_access` (
4812  `requisition_id` INT(20) NOT NULL,
4813  `users_id` INT(20) NOT NULL,
4814  `req_users_access_id` INT(11) NOT NULL AUTO_INCREMENT,
4815  PRIMARY KEY (`requisition_id`, `users_id`),
4816  UNIQUE INDEX `req_users_access_id` (`req_users_access_id` ASC),
4817  INDEX `users_id` (`users_id` ASC),
4818  CONSTRAINT `req_users_access_ibfk_1`
4819    FOREIGN KEY (`users_id`)
4820    REFERENCES `users` (`users_id`)
4821    ON DELETE CASCADE,
4822  CONSTRAINT `req_users_access_ibfk_3`
4823    FOREIGN KEY (`users_id`)
4824    REFERENCES `users` (`users_id`)
4825    ON DELETE CASCADE)
4826ENGINE = InnoDB
4827DEFAULT CHARACTER SET = latin1;
4828
4829
4830-- -----------------------------------------------------
4831-- Table `requisition_attachment`
4832-- -----------------------------------------------------
4833CREATE TABLE IF NOT EXISTS `requisition_attachment` (
4834  `requisition_attachment_id` INT(11) NOT NULL AUTO_INCREMENT,
4835  `requisition_id` INT(11) NOT NULL,
4836  `attachment_type` VARCHAR(16) NULL DEFAULT NULL,
4837  `description` TEXT NULL DEFAULT NULL,
4838  `document` MEDIUMBLOB NULL DEFAULT NULL,
4839  `name` VARCHAR(100) NULL DEFAULT NULL,
4840  `type` VARCHAR(100) NULL DEFAULT NULL,
4841  `size` INT(11) NULL DEFAULT NULL,
4842  `create_date` DATETIME NULL DEFAULT NULL,
4843  PRIMARY KEY (`requisition_attachment_id`),
4844  INDEX `FK_requisition_attachment` (`requisition_id` ASC))
4845ENGINE = InnoDB
4846DEFAULT CHARACTER SET = latin1;
4847
4848
4849-- -----------------------------------------------------
4850-- Table `requisition_recommend`
4851-- -----------------------------------------------------
4852CREATE TABLE IF NOT EXISTS `requisition_recommend` (
4853  `requisition_id` INT(11) NOT NULL,
4854  `recommend_id` INT(11) NOT NULL,
4855  `sort_order` INT(11) NOT NULL,
4856  `effective_date` DATETIME NOT NULL)
4857ENGINE = InnoDB
4858DEFAULT CHARACTER SET = latin1;
4859
4860
4861-- -----------------------------------------------------
4862-- Table `requisition_repository`
4863-- -----------------------------------------------------
4864CREATE TABLE IF NOT EXISTS `requisition_repository` (
4865  `requisition_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
4866  `requisition_id` INT(20) NOT NULL DEFAULT '0',
4867  `users_id` INT(10) NOT NULL DEFAULT '0',
4868  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
4869  `ip_address` VARCHAR(64) NULL DEFAULT NULL,
4870  `session_info` VARCHAR(255) NULL DEFAULT NULL,
4871  `data` TEXT NULL DEFAULT NULL,
4872  PRIMARY KEY (`requisition_repository_id`),
4873  INDEX `requisition_id` (`requisition_id` ASC),
4874  CONSTRAINT `requisition_repository_ibfk_1`
4875    FOREIGN KEY (`requisition_id`)
4876    REFERENCES `requisition` (`requisition_id`)
4877    ON DELETE CASCADE
4878    ON UPDATE CASCADE)
4879ENGINE = InnoDB
4880AUTO_INCREMENT = 6409
4881DEFAULT CHARACTER SET = latin1;
4882
4883
4884-- -----------------------------------------------------
4885-- Table `residence`
4886-- -----------------------------------------------------
4887CREATE TABLE IF NOT EXISTS `residence` (
4888  `residence_id` INT(20) NOT NULL AUTO_INCREMENT,
4889  `candidate_id` INT(20) NULL DEFAULT NULL,
4890  `address1` VARCHAR(50) NULL DEFAULT NULL,
4891  `address2` VARCHAR(50) NULL DEFAULT NULL,
4892  `city` VARCHAR(100) NULL DEFAULT NULL,
4893  `state` CHAR(2) NULL DEFAULT NULL,
4894  `zip` VARCHAR(9) NULL DEFAULT NULL,
4895  `county` VARCHAR(50) NULL DEFAULT NULL,
4896  `country` VARCHAR(50) NULL DEFAULT NULL,
4897  `start_date` DATE NULL DEFAULT NULL,
4898  `end_date` DATE NULL DEFAULT NULL,
4899  `is_gap` CHAR(1) NULL DEFAULT NULL,
4900  `gap_explain` VARCHAR(255) NULL DEFAULT NULL,
4901  PRIMARY KEY (`residence_id`),
4902  INDEX `candidate_id` (`candidate_id` ASC))
4903ENGINE = InnoDB
4904AUTO_INCREMENT = 2
4905DEFAULT CHARACTER SET = latin1;
4906
4907
4908-- -----------------------------------------------------
4909-- Table `resume`
4910-- -----------------------------------------------------
4911CREATE TABLE IF NOT EXISTS `resume` (
4912  `resume_id` INT(11) NOT NULL AUTO_INCREMENT,
4913  `candidate_id` INT(11) NOT NULL DEFAULT '0',
4914  `name` VARCHAR(256) NULL DEFAULT NULL,
4915  `objective` TEXT NULL DEFAULT NULL,
4916  `other` TEXT NULL DEFAULT NULL,
4917  `resume` TEXT NULL DEFAULT NULL,
4918  `cover_letter` TEXT NULL DEFAULT NULL,
4919  PRIMARY KEY (`resume_id`),
4920  INDEX `candidate_ndx` (`candidate_id` ASC),
4921  CONSTRAINT `0_3343`
4922    FOREIGN KEY (`candidate_id`)
4923    REFERENCES `candidate_orig` (`candidate_id`)
4924    ON DELETE CASCADE
4925    ON UPDATE CASCADE)
4926ENGINE = InnoDB
4927DEFAULT CHARACTER SET = latin1;
4928
4929
4930-- -----------------------------------------------------
4931-- Table `resume_spy`
4932-- -----------------------------------------------------
4933CREATE TABLE IF NOT EXISTS `resume_spy` (
4934  `resume_spy_id` INT(20) NOT NULL AUTO_INCREMENT,
4935  `users_id` INT(20) NULL DEFAULT NULL,
4936  `name` VARCHAR(50) NULL DEFAULT NULL,
4937  `keywords` VARCHAR(255) NULL DEFAULT NULL,
4938  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
4939  `last_searched` DATETIME NULL DEFAULT NULL,
4940  `active` CHAR(1) NULL DEFAULT NULL,
4941  PRIMARY KEY (`resume_spy_id`),
4942  INDEX `users_id` (`users_id` ASC),
4943  CONSTRAINT `resume_spy_ibfk_1`
4944    FOREIGN KEY (`users_id`)
4945    REFERENCES `users` (`users_id`)
4946    ON DELETE CASCADE
4947    ON UPDATE CASCADE)
4948ENGINE = InnoDB
4949AUTO_INCREMENT = 23
4950DEFAULT CHARACTER SET = latin1;
4951
4952
4953-- -----------------------------------------------------
4954-- Table `resume_spy_sent`
4955-- -----------------------------------------------------
4956CREATE TABLE IF NOT EXISTS `resume_spy_sent` (
4957  `resume_spy_id` INT(20) NOT NULL DEFAULT '0',
4958  `candidate_id` INT(20) NOT NULL DEFAULT '0',
4959  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
4960  PRIMARY KEY (`resume_spy_id`, `candidate_id`),
4961  INDEX `candidate_id` (`candidate_id` ASC),
4962  CONSTRAINT `resume_spy_sent_ibfk_1`
4963    FOREIGN KEY (`candidate_id`)
4964    REFERENCES `candidate_orig` (`candidate_id`)
4965    ON DELETE CASCADE
4966    ON UPDATE CASCADE,
4967  CONSTRAINT `resume_spy_sent_ibfk_2`
4968    FOREIGN KEY (`resume_spy_id`)
4969    REFERENCES `resume_spy` (`resume_spy_id`)
4970    ON DELETE CASCADE
4971    ON UPDATE CASCADE)
4972ENGINE = InnoDB
4973DEFAULT CHARACTER SET = latin1;
4974
4975
4976-- -----------------------------------------------------
4977-- Table `rule`
4978-- -----------------------------------------------------
4979CREATE TABLE IF NOT EXISTS `rule` (
4980  `rule_id` INT(20) NOT NULL AUTO_INCREMENT,
4981  `name` CHAR(40) NOT NULL DEFAULT '',
4982  `rule` CHAR(40) NOT NULL DEFAULT '',
4983  PRIMARY KEY (`rule_id`))
4984ENGINE = InnoDB
4985AUTO_INCREMENT = 205
4986DEFAULT CHARACTER SET = latin1;
4987
4988
4989-- -----------------------------------------------------
4990-- Table `screen_pass`
4991-- -----------------------------------------------------
4992CREATE TABLE IF NOT EXISTS `screen_pass` (
4993  `view_group_id` INT(10) NULL DEFAULT NULL,
4994  `candidate_id` INT(20) NULL DEFAULT NULL,
4995  `pass` CHAR(1) NULL DEFAULT NULL,
4996  `date` DATETIME NULL DEFAULT NULL,
4997  INDEX `view_group_id` (`view_group_id` ASC),
4998  INDEX `candidate_id` (`candidate_id` ASC),
4999  CONSTRAINT `screen_pass_ibfk_1`
5000    FOREIGN KEY (`view_group_id`)
5001    REFERENCES `view_group` (`view_group_id`)
5002    ON DELETE CASCADE
5003    ON UPDATE CASCADE,
5004  CONSTRAINT `screen_pass_ibfk_2`
5005    FOREIGN KEY (`candidate_id`)
5006    REFERENCES `candidate_orig` (`candidate_id`)
5007    ON DELETE CASCADE
5008    ON UPDATE CASCADE)
5009ENGINE = InnoDB
5010DEFAULT CHARACTER SET = latin1;
5011
5012
5013-- -----------------------------------------------------
5014-- Table `security`
5015-- -----------------------------------------------------
5016CREATE TABLE IF NOT EXISTS `security` (
5017  `security_id` INT(20) NOT NULL AUTO_INCREMENT,
5018  `groups_id` INT(11) NOT NULL DEFAULT '0',
5019  `area` VARCHAR(100) NULL DEFAULT NULL,
5020  `rights` VARCHAR(100) NOT NULL DEFAULT '',
5021  PRIMARY KEY (`security_id`),
5022  INDEX `groups_ndx` (`groups_id` ASC),
5023  INDEX `area` (`area`(1) ASC, `rights` ASC),
5024  CONSTRAINT `0_3863`
5025    FOREIGN KEY (`groups_id`)
5026    REFERENCES `groups` (`groups_id`)
5027    ON DELETE CASCADE
5028    ON UPDATE CASCADE)
5029ENGINE = InnoDB
5030AUTO_INCREMENT = 326
5031DEFAULT CHARACTER SET = latin1;
5032
5033
5034-- -----------------------------------------------------
5035-- Table `security_attachment`
5036-- -----------------------------------------------------
5037CREATE TABLE IF NOT EXISTS `security_attachment` (
5038  `security_attachment_id` INT(11) NOT NULL AUTO_INCREMENT,
5039  `candidate_id` INT(11) NOT NULL DEFAULT '0',
5040  `users_id` INT(11) NULL DEFAULT '0',
5041  `name` VARCHAR(60) NULL DEFAULT NULL,
5042  `type` VARCHAR(30) NULL DEFAULT NULL,
5043  `description` VARCHAR(30) NULL DEFAULT NULL,
5044  `document` MEDIUMBLOB NULL DEFAULT NULL,
5045  `create_date` DATETIME NULL DEFAULT NULL,
5046  `attachment_type` VARCHAR(16) NULL DEFAULT NULL,
5047  PRIMARY KEY (`security_attachment_id`),
5048  INDEX `security_att_candidate_ndx` (`candidate_id` ASC),
5049  INDEX `users_id` (`users_id` ASC),
5050  CONSTRAINT `security_attachment_ibfk_2`
5051    FOREIGN KEY (`users_id`)
5052    REFERENCES `users` (`users_id`)
5053    ON DELETE CASCADE)
5054ENGINE = InnoDB
5055DEFAULT CHARACTER SET = latin1
5056COMMENT = 'InnoDB free: 9216 kB'
5057ROW_FORMAT = DYNAMIC;
5058
5059
5060-- -----------------------------------------------------
5061-- Table `security_log`
5062-- -----------------------------------------------------
5063CREATE TABLE IF NOT EXISTS `security_log` (
5064  `security_log_id` INT(20) NOT NULL AUTO_INCREMENT,
5065  `candidate_id` INT(20) NOT NULL DEFAULT '0',
5066  `users_id` INT(10) NULL DEFAULT NULL,
5067  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5068  `comment` VARCHAR(255) NULL DEFAULT NULL,
5069  `private` CHAR(1) NULL DEFAULT NULL,
5070  PRIMARY KEY (`security_log_id`),
5071  INDEX `candidate_id` (`candidate_id` ASC),
5072  INDEX `users_id` (`users_id` ASC),
5073  CONSTRAINT `security_log_ibfk_1`
5074    FOREIGN KEY (`candidate_id`)
5075    REFERENCES `candidate` (`candidate_id`)
5076    ON DELETE CASCADE
5077    ON UPDATE CASCADE,
5078  CONSTRAINT `security_log_ibfk_2`
5079    FOREIGN KEY (`users_id`)
5080    REFERENCES `users` (`users_id`)
5081    ON DELETE SET NULL
5082    ON UPDATE CASCADE)
5083ENGINE = InnoDB
5084AUTO_INCREMENT = 1202
5085DEFAULT CHARACTER SET = latin1;
5086
5087
5088-- -----------------------------------------------------
5089-- Table `security_status`
5090-- -----------------------------------------------------
5091CREATE TABLE IF NOT EXISTS `security_status` (
5092  `security_status_id` INT(10) NOT NULL AUTO_INCREMENT,
5093  `application_id` INT(20) NOT NULL DEFAULT '0',
5094  `status` VARCHAR(50) NULL DEFAULT NULL,
5095  `status_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
5096  `comments` VARCHAR(255) NULL DEFAULT NULL,
5097  `users_id` INT(10) NULL DEFAULT NULL,
5098  `originating_user` INT(10) NULL DEFAULT NULL,
5099  PRIMARY KEY (`security_status_id`),
5100  INDEX `application_id` (`application_id` ASC),
5101  INDEX `users_id` (`users_id` ASC),
5102  INDEX `originating_user` (`originating_user` ASC),
5103  CONSTRAINT `security_status_ibfk_1`
5104    FOREIGN KEY (`application_id`)
5105    REFERENCES `application` (`application_id`)
5106    ON DELETE CASCADE
5107    ON UPDATE CASCADE,
5108  CONSTRAINT `security_status_ibfk_2`
5109    FOREIGN KEY (`users_id`)
5110    REFERENCES `users` (`users_id`)
5111    ON DELETE SET NULL
5112    ON UPDATE CASCADE,
5113  CONSTRAINT `security_status_ibfk_3`
5114    FOREIGN KEY (`originating_user`)
5115    REFERENCES `users` (`users_id`)
5116    ON DELETE CASCADE
5117    ON UPDATE CASCADE)
5118ENGINE = InnoDB
5119AUTO_INCREMENT = 235
5120DEFAULT CHARACTER SET = latin1;
5121
5122
5123-- -----------------------------------------------------
5124-- Table `sendgrid_details`
5125-- -----------------------------------------------------
5126CREATE TABLE IF NOT EXISTS `sendgrid_details` (
5127  `sendgrid_id` INT(11) NOT NULL AUTO_INCREMENT,
5128  `communication_id_fk` INT(11) NOT NULL,
5129  `sendgrid_processedEvent` CHAR(1) NULL DEFAULT 'f',
5130  `sendgrid_deliveredEvent` CHAR(1) NULL DEFAULT 'f',
5131  `sendgrid_openEvent` CHAR(1) NULL DEFAULT 'f',
5132  `sendgrid_unsubscribeEvent` CHAR(1) NULL DEFAULT 'f',
5133  `sendgrid_processedDateTime` TIMESTAMP NULL DEFAULT NULL,
5134  `sendgrid_deliveredDateTime` TIMESTAMP NULL DEFAULT NULL,
5135  `sendgrid_openDateTime` TIMESTAMP NULL DEFAULT NULL,
5136  `sendgrid_unsubscribeDateTime` TIMESTAMP NULL DEFAULT NULL,
5137  PRIMARY KEY (`sendgrid_id`),
5138  INDEX `communication_id_fk` (`communication_id_fk` ASC),
5139  CONSTRAINT `sendgrid_details_ibfk_2`
5140    FOREIGN KEY (`communication_id_fk`)
5141    REFERENCES `communication_log` (`communication_log_id`)
5142    ON DELETE CASCADE
5143    ON UPDATE CASCADE)
5144ENGINE = InnoDB
5145AUTO_INCREMENT = 43
5146DEFAULT CHARACTER SET = utf8;
5147
5148
5149-- -----------------------------------------------------
5150-- Table `sendgrid_event`
5151-- -----------------------------------------------------
5152CREATE TABLE IF NOT EXISTS `sendgrid_event` (
5153  `sendgrid_event_id` INT(20) NOT NULL AUTO_INCREMENT,
5154  `communication_log_id` INT(20) NOT NULL,
5155  `event` VARCHAR(64) NOT NULL,
5156  `event_id` VARCHAR(128) NOT NULL,
5157  `message_id` VARCHAR(128) NOT NULL,
5158  `event_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
5159  `email` VARCHAR(128) NOT NULL,
5160  `smtp_id` VARCHAR(128) NOT NULL,
5161  `ip_address` VARCHAR(32) NOT NULL,
5162  `response` VARCHAR(128) NOT NULL,
5163  `tls` VARCHAR(1) NOT NULL,
5164  `reason` TEXT NULL DEFAULT NULL,
5165  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5166  PRIMARY KEY (`sendgrid_event_id`),
5167  UNIQUE INDEX `event_id` (`event_id` ASC),
5168  INDEX `se_clid_fk` (`communication_log_id` ASC),
5169  CONSTRAINT `se_clid_fk`
5170    FOREIGN KEY (`communication_log_id`)
5171    REFERENCES `communication_log` (`communication_log_id`)
5172    ON DELETE CASCADE
5173    ON UPDATE CASCADE)
5174ENGINE = InnoDB
5175DEFAULT CHARACTER SET = latin1;
5176
5177
5178-- -----------------------------------------------------
5179-- Table `skill_category`
5180-- -----------------------------------------------------
5181CREATE TABLE IF NOT EXISTS `skill_category` (
5182  `skill_category_id` INT(20) NOT NULL AUTO_INCREMENT,
5183  `name` CHAR(40) NULL DEFAULT NULL,
5184  PRIMARY KEY (`skill_category_id`))
5185ENGINE = InnoDB
5186AUTO_INCREMENT = 68
5187DEFAULT CHARACTER SET = latin1;
5188
5189
5190-- -----------------------------------------------------
5191-- Table `skill_detail`
5192-- -----------------------------------------------------
5193CREATE TABLE IF NOT EXISTS `skill_detail` (
5194  `skill_detail_id` INT(20) NOT NULL AUTO_INCREMENT,
5195  `skill_category_id` INT(20) NOT NULL DEFAULT '0',
5196  `skill` CHAR(40) NULL DEFAULT NULL,
5197  `level` CHAR(40) NULL DEFAULT NULL,
5198  `last_used_list` CHAR(40) NULL DEFAULT NULL,
5199  `experience_list` CHAR(40) NULL DEFAULT NULL,
5200  PRIMARY KEY (`skill_detail_id`),
5201  INDEX `app_can_asso_ndx` (`skill_category_id` ASC),
5202  CONSTRAINT `0_2366`
5203    FOREIGN KEY (`skill_category_id`)
5204    REFERENCES `skill_category` (`skill_category_id`)
5205    ON DELETE CASCADE
5206    ON UPDATE CASCADE)
5207ENGINE = InnoDB
5208AUTO_INCREMENT = 233
5209DEFAULT CHARACTER SET = latin1;
5210
5211
5212-- -----------------------------------------------------
5213-- Table `skill`
5214-- -----------------------------------------------------
5215CREATE TABLE IF NOT EXISTS `skill` (
5216  `skill_id` INT(20) NOT NULL AUTO_INCREMENT,
5217  `candidate_id` INT(20) NOT NULL DEFAULT '0',
5218  `skill_detail_id` INT(20) NULL DEFAULT NULL,
5219  `users_id` INT(20) NULL DEFAULT NULL,
5220  `last_used` DECIMAL(3,0) NULL DEFAULT NULL,
5221  `experience` DECIMAL(3,0) NULL DEFAULT NULL,
5222  `level` INT(11) NULL DEFAULT NULL,
5223  PRIMARY KEY (`skill_id`),
5224  INDEX `sk_can_asso_ndx` (`candidate_id` ASC),
5225  INDEX `sk_sk_det_asso_ndx` (`skill_detail_id` ASC),
5226  INDEX `skill_detail_id_experience` (`skill_detail_id` ASC, `experience` ASC),
5227  INDEX `skill_detail_id_level` (`skill_detail_id` ASC, `level` ASC),
5228  CONSTRAINT `0_2363`
5229    FOREIGN KEY (`candidate_id`)
5230    REFERENCES `candidate_orig` (`candidate_id`)
5231    ON DELETE CASCADE
5232    ON UPDATE CASCADE,
5233  CONSTRAINT `0_2364`
5234    FOREIGN KEY (`skill_detail_id`)
5235    REFERENCES `skill_detail` (`skill_detail_id`)
5236    ON DELETE CASCADE
5237    ON UPDATE CASCADE)
5238ENGINE = InnoDB
5239AUTO_INCREMENT = 362
5240DEFAULT CHARACTER SET = latin1;
5241
5242
5243-- -----------------------------------------------------
5244-- Table `socialchannel`
5245-- -----------------------------------------------------
5246CREATE TABLE IF NOT EXISTS `socialchannel` (
5247  `socialchannel_id` INT(20) NOT NULL AUTO_INCREMENT,
5248  `socialchannel` VARCHAR(50) NULL DEFAULT NULL,
5249  PRIMARY KEY (`socialchannel_id`),
5250  INDEX `socialchannel` (`socialchannel` ASC))
5251ENGINE = InnoDB
5252DEFAULT CHARACTER SET = latin1;
5253
5254
5255-- -----------------------------------------------------
5256-- Table `socialchannel_config`
5257-- -----------------------------------------------------
5258CREATE TABLE IF NOT EXISTS `socialchannel_config` (
5259  `socialchannel_config_id` INT(20) NOT NULL AUTO_INCREMENT,
5260  `socialchannel_id` INT(20) NOT NULL,
5261  `candidate_id` INT(20) NOT NULL,
5262  `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5263  `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5264  PRIMARY KEY (`socialchannel_config_id`),
5265  INDEX `so_coid_fk` (`candidate_id` ASC),
5266  INDEX `so_coid_fk2` (`socialchannel_id` ASC),
5267  CONSTRAINT `so_coid_fk`
5268    FOREIGN KEY (`candidate_id`)
5269    REFERENCES `candidate` (`candidate_id`)
5270    ON DELETE CASCADE
5271    ON UPDATE CASCADE,
5272  CONSTRAINT `so_coid_fk2`
5273    FOREIGN KEY (`socialchannel_id`)
5274    REFERENCES `socialchannel` (`socialchannel_id`)
5275    ON DELETE CASCADE
5276    ON UPDATE CASCADE)
5277ENGINE = InnoDB
5278DEFAULT CHARACTER SET = latin1;
5279
5280
5281-- -----------------------------------------------------
5282-- Table `spy_sent`
5283-- -----------------------------------------------------
5284CREATE TABLE IF NOT EXISTS `spy_sent` (
5285  `job_spy_id` INT(20) NULL DEFAULT NULL,
5286  `requisition_id` INT(20) NULL DEFAULT NULL,
5287  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
5288  INDEX `job_spy_id` (`job_spy_id` ASC),
5289  INDEX `requisition_id` (`requisition_id` ASC),
5290  CONSTRAINT `spy_sent_ibfk_1`
5291    FOREIGN KEY (`job_spy_id`)
5292    REFERENCES `job_spy` (`job_spy_id`)
5293    ON DELETE CASCADE
5294    ON UPDATE CASCADE,
5295  CONSTRAINT `spy_sent_ibfk_2`
5296    FOREIGN KEY (`requisition_id`)
5297    REFERENCES `requisition_orig` (`requisition_id`)
5298    ON DELETE CASCADE
5299    ON UPDATE CASCADE)
5300ENGINE = InnoDB
5301DEFAULT CHARACTER SET = latin1;
5302
5303
5304-- -----------------------------------------------------
5305-- Table `step`
5306-- -----------------------------------------------------
5307CREATE TABLE IF NOT EXISTS `step` (
5308  `step_id` INT(20) NOT NULL AUTO_INCREMENT,
5309  `application_id` INT(20) NOT NULL DEFAULT '0',
5310  `name` VARCHAR(40) NULL DEFAULT NULL,
5311  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
5312  `comment` VARCHAR(100) NULL DEFAULT NULL,
5313  `user_id` INT(10) NULL DEFAULT NULL,
5314  PRIMARY KEY (`step_id`),
5315  INDEX `steip_app_asso_ndx` (`application_id` ASC),
5316  INDEX `date` (`date` ASC),
5317  CONSTRAINT `0_3364`
5318    FOREIGN KEY (`application_id`)
5319    REFERENCES `application` (`application_id`)
5320    ON DELETE CASCADE
5321    ON UPDATE CASCADE)
5322ENGINE = InnoDB
5323AUTO_INCREMENT = 2477357
5324DEFAULT CHARACTER SET = latin1;
5325
5326
5327-- -----------------------------------------------------
5328-- Table `step_group`
5329-- -----------------------------------------------------
5330CREATE TABLE IF NOT EXISTS `step_group` (
5331  `step_group_id` INT(11) NOT NULL AUTO_INCREMENT,
5332  `name` VARCHAR(50) NULL DEFAULT NULL,
5333  `rule_types` VARCHAR(50) NULL DEFAULT NULL,
5334  `role_override` VARCHAR(200) NULL DEFAULT NULL,
5335  `sort_order` INT(11) NULL DEFAULT NULL,
5336  PRIMARY KEY (`step_group_id`))
5337ENGINE = InnoDB
5338AUTO_INCREMENT = 14
5339DEFAULT CHARACTER SET = latin1;
5340
5341
5342-- -----------------------------------------------------
5343-- Table `step_group_detail`
5344-- -----------------------------------------------------
5345CREATE TABLE IF NOT EXISTS `step_group_detail` (
5346  `step_group_detail_id` INT(11) NOT NULL AUTO_INCREMENT,
5347  `list_id` INT(11) NULL DEFAULT NULL,
5348  `step_group_id` INT(11) NULL DEFAULT NULL,
5349  `sort_order` INT(11) NULL DEFAULT NULL,
5350  PRIMARY KEY (`step_group_detail_id`),
5351  UNIQUE INDEX `step_group_id` (`step_group_id` ASC, `list_id` ASC))
5352ENGINE = InnoDB
5353AUTO_INCREMENT = 77
5354DEFAULT CHARACTER SET = latin1;
5355
5356
5357-- -----------------------------------------------------
5358-- Table `task`
5359-- -----------------------------------------------------
5360CREATE TABLE IF NOT EXISTS `task` (
5361  `task_id` INT(20) NOT NULL AUTO_INCREMENT,
5362  `users_id` INT(10) NOT NULL,
5363  `candidate_id` INT(20) NULL DEFAULT NULL,
5364  `subject` VARCHAR(255) NULL DEFAULT NULL,
5365  `due_date` DATETIME NULL DEFAULT NULL,
5366  `acknowledged` CHAR(1) NULL DEFAULT NULL,
5367  `create_date` TIMESTAMP NULL DEFAULT NULL,
5368  PRIMARY KEY (`task_id`),
5369  INDEX `FK_task` (`candidate_id` ASC),
5370  INDEX `FK_task1` (`users_id` ASC),
5371  CONSTRAINT `task_ibfk_1`
5372    FOREIGN KEY (`candidate_id`)
5373    REFERENCES `candidate_orig` (`candidate_id`)
5374    ON DELETE CASCADE
5375    ON UPDATE CASCADE,
5376  CONSTRAINT `task_ibfk_2`
5377    FOREIGN KEY (`users_id`)
5378    REFERENCES `users` (`users_id`)
5379    ON DELETE CASCADE
5380    ON UPDATE CASCADE)
5381ENGINE = InnoDB
5382AUTO_INCREMENT = 59
5383DEFAULT CHARACTER SET = latin1;
5384
5385
5386-- -----------------------------------------------------
5387-- Table `tp_background_order`
5388-- -----------------------------------------------------
5389CREATE TABLE IF NOT EXISTS `tp_background_order` (
5390  `tp_background_order_id` INT(20) NOT NULL AUTO_INCREMENT,
5391  `vendor_id` VARCHAR(50) NULL DEFAULT NULL,
5392  `users_id` INT(20) NOT NULL DEFAULT '0',
5393  `candidate_id` INT(20) NOT NULL DEFAULT '0',
5394  `package_id` VARCHAR(75) NULL DEFAULT NULL,
5395  `product_id` VARCHAR(75) NULL DEFAULT NULL,
5396  `order_id` VARCHAR(75) NULL DEFAULT NULL,
5397  `order_date` DATE NULL DEFAULT NULL,
5398  `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5399  `status` VARCHAR(50) NULL DEFAULT NULL,
5400  `result_available` CHAR(1) NULL DEFAULT 'f',
5401  `result_url` VARCHAR(255) NULL DEFAULT NULL,
5402  `result_text` TEXT NULL DEFAULT NULL,
5403  `complete` CHAR(1) NULL DEFAULT NULL,
5404  `current` CHAR(1) NULL DEFAULT NULL,
5405  `custom1` VARCHAR(255) NULL DEFAULT NULL,
5406  PRIMARY KEY (`tp_background_order_id`),
5407  INDEX `candidate_id` (`candidate_id` ASC),
5408  INDEX `users_id` (`users_id` ASC))
5409ENGINE = InnoDB
5410AUTO_INCREMENT = 225
5411DEFAULT CHARACTER SET = latin1;
5412
5413
5414-- -----------------------------------------------------
5415-- Table `tp_background_package`
5416-- -----------------------------------------------------
5417CREATE TABLE IF NOT EXISTS `tp_background_package` (
5418  `tp_background_package_id` INT(11) NOT NULL AUTO_INCREMENT,
5419  `vendor` VARCHAR(60) NULL DEFAULT NULL,
5420  `name` VARCHAR(200) NULL DEFAULT NULL,
5421  `code` VARCHAR(60) NULL DEFAULT NULL,
5422  PRIMARY KEY (`tp_background_package_id`))
5423ENGINE = InnoDB
5424AUTO_INCREMENT = 9
5425DEFAULT CHARACTER SET = latin1;
5426
5427
5428-- -----------------------------------------------------
5429-- Table `tp_background_package_detail`
5430-- -----------------------------------------------------
5431CREATE TABLE IF NOT EXISTS `tp_background_package_detail` (
5432  `tp_background_package_detail_id` INT(11) NOT NULL AUTO_INCREMENT,
5433  `tp_background_package_id` INT(11) NOT NULL,
5434  `tp_background_screening_id` INT(11) NOT NULL,
5435  PRIMARY KEY (`tp_background_package_detail_id`))
5436ENGINE = InnoDB
5437DEFAULT CHARACTER SET = latin1;
5438
5439
5440-- -----------------------------------------------------
5441-- Table `tp_background_screening`
5442-- -----------------------------------------------------
5443CREATE TABLE IF NOT EXISTS `tp_background_screening` (
5444  `tp_background_screening_id` INT(11) NOT NULL AUTO_INCREMENT,
5445  `name` VARCHAR(60) NULL DEFAULT NULL,
5446  `type` VARCHAR(60) NULL DEFAULT NULL,
5447  `qualifier` VARCHAR(60) NULL DEFAULT NULL,
5448  `subqualifier` VARCHAR(60) NULL DEFAULT NULL,
5449  `description` VARCHAR(200) NULL DEFAULT NULL,
5450  PRIMARY KEY (`tp_background_screening_id`))
5451ENGINE = InnoDB
5452AUTO_INCREMENT = 3
5453DEFAULT CHARACTER SET = latin1;
5454
5455
5456-- -----------------------------------------------------
5457-- Table `tp_candidate`
5458-- -----------------------------------------------------
5459CREATE TABLE IF NOT EXISTS `tp_candidate` (
5460  `tp_candidate_id` INT(11) NOT NULL AUTO_INCREMENT,
5461  `candidate_id` INT(11) NOT NULL,
5462  `object_id` INT(11) NULL DEFAULT NULL,
5463  `tp_vendor_id` INT(11) NOT NULL,
5464  `category` VARCHAR(20) NULL DEFAULT NULL,
5465  `status` VARCHAR(10) NULL DEFAULT NULL,
5466  `vendor_key` VARCHAR(20) NULL DEFAULT NULL,
5467  `users_id` INT(11) NULL DEFAULT NULL,
5468  `create_date` DATETIME NOT NULL,
5469  `update_date` DATETIME NOT NULL,
5470  `t1` VARCHAR(20) NULL DEFAULT NULL,
5471  `t2` VARCHAR(20) NULL DEFAULT NULL,
5472  `t3` VARCHAR(20) NULL DEFAULT NULL,
5473  `d1` DATETIME NULL DEFAULT NULL,
5474  `d2` DATETIME NULL DEFAULT NULL,
5475  `d3` DATETIME NULL DEFAULT NULL,
5476  PRIMARY KEY (`tp_candidate_id`),
5477  INDEX `vendor_candidate_category` (`tp_vendor_id` ASC, `candidate_id` ASC, `category` ASC),
5478  INDEX `vendor_object` (`tp_vendor_id` ASC, `object_id` ASC),
5479  INDEX `vendor_vendor_key` (`tp_vendor_id` ASC, `vendor_key` ASC),
5480  INDEX `vendor_status` (`tp_vendor_id` ASC, `status` ASC),
5481  INDEX `vendor_category` (`tp_vendor_id` ASC, `category` ASC))
5482ENGINE = InnoDB
5483AUTO_INCREMENT = 963
5484DEFAULT CHARACTER SET = latin1;
5485
5486
5487-- -----------------------------------------------------
5488-- Table `tp_i9verification`
5489-- -----------------------------------------------------
5490CREATE TABLE IF NOT EXISTS `tp_i9verification` (
5491  `tp_i9verification_id` INT(11) NOT NULL AUTO_INCREMENT,
5492  `candidate_id` INT(11) NOT NULL,
5493  `vendor` VARCHAR(32) NOT NULL,
5494  `vendor_data` TEXT NOT NULL,
5495  `status` VARCHAR(32) NOT NULL,
5496  PRIMARY KEY (`tp_i9verification_id`),
5497  INDEX `FK_tp_i9verification` (`candidate_id` ASC),
5498  CONSTRAINT `FK_tp_i9verification`
5499    FOREIGN KEY (`candidate_id`)
5500    REFERENCES `candidate_orig` (`candidate_id`)
5501    ON DELETE CASCADE
5502    ON UPDATE CASCADE)
5503ENGINE = InnoDB
5504AUTO_INCREMENT = 41
5505DEFAULT CHARACTER SET = latin1;
5506
5507
5508-- -----------------------------------------------------
5509-- Table `tp_vendor`
5510-- -----------------------------------------------------
5511CREATE TABLE IF NOT EXISTS `tp_vendor` (
5512  `tp_vendor_id` INT(11) NOT NULL AUTO_INCREMENT,
5513  `name` VARCHAR(30) NULL DEFAULT NULL,
5514  `type` VARCHAR(30) NULL DEFAULT NULL,
5515  `active` CHAR(1) NULL DEFAULT NULL,
5516  PRIMARY KEY (`tp_vendor_id`),
5517  INDEX `type` (`type` ASC))
5518ENGINE = InnoDB
5519AUTO_INCREMENT = 20
5520DEFAULT CHARACTER SET = latin1;
5521
5522
5523-- -----------------------------------------------------
5524-- Table `tp_vendor_users`
5525-- -----------------------------------------------------
5526CREATE TABLE IF NOT EXISTS `tp_vendor_users` (
5527  `tp_vendor_id` INT(11) NOT NULL DEFAULT '0',
5528  `users_id` INT(11) NOT NULL DEFAULT '0',
5529  `uname` VARCHAR(250) NULL DEFAULT NULL,
5530  `password` VARCHAR(40) NULL DEFAULT NULL,
5531  `account` VARCHAR(30) NULL DEFAULT NULL,
5532  `cost_center` VARCHAR(30) NULL DEFAULT NULL,
5533  `active` CHAR(1) NULL DEFAULT NULL,
5534  PRIMARY KEY (`tp_vendor_id`, `users_id`),
5535  INDEX `FK_tp_vendor_users_users` (`users_id` ASC),
5536  INDEX `FK_tp_vendor_users` (`tp_vendor_id` ASC),
5537  CONSTRAINT `FK_tp_vendor_users`
5538    FOREIGN KEY (`tp_vendor_id`)
5539    REFERENCES `tp_vendor` (`tp_vendor_id`)
5540    ON DELETE CASCADE
5541    ON UPDATE CASCADE,
5542  CONSTRAINT `FK_tp_vendor_users_users`
5543    FOREIGN KEY (`users_id`)
5544    REFERENCES `users` (`users_id`)
5545    ON DELETE CASCADE
5546    ON UPDATE CASCADE)
5547ENGINE = InnoDB
5548DEFAULT CHARACTER SET = latin1;
5549
5550
5551-- -----------------------------------------------------
5552-- Table `transaction_log`
5553-- -----------------------------------------------------
5554CREATE TABLE IF NOT EXISTS `transaction_log` (
5555  `transaction_log_id` INT(11) NOT NULL AUTO_INCREMENT,
5556  `candidate_id` INT(11) NOT NULL DEFAULT '0',
5557  `users_id` INT(11) NOT NULL DEFAULT '0',
5558  `comment` VARCHAR(1024) NULL DEFAULT NULL,
5559  `private` CHAR(1) NULL DEFAULT NULL,
5560  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5561  PRIMARY KEY (`transaction_log_id`),
5562  INDEX `dr_candidate_ndx` (`candidate_id` ASC),
5563  INDEX `dr_users_ndx` (`users_id` ASC),
5564  CONSTRAINT `0_2448`
5565    FOREIGN KEY (`users_id`)
5566    REFERENCES `users` (`users_id`)
5567    ON DELETE CASCADE
5568    ON UPDATE CASCADE)
5569ENGINE = InnoDB
5570AUTO_INCREMENT = 28574
5571DEFAULT CHARACTER SET = latin1;
5572
5573
5574-- -----------------------------------------------------
5575-- Table `transition_rule`
5576-- -----------------------------------------------------
5577CREATE TABLE IF NOT EXISTS `transition_rule` (
5578  `transition_id` INT(11) NOT NULL DEFAULT '0',
5579  `rule_id` INT(11) NOT NULL DEFAULT '0',
5580  `priority` INT(11) NOT NULL DEFAULT '0',
5581  `next` INT(11) NOT NULL DEFAULT '0',
5582  PRIMARY KEY (`transition_id`, `rule_id`),
5583  INDEX `transition_asso_ndx` (`transition_id` ASC),
5584  INDEX `rule_asso_ndx` (`rule_id` ASC),
5585  CONSTRAINT `0_1318`
5586    FOREIGN KEY (`transition_id`)
5587    REFERENCES `transition` (`transition_id`)
5588    ON DELETE CASCADE
5589    ON UPDATE CASCADE,
5590  CONSTRAINT `0_1319`
5591    FOREIGN KEY (`rule_id`)
5592    REFERENCES `rule` (`rule_id`)
5593    ON DELETE CASCADE
5594    ON UPDATE CASCADE)
5595ENGINE = InnoDB
5596DEFAULT CHARACTER SET = latin1;
5597
5598
5599-- -----------------------------------------------------
5600-- Table `user views`
5601-- -----------------------------------------------------
5602CREATE TABLE IF NOT EXISTS `user views` (
5603  `users_id` INT(10) NULL DEFAULT NULL,
5604  `candidate_id` INT(20) NULL DEFAULT NULL,
5605  `date` TIMESTAMP NULL DEFAULT NULL,
5606  INDEX `FK_user_views` (`users_id` ASC))
5607ENGINE = InnoDB
5608DEFAULT CHARACTER SET = latin1;
5609
5610
5611-- -----------------------------------------------------
5612-- Table `user_logins`
5613-- -----------------------------------------------------
5614CREATE TABLE IF NOT EXISTS `user_logins` (
5615  `users_id` INT(10) NULL DEFAULT NULL,
5616  `ip_address` VARCHAR(64) NULL DEFAULT NULL,
5617  `success` CHAR(1) NULL DEFAULT NULL COMMENT 'Was the login successful',
5618  `fail_entry` VARCHAR(50) NULL DEFAULT NULL COMMENT 'On fail, what was entered',
5619  `date` TIMESTAMP NULL DEFAULT NULL,
5620  `passphrase` VARCHAR(20) NULL DEFAULT NULL,
5621  INDEX `FK_user_logins` (`users_id` ASC),
5622  INDEX `data_users_login_ndxt` (`date` ASC),
5623  CONSTRAINT `user_logins_ibfk_1`
5624    FOREIGN KEY (`users_id`)
5625    REFERENCES `users` (`users_id`)
5626    ON DELETE CASCADE
5627    ON UPDATE CASCADE)
5628ENGINE = InnoDB
5629DEFAULT CHARACTER SET = latin1;
5630
5631
5632-- -----------------------------------------------------
5633-- Table `user_roles`
5634-- -----------------------------------------------------
5635CREATE TABLE IF NOT EXISTS `user_roles` (
5636  `users_id` INT(20) NOT NULL,
5637  `role_id` INT(5) NOT NULL,
5638  INDEX `ur_groupsId` (`users_id` ASC),
5639  INDEX `ur_roleId` (`role_id` ASC),
5640  CONSTRAINT `ur_groupsId`
5641    FOREIGN KEY (`users_id`)
5642    REFERENCES `users` (`users_id`)
5643    ON DELETE CASCADE
5644    ON UPDATE CASCADE,
5645  CONSTRAINT `ur_roleId`
5646    FOREIGN KEY (`role_id`)
5647    REFERENCES `roles` (`role_id`)
5648    ON DELETE CASCADE
5649    ON UPDATE CASCADE)
5650ENGINE = InnoDB
5651DEFAULT CHARACTER SET = latin1;
5652
5653
5654-- -----------------------------------------------------
5655-- Table `user_views`
5656-- -----------------------------------------------------
5657CREATE TABLE IF NOT EXISTS `user_views` (
5658  `users_id` INT(10) NULL DEFAULT NULL,
5659  `candidate_id` INT(20) NULL DEFAULT NULL,
5660  `date` TIMESTAMP NULL DEFAULT NULL,
5661  INDEX `FK_user_views` (`users_id` ASC),
5662  INDEX `candidate_id` (`users_id` ASC, `candidate_id` ASC),
5663  CONSTRAINT `user_views_ibfk_1`
5664    FOREIGN KEY (`users_id`)
5665    REFERENCES `users` (`users_id`)
5666    ON DELETE CASCADE
5667    ON UPDATE CASCADE)
5668ENGINE = InnoDB
5669DEFAULT CHARACTER SET = latin1;
5670
5671
5672-- -----------------------------------------------------
5673-- Table `view_complete`
5674-- -----------------------------------------------------
5675CREATE TABLE IF NOT EXISTS `view_complete` (
5676  `candidate_id` INT(20) NOT NULL DEFAULT '0',
5677  `page_info_id` INT(20) NOT NULL DEFAULT '0',
5678  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5679  PRIMARY KEY (`candidate_id`, `page_info_id`),
5680  INDEX `dr_piid_ndx` (`page_info_id` ASC),
5681  INDEX `dr_vgid_ndx` (`candidate_id` ASC),
5682  CONSTRAINT `0_3398`
5683    FOREIGN KEY (`page_info_id`)
5684    REFERENCES `page_info` (`page_info_id`)
5685    ON DELETE CASCADE
5686    ON UPDATE CASCADE,
5687  CONSTRAINT `0_3399`
5688    FOREIGN KEY (`candidate_id`)
5689    REFERENCES `candidate_orig` (`candidate_id`)
5690    ON DELETE CASCADE
5691    ON UPDATE CASCADE)
5692ENGINE = InnoDB
5693DEFAULT CHARACTER SET = latin1;
5694
5695
5696-- -----------------------------------------------------
5697-- Table `view_element_i18n`
5698-- -----------------------------------------------------
5699CREATE TABLE IF NOT EXISTS `view_element_i18n` (
5700  `view_element_i18n_id` INT(11) NOT NULL AUTO_INCREMENT,
5701  `view_element_id` INT(11) NOT NULL,
5702  `locale` VARCHAR(10) NULL DEFAULT NULL,
5703  `props` TEXT NULL DEFAULT NULL,
5704  PRIMARY KEY (`view_element_i18n_id`),
5705  INDEX `FK_view_element_i18n_view_element_id` (`view_element_id` ASC))
5706ENGINE = InnoDB
5707AUTO_INCREMENT = 5
5708DEFAULT CHARACTER SET = latin1;
5709
5710
5711-- -----------------------------------------------------
5712-- Table `view_group_detail`
5713-- -----------------------------------------------------
5714CREATE TABLE IF NOT EXISTS `view_group_detail` (
5715  `view_group_id` INT(11) NOT NULL DEFAULT '0',
5716  `page_info_id` INT(11) NOT NULL DEFAULT '0',
5717  `sort_order` INT(5) NOT NULL DEFAULT '0',
5718  PRIMARY KEY (`view_group_id`, `page_info_id`),
5719  INDEX `dr_piid_ndx` (`page_info_id` ASC),
5720  INDEX `dr_vgid_ndx` (`view_group_id` ASC),
5721  CONSTRAINT `0_3405`
5722    FOREIGN KEY (`page_info_id`)
5723    REFERENCES `page_info` (`page_info_id`)
5724    ON DELETE CASCADE
5725    ON UPDATE CASCADE,
5726  CONSTRAINT `0_3406`
5727    FOREIGN KEY (`view_group_id`)
5728    REFERENCES `view_group` (`view_group_id`)
5729    ON DELETE CASCADE
5730    ON UPDATE CASCADE)
5731ENGINE = InnoDB
5732DEFAULT CHARACTER SET = latin1;
5733
5734
5735-- -----------------------------------------------------
5736-- Table `wf_log`
5737-- -----------------------------------------------------
5738CREATE TABLE IF NOT EXISTS `wf_log` (
5739  `wf_log_id` INT(20) NOT NULL AUTO_INCREMENT,
5740  `token` BLOB NULL DEFAULT NULL,
5741  PRIMARY KEY (`wf_log_id`))
5742ENGINE = InnoDB
5743AUTO_INCREMENT = 1668
5744DEFAULT CHARACTER SET = latin1;
5745
5746
5747-- -----------------------------------------------------
5748-- Table `work_history`
5749-- -----------------------------------------------------
5750CREATE TABLE IF NOT EXISTS `work_history` (
5751  `work_history_id` INT(11) NOT NULL AUTO_INCREMENT,
5752  `candidate_id` INT(11) NOT NULL DEFAULT '0',
5753  `company` VARCHAR(80) NULL DEFAULT NULL,
5754  `address1` VARCHAR(50) NULL DEFAULT NULL,
5755  `address2` VARCHAR(50) NULL DEFAULT NULL,
5756  `city` VARCHAR(50) NULL DEFAULT NULL,
5757  `state` CHAR(2) NULL DEFAULT NULL,
5758  `zip` VARCHAR(9) NULL DEFAULT NULL,
5759  `start_date` DATE NULL DEFAULT NULL,
5760  `end_date` DATE NULL DEFAULT NULL,
5761  `current` CHAR(1) NULL DEFAULT NULL,
5762  `title` VARCHAR(50) NULL DEFAULT NULL,
5763  `description` VARCHAR(500) NULL DEFAULT NULL,
5764  `manager_name` VARCHAR(50) NULL DEFAULT NULL,
5765  `manager_phone` VARCHAR(50) NULL DEFAULT NULL,
5766  `contact` CHAR(1) NULL DEFAULT NULL,
5767  `ref_checked` CHAR(1) NULL DEFAULT NULL,
5768  `reason` VARCHAR(500) NULL DEFAULT NULL,
5769  `is_gap` CHAR(1) NULL DEFAULT NULL,
5770  `gap_explain` VARCHAR(255) NULL DEFAULT NULL,
5771  `start_salary` VARCHAR(10) NULL DEFAULT NULL,
5772  `end_salary` VARCHAR(10) NULL DEFAULT NULL,
5773  `start_salary_type` VARCHAR(10) NULL DEFAULT NULL,
5774  `end_salary_type` VARCHAR(10) NULL DEFAULT NULL,
5775  `reviewed` CHAR(1) NULL DEFAULT NULL,
5776  PRIMARY KEY (`work_history_id`),
5777  INDEX `candidate_ndx` (`candidate_id` ASC),
5778  CONSTRAINT `0_3347`
5779    FOREIGN KEY (`candidate_id`)
5780    REFERENCES `candidate` (`candidate_id`)
5781    ON DELETE CASCADE
5782    ON UPDATE CASCADE)
5783ENGINE = InnoDB
5784AUTO_INCREMENT = 1962639
5785DEFAULT CHARACTER SET = latin1;
5786
5787
5788-- -----------------------------------------------------
5789-- Table `workhistory_repository`
5790-- -----------------------------------------------------
5791CREATE TABLE IF NOT EXISTS `workhistory_repository` (
5792  `work_history_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
5793  `work_history_id` INT(20) NOT NULL,
5794  `uname` VARCHAR(75) NOT NULL,
5795  `change_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
5796  `data` TEXT NULL DEFAULT NULL,
5797  PRIMARY KEY (`work_history_repository_id`),
5798  INDEX `whr_whid_fk` (`work_history_id` ASC),
5799  CONSTRAINT `whr_whid_fk`
5800    FOREIGN KEY (`work_history_id`)
5801    REFERENCES `work_history` (`work_history_id`)
5802    ON DELETE CASCADE
5803    ON UPDATE CASCADE)
5804ENGINE = InnoDB
5805AUTO_INCREMENT = 443
5806DEFAULT CHARACTER SET = latin1;
5807
5808
5809-- -----------------------------------------------------
5810-- Table `xml_config`
5811-- -----------------------------------------------------
5812CREATE TABLE IF NOT EXISTS `xml_config` (
5813  `xml_config_id` INT(11) NOT NULL AUTO_INCREMENT,
5814  `name` VARCHAR(50) NOT NULL,
5815  `generation_class` VARCHAR(50) NOT NULL,
5816  `xml` MEDIUMTEXT NOT NULL,
5817  PRIMARY KEY (`xml_config_id`))
5818ENGINE = InnoDB
5819AUTO_INCREMENT = 6
5820DEFAULT CHARACTER SET = latin1;
5821
5822
5823-- -----------------------------------------------------
5824-- Table `xml_repository`
5825-- -----------------------------------------------------
5826CREATE TABLE IF NOT EXISTS `xml_repository` (
5827  `xml_repository_id` INT(20) NOT NULL AUTO_INCREMENT,
5828  `candidate_id` INT(20) NOT NULL DEFAULT '0',
5829  `type` VARCHAR(50) NULL DEFAULT NULL,
5830  `event_date` DATETIME NULL DEFAULT NULL,
5831  `data` TEXT NULL DEFAULT NULL,
5832  PRIMARY KEY (`xml_repository_id`),
5833  INDEX `candidate_id` (`candidate_id` ASC),
5834  CONSTRAINT `xml_repository_ibfk_1`
5835    FOREIGN KEY (`candidate_id`)
5836    REFERENCES `candidate_orig` (`candidate_id`)
5837    ON DELETE CASCADE
5838    ON UPDATE CASCADE)
5839ENGINE = InnoDB
5840DEFAULT CHARACTER SET = latin1;
5841
5842
5843SET SQL_MODE=@OLD_SQL_MODE;
5844SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
5845SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
5846© 2018 GitHub, Inc.
5847Terms
5848Privacy
5849Security
5850Status
5851Help
5852Contact GitHub
5853API
5854Training
5855Shop
5856Blog
5857About