CLIENT SUPPORT » IMFI Bugs

??USE [MinovaHR_Indomobil_Prod]

GO

/****** Object:  StoredProcedure [dbo].[rp_trPS00_ess]    Script Date: 28/02/2025 14.51.05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[rp_trPS00_ess] -- exec rp_trPS00 '100', '201011', '00000324', '0', '', 'scriberion', 'triadblank'

    @landscape VARCHAR(3) ,

    @payperiod VARCHAR(6) ,

    @employee_id VARCHAR(8) ,

    @running VARCHAR(2) ,

    @paytype VARCHAR(2) ,

    @key1 AS NVARCHAR(MAX) ,

    @key2 AS NVARCHAR(MAX)

AS 

	------BEGIN

	------	SET NOCOUNT ON added to prevent extra result sets from

	------	interfering with SELECT statements.

	------    SET NOCOUNT ON;

--DECLARE @landscape VARCHAR(3) = '100'

--DECLARE @payperiod VARCHAR(6) = '202502'

--DECLARE @employee_id VARCHAR(8) = '20090832'

--DECLARE @running VARCHAR(2) = '1'

--DECLARE @paytype VARCHAR(2) = ''

--DECLARE @key1 AS NVARCHAR(max) = 'scriberion'

--DECLARE @key2 AS NVARCHAR(max) = 'triadblank'

	------Insert statements for procedure here

	------DECLARE @RunPeriodMonthMax VARCHAR(12)

	------DECLARE @RunPeriodYearMax VARCHAR(12)

	------SELECT  @RunPeriodMonthMax = MAX(run_period_month) ,

	------@RunPeriodYearMax = MAX(run_period_year)

	------FROM    hr_tr0300

	------WHERE   employee_id = @employee_id

	------AND SUBSTRING(pay_period_year, 1, 4) = SUBSTRING(@payperiod, 1, 4)

	------GROUP BY employee_id 

DECLARE @companycode AS VARCHAR(50)

DECLARE @companyname AS NVARCHAR(50)

DECLARE @companylogo AS VARBINARY(MAX)

DECLARE @paymonth AS NVARCHAR(2)

DECLARE @payyear AS NVARCHAR(4)

DECLARE @begda AS NVARCHAR(8)

DECLARE @endda AS NVARCHAR(8)

SET @paymonth = RIGHT(@payperiod, 2)

SET @payyear = LEFT(@payperiod, 4)

SET @begda = @payperiod + '01'

SET @endda = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1, @begda)), 112)

--IF ( @RunPeriodMonthMax = @paymonth ) 

--    BEGIN

        DECLARE @payslipsigned AS NVARCHAR(50)

        SELECT TOP ( 1 )

                @payslipsigned = [value]

        FROM    hr_cu0307

        WHERE   ( landscape = @landscape )

                AND ( pay_param = 'PAYSLIPSIGNED' )

                AND ( start_date <= @endda

                      AND end_date >= @endda

                    )

        IF ( @running = '1' ) 

            BEGIN

                SELECT TOP ( 1 )

                        @companycode = company_id

                FROM    hr_tr0300

                WHERE   ( landscape = @landscape )

                        AND ( employee_id = @employee_id )

                        AND ( pay_period_month = @paymonth )

                        AND ( pay_period_year = @payyear )

                        AND ( run_period_month = @paymonth )

                        AND ( run_period_year = @payyear )

            END

        ELSE 

            BEGIN

                SELECT TOP ( 1 )

                        @companycode = company_id

                FROM    hr_tr0300_sim

                WHERE   ( landscape = @landscape )

                        AND ( employee_id = @employee_id )

                        AND ( pay_period_month = @paymonth )

                        AND ( pay_period_year = @payyear )

                        AND ( run_period_month = @paymonth )

                        AND ( run_period_year = @payyear )

            END

        SELECT  @companyname = description ,

                @companylogo = logo

        FROM    base_cust_ref_companycode

        WHERE   ( landscape = @landscape )

                AND ( companycode = @companycode )

        IF ( @running = '1' ) 

            BEGIN   

				DECLARE @TableCA TABLE

				(

					employee_id VARCHAR(20)

					--,wage_type VARCHAR(20)

					--,rate DECIMAL(22,0)

					,amount DECIMAL(22,0)

					,retrotype VARCHAR(20)

				)

				INSERT INTO @TableCA

				SELECT DISTINCT sumca.employee_id, SUM(DISTINCT(sumca.amount)) AS amount, MIN(sumca.retrotype) FROM

				( SELECT DISTINCT ca.employee_id, ca.wage_type,  CAST(dbo.GetPEN(@landscape, ca.rate) AS DECIMAL) 'rate' , CAST(dbo.GetPEN(@landscape, ca.amount) AS DECIMAL) 'amount', ca.retrotype

				FROM (

				SELECT * ,

                    'N' retrotype

                FROM   hr_tr0301 WHERE hr_tr0301.wage_type = '3000'

									AND hr_tr0301.employee_id = @employee_id

									AND hr_tr0301.pay_period_month = @paymonth

									AND hr_tr0301.pay_period_year = @payyear

									AND hr_tr0301.run_period_month = @paymonth

									AND hr_tr0301.run_period_year = @payyear

                UNION ALL

                SELECT * ,