Upload Via Excel to DB in Apex (By Using Exel2Collection Plugin)

 DECLARE
BEGIN
    IF NOT APEX_COLLECTION.COLLECTION_EXISTS('SHOP_ROUTE') THEN
        APEX_COLLECTION.CREATE_COLLECTION('SHOP_ROUTE');
    END IF;
END;
DECLARE
    vId NUMBER;
    cnt_route NUMBER;
    cnt_channel NUMBER;
    cnt_city NUMBER;
    cnt_outlet Number;
    cnt_sub_outlet Number;
    v_outlet_ID NUMBER;
    -- Distinct Routes
    CURSOR R1 IS
        SELECT DISTINCT C004 AS DIS_ROUTE
        FROM APEX_COLLECTIONS C
        WHERE C.COLLECTION_NAME = 'SHOP_ROUTE';
    
    -- Distinct Channels
    CURSOR CH1 IS
        SELECT DISTINCT C005 AS DIS_CHANNEL
        FROM APEX_COLLECTIONS C
        WHERE C.COLLECTION_NAME = 'SHOP_ROUTE';
    -- Distinct City
    CURSOR CT1 IS
        SELECT DISTINCT C007 AS DIS_CITY
        FROM APEX_COLLECTIONS C
        WHERE C.COLLECTION_NAME = 'SHOP_ROUTE';
    CURSOR OL1 IS
        SELECT DISTINCT C008 AS DIS_OUTLET
        FROM APEX_COLLECTIONS C
        WHERE C.COLLECTION_NAME = 'SHOP_ROUTE';
    CURSOR SOL1(vOutletId nvarchar2)  IS
        SELECT DISTINCT C009 AS DIS_SUB_OUTLET
        FROM APEX_COLLECTIONS C
        WHERE C.COLLECTION_NAME = 'SHOP_ROUTE' and C008 = vOutletId;
    CURSOR C1 IS
        SELECT SEQ_ID, 
               C001 AS CUSTOMER_NAME, 
               C002 AS CUSTOMER_STATUS, 
               C003 AS CUSTOMER_TYPE, 
               C004 AS ROUTE,
               C005 AS CHANNEL, 
               C006 AS ADDRESS, 
               C007 AS CITY,
               C008 AS OUTLET, 
               C009 AS SUBOUTLET, 
               C010 AS GPS_LAT,
               C011 AS GPS_LONG,
               C012 AS PRICELIST
          FROM APEX_COLLECTIONS C
         WHERE C.COLLECTION_NAME = 'SHOP_ROUTE';
BEGIN
-- Inserting OUTLETS into setup_OUTLETS
    For ol IN OL1 LOOP
    -- Master Outlet
        SELECT COUNT(*) INTO cnt_outlet
        FROM setup_outlets
        WHERE TRIM(UPPER(outlet_type)) = TRIM(UPPER(ol.DIS_OUTLET));
        IF cnt_outlet = 0 THEN
            INSERT INTO setup_outlets (OUTLET_ID, OUTLET_TYPE, status, user_id, CREATEDBY, CREATEDDATE)
            VALUES (seq_outlet.NEXTVAL, ol.DIS_OUTLET, 'A', :USER_ID, name_of.user_name(:USER_ID), SYSDATE);
        END IF;
    -- Detail Outlet
        FOR sol IN SOL1(ol.DIS_OUTLET) LOOP
            
            SELECT COUNT(*) INTO cnt_sub_outlet
            FROM SETUP_SUB_OUTLETS
            WHERE TRIM(UPPER(OUTLET_ID)) = TRIM(UPPER(ol.DIS_OUTLET)) AND TRIM(UPPER(OUTLET_SUB_ID)) = TRIM(UPPER(sol.DIS_SUB_OUTLET));
            IF cnt_sub_outlet = 0 THEN
                SELECT name_of.get_outlet_id(ol.DIS_OUTLET) INTO v_outlet_ID  FROM DUAL;
                INSERT INTO SETUP_SUB_OUTLETS (OUTLET_ID,OUTLET_SUB_ID,OUTLET_SUB_TYPE, status, user_id, CREATEDBY, CREATEDDATE)
                VALUES(v_outlet_ID,seq_sub_outlet.NEXTVAL,sol.DIS_SUB_OUTLET,  'A', :USER_ID, name_of.user_name(:USER_ID), SYSDATE );
            END IF;
        END LOOP;


    END LOOP;


-- Inserting Routes into setup_routes
    For r IN R1 LOOP
        SELECT COUNT(*) INTO cnt_route
        FROM setup_routes
        WHERE TRIM(UPPER(route_name)) = TRIM(UPPER(r.DIS_ROUTE));
        IF cnt_route = 0 THEN
            INSERT INTO setup_routes (route_id, route_name, status, user_id, CREATE_BY, CREATION_DATE)
            VALUES (seq_routes.NEXTVAL, r.DIS_ROUTE, 'A', :USER_ID, name_of.user_name(:USER_ID), SYSDATE);
        END IF;
    END LOOP;
-- Inserting Channels into setup_channels 
    For ch IN CH1 LOOP
        SELECT COUNT(*) INTO cnt_channel
        FROM setup_channels
        WHERE TRIM(UPPER(CHAN_NAME)) = TRIM(UPPER(ch.DIS_CHANNEL));
       
        IF cnt_channel = 0 THEN
            INSERT INTO setup_channels (CHAN_ID, CHAN_NAME, STATUS, user_id, CREATE_BY, CREATION_DATE)
            VALUES (seq_channel.NEXTVAL, ch.DIS_CHANNEL, 'A', :USER_ID, name_of.user_name(:USER_ID), SYSDATE);
        END IF;
    END LOOP;
-- Inserting Cities into city
    For ct IN CT1 LOOP
        SELECT COUNT(*) INTO cnt_city
        FROM city
        WHERE TRIM(UPPER(CITY_NAME)) = TRIM(UPPER(ct.DIS_CITY));
        IF cnt_city = 0 THEN
            INSERT INTO city (CITY_CODE, CITY_NAME, status, user_id, CREATEDBY, CREATEDDATE)
            VALUES (seq_city.NEXTVAL, ct.DIS_CITY, 'A', :USER_ID, name_of.user_name(:USER_ID), SYSDATE);
        END IF;
    END LOOP;
-- Inserting Data into setup_last_account
    FOR i IN C1 LOOP
        INSERT INTO setup_last_account(
            sub_acc_id, 
            last_acc_id, 
            last_acc_name, 
            Status, 
            Business_type, 
            ROUTE_ID, 
            CHANNEL_ID, 
            BILLING_ADDRESS, 
            CITY_NAME, 
            OUTLET_ID, 
            OUTLET_SUB_ID, 
            GPS_LAT, 
            GPS_LONG, 
            PRICELIST_ID, 
            createdby, 
            createddate
        )
        VALUES (
            1, 
            get_seq.last_account, 
            i.CUSTOMER_NAME, 
            'A', 
            i.CUSTOMER_TYPE, 
            name_of.get_route_id(i.ROUTE), 
            name_of.get_channel_id(i.CHANNEL), 
            i.ADDRESS, 
            name_of.get_city_id(i.CITY), 
            name_of.get_outlet_id(i.OUTLET), 
            name_of.get_sub_outlet_id(i.SUBOUTLET, name_of.get_outlet_id(i.OUTLET)), 
            i.GPS_LAT, 
            i.GPS_LONG, 
            name_of.get_pricelist_id(i.PRICELIST), 
            name_of.user_name(:USER_ID), 
            SYSDATE
        );
    END LOOP;
    IF APEX_COLLECTION.COLLECTION_EXISTS('SHOP_ROUTE') THEN
        APEX_COLLECTION.TRUNCATE_COLLECTION(P_COLLECTION_NAME => 'SHOP_ROUTE');
    END IF;
END;