INSERT INTO `hs_hr_country`(`cou_code`, `name`, `cou_name`, `iso3`, `numcode`) VALUES ('AF', 'AFGHANISTAN', 'Afghanistan', 'AFG', 4),
('AL', 'ALBANIA', 'Albania', 'ALB', 8),
('DZ', 'ALGERIA', 'Algeria', 'DZA', 12),
('AS', 'AMERICAN SAMOA', 'American Samoa', 'ASM', 16),
('AD', 'ANDORRA', 'Andorra', 'AND', 20),
('AO', 'ANGOLA', 'Angola', 'AGO', 24),
('AI', 'ANGUILLA', 'Anguilla', 'AIA', 660),
('AQ', 'ANTARCTICA', 'Antarctica', NULL, NULL),
('AG', 'ANTIGUA AND BARBUDA', 'Antigua and Barbuda', 'ATG', 28),
('AR', 'ARGENTINA', 'Argentina', 'ARG', 32),
('AM', 'ARMENIA', 'Armenia', 'ARM', 51),
('AW', 'ARUBA', 'Aruba', 'ABW', 533),
('AU', 'AUSTRALIA', 'Australia', 'AUS', 36),
('AT', 'AUSTRIA', 'Austria', 'AUT', 40),
('AZ', 'AZERBAIJAN', 'Azerbaijan', 'AZE', 31),
('BS', 'BAHAMAS', 'Bahamas', 'BHS', 44),
('BH', 'BAHRAIN', 'Bahrain', 'BHR', 48),
('BD', 'BANGLADESH', 'Bangladesh', 'BGD', 50),
('BB', 'BARBADOS', 'Barbados', 'BRB', 52),
('BY', 'BELARUS', 'Belarus', 'BLR', 112),
('BE', 'BELGIUM', 'Belgium', 'BEL', 56),
('BZ', 'BELIZE', 'Belize', 'BLZ', 84),
('BJ', 'BENIN', 'Benin', 'BEN', 204),
('BM', 'BERMUDA', 'Bermuda', 'BMU', 60),
('BT', 'BHUTAN', 'Bhutan', 'BTN', 64),
('BO', 'BOLIVIA', 'Bolivia', 'BOL', 68),
('BA', 'BOSNIA AND HERZEGOVINA', 'Bosnia and Herzegovina', 'BIH', 70),
('BW', 'BOTSWANA', 'Botswana', 'BWA', 72),
('BV', 'BOUVET ISLAND', 'Bouvet Island', NULL, NULL),
('BR', 'BRAZIL', 'Brazil', 'BRA', 76),
('IO', 'BRITISH INDIAN OCEAN TERRITORY', 'British Indian Ocean Territory', NULL, NULL),
('BN', 'BRUNEI DARUSSALAM', 'Brunei Darussalam', 'BRN', 96),
('BG', 'BULGARIA', 'Bulgaria', 'BGR', 100),
('BF', 'BURKINA FASO', 'Burkina Faso', 'BFA', 854),
('BI', 'BURUNDI', 'Burundi', 'BDI', 108),
('KH', 'CAMBODIA', 'Cambodia', 'KHM', 116),
('CM', 'CAMEROON', 'Cameroon', 'CMR', 120),
('CA', 'CANADA', 'Canada', 'CAN', 124),
('CV', 'CAPE VERDE', 'Cape Verde', 'CPV', 132),
('KY', 'CAYMAN ISLANDS', 'Cayman Islands', 'CYM', 136),
('CF', 'CENTRAL AFRICAN REPUBLIC', 'Central African Republic', 'CAF', 140),
('TD', 'CHAD', 'Chad', 'TCD', 148),
('CL', 'CHILE', 'Chile', 'CHL', 152),
('CN', 'CHINA', 'China', 'CHN', 156),
('CX', 'CHRISTMAS ISLAND', 'Christmas Island', NULL, NULL),
('CC', 'COCOS (KEELING) ISLANDS', 'Cocos (Keeling) Islands', NULL, NULL),
('CO', 'COLOMBIA', 'Colombia', 'COL', 170),
('KM', 'COMOROS', 'Comoros', 'COM', 174),
('CG', 'CONGO', 'Congo', 'COG', 178),
('CD', 'CONGO, THE DEMOCRATIC REPUBLIC OF THE', 'Congo, the Democratic Republic of the', 'COD', 180),
('CK', 'COOK ISLANDS', 'Cook Islands', 'COK', 184),
('CR', 'COSTA RICA', 'Costa Rica', 'CRI', 188),
('CI', 'COTE D''IVOIRE', 'Cote D''Ivoire', 'CIV', 384),
('HR', 'CROATIA', 'Croatia', 'HRV', 191),
('CU', 'CUBA', 'Cuba', 'CUB', 192),
('CY', 'CYPRUS', 'Cyprus', 'CYP', 196),
('CZ', 'CZECH REPUBLIC', 'Czech Republic', 'CZE', 203),
('DK', 'DENMARK', 'Denmark', 'DNK', 208),
('DJ', 'DJIBOUTI', 'Djibouti', 'DJI', 262),
('DM', 'DOMINICA', 'Dominica', 'DMA', 212),
('DO', 'DOMINICAN REPUBLIC', 'Dominican Republic', 'DOM', 214),
('EC', 'ECUADOR', 'Ecuador', 'ECU', 218),
('EG', 'EGYPT', 'Egypt', 'EGY', 818),
('SV', 'EL SALVADOR', 'El Salvador', 'SLV', 222),
('GQ', 'EQUATORIAL GUINEA', 'Equatorial Guinea', 'GNQ', 226),
('ER', 'ERITREA', 'Eritrea', 'ERI', 232),
('EE', 'ESTONIA', 'Estonia', 'EST', 233),
('ET', 'ETHIOPIA', 'Ethiopia', 'ETH', 231),
('FK', 'FALKLAND ISLANDS (MALVINAS)', 'Falkland Islands (Malvinas)', 'FLK', 238),
('FO', 'FAROE ISLANDS', 'Faroe Islands', 'FRO', 234),
('FJ', 'FIJI', 'Fiji', 'FJI', 242),
('FI', 'FINLAND', 'Finland', 'FIN', 246),
('FR', 'FRANCE', 'France', 'FRA', 250),
('GF', 'FRENCH GUIANA', 'French Guiana', 'GUF', 254),
('PF', 'FRENCH POLYNESIA', 'French Polynesia', 'PYF', 258),
('TF', 'FRENCH SOUTHERN TERRITORIES', 'French Southern Territories', NULL, NULL),
('GA', 'GABON', 'Gabon', 'GAB', 266),
('GM', 'GAMBIA', 'Gambia', 'GMB', 270),
('GE', 'GEORGIA', 'Georgia', 'GEO', 268),
('DE', 'GERMANY', 'Germany', 'DEU', 276),
('GH', 'GHANA', 'Ghana', 'GHA', 288),
('GI', 'GIBRALTAR', 'Gibraltar', 'GIB', 292),
('GR', 'GREECE', 'Greece', 'GRC', 300),
('GL', 'GREENLAND', 'Greenland', 'GRL', 304),
('GD', 'GRENADA', 'Grenada', 'GRD', 308),
('GP', 'GUADELOUPE', 'Guadeloupe', 'GLP', 312),
('GU', 'GUAM', 'Guam', 'GUM', 316),
('GT', 'GUATEMALA', 'Guatemala', 'GTM', 320),
('GN', 'GUINEA', 'Guinea', 'GIN', 324),
('GW', 'GUINEA-BISSAU', 'Guinea-Bissau', 'GNB', 624),
('GY', 'GUYANA', 'Guyana', 'GUY', 328),
('HT', 'HAITI', 'Haiti', 'HTI', 332),
('HM', 'HEARD ISLAND AND MCDONALD ISLANDS', 'Heard Island and Mcdonald Islands', NULL, NULL),
('VA', 'HOLY SEE (VATICAN CITY STATE)', 'Holy See (Vatican City State)', 'VAT', 336),
('HN', 'HONDURAS', 'Honduras', 'HND', 340),
('HK', 'HONG KONG', 'Hong Kong', 'HKG', 344),
('HU', 'HUNGARY', 'Hungary', 'HUN', 348),
('IS', 'ICELAND', 'Iceland', 'ISL', 352),
('IN', 'INDIA', 'India', 'IND', 356),
('ID', 'INDONESIA', 'Indonesia', 'IDN', 360),
('IR', 'IRAN, ISLAMIC REPUBLIC OF', 'Iran, Islamic Republic of', 'IRN', 364),
('IQ', 'IRAQ', 'Iraq', 'IRQ', 368),
('IE', 'IRELAND', 'Ireland', 'IRL', 372),
('IL', 'ISRAEL', 'Israel', 'ISR', 376),
('IT', 'ITALY', 'Italy', 'ITA', 380),
('JM', 'JAMAICA', 'Jamaica', 'JAM', 388),
('JP', 'JAPAN', 'Japan', 'JPN', 392),
('JO', 'JORDAN', 'Jordan', 'JOR', 400),
('KZ', 'KAZAKHSTAN', 'Kazakhstan', 'KAZ', 398),
('KE', 'KENYA', 'Kenya', 'KEN', 404),
('KI', 'KIRIBATI', 'Kiribati', 'KIR', 296),
('KP', 'KOREA, DEMOCRATIC PEOPLE''S REPUBLIC OF', 'Korea, Democratic People''s Republic of', 'PRK', 408),
('KR', 'KOREA, REPUBLIC OF', 'Korea, Republic of', 'KOR', 410),
('KW', 'KUWAIT', 'Kuwait', 'KWT', 414),
('KG', 'KYRGYZSTAN', 'Kyrgyzstan', 'KGZ', 417),
('LA', 'LAO PEOPLE''S DEMOCRATIC REPUBLIC', 'Lao People''s Democratic Republic', 'LAO', 418),
('LV', 'LATVIA', 'Latvia', 'LVA', 428),
('LB', 'LEBANON', 'Lebanon', 'LBN', 422),
('LS', 'LESOTHO', 'Lesotho', 'LSO', 426),
('LR', 'LIBERIA', 'Liberia', 'LBR', 430),
('LY', 'LIBYAN ARAB JAMAHIRIYA', 'Libyan Arab Jamahiriya', 'LBY', 434),
('LI', 'LIECHTENSTEIN', 'Liechtenstein', 'LIE', 438),
('LT', 'LITHUANIA', 'Lithuania', 'LTU', 440),
('LU', 'LUXEMBOURG', 'Luxembourg', 'LUX', 442),
('MO', 'MACAO', 'Macao', 'MAC', 446),
('MK', 'MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF', 'Macedonia, the Former Yugoslav Republic of', 'MKD', 807),
('MG', 'MADAGASCAR', 'Madagascar', 'MDG', 450),
('MW', 'MALAWI', 'Malawi', 'MWI', 454),
('MY', 'MALAYSIA', 'Malaysia', 'MYS', 458),
('MV', 'MALDIVES', 'Maldives', 'MDV', 462),
('ML', 'MALI', 'Mali', 'MLI', 466),
('MT', 'MALTA', 'Malta', 'MLT', 470),
('MH', 'MARSHALL ISLANDS', 'Marshall Islands', 'MHL', 584),
('MQ', 'MARTINIQUE', 'Martinique', 'MTQ', 474),
('MR', 'MAURITANIA', 'Mauritania', 'MRT', 478),
('MU', 'MAURITIUS', 'Mauritius', 'MUS', 480),
('YT', 'MAYOTTE', 'Mayotte', NULL, NULL),
('MX', 'MEXICO', 'Mexico', 'MEX', 484),
('FM', 'MICRONESIA, FEDERATED STATES OF', 'Micronesia, Federated States of', 'FSM', 583),
('MD', 'MOLDOVA, REPUBLIC OF', 'Moldova, Republic of', 'MDA', 498),
('MC', 'MONACO', 'Monaco', 'MCO', 492),
('MN', 'MONGOLIA', 'Mongolia', 'MNG', 496),
('MS', 'MONTSERRAT', 'Montserrat', 'MSR', 500),
('MA', 'MOROCCO', 'Morocco', 'MAR', 504),
('MZ', 'MOZAMBIQUE', 'Mozambique', 'MOZ', 508),
('MM', 'MYANMAR', 'Myanmar', 'MMR', 104),
('NA', 'NAMIBIA', 'Namibia', 'NAM', 516),
('NR', 'NAURU', 'Nauru', 'NRU', 520),
('NP', 'NEPAL', 'Nepal', 'NPL', 524),
('NL', 'NETHERLANDS', 'Netherlands', 'NLD', 528),
('AN', 'NETHERLANDS ANTILLES', 'Netherlands Antilles', 'ANT', 530),
('NC', 'NEW CALEDONIA', 'New Caledonia', 'NCL', 540),
('NZ', 'NEW ZEALAND', 'New Zealand', 'NZL', 554),
('NI', 'NICARAGUA', 'Nicaragua', 'NIC', 558),
('NE', 'NIGER', 'Niger', 'NER', 562),
('NG', 'NIGERIA', 'Nigeria', 'NGA', 566),
('NU', 'NIUE', 'Niue', 'NIU', 570),
('NF', 'NORFOLK ISLAND', 'Norfolk Island', 'NFK', 574),
('MP', 'NORTHERN MARIANA ISLANDS', 'Northern Mariana Islands', 'MNP', 580),
('NO', 'NORWAY', 'Norway', 'NOR', 578),
('OM', 'OMAN', 'Oman', 'OMN', 512),
('PK', 'PAKISTAN', 'Pakistan', 'PAK', 586),
('PW', 'PALAU', 'Palau', 'PLW', 585),
('PS', 'PALESTINIAN TERRITORY, OCCUPIED', 'Palestinian Territory, Occupied', NULL, NULL),
('PA', 'PANAMA', 'Panama', 'PAN', 591),
('PG', 'PAPUA NEW GUINEA', 'Papua New Guinea', 'PNG', 598),
('PY', 'PARAGUAY', 'Paraguay', 'PRY', 600),
('PE', 'PERU', 'Peru', 'PER', 604),
('PH', 'PHILIPPINES', 'Philippines', 'PHL', 608),
('PN', 'PITCAIRN', 'Pitcairn', 'PCN', 612),
('PL', 'POLAND', 'Poland', 'POL', 616),
('PT', 'PORTUGAL', 'Portugal', 'PRT', 620),
('PR', 'PUERTO RICO', 'Puerto Rico', 'PRI', 630),
('QA', 'QATAR', 'Qatar', 'QAT', 634),
('RE', 'REUNION', 'Reunion', 'REU', 638),
('RO', 'ROMANIA', 'Romania', 'ROM', 642),
('RU', 'RUSSIAN FEDERATION', 'Russian Federation', 'RUS', 643),
('RW', 'RWANDA', 'Rwanda', 'RWA', 646),
('SH', 'SAINT HELENA', 'Saint Helena', 'SHN', 654),
('KN', 'SAINT KITTS AND NEVIS', 'Saint Kitts and Nevis', 'KNA', 659),
('LC', 'SAINT LUCIA', 'Saint Lucia', 'LCA', 662),
('PM', 'SAINT PIERRE AND MIQUELON', 'Saint Pierre and Miquelon', 'SPM', 666),
('VC', 'SAINT VINCENT AND THE GRENADINES', 'Saint Vincent and the Grenadines', 'VCT', 670),
('WS', 'SAMOA', 'Samoa', 'WSM', 882),
('SM', 'SAN MARINO', 'San Marino', 'SMR', 674),
('ST', 'SAO TOME AND PRINCIPE', 'Sao Tome and Principe', 'STP', 678),
('SA', 'SAUDI ARABIA', 'Saudi Arabia', 'SAU', 682),
('SN', 'SENEGAL', 'Senegal', 'SEN', 686),
('CS', 'SERBIA AND MONTENEGRO', 'Serbia and Montenegro', NULL, NULL),
('SC', 'SEYCHELLES', 'Seychelles', 'SYC', 690),
('SL', 'SIERRA LEONE', 'Sierra Leone', 'SLE', 694),
('SG', 'SINGAPORE', 'Singapore', 'SGP', 702),
('SK', 'SLOVAKIA', 'Slovakia', 'SVK', 703),
('SI', 'SLOVENIA', 'Slovenia', 'SVN', 705),
('SB', 'SOLOMON ISLANDS', 'Solomon Islands', 'SLB', 90),
('SO', 'SOMALIA', 'Somalia', 'SOM', 706),
('ZA', 'SOUTH AFRICA', 'South Africa', 'ZAF', 710),
('GS', 'SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS', 'South Georgia and the South Sandwich Islands', NULL, NULL),
('ES', 'SPAIN', 'Spain', 'ESP', 724),
('LK', 'SRI LANKA', 'Sri Lanka', 'LKA', 144),
('SD', 'SUDAN', 'Sudan', 'SDN', 736),
('SR', 'SURINAME', 'Suriname', 'SUR', 740),
('SJ', 'SVALBARD AND JAN MAYEN', 'Svalbard and Jan Mayen', 'SJM', 744),
('SZ', 'SWAZILAND', 'Swaziland', 'SWZ', 748),
('SE', 'SWEDEN', 'Sweden', 'SWE', 752),
('CH', 'SWITZERLAND', 'Switzerland', 'CHE', 756),
('SY', 'SYRIAN ARAB REPUBLIC', 'Syrian Arab Republic', 'SYR', 760),
('TW', 'TAIWAN, PROVINCE OF CHINA', 'Taiwan', 'TWN', 158),
('TJ', 'TAJIKISTAN', 'Tajikistan', 'TJK', 762),
('TZ', 'TANZANIA, UNITED REPUBLIC OF', 'Tanzania, United Republic of', 'TZA', 834),
('TH', 'THAILAND', 'Thailand', 'THA', 764),
('TL', 'TIMOR-LESTE', 'Timor-Leste', NULL, NULL),
('TG', 'TOGO', 'Togo', 'TGO', 768),
('TK', 'TOKELAU', 'Tokelau', 'TKL', 772),
('TO', 'TONGA', 'Tonga', 'TON', 776),
('TT', 'TRINIDAD AND TOBAGO', 'Trinidad and Tobago', 'TTO', 780),
('TN', 'TUNISIA', 'Tunisia', 'TUN', 788),
('TR', 'TURKEY', 'Turkey', 'TUR', 792),
('TM', 'TURKMENISTAN', 'Turkmenistan', 'TKM', 795),
('TC', 'TURKS AND CAICOS ISLANDS', 'Turks and Caicos Islands', 'TCA', 796),
('TV', 'TUVALU', 'Tuvalu', 'TUV', 798),
('UG', 'UGANDA', 'Uganda', 'UGA', 800),
('UA', 'UKRAINE', 'Ukraine', 'UKR', 804),
('AE', 'UNITED ARAB EMIRATES', 'United Arab Emirates', 'ARE', 784),
('GB', 'UNITED KINGDOM', 'United Kingdom', 'GBR', 826),
('US', 'UNITED STATES', 'United States', 'USA', 840),
('UM', 'UNITED STATES MINOR OUTLYING ISLANDS', 'United States Minor Outlying Islands', NULL, NULL),
('UY', 'URUGUAY', 'Uruguay', 'URY', 858),
('UZ', 'UZBEKISTAN', 'Uzbekistan', 'UZB', 860),
('VU', 'VANUATU', 'Vanuatu', 'VUT', 548),
('VE', 'VENEZUELA', 'Venezuela', 'VEN', 862),
('VN', 'VIET NAM', 'Viet Nam', 'VNM', 704),
('VG', 'VIRGIN ISLANDS, BRITISH', 'Virgin Islands, British', 'VGB', 92),
('VI', 'VIRGIN ISLANDS, U.S.', 'Virgin Islands, U.s.', 'VIR', 850),
('WF', 'WALLIS AND FUTUNA', 'Wallis and Futuna', 'WLF', 876),
('EH', 'WESTERN SAHARA', 'Western Sahara', 'ESH', 732),
('YE', 'YEMEN', 'Yemen', 'YEM', 887),
('ZM', 'ZAMBIA', 'Zambia', 'ZMB', 894),
('ZW', 'ZIMBABWE', 'Zimbabwe', 'ZWE', 716);
INSERT INTO `hs_hr_currency_type`(`code`, `currency_id`, `currency_name`) VALUES (3, 'AED', 'Utd. Arab Emir. Dirham'),
(4, 'AFN', 'Afghanistan Afghani'),
(5, 'ALL', 'Albanian Lek'),
(6, 'ANG', 'NL Antillian Guilder'),
(7, 'AOR', 'Angolan New Kwanza'),
(177, 'ARP', 'Argentina Pesos'),
(8, 'ARS', 'Argentine Peso'),
(10, 'AUD', 'Australian Dollar'),
(11, 'AWG', 'Aruban Florin'),
(12, 'BBD', 'Barbados Dollar'),
(13, 'BDT', 'Bangladeshi Taka'),
(15, 'BGL', 'Bulgarian Lev'),
(16, 'BHD', 'Bahraini Dinar'),
(17, 'BIF', 'Burundi Franc'),
(18, 'BMD', 'Bermudian Dollar'),
(19, 'BND', 'Brunei Dollar'),
(20, 'BOB', 'Bolivian Boliviano'),
(21, 'BRL', 'Brazilian Real'),
(22, 'BSD', 'Bahamian Dollar'),
(23, 'BTN', 'Bhutan Ngultrum'),
(24, 'BWP', 'Botswana Pula'),
(25, 'BZD', 'Belize Dollar'),
(26, 'CAD', 'Canadian Dollar'),
(27, 'CHF', 'Swiss Franc'),
(28, 'CLP', 'Chilean Peso'),
(29, 'CNY', 'Chinese Yuan Renminbi'),
(30, 'COP', 'Colombian Peso'),
(31, 'CRC', 'Costa Rican Colon'),
(171, 'CZK', 'Czech Koruna'),
(32, 'CUP', 'Cuban Peso'),
(33, 'CVE', 'Cape Verde Escudo'),
(34, 'CYP', 'Cyprus Pound'),
(37, 'DJF', 'Djibouti Franc'),
(38, 'DKK', 'Danish Krona'),
(39, 'DOP', 'Dominican Peso'),
(40, 'DZD', 'Algerian Dinar'),
(41, 'ECS', 'Ecuador Sucre'),
(43, 'EEK', 'Estonian Krona'),
(44, 'EGP', 'Egyptian Pound'),
(46, 'ETB', 'Ethiopian Birr'),
(42, 'EUR', 'Euro'),
(48, 'FJD', 'Fiji Dollar'),
(49, 'FKP', 'Falkland Islands Pound'),
(51, 'GBP', 'Pound Sterling'),
(52, 'GHC', 'Ghanaian Cedi'),
(53, 'GIP', 'Gibraltar Pound'),
(54, 'GMD', 'Gambian Dalasi'),
(55, 'GNF', 'Guinea Franc'),
(57, 'GTQ', 'Guatemalan Quetzal'),
(58, 'GYD', 'Guyanan Dollar'),
(59, 'HKD', 'Hong Kong Dollar'),
(60, 'HNL', 'Honduran Lempira'),
(61, 'HRK', 'Croatian Kuna'),
(62, 'HTG', 'Haitian Gourde'),
(63, 'HUF', 'Hungarian Forint'),
(64, 'IDR', 'Indonesian Rupiah'),
(66, 'ILS', 'Israeli New Shekel'),
(67, 'INR', 'Indian Rupee'),
(68, 'IQD', 'Iraqi Dinar'),
(69, 'IRR', 'Iranian Rial'),
(70, 'ISK', 'Iceland Krona'),
(72, 'JMD', 'Jamaican Dollar'),
(73, 'JOD', 'Jordanian Dinar'),
(74, 'JPY', 'Japanese Yen'),
(75, 'KES', 'Kenyan Shilling'),
(76, 'KHR', 'Kampuchean Riel'),
(77, 'KMF', 'Comoros Franc'),
(78, 'KPW', 'North Korean Won'),
(79, 'KRW', 'Korean Won'),
(80, 'KWD', 'Kuwaiti Dinar'),
(81, 'KYD', 'Cayman Islands Dollar'),
(82, 'KZT', 'Kazakhstan Tenge'),
(83, 'LAK', 'Lao Kip'),
(84, 'LBP', 'Lebanese Pound'),
(85, 'LKR', 'Sri Lanka Rupee'),
(86, 'LRD', 'Liberian Dollar'),
(87, 'LSL', 'Lesotho Loti'),
(88, 'LTL', 'Lithuanian Litas'),
(90, 'LVL', 'Latvian Lats'),
(91, 'LYD', 'Libyan Dinar'),
(92, 'MAD', 'Moroccan Dirham'),
(93, 'MGF', 'Malagasy Franc'),
(94, 'MMK', 'Myanmar Kyat'),
(95, 'MNT', 'Mongolian Tugrik'),
(96, 'MOP', 'Macau Pataca'),
(97, 'MRO', 'Mauritanian Ouguiya'),
(98, 'MTL', 'Maltese Lira'),
(99, 'MUR', 'Mauritius Rupee'),
(100, 'MVR', 'Maldive Rufiyaa'),
(101, 'MWK', 'Malawi Kwacha'),
(102, 'MXN', 'Mexican New Peso'),
(172, 'MXP', 'Mexican Peso'),
(103, 'MYR', 'Malaysian Ringgit'),
(104, 'MZM', 'Mozambique Metical'),
(105, 'NAD', 'Namibia Dollar'),
(106, 'NGN', 'Nigerian Naira'),
(107, 'NIO', 'Nicaraguan Cordoba Oro'),
(109, 'NOK', 'Norwegian Krona'),
(110, 'NPR', 'Nepalese Rupee'),
(111, 'NZD', 'New Zealand Dollar'),
(112, 'OMR', 'Omani Rial'),
(113, 'PAB', 'Panamanian Balboa'),
(114, 'PEN', 'Peruvian Nuevo Sol'),
(115, 'PGK', 'Papua New Guinea Kina'),
(116, 'PHP', 'Philippine Peso'),
(117, 'PKR', 'Pakistan Rupee'),
(118, 'PLN', 'Polish Zloty'),
(120, 'PYG', 'Paraguay Guarani'),
(121, 'QAR', 'Qatari Rial'),
(122, 'ROL', 'Romanian Leu'),
(123, 'RUB', 'Russian Rouble'),
(180, 'RUR', 'Russia Rubles'),
(125, 'SBD', 'Solomon Islands Dollar'),
(126, 'SCR', 'Seychelles Rupee'),
(127, 'SDD', 'Sudanese Dinar'),
(128, 'SDP', 'Sudanese Pound'),
(129, 'SEK', 'Swedish Krona'),
(131, 'SGD', 'Singapore Dollar'),
(132, 'SHP', 'St. Helena Pound'),
(130, 'SKK', 'Slovak Koruna'),
(135, 'SLL', 'Sierra Leone Leone'),
(136, 'SOS', 'Somali Shilling'),
(137, 'SRD', 'Surinamese Dollar'),
(138, 'STD', 'Sao Tome/Principe Dobra'),
(139, 'SVC', 'El Salvador Colon'),
(140, 'SYP', 'Syrian Pound'),
(141, 'SZL', 'Swaziland Lilangeni'),
(142, 'THB', 'Thai Baht'),
(143, 'TND', 'Tunisian Dinar'),
(144, 'TOP', 'Tongan Pa''anga'),
(145, 'TRL', 'Turkish Lira'),
(146, 'TTD', 'Trinidad/Tobago Dollar'),
(147, 'TWD', 'Taiwan Dollar'),
(148, 'TZS', 'Tanzanian Shilling'),
(149, 'UAH', 'Ukraine Hryvnia'),
(150, 'UGX', 'Uganda Shilling'),
(151, 'USD', 'United States Dollar'),
(152, 'UYP', 'Uruguayan Peso'),
(153, 'VEB', 'Venezuelan Bolivar'),
(154, 'VND', 'Vietnamese Dong'),
(155, 'VUV', 'Vanuatu Vatu'),
(156, 'WST', 'Samoan Tala'),
(158, 'XAF', 'CFA Franc BEAC'),
(159, 'XAG', 'Silver (oz.)'),
(160, 'XAU', 'Gold (oz.)'),
(161, 'XCD', 'Eastern Caribbean Dollars'),
(179, 'XDR', 'IMF Special Drawing Right'),
(162, 'XOF', 'CFA Franc BCEAO'),
(163, 'XPD', 'Palladium (oz.)'),
(164, 'XPF', 'CFP Franc'),
(165, 'XPT', 'Platinum (oz.)'),
(166, 'YER', 'Yemeni Riyal'),
(167, 'YUM', 'Yugoslavian Dinar'),
(175, 'YUN', 'Yugoslav Dinar'),
(168, 'ZAR', 'South African Rand'),
(176, 'ZMK', 'Zambian Kwacha'),
(169, 'ZRN', 'New Zaire'),
(170, 'ZWD', 'Zimbabwe Dollar'),
(173, 'SAR', 'Saudi Arabia Riyal');
INSERT INTO `hs_hr_province`(`id`, `province_name`, `province_code`, `cou_code`) VALUES (1, 'Alaska', 'AK', 'US'),
(2, 'Alabama', 'AL', 'US'),
(3, 'American Samoa', 'AS', 'US'),
(4, 'Arizona', 'AZ', 'US'),
(5, 'Arkansas', 'AR', 'US'),
(6, 'California', 'CA', 'US'),
(7, 'Colorado', 'CO', 'US'),
(8, 'Connecticut', 'CT', 'US'),
(9, 'Delaware', 'DE', 'US'),
(10, 'District of Columbia', 'DC', 'US'),
(11, 'Federated States of Micronesia', 'FM', 'US'),
(12, 'Florida', 'FL', 'US'),
(13, 'Georgia', 'GA', 'US'),
(14, 'Guam', 'GU', 'US'),
(15, 'Hawaii', 'HI', 'US'),
(16, 'Idaho', 'ID', 'US'),
(17, 'Illinois', 'IL', 'US'),
(18, 'Indiana', 'IN', 'US'),
(19, 'Iowa', 'IA', 'US'),
(20, 'Kansas', 'KS', 'US'),
(21, 'Kentucky', 'KY', 'US'),
(22, 'Louisiana', 'LA', 'US'),
(23, 'Maine', 'ME', 'US'),
(24, 'Marshall Islands', 'MH', 'US'),
(25, 'Maryland', 'MD', 'US'),
(26, 'Massachusetts', 'MA', 'US'),
(27, 'Michigan', 'MI', 'US'),
(28, 'Minnesota', 'MN', 'US'),
(29, 'Mississippi', 'MS', 'US'),
(30, 'Missouri', 'MO', 'US'),
(31, 'Montana', 'MT', 'US'),
(32, 'Nebraska', 'NE', 'US'),
(33, 'Nevada', 'NV', 'US'),
(34, 'New Hampshire', 'NH', 'US'),
(35, 'New Jersey', 'NJ', 'US'),
(36, 'New Mexico', 'NM', 'US'),
(37, 'New York', 'NY', 'US'),
(38, 'North Carolina', 'NC', 'US'),
(39, 'North Dakota', 'ND', 'US'),
(40, 'Northern Mariana Islands', 'MP', 'US'),
(41, 'Ohio', 'OH', 'US'),
(42, 'Oklahoma', 'OK', 'US'),
(43, 'Oregon', 'OR', 'US'),
(44, 'Palau', 'PW', 'US'),
(45, 'Pennsylvania', 'PA', 'US'),
(46, 'Puerto Rico', 'PR', 'US'),
(47, 'Rhode Island', 'RI', 'US'),
(48, 'South Carolina', 'SC', 'US'),
(49, 'South Dakota', 'SD', 'US'),
(50, 'Tennessee', 'TN', 'US'),
(51, 'Texas', 'TX', 'US'),
(52, 'Utah', 'UT', 'US'),
(53, 'Vermont', 'VT', 'US'),
(54, 'Virgin Islands', 'VI', 'US'),
(55, 'Virginia', 'VA', 'US'),
(56, 'Washington', 'WA', 'US'),
(57, 'West Virginia', 'WV', 'US'),
(58, 'Wisconsin', 'WI', 'US'),
(59, 'Wyoming', 'WY', 'US'),
(60, 'Armed Forces Africa', 'AE', 'US'),
(61, 'Armed Forces Americas (except Canada)', 'AA', 'US'),
(62, 'Armed Forces Canada', 'AE', 'US'),
(63, 'Armed Forces Europe', 'AE', 'US'),
(64, 'Armed Forces Middle East', 'AE', 'US'),
(65, 'Armed Forces Pacific', 'AP', 'US');
INSERT INTO `hs_hr_module` VALUES ('MOD001','Admin','OrangeHRM','info@orangehrm.com','VER001','HR Admin'),
('MOD002','PIM','OrangeHRM','info@orangehrm.com','VER001','HR Functions'),
('MOD004','Report','OrangeHRM','info@orangehrm.com','VER001','Reporting'),
('MOD005', 'Leave', 'OrangeHRM', 'info@orangehrm.com', 'VER001', 'Leave Tracking'),
('MOD006', 'Time', 'OrangeHRM', 'info@orangehrm.com', 'VER001', 'Time Tracking'),
('MOD007', 'Benefits', 'OrangeHRM', 'info@orangehrm.com', 'VER001', 'Benefits Tracking'),
('MOD008', 'Recruitment', 'OrangeHRM', 'info@orangehrm.com', 'VER001', 'Recruitment'),
('MOD009', 'Performance', 'OrangeHRM', 'info@orangehrm.com', 'VER001', 'Performance');
INSERT INTO `ohrm_work_week` VALUES (1, NULL, 0, 0, 0, 0, 0, 8, 8);
INSERT INTO `hs_hr_payperiod`(payperiod_code, payperiod_name) VALUES (1, 'Weekly'),
(2, 'Bi Weekly'),
(3, 'Semi Monthly'),
(4, 'Monthly'),
(5, 'Monthly on first pay of month.'),
(6, 'Hourly');
INSERT INTO `hs_hr_config`(`key`, `value`) VALUES ('ldap_server', ''),
('ldap_domain_name', ''),
('ldap_port', ''),
('ldap_status', ''),
('hsp_current_plan', '0'),
('hsp_accrued_last_updated', '0000-00-00'),
('hsp_used_last_updated', '0000-00-00'),
('attendanceEmpEditSubmitted', 'No'),
('attendanceSupEditSubmitted', 'No'),
('pim_show_deprecated_fields', 0),
('showSSN', 0),
('showSIN', 0),
('showTaxExemptions', 0),
('timesheet_time_format', '1'),
('timesheet_period_set', 'No'),
('timesheet_period_and_start_date', 'WeeklyWeeklyTimesheetPeriod1Week'),
('admin.localization.default_language', 'en_US'),
('admin.localization.use_browser_language', 'No'),
('admin.localization.default_date_format', 'Y-m-d'),
('authorize_user_role_manager_class', 'BasicUserRoleManager'),
('include_supervisor_chain', 'No'),
('leave.entitlement_consumption_algorithm', 'FIFOEntitlementConsumptionStrategy'),
('leave.work_schedule_implementation', 'BasicWorkSchedule'),
('themeName', 'default'),
('leave.leavePeriodStatus',1),
('leave.include_pending_leave_in_balance', 1),
('admin.default_workshift_start_time', '09:00'),
('admin.default_workshift_end_time', '17:00'),
('report.mysql_group_concat_max_len', 2048);
INSERT INTO `ohrm_emp_reporting_method`(`reporting_method_id`, `reporting_method_name`) VALUES(1, 'Direct'),
(2, 'Indirect');
INSERT INTO `hs_hr_unique_id`(last_id, table_name, field_name) VALUES
(0, 'hs_hr_employee', 'emp_number'),
(6, 'hs_hr_module', 'mod_id'),
(0, 'hs_hr_leave', 'leave_id'),
(0, 'hs_hr_leavetype', 'leave_type_id'),
(0, 'hs_hr_leave_requests', 'leave_request_id'),
(0, 'hs_hr_custom_export', 'export_id'),
(0, 'hs_hr_custom_import', 'import_id'),
(0, 'hs_hr_pay_period', 'id'),
(0, 'hs_hr_kpi', 'id'),
(0, 'hs_hr_performance_review', 'id'),
(2, 'ohrm_emp_reporting_method', 'reporting_method_id'),
(0, 'ohrm_timesheet', 'timesheet_id'),
(0, 'ohrm_timesheet_action_log', 'timesheet_action_log_id'),
(0, 'ohrm_timesheet_item', 'timesheet_item_id'),
(0,'ohrm_attendance_record', 'id'),
(0, 'ohrm_job_vacancy', 'id'),
(0, 'ohrm_job_candidate', 'id'),
(106,'ohrm_workflow_state_machine', 'id'),
(0, 'ohrm_job_candidate_attachment', 'id'),
(0, 'ohrm_job_vacancy_attachment', 'id'),
(0, 'ohrm_job_candidate_vacancy', 'id'),
(0, 'ohrm_job_candidate_history', 'id'),
(0, 'ohrm_job_interview', 'id');
INSERT INTO `ohrm_workflow_state_machine`(`id`, `workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `priority`)
VALUES ('1','0','INITIAL','SYSTEM','7','NOT SUBMITTED', '', 0),
('2','0','SUBMITTED','ADMIN','2','APPROVED', '', 0),
('3','0','SUBMITTED','ADMIN','3','REJECTED', '', 0),
('4','0','SUBMITTED','ADMIN','0','SUBMITTED', '', 0),
('5','0','SUBMITTED','ADMIN','5','SUBMITTED', '', 0),
('6','0','SUBMITTED','SUPERVISOR','2','APPROVED', '', 0),
('7','0','SUBMITTED','SUPERVISOR','3','REJECTED', '', 0),
('8','0','SUBMITTED','SUPERVISOR','5','SUBMITTED', '', 0),
('9','0','SUBMITTED','SUPERVISOR','0','SUBMITTED', '', 0),
('10','0','SUBMITTED','ESS USER','0','SUBMITTED', '', 0),
('11','0','SUBMITTED','ESS USER','5','SUBMITTED', '', 0),
('12','0','NOT SUBMITTED','ESS USER','1','SUBMITTED', '', 0),
('13','0','NOT SUBMITTED','ESS USER','5','NOT SUBMITTED', '', 0),
('15','0','NOT SUBMITTED','ESS USER','0','NOT SUBMITTED', '', 0),
('16','0','NOT SUBMITTED','SUPERVISOR','0','NOT SUBMITTED', '', 0),
('17','0','NOT SUBMITTED','SUPERVISOR','5','NOT SUBMITTED', '', 0),
('18','0','NOT SUBMITTED','SUPERVISOR','1','SUBMITTED', '', 0),
('19','0','NOT SUBMITTED','ADMIN','0','NOT SUBMITTED', '', 0),
('20','0','NOT SUBMITTED','ADMIN','5','NOT SUBMITTED', '', 0),
('21','0','NOT SUBMITTED','ADMIN','1','SUBMITTED', '', 0),
('22','0','REJECTED','ESS USER','1','SUBMITTED', '', 0),
('23','0','REJECTED','ESS USER','0','REJECTED', '', 0),
('24','0','REJECTED','ESS USER','5','REJECTED', '', 0),
('25','0','REJECTED','SUPERVISOR','1','SUBMITTED', '', 0),
('26','0','REJECTED','SUPERVISOR','0','REJECTED', '', 0),
('27','0','REJECTED','SUPERVISOR','5','REJECTED', '', 0),
('28','0','REJECTED','ADMIN','0','REJECTED', '', 0),
('29','0','REJECTED','ADMIN','5','SUBMITTED', '', 0),
('30','0','REJECTED','ADMIN','1','SUBMITTED', '', 0),
('31','0','APPROVED','ESS USER','0','APPROVED', '', 0),
('32','0','APPROVED','SUPERVISOR','0','APPROVED', '', 0),
('33','0','APPROVED','ADMIN','0','APPROVED', '', 0),
('34','0','APPROVED','ADMIN','4','SUBMITTED', '', 0),
('35','1','PUNCHED IN','ESS USER','1','PUNCHED OUT', '', 0),
('36','1','INITIAL','ESS USER','0','PUNCHED IN', '', 0),
('37','2','INITIAL','ADMIN','1','APPLICATION INITIATED', '', 0),
('38','2','APPLICATION INITIATED','ADMIN','2','SHORTLISTED', '', 0),
('39','2','APPLICATION INITIATED','ADMIN','3','REJECTED', '', 0),
('40','2','SHORTLISTED','ADMIN','4','INTERVIEW SCHEDULED', '', 0),
('41','2','SHORTLISTED','ADMIN','3','REJECTED', '', 0),
('42','2','INTERVIEW SCHEDULED','ADMIN','3','REJECTED', '', 0),
('43','2','INTERVIEW SCHEDULED','ADMIN','5','INTERVIEW PASSED', '', 0),
('44','2','INTERVIEW SCHEDULED','ADMIN','6','INTERVIEW FAILED', '', 0),
('45','2','INTERVIEW PASSED','ADMIN','4','INTERVIEW SCHEDULED', '', 0),
('46','2','INTERVIEW PASSED','ADMIN','7','JOB OFFERED', '', 0),
('47','2','INTERVIEW PASSED','ADMIN','3','REJECTED', '', 0),
('48','2','INTERVIEW FAILED','ADMIN','3','REJECTED', '', 0),
('49','2','JOB OFFERED','ADMIN','8','OFFER DECLINED', '', 0),
('50','2','JOB OFFERED','ADMIN','3','REJECTED', '', 0),
('51','2','JOB OFFERED','ADMIN','9','HIRED', '', 0),
('52','2','OFFER DECLINED','ADMIN','3','REJECTED', '', 0),
('53','2','INITIAL','HIRING MANAGER','1','APPLICATION INITIATED', '', 0),
('54','2','APPLICATION INITIATED','HIRING MANAGER','2','SHORTLISTED', '', 0),
('55','2','APPLICATION INITIATED','HIRING MANAGER','3','REJECTED', '', 0),
('56','2','SHORTLISTED','HIRING MANAGER','4','INTERVIEW SCHEDULED', '', 0),
('57','2','SHORTLISTED','HIRING MANAGER','3','REJECTED', '', 0),
('58','2','INTERVIEW SCHEDULED','HIRING MANAGER','3','REJECTED', '', 0),
('59','2','INTERVIEW SCHEDULED','HIRING MANAGER','5','INTERVIEW PASSED', '', 0),
('60','2','INTERVIEW SCHEDULED','HIRING MANAGER','6','INTERVIEW FAILED', '', 0),
('61','2','INTERVIEW PASSED','HIRING MANAGER','4','INTERVIEW SCHEDULED', '', 0),
('62','2','INTERVIEW PASSED','HIRING MANAGER','7','JOB OFFERED', '', 0),
('63','2','INTERVIEW PASSED','HIRING MANAGER','3','REJECTED', '', 0),
('64','2','INTERVIEW FAILED','HIRING MANAGER','3','REJECTED', '', 0),
('65','2','JOB OFFERED','HIRING MANAGER','8','OFFER DECLINED', '', 0),
('66','2','JOB OFFERED','HIRING MANAGER','3','REJECTED', '', 0),
('67','2','JOB OFFERED','HIRING MANAGER','9','HIRED', '', 0),
('68','2','OFFER DECLINED','HIRING MANAGER','3','REJECTED', '', 0),
('69','2','INTERVIEW SCHEDULED','INTERVIEWER','5','INTERVIEW PASSED', '', 0),
('70','2','INTERVIEW SCHEDULED','INTERVIEWER','6','INTERVIEW FAILED', '', 0),
('71','1','INITIAL','ADMIN','5','PUNCHED IN', '', 0),
('72','1','PUNCHED IN','ADMIN','6','PUNCHED OUT', '', 0),
('73','1','PUNCHED IN','ADMIN','2','PUNCHED IN', '', 0),
('74','1','PUNCHED IN','ADMIN','7','N/A', '', 0),
('75','1','PUNCHED OUT','ADMIN','2','PUNCHED OUT', '', 0),
('76','1','PUNCHED OUT','ADMIN','3','PUNCHED OUT', '', 0),
('77','1','PUNCHED OUT','ADMIN','7','N/A', '', 0),
('78','0','INITIAL','ADMIN','7','NOT SUBMITTED', '', 0),
('79','0','INITIAL','ESS USER','7','NOT SUBMITTED', '', 0),
('80','0','INITIAL','SUPERVISOR','7','NOT SUBMITTED', '', 0),
('81','3','NOT_EXIST','ADMIN','1','ACTIVE', '', 0),
('82','3','ACTIVE','ADMIN','2','NOT_EXIST', '', 0),
('83','3','ACTIVE','ADMIN','3','TERMINATED', '', 0),
('84','3','TERMINATED','ADMIN','4','ACTIVE', '', 0),
('85','3','TERMINATED','ADMIN','5','NOT_EXIST', '', 0),
(86, '4', 'INITIAL', 'ESS', 'APPLY', 'PENDING APPROVAL', 'supervisor,subscriber', 0),
(87, '4', 'INITIAL', 'ADMIN', 'ASSIGN', 'SCHEDULED', 'ess,supervisor,subscriber', 0),
(88, '4', 'INITIAL', 'SUPERVISOR', 'ASSIGN', 'SCHEDULED', 'ess,supervisor,subscriber', 0),
(89, '4', 'PENDING APPROVAL', 'ADMIN', 'APPROVE', 'SCHEDULED', 'ess,subscriber', 0),
(90, '4', 'PENDING APPROVAL', 'SUPERVISOR', 'APPROVE', 'SCHEDULED', 'ess,subscriber', 0),
(91, '4', 'PENDING APPROVAL', 'ESS', 'CANCEL', 'CANCELLED', 'supervisor,subscriber', 0),
(92, '4', 'PENDING APPROVAL', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(93, '4', 'PENDING APPROVAL', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(94, '4', 'PENDING APPROVAL', 'ADMIN', 'REJECT', 'REJECTED', 'ess,subscriber', 0),
(95, '4', 'PENDING APPROVAL', 'SUPERVISOR', 'REJECT', 'REJECTED', 'ess,subscriber', 0),
(96, '4', 'SCHEDULED', 'ESS', 'CANCEL', 'CANCELLED', 'supervisor,subscriber', 0),
(97, '4', 'SCHEDULED', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(98, '4', 'SCHEDULED', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(99, '4', 'TAKEN', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(100, '4', 'LEAVE TYPE DELETED PENDING APPROVAL', 'ESS', 'CANCEL', 'CANCELLED', 'supervisor,subscriber', 0),
(101, '4', 'LEAVE TYPE DELETED PENDING APPROVAL', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(102, '4', 'LEAVE TYPE DELETED PENDING APPROVAL', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(103, '4', 'LEAVE TYPE DELETED SCHEDULED', 'ESS', 'CANCEL', 'CANCELLED', 'supervisor,subscriber', 0),
(104, '4', 'LEAVE TYPE DELETED SCHEDULED', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(105, '4', 'LEAVE TYPE DELETED SCHEDULED', 'SUPERVISOR', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0),
(106, '4', 'LEAVE TYPE DELETED TAKEN', 'ADMIN', 'CANCEL', 'CANCELLED', 'ess,subscriber', 0);
INSERT INTO `ohrm_report_group` (`report_group_id`, `name`, `core_sql`) VALUES
(1,'timesheet', 'SELECT selectCondition FROM ohrm_project_activity LEFT JOIN (SELECT * FROM ohrm_timesheet_item WHERE whereCondition1) AS ohrm_timesheet_item ON (ohrm_timesheet_item.activity_id = ohrm_project_activity.activity_id) LEFT JOIN ohrm_project ON (ohrm_project.project_id = ohrm_project_activity.project_id) LEFT JOIN hs_hr_employee ON (hs_hr_employee.emp_number = ohrm_timesheet_item.employee_id) LEFT JOIN ohrm_timesheet ON (ohrm_timesheet.timesheet_id = ohrm_timesheet_item.timesheet_id) LEFT JOIN ohrm_customer ON (ohrm_customer.customer_id = ohrm_project.customer_id) WHERE whereCondition2 groupByClause ORDER BY ohrm_customer.name, ohrm_project.name, ohrm_project_activity.name, hs_hr_employee.emp_lastname, hs_hr_employee.emp_firstname'),
(2,'attendance', 'SELECT selectCondition FROM hs_hr_employee LEFT JOIN (SELECT * FROM ohrm_attendance_record WHERE ( ( ohrm_attendance_record.punch_in_user_time BETWEEN "#@fromDate@,@1970-01-01@#" AND #@"toDate"@,@CURDATE()@# ) AND ( ohrm_attendance_record.punch_out_user_time BETWEEN "#@fromDate@,@1970-01-01@#" AND #@"toDate"@,@CURDATE()@# ) ) ) AS ohrm_attendance_record ON (hs_hr_employee.emp_number = ohrm_attendance_record.employee_id) WHERE hs_hr_employee.emp_number = #@employeeId@,@hs_hr_employee.emp_number AND (hs_hr_employee.termination_id is null) @# AND (hs_hr_employee.job_title_code = #@"jobTitle")@,@hs_hr_employee.job_title_code OR hs_hr_employee.job_title_code is null)@# AND (hs_hr_employee.work_station IN (#@subUnit)@,@SELECT id FROM ohrm_subunit) OR hs_hr_employee.work_station is null@#) AND (hs_hr_employee.emp_status = #@"employeeStatus")@,@hs_hr_employee.emp_status OR hs_hr_employee.emp_status is null)@# groupByClause ORDER BY hs_hr_employee.emp_lastname, hs_hr_employee.emp_firstname'),
(3,'pim', 'SELECT selectCondition FROM hs_hr_employee
LEFT JOIN hs_hr_emp_emergency_contacts ON
(hs_hr_employee.emp_number = hs_hr_emp_emergency_contacts.emp_number)
LEFT JOIN ohrm_subunit ON
(hs_hr_employee.work_station = ohrm_subunit.id)
LEFT JOIN ohrm_employment_status ON
(hs_hr_employee.emp_status = ohrm_employment_status.id)
LEFT JOIN ohrm_job_title ON
(hs_hr_employee.job_title_code = ohrm_job_title.id)
LEFT JOIN ohrm_job_category ON
(hs_hr_employee.eeo_cat_code = ohrm_job_category.id)
LEFT JOIN ohrm_nationality ON
(hs_hr_employee.nation_code = ohrm_nationality.id)
LEFT JOIN hs_hr_emp_dependents ON
(hs_hr_employee.emp_number = hs_hr_emp_dependents.emp_number)
LEFT JOIN hs_hr_emp_locations AS emp_location ON
(hs_hr_employee.emp_number = emp_location.emp_number)
LEFT JOIN ohrm_location ON
(emp_location.location_id = ohrm_location.id)
LEFT JOIN hs_hr_emp_contract_extend ON
(hs_hr_employee.emp_number = hs_hr_emp_contract_extend.emp_number)
LEFT JOIN hs_hr_emp_basicsalary ON
(hs_hr_employee.emp_number = hs_hr_emp_basicsalary.emp_number)
LEFT JOIN ohrm_pay_grade ON
(hs_hr_emp_basicsalary.sal_grd_code = ohrm_pay_grade.id)
LEFT JOIN hs_hr_currency_type ON
(hs_hr_emp_basicsalary.currency_id = hs_hr_currency_type.currency_id)
LEFT JOIN hs_hr_payperiod ON
(hs_hr_emp_basicsalary.payperiod_code = hs_hr_payperiod.payperiod_code)
LEFT JOIN hs_hr_emp_passport ON
(hs_hr_employee.emp_number = hs_hr_emp_passport.emp_number)
LEFT JOIN hs_hr_emp_reportto AS subordinate_list ON
(hs_hr_employee.emp_number = subordinate_list.erep_sup_emp_number)
LEFT JOIN hs_hr_employee AS subordinate ON
(subordinate.emp_number = subordinate_list.erep_sub_emp_number)
LEFT JOIN ohrm_emp_reporting_method AS subordinate_reporting_method ON
(subordinate_list.erep_reporting_mode = subordinate_reporting_method.reporting_method_id)
LEFT JOIN hs_hr_emp_work_experience ON
(hs_hr_employee.emp_number = hs_hr_emp_work_experience.emp_number)
LEFT JOIN ohrm_emp_education ON
(hs_hr_employee.emp_number = ohrm_emp_education.emp_number)
LEFT JOIN ohrm_education ON
(ohrm_emp_education.education_id = ohrm_education.id)
LEFT JOIN hs_hr_emp_skill ON
(hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number)
LEFT JOIN ohrm_skill ON
(hs_hr_emp_skill.skill_id = ohrm_skill.id)
LEFT JOIN hs_hr_emp_language ON
(hs_hr_employee.emp_number = hs_hr_emp_language.emp_number)
LEFT JOIN ohrm_language ON
(hs_hr_emp_language.lang_id = ohrm_language.id)
LEFT JOIN ohrm_emp_license ON
(hs_hr_employee.emp_number = ohrm_emp_license.emp_number)
LEFT JOIN ohrm_license ON
(ohrm_emp_license.license_id = ohrm_license.id)
LEFT JOIN hs_hr_emp_member_detail ON
(hs_hr_employee.emp_number = hs_hr_emp_member_detail.emp_number)
LEFT JOIN ohrm_membership ON
(hs_hr_emp_member_detail.membship_code = ohrm_membership.id)
LEFT JOIN hs_hr_country ON
(hs_hr_employee.coun_code = hs_hr_country.cou_code)
LEFT JOIN hs_hr_emp_directdebit ON
(hs_hr_emp_basicsalary.id = hs_hr_emp_directdebit.salary_id)
LEFT JOIN hs_hr_emp_reportto AS supervisor_list ON
(hs_hr_employee.emp_number = supervisor_list.erep_sub_emp_number)
LEFT JOIN hs_hr_employee AS supervisor ON
(supervisor.emp_number = supervisor_list.erep_sup_emp_number)
LEFT JOIN ohrm_emp_reporting_method AS supervisor_reporting_method ON
(supervisor_list.erep_reporting_mode = supervisor_reporting_method.reporting_method_id)
LEFT JOIN ohrm_emp_termination ON
(hs_hr_employee.termination_id = ohrm_emp_termination.id)
LEFT JOIN ohrm_emp_termination_reason ON
(ohrm_emp_termination.reason_id = ohrm_emp_termination_reason.id)
WHERE hs_hr_employee.emp_number in (
SELECT hs_hr_employee.emp_number FROM hs_hr_employee
LEFT JOIN hs_hr_emp_basicsalary ON
(hs_hr_employee.emp_number = hs_hr_emp_basicsalary.emp_number)
LEFT JOIN ohrm_emp_education ON
(hs_hr_employee.emp_number = ohrm_emp_education.emp_number)
LEFT JOIN hs_hr_emp_skill ON
(hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number)
LEFT JOIN hs_hr_emp_language ON
(hs_hr_employee.emp_number = hs_hr_emp_language.emp_number)
WHERE whereCondition1
)
GROUP BY
hs_hr_employee.emp_number,
hs_hr_employee.emp_lastname,
hs_hr_employee.emp_firstname,
hs_hr_employee.emp_middle_name,
hs_hr_employee.emp_birthday,
ohrm_nationality.name,
hs_hr_employee.emp_gender,
hs_hr_employee.emp_marital_status,
hs_hr_employee.emp_dri_lice_num,
hs_hr_employee.emp_dri_lice_exp_date,
hs_hr_employee.emp_street1,
hs_hr_employee.emp_street2,
hs_hr_employee.city_code,
hs_hr_employee.provin_code,
hs_hr_employee.emp_zipcode,
hs_hr_country.cou_code,
hs_hr_employee.emp_hm_telephone,
hs_hr_employee.emp_mobile,
hs_hr_employee.emp_work_telephone,
hs_hr_employee.emp_work_email,
hs_hr_employee.emp_oth_email
ORDER BY hs_hr_employee.emp_lastname
');
INSERT INTO `ohrm_report` (`report_id`, `name`, `report_group_id`, `use_filter_field`, `type`) VALUES (1, 'Project Report', 1, 1, null),
(2, 'Employee Report', 1, 1, null),
(3, 'Project Activity Details', 1, 1, null),
(4, 'Attendance Total Summary Report', 2, 0, null),
(5, 'PIM Sample Report', 3, 1, 'PIM_DEFINED');
INSERT INTO `ohrm_filter_field` (`filter_field_id`, `report_group_id`, `name`, `where_clause_part`, `filter_field_widget`, `condition_no`, `required`) VALUES
(1, 1, 'project_name', 'ohrm_project.project_id', 'ohrmWidgetProjectList', 2, 'true'),
(2, 1, 'activity_show_deleted', 'ohrm_project_activity.is_deleted', 'ohrmWidgetInputCheckbox', 2, 'false'),
(3, 1, 'project_date_range', 'date', 'ohrmWidgetDateRange', 1, 'false'),
(4, 1, 'employee', 'hs_hr_employee.emp_number', 'ohrmReportWidgetEmployeeListAutoFill', 2, 'true'),
(5, 1, 'activity_name', 'ohrm_project_activity.activity_id', 'ohrmWidgetProjectActivityList', 2, 'true'),
(6, 1, 'project_name', 'ohrm_project.project_id', 'ohrmWidgetProjectListWithAllOption', 2, 'true'),
(7, 1, 'only_include_approved_timesheets', 'ohrm_timesheet.state', 'ohrmWidgetApprovedTimesheetInputCheckBox', 2, null),
(8, 3, 'employee_name', 'hs_hr_employee.emp_number', 'ohrmReportWidgetEmployeeListAutoFill', 1, null),
(9, 3, 'pay_grade', 'hs_hr_emp_basicsalary.sal_grd_code', 'ohrmReportWidgetPayGradeDropDown', 1, null),
(10, 3, 'education', 'ohrm_emp_education.education_id', 'ohrmReportWidgetEducationtypeDropDown', 1, null),
(11, 3, 'employment_status', 'hs_hr_employee.emp_status', 'ohrmWidgetEmploymentStatusList', 1, null),
(12, 3, 'service_period', 'datediff(current_date(), hs_hr_employee.joined_date)/365', 'ohrmReportWidgetServicePeriod', 1, null),
(13, 3, 'joined_date', 'hs_hr_employee.joined_date', 'ohrmReportWidgetJoinedDate', 1, null),
(14, 3, 'job_title', 'hs_hr_employee.job_title_code', 'ohrmWidgetJobTitleList', 1, null),
(15, 3, 'language', 'hs_hr_emp_language.lang_id', 'ohrmReportWidgetLanguageDropDown', 1, null),
(16, 3, 'skill', 'hs_hr_emp_skill.skill_id', 'ohrmReportWidgetSkillDropDown', 1, null),
(17, 3, 'age_group', 'datediff(current_date(), hs_hr_employee.emp_birthday)/365', 'ohrmReportWidgetAgeGroup', 1, null),
(18, 3, 'sub_unit', 'hs_hr_employee.work_station', 'ohrmWidgetSubDivisionList', 1, null),
(19, 3, 'gender', 'hs_hr_employee.emp_gender', 'ohrmReportWidgetGenderDropDown', 1, null),
(20, 3, 'location', 'ohrm_location.id', 'ohrmReportWidgetOperationalCountryLocationDropDown', 1, null),
(21, 1, 'is_deleted', 'ohrm_project_activity.is_deleted', '', 2, null),
(22, 3, 'include', 'hs_hr_employee.termination_id', 'ohrmReportWidgetIncludedEmployeesDropDown', 1, 'true');
INSERT INTO `ohrm_display_field_group`(`id`, `report_group_id`, `name`, `is_list`) VALUES
(1, 3, 'Personal', false),
(2, 3, 'Contact Details', false),
(3, 3, 'Emergency Contacts', true),
(4, 3, 'Dependents', true),
(5, 3, 'Immigration', true),
(6, 3, 'Job', false),
(7, 3, 'Salary', true),
(8, 3, 'Subordinates', true),
(9, 3, 'Supervisors', true),
(10, 3, 'Work Experience', true),
(11, 3, 'Education', true),
(12, 3, 'Skills', true),
(13, 3, 'Languages', true),
(14, 3, 'License', true),
(15, 3, 'Memberships', true),
(16, 3, 'Custom Fields', false);
INSERT INTO `ohrm_display_field` (`display_field_id`, `report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
(1, 1, 'ohrm_project.name', 'Project Name', 'projectname', 'false', null, null, 'label', 'projectname', 200, '0', null, false, null, null, false, false),
(2, 1, 'ohrm_project_activity.name', 'Activity Name', 'activityname', 'false', null, null, 'link', 'activitynameactivity_idtotaldurationprojectIdfromDatetoDateonlyIncludeApprovedTimesheets../../displayProjectActivityDetailsReport?reportId=3#activityId={id}#total={total}#from={from}#to={to}#projectId={projectId}#onlyIncludeApprovedTimesheets={approved}', 200, '0', null, false, null, null, false, false),
(3, 1, 'ohrm_project_activity.project_id', 'Project Id', null, 'false', null, null, 'label', 'project_id', 75, '0', 'right', false, null, null, false, true),
(4, 1, 'ohrm_project_activity.activity_id', 'Activity Id', null, 'false', null, null, 'label', 'activity_id', 75, '0', 'right', false, null, null, false, true),
(5, 1, 'ohrm_timesheet_item.duration', 'Time (hours)', null, 'false', null, null, 'label', 'duration', 75, '0', 'right', false, null, null, false, false),
(6, 1, 'hs_hr_employee.emp_firstname', 'Employee First Name', null, 'false', null, null, 'label', 'emp_firstname', 200, '0', null, false, null, null, false, false),
(7, 1, 'hs_hr_employee.emp_lastname', 'Employee Last Name', null, 'false', null, null, 'label', 'emp_lastname', 200, '0', null, false, null, null, false, false),
(8, 1, 'ohrm_project_activity.name', 'Activity Name', 'activityname', 'false', null, null, 'label', 'activityname', 200, '0', null, false, null, null, false, false),
(9, 3, 'hs_hr_employee.employee_id', 'Employee Id', 'employeeId', 'false', null, null, 'label', 'employeeId', 100, '0', null, false, 1, '---', false, false),
(10, 3, 'hs_hr_employee.emp_lastname', 'Employee Last Name', 'employeeLastname', 'false', null, null, 'label', 'employeeLastname', 200, '0', null, false, 1, '---', false, false),
(11, 3, 'hs_hr_employee.emp_firstname', 'Employee First Name','employeeFirstname', 'false', null, null, 'label', 'employeeFirstname', 200, '0', null, false, 1, '---', false, false),
(12, 3, 'hs_hr_employee.emp_middle_name', 'Employee Middle Name', 'employeeMiddlename', 'false', null, null, 'label', 'employeeMiddlename', 200, '0', null, false, 1, '---', false, false),
(13, 3, 'hs_hr_employee.emp_birthday', 'Date of Birth', 'empBirthday', 'false', null, null, 'labelDate', 'empBirthday', 100, '0', null, false, 1, '---', false, false),
(14, 3, 'ohrm_nationality.name', 'Nationality', 'nationality', 'false', null, null, 'label', 'nationality', 200, '0', null, false, 1, '---', false, false),
(15, 3, 'CASE hs_hr_employee.emp_gender WHEN 1 THEN "Male" WHEN 2 THEN "Female" WHEN 3 THEN "Other" END', 'Gender', 'empGender', 'false', null, null, 'label', 'empGender', 80, '0', null, false, 1, '---', false, false),
(17, 3, 'hs_hr_employee.emp_marital_status', 'Marital Status', 'maritalStatus', 'false', null, null, 'label', 'maritalStatus', 100, '0', null, false, 1, '---', false, false),
(18, 3, 'hs_hr_employee.emp_dri_lice_num', 'Driver License Number', 'driversLicenseNumber', 'false', null, null, 'label', 'driversLicenseNumber', 240, '0', null, false, 1, '---', false, false),
(19, 3, 'hs_hr_employee.emp_dri_lice_exp_date', 'License Expiry Date', 'licenseExpiryDate', 'false', null, null, 'labelDate', 'licenseExpiryDate', 135, '0', null, false, 1, '---', false, false),
(20, 3, 'CONCAT_WS(", ", NULLIF(hs_hr_employee.emp_street1, ""), NULLIF(hs_hr_employee.emp_street2, ""), NULLIF(hs_hr_employee.city_code, ""), NULLIF(hs_hr_employee.provin_code,""), NULLIF(hs_hr_employee.emp_zipcode,""), NULLIF(hs_hr_country.cou_name,""))', 'Address', 'address', 'false', null, null, 'label', 'address', 200, '0', null, false, 2, '---', false, false),
(21, 3, 'hs_hr_employee.emp_hm_telephone', 'Home Telephone', 'homeTelephone', 'false', null, null, 'label', 'homeTelephone', 130, '0', null, false, 2, '---', false, false),
(22, 3, 'hs_hr_employee.emp_mobile', 'Mobile', 'mobile', 'false', null, null, 'label', 'mobile', 100, '0', null, false, 2, '---', false, false),
(23, 3, 'hs_hr_employee.emp_work_telephone', 'Work Telephone', 'workTelephone', 'false', null, null, 'label', 'workTelephone', 100, '0', null, false, 2, '---', false, false),
(24, 3, 'hs_hr_employee.emp_work_email', 'Work Email', 'workEmail', 'false', null, null, 'label', 'workEmail', 200, '0', null, false, 2, '---', false, false),
(25, 3, 'hs_hr_employee.emp_oth_email', 'Other Email', 'otherEmail', 'false', null, null, 'label', 'otherEmail', 200, '0', null, false, 2, '---', false, false),
(26, 3, 'hs_hr_emp_emergency_contacts.eec_name', 'Name', 'ecname', 'false', null, null, 'label', 'ecname', 200, '0', null, true, 3, '---', false, false),
(27, 3, 'hs_hr_emp_emergency_contacts.eec_home_no', 'Home Telephone', 'ecHomeTelephone', 'false', null, null, 'label', 'ecHomeTelephone', 130, '0', null, true, 3, '---', false, false),
(28, 3, 'hs_hr_emp_emergency_contacts.eec_office_no', 'Work Telephone', 'ecWorkTelephone', 'false', null, null, 'label', 'ecWorkTelephone', 100, '0', null, true, 3, '---', false, false),
(29, 3, 'hs_hr_emp_emergency_contacts.eec_relationship', 'Relationship', 'ecRelationship', 'false', null, null, 'label', 'ecRelationship', 200, '0', null, true, 3, '---', false, false),
(30, 3, 'hs_hr_emp_emergency_contacts.eec_mobile_no', 'Mobile', 'ecMobile', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 3, '---', false, false),
(31, 3, 'hs_hr_emp_dependents.ed_name', 'Name', 'dependentName', 'false', null, null, 'label', 'dependentName', 200, '0', null, true, 4, '---', false, false),
(32, 3, 'IF (hs_hr_emp_dependents.ed_relationship_type = \'other\', hs_hr_emp_dependents.ed_relationship, hs_hr_emp_dependents.ed_relationship_type)', 'Relationship', 'dependentRelationship', 'false', null, null, 'label', 'dependentRelationship', 200, '0', null, true, 4, '---', false, false),
(33, 3, 'hs_hr_emp_dependents.ed_date_of_birth', 'Date of Birth', 'dependentDateofBirth', 'false', null, null, 'labelDate', 'dependentDateofBirth', 100, '0', null, true, 4, '---', false, false),
(35, 3, 'ohrm_membership.name', 'Membership', 'name', 'false', null, null, 'label', 'name', 200, '0', null, true, 15, '---', false, false),
(36, 3, 'hs_hr_emp_member_detail.ememb_subscript_ownership', 'Subscription Paid By', 'subscriptionPaidBy', 'false', null, null, 'label', 'subscriptionPaidBy', 200, '0', null, true, 15, '---', false, false),
(37, 3, 'hs_hr_emp_member_detail.ememb_subscript_amount', 'Subscription Amount', 'subscriptionAmount', 'false', null, null, 'label', 'subscriptionAmount', 200, '0', null, true, 15, '---', false, false),
(38, 3, 'hs_hr_emp_member_detail.ememb_subs_currency', 'Currency', 'membershipCurrency', 'false', null, null, 'label', 'membershipCurrency', 200, '0', null, true, 15, '---', false, false),
(39, 3, 'hs_hr_emp_member_detail.ememb_commence_date', 'Subscription Commence Date', 'subscriptionCommenceDate', 'false', null, null, 'labelDate', 'subscriptionCommenceDate', 200, '0', null, true, 15, '---', false, false),
(40, 3, 'hs_hr_emp_member_detail.ememb_renewal_date', 'Subscription Renewal Date', 'subscriptionRenewalDate', 'false', null, null, 'labelDate', 'subscriptionRenewalDate', 200, '0', null, true, 15, '---', false, false),
(41, 3, 'hs_hr_emp_work_experience.eexp_employer', 'Company', 'expCompany', 'false', null, null, 'label', 'expCompany', 200, '0', null, true, 10, '---', false, false),
(42, 3, 'hs_hr_emp_work_experience.eexp_jobtit', 'Job Title', 'expJobTitle', 'false', null, null, 'label', 'expJobTitle', 200, '0', null, true, 10, '---', false, false),
(43, 3, 'hs_hr_emp_work_experience.eexp_from_date', 'From', 'expFrom', 'false', null, null, 'labelDate', 'expFrom', 100, '0', null, true, 10, '---', false, false),
(44, 3, 'hs_hr_emp_work_experience.eexp_to_date', 'To', 'expTo', 'false', null, null, 'labelDate', 'expTo', 100, '0', null, true, 10, '---', false, false),
(45, 3, 'hs_hr_emp_work_experience.eexp_comments', 'Comment', 'expComment', 'false', null, null, 'label', 'expComment', 200, '0', null, true, 10, '---', false, false),
(47, 3, 'ohrm_education.name', 'Level', 'eduProgram', 'false', null, null, 'label', 'eduProgram', 200, '0', null, true, 11, '---', false, false),
(48, 3, 'ohrm_emp_education.year', 'Year', 'eduYear', 'false', null, null, 'label', 'eduYear', 100, '0', null, true, 11, '---', false, false),
(49, 3, 'ohrm_emp_education.score', 'Score', 'eduGPAOrScore', 'false', null, null, 'label', 'eduGPAOrScore', 80, '0', null, true, 11, '---', false, false),
(52, 3, 'ohrm_skill.name', 'Skill', 'skill', 'false', null, null, 'label', 'skill', 200, '0', null, true, 12, '---', false, false),
(53, 3, 'hs_hr_emp_skill.years_of_exp', 'Years of Experience', 'skillYearsOfExperience', 'false', null, null, 'label', 'skillYearsOfExperience', 135, '0', null, true, 12, '---', false, false),
(54, 3, 'hs_hr_emp_skill.comments', 'Comments', 'skillComments', 'false', null, null, 'label', 'skillComments', 200, '0', null, true, 12, '---', false, false),
(55, 3, 'ohrm_language.name', 'Language', 'langName', 'false', null, null, 'label', 'langName', 200, '0', null, true, 13, '---', false, false),
(57, 3, 'CASE hs_hr_emp_language.competency WHEN 1 THEN "Poor" WHEN 2 THEN "Basic" WHEN 3 THEN "Good" WHEN 4 THEN "Mother Tongue" END', 'Competency', 'langCompetency', 'false', null, null, 'label', 'langCompetency', 130, '0', null, true, 13, '---', false, false),
(58, 3, 'hs_hr_emp_language.comments', 'Comments', 'langComments', 'false', null, null, 'label', 'langComments', 200, '0', null, true, 13, '---', false, false),
(59, 3, 'ohrm_license.name', 'License Type', 'empLicenseType', 'false', null, null, 'label', 'empLicenseType', 200, '0', null, true, 14, '---', false, false),
(60, 3, 'ohrm_emp_license.license_issued_date', 'Issued Date', 'empLicenseIssuedDate', 'false', null, null, 'labelDate', 'empLicenseIssuedDate', 100, '0', null, true, 14, '---', false, false),
(61, 3, 'ohrm_emp_license.license_expiry_date', 'Expiry Date', 'empLicenseExpiryDate', 'false', null, null, 'labelDate', 'empLicenseExpiryDate', 100, '0', null, true, 14, '---', false, false),
(62, 3, 'supervisor.emp_firstname', 'First Name', 'supervisorFirstName', 'false', null, null, 'label', 'supervisorFirstName', 200, '0', null, true, 9, '---', false, false),
(63, 3, 'subordinate.emp_firstname', 'First Name', 'subordinateFirstName', 'false', null, null, 'label', 'subordinateFirstName', 200, '0', null, true, 8, '---', false, false),
(64, 3, 'supervisor.emp_lastname', 'Last Name', 'supervisorLastName', 'false', null, null, 'label', 'supervisorLastName', 200, '0', null, true, 9, '---', false, false),
(65, 3, 'ohrm_pay_grade.name', 'Pay Grade', 'salPayGrade', 'false', null, null, 'label', 'salPayGrade', 200, '0', null, true, 7, '---', false, false),
(66, 3, 'hs_hr_emp_basicsalary.salary_component', 'Salary Component', 'salSalaryComponent', 'false', null, null, 'label', 'salSalaryComponent', 200, '0', null, true, 7, '---', false, false),
(67, 3, 'hs_hr_emp_basicsalary.ebsal_basic_salary', 'Amount', 'salAmount', 'false', null, null, 'label', 'salAmount', 200, '0', null, true, 7, '---', true, false),
(68, 3, 'hs_hr_emp_basicsalary.comments', 'Comments', 'salComments', 'false', null, null, 'label', 'salComments', 200, '0', null, true, 7, '---', false, false),
(69, 3, 'hs_hr_payperiod.payperiod_name', 'Pay Frequency', 'salPayFrequency', 'false', null, null, 'label', 'salPayFrequency', 200, '0', null, true, 7, '---', false, false),
(70, 3, 'hs_hr_currency_type.currency_name', 'Currency', 'salCurrency', 'false', null, null, 'label', 'salCurrency', 200, '0', null, true, 7, '---', false, false),
(71, 3, 'hs_hr_emp_directdebit.dd_account', 'Direct Deposit Account Number', 'ddAccountNumber', 'false', null, null, 'label', 'ddAccountNumber', 200, '0', null, true, 7, '---', false, false),
(72, 3, 'hs_hr_emp_directdebit.dd_account_type', 'Direct Deposit Account Type', 'ddAccountType', 'false', null, null, 'label', 'ddAccountType', 200, '0', null, true, 7, '---', false, false),
(73, 3, 'hs_hr_emp_directdebit.dd_routing_num', 'Direct Deposit Routing Number', 'ddRoutingNumber', 'false', null, null, 'label', 'ddRoutingNumber', 200, '0', null, true, 7, '---', false, false),
(74, 3, 'hs_hr_emp_directdebit.dd_amount', 'Direct Deposit Amount', 'ddAmount', 'false', null, null, 'label', 'ddAmount', 200, '0', null, true, 7, '---', false, false),
(75, 3, 'hs_hr_emp_contract_extend.econ_extend_start_date', 'Contract Start Date', 'empContStartDate', 'false', null, null, 'labelDate', 'empContStartDate', 200, '0', null, true, 6, '---', false, false),
(76, 3, 'hs_hr_emp_contract_extend.econ_extend_end_date', 'Contract End Date', 'empContEndDate', 'false', null, null, 'labelDate', 'empContEndDate', 200, '0', null, true, 6, '---', false, false),
(77, 3, 'ohrm_job_title.job_title', 'Job Title', 'empJobTitle', 'false', null, null, 'label', 'empJobTitle', 200, '0', null, true, 6, '---', false, false),
(78, 3, 'ohrm_employment_status.name', 'Employment Status', 'empEmploymentStatus', 'false', null, null, 'label', 'empEmploymentStatus', 200, '0', null, true, 6, '---', false, false),
(80, 3, 'ohrm_job_category.name', 'Job Category', 'empJobCategory', 'false', null, null, 'label', 'empJobCategory', 200, '0', null, true, 6, '---', false, false),
(81, 3, 'hs_hr_employee.joined_date', 'Joined Date', 'empJoinedDate', 'false', null, null, 'labelDate', 'empJoinedDate', 100, '0', null, true, 6, '---', false, false),
(82, 3, 'ohrm_subunit.name', 'Sub Unit', 'empSubUnit', 'false', null, null, 'label', 'empSubUnit', 200, '0', null, true, 6, '---', false, false),
(83, 3, 'ohrm_location.name', 'Location', 'empLocation', 'false', null, null, 'label', 'empLocation', 200, '0', null, true, 6, '---', false, false),
(84, 3, 'hs_hr_emp_passport.ep_passport_num', 'Number', 'empPassportNo', 'false', null, null, 'label', 'empPassportNo', 200, '0', null, true, 5, '---', false, false),
(85, 3, 'hs_hr_emp_passport.ep_passportissueddate', 'Issued Date', 'empPassportIssuedDate', 'false', null, null, 'labelDate', 'empPassportIssuedDate', 100, '0', null, true, 5, '---', false, false),
(86, 3, 'hs_hr_emp_passport.ep_passportexpiredate', 'Expiry Date', 'empPassportExpiryDate', 'false', null, null, 'labelDate', 'empPassportExpiryDate', 100, '0', null, true, 5, '---', false, false),
(87, 3, 'hs_hr_emp_passport.ep_i9_status', 'Eligibility Status', 'empPassportEligibleStatus', 'false', null, null, 'label', 'empPassportEligibleStatus', 200, '0', null, true, 5, '---', false, false),
(88, 3, 'hs_hr_emp_passport.cou_code', 'Issued By', 'empPassportIssuedBy', 'false', null, null, 'label', 'empPassportIssuedBy', 200, '0', null, true, 5, '---', false, false),
(89, 3, 'hs_hr_emp_passport.ep_i9_review_date', 'Eligibility Review Date', 'empPassportEligibleReviewDate', 'false', null, null, 'labelDate', 'empPassportEligibleReviewDate', 200, '0', null, true, 5, '---', false, false),
(90, 3, 'hs_hr_emp_passport.ep_comments', 'Comments', 'empPassportComments', 'false', null, null, 'label', 'empPassportComments', 200, '0', null, true, 5, '---', false, false),
(91, 3, 'subordinate.emp_lastname', 'Last Name', 'subordinateLastName', 'false', null, null, 'label', 'subordinateLastName', 200, '0', null, true, 8, '---', false, false),
(92, 3, 'CASE hs_hr_emp_language.fluency WHEN 1 THEN "Writing" WHEN 2 THEN "Speaking" WHEN 3 THEN "Reading" END', 'Fluency', 'langFluency', 'false', null, null, 'label', 'langFluency', 200, '0', null, true, 13, '---', false, false),
(93, 3, 'supervisor_reporting_method.reporting_method_name', 'Reporting Method', 'supReportingMethod', 'false', null, null, 'label', 'supReportingMethod', 200, '0', null, true, 9, '---', false, false),
(94, 3, 'subordinate_reporting_method.reporting_method_name', 'Reporting Method', 'subReportingMethod', 'false', null, null, 'label', 'subReportingMethod', 200, '0', null, true, 8, '---', false, false),
(95, 3, 'CASE hs_hr_emp_passport.ep_passport_type_flg WHEN 1 THEN "Passport" WHEN 2 THEN "Visa" END', 'Document Type', 'documentType', 'false', null, null, 'label', 'documentType', 200, '0', null, true, 5, '---', false, false),
(97, 3, 'hs_hr_employee.emp_other_id', 'Other Id', 'otherId', 'false', null, null, 'label', 'otherId', 100, '0', null, false, 1, '---', false, false),
(98, 3, 'hs_hr_emp_emergency_contacts.eec_seqno', 'ecSeqNo', 'ecSeqNo', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 3, '---', false, true),
(99, 3, 'hs_hr_emp_dependents.ed_seqno', 'SeqNo', 'edSeqNo', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 4, '---', false, true),
(100, 3, 'hs_hr_emp_passport.ep_seqno', 'SeqNo', 'epSeqNo', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 5, '---', false, true),
(101, 3, 'hs_hr_emp_basicsalary.id', 'salaryId', 'salaryId', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 7, '---', false, true),
(102, 3, 'subordinate.emp_number', 'subordinateId', 'subordinateId', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 8, '---', false, true),
(103, 3, 'supervisor.emp_number', 'supervisorId', 'supervisorId', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 9, '---', false, true),
(104, 3, 'hs_hr_emp_work_experience.eexp_seqno', 'workExpSeqNo', 'workExpSeqNo', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 10, '---', false, true),
(105, 3, 'ohrm_emp_education.education_id', 'empEduCode', 'empEduCode', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 11, '---', false, true),
(106, 3, 'hs_hr_emp_skill.skill_id', 'empSkillCode', 'empSkillCode', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 12, '---', false, true),
(107, 3, 'hs_hr_emp_language.lang_id', 'empLangCode', 'empLangCode', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 13, '---', false, true),
(108, 3, 'hs_hr_emp_language.fluency', 'empLangType', 'empLangType', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 13, '---', false, true),
(109, 3, 'ohrm_emp_license.license_id', 'empLicenseCode', 'empLicenseCode', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 14, '---', false, true),
(110, 3, 'hs_hr_emp_member_detail.membship_code', 'membershipCode', 'membershipCode', 'false', null, null, 'label', 'ecMobile', 100, '0', null, true, 15, '---', false, true),
(112, 3, 'ROUND(DATEDIFF(hs_hr_emp_work_experience.eexp_to_date, hs_hr_emp_work_experience.eexp_from_date)/365,1)', 'Duration', 'expDuration', 'false', null, null, 'label', 'expDuration', 100, '0', null, true, 10, '---', false, false),
(113, 3, 'ohrm_emp_termination.termination_date', 'Termination Date', 'terminationDate', 'false', null, null, 'labelDate', 'terminationDate', 100, '0', null, true, 6, '---', false, false),
(114, 3, 'ohrm_emp_termination_reason.name', 'Termination Reason', 'terminationReason', 'false', null, null, 'label', 'terminationReason', 100, '0', null, true, 6, '---', false, false),
(115, 3, 'ohrm_emp_education.institute', 'Institute', 'getInstitute', 'false', null, null, 'label', 'getInstitute', 80, '0', null, true, 11, '---', false, false),
(116, 3, 'ohrm_emp_education.major', 'Major/Specialization', 'getMajor', 'false', null, null, 'label', 'getMajor', 80, '0', null, true, 11, '---', false, false),
(117, 3, 'ohrm_emp_education.start_date', 'Start Date', 'getStartDate', 'false', null, null, 'labelDate', 'getStartDate', 80, '0', null, true, 11, '---', false, false),
(118, 3, 'ohrm_emp_education.end_date', 'End Date', 'getEndDate', 'false', null, null, 'labelDate', 'getEndDate', 80, '0', null, true, 11, '---', false, false),
(119, 3, 'ohrm_emp_license.license_no', 'License Number', 'getLicenseNo', 'false', null, null, 'label', 'getLicenseNo', 200, '0', null, true, 14, '---', false, false),
(120, 3, 'ohrm_emp_termination.note', 'Termination Note', 'getNote', 'false', null, null, 'label', 'getNote', 100, '0', null, true, 6, '---', false, false);
INSERT INTO `ohrm_group_field` (`group_field_id`, `name`, `group_by_clause`, `group_field_widget`) VALUES
(1, 'activity id', 'GROUP BY ohrm_project_activity.activity_id', null),
(2, 'employee number', 'GROUP BY hs_hr_employee.emp_number', null);
INSERT INTO `ohrm_selected_filter_field` (`report_id`, `filter_field_id`, `filter_field_order`, `value1`, `value2`, `where_condition`, `type`) VALUES
(1, 1, 1, null, null, null, 'Runtime'),
(1, 3, 2, null, null, null, 'Runtime'),
(1, 7, 3, null, null, null, 'Runtime'),
(1, 21, 4, '0', null, '=', 'Predefined'),
(2, 3, 4, null, null, null, 'Runtime'),
(2, 4, 1, null, null, null, 'Runtime'),
(2, 5, 3, null, null, null, 'Runtime'),
(2, 6, 2, null, null, null, 'Runtime'),
(2, 7, 5, null, null, null, 'Runtime'),
(3, 3, 2, null, null, null, 'Runtime'),
(3, 5, 1, null, null, null, 'Runtime'),
(3, 7, 3, null, null, null, 'Runtime'),
(5, 22, 1, null, null, 'IS NULL', 'Predefined');
INSERT INTO `ohrm_selected_display_field` (`id`, `display_field_id`, `report_id`) VALUES
(2, 2, 1),
(4, 8, 2),
(5, 9, 5),
(6, 10, 5),
(7, 11, 5),
(8, 12, 5),
(9, 13, 5),
(10, 14, 5),
(11, 15, 5),
(13, 17, 5),
(14, 18, 5),
(15, 19, 5),
(16, 20, 5),
(17, 21, 5),
(18, 22, 5),
(19, 23, 5),
(20, 24, 5),
(21, 25, 5),
(22, 26, 5),
(23, 27, 5),
(24, 28, 5),
(25, 29, 5),
(26, 30, 5),
(27, 31, 5),
(28, 32, 5),
(29, 33, 5),
(31, 35, 5),
(32, 36, 5),
(33, 37, 5),
(34, 38, 5),
(35, 39, 5),
(36, 40, 5),
(37, 41, 5),
(38, 42, 5),
(39, 43, 5),
(40, 44, 5),
(41, 45, 5),
(43, 47, 5),
(44, 48, 5),
(45, 49, 5),
(48, 52, 5),
(49, 53, 5),
(50, 54, 5),
(51, 55, 5),
(53, 57, 5),
(54, 58, 5),
(55, 59, 5),
(56, 60, 5),
(57, 61, 5),
(58, 62, 5),
(59, 63, 5),
(60, 64, 5),
(61, 65, 5),
(62, 66, 5),
(63, 67, 5),
(64, 68, 5),
(65, 69, 5),
(66, 70, 5),
(67, 71, 5),
(68, 72, 5),
(69, 73, 5),
(70, 74, 5),
(71, 75, 5),
(72, 76, 5),
(73, 77, 5),
(74, 78, 5),
(76, 80, 5),
(77, 81, 5),
(78, 82, 5),
(79, 83, 5),
(80, 84, 5),
(81, 85, 5),
(82, 86, 5),
(83, 87, 5),
(84, 88, 5),
(85, 89, 5),
(86, 90, 5),
(87, 91, 5),
(88, 92, 5),
(89, 93, 5),
(90, 94, 5),
(91, 95, 5),
(93, 97, 5);
INSERT INTO `ohrm_selected_display_field_group`(`id`, `report_id`, `display_field_group_id`) VALUES
(1, 5, 1),
(2, 5, 2),
(3, 5, 3),
(4, 5, 4),
(5, 5, 5),
(6, 5, 6),
(7, 5, 7),
(8, 5, 8),
(9, 5, 9),
(10, 5, 10),
(11, 5, 11),
(12, 5, 12),
(13, 5, 13),
(14, 5, 14),
(15, 5, 15);
INSERT INTO `ohrm_composite_display_field` (`composite_display_field_id`, `report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES (1, 1, 'IF(hs_hr_employee.termination_id IS NULL, CONCAT(hs_hr_employee.emp_firstname, " " ,hs_hr_employee.emp_lastname), CONCAT(hs_hr_employee.emp_firstname, " " ,hs_hr_employee.emp_lastname, " (Past Employee)"))', 'Employee Name', 'employeeName', 'false', null, null, 'label', 'employeeName', 300, '0', null, false, null, 'Deleted Employee', false, false),
(2, 1, 'CONCAT(ohrm_customer.name, " - " ,ohrm_project.name)', 'Project Name', 'projectname', 'false', null, null, 'label', 'projectname', 300, '0', null, false, null, null, false, false);
INSERT INTO `ohrm_selected_composite_display_field` (`id`, `composite_display_field_id`, `report_id`) VALUES (1, 1, 3),
(2, 1, 4),
(3, 2, 2);
INSERT INTO `ohrm_summary_display_field` (`summary_display_field_id`, `function`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`) VALUES (1, 'ROUND(COALESCE(sum(duration)/3600, 0),2)', 'Time (Hours)', 'totalduration', 'false', null, null, 'label', 'totalduration', 100, 'false', 'right', false, null, null),
(2, 'ROUND(COALESCE(sum(TIMESTAMPDIFF(SECOND , ohrm_attendance_record.punch_in_utc_time , ohrm_attendance_record.punch_out_utc_time))/3600, 0),2)', 'Time (Hours)', 'totalduration', 'false', null, null, 'label', 'totalduration', 100, 'false', 'right', false, null, null);
INSERT INTO `ohrm_selected_group_field` (`group_field_id`, `summary_display_field_id`, `report_id`) VALUES (1, 1, 1),
(1, 1, 2),
(2, 1, 3),
(2, 2, 4);
INSERT INTO `ohrm_subunit` VALUES (1, 'Organization', '', '', 1, 2, 0);
INSERT INTO `ohrm_emp_termination_reason` VALUES (1, 'Other'),
(2, 'Retired'),
(3, 'Contract Not Renewed'),
(4, 'Resigned - Company Requested'),
(5, 'Resigned - Self Proposed'),
(6, 'Resigned'),
(7, 'Deceased'),
(8, 'Physically Disabled/Compensated'),
(9, 'Laid-off'),
(10, 'Dismissed');
INSERT INTO `ohrm_user_role` (`id`, `name`, `display_name`, `is_assignable`, `is_predefined`) VALUES
(1, 'Admin', 'Admin', 1, 1),
(2, 'ESS', 'ESS', 1, 1),
(3, 'Supervisor', 'Supervisor', 0, 1),
(4, 'ProjectAdmin', 'ProjectAdmin', 0, 1),
(5, 'Interviewer', 'Interviewer', 0, 1),
(6, 'HiringManager', 'HiringManager', 0, 1),
(7, 'Reviewer', 'Reviewer', 0, 1);
INSERT INTO `ohrm_nationality` (`id`, `name`) VALUES
(1, 'Afghan'),
(2, 'Albanian'),
(3, 'Algerian'),
(4, 'American'),
(5, 'Andorran'),
(6, 'Angolan'),
(7, 'Antiguans'),
(8, 'Argentinean'),
(9, 'Armenian'),
(10, 'Australian'),
(11, 'Austrian'),
(12, 'Azerbaijani'),
(13, 'Bahamian'),
(14, 'Bahraini'),
(15, 'Bangladeshi'),
(16, 'Barbadian'),
(17, 'Barbudans'),
(18, 'Batswana'),
(19, 'Belarusian'),
(20, 'Belgian'),
(21, 'Belizean'),
(22, 'Beninese'),
(23, 'Bhutanese'),
(24, 'Bolivian'),
(25, 'Bosnian'),
(26, 'Brazilian'),
(27, 'British'),
(28, 'Bruneian'),
(29, 'Bulgarian'),
(30, 'Burkinabe'),
(31, 'Burmese'),
(32, 'Burundian'),
(33, 'Cambodian'),
(34, 'Cameroonian'),
(35, 'Canadian'),
(36, 'Cape Verdean'),
(37, 'Central African'),
(38, 'Chadian'),
(39, 'Chilean'),
(40, 'Chinese'),
(41, 'Colombian'),
(42, 'Comoran'),
(43, 'Congolese'),
(44, 'Costa Rican'),
(45, 'Croatian'),
(46, 'Cuban'),
(47, 'Cypriot'),
(48, 'Czech'),
(49, 'Danish'),
(50, 'Djibouti'),
(51, 'Dominican'),
(52, 'Dutch'),
(53, 'East Timorese'),
(54, 'Ecuadorean'),
(55, 'Egyptian'),
(56, 'Emirian'),
(57, 'Equatorial Guinean'),
(58, 'Eritrean'),
(59, 'Estonian'),
(60, 'Ethiopian'),
(61, 'Fijian'),
(62, 'Filipino'),
(63, 'Finnish'),
(64, 'French'),
(65, 'Gabonese'),
(66, 'Gambian'),
(67, 'Georgian'),
(68, 'German'),
(69, 'Ghanaian'),
(70, 'Greek'),
(71, 'Grenadian'),
(72, 'Guatemalan'),
(73, 'Guinea-Bissauan'),
(74, 'Guinean'),
(75, 'Guyanese'),
(76, 'Haitian'),
(77, 'Herzegovinian'),
(78, 'Honduran'),
(79, 'Hungarian'),
(80, 'I-Kiribati'),
(81, 'Icelander'),
(82, 'Indian'),
(83, 'Indonesian'),
(84, 'Iranian'),
(85, 'Iraqi'),
(86, 'Irish'),
(87, 'Israeli'),
(88, 'Italian'),
(89, 'Ivorian'),
(90, 'Jamaican'),
(91, 'Japanese'),
(92, 'Jordanian'),
(93, 'Kazakhstani'),
(94, 'Kenyan'),
(95, 'Kittian and Nevisian'),
(96, 'Kuwaiti'),
(97, 'Kyrgyz'),
(98, 'Laotian'),
(99, 'Latvian'),
(100, 'Lebanese'),
(101, 'Liberian'),
(102, 'Libyan'),
(103, 'Liechtensteiner'),
(104, 'Lithuanian'),
(105, 'Luxembourger'),
(106, 'Macedonian'),
(107, 'Malagasy'),
(108, 'Malawian'),
(109, 'Malaysian'),
(110, 'Maldivan'),
(111, 'Malian'),
(112, 'Maltese'),
(113, 'Marshallese'),
(114, 'Mauritanian'),
(115, 'Mauritian'),
(116, 'Mexican'),
(117, 'Micronesian'),
(118, 'Moldovan'),
(119, 'Monacan'),
(120, 'Mongolian'),
(121, 'Moroccan'),
(122, 'Mosotho'),
(123, 'Motswana'),
(124, 'Mozambican'),
(125, 'Namibian'),
(126, 'Nauruan'),
(127, 'Nepalese'),
(128, 'New Zealander'),
(129, 'Nicaraguan'),
(130, 'Nigerian'),
(131, 'Nigerien'),
(132, 'North Korean'),
(133, 'Northern Irish'),
(134, 'Norwegian'),
(135, 'Omani'),
(136, 'Pakistani'),
(137, 'Palauan'),
(138, 'Panamanian'),
(139, 'Papua New Guinean'),
(140, 'Paraguayan'),
(141, 'Peruvian'),
(142, 'Polish'),
(143, 'Portuguese'),
(144, 'Qatari'),
(145, 'Romanian'),
(146, 'Russian'),
(147, 'Rwandan'),
(148, 'Saint Lucian'),
(149, 'Salvadoran'),
(150, 'Samoan'),
(151, 'San Marinese'),
(152, 'Sao Tomean'),
(153, 'Saudi'),
(154, 'Scottish'),
(155, 'Senegalese'),
(156, 'Serbian'),
(157, 'Seychellois'),
(158, 'Sierra Leonean'),
(159, 'Singaporean'),
(160, 'Slovakian'),
(161, 'Slovenian'),
(162, 'Solomon Islander'),
(163, 'Somali'),
(164, 'South African'),
(165, 'South Korean'),
(166, 'Spanish'),
(167, 'Sri Lankan'),
(168, 'Sudanese'),
(169, 'Surinamer'),
(170, 'Swazi'),
(171, 'Swedish'),
(172, 'Swiss'),
(173, 'Syrian'),
(174, 'Taiwanese'),
(175, 'Tajik'),
(176, 'Tanzanian'),
(177, 'Thai'),
(178, 'Togolese'),
(179, 'Tongan'),
(180, 'Trinidadian or Tobagonian'),
(181, 'Tunisian'),
(182, 'Turkish'),
(183, 'Tuvaluan'),
(184, 'Ugandan'),
(185, 'Ukrainian'),
(186, 'Uruguayan'),
(187, 'Uzbekistani'),
(188, 'Venezuelan'),
(189, 'Vietnamese'),
(190, 'Welsh'),
(191, 'Yemenite'),
(192, 'Zambian'),
(193, 'Zimbabwean');
INSERT INTO `ohrm_email_notification` (`id`, `name`, `is_enable`) VALUES
(1, 'Leave Applications', 0),
(2, 'Leave Assignments', 0),
(3, 'Leave Approvals', 0),
(4, 'Leave Cancellations', 0),
(5, 'Leave Rejections', 0);
INSERT INTO `ohrm_module` (`id`, `name`, `status`) VALUES
(1, 'core', 1),
(2, 'admin', 1),
(3, 'pim', 1),
(4, 'leave', 1),
(5, 'time', 1),
(6, 'attendance', 1),
(7, 'recruitment', 1),
(8, 'recruitmentApply', 1);
INSERT INTO ohrm_screen (`id`, `name`, `module_id`, `action_url`) VALUES
(1, 'User List', 2, 'viewSystemUsers'),
(2, 'Add/Edit System User', 2, 'saveSystemUser'),
(3, 'Delete System Users', 2, 'deleteSystemUsers'),
(4, 'Add Employee', 3, 'addEmployee'),
(5, 'View Employee List', 3, 'viewEmployeeList'),
(6, 'Delete Employees', 3, 'deleteEmployees'),
(7, 'Leave Type List', 4, 'leaveTypeList'),
(8, 'Define Leave Type', 4, 'defineLeaveType'),
(9, 'Undelete Leave Type', 4, 'undeleteLeaveType'),
(10, 'Delete Leave Type', 4, 'deleteLeaveType'),
(11, 'View Holiday List', 4, 'viewHolidayList'),
(12, 'Define Holiday', 4, 'defineHoliday'),
(13, 'Delete Holiday', 4, 'deleteHoliday'),
(14, 'Define WorkWeek', 4, 'defineWorkWeek'),
(16, 'Leave List', 4, 'viewLeaveList'),
(17, 'Assign Leave', 4, 'assignLeave'),
(18, 'View Leave Summary', 4, 'viewLeaveSummary'),
(19, 'Save Leave Entitlements', 4, 'saveLeaveEntitlements'),
(20, 'General Information', 2, 'viewOrganizationGeneralInformation'),
(21, 'Location List', 2, 'viewLocations'),
(22, 'View Company Structure', 2, 'viewCompanyStructure'),
(23, 'Job Title List', 2, 'viewJobTitleList'),
(24, 'Pay Grade List', 2, 'viewPayGrades'),
(25, 'Employment Status List', 2, 'employmentStatus'),
(26, 'Job Category List', 2, 'jobCategory'),
(27, 'Work Shift List', 2, 'workShift'),
(28, 'Skill List', 2, 'viewSkills'),
(29, 'Education List', 2, 'viewEducation'),
(30, 'License List', 2, 'viewLicenses'),
(31, 'Language List', 2, 'viewLanguages'),
(32, 'Membership List', 2, 'membership'),
(33, 'Nationality List', 2, 'nationality'),
(34, 'Add/Edit Mail Configuration', 2, 'listMailConfiguration'),
(35, 'Notification List', 2, 'viewEmailNotification'),
(36, 'Customer List', 2, 'viewCustomers'),
(37, 'Project List', 2, 'viewProjects'),
(38, 'Localization', 2, 'localization'),
(39, 'Module Configuration', 2, 'viewModules'),
(40, 'Configure PIM', 3, 'configurePim'),
(41, 'Custom Field List', 3, 'listCustomFields'),
(42, 'Data Import', 2, 'pimCsvImport'),
(43, 'Reporting Method List', 3, 'viewReportingMethods'),
(44, 'Termination Reason List', 3, 'viewTerminationReasons'),
(45, 'PIM Reports List', 1, 'viewDefinedPredefinedReports'),
(46, 'View MyInfo', 3, 'viewMyDetails'),
(47, 'Define Leave Period', 4, 'defineLeavePeriod'),
(48, 'View My Leave List', 4, 'viewMyLeaveList'),
(49, 'Apply Leave', 4, 'applyLeave'),
(50, 'Define Timesheet Start Date', 5, 'defineTimesheetPeriod'),
(51, 'View My Timesheet', 5, 'viewMyTimesheet'),
(52, 'View Employee Timesheet', 5, 'viewEmployeeTimesheet'),
(53, 'View My Attendance', 6, 'viewMyAttendanceRecord'),
(54, 'Punch In/Out', 6, 'punchIn'),
(55, 'View Employee Attendance', 6, 'viewAttendanceRecord'),
(56, 'Attendance Configuration', 6, 'configure'),
(57, 'View Project Report Criteria', 5, 'displayProjectReportCriteria'),
(58, 'View Employee Report Criteria', 5, 'displayEmployeeReportCriteria'),
(59, 'View Attendance Report Criteria', 5, 'displayAttendanceSummaryReportCriteria'),
(60, 'Candidate List', 7, 'viewCandidates'),
(61, 'Vacancy List', 7, 'viewJobVacancy'),
(67, 'View Time Module', 5, 'viewTimeModule'),
(68, 'View Leave Module', 4, 'viewLeaveModule'),
(69, 'Leave Entitlements', 4, 'viewLeaveEntitlements'),
(70, 'My Leave Entitlements', 4, 'viewMyLeaveEntitlements'),
(71, 'Delete Leave Entitlements', 4, 'deleteLeaveEntitlements'),
(72, 'Add Leave Entitlement', 4, 'addLeaveEntitlement'),
(73, 'Edit Leave Entitlement', 4, 'editLeaveEntitlement'),
(74, 'View Admin Module', 2, 'viewAdminModule'),
(75, 'View PIM Module', 3, 'viewPimModule'),
(76, 'View Recruitment Module', 7, 'viewRecruitmentModule'),
(78, 'Leave Balance Report', 4, 'viewLeaveBalanceReport'),
(79, 'My Leave Balance Report', 4, 'viewMyLeaveBalanceReport'),
(80, 'Save Job Title', 2, 'saveJobTitle'),
(81, 'Delete Job Title', 2, 'deleteJobTitle'),
(82, 'Save Pay Grade', 2, 'payGrade'),
(83, 'Delete Pay Grade', 2, 'deletePayGrades'),
(84, 'Save Pay Grade Currency', 2, 'savePayGradeCurrency'),
(85, 'Delete Pay Grade Currency', 2, 'deletePayGradeCurrency'),
(86, 'Add Customer', 2, 'addCustomer'),
(87, 'Delete Customer', 2, 'deleteCustomer'),
(88, 'Save Project', 2, 'saveProject'),
(89, 'Delete Project', 2, 'deleteProject'),
(90, 'Add Project Adtivity', 2, 'addProjectActivity'),
(91, 'Delete Project Adtivity', 2, 'deleteProjectActivity'),
(92, 'Define PIM reports', 1, 'definePredefinedReport'),
(93, 'Display PIM reports', 1, 'displayPredefinedReport'),
(94, 'Add Job Vacancy', 7, 'addJobVacancy'),
(95, 'Delete Job Vacancy', 7, 'deleteJobVacancy'),
(96, 'Add Candidate', 7, 'addCandidate'),
(97, 'Delete Candidate', 7, 'deleteCandidateVacancies'),
(98, 'View Leave Request', 4, 'viewLeaveRequest'),
(99, 'Change Leave Status', 4, 'changeLeaveStatus'),
(100, 'Terminate Employment', 3, 'terminateEmployement'),
(101, 'View Attendance Summary Report', 5, 'displayAttendanceSummaryReport'),
(102, 'View Project Activity Details Report', 5, 'displayProjectActivityDetailsReport');
INSERT INTO ohrm_menu_item (`id`, `menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
(1, 'Admin', 74, NULL, 1, 100, NULL, 1),
(2, 'User Management', NULL, 1, 2, 100, NULL, 1),
(3, 'Project Info', NULL, 52, 2, 400, NULL, 0),
(4, 'Customers', 36, 3, 3, 100, NULL, 0),
(5, 'Projects', 37, 3, 3, 200, NULL, 0),
(6, 'Job', NULL, 1, 2, 300, NULL, 1),
(7, 'Job Titles', 23, 6, 3, 100, NULL, 1),
(8, 'Pay Grades', 24, 6, 3, 200, NULL, 1),
(9, 'Employment Status', 25, 6, 3, 300, NULL, 1),
(10, 'Job Categories', 26, 6, 3, 400, NULL, 1),
(11, 'Work Shifts', 27, 6, 3, 500, NULL, 1),
(12, 'Organization', NULL, 1, 2, 400, NULL, 1),
(13, 'General Information', 20, 12, 3, 100, NULL, 1),
(14, 'Locations', 21, 12, 3, 200, NULL, 1),
(15, 'Structure', 22, 12, 3, 300, NULL, 1),
(16, 'Qualifications', NULL, 1, 2, 500, NULL, 1),
(17, 'Skills', 28, 16, 3, 100, NULL, 1),
(18, 'Education', 29, 16, 3, 200, NULL, 1),
(19, 'Licenses', 30, 16, 3, 300, NULL, 1),
(20, 'Languages', 31, 16, 3, 400, NULL, 1),
(21, 'Memberships', 32, 16, 3, 500, NULL, 1),
(22, 'Nationalities', 33, 1, 2, 700, NULL, 1),
(23, 'Configuration', NULL, 1, 2, 900, NULL, 1),
(24, 'Email Configuration', 34, 23, 3, 100, NULL, 1),
(25, 'Email Subscriptions', 35, 23, 3, 200, NULL, 1),
(27, 'Localization', 38, 23, 3, 300, NULL, 1),
(28, 'Modules', 39, 23, 3, 400, NULL, 1),
(30, 'PIM', 75, NULL, 1, 200, NULL, 1),
(31, 'Configuration', NULL, 30, 2, 100, NULL, 1),
(32, 'Optional Fields', 40, 31, 3, 100, NULL, 1),
(33, 'Custom Fields', 41, 31, 3, 200, NULL, 1),
(34, 'Data Import', 42, 31, 3, 300, NULL, 1),
(35, 'Reporting Methods', 43, 31, 3, 400, NULL, 1),
(36, 'Termination Reasons', 44, 31, 3, 500, NULL, 1),
(37, 'Employee List', 5, 30, 2, 200, '/reset/1', 1),
(38, 'Add Employee', 4, 30, 2, 300, NULL, 1),
(39, 'Reports', 45, 30, 2, 400, '/reportGroup/3/reportType/PIM_DEFINED', 1),
(40, 'My Info', 46, NULL, 1, 700, NULL, 1),
(41, 'Leave', 68, NULL, 1, 300, NULL, 1),
(42, 'Configure', NULL, 41, 2, 500, NULL, 0),
(43, 'Leave Period', 47, 42, 3, 100, NULL, 0),
(44, 'Leave Types', 7, 42, 3, 200, NULL, 0),
(45, 'Work Week', 14, 42, 3, 300, NULL, 0),
(46, 'Holidays', 11, 42, 3, 400, NULL, 0),
(48, 'Leave List', 16, 41, 2, 600, '/reset/1', 0),
(49, 'Assign Leave', 17, 41, 2, 700, NULL, 0),
(50, 'My Leave', 48, 41, 2, 200, '/reset/1', 0),
(51, 'Apply', 49, 41, 2, 100, NULL, 0),
(52, 'Time', 67, NULL, 1, 400, NULL, 1),
(53, 'Timesheets', NULL, 52, 2, 100, NULL, 1),
(54, 'My Timesheets', 51, 53, 3, 100, NULL, 0),
(55, 'Employee Timesheets', 52, 53, 3, 200, NULL, 0),
(56, 'Attendance', NULL, 52, 2, 200, NULL, 1),
(57, 'My Records', 53, 56, 3, 100, NULL, 0),
(58, 'Punch In/Out', 54, 56, 3, 200, NULL, 0),
(59, 'Employee Records', 55, 56, 3, 300, NULL, 0),
(60, 'Configuration', 56, 56, 3, 400, NULL, 0),
(61, 'Reports', NULL, 52, 2, 300, NULL, 1),
(62, 'Project Reports', 57, 61, 3, 100, '?reportId=1', 0),
(63, 'Employee Reports', 58, 61, 3, 200, '?reportId=2', 0),
(64, 'Attendance Summary', 59, 61, 3, 300, '?reportId=4', 0),
(65, 'Recruitment', 76, NULL, 1, 500, NULL, 1),
(66, 'Candidates', 60, 65, 2, 100, NULL, 1),
(67, 'Vacancies', 61, 65, 2, 200, NULL, 1),
(74, 'Entitlements', NULL, 41, 2, 300, NULL, 0),
(75, 'Add Entitlements', 72, 74, 3, 100, NULL, 0),
(76, 'My Entitlements', 70, 74, 3, 300, '/reset/1', 0),
(77, 'Employee Entitlements', 69, 74, 3, 200, '/reset/1', 0),
(78, 'Reports', NULL, 41, 2, 400, NULL, 0),
(79, 'Leave Entitlements and Usage Report', 78, 78, 3, 100, NULL, 0),
(80, 'My Leave Entitlements and Usage Report', 79, 78, 3, 200, NULL, 0),
(81, 'Users', 1, 2, 3, 100, NULL, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(1, 1, 1, 1, 1, 1),
(1, 2, 1, 1, 1, 1),
(2, 2, 0, 0, 0, 0),
(3, 2, 0, 0, 0, 0),
(1, 3, 1, 1, 1, 1),
(2, 3, 0, 0, 0, 0),
(3, 3, 0, 0, 0, 0),
(1, 4, 1, 1, 1, 1),
(1, 5, 1, 1, 1, 1),
(3, 5, 1, 0, 0, 0),
(1, 6, 1, 0, 0, 1),
(1, 7, 1, 1, 1, 1),
(1, 8, 1, 1, 1, 1),
(1, 9, 1, 1, 1, 1),
(1, 10, 1, 1, 1, 1),
(1, 11, 1, 1, 1, 1),
(1, 12, 1, 1, 1, 1),
(1, 13, 1, 1, 1, 1),
(1, 14, 1, 1, 1, 1),
(1, 16, 1, 1, 1, 0),
(3, 16, 1, 1, 1, 0),
(1, 17, 1, 1, 1, 0),
(3, 17, 1, 1, 1, 0),
(1, 18, 1, 1, 1, 0),
(2, 18, 1, 0, 0, 0),
(3, 18, 1, 0, 0, 0),
(1, 19, 1, 1, 1, 1),
(1, 20, 1, 1, 1, 1),
(1, 21, 1, 1, 1, 1),
(1, 22, 1, 1, 1, 1),
(1, 23, 1, 1, 1, 1),
(1, 24, 1, 1, 1, 1),
(1, 25, 1, 1, 1, 1),
(1, 26, 1, 1, 1, 1),
(1, 27, 1, 1, 1, 1),
(1, 28, 1, 1, 1, 1),
(1, 29, 1, 1, 1, 1),
(1, 30, 1, 1, 1, 1),
(1, 31, 1, 1, 1, 1),
(1, 32, 1, 1, 1, 1),
(1, 33, 1, 1, 1, 1),
(1, 34, 1, 1, 1, 1),
(1, 35, 1, 1, 1, 1),
(1, 36, 1, 1, 1, 1),
(1, 37, 1, 1, 1, 1),
(4, 37, 1, 0, 0, 0),
(1, 38, 1, 1, 1, 1),
(1, 39, 1, 1, 1, 1),
(1, 40, 1, 1, 1, 1),
(1, 41, 1, 1, 1, 1),
(1, 42, 1, 1, 1, 1),
(1, 43, 1, 1, 1, 1),
(1, 44, 1, 1, 1, 1),
(1, 45, 1, 1, 1, 1),
(2, 46, 1, 1, 1, 1),
(1, 47, 1, 1, 1, 1),
(2, 48, 1, 1, 1, 0),
(2, 49, 1, 1, 1, 1),
(1, 50, 1, 1, 1, 1),
(2, 51, 1, 1, 1, 1),
(1, 52, 1, 1, 1, 1),
(3, 52, 1, 1, 1, 1),
(2, 53, 1, 1, 0, 0),
(2, 54, 1, 1, 1, 1),
(1, 55, 1, 1, 0, 1),
(3, 55, 1, 1, 0, 0),
(1, 56, 1, 1, 1, 1),
(1, 57, 1, 1, 1, 1),
(4, 57, 1, 1, 1, 1),
(1, 58, 1, 1, 1, 1),
(3, 58, 1, 1, 1, 1),
(1, 59, 1, 1, 1, 1),
(3, 59, 1, 1, 1, 1),
(1, 60, 1, 1, 1, 1),
(6, 60, 1, 1, 1, 1),
(5, 60, 1, 0, 1, 0),
(1, 61, 1, 1, 1, 1),
(1, 67, 1, 1, 1, 1),
(2, 67, 1, 0, 1, 0),
(3, 67, 1, 0, 1, 0),
(1, 68, 1, 1, 1, 1),
(2, 68, 1, 0, 1, 0),
(3, 68, 1, 0, 1, 0),
(1, 69, 1, 1, 1, 1),
-- (2, 69, 0, 0, 0, 0),
(3, 69, 1, 0, 0, 0),
-- (1, 70, 0, 0, 0, 0),
(2, 70, 1, 0, 0, 0),
-- (3, 70, 0, 0, 0, 0),
(1, 71, 1, 0, 0, 1),
(1, 72, 1, 1, 1, 0),
(1, 73, 1, 0, 1, 0),
(1, 74, 1, 1, 1, 1),
(1, 75, 1, 1, 1, 1),
(3, 75, 1, 1, 1, 1),
(1, 76, 1, 1, 1, 1),
(5, 76, 1, 1, 1, 1),
(6, 76, 1, 1, 1, 1),
(1, 78, 1, 0, 0, 0),
(3, 78, 1, 0, 0, 0),
(2, 79, 1, 0, 0, 0),
(1, 80, 1, 1, 1, 1),
(1, 81, 1, 1, 1, 1),
(1, 82, 1, 1, 1, 1),
(1, 83, 1, 1, 1, 1),
(1, 84, 1, 1, 1, 1),
(1, 85, 1, 1, 1, 1),
(1, 86, 1, 1, 1, 1),
(1, 87, 1, 1, 1, 1),
(1, 88, 1, 1, 1, 1),
(4, 88, 1, 1, 1, 1),
(1, 89, 1, 1, 1, 1),
(1, 90, 1, 1, 1, 1),
(4, 90, 1, 1, 1, 1),
(1, 91, 1, 1, 1, 1),
(4, 91, 1, 1, 1, 1),
(1, 92, 1, 1, 1, 1),
(1, 93, 1, 1, 1, 1),
(1, 94, 1, 1, 1, 1),
(1, 95, 1, 1, 1, 1),
(1, 96, 1, 1, 1, 1),
(5, 96, 1, 1, 1, 1),
(6, 96, 1, 1, 1, 1),
(1, 97, 1, 1, 1, 1),
(6, 97, 1, 1, 1, 1),
(1, 98, 1, 1, 1, 1),
(2, 98, 1, 1, 1, 1),
(3, 98, 1, 1, 1, 1),
(1, 99, 1, 0, 1, 0),
(2, 99, 1, 0, 1, 0),
(3, 99, 1, 0, 1, 0),
(1, 100, 1, 0, 0, 0),
(1, 101, 1, 1, 1, 1),
(3, 101, 1, 1, 1, 1),
(1, 102, 1, 1, 1, 1),
(4, 102, 1, 1, 1, 1);
INSERT INTO `ohrm_data_group` (`id`, `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(1, 'personal_information', 'PIM - Personal Details', 1, NULL, 1, NULL),
(2, 'personal_attachment', 'PIM - Personal Details - Attachments', 1, 1, 1, 1),
(3, 'personal_custom_fields', 'PIM - Personal Details - Custom Fields', 1, NULL, 1, NULL),
(4, 'contact_details', 'PIM - Contact Details', 1, NULL, 1, NULL),
(5, 'contact_attachment', 'PIM - Contact Details - Attachments', 1, 1, 1, 1),
(6, 'contact_custom_fields', 'PIM - Contact Details - Custom Fields', 1, NULL, 1, NULL),
(7, 'emergency_contacts', 'PIM - Emergency Contacts', 1, 1, 1, 1),
(8, 'emergency_attachment', 'PIM - Emergency Contacts - Attachments', 1, 1, 1, 1),
(9, 'emergency_custom_fields', 'PIM - Emergency Contacts - Custom Fields', 1, NULL, 1, NULL),
(10, 'dependents', 'PIM - Dependents', 1, 1, 1, 1),
(11, 'dependents_attachment', 'PIM - Dependents - Attachments', 1, 1, 1, 1),
(12, 'dependents_custom_fields', 'PIM - Dependents - Custom Fields', 1, NULL, 1, NULL),
(13, 'immigration', 'PIM - Immigration', 1, 1, 1, 1),
(14, 'immigration_attachment', 'PIM - Immigration - Attachments', 1, 1, 1, 1),
(15, 'immigration_custom_fields', 'PIM - Immigration - Custom Fields', 1, NULL, 1, NULL),
(16, 'job_details', 'PIM - Job', 1, NULL, 1, NULL),
(17, 'job_attachment', 'PIM - Job - Attachments', 1, 1, 1, 1),
(18, 'job_custom_fields', 'PIM - Job - Custom Fields', 1, NULL, 1, NULL),
(19, 'salary_details', 'PIM - Salary', 1, 1, 1, 1),
(20, 'salary_attachment', 'PIM - Salary - Attachments', 1, 1, 1, 1),
(21, 'salary_custom_fields', 'PIM - Salary - Custom Fields', 1, NULL, 1, NULL),
(22, 'tax_exemptions', 'PIM - Tax Exemptions', 1, NULL, 1, NULL),
(23, 'tax_attachment', 'PIM - Tax Exemptions - Attachments', 1, 1, 1, 1),
(24, 'tax_custom_fields', 'PIM - Tax Exemptions - Custom Fields', 1, NULL, 1, NULL),
(25, 'supervisor', 'PIM - Employee Supervisors', 1, 1, 1, 1),
(26, 'subordinates', 'PIM - Employee Subordinates', 1, 1, 1, 1),
(27, 'report-to_attachment', 'PIM - Employee Supervisors/Subordinates - Attachment', 1, 1, 1, 1),
(28, 'report-to_custom_fields', 'PIM - Employee Supervisors/Subordinates - Custom Fields', 1, NULL, 1, NULL),
(29, 'qualification_work', 'PIM - Qualifications - Work Experience', 1, 1, 1, 1),
(30, 'qualification_education', 'PIM - Qualifications - Education', 1, 1, 1, 1),
(31, 'qualification_skills', 'PIM - Qualifications - Skills', 1, 1, 1, 1),
(32, 'qualification_languages', 'PIM - Qualifications - Languages', 1, 1, 1, 1),
(33, 'qualification_license', 'PIM - Qualifications - License', 1, 1, 1, 1),
(34, 'qualifications_attachment', 'PIM - Qualifications - Attachments', 1, 1, 1, 1),
(35, 'qualifications_custom_fields', 'PIM - Qualifications - Custom Fields', 1, NULL, 1, NULL),
(36, 'membership', 'PIM - Membership', 1, 1, 1, 1),
(37, 'membership_attachment', 'PIM - Membership - Attachments', 1, 1, 1, 1),
(38, 'membership_custom_fields', 'PIM - Membership - Custom Fields', 1, NULL, 1, NULL),
(39, 'photograph', 'PIM - Employee Photograph', 1, NULL, 1, 1),
(40, 'leave_entitlements', 'Leave - Leave Entitlements', 1, 1, 1, 1),
(41, 'leave_entitlements_usage_report', 'Leave - Leave Entitlements and Usage Report', 1, NULL, NULL, NULL),
(42, 'job_titles', 'Admin - Job Titles', 1, 1, 1, 1),
(43, 'pay_grades', 'Admin - Pay Grades', 1, 1, 1, 1),
(44, 'time_customers', 'Time - Project Info - Customers', 1, 1, 1, 1),
(45, 'time_projects', 'Time - Project Info - Projects', 1, 1, 1, 1),
(46, 'pim_reports', 'PIM - Reports', 1, 1, 1, 1),
(47, 'attendance_configuration', 'Time - Attendance Configuration', 1, 0, 1, 0),
(48, 'attendance_records', 'Time - Attendance Records', 1, 0, 0, 0),
(49, 'time_project_reports', 'Time - Project Reports', 1, 0, 0, 0),
(50, 'time_employee_reports', 'Time - Employee Reports', 1, 0, 0, 0),
(51, 'attendance_summary', 'Time - Attendance Summary', 1, 0, 0, 0),
(52, 'leave_period', 'Leave - Leave Period', 1, 0, 1, 0),
(53, 'leave_types', 'Leave - Leave Types', 1, 1, 1, 1),
(54, 'work_week', 'Leave - Work Week', 1, 0, 1, 0),
(55, 'holidays', 'Leave - Holidays', 1, 1, 1, 1),
(56, 'recruitment_vacancies', 'Recruitment - Vacancies', 1, 1, 1, 1),
(57, 'recruitment_candidates', 'Recruitment - Candidates', 1, 1, 1, 1),
(58, 'time_employee_timesheets', 'Time - Employee Timesheets', 1, 0, 0, 0),
(59, 'leave_list', 'Leave - Leave List', 1, 0, 0, 0),
(60, 'leave_list_comments', 'Leave - Leave List - Comments', 0, 1, 0, 0);
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(1, 1, 1, NULL, 1, NULL, 0),
(1, 2, 1, 1, 1, 1, 0),
(1, 3, 1, NULL, 1, NULL, 0),
(1, 4, 1, NULL, 1, NULL, 0),
(1, 5, 1, 1, 1, 1, 0),
(1, 6, 1, NULL, 1, NULL, 0),
(1, 7, 1, 1, 1, 1, 0),
(1, 8, 1, 1, 1, 1, 0),
(1, 9, 1, NULL, 1, NULL, 0),
(1, 10, 1, 1, 1, 1, 0),
(1, 11, 1, 1, 1, 1, 0),
(1, 12, 1, NULL, 1, NULL, 0),
(1, 13, 1, 1, 1, 1, 0),
(1, 14, 1, 1, 1, 1, 0),
(1, 15, 1, NULL, 1, NULL, 0),
(1, 16, 1, NULL, 1, NULL, 0),
(1, 17, 1, 1, 1, 1, 0),
(1, 18, 1, NULL, 1, NULL, 0),
(1, 19, 1, 1, 1, 1, 0),
(1, 20, 1, 1, 1, 1, 0),
(1, 21, 1, NULL, 1, NULL, 0),
(1, 22, 1, NULL, 1, NULL, 0),
(1, 23, 1, 1, 1, 1, 0),
(1, 24, 1, NULL, 1, NULL, 0),
(1, 25, 1, 1, 1, 1, 0),
(1, 26, 1, 1, 1, 1, 0),
(1, 27, 1, 1, 1, 1, 0),
(1, 28, 1, NULL, 1, NULL, 0),
(1, 29, 1, 1, 1, 1, 0),
(1, 30, 1, 1, 1, 1, 0),
(1, 31, 1, 1, 1, 1, 0),
(1, 32, 1, 1, 1, 1, 0),
(1, 33, 1, 1, 1, 1, 0),
(1, 34, 1, 1, 1, 1, 0),
(1, 35, 1, NULL, 1, NULL, 0),
(1, 36, 1, 1, 1, 1, 0),
(1, 37, 1, 1, 1, 1, 0),
(1, 38, 1, NULL, 1, NULL, 0),
(1, 39, 1, NULL, 1, 1, 0),
(1, 40, 1, 1, 1, 1, 0),
(1, 41, 1, NULL, NULL, NULL, 0),
(1, 40, 1, 1, 1, 1, 1),
(2, 1, 1, NULL, 1, NULL, 1),
(2, 2, 1, 1, 1, 1, 1),
(2, 3, 1, NULL, 1, NULL, 1),
(2, 4, 1, NULL, 1, NULL, 1),
(2, 5, 1, 1, 1, 1, 1),
(2, 6, 1, NULL, 1, NULL, 1),
(2, 7, 1, 1, 1, 1, 1),
(2, 8, 1, 1, 1, 1, 1),
(2, 9, 1, NULL, 1, NULL, 1),
(2, 10, 1, 1, 1, 1, 1),
(2, 11, 1, 1, 1, 1, 1),
(2, 12, 1, NULL, 1, NULL, 1),
(2, 13, 1, 1, 1, 1, 1),
(2, 14, 1, 1, 1, 1, 1),
(2, 15, 1, NULL, 1, NULL, 1),
(2, 16, 1, NULL, NULL, NULL, 1),
(2, 17, 1, 0, 0, 0, 1),
(2, 18, 1, 0, 0, 0, 1),
(2, 19, 1, NULL, NULL, NULL, 1),
(2, 20, 1, 0, 0, 0, 1),
(2, 21, 1, 0, 0, 0, 1),
(2, 22, 1, NULL, NULL, NULL, 1),
(2, 23, 1, 0, 0, 0, 1),
(2, 24, 1, 0, 0, 0, 1),
(2, 25, 1, NULL, NULL, NULL, 1),
(2, 26, 1, NULL, NULL, NULL, 1),
(2, 27, 1, 0, 0, 0, 1),
(2, 28, 1, 0, 0, 0, 1),
(2, 29, 1, 1, 1, 1, 1),
(2, 30, 1, 1, 1, 1, 1),
(2, 31, 1, 1, 1, 1, 1),
(2, 32, 1, 1, 1, 1, 1),
(2, 33, 1, 1, 1, 1, 1),
(2, 34, 1, 1, 1, 1, 1),
(2, 35, 1, NULL, 1, NULL, 1),
(2, 36, 1, 1, 1, 1, 1),
(2, 37, 1, 1, 1, 1, 1),
(2, 38, 1, NULL, 1, NULL, 1),
(2, 39, 1, NULL, 1, 1, 1),
(2, 40, 1, 0, 0, 0, 1),
(2, 41, 1, NULL, NULL, NULL, 1),
(3, 1, 1, NULL, 1, NULL, 0),
(3, 2, 1, 1, 1, 1, 0),
(3, 3, 1, NULL, 1, NULL, 0),
(3, 4, 1, NULL, 1, NULL, 0),
(3, 5, 1, 1, 1, 1, 0),
(3, 6, 1, NULL, 1, NULL, 0),
(3, 7, 1, 1, 1, 1, 0),
(3, 8, 1, 1, 1, 1, 0),
(3, 9, 1, NULL, 1, NULL, 0),
(3, 10, 1, 1, 1, 1, 0),
(3, 11, 1, 1, 1, 1, 0),
(3, 12, 1, NULL, 1, NULL, 0),
(3, 13, 1, 1, 1, 1, 0),
(3, 14, 1, 1, 1, 1, 0),
(3, 15, 1, NULL, 1, NULL, 0),
(3, 16, 1, NULL, NULL, NULL, 0),
(3, 17, 1, 0, 0, 0, 0),
(3, 18, 1, 0, 0, 0, 0),
(3, 19, 0, 0, 0, 0, 0),
(3, 20, 0, 0, 0, 0, 0),
(3, 21, 0, 0, 0, 0, 0),
(3, 22, 1, NULL, NULL, NULL, 0),
(3, 23, 1, 0, 0, 0, 0),
(3, 24, 1, 0, 0, 0, 0),
(3, 25, 1, NULL, NULL, NULL, 0),
(3, 26, 1, NULL, NULL, NULL, 0),
(3, 27, 1, 0, 0, 0, 0),
(3, 28, 1, 0, 0, 0, 0),
(3, 29, 1, 1, 1, 1, 0),
(3, 30, 1, 1, 1, 1, 0),
(3, 31, 1, 1, 1, 1, 0),
(3, 32, 1, 1, 1, 1, 0),
(3, 33, 1, 1, 1, 1, 0),
(3, 34, 1, 1, 1, 1, 0),
(3, 35, 1, NULL, 1, NULL, 0),
(3, 36, 1, 1, 1, 1, 0),
(3, 37, 1, 1, 1, 1, 0),
(3, 38, 1, NULL, 1, NULL, 0),
(3, 39, 1, NULL, 1, 1, 0),
(3, 40, 1, 0, 0, 0, 0),
(3, 41, 1, NULL, NULL, NULL, 0),
(3, 1, 1, NULL, 1, NULL, 1),
(3, 2, 1, 1, 1, 1, 1),
(3, 3, 1, NULL, 1, NULL, 1),
(3, 4, 1, NULL, 1, NULL, 1),
(3, 5, 1, 1, 1, 1, 1),
(3, 6, 1, NULL, 1, NULL, 1),
(3, 7, 1, 1, 1, 1, 1),
(3, 8, 1, 1, 1, 1, 1),
(3, 9, 1, NULL, 1, NULL, 1),
(3, 10, 1, 1, 1, 1, 1),
(3, 11, 1, 1, 1, 1, 1),
(3, 12, 1, NULL, 1, NULL, 1),
(3, 13, 1, 1, 1, 1, 1),
(3, 14, 1, 1, 1, 1, 1),
(3, 15, 1, NULL, 1, NULL, 1),
(3, 16, 1, NULL, NULL, NULL, 1),
(3, 17, 1, 0, 0, 0, 1),
(3, 18, 1, 0, 0, 0, 1),
(3, 19, 1, 0, 0, 0, 1),
(3, 20, 1, 0, 0, 0, 1),
(3, 21, 1, 0, 0, 0, 1),
(3, 22, 1, NULL, NULL, NULL, 1),
(3, 23, 1, 0, 0, 0, 1),
(3, 24, 1, 0, 0, 0, 1),
(3, 25, 1, NULL, NULL, NULL, 1),
(3, 26, 1, NULL, NULL, NULL, 1),
(3, 27, 1, 0, 0, 0, 1),
(3, 28, 1, 0, 0, 0, 1),
(3, 29, 1, 1, 1, 1, 1),
(3, 30, 1, 1, 1, 1, 1),
(3, 31, 1, 1, 1, 1, 1),
(3, 32, 1, 1, 1, 1, 1),
(3, 33, 1, 1, 1, 1, 1),
(3, 34, 1, 1, 1, 1, 1),
(3, 35, 1, NULL, 1, NULL, 1),
(3, 36, 1, 1, 1, 1, 1),
(3, 37, 1, 1, 1, 1, 1),
(3, 38, 1, NULL, 1, NULL, 1),
(3, 39, 1, NULL, 1, 1, 1),
(3, 40, 1, 0, 0, 0, 1),
(3, 41, 1, NULL, NULL, NULL, 1),
(1, 42, 1, 1, 1, 1, 0),
(2, 42, 0, 0, 0, 0, 0),
(3, 42, 0, 0, 0, 0, 0),
(1, 43, 1, 1, 1, 1, 0),
(2, 43, 0, 0, 0, 0, 0),
(3, 43, 0, 0, 0, 0, 0),
(1, 44, 1, 1, 1, 1, 0),
(2, 44, 0, 0, 0, 0, 0),
(3, 44, 0, 0, 0, 0, 0),
(1, 45, 1, 1, 1, 1, 0),
(2, 45, 0, 0, 0, 0, 0),
(3, 45, 0, 0, 0, 0, 0),
(4, 45, 1, 0, 1, 0, 0),
(1, 46, 1, 1, 1, 1, 0),
(2, 46, 0, 0, 0, 0, 0),
(3, 46, 0, 0, 0, 0, 0),
(1, 47, 1, NULL, 1, NULL, 0),
(2, 47, 0, 0, 0, 0, 0),
(3, 47, 0, 0, 0, 0, 0),
(1, 48, 1, 0, 0, 0, 0),
(2, 48, 0, 0, 0, 0, 0),
(2, 48, 1, 0, 0, 0, 1),
(3, 48, 1, 0, 0, 0, 0),
(1, 49, 1, 0, 0, 0, 0),
(2, 49, 0, 0, 0, 0, 0),
(3, 49, 0, 0, 0, 0, 0),
(4, 49, 1, 0, 0, 0, 0),
(1, 50, 1, 0, 0, 0, 0),
(2, 50, 0, 0, 0, 0, 0),
(3, 50, 1, 0, 0, 0, 0),
(1, 51, 1, 0, 0, 0, 0),
(2, 51, 0, 0, 0, 0, 0),
(3, 51, 1, 0, 0, 0, 0),
(1, 52, 1, NULL, 1, NULL, 0),
(2, 52, 0, 0, 0, 0, 0),
(3, 52, 0, 0, 0, 0, 0),
(1, 53, 1, 1, 1, 1, 0),
(2, 53, 0, 0, 0, 0, 0),
(3, 53, 0, 0, 0, 0, 0),
(1, 54, 1, 0, 1, 0, 0),
(2, 54, 0, 0, 0, 0, 0),
(3, 54, 0, 0, 0, 0, 0),
(1, 55, 1, 1, 1, 1, 0),
(2, 55, 0, 0, 0, 0, 0),
(3, 55, 0, 0, 0, 0, 0),
(1, 56, 1, 1, 1, 1, 0),
(2, 56, 0, 0, 0, 0, 0),
(3, 56, 0, 0, 0, 0, 0),
(1, 57, 1, 1, 1, 1, 0),
(6, 57, 1, 1, 1, 1, 0),
(5, 57, 1, 0, 1, 0, 0),
(1, 58, 1, 0, 0, 0, 0),
(2, 58, 0, 0, 0, 0, 0),
(2, 58, 1, 0, 0, 0, 1),
(3, 58, 1, 0, 0, 0, 0),
(1, 59, 1, 0, 0, 0, 0),
(2, 59, 1, 0, 0, 0, 1),
(3, 59, 1, 0, 0, 0, 0),
(1, 60, 0, 1, 0, 0, 0),
(2, 60, 0, 1, 0, 0, 1),
(3, 60, 0, 1, 0, 0, 0);
INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(40, 69, 1),
(40, 72, 2),
(40, 73, 3),
(40, 71, 4),
(41, 78, 1),
(42, 23, 1),
(42, 80, 1),
(42, 80, 2),
(42, 80, 3),
(42, 81, 4),
(43, 24, 1),
(43, 82, 1),
(43, 82, 2),
(43, 82, 3),
(43, 83, 4),
(43, 84, 3),
(43, 85, 3),
(42, 74, 1),
(43, 74, 1),
(44, 36, 1),
(44, 86, 2),
(44, 86, 3),
(44, 87, 4),
(45, 37, 1),
(45, 88, 1),
(45, 88, 2),
(45, 88, 3),
(45, 89, 4),
(45, 90, 2),
(45, 90, 3),
(45, 91, 2),
(45, 91, 3),
(46, 45, 1),
(46, 45, 4),
(46, 92, 2),
(46, 92, 3),
(46, 93, 1),
(47, 56, 1),
(47, 56, 3),
(48, 55, 1),
(49, 57, 1),
(49, 102, 1),
(50, 58, 1),
(51, 59, 1),
(51, 101, 1),
(52, 47, 1),
(52, 47, 3),
(53, 7, 1),
(53, 8, 1),
(53, 8, 2),
(53, 8, 3),
(53, 9, 2),
(53, 10, 4),
(54, 14, 1),
(54, 14, 3),
(55, 11, 1),
(55, 12, 2),
(55, 12, 3),
(55, 13, 4),
(56, 61, 1),
(56, 94, 1),
(56, 94, 2),
(56, 94, 3),
(56, 95, 4),
(57, 60, 1),
(57, 96, 1),
(57, 96, 2),
(57, 96, 3),
(57, 97, 4),
(56, 76, 1),
(57, 76, 1),
(58, 52, 1),
(59, 16, 1),
(59, 98, 1),
(59, 99, 1);
INSERT INTO `ohrm_job_category` (`name`) VALUES ('Officials and Managers'),
('Professionals'),
('Technicians'),
('Sales Workers'),
('Operatives'),
('Office and Clerical Workers'),
('Craft Workers'),
('Service Workers'),
('Laborers and Helpers');
INSERT INTO ohrm_email(`id`, `name`) VALUES
(1, 'leave.apply'),
(2, 'leave.assign'),
(3, 'leave.approve'),
(4, 'leave.cancel'),
(5, 'leave.reject'),
(6, 'leave.change');
INSERT INTO ohrm_email_template(`id`, `email_id`, `locale`, `performer_role`, `recipient_role`,`body`, `subject`) VALUES
(1, 1, 'en_US', NULL, 'supervisor', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationSubject.txt'),
(2, 1, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/apply/leaveApplicationSubject.txt'),
(3, 3, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalSubject.txt'),
(4, 3, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/approve/leaveApprovalSubscriberSubject.txt'),
(5, 2, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubject.txt'),
(6, 2, 'en_US', NULL, 'supervisor', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentBodyForSupervisors.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubjectForSupervisors.txt'),
(7, 2, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/assign/leaveAssignmentSubscriberSubject.txt'),
(8, 4, 'en_US', 'ess', 'supervisor', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationSubject.txt'),
(9, 4, 'en_US', 'ess', 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveEmployeeCancellationSubscriberSubject.txt'),
(10, 4, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationSubject.txt'),
(11, 4, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/cancel/leaveCancellationSubscriberSubject.txt'),
(12, 5, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionSubject.txt'),
(13, 5, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/reject/leaveRejectionSubscriberSubject.txt'),
(14, 6, 'en_US', NULL, 'ess', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeSubject.txt'),
(15, 6, 'en_US', NULL, 'subscriber', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeSubscriberBody.txt', 'orangehrmLeavePlugin/modules/leave/templates/mail/en_US/change/leaveChangeSubscriberSubject.txt');
INSERT INTO ohrm_email_processor(`id`, `email_id`, `class_name`) VALUES
(1, 1, 'LeaveEmailProcessor'),
(2, 2, 'LeaveEmailProcessor'),
(3, 3, 'LeaveEmailProcessor'),
(4, 4, 'LeaveEmailProcessor'),
(5, 5, 'LeaveEmailProcessor'),
(6, 6, 'LeaveChangeMailProcessor');
INSERT INTO ohrm_leave_entitlement_type(`id`,`name`,`is_editable`) VALUES
(1,'Added',1);
INSERT INTO ohrm_advanced_report (id, name, definition) VALUES
(1, 'Leave Entitlements and Usage Report', '
1
1
FROM ohrm_leave_type WHERE (deleted = 0) OR (SELECT count(l.id) FROM ohrm_leave l WHERE l.status = 3 AND l.leave_type_id = ohrm_leave_type.id) > 0 ORDER BY ohrm_leave_type.id
leaveTypeId
ohrm_leave_type.id
leaveTypeId
Leave Type ID
1
ohrm_leave_type.exclude_in_reports_if_no_entitlement
exclude_if_no_entitlement
Exclude
1
ohrm_leave_type.deleted
leave_type_deleted
Leave Type Deleted
1
ohrm_leave_type.name
leaveType
Leave Type
160
s
FROM (
SELECT ohrm_leave_entitlement.id as id,
ohrm_leave_entitlement.leave_type_id as leave_type_id,
ohrm_leave_entitlement.no_of_days as no_of_days,
sum(IF(ohrm_leave.status = 2, ohrm_leave_leave_entitlement.length_days, 0)) AS scheduled,
sum(IF(ohrm_leave.status = 3, ohrm_leave_leave_entitlement.length_days, 0)) AS taken
FROM ohrm_leave_entitlement LEFT JOIN ohrm_leave_leave_entitlement ON
ohrm_leave_entitlement.id = ohrm_leave_leave_entitlement.entitlement_id
LEFT JOIN ohrm_leave ON ohrm_leave.id = ohrm_leave_leave_entitlement.leave_id AND
( $X{>,ohrm_leave.date,toDate} OR $X{<,ohrm_leave.date,fromDate} )
WHERE ohrm_leave_entitlement.deleted=0 AND $X{=,ohrm_leave_entitlement.emp_number,empNumber} AND
$X{IN,ohrm_leave_entitlement.leave_type_id,leaveTypeId} AND
(
( $X{<=,ohrm_leave_entitlement.from_date,fromDate} AND $X{>=,ohrm_leave_entitlement.to_date,fromDate} ) OR
( $X{<=,ohrm_leave_entitlement.from_date,toDate} AND $X{>=,ohrm_leave_entitlement.to_date,toDate} ) OR
( $X{>=,ohrm_leave_entitlement.from_date,fromDate} AND $X{<=,ohrm_leave_entitlement.to_date,toDate} )
)
GROUP BY ohrm_leave_entitlement.id
) AS A
GROUP BY A.leave_type_id
ORDER BY A.leave_type_id
leaveTypeId
A.leave_type_id
leaveTypeId
Leave Type ID
1
sum(A.no_of_days) - sum(A.scheduled) - sum(A.taken)
entitlement_total
Leave Entitlements (Days)
120
right
leave/viewLeaveEntitlements?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&stddate=1
FROM ohrm_leave_type LEFT JOIN
ohrm_leave ON ohrm_leave_type.id = ohrm_leave.leave_type_id AND
$X{=,ohrm_leave.emp_number,empNumber} AND
ohrm_leave.status = 1 AND
$X{>=,ohrm_leave.date,fromDate} AND $X{<=,ohrm_leave.date,toDate}
WHERE
ohrm_leave_type.deleted = 0 AND
$X{IN,ohrm_leave_type.id,leaveTypeId}
GROUP BY ohrm_leave_type.id
ORDER BY ohrm_leave_type.id
leaveTypeId
ohrm_leave_type.id
leaveTypeId
Leave Type ID
1
sum(length_days)
pending
Leave Pending Approval (Days)
120
right
leave/viewLeaveList?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&status=1&stddate=1
FROM ohrm_leave_type LEFT JOIN
ohrm_leave ON ohrm_leave_type.id = ohrm_leave.leave_type_id AND
$X{=,ohrm_leave.emp_number,empNumber} AND
ohrm_leave.status = 2 AND
$X{>=,ohrm_leave.date,fromDate} AND $X{<=,ohrm_leave.date,toDate}
WHERE
ohrm_leave_type.deleted = 0 AND
$X{IN,ohrm_leave_type.id,leaveTypeId}
GROUP BY ohrm_leave_type.id
ORDER BY ohrm_leave_type.id
leaveTypeId
ohrm_leave_type.id
leaveTypeId
Leave Type ID
1
sum(length_days)
scheduled
Leave Scheduled (Days)
120
right
leave/viewLeaveList?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&status=2&stddate=1
FROM ohrm_leave WHERE $X{=,emp_number,empNumber} AND
status = 3 AND
$X{IN,ohrm_leave.leave_type_id,leaveTypeId} AND
$X{>=,ohrm_leave.date,fromDate} AND $X{<=,ohrm_leave.date,toDate}
GROUP BY leave_type_id
ORDER BY ohrm_leave.leave_type_id
leaveTypeId
ohrm_leave.leave_type_id
leaveTypeId
Leave Type ID
1
sum(length_days)
taken
Leave Taken (Days)
120
right
leave/viewLeaveList?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&status=3&stddate=1
FROM ohrm_leave_type WHERE deleted = 0 AND $X{IN,ohrm_leave_type.id,leaveTypeId} ORDER BY ohrm_leave_type.id
leaveTypeId
ohrm_leave_type.id
leaveTypeId
Leave Type ID
1
ohrm_leave_type.name
unused
Leave Balance (Days)
160
right
100
');
INSERT INTO ohrm_advanced_report (id, name, definition) VALUES
(2, 'Leave Entitlements and Usage Report', '
1
1
FROM hs_hr_employee
LEFT JOIN hs_hr_emp_locations ON hs_hr_employee.emp_number = hs_hr_emp_locations.emp_number
WHERE $X{IN,hs_hr_employee.emp_number,emp_numbers}
AND $X{=,hs_hr_employee.job_title_code,job_title}
AND $X{IN,hs_hr_employee.work_station,sub_unit}
AND $X{IN,hs_hr_emp_locations.location_id,location}
AND $X{IS NULL,hs_hr_employee.termination_id,terminated}
ORDER BY hs_hr_employee.emp_lastname
empNumber
hs_hr_employee.emp_number
empNumber
Employee Number
1
hs_hr_employee.termination_id
termination_id
Termination ID
1
CONCAT(hs_hr_employee.emp_firstname, \' \', hs_hr_employee.emp_lastname)
employeeName
Employee
150
FROM (
SELECT ohrm_leave_entitlement.id as id,
ohrm_leave_entitlement.emp_number as emp_number,
ohrm_leave_entitlement.no_of_days as no_of_days,
sum(IF(ohrm_leave.status = 2, ohrm_leave_leave_entitlement.length_days, 0)) AS scheduled,
sum(IF(ohrm_leave.status = 3, ohrm_leave_leave_entitlement.length_days, 0)) AS taken
FROM ohrm_leave_entitlement LEFT JOIN ohrm_leave_leave_entitlement ON
ohrm_leave_entitlement.id = ohrm_leave_leave_entitlement.entitlement_id
LEFT JOIN ohrm_leave ON ohrm_leave.id = ohrm_leave_leave_entitlement.leave_id AND
( $X{>,ohrm_leave.date,toDate} OR $X{<,ohrm_leave.date,fromDate} )
WHERE ohrm_leave_entitlement.deleted=0 AND $X{=,ohrm_leave_entitlement.leave_type_id,leaveType}
AND $X{IN,ohrm_leave_entitlement.emp_number,empNumber} AND
(
( $X{<=,ohrm_leave_entitlement.from_date,fromDate} AND $X{>=,ohrm_leave_entitlement.to_date,fromDate} ) OR
( $X{<=,ohrm_leave_entitlement.from_date,toDate} AND $X{>=,ohrm_leave_entitlement.to_date,toDate} ) OR
( $X{>=,ohrm_leave_entitlement.from_date,fromDate} AND $X{<=,ohrm_leave_entitlement.to_date,toDate} )
)
GROUP BY ohrm_leave_entitlement.id
) AS A
GROUP BY A.emp_number
ORDER BY A.emp_number
empNumber
A.emp_number
empNumber
Emp Number
1
sum(A.no_of_days) - sum(A.scheduled) - sum(A.taken)
entitlement_total
Leave Entitlements (Days)
120
right
leave/viewLeaveEntitlements?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&stddate=1
FROM ohrm_leave WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
status = 1 AND
$X{IN,ohrm_leave.emp_number,empNumber} AND
$X{>=,ohrm_leave.date,fromDate} AND $X{<=,ohrm_leave.date,toDate}
GROUP BY emp_number
ORDER BY ohrm_leave.emp_number
empNumber
ohrm_leave.emp_number
empNumber
Emp Number
1
sum(length_days)
pending
Leave Pending Approval (Days)
121
right
leave/viewLeaveList?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&status=1&stddate=1
FROM ohrm_leave WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
status = 2 AND
$X{IN,ohrm_leave.emp_number,empNumber} AND
$X{>=,ohrm_leave.date,fromDate} AND $X{<=,ohrm_leave.date,toDate}
GROUP BY emp_number
ORDER BY ohrm_leave.emp_number
empNumber
ohrm_leave.emp_number
empNumber
Emp Number
1
sum(length_days)
scheduled
Leave Scheduled (Days)
121
right
leave/viewLeaveList?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&status=2&stddate=1
FROM ohrm_leave WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
status = 3 AND
$X{IN,ohrm_leave.emp_number,empNumber} AND
$X{>=,ohrm_leave.date,fromDate} AND $X{<=,ohrm_leave.date,toDate}
GROUP BY emp_number
ORDER BY ohrm_leave.emp_number
empNumber
ohrm_leave.emp_number
empNumber
Emp Number
1
sum(length_days)
taken
Leave Taken (Days)
120
right
leave/viewLeaveList?empNumber=$P{empNumber}&fromDate=$P{fromDate}&toDate=$P{toDate}&leaveTypeId=$P{leaveTypeId}&status=3&stddate=1
FROM hs_hr_employee WHERE $X{IN,hs_hr_employee.emp_number,empNumber} ORDER BY hs_hr_employee.emp_number
empNumber
hs_hr_employee.emp_number
empNumber
Employee Number
1
hs_hr_employee.emp_firstname
unused
Leave Balance (Days)
150
right
20
');
INSERT INTO `ohrm_leave_status` (`status`,`name`) VALUES
(-1, 'REJECTED'),
(0, 'CANCELLED'),
(1, 'PENDING APPROVAL'),
(2, 'SCHEDULED'),
(3, 'TAKEN'),
(4, 'WEEKEND'),
(5, 'HOLIDAY');
INSERT INTO ohrm_home_page (`user_role_id`, `action`, `enable_class`, `priority`) VALUES
(1, 'pim/viewEmployeeList', NULL, 10),
(2, 'pim/viewMyDetails', NULL, 0);
INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
(2, 1, 'admin/viewSystemUsers', NULL, 20),
(3, 1, 'pim/viewEmployeeList', NULL, 20),
(3, 3, 'pim/viewEmployeeList', NULL, 10),
(3, 2, 'pim/viewMyDetails', NULL, 0),
(4, 1, 'leave/viewLeaveList/reset/1', NULL, 20),
(4, 3, 'leave/viewLeaveList/reset/1', NULL, 10),
(4, 2, 'leave/viewMyLeaveList', NULL, 0),
(4, 1, 'leave/defineLeavePeriod', 'LeavePeriodDefinedHomePageEnabler', 100),
(4, 2, 'leave/showLeavePeriodNotDefinedWarning', 'LeavePeriodDefinedHomePageEnabler', 90),
(5, 1, 'time/viewEmployeeTimesheet', NULL, 20),
(5, 2, 'time/viewMyTimesheet', NULL, 0),
(5, 1, 'time/defineTimesheetPeriod', 'TimesheetPeriodDefinedHomePageEnabler', 100),
(5, 2, 'time/timesheetPeriodNotDefined', 'TimesheetPeriodDefinedHomePageEnabler', 100),
(7, 1, 'recruitment/viewCandidates', NULL, 20),
(7, 5, 'recruitment/viewCandidates', NULL, 10),
(7, 6, 'recruitment/viewCandidates', NULL, 5);
INSERT INTO `hs_hr_config`(`key`,`value`) VALUES
('beacon.activation_acceptance_status','off'),
('beacon.company_name',''),
('beacon.activiation_status','off'),
('beacon.uuid',0),
('beacon.next_flash_time','0000-00-00'),
('beacon.lock','unlocked'),
('beacon.flash_period','120'),
('admin.product_type','os');
INSERT INTO `ohrm_module` (`name`,`status`) VALUES
('communication',1);
INSERT INTO `ohrm_datapoint_type`(`id`,`name`,`action_class`) VALUES
(1,'config','configDatapointProcessor'),
(2,'count','countDatapointProcessor'),
(3, 'session', 'sessionDatapointProcessor'),
(4,'organization','OrganizationDataProcessor');
SET @admin_user_role_id := (SELECT id FROM ohrm_user_role WHERE name = 'Admin' LIMIT 1);
SET @ess_user_role_id := (SELECT id FROM ohrm_user_role WHERE name = 'ESS' LIMIT 1);
SET @admin_home_page := (SELECT id FROM ohrm_home_page WHERE user_role_id = @admin_user_role_id LIMIT 1);
SET @ess_home_page := (SELECT id FROM ohrm_home_page WHERE user_role_id = @ess_user_role_id LIMIT 1);
UPDATE ohrm_home_page SET action = 'dashboard/index', priority = '15' WHERE user_role_id = @admin_home_page;
UPDATE ohrm_home_page SET action = 'dashboard/index', priority = '5' WHERE user_role_id = @ess_home_page;
INSERT INTO ohrm_module (name, status) VALUES ('dashboard', 1);
SET @dashboard_module := (SELECT id FROM ohrm_module WHERE name = 'dashboard' LIMIT 1);
INSERT INTO ohrm_screen (name, module_id, action_url) VALUES ('Dashboard', @dashboard_module, 'index');
SET @dashboard_screen := (SELECT id FROM ohrm_screen WHERE name = 'Dashboard' LIMIT 1);
INSERT INTO ohrm_menu_item (menu_title, screen_id, parent_id, level, order_hint, url_extras, status) VALUES
('Dashboard', @dashboard_screen, NULL, 1, 800, NULL, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role_id, @dashboard_screen, 1, 0, 0, 0),
(@ess_user_role_id, @dashboard_screen, 1, 0, 0, 0);
INSERT INTO `ohrm_reviewer_group` (`id`, `name`,`piority`) VALUES
(1, 'Supervisor',1),
(2, 'Employee',2);
SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');
SET @supervisor_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Supervisor');
INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('performance', 1);
SET @module_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Save KPI', @module_id, 'saveKpi');
SET @save_kpi_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Saearch KPI', @module_id, 'searchKpi');
SET @search_kpi_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('My Reviews', @module_id, 'myPerformanceReview');
SET @my_reviews_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Add Review', @module_id, 'saveReview');
SET @add_review_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Review Evaluate', @module_id, 'reviewEvaluate');
SET @review_evaluate_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Review Evaluate By Admin', @module_id, 'reviewEvaluateByAdmin');
SET @review_evaluate_admin_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Search Evaluate Performance', @module_id, 'searchEvaluatePerformancReview');
SET @search_evaluate_performance_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Search Performance Review', @module_id, 'searchPerformancReview');
SET @search_performance_review_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Performance', NULL, NULL, 1, 700, '', 1);
SET @performance_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Performance' AND `level` = 1);
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Configure', NULL, @performance_menu_id, 2, 100, '', 1);
SET @ConfigureKPI_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Manage Reviews', NULL, @performance_menu_id, 2, 200, '', 1);
SET @Manage_Reviews_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('KPIs', @search_kpi_screen_id, @ConfigureKPI_screen_id, 3, 100, '', 1),
('Manage Reviews', @search_performance_review_screen_id, @Manage_Reviews_screen_id, 3, 100, '', 1),
('My Reviews', @my_reviews_screen_id, @Manage_Reviews_screen_id, 3, 200, '', 1),
('Review List', @search_evaluate_performance_screen_id, @Manage_Reviews_screen_id, 3, 300, '', 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @save_kpi_screen_id, 1, 1, 1, 0),
(@admin_role_id, @search_kpi_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_review_screen_id, 1, 1, 1, 0),
(@admin_role_id, @review_evaluate_admin_screen_id, 1, 1, 1, 0),
(@admin_role_id, @search_performance_review_screen_id, 1, 1, 1, 1),
(@ess_role_id, @search_evaluate_performance_screen_id, 1, 0, 1, 0),
(@ess_role_id, @review_evaluate_screen_id, 1, 1, 1, 0),
(@ess_role_id, @my_reviews_screen_id, 1, 0, 1, 0),
(@supervisor_role_id, @review_evaluate_admin_screen_id, 1, 1, 1, 0),
(@ess_role_id, @review_evaluate_admin_screen_id, 1, 1, 1, 0);
SET @admin_user_role := (SELECT id FROM ohrm_user_role WHERE name = 'Admin');
SET @ess_user_role := (SELECT id FROM ohrm_user_role WHERE name = 'ESS');
SET @performance_module_id:= (SELECT `id` FROM `ohrm_module` WHERE `name` = 'performance');
-- Admin Section. Manage Trackers.
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
( 'Manage_Trackers', @performance_module_id, 'addPerformanceTracker');
SET @manage_performance_trackers_screen_id := (SELECT LAST_INSERT_ID());
SET @performance_menu_id:= (SELECT id FROM ohrm_menu_item where menu_title = 'Performance');
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Trackers', @manage_performance_trackers_screen_id, @ConfigureKPI_screen_id, 3, 200, NULL, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role, @manage_performance_trackers_screen_id, 1, 1, 1, 1),
(@ess_user_role, @manage_performance_trackers_screen_id, 0, 0, 0, 0);
-- Admin/ ESS Section Employee Trackers.
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
( 'Employee_Trackers', @performance_module_id, 'viewEmployeePerformanceTrackerList');
SET @employee_trackers_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Employee Trackers', @employee_trackers_screen_id, @performance_menu_id, 2, 800, NULL, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role, @employee_trackers_screen_id, 1, 1, 1, 1),
(@ess_user_role, @employee_trackers_screen_id, 1, 1, 1, 0);
-- ESS secetion. My Trackers.
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
( 'My_Trackers', @performance_module_id, 'viewMyPerformanceTrackerList');
SET @my_trackers_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('My Trackers', @my_trackers_screen_id, @performance_menu_id, 2, 700, NULL, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role, @my_trackers_screen_id, 0, 0, 0, 0),
(@ess_user_role, @my_trackers_screen_id, 1, 0, 1, 0);
-- Tracker Logs. (No menu item)
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
( 'Employee_Tracker_Logs', @performance_module_id, 'addPerformanceTrackerLog');
SET @employee_tracker_logs_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role, @employee_tracker_logs_screen_id, 1, 1, 1, 0),
(@ess_user_role, @employee_tracker_logs_screen_id, 1, 0, 0, 0);
-- Install CorporateDirectory --
INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('directory', 1);
SET @module_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Directory', @module_id, 'viewDirectory');
SET @directory_configuration_screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Directory', @directory_configuration_screen_id, null, 1, 1000, '/reset/1', 1);
SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @directory_configuration_screen_id, 1, 1, 1, 1),
(@ess_role_id, @directory_configuration_screen_id, 1, 1, 1, 1);
-- install open id
INSERT INTO `hs_hr_config` (`key` ,`value`) VALUES ('domain.name', 'localhost');
INSERT INTO ohrm_screen ( `name`, `module_id`, `action_url`) VALUES ( 'Manage OpenId', 2, 'openIdProvider');
SET @opnid_screen_id := (SELECT LAST_INSERT_ID());
SET @admin_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Admin' AND `level` = 1);
SET @configuration_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Configuration' AND `level` = 2 AND parent_id = @admin_menu_id);
SET @max_order := (SELECT MAX(`order_hint`) FROM ohrm_menu_item WHERE parent_id = @configuration_id);
INSERT INTO ohrm_menu_item ( `menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Social Media Authentication', @opnid_screen_id, @configuration_id, 3, @max_order+100, NULL, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(1, @opnid_screen_id, 1, 1, 1, 0);
INSERT INTO hs_hr_config (`key`, `value`) VALUES
('openId.provider.added', 'on');