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

DECLARE @md02 AS TABLE
    (
	 md02startDate VARCHAR(MAX),
	  md02endDate VARCHAR(MAX),
      md02emp_id VARCHAR(MAX),
	  md02organization VARCHAR(MAX),
	   md02position VARCHAR(MAX)
    )
INSERT  INTO @md02
       select start_date,end_date,emp_id,organization,position from hr_md_emp_md0002 where start_date <= @now and end_date >= @now and employee_status = '01'

DECLARE @relationshipORG AS TABLE
    (
      startDate VARCHAR(MAX),
	  endDate VARCHAR(MAX),
	   organisasi VARCHAR(MAX),
	    class VARCHAR(MAX),
		 rel_class VARCHAR(MAX),
		 posisi VARCHAR(MAX)
    )
INSERT  INTO @relationshipORG
       select distinct start_date, end_date,object,class,rel_class,rel_object from hr_md_orm_relationship ship
	   inner join @md02 m02 ON ship.object = m02.md02organization and ship.rel_object = m02.md02position
	   where class = 'O' and rel_class = 'P' and start_date <= @now and end_date >= @now  and rel_type = '001'




DECLARE @omEmployee AS TABLE
    (
     relationshipstartDate VARCHAR(MAX),
	  relationshipendDate VARCHAR(MAX),
	   relationshipemp_id VARCHAR(MAX),
		 posisi VARCHAR(MAX)
    )
INSERT  INTO @omEmployee      
select hip.start_date,hip.end_date,hip.object,hip.rel_object from hr_md_orm_relationship hip
inner join @relationshipORG ORG on hip.rel_object = ORG.posisi
where hip.class = 'E' and hip.rel_class = 'P' and start_date <= @now and end_date >= @now 


SELECT A.*, B.*
FROM @md02 A
    FULL JOIN @omEmployee B ON (A.md02emp_id = B.relationshipemp_id)
	where B.relationshipemp_id is null