Oracle Inventory - Get Item Cost

Below is the Oracle Standard API to fetch the Item cost.
Having a look at API We can see that the derivation is in various stages. Based on the Cost Method.

Standard cost method id is 1
Average cost method id is 2

-----
If the costing method is 1 the costs come from cst_item_costs
If not the costs come from cst_quantity_layers which will be based on the cost group for the item.

Pls download the attached code for more info.
Download File

PA Patchset level.

To find the current PA Patch set level you are on use the attached script.
Download File

Oracle SQL - GREATEST and LEAST

To select max from 2 columns
select greatest(4,2) from dual

To select min from 2 columns
select least(1,3) from dual

Search for Quote character in SQL

Ever wanted to find strings which contain Quote character. Using CHR(39) is the way.

Ex:
select table_name||CHR(39) from all_tables where table_name like '%'||CHR(39)||'%' and rownum < 5;

I am here trying to find table names which contain a quote char.

Fetch Environment variables in PL/SQL

How to get the value of environment variables in PL/SQL code
-------------------------------------------------------------
1.For eBusiness Suite, you can also look at fnd_env_context table in your pl/sql code to get the path to the custom top variables as well.


2.A well coded program avoids hard-coding of paths and uses variables. If you want to get the value of unix environment variables in your programs, you can use the DBMS_SYSTEM.GET_ENV procedure. For applying it in E-Business Suite, you would need to make sure that the variables are defined in RDBMS $ORACLE_HOME/custom$CONTEXT_NAME.env

For example, I defined environment variable CUSTOM_TOP in a new file $ORACLE_HOME/custom$CONTEXT_NAME.env. This file is called automatically by your database environment file $CONTEXT_NAME.env.

To test, whether the value appears simply do this in an sql session:
SQL> set autoprint on
SQL> var CUSTOM_TOP varchar2(255)
SQL> exec dbms_system.get_env('CUSTOM_TOP',:CUSTOM_TOP);

PL/SQL procedure successfully completed.

XML Publisher/Template Builder compile error

Symptoms:
When building a template or loading an BI Publisher, (XML Publisher), sample file, the system throws error:

"Compile error in hidden module: Module_starter"
Cause:
This error is due to the installation of the Microsoft Security Update KB936021. Solution

To workaround this issue, follow these steps:

1. Go to the Start Menu on the affected machine: Program Files > Oracle> XML Publisher Desktop > Template Builder for Word -Bin.
2. Double click on the file "ChangeUILang.exe".
3. Select the appropriate language and click ok. This will register the User Interface language.
4. Test.
5. Migrate to other machines as needed.

Enable Help -> Diagnostics in Oracle Applications

We just need to set a profile to enable Help - Diagnostics in Oracle Applications. From this we can Examine forms value, personalizations etc.Pls see the below screenshot.

Set the following profile options.
1. Hide Diagnostics menu entry - No
2. FND: Diagnostics - Yes
3. Utilities:Diagnostics - Yes