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 ,
        CASE WHEN  md02_last.employee_type = '01' THEN CONVERT(DATE, md02_last.start_date) ELSE '' END 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
