xTuple.com xTupleU Blog & News Customer Support

Inventory QOH Report as of a specific date with Lot# and Expiration Information

I need to generate a report of QOH inventory on 30Jun2022 that lists the item #, description, qty, Lot# and expiration date. How do I go about in generating this? I tried to use the “As of” filter but it does not give any results. Is this an issue/glitch with our xTuple or is this system wide?

Is the “As Of” processing option turned on? This is in Setup, in the Inventory tab.

Screen Shot 2022-08-17 at 12.07.05

Yes it is.

From looking at the caption line on your screen shot it appears you have a customized version of this report and perhaps that is the reason your report is not working. While the As Of will give item totals I don’t think it is possible to have the quantities broken down by Lot or Serial on the As Of date.

Larry Cartee

This won’t help you look back now, but if you will need this kind of thing later I would suggest you create a display screen with the data you want and have xtConnect run it to a file each night, or maybe run a query that adds to a custom table with all the fields you need plus a “time run” field.

Phil

Hi Larry,

Tried the default and same results… no results showing

Have you tried this without a site filter? We should establish whether you have ANY “as of” data.

Hello Scott,

I did, it shows a number for the results found as 9103 but no actual results showing in the report.

On the caption line I see Class Code / Lot Serial #, Cost Category/Expiration Date, and UOM / Lot Qty.
These are not captions or information available from the standard Qty On Hand report and that is why I believe there is a custom report that does not work for As Of.

Larry Cartee

Hi Larry,

These columns are from xTuple. I removed them and no results showing in the report.

image

Something strange is happening. It looks like you have 9103 records, but nothing is showing?

Screen Shot 2022-08-17 at 14.17.58

This is what Larry is referring to. The standard version of the Quantity on Hand report doesn’t show the Lot information that is on your screen.

Thank you for the clarification. I was not aware our list of columns is different.

It may appear this is a lost cause… and I will not be able to generate a report with the required information.

Thank you all for your time and help.

cloi, Who did the customization on your window? If you want you can send me the script and metasql and I can take a look at those things to help, But it looks like someone has already changed some things within your companies database.

you can check by going to System > Design > Scripts and finding a script called dspQOH if you see a script there it means that someone has customized that screen on your company database.

I can look through that script as well as the query for the data.

to get the query for the data go to System > Design > MetaSQL Statements it will be group: qoh name: detail

I can look at those and compare them to what we have here and at least point you on the right track

1 Like

Hello Caleb,

All customization of xTuple was done way before my time (2019), therefore I do not know who requested or who did the customization.

I did see the dspQOH; script below.

for the MetaSQL, there are two

image

Script

debugger;

// Function to rename column uom_name to “UOM / Lot Qty”

// rename column costcat_code and column classcode_code

function renameHeaders()

{

var list = mywindow.list();

var header = list.headerItem();

var uomcol = list.column(“uom_name”);

header.setText(uomcol, qsTr(“UOM /\r\n Lot Qty”));

mywindow.list().headerItem().setTextAlignment(mywindow.list().column(“uom_name”), Qt.AlignRight);

var costcatcol = list.column(“costcat_code”);

header.setText(costcatcol, qsTr(" Cost Category /\r\n Expiration Date"));

var classcol = list.column(“classcode_code”);

header.setText(classcol, qsTr(" Class Code /\r\n Lot Serial #"));

}

function showEvent()

{

renameHeaders()

}

xTuple works by only using the highest grade query. It works as a version control of sorts so you don’t loose any of your previous work on accident.

All the script does is rename the columns.

So if you wanted to test xTuple’s default report you can open the grade 0 report and save it as grade 6.
this should give you 3 reports. A grade 0,5,6. it will only run the grade 6 one.

Also if you want you can paste the Grade 5 sql here for me to look at real quick

Hello Caleb,

Here is the Grade 5. I will try you suggestion regarding Grade 6 and advise outcome

– Group: qoh
– Name: detail
– Notes:
– Quantity on Hand By Location
– Copyright © 1999-2014 by OpenMFG LLC, d/b/a xTuple.
– See www.xtuple.com/CPAL for the full text of the software license.

<? if exists("byLocation") ?>

SELECT itemloc_id, alt_id, warehous_code, item_number,
f_descrip, f_lotserial, uom_name,
qoh, reservedqty,
‘qty’ AS qoh_xtnumericrole,
‘qty’ AS reservedqty_xtnumericrole,
level AS xtindentrole
FROM (
SELECT itemloc_id, 0 AS alt_id, 0 AS level, item_number AS sortkey, warehous_code, item_number,
(item_descrip1 || ’ ’ || item_descrip2) AS f_descrip,
formatlotserialnumber(itemloc_ls_id) AS f_lotserial, uom_name,
itemloc_qty AS qoh,

<? if exists("EnableSOReservationsByLocation") ?>
         qtyReservedLocation(itemloc_id) AS reservedqty
<? else ?>
         0 AS reservedqty
<? endif ?>
  FROM itemloc, itemsite, whsinfo, item, uom
  WHERE ((itemloc_itemsite_id=itemsite_id)
     AND (itemsite_item_id=item_id)
     AND (item_inv_uom_id=uom_id)
     AND (itemsite_warehous_id=warehous_id)
     AND (itemloc_location_id=<? value("location_id") ?>))
  UNION
  SELECT -1 AS itemloc_id, 0 AS alt_id, 0 AS level, item_number AS sortkey, warehous_code, item_number,
         (item_descrip1 || ' ' || item_descrip2) AS f_descrip,
         <? value("na") ?> AS f_lotserial, uom_name,
         itemsite_qtyonhand AS qoh,
         0 AS reservedqty
  FROM itemsite, whsinfo, item, uom
  WHERE ((itemsite_item_id=item_id)
     AND (item_inv_uom_id=uom_id)
     AND (itemsite_warehous_id=warehous_id)
     AND (NOT itemsite_loccntrl)
     AND (itemsite_location_id=<? value("location_id") ?>))
<? if exists("EnableSOReservationsByLocation") ?>
  UNION
  SELECT itemloc_id, -1 AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
         (reserve_demand_type || '-' || formatSOItemNumber(reserve_demand_id)) AS f_descrip,
         '' AS f_lotserial, '' AS uom_name,
         NULL AS qoh,
         reserve_qty AS reservedqty
  FROM reserve, itemloc, itemsite, item
  WHERE ((reserve_supply_id=itemloc_id)
     AND (reserve_supply_type='I')
     AND (itemsite_id=itemloc_itemsite_id)
     AND(item_id=itemsite_item_id)
     AND(itemloc_location_id=<? value("location_id") ?>))
<? endif ?> <? if exists("ShowDemand") ?>
  UNION
  SELECT itemloc_id, coitem_cohead_id AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
         (<? value("so") ?>|| '-' || formatSOItemNumber(coitem_id)) AS f_descrip,
         '' AS f_lotserial, '' AS uom_name,
         itemUOMtoUOM(item_id, coitem_qty_uom_id, NULL, (coitem_qtyord - coitem_qtyshipped - qtyAtShipping(coitem_id))) AS qoh,
         0 AS reservedqty
  FROM itemloc JOIN itemsite ON (itemsite_id=itemloc_itemsite_id)
               JOIN item ON (item_id=itemsite_item_id)
               JOIN coitem ON (coitem_itemsite_id=itemloc_itemsite_id AND coitem_status='O')
  WHERE ((coitem_qtyord > (coitem_qtyshipped + qtyAtShipping(coitem_id)))
     AND(itemloc_location_id=<? value("location_id") ?>))
  UNION
  SELECT itemloc_id, -1 AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
         (<? value("wo") ?>|| '-' || formatWONumber(womatl_wo_id)) AS f_descrip,
         '' AS f_lotserial, '' AS uom_name,
         (womatl_qtyreq - womatl_qtyiss) AS qoh,
         0 AS reservedqty
  FROM itemloc JOIN itemsite ON (itemsite_id=itemloc_itemsite_id)
               JOIN item ON (item_id=itemsite_item_id)
               JOIN womatl ON (womatl_itemsite_id=itemloc_itemsite_id)
  WHERE ((womatl_qtyreq > womatl_qtyiss)
     AND(itemloc_location_id=<? value("location_id") ?>))
  UNION
  SELECT itemloc_id, -1 AS alt_id, 1 AS level, item_number AS sortkey, '' AS warehous_code, '' AS item_number,
         (<? value("to") ?>|| '-' || formatTONumber(toitem_id)) AS f_descrip,
         '' AS f_lotserial, '' AS uom_name,
         (toitem_qty_ordered - toitem_qty_shipped - qtyAtShipping('TO', toitem_id)) AS qoh,
         0 AS reservedqty
  FROM itemloc JOIN itemsite ON (itemsite_id=itemloc_itemsite_id)
               JOIN item ON (item_id=itemsite_item_id)
               JOIN toitem ON (toitem_item_id=item_id AND toitem_status='O')
               JOIN tohead ON (tohead_id=toitem_tohead_id AND tohead_src_warehous_id=itemsite_warehous_id)
  WHERE ((toitem_qty_ordered > (toitem_qty_shipped + qtyAtShipping('TO', toitem_id)))
     AND(itemloc_location_id=<? value("location_id") ?>))
<? endif ?>
 ) AS data

ORDER BY
sortkey, itemloc_id, level;

<? else ?> <? if exists("asOf") ?>

SELECT forwardupdateinvbalance(invbal_id)
FROM (
SELECT DISTINCT ON (itemsite_id) invbal_id
FROM invbal, itemsite, item, whsinfo, classcode, uom, costcat, period
WHERE ((invbal_dirty)
AND (invbal_period_id=period_id)
AND (itemsite_item_id=item_id)
AND (itemsite_warehous_id=warehous_id)
AND (itemsite_active)
AND (item_inv_uom_id=uom_id)
AND (item_classcode_id=classcode_id)
AND (itemsite_costcat_id=costcat_id)

<? if exists("item_id") ?>
     AND (itemsite_item_id=<? value("item_id") ?>)
<? endif ?> <? if exists("classcode_id") ?>
     AND (classcode_id=<? value("classcode_id") ?>)
<? endif ?> <? if exists("classcode_pattern") ?>
     AND (classcode_id IN (SELECT classcode_id
                           FROM classcode
                           WHERE classcode_code ~ <? value("classcode_pattern") ?>))
<? endif ?> <? if exists("costcat_id") ?>
     AND (costcat_id=<? value("costcat_id") ?>)
<? endif ?> <? if exists("costcat_pattern") ?>
     AND (costcat_id IN (SELECT costcat_id
                           FROM costcat
                           WHERE costcat_code ~ <? value("costcat_pattern") ?>))
<? endif ?> <? if exists("itemgrp_id") ?>
     AND (item_id IN (SELECT itemgrpitem_item_id
                      FROM itemgrpitem
                      WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
<? endif ?> <? if exists("itemgrp_pattern") ?>
     AND (item_id IN (SELECT itemgrpitem_item_id
                      FROM itemgrpitem, itemgrp
                      WHERE ((itemgrpitem_itemgrp_id=itemgrp_id)
                         AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>))))
<? endif ?> <? if exists("showPositive") ?>
   AND (itemsite_qtyonhand > 0)
<? elseif exists("showNegative") ?>
   AND (itemsite_qtyonhand < 0)
<? endif ?> <? if exists("warehous_id") ?>
     AND (itemsite_warehous_id=<? value("warehous_id") ?>)
<? endif ?>
        )
   ORDER BY itemsite_id, period_start
 ) AS data

;

<? endif ?>

– Quantity on Hand
SELECT itemsite_id, level AS xtindentrole, detail, warehous_code,
classcode_code, item_number, uom_name,
costcat_code,
item_descrip1, item_descrip2,
(item_descrip1 || ’ ’ || item_descrip2) AS itemdescrip,
defaultlocation,
reorderlevel, formatQty(reorderlevel) AS f_reorderlevel,
qoh, formatQty(qoh) AS f_qoh,
availqoh, formatQty(availqoh) AS f_availqoh,
nonavailqoh, formatQty(nonavailqoh) AS f_nonavailqoh,
netqoh, formatQty(netqoh) AS f_netqoh,
nonnetqoh, formatQty(nonnetqoh) AS f_nonnetqoh,
cost, (cost * qoh) AS value,
(cost * availqoh) AS availvalue,
(cost * nonavailqoh) AS nonavailvalue,
(cost * netqoh) AS netvalue,
(cost * nonnetqoh) AS nonnetvalue,
CASE WHEN(itemsite_costmethod=‘A’) THEN ‘Average’
WHEN(itemsite_costmethod=‘S’) THEN ‘Standard’
WHEN(itemsite_costmethod=‘J’) THEN ‘Job’
WHEN(itemsite_costmethod=‘N’) THEN ‘None’
ELSE ‘UNKNOWN’
END AS costmethod,

<? if exists("showValue") ?>
   formatMoney(cost) AS f_cost,
   formatMoney(cost * qoh) AS f_value,
   formatMoney(cost * availqoh) AS f_availvalue,
   formatMoney(cost * nonavailqoh) AS f_nonavailvalue,
   formatMoney(cost * netqoh) AS f_netvalue,
   formatMoney(cost * nonnetqoh) AS f_nonnetvalue,
   CASE WHEN(itemsite_costmethod='A') THEN 'Average'
        WHEN(itemsite_costmethod='S') THEN 'Standard'
        WHEN(itemsite_costmethod='J') THEN 'Job'
        WHEN(itemsite_costmethod='N') THEN 'None'
     ELSE 'UNKNOWN'
   END AS f_costmethod,
<? endif ?>
   'qty' AS reorderlevel_xtnumericrole,
   'qty' AS qoh_xtnumericrole,
   'qty' AS availqoh_xtnumericrole,
   'qty' AS nonavailqoh_xtnumericrole,
   'qty' AS netqoh_xtnumericrole,
   'qty' AS nonnetqoh_xtnumericrole,
   0 AS qoh_xttotalrole,
   0 AS availqoh_xttotalrole,
   0 AS nonavailqoh_xttotalrole,
   0 AS netqoh_xttotalrole,
   0 AS nonnetqoh_xttotalrole,
   'cost' AS cost_xtnumericrole,
   'curr' AS value_xtnumericrole,
   'curr' AS availvalue_xtnumericrole,
   'curr' AS nonavailvalue_xtnumericrole,
   'curr' AS netvalue_xtnumericrole,
   'curr' AS nonnetvalue_xtnumericrole,
   0 AS value_xttotalrole,
   0 AS availvalue_xttotalrole,
   0 AS nonavailvalue_xttotalrole,
   0 AS netvalue_xttotalrole,
   0 AS nonnetvalue_xttotalrole,
   <? value("na") ?> AS availqoh_xtnullrole,
   <? value("na") ?> AS nonavailqoh_xtnullrole,
   <? value("na") ?> AS availvalue_xtnullrole,
   <? value("na") ?> AS nonavailvalue_xtnullrole,
   <? value("na") ?> AS netqoh_xtnullrole,
   <? value("na") ?> AS nonnetqoh_xtnullrole,
   <? value("na") ?> AS netvalue_xtnullrole,
   <? value("na") ?> AS nonnetvalue_xtnullrole,
   CASE WHEN (qoh < 0) THEN 'error' END AS qoh_qtforegroundrole,
   CASE WHEN (reorderlevel > qoh) THEN 'warning' END AS qoh_qtforegroundrole

FROM (
SELECT itemsite_id, 0 AS level, itemsite_loccntrl, itemsite_costmethod,
((itemsite_loccntrl) OR (itemsite_controlmethod IN (‘L’, ‘S’)) ) AS detail,
classcode_code, item_number, uom_name, item_descrip1, item_descrip2,
costcat_code,
CASE WHEN (NOT useDefaultLocation(itemsite_id)) THEN <? value("none") ?>
ELSE defaultLocationName(itemsite_id)
END AS defaultlocation,
warehous_code,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,

<? if exists("asOf") ?>
         COALESCE(invbal_qoh_ending,0) AS qoh,
         COALESCE(invbal_qoh_ending,0) AS availqoh,
         COALESCE(invbal_nn_ending,0) AS nonavailqoh,
         COALESCE(invbal_qoh_ending,0) AS netqoh,
         COALESCE(invbal_nn_ending,0) AS nonnetqoh,
<? else ?>
         itemsite_qtyonhand AS qoh,
         qtyAvailable(itemsite_id) AS availqoh,
         qtyAvailable(itemsite_id, FALSE) AS nonavailqoh,
         qtyNetable(itemsite_id) AS netqoh,
         qtyNetable(itemsite_id, FALSE) AS nonnetqoh,
<? endif ?> <? if exists("useStandardCosts") ?>
         stdcost(item_id) AS cost
<? elseif exists("useActualCosts") ?>
         actcost(item_id) AS cost
<? else ?>
<? if exists("asOf") ?>
         COALESCE((invbal_value_ending / CASE WHEN(invbal_qoh_ending=0) THEN 1
                                              ELSE invbal_qoh_ending END),0) AS cost
<? else ?>
         (itemsite_value / CASE WHEN(itemsite_qtyonhand=0) THEN 1
                                ELSE itemsite_qtyonhand END) AS cost
<? endif ?>
<? endif ?>
  FROM item, whsinfo, classcode, uom, costcat, itemsite
<? if exists("asOf") ?>
    LEFT OUTER JOIN invbal ON ((itemsite_id=invbal_itemsite_id)
                           AND (invbal_period_id=<? value("asOf") ?>))
<? endif ?>
  WHERE ((itemsite_item_id=item_id)
     AND (itemsite_warehous_id=warehous_id)
     AND (itemsite_active)
     AND (item_inv_uom_id=uom_id)
     AND (item_classcode_id=classcode_id)
     AND (itemsite_costcat_id=costcat_id)
<? if exists("item_id") ?>
     AND (itemsite_item_id=<? value("item_id") ?>)
<? endif ?> <? if exists("classcode_id") ?>
     AND (classcode_id=<? value("classcode_id") ?>)
<? endif ?> <? if exists("classcode_pattern") ?>
     AND (classcode_id IN (SELECT classcode_id
                           FROM classcode
                           WHERE classcode_code ~ <? value("classcode_pattern") ?>))
<? endif ?> <? if exists("costcat_id") ?>
     AND (costcat_id=<? value("costcat_id") ?>)
<? endif ?> <? if exists("costcat_pattern") ?>
     AND (costcat_id IN (SELECT costcat_id
                           FROM costcat
                           WHERE costcat_code ~ <? value("costcat_pattern") ?>))
<? endif ?> <? if exists("itemgrp_id") ?>
     AND (item_id IN (SELECT itemgrpitem_item_id
                      FROM itemgrpitem
                      WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
<? endif ?> <? if exists("itemgrp_pattern") ?>
     AND (item_id IN (SELECT itemgrpitem_item_id
                      FROM itemgrpitem, itemgrp
                      WHERE ((itemgrpitem_itemgrp_id=itemgrp_id)
                         AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>))))
<? endif ?> <? if exists("showPositive") ?>
<? if exists("asOf") ?>
   AND (COALESCE(invbal_qoh_ending,0) > 0)
<? else ?>
   AND (itemsite_qtyonhand > 0)
<? endif ?>
<? elseif exists("showNegative") ?>
<? if exists("asOf") ?>
   AND (COALESCE(invbal_qoh_ending,0) < 0)
<? else ?>
   AND (itemsite_qtyonhand < 0)
<? endif ?>
<? endif ?> <? if exists("warehous_id") ?>
     AND (itemsite_warehous_id=<? value("warehous_id") ?>)
<? endif ?>
        )
 ) AS data

UNION

(SELECT itemsite_id, 1 AS level, NULL,
warehous_code,
CASE WHEN (itemsite_controlmethod NOT IN (‘L’, ‘S’)) THEN text(‘N/A’)
ELSE formatlotserialnumber(itemloc_ls_id)
END AS lotserial,
item_number,
formatQty(itemloc_qty) AS f_qty,
CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
ELSE text(‘N/A’)
END AS f_expiration, NULL, NULL,
CASE WHEN (location_id IS NULL) THEN text(’ Lot/Serial #’)
ELSE firstLine(location_descrip)
END AS f_descrip,
CASE WHEN (location_id IS NULL) THEN text(‘N/A’)
ELSE formatLocationName(location_id)
END AS locationname,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
CASE WHEN (location_id IS NULL) THEN text(‘N/A’)
WHEN (location_netable) THEN text(‘Yes’)
ELSE text(‘No’)
END AS f_netable,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
<? if exists("showValue") ?>
NULL AS f_cost,
NULL AS f_value,
NULL AS f_availvalue,
NULL AS f_nonavailvalue,
NULL AS f_netvalue,
NULL AS f_nonnetvalue,
NULL AS f_costmethod,
<? endif ?>
‘qty’ AS reorderlevel_xtnumericrole,
‘qty’ AS qoh_xtnumericrole,
‘qty’ AS availqoh_xtnumericrole,
‘qty’ AS nonavailqoh_xtnumericrole,
‘qty’ AS netqoh_xtnumericrole,
‘qty’ AS nonnetqoh_xtnumericrole,
NULL AS qoh_xttotalrole,
0 AS availqoh_xttotalrole,
0 AS nonavailqoh_xttotalrole,
0 AS netqoh_xttotalrole,
0 AS nonnetqoh_xttotalrole,
‘cost’ AS cost_xtnumericrole,
‘curr’ AS value_xtnumericrole,
‘curr’ AS availvalue_xtnumericrole,
‘curr’ AS nonavailvalue_xtnumericrole,
‘curr’ AS netvalue_xtnumericrole,
‘curr’ AS nonnetvalue_xtnumericrole,
0 AS value_xttotalrole,
0 AS availvalue_xttotalrole,
0 AS nonavailvalue_xttotalrole,
0 AS netvalue_xttotalrole,
0 AS nonnetvalue_xttotalrole,
<? value("na") ?> AS availqoh_xtnullrole,
<? value("na") ?> AS nonavailqoh_xtnullrole,
<? value("na") ?> AS availvalue_xtnullrole,
<? value("na") ?> AS nonavailvalue_xtnullrole,
<? value("na") ?> AS netqoh_xtnullrole,
<? value("na") ?> AS nonnetqoh_xtnullrole,
<? value("na") ?> AS netvalue_xtnullrole,
<? value("na") ?> AS nonnetvalue_xtnullrole,
<? value("na") ?> AS qoh_qtforegroundrole,
<? value("na") ?> AS qoh_qtforegroundrole
FROM itemsite, whsinfo,item,
itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
WHERE ( ( (itemsite_loccntrl) OR (itemsite_controlmethod IN (‘L’, ‘S’)) )
AND (itemloc_itemsite_id=itemsite_id)
AND (itemsite_warehous_id=warehous_id)
AND (itemsite_item_id=item_id)
/*<? if exists("warehous_id") ?>
AND (itemsite_warehous_id=<? value("warehous_id") ?>)

<? endif ?> <? if NOT exists("showZeroLevel") ?>

AND (itemsite_qtyonhand <> 0)

<? endif ?>*/ <? if exists("item_id") ?>
     AND (itemsite_item_id=<? value("item_id") ?>)
<? endif ?> <? if exists("classcode_id") ?>
     AND (classcode_id=<? value("classcode_id") ?>)
<? endif ?> <? if exists("classcode_pattern") ?>
     AND (classcode_id IN (SELECT classcode_id
                           FROM classcode
                           WHERE classcode_code ~ <? value("classcode_pattern") ?>))
<? endif ?> <? if exists("costcat_id") ?>
     AND (costcat_id=<? value("costcat_id") ?>)
<? endif ?> <? if exists("costcat_pattern") ?>
     AND (costcat_id IN (SELECT costcat_id
                           FROM costcat
                           WHERE costcat_code ~ <? value("costcat_pattern") ?>))
<? endif ?> <? if exists("itemgrp_id") ?>
     AND (item_id IN (SELECT itemgrpitem_item_id
                      FROM itemgrpitem
                      WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
<? endif ?> <? if exists("itemgrp_pattern") ?>
     AND (item_id IN (SELECT itemgrpitem_item_id
                      FROM itemgrpitem, itemgrp
                      WHERE ((itemgrpitem_itemgrp_id=itemgrp_id)
                         AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>))))
<? endif ?> <? if exists("showPositive") ?>
<? if exists("asOf") ?>
   AND (COALESCE(invbal_qoh_ending,0) > 0)
<? else ?>
   AND (itemsite_qtyonhand > 0)
<? endif ?>
<? elseif exists("showNegative") ?>
<? if exists("asOf") ?>
   AND (COALESCE(invbal_qoh_ending,0) < 0)
<? else ?>
   AND (itemsite_qtyonhand < 0)
<? endif ?>
<? endif ?> <? if exists("warehous_id") ?>
     AND (itemsite_warehous_id=<? value("warehous_id") ?>)
<? endif ?>

)
)

ORDER BY warehous_code, item_number, xtindentrole, classcode_code

<? endif ?>

Hello Caleb,

I tried the Grade 6 by copying Grade 0. It gave results when I queried with an “As of” date range. However it does not give Lot # and Expiration Date, which is what I need…

So it appears that I can only generate one or the other report, but not a combination of both QOH as of 30Jun2022 listing Lot # and Expiration Date for said inventory.

I see a couple problems off the start. But I’m not sure what the intention of the original code edit was.

But what I see is

<? if exists("asOf") ?>
SELECT forwardupdateinvbalance(invbal_id) FROM ...

When you are doing an inventory as of report it only selects returns one column. so there is not data to populate anything other then the id’s.

you could check my theory by changing the line to

<? if exists("asOf") ?>
SELECT forwardupdateinvbalance(invbal_id), item_number FROM ...

This would at least put the item numbers on the report. But unless you have staff that can take that and expand upon it I don’t see that you could get everything you are wanting.

The next problem is

– Quantity on Hand

You should probably just delete that line.

The last thing I noticed is the Copyright year of 2014. I think there have been system changes since that time that may be contributing to the report not displaying. But the main problem is not selecting the columns whos data you want. Someone with a little coding experience should be able to look at the original and the new one and find out what header names are needed for the data

Hi everyone,

I just came across this thread.

We made the modification to print the lot numbers and expiry date a few years ago.

This was never intended to work with the ‘as of’ date.

A couple of years later, there was a request to have the ‘as of’ date format to print with the lot/serial and expiry information.

An preliminary analysis was performed and it was deemed too costly to proceed with the project.

So, the way it is setup now only provides lot/serial and expiry information for the current status of the inventory.

If I remember correctly, the ‘as of’ date is only available with the buckets calculated when generating the ‘as of’ file, the same concept as the trial balance. So it covers a range of dates, not a single date.

To achieve the desired report, there would have to be a function that would retrieve all lot/serial numbers that existed at the end each of the bucket range.

If there is another way to do this, I would be curious to learn how to achieve that.

Regards,

Bernard Le Jour
AS Plus Informatique Inc.