Skip to content

Instantly share code, notes, and snippets.

@jon-dixon
Last active December 28, 2022 14:15
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
ARCS Run Custom Report APEX Process
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment