cyf/lserp_cs_6.0
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
codex/agent-pet-startup
lserp_cs_6.0/插件库/Lskj.AgentBridge/Deployment/customer-profiles/lserp-ai.workflow-write.leave.compat100.draft.sql
T
郎速科技 4c08f4c948 feat: add ERP agent pet bridge and startup guide
2026-08-14 14:28:28 +08:00

766 lines
30 KiB
Transact-SQL
Raw Permalink Blame History

/*
Customer-specific review draft for hr_4011 leave writes in the lserp_AI
compatibility-level-100 database.
THIS FILE IS INERT:
1. SET NOEXEC ON prevents CREATE PROCEDURE from being applied.
2. @customer_dba_reviewed is deliberately fixed to 0.
3. The script must never be edited into a production deployment artifact. A customer
DBA must create and sign a separate script after reviewing every dependency below.
Reviewed design assumptions that still require customer DBA acceptance:
- p_lserp_agent_workflow_read_compat100 is the approved fixed-parameter read contract;
- hr_4011 is a newVer=1 base module backed by HR_EmpLeaveAloneTab;
- p_BaseSave70 is the exact ERP create path and generates hr_ela_no through the
configured document-number rules;
- p_baseApply is the exact ERP submit path;
- p_SubsysPurviewTab.hrPurview is the customer menu permission source, menu
16629 is hr_4011, and only the edit token (without the read-only "|" suffix)
authorizes a write;
- p_agent_command_idempotency, p_agent_business_audit and
p_agent_integration_outbox were created from 001_agent_business_idempotency.sql;
- the legacy procedures may emit diagnostic result sets; the trusted ERP gateway
discards those and accepts exactly one result set whose ten columns exactly match
the fixed BusinessWriteResult contract;
- the fixed INSERT column list below matches the approved UI save payload, including
nullable department/post and secondary-hours fields that are intentionally omitted.
No table name, column name, SQL fragment or procedure name is accepted from the Agent.
The only dynamic SQL string is an internally constructed, fixed-template @base_sql
required by the legacy p_BaseSave70 contract. Every text value is length checked and
escaped inside this wrapper.
*/
SET NOEXEC ON;
GO
CREATE PROCEDURE dbo.p_lserp_agent_workflow_write_leave_compat100
@action VARCHAR(64),
@module_code NVARCHAR(64),
@account_book NVARCHAR(64),
@subsystem_id NVARCHAR(32),
@user_id NVARCHAR(64),
@correlation_id VARCHAR(128),
@idempotency_key VARCHAR(128),
@input_fingerprint CHAR(64),
@employee_id NVARCHAR(64) = NULL,
@leave_type_code NVARCHAR(64) = NULL,
@flow_type_code NVARCHAR(64) = NULL,
@start_local DATETIME = NULL,
@end_local DATETIME = NULL,
@requested_hours DECIMAL(18, 6) = NULL,
@reason NVARCHAR(500) = NULL,
@submit_after_save_intent BIT = NULL,
@record_id NVARCHAR(128) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @customer_dba_reviewed BIT;
SET @customer_dba_reviewed = 0;
IF @customer_dba_reviewed <> 1
BEGIN
RAISERROR(N'customer_dba_review_required', 16, 1);
RETURN;
END;
IF DB_NAME() <> N'lserp_AI'
OR @module_code <> N'hr_4011'
OR @subsystem_id <> N'7'
OR @action NOT IN ('create_draft', 'submit')
OR NULLIF(LTRIM(RTRIM(@account_book)), N'') IS NULL
OR NULLIF(LTRIM(RTRIM(@user_id)), N'') IS NULL
OR @@TRANCOUNT < 1 OR XACT_STATE() <> 1
BEGIN
RAISERROR(N'leave_write_scope_invalid', 16, 1);
RETURN;
END;
IF @correlation_id IS NULL
OR LEN(@correlation_id) < 8 OR LEN(@correlation_id) > 128
OR @correlation_id LIKE '%[^A-Za-z0-9_.:-]%'
OR @idempotency_key IS NULL
OR LEN(@idempotency_key) < 8 OR LEN(@idempotency_key) > 128
OR @idempotency_key LIKE '%[^A-Za-z0-9_.:-]%'
OR @input_fingerprint IS NULL OR LEN(@input_fingerprint) <> 64
OR @input_fingerprint LIKE '%[^A-Fa-f0-9]%'
BEGIN
RAISERROR(N'leave_write_evidence_invalid', 16, 1);
RETURN;
END;
DECLARE @context TABLE
(
current_employee_id NVARCHAR(64) NOT NULL,
can_apply_for_others BIT NOT NULL,
now_local DATETIME NOT NULL
);
INSERT INTO @context
(current_employee_id, can_apply_for_others, now_local)
EXEC dbo.p_lserp_agent_workflow_read_compat100
@workflow = 'leave',
@action = 'context',
@module_code = @module_code,
@account_book = @account_book,
@subsystem_id = @subsystem_id,
@user_id = @user_id;
IF (SELECT COUNT_BIG(*) FROM @context) <> 1
BEGIN
RAISERROR(N'leave_context_contract_invalid', 16, 1);
RETURN;
END;
DECLARE @current_employee_text NVARCHAR(64);
DECLARE @can_apply_for_others BIT;
DECLARE @now_local DATETIME;
SELECT
@current_employee_text = current_employee_id,
@can_apply_for_others = can_apply_for_others,
@now_local = now_local
FROM @context;
IF LEN(@current_employee_text) > 10
OR @current_employee_text LIKE N'%[^0-9]%'
BEGIN
RAISERROR(N'leave_context_contract_invalid', 16, 1);
RETURN;
END;
DECLARE @current_employee_number DECIMAL(10, 0);
DECLARE @current_employee_id INT;
SET @current_employee_number = CONVERT(DECIMAL(10, 0), @current_employee_text);
IF @current_employee_number < 1 OR @current_employee_number > 2147483647
BEGIN
RAISERROR(N'leave_context_contract_invalid', 16, 1);
RETURN;
END;
SET @current_employee_id = CONVERT(INT, @current_employee_number);
IF @user_id IS NULL OR LEN(@user_id) > 10
OR @user_id LIKE N'%[^0-9]%'
BEGIN
RAISERROR(N'leave_user_scope_invalid', 16, 1);
RETURN;
END;
DECLARE @user_number DECIMAL(10, 0);
SET @user_number = CONVERT(DECIMAL(10, 0), @user_id);
IF @user_number < 1 OR @user_number > 2147483647
OR CONVERT(INT, @user_number) <> @current_employee_id
BEGIN
RAISERROR(N'leave_user_scope_invalid', 16, 1);
RETURN;
END;
DECLARE @operator_name NVARCHAR(50);
SELECT @operator_name = CONVERT(NVARCHAR(50), EmployeeName)
FROM dbo.p_employeetab WITH (HOLDLOCK)
WHERE employeeid = @current_employee_id
AND ISNULL(p_emp_status, '') <> N'离职';
IF @operator_name IS NULL
BEGIN
RAISERROR(N'leave_context_contract_invalid', 16, 1);
RETURN;
END;
/*
Read-only menu tokens end in "|". A write must have the exact edit token
",16629,"; the trusted WinForm authorizer performs the same check before
confirmation, and this database check closes the time-of-check/use gap.
*/
DECLARE @leave_permission_text NVARCHAR(MAX);
SET @leave_permission_text = N',';
SELECT TOP (1)
@leave_permission_text = N','
+ CONVERT(NVARCHAR(MAX), ISNULL(hrPurview, '')) + N','
FROM dbo.p_SubsysPurviewTab WITH (UPDLOCK, HOLDLOCK)
WHERE employeeid = @current_employee_id;
IF (@current_employee_id <> 1 OR @operator_name <> N'管理员')
AND CHARINDEX(N',16629,', @leave_permission_text) = 0
BEGIN
RAISERROR(N'leave_write_permission_denied', 16, 1);
RETURN;
END;
DECLARE @command_name VARCHAR(128);
SET @command_name = CASE WHEN @action = 'create_draft'
THEN 'hr.leave.create' ELSE 'hr.leave.submit' END;
IF OBJECT_ID(N'dbo.p_agent_command_idempotency', N'U') IS NULL
OR OBJECT_ID(N'dbo.p_agent_business_audit', N'U') IS NULL
OR OBJECT_ID(N'dbo.p_agent_integration_outbox', N'U') IS NULL
BEGIN
RAISERROR(N'leave_agent_evidence_schema_missing', 16, 1);
RETURN;
END;
DECLARE @application_lock_result INT;
DECLARE @application_lock_resource NVARCHAR(255);
SET @application_lock_resource = N'lserp-agent:leave:'
+ LEFT(@account_book, 40) + N':' + LEFT(@subsystem_id, 20)
+ N':' + LEFT(@user_id, 10) + N':' + @command_name
+ N':' + CONVERT(NVARCHAR(128), @idempotency_key);
EXEC @application_lock_result = sys.sp_getapplock
@Resource = @application_lock_resource,
@LockMode = 'Exclusive',
@LockOwner = 'Transaction',
@LockTimeout = 10000;
IF @application_lock_result < 0
BEGIN
RAISERROR(N'leave_idempotency_lock_failed', 16, 1);
RETURN;
END;
DECLARE @idempotency_id BIGINT;
DECLARE @stored_fingerprint CHAR(64);
DECLARE @stored_status TINYINT;
DECLARE @stored_result_code VARCHAR(128);
DECLARE @stored_record_id NVARCHAR(128);
DECLARE @stored_transaction_id VARCHAR(128);
DECLARE @stored_audit_id VARCHAR(128);
SELECT
@idempotency_id = id,
@stored_fingerprint = input_fingerprint,
@stored_status = status,
@stored_result_code = result_code,
@stored_record_id = record_id,
@stored_transaction_id = transaction_evidence_id,
@stored_audit_id = business_audit_id
FROM dbo.p_agent_command_idempotency WITH (UPDLOCK, HOLDLOCK)
WHERE account_book = @account_book
AND subsystem_id = @subsystem_id
AND user_id = @user_id
AND command_name = @command_name
AND idempotency_key = @idempotency_key;
IF @idempotency_id IS NOT NULL
AND @stored_fingerprint <> @input_fingerprint
BEGIN
SELECT
CONVERT(BIT, 0) AS success,
CONVERT(VARCHAR(128), 'idempotency_key_conflict') AS code,
CONVERT(NVARCHAR(1000), N'同一幂等键已绑定不同的业务输入。') AS message,
CONVERT(NVARCHAR(128), NULL) AS record_id,
CONVERT(BIT, 0) AS needs_ui,
CONVERT(BIT, 0) AS idempotency_replayed,
@idempotency_key AS applied_idempotency_key,
@input_fingerprint AS applied_input_fingerprint,
CONVERT(VARCHAR(128), NULL) AS transaction_evidence_id,
CONVERT(VARCHAR(128), NULL) AS business_audit_id;
RETURN;
END;
IF @idempotency_id IS NOT NULL AND @stored_status = 1
BEGIN
SELECT
CONVERT(BIT, 1) AS success,
@stored_result_code AS code,
CONVERT(NVARCHAR(1000), N'已返回同一幂等请求的原事务结果。') AS message,
@stored_record_id AS record_id,
CONVERT(BIT, 0) AS needs_ui,
CONVERT(BIT, 1) AS idempotency_replayed,
@idempotency_key AS applied_idempotency_key,
@input_fingerprint AS applied_input_fingerprint,
@stored_transaction_id AS transaction_evidence_id,
@stored_audit_id AS business_audit_id;
RETURN;
END;
IF @idempotency_id IS NOT NULL
BEGIN
SELECT
CONVERT(BIT, 0) AS success,
CONVERT(VARCHAR(128), 'idempotency_request_not_replayable') AS code,
CONVERT(NVARCHAR(1000), N'同一幂等请求尚未形成可重放的成功结果。') AS message,
CONVERT(NVARCHAR(128), NULL) AS record_id,
CONVERT(BIT, 0) AS needs_ui,
CONVERT(BIT, 0) AS idempotency_replayed,
@idempotency_key AS applied_idempotency_key,
@input_fingerprint AS applied_input_fingerprint,
CONVERT(VARCHAR(128), NULL) AS transaction_evidence_id,
CONVERT(VARCHAR(128), NULL) AS business_audit_id;
RETURN;
END;
INSERT INTO dbo.p_agent_command_idempotency
(
account_book, subsystem_id, user_id, command_name,
idempotency_key, input_fingerprint, status
)
VALUES
(
@account_book, @subsystem_id, @user_id, @command_name,
@idempotency_key, @input_fingerprint, 0
);
SET @idempotency_id = CONVERT(BIGINT, SCOPE_IDENTITY());
DECLARE @effective_record_id NVARCHAR(128);
DECLARE @result_code VARCHAR(128);
DECLARE @result_message NVARCHAR(1000);
IF @action = 'create_draft'
BEGIN
SET @employee_id = NULLIF(LTRIM(RTRIM(@employee_id)), N'');
SET @leave_type_code = NULLIF(LTRIM(RTRIM(@leave_type_code)), N'');
SET @flow_type_code = NULLIF(LTRIM(RTRIM(@flow_type_code)), N'');
SET @reason = NULLIF(LTRIM(RTRIM(@reason)), N'');
IF @employee_id IS NULL OR LEN(@employee_id) > 10
OR @employee_id LIKE N'%[^0-9]%'
OR @leave_type_code IS NULL OR LEN(@leave_type_code) > 10
OR @leave_type_code LIKE N'%[^0-9]%'
OR @flow_type_code IS NULL OR LEN(@flow_type_code) > 10
OR @flow_type_code LIKE N'%[^0-9]%'
OR @start_local IS NULL OR @end_local IS NULL
OR @end_local <= @start_local
OR DATEDIFF(DAY, CONVERT(DATE, @start_local),
CONVERT(DATE, @end_local)) > 31
OR @requested_hours IS NULL
OR @requested_hours < 0 OR @requested_hours > 744
OR @reason IS NULL OR LEN(@reason) < 2 OR LEN(@reason) > 500
OR DATALENGTH(CONVERT(VARCHAR(8000), @reason)) > 500
OR CONVERT(NVARCHAR(500), CONVERT(VARCHAR(500), @reason)) <> @reason
BEGIN
RAISERROR(N'leave_create_input_invalid', 16, 1);
RETURN;
END;
DECLARE @control_code INT;
SET @control_code = 1;
WHILE @control_code <= 31
BEGIN
IF CHARINDEX(NCHAR(@control_code), @reason) > 0
BEGIN
RAISERROR(N'leave_create_input_invalid', 16, 1);
RETURN;
END;
SET @control_code = @control_code + 1;
END;
IF CHARINDEX(NCHAR(127), @reason) > 0
BEGIN
RAISERROR(N'leave_create_input_invalid', 16, 1);
RETURN;
END;
DECLARE @requested_employee_number DECIMAL(10, 0);
DECLARE @leave_type_number DECIMAL(10, 0);
DECLARE @flow_type_number DECIMAL(10, 0);
SET @requested_employee_number = CONVERT(DECIMAL(10, 0), @employee_id);
SET @leave_type_number = CONVERT(DECIMAL(10, 0), @leave_type_code);
SET @flow_type_number = CONVERT(DECIMAL(10, 0), @flow_type_code);
IF @requested_employee_number < 1
OR @requested_employee_number > 2147483647
OR @leave_type_number < 1 OR @leave_type_number > 2147483647
OR @flow_type_number < 1 OR @flow_type_number > 2147483647
OR (@requested_employee_number <> @current_employee_id
AND ISNULL(@can_apply_for_others, 0) <> 1)
BEGIN
RAISERROR(N'leave_apply_for_others_denied', 16, 1);
RETURN;
END;
IF @start_local < CONVERT(DATE, @now_local)
BEGIN
RAISERROR(N'leave_past_start_denied', 16, 1);
RETURN;
END;
DECLARE @type_state TABLE (enabled BIT NOT NULL);
INSERT INTO @type_state(enabled)
EXEC dbo.p_lserp_agent_workflow_read_compat100
@workflow = 'leave',
@action = 'type_enabled',
@module_code = @module_code,
@account_book = @account_book,
@subsystem_id = @subsystem_id,
@user_id = @user_id,
@leave_type_code = @leave_type_code;
IF (SELECT COUNT_BIG(*) FROM @type_state) <> 1
OR NOT EXISTS (SELECT 1 FROM @type_state WHERE enabled = 1)
BEGIN
RAISERROR(N'leave_type_not_enabled', 16, 1);
RETURN;
END;
DECLARE @flow_state TABLE (enabled BIT NOT NULL);
INSERT INTO @flow_state(enabled)
EXEC dbo.p_lserp_agent_workflow_read_compat100
@workflow = 'leave',
@action = 'flow_type_enabled',
@module_code = @module_code,
@account_book = @account_book,
@subsystem_id = @subsystem_id,
@user_id = @user_id,
@flow_type_code = @flow_type_code;
IF (SELECT COUNT_BIG(*) FROM @flow_state) <> 1
OR NOT EXISTS (SELECT 1 FROM @flow_state WHERE enabled = 1)
BEGIN
RAISERROR(N'leave_flow_type_not_enabled', 16, 1);
RETURN;
END;
DECLARE @hours_state TABLE (hours DECIMAL(18, 6) NOT NULL);
INSERT INTO @hours_state(hours)
EXEC dbo.p_lserp_agent_workflow_read_compat100
@workflow = 'leave',
@action = 'calculate_hours',
@module_code = @module_code,
@account_book = @account_book,
@subsystem_id = @subsystem_id,
@user_id = @user_id,
@employee_id = @employee_id,
@start_local = @start_local,
@end_local = @end_local;
IF (SELECT COUNT_BIG(*) FROM @hours_state) <> 1
BEGIN
RAISERROR(N'leave_hours_contract_invalid', 16, 1);
RETURN;
END;
DECLARE @calculated_hours DECIMAL(18, 6);
SELECT @calculated_hours = hours FROM @hours_state;
IF @calculated_hours <= 0
OR (@requested_hours > 0
AND ABS(@requested_hours - @calculated_hours) > 0.01)
BEGIN
RAISERROR(N'leave_hours_mismatch', 16, 1);
RETURN;
END;
DECLARE @conflict_state TABLE (has_conflict BIT NOT NULL);
INSERT INTO @conflict_state(has_conflict)
EXEC dbo.p_lserp_agent_workflow_read_compat100
@workflow = 'leave',
@action = 'has_conflict',
@module_code = @module_code,
@account_book = @account_book,
@subsystem_id = @subsystem_id,
@user_id = @user_id,
@employee_id = @employee_id,
@start_local = @start_local,
@end_local = @end_local;
IF (SELECT COUNT_BIG(*) FROM @conflict_state) <> 1
OR EXISTS (SELECT 1 FROM @conflict_state WHERE has_conflict = 1)
BEGIN
RAISERROR(N'leave_time_conflict', 16, 1);
RETURN;
END;
DECLARE @employee_name NVARCHAR(50);
DECLARE @employee_department_id INT;
DECLARE @employee_post_id INT;
SELECT
@employee_name = CONVERT(NVARCHAR(50), EmployeeName),
@employee_department_id = Departmentid,
@employee_post_id = p_emp_postid
FROM dbo.p_employeetab WITH (HOLDLOCK)
WHERE employeeid = CONVERT(INT, @requested_employee_number)
AND ISNULL(p_emp_status, '') <> N'离职';
IF @employee_name IS NULL
OR @employee_department_id IS NULL
OR @employee_post_id IS NULL
OR DATALENGTH(CONVERT(VARCHAR(8000), @employee_name)) > 50
OR DATALENGTH(CONVERT(VARCHAR(8000), @operator_name)) > 10
BEGIN
RAISERROR(N'leave_employee_not_available', 16, 1);
RETURN;
END;
DECLARE @standard_day_hours DECIMAL(18, 6);
DECLARE @calculated_days DECIMAL(18, 6);
SELECT TOP (1)
@standard_day_hours = CONVERT(DECIMAL(18, 6),
CASE WHEN ISNULL(check_param.shichang, 0) = 0
THEN 7.5 ELSE check_param.shichang END)
FROM dbo.HR_ScheduleTab AS schedule WITH (HOLDLOCK)
LEFT JOIN dbo.P_SystemCheckParamTab AS check_param WITH (HOLDLOCK)
ON schedule.HR_Schedule_billdocument_d = check_param.Billdocument_Id
WHERE schedule.HR_Schedule_employeeid =
CONVERT(INT, @requested_employee_number)
AND schedule.HR_Schedule_Date <= CONVERT(DATE, @start_local)
ORDER BY schedule.HR_Schedule_Date DESC;
IF @standard_day_hours IS NULL OR @standard_day_hours <= 0
BEGIN
RAISERROR(N'leave_day_hours_not_configured', 16, 1);
RETURN;
END;
SET @calculated_days = ROUND(
@calculated_hours / @standard_day_hours, 1);
IF @calculated_days <= 0 OR @calculated_days > 366
BEGIN
RAISERROR(N'leave_calculated_days_invalid', 16, 1);
RETURN;
END;
DECLARE @number_lock_result INT;
EXEC @number_lock_result = sys.sp_getapplock
@Resource = N'lserp-agent:hr_4011:document-number',
@LockMode = 'Exclusive',
@LockOwner = 'Transaction',
@LockTimeout = 10000;
IF @number_lock_result < 0
BEGIN
RAISERROR(N'leave_document_number_lock_failed', 16, 1);
RETURN;
END;
/*
Hold the identity tail until the outer transaction completes. The legacy
save procedure can overwrite @msg after assigning the document number, so
the created row is identified by the protected identity interval instead of
trusting that output text.
*/
DECLARE @identity_before INT;
SELECT @identity_before = ISNULL(MAX(hr_ela_id), 0)
FROM dbo.HR_EmpLeaveAloneTab WITH (UPDLOCK, HOLDLOCK);
DECLARE @base_sql NVARCHAR(MAX);
SET @base_sql =
N'INSERT INTO dbo.HR_EmpLeaveAloneTab '
+ N'(hr_ela_empid,hr_ela_employeename,hr_ela_depid,hr_ela_postid,'
+ N'hr_ela_type,hr_ela_billtype,'
+ N'hr_ela_Leavebak,hr_ela_starttime,hr_ela_finishtime,'
+ N'hr_ela_totals,hr_ela_totals1,hr_ela_operatorid,hr_ela_operatorname,'
+ N'hr_ela_operatedate) VALUES ('
+ CONVERT(NVARCHAR(20), CONVERT(INT, @requested_employee_number))
+ N',N''' + REPLACE(@employee_name, N'''', N'''''') + N''','
+ N'N''' + CONVERT(NVARCHAR(20), @employee_department_id) + N''','
+ N'N''' + CONVERT(NVARCHAR(20), @employee_post_id) + N''','
+ CONVERT(NVARCHAR(20), CONVERT(INT, @leave_type_number))
+ N',' + CONVERT(NVARCHAR(20), CONVERT(INT, @flow_type_number)) + N','
+ N'N''' + REPLACE(@reason, N'''', N'''''') + N''','''
+ CONVERT(NVARCHAR(23), @start_local, 121) + N''','''
+ CONVERT(NVARCHAR(23), @end_local, 121) + N''','
+ CONVERT(NVARCHAR(50), @calculated_hours) + N','
+ CONVERT(NVARCHAR(50), @calculated_days) + N','
+ CONVERT(NVARCHAR(20), @current_employee_id) + N',N'''
+ REPLACE(@operator_name, N'''', N'''''') + N''','''
+ CONVERT(NVARCHAR(23), @now_local, 121) + N''')';
DECLARE @legacy_message NVARCHAR(4000);
DECLARE @legacy_return INT;
SET @legacy_message = N'';
EXEC @legacy_return = dbo.p_BaseSave70
@baseSql = @base_sql,
@saveType = 1,
@modid = 'hr_4011',
@tablename = 'HR_EmpLeaveAloneTab',
@keyfield = 'hr_ela_id',
@keyvalue = '',
@Operatorid = @current_employee_id,
@operatorName = @operator_name,
@msg = @legacy_message OUTPUT,
@comfirmFlag = 0;
IF @legacy_return <> 1 OR @@TRANCOUNT < 1 OR XACT_STATE() <> 1
BEGIN
RAISERROR(N'leave_legacy_create_failed', 16, 1);
RETURN;
END;
DECLARE @created_record_count BIGINT;
DECLARE @created_identity INT;
SELECT
@created_record_count = COUNT_BIG(*),
@created_identity = MAX(hr_ela_id),
@effective_record_id = MAX(CONVERT(NVARCHAR(50), hr_ela_no))
FROM dbo.HR_EmpLeaveAloneTab WITH (UPDLOCK, HOLDLOCK)
WHERE hr_ela_id > @identity_before;
IF @created_record_count <> 1
OR @created_identity IS NULL
OR NULLIF(LTRIM(RTRIM(@effective_record_id)), N'') IS NULL
OR NOT EXISTS
(
SELECT 1
FROM dbo.HR_EmpLeaveAloneTab WITH (UPDLOCK, HOLDLOCK)
WHERE hr_ela_id = @created_identity
AND CONVERT(NVARCHAR(50), hr_ela_no) = @effective_record_id
AND hr_ela_empid = CONVERT(INT, @requested_employee_number)
AND hr_ela_employeename = CONVERT(VARCHAR(50), @employee_name)
AND hr_ela_depid = CONVERT(VARCHAR(20), @employee_department_id)
AND hr_ela_postid = CONVERT(VARCHAR(20), @employee_post_id)
AND hr_ela_type = CONVERT(INT, @leave_type_number)
AND hr_ela_billtype = CONVERT(INT, @flow_type_number)
AND hr_ela_starttime = @start_local
AND hr_ela_finishtime = @end_local
AND ABS(ISNULL(hr_ela_totals, 0) - @calculated_hours) <= 0.01
AND ABS(ISNULL(hr_ela_totals1, 0) - @calculated_days) <= 0.01
AND hr_ela_Leavebak = CONVERT(VARCHAR(500), @reason)
AND hr_ela_operatorid = @current_employee_id
AND ISNULL(hr_ela_cancelFlag, 0) = 0
)
BEGIN
RAISERROR(N'leave_legacy_create_evidence_invalid', 16, 1);
RETURN;
END;
SET @result_code = 'leave_draft_created';
SET @result_message = N'请假申请草稿已通过 ERP 原保存链创建。';
/* submit_after_save_intent is intentionally never executed here. */
SET @submit_after_save_intent = ISNULL(@submit_after_save_intent, 0);
END
ELSE
BEGIN
SET @record_id = NULLIF(LTRIM(RTRIM(@record_id)), N'');
IF @record_id IS NULL OR LEN(@record_id) > 128
BEGIN
RAISERROR(N'leave_submit_record_invalid', 16, 1);
RETURN;
END;
IF DATALENGTH(CONVERT(VARCHAR(8000), @operator_name)) > 20
BEGIN
RAISERROR(N'leave_submit_operator_invalid', 16, 1);
RETURN;
END;
/* p_baseApply contains legacy self-migration branches; never let an Agent
request reach those branches. Schema repair remains a DBA operation. */
IF COL_LENGTH('dbo.p_systemdlltabflowtypestep', 'autoStep') IS NULL
OR COL_LENGTH('dbo.p_systemdlltabflowtypestep', 'comfirmOper') IS NULL
OR COL_LENGTH('dbo.p_systemNotification', 'stepCode') IS NULL
BEGIN
RAISERROR(N'leave_approval_schema_not_ready', 16, 1);
RETURN;
END;
DECLARE @submit_state TABLE
(
can_submit BIT NOT NULL,
reason NVARCHAR(1000) NULL
);
INSERT INTO @submit_state(can_submit, reason)
EXEC dbo.p_lserp_agent_workflow_read_compat100
@workflow = 'leave',
@action = 'can_submit',
@module_code = @module_code,
@account_book = @account_book,
@subsystem_id = @subsystem_id,
@user_id = @user_id,
@record_id = @record_id;
IF (SELECT COUNT_BIG(*) FROM @submit_state) <> 1
OR NOT EXISTS (SELECT 1 FROM @submit_state WHERE can_submit = 1)
BEGIN
RAISERROR(N'leave_submit_not_allowed', 16, 1);
RETURN;
END;
DECLARE @submit_employee_id INT;
DECLARE @submit_record_count BIGINT;
SELECT
@submit_record_count = COUNT_BIG(*),
@submit_employee_id = MAX(hr_ela_empid),
@effective_record_id = MAX(CONVERT(NVARCHAR(50), hr_ela_no))
FROM dbo.HR_EmpLeaveAloneTab WITH (UPDLOCK, HOLDLOCK)
WHERE CONVERT(NVARCHAR(32), hr_ela_id) = @record_id
OR CONVERT(NVARCHAR(50), hr_ela_no) = @record_id;
IF @submit_record_count <> 1
OR @submit_employee_id <> @current_employee_id
OR NULLIF(@effective_record_id, N'') IS NULL
BEGIN
RAISERROR(N'leave_submit_record_scope_invalid', 16, 1);
RETURN;
END;
DECLARE @apply_message NVARCHAR(4000);
DECLARE @apply_return INT;
SET @apply_message = N'';
EXEC @apply_return = dbo.p_baseApply
@typeCode = 'hr_4011',
@billDocumentId = @effective_record_id,
@Operatorid = @current_employee_id,
@operatorName = @operator_name,
@comfirmType = 1,
@msg = @apply_message OUTPUT;
IF @apply_return <> 1 OR @@TRANCOUNT < 1 OR XACT_STATE() <> 1
BEGIN
RAISERROR(N'leave_legacy_submit_failed', 16, 1);
RETURN;
END;
IF NOT EXISTS
(
SELECT 1
FROM dbo.HR_EmpLeaveAloneTab AS leave_record WITH (UPDLOCK, HOLDLOCK)
JOIN dbo.p_baseflowOper AS flow_record WITH (UPDLOCK, HOLDLOCK)
ON flow_record.modid = 'hr_4011'
AND flow_record.keyvalue = leave_record.hr_ela_no
AND flow_record.billtype = leave_record.hr_ela_billtype
WHERE leave_record.hr_ela_no = @effective_record_id
AND leave_record.hr_ela_empid = @current_employee_id
)
BEGIN
RAISERROR(N'leave_legacy_submit_evidence_invalid', 16, 1);
RETURN;
END;
SET @result_code = 'leave_submitted';
SET @result_message = N'请假申请已通过 ERP 原审批流提交。';
END;
DECLARE @guid_text VARCHAR(32);
DECLARE @transaction_evidence_id VARCHAR(128);
DECLARE @business_audit_id VARCHAR(128);
DECLARE @outbox_event_id VARCHAR(128);
SET @guid_text = LOWER(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''));
SET @transaction_evidence_id = 'tx-leave-' + @guid_text;
SET @business_audit_id = 'audit-leave-' + @guid_text;
SET @outbox_event_id = 'outbox-leave-' + @guid_text;
INSERT INTO dbo.p_agent_business_audit
(
business_audit_id, transaction_evidence_id, correlation_id,
idempotency_id, account_book, subsystem_id, user_id,
command_name, module_code, action_name, record_id,
input_fingerprint
)
VALUES
(
@business_audit_id, @transaction_evidence_id, @correlation_id,
@idempotency_id, @account_book, @subsystem_id, @user_id,
@command_name, @module_code, @action, @effective_record_id,
@input_fingerprint
);
INSERT INTO dbo.p_agent_integration_outbox
(
event_id, business_audit_id, correlation_id, account_book,
subsystem_id, module_code, action_name, record_id, status
)
VALUES
(
@outbox_event_id, @business_audit_id, @correlation_id, @account_book,
@subsystem_id, @module_code, @action, @effective_record_id, 0
);
UPDATE dbo.p_agent_command_idempotency
SET status = 1,
result_code = @result_code,
record_id = @effective_record_id,
transaction_evidence_id = @transaction_evidence_id,
business_audit_id = @business_audit_id,
completed_at_utc = SYSUTCDATETIME()
WHERE id = @idempotency_id AND status = 0;
IF @@ROWCOUNT <> 1
BEGIN
RAISERROR(N'leave_idempotency_completion_failed', 16, 1);
RETURN;
END;
SELECT
CONVERT(BIT, 1) AS success,
@result_code AS code,
@result_message AS message,
@effective_record_id AS record_id,
CONVERT(BIT, 0) AS needs_ui,
CONVERT(BIT, 0) AS idempotency_replayed,
@idempotency_key AS applied_idempotency_key,
@input_fingerprint AS applied_input_fingerprint,
@transaction_evidence_id AS transaction_evidence_id,
@business_audit_id AS business_audit_id;
END;
GO
Reference in New Issue View Git Blame Copy Permalink