Toegang vanuit Power BI tot bewakingscodes in Bouw7

Vragen
,
5

De nieuwe project-security-links API is geen onderdeel van Bouw7 Apollo, maar van Heimdal.

Het ophalen via Swagger (specificatie van de Heimdal API van Bouw7) is nog niet mogelijk omdat project-security-links hier nog niet in gedocumenteerd is.

De hoofdbewakingscodes zijn te bepalen voor een project via:

select *
from   jsontable
       ( '[*].securityCodesPerChapters[*].securityCodeChapter'
         passing json
         columns id   int      path 'id'
         ,       name varchar2 path 'name'
         ,       code varchar2 path 'code'
       )

De bewakingscodes / kostensoorten zijn op te halen voor een project via:

select *
from   jsontable
       ( '[*].securityCodesPerChapters[*].budgetDataPerSecurityCodes[*]'
         passing json
         columns ChapterId               int      path '::ancestor.securityCodeChapter.id'
         ,       ChapterName             varchar2 path '::ancestor.securityCodeChapter.name'
         ,       ChapterCode             varchar2 path '::ancestor.securityCodeChapter.code'
         ,       SecurityCodeId          int      path 'securityCode.id'
         ,       SecurityCodeName        varchar2 path 'securityCode.name'
         ,       SecurityCodeCode        varchar2 path 'securityCode.code'
         ,       SecurityCodeChapterName varchar2 path 'securityCode.chapterName'
         ,       Name                    varchar2 path 'name'
         ,       LaborHours              number   path 'laborHours'
         ,       LaborHourlyRate         number   path 'laborHourlyRate'
         ,       LaborCosts              number   path 'laborCosts'
         ,       PurchaseOrderCosts      number   path 'purchaseOrderCosts'
         ,       SubcontractorCosts      number   path 'subcontractorCosts'
         ,       EquipmentHours          number   path 'equipmentHours'
         ,       EquipmentHourlyRate     number   path 'equipmentHourlyRate'
         ,       EquipmentCosts          number   path 'equipmentCosts'
         ,       MaterialCosts           number   path 'materialCosts'
         ,       WasteCosts              number   path 'wasteCosts'
         ,       MiscellaneousCosts      number   path 'miscellaneousCosts'
         ,       CreatedAt               datetime path 'createdAt'
         ,       CreatedBy               varchar2 path 'createdBy'
         ,       UpdatedAt               datetime path 'updatedAt'
         ,       UpdatedBy               varchar2 path 'updatedBy'
         -- Skipped: array nonEditableCostTypes
       )

Gecombineerd voor gebruik als Startup SQL van een Invantive Cloud-database wordt het (zie Startup SQL instelbaar voor alle Invantive Cloud-gebruikers)

declare
  l_unique_id varchar2;
begin
  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
  )
  select 'https://heimdall.bouw7.nl/project/' || to_char(pjt.id) || '/project-security-links'
  ,      'GET'
  ,      to_char(pjt.id)
  ,      l_unique_id || '-PSK-PJT-' || to_char(pjt.id)
  ,      true
  ,      true
  ,      3600 * 20 /* 20 hours cache */
  from   projects pjt
  ;
  create or replace table ProjectSecurityCodeChapters@InMemoryStorage
  as
  select nst.ProjectId
  ,      jte.*
  from   ( select result_text, to_number(orig_system_group) ProjectId from Bouw7.Native.NATIVEPLATFORMSCALARREQUESTS where orig_system_reference like l_unique_id || '-%' ) nst
  join   jsontable
         ( '[*].securityCodesPerChapters[*].securityCodeChapter'
           passing nst.result_text
           columns id   int      path 'id'
           ,       name varchar2 path 'name'
           ,       code varchar2 path 'code'
         ) jte
  ;
  create or replace table ProjectSecurityCodeBudgetData@InMemoryStorage
  as
  select nst.ProjectId
  ,      jte.*
  from   ( select result_text, to_number(orig_system_group) ProjectId from Bouw7.Native.NATIVEPLATFORMSCALARREQUESTS where orig_system_reference like l_unique_id || '-%' ) nst
  join   jsontable
         ( '[*].securityCodesPerChapters[*].budgetDataPerSecurityCodes[*]'
           passing nst.result_text
           columns ChapterId               int      path '::ancestor.securityCodeChapter.id'
           ,       ChapterName             varchar2 path '::ancestor.securityCodeChapter.name'
           ,       ChapterCode             varchar2 path '::ancestor.securityCodeChapter.code'
           ,       SecurityCodeId          int      path 'securityCode.id'
           ,       SecurityCodeName        varchar2 path 'securityCode.name'
           ,       SecurityCodeCode        varchar2 path 'securityCode.code'
           ,       SecurityCodeChapterName varchar2 path 'securityCode.chapterName'
           ,       Name                    varchar2 path 'name'
           ,       LaborHours              number   path 'laborHours'
           ,       LaborHourlyRate         number   path 'laborHourlyRate'
           ,       LaborCosts              number   path 'laborCosts'
           ,       PurchaseOrderCosts      number   path 'purchaseOrderCosts'
           ,       SubcontractorCosts      number   path 'subcontractorCosts'
           ,       EquipmentHours          number   path 'equipmentHours'
           ,       EquipmentHourlyRate     number   path 'equipmentHourlyRate'
           ,       EquipmentCosts          number   path 'equipmentCosts'
           ,       MaterialCosts           number   path 'materialCosts'
           ,       WasteCosts              number   path 'wasteCosts'
           ,       MiscellaneousCosts      number   path 'miscellaneousCosts'
           ,       CreatedAt               datetime path 'createdAt'
           ,       CreatedBy               varchar2 path 'createdBy'
           ,       UpdatedAt               datetime path 'updatedAt'
           ,       UpdatedBy               varchar2 path 'updatedBy'
           -- Skipped: array nonEditableCostTypes
         ) jte
  ;
end;

Na het sluiten van de database en het legen van de caches op Invantive Bridge Online zullen er twee nieuwe tabellen zichtbaar worden:

  • ProjectSecurityCodeChapters@InMemoryStorage: hoofdstukken
  • ProjectSecurityCodeBudgetData@InMemoryStorage: bewakingscodes

Een verzoek is ingediend bij Bouw7 om project-security-links ook beschikbaar te krijgen in de metadatadefinities van de Bouw7 API’s.