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

    • 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 ...

    • PO Edit List Print & Export CSV Shows Canceled Line Items When Not Selected

      Release Date: 03/16/2022 Version: 7.5 & Up Q - The PO Edit List option under Purchase Order Processing does not adhere to the selection made when exporting to a CSV file. We choose to export only these PO status types (U R P) where the possible ...

    • 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 ...

    • Feature - Export 865 for Canceled/Closed Orders Separately

      Release Date: 10/12/17 This feature allows the user to specify whether canceled and closed orders should be exported separately from regular changes when exporting order audit data. To support this, a new flag has been added to the EDI Profile ...

    • Feature - Mass Close Backorder Line Items by Customer

      Release date: 12/17/2021 Version 8.5 and Above A new feature, "Mass Close BO Line Items by Customer" has been added to Global Setup / Utilities / COP Utilities / Utilities. This function can be valuable for those who use Elliott's backorder features. ...