| Info |
|---|
OverviewThis 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. |
...
| Code Block | ||
|---|---|---|
| ||
{"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 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:
...
| language | sql |
|---|
...
. |
...
Typical statuses for myBuildings are:
· 16: Awaiting Approval
...
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
...
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.
...
| Code Block | ||
|---|---|---|
| ||
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:
SQL Agent Job will be configured to run Hourly on Weekdays by Default. Client can decide to change that frequency.
Configuration Settings:
Following Configuration Keys will be created in Synergetic DB for this process:
...
'Creditors' Key1, 'InvoicesImport' Key2, 'URL_GetData' Key3
'Creditors' Key1, 'InvoicesImport' Key2, 'URLPOST' Key3
Frequency for SQL Agent Job
...
URL_PostData' Key3