Bouw7 Athena API security codes per kostensoort

Vragen
,
2

In release 24.0.739 en later is er een domain value CostTypes die hiervoor gebruikt kan worden als in onderstaande code voor gebruik met Power BI zoals beschreven in Add new Athena API endpoints to Bouw7 / Exact Online Bouw :

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) 
         || '/cost-type/' 
         || cte.code 
         || '/security-codes'
  ,      'GET'
  ,      ''
         || '{'
         || jsonelement('project_id', pjt.id)
         || ', '
         || jsonelement('cost_type_code', cte.code)
         || ', '
         || jsonelement('cost_type_description', cte.description)
         || '}'
         orig_system_group
  ,      l_unique_id
  ,      true
  ,      true
  ,      3600 * 20 /* 20 hours cache */
  ,      true
  from   projects pjt
  join   CostTypes cte
  ;
  --
  -- Retrieve the JSON answer and wrap it once more in another
  -- JSON.
  --
  for r
  in
  ( select jm.project_id
    ,      jm.cost_type_code
    ,      jm.cost_type_description
    ,      jte.payload
    from   NATIVEPLATFORMSCALARREQUESTS rst
    join   jsontable
           ( ''
             passing rst.orig_system_group
             columns project_id            varchar2 path 'project_id'
             ,       cost_type_code        varchar2 path 'cost_type_code'
             ,       cost_type_description varchar2 path 'cost_type_description'
           ) jm 
    join   jsontable
           ( 'items[*]'
             passing rst.result_text
             columns payload varchar2 path '::self'
           ) jte
    where  rst.orig_system_reference = l_unique_id
    for json auto
  )
  loop
    --dbms_output.put_line(r.json);
    cloud_http.append_to_response_body_text(r.json);
  end loop;
end;