Sunday, August 12, 2012

Approal Function (SQL)

CREATE FUNCTION HS_FN_LEV_APPROVING_PERSON_PARAPET (@EMP_NUMBER VARCHAR(6))
RETURNS VARCHAR(6)
AS BEGIN

DECLARE @EMPLOYEE AS VARCHAR(6)
DECLARE @CORPORATE AS VARCHAR(6)
DECLARE @SUP_EMPLOYEE AS VARCHAR(6)
DECLARE @HR AS VARCHAR(6)
DECLARE @HOD AS VARCHAR(6)
DECLARE @CEO AS VARCHAR(6)
DECLARE @APPROVER AS VARCHAR(6)

SET @EMPLOYEE = @EMP_NUMBER
SET @SUP_EMPLOYEE =  ( SELECT EREP_SUP_EMP_NUMBER FROM HS_HR_EMP_REPORTTO WHERE EREP_REPORTING_MODE=1
AND EREP_SUB_EMP_NUMBER= @EMPLOYEE)
SET @CORPORATE = (SELECT CT_CODE FROM HS_HR_EMPLOYEE WHERE EMP_NUMBER = @EMPLOYEE )
SET @HR = ( SELECT WF_MAIN_APP_EMPLOYEE FROM HS_HR_WF_GROUP_APP_PERSON WHERE WFAPPGRP_CODE = '000001' )
SET @CEO = (SELECT WF_MAIN_APP_EMPLOYEE FROM HS_HR_WF_GROUP_APP_PERSON WHERE WFAPPGRP_CODE = '000001')
SET @HOD = (SELECT HIE_HEAD_EMP_NUMBER FROM HS_HR_COMPANY_HIERARCHY WHERE DEF_LEVEL = 4
AND HIE_CODE IN (SELECT HIE_CODE_4 FROM HS_HR_EMPLOYEE WHERE EMP_NUMBER = @EMPLOYEE ))

IF @CORPORATE IN ('000004','000008','000009','000010','000012','000014','000016')
SET @APPROVER = @SUP_EMPLOYEE
ELSE IF @EMPLOYEE = @HOD
SET @APPROVER = @CEO
ELSE
SET @APPROVER = @HOD

RETURN (@APPROVER)
END

GO

No comments:

Post a Comment