Costed Inventory Movement Report is not Reconciled

Are you referring to 2.3 or 2.4 or maybe both?

Joe

Hello again,

I didn't create this report. So I had to go into the source code.

As I can see, the rate is based on
1. Initial values is based on the rate on the beginning date.
2. Incoming values are based on the average rate during the period.
3. The same for outgoing values.
4. The end values are based on the average values for the whole period.

The guy who wrote this report are not reachable at present.

Can you use some of the other reports if this is not adequate.

Joe

The report is in the file /reporting/rep308.php.

You see the algorithms here. Can you advise me how you would implement this report in detail. As I told I didn't create this report. So I would appreciate if you will help.

Joe

<?php
/**********************************************************************
    Copyright (C) Boxygen, LLC.
    Released under the terms of the GNU General Public License, GPL,
    as published by the Free Software Foundation, either version 3
    of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision:    2.0 $
// Creator:    Jujuk, Joe Hunt
// date_:    2011-05-24
// Title:    Stock Movements
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/ui/ui_input.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/sales/includes/db/sales_types_db.inc");
include_once($path_to_root . "/inventory/includes/inventory_db.inc");

//----------------------------------------------------------------------------------------------------

inventory_movements();

function get_domestic_price($myrow, $stock_id, $qty, $old_std_cost, $old_qty)
{
    if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
    {
        $price = $myrow['price'];
        if ($myrow['type'] == ST_SUPPRECEIVE)
        {
            // Has the supplier invoice increased the receival price?
            $sql = "SELECT DISTINCT act_price FROM ".TB_PREF."purch_order_details pod INNER JOIN ".TB_PREF."grn_batch grn ON pod.order_no =
                grn.purch_order_no WHERE grn.id = ".$myrow['trans_no']." AND pod.item_code = '$stock_id'";
            $result = db_query($sql, "Could not retrieve act_price from purch_order_details");
            $row = db_fetch_row($result);
            if ($row[0] > 0 AND $row[0] <> $myrow['price'])
                $price = $row[0];
        }
        if ($myrow['person_id'] > 0)
        {
            // Do we have foreign currency?
            $supp = get_supplier($myrow['person_id']);
            $currency = $supp['curr_code'];
            $ex_rate = get_exchange_rate_to_home_currency($currency, sql2date($myrow['tran_date']));
            $price /= $ex_rate;
        }
    }
    elseif ($myrow['type'] != ST_INVADJUST) // calcutale the price from avg. price
        $price = ($myrow['standard_cost'] * $qty - $old_std_cost * $old_qty) / $myrow['qty'];
    else
        $price = $myrow['standard_cost']; // Item Adjustments just have the real cost

    $price = $myrow['standard_cost'];
    return $price;
}

function fetch_items($category=0)
{
        $sql = "SELECT stock_id, stock.description AS name,
                stock.category_id,units,
                cat.description
            FROM ".TB_PREF."stock_master stock LEFT JOIN ".TB_PREF."stock_category cat ON stock.category_id=cat.category_id
                WHERE mb_flag <> 'D'";
        if ($category != 0)
            $sql .= " AND cat.category_id = ".db_escape($category);
        $sql .= " ORDER BY stock.category_id, stock_id";

    return db_query($sql,"No transactions were returned");
}

function trans_qty($stock_id, $location=null, $from_date, $to_date, $inward = true)
{
    if ($from_date == null)
        $from_date = Today();

    $from_date = date2sql($from_date);

    if ($to_date == null)
        $to_date = Today();

    $to_date = date2sql($to_date);

    $sql = "SELECT ".($inward ? '' : '-')."SUM(qty) FROM ".TB_PREF."stock_moves
        WHERE stock_id=".db_escape($stock_id)."
        AND tran_date >= '$from_date'
        AND tran_date <= '$to_date' AND type <> ".ST_LOCTRANSFER;

    if ($location != '')
        $sql .= " AND loc_code = ".db_escape($location);

    if ($inward)
        $sql .= " AND qty > 0 ";
    else
        $sql .= " AND qty < 0 ";

    $result = db_query($sql, "QOH calculation failed");

    $myrow = db_fetch_row($result);

    return $myrow[0];

}

function avg_unit_cost($stock_id, $location=null, $to_date)
{
    if ($to_date == null)
        $to_date = Today();

    $to_date = date2sql($to_date);

    $sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id  FROM ".TB_PREF."stock_moves
        WHERE stock_id=".db_escape($stock_id)."
        AND tran_date < '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;

    if ($location != '')
        $sql .= " AND loc_code = ".db_escape($location);
    $sql .= " ORDER BY tran_date";

    $result = db_query($sql, "No standard cost transactions were returned");
    if ($result == false)
        return 0;
    $qty = $old_qty = $count = $old_std_cost = $tot_cost = 0;
    while ($row=db_fetch($result))
    {
        $qty += $row['qty'];

        $price = get_domestic_price($row, $stock_id, $qty, $old_std_cost, $old_qty);

        $old_std_cost = $row['standard_cost'];
        $tran_cost = $price * $row['qty'];
        $tot_cost += $tran_cost;
        $count++;
        $old_qty = $qty;
    }
    if ($qty != 0)
        return $tot_cost/ $qty;
    else
    return 0; //by Faisal
}

//----------------------------------------------------------------------------------------------------

function trans_qty_unit_cost($stock_id, $location=null, $from_date, $to_date, $inward = true)
{
    if ($from_date == null)
        $from_date = Today();

    $from_date = date2sql($from_date);

    if ($to_date == null)
        $to_date = Today();

    $to_date = date2sql($to_date);

    $sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id FROM ".TB_PREF."stock_moves
        WHERE stock_id=".db_escape($stock_id)."
        AND tran_date <= '$to_date' AND tran_date >= '$from_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;

    if ($location != '')
        $sql .= " AND loc_code = ".db_escape($location);

    if ($inward)
        $sql .= " AND qty > 0 ";
    else
        $sql .= " AND qty < 0 ";
    $sql .= " ORDER BY tran_date";
    $result = db_query($sql, "No standard cost transactions were returned");
    if ($result == false)
        return 0;
    $qty = $count = $old_qty = $old_std_cost = $tot_cost = 0;
    while ($row=db_fetch($result))
    {
        $qty += $row['qty'];

        $price = get_domestic_price($row, $stock_id, $qty, $old_std_cost, $old_qty);

            $tran_cost = $row['qty'] * $price;
            $tot_cost += $tran_cost;
            $count++;

        $old_std_cost = $row['standard_cost'];
        $old_qty = $qty;
    }
    if ($count == 0)
        return 0;
    return $tot_cost / $qty;

}

//----------------------------------------------------------------------------------------------------

function inventory_movements()
{
    global $path_to_root;

    $from_date = $_POST['PARAM_0'];
    $to_date = $_POST['PARAM_1'];
    $category = $_POST['PARAM_2'];
    $location = $_POST['PARAM_3'];
    $comments = $_POST['PARAM_4'];
    $orientation = $_POST['PARAM_5'];
    $destination = $_POST['PARAM_6'];
    if ($destination)
        include_once($path_to_root . "/reporting/includes/excel_report.inc");
    else
        include_once($path_to_root . "/reporting/includes/pdf_report.inc");

    $orientation = ($orientation ? 'L' : 'P');
    if ($category == ALL_NUMERIC)
        $category = 0;
    if ($category == 0)
        $cat = _('All');
    else
        $cat = get_category_name($category);

    if ($location == '')
        $loc = _('All');
    else
        $loc = get_location_name($location);

    $cols = array(0, 60, 130, 160, 185, 215, 250, 275, 305, 340, 365, 395, 430, 455, 485, 520);

    $headers = array(_('Category'), _('Description'),    _('UOM'), '', '', _('OpeningStock'), '', '',_('StockIn'), '', '', _('Delivery'), '', '', _('ClosingStock'));
    $headers2 = array("", "", "", _("QTY"), _("Rate"), _("Value"), _("QTY"), _("Rate"), _("Value"), _("QTY"), _("Rate"), _("Value"), _("QTY"), _("Rate"), _("Value"));

    $aligns = array('left',    'left',    'left', 'right', 'right', 'right', 'right','right' ,'right', 'right', 'right','right', 'right', 'right', 'right');

    $params =   array(     0 => $comments,
                        1 => array('text' => _('Period'), 'from' => $from_date, 'to' => $to_date),
                        2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
                        3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));

    $rep = new FrontReport(_('Costed Inventory Movements'), "CostedInventoryMovements", user_pagesize(), 8, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);

    $rep->Font();
    $rep->Info($params, $cols, $headers2, $aligns, $cols, $headers, $aligns);
    $rep->NewPage();

    $totval_open = $totval_in = $totval_out = $totval_close = 0;
    $result = fetch_items($category);

    $dec = user_price_dec();
    $catgor = '';
    while ($myrow=db_fetch($result))
    {
        if ($catgor != $myrow['description'])
        {
            $rep->NewLine(2);
            $rep->fontSize += 2;
            $rep->TextCol(0, 3, $myrow['category_id'] . " - " . $myrow['description']);
            $catgor = $myrow['description'];
            $rep->fontSize -= 2;
            $rep->NewLine();
        }
        $qoh_start = get_qoh_on_date($myrow['stock_id'], $location, add_days($from_date, -1));
        $qoh_end = get_qoh_on_date($myrow['stock_id'], $location, $to_date);

        $inward = trans_qty($myrow['stock_id'], $location, $from_date, $to_date);
        $outward = trans_qty($myrow['stock_id'], $location, $from_date, $to_date, false);
        $openCost = avg_unit_cost($myrow['stock_id'], $location, $from_date);
        $unitCost = avg_unit_cost($myrow['stock_id'], $location, add_days($to_date, 1));
        if ($qoh_start == 0 && $inward == 0 && $outward == 0 && $qoh_end == 0)
            continue;
        $rep->NewLine();
        $rep->TextCol(0, 1,    $myrow['stock_id']);
        $rep->TextCol(1, 2, $myrow['name']);
        $rep->TextCol(2, 3, $myrow['units']);
        $rep->AmountCol(3, 4, $qoh_start, get_qty_dec($myrow['stock_id']));
        $rep->AmountCol(4, 5, $openCost, $dec);
        $openCost *= $qoh_start;
        $totval_open += $openCost;
        $rep->AmountCol(5, 6, $openCost);

        if($inward>0){
            $rep->AmountCol(6, 7, $inward, get_qty_dec($myrow['stock_id']));
            $unitCost_in = trans_qty_unit_cost($myrow['stock_id'], $location, $from_date, $to_date);
            $rep->AmountCol(7, 8, $unitCost_in,$dec);
            $unitCost_in *= $inward;
            $totval_in += $unitCost_in;
            $rep->AmountCol(8, 9, $unitCost_in);
        }

        if($outward>0){
            $rep->AmountCol(9, 10, $outward, get_qty_dec($myrow['stock_id']));
            $unitCost_out =    trans_qty_unit_cost($myrow['stock_id'], $location, $from_date, $to_date, false);
            $rep->AmountCol(10, 11, $unitCost_out,$dec);
            $unitCost_out *= $outward;
            $totval_out += $unitCost_out;
            $rep->AmountCol(11, 12, $unitCost_out);
        }

        $rep->AmountCol(12, 13, $qoh_end, get_qty_dec($myrow['stock_id']));
        $rep->AmountCol(13, 14, $unitCost,$dec);
        $unitCost *= $qoh_end;
        $totval_close += $unitCost;
        $rep->AmountCol(14, 15, $unitCost);

        $rep->NewLine(0, 1);
    }
    $rep->Line($rep->row  - 4);
    $rep->NewLine(2);
    $rep->TextCol(0, 1,    _("Total"));
    $rep->AmountCol(5, 6, $totval_open);
    $rep->AmountCol(8, 9, $totval_in);
    $rep->AmountCol(11, 12, $totval_out);
    $rep->AmountCol(14, 15, $totval_close);
    $rep->Line($rep->row  - 4);

    $rep->End();
}

?>

The Exchange Rate Factor will only affect when we pick the rate from Supplier's table. But doing that will again disturb this report. Since I am picking the rate from stock_moves table and this rate is already adjusted by the exchange rate at the time of transaction so no need to take into account the factor of exchange rate.

Thanks Joe,

Its a great pleasure for me that I have contributed to FA.

We can't change rep301 because it is reconciled with the Inventory Valuation given by Trial Balance.

I am looking into this report further to have it reconciled with rep301 also.

/Faisal

There is again a logical Bug in rep301. If we set $use_costed_value = 1 it will call function getAverageCost(). In this function the algorithm to calculate Average Cost is not Weighted Average.

Please change with the following Code from Line 81 to 93

$qty += $row['qty'];

        $price = $row['standard_cost']; //get_domestic_price($row, $stock_id, $qty, $old_std_cost, $old_qty);

        //$old_std_cost = $row['standard_cost'];
        $tran_cost = $row['qty'] * $price;
        $tot_cost += $tran_cost;
        //$count++;
        $old_qty = $qty;
    }
    if ($qty == 0)
        return 0;
        return $tot_cost / $qty;

Here again there is no use of function get_domestic_price.

After committing this change the Costed Inventory Valuation by rep301 is same as that of rep308.

But both these Inventory Valuations are not reconciling with that of Trial Balance.

While with value of $use_costed_value = 0 the Inventory Valuation of rep301 reconciles with Trial Balance.

So now we are in a fix whether to reconcile rep301 with rep308 or with GL.

We need to further look into it minutely that these two reports shall also match with GL.

/Faisal

REP301

<?php
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision:    2.0 $
// Creator:    Joe Hunt
// date_:    2005-05-19
// Title:    Inventory Valuation
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");

//----------------------------------------------------------------------------------------------------

print_inventory_valuation_report();

function get_domestic_price($myrow, $stock_id)
{
    if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT)
    {
        $price = $myrow['price'];
        if ($myrow['person_id'] > 0)
        {
            // Do we have foreign currency?
            $supp = get_supplier($myrow['person_id']);
            $currency = $supp['curr_code'];
            $ex_rate = get_exchange_rate_to_home_currency($currency, sql2date($myrow['tran_date']));
            $price /= $ex_rate;
        }
    }
    else
        $price = $myrow['standard_cost']; // Item Adjustments just have the real cost
    return $price;
}

function getAverageCost($stock_id, $to_date)
{
    if ($to_date == null)
        $to_date = Today();

    $to_date = date2sql($to_date);

    $sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id FROM ".TB_PREF."stock_moves
        WHERE stock_id=".db_escape($stock_id)."
        AND tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;

    $sql .= " ORDER BY tran_date";

    $result = db_query($sql, "No standard cost transactions were returned");
    if ($result == false)
        return 0;
    $qty = $old_qty = $count = $old_std_cost = $tot_cost = 0;
    while ($row=db_fetch($result))
    {
        $qty += $row['qty'];

        $price = get_domestic_price($row, $stock_id);

        //$old_std_cost = $row['standard_cost'];
        $tran_cost = $row['qty'] * $price;
        $tot_cost += $tran_cost;
        //$count++;
        $old_qty = $qty;
    }
    if ($qty == 0)
        return 0;
        return $tot_cost / $qty;
}

function getTransactions($category, $location, $date)
{
    $date = date2sql($date);

    $sql = "SELECT ".TB_PREF."stock_master.category_id,
            ".TB_PREF."stock_category.description AS cat_description,
            ".TB_PREF."stock_master.stock_id,
            ".TB_PREF."stock_master.units,
            ".TB_PREF."stock_master.description, ".TB_PREF."stock_master.inactive,
            ".TB_PREF."stock_moves.loc_code,
            SUM(".TB_PREF."stock_moves.qty) AS QtyOnHand,
            ".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost AS UnitCost,
            SUM(".TB_PREF."stock_moves.qty) *(".TB_PREF."stock_master.material_cost + ".TB_PREF."stock_master.labour_cost + ".TB_PREF."stock_master.overhead_cost) AS ItemTotal
            FROM ".TB_PREF."stock_master,
            ".TB_PREF."stock_category,
            ".TB_PREF."stock_moves
        WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."stock_moves.stock_id
        AND ".TB_PREF."stock_master.category_id=".TB_PREF."stock_category.category_id
        AND ".TB_PREF."stock_master.mb_flag<>'D'
        AND ".TB_PREF."stock_moves.tran_date <= '$date'
        GROUP BY ".TB_PREF."stock_master.category_id,
            ".TB_PREF."stock_category.description, ";
        if ($location != 'all')
            $sql .= TB_PREF."stock_moves.loc_code, ";
        $sql .= TB_PREF."stock_master.stock_id,
            ".TB_PREF."stock_master.description
        HAVING SUM(".TB_PREF."stock_moves.qty) != 0";
        if ($category != 0)
            $sql .= " AND ".TB_PREF."stock_master.category_id = ".db_escape($category);
        if ($location != 'all')
            $sql .= " AND ".TB_PREF."stock_moves.loc_code = ".db_escape($location);
        $sql .= " ORDER BY ".TB_PREF."stock_master.category_id,
            ".TB_PREF."stock_master.stock_id";

    return db_query($sql,"No transactions were returned");
}

//----------------------------------------------------------------------------------------------------

function print_inventory_valuation_report()
{
    global $path_to_root, $use_costed_values;;

    $date = $_POST['PARAM_0'];
    $category = $_POST['PARAM_1'];
    $location = $_POST['PARAM_2'];
    $detail = $_POST['PARAM_3'];
    $comments = $_POST['PARAM_4'];
    $orientation = $_POST['PARAM_5'];
    $destination = $_POST['PARAM_6'];
    if ($destination)
        include_once($path_to_root . "/reporting/includes/excel_report.inc");
    else
        include_once($path_to_root . "/reporting/includes/pdf_report.inc");
    $detail = !$detail;
    $dec = user_price_dec();

    $orientation = ($orientation ? 'L' : 'P');
    if ($category == ALL_NUMERIC)
        $category = 0;
    if ($category == 0)
        $cat = _('All');
    else
        $cat = get_category_name($category);

    if ($location == ALL_TEXT)
        $location = 'all';
    if ($location == 'all')
        $loc = _('All');
    else
        $loc = get_location_name($location);

    $cols = array(0, 75, 225, 250, 350, 450,    515);

    $headers = array(_('Category'), '', _('UOM'), _('Quantity'), _('Unit Cost'), _('Value'));

    $aligns = array('left',    'left',    'left', 'right', 'right', 'right');

    $params =   array(     0 => $comments,
                        1 => array('text' => _('End Date'), 'from' => $date,         'to' => ''),
                        2 => array('text' => _('Category'), 'from' => $cat, 'to' => ''),
                        3 => array('text' => _('Location'), 'from' => $loc, 'to' => ''));

    $rep = new FrontReport(_('Inventory Valuation Report'), "InventoryValReport", user_pagesize(), 9, $orientation);
    if ($orientation == 'L')
        recalculate_cols($cols);
    $rep->Font();
    $rep->Info($params, $cols, $headers, $aligns);
    $rep->NewPage();

    $res = getTransactions($category, $location, $date);
    $total = $grandtotal = 0.0;
    $catt = '';
    while ($trans=db_fetch($res))
    {
        if ($catt != $trans['cat_description'])
        {
            if ($catt != '')
            {
                if ($detail)
                {
                    $rep->NewLine(2, 3);
                    $rep->TextCol(0, 4, _('Total'));
                }
                $rep->AmountCol(5, 6, $total, $dec);
                if ($detail)
                {
                    $rep->Line($rep->row - 2);
                    $rep->NewLine();
                }
                $rep->NewLine();
                $total = 0.0;
            }
            $rep->TextCol(0, 1, $trans['category_id']);
            $rep->TextCol(1, 2, $trans['cat_description']);
            $catt = $trans['cat_description'];
            if ($detail)
                $rep->NewLine();
        }
        if (isset($use_costed_values) && $use_costed_values==1)
        {
            $UnitCost = getAverageCost($trans['stock_id'], $date);
            $ItemTotal = $trans['QtyOnHand'] * $UnitCost;
        }
        else
        {
            $UnitCost = $trans['UnitCost'];
            $ItemTotal = $trans['ItemTotal'];
        }
        if ($detail)
        {
            $rep->NewLine();
            $rep->fontSize -= 2;
            $rep->TextCol(0, 1, $trans['stock_id']);
            $rep->TextCol(1, 2, $trans['description'].($trans['inactive']==1 ? " ("._("Inactive").")" : ""), -1);
            $rep->TextCol(2, 3, $trans['units']);
            $rep->AmountCol(3, 4, $trans['QtyOnHand'], get_qty_dec($trans['stock_id']));

            $dec2 = 0;
            price_decimal_format($UnitCost, $dec2);
            $rep->AmountCol(4, 5, $UnitCost, $dec2);
            $rep->AmountCol(5, 6, $ItemTotal, $dec);
            $rep->fontSize += 2;
        }
        $total += $ItemTotal;
        $grandtotal += $ItemTotal;
    }
    if ($detail)
    {
        $rep->NewLine(2, 3);
        $rep->TextCol(0, 4, _('Total'));
    }
    $rep->Amountcol(5, 6, $total, $dec);
    if ($detail)
    {
        $rep->Line($rep->row - 2);
        $rep->NewLine();
    }
    $rep->NewLine(2, 1);
    $rep->TextCol(0, 4, _('Grand Total'));
    $rep->AmountCol(5, 6, $grandtotal, $dec);
    $rep->Line($rep->row  - 4);
    $rep->NewLine();
    $rep->End();
}

?>

Thanks boxygen.

Will look into this and optimize it. Forget my earlier files. Will remove them.

/Joe

Thanks @joe, @boxygen.

Committed to my repo after synching the whitespaces first so that the actual changes alone can be visible clearly.

A little modification to rep301 is needed.

Replace lines from 209-218 with following lines

$UnitCost = getAverageCost($trans['stock_id'], $location, $date);
$ItemTotal = $trans['QtyOnHand'] * $UnitCost;

Hence now no need of $use_costed_values in config.php