/*
   19 Juli 201811:50:35
   User: 
   Server: dev-db01
   Database: AJK
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.custport_m_flow_action
	DROP CONSTRAINT DF_custport_m_flow_action_is_need_reason
GO
ALTER TABLE dbo.custport_m_flow_action
	DROP CONSTRAINT DF_custport_m_flow_action_is_reference_no
GO
ALTER TABLE dbo.custport_m_flow_action
	DROP CONSTRAINT DF_custport_m_flow_action_is_initial_action
GO
ALTER TABLE dbo.custport_m_flow_action
	DROP CONSTRAINT DF_custport_m_flow_action_is_final_action
GO
ALTER TABLE dbo.custport_m_flow_action
	DROP CONSTRAINT DF_custport_m_flow_action_is_active
GO
CREATE TABLE dbo.Tmp_custport_m_flow_action
	(
	flow_action_id int NOT NULL IDENTITY (1, 1),
	flow_action_code varchar(50) NOT NULL,
	flow_stage_code varchar(50) NOT NULL,
	description varchar(150) NOT NULL,
	next_flow_stage varchar(50) NOT NULL,
	is_need_reason bit NOT NULL,
	reason_type_code varchar(10) NULL,
	is_reference_no bit NOT NULL,
	is_initial_action bit NOT NULL,
	is_final_action bit NOT NULL,
	created_by varchar(15) NOT NULL,
	created_date datetime NOT NULL,
	modified_by varchar(15) NOT NULL,
	modified_date datetime NOT NULL,
	is_active bit NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_custport_m_flow_action SET (LOCK_ESCALATION = TABLE)
GO
GRANT DELETE ON dbo.Tmp_custport_m_flow_action TO public  AS dbo
GO
GRANT INSERT ON dbo.Tmp_custport_m_flow_action TO public  AS dbo
GO
GRANT REFERENCES ON dbo.Tmp_custport_m_flow_action TO public  AS dbo
GO
GRANT SELECT ON dbo.Tmp_custport_m_flow_action TO public  AS dbo
GO
GRANT UPDATE ON dbo.Tmp_custport_m_flow_action TO public  AS dbo
GO
ALTER TABLE dbo.Tmp_custport_m_flow_action ADD CONSTRAINT
	DF_custport_m_flow_action_is_need_reason DEFAULT ((0)) FOR is_need_reason
GO
ALTER TABLE dbo.Tmp_custport_m_flow_action ADD CONSTRAINT
	DF_custport_m_flow_action_is_reference_no DEFAULT ((0)) FOR is_reference_no
GO
ALTER TABLE dbo.Tmp_custport_m_flow_action ADD CONSTRAINT
	DF_custport_m_flow_action_is_initial_action DEFAULT ((0)) FOR is_initial_action
GO
ALTER TABLE dbo.Tmp_custport_m_flow_action ADD CONSTRAINT
	DF_custport_m_flow_action_is_final_action DEFAULT ((0)) FOR is_final_action
GO
ALTER TABLE dbo.Tmp_custport_m_flow_action ADD CONSTRAINT
	DF_custport_m_flow_action_is_active DEFAULT ((0)) FOR is_active
GO
SET IDENTITY_INSERT dbo.Tmp_custport_m_flow_action ON
GO
IF EXISTS(SELECT * FROM dbo.custport_m_flow_action)
	 EXEC('INSERT INTO dbo.Tmp_custport_m_flow_action (flow_action_id, flow_action_code, flow_stage_code, description, next_flow_stage, is_need_reason, is_reference_no, is_initial_action, is_final_action, created_by, created_date, modified_by, modified_date, is_active)
		SELECT flow_action_id, flow_action_code, flow_stage_code, description, next_flow_stage, is_need_reason, is_reference_no, is_initial_action, is_final_action, created_by, created_date, modified_by, modified_date, is_active FROM dbo.custport_m_flow_action WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_custport_m_flow_action OFF
GO
DROP TABLE dbo.custport_m_flow_action
GO
EXECUTE sp_rename N'dbo.Tmp_custport_m_flow_action', N'custport_m_flow_action', 'OBJECT' 
GO
ALTER TABLE dbo.custport_m_flow_action ADD CONSTRAINT
	PK__custport__81AFD39A488B28E1 PRIMARY KEY CLUSTERED 
	(
	flow_action_id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.custport_m_flow_action ADD CONSTRAINT
	UQ__custport__08AE9413EA792412 UNIQUE NONCLUSTERED 
	(
	flow_action_id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.custport_m_flow_action ADD CONSTRAINT
	UK_custport_m_flow_action_code UNIQUE NONCLUSTERED 
	(
	flow_action_code
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT


UPDATE dbo.custport_m_flow_action SET reason_type_code = 'PND' WHERE flow_action_code = 'ACT00002'
UPDATE dbo.custport_m_flow_action SET reason_type_code = 'PND' WHERE flow_action_code = 'ACT00004'
