GO INSERT INTO [dbo].[SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES( 'MONTH', 'MONTH_ID', 'MONTH_NAME', 'LIST', 1, 1, 1, 1, 0, 0, 7, 4, '') GO ----------------------------------------02/06/2015---------------------------------------------------------- GO ALTER TABLE ASSET ADD CANCEL_DATE smalldatetime Default NULL GO ----------------------------------------03/24/2015---------------------------------------------------------- GO INSERT INTO dbo.SYS_MENU ( MENU_ID , PROJECT_ID , MODULE_ID , MENU_TYPE , MENU_GROUP , MENU_ICON , MENU_WIDTH , MENU_TAG , MENU_ORDER , ACTIVE ) VALUES ( N'TP_SALARY_COST_LXE' , N'TP' , N'TP_ORDER' , N'FUNC' , N'COSTGROUP1' , N'tp_cost.png' , 0 , N'TP_SALARY_COST_LXE' , 1 , 1 ) GO INSERT INTO dbo.SYS_MENU ( MENU_ID , PROJECT_ID , MODULE_ID , MENU_TYPE , MENU_GROUP , MENU_ICON , MENU_WIDTH , MENU_TAG , MENU_ORDER , ACTIVE ) VALUES ( N'TP_SALARY_COST_QLY' , N'TP' , N'TP_ORDER' , N'FUNC' , N'COSTGROUP1' , N'tp_cost.png' , 0 , N'TP_SALARY_COST_QLY' , 1 , 1 ) GO INSERT INTO dbo.SYS_MENU ( MENU_ID , PROJECT_ID , MODULE_ID , MENU_TYPE , MENU_GROUP , MENU_ICON , MENU_WIDTH , MENU_TAG , MENU_ORDER , ACTIVE ) VALUES ( N'TP_SALARY_FIX' , N'TP' , N'TP_ORDER' , N'FUNC' , N'COSTGROUP1' , N'tp_cost.png' , 0 , N'TP_SALARY_FIX' , 2 , 1 ) GO update SYS_RESOURCE set RES_VALUE = N'Bảng giá tiền lương cố định' where RES_ID = 'MSG_MNU_TP_SALARY_FIX' GO update SYS_RESOURCE set RES_VALUE = N'Bảng giá tiền lương lái xe' where RES_ID = 'MSG_MNU_TP_SALARY_COST_LXE' GO update SYS_RESOURCE set RES_VALUE = N'Bảng giá tiền lương quản lý' where RES_ID = 'MSG_MNU_TP_SALARY_COST_QLY' GO -- GO ALTER TABLE [dbo].[TP_SALARY_COST] ADD ROUTE_ID [NVARCHAR](20) GO ALTER TABLE [dbo].[TP_SALARY_COST] ADD VOLUME_TOTAL_FROM [MONEY] GO ALTER TABLE [dbo].[TP_SALARY_COST] ADD VOLUME_TOTAL_TO [MONEY] GO ALTER TABLE [dbo].[TP_SALARY_COST] ADD PRICE_TYPE [NVARCHAR](10) GO CREATE TABLE [dbo].[TP_SALARY_FIX]( [PR_KEY] [money] NOT NULL, [PRICE] [money] NOT NULL, [VALID_DATE] [smalldatetime] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_TP_SALARY_FIX] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TP_SALARY_FIX] ADD DEFAULT ('') FOR [ORGANIZATION_ID] GO -- 30/03 -- GO CREATE TABLE [HR_EMPLOYEE_ASSETS]( [PR_KEY] [uniqueidentifier] NOT NULL, [TRAN_ID][nvarchar](20) NOT NULL, [TRAN_NO][nvarchar](20) NOT NULL, [TRAN_DATE][smalldatetime] NOT NULL, [DELIVERY_NO] [nvarchar](50) NOT NULL, [DATE_DELIVERY][smalldatetime] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [IS_DELIVERED] [smallint] NOT NULL, [PLATE_OF_DELIVERY] [nvarchar](200) NOT NULL, [NOTES_DELIVERY] [nvarchar](500) NOT NULL, [RECEIVES_NO] [nvarchar](50) NOT NULL, [DATE_RECEIVE][smalldatetime] NOT NULL, [EMPLOYEE_ID_RECEIVE] [nvarchar](20) NOT NULL, [IS_RECEIVED] [smallint] NOT NULL, [PLATE_OF_RECEIVE] [nvarchar](200) NOT NULL, [NOTES_RECEIVE] [nvarchar](500) NOT NULL PRIMARY KEY ([EMPLOYEE_ID])) GO CREATE TABLE [HR_EMPLOYEE_ASSETS_DETAIL]( [PR_KEY] [uniqueidentifier] NOT NULL, [FR_KEY] [uniqueidentifier] NOT NULL, [ASSETS_ID] [nvarchar](20) NOT NULL, [NUM_RECEIVE] [money] NOT NULL, [NUM_DELIVERY] [money] NOT NULL, [NUM_OWE] [money] NOT NULL, [STATUS] [nvarchar](200) NOT NULL, [NOTES] [nvarchar](500) NOT NULL PRIMARY KEY ( [FR_KEY],[ASSETS_ID] )) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_EMPLOYEE_ASSETS','HRM','HRM_INFO','FUNC','HRMHRINFOGROUP3','ht_amenities.png',80,'HR_EMPLOYEE_ASSETS',3,1) GO update SYS_GRIDINFO set visible = '1', visibleindex = '0', filtered ='1' , enabled =0 where (form_name = 'FrmHr_Employee_Assets_EditList' and GRID_NAME = 'TREELIST') GO INSERT INTO SYS_TRAN (TRAN_ID,TRAN_NAME,TRAN_CLASS,TRAN_SUB_CLASS,LIST_ORDER,OUTPUT_FORM, POSTED,MODULE_ID,PROJECT_ID,USER_ID,ACTIVE,SHOW_IN_MENU) VALUES('HR_EMPLOYEE_ASSETS',N'Giao nhận tài sản nhân sự','HR_INFO','HR_EMPLOYEE_ASSETS',1,'FRMHR_EMPLOYEE_ASSETS',1,'HRM_INFO','HRM','ADMIN',1,0) GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('HR_ASSETS' ,'ASSETS_ID' ,'ASSETS_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO ALTER TABLE HR_EMPLOYEE_LEAVE_N ADD LOT nvarchar(50) DEFAULT '' NOT NULL GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('IS_NOT_SHOW_REASON_LEAVE_WHEN_CHECKIN', 1, N'Không hiển thị chế độ nghỉ khi có chấm công trong HR_TIME_WORK', 'INT', 'HRM') GO -- 31/03 -- GO ALTER TABLE HR_EMPLOYEE_ADDRESS ADD ADDRESS_ENG nvarchar(500) DEFAULT '' NOT NULL GO -- 01/04 -- GO ALTER TABLE [HR_EMPLOYEE_ASSETS] ADD DEPOSITS money DEFAULT 0 NOT NULL GO ALTER TABLE [HR_EMPLOYEE_ASSETS] ADD DEPOSITS_DATE smalldatetime DEFAULT '2015-01-01' NOT NULL GO ALTER TABLE [HR_EMPLOYEE_ASSETS] ADD REPAY_TYPE nvarchar(50) DEFAULT '' NOT NULL GO ALTER TABLE [HR_EMPLOYEE_ASSETS] ADD REPAY_DATE smalldatetime DEFAULT '2015-01-01' NOT NULL GO ALTER TABLE [HR_EMPLOYEE_ASSETS] ADD IS_REPAY smallint DEFAULT 0 NOT NULL GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('DAY_AUTO_LOCK_EVALUATION', '-30', N'Số ngày addday()vào ngày cuối tháng hiện tại', 'INT', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HOUR_AUTO_LOCK_EVALUATION', '17', N'Số ngày addday()vào ngày cuối tháng hiện tại', 'INT', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('MUNITE_AUTO_LOCK_EVALUATION', '1', N'Số ngày addday()vào ngày cuối tháng hiện tại', 'INT', 'HRM') GO -- 02/04 -- GO ALTER TABLE DM_EMPLOYEE_STATUS ADD EMPLOYEE_STATUS_NAME_EN nvarchar(200) DEFAULT '' NOT NULL GO ALTER TABLE DM_EMPLOYEE_STATUS ADD EMPLOYEE_STATUS_NAME_OTHER nvarchar(200) DEFAULT '' NOT NULL GO ALTER TABLE DM_HR_GROUP_POSITION ADD GROUP_POSITION_NAME_EN nvarchar(200) DEFAULT '' NOT NULL GO ALTER TABLE DM_HR_GROUP_POSITION ADD GROUP_POSITION_NAME_OTHER nvarchar(200) DEFAULT '' NOT NULL GO ALTER TABLE HR_EMPLOYEE_INFO ADD IDENTITY_ISSUE_PLACE_EN nvarchar(200) DEFAULT '' NOT NULL GO ALTER TABLE HR_EMPLOYEE_INFO ADD IDENTITY_ISSUE_PLACE_OTHER nvarchar(200) DEFAULT '' NOT NULL GO -- 02/04 -- GO DROP VIEW [dbo].[HR_EMPLOYEE_INFO_VIEW] GO CREATE VIEW [dbo].[HR_EMPLOYEE_INFO_VIEW] AS SELECT TOP (100) PERCENT dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.HR_EMPLOYEE_INFO.SEX, dbo.HR_EMPLOYEE_INFO.CANDIDATES_ID, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME_OTHER, dbo.HR_EMPLOYEE_INFO.DOB, dbo.HR_EMPLOYEE_INFO.BIRTH_PLACE, dbo.HR_EMPLOYEE_INFO.ORIGIN_PLACE, dbo.HR_EMPLOYEE_INFO.ORIGIN_CITY, dbo.HR_EMPLOYEE_INFO.IDENTITY_NO, dbo.HR_EMPLOYEE_INFO.IDENTITY_ISSUE_DATE, dbo.HR_EMPLOYEE_INFO.IDENTITY_ISSUE_PLACE, dbo.HR_EMPLOYEE_INFO.PASSPORT_NO, dbo.HR_EMPLOYEE_INFO.PASSPORT_ISSUE_DATE, dbo.HR_EMPLOYEE_INFO.PASSPORT_ISSUE_PLACE, dbo.HR_EMPLOYEE_INFO.PASSPORT_EXPIRE_DATE, dbo.HR_EMPLOYEE_INFO.SOCIAL_BOOK_NO, dbo.HR_EMPLOYEE_INFO.SOCIAL_BOOK_ISSUE_DATE, dbo.HR_EMPLOYEE_INFO.SOCIAL_BOOK_ISSUE_PLACE, dbo.HR_EMPLOYEE_INFO.BANK_ACCOUNT_NO, dbo.HR_EMPLOYEE_INFO.BANK_NAME, dbo.HR_EMPLOYEE_INFO.BANK_BRANCH, dbo.HR_EMPLOYEE_INFO.MARITAL_STATUS_ID, dbo.HR_EMPLOYEE_INFO.FAMILY_ORIGIN_ID, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ORIGIN_ID, dbo.HR_EMPLOYEE_INFO.ETHNICS_ID, dbo.HR_EMPLOYEE_INFO.RELIGION_ID, dbo.HR_EMPLOYEE_INFO.NATIONALITY_ID, dbo.HR_EMPLOYEE_INFO.HOME_PHONE, dbo.HR_EMPLOYEE_INFO.WORK_PHONE, dbo.HR_EMPLOYEE_INFO.MOBILE_PHONE, dbo.HR_EMPLOYEE_INFO.PERSONAL_EMAIL, dbo.HR_EMPLOYEE_INFO.WORK_EMAIL, dbo.HR_EMPLOYEE_INFO.YAHOO_ID, dbo.HR_EMPLOYEE_INFO.SKYPE_ID, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_NAME, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID_L1, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_NAME_L1, dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID, dbo.HR_EMPLOYEE_POSITION.START_DATE, dbo.HR_EMPLOYEE_POSITION.POSITION_ID, dbo.HR_EMPLOYEE_POSITION.CAREER_ID, dbo.HR_EMPLOYEE_POSITION.ALLOW_SALARY_RAISE, dbo.HR_EMPLOYEE_POSITION.COMMENTS AS POSITION_COMMENTS, dbo.HR_EMPLOYEE_POSITION.DESC_OF_WORK, dbo.HR_EMPLOYEE_POSITION.END_DATE, dbo.HR_EMPLOYEE_POSITION.IS_CURRENT AS POSITION_IS_CURRENT, dbo.HR_EMPLOYEE_POSITION.LINE_MANAGER_ID, dbo.HR_EMPLOYEE_POSITION.POS_DECISION_DATE, dbo.HR_EMPLOYEE_POSITION.POS_DECISION_NO, dbo.HR_EMPLOYEE_POSITION.REASON_ID, dbo.HR_EMPLOYEE_POSITION.SALARY_LEVEL_ID, dbo.HR_EMPLOYEE_POSITION.LEVEL_SALARY, dbo.HR_EMPLOYEE_POSITION.RATE_SALARY_POINT1, dbo.HR_EMPLOYEE_POSITION.SALARY_POINT1, dbo.HR_EMPLOYEE_POSITION.SALARY_POINT2, dbo.HR_EMPLOYEE_POSITION.SALARY_AMOUNT1, dbo.HR_EMPLOYEE_POSITION.SALARY_AMOUNT2, dbo.HR_EMPLOYEE_POSITION.SALARY_RANK_ID, dbo.HR_EMPLOYEE_POSITION.SCALE_OF_CIVIL_ID, dbo.HR_EMPLOYEE_POSITION.WORK_PLACE_ID, REVERSE(SUBSTRING(REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0, CHARINDEX(' ', REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0))) AS FIRST_NAME, REVERSE(SUBSTRING(REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), CHARINDEX(' ', REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0) + 1, LEN(REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME)) - CHARINDEX(' ', REVERSE(dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME), 0))) AS LAST_NAME, dbo.HR_ORGANIZATION_VIEW.HR_LIST_PARENT_ORGANIZATION_ID AS HR_PARENT_ORGANIZATION_ID, dbo.HR_ORGANIZATION_VIEW.HR_LIST_CHILD_ORGANIZATION_ID, '' AS CONTACT_TYPE_ID, '' AS STATUS_ID, dbo.HR_EMPLOYEE_INFO.DATE_COMPANY, DATEDIFF(YEAR, dbo.HR_EMPLOYEE_INFO.DOB, GETDATE()) AS YEARS, dbo.HR_EMPLOYEE_INFO.PERSONAL_TAXCODE, dbo.HR_EMPLOYEE_INFO.PERSONAL_TAXCODE_DATE, dbo.HR_EMPLOYEE_INFO.PERSONAL_TAXCODE_PLACE, dbo.DM_POSITION.POSITION_NAME, dbo.HR_EMPLOYEE_INFO.ACCEPT_STATUS, dbo.HR_EMPLOYEE_INFO.COMMENTS_ACCEPT, dbo.HR_ORGANIZATION_VIEW.ORGANIZATION_ID, dbo.HR_EMPLOYEE_OTHER.WORK_EXPERIENCE, dbo.HR_EMPLOYEE_OTHER.SKILL_SUMMARY, dbo.HR_EMPLOYEE_OTHER.CREDITS_FAULTS, dbo.HR_EMPLOYEE_OTHER.INTERESTS, dbo.HR_EMPLOYEE_OTHER.STRENGTHS, dbo.HR_EMPLOYEE_OTHER.WEAKNESSES, dbo.HR_EMPLOYEE_OTHER.COMMENTS AS OTHER_COMMENTS, dbo.HR_EMPLOYEE_OTHER.ENROLL_NUMBER, dbo.HR_EMPLOYEE_OTHER.FOREIGN_RELATION, dbo.HR_EMPLOYEE_OTHER.LAW_VIOLATIONS, dbo.HR_EMPLOYEE_OTHER.FAMILY_OVERSEAS, dbo.HR_EMPLOYEE_POLITICS.IS_PARTY_MEMBER, dbo.HR_EMPLOYEE_POLITICS.PARTY_PROBATION_DATE, dbo.HR_EMPLOYEE_POLITICS.PARTY_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.PARTY_POSITION, dbo.HR_EMPLOYEE_POLITICS.PARTY_JOIN_PLACE, dbo.HR_EMPLOYEE_POLITICS.YOUTH_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.IS_YOUTH_MEMBER, dbo.HR_EMPLOYEE_POLITICS.YOUTH_JOIN_PLACE, dbo.HR_EMPLOYEE_POLITICS.YOUTH_POSITION, dbo.HR_EMPLOYEE_POLITICS.IS_MILITARY, dbo.HR_EMPLOYEE_POLITICS.MILITARY_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.MILITARY_POSITION, dbo.HR_EMPLOYEE_POLITICS.MILIBARY_LEVEL, dbo.HR_EMPLOYEE_POLITICS.MILITARY_ORGANIZATION, dbo.HR_EMPLOYEE_POLITICS.MILITARY_LEAVE_DATE, dbo.HR_EMPLOYEE_POLITICS.MILITARY_LEAVE_REASON, dbo.HR_EMPLOYEE_POLITICS.IS_WOUNDED, dbo.HR_EMPLOYEE_POLITICS.WONDED_LEVEL, dbo.HR_EMPLOYEE_POLITICS.WONDED_PERCENTAGE, dbo.HR_EMPLOYEE_POLITICS.REVOLUTION_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.CREDITS_FAULTS AS POLITICS_CREDITS_FAULTS, dbo.HR_EMPLOYEE_POLITICS.IS_CREDITED, dbo.HR_EMPLOYEE_POLITICS.STATE_MANAGEMENT_ID, dbo.HR_EMPLOYEE_POLITICS.IS_POLICY, dbo.HR_EMPLOYEE_POLITICS.POLITICAL_THEORY_ID, dbo.HR_EMPLOYEE_POLITICS.COMMENTS AS POLITICS_COMMENTS, dbo.HR_EMPLOYEE_HEALTH.HEALTH_LEVEL, dbo.HR_EMPLOYEE_HEALTH.BLOOD_TYPE, dbo.HR_EMPLOYEE_HEALTH.HEIGHT, dbo.HR_EMPLOYEE_HEALTH.WEIGHT, dbo.HR_EMPLOYEE_HEALTH.HEALTH_STATUS, dbo.HR_EMPLOYEE_HEALTH.COMMENTS AS HEALTH_COMMENTS, dbo.HR_EMPLOYEE_HEALTH.DISEASES, dbo.HR_EMPLOYEE_HEALTH.EVALUATION_DATE, dbo.DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME, dbo.HR_EMPLOYEE_INFO.WORK_DATE_START, dbo.HR_EMPLOYEE_INFO.DATE_GROUP_COMPANY, dbo.HR_EMPLOYEE_INFO.INFO_EXTRA_STRING_1, dbo.HR_EMPLOYEE_INFO.INFO_EXTRA_STRING_2, dbo.HR_EMPLOYEE_INFO.INFO_EXTRA_STRING_3, dbo.DM_POSITION.POSITION_INDEX_VIEW, dbo.HR_EMPLOYEE_INFO.REC_CHANNEL_ID,dbo.HR_EMPLOYEE_INFO.REC_CHANNEL_DETAIL,dbo.HR_EMPLOYEE_INFO.EMPLOYEE_INTRODUCED_ID, dbo.HR_EMPLOYEE_INFO.IDENTITY_ISSUE_PLACE_EN,dbo.HR_EMPLOYEE_INFO.IDENTITY_ISSUE_PLACE_OTHER FROM dbo.HR_ORGANIZATION_VIEW RIGHT OUTER JOIN dbo.HR_EMPLOYEE_CURRENT_POS INNER JOIN dbo.HR_EMPLOYEE_POSITION ON dbo.HR_EMPLOYEE_CURRENT_POS.EMPLOYEE_ID = dbo.HR_EMPLOYEE_POSITION.EMPLOYEE_ID AND dbo.HR_EMPLOYEE_CURRENT_POS.START_DATE = dbo.HR_EMPLOYEE_POSITION.START_DATE LEFT OUTER JOIN dbo.DM_EMPLOYEE_LEVEL ON dbo.HR_EMPLOYEE_POSITION.EMPLOYEE_LEVEL_ID = dbo.DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID LEFT OUTER JOIN dbo.DM_POSITION ON dbo.HR_EMPLOYEE_POSITION.POSITION_ID = dbo.DM_POSITION.POSITION_ID ON dbo.HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID = dbo.HR_EMPLOYEE_POSITION.PR_ORGANIZATION_ID RIGHT OUTER JOIN dbo.HR_EMPLOYEE_INFO LEFT OUTER JOIN dbo.HR_EMPLOYEE_HEALTH ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.HR_EMPLOYEE_HEALTH.EMPLOYEE_ID LEFT OUTER JOIN dbo.HR_EMPLOYEE_POLITICS ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.HR_EMPLOYEE_POLITICS.EMPLOYEE_ID LEFT OUTER JOIN dbo.HR_EMPLOYEE_OTHER ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.HR_EMPLOYEE_OTHER.EMPLOYEE_ID ON dbo.HR_EMPLOYEE_CURRENT_POS.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID WHERE (dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID NOT IN ('', '99999')) AND (dbo.HR_EMPLOYEE_POSITION.PR_ORGANIZATION_ID <> '') ORDER BY dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID GO DROP PROCEDURE [dbo].[Pro_Load_Full_Employee_Info] GO CREATE PROCEDURE [dbo].[Pro_Load_Full_Employee_Info] @ACCEP_STATUS nvarchar(50) AS BEGIN SET NOCOUNT ON; select distinct A.*,A.START_DATE_STATUS as EMPLOYEE_STATUS_DATE,DM_S.EMPLOYEE_STATUS_NAME,C.END_DATE as END_DATE_CONTRACT,C.COMMENTS as CONTRACT_COMMENTS,C.CONTRACT_DATE,C.CONTRACT_NO,C.CONTRACT_PERIOD,C.DUTY_RATE,C.IS_CURRENT as CONTRACT_IS_CURRENT,C.MANAGER_ID,DM_C.CONTRACT_TYPE_NAME, P.PR_ORGANIZATION_ID,P.POSITION_ID,P.CAREER_ID,P.ALLOW_SALARY_RAISE,P.COMMENTS as POSITION_COMMENTS, P.DESC_OF_WORK,P.END_DATE,P.IS_CURRENT as POSITION_IS_CURRENT,P.LINE_MANAGER_ID, P.POS_DECISION_DATE,P.POS_DECISION_NO,P.REASON_ID, P.SALARY_LEVEL_ID, P.SALARY_LEVEL_ID as GROUP_SALARY_NAME_BY_LAW, P.LEVEL_SALARY,P.RATE_SALARY_POINT1,P.SALARY_POINT1,P.SALARY_POINT2,P.SALARY_AMOUNT1,P.SALARY_AMOUNT2, P.SALARY_RANK_ID,P.SCALE_OF_CIVIL_ID,P.WORK_PLACE_ID, DM_ORG.PR_ORGANIZATION_NAME,DM_POSITION.POSITION_NAME,DM_POSITION.POSITION_INDEX_VIEW, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME, DM_ORG.PR_ORGANIZATION_NAME_EN,DM_ORG.PR_ORGANIZATION_NAME_OTHER,DM_POSITION.POSITION_NAME_EN,DM_POSITION.POSITION_NAME_OTHER, DM_S.EMPLOYEE_STATUS_NAME_EN,DM_S.EMPLOYEE_STATUS_NAME_OTHER, DM_POSITION.GROUP_POSITION_ID,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_EN,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_OTHER, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_EN, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_OTHER, DM_HR_CAREER.CAREER_NAME_EN, DM_HR_CAREER.CAREER_NAME_OTHER, HR_EMPLOYEE_HEALTH.BLOOD_TYPE, HR_EMPLOYEE_HEALTH.COMMENTS,HR_EMPLOYEE_HEALTH.DISEASES,HR_EMPLOYEE_HEALTH.EVALUATION_DATE, dbo.HR_ORGANIZATION_VIEW.HR_LIST_PARENT_ORGANIZATION_ID AS HR_PARENT_ORGANIZATION_ID, dbo.HR_ORGANIZATION_VIEW.HR_LIST_CHILD_ORGANIZATION_ID, dbo.HR_EMPLOYEE_HEALTH.HEALTH_LEVEL,HR_EMPLOYEE_HEALTH.HEALTH_STATUS,HR_EMPLOYEE_HEALTH.HEIGHT,HR_EMPLOYEE_HEALTH.WEIGHT, dbo.HR_EMPLOYEE_POLITICS.IS_PARTY_MEMBER, dbo.HR_EMPLOYEE_POLITICS.PARTY_PROBATION_DATE, dbo.HR_EMPLOYEE_POLITICS.PARTY_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.PARTY_POSITION, dbo.HR_EMPLOYEE_POLITICS.PARTY_JOIN_PLACE, dbo.HR_EMPLOYEE_POLITICS.YOUTH_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.IS_YOUTH_MEMBER, dbo.HR_EMPLOYEE_POLITICS.YOUTH_JOIN_PLACE, dbo.HR_EMPLOYEE_POLITICS.YOUTH_POSITION, dbo.HR_EMPLOYEE_POLITICS.IS_MILITARY, dbo.HR_EMPLOYEE_POLITICS.MILITARY_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.MILITARY_POSITION, dbo.HR_EMPLOYEE_POLITICS.MILIBARY_LEVEL, dbo.HR_EMPLOYEE_POLITICS.MILITARY_ORGANIZATION, dbo.HR_EMPLOYEE_POLITICS.MILITARY_LEAVE_DATE, dbo.HR_EMPLOYEE_POLITICS.MILITARY_LEAVE_REASON, dbo.HR_EMPLOYEE_POLITICS.IS_WOUNDED, dbo.HR_EMPLOYEE_POLITICS.WONDED_LEVEL, dbo.HR_EMPLOYEE_POLITICS.WONDED_PERCENTAGE, dbo.HR_EMPLOYEE_POLITICS.REVOLUTION_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.CREDITS_FAULTS AS POLITICS_CREDITS_FAULTS, dbo.HR_EMPLOYEE_POLITICS.IS_CREDITED, dbo.HR_EMPLOYEE_POLITICS.STATE_MANAGEMENT_ID, dbo.HR_EMPLOYEE_POLITICS.IS_POLICY, dbo.HR_EMPLOYEE_POLITICS.POLITICAL_THEORY_ID, dbo.HR_EMPLOYEE_POLITICS.COMMENTS AS POLITICS_COMMENTS, dbo.HR_EMPLOYEE_OTHER.WORK_EXPERIENCE, dbo.HR_EMPLOYEE_OTHER.SKILL_SUMMARY, dbo.HR_EMPLOYEE_OTHER.CREDITS_FAULTS, dbo.HR_EMPLOYEE_OTHER.INTERESTS, dbo.HR_EMPLOYEE_OTHER.STRENGTHS, dbo.HR_EMPLOYEE_OTHER.WEAKNESSES, dbo.HR_EMPLOYEE_OTHER.COMMENTS AS OTHER_COMMENTS, dbo.HR_EMPLOYEE_OTHER.ENROLL_NUMBER, dbo.HR_EMPLOYEE_OTHER.FOREIGN_RELATION, dbo.HR_EMPLOYEE_OTHER.LAW_VIOLATIONS, dbo.HR_EMPLOYEE_OTHER.FAMILY_OVERSEAS, HR_UNJOIN_INS.NUM_MONTH_UNJOIN, CONVERT(NVARCHAR(50), isnull(NUM_MONTH_UNJOIN,0) / 12 )+' '+N' năm '+' '+CONVERT(NVARCHAR(50), isnull(NUM_MONTH_UNJOIN,0) - ( isnull(NUM_MONTH_UNJOIN,0) / 12 ) * 12 )+' '+N' tháng ' as STRING_SENIORITY_UNJOIN_INS , DATEDIFF(YEAR, A.DOB, GETDATE()) AS YEARS, DATEDIFF(mm,DATE_COMPANY,GETDATE()) as NUM_MONTH,DATEDIFF(mm,DATE_COMPANY,GETDATE())/12 as N_YEAR, CONVERT(NVARCHAR(50),DATEDIFF(mm,DATE_COMPANY,GETDATE())/12)+' '+N' năm '+' '+CONVERT(NVARCHAR(50), DATEDIFF(mm,DATE_COMPANY,GETDATE())-(DATEDIFF(mm,DATE_COMPANY,GETDATE())/12)*12)+' '+N' tháng ' as STRING_SENIORITY, CONVERT(NVARCHAR(50),(DATEDIFF(mm,DATE_COMPANY,GETDATE())+1)/12)+' '+N' năm '+' '+CONVERT(NVARCHAR(50), ((DATEDIFF(mm,DATE_COMPANY,GETDATE())+1) -((DATEDIFF(mm,DATE_COMPANY,GETDATE())+1)/12)*12 ))+' '+N' tháng ' as STRING_SENIORITY_NN , CONVERT(NVARCHAR(50),(DATEDIFF(mm,SOCIAL_BOOK_ISSUE_DATE,GETDATE())+1 - isnull(NUM_MONTH_UNJOIN,0))/12)+' '+N' năm '+' '+CONVERT(NVARCHAR(50), ((DATEDIFF(mm,SOCIAL_BOOK_ISSUE_DATE,GETDATE())+ 1 - isnull(NUM_MONTH_UNJOIN,0)) -((DATEDIFF(mm,SOCIAL_BOOK_ISSUE_DATE,GETDATE())+1 - isnull(NUM_MONTH_UNJOIN,0))/12)*12 ))+' '+N' tháng ' as STRING_SENIORITY_SOCIAL , DATEDIFF(mm,DATE_COMPANY,GETDATE())-(DATEDIFF(mm,DATE_COMPANY,GETDATE())/12)*12 as N_MONTH, CAST((DATEDIFF(mm,DATE_COMPANY,GETDATE())/(CONVERT(DECIMAL(9,2),12))) AS NUMERIC(18,1)) as NUM_YEAR, '' as LEAVE_REASON_ID, '' as P_HAMLET,'' as P_COMMUNES_ID,'' as P_DISTRICT_ID,'' as P_PROVINCE_ID,'' as E_PERMANENT_ADDRESS, '' as E_PERMANENT_ADDRESS_EN, '' as T_HAMLET,'' as T_COMMUNES_ID,'' as T_DISTRICT_ID,'' as T_PROVINCE_ID,'' as E_TEMPORARY_ADDRESS, '' as E_TEMPORARY_ADDRESS_EN, '' as EDU_ID_MAX,'' as CERTIFICATION_NAME_MAX,'' as REWARDS_MAX,'' as FAULTS_MAX , DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, A.DOB), A.DOB) as DOB_CURRENT_YEAR from HR_EMPLOYEE_INFO A LEFT JOIN HR_EMPLOYEE_POSITION P ON A.EMPLOYEE_ID=P.EMPLOYEE_ID AND A.START_DATE_POSITION=P.START_DATE AND P.IS_CURRENT =1 LEFT JOIN DM_EMPLOYEE_STATUS DM_S ON A.EMPLOYEE_STATUS_ID = DM_S.EMPLOYEE_STATUS_ID LEFT JOIN HR_EMPLOYEE_CONTRACT C ON A.EMPLOYEE_ID=C.EMPLOYEE_ID AND A.START_DATE_CONTRACT=C.START_DATE LEFT JOIN DM_CONTRACT_TYPE DM_C ON C.CONTRACT_TYPE_ID=DM_C.CONTRACT_TYPE_ID LEFT JOIN DM_POSITION ON P.POSITION_ID=DM_POSITION.POSITION_ID LEFT JOIN DM_HR_GROUP_POSITION ON DM_POSITION.GROUP_POSITION_ID=DM_HR_GROUP_POSITION.GROUP_POSITION_ID LEFT JOIN DM_HR_CAREER ON P.CAREER_ID=DM_HR_CAREER.CAREER_ID LEFT JOIN DM_PR_HR_ORGANIZATION DM_ORG ON P.PR_ORGANIZATION_ID=DM_ORG.PR_ORGANIZATION_ID LEFT JOIN DM_EMPLOYEE_LEVEL ON P.EMPLOYEE_LEVEL_ID = DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID LEFT JOIN HR_ORGANIZATION_VIEW ON HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID = P.PR_ORGANIZATION_ID LEFT JOIN HR_EMPLOYEE_HEALTH ON A.EMPLOYEE_ID = HR_EMPLOYEE_HEALTH.EMPLOYEE_ID LEFT JOIN HR_EMPLOYEE_POLITICS ON A.EMPLOYEE_ID = HR_EMPLOYEE_POLITICS.EMPLOYEE_ID LEFT JOIN HR_EMPLOYEE_OTHER ON A.EMPLOYEE_ID = HR_EMPLOYEE_OTHER.EMPLOYEE_ID LEFT JOIN ( select EMPLOYEE_ID, Cast(SUM(NUM_MONTH)as smallint) as NUM_MONTH_UNJOIN from HR_EMPLOYEE_HISTORY_UNJOIN_INS GROUP BY EMPLOYEE_ID ) HR_UNJOIN_INS ON A.EMPLOYEE_ID = HR_UNJOIN_INS.EMPLOYEE_ID where A.ACCEPT_STATUS = @ACCEP_STATUS and A.START_DATE_STATUS <= GETDATE() and A.START_DATE_POSITION <= GETDATE() AND A.START_DATE_CONTRACT <= GETDATE() ORDER BY DM_POSITION.POSITION_INDEX_VIEW,A.EMPLOYEE_ID END GO DROP PROCEDURE [dbo].[Pro_Load_Full_Employee_Info_With_Status] GO CREATE PROCEDURE [dbo].[Pro_Load_Full_Employee_Info_With_Status] @ACCEP_STATUS nvarchar(50), @EMPLOYEE_STATUS_ID nvarchar(20) AS BEGIN SET NOCOUNT ON; select distinct A.*,A.START_DATE_STATUS as EMPLOYEE_STATUS_DATE,DM_S.EMPLOYEE_STATUS_NAME,C.END_DATE as END_DATE_CONTRACT,C.COMMENTS as CONTRACT_COMMENTS,C.CONTRACT_DATE,C.CONTRACT_NO,C.CONTRACT_PERIOD,C.DUTY_RATE,C.IS_CURRENT as CONTRACT_IS_CURRENT,C.MANAGER_ID,DM_C.CONTRACT_TYPE_NAME, P.PR_ORGANIZATION_ID,P.POSITION_ID,P.CAREER_ID,P.ALLOW_SALARY_RAISE,P.COMMENTS as POSITION_COMMENTS, P.DESC_OF_WORK,P.END_DATE,P.IS_CURRENT as POSITION_IS_CURRENT,P.LINE_MANAGER_ID, P.POS_DECISION_DATE,P.POS_DECISION_NO,P.REASON_ID, P.SALARY_LEVEL_ID,P.LEVEL_SALARY,P.RATE_SALARY_POINT1,P.SALARY_POINT1,P.SALARY_POINT2,P.SALARY_AMOUNT1,P.SALARY_AMOUNT2, P.SALARY_RANK_ID,P.SCALE_OF_CIVIL_ID,P.WORK_PLACE_ID, DM_ORG.PR_ORGANIZATION_NAME, DM_POSITION.POSITION_NAME,DM_POSITION.POSITION_INDEX_VIEW,DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME, DM_ORG.PR_ORGANIZATION_NAME_EN,DM_ORG.PR_ORGANIZATION_NAME_OTHER,DM_POSITION.POSITION_NAME_EN,DM_POSITION.POSITION_NAME_OTHER, DM_S.EMPLOYEE_STATUS_NAME_EN,DM_S.EMPLOYEE_STATUS_NAME_OTHER, DM_POSITION.GROUP_POSITION_ID,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_EN,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_OTHER, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_EN, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_OTHER, DM_HR_CAREER.CAREER_NAME_EN, DM_HR_CAREER.CAREER_NAME_OTHER, HR_EMPLOYEE_HEALTH.BLOOD_TYPE,HR_EMPLOYEE_HEALTH.COMMENTS,HR_EMPLOYEE_HEALTH.DISEASES, HR_EMPLOYEE_HEALTH.EVALUATION_DATE, dbo.HR_ORGANIZATION_VIEW.HR_LIST_PARENT_ORGANIZATION_ID AS HR_PARENT_ORGANIZATION_ID, dbo.HR_ORGANIZATION_VIEW.HR_LIST_CHILD_ORGANIZATION_ID, dbo.HR_EMPLOYEE_HEALTH.HEALTH_LEVEL,HR_EMPLOYEE_HEALTH.HEALTH_STATUS,HR_EMPLOYEE_HEALTH.HEIGHT,HR_EMPLOYEE_HEALTH.WEIGHT, dbo.HR_EMPLOYEE_POLITICS.IS_PARTY_MEMBER, dbo.HR_EMPLOYEE_POLITICS.PARTY_PROBATION_DATE, dbo.HR_EMPLOYEE_POLITICS.PARTY_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.PARTY_POSITION, dbo.HR_EMPLOYEE_POLITICS.PARTY_JOIN_PLACE, dbo.HR_EMPLOYEE_POLITICS.YOUTH_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.IS_YOUTH_MEMBER, dbo.HR_EMPLOYEE_POLITICS.YOUTH_JOIN_PLACE, dbo.HR_EMPLOYEE_POLITICS.YOUTH_POSITION, dbo.HR_EMPLOYEE_POLITICS.IS_MILITARY, dbo.HR_EMPLOYEE_POLITICS.MILITARY_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.MILITARY_POSITION, dbo.HR_EMPLOYEE_POLITICS.MILIBARY_LEVEL, dbo.HR_EMPLOYEE_POLITICS.MILITARY_ORGANIZATION, dbo.HR_EMPLOYEE_POLITICS.MILITARY_LEAVE_DATE, dbo.HR_EMPLOYEE_POLITICS.MILITARY_LEAVE_REASON, dbo.HR_EMPLOYEE_POLITICS.IS_WOUNDED, dbo.HR_EMPLOYEE_POLITICS.WONDED_LEVEL, dbo.HR_EMPLOYEE_POLITICS.WONDED_PERCENTAGE, dbo.HR_EMPLOYEE_POLITICS.REVOLUTION_JOIN_DATE, dbo.HR_EMPLOYEE_POLITICS.CREDITS_FAULTS AS POLITICS_CREDITS_FAULTS, dbo.HR_EMPLOYEE_POLITICS.IS_CREDITED, dbo.HR_EMPLOYEE_POLITICS.STATE_MANAGEMENT_ID, dbo.HR_EMPLOYEE_POLITICS.IS_POLICY, dbo.HR_EMPLOYEE_POLITICS.POLITICAL_THEORY_ID, dbo.HR_EMPLOYEE_POLITICS.COMMENTS AS POLITICS_COMMENTS, dbo.HR_EMPLOYEE_OTHER.WORK_EXPERIENCE, dbo.HR_EMPLOYEE_OTHER.SKILL_SUMMARY, dbo.HR_EMPLOYEE_OTHER.CREDITS_FAULTS, dbo.HR_EMPLOYEE_OTHER.INTERESTS, dbo.HR_EMPLOYEE_OTHER.STRENGTHS, dbo.HR_EMPLOYEE_OTHER.WEAKNESSES, dbo.HR_EMPLOYEE_OTHER.COMMENTS AS OTHER_COMMENTS, dbo.HR_EMPLOYEE_OTHER.ENROLL_NUMBER, dbo.HR_EMPLOYEE_OTHER.FOREIGN_RELATION, dbo.HR_EMPLOYEE_OTHER.LAW_VIOLATIONS, dbo.HR_EMPLOYEE_OTHER.FAMILY_OVERSEAS, DATEDIFF(YEAR, A.DOB, GETDATE()) AS YEARS, DATEDIFF(mm,DATE_COMPANY,GETDATE()) as NUM_MONTH,DATEDIFF(mm,DATE_COMPANY,GETDATE())/12 as N_YEAR, CONVERT(NVARCHAR(50),DATEDIFF(mm,DATE_COMPANY,GETDATE())/12)+' '+N' nam '+' '+CONVERT(NVARCHAR(50), DATEDIFF(mm,DATE_COMPANY,GETDATE())-(DATEDIFF(mm,DATE_COMPANY,GETDATE())/12)*12)+' '+N' thang ' as STRING_SENIORITY, CONVERT(NVARCHAR(50),(DATEDIFF(mm,DATE_COMPANY,GETDATE())+1)/12)+' '+N' nam '+' '+CONVERT(NVARCHAR(50), ((DATEDIFF(mm,DATE_COMPANY,GETDATE())+1) -((DATEDIFF(mm,DATE_COMPANY,GETDATE())+1)/12)*12 ))+' '+N' thang ' as STRING_SENIORITY_NN , CONVERT(NVARCHAR(50),(DATEDIFF(mm,SOCIAL_BOOK_ISSUE_DATE,GETDATE())+1)/12)+' '+N' nam '+' '+CONVERT(NVARCHAR(50), ((DATEDIFF(mm,SOCIAL_BOOK_ISSUE_DATE,GETDATE())+1) -((DATEDIFF(mm,SOCIAL_BOOK_ISSUE_DATE,GETDATE())+1)/12)*12 ))+' '+N' thang ' as STRING_SENIORITY_SOCIAL , DATEDIFF(mm,DATE_COMPANY,GETDATE())-(DATEDIFF(mm,DATE_COMPANY,GETDATE())/12)*12 as N_MONTH, CAST((DATEDIFF(mm,DATE_COMPANY,GETDATE())/(CONVERT(DECIMAL(9,2),12))) AS NUMERIC(18,1)) as NUM_YEAR, '' as LEAVE_REASON_ID, '' as P_HAMLET,'' as P_COMMUNES_ID,'' as P_DISTRICT_ID,'' as P_PROVINCE_ID,'' as E_PERMANENT_ADDRESS, '' as E_PERMANENT_ADDRESS_EN, '' as T_HAMLET,'' as T_COMMUNES_ID,'' as T_DISTRICT_ID,'' as T_PROVINCE_ID,'' as E_TEMPORARY_ADDRESS, '' as E_TEMPORARY_ADDRESS_OTHER, '' as EDU_ID_MAX,'' as CERTIFICATION_NAME_MAX,'' as REWARDS_MAX,'' as FAULTS_MAX , DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, A.DOB), A.DOB) as DOB_CURRENT_YEAR from HR_EMPLOYEE_INFO A LEFT JOIN HR_EMPLOYEE_POSITION P ON A.EMPLOYEE_ID=P.EMPLOYEE_ID AND A.START_DATE_POSITION=P.START_DATE AND P.IS_CURRENT =1 LEFT JOIN DM_EMPLOYEE_STATUS DM_S ON A.EMPLOYEE_STATUS_ID = DM_S.EMPLOYEE_STATUS_ID LEFT JOIN HR_EMPLOYEE_CONTRACT C ON A.EMPLOYEE_ID=C.EMPLOYEE_ID AND A.START_DATE_CONTRACT=C.START_DATE LEFT JOIN DM_CONTRACT_TYPE DM_C ON C.CONTRACT_TYPE_ID=DM_C.CONTRACT_TYPE_ID LEFT JOIN DM_POSITION ON P.POSITION_ID=DM_POSITION.POSITION_ID LEFT JOIN DM_HR_GROUP_POSITION ON DM_POSITION.GROUP_POSITION_ID=DM_HR_GROUP_POSITION.GROUP_POSITION_ID LEFT JOIN DM_HR_CAREER ON P.CAREER_ID=DM_HR_CAREER.CAREER_ID LEFT JOIN DM_PR_HR_ORGANIZATION DM_ORG ON P.PR_ORGANIZATION_ID=DM_ORG.PR_ORGANIZATION_ID LEFT JOIN DM_EMPLOYEE_LEVEL ON P.EMPLOYEE_LEVEL_ID = DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID LEFT JOIN HR_ORGANIZATION_VIEW ON HR_ORGANIZATION_VIEW.PR_ORGANIZATION_ID = P.PR_ORGANIZATION_ID LEFT JOIN HR_EMPLOYEE_HEALTH ON A.EMPLOYEE_ID = HR_EMPLOYEE_HEALTH.EMPLOYEE_ID LEFT JOIN HR_EMPLOYEE_POLITICS ON A.EMPLOYEE_ID = HR_EMPLOYEE_POLITICS.EMPLOYEE_ID LEFT JOIN HR_EMPLOYEE_OTHER ON A.EMPLOYEE_ID = HR_EMPLOYEE_OTHER.EMPLOYEE_ID where A.ACCEPT_STATUS = @ACCEP_STATUS and A.EMPLOYEE_STATUS_ID = @EMPLOYEE_STATUS_ID and A.START_DATE_STATUS <= GETDATE() and A.START_DATE_POSITION <= GETDATE() AND A.START_DATE_CONTRACT <= GETDATE() ORDER BY P.PR_ORGANIZATION_ID, A.EMPLOYEE_ID END GO -- 04/04 -- GO ALTER TABLE DM_CONTRACT_TYPE ADD CONTRACT_PERIOD smallint DEFAULT 0 NOT NULL GO ALTER TABLE DM_CONTRACT_TYPE ADD DUTY_RATE money DEFAULT 1 NOT NULL GO -- 05/04 -- GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_EMPLOYEE_CONTRACT_EXP_VIEW','HRM','HRM_INFO','FUNC','HRMHRINFOGROUP3','Hrm_Dm_Position.png',80,'HR_EMPLOYEE_CONTRACT_EXP_VIEW',4,0) GO update SYS_GRIDINFO set visible = '1', visibleindex = '0', filtered ='1' , enabled =0 where (form_name = 'FrmHr_Employee_Contract_Exp_View_EditList' and GRID_NAME = 'TREELIST') GO DROP PROCEDURE [dbo].[CHANGEACCOUNT] GO CREATE PROCEDURE [dbo].[CHANGEACCOUNT] @poldaccount varchar(20), @pnewaccount varchar(20) as begin transaction modify if not exists(select * from DM_ACCOUNT where ACCOUNT_ID=@pnewaccount) begin insert into DM_ACCOUNT SELECT @pnewaccount, ACCOUNT_NAME, ACCOUNT_NAME_ULS, ACCOUNT_LEVEL, IS_PARENT, IS_PR_DETAIL, IS_EXPENSE, IS_JOB, IS_ITEM, IS_VAT, IS_OOB, BALANCE_TYPE, BANK_NAME, BANK_BRANCH, BANK_ACCOUNT, VOUCHER_FILE_NAME, ACTIVE, USER_ID, ACCOUNT_TYPE_ID, PARENT_ACCOUNT_ID, CURRENCY_ID, RATE_METHOD, ACCOUNT_ID_ULS, ACCOUNT_NAME_JP, ACCOUNT_NAME_KR, IS_QUANTITY, IS_PAYMENT, IS_BANK, IS_POS, IS_CHECK, IS_CHAPTER, IS_CAPITAL_SOURCE FROM DM_ACCOUNT where account_id=@poldaccount end UPDATE DM_ACCOUNT SET ACTIVE=0 WHERE ACCOUNT_ID=@poldaccount UPDATE ADJUSTMENT SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE ADJUSTMENT SET ADJUST_ACCOUNT_ID=@pnewaccount WHERE ADJUST_ACCOUNT_ID=@poldaccount UPDATE ASSET SET DEP_ACCOUNT_ID=@pnewaccount WHERE DEP_ACCOUNT_ID=@poldaccount UPDATE ASSET SET EXPENSE_ACCOUNT_ID=@pnewaccount WHERE EXPENSE_ACCOUNT_ID=@poldaccount UPDATE ASSET SET FA_ACCOUNT_ID=@pnewaccount WHERE FA_ACCOUNT_ID=@poldaccount UPDATE ASSET_DEP SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE ASSET_DEP SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE ADJUSTMENT_ASSET SET FA_ACCOUNT_ID=@pnewaccount WHERE FA_ACCOUNT_ID=@poldaccount UPDATE BALANCE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE BUDGET SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE CLOSING_ENTRY SET ACCOUNT_DES=@pnewaccount WHERE ACCOUNT_DES=@poldaccount UPDATE CLOSING_ENTRY SET ACCOUNT_SRC=@pnewaccount WHERE ACCOUNT_SRC=@poldaccount UPDATE CONTRACT SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE CONTRACT_IMPLEMENTATION SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE CONTRACT_PAYMENT SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE CONTRACT_RATE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE DM_FA_CLASS SET DEP_ACCOUNT_ID=@pnewaccount WHERE DEP_ACCOUNT_ID=@poldaccount UPDATE DM_FA_CLASS SET EXPENSE_ACCOUNT_ID=@pnewaccount WHERE EXPENSE_ACCOUNT_ID=@poldaccount UPDATE DM_FA_CLASS SET FA_ACCOUNT_ID=@pnewaccount WHERE FA_ACCOUNT_ID=@poldaccount UPDATE DM_ITEM SET ACCOUNT_ID_COST=@pnewaccount WHERE ACCOUNT_ID_COST=@poldaccount UPDATE DM_ITEM SET ACCOUNT_ID_SALE_COST=@pnewaccount WHERE ACCOUNT_ID_SALE_COST=@poldaccount UPDATE DM_ITEM SET ACCOUNT_ID_INCOME=@pnewaccount WHERE ACCOUNT_ID_INCOME=@poldaccount UPDATE DM_ITEM SET ACCOUNT_ID_RETURN=@pnewaccount WHERE ACCOUNT_ID_RETURN=@poldaccount UPDATE DM_ITEM_CLASS SET DEFAULT_ACCOUNT_ID_COST=@pnewaccount WHERE DEFAULT_ACCOUNT_ID_COST=@poldaccount UPDATE DM_ITEM_CLASS SET DEFAULT_ACCOUNT_ID_INCOME=@pnewaccount WHERE DEFAULT_ACCOUNT_ID_INCOME=@poldaccount UPDATE DM_ITEM_CLASS SET DEFAULT_ACCOUNT_ID_RETURN=@pnewaccount WHERE DEFAULT_ACCOUNT_ID_RETURN=@poldaccount UPDATE DM_ITEM_CLASS_MAPPING SET DEFAULT_ACCOUNT_ID_COST=@pnewaccount WHERE DEFAULT_ACCOUNT_ID_COST=@poldaccount UPDATE DM_ITEM_CLASS_MAPPING SET DEFAULT_ACCOUNT_ID_INCOME=@pnewaccount WHERE DEFAULT_ACCOUNT_ID_INCOME=@poldaccount UPDATE DM_ITEM_CLASS_MAPPING SET DEFAULT_ACCOUNT_ID_RETURN=@pnewaccount WHERE DEFAULT_ACCOUNT_ID_RETURN=@poldaccount UPDATE DM_JOB SET EXPENSE_ACCOUNT_ID=@pnewaccount WHERE EXPENSE_ACCOUNT_ID=@poldaccount UPDATE DM_PAYMENT_METHOD SET PAYMENT_ACCOUNT_ID=@pnewaccount WHERE PAYMENT_ACCOUNT_ID=@poldaccount UPDATE DM_POSTING_DETAIL SET ACCOUNT_ID_DEBIT=@pnewaccount WHERE ACCOUNT_ID_DEBIT=@poldaccount UPDATE DM_POSTING_DETAIL SET ACCOUNT_ID_CREDIT=@pnewaccount WHERE ACCOUNT_ID_CREDIT=@poldaccount UPDATE DM_PR_DETAIL SET PR_ACCOUNT_ID=@pnewaccount WHERE PR_ACCOUNT_ID=@poldaccount UPDATE DM_PR_DETAIL SET PR_ACCOUNT_ID1=@pnewaccount WHERE PR_ACCOUNT_ID1=@poldaccount UPDATE DM_WAREHOUSE SET COST_ACCOUNT_ID=@pnewaccount WHERE COST_ACCOUNT_ID=@poldaccount UPDATE DM_WAREHOUSE SET SALE_COST_ACCOUNT_ID=@pnewaccount WHERE SALE_COST_ACCOUNT_ID=@poldaccount UPDATE JOURNAL_DETAIL SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE LEDGER SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE LEDGER SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE PAYMENT_BALANCE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE PAYMENT_BALANCE SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE PURCHASE SET ACCOUNT_ID_PR=@pnewaccount WHERE ACCOUNT_ID_PR=@poldaccount UPDATE PURCHASE SET ACCOUNT_ID_IMPORT_TAX=@pnewaccount WHERE ACCOUNT_ID_IMPORT_TAX=@poldaccount UPDATE PURCHASE SET ACCOUNT_ID_VAT=@pnewaccount WHERE ACCOUNT_ID_VAT=@poldaccount UPDATE PURCHASE SET ACCOUNT_ID_VAT_CREDIT=@pnewaccount WHERE ACCOUNT_ID_VAT_CREDIT=@poldaccount UPDATE PURCHASE_DETAIL SET ACCOUNT_ID_COST=@pnewaccount WHERE ACCOUNT_ID_COST=@poldaccount UPDATE PURCHASE_DETAIL SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE PURCHASE_DETAIL SET ACCOUNT_ID_RETURN=@pnewaccount WHERE ACCOUNT_ID_RETURN=@poldaccount UPDATE PURCHASE_DETAIL SET ACCOUNT_ID_COGS=@pnewaccount WHERE ACCOUNT_ID_COGS=@poldaccount UPDATE PURCHASE_PAYMENT SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE SALE SET ACCOUNT_ID_PR=@pnewaccount WHERE ACCOUNT_ID_PR=@poldaccount UPDATE SALE SET ACCOUNT_ID_VAT=@pnewaccount WHERE ACCOUNT_ID_VAT=@poldaccount UPDATE SALE SET ACCOUNT_ID_EXPORT_TAX=@pnewaccount WHERE ACCOUNT_ID_EXPORT_TAX=@poldaccount UPDATE SALE_DETAIL SET ACCOUNT_ID_COST=@pnewaccount WHERE ACCOUNT_ID_COST=@poldaccount UPDATE SALE_DETAIL SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE SALE_DETAIL SET ACCOUNT_ID_INCOME=@pnewaccount WHERE ACCOUNT_ID_INCOME=@poldaccount UPDATE SALE_FIXED SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE SALE_PAYMENT SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE TOOL_ALLOCATION SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE TOOL_ALLOCATION SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE TOOL_ALLOCATION SET EXPENSE_ACCOUNT_ID=@pnewaccount WHERE EXPENSE_ACCOUNT_ID=@poldaccount UPDATE VAT_TRANSACTION SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE VAT_TRANSACTION SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE VOUCHER_DETAIL SET ACCOUNT_ID_DEBIT=@pnewaccount WHERE ACCOUNT_ID_DEBIT=@poldaccount UPDATE VOUCHER_DETAIL SET ACCOUNT_ID_CREDIT=@pnewaccount WHERE ACCOUNT_ID_CREDIT=@poldaccount UPDATE WAREHOUSE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE WAREHOUSE SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE WAREHOUSE_BALANCE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE WAREHOUSE_BALANCE_ACTUAL SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE CA_EXPENSE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE CA_EXPENSE SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE CA_EXPENSE_DECREASE SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE CA_EXPENSE_RESULT SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount UPDATE CA_EXPENSE_RESULT SET ACCOUNT_ID_CONTRA=@pnewaccount WHERE ACCOUNT_ID_CONTRA=@poldaccount UPDATE CA_RATIO SET ACCOUNT_ID=@pnewaccount WHERE ACCOUNT_ID=@poldaccount COMMIT transaction GO GO -- 07/04 -- GO ALTER TABLE DM_ADDRESS_TYPE ADD IS_PERMANENT smallint DEFAULT 0 NOT NULL GO -- 07/04 -- GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pro_Load_Quick_A_Employee_Info_Web]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[Pro_Load_Quick_A_Employee_Info_Web] GO CREATE PROCEDURE [dbo].[Pro_Load_Quick_A_Employee_Info_Web] @EMPLOYEE_ID nvarchar(50) AS BEGIN SET NOCOUNT ON; select distinct HR_EMPLOYEE_INFO.EMPLOYEE_ID ,HR_EMPLOYEE_INFO.EMPLOYEE_NAME ,HR_EMPLOYEE_INFO.EMPLOYEE_NAME_OTHER ,HR_EMPLOYEE_INFO.EMPLOYEE_STATUS_ID, HR_EMPLOYEE_INFO.SEX ,HR_EMPLOYEE_INFO.DOB ,HR_EMPLOYEE_INFO.BIRTH_PLACE ,HR_EMPLOYEE_INFO.ORIGIN_PLACE ,HR_EMPLOYEE_INFO.ORIGIN_CITY , HR_EMPLOYEE_INFO.IDENTITY_NO ,HR_EMPLOYEE_INFO.IDENTITY_ISSUE_DATE ,HR_EMPLOYEE_INFO.IDENTITY_ISSUE_PLACE , HR_EMPLOYEE_INFO.PASSPORT_NO ,HR_EMPLOYEE_INFO.PASSPORT_ISSUE_DATE ,HR_EMPLOYEE_INFO.PASSPORT_ISSUE_PLACE ,HR_EMPLOYEE_INFO.PASSPORT_EXPIRE_DATE , HR_EMPLOYEE_INFO.SOCIAL_BOOK_NO ,HR_EMPLOYEE_INFO.SOCIAL_BOOK_ISSUE_DATE ,HR_EMPLOYEE_INFO.SOCIAL_BOOK_ISSUE_PLACE, HR_EMPLOYEE_INFO.HOME_PHONE ,HR_EMPLOYEE_INFO.WORK_PHONE ,HR_EMPLOYEE_INFO.MOBILE_PHONE ,HR_EMPLOYEE_INFO.PERSONAL_EMAIL ,HR_EMPLOYEE_INFO.WORK_EMAIL ,HR_EMPLOYEE_INFO.YAHOO_ID ,HR_EMPLOYEE_INFO.SKYPE_ID , HR_EMPLOYEE_INFO.PERSONAL_TAXCODE ,HR_EMPLOYEE_INFO.PERSONAL_TAXCODE_DATE ,HR_EMPLOYEE_INFO.PERSONAL_TAXCODE_PLACE , HR_EMPLOYEE_INFO.WORK_DATE_START ,HR_EMPLOYEE_INFO.DATE_COMPANY ,HR_EMPLOYEE_INFO.DATE_GROUP_COMPANY ,HR_EMPLOYEE_INFO.CANDIDATES_ID , HR_EMPLOYEE_INFO.INFO_EXTRA_STRING_1 ,HR_EMPLOYEE_INFO.INFO_EXTRA_STRING_2 ,HR_EMPLOYEE_INFO.INFO_EXTRA_STRING_3 , HR_EMPLOYEE_INFO.REC_CHANNEL_ID ,HR_EMPLOYEE_INFO.REC_CHANNEL_DETAIL ,HR_EMPLOYEE_INFO.EMPLOYEE_INTRODUCED_ID, HR_EMPLOYEE_POSITION.PR_ORGANIZATION_ID ,HR_EMPLOYEE_POSITION.POSITION_ID ,HR_EMPLOYEE_POSITION.EMPLOYEE_LEVEL_ID ,HR_EMPLOYEE_POSITION.CAREER_ID , HR_EMPLOYEE_POSITION.START_DATE ,HR_EMPLOYEE_POSITION.END_DATE ,HR_EMPLOYEE_POSITION.POS_DECISION_DATE , HR_EMPLOYEE_POSITION.SALARY_POINT1 ,HR_EMPLOYEE_POSITION.SALARY_POINT2 ,HR_EMPLOYEE_POSITION.RATE_SALARY_POINT1 ,HR_EMPLOYEE_POSITION.SALARY_AMOUNT1,HR_EMPLOYEE_POSITION.SALARY_AMOUNT2, HR_EMPLOYEE_POSITION.SALARY_LEVEL_ID ,HR_EMPLOYEE_POSITION.LEVEL_SALARY ,HR_EMPLOYEE_POSITION.REASON_ID , HR_EMPLOYEE_POSITION.POS_DECISION_NO ,HR_EMPLOYEE_POSITION.LINE_MANAGER_ID ,HR_EMPLOYEE_POSITION.WORK_PLACE_ID ,HR_EMPLOYEE_POSITION.DESC_OF_WORK , HR_EMPLOYEE_POSITION.ALLOW_SALARY_RAISE ,HR_EMPLOYEE_POSITION.COMMENTS ,HR_EMPLOYEE_POSITION.IS_CURRENT ,HR_EMPLOYEE_POSITION.SCALE_OF_CIVIL_ID, HR_EMPLOYEE_CONTRACT.CONTRACT_NO,HR_EMPLOYEE_CONTRACT.DUTY_RATE,HR_EMPLOYEE_CONTRACT.CONTRACT_TYPE_ID,HR_EMPLOYEE_CONTRACT.START_DATE as CONTRACT_START_DATE, HR_EMPLOYEE_CONTRACT.END_DATE as CONTRACT_END_DATE,HR_EMPLOYEE_CONTRACT.CONTRACT_DATE ,DM_CONTRACT_TYPE.CONTRACT_TYPE_NAME,DM_CONTRACT_TYPE.CONTRACT_TYPE_NAME_EN,DM_CONTRACT_TYPE.CONTRACT_TYPE_NAME_OTHER, HR_EMPLOYEE_CONTRACT.COMMENTS as CONTRACT_COMMENTS, DM_POSITION.POSITION_NAME,DM_POSITION.POSITION_NAME_EN,DM_POSITION.POSITION_NAME_OTHER,DM_POSITION.GROUP_POSITION_ID, DM_GROUP_SALARY.GROUP_SALARY_NAME,DM_GROUP_SALARY.GROUP_SALARY_NAME_BY_LAW, DM_HR_CAREER.CAREER_NAME,DM_HR_CAREER.CAREER_NAME_EN,DM_HR_CAREER.CAREER_NAME_OTHER, DM_HR_GROUP_POSITION.GROUP_POSITION_NAME,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_EN,DM_HR_GROUP_POSITION.GROUP_POSITION_NAME_OTHER , DM_PR_HR_ORGANIZATION.PR_ORGANIZATION_NAME,DM_PR_HR_ORGANIZATION.PR_ORGANIZATION_NAME_EN,DM_PR_HR_ORGANIZATION.PR_ORGANIZATION_NAME_OTHER, DM_PR_HR_ORGANIZATION.ORGANIZATION_ID,DM_ORGANIZATION.ORGANIZATION_NAME, DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME,DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_EN,DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_NAME_OTHER, CONVERT(NVARCHAR(50),DATEDIFF(mm,DATE_COMPANY,GETDATE() )/12)+N' năm '+CONVERT(NVARCHAR(50), + DATEDIFF(mm,DATE_COMPANY, GETDATE() )-(DATEDIFF(mm,DATE_COMPANY, GETDATE() )/12)*12)+N' tháng ' as STRING_SENIORITY, CONVERT(NVARCHAR(50),(DATEDIFF(mm,DATE_COMPANY, GETDATE() )+1)/12)+N' năm '+CONVERT(NVARCHAR(50), + ((DATEDIFF(mm,DATE_COMPANY, GETDATE() )+1) -((DATEDIFF(mm,DATE_COMPANY, GETDATE() )+1)/12)*12 ))+N' tháng ' as STRING_SENIORITY_NN , P_ADDRESS.ADDRESS as P_ADDRESS ,P_DM_COUNTRY.COUNTRY_NAME as P_COUNTRY_NAME,P_ADDRESS.POST_CODE as P_POST_CODE, P_ADDRESS.ADDRESS_ENG as P_ADDRESS_ENG, T_ADDRESS.ADDRESS as T_ADDRESS,T_DM_COUNTRY.COUNTRY_NAME as T_COUNTRY_NAME,T_ADDRESS.POST_CODE as T_POST_CODE, T_ADDRESS.ADDRESS_ENG as T_ADDRESS_ENG from HR_EMPLOYEE_INFO LEFT JOIN HR_EMPLOYEE_POSITION ON HR_EMPLOYEE_INFO.EMPLOYEE_ID=HR_EMPLOYEE_POSITION.EMPLOYEE_ID and HR_EMPLOYEE_INFO.START_DATE_POSITION=HR_EMPLOYEE_POSITION.START_DATE and HR_EMPLOYEE_POSITION.IS_CURRENT = 1 LEFT JOIN HR_EMPLOYEE_CONTRACT ON HR_EMPLOYEE_INFO.EMPLOYEE_ID=HR_EMPLOYEE_CONTRACT.EMPLOYEE_ID AND HR_EMPLOYEE_INFO.START_DATE_CONTRACT=HR_EMPLOYEE_CONTRACT.START_DATE LEFT JOIN DM_PR_HR_ORGANIZATION ON HR_EMPLOYEE_POSITION.PR_ORGANIZATION_ID = DM_PR_HR_ORGANIZATION.PR_ORGANIZATION_ID LEFT JOIN DM_POSITION ON HR_EMPLOYEE_POSITION.POSITION_ID = DM_POSITION.POSITION_ID LEFT JOIN DM_EMPLOYEE_LEVEL ON HR_EMPLOYEE_POSITION.EMPLOYEE_LEVEL_ID = DM_EMPLOYEE_LEVEL.EMPLOYEE_LEVEL_ID LEFT JOIN DM_HR_CAREER ON HR_EMPLOYEE_POSITION.CAREER_ID = DM_HR_CAREER.CAREER_ID LEFT JOIN DM_GROUP_SALARY ON HR_EMPLOYEE_POSITION.SALARY_LEVEL_ID = DM_GROUP_SALARY.GROUP_SALARY_ID LEFT JOIN DM_HR_GROUP_POSITION ON DM_POSITION.GROUP_POSITION_ID = DM_HR_GROUP_POSITION.GROUP_POSITION_ID LEFT JOIN DM_ORGANIZATION ON DM_PR_HR_ORGANIZATION.ORGANIZATION_ID = DM_ORGANIZATION.ORGANIZATION_ID LEFT JOIN DM_CONTRACT_TYPE ON HR_EMPLOYEE_CONTRACT.CONTRACT_TYPE_ID=DM_CONTRACT_TYPE.CONTRACT_TYPE_ID LEFT JOIN HR_EMPLOYEE_ADDRESS P_ADDRESS ON HR_EMPLOYEE_INFO.EMPLOYEE_ID = P_ADDRESS.EMPLOYEE_ID and P_ADDRESS.ADDRESS <> '' and P_ADDRESS.ADDRESS_TYPE_ID = (select top 1 ADDRESS_TYPE_ID from DM_ADDRESS_TYPE where IS_PERMANENT = 1) LEFT JOIN HR_EMPLOYEE_ADDRESS T_ADDRESS ON HR_EMPLOYEE_INFO.EMPLOYEE_ID = T_ADDRESS.EMPLOYEE_ID and T_ADDRESS.ADDRESS <> '' and T_ADDRESS.ADDRESS_TYPE_ID = (select top 1 ADDRESS_TYPE_ID from DM_ADDRESS_TYPE where IS_PERMANENT = 0) LEFT JOIN DM_COUNTRY P_DM_COUNTRY ON P_ADDRESS.COUNTRY_ID=P_DM_COUNTRY.COUNTRY_ID LEFT JOIN DM_COUNTRY T_DM_COUNTRY ON P_ADDRESS.COUNTRY_ID=T_DM_COUNTRY.COUNTRY_ID WHERE HR_EMPLOYEE_INFO.EMPLOYEE_ID = @EMPLOYEE_ID END GO ALTER TABLE HR_PLAN_OVERTIME ADD RQ_TIME_OUT smalldatetime DEFAULT '2010-01-01' NOT NULL GO ALTER TABLE HR_PLAN_OVERTIME ADD RQ_TIME_IN smalldatetime DEFAULT '2010-01-01' NOT NULL GO ALTER TABLE HR_PLAN_OVERTIME ADD RQ_OVERTIME_REG money DEFAULT 0 NOT NULL GO ALTER TABLE HR_PLAN_OVERTIME ADD IS_APPROVED smallint DEFAULT 1 NOT NULL GO ALTER TABLE HR_PLAN_OVERTIME ADD RQ_COMMENT nvarchar(200) DEFAULT '' NOT NULL GO -- 08/04 -- GO ALTER TABLE HR_TIME_WORK_MANUAL ADD COUNT_DAYS_MORE_ONE money DEFAULT 0 NOT NULL GO -- 12/04 -- GO CREATE TABLE [HR_ORG_MANAGE_SHIFT_PLAN]( [PR_KEY] [uniqueidentifier] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [PR_ORGANIZATION_ID] [nvarchar](20) NOT NULL, [COMMENTS] [nvarchar](500) NOT NULL PRIMARY KEY ( [EMPLOYEE_ID],[PR_ORGANIZATION_ID] )) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_ORG_MANAGE_SHIFT_PLAN','HRM','HRM_TIME','LIST','','',100,'HR_ORG_MANAGE_SHIFT_PLAN',10,1) GO INSERT INTO SYS_SYSTEMVAR(VAR_NAME, VAR_VALUE, DESCRIPTION, VAR_TYPE, VAR_GROUP) VALUES ('SALE_COST_BY_ORGANIZATION', 1, 'SALE_COST_BY_ORGANIZATION', 'BOOLEAN', 'COMMON') GO -- 13-04-2015 GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_SALARY_TYPE_FOR_BASIC_AVG', 'LCB TB', '', 'STRING', 'HRM') GO -- 14/04 -- GO ALTER TABLE HR_SHIFT_PLAN ADD STATUS_IMPORT nvarchar(200) DEFAULT '' NOT NULL GO DROP VIEW ASSET_VIEW GO CREATE VIEW [dbo].[ASSET_VIEW] AS SELECT dbo.ASSET.PR_KEY AS PR_KEY_CTU, dbo.ASSET.TRAN_ID,dbo.ASSET.TRAN_NO, dbo.ASSET.ASSET_NO, dbo.ASSET.ASSET_ID, dbo.ASSET.DESCRIPTION, dbo.ASSET.FA_CLASS_ID,dbo.ASSET.MANU_YEAR, dbo.ASSET.MANU_COUNTRY, dbo.ASSET.UNIT, dbo.ASSET.QUANTITY, dbo.ASSET.FA_STATUS_ID, dbo.ASSET.PR_DETAIL_ID,dbo.ASSET.PURCHASE_DATE, dbo.ASSET.USE_DATE, dbo.ASSET.LIQUID_DATE, dbo.ASSET.DEP_CEASE_DATE,dbo.ASSET.CANCEL_DATE, dbo.ASSET.DEP_METHOD_ID,dbo.ASSET.DEP_LENGTH, dbo.ASSET.FA_ACCOUNT_ID, dbo.ASSET.DEP_ACCOUNT_ID, dbo.ASSET_DETAIL.LIST_ORDER, dbo.ASSET_DETAIL.FA_OPERATION_ID, dbo.ASSET_DETAIL.FA_SOURCE_ID, dbo.ASSET_DETAIL.EFFECTIVE_DATE, dbo.ASSET_DETAIL.END_DATE, dbo.ASSET_DETAIL.VALUE_PREVIOUS,dbo.ASSET_DETAIL.VALUE_INCREASE, dbo.ASSET_DETAIL.VALUE_ORIG, dbo.ASSET_DETAIL.DEP_VALUE_PREVIOUS, dbo.ASSET_DETAIL.DEP_VALUE_INCREASE,dbo.ASSET_DETAIL.DEP_VALUE_ORIG, dbo.ASSET_DETAIL.DEP_VALUE_ACCU, dbo.ASSET_DETAIL.DEP_VALUE_CURRENT,dbo.ASSET_DETAIL.REMAIN_VALUE_ORIG, dbo.ASSET_DETAIL.REMAIN_VALUE_CURRENT, dbo.ASSET_DETAIL.DEP_RATE, dbo.ASSET_DETAIL.VALUE_PREVIOUS_EXTRA, dbo.ASSET_DETAIL.VALUE_INCREASE_EXTRA, dbo.ASSET_DETAIL.VALUE_ORIG_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_PREVIOUS_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_INCREASE_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_ORIG_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_ACCU_EXTRA, dbo.ASSET_DETAIL.DEP_VALUE_CURRENT_EXTRA, dbo.ASSET_DETAIL.REMAIN_VALUE_ORIG_EXTRA, dbo.ASSET_DETAIL.REMAIN_VALUE_CURRENT_EXTRA, dbo.ASSET_DETAIL.DEP_RATE_EXTRA, dbo.ASSET_DETAIL.LOCKED, dbo.DM_FA_OPERATION.INC_DEC, dbo.ASSET.FA_ACCOUNT_ID AS ACCOUNT_ID, dbo.ASSET_DETAIL.EXCHANGE_RATE_EXTRA, dbo.ASSET.JOB_ID, dbo.ASSET.ORGANIZATION_ID, dbo.ASSET.EXPENSE_ID, dbo.ASSET.EXPENSE_ACCOUNT_ID, dbo.ASSET_DETAIL.FR_KEY, dbo.ASSET_DETAIL.PR_KEY, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.DM_FA_SOURCE.FA_SOURCE_NAME, dbo.ASSET.COMMENTS,DM_FA_STATUS.FA_STATUS_NAME FROM dbo.ASSET INNER JOIN dbo.ASSET_DETAIL ON dbo.ASSET.PR_KEY = dbo.ASSET_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_FA_SOURCE ON dbo.ASSET_DETAIL.FA_SOURCE_ID = dbo.DM_FA_SOURCE.FA_SOURCE_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.ASSET.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_FA_OPERATION ON dbo.ASSET_DETAIL.FA_OPERATION_ID = dbo.DM_FA_OPERATION.FA_OPERATION_ID LEFT OUTER JOIN dbo.DM_FA_STATUS ON dbo.ASSET.FA_STATUS_ID = dbo.DM_FA_STATUS.FA_STATUS_ID GO DROP VIEW WAREHOUSE_VIEW GO CREATE VIEW [dbo].[WAREHOUSE_VIEW] AS SELECT dbo.WAREHOUSE.PR_KEY_WAREHOUSE, dbo.WAREHOUSE.PR_KEY, dbo.WAREHOUSE.PR_KEY_DETAIL, dbo.WAREHOUSE.ISSUE_RECEIVE, dbo.WAREHOUSE.TRAN_ID, dbo.WAREHOUSE.TRAN_DATE, dbo.WAREHOUSE.TRAN_NO, dbo.WAREHOUSE.COMMENTS, dbo.WAREHOUSE.WAREHOUSE_ID, dbo.WAREHOUSE.ITEM_ID, dbo.WAREHOUSE.LOT_NO, dbo.WAREHOUSE.RECEIVE_DATE, dbo.WAREHOUSE.MANU_DATE, dbo.WAREHOUSE.EXPIRED_TERM, dbo.WAREHOUSE.EXPIRED_DATE, dbo.WAREHOUSE.DESCRIPTION, dbo.WAREHOUSE.DESCRIPTION_ULS, dbo.WAREHOUSE.QUANTITY, dbo.WAREHOUSE.QUANTITY_EXTRA, dbo.WAREHOUSE.UNIT_PRICE, dbo.WAREHOUSE.AMOUNT, dbo.WAREHOUSE.AMOUNT_EXTRA, dbo.WAREHOUSE.ACCOUNT_ID, dbo.WAREHOUSE.JOB_QTY, dbo.WAREHOUSE.ACCOUNT_ID_CONTRA, dbo.WAREHOUSE.EXPENSE_ID, dbo.WAREHOUSE.JOB_ID, ISNULL(dbo.DM_ITEM.ITEM_CLASS_ID, N' ') AS ITEM_CLASS_ID, ISNULL(dbo.DM_JOB.JOB_CLASS_ID, N' ') AS JOB_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.COST_METHOD, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.ITEM_NAME, dbo.WAREHOUSE.ORGANIZATION_ID, ISNULL(dbo.DM_JOB.JOB_NAME, N' ') AS JOB_NAME, dbo.DM_ITEM.ORIGIN, dbo.WAREHOUSE.PR_DETAIL_ID, dbo.WAREHOUSE.ITEM_SOURCE_ID, WAREHOUSE.ITEM_STATUS_ID, dbo.WAREHOUSE.WAREHOUSE_ID_ISSUE, dbo.WAREHOUSE.ITEM_OP_ID, dbo.WAREHOUSE.UNIT_ID_ACTUAL, dbo.WAREHOUSE.ITEM_ID + dbo.WAREHOUSE.UNIT_ID_ACTUAL AS ITEM_COMBO_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_WAREHOUSE.WAREHOUSE_CLASS_ID, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.WAREHOUSE.VAT_TRAN_NO, dbo.WAREHOUSE.POS_SHIFT_PR_KEY,WAREHOUSE.ITEM_HEIGHT,WAREHOUSE.ITEM_WIDTH,WAREHOUSE.ITEM_DEPTH,WAREHOUSE.ITEM_SIZE,WAREHOUSE.ITEM_LOCATION,WAREHOUSE.ITEM_SERI_NO,WAREHOUSE.ITEM_SERI_NO1,WAREHOUSE.ITEM_SERI_NO2,WAREHOUSE.ITEM_GRADE_ID,WAREHOUSE.ITEM_GRADE1_ID,DM_ITEM.ITEM_CLASS1_ID,DM_WAREHOUSE.WAREHOUSE_NAME FROM dbo.WAREHOUSE LEFT OUTER JOIN dbo.DM_ITEM ON dbo.WAREHOUSE.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.WAREHOUSE.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.WAREHOUSE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_WAREHOUSE ON dbo.WAREHOUSE.WAREHOUSE_ID = dbo.DM_WAREHOUSE.WAREHOUSE_ID GO DROP VIEW [WAREHOUSE_BALANCE_VIEW] GO CREATE VIEW [dbo].[WAREHOUSE_BALANCE_VIEW] AS SELECT dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.COST_METHOD, dbo.DM_ITEM.ITEM_CLASS_ID, 'DEB' AS DEBIT_CREDIT, dbo.DM_ITEM.ORIGIN, dbo.WAREHOUSE_BALANCE.PR_KEY, dbo.WAREHOUSE_BALANCE.TRAN_DATE, dbo.WAREHOUSE_BALANCE.ORGANIZATION_ID, dbo.WAREHOUSE_BALANCE.WAREHOUSE_ID, dbo.WAREHOUSE_BALANCE.ITEM_ID, dbo.WAREHOUSE_BALANCE.QUANTITY, dbo.WAREHOUSE_BALANCE.QUANTITY_EXTRA, dbo.WAREHOUSE_BALANCE.UNIT_PRICE, dbo.WAREHOUSE_BALANCE.AMOUNT, dbo.WAREHOUSE_BALANCE.AMOUNT_EXTRA, dbo.WAREHOUSE_BALANCE.USER_ID, dbo.WAREHOUSE_BALANCE.LOT_NO, dbo.WAREHOUSE_BALANCE.RECEIVE_DATE, dbo.WAREHOUSE_BALANCE.MANU_DATE, dbo.WAREHOUSE_BALANCE.EXPIRE_DATE, dbo.WAREHOUSE_BALANCE.EXPIRE_DATE AS EXPIRED_DATE, dbo.WAREHOUSE_BALANCE.ACCOUNT_ID, dbo.WAREHOUSE_BALANCE.ITEM_SOURCE_ID,WAREHOUSE_BALANCE.ITEM_STATUS_ID, dbo.WAREHOUSE_BALANCE.JOB_ID, dbo.DM_JOB.JOB_CLASS_ID,UNIT_ID_ACTUAL,DM_ITEM.ITEM_NAME,WAREHOUSE_BALANCE.ITEM_ID + WAREHOUSE_BALANCE.UNIT_ID_ACTUAL AS ITEM_COMBO_ID, WAREHOUSE_BALANCE.ITEM_HEIGHT,WAREHOUSE_BALANCE.ITEM_WIDTH,WAREHOUSE_BALANCE.ITEM_DEPTH,WAREHOUSE_BALANCE.ITEM_SIZE,WAREHOUSE_BALANCE.ITEM_LOCATION,WAREHOUSE_BALANCE.ITEM_SERI_NO,WAREHOUSE_BALANCE.ITEM_SERI_NO1,WAREHOUSE_BALANCE.ITEM_SERI_NO2,WAREHOUSE_BALANCE.ITEM_GRADE_ID,WAREHOUSE_BALANCE.ITEM_GRADE1_ID,DM_ITEM.ITEM_CLASS1_ID FROM dbo.WAREHOUSE_BALANCE LEFT OUTER JOIN dbo.DM_JOB ON dbo.WAREHOUSE_BALANCE.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.WAREHOUSE_BALANCE.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO -- 14/04 -- GO ALTER TABLE HR_SHIFT_PLAN ADD STATUS_IMPORT nvarchar(200) DEFAULT '' NOT NULL GO -- 15/04 -- GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL_SENT_TIMEOUT', '30000', N'Thời gian chờ tối đa khi gửi email', 'INT', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL_ENABLE_SSL', 1, N'Thiết lập mã hóa bằng SSL', 'BOOLEAN', 'HRM') GO ----------------------------------------04/16/2015---------------------------------------------------------- GO CREATE TABLE [dbo].[DM_EXPENSE_CONTENT]( [EXPENSE_CONTENT_ID] [nvarchar](20) NOT NULL, [EXPENSE_CONTENT_NAME] [nvarchar](100) NOT NULL, [EXPENSE_FORMALITY_ID] [nvarchar](20) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_EXPENSE_CONTENT] PRIMARY KEY CLUSTERED ( [EXPENSE_CONTENT_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DM_EXPENSE_FORMALITY]( [EXPENSE_FORMALITY_ID] [nvarchar](20) NOT NULL, [EXPENSE_FORMALITY_NAME] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_EXPENSE_FORMALITY] PRIMARY KEY CLUSTERED ( [EXPENSE_FORMALITY_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO dbo.SYS_MENU ( MENU_ID , PROJECT_ID , MODULE_ID , MENU_TYPE , MENU_GROUP , MENU_ICON , MENU_WIDTH , MENU_TAG , MENU_ORDER , ACTIVE ) VALUES ( N'FIN_OM_DM_EXPENSE_CONTENT' , N'FIN' , N'FIN_OM' , N'LIST' , N'' , N'' , 0 , N'DM_EXPENSE_CONTENT' , 31 , 1 ) GO INSERT INTO dbo.SYS_MENU ( MENU_ID , PROJECT_ID , MODULE_ID , MENU_TYPE , MENU_GROUP , MENU_ICON , MENU_WIDTH , MENU_TAG , MENU_ORDER , ACTIVE ) VALUES ( N'FIN_OM_DM_EXPENSE_FORMALITY' , N'FIN' , N'FIN_OM' , N'LIST' , N'' , N'' , 0 , N'DM_EXPENSE_FORMALITY' , 32 , 1 ) GO alter table SALE_COST_DETAIL add EXPENSE_CONTENT_ID NVARCHAR(20) NOT NULL DEFAULT '' GO ALTER TABLE [dbo].[SALE_COST_DETAIL] DROP CONSTRAINT [IX_SALE_COST_DETAIL]; CREATE UNIQUE NONCLUSTERED INDEX [IX_SALE_COST_DETAIL] ON [dbo].[SALE_COST_DETAIL] ( [FR_KEY] ASC, [ITEM_OP_ID] ASC, [EXPENSE_CONTENT_ID] ASC, [PR_DETAIL_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO INSERT INTO [dbo].[SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES( 'DM_EXPENSE_CONTENT', 'EXPENSE_CONTENT_ID', 'EXPENSE_CONTENT_NAME', 'LIST', 1, 1, 1, 1, 0, 0, 20, 1, '') GO INSERT INTO [dbo].[SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES( 'DM_EXPENSE_FORMALITY', 'EXPENSE_FORMALITY_ID', 'EXPENSE_FORMALITY_NAME', 'LIST', 1, 1, 1, 1, 0, 0, 20, 1, '') GO DROP VIEW [dbo].[SALE_COST_VIEW]; GO CREATE VIEW [dbo].[SALE_COST_VIEW] AS SELECT dbo.SALE_COST.PR_KEY AS PR_KEY_CTU, dbo.SALE_COST.ORGANIZATION_ID, dbo.SALE_COST.TRAN_ID, dbo.SALE_COST.TRAN_NO, dbo.SALE_COST.TRAN_DATE, dbo.SALE_COST.COMMENTS, dbo.SALE_COST.ACTIVE, dbo.SALE_COST.USER_ID, dbo.SALE_COST_DETAIL.PR_KEY, dbo.SALE_COST_DETAIL.FR_KEY, dbo.SALE_COST_DETAIL.LIST_ORDER, dbo.SALE_COST_DETAIL.PR_DETAIL_ID, dbo.SALE_COST_DETAIL.ITEM_OP_ID, dbo.SALE_COST_DETAIL.EXPENSE_CONTENT_ID, dbo.SALE_COST_DETAIL.AMOUNT_REVENUE, dbo.SALE_COST_DETAIL.AMOUNT_COST, dbo.DM_ITEM_OP.ITEM_OP_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.DM_EXPENSE_CONTENT.EXPENSE_CONTENT_NAME, dbo.DM_EXPENSE_CONTENT.EXPENSE_FORMALITY_ID FROM dbo.SALE_COST INNER JOIN dbo.SALE_COST_DETAIL ON dbo.SALE_COST.PR_KEY = dbo.SALE_COST_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM_OP ON dbo.SALE_COST_DETAIL.ITEM_OP_ID = dbo.DM_ITEM_OP.ITEM_OP_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE_COST_DETAIL.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_EXPENSE_CONTENT ON dbo.SALE_COST_DETAIL.EXPENSE_CONTENT_ID = dbo.DM_EXPENSE_CONTENT.EXPENSE_CONTENT_ID GO -- 17/04 -- GO ALTER TABLE DM_HR_LEAVE_REASON ADD [HR_REASON_TYPE] NVARCHAR(100) DEFAULT '' NOT NULL GO GO ALTER TABLE SALE_COST_DETAIL ADD COMMENTS NVARCHAR(200) NOT NULL DEFAULT '' GO DROP VIEW [dbo].[SALE_DRAFTPOST_VIEW] GO CREATE VIEW [dbo].[SALE_DRAFTPOST_VIEW] AS ( SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE_DETAIL.ITEM_SOURCE_ID, dbo.SALE.SHIPPING_METHOD_ID, dbo.SALE.VEHICLE_ID, dbo.SALE.TAX_OFFICE_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.RECEIVE_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.EXPORT_TAX_RATE, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.SALE.DRIVER_NAME, dbo.SALE.ORIG_TRAN_NO, dbo.SALE.ORIG_TRAN_ID, dbo.SALE.EMPLOYEE_ID,SALE.EXCHANGE_RATE_EXTRA, SALE_DETAIL.AMOUNT_EXTRA,SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA,SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA,SALE_DETAIL.SALE_COST_EXTRA, SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA,SALE_DETAIL.TOTAL_AMOUNT_EXTRA,SALE_DETAIL.EXPORT_TAX_AMOUNT_EXTRA,SALE_DETAIL.COG_AMOUNT_EXTRA, dbo.SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, SALE_DETAIL.ROOM_ID,DM_ITEM.ITEM_NAME AS ITEM_NAME, '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY, dbo.SALE_DETAIL.JOB_ID, sale_detail.ITEM_HEIGHT,sale_detail.ITEM_WIDTH,sale_detail.ITEM_DEPTH,sale_detail.ITEM_SIZE,sale_detail.ITEM_LOCATION,sale_detail.ITEM_SERI_NO, sale_detail.ITEM_SERI_NO1,sale_detail.ITEM_SERI_NO2,sale_detail.ITEM_GRADE_ID,sale_detail.ITEM_GRADE1_ID,SALE_DETAIL.ITEM_STATUS_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID ) GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcAllowanceTimeSheetByAmountLevel', N'CalcAllowanceTimeSheetByAmountLevel', N'Hàm tính phu cấp ngày công, chuyên cần theo ngày chênh lệch với ngày công chuẩn và kiểm tra điện kiện theo 1 loại hoặc nhiều loại công khác', 1, 1, N'ADMIN') GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcAllowanceTimeSheet', N'CalcAllowanceTimeSheet', N'Hàm tính phu cấp theo ngày công thực tế và dựa trên ngày công chuẩn, có kiểm tra điền kiện của một hay nhiều loại công khác', 1, 1, N'ADMIN') GO --07-05 GO ALTER TABLE PR_TIMESHEET ADD NOTES NVARCHAR(520) DEFAULT '' NOT NULL GO ALTER TABLE PR_SALARY_DETAIL ADD NOTES NVARCHAR(520) DEFAULT '' NOT NULL GO DROP VIEW LEDGER_PAYMENT GO CREATE VIEW [dbo].[LEDGER_PAYMENT] AS SELECT PR_KEY,PR_KEY_DETAIL,CASE WHEN A.AMOUNT_ORIG >= 0 THEN DEBIT_CREDIT ELSE CASE WHEN DEBIT_CREDIT='DEB' THEN 'CRD' ELSE 'DEB' END END AS DEBIT_CREDIT,TRAN_ID,TRAN_DATE,TRAN_NO,VAT_TRAN_NO,PAYMENT_DATE,PR_DETAIL_ID,ITEM_ID,PR_DETAIL_CLASS_ID,ITEM_CLASS_ID, ACCOUNT_ID,CURRENCY_ID,EXCHANGE_RATE,EXCHANGE_RATE_EXTRA,ORGANIZATION_ID,IS_BOOKED,DESCRIPTION,PAYMENT_TERM_ID,COMMENTS,JOB_ID,CAST(0 AS SMALLINT) AS IS_PRODUCT_COST, CASE WHEN AMOUNT_ORIG >= 0 THEN AMOUNT ELSE AMOUNT*-1 END AS AMOUNT ,CASE WHEN AMOUNT_ORIG >= 0 THEN AMOUNT_ORIG ELSE AMOUNT_ORIG*-1 END AS AMOUNT_ORIG,CASE WHEN AMOUNT_ORIG >= 0 THEN AMOUNT_EXTRA ELSE AMOUNT_EXTRA*-1 END AS AMOUNT_EXTRA FROM (SELECT PR_KEY,PR_KEY_DETAIL,DEBIT_CREDIT,TRAN_ID,TRAN_DATE,TRAN_NO,VAT_TRAN_NO,PAYMENT_DATE,LEDGER.PR_DETAIL_ID,LEDGER.ITEM_ID,DM_PR_DETAIL.PR_DETAIL_CLASS_ID,DM_ITEM.ITEM_CLASS_ID, LEDGER.ACCOUNT_ID,LEDGER.CURRENCY_ID,EXCHANGE_RATE,EXCHANGE_RATE_EXTRA,ORGANIZATION_ID,IS_BOOKED,LEDGER.DESCRIPTION,PAYMENT_TERM_ID,LEDGER.COMMENTS,JOB_ID,CAST(0 AS SMALLINT) AS IS_PRODUCT_COST, SUM(AMOUNT) AS AMOUNT,SUM(AMOUNT_ORIG) AS AMOUNT_ORIG,SUM(AMOUNT_EXTRA) AS AMOUNT_EXTRA FROM LEDGER left join DM_PR_DETAIL ON LEDGER.PR_DETAIL_ID=DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM ON LEDGER.ITEM_ID=DM_ITEM.ITEM_ID INNER JOIN DM_ACCOUNT ON LEDGER.ACCOUNT_ID = DM_ACCOUNT.ACCOUNT_ID WHERE DM_ACCOUNT.IS_PAYMENT=1 AND TRAN_ID <> 'CLTG' GROUP BY PR_KEY,PR_KEY_DETAIL,DEBIT_CREDIT,TRAN_ID,TRAN_DATE,TRAN_NO,VAT_TRAN_NO,PAYMENT_DATE,LEDGER.PR_DETAIL_ID,LEDGER.ITEM_ID,DM_PR_DETAIL.PR_DETAIL_CLASS_ID,DM_ITEM.ITEM_CLASS_ID, LEDGER.ACCOUNT_ID,LEDGER.CURRENCY_ID,EXCHANGE_RATE,EXCHANGE_RATE_EXTRA,ORGANIZATION_ID,IS_BOOKED,LEDGER.DESCRIPTION,PAYMENT_TERM_ID,LEDGER.COMMENTS,JOB_ID) A GO --20-05-2015 GO ALTER TABLE PR_SALARY ADD TOTAL_COST MONEY DEFAULT 0 NOT NULL GO ALTER TABLE PR_SALARY ADD TOTAL_COST_FC MONEY DEFAULT 0 NOT NULL GO -- 21/05 -- GO ALTER TABLE HR_EMPLOYEE_LEAVE ADD [LOT] NVARCHAR(20) DEFAULT 'LOT1' NOT NULL GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HR_EMPLOYEE_LEAVE]') AND name = N'PK_HR_EMPLOYEE_LEAVE') ALTER TABLE [dbo].[HR_EMPLOYEE_LEAVE] DROP CONSTRAINT [PK_HR_EMPLOYEE_LEAVE] GO ALTER TABLE [dbo].[HR_EMPLOYEE_LEAVE] ADD CONSTRAINT [PK_HR_EMPLOYEE_LEAVE] PRIMARY KEY CLUSTERED ( [LEAVE_DATE] DESC, [EMPLOYEE_ID] ASC, [LOT] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO --29-05 GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_IS_TIMESHEET_DETAIL', 1, N'Bảng chấm công tháng chi tiết theo từng gia đoạn', 'BOOLEAN', 'HRM') GO -- 01/06 -- GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HR_NUM_MONTH_DOWN_SABBATICAL', '0', N'Số tháng giữ phép trả sau', 'INT', 'HRM') GO -- 03/06 -- GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_EMPLOYEE_LEAVE_CHECK','HRM','HRM_INFO','LIST','HRMHRINFOGROUP3','',80,'HR_EMPLOYEE_LEAVE_CHECK',30,0) GO --05-06 GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcTimeSheetFormulaTotal', N'CalcTimeSheetFormulaTotal', N'-Phương thức tính toán ngày công không đi làm. hoặc ngày công chuẩn trừ đi tổng số ngày công theo công thức', 1, 0, N'ADMIN') GO --12-06 GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcTimeSheetShiftNightByPeriod', N'CalcTimeSheetShiftNightByPeriod', N'-Phương thức tính công đêm theo khung giờ', 1, 0, N'ADMIN') GO -- 14/06 -- GO ALTER TABLE HR_INS_SALARY ADD REMAIN_SI_PE_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD REMAIN_HI_PE_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD REMAIN_UI_PE_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD TOTAL_REMAIN_PE_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD REMAIN_SI_CO_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD REMAIN_HI_CO_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD REMAIN_UI_CO_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD TOTAL_REMAIN_CO_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE HR_INS_SALARY ADD TOTAL_REMAIN_AMOUNT money DEFAULT 0 NOT NULL GO ALTER TABLE SALE_COST_DETAIL ADD COMMENTS NVARCHAR(200) NOT NULL DEFAULT '' GO ----------------------------------------06/30/2015---------------------------------------------------------- GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('PRINT_ADJUSTMENT_BY_CONDITION', 1, N'In có đk biên bản kiểm kê', 'BOOLEAN', 'COMMON') GO ----------------------------------------07/06/2015---------------------------------------------------------- GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('ADJUSTMENT_AVR_PRICE', 1, N'Lay gia binh quan trong kiem ke', 'BOOLEAN', 'COMMON') GO -- 30/06 -- GO ALTER TABLE HR_TRAINING_EMPLOYEE ADD SOURCE_TYPE nvarchar(50) DEFAULT 'Moudle_Training' NOT NULL GO -- 01/07 -- GO CREATE TABLE [dbo].[DM_PRODUCTION_STEP_GROUP]( [PRODUCTION_STEP_GROUP_ID] [nvarchar](20) NOT NULL, [PRODUCTION_STEP_GROUP_NAME] [nvarchar](200) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_PRODUCTION_STEP_GROUP] PRIMARY KEY CLUSTERED ( [PRODUCTION_STEP_GROUP_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HRM_DM_PRODUCTION_STEP_GROUP','HRM','HRM_SA_ITEM','LIST','HRMPRITEMGROUP1','',100,'DM_PRODUCTION_STEP_GROUP',5,1) GO -- 03/07 -- GO ALTER TABLE PR_ITEM_STEP ADD PRODUCTION_STEP_GROUP_ID nvarchar(20) DEFAULT '' NOT NULL GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_PRODUCTION_STEP_GROUP' ,'PRODUCTION_STEP_GROUP_ID' ,'PRODUCTION_STEP_GROUP_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO CREATE TABLE [dbo].[PR_ITEM_PLAN_RATE]( [PR_KEY] [uniqueidentifier] NOT NULL, [ITEM_ID] [nvarchar](20) NOT NULL, [FROM_QTY] [money] NOT NULL, [TO_QTY] [money] NOT NULL, [RATE_ITEM] [money] NOT NULL, [EFFECTIVE_DATE] [smalldatetime] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, [DATE_MODIFIED] [smalldatetime] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_PR_ITEM_PLAN_RATE_1] PRIMARY KEY CLUSTERED ( [ITEM_ID] ASC, [FROM_QTY] ASC, [TO_QTY] ASC, [EFFECTIVE_DATE] ASC, [ORGANIZATION_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- 06/07 -- GO ALTER TABLE LOGGING ALTER Column DESCRIPTION nvarchar(600) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HRM_PR_ITEM_OUTPUT_2','HRM','HRM_SA_ITEM','FUNC','HRMPRITEMGROUP4','Contact.png',100,'PR_ITEM_OUTPUT_2',7,0) GO --03-07 GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcAllowanceTimeSheetByAmountRange', N'CalcAllowanceTimeSheetByAmountRange', N'Hàm tính phu cấp ngày công theo tỷ lệ ngày công đi làm, theo ngày chênh lệch với ngày công chuẩn và kiểm tra điện kiện theo 1 loại hoặc nhiều loại công khác', 1, 1, N'ADMIN') GO --07/07 GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcSalaryFormulaTotalByTimeSheet', N'CalcSalaryFormulaTotalByTimeSheet', N'Hàm tính lương với lương cơ bản bằng tổng công thức các loại lương trong tháng', 1, 1, N'ADMIN') GO UPDATE SYS_MENU SET MENU_TAG='AR_STATEMENT' WHERE MENU_ID='FIN_AR_ACCOUNTSUMMARY' GO UPDATE SYS_MENU SET MENU_TAG='AP_STATEMENT' WHERE MENU_ID='FIN_AP_ACCOUNTSUMMARY' GO UPDATE SYS_MENU SET MENU_TAG = 'AR_STATEMENT' WHERE MENU_ID = 'POS_SALE_ACCOUNTSUMMARY' GO -- 14/07 -- GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_SALARY_AUTO_UP_RATE', 0, N'Tỷ lệ tăng lương tự động khi gia hạn HĐ', 'INT', 'HRM') GO Drop view Purchase_all_view Go CREATE VIEW [dbo].[Purchase_all_view] AS ( SELECT dbo.PURCHASE.PR_KEY AS PR_KEY_CTU , dbo.PURCHASE.TRAN_ID , dbo.PURCHASE.TRAN_NO , dbo.PURCHASE.TRAN_DATE , dbo.PURCHASE.REFERENCE_NO , dbo.PURCHASE.PO_TRAN_NO , dbo.PURCHASE.PO_TRAN_ID , dbo.PURCHASE.VAT_TRAN_NO , dbo.PURCHASE.VAT_TRAN_DATE , dbo.PURCHASE.VAT_TRAN_SERIE , dbo.PURCHASE.VAT_PURCHASE_ID , dbo.PURCHASE.WAREHOUSE_ID_ISSUE , dbo.PURCHASE.MARKET_ID , dbo.PURCHASE.EXCHANGE_RATE , dbo.PURCHASE.CURRENCY_ID , dbo.PURCHASE.PR_DETAIL_ID , dbo.PURCHASE.EMPLOYEE_ID , dbo.PURCHASE.PR_DETAIL_NAME , dbo.PURCHASE.CONTACT_PERSON , dbo.PURCHASE.ADDRESS , dbo.PURCHASE.TAX_FILE_NUMBER , dbo.PURCHASE.PAYMENT_METHOD_ID , dbo.PURCHASE.COMMENTS , dbo.PURCHASE.PAYMENT_TERM_ID , dbo.PURCHASE.PAYMENT_DATE , dbo.PURCHASE_DETAIL.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.PURCHASE.ORGANIZATION_ID , dbo.PURCHASE.ACCOUNT_ID_PR , dbo.PURCHASE.ACCOUNT_ID_VAT , PURCHASE_DETAIL.ACCOUNT_ID_COST, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.PURCHASE_DETAIL.PR_KEY , dbo.PURCHASE_DETAIL.FR_KEY , dbo.PURCHASE_DETAIL.LIST_ORDER , dbo.PURCHASE_DETAIL.LOT_NO , dbo.PURCHASE_DETAIL.MANU_DATE , dbo.PURCHASE_DETAIL.EXPIRED_TERM , dbo.PURCHASE_DETAIL.EXPIRED_DATE , dbo.PURCHASE_DETAIL.ITEM_ID , dbo.PURCHASE_DETAIL.DESCRIPTION , dbo.PURCHASE_DETAIL.DESCRIPTION_ULS , dbo.PURCHASE_DETAIL.UNIT_ID , dbo.PURCHASE_DETAIL.QUANTITY , dbo.PURCHASE_DETAIL.QUANTITY_EXTRA , dbo.PURCHASE_DETAIL.QUANTITY_WH , dbo.PURCHASE_DETAIL.UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.UNIT_PRICE , dbo.PURCHASE_DETAIL.UNIT_PRICE_WH , dbo.PURCHASE_DETAIL.AMOUNT_ORIG , dbo.PURCHASE_DETAIL.AMOUNT , dbo.PURCHASE_DETAIL.AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_ID , dbo.PURCHASE_DETAIL.VAT_TAX_RATE , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_RATE , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.PURCHASE_COST , dbo.PURCHASE_DETAIL.PURCHASE_COST_ORIG , dbo.PURCHASE_DETAIL.PURCHASE_COST_EXTRA , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE , dbo.PURCHASE_DETAIL.COG_AMOUNT , dbo.PURCHASE_DETAIL.COG_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.DISCOUNT_RATE , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_RATE , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.ITEM_OP_ID , dbo.PURCHASE_DETAIL.WAREHOUSE_ID , dbo.PURCHASE_DETAIL.PRICE_LEVEL_ID , dbo.DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.PURCHASE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.PURCHASE_DETAIL.QUANTITY_EXPECTED , dbo.PURCHASE_DETAIL.FIXED_UNIT_PRICE , dbo.PURCHASE_DETAIL.FIXED_AMOUNT , dbo.PURCHASE_DETAIL.QUANTITY_BILL , dbo.PURCHASE_DETAIL.QUANTITY_MEASURE , PURCHASE.EXCHANGE_RATE_EXTRA , PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , PURCHASE.VEHICLE_ID , '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_RATE , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.INSURANCE_RATE , PURCHASE_DETAIL.EMPLOYEE_COMMISSION , PURCHASE_DETAIL.EMPLOYEE_COMMISSION_ORIG , PURCHASE_DETAIL.EMPLOYEE_COMMISSION_RATE, PURCHASE_DETAIL.ACCOUNT_ID_COST AS ACCOUNT_ID, dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM,DM_PR_DETAIL_ITEM.ADDRESS AS ADDRESS_ITEM,DM_PR_DETAIL_ITEM.TAX_FILE_NUMBER AS TAX_FILE_NUMBER_ITEM, DM_PR_DETAIL_ITEM.PR_DETAIL_NAME AS PR_DETAIL_NAME_ITEM, PURCHASE_DETAIL.ITEM_HEIGHT,PURCHASE_DETAIL.ITEM_WIDTH,PURCHASE_DETAIL.ITEM_DEPTH,PURCHASE_DETAIL.ITEM_SIZE,PURCHASE_DETAIL.ITEM_LOCATION,PURCHASE_DETAIL.ITEM_SERI_NO,PURCHASE_DETAIL.ITEM_SERI_NO1,PURCHASE_DETAIL.ITEM_SERI_NO2,PURCHASE_DETAIL.ITEM_GRADE_ID,PURCHASE_DETAIL.ITEM_GRADE1_ID,PURCHASE_DETAIL.ITEM_STATUS_ID,PURCHASE_DETAIL.JOB_ID FROM dbo.PURCHASE INNER JOIN dbo.PURCHASE_DETAIL ON dbo.PURCHASE.PR_KEY = dbo.PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL AS DM_PR_DETAIL_ITEM ON dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM = DM_PR_DETAIL_ITEM.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON PURCHASE.TRAN_ID = SYS_TRAN.TRAN_ID WHERE PURCHASE.STATUS='POSTED' ) UNION ALL ( SELECT dbo.POS_PURCHASE.PR_KEY AS PR_KEY_CTU , dbo.POS_PURCHASE.TRAN_ID , dbo.POS_PURCHASE.TRAN_NO , dbo.POS_PURCHASE.TRAN_DATE , '' AS REFERENCE_NO , '' AS PO_TRAN_NO , '' AS PO_TRAN_ID , dbo.POS_PURCHASE.VAT_TRAN_NO , dbo.POS_PURCHASE.VAT_TRAN_DATE , dbo.POS_PURCHASE.VAT_TRAN_SERIE , '' AS VAT_POS_PURCHASE_ID , dbo.POS_PURCHASE.WAREHOUSE_ID_ISSUE , '' AS MARKET_ID , dbo.POS_PURCHASE.EXCHANGE_RATE , dbo.POS_PURCHASE.CURRENCY_ID , dbo.POS_PURCHASE.PR_DETAIL_ID , dbo.POS_PURCHASE.EMPLOYEE_ID , dbo.POS_PURCHASE.PR_DETAIL_NAME , dbo.POS_PURCHASE.CONTACT_PERSON , dbo.POS_PURCHASE.ADDRESS , dbo.POS_PURCHASE.TAX_FILE_NUMBER , dbo.POS_PURCHASE.PAYMENT_METHOD_ID , dbo.POS_PURCHASE.COMMENTS , dbo.POS_PURCHASE.PAYMENT_TERM_ID , dbo.POS_PURCHASE.PAYMENT_DATE , dbo.POS_PURCHASE.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.POS_PURCHASE.ORGANIZATION_ID , '' AS ACCOUNT_ID_PR, '' AS ACCOUNT_ID_VAT, '' AS ACCOUNT_ID_COST, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.POS_PURCHASE_DETAIL.PR_KEY , dbo.POS_PURCHASE_DETAIL.FR_KEY , dbo.POS_PURCHASE_DETAIL.LIST_ORDER , dbo.POS_PURCHASE_DETAIL.LOT_NO , dbo.POS_PURCHASE_DETAIL.MANU_DATE , dbo.POS_PURCHASE_DETAIL.EXPIRED_TERM , dbo.POS_PURCHASE_DETAIL.EXPIRED_DATE , dbo.POS_PURCHASE_DETAIL.ITEM_ID , dbo.POS_PURCHASE_DETAIL.DESCRIPTION , '' AS DESCRIPTION_ULS , dbo.POS_PURCHASE_DETAIL.UNIT_ID , dbo.POS_PURCHASE_DETAIL.QUANTITY , dbo.POS_PURCHASE_DETAIL.QUANTITY_EXTRA , dbo.POS_PURCHASE_DETAIL.QUANTITY_WH , dbo.POS_PURCHASE_DETAIL.UNIT_PRICE_ORIG , dbo.POS_PURCHASE_DETAIL.UNIT_PRICE , dbo.POS_PURCHASE_DETAIL.UNIT_PRICE_WH , dbo.POS_PURCHASE_DETAIL.AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT , dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT * 0 AS VAT_INCOME_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.VAT_TAX_ID , dbo.POS_PURCHASE_DETAIL.VAT_TAX_RATE , dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT , dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT * 0 AS VAT_TAX_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_RATE , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST_EXTRA , dbo.POS_PURCHASE_DETAIL.COG_UNIT_PRICE , dbo.POS_PURCHASE_DETAIL.COG_AMOUNT , dbo.POS_PURCHASE_DETAIL.COG_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.COG_AMOUNT_ORIG * 0 AS COG_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.DISCOUNT_RATE , dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT , dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG * 0 AS DISCOUNT_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT , dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT * 0 AS TOTAL_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.POS_PURCHASE_DETAIL.LUX_TAX_RATE , dbo.POS_PURCHASE_DETAIL.LUX_TAX_AMOUNT , dbo.POS_PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS LUX_TAX_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.ITEM_OP_ID , dbo.POS_PURCHASE_DETAIL.WAREHOUSE_ID , dbo.POS_PURCHASE_DETAIL.PRICE_LEVEL_ID , DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.POS_PURCHASE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED , dbo.POS_PURCHASE_DETAIL.FIXED_UNIT_PRICE , dbo.POS_PURCHASE_DETAIL.FIXED_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_BILL , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_MEASURE , 1 AS EXCHANGE_RATE_EXTRA , POS_PURCHASE_DETAIL.ITEM_ID + POS_PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , '' AS VEHICLE_ID , POS_SHIFT_PR_KEY , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_RATE , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS INSURANCE_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS INSURANCE_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS INSURANCE_RATE , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS EMPLOYEE_COMMISSION , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS EMPLOYEE_COMMISSION_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS EMPLOYEE_COMMISSION_RATE, '' AS ACCOUNT_ID, '' AS PR_DETAIL_ID_ITEM,'' AS ADDRESS_ITEM,'' AS TAX_FILE_NUMBER_ITEM, '' AS PR_DETAIL_NAME_ITEM, CAST(0 AS MONEY) AS ITEM_HEIGHT,CAST(0 AS MONEY) AS ITEM_WIDTH,CAST(0 AS MONEY) AS ITEM_DEPTH,CAST(0 AS MONEY) AS ITEM_SIZE,'' as ITEM_LOCATION,'' as ITEM_SERI_NO,'' as ITEM_SERI_NO1,'' as ITEM_SERI_NO2,'' as ITEM_GRADE_ID,'' as ITEM_GRADE1_ID,'' as ITEM_STATUS_ID,'' AS JOB_ID FROM dbo.POS_PURCHASE INNER JOIN dbo.POS_PURCHASE_DETAIL ON dbo.POS_PURCHASE.PR_KEY = dbo.POS_PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON POS_PURCHASE_DETAIL.ITEM_ID + POS_PURCHASE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON POS_PURCHASE.TRAN_ID = SYS_TRAN.TRAN_ID WHERE POS_PURCHASE.STATUS='POSTED' ) UNION ALL ( SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU , dbo.SALE.TRAN_ID , dbo.SALE.TRAN_NO , dbo.SALE.TRAN_DATE , dbo.SALE.REFERENCE_NO , '' AS PO_TRAN_NO , '' AS PO_TRAN_ID , dbo.SALE.VAT_TRAN_NO , dbo.SALE.VAT_TRAN_DATE , dbo.SALE.VAT_TRAN_SERIE , dbo.SALE.VAT_PURCHASE_ID , '' AS WAREHOUSE_ID_ISSUE , dbo.SALE.MARKET_ID , dbo.SALE.EXCHANGE_RATE , dbo.SALE.CURRENCY_ID , dbo.SALE.PR_DETAIL_ID , dbo.SALE.EMPLOYEE_ID , dbo.SALE.PR_DETAIL_NAME , dbo.SALE.CONTACT_PERSON , dbo.SALE.ADDRESS , dbo.SALE.TAX_FILE_NUMBER , dbo.SALE.PAYMENT_METHOD_ID , dbo.SALE.COMMENTS , dbo.SALE.PAYMENT_TERM_ID , dbo.SALE.PAYMENT_DATE , dbo.SALE_DETAIL.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.SALE.ORGANIZATION_ID , dbo.SALE.ACCOUNT_ID_PR , dbo.SALE.ACCOUNT_ID_VAT , SALE_DETAIL.ACCOUNT_ID_COST, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.SALE_DETAIL.PR_KEY , dbo.SALE_DETAIL.FR_KEY , dbo.SALE_DETAIL.LIST_ORDER , dbo.SALE_DETAIL.LOT_NO , dbo.SALE_DETAIL.MANU_DATE , dbo.SALE_DETAIL.EXPIRED_TERM , dbo.SALE_DETAIL.EXPIRED_DATE , dbo.SALE_DETAIL.ITEM_ID , dbo.SALE_DETAIL.DESCRIPTION , dbo.SALE_DETAIL.DESCRIPTION_ULS , dbo.SALE_DETAIL.UNIT_ID , dbo.SALE_DETAIL.QUANTITY , dbo.SALE_DETAIL.QUANTITY_EXTRA , dbo.SALE_DETAIL.QUANTITY_WH , dbo.SALE_DETAIL.UNIT_PRICE_ORIG , dbo.SALE_DETAIL.UNIT_PRICE , dbo.SALE_DETAIL.UNIT_PRICE_WH , dbo.SALE_DETAIL.AMOUNT_ORIG , dbo.SALE_DETAIL.AMOUNT , dbo.SALE_DETAIL.AMOUNT_EXTRA , dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.SALE_DETAIL.VAT_INCOME_AMOUNT , dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_EXTRA , dbo.SALE_DETAIL.VAT_TAX_ID , dbo.SALE_DETAIL.VAT_TAX_RATE , dbo.SALE_DETAIL.VAT_TAX_AMOUNT , dbo.SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA , dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG , 0 AS IMPORT_TAX_RATE , 0 AS IMPORT_TAX_AMOUNT , 0 AS IMPORT_TAX_AMOUNT_ORIG , 0 AS IMPORT_TAX_AMOUNT_EXTRA , dbo.SALE_DETAIL.SALE_COST , dbo.SALE_DETAIL.SALE_COST_ORIG , dbo.SALE_DETAIL.SALE_COST_EXTRA , dbo.SALE_DETAIL.COG_UNIT_PRICE , dbo.SALE_DETAIL.COG_AMOUNT , dbo.SALE_DETAIL.COG_AMOUNT_ORIG , dbo.SALE_DETAIL.COG_AMOUNT_EXTRA , dbo.SALE_DETAIL.DISCOUNT_RATE , dbo.SALE_DETAIL.DISCOUNT_AMOUNT , dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA , dbo.SALE_DETAIL.TOTAL_AMOUNT , dbo.SALE_DETAIL.TOTAL_AMOUNT_EXTRA , dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.SALE_DETAIL.LUX_TAX_RATE , dbo.SALE_DETAIL.LUX_TAX_AMOUNT , dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA , dbo.SALE_DETAIL.ITEM_OP_ID , dbo.SALE_DETAIL.WAREHOUSE_ID , dbo.SALE_DETAIL.PRICE_LEVEL_ID , DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.SALE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.SALE_DETAIL.QUANTITY_EXPECTED , dbo.SALE_DETAIL.FIXED_UNIT_PRICE , dbo.SALE_DETAIL.FIXED_AMOUNT , 0 AS QUANTITY_BILL , 0 AS QUANTITY_MEASURE , SALE.EXCHANGE_RATE_EXTRA , SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , SALE.VEHICLE_ID , '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY , dbo.SALE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_AMOUNT , dbo.SALE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_AMOUNT_EXTRA , dbo.SALE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_RATE , dbo.SALE_DETAIL.AMOUNT * 0 AS INSURANCE_AMOUNT , dbo.SALE_DETAIL.AMOUNT * 0 AS INSURANCE_AMOUNT_EXTRA , dbo.SALE_DETAIL.AMOUNT * 0 AS INSURANCE_RATE , dbo.SALE_DETAIL.EMPLOYEE_COMMISSION , dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG , dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.SALE_DETAIL.ACCOUNT_ID_COST AS ACCOUNT_ID, dbo.SALE_DETAIL.PR_DETAIL_ID_ITEM,DM_PR_DETAIL_ITEM.ADDRESS AS ADDRESS_ITEM,DM_PR_DETAIL_ITEM.TAX_FILE_NUMBER AS TAX_FILE_NUMBER_ITEM,DM_PR_DETAIL_ITEM.PR_DETAIL_NAME AS PR_DETAIL_NAME_ITEM, sale_detail.ITEM_HEIGHT,sale_detail.ITEM_WIDTH,sale_detail.ITEM_DEPTH,sale_detail.ITEM_SIZE,sale_detail.ITEM_LOCATION,sale_detail.ITEM_SERI_NO,sale_detail.ITEM_SERI_NO1,sale_detail.ITEM_SERI_NO2,sale_detail.ITEM_GRADE_ID,sale_detail.ITEM_GRADE1_ID,SALE_DETAIL.ITEM_STATUS_ID,SALE_DETAIL.JOB_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL AS DM_PR_DETAIL_ITEM ON dbo.SALE_DETAIL.PR_DETAIL_ID_ITEM = DM_PR_DETAIL_ITEM.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON SALE.TRAN_ID = SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON SALE_DETAIL.ITEM_OP_ID = DM_ITEM_OP.ITEM_OP_ID WHERE DM_ITEM_OP.OP_TYPE = 'N' AND SALE.STATUS='POSTED' ) UNION ALL ( SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU , dbo.POS_SALE.TRAN_ID , dbo.POS_SALE.TRAN_NO , dbo.POS_SALE.TRAN_DATE , '' AS REFERENCE_NO , '' AS PO_TRAN_NO , '' AS PO_TRAN_ID , dbo.POS_SALE.VAT_TRAN_NO , dbo.POS_SALE.VAT_TRAN_DATE , dbo.POS_SALE.VAT_TRAN_SERIE , '' AS VAT_POS_SALE_ID , '' AS WAREHOUSE_ID_ISSUE , '' AS MARKET_ID , dbo.POS_SALE.EXCHANGE_RATE , dbo.POS_SALE.CURRENCY_ID , dbo.POS_SALE.PR_DETAIL_ID , dbo.POS_SALE.EMPLOYEE_ID , dbo.POS_SALE.PR_DETAIL_NAME , dbo.POS_SALE.CONTACT_PERSON , dbo.POS_SALE.ADDRESS , dbo.POS_SALE.TAX_FILE_NUMBER , dbo.POS_SALE.PAYMENT_METHOD_ID , dbo.POS_SALE.COMMENTS , dbo.POS_SALE.PAYMENT_TERM_ID , dbo.POS_SALE.PAYMENT_DATE , dbo.POS_SALE.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.POS_SALE.ORGANIZATION_ID , '' AS ACCOUNT_ID_PR, '' AS ACCOUNT_ID_VAT, '' AS ACCOUNT_ID_COST, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.POS_SALE_DETAIL.PR_KEY , dbo.POS_SALE_DETAIL.FR_KEY , dbo.POS_SALE_DETAIL.LIST_ORDER , dbo.POS_SALE_DETAIL.LOT_NO , dbo.POS_SALE_DETAIL.MANU_DATE , dbo.POS_SALE_DETAIL.EXPIRED_TERM , dbo.POS_SALE_DETAIL.EXPIRED_DATE , dbo.POS_SALE_DETAIL.ITEM_ID , dbo.POS_SALE_DETAIL.DESCRIPTION , '' AS DESCRIPTION_ULS , dbo.POS_SALE_DETAIL.UNIT_ID , dbo.POS_SALE_DETAIL.QUANTITY , dbo.POS_SALE_DETAIL.QUANTITY_EXTRA , dbo.POS_SALE_DETAIL.QUANTITY_WH , dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG , dbo.POS_SALE_DETAIL.UNIT_PRICE , dbo.POS_SALE_DETAIL.UNIT_PRICE_WH , dbo.POS_SALE_DETAIL.AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT , dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT * 0 AS VAT_INCOME_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.VAT_TAX_ID , dbo.POS_SALE_DETAIL.VAT_TAX_RATE , dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT , dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT * 0 AS VAT_TAX_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_RATE , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST_EXTRA , 0 AS COG_UNIT_PRICE , 0 AS COG_AMOUNT , 0 AS COG_AMOUNT_ORIG , 0 AS COG_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.DISCOUNT_RATE , dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT , dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT * 0 AS DISCOUNT_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.TOTAL_AMOUNT , dbo.POS_SALE_DETAIL.TOTAL_AMOUNT * 0 AS TOTAL_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG , 0 AS COG_UNIT_PRICE_ORIG , dbo.POS_SALE_DETAIL.LUX_TAX_RATE , dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT , dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS LUX_TAX_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.ITEM_OP_ID , dbo.POS_SALE_DETAIL.WAREHOUSE_ID , dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID , DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.POS_SALE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED , dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE , dbo.POS_SALE_DETAIL.FIXED_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_BILL , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_MEASURE , 1 AS EXCHANGE_RATE_EXTRA , POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , '' AS VEHICLE_ID , POS_SHIFT_PR_KEY , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS TRANSPORT_FEE_RATE , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS INSURANCE_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS INSURANCE_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS INSURANCE_RATE , dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION , dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG , dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, '' AS ACCOUNT_ID, '' as PR_DETAIL_ID_ITEM,'' AS ADDRESS_ITEM,'' AS TAX_FILE_NUMBER_ITEM, '' AS PR_DETAIL_NAME_ITEM, CAST(0 AS MONEY) AS ITEM_HEIGHT,CAST(0 AS MONEY) AS ITEM_WIDTH,CAST(0 AS MONEY) AS ITEM_DEPTH,CAST(0 AS MONEY) AS ITEM_SIZE,'' as ITEM_LOCATION,'' as ITEM_SERI_NO,'' as ITEM_SERI_NO1,'' as ITEM_SERI_NO2,'' as ITEM_GRADE_ID,'' as ITEM_GRADE1_ID,'' as ITEM_STATUS_ID,POS_SALE_DETAIL.JOB_ID FROM dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON POS_SALE.TRAN_ID = SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON POS_SALE_DETAIL.ITEM_OP_ID = DM_ITEM_OP.ITEM_OP_ID WHERE DM_ITEM_OP.OP_TYPE = 'N' ) GO DROP VIEW SALE_ALL_VIEW GO CREATE VIEW [dbo].[SALE_ALL_VIEW] AS SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.ACCOUNT_ID_PR, dbo.SALE.ACCOUNT_ID_VAT, dbo.SALE.ACCOUNT_ID_EXPORT_TAX, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.EXCHANGE_RATE_EXTRA, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.STATUS, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.SALE.IMPLEMENTED_EMPLOYEE_ID, dbo.SALE.SALE_DATE, dbo.SALE.IMPLEMENTED_DATE, dbo.DM_ITEM.ORIGIN, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE.SHIPPING_METHOD_ID, dbo.SALE.VEHICLE_ID, dbo.SALE.TAX_OFFICE_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.RECEIVE_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.DESCRIPTION_ULS, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.EXPORT_TAX_RATE, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.ACCOUNT_ID, dbo.SALE_DETAIL.ACCOUNT_ID_COST, dbo.SALE_DETAIL.ACCOUNT_ID_INCOME, dbo.SALE_DETAIL.PR_DETAIL_ID_ITEM, dbo.SALE_DETAIL.EXPENSE_ID, dbo.SALE_DETAIL.JOB_ID, dbo.SALE_DETAIL.AMOUNT_EXTRA, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_EXTRA, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.COG_AMOUNT_EXTRA, dbo.SALE_DETAIL.SALE_COST_EXTRA, dbo.SALE_DETAIL.TOTAL_AMOUNT_EXTRA, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.JOB_QTY, dbo.SALE_DETAIL.BILL_NO, dbo.SALE_DETAIL.BILL_AMOUNT, dbo.SALE_DETAIL.BILL_VAT_TAX_AMOUNT, dbo.SALE_DETAIL.OPENNING_QUANTITY, dbo.SALE_DETAIL.CLOSING_QUANTITY, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_EXTRA, dbo.SALE_DETAIL.EMPLOYEE_ID, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, dbo.SALE_DETAIL.ROOM_ID, dbo.SALE_DETAIL.ITEM_SOURCE_ID, dbo.SALE_DETAIL.PR_KEY_HT_FOLIO, dbo.SALE_DETAIL.QUANTITY_BILL, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.SALE_DETAIL.ITEM_ID + dbo.SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, dbo.SALE.ORIG_TRAN_NO, dbo.DM_WAREHOUSE.WAREHOUSE_CLASS_ID, dbo.SALE.ORIG_VAT_TRAN_DATE, dbo.SALE.ORIG_VAT_TRAN_NO, dbo.SALE.PR_KEY_POS, sale_detail.ITEM_HEIGHT,sale_detail.ITEM_WIDTH,sale_detail.ITEM_DEPTH,sale_detail.ITEM_SIZE,sale_detail.ITEM_LOCATION,sale_detail.ITEM_SERI_NO,sale_detail.ITEM_SERI_NO1,sale_detail.ITEM_SERI_NO2,sale_detail.ITEM_GRADE_ID,sale_detail.ITEM_GRADE1_ID,SALE_DETAIL.ITEM_STATUS_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SALE_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.HR_EMPLOYEE_INFO ON dbo.SALE_DETAIL.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID LEFT OUTER JOIN dbo.DM_WAREHOUSE ON dbo.DM_WAREHOUSE.WAREHOUSE_ID = dbo.SALE.WAREHOUSE_ID AND SALE.STATUS='POSTED' GO -- 15/07 -- GO CREATE TABLE [dbo].[DM_EVALUATION_PROBATION]( [EVALUATION_PROBATION_ID] [nvarchar](20) NOT NULL, [EVALUATION_PROBATION_NAME] [nvarchar](200) NOT NULL, [COMMENTS] [nvarchar](500) NOT NULL, [PERCENTAGE] [money] NOT NULL, [PROPORTION] [money] NOT NULL, [USER_ID] [nvarchar](20) NULL, [ACTIVE] [smallint] NULL, CONSTRAINT [PK_DM_EVALUATION_PROBATION] PRIMARY KEY CLUSTERED ( [EVALUATION_PROBATION_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO alter table DM_HR_EVA_CONVERT_RESULT add POINT money DEFAULT 0 NOT NULL GO CREATE TABLE [HR_PROBATION_EVALUATION]( [PR_KEY] [uniqueidentifier] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [EVALUATION_PROBATION_ID] [nvarchar](20) NOT NULL, [PROPORTION] [money] NOT NULL, [RESULT_ID] [nvarchar](20) NOT NULL, [POINT] [money] NOT NULL, [POINT_PROPORTION] [money] NOT NULL, [COMMENT] [nvarchar](200) NOT NULL, [DATE_MODIFIED] [smalldatetime] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, PRIMARY KEY ( [PR_KEY] )) GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_EVALUATION_PROBATION' ,'EVALUATION_PROBATION_ID' ,'EVALUATION_PROBATION_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_DM_EVALUATION_PROBATION','HRM','HRM_INFO','LIST','','',80,'DM_EVALUATION_PROBATION',20,0) GO -- 16/07 -- GO alter table HR_EMPLOYEE_CONTRACT add REVIEW_RESULT nvarchar(50) DEFAULT '' NOT NULL GO -- 20/07 -- GO CREATE TABLE [dbo].[DM_GROUP_REWARDS_FAULTS]( [GROUP_REWARDS_FAULTS_ID] [nvarchar](20) NOT NULL, [GROUP_REWARDS_FAULTS_NAME] [nvarchar](200) NOT NULL, [COMMENTS] [nvarchar](200) NOT NULL, [USER_ID] [nvarchar](20) NULL, [ACTIVE] [smallint] NULL, CONSTRAINT [PK_DM_GROUP_REWARDS_FAULTS] PRIMARY KEY CLUSTERED ( [GROUP_REWARDS_FAULTS_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO alter table HR_EMPLOYEE_REWARDS_FAULTS add GROUP_REWARDS_FAULTS_ID nvarchar(20) DEFAULT '' NOT NULL GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HRM_DM_GROUP_REWARDS_FAULTS','HRM','HRM_INFO','LIST','HRMADMINGROUP1','',0,'DM_GROUP_REWARDS_FAULTS',31,1) GO alter table DM_GROUP_REWARDS_FAULTS add TYPE_RF nvarchar(20) DEFAULT '' NOT NULL GO --22/07/2015 GO alter table HR_EMPLOYEE_REWARDS_FAULTS add COMMENT1 nvarchar(500) DEFAULT '' NOT NULL GO alter table HR_EMPLOYEE_REWARDS_FAULTS add COMMENT2 nvarchar(500) DEFAULT '' NOT NULL GO alter table HR_EMPLOYEE_REWARDS_FAULTS add COMMENT3 nvarchar(500) DEFAULT '' NOT NULL GO alter table HR_EMPLOYEE_REWARDS_FAULTS add COMMENT4 nvarchar(500) DEFAULT '' NOT NULL GO alter table HR_EMPLOYEE_REWARDS_FAULTS add COMMENT5 nvarchar(500) DEFAULT '' NOT NULL GO alter table HR_EMPLOYEE_REWARDS_FAULTS add EMPLOYEE_ID_MAKE_RECORD nvarchar(20) DEFAULT '' NOT NULL GO alter table HR_EMPLOYEE_REWARDS_FAULTS add FORM_REWARDS_FAULTS_ID nvarchar(20) DEFAULT '' NOT NULL GO CREATE TABLE [dbo].[DM_FORM_REWARDS_FAULTS]( [FORM_REWARDS_FAULTS_ID] [nvarchar](20) NOT NULL, [FORM_REWARDS_FAULTS_NAME] [nvarchar](100) NOT NULL, [TYPE_RF] [nvarchar](20) NOT NULL, [COMMENT] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_FORM_REWARDS_FAULTS] PRIMARY KEY CLUSTERED ( [FORM_REWARDS_FAULTS_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --31/07/2015 GO ALTER TABLE HR_EMPLOYEE_HEALTH ADD DATE_END smalldatetime DEFAULT getdate() NOT NULL GO -- 05/08 -- GO ALTER TABLE [DM_TIMESHEET_YEAR_SABBATICAL] ADD SABBATICAL_LIQUIDATION money DEFAULT 0 NOT NULL GO -- 06/08 -- GO CREATE TABLE [DM_HR_FIELD_NOT_EMPTY]( [PR_KEY] [uniqueidentifier] NOT NULL, [TABLE_NAME] [nvarchar](100) NOT NULL, [FIELD_ID] [nvarchar](100) NOT NULL, [IS_NOT_EMPTY] [smallint] NOT NULL, [COMMENT] [nvarchar](200) NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, PRIMARY KEY ( [PR_KEY] )) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_DM_HR_FIELD_NOT_EMPTY','HRM','HRM_ADMI','LIST','','',80,'DM_HR_FIELD_NOT_EMPTY',50,0) GO -- 07/08 -- GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('PR_ITEM_PRICE_STEP' ,'PRODUCTION_STEP_ID' ,'PRODUCTION_STEP_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO -- 10/08 -- GO alter table DM_HR_FIELD_NOT_EMPTY add DATA_TYPE nvarchar(50) DEFAULT '' NOT NULL GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_HR_TABLE_NOT_EMPTY' ,'TABLE_ID' ,'TABLE_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'100' ,'1' ,'') GO -- 10/08 -- GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[DM_HR_TABLE_NOT_EMPTY]')) DROP VIEW [dbo].[DM_HR_TABLE_NOT_EMPTY] GO CREATE VIEW [dbo].[DM_HR_TABLE_NOT_EMPTY] AS SELECT distinct TABLE_NAME as TABLE_ID, ISNULL( SYS_RESOURCE.RES_VALUE,TABLE_NAME )TABLE_NAME FROM DM_HR_FIELD_NOT_EMPTY left join (select * from SYS_RESOURCE where RES_ID like 'TABLE_NAME_%') SYS_RESOURCE on 'TABLE_NAME_'+DM_HR_FIELD_NOT_EMPTY.TABLE_NAME = SYS_RESOURCE.RES_ID WHERE IS_NOT_EMPTY = 1 GO ALTER TABLE [dbo].[DM_FA_CLASS] ADD [FA_ACCOUNT_ID] [nvarchar](20) NOT NULL DEFAULT '' GO ALTER TABLE [dbo].[DM_FA_CLASS] ADD [DEP_ACCOUNT_ID] [nvarchar](20) NOT NULL DEFAULT '' GO ALTER TABLE [dbo].[DM_FA_CLASS] ADD [EXPENSE_ACCOUNT_ID] [nvarchar](20) NOT NULL DEFAULT '' GO -----------------------17/08/2015------------------- GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_HEALTH_CRITERIA' ,'CRITERIA_ID' ,'CRITERIA_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO GO CREATE TABLE [dbo].[DM_UNIFORM]( [UNIFORM_ID] [nvarchar](20) NOT NULL, [UNIFORM_NAME] [nvarchar](100) NOT NULL, [UNIFORM_NAME_SHORT] [nvarchar](50) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [TIME_EXPIRED] [int] NOT NULL, [UNIT_PRICE] [money] NOT NULL, [COST_ASSISTANCE] [money] NOT NULL, [MONEY_DEPOSIT] [money] NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_UNIFORM] PRIMARY KEY CLUSTERED ( [UNIFORM_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ---------Danh muc chuc danh + dong phuc GO CREATE TABLE [dbo].[DM_UNIFORM_POSITION]( [PR_KEY] [uniqueidentifier] NOT NULL, [EFFECTIVE_DATE] [smalldatetime] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [POSITION_ID] [nvarchar](20) NOT NULL, [UNIFORM_ID] [nvarchar](20) NOT NULL, [QUANTITY] [int] NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_UNIFORM_POSITION] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ---------Thong tin dong phuc----------- GO CREATE TABLE [dbo].[HR_UNIFORM_INFO]( [PR_KEY] [uniqueidentifier] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [UNIFORM_ID] [nvarchar](20) NOT NULL, [POSITION_ID] [nvarchar](20) NOT NULL, [DATE_SUPPLY] [smalldatetime] NOT NULL, [SIZE] [nvarchar](10) NOT NULL, [QUANTITY_RQ] [int] NOT NULL, [QUANTITY_REAL] [int] NOT NULL, [UNIT_PRICE] [money] NOT NULL, [COST_ASSISTANCE] [money] NOT NULL, [MONEY_DEPOSIT] [money] NOT NULL, [TIME_DEPRECIATION] [int] NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_HR_UNIFORM_IFO] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_DM_UNIFORM','HRM','HRM_UNIFORM','LIST','HRMUNIFORMGROUP1','',0,'DM_UNIFORM',1,0) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_DM_UNIFORM_POSITION','HRM','HRM_UNIFORM','LIST','HRMUNIFORMGROUP1','',0,'DM_UNIFORM_POSITION',2,0) GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_UNIFORM' ,'UNIFORM_ID' ,'UNIFORM_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_HR_UNIFORM_INFO','HRM','HRM_UNIFORM','FUNC','HRMUNIFORMGROUP1','',0,'HR_UNIFORM_INFO',2,0) GO ---------06/08/2015 GO update SYS_GRIDINFO set visible = '1', visibleindex = '0', filtered ='1' , enabled =0 where (form_name = 'FrmHr_Uniform_Info' and GRID_NAME = 'TREELIST') GO -------11/08/2015 QUẢN LÝ SỨC KHỎE--------------------------------- GO -------dm tiêu chí------- GO CREATE TABLE [dbo].[DM_HEALTH_CRITERIA]( [CRITERIA_ID] [nvarchar](20) NOT NULL, [CRITERIA_NAME] [nvarchar](100) NOT NULL, [CRITERIA_NAME_SHORT] [nvarchar](50) NOT NULL, [NO_MONTH_RE_EXAMINATION] [int] NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_HEALTH_CRITERIA] PRIMARY KEY CLUSTERED ( [CRITERIA_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DM_HEALTH_CRITERIA_POSITION]( [PR_KEY] [uniqueidentifier] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [POSITION_ID] [nvarchar](20) NOT NULL, [CRITERIA_ID] [nvarchar](20) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_HEALTH_CRITERIA_POSITION] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[HR_CRITERIA_RESULT]( [PR_KEY] [uniqueidentifier] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [POSITION_ID] [nvarchar](20) NOT NULL, [CRITERIA_ID] [nvarchar](20) NOT NULL, [RESULT_CRITERIA] [nvarchar](200) NOT NULL, [DATE_EXAMINATION] [smalldatetime] NOT NULL, [EXPIRATION_DATE] [smalldatetime] NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_HR_CRITERIA_RESULT] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_DM_HEALTH_CRITERIA','HRM','HRM_UNIFORM','LIST','HRMUNIFORMGROUP1','',0,'DM_HEALTH_CRITERIA',2,0) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_DM_HEALTH_CRITERIA_POSITION','HRM','HRM_UNIFORM','FUNC','HRMUNIFORMGROUP1','',0,'DM_HEALTH_CRITERIA_POSITION',2,0) GO update SYS_GRIDINFO set visible = '1', visibleindex = '0', filtered ='1' , enabled =0 where (form_name = 'FrmDm_Health_Criteria_Position' and GRID_NAME = 'TREELIST') GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('HR_HR_CRITERIA_RESULT','HRM','HRM_UNIFORM','FUNC','HRMUNIFORMGROUP1','',0,'HR_CRITERIA_RESULT',1,0) GO update SYS_GRIDINFO set visible = '1', visibleindex = '0', filtered ='1' , enabled =0 where (form_name = 'FrmHr_Criteria_Result' and GRID_NAME = 'TREELIST') GO DROP VIEW SALE_TRAN_VIEW GO CREATE VIEW [dbo].[SALE_TRAN_VIEW] AS SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.ACCOUNT_ID_PR, dbo.SALE.ACCOUNT_ID_VAT, dbo.SALE.ACCOUNT_ID_EXPORT_TAX, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.EXCHANGE_RATE_EXTRA, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.STATUS, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.SALE.IMPLEMENTED_EMPLOYEE_ID, dbo.SALE.SALE_DATE, dbo.SALE.IMPLEMENTED_DATE, dbo.DM_ITEM.ORIGIN, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE.SHIPPING_METHOD_ID, dbo.SALE.VEHICLE_ID, dbo.SALE.TAX_OFFICE_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.RECEIVE_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.DESCRIPTION_ULS, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.EXPORT_TAX_RATE, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.ACCOUNT_ID, dbo.SALE_DETAIL.ACCOUNT_ID_COST, dbo.SALE_DETAIL.ACCOUNT_ID_INCOME, dbo.SALE_DETAIL.PR_DETAIL_ID_ITEM, dbo.SALE_DETAIL.EXPENSE_ID, dbo.SALE_DETAIL.JOB_ID, dbo.SALE_DETAIL.AMOUNT_EXTRA, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_EXTRA, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.COG_AMOUNT_EXTRA, dbo.SALE_DETAIL.SALE_COST_EXTRA, dbo.SALE_DETAIL.TOTAL_AMOUNT_EXTRA, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.JOB_QTY, dbo.SALE_DETAIL.BILL_NO, dbo.SALE_DETAIL.BILL_AMOUNT, dbo.SALE_DETAIL.BILL_VAT_TAX_AMOUNT, dbo.SALE_DETAIL.OPENNING_QUANTITY, dbo.SALE_DETAIL.CLOSING_QUANTITY, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_EXTRA, dbo.SALE_DETAIL.EMPLOYEE_ID, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, dbo.SALE_DETAIL.ROOM_ID, dbo.SALE_DETAIL.ITEM_SOURCE_ID, dbo.SALE_DETAIL.PR_KEY_HT_FOLIO, dbo.SALE_DETAIL.QUANTITY_BILL, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.SALE_DETAIL.ITEM_ID + dbo.SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, dbo.SALE.ORIG_TRAN_NO, dbo.DM_WAREHOUSE.WAREHOUSE_CLASS_ID, dbo.SALE.ORIG_VAT_TRAN_DATE, dbo.SALE.ORIG_VAT_TRAN_NO, dbo.SALE.PR_KEY_POS, sale_detail.ITEM_HEIGHT,sale_detail.ITEM_WIDTH,sale_detail.ITEM_DEPTH,sale_detail.ITEM_SIZE,sale_detail.ITEM_LOCATION,sale_detail.ITEM_SERI_NO,sale_detail.ITEM_SERI_NO1,sale_detail.ITEM_SERI_NO2,sale_detail.ITEM_GRADE_ID,sale_detail.ITEM_GRADE1_ID,SALE_DETAIL.ITEM_STATUS_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SALE_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.HR_EMPLOYEE_INFO ON dbo.SALE_DETAIL.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID LEFT OUTER JOIN dbo.DM_WAREHOUSE ON dbo.DM_WAREHOUSE.WAREHOUSE_ID = dbo.SALE.WAREHOUSE_ID GO GO Drop view Purchase_TRAN_view Go CREATE VIEW [dbo].[Purchase_TRAN_view] AS SELECT dbo.PURCHASE.PR_KEY AS PR_KEY_CTU , dbo.PURCHASE.STATUS, dbo.PURCHASE.TRAN_ID , dbo.PURCHASE.TRAN_NO , dbo.PURCHASE.TRAN_DATE , dbo.PURCHASE.REFERENCE_NO , dbo.PURCHASE.ORIG_TRAN_NO , dbo.PURCHASE.PO_TRAN_NO , dbo.PURCHASE.PO_TRAN_ID , dbo.PURCHASE.VAT_TRAN_NO , dbo.PURCHASE.VAT_TRAN_DATE , dbo.PURCHASE.VAT_TRAN_SERIE , dbo.PURCHASE.VAT_PURCHASE_ID , dbo.PURCHASE.WAREHOUSE_ID_ISSUE , dbo.PURCHASE.MARKET_ID , dbo.PURCHASE.EXCHANGE_RATE , dbo.PURCHASE.CURRENCY_ID , dbo.PURCHASE.PR_DETAIL_ID , dbo.PURCHASE.EMPLOYEE_ID , dbo.PURCHASE.PR_DETAIL_NAME , dbo.PURCHASE.CONTACT_PERSON , dbo.PURCHASE.ADDRESS , dbo.PURCHASE.TAX_FILE_NUMBER , dbo.PURCHASE.PAYMENT_METHOD_ID , dbo.PURCHASE.COMMENTS , dbo.PURCHASE.PAYMENT_TERM_ID , dbo.PURCHASE.PAYMENT_DATE , dbo.PURCHASE_DETAIL.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.PURCHASE.ORGANIZATION_ID , dbo.PURCHASE.ACCOUNT_ID_PR , dbo.PURCHASE.ACCOUNT_ID_VAT , PURCHASE_DETAIL.ACCOUNT_ID_COST, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.PURCHASE_DETAIL.PR_KEY , dbo.PURCHASE_DETAIL.FR_KEY , dbo.PURCHASE_DETAIL.LIST_ORDER , dbo.PURCHASE_DETAIL.LOT_NO , dbo.PURCHASE_DETAIL.MANU_DATE , dbo.PURCHASE_DETAIL.EXPIRED_TERM , dbo.PURCHASE_DETAIL.EXPIRED_DATE , dbo.PURCHASE_DETAIL.ITEM_ID , dbo.PURCHASE_DETAIL.DESCRIPTION , dbo.PURCHASE_DETAIL.DESCRIPTION_ULS , dbo.PURCHASE_DETAIL.UNIT_ID , dbo.PURCHASE_DETAIL.QUANTITY , dbo.PURCHASE_DETAIL.QUANTITY_EXTRA , dbo.PURCHASE_DETAIL.QUANTITY_WH , dbo.PURCHASE_DETAIL.UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.UNIT_PRICE , dbo.PURCHASE_DETAIL.UNIT_PRICE_WH , dbo.PURCHASE_DETAIL.AMOUNT_ORIG , dbo.PURCHASE_DETAIL.AMOUNT , dbo.PURCHASE_DETAIL.AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_ID , dbo.PURCHASE_DETAIL.VAT_TAX_RATE , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_RATE , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.PURCHASE_COST , dbo.PURCHASE_DETAIL.PURCHASE_COST_ORIG , dbo.PURCHASE_DETAIL.PURCHASE_COST_EXTRA , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE , dbo.PURCHASE_DETAIL.COG_AMOUNT , dbo.PURCHASE_DETAIL.COG_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.DISCOUNT_RATE , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_RATE , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.ITEM_OP_ID , dbo.PURCHASE_DETAIL.WAREHOUSE_ID , dbo.PURCHASE_DETAIL.PRICE_LEVEL_ID , dbo.DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.PURCHASE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.PURCHASE_DETAIL.QUANTITY_EXPECTED , dbo.PURCHASE_DETAIL.FIXED_UNIT_PRICE , dbo.PURCHASE_DETAIL.FIXED_AMOUNT , dbo.PURCHASE_DETAIL.QUANTITY_BILL , dbo.PURCHASE_DETAIL.QUANTITY_MEASURE , PURCHASE.EXCHANGE_RATE_EXTRA , PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , PURCHASE.VEHICLE_ID , '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_RATE , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.INSURANCE_RATE , PURCHASE_DETAIL.EMPLOYEE_COMMISSION , PURCHASE_DETAIL.EMPLOYEE_COMMISSION_ORIG , PURCHASE_DETAIL.EMPLOYEE_COMMISSION_RATE, PURCHASE_DETAIL.ACCOUNT_ID_COST AS ACCOUNT_ID, dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM,DM_PR_DETAIL_ITEM.ADDRESS AS ADDRESS_ITEM,DM_PR_DETAIL_ITEM.TAX_FILE_NUMBER AS TAX_FILE_NUMBER_ITEM, DM_PR_DETAIL_ITEM.PR_DETAIL_NAME AS PR_DETAIL_NAME_ITEM, PURCHASE_DETAIL.ITEM_HEIGHT,PURCHASE_DETAIL.ITEM_WIDTH,PURCHASE_DETAIL.ITEM_DEPTH,PURCHASE_DETAIL.ITEM_SIZE,PURCHASE_DETAIL.ITEM_LOCATION,PURCHASE_DETAIL.ITEM_SERI_NO,PURCHASE_DETAIL.ITEM_SERI_NO1,PURCHASE_DETAIL.ITEM_SERI_NO2,PURCHASE_DETAIL.ITEM_GRADE_ID,PURCHASE_DETAIL.ITEM_GRADE1_ID,PURCHASE_DETAIL.ITEM_STATUS_ID,PURCHASE_DETAIL.SELECTED, PURCHASE_DETAIL.JOB_ID FROM dbo.PURCHASE INNER JOIN dbo.PURCHASE_DETAIL ON dbo.PURCHASE.PR_KEY = dbo.PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL AS DM_PR_DETAIL_ITEM ON dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM = DM_PR_DETAIL_ITEM.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON PURCHASE.TRAN_ID = SYS_TRAN.TRAN_ID GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HR_UNIFORM_LIST_COLOR', 'RED,YELLOW,GREEN,WHITE', N'Danh sách màu trong thông tin đồng phục', 'STRING', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_VERSION_NEW', 0, N'Phien ban tinh luong moi', 'BOOLEAN', 'HRM') GO alter table HR_EMPLOYEE_HISTORY add DEPARTMENTS nvarchar(200) DEFAULT '' NOT NULL GO ALTER TABLE HR_TRAINING_EMPLOYEE ADD IS_CERTIFICATION smallint DEFAULT 0 NOT NULL GO ALTER TABLE HR_DOCUMENT_FILESTREAM ADD TBL_NAME NVARCHAR(200) DEFAULT '' NOT NULL GO ALTER TABLE HR_CERTIFICATION ADD FR_KEY uniqueidentifier DEFAULT NEWID() NOT NULL GO -----27/08/2015------------------- GO CREATE TABLE [dbo].[HR_PROFILE_EXIT]( [PR_KEY] [uniqueidentifier] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [DATE_START] [smalldatetime] NOT NULL, [NO_MONTHS] [int] NOT NULL, [DATE_END] [smalldatetime] NOT NULL, [CONTENTS] [nvarchar](200) NOT NULL, [PROFILE_EXIT_PLACE_ID] [nvarchar](200) NOT NULL, [PROFILE_EXIT_PLACE_TEXT] [nvarchar](300) NOT NULL, [EXPENSE_REGISTRY] [money] NOT NULL, [EXPENSE_OUT] [money] NOT NULL, [CONTENTS1] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_HR_PROFILE_EXIT] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO DROP VIEW SO_VIEW GO CREATE VIEW [dbo].[SO_VIEW] AS SELECT dbo.SO.PR_KEY AS PR_KEY_CTU, dbo.SO.ORGANIZATION_ID, dbo.SO.TRAN_ID, dbo.SO.TRAN_NO, dbo.SO.TRAN_DATE, dbo.SO.SBO_TRAN_ID, dbo.SO.SBO_TRAN_NO, dbo.SO.IS_OVER_CREDIT_LIMIT, dbo.SO.APPROVE_DATE, dbo.SO.RELEASE_DATE, dbo.SO.DELIVERY_DATE, dbo.SO.EMPLOYEE_ID, dbo.SO.MARKET_ID, dbo.SO.SHIPPING_METHOD_ID, dbo.SO.VEHICLE_ID, dbo.SO.CURRENCY_ID, dbo.SO.EXCHANGE_RATE, dbo.SO.EXCHANGE_RATE_EXTRA, dbo.SO.PR_DETAIL_NAME, dbo.SO.CONTACT_PERSON, dbo.SO.ADDRESS, dbo.SO.TAX_FILE_NUMBER, dbo.SO.PAYMENT_METHOD_ID, dbo.SO.PAYMENT_TERM_ID, dbo.SO.COMMENTS, dbo.SO.STATUS, dbo.SO.USER_ID, dbo.SO.APPROVER_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.SO.DRIVER_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.SO.DELIVER_ORGANIZATION_ID, dbo.SO_DETAIL.PR_KEY, dbo.SO_DETAIL.FR_KEY, dbo.SO_DETAIL.LIST_ORDER, dbo.SO_DETAIL.ITEM_SOURCE_ID, dbo.SO_DETAIL.WAREHOUSE_ID, dbo.SO_DETAIL.PR_DETAIL_ID, dbo.SO_DETAIL.JOB_ID, dbo.SO_DETAIL.ITEM_ID, dbo.SO_DETAIL.DESCRIPTION, dbo.SO_DETAIL.DESCRIPTION_ULS, dbo.SO_DETAIL.UNIT_ID, dbo.SO_DETAIL.QUANTITY, dbo.SO_DETAIL.QUANTITY_EXTRA, dbo.SO_DETAIL.QUANTITY_WH, dbo.SO_DETAIL.UNIT_PRICE_ORIG, dbo.SO_DETAIL.UNIT_PRICE, dbo.SO_DETAIL.UNIT_PRICE_WH, dbo.SO_DETAIL.AMOUNT_ORIG, dbo.SO_DETAIL.AMOUNT, dbo.SO_DETAIL.AMOUNT_EXTRA, dbo.SO_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.SO_DETAIL.VAT_INCOME_AMOUNT, dbo.SO_DETAIL.VAT_INCOME_AMOUNT_EXTRA, dbo.SO_DETAIL.VAT_TAX_ID, dbo.SO_DETAIL.VAT_TAX_RATE, dbo.SO_DETAIL.VAT_TAX_AMOUNT, dbo.SO_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SO_DETAIL.VAT_TAX_AMOUNT_EXTRA, dbo.SO_DETAIL.EXPORT_TAX_RATE, dbo.SO_DETAIL.EXPORT_TAX_AMOUNT, dbo.SO_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SO_DETAIL.EXPORT_TAX_AMOUNT_EXTRA, dbo.SO_DETAIL.DISCOUNT_RATE, dbo.SO_DETAIL.DISCOUNT_AMOUNT, dbo.SO_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SO_DETAIL.DISCOUNT_AMOUNT_EXTRA, dbo.SO_DETAIL.LUX_TAX_RATE, dbo.SO_DETAIL.LUX_TAX_AMOUNT, dbo.SO_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SO_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.SO_DETAIL.TOTAL_AMOUNT, dbo.SO_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SO_DETAIL.TOTAL_AMOUNT_EXTRA, dbo.SO_DETAIL.PRICE_LEVEL_ID, dbo.SO_DETAIL.ITEM_OP_ID, dbo.SO_DETAIL.DELIVERY_TIME, dbo.SO_DETAIL.IS_SII, dbo.SO_DETAIL.IS_DELIVERED, dbo.SO_DETAIL.IS_INVOICED, dbo.SO_DETAIL.QUANTITY_INVOICE, dbo.SO_DETAIL.QUANTITY_DELIVERY, dbo.SO_DETAIL.QUANTITY_SII, dbo.DM_PR_DETAIL.PR_ACCOUNT_ID, dbo.DM_PR_DETAIL.PR_ACCOUNT_ID1,DM_PR_DETAIL.PR_DETAIL_NAME AS PR_DETAIL_NAME_DETAIL, SO_DETAIL.ITEM_HEIGHT,SO_DETAIL.ITEM_WIDTH,SO_DETAIL.ITEM_DEPTH,SO_DETAIL.ITEM_SIZE,SO_DETAIL.ITEM_LOCATION,SO_DETAIL.ITEM_SERI_NO,SO_DETAIL.ITEM_SERI_NO1,SO_DETAIL.ITEM_SERI_NO2,SO_DETAIL.ITEM_GRADE_ID,SO_DETAIL.ITEM_GRADE1_ID,SO_DETAIL.ITEM_STATUS_ID FROM dbo.SO INNER JOIN dbo.SO_DETAIL ON dbo.SO.PR_KEY = dbo.SO_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SO_DETAIL.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SO_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SO_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO INSERT INTO SYS_SYSTEMVAR VALUES('COSTING_BY_ALL','0','COSTING_BY_ALL','BOOLEAN','COMMON') GO DROP VIEW [dbo].[PURCHASE_VIEW] GO CREATE VIEW [dbo].[PURCHASE_VIEW] AS ( SELECT dbo.PURCHASE.PR_KEY AS PR_KEY_CTU , dbo.PURCHASE.TRAN_ID , dbo.PURCHASE.TRAN_NO , dbo.PURCHASE.TRAN_DATE , dbo.PURCHASE.REFERENCE_NO , dbo.PURCHASE.ORIG_TRAN_NO , dbo.PURCHASE.PO_TRAN_NO , dbo.PURCHASE.PO_TRAN_ID , dbo.PURCHASE.VAT_TRAN_NO , dbo.PURCHASE.VAT_TRAN_DATE , dbo.PURCHASE.VAT_TRAN_SERIE , dbo.PURCHASE.VAT_PURCHASE_ID , dbo.PURCHASE.WAREHOUSE_ID_ISSUE , dbo.PURCHASE.MARKET_ID , dbo.PURCHASE.EXCHANGE_RATE , dbo.PURCHASE.CURRENCY_ID , dbo.PURCHASE.PR_DETAIL_ID , dbo.PURCHASE.PR_DETAIL_NAME , dbo.PURCHASE.CONTACT_PERSON , dbo.PURCHASE.ADDRESS , dbo.PURCHASE.TAX_FILE_NUMBER , dbo.PURCHASE.PAYMENT_METHOD_ID , dbo.PURCHASE.COMMENTS , dbo.PURCHASE.PAYMENT_TERM_ID , dbo.PURCHASE.PAYMENT_DATE , dbo.PURCHASE_DETAIL.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.PURCHASE.ORGANIZATION_ID , dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.PURCHASE_DETAIL.PR_KEY , dbo.PURCHASE_DETAIL.FR_KEY , dbo.PURCHASE_DETAIL.LIST_ORDER , dbo.PURCHASE_DETAIL.LOT_NO , dbo.PURCHASE_DETAIL.MANU_DATE , dbo.PURCHASE_DETAIL.EXPIRED_TERM , dbo.PURCHASE_DETAIL.EXPIRED_DATE , dbo.PURCHASE_DETAIL.ITEM_ID , dbo.PURCHASE_DETAIL.DESCRIPTION , dbo.PURCHASE_DETAIL.DESCRIPTION_ULS , dbo.PURCHASE_DETAIL.UNIT_ID , dbo.PURCHASE_DETAIL.QUANTITY , dbo.PURCHASE_DETAIL.QUANTITY_EXTRA , dbo.PURCHASE_DETAIL.QUANTITY_WH , dbo.PURCHASE_DETAIL.UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.UNIT_PRICE , dbo.PURCHASE_DETAIL.UNIT_PRICE_WH , dbo.PURCHASE_DETAIL.AMOUNT_ORIG , dbo.PURCHASE_DETAIL.AMOUNT , dbo.PURCHASE_DETAIL.AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_ID , dbo.PURCHASE_DETAIL.VAT_TAX_RATE , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_RATE , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.PURCHASE_COST , dbo.PURCHASE_DETAIL.PURCHASE_COST_ORIG , dbo.PURCHASE_DETAIL.PURCHASE_COST_EXTRA , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE , dbo.PURCHASE_DETAIL.COG_AMOUNT , dbo.PURCHASE_DETAIL.COG_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.DISCOUNT_RATE , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_RATE , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.ITEM_OP_ID , dbo.PURCHASE_DETAIL.WAREHOUSE_ID , dbo.PURCHASE_DETAIL.PRICE_LEVEL_ID , dbo.DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.PURCHASE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.PURCHASE_DETAIL.QUANTITY_EXPECTED , dbo.PURCHASE_DETAIL.FIXED_UNIT_PRICE , dbo.PURCHASE_DETAIL.FIXED_AMOUNT , dbo.PURCHASE_DETAIL.QUANTITY_BILL , dbo.PURCHASE_DETAIL.QUANTITY_MEASURE , PURCHASE.EXCHANGE_RATE_EXTRA , PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , PURCHASE.VEHICLE_ID , '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY, dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_RATE , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT_EXTRA, dbo.PURCHASE_DETAIL.INSURANCE_RATE, PURCHASE_DETAIL.EMPLOYEE_COMMISSION, PURCHASE_DETAIL.EMPLOYEE_COMMISSION_ORIG,PURCHASE_DETAIL.ITEM_STATUS_ID,DM_ITEM_STATUS.ITEM_STATUS_NAME,PURCHASE_DETAIL.VCF,PURCHASE_DETAIL.WCF,PURCHASE_DETAIL.ITEM_WIDTH,PURCHASE_DETAIL.ITEM_HEIGHT,PURCHASE_DETAIL.ITEM_DEPTH FROM dbo.PURCHASE INNER JOIN dbo.PURCHASE_DETAIL ON dbo.PURCHASE.PR_KEY = dbo.PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_ITEM_STATUS ON dbo.PURCHASE_DETAIL.ITEM_STATUS_ID = dbo.DM_ITEM_STATUS.ITEM_STATUS_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON PURCHASE.TRAN_ID = SYS_TRAN.TRAN_ID WHERE SYS_TRAN.IS_SALE = 1 AND PURCHASE.STATUS='POSTED' ) UNION ALL ( SELECT dbo.POS_PURCHASE.PR_KEY AS PR_KEY_CTU , dbo.POS_PURCHASE.TRAN_ID , dbo.POS_PURCHASE.TRAN_NO , dbo.POS_PURCHASE.TRAN_DATE , '' AS REFERENCE_NO , '' AS ORIG_TRAN_NO , '' AS PO_TRAN_NO , '' AS PO_TRAN_ID , dbo.POS_PURCHASE.VAT_TRAN_NO , dbo.POS_PURCHASE.VAT_TRAN_DATE , dbo.POS_PURCHASE.VAT_TRAN_SERIE , '' AS VAT_POS_PURCHASE_ID , dbo.POS_PURCHASE.WAREHOUSE_ID_ISSUE , '' AS MARKET_ID , dbo.POS_PURCHASE.EXCHANGE_RATE , dbo.POS_PURCHASE.CURRENCY_ID , dbo.POS_PURCHASE.PR_DETAIL_ID , dbo.POS_PURCHASE.PR_DETAIL_NAME , dbo.POS_PURCHASE.CONTACT_PERSON , dbo.POS_PURCHASE.ADDRESS , dbo.POS_PURCHASE.TAX_FILE_NUMBER , dbo.POS_PURCHASE.PAYMENT_METHOD_ID , dbo.POS_PURCHASE.COMMENTS , dbo.POS_PURCHASE.PAYMENT_TERM_ID , dbo.POS_PURCHASE.PAYMENT_DATE , dbo.POS_PURCHASE.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.POS_PURCHASE.ORGANIZATION_ID , dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.POS_PURCHASE_DETAIL.PR_KEY , dbo.POS_PURCHASE_DETAIL.FR_KEY , dbo.POS_PURCHASE_DETAIL.LIST_ORDER , dbo.POS_PURCHASE_DETAIL.LOT_NO , dbo.POS_PURCHASE_DETAIL.MANU_DATE , dbo.POS_PURCHASE_DETAIL.EXPIRED_TERM , dbo.POS_PURCHASE_DETAIL.EXPIRED_DATE , dbo.POS_PURCHASE_DETAIL.ITEM_ID , dbo.POS_PURCHASE_DETAIL.DESCRIPTION , '' AS DESCRIPTION_ULS , dbo.POS_PURCHASE_DETAIL.UNIT_ID , dbo.POS_PURCHASE_DETAIL.QUANTITY , dbo.POS_PURCHASE_DETAIL.QUANTITY_EXTRA , dbo.POS_PURCHASE_DETAIL.QUANTITY_WH , dbo.POS_PURCHASE_DETAIL.UNIT_PRICE_ORIG , dbo.POS_PURCHASE_DETAIL.UNIT_PRICE , dbo.POS_PURCHASE_DETAIL.UNIT_PRICE_WH , dbo.POS_PURCHASE_DETAIL.AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT , dbo.POS_PURCHASE_DETAIL.VAT_INCOME_AMOUNT * 0 AS VAT_INCOME_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.VAT_TAX_ID , dbo.POS_PURCHASE_DETAIL.VAT_TAX_RATE , dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT , dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT * 0 AS VAT_TAX_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_RATE , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS POS_PURCHASE_COST_EXTRA , dbo.POS_PURCHASE_DETAIL.COG_UNIT_PRICE , dbo.POS_PURCHASE_DETAIL.COG_AMOUNT , dbo.POS_PURCHASE_DETAIL.COG_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.COG_AMOUNT_ORIG * 0 AS COG_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.DISCOUNT_RATE , dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT , dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG * 0 AS DISCOUNT_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT , dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT * 0 AS TOTAL_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.POS_PURCHASE_DETAIL.LUX_TAX_RATE , dbo.POS_PURCHASE_DETAIL.LUX_TAX_AMOUNT , dbo.POS_PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS LUX_TAX_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.ITEM_OP_ID , dbo.POS_PURCHASE_DETAIL.WAREHOUSE_ID , dbo.POS_PURCHASE_DETAIL.PRICE_LEVEL_ID , DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.POS_PURCHASE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED , dbo.POS_PURCHASE_DETAIL.FIXED_UNIT_PRICE , dbo.POS_PURCHASE_DETAIL.FIXED_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_BILL , dbo.POS_PURCHASE_DETAIL.AMOUNT * 0 AS QUANTITY_MEASURE , 1 AS EXCHANGE_RATE_EXTRA , POS_PURCHASE_DETAIL.ITEM_ID + POS_PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , '' AS VEHICLE_ID , POS_SHIFT_PR_KEY, dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_AMOUNT_EXTRA , dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_RATE , dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as INSURANCE_AMOUNT , dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as INSURANCE_AMOUNT_EXTRA, dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as INSURANCE_RATE, dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as EMPLOYEE_COMMISSION, dbo.POS_PURCHASE_DETAIL.AMOUNT*0 as EMPLOYEE_COMMISSION_ORIG,POS_PURCHASE_DETAIL.ITEM_STATUS_ID,DM_ITEM_STATUS.ITEM_STATUS_NAME,dbo.POS_PURCHASE_DETAIL.AMOUNT*0 AS VCF,dbo.POS_PURCHASE_DETAIL.AMOUNT*0 AS WCF,dbo.POS_PURCHASE_DETAIL.AMOUNT*0 AS ITEM_WIDTH,dbo.POS_PURCHASE_DETAIL.AMOUNT*0 AS ITEM_HEIGHT,dbo.POS_PURCHASE_DETAIL.AMOUNT*0 AS ITEM_DEPTH FROM dbo.POS_PURCHASE INNER JOIN dbo.POS_PURCHASE_DETAIL ON dbo.POS_PURCHASE.PR_KEY = dbo.POS_PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_ITEM_STATUS ON dbo.POS_PURCHASE_DETAIL.ITEM_STATUS_ID = dbo.DM_ITEM_STATUS.ITEM_STATUS_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON POS_PURCHASE_DETAIL.ITEM_ID + POS_PURCHASE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON POS_PURCHASE.TRAN_ID = SYS_TRAN.TRAN_ID WHERE SYS_TRAN.IS_SALE = 1 AND POS_PURCHASE.STATUS='POSTED' ) UNION ALL ( SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU , dbo.SALE.TRAN_ID , dbo.SALE.TRAN_NO , dbo.SALE.TRAN_DATE , dbo.SALE.REFERENCE_NO , dbo.SALE.ORIG_TRAN_NO , '' AS PO_TRAN_NO , '' AS PO_TRAN_ID , dbo.SALE.VAT_TRAN_NO , dbo.SALE.VAT_TRAN_DATE , dbo.SALE.VAT_TRAN_SERIE , dbo.SALE.VAT_PURCHASE_ID , '' AS WAREHOUSE_ID_ISSUE , dbo.SALE.MARKET_ID , dbo.SALE.EXCHANGE_RATE , dbo.SALE.CURRENCY_ID , dbo.SALE.PR_DETAIL_ID , dbo.SALE.PR_DETAIL_NAME , dbo.SALE.CONTACT_PERSON , dbo.SALE.ADDRESS , dbo.SALE.TAX_FILE_NUMBER , dbo.SALE.PAYMENT_METHOD_ID , dbo.SALE.COMMENTS , dbo.SALE.PAYMENT_TERM_ID , dbo.SALE.PAYMENT_DATE , dbo.SALE_DETAIL.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.SALE.ORGANIZATION_ID , dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.SALE_DETAIL.PR_KEY , dbo.SALE_DETAIL.FR_KEY , dbo.SALE_DETAIL.LIST_ORDER , dbo.SALE_DETAIL.LOT_NO , dbo.SALE_DETAIL.MANU_DATE , dbo.SALE_DETAIL.EXPIRED_TERM , dbo.SALE_DETAIL.EXPIRED_DATE , dbo.SALE_DETAIL.ITEM_ID , dbo.SALE_DETAIL.DESCRIPTION , dbo.SALE_DETAIL.DESCRIPTION_ULS , dbo.SALE_DETAIL.UNIT_ID , dbo.SALE_DETAIL.QUANTITY , dbo.SALE_DETAIL.QUANTITY_EXTRA , dbo.SALE_DETAIL.QUANTITY_WH , dbo.SALE_DETAIL.UNIT_PRICE_ORIG , dbo.SALE_DETAIL.UNIT_PRICE , dbo.SALE_DETAIL.UNIT_PRICE_WH , dbo.SALE_DETAIL.AMOUNT_ORIG , dbo.SALE_DETAIL.AMOUNT , dbo.SALE_DETAIL.AMOUNT_EXTRA , dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.SALE_DETAIL.VAT_INCOME_AMOUNT , dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_EXTRA , dbo.SALE_DETAIL.VAT_TAX_ID , dbo.SALE_DETAIL.VAT_TAX_RATE , dbo.SALE_DETAIL.VAT_TAX_AMOUNT , dbo.SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA , dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG , 0 AS IMPORT_TAX_RATE , 0 AS IMPORT_TAX_AMOUNT , 0 AS IMPORT_TAX_AMOUNT_ORIG , 0 AS IMPORT_TAX_AMOUNT_EXTRA , dbo.SALE_DETAIL.SALE_COST , dbo.SALE_DETAIL.SALE_COST_ORIG , dbo.SALE_DETAIL.SALE_COST_EXTRA , dbo.SALE_DETAIL.COG_UNIT_PRICE , dbo.SALE_DETAIL.COG_AMOUNT , dbo.SALE_DETAIL.COG_AMOUNT_ORIG , dbo.SALE_DETAIL.COG_AMOUNT_EXTRA , dbo.SALE_DETAIL.DISCOUNT_RATE , dbo.SALE_DETAIL.DISCOUNT_AMOUNT , dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA , dbo.SALE_DETAIL.TOTAL_AMOUNT , dbo.SALE_DETAIL.TOTAL_AMOUNT_EXTRA , dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.SALE_DETAIL.LUX_TAX_RATE , dbo.SALE_DETAIL.LUX_TAX_AMOUNT , dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA , dbo.SALE_DETAIL.ITEM_OP_ID , dbo.SALE_DETAIL.WAREHOUSE_ID , dbo.SALE_DETAIL.PRICE_LEVEL_ID , DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.SALE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.SALE_DETAIL.QUANTITY_EXPECTED , dbo.SALE_DETAIL.FIXED_UNIT_PRICE , dbo.SALE_DETAIL.FIXED_AMOUNT , 0 AS QUANTITY_BILL , 0 AS QUANTITY_MEASURE , SALE.EXCHANGE_RATE_EXTRA , SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , SALE.VEHICLE_ID , '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY, dbo.SALE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_AMOUNT , dbo.SALE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_AMOUNT_EXTRA , dbo.SALE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_RATE , dbo.SALE_DETAIL.AMOUNT*0 as INSURANCE_AMOUNT , dbo.SALE_DETAIL.AMOUNT*0 as INSURANCE_AMOUNT_EXTRA, dbo.SALE_DETAIL.AMOUNT*0 as INSURANCE_RATE, dbo.SALE_DETAIL.AMOUNT*0 as EMPLOYEE_COMMISSION, dbo.SALE_DETAIL.AMOUNT*0 as EMPLOYEE_COMMISSION_ORIG,SALE_DETAIL.ITEM_STATUS_ID,DM_ITEM_STATUS.ITEM_STATUS_NAME,SALE_DETAIL.VCF,SALE_DETAIL.WCF,SALE_DETAIL.ITEM_WIDTH,SALE_DETAIL.ITEM_HEIGHT,SALE_DETAIL.ITEM_DEPTH FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_ITEM_STATUS ON dbo.SALE_DETAIL.ITEM_STATUS_ID = dbo.DM_ITEM_STATUS.ITEM_STATUS_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON SALE.TRAN_ID = SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON SALE_DETAIL.ITEM_OP_ID = DM_ITEM_OP.ITEM_OP_ID WHERE DM_ITEM_OP.OP_TYPE = 'N' AND SYS_TRAN.IS_SALE = 1 AND SALE.STATUS='POSTED' ) UNION ALL ( SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU , dbo.POS_SALE.TRAN_ID , dbo.POS_SALE.TRAN_NO , dbo.POS_SALE.TRAN_DATE , '' AS REFERENCE_NO , '' AS ORIG_TRAN_NO , '' AS PO_TRAN_NO , '' AS PO_TRAN_ID , dbo.POS_SALE.VAT_TRAN_NO , dbo.POS_SALE.VAT_TRAN_DATE , dbo.POS_SALE.VAT_TRAN_SERIE , '' AS VAT_POS_SALE_ID , '' AS WAREHOUSE_ID_ISSUE , '' AS MARKET_ID , dbo.POS_SALE.EXCHANGE_RATE , dbo.POS_SALE.CURRENCY_ID , dbo.POS_SALE.PR_DETAIL_ID , dbo.POS_SALE.PR_DETAIL_NAME , dbo.POS_SALE.CONTACT_PERSON , dbo.POS_SALE.ADDRESS , dbo.POS_SALE.TAX_FILE_NUMBER , dbo.POS_SALE.PAYMENT_METHOD_ID , dbo.POS_SALE.COMMENTS , dbo.POS_SALE.PAYMENT_TERM_ID , dbo.POS_SALE.PAYMENT_DATE , dbo.POS_SALE.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.POS_SALE.ORGANIZATION_ID , dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.POS_SALE_DETAIL.PR_KEY , dbo.POS_SALE_DETAIL.FR_KEY , dbo.POS_SALE_DETAIL.LIST_ORDER , dbo.POS_SALE_DETAIL.LOT_NO , dbo.POS_SALE_DETAIL.MANU_DATE , dbo.POS_SALE_DETAIL.EXPIRED_TERM , dbo.POS_SALE_DETAIL.EXPIRED_DATE , dbo.POS_SALE_DETAIL.ITEM_ID , dbo.POS_SALE_DETAIL.DESCRIPTION , '' AS DESCRIPTION_ULS , dbo.POS_SALE_DETAIL.UNIT_ID , dbo.POS_SALE_DETAIL.QUANTITY , dbo.POS_SALE_DETAIL.QUANTITY_EXTRA , dbo.POS_SALE_DETAIL.QUANTITY_WH , dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG , dbo.POS_SALE_DETAIL.UNIT_PRICE , dbo.POS_SALE_DETAIL.UNIT_PRICE_WH , dbo.POS_SALE_DETAIL.AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT , dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT * 0 AS VAT_INCOME_AMOUNT_EXTRA, dbo.POS_SALE_DETAIL.VAT_TAX_ID , dbo.POS_SALE_DETAIL.VAT_TAX_RATE , dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT , dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT * 0 AS VAT_TAX_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_RATE , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS IMPORT_TAX_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS POS_SALE_COST_EXTRA , 0 AS COG_UNIT_PRICE , 0 AS COG_AMOUNT , 0 AS COG_AMOUNT_ORIG , 0 AS COG_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.DISCOUNT_RATE , dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT , dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT * 0 AS DISCOUNT_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.TOTAL_AMOUNT , dbo.POS_SALE_DETAIL.TOTAL_AMOUNT * 0 AS TOTAL_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG , 0 AS COG_UNIT_PRICE_ORIG , dbo.POS_SALE_DETAIL.LUX_TAX_RATE , dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT , dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS LUX_TAX_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.ITEM_OP_ID , dbo.POS_SALE_DETAIL.WAREHOUSE_ID , dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID , DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.POS_SALE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED , dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE , dbo.POS_SALE_DETAIL.FIXED_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_BILL , dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_MEASURE , 1 AS EXCHANGE_RATE_EXTRA , POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , '' AS VEHICLE_ID , POS_SHIFT_PR_KEY, dbo.POS_SALE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_AMOUNT_EXTRA , dbo.POS_SALE_DETAIL.AMOUNT*0 as TRANSPORT_FEE_RATE , dbo.POS_SALE_DETAIL.AMOUNT*0 as INSURANCE_AMOUNT , dbo.POS_SALE_DETAIL.AMOUNT*0 as INSURANCE_AMOUNT_EXTRA, dbo.POS_SALE_DETAIL.AMOUNT*0 as INSURANCE_RATE, dbo.POS_SALE_DETAIL.AMOUNT*0 as EMPLOYEE_COMMISSION, dbo.POS_SALE_DETAIL.AMOUNT*0 as EMPLOYEE_COMMISSION_ORIG,POS_SALE_DETAIL.ITEM_STATUS_ID,DM_ITEM_STATUS.ITEM_STATUS_NAME,dbo.POS_SALE_DETAIL.AMOUNT*0 AS VCF,dbo.POS_SALE_DETAIL.AMOUNT*0 AS WCF,dbo.POS_SALE_DETAIL.AMOUNT*0 AS ITEM_WIDTH,dbo.POS_SALE_DETAIL.AMOUNT*0 AS ITEM_HEIGHT,dbo.POS_SALE_DETAIL.AMOUNT*0 AS ITEM_DEPTH FROM dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_ITEM_STATUS ON dbo.POS_SALE_DETAIL.ITEM_STATUS_ID = dbo.DM_ITEM_STATUS.ITEM_STATUS_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON POS_SALE.TRAN_ID = SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON POS_SALE_DETAIL.ITEM_OP_ID = DM_ITEM_OP.ITEM_OP_ID WHERE DM_ITEM_OP.OP_TYPE = 'N' AND SYS_TRAN.IS_SALE = 1 ) GO DROP VIEW [dbo].[SALE_BASE_PRICE_VIEW] GO CREATE VIEW [dbo].[SALE_BASE_PRICE_VIEW] AS SELECT dbo.SALE_BASE_PRICE.PR_KEY AS PR_KEY_CTU, dbo.SALE_BASE_PRICE.ORGANIZATION_ID, dbo.SALE_BASE_PRICE.TRAN_ID, dbo.SALE_BASE_PRICE.DAY_START, dbo.SALE_BASE_PRICE.DAY_END, dbo.SALE_BASE_PRICE.TRAN_NO, dbo.SALE_BASE_PRICE.COMMENTS, dbo.SALE_BASE_PRICE.ACTIVE, dbo.SALE_BASE_PRICE.USER_ID, dbo.SALE_BASE_PRICE_DETAIL.PR_KEY, dbo.SALE_BASE_PRICE_DETAIL.FR_KEY, dbo.SALE_BASE_PRICE_DETAIL.LIST_ORDER, dbo.SALE_BASE_PRICE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_BASE_PRICE_DETAIL.ITEM_ID, dbo.SALE_BASE_PRICE_DETAIL.CURRENCY_ID, dbo.SALE_BASE_PRICE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_BASE_PRICE_DETAIL.PR_DETAIL_ID, dbo.SALE_BASE_PRICE_DETAIL.UNIT_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.SALE_BASE_PRICE_DETAIL.DETAIL_ACTIVE, dbo.SALE_BASE_PRICE.STATUS,DM_ORGANIZATION.ORGANIZATION_NAME,DM_PRICE_LEVEL.PRICE_LEVEL_NAME FROM dbo.SALE_BASE_PRICE INNER JOIN dbo.SALE_BASE_PRICE_DETAIL ON dbo.SALE_BASE_PRICE.PR_KEY = dbo.SALE_BASE_PRICE_DETAIL.FR_KEY INNER JOIN dbo.DM_ITEM ON dbo.SALE_BASE_PRICE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE_BASE_PRICE_DETAIL.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_ORGANIZATION ON dbo.SALE_BASE_PRICE.ORGANIZATION_ID = dbo.DM_ORGANIZATION.ORGANIZATION_ID LEFT OUTER JOIN dbo.DM_PRICE_LEVEL ON dbo.SALE_BASE_PRICE_DETAIL.PRICE_LEVEL_ID = dbo.DM_PRICE_LEVEL.PRICE_LEVEL_ID GO ALTER TABLE SBO_DETAIL ADD ITEM_CLASS1_ID NVARCHAR(20) NULL GO CREATE TABLE [dbo].[DM_STREETS]( [STREET_ID] [nvarchar](20) NOT NULL, [STREET_NAME] [nvarchar](50) NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, [ACTIVE] [smallint] NOT NULL, [DISTRICT_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_STREETS] PRIMARY KEY CLUSTERED ( [STREET_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DM_STREETS] ADD CONSTRAINT [DF_DM_STREETS_DISTRICT_ID] DEFAULT ('') FOR [DISTRICT_ID] GO CREATE TABLE [dbo].[DM_HAMLETS]( [HAMLET_ID] [nvarchar](20) NOT NULL, [HAMLET_NAME] [nvarchar](50) NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, [ACTIVE] [smallint] NOT NULL, [COMMUNE_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_HAMLETS] PRIMARY KEY CLUSTERED ( [HAMLET_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DM_HAMLETS] ADD CONSTRAINT [DF_DM_HAMLETS_COMMUNE_ID] DEFAULT ('') FOR [COMMUNE_ID] GO INSERT INTO SYS_TABLE VALUES('DM_STREETS','STREET_ID','STREET_NAME','LIST',1,1,1,1,0,0,20,1,'') GO INSERT INTO SYS_TABLE VALUES('DM_HAMLETS','HAMLET_ID','HAMLET_NAME','LIST',1,1,1,1,0,0,20,1,'') GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('FIN_OM_DM_DISTRICTS','FIN','FIN_OM','LIST','','',100,'DM_DISTRICTS',16,0) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('FIN_OM_DM_STREETS','FIN','FIN_OM','LIST','','',100,'DM_STREETS',17,0) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('FIN_OM_DM_COMMUNES','FIN','FIN_OM','LIST','','',100,'DM_COMMUNES',18,0) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('FIN_OM_DM_HAMLETS','FIN','FIN_OM','LIST','','',100,'DM_HAMLETS',19,0) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_PROVINCES',1,1,1,1,GETDATE(),'ADMIN',1,1) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_DISTRICTS',1,1,1,1,GETDATE(),'ADMIN',1,1) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_COMMUNES',1,1,1,1,GETDATE(),'ADMIN',1,1) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_STREETS',1,1,1,1,GETDATE(),'ADMIN',1,1) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_HAMLETS',1,1,1,1,GETDATE(),'ADMIN',1,1) GO UPDATE DM_COMMUNICATE_CONFIG SET IS_NEW=1,IS_EDIT=1,IS_DELETE=1,IS_PUBLIC=1 WHERE TABLE_NAME='DM_COMMUNES' GO ALTER TABLE DM_PR_DETAIL ADD STREET_ID NVARCHAR(20) DEFAULT '' NOT NULL GO ALTER TABLE DM_PR_DETAIL ADD COMMUNE_ID NVARCHAR(20) DEFAULT '' NOT NULL GO ALTER TABLE DM_PR_DETAIL ADD HAMLET_ID NVARCHAR(20) DEFAULT '' NOT NULL GO ALTER TABLE COST_APPLICATION ADD IS_SUB_ORG SMALLINT DEFAULT 0 NULL GO CREATE TABLE [dbo].[DM_JOB_RELATION]( [JOB_ID] [nvarchar](20) NOT NULL, [DES_JOB_ID] [nvarchar](20) NOT NULL ) ON [PRIMARY] GO DROP VIEW DM_SBO GO CREATE VIEW [dbo].[DM_SBO] AS SELECT DISTINCT ORGANIZATION_ID + '-' + TRAN_ID + '-' + TRAN_NO + '_' + ITEM_ID AS SBO_ID, ORGANIZATION_ID, TRAN_ID AS SBO_TRAN_ID, TRAN_NO AS SBO_TRAN_NO, TRAN_DATE AS SBO_TRAN_DATE,PR_DETAIL_ID, PR_DETAIL_NAME, STATUS,PAYMENT_METHOD_ID,ITEM_ID, PAYMENT_TERM_ID,CURRENCY_ID,APPROVE_DATE,RELEASE_DATE,CREDIT_LIMIT FROM dbo.SBO_VIEW GO --02-11-2016--- GO ALTER TABLE DM_PROVINCES ADD REGION_ID NVARCHAR(20) DEFAULT '' NULL GO ALTER VIEW [DM_REC_PR_DETAIL] AS SELECT PR_DETAIL_ID ,PR_DETAIL_NAME FROM DM_PR_DETAIL WHERE ACTIVE=1 GO insert into sys_table values('DM_REC_PR_DETAIL','PR_DETAIL_ID','PR_DETAIL_NAME','VIEW',1,1,1,0,0,0,20,1,'') GO UPDATE SYS_TABLE SET ID_FIELD='PR_DETAIL_ID' WHERE TABLE_NAME='DM_REC_PR_DETAIL' GO ALTER TABLE DM_WAREHOUSE ADD COMMENT NVARCHAR(100) DEFAULT '' NOT NULL GO --16-01-2017-- GO INSERT [dbo].[DM_PR_METHOD] ([PR_METHOD_NAME], [PR_METHOD_DISPLAYNAME], [PR_METHOD_DETAIL], [ACTIVE], [IS_SALARY], [USER_ID]) VALUES (N'CalcTetBonusKangNa', N'Phương thức tinh thưởng tết KangNa 2017', N'CalcTetBonusKangNa-Phương thức tinh thưởng tết KangNa 2017', 1, 1, N'ADMIN') GO ALTER TABLE DM_PAYMENT_TERM ADD PAYMENT_DAY_SAME_MONTH INT DEFAULT 0 GO ALTER TABLE DM_PAYMENT_TERM ADD PAYMENT_DAY_NEXT_MONTH INT DEFAULT 0 GO ALTER TABLE DM_PR_DETAIL ADD EMPLOYEE_ID NVARCHAR(20) DEFAULT '' NOT NULL GO ALTER TABLE POS_SALE ADD IMPLEMENTED_EMPLOYEE_ID NVARCHAR(20) DEFAULT '' GO ALTER TABLE DM_PR_DETAIL ADD CREATE_DATE SMALLDATETIME DEFAULT '2017-01-01' NOT NULL GO ALTER TABLE CA_ACCOUNT_CONFIG ADD NOT_JOB SMALLINT DEFAULT 0 NOT NULL GO INSERT INTO SYS_SYSTEMVAR VALUES('IS_PGAS','0','IS_PGAS','BOOLEAN','COMMON') GO GO ALTER TABLE SALE_COST_DETAIL ADD SALE_TRAN_NO NVARCHAR(30) NOT NULL DEFAULT '' GO insert into sys_table values('DM_TRAN_NO','TRAN_NO','TRAN_NO','VIEW',1,1,1,0,0,0,20,1,'') GO ----------------------------------------23/08/2017---------------------------------------------------------- GO ALTER TABLE SALE_COST ADD DOCUMENT_TRAN_NO NVARCHAR(20) GO ALTER TABLE SALE_COST ADD DOCUMENT_TRAN_DATE SMALLDATETIME NULL GO ALTER TABLE SALE_COST_DETAIL ADD ITEM_ID NVARCHAR(20) GO ---07---09-2017-- GO ALTER TABLE DM_PR_DETAIL ADD IS_YOUTH_MEMBER SMALLINT DEFAULT 0 NOT NULL GO ALTER TABLE DM_PR_DETAIL ADD YOUTH_MEMBER_CARD NVARCHAR(30) DEFAULT '' NOT NULL GO GO ALTER TABLE BUDGET ADD EXPENSE_CONTENT_ID NVARCHAR(20) DEFAULT '' NOT NULL GO ALTER TABLE SYS_REPORT_FORMULA_DETAIL ADD EXPENSE_CONTENT_ID NVARCHAR(100) DEFAULT '' NOT NULL GO ALTER TABLE DM_PAYMENT_TERM ADD DUE_DAYS1 INT DEFAULT 0 NOT NULL GO ALTER TABLE DM_PAYMENT_TERM ADD PAYMENT_DAY_NEXT_MONTH1 INT DEFAULT 0 NOT NULL GO ALTER TABLE DM_PAYMENT_TERM ADD PAYMENT_DAY_NEXT_MONTH2 INT DEFAULT 0 NOT NULL GO CREATE TABLE [dbo].[DM_EXPENSE_CLASS1]( [EXPENSE_CLASS1_ID] [nvarchar](20) NOT NULL, [EXPENSE_CLASS1_NAME] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_EXPENSE_CLASS1] PRIMARY KEY CLUSTERED ( [EXPENSE_CLASS1_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_TABLE VALUES('DM_EXPENSE_CLASS1','EXPENSE_CLASS_ID','EXPENSE_CLASS_NAME','LIST',1,1,1,1,0,0,20,1,'') GO INSERT INTO DM_EXPENSE_CLASS1 VALUES ('01',N'Chi phí cố định',1,'ADMIN') GO INSERT INTO DM_EXPENSE_CLASS1 VALUES ('02',N'Chi phí biến đổi',1,'ADMIN') GO ALTER TABLE DM_EXPENSE ADD EXPENSE_CLASS1_ID NVARCHAR(20) DEFAULT '' NOT NULL GO INSERT INTO SYS_DATASOURCE VALUES ('CPHT01',N'Chi phí hỗ trợ kỳ này',1,'ADMIN') GO INSERT INTO SYS_DATASOURCE VALUES ('CPHT02',N'Chi phí hỗ trợ đến đầu kỳ',1,'ADMIN') GO INSERT INTO SYS_DATASOURCE VALUES ('CPHT03',N'Chi phí hỗ trợ đến cuối kỳ',1,'ADMIN') GO DROP VIEW SALE_VIEW GO CREATE VIEW [dbo].[SALE_VIEW] AS (SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE_DETAIL.ITEM_SOURCE_ID, dbo.SALE.SHIPPING_METHOD_ID, dbo.SALE.VEHICLE_ID, dbo.SALE.TAX_OFFICE_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.RECEIVE_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.EXPORT_TAX_RATE, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.SALE.DRIVER_NAME, dbo.SALE.ORIG_TRAN_NO, dbo.SALE.ORIG_TRAN_ID, dbo.SALE.EMPLOYEE_ID,SALE.EXCHANGE_RATE_EXTRA, SALE_DETAIL.AMOUNT_EXTRA,SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA,SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA,SALE_DETAIL.SALE_COST_EXTRA,SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA,SALE_DETAIL.TOTAL_AMOUNT_EXTRA,SALE_DETAIL.EXPORT_TAX_AMOUNT_EXTRA,SALE_DETAIL.COG_AMOUNT_EXTRA, dbo.SALE_DETAIL.ITEM_ID + SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, SALE_DETAIL.ROOM_ID,DM_ITEM.ITEM_NAME AS ITEM_NAME,'00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY, dbo.SALE_DETAIL.JOB_ID, sale_detail.ITEM_HEIGHT,sale_detail.ITEM_WIDTH,sale_detail.ITEM_DEPTH,sale_detail.ITEM_SIZE,sale_detail.ITEM_LOCATION,sale_detail.ITEM_SERI_NO,sale_detail.ITEM_SERI_NO1,sale_detail.ITEM_SERI_NO2,sale_detail.ITEM_GRADE_ID,sale_detail.ITEM_GRADE1_ID,SALE_DETAIL.ITEM_STATUS_ID,DM_PR_DETAIL.CREATE_DATE,DM_SHIPPING_METHOD.SHIPPING_METHOD_NAME FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_ITEM_OP ON SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID LEFT JOIN DM_SHIPPING_METHOD ON SALE.SHIPPING_METHOD_ID=DM_SHIPPING_METHOD.SHIPPING_METHOD_ID WHERE (SALE_DETAIL.ITEM_OP_ID = '' OR (DM_ITEM_OP.OP_TYPE='X' and DM_ITEM_OP.ITEM_OP_ID <> '12000003')) AND SYS_TRAN.IS_SALE=1 AND SALE.STATUS='POSTED' ) UNION ALL (SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE.TRAN_ID, dbo.POS_SALE.TRAN_NO, dbo.POS_SALE.TRAN_DATE, '' AS REFERENCE_NO, dbo.POS_SALE.VAT_TRAN_DATE, dbo.POS_SALE.VAT_TRAN_SERIE, dbo.POS_SALE.MARKET_ID, dbo.POS_SALE.EXCHANGE_RATE, dbo.POS_SALE.CURRENCY_ID, dbo.POS_SALE.PR_DETAIL_ID, dbo.POS_SALE.PR_DETAIL_NAME, dbo.POS_SALE.CONTACT_PERSON, dbo.POS_SALE.ADDRESS, dbo.POS_SALE.TAX_FILE_NUMBER, dbo.POS_SALE.PAYMENT_METHOD_ID, dbo.POS_SALE.COMMENTS, dbo.POS_SALE.PAYMENT_TERM_ID, dbo.POS_SALE.PAYMENT_DATE, dbo.POS_SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.POS_SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, '' AS SBO_TRAN_ID, '' AS SBO_TRAN_NO, dbo.POS_SALE.SO_TRAN_ID, dbo.POS_SALE.SO_TRAN_NO, '' AS SII_TRAN_ID, '' AS SII_TRAN_NO, dbo.POS_SALE.VAT_TRAN_NO, dbo.POS_SALE.VAT_PURCHASE_ID, dbo.POS_SALE.REC_PR_DETAIL_ID, dbo.POS_SALE.ITEM_SOURCE_ID, '' AS SHIPPING_METHOD_ID, '' AS VEHICLE_ID, dbo.POS_SALE.TAX_OFFICE_ID, dbo.POS_SALE_DETAIL.PR_KEY, dbo.POS_SALE_DETAIL.FR_KEY, dbo.POS_SALE_DETAIL.LIST_ORDER, dbo.POS_SALE_DETAIL.LOT_NO, dbo.POS_SALE_DETAIL.MANU_DATE, dbo.POS_SALE_DETAIL.RECEIVE_DATE, dbo.POS_SALE_DETAIL.EXPIRED_TERM, dbo.POS_SALE_DETAIL.EXPIRED_DATE, dbo.POS_SALE_DETAIL.ITEM_ID, dbo.POS_SALE_DETAIL.DESCRIPTION, dbo.POS_SALE_DETAIL.UNIT_ID, dbo.POS_SALE_DETAIL.QUANTITY, dbo.POS_SALE_DETAIL.QUANTITY_EXTRA, dbo.POS_SALE_DETAIL.QUANTITY_WH, dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.UNIT_PRICE, dbo.POS_SALE_DETAIL.UNIT_PRICE_WH, dbo.POS_SALE_DETAIL.AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_ID, dbo.POS_SALE_DETAIL.VAT_TAX_RATE, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EXPORT_TAX_RATE, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.SALE_COST, dbo.POS_SALE_DETAIL.SALE_COST_ORIG, dbo.POS_SALE_DETAIL.DISCOUNT_RATE, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_UNIT_PRICE, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_AMOUNT, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS COG_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.LUX_TAX_RATE, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT * 0 AS QUANTITY_EXPECTED, dbo.POS_SALE_DETAIL.ITEM_OP_ID, dbo.POS_SALE_DETAIL.WAREHOUSE_ID, dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID, dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_SALE_DETAIL.FIXED_AMOUNT, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, '' AS DRIVER_NAME, '' AS ORIG_TRAN_NO, '' AS ORIG_TRAN_ID, dbo.POS_SALE.EMPLOYEE_ID,1 AS EXCHANGE_RATE_EXTRA, POS_SALE_DETAIL.AMOUNT * 0 AS AMOUNT_EXTRA,POS_SALE_DETAIL.AMOUNT *0 AS DISCOUNT_AMOUNT_EXTRA,POS_SALE_DETAIL.AMOUNT*0 AS VAT_TAX_AMOUNT_EXTRA,POS_SALE_DETAIL.AMOUNT*0 AS SALE_COST_EXTRA,POS_SALE_DETAIL.AMOUNT*0 AS LUX_TAX_AMOUNT_EXTRA,POS_SALE_DETAIL.AMOUNT*0 AS TOTAL_AMOUNT_EXTRA,POS_SALE_DETAIL.AMOUNT*0 AS EXPORT_TAX_AMOUNT_EXTRA,POS_SALE_DETAIL.AMOUNT*0 AS COG_AMOUNT_EXTRA, POS_SALE_DETAIL.ITEM_ID + POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, '' AS ROOM_ID,DM_ITEM.ITEM_NAME,POS_SHIFT_PR_KEY, '' as JOB_ID, CAST(0 AS MONEY) AS ITEM_HEIGHT,CAST(0 AS MONEY) AS ITEM_WIDTH,CAST(0 AS MONEY) AS ITEM_DEPTH,CAST(0 AS MONEY) AS ITEM_SIZE,'' as ITEM_LOCATION,'' as ITEM_SERI_NO,'' as ITEM_SERI_NO1,'' as ITEM_SERI_NO2,'' as ITEM_GRADE_ID,'' as ITEM_GRADE1_ID,'' as ITEM_STATUS_ID,DM_PR_DETAIL.CREATE_DATE,DM_SHIPPING_METHOD.SHIPPING_METHOD_NAME FROM dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.POS_SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID INNER JOIN SYS_TRAN ON POS_SALE.TRAN_ID=SYS_TRAN.TRAN_ID LEFT JOIN DM_SHIPPING_METHOD ON POS_SALE.SHIPPING_METHOD_ID=DM_SHIPPING_METHOD.SHIPPING_METHOD_ID LEFT JOIN DM_ITEM_OP ON POS_SALE_DETAIL.ITEM_OP_ID=DM_ITEM_OP.ITEM_OP_ID WHERE (POS_SALE_DETAIL.ITEM_OP_ID = '' OR DM_ITEM_OP.OP_TYPE='X') AND SYS_TRAN.IS_SALE=1 ) GO GO CREATE TABLE [dbo].[DM_PR_DETAIL_PHONE]( [PHONE] [nvarchar](20) NOT NULL, [PR_DETAIL_ID] [nvarchar](50) NOT NULL, [NOTES] [nvarchar](200) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_PR_DETAIL_PHONE] PRIMARY KEY CLUSTERED ( [PHONE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DM_PR_DETAIL_CLASS1]( [PR_DETAIL_CLASS1_ID] [nvarchar](20) NOT NULL, [PR_DETAIL_CLASS1_NAME] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_CUSTOMER_CLASS1] PRIMARY KEY CLUSTERED ( [PR_DETAIL_CLASS1_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO INSERT SYS_TABLE Values ( 'DM_PR_DETAIL_PHONE','PHONE','PR_DETAIL_ID','LIST',1,1,1,1,0,0,20,1,'') GO CREATE TABLE [dbo].[DM_PR_DETAIL_PHONE_LOG]( [PR_KEY] [uniqueidentifier] NOT NULL, [PHONE] [nvarchar](20) NOT NULL, [STATUS] [nvarchar](20) NOT NULL, [NOTES] [nvarchar](200) NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [TRAN_DATE] smalldatetime NOT NULL, CONSTRAINT [PK_DM_PR_DETAIL_PHONE_LOG] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO alter table dm_pr_detail add UNION_CARD nvarchar (50) not null default '' GO alter table dm_pr_detail add CH_PR_DETAIL_ID nvarchar (50) not null default '' GO ALTER TABLE DM_PR_DETAIL ADD IS_YOUTH_MEMBER SMALLINT DEFAULT 0 NOT NULL GO ALTER TABLE DM_PR_DETAIL ADD YOUTH_MEMBER_CARD NVARCHAR(30) DEFAULT '' NOT NULL GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('IS_GAS_CO', 0, N'Verision cho ty GAS', 'BOOLEAN', 'COMMON') GO alter table dm_pr_detail add PR_DETAIL_CLASS1_ID nvarchar(20) not null default '' GO alter table DM_PR_DETAIL add NAME_TRANSACTION NVARCHAR(100) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add EMAIL_TO NVARCHAR(500) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add EMAIL_CC NVARCHAR(500) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add DATE_DOB SMALLDATETIME DEFAULT GETDATE() NOT NULL GO alter table DM_PR_DETAIL add TYPE_PR_DETAIL NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add POSITION_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add PRIORITY_LEVEL_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add GROUP_SPECIALIZED_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add GROUP_PURPOSE_USE_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add DATE_SELL_FIRST SMALLDATETIME DEFAULT GETDATE() NOT NULL GO alter table DM_PR_DETAIL add NAME_BUILDING NVARCHAR(50) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add ADDRESS_DELIVERY NVARCHAR(200) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add DISTANCE_DELIVERY MONEY DEFAULT 0 NOT NULL GO alter table DM_PR_DETAIL add COORDINATE_CUSTOMER_X MONEY DEFAULT 0 NOT NULL GO alter table DM_PR_DETAIL add COORDINATE_CUSTOMER_Y MONEY DEFAULT 0 NOT NULL GO alter table DM_PR_DETAIL add SALE_CONTRACT_NO NVARCHAR(50) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add COMMENTS NVARCHAR(100) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add COMMENTS1 NVARCHAR(100) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add COMMENTS2 NVARCHAR(100) DEFAULT '' NOT NULL GO CREATE TABLE [dbo].[DM_PRIORITY_LEVEL]( [PRIORITY_LEVEL_ID] [nvarchar](20) NOT NULL, [PRIORITY_LEVEL_NAME] [nvarchar](50) NOT NULL, [COMMENTS] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_PRIORITY_LEVEL] PRIMARY KEY CLUSTERED ( [PRIORITY_LEVEL_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DM_GROUP_PURPOSE_USE]( [GROUP_PURPOSE_USE_ID] [nvarchar](20) NOT NULL, [GROUP_PURPOSE_USE_NAME] [nvarchar](50) NOT NULL, [COMMENTS] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_GROUP_PURPOSE_USE] PRIMARY KEY CLUSTERED ( [GROUP_PURPOSE_USE_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO alter table DM_PR_DETAIL add CYCLE_USER SMALLINT not null default 0 GO alter table DM_PR_DETAIL add ALLOW_DEBT SMALLINT DEFAULT 0 NOT NULL GO alter table DM_PR_DETAIL add CREDIT_LIMIT MONEY DEFAULT 0 NOT NULL GO alter table DM_PR_DETAIL add MOBILE_PHONE NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add CONTACT_POSITION NVARCHAR(50) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add SKYPE_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add WEBSITE NVARCHAR(50) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add IS_PUBLIC SMALLINT DEFAULT 0 NOT NULL GO alter table DM_PR_DETAIL add UNION_CARD NVARCHAR(50) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add SALE_DIRECTOR_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add ASM_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add SALE_MANAGER_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add CLASS_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add ITEM_CLASS_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add RECEIVE_PRICE_LEVEL_ID NVARCHAR(20) DEFAULT '' NOT NULL GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('CYCLE_WARNING_GAS','POS','POS_SALE','LIST','','Ins_Period.png',80,'CYCLE_WARNING_GAS',1,1) GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_PR_DETAIL_CLASS1' ,'PR_DETAIL_CLASS1_ID' ,'PR_DETAIL_CLASS1_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO alter table DM_PR_DETAIL add ADDRESS_DELIVERY NVARCHAR(200) DEFAULT '' NOT NULL GO alter table DM_PR_DETAIL add LIST_ITEM NVARCHAR(100) DEFAULT '' NOT NULL GO CREATE TABLE [dbo].[PR_DETAIL_ITEM]( [PR_DETAIL_ID] [nvarchar](20) NOT NULL, [ITEM_ID] [nvarchar](20) NOT NULL, [ITEM_NAME] [nvarchar](200) NOT NULL, CONSTRAINT [PK_PR_DETAIL_ITEM] PRIMARY KEY CLUSTERED ( [PR_DETAIL_ID] ASC, [ITEM_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[POS_SALE_VIEW_GAS]')) DROP VIEW [dbo].[POS_SALE_VIEW_GAS] GO CREATE VIEW [dbo].[POS_SALE_VIEW_GAS] AS SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE.ORGANIZATION_ID, dbo.POS_SALE.TRAN_ID, dbo.POS_SALE.TRAN_NO, dbo.POS_SALE.TRAN_DATE, dbo.POS_SALE.TRAN_HOUR, dbo.POS_SALE.TRAN_MINUTE, dbo.POS_SALE.SO_TRAN_ID, dbo.POS_SALE.SO_TRAN_NO, dbo.POS_SALE.VAT_TRAN_NO, dbo.POS_SALE.VAT_TRAN_DATE, dbo.POS_SALE.VAT_TRAN_SERIE, dbo.POS_SALE.VAT_PURCHASE_ID, dbo.POS_SALE.ITEM_SOURCE_ID, dbo.POS_SALE.WAREHOUSE_ID_RECEIVE, dbo.POS_SALE.EMPLOYEE_ID, dbo.POS_SALE.MARKET_ID, dbo.POS_SALE.CURRENCY_ID, dbo.POS_SALE.EXCHANGE_RATE, dbo.POS_SALE.PR_DETAIL_ID, dbo.POS_SALE.REC_PR_DETAIL_ID, dbo.POS_SALE.CONTACT_PERSON, dbo.POS_SALE.ADDRESS, dbo.POS_SALE.TAX_FILE_NUMBER, dbo.POS_SALE.PAYMENT_METHOD_ID, dbo.POS_SALE.PAYMENT_TERM_ID, dbo.POS_SALE.PAYMENT_DATE, dbo.POS_SALE.COMMENTS, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH,dbo.DM_ITEM.ITEM_IMAGE, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.WARRANTY_PERIOD, dbo.POS_SALE.POS_SHIFT_PR_KEY, dbo.POS_SALE.PR_DETAIL_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID,DM_PR_DETAIL.CYCLE_USER, dbo.POS_SALE.TAX_OFFICE_ID, dbo.POS_SALE.SO_TRAN_DATE, dbo.POS_SALE.SO_TRAN_HOUR, dbo.POS_SALE.SO_TRAN_MINUTE, dbo.POS_SALE.PR_KEY_SO, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.POS_SALE_DETAIL.PR_KEY, dbo.POS_SALE_DETAIL.FR_KEY, dbo.POS_SALE_DETAIL.LIST_ORDER, dbo.POS_SALE_DETAIL.LOT_NO, dbo.POS_SALE_DETAIL.MANU_DATE, dbo.POS_SALE_DETAIL.RECEIVE_DATE, dbo.POS_SALE_DETAIL.EXPIRED_TERM, dbo.POS_SALE_DETAIL.EXPIRED_DATE, dbo.POS_SALE_DETAIL.ITEM_ID, dbo.POS_SALE_DETAIL.DESCRIPTION, dbo.POS_SALE_DETAIL.UNIT_ID, dbo.POS_SALE_DETAIL.QUANTITY, dbo.POS_SALE_DETAIL.QUANTITY_EXTRA, dbo.POS_SALE_DETAIL.QUANTITY_WH, dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.UNIT_PRICE, dbo.POS_SALE_DETAIL.UNIT_PRICE_WH, dbo.POS_SALE_DETAIL.AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_ID, dbo.POS_SALE_DETAIL.VAT_TAX_RATE, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EXPORT_TAX_RATE, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.LUX_TAX_RATE, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION, dbo.POS_SALE_DETAIL.SALE_COST, dbo.POS_SALE_DETAIL.SALE_COST_ORIG, dbo.POS_SALE_DETAIL.DISCOUNT_RATE, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_SALE_DETAIL.FIXED_AMOUNT, dbo.POS_SALE_DETAIL.ITEM_OP_ID, dbo.POS_SALE_DETAIL.WAREHOUSE_ID, dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM_OP.OP_TYPE, dbo.POS_SALE_DETAIL.ITEM_ID + dbo.POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, dbo.POS_SHIFT.SHIFT_ID, dbo.POS_SHIFT.SHIFT_DATE, dbo.DM_SHIFT.SHIFT_NAME, dbo.DM_SHIFT.START_HOUR, dbo.DM_SHIFT.START_MINUTE, dbo.DM_SHIFT.END_HOUR, dbo.DM_SHIFT.END_MINUTE FROM dbo.DM_SHIFT RIGHT OUTER JOIN dbo.POS_SHIFT ON dbo.DM_SHIFT.SHIFT_ID = dbo.POS_SHIFT.SHIFT_ID RIGHT OUTER JOIN dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY ON dbo.POS_SHIFT.PR_KEY = dbo.POS_SALE.POS_SHIFT_PR_KEY LEFT OUTER JOIN dbo.HR_EMPLOYEE_INFO ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.POS_SALE.EMPLOYEE_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.DM_ITEM.ITEM_ID = dbo.POS_SALE_DETAIL.ITEM_ID LEFT OUTER JOIN dbo.DM_ITEM_OP ON dbo.DM_ITEM_OP.ITEM_OP_ID = dbo.POS_SALE_DETAIL.ITEM_OP_ID GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('DM_PR_DETAIL_CLASS1','POS','POS_SALE','LIST','','',80,'DM_PR_DETAIL_CLASS1',1,1) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('POS1_DM_PR_DETAIL','POS','POS_SALE','LIST','','',80,'DM_PR_DETAIL',1,1) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('DM_PR_DETAIL_PHONE_CALLING','POS','POS_SALE','LIST','','Ins_Period.png',80,'DM_PR_DETAIL_PHONE_CALLING',1,1) GO alter table DM_COMMUNICATE_CONFIG add IS_DOWNLOAD SMALLINT NOT NULL default 0 GO GO CREATE VIEW [dbo].[POS_SALE_VIEW_GAS] AS SELECT dbo.POS_SALE.PR_KEY AS PR_KEY_CTU, dbo.POS_SALE.ORGANIZATION_ID, dbo.POS_SALE.TRAN_ID, dbo.POS_SALE.TRAN_NO, dbo.POS_SALE.TRAN_DATE, dbo.POS_SALE.TRAN_HOUR, dbo.POS_SALE.TRAN_MINUTE, dbo.POS_SALE.SO_TRAN_ID, dbo.POS_SALE.SO_TRAN_NO, dbo.POS_SALE.VAT_TRAN_NO, dbo.POS_SALE.VAT_TRAN_DATE, dbo.POS_SALE.VAT_TRAN_SERIE, dbo.POS_SALE.VAT_PURCHASE_ID, dbo.POS_SALE.ITEM_SOURCE_ID, dbo.POS_SALE.WAREHOUSE_ID_RECEIVE, dbo.POS_SALE.EMPLOYEE_ID, dbo.POS_SALE.MARKET_ID, dbo.POS_SALE.CURRENCY_ID, dbo.POS_SALE.EXCHANGE_RATE, dbo.POS_SALE.PR_DETAIL_ID, dbo.POS_SALE.REC_PR_DETAIL_ID, dbo.POS_SALE.CONTACT_PERSON, dbo.POS_SALE.ADDRESS, dbo.POS_SALE.TAX_FILE_NUMBER, dbo.POS_SALE.PAYMENT_METHOD_ID, dbo.POS_SALE.PAYMENT_TERM_ID, dbo.POS_SALE.PAYMENT_DATE, dbo.POS_SALE.COMMENTS, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH,dbo.DM_ITEM.ITEM_IMAGE, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.WARRANTY_PERIOD, dbo.POS_SALE.POS_SHIFT_PR_KEY, dbo.POS_SALE.PR_DETAIL_NAME, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.POS_SALE.TAX_OFFICE_ID, dbo.POS_SALE.SO_TRAN_DATE, dbo.POS_SALE.SO_TRAN_HOUR, dbo.POS_SALE.SO_TRAN_MINUTE, dbo.POS_SALE.PR_KEY_SO, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.POS_SALE_DETAIL.PR_KEY, dbo.POS_SALE_DETAIL.FR_KEY, dbo.POS_SALE_DETAIL.LIST_ORDER, dbo.POS_SALE_DETAIL.LOT_NO, dbo.POS_SALE_DETAIL.MANU_DATE, dbo.POS_SALE_DETAIL.RECEIVE_DATE, dbo.POS_SALE_DETAIL.EXPIRED_TERM, dbo.POS_SALE_DETAIL.EXPIRED_DATE, dbo.POS_SALE_DETAIL.ITEM_ID, dbo.POS_SALE_DETAIL.DESCRIPTION, dbo.POS_SALE_DETAIL.UNIT_ID, dbo.POS_SALE_DETAIL.QUANTITY, dbo.POS_SALE_DETAIL.QUANTITY_EXTRA, dbo.POS_SALE_DETAIL.QUANTITY_WH, dbo.POS_SALE_DETAIL.UNIT_PRICE_ORIG, dbo.POS_SALE_DETAIL.UNIT_PRICE, dbo.POS_SALE_DETAIL.UNIT_PRICE_WH, dbo.POS_SALE_DETAIL.AMOUNT_ORIG, dbo.POS_SALE_DETAIL.AMOUNT, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_ID, dbo.POS_SALE_DETAIL.VAT_TAX_RATE, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EXPORT_TAX_RATE, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.POS_SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.LUX_TAX_RATE, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT, dbo.POS_SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.POS_SALE_DETAIL.EMPLOYEE_COMMISSION, dbo.POS_SALE_DETAIL.SALE_COST, dbo.POS_SALE_DETAIL.SALE_COST_ORIG, dbo.POS_SALE_DETAIL.DISCOUNT_RATE, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT, dbo.POS_SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT, dbo.POS_SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.POS_SALE_DETAIL.FIXED_UNIT_PRICE, dbo.POS_SALE_DETAIL.FIXED_AMOUNT, dbo.POS_SALE_DETAIL.ITEM_OP_ID, dbo.POS_SALE_DETAIL.WAREHOUSE_ID, dbo.POS_SALE_DETAIL.PRICE_LEVEL_ID, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM_OP.OP_TYPE, dbo.POS_SALE_DETAIL.ITEM_ID + dbo.POS_SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, dbo.POS_SHIFT.SHIFT_ID, dbo.POS_SHIFT.SHIFT_DATE, dbo.DM_SHIFT.SHIFT_NAME, dbo.DM_SHIFT.START_HOUR, dbo.DM_SHIFT.START_MINUTE, dbo.DM_SHIFT.END_HOUR, dbo.DM_SHIFT.END_MINUTE FROM dbo.DM_SHIFT RIGHT OUTER JOIN dbo.POS_SHIFT ON dbo.DM_SHIFT.SHIFT_ID = dbo.POS_SHIFT.SHIFT_ID RIGHT OUTER JOIN dbo.POS_SALE INNER JOIN dbo.POS_SALE_DETAIL ON dbo.POS_SALE.PR_KEY = dbo.POS_SALE_DETAIL.FR_KEY ON dbo.POS_SHIFT.PR_KEY = dbo.POS_SALE.POS_SHIFT_PR_KEY LEFT OUTER JOIN dbo.HR_EMPLOYEE_INFO ON dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID = dbo.POS_SALE.EMPLOYEE_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.POS_SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.DM_ITEM.ITEM_ID = dbo.POS_SALE_DETAIL.ITEM_ID LEFT OUTER JOIN dbo.DM_ITEM_OP ON dbo.DM_ITEM_OP.ITEM_OP_ID = dbo.POS_SALE_DETAIL.ITEM_OP_ID GO DROP VIEW PAYMENT_BALANCE_VIEW GO CREATE VIEW [dbo].[PAYMENT_BALANCE_VIEW] AS (SELECT BALANCE.PR_KEY,BALANCE_DETAIL.PR_KEY AS PR_KEY_DETAIL,TRAN_ID,ORGANIZATION_ID,TRAN_DATE AS BALANCE_DATE,TRAN_DATE, TRAN_DATE AS PAYMENT_DATE,'' AS TRAN_NO,'' AS VAT_TRAN_NO,'' AS DESCRIPTION,'' AS COMMENTS,'CRD' AS DEBIT_CREDIT,ACCOUNT_ID,'' AS ACCOUNT_ID_CONTRA,CURRENCY_ID,EXCHANGE_RATE, (EXCHANGE_RATE * 0 + 1) AS EXCHANGE_RATE_EXTRA,AMOUNT,AMOUNT_ORIG,AMOUNT_EXTRA,BALANCE_DETAIL.PR_DETAIL_ID,JOB_ID,BALANCE_DETAIL.ITEM_ID,PR_DETAIL_CLASS_ID ,DM_ITEM.ITEM_CLASS_ID,'' AS PAYMENT_TERM_ID, '' AS SBO_TRAN_ID,'' AS SBO_TRAN_NO FROM BALANCE INNER JOIN BALANCE_DETAIL ON BALANCE.PR_KEY=BALANCE_DETAIL.FR_KEY INNER JOIN DM_PR_DETAIL ON BALANCE_DETAIL.PR_DETAIL_ID=DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM ON BALANCE_DETAIL.ITEM_ID=DM_ITEM.ITEM_ID WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM DM_ACCOUNT WHERE IS_PAYMENT=1 AND ACCOUNT_TYPE_ID IN ('03','04','06')) AND (BALANCE.AMOUNT_CREDIT <> 0 OR BALANCE.AMOUNT_CREDIT_EXTRA <> 0)) UNION ALL (SELECT BALANCE.PR_KEY,BALANCE_DETAIL.PR_KEY AS PR_KEY_DETAIL,TRAN_ID,ORGANIZATION_ID,TRAN_DATE AS BALANCE_DATE,TRAN_DATE,TRAN_DATE AS PAYMENT_DATE,'' AS TRAN_NO,'' AS VAT_TRAN_NO,'' AS DESCRIPTION,'' AS COMMENTS,'DEB' AS DEBIT_CREDIT,ACCOUNT_ID,'' AS ACCOUNT_ID_CONTRA,CURRENCY_ID,EXCHANGE_RATE, (EXCHANGE_RATE * 0 + 1) AS EXCHANGE_RATE_EXTRA,AMOUNT,AMOUNT_ORIG,AMOUNT_EXTRA,BALANCE_DETAIL.PR_DETAIL_ID,JOB_ID,BALANCE_DETAIL.ITEM_ID,PR_DETAIL_CLASS_ID ,DM_ITEM.ITEM_CLASS_ID,'' AS PAYMENT_TERM_ID, '' AS SBO_TRAN_ID,'' AS SBO_TRAN_NO FROM BALANCE INNER JOIN BALANCE_DETAIL ON BALANCE.PR_KEY=BALANCE_DETAIL.FR_KEY INNER JOIN DM_PR_DETAIL ON BALANCE_DETAIL.PR_DETAIL_ID= DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM ON BALANCE_DETAIL.ITEM_ID=DM_ITEM.ITEM_ID WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM DM_ACCOUNT WHERE IS_PAYMENT=1 AND ACCOUNT_TYPE_ID IN ('10','13','14')) AND AMOUNT_DEBIT > 0) UNION ALL (SELECT LEDGER.PR_KEY,LEDGER.PR_KEY_DETAIL,LEDGER.TRAN_ID,LEDGER.ORGANIZATION_ID,LEDGER.TRAN_DATE AS BALANCE_DATE, LEDGER.TRAN_DATE,LEDGER.PAYMENT_DATE, LEDGER.TRAN_NO,LEDGER.VAT_TRAN_NO,LEDGER.DESCRIPTION,LEDGER.COMMENTS,LEDGER.DEBIT_CREDIT,LEDGER.ACCOUNT_ID,LEDGER.ACCOUNT_ID_CONTRA,LEDGER.CURRENCY_ID,LEDGER.EXCHANGE_RATE, LEDGER.EXCHANGE_RATE_EXTRA,AMOUNT,AMOUNT_ORIG,AMOUNT_EXTRA,LEDGER.PR_DETAIL_ID,LEDGER.JOB_ID,LEDGER.ITEM_ID,DM_PR_DETAIL.PR_DETAIL_CLASS_ID ,DM_ITEM.ITEM_CLASS_ID,LEDGER.PAYMENT_TERM_ID, SALE.SBO_TRAN_ID,SALE.SBO_TRAN_NO FROM LEDGER INNER JOIN DM_PR_DETAIL ON LEDGER.PR_DETAIL_ID=DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN SALE ON LEDGER.PR_KEY=SALE.PR_KEY LEFT JOIN DM_ITEM ON LEDGER.ITEM_ID=DM_ITEM.ITEM_ID WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM DM_ACCOUNT WHERE IS_PAYMENT=1) AND LEDGER.TRAN_ID IN ('SB','PB')) UNION ALL (SELECT PR_KEY_LEDGER AS PR_KEY,PR_KEY_DETAIL, TRAN_ID,ORGANIZATION_ID,BALANCE_DATE,TRAN_DATE,PAYMENT_DATE,TRAN_NO,VAT_TRAN_NO,PAYMENT_BALANCE.DESCRIPTION,PAYMENT_BALANCE.COMMENTS,DEBIT_CREDIT,ACCOUNT_ID,ACCOUNT_ID_CONTRA,CURRENCY_ID,EXCHANGE_RATE, EXCHANGE_RATE_EXTRA,AMOUNT,AMOUNT_ORIG,AMOUNT_EXTRA,PAYMENT_BALANCE.PR_DETAIL_ID,JOB_ID,PAYMENT_BALANCE.ITEM_ID,DM_PR_DETAIL.PR_DETAIL_CLASS_ID ,DM_ITEM.ITEM_CLASS_ID, PAYMENT_TERM_ID,SBO_TRAN_ID,SBO_TRAN_NO FROM PAYMENT_BALANCE LEFT JOIN DM_PR_DETAIL ON PAYMENT_BALANCE.PR_DETAIL_ID=DM_PR_DETAIL.PR_DETAIL_ID LEFT JOIN DM_ITEM ON PAYMENT_BALANCE.ITEM_ID=DM_ITEM.ITEM_ID WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM DM_ACCOUNT WHERE IS_PAYMENT=1)) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_PR_DETAIL_PHONE',1,1,1,1,GETDATE(),'ADMIN',1,1) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_PR_DETAIL_CLASS1',1,1,1,1,GETDATE(),'ADMIN',1,1) GO --5/2/2018 GO alter table DM_PR_DETAIL_PHONE add DATE_ADD SMALLDATETIME not null default GETDATE() GO alter table DM_POSITION add ORGANIZATION_ID NVARCHAR(20) DEFAULT '' NOT NULL GO alter table DM_POSITION add HR_ORGANIZATION_TYPE NVARCHAR(100) DEFAULT '' NOT NULL GO alter table PR_DETAIL_ITEM add ITEM_IMAGE IMAGE NULL GO alter table PR_DETAIL_ITEM add COMMENTS NVARCHAR(100) DEFAULT '' NOT NULL GO --8/2/2018 GO alter table DM_PR_DETAIL_PHONE_LOG add TIME_START SMALLDATETIME not null default GETDATE() GO alter table DM_PR_DETAIL_PHONE_LOG add TIME_END SMALLDATETIME not null default GETDATE() GO alter table DM_PR_DETAIL_PHONE_LOG add TIME_CALLING NVARCHAR(8) not null default '' GO alter table DM_PR_DETAIL_PHONE_LOG add LINK_RECORD nvarchar(1000) not null default '' GO alter table DM_PR_DETAIL_PHONE_LOG add TYPE_CALL nvarchar(20) not null default '' GO --01/03/2018 GO alter table DM_PR_DETAIL_PHONE add IS_MOBILE SMALLINT not null default 1 GO alter table HR_EMPLOYEE_INFO add POSITION_ID NVARCHAR(20) not null default '' GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('LIST_ITEM_MAIN', '', N'Danh sach mat hang chinh', 'STRING', 'COMMON') GO CREATE TABLE [dbo].[DM_INDUSTRIAL_ZONE]( [INDUSTRIAL_ZONE_ID] [nvarchar](20) NOT NULL, [INDUSTRIAL_ZONE_NAME] [nvarchar](50) NOT NULL, [INDUSTRIAL_ZONE_NAME_EN] [nvarchar](50) NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, [ACTIVE] [smallint] NOT NULL, [PROVINCE_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_INDUSTRIAL_ZONE] PRIMARY KEY CLUSTERED ( [INDUSTRIAL_ZONE_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_INDUSTRIAL_ZONE' ,'INDUSTRIAL_ZONE_ID' ,'INDUSTRIAL_ZONE_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO --09/03/2018 GO CREATE TABLE [dbo].[DM_VEHICLE_TYPE_OF]( [VEHICLE_TYPE_OF_ID] [nvarchar](20) NOT NULL, [VEHICLE_TYPE_OF_NAME] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_VEHICLE_TYPE_OF] PRIMARY KEY CLUSTERED ( [VEHICLE_TYPE_OF_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO alter table DM_VEHICLE add PR_DETAIL_ID NVARCHAR(20) not null default '' GO alter table DM_VEHICLE add VEHICLE_TYPE_OF_ID NVARCHAR(20) not null default '' GO alter table POS_SALE add VEHICLE_ID NVARCHAR(20) null default '' GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_VEHICLE_TYPE_OF' ,'VEHICLE_TYPE_OF_ID' ,'VEHICLE_TYPE_OF_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO GO CREATE TABLE [dbo].[SYS_CONFIG_FORMLAYOUT]( [FORM_NAME] [nvarchar](100) NOT NULL, [CONFIG] [nvarchar](max) NOT NULL, CONSTRAINT [PK_SYS_CONFIG_FORMLAYOUT] PRIMARY KEY CLUSTERED ( [FORM_NAME] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO alter table POS_SALE add SHIPPING_METHOD_ID NVARCHAR(20) not null default '' GO alter table POS_SALE add NAME_DRIVER NVARCHAR(100) not null default '' GO --20/04/2018 GO alter table DM_PR_DETAIL add DATE_MODIFY SMALLDATETIME null default GETDATE() GO ALTER TABLE PR_DETAIL_ITEM ADD PR_KEY UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL GO ----------------------------------------22/05---------------------------------------------------------- GO insert into SYS_MENU_MAPPING(PR_KEY,FUNCTION_ID,MENU_ID) values((SELECT MAX(PR_KEY)+1 FROM SYS_MENU_MAPPING),'AUTOCLAUSE','FIN_GL_AUTOCLAUSE') GO insert into SYS_MENU values('FIN_GL_AUTOCLAUSE','FIN','FIN_GL','FUNC','GLGROUP1','',0,'AUTOCLAUSE',9,0) GO INSERT INTO [SYS_TABLE]([TABLE_NAME],[ID_FIELD],[NAME_FIELD],[TABLE_TYPE],[BACKUPS],[NUM_ORDER],[RES_ORDER],[CAN_GROUP],[ID_AUTO],[ID_MASK],[ID_LENGTH],[ID_PARTS],[ID_SPLIT])VALUES('DM_PR_DETAIL_PHONE_LOG','PR_KEY','PHONE','LIST','1','1','1','1','0','0','20','1' ,'') GO --31/05/2018 GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_PR_DETAIL_PHONE_LOG' ,'PR_KEY' ,'PHONE' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_PR_DETAIL_PHONE_LOG',1,1,1,1,GETDATE(),'ADMIN',1,1,0) GO UPDATE DM_COMMUNICATE_CONFIG SET IS_NEW=1,IS_EDIT=1,IS_DELETE=1,IS_PUBLIC=1 WHERE TABLE_NAME='DM_PR_DETAIL_PHONE_LOG' GO CREATE TABLE [dbo].[POS_NOTIFICATIONS_CONFIG]( [NOTIFICATIONS_ID] [nvarchar](20) NOT NULL, [NOTIFICATIONS_TYPE] [nvarchar](20) NOT NULL, [INDEX_TYPE] [smallint] NOT NULL, [PR_KEY_REPORT] [money] NOT NULL, [COMMENTS] [nvarchar](200) NOT NULL, [FORM_SMS] [nvarchar](200) NOT NULL, [FORM_EMAIL] [nvarchar](max) NOT NULL, [IS_SEND_EMAIL] [smallint] NOT NULL, [IS_SEND_SMS] [smallint] NOT NULL, [LINK_API_SMS] [nvarchar](200) NOT NULL, [SEND_TO_OTHER] [nvarchar](300) NOT NULL, [SEND_CC] [nvarchar](300) NOT NULL, [SEND_BCC] [nvarchar](300) NOT NULL, [MOBILE_PHONE_OTHER] [nvarchar](300) NOT NULL, [DATE_MODIFIED] [smalldatetime] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [BLOCK_TIME_SEND] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_POS_NOTIFICATIONS_CONFIG_1] PRIMARY KEY CLUSTERED ( [NOTIFICATIONS_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[POS_NOTIFICATIONS_LOG]( [PR_KEY] [uniqueidentifier] NOT NULL, [FR_KEY] [uniqueidentifier] NOT NULL, [COMPUTER] [nvarchar](100) NOT NULL, [COMPUTER_IP] [nvarchar](50) NOT NULL, [NOTIFICATIONS_ID] [nvarchar](50) NOT NULL, [NOTIFICATIONS_TYPE] [nvarchar](20) NOT NULL, [PR_DETAIL_ID] [nvarchar](20) NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, [COMMENTS] [nvarchar](1000) NOT NULL, [IS_SEND_EMAIL] [smallint] NOT NULL, [IS_SEND_SMS] [smallint] NOT NULL, [PHONE_SEND] [nvarchar](15) NOT NULL, [SEND_TO] [nvarchar](300) NOT NULL, [SEND_CC] [nvarchar](300) NOT NULL, [SEND_BCC] [nvarchar](300) NOT NULL, [DATE_SENT] [smalldatetime] NOT NULL, [TIME_SENT] [smalldatetime] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK__POS_NOTI__0C3D72E13A349AA3] PRIMARY KEY CLUSTERED ( [TIME_SENT] DESC, [NOTIFICATIONS_ID] ASC, [PR_DETAIL_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('POS_NOTIFICATIONS_CONFIG',1,1,1,1,GETDATE(),'ADMIN',1,1,0) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('POS_NOTIFICATIONS_LOG',1,1,1,1,GETDATE(),'ADMIN',1,1,0) GO GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('POS_NOTIFICATIONS_CONFIG','POS','POS_SALE','LIST','','',80,'POS_NOTIFICATIONS_CONFIG',1,1) GO GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('POS_NOTIFICATIONS_LOG','POS','POS_SALE','LIST','','',80,'POS_NOTIFICATIONS_LOG',1,1) GO GO INSERT INTO [SYS_SYSTEMVAR] VALUES('SEND_SMS_LINK_API','',N'Đường link gửi tin nhắn','STRING', 'COMMON') GO INSERT INTO [SYS_SYSTEMVAR] VALUES('SEND_SMS_USER_ID','',N'ên đăng nhập gửi sms','STRING', 'COMMON') GO INSERT INTO [SYS_SYSTEMVAR] VALUES('SEND_SMS_PASS','',N'Mật khẩu gửi sms','STRING', 'COMMON') GO alter table POS_NOTIFICATIONS_CONFIG add IS_TV SMALLINT not null default 0 GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_NOTIFICATIONS_CONFIG','POS_NOTIFICATIONS_CONFIG') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_NOTIFICATIONS_LOG','POS_NOTIFICATIONS_LOG') GO --06/06/2018 GO alter table POS_NOTIFICATIONS_CONFIG add LIST_ORG NVARCHAR(3000) not null default '' GO --07/06/2018 GO alter table DM_PR_DETAIL_PHONE add PR_KEY uniqueidentifier not null default NEWID() GO INSERT SYS_TABLE Values ( 'DM_PR_DETAIL_PHONE','PHONE','PR_DETAIL_ID','LIST',1,1,1,1,0,0,20,1,'') GO UPDATE SYS_TABLE SET ID_FIELD = 'PR_KEY',NAME_FIELD = 'PHONE' WHERE TABLE_NAME = 'DM_PR_DETAIL_PHONE' GO --12/06/2018 GO CREATE procedure [dbo].[GETDATETIME_NOW] @DateTimeServer DateTime output as begin transaction Select @DateTimeServer= GETDATE() commit transaction GO --13/06/2018 GO DECLARE @ConstraintName nvarchar(200) select @ConstraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'POS_NOTIFICATIONS_LOG' and COLUMN_NAME = 'TIME_SENT' IF @ConstraintName IS NOT NULL EXEC('alter table POS_NOTIFICATIONS_LOG drop CONSTRAINT ' + @ConstraintName) GO ALTER TABLE POS_NOTIFICATIONS_LOG ADD PRIMARY KEY (PR_KEY); GO ALTER TABLE POS_NOTIFICATIONS_LOG ALTER COLUMN TIME_SENT datetime not null go --14/06/2018 GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL', '', N'Địa chỉ email hệ thống', 'STRING', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL_PASSS', '', N'Pass Địa chỉ email hệ thống', 'STRING', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL_HOST', 'smtp.gmail.com', N'Host Server Email', 'STRING', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL_PORT', '587', N'Port Server Email', 'STRING', 'HRM') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('HRM_MAIN_EMAIL_TO_OTHER', '587', N'Địa chỉ email nhận kèm', 'STRING', 'HRM') GO --18/06/2018 GO alter table POS_NOTIFICATIONS_CONFIG add LIST_PR_DETAIL_CLASS_ID NVARCHAR(200) not null default '' GO alter table POS_NOTIFICATIONS_CONFIG add IS_SEND_CHT smallint not null default 1 GO alter table POS_NOTIFICATIONS_CONFIG add IS_SEND_QLKH smallint not null default 0 GO alter table POS_NOTIFICATIONS_LOG add TRAN_ID NVARCHAR(20) not null default '' GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('DATE_BEFORE_POS_SALE', '3', N'Số ngày được lập trước hóa đơn bán lẻ', 'INT', 'COMMON') GO --30/06/2018 GO CREATE TABLE [dbo].[DM_PHONE_INTERNAL]( [PHONE] [nvarchar](20) NOT NULL, [NAME] [nvarchar](100) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_PHONE_INTERNAL] PRIMARY KEY CLUSTERED ( [PHONE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('DM_PHONE_INTERNAL','POS','POS_SALE','LIST','','',80,'DM_PHONE_INTERNAL',1,1) GO INSERT INTO DM_COMMUNICATE_CONFIG VALUES('DM_PHONE_INTERNAL',1,1,1,1,GETDATE(),'ADMIN',1,1,0) GO --23/07/2018 GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'CYCLE_WARNING_GAS','CYCLE_WARNING_GAS') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'DM_PR_DETAIL','POS1_DM_PR_DETAIL') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'DM_PHONE_INTERNAL','DM_PHONE_INTERNAL') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'DM_PR_DETAIL_CLASS1','DM_PR_DETAIL_CLASS1') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'DM_PR_DETAIL_PHONE_CALLING','DM_PR_DETAIL_PHONE_CALLING') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_DEPOSIT_KC','POS_DEPOSIT_KC') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_DEPOSIT_TKC','POS_DEPOSIT_TKC') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'BB_CNO_AR','POS_SALE_ACCOUNTSUMMARY') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'ADJUSTMENT','POS_SALE_INVENTORY') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'WAREHOUSE_LISTING','POS_SALE_WAREHOUSE_LISTING') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_SALE_LISTING','POS_SALE_XKKVCNB') GO --25/07/2018 GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('POS_SEND_SMS_CD','POS','POS_SALE','LIST','','',80,'POS_SEND_SMS_CD',1,1) GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_SEND_SMS_CD','POS_SEND_SMS_CD') GO declare @j int select @j = MAX(PR_KEY) + 1 from SYS_MENU_MAPPING insert into SYS_MENU_MAPPING values(@j,'POS_SEND_SMS','POS_SEND_SMS_CD') GO --26/07/2018 GO CREATE TABLE [dbo].[POS_SEND_SMS_CD]( [PR_KEY] [uniqueidentifier] NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [TRAN_ID] [nvarchar](20) NULL, [TRAN_DATE] [smalldatetime] NOT NULL, [TRAN_NO] [nvarchar](20) NULL, [CONTENT_SMS] [nvarchar](1500) NOT NULL, [NAME_FILE] [nvarchar](200) NOT NULL, [SYSTEM_FILE] [varbinary](max) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [STATUS_SMS] [nvarchar](20) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_POS_SEND_SMS_CD] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[POS_SEND_SMS_CD_DETAIL]( [PR_KEY] [uniqueidentifier] NOT NULL, [FR_KEY] [uniqueidentifier] NOT NULL, [PHONE] [nvarchar](11) NOT NULL, [CONTENT_SMS] [nvarchar](1500) NOT NULL, [PR_DETAIL_ID] [nvarchar](20) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [TYPE_NEW] [nvarchar](20) NOT NULL, CONSTRAINT [PK_POS_SEND_SMS_CD_DETAIL] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_TRAN_CLASS (TRAN_CLASS, TRAN_CLASS_NAME,ACTIVE,USER_ID) VALUES ('POS_SEND_SMS_CD' ,N'Gửi tin nhắn chủ động' ,1 ,'ADMIN') GO INSERT INTO [SYS_TRAN] ([TRAN_ID],[TRAN_NAME],[TRAN_CLASS],[TRAN_SUB_CLASS],[LIST_ORDER],[OUTPUT_FORM],[POSTED],[MODULE_ID],[PROJECT_ID],[USER_ID],[ACTIVE],[SHOW_IN_MENU], [IS_SALE],[IS_LISTING]) VALUES ('POS_SEND_SMS_CD' ,N'Gửi tin nhắn chủ động' ,'POS_SEND_SMS_CD' ,'POS_SEND_SMS_CD' ,1 ,'FRMPOS_SENDSMS_CD' ,1 ,'POS_SALE' ,'POS' ,'ADMIN' ,1 ,0 ,0 ,1) GO --02/10/2018 GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('DM_CONSTRUCTION','POS','POS_SALE','LIST','','',80,'DM_CONSTRUCTION',1,1) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('DM_CATEGORIES_TEST','POS','POS_SALE','LIST','','',80,'DM_CATEGORIES_TEST',1,1) GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_CATEGORIES_TEST' ,'CATEGORIES_TEST_ID' ,'CATEGORIES_TEST_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO CREATE TABLE [dbo].[DM_CATEGORIES_TEST]( [CATEGORIES_TEST_ID] [nvarchar](20) NOT NULL, [CATEGORIES_TEST_NAME] [nvarchar](100) NOT NULL, [COMMENTS] [nvarchar](50) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_CATEGORIES_TEST] PRIMARY KEY CLUSTERED ( [CATEGORIES_TEST_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DM_CONSTRUCTION]( [CONSTRUCTION_ID] [nvarchar](20) NOT NULL, [CONSTRUCTION_NAME] [nvarchar](100) NOT NULL, [PR_DETAIL_ID] [nvarchar](20) NOT NULL, [CONSTRUCTION_TYPE] [nvarchar](20) NOT NULL, [FORM_INVESTMENT] [nvarchar](20) NOT NULL, [ASSET_ID_LIST] [nvarchar](1000) NOT NULL, [DATE_MAINTENANCE_FIRSTLY] [smalldatetime] NOT NULL, [CYCLE] [int] NOT NULL, [TIME_START] [smalldatetime] NOT NULL, [DESCRIPTION] [nvarchar](100) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [CATEGORIES_TEST_ID_LIST] [nvarchar](1000) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_DM_CONSTRUCTION] PRIMARY KEY CLUSTERED ( [CONSTRUCTION_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[PR_CONSTRUCTION_TEST]( [PR_KEY] [uniqueidentifier] NOT NULL, [TRAN_DATE] [smalldatetime] NOT NULL, [TRAN_NO] [nvarchar](20) NOT NULL, [TRAN_ID] [nvarchar](20) NOT NULL, [CONSTRUCTION_ID] [nvarchar](20) NOT NULL, [CLASSIFY_TEST] [nvarchar](20) NOT NULL, [ORGANIZATION_TEST] [nvarchar](100) NOT NULL, [EMPLOYEE_TEST] [nvarchar](100) NOT NULL, [ORGANIZATION_ID] [nvarchar](20) NOT NULL, [OPINION_CUSTOMER] [nvarchar](300) NOT NULL, [OPINION_ORGANIZATION_TEST] [nvarchar](300) NOT NULL, [IS_INTERNAL] [smallint] NOT NULL, [EMPLOYEE_ID] [nvarchar](20) NOT NULL, [EMPLOYEE_OUT] [nvarchar](100) NOT NULL, [REPRESENTATIVE] [nvarchar](100) NOT NULL, [FILE_SCAN] [varbinary](max) NOT NULL, [NAME_SCAN] [nvarchar](100) NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, [ACTIVE] [smallint] NOT NULL, [USER_ID] [nvarchar](20) NOT NULL, CONSTRAINT [PK_PR_CONSTRUCTION_TEST] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[PR_CONSTRUCTION_TEST_DETAIL]( [PR_KEY] [uniqueidentifier] NOT NULL, [FR_KEY] [uniqueidentifier] NOT NULL, [CATEGORIES_TEST_ID] [nvarchar](20) NOT NULL, [RESULT] [nvarchar](20) NOT NULL, [DATE_FINISH] [smalldatetime] NOT NULL, [COMMENTS] [nvarchar](100) NOT NULL, CONSTRAINT [PK_PR_CONSTRUCTION_TEST_DETAIL] PRIMARY KEY CLUSTERED ( [PR_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO SYS_TRAN_CLASS (TRAN_CLASS, TRAN_CLASS_NAME,ACTIVE,USER_ID) VALUES ('PR_CONSTRUCTION_TEST' ,N'Kiểm tra bảo dưỡng GAS' ,1 ,'ADMIN') GO INSERT INTO [SYS_TRAN] ([TRAN_ID],[TRAN_NAME],[TRAN_CLASS],[TRAN_SUB_CLASS],[LIST_ORDER],[OUTPUT_FORM],[POSTED],[MODULE_ID],[PROJECT_ID],[USER_ID],[ACTIVE],[SHOW_IN_MENU], [IS_SALE],[IS_LISTING]) VALUES ('PR_CONSTRUCTION_TEST' ,N'Kiểm tra bảo dưỡng GAS' ,'PR_CONSTRUCTION_TEST' ,'PR_CONSTRUCTION_TEST' ,1 ,'FRMPR_CONSTRUCTION_TEST' ,1 ,'POS_SALE' ,'POS' ,'ADMIN' ,1 ,0 ,0 ,1) GO INSERT INTO SYS_MENU ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('PR_CONSTRUCTION_TEST','POS','POS_SALE','LIST','','',80,'PR_CONSTRUCTION_TEST',1,1) GO INSERT INTO [SYS_TABLE] ([TABLE_NAME] ,[ID_FIELD] ,[NAME_FIELD] ,[TABLE_TYPE] ,[BACKUPS] ,[NUM_ORDER] ,[RES_ORDER] ,[CAN_GROUP] ,[ID_AUTO] ,[ID_MASK] ,[ID_LENGTH] ,[ID_PARTS] ,[ID_SPLIT]) VALUES ('DM_CONSTRUCTION' ,'CONSTRUCTION_ID' ,'CONSTRUCTION_NAME' ,'LIST' ,'1' ,'1' ,'1' ,'1' ,'0' ,'0' ,'20' ,'1' ,'') GO ALTER TABLE ADJUSTMENT ADD ITEM_GRADE_ID NVARCHAR(20) DEFAULT '' NOT NULL GO GO DROP VIEW [dbo].[ADJUSTMENT_VIEW] GO CREATE VIEW [dbo].[ADJUSTMENT_VIEW] AS SELECT dbo.ADJUSTMENT.PR_KEY, dbo.ADJUSTMENT.TRAN_ID, dbo.ADJUSTMENT.ADJUSTMENT_DATE, dbo.ADJUSTMENT.ACCOUNT_ID, dbo.ADJUSTMENT.WAREHOUSE_ID, dbo.ADJUSTMENT.ITEM_ID, dbo.ADJUSTMENT.BOOK_QUANTITY, dbo.ADJUSTMENT.BOOK_UNIT_PRICE, dbo.ADJUSTMENT.BOOK_AMOUNT, dbo.ADJUSTMENT.BOOK_AMOUNT_EXTRA, dbo.ADJUSTMENT.ACTUAL_QUANTITY, dbo.ADJUSTMENT.ACTUAL_UNIT_PRICE, dbo.ADJUSTMENT.ACTUAL_AMOUNT, dbo.ADJUSTMENT.ACTUAL_AMOUNT_EXTRA, dbo.ADJUSTMENT.DIFF_QUANTITY, dbo.ADJUSTMENT.DIFF_AMOUNT, dbo.ADJUSTMENT.DIFF_AMOUNT_EXTRA, dbo.ADJUSTMENT.COMMENTS, dbo.ADJUSTMENT.ORGANIZATION_ID, dbo.ADJUSTMENT.ITEM_SOURCE_ID, dbo.ADJUSTMENT.UNIT_ID, dbo.DM_WAREHOUSE.WAREHOUSE_NAME, dbo.DM_ITEM.ITEM_NAME, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.ORIGIN, dbo.ADJUSTMENT.ITEM_GRADE_ID FROM dbo.ADJUSTMENT LEFT OUTER JOIN dbo.DM_WAREHOUSE ON dbo.ADJUSTMENT.WAREHOUSE_ID = dbo.DM_WAREHOUSE.WAREHOUSE_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.ADJUSTMENT.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO ----------------------------------------24/12---------------------------------------------------------- GO ALTER TABLE POS_ITEM_PRICE_FOR_DELIVERY ADD SHIPPING_METHOD_ID NVARCHAR(20) NOT NULL DEFAULT '' GO --21/12/2018------- GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('LIST_ITEM_OP_ID_ORDER', '', N'Danh sách phương thức nhập xuất mặc định màn hình Lệnh xuất hàng', 'STRING', 'COMMON') GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('LIST_ITEM_ID_ORDER', '', N'Danh sách mặt hàng mặc định màn hình Lệnh xuất hàng', 'STRING', 'COMMON') GO alter table DM_PR_DETAIL_PHONE_LOG add IS_TONGDAI SMALLINT not null default 0 GO GO ALTER TABLE PURCHASE ADD SHIPPING_METHOD_ID NVARCHAR(20) NOT NULL DEFAULT '' GO ALTER TABLE POS_PURCHASE ADD SHIPPING_METHOD_ID NVARCHAR(20) NOT NULL DEFAULT '' GO INSERT INTO [dbo].[SYS_MENU] ([MENU_ID] ,[PROJECT_ID] ,[MODULE_ID] ,[MENU_TYPE] ,[MENU_GROUP] ,[MENU_ICON] ,[MENU_WIDTH] ,[MENU_TAG] ,[MENU_ORDER] ,[ACTIVE]) VALUES ('FIN_GL_PRINTMULTI' ,'FIN' ,'FIN_GL' ,'FUNC' ,'' ,'' ,0 ,'PRINTMULTI' ,1 ,1) GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('ALLOW_CHANGE_AFTER_PAYMENT', '0', N'ALLOW_CHANGE_AFTER_PAYMENT', 'STRING', 'COMMON') GO ALTER TABLE VAT_TRANSACTION ADD VAT_TRACKING_URL NVARCHAR(150) DEFAULT '' NOT NULL GO ALTER TABLE VAT_TRANSACTION ADD VAT_TRACKING_CODE NVARCHAR(50) DEFAULT '' NOT NULL GO ALTER TABLE PURCHASE ADD VAT_TRACKING_URL NVARCHAR(150) DEFAULT '' NOT NULL GO ALTER TABLE PURCHASE ADD VAT_TRACKING_CODE NVARCHAR(50) DEFAULT '' NOT NULL GO CREATE VIEW [dbo].[WAREHOUSE_BALANCE1_VIEW] AS SELECT dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.COST_METHOD, dbo.DM_ITEM.ITEM_CLASS_ID, 'DEB' AS DEBIT_CREDIT, dbo.DM_ITEM.ORIGIN, dbo.WAREHOUSE_BALANCE.PR_KEY, dbo.WAREHOUSE_BALANCE.TRAN_DATE, dbo.WAREHOUSE_BALANCE.ORGANIZATION_ID, dbo.WAREHOUSE_BALANCE.WAREHOUSE_ID, dbo.WAREHOUSE_BALANCE.ITEM_ID, dbo.WAREHOUSE_BALANCE.QUANTITY, dbo.WAREHOUSE_BALANCE.QUANTITY_EXTRA, dbo.WAREHOUSE_BALANCE.UNIT_PRICE, dbo.WAREHOUSE_BALANCE.AMOUNT, dbo.WAREHOUSE_BALANCE.AMOUNT_EXTRA, dbo.WAREHOUSE_BALANCE.USER_ID, dbo.WAREHOUSE_BALANCE.LOT_NO, dbo.WAREHOUSE_BALANCE.RECEIVE_DATE, dbo.WAREHOUSE_BALANCE.MANU_DATE, dbo.WAREHOUSE_BALANCE.EXPIRE_DATE, dbo.WAREHOUSE_BALANCE.EXPIRE_DATE AS EXPIRED_DATE, dbo.WAREHOUSE_BALANCE.ACCOUNT_ID, dbo.WAREHOUSE_BALANCE.ITEM_SOURCE_ID,WAREHOUSE_BALANCE.ITEM_STATUS_ID, dbo.WAREHOUSE_BALANCE.ORGANIZATION_ID + '000000' AS JOB_ID, dbo.DM_JOB.JOB_CLASS_ID,UNIT_ID_ACTUAL,DM_ITEM.ITEM_NAME,WAREHOUSE_BALANCE.ITEM_ID + WAREHOUSE_BALANCE.UNIT_ID_ACTUAL AS ITEM_COMBO_ID, WAREHOUSE_BALANCE.ITEM_HEIGHT,WAREHOUSE_BALANCE.ITEM_WIDTH,WAREHOUSE_BALANCE.ITEM_DEPTH,WAREHOUSE_BALANCE.ITEM_SIZE,WAREHOUSE_BALANCE.ITEM_LOCATION,WAREHOUSE_BALANCE.ITEM_SERI_NO,WAREHOUSE_BALANCE.ITEM_SERI_NO1,WAREHOUSE_BALANCE.ITEM_SERI_NO2,WAREHOUSE_BALANCE.ITEM_GRADE_ID,WAREHOUSE_BALANCE.ITEM_GRADE1_ID,DM_ITEM.ITEM_CLASS1_ID FROM dbo.WAREHOUSE_BALANCE LEFT OUTER JOIN dbo.DM_JOB ON dbo.WAREHOUSE_BALANCE.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_ITEM ON dbo.WAREHOUSE_BALANCE.ITEM_ID = dbo.DM_ITEM.ITEM_ID GO CREATE VIEW [dbo].[WAREHOUSE1_VIEW] AS SELECT dbo.WAREHOUSE.PR_KEY_WAREHOUSE, dbo.WAREHOUSE.PR_KEY, dbo.WAREHOUSE.PR_KEY_DETAIL, dbo.WAREHOUSE.ISSUE_RECEIVE, dbo.WAREHOUSE.TRAN_ID, dbo.WAREHOUSE.TRAN_DATE, dbo.WAREHOUSE.TRAN_NO, dbo.WAREHOUSE.COMMENTS, dbo.WAREHOUSE.WAREHOUSE_ID, dbo.WAREHOUSE.ITEM_ID, dbo.WAREHOUSE.LOT_NO, dbo.WAREHOUSE.RECEIVE_DATE, dbo.WAREHOUSE.MANU_DATE, dbo.WAREHOUSE.EXPIRED_TERM, dbo.WAREHOUSE.EXPIRED_DATE, dbo.WAREHOUSE.DESCRIPTION, dbo.WAREHOUSE.DESCRIPTION_ULS, dbo.WAREHOUSE.QUANTITY, dbo.WAREHOUSE.QUANTITY_EXTRA, dbo.WAREHOUSE.UNIT_PRICE, dbo.WAREHOUSE.AMOUNT, dbo.WAREHOUSE.AMOUNT_EXTRA, dbo.WAREHOUSE.ACCOUNT_ID, dbo.WAREHOUSE.JOB_QTY, dbo.WAREHOUSE.ACCOUNT_ID_CONTRA, dbo.WAREHOUSE.EXPENSE_ID, dbo.WAREHOUSE.ORGANIZATION_ID + '000000' AS JOB_ID, ISNULL(dbo.DM_ITEM.ITEM_CLASS_ID, N' ') AS ITEM_CLASS_ID, ISNULL(dbo.DM_JOB.JOB_CLASS_ID, N' ') AS JOB_CLASS_ID, dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH, dbo.DM_ITEM.COST_METHOD, dbo.DM_ITEM.UNIT_ID_EXTRA, dbo.DM_ITEM.ITEM_NAME, dbo.WAREHOUSE.ORGANIZATION_ID, ISNULL(dbo.DM_JOB.JOB_NAME, N' ') AS JOB_NAME, dbo.DM_ITEM.ORIGIN, dbo.WAREHOUSE.PR_DETAIL_ID, dbo.WAREHOUSE.ITEM_SOURCE_ID, WAREHOUSE.ITEM_STATUS_ID, dbo.WAREHOUSE.WAREHOUSE_ID_ISSUE, dbo.WAREHOUSE.ITEM_OP_ID, dbo.WAREHOUSE.UNIT_ID_ACTUAL, dbo.WAREHOUSE.ITEM_ID + dbo.WAREHOUSE.UNIT_ID_ACTUAL AS ITEM_COMBO_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_WAREHOUSE.WAREHOUSE_CLASS_ID, dbo.DM_PR_DETAIL.PR_DETAIL_NAME, dbo.WAREHOUSE.VAT_TRAN_NO, dbo.WAREHOUSE.POS_SHIFT_PR_KEY,WAREHOUSE.ITEM_HEIGHT,WAREHOUSE.ITEM_WIDTH,WAREHOUSE.ITEM_DEPTH,WAREHOUSE.ITEM_SIZE,WAREHOUSE.ITEM_LOCATION,WAREHOUSE.ITEM_SERI_NO,WAREHOUSE.ITEM_SERI_NO1,WAREHOUSE.ITEM_SERI_NO2,WAREHOUSE.ITEM_GRADE_ID,WAREHOUSE.ITEM_GRADE1_ID,DM_ITEM.ITEM_CLASS1_ID, DM_WAREHOUSE.WAREHOUSE_NAME FROM dbo.WAREHOUSE LEFT OUTER JOIN dbo.DM_ITEM ON dbo.WAREHOUSE.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.WAREHOUSE.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.WAREHOUSE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_WAREHOUSE ON dbo.WAREHOUSE.WAREHOUSE_ID = dbo.DM_WAREHOUSE.WAREHOUSE_ID GO CREATE VIEW [dbo].[Purchase_TRAN1_view] AS SELECT dbo.PURCHASE.PR_KEY AS PR_KEY_CTU , dbo.PURCHASE.STATUS, dbo.PURCHASE.TRAN_ID , dbo.PURCHASE.TRAN_NO , dbo.PURCHASE.TRAN_DATE , dbo.PURCHASE.REFERENCE_NO , dbo.PURCHASE.ORIG_TRAN_NO , dbo.PURCHASE.PO_TRAN_NO , dbo.PURCHASE.PO_TRAN_ID , dbo.PURCHASE.VAT_TRAN_NO , dbo.PURCHASE.VAT_TRAN_DATE , dbo.PURCHASE.VAT_TRAN_SERIE , dbo.PURCHASE.VAT_PURCHASE_ID , dbo.PURCHASE.WAREHOUSE_ID_ISSUE , dbo.PURCHASE.MARKET_ID , dbo.PURCHASE.EXCHANGE_RATE , dbo.PURCHASE.CURRENCY_ID , dbo.PURCHASE.PR_DETAIL_ID , dbo.PURCHASE.EMPLOYEE_ID , dbo.PURCHASE.PR_DETAIL_NAME , dbo.PURCHASE.CONTACT_PERSON , dbo.PURCHASE.ADDRESS , dbo.PURCHASE.TAX_FILE_NUMBER , dbo.PURCHASE.PAYMENT_METHOD_ID , dbo.PURCHASE.COMMENTS , dbo.PURCHASE.PAYMENT_TERM_ID , dbo.PURCHASE.PAYMENT_DATE , dbo.PURCHASE_DETAIL.ITEM_SOURCE_ID , dbo.DM_ITEM.ITEM_CLASS_ID , dbo.PURCHASE.ORGANIZATION_ID , dbo.PURCHASE.ACCOUNT_ID_PR , dbo.PURCHASE.ACCOUNT_ID_VAT , PURCHASE_DETAIL.ACCOUNT_ID_COST, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID , dbo.PURCHASE_DETAIL.PR_KEY , dbo.PURCHASE_DETAIL.FR_KEY , dbo.PURCHASE_DETAIL.LIST_ORDER , dbo.PURCHASE_DETAIL.LOT_NO , dbo.PURCHASE_DETAIL.MANU_DATE , dbo.PURCHASE_DETAIL.EXPIRED_TERM , dbo.PURCHASE_DETAIL.EXPIRED_DATE , dbo.PURCHASE_DETAIL.ITEM_ID , dbo.PURCHASE_DETAIL.DESCRIPTION , dbo.PURCHASE_DETAIL.DESCRIPTION_ULS , dbo.PURCHASE_DETAIL.UNIT_ID , dbo.PURCHASE_DETAIL.QUANTITY , dbo.PURCHASE_DETAIL.QUANTITY_EXTRA , dbo.PURCHASE_DETAIL.QUANTITY_WH , dbo.PURCHASE_DETAIL.UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.UNIT_PRICE , dbo.PURCHASE_DETAIL.UNIT_PRICE_WH , dbo.PURCHASE_DETAIL.AMOUNT_ORIG , dbo.PURCHASE_DETAIL.AMOUNT , dbo.PURCHASE_DETAIL.AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT , dbo.PURCHASE_DETAIL.VAT_INCOME_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_ID , dbo.PURCHASE_DETAIL.VAT_TAX_RATE , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.VAT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_RATE , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.IMPORT_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.PURCHASE_COST , dbo.PURCHASE_DETAIL.PURCHASE_COST_ORIG , dbo.PURCHASE_DETAIL.PURCHASE_COST_EXTRA , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE , dbo.PURCHASE_DETAIL.COG_AMOUNT , dbo.PURCHASE_DETAIL.COG_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.DISCOUNT_RATE , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.DISCOUNT_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TOTAL_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.COG_UNIT_PRICE_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_RATE , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_ORIG , dbo.PURCHASE_DETAIL.LUX_TAX_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.ITEM_OP_ID , dbo.PURCHASE_DETAIL.WAREHOUSE_ID , dbo.PURCHASE_DETAIL.PRICE_LEVEL_ID , dbo.DM_ITEM_COMBO_VIEW.ITEM_COMBO_NAME AS ITEM_NAME , dbo.DM_ITEM.UNIT_ID AS UNIT_ID_WH , dbo.DM_ITEM.UNIT_ID_EXTRA , dbo.PURCHASE.TAX_OFFICE_ID , dbo.DM_PR_DETAIL.PROVINCE_ID , dbo.PURCHASE_DETAIL.QUANTITY_EXPECTED , dbo.PURCHASE_DETAIL.FIXED_UNIT_PRICE , dbo.PURCHASE_DETAIL.FIXED_AMOUNT , dbo.PURCHASE_DETAIL.QUANTITY_BILL , dbo.PURCHASE_DETAIL.QUANTITY_MEASURE , PURCHASE.EXCHANGE_RATE_EXTRA , PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID AS ITEM_COMBO_ID , PURCHASE.VEHICLE_ID , '00000000-0000-0000-0000-000000000000' AS POS_SHIFT_PR_KEY , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.TRANSPORT_FEE_RATE , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT , dbo.PURCHASE_DETAIL.INSURANCE_AMOUNT_EXTRA , dbo.PURCHASE_DETAIL.INSURANCE_RATE , PURCHASE_DETAIL.EMPLOYEE_COMMISSION , PURCHASE_DETAIL.EMPLOYEE_COMMISSION_ORIG , PURCHASE_DETAIL.EMPLOYEE_COMMISSION_RATE, PURCHASE_DETAIL.ACCOUNT_ID_COST AS ACCOUNT_ID, dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM,DM_PR_DETAIL_ITEM.ADDRESS AS ADDRESS_ITEM,DM_PR_DETAIL_ITEM.TAX_FILE_NUMBER AS TAX_FILE_NUMBER_ITEM, DM_PR_DETAIL_ITEM.PR_DETAIL_NAME AS PR_DETAIL_NAME_ITEM, PURCHASE_DETAIL.ITEM_HEIGHT,PURCHASE_DETAIL.ITEM_WIDTH,PURCHASE_DETAIL.ITEM_DEPTH,PURCHASE_DETAIL.ITEM_SIZE,PURCHASE_DETAIL.ITEM_LOCATION,PURCHASE_DETAIL.ITEM_SERI_NO,PURCHASE_DETAIL.ITEM_SERI_NO1,PURCHASE_DETAIL.ITEM_SERI_NO2,PURCHASE_DETAIL.ITEM_GRADE_ID,PURCHASE_DETAIL.ITEM_GRADE1_ID,PURCHASE_DETAIL.ITEM_STATUS_ID,PURCHASE_DETAIL.SELECTED, PURCHASE.ORGANIZATION_ID + '000000' AS JOB_ID FROM dbo.PURCHASE INNER JOIN dbo.PURCHASE_DETAIL ON dbo.PURCHASE.PR_KEY = dbo.PURCHASE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.PURCHASE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.PURCHASE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL AS DM_PR_DETAIL_ITEM ON dbo.PURCHASE_DETAIL.PR_DETAIL_ID_ITEM = DM_PR_DETAIL_ITEM.PR_DETAIL_ID LEFT JOIN DM_ITEM_COMBO_VIEW ON PURCHASE_DETAIL.ITEM_ID + PURCHASE_DETAIL.UNIT_ID = DM_ITEM_COMBO_VIEW.ITEM_COMBO_ID INNER JOIN SYS_TRAN ON PURCHASE.TRAN_ID = SYS_TRAN.TRAN_ID GO CREATE VIEW [dbo].[SALE_TRAN1_VIEW] AS SELECT dbo.SALE.PR_KEY AS PR_KEY_CTU, dbo.SALE.TRAN_ID, dbo.SALE.TRAN_NO, dbo.SALE.TRAN_DATE, dbo.SALE.REFERENCE_NO, dbo.SALE.VAT_TRAN_DATE, dbo.SALE.VAT_TRAN_SERIE, dbo.SALE.ACCOUNT_ID_PR, dbo.SALE.ACCOUNT_ID_VAT, dbo.SALE.ACCOUNT_ID_EXPORT_TAX, dbo.SALE.MARKET_ID, dbo.SALE.EXCHANGE_RATE, dbo.SALE.EXCHANGE_RATE_EXTRA, dbo.SALE.CURRENCY_ID, dbo.SALE.PR_DETAIL_ID, dbo.SALE.PR_DETAIL_NAME, dbo.SALE.CONTACT_PERSON, dbo.SALE.ADDRESS, dbo.SALE.TAX_FILE_NUMBER, dbo.SALE.PAYMENT_METHOD_ID, dbo.SALE.COMMENTS, dbo.SALE.PAYMENT_TERM_ID, dbo.SALE.STATUS, dbo.SALE.PAYMENT_DATE, dbo.SALE.WAREHOUSE_ID_RECEIVE, dbo.DM_ITEM.ITEM_CLASS_ID, dbo.DM_ITEM.ITEM_TYPE_ID, dbo.SALE.ORGANIZATION_ID, dbo.DM_PR_DETAIL.PR_DETAIL_CLASS_ID, dbo.DM_JOB.JOB_CLASS_ID, dbo.DM_JOB.JOB_NAME, dbo.SALE.IMPLEMENTED_EMPLOYEE_ID, dbo.SALE.SALE_DATE, dbo.SALE.IMPLEMENTED_DATE, dbo.DM_ITEM.ORIGIN, dbo.HR_EMPLOYEE_INFO.EMPLOYEE_NAME, dbo.SALE.SBO_TRAN_ID, dbo.SALE.SBO_TRAN_NO, dbo.SALE.SO_TRAN_ID, dbo.SALE.SO_TRAN_NO, dbo.SALE.SII_TRAN_ID, dbo.SALE.SII_TRAN_NO, dbo.SALE.VAT_TRAN_NO, dbo.SALE.VAT_PURCHASE_ID, dbo.SALE.REC_PR_DETAIL_ID, dbo.SALE.SHIPPING_METHOD_ID, dbo.SALE.VEHICLE_ID, dbo.SALE.TAX_OFFICE_ID, dbo.SALE_DETAIL.PR_KEY, dbo.SALE_DETAIL.FR_KEY, dbo.SALE_DETAIL.LIST_ORDER, dbo.SALE_DETAIL.LOT_NO, dbo.SALE_DETAIL.MANU_DATE, dbo.SALE_DETAIL.RECEIVE_DATE, dbo.SALE_DETAIL.EXPIRED_TERM, dbo.SALE_DETAIL.EXPIRED_DATE, dbo.SALE_DETAIL.ITEM_ID, dbo.SALE_DETAIL.DESCRIPTION, dbo.SALE_DETAIL.DESCRIPTION_ULS, dbo.SALE_DETAIL.UNIT_ID, dbo.SALE_DETAIL.QUANTITY, dbo.SALE_DETAIL.QUANTITY_EXTRA, dbo.SALE_DETAIL.QUANTITY_WH, dbo.SALE_DETAIL.UNIT_PRICE_ORIG, dbo.SALE_DETAIL.UNIT_PRICE, dbo.SALE_DETAIL.UNIT_PRICE_WH, dbo.SALE_DETAIL.AMOUNT_ORIG, dbo.SALE_DETAIL.AMOUNT, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_ORIG, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_ID, dbo.SALE_DETAIL.VAT_TAX_RATE, dbo.SALE_DETAIL.VAT_TAX_AMOUNT, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.EXPORT_TAX_RATE, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.SALE_COST, dbo.SALE_DETAIL.SALE_COST_ORIG, dbo.SALE_DETAIL.DISCOUNT_RATE, dbo.SALE_DETAIL.DISCOUNT_AMOUNT, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_ORIG, dbo.SALE_DETAIL.COG_UNIT_PRICE, dbo.SALE_DETAIL.COG_UNIT_PRICE_ORIG, dbo.SALE_DETAIL.COG_AMOUNT, dbo.SALE_DETAIL.COG_AMOUNT_ORIG, dbo.SALE_DETAIL.TOTAL_AMOUNT, dbo.SALE_DETAIL.TOTAL_AMOUNT_ORIG, dbo.SALE_DETAIL.ACCOUNT_ID, dbo.SALE_DETAIL.ACCOUNT_ID_COST, dbo.SALE_DETAIL.ACCOUNT_ID_INCOME, dbo.SALE_DETAIL.PR_DETAIL_ID_ITEM, dbo.SALE_DETAIL.EXPENSE_ID, dbo.SALE.ORGANIZATION_ID + '000000' AS JOB_ID, dbo.SALE_DETAIL.AMOUNT_EXTRA, dbo.SALE_DETAIL.VAT_INCOME_AMOUNT_EXTRA, dbo.SALE_DETAIL.VAT_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.DISCOUNT_AMOUNT_EXTRA, dbo.SALE_DETAIL.EXPORT_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.COG_AMOUNT_EXTRA, dbo.SALE_DETAIL.SALE_COST_EXTRA, dbo.SALE_DETAIL.TOTAL_AMOUNT_EXTRA, dbo.SALE_DETAIL.LUX_TAX_RATE, dbo.SALE_DETAIL.LUX_TAX_AMOUNT, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_ORIG, dbo.SALE_DETAIL.LUX_TAX_AMOUNT_EXTRA, dbo.SALE_DETAIL.JOB_QTY, dbo.SALE_DETAIL.BILL_NO, dbo.SALE_DETAIL.BILL_AMOUNT, dbo.SALE_DETAIL.BILL_VAT_TAX_AMOUNT, dbo.SALE_DETAIL.OPENNING_QUANTITY, dbo.SALE_DETAIL.CLOSING_QUANTITY, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_RATE, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_ORIG, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION, dbo.SALE_DETAIL.EMPLOYEE_COMMISSION_EXTRA, dbo.SALE_DETAIL.EMPLOYEE_ID, dbo.SALE_DETAIL.QUANTITY_EXPECTED, dbo.SALE_DETAIL.ITEM_OP_ID, dbo.SALE_DETAIL.WAREHOUSE_ID, dbo.SALE_DETAIL.PRICE_LEVEL_ID, dbo.SALE_DETAIL.FIXED_UNIT_PRICE, dbo.SALE_DETAIL.FIXED_AMOUNT, dbo.SALE_DETAIL.ROOM_ID, dbo.SALE_DETAIL.ITEM_SOURCE_ID, dbo.SALE_DETAIL.PR_KEY_HT_FOLIO, dbo.SALE_DETAIL.QUANTITY_BILL, dbo.DM_PR_DETAIL.PROVINCE_ID, dbo.SALE_DETAIL.ITEM_ID + dbo.SALE_DETAIL.UNIT_ID AS ITEM_COMBO_ID, dbo.SALE.ORIG_TRAN_NO, dbo.DM_WAREHOUSE.WAREHOUSE_CLASS_ID, dbo.SALE.ORIG_VAT_TRAN_DATE, dbo.SALE.ORIG_VAT_TRAN_NO, dbo.SALE.PR_KEY_POS, sale_detail.ITEM_HEIGHT,sale_detail.ITEM_WIDTH,sale_detail.ITEM_DEPTH,sale_detail.ITEM_SIZE,sale_detail.ITEM_LOCATION,sale_detail.ITEM_SERI_NO,sale_detail.ITEM_SERI_NO1,sale_detail.ITEM_SERI_NO2,sale_detail.ITEM_GRADE_ID,sale_detail.ITEM_GRADE1_ID,SALE_DETAIL.ITEM_STATUS_ID FROM dbo.SALE INNER JOIN dbo.SALE_DETAIL ON dbo.SALE.PR_KEY = dbo.SALE_DETAIL.FR_KEY LEFT OUTER JOIN dbo.DM_ITEM ON dbo.SALE_DETAIL.ITEM_ID = dbo.DM_ITEM.ITEM_ID LEFT OUTER JOIN dbo.DM_PR_DETAIL ON dbo.SALE.PR_DETAIL_ID = dbo.DM_PR_DETAIL.PR_DETAIL_ID LEFT OUTER JOIN dbo.DM_JOB ON dbo.SALE_DETAIL.JOB_ID = dbo.DM_JOB.JOB_ID LEFT OUTER JOIN dbo.HR_EMPLOYEE_INFO ON dbo.SALE_DETAIL.EMPLOYEE_ID = dbo.HR_EMPLOYEE_INFO.EMPLOYEE_ID LEFT OUTER JOIN dbo.DM_WAREHOUSE ON dbo.DM_WAREHOUSE.WAREHOUSE_ID = dbo.SALE.WAREHOUSE_ID GO ALTER TABLE COST_APPLICATION ADD RATIO_ORGANIZATION_ID_LIST NVARCHAR(200) DEFAULT '' NOT NULL GO ALTER TABLE CA_RATIO ADD ITEM_CLASS1_ID NVARCHAR(20) DEFAULT '' NOT NULL GO ALTER TABLE COST_APPLICATION ADD ITEM_CLASS1_ID_LIST NVARCHAR(200) DEFAULT '' NOT NULL GO ALTER TABLE COST_APPLICATION ADD SAME_ORG_JOB SMALLINT DEFAULT 0 NOT NULL GO DROP VIEW DM_JOB_MAPPING_VIEW GO CREATE VIEW DM_JOB_MAPPING_VIEW AS SELECT ORGANIZATION_ID,DM_ITEM.ITEM_ID,DM_ITEM.ITEM_CLASS1_ID,DM_JOB_MAPPING.ITEM_OP_ID,JOB_ID FROM DM_JOB_MAPPING INNER JOIN DM_ITEM ON DM_JOB_MAPPING.ITEM_ID=DM_ITEM.ITEM_ID WHERE DM_JOB_MAPPING.ACTIVE=1 AND DM_ITEM.ACTIVE=1 AND DM_JOB_MAPPING.ITEM_STATUS_ID='' AND DM_JOB_MAPPING.VEHICLE_ID='' GO INSERT INTO [SYS_SYSTEMVAR] ([VAR_NAME] ,[VAR_VALUE] ,[DESCRIPTION] ,[VAR_TYPE] ,[VAR_GROUP]) VALUES ('IS_GAS', '0', N'IS_GAS', 'BOOLEAN', 'COMMON') GO alter table CA_ACCOUNT_CONFIG ALTER COLUMN ACCOUNT_ID NVARCHAR(200) GO ALTER TABLE DM_JOB_MAPPING ADD ITEM_CLASS1_ID NVARCHAR(20) DEFAULT '' NOT NULL GO UPDATE DM_JOB_MAPPING SET ITEM_CLASS1_ID = ISNULL((SELECT ITEM_CLASS1_ID FROM DM_ITEM WHERE DM_ITEM.ITEM_ID=DM_JOB_MAPPING.ITEM_ID),'') GO ALTER TABLE COST_APPLICATION ADD SAME_ORG_JOB_SUB SMALLINT DEFAULT 0 NOT NULL GO ALTER TABLE SYS_REPORT_FORMULA_DETAIL ADD ITEM_CLASS1_ID NVARCHAR(100) DEFAULT '' NOT NULL GO alter table CA_ACCOUNT_CONFIG ADD ORGANIZATION_LIST_ID NVARCHAR(200) DEFAULT '' NOT NULL GO