Show available qty across multiple sites on CO line
I’m trying to create a custom field on Customer Order Line which returns the sum of total Available qty across multiple sites. We have parts in stock on multiple sites so the sales team would like to see, when raising an order on the ‘main’ sales site, if there is available stock on any of the other sites in the same company.
This works when running a query in PL/SQL ...
(select sum(IFSapp.Inventory_Part_In_Stock_API.Get_Inventory_Quantity (contract,part_no,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 inventory_part_in_stock IPIS where ipis.CONTRACT IN ('ABC','ABD') AND ipis.PROJECT_ID is null AND IPIS.part_no = colc.part_no)
… but when adding as a custom field (even when only joining on part number - IPIS.part_no = :part_no) the field only returns the available qty in the site the Customer Order is raised on.
Not sure if that makes sense!
Any thoughts/suggestions/alternate approaches would be appreciated.
Thanks
Linda
Page 1 / 1
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
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.
(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
Hi Linda,
Its closer to option 2 but the view should be inventory_part_in_stock_total instead of inventory_part_in_stock and the key variables need to be passed into the right places. I don’t have access to an apps 9 database to check the function definition but in a later version the query would look like this.
Note I had to remove some of the variables from your original select from my example here as the method in my environment did not have mappings for them. I have included code comments in the above example to show the column names. You would need to find the function definition in your database and just pass through the 9 keys below into the appropriate locations.
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?
