CLIENT SUPPORT » IMFI Bugs

Dear tim developer mohon supportnya untuk perbaikan pada report dimana pada saat menampilkan view report assignment enhanced memakan waktu yang lama dan timbul error.

remote.minovais.com:31141
minovais

database sql server 2012
remote.minovais.com, 1438

nama report nya Rpt_IMFI_HR_PA_DynamicEmployeeAssignment
reportid 9000127

Berikut ini query nya di dalam rdl tersebut =========
DECLARE @landscape VARCHAR
DECLARE @companyid VARCHAR
DECLARE @emp_subst VARCHAR
DECLARE @emp_suben VARCHAR
DECLARE @emp_statst VARCHAR
DECLARE @emp_staten VARCHAR
DECLARE @emp_areast VARCHAR
DECLARE @emp_areaen VARCHAR
DECLARE @pygrst VARCHAR
DECLARE @pygren VARCHAR
DECLARE @emp_idst VARCHAR
DECLARE @emp_iden VARCHAR
DECLARE @emp_typest VARCHAR
DECLARE @emp_typeen VARCHAR
DECLARE @organisasi VARCHAR
DECLARE @movtype_st VARCHAR
DECLARE @movtype_en VARCHAR
DECLARE @reason_st VARCHAR
DECLARE @reason_en VARCHAR
DECLARE @subtype_st VARCHAR
DECLARE @subtype_en VARCHAR
DECLARE @startdate VARCHAR
DECLARE @enddate VARCHAR
DECLARE @realdatest VARCHAR
DECLARE @realdateen VARCHAR
DECLARE @createddatest VARCHAR
DECLARE @createddateen VARCHAR

SET @landscape = '100'
SET @companyid = '1000'
SET @emp_subst = ''
SET @emp_suben = ''
SET @emp_statst = '01'
SET @emp_staten = '01'
SET @emp_areast = ''
SET @emp_areaen = ''
SET @pygrst = ''
SET @pygren = ''
SET @emp_idst = ''
SET @emp_iden = ''
SET @emp_typest = ''
SET @emp_typeen = ''
SET @organisasi = ''
SET @movtype_st = ''
SET @movtype_en = ''
SET @reason_st = ''
SET @reason_en = ''
SET @subtype_st = ''
SET @subtype_en = ''
SET @startdate = ''
SET @enddate = ''
SET @realdatest = ''
SET @realdateen = ''
SET @createddatest = ''
SET @createddateen = ''

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

DECLARE @temp AS TABLE
(
emp_id VARCHAR ,
full_name VARCHAR ,
[Start Date] VARCHAR ,
[End Date] VARCHAR ,
[Movement Type] VARCHAR ,
[Employee Status] VARCHAR ,
[Employee Type] VARCHAR ,
[Organization] VARCHAR ,
[Employee Office] VARCHAR ,
[Position] VARCHAR ,
[Employee Sub Type] VARCHAR ,
assignment VARCHAR ,
number VARCHAR
)
INSERT INTO @temp
SELECT DISTINCT
md1.emp_id ,
md1.full_name ,
dbo.fn_formatdatetime(md2.start_date,'dd/mm/yyyy') AS start_date ,
dbo.fn_formatdatetime(md2.end_date,'dd/mm/yyyy') AS end_date ,
movtype.description AS movement_type ,
empstat.description AS emp_status ,
emptype.description AS emp_type ,
o.description AS org ,
empoff.emp_subarea_description AS emp_office ,
p.description AS pos ,
empsubtype.description AS emp_subtype ,
ROW_NUMBER() OVER as assignment,
DENSE_RANK() OVER as number
FROM hr_md_emp_md0001 md1
INNER JOIN dbo.hr_md_emp_md0002 md2 ON md1.landscape = md2.landscape
AND md1.emp_id = md2.emp_id
LEFT OUTER JOIN dbo.base_cust_ref_emp_status empstat ON md2.landscape = empstat.landscape
AND md2.employee_status = empstat.emp_status
LEFT OUTER JOIN dbo.base_cust_ref_emp_area emparea ON md2.landscape = emparea.landscape
AND md2.employee_area = emparea.emp_area
LEFT OUTER JOIN dbo.base_cust_ref_emp_office empoff ON md2.landscape = empoff.landscape
AND md2.employee_office = empoff.emp_subarea
LEFT OUTER JOIN dbo.base_cust_ref_emp_type emptype ON md2.landscape = emptype.landscape
AND md2.employee_type = emptype.emp_type
LEFT OUTER JOIN dbo.base_cust_ref_emp_subtype empsubtype ON md2.landscape = empsubtype.landscape
AND md2.employee_subtype = empsubtype.emp_subtype
LEFT OUTER JOIN dbo.base_cust_ref_mov_type movtype ON md2.landscape = movtype.landscape
AND md2.movement_type = movtype.code
LEFT OUTER JOIN dbo.base_cust_ref_mov_reason reason ON md2.landscape = reason.landscape
AND md2.reason = reason.code
AND md2.movement_type = reason.mov_type
LEFT OUTER JOIN dbo.hr_md_orm_object cc ON md2.landscape = cc.landscape
AND md2.costcenter = cc.object
AND cc.class = 'CC'
AND cc.start_date <= @now
AND cc.end_date >= @now
LEFT OUTER JOIN dbo.hr_md_orm_object o ON md2.landscape = o.landscape
AND md2.organization = o.object
AND o.class = 'O'
AND o.start_date <= @now
AND o.end_date >= @now
LEFT OUTER JOIN dbo.hr_md_orm_object p ON md2.landscape = p.landscape
AND md2.position = p.object
AND p.class = 'P'
AND p.start_date <= @now
AND p.end_date >= @now
LEFT OUTER JOIN dbo.hr_md_orm_object j ON md2.landscape = j.landscape
AND md2.job = j.object
AND j.class = 'J'
AND j.start_date <= @now
AND j.end_date >= @now
WHERE ( md1.landscape = @landscape )
AND ( md2.emp_id >= @emp_idst
OR @emp_idst = ''
)
AND ( md2.emp_id <= @emp_iden
OR @emp_iden = ''
)
AND ( md2.company_id = @companyid
OR @companyid = ''
)
AND ( md1.start_date <= @now )
AND ( md1.end_date >= @now )
AND md2.employee_status IN (
CASE WHEN ISNULL = ''
THEN md2.employee_status
ELSE @emp_statst
END,
CASE WHEN ISNULL = ''
THEN md2.employee_status
ELSE @emp_staten
END )
AND md2.employee_area IN (
CASE WHEN ISNULL = '' THEN md2.employee_area
ELSE @emp_areast
END,
CASE WHEN ISNULL = '' THEN md2.employee_area
ELSE @emp_areaen
END )
AND md2.employee_office IN (
CASE WHEN ISNULL = '' THEN md2.employee_office
ELSE @emp_subst
END,
CASE WHEN ISNULL = '' THEN md2.employee_office
ELSE @emp_suben
END )
AND md2.employee_type IN (
CASE WHEN ISNULL = '' THEN md2.employee_type
ELSE @emp_typest
END,
CASE WHEN ISNULL = '' THEN md2.employee_type
ELSE @emp_typeen
END )
AND md2.movement_type IN (
CASE WHEN ISNULL = '' THEN md2.movement_type
ELSE @movtype_st
END,
CASE WHEN ISNULL = '' THEN md2.movement_type
ELSE @movtype_en
END )
AND md2.reason IN (
CASE WHEN ISNULL = '' THEN md2.reason
ELSE @reason_st
END, CASE WHEN ISNULL = '' THEN md2.reason
ELSE @reason_en
END )
AND md2.employee_subtype IN (
CASE WHEN ISNULL = ''
THEN md2.employee_subtype
ELSE @subtype_st
END,
CASE WHEN ISNULL = ''
THEN md2.employee_subtype
ELSE @subtype_en
END )
AND md2.payroll_group IN (
CASE WHEN ISNULL = '' THEN md2.payroll_group
ELSE @pygrst
END, CASE WHEN ISNULL = '' THEN md2.payroll_group
ELSE @pygren
END )
AND ( md2.start_date >= @startdate
OR @startdate = ''
)
AND ( md2.start_date <= @enddate
OR @enddate = ''
)
AND ( LEFT >= @createddatest
OR @createddatest = ''
)
AND ( LEFT <= @createddateen
OR @createddateen = ''
)

SELECT *
FROM @temp UNPIVOT ( value FOR field IN ( [Start Date], [End Date],
[Movement Type],
[Employee Status],
[Employee Type], [Organization],
[Employee Office], [Position],
[Employee Sub Type] ) ) unpvt

============