--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  Table [dbo].[t_audit_history]    Script Date: 02/28/2018 15:59:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[t_audit_history](
	[id_t_audit_history] [int] IDENTITY(1,1) NOT NULL,
	[id_transaction] [bigint] NOT NULL,
	[register_number] [varchar](9) NOT NULL,
	[id_status_audit] [int] NOT NULL,
	[remarks] [varchar](160) NULL,
	[created_by] [varchar](8) NOT NULL,
	[created_date] [datetime] NOT NULL,
	[modified_by] [varchar](8) NULL,
	[modified_date] [datetime] NULL,
	[is_delete] [tinyint] NOT NULL,
 CONSTRAINT [PK_t_audit_history] PRIMARY KEY CLUSTERED 
(
	[id_t_audit_history] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[t_audit_history] ADD  CONSTRAINT [DF_t_audit_history_is_delete]  DEFAULT ((0)) FOR [is_delete]
GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  Trigger [dbo].[CreateTransTypeCode]    Script Date: 02/28/2018 14:29:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- last modified by Pribadi Pamungkas. 2018-02-06
ALTER TRIGGER [dbo].[CreateTransTypeCode] ON [dbo].[m_transaction_type]
	FOR INSERT 
AS 

BEGIN

	SET NOCOUNT ON;

	--DECLARE @rownums VARCHAR(5);
	--DECLARE @code VARCHAR(8);

 --   SET @rownums = (
	--	SELECT CAST((COUNT(transaction_type_id)) AS VARCHAR) 
	--	FROM m_transaction_type
	--);

	--SET @code = (SELECT 'T' + STUFF('000000', 6 - LEN(@rownums) + 1, LEN(@rownums), @rownums));
	
	DECLARE 
		@last_transaction_type_code VARCHAR(7),
		@new_transaction_type_code VARCHAR(7)

	IF EXISTS(
							SELECT
								*
							FROM
								dbo.m_transaction_type
							WHERE
								COALESCE(transaction_type_code,'') <> ''
						)
		BEGIN
			SELECT
				@last_transaction_type_code = MAX(transaction_type_code)
			FROM
				dbo.m_transaction_type
			WHERE
				COALESCE(transaction_type_code,'') <> ''

			SET @new_transaction_type_code = LEFT(@last_transaction_type_code,1) + RIGHT('000000' + CAST(CAST(RIGHT(@last_transaction_type_code,6) AS INTEGER) + 1 AS VARCHAR(6)),6)
		END
	ELSE
		SET @new_transaction_type_code = 'T000001'	-- initial

	UPDATE 
		dbo.m_transaction_type
	SET 
		transaction_type_code = @new_transaction_type_code
	WHERE 
		transaction_type_id IN (
														SELECT 
															transaction_type_id 
														FROM 
															inserted
														);

END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  UserDefinedFunction [dbo].[get_audit_exist]    Script Date: 02/28/2018 14:34:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_audit_exist]
(
	@pi_transaction_id BIGINT
)
RETURNS INT
AS
BEGIN

	DECLARE @audit_cnt INT;

	SET @audit_cnt = (
		SELECT COUNT(id_t_audit_history)
		FROM t_audit_history
		WHERE id_transaction = @pi_transaction_id
		AND is_delete = 0
	);

	RETURN @audit_cnt;

END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  UserDefinedFunction [dbo].[get_is_complience_audit]    Script Date: 02/28/2018 14:35:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_is_complience_audit]
(
	@pi_transaction_id BIGINT
)
RETURNS VARCHAR(50)
AS
BEGIN

	DECLARE @is_complience VARCHAR(50);

	SET @is_complience = (
		SELECT TOP 1
			s.is_complience
		FROM t_audit_history h
		JOIN m_status_audit s ON h.id_status_audit = s.id_status_audit
		WHERE h.id_transaction = @pi_transaction_id
		ORDER BY h.created_date DESC
	);

	RETURN @is_complience;

END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  UserDefinedFunction [dbo].[get_latest_audit_date]    Script Date: 02/28/2018 14:37:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_latest_audit_date]
(
	@pi_transaction_id BIGINT
)
RETURNS DATE
AS
BEGIN

	DECLARE @audit_stat VARCHAR(50);

	SET @audit_stat = (
		SELECT TOP 1
			CAST(h.created_date AS DATE)
		FROM t_audit_history h
		WHERE h.id_transaction = @pi_transaction_id
		ORDER BY h.created_date DESC
	);

	RETURN @audit_stat;

END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  UserDefinedFunction [dbo].[get_latest_audit_status]    Script Date: 02/28/2018 14:37:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_latest_audit_status]
(
	@pi_transaction_id BIGINT
)
RETURNS VARCHAR(50)
AS
BEGIN

	DECLARE @audit_stat VARCHAR(50);

	SET @audit_stat = (
		SELECT TOP 1
			s.description
		FROM t_audit_history h
		JOIN m_status_audit s ON h.id_status_audit = s.id_status_audit
		WHERE h.id_transaction = @pi_transaction_id
		ORDER BY h.created_date DESC
	);

	RETURN @audit_stat;

END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  UserDefinedFunction [dbo].[get_latest_auditor]    Script Date: 02/28/2018 14:39:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_latest_auditor]
(
	@pi_transaction_id BIGINT
)
RETURNS VARCHAR(50)
AS
BEGIN

	DECLARE @auditor VARCHAR(50);

	SET @auditor = (
		SELECT TOP 1
			s.user_fullname
		FROM t_audit_history h
		JOIN m_user s ON h.created_by = s.username
		WHERE h.id_transaction = @pi_transaction_id
		ORDER BY h.created_date DESC
	);

	RETURN @auditor;

END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  StoredProcedure [dbo].[proc_insert_eform]    Script Date: 02/28/2018 15:54:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- by Pribadi Pamungkas. 2018-01-19

CREATE PROCEDURE [dbo].[proc_insert_eform]
	@pi_transaction_type_id INT,
	@pi_is_need_surat_kuasa VARCHAR(1),
	@pi_remarks VARCHAR(255),
	@pi_status_id INT,
	@pi_is_delete VARCHAR(1) = '0',
	@pi_user VARCHAR(8)
AS
BEGIN

	SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	IF @pi_status_id IN (5,2)	-- active
		BEGIN
			IF EXISTS (
									SELECT
										*
									FROM
										dbo.m_eform
									WHERE
										transaction_type_id = @pi_transaction_type_id
										AND status_id = 5
								)
				BEGIN
					SELECT 1 AS [status], 'Data cannot be saved. There is already exist form with status Active.' AS [message]
					RETURN 1
				END
			ELSE IF EXISTS (
									SELECT
										*
									FROM
										dbo.m_eform
									WHERE
										transaction_type_id = @pi_transaction_type_id
										AND status_id = 2
								)
				BEGIN
					SELECT 1 AS [status], 'Data cannot be saved. There is already exist form with status Draft.' AS [message]
					RETURN 1
				END
			ELSE
				BEGIN
					INSERT INTO [m_eform]
										 ([transaction_type_id]
										 ,[is_need_surat_kuasa]
										 ,[remarks]
										 ,[status_id]
										 ,[created_by]
										 ,[created_date]
										 ,[modified_by]
										 ,[modified_date]
										 ,[is_delete])
							 VALUES
										 (@pi_transaction_type_id
										 ,COALESCE(@pi_is_need_surat_kuasa,'0')
										 ,@pi_remarks
										 ,@pi_status_id
										 ,@pi_user
										 ,GETDATE()
										 ,@pi_user
										 ,GETDATE()
										 ,COALESCE(@pi_is_delete,'0'))
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SELECT 1 AS [status], 'Create failed' AS [message]
							RETURN 1
						END
					ELSE
						BEGIN
							SELECT 0 AS [status], 'Success' AS [message]
							RETURN 0
						END
				END
		END
	ELSE
		BEGIN
			INSERT INTO [m_eform]
								 ([transaction_type_id]
								 ,[is_need_surat_kuasa]
								 ,[remarks]
								 ,[status_id]
								 ,[created_by]
								 ,[created_date]
								 ,[modified_by]
								 ,[modified_date]
								 ,[is_delete])
					 VALUES
								 (@pi_transaction_type_id
								 ,COALESCE(@pi_is_need_surat_kuasa,'0')
								 ,@pi_remarks
								 ,@pi_status_id
								 ,@pi_user
								 ,GETDATE()
								 ,@pi_user
								 ,GETDATE()
								 ,COALESCE(@pi_is_delete,'0'))
			IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
				BEGIN
					SELECT 1 AS [status], 'Create failed' AS [message]
					RETURN 1
				END
			ELSE
				BEGIN
					SELECT 0 AS [status], 'Success' AS [message]
					RETURN 0
				END
		END
END

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  StoredProcedure [dbo].[proc_update_eform]    Script Date: 02/28/2018 15:56:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- by Pribadi Pamungkas. 2018-01-19

CREATE PROCEDURE [dbo].[proc_update_eform]
	@pi_eform_id INT,
	@pi_transaction_type_id INT,
	@pi_is_need_surat_kuasa VARCHAR(1),
	@pi_remarks VARCHAR(255),
	@pi_status_id INT,
	@pi_is_delete VARCHAR(1) = '0',
	@pi_user VARCHAR(8)
AS
BEGIN

	SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	
	IF @pi_status_id = 5	-- active
		BEGIN
			IF EXISTS (
									SELECT
										*
									FROM
										dbo.m_eform
									WHERE
										transaction_type_id = @pi_transaction_type_id
										AND status_id = 5
								)
				BEGIN
					SELECT 1 AS [status], 'Data cannot be saved' AS [message]
					RETURN 1
				END
			ELSE
				BEGIN										 
					UPDATE [m_eform]
						 SET [transaction_type_id] = @pi_transaction_type_id
								,[is_need_surat_kuasa] = COALESCE(@pi_is_need_surat_kuasa,'0')
								,[remarks] = @pi_remarks
								,[status_id] = @pi_status_id
								,[modified_by] = @pi_user
								,[modified_date] = GETDATE()
								,[is_delete] = COALESCE(@pi_is_delete,'0')
					 WHERE eform_id = @pi_eform_id
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SELECT 1 AS [status], 'Create failed' AS [message]
							RETURN 1
						END
					ELSE
						BEGIN
							SELECT 0 AS [status], 'Success' AS [message]
							RETURN 0
						END
				END
		END
	ELSE
		BEGIN
			UPDATE [m_eform]
						 SET [transaction_type_id] = @pi_transaction_type_id
								,[is_need_surat_kuasa] = COALESCE(@pi_is_need_surat_kuasa,'0')
								,[remarks] = @pi_remarks
								,[status_id] = @pi_status_id
								,[modified_by] = @pi_user
								,[modified_date] = GETDATE()
								,[is_delete] = COALESCE(@pi_is_delete,'0')
					 WHERE eform_id = @pi_eform_id
			IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
				BEGIN
					SELECT 1 AS [status], 'Update failed' AS [message]
					RETURN 1
				END
			ELSE
				BEGIN
					SELECT 0 AS [status], 'Success' AS [message]
					RETURN 0
				END
		END
END


GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  StoredProcedure [dbo].[proc_submit_transaction]    Script Date: 02/28/2018 15:55:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- changelog:
-- by Pribadi Pamungkas. 2018-01-31 => - tambah validasi, apakah user berhak melakukan submit
-- by Pribadi Pamungkas. 2018-01-09 => - panjangin variable penampungan hasil data dari PL
--                                     - 
-- by Pribadi Pamungkas. 2017-12-29 => - bugfix untuk pengecekan data PL (db_link_project)
--                                     - pengecekan data PL hanya berlaku server production, selain itu tidak dicek
--           
-- by Pribadi Pamungkas. 2017-11-06 => - apakah sudah digunakan di transaksi edits yang lain? Jika sudah maka sistem akan menolak proses dan mengeluarkan pesan "Duplicate Reference No. with Register No [RegisterNumber EDITS]! "
--                                     - apakah nomor transaksi sudah terdaftar di aplikasi core sistem? Jika sudah maka sistem akan menolak proses dan mengeluarkan pesan "Reference No. not found. Please input Register No. from Core System!"
--
-- todo: - cek status tiap tabel (active) dan is_delete
--       - informasi mandatory
ALTER PROCEDURE [dbo].[proc_submit_transaction]
	@pi_transaction_id BIGINT,
	@pi_workflow_template_action_id BIGINT,  -- next workflow_template_action_id yg akan dituju
	@pi_reason_id INT,
	@pi_reference_number VARCHAR(50),
	@pi_remarks VARCHAR(255),
	@pi_user_id VARCHAR(8),
	@po_message VARCHAR(400) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE 
	  @v_result BIT = 0,
	  @v_current_datetime DATETIME = GETDATE()
	
	-- untuk menghemat join dengan tabel m_status buat mendapatkan status_id yg aktif, mending pake variable
	DECLARE
		@status_id_aktif INT
	SELECT
		@status_id_aktif = status_id
	FROM
		dbo.m_status
	WHERE
		status_code = 'STAT02'	-- Active
		AND is_delete = '0'


	BEGIN TRY
	BEGIN TRANSACTION proses_utama

		--====================== cek apakah user ini boleh submit? ======================
		IF NOT EXISTS(SELECT 
										* 
									FROM 
										dbo.t_transaction_task 
									WHERE 
										transaction_id = @pi_transaction_id 
										AND [user_id] = @pi_user_id 
										AND status_id = 1 --Taken
									)
			BEGIN
				SET @po_message = 'This task has been submitted to next process. Please open menu O/S Transaction'
				GOTO ERROR_OUT
			END
		
		--====================== get transaction ======================
		DECLARE
			@v_current_workflow_template_stage_id BIGINT,
			@v_requester_id VARCHAR(8),
			@v_branch_id INT,
			@v_transaction_type_id INT,
			@v_currency_id INT,
			@v_currency_code VARCHAR(3),
			@v_transaction_value DECIMAL(12,2),
			@v_is_policy_holder TINYINT = 0
		SELECT
			@v_current_workflow_template_stage_id = workflow_template_stage_id,
			@v_requester_id = [user_id],
			@v_branch_id = branch_id,
			@v_transaction_type_id = transaction_type_id,
			@v_currency_id = currency_id,
			@v_currency_code = currency_code,
			@v_transaction_value = COALESCE(transaction_value,0),
			@v_is_policy_holder = COALESCE(is_policy_holder,0)
		FROM
			dbo.t_transaction
		WHERE
			transaction_id = @pi_transaction_id
		IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			BEGIN
				SET @po_message = 'Transaction not found'
				GOTO ERROR_OUT
			END
	

		--====================== validasi next workflow stage adalah benar lanjutan dari workflow stage saat ini ======================
		DECLARE 
			@v_destination_stage_id INT,
			@v_is_check_mandatory_document BIT = 0,
			@v_workflow_template_stage_id BIGINT,
			@v_action_description VARCHAR(255),
			@v_is_stage_approval VARCHAR(1),
			@v_approv_matrix_id INT,
			@v_is_final_action VARCHAR(1) = '0',
			@v_transaction_status_id INT,
			@v_is_fill_reference_number VARCHAR(1), -- new
			@v_is_need_reason VARCHAR(1), -- new
			@v_pic_is_requester VARCHAR(1), -- new
			--sudah di-declare dibawah  --@v_group_pic_id INT -- new
			@v_is_remarks_mandatory VARCHAR(1) = '0',
			@v_is_check_transaction_status_core VARCHAR(1) = '0',
			@v_code_transaction_status VARCHAR(50)
		SELECT
			@v_destination_stage_id = act.destination_stage_id,
			@v_is_check_mandatory_document = CASE act.is_check_mandatory_document
												                WHEN '1' THEN 1
												                ELSE 0
											                 END,
			@v_workflow_template_stage_id = act.workflow_template_stage_id,
			@v_action_description = act.[description],
			--@v_is_stage_approval = COALESCE(stg.is_stage_approval,'0'),
			@v_is_stage_approval = COALESCE(next_stg.is_stage_approval,'0'),  -- modified by Pribadi Pamungkas. 2017-10-11. fix ke next stage
			--@v_approv_matrix_id = stg.approv_matrix_id,
			@v_approv_matrix_id = next_stg.approv_matrix_id,      -- modified by Pribadi Pamungkas. 2017-10-11. fix ke next stage
			@v_is_final_action = COALESCE(act.is_final_action,'0'),
			@v_transaction_status_id = act.transaction_status_id,
	    @v_is_fill_reference_number = COALESCE(act.is_fill_reference_number,'0'), -- new
	    @v_is_need_reason = COALESCE(act.is_need_reason,'0'), -- new
	    @v_pic_is_requester = COALESCE(next_stg.pic_is_requester,'0'), -- new
	    --sudah di-declare dibawah  --@v_group_pic_id = next_stg.group_pic_id, -- new
	    @v_is_remarks_mandatory = COALESCE(act.is_remarks_mandatory,'0'),
	    @v_is_check_transaction_status_core = COALESCE(act.is_check_transaction_status_core,'0'),
	    @v_code_transaction_status = act.code_transaction_status
		FROM
			dbo.m_workflow_template_action AS act INNER JOIN
			dbo.m_workflow_template_stage AS stg ON act.workflow_template_stage_id = stg.workflow_template_stage_id LEFT JOIN
			dbo.m_workflow_template_stage AS next_stg ON act.destination_stage_id = next_stg.workflow_template_stage_id
		WHERE
			act.workflow_template_stage_id = @v_current_workflow_template_stage_id  -- workflow_template_stage_id saat ini
			AND act.workflow_template_action_id = @pi_workflow_template_action_id   -- 
			AND act.is_delete = '0'
			AND stg.is_delete = '0'
		IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			BEGIN
				SET @po_message = 'Destination Action is not valid'
				GOTO ERROR_OUT
			END

    IF (@v_is_final_action = '0') AND (@v_destination_stage_id IS NULL) -- jika bukan final action, tetapi tidak ada destination_stage
			BEGIN
				SET @po_message = 'Missing Destination Stage'
				GOTO ERROR_OUT
			END


		--====================== cek remarks ======================
		IF (@v_is_remarks_mandatory = '1')	-- jika remarks wajib diisi
			BEGIN
				IF (LEN(COALESCE(@pi_remarks,''))<=1)	-- jika remaks berisi 1 karakter atau kurang
					BEGIN
			      SET @po_message = 'Please fill in the remarks field'
			      GOTO ERROR_OUT
					END
			END


		--====================== cek informasi transaksi mandatory ======================
		IF (@v_is_fill_reference_number = '1')
		  BEGIN
		    IF (COALESCE(@pi_reference_number,'') = '')
		      BEGIN
			      SET @po_message = 'Reference Number is empty'
			      GOTO ERROR_OUT
		      END
		    ELSE
		      BEGIN
						IF EXISTS(SELECT
												* 
											FROM 
												dbo.t_transaction 
											WHERE 
												reference_number = @pi_reference_number 
												AND status_id NOT IN (3,5)	-- Reject / Cancel
												AND is_delete = 0
												AND transaction_id <> @pi_transaction_id)
							BEGIN		-- jika sudah terpakai
								DECLARE @register_number_terpakai VARCHAR(9)
								SELECT
									@register_number_terpakai = register_number 
								FROM 
									dbo.t_transaction 
								WHERE 
									reference_number = @pi_reference_number 
									AND status_id NOT IN (3,5)	-- Reject / Cancel
									AND is_delete = 0
									AND transaction_id <> @pi_transaction_id
								SET @po_message = 'Duplicate Reference No. with Register No '+@register_number_terpakai
								GOTO ERROR_OUT
							END
						ELSE
							BEGIN
								
								DECLARE @servername VARCHAR(40)
								SELECT @servername = @@SERVERNAME

								IF @servername = 'ELI-DB10\EQUITY'	-- query berikut dijalankan hanya di server Production
									BEGIN

										---- cek di PL ada gak
										DECLARE @v_transaction_category_code VARCHAR(6)
										SELECT
											@v_transaction_category_code = mtc.transaction_category_code
										FROM
											dbo.m_transaction_type AS mtt INNER JOIN
											dbo.m_transaction_category AS mtc ON mtt.transaction_category_id = mtc.transaction_category_id
										WHERE
											mtt.transaction_type_id = @v_transaction_type_id

										DECLARE
											@return_value_core INT,
											@return_numrow_core INT = 0 

										IF @v_transaction_category_code = 'NB'	-- New Business
											BEGIN
												--EXEC @return_value_core = [db_link_project].[EDITS].[proc_get_new_business_information] @pi_proposal_no = @pi_reference_number
												--SELECT @return_numrow_core = @@ROWCOUNT	-- step ini hanya mengecek apakah data tsb ada atau tidak di PL, jadi cukup pake @@ROWCOUNT aja, gak usah melihat isi data hasilnya
												--IF (@return_value_core = 0) OR (@@ERROR = 0)	-- jika tidak ada error pas execute
												--	BEGIN
												--		IF (@return_numrow_core = 0)
												--			BEGIN
												--				SET @po_message = 'Reference No. not found. Please input Register No. from Core System!'
												--				GOTO ERROR_OUT
												--			END
												--	END
												--ELSE
												--	BEGIN
												--		SET @po_message = 'Failed to get data from core system'
												--		GOTO ERROR_OUT
												--	END
												DECLARE @temp_table_nb TABLE ( 
																										proposal_no VARCHAR(12),
																										policy_no VARCHAR(12),
																										proposal_status_code VARCHAR(24),
																										proposal_status VARCHAR(80)
																									)
												INSERT INTO @temp_table_nb
																(proposal_no,
																 policy_no,
																 proposal_status_code,
																 proposal_status)	
												EXEC [db_link_project].[EDITS].[proc_get_new_business_information] @pi_proposal_no = @pi_reference_number
												IF (@@ERROR <> 0)
													BEGIN
														SET @po_message = 'Failed to get data from core system'
														GOTO ERROR_OUT
													END
												IF NOT EXISTS(SELECT * FROM @temp_table_nb)
													BEGIN
														SET @po_message = 'Reference No. not found. Please input Register No. from Core System!'
														GOTO ERROR_OUT
													END
												ELSE	-- jika reference no ditemukan di PL
													BEGIN
														IF @v_is_check_transaction_status_core = '1'	-- Jika 'Check Transaction Status in Core'
															BEGIN
																IF COALESCE(@v_code_transaction_status,'') = ''	-- cek code_transaction_status
																	BEGIN
																		SET @po_message = 'Code Transaction Status is not defined in action setting'
																		GOTO ERROR_OUT
																	END
																ELSE
																	BEGIN
																		IF NOT EXISTS(
																									SELECT
																										*
																									FROM
																										@temp_table_nb AS tmp CROSS APPLY
																										dbo.func_explode(@v_code_transaction_status,';') AS list
																									WHERE
																										tmp.proposal_status_code = LTRIM(RTRIM(list.item))
																									)
																			BEGIN
																				SET @po_message = 'Transaction Status in Core System is not valid'
																				GOTO ERROR_OUT
																			END
																	END
															END
													END
											END
										ELSE IF @v_transaction_category_code = 'PS'	-- Policy Service
											BEGIN
												--EXEC @return_value_core = [db_link_project].[EDITS].[proc_get_alteration_information] @pi_registration_no = @pi_reference_number
												--SELECT @return_numrow_core = @@ROWCOUNT	-- step ini hanya mengecek apakah data tsb ada atau tidak di PL, jadi cukup pake @@ROWCOUNT aja, gak usah melihat isi data hasilnya
												--IF (@return_value_core = 0) OR (@@ERROR = 0)	-- jika tidak ada error pas execute
												--	BEGIN
												--		IF (@return_numrow_core = 0)
												--			BEGIN
												--				SET @po_message = 'Reference No. not found. Please input Register No. from Core System!'
												--				GOTO ERROR_OUT
												--			END
												--	END
												--ELSE
												--	BEGIN
												--		SET @po_message = 'Failed to get data from core system'
												--		GOTO ERROR_OUT
												--	END
												DECLARE @temp_table_ps TABLE ( 
																										registration_no VARCHAR(13),
																										policy_no VARCHAR(12),
																										task_type_code VARCHAR(24),
																										task_type VARCHAR(120),
																										task_status_code VARCHAR(16),
																										task_status VARCHAR(50)
																									)
												INSERT INTO @temp_table_ps
																(registration_no,
																 policy_no,
																 task_type_code,
																 task_type,
																 task_status_code,
																 task_status)
												EXEC [db_link_project].[EDITS].[proc_get_alteration_information] @pi_registration_no = @pi_reference_number
												IF (@@ERROR <> 0)
													BEGIN
														SET @po_message = 'Failed to get data from core system'
														GOTO ERROR_OUT
													END
												IF NOT EXISTS(SELECT * FROM @temp_table_ps)
													BEGIN
														SET @po_message = 'Reference No. not found. Please input Register No. from Core System!'
														GOTO ERROR_OUT
													END
												ELSE	-- jika reference no ditemukan di PL
													BEGIN
														IF @v_is_check_transaction_status_core = '1'	-- Jika 'Check Transaction Status in Core'
															BEGIN
																IF COALESCE(@v_code_transaction_status,'') = ''	-- cek code_transaction_status
																	BEGIN
																		SET @po_message = 'Code Transaction Status is not defined in action setting'
																		GOTO ERROR_OUT
																	END
																ELSE
																	BEGIN
																		IF NOT EXISTS(
																									SELECT
																										*
																									FROM
																										@temp_table_ps AS tmp CROSS APPLY
																										dbo.func_explode(@v_code_transaction_status,';') AS list
																									WHERE
																										tmp.task_status_code = LTRIM(RTRIM(list.item))
																									)
																			BEGIN
																				SET @po_message = 'Transaction Status in Core System is not valid'
																				GOTO ERROR_OUT
																			END
																	END
															END
													END
											END
										ELSE IF @v_transaction_category_code = 'CL'	-- Claim
											BEGIN
												--EXEC @return_value_core = [db_link_project].[EDITS].[proc_get_claim_information] @pi_claim_no = @pi_reference_number
												--SELECT @return_numrow_core = @@ROWCOUNT	-- step ini hanya mengecek apakah data tsb ada atau tidak di PL, jadi cukup pake @@ROWCOUNT aja, gak usah melihat isi data hasilnya
												--IF (@return_value_core = 0) OR (@@ERROR = 0)	-- jika tidak ada error pas execute
												--	BEGIN
												--		IF (@return_numrow_core = 0)
												--			BEGIN
												--				SET @po_message = 'Reference No. not found. Please input Register No. from Core System!'
												--				GOTO ERROR_OUT
												--			END
												--	END
												--ELSE
												--	BEGIN
												--		SET @po_message = 'Failed to get data from core system'
												--		GOTO ERROR_OUT
												--	END
												DECLARE @temp_table_cl TABLE ( 
																										claim_no VARCHAR(16),
																										policy_no VARCHAR(12),
																										claim_status_code VARCHAR(24),
																										claim_status VARCHAR(80)
																									)
												INSERT INTO @temp_table_cl
																(claim_no,
																 policy_no,
																 claim_status_code,
																 claim_status)
												EXEC [db_link_project].[EDITS].[proc_get_claim_information] @pi_claim_no = @pi_reference_number
												IF (@@ERROR <> 0)
													BEGIN
														SET @po_message = 'Failed to get data from core system'
														GOTO ERROR_OUT
													END
												IF NOT EXISTS(SELECT * FROM @temp_table_cl)
													BEGIN
														SET @po_message = 'Reference No. not found. Please input Register No. from Core System!'
														GOTO ERROR_OUT
													END
												ELSE	-- jika reference no ditemukan di PL
													BEGIN
														IF @v_is_check_transaction_status_core = '1'	-- Jika 'Check Transaction Status in Core'
															BEGIN
																IF COALESCE(@v_code_transaction_status,'') = ''	-- cek code_transaction_status
																	BEGIN
																		SET @po_message = 'Code Transaction Status is not defined in action setting'
																		GOTO ERROR_OUT
																	END
																ELSE
																	BEGIN
																		IF NOT EXISTS(
																									SELECT
																										*
																									FROM
																										@temp_table_cl AS tmp CROSS APPLY
																										dbo.func_explode(@v_code_transaction_status,';') AS list
																									WHERE
																										tmp.claim_status_code = LTRIM(RTRIM(list.item))
																									)
																			BEGIN
																				SET @po_message = 'Transaction Status in Core System is not valid'
																				GOTO ERROR_OUT
																			END
																	END
															END
													END
											END
										--ELSE IF @v_transaction_category_code = 'CO'	-- Collection
										--	BEGIN
											
										--	END
										ELSE
											BEGIN
												SET @po_message = 'Unknown transaction category'
												GOTO ERROR_OUT
											END

									END

								UPDATE
									dbo.t_transaction
								SET
									reference_number = @pi_reference_number
								WHERE
									transaction_id = @pi_transaction_id
								IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
									BEGIN
										SET @po_message = 'Failed update reference_number'
										GOTO ERROR_OUT
									END
							END
		      END
		  END


		IF (@v_is_need_reason = '1')
		  IF (COALESCE(@pi_reason_id,0) = 0)
		    BEGIN
			    SET @po_message = 'Reason is empty'
			    GOTO ERROR_OUT
		    END


		DECLARE @v_information_valid BIT = 1  -- nanti kalau sudah dikasih pengecekan, ganti dengan 0
		--bla bla bla
		IF @v_information_valid = 0
			BEGIN
				SET @po_message = 'Document can not be processed. Please check mandatory Information'
				GOTO ERROR_OUT
			END


		--====================== cek dokumen transaksi mandatory ======================
		IF @v_is_check_mandatory_document=1
			BEGIN
				DECLARE @v_document_valid BIT = 0
				SELECT @v_document_valid = dbo.func_is_document_complete(@pi_transaction_id)
				IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					BEGIN
						SET @po_message = 'Failed check document'
						GOTO ERROR_OUT
					END
    	
				IF @v_document_valid = 0
					BEGIN
						SET @po_message = 'Document can not be processed. Please check mandatory Document'
						GOTO ERROR_OUT
					END
			END
	
	
		--====================== update transaction task sebelumnya menjadi not available ======================
		UPDATE
			tt
		SET
			status_id = 3,    -- 3:Not Available
			modified_by = @pi_user_id,
			modified_date = @v_current_datetime
		FROM
			dbo.t_transaction_task AS tt INNER JOIN
			(
				SELECT
					MAX(process_sequence) AS process_sequence,
					transaction_id
				FROM
					dbo.t_transaction_task
				WHERE
					transaction_id = @pi_transaction_id
				GROUP BY
					transaction_id
			) AS tt_max ON tt.transaction_id = tt_max.transaction_id
							AND tt.process_sequence = tt_max.process_sequence
		IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			BEGIN
				SET @po_message = 'Failed update t_transaction_task'
				GOTO ERROR_OUT
			END


		--====================== history workflow ======================
		-- added by Pribadi Pamungkas. 2017-10-12
    IF EXISTS(SELECT
              	*
              FROM
              	dbo.t_transaction_history_workflow
              WHERE
                transaction_id = @pi_transaction_id
                AND workflow_template_stage_id = @v_workflow_template_stage_id  -- cek stage awal
                AND is_delete = 0)
      BEGIN  -- jika di stage awal (stage saat ini) sudah ada di tabel history_workflow

        UPDATE
          dbo.t_transaction_history_workflow
        SET
          last_user = @pi_user_id,                -- update user
          last_submit_date = @v_current_datetime  -- update tanggal
        WHERE
          transaction_id = @pi_transaction_id
          AND workflow_template_stage_id = @v_workflow_template_stage_id  -- update stage awal
          AND is_delete = 0

      END
    ELSE
      BEGIN  -- jika di stage awal (stage saat ini) BELUM ada di tabel history_workflow
        -- insert stage awal di tabel history_workflow
        INSERT INTO dbo.t_transaction_history_workflow
                   ([transaction_id]
                   ,[workflow_template_stage_id]
                   ,[last_user]
                   ,[last_submit_date]
                   ,[created_by]
                   ,[created_date]
                   ,[modified_by]
                   ,[modified_date]
                   ,[is_delete])
             VALUES
                   (@pi_transaction_id
                   ,@v_workflow_template_stage_id
                   ,@pi_user_id
                   ,@v_current_datetime
                   ,@pi_user_id
                   ,@v_current_datetime
                   ,@pi_user_id
                   ,@v_current_datetime
                   ,0)
      END



    -- insert transaction task
    DECLARE
      @v_next_process_sequence INT,
      @v_next_task_status_id INT = 2 --         <------------------ [1:Taken, 2:Available, 3:Not Available]
    SELECT
      @v_next_process_sequence = MAX(process_sequence) + 1
    FROM
      dbo.t_transaction_task
    WHERE
      transaction_id = @pi_transaction_id
    GROUP BY
      transaction_id

    IF (@v_is_final_action = '0') -- jika bukan final
      BEGIN
		    --====================== APPROVAL ======================
		    IF @v_is_stage_approval = '1'
		      BEGIN
		        IF @v_approv_matrix_id IS NULL
		          BEGIN
				        SET @po_message = 'Approval Matrix is not defined'
				        GOTO ERROR_OUT
		          END
            
            DECLARE @v_level_type_id INT
            SELECT
	            @v_level_type_id = mtd.level_type_id
            FROM
	            dbo.m_approv_matrix AS mtx INNER JOIN
	            dbo.m_approv_matrix_detail AS mtd ON mtx.approv_matrix_id = mtd.approv_matrix_id
            WHERE
              mtx.approv_matrix_id = @v_approv_matrix_id
              AND mtd.maximum_value >= @v_transaction_value
              AND mtd.currency_id = @v_currency_id


            -- list head (masih hardcode menggunakan SELECT satu-satu, harusnya menggunakan looping hierarki)
            DECLARE @list_user_approval TABLE ( userid VARCHAR(8))
            
            DECLARE 
              @v_organization_id INT,
              @v_organization_id_parent_1 INT,
              @v_organization_id_parent_2 INT,
              @v_organization_id_parent_3 INT,
              @v_organization_id_parent_4 INT,
              @v_organization_id_parent_5 INT,
              @v_level_type_id_1 INT,
              @v_level_type_id_2 INT,
              @v_level_type_id_3 INT,
              @v_level_type_id_4 INT,
              @v_level_type_id_5 INT,
              @v_user_id_head_1 VARCHAR(8),
              @v_user_id_head_2 VARCHAR(8),
              @v_user_id_head_3 VARCHAR(8),
              @v_user_id_head_4 VARCHAR(8),
              @v_user_id_head_5 VARCHAR(8)
            DECLARE list_organization_cursor CURSOR FAST_FORWARD FOR  
            SELECT
	            org.organization_id
            FROM
	            dbo.m_user AS usr INNER JOIN
	            dbo.m_user_organization AS org ON usr.username = org.user_id_organization
            WHERE
              usr.username = @pi_user_id
              AND usr.is_delete = 0
              AND usr.status_id = @status_id_aktif
            
            OPEN list_organization_cursor  
            FETCH NEXT FROM list_organization_cursor INTO @v_organization_id
            
            WHILE @@FETCH_STATUS = 0  
            BEGIN    
            
              -- reset
              SET @v_organization_id_parent_1 = NULL
              SET @v_organization_id_parent_2 = NULL
              SET @v_organization_id_parent_3 = NULL
              SET @v_organization_id_parent_4 = NULL
              SET @v_organization_id_parent_5 = NULL
              SET @v_level_type_id_1 = NULL
              SET @v_level_type_id_2 = NULL
              SET @v_level_type_id_3 = NULL
              SET @v_level_type_id_4 = NULL
              SET @v_level_type_id_5 = NULL
              SET @v_user_id_head_1 = NULL
              SET @v_user_id_head_2 = NULL
              SET @v_user_id_head_3 = NULL
              SET @v_user_id_head_4 = NULL
              SET @v_user_id_head_5 = NULL

              IF (@v_organization_id IS NOT NULL) AND (@v_level_type_id IS NOT NULL)
                BEGIN
                  SELECT
	                  @v_level_type_id_1 = level_type_id,
	                  @v_user_id_head_1 = user_id_head,
	                  @v_organization_id_parent_1 = organization_id_parent
                  FROM
	                  dbo.m_organization
                  WHERE
                    organization_id = @v_organization_id
                    AND level_type_id >= @v_level_type_id
                  IF (@@ROWCOUNT > 0) AND (COALESCE(@v_user_id_head_1,'')<>'')
                    INSERT INTO @list_user_approval ( userid ) VALUES (@v_user_id_head_1)
                END
              
              IF (@v_organization_id_parent_1 IS NOT NULL) AND (@v_level_type_id_1 IS NOT NULL)
                BEGIN
                  SELECT
	                  @v_level_type_id_2 = level_type_id,
	                  @v_user_id_head_2 = user_id_head,
	                  @v_organization_id_parent_2 = organization_id_parent
                  FROM
	                  dbo.m_organization
                  WHERE
                    organization_id = @v_organization_id_parent_1
                    AND level_type_id < @v_level_type_id_1
                  IF (@@ROWCOUNT > 0) AND (COALESCE(@v_user_id_head_2,'')<>'')
                    INSERT INTO @list_user_approval ( userid ) VALUES (@v_user_id_head_2)
                END

              IF (@v_organization_id_parent_2 IS NOT NULL) AND (@v_level_type_id_2 IS NOT NULL)
                BEGIN
                  SELECT
	                  @v_level_type_id_3 = level_type_id,
	                  @v_user_id_head_3 = user_id_head,
	                  @v_organization_id_parent_3 = organization_id_parent
                  FROM
	                  dbo.m_organization
                  WHERE
                    organization_id = @v_organization_id_parent_2
                    AND level_type_id < @v_level_type_id_2
                  IF (@@ROWCOUNT > 0) AND (COALESCE(@v_user_id_head_3,'')<>'')
                    INSERT INTO @list_user_approval ( userid ) VALUES (@v_user_id_head_3)
                END

              IF (@v_organization_id_parent_3 IS NOT NULL) AND (@v_level_type_id_3 IS NOT NULL)
                BEGIN
                  SELECT
	                  @v_level_type_id_4 = level_type_id,
	                  @v_user_id_head_4 = user_id_head,
	                  @v_organization_id_parent_4 = organization_id_parent
                  FROM
	                  dbo.m_organization
                  WHERE
                    organization_id = @v_organization_id_parent_3
                    AND level_type_id < @v_level_type_id_3
                  IF (@@ROWCOUNT > 0) AND (COALESCE(@v_user_id_head_4,'')<>'')
                    INSERT INTO @list_user_approval ( userid ) VALUES (@v_user_id_head_4)
                END

              IF (@v_organization_id_parent_4 IS NOT NULL) AND (@v_level_type_id_4 IS NOT NULL)
                BEGIN
                  SELECT
	                  @v_level_type_id_5 = level_type_id,
	                  @v_user_id_head_5 = user_id_head,
	                  @v_organization_id_parent_5 = organization_id_parent
                  FROM
	                  dbo.m_organization
                  WHERE
                    organization_id = @v_organization_id_parent_4
                    AND level_type_id < @v_level_type_id_4
                  IF (@@ROWCOUNT > 0) AND (COALESCE(@v_user_id_head_5,'')<>'')
                    INSERT INTO @list_user_approval ( userid ) VALUES (@v_user_id_head_5)
                END
      
              FETCH NEXT FROM list_organization_cursor INTO @v_organization_id  
            END  

            CLOSE list_organization_cursor  
            DEALLOCATE list_organization_cursor 


    			        
            IF EXISTS(SELECT * FROM @list_user_approval)
              BEGIN
		            -- insert history proses untuk pergantian action
		            -- Saat user memproses task transaksi untuk berpindah stage
		            -- Proses ini dijalankan saat user menekan tombol submit pada layar task information untuk memproses transaksi ke tahapan selanjutnya. Berikut informasi histori yang dibentuk oleh sistem:
		            -- 1.	Process Date = get date
		            -- 2.	Process Time = get time
		            -- 3.	User = get user
		            -- 4.	Action = get from field action
		            -- 5.	Reason = get from field reason
		            -- 6.	Remarks = get from field remarks
		            INSERT INTO dbo.t_transaction_history_process
				            (transaction_id,
				            process_date,
				            [user_id],
				            workflow_template_action_id,
				            action_description,
				            reason_id,
				            remarks,
				            created_by,
				            created_date,
				            modified_by,
				            modified_date,
				            is_delete)
		            VALUES
				            (@pi_transaction_id, -- transaction_id - bigint
				            @v_current_datetime, -- process_date - datetime
				            @pi_user_id, -- user_id - int
				            @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
				            @v_action_description,
				            @pi_reason_id, -- reason_id - int
				            @pi_remarks, -- remarks - varchar(255)
				            @pi_user_id, -- created_by - varchar(8)
				            @v_current_datetime, -- created_date - datetime
				            @pi_user_id, -- modified_by - varchar(8)
				            @v_current_datetime, -- modified_date - datetime
				            '0'  -- is_delete - char(1)
				            )
		            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			            BEGIN
				            SET @po_message = 'Failed insert t_transaction_history_process'
				            GOTO ERROR_OUT
			            END


				        INSERT INTO dbo.t_transaction_task
							        (transaction_id,
							        process_sequence,
							        [user_id],
							        status_id,
							        created_by,
							        created_date,
							        modified_by,
							        modified_date,
							        is_delete)
			          SELECT
			            @pi_transaction_id, -- transaction_id - bigint
			            @v_next_process_sequence, -- process_sequence - int
			            mu.username,   -- 
			            @v_next_task_status_id, -- status_id - int
			      	    @pi_user_id, -- created_by - varchar(8)
					        @v_current_datetime, -- created_date - datetime
					        @pi_user_id, -- modified_by - varchar(8)
					        @v_current_datetime, -- modified_date - datetime
					        '0'  -- is_delete - char(1)
			          FROM
			      	    @list_user_approval AS usr_appr INNER JOIN
			      	    dbo.m_user AS mu ON usr_appr.userid = mu.username
			          WHERE
			            mu.is_delete = 0
			            AND mu.is_delete = 0
									AND mu.status_id = @status_id_aktif
    			        
				        -- insert history pengiriman task ke pic yang dituju 
				        -- Saat sistem mengirimkan task ke pic yang dituju 
				        -- Proses ini dijalankan otomatis saat sistem sudah mengirimkan task ke PIC yang akan mengerjakan task tersebut. Berikut informasi histori yang dibentuk oleh sistem:
				        -- 1.	Process Date = get date
				        -- 2.	Process Time = get time
				        -- 3.	User = System
				        -- 4.	Action = ‘Assign task to  [Destination PIC]’
				        -- 5.	Reason = ‘NULL’
				        -- 6.	Remarks = ‘NULL’
				        INSERT INTO dbo.t_transaction_history_process
					        (transaction_id,
					        process_date,
					        [user_id],
					        workflow_template_action_id,
					        action_description,
					        reason_id,
					        remarks,
					        created_by,
					        created_date,
					        modified_by,
					        modified_date,
					        is_delete)
			          SELECT
			            @pi_transaction_id, -- transaction_id - bigint
			            @v_current_datetime, -- process_date - datetime
					        @pi_user_id, -- user_id - int
					        @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
					        'Send approval to '+mu.user_fullname,  -- gunakan nama lengkap
                  @pi_reason_id, -- reason_id - int
					        @pi_remarks, -- remarks - varchar(255)
					        @pi_user_id, -- created_by - varchar(8)
					        @v_current_datetime, -- created_date - datetime
					        @pi_user_id, -- modified_by - varchar(8)
					        @v_current_datetime, -- modified_date - datetime
					        '0'  -- is_delete - char(1)
			          FROM
			      	    @list_user_approval AS usr_appr INNER JOIN
			      	    dbo.m_user AS mu ON usr_appr.userid = mu.username
			          WHERE
			            mu.is_delete = 0
			            AND mu.is_delete = 0
									AND mu.status_id = @status_id_aktif
    			      
                --Send approval to john
              END
            ELSE
              BEGIN
				        SET @po_message = 'There is no approver is intended'
				        GOTO ERROR_OUT
              END
            
		      END
        ELSE
		    --====================== BUKAN APPROVAL, ALIAS STAGE NORMAL ======================
          BEGIN

            -- tambahin: jika stage next sudah ada di tabel t_transaction_history_workflow, maka gunakan pic di tabel tsb,
            --           jika belum ada, insert
            DECLARE @v_user_history_workflow VARCHAR(8) = NULL
            IF EXISTS(SELECT
                  	    *
                      FROM
                  	    dbo.t_transaction_history_workflow
                      WHERE
                        transaction_id = @pi_transaction_id
                        AND workflow_template_stage_id = @v_destination_stage_id  --stage tujuan
                        AND is_delete = 0)
              BEGIN
                -- gunakan pic ini
                SELECT
            	    @v_user_history_workflow = last_user
                FROM
            	    dbo.t_transaction_history_workflow
                WHERE
                  transaction_id = @pi_transaction_id
                  AND workflow_template_stage_id = @v_destination_stage_id  --stage tujuan
                  AND is_delete = 0

              END
            ELSE
              BEGIN
                -- get/cari pic
                SET @v_user_history_workflow = NULL
              END
     
            IF (COALESCE(@v_user_history_workflow,'') <> '')  -- jika user pada history_workflow sudah ada, maka gunakan user tsb sebagai pic
              BEGIN
		            -- insert history proses untuk pergantian action
		            -- Saat user memproses task transaksi untuk berpindah stage
		            -- Proses ini dijalankan saat user menekan tombol submit pada layar task information untuk memproses transaksi ke tahapan selanjutnya. Berikut informasi histori yang dibentuk oleh sistem:
		            -- 1.	Process Date = get date
		            -- 2.	Process Time = get time
		            -- 3.	User = get user
		            -- 4.	Action = get from field action
		            -- 5.	Reason = get from field reason
		            -- 6.	Remarks = get from field remarks
		            INSERT INTO dbo.t_transaction_history_process
				            (transaction_id,
				            process_date,
				            [user_id],
				            workflow_template_action_id,
				            action_description,
				            reason_id,
				            remarks,
				            created_by,
				            created_date,
				            modified_by,
				            modified_date,
				            is_delete)
		            VALUES
				            (@pi_transaction_id, -- transaction_id - bigint
				            @v_current_datetime, -- process_date - datetime
				            @pi_user_id, -- user_id - int
				            @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
				            @v_action_description,
				            @pi_reason_id, -- reason_id - int
				            @pi_remarks, -- remarks - varchar(255)
				            @pi_user_id, -- created_by - varchar(8)
				            @v_current_datetime, -- created_date - datetime
				            @pi_user_id, -- modified_by - varchar(8)
				            @v_current_datetime, -- modified_date - datetime
				            '0'  -- is_delete - char(1)
				            )
		            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			            BEGIN
				            SET @po_message = 'Failed insert t_transaction_history_process'
				            GOTO ERROR_OUT
			            END

                INSERT INTO dbo.t_transaction_task
                           ([transaction_id]
                           ,[process_sequence]
                           ,[user_id]
                           ,[status_id]
                           ,[created_by]
                           ,[created_date]
                           ,[modified_by]
                           ,[modified_date]
                           ,[is_delete])
                     VALUES
                           (@pi_transaction_id -- transaction_id - bigint
                           ,@v_next_process_sequence -- process_sequence - int
                           ,@v_user_history_workflow
                           ,@v_next_task_status_id -- status_id - int  
                           ,@pi_user_id -- created_by - varchar(8)
                           ,@v_current_datetime -- created_date - datetime
                           ,@pi_user_id -- modified_by - varchar(8)
                           ,@v_current_datetime -- modified_date - datetime
                           ,'0')
		            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			            BEGIN
				            SET @po_message = 'Failed insert t_transaction_task'
				            GOTO ERROR_OUT
			            END

		            -- insert history pengiriman task ke pic yang dituju 
		            -- Saat sistem mengirimkan task ke pic yang dituju 
		            -- Proses ini dijalankan otomatis saat sistem sudah mengirimkan task ke PIC yang akan mengerjakan task tersebut. Berikut informasi histori yang dibentuk oleh sistem:
		            -- 1.	Process Date = get date
		            -- 2.	Process Time = get time
		            -- 3.	User = System
		            -- 4.	Action = ‘Assign task to  [Destination PIC]’
		            -- 5.	Reason = ‘NULL’
		            -- 6.	Remarks = ‘NULL’
		            INSERT INTO dbo.t_transaction_history_process
			            (transaction_id,
			            process_date,
			            [user_id],
			            workflow_template_action_id,
			            action_description,
			            reason_id,
			            remarks,
			            created_by,
			            created_date,
			            modified_by,
			            modified_date,
			            is_delete)
		            SELECT
			            @pi_transaction_id, -- transaction_id - bigint
			            @v_current_datetime, -- process_date - datetime
			            'SYS', -- user_id - int
			            NULL, --@pi_workflow_template_action_id, -- workflow_template_action_id - bigint
			            'Assign task to '+user_fullname,  -- gunakan nama lengkap
			            @pi_reason_id, -- reason_id - int
			            NULL, --@pi_remarks, -- remarks - varchar(255)
			            @pi_user_id, -- created_by - varchar(8)
			            @v_current_datetime, -- created_date - datetime
			            @pi_user_id, -- modified_by - varchar(8)
			            @v_current_datetime, -- modified_date - datetime
			            '0'  -- is_delete - char(1)
		            FROM
			            dbo.m_user
		            WHERE
			            username = @v_user_history_workflow
		            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			            BEGIN
				            SET @po_message = 'Transaction not found'
				            GOTO ERROR_OUT
			            END

              END
            ELSE  -- jika belum ada, maka cari pic
              BEGIN
		            -- check pic yg dibutuhkan
		            DECLARE @v_pic_yg_dibutuhkan TINYINT = 0
		            -- 1: user dia sendiri
		            -- 2: head requester
		            -- 3: grup pic id
              
		            DECLARE @v_group_pic_id INT
		            SELECT
			            @v_pic_yg_dibutuhkan = CASE
										            WHEN pic_is_requester = '1' THEN 1--'user dia sendiri'
										            ELSE CASE user_workflow_id
												            WHEN 2 THEN 2 --'head requester'
												            WHEN 3 THEN 3 --'gunakan grup pic id'
											            END
									            END,
			            @v_group_pic_id = group_pic_id
		            FROM
			            dbo.m_workflow_template_stage
		            WHERE
			            workflow_template_stage_id = @v_destination_stage_id  --@v_workflow_template_stage_id
                
		            -- head requester
		            DECLARE @v_head_requester_user_id VARCHAR(8)
		            SELECT
			            @v_head_requester_user_id = org.user_id_head
		            FROM
			            dbo.m_user_organization AS muo INNER JOIN 
			            dbo.m_status AS ms ON muo.status_id = ms.status_id INNER JOIN
			            dbo.m_organization AS org ON muo.organization_id = org.organization_id
		            WHERE
			            muo.user_id_organization = @pi_user_id
			            AND muo.status_id = 5
			            AND ms.status_id = 5


			          IF (@v_pic_yg_dibutuhkan = 3)
			            IF (@v_group_pic_id IS NULL)
			              BEGIN
				              SET @po_message = 'Group PIC undefined'
				              GOTO ERROR_OUT
			              END
			          ELSE IF (@v_pic_yg_dibutuhkan = 2)
			            IF (COALESCE(@v_head_requester_user_id,'') = '')
			              BEGIN
				              SET @po_message = 'Head Requester PIC is empty'
				              GOTO ERROR_OUT
			              END

			          IF (@v_pic_yg_dibutuhkan <> 3)
			            BEGIN
		                -- get PIC
		                DECLARE @v_user_pic VARCHAR(8) = NULL
		                SELECT
			                @v_user_pic = CASE
							                WHEN pic_is_requester = '1' THEN @v_requester_id--'user dia sendiri'
							                ELSE CASE user_workflow_id
									                WHEN 2 THEN @v_head_requester_user_id --'head requester' (tambahin, jika kosong maka error, tidak bisa submit)
									                --WHEN 3 THEN @v_user_group_pic_id  --'gunakan grup pic id'
									                ELSE NULL
								                END
							                END
		                FROM
			                dbo.m_workflow_template_stage
		                WHERE
			                workflow_template_stage_id = @v_destination_stage_id  --@v_workflow_template_stage_id
		                IF @v_user_pic IS NULL
			                BEGIN
				                SET @po_message = 'PIC is empty'
				                GOTO ERROR_OUT
			                END
			            END


		            -- insert history proses untuk pergantian action
		            -- Saat user memproses task transaksi untuk berpindah stage
		            -- Proses ini dijalankan saat user menekan tombol submit pada layar task information untuk memproses transaksi ke tahapan selanjutnya. Berikut informasi histori yang dibentuk oleh sistem:
		            -- 1.	Process Date = get date
		            -- 2.	Process Time = get time
		            -- 3.	User = get user
		            -- 4.	Action = get from field action
		            -- 5.	Reason = get from field reason
		            -- 6.	Remarks = get from field remarks
		            INSERT INTO dbo.t_transaction_history_process
				            (transaction_id,
				            process_date,
				            [user_id],
				            workflow_template_action_id,
				            action_description,
				            reason_id,
				            remarks,
				            created_by,
				            created_date,
				            modified_by,
				            modified_date,
				            is_delete)
		            VALUES
				            (@pi_transaction_id, -- transaction_id - bigint
				            @v_current_datetime, -- process_date - datetime
				            @pi_user_id, -- user_id - int
				            @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
				            @v_action_description,
				            @pi_reason_id, -- reason_id - int
				            @pi_remarks, -- remarks - varchar(255)
				            @pi_user_id, -- created_by - varchar(8)
				            @v_current_datetime, -- created_date - datetime
				            @pi_user_id, -- modified_by - varchar(8)
				            @v_current_datetime, -- modified_date - datetime
				            '0'  -- is_delete - char(1)
				            )
		            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			            BEGIN
				            SET @po_message = 'Failed insert t_transaction_history_process'
				            GOTO ERROR_OUT
			            END
              
              
		            --==================================================
		            -- insert transaction task
		            --DECLARE
			          --  @v_next_process_sequence INT,
			          --  @v_next_task_status_id INT = 2 --         <------------------ [1:Taken, 2:Available, 3:Not Available]
		            SELECT
			            @v_next_process_sequence = MAX(process_sequence) + 1
		            FROM
			            dbo.t_transaction_task
		            WHERE
			            transaction_id = @pi_transaction_id
		            GROUP BY
			            transaction_id

		            -- jika user_workflow_id = 5, maka pakai group_pic_id
		            IF @v_pic_yg_dibutuhkan=3 --(jika yg dibutuhkan adalah group_pic_id)
			            BEGIN
				            -- cek ada grup pic enggak?
				            IF NOT EXISTS(
						            SELECT
							            mu.username
						            FROM
							            dbo.m_group_pic AS mgp INNER JOIN
							            dbo.m_group_pic_detail AS mgpd ON mgp.group_pic_id = mgpd.group_pic_id INNER JOIN
							            dbo.m_user AS mu ON mgpd.user_id_group_pic_detail = mu.[user_id]
						            WHERE
							            mgp.group_pic_id = @v_group_pic_id
							            AND (mgpd.branch_id = CAST(@v_branch_id AS VARCHAR(5)) OR mgpd.branch_id = '*')
							            AND (mgpd.transaction_type_id = CAST(@v_transaction_type_id AS VARCHAR(5)) OR mgpd.transaction_type_id = '*')
							            AND mgp.is_delete = '0'
							            AND mgpd.is_delete = '0'
							            AND mu.is_delete = 0
						            )
					            BEGIN
						            SET @po_message = 'Group PIC is empty'
						            GOTO ERROR_OUT
					            END

				            INSERT INTO dbo.t_transaction_task
							            (transaction_id,
							            process_sequence,
							            [user_id],
							            status_id,
							            created_by,
							            created_date,
							            modified_by,
							            modified_date,
							            is_delete)
				            SELECT DISTINCT
					            @pi_transaction_id, -- transaction_id - bigint
					            @v_next_process_sequence, -- process_sequence - int
					            mu.username,   -- gunakan user group pic (bisa lebih dari 1)
					            @v_next_task_status_id, -- status_id - int                             <----------------------- [1:Taken, 2:Available, 3:Not Available]
					            @pi_user_id, -- created_by - varchar(8)
					            @v_current_datetime, -- created_date - datetime
					            @pi_user_id, -- modified_by - varchar(8)
					            @v_current_datetime, -- modified_date - datetime
					            '0'  -- is_delete - char(1)
				            FROM
					            dbo.m_group_pic AS mgp INNER JOIN
					            dbo.m_group_pic_detail AS mgpd ON mgp.group_pic_id = mgpd.group_pic_id INNER JOIN
					            dbo.m_user AS mu ON mgpd.user_id_group_pic_detail = mu.[user_id]
				            WHERE
					            mgp.group_pic_id = @v_group_pic_id
					            AND (mgpd.branch_id = CAST(@v_branch_id AS VARCHAR(5)) OR mgpd.branch_id = '*')
					            AND (mgpd.transaction_type_id = CAST(@v_transaction_type_id AS VARCHAR(5)) OR mgpd.transaction_type_id = '*')
					            AND mgp.is_delete = '0'
					            AND mgpd.is_delete = '0'
					            AND mu.is_delete = 0
				            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					            BEGIN
						            SET @po_message = 'Group PIC is empty'
						            GOTO ERROR_OUT
					            END
            	    
				            -- insert history pengiriman task ke pic yang dituju 
				            -- Saat sistem mengirimkan task ke pic yang dituju 
				            -- Proses ini dijalankan otomatis saat sistem sudah mengirimkan task ke PIC yang akan mengerjakan task tersebut. Berikut informasi histori yang dibentuk oleh sistem:
				            -- 1.	Process Date = get date
				            -- 2.	Process Time = get time
				            -- 3.	User = System
				            -- 4.	Action = ‘Assign task to  [Destination PIC]’
				            -- 5.	Reason = ‘NULL’
				            -- 6.	Remarks = ‘NULL’
				            INSERT INTO dbo.t_transaction_history_process
					            (transaction_id,
					            process_date,
					            [user_id],
					            workflow_template_action_id,
					            action_description,
					            reason_id,
					            remarks,
					            created_by,
					            created_date,
					            modified_by,
					            modified_date,
					            is_delete)
				            SELECT DISTINCT
					            @pi_transaction_id, -- transaction_id - bigint
					            @v_current_datetime, -- process_date - datetime
					            'SYS', -- user_id - int
					            NULL, --@pi_workflow_template_action_id, -- workflow_template_action_id - bigint
					            'Assign task to '+mu.user_fullname,  -- gunakan nama lengkap
					            @pi_reason_id, -- reason_id - int
					            NULL, --@pi_remarks, -- remarks - varchar(255)
					            @pi_user_id, -- created_by - varchar(8)
					            @v_current_datetime, -- created_date - datetime
					            @pi_user_id, -- modified_by - varchar(8)
					            @v_current_datetime, -- modified_date - datetime
					            '0'  -- is_delete - char(1)
				            FROM
					            dbo.m_group_pic AS mgp INNER JOIN
					            dbo.m_group_pic_detail AS mgpd ON mgp.group_pic_id = mgpd.group_pic_id INNER JOIN
					            dbo.m_user AS mu ON mgpd.user_id_group_pic_detail = mu.[user_id]
				            WHERE
					            mgp.group_pic_id = @v_group_pic_id
					            AND (mgpd.branch_id = CAST(@v_branch_id AS VARCHAR(5)) OR mgpd.branch_id = '*')
					            AND (mgpd.transaction_type_id = CAST(@v_transaction_type_id AS VARCHAR(5)) OR mgpd.transaction_type_id = '*')
					            AND mgp.is_delete = '0'
					            AND mgpd.is_delete = '0'
					            AND mu.is_delete = 0
				            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					            BEGIN
						            SET @po_message = 'Transaction not found'
						            GOTO ERROR_OUT
					            END

			            END
		            ELSE -- jika yang dibutuhkan adalah SELAIN group_pic_id
			            BEGIN

				            INSERT INTO dbo.t_transaction_task
							            (transaction_id,
							            process_sequence,
							            [user_id],
							            status_id,
							            created_by,
							            created_date,
							            modified_by,
							            modified_date,
							            is_delete)
				            SELECT
					            @pi_transaction_id, -- transaction_id - bigint
					            MAX(process_sequence) + 1, -- process_sequence - int
					            @v_user_pic, -- user_id - varchar(8)  
					            @v_next_task_status_id, -- status_id - int 
					            @pi_user_id, -- created_by - varchar(8)
					            @v_current_datetime, -- created_date - datetime
					            @pi_user_id, -- modified_by - varchar(8)
					            @v_current_datetime, -- modified_date - datetime
					            '0'  -- is_delete - char(1)
				            FROM
					            dbo.t_transaction_task
				            WHERE
					            transaction_id = @pi_transaction_id
				            GROUP BY
					            transaction_id
				            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					            BEGIN
						            SET @po_message = 'Failed insert t_transaction_task'
						            GOTO ERROR_OUT
					            END

				            -- insert history pengiriman task ke pic yang dituju 
				            -- Saat sistem mengirimkan task ke pic yang dituju 
				            -- Proses ini dijalankan otomatis saat sistem sudah mengirimkan task ke PIC yang akan mengerjakan task tersebut. Berikut informasi histori yang dibentuk oleh sistem:
				            -- 1.	Process Date = get date
				            -- 2.	Process Time = get time
				            -- 3.	User = System
				            -- 4.	Action = ‘Assign task to  [Destination PIC]’
				            -- 5.	Reason = ‘NULL’
				            -- 6.	Remarks = ‘NULL’
				            INSERT INTO dbo.t_transaction_history_process
							            (transaction_id,
							            process_date,
							            [user_id],
							            workflow_template_action_id,
							            action_description,
							            reason_id,
							            remarks,
							            created_by,
							            created_date,
							            modified_by,
							            modified_date,
							            is_delete)
				            SELECT
					            @pi_transaction_id, -- transaction_id - bigint
					            @v_current_datetime, -- process_date - datetime
					            'SYS', -- user_id - int
					            NULL, --@pi_workflow_template_action_id, -- workflow_template_action_id - bigint
					            'Assign task to '+user_fullname,  -- gunakan nama lengkap
					            @pi_reason_id, -- reason_id - int
					            NULL, --@pi_remarks, -- remarks - varchar(255)
					            @pi_user_id, -- created_by - varchar(8)
					            @v_current_datetime, -- created_date - datetime
					            @pi_user_id, -- modified_by - varchar(8)
					            @v_current_datetime, -- modified_date - datetime
					            '0'  -- is_delete - char(1)
				            FROM
					            dbo.m_user
				            WHERE
					            username = @v_user_pic
					            AND is_delete = 0
					            AND status_id = @status_id_aktif
				            IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
					            BEGIN
						            SET @po_message = 'Transaction not found'
						            GOTO ERROR_OUT
					            END

			            END
              END

          END
      END
    ELSE  -- jika final
      BEGIN
        -- insert history proses untuk pergantian action
        -- Saat user memproses task transaksi untuk berpindah stage
        -- Proses ini dijalankan saat user menekan tombol submit pada layar task information untuk memproses transaksi ke tahapan selanjutnya. Berikut informasi histori yang dibentuk oleh sistem:
        -- 1.	Process Date = get date
        -- 2.	Process Time = get time
        -- 3.	User = get user
        -- 4.	Action = get from field action
        -- 5.	Reason = get from field reason
        -- 6.	Remarks = get from field remarks
        INSERT INTO dbo.t_transaction_history_process
            (transaction_id,
            process_date,
            [user_id],
            workflow_template_action_id,
            action_description,
            reason_id,
            remarks,
            created_by,
            created_date,
            modified_by,
            modified_date,
            is_delete)
        VALUES
            (@pi_transaction_id, -- transaction_id - bigint
            @v_current_datetime, -- process_date - datetime
            @pi_user_id, -- user_id - int
            @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
            @v_action_description,
            @pi_reason_id, -- reason_id - int
            @pi_remarks, -- remarks - varchar(255)
            @pi_user_id, -- created_by - varchar(8)
            @v_current_datetime, -- created_date - datetime
            @pi_user_id, -- modified_by - varchar(8)
            @v_current_datetime, -- modified_date - datetime
            '0'  -- is_delete - char(1)
            )
        IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
          BEGIN
            SET @po_message = 'Failed insert t_transaction_history_process'
            GOTO ERROR_OUT
          END
      END


		

		--====================== update t_transaction ======================
		DECLARE @v_final_destination_stage_id INT
		IF (@v_is_final_action = '1')
		  SET @v_final_destination_stage_id = @v_current_workflow_template_stage_id -- jika Final Action, maka biarkan menggunakan stage_id sebelumnya (maksudnya saat ini)
		ELSE
		  SET @v_final_destination_stage_id = @v_destination_stage_id

		UPDATE
			dbo.t_transaction
		SET
			workflow_template_stage_id = @v_final_destination_stage_id,
			status_id = @v_transaction_status_id,
			modified_by = @pi_user_id,
			modified_date = @v_current_datetime
		WHERE
			transaction_id = @pi_transaction_id
		IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			BEGIN
				SET @po_message = 'Failed update t_transaction'
				GOTO ERROR_OUT
			END

		COMMIT TRANSACTION proses_utama
		SET @po_message = 'Sukses'  -- variable output 
		SET @v_result = 1
		SELECT @v_result AS [status], @po_message AS [message]  -- dual output stored procedure, variable output dan table output
		GOTO CLEAN_OUT

	END TRY
	BEGIN CATCH	-- tangkap error message selain yg sudah dideskripsikan
		SET @po_message = LEFT(ERROR_MESSAGE(),400)	-- berhubung variable diatas diset 400, maka potong error message
		GOTO ERROR_OUT
	END CATCH

	
	ERROR_OUT: 
		ROLLBACK TRANSACTION proses_utama
		SELECT @v_result AS [status], @po_message AS [message]  -- dual output stored procedure, variable output dan table output
		--RETURN 1

	CLEAN_OUT:
		--RETURN 0
			
END


GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_get_user_transaction_category]    Script Date: 02/28/2018 17:17:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[v_get_user_transaction_category]
AS
	SELECT
		uc.id,
		 uc.user_id,
		 u.user_fullname,
		 uc.transaction_category_id,
		 c.description,
		 uc.is_delete,
		 c.transaction_category_code
	FROM m_user_transaction_category uc
	JOIN m_user u ON uc.user_id = u.username
	JOIN m_transaction_category c ON uc.transaction_category_id = c.transaction_category_id

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_m_approv_matrix_detail]    Script Date: 02/28/2018 17:15:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[v_m_approv_matrix_detail]
AS
    SELECT  amd.approv_matrix_detail_id ,
            amd.approv_matrix_id ,
            amd.maximum_value ,
			CONVERT(varchar, CONVERT(money, amd.maximum_value), 1) as format_maximum_value,
            amd.level_type_id ,
            lt.head_description AS level_type_desc, 
            amd.currency_id,
			cu.currency_code,
			cu.description as currency_description

    FROM    m_approv_matrix_detail amd
            INNER JOIN m_level_type lt ON lt.level_type_id = amd.level_type_id
			left JOIN m_currency cu on cu.currency_id =  amd.currency_id
    WHERE   lt.is_delete = 0
            AND amd.is_delete = 0


GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_m_eform_document]    Script Date: 02/28/2018 17:14:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- created by Pribadi Pamungkas. 2017-07-23
-- last modified by Pribadi Pamungkas. 2018-01-18 => tambah kolom applies_to

ALTER VIEW [dbo].[v_m_eform_document]
AS
    SELECT
  	  med.eform_document_id,
  	  med.eform_id,
  	  med.document_id,
  	  md.document_code,
  	  md.description,
  	  med.minimum_files,
  	  med.maximum_files,
  	  med.applies_to,
  	  med.seq_number,
  	  med.status_id,
  	  med.created_by,
  	  med.created_date,
  	  med.modified_by,
  	  med.modified_date,
  	  med.is_delete
    FROM
    	dbo.m_eform_document AS med INNER JOIN
    	dbo.m_document AS md ON med.document_id = md.document_id
    WHERE
      med.is_delete = '0'
      AND md.is_delete = '0'


GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_m_transaction_document]    Script Date: 02/28/2018 17:13:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[v_m_transaction_document]
AS
SELECT
  t.transaction_id,
  md.document_id,
  md.document_code,
  md.description,
  md.remarks,
  med.eform_document_id,
  med.eform_id,
  med.minimum_files,
  med.maximum_files,
  med.seq_number,
  med.status_id,
  COALESCE(df.jumlah_file,0) AS jumlah_file,
  COALESCE(ttd2.receive_doc_check,0) AS receive_doc_check,
  med.applies_to
FROM
  dbo.t_transaction AS t
  INNER JOIN 
  dbo.m_eform AS me ON t.transaction_type_id = me.transaction_type_id
  INNER JOIN 
  dbo.m_eform_document AS med ON me.eform_id = med.eform_id
  INNER JOIN 
  dbo.m_document AS md ON med.document_id = md.document_id
  LEFT JOIN
  (
    SELECT
	    ttd.transaction_id,
	    ttd.document_type_id,
	    ttd.receive_doc_check,
	    COUNT(*) AS jumlah_file
    FROM
	    dbo.t_transaction_document AS ttd INNER JOIN
	    dbo.t_transaction_document_file AS ttdf ON ttd.transaction_document_id = ttdf.transaction_document_id
    WHERE
      ttd.is_delete = '0'
      AND ttdf.is_delete = '0'
    GROUP BY
	    ttd.transaction_id,
	    ttd.document_type_id,
	    ttd.receive_doc_check
  ) AS df ON t.transaction_id = df.transaction_id
             AND md.document_id = df.document_type_id LEFT JOIN
  dbo.t_transaction_document AS ttd2 ON t.transaction_id = ttd2.transaction_id
																				AND md.document_id = ttd2.document_type_id
WHERE
  (t.is_delete = 0)
  AND (me.is_delete = '0')
  AND (med.is_delete = '0')
  AND (md.is_delete = '0')
  AND (
				CASE t.is_policy_holder
					WHEN 1 THEN 'p'
					WHEN 0 THEN 'n'
				END = med.applies_to
				OR med.applies_to = 'a'
			)

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_m_transaction_type]    Script Date: 02/28/2018 17:12:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[v_m_transaction_type]
AS
    SELECT  mtt.transaction_type_id ,
            mtt.transaction_type_code ,
            mtt.description AS transaction_type_desc ,
            mtt.remarks AS transaction_type_remarks ,
            mtt.footnote_receipt_form AS transaction_type_footnote_receipt_form ,
            mtt.transaction_category_id ,
            (
				SELECT TOP 1
					mew.eform_workflow_id
				FROM
					dbo.m_eform AS me INNER JOIN
					dbo.m_eform_workflow AS mew ON me.eform_id = mew.eform_id INNER JOIN
					dbo.m_workflow_template AS mwt ON mew.workflow_template_id = mwt.workflow_template_id
				WHERE
					mew.effective_date <= CONVERT (date, GETDATE())
					AND me.is_delete = '0'
					AND mew.is_delete = '0'
					AND mwt.is_delete = '0'
					AND mew.status_id = 5
					AND me.transaction_type_id =  mtt.transaction_type_id
					AND mew.source_transaction_id = 1
					-- tambahkan status aktif kalau perlu
				ORDER BY
					mew.effective_date DESC

			) AS eform_workflow_id,
            --mtc.transaction_category_id ,
            mtc.transaction_category_code ,
            mtc.description AS transaction_category_desc,
            mtt.is_delete
    FROM    m_transaction_type mtt
            INNER JOIN m_transaction_category mtc ON mtc.transaction_category_id = mtt.transaction_category_id
    WHERE   mtt.is_delete = 0
            AND mtc.is_delete = 0

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_t_transaction_audit_history]    Script Date: 02/28/2018 16:49:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_t_transaction_audit_history] AS
SELECT 
	h.id_t_audit_history,
	h.id_transaction,
	h.register_number,
	h.id_status_audit,
	s.description,
	h.remarks,
	h.created_by,
	CAST(h.created_date AS DATE) as created_date, 
	h.created_date AS created_date_time,
	u.user_fullname,
	h.is_delete
FROM t_audit_history h
LEFT JOIN m_user u ON u.username = h.created_by
JOIN m_status_audit s ON h.id_status_audit = s.id_status_audit
WHERE h.is_delete = 0
GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_transaction_audit]    Script Date: 02/28/2018 16:10:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_transaction_audit] AS
SELECT
	t.transaction_id,
	t.register_number,
	t.policy_id_reference,
	t.policy_holder_reference,
	t.product_name_reference,
	t.reference_number,
	t.branch_id,
	t.transaction_type_id,
	b.description AS branch_desc,
	tt.description AS trans_type_desc,
	s.description AS stage_desc,
	ts.description As status_desc,
	tc.description AS cat_desc,
	tt.transaction_category_id,
	dbo.get_audit_exist(t.transaction_id) AS has_audit,
	dbo.get_latest_audit_status(t.transaction_id) AS latest_audit_status,
	dbo.get_is_follow_up_audit(t.transaction_id) AS is_follow_up_audit,
	dbo.get_is_complience_audit(t.transaction_id) AS is_complience_audit,
	dbo.get_latest_audit_date(t.transaction_id) AS latest_audit_date,
	t.created_by,
	mu.user_fullname,
	dbo.get_latest_auditor(t.transaction_id) AS latest_auditor,
	t.status_id,
	t.is_delete
FROM t_transaction t
JOIN m_branch b ON b.branch_id = t.branch_id
JOIN m_transaction_type tt ON tt.transaction_type_id = t.transaction_type_id
JOIN m_transaction_category tc ON tt.transaction_category_id = tc.transaction_category_id
JOIN m_workflow_template_stage s ON s.workflow_template_stage_id = t.workflow_template_stage_id
JOIN m_transaction_status ts ON ts.transaction_status_id = t.status_id
LEFT JOIN m_user mu ON t.created_by = mu.username 
WHERE t.is_delete = 0

GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [db_edits]
GO

/****** Object:  View [dbo].[v_user]    Script Date: 02/28/2018 16:09:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[v_user] AS
SELECT 
	m.user_id,
	m.username,
	m.user_fullname,
	m.branch_id,
	m.status_id,
	m.created_by,
	m.created_date,
	m.modified_by,
	m.modified_date,
	m.add_audit,
	m.view_history_audit,
	b.branch_code,
	b.description,
	org.organization_id,
	org.description AS organization
FROM m_user m
LEFT JOIN m_branch b ON (m.branch_id = b.branch_id)
LEFT JOIN (
	SELECT 
		g.status_id, 
		g.user_id_organization, 
		g.organization_id, 
		m.description
	FROM m_user_organization g
	JOIN m_organization m ON m.organization_id = g.organization_id
	WHERE g.status_id = 5 and g.is_delete = 0
) AS org ON (m.username = org.user_id_organization)
where
	m.is_delete = 0


GO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
