Add new Athena API endpoints to Bouw7 / Exact Online Bouw

[awa]

Is it possible to add these ‘new’ endpoints to the Power BI connector of Bouw7.

• Bewakingscodes - https://athena.bouw7.nl/api-reference/getTotalSecurityCodes
• Hoofdstukken - https://athena.bouw7.nl/api-reference/getTotalChapters
• Objecten - https://athena.bouw7.nl/api-reference/getTotalObjects

[forums]

This idea relates to New Apollo APIs for Bouw7 (Exact Online Bouw). That related idea contains a workaround.

There are currently no plans at Invantive to add the Athena APIs. From latest information received from Exact, these APIs are no longer intended for public use.

In case a different policy applies by now, please add a reference to an Exact website URL in which it stated that Athena are available for long-term public use.

[forums]

As an alternative, you can use the following application module which can be accessed using the link available on “Copy link” on the module when the application is started (see also Automatically launch application module from Azure Data Factory):

declare
  l_unique_id           varchar2;
begin
  cloud_http.set_response_content_type('application/json');
  cloud_http.set_response_status_code(200);
  --
  -- Use the secured channel to Bouw7 to retrieve the security codes.
  --
  dbms_output.put_line('Retrieve security codes.');
  l_unique_id := to_char(newid());
  insert into Bouw7.Native.NATIVEPLATFORMSCALARREQUESTS
  ( url
  , HTTP_METHOD
  , ORIG_SYSTEM_GROUP
  , ORIG_SYSTEM_REFERENCE
  , HTTP_DISK_CACHE_USE
  , HTTP_DISK_CACHE_SAVE
  , HTTP_DISK_CACHE_MAX_AGE_SEC
  , fail_on_error
  )
  select 'https://athena.bouw7.nl/project-control/' || to_char(pjt.id) || '/total/security-codes'
  ,      'GET'
  ,      to_char(pjt.id)
  ,      l_unique_id
  ,      true
  ,      true
  ,      3600 * 20 /* 20 hours cache */
  ,      true
  from   projects pjt
  limit 100
  ;
  --
  -- Retrieve the JSON answer and wrap it once more in another
  -- JSON.
  --
  for r 
  in
  ( select rst.orig_system_group projectId
    ,      jte.payload
    from   NATIVEPLATFORMSCALARREQUESTS rst
    join   jsontable
           ( 'items[*]'
             passing rst.result_text
             columns payload varchar2 path '::self'
           ) jte
    where  orig_system_reference = l_unique_id
    for json auto
  )
  loop
    cloud_http.append_to_response_body_text(r.json);
  end loop;
end;

Please note that you will need to remove the limit 100 to include all projects. These have been added to ensure that a test case does not take forever when there are 10.000 projects.

In Power BI the JSON must be expanded twice (second time by adding an additional column using Json.Document):

let
    Bron = Json.Document(Web.Contents("https://app-online.invantive.com/apps/.../databases/acme-bouw7/modules/.../")),
    #"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "Column1", {"projectId", "payload"}, {"Column1.projectId", "Column1.payload"}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Column1 uitgevouwen", "Aangepast", each Json.Document([Column1.payload])),
    #"Aangepast uitgevouwen" = Table.ExpandRecordColumn(#"Aangepaste kolom toegevoegd", "Aangepast", {"hourInfo", "code", "name", "budgetAmount", "...", "Aangepast.pslIds"})
in
    #"Aangepast uitgevouwen"

[forums]

Bouw7 getTotalObjects

Notre that for getTotalObjects, a HTTP 400 status code is returned when there are no security objects with the payload:

{
    "code": 400,
    "status": "There are no security objects for the given project ID"
}

Use the following refined logic without automatic failure:

declare
  l_unique_id           varchar2;
begin
  cloud_http.set_response_content_type('application/json');
  cloud_http.set_response_status_code(200);
  --
  -- Use the secured channel to Bouw7 to retrieve the objects.
  --
  dbms_output.put_line('Retrieve objects.');
  l_unique_id := to_char(newid());
  insert into Bouw7.Native.NATIVEPLATFORMSCALARREQUESTS
  ( url
  , HTTP_METHOD
  , ORIG_SYSTEM_GROUP
  , ORIG_SYSTEM_REFERENCE
  , HTTP_DISK_CACHE_USE
  , HTTP_DISK_CACHE_SAVE
  , HTTP_DISK_CACHE_MAX_AGE_SEC
  , fail_on_error
  )
  select 'https://athena.bouw7.nl/project-control/' || to_char(pjt.id) || '/total/objects'
  ,      'GET'
  ,      to_char(pjt.id)
  ,      l_unique_id
  ,      true
  ,      true
  ,      3600 * 20 /* 20 hours cache */
  ,      false
  from   projects pjt
  limit 100
  ;
  --
  -- Retrieve the JSON answer and wrap it once more in another
  -- JSON.
  --
  for r 
  in
  ( select rst.orig_system_group projectId
    ,      jte.payload
    from   NATIVEPLATFORMSCALARREQUESTS rst
    join   jsontable
           ( 'items[*]'
             passing rst.result_text
             columns payload varchar2 path '::self'
           ) jte
    where  orig_system_reference = l_unique_id
    --
    -- Ignore HTTP 400 caused by "There are no security objects for the given project ID."
    --
    and    ( successful or coalesce(error_message_code, 'x') != 'itgenclr217' )
    for json auto
  )
  loop
    cloud_http.append_to_response_body_text(r.json);
  end loop;
end;