CLIENT SUPPORT » IMFI Bugs

dear tim developer,
mohon support nya untuk perbaikan pada report dimana untuk amount tunjangan makan tidak tampil data amount nya.
untuk wage type nya 1213,
contoh employee id nya 20180075

database sql server
192.168.3.105\MSSQLSERVER2008

SP nya Rpt_IMFI_HR_PA_ALL_EMPLOYEE_NEW

ini isian SP nya =====================
--USE [MinovaHR_ESS_Indomobil_Production]
--GO
--/****** Object: StoredProcedure [dbo].[Rpt_IMFI_HR_PA_ALL_EMPLOYEE_NEW] Script Date: 02/22/2023 10:49:08 **/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--ALTER PROCEDURE [dbo].[Rpt_IMFI_HR_PA_ALL_EMPLOYEE_NEW]
-- (
-- @landscape VARCHAR ,
-- @companycode VARCHAR ,
-- @emp_status VARCHAR ,
-- @emp_type VARCHAR ,
-- @emp_area VARCHAR ,
-- @emp_office VARCHAR ,
-- @pay_group VARCHAR
-- )
--AS

DECLARE @landscape VARCHAR = '100'
DECLARE @companycode VARCHAR = '1000'
DECLARE @emp_status VARCHAR = '01'
DECLARE @emp_type VARCHAR= '01'
DECLARE @emp_area VARCHAR= '1110'
DECLARE @emp_office VARCHAR = '101'
DECLARE @pay_group VARCHAR = '13'

DECLARE @now VARCHAR = dbo.fn_formatdatetime(GETDATE, 'yyyymmdd')
--DECLARE @now VARCHAR = '20150901'
--SELECT * FROM dbo.GetOrgInOrgWide('00000001', @now, @landscape)

SELECT * INTO #md001 FROM  dbo.hr_md_emp_md0001 md01 WHERE  md01.start_date <= @now
                    AND md01.end_date >= @now

SELECT * INTO #md003 FROM  dbo.hr_md_emp_md0003 md03 WHERE  md03.start_date <= @now
                    AND md03.end_date >= @now

SELECT * INTO #md025 FROM  dbo.hr_md_emp_md0025 md25 WHERE  md25.start_date <= @now
                    AND md25.end_date >= @now

SELECT * INTO #hr_md_emp_md0002_md02_hiring FROM hr_md_emp_md0002 md02_hiring
                    WHERE md02_hiring.movement_type = '10'

SELECT * INTO #hr_md_emp_md0002_md02_resign FROM hr_md_emp_md0002 md02_resign
                    WHERE md02_resign.movement_type = '80'

SELECT * INTO #hr_md_emp_md0002_md02_contract FROM hr_md_emp_md0002 md02_contract
                    WHERE md02_contract.movement_type = '60'

SELECT * INTO #hr_md_emp_md0039_md39_group FROM hr_md_emp_md0039 md39_group
                    WHERE md39_group.date_type = '10'
                    AND md39_group.start_date <= @now
                    AND md39_group.end_date >= @now

SELECT * INTO #hr_md_emp_md0039_md39_contract FROM hr_md_emp_md0039 md39_contract
                    WHERE 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'
                            )

SELECT * INTO #hr_md_emp_md0012_md12_hp FROM hr_md_emp_md0012 md12_hp
                    WHERE 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'
                                )

SELECT * INTO #hr_md_emp_md0012_md12_email FROM hr_md_emp_md0012 md12_email
                    WHERE 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'
                                                              )

SELECT * INTO #hr_md_emp_md0006_md06_resident FROM hr_md_emp_md0006 md06_resident
                    WHERE 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
                            )

SELECT * INTO #hr_md_emp_md0006_md06_ktp FROM hr_md_emp_md0006 md06_ktp
                    WHERE 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
                                )

SELECT * INTO #hr_md_emp_md0006_md06_cp from hr_md_emp_md0006 md06_cp
                WHERE  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
                            )

SELECT * INTO #hr_md_emp_md0013tmp from hr_md_emp_md0013 md13_ktp

SELECT * INTO #hr_md_emp_md0013_md13_ktp from #hr_md_emp_md0013tmp md13_ktp
                WHERE md13_ktp.id_type = '01'
                AND md13_ktp.seq = ( SELECT
                            MAX(md13_ktpe.seq)
                            FROM
                            #hr_md_emp_md0013tmp md13_ktpe
                            WHERE
                            md13_ktpe.emp_id = md13_ktp.emp_id
                            AND md13_ktpe.id_type = '01'
                            )

SELECT * INTO #hr_md_emp_md0013_md13_a from hr_md_emp_md0013 md13_a
                WHERE md13_a.id_type = '03'

SELECT * INTO #hr_md_emp_md0013_md13_b from hr_md_emp_md0013 md13_b
                WHERE md13_b.id_type = '04'

SELECT * INTO #hr_md_emp_md0013_md13_c from hr_md_emp_md0013 md13_c
                WHERE md13_c.id_type = '05'

SELECT * INTO #hr_md_emp_md0013_md13_passport FROM hr_md_emp_md0013 md13_passport
                WHERE md13_passport.id_type = '02'

select * INTO #hr_md_emp_md0004_gapok  FROM hr_md_emp_md0004 gapok 
                WHERE gapok.start_date <= @now
                AND gapok.end_date >= @now
                AND gapok.wage_type = '1000'

select * INTO #hr_md_emp_md0004_makan  FROM hr_md_emp_md0004 makan 
                WHERE 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 )
                    )

SELECT * INTO #hr_md_emp_md0004_ttp  FROM hr_md_emp_md0004 ttp 
            WHERE 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 )
                    )

SELECT * INTO #hr_md_emp_md0004_hp  FROM hr_md_emp_md0004 hp 
            WHERE 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 )
                    )

SELECT * INTO #hr_md_emp_md0004_tj  FROM hr_md_emp_md0004 tj 
            WHERE 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 )
                    )
                AND tj.wage_type <> '1202'

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

--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
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
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 ,
level1_desc VARCHAR ,
level2 VARCHAR ,
level2_desc VARCHAR ,
level3 VARCHAR ,
level3_desc VARCHAR ,
level4 VARCHAR ,
level4_desc VARCHAR ,
level5 VARCHAR ,
level5_desc VARCHAR ,
level6 VARCHAR ,
level6_desc VARCHAR ,
level7 VARCHAR ,
level7_desc VARCHAR ,
level8 VARCHAR ,
level8_desc VARCHAR ,
emp_id VARCHAR
)

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 ,
movement_type VARCHAR ,
jumlah DECIMAL
)

--SELECT * FROM dbo.hr_md_emp_md0002

INSERT INTO @tbl_2
SELECT DISTINCT
emp_id ,
movement_type ,
COUNT
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 ,
emp_id VARCHAR ,
start_date VARCHAR ,
end_date VARCHAR ,
movement_type VARCHAR ,
reason VARCHAR ,
company_id VARCHAR ,
costcenter VARCHAR ,
employee_area VARCHAR ,
employee_office VARCHAR ,
employee_status VARCHAR ,
employee_type VARCHAR ,
employee_subtype VARCHAR ,
tax_office VARCHAR ,
payroll_group VARCHAR ,
organization VARCHAR ,
position VARCHAR ,
job VARCHAR ,
remark VARCHAR ,
additional_1 VARCHAR ,
datedifff VARCHAR
)

INSERT INTO @tbl_md2_last
SELECT tbl2.* ,
'' AS datedifff
--DATEDIFF 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 = ''
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 AS join_date ,
CASE WHEN md02_last.movement_type = '20' THEN
--CONVERT --AS permanent_date ,
CONVERT
ELSE ( SELECT CONVERT
FROM hr_md_emp_md0039
WHERE date_type = '04'
AND emp_id = md02_last.emp_id
)
END permanent_date ,
CONVERT AS join_group ,
mov_type_last.description AS effective_type_status ,
CONVERT AS effective_date ,
CONVERT AS real_date ,
md02_last.employee_status ,
emp_status_last.description AS emp_status ,
CONVERT AS resign_date ,
emp_type_last.description AS employee_type ,
CASE WHEN ( md39_contract.date >= @now )
THEN CONVERT
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 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 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 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 AS id_c_expired ,
md13_passport.id_description AS id_password_no ,
CONVERT 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 ,
CASE WHEN ISNULL = '' THEN 0
ELSE CONVERT)
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 = '' 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 = '' 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

FROM dbo.#md001 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_md02_hiring 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
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_md39_group 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_md02_resign 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_md02_contract AS md02_contract ON md01.emp_id = md02_contract.emp_id
--AND md02_contract.movement_type = '60'
LEFT JOIN #hr_md_emp_md0039_md39_contract 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
-- 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 #md025 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 #md003 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_md06_resident 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
-- 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_md12_hp AS md12_hp ON md01.emp_id = md12_hp.emp_id
--AND md12_hp.comm_type = '02'
--AND md12_hp.seq = ( SELECT
-- MAX
-- 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_md12_email AS md12_email ON md01.emp_id = md12_email.emp_id
--AND md12_email.comm_type = '04'
--AND md12_email.seq = ( SELECT
-- MAX
-- 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_md13_ktp AS md13_ktp ON md01.emp_id = md13_ktp.emp_id
--AND md13_ktp.id_type = '01'
--AND md13_ktp.seq = ( SELECT
-- MAX
-- 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_md13_a AS md13_a ON md01.emp_id = md13_a.emp_id
--AND md13_a.id_type = '03'
LEFT JOIN #hr_md_emp_md0013_md13_b AS md13_b ON md01.emp_id = md13_b.emp_id
--AND md13_b.id_type = '04'
LEFT JOIN #hr_md_emp_md0013_md13_c AS md13_c ON md01.emp_id = md13_c.emp_id
--AND md13_c.id_type = '05'
LEFT JOIN #hr_md_emp_md0013_md13_passport AS md13_passport ON md01.emp_id = md13_passport.emp_id
--AND md13_passport.id_type = '02'
LEFT JOIN #hr_md_emp_md0006_md06_ktp 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
-- 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_md06_cp 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
-- 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
LEFT JOIN #hr_md_emp_md0004_gapok 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 #hr_md_emp_md0004_makan 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 #hr_md_emp_md0004_ttp 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 #hr_md_emp_md0004_hp 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 #hr_md_emp_md0004_tj 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 )
-- )
--AND tj.wage_type <> '1202'
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 = '20180075'
ORDER BY md02_last.employee_office ,
md01.emp_id

DROP TABLE #md001
DROP TABLE #md003
DROP TABLE #md025
DROP TABLE #hr_md_emp_md0002_md02_hiring
DROP TABLE #hr_md_emp_md0002_md02_resign
DROP TABLE #hr_md_emp_md0002_md02_contract
DROP TABLE #hr_md_emp_md0039_md39_group
DROP TABLE #hr_md_emp_md0039_md39_contract
DROP TABLE #hr_md_emp_md0012_md12_hp
DROP TABLE #hr_md_emp_md0012_md12_email
DROP TABLE #hr_md_emp_md0006_md06_resident
DROP TABLE #hr_md_emp_md0006_md06_ktp
DROP TABLE #hr_md_emp_md0006_md06_cp
DROP TABLE #hr_md_emp_md0013tmp
DROP TABLE #hr_md_emp_md0013_md13_ktp
DROP TABLE #hr_md_emp_md0013_md13_a
DROP TABLE #hr_md_emp_md0013_md13_b
DROP TABLE #hr_md_emp_md0013_md13_c
DROP TABLE #hr_md_emp_md0013_md13_passport
DROP TABLE #hr_md_emp_md0004_gapok
DROP TABLE #hr_md_emp_md0004_makan
DROP TABLE #hr_md_emp_md0004_ttp
DROP TABLE #hr_md_emp_md0004_hp
DROP TABLE #hr_md_emp_md0004_tj =========================