/*
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
