Skip to main content

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

 

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.

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. 

ok understood

So what is the objective of the lobby ?

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 

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.  

 

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