|
1
|
DECLARE @now VARCHAR(10)
|
|
2
|
SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
|
|
3
|
|
|
4
|
DECLARE @md02 AS TABLE
|
|
5
|
(
|
|
6
|
md02startDate VARCHAR(MAX),
|
|
7
|
md02endDate VARCHAR(MAX),
|
|
8
|
md02emp_id VARCHAR(MAX),
|
|
9
|
md02organization VARCHAR(MAX),
|
|
10
|
md02position VARCHAR(MAX)
|
|
11
|
)
|
|
12
|
INSERT INTO @md02
|
|
13
|
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'
|
|
14
|
|
|
15
|
DECLARE @relationshipORG AS TABLE
|
|
16
|
(
|
|
17
|
startDate VARCHAR(MAX),
|
|
18
|
endDate VARCHAR(MAX),
|
|
19
|
organisasi VARCHAR(MAX),
|
|
20
|
class VARCHAR(MAX),
|
|
21
|
rel_class VARCHAR(MAX),
|
|
22
|
posisi VARCHAR(MAX)
|
|
23
|
)
|
|
24
|
INSERT INTO @relationshipORG
|
|
25
|
select distinct start_date, end_date,object,class,rel_class,rel_object from hr_md_orm_relationship ship
|
|
26
|
inner join @md02 m02 ON ship.object = m02.md02organization and ship.rel_object = m02.md02position
|
|
27
|
where class = 'O' and rel_class = 'P' and start_date <= @now and end_date >= @now and rel_type = '001'
|
|
28
|
|
|
29
|
|
|
30
|
|
|
31
|
|
|
32
|
DECLARE @omEmployee AS TABLE
|
|
33
|
(
|
|
34
|
relationshipstartDate VARCHAR(MAX),
|
|
35
|
relationshipendDate VARCHAR(MAX),
|
|
36
|
relationshipemp_id VARCHAR(MAX),
|
|
37
|
posisi VARCHAR(MAX)
|
|
38
|
)
|
|
39
|
INSERT INTO @omEmployee
|
|
40
|
select hip.start_date,hip.end_date,hip.object,hip.rel_object from hr_md_orm_relationship hip
|
|
41
|
inner join @relationshipORG ORG on hip.rel_object = ORG.posisi
|
|
42
|
where hip.class = 'E' and hip.rel_class = 'P' and start_date <= @now and end_date >= @now
|
|
43
|
|
|
44
|
|
|
45
|
SELECT A.*, B.*
|
|
46
|
FROM @md02 A
|
|
47
|
FULL JOIN @omEmployee B ON (A.md02emp_id = B.relationshipemp_id)
|
|
48
|
where B.relationshipemp_id is null
|
