GetAllocatedTransactions
This is a standard system SQL Wrapper [more info] provided by the platform and automatically upgraded on every new release. Do not change this SQL Wrapper unless you are confident about the changes you are making - Contact LemonEdge Support for any queries.
Get Allocated Transactions Features
This SQL Wrapper is automatically generated by the system every time changes are made to the Transaction configuration for Transaction type Values.
This means you can't change this SQL Wrapper as it will just be overwritten by the system anyway. If you want to change it make a copy and work with that.
As with all system SQL Wrappers you can view the SQL code for yourself to see how the system runs this process to ensure there are no black-box algorithms closed of from you.
The system doesn't use this SQL Wrapper by default in any of its processes, rather it is meant as an easy to use query for reporting across all the transactions in the system including all your configuration. This SQL Wrapper forms part of a set of 4 SQL Wrappers designed to be used in any custom query you wish to write:
- Get Allocated Transactions
- Get Transactions
- Get Pivoted Allocated Transactions
- Get Pivoted Transactions
This SQL Wrapper returns all allocated transactions in the system including all the values for those transactions according to how you've configured them in Transaction Type Values.
Parameters
This SQL Wrapper has no additional parameters other than the standard parameters of any SQL Wrapper [more info].
This is because you can use this query in any custom sql you like joining to any of its properties as you require.
Results
The results have the following columns in the data set:
| Name | Description |
| Status | Transaction Status |
| Canceled ID | ID of the Transaction this record cancels |
| Corrected By ID | ID of the Transaction this cancelled transaction has been corrected by |
| Account ID | Standard Account ID field |
| ID | globally unique ID of this transaction |
| LastUpdated | Date/time stamp this record was last udpated |
| Modified By User | ID of the user that last updated this record |
| Canvas | The ID of any canvas this record belongs to |
| Locked | Indicates if this record has been locked - is Cancelled or Finalized |
| Index | The order of this transaction in a set of transactions |
| Settlement Date | The date this transaction was settled |
| Units | The units this transaction was for |
| Transfer ID | If this record was created from a Transfer this holds the link to the owning Transfer |
| Transferred Original Item | The ID of the original record this one is transferring amounts off of. |
| EntityID | The ID of the entity this transaction allocated amount belongs to |
| Effective Date | The effective date of this transaction |
| Root Transaction | The ID of the root transaction in a set of transaction. |
| Parent Transaction | The parent transaction record this break down allocation amount belongs to |
| Manual Added | Indicates if this transaction allocation record was manually added by the user and not calculated by the system |
| LocalXXX... | For every Transaction Type Value you've created in the system this SQL Wrapper will hold a corresponding LocalXXX value, where XXX is the Transaction Type Value Code, and it holds the value amount in local currency. |
| FuncXXX... | For every Transaction Type Value you've created in the system this SQL Wrapper will hold a corresponding FuncXXX value, where XXX is the Transaction Type Value Code, and it holds the value amount in functional currency. |
| ReportingXXX... | For every Transaction Type Value you've created in the system this SQL Wrapper will hold a corresponding ReportingXXX value, where XXX is the Transaction Type Value Code, and it holds the value amount in reporting currency. |
See Transactions, and their setup, in the Financial Services area for more information. Basically this is a view of transactions that have been broken down by allocated amounts already (Capital Transactions) and hold all the amounts in the values you've configured for the system in Transaction Type Values. You will have 3 times as many columns as you do Transaction Type Values configured in the system.
Commands
All SQL Wrappers have the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
See here for more information on the standard SQL Wrappers result view.