Информационные технологии в управлении персоналом на примере компании ООО "Аксис ПРО"

>

Процедура сохранения данных с закладки "Должность" pfrmEmpCareerPlanPos_Save

USE [ETWeb]

GO

/*** Object: StoredProcedure [dbo].[pfrmEmpCareerPlanPos_Save] ***/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[pfrmEmpCareerPlanPos_Save]

@vintCurUserID INT,

@vintCurSysLID INT,

@rintEmpCPlanPosID INT OUTPUT,

@vintPID INT,

@vintPosID INT,

@vintCPrefID INT,

@vintCandReadyID INT,

@vdtmReadinessDate DATETIME,

@vdtmProposalDate DATETIME,

@vtxtComment NTEXT

,@vintEmpCarPlanID INT -- a. sab

,@rblnIsInsertJobFam BIT OUTPUT -- a. sab1

AS

/*@@*_____________________________________________________

(c) 2001-2004 ExecuTRACK Software AG

Object: pfrmEmpCareerPlanPos_Save

Purpose:

This SP saves data from the Career Plan Position Recommendations form

To the database.

Parameters:

@vintCurUserID: Current user context if available. NULL if unknown.

@vintCurSysLID: Language for translated text. NULL if unknown.

@rintEmpCPlanPosID: PK of record to be saved. If inserting a new record,

Then PK of new record will be returned.

@vintPID: PK of employee with position recommendation.

@vintPosID: PK of position.

Other parameters: Other data to be saved.

Recordsets:

None

Returns:

0 - Success

<>0 - Error occurred

_______________________________________________________*@@*/

/*________________ CONSTANT DECLARATIONS ______________*/

DECLARE

@cstrThisProc NVARCHAR(128),

@cintErrGeneral INT,

@cintErrParamNotFound INT,

@cintErrCPlanPosDupe INT

,@cintErrCurPos INT -- a. sab

,@intJobFamID INT -- a. sab1

,@blnIsExistsJobFam BIT -- a. sab1

SET @cstrThisProc = N'pfrmEmpCareerPlanPos_Save'

SET @cintErrGeneral = 50000 -- General failure

SET @cintErrParamNotFound = 50003 -- Parameter value not found

SET @cintErrCPlanPosDupe = 50423 -- Attempt to enter duplicate

-- position recommendation.

SET @cintErrCurPos = 90001

/*___________ VARIABLE DECLARATIONS ___________________*/

DECLARE

@intErrNo INT,

@strErrMsg NVARCHAR(255),

@intReturn INT

/*_______________________________ INIT _____________________*/

    -- Setting NOCOUNT ON prevents additional recordsets with the -- "number of records affected" from being returned, which is not -- handled the same by all providers, such as for OLEDB vs. ODBC.

SET NOCOUNT ON

/*______________ VALIDATE PARAMETERS __________________*/

-- Check whether @rintEmpCPlanPosID exists

IF (@rintEmpCPlanPosID IS NOT NULL)

AND NOT EXISTS ( SELECT *

FROM tEmpCareerPlanPos EmpCPlanPos

WHERE (EmpCPlanPos. EmpCPlanPosID = @rintEmpCPlanPosID)

) BEGIN

-- Throw the appropriate error and jump to default error handler

SET @intErrNo = @cintErrParamNotFound

EXEC pzError_Throw

@vintCurUserID = @vintCurUserID,

@vintCurSysLID = @vintCurSysLID,

@vstrProcName = @cstrThisProc,

@vintErrNo = @intErrNo,

@rstrErrMsg = @strErrMsg OUTPUT,

@vvarParam01 = N'@rintEmpCPlanPosID'

GOTO lblError

END

    --{ -- Check position

IF EXISTS (SELECT * FROM tSlot Slot WHERE PID = @vintPID AND PosID = @vintPosID) BEGIN

-- Throw the appropriate error and jump to default error handler

SET @intErrNo = @cintErrCurPos

EXEC pzError_Throw

@vintCurUserID = @vintCurUserID,

@vintCurSysLID = @vintCurSysLID,

@vstrProcName = @cstrThisProc,

@vintErrNo = @intErrNo,

@rstrErrMsg = @strErrMsg OUTPUT,

@vvarParam01 = N''

GOTO lblError

END

-- }

/*__________________ MAIN _________________________________*/

-- Check whether the position is already a recommendation for the employee.

IF EXISTS ( SELECT *

FROM tEmpCareerPlanPos EmpCPlanPos

WHERE (EmpCPlanPos. PID = @vintPID)

AND (EmpCPlanPos. PosID = @vintPosID)

AND (EmpCPlanPos. EmpCarPlanID = @vintEmpCarPlanID)

AND ((@rintEmpCPlanPosID IS NULL)

OR (EmpCPlanPos. EmpCPlanPosID <> @rintEmpCPlanPosID))

) BEGIN

-- Throw the appropriate error and jump to default error handler

SET @intErrNo = @cintErrCPlanPosDupe

EXEC pzError_Throw

@vintCurUserID = @vintCurUserID,

@vintCurSysLID = @vintCurSysLID,

@vstrProcName = @cstrThisProc,

@vintErrNo = @intErrNo,

@rstrErrMsg = @strErrMsg OUTPUT

GOTO lblError

END -- IF position recommendation exists

SET @vdtmProposalDate = GETDATE()

-- {

SELECT

@intJobFamID = Pos. JobFamID

FROM tPosition Pos

WHERE Pos. PosID = @vintPosID

SET @blnIsExistsJobFam = 0

IF EXISTS (SELECT *

FROM vEmpCareerPlanJobFam EmpCarPlanJobFam

WHERE (EmpCarPlanJobFam. JobFamID = @intJobFamID)

AND (EmpCarPlanJobFam. EmpCarPlanID = @vintEmpCarPlanID)) BEGIN

SET @blnIsExistsJobFam = 1

END

    -- } -- Check whether inserting a new record (no PK provided) or -- updating an existing record.

IF (@rintEmpCPlanPosID IS NULL) BEGIN

-- Insert a new record.

INSERT INTO tEmpCareerPlanPos (

PID,

PosID,

CPrefID,

CandReadyID,

ProposalDate,

ReadinessDate,

Comment

,EmpCarPlanID -- a. sab

)

SELECT

@vintPID,

@vintPosID,

@vintCPrefID,

@vintCandReadyID,

@vdtmProposalDate,

ReadinessDate = CASE

WHEN SysCfg. AutoCalcReadinessDate = 1

THEN DATEADD(MONTH, CandReady. PeriodLengthInMonths, @vdtmProposalDate)

ELSE @vdtmReadinessDate

END,

@vtxtComment

,@vintEmpCarPlanID -- a. sab

FROM

TzSystemConfig SysCfg

LEFT JOIN tCandidateReadiness CandReady

ON (CandReady. CandReadyID = @vintCandReadyID)

-- Check for a successful insert

SELECT @intErrNo = @@ERROR,

@rintEmpCPlanPosID = SCOPE_IDENTITY()

IF (@intErrNo <> 0) GOTO lblError

    -- { -- Insert a new record.

INSERT INTO tCandidatePos (

PID,

PosID,

IsActive,

Priority,

IsDesignatedSuccessor,

ExternalAtProposal,

CandReadyID,

ProposalDate,

ReadinessDate,

HRPlanPerID,

CandPropSrcID,

ProposedBy,

Comment,

IsFromCareerPlan,

SourceEmpCPlanPosID

,EmpCarPlanID

)

SELECT

@vintPID,

@vintPosID,

IsActive = 1,

NULL,

IsDesignatedSuccessor = 0,

ExternalAtProposal = CASE

WHEN @vintPID IS NULL THEN 1

ELSE 0

END,

@vintCandReadyID,

@vdtmProposalDate,

ReadinessDate = CASE

WHEN SysCfg. AutoCalcReadinessDate = 1

THEN DATEADD(MONTH, CandReady. PeriodLengthInMonths, @vdtmProposalDate)

ELSE @vdtmReadinessDate

END,

HRPlanPerID = (SELECT TOP(1) HRPlanPerID FROM tEmpCareerPlan WHERE PID = @vintPID),

-1,

NULL,

@vtxtComment,

1,

@rintEmpCPlanPosID

,@vintEmpCarPlanID

FROM

tzSystemConfig SysCfg

LEFT JOIN tCandidateReadiness CandReady

ON (CandReady. CandReadyID = @vintCandReadyID)

-- Check for a successful insert

SELECT @intErrNo = @@ERROR

IF (@intErrNo <> 0) GOTO lblError

    -- } -- {

IF @blnIsExistsJobFam = 0

AND @intJobFamID IS NOT NULL

AND NOT EXISTS (SELECT *

FROM tEmpCareerPlanJobFam

WHERE JobFamID = @intJobFamID

AND EmpCarPlanID = @vintEmpCarPlanID) BEGIN

-- Insert a new record.

INSERT INTO tEmpCareerPlanJobFam (

PID,

JobFamID,

CPrefID,

CandReadyID,

ProposalDate,

ReadinessDate,

Comment,

EmpCarPlanID

)

SELECT

@vintPID,

@intJobFamID,

@vintCPrefID,

@vintCandReadyID,

@vdtmProposalDate,

ReadinessDate = CASE

WHEN SysCfg. AutoCalcReadinessDate = 1

THEN DATEADD(MONTH, CandReady. PeriodLengthInMonths, @vdtmProposalDate)

ELSE @vdtmReadinessDate

END,

@vtxtComment,

@vintEmpCarPlanID

FROM

TzSystemConfig SysCfg

LEFT JOIN tCandidateReadiness CandReady

ON (CandReady. CandReadyID = @vintCandReadyID)

-- Check for a successful insert

SELECT @intErrNo = @@ERROR,

@rblnIsInsertJobFam = CAST (1 AS BIT)

IF (@intErrNo <> 0) GOTO lblError

END

-- }

END -- INSERT

ELSE BEGIN -- UPDATE

-- Update the existing record.

UPDATE EmpCPlanPos

SET

EmpCPlanPos. CPrefID = @vintCPrefID,

EmpCPlanPos. CandReadyID = @vintCandReadyID,

EmpCPlanPos. ProposalDate = @vdtmProposalDate,

EmpCPlanPos. ReadinessDate =

CASE

WHEN SysCfg. AutoCalcReadinessDate = 1

THEN DATEADD(MONTH, CandReady. PeriodLengthInMonths, @vdtmProposalDate)

ELSE @vdtmReadinessDate

END,

EmpCPlanPos. Comment = @vtxtComment

FROM

TEmpCareerPlanPos EmpCPlanPos

CROSS JOIN tzSystemConfig SysCfg

LEFT JOIN tCandidateReadiness CandReady

ON (CandReady. CandReadyID = @vintCandReadyID)

WHERE EmpCPlanPos. EmpCPlanPosID = @rintEmpCPlanPosID

AND EmpCPlanPos. EmpCarPlanID = @vintEmpCarPlanID

-- Check for successful update.

SET @intErrNo = @@ERROR

IF (@intErrNo <> 0) GOTO lblError

    -- { -- Update the existing record.

UPDATE CandPos

SET

CandPos. CandReadyID = @vintCandReadyID,

CandPos. ProposalDate = @vdtmProposalDate,

CandPos. ReadinessDate =

CASE

WHEN SysCfg. AutoCalcReadinessDate = 1

THEN DATEADD(MONTH, CandReady. PeriodLengthInMonths, @vdtmProposalDate)

ELSE @vdtmReadinessDate

END,

CandPos. HRPlanPerID = (SELECT TOP(1) HRPlanPerID FROM tEmpCareerPlan WHERE PID = @vintPID),

CandPos. Comment = @vtxtComment

FROM

TCandidatePos CandPos

CROSS JOIN tzSystemConfig SysCfg

LEFT JOIN tCandidateReadiness CandReady

ON (CandReady. CandReadyID = @vintCandReadyID)

WHERE CandPos. PID = @vintPID

AND CandPos. PosID = @vintPosID

AND EmpCarPlanID = @vintEmpCarPlanID

-- Check for a successful update

SELECT @intErrNo = @@ERROR

IF (@intErrNo <> 0) GOTO lblError

-- }

END -- UPDATE

-- Return 0 for success

SET @intReturn = 0

LblExit:

RETURN @intReturn

/*____________________ ERROR HANDLER ___________________*/

LblError:

    -- An error has occurred. On entering this section @intErrNo should -- already hold the error number -- By default, if there are any open transactions then roll them back

IF (@@TRANCOUNT > 0) BEGIN

ROLLBACK TRAN

END

    -- If return value has not been explicitly set, then use the error -- number as return value to signify failure

IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN

SET @intReturn = @intErrNo

END

GOTO lblExit

Похожие статьи




Информационные технологии в управлении персоналом на примере компании ООО "Аксис ПРО"

Предыдущая | Следующая