Solved

Lobby Data Source How to Get Avail QTY Locations

  • 12 November 2020
  • 9 replies
  • 152 views

Badge +5

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'

icon

Best answer by johnw66 13 November 2020, 20:37

@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

View original

9 replies

Userlevel 5
Badge +12

Hi @Rpauley 

Have a look at the following Tips & Tricks videos under the Lobby section:

http://www2.ifsworld.com/apps-instr-videos/default.htm

 

Also, the help files:

https://docs.ifs.com/techdocs/

 

Once the page opens search for Lobby.

 

John

Userlevel 5
Badge +12

In relation to your SQL 

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’)

 

 

Userlevel 3
Badge +5

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.

Badge +5

@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%')

Badge +5

@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

Userlevel 5
Badge +12

@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

Badge +5

Thank you I will try shortly we lost power I will let you know if it works, thank you for the help

Badge +5

@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

Badge +5

@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

 

 

Reply