ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PA_ALL_EMPLOYEE_Service]
    (
      @landscape VARCHAR(3) ,
      @companycode VARCHAR(5) ,
      @emp_id VARCHAR(100) 
    )
AS 

--DECLARE @landscape VARCHAR(3) = '100'
--DECLARE @companycode VARCHAR(5) = '1000'
--DECLARE @emp_id VARCHAR(100) = '20230870'


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

select * into #md1 from hr_md_emp_md0001 with (nolock) where emp_id=@emp_id
select * into #md2 from hr_md_emp_md0002 md2 with (nolock)  where emp_id=@emp_id AND  md2.start_date <= @now AND md2.end_date >= @now   



       select emp_id ,
                edu_level ,
                major ,
                institution ,
                gpa ,
                edu_level.description ,
				edu_level.description AS edu_level_desc ,
                start_date
				INTO #tbl_3
        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'
                             )
		ORDER BY edu.seq DESC
    
    
        SELECT DISTINCT
                a.org_id_01 AS level1,
                a.org_name_01 AS level1_desc,
                a.org_id_02 AS level2,
                a.org_name_02 AS level2_desc,
                a.org_id_03 AS level3,
                a.org_name_03 AS level3_desc,
                a.org_id_04 AS level4,
                a.org_name_04 AS level4_desc ,
                a.org_id_05 AS level5,
                a.org_name_05 AS level5_desc ,
                a.org_id_06 AS level6,
                a.org_name_06 AS level6_desc,
                a.org_id_07 AS level7,
                a.org_name_07 AS level7_desc,
                a.org_id_08 AS level8,
                a.org_name_08 AS level8_desc,
                md2.emp_id AS emp_id
				INTO #tbl_1
        FROM    dbo.GetOrgInOrgWide('00000001', @now, @landscape) AS a
                INNER JOIN #md2 AS md2 ON a.org_id = md2.organization                                                       	 
	
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    #md2
        WHERE   movement_type = '60'
        GROUP BY emp_id , movement_type



        SELECT  tbl2.* ,
               '' as datedifff
			   INTO #tbl_md2_last
        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,
							CASE WHEN ISNULL(additional_1, '') = ''
                                 THEN start_date
                                 ELSE additional_1
                            END AS additional_1
                  FROM      #md2
                ) AS tbl2


				INSERT INTO hr_md_emp_resign
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_last.start_date)
             ELSE 
             CONVERT(DATE, md39.date)
             
             --( SELECT  CONVERT(DATE, date)
             --       FROM    hr_md_emp_md0039
             --       WHERE   date_type = '04'
             --               AND emp_id = md02_last.emp_id
             --               AND seq = ()
             -- )
        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 ,
		CONVERT(DATE, md39_contract.date) as 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.level4 ,
        tbl_1.level4_desc ,
        tbl_1.level5 ,
        tbl_1.level5_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 ,
        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 
FROM    #md1 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 #md2 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 hr_md_emp_md0039 AS md39 ON md02_last.emp_id=md39.emp_id AND md39.date_type='04'
        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 #md2 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 #md2 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.date = ( SELECT
                                                              MAX(md39_contracte.date)
                                                              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_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.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
        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
       
WHERE   md02_last.company_id = @companycode
		AND md01.start_date <= @now
		AND md01.end_date >= @now
		--AND md02_last.movement_type='80'
ORDER BY md02_last.employee_office ,
        md01.emp_id

		DROP TABLE #md1
		DROP TABLE #md2
		DROP TABLE #tbl_3
		DROP TABLE #tbl_1
		DROP TABLE #tbl_md2_last