sp_hr_md_emp_mutasi_20260206.sql - Bank Mega - Redmine

Bug #4181 » sp_hr_md_emp_mutasi_20260206.sql

Tri Rizqiaty, 02/06/2026 03:46 PM

    
      ??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

(4-4/6)

Project

General

Profile

CLIENT SUPPORT » Bank Mega

Bug #4181 » sp_hr_md_emp_mutasi_20260206.sql