Skip to content

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 
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: 
If you can use any one of them in your query for filtering, that will help to make your query run faster.


Feedback and Knowledge Base