How to obtain the available quantity of a product (ATP model) by querying the database *|* -ED2AA20BE126E3D785257A84004ED

To check the inventory of a product under the ATP model manually run the following SQL queries:

1. Enter:

SELECT * FROM baseitem WHERE baseitem_id=(SELECT baseitem_id FROM catentry WHERE catentry_id=<your_catentry_id>);

This query gives you the baseitem_id that you need to provide in the next step.

2. Enter:

SELECT itemspc_id FROM itemspc WHERE baseitem_id = <your_baseitem_id>;

This query gives you the itemspc_id that you need to provide in step 4.

3. Enter:

SELECT ffmcenter_id FROM store WHERE store_id = <your_store_id>

This query gives you the ffmcenter_id that you need to provide in step 4.

4. Enter:

SELECT itemspc.partnumber as "SKU"
baseitmdsc.shortdescription
ffmcentds.displayname
qtyunitdsc.description
sum(receipt.qtyonhand+receipt.qtyinkits) AS QUANTITY_ON_SITE
sum(receipt.qtyinprocess) AS QUANTITY_IN_PROCESS itemffmctr.qtyreserved
sum(receipt.qtyonhand - receipt.qtyinprocess) - (itemffmctr.qtyreserved + itemffmctr.qtyallocbackorder) AS QUANTITY_ORDERABLE
itemffmctr.qtyallocbackorder
itemffmctr.qtybackordered
max(cast(receipt.receiptdate as date))
FROM baseitem baseitmdsc itemspc receipt ffmcentds itemffmctr qtyunitdsc versionspc
WHERE receipt.versionspc_id=versionspc.versionspc_id
and versionspc.itemspc_id=itemspc.itemspc_id
and itemspc.baseitem_id=baseitem.baseitem_id
and baseitem.baseitem_id=baseitmdsc.baseitem_id
and itemspc.itemspc_id=itemffmctr.itemspc_id
and receipt.ffmcenter_id=itemffmctr.ffmcenter_id
and receipt.store_id=itemffmctr.store_id
and receipt.ffmcenter_id=ffmcentds.ffmcenter_id
and baseitem.quantitymeasure=qtyunitdsc.qtyunit_id
and qtyunitdsc.language_id=-1
and ffmcentds.language_id=-1
and baseitmdsc.language_id=-1
and receipt.store_id=<your store id>
and itemspc.itemspc_id in <itemspc_id from query 2>
and ffmcentds.ffmcenter_id in <ffmcenter_id from query 3>
GROUP BY ffmcentds.displayname baseitmdsc.shortdescription itemspc.partnumber qtyunitdsc.description
itemffmctr.qtyreserved itemffmctr.qtyallocbackorder itemffmctr.qtybackordered
ORDER BY "SKU";

This query presents the inventory information to the customer.