jon-dixon

DECLARE

  lt_parm_names     apex_application_global.VC_ARR2;
  lt_parm_values    apex_application_global.VC_ARR2;
  l_response        CLOB;
  l_rest_api_url    VARCHAR2(255);
  l_item_obj        json_object_t := json_object_t();
  l_json_obj        json_object_t := json_object_t();
  l_msgs_arr        json_array_t  := json_array_t();
  l_del_json_obj    json_object_t := json_object_t();

jon-dixon

DECLARE
  l_payload         CLOB;
  l_response        CLOB;
  l_rest_api_url    VARCHAR2(255);
  l_item_obj        json_object_t := json_object_t();
  l_content_obj     json_object_t := json_object_t();
  l_json_obj        json_object_t := json_object_t();
  l_msgs_arr        json_array_t  := json_array_t();
BEGIN
  -- Handle Create/Update of Interactive Grid Record.

jon-dixon

DECLARE
  -- Cursor to return the parsed CSV file.
  CURSOR cr_match_types (cp_report_file_url IN VARCHAR2) IS
    SELECT col001 mt_name
    ,      col002 mt_text_id
    ,      col003 mt_status
    ,      col004 mt_update_date
    ,      col005 ds_name
    ,      col006 ds_text_id
    ,      col007 dynamic_table_name

jon-dixon

PROCEDURE run_csv_report
  (p_group_name  IN VARCHAR2,
   p_report_name IN VARCHAR2,
   p_params_json IN VARCHAR2,
   p_module      IN VARCHAR2,
   p_run_async   IN BOOLEAN,
   x_file_url   OUT NOCOPY VARCHAR2,
   x_status_rec OUT NOCOPY status_rec) IS

  l_report_file_name     VARCHAR2(500);

jon-dixon

DECLARE
  lc_arcs_base_url       CONSTANT VARCHAR2(100) := '<BaseURL>';
  lc_job_name            CONSTANT VARCHAR2(100) := 'importtmpremappedtransactions';
  l_job_params           VARCHAR2(1000);
  l_job_status_url       VARCHAR2(500);
  l_file_name            VARCHAR2(100);
  l_job_complete         VARCHAR2(1);
  l_job_status_code      NUMBER;
  l_jobs_status_details  VARCHAR2(32000);
BEGIN

jon-dixon

DECLARE
  lc_arcs_job_status_url CONSTANT VARCHAR2(100) := '<BaseURL>/arm/rest/v1/jobs/100000003540031';
  l_job_complete         VARCHAR2(1);
  l_job_status_code      NUMBER;
  l_jobs_status_details  VARCHAR2(32000);
BEGIN

  -- Call the helper procedure to Start the Import Job.
  arcs_utl_pk.job_status
   (p_job_status_url     => lc_arcs_job_status_url,

jon-dixon

PROCEDURE job_status
 (p_job_status_url      IN VARCHAR2,
  x_job_complete       OUT NOCOPY VARCHAR2,
  x_job_status_code    OUT NOCOPY NUMBER,
  x_job_status_details OUT NOCOPY VARCHAR2) IS

  l_response_clob    CLOB;
  l_response_obj     JSON_OBJECT_T;

BEGIN

jon-dixon

DECLARE
  lc_arcs_base_url       CONSTANT VARCHAR2(100) := '<BaseURL>';
  lc_job_name            CONSTANT VARCHAR2(100) := 'importtmpremappedtransactions';
  l_job_params           VARCHAR2(1000);
  l_job_status_url       VARCHAR2(500);
  l_file_name            VARCHAR2(100);
BEGIN

  -- Build up the parameters JSON for the Import Transactions Job.
  -- Set the file name. My previous blog showed how to send a file to the ARCS file system.

jon-dixon

PROCEDURE start_job
 (p_base_url        IN VARCHAR2,
  p_job_name        IN VARCHAR2,
  p_params_json     IN VARCHAR2,
  x_job_status_url OUT NOCOPY VARCHAR2) IS

  l_complete_url     VARCHAR2(500);
  lc_job_path        CONSTANT VARCHAR2(100) := 'arm/rest/v1/jobs';
  l_payload_json     VARCHAR2(4000);
  l_response_clob    CLOB;

jon-dixon

DECLARE
  l_file_name        VARCHAR2(100);
  lc_arcs_base_url   CONSTANT VARCHAR2(100) := 'https://<baseURL>/';
BEGIN

  l_file_name := 'AAA.csv';
  
  -- Call PL/SQL Procedure to Call the ARCS Delete REST API.
  arcs_utl_pk.delete_file
   (p_base_url    => lc_arcs_base_url,