GetPivotedAllocatedTransactions
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 Pivoted 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 allocated 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. These values are returned as a value label and value amount, effectively pivoting the results.
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 |
| Transaction Type Value ID | The ID of the transaction type value this record holds a value within this allocated transaction for |
| Transaction Type Value ID Label | The label of the transaction type value. This has the parent index set to the transaction type value id column so the user sees the label instead of the id. |
| ReportingValue | Holds the value of this transaction type value for this allocated transaction in reporting currency. |
| FuncValue | Holds the value of this transaction type value for this allocated transaction in functional currency. |
| LocalValue | Holds the value of this transaction type value for this allocated transaction in local 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 a Transaction Type Value and its associated amount, effectively pivoting all the values in this allocated transaction record.
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.