PERTALIFE

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

            )