Show available qty across multiple sites on CO line

Hi Linda,

I think this might be happening because inventory_part_in_stock view is filtered on the user granted site as shown below. You could try to use the inventory_part_in_stock_total or inventory_part_in_stock_avail views instead which don’t have this filter.

Regards

Telha

Telha wrote:

Hi Linda,

 

I think this might be happening because inventory_part_in_stock view is filtered on the user granted site as shown below. You could try to use the inventory_part_in_stock_total or inventory_part_in_stock_avail views instead which don’t have this filter.

 

Regards

 

Telha

Thanks for the steer, Telha.  

I’ve given this a go but I’m still missing something somewhere.  

In the case of the part below, where there are two records (even though Available Qty only in one Location) the custom field returns qty 30.  Feels like I’m missing a ‘join’ somwhere as it’s doubling up.  

Hi Linda,

Yes this is because the query will run the Get_Inventory_Quantity function for every row of the inventory_part_in_stock_total table. However as this function calculates the qty using fixed values being passed in its getting 15 back for both the rows above.

There is a couple ways to fix this, one way is to pass in the values from the view into the function instead of the fixed values or you could just select from dual by passing in the contract, part and configuration id from the customer order line as shown below. Selecting from dual means that the function will only be called once per customer order line. 

SELECT   IFSapp.Inventory_Part_In_Stock_API.Get_Inventory_Quantity(colc.contract,colc.part_no,colc.configuration_id,'AVAILTRANSIT','NOT EXPIRED','NETTABLE','CONSIGNMENT','COMPANY OWNED', NULL,NULL,NULL,NULL,NULL,NULL,'PICKING','F','MANUFACTURING','PALLET','DEEP','BUFFER','DELIVERY','SHIPMENT',NULL,NULL,NULL,NULL,'TRUE','FALSE',NULL,'*')
from dual

Alternatively the custom field can also be set to type Expression to just use the function as shown below.

Regards

Telha

Hi @Telha 

If I pass in the site from the CO, won’t I only pick up the ‘available’ qty from that site?  I’m trying to include ‘available’ from the other two sites as well.  

There is a couple ways to fix this, one way is to pass in the values from the view into the function instead of the fixed values or you could just select from dual by passing in the contract, part and configuration id from the customer order line as shown below. Selecting from dual means that the function will only be called once per customer order line. 

Sorry for the questions!

Linda

Hi Linda,

Yes missed that this would limit it by site. This means that you will need to go with the option of passing in all of the keys from the inventory_part_in_stock_avail view into the function. These are the 9 values below and if these are passed into the function then the rows should be calculated correctly.

CONFIGURATION_ID, CONTRACT, LOCATION_NO, PART_NO, ENG_CHG_LEVEL, SERIAL_NO, LOT_BATCH_NO, WAIV_DEV_REJ_NO, ACTIVITY_SEQ

Regards

Telha

Thanks, Telha.  Just to clarify are you saying your suggested ‘from dual’ should be this... 

SELECT   IFSapp.Inventory_Part_In_Stock_API.Get_Inventory_Quantity(colc.contract,colc.part_no,CONFIGURATION_ID, CONTRACT, LOCATION_NO, PART_NO, ENG_CHG_LEVEL, SERIAL_NO, LOT_BATCH_NO, WAIV_DEV_REJ_NO, ACTIVITY_SEQ, 'AVAILTRANSIT','NOT  EXPIRED','NETTABLE','CONSIGNMENT','COMPANY OWNED', NULL,NULL,NULL,NULL,NULL,NULL,'PICKING','F','MANUFACTURING','PALLET','DEEP','BUFFER','DELIVERY','SHIPMENT',NULL,NULL,NULL,NULL,'TRUE','FALSE',NULL,'*')
from dual

And/or that the custom field should be this...

(select
sum(IFSapp.Inventory_Part_In_Stock_API.Get_Inventory_Quantity(colc.contract,colc.part_no,CONFIGURATION_ID, CONTRACT, LOCATION_NO, PART_NO, ENG_CHG_LEVEL, SERIAL_NO, LOT_BATCH_NO, WAIV_DEV_REJ_NO, ACTIVITY_SEQ, 'AVAILTRANSIT','NOT  EXPIRED','NETTABLE','CONSIGNMENT','COMPANY OWNED', NULL,NULL,NULL,NULL,NULL,NULL,'PICKING','F','MANUFACTURING','PALLET','DEEP','BUFFER','DELIVERY','SHIPMENT',NULL,NULL,NULL,NULL,'TRUE','FALSE',NULL,'*'))
from inventory_part_in_stock IPIS
where ipis.CONTRACT IN ('ABC','ABD') AND 
      ipis.PROJECT_ID is null AND
IPIS.part_no = colc.part_no)

Linda

Thanks, Telha.  There’s certainly a lot for me to have a go at there.  Thank you so much for taking the time to engage.  

Linda

One final question, Telha, if you don’t mind. Inventory_Part_In_Stock_Total doesn’t have ‘available’ field so I’m going to need to do a calculation - onhand minus reserved minus in transit… I  think?

Linda