/* Customer-specific review draft for acc_1007 purchase-invoice 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 and @customer_row_scope_reviewed are fixed to 0. 3. The trusted ERP gateway has a fixed route for this exact contract, but normal command registration remains unavailable while any database readiness or TrustedPeople-signed acceptance requirement is missing. 4. A customer DBA must generate and sign a separate deployment script after the currency field, currency crosswalk, procurement row scope and Windows client integration evidence have all been approved. Even after registration, the two review switches below independently reject every call while this file remains a draft. The wrapper accepts fixed scalars and two schema-checked XML rowsets emitted by XmlWriter in the trusted ERP process. It never accepts a table name, column name, procedure name, SQL fragment or model-generated XML. It locks and rechecks the purchase-order source, then calls the same P_BillSavePr70 chain used by BillModule. That chain creates an unsubmitted acc_1007 draft and performs the configured ERP save side effects. Submission/approval is intentionally outside this command. Expected supporting objects: - p_agent_command_idempotency, p_agent_business_audit, p_agent_business_source_document and p_agent_integration_outbox from 001_agent_business_idempotency.sql; - p_agent_purchase_currency_crosswalk populated only by a customer DBA after a signed finance review. The schema is shipped empty and never guesses mappings. - p_agent_purchase_row_scope populated only with exact account/subsystem/ERP-user + organization/department/purchaser tuples after signed procurement review. The schema is shipped empty and has no wildcard grant. The legacy save procedure may emit diagnostic result sets. SqlWorkflowProcedureGateway discards them and accepts exactly one result set whose ten columns exactly match the BusinessWriteResult contract. */ SET NOEXEC ON; GO CREATE PROCEDURE dbo.p_lserp_agent_workflow_write_purchase_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), @supplier_code NVARCHAR(64), @currency_code NVARCHAR(64), @invoice_number NVARCHAR(128), @invoice_date DATETIME, @total_without_tax DECIMAL(28, 8), @tax_amount DECIMAL(28, 8), @total_with_tax DECIMAL(28, 8), @lines_xml XML, @source_documents_xml XML AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @customer_dba_reviewed BIT; DECLARE @customer_row_scope_reviewed BIT; SET @customer_dba_reviewed = 0; SET @customer_row_scope_reviewed = 0; IF @customer_dba_reviewed <> 1 BEGIN RAISERROR(N'customer_dba_review_required', 16, 1); RETURN; END; IF @customer_row_scope_reviewed <> 1 BEGIN RAISERROR(N'purchase_row_scope_review_required', 16, 1); RETURN; END; IF DB_NAME() <> N'lserp_AI' OR @action <> 'create_document' OR @module_code <> N'acc_1007' OR @subsystem_id <> N'5' 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'purchase_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'purchase_write_evidence_invalid', 16, 1); RETURN; END; SET @supplier_code = NULLIF(LTRIM(RTRIM(@supplier_code)), N''); SET @currency_code = NULLIF(LTRIM(RTRIM(@currency_code)), N''); SET @invoice_number = NULLIF(LTRIM(RTRIM(@invoice_number)), N''); IF @supplier_code IS NULL OR LEN(@supplier_code) > 10 OR @supplier_code LIKE N'%[^0-9]%' OR @currency_code IS NULL OR LEN(@currency_code) > 10 OR @currency_code LIKE N'%[^0-9]%' OR @invoice_number IS NULL OR LEN(@invoice_number) > 128 OR @invoice_date IS NULL OR @invoice_date < '19000101' OR @invoice_date >= '21010101' OR CONVERT(TIME, @invoice_date) <> CONVERT(TIME, '00:00:00') OR @total_without_tax < 0 OR @tax_amount < 0 OR @total_with_tax <= 0 OR ROUND(@total_without_tax, 2) <> @total_without_tax OR ROUND(@tax_amount, 2) <> @tax_amount OR ROUND(@total_with_tax, 2) <> @total_with_tax OR ABS(ROUND(@total_without_tax + @tax_amount, 2) - @total_with_tax) > 0.05 BEGIN RAISERROR(N'purchase_header_input_invalid', 16, 1); RETURN; END; DECLARE @supplier_number DECIMAL(10, 0); DECLARE @source_currency_number DECIMAL(10, 0); DECLARE @supplier_id INT; DECLARE @source_currency_id INT; SET @supplier_number = CONVERT(DECIMAL(10, 0), @supplier_code); SET @source_currency_number = CONVERT(DECIMAL(10, 0), @currency_code); IF @supplier_number < 1 OR @supplier_number > 2147483647 OR @source_currency_number < 1 OR @source_currency_number > 2147483647 BEGIN RAISERROR(N'purchase_header_identity_invalid', 16, 1); RETURN; END; SET @supplier_id = CONVERT(INT, @supplier_number); SET @source_currency_id = CONVERT(INT, @source_currency_number); IF @lines_xml IS NULL OR DATALENGTH(CONVERT(NVARCHAR(MAX), @lines_xml)) > 1048576 OR @lines_xml.value('count(/lines)', 'int') <> 1 OR @lines_xml.exist('/lines[@version="1"]') <> 1 OR @lines_xml.value('count(/lines/@*)', 'int') <> 1 OR @lines_xml.value('count(/lines/line)', 'int') < 1 OR @lines_xml.value('count(/lines/line)', 'int') > 200 OR @lines_xml.value('count(/lines/*)', 'int') <> @lines_xml.value('count(/lines/line)', 'int') OR @lines_xml.exist('/lines/line/*') = 1 OR @lines_xml.value('count(/lines/text())', 'int') <> 0 OR @lines_xml.value('count(/lines/line/text())', 'int') <> 0 BEGIN RAISERROR(N'purchase_lines_xml_invalid', 16, 1); RETURN; END; IF EXISTS ( SELECT 1 FROM @lines_xml.nodes('/lines/line') AS parsed(line_node) WHERE parsed.line_node.value('count(@*)', 'int') <> 16 ) BEGIN RAISERROR(N'purchase_lines_xml_shape_invalid', 16, 1); RETURN; END; IF @source_documents_xml IS NULL OR DATALENGTH(CONVERT(NVARCHAR(MAX), @source_documents_xml)) > 1048576 OR @source_documents_xml.value('count(/source_documents)', 'int') <> 1 OR @source_documents_xml.exist('/source_documents[@version="3"]') <> 1 OR @source_documents_xml.value('count(/source_documents/@*)', 'int') <> 1 OR @source_documents_xml.value('count(/source_documents/document)', 'int') > 3 OR @source_documents_xml.value('count(/source_documents/*)', 'int') <> @source_documents_xml.value('count(/source_documents/document)', 'int') OR @source_documents_xml.exist('/source_documents/document/*') = 1 OR @source_documents_xml.value('count(/source_documents/text())', 'int') <> 0 OR @source_documents_xml.value( 'count(/source_documents/document/text())', 'int') <> 0 BEGIN RAISERROR(N'purchase_source_documents_xml_invalid', 16, 1); RETURN; END; IF EXISTS ( SELECT 1 FROM @source_documents_xml.nodes('/source_documents/document') AS parsed(document_node) WHERE parsed.document_node.value('count(@*)', 'int') <> 6 ) BEGIN RAISERROR(N'purchase_source_documents_xml_shape_invalid', 16, 1); RETURN; END; DECLARE @requested_lines TABLE ( row_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, line_id NVARCHAR(4000) NOT NULL, material_code NVARCHAR(4000) NOT NULL, unit_name NVARCHAR(4000) NOT NULL, quantity DECIMAL(28, 8) NOT NULL, unit_price DECIMAL(28, 8) NOT NULL, tax_rate DECIMAL(28, 8) NOT NULL, line_tax_amount DECIMAL(28, 8) NOT NULL, line_amount DECIMAL(28, 8) NOT NULL, source_order_id NVARCHAR(4000) NOT NULL, source_order_number NVARCHAR(4000) NOT NULL, source_line_id INT NOT NULL, source_unit NVARCHAR(4000) NOT NULL, source_remaining_quantity DECIMAL(28, 8) NOT NULL, source_unit_price DECIMAL(28, 8) NOT NULL, source_tax_rate DECIMAL(28, 8) NOT NULL, source_exchange_rate DECIMAL(28, 8) NOT NULL ); INSERT INTO @requested_lines ( line_id, material_code, unit_name, quantity, unit_price, tax_rate, line_tax_amount, line_amount, source_order_id, source_order_number, source_line_id, source_unit, source_remaining_quantity, source_unit_price, source_tax_rate, source_exchange_rate ) SELECT parsed.line_node.value('(@line_id)[1]', 'nvarchar(4000)'), parsed.line_node.value('(@material_code)[1]', 'nvarchar(4000)'), parsed.line_node.value('(@unit)[1]', 'nvarchar(4000)'), parsed.line_node.value('(@quantity)[1]', 'decimal(28,8)'), parsed.line_node.value('(@unit_price)[1]', 'decimal(28,8)'), parsed.line_node.value('(@tax_rate)[1]', 'decimal(28,8)'), parsed.line_node.value('(@tax_amount)[1]', 'decimal(28,8)'), parsed.line_node.value('(@line_amount)[1]', 'decimal(28,8)'), parsed.line_node.value('(@source_order_id)[1]', 'nvarchar(4000)'), parsed.line_node.value('(@source_order_number)[1]', 'nvarchar(4000)'), parsed.line_node.value('(@source_line_id)[1]', 'int'), parsed.line_node.value('(@source_unit)[1]', 'nvarchar(4000)'), parsed.line_node.value('(@source_remaining_quantity)[1]', 'decimal(28,8)'), parsed.line_node.value('(@source_unit_price)[1]', 'decimal(28,8)'), parsed.line_node.value('(@source_tax_rate)[1]', 'decimal(28,8)'), parsed.line_node.value('(@source_exchange_rate)[1]', 'decimal(28,8)') FROM @lines_xml.nodes('/lines/line') AS parsed(line_node); DECLARE @requested_documents TABLE ( document_kind NVARCHAR(4000) NOT NULL, sanitized_filename NVARCHAR(4000) NOT NULL, source_sha256 NVARCHAR(4000) NOT NULL, extraction_sha256 NVARCHAR(4000) NOT NULL, preprocess_contract NVARCHAR(4000) NOT NULL, size_bytes BIGINT NOT NULL ); INSERT INTO @requested_documents (document_kind, sanitized_filename, source_sha256, extraction_sha256, preprocess_contract, size_bytes) SELECT parsed.document_node.value('(@kind)[1]', 'nvarchar(4000)'), parsed.document_node.value('(@filename)[1]', 'nvarchar(4000)'), parsed.document_node.value('(@sha256)[1]', 'nvarchar(4000)'), parsed.document_node.value( '(@extraction_sha256)[1]', 'nvarchar(4000)'), parsed.document_node.value( '(@preprocess_contract)[1]', 'nvarchar(4000)'), parsed.document_node.value('(@size_bytes)[1]', 'bigint') FROM @source_documents_xml.nodes('/source_documents/document') AS parsed(document_node); IF (SELECT COUNT_BIG(*) FROM @requested_lines) < 1 OR (SELECT COUNT_BIG(*) FROM @requested_lines) > 200 OR EXISTS ( SELECT line_id FROM @requested_lines GROUP BY line_id HAVING COUNT_BIG(*) <> 1 ) OR EXISTS ( SELECT 1 FROM @requested_lines WHERE NULLIF(LTRIM(RTRIM(line_id)), N'') IS NULL OR LEN(line_id) > 128 OR NULLIF(LTRIM(RTRIM(material_code)), N'') IS NULL OR LEN(material_code) > 50 OR NULLIF(LTRIM(RTRIM(unit_name)), N'') IS NULL OR LEN(unit_name) > 50 OR NULLIF(LTRIM(RTRIM(source_order_id)), N'') IS NULL OR LEN(source_order_id) > 50 OR NULLIF(LTRIM(RTRIM(source_order_number)), N'') IS NULL OR LEN(source_order_number) > 50 OR source_line_id <= 0 OR NULLIF(LTRIM(RTRIM(source_unit)), N'') IS NULL OR LEN(source_unit) > 50 OR quantity <= 0 OR quantity > 1000000000 OR source_remaining_quantity <= 0 OR source_remaining_quantity > 1000000000 OR unit_price <= 0 OR unit_price > 1000000000000 OR source_unit_price <= 0 OR source_unit_price > 1000000000000 OR tax_rate < 0 OR tax_rate > 1 OR source_tax_rate < 0 OR source_tax_rate > 1 OR source_exchange_rate <= 0 OR source_exchange_rate > 1000000000 OR line_tax_amount < 0 OR line_amount <= 0 OR ROUND(quantity, 2) <> quantity OR ROUND(source_remaining_quantity, 2) <> source_remaining_quantity OR ROUND(unit_price, 2) <> unit_price OR ROUND(source_unit_price, 2) <> source_unit_price OR ROUND(tax_rate, 2) <> tax_rate OR ROUND(source_tax_rate, 2) <> source_tax_rate OR ROUND(source_exchange_rate, 2) <> source_exchange_rate OR ROUND(line_tax_amount, 2) <> line_tax_amount OR ROUND(line_amount, 2) <> line_amount OR quantity > source_remaining_quantity OR unit_name <> source_unit OR ABS(unit_price - source_unit_price) > 0.01 OR ABS(tax_rate - source_tax_rate) > 0.0001 OR ABS(ROUND(quantity * unit_price, 2) - line_amount) > 0.02 OR ABS(ROUND(line_amount / (1 + tax_rate), 2) - (line_amount - line_tax_amount)) > 0.02 ) BEGIN RAISERROR(N'purchase_line_contract_invalid', 16, 1); RETURN; END; IF (SELECT COUNT_BIG(DISTINCT source_exchange_rate) FROM @requested_lines) <> 1 OR ABS(ROUND((SELECT SUM(line_amount - line_tax_amount) FROM @requested_lines), 2) - @total_without_tax) > 0.05 OR ABS(ROUND((SELECT SUM(line_tax_amount) FROM @requested_lines), 2) - @tax_amount) > 0.05 OR ABS(ROUND((SELECT SUM(line_amount) FROM @requested_lines), 2) - @total_with_tax) > 0.05 BEGIN RAISERROR(N'purchase_amount_or_exchange_contract_invalid', 16, 1); RETURN; END; IF EXISTS ( SELECT 1 FROM @requested_documents WHERE document_kind NOT IN (N'image', N'file') OR LEN(sanitized_filename) < 1 OR LEN(sanitized_filename) > 128 OR sanitized_filename IN (N'.', N'..') OR sanitized_filename LIKE N'%/%' OR sanitized_filename LIKE N'%\%' OR LEN(source_sha256) <> 64 OR source_sha256 COLLATE Latin1_General_100_BIN2 LIKE N'%[^0-9a-f]%' OR LEN(extraction_sha256) <> 64 OR extraction_sha256 COLLATE Latin1_General_100_BIN2 LIKE N'%[^0-9a-f]%' OR ( NOT ( document_kind COLLATE Latin1_General_100_BIN2 = N'image' AND preprocess_contract COLLATE Latin1_General_100_BIN2 = N'minimax_vlm_0.0.4' AND ( LOWER(RIGHT(sanitized_filename, 4)) IN (N'.png', N'.jpg') OR LOWER(RIGHT(sanitized_filename, 5)) IN (N'.jpeg', N'.webp') ) ) AND NOT ( document_kind COLLATE Latin1_General_100_BIN2 = N'file' AND ( (LOWER(RIGHT(sanitized_filename, 4)) = N'.pdf' AND preprocess_contract COLLATE Latin1_General_100_BIN2 = N'pdfium_minimax_pages_v1') OR (LOWER(RIGHT(sanitized_filename, 4)) = N'.csv' AND preprocess_contract COLLATE Latin1_General_100_BIN2 = N'document_sandbox_csv_v1') OR (LOWER(RIGHT(sanitized_filename, 5)) = N'.xlsx' AND preprocess_contract COLLATE Latin1_General_100_BIN2 = N'document_sandbox_xlsx_v1') ) ) ) OR size_bytes < 1 OR size_bytes > 12582912 ) OR EXISTS ( SELECT source_sha256 FROM @requested_documents GROUP BY source_sha256 HAVING COUNT_BIG(*) <> 1 ) BEGIN RAISERROR(N'purchase_source_document_contract_invalid', 16, 1); RETURN; END; DECLARE @control_code INT; SET @control_code = 1; WHILE @control_code <= 31 BEGIN IF CHARINDEX(NCHAR(@control_code), @invoice_number) > 0 OR EXISTS ( SELECT 1 FROM @requested_lines WHERE CHARINDEX(NCHAR(@control_code), line_id) > 0 OR CHARINDEX(NCHAR(@control_code), material_code) > 0 OR CHARINDEX(NCHAR(@control_code), unit_name) > 0 OR CHARINDEX(NCHAR(@control_code), source_order_id) > 0 OR CHARINDEX(NCHAR(@control_code), source_order_number) > 0 OR CHARINDEX(NCHAR(@control_code), source_unit) > 0 ) OR EXISTS ( SELECT 1 FROM @requested_documents WHERE CHARINDEX(NCHAR(@control_code), sanitized_filename) > 0 ) BEGIN RAISERROR(N'purchase_control_character_invalid', 16, 1); RETURN; END; SET @control_code = @control_code + 1; END; IF CHARINDEX(NCHAR(127), @invoice_number) > 0 OR EXISTS ( SELECT 1 FROM @requested_lines WHERE CHARINDEX(NCHAR(127), line_id + material_code + unit_name + source_order_id + source_order_number + source_unit) > 0 ) OR EXISTS ( SELECT 1 FROM @requested_documents WHERE CHARINDEX(NCHAR(127), sanitized_filename) > 0 ) BEGIN RAISERROR(N'purchase_control_character_invalid', 16, 1); RETURN; END; IF LEN(@user_id) > 10 OR @user_id LIKE N'%[^0-9]%' BEGIN RAISERROR(N'erp_employee_context_invalid', 16, 1); RETURN; END; DECLARE @user_number DECIMAL(10, 0); DECLARE @operator_id INT; DECLARE @operator_name NVARCHAR(50); SET @user_number = CONVERT(DECIMAL(10, 0), @user_id); IF @user_number < 1 OR @user_number > 2147483647 BEGIN RAISERROR(N'erp_employee_context_invalid', 16, 1); RETURN; END; SET @operator_id = CONVERT(INT, @user_number); SELECT @operator_name = CONVERT(NVARCHAR(50), EmployeeName) FROM dbo.p_employeetab WITH (UPDLOCK, HOLDLOCK) WHERE employeeid = @operator_id AND ISNULL(p_emp_status, '') <> N'离职'; IF @operator_name IS NULL BEGIN RAISERROR(N'erp_employee_context_invalid', 16, 1); RETURN; END; DECLARE @permission_text NVARCHAR(MAX); SET @permission_text = N','; SELECT TOP (1) @permission_text = N',' + CONVERT(NVARCHAR(MAX), ISNULL(scmPurview, '')) + N',' + CONVERT(NVARCHAR(MAX), ISNULL(accPurview, '')) + N',' FROM dbo.p_SubsysPurviewTab WITH (UPDLOCK, HOLDLOCK) WHERE employeeid = @operator_id; /* Target menu 18848 requires edit permission. Source menu 187 may be read-only. */ IF (@operator_id <> 1 OR @operator_name <> N'管理员') AND ( CHARINDEX(N',18848,', @permission_text) = 0 OR (CHARINDEX(N',187,', @permission_text) = 0 AND CHARINDEX(N',187|,', @permission_text) = 0) ) BEGIN RAISERROR(N'purchase_write_permission_denied', 16, 1); RETURN; 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_business_source_document', N'U') IS NULL OR OBJECT_ID(N'dbo.p_agent_integration_outbox', N'U') IS NULL OR OBJECT_ID(N'dbo.p_agent_purchase_currency_crosswalk', N'U') IS NULL OR OBJECT_ID(N'dbo.p_agent_purchase_row_scope', N'U') IS NULL OR COL_LENGTH(N'dbo.p_agent_business_source_document', N'extraction_sha256') IS NULL OR COL_LENGTH(N'dbo.p_agent_business_source_document', N'preprocess_contract') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_currency_crosswalk', N'source_currency_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_currency_crosswalk', N'target_currency_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_currency_crosswalk', N'is_active') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_currency_crosswalk', N'approval_evidence_sha256') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'account_book') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'subsystem_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'erp_user_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'group_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'department_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'purchase_user_id') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'is_active') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'approval_evidence_sha256') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'valid_from_utc') IS NULL OR COL_LENGTH(N'dbo.p_agent_purchase_row_scope', N'valid_to_utc') IS NULL OR NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.p_agent_purchase_currency_crosswalk') AND name = N'UX_p_agent_purchase_currency_active' AND is_unique = 1 AND has_filter = 1 AND is_disabled = 0 ) OR NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.p_agent_purchase_row_scope') AND name = N'UX_p_agent_purchase_row_scope_active' AND is_unique = 1 AND has_filter = 1 AND is_disabled = 0 ) BEGIN RAISERROR(N'purchase_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:purchase:' + LEFT(@account_book, 40) + N':' + LEFT(@user_id, 10) + 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'purchase_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 = 'purchase.invoice.create' 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, 'purchase.invoice.create', @idempotency_key, @input_fingerprint, 0 ); SET @idempotency_id = CONVERT(BIGINT, SCOPE_IDENTITY()); IF NOT EXISTS ( SELECT 1 FROM dbo.p_systembillInfo WITH (UPDLOCK, HOLDLOCK) WHERE typeCode = 'acc_1007' AND fieldName = 'acc_mphhscm_currency' ) BEGIN RAISERROR(N'purchase_currency_field_not_configured', 16, 1); RETURN; END; DECLARE @target_currency_id INT; DECLARE @currency_mapping_count BIGINT; SELECT @currency_mapping_count = COUNT_BIG(*), @target_currency_id = MAX(target_currency_id) FROM dbo.p_agent_purchase_currency_crosswalk WITH (UPDLOCK, HOLDLOCK) WHERE source_currency_id = @source_currency_id AND is_active = 1 AND LEN(approval_evidence_sha256) = 64 AND approval_evidence_sha256 COLLATE Latin1_General_100_BIN2 NOT LIKE '%[^0-9a-f]%'; IF @currency_mapping_count <> 1 OR @target_currency_id IS NULL OR NOT EXISTS ( SELECT 1 FROM dbo.P_CurrencyType WITH (UPDLOCK, HOLDLOCK) WHERE id = @source_currency_id AND ISNULL(is_active, 1) = 1 ) OR NOT EXISTS ( SELECT 1 FROM dbo.P_BaseMixInfoTab WITH (UPDLOCK, HOLDLOCK) WHERE ID = @target_currency_id AND Tag = 'L000101' AND ISNULL(Ban, 0) = 0 AND ISNULL(Delid, 0) = 0 ) BEGIN RAISERROR(N'purchase_currency_crosswalk_not_approved', 16, 1); RETURN; END; DECLARE @flow_type_id INT; DECLARE @flow_type_count BIGINT; SELECT @flow_type_count = COUNT_BIG(*), @flow_type_id = MAX(id) FROM dbo.p_systembillflowtype WITH (UPDLOCK, HOLDLOCK) WHERE typeCode = 'acc_1007' AND ISNULL(billflowban, 0) = 0 AND CHARINDEX(N',' + @operator_name + N',', N',' + CONVERT(NVARCHAR(500), ISNULL(operUser, ''))) > 0; IF @flow_type_count <> 1 OR @flow_type_id IS NULL BEGIN RAISERROR(N'purchase_flow_type_not_available', 16, 1); RETURN; END; IF NOT EXISTS ( SELECT 1 FROM dbo.p_providertab WITH (UPDLOCK, HOLDLOCK) WHERE id = @supplier_id AND ISNULL(IsDisabled, 0) = 0 AND ISNULL(ban, 0) = 0 AND ISNULL(cancelFlag, 0) = 0 ) BEGIN RAISERROR(N'purchase_supplier_not_available', 16, 1); RETURN; END; DECLARE @authoritative TABLE ( row_id INT NOT NULL PRIMARY KEY, source_line_id INT NOT NULL, source_order_id VARCHAR(50) NOT NULL, source_order_number VARCHAR(50) NOT NULL, supplier_id INT NOT NULL, source_currency_text VARCHAR(20) NOT NULL, product_code VARCHAR(50) NOT NULL, appellation VARCHAR(200) NULL, specification VARCHAR(200) NULL, model_name VARCHAR(200) NULL, unit_name VARCHAR(50) NOT NULL, requested_quantity DECIMAL(28, 8) NOT NULL, remaining_quantity DECIMAL(28, 8) NOT NULL, unit_price DECIMAL(28, 8) NOT NULL, tax_rate DECIMAL(28, 8) NOT NULL, exchange_rate DECIMAL(28, 8) NOT NULL, group_text VARCHAR(50) NULL, department_text VARCHAR(50) NULL, purchase_user_id INT NULL, invoice_type_id INT NULL, settlement_text VARCHAR(50) NULL, purchase_request_line_id INT NULL ); ;WITH reserved AS ( SELECT invoice_line.acc_lphhscm_ScmPoid AS source_line_id, SUM(ISNULL(invoice_line.acc_lphhscm_amount, 0)) AS reserved_quantity FROM dbo.ACC_billscmInvoicelistPIDHxtab AS invoice_line WITH (UPDLOCK, HOLDLOCK) INNER JOIN dbo.ACC_billscmInvoicemainPIDHxtab AS invoice_main WITH (UPDLOCK, HOLDLOCK) ON invoice_main.acc_mphhscm_billdocument_id = invoice_line.acc_lphhscm_billdocument_id WHERE invoice_line.acc_lphhscm_ScmPoid IS NOT NULL AND ISNULL(invoice_main.acc_mphhscm_cancelFlag, 0) = 0 AND ISNULL(invoice_main.acc_mphhscm_wasteoper, 0) = 0 AND ISNULL(invoice_main.acc_mphhscm_delid, 0) = 0 GROUP BY invoice_line.acc_lphhscm_ScmPoid ) INSERT INTO @authoritative ( row_id, source_line_id, source_order_id, source_order_number, supplier_id, source_currency_text, product_code, appellation, specification, model_name, unit_name, requested_quantity, remaining_quantity, unit_price, tax_rate, exchange_rate, group_text, department_text, purchase_user_id, invoice_type_id, settlement_text, purchase_request_line_id ) SELECT requested.row_id, order_line.scm_lpo_id, order_line.scm_lpo_Billdocument_id, COALESCE(NULLIF(LTRIM(RTRIM(order_main.scm_mpo_handcraftId)), ''), order_line.scm_lpo_Billdocument_id), order_main.scm_mpo_Providerid, order_main.scm_mpo_Currency, order_line.scm_lpo_Productid, order_line.scm_lpo_appellation, order_line.scm_lpo_spec, order_line.scm_lpo_model, order_line.scm_lpo_ProductUnitName, requested.quantity, CONVERT(DECIMAL(28, 8), ISNULL(order_line.scm_lpo_Amount, 0) - ISNULL(reserved.reserved_quantity, 0)), CONVERT(DECIMAL(28, 8), ISNULL(order_line.scm_lpo_Fprice, 0)), CONVERT(DECIMAL(28, 8), ISNULL(order_line.scm_lpo_taxRate, 0)), CONVERT(DECIMAL(28, 8), COALESCE( NULLIF(order_line.scm_lpo_ExchangeRate, 0), order_main.scm_mpo_ExchangeRate, 0)), order_main.scm_mpo_Groupid, order_main.scm_mpo_Departmentid, order_main.scm_mpo_PurchaseUserId, order_main.scm_mpo_InvoiceType, order_main.scm_mpo_SettlementType, order_line.scm_lpo_ScmPrid FROM @requested_lines AS requested INNER JOIN dbo.scm_BillPolistTab AS order_line WITH (UPDLOCK, HOLDLOCK) ON order_line.scm_lpo_id = requested.source_line_id AND CONVERT(NVARCHAR(50), order_line.scm_lpo_Billdocument_id) = requested.source_order_id INNER JOIN dbo.scm_BillPoMainTab AS order_main WITH (UPDLOCK, HOLDLOCK) ON order_main.scm_mpo_Billdocument_Id = order_line.scm_lpo_Billdocument_id LEFT JOIN reserved ON reserved.source_line_id = order_line.scm_lpo_id WHERE order_main.scm_mpo_Providerid = @supplier_id AND LTRIM(RTRIM(order_main.scm_mpo_Currency)) = CONVERT(VARCHAR(20), @source_currency_id) AND ISNULL(order_main.scm_mpo_stepover, 0) = 1 AND ISNULL(order_main.scm_mpo_cancelFlag, 0) = 0 AND ISNULL(order_main.scm_mpo_wasteoper, 0) = 0 AND ISNULL(order_main.scm_mpo_ban, 0) = 0; IF (SELECT COUNT_BIG(*) FROM @authoritative) <> (SELECT COUNT_BIG(*) FROM @requested_lines) OR EXISTS ( SELECT 1 FROM @authoritative AS actual INNER JOIN @requested_lines AS requested ON requested.row_id = actual.row_id WHERE CONVERT(NVARCHAR(50), actual.source_order_number) <> requested.source_order_number OR CONVERT(NVARCHAR(50), actual.product_code) <> requested.material_code OR CONVERT(NVARCHAR(50), actual.unit_name) <> requested.unit_name OR CONVERT(NVARCHAR(50), actual.unit_name) <> requested.source_unit OR actual.remaining_quantity <= 0 OR ABS(actual.remaining_quantity - requested.source_remaining_quantity) > 0.0001 OR ABS(actual.unit_price - requested.source_unit_price) > 0.01 OR ABS(actual.unit_price - requested.unit_price) > 0.01 OR ABS(actual.tax_rate - requested.source_tax_rate) > 0.0001 OR ABS(actual.tax_rate - requested.tax_rate) > 0.0001 OR ABS(actual.exchange_rate - requested.source_exchange_rate) > 0.000001 ) OR EXISTS ( SELECT source_line_id FROM @authoritative GROUP BY source_line_id HAVING SUM(requested_quantity) > MAX(remaining_quantity) + 0.0001 ) OR EXISTS ( SELECT 1 FROM @authoritative WHERE DATALENGTH(ISNULL(appellation, '')) > 100 OR DATALENGTH(ISNULL(specification, '')) > 100 OR DATALENGTH(ISNULL(model_name, '')) > 100 ) BEGIN RAISERROR(N'purchase_source_changed_or_invalid', 16, 1); RETURN; END; IF EXISTS ( SELECT 1 FROM @authoritative WHERE NULLIF(LTRIM(RTRIM(group_text)), '') IS NULL OR group_text LIKE '%[^0-9]%' OR LEN(group_text) > 10 OR NULLIF(LTRIM(RTRIM(department_text)), '') IS NULL OR department_text LIKE '%[^0-9]%' OR LEN(department_text) > 10 OR purchase_user_id IS NULL OR purchase_user_id <= 0 OR invoice_type_id IS NULL OR invoice_type_id <= 0 OR NULLIF(LTRIM(RTRIM(settlement_text)), '') IS NULL OR settlement_text LIKE '%[^0-9]%' OR LEN(settlement_text) > 10 ) OR (SELECT COUNT_BIG(DISTINCT group_text) FROM @authoritative) <> 1 OR (SELECT COUNT_BIG(DISTINCT department_text) FROM @authoritative) <> 1 OR (SELECT COUNT_BIG(DISTINCT purchase_user_id) FROM @authoritative) <> 1 OR (SELECT COUNT_BIG(DISTINCT invoice_type_id) FROM @authoritative) <> 1 OR (SELECT COUNT_BIG(DISTINCT settlement_text) FROM @authoritative) <> 1 OR (SELECT COUNT_BIG(DISTINCT exchange_rate) FROM @authoritative) <> 1 BEGIN RAISERROR(N'purchase_source_header_conflict', 16, 1); RETURN; END; DECLARE @group_id INT; DECLARE @department_id INT; DECLARE @purchase_user_id INT; DECLARE @invoice_type_id INT; DECLARE @settlement_type_id INT; DECLARE @exchange_rate DECIMAL(18, 8); SELECT TOP (1) @group_id = CONVERT(INT, CONVERT(DECIMAL(10, 0), group_text)), @department_id = CONVERT(INT, CONVERT(DECIMAL(10, 0), department_text)), @purchase_user_id = purchase_user_id, @invoice_type_id = invoice_type_id, @settlement_type_id = CONVERT(INT, CONVERT(DECIMAL(10, 0), settlement_text)), @exchange_rate = exchange_rate FROM @authoritative; IF @group_id <= 0 OR @department_id <= 0 OR @purchase_user_id <= 0 OR @settlement_type_id <= 0 OR @exchange_rate <= 0 OR NOT EXISTS ( SELECT 1 FROM dbo.P_GroupTab WITH (UPDLOCK, HOLDLOCK) WHERE Groupid = @group_id ) OR NOT EXISTS ( SELECT 1 FROM dbo.p_departmenttab WITH (UPDLOCK, HOLDLOCK) WHERE departmentid = @department_id ) OR NOT EXISTS ( SELECT 1 FROM dbo.P_InvoiceTypeTax WITH (UPDLOCK, HOLDLOCK) WHERE id = @invoice_type_id AND is_active = 1 ) OR NOT EXISTS ( SELECT 1 FROM dbo.P_SettlementTermsTab WITH (UPDLOCK, HOLDLOCK) WHERE st_id = @settlement_type_id AND is_active = 1 ) BEGIN RAISERROR(N'purchase_source_master_data_invalid', 16, 1); RETURN; END; /* Menu permission is necessary but not sufficient. Every accepted source order must resolve to one exact, currently active, signed procurement row-scope tuple. The shipped scope table is empty and supports no wildcard rows. */ IF NOT EXISTS ( SELECT 1 FROM dbo.p_agent_purchase_row_scope WITH (UPDLOCK, HOLDLOCK) WHERE account_book = @account_book AND subsystem_id = @subsystem_id AND erp_user_id = @user_id AND group_id = @group_id AND department_id = @department_id AND purchase_user_id = @purchase_user_id AND is_active = 1 AND valid_from_utc <= SYSUTCDATETIME() AND (valid_to_utc IS NULL OR valid_to_utc > SYSUTCDATETIME()) AND LEN(approval_evidence_sha256) = 64 AND approval_evidence_sha256 COLLATE Latin1_General_100_BIN2 NOT LIKE '%[^0-9a-f]%' ) BEGIN RAISERROR(N'purchase_row_scope_denied', 16, 1); RETURN; END; IF EXISTS ( SELECT 1 FROM dbo.ACC_billscmInvoicemainPIDHxtab WITH (UPDLOCK, HOLDLOCK) WHERE acc_mphhscm_Providerid = @supplier_id AND CONVERT(NVARCHAR(128), acc_mphhscm_invoice) = @invoice_number AND ISNULL(acc_mphhscm_cancelFlag, 0) = 0 AND ISNULL(acc_mphhscm_wasteoper, 0) = 0 AND ISNULL(acc_mphhscm_delid, 0) = 0 ) OR EXISTS ( SELECT 1 FROM dbo.ACC_billscminvoicemaintab WITH (UPDLOCK, HOLDLOCK) WHERE acc_mscmi_Providerid = @supplier_id AND CONVERT(NVARCHAR(128), acc_mscmi_invoice) = @invoice_number AND ISNULL(acc_mscmi_cancelFlag, 0) = 0 AND ISNULL(acc_mscmi_wasteoper, 0) = 0 ) BEGIN RAISERROR(N'duplicate_invoice', 16, 1); RETURN; END; DECLARE @invoice_number_varchar VARCHAR(128); DECLARE @sign_varchar VARCHAR(20); DECLARE @now_local DATETIME; SET @invoice_number_varchar = CONVERT(VARCHAR(128), @invoice_number); SET @sign_varchar = CONVERT(VARCHAR(20), N'核销生成发票'); SET @now_local = CONVERT(DATETIME, DATEADD(HOUR, 8, SYSUTCDATETIME())); IF CONVERT(NVARCHAR(128), @invoice_number_varchar) <> @invoice_number OR CONVERT(NVARCHAR(20), @sign_varchar) <> N'核销生成发票' OR NOT EXISTS ( SELECT 1 FROM dbo.p_systembillInfo WITH (UPDLOCK, HOLDLOCK) WHERE typeCode = 'acc_1007' AND fieldName = 'acc_mphhscm_sign' AND defaultValue = @sign_varchar ) BEGIN RAISERROR(N'purchase_legacy_text_encoding_invalid', 16, 1); RETURN; END; DECLARE @detail_guid VARCHAR(50); SET @detail_guid = LOWER(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', '')); INSERT INTO dbo.ACC_billscmInvoicelistPIDHxtab_temp ( acc_lphhscm_productid, acc_lphhscm_appellation, acc_lphhscm_spec, acc_lphhscm_model, acc_lphhscm_Productunitname, acc_lphhscm_amount, acc_lphhscm_famount, acc_lphhscm_price, acc_lphhscm_summoney, acc_lphhscm_noprice, acc_lphhscm_nosummoney, acc_lphhscm_taxsummoney, acc_lphhscm_taxRate, acc_lphhscm_exchangeRate, acc_lphhscm_ScmPrid, acc_lphhscm_ScmPoid, acc_lphhscm_ScmJoinid, acc_lphhscm_ScmDzid, acc_lphhscm_sourcebillid, acc_lphhscm_sourcename, acc_lphhscm_lsidx_id, sysstr ) SELECT actual.product_code, actual.appellation, actual.specification, actual.model_name, actual.unit_name, requested.quantity, requested.quantity, requested.unit_price, requested.line_amount, ROUND(requested.unit_price / (1 + requested.tax_rate), 2), requested.line_amount - requested.line_tax_amount, requested.line_tax_amount, requested.tax_rate, requested.source_exchange_rate, actual.purchase_request_line_id, requested.source_line_id, 0, 0, requested.source_order_id, requested.source_order_number, requested.row_id, @detail_guid FROM @requested_lines AS requested INNER JOIN @authoritative AS actual ON actual.row_id = requested.row_id; IF @@ROWCOUNT <> (SELECT COUNT(*) FROM @requested_lines) BEGIN RAISERROR(N'purchase_detail_stage_failed', 16, 1); RETURN; END; DECLARE @main_sql VARCHAR(8000); SET @main_sql = 'INSERT INTO dbo.ACC_billscmInvoicemainPIDHxtab ' + '(acc_mphhscm_billdocument_id,acc_mphhscm_groupid,' + 'acc_mphhscm_Departmentid,acc_mphhscm_Providerid,' + 'acc_mphhscm_billtype,acc_mphhscm_InvoiceType,' + 'acc_mphhscm_SettlementType,acc_mphhscm_invoice,' + 'acc_mphhscm_summoney,acc_mphhscm_nosummoney,' + 'acc_mphhscm_taxsummoney,acc_mphhscm_BusinessDate,' + 'acc_mphhscm_invoiceDate,acc_mphhscm_operatorid,' + 'acc_mphhscm_operatedate,acc_mphhscm_currency,' + 'acc_mphhscm_sign) VALUES (' + QUOTENAME('@billcode', '''') + ',' + CONVERT(VARCHAR(20), @group_id) + ',' + CONVERT(VARCHAR(20), @department_id) + ',' + CONVERT(VARCHAR(20), @supplier_id) + ',' + CONVERT(VARCHAR(20), @flow_type_id) + ',' + CONVERT(VARCHAR(20), @invoice_type_id) + ',' + CONVERT(VARCHAR(20), @settlement_type_id) + ',' + QUOTENAME(@invoice_number_varchar, '''') + ',' + CONVERT(VARCHAR(50), CONVERT(DECIMAL(18, 2), @total_with_tax)) + ',' + CONVERT(VARCHAR(50), CONVERT(DECIMAL(18, 2), @total_without_tax)) + ',' + CONVERT(VARCHAR(50), CONVERT(DECIMAL(18, 2), @tax_amount)) + ',' + QUOTENAME(CONVERT(VARCHAR(23), @now_local, 121), '''') + ',' + QUOTENAME(CONVERT(VARCHAR(23), @invoice_date, 121), '''') + ',' + CONVERT(VARCHAR(20), @operator_id) + ',' + QUOTENAME(CONVERT(VARCHAR(23), @now_local, 121), '''') + ',' + CONVERT(VARCHAR(20), @target_currency_id) + ',' + QUOTENAME(@sign_varchar, '''') + ')'; IF @main_sql IS NULL OR LEN(@main_sql) > 8000 BEGIN RAISERROR(N'purchase_legacy_main_contract_invalid', 16, 1); RETURN; END; DECLARE @legacy_message VARCHAR(2000); DECLARE @legacy_return INT; SET @legacy_message = ''; EXEC @legacy_return = dbo.P_BillSavePr70 @Sql = @main_sql, @billdocument_id = '', @tmpstr = @detail_guid, @Operatorid = @operator_id, @Fbilltagid = 1, @operateway = 'acc_1007', @msg = @legacy_message OUTPUT, @auditFlag = 0, @comfirmFlag = 0; IF @legacy_return <> 1 OR @@TRANCOUNT < 1 OR XACT_STATE() <> 1 BEGIN RAISERROR(N'purchase_legacy_create_failed', 16, 1); RETURN; END; DECLARE @effective_record_id NVARCHAR(128); SET @effective_record_id = NULLIF(LTRIM(RTRIM( CONVERT(NVARCHAR(128), @legacy_message))), N''); IF @effective_record_id IS NULL OR EXISTS ( SELECT 1 FROM dbo.ACC_billscmInvoicelistPIDHxtab_temp WHERE sysstr = @detail_guid ) OR (SELECT COUNT_BIG(*) FROM dbo.ACC_billscmInvoicemainPIDHxtab WITH (UPDLOCK, HOLDLOCK) WHERE acc_mphhscm_billdocument_id = @effective_record_id) <> 1 OR NOT EXISTS ( SELECT 1 FROM dbo.ACC_billscmInvoicemainPIDHxtab WITH (UPDLOCK, HOLDLOCK) WHERE acc_mphhscm_billdocument_id = @effective_record_id AND acc_mphhscm_groupid = @group_id AND acc_mphhscm_Departmentid = @department_id AND acc_mphhscm_Providerid = @supplier_id AND acc_mphhscm_billtype = @flow_type_id AND acc_mphhscm_InvoiceType = @invoice_type_id AND acc_mphhscm_SettlementType = @settlement_type_id AND CONVERT(NVARCHAR(128), acc_mphhscm_invoice) = @invoice_number AND ABS(ISNULL(acc_mphhscm_summoney, 0) - @total_with_tax) <= 0.01 AND ABS(ISNULL(acc_mphhscm_nosummoney, 0) - @total_without_tax) <= 0.01 AND ABS(ISNULL(acc_mphhscm_taxsummoney, 0) - @tax_amount) <= 0.01 AND acc_mphhscm_invoiceDate = @invoice_date AND acc_mphhscm_operatorid = @operator_id AND acc_mphhscm_currency = @target_currency_id AND acc_mphhscm_sign = @sign_varchar AND ISNULL(acc_mphhscm_Affirmer, 0) = 0 AND ISNULL(acc_mphhscm_cancelFlag, 0) = 0 AND ISNULL(acc_mphhscm_wasteoper, 0) = 0 AND ISNULL(acc_mphhscm_delid, 0) = 0 ) OR (SELECT COUNT_BIG(*) FROM dbo.ACC_billscmInvoicelistPIDHxtab WITH (UPDLOCK, HOLDLOCK) WHERE acc_lphhscm_billdocument_id = @effective_record_id) <> (SELECT COUNT_BIG(*) FROM @requested_lines) OR EXISTS ( SELECT 1 FROM dbo.ACC_billscmInvoicelistPIDHxtab AS saved WITH (UPDLOCK, HOLDLOCK) WHERE saved.acc_lphhscm_billdocument_id = @effective_record_id AND NOT EXISTS ( SELECT 1 FROM @requested_lines AS requested INNER JOIN @authoritative AS actual ON actual.row_id = requested.row_id WHERE requested.source_line_id = saved.acc_lphhscm_ScmPoid AND requested.source_order_id = saved.acc_lphhscm_sourcebillid AND requested.source_order_number = saved.acc_lphhscm_sourcename AND requested.material_code = saved.acc_lphhscm_productid AND requested.unit_name = saved.acc_lphhscm_Productunitname AND ISNULL(actual.purchase_request_line_id, 0) = ISNULL(saved.acc_lphhscm_ScmPrid, 0) AND ISNULL(saved.acc_lphhscm_ScmJoinid, 0) = 0 AND ISNULL(saved.acc_lphhscm_ScmDzid, 0) = 0 AND ABS(requested.quantity - ISNULL(saved.acc_lphhscm_amount, 0)) <= 0.01 AND ABS(requested.unit_price - ISNULL(saved.acc_lphhscm_price, 0)) <= 0.01 AND ABS(requested.line_amount - ISNULL(saved.acc_lphhscm_summoney, 0)) <= 0.01 AND ABS(requested.line_tax_amount - ISNULL(saved.acc_lphhscm_taxsummoney, 0)) <= 0.01 AND ABS(requested.tax_rate - ISNULL(saved.acc_lphhscm_taxRate, 0)) <= 0.0001 AND ABS(requested.source_exchange_rate - ISNULL(saved.acc_lphhscm_exchangeRate, 0)) <= 0.000001 ) ) OR EXISTS ( SELECT 1 FROM @requested_lines AS requested INNER JOIN @authoritative AS actual ON actual.row_id = requested.row_id WHERE NOT EXISTS ( SELECT 1 FROM dbo.ACC_billscmInvoicelistPIDHxtab AS saved WITH (UPDLOCK, HOLDLOCK) WHERE saved.acc_lphhscm_billdocument_id = @effective_record_id AND saved.acc_lphhscm_ScmPoid = requested.source_line_id AND saved.acc_lphhscm_sourcebillid = requested.source_order_id AND saved.acc_lphhscm_sourcename = requested.source_order_number AND saved.acc_lphhscm_productid = requested.material_code AND saved.acc_lphhscm_Productunitname = requested.unit_name AND ISNULL(saved.acc_lphhscm_ScmPrid, 0) = ISNULL(actual.purchase_request_line_id, 0) AND ISNULL(saved.acc_lphhscm_ScmJoinid, 0) = 0 AND ISNULL(saved.acc_lphhscm_ScmDzid, 0) = 0 AND ABS(ISNULL(saved.acc_lphhscm_amount, 0) - requested.quantity) <= 0.01 AND ABS(ISNULL(saved.acc_lphhscm_price, 0) - requested.unit_price) <= 0.01 AND ABS(ISNULL(saved.acc_lphhscm_summoney, 0) - requested.line_amount) <= 0.01 AND ABS(ISNULL(saved.acc_lphhscm_taxsummoney, 0) - requested.line_tax_amount) <= 0.01 AND ABS(ISNULL(saved.acc_lphhscm_taxRate, 0) - requested.tax_rate) <= 0.0001 AND ABS(ISNULL(saved.acc_lphhscm_exchangeRate, 0) - requested.source_exchange_rate) <= 0.000001 ) ) BEGIN RAISERROR(N'purchase_legacy_create_evidence_invalid', 16, 1); RETURN; 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-purchase-' + @guid_text; SET @business_audit_id = 'audit-purchase-' + @guid_text; SET @outbox_event_id = 'outbox-purchase-' + @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, 'purchase.invoice.create', @module_code, @action, @effective_record_id, @input_fingerprint ); INSERT INTO dbo.p_agent_business_source_document ( business_audit_id, document_kind, sanitized_filename, source_sha256, extraction_sha256, preprocess_contract, size_bytes ) SELECT @business_audit_id, CONVERT(VARCHAR(8), document_kind), CONVERT(NVARCHAR(128), sanitized_filename), CONVERT(CHAR(64), source_sha256), CONVERT(CHAR(64), extraction_sha256), CONVERT(VARCHAR(64), preprocess_contract), size_bytes FROM @requested_documents; IF @@ROWCOUNT <> (SELECT COUNT(*) FROM @requested_documents) BEGIN RAISERROR(N'purchase_source_document_audit_failed', 16, 1); RETURN; END; 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 = 'purchase_draft_created', 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'purchase_idempotency_completion_failed', 16, 1); RETURN; END; SELECT CONVERT(BIT, 1) AS success, CONVERT(VARCHAR(128), 'purchase_draft_created') AS code, CONVERT(NVARCHAR(1000), N'采购发票草稿已通过 ERP 原保存链创建。') 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