Skip to main content
Solved

Using a data source to show demand against stock in specific location

  • September 9, 2024
  • 6 replies
  • 103 views

Forum|alt.badge.img+2

Hi,

       I am trying to create a lobby element to show demand vs current stock in specific locations instead of whole site. I have a data source that shows all current demand for site and to filter when demand qty > beginning onhand stock. I can’t figure out how to direct the search just to specific locations or warehouse.

Have tried adding various API.get info arguments but can’t seem to get it right.

 

Looking to have a value that will show Demand Qty > Stock in “X” location.

 

Any help will be greatly appreciated.

Best answer by COVJAMIEB

Hello,

You could add the following into the condition

DEMAND_QTY=(select sum(qty_onhand) from &AO.inventory_part_in_stock where part_no=mrp_part_supply_demand_all.part_no and location_no = 'X')

However, I would suggest you create an IAL to fetch this data. You can then use the IAL as the view.

If you dont want to create the IAL then I would suggest getting a Technical Person to create a new API procedure to fetch the qty_onhand for the required locations.

View original
Did this topic help you find an answer to your question?

6 replies

COVJAMIEB
Hero (Partner)
Forum|alt.badge.img+11
  • Hero (Partner)
  • 202 replies
  • Answer
  • September 10, 2024

Hello,

You could add the following into the condition

DEMAND_QTY=(select sum(qty_onhand) from &AO.inventory_part_in_stock where part_no=mrp_part_supply_demand_all.part_no and location_no = 'X')

However, I would suggest you create an IAL to fetch this data. You can then use the IAL as the view.

If you dont want to create the IAL then I would suggest getting a Technical Person to create a new API procedure to fetch the qty_onhand for the required locations.


Forum|alt.badge.img+2
  • Author
  • Sidekick (Customer)
  • 7 replies
  • September 10, 2024

Hi,

       I have added your suggestion to my data source but sadly it did not work. Just gave me back a blank preview.

                         I will look into creating IAL but am new to this so will seek some assistance from our technical expert.

 

Thank you very much for your support :)


COVJAMIEB
Hero (Partner)
Forum|alt.badge.img+11
  • Hero (Partner)
  • 202 replies
  • September 10, 2024

Did you change the = to >


Forum|alt.badge.img+2
  • Author
  • Sidekick (Customer)
  • 7 replies
  • September 10, 2024

Yes I did :)

 

from dual) and DEMAND_QTY > (select sum(qty_onhand) from &AO.inventory_part_in_stock where part_no=mrp_part_supply_demand_all.part_no and location_no = 'LRB1' or location_no = 'HCB1') 

 

Tried with only one location defined also but still a blank return.


COVJAMIEB
Hero (Partner)
Forum|alt.badge.img+11
  • Hero (Partner)
  • 202 replies
  • September 10, 2024

Is it returning blanks because there are no records where the demand qty > available stock?

I can use the select statement as shown below.

 


Forum|alt.badge.img+2
  • Author
  • Sidekick (Customer)
  • 7 replies
  • September 10, 2024

Got it! I was looking at it from the wrong way, change the > to < and works perfect!

 

Thank you so much!!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings