جملة استعلام لعرض الأصناف التي وصلت إلى حد الطلب

Use a query like this:

select sum(net) qty, i.code, i.name1, i.dfOrderLimit
from ItemDimensionsQty q
left join InvItem i on i.id = q.item_id
group by i.code, i.name1, i.dfOrderLimit
having sum(net) <= i.dfOrderLimit and i.dfOrderLimit > 0

Notification Template:

Please Note that the following items reached order Limit
{table}
{row}{cell}Item Code{cell} Item Name{cell}Order Limit{cell}Current Qty{endrow}
{loop()}
{row}{cell}{code}{cell}{name1}{cell}{dfOrderLimit}{cell}{qty}{endrow}
{endloop}
{endtable}

You can also review this example: استيراد أمر شراء بالأصناف التي وصلت لحد الطلب.

Another example for a query that imports using details of the item:

select '' [:-detail:details],
       'PR' + CONVERT(NVARCHAR(10), GETDATE(), 112) #code,
       i.code [details.item.itemCode],
       i.id [details.item.item],
       d.dataOrderLimit - sum(coalesce(q.net, 0)) [details.quantity.quantity.primeQty.value],
       i.primBaseUnit_id [details.quantity.quantity.primeQty.uom]
from InvItem i
left join ItemDimensionsDetail d on d.invitem_id = i.id
left join ItemDimensionsQty q on q.item_id = i.id and q.warehouse_id = d.warehouse_id
where d.dataOrderLimit > 0
group by i.id, d.dataOrderLimit, primBaseUnit_id, i.code
having sum(coalesce(q.net, 0)) < d.dataOrderLimit

Originally posted at https://answers.namasoft.com/question/160/.