How to Find Out All Closed or Canceled Line Items from a Customer

How to Find Out All Closed or Canceled Line Items from a Customer

Release Date: 05/16/2023
Version: 8.x & Up

Q - How does a user run a report of all closed or cancelled line items for a specific customer for a specific date range? For example, closed/cancelled line items for customer 005950 from 1/1/22 to 12/31/22 including the item data and quantity.

A - There’s no canned report in Elliott. On the other hand, you can retrieve data from the CPINVLIN table and maybe join with CPINVHDR and other tables. The following is a sample SQL statement:
SELECT inv_itm_inv_no, inv_itm_itm_no, inv_itm_inv_date, inv_itm_qty_order, inv_itm_cancel_cls 
from CPINVLIN
where inv_itm_cust_no = '005950'
and inv_itm_inv_date between 20220101 and 20221231
and inv_itm_cancel_cls <> '';

The possible values of inv_itm_cancel_cls are:
  • Blank = The invoice line item was not canceled or closed (i.e., this is a normal invoice line item).
  • C = The invoice line item was closed.
  • X = The invoice line item was canceled.
  • Y = This is a legacy value from the old days where it means the invoice line item is either closed or canceled.
The SQL statement will take advantage of the filter condition of inv_itm_cust_no = "005950," which is an index to allow data to return back quickly.  The three index columns you can use to speed up your query are: 
  • INV_ITM_INV_NO
  • INV_ITM_ITM_NO
  • INV_ITM_CUST_NO.  
If you can use any one of them in your query for filtering, that will help to make your query run faster.

EMK

    • Related Articles

    • Canceled PO Line Items Still Exporting on EDI

      Released Date: 11/7/2022 Version: 8.5 & Up Q - We’ve seen this happen twice in the last month. We create a purchase order, then cancel line items on it. When we export the EDI document again, it still contains the cancelled line items on it, which we ...

    • ARPRGSCN Accounts Receivable Purge Closed Open Items

      Purge Closed Open Items Application Overview Periodically, you will want to remove from the A/R Open Item File all transactions that have been fully paid off. Otherwise, the file could become very large over a period of time. This application will ...

    • Feature - Export Turn Around 855 for Canceled/Closed Line Items

      Release Date: 03/09/2021 Version: 8.5 & Up In Customer EDI Profile, Screen 6, the following flag has been changed: 7. Export Turn Around During Sales Order Import? It is now changed to: 7. Export Turn Around In SO Import/Exp Cancel Lin If you answer ...

    • Change - Allow User to Delete Canceled or Closed Trx in Warehouse Receiving

      Release Date: 02/05/2021 Version: 8.5 & Up In PO Warehouse Receiving, if the user chooses to delete a receiving transaction that's marked as canceled or closed, the user will receive the following message: Order Canceled - Receiving Not Allowed See ...

    • CPISSULS Customer Order Processing Issue Serial/Lot Component Items

      Issue Serial/Lot Component Items Application Overview After posting invoices to Accounts Receivable which includes a line item which is non-stocked and controlled and has a product structure which contains Serial/Lot components, a record for each one ...