USE [db_edits]
GO
/****** Object:  StoredProcedure [dbo].[proc_submit_transaction]    Script Date: 12/29/2017 10:44:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- changelog:
-- 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()
	

	BEGIN TRY
	BEGIN TRANSACTION proses_utama

		--====================== 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(20),
																										proposal_status VARCHAR(30)
																									)
												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(20),
																										task_type VARCHAR(30),
																										task_status_code VARCHAR(10),
																										task_status VARCHAR(30)
																									)
												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(20),
																										claim_status VARCHAR(30)
																									)
												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
	            m_approv_matrix AS mtx INNER JOIN
	            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
	            m_user AS usr INNER JOIN
	            m_user_organization AS org ON usr.username = org.user_id_organization
            WHERE
              usr.username = @pi_user_id
            
            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
	                  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
	                  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
	                  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
	                  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
	                  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
    			        
				        -- 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
    			      
                --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
				            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
