DECLARE @landscape VARCHAR(10)='100'
DECLARE @companyid VARCHAR(20)='1000'
DECLARE @emp_subst VARCHAR(10)='200'
DECLARE @emp_suben VARCHAR(10)='200'
DECLARE @emp_statst VARCHAR(10)='01'
DECLARE @emp_staten VARCHAR(10)='01'
DECLARE @emp_areast VARCHAR(10)=''
DECLARE @emp_areaen VARCHAR(10)=''
DECLARE @pygrst VARCHAR(10)=''
DECLARE @pygren VARCHAR(10)=''
DECLARE @emp_idst VARCHAR(10)=''
DECLARE @emp_iden VARCHAR(10)=''
DECLARE @emp_typest VARCHAR(10)=''
DECLARE @emp_typeen VARCHAR(10)=''
DECLARE @organisasi VARCHAR(20)=''
DECLARE @movtype_st VARCHAR(10)=''
DECLARE @movtype_en VARCHAR(10)=''
DECLARE @reason_st VARCHAR(10)=''
DECLARE @reason_en VARCHAR(10)=''
DECLARE @subtype_st VARCHAR(10)=''
DECLARE @subtype_en VARCHAR(10)=''
DECLARE @startdate VARCHAR(8)=''
DECLARE @enddate VARCHAR(8)=''
DECLARE @datatype_st VARCHAR(5)=''
DECLARE @datatype_en VARCHAR(5)=''

--SET @landscape = '100'
--SET @companyid = '1000'
--SET @emp_subst = ''
--SET @emp_suben = ''
--SET @emp_statst = ''
--SET @emp_staten = ''
--SET @emp_areast = ''
--SET @emp_areaen = ''
--SET @pygrst = ''
--SET @pygren = ''
--SET @emp_idst = '20170077'--00000002'
--SET @emp_iden = '20170077'--00000006'
--SET @emp_typest = ''
--SET @emp_typeen = ''
--SET @organisasi = ''
--SET @movtype_st = ''
--SET @movtype_en = ''
--SET @reason_st = ''
--SET @reason_en = ''
--SET @subtype_st = ''
--SET @subtype_en = ''
--SET @startdate = ''--'20100803'
--SET @enddate = ''--'20101212'
--SET @datatype_en = ''
--SET @datatype_st = ''

DECLARE @now VARCHAR(10)

SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
SELECT DISTINCT
        m1.landscape ,
        DENSE_RANK() OVER ( ORDER BY m1.emp_id ) AS GroupSeq ,
        m1.emp_id ,
        m10.start_date ,
        m10.end_date ,
        ROW_NUMBER() OVER ( PARTITION BY m1.emp_id ORDER BY m1.emp_id ASC ) AS seq ,
        --m10.seq ,
        m10.data_type ,
        m10.description ,
        m10.responsible ,
        m10.amount ,
        d1.description AS datatype_desc ,
        m1.full_name ,
        o1.emp_subarea_description ,
        org.description AS organization ,
        pos.description AS position
FROM    hr_md_emp_md0001 m1
        INNER JOIN ( SELECT *
                     FROM   hr_md_emp_md0010
                     WHERE  ( landscape = @landscape )
                   ) AS m10 ON ( m1.landscape = m10.landscape )
                               AND ( m1.emp_id = m10.emp_id )
        LEFT OUTER JOIN ( SELECT    *
                          FROM      hr_md_emp_md0002
                          WHERE     ( landscape = @landscape )
                                    AND ( start_date <= @now )
                                    AND ( end_date >= @now )
                        ) AS m2 ON ( m1.landscape = m2.landscape )
                                   AND ( m1.emp_id = m2.emp_id )
        LEFT OUTER JOIN base_cust_ref_emp_office AS o1 ON ( m2.landscape = o1.landscape )
                                                          AND ( m2.employee_office = o1.emp_subarea )
        LEFT OUTER JOIN dbo.base_cust_ref_data_type AS d1 ON ( m2.landscape = d1.landscape )
                                                             AND ( m10.data_type = d1.code )
        LEFT OUTER JOIN hr_md_orm_object AS org ON ( org.landscape = m2.landscape )
                                                   AND ( org.object = m2.organization )
                                                   AND ( org.class = 'O' )
                                                   AND ( org.start_date <= @now )
                                                   AND ( org.end_date >= @now )
        LEFT OUTER JOIN hr_md_orm_object AS pos ON ( pos.landscape = m2.landscape )
                                                   AND ( pos.object = m2.position )
                                                   AND ( pos.class = 'P' )
                                                   AND ( pos.start_date <= @now )
                                                   AND ( pos.end_date >= @now )
WHERE   ( m1.landscape = @landscape )
        AND ( m1.start_date <= @now )
        AND ( m1.end_date >= @now )
        AND ( m2.emp_id >= @emp_idst
              OR @emp_idst = ''
            )
        AND ( m2.emp_id <= @emp_iden
              OR @emp_iden = ''
            )
        AND ( m2.company_id = @companyid
              OR @companyid = ''
            )
        AND m2.employee_status IN (
        CASE WHEN ISNULL(@emp_statst, '') = '' THEN m2.employee_status
             ELSE @emp_statst
        END, CASE WHEN ISNULL(@emp_staten, '') = '' THEN m2.employee_status
                  ELSE @emp_staten
             END )
        AND m2.employee_area IN (
        CASE WHEN ISNULL(@emp_areast, '') = '' THEN m2.employee_area
             ELSE @emp_areast
        END, CASE WHEN ISNULL(@emp_areaen, '') = '' THEN m2.employee_area
                  ELSE @emp_areaen
             END )
        AND m2.employee_office IN (
        CASE WHEN ISNULL(@emp_subst, '') = '' THEN m2.employee_office
             ELSE @emp_subst
        END, CASE WHEN ISNULL(@emp_suben, '') = '' THEN m2.employee_office
                  ELSE @emp_suben
             END )
        AND m2.employee_type IN (
        CASE WHEN ISNULL(@emp_typest, '') = '' THEN m2.employee_type
             ELSE @emp_typest
        END, CASE WHEN ISNULL(@emp_typeen, '') = '' THEN m2.employee_type
                  ELSE @emp_typeen
             END )
        AND m2.payroll_group IN (
        CASE WHEN ISNULL(@pygrst, '') = '' THEN m2.payroll_group
             ELSE @pygrst
        END, CASE WHEN ISNULL(@pygren, '') = '' THEN m2.payroll_group
                  ELSE @pygren
             END )
        AND m10.data_type IN (
        CASE WHEN ISNULL(@datatype_st, '') = '' THEN m10.data_type
             ELSE @datatype_st
        END, CASE WHEN ISNULL(@datatype_en, '') = '' THEN m10.data_type
                  ELSE @datatype_en
             END )
        AND ( m10.start_date >= @startdate
              OR @startdate = ''
            )
        AND ( m10.start_date <= @enddate
              OR @enddate = ''
            )