Oracle Inventory, Historical Inventory Values

Update: 06.May.2013

Recently we received few comments about the inventory values generated using the below hack not matching the transaction historical summary report values (R12). We had gone through a series of exercises to evaluate such claims and confirmed that all those comments were valid. Below are the few possible explanations.

Though both the above said reports call the same API to populate inventory details, parameters passed into the API are slightly different.

For the report “Inventory Value By Subinventory” the API is called like following

 CST_Inventory_PUB.Calculate_InventoryValue( 
      p_api_version => 1.0,
      p_init_msg_list => CST_Utility_PUB.get_true,
      p_organization_id => :P_ORG_ID,
      p_onhand_value => 1,
      p_intransit_value => 1,
      p_receiving_value => 0,
      p_valuation_date => to_date(:P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS'),
      p_cost_type_id => :P_COST_TYPE_ID,
      p_item_from => :P_ITEM_FROM,
      p_item_to => :P_ITEM_TO,   
      p_category_set_id => :P_CATEGORY_SET,
      p_category_from => :P_CAT_FROM,
      p_category_to => :P_CAT_TO,
      p_cost_group_from => NULL,
      p_cost_group_to => NULL,
      p_subinventory_from => :P_SUBINV_FROM,
      p_subinventory_to => :P_SUBINV_TO,
      p_qty_by_revision => :P_ITEM_REVISION,
      p_zero_cost_only => :P_ZERO_COST,   
      p_zero_qty => :P_ZERO_QTY,
      p_expense_item => :P_EXP_ITEM,
      p_expense_sub => l_exp_sub,
      p_unvalued_txns => :P_UNCOSTED_TXN,
      p_receipt => 1,
      p_shipment => 1,   
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data
    );  

Parameter “p_intransit_value => 1” makes the entire scenario different from “Transaction Historical Summary” as the inventory values are calculated against the quantities including the quantities in transit also. Further, for Average cost organizations, the report calculates the values against the current date item costs (We are trying to get an explanation for the same from multiple oracle communities, including communities.oracle.com)

Further, the rollback date column, unless entered as cutoff date 23:59:59 (eg: 31-dec-2012 23:59:59) always rolls back to 31-dec-2012 00:00:00, thus not picking up lines whichever were processed later for the entered date.

On the other hand, Transaction Historical Summary report calls the API like following

CST_Inventory_PUB.Calculate_InventoryValue(
     p_api_version => 1.0,
     p_init_msg_list => CST_Utility_PUB.get_true,
     p_organization_id => :P_org_id ,
     p_onhand_value => 1,
     p_intransit_value => NULL,
     p_receiving_value => 0,
     p_valuation_date => l_hist_date,
     p_cost_type_id => NULL,
     p_item_from => :p_item_lo ,
     p_item_to => :p_item_hi ,
     p_category_set_id => :p_cat_set_id ,
     p_category_from => :p_cat_lo,
     p_category_to => :p_cat_hi,
     p_cost_group_from => :p_cg_lo,
     p_cost_group_to => :p_cg_hi,
     p_subinventory_from => :p_subinv_lo,
     p_subinventory_to => :p_subinv_hi ,
     p_qty_by_revision => NULL,
     p_zero_cost_only => NULL,
     p_zero_qty => NULL,
     p_expense_item => NULL,
     p_expense_sub => NULL,
     p_unvalued_txns => 0,
     p_receipt => NULL,
     p_shipment => NULL,
     x_return_status => l_return_status,
     x_msg_count => l_msg_count,
     x_msg_data => l_msg_data
  );

Here the parameter “p_intransit_value => NULL” is set as NULL, thus the quantities in transit are not calculated. Further, the historical average costs are picked up for the transactions (material costs)

Further the rollback date is always expected to be entered like “31-dec-2012 23:59:59” in order to include the all the material transactions happened on the date.

So the in-transit quantity differences, item costs and the material transactions happened within the cutoff date time frame creates the variances what the user see with both the reports.

We had done the exercises more than few dozen times to reach to these unconfirmed conclusions. If as an experienced Oracle application user, dealing with Oracle inventory has any other explanations, Please, come ahead and we will amend the post with your valued inputs.

 

Requirement details: Oracle provides multiple standard reports to generate Inventories values on specified dates and depending upon the volume of transactions all these reports generate thousands of lines details to reach to such cut off date inventory values. Our requirement was to provide the auditors a quick view to inventory values as on end of each month, thus the entire development of below provided solution started.

How it works

Folder view supported form module developed by us calls a stored procedure, generates the rows into GLOBAL TEMPORARY tables and a stored function sum ups the material value and inserts into a local table (Base table with the form module)

For all the closed inventory periods, an insert statement picks up the values for the cut off dates from the view“cst_period_summary_v” and the months which are not already in the “cst_period_summary_v” view are populated by calling PVT APIs what we have heavily customized.

We achieved our goal by disabling the gather table statistics which are called from the PUBLIC API, then exclusively calling a COMMIT from the primary loop initiated by “populate history values” button press, thus indirectly flushing out the GLOBAL TEMPORARY TABLES for next run.

We hope this solution will be useful for organizations around the world who are running Oracle ERP 12.x.xx suites.

Oracle clearly states the API CST_Inventory_PUB  is private and shouldn’t be called by the users exclusively from any other procedures or packages. Please refer to :“Using Oracle API CST_Inventory_PUB Package ID 847101.1]”

Hence you are going to use the solution provided by us at your own risk (Ironically, this PVT API is nothing more than few select statements based on different parameters passed in)

Here we are providing a solution to populate inventory values until “last month” by a mere mouse click

image

The entire solution could be downloaded from here

Regards,

Admin

8 Responses to Oracle Inventory, Historical Inventory Values

  1. Subrahmanyeswara Rao says:

    A wonderful one n realistic

  2. windows7bugs says:

    Thank you Mr. Rao for your comment. We have developed a “kind” of Retailing application for Order Management, completely based on PUBLIC APIs also. We will “one day” provide the entire solution to public!

    Regards,

  3. venkatesh says:

    Dear Rajesh ,

    Thanks a ton for the solution. I have used your solution and check for a few of inventory orgs for my requirement. It matched in many cases but there were some orgs where it was differing slighttly. On checking item by item i have found that i did not match for a couple of items.

    I had run the standard historical summary report inventory wise and also the API that was provided through your solution in the same way. But i am not able to debug , why the standard report is differing when i am running both providing the same values. Any help in this matter would be highly appreciated.

    Thanks
    Venkatesh

    • venkatesh says:

      apologies , i had run the report sub inventory wise. My organization uses Average costing method. Report Option is Balance.

    • windows7bugs says:

      Hello Venkatesh

      Thank you for the comment. We will not be in a position to assess the differences what you have came across at specific setups. Meanwhile, would like to know, whether the differences are occurring between standard reports and our API driven custom application, along with the ERP version(s). Please specify the exact version numbers.

      regards,

      for Windows7bugs

      rajesh

  4. venkatesh says:

    Dear Rajesh,

    Thanks for your comment.

    Versions are as below
    —————————–

    RDBMS : 11.2.0.3.0
    Oracle Applications : 12.1.3

    I believe that the standard report also calls the same API when the Report is run subinventory wise.
    But when i run the standard Report for a specific Item , i am getting a different value and using the custom solution.

    I am no sure whether any further massaging of data is being done in the rdf.

    I have tried launching the Standard API by passing the values from back end and found that same data is getting populated in the temporary tables of the standard report. This data is same which is getting generated / populated in the custom solution temporary tables. But , some how the cost is not considered for that particular item and hence value is NULL in the standard report and in the custom report , the cost is considered and hance value is different.

    Regarding Checking of the Exact parameter values passed in standard report:
    ______________________________________________________________________________

    Is there any way i can find out what are the parameter values that are being passed to the Calculate_InventoryValue API.

    I had tried to put sutonomous insert command in the standard API (test instance) and tried to check exactly what values are being passed.
    Unfortunately no values are getting inserted into the table when the report is launched from front end.

    Any suggestion or help in this regard would be of great help to me.

    Thanks and Regards,
    Venkatesh

    • windows7bugs says:

      Dear Venkatesh

      As I have explained with the post, there are two values generated using the same API, one for inventory values and the other one for the historical values. Both differ slightly with the approach, if you read again, you will find, for the inventory values, items in transit are considered and current average cost is taken, Further, the rollback date column, unless entered as cutoff date 23:59:59 (eg: 31-dec-2012 23:59:59) always rolls back to 31-dec-2012 00:00:00, thus not picking up lines whichever were processed later for the entered date.

      Please read the post again, I have given the exact parameters expected for both inventory values and historical values. I can do one thing, test the mentioned differences against one of the subinventories and POST you whether such differences are happening at our end.

      You may reach me at w 7 b u g s at g m a i l with screenshots of report parameter details.

      regards,

      rajesh

  5. Princy says:

    Can I have the solution please???

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: