Oracle developer 10g, developer 6i default values for columns using :PARAMETER.name

We just came across a peculiar situation. Recently we had developed a new approach towards developing new applications for Oracle EBS instance.

We do the following

Develop the prototype using developer 6i forms (hacked and patched for Windows 7 & Windows 8). The only change we make with the developer 6i is changing the “coordinate system” property from points to “inches”

image

This approach gives us rapid development scenarios, as we don’t have to upload the form module to instance top and compile it and try it against the TEST instances.

All we are doing is, once the complete application is tested on developer 6i, a copy of the same opened with Developer 10g forms, and copying the objects following the following sequence

  1. Window( s )
  2. Canvases
  3. Blocks
  4. Procedures & functions
  5. Parameters (if any)

Once mapping the PARAMETERS with PROFILE values, the form is ready for APPS instance testing (99% of the times everything is already tested, so a failure is seldom experienced)

Now, coming to one actual issue we are still struggling with is, using the :PARAMETER.xyz, :PARAMETER.abc as initial values for block elements.

The hacked developer 6i does not populate the initial value from :PARAMETER.xyz or :PARAMETER.abc during run time. ie, the first record (record orientation: forms) will not show the already populated values for the columns, however, the very next record(a delete does it) starts showing the :PARAMETER.xyz, abc values being populated to columns wherever they are set as initial values!!!

Do you have solutions? Please let us know with the comment section

Update 19.11.2012

With developer forms 6i, we loaded the values to column through the WHEN-NEW-FORM-INSTANCE like following

:PARAMETER.ORGANIZATION_ID := FND_PROFILE.VALUE(‘org_id’);
:PARAMETER.ORG_ID := FND_PROFILE.VALUE(‘mfg_organization_id’);
:PARAMETER.USERNAME := FND_PROFILE.VALUE(‘username’);

then, explicitly assigned the values to block columns like

:BLOCK.COLUMN1 := :PARAMETER.ORGANIZATION_ID;

in addition to setting up the initial value for the columns using :PARAMETER.xyz

Looks like the stuff are in place now :)

With Developer 10g, this issue has much easier to fix. All we had to do was to change the case of coding like following:

:parameter.organization_id := fnd_profile.value(‘org_id’);
:parameter.org_id := fnd_profile.value(‘mfg_organization_id’);
:parameter.username := fnd_profile.value(‘username’);

To

:PARAMETER.ORGANIZATION_ID := FND_PROFILE.VALUE(‘org_id’);
:PARAMETER.ORG_ID := FND_PROFILE.VALUE(‘mfg_organization_id’);
:PARAMETER.USERNAME := FND_PROFILE.VALUE(‘username’);

and without any further hacks, the 10g forms started showing initial values.

If you experienced the same, have a solution, please provide us the same with the comment section

regards,

admin

Oracle Inventory, Historical Inventory Values

Update: 11.Jan.2018

We’ve rectified the minor inventory value differences between the API generated values & the standard report “Inventory Value Report – by Subinventory” as due to the item costs those are used.

For average costing organizations, the report always generates the cut off date values again the current costs, instead of the costs those are populated to the intermediate tables.

IC_TOTAL_COST, the column that shows the cost as on cut off date is using the following formula

NVL(CIQT.rollback_qty,0) * DECODE(NVL(SEC.asset_inventory,1), 1, NVL(CIC.item_cost,0), 0) *:P_EXCHANGE_RATE IC_TOTAL_COST

following the same formula with the private API function has generated the same value as per the inventory report.

The updated API is packaged once again and available to download from here 

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