PT BIA

USE [MinovaES_BIA_BaseServerCloud]

GO

/****** Object:  StoredProcedure [dbo].[PRPTPAALLEMPLOYEE]    Script Date: 25/08/2025 10.20.15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PRPTPAALLEMPLOYEE]

    (

      @CompanyID VARCHAR(5) ,

      @EmployeeStatus VARCHAR(5) ,

      @EmployeeType VARCHAR(5) ,

      @EmployeeArea VARCHAR(5) ,

      @EmployeeOffice VARCHAR(5) ,

      @PayrollGroup VARCHAR(5) ,

      @Date VARCHAR(12) ,

      @UserID VARCHAR(12) ,

      @MenuID VARCHAR(10)

    )

AS 

--DECLARE @CompanyID VARCHAR(20) = '1000'

--DECLARE @EmployeeStatus VARCHAR(20) = '01'

--DECLARE @EmployeeType VARCHAR(20)= ''

--DECLARE @EmployeeArea VARCHAR(20)= '0001'

--DECLARE @EmployeeOffice VARCHAR(20) = '0001'

--DECLARE @PayrollGroup VARCHAR(20) = ''

--DECLARE @Date VARCHAR(12) = '20250807'

--DECLARE @UserID VARCHAR(10) = 'nando'

--DECLARE @MenuID VARCHAR(10) = 'PAR19'

------------------ // Get Otorisasi //---------------

DECLARE @TableResult TABLE ( EmployeeID VARCHAR(20) )

INSERT  INTO @TableResult

        EXEC dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, -- varchar(max)

            @UserID = @UserID

 -- varchar(max)

------------------ // End Get Otorisasi //---------------

DECLARE @now VARCHAR(12) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

DECLARE @PermanentDate VARCHAR(5)

SELECT  @PermanentDate = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_PRMNTDATE'

DECLARE @PermanentDate2 VARCHAR(5)

SELECT  @PermanentDate2 = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_DATETYP'

DECLARE @HiringDate VARCHAR(5)

SELECT  @HiringDate = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_HIRINGDATE'

DECLARE @LastAssign VARCHAR(5)

SELECT  @LastAssign = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_LASTASSGNMNT'

DECLARE @Resign VARCHAR(5)

SELECT  @Resign = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIGN'

DECLARE @Expired VARCHAR(5)

SELECT  @Expired = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_DATESPEC'

DECLARE @joingroup VARCHAR(5)

SELECT  @joingroup = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_DATETYP_JOINGROUP'

DECLARE @Resident VARCHAR(5)

SELECT  @Resident = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIDENT'

DECLARE @HP VARCHAR(5)

SELECT  @HP = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_COMMTYPHP'

DECLARE @Email VARCHAR(5)

SELECT  @Email = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_COMMTYPEMAIL'

DECLARE @IDKTP VARCHAR(5)

SELECT  @IDKTP = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDKTP'

DECLARE @IDPassport VARCHAR(5)

SELECT  @IDPassport = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDPSSPRT'

DECLARE @IDSIMA VARCHAR(5)

SELECT  @IDSIMA = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDSIMA'

DECLARE @IDSIMB VARCHAR(5)

SELECT  @IDSIMB = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDSIMB'

DECLARE @IDSIMC VARCHAR(5)

SELECT  @IDSIMC = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_IDSIMC'

DECLARE @ResidentKTP VARCHAR(5)

SELECT  @ResidentKTP = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIDENTKTP'

DECLARE @ResidentKost VARCHAR(5)

SELECT  @ResidentKost = Value1

FROM    PCMEPGENPARAM

WHERE   Parameter = 'PRPTPAALLEMPLOYEE_RESIDENTKOST'

DECLARE @par_pension VARCHAR(10)

SELECT  @par_pension = Value1

FROM    dbo.PCMEPGENPARAM

WHERE   Parameter = 'MDREMINDERMAXAGEPENSION'

DECLARE @tbl_3 TABLE

    (

      emp_id VARCHAR(18) ,

      edu_level VARCHAR(180) ,

      major VARCHAR(600) ,

      gpa VARCHAR(180) ,

      edu_level_desc VARCHAR(200) ,

      edu_start VARCHAR(80) ,

      institution VARCHAR(200)

    )

    --INSERT  INTO @tbl_3

    --        SELECT  a.* ,

    --                edu_level.description

    --        FROM    ( SELECT    emp_id ,

    --                            MAX(edu_level) AS edu_level ,

    --                            major ,

    --                            institution ,

    --                            CONVERT(DECIMAL(18, 2), gpa) AS gpa,

    --                            MAX (start_date) AS edu_start

    --                  FROM      dbo.hr_md_emp_md0008 AS md08

    --                  GROUP BY  emp_id ,

    --                            major ,

    --                            institution ,

    --                            gpa

    --                ) AS a

--    --                LEFT JOIN base_cust_ref_edu_level AS edu_level ON a.edu_level = edu_level.code

--INSERT  INTO @tbl_3

--       -- SELECT  TOP(1)

--        SELECT  EmployeeID ,

--      -- start_date, 

--       --edu_type,

--                edu.EducationLevel ,

--                Major ,

--                --Institution ,

--                CASE WHEN inst.InstitutionDescription = 'OTHERS'

--                     THEN edu.OtherInstitution

--                     ELSE inst.InstitutionDescription

--                END AS Institution ,

--                GPA ,

--                edu_level.EducationLevelDescription ,

--                edu.StartDate

--        FROM    dbo.PHRPA0008 AS edu

--                LEFT JOIN PHRPAEDULVL AS edu_level ON edu.EducationLevel = edu_level.EducationLevel

--                LEFT JOIN PHRPAINST inst ON edu.Institution = inst.InstitutionType

--        WHERE   edu.EndDate = ( SELECT  MAX(EndDate)

--                                FROM    dbo.PHRPA0008 AS x

--                                WHERE   x.EmployeeID = edu.EmployeeID

--                                        AND x.EducationType = 'F'

--                              )

--                AND edu.StartDate <= @Date

--                AND edu.EndDate >= @Date

--                AND edu.LastEducation = '1'

--        ORDER BY edu.Sequence DESC

INSERT  INTO @tbl_3

        SELECT  EmployeeID ,

                edu.EducationLevel ,

                Major ,

                GPA ,

                edu_level.EducationLevelDescription ,

                edu.StartDate ,

                CASE WHEN inst.InstitutionDescription = 'OTHERS'

                     THEN edu.OtherInstitution

                     ELSE inst.InstitutionDescription

                END AS Institution

        FROM    dbo.PHRPA0008 AS edu

                LEFT JOIN PHRPAEDULVL AS edu_level ON edu.EducationLevel = edu_level.EducationLevel

                LEFT JOIN PHRPAINST inst ON edu.Institution = inst.InstitutionType

        WHERE   edu.LastEducation = '1'

        ORDER BY edu.Sequence DESC

DECLARE @tbl_1 TABLE

    (

      level1 VARCHAR(50) ,

      level1_desc VARCHAR(500) ,

      level2 VARCHAR(50) ,

      level2_desc VARCHAR(500) ,

      level3 VARCHAR(50) ,

      level3_desc VARCHAR(500) ,

      level4 VARCHAR(50) ,

      level4_desc VARCHAR(500) ,

      level5 VARCHAR(50) ,

      level5_desc VARCHAR(500) ,

      level6 VARCHAR(50) ,

      level6_desc VARCHAR(500) ,

      level7 VARCHAR(50) ,

      level7_desc VARCHAR(500) ,

      level8 VARCHAR(50) ,

      level8_desc VARCHAR(500) ,

      emp_id VARCHAR(50)

    )

    --INSERT  INTO @tbl_1

            --SELECT DISTINCT

            --        a.org_id_01 ,

            --        a.org_name_01 ,

            --        a.org_id_02 ,

            --        a.org_name_02 ,

            --        a.org_id_03 ,

            --        a.org_name_03 ,

            --        a.org_id_04 ,

            --        a.org_name_04 ,

            --        a.org_id_05 ,

            --        a.org_name_05 ,

            --        a.org_id_06 ,

            --        a.org_name_06 ,

            --        a.org_id_07 ,

            --        a.org_name_07 ,

            --        a.org_id_08 ,

            --        a.org_name_08 ,

            --        md2.EmployeeID

            --FROM    dbo.GetOrgInOrgWide('00000001', @Date) AS a

            --        INNER JOIN dbo.PHRPA0002 AS md2 ON a.org_id = md2.Organization

            --                                           AND md2.StartDate <= @Date

            --                                           AND md2.EndDate >= @Date                                                          	 

DECLARE @tbl_2 TABLE

    (

      emp_id VARCHAR(18) ,

      movement_type VARCHAR(12) ,

      jumlah DECIMAL(18, 0)

    )	

INSERT  INTO @tbl_2

        SELECT DISTINCT

                EmployeeID ,

                MovementType ,

                COUNT(StartDate)

        FROM    dbo.PHRPA0002 WITH ( NOLOCK )

        --WHERE   MovementType = '60'

        GROUP BY EmployeeID ,

                MovementType

DECLARE @tbl_md2_last TABLE

    (

      --landscape VARCHAR(3) ,

      emp_id VARCHAR(18) ,

      start_date VARCHAR(8) ,

      end_date VARCHAR(8) ,

      movement_type VARCHAR(10) ,

      reason VARCHAR(20) ,

      company_id VARCHAR(4) ,

      costcenter VARCHAR(18) ,

      employee_area VARCHAR(50) ,

      employee_office VARCHAR(50) ,

      employee_status VARCHAR(50) ,

      employee_type VARCHAR(50) ,

      employee_subtype VARCHAR(50) ,

      tax_office VARCHAR(50) ,

      payroll_group VARCHAR(20) ,

      organization VARCHAR(50) ,

      position VARCHAR(50) ,

      job VARCHAR(50) ,

      remark VARCHAR(MAX),

	  divisioncode VARCHAR(10)

          --additional_1 VARCHAR(8) ,

          --datedifff VARCHAR(MAX)

    )

INSERT  INTO @tbl_md2_last

        SELECT  tbl2.* --,

                    --DATEDIFF(DAY, tbl2.realDate, @Date) datedifff

        FROM    ( SELECT    EmployeeID ,

                            StartDate ,

                            EndDate ,

                            MovementType ,

                            MovementReason ,

                            CompanyID ,

                            CostCenter ,

                            EmployeeArea ,

                            EmployeeOffice ,

                            EmployeeStatus ,

                            EmployeeType ,

                            EmployeeSubType ,

                            TaxOffice ,

                            PayrollGroup ,

                            Organization ,

                            Position ,

                            Job ,

                            Notes ,

							DivisionCode

                                --CASE WHEN ISNULL(additional_1, '') = ''

                                --     THEN StartDate

                                --     ELSE additional_1

                                --END AS realDate

                  FROM      dbo.PHRPA0002 WITH ( NOLOCK )

                ) AS tbl2

SELECT DISTINCT 

        md01.EmployeeID ,

        md01.FullName ,

        md01.ExternalID ,

		md01.NumberOfChildren,

        md02_last.company_id ,

        CONVERT(DATE, md02_hiring.StartDate) AS join_date ,

        CONVERT(DATE, md02_last.start_date) AS permanent_date, ---- CONVERT(DATE, md39_permanent.Date) AS permanent_date , ----// change for BIA 20250825

        CONVERT(DATE, md39_joingroup.Date) AS join_group ,

        mov_type_last.MovementTypeDescription AS effective_type_status ,

        CONVERT(DATE, md02_last.start_date) AS effective_date ,

            --CONVERT(DATE, md02_last.additional_1) AS real_date ,

        md02_last.employee_status ,

        emp_status_last.EmployeeStatusDescription AS emp_status ,

        --CONVERT(DATE, md02_resign.StartDate) AS resign_date ,

        CONVERT(DATE,md02_resign.StartDate) AS resign_date ,

		CONVERT(DATE, md02_enddate.EndDate) AS end_date,

        emp_type_last.EmployeeTypeDescription AS employee_type ,

        CASE WHEN ( md39_contract.Date >= @Date )

             THEN CONVERT(DATE, md39_contract.Date)

             ELSE NULL

        END expired_status ,

        --tbl_2.jumlah AS contract_extent ,

        ws_type.WorkScheduleTypeDesc AS workschedule_type ,

        md03.TaxStatus ,

        md02_last.employee_area AS area_code ,

        emp_area_last.EmployeeAreaDescription AS area_desc ,

        md02_last.employee_office AS office_code ,

        emp_office_last.EmployeeOfficeDesc AS office_desc ,

        md02_last.payroll_group AS payroll_group_id ,

        payroll_group_last.PayrollGroupDescription AS payroll_group ,

        emp_sub_type_desc.EmployeeSubTypeDesc AS employee_subtype ,

        tbl_1.level1 ,

        tbl_1.level1_desc ,

        tbl_1.level2 ,

        tbl_1.level2_desc ,

        tbl_1.level3 ,

        tbl_1.level3_desc ,

        tbl_1.level4 ,

        tbl_1.level4_desc ,

        tbl_1.level5 ,

        tbl_1.level5_desc ,

        tbl_1.level6 ,

        tbl_1.level6_desc ,

        tbl_1.level7 ,

        tbl_1.level7_desc ,

        tbl_1.level8 ,

        tbl_1.level8_desc ,

            --md02_last.costcenter AS cc_code ,

            --cc.description AS cc_desc ,

            md02_last.position AS pos_code ,

			pos.ObjectDescription AS pos_desc,

            --p.description AS pos_desc ,

            --md02_last.job AS job_code ,

            --j.description AS job_desc ,

        pay_grade.PayrollGradeDescription AS pay_grade ,

        md01.NickName ,

        gender.GenderDescription AS gender ,

        md01.BirthPlace ,

        CONVERT (DATE, md01.BirthDate) AS birth_date ,

        FLOOR(DATEDIFF(DAY, md01.BirthDate, @Date) / 365.25) AS age ,

        dbo.GetDateDiffTahunBulanHari(md02_hiring.StartDate, @now) AS MasaKerja ,

        CASE WHEN ( FLOOR(DATEDIFF(DAY, md01.BirthDate, @Date) / 365.25) ) > @par_pension

             THEN 'YES'

             ELSE ''

        END AS Flag_Pension ,

        md06_resident.Address ,

        ( SELECT    ProvinceDescription

          FROM      PHRPAPROV

          WHERE     Province = md06_resident.Province

        ) AS province ,

            --md06_resident.location ,

        md06_resident.PostalCode ,

        md06_resident.TelephoneNumber ,

            --md12_hp.CommunicationDescription AS cellphone ,

        md12_email.CommunicationDescription AS email ,

        religion.ReligionDescription AS religion ,

        marital_status.MaritalStatusDescription AS marital_status ,

		--tax_status.TaxStatusDescription AS marital_status,

        --md11.BloodType ,

        bld.BloodTypeDescription AS BloodType ,

        --md13_ktp.IDDescription AS id ,

		md01.IDCard AS id,

        CONVERT(DATE, md13_ktp.EndDate) AS ktp_expired ,

        md06_ktp.Address AS ktp_address ,

        ( SELECT    ProvinceDescription

          FROM      PHRPAPROV

          WHERE     Province = md06_ktp.Province

        ) AS ktp_province ,

        md06_ktp.PostalCode AS ktp_postalcode ,

            --( SELECT    description

            --  FROM      base_cust_ref_id_type

            --  WHERE     code = md13_a.id_type

            --) AS id_a ,

            --md13_a.id_description AS id_a_no ,

            --CONVERT(DATE, md13_a.end_date) AS id_a_expired ,

            --( SELECT    description

            --  FROM      base_cust_ref_id_type

            --  WHERE     code = md13_b.id_type

            --) AS id_b ,

            --md13_b.id_description AS id_b_no ,

            --CONVERT(DATE, md13_b.end_date) AS id_b_expired ,

            --( SELECT    description

            --  FROM      base_cust_ref_id_type

            --  WHERE     code = md13_c.id_type

            --) AS id_c ,

        md13_c.IDDescription AS id_c_no ,

        CONVERT(DATE, md13_c.EndDate) AS id_c_expired ,

        md13_passport.IDDescription AS id_password_no ,

        CONVERT(DATE, md13_passport.EndDate) AS id_password_expired ,

            --md06_cp.contact_person AS cp_name ,

        md06_cp.Address AS cp_address ,

        md12_hp.CommunicationDescription AS cp_tlp, --md06_cp.TelephoneNumber AS cp_tlp , -----// edit tri 20250610 redmine 3659

        md06_cp.CellphoneNumber AS cp_cell ,

        md03.NPWP ,

            --CASE WHEN md03.npwp_date = '' THEN ''

            --     ELSE dbo.fn_formatdatetime(md03.npwp_date, 'dd mmm yyyy')

            --END AS npwp_date ,

            --md03.BPJSTKType ,

            --jams_type.description AS jams_type ,

           -- md03.BPJSTKNumber ,

            --CASE WHEN md03.additional_1 = '' THEN ''

            --     ELSE dbo.fn_formatdatetime(md03.additional_1, 'dd mmm yyyy')

            --END AS additional_1 ,

            --pension_type.PensionTypeDescription AS pension_type ,

            --md03.PensionType ,

            --CASE WHEN md03.additional_2 = '' THEN ''

            --     ELSE dbo.fn_formatdatetime(md03.additional_2, 'dd mmm yyyy')

            --END AS additional_2 ,

        cust_bank_id.BankDescription AS bank_id ,

        md03.BankAccount ,

        md03.BankAccountName ,

        tbl_3.edu_level_desc ,

        tbl_3.major ,

        tbl_3.institution ,

        tbl_3.gpa ,

        md02_last.remark ,

		md02_last.reason,

		emp_mov_reason.MovementReasonDesc,

		emp_division.DivisionCodeDescription,

		emp_division.EstateCode,

		emp_class.PayrollGrade,

		emp_class.PayrollClass,

		pay_class.PayrollClassDescription,

		emp_office_last.CodeOffice,

		'00'+emp_office_last.CodeOffice AS Plant,

		emp_ethnic.EthnicDescription,

		md13a.BenefitNo AS BPJSKESEHATAN,

		md13.BenefitNo AS BPJSKETENAGAKERJAAN

		    --CASE WHEN ISNULL(gapok.amount, '') = '' THEN 0

            --     ELSE CONVERT(DECIMAL, dbo.GetPEN(@landscape, gapok.amount))

            --END amount_gapok ,

            --CASE WHEN ( SELECT  valuation

            --            FROM    hr_cu0300

            --            WHERE   code = makan.wage_type

            --          ) = 'I2'

            --     THEN ( SELECT  amount

            --            FROM    hr_cu0304

            --            WHERE   start_date <= @Date

            --                    AND end_date >= @Date

            --                    AND wage_type = makan.wage_type

            --                    AND ( pay_grade = md03.pay_grade

            --                          OR pay_grade = '*'

            --                        )

            --                    AND ( pay_class = md03.pay_class

            --                          OR pay_class = '*'

            --                        )

            --                    AND ( pay_area = md03.pay_area

            --                          OR pay_area = '*'

            --                        )

            --          )

            --     ELSE ( CASE WHEN ISNULL(makan.amount, '') = '' THEN 0

            --                 ELSE CONVERT(DECIMAL, dbo.GetPEN(makan.amount))

            --            END )

            --END amount_makan ,

            --CASE WHEN ( SELECT  valuation

            --            FROM    hr_cu0300

            --            WHERE   code = ttp.wage_type

            --          ) = 'I2'

            --     THEN ( SELECT  amount

            --            FROM    hr_cu0304

            --            WHERE   start_date <= @Date

            --                    AND end_date >= @Date

            --                    AND wage_type = ttp.wage_type

            --                    AND ( pay_grade = md03.pay_grade

            --                          OR pay_grade = '*'

            --                        )

            --                    AND ( pay_class = md03.pay_class

            --                          OR pay_class = '*'

            --                        )

            --                    AND ( pay_area = md03.pay_area

            --                          OR pay_area = '*'

            --                        )

            --          )

            --     ELSE ( CASE WHEN ISNULL(ttp.amount, '') = '' THEN 0

            --                 ELSE CONVERT(DECIMAL, dbo.GetPEN(@landscape,

            --                                                  ttp.amount))

            --            END )

            --END amount_transport ,

            --CASE WHEN ( SELECT  valuation

            --            FROM    hr_cu0300

            --            WHERE   code = hp.wage_type

            --          ) = 'I2'

            --     THEN ( SELECT  amount

            --            FROM    hr_cu0304

            --            WHERE   start_date <= @Date

            --                    AND end_date >= @Date

            --                    AND wage_type = hp.wage_type

            --                    AND ( pay_grade = md03.pay_grade

            --                          OR pay_grade = '*'

            --                        )

            --                    AND ( pay_class = md03.pay_class

            --                          OR pay_class = '*'

            --                        )

            --                    AND ( pay_area = md03.pay_area

            --                          OR pay_area = '*'

            --                        )

            --          )

            --     ELSE ( SELECT  amount

            --            FROM    hr_cu0303

            --            WHERE   start_date <= @Date

            --                    AND end_date >= @Date

            --                    AND wage_type = hp.wage_type

            --          )

            --END amount_hp ,

            --CASE WHEN ( SELECT  valuation

            --            FROM    hr_cu0300

            --            WHERE   code = tj.wage_type

            --          ) = 'I2'

            --     THEN ( SELECT  amount

            --            FROM    hr_cu0304

            --            WHERE   start_date <= @Date

            --                    AND end_date >= @Date

            --                    AND wage_type = tj.wage_type

            --                    AND ( pay_grade = md03.pay_grade

            --                          OR pay_grade = '*'

            --                        )

            --                    AND ( pay_class = md03.pay_class

            --                          OR pay_class = '*'

            --                        )

            --                    AND ( pay_area = md03.pay_area

            --                          OR pay_area = '*'

            --                        )

            --          )

            --     ELSE ( SELECT  amount

            --            FROM    hr_cu0303

            --            WHERE   start_date <= @Date

            --                    AND end_date >= @Date

            --                    AND wage_type = tj.wage_type	

            --          )

            --END amount_jabatan

FROM    dbo.PHRPA0001 md01 --@tbl_personal AS md01

        LEFT JOIN PHRPAGENDER AS gender ON md01.Gender = gender.Gender

        LEFT JOIN dbo.PHRPA0039 pa39 ON md01.EmployeeID = pa39.EmployeeID

                                        AND pa39.DateType = '0001'

        LEFT JOIN PHRPARELIGI AS religion ON md01.Religion = religion.Religion

		LEFT JOIN PHRPA0003 AS taxmd on  md01.EmployeeID=taxmd.EmployeeID and taxmd.EndDate='99991231'

		LEFT JOIN PHRPYTAXSTAT AS tax_status on taxmd.TaxStatus=tax_status.TaxStatus

        LEFT JOIN PHRPAMSTAT AS marital_status ON md01.MaritalStatus = marital_status.MaritalStatus

		and md01.EndDate='99991231'

        LEFT JOIN PHRPA0002 AS md02_hiring ON md01.EmployeeID = md02_hiring.EmployeeID -- Hiring Date

                                              AND md02_hiring.MovementType = @HiringDate

        --LEFT JOIN PHRPA0002 AS md02_permanent ON md01.EmployeeID = md02_permanent.EmployeeID -- Hiring Permanent

        --                                         --AND md02_permanent.MovementType = @PermanentDate

        --                                         --AND md02_permanent.employeetype = '01'

								--				   AND md02_permanent.StartDate <= @Date

                                             --AND md02_permanent.EndDate >= @Date

        LEFT JOIN @tbl_md2_last AS md02_last ON md01.EmployeeID = md02_last.emp_id

                                                AND md02_last.start_date <= @Date		-- Last Assignment dirubah

                                                AND md02_last.end_date >= @Date

                                                AND md02_last.payroll_group <> @LastAssign

		LEFT JOIN PHROM0001 AS pos ON  md02_last.position=pos.ObjectID

												AND pos.ObjectClass='P'

                                                    --AND md02_last.datedifff = ( SELECT

                                                              --MIN(tbl_last.datedifff)

                                                              --FROM

                                                              --@tbl_md2_last tbl_last

                                                              --WHERE

                                                              --tbl_last.start_date <= @Date  --dirubah

                                                              --AND tbl_last.end_date >= @Date

                                                              --AND tbl_last.payroll_group <> '99'

                                                              --AND md01.emp_id = tbl_last.emp_id

                                                              --)

        LEFT JOIN PCMEPMOVTYP AS mov_type_last ON md02_last.movement_type = mov_type_last.MovementType

        LEFT JOIN PCMEPEMPTYP AS emp_type_last ON md02_last.employee_type = emp_type_last.EmployeeType

        LEFT JOIN PCMEPEMPAREA AS emp_area_last ON md02_last.employee_area = emp_area_last.EmployeeArea

        LEFT JOIN PCMEPEMPOFF AS emp_office_last ON md02_last.employee_office = emp_office_last.EmployeeOffice

        LEFT JOIN PHRPYPGRUP AS payroll_group_last ON md02_last.payroll_group = payroll_group_last.PayrollGroup

        LEFT JOIN PCMEPEMPSTAT AS emp_status_last ON md02_last.employee_status = emp_status_last.EmployeeStatus

        LEFT JOIN PCMEPEMPSTYP AS emp_sub_type_desc ON md02_last.employee_subtype = emp_sub_type_desc.EmployeeSubType

		LEFT JOIN PCMEPMOVR AS emp_mov_reason ON md02_last.reason = emp_mov_reason.MovementReason AND emp_mov_reason.MovementType='17'

		LEFT JOIN PCMEMPDVSCODE AS  emp_division ON md02_last.divisioncode = emp_division.DivisionCode

		LEFT JOIN PHRPA0003 AS  emp_class ON md02_last.emp_id = emp_class.EmployeeID and emp_class.EndDate='99991231'

		left join PHRPYPCLASS AS pay_class on emp_class.PayrollClass=pay_class.PayrollClass and pay_class.EndDate='99991231' and pay_class.PayrollArea=taxmd.PayrollArea

		--Update 23/11/2022 by Shofwan

		LEFT JOIN PHRPAETHNIC AS  emp_ethnic ON md01.Ethnic = emp_ethnic.Ethnic

		LEFT JOIN PHRPA0022 AS md13 ON md13.EmployeeID= md01.EmployeeID AND md13.BenefitType='B001' and md13.EndDate='99991231'

		LEFT JOIN PHRPA0022 AS md13a ON md13a.EmployeeID= md01.EmployeeID AND md13a.BenefitType='B002' and md13a.EndDate='99991231'

            --LEFT OUTER JOIN hr_md_orm_object cc ON cc.landscape = @landscape

            --                                       AND cc.start_date <= @Date

            --                                       AND cc.end_date >= @Date

            --                                       AND md02_last.costcenter = cc.object

            --                                       AND cc.class = 'CC'

            --LEFT OUTER JOIN hr_md_orm_object o ON o.landscape = @landscape

            --                                      AND o.start_date <= @Date

            --                                      AND o.end_date >= @Date

            --                                      AND md02_last.organization = o.object

            --                                      AND o.class = 'O'

            --LEFT OUTER JOIN hr_md_orm_object p ON p.landscape = @landscape

            --                                      AND p.start_date <= @Date

            --                                      AND p.end_date >= @Date

            --                                      AND md02_last.position = p.object

            --                         --AND md02_last.payroll_group <> '99'

            --                                      AND p.class = 'P'

            --LEFT OUTER JOIN hr_md_orm_object j ON j.landscape = @landscape

            --                                      AND j.start_date <= @Date

            --                                      AND j.end_date >= @Date

            --                                      AND md02_last.job = j.object

            --                                      AND j.class = 'J'

        --LEFT JOIN phrpa0039 AS md39_group ON md01.EmployeeID = md39_group.EmployeeID				-- Date Spesification, Join Date Group

        --                                     AND md39_group.DateType = '10'

        --                                     AND md39_group.StartDate <= @Date

        --                                     AND md39_group.EndDate >= @Date

        LEFT JOIN PHRPA0002 AS md02_resign ON md01.EmployeeID = md02_resign.EmployeeID			-- Resign

                                              AND md02_resign.MovementType = @Resign

		LEFT JOIN PHRPA0002 AS md02_enddate ON md01.EmployeeID=md02_enddate.EmployeeID

												AND md02_enddate.EndDate='99991231'

                                                    --AND md02_resign.end_date = '99991231'

        --LEFT JOIN phrpa0002 AS md02_contract ON md01.EmployeeID = md02_contract.EmployeeID

        --                                        AND md02_contract.MovementType = '19'

		LEFT JOIN PHRPA0039 AS md39_permanent ON md01.EmployeeID = md39_permanent.EmployeeID

			and md39_permanent.DateType='0004'

        LEFT JOIN PHRPA0039 AS md39_contract ON md01.EmployeeID = md39_contract.EmployeeID		-- Date Spesification, expired contract dengan code 02

                                                AND md39_contract.DateType = @Expired

                                                AND md39_contract.Sequence = ( SELECT

                                                              MAX(md39_contracte.Sequence)

                                                              FROM

                                                              PHRPA0039 md39_contracte

                                                              WHERE

                                                              md39_contracte.EmployeeID = md39_contract.EmployeeID

                                                              AND md39_contracte.DateType = @Expired

                                                              )

        LEFT JOIN PHRPA0039 AS md39_joingroup ON md01.EmployeeID = md39_joingroup.EmployeeID		-- Date Spesification, expired contract dengan code 02

                                                 AND md39_joingroup.DateType = @joingroup

                                                 AND md39_joingroup.Sequence = ( SELECT

                                                              MAX(md39_joingroupe.Sequence)

                                                              FROM

                                                              PHRPA0039 md39_joingroupe

                                                              WHERE

                                                              md39_joingroupe.EmployeeID = md39_joingroup.EmployeeID

                                                              AND md39_joingroupe.DateType = @joingroup

                                                              )                                                     

        --LEFT JOIN hr_md_emp_md0002 AS md02_appointment ON md01.emp_id = md02_appointment.emp_id		-- Appointment

        --                                                  AND md02_appointment.movement_type = '20'

        LEFT JOIN PHRPA0025 AS md25 ON md01.EmployeeID = md25.EmployeeID							-- Working Schedule

                                       AND md25.StartDate <= @Date

                                       AND md25.EndDate >= @Date

        LEFT JOIN PHRTMWSTYP AS ws_type ON md25.WorkScheduleType = ws_type.WorkScheduleType

        LEFT JOIN PHRPA0003 AS md03 ON md01.EmployeeID = md03.EmployeeID						--Payroll Basic

                                       AND md03.StartDate <= @Date

                                       AND md03.EndDate >= @Date

            --LEFT JOIN PHRPYPENSTYP AS pension_type ON md03.PensionType = pension_type.PensionType

        LEFT JOIN PHRPYBANKTYP AS cust_bank_id ON md03.BankType = cust_bank_id.BankType

        LEFT JOIN PHRPYPGRAD AS pay_grade ON md03.PayrollGrade = pay_grade.PayrollGrade and md03.PayrollClass=pay_grade.PayrollClass

        LEFT JOIN PHRPA0006 AS md06_resident ON md01.EmployeeID = md06_resident.EmployeeID

                                                AND md06_resident.AddressType = @Resident

                                                       --AND md06_resident.end_date = '99991231'

                                                AND md06_resident.StartDate <= @Date

                                                AND md06_resident.EndDate >= @Date

                                                AND md06_resident.Sequence = ( SELECT

                                                              MAX(md06_st.Sequence)

                                                              FROM

                                                              PHRPA0006 md06_st

                                                              WHERE

                                                              md06_st.AddressType = @Resident

                                                              AND md06_st.EmployeeID = md06_resident.EmployeeID

                                                              )

        LEFT JOIN PHRPA0012 AS md12_hp ON md01.EmployeeID = md12_hp.EmployeeID

                                          AND md12_hp.CommunicationType = @HP

                                          AND md12_hp.Sequence = ( SELECT

                                                              MAX(md12_st.Sequence)

                                                              FROM

                                                              PHRPA0012 md12_st

                                                              WHERE

                                                              md12_st.EmployeeID = md12_hp.EmployeeID

                                                              AND md12_st.CommunicationType = @HP

                                                              )

        LEFT JOIN PHRPA0012 AS md12_email ON md01.EmployeeID = md12_email.EmployeeID

                                             AND md12_email.CommunicationType = @Email

                                             AND md12_email.Sequence = ( SELECT

                                                              MAX(md12e_st.Sequence)

                                                              FROM

                                                              PHRPA0012 md12e_st

                                                              WHERE

                                                              md12e_st.EmployeeID = md12_email.EmployeeID

                                                              AND md12e_st.CommunicationType = @Email

                                                              )

        LEFT JOIN PHRPA0011 AS md11 ON md01.EmployeeID = md11.EmployeeID

        LEFT JOIN PHRPABLODTYP AS bld ON md11.BloodType = bld.BloodType

        LEFT JOIN PHRPA0013 AS md13_ktp ON md01.EmployeeID = md13_ktp.EmployeeID

                                           AND md13_ktp.IDType = @IDKTP

                                           AND md13_ktp.Sequence = ( SELECT

                                                              MAX(md13_ktpe.Sequence)

                                                              FROM

                                                              PHRPA0013 md13_ktpe

                                                              WHERE

                                                              md13_ktpe.EmployeeID = md13_ktp.EmployeeID

                                                              AND md13_ktpe.IDType = @IDKTP

                                                              )

        LEFT JOIN PHRPA0013 AS md13_a ON md01.EmployeeID = md13_a.EmployeeID

                                         AND md13_a.IDType = @IDSIMA

        LEFT JOIN PHRPA0013 AS md13_b ON md01.EmployeeID = md13_b.EmployeeID

                                         AND md13_b.IDType = @IDSIMB

        LEFT JOIN PHRPA0013 AS md13_c ON md01.EmployeeID = md13_c.EmployeeID

                                         AND md13_c.IDType = @IDSIMC

        LEFT JOIN PHRPA0013 AS md13_passport ON md01.EmployeeID = md13_passport.EmployeeID

                                                AND md13_passport.IDType = @IDPassport

        LEFT JOIN PHRPA0006 AS md06_ktp ON md01.EmployeeID = md06_ktp.EmployeeID

                                           AND md06_ktp.AddressType = @ResidentKTP

                                           AND md06_ktp.StartDate <= @Date

                                           AND md06_ktp.EndDate >= @Date

                                                  --AND md06_ktp.end_date = '99991231'

                                           AND md06_ktp.Sequence = ( SELECT

                                                              MAX(md6_st.Sequence)

                                                              FROM

                                                              PHRPA0006 md6_st

                                                              WHERE

                                                              md6_st.AddressType = @ResidentKTP

                                                              AND md6_st.EmployeeID = md06_ktp.EmployeeID

                                                              )

        LEFT JOIN PHRPA0006 AS md06_cp ON md01.EmployeeID = md06_cp.EmployeeID

                                          AND md06_cp.AddressType = @ResidentKost

                                          AND md06_cp.StartDate <= @Date

                                          AND md06_cp.EndDate >= @Date

                                          AND md06_cp.Sequence = ( SELECT

                                                              MAX(md6_st.Sequence)

                                                              FROM

                                                              PHRPA0006 md6_st

                                                              WHERE

                                                              md6_st.AddressType = @ResidentKost

                                                              AND md6_st.EmployeeID = md06_cp.EmployeeID

                                                              )

        INNER JOIN PHRPA0015 AS md15 ON md01.EmployeeID = md15.EmployeeID

        LEFT JOIN @tbl_1 AS tbl_1 ON md01.EmployeeID = tbl_1.emp_id

        INNER JOIN @TableResult AS tr ON tr.EmployeeID = md01.EmployeeID

        LEFT JOIN @tbl_2 AS tbl_2 ON md01.EmployeeID = tbl_2.emp_id

        LEFT JOIN @tbl_3 AS tbl_3 ON md01.EmployeeID = tbl_3.emp_id

            --LEFT JOIN dbo.phrpa0004 gapok ON md02_last.emp_id = gapok.emp_id -- GAPOK

            --                                        AND gapok.start_date <= @Date

            --                                        AND gapok.end_date >= @Date

            --                                        AND gapok.wage_type = '1000'

            --LEFT JOIN dbo.phrpa0004 makan ON md02_last.emp_id = makan.emp_id -- MAKAN

            --                                        AND makan.start_date <= @Date

            --                                        AND makan.end_date >= @Date

            --                                        AND ( makan.wage_type IN (

            --                                              '1200', '1201' )

            --                                              OR makan.wage_type IN (

            --                                              SELECT

            --                                                  val1

            --                                              FROM

            --                                                  base_cust_parameter

            --                                              WHERE

            --                                                  param = 'IMFI_WAGE_TYPE_MK'

            --                                                  AND start_date <= @Date

            --                                                  AND end_date >= @Date )

            --                                            )

            --LEFT JOIN dbo.phrpa0004 ttp ON md02_last.emp_id = ttp.emp_id -- TRANSPORT

            --                                      AND ttp.start_date <= @Date

            --                                      AND ttp.end_date >= @Date

            --                                      AND ( ttp.wage_type IN (

            --                                            '1203', '1210', '1212' )

            --                                            OR ttp.wage_type IN (

            --                                            SELECT

            --                                                  val1

            --                                            FROM  base_cust_parameter

            --                                            WHERE param = 'IMFI_WAGE_TYPE_TP'

            --                                                  AND start_date <= @Date

            --                                                  AND end_date >= @Date )

            --                                          )

            --LEFT JOIN dbo.phrpa0004 hp ON md02_last.emp_id = hp.emp_id -- HP

            --                                     AND hp.start_date <= @Date

            --                                     AND hp.end_date >= @Date

            --                                     AND ( hp.wage_type = '1205'

            --                                           OR ( hp.wage_type >= '1231'

            --                                                AND hp.wage_type <= '1235'

            --                                              )

            --                                           OR hp.wage_type IN (

            --                                           SELECT val1

            --                                           FROM   base_cust_parameter

            --                                           WHERE  param = 'IMFI_WAGE_TYPE_HP'

            --                                                  AND start_date <= @Date

            --                                                  AND end_date >= @Date )

            --                                         )

            --LEFT JOIN dbo.phrpa0004 tj ON md02_last.EmployeeID = tj.EmployeeID -- JABATAN

            --                                     AND tj.StartDate <= @Date

            --                                     AND tj.EndDate >= @Date

            --                                     AND ( tj.WageType = '1202'

            --                                           OR ( tj.WageType >= '1221'

            --                                                AND tj.WageType <= '1229'

            --                                              )

            --                                           OR tj.WageType IN (

            --                                           SELECT val1

            --                                           FROM   base_cust_parameter

            --                                           WHERE  param = 'IMFI_WAGE_TYPE_TJ'

            --                                                  AND start_date <= @Date

            --                                                  AND end_date >= @Date )

            --                                         )

WHERE   ( md02_last.company_id = @CompanyID

          OR @CompanyID = ''

        )

        AND md01.StartDate <= @Date

        AND md01.EndDate >= @Date

        AND ( md02_last.payroll_group = @PayrollGroup

              OR @PayrollGroup = ''

            )

        AND ( md02_last.employee_status = @EmployeeStatus

              OR @EmployeeStatus = ''

            )

        AND ( md02_last.employee_area = @EmployeeArea

              OR @EmployeeArea = ''

            )

        AND ( md02_last.employee_office = @EmployeeOffice

              OR @EmployeeOffice = ''

            )

        AND ( md02_last.employee_type = @EmployeeType

              OR @EmployeeType = ''

            )

        AND ( md02_last.company_id = @CompanyID

              OR @CompanyID = ''

            )

		--AND md02_last.emp_id = '20050767'

		--AND md01.EmployeeID ='11010018'

ORDER BY md02_last.employee_office ,

        md01.EmployeeID