Find query behind the Form or OA Framework page

http://piotrbelter.blogspot.com/2009/07/find-query-behind-form-or-oa-framework.html

How to query key flexfields

It is quite common that in Oracle Applications you need to write queries listing specific account combinations. The problem with that is that the account combinations are stored as their ids and you have to go to the GL_CODE_COMBINATIONS table to find the specific segments and concatenate them one by one.
Easy to do but always waste of time. Moreover, each EBS install has a different number of segments and query written for one client needs to be rewritten for another.
Well, not any more. There are Oracle APIs that allow you to get the complete combination as a string by passing the CCID

Here is the syntax:

fnd_flex_ext.get_segs('SQLGL', 'GL#', chart_of_accounts_id, code_combination_id)

Now, if you are on release 12 and want to have the concatenated combination descriptions you can also run this:

xla_oa_functions_pkg.get_ccid_description (chart_of_accounts_id, code_combination_id)

Here is a sample query:

select
fnd_flex_ext.get_segs( 'SQLGL',
'GL#',
(select chart_of_accounts_id
from GL_SETS_OF_BOOKS
where name='Vision Operations (USA)'),
code_combination_id) account,
xla_oa_functions_pkg.get_ccid_description(
(select chart_of_accounts_id
from GL_SETS_OF_BOOKS
where name='Vision Operations (USA)'),
Code_combination_id) description
from GL_JE_LINES


The fnd_flex_ext.get_segs call is universal and will work for any key flexfield. Here is the syntax for HR position hierarchies:


FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id)


and here is a sample query:


select
FND_FLEX_EXT.GET_SEGS('PER', 'POS', id_flex_num, position_definition_id) position_definition
from per_position_definitions

How to find item cost

Another common issue when writing queries is getting an item cost.


Here are the APIs to use in queries:


For discrete manufacturing:


cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)


here is a sample query:


select segment1 item,
cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL) cost
from mtl_system_items_b

Oracle Application Help Line: Email the output of a concurrent program as Attachment

http://erpschools.com/Apps/oracle-applications/Articles/Sysadmin-and-AOL/Email-the-output-of-a-concurrent-program-as-Attachment/index.aspx

Email the output of a concurrent program as Attachment

OA Frame Work

Following are the Steps to Start With OAF :-

1)Download the latest version of JDeveloper (OAJdev.zip) which is patched for OA Framework from metalink.(We used 9.0.3 version of JDeveloper)
2) Unzip the accompanying zip file to a directory of your choice (see **NOTE below) on your client machine, which creates the following directory structure under your :
jdevbin\
jdevdoc\
jdevhome\
**NOTE: The installation directory to which you are unzipping this file must NOT contain spaces.E.g. Do NOT unzip to C:\Program Files\jdeveloper.
3. For convenient access to startup the IDE, create a desktop shortcut
to the JDeveloper executable located at:
\jdevbin\jdev\bin\jdevw.exe
Do NOT launch JDeveloper at this time. Proceed to Step 3 below.
4. To setup and test your development environment access the chapter on
'Setting Up Your Development Environment' in the Oracle Applications
Framework Developer's Guide from the following location:
\jdevdoc\WebHelp\devguide\gs\gs_setup.htm
Follow the instructions and complete all of the steps under the section titled:
'You are Customer, Consultant or Support Representative'
To ensure your setup is correct, you should complete the verification
steps described under 'Task 6: Test your Setup'.

Steps to Kill Concurrent Program

1)SELECT * FROM DBA_DDL_LOCKS WHERE NAME LIKE'PACKAGE NAME ON WHICH CONC
PGM IS RUNNING%';
-- TAKE SESSION_ID AS SID
2) SELECT * FROM V$SESSION WHERE SID=----;
--SELECT SID AND SERIAL# FROM THE ABOVE
3)ALTER SYSTEM KILL SESSION 'SID,SERIAL#';