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 * ,
                    'R' retrotype
                FROM   hr_tr0301_retro WHERE hr_tr0301_retro.wage_type = '3000'
									AND hr_tr0301_retro.employee_id = @employee_id
									AND hr_tr0301_retro.pay_period_month = @paymonth
									AND hr_tr0301_retro.pay_period_year = @payyear
									AND hr_tr0301_retro.run_period_month = @paymonth
									AND hr_tr0301_retro.run_period_year = @payyear
				) ca ) sumca
				GROUP BY sumca.employee_id

                SELECT DISTINCT  @payslipsigned payslipsigned ,
                        c308.landscape ,
                        @companyname companyname ,
                        @companylogo logo ,
                        c308.reportid ,
                        c308.groupby ,
                        c308P.description parentdescription ,
                        c308.sequence ,
                        c308.description ,
                        c308.paytype ,
                        c308.wage_type ,
                        c308.isdisplaywage_type ,
                        c308.isdisplaynumber ,
                        c308.isdisplayrate ,
                        c308.isdisplayamount ,
                        c308.start_date ,
                        c308.end_date ,
                        c308.isactive ,
                        c308.change_by ,
                        c308.change_date ,
                        c308.created_by ,
                        c308.created_date ,
                        hr_tr0301.employee_id ,
                        hr_tr0301.pay_period_month ,
                        hr_tr0301.pay_period_year ,
                        hr_tr0301.run_period_month ,
                        hr_tr0301.run_period_year ,
                        hr_tr0301.split_indicator , 
						--hr_tr0301.rate, hr_tr0301.amount, 
                        CAST(dbo.GetPEN(@landscape, hr_tr0301.rate) AS DECIMAL) 'rate' ,
                        --( ( ISNULL(CONVERT(INT, c308.[percent]), 100) / 100 ) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL) ) 'amount' ,
						CASE WHEN c308.paytype = 'T' THEN ( ( ISNULL(CONVERT(INT, c308.[percent]), 100) / 100 ) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL) ) + ISNULL(tblca.amount,0)
							 ELSE ( ( ISNULL(CONVERT(INT, c308.[percent]), 100) / 100 ) * CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL) )
						END 'amount' ,
                        hr_tr0301.number ,
                        hr_tr0301.currency ,
                        hr_tr0301.flag ,
                        md001.full_name ,
                        md003.tax_status ,
                        tax_stat.description tax_status_desc ,
                        md001.marital_status ,
                        ms.description maritaldescription ,
                        md003.pay_industry ,
                        md003.pay_area ,
                        md003.pay_class ,
                        md003.pay_grade ,
                        grd.description AS grd_desc ,
                        md002.job ,
                        md002.employee_area ,
                        area.description areaname ,
                        md002.organization ,
                        org.description orgname ,
                        md002.position ,
                        pos.description posname ,
                        md002.employee_office ,
                        office.emp_subarea_description AS emp_office_desc
                FROM    hr_cu0308 AS c308
                        LEFT JOIN hr_cu0308 AS c308P ON c308.landscape = c308P.landscape
                                                        AND c308.groupby = c308P.reportid
                        INNER JOIN ( SELECT * ,
                                            'N' retrotype
                                     FROM   hr_tr0301
                                     UNION ALL
                                     SELECT * ,
                                            'R' retrotype
                                     FROM   hr_tr0301_retro
                                   ) hr_tr0301 ON c308.landscape = hr_tr0301.landscape
                                                  AND c308.wage_type = hr_tr0301.wage_type
                                                  AND c308.retrotype = hr_tr0301.retrotype
                                                  AND ( c308.paytype = @paytype
                                                        OR @paytype = ''
                                                      )
                        INNER JOIN ( SELECT m1.*
                                     FROM   hr_md_emp_md0001 m1
                                     WHERE  ( m1.start_date <= @endda
                                              AND m1.end_date >= @endda
                                            )
                                   ) md001 ON md001.landscape = hr_tr0301.landscape
                                              AND md001.emp_id = hr_tr0301.employee_id
						LEFT JOIN @TableCA AS tblca
							ON hr_tr0301.employee_id = tblca.employee_id AND tblca.retrotype = hr_tr0301.retrotype
                        LEFT JOIN ( SELECT  m1.*
                                    FROM    hr_md_emp_md0003 m1
                                    WHERE   ( m1.start_date <= @endda
                                              AND m1.end_date >= @endda
                                            )
                                  ) md003 ON md001.landscape = md003.landscape
                                             AND md001.emp_id = md003.emp_id
                        LEFT JOIN ( SELECT  m1.*
                                    FROM    hr_md_emp_md0002 m1
                                    WHERE   ( m1.start_date <= @endda
                                              AND m1.end_date >= @endda
                                            )
                                  ) md002 ON hr_tr0301.landscape = md002.landscape
                                             AND hr_tr0301.employee_id = md002.emp_id
                        LEFT JOIN base_cust_ref_emp_area area ON md002.landscape = area.landscape
                                                              AND md002.employee_area = area.emp_area
                        LEFT JOIN base_cust_ref_emp_office office ON office.landscape = md002.landscape
                                                              AND md002.employee_office = office.emp_subarea
                        LEFT JOIN ( SELECT  m1.*
                                    FROM    hr_md_orm_object m1
                                    WHERE   class = 'O'
                                            AND ( start_date <= @endda
                                                  AND end_date >= @endda
                                                )
                                  ) org ON md002.landscape = org.landscape
                                           AND md002.organization = org.object
                        LEFT JOIN ( SELECT  m1.*
                                    FROM    hr_md_orm_object m1
                                    WHERE   class = 'P'
                                            AND ( start_date <= @endda
                                                  AND end_date >= @endda
                                                )
                                  ) pos ON md002.landscape = pos.landscape
                                           AND md002.position = pos.object
                        LEFT JOIN base_cust_ref_pay_grade grd ON md003.landscape = grd.landscape
                                                              AND md003.pay_grade = grd.pay_grade
                        LEFT JOIN base_cust_ref_marital_status ms ON md001.landscape = ms.landscape
                                                              AND md001.marital_status = ms.code
                        LEFT JOIN hr_cu0302 tax_stat ON tax_stat.landscape = md003.landscape
                                                        AND md003.tax_status = tax_stat.status
                                                        AND tax_stat.start_date <= @endda
                                                        AND tax_stat.end_date >= @endda
                WHERE   c308.landscape = @landscape
                        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
			  --AND hr_tr0301.amount <> 0
                        AND CAST(dbo.GetPEN(@landscape, hr_tr0301.amount) AS DECIMAL) <> 0
                        AND dbo.CekObjectKey(@landscape, @key1, @key2,
                                             'M!N0V@2010') > 0
                ORDER BY c308.groupby ,
                        c308.wage_type
            END
        ELSE 
            SELECT  @payslipsigned payslipsigned ,
                    c308.landscape ,
                    @companyname companyname ,
                    @companylogo logo ,
                    c308.reportid ,
                    c308.groupby ,
                    c308P.description parentdescription ,
                    c308.sequence ,
                    c308.description ,
                    c308.paytype ,
                    c308.wage_type ,
                    c308.isdisplaywage_type ,
                    c308.isdisplaynumber ,
                    c308.isdisplayrate ,
                    c308.isdisplayamount ,
                    c308.start_date ,
                    c308.end_date ,
                    c308.isactive ,
                    c308.change_by ,
                    c308.change_date ,
                    c308.created_by ,
                    c308.created_date ,
                    hr_tr0301_sim.employee_id ,
                    hr_tr0301_sim.pay_period_month ,
                    hr_tr0301_sim.pay_period_year ,
                    hr_tr0301_sim.run_period_month ,
                    hr_tr0301_sim.run_period_year ,
                    hr_tr0301_sim.split_indicator , 
			  --hr_tr0301_sim.rate, hr_tr0301_sim.amount, 
                    CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.rate) AS DECIMAL) 'rate' ,
                    CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.amount) AS DECIMAL) 'amount' ,
                    hr_tr0301_sim.number ,
                    hr_tr0301_sim.currency ,
                    hr_tr0301_sim.flag ,
                    md001.full_name ,
                    md003.tax_status ,
                    tax_stat.description tax_status_desc ,
                    md001.marital_status ,
                    ms.description maritaldescription ,
                    md003.pay_industry ,
                    md003.pay_area ,
                    md003.pay_class ,
                    md003.pay_grade ,
                    grd.description AS grd_desc ,
                    md002.job ,
                    md002.employee_area ,
                    area.description areaname ,
                    md002.organization ,
                    org.description orgname ,
                    md002.position ,
                    pos.description posname ,
                    md002.employee_office ,
                    office.emp_subarea_description AS emp_office_desc
            FROM    hr_cu0308 AS c308
                    LEFT JOIN hr_cu0308 AS c308P ON c308.landscape = c308P.landscape
                                                    AND c308.groupby = c308P.reportid
                    INNER JOIN ( SELECT * ,
                                        'N' retrotype
                                 FROM   hr_tr0301_sim
                                 UNION ALL
                                 SELECT * ,
                                        'R' retrotype
                                 FROM   hr_tr0301_retro_sim
                               ) hr_tr0301_sim ON c308.landscape = hr_tr0301_sim.landscape
                                                  AND c308.wage_type = hr_tr0301_sim.wage_type
                                                  AND c308.retrotype = hr_tr0301_sim.retrotype
                                                  AND ( c308.paytype = @paytype
                                                        OR @paytype = ''
                                                      )
                    INNER JOIN ( SELECT m1.*
                                 FROM   hr_md_emp_md0001 m1
                                 WHERE  ( m1.start_date <= @endda
                                          AND m1.end_date >= @endda
                                        )
                               ) md001 ON md001.landscape = hr_tr0301_sim.landscape
                                          AND md001.emp_id = hr_tr0301_sim.employee_id
                    LEFT JOIN ( SELECT  m1.*
                                FROM    hr_md_emp_md0003 m1
                                WHERE   ( m1.start_date <= @endda
                                          AND m1.end_date >= @endda
                                        )
                              ) md003 ON md001.landscape = md003.landscape
                                         AND md001.emp_id = md003.emp_id
                    LEFT JOIN ( SELECT  m1.*
                                FROM    hr_md_emp_md0002 m1
                                WHERE   ( m1.start_date <= @endda
                                          AND m1.end_date >= @endda
                                        )
                              ) md002 ON hr_tr0301_sim.landscape = md002.landscape
                                         AND hr_tr0301_sim.employee_id = md002.emp_id
                    LEFT JOIN base_cust_ref_emp_area area ON md002.landscape = area.landscape
                                                             AND md002.employee_area = area.emp_area
                    LEFT JOIN base_cust_ref_emp_office office ON office.landscape = md002.landscape
                                                              AND md002.employee_office = office.emp_subarea
                    LEFT JOIN ( SELECT  m1.*
                                FROM    hr_md_orm_object m1
                                WHERE   class = 'O'
                                        AND ( start_date <= @endda
                                              AND end_date >= @endda
                                            )
                              ) org ON md002.landscape = org.landscape
                                       AND md002.organization = org.object
                    LEFT JOIN ( SELECT  m1.*
                                FROM    hr_md_orm_object m1
                                WHERE   class = 'P'
                                        AND ( start_date <= @endda
                                              AND end_date >= @endda
                                            )
                              ) pos ON md002.landscape = pos.landscape
                                       AND md002.position = pos.object
                    LEFT JOIN base_cust_ref_pay_grade grd ON md003.landscape = grd.landscape
                                                             AND md003.pay_grade = grd.pay_grade
                    LEFT JOIN base_cust_ref_marital_status ms ON md001.landscape = ms.landscape
                                                              AND md001.marital_status = ms.code
                    LEFT JOIN hr_cu0302 tax_stat ON tax_stat.landscape = md003.landscape
                                                    AND md003.tax_status = tax_stat.status
                                                    AND tax_stat.start_date <= @endda
                                                    AND tax_stat.end_date >= @endda
            WHERE   c308.landscape = @landscape
                    AND hr_tr0301_sim.employee_id = @employee_id
                    AND hr_tr0301_sim.pay_period_month = @paymonth
                    AND hr_tr0301_sim.pay_period_year = @payyear
                    AND hr_tr0301_sim.run_period_month = @paymonth
                    AND hr_tr0301_sim.run_period_year = @payyear
			  --AND hr_tr0301_sim.amount <> 0
                    AND CAST(dbo.GetPEN(@landscape, hr_tr0301_sim.amount) AS DECIMAL) <> 0
                    AND dbo.CekObjectKey(@landscape, @key1, @key2,
                                         'M!N0V@2010') > 0
            ORDER BY c308.groupby ,
                    c308.wage_type
    --END			
--ELSE 
--    BEGIN
--        SELECT  *
--        FROM    dbo.hr_tr0300
--        WHERE   employee_id = @employee_id
--                AND SUBSTRING(pay_period_year, 1, 4) = @payperiod
--            END
    --END