Form StockPerLocationForm

• Unit name: Forms.StockPerLocation
• Form name: TStockPerLocationForm
• Old form name: Form95 (unit95)

Synopsis

Form to display and manage stock on a location.

Functionalities

1. Form displays a dropdown with locations. (from global location lookup)
2. Form displays a dropdown with stockcodes (from global stockcode lookup)
3. When either of the dropdowns changes, the form is cleared (datasets closed).
4. A button 'Display' to activate Search action.
5. A grid shows the contents of the location (LocationStock)
6. A grid shows the contents of the location total quantities (LocationStockQuantities)
7. A button to execute the Correction action

Actions

Search

1. Location must be filled in to be active.
2. displays the contents of the location (action), using views LocationStock, LocationStockQuantities

Correction

1. Location and stock code must be filled in to be active, the grid must be displaying data and theere should be a current record.
2. On execute, uses the LocationStockQuantities and LocationCCStockQuantities views to calculate the stock limits. (The supplier parameter is taken from the current record, the stockcode and location from the dropdown)
3. The ArticleStockCorrection is shown for a minus correction.
4. When the form is closed with modal result mrOK, then the service call StockControl.StockCorrection is executed with negative Quantity field.
5. The display is refreshed completely.

Move location.

1. Only enabled if the locationStock is not empty.
2. When called, asks for a new location (combobox) and executes service call StockControl.FullLocationMove with current and new location.
3. Refreshes the display.

Tables/Views

• Location Global lookup.
• StockCode Global lookup.
• LocationStock custom view:

  SELECT
    A.STOCKCODE, A.Location, A.Lotno, A.ExpiryDate,  A.SUPPLIER, A.FDATE, A.ARTCODE,
    SUM(A.QTYIN-ISNULL(A.QTYOUT, 0)) AS QTY,
    '*'+A.ARTCODE+'*' AS ArtBareCore,
    (CASE WHEN ISNULL(A.LOTNO, '')<>'' THEN '*'+A.LOTNO+'*' ELSE '' END) as LotNo2,
    (CASE WHEN ISNULL(A.LOTNO, '')<>'' THEN '*'+A.ARTCODE+'-'+A.LOTNO+'*' ELSE '*'+A.ARTCODE+'*' END) as  ProductLot
    (CASE WHEN ISNULL(A.LOTNO, '')<>'' THEN '*'+B.EANCODE3+'-'+A.LOTNO+'*' ELSE '*'+B.EANCODE3+'*' END) as ProductLot1,
    B.NAME2
  FROM
    STOCTOTAL A
    LEFT JOIN ARTICLE B on (B.NAME=A.ARTCODE)
  WHERE
    (QTYIN-ISNULL(QTYOUT, 0)>0)
    AND (Location=:Location)
    AND (:StockCode in ('',StockCode))
  GROUP BY
    A.STOCKCODE, A.ARTCODE, A.Location, A.Lotno, A.ExpiryDate, A.SUPPLIER, A.FDATE, B.EANCODE3, B.NAME2
  HAVING
    SUM(QTYIN-ISNULL(QTYOUT, 0))>0
  ORDER BY
    artcode, ExpiryDate asc, FDate asc
  

• LocationStockQuantities custom view:

  SELECT
    A.ARTCODE, STOCKCODE, ORDID,
    SUM(QTYIN-ISNULL(QTYOUT, 0)) AS QTY,
   FROM
     STOCTOTAL
     LEFT JOIN STOCKCODE B ON (A.STOCKCODE=B.NAME)
  WHERE  
    QTYIN-ISNULL(QTYOUT, 0)>0
    AND (Location=:Location)
    AND (:StockCode in ('',StockCode))
  GROUP BY
    A.ARTCODE, STOCKCODE, ORDID
  HAVING
   SUM(QTYIN-ISNULL(QTYOUT, 0))>0
  ORDER BY
    A.ARTCODE, ORDID asc
  

• LocationCCStockQuantities custom view:

  With ArtCodes as(
    SELECT DISTINCT
      ARTCODE
    FROM
      STOCTOTAL
    WHERE
      (LOCATION = :Location)
      (STOCKCODE = :StockCode) AND
      (SUPPLIER = :SUPPLIER)
    GROUP BY
      ARTCODE
    having
     SUM(QTYIN - ISNULL(QTYOUT, 0)) > 0
  ),
  SELECT
    ISNULL(SUM(QTY), 0) AS QTYCC, A.ARTCODE
  FROM
    ARTCODES
    LEFT JOINSTOCTOTALCC A  on ( A.ARTCODE = ARTCODES.ARTCODE)
  WHERE
    A.STOCKCODE = '99'
    (A.SUPPLIER = :SUPPLIER)
  GROUP BY
    A.ARTCODE
  having
   SUM(A.QTY) > 0
  order by
   QTYCC asc
  

Needed Service Calls

StockControl.StockCorrection

Needed for (negative) stock corrections.

StockControl.FullLocationMove

Needed for full location stock move.

Reports

None.