/*
   18 Juli 201811:32:49
   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_claim
	DROP CONSTRAINT FK__custport___claim__32A230EB
GO
ALTER TABLE dbo.custport_m_claim_type SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.custport_claim
	DROP CONSTRAINT FK__custport___curre__33965524
GO
ALTER TABLE dbo.custport_m_currency SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_custport_claim
	(
	claim_id int NOT NULL IDENTITY (1, 1),
	claim_no varchar(50) NOT NULL,
	no_polis varchar(13) NULL,
	id_peserta varchar(12) NULL,
	certificate_no varchar(20) NOT NULL,
	contract_no varchar(20) NOT NULL,
	customer_name varchar(100) NOT NULL,
	register_date date NULL,
	event_date date NULL,
	claim_type_code varchar(50) NOT NULL,
	currency_code varchar(50) NOT NULL,
	claim_amount decimal(12, 2) NOT NULL,
	reference_no varchar(20) NULL,
	status_code varchar(50) NOT NULL,
	flow_stage_code varchar(50) NULL,
	reason_code varchar(50) NULL,
	notes varchar(600) NULL,
	company_code varchar(10) NULL,
	company_region_code varchar(10) NULL,
	tmp_guid varchar(50) NULL,
	id_klaim_core varchar(12) 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_claim SET (LOCK_ESCALATION = TABLE)
GO
GRANT DELETE ON dbo.Tmp_custport_claim TO public  AS dbo
GO
GRANT INSERT ON dbo.Tmp_custport_claim TO public  AS dbo
GO
GRANT REFERENCES ON dbo.Tmp_custport_claim TO public  AS dbo
GO
GRANT SELECT ON dbo.Tmp_custport_claim TO public  AS dbo
GO
GRANT UPDATE ON dbo.Tmp_custport_claim TO public  AS dbo
GO
SET IDENTITY_INSERT dbo.Tmp_custport_claim ON
GO
IF EXISTS(SELECT * FROM dbo.custport_claim)
	 EXEC('INSERT INTO dbo.Tmp_custport_claim (claim_id, claim_no, no_polis, id_peserta, certificate_no, contract_no, customer_name, register_date, event_date, claim_type_code, currency_code, claim_amount, reference_no, status_code, flow_stage_code, reason_code, notes, company_code, company_region_code, tmp_guid, created_by, created_date, modified_by, modified_date, is_active)
		SELECT claim_id, claim_no, no_polis, id_peserta, certificate_no, contract_no, customer_name, register_date, event_date, claim_type_code, currency_code, claim_amount, reference_no, status_code, flow_stage_code, reason_code, notes, company_code, company_region_code, tmp_guid, created_by, created_date, modified_by, modified_date, is_active FROM dbo.custport_claim WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_custport_claim OFF
GO
DROP TABLE dbo.custport_claim
GO
EXECUTE sp_rename N'dbo.Tmp_custport_claim', N'custport_claim', 'OBJECT' 
GO
ALTER TABLE dbo.custport_claim ADD CONSTRAINT
	PK__custport__F9CC114572348F26 PRIMARY KEY CLUSTERED 
	(
	claim_id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.custport_claim ADD CONSTRAINT
	UK_custport_claim_claim_no UNIQUE NONCLUSTERED 
	(
	claim_no
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.custport_claim ADD CONSTRAINT
	FK__custport___curre__33965524 FOREIGN KEY
	(
	currency_code
	) REFERENCES dbo.custport_m_currency
	(
	currency_code
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.custport_claim ADD CONSTRAINT
	FK__custport___claim__32A230EB FOREIGN KEY
	(
	claim_type_code
	) REFERENCES dbo.custport_m_claim_type
	(
	claim_type_code
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
-- created by Pribadi Pamungkas. 2018-06-29
CREATE TRIGGER [dbo].[trig_insert_claim]
   ON  dbo.custport_claim
   INSTEAD OF INSERT
   --AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE
		@numrows INT,
		@claim_no VARCHAR(12)

	-- pake logic seperti ini dengan catatan tidak ada row yg di (hard) delete
	SELECT 
		@numrows = COUNT(claim_id)
	FROM 
		custport_claim
	WHERE 
		created_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
		AND created_date < DATEADD(DAY, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

	SELECT @claim_no = 'CL' + RIGHT(CONVERT(VARCHAR(8), GETDATE(), 1),2) + CONVERT(VARCHAR(2), GETDATE(), 101) + RIGHT('00000' + CAST(@numrows+1 AS VARCHAR(6)),6)

	INSERT INTO [dbo].[custport_claim]
			   ([claim_no]
			   ,[no_polis]
			   ,[id_peserta]
			   ,[certificate_no]
			   ,[contract_no]
			   ,[customer_name]
			   ,[register_date]
			   ,[event_date]
			   ,[claim_type_code]
			   ,[currency_code]
			   ,[claim_amount]
			   ,[reference_no]
			   ,[status_code]
			   ,[flow_stage_code]
			   ,[reason_code]
			   ,[notes]
			   ,[company_code]
			   ,[company_region_code]
			   ,[tmp_guid]
			   ,[id_klaim_core]
			   ,[created_by]
			   ,[created_date]
			   ,[modified_by]
			   ,[modified_date]
			   ,[is_active])
			SELECT @claim_no
				  ,[no_polis]
				  ,[id_peserta]
				  ,[certificate_no]
				  ,[contract_no]
				  ,[customer_name]
				  ,[register_date]
				  ,[event_date]
				  ,[claim_type_code]
				  ,[currency_code]
				  ,[claim_amount]
				  ,[reference_no]
				  ,[status_code]
				  ,[flow_stage_code]
				  ,[reason_code]
				  ,[notes]
				  ,[company_code]
				  ,[company_region_code]
				  ,[tmp_guid]
				  ,[id_klaim_core]
				  ,[created_by]
				  ,[created_date]
				  ,[modified_by]
				  ,[modified_date]
				  ,[is_active]
			  FROM INSERTED

END
GO
COMMIT
