DECLARE
	@p_submitted BIT = 0, 
	@p_id_user VARCHAR(4) = '2EC',
	@p_submit_date_1 DATETIME = '1900-01-01',
	@p_submit_date_2 DATETIME = '2015-05-07',
	@p_row_start BIGINT = 1,
	@p_row_end BIGINT = 1000




SELECT
  TOP 99.99999 PERCENT
  *
FROM (
      SELECT
        ROW_NUMBER() OVER (ORDER BY r.submitted_date, r.create_date) AS RowNum, 
        r.id_register,
        r.[guid],
        r.id_transaction_type,
        t.[description] AS transaction_type,
        r.no_spaj,
        r.no_polis,
        r.policy_holder_name,
        r.ref_no,
        dokfile.jumlah,
        r.[status],
        r.id_status,
        r.id_unit_pelayanan_create,
        up.deskripsi AS unit_pelayanan_create,
        r.create_by,
        u1.Nama AS create_name,
        r.create_date,
        r.change_by,
        u2.Nama AS change_name,
        r.change_date,
        r.submitted,
        r.submitted_by,
        u3.Nama AS submitted_name,
        r.submitted_date
      FROM
        dbo.register AS r INNER JOIN
        dbo.transaction_type AS t ON r.id_transaction_type = t.id_transaction_type LEFT JOIN 
        IndInsurance.dbo.gUser_Name u1 ON u1.Id_User=r.create_by LEFT JOIN 
        IndInsurance.dbo.gUser_Name u2 ON u2.Id_User=r.change_by LEFT JOIN 
        IndInsurance.dbo.gUser_Name u3 ON u3.Id_User=r.submitted_by LEFT JOIN
        (SELECT [guid], COUNT(*) AS jumlah FROM dbo.document_files GROUP BY guid) AS dokfile ON r.guid = dokfile.guid LEFT JOIN
        dbo.unit_pelayanan AS up ON r.id_unit_pelayanan_create=up.id_unit_pelayanan
      WHERE 
        r.status='I' 
        AND r.submitted=@p_submitted
        
        AND (r.id_unit_pelayanan_create IN (
                                            SELECT id_unit_pelayanan 
                                            FROM dbo.func_get_user_hirarki_unit_pelayanan(@p_id_user)
                                           ) 
                                           --OR @p_submitted=1
                                           OR '0000' IN (
                                            SELECT id_unit_pelayanan 
                                            FROM dbo.func_get_user_hirarki_unit_pelayanan(@p_id_user)
                                           )
            )
        
        AND ((IndInsurance.dbo.func_fixed_date(r.submitted_date)>=@p_submit_date_1) OR (@p_submitted=0))
        AND ((IndInsurance.dbo.func_fixed_date(r.submitted_date)<=@p_submit_date_2) OR (@p_submitted=0))
      ) AS list
WHERE
  RowNum >= @p_row_start
  AND RowNum < @p_row_end
ORDER BY RowNum
