VkSgVBL7

1/*
2SQLyog Ultimate v12.4.3 (64 bit)
3MySQL - 10.4.6-MariaDB : Database - naf269
4*********************************************************************
5*/
6
7/*!40101 SET NAMES utf8 */;
8
9/*!40101 SET SQL_MODE=''*/;
10
11/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
12/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
13/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
14/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
15CREATE DATABASE /*!32312 IF NOT EXISTS*/`naf269` /*!40100 DEFAULT CHARACTER SET latin1 */;
16
17USE `naf269`;
18
19/*Table structure for table `payroll` */
20
21DROP TABLE IF EXISTS `payroll`;
22
23CREATE TABLE `payroll` (
24  `payroll_id` int(11) NOT NULL AUTO_INCREMENT,
25  `period` varchar(20) NOT NULL,
26  `instance_id` int(11) NOT NULL,
27  `ref_code` varchar(40) NOT NULL,
28  `ref_id` int(11) NOT NULL,
29  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
30  `issued_date` date NOT NULL,
31  `notes` varchar(128) DEFAULT NULL,
32  `verified_by` int(11) DEFAULT NULL,
33  `verified_dtm` datetime DEFAULT NULL,
34  PRIMARY KEY (`payroll_id`),
35  KEY `fk_payroll_period_idx` (`period`),
36  KEY `fk_payroll_instance_idx` (`instance_id`),
37  KEY `fk_payroll_ref_idx` (`ref_code`),
38  CONSTRAINT `fk_payroll_instance` FOREIGN KEY (`instance_id`) REFERENCES `payroll_instances` (`instance_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
39  CONSTRAINT `fk_payroll_period` FOREIGN KEY (`period`) REFERENCES `payroll_period` (`period`) ON DELETE NO ACTION ON UPDATE NO ACTION,
40  CONSTRAINT `fk_payroll_ref` FOREIGN KEY (`ref_code`) REFERENCES `payroll_reference` (`code`) ON UPDATE CASCADE
41) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
42
43/*Data for the table `payroll` */
44
45insert  into `payroll`(`payroll_id`,`period`,`instance_id`,`ref_code`,`ref_id`,`total_amount`,`issued_date`,`notes`,`verified_by`,`verified_dtm`) values 
46(1,'201901-1',1,'EMPLOYEE',1,5844700.00,'2019-09-10',NULL,NULL,NULL);
47
48/*Table structure for table `payroll_bpjs_comp_person` */
49
50DROP TABLE IF EXISTS `payroll_bpjs_comp_person`;
51
52CREATE TABLE `payroll_bpjs_comp_person` (
53  `bpjs_comp_person_id` int(11) NOT NULL AUTO_INCREMENT,
54  `person_id` int(11) NOT NULL,
55  `bpjs_component_id` smallint(6) NOT NULL,
56  `company_share` decimal(3,2) DEFAULT 0.00,
57  `person_share` decimal(3,2) DEFAULT 0.00,
58  `start_period` varchar(20) NOT NULL,
59  `end_period` varchar(20) DEFAULT NULL,
60  `last_update_dtm` datetime NOT NULL,
61  `update_by` varchar(32) NOT NULL,
62  PRIMARY KEY (`bpjs_comp_person_id`),
63  KEY `fk_bpjsemp_bpjs_idx` (`bpjs_component_id`),
64  CONSTRAINT `fk_bpjsemp_bpjs` FOREIGN KEY (`bpjs_component_id`) REFERENCES `payroll_bpjs_component` (`bpjs_component_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
65) ENGINE=InnoDB DEFAULT CHARSET=latin1;
66
67/*Data for the table `payroll_bpjs_comp_person` */
68
69/*Table structure for table `payroll_bpjs_component` */
70
71DROP TABLE IF EXISTS `payroll_bpjs_component`;
72
73CREATE TABLE `payroll_bpjs_component` (
74  `bpjs_component_id` smallint(6) NOT NULL AUTO_INCREMENT,
75  `bpjs_component_name` varchar(40) NOT NULL,
76  `bpjs_type` tinyint(4) NOT NULL,
77  `company_share` decimal(3,2) DEFAULT 0.00,
78  `person_share` decimal(3,2) DEFAULT 0.00,
79  `is_active` tinyint(1) DEFAULT 1,
80  `start_period` varchar(20) NOT NULL,
81  `end_period` varchar(20) DEFAULT NULL,
82  `last_update_dtm` datetime NOT NULL,
83  `update_by` int(11) NOT NULL,
84  PRIMARY KEY (`bpjs_component_id`),
85  KEY `fk_bpjscomp_type_idx` (`bpjs_type`),
86  CONSTRAINT `fk_bpjscomp_type` FOREIGN KEY (`bpjs_type`) REFERENCES `payroll_bpjs_type` (`bpjs_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
87) ENGINE=InnoDB DEFAULT CHARSET=latin1;
88
89/*Data for the table `payroll_bpjs_component` */
90
91/*Table structure for table `payroll_bpjs_type` */
92
93DROP TABLE IF EXISTS `payroll_bpjs_type`;
94
95CREATE TABLE `payroll_bpjs_type` (
96  `bpjs_type_id` tinyint(4) NOT NULL,
97  `name` varchar(40) NOT NULL,
98  `description` varchar(120) DEFAULT NULL,
99  PRIMARY KEY (`bpjs_type_id`)
100) ENGINE=InnoDB DEFAULT CHARSET=latin1;
101
102/*Data for the table `payroll_bpjs_type` */
103
104insert  into `payroll_bpjs_type`(`bpjs_type_id`,`name`,`description`) values 
105(1,'JKK','Jaminan Kecelakaan Kerja'),
106(2,'JHT','Jaminan Hari Tua');
107
108/*Table structure for table `payroll_compensation_category` */
109
110DROP TABLE IF EXISTS `payroll_compensation_category`;
111
112CREATE TABLE `payroll_compensation_category` (
113  `compensation_category_id` tinyint(4) NOT NULL AUTO_INCREMENT,
114  `type_category` varchar(40) NOT NULL,
115  `description` varchar(120) DEFAULT NULL,
116  PRIMARY KEY (`compensation_category_id`),
117  UNIQUE KEY `type_category_UNIQUE` (`type_category`)
118) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
119
120/*Data for the table `payroll_compensation_category` */
121
122insert  into `payroll_compensation_category`(`compensation_category_id`,`type_category`,`description`) values 
123(1,'CATEGORY','employee category'),
124(2,'JOB_POSITION','position on an company organization'),
125(3,'OBJECT','item or object of an employee related to company');
126
127/*Table structure for table `payroll_compensation_type` */
128
129DROP TABLE IF EXISTS `payroll_compensation_type`;
130
131CREATE TABLE `payroll_compensation_type` (
132  `compensation_type_id` tinyint(4) NOT NULL AUTO_INCREMENT,
133  `name` varchar(40) DEFAULT NULL,
134  `description` varchar(120) DEFAULT NULL,
135  `type_category` varchar(40) NOT NULL,
136  `is_range` char(1) NOT NULL DEFAULT 'Y',
137  PRIMARY KEY (`compensation_type_id`),
138  KEY `fk_comptype_cat_idx` (`type_category`),
139  CONSTRAINT `fk_comptype_cat` FOREIGN KEY (`type_category`) REFERENCES `payroll_compensation_category` (`type_category`) ON DELETE CASCADE ON UPDATE CASCADE
140) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
141
142/*Data for the table `payroll_compensation_type` */
143
144insert  into `payroll_compensation_type`(`compensation_type_id`,`name`,`description`,`type_category`,`is_range`) values 
145(1,'Basic Salary','','CATEGORY','Y'),
146(2,'Position Basic Benefit','','JOB_POSITION','Y'),
147(3,'Transportation Basic Benefit','','CATEGORY','Y'),
148(4,'Koperasi','Pinjaman koperasi','OBJECT','Y'),
149(5,'Sewa Laptop','','OBJECT','Y');
150
151/*Table structure for table `payroll_component` */
152
153DROP TABLE IF EXISTS `payroll_component`;
154
155CREATE TABLE `payroll_component` (
156  `component_id` smallint(6) NOT NULL AUTO_INCREMENT,
157  `component_parent_id` smallint(6) DEFAULT NULL,
158  `name` varchar(64) NOT NULL,
159  `description` varchar(128) DEFAULT NULL,
160  `component_type_id` tinyint(4) NOT NULL DEFAULT 1,
161  `component_compensation_id` int(11) DEFAULT NULL,
162  `amount` double DEFAULT 0,
163  `include_on_slip` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = yes, 0 = no',
164  `is_taxed` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = taxed, 0 = not taxed',
165  `credit_type` varchar(5) NOT NULL DEFAULT 'PLUS',
166  `func_calculate` varchar(64) DEFAULT NULL,
167  `start_date` date NOT NULL,
168  `end_date` date DEFAULT NULL,
169  PRIMARY KEY (`component_id`),
170  KEY `fk_comp_type_idx` (`component_type_id`),
171  CONSTRAINT `fk_comp_type` FOREIGN KEY (`component_type_id`) REFERENCES `payroll_component_type` (`component_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
172) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
173
174/*Data for the table `payroll_component` */
175
176insert  into `payroll_component`(`component_id`,`component_parent_id`,`name`,`description`,`component_type_id`,`component_compensation_id`,`amount`,`include_on_slip`,`is_taxed`,`credit_type`,`func_calculate`,`start_date`,`end_date`) values 
177(1,NULL,'Gaji pokok dan tunjangan','',1,NULL,NULL,1,0,'PLUS',NULL,'2019-09-06',NULL),
178(2,1,'Gaji pokok','',4,1,NULL,1,1,'PLUS',NULL,'2019-09-06',NULL),
179(3,1,'Tunjangan jabatan','',4,2,NULL,1,1,'PLUS',NULL,'2019-09-06',NULL),
180(4,1,'Tunjangan transportasi','',4,3,NULL,1,1,'PLUS',NULL,'2019-09-06',NULL),
181(5,NULL,'Penambahan','',1,NULL,NULL,1,0,'PLUS',NULL,'2019-09-06',NULL),
182(6,5,'Uang Makan','',2,NULL,500000,1,0,'PLUS',NULL,'2019-09-06',NULL),
183(7,NULL,'Pengurangan','',1,NULL,NULL,1,0,'MINUS',NULL,'2019-09-06',NULL),
184(8,7,'PPH 21','',5,NULL,NULL,1,0,'MINUS','calcTaxes','2019-09-06',NULL),
185(9,7,'Potongan Koperasi','',4,4,NULL,1,0,'MINUS',NULL,'2019-09-13',NULL);
186
187/*Table structure for table `payroll_component_compensation` */
188
189DROP TABLE IF EXISTS `payroll_component_compensation`;
190
191CREATE TABLE `payroll_component_compensation` (
192  `compensation_id` int(11) NOT NULL AUTO_INCREMENT,
193  `compensation_type_id` tinyint(4) NOT NULL,
194  `ref_code` varchar(40) DEFAULT NULL,
195  `ref_id` varchar(32) DEFAULT NULL,
196  `amount_min` decimal(10,2) NOT NULL DEFAULT 0.00,
197  `amount_max` decimal(10,2) DEFAULT NULL,
198  `start_date` date NOT NULL,
199  `end_date` date DEFAULT NULL,
200  `last_update_dtm` datetime NOT NULL,
201  `update_by` int(11) DEFAULT NULL,
202  PRIMARY KEY (`compensation_id`),
203  KEY `fk_paycc_type_idx` (`compensation_type_id`),
204  KEY `fk_paycc_ref_idx` (`ref_code`),
205  CONSTRAINT `fk_paycc_ref` FOREIGN KEY (`ref_code`) REFERENCES `payroll_reference` (`code`) ON UPDATE CASCADE,
206  CONSTRAINT `fk_paycc_type` FOREIGN KEY (`compensation_type_id`) REFERENCES `payroll_compensation_type` (`compensation_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
207) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
208
209/*Data for the table `payroll_component_compensation` */
210
211insert  into `payroll_component_compensation`(`compensation_id`,`compensation_type_id`,`ref_code`,`ref_id`,`amount_min`,`amount_max`,`start_date`,`end_date`,`last_update_dtm`,`update_by`) values 
212(1,1,'EMPLOYEE','1',0.00,5000000.00,'2019-09-04','2019-09-13','2019-09-04 09:38:52',10000000),
213(2,3,'EMP_CATEGORY','1',200000.00,1000000.00,'2019-09-04',NULL,'2019-09-12 16:23:51',10000000),
214(3,2,'ORG_POSITION','1',0.00,3000000.00,'2019-09-12',NULL,'2019-09-12 14:18:57',10000000),
215(4,1,'EMP_CATEGORY','1',0.00,10000000.00,'2019-09-13',NULL,'2019-09-13 09:23:46',10000000),
216(5,4,'EMP_OBJECT',NULL,0.00,6000000.00,'2019-09-13',NULL,'2019-09-13 10:06:18',10000000),
217(6,2,'ORG_POSITION','4',500000.00,15000000.00,'2019-09-13',NULL,'2019-09-13 10:11:32',10000000);
218
219/*Table structure for table `payroll_component_type` */
220
221DROP TABLE IF EXISTS `payroll_component_type`;
222
223CREATE TABLE `payroll_component_type` (
224  `component_type_id` tinyint(4) NOT NULL AUTO_INCREMENT,
225  `name` varchar(45) NOT NULL,
226  PRIMARY KEY (`component_type_id`)
227) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
228
229/*Data for the table `payroll_component_type` */
230
231insert  into `payroll_component_type`(`component_type_id`,`name`) values 
232(1,'Group Parent'),
233(2,'Fixed'),
234(3,'Dinamic'),
235(4,'Compensation'),
236(5,'Taxes');
237
238/*Table structure for table `payroll_detail` */
239
240DROP TABLE IF EXISTS `payroll_detail`;
241
242CREATE TABLE `payroll_detail` (
243  `detail_id` int(11) NOT NULL AUTO_INCREMENT,
244  `payroll_id` int(11) NOT NULL,
245  `component_id` smallint(6) NOT NULL,
246  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
247  PRIMARY KEY (`detail_id`),
248  KEY `fk_paydet_pay_idx` (`payroll_id`),
249  KEY `fk_paydet_comp_idx` (`component_id`),
250  CONSTRAINT `fk_paydet_comp` FOREIGN KEY (`component_id`) REFERENCES `payroll_component` (`component_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
251  CONSTRAINT `fk_paydet_pay` FOREIGN KEY (`payroll_id`) REFERENCES `payroll` (`payroll_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
252) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
253
254/*Data for the table `payroll_detail` */
255
256insert  into `payroll_detail`(`detail_id`,`payroll_id`,`component_id`,`amount`) values 
257(1,1,2,5000000.00),
258(2,1,3,200000.00),
259(3,1,4,50000.00),
260(4,1,8,-94700.00),
261(5,1,1,0.00),
262(6,1,5,0.00),
263(7,1,6,500000.00),
264(8,1,7,0.00);
265
266/*Table structure for table `payroll_detail_bpjs` */
267
268DROP TABLE IF EXISTS `payroll_detail_bpjs`;
269
270CREATE TABLE `payroll_detail_bpjs` (
271  `payroll_id` int(11) NOT NULL,
272  `bruto_taxed_income` decimal(12,2) NOT NULL,
273  `jkk_1` decimal(8,2) DEFAULT 0.00,
274  `jkk_2` decimal(8,2) DEFAULT 0.00,
275  `jkm_1` decimal(8,2) DEFAULT 0.00,
276  `jkm_2` decimal(8,2) DEFAULT 0.00,
277  `jht_1` decimal(8,2) DEFAULT 0.00,
278  `jht_2` decimal(8,2) DEFAULT 0.00,
279  `jp_1` decimal(8,2) DEFAULT 0.00,
280  `jp_2` decimal(8,2) DEFAULT 0.00,
281  `total_bpjs_tk_1` decimal(10,2) DEFAULT 0.00,
282  `total_bpjs_tk_2` decimal(10,2) DEFAULT 0.00,
283  `jkes_1` decimal(8,2) DEFAULT 0.00,
284  `jkes_2` decimal(8,2) DEFAULT 0.00,
285  KEY `fk_detbpjs_payroll_idx` (`payroll_id`),
286  CONSTRAINT `fk_detbpjs_payroll` FOREIGN KEY (`payroll_id`) REFERENCES `payroll` (`payroll_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
287) ENGINE=InnoDB DEFAULT CHARSET=latin1;
288
289/*Data for the table `payroll_detail_bpjs` */
290
291/*Table structure for table `payroll_detail_taxes` */
292
293DROP TABLE IF EXISTS `payroll_detail_taxes`;
294
295CREATE TABLE `payroll_detail_taxes` (
296  `payroll_tax_id` int(11) NOT NULL AUTO_INCREMENT,
297  `payroll_id` int(11) NOT NULL,
298  `previous_payroll_id` int(11) DEFAULT NULL,
299  `npwp` varchar(20) DEFAULT NULL,
300  `marital_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = single, 1 = married',
301  `num_child` tinyint(2) NOT NULL DEFAULT 0,
302  `bruto_taxed_income` decimal(12,2) NOT NULL DEFAULT 0.00,
303  `job_payload` decimal(10,2) NOT NULL DEFAULT 0.00,
304  `jht` decimal(8,2) NOT NULL DEFAULT 0.00,
305  `jp` decimal(8,2) NOT NULL DEFAULT 0.00,
306  `net_taxed_income_month` decimal(12,2) NOT NULL DEFAULT 0.00,
307  `net_taxed_income_year` decimal(12,2) NOT NULL DEFAULT 0.00,
308  `ptkp_yearly` decimal(12,2) NOT NULL DEFAULT 0.00,
309  `pkp_yearly` decimal(12,2) NOT NULL DEFAULT 0.00,
310  `tax_year` decimal(12,2) NOT NULL DEFAULT 0.00,
311  `tax_month` decimal(10,2) NOT NULL DEFAULT 0.00,
312  PRIMARY KEY (`payroll_tax_id`),
313  KEY `fk_paytax_payroll_idx` (`payroll_id`),
314  CONSTRAINT `fk_paytax_payroll` FOREIGN KEY (`payroll_id`) REFERENCES `payroll` (`payroll_id`) ON DELETE CASCADE ON UPDATE CASCADE
315) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
316
317/*Data for the table `payroll_detail_taxes` */
318
319insert  into `payroll_detail_taxes`(`payroll_tax_id`,`payroll_id`,`previous_payroll_id`,`npwp`,`marital_status`,`num_child`,`bruto_taxed_income`,`job_payload`,`jht`,`jp`,`net_taxed_income_month`,`net_taxed_income_year`,`ptkp_yearly`,`pkp_yearly`,`tax_year`,`tax_month`) values 
320(5,1,NULL,NULL,0,0,5250000.00,945000.00,0.00,0.00,4987500.00,59850000.00,54000000.00,5850000.00,0.00,0.00);
321
322/*Table structure for table `payroll_generator_scheduler` */
323
324DROP TABLE IF EXISTS `payroll_generator_scheduler`;
325
326CREATE TABLE `payroll_generator_scheduler` (
327  `scheduler_id` int(11) NOT NULL AUTO_INCREMENT,
328  `payroll_period` varchar(20) NOT NULL,
329  `posted_by` int(11) NOT NULL,
330  `schedule_on` datetime NOT NULL,
331  `sch_status_process` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = not yet executed, 1 = inprogress, 2 = execute completed',
332  `sch_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0=not yet processed, 1 = complete succesfully, 2 = complete with some error, 3 = failed',
333  `sch_status_desc` varchar(200) DEFAULT NULL,
334  `completed_on` datetime DEFAULT NULL,
335  PRIMARY KEY (`scheduler_id`),
336  KEY `fk_sch_payperiod_idx` (`payroll_period`),
337  CONSTRAINT `fk_sch_payperiod` FOREIGN KEY (`payroll_period`) REFERENCES `payroll_period` (`period`) ON DELETE NO ACTION ON UPDATE NO ACTION
338) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
339
340/*Data for the table `payroll_generator_scheduler` */
341
342insert  into `payroll_generator_scheduler`(`scheduler_id`,`payroll_period`,`posted_by`,`schedule_on`,`sch_status_process`,`sch_status`,`sch_status_desc`,`completed_on`) values 
343(1,'201901-1',1,'2019-09-09 09:48:25',2,2,'instance_id:2-Invalid component instance','2019-09-09 10:02:46'),
344(2,'201901-1',1,'2019-09-09 16:51:30',2,2,'instance_id:2-Invalid component instance','2019-09-09 16:51:49'),
345(3,'201901-1',1,'2019-09-09 16:57:16',2,2,'instance_id:2-Invalid component instance','2019-09-09 16:57:36'),
346(4,'201901-1',1,'2019-09-09 16:59:52',2,2,'instance_id:2-Invalid component instance','2019-09-09 17:00:19'),
347(5,'201901-1',1,'2019-09-10 09:00:58',2,2,'instance_id:1-Invalid component instance | instance_id:2-Invalid component instance | ','2019-09-10 09:01:08'),
348(6,'201901-1',1,'2019-09-10 09:03:30',2,2,'instance_id:2-Invalid component instance | ','2019-09-10 09:03:36'),
349(7,'201901-1',1,'2019-09-10 09:04:03',2,2,'instance_id:1-Invalid component instance | instance_id:2-Invalid component instance | ','2019-09-10 09:04:09'),
350(8,'201901-1',1,'2019-09-10 09:31:04',2,2,'instance_id:1-Invalid ref id | instance_id:2-Invalid component instance | ','2019-09-10 09:31:12'),
351(9,'201901-1',1,'2019-09-10 09:32:32',2,2,'instance_id:1-Invalid ref id | instance_id:2-Invalid component instance | ','2019-09-10 09:32:37'),
352(10,'201901-1',1,'2019-09-10 09:40:40',2,2,'instance_id:1-Payroll period: 201901-1 generated successfully | instance_id:2-Invalid component instance | ','2019-09-10 09:40:45'),
353(11,'201901-1',1,'2019-09-10 09:56:46',2,2,'instance_id:1-Payroll period: 201901-1 generated successfully | instance_id:2-Invalid component instance | ','2019-09-10 09:56:52'),
354(12,'201901-1',1,'2019-09-10 10:03:56',2,2,'instance_id:1-Payroll period: 201901-1 generated successfully | instance_id:2-Invalid component instance | ','2019-09-10 10:04:01'),
355(13,'201901-1',1,'2019-09-10 10:08:54',2,2,'instance_id:1-Payroll period: 201901-1 generated successfully | instance_id:2-Invalid component instance | ','2019-09-10 10:08:59'),
356(14,'201901-1',1,'2019-09-10 14:12:01',2,2,'instance_id:1-Payroll period: 201901-1 generated successfully | instance_id:2-Invalid component instance | ','2019-09-10 14:12:07');
357
358/*Table structure for table `payroll_instance_component` */
359
360DROP TABLE IF EXISTS `payroll_instance_component`;
361
362CREATE TABLE `payroll_instance_component` (
363  `instance_component_id` int(11) NOT NULL AUTO_INCREMENT,
364  `instance_id` int(11) NOT NULL,
365  `component_id` smallint(6) NOT NULL,
366  `amount` decimal(10,2) DEFAULT 0.00,
367  `func_calculate` varchar(64) DEFAULT NULL,
368  `update_dtm` datetime NOT NULL,
369  `update_by` int(11) DEFAULT NULL,
370  PRIMARY KEY (`instance_component_id`),
371  KEY `fk_pic_instance_idx` (`instance_id`),
372  KEY `fk_pic_comp_idx` (`component_id`),
373  CONSTRAINT `fk_pic_comp` FOREIGN KEY (`component_id`) REFERENCES `payroll_component` (`component_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
374  CONSTRAINT `fk_pic_instance` FOREIGN KEY (`instance_id`) REFERENCES `payroll_instances` (`instance_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
375) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
376
377/*Data for the table `payroll_instance_component` */
378
379insert  into `payroll_instance_component`(`instance_component_id`,`instance_id`,`component_id`,`amount`,`func_calculate`,`update_dtm`,`update_by`) values 
380(1,1,1,0.00,'','2019-09-09 11:53:07',NULL),
381(2,1,2,0.00,'','2019-09-09 11:53:11',NULL),
382(3,1,3,0.00,'','2019-09-09 11:53:15',NULL),
383(4,1,4,0.00,'','2019-09-09 11:53:19',NULL),
384(5,1,5,0.00,'','2019-09-09 11:53:22',NULL),
385(6,1,6,1000000.00,'','2019-09-09 11:53:30',NULL),
386(7,1,7,0.00,'','2019-09-09 11:53:38',NULL),
387(8,1,8,0.00,'','2019-09-09 11:53:43',NULL);
388
389/*Table structure for table `payroll_instances` */
390
391DROP TABLE IF EXISTS `payroll_instances`;
392
393CREATE TABLE `payroll_instances` (
394  `instance_id` int(11) NOT NULL AUTO_INCREMENT,
395  `name` varchar(40) NOT NULL,
396  `instance_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = new, 1 = active, 2 = terminated',
397  `description` varchar(128) DEFAULT NULL,
398  `period_start` varchar(20) NOT NULL,
399  `period_end` varchar(20) DEFAULT NULL,
400  `terminated_date` date DEFAULT NULL,
401  PRIMARY KEY (`instance_id`)
402) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
403
404/*Data for the table `payroll_instances` */
405
406insert  into `payroll_instances`(`instance_id`,`name`,`instance_status`,`description`,`period_start`,`period_end`,`terminated_date`) values 
407(1,'Gaji Bulanan 2019',1,'Periode penggajian bulanan','201901-1','201902-1',NULL),
408(2,'Gaji Rapel 2019',1,'Periode penggajian rapel','201901-1','201902-1',NULL);
409
410/*Table structure for table `payroll_object_has_instance` */
411
412DROP TABLE IF EXISTS `payroll_object_has_instance`;
413
414CREATE TABLE `payroll_object_has_instance` (
415  `object_has_instance_id` int(11) NOT NULL AUTO_INCREMENT,
416  `ref_code` varchar(40) NOT NULL,
417  `ref_id` int(11) NOT NULL,
418  `instance_id` int(11) NOT NULL,
419  `period_start` varchar(20) NOT NULL,
420  `period_end` varchar(20) DEFAULT NULL,
421  `last_update_dtm` datetime NOT NULL,
422  `update_by` int(11) DEFAULT NULL,
423  PRIMARY KEY (`object_has_instance_id`),
424  KEY `fk_ohi_ref_idx` (`ref_code`),
425  KEY `fk_ohi_instance_idx` (`instance_id`),
426  CONSTRAINT `fk_ohi_instance` FOREIGN KEY (`instance_id`) REFERENCES `payroll_instances` (`instance_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
427  CONSTRAINT `fk_ohi_ref` FOREIGN KEY (`ref_code`) REFERENCES `payroll_reference` (`code`) ON UPDATE CASCADE
428) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
429
430/*Data for the table `payroll_object_has_instance` */
431
432insert  into `payroll_object_has_instance`(`object_has_instance_id`,`ref_code`,`ref_id`,`instance_id`,`period_start`,`period_end`,`last_update_dtm`,`update_by`) values 
433(6,'EMPLOYEE',1,1,'201901-1','201902-1','2019-09-10 04:32:18',NULL);
434
435/*Table structure for table `payroll_object_salary` */
436
437DROP TABLE IF EXISTS `payroll_object_salary`;
438
439CREATE TABLE `payroll_object_salary` (
440  `object_salary_id` int(11) NOT NULL AUTO_INCREMENT,
441  `component_id` smallint(6) NOT NULL,
442  `ref_code` varchar(40) NOT NULL,
443  `ref_id` int(11) NOT NULL,
444  `amount` double NOT NULL,
445  `period_start` varchar(20) NOT NULL,
446  `period_end` varchar(20) DEFAULT NULL,
447  `last_update_dtm` datetime NOT NULL,
448  `update_by` int(11) DEFAULT NULL,
449  PRIMARY KEY (`object_salary_id`),
450  KEY `fk_ohsal_ref_idx` (`ref_code`),
451  KEY `fk_ohsal_comp_idx` (`component_id`),
452  CONSTRAINT `fk_ohsal_comp` FOREIGN KEY (`component_id`) REFERENCES `payroll_component` (`component_id`) ON UPDATE CASCADE,
453  CONSTRAINT `fk_ohsal_ref` FOREIGN KEY (`ref_code`) REFERENCES `payroll_reference` (`code`) ON UPDATE CASCADE
454) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
455
456/*Data for the table `payroll_object_salary` */
457
458insert  into `payroll_object_salary`(`object_salary_id`,`component_id`,`ref_code`,`ref_id`,`amount`,`period_start`,`period_end`,`last_update_dtm`,`update_by`) values 
459(1,9,'EMPLOYEE',1,3000000,'201901-1','201901-1','2019-09-16 19:30:40',NULL),
460(2,4,'EMPLOYEE',1,200000,'201901-1',NULL,'2019-09-17 10:15:40',NULL),
461(3,3,'EMPLOYEE',1,3000000,'201901-1',NULL,'2019-09-17 10:26:17',NULL),
462(4,9,'EMPLOYEE',1,2000000,'201901-1','201901-1','2019-09-17 11:06:12',NULL),
463(5,9,'EMPLOYEE',1,1000000,'201901-1','201901-1','2019-09-17 11:07:45',NULL),
464(6,9,'EMPLOYEE',1,2000000,'201901-1','201901-1','2019-09-19 09:59:11',NULL),
465(7,9,'EMPLOYEE',1,5959000,'201901-1',NULL,'2019-09-19 11:05:36',NULL);
466
467/*Table structure for table `payroll_period` */
468
469DROP TABLE IF EXISTS `payroll_period`;
470
471CREATE TABLE `payroll_period` (
472  `period_id` int(11) NOT NULL AUTO_INCREMENT,
473  `period` varchar(20) NOT NULL,
474  `period_type_id` tinyint(4) NOT NULL,
475  `description` varchar(120) DEFAULT NULL,
476  `start_date` date NOT NULL,
477  `end_date` date NOT NULL,
478  PRIMARY KEY (`period_id`),
479  UNIQUE KEY `period_UNIQUE` (`period`),
480  KEY `fk_payperiod_type_idx` (`period_type_id`),
481  CONSTRAINT `payroll_period_ibfk_1` FOREIGN KEY (`period_type_id`) REFERENCES `payroll_period_types` (`payroll_period_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
482) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
483
484/*Data for the table `payroll_period` */
485
486insert  into `payroll_period`(`period_id`,`period`,`period_type_id`,`description`,`start_date`,`end_date`) values 
487(1,'201901-1',1,'Periode penggajian januari','2018-12-26','2019-01-25'),
488(2,'201902-1',1,'Periode penggajian februari','2019-02-26','2019-03-25');
489
490/*Table structure for table `payroll_period_types` */
491
492DROP TABLE IF EXISTS `payroll_period_types`;
493
494CREATE TABLE `payroll_period_types` (
495  `payroll_period_type_id` tinyint(4) NOT NULL AUTO_INCREMENT,
496  `code` varchar(12) NOT NULL,
497  `description` varchar(80) DEFAULT NULL,
498  PRIMARY KEY (`payroll_period_type_id`),
499  UNIQUE KEY `code_UNIQUE` (`code`)
500) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
501
502/*Data for the table `payroll_period_types` */
503
504insert  into `payroll_period_types`(`payroll_period_type_id`,`code`,`description`) values 
505(1,'BLN','Bulanan'),
506(2,'THR','Tunjangan Hari Raya'),
507(3,'BTT','Bonus Tengah Tahun'),
508(4,'BAT','Bonus Akhir Tahun'),
509(5,'RPL','Rapel');
510
511/*Table structure for table `payroll_reference` */
512
513DROP TABLE IF EXISTS `payroll_reference`;
514
515CREATE TABLE `payroll_reference` (
516  `reference_id` smallint(6) NOT NULL AUTO_INCREMENT,
517  `code` varchar(40) NOT NULL,
518  `description` varchar(128) DEFAULT NULL,
519  PRIMARY KEY (`reference_id`),
520  UNIQUE KEY `code_UNIQUE` (`code`)
521) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
522
523/*Data for the table `payroll_reference` */
524
525insert  into `payroll_reference`(`reference_id`,`code`,`description`) values 
526(1,'EMPLOYEE','reference to employee '),
527(2,'EMP_CATEGORY','reference to employee categories'),
528(3,'ORG_POSITION','reference to position on an org'),
529(4,'EMP_OBJECT','reference to employees object related to company');
530
531/*Table structure for table `payroll_tax_config` */
532
533DROP TABLE IF EXISTS `payroll_tax_config`;
534
535CREATE TABLE `payroll_tax_config` (
536  `tax_config_id` smallint(6) NOT NULL AUTO_INCREMENT,
537  `config_code` varchar(40) NOT NULL,
538  `config_description` varchar(200) DEFAULT NULL,
539  `config_value1` varchar(40) NOT NULL,
540  `config_value2` varchar(80) DEFAULT NULL,
541  `start_date` date NOT NULL,
542  `end_date` date DEFAULT NULL,
543  `last_update_dtm` datetime NOT NULL,
544  `update_by` int(11) NOT NULL,
545  PRIMARY KEY (`tax_config_id`)
546) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
547
548/*Data for the table `payroll_tax_config` */
549
550insert  into `payroll_tax_config`(`tax_config_id`,`config_code`,`config_description`,`config_value1`,`config_value2`,`start_date`,`end_date`,`last_update_dtm`,`update_by`) values 
551(1,'COLLECTOR','Pemotong Pajak','Agung Widyangga','123456','2019-09-04',NULL,'2019-09-04 11:07:10',1),
552(2,'METHOD','metode pemotong karyawan tetap','1','Gross Up','2019-09-05',NULL,'2019-09-05 09:52:01',1),
553(3,'COLLECTOR','Pengecekan potongan pajak','Wulan Lastia Permana','1','2019-09-05',NULL,'2019-09-05 09:53:03',1),
554(4,'PTKP','Tidak kawin','TK','54000000','2019-09-05',NULL,'2019-09-05 09:53:46',1);
555
556/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
557/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
558/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
559/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;