CREATE VIEW `sivir_products_list_not_homologated` AS
SELECT
    `product`.`relative` AS `relative`,
    `product`.`users_sid` AS `users_sid`,
    `product`.`global_branch_office_id` AS `global_branch_office_id`,
    `product`.`product_kind` AS `product_kind`,
    `product`.`fullname` AS `fullname`,
    `product`.`description` AS `description`,
    `product`.`code` AS `code`,
    `product`.`category_id` AS `category_id`,
    `product`.`subcategory_id` AS `subcategory_id`,
    `product`.`brand_id` AS `brand_id`,
    `product`.`images` AS `images`,
    `product`.`barcode` AS `barcode`,
    `product`.`enabled` AS `enabled`,
    `product`.`status` AS `status`,
    `product`.`created_at` AS `created_at`,
    `product`.`updated_at` AS `updated_at`,
    `price`.`price` AS `price`,
    `price`.`currency_id` AS `currency_id`,
    `currency`.`code` AS `currency`,
    `category`.`label` AS `category`,
    `stock`.`quantity` AS `quantity`,
    `stock`.`measurement_id` AS `measurement_id`,
    `measurement`.`label` AS `measurement`
FROM
    (
        (
            (
                (
                    (
                        `farmovnq_core_sivir0`.`sivir_products` `product`
                    JOIN `farmovnq_core_sivir0`.`sivir_products_price` `price`
                    )
                JOIN `farmovnq_core`.`global_currency` `currency`
                )
            JOIN `farmovnq_core_sivir0`.`sivir_catalog_items` `category`
            )
        JOIN `farmovnq_core_sivir0`.`sivir_products_stock` `stock`
        )
    JOIN `farmovnq_core_sivir0`.`sivir_catalog_items` `measurement`
    )
WHERE
    (
        (`product`.`product_kind` <> 'IAF') AND(
            CONVERT(`product`.`relative` USING utf8mb3) = `price`.`product_id`
        ) AND(
            `product`.`relative` = `stock`.`product_id`
        ) AND(
            `price`.`currency_id` = `currency`.`codigoClasificador`
        ) AND(
            `product`.`category_id` = `category`.`value`
        ) AND(
            `category`.`catalog_code` = 'CATEGORY'
        ) AND(`category`.`catalog_code_dep` = 0) AND(`category`.`catalog_id_dep` = 0) AND(
            `category`.`global_branch_office_id` = CONVERT(
                `product`.`global_branch_office_id` USING utf8mb3
            )
        ) AND(
            `stock`.`measurement_id` = `measurement`.`value`
        ) AND(
            `measurement`.`catalog_code` = 'MEASUREMENT'
        ) AND(
            `measurement`.`catalog_code_dep` = 0
        ) AND(`measurement`.`catalog_id_dep` = 0) AND(
            `measurement`.`global_branch_office_id` = CONVERT(
                `product`.`global_branch_office_id` USING utf8mb3
            )
        ) AND `product`.`relative` IN(
        SELECT
            `farmovnq_core_sivir0`.`sivir_products_code_approved`.`product_id`
        FROM
            `farmovnq_core_sivir0`.`sivir_products_code_approved`
        WHERE
            (
                `farmovnq_core_sivir0`.`sivir_products_code_approved`.`status` = 'A'
            )
    ) IS FALSE
    )
