Stock

Service StockControl

Synopsis

This service contains calls that control the stock.

Service Data structures

Enumeration TTransactionType
ttTransfer : Transfer of stock (asString: TRANSFER)
ttCorrection : Correction of stock (asString: COR)
ttKit : kit. (asString: KIT)
ttORD : Order. (asString: ORD)
ttSpecialJob ? (asString: SP-JOB)
ttRetDel : Pallete pick ? (asString: RET-DEL)
ttClear : Customs cleared stock ? (asString: CLEAR)
Enumeration TScanOperationType
soCorrection (asString: COR)
soCorrectionMove (asString: COR-M)
soCorrectionPick (asString: COR-P)
**soDelete ** (asString: DELETE ROW)
**soMove ** (asString: MOVE)
soMoveFill (asString: MOVE-FL)
soMoveFillReturn (asString: MOVE-FL-RET)
soMoveFillAlternate (asString: MOVE-FLA)
soMovePickReturn (asString: MOVE-P-RET)
soPick (asString: PICK)
soReception (asString: RECEPTION)
soReturn (asString: RETURN)
soReturnDamaged (asString: RETURN-D)
soReturnGood (asString: RETURN-G)
soTransfer (asString: TRANSFER)
soKit (asString: KIT)
soOrder (asAstring: ORD)
soSpecialJob (asString SP-JOB)
soReturnDelivery (asString: RET-DEL)

Mapping with TTransactionType:

TTransactionType	TScanOperationType
ttTransfer	soTransfer
ttCorrection	soCorrection
ttKit	soKit
ttORD	soOrder
ttSpecialJob	soSpecialJob
ttRetDel	soReturnDelivery

Record TStockDescriptor
ArticleID (Int64) Article identifier.
LocationID (Int64) Location identifier.
StockCode (string) stock code identifier.
LotNo (string) Optional lot identifier.
Record TStockMoveDescriptor
OldStock (TStockDescriptor) Where stock comes from.
NewStock (TStockDescriptor) Where stock goes to.
Operation (TTransactionType) Type of TTransaction.
Comments (String) Comment to be logged in scan history.
ExpiryDate (TDateTime) Date of expiry.
Quantity (integer) number of articles to move.
AdaptOutCC (Boolean) change INVDETAILSCC (default false)
AdaptINVCC (Boolean) change OUTDETAILSCC (default false)
CustomerID (integer) customer ID. (default false)
InvoiceNo: Generated number, see code in TForm.PartialMove
Record TScanOperation
Operation (TScanOperationType) what operation is performed.
OldStock (TStockDescriptor) the old stock description.
NewStock (TStockDescriptor) the new stock description.
InvoiceNo (String) what order no. is being scanned.
Quantity (Integer) number of articles.
Comment (string) comment text.
ArticleID (Integer) numerical ID of the article.
Record TCorrectionDescription
CustomerID (Int64) ID of customer.
CorrectionNr (String) Unique code for correction.
ArticleID (Int64) Article Integer.
LocationID (Int64) location ID.
StockCode (String) stock code.
Quantity (Integer)
FillDate (TDatetime) fill date.
LotNo (String) Lot selection.
ExpiryDate (TDateTime) expiry date selection.
Comments (String) Comments.
Record TInvoiceHeader
CustomerID (Int64) Customer ID.
InvoiceNo (String) Invoice number.
Date (TDateTime) invoice date.
Rectype Record type.
Record TArticleHeader
ArticleID (Int64) Unique article code.
Quantity (Integer) Quantity for the article.
LocationID (Int64) Location code for the article.
StockCode (String) Stock type code for the article.
CustomsCode (Integer) Customs code for the article.
LotNo (String) Lot number for the article.
ExpiryDate (TDateTime) Stock type code for the article
Comments (String) Comments.
Barcode (String) Optional barcode, used to create the article if it is new.
Code (String) Optional code, used to create the article if it is new.
CustomerID (Int64) Customer ID.
Weight (Double) weight.
Volume (double) Volume.
Description (String) Article Description
RowID1 (Integer) Row no for import.
Array TArticleHeaderArray array of TArticleHeader records.
Record TStockCCOperationDescriptor
OrderID (Int64) OUT record ID for which to execute Customs Clearance.
ArticleID (Int64) article code.
Quantity (Integer) quantity.
CustomerID (Int64) Customer
StockCode (String) Stock code.
Record TClearStockDescriptor
ArticleID (Int64) Article ID.
Quantity (Integer) Quantity ID
CustomerID (int64) Customer ID
Record TStockCorrectionDescriptor
ArticleID (Int64) Article ID
Quantity (Integer) Correction quantity
StockCode (String) New stock code
CustomerID (int64) Customer ID
Comment (string) Comment to be entered
LocationID (Int64) New location.
LotNo (string) New Lot number.
ExpiryDate (TDateTime) Expiry date (can be empty)
ReceptionDate (TDateTime) Reception date.
Record TCCStockCorrectionDescriptor
ArticleID (Int64) Article ID
Quantity (Integer) Quantity for correction.
StockCode (String) Stock code
CustomerID (int64) Customer ID
LocationID (Int64) Location ID.
Record TLotNoChangeDescriptor
ArticleID (Int64) Article ID
LocationID (Int64) Location ID
StockCode (StockCode) Stock code
OldLotNo (String) Lot number
NewLotNo (String) Lot number
ExpiryDate (TDateTime) Expiry date (can be empty)
ReceptionDate (TDateTime) Reception date
Record TArticleReceptionData
InvoiceID (Int64) Invoice ID
ArticleID (Int64) Article ID
ArticleDescription (string) Description of article.
Quantity (Integer) Quantity
StockCode (String) Stock code
HasExpiryDate (Boolean) Is expiry date needed ?
HasLotNo (Boolean) Is a lot number required ?
DisplayMessage (String) Display message
DetailCount (integer) Detail count.
Record TReceptionDescriptor
LocationID (int64) Location ID
InvoiceID (int64) Invoice ID
ArticleID (int64) Article ID
StockCode (string) Stock code
Quantity (Integer) Quantity
LotNo (String) Lot number (can be empty)
ExpiryDate (DateTime) Expiry date (can be empty)
Record TInventoryScanDescriptor
InventoryID (Int64) Inventory ID
LocationID (Int64) Location ID
ArticleID (Int64) Article ID
Quantity (Integer) Quantity
StockCode (String)

Service Calls

FullLocationMove

Move all stock on a location to another location.

Declaration:

Procedure FullLocationMove(OldLocation,NewLocation : Int64);

1. The string version looks up the locations and calls the integer version. 1. Checks that both locations exist. 1. Executes the following query (check whether this can be done with a command)

insert into ScanHistory (
  OpType, OrderNo, ArtCode, QTY,
  Location, StockCode, UserName,
  CreationDate, CreationTime,
  Comments, OriginalLocation,
  OriginalStockCode, OriginalArtcode, IDNO
)
SELECT
  'MOVE-FLA', :OrderNo, ARTCODE, SUM(QTYIN - ISNULL(QTYOUT, 0)),
  :NewLocation, STOCKCODE, :CurrentUser,
   GetDate(), right (CONVERT (CHAR (19), getdate(), 120), 8),
  :Comments,  Location,
  StockCode,  Artcode,  ID
from
  STOCTOTAL X
WHERE
  (X.LOCATION = :OldLocation)
GROUP BY
  ARTCODE,
  STOCKCODE,
  LOCATION,
  ID
HAVING
  SUM(X.QTYIN - ISNULL(X.QTYOUT, 0)) > 0);

Where OrderNo is:

'M'+FORMATDATETIME(YYMMDD-HHNNSS, NOW)

and COMMENTS is:

'Move ' + :OldLOCATION + '-> ' + :NewNewLocation,

1. The following query is executed:

UPDATE INVDETAIL  SET
  LOCATION=:NewLocation
WHERE
  ID IN
  (SELECT DISTINCT ID
   FROM STOCTOTAL X
   WHERE X.LOCATION=:OldLocation
   GROUP BY X.ID
   HAVING SUM(X.QTYIN-ISNULL(X.QTYOUT, 0))>0
  );

PartialMove

Move stock in the database.

Declaration:

Function PartialMove(Descr : TStockMoveDescriptor) : integer;

Operation: 1. Checks whether stock descriptors are valid (all fields must be nonempty) 2. Checks if the new article,location,stock exist in the DB. 3. Moves the old articles to new articles. For each article, a ScanHistory record is made with following parameters: * Operation: Descr.operation * OldStock: Descr.OldStock * NewStock: Descr.NewStock * Quantity: Aantal artikels (count). * InvoiceNo: Descr.InvoiceNo * Comment: Descr.comments * ArticleID : INVDETAIL.ID 4. Reports the number of moved articles. 5. If AdaptOUTCC is true, the following query is executed

INSERT INTO OUTDETAILSCC
  (AVIZNO, ARTCODE, QTY, STOCKCODE, OriginalSTOCKCODE, Supplier,
    PCNAME, FILLDATE, COMMENTS, TransactionType)
VALUES
  (:AVIZNO, :ARTCODE, :QTY, 99, 99, :Supplier,
  :PCNAME, getDate(), :COMMENTS, :TransactionType)

With * AVIZNO: Descr.InvoiceNo * Artcode: Descr.Article * Qty: Descr.Quantity * Supplier: Descr.Supplier * PCNAME: Currently logged in user. * COMMENTS Descr.Comment * TransactionType Descr.Operation (sample code: unit43, button2click) 6. If AdaptINVCC is true, the following query is executed

INSERT INTO INVDETAILSCC
  (INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER, PCNAME,
  FILLDATE, TransactionType, COMMENTS)
VALUES
  (:INVOICE, :ARTCODE, :QTY, 99, :Supplier, :PCNAME,
   getDate(),  :TransactionType, :COMMENTS)

Where * INVOICE: Descr.InvoiceNo * Artcode: Descr.Article * Qty: Descr.Quantity * Supplier: Descr.Supplier * PCNAME: Currently logged in user. * COMMENTS Descr.Comment * TransactionType Descr.Operation (sample code: unit43, button2click) 7. Check if previous 2 points can be done using OperateStockCC call ?

On error, an exception is raised. Sample code can be found in MAIN unit, TForm.PartialMove.

AddScanHistory

Add a scan event (or its equivalent) in the database. The fields in anOperation map 1-1 on the fields in the table.

Declaration:

Function AddScanHistory(anOperation: TScanOperation) : int64;

MinusCorrection

Perform a correction on the stock. This operation is similar to PartialMove.

Declaration:

Function MinusCorrection(anOperation: TCorrectionDescription) : integer;

Sample code can be found in mainform.MinusCorection. The function returns the number of records moved.

CreateInvoice

Create a new invoice in the system.

Declaration:

Function CreateInvoice(aHeader : TInvoiceHeader) : Int64;

The function creates a new record in the database, and returns the invoice ID. The function checks the arguments: all elements must be nonempty.

The invoice is created with the following values: * INVOICE aHeader.Invoiceno * DATA aHeader.Date * SUPPL aHeader.Supplier * PCNAME Current user name. * FILLDATE Current date. * Rectype aHeader.Rectype

AddArticlesToInvoice

Add one or more articles to the invoice.

Declaration:

Function AddArticlesToInvoice(aInvoice : Int64; Articles : TArticleHeaderArray; AllowCreate : boolean) : TInt64Array;

The function checks all the articles in the array, and if all articles are correct, they are added to the invoice.

The function returns an array of ID's of the rows that were added to the invoice.

The following checks are done: 1. Quantity>0 2. Existing article code. (see below, however) 3. Existing location code. 4. Existing stock code. 5. If article requires a lot no: nonempty lot no. 6. If article requires a expiry date: expiry date must be nonzero. 7. If StockCode equals 75, CustomsCode must be one of 25 or 99.

The INVDETAIL fields are filled as follows from an article (Art).: * INVOICE aInvoice * ARTCODE Art.code. * QTYIN Art.Quantity. * QTY Art.Quantity. * LOCATION Art.Location. * COMENTS Art.Comments. * SUPPLIER Art.Supplier. * STOCKCODE Art.StockCode. * PCNAME Current user. * EXPIRYDATE Art.ExpiryDate. * LOTNO Art.LotNo. * COLNO1 Art.RowID1.

If AllowCreate is True, then articles not found in the database can be created:

If the article does not yet exist in the ARTICLE table, then a record is made:
aCode Art.code.
aCreatedByFK, aChangedByFK User name of active user.
aCreatedOn, aChangedOn Now().
aCustomerFK equals Art.Supplier.
aBarcode1 Art.barcode.
aBarcode4 Art.code.
aVolume Art.Volume
aWeight Art.Weight
aDescription Art.Description.
If the CustomsCode is nonzero, then a INVDETAILSCC record is made after the INVDETAIL record was inserted. The fields are filled as follows:
INVOICE aInvoice
ARTCODE Art.code.
QTY Art.Quantity.
STOCKCODE Art.StockCode.
SUPPLIER Art.Supplier.
PCNAME Current user.
FILLDATE Now().
TRANSACTIONTYPE 'REC'.
ORIGINALID ID van het INVDETAIL record.

DeleteInvoice

Remove an invoice from the system.

Declaration:

Procedure DeleteInvoice(aID : Int64);

The overloaded version looks up the invoice using the provided strings, and calls the ID version.

The function checks that there are no outgoing (deliveries) articles corresponding to the invoice. If there are, an error message is returned . ( sample code unit2, TForm2.SpeedButtonClick)

The auditing service UserHistory is used to log this action with parameters: * aInvoice, * 'INVOICE' * 'INVOICE COMPLETELY DELETED' * 'INVOICE MENU'

After this, the invoice matching (aInvoice, asupplier) and its corresponding INVDETAIL , INVDETAILSCC records are deleted.

DeleteInvoiceArticles

Delete some articles from the invoice:

Declaration:

Procedure DeleteInvoiceArticles(aInvoiceID, aSupplierID : Int64; Articles: TInt64Array);

Before deleting the articles from the database, the following checks are performed:

There are no records in the table INVOP corresponding to this invoice.
For each article in the list, the following checks are performed:
Article belongs to selected invoice/supplier.
There are records in the table INVDETAILSCC corresponding to the article.
There is no corresponding record in OUTDETAILS for the article. (error message shows the list of out records.)

a failed check results in an exception.

If all checks are completed succesfully, the articles are deleted from tables INVDETAIL and INVDETAILSCC. Each delete action is logged with the auditing service UserHistory with the following parameters: * Article.Code * 'ARTICLE' * 'ARTICLE DELETED FROM INVOICE '+AddArticleToInvoice * 'INVOICE MENU'

ConfirmInvoiceQuantities

Confirm the quantities in the invoice:

Declaration:

 Procedure ConfirmInvoiceQuantities(aInvoiceID, aSupplierID : Int64);

The call performs the following actions for all articles in the invoice: 1. If the location is empty, it is filled with a previously used location for this article from the same supplier. This is the first location for the same article in another invoice of the supplier. The locations are sorted alphabetically. 2. The QTY field is updated with the QTYIN field.

FinishNorwexBOMImport

Call to finish the import of the Norwex BOM data.

Declaration:

Procedure FinishNorwexBOMImport

Executes the following query:

INSERT INTO ARTICLE
  (NAME, NAME2, EANCODE3, PCNAME, FILLDATE, FSupplier)
SELECT DISTINCT
  (CASE
    WHEN isnull(LINKEDARTCODE2, '')=''
    THEN 'NW'+ARTCODE+'.'+LINKEDARTCODE1
    ELSE 'NW'+ARTCODE+'.'+LINKEDARTCODE1+'.'+LINKEDARTCODE2 END),
  ART_DESCRIPTION,
  (CASE
     WHEN isnull(LINKEDARTCODE2, '')=''
     THEN 'NW'+ARTCODE+'.'+LINKEDARTCODE1
     ELSE 'NW'+ARTCODE+'.'+LINKEDARTCODE1+'.'+LINKEDARTCODE2 END),  
  :UserName,
  GetDate(),
  'NORWEX'
FROM
  NRW_ARTICLE_GLOBAL
WHERE
  (CASE
    WHEN isnull(LINKEDARTCODE2, '')=''
      THEN 'NW'+ARTCODE+'.'+LINKEDARTCODE1
      ELSE 'NW'+ARTCODE+'.'+LINKEDARTCODE1+'.'+LINKEDARTCODE2 END
      ) NOT IN (SELECT NAME FROM ARTICLE)

* UserName is the currently logged in user.

PrepareStockCompare

Call to prepare a stock compare by importing a CSV file.

Declaration:

Function PrepareStockCompare(const aCustomer: Int64; aFile : Binary; QtyPos : Integer)  : Integer;

The command imports a CSV file into the STOCKCOMPARE table.

Note: The table must be altered: a new field must be added:

CompareID INT NOT NULL Default 0

The import starts by allocating a compare ID number (sequence) for the compare job. This is the function result.
First field: article (Pad with 0 to 10 digits)
QtyPos (1-based) field: quantity.
For each record in the CSV file with a valid quantity (it must be a number), a record is inserted in the STOCKCOMPARE table: The fields of StockCompare are filled as follows:

Field	Source
ARTCODE	Article
QTY	Quantity
CompareID	Compare job ID

CleanStockCompare

Call to clear a stock compare.

Declaration:

Procedure CleanStockCompare(const aCompareID : int64);

The command removes all records from the STOCKCOMPARE table.

SuggestReturnLocation

Call to suggest locations for items in a returned order.

Declaration:

Procedure SuggestReturnLocation(Const invoice : Int64;

The call executes the following queries in succession: 1. Clear INVDETAIL suggested location.

UPDATE INVDETAIL SET
  INVDETAIL.SUGESTEDLOCATIONRET=''
WHERE
  (INVOICE=:InvoiceID)

2. Clear suggested stock locations

 DELETE FROM SUGESTED_STOCK_LIST

3. Fill suggested stock locations with new data

INSERT INTO SUGESTED_STOCK_LIST (
    INVOICE, ARTCODE, QTY, LOCATION, COLOR, AMBALAJ, STOCKCODE,
   FILLDATE, ID, MyPRIORITY, EXPIRYDATE, LotPriority,
   HighPrSQ,
   LocationLevelPriority,
   ExpirySQ)
SELECT
   A.INVOICE, A.ARTCODE, A.QTY-isnull(SUM(B.QTY), 0) AS QTY, A.LOCATION, A.COLOR, A.AMBALAJ, A.STOCKCODE,
   A.FILLDATE, A.ID, isnull(C.SQ, '99') as MyPRIORITY, A.EXPIRYDATE, Right(' '+isnull(A.LotNo, ''), 1) as LotPriority,  
   (CASE WHEN isnull(C.HighPriorityForPick, 'N')='Y' THEN 0 ELSE 1 END) AS HighPrSQ,
   (CASE WHEN RIGHT(A.LOCATION, 1)='A' THEN 0 ELSE 1 END) AS LocationLevelPriority,
   99 AS ExpirySQ
FROM
  INVDETAIL A
  LEFT JOIN OUTDETAILS B ON (A.ID=B.INVOICENO)
  INNER JOIN LOCATION C ON (A.LOCATION=C.LOCATION)
WHERE
  (A.ARTCODE IN (SELECT DISTINCT X.ARTCODE
                     FROM INVDETAIL X
                     WHERE (X.INVOICE=:InvoiceID)))
GROUP BY
  A.INVOICE, A.ARTCODE, A.LOCATION , A.COLOR, A.QTY, A.AMBALAJ, A.STOCKCODE,
  A.FILLDATE, A.ID, C.SQ, A.EXPIRYDATE, Right(' '+isnull(A.LotNo, ''), 1),
  isnull(C.HighPriorityForPick, 'N')
HAVING
  (A.QTY-isnull(SUM(B.QTY), 0) > 0)
ORDER BY
  ExpirySQ DESC, HighPrSQ ASC, A.EXPIRYDATE ASC, LotPriority asc,
  LocationLevelPriority asc, A.FILLDATE ASC, MyPRIORITY ASC,
  A.LOCATION asc

4. Fill invoice with suggested stock locations :

UPDATE INVDETAIL SET
  INVDETAIL.SUGESTEDLOCATIONRET=
  (SELECT TOP 1
      LOCATION
   FROM
     SUGESTED_STOCK_LIST X
   WHERE
     (X.ARTCODE=INVDETAIL.ARTCODE)
     AND (X.STOCKCODE=INVDETAIL.STOCKCODE)
     AND (RIGHT(X.LOCATION, 1) = 'A')
     AND (LEFT(X.LOCATION, 1) IN ('M', 'H'))
     AND (X.LOCATION<>'H.30.01.A')
   ORDER BY
      HighPrSQ ASC, X.EXPIRYDATE ASC, LotPriority asc,
      LocationLevelPriority asc, FDATE ASC, MyPRIORITY ASC, X.LOCATION asc
  )
WHERE
  (INVDETAIL.INVOICE=:InvoiceID)

5. Fill remaining items with default location:

UPDATE INVDETAIL SET
  INVDETAIL.SUGESTEDLOCATIONRET='H.30.01.A'
WHERE
  (INVDETAIL.INVOICE=:InvoiceID)
  AND isnull(INVDETAIL.SUGESTEDLOCATIONRET, '')=''

CheckPalletInReturn

Check if a pallet exists in a return order.

Declaration:

Function CheckPalletInReturn (Const aPallet : Int64): Int64;

The function checks whether the pallet already exists in a RETUROUT order:

Select TOP 1
  IDNO from RETUROUTDET
WHERE
  (PALNO=:Pallet)

If the query result is nonempty, the ID is returned, else -1 is returned.

OperateStockCC

Process customs clearance for an outgoing order. (Sample code Unit3: form3.OperateStockCC)

Declaration:

procedure OperateStockCC (aOperation : TStockCCOperationDescriptor);

1. If supplier is empty, it is set to 'GUTHY RENKER' 2. DifStockCode is 25 if StockCode equals 25, otherwise it is 99. 3. The maximum stock is calculated:

SELECT
  isnull(sum(QTY), 0) as MaxOnStock
FROM
   FSTOCTOTALCC(:ArtCode)
WHERE
  (STOCKCODE=:StockCode)

The ArtCode and StockCode parameters are filled from the corresponding fields in the aOperation record. 4. The following insert is performed on OUTDETAILSCC:

INSERT INTO OUTDETAILSCC
(AVIZNO, ARTCODE, QTY, STOCKCODE, OriginalSTOCKCODE, PCNAME, FILLDATE, SUPPLIER, TransactionType)
VALUES
(:AVIZNO, :ARTCODE, :QTY, :STOCKCODE, :OriginalSTOCKCODE, :PCNAME, GetDate(), :SUPPLIER, 'ORD')

With parameters filled as follows:

Param	Value
AVIZNO	aOperation.AVIZ
ARTCODE	aOperation.Article
Qty	Min(aOperation.Quantity,MaxOnStock)
StockCode	aOperation.StockCode
OriginalStockCode	aOperation.StockCode
PCName	Aangemelde gebruiker
Supplier	aOperation.Supplier

if aOperation.Quantity is larger than MaxOnStock then The query is done a second time, with Quantity : aOperation.Quantity-MaxOnStock and stock code DifStockCode

EasyPartialMove

Simplified version of the PartialMove call.

Declaration:

Function EasyPartialMove(Descr :TStockMoveDescriptor) : integer;

Sample implementation, Main unit, form1.EasyPartialMove

AddInventoryScan

Add a scan result to an inventory Declaration:

Function AddInventoryScan( ADescr : TInventoryScanDescriptor) : Int64;

1. Check arguments (Inventory, Location, Article, StockCode) are correct. Raise exception if one of them does not exist. 1. Insert a record in the inventory details:

insert into  INVENTORYDET
    (ID,IDNO, ARTCODE, LOCATION, STOCKCODE,
     QTY, CRDATE, CRTIME, MYOPERATOR, TransactionType)
VALUES
   (:Result,:InventoryID, :ArticldeID, :LOCATIONID, STOCKCODE,
  :QTY, GetDate(), :CurrentUser, 'N')

1. Return the ID of the new record.

DeleteInventoryScan

Delete a scan result to an inventory Declaration:

Function DeleteInventoryScan( ADescr : TInventoryScanDescriptor) : Integer;

1. Check arguments (Inventory, Location, Article, StockCode) are correct. Raise exception if one of them does not exist. 1. Delete a record in the inventory details:

DELETE FROM
  INVENTORYDET
Where
  (IDNO=:InventoryID)
  AND (ArticleID=:ArticleID)
  AND (LocationID=:LocationID)
  AND (StockCode=:StockCode)

Returns the number of deleted records.

CreateInventoryScannerList

Creates a difference list for the scanners, based on the current inventory. (sample code, Unit69, BitBtn8click)

Declaration:

Procedure CreateInventoryScannerList(aInventoryID : Int64);

1. The data of the INVENTORY record with ID aInventoryID is fetched. 2. A filter on location is constructed: CustomFilter is a filter on A.location, depending on the inventory type: For type L, the filter is on the result of:

Select distinct LOCATION from LOCATION WHERE (INVENTORYID=:ID)

if it is non-empty. If it is empty, CustomFilter is empty.

For type F, the filter is on the result of:

Select distinct LOCATION from INVENTORYDET WHERE (INVENTORYID=:ID)

if it is non-empty. If it is empty, CustomFilter is empty.

for type N, the filter is on :

AND (RIGHT(LEFT(A.LOCATION, 7), 2)>=IPlace1)
AND (RIGHT(LEFT(A.LOCATION, 7), 2)<=IPlace2)
AND (RIGHT(LEFT(A.LOCATION, 4), 2)>=IROW1)
AND (RIGHT(LEFT(A.LOCATION, 4), 2)<=IROW2)
AND (RIGHT(A.LOCATION, 1)>=ILEVEL1)
AND (RIGHT(A.LOCATION, 1)<=ILEVEL2)

Where IPLACE1, IPLACE2, IROW1, IROW2, ILEVEL1 and ILEVEL2 are the fields as they appear in the inventory table. (maybe check if it is easier to add Inventory table to SQL)

NOTE: The above is based on the old database structure where A.LOCATION is a varchar(). In the new database structure LOCATION is a numerical bigint (foreign key), so we need to join in the Location table so we can get access to the Place, Row and Level fields.

The following is executed
```
DELETE FROM InventoryList
```
The following is executed:

WITH TempData AS (
SELECT
  artcode, name2, groupe, A.LOCATION,
  sum(qtyin-isnull(qtyout, 0)) as qty
FROM
  STOCTOTAL A
  INNER JOIN ARTICLE B ON (a.artcode=b.name)
WHERE
--  {CustomFilter}
GROUP BY
  artcode, name2, groupe, A.LOCATION
HAVING
  sum(qtyin-isnull(qtyout, 0))>0
ORDER BY
  artcode
)
INSERT INTO INVENTORYLIST (ARTCODE, NAME2, LOCATION)
SELECT
  a.Artcode, '' as Description, A.LOCATION
from
  TempData A
  LEFT OUTER JOIN INVENTORYDET B ON
    (a.artcode=b.artcode)
    AND A.LOCATION=B.LOCATION
WHERE
  (isnull(a.artcode, '')<>'')
  AND (B.IDNO=:ID)
-- {CustomFilter}
GROUP BY
  a.artcode, a.name2, a.groupe , a.qty, A.LOCATION
HAVING
  A.QTY<>ISNULL(sum(b.qty), 0)
UNION
SELECT
  a.Artcode,  'Only in warehouse', A.LOCATION
FROM
  INVENTORYDET A
  INNER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
WHERE
  (A.IDNO=:ID)
  AND (A.artcode not in (select artcode+'-'+location from tmpData))
-- {CustomFilter}
GROUP BY
  a.artcode,  name2, groupe, A.LOCATION
ORDER BY
  A.LOCATION, a.artcode

GetMaxCCStock

Call to get maximum movable quantity for customs cleared article.

Declaration:

Function GetMaxCCStock(ArticleID : Int64; AStockCode : String) : Integer;

Returns the result of:

SELECT
  SUM(QTY)
FROM
  STOCTOTALCC
WHERE
  (ARTCODE = :ArticleID)
  AND (STOCKCODE=:Code)

If code is empty, 99 is used.

ClearStock

Marks custom stock as cleared.

Declaration:

Procedure ClearStock(desc : TClearStockDescriptor);

Gets dutyfree stock (DFStock) using GetMaxCCStock and code 25. If there is not enough stock (DFStock less than Desc.Quantity), an exception is raised.
```
Quanity :AQuantity is too big: Duty free stock is only :DFStock pieces.
```

An INVNO is created:

INVNO:='CLEAR-'+FORMATDATETIME('YYMMDD-HHNNSS', NOW);

The following query is executed:

INSERT INTO OUTDETAILSCC
  (AVIZNO, ARTCODE, QTY, STOCKCODE, OriginalSTOCKCODE, Supplier,
   PCNAME, FILLDATE, COMMENTS, TransactionType)
VALUES
  (:AVIZNO, :ARTCODE, :QTY, '25', '25', :Supplier,
   :PCNAME, GetDate(), '25 -> 29', 'CLEAR')

Where

AVIZNO the generated INVNO
Artcode Desc.ArticleID
Qty Desc.Quantity
Supplier SupplierID
PCNAme Currently logged in user.

The following query is executed:

INSERT INTO INVDETAILSCC
  (INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER, PCNAME, FILLDATE, TransactionType, COMMENTS)
VALUES
  (:INVOICE, :ARTCODE, :QTY, '99', :SUPPLIER, :PCNAME, GetDate(), 'CLEAR', '25 -> 29')

Where

INVOICE the generated INVNO
Artcode Desc.ArticleID
Qty Desc.Quantity
Supplier SupplierID
PCNAme Currently logged in user.

StockCorrection

Do a custom correction on the stock

Declaration:

Procedure StockCorrection(desc : TStockCorrectionDescriptor);

The action of this method depends on the sign of the quantity field.

It starts by checking that all fields are filled. 1. quantity, location, stockcode, comment and supplier are not empty. 1. For plus corrections, stock codes must differ from 25 and 99. 1. For plus corrections, reception date must be filled.

Positive quantity:

(sample code in unit25, corplus) 1. Create an invno:

INVNO:='COR-'+FORMATDATETIME('YYMMDD-HHNNSS', NOW);

1. Use this invno to create an INV record:

INSERT INTO INV (INVOICE, DATA, SUPPL, PCNAME, FILLDATE)
VALUES (:INVOICE, GetDate(), :SUPPL, :PCNAME, getDate())

Where * INVOICE INVNO * SUPPL Desc.Supplier. * PCNAme Currently logged in user. 1. Create an INVDETAIL record

INSERT INTO INVDETAIL
  (INVOICE, ARTCODE, QTYIN, QTY, LOCATION, COMENTS, SUPPLIER, STOCKCODE,
    PCNAME, FILLDATE, TransactionType, LotNo, ExpiryDate)
values
  (:INVOICE, :ARTCODE, :QTYIN, :QTY, :LOCATION, :COMENTS, :SUPPLIER, :STOCKCODE,
   :PCNAME, GetDate(), 'COR', :LotNo, :ExpiryDate)

Where: * INVOICE INVNO * ARTCODE Desc.ArticleID. * Qty Desc.Quantity. * Location Desc.Location. * COMENTS Desc.Comment. * SUPPLIER Desc.Supplier. * Stockcode Desc.StockCode. * PCNAme Currently logged in user. * LotNo Desc.LotNo. * ExpiryDate Desc.ExpiryDate. Note: Must be NULL if Desc.ExpiryDate=0. 1. If StockCode is 75, then create an INVDETAILSCC record:

INSERT INTO INVDETAILSCC
  (INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER, PCNAME,
  FILLDATE, TransactionType, COMMENTS)
VALUES
  (:INVOICE, :ARTCODE, :QTY, :STOCKCODE, :SUPPLIER, :PCNAME,
  getdate(), 'COR', :COMMENTS)

Where: * INVOICE INVNO * ARTCODE Desc.ArticleID. * Qty Desc.Quantity. * Stockcode Desc.StockCode. * SUPPLIER Desc.Supplier. * PCNAme Currently logged in user. * COMMENTS Desc.Comment. 1. call SaveScanhistory.

Negative quantity:

(sample code in unit25, corminus)

Minus correction means that articles are taken from stock, from whatever possible "incoming article" documents (invDetail).

So, a list of possible 'incoming article' documents from which to take stock is constructed. Then, a loop is done, subtracting from every possible incoming article document the possible stock to be taken: - The requested stock, - Or, if not so much stock is available, the available stock in the incoming article document. The loop ends when the requested amount of stock correction is reached. (Remaining = 0).

This processing is very similar to MinusCorrection()'s implementation details.

if stockcode is 75, check that abs(desc.Quantity) is less than the result of
```
GetMaxCCStock(desc.ArticleID, 99)
```
if not, an exception is raised.

Create an invno:

INVNO:='COR-'+FORMATDATETIME('YYMMDD-HHNNSS', NOW);

The following query is executed:

INSERT INTO OUT (AVIZ, DATA, CLIENT, BLOCKNO, FILENAME, PCNAME, FILLDATE, idclient)
VALUES (:AVIZ, getDate(), :CLIENT, 0, 'Correction', :PCNAME, getDate(), 7)

Where:

AVIZ InvoceNo.
CLIENT Desc.Supplier.
PCName The currently logged in user.

A loop is executed over the result of the following query

SELECT
  A.ID, A.QTY-isnull(SUM(B.QTY), 0) AS QTY,
FROM
  INVDETAIL A
  LEFT JOIN OUTDETAILS B ON (A.ID=B.INVOICENO)
WHERE
  (A.ARTCODE = :Artcode)
  AND (A.STOCKCODE = :Stockcode)
  AND (A.LOCATION = :Location)
  AND (A.FILLDATE=:FILLDATE)
--  {CustomFilter}
GROUP BY
  A.ID, A.FILLDATE, A.SUPPLIER, A.QTY, A.EXPIRYDATE
HAVING
  A.QTY-isnull(SUM(B.QTY), 0) > 0
ORDER BY
  A.EXPIRYDATE ASC, A.FILLDATE ASC

Where

Macro CustomFilter is composed from:
1. If LotNo<>'' then
```
  AND (LotNo=:LotNo)
```
2. If ExpiryDate<>'' then
```
AND (ExpiryDate=:ExpiryDate)
```
Artcode Desc.ArticleID.
StockCode Desc.StockCode.
Location Desc.Location.
FillDate Desc.ReceptionDate.
LotNo Desc.LotNo. (optional)
ExpiryDate Desc.ExpiryDate. (optional)

For each iteration of the loop, an OUTDETAILS record is inserted in the database:

INSERT INTO OUTDETAILS
  (AVIZNO, ARTCODE, QTYASKED, QTY, COMMENTS, INVOICENO, STOCKCODE,
    ROWNO, PCNAME, FILLDATE, TransactionType)
VALUES
  (:AVIZNO, :ARTCODE, :QTYASKED, :QTY, :COMMENTS, :INVOICENO, :STOCKCODE,
   0, :PCNAME, GetDate(), 'COR')

Where

AVIZNO InvNo
ARTCODE Desc.ArtNo
QtyAsked Desc.Quantity
Qty Minimum between loop Qty field and Remaining Qty.
COMMENTS Desc.Comment.
InvoiceNo Loop ID field.
StockCode Desc.StockCode.
PCNAME Name of current user.
The remaining quantity is subtracted with the quantity from the inserted record.

If stockcode is 75, then an OUTDETAILSCC record is inserted in the DB:

INSERT INTO OUTDETAILSCC (AVIZNO, ARTCODE, QTY, STOCKCODE, OriginalSTOCKCODE,
  Supplier, PCNAME, FILLDATE, COMMENTS, TransactionType)
VALUES
  (:AVIZNO, :ARTCODE, :QTY, 99, :OriginalSTOCKCODE,
  :Supplier, :PCNAME, GetDate(), :COMMENTS, 'COR')

Where

AVIZNO InvNo
ARTCODE Desc.ArtNo
Qty Minimum between loop Qty field and Remaining Qty.
OriginalStockCode Desc.StockCode.
Supplier Desc.Supplier.
PCNAME Name of current user.
COMMENTS Desc.Comment.
Loop exits if remaining qty is zero, or the end of query data is reached.
call SaveScanhistory.

CCStockCorrection

Execute customs cleared stock correction.

Declaration:

Procedure CCStockCorrection(Desc : TCCStockCorrectionDescriptor);

1. Check that all elements of descriptor are non-empty. 1. if Quantity is negative, check that the abs(quantity) is less than the result of

GetMaxCCStock(desc.ArticleID, 99)

if not, an exception is raised. 1. Generate an INVNO :

INVNO:='COR-FIX-'+FORMATDATETIME('YYMMDD-HHNNSS', NOW);

1. If desc.quantity is negative, then insert an OUTDETAILSCC record:

INSERT INTO OUTDETAILSCC
  (AVIZNO, ARTCODE, QTY, STOCKCODE, OriginalSTOCKCODE,
   PCNAME, FILLDATE, SUPPLIER, TransactionType)
VALUES
   (:AVIZNO, :ARTCODE, :QTY, :STOCKCODE, :OriginalSTOCKCODE,
    :PCNAME, GetDate(), :SUPPLIER, 'COR')

Where * AVIZNO InvNo * ARTCODE Desc.ArtNo * Qty Abs(Desc.Quantity). * StockCode Desc.StockCode. * OriginalStockCode Desc.StockCode. * Supplier Desc.Supplier. * PCNAME Name of current user.

If desc.quantity is positive, then insert an INVDETAILSCC record:

INSERT INTO INVDETAILSCC
  (INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER, PCNAME,
  FILLDATE, TransactionType, ORIGINALID)
VALUES
  (:INVOICE, :ARTCODE, :QTY, :STOCKCODE, :SUPPLIER, :PCNAME,
  GetDate(), 'COR', 0)

Where

AVIZNO InvNo
ARTCODE Desc.ArtNo
Qty Abs(Desc.Quantity).
StockCode Desc.StockCode.
Supplier Desc.Supplier.
PCNAME Name of current user.

ChangeLotNo

Call to change lot no on incoming traffic.

Declaration:

Procedure ChangeLotNo(Desc: TLotNoChangeDescriptor);

1. Executes the following query

UPDATE INVDETAIL SET
  LOTNO=:NewLotNo
  EXPIRYDATE=:ExpiryDate
WHERE
  (ARTCODE = :ArticleID)
  AND (LOCATION=:Location)
  AND (STOCKCODE=:StockCode)
  AND (FILLDATE=:ReceptionDate)
  AND (lotno=:OldLotNo);

GetInvArticleReceptionData

Call to get article data for the reception screen in scanner module.

Declaration:

Procedure GetInvArticleReceptionData(Const InvoiceCode, ArticleCode : String) : TArticleReceptionData;

The invoice and article ID are determined based on the Article code and Invoice code. If either of them does not exist, or the combination of article and invoice does not exist, an exception is raised.

Based on the IDs, the following data is fetched and returned in the result

SELECT
  Min(idoStockCode) as StockCode,
  count(*) as DetailCount,
  sum(QTYIN) AS Quantity
from
  INVDETAILSOP A
  LEFT JOIN STOCKCODE B ON (B.Name=A.StockCode)
where
  (idoArticleFK=:ArticleID)
  AND (idoInvoiceFK=:InvoiceID)
ORDER BY ORDID ASC

If stockcode is empty, then it is determined from the following:

SELECT DISTINCT
  idoStockCode
FROM
  INVDETAILSOP
WHERE
  (idoInvoiceFK=:InvoiceID)
ORDER BY
  idoStockCode

if the result of this is empty, stockcode is 99.

RegisterReceivedQuantity

Call to register a scanned quantity in reception.

Declaration:

Procedure RegisterReceivedQuantity(descr : TReceptionDescriptor) ;

1. Code checks whether there are enough articles in the location:

SELECT
  SUM(Qty) as Qty,Count(*)  as TheCount, Min(ID) as TheID
FROM
  scanin
WHERE
  (siLocationFK=:locationID)
  AND (siInvoiceFK=:InvoiceID)
  AND (siArticleFK=:ArticleFK)
  AND (siStockCode=:StockCode)
  -- AND {CustomFilter}

The {CustomFilter} is a filter on LotNo, if one is provided and expirydate is non-empty.

If the requested quantity is larger than the available quantity, an error is raised:

You are trying to take :Quantity items from this location, but only :Qty items are available.

1. If there is no scan-in record (TheCount=0) for this article and location, an article is inserted:

insert into  SCANIN (INVOICE, ARTCODE, LOCATION, STOCKCODE, QTY,
   CREATIONDATE, CREATIONTIME, MYOPERATOR, LOTNO, EXPIRYDATE)
Values  (:INVOICE, :ARTCODE, :LOCATION, :STOCKCODE, :QTY,
   GetDate(), :CREATIONTIME, :CurrentUser, :LOTNO, :EXPIRYDATE)

Where * Invoice is InvoiceID. * ArtCode is ArticleID. * Location is LocationID. * StockCode is stockcode. * Qty is quantity * CreationTime Current time. * CurrentUser Currently logged in user. * LotNo LotNo. * ExpiryDate Expirydate or null if not set. 1. An event is registered in ScanHistory table with optype RECEPTION. 1. If there was a scanin event (TheCount=1, TheID is set), the Qty field is updated with the new value.

UPDATE SCANIN SET
  Qty = :Qty
WHERE
  (ID=:TheID)