Solved

LOBBY WAREHOUSE

  • 20 November 2020
  • 6 replies
  • 218 views
R
Rank
Userlevel 3
Badge +7

Good Morning,

 I was wondering if there was a way to view only certin warehouses in a data set for Distribution Orders, with help from this forum i was able to see the warehouse locations in the columns, now i was wondering if i could get it to only pull from specific warehouses. below is my Condition SQL and Column SQL along with screen shots, i greatly appreciate everyones help 

Condition SQL 

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

Column SQL

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

 

icon

Best answer by paul harland 20 November 2020, 16:54

View original
This topic has been closed for comments

6 replies

paul harland
Rank
Userlevel 7
Badge +24

The difficulty here is that IFS would let you store the same item in multiple warehouses, so it is difficult to classify a distribution order by warehouse.

In your setup, can it be assumed that a part is only stored in one warehouse?  If so, this becomes a lot easier.

R
Rank
Userlevel 3
Badge +7

Some Parts are stored in multiple warehouses, but some warehouses are used for the repair center and can not have parts taken from to send to other sites. 

paul harland
Rank
Userlevel 7
Badge +24

ok understood

So what is the objective of the lobby ?

R
Rank
Userlevel 3
Badge +7

Trying to see Distribution Orders that can be filled and sent to other sites that need the parts, trying to simplify things for my warehouse personnel 

paul harland
Rank
Userlevel 7
Badge +24

ok

Then i would suggest you add something like this into your Condition field

AND

(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)) in  ('A1','B2','C3')

(where those 3 are the warehouses you want to see).

 

However - you still have the issue of multiple warehouses.  For example the listagg might return “A1^B2” which is not either A1 or B2.  In which case you would lose that DO.

To be honest it would be difficult to give you a reliable solution to that issue over this forum, without having a lot more visibility of how your data looks.  

 

R
Rank
Userlevel 3
Badge +7

Paul,

 That seems to work i will run a check with the manual way that we do things now to make sure its not missing any DO’s. i have one last thing i am trying to exclude and dont think it will be possible or i am doing it wrong but want to eliminate items with a availability control of inventory discrepancy below is what i have tried in the Condition section

 

Inventory_Part_In_Stock_API.Get_Availability_Control_Id (contract_ IN VARCHAR2,part_no_ IN VARCHAR2,configuration_id_ IN VARCHAR2,location_no_ IN VARCHAR2,lot_batch_no_ IN VARCHAR2, serial_no_ IN VARCHAR2,eng_chg_level_ IN VARCHAR2,waiv_dev_rej_no_ IN VARCHAR2,activity_seq_ IN NUMBER) NOT IN ‘INVENTORY DISCREPANCY’Inventory_Part_In_Stock_API.Get_Availability_Control_Id (contract_ IN VARCHAR2,part_no_ IN VARCHAR2,configuration_id_ IN VARCHAR2,location_no_ IN VARCHAR2,lot_batch_no_ IN VARCHAR2, serial_no_ IN VARCHAR2,eng_chg_level_ IN VARCHAR2,waiv_dev_rej_no_ IN VARCHAR2,activity_seq_ IN NUMBER) NOT IN ‘INVENTORY DISCREPANCY’

Terms & ConditionsCookie settings