Table: dbo.Quick_Books_Retrieved_Transaction
This table contains information on transactions retrieved from quickbooks for a given ACS client, where the transactions have not yet been imported into ACS for that client. For quickbooks online, the process of importing data has two steps. First, we retrieve the data from a quickbooks online company, and then we import the retrieved data into the permanent ACS tables. We need a means to store off the information after that first step has completed, but before that second step. This table provides the means for us to store information on transactions that have been retrieved from a quickbooks online company, but not yet imported into the permanent tables in ACS for the client. Once the import completes for one client, the records in this table for that one client will be removed.
Columns
Name | Type | Constraints | Description
quick_books_retrieved_transaction_KEY | int (4) | NOT NULL | The primary Key for this table.
client_KEY | int (4) | NOT NULL | The client key, to allow us to group retrieved transactions by ACS client.
quick_books_transaction_type_KEY | tinyint (1) | NOT NULL | The quickbooks transaction type for this transaction.
transaction_id | nvarchar (60) | NOT NULL | The quickbooks transaction id of the transaction. This is supposed to be a unique value, for the transactions of one quickbooks company.
transaction_date | smalldatetime (4) | NOT NULL | The transaction date of this transaction.
amount | decimal (9, 2) | NOT NULL | The amount of this transaction.
reference_number | nvarchar (42) | NOT NULL | The reference number of this transaction. Reference numbers in qb online have a maximum of 21 characters.
description | nvarchar (400) | NOT NULL | The description of this transaction.

Primary key
Name | Columns | Description
PK_Quick_Books_Retrieved_Transaction | quick_books_retrieved_transaction_KEY |
Unique constraints
Name | Columns | Description
UK_Quick_Books_Retrieved_Transaction$client_KEY$transaction_id | client_KEY, transaction_id |

Foreign keys
Name | Columns | Referenced table | Description
FK_Quick_Books_Retrieved_Transaction__Client__client_KEY | client_KEY | Client (client_KEY) |
FK_Quick_Books_Retrieved_Transaction__Quick_Books_Transaction_Type__quick_books_transaction_type_KEY | quick_books_transaction_type_KEY | Quick_Books_Transaction_Type (quick_books_transaction_type_KEY) |

Incoming foreign keys
Name | Columns | Referencing table | Description
FK_Quick_Books_Retrieved_Transaction_Distribution__Quick_Books_Retrieved_Transaction__quick_books_retrieved_transaction_KEY | quick_books_retrieved_transaction_KEY | Quick_Books_Retrieved_Transaction_Distribution |