How to Retrieve Open Orders That Are Not Invoiced Through Query

How to Retrieve Open Orders That Are Not Invoiced Through Query

Release Date: 05/04/2022
Version: 7.5 & Higher

Q - I used the following SQL statement to get invoice history:

SELECT INV_ITM_INV_DATE, INV_ITM_ITM_NO, INV_ITM_DESC_1, INV_ITM_DESC_2, INV_ITM_QTY_ORDER,
INV_ITM_TOT_QTY_SHP, INV_ITM_UNIT_PRICE, INV_ITM_UNIT_COST, (INV_ITM_UNIT_PRICE*INV_ITM_QTY_ORDER) AS ExtPrice,
INV_ITM_INV_NO, INV_ITM_PROD_CATE, INV_TYPE, INV_ITM_CUST_NO, INV_SHIP_TO_NO, INV_SHIP_TO_NAME, INV_SHIP_TO_ADDR_1,
INV_SHIP_TO_CITY, INV_SHIP_TO_ST, INV_SHIP_TO_ZIPCD, INV_SHIP_TO_COUNTRY
FROM CPINVLIN INNER JOIN CPINVHDR ON INV_ITM_INV_NO = INV_NO
WHERE INV_ITM_ITM_NO='R006-A' OR INV_ITM_ITM_NO='R006-B' OR
INV_ITM_ITM_NO='R006-B5' OR INV_ITM_ITM_NO='R006-C' OR INV_ITM_ITM_NO='R005-A' OR INV_ITM_ITM_NO='R005-B' OR
INV_ITM_ITM_NO='R005-B5' OR INV_ITM_ITM_NO='R005-C' AND INV_ITM_INV_DATE>=20200101 AND INV_ITM_INV_DATE<=20220331

What logic would I need to just pick up existing orders that have not been invoiced?  Maybe a WHERE invoice # = '0'?

A - Records are created in the Invoice Header (CPINVHDR) and Invoice Line Item (CPINVLIN) after the invoice has posted. To get uninvoiced orders, you will have to query on the Order Header (CPORDHDR) and Order Line Item (CPORDLIN). Your query will be much the same, but using the Order fields instead of the Invoice fields. Also, invoices that are posted are not removed from the Order files. The status of the order is just changed from Open or Invoiced to Posted. So, you might be better off just starting with the Order files.

So you need to get your data FROM CPORDLIN INNER JOIN CPORDHDR ON LINE_ITM_ORDER_NO = ORDER_NO. If you only want the order that has not been invoiced, then filter on ORDER_SELECTION_CODE IN (‘C’,’I’,’S’). Just a little bit of an explanation on ORDER_SELECTION_CODE for you to adjust your own preferences -- this column can have the following possible values:
  • C – Order is complete
  • I – Order is incomplete or in middle of editing
  • S – Order is selected
  • X – Order is invoiced
  • N – Order is invoiced not OK (a temporary status before user answer “Are Invoice Printed OK”)
  • Z – Order is posted (history)

EMK

    • Related Articles

    • How to Retrieve Tracking Number for an Order from Notes

      Release Date: 12/13/2017 Q - Can you let me know how the tracking number is stored in Elliott so I can use query to retrieve the information? A - Regarding how Elliott stores tracking numbers, please refer to the following Knowledge Base article: ...

    • CPOPNSSN Customer Order Processing Open Orders by Salesman Report

      Open Orders by Salesman Report Application Overview This report featuring only open orders can be used as a sales analysis auditing tool, measure productivity, and as a customer service tool. You can print the report for a single salesman or a range ...

    • How to Retrieve Open Purchase Orders Through SQL SELECT Statement

      Released Date: 9/11/2017 Q - How do I retrieve an Elliott purchase order from a third party system through ODBC or ADO.NET? A - If you only need the purchase order header, you can use the following SELECT statement: SELECT * FROM POORDHDR WHERE ...

    • CP1700 Customer Order Processing Invoice History Inquiry

      Invoice History Inquiry Application Overview This function gives you fast and easy access to all the necessary information to provide customer service for posted invoices on file for a customer. It allows you to quickly access and display a customer ...

    • Open Order / Invoice History Inquiries GUI Windows Show No Column or Data

      Q - In the following areas I can access two special functions "Open Order Inquiry" (Alt-O) or "Invoice History Inquiry" (Alt-V): COP Sales Desk A/R Customer File Maintenance or Inquiry COP Order Entry However, when some of my users try to access ...