CREATE TABLE `msm_leave_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leave_type` varchar(50) DEFAULT NULL,
  `leave_type_name` varchar(250) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT 1,
  `status` tinyint(15) DEFAULT 1 COMMENT '1-Live,2-Delete',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `msm_leave_entitlement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leave_type` varchar(50) DEFAULT NULL,
   effective_date varchar(15) NOT NULL,
   expiry_date varchar(15) NOT NULL,
   entitled_days DECIMAL(11,2) DEFAULT 0,
  `create_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT 1,
  `status` tinyint(15) DEFAULT 1 COMMENT '1-Live,2-Delete',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Leave type: Annual Leave, Sick Leave, Casual Leave, Marriage Leave, Maternity Leave, Paternity Leave, Compassionate Leave, Bereavement Leave, Others!


CREATE TABLE `msm_leave_appliacation` (
   id bigint(11) NOT NULL AUTO_INCREMENT,
   employee_idno VARCHAR(25),
   leave_type varchar(50) DEFAULT NULL,
   leave_no VARCHAR(50),
   submit_date VARCHAR(20),
   start_date VARCHAR(20),
   end_date VARCHAR(20),
   start_time VARCHAR(20),
   end_time VARCHAR(20),
   reason VARCHAR(500),
   create_by VARCHAR(100),
   leave_status int DEFAULT 1,
   approved_id VARCHAR(20),
   approved_by VARCHAR(100),
   approved_date VARCHAR(20),   
   leave_days DECIMAL(11,2) DEFAULT 0,
   leave_balance DECIMAL(11,2),
   sl_attachment varchar(250),
   supervisor_id varchar(20),
  `create_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT 1,
  `status` tinyint(15) DEFAULT 1 COMMENT '1-Live,2-Delete',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `msm_leave_appliacation` ADD `received_by` VARCHAR(100) NULL DEFAULT NULL AFTER `approved_date`, ADD `received_date` VARCHAR(20) NULL DEFAULT NULL AFTER `received_by`; 

CREATE TABLE `msm_leave_tail` (
   id bigint(11) NOT NULL AUTO_INCREMENT,
   employee_idno VARCHAR(25),
   leave_type varchar(50) DEFAULT NULL,
   leave_no VARCHAR(50),
   leavedate VARCHAR(20),
   leavedays DECIMAL(11,2),
   minutes DECIMAL(11,2),
   stime VARCHAR(20),
   etime VARCHAR(20),
   create_by VARCHAR(100),
   leave_status int DEFAULT 1,
  `create_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT 1,
  `status` tinyint(15) DEFAULT 1 COMMENT '1-Live,2-Delete',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



USE [HRMS]
GO

/****** Object:  Table [dbo].[i_master]    Script Date: 26-Oct-23 8:48:04 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO



CREATE TABLE i_letter_body(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[body_content] text NULL,
	[body_type] [varchar](100) NULL,
	[expire_date] [nvarchar](20) NULL,
	[create_by] [nvarchar](100) NULL,
	[time_create] [datetime] NULL,
	[plant_code] [nvarchar](50) NULL
);


CREATE TABLE i_increment_promotion(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	status nvarchar(50),
	emp_code nvarchar(50),
	emp_name nvarchar(250),
	months nvarchar(50),
	effectivedate nvarchar(50),
	AdjustType nvarchar(100),
	AdjustNo nvarchar(50),
	pre_designation nvarchar(100),
	new_designation nvarchar(100),
	pre_salary FLOAT,
	new_salary FLOAT,
	[body_content] text NULL,
	[create_date] [nvarchar](20) NULL,
	[create_by] [nvarchar](100) NULL,
	[approved_by] [nvarchar](100) NULL,
	[approved_date] [nvarchar](20) NULL,
	[received_by] [nvarchar](100) NULL,
	[received_date] [nvarchar](20) NULL,
	[time_create] [datetime] NULL,
	[time_update] [datetime] NULL,
	[plant_code] [nvarchar](50) NULL
);


CREATE TABLE [cutting_knife_master](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[department_id] [int] NULL,
	[requisition_no] [nvarchar](50) NULL,
	[requisition_date] [date] NULL,
	[requisition_time] [nvarchar](50) NULL,
	[requisition_type] [nvarchar](50) NULL,
	employee_id nvarchar(20),
	tools_type nvarchar(50),
	[requested_by] [nvarchar](150) NULL,
	[requested_date] [nvarchar](30) NULL,
	[approved_by] [nvarchar](150) NULL,
	[approved_id] [int] NULL,
	[approved_date] [nvarchar](30) NULL,
	[received_by] [nvarchar](100) NULL,
	[received_id] [int] NULL,
	[received_date] [nvarchar](30) NULL,
	finished_by nvarchar(100),
	[finished_date] [nvarchar](30) NULL,
	[status] [nvarchar](50) NULL,
	[create_date] [date] NULL,
	[user_id] [int] NULL,
	[user_name] [varchar](50) NULL,
	[create_datetime] [varchar](50) NULL,
	[update_datetime] [varchar](50) NULL,
);

CREATE TABLE [cutting_knife_detail](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[requisition_no] [nvarchar](20) NULL,
	[pmo] [nvarchar](20) NULL,
	[file_no] [nvarchar](50) NULL,
	[style_no] [nvarchar](250) NULL,
	[line_no] [nvarchar](100) NULL,
	[start_time] [nvarchar](20) NULL,
	[quantity] [int] NULL,
	[knife_no] [nvarchar](100) NULL,
	[description] [nvarchar](500) NULL,
	[status] [nvarchar](50) NULL,
	[create_date] [date] NULL,
	[user_id] [int] NULL,
	[user_name] [varchar](50) NULL,
	[create_datetime] [varchar](50) NULL,
	[update_datetime] [varchar](50) NULL,
);

CREATE TABLE manpowertransfer(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[from_line] [nvarchar](20) NULL,
	[pmo] [nvarchar](20) NULL,
	[file_no] [nvarchar](50) NULL,
	[style_no] [nvarchar](250) NULL,
	[line_no] [nvarchar](100) NULL,
	[start_time] [nvarchar](20) NULL,
	[quantity] [int] NULL,
	[required_qty] [int] NULL,
	[knife_no] [nvarchar](100) NULL,
	[description] [nvarchar](500) NULL,
	[status] [nvarchar](50) NULL,
	[create_date] [date] NULL,
	[user_id] [int] NULL,
	[user_name] [varchar](50) NULL,
	[create_datetime] [varchar](50) NULL,
	[update_datetime] [varchar](50) NULL,
);

/////////////////////////////

CREATE TABLE reward_position_percent(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	years nvarchar(20) NULL,
	position nvarchar(100) NULL,
	position_name nvarchar(150) NULL,
	abudget_per nvarchar(20) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);

DROP TABLE reward_project_budget;
CREATE TABLE reward_project_budget(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	years nvarchar(20) NULL,
	project_name nvarchar(350) NULL,
	budget nvarchar(20) NULL,
	prank nvarchar(20) NULL,
	project_leader nvarchar(100) NULL,
	remarks nvarchar(500) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);
DROP TABLE reward_department_amount;
CREATE TABLE reward_department_amount(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	years nvarchar(20) NULL,
	dept_code nvarchar(20) NULL,
	first_budget DECIMAL(11,2),
	second_budget DECIMAL(11,2),
	third_budget DECIMAL(11,2),
	four_budget DECIMAL(11,2),
	five_budget DECIMAL(11,2),
	six_budget DECIMAL(11,2),
	seven_budget DECIMAL(11,2),
	eight_budget DECIMAL(11,2),
	nine_budget DECIMAL(11,2),
	ten_budget DECIMAL(11,2),
	bbudget_amount nvarchar(20) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);


CREATE TABLE reward_previous_history(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	emp_code nvarchar(20) NULL,
	years nvarchar(20) NULL,
	reward_amount nvarchar(20) NULL,
	reward_per nvarchar(20) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);

DROP TABLE reward_master_table;
CREATE TABLE reward_master_table(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	this_status int NOT NULL DEFAULT '1',
	emp_code nvarchar(20) NULL,
	years nvarchar(20) NULL,
	designation nvarchar(150) NULL,
	emp_category nvarchar(150) NULL,
	dept_code nvarchar(20) NULL,
	dept_name nvarchar(150) NULL,
	div_code nvarchar(20) NULL,
	div_name nvarchar(150) NULL,
	april_basic nvarchar(20) NULL,
	april_gross nvarchar(20) NULL,
	present_basic nvarchar(20) NULL,
	present_gross nvarchar(20) NULL,
	budget nvarchar(20) NULL,
	budget_a nvarchar(20) NULL,
	budget_a_stry_div nvarchar(20) NULL,
	hod_budget_a_amount nvarchar(20) NULL,
	hod_budget_a_per nvarchar(20) NULL,
	hod_budget_b_amount nvarchar(20) NULL,
	hod_budget_b_per nvarchar(20) NULL,
	dih_budget_a_amount nvarchar(20) NULL,
	dih_budget_a_per nvarchar(20) NULL,
	dih_budget_b_amount nvarchar(20) NULL,
	dih_budget_b_per nvarchar(20) NULL,
	coo_budget_a_amount nvarchar(20) NULL,
	coo_budget_a_per nvarchar(20) NULL,
	coo_budget_b_amount nvarchar(20) NULL,
	coo_budget_b_per nvarchar(20) NULL,
	coo_budget_c_amount nvarchar(20) NULL,
	total_abc_amount nvarchar(20) NULL,
	total_abc_per nvarchar(20) NULL,
	warning_no int  NOT NULL DEFAULT '0',
	warning_deduction_amount nvarchar(20) NULL,
	net_pay_amount nvarchar(20) NULL,
	net_pay_amount_per nvarchar(20) NULL,
	net_pay_amount_hidden nvarchar(20) NULL,
	coo_reserve_amount nvarchar(20) NULL,
	remarks nvarchar(200) NULL,
	div_emp_code nvarchar(20) NULL,
	hod_emp_code nvarchar(20) NULL,
	hod_by [nvarchar](100) NULL,
	hod_by_date [nvarchar](20) NULL,
	hod_submit_date [nvarchar](20) NULL,
	div_by [nvarchar](100) NULL,
	div_by_date [nvarchar](20) NULL,
	div_submit_date [nvarchar](20) NULL,
	coo_by [nvarchar](100) NULL,
	coo_by_date [nvarchar](20) NULL,
	coo_final_date [nvarchar](20) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);


DROP TABLE tbl_vendor_information;
CREATE TABLE tbl_vendor_information(
	id [bigint] IDENTITY(1,1) NOT NULL,
	date nvarchar(50) NULL,
	vendor_name nvarchar(200) NULL,
	vendor_address nvarchar(350) NULL,
	mobile_no nvarchar(20) NULL,
	email_address nvarchar(100) NULL,
	dept_code nvarchar(100) NULL,
	address nvarchar(500) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);

DROP TABLE tbl_guest_information;
CREATE TABLE tbl_guest_information(
	id [bigint] IDENTITY(1,1) NOT NULL,
	date nvarchar(50) NULL,
	guest_name nvarchar(200) NULL,
	guest_address nvarchar(350) NULL,
	mobile_no nvarchar(20) NULL,
	dept_code nvarchar(100) NULL,
	types [nvarchar](100) NULL,
	address nvarchar(500) NULL,
	vendor_id int NULL,
	driver_lic_no nvarchar(30) NULL,
	license_copy nvarchar(500) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);

DROP TABLE tbl_visitor_card;
CREATE TABLE tbl_visitor_card(
	id [bigint] IDENTITY(1,1) NOT NULL,
	card_type nvarchar(200) NULL,
	card_no nvarchar(350) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);


DROP TABLE tbl_visitor;
CREATE TABLE tbl_visitor(
	id [bigint] IDENTITY(1,1) NOT NULL,
	date nvarchar(50) NULL,
	guest_id bigint NULL,
	vendor_id int,
	to_whom nvarchar(200) NULL,
	dept_code nvarchar(100) NULL,
	card_no nvarchar(50) NULL,
	purpose nvarchar(350) NULL,
	in_time [nvarchar](20) NULL,
	out_time nvarchar(20) NULL,
 	in_security_by [nvarchar](100) NULL,
	out_security_by [nvarchar](100) NULL,
	remarks [nvarchar](250) NULL,
	this_status int NOT NULL DEFAULT 1,
	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);


DROP TABLE tbl_vehicle;
CREATE TABLE tbl_vehicle(
	id [bigint] IDENTITY(1,1) NOT NULL,
	date nvarchar(50) NULL,
	guest_id int,
	card_no nvarchar(20),
	hguest_id int,
	hcard_no nvarchar(20),
	vendor_id int,
	vehicle_no nvarchar(50) NULL,
	container_no nvarchar(50) NULL,
	
	purpose nvarchar(350) NULL,
	in_time [nvarchar](20) NULL,
	out_time nvarchar(20) NULL,
 	in_security_by [nvarchar](100) NULL,
	out_security_by [nvarchar](100) NULL,
	remarks [nvarchar](250) NULL,
	this_status int NOT NULL DEFAULT 1,
	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);
 
TRUNCATE TABLE tbl_tm_evaluation_master;
TRUNCATE TABLE tbl_tm_evaluation_detail;
TRUNCATE TABLE tbl_ws_evaluation_master;
TRUNCATE TABLE tbl_ws_evaluation_detail;






DROP TABLE tbl_tm_evaluation_master;
CREATE TABLE tbl_tm_evaluation_master(
	rowids [bigint] IDENTITY(1,1) NOT NULL,
	evaluate_by_id nvarchar(10) NULL,
	evaluate_by nvarchar(100) NULL,
	years nvarchar(10) NULL,
	serial_no nvarchar(20) NULL,
	total_person nvarchar(10) NULL,
	this_status int NOT NULL DEFAULT 1,
	received_by [nvarchar](100) NULL,
	received_date nvarchar(20) NULL,
 	create_by [nvarchar](100) NULL,
	create_date nvarchar(20) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](10) NULL
);


DROP TABLE tbl_tm_evaluation_detail;
CREATE TABLE tbl_tm_evaluation_detail(
	rowids [bigint] IDENTITY(1,1) NOT NULL,
	serial_no nvarchar(20) NULL,
	evaluate_by_id nvarchar(10) NULL,
	evaluate_by nvarchar(100) NULL,
	years nvarchar(10) NULL,
	increment_month nvarchar(30) NULL,
	emp_code nvarchar(10) NULL,
	designation nvarchar(100) NULL,
	section_name nvarchar(100) NULL,
	floor_name [nvarchar](20) NULL,
	line_no [nvarchar](20) NULL,
	standard_expectation int NULL,
	quality_product int NULL,
	continues_improvement int NULL,
	new_process int NULL,
	mutual_trust int NULL,
	attendance int NULL,
	total_mark nvarchar(10) NULL,
	average_mark nvarchar(10) NULL,
	this_status int NOT NULL DEFAULT 1,
 	create_by [nvarchar](100) NULL,
	create_date nvarchar(50) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);


DROP TABLE tbl_ws_evaluation_master;
CREATE TABLE tbl_ws_evaluation_master(
	rowids [bigint] IDENTITY(1,1) NOT NULL,
	evaluate_by_id nvarchar(10) NULL,
	evaluate_by nvarchar(100) NULL,
	years nvarchar(10) NULL,
	serial_no nvarchar(20) NULL,
	total_person nvarchar(10) NULL,
	this_status int NOT NULL DEFAULT 1,
	received_by [nvarchar](100) NULL,
	received_date nvarchar(20) NULL,
 	create_by [nvarchar](100) NULL,
	create_date nvarchar(20) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](10) NULL
);


DROP TABLE tbl_ws_evaluation_detail;
CREATE TABLE tbl_ws_evaluation_detail(
	rowids [bigint] IDENTITY(1,1) NOT NULL,
	serial_no nvarchar(20) NULL,
	evaluate_by_id nvarchar(10) NULL,
	evaluate_by nvarchar(100) NULL,
	years nvarchar(10) NULL,
	increment_month nvarchar(30) NULL,
	emp_code nvarchar(10) NULL,
	designation nvarchar(100) NULL,
	section_name nvarchar(100) NULL,
	floor_name [nvarchar](20) NULL,
	line_no [nvarchar](20) NULL,
	customer_satisfation int NULL,
	quality_product int NULL,
	continues_improvement int NULL,
	learning_ability int NULL,
	adaptibilty int NULL,
	collaborative_working int NULL,
	managing_team int NULL,
	attendance int NULL,
	total_mark nvarchar(10) NULL,
	average_mark nvarchar(10) NULL,
	this_status int NOT NULL DEFAULT 1,
 	create_by [nvarchar](100) NULL,
	create_date nvarchar(50) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);
 
 [v_hrInjob]
 [Supervisor_ID]
 exec GetCheckInOutData '2024-02-20','2024-02-20','%';
 
 
 
 USE [HRMS]
GO

SELECT [plant_code]
      ,[emp_code]
      ,[employee_id]
      ,[card_no]
      ,[emp_name]
      ,[emp_name_q]
      ,[dept_code]
      ,[group_code]
      ,[class_code]
      ,[sex]
      ,[duty]
      ,[in_date]
      ,[in_datenew]
      ,[indateflag]
      ,[tryout_term]
      ,[tryout_limit]
      ,[introducer]
      ,[cautioner]
      ,[hire]
      ,[leave_flag]
      ,[workstate]
      ,[out_date]
      ,[manager]
      ,[Major]
      ,[direct]
      ,[worktype]
      ,[emp_type]
      ,[CATV]
      ,[birthday]
      ,[id_no]
      ,[id_date]
      ,[id_term]
      ,[marriage]
      ,[nation]
      ,[native_place]
      ,[edu]
      ,[blood]
      ,[home_address]
      ,[alias]
      ,[tel]
      ,[fax]
      ,[e_mail]
      ,[hand_tel]
      ,[liaison_person]
      ,[liaison_tel]
      ,[card_free]
      ,[Religion]
      ,[defshift]
      ,[allow_over]
      ,[salary_type]
      ,[salary_level]
      ,[bottom_salary]
      ,[loan_total]
      ,[bank_id]
      ,[bank_no]
      ,[allowancestate]
      ,[mach]
      ,[spec_allowance]
      ,[social_insurance]
      ,[ill_allowance]
      ,[house_allowance]
      ,[job_allowance]
      ,[sy_allowance]
      ,[building_contract]
      ,[contract_type]
      ,[contractdate]
      ,[contract_Sdate]
      ,[contract_Edate]
      ,[out_reason]
      ,[dutykind]
      ,[workterm]
      ,[dupt]
      ,[ducs]
      ,[now_address]
      ,[create_by]
      ,[out_type]
      ,[update_by]
      ,[time_create]
      ,[time_update]
      ,[emailaddress]
      ,[kaiqindept]
      ,[isillallowance]
      ,[bottom_sht]
      ,[inited]
      ,[emp_namEng]
      ,[PostalCode]
      ,[Fathather]
      ,[Monther]
      ,[WorkExpe]
      ,[PassportNo]
      ,[ReChannel]
      ,[Passportdate1]
      ,[Passportdate2]
      ,[hezhun]
      ,[hzuid]
      ,[hzdate]
      ,[sunOT]
      ,[kaiqindeptQ]
      ,[dept_head]
      ,[roomno]
      ,[Position]
      ,[children]
      ,[Position_name]
      ,[duty_name]
      ,[worktype_name]
      ,[salary_type_name]
      ,[edu_name]
      ,[native_place_name]
      ,[Religion_name]
      ,[Major_name]
      ,[CATV_name]
      ,[out_reason_name]
      ,[hire_name]
      ,[out_type_name]
      ,[contract_type_name]
      ,[group_name]
      ,[class_name]
      ,[kaoqindeptname]
      ,[dept_name]
      ,[divcode]
      ,[divname]
      ,[DFloor]
      ,[Line]
      ,[PPDate]
      ,[IncMonth]
      ,[OT_Status]
      ,[Work_Group]
      ,[PFStatus]
      ,[PlaceOfBirth]
      ,[nid_no]
      ,[Locker_No]
      ,[Institute]
      ,[Job_Nature]
      ,[Supervisor_ID]
      ,[Supervisor_Name]
      ,[Ecategory]
      ,[ECname1]
      ,[ECname2]
      ,[ECnameNo1]
      ,[ECnameNo2]
      ,[ECnameRel1]
      ,[ECnameRel2]
      ,[Health_Condition]
      ,[EHeight]
      ,[EWeight]
      ,[LengthNR]
      ,[adjustflag]
      ,[loginPassWord]
      ,[FAddress_VS]
      ,[FAddress_PO]
      ,[FAddress_PS]
      ,[FAddress_DT]
      ,[FAddress_ZC]
      ,[FAddress_DS]
      ,[RAddress_VS]
      ,[RAddress_PO]
      ,[RAddress_PS]
      ,[RAddress_DT]
      ,[RAddress_ZC]
      ,[RAddress_DS]
      ,[sub_section]
  FROM [dbo].[v_hrInjob]
GO

CREATE TABLE tbl_pro_parameter(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[parametern] [varchar](500) NULL,
	[details] [varchar](1000) NULL,
	[create_by] [varchar](50) NULL,
	[time_create] [datetime] NULL,
	[plant_code] [varchar](50) NULL,
);


CREATE TABLE [dbo].[tbl_prom_master](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[serial_no] [nvarchar](20) NULL,
	[years] [nvarchar](20) NULL,
	[months] [nvarchar](20) NULL,
	[emp_code] [nvarchar](10) NULL,
	[emp_name] [nvarchar](150) NULL,
	[current_position] [nvarchar](100) NULL,
	[dept_code] [nvarchar](20) NULL,
	[proposed_position] [nvarchar](100) NULL,
	[explain] [nvarchar](1000) NULL,
	[proposed_increment_rate] [nvarchar](50) NULL,
	[intv_date] [nvarchar](15) NULL,
	[istatus] [nvarchar](20) NULL,
	[create_id] [nvarchar](50) NULL,
	[create_by] [nvarchar](50) NULL,
	[time_create] [datetime] NULL,
	[plant_code] [nvarchar](10) NULL
	);


DROP TABLE tbl_prom_detail; 
CREATE TABLE [dbo].[tbl_prom_detail](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[master_id] [bigint] NULL,
	[serial_no] [nvarchar](20) NULL,
	[par_id] [int] NULL,
	[parametern] [varchar](250) NULL,
	[mark] [int] NULL,
	[create_by] [varchar](50) NULL,
	[time_create] [datetime] NULL,
	[plant_code] [varchar](50) NULL
	);
	
DROP TABLE tbl_prom_emp; 	
CREATE TABLE tbl_prom_emp(
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	emp_code nvarchar(20) NULL,
	years nvarchar(20) NULL,
	months nvarchar(20) NULL,
	proposed_position nvarchar(100) NULL,
 	create_by [nvarchar](100) NULL,
	time_create [datetime] NULL,
	time_update [datetime] NULL,
	plant_code [nvarchar](50) NULL
);