GetPivotedTransactions
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 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 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 |
| Reference | The unique (auto incrementing) reference of this transaction record |
| Description | The user description for this transaction |
| Transaction Date | The transaction date of this transaction |
| 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 |
| Transaction Code ID | The transaction code used for this transaction |
| Root Transaction | The ID of the root transaction in a set of transaction. |
| Parent Transaction | If this is a sub-transaction this holds the parent transaction record |
| Parent Item | Transactions can belong to a set that in turn belong to a certain type of record. If that is the case this holds the parent item id. |
| Parent Item Type | The type of record that is a parent of this transaction |
| Settings ID | Transactions can have custom settings. This holds the id of any record that holds extra settings for this transaction |
| Settings Type | The type of entity that holds settings for this transaction |
| Is Header Transaction | Indicates this is a header transaction. Children transactions can occur if the transaction is being replicated up a particular path |
| Entity ID | The entity this transaction is against |
| Instrument ID | The instrument id this transaction is against. This can be used to determine the path for allocating this transaction |
| Path ID | The path this transaction is against Can hold the actual path to use for allocations if not against an instrument |
| Allocation Type | The method to use for allocating this transaction
|
| Ledger ID | The ledger this transaction is recorded in |
| Currency ID | The local currency for this transaction |
| Rounding | The type of rounding to apply when allocating this transaction
|
| Financial Date | The financial date for this transaction |
| FX Rate | The rate used by the system to exchange from local to functional currency |
| Reporting FX Rate | The rate used by the system to exchange from local to reporting currency |
| Price | The price of this transaction |
| Local Net Amount | The total local values for this transaction |
| Func Net Amount | The total functional values for this transaction |
| Reporting Net Amount | The total reporting values for this transaction |
| Transaction Type Value ID | The ID of the transaction type value this record holds a value within this 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 transaction in reporting currency. |
| FuncValue | Holds the value of this transaction type value for this transaction in functional currency. |
| LocalValue | Holds the value of this transaction type value for this 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 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.