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

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

  /