CLIENT SUPPORT » IMFI Bugs

??

DECLARE @landscape VARCHAR(3) = '100'

DECLARE @companycode VARCHAR(5) = '1000'

DECLARE @emp_status VARCHAR(5) = ''

DECLARE @emp_type VARCHAR(5)= ''

DECLARE @emp_area VARCHAR(5)= ''

DECLARE @emp_office VARCHAR(5) = ''

DECLARE @pay_group VARCHAR(2) = ''

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

--DECLARE @now VARCHAR(12) = '20150901'

       select emp_id ,

                edu_level ,

                major ,

                institution ,

                gpa ,

                edu_level.description ,

				edu_level.description AS edu_level_desc ,

                start_date

				INTO #tbl_3

        FROM    dbo.hr_md_emp_md0008 AS edu

                LEFT JOIN base_cust_ref_edu_level AS edu_level ON edu.edu_level = edu_level.code

        WHERE   end_date = ( SELECT   MAX(end_date)

                               FROM     dbo.hr_md_emp_md0008 AS x

                               WHERE    x.emp_id = edu.emp_id AND x.edu_type='F'

                             )

		ORDER BY edu.seq DESC

        SELECT DISTINCT

                a.org_id_01 AS level1,

                a.org_name_01 AS level1_desc,

                a.org_id_02 AS level2,

                a.org_name_02 AS level2_desc,

                a.org_id_03 AS level3,

                a.org_name_03 AS level3_desc,

                a.org_id_04 AS level4,

                a.org_name_04 AS level4_desc ,

                a.org_id_05 AS level5,

                a.org_name_05 AS level5_desc ,

                a.org_id_06 AS level6,

                a.org_name_06 AS level6_desc,

                a.org_id_07 AS level7,

                a.org_name_07 AS level7_desc,

                a.org_id_08 AS level8,

                a.org_name_08 AS level8_desc,

                md2.emp_id AS emp_id

				INTO #tbl_1

        FROM    dbo.GetOrgInOrgWide('00000001', @now, @landscape) 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                                                          	 

DECLARE @tbl_2 TABLE

    (

      emp_id VARCHAR(8) ,

      movement_type VARCHAR(2) ,

      jumlah DECIMAL(18, 0)

    )	

INSERT  INTO @tbl_2

        SELECT DISTINCT

                emp_id ,

                movement_type ,

                COUNT(start_date)

        FROM    dbo.hr_md_emp_md0002 WITH ( NOLOCK )

        WHERE   movement_type = '60'

        GROUP BY emp_id , movement_type

        SELECT  tbl2.* ,

               '' as datedifff

			   INTO #tbl_md2_last

        FROM    ( SELECT    landscape ,

                            emp_id ,

                            start_date ,

                            end_date ,

                            movement_type ,

                            reason ,

                            company_id ,

                            costcenter ,

                            employee_area ,

                            employee_office ,

                            employee_status ,

                            employee_type ,

                            employee_subtype ,

                            tax_office ,

                            payroll_group ,

                            organization ,

                            position ,

                            job ,

                            remark ,

                            CASE WHEN ISNULL(additional_1, '') = ''

                                 THEN start_date

                                 ELSE additional_1

                            END AS realDate,

							CASE WHEN ISNULL(additional_1, '') = ''

                                 THEN start_date

                                 ELSE additional_1

                            END AS additional_1

                  FROM      dbo.hr_md_emp_md0002 WITH ( NOLOCK )

                ) AS tbl2

SELECT DISTINCT

        md01.emp_id ,

        md01.full_name ,

        md02_last.company_id ,

        CONVERT(DATE, md02_hiring.start_date) AS join_date ,

        CASE WHEN md02_last.movement_type = '20' THEN

                  CONVERT(DATE, md02_last.start_date)

             ELSE ( SELECT  CONVERT(DATE, date)

                    FROM    hr_md_emp_md0039

                    WHERE   date_type = '04'

                            AND emp_id = md02_last.emp_id

                  )

        END permanent_date ,

        CONVERT(DATE, md39_group.date) AS join_group ,