USE [AJK]
GO

/****** Object:  Trigger [dbo].[trig_insert_claim]    Script Date: 17/10/2018 14.15.05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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]
			   ,[alt_phone]
			   ,[alt_email]
			   ,[account_no]
			   ,[account_beneficiary]
			   ,[account_currency_code]
			   ,[bank_id]
			   ,[bank_name]
			   ,[bank_code]
			   ,[bank_branch]
			   ,[company_code]
			   ,[company_region_code]
			   ,[tmp_guid]
			   ,[id_klaim_core]
			   ,[complete_doc_date]
			   ,[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]
				  ,[alt_phone]
				  ,[alt_email]
				  ,[account_no]
				  ,[account_beneficiary]
				  ,[account_currency_code]
				  ,[bank_id]
				  ,[bank_name]
				  ,[bank_code]
				  ,[bank_branch]
				  ,[company_code]
				  ,[company_region_code]
				  ,[tmp_guid]
				  ,[id_klaim_core]
				  ,[complete_doc_date]
				  ,[created_by]
				  ,[created_date]
				  ,[modified_by]
				  ,[modified_date]
				  ,[is_active]
			  FROM INSERTED

END
GO

ALTER TABLE [dbo].[custport_claim] ENABLE TRIGGER [trig_insert_claim]
GO


