DECLARE @StartDate VARCHAR(12) = '20240101'
DECLARE @EndDate VARCHAR(12) = '20240808'
DECLARE @Position VARCHAR(12) = ''
DECLARE @ApplicantIDTo VARCHAR(12) = ''
DECLARE @ApplicantIDFrom VARCHAR(12) = ''
DECLARE @ApplicantActivity VARCHAR(12) = ''
DECLARE @ApplicantStatus VARCHAR(12) = ''
DECLARE @MenuID VARCHAR(10) = 'PA01'
DECLARE @Userid VARCHAR(10) = 'minovais'


DECLARE @table_maxtivity AS TABLE ( emp_id VARCHAR(18) )




INSERT  INTO @table_maxtivity
        SELECT  EmployeeID
        FROM    dbo.PHRRC0027
        GROUP BY EmployeeID 

		--select * from @table_maxtivity
 
--INSERT  INTO @table_activity
SELECT  m2.Position ,
        pb.ObjectDescription Positiondesc ,
        m1.EmployeeID ,
        m1.FullName ,
        m1.BirthPlace ,
        m1.BirthDate ,
        gend.GenderDescription gender ,
        marstat.MaritalStatusDescription marital ,
        edulvl.EducationLevelDescription edulevel ,
        m8.Institution ,
        FORMAT(m8.GPA,'N2') AS GPA,
        m2.ApplicantAction,
        act.ApplicantActionDescription ,
        m2.ApplicantStatus ,
        stat.ApplicantStatusDescription AS AppActDes ,
        CONVERT(VARCHAR(25), dbo.fn_formatdatetime(@StartDate, 'dd mmmm yyyy')) AS StartDate ,
        CONVERT(VARCHAR(25), dbo.fn_formatdatetime(@EndDate, 'dd mmmm yyyy')) AS EndDate
FROM    dbo.PHRRC0001 m1
        LEFT OUTER JOIN dbo.PHRRC0002 m2 ON m1.EmployeeID = m2.EmployeeID
                                            AND m2.StartDate <= @EndDate
                                            AND m2.EndDate >= @StartDate
        LEFT OUTER JOIN dbo.PHRRC0008 m8 ON m1.EmployeeID = m8.EmployeeID
                                            AND m8.StartDate <= @EndDate
                                            AND m8.EndDate >= @StartDate
        LEFT OUTER JOIN dbo.PHRRC0009 AS m9 ON m9.EmployeeID = m1.EmployeeID
                                               AND m9.StartDate <= @EndDate
                                               AND m9.EndDate >= @StartDate
        LEFT OUTER JOIN dbo.PHRRCAPPSTAT stat ON m2.ApplicantStatus = stat.ApplicantStatus
                                                  AND stat.StartDate <= @EndDate
                                                  AND stat.EndDate >= @StartDate
        LEFT OUTER JOIN dbo.PHRRCAPPACT act ON m2.ApplicantAction = act.ApplicantAction
                                                  AND act.StartDate <= @EndDate
                                                  AND act.EndDate >= @StartDate
        LEFT OUTER JOIN dbo.PHRPAGENDER gend ON m1.Gender = gend.Gender
        LEFT OUTER JOIN dbo.PHRPAMSTAT marstat ON m1.MaritalStatus = marstat.MaritalStatus
        LEFT OUTER JOIN dbo.PHRPAEDULVL edulvl ON m8.EducationLevel = edulvl.EducationLevel
        LEFT OUTER JOIN dbo.PHROM0001 jb ON m2.Job = jb.ObjectID
                                            AND jb.ObjectClass = 'J'
        LEFT OUTER JOIN dbo.PHROM0001 pb ON m2.Position = pb.ObjectID
                                            AND pb.ObjectClass = 'P'
        INNER JOIN @table_maxtivity maxtivity ON maxtivity.emp_id = m2.EmployeeID
WHERE   m2.StartDate <= @EndDate
        AND ( m1.EndDate >= @StartDate )
        AND ( m1.EmployeeID >= @ApplicantIDFrom
              OR @ApplicantIDFrom = ''
            )
        AND ( m1.EmployeeID <= @ApplicantIDTo
              OR @ApplicantIDTo = ''
            )
        AND ( m2.Position = @Position
              OR @Position = ''
            )
        AND ( m2.ApplicantAction= @ApplicantActivity
              OR @ApplicantActivity = ''
            )
        AND ( m2.ApplicantStatus = @ApplicantStatus
              OR @ApplicantStatus = ''
            )