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'
Page 1 / 1
Hi @Rpauley
Have a look at the following Tips & Tricks videos under the Lobby section:
AUTHORIZE_CODE <> 'E380748' AND AUTHORIZE_CODE <> 'EST'
You would be better using:
AUTHORIZE_CODE NOT IN (‘E380748’, ‘EST’)
You would use the same sort of coding for Location No to exclude the sites Loc1, Loc2, Loc3
e.g. LOCATION_NO NOT IN (‘Loc1’, ‘Loc2’, ‘Loc3’)
Is it only your Quick Report that shall exclude some inventory locations or do you want the reservation logic in IFS to not see the locations too?
If you want the reservation logic etc. to not see them you can solve it with default Part Availability Control ID’s on some level in the Warehouse Navigator.
Then your SQL works as is.
@johnw66
When i updated using your suggestion now it doesn't show any available DO’s to fill, how ever i do know i have some to fill that the Avail Qty is in a correct location, any suggestions as to why this is
(OBJSTATE = (select &AO.DISTRIBUTION_ORDER_API.FINITE_STATE_ENCODE__('Planned') from dual) or OBJSTATE = (select &AO.DISTRIBUTION_ORDER_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 NOT IN ('E380748', 'EST') and ORDER_ID = 'NO' and LOCATION_NO NOT IN ('GMDT-REPAIR-IN', 'TES-RSTK-GFP', 'ESTW-RSTK-CFP','RESTOCK%')
@Tomas Ruderfelt
Thank you but unfortunately they don't want to use Availability control on parts, i would like the lobby element to not see locations so my warehouse knows exactly how many DO’s they can fill and push out to other sites. thank you
@Rpauley
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.
John
Thank you I will try shortly we lost power I will let you know if it works, thank you for the help
@johnw66
Yes this did work, thank you for the assistant, since i am using the Customer Order View can add a navigation to the Distro Order Screen from here not a big deal if it cant be done. thank you again for all of your help hope you have a good weekend
@johnw66
Sir i finally had a chance to play around with this and although the data pulls in the preview, when i create the counter for the lobby, and click into it, it is actually not pulling the correct data it is still pulling from all locations, i am thinking that it has something to do with the Location No Column in the Data being blank any suggestions would be helpful
