USE [MinovaHR_ESS_Indomobil_Production]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_IMFI_HR_PA_ALL_EMPLOYEE]    Script Date: 01/20/2021 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery3.sql|0|0|C:\Users\Administrator.IMFI\AppData\Local\Temp\2\~vsB54F.sql
--USE [MinovaHR_ESS_Indomobil_Production]
--GO
--/****** Object:  StoredProcedure [dbo].[Rpt_IMFI_HR_PA_ALL_EMPLOYEE]    Script Date: 28-11-2018 10:24:45 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PA_ALL_EMPLOYEE]
    (
      @landscape VARCHAR(3) ,
      @companycode VARCHAR(5) ,
      @emp_status VARCHAR(5) ,
      @emp_type VARCHAR(5) ,
      @emp_area VARCHAR(5) ,
      @emp_office VARCHAR(5) ,
      @pay_group VARCHAR(2)
    )
AS 

--DECLARE @landscape VARCHAR(3) = '100'
--DECLARE @companycode VARCHAR(5) = '1000'
--DECLARE @emp_status VARCHAR(5) = ''
--DECLARE @emp_type VARCHAR(5)= ''
--DECLARE @emp_area VARCHAR(5)= ''
--DECLARE @emp_office VARCHAR(5) = ''
--DECLARE @pay_group VARCHAR(2) = ''


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

DECLARE @tbl_3 TABLE
    (
      emp_id VARCHAR(8) ,
      edu_level VARCHAR(8) ,
      major VARCHAR(60) ,
      institution VARCHAR(200) ,
      gpa VARCHAR(8) ,
      edu_level_desc VARCHAR(200) ,
      edu_start VARCHAR(8)
    )

    --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 emp_id ,
      -- start_date, 
       --edu_type,
                edu_level ,
                major ,
                institution ,
                gpa ,
                edu_level.description ,
                start_date
        FROM    dbo.hr_md_emp_md0008 AS edu
                LEFT JOIN base_cust_ref_edu_level AS edu_level ON edu.edu_level = edu_level.code
        WHERE   end_date = ( SELECT   MAX(end_date)
                               FROM     dbo.hr_md_emp_md0008 AS x
                               WHERE    x.emp_id = edu.emp_id AND x.edu_type='F'
                             )
                            
							 
							  --AND edu.emp_id = '20110003'
		ORDER BY edu.seq DESC
    
    
DECLARE @tbl_1 TABLE
    (
      level1 VARCHAR(8) ,
      level1_desc VARCHAR(200) ,
      level2 VARCHAR(8) ,
      level2_desc VARCHAR(200) ,
      level3 VARCHAR(8) ,
      level3_desc VARCHAR(200) ,
      level4 VARCHAR(8) ,
      level4_desc VARCHAR(200) ,
      level5 VARCHAR(8) ,
      level5_desc VARCHAR(200) ,
      level6 VARCHAR(8) ,
      level6_desc VARCHAR(200) ,
      level7 VARCHAR(8) ,
      level7_desc VARCHAR(200) ,
      level8 VARCHAR(8) ,
      level8_desc VARCHAR(200) ,
      emp_id VARCHAR(8)
    )


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.emp_id
        FROM    dbo.GetOrgInOrgWide('00000001', @now, @landscape) AS a
                INNER JOIN dbo.hr_md_emp_md0002 AS md2 ON a.org_id = md2.organization
                                                          AND md2.start_date <= @now
                                                          AND md2.end_date >= @now                                                          	 
	
DECLARE @tbl_2 TABLE
    (
      emp_id VARCHAR(8) ,
      movement_type VARCHAR(2) ,
      jumlah DECIMAL(18, 0)
    )	

INSERT  INTO @tbl_2
        SELECT DISTINCT
                emp_id ,
                movement_type ,
                COUNT(start_date)
        FROM    dbo.hr_md_emp_md0002 WITH ( NOLOCK )
        WHERE   movement_type = '60'
        GROUP BY emp_id ,
                movement_type

DECLARE @tbl_md2_last TABLE
    (
      landscape VARCHAR(3) ,
      emp_id VARCHAR(8) ,
      start_date VARCHAR(8) ,
      end_date VARCHAR(8) ,
      movement_type VARCHAR(8) ,
      reason VARCHAR(2) ,
      company_id VARCHAR(4) ,
      costcenter VARCHAR(8) ,
      employee_area VARCHAR(20) ,
      employee_office VARCHAR(4) ,
      employee_status VARCHAR(2) ,
      employee_type VARCHAR(20) ,
      employee_subtype VARCHAR(20) ,
      tax_office VARCHAR(5) ,
      payroll_group VARCHAR(2) ,
      organization VARCHAR(8) ,
      position VARCHAR(8) ,
      job VARCHAR(8) ,
      remark VARCHAR(MAX) ,
      additional_1 VARCHAR(8) ,
      datedifff VARCHAR(MAX)
    )

INSERT  INTO @tbl_md2_last
        SELECT  tbl2.* ,
               '' as datedifff
        FROM    ( SELECT    landscape ,
                            emp_id ,
                            start_date ,
                            end_date ,
                            movement_type ,
                            reason ,
                            company_id ,
                            costcenter ,
                            employee_area ,
                            employee_office ,
                            employee_status ,
                            employee_type ,
                            employee_subtype ,
                            tax_office ,
                            payroll_group ,
                            organization ,
                            position ,
                            job ,
                            remark ,
                            CASE WHEN ISNULL(additional_1, '') = ''
                                 THEN start_date
                                 ELSE additional_1
                            END AS realDate
                  FROM      dbo.hr_md_emp_md0002 WITH ( NOLOCK )
                ) AS tbl2
                        
SELECT DISTINCT
        md01.emp_id ,
        md01.full_name ,
        md02_last.company_id ,
        CONVERT(DATE, md02_hiring.start_date) AS join_date ,
        CASE WHEN md02_last.movement_type = '20' THEN
        --CONVERT(DATE, md02_appointment.start_date) --AS permanent_date ,
                  CONVERT(DATE, md02_last.start_date)
             ELSE ( SELECT  CONVERT(DATE, date)
                    FROM    hr_md_emp_md0039
                    WHERE   date_type = '04'
                            AND emp_id = md02_last.emp_id
                  )
        END permanent_date ,
        CONVERT(DATE, md39_group.date) AS join_group ,
        mov_type_last.description 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.description AS emp_status ,
        CONVERT(DATE, md02_resign.start_date) AS resign_date ,
        emp_type_last.description AS employee_type ,
        CASE WHEN ( md39_contract.date >= @now )
             THEN CONVERT(DATE, md39_contract.date)
             ELSE NULL
        END expired_status ,
        tbl_2.jumlah AS contract_extent ,
        ws_type.description AS workschedule_type ,
        md03.tax_status ,
        md02_last.employee_area AS area_code ,
        emp_area_last.description AS area_desc ,
        md02_last.employee_office AS office_code ,
        emp_office_last.emp_subarea_description AS office_desc ,
        md02_last.payroll_group AS payroll_group_id ,
        payroll_group_last.description AS payroll_group ,
        emp_sub_type_desc.description 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 ,
        p.description AS pos_desc ,
        --md02_last.job AS job_code ,
        --j.description AS job_desc ,
        pay_grade.description AS pay_grade ,
        md01.nick_name ,
        gender.description AS gender ,
        md01.birth_place ,
        CONVERT (DATE, md01.birth_date) AS birth_date ,
        md06_resident.street ,
        ( SELECT    description
          FROM      base_cust_ref_province
          WHERE     code = md06_resident.province
        ) AS province ,
        md06_resident.location ,
        md06_resident.postalcode ,
        md06_resident.telp_num ,
        md12_hp.comm_description AS cellphone ,
        md12_email.comm_description AS email ,
        religion.description AS religion ,
        marital_status.description AS marital_status ,
        md11.blood_type ,
        md13_ktp.id_description AS id ,
        CONVERT(DATE, md13_ktp.end_date) AS ktp_expired ,
        md06_ktp.street AS ktp_address ,
        ( SELECT    description
          FROM      base_cust_ref_province
          WHERE     code = 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.id_description AS id_c_no ,
        CONVERT(DATE, md13_c.end_date) AS id_c_expired ,
        md13_passport.id_description AS id_password_no ,
        CONVERT(DATE, md13_passport.end_date) AS id_password_expired ,
        md06_cp.contact_person AS cp_name ,
        md06_cp.street AS cp_address ,
        md06_cp.telp_num AS cp_tlp ,
        md06_cp.cellphone_num 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.jamsostek_type ,
        jams_type.description AS jams_type ,
        md03.jamsostek ,
        CASE WHEN md03.additional_1 = '' THEN ''
             ELSE dbo.fn_formatdatetime(md03.additional_1, 'dd mmm yyyy')
        END AS additional_1 ,
        pension_type.description AS pension_type ,
        md03.pension_id ,
        CASE WHEN md03.additional_2 = '' THEN ''
             ELSE dbo.fn_formatdatetime(md03.additional_2, 'dd mmm yyyy')
        END AS additional_2 ,
        cust_bank_id.description AS bank_id ,
        md03.bank_account ,
        md03.bank_account_name ,
        tbl_3.edu_level_desc ,
        tbl_3.major ,
        tbl_3.institution ,
        tbl_3.gpa ,
        md02_last.remark 
        /* ---------------------------Tidak ditampilkan direport(#Data jadi double)--------------------------
        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 <= @now
                                            AND end_date >= @now
                                            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(@landscape,
                                                          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 <= @now
                                            AND end_date >= @now
                                            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 <= @now
                                            AND end_date >= @now
                                            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 <= @now
                            AND end_date >= @now
                            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 <= @now
                                            AND end_date >= @now
                                            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 <= @now
                            AND end_date >= @now
                            AND wage_type = tj.wage_type
                  )
        END amount_jabatan
        */ ------------------- Hamidi 20170209 ---------------------------------
FROM    dbo.hr_md_emp_md0001 md01 --@tbl_personal AS md01
        LEFT JOIN base_cust_ref_gender AS gender ON md01.gender = gender.code
        LEFT JOIN base_cust_ref_religion AS religion ON md01.religion = religion.code
        LEFT JOIN base_cust_ref_marital_status AS marital_status ON md01.marital_status = marital_status.code
        LEFT JOIN hr_md_emp_md0002 AS md02_hiring ON md01.emp_id = md02_hiring.emp_id			-- Hiring Date
                                                     AND md02_hiring.movement_type = '10'
        LEFT JOIN @tbl_md2_last AS md02_last ON md01.emp_id = md02_last.emp_id
                                                AND md02_last.start_date <= @now		-- Last Assignment dirubah
                                                AND md02_last.end_date >= @now
                                                AND md02_last.payroll_group <> '99'
                                                AND md02_last.datedifff = ( SELECT
                                                              MIN(tbl_last.datedifff)
                                                              FROM
                                                              @tbl_md2_last tbl_last
                                                              WHERE
                                                              tbl_last.start_date <= @now  --dirubah
                                                              AND tbl_last.end_date >= @now
                                                              AND tbl_last.payroll_group <> '99'
                                                              AND md01.emp_id = tbl_last.emp_id
                                                              )
        LEFT JOIN base_cust_ref_mov_type AS mov_type_last ON md02_last.movement_type = mov_type_last.code
        LEFT JOIN base_cust_ref_emp_type AS emp_type_last ON md02_last.employee_type = emp_type_last.emp_type
        LEFT JOIN base_cust_ref_emp_area AS emp_area_last ON md02_last.employee_area = emp_area_last.emp_area
        LEFT JOIN base_cust_ref_emp_office AS emp_office_last ON md02_last.employee_office = emp_office_last.emp_subarea
        LEFT JOIN base_cust_ref_payroll_group AS payroll_group_last ON md02_last.payroll_group = payroll_group_last.payroll_group
        LEFT JOIN base_cust_ref_emp_status AS emp_status_last ON md02_last.employee_status = emp_status_last.emp_status
        LEFT JOIN base_cust_ref_emp_subtype AS emp_sub_type_desc ON md02_last.employee_subtype = emp_sub_type_desc.emp_subtype
        LEFT OUTER JOIN hr_md_orm_object cc ON cc.landscape = @landscape
                                               AND cc.start_date <= @now
                                               AND cc.end_date >= @now
                                               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 <= @now
                                              AND o.end_date >= @now
                                              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 <= @now
                                              AND p.end_date >= @now
                                              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 <= @now
                                              AND j.end_date >= @now
                                              AND md02_last.job = j.object
                                              AND j.class = 'J'
        LEFT JOIN hr_md_emp_md0039 AS md39_group ON md01.emp_id = md39_group.emp_id				-- Date Spesification, Join Date Group
                                                    AND md39_group.date_type = '10'
                                                    AND md39_group.start_date <= @now
                                                    AND md39_group.end_date >= @now
        LEFT JOIN hr_md_emp_md0002 AS md02_resign ON md01.emp_id = md02_resign.emp_id			-- Resign
                                                     AND md02_resign.movement_type = '80'
                                                    --AND md02_resign.end_date = '99991231'
        LEFT JOIN hr_md_emp_md0002 AS md02_contract ON md01.emp_id = md02_contract.emp_id
                                                       AND md02_contract.movement_type = '60'
        LEFT JOIN hr_md_emp_md0039 AS md39_contract ON md01.emp_id = md39_contract.emp_id		-- Date Spesification, expired contract dengan code 02
                                                       AND md39_contract.date_type = '02'
                                                       AND md39_contract.seq = ( SELECT
                                                              MAX(md39_contracte.seq)
                                                              FROM
                                                              hr_md_emp_md0039 md39_contracte
                                                              WHERE
                                                              md39_contracte.emp_id = md39_contract.emp_id
                                                              AND md39_contracte.date_type = '02'
                                                              )
        --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 hr_md_emp_md0025 AS md25 ON md01.emp_id = md25.emp_id							-- Working Schedule
                                              AND md25.start_date <= @now
                                              AND md25.end_date >= @now
        LEFT JOIN hr_tm_workschedule_type AS ws_type ON md25.ws_type = ws_type.workschedule_type
        LEFT JOIN hr_md_emp_md0003 AS md03 ON md01.emp_id = md03.emp_id						--Payroll Basic
                                              AND md03.start_date <= @now
                                              AND md03.end_date >= @now
        LEFT JOIN base_cust_ref_jamsostek_type AS jams_type ON md03.jamsostek_type = jams_type.code
        LEFT JOIN base_cust_ref_pension_type AS pension_type ON md03.pension_type = pension_type.code
        LEFT JOIN base_cust_ref_bankid AS cust_bank_id ON md03.bank_id = cust_bank_id.code
        LEFT JOIN base_cust_ref_pay_grade AS pay_grade ON md03.pay_grade = pay_grade.pay_grade
        LEFT JOIN hr_md_emp_md0006 AS md06_resident ON md01.emp_id = md06_resident.emp_id
                                                       AND md06_resident.address_type = '02'
                                                       --AND md06_resident.end_date = '99991231'
                                                       AND md06_resident.start_date <= @now
                                                       AND md06_resident.end_date >= @now
                                                       AND md06_resident.seq = ( SELECT
                                                              MAX(md06_st.seq)
                                                              FROM
                                                              hr_md_emp_md0006 md06_st
                                                              WHERE
                                                              md06_st.address_type = '02'
                                                              AND md06_st.emp_id = md06_resident.emp_id
                                                              )
        LEFT JOIN hr_md_emp_md0012 AS md12_hp ON md01.emp_id = md12_hp.emp_id
                                                 AND md12_hp.comm_type = '02'
                                                 AND md12_hp.seq = ( SELECT
                                                              MAX(md12_st.seq)
                                                              FROM
                                                              hr_md_emp_md0012 md12_st
                                                              WHERE
                                                              md12_st.emp_id = md12_hp.emp_id
                                                              AND md12_st.comm_type = '02'
                                                              )
        LEFT JOIN hr_md_emp_md0012 AS md12_email ON md01.emp_id = md12_email.emp_id
                                                    AND md12_email.comm_type = '04'
                                                    AND md12_email.seq = ( SELECT
                                                              MAX(md12e_st.seq)
                                                              FROM
                                                              hr_md_emp_md0012 md12e_st
                                                              WHERE
                                                              md12e_st.emp_id = md12_email.emp_id
                                                              AND md12e_st.comm_type = '04'
                                                              )
        LEFT JOIN hr_md_emp_md0011 AS md11 ON md01.emp_id = md11.emp_id
        LEFT JOIN hr_md_emp_md0013 AS md13_ktp ON md01.emp_id = md13_ktp.emp_id
                                                  AND md13_ktp.id_type = '01'
                                                  AND md13_ktp.seq = ( SELECT
                                                              MAX(md13_ktpe.seq)
                                                              FROM
                                                              hr_md_emp_md0013 md13_ktpe
                                                              WHERE
                                                              md13_ktpe.emp_id = md13_ktp.emp_id
                                                              AND md13_ktpe.id_type = '01'
                                                              )
        LEFT JOIN hr_md_emp_md0013 AS md13_a ON md01.emp_id = md13_a.emp_id
                                                AND md13_a.id_type = '03'
        LEFT JOIN hr_md_emp_md0013 AS md13_b ON md01.emp_id = md13_b.emp_id
                                                AND md13_b.id_type = '04'
        LEFT JOIN hr_md_emp_md0013 AS md13_c ON md01.emp_id = md13_c.emp_id
                                                AND md13_c.id_type = '05'
        LEFT JOIN hr_md_emp_md0013 AS md13_passport ON md01.emp_id = md13_passport.emp_id
                                                       AND md13_passport.id_type = '02'
        LEFT JOIN hr_md_emp_md0006 AS md06_ktp ON md01.emp_id = md06_ktp.emp_id
                                                  AND md06_ktp.address_type = '01'
                                                  AND md06_ktp.start_date <= @now
                                                  AND md06_ktp.end_date >= @now
                                                  --AND md06_ktp.end_date = '99991231'
                                                  --AND md06_ktp.seq = ( SELECT
                                                  --            MAX(md6_st.seq)
                                                  --            FROM
                                                  --            hr_md_emp_md0006 md6_st
                                                  --            WHERE
                                                  --            md6_st.address_type = '01'
                                                  --            AND md6_st.emp_id = md06_ktp.emp_id
                                                  --            )
        LEFT JOIN hr_md_emp_md0006 AS md06_cp ON md01.emp_id = md06_cp.emp_id
                                                 AND md06_cp.address_type = '04'
                                                 AND md06_cp.start_date <= @now
                                                 AND md06_cp.end_date >= @now
                                                 AND md06_cp.seq = ( SELECT
                                                              MAX(md6_st.seq)
                                                              FROM
                                                              hr_md_emp_md0006 md6_st
                                                              WHERE
                                                              md6_st.address_type = '04'
                                                              AND md6_st.emp_id = md06_cp.emp_id
                                                              )
        INNER JOIN hr_md_emp_md0015 AS md15 ON md01.emp_id = md15.emp_id
        LEFT JOIN @tbl_1 AS tbl_1 ON md01.emp_id = tbl_1.emp_id
        LEFT JOIN @tbl_2 AS tbl_2 ON md01.emp_id = tbl_2.emp_id
        LEFT JOIN @tbl_3 AS tbl_3 ON md01.emp_id = tbl_3.emp_id
        /* ---------------------------Tidak ditampilkan direport(#Data jadi double)--------------------------
        LEFT JOIN dbo.hr_md_emp_md0004 gapok ON md02_last.emp_id = gapok.emp_id -- GAPOK
                                                AND gapok.start_date <= @now
                                                AND gapok.end_date >= @now
                                                AND gapok.wage_type = '1000'
        LEFT JOIN dbo.hr_md_emp_md0004 makan ON md02_last.emp_id = makan.emp_id -- MAKAN
                                                AND makan.start_date <= @now
                                                AND makan.end_date >= @now
                                                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 <= @now
                                                              AND end_date >= @now )
                                                    )
        LEFT JOIN dbo.hr_md_emp_md0004 ttp ON md02_last.emp_id = ttp.emp_id -- TRANSPORT
                                              AND ttp.start_date <= @now
                                              AND ttp.end_date >= @now
                                              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 <= @now
                                                            AND end_date >= @now )
                                                  )
        LEFT JOIN dbo.hr_md_emp_md0004 hp ON md02_last.emp_id = hp.emp_id -- HP
                                             AND hp.start_date <= @now
                                             AND hp.end_date >= @now
                                             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 <= @now
                                                            AND end_date >= @now )
                                                 )
        LEFT JOIN dbo.hr_md_emp_md0004 tj ON md02_last.emp_id = tj.emp_id -- JABATAN
                                             AND tj.start_date <= @now
                                             AND tj.end_date >= @now
                                             AND ( tj.wage_type = '1202'
                                                   OR ( tj.wage_type >= '1221'
                                                        AND tj.wage_type <= '1229'
                                                      )
                                                   OR tj.wage_type IN (
                                                   SELECT   val1
                                                   FROM     base_cust_parameter
                                                   WHERE    param = 'IMFI_WAGE_TYPE_TJ'
                                                            AND start_date <= @now
                                                            AND end_date >= @now )
                                                 )
        */ --------------------------- Hamidi 20170209 --------------------------
WHERE   md02_last.company_id = @companycode
		AND md01.start_date <= @now
		AND md01.end_date >= @now
        AND ( md02_last.payroll_group = @pay_group
              OR @pay_group = ''
            )
        AND ( md02_last.employee_status = @emp_status
              OR @emp_status = ''
            )
        AND ( md02_last.employee_area = @emp_area
              OR @emp_area = ''
            )
        AND ( md02_last.employee_office = @emp_office
              OR @emp_office = ''
            )
        AND ( md02_last.employee_type = @emp_type
              OR @emp_type = ''
            )
		--AND md02_last.emp_id = '20050767'
ORDER BY md02_last.employee_office ,
        md01.emp_id