CreateCapitalAccountData

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.

Create Capital Account Data Item Features

This SQL Wrapper executes a stored procedure that is responsible for calculating all period end gl data on a year to date basis and summarizing it all in the CapitalAccountDataItems table for auditing and reporting.

This SQL Wrapper is automatically used by the system when processing Period End calculations. See here for more info.

Caution

This is not like a normal SQL Wrapper designed to return results to the system. Instead this SQL Wrapper contains a stored procedure that runs an actual process which updates data in the system. This is an example of how you can configure high performance custom processes in sql and still use them from the LemonEdge platform in a way that is fully integrated with the system.

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.

Caution

Note this algorithm will delete all current capital account data items (in an audited way) against this period end that are not manual adjustments, and populate it with the new calculated set of entries.

The process itself goes through several stages detailed here:

  • First the system determines the date for this period end, and the last year end, and runs as of any locked processing date attached to the period end itself.
  • Next the system loads in to temporary tables any possible path used for transactions in this entity set, along with their allocations and root entities.
  • Then the system creates all the Opening Balance entries by copying the last year end entries. It marks any transfer entries as Opening Balance Transfer In/Out, and everything else as just Opening Balance
  • Then the system pulls in all Finalized (and Cancelled) gl entries for this current year up to the period end date. Including gl entries that are allocated to ultimate owners (Likely Capital transactions) and those which have still to be allocated using the rules, paths and mappings.
    • This will also include any current transactions (outside the locked processing date) that are attached to the period end owner itself.
  • Then the system creates all the period end entries that are already allocated. This also takes account of any entries that are transferred marking them as such
    • Transferred entries (See Transfers for more info) also take into consideration the TransferCapitalUpToDate.
      If a transfer has this date set after the Transfer date then although the system will have transferred any gl entries between the transfer date and transfer capital up to date, we want it to show in this report as if it actually took place with those amounts, and not as a transfer of them.
      This is because the Transfer Capital Up To Date is a shortcut to transfer transactions that happened after the transfer date automatically but to show it in reporting as if it actually happened that way - which it would have from being after the transfer date.
  • Then for transactions that aren't allocated the system gets the associated instruments and settings for capital account data items
  • Then the system creates all period end entries for transactions that aren't allocated and require the system to calculate the allocation breakdowns through the relevant paths. This also takes account of any entries that are transferred, marking them as such and also accounting for the TransferCapitalUpToDate as above.
  • Then the system performs rounding for transferred amounts and amounts over all.
  • Finally the system deletes the old capital account data item entries and add the newly calculated ones.
Important

The system includes Cancelled Transactions as well as Finalized Transactions in the process. This is in case any Cancellations have occurred across different Financial Dates due to closed entity periods. The system correctly accounts for that by including Cancelled as well as Finalized. If the Cancellation happens within the same Financial Dates the total will just net to zero anyway thanks to our cancel/correct logic within Transactions. See here for more information.

This data is an aggregation of all gl data from a year to date basis for this period end. It is broken down to the following detail:

  • Entity
  • Period End Type
    • Period End
    • Opening Balance
    • Transfer In
    • Transfer Out
    • Adjustment
    • Opening Balance Transfer In
    • Opening Balance Transfer Out
  • Root Entity
  • Ledger
  • Transaction Code
  • Transaction Type Value
  • Instrument
  • GL Account
Note

This granularity is then used with GLRollups to easily create any highly performant GL report you require. You are also safe that the data is burned into it's own period end table for auditing, and reporting purposes so you can always recreate reports, and see the data, precisely as they went out externally.

Parameters

In addition to the standard parameters of any SQL Wrapper [more info] this one takes the following parameters:

Name Description
[Required]
Period End		 This is the period end you want to run the create capital account data items process on.
Caution

Note: The Period End can not be Finalized or Cancelled when you are running this process.

Results

The results have the following columns in the data set:

Name Description
Exit Code Returns 1 if executed successfully.

Commands

Note

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.