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

--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

--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;