How to Retrieve Open Orders That Are Not Invoiced Through Query
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