Query to get Concurent Programs attached to given value set

SELECT fcpl.user_concurrent_program_name ,
fcp.concurrent_program_name ,
par.column_seq_num ,
par.end_user_column_name ,
par.form_left_prompt prompt ,
par.enabled_flag ,
par.required_flag ,
par.display_flag ,
par.flex_value_set_id ,
ffvs.flex_value_set_name ,
flv.meaning default_type ,
par.DEFAULT_VALUE
FROM APPS.fnd_concurrent_programs fcp ,
APPS.fnd_concurrent_programs_tl fcpl ,
APPS.fnd_descr_flex_col_usage_vl par ,
APPS.fnd_flex_value_sets ffvs ,
APPS.fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
AND ffvs.flex_value_set_id=102362

Oracle Applications: Key Flex Field Structures & How to Retrieve Information

http://www.notesbit.com/index.php/oracle-applications/11i-scripts/oracle-applications-key-flex-field-structures-how-to-retrieve-information/

This script is used to extract order header details from backend.

SELECT ra.customer_number,
hl.address1,
hl.address2,
hl.city,
hl.state,
hl.postal_code,
hl.country,
hl.province,
hl.county
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
ra_customers ra
WHERE 1 = 1
AND ooh.invoice_to_org_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcas.cust_account_id = ra.customer_id
AND order_number = &Order_number

Oracle Financial Training

http://www.aboutoracleapps.com/2007/07/gl-concepts-1.html

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