--Hash:5F31F413C9BDDF1FBD54B433857C3091

-- Bloqueo exclusivo por base de datos
--//
SET NOCOUNT ON;

DECLARE @__ZiurDbScriptLockResource NVARCHAR(200) = N'ZiurSoft:DbScriptLock:' + CAST(DB_ID() AS NVARCHAR(20));
DECLARE @__ZiurDbScriptLockResult INT;
EXEC @__ZiurDbScriptLockResult = sp_getapplock
    @Resource = @__ZiurDbScriptLockResource,
    @LockMode = N'Exclusive',
    @LockOwner = N'Session',
    @LockTimeout = 600000; -- 10 min

IF (@__ZiurDbScriptLockResult < 0)
BEGIN
    RAISERROR(N'No se pudo obtener el bloqueo exclusivo de actualizacion para esta base de datos.', 16, 1);
    SET NOEXEC ON;
END

/* Idempotencia a nivel de script: si la version ya existe, no ejecutar nada. */
IF EXISTS (SELECT 1 FROM dbo.ZiurVersiones WHERE NumVersion = 981)
BEGIN
    PRINT N'Scripts_981.sql: la version 981 ya esta aplicada. Se omite ejecucion.';
    EXEC sp_releaseapplock @Resource = @__ZiurDbScriptLockResource, @LockOwner = N'Session';
    SET NOEXEC ON;
END

SET XACT_ABORT ON;

GO

SET DATEFORMAT mdy;

--Validar que no existan scripts que no se hayan ejecutado
--//
IF EXISTS
(
	SELECT a.NumVersion + 1 AS HuecoInicio
	FROM ZiurVersiones a
	LEFT JOIN ZiurVersiones b ON a.NumVersion + 1 = b.NumVersion
	WHERE a.NumVersion>830 AND b.NumVersion IS NULL
	AND a.NumVersion + 1 < 981
)
RAISERROR('Error : hay un script que no se ejecuto , debe ejecutarlo manualmente.', 20, -1) with log
--//

--Colocar contenido de script diferencial aqui (Ziur.Database.publish.sql)

GO
/*
 Plantilla de script anterior a la implementación							
--------------------------------------------------------------------------------------
 Este archivo contiene instrucciones de SQL que se ejecutarán antes del script de compilación	
 Use la sintaxis de SQLCMD para incluir un archivo en el script anterior a la implementación			
 Ejemplo:      :r .\miArchivo.sql								
 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script anterior a la implementación		
 Ejemplo:      :setvar TableName miTabla							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
GO

GO
PRINT N'Altering Procedure [dbo].[GenerarDocumentos]...';

IF OBJECT_ID(N'dbo.GenerarDocumentos', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE dbo.GenerarDocumentos AS BEGIN SET NOCOUNT ON; RETURN 0; END');


GO
ALTER PROCEDURE GenerarDocumentos
    @IdDoc INT,
    @TipoImplementacion INT, -- 0=GenerarDoc, 1=GenerarEquiv
    @IdEncabNew INT OUTPUT,
    @Exito INT = 0 OUTPUT,
    @MensajeError NVARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY

        IF @TipoImplementacion IS NULL
			SET @TipoImplementacion = 0;

        DECLARE @IdEncab INT;
        DECLARE @XML XML;
        DECLARE @NumeroFacturaFull VARCHAR(50);
        DECLARE @NumeroFactura VARCHAR(50);
        DECLARE @FechaFactura DATE;
        DECLARE @ValorTotal DECIMAL(18,2);
        DECLARE @Mensaje VARCHAR(500);
        DECLARE @TipoDocSincro  INT;
        
        -- Variables para datos del XML
        DECLARE @NitProveedor VARCHAR(50);
        DECLARE @NombreProveedor VARCHAR(200);
        DECLARE @CodigoMoneda VARCHAR(10);
        DECLARE @IdDocumento INT;
        DECLARE @TipoDocumento VARCHAR(10);
        DECLARE @UnidadMedida VARCHAR(10);
        DECLARE @TipoIdentProveedor VARCHAR(10);
        
        -- Variables para dirección del cliente
        DECLARE @DireccionCliente VARCHAR(500);
        DECLARE @TelefonoCliente VARCHAR(50);
        DECLARE @EmailCliente VARCHAR(100);
        DECLARE @IdCiudadCliente VARCHAR(20);
        DECLARE @IdFormaPago VARCHAR(10);
        DECLARE @PlazoCliente INT;
        DECLARE @Vence DATE;
        
        -- Variables para cálculos
        DECLARE @Cantidad DECIMAL(18,6);
        DECLARE @BaseImpuesto DECIMAL(18,2);
        DECLARE @ValorImpuesto DECIMAL(18,2);
        DECLARE @ValorIva DECIMAL(18,2);
        DECLARE @ValorINC DECIMAL(18,2);
		DECLARE @ValorIBUA DECIMAL(18,2);
        DECLARE @ValorICUI DECIMAL (18,2);
		DECLARE @ValorICL DECIMAL (18,2);
		DECLARE @ValorADV DECIMAL (18,2);
		DECLARE @ValorIC DECIMAL (18,2);
        DECLARE @TipoImpuestoSaludable INT;
        DECLARE @SubTotal DECIMAL(18,2);
        DECLARE @DescuentoTotal DECIMAL(18,2);
        DECLARE @FactorDescuento DECIMAL(18,6);

        -- Variables para datos del XML
        DECLARE @NitEmpresa VARCHAR(50);
        DECLARE @CiudadBodega VARCHAR(50);
        DECLARE @CodigoCiudad VARCHAR(10);

        DECLARE @IdTipoIdent INT;
        DECLARE @TaxLevelCode VARCHAR(200);
        DECLARE @IdTipoRegimen INT;

        -- Obtenemos el XML
        SELECT @XML = CAST(PR.Recurso AS XML)
        FROM dbo.DocElectronicos DE 
        INNER JOIN dbo.DocElectronicos_DocRecursos DR ON DE.IdDoc = DR.IdDoc 
        INNER JOIN dbo.Parametros_Recursos PR ON DR.IdRecurso = PR.IdRecurso
        WHERE DE.IdDoc = @IdDoc
        AND DR.IdTipo = 0;

        IF @XML IS NULL
        BEGIN
            RAISERROR('No se encontró el documento XML para el ID especificado.', 16, 1);
            RETURN;
        END;

        SELECT @TipoDocSincro = TipoDocSincro FROM DocElectronicos WHERE IdDoc=@IdDoc;

        -- Extraemos todos los datos relevantes del XML
        WITH XMLNAMESPACES (
            'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' as inv,
            'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' as cn,
            'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,
            'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
        )
        SELECT 
            -- Datos básicos del documento
            @NumeroFactura = COALESCE(
                @XML.value('(/inv:Invoice/cbc:ID)[1]', 'varchar(50)'),
                @XML.value('(/cn:CreditNote/cbc:ID)[1]', 'varchar(50)')
            ),
            @FechaFactura  = COALESCE(
                @XML.value('(/inv:Invoice/cbc:IssueDate)[1]', 'date'),
                @XML.value('(/cn:CreditNote/cbc:IssueDate)[1]', 'date')
            ),
            @TipoDocumento = COALESCE(
                @XML.value('(/inv:Invoice/cbc:InvoiceTypeCode)[1]', 'varchar(10)'),
                @XML.value('(/cn:CreditNote/cbc:CreditNoteTypeCode)[1]', 'varchar(10)')
            ),
            @CodigoMoneda  = COALESCE(
                @XML.value('(/inv:Invoice/cbc:DocumentCurrencyCode)[1]', 'varchar(10)'),
                @XML.value('(/cn:CreditNote/cbc:DocumentCurrencyCode)[1]', 'varchar(10)')
            ),
            -- Datos de la empresa receptora (para determinar IdEmpresa)
            @NitEmpresa = COALESCE(
                  @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)'),
                  @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)')
              ),            
            -- Datos de ubicación (para determinar bodega)
            @CiudadBodega = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:CityName)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:CityName)[1]','varchar(50)')
            ),
            @CodigoCiudad = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(10)')
            ),
            -- Datos del proveedor
            @NitProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)')
            ),
            @NombreProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name)[1]','varchar(200)'),
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:RegistrationName)[1]','varchar(200)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:RegistrationName)[1]','varchar(200)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name)[1]','varchar(200)')
            ),
            @TipoIdentProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]','varchar(10)')
            ),
            -- Datos del producto/servicio
            @UnidadMedida = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine/cbc:InvoicedQuantity/@unitCode)[1]','varchar(10)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine/cbc:CreditedQuantity/@unitCode)[1]','varchar(10)')
              ),            
            -- Datos del proveedor y su dirección
            @DireccionCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cac:AddressLine/cbc:Line)[1]','varchar(500)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cac:AddressLine/cbc:Line)[1]','varchar(500)')
            ),
            @TelefonoCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:Telephone)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:Telephone)[1]','varchar(50)')
            ),
            @EmailCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:ElectronicMail)[1]','varchar(100)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:Contact/cbc:ElectronicMail)[1]','varchar(100)')
            ),
            @IdCiudadCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(20)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(20)')
            ),
            @IdFormaPago = COALESCE(
                @XML.value('(/inv:Invoice/cac:PaymentMeans/cbc:ID)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:PaymentMeans/cbc:ID)[1]','varchar(10)')
            ),
            -- Cantidades y valores
            @Cantidad = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine/cbc:InvoicedQuantity)[1]','decimal(18,6)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine/cbc:CreditedQuantity)[1]','decimal(18,6)')
            ),            
            --@ValorUnitarioItem = @XML.value('(/inv:Invoice/cac:InvoiceLine/cac:Price/cbc:PriceAmount)[1]', 'decimal(18,2)'),
            @BaseImpuesto = COALESCE(
                @XML.value('(/inv:Invoice/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxableAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxableAmount)[1]','decimal(18,2)')
            ),
            @ValorImpuesto = COALESCE(
                @XML.value('(/inv:Invoice/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxAmount)[1]','decimal(18,2)')
            ),
            @SubTotal = COALESCE(
                @XML.value('(/inv:Invoice/cac:LegalMonetaryTotal/cbc:LineExtensionAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:LegalMonetaryTotal/cbc:LineExtensionAmount)[1]','decimal(18,2)')
            ),
            @DescuentoTotal = COALESCE(
                @XML.value('(/inv:Invoice/cac:LegalMonetaryTotal/cbc:AllowanceTotalAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:LegalMonetaryTotal/cbc:AllowanceTotalAmount)[1]','decimal(18,2)')
            ),
            @ValorTotal = COALESCE(
                @XML.value('(/inv:Invoice/cac:LegalMonetaryTotal/cbc:PayableAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:LegalMonetaryTotal/cbc:PayableAmount)[1]','decimal(18,2)')
            ),
            @IdTipoIdent = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]', 'int'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]', 'int')
            ),
            @TaxLevelCode = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:TaxLevelCode)[1]', 'varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:TaxLevelCode)[1]', 'varchar(50)')
            ),

            @IdTipoRegimen =
            CASE
                WHEN @IdTipoIdent = 42 THEN 9 -- Tercero del Exterior
                WHEN @IdTipoIdent = 13 AND @TaxLevelCode LIKE '%O-15%' THEN 10 -- Gran Contribuyente Persona Natural
                WHEN @IdTipoIdent = 13 AND @TaxLevelCode = 'R-99-PN' THEN 6 -- Persona Natural No Responsable de IVA
                WHEN @IdTipoIdent = 13 THEN 2 -- Persona Natural Responsable de IVA
                WHEN @IdTipoIdent = 31 AND @TaxLevelCode LIKE '%O-15%' THEN 4 -- Gran Contribuyente
                WHEN @IdTipoIdent = 31 THEN 3 -- Persona Jurídica
                ELSE 6
            END

		IF @TipoDocSincro=1
		BEGIN
		WITH XMLNAMESPACES (
            'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' as inv,
            'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' as cn,
            'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,
            'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
        )
        SELECT        
			@NitEmpresa = COALESCE(
                  @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)'),
                  @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)')
              ),
            -- Datos de ubicación (para determinar bodega)
            @CiudadBodega = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:CityName)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:CityName)[1]','varchar(50)')
            ),
            @CodigoCiudad = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(10)')
            ),
            -- Datos del cliente
            @NitProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)')
            ),
            @NombreProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PartyName/cbc:Name)[1]','varchar(200)'),
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:RegistrationName)[1]','varchar(200)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:RegistrationName)[1]','varchar(200)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PartyName/cbc:Name)[1]','varchar(200)')
            ),
            @TipoIdentProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]','varchar(10)')
            ),            
            -- Datos del proveedor y su dirección
            @DireccionCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cac:AddressLine/cbc:Line)[1]','varchar(500)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cac:AddressLine/cbc:Line)[1]','varchar(500)')
            ),
            @TelefonoCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:Telephone)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:Telephone)[1]','varchar(50)')
            ),
            @EmailCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:ElectronicMail)[1]','varchar(100)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:Contact/cbc:ElectronicMail)[1]','varchar(100)')
            ),
            @IdCiudadCliente = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(20)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(20)')
            )
		END;

        SET @TipoDocumento = COALESCE(@TipoDocumento,'');
        
        SELECT @IdDocumento = CASE 
            WHEN @TipoDocumento IN ('01', '40') THEN 1
            WHEN @TipoDocumento = '91' THEN 16
            ELSE NULL -- o algún valor por defecto si lo necesitas
        END
        

        SET @NumeroFacturaFull = (CASE WHEN @TipoDocumento = '01' THEN '' ELSE @TipoDocumento + '-' END) + @NumeroFactura;

        IF @TipoImplementacion = 0
        BEGIN
            -- Validamos que solo se puedan procesar facturas(01), Peajes(40; Sin probar) y notas crédito(91)
            IF @TipoDocSincro=0
            BEGIN
                IF @TipoDocumento NOT IN ('01','40','91')
                BEGIN
                    RAISERROR('No se pueden procesar documentos que no sean ''Factura electrónica'' o ''Doc cobro peajes'' o ''Nota Crédito''.', 16, 1);
                    RETURN;
                END;
            END;
            ELSE
            BEGIN
                SELECT @IdDocumento = CASE WHEN @TipoDocumento IN ('01') THEN 2 END --Esto es para documento enviado y se convierte a factura venta
                IF @TipoDocumento NOT IN ('01')
                BEGIN
                    RAISERROR('No se pueden procesar documentos que no sean ''Factura electrónica''.', 16, 1);
                    RETURN;
                END;
            END;
            
            -- Validamos que tengamos los datos mínimos necesarios
            IF @NumeroFactura IS NULL OR @FechaFactura IS NULL OR @ValorTotal IS NULL OR @NitProveedor IS NULL
            BEGIN
                RAISERROR('No se pudieron extraer todos los datos necesarios de la factura.', 16, 1);
                RETURN;
            END;

            -- Validamos que el documento no exista
            --//
            IF EXISTS (SELECT 1 FROM Encabezados WHERE NumDocumentoExt = @NumeroFacturaFull)
            BEGIN
                RAISERROR('El documento %s ya existe en el sistema !', 16, 1, @NumeroFacturaFull);
                RETURN;
            END;
            --//

            --Se inicia aqui la transaccion para que se haga el insert de DocElectronicos_Equivalencias
            BEGIN TRANSACTION;

            -- Buscamos o creamos el proveedor si no existe
            DECLARE @IdCliente INT;
            SELECT TOP 1 @IdCliente = IdCliente FROM Clientes WHERE Identificacion = @NitProveedor;

            -- Si el cliente no existe, lo creamos junto con su dirección
            IF @IdCliente IS NULL
            BEGIN
                -- Calculamos el DV
                DECLARE @DVCliente VARCHAR(2) = NULL;
                IF @TipoIdentProveedor=31
                    SET @DVCliente = dbo.GetDigitoVerificacion(@NitProveedor);

                -- Insertamos el cliente
                INSERT INTO Clientes (
                    Identificacion,
                    Nombre1,
                    EsActivo,
                    IdTipoIdent,
                    DVCliente,
                    IdCiudad,
                    IdTipoRegimen,
                    TarifaIca,
                    CupoCreditoPlazo,
                    ComisionTiene,
                    IdPais,
                    FechaCreacion,
                    FechaUltModificacion
                )
                VALUES (
                    @NitProveedor,
                    @NombreProveedor,
                    1,
                    @IdTipoIdent,
                    @DVCliente,
                    @IdCiudadCliente,
                    @IdTipoRegimen,
                    NULL,
                    NULL,
                    NULL,
                    48,
                    GETDATE(),
                    GETDATE()
                );
            
                SET @IdCliente = SCOPE_IDENTITY();

                -- Obtenemos el IdTipoDir mínimo
                DECLARE @IdTipoDir INT;
                SELECT @IdTipoDir = MIN(IdTipoDir) FROM Clientes_TiposDireccion;

                -- Insertamos la dirección del cliente
                INSERT INTO Clientes_Direcciones (
                    IdCliente,
                    IdTipoDir,
                    Direccion,
                    DireccionFormato1,
                    DireccionFormato2,
                    Telefonos,
                    Celular,
                    Email,
                    IdCiudad,
                    Nota,
                    EsDirPrincipal,
                    Subdivision,
                    CodigoPostal
                )
                VALUES (
                    @IdCliente,
                    @IdTipoDir,
                    @DireccionCliente,
                    @DireccionCliente,
                    @DireccionCliente,
                    @TelefonoCliente,
                    @TelefonoCliente,
                    @EmailCliente,
                    @IdCiudadCliente,
                    NULL,
                    1,
                    NULL,
                    NULL
                );

                -- Insertamos las asociaciones del cliente
                INSERT INTO Clientes_Asociacion (IdCliente, IdTipo) VALUES (@IdCliente, 5);
                INSERT INTO Clientes_Asociacion (IdCliente, IdTipo) VALUES (@IdCliente, 8);
            END;

            -- Obtenemos el IdClienteDir principal
            DECLARE @IdClienteDir INT;
            SELECT @IdClienteDir = MAX(idclientedir) 
            FROM Clientes_Direcciones 
            WHERE EsDirPrincipal = 1 AND IdCliente = @IdCliente;

            -- Obtenemos el plazo del cliente
            SELECT @PlazoCliente = ISNULL(CupoCreditoPlazo, 0) 
            FROM Clientes 
            WHERE IdCliente = @IdCliente;

            -- Buscamos el ID de la moneda
            DECLARE @IdMoneda INT;
            IF @CodigoMoneda = 'COP'
                SET @IdMoneda = 1; -- Asumimos que 1 es el ID para COP
            ELSE
            BEGIN
                SELECT TOP 1 @IdMoneda = IdMoneda
                FROM Monedas
                WHERE Abrev = @CodigoMoneda
                OR NomMoneda1 = @CodigoMoneda;

                IF @IdMoneda IS NULL
                    SET @IdMoneda = 1; -- Default a COP si no se encuentra
            END;


            -- Buscamos la empresa por su NIT
            DECLARE @IdEmpresa INT;
            SELECT TOP 1 @IdEmpresa = IdEmpresa
            FROM Empresas
            WHERE EmpresaNit = @NitEmpresa;

            IF @IdEmpresa IS NULL
            BEGIN
                RAISERROR('No se encontró la empresa con NIT: %s', 16, 1, @NitEmpresa);
                RETURN;
            END;

            -- Bodega principal predterminada
            DECLARE @IdBodega INT = 1; -- Valor por defecto
            SET @Vence = DATEADD(day, @PlazoCliente, @FechaFactura)

            -- Insertar en Encabezados
            INSERT INTO Encabezados 
            (
                IdEmpresa,
                IdDocumento,
                NumDocumentoExt,
                Fecha,
                IdCliente1,
                IdCliente1_IdDir,
                IdMoneda,
                ValorTRM,
                ValorPago,
                Nota,
                IdFormaPago,
                Vence,
                FechaCreacion,
                FechaUltModificacion
            )
            VALUES (
                @IdEmpresa,
                @IdDocumento,
                @NumeroFacturaFull,
                @FechaFactura,
                @IdCliente,
                @IdClienteDir,
                @IdMoneda,
                0,                  -- ValorTRM siempre 0
                0,
                'Importado automáticamente',
                @IdFormaPago,
                @Vence,
                GETDATE(),
                GETDATE()
            );

            SET @IdEncab = SCOPE_IDENTITY();
        
        END; -- Fin If TipoImplementacion = 0

        --*************************************************************************
		--ITERAR ITEMS
        DECLARE @TotalItems INT
        DECLARE @Index INT = 1
        DECLARE @IdInventario INT;
        DECLARE @IdUnidad INT = 1; -- Valor por defecto
        DECLARE @CodInventario VARCHAR(50);
        DECLARE @CodInventario2 VARCHAR(50);
        DECLARE @CodInventario3 VARCHAR(50);
        DECLARE @NomInventario VARCHAR(500);
        DECLARE @ValorUnitario NUMERIC(18,2);

        --Equivalencias
        DECLARE @EqSenFind                 BIT;
        DECLARE @EqIdEquiv                 INT;
        DECLARE @EqIdInventario            INT;
        DECLARE @EqIdTipoInv               INT;
        DECLARE @EqIdContab               INT;
        DECLARE @EqCodInventario_Pref      NVARCHAR(50);

        -- Contar cuántos nodos hay
		WITH XMLNAMESPACES (
			'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS inv,
            'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS cn,
			'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
			'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc
		)
        SELECT @TotalItems = CASE
          WHEN @XML.exist('/inv:Invoice') = 1
               THEN @XML.value('count(/inv:Invoice/cac:InvoiceLine)', 'int')
          ELSE     @XML.value('count(/cn:CreditNote/cac:CreditNoteLine)', 'int')
        END;        
        


        -- Bucle para procesar cada uno
        WHILE @Index <= @TotalItems
        BEGIN
            -- Inicializar
            SET @EqSenFind                 = 0;
            SET @EqIdEquiv                 = NULL;
            SET @EqIdInventario            = NULL;
            SET @EqIdTipoInv               = NULL;
            SET @EqIdContab                = NULL;
            SET @EqCodInventario_Pref      = NULL;
            SET @IdInventario              = NULL;

            -- Extraer valores del XML
            WITH XMLNAMESPACES (
                'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS inv,
                'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS cn,
                'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
                'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc
            )
            SELECT
                @CodInventario = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cbc:ID)[1]','varchar(20)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cbc:ID)[1]','varchar(20)')
                ),
                @CodInventario3 = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Item/cac:StandardItemIdentification/cbc:ID)[1]','varchar(20)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Item/cac:StandardItemIdentification/cbc:ID)[1]','varchar(20)')
                ),
                @CodInventario2 = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Item/cac:SellersItemIdentification/cbc:ID)[1]','varchar(20)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Item/cac:SellersItemIdentification/cbc:ID)[1]','varchar(20)')
                ),
                @NomInventario = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Item/cbc:Description)[1]','varchar(500)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Item/cbc:Description)[1]','varchar(500)')
                ),
                @Cantidad = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cbc:InvoicedQuantity)[1]','decimal(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cbc:CreditedQuantity)[1]','decimal(18,2)')
                ),
                @ValorTotal = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cbc:LineExtensionAmount)[1]','decimal(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cbc:LineExtensionAmount)[1]','decimal(18,2)')
                ),
                @ValorUnitario = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Price/cbc:PriceAmount)[1]','numeric(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Price/cbc:PriceAmount)[1]','numeric(18,2)')
                ),
                @ValorIva = COALESCE(
                    @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="01"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)'),
                    @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="01"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)')
                    ),
                @ValorINC = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="04"]/cbc:TaxAmount)[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="04"]/cbc:TaxAmount)[1]','decimal(18,2)')
                ),
				@ValorIC = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="02"]/cbc:TaxAmount)[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="02"]/cbc:TaxAmount)[1]','decimal(18,2)')
                ),
                @ValorIBUA = COALESCE(
                    @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="34"]/cbc:TaxAmount)[1]','decimal(18,2)'),
                    @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="34"]/cbc:TaxAmount)[1]','decimal(18,2)')
                ),
                @ValorICUI =COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="35"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="35"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)')
                ),
				@ValorICL =COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="32"]/cbc:TaxAmount )[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="32"]/cbc:TaxAmount )[1]','decimal(18,2)')
                ),
				@ValorADV =COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="36"]/cbc:TaxAmount  )[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="36"]/cbc:TaxAmount  )[1]','decimal(18,2)')
                ),
                @FactorDescuento = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:AllowanceCharge/cbc:MultiplierFactorNumeric)[1]','decimal(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:AllowanceCharge/cbc:MultiplierFactorNumeric)[1]','decimal(18,2)')
                );

                SET @ValorINC = COALESCE(@ValorINC, @ValorIC, 0);
				SET @ValorIC = COALESCE(@ValorIC, 0);
                SET @ValorIBUA = COALESCE(@ValorIBUA, 0);
                SET @ValorICUI = COALESCE(@ValorICUI, 0);
				SET @ValorICL = COALESCE(@ValorICL, 0);
				SET @ValorADV = COALESCE(@ValorADV, 0);
				SET @ValorINC = @ValorINC+@ValorICL+@ValorADV;
                SET @TipoImpuestoSaludable = 0; --0 = Impuesto ICUI, 1 = Impuesto IBUA
                IF @ValorIBUA > 0
                    SET @TipoImpuestoSaludable = 1;

            -- Revisa si tiene el codigo personalizado o uno generico
            SET @CodInventario = COALESCE(@CodInventario3,@CodInventario2, @CodInventario);
            SET @ValorIva = COALESCE(@ValorIva, 0);
            SET @FactorDescuento = COALESCE(@FactorDescuento, 0);

            IF @TipoImplementacion = 0
                BEGIN
                --Caso Equivalencias NomInventario
                --//
                IF (@EqSenFind=0)
                BEGIN
                    SELECT @EqIdEquiv = IdEquiv , @EqIdInventario = IdInventario,  @EqIdTipoInv = IdTipoInv, @EqIdContab= IdContab, @EqCodInventario_Pref = CodInventario_Pref
                    FROM DocElectronicos_Equivalencias 
                    WHERE 
                    (IdTipoIdent = @IdTipoIdent AND Identificacion=@NitProveedor) AND 
                    (NomInventario_Operador IS NOT NULL AND NomInventario IS NOT NULL) AND 
                    (
                        (NomInventario_Operador = 0 AND @NomInventario = NomInventario ) OR
                        (NomInventario_Operador = 1 AND @NomInventario LIKE '%' + NomInventario + '%') OR
                        (NomInventario_Operador = 2 AND @NomInventario LIKE NomInventario + '%') OR
                        (NomInventario_Operador = 3 AND @NomInventario LIKE '%' + NomInventario)
                    ) AND
                    (ValorIva = @ValorIva OR ValorIva IS NULL)
                    ORDER BY CASE WHEN ValorIva = @ValorIva THEN 1 WHEN ValorIva IS NULL THEN 2 END;

                    IF @EqIdEquiv IS NOT NULL
                        SET @EqSenFind = 1
                END
                --//

                --Caso Equivalencias CodInventario
                --//
                IF (@EqSenFind=0)
                BEGIN
                    SELECT @EqIdEquiv = IdEquiv , @EqIdInventario = IdInventario,  @EqIdTipoInv = IdTipoInv, @EqIdContab= IdContab, @EqCodInventario_Pref = CodInventario_Pref
                    FROM DocElectronicos_Equivalencias 
                    WHERE 
                    (IdTipoIdent = @IdTipoIdent AND Identificacion=@NitProveedor) AND 
                    (CodInventario_Operador IS NOT NULL AND CodInventario IS NOT NULL) AND 
                    (
                        (CodInventario_Operador = 0 AND @CodInventario = CodInventario ) OR
                        (CodInventario_Operador = 1 AND @CodInventario LIKE '%' + CodInventario + '%') OR
                        (CodInventario_Operador = 2 AND @CodInventario LIKE CodInventario + '%') OR
                        (CodInventario_Operador = 3 AND @CodInventario LIKE '%' + CodInventario)
                    ) AND
                    (ValorIva = @ValorIva OR ValorIva IS NULL)
                    ORDER BY CASE WHEN ValorIva = @ValorIva THEN 1 WHEN ValorIva IS NULL THEN 2 END;
                
                    IF @EqIdEquiv IS NOT NULL
                        SET @EqSenFind = 1
                END
                --//
            
                --Caso Equivalencias General por TipoIdent y Proveedor
                --//
                IF (@EqSenFind=0)
                BEGIN
                    SELECT TOP 1 @EqIdEquiv = IdEquiv , @EqIdInventario = IdInventario,  @EqIdTipoInv = IdTipoInv, @EqIdContab= IdContab, @EqCodInventario_Pref = CodInventario_Pref
                    FROM DocElectronicos_Equivalencias 
                    WHERE 
                    (IdTipoIdent = @IdTipoIdent AND Identificacion=@NitProveedor) AND
                    (ValorIva = @ValorIva OR ValorIva IS NULL)
                    ORDER BY CASE WHEN ValorIva = @ValorIva THEN 1 WHEN ValorIva IS NULL THEN 2 END;

                    IF @EqIdEquiv IS NOT NULL
                        SET @EqSenFind = 1
                END
                --//

                --Caso Equivalencias General
                --//
                IF (@EqSenFind=0)
                BEGIN
                    SELECT TOP 1 @EqIdEquiv = IdEquiv , @EqIdInventario = IdInventario,  @EqIdTipoInv = IdTipoInv, @EqIdContab= IdContab, @EqCodInventario_Pref = CodInventario_Pref
                    FROM DocElectronicos_Equivalencias 
                    WHERE 
                    (ValorIva = @ValorIva OR ValorIva IS NULL)
                    ORDER BY CASE WHEN ValorIva = @ValorIva THEN 1 WHEN ValorIva IS NULL THEN 2 END;

                    IF @EqIdEquiv IS NOT NULL
                        SET @EqSenFind = 1
                END
                --//

                IF (@EqSenFind =0)
                BEGIN
                    DECLARE @NomTipoIdent NVARCHAR(100);
                    SELECT @NomTipoIdent = NomTipoIdent FROM Clientes_TiposIdent WHERE IdTipoIdent = @IdTipoIdent;

                    DECLARE @strValorIVA NVARCHAR(200) = CAST(@ValorIva AS NVARCHAR(200));
                    RAISERROR('Doc %s : No se encontró equivalencia con TipoIdent: %s, Número: %s, Inventario: %s, ValorIva: %s', 16, 1, @NumeroFacturaFull, @NomTipoIdent, @NitProveedor, @CodInventario, @strValorIVA);
                    RETURN;
                END;

                --Obtener el id del inventario
                --//
                DECLARE @CodInventarioFull NVARCHAR(50);
                DECLARE @tIdTipoInv INT = NULL;

                SET @CodInventarioFull = COALESCE(@EqCodInventario_Pref, '') + @CodInventario;

                IF (@EqIdInventario IS NULL)
                BEGIN
                    SELECT TOP 1 @IdInventario = IdInventario, @tIdTipoInv = IdTipoInv FROM Inventarios WHERE CodInventario = @CodInventarioFull;


                    DECLARE @TipoInvMovCSV varchar(200);
                    SELECT @TipoInvMovCSV = REPLACE(ISNULL(TipoInvMov,''),' ','')
                    FROM dbo.Documentos
                    WHERE IdDocumento = @IdDocumento;
                    --Validar el tipo de inventario permitido de manera dinámica
                    --//
                    IF (@IdInventario IS NOT NULL)
                       AND CHARINDEX(',' + CAST(@tIdTipoInv AS varchar(10)) + ',', ',' + @TipoInvMovCSV + ',') = 0
                    BEGIN
                        RAISERROR('Doc %s : El inventario %s (TipoInv %d) no está permitido para el doc %d. Permitidos: %s',16, 1, @NumeroFacturaFull, @CodInventarioFull, @tIdTipoInv, @IdDocumento, @TipoInvMovCSV);
                        RETURN;
                    END
                    --//

                    --Validar si toca crear un producto y no tiene una contabilizacion definida
                    --//
                    IF (@IdInventario IS NULL) AND (@EqIdContab IS NULL)
                    BEGIN
                        RAISERROR('Doc %s : No existe una contabilización definida, para la creación del producto %s', 16, 1, @NumeroFacturaFull, @CodInventarioFull);
                        RETURN;
                    END
                    --//

                    IF @IdInventario IS NULL
                    BEGIN
                        -- Aquí deberíamos insertar el nuevo producto
                        INSERT INTO Inventarios 
                        (
                            CodInventario,
                            NomInventario,
                            EsActivo,
                            CantMaxima,
                            CantMinima, 
                            PuntoReorden,
                            IdUnidad,
                            ValorIva,
							TipoImptoConsumo,
                            ValorImptoConsumo,
                            ImptoSaludableTipo,
                            IdTipoInv,
                            IdContab,
                            ValorUtilidadEst,
                            TipoVentaKit,
                            FechaCreacion,
                            FechaUltModificacion
                        )
                        VALUES (
                            @CodInventarioFull,
                            @NomInventario,
                            1,
                            NULL,
                            NULL,
                            NULL,
                            1,
                            @ValorIva,
							1,
                            @ValorINC,
                            @TipoImpuestoSaludable,
                            COALESCE(@EqIdTipoInv,1),
                            @EqIdContab,
                            NULL,
                            NULL,
                            GETDATE(),
                            GETDATE()
                        );
            
                        SET @IdInventario = SCOPE_IDENTITY();
                    END;
                END;
                ELSE
                BEGIN
                    SET @IdInventario = @EqIdInventario;
                END;
                --//

                --Verificar si el producto tiene contabilizacion
                --//
                DECLARE @tIdContab INT = NULL
                SELECT @tIdContab = IdContab from Inventarios where IdInventario = @IdInventario
                IF (@tIdContab IS NULL)
                BEGIN
                    RAISERROR('Doc %s : No se encontró una contabilización para el inventario : %s', 16, 1, @NumeroFacturaFull, @CodInventarioFull);
                    RETURN;
                END
                --//

                -- Insertar movimiento inventario
                INSERT INTO Encabezados_Mov 
				    (
					    IdEncab,
					    IdInventario,
					    IdBodega,
					    IdLote,
					    Cantidad,
					    CantidadFisica,
					    IdUnidad,
					    FactorCantidad,
					    FactorMovim,
					    ValorUnitario,
					    ValorUnitarioExt,
					    ValorIva,
						TipoImptoConsumo,
					    ValorImptoConsumo,
					    ImptoSaludableValor,
					    ImptoSaludableTipo,
					    TotalMov,
					    IdCliente,
					    IdMoneda,
					    Nota,
					    Vence,
					    IdCentroCosto,
					    Dcto,
					    FechaCreacion,
					    FechaUltModificacion
				    )
				    VALUES (
					    @IdEncab,
					    @IdInventario,     -- Ya era dinámico
					    @IdBodega,         -- Ahora dinámico basado en la ubicación
					    NULL,              -- IdLote
					    @Cantidad,         -- Ya era dinámico
					    NULL,              -- CantidadFisica
					    @IdUnidad,         -- Ya era dinámico
					    1,         -- Ya era dinámico
					    1,                 -- FactorMovim
					    @ValorUnitario,    -- Ya era dinámico
					    NULL,              -- ValorUnitarioExt
					    @ValorIva, -- Ya era dinámico
						1,
					    @ValorINC/@Cantidad,          -- ValorImptoConsumo
					    CASE WHEN @TipoImpuestoSaludable = 0 THEN @ValorICUI ELSE @ValorIBUA/@Cantidad END,
					    @TipoImpuestoSaludable,
					    NULL,       -- Ya era dinámico
					    @IdCliente,        -- Ya era dinámico
					    1,
					    'Factura XML: ' + @NumeroFacturaFull + ' - ' + @NomInventario,
					    @Vence,              -- Vence
					    NULL,              -- IdCentroCosto
					    @FactorDescuento,   -- Ya era dinámico
					    GETDATE(),
					    GETDATE()
				    );
                END -- Fin If TipoImplementacion = 0
                ELSE
                BEGIN
			        -- 3) Insertar equivalencia 
			        INSERT INTO DocElectronicos_Equivalencias (
				        IdTipoIdent, 
				        Identificacion, 
				        NombreCompleto,
				        NomInventario_Operador, 
				        NomInventario,
				        CodInventario_Operador, 
				        CodInventario,
				        ValorIva, 
				        IdInventario, 
				        IdTipoInv, 
				        IdContab, 
				        CodInventario_Pref
			        )
			        SELECT
				        @IdTipoIdent, 
				        @NitProveedor, 
				        @NombreProveedor,
				        0, 
				        @NomInventario,
				        0, 
				        @CodInventario,
				        @ValorIva, 
				        NULL, 
				        NULL, 
				        NULL, 
				        NULL
			        WHERE NOT EXISTS (
				        SELECT 1
				        FROM DocElectronicos_Equivalencias e
				        WHERE ISNULL(LTRIM(RTRIM(e.Identificacion)),'') = ISNULL(LTRIM(RTRIM(@NitProveedor)),'')
				          AND ISNULL(LTRIM(RTRIM(e.CodInventario)),'') = ISNULL(LTRIM(RTRIM(@CodInventario)),'')
                          AND ISNULL(LTRIM(RTRIM(e.NomInventario)),'') = ISNULL(LTRIM(RTRIM(@NomInventario)),'')
			        );

                END; -- Fin Else TipoImplementacion = 1
                SET @Index = @Index + 1
            END

            IF @TipoImplementacion = 0
            BEGIN
                -- Retornamos el ID del encabezado creado
                SELECT @IdEncab as IdEncabezado;
                SET @IdEncabNew = @IdEncab;

                --Contabilizar Documento
                --//
                EXEC [dbo].[ContabilizarDoc] @IdEncab = @IdEncab, @Exito = @Exito OUTPUT, @InfoUsuario = N'0,--BASEDATOS--';
                --//

                COMMIT TRANSACTION;

                -- Si todo sale bien, establecemos el éxito
                --//
                SET @Exito = 1
                SET @MensajeError = FORMATMESSAGE(N'Documento ''%s'', procesado correctamente', @NumeroFacturaFull);
                RETURN
            END
            ELSE
            BEGIN
             -- Si todo sale bien, establecemos el éxito
                --//
                SET @Exito = 1
                SET @MensajeError = FORMATMESSAGE(N'Equivalencia del Documento ''%s'', copiada correctamente', @NumeroFacturaFull);
                RETURN
                --//
            END;
            --//
    END TRY
    BEGIN CATCH
        --si hay error hace rollback de la transacción
        IF XACT_STATE() <> 0    
            ROLLBACK TRANSACTION;

        -- Si hay error, enviamos el msg de error
        --//
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        SET @Exito = 0
        SET @MensajeError = @ErrorMsg;
        --//
    END CATCH;
END;
GO
PRINT N'Altering Procedure [dbo].[GenerarEquivalencia]...';

IF OBJECT_ID(N'dbo.GenerarEquivalencia', N'P') IS NULL
    EXEC(N'CREATE PROCEDURE dbo.GenerarEquivalencia AS BEGIN SET NOCOUNT ON; RETURN 0; END');


GO
ALTER PROCEDURE GenerarEquivalencia
    @IdDoc INT,
    @Exito INT = 0 OUTPUT,
    @MensajeError NVARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @IdEncab INT;
        DECLARE @XML XML;
        DECLARE @NumeroFacturaFull VARCHAR(50);
        DECLARE @NumeroFactura VARCHAR(50);
        DECLARE @FechaFactura DATE;
        DECLARE @ValorTotal DECIMAL(18,2);
        DECLARE @Mensaje VARCHAR(500);
        
        -- Variables para datos del XML
        DECLARE @NitProveedor VARCHAR(50);
        DECLARE @NombreProveedor VARCHAR(200);
        DECLARE @CodigoMoneda VARCHAR(10);
        DECLARE @IdDocumento INT;
        DECLARE @TipoDocumento VARCHAR(10);
        DECLARE @UnidadMedida VARCHAR(10);
        DECLARE @TipoIdentProveedor VARCHAR(10);
        
        -- Variables para cálculos
        DECLARE @Cantidad DECIMAL(18,6);
        DECLARE @BaseImpuesto DECIMAL(18,2);
        DECLARE @ValorImpuesto DECIMAL(18,2);
        DECLARE @ValorIva DECIMAL(18,2);
        DECLARE @ValorINC DECIMAL(18,2);
		DECLARE @ValorIBUA DECIMAL(18,2);
        DECLARE @ValorICUI DECIMAL (18,2);
        DECLARE @TipoImpuestoSaludable INT;
        DECLARE @SubTotal DECIMAL(18,2);
        DECLARE @DescuentoTotal DECIMAL(18,2);
        DECLARE @FactorDescuento DECIMAL(18,6);

        -- Variables para datos del XML
        DECLARE @NitEmpresa VARCHAR(50);
        DECLARE @CiudadBodega VARCHAR(50);
        DECLARE @CodigoCiudad VARCHAR(10);

        DECLARE @IdTipoIdent INT;
        DECLARE @TaxLevelCode VARCHAR(200);
        DECLARE @IdTipoRegimen INT;

        -- Obtenemos el XML
        SELECT @XML = CAST(PR.Recurso AS XML)
        FROM dbo.DocElectronicos DE 
        INNER JOIN dbo.DocElectronicos_DocRecursos DR ON DE.IdDoc = DR.IdDoc 
        INNER JOIN dbo.Parametros_Recursos PR ON DR.IdRecurso = PR.IdRecurso
        WHERE DE.IdDoc = @IdDoc
        AND DR.IdTipo = 0;

        IF @XML IS NULL
        BEGIN
            RAISERROR('No se encontró el documento XML para el ID especificado.', 16, 1);
            RETURN;
        END;

        -- Extraemos todos los datos relevantes del XML
        WITH XMLNAMESPACES (
            'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' as inv,
            'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' as cn,
            'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as cbc,
            'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as cac
        )
        SELECT 
            -- Datos básicos del documento
            @NumeroFactura = COALESCE(
                @XML.value('(/inv:Invoice/cbc:ID)[1]', 'varchar(50)'),
                @XML.value('(/cn:CreditNote/cbc:ID)[1]', 'varchar(50)')
            ),
            @FechaFactura  = COALESCE(
                @XML.value('(/inv:Invoice/cbc:IssueDate)[1]', 'date'),
                @XML.value('(/cn:CreditNote/cbc:IssueDate)[1]', 'date')
            ),
            @TipoDocumento = COALESCE(
                @XML.value('(/inv:Invoice/cbc:InvoiceTypeCode)[1]', 'varchar(10)'),
                @XML.value('(/cn:CreditNote/cbc:CreditNoteTypeCode)[1]', 'varchar(10)')
            ),
            @CodigoMoneda  = COALESCE(
                @XML.value('(/inv:Invoice/cbc:DocumentCurrencyCode)[1]', 'varchar(10)'),
                @XML.value('(/cn:CreditNote/cbc:DocumentCurrencyCode)[1]', 'varchar(10)')
            ),
            -- Datos de la empresa receptora (para determinar IdEmpresa)
            @NitEmpresa = COALESCE(
                  @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)'),
                  @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)')
              ),            
            -- Datos de ubicación (para determinar bodega)
            @CiudadBodega = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:CityName)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:CityName)[1]','varchar(50)')
            ),
            @CodigoCiudad = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:AccountingCustomerParty/cac:Party/cac:PhysicalLocation/cac:Address/cbc:ID)[1]','varchar(10)')
            ),
            -- Datos del proveedor
            @NitProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID)[1]','varchar(50)')
            ),
            @NombreProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name)[1]','varchar(200)'),
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:RegistrationName)[1]','varchar(200)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:RegistrationName)[1]','varchar(200)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name)[1]','varchar(200)')
            ),
            @TipoIdentProveedor = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]','varchar(10)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]','varchar(10)')
            ),
            -- Datos del producto/servicio
            @UnidadMedida = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine/cbc:InvoicedQuantity/@unitCode)[1]','varchar(10)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine/cbc:CreditedQuantity/@unitCode)[1]','varchar(10)')
              ),            
            -- Cantidades y valores
            @Cantidad = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine/cbc:InvoicedQuantity)[1]','decimal(18,6)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine/cbc:CreditedQuantity)[1]','decimal(18,6)')
            ),            
            --@ValorUnitarioItem = @XML.value('(/inv:Invoice/cac:InvoiceLine/cac:Price/cbc:PriceAmount)[1]', 'decimal(18,2)'),
            @BaseImpuesto = COALESCE(
                @XML.value('(/inv:Invoice/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxableAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxableAmount)[1]','decimal(18,2)')
            ),
            @ValorImpuesto = COALESCE(
                @XML.value('(/inv:Invoice/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:TaxTotal/cac:TaxSubtotal/cbc:TaxAmount)[1]','decimal(18,2)')
            ),
            @SubTotal = COALESCE(
                @XML.value('(/inv:Invoice/cac:LegalMonetaryTotal/cbc:LineExtensionAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:LegalMonetaryTotal/cbc:LineExtensionAmount)[1]','decimal(18,2)')
            ),
            @DescuentoTotal = COALESCE(
                @XML.value('(/inv:Invoice/cac:LegalMonetaryTotal/cbc:AllowanceTotalAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:LegalMonetaryTotal/cbc:AllowanceTotalAmount)[1]','decimal(18,2)')
            ),
            @ValorTotal = COALESCE(
                @XML.value('(/inv:Invoice/cac:LegalMonetaryTotal/cbc:PayableAmount)[1]','decimal(18,2)'),
                @XML.value('(/cn:CreditNote/cac:LegalMonetaryTotal/cbc:PayableAmount)[1]','decimal(18,2)')
            ),
            @IdTipoIdent = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]', 'int'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:CompanyID/@schemeName)[1]', 'int')
            ),
            @TaxLevelCode = COALESCE(
                @XML.value('(/inv:Invoice/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:TaxLevelCode)[1]', 'varchar(50)'),
                @XML.value('(/cn:CreditNote/cac:AccountingSupplierParty/cac:Party/cac:PartyTaxScheme/cbc:TaxLevelCode)[1]', 'varchar(50)')
            ),

            @IdTipoRegimen =
            CASE
                WHEN @IdTipoIdent = 42 THEN 9 -- Tercero del Exterior
                WHEN @IdTipoIdent = 13 AND @TaxLevelCode LIKE '%O-15%' THEN 10 -- Gran Contribuyente Persona Natural
                WHEN @IdTipoIdent = 13 AND @TaxLevelCode = 'R-99-PN' THEN 6 -- Persona Natural No Responsable de IVA
                WHEN @IdTipoIdent = 13 THEN 2 -- Persona Natural Responsable de IVA
                WHEN @IdTipoIdent = 31 AND @TaxLevelCode LIKE '%O-15%' THEN 4 -- Gran Contribuyente
                WHEN @IdTipoIdent = 31 THEN 3 -- Persona Jurídica
                ELSE 6
            END

        SET @TipoDocumento = COALESCE(@TipoDocumento,'');
        
        SELECT @IdDocumento = CASE 
            WHEN @TipoDocumento IN ('01', '40') THEN 1
            WHEN @TipoDocumento = '91' THEN 16
            ELSE NULL -- o algún valor por defecto si lo necesitas
        END


        SET @NumeroFacturaFull = (CASE WHEN @TipoDocumento = '01' THEN '' ELSE @TipoDocumento + '-' END) + @NumeroFactura;

        --*************************************************************************
		--ITERAR ITEMS
        DECLARE @TotalItems INT
        DECLARE @Index INT = 1
        DECLARE @IdInventario INT;
        DECLARE @IdUnidad INT = 1; -- Valor por defecto
        DECLARE @CodInventario VARCHAR(50);
        DECLARE @CodInventario2 VARCHAR(50);
        DECLARE @CodInventario3 VARCHAR(50);
        DECLARE @NomInventario VARCHAR(500);
        DECLARE @ValorUnitario NUMERIC(18,2);

        --Equivalencias
        DECLARE @EqSenFind                 BIT;
        DECLARE @EqIdEquiv                 INT;
        DECLARE @EqIdInventario            INT;
        DECLARE @EqIdTipoInv               INT;
        DECLARE @EqIdContab               INT;
        DECLARE @EqCodInventario_Pref      NVARCHAR(50);

        -- Contar cuántos nodos hay
		WITH XMLNAMESPACES (
			'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS inv,
            'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS cn,
			'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
			'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc
		)
        SELECT @TotalItems = CASE
          WHEN @XML.exist('/inv:Invoice') = 1
               THEN @XML.value('count(/inv:Invoice/cac:InvoiceLine)', 'int')
          ELSE     @XML.value('count(/cn:CreditNote/cac:CreditNoteLine)', 'int')
        END;        
        
        RAISERROR('Total documentos: %d', 0, 1, @TotalItems) WITH NOWAIT;

        -- Bucle para procesar cada uno
        WHILE @Index <= @TotalItems
        BEGIN
            -- Inicializar
            SET @EqSenFind                 = 0;
            SET @EqIdEquiv                 = NULL;
            SET @EqIdInventario            = NULL;
            SET @EqIdTipoInv               = NULL;
            SET @EqIdContab                = NULL;
            SET @EqCodInventario_Pref      = NULL;
            SET @IdInventario              = NULL;

            -- Extraer valores del XML
            WITH XMLNAMESPACES (
                'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS inv,
                'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS cn,
                'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
                'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc
            )
            SELECT
                @CodInventario = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cbc:ID)[1]','varchar(20)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cbc:ID)[1]','varchar(20)')
                ),
                @CodInventario3 = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Item/cac:StandardItemIdentification/cbc:ID)[1]','varchar(20)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Item/cac:StandardItemIdentification/cbc:ID)[1]','varchar(20)')
                ),
                @CodInventario2 = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Item/cac:SellersItemIdentification/cbc:ID)[1]','varchar(20)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Item/cac:SellersItemIdentification/cbc:ID)[1]','varchar(20)')
                ),
                @NomInventario = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Item/cbc:Description)[1]','varchar(500)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Item/cbc:Description)[1]','varchar(500)')
                ),
                @Cantidad = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cbc:InvoicedQuantity)[1]','decimal(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cbc:CreditedQuantity)[1]','decimal(18,2)')
                ),
                @ValorTotal = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cbc:LineExtensionAmount)[1]','decimal(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cbc:LineExtensionAmount)[1]','decimal(18,2)')
                ),
                @ValorUnitario = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:Price/cbc:PriceAmount)[1]','numeric(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:Price/cbc:PriceAmount)[1]','numeric(18,2)')
                ),
                @ValorIva = COALESCE(
                    @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="01"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)'),
                    @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="01"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)')
                    ),
                @ValorINC = COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="04"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="04"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)')
                ),
                @ValorIBUA = COALESCE(
                    @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="34"]/cbc:TaxAmount)[1]','decimal(18,2)'),
                    @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="34"]/cbc:TaxAmount)[1]','decimal(18,2)')
                ),
                @ValorICUI =COALESCE(
                  @XML.value('(/inv:Invoice/cac:InvoiceLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="35"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)'),
                  @XML.value('(/cn:CreditNote/cac:CreditNoteLine[sql:variable("@Index")]/cac:TaxTotal/cac:TaxSubtotal[cac:TaxCategory/cac:TaxScheme/cbc:ID="35"]/cac:TaxCategory/cbc:Percent)[1]','decimal(18,2)')
                ),
                @FactorDescuento = COALESCE(
                    @XML.value('(//inv:Invoice/cac:InvoiceLine[position()=sql:variable("@Index")]/cac:AllowanceCharge/cbc:MultiplierFactorNumeric)[1]','decimal(18,2)'),
                    @XML.value('(//cn:CreditNote/cac:CreditNoteLine[position()=sql:variable("@Index")]/cac:AllowanceCharge/cbc:MultiplierFactorNumeric)[1]','decimal(18,2)')
                );

            SET @ValorINC = COALESCE(@ValorINC, 0);
            SET @ValorIBUA = COALESCE(@ValorIBUA, 0);
            SET @ValorICUI = COALESCE(@ValorICUI, 0);
            SET @TipoImpuestoSaludable = 0; --0 = Impuesto ICUI, 1 = Impuesto IBUA
            IF @ValorIBUA > 0
                SET @TipoImpuestoSaludable = 1;


            -- Revisa si tiene el codigo personalizado o uno generico
            SET @CodInventario = COALESCE(@CodInventario3,@CodInventario2, @CodInventario);
            SET @ValorIva = COALESCE(@ValorIva, 0);
            SET @FactorDescuento = COALESCE(@FactorDescuento, 0);


            --Obtener el id del inventario
            --//
            DECLARE @CodInventarioFull NVARCHAR(50);
            DECLARE @tIdTipoInv INT = NULL;

            SET @CodInventarioFull = COALESCE(@EqCodInventario_Pref, '') + @CodInventario;

                        
			-- 3) Insertar equivalencia 
			INSERT INTO DocElectronicos_Equivalencias (
				IdTipoIdent, 
				Identificacion, 
				NombreCompleto,
				NomInventario_Operador, 
				NomInventario,
				CodInventario_Operador, 
				CodInventario,
				ValorIva, 
				IdInventario, 
				IdTipoInv, 
				IdContab, 
				CodInventario_Pref
			)
			SELECT
				@IdTipoIdent, 
				@NitProveedor, 
				@NombreProveedor,
				0, 
				@NomInventario,
				0, 
				@CodInventario,
				@ValorIva, 
				NULL, 
				NULL, 
				NULL, 
				NULL
			WHERE NOT EXISTS (
				SELECT 1
				FROM DocElectronicos_Equivalencias e
				WHERE ISNULL(LTRIM(RTRIM(e.Identificacion)),'') = ISNULL(LTRIM(RTRIM(@NitProveedor)),'')
				  AND ISNULL(LTRIM(RTRIM(e.CodInventario)),'') = ISNULL(LTRIM(RTRIM(@CodInventario)),'')
                  AND ISNULL(LTRIM(RTRIM(e.NomInventario)),'') = ISNULL(LTRIM(RTRIM(@NomInventario)),'')
			);
					   
            SET @Index = @Index + 1
        END

        -- Si todo sale bien, establecemos el éxito
        --//
        SET @Exito = 1
        SET @MensajeError = FORMATMESSAGE(N'Equivalencia del Documento ''%s'', copiada correctamente', @NumeroFacturaFull);
        RETURN
        --//
    END TRY
    BEGIN CATCH
        --si hay error hace rollback de la transacción
        IF XACT_STATE() <> 0    
            ROLLBACK TRANSACTION;

        -- Si hay error, enviamos el msg de error
        --//
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        SET @Exito = 0
        SET @MensajeError = @ErrorMsg;
        --//
    END CATCH;
END;
GO
	/*
	Plantilla de script posterior a la implementación							
	--------------------------------------------------------------------------------------
	 Este archivo contiene instrucciones de SQL que se anexarán al script de compilación.		
	 Use la sintaxis de SQLCMD para incluir un archivo en el script posterior a la implementación.			
	 Ejemplo:      :r .\miArchivo.sql								
	 Use la sintaxis de SQLCMD para hacer referencia a una variable en el script posterior a la implementación.		
	 Ejemplo:      :setvar TableName miTabla							
				   SELECT * FROM [$(TableName)]					
	--------------------------------------------------------------------------------------
	*/

	--Actualizar Razon Social Banco Caja Social BCSC
	UPDATE Clientes Set Nombre1 = 'BANCO CAJA SOCIAL S.A.' Where IdCliente = 127;

	--Actualizar direcciones email y codigo postal
	UPDATE Clientes_Direcciones 
	Set Direccion = 'CR 7 77 65 P 9',
		DireccionFormato1 = 'CR 7 77 65 P 9',
		DireccionFormato2 = 'CR 7 77 65 P 9',
		EMail = 'notificacionesdianbcs@fundaciongruposocial.co',
		CodigoPostal = '110221'
	Where IdCliente = 127;
GO

GO
PRINT N'Update complete.';


GO


--Actualizar datos de la version

IF NOT EXISTS (SELECT 1 FROM dbo.ZiurVersiones WHERE NumVersion = 981)
BEGIN
    INSERT INTO dbo.ZiurVersiones (IdVersion, NumVersion, Descripcion)
    VALUES (391, 981, 'Unificacion SP generar Documento y Equivalencia generacion Docs Enviados y cambio Razon Social');
END
GO


/* Liberar bloqueo exclusivo por base de datos */
DECLARE @__ZiurDbScriptLockResourceEnd NVARCHAR(200) = N'ZiurSoft:DbScriptLock:' + CAST(DB_ID() AS NVARCHAR(20));
EXEC sp_releaseapplock
    @Resource = @__ZiurDbScriptLockResourceEnd,
    @LockOwner = N'Session';
GO
