Solved

LOBBY WAREHOUSE

  • 20 November 2020
  • 6 replies
  • 59 views

Badge +5

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

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.  

 

View original

6 replies

Userlevel 5
Badge +12

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.

Badge +5

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. 

Userlevel 5
Badge +12

ok understood

So what is the objective of the lobby ?

Badge +5

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 

Userlevel 5
Badge +12

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.  

 

Badge +5

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’

Reply