Versions Compared

Old Version 6

changes.mady.by.user James Overell

Saved on

compared with

New Version Current

changes.mady.by.user Mathieu Apers

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
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.

...

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

Technical implementation

Sample JSON Payload: 

Code Block
languagejson
{"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

...

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.

 Typical  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.

  1. New creditor payment approved in myBuildings

  2. Synergetic agent job will be running on an hourly basis, and picks up an unprocessed invoice (using myBuildings invoices API)

  3. Synergetic creditor identified

  4. New invoice created in Synergetic

...

  1. Synergetic will check the TaxAmount and Invoice Amount for each Invoice and try to establish one of the Synergetic Tax Codes for the Invoice.

  2. 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%)

  3. Process will try to use a TaxCode which has SynergyMeanings for luTax?

  4. 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 (CreditorInvoicesAPIImport). This table will record each Invoice coming into Synergetic using this process.

Table fields:

Code Block
languagesql
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 hourly on Weekdays weekdays by Defaultdefault. Client

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:

  1. 'Creditors' Key1, 'InvoicesImport' Key2, 'AuthenticationHeader' Key3

  2. 'Creditors' Key1, 'InvoicesImport' Key2, 'ContentType' Key3

  3. 'Creditors' Key1, 'InvoicesImport' Key2, 'URL_GetData' Key3

  4. 'Creditors' Key1, 'InvoicesImport' Key2, 'URL_PostData' Key3