ARCS Run Custom Report APEX Process

arcs_run_report_apex_process.sql

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
    ,      col008 ds_update_date
    FROM   TABLE(apex_data_parser.parse
                  -- Use apex_web_service.make_rest_request_b to fetch the CSV file from the ARCS 
                  --  file system and feed tghe returned BLOB into apex_data_parser.parse
                  (p_content => apex_web_service.make_rest_request_b
                                 (p_url                  => cp_report_file_url,
                                  p_http_method          => 'GET',
                                  p_credential_static_id => 'ARCS_BASIC_AUTH'),
                   p_detect_data_types => 'N',
                   p_skip_rows         => 1,
                   p_file_name         => 'test.csv'));

  l_file_name            VARCHAR2(100);
  lr_status_rec          arcs_utl_pk.status_rec;
  l_report_file_url      VARCHAR2(500);

BEGIN
  -- Call API to run the custom report and return the URL to the CSV file
  --   that was generated on the ARCS file system.
  arcs_utl_pk.run_csv_report
    (p_base_url    => '<BaseURL>',
     p_group_name  => 'Integrations',
     p_report_name => 'Custom Data Sources by Match Type',
     p_params_json => NULL,
     p_module      => 'TM',
     p_run_async   => FALSE,
     x_file_url    => l_report_file_url,
     x_status_rec  => lr_status_rec);

  IF lr_status_rec.status_code = 'S' THEN
    -- If the Report ran without error, then fetch and parse the CSV file,
    --   and add the records to an APEX Collection.
    apex_collection.create_or_truncate_collection(p_collection_name => 'ARCS_MATCH_TYPES');
    -- Loop through the records returned from APEX_DATA_
    FOR mt_rec IN cr_match_types (cp_report_file_url => l_report_file_url) LOOP
      apex_collection.add_member
       (p_collection_name => 'ARCS_MATCH_TYPES',
        p_c001            => mt_rec.mt_name,
        p_c002            => mt_rec.mt_text_id,
        p_c003            => mt_rec.mt_status,
        p_c004            => mt_rec.ds_name,
        p_c005            => mt_rec.ds_text_id,
        p_c006            => mt_rec.dynamic_table_name,
        p_d001            => TO_DATE(mt_rec.mt_update_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
        p_d002            => TO_DATE(mt_rec.ds_update_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'));
    END LOOP;
    :P50_ARCS_FETCH_COMPLETE := 'Y';
  ELSE
    apex_error.add_error
     (p_message          => lr_status_rec.status_msg,
      p_display_location => apex_error.c_inline_in_notification );
  END IF;  
END;