Solved

Lobby Data Warehouse Location

  • 19 November 2020
  • 6 replies
  • 230 views

Userlevel 3
Badge +7

Good morning,

 i am trying to get a column in my data source that shows the warehouse location of the Avail Qty from the Supply Site on a Distribution Order. I was able to pull the Avail Qty from the Supply site just need to find a way if possible to pull the Warehouse the Avail Qty is in. thank you in advance

 

Avail Qty Column SQL

&AO.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand(SUPPLY_SITE,PART_NO,'*')

 

Screen Shot of Preview Screen

 

icon

Best answer by paul harland 19 November 2020, 21:38

View original

This topic has been closed for comments

6 replies

Userlevel 7
Badge +24

Try adding a column containing this:

 

SELECT listagg(warehouse, '^') WITHIN GROUP (ORDER BY 1) FROM
(SELECT warehouse
FROM &AO.inventory_part_in_stock i
WHERE i.part_no = DISTRIBUTION_ORDER.PART_NO
AND i.contract = DISTRIBUTION_ORDER.SUPPLY_SITE
AND (i.QTY_ONHAND - i.QTY_RESERVED) > 0
GROUP BY warehouse)

Userlevel 3
Badge +7

i get a error message that says missing expression

Userlevel 7
Badge +24

try putting that whole thing in parentheses ( )

Userlevel 3
Badge +7

@paul harland 

 That worked, below is how it is now

Now for the fun part to try to figure out how to exclude warehouses in the condition portion 

 

(SELECT listagg(warehouse, '^') WITHIN GROUP (ORDER BY 1) FROM (SELECT warehouse FROM &AO.inventory_part_in_stock I WHERE i.part_no = DISTRIBUTION_ORDER.PART_NO AND i.contract = DISTRIBUTION_ORDER.SUPPLY_SITE AND (i.QTY_ONHAND -i.QTY_RESERVED) > 0 GROUP BY warehouse))

Userlevel 7
Badge +24

(SELECT listagg(warehouse, '^') WITHIN GROUP (ORDER BY 1) FROM (SELECT warehouse FROM &AO.inventory_part_in_stock I WHERE i.part_no = DISTRIBUTION_ORDER.PART_NO AND i.contract = DISTRIBUTION_ORDER.SUPPLY_SITE AND (i.QTY_ONHAND -i.QTY_RESERVED) > 0

and warehouse not in ('A1','B2','C3')

GROUP BY warehouse))

Userlevel 3
Badge +7

@paul harland 

 That works in the column section and it wont list the exluded warehouse, what i am trying is to get it in the SQL Section where it wont even pull the Distro Order at all  below is my SQL Condition

 

(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 SUPPLY_SITE = 'C0283' and &AO.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Onhand(SUPPLY_SITE,PART_NO,'*')>0 
 

This gives me a list of all DO’s that are fillable but some warehouses are not true inventory locations that we can use to send to other sites