# Oracle Cloud ERP Extracts Part 3: Building FULL Load Data Flows and Workflows

This is the third video in the series focused on building comprehensive data extracts from **Oracle Fusion Cloud ERP** using **Oracle Data Transforms**. The series begins with foundational concepts and techniques for creating ERP data extracts and concludes by demonstrating the value of a well-designed data model in **Autonomous Data Warehouse (ADW)** and how that model can be leveraged in **Oracle Analytics Cloud (OAC)**.

This video demonstrates how to build a FULL Load of the Accounts Payable Distributions table. It details the different setups and additional steps needed for a FULL table extract vs. a typical Incremental extract.

Oracle Cloud ERP Extracts - Part 3 - Building Full Extract Data Flows using Oracle Data Transforms - YouTube

## PL SQL Procedure Script

```sql
--CREATE PROCEDURE
create or replace PROCEDURE PRC_REST_BICC_REST_PVO_TO_FULL_EXTRACT
(
    USERNAME IN VARCHAR2,
    PASSWORD IN VARCHAR2,
    in_datastore_id in varchar2,
    in_url in varchar2
) AS
BEGIN
    declare
        v_req utl_http.req;
        v_response clob;
        v_res utl_http.resp;
        v_buffer varchar2(4000);
        v_body varchar2(4000) := '{"field":"value"}'; -- Your JSON
        v_url varchar2(4000) := in_url;
        v_datastore_id varchar2(4000) := in_datastore_id; --
        l_resp clob;
    begin
        dbms_output.put_line('start' );
        -- Set connection and invoke REST API.
        v_url := v_url || '/rest/meta/datastores/' || v_datastore_id || '/actions/resetToFull';
        dbms_output.put_line('v_url : ' || v_url );
        -- Set connection.
        v_req := utl_http.begin_request(v_url, 'POST');
        utl_http.set_authentication(v_req, USERNAME,PASSWORD);
        utl_http.set_header(v_req, 'content-type', 'application/json');
        utl_http.set_header(v_req, 'Content-Length', length(v_body));
        -- Invoke REST API.
        utl_http.write_text(v_req, v_body);
        -- Get response.
        v_res := utl_http.get_response(v_req);
        begin
            loop
                utl_http.read_line(v_res, v_buffer);
                -- Do something with buffer.
                dbms_output.put_line(v_buffer);
            end loop;
            utl_http.end_response(v_res);
        exception
            when utl_http.end_of_body then
                utl_http.end_response(v_res);
        end;
    end;
END PRC_REST_BICC_REST_PVO_TO_FULL_EXTRACT;
```

## PL SQL Run Procedure Code Block

```sql
--CALL PROCEDURE
DECLARE
    USERNAME VARCHAR2(200);
    PASSWORD VARCHAR2(200);
    IN_DATASTORE_ID VARCHAR2(200);
    IN_URL VARCHAR2(200);
BEGIN
    USERNAME := 'BICC_USER';
    PASSWORD := 'BICC_PASSWORD';
    IN_DATASTORE_ID := 'FscmTopModelAM.FinExtractAM.ApBiccExtractAM.InvoiceDistributionExtractPVO';
    IN_URL := 'https://your_fusion_bicc_url.com/biacm';

--ENTER your custom DB User Name as the procedure owner below
    YOUR_DB_USER_NAME.PRC_REST_BICC_REST_PVO_TO_FULL_EXTRACT(
        USERNAME => USERNAME,
        PASSWORD => PASSWORD,
        IN_DATASTORE_ID => IN_DATASTORE_ID,
        IN_URL => IN_URL
    );
    --rollback;
END;
```