SSIS Components for Sage Business Cloud Accounting

Build 22.0.8509

Journals

Query Journals in Sage Business Cloud Accounting.

Table Specific Information

Query the available Journals

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Journals WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Journals WHERE DeletedSince='2018-12-15'

Insert

  • Insert a journal with aggregate value.
    INSERT INTO  [Journals] ( JournalLineAggregate, date, reference) VALUES ('{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 12,"credit": 0,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true},{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 0,"credit": 12,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true}','2019-07-12','Journal Reference')
  • Insert a journal using temp tables.
    INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('10', '0', 'details1', '6035f3d39d7511e9a2b70e070fead9d0')
    INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('0', '10', 'details2', '6035f3d39d7511e9a2b70e070fead9d0')
    INSERT INTO  [Journals] (JournalLineAggregate, date, reference) VALUES ('JournalLineItemsAggregate#Temp','2019-07-12','Reference2')

Delete

You must specify the Id of the Journals to delete it.

DELETE FROM Journals WHERE id ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

JournalLineAggregate String False

The journal lines

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

TransactionId String False

Transactions.Id

The unique identifier for the item

Date Datetime True

The date of the journal

Reference String False

A reference for the journal

Migrated Bool False

Indicates if the journal was migrated from another system.

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

DeletedAt Datetime True

The datetime when the item was deleted

Total String False

The total for the journal

Description String False

A description of the journal

JournalCodeId String False

JournalCodes.Id

The unique identifier for the item

DeletedSince Datetime True

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8509