郎速科技
1237 lines
52 KiB
Transact-SQL
1237 lines
52 KiB
Transact-SQL
/*
|
|
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
|