Ga naar inhoud

Deliverycontrol

Service DeliveryControl

Synopsis

Service to control outgoing data.

Service Data structures

  • Record TArticleDescriptor
  • ArticleID (Integer) ID of Article to add.
  • ArticleCode (String) code of Article to add.
  • Quantity (Integer) Quantity of article to add.
  • StockCode (integer) stock code to use when adding article.
  • DutyCleared (boolean) duty cleared article or not ?

  • CustomPickJobID (int64) custom Locations from which to pick article. (0 if none)

  • TPickPackCounts

  • PickedQtyDiff
  • CheckedQtyDiff
  • NotPickedCount

  • NotCheckedCount

  • Enumerated TScanResetOperation

  • sroPick Reset pick data.
  • sroCheck Reset check data.

  • sroBoth Reset both pick and check data.

  • Array TIDArray

  • array of int64 values.

  • Record TPickjobDescriptor

  • InvoiceID (int64) Invoice ID

  • TrolleyLocationID (string?) Trolley location .

  • Record TPickScanDescriptor

  • InvoiceID (int64)
  • ArticleID (int64)
  • StockCode (String)
  • LocationID (Integer)

  • LotNo (string)

  • Record TPickCheckScanResult

  • DisplayMessage (string)
  • StartScanDate (DateTime)

  • HaveLotNo (Boolean)

  • Record TArticleScanDataDescriptor

  • PickJobID (int64)
  • ArticleID (int64)
  • LocationID (int64)
  • OutDetailID (int64)
  • TransitLocation (string)
  • SingleRow (Boolean)

  • StockCode (string)

  • Record: TSaveScanResult

  • LocationEmptyStock : Boolean;
  • PickJobDone Boolean;

Service Calls

UpdateOutgoingArticleCount

Service call to update counts on outgoing articles. (sample code : unit204, updaterow)

Declaration: 

Procedure UpdateOutgoingArticleCount(aOrderID : int64; aArticle : String);
1. The following query is run (call it Q1) 
SELECT
  ID,  qty, QtyCF, CheckedQty, ID, PickedBy,
  qty-isnull(CheckedQty, 0) as RESTQTY
from
  Outdetails
WHERE
  (avizno=:aOrderID)
  AND (ARTCODE=:aArticle)
  AND (qty>isnull(CheckedQty, 0))
ORDER BY
  outdetails.ID asc
2. If the query result is empty, then an exception is raised: 
Article cannot be added, over-delivery ?);
2. If the result of Q1 is not empty, if RestQTy equals 1 then 1. If PickedBy is empty, then the following query is executed: 
UPDATE
  OutOrderDetail
SET
  odQuantityCurrent=isnull(odQuantityChecked, 0)+1,
  odPickedByFK=:CurrentUser,
  odPickedBy2FK=:CurrentUser,
  odQuantityChangedOn=GetDate()
WHERE
  (odID=:aID)
Here Currentuser is the currently logged in user. aID is the ID obtained in the first query. 2. the following query is executed: 
UPDATE
  OutOrderDetail
SET
  odQuantityChecked = isnull(odQuantityChecked, 0)+1,
  odCheckedByFK=:currentuser,
  odCheckedOn=GetDate()
WHERE
  (odID=:aID)
Here Currentuser is the currently logged in user. aID is the ID obtained in the first query. Remark: Check if the queries can be combined. QtyCf,CheckedQty may be tricky. 3. If Q1 is not empty and RestQty is not 1, the following query is executed: 
UPDATE OUTDETAILS SET
  checkedqty=isnull(CheckedQty, 0)+1
WHERE
  (ID=:AID)

CreateNewColli

Call to allocate a new colli number:

Declaration: 

Function CreateNewColli(aOrderID : int64) : Integer;
The function returns the COLNO result of the following insert Query: 
INSERT INTO OutdetailsPackingColCounter
  (AvizNo, CreationDate, PCName)
OUTPUT
  Inserted.COLNO
VALUES
  (:AOrderID,getdate(),:CurrentUser)
CurrentUser is the currently logged in user.

ClearOrderColliData

Call to reset the colli data for an order.

Declaration: 

Procedure ClearOrderColliData(aOrderID : int64);
The following SQL is executed: 
Delete from OutDetailsPacking where (AvizNo=:AOrderID);

ConfirmColliOrder

Call to confirm a colli packaged order.

Declaration: 

Procedure ConfirmColliOrder(aOrderID : int64)
For all articles in the order 
Select
  Artcode,Count(*) as thecount
FROM
  OutDetailsPacking
where
  (AVIZNO=:AOrderID)
the following is executed: 
UPDATE OUTDETAILS SET
  checkedqty=:TheCount,
  CHECKEDBY=:CurrentUser,
  CHECKEDDATE=GetDate(),
  CHECKEDTIME=GetTime()
WHERE
  (Avizno=:OrderID)
  AND (ARTCODE=:ArtCode)
  AND isnull(CheckedBy,'')=''

PrepareColliPackingListData

Call to prepare printing of colli packing list.

Declaration: 

Procedure PrepareColliPackingListData(aOrderID : int64)
Executes the following query 
UPDATE OutDetailsPacking SET
  OutDetailsPacking.OriginalImpRow=OutDetails.OriginalImpRow,
  OutDetailsPacking.UnitPrice=cast(
      Right(Left(OutDetails.OriginalImpRow,164), 5)+'.'+
      Right(Left(OutDetails.OriginalImpRow,166), 2)  as numeric(12,2) ),
  OutDetailsPacking.UnitListPrice=cast(
      Right(Left(OutDetails.OriginalImpRow,175), 5)+'.'+
      Right(Left(OutDetails.OriginalImpRow,177), 2)  as numeric(12,2) ) ,
  OutDetailsPacking.ContactPerson=LTRIM(Right(Left(OUT.OriginalImpRow2, 301), 50)),
  OutDetailsPacking.Phone=LTRIM(Right(Left(OUT.OriginalImpRow2, 321), 20)),
  OutDetailsPacking.Fax=LTRIM(Right(Left(OUT.OriginalImpRow2, 341), 20)),
  OutDetailsPacking.Language=LTRIM(Right(Left(OUT.OriginalImpRow2, 351), 10)),
  OutDetailsPacking.Currency=LTRIM(Right(Left(OUT.OriginalImpRow2, 361), 10))  
FROM
  OutDetailsPacking
  INNER JOIN Out on (OutDetailsPacking.Avizno=Out.Aviz)
  INNER JOIN OutDetails on
    (OutDetailsPacking.Avizno=Outdetails.Avizno)
    and (OutDetailsPacking.Artcode=Outdetails.Artcode)
WHERE
  OutDetailsPacking.AVIZNO=:orderID

GetEDISerialNo

Call to get a new EDI message serial number.

Declaration: 

Function GetEDISerialNo(Const aFileType : string; aFileDate : TDateTime) : Integer;
The call increases the CounterID in EDI_COUNTER for the specified filetype and file date and returns that number. The number is stored in the table.

CancelOrder

Call to cancel an outgoing orders pick&Pack info.

Remark This has a lot of code in common with DeleteDelivery. Factor out common code. 

Procedure CancelOrder(Const aOrderID : int64)
The following queries are executed: 
DELETE FROM OUTDETAILS
WHERE AVIZNO=:OrderID;

DELETE FROM OUTDETAILSCC
WHERE AVIZNO=:OrderID;

DELETE FROM OUT
WHERE AVIZNO=:OrderID;
The action is logged inuser history: 
 UserHistory(:OrderNo, 'ORDER', 'ORDER COMPLETELY DELETED', 'PICK AND PACK MENU');

DeleteOutBlock

Call to delete a block of OUT (shipping) orders.

Declaration: 

Function DeleteOutBlock(Const BlockNo : string) : Integer;
The call deletes the records pertaining to block BlockNo: 1. First, the action is logged in the DELHISTORY table: 
INSERT INTO DELHISTORY(CPERSON, DELDATE, DELHOUR, BLOCKNO)
VALUES (:CPERSON, :DELDATE, :DELHOUR, :BLOCKNO)
With the following parameters * CPERSON (string) The currently logged in user. * DELDATE (datetime) Today's date. * DELHOUR (String) Time in hh:nn:ss format. * BLOCKNO (String) The value of BlockNo 2. Records are updated in SPIR_ORDER: ``` UPDATE SPIR_ORDER SET PROCESSED='N'
WHERE OrderNo in (SELECT AVIZ FROM OUT WHERE (BLOCKNO=:BlockNO)) 
2. Records are updated in **NRW_OUT**:
UPDATE NRW_OUT SET PROCESSED='N'
WHERE ShipmentNumber in (SELECT AVIZ FROM OUT WHERE (BLOCKNO=:BLOCKNO)) 
3. Records are updated in **XML_OUT**:
UPDATE XML_OUT SET PROCESSED='N'
WHERE OrderNo in (SELECT AVIZ FROM OUT WHERE (BLOCKNO=:BLOCKNO)) 
4. Records are deleted from **OUTDETAILS**:
DELETE FROM OUTDETAILS WHERE AVIZNO IN (SELECT AVIZ FROM OUT WHERE (BLOCKNO=:BlockNo)) 
5. Records are deleted from **OUTDETAILSCC**:
DELETE FROM OUTDETAILSCC WHERE AVIZNO IN (SELECT AVIZ FROM OUT WHERE (BLOCKNO=:BlockNo)) 
5. Records are deleted from **OUT**:
DELETE FROM OUT WHERE (BLOCKNO=:BLOCKNO); 
The result is the number of deleted rows from OUT (RowsAffected)
6. The action is listed in the userhistory (auditing API):
UserHistory(BLOCKNO, 'BLOC', 'BLOC WAS COMPLETELY DELETED FROM STOC', 'DELETE LAST BLOC'); 
#### DeleteDelivery
Call to delete a delivery order.

**Declaration:**
```delphi
Procedure DeleteDelivery(Const aOrderID : Int64);
Executes the following queries (AVIZNO -> aOrderID): 1. OutDetails. 
DELETE FROM OUTDETAILS  
WHERE
  AVIZNO = :AVIZNO;
2. OutDetailsCC 
DELETE FROM OUTDETAILSCC  
WHERE
  AVIZNO = :AVIZNO;
3. InvDetailsCC 
DELETE FROM INVDETAILSCC  
WHERE
  (INVOICE = :AVIZNO)
  AND (TRANSACTIONTYPE='Correction');
  AND (COMMENTS='Correction for order : '+:AvizNO)
4. OUT 
DELETE FROM OUT  
WHERE
  AVIZ = :AVIZNo;
5. The action is listed in the userhistory (auditing API): 
UserHistory(AVIZ, 'ORDER', 'ORDER COMPLETELY DELETED', 'ORDER MENU');

AddArticleToOrder

Call to add an article to a delivery order.

Declaration: 

Function AddArticleToOrder(aOrderID : int64; AArticle: TArticleDescriptor) : Integer;

  1. let AskedQty equal AArticle.Quantity.
  2. let Qty equal AArticle.Quantity.
  3. if StockCode = 75, then the quantity is checked against available quanity: 
    SELECT
  isnull(sum(QTY), 0)
from
  FSTOCTOTALCC(:ArticleID);
    if this is less than AskedQty, then Qty is reduced to the available quantity.
  4. Pick locations are selected for the order from the following query, see kitting service, (AvailableArticles). The AdditionalJoin macro is replaced by:

 inner join PickTable S ON
   (A.STOCKCODE=S.PKSTOCKCODE)
   AND (A.LOCATION=S.PKLOCATION)
   AND isnull(A.ExpiryDate, '2000-01-01')=isnull(S.PKExpiryDate, '2000-01-01')
   AND isnull(A.LOTNO, '')=isnull(S.PKLOTNO, '')
   AND (S.JobID=:JobID)
4. if result from the AvailableArticles query is empty, a single record is inserted in OUTDETAILS with quantity 0 and Comment 'Not delivered because the stock is 0 (zero)', 4. The result from the AvailableArticles query is looped, and a record is inserted in OUTDETAILS till the asked quantity is reached. Let ThisQty equal the Available quantity from current record topped by still needed qty. 
INSERT INTO OUTDETAILS
  (AVIZNO, ARTCODE, QTYASKED, QTY,  COMMENTS,
   INVOICENO, STOCKCODE, ROWNO, PCNAME, FILLDATE, LSBFILE,
  FILLTIME, QTYSOFT, TransactionType)
VALUES (
  (:AVIZNO, :ARTCODE, :QTYASKED, :Qty, :Comment
   :INVOICENO, :STOCKCODE, :ROWNO, :PCNAME, GetDate(), 'MANUAL FILL',
  :FILLTIME, :QtySoft, 'ORD')
Where the parameters are: * AvizNO : order number. * ArtCode : Article code. * QtyAsked : AskedQty * Qty : ThisQty * Comment : empty; * InvoiceNo : InvoiceID. * StockCode : AArticle.StockCode. * ROWNO : FormatDateTime() * PCName : Logged in user. * QtySoft : ThisQty 4. if total quantity is nonzero, the StockControl.OperateStockCC is called: * Aviz is OrderID * Qty is total quantity. * Supplier is empty. * StockCode is 99 is AArticle.DutyCleared = true, else it is 25.

  1. The PickTable job is removed from the pick table.
  2. The result is the number of articles inserted in the order.

GetOrderPickedCheckedCounts

Call to prepare statistics for the MarkOrderPickedChecked call;

declaration: 

Function GetOrderPickedCheckedCounts (aOrderID : int64) : TPickPackCounts;
the result is filled from the following query: 
select
  SUM(CASE WHEN QTYCF<>QTY THEN 1 ELSE 0 END) AS PickedQtyDiff,
  SUM(CASE WHEN CheckedQTY<>QTY TNEN 1 ELSE 0 END) AS CheckedQtyDiff,
  SUM(CASE WHEN PICKEDBY='' THEN 0 ELSE 1 END) AS NotPickedCount,
  SUM(CASE WHEN CHECKEDBY='' THEN 1 ELSE 0 END) AS NotCheckedCount,
FROM
  OUTDETAILS
WHERE
  (AVIZNO=:OrderNO)
The record fields are filled from their corresponding fields in the query result.

MarkOrderPickedChecked

Call to mark not yet picked and packed items in order as picked and packed.

declaration: 

Procedure MarkOrderPickedChecked(aOrderID : int64);
1. The following 2 queries are executed: 2. Update picked 
UPDATE OUTDETAILS SET
  qtycf=qty,
  PICKEDBY=:CurrentUser,
  PICKEDBY2=:CurrentUser,
  DATACF=GetDate(),
  TIMECF=GetDate()
WHERE
  (PICKEDBY='')
  AND (AVIZNO=:OrderNo)
2. Update checked 
UPDATE OUTDETAILS SET
  checkedqty=qty,
  CHECKEDBY=:CurrentUser,
  CHECKEDDATE=GetDate(),
  CHECKEDTIME=GetDate()
WHERE
  (CHECKEDBY='')
  AND (AVIZNO=:OrderNo)
1. The action is logged in userhistory: 
UserHistory(:OrderNo, 'ORDER', 'ORDER MARKED AS PICKED AND CHECKED', 'PICK AND PACK MENU');

ResetOrderPickedChecked

Call to mark not picked and packed items in order as not yet picked and packed.

declaration: 

Procedure ResetOrderPickedChecked(aOrderID : int64);
1. The following query is executed: 
UPDATE OUTDETAILS SET
  qtycf=qty,
  checkedqty=0,
  PICKEDBY='',
  PICKEDBY2='',
  CHECKEDBY='',
  DATACF=Null,
  TIMECF='',
  CHECKEDDATE=Null,
  CHECKEDTIME=''
WHERE
  AND (AVIZNO=:OrderNo)
2. The action is logged in userhistory: 
UserHistory(:OrderNo, 'ORDER', 'RESET PICKING AND CHECKING FOR THE ORDER', 'PICK AND PACK MENU');

GetOrderTrackingNumber

Call to fetch the order tracking Number, this is the tracking number from table SPIR_ORDER.

declaration: 

Function GetOrderTrackingNumber(aOrderID : int64) : String;
First the following query is executed: 
Select TRACKINGNUMBER FROM SPIR_ORDER WHERE ORDERNO=:OrderNo
if the tracking number is less than 3 long, get it it from: 
Select TRACKINGNUMBER FROM OUT WHERE AVIZ=:OrderNo
(combine in 1 query?)

OrderResetScanPickCheck

Call to reset the pick and/or check data for selected scans.

declaration: 

Procedure OrderResetScanPickCheck(aOrderID : int64; ScanIDs : TIDArray; Operation : TScanResetOperation);
1. Executes the following Query 
 UPDATE OUTDETAILS SET
--   {fieldlist}
 WHERE
   (ID IN :ScanIDS)
2. for Operation sroCheck the fieldlist is 
checkedqty=NULL ,
CHECKEDBY='',
CHECKEDDATE=NULL,
CHECKEDTIME=''
2. for Operation sroPick the fieldlist is 
qtycf=NULL,
PICKEDBY='',
PICKEDBY2='',
DATACF=NULL,
TIMECF=''
2. For operation sroBoth the fieldlist is the list of fields of both together. 1. The operation is logged in userhistory: 
UserHistory(OrderID, 'LINES', 'RESET FOR '+{Description}, 'PICK AND PACK MENU');
Where description is one of PICK, CHECK or PICK+CHECK, depending on the operation.

MarkOrdersAsPrinted

Mark selected orders as printed.

declaration: 

Procedure MarkOrdersAsPrinted(OrderIDS : TIDArray);
Execute the following query: 
UPDATE OUT SET
  PLPrintingDate=GetDate()
WHERE
  (AVIZ in :OrderIDs);

AddToPickJob

Add an invoice to trolley pick job.

declaration: 

function AddToPickJob(ajobID: Int64; aDescr : TPickjobDescriptor) : Int64;
1. if Execute the following query if thetrolley location is set: 
UPDATE OUT SET
  OUT.TLOCATION=:TrolleyLocationID
WHERE
  (AVIZ=:InvoiceID);
1. if (jobID=-1) get a new job ID from seqPickJobID generator.
1. Insert descriptor data in PickJob table using the ID. 1. Returns the jobID.

PickCheckArticleScanned

Executed when an article is scanned.

declaration: 

function PickCheckArticleScanned(aDesc :  TPickScanDescriptor) : TPickCheckScanResult;
1. Barcode must be non-empty. 1. Check if the article exists, and get its ID: 
SELECT  
  aID, NAME, NAME2, EANCODE4, SET1, SET2, B.ARTCODE, GetDate() as CrtDate, Pop_up_flag_del
FROM
  ARTICLE A
  LEFT OUTER JOIN ARTICLELINK B on A.NAME=B.ARTCODE
WHERE
  :aBarCode in (aEANCode, aEANCode1, EANCode2, EANCode2, EANCode4)
If the article does not exist, an exception is raised: 
Unknown barcode :abarcode.
the Data returned from the query is saved: 2. Pop_up_flag_del is stored in result.DisplayMessage 2. CrtDate Is returned in result.StartScanDate 1. Check if the article was alreay picked: 
Select
  min(isnull(PICKEDBY, ''))
from
  printjob
  left join Out on (pjInvoiceFK=oID)
  inner join Outdetails on aviz = avizno
  inner join invdetails on
    (INVDETAILS.ID = OUTDETAILS.INVOICENO)
    AND (INVDETAILS.STOCKCODE = OUTDETAILS.STOCKCODE)
    AND (INVDETAILS.ARTCODE =  OUTDETAILS.ARTCODE)
WHERE
  (pjPrintJobID=:printjobid)
  -- {PK_ORDER_COND}
  AND (OUTDETAILS.ARTCODE = :ArticleID)
  AND (OUTDETAILS.STOCKCODE = :StockCode)
  AND isnull(invdetails.location=:Location)
  AND isnull(invdetails.Lotno=:LotNo)
If it was already picked, an exception is raised: 
Article already picked: :Name
1. Check that the article ID corresponds to the expected ID (desc.articleid). if not an exception is raised: 
Barcode wrong: not the correct article ?
1. Check lotno for this location: 
SELECT
   SUM(QTYIN - ISNULL(QTYOUT, 0))
FROM
   STOCTOTAL A
   left outer join ARTICLE B on NAME = ARTCODE,
   LOCATION D
WHERE
  (A.LOCATION = :Location )
  AND (A.LOTNO <> :LotNo)
  AND (A.ARTCODE = :ArticleID)
  AND (A.LOCATION=D.LOCATION)
HAVING
  SUM(QTYIN-ISNULL(QTYOUT, 0))>0
UNION
SELECT
   A.QTY
FROM
  OUTDETAILS a,
  INNER JOIN INVDETAILS b ON
    (b.ID = a.INVOICENO)
    AND (b.STOCKCODE = a.STOCKCODE)
    AND (b.ARTCODE = a.ARTCODE)
  INNER JOIN ARTICLE c ON (a.artcode = c.name)
  INNER JOIN LOCATION D ON (B.LOCATION = D.LOCATION)
WHERE
   (B.LOCATION = :Location)
   AND (B.LOTNO <> :LotNo)
   AND (B.ARTCODE = :articleid)
   AND (pickedby = '')
   AND (A.filldate > DateAdd(day - 15, GETDATE()))
if the qty field >0 then Result.HaveLotNo is true

ScanTrolleyLocation

Called to check if the scanned trolley location is OK.

Declaration: 

Procedure ScanTrolleyLocation(aPrintJobID, aInvoiceID : Int64; const TrolleyLocation : String)

  1. Check that Trolley location is at least 5 characters long. if not, an exception is raised with message: 
    The trolley location must at least be 5 characters long.
  2. Check that no articles from other orders are yet in this trolley location: 
    Select
   count(*)
from  
   pickjob
   LEFT JOIN OUT  on (aviz=pjInvoiceFK)
WHERE
  (PickJobID=:PickJobID)
  AND (AVIZ<>InvoiceID)
  AND (TLOCATION=:TrolleyLocation);
    If the count>0, an exception is raised: 
    This Trolley Location is used for another order.
Please select a new Location.
  3. Set the trolley location for the selected order: 
    UPDATE OUT SET
  TLOCATION = Trolleylocation
WHERE
  (AVIZ = :aInvoiceID )

SaveArticleScanData

Called when an article was scanned for picking, to end picking operation and save results. (sample code see scanner, unit9 Savecandata AND edit3KeyPress)

Declaration: 

Function SaveArticleScanData(aDesc: TArticleScanDataDescriptor) : TSaveScanResult;
1. for Desc.singlerow=true, the following SQL executed if the invoice does not start with KG: 
UPDATE OUTDETAILS SET
  OUTDETAILS.qtycf=:Quantity,
  OUTDETAILS.TransitLocation=:TranslitLocation,
  OUTDETAILS.PICKEDBY=:UserID,
  OUTDETAILS.PICKEDBY2=:UserID,
  OUTDETAILS.DATACF=GetDate(),
  OUTDETAILS.TIMECF=GetDate(),
  checkedqty=:Quantity,
  CHECKEDBY=:UserID,
  CHECKEDDATE=getdate(),
  CHECKEDTIME=gedate(),
  OUTDETAILS.PickingLocation=:locationID
from  
  pickjob,
  INNER JOIN Out ON (pjInvoiceFK=AVIZ)
  INNER JOIN Outdetails on (aviz=avizno)
WHERE
  {PK_ORDER_COND}
 AND OUTDETAILS.ARTCODE=:article
 AND OUTDETAILS.ID=:outdetailsid
1. for Desc.singlerow=true, the following SQL is executed if the invoice starts with KG: 
UPDATE OUTDETAILS SET
  OUTDETAILS.qtycf=:Quantity,
  OUTDETAILS.TransitLocation=:TranslitLocation,
  OUTDETAILS.PICKEDBY=:UserID,
  OUTDETAILS.PICKEDBY2=:UserID,
  OUTDETAILS.DATACF=GetDate(),
  OUTDETAILS.TIMECF=GetDate(),
  OUTDETAILS.PickingLocation=:locationID
from  
  pickjob,
  INNER JOIN Out ON (pjInvoiceFK=AVIZ)
  INNER JOIN Outdetails on (aviz=avizno)
WHERE
  {PK_ORDER_COND}
  AND OUTDETAILS.ARTCODE=:article
  AND OUTDETAILS.ID=:outdetailsid
1. If Desc.singlerow=false then the quantities are updated in a loop determined by the following query: 
SELECT
  ID, qty, QtyCF
from
  printjob
  inner join Out on (pjInvoiceFK=oud.avizno)
  inner join Outdetails on aviz = avizno
  inner join invdetails on INVDETAILS.ID = OUTDETAILS.INVOICENO AND  INVDETAILS.ARTCODE = OUTDETAILS.ARTCODE
WHERE
   (pjPrintjobID=:printjobid)
   AND (OUTDETAILS.ARTCODE = :articleID)
   AND (OUTDETAILS.STOCKCODE = :StockCode)
   AND (invdetails = :LocationID)
   AND (invdetails.Lotno = :LotNo)
   AND isnull(OUTDETAILS.PICKEDBY=:UserID)
order by
   outdetails.ID asc
2. Before the start of the loop, the remaining quantity (rquantity) is set to desc.quantity 2. For each row, calculate uquantity= min(rquantity,Qty) where qty is the value of the field in the current record in the loop. 2. Subtract uquantity from rquantity. 2. Update the outdetails record with the following query if the invoice does not start with 'KG' 
UPDATE OUTDETAILS SET
  OUTDETAILS.qtycf=:Quantity,
  OUTDETAILS.TransitLocation=:TranslitLocation,
  OUTDETAILS.PICKEDBY=:UserID,
  OUTDETAILS.PICKEDBY2=:UserID,
  OUTDETAILS.DATACF=GetDate(),
  OUTDETAILS.TIMECF=GetDate(),
  checkedqty=:Quantity,
  CHECKEDBY=:UserID,
  CHECKEDDATE=getdate(),
  CHECKEDTIME=gedate(),
  OUTDETAILS.PickingLocation=:locationID
from  
  pickjob,
  INNER JOIN Out ON (pjInvoiceFK=AVIZ)
  INNER JOIN Outdetails on (aviz=avizno)
WHERE
  OUTDETAILS.ID=:outdetailsid
Where outdetalsid is the value of the ID field from the current record in the loop. 2. Update the outdetails record with the following query if the invoice starts with 'KG' 
UPDATE OUTDETAILS SET
  OUTDETAILS.qtycf=:Quantity,
  OUTDETAILS.TransitLocation=:TranslitLocation,
  OUTDETAILS.PICKEDBY=:UserID,
  OUTDETAILS.PICKEDBY2=:UserID,
  OUTDETAILS.DATACF=GetDate(),
  OUTDETAILS.TIMECF=GetDate(),
  OUTDETAILS.PickingLocation=:locationID
from  
  pickjob,
  INNER JOIN Out ON (pjInvoiceFK=AVIZ)
  INNER JOIN Outdetails on (aviz=avizno)
WHERE
  OUTDETAILS.ID=:outdetailsid
1. Save current operation in scan history: 
SaveScanHistory('PICK', :InvoicblockeID, ArticldeID, Quantity,Location, stockcode, PickjobID)
1. Calculate result.LocationEmptyStock from this query: 
With XXX as (
  Select
    A.ARTCODE,
    A.ID,
    isnull(A.QTY, 0) - isnull(SUM(B.QTY), 0) as QTY
  from
    dbo.INVDETAILS A
    LEFT OUTER JOIN dbo.OUTDETAILS B ON
      (A.ID = B.INVOICENO)
      and (A.ARTCODE = B.ARTCODE)
      and isnull(B.TRANSACTIONTYPE, '') = 'ORD'
      AND (B.pickedby<>'')
  where
    A.LOCATION = :LocationID
    AND A.ARTCODE=:ArticleID
  GROUP BY
    A.ARTCODE,
    A.ID,
    isnull(A.QTY, 0)
  HAVING
    isnull(A.QTY, 0) - isnull(SUM(B.QTY), 0) <> 0
)
Select
  isnull(sum(QTY), 0)
from
  XXX
If the result field is 0, then result.LocationEmptyStock is true. 1. Calculate result.PickJobDone from query: 
SELECT
  count(*)
FROM
  pickjob
  inner join out A on (pickjob.pjInvoiceFK=a.aviz)
  inner join outdetails B ON A.AVIZ=B.AVIZNO
WHERE
  (pjPickJobID=:PickJoBID)
  and isnull(pickedby, '')=''
If the result field is 0, then result.PickJobDone is true.