I am trying to see Distribution Orders that are open and fillable, i need to see the locations that the Available Qty is in. If this is possible is there also a way to exclude certain locations, i am fairly new to SQL’s and IFS lobby’s.
Below is my current SQL
OBJSTATE = (select &AO.CUSTOMER_ORDER_LINE_API.FINITE_STATE_ENCODE__('Released') from dual) and CONTRACT = 'C0283' and BUY_QTY_DUE > 0 and &AO.Reserve_Customer_Order_API.Get_Available_Qty(NVL(supply_site,contract), NVL(part_no,catalog_no),configuration_id,order_no,line_no,rel_no,line_item_no,supply_code_db,part_ownership_db,owning_customer_no,project_id,condition_code,NULL,'TRUE') >= BUY_QTY_DUE and AUTHORIZE_CODE <> 'E380748' AND AUTHORIZE_CODE <> 'EST' and ORDER_ID = 'NO'
Best answer by johnw66
Sorry, been a mad day.
I tried your query on my database and as you stated using NOT IN …. displayed nothing
Therefore, commented out the line
and LOCATION_NO NOT IN ('GMDT-REPAIR-IN', 'TES-RSTK-GFP', 'ESTW-RSTK-CFP', 'RESTOCK%')
and ran the query and sure enough data was returned. This was because the field LOCATION_NO is null.
So, amended the entry to:
and (LOCATION_NO NOT IN ('GMDT-REPAIR-IN', 'TES-RSTK-GFP', 'ESTW-RSTK-CFP', 'RESTOCK%') OR LOCATION_NO IS NULL)
This time the data was returned.
Please retry and advise.