Ga naar inhoud

Kitting

Service Kitting

Synopsis

Service for the controlling of kitting.

Service Data structures

  • record TKitOrderDescriptor
  • SupplierID (int64) The ID of the supplier of the article.
  • MasterCode (String) master article code.
  • StockCode (String) Stock code for the new article.
  • Quantity (Integer) Quantity to be created.
  • LocationID (int64) Optional location to pick from.

  • Comment (String) Optional comment to add.

  • record TKitOrderResult

  • OrderID (int64) ID of created record.

  • Warning (String) a warning to be displayed to the user.

  • record TMoveKitToStockDescriptor

  • KitOrderID (int64)
  • Quantity (Integer)
  • LocationID (int64)
  • ExpiryDate (TDateTime)
  • LotNo (string)

Service Calls

CreateKitOrder

  • Creates a kit order in the system.

Declaration: 

Function CreateKitOrder(aOrder : TKitOrderDescriptor) : TKitOrderResult;
The function inserts a kitting order in the system, and does all stock operations necessary to do the order. (sample code: Unit89, OperateKitOnStock) 1. Get Supplier name from supplier ID in aOrder: 
SELECT NAME From SUPPLIERS where (ID=:SupplierID)
1. Insert a record in CGROUP 
INSERT INTO CGROUP (ARTCODE, QTY, STOCKCODE, CREATIONDATE, SUPPLIER, SUPPLIERID, PCNAME, StockOperated, COMMENTS)
 VALUES (:ARTCODE, :QTY, :STOCKCODE, GetDate(), :SUPPLIER, :SUPPLIERID, :PCNAME, 'Y', :COMMENTS)
With parameters:

Param Source
ARTCODE aOrder.MasterCode
QTY aOrder.Quantity
StockCode aOrder.StockCode
SUPPLIER Supplier name
SupplierID aOrder.SupplierID
PCNAME Currently logged in user
COMMENTS aOrder.Comment

The ID of the newly inserted record must be kept (KitID) and returned in the Result.orderID.

  1. Insert the details of the kit in CGROUPDET: 
    INSERT INTO CGROUPDET (IDNO, ARTCODE, UNITQTY, STOCKCODE)
SELECT DISTINCT
  :KitID, LinkedArtcode, QTY , :StockCode
FROM
  ArticleLink
WHERE
  (ARTCODE=:MasterCode)
  2. Get a new block ID for the out blocks: 
    SELECT MAX(BLOCKNO) FROM OUT
    Add 1 to result (BlockNO).
  3. Create order 
    INSERT INTO OUT
  (AVIZ, DATA, CLIENT, BLOCKNO, FILENAME, PCNAME, FILLDATE, IDCLIENT, COUNTRY, OrderType)
VALUES
  (:AVIZ, GetDate(), :CLIENT, :BLOCKNO, 'KIT ORDER', :PCNAME, GetDate(), :IDCLIENT, 'LCW', '000')
    With parameters:
Param Source
AVIZ 'KG'+KitID
CLIENT Supplier name
BLOCKNO BlockNo
PCNAME Currently logged in user
IDCLIENT aOrder.SupplierID

For each article in the kit, take it from stock and add it to the order. Articles in kit: 

SELECT DISTINCT
  A.LinkedARTCODE as Artcode, A.QTY as UNITQTY
FROM
  ArticleLink A
WHERE
  (ARTCODE = :MasterCode)
ORDER BY
  ARTCODE
Select an article from the stock (AvailableArticles): 
With ART As (
SELECT
  INVOICE, A.ARTCODE, A.LOCATION,  A.QTY AS QTYIN, A.STOCKCODE, A.FILLDATE, A.ID,
  isnull(C.SQ, '99') as MyPRIORITY,
  ISNULL(A.ExpiryDate, '1900-01-01') as 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,
  (CASE
    WHEN EXPIRYDATE IS NULL THEN 3
    WHEN EXPIRYDATE IS not NULL and DateAdd(Day, -240, ExpiryDate)>GetDate() THEN 2
    WHEN EXPIRYDATE IS not NULL and DateAdd(Day, -240, ExpiryDate)<=GetDate() AND EXPIRYDATE>GETDATE() and isnull(AExpFirst,'N')<>'Y' THEN 1
    WHEN EXPIRYDATE IS not NULL and DateAdd(Day, -240, ExpiryDate)<=GetDate() AND EXPIRYDATE>GETDATE() and isnull(AExpFirst,'N')='Y' THEN 4
    WHEN EXPIRYDATE IS not NULL and ExpiryDate<=GetDate() THEN 0
   ELSE 1
   END) AS ExpirySQ
FROM
  INVDETAILS A
  INNER JOIN ARTICLE D ON (A.ARTCODE=D.NAME)
  INNER JOIN LOCATION C ON (A.LOCATION=C.LOCATION)
-- {AdditionalJoin}
WHERE
  (A.ARTCODE = :ArticleCode)
  AND (A.STOCKCODE = '75')
  AND isnull(C.NotForPicking,'')<>'Y'
  AND (C.NotForPicking<>'Y')
--  AND {LocationFilter}

)
SELECT
  ART.*,
  ART.QTYIN-isnull(SUM(B.QTY), 0) as QTY
FROM
  ART
  LEFT OUTER JOIN OUTDETAILS B ON (ART.ID=B.INVOICENO)
WHERE
  ExpirySQ<>0
GROUP BY
   ART.INVOICE, ART.ARTCODE, ART.LOCATION , ART.COLOR, ART.QTYIN, ART.AMBALAJ, ART.STOCKCODE, ART.FILLDATE, ART.ID,
   ART.MYPRIORITY, ART.ExpiryDate, ART.LotPriority, ART.HighPrSQ,ART.ExpirySQ, Art.LocationLevelPriority
HAVING
   ART.QTYIN-isnull(SUM(B.QTY), 0) > 0
ORDER BY
   ExpirySQ DESC, EXPIRYDATE ASC, HighPrSQ ASC, LotPriority asc, LocationLevelPriority asc, FILLDATE ASC, MyPRIORITY ASC
{LocationFilter} is replaced with 
(isNull(A.LOCATION,'')<>'')
if the aOrder.location is empty, or 
(A.LOCATION<>:Location)
if it is not empty. ArticleCode is the article code from the current article in the kit article details loop. The AdditionalJoin filter is empty.

UsedQty is the quantity still needed to fill the kit, topped by available quantity in the current AvailableArticles line.

The following insert is performed while the asked quantity (QtyAsked = KitQty x GrpQty) is not reached: 

INSERT INTO OUTDETAILS
 (AVIZNO, ARTCODE, QTYASKED, QTY, AMBALAJ, COMMENTS,
  OLDAMB, COLOR, INVOICENO, STOCKCODE, ROWNO, PCNAME, FILLDATE, LSBFILE,
  FILLTIME, QTYSOFT, TransactionType)
VALUES
  (:AVIZNO, :ARTCODE, :QTYASKED, :QTY, '' :COMMENTS,
   '', '', :INVOICENO, '75', :ROWNO, :PCNAME, GetDate(),'KIT ORDER',
   :FILLTIME, :QTYSOFT, ''KIT'')
If there are no quantities ( AvailableArticles is empty), the insert query is performed exactly once, with Qty 0 and comment 'Not delivered because stock is 0'

Where the parameters are filled as follows:

Param Source
AVIZNO 'KG'+KitID
ARTCODE Current article
QtyAsked UniqQTY*GrpQty
Qty UsedQty or 0
COMMENTS Empty or 'Not delivered because stock is 0'
INVOICENO AvailableArticles.QTY
ROWNO Kit Article Loop counter (1-based)
PCNAME Currently logged in user
LSBFILE KIT ORDER
FILLTIME Current time in hh:nn format
QTYSOFT UsedQty or 0
CLIENT Supplier name
BLOCKNO BlockNo
PCNAME Currently logged in user

The loop maintains a QtySupplied counter, which is the amount of articles actually supplied.

If there were not enough articles on stock (QtySupplied < QtyAsked), then a warning message is attached to the result: 

Voor artikel :CODE zijn :Qty gevraagd, maar er waren slechts :QtySupplied artikels beschikbaar.
There may be multiple warnings (1 per article)

For every kit article line where QtySupplied is nonzero, the StockControl.OperateStockCC is called: * Aviz is 'KG'+KitID * Qty is QtySupplied * Supplier is empty. * StockCode is 99.

CheckKitOrderPossible

Check if a kitting order is possible. It returns a list of errors. All possible errors are returned.

Declaration: 

Function CheckKitOrderPossible(Const ArtCode,aStockCode : String; AQty : Integer) : TStringArray;
The following checks are done: 1. Check if the article has sub-articles. 
SELECT
  count(*) as TheCount
FROM
  articlelink
WHERE
  (ARTCODE=:ArtCode)
If the result is 0, an error is reported: 
This article :ArtCode  is missing parts.
2. check if all sub-articles are defined. 
Select
  LINKEDARTCODE
from
  articlelink L
  LEFT JOIN ARTICLE A ON (A.NAME=L.LINKEDARTCODE)
WHERE
  (L.ARTCODE=:ArtCode)
  AND (L.LINKEDARTCODE is NULL)
If the query is non-empty, all returned articles are included in an error message: 
Unknown article in this composition:
:ArticleList
Please register the articles.
3. For all parts, the stock is checked: 
SELECT
  SUM(ISNULL(QTYIN,0)-ISNULL(QTYOUT, 0)) as QtyAvail
  B.LinkedARTCODE
FROM
  articlelink B
  LEFT OUTER JOIN STOCTOTAL A ON
    (A.ARTCODE=B.LINKEDARTCODE)
    AND (A.STOCKCODE=:StockCode)
  INNER JOIN Location C on (A.LOCATION=C.LOCATION) AND (isnull(C.NotForPicking,'')<>'Y')
WHERE
  (B.ARTCODE = : Artcode)
  AND (CASE WHEN EXPIRYDATE IS not NULL and DateAdd(Day, -240, ExpiryDate)<=GetDate()
       THEN 0
       ELSE 1 END)=1
GROUP BY
  B.LinkedARTCODE
If QtyAvail=0, An error is returned: 
No stock available for :LinkedArticle
If QtyAvail is less than Qty, a warning is returned:

Article :ArtCode has insufficint stock:  **QtyAvail**
4. Same as previous, but now for customs cleared stock: 
SELECT
  isnull(SUM(isnull(A.QTY, 0)), 0) as QtyAvail,
  B.LinkedARTCODE
FROM
  articlelink B
  LEFT OUTER JOIN STOCTOTALCC A on
     (A.ARTCODE=B.LINKEDARTCODE) and (A.STOCKCODE='99')
WHERE
  (B.ARTCODE =: Artcode)
GROUP BY
  B.LinkedARTCODE, B.QTY
If QtyAvail=0, An error is returned: 
No customs-cleared stock available for :LinkedArticle
If QtyAvail < Qty, An error is returned: 
Not enough customs-cleared stock available for :LinkedArticle

DeleteKitOrder

Delete all information related to a kit.

Declaration: 

Procedure DeleteKitOrder(Const aKit : Integer; const aMoveLocation : String);
1. if aMoveLocation is not empty, the result of the following query 
SELECT
  B.ARTCODE, B.STOCKCODE, B.LOCATION, A.QTY, B.LOTNO, B.ID INTO tmpMoveDelKit
FROM
  OUTDETAILS A
  INNER JOIN INVDETAILS B on (A.InvoiceNo=B.ID)
WHERE
  (A.AVIZNO='KG'+:ID)
is used to perform the following loop, for each record in the result set the StockControl.EasyPartialMove call is executed. Parameters are filled as follows: * Oldlocation: Artcode, location, SockStockCode, lotno from the query result. * Newlocation: Artcode, SockStockCode, lotno from the query result. Location is MoveLocation. * Quantity: Query result Qty field. * Comments: 'Move after del-kit KG'+IntToStr(aKit) * TransactionType: 'MOVE' 2. The following queries are executed with KitID equal to 'KG'+IntToStr(aKit) and ID equals aKit 
DELETE FROM INVDETAILS WHERE (INVOICE=:KitID);
DELETE FROM INVDETAILSCC WHERE (INVOICE=:KitID);
DELETE FROM INV WHERE (INVOICE=:KitID);
DELETE FROM OUTDETAILS WHERE (AVIZNO=:KitID);
DELETE FROM OUTDETAILSCC WHERE (AVIZNO=:KitID);
DELETE FROM OUT WHERE (AVIZ=:KitID);
DELETE FROM CGROUPDET WHERE (IDNO=:ID);
DELETE FROM CGROUP WHERE (ID=:ID);

AssignMasterLotNo

Assign a master LotNo to a kit order.

Declaration: 

Function AssignMasterLotNo(aKitID : Integer) : String;
1. The following query is executed: 
SELECT
  min(A.ExpiryDate) AS ExpDate,
  A.ARTCODE
FROM
  INVDETAILS A,
  INNER JOIN ARTICLE C ON (A.ARTCODE=C.NAME)
  INNER JOIN OUTDETAILS B ON (A.ID=B.INVOICENO)
WHERE
  (B.AVIZNO='KG'+:aKitID)
  AND (A.ExpiryDATE is not null)
GROUP BY
  EXPIRYTIME, A.ARTCODE
ORDER BY
  ExpDate asc
2. if the query returns a non-empty result and

the ExpDate field is not null, the basis of the lot number is composed of: 

DecodeDate(ExpDate,Y,M,D);
MyExpExt:='L'+char(ord('A')+M-1)+IntToStr(MyExpYear);
3. if MyExpExt is not empty, then the following query is used to compose a suffix: 
SELECT
  count(*) as acount, Max(MLOTNO) as aMax
FROM
  CGROUP
WHERE
  MLOTNO LIKE :MyExpExt
(MyExpExt has %s appended). The numerical value of aMax+1 is used to determine the lot number: 
Result:=MyExpExt+Format('%.4d',[aMax+1])
The lot number is applied to the kit order: 
UPDATE
  CGROUP
  SET MLOTNO=:LOTNO
  MExpiryDate=:MyExpiryDate
WHERE
  (ID=:aKitID)

MarkAsChecked

This will mark outgoing orders as checked by the current user.

Declaration: 

Procedure MarkAsChecked(aKitID : Integer);
Executes the following query 
UPDATE OUTDETAILS SET
  checkedqty=qtycf,
  CHECKEDBY=:CurrentUser
  CHECKEDDATE=GetDate(),
  CHECKEDTIME=:CheckedTime,
WHERE
  (AVIZNO=:AVIZNO)
  AND isnull(CheckedBy,'')=''
With parameters: * CurrentUser '+formatdatetime(HH:NN, NOW)+' * CheckedTime : Current time in HH:NN format. * AVIZNO 'KG'+IntToStr(aKitID)

ChangeKitQuantity

This will change the quantity of the kit

Declaration: 

Procedure ChangeKitQuantity(aKitID : Integer; ANewQty  : Integer);
The procedure checks first whether the quantity is less than the current quantity in the kit (CGROUP.Qty). If not an error is raised: 
The quantity :ANewQty exceeds the current quantity: :Qty
if the quanity is less, it modifies the quantity: 
UPDATE CGROUP SET
  QTY=:aNewQty
where
  (ID=:aKitID)

ChangeKitArticleQuantity

This will change the master quantity of an article in the kit

Declaration: 

Procedure ChangeKitArticleQuantity(aKitID,aLineId : Integer; ANewQty  : Integer);
The procedure checks first whether the quantity is less than the current quantity in the kit (CGROUPDET.Qty). If not an error is raised: 
  The quantity :ANewQty exceeds the current quantity for :Article: :Qty
(article the article code)

if the new article quantity is OK, the following are executed 

UPDATE OUTDETAILS SET
  QTY=:aNewQty
  QTYSOFT=:aNewQty
where
  (ID=:aLineID)
and, secondly: 
UPDATE OUTDETAILS SET
  QTYCF=:aNewty
WHERE
  (ID=:aLineID) AND (ISNULL(PICKEDBY, '')<>'')

MoveKitToStock

This will move part of the prepared kit to the stock.

Declaration: 

Procedure MoveKitToStock(desc : TMoveKitToStockDescriptor);
1. The stock must be reduced for all components of the kits. This means that for each line in the following loop: 
SELECT
  DISTINCT B.ArtCode,
  A.ExpiryDATE,
  A.Lotno,
  D.UnitQTY,
  sum(B.QTY) AS QTY,
  sum(B.QTYCF) AS PK_QTY
FROM
  INVDETAILS A
  INNER JOIN OUTDETAILS B ON A.ID = B.INVOICENO
  INNER JOIN CGROUP C ON 'KG' + CAST (C.ID AS VARCHAR (15)) = B.AVIZNO
  INNER JOIN CGROUPDET D ON (B.ARTCODE = D.ARTCODE) AND (C.ID = D.IDNO)
WHERE
  B.AVIZNO = :KitOrder
  AND (pickedby <> '')
  AND (checkedby = '')
GROUP BY
  B.ArtCode,
  A.ExpiryDATE,
  A.Lotno,
  D.UnitQTY
ORDER BY
  B.ARTCODE,
  A.Lotno ASC
Do the following:

  1. If no INV exists for KitOrder: 
    SELECT  count(*) from INV where (INVOICE=:KitOrder)
    insert a new order: 
      insert into INV (INVOICE, DATA, SUPPL, PCNAME, FILLDATE, DEP)
  Select DISTINCT
   :Invoice,getDate(),Supplier, :UserName,Now,'KIT'
   FROM CGROUP WHERE ('KG'+CAST(ID AS VARCHAR(15))=:KitOrder)

  2. Set the requested quantity (QtyRequested) to the kit qty * article quantity. Run a second loop: 

    SELECT
 A.ID, A.QTY as QtyStock, A.QTYCF
From
  outdetails A
  Inner join INVDETAILS B ON A.INVOICENO=B.ID
where
 (A.avizno=:OrderID)
 AND (A.ARTCODE=:ArtCode)
 AND (B.LOTNO=:LotNo)
 and checkedby=''
    The ArtCode parameter value comes from the field in loop 1 and the lotno parameter valie also from the field in loop 1.

  3. While requested quantity is positive, run the loop:

  4. if the requested quantity is strictly less than the stock quantity from the second loop line, then the detail line is split in 2 lines: one with requested quantity, one with remaining quantity.

    • Update existing line to match requested quantity:

UPDATE OUTDETAILS
SET
 QTY=:QtyRequested,
 qtycf=:QtyRequested,
  qtySOFT=:QtyRequested,
checkedqty=:QtyRequested,
CHECKEDBY=:CurrentUser,
CHECKEDDATE=:GetDate(),
CHECKEDTIME=:GetDate()
WHERE ID=:ID
* Insert duplicate with remaining quantity (:RemainingQty=:QTYSTOC-:QTYREQUESTED): 
INSERT INTO OUTDETAILS
  (AVIZNO, ARTCODE, QTYASKED, QTY, AMBALAJ, COMMENTS,
   OLDAMB, COLOR, INVOICENO, STOCKCODE, ROWNO, PCNAME, FILLDATE, QTYCF, DATACF, LSBFILE, FILLTIME,
   BPOINTS, QTYSOFT, UNITVALUE, CURENCY, TotalValue, TCurency, PICKEDBY, MODIFYQTY, MODIFY, TIMECF,
   LSBDATE, LSBTIME, CHECKEDQTY, CHECKEDBY, CHECKEDDATE, CHECKEDTIME, PICKEDBY2, COLNO, COLQTY, COLDATE,
   TransactionType, TransitLocation
)
 SELECT
   AVIZNO, ARTCODE, QTYASKED, :RemainingQty, AMBALAJ, COMMENTS,
   OLDAMB, COLOR, INVOICENO, STOCKCODE, ROWNO, PCNAME, FILLDATE, :RemainingQty, DATACF, LSBFILE, FILLTIME,
   BPOINTS, :RemainingQty, UNITVALUE, CURENCY, TotalValue, TCurency, PICKEDBY, MODIFYQTY, MODIFY, TIMECF,
   LSBDATE, LSBTIME, NULL, NULL, NULL, NULL, PICKEDBY2, COLNO, COLQTY, COLDATE,
   TransactionType, TransitLocation
 FROM
   OUTDETAILS
 WHERE
   (ID=:ID)
QtyRequested is set to zero, so the loop ends.

  1. If the requested quantity is larger or equal to stock quantity, update existing line: 

    UPDATE OUTDETAILS
SET
  QTY=:QtyStock,
  qtycf=:QtyStock,
  qtySOFT=:QtyStock,
  checkedqty=:QtyStock,
  CHECKEDBY=:CurrentUser,
  CHECKEDDATE=:GetDate(),
  CHECKEDTIME=:GetDate()
WHERE
ID=:ID
    subtract QtyStock from requested quantity.

  2. The prepared kit quantity must be entered in the stock. Execute: 

    INSERT INTO INVDETAILS (
  INVOICE, ARTCODE, QTYIN, QTY, LOCATION,
  SUPPLIER, STOCKCODE, PCNAME, FILLDATE,
  ExpiryDate, LotNo, TransactionType
)
 Select DISTINCT
:KitOrderID, artcode, :Quantity, :Quantity, :Location,
 Supplier, STOCKCODE, :CurrentUser,GetDate(),
 :ExpiryDate, :LotNo, 'KIT'
FROM
  CGROUP
WHERE
  'KG'+CAST(ID AS VARCHAR(15))=:KitOrderID

  3. Prepared kit must be inserted in customs-cleared stock: 

     INSERT INTO INVDETAILSCC (
     INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER,
     PCNAME, FILLDATE, TransactionType
)
Select DISTINCT
 :KitOrderID, ARTCODE, :Quantity, '99', Supplier,
 :CurrentUser, GetDate(), 'KIT'
FROM
  CGROUP
WHERE
  'KG'+CAST(ID AS VARCHAR(15))=:KitOrderID

  4. Update kit definition to reflect new available quantity. Execute: 

    UPDATE
  CGROUP
SET
 READYQTY = READYQTY + :Quantity
WHERE
  'KG'+CAST(ID AS VARCHAR(15))=:OrderID