CREATE PROC [dbo].[SP_MALZEME_KAYIT_ETIKETLER]
(
    @CODE VARCHAR(MAX),
    @AD   VARCHAR(MAX),
    @FAB  VARCHAR(MAX)
)
AS
BEGIN
    DECLARE @ACK VARCHAR(255)
    DECLARE @MDL VARCHAR(255)
    DECLARE @BRC VARCHAR(50)
    DECLARE @LOGREF INT

    -- Öncelikle IHR veritabanindaki ITEMS tablosunda ara
    SELECT @LOGREF = LOGICALREF 
    FROM [IHR].[dbo].[ITEMS] 
    WHERE CODE = @CODE

    -- Eger IHR'de bulunamazsa UNIVERSALDB'den ara
    IF @LOGREF IS NULL
    BEGIN
        SELECT @LOGREF = LOGICALREF 
        FROM [UNIVERSALDB].[dbo].[LG_011_ITEMS] 
        WHERE CODE = @CODE
    END

    -- @ACK (Açiklama)
    SET @ACK = ISNULL(
        (SELECT REPLACE(REPLACE(ISNULL(ETIKET_ACIKLAMA,''), '''', ''), '"', '')
         FROM [UNIVERSALDB].[dbo].[LG_XT1001_011]
         WHERE PARLOGREF = @LOGREF
        ), '')

    -- @MDL (Model Bilgisi)
    SET @MDL = ISNULL(
        (SELECT CASE 
                    WHEN CHARINDEX('/', REPLACE(REPLACE(ISNULL(MODEL_BILGI,''), '''', ''), '"', '')) > 0 
                        THEN LEFT(REPLACE(REPLACE(ISNULL(MODEL_BILGI,''), '''', ''), '"', ''),
                                  CHARINDEX('/', REPLACE(REPLACE(ISNULL(MODEL_BILGI,''), '''', ''), '"', '')) - 1)
                    ELSE REPLACE(REPLACE(ISNULL(MODEL_BILGI,''), '''', ''), '"', '')
                END
         FROM [UNIVERSALDB].[dbo].[LG_XT1001_011]
         WHERE PARLOGREF = @LOGREF
        ), '')

    -- @BRC (Barcode)
    SET @BRC = ISNULL(
        (SELECT EXPCTGNO 
         FROM [UNIVERSALDB].[dbo].[LG_011_ITEMS]
         WHERE LOGICALREF = @LOGREF), '')

    -- Son olarak ekleme islemi
    INSERT INTO [IHR].[dbo].[MALZEME_ETIKETLER] 
           ([OEM], [ACIKLAMA], [MODEL], [FIRMA], [FABRIKA], [ADET], [BARCODE])
    VALUES (@CODE, @ACK, @MDL, @AD, @FAB, 1, @BRC)
END