How to initialize session in Oracle ?
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='
REQUISITION'
AND pah.object_id=prha.requisition_header_id
How to find On-hand inventory information?
Execute below query to see the onhand inventory information in oracle applications.
SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,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.transaction_quantity))), ' ') trans_qnty
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 version in oracle apps?
select * from v$version;
With the help of above query we can find out the oracle apps version.
How to know the application is multi-org or not?
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
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='
REQUISITION'
AND pah.object_id=prha.requisition_header_id
How to find On-hand inventory information?
Execute below query to see the onhand inventory information in oracle applications.
SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,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.transaction_quantity))), ' ') trans_qnty
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 version in oracle apps?
select * from v$version;
With the help of above query we can find out the oracle apps version.
How to know the application is multi-org or not?
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
No comments:
Post a Comment