USE [DB_EDITS]
GO
/****** Object:  StoredProcedure [dbo].[proc_submit_transaction]    Script Date: 10/11/2017 11:45:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2017-10-09 => tambah approval stage
-- todo: - cek status tiap tabel (active) dan is_delete
--       - informasi mandatory
--       - getdate dijadikan 1 saja, biar sama jamnya [done]
--ALTER PROCEDURE [dbo].[proc_submit_transaction]
DECLARE
	@pi_transaction_id BIGINT = 68,
	@pi_workflow_template_action_id BIGINT = 22,  -- next workflow_template_action_id yg akan dituju
	@pi_reason_id INT = NULL,
	@pi_reference_number VARCHAR(50) = NULL,
	@pi_remarks VARCHAR(255) = 'tolong dibantu',
	@pi_user_id VARCHAR(8) = 'FMJ',
	@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

		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)
		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)
		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),
			@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
		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
		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 INNER 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 Stage 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 informasi transaksi mandatory ======================
		IF (@v_is_fill_reference_number = '1')
		  IF (COALESCE(@pi_reference_number,'') = '')
		    BEGIN
			    SET @po_message = 'Reference Number is empty'
			    GOTO ERROR_OUT
		    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
PRINT 'v_next_process_sequence: '+CAST(@v_next_process_sequence AS VARCHAR(10))
		--====================== APPROVAL ======================
		IF @v_is_stage_approval = '1'
		  BEGIN
PRINT 'approval'
		    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


PRINT 'a'
				    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
PRINT 'bukan approval'
        -- 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
PRINT 'mau insert ke history_process, dgn template_action_id '+CAST(@pi_workflow_template_action_id AS VARCHAR(10))+' '+@v_action_description
		        -- 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
PRINT 'b'
            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
PRINT 'gunakan v_user_history_workflow '+@v_user_history_workflow+' untuk pic di transaction_history_process'
		        -- 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
			        @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
			        'Assign task to '+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
			        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
PRINT 'cari pic yuks'
		        -- 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
			        
PRINT 'v_pic_yg_dibutuhkan: '+CAST(@v_pic_yg_dibutuhkan AS VARCHAR(10))
PRINT 'v_group_pic_id: '+CAST(@v_group_pic_id AS varchar(10))		


		        -- 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
PRINT 'v_head_requester_user_id: '+@v_head_requester_user_id


			      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
    PRINT 'v_user_pic: '+@v_user_pic
			        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
PRINT 'insert history_process done'
          
		        --==================================================
		        -- 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
PRINT 'v_next_process_sequence: '+CAST(@v_next_process_sequence AS VARCHAR(10))
		        -- 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 = @v_branch_id 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
PRINT 'c'
				        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,   -- 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 = @v_branch_id 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
					        @pi_transaction_id, -- transaction_id - bigint
					        @v_current_datetime, -- process_date - datetime
					        'SYS', -- user_id - int
					        @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
					        @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 = @v_branch_id 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
PRINT 'insert transaction_task ke '+@v_user_pic
				        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
					          PRINT '1'
						        SET @po_message = 'Failed insert t_transaction_task'
						        GOTO ERROR_OUT
					        END
PRINT 'insert history_process ke '+@v_user_pic
				        -- 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
					        @pi_workflow_template_action_id, -- workflow_template_action_id - bigint
					        'Assign task to '+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
					        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


		

		--====================== 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
