How can I use a loop to properly increment through data coming back from oracle ERP?

Question

Working on calling a report in Oracle ERP to get data from the server and sync it with a table in Oracle Apex. The data is huge so I need to sync the data at 10K rows at a time. I dont know if I am getting the l_row_count correctly. I log the value for every loop and its 10K, 20K, 30K, etc and not 10K, 10K, 5673 (25673 rows total). Am I looping wrong? Do I need to clear out the data from the xmlTable?

    v_update in varchar2 default null
)
as
 
    l_path varchar2(200) := '/Custom/report.xdo';
    l_status number;
    l_res clob;
    l_report_parameters clob;
    l_interval number := 10000;
    l_from_num number := 1;
    l_to_num number := l_interval;
    l_loop boolean := true;
    l_row_count number := 0;
    L_LOOP_COUNT number := 0;
    v_error_message VARCHAR2(32767);
 
BEGIN
 
    while l_loop Loop
 
        BEGIN
 
        APEX_AUTOMATION.LOG_INFO ('Starting' || SYSDATE);
 
        l_loop_count := L_loop_Count +1;
 
        APEX_AUTOMATION.LOG_INFO ('l_from_num-->>  ' ||l_from_num);
        APEX_AUTOMATION.LOG_INFO ('l_to_num-->>  ' ||l_to_num);
        APEX_AUTOMATION.LOG_INFO ('L_loop_Count -->>  ' ||L_loop_Count );
 
        l_report_parameters := '<pub:parameterNameValues>
<pub:item>
<pub:name>p_from_num</pub:name>
<pub:values>
<pub:item>'|| l_from_num ||'</pub:item>
</pub:values>
</pub:item>
<pub:item>
<pub:name>p_to_num</pub:name>
<pub:values>
<pub:item>'|| l_to_num ||'</pub:item>
</pub:values>
</pub:item>
</pub:parameterNameValues>';
 
        GET_BI_REPORT(
            v_report_path => l_path,
            v_status => l_status,
            v_response => l_res,
            v_report_parameters => l_report_parameters
        );
 
        APEX_AUTOMATION.LOG_INFO ('l_res-->>  ' ||l_res);
 
    l_row_count := 0;
 
    select count(*)
    into l_row_count
        from xmltable (
            '//DATA_DS/G_1'
            passing xmltype (l_res)
            COLUMNS 
                INVENTORY_ITEM_ID NUMBER PATH 'INVENTORY_ITEM_ID'
        ) xc;
    APEX_AUTOMATION.LOG_INFO ('l_row_count-->>  ' ||l_row_count);
 
    if l_row_count < l_interval then
        APEX_AUTOMATION.LOG_INFO ('row count was false');
        l_loop := false;
    end if;
 
    
    for item in (
        select xt.*
        from xmltable (
            '//DATA_DS/G_1'
            passing xmltype (l_res)
            COLUMNS 
                --report only consists of header id's
                INVENTORY_ITEM_ID NUMBER PATH 'INVENTORY_ITEM_ID'
                ,organization_id NUMBER PATH 'organization_id'
                ,ITEM_NUMBER varchar(2000) PATH 'ITEM_NUMBER'
                ,DESCRIPTION varchar(2000) PATH 'DESCRIPTION'
                ,MANUFACTURER varchar(2000) PATH 'MANUFACTURER'
                ,SPECIAL_ORDER varchar(2000) PATH 'SPECIAL_ORDER'
                ,COLOR varchar(2000) PATH 'COLOR'
                ,INSTALLATION_TYPE varchar(2000) PATH 'INSTALLATION_TYPE'
                ,IMAGE_URL varchar(2000) PATH 'IMAGE_URL'
                ,CATEGORY_NAME varchar(2000) PATH 'CATEGORY_NAME'
                ,MSRP_PRICE NUMBER PATH 'MSRP_PRICE'
        ) xt
    )
    LOOP
        begin
 
        MERGE INTO SYNC_GET_PART_T tgt
            USING (SELECT
                       item.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
                       item.organization_id AS organization_id,
                       item.ITEM_NUMBER AS ITEM_NUMBER,
                       item.DESCRIPTION AS DESCRIPTION,
                       item.MANUFACTURER AS MANUFACTURER,
                       item.SPECIAL_ORDER AS SPECIAL_ORDER,
                       item.COLOR AS COLOR,
                       item.INSTALLATION_TYPE AS INSTALLATION_TYPE,
                       item.IMAGE_URL AS IMAGE_URL,
                       item.CATEGORY_NAME AS CATEGORY_NAME,
                       item.MSRP_PRICE AS MSRP_PRICE
                    FROM DUAL) src
        ON (tgt.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID)
        WHEN MATCHED THEN
            UPDATE
                SET tgt.organization_id = src.organization_id,
                    tgt.ITEM_NUMBER = src.ITEM_NUMBER,
                    tgt.DESCRIPTION = src.DESCRIPTION,
                    tgt.MANUFACTURER = src.MANUFACTURER,
                    tgt.SPECIAL_ORDER = src.SPECIAL_ORDER,
                    tgt.COLOR = src.COLOR,
                    tgt.INSTALLATION_TYPE = src.INSTALLATION_TYPE,
                    tgt.IMAGE_URL = src.IMAGE_URL,
                    tgt.CATEGORY_NAME = src.CATEGORY_NAME,
                    tgt.MSRP_PRICE = src.MSRP_PRICE,
                    
        WHEN NOT MATCHED THEN
            INSERT (
                    tgt.INVENTORY_ITEM_ID,
                    tgt.organization_id,
                    tgt.ITEM_NUMBER,
                    tgt.DESCRIPTION,
                    tgt.MANUFACTURER,
                    tgt.SPECIAL_ORDER,
                    tgt.COLOR,
                    tgt.INSTALLATION_TYPE,
                    tgt.IMAGE_URL,
                    tgt.CATEGORY_NAME,
                    tgt.MSRP_PRICE) 
            VALUES (
                    src.INVENTORY_ITEM_ID,
                    src.organization_id,
                    src.ITEM_NUMBER,
                    src.DESCRIPTION,
                    src.MANUFACTURER,
                    src.SPECIAL_ORDER,
                    src.COLOR,
                    src.INSTALLATION_TYPE,
                    src.IMAGE_URL,
                    src.CATEGORY_NAME,
                    src.MSRP_PRICE);
 
            exception when OTHERS then
            APEX_AUTOMATION.LOG_INFO ('SQLERRM in merge loop-->>  ' ||SQLERRM);
            APEX_AUTOMATION.LOG_INFO ('Ending in merge loop' || sysdate);
 
            end;
 
    END LOOP;
 
        l_row_count := 0;
        l_from_num := l_from_num + l_interval;
        l_to_num := l_to_num + l_interval;
 
        exception when OTHERS then
            APEX_AUTOMATION.LOG_INFO ('SQLERRM in while loop-->>  ' ||SQLERRM);
            APEX_AUTOMATION.LOG_INFO ('Ending in while loop' || sysdate);
        END;
 
    end LOOP;
 
    APEX_AUTOMATION.LOG_INFO ('Ending' || sysdate);
 
    EXCEPTION WHEN OTHERS THEN
        v_error_message := SQLERRM;
 
        APEX_AUTOMATION.LOG_INFO ('exception outside of loop' || sysdate);
        APEX_AUTOMATION.LOG_INFO ('v_error_message outside of loop' || v_error_message);
END;
/```