Re: On Hand correction

[Armaghan Saqib <..hidden..>]

On Thu, Feb 19, 2009 at 5:08 PM, Ed W <..hidden..> wrote:
> I think a similar "wish list" would be to see the net profit per
> invoice.  ie something similar to the ledger view, but limited only to
> transactions which related to a specific invoice, but also with more
> information than the GL view shows.  The point being to look at an
> invoice for 6 widgets and a thingamy and see that the system thinks that
> the raw buy price came from these 4 purchase invoices and that the cogs
> looks ok.

Following query (from a custom report) can list this information
(per-invoice COGS) in SL and should work in LSMB 1.2 too.

                SELECT
                  aa.id,
                  aa.invnumber,
                  aa.transdate,
                  ct.customernumber,
                  ct.name,
                  e.name AS employee,
                  aa.netamount AS amount,
                  aa.amount - aa.netamount AS tax,
                  aa.amount AS total,

        (SELECT SUM(0-ac.amount)
        FROM acc_trans ac
        JOIN chart c ON (c.id = ac.chart_id)
        WHERE ac.trans_id = aa.id
        AND c.link LIKE '%IC_cogs%') AS cogs,

                aa.invoice,
                aa.till

                FROM ar
                JOIN customer ct ON (ct.id = aa.customer_id)
                LEFT JOIN employee e ON (e.id = aa.employee_id)

> This kind of view would be really helpful for flushing out cogs
> problems.  Also personally I would love to see my profit per item so
> that I can figure out what I am selling for the right price, where I am
> "bundling", etc (this view would not come from the screen proposed
> above, but the basic ground work would be similar)

Done this too in SL. Was bit tricky and here is how I did it.

1. Added a new table FIFO which stored invoice line, FIFO cost for the
item and invoice line qty for the item from that particular cost tier.
This invoice line repeats if the sold item comes from more than one
cost tier.

2. A new per-item sale report utilizing info from this table with a
subquery something like:

        (SELECT SUM(qty * costprice)
        FROM fifo f
        WHERE f.trans_id = aa.id
        AND f.parts_id = i.parts_id
        $fifowhere) AS cogs,

Shall hopefully contribute this to 1.3 if there is a need and as soon
as I get comfortable with 1.3.

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

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