Skip to main content
Solved

LOBBY WAREHOUSE

  • November 20, 2020
  • 6 replies
  • 246 views
R
Sidekick
Forum|alt.badge.img+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))

 

Best answer by paul harland

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
    Did this topic help you find an answer to your question?
    This topic has been closed for comments

    6 replies

    paul harland
    Superhero (Employee)
    Forum|alt.badge.img+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
    Sidekick
    Forum|alt.badge.img+7
    • Rpauley
    • Author
    • Sidekick
    • 65 replies
    • November 20, 2020

    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
    Superhero (Employee)
    Forum|alt.badge.img+24

    ok understood

    So what is the objective of the lobby ?

    R
    Sidekick
    Forum|alt.badge.img+7
    • Rpauley
    • Author
    • Sidekick
    • 65 replies
    • November 20, 2020

    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
    Superhero (Employee)
    Forum|alt.badge.img+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.  

     

    E
    1 person likes this
    • E
      EvrFelipP
    R
    Sidekick
    Forum|alt.badge.img+7
    • Rpauley
    • Author
    • Sidekick
    • 65 replies
    • November 20, 2020

    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

    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