USE [MinovaHR_BANK_MEGA_Development]
GO
/****** Object:  StoredProcedure [dbo].[sp_hr_md_emp_mutasi]    Script Date: 06/02/2026 10.57.20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_hr_md_emp_mutasi]

@EffectiveDate VARCHAR (10),
@Position VARCHAR (MAX),
@YearsFrom INT,
@MonthFrom INT,
@YearsTo INT,
@MonthTo INT,
@user_id VARCHAR (MAX)

AS

--DECLARE @EffectiveDate VARCHAR (10) ='20260206'
--DECLARE @Position VARCHAR (MAX) =''
--DECLARE @YearsFrom INT =''
--DECLARE @MonthFrom INT=''
--DECLARE @YearsTo INT =''
--DECLARE @MonthTo INT=''
--DECLARE @user_id VARCHAR (MAX)='all'

DECLARE @now VARCHAR(12) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
DECLARE @EffectiveDate2 VARCHAR(12) = @EffectiveDate


-----------------------// User ID Authorization // -----------------------------
select * into #userid
from hr_cust_authorization id
where id.[user_id]=@user_id
--select * from #userid

DECLARE @CompanyID VARCHAR (10)=''
SET @CompanyID = (select company_id from  #userid)

DECLARE @EmployeeType VARCHAR (10)=''
SET @EmployeeType = (select employee_type from  #userid)

DECLARE @EmployeeSubType VARCHAR (10)=''
SET @EmployeeSubType = (select employee_subtype from  #userid)

DECLARE @Regional VARCHAR (10)=''
SET @Regional = (select employee_region from  #userid)

DECLARE @Employee_Area VARCHAR (10)=''
SET @Employee_Area = (select employee_area from  #userid)

DECLARE @EmployeeOffice VARCHAR (10)=''
SET @EmployeeOffice = (select employee_office from  #userid)

DECLARE @PayrollGroup VARCHAR (10)=''
SET @PayrollGroup = (select payroll_group from  #userid)

DECLARE @GradeMin VARCHAR (10)=''
SET @GradeMin = (select grade_min from  #userid)

DECLARE @GradeMax VARCHAR (10)=''
SET @GradeMax = (select grade_max from  #userid)

SELECT DISTINCT
                a.org_id_01 AS level1,
                a.org_name_01 AS level1_desc,
                md2.emp_id AS emp_id
				INTO #tbl_1
        FROM    dbo.GetOrgInOrgWide('00007696', @now, '100') 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  

SELECT DISTINCT
                a.org_id_01 AS level1,
                a.org_name_01 AS level1_desc,
                md2.emp_id AS emp_id
				INTO #tbl_2
        FROM    dbo.GetOrgInOrgWide('00005246', @now, '100') 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 
SELECT DISTINCT
                a.org_id_01 AS level1,
                a.org_name_01 AS level1_desc,
                md2.emp_id AS emp_id
				INTO #tbl_3
        FROM    dbo.GetOrgInOrgWide('00001174', @now, '100') 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 
SELECT DISTINCT
                a.org_id_01 AS level1,
                a.org_name_01 AS level1_desc,
                md2.emp_id AS emp_id
				INTO #tbl_4
        FROM    dbo.GetOrgInOrgWide('00006649', @now, '100') 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                                                   	 

-----------------------// Get Grade Maximum // -----------------------------
select distinct emp_id, max(grade) as grade into #grademax
from hr_md_emp_md0008 
group by emp_id
-----------------------// Position Character Replace // -----------------------------
set @Position=REPLACE( @Position,'&','and') 

--------------// Years od Service //-------------------------------
declare @table as table
(
number int,
_sequence int,
start_date varchar(max),
end_date varchar(max),
emp_id varchar(max),
position varchar(max),
organization varchar(max),
orggroup VARCHAR(max),
stdtorggroup VARCHAR(max)
)

insert into @table
select distinct
ROW_NUMBER()  OVER (ORDER BY (SELECT null)) as number,
ROW_NUMBER()  OVER (partition by md2.emp_id order by emp_id) as _sequence,
md2.start_date,md2.end_date,md2.emp_id,pos.description as position,md2.organization  
,oo.org_group ,md2.start_date AS stdtorggroup
FROM hr_md_emp_md0002 md2 
left join hr_md_orm_object pos on md2.position=pos.object  and pos.class='P'
LEFT JOIN hr_md_orm_o_o oo ON md2.organization = oo.object
where md2.position !=''  
order by number asc

select convert(varchar(max),'')as number,min(start_date)as start_date,max(end_date)as end_date,emp_id,position,convert(varchar(max),'')as organization  into #temp2  from @table group by  emp_id,position

--SELECT * from #temp2 where emp_id='12111686'

update #temp2 set
number=a.number,
organization =a.organization from (select number,organization,emp_id,end_date,position from @table )a where #temp2.emp_id+#temp2.end_date+#temp2.position = a.emp_id+a.end_date+a.position

--select * from #temp2 where emp_id='12111686'


DECLARE @CounterData INT =1 
WHILE ( @CounterData = ( select count(distinct emp_id) from @table ) )
BEGIN

    DECLARE @CounterDataeMP INT =(select CONVERT(INT, number) from @table WHERE _sequence=( select MAX(_sequence)AS_sequence from @table) ) 
    declare @LastPosition varchar(max) =(select position from @table WHERE _sequence=@CounterDataeMP)
    WHILE ( @CounterDataeMP > 0 )
        BEGIN
            declare @positions varchar(max) = ( select position from @table WHERE _sequence=@CounterDataeMP)
            if @positions !=@LastPosition
            begin
                    update #temp2 set
                    start_date = a.start_date
                    from( select * from @table WHERE _sequence=@CounterDataeMP+1    )a where #temp2.emp_id+#temp2.position = a.emp_id+a.position
            BREAK
            end
           SET @CounterDataeMP  = @CounterDataeMP - 1
        END
    SET @CounterData  = @CounterData  + 1
END

--select * from #temp2 where emp_id='12111686'

----//---- mutasi berdasarkan orggroup start
select convert(varchar(max),'')as number,min(start_date)as start_date,max(end_date)as end_date,emp_id,convert(varchar(max),'')as organization, orggroup into #temp3  from @table group by  emp_id,orggroup

--SELECT * from #temp3 where emp_id='12111686'

update #temp3 set
number=a.number,
organization =a.organization from (select number,organization,emp_id,end_date, orggroup from @table )a where #temp3.emp_id+#temp3.end_date+#temp3.orggroup = a.emp_id+a.end_date+a.orggroup

--select * from #temp3 where emp_id='12111686'

DECLARE @CounterData1 INT =1 
WHILE ( @CounterData1 = ( select count(distinct emp_id) from @table ) )
BEGIN

    DECLARE @CounterDataeMP1 INT =(select CONVERT(INT, number) from @table WHERE _sequence=( select MAX(_sequence)AS_sequence from @table) ) 
    declare @LastPosition1 varchar(max) =(select orggroup from @table WHERE _sequence=@CounterDataeMP1)
    WHILE ( @CounterDataeMP1 > 0 )
        BEGIN
            declare @positions1 varchar(max) = ( select orggroup from @table WHERE _sequence=@CounterDataeMP1)
            if @positions1 !=@LastPosition1
            begin
                    update #temp3 set
                    start_date = a.start_date
                    from( select * from @table WHERE _sequence=@CounterDataeMP+1    )a where #temp3.emp_id+#temp3.orggroup = a.emp_id+a.orggroup
            BREAK
            end
           SET @CounterDataeMP1  = @CounterDataeMP1 - 1
        END
    SET @CounterData1  = @CounterData1  + 1
END

--select * from #temp3 where emp_id='12111686'

----//----  mutasi berdasarkan orggroup end 

-------------------// Result //-------------------------------
select distinct md01.emp_id
,CAST(@EffectiveDate2 AS Date) AS DateParam
,md01.full_name
--,md02.employee_status
,CAST(md15.hiring_date AS DATE) AS EffectifeDate
,md02.position
,pos.description AS JobTitle
,org.description AS Organization
,oog.code AS OrgNum
--,oo.location as test
,oog.description AS OrgGroup
,md02.regional
,tbl_1.level1 AS Direktorat
,tbl_1.level1_desc AS DirektoratDesc
,regional.description AS Regional
,CAST(yos.start_date AS Date) AS MulaiPosisi
--,CONCAT(datediff(day, yos.start_date, @now)/365, ' Thn')AS Years				----// comment mutasi berdasarkan orggroup
--,CONCAT((datediff(day, yos.start_date, @now) % 365)/30, ' Bln') AS Months		----// comment mutasi berdasarkan orggroup
--,yos1.start_date AS stdtorg													----// test mutasi berdasarkan orggroup
,CONCAT(datediff(day, yos1.start_date, @now)/365, ' Thn')AS Years1
,CONCAT((datediff(day, yos1.start_date, @now) % 365)/30, ' Bln') AS Months1
from hr_md_emp_md0001 md01
--inner join hr_md_emp_md0039 md39 on md39.emp_id=md01.emp_id and md39.date_type='01'
inner join hr_md_emp_md0002 md02 on md01.emp_id=md02.emp_id and md02.payroll_group='01' and md02.start_date<=@EffectiveDate and md02.end_date>=@EffectiveDate
-- VALIDASI PAYROLL GROUP 02 -shofwan
left join hr_md_orm_object pos on md02.position=pos.object  and pos.class='P' and pos.end_date='99991231'
left join hr_md_orm_object org on md02.organization=org.object  and org.class='O' and org.end_date='99991231'
left join hr_md_orm_o_o oo on  org.object= oo.object AND oo.start_date <= @EffectiveDate  AND oo.end_date >= @EffectiveDate
left join base_cust_ref_emp_office as locs on oo.location=locs.emp_subarea
left join base_cust_ref_regional regional on md02.regional=regional.regional
left join #temp2 yos on yos.emp_id=md01.emp_id and yos.position=pos.description
left join #temp3 yos1 on yos1.emp_id=md01.emp_id and yos1.organization= md02.organization
left join base_cust_ref_org_level orglvl on orglvl.code=oo.org_level
left join hr_md_emp_md0002 directorat on directorat.emp_id=md01.emp_id and directorat.end_date='99991231'
left join hr_md_orm_relationship relation on directorat.organization=relation.object  and relation.end_date='99991231' and relation.class='O' and relation.rel_dir='B' and relation.rel_class='O'
left join hr_md_orm_o_o relation2 on relation.rel_object=relation2.object
left join hr_md_orm_relationship relation3 on relation2.object=relation3.object  and relation3.end_date='99991231' and relation3.class='O' and relation3.rel_dir='B' and relation3.rel_class='O'
left join hr_md_orm_object relation4 on relation3.rel_object=relation4.object and relation4.class='O'
left join hr_md_orm_o_o relation5 on relation3.rel_object=relation5.object 
LEFT JOIN base_cust_ref_OrgGroup oog on oo.org_group=oog.code
left join hr_md_emp_md0015 md15 on md01.emp_id=md15.emp_id
left join #grademax grade_max  on md01.emp_id=grade_max.emp_id
left join (select * from #tbl_1
	union all
	select * from #tbl_2
	union all
	select * from #tbl_3
	union all
	select * from #tbl_4) tbl_1 on md01.emp_id=tbl_1.emp_id
where (pos.description =@Position or (SUBSTRING(pos.description,0,CHARINDEX('&',pos.description,0)) = SUBSTRING(@Position, 0, charindex('and', @Position, 0))and SUBSTRING(@Position,CHARINDEX('and',@Position)+4,LEN(@Position))=SUBSTRING(pos.description,CHARINDEX('&',pos.description)+2,LEN(pos.description)))
or md02.position in 
(
    Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@Position,',','bb  </x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
) or pos.description in
(
	Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@Position,',','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
)
 or @Position=''
)
and 
((datediff(day, yos.start_date, @now)/365>=@YearsFrom or @YearsFrom='')and((datediff(day, yos.start_date, @now) % 365)/30>=@MonthFrom or @MonthFrom=''))
and
((datediff(day, yos.start_date, @now)/365<=@YearsTo or @YearsTo='')and((datediff(day, yos.start_date, @now) % 365)/30<=@MonthTo or @MonthTo=''))
--and
--(md01.external_id like '________' or md01.external_id like '_________')
and
(md02.employee_status='01')
and
(md02.company_id=@CompanyID or @CompanyID='*')
and
(md02.employee_type=@EmployeeType or @EmployeeType='*')
and
(md02.employee_subtype=@EmployeeSubType or @EmployeeSubType='*')
and
(md02.regional=@Regional or @Regional='*')
and
(md02.employee_area=@Employee_Area or @Employee_Area='*')
and
(md02.employee_office=@EmployeeOffice or @EmployeeOffice='*')
and
(md02.payroll_group=@PayrollGroup or @PayrollGroup='*')
and
(grade_max.grade>=@GradeMin or @GradeMin='*')
and
(grade_max.grade<=@GradeMax or @GradeMax='*')
and
(md01.end_date='99991231')
--and
--(md01.emp_id='00101571')
--VALIDASI EXTERNAL ID

drop table #temp2
drop table #temp3
drop table #userid
drop table #grademax

drop table #tbl_1
drop table #tbl_2
drop table #tbl_3
drop table #tbl_4