VbzCart/queries/qryRstks info
Jump to navigation
Jump to search
Details
- Returns: Information about restock requests and receipts, one line per receipt (or request, if nothing received)
- Requires:
- REDIRECT Template:l/vc/table, rstk_rcd
- 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
<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>