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.

Sample JSON Payload:

{"Invoices":[

    {

     "WorkRequestID":11603508,

     "BuildingName":"Elliot Hall",

     "ThirdPartySystem_BuildingID":"6",

     "JobCode":"ELLIOT2300207",

     "InvoiceStatus":"Approved for Processing",

     "ContractorName":"Triangle Fire Protection Ltd",

     "ThirdPartySystem_ContractorID":"",

     "invoiceamount":1539.20,

     "InvoiceTax":230.88,

     "InvoiceTotal":1770.08,

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

     "InvoiceNumber":"73596",

     "InvoiceDescription":"MAIN BLOCK/ELLIOT",

         "InvoiceImageURL":"https://mybuildings.blob.core.windows.net/storage/Core/SiteDownloadFiles/719/ContractorInvoices/2023/11/951e84fad3644b4bb5d8.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 MacFarlan Centre",

        "ThirdPartySystem_BuildingID":"21",

        "JobCode":"EMC2300186",

        "InvoiceStatus":"Approved for Processing",

        "ContractorName":"Advantage Door Services”,

        "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/9df18f247d934c539525.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"}';

Field 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:

    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 are:

· 16: Awaiting Approval

· 17: Approved for Processing

· 18: Processed

· 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:

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

a. Update status through workrequest API (status 18: Processed)

b. Comment added ‘Invoice successfully created in Synergetic’

Complete

Invalid 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 not identified
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid Creditor ID. Supplier cannot be identified in Synergetic, please contact your Finance Team.’
Complete

Invalid Invoice Amount 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)
Invoice Amount is not valid.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid Invoice Amount. Invoice amount must be a numeric value.’
Complete

Creditor Invoice Already Exists

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)
Invoice already Exists in Synergetic.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Creditor Invoice Already Exists in Synergetic. This process is for creating new Invoices.’
Complete

Invalid Invoice Date 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)
Invalid Invoice Date provided.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid Invoice Date. Date provided for the Invoice is not a valid Date.’
Complete

Invalid Invoice DueDate 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)
Invalid Invoice DueDate provided.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid Invoice DueDate. DueDate provided for the Invoice is not a valid Date.’
Complete

Invalid TaxCode.

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)
Invalid Taxcode for the Invoice is provided or calculated.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid TaxCode. A valid Synergetic TaxCode cannot be established for this Invoice.’
Complete

Invalid Tax Amount 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)
Invoice Amount is not valid.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid Tax Amount. Invoice Tax amount must be a numeric value.’
Complete

Invalid GLCode 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)
Invalid GLCode provided.
Error passed to workrequest API with no change to status and error message which can be seen through the myBuilding UI
1. Update status through invoices API (status = 31)
2. Comment added ‘Invalid GLCode Supplied.The GLCode provided does not exist in Synergetic. Please contact your Finance team.’
Complete

No tax code provided in the payload.

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.

History Table

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

Table fields:

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)

Configuration Settings:

Following 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' Key3

'Creditors' Key1, 'InvoicesImport' Key2, 'URLPOST' Key3

Frequency for SQL Agent Job

SQL Agent Job will be configured to run Hourly on Weekdays by Default. Client can decide to change that frequency.

myBuildings Creditor Invoices API Import