Laden uren vanuit Freshdesk naar Exact Online

Wiki (nl)
,
1

Met de integratiemogelijkheden van Invantive Cloud zijn ook maatwerkkoppelingen mogelijk voor een naadloze integratie, zoals bijvoorbeeld tussen Freshdesk en Exact Online. In Freshdesk wordt - net zoals in andere ticketsystemen - vaak de gespendeerde tijd vastgelegd, die echter voor de facturatie door de boekhouding nodig is in de projectenmodule van Exact Online.

Met de volgende statements kun het laden van uren geregistreerd in Freshdesk als uren in Exact Online automatiseren.

--
-- Get default project per customer.
--
create or replace table AccountGenericProjects@InMemoryStorage
as
select pjt.Id EolProjectId
,      pjt.Code EolProjectCode
,      pjt.Description EolProjectDescription
,      act.Id EolAccountId
,      act.Name EolAccountName
,      act.Email EolAccountEmail
from   ProjectsIncremental@eol pjt
join   AccountsIncremental@eol act
on     act.Code = pjt.Code

create or replace table EolHourType@InMemoryStorage
as
select itm.Id EolHourType
from   ItemsIncremental@eol itm
join   hourtypes@eol hte
on     hte.ItemId = itm.Id
where  itm.code = 'CHOOSE-A-HOURTYPE'

create or replace table UserMappingFdk2Eol@InMemoryStorage
as
select agt.id FdkAgentId
,      agt.contact_name FdkAgentName
,      agt.contact_email FdkAgentEmail
,      usr.userid EolUserId
,      emp.id EolEmployeeId
from   Agents@fdk agt
join   exactonlinerest..users@eol usr
on     usr.email = agt.contact_email
join   ExactOnlineREST..Employees@eol emp
on     emp.User = usr.UserId

create or replace table PartyMappingFdk2Eol@InMemoryStorage
as
select act.Id EolAccountId
,      act.Code EolAccountCode
,      act.Name EolAccountName
,      cpy.id FdkCompanyId
,      cpy.Name FdkCompanyName
from   AccountsIncremental@eol act
join   Companies@fdk cpy
on     lower(cpy.name) = lower(act.name)

create or replace table TimeEntries@InMemoryStorage
as
select *
from   time_entries@fdk/*(executed_after => trunc(sysdate))*/
limit  1000

create or replace table EolPjtTimeTransactionsSoll@InMemoryStorage
as
select tey.EolEmployeeId Employee
,      tey.EolAccountId Account
,      tey.EolProjectId Project
,      1 HourStatus
,      1 Type
,      tey.EolHourType HourType
,      'Upload from Freshdesk' Notes
--,      tey.ticket_id
--,      tey.billable
,      tey.dy Date
,      sum(ceil(tey.time_spent / 900) / 4) Quantity
--,      count(*) cnt
from   ( select umg.EolEmployeeId
         ,      pmg.EolAccountId
         ,      agt.EolProjectId
         ,      hte.EolHourType
         ,      tey.ticket_id
         ,      tey.billable
         ,      trunc(tey.start_time) dy
         ,      tey.time_spent
         from   TimeEntries@InMemoryStorage tey
         join   EolHourType@InMemoryStorage hte
         left
         outer
         join   UserMappingFdk2Eol@InMemoryStorage umg
         on     umg.FdkAgentId = tey.agent_id
         left
         outer
         join   PartyMappingFdk2Eol@InMemoryStorage pmg
         on     pmg.FdkCompanyId = tey.company_id
         left
         outer
         join   AccountGenericProjects@InMemoryStorage agt
         on     agt.EolAccountId = pmg.EolAccountId         
      ) tey
group
by     tey.EolEmployeeId
,      tey.EolAccountId
,      tey.EolProjectId
,      tey.EolHourType
,      tey.ticket_id
,      tey.billable
,      dy


insert into PjtTimeTransactions@eol
( Employee
, Account
, Project
, HourStatus
, Item
, Type
, Notes
, Date
, Quantity
)
select Employee
,      Account
,      Project
,      HourStatus
,      HourType
,      Type
,      Notes
,      Date
,      Quantity
from   EolPjtTimeTransactionsSoll@InMemoryStorage
where  Employee is not null
and    Account is not null
and    Date >= trunc(sysdateutc, -1)