Returns

Service Returns

Synopsis

Services for the handling of returns.

Service Data structures

• Enumerated TReturnArticleStatus
• rasUnknown (unknown)
• rasOK (Not damaged)

• rasDamaged (Damaged)

• Enumerated TReturnInvoiceSource

• risUnknown (unknown source)
• risCurrent (this year's invoices)
• risPrevious (last year's invoices)

• risOrder (this is an order from OUT table)

• Record TReturnInvoiceDetail

• Source (TReturnInvoiceSource) Source of the invoice.
• FileID (string) file ID of the invoice.
• AllowPrepaid (boolean) Does the country allow prepaid.

• CountryCode (string) countrycode.

• Record TReturnInvoiceArticle

• ArticleID (int64) article ID.

• ArticleStatus (TReturnArticleStatus) (article status)

• Record TAddInvoiceToReturnDescriptor

• InvoiceCode Code of the invoice.
• Source (TReturnInvoiceSource) Source of the invoice.
• Prepayed (Boolean) Was invoice prepaid
• PrepayedDate Date on which invoice was prepaid.
• SpecialInstructions (Boolean)
• StockCode (Integer)
• CustomsClearedCode (Integer) 25 or 99
• ReturnReasonID (Int64).
• NotPickedUp (Boolean) Picked up or not ?

• FileID (String) Original invoice file ID. Can be empty.

• Array TAddInvoiceToReturnDescriptorArray

• Array of TAddInvoiceToReturnDescriptor records.

• Record TReturnDescriptor

• SupplierID (int64) The supplier.
• ReturnReasonID (int64) The ID of the return reason.
• Damaged (Boolean) Damaged goods or not.
• OrderCode (String) Original order ID.

• InvoiceIDs (TAddInvoiceToReturnDescriptorArray) Original invoices.

• Record TAddArticleToReturnDescriptor

• ReturnID (Int64) Return descriptor ID.
• Quantity (int) The article quantity.
• ArticleCode (String) Article code.
• ArticleID (Int64) Article ID.
• SupplierID (Int64) Supplier ID.
• PaletteNr (Int64) Palette nr.
• StockCode (String) Stock code.
• CustomsClearCode (Integer) one of 25 or 99
• LotNo (String) Lot number.
• ReturnReasonID (Int64) Return reason ID.
• ExpiryDate (TDateTime) Expiry date.

• TransactionType (TTransactionType) transaction type.

• Record TPickFromPaletteDescriptor OrderNumber (string) order number for pick. PaletteNumber (string) Palette number. ReturnID (integer) Return order.

• Record TMoveReturnArticleToStockDescriptor ReturnOrder (string) return order code. Location (string) location code. ArticleLineID (int64) article line ID ReturnTypeID (string) return type. Quantity (Integer) quantity of articles to move.

Service Calls

NewReturn

Call to create new return. Returns the new return ID. (See scanner app, unit 57)

Declaration:

Function NewReturn(Descr: TReturnDescriptor): Int64;

1. Check that the supplierID is correct. Raise exception if it is not. 1. Check that the ReturnReasonID is correct: Raise exception if it is not. 1. If the supplier has 'IsOrderIDRequired' for the selected supplier and OrderID is empty or does not exist raise an exception. check sql:

select Count(*) FROM OUT WHERE (AVIZ=:OrderID)

1. Execute the following query:

INSERT INTO RETUR
  (CREATIONDATE, CREATIONTIME, PCNAME, OriginalOrderNumber, Supplier, SupplierID, Reason, ReasonID)
Values
  ( GETDATE(), GetTime(), :CurrentUser,:OriginalOrderNumber, :Supplier, :SupplierID, :Reason, :ReasonID)

If the list of invoice IDs is not empty, call AddInvoiceToReturn for each ID in the list.

AllocatePalette

Call to allocate new palette.

Declaration:

Function AllocatePalette() : Int64;

1. Use seqPalette sequence to generate new pallete number and return this number.

FindInvoiceForReturn

Declaration:

Function AddInvoiceToReturn(AInvoiceCode : String) : TReturnInvoiceDetail;

1. Result.Source is risCurrent when the following returns a result:

SELECT
  FileID,Country
FROM
  DBPrefix.GR_PRINTFILE_INVOICE
WHERE
  InvoiceNumber = :InvoiceCode
order by FileID

Result.FileID is filled with the FileID field of the query result.

1. Result.Source is risPrevious when the following returns a result:

   SELECT
     FileID,Country
   FROM
     DBPrefix.LCWExtractPreviousYear
   WHERE
     InvoiceNumber = :InvoiceCode
   

   Result.FileID is filled with '0'
2. Result is risUnknown if both queries did not return a result
3. AllowPrepaid is true if the country is AT/NO (riscurrent) or GN/GA (risprevious)

AddInvoiceToReturn

Add invoice number to return. (see scanner app, unit 51)

Declaration:

Function AddInvoiceToReturn(AReturn : Integer; Invoices: TAddInvoiceToReturnDescriptorArray);

1. For each record in the array, execute the following: 2. If NotPickedUp is true, check that the invoice does not exist yet in return invoices:

Select  RetNo from
  RETURDETGR
WHERE
  (OriginalInvoiceNumber=:InvoiceCode)
 AND (RetNo =:ReturnID)

If the count>0 then an error is raised:

Invoice is already registered in return :RetNo.

If the list of articles is empty, an exception is raised:

  Invoice :RetNo has no returned articles associated with it.

1. if NotPickedup is True, CustomsClearedCode must be 99. If not, an exception is raised:

   For not picked up invoices, CustomsClearedCode must be 99
   

2. if NotPickedup is True, StockCode must be 75. If not, an exception is raised:

   For not picked up invoices, StockCode must be 75
   

3. Check the source of the invoice.
4. If Source is risCurrent, the following query must return a result:

   SELECT
     FileID,Country
   FROM
     DBPrefix.GR_PRINTFILE_INVOICE
   WHERE
     InvoiceNumber = :InvoiceCode
   order by FileID
   

   if the query returns a result, then ReturnReason is ignored and changed depending no the country code (field Country) :
   1. 'Not Picked Up' for one of DK,NO,SE
   2. 'NPU - No fee' for all other countries. The FileID is recorded if it was not yet provided, it is checked if it was provided. if there was no result, an exception is raised:

      Unknown invoice: InvoiceCode.
      

5. If the source is risPrevious, the existence of the invoice is checked using:
   

   SELECT
     FileID,TenantOrg
   FROM DBPrefix.LCWExtractPreviousYear
   WHERE (InvoiceNumber = :InvoiceCode)
   

   if the query returns a result, then ReturnReason is ignored and changed depending no the country code (field TenantOrg) :
   1. 'Not Picked Up' for one of GD,GN,GS
   2. 'NPU - No fee' for all other countries. The FileID is recorded if it was not yet provided, it is checked if it was provided. if there was no result, an exception is raised:

      Unknown invoice: InvoiceCode.
      

6. For each record in the invoice, a record is inserted in the database.
7. If NotPickedUp is true for the record then the following query is used.
   • For Source risCurrent, the following SQL is performed:

     INSERT INTO RETURDETGR
       (RetNo, ARTCODE, QTY, STOCKCODE, ItemStatus, CCODE,
        LOCATION, CREATIONDATE, PCNAME, ReturnDate,
        OriginalInvoiceNumber, TenantOrg, OriginalFileID,  
        Prepaid, ReturnReason, SpecialInstructions, RetSource)
     Select  
       :RetNo, B.skuID, B.itemQty, :StockCode, 'Y', :CustomsClearedCode,
       'R.00.00.A', getdate(), :CurrentUser, :ReturnedDate,
       :InvoiceCode, TenantOrg, FileID,
       :Prepaid, :ReturnReason, :SpecialInstructions, :Source
     FROM
       DBPrefix.GR_PRINTFILE_INVOICE A
       INNER JOIN DBPrefix.GR_PRINTFILE_INVOICE_ITEMS B ON
       (A.FILEID=B.FILEID) AND (A.INVOICENUMBER=B.INVOICENUMBER)+
       WHERE
       AND (A.FILEID=:FileID) AND (A.InvoiceNumber = :InvoiceCode)
     

   • For source risPrevious the following must be done:

     INSERT INTO RETURDETGR
       (RetNo, ARTCODE, QTY, STOCKCODE, ItemStatus, CCODE,
        LOCATION, CREATIONDATE, PCNAME, ReturnDate,
        OriginalInvoiceNumber, TenantOrg, OriginalFileID,
        Prepaid, ReturnReason, SpecialInstructions, RetSource)
     Select  
       :RetNo,  B.skuID, B.itemQty, :StockCode, 'Y', :CustomsClearedCode,
       'R.00.00.A', getdate(), :CurrentUser, :PrepayedDate,
       :InvoiceCode, TenantOrg, FileID,
       :Prepaid, :ReturnReason, :SpecialInstructions, :Source
     FROM
        DBPrefix.LCWExtractPreviousYear
     WHERE
       (B.InvoiceNumber = :InvoiceCode)
     

8. If NotPickedUp is False for the record then the following query is used in a loop for every article in the list of returned articles:
   1. If source is risCurrent, the query is

      INSERT INTO RETURDETGR
         (RetNo, ARTCODE, QTY, STOCKCODE, ItemStatus, CCODE,
          LOCATION, CREATIONDATE, PCNAME, ReturnDate,
          OriginalInvoiceNumber, TenantOrg, OriginalFileID,  
          Prepaid, ReturnReason, SpecialInstructions, RetSource)
      Select  
        :RetNo,  B.skuID, B.itemQty, :STOCKCODE, :ArticleStatus, :CustomsClearedCode,
        'R.00.00.A',  getdate(), :CurrentUser, :ReturnDate,
        B.InvoiceNumber,  TenantOrg, FileID,
        :Prepaid, :ReturnReasonID, :SpecialInstructions, :ReturnSource
      FROM
        DBPrefix.GR_PRINTFILE_INVOICE A
        INNER JOIN DBPrefix.GR_PRINTFILE_INVOICE_ITEMS B
        ON (A.FILEID=B.FILEID) AND (A.INVOICENUMBER=B.INVOICENUMBER)
      WHERE
        AND (A.FILEID=:FileID)
        AND (A.InvoiceNumber = :InvoiceID);
        AND (B.ID=:ArticleID)
      

   2. If source is risPrevious, the query is

      INSERT INTO RETURDETGR
        (RetNo, ARTCODE, QTY, STOCKCODE, ItemStatus, CCODE,
         LOCATION, CREATIONDATE, PCNAME, ReturnDate,
         OriginalInvoiceNumber, TenantOrg, OriginalFileID,  
         Prepaid, ReturnReason, SpecialInstructions, RetSource)
      Select  
        :RetNo, B.skuID, B.itemQty, :STOCKCODE, :ArticleStatus, :CustomsClearedCode,
        'R.00.00.A', getdate(), :CurrentUser, :ReturnDate,
        B.InvoiceNumber, TenantOrg, FileID,
        :Prepaid, :ReturnReasonID, :SpecialInstructions, :ReturnSource
      FROM
        DBPrefix.LCWExtractPreviousYear B
      WHERE
        (B.InvoiceNumber=:InvoiceID)
        AND (B.ID=:ArticleID)
      

AddArticleToReturn

This call must be used to add an article to a return stock.

Declaration:

Function AddArticleToReturn(Desc: TAddArticleToReturnDescriptor) : Int64;

1. The Return ID is checked for existence. Record the invoice ID. Check whether it is a damage return or not. 1. One of articlecode or articleID must be filled in. 1. ReturnReasonID must be filled in. 1. Quantity must be positive. 1. StockCode must be 25 or 99 if it is not a damaged return. 1. Check that transaction type is one of the valid transaction types. 1. Check that customsclearance code is one of 25 or 99 1. If ArticleCode is filled in but ID is not, article ID is searched through:

SELECT  
  A.ID, NAME, NAME2, EANCODE4, SET1, SET2, B.ARTCODE, A.FLOTNO,
  isnull(ExpiryTime, 0)*30 as ExpiryTime
FROM
  ARTICLE A
  LEFT OUTER JOIN ARTICLELINK B on (A.NAME=B.ARTCODE)
WHERE
  :ArticleCode in (EANCODE, EANCODE1, EANCODE2, EANCODE3, EANCODE4)

if the article does not exist, an exception is raised:

Article :ArticleCode does not exist.

2. If the article ID is filled, the following query is executed

SELECT  
  A.ID, NAME, NAME2, EANCODE4, SET1, SET2, B.ARTCODE, A.FLOTNO,
  isnull(ExpiryTime, 0)*30 as ExpiryTime,
  EANCODE, EANCODE1, EANCODE2, EANCODE3, EANCODE4
FROM
  ARTICLE A
  LEFT OUTER JOIN ARTICLELINK B on (A.NAME=B.ARTCODE)
WHERE
   A.ID=:ArticleID

2. If the query has no result, an exception is raised:

Article ID :ArticleID does not exist.

2. If the article code was filled, the code is checked to be one of the 5 EANCODE fields.

Article ID :ArticleID and Article code :ArticleCode do not match.

2. ExpiryDate is noted. 1. The article is inserted into the return invoice:

INSERT INTO INVDETAILS (
  INVOICE, ARTCODE, QTYIN, QTY,LOCATION, SUPPLIER,
  STOCKCODE, PCNAME, FILLDATE, ExpiryDate, LotNo,
  TransactionType, CUSTOMSCODE, DATETIMEID,
  COLNO, COLNO1)
VALUES (
  :ReturnInvoiceID,:ArticleID,:Quantity,:Quantity,'R.00.OO.A',:SupplierID,
  :STOCKCODE,:CurrentUser,GetDate-366,:ExpiryDate,:LotNo,
  :TransactionType,:CustomsCode,:DateTimeID,
:PalleteNo, :DamagedOrNull
);

* DateTimeID is calculated in the usual manner. * DamagedOrNull is 'D' when it is a damaged return, null otherwise. 1. If it is a non-damage return, the article is also inserted in customs-clearance data:

INSERT INTO INVDETAILSCC
   (INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER,
    PCNAME, FILLDATE, TransactionType, DateTimeID)
VALUES
   (:ReturnInvoiceID,:ArticleId,:Quantity,:CustomsCode,:SupplierID,
     :CurrentUser,:GetDate,:TransactionType,:DateTimeID
   )

1. Insert scan history record:

SaveScanHistory(:Op, 'RET'+:ReturnID, :ReturnID, :ArticleCode,:Quantity ,
'R.00.00.A', :STOCKCODE, '', '' , '' , '', '0', '');

OP is RETURN-G or RETURN-D depending on good/damaged return.

MoveReturnToStock

This call moves the return contents to the stock.

Declaration:

Function MoveReturnToStock (ReturnID, SupplierID : Integer; TransactionType: TTransactionType) : Integer;

1. The following query is executed to insert articles (which were not yet transferred) into the stock tables:

INSERT INTO INVDETAILS (
  INVOICE, ARTCODE, QTYIN, QTY,LOCATION, SUPPLIER, STOCKCODE, PCNAME,
  FILLDATE, ExpiryDate, LotNo, TransactionType, CUSTOMSCODE, DATETIMEID)
SELECT
  RET+:ReturnID, Artcode, QTY, QTY, Location,:Supplier,:StockCode,:CurrentUser,
  GetDate()-366,NULL,'',:TransactionType,CCODE,DateTimeID
FROM
  ReturDetGR
WHERE
  RetNo = :ReturnID
  and (Transferred<>'Y')

here DateTimeID is calculated as

DateTimeID:=FormatDateTime('yyyy.MM.dd hh.nn.ss.zzz', now);

The rows affected is assigned to the result of the function 1. The following query is executed to insert into customs clearance stock:

INSERT INTO INVDETAILSCC (
   INVOICE, ARTCODE, QTY, STOCKCODE, SUPPLIER,
   PCNAME, FILLDATE,  TransactionType, DateTimeID)
Select
  RET+:ReturnID, Artcode, QTY, CCODE , :Supplier,
  :CurrentUser,GetDate(),:TransactionType,:DateTimeID);
FROM
  ReturDetGR
WHERE
  (RetNo = :ReturnID) AND (Transfered<>'Y') ;

1. The following query is executed to update the articles:

UPDATE
  ReturDetGR
SET
  Transfered='Y'
WHERE
  (RetNo = :ReturnID)

PickFromPalette

Pick an article from the returns palette.

Declaration:

Function PickFromPalette(Desc : TPickFromPaletteDescriptor) : Boolean;

1. The following query is executed to mark the items as picked:

UPDATE RETUROUTDET SET
  PICKED='Y',
  PICKEDBY=:Currentuser,
  PICKEDDATE=GETDATE()
WHERE
  (IDNO=:OrderNumber)
  AND (PALNO=:PaletteNumber)

1. If no delivery order exists, create an order. 2. Determining whether a delivery order exists can be done with:

SELECT count(*) FROM OUT where (AVIZ=:ReturnNumber)

2. Creating a delivery order is done by getting a new pick order number and inserting the record:

INSERT INTO OUT
  (AVIZ, DATA, CLIENT, BLOCKNO, FILENAME,
   PCNAME, FILLDATE, IDCLIENT, COUNTRY, OrderType)
 VALUES (
   :ReturnNumber, GETDATE(), 'TVSHOP',:PickOrderNumber, 'RETD DELIVER',
   :CurrentUser,GetDate(),1,'','RET-DEL'
)

1. Insert the selected palette number articles:

INSERT INTO OUTDETAILS (AVIZNO, ARTCODE, QTYASKED, QTY,
  INVOICENO, STOCKCODE, ROWNO, PCNAME, FILLDATE, LSBFILE,
  FILLTIME, QTYSOFT, TransactionType, qtycf, PICKEDBY,
  PICKEDBY2, DATACF, TIMECF,  
  CHECKEDQTY, CHECKEDBY, CHECKEDDATE, CHECKEDTIME)
Select
  :ReturnNumber, ARTCODE, QTY, QTY,
  ID, STOCKCODE , ID ,:CurrentUser, getDate(), 'MANUAL FILL',
  GetDate(),QTY, 'RET-DEL', qty, :CurrentUser,
  :CurrentUser, getDate(),getDate(),getDate()
FROM
  INVDETAILS
WHERE
  (COLNO=:PaletteNumber)
  AND LEFT(TRANSACTIONTYPE, 3)='RET'

1. Insert the selected palette number articles in customs data:

INSERT INTO OUTDETAILSCC
  (AVIZNO, ARTCODE, QTY, STOCKCODE, OriginalSTOCKCODE,
   PCNAME, FILLDATE, SUPPLIER, TransactionType)
SELECT
  :ReturnNumber, ARTCODE , QTY, STOCKCODE, STOCKCODE,
  :Currentuser, GetDate() , 'TV SHOP',  'RETDEL'
FROM
  INVDETAILS
WHERE
  (COLNO=:PaletteNumber)
  AND STOCKCODE='75'
  AND LEFT(TRANSACTIONTYPE, 3)='RET'

1. The return value is true if the following query returns zero:

SELECT count(*) FROM RETUROUTDET
WHERE
  (IDNO=:OrderNumber)
  AND (PICKED<>'Y')

CheckPaletteLocation

Call to check return palette location. The palette may or may not yet have been picked.

Declaration:

Function CheckPalette(aCode : String; CheckNotPicked : Boolean) : String;

1. If CheckNotPicked is True then the following query is run:

SELECT
  count(*) as C
FROM
  RETUROUTDET
WHERE
  (palno=:aCode) AND PICKED='Y');

if the result C>0 then an exception is raised:

This palette was already picked.  

1. The following query is run:

SELECT DISTINCT
  location
FROM
  INVDETAILS
WHERE (COLNO=:Code) AND (LEFT(TRANSACTIONTYPE, 3)='RET')

The result's LOCATION field is returned as the function result.

MovePaletteToLocation

Function to put a palette on a certain location.

Declaration:

Procedure MovePalette(aPaletteCode,aLocation : String);

1. Check the location:

SELECT
  count(*) FROM Location
WHERE
  (LOCATION=:aLocation);

If the result is 0, then an exception is raised:

Unknown location: aLocation.

1. Execute the following query:

UPDATE
  INVDETAILS
SET
  LOCATION=:Location
WHERE
  (COLNO=:aPaletteCode)
  AND (LEFT(TRANSACTIONTYPE, 3)='RET');

MoveArticleToStock

Function to move articles to a location in the stock.

Declaration:

procedure MoveArticleToStock(Descr: TMoveReturnArticleToStockDescriptor);

1. Check that the location exists:

SELECT
  count(*) FROM Location
WHERE
  (LOCATION=:aLocation);

If the result is 0, then an exception is raised:

Unknown location: aLocation.

1. Check whether the quantity equals the full article quantity:

SELECT Qty,Artcode,stockcode, location as oldlocation FROM INVDETAILS WHERE ID=:ARTICLEID

the fields must be kept for the insertscanhistory operation. 2. If the requested quantity is larger than the current quantity: Raise an exception. 2. If the requested quantity equals the current quantity, execute the following query:

UPDATE INVDETAILS SET
  LOCATION=:Location
  RETURNTYPE=:ReturnType
  PCNAME=:CurrentUser
WHERE
  (ID=:ArticleID)

2. If the requested quantity is less than the current quantity: 3.Execute the following query (correcting the quantity at current location):

UPDATE INVDETAILS SET
  Qty=Qty-:Quantity
WHERE
  ID=:ArticleID  

3. Execute the following SQL (insert at new location)

INSERT INTO INVDETAILS (
  INVOICE, ARTCODE, QTYIN, QTY,  LOCATION, COMENTS, SUPPLIER, STOCKCODE,
  PCNAME, FILLDATE, COLNO, COLNO1, INVOICEGROUP, ExpiryDate, LotNo,
  TransactionType, TransferStockCodeID,  CustomsCode, RETURNTYPE)
SELECT
  INVOICE, ARTCODE, :Quantity, :Quantity, LOCATION, COMENTS, SUPPLIER, STOCKCODE,
  PCNAME, FILLDATE, COLNO, COLNO1, INVOICEGROUP, ExpiryDate, LotNo,
  TransactionType, TransferStockCodeID, CustomsCode, :RETURNTYPE
FROM  
  INVDETAILS
WHERE
  ID=:ArticleID

1. Insert the following in scan history:

SaveScanHistory(:op, 'M-'+FORMATDATETIME('YYMMDD-HHNNSS', NOW),
:Artcode, :Quantity, :Location , :OldLocation,
'Move '+:OldLocation+'->'+:Location, :OldLocation,
:StockCode , :ArticleCode, :ArticleID, '')

here Op is 'MOV-F-RET' for full move, 'MOVE-P-RET' for a partial move.