VbzCart/queries/qryRstkReq Item Rcd status

Details

• Returns: Information about received shipments for a given Item in a given Restock Request
• Requires:

1. REDIRECT Template:l/vc/table, rstk_rcd, rstk_req

• Used by:

1. REDIRECT Template:l/vc/query

• History:
  • 2008-11-21 Created for new restock process
  • 2008-11-23 WhenOrdered -> WhenFirstOrder (MIN), WhenFinalOrder (MAX)
• Fields:
  • WhenFirstOrder is the timestamp of the earliest open customer order for this item. This is so we know the longest time that anyone has been waiting for this item, and hence how urgent the restock is.
  • WhenFinalOrder is the timestamp of the latest open customer order for this item. This is so we know if there have been further requests for this item, adding further urgency.

SQL

<mysql>CREATE OR REPLACE VIEW qryRstkReq_Item_Rcd_status AS SELECT

 rq.ID AS ID_RstkReq,
 MIN(rq.WhenOrdered) AS WhenFirstOrder,
 MAX(rq.WhenOrdered) AS WhenFinalOrder,
 rcl.ID_Item,
 SUM(IFNULL(rcl.QtyFiled,rcl.QtyRecd)) AS QtyRecd

FROM

     (rstk_rcd_line AS rcl
   LEFT JOIN rstk_rcd AS rc ON rcl.ID_RstkRcd=rc.ID)
 LEFT JOIN rstk_req AS rq ON rc.ID_Restock=rq.ID

GROUP BY rq.ID, rcl.ID_Item;</mysql>