Solved

Show available qty across multiple sites on CO line

  • 16 January 2024
  • 9 replies
  • 87 views
PRODQ
Rank
Userlevel 6
Badge +13
  • PRODQ
  • Hero (Customer)
  • 355 replies

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

icon

Best answer by Telha 17 January 2024, 16:13

View original
Linda

9 replies

T
Rank
Userlevel 2
Badge +8

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

PRODQ
Rank
Userlevel 6
Badge +13

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.  

 



 

 

Linda
T
Rank
Userlevel 2
Badge +8

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

PRODQ
Rank
Userlevel 6
Badge +13

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

Linda
T
Rank
Userlevel 2
Badge +8

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

PRODQ
Rank
Userlevel 6
Badge +13

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

Linda
T
Rank
Userlevel 2
Badge +8

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.

 

SELECT
sum(IFSapp.Inventory_Part_In_Stock_API.Get_Inventory_Quantity
      (CONTRACT,                      --contract_
      PART_NO,                          --part_no_
      CONFIGURATION_ID,      --configuration_id_
      'AVAILTRANSIT',               --qty_type_
      'NOT EXPIRED',                --expiration_control_
      'NETTABLE',                      --supply_control_db_
      'CONSIGNMENT',             --ownership_type1_db_
      'COMPANY OWNED',       --ownership_type2_db_
      NULL,                                 --ownership_type3_db_
      NULL,                                --ownership_type4_db_
      NULL,                                --ownership_type5_db_
      NULL,                                --ownership_type6_db_
      NULL,                                --owning_customer_no_
      NULL,                               --owning_vendor_no_
      'PICKING',                        --location_type1_db_
      'F',                                     --location_type2_db_
      'MANUFACTURING',     --location_type3_db_
      'PALLET',                        --location_type4_db_
      LOT_BATCH_NO,          --lot_batch_no_
      SERIAL_NO,                  --serial_no_
      ENG_CHG_LEVEL,       --eng_chg_level_
      WAIV_DEV_REJ_NO,   --waiv_dev_rej_no_
      'TRUE',                           --include_standard_
      'FALSE',                         --include_project_
      ACTIVITY_SEQ,            --activity_seq_
      NULL,                            --project_id_
      LOCATION_NO,           --location_no_
      NULL,                           --order_issue_control_db_
      NULL,                           --automat_reserv_ctrl_db_
      NULL,                           --manual_reserv_ctrl_db_
      NULL,                           --condition_code_
      'INVENTORY',              --unit_of_measure_type_
      NULL,                           --expiration_control_date_
      NULL,                           --warehouse_id_
      NULL,                           --ignore_this_avail_control_id_
      NULL                            --handling_unit_id_
      )
)
from inventory_part_in_stock_total IPIS
where ipis.CONTRACT IN ('ABC','ABD') 
AND ipis.PROJECT_ID is NULL 
AND IPIS.part_no = colc.part_no

 

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.

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

 

Hope that helps.

 

Regards

 

Telha

PRODQ
Rank
Userlevel 6
Badge +13

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

 

Linda
PRODQ
Rank
Userlevel 6
Badge +13

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

 

Linda

Reply


Terms & ConditionsCookie settings