Notes

FOR rec IN products_cursor LOOP
-- SEARCH THE PRODUCT IN THE DIMENSION BY SELECTING SCD2 ATTRIBUTES
BEGIN
SELECT product_key, NVL(UPPER(product_size_package),-1),NVL(UPPER(product_type_package),-1),UPPER(product_diet_type),NVL(product_liquid_weight,-1)
INTO v_product_key,v_size_package,v_type_package,v_diet_type,v_liquid_weight
FROM t_dim_product
WHERE product_natural_key=rec.id AND is_expired_version='NO';

-- IF A RECORD WAS FOUND, THEN THE SOURCE PRODUCT IS IN FACT A NEW VERSION:
-- DID ANY OF THE SCD2 ATTRIBUTES CHANGE?
IF NVL(UPPER(rec.pack_size),-1)!=v_size_package OR NVL(UPPER(rec.pack_type),-1) != v_type_package OR UPPER(rec.diet_type) != v_diet_type OR NVL(rec.liq_weight,-1) != v_liquid_weight
THEN
-- EXPIRES LAST AND INSERT NEW
UPDATE T_DIM_PRODUCT
SET IS_EXPIRED_VERSION = 'YES'
WHERE PRODUCT_KEY=V_PRODUCT_KEY;

-- INSERT NEW VERSION
INSERT INTO T_DIM_PRODUCT
(PRODUCT_KEY,PRODUCT_NATURAL_KEY,PRODUCT_NAME,PRODUCT_BRAND,PRODUCT_CATEGORY,PRODUCT_SIZE_PACKAGE,PRODUCT_TYPE_PACKAGE,PRODUCT_DIET_TYPE,PRODUCT_LIQUID_WEIGHT,IS_EXPIRED_VERSION)
VALUES(seq_dim_product.NEXTVAL,rec.id, rec.name, rec.brand,rec.category_name,rec.pack_size, rec.pack_type,rec.diet_type,rec.liq_weight,'NO');

v_new_versions:=v_new_versions+1;
ELSE
--UPDATES VERSION IN T_DIM
UPDATE T_DIM_PRODUCT
SET
PRODUCT_NAME=REC.NAME,
PRODUCT_BRAND=REC.BRAND,
PRODUCT_CATEGORY=REC.CATEGORY_NAME
WHERE V_PRODUCT_KEY = PRODUCT_KEY;

--COUNT NEW VERSION
v_old_versions:=v_old_versions+1;

END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- IF NOT FOUND, THEN ITS A NEW PRODUCT
INSERT INTO t_dim_product (product_key,PRODUCT_NATURAL_KEY,PRODUCT_NAME,PRODUCT_BRAND,PRODUCT_CATEGORY,PRODUCT_SIZE_PACKAGE,PRODUCT_TYPE_PACKAGE,PRODUCT_DIET_TYPE,PRODUCT_LIQUID_WEIGHT,IS_EXPIRED_VERSION)
VALUES (seq_dim_product.NEXTVAL, rec.id,rec.name,rec.brand,rec.category_name,rec.pack_size,rec.pack_type,rec.diet_type,rec.liq_weight,'NO');
v_new_products:=v_new_products+1;
END;
END LOOP;