Topic: Costed Inventory Movement Report is not Reconciled

Hello,

If you login here  Costed Inventory Movement

with id: admin
pass: 1234
company: gp

You will find that the Report is not Reconciled

i.e. Opening Stock + Stock In - Deliveries IS NOT EQUAL TO  Closing Stock

I tried with different categories also and found the same Non Reconciled.

If you try the Item Category 4x4 Ltr. This is the simplest. You will find the difference in Rates that are causing the difference.


Any Ideas

Re: Costed Inventory Movement Report is not Reconciled

Are you referring to 2.3 or 2.4 or maybe both?

Joe

Re: Costed Inventory Movement Report is not Reconciled

Ah, I see. 2.3. Will have a look into this.

Joe

Re: Costed Inventory Movement Report is not Reconciled

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

Re: Costed Inventory Movement Report is not Reconciled

Actually I implemented this ERP in an organization. The CFO is a chartered accountant. He put me in a difficult position that how to reconcile the COGS reflecting in Profit and Loss through Inventory Movement.

He said that COGS shall be come through formula Opening Inventory + Stock In - Closing Inventory.

For that purpose I had to review this report but the report itself is not correct.

So Please try to check the Code and Correct this bug.

Thanks

Re: Costed Inventory Movement Report is not Reconciled

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

Re: Costed Inventory Movement Report is not Reconciled

The change in currency rates can change the value of the stock so that the value at the end is not the same as in the General Ledger.

To compensate for this you may enter a Journal Voucher.

This report gives you a more adequate value than the report on the average standard costs over time.

/Joe

Re: Costed Inventory Movement Report is not Reconciled

<?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();
}

?>

Re: Costed Inventory Movement Report is not Reconciled

I have made few adjustments to this report.

1. Forced to return only the standard cost of the transaction instead of any supplier price or any other factor from the function get_domestic_price()

2. The Weighted Average Cost Calculation was not done in two functions avg_unit_cost() and trans_qty_unit_cost()

Now this report is reconciled means it is showing the result Opening Stock + Stock In - Delivers = Closing Stock

But it has few assumptions.

The Closing Stock Value may differ from Stock Valuation Report because in this report the base is only Transactional Cost i.e. standard_cost from stock_moves table. While the Stock Valuation report depends on latest Avg Cost of the item.

Please check and confirm.

Re: Costed Inventory Movement Report is not Reconciled

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.

Re: Costed Inventory Movement Report is not Reconciled

Yes, I have been looking into this also tonight and think you are right. What you mean is to  use to the stock_moves standard_cost instead of calcutating from the purchase price, right?

I guess your idea is better. The question is if we should change rep301 too in accordance to this. What do you think?

I have changed the copyright to FrontAccounting LLC and inserted your name as the creator. I have also optimized the report.

Please rename the file to extension php.

Can I commit this report and also in 2.4?

/Joe

Post's attachments

rep308.php1 10.2 kb, 5 downloads since 2017-05-13 

You don't have the permssions to download the attachments of this post.

Re: Costed Inventory Movement Report is not Reconciled

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

Re: Costed Inventory Movement Report is not Reconciled

Hello boxygen,

There is a flag in config.php, called $use_costed_values that is set to 0 by default. In that case the standard cost set in the stock_master table (the last average standard cost) is used all over the period. This is only used in rep301.php

If this flag is set to 1, it will use the costed values all over the period, and the total will be the same as in the Costed Inventory Movements.

The reason for setting this variable $use_costed_values to 0 (default) is that this also can cope with manual changes in the standard costs.

I guess we use the same algorithms for Costed Values in both rep 301 and 308. Right?

/Joe

14 (edited by boxygen 05/14/2017 11:05:02 am)

Re: Costed Inventory Movement Report is not Reconciled

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

Re: Costed Inventory Movement Report is not Reconciled

Hurrah,

Finally got all reports reconciled with each other as well as with GL.

However I am not claiming so please have a critical look.

I edited the function get_domestic_price() in both rep301 and rep308.

Now both of them are reconciled with each other as well as GL whether you set the flag $use_costed_value to 1 or 0.

Re: Costed Inventory Movement Report is not Reconciled

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();
}

?>

Re: Costed Inventory Movement Report is not Reconciled

REP308

<?php
/**********************************************************************
    Copyright (C) FrontAccounting, 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.1 $
// Creator:        boxygen
// date_:        2017-05-12
// Title:        Costed Inventory 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)
{
    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 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 = $tot_cost = 0;
    while ($row=db_fetch($result))
    {
        $qty += $row['qty'];
        $price = get_domestic_price($row, $stock_id);
        $tran_cost = $price * $row['qty'];
        $tot_cost += $tran_cost;
    }
    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 = $tot_cost = 0;
    while ($row=db_fetch($result))
    {
        $qty += $row['qty'];
        $price = get_domestic_price($row, $stock_id);
        $tran_cost = $row['qty'] * $price;
        $tot_cost += $tran_cost;
    }
    if ($qty != 0)
        return $tot_cost/ $qty;
    else
        return 0; //by Faisal
}

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

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();
}
?>

Re: Costed Inventory Movement Report is not Reconciled

Thanks boxygen.

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

/Joe

Re: Costed Inventory Movement Report is not Reconciled

This has now been fixed and committed to 2.3 stable and 2.4 unstable repo.

Thanks for your participation, boxygen. You have made my day smile

/Joe

Re: Costed Inventory Movement Report is not Reconciled

Thanks @joe, @boxygen.

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

Re: Costed Inventory Movement Report is not Reconciled

Its my pleasure. Thanks for giving us such a rich platform.

Re: Costed Inventory Movement Report is not Reconciled

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

Re: Costed Inventory Movement Report is not Reconciled

Thanks for pointing this out.
We are no longer supporting release 2.3, but for those still using it they could just set the value $use_costed_values to 1 instead of 0 in config.php.

For backwards compatibility in 2.4 we will just change the value $use_costed_values to 1 in config.default.php, so all new users will automatically get this option.

Should they prefer the old method for some reason, they can set this value to 0.

Thanks @boxygen for helping with this.

/Joe

Re: Costed Inventory Movement Report is not Reconciled

We need to set this as a comment in the config file so that it is not used.

The line numbers stated by @boxygen are for the FA 2.4 version of reporting/rep301.php. The equivalent line numbers for FA 2.3 are 187 to 196.