Skip to main content
Solved

On Hand Qty for a part

  • May 30, 2025
  • 4 replies
  • 102 views
E
Sidekick (Customer)
Forum|alt.badge.img+5

Hi, we want to implement Large Order Quantity check.

I would like to get some fields. Can someone please help me to get the right value in DB?

 

  1. On Hand Qty for a part, I found it in table IFSAPP.INVENTORY_PART_IN_STOCK_TAB, but it can have multiple rows because of receiving part. But I would like to get a sum amount of a part like IFS showing in page. Should I sum all the rows relate to the same Part_NO? Or I can get it from some other places?

  2. Dose Catalog_No in IFSAPP.CUSTOMER_ORDER_LINE_TAB is as same as Part_No?

Best answer by dinuka9

Hi,

For better understanding, couple of questions first.
This is under the topic "Warehouse Data Collection (WaDaCo)", is this about Wadaco or related to Customer orders (sales) ?
If this is related to Wadaco, what is the relevant Core Process ?


Answers -

  1. INVENTORY_PART_IN_STOCK_TAB has 10 keys, PART_NO is one of those keys. For an example if there are two Lot/Batches there will be two stock records with the same PART_NO but different LOT_BATCH_NO. If you want the all quantities for a part, yes you can get a sum of QTY_ONHAND from INVENTORY_PART_IN_STOCK_TAB.
    QTY_ONHAND includes the reserved quantities as well, so if you want the not-reserved/available quantities you need to get sum of (QTY_ONHAND - QTY_RESERVED).
     
  2. CATALOG_NO in CUSTOMER_ORDER_LINE_TAB refers to the Sales Part No, not the Inventory Part No. SALES_PART_TAB has both the CATALOG_NO (Sales Part No) and the PART_NO (corresponding Inventory Part No). Inventory Part No is same as the Master Part (PART_NO in PART_CATALOG_TAB).

 

Hope this helps.
If you need more clarifications, please share some more information about the exact requirement so that this can be redirected to the related business area experts.


Best Regards,
Dinuka.

    4 replies

    D
    Do Gooder (Employee)
    Forum|alt.badge.img+4
    • dinuka9
    • Do Gooder (Employee)
    • Answer
    • June 2, 2025

    Hi,

    For better understanding, couple of questions first.
    This is under the topic "Warehouse Data Collection (WaDaCo)", is this about Wadaco or related to Customer orders (sales) ?
    If this is related to Wadaco, what is the relevant Core Process ?


    Answers -

    1. INVENTORY_PART_IN_STOCK_TAB has 10 keys, PART_NO is one of those keys. For an example if there are two Lot/Batches there will be two stock records with the same PART_NO but different LOT_BATCH_NO. If you want the all quantities for a part, yes you can get a sum of QTY_ONHAND from INVENTORY_PART_IN_STOCK_TAB.
      QTY_ONHAND includes the reserved quantities as well, so if you want the not-reserved/available quantities you need to get sum of (QTY_ONHAND - QTY_RESERVED).
       
    2. CATALOG_NO in CUSTOMER_ORDER_LINE_TAB refers to the Sales Part No, not the Inventory Part No. SALES_PART_TAB has both the CATALOG_NO (Sales Part No) and the PART_NO (corresponding Inventory Part No). Inventory Part No is same as the Master Part (PART_NO in PART_CATALOG_TAB).

     

    Hope this helps.
    If you need more clarifications, please share some more information about the exact requirement so that this can be redirected to the related business area experts.


    Best Regards,
    Dinuka.

    E
    Sidekick (Customer)
    Forum|alt.badge.img+5
    • emma_buyer
    • Author
    • Sidekick (Customer)
    • June 2, 2025

    Hi,

    For better understanding, couple of questions first.
    This is under the topic "Warehouse Data Collection (WaDaCo)", is this about Wadaco or related to Customer orders (sales) ?
    If this is related to Wadaco, what is the relevant Core Process ?


    Answers -

    1. INVENTORY_PART_IN_STOCK_TAB has 10 keys, PART_NO is one of those keys. For an example if there are two Lot/Batches there will be two stock records with the same PART_NO but different LOT_BATCH_NO. If you want the all quantities for a part, yes you can get a sum of QTY_ONHAND from INVENTORY_PART_IN_STOCK_TAB.
      QTY_ONHAND includes the reserved quantities as well, so if you want the not-reserved/available quantities you need to get sum of (QTY_ONHAND - QTY_RESERVED).
       
    2. CATALOG_NO in CUSTOMER_ORDER_LINE_TAB refers to the Sales Part No, not the Inventory Part No. SALES_PART_TAB has both the CATALOG_NO (Sales Part No) and the PART_NO (corresponding Inventory Part No). Inventory Part No is same as the Master Part (PART_NO in PART_CATALOG_TAB).

     

    Hope this helps.
    If you need more clarifications, please share some more information about the exact requirement so that this can be redirected to the related business area experts.


    Best Regards,
    Dinuka.

    Thank you ​@dinuka9, that is very helpful. I am doing with Customer order (Sales), we have some rules to check whether the order is a large quantity order. We need to check every order line, actually the part relates to the order line.

    One more question, where can I find the logic how to create a temporary table? Like “INV_PART_AVAIL_SUM_QTY_TMP”

    D
    Do Gooder (Employee)
    Forum|alt.badge.img+4
    • dinuka9
    • Do Gooder (Employee)
    • June 3, 2025

    You can find the definition of INV_PART_AVAIL_SUM_QTY_TMP in OrderSupplyDemand.storage file.
    It is not actually a Oracle temporary table, it’s a Snapshot based table (indexed on sanpshot_id) where the data is managed through PLSQL business logic.

    If your requirement is for an actual oracle temporary table, you can refer custord_supp_dem_plan_tmp in the same file (definition starts with “TEMPORARY TABLE ”).

    E
    Sidekick (Customer)
    Forum|alt.badge.img+5
    • emma_buyer
    • Author
    • Sidekick (Customer)
    • July 1, 2025

    ​@dinuka9  I would like to get one field, but I could not find it in Oracle DB.

    I saw AVAILABLE QTY in Customer Order->Lines, it shows for every order line.

    I saw it is from view CUSTOMER_ORDER_JOIN / CUSTOMER_ORDER_LINE

    But I could not find it from view CUSTOMER_ORDER_LINE /CUSTOMER_ORDER_JOIN

     

    Can you please show me where I can find it?

    I want to get all lines for an order, check whether qty_order < qty_avaiable and set a filed for the order.

     

    Thank you.

    Terms & ConditionsCookie settingsAccessibility statement