How to Find Out All Closed or Canceled Line Items from a Customer
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 <> '';
- 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