By using below query we can get the multi org status.
SELECT multi_org_flag
FROM fnd_product_groups
How to know Which User is Locked the table?
This Query will list the name of user who locked the table table. The object name is taken as an input parameter.
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';
How to get Customer Address Details in Oracle apps?
The following Query will provide the customer address details in oracle applications.
SELECT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;
Script to find Oracle API’s for any module
With the help of below query we can find the API for a module in Oracle Apps.
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name
OPM – GMD – Delete Row from the formula in R12
The procedure to delete the row from the formula
PROCEDURE delete_formuladetail(
p_formula_id NUMBER ,
p_formulaline_id NUMBER ,
p_return_status OUT VARCHAR2 ,
p_message OUT VARCHAR2 )
IS
l_api_version CONSTANT NUMBER := 1.1;
l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE ;
l_commit VARCHAR2(1) := FND_API.G_FALSE;
l_called_from_forms VARCHAR2(10) := ‘NO’;
l_return_status VARCHAR2(1) ;
l_msg_count NUMBER ;
l_msg_data VARCHAR2(400) ;
L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_UPDATE_DTL_TBL_TYPE;
— l_formula_head fm_form_mst_b%rowtype;
L_FORMULA_LINE FM_MATL_DTL%ROWTYPE;
— i pls_integer := 1;
NO_PROC_FINISH EXCEPTION ;
BEGIN
IF (P_FORMULA_ID IS NULL) THEN
P_MESSAGE := ‘ P_formula_id parameter can not be empty ‘;
RAISE NO_PROC_FINISH;
END IF ;
— получаем данные заголовка
BEGIN
SELECT *
INTO l_formula_head
FROM fm_form_mst_b fm
WHERE fm.formula_id = p_formula_id;
EXCEPTION
WHEN OTHERS THEN
p_message := ‘ Unable to find a formula ‘;
raise no_proc_finish;
END ;
— get the data string formula
BEGIN
SELECT *
INTO l_formula_line
FROM fm_matl_dtl fm
WHERE fm.formulaline_id = p_formulaline_id;
EXCEPTION
WHEN OTHERS THEN
p_message := ‘ Could not find a string formula ‘;
raise no_proc_finish;
END ;
gme_common_pvt.set_who;
l_formula_detail_tbl(i).RECORD_TYPE := ‘D’;
l_formula_detail_tbl(i).formula_id := p_formula_id;
l_formula_detail_tbl(i).formula_no := l_formula_head.formula_no;
l_formula_detail_tbl(i).formula_vers := l_formula_head.formula_vers;
l_formula_detail_tbl(i).formulaline_id := p_formulaline_id;
GMD_FORMULA_DETAIL_PUB.DELETE_FORMULADETAIL( P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => L_INIT_MSG_LIST, P_COMMIT => L_COMMIT, P_CALLED_FROM_FORMS => L_CALLED_FROM_FORMS, X_RETURN_STATUS => L_RETURN_STATUS, X_MSG_COUNT => L_MSG_COUNT, X_MSG_DATA => L_MSG_DATA, P_FORMULA_DETAIL_TBL => L_FORMULA_DETAIL_TBL );
IF l_return_status != FND_API.g_ret_sts_success THEN
IF l_msg_count = 1 THEN
p_message := FND_MSG_PUB.get(1,’F’);
RAISE no_proc_finish;
ELSE
FOR l IN 1..l_msg_count
LOOP
FND_MSG_PUB.get (p_msg_index => l ,p_encoded => ‘F’ ,p_data => p_message ,p_msg_index_out => l_msg_count);
END LOOP ;
RAISE no_proc_finish;
END IF ;
END IF ;
p_return_status := ‘S’;
EXCEPTION
WHEN no_proc_finish THEN
p_return_status := ‘ E ‘;
WHEN OTHERS THEN
p_message := SQLERRM ;
p_return_status := ‘ E ‘;
END ;
Share this:
How to add a context dependent descriptive flex-field on OAF Control Item page?
These context dependent flexfields were at first not possible in framework because the references like BLOCK etc (which you can use in Forms) are not applicable to framework pages. Starting from release 12.0.6 of E-Business Suite there is a way to implement context descriptive flexfields on framework pages.
As an example I’ll use some example we encountered during an implementation. There was a requirement that additional details should be given to a control item in Project Management. Control Items in Project Management can be used to record your issues and risk on your running projects. In this case we have 2 Control Item Types being Issue and Risk. Based on the type we need to display additional segments in a DFF for the end user to use. This DFF must change with every type of issue.
To implement this behaviour we need to do a number of things:
Key thing in the above is step number 4. Starting from release 12.0.6 E-Business Suite introduces the functionality of Flex Maps. Follow the below steps to implement the behaviour. Of course the same method can be used in other parts of E-Business Suite if the page has a seeded DFF. If that’s not the case than also a self made descriptive flexfield can be added but I’ll write a post about that in near future.
Step 1: Implement the DFF for Control Items
a) Go to Application Developer – Flexfield – Decriptive – Segments and query the Control Item flexfield with the name Control Items Desc Flex.
b) Unfreeze the Flexfield Definition if needed. Tick the checkbox Synchronize with Reference Field to make sure the context is updated when you move along control items. Also untick the Displayed checkbox as we don’t want to show the Context field to the end user. Set the Reference Field. Add the syntax :$PROFILES$.<Name>. As a name anything can be used. Remember that name because we need it in a later step. In my example I’ll set it to :$PROFILES$.TypeCICyriel
c) Add Context Field values. I’ll add 2 values named Issue and Risk. It’s very important that these names represent the exact naming of your control item types. Add as many as you like representing each option you have for the types. Add your segments to the contexts and assign them to one of the attribute fields.
By default the segments are set to be a required segment. If applicable turn this off by opening the segment (click on Open) and untick the required checkbox.
d) When done adding your context field values and the related segments save the work and freeze the Flexfield definition. Your DFF should compile without problems.
Step 2: Handle the DFF on the framework page (Control Items)
Make sure the DFF is enabled (Shown) for the page. If that’s not the case than click the Personalize pencil on the row Flex: (PaDescFlex) and change the rendered setting to Yes.
Step 3: Add a Flex Map (bean) to the DFF in OAF through personalization
a) Click on Create Item on the row Flex: (PaDescFlex). You get a new screen where you define the Flex Map settings.
b) Enter the ID which can be anything you want. Leave the Attribute Set and Comments blank. For Name you have to use the exact name as defined in the reference field setting in the DFF definition without the :$PROFILES$. I used the reference field name settting :$PROFILES$.TypeCICyriel so the name of the Reference Value of the Flex Map must be TypeCICyriel.
The Reference Value is very important and will be the placeholder for in this case the Control Item Type name. This setting can use the so called SPEL language with which you reference an attribute of the page or view object of the OAF page. In this case the Control Item Type name is a seeded attribute of the ControlItemVO object of the page. We enter ${oa.ControlItemVO.CiTypeName} as the reference value. The Refers Context option needs to be set to true as we use this Flex Map as a context item.
c) Save all your work and bounce apache if the changes are not reflexted immediately.
The end result will be that when you create a new control item being, in our example, a risk or an issue the applicable additional segments are shown for the type. We’ve unticked the Displayed checkbox within the DFF definition so it’s also not visible in the OAF page. See below the shots for the end results. Good luck with using this feature 🙂
Share this:
How to Configure Oracle Approval Management (AME) for developer?
Steps to AME configuration for any user:
1) Select the User Management responsibility.
2) Select the Users page.
3) Search for the user to whom you wish to grant AME roles.
4) In the results table, click Update. In the Update User page, you can view user details along with a list of roles available to the user.
5) Click Assign Roles.
6) Select following roles and click Apply.
7) Grant data access to users
8) Run “Workflow Directory Services User/Role Validation”
9) Select Approval Management Business Analyst responsibility and check functionality.
Share this:
Tricks for generating SQL statements in Excel 2003/2007
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")Share this:
Some Useful Queries in Oracle Apps R12
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API’s in Oracle Ebusiness suite. Its not required for all the API’s but its recommended that you set this profile before making any calls to either private or public API.
Mentioned below is a sample call to FND_GLOBAL.APPS_INITIALIZE function
fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);
l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.
You can use either sysadmin or use some user who has all the above listed responsibilities.
For SYSADMIN, utilize the following query to get the respective values
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = ‘SYSADMIN’
and fresp.responsibility_name = ‘Order Management Super User’;
Another option is Help > Diagnostics > Examine and get the values from $profile session values.
How to Find PO Requisitions associated with PO Orders?
By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.
They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.
SELECT prha.segment1 “Requisition Number”,
prha.type_lookup_code “Requisition Type”,
pha.segment1 “Purchase Order Number”,
pha.type_lookup_code “Purchase Order Type”
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.
SELECT
prha.requisition_header_id “requisition_header_id”
,prha.segment1 “Requisition Number”
,prha.preparer_id “preparer_id”
,TRUNC(prha.creation_date) “creation_date”
,prha.description “description”
,prha.note_to_authorizer “note_to_authorizer”
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code=’CANCEL’
AND pah.object_type_code=’
AND pah.object_id=prha.
SELECT
NVL(substr(org.organization_
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;
How to know the application is multi-org or not?Share this: