INLJ.sql

--------------------------------------------------------------------------------
--                               DECLARE CURSOR                               --
--------------------------------------------------------------------------------
DECLARE
  CURSOR TRANSACTIONSCURSOR  IS  SELECT * FROM TRANSACTIONS;
  /*TYPE NT_TRANSACTIONS IS TABLE OF VARCHAR2(20);*/
  TYPE NT_TRANSACTIONS IS TABLE OF TRANSACTIONSCURSOR%ROWTYPE;
  T_ID NT_TRANSACTIONS;
  /* CREATE VARIABLES TO STORE MD */
  -- PRODUCT MD TABLE
  MDPRODUCT_ID    PRODUCTS.PRODUCT_ID%TYPE;
  MDPRODUCT_NAME  PRODUCTS.PRODUCT_NAME%TYPE;
  MDSUPPLIER_ID   PRODUCTS.SUPPLIER_ID%TYPE;
  MDSUPPLIER_NAME PRODUCTS.SUPPLIER_NAME%TYPE;
  MDPRICE         PRODUCTS.PRICE%TYPE;
  -- CUSTOMER MD TABLE
  MDCUSTOMER_ID   CUSTOMERS.CUSTOMER_ID%TYPE;
  MDCUSTOMER_NAME CUSTOMERS.CUSTOMER_NAME%TYPE;
/* Record Counter */
REC int;
  
--------------------------------------------------------------------------------
--                               DATA EXTRACTION                              --
--------------------------------------------------------------------------------
BEGIN
OPEN TRANSACTIONSCURSOR;
LOOP /* LOOP OVER TRANSACTIONS TABLE */
  /* READ BATCHES OF 100 TUPLES FROM TRANSACTIONS TABLE INTO CURSOR */
  FETCH TRANSACTIONSCURSOR BULK COLLECT INTO T_ID LIMIT 100;
  EXIT WHEN TRANSACTIONSCURSOR%NOTFOUND;
  /* READ CURSOR TUPLE BY TUPLE */
  FOR i IN T_ID.FIRST .. T_ID.LAST
    LOOP

/* RETRIEVE RELEVANT TUPLES FROM PRODUCTS MD */
   -- PRODUCTS MD TABLE
      SELECT  PRODUCT_ID, PRODUCT_NAME, SUPPLIER_ID, SUPPLIER_NAME, PRICE
        INTO  MDPRODUCT_ID, MDPRODUCT_NAME, 
              MDSUPPLIER_ID, MDSUPPLIER_NAME, 
              MDPRICE
        FROM  PRODUCTS WHERE PRODUCT_ID = T_ID(i).PRODUCT_ID;
   -- CUSTOMERS MD TABLE
      SELECT  CUSTOMER_ID, CUSTOMER_NAME
        INTO  MDCUSTOMER_ID, MDCUSTOMER_NAME
        FROM  CUSTOMERS WHERE CUSTOMER_ID = T_ID(i).CUSTOMER_ID;

/* CHECK IF INFO EXISTS IN DIMENSION TABLES. 
   IF YES: UPDATE FACT TABLE. 
   IF NO: UPDATE ALL */
   -- D_CUSTOMERS
      SELECT COUNT(0) INTO REC FROM D_CUSTOMERS 
        WHERE CUSTOMER_ID = T_ID(i).CUSTOMER_ID;
      IF REC = 0 THEN
        INSERT INTO D_CUSTOMERS(CUSTOMER_ID, CUSTOMER_NAME)
          VALUES (T_ID(i).CUSTOMER_ID, MDCUSTOMER_NAME);
      END IF;
      
   -- D_PRODUCTS
      SELECT COUNT(0) INTO REC FROM D_PRODUCTS 
        WHERE PRODUCT_ID = T_ID(i).PRODUCT_ID;
      IF REC = 0 THEN
        INSERT INTO D_PRODUCTS(PRODUCT_ID, PRODUCT_NAME)
          VALUES (T_ID(i).PRODUCT_ID, MDPRODUCT_NAME);
      END IF;
      
   -- D_STORES
      SELECT COUNT(0) INTO REC FROM D_STORES 
        WHERE STORE_ID = T_ID(i).STORE_ID;
      IF REC = 0 THEN
        INSERT INTO D_STORES(STORE_ID, STORE_NAME)
          VALUES (T_ID(i).STORE_ID, T_ID(i).STORE_NAME);
      END IF;
      
   -- D_SUPPLIERS
      SELECT COUNT(0) INTO REC FROM D_SUPPLIERS 
        WHERE SUPPLIER_ID = MDSUPPLIER_ID;
      IF REC = 0 THEN
        INSERT INTO D_SUPPLIERS(SUPPLIER_ID, SUPPLIER_NAME)
          VALUES (MDSUPPLIER_ID, MDSUPPLIER_NAME);
      END IF;
      
   -- D_TIME
      SELECT COUNT(0) INTO REC FROM D_TIME WHERE TIME_ID = T_ID(i).TIME_ID;
      IF REC = 0 THEN
        INSERT INTO D_TIME(TIME_ID, CAL_DATE, CAL_DAY, 
                           CAL_MONTH, CAL_QUARTER, CAL_YEAR)
          VALUES (T_ID(i).TIME_ID, T_ID(i).T_DATE,
                  EXTRACT(DAY FROM T_ID(i).T_DATE),
                  TO_CHAR(T_ID(i).T_DATE,'MON'),
                  TO_CHAR(T_ID(i).T_DATE,'Q'),
                  EXTRACT(YEAR FROM T_ID(i).T_DATE)
                 );
      END IF;

   -- W_FACTS
      SELECT COUNT(0) INTO REC FROM W_FACTS 
        WHERE TRANSACTION_ID = T_ID(i).TRANSACTION_ID;
      IF REC = 0 THEN
        INSERT INTO W_FACTS(TRANSACTION_ID, CUSTOMER_ID, PRODUCT_ID, STORE_ID, 
                            SUPPLIER_ID, TIME_ID, QUANTITY, PRICE, SALE)
          VALUES (T_ID(i).TRANSACTION_ID, T_ID(i).CUSTOMER_ID, 
                  T_ID(i).PRODUCT_ID, T_ID(i).STORE_ID, MDSUPPLIER_ID, 
                  T_ID(i).TIME_ID, T_ID(i).QUANTITY, MDPRICE, 
                  T_ID(i).QUANTITY*MDPRICE
                 );
      END IF;
      commit;
    END LOOP;
    commit;
END LOOP;
CLOSE TRANSACTIONSCURSOR;
END;