sql-ledger-users

[SL] Re-generating Year End For Prior Years

[Rich S. <rsh...@ap...>]

   I have to manually clean up all the duplicate entries that go back 61
months. Can I re-run the year end closing for these years (2004-2007) on the
corrected data?

Thanks,

Rich

-- 
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863

Re: [SL] Re-generating Year End For Prior Years

[Armaghan S. <sa...@le...>]

On 2/12/09, Rich Shepard <rsh...@ap...> wrote:
> I have to manually clean up all the duplicate entries that go back 61months.
> Can I re-run the year end closing for these years (2004-2007) on the corrected data?

In sql-ledger, yearend  process posts a normal gl transaction which
posts the net diffierence of revenue/expenses accounts to earnings
accounts.

Yes, you can re-run the year end for all the past years one by one.
Before doing this you should delete the existing year-end journal
entries. You can list them using 'General Ledger -- Reports' menu
option.

If you have forgotten the dates of previous year ends, you can look
into the yearend table which contains the ID and date of the year end
journal entries. When you delete the yearend journals, rows from
yearend table will be removed too.

I hope this is clear. If not let me know.

Regards
-- 
http://www.ledger123.com/

- Free SQL-Ledger Hosting
- Documentation wiki
- Virtual appliances
--

Re: [SL] Re-generating Year End For Prior Years

[Rich S. <rsh...@ap...>]

On Thu, 12 Feb 2009, Armaghan Saqib wrote:

> In sql-ledger, yearend  process posts a normal gl transaction which
> posts the net diffierence of revenue/expenses accounts to earnings
> accounts.

Armaghan,

   Yes, I've seen this each year.

> Yes, you can re-run the year end for all the past years one by one.
> Before doing this you should delete the existing year-end journal
> entries. You can list them using 'General Ledger -- Reports' menu
> option.

   I thought so. They're duplicated, too, so the entire system is a mess and
I have to quickly straighten it all out.

> I hope this is clear. If not let me know.

   Much appreciated.

Rich

-- 
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863

Re: [SL] Re-generating Year End For Prior Years

[Armaghan S. <sa...@le...>]

On 2/12/09, Rich Shepard <rsh...@ap...> wrote:
>    I thought so. They're duplicated, too, so the entire system is a mess and
> I have to quickly straighten it all out.

Look into the database tables directly and let us know if these
transactions are duplicated in the database too.

I think there is a duplicate id in chart (or somewhere else). This
will be confirmed if the transaction is not duplicated in table
(acc_trans) and only appears so in sql-ledger.

Duplication in chart can be confirmed using the following query.

                SELECT id, 'chart' AS table, COUNT(*) AS count
                FROM chart
                GROUP BY 1,2
                HAVING COUNT(*) > 1

I have a small script (LedgerDoctor) which does a number of health
checks (actually sql queries) on your database. It does not correct
any issue but you can do that yourself once you know where the problem
is. (All queries in the source are documented so you can run these one
by one yourself if you like)

I am sending you this script off-list. (Anyone else interested, let me
know. It is free for the list.)

Regards

-- 
http://www.ledger123.com/

- Free SQL-Ledger Hosting
- Documentation wiki
- Virtual appliances
--

Re: [SL] Re-generating Year End For Prior Years

[Rich S. <rsh...@ap...>]

On Thu, 12 Feb 2009, Armaghan Saqib wrote:

> Duplication in chart can be confirmed using the following query.
>
>                SELECT id, 'chart' AS table, COUNT(*) AS count
>                FROM chart
>                GROUP BY 1,2
>                HAVING COUNT(*) > 1

Armaghan,

   The above script returns 0 rows. Where do I learn what function is
provided by the chart table?

Rich

-- 
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863

Re: [SL] Re-generating Year End For Prior Years

[Armaghan S. <sa...@le...>]

On Thu, Feb 12, 2009 at 7:06 PM, Rich Shepard <rsh...@ap...> wrote:
> On Thu, 12 Feb 2009, Armaghan Saqib wrote:
>> Duplication in chart can be confirmed using the following query.
>>
>>                SELECT id, 'chart' AS table, COUNT(*) AS count
>>                FROM chart
>>                GROUP BY 1,2
>>                HAVING COUNT(*) > 1
>
> Armaghan,
>
>   The above script returns 0 rows. Where do I learn what function is
> provided by the chart table?

'chart' table is used for the chart of accounts.

1. Could you confirm that year end transactions are not duplicated in
the database (acc_trans). You could search for these by using query
like:

SELECT * FROM acc_trans WHERE trans_id = closing_entry_id;

closing_entry_id can be found on "general ledger-reports". click the
'ID' checkbox before clicking the continue button.

2. Also make sure there is no duplicate account number.  Following
query should help.

                SELECT accno, COUNT(*) AS count
                FROM chart
                GROUP BY 1
                HAVING COUNT(*) > 1

Regards
-- 
http://www.ledger123.com/

- Free SQL-Ledger Hosting
- Documentation wiki
- Virtual appliances
--