VbzCart/docs/queries/qryRstks info

Details

• Returns: Information about restock requests and receipts, one line per receipt (or request, if nothing received)
• Requires: Template:Vbzcart/table, Template:Vbzcart/table
• Used by: (MS Access) sfrm_SuppRstks
• History:
  • 2008-11-30 Created for new restock process
• Notes:
  • Creates one list by left-joining received restocks with their requests, another list of requests with no receipts, and then UNIONs them together

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstks_info AS SELECT

 rq.ID AS ID_Req,
 rq.ID_Supplier,
 rq.PurchOrdNum,
 rq.SuppOrdNum,
 rq.WhenCreated,
 rq.WhenOrdered,
 rq.WhenKilled,
 rq.WhenOrphaned,
 rq.WhenClosed,
 rq.Notes AS NotesReq,
 rc.ID AS ID_Rcd,
 rc.SuppInvcNum,
 rc.WhenReceived,
 rc.TotalInvMerch,
 rc.TotalInvFinal,
 rc.Notes AS NotesRcd

FROM

 rstk_rcd AS rc LEFT JOIN rstk_req AS rq ON rc.ID_Restock=rq.ID

UNION SELECT

 rq.ID AS ID_Req,
 rq.ID_Supplier,
 rq.PurchOrdNum,
 rq.SuppOrdNum,
 rq.WhenCreated,
 rq.WhenOrdered,
 rq.WhenKilled,
 rq.WhenOrphaned,
 rq.WhenClosed,
 rq.Notes AS NotesReq,
 rc.ID AS ID_Rcd,
 rc.SuppInvcNum,
 rc.WhenReceived,
 rc.TotalInvMerch,
 rc.TotalInvFinal,
 rc.Notes AS NotesRcd

FROM

 rstk_req AS rq LEFT JOIN rstk_rcd AS rc ON rc.ID_Restock=rq.ID
 WHERE rc.ID IS NULL;</mysql>

<section end=sql />