1

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

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

Eldabbas's avatar
9k
Eldabbas
asked 2018-07-03 07:36:02 +0200, updated 2018-07-03 07:57:17 +0200
edit flag offensive 0 remove flag close merge delete

Comments

add a comment see more comments

1 Answer

1

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 <br/>
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

Eldabbas's avatar
9k
Eldabbas
answered 2018-07-03 07:37:25 +0200
Ahmed Qasid's avatar
5.8k
Ahmed Qasid
updated 2018-08-07 10:06:49 +0200
edit flag offensive 0 remove flag delete link

Comments

add a comment see more comments

Your Answer

Login/Signup to Answer