Info

Overview

This document details the specification of the myBuildings API import of Creditor invoices. Based on the data returned from the API Call new Creditor Invoices and GL Journals will be created in Synergetic Database and Postings will be shown under Creditor Invoice Entry in Synergetic.

Fees and Subscription Model

A one-off implementation fee will be charged. This will cover the one-off effort and will depend on the amount of consultant time required.

Tasks covered:

Implementation of the Synergetic SQL Agent job, configuration & testing
Consulting time if client requires help inputting/matching GL codes in myBuildings and/or Synergetic

A subscription will also be introduced to cover any additional support and further development of this function. The monthly fee will more than reflect the time saved by Finance staff from having to manually input myBuildings Invoices into Synergetic.

Clients should reach out to their CSM if they would like this feature implemented.

Technical implementation

Sample JSON Payload:

Code Block

language	json

{"Invoices":[

    {

     "WorkRequestID":11603508,

     "BuildingName":"ElliotMain Hall",

     "ThirdPartySystem_BuildingID":"6",

     "JobCode":"ELLIOT2300207",

     "InvoiceStatus":"Approved for Processing",

     "ContractorName":"Triangle Fire Protection LtdContractor ABC",

     "ThirdPartySystem_ContractorID":"",

     "invoiceamount":1539.20,

     "InvoiceTax":230.88,

     "InvoiceTotal":1770.08,

     "InvoiceDate":"2023-10-31T00:00:00",

     "InvoiceNumber":"73596",

     "InvoiceDescription":"MAIN BLOCK/ELLIOTHALL",

         "InvoiceImageURL":"https://mybuildings.blob.core.windows.net/storage/Core/SiteDownloadFiles/719/ContractorInvoices/2023/11/951e84fad3644b4bb5d8url_location/invoice123.pdf",

         "DateUploaded":"2023-11-06T03:27:00",

         "AccountCodeBreakdown":[

                    {

                    "AccountNumber":"2810-10-5300",

                    "Percentage":100.0,

                    "Amount":1539.20,

                    "TaxAmount":230.88,

                    "TotalIncludingTax":1770.08

                    }

                    ]

        },

        {"WorkRequestID":11567457,

        "BuildingName":"Elizabeth MacFarlanAdministration Centre",

        "ThirdPartySystem_BuildingID":"21",

        "JobCode":"EMC2300186",

        "InvoiceStatus":"Approved for Processing",

        "ContractorName":"AdvantageContractor Door Services”XYZ”,

        "ThirdPartySystem_ContractorID":"",

        "invoiceamount":596.70,

        "InvoiceTax":89.50,

        "InvoiceTotal":686.20,

        "InvoiceDate":"2023-10-31T00:00:00",

        "InvoiceNumber":"135022",
               "InvoiceDescription":"Door is constantly opening and closing a",

        "InvoiceImageURL":"https://mybuildings.blob.core.windows.net/storage/Core/SiteDownloadFiles/719/ContractorInvoices/2023/11/9df18f247d934c539525url_location/invoice124.pdf",

        "DateUploaded":"2023-11-09T01:33:00",

        "AccountCodeBreakdown":[

                    {

                    "AccountNumber":"2040-10-5105",

                    "Percentage":100.0,

                    "Amount":596.70,

                    "TaxAmount":89.50,

                    "TotalIncludingTax":686.20

                    }

                    ]

        }

      ]

},"RecordsReturned":"2"}';

...

Fields for Creditor Invoice Details

Field Name	SQL Data Type	Mandatory	Description
ContractorName	VARCHAR (100)	N	Creditor PayeeName
ThirdPartySystem_ContractorID	INTEGER	Y	Synergetic Creditor ID
InvoiceAmount	MONEY	Y	Invoice Amount (Excl. Tax)
InvoiceTax	MONEY	Y	Invoice Tax Amount
InvoiceDate	DATETIME	Y	Invoice Date
InvoiceNumber	VARCHAR (15)	Y	Invoice Number
InvoiceDescription	VARCHAR (100)	Y	Description for this Invoice

Fields for GLAllocations

Field Name	SQL Data Type	Mandatory	Description
AccountNumber	VARCHAR (15)	Y	GLCode. Must exist in Synergetic General Ledger
Amount	MONEY	Y	GLAmount (Excl. Tax)
TaxCode	VARCHAR (5)	N	Tax Code must exist in luTax
GLDescription	VARCHAR (50)	N	Invoice Description from the Invoices Node.
TaxAmount	MONEY	Y	The amount of tax for the allocation.

Sample SQL Code for API Call to myBuildings:

Code Block

language	sql

    DECLARE @authHeader VARCHAR(8000);

    DECLARE @contentType VARCHAR(8000);

    DECLARE @postData VARCHAR(8000);

    DECLARE @responseText VARCHAR(8000);

    DECLARE @responseXML VARCHAR(8000);

    DECLARE @ret INT;

    DECLARE @status VARCHAR(8000);

    DECLARE @statusText VARCHAR(8000);

    DECLARE @token INT;

    DECLARE @url VARCHAR(8000);

    DECLARE @JSON VARCHAR(MAX);

 

    SET @authHeader = 'Bearer ?????????????????';

    SET @contentType = 'application/json';

    SET @url = 'https://sb-pk.mybuildings-stage.com/core/api/invoices/v1?statusid=17 '

 
    -- Open the connection.

    EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;

    IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

 

    -- Send the request.

    EXEC @ret = sp_OAMethod @token, 'Open', null, 'GET', @Url, 'false'

    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;

    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;


    EXEC sp_OAMethod @token, 'send', null

    EXEC sp_OAMethod @token, 'responseText', @ResponseText OUTPUT

 
    SET @JSON = REPLACE(@ResponseText,'{"Success":true,"Data":','')

Typical statuses Typical statuses for myBuildings are:

· 16: Awaiting Approval

...

· 31: Error With Invoice Processing

Process will only import Creditor Invoices with Status “17: Approved for Processing“

workrequest API (POST)

Field Name	SQL Data Type	Mandatory	Description
NewStatusID	Integer	Yes	New StatusID. If not passed in (or -1), status will not be altered. Possible statuses available from this API are as follows: 1: New 2: In Progress 3: On Hold 4: Complete 6: Contractor Complete
Comment	String	Yes	Comment to record against each request (added to Work Notes)
WorkRequestID	Integer	No (Yes in our use case)	Which work request to update

User stories

...

A number of use cases have been considered for this implementation including the following.
Valid Creditor provided.
New creditor payment approved in myBuildings
Synergetic agent job will be running on an hourly basis, and picks up an unprocessed invoice (using myBuildings invoices API)
Synergetic creditor identified
New invoice created in Synergetic
...
Synergetic will check the TaxAmount and Invoice Amount for each Invoice and try to establish one of the Synergetic Tax Codes for the Invoice.
Process will infer a tax code where this is a single value that reconciles to the finance.luTax lookup references. Usually GST (10%) or tax-free (0%)
Process will try to use a TaxCode which has SynergyMeanings for luTax?
Invalid TaxCode error will be thrown if the process is unable to establish a valid TaxCode for a given Invoice.

Import Procedure

[finance].[spiCreditorTransactionsLoadFileAPI] will be created in Synergetic database and process all the Invoices as part of this Import.

History Table

History table will be created for this process (uCreditorInvoicesAPIImportCreditorInvoicesAPIImport). This table will record each Invoice coming into Synergetic using this process.

Table fields:

Code Block

language	sql

CreditorID VARCHAR(15),
InvoiceDescription VARCHAR(100),
InvoiceNumber VARCHAR(20),
InvoiceAmount VARCHAR(20),
InvoiceDate VARCHAR(12),
PaymentDate VARCHAR(12),
PaymentMethod VARCHAR(10),
PricesIncludeTaxFlag BIT,
InvoiceMode VARCHAR(10),
ForeignCurrencyCode VARCHAR(15),
ForeignCurrencyExchangeRate VARCHAR(15),
ForeignCurrencyAmount VARCHAR(15),
BankFeeAmount VARCHAR(15),
BankFeeGLCode VARCHAR(15),
BankFeeDescription VARCHAR(100),
DueDate VARCHAR(12),
ChequeNumber INT,
InvoiceError VARCHAR(500),
GLAllocationError VARCHAR(500),          
ProcessedFlag BIT,
RecordCreatedDate Datetime,
SEQ INT IDENTITY(1,1),
WorkRequestID INT,
myBuildingsPOSTResponse VARCHAR(Max)

SQL Agent Job

A SQL Agent Job called “Synergetic - Creditor Invoices API Import“ will be created on the relevant SQL Server and run periodically to process the Invoices.

Frequency for SQL Agent Job

The SQL Agent Job will be configured to run hourly on weekdays by default.

Clients can decide to change that frequency as required.

Configuration Settings

...

...
The following Finance Configuration Keys will be created in Synergetic DB for this process:
'Creditors' Key1, 'InvoicesImport' Key2, 'AuthenticationHeader' Key3
'Creditors' Key1, 'InvoicesImport' Key2, 'ContentType' Key3
'Creditors' Key1, 'InvoicesImport' Key2, 'URL_GetData' Key3
'Creditors' Key1, 'InvoicesImport' Key2, '
...
Frequency for SQL Agent Job
...
URL_PostData' Key3

Versions Compared

Old Version 2

New Version Current

Key

Overview

Fees and Subscription Model

Technical implementation

Fields for Creditor Invoice Details

Fields for GLAllocations

Sample SQL Code for API Call to myBuildings:

workrequest API (POST)

User stories

Import Procedure

History Table

SQL Agent Job

Frequency for SQL Agent Job

Configuration Settings

Page Comparison

Versions Compared

Old Version 2

New Version Current

Key

Overview

Fees and Subscription Model

Technical implementation

Fields for Creditor Invoice Details

Fields for GLAllocations

Sample SQL Code for API Call to myBuildings:

workrequest API (POST)

User stories

Import Procedure

History Table

SQL Agent Job

Frequency for SQL Agent Job

Configuration Settings