Thursday, September 22, 2011

Oracle iProcurement : Realms

In Oracle iProcurement, Realms can be used to control access to punchout catalogs(/stores) to different responsibilities or users. This helps in limiting the access to catalogs for users from different departments. So for e.g. if your company policy limits the purchasing of computer hardware to the Information Technology (IT) department. To adhere to this policy and restrict certain users from ordering these types of items the Realms feature can be used.
To use this the following steps need to be followed.
    1. Create the realm. (Log in to Oracle Purchasing and use the following navigation path to open the Realms window: Setup > E-Catalog Admin > Realms)
    2. Assign the realm to a responsibility. (Login to System Administrator responsibility. Open the Responsibilities window using the following navigation path:Security > Responsibility > Define. Open the         Securing Attributes tab and do the necessary setup).
For detailed steps refer Chapter number 2 - Buyer Setup in the Oracle iProcurement user guide (http://download.oracle.com/docs/cd/B12190_11/current/acrobat/icx115punchout.pdf) provided by Oracle.

Friday, September 16, 2011

Oracle Database: Utility Queries

--*****************************************************************************************************
--***************************** To find value of parameters set in init.ora****************************
--*****************************************************************************************************
select name,value from v$parameter where name = 'utl_file_dir'
--*****************************************************************************************************
--***************************** To find object information ****************************************
--*****************************************************************************************************
-- for all objects user dba_<xxxx> views
select * from dba_objects -- gives details of all types of objects
select * from dba_procedures
select * from dba_tables
select * from dba_indexes
-- for objects owned by user use user_<xxxx> views
select * from user_objects -- gives details of all types of objects
select * from user_procedures
select * from user_tables
select * from user_indexes
--*****************************************************************************************************
--***************************** To find current sessions running on a database*************************
--*****************************************************************************************************
select distinct
sid
,s.serial#
,s.module
,lockwait
,s.schemaname
,s.username
,osuser
,machine
,s.terminal
,s.program
,status
,q.sql_text
from v$session s
,v$process p
,v$sqlarea q
where p.addr(+)=s.paddr
and q.hash_value(+)=s.sql_hash_value
and q.address(+)=s.sql_address
and status = 'ACTIVE'
order by sid desc
--*****************************************************************************************************
--***************************** To kill a session on a database *************************************
--*****************************************************************************************************

--ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390'; -- SID and SERIAL# received from above query.

--*****************************************************************************************************
--***************************** To get the current execution of cursor *******************************
--*****************************************************************************************************
SELECT Executions,
loads,
first_load_time ,
elapsed_time
FROM V$sqlarea
WHERE hash_value in (SELECT sql_hash_value
FROM V$session
WHERE sid = 48 );
--*****************************************************************************************************
--**************** To check which processes have locked a resource (generally a Table)*****************
--*****************************************************************************************************

SELECT c.sid,
c.serial#,
c.username,
a.object_id,
b.object_name,
c.program,
c.status,
d.name,
c.osuser
FROM v$Locked_object a,
all_objects b,
v$session c,
audit_actions d
WHERE a.object_id=b.object_id
AND a.session_id =c.sid(+)
AND c.command=d.action;
--*****************************************************************************************************
--***************************** To check ACTIVE session in any instnace *******************************
--*****************************************************************************************************
SELECT s.osuser, s.sid, s.sql_hash_value, s.module, w.event
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND s.status = 'ACTIVE'
AND event not like 'rdbms ipc message'
AND event not like 'pipe get'
AND event not like 'queue messages'
ORDER BY s.module

--*****************************************************************************************************
--***************************** To check current activity done by the REQUEST_ID **********************
--*****************************************************************************************************
SELECT fcr.request_id
,st.sql_text
,fcr.status_code
,s.sid
,s.serial#
,s.*
FROM apps.fnd_concurrent_requests fcr
,v$session s
,v$sqltext st
WHERE fcr.os_process_id = s.process (+)
AND s.sql_hasH_VALUE = st.hash_value (+)
AND fcr.request_id = 18740879
ORDER BY fcr.request_id, st.piece
--*****************************************************************************************************
--****************************************** To check deadlock ****************************************
--*****************************************************************************************************
select /*+ ordered */ w1.sid waiting_session
,h1.sid holding_session
,w.kgllktype lock_or_pin
,w.kgllkhdl address
,decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held
,decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock w
, dba_kgllock h
, v$session w1
, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr ;

Monday, September 5, 2011

Oracle Apps : Set Organization (Operating Unit) while working in a database session

-- ####################################
-- Notes
-- ####################################
-- All the queries below set the Organisation (read: Operating Unit) information for the session
--
-- Query 01 -- does the same by setting the user and responsibility for the session.
-- The calling code then derives the Organisation (Operating Unit, value in profile "MO: Operating Unit"
-- for that user/responsibility.
-- Query 02 -- does the same by directly setting the client info to the required 'Operating Unit'
-- Query 03 -- also does the same by directly setting the client info to the required 'Operating Unit'
-- Fyi - Operating units are defined in the HR_ALL_ORGANIZATION_UNITS table.

-- ####################################
-- Query 01
-- ####################################
-- The following call to apps_intialize function does not pass
-- the parameter "security_group_id", hence it is defaulted to
-- to zero (that is the setup Business Group, Business Group is the topmost level in the
-- Organization Definition Hierarchy. So generally the ORG_ID for the same is zero)

declare
    l_user_id      number;
    l_resp_id      number;
    l_resp_appl_id number;
begin
    -- get conversion user
    select fu.user_id
    into   l_user_id
    from   fnd_user fu
    where  fu.user_name = :p_user_name;

    -- get conversion responsibility
    select fr.responsibility_id,
           fr.application_id
    into   l_resp_id,
           l_resp_appl_id
    from   fnd_responsibility fr
    where  fr.responsibility_key = :p_responsibility_key;

    fnd_global.apps_initialize(
      user_id       => l_user_id,
      resp_id       => l_resp_id,
      resp_appl_id  => l_resp_appl_id);
end;
/


-- ####################################
-- Query 02
-- ####################################
-- To set the organisation for the session the following code can be used.

begin
  fnd_client_info.set_org_context('255');
end;
/



-- ####################################
-- Query 03
-- ####################################
-- To set the organisation for the session the following code can also be used.

exec dbms_application_info.set_client_info ('255');

Oracle Apps: HRMS - Set Effective Date for a Session through database

-- Following query can be used to set Effective Date for a session . This is particularly required while you are working with Oracle HRMS related tables.

INSERT INTO fnd_sessions
  (session_id
  ,effective_date)
  (SELECT userenv('sessionid')
         ,SYSDATE
   FROM dual
   WHERE NOT EXISTS (SELECT 'c'
          FROM fnd_sessions s1
           WHERE userenv('sessionid') = s1.session_id));

Sunday, August 28, 2011

Oracle Apps: Create Customer (Organization/Person) using API


Download the sample code (pls files) referred to in the below description from the following link 


Now follow the steps below to create an organization/person using API's

1) User Create Customer Account to create an Organization (or Person) type of customer
    create_customer_accounts_03.pls
 
2) Create Location
    create_location.pls
 
3) Create Party Site. (Link the Location (step 2) to Party created in (Step1))
    create_party_site.pls
 
4) Create Customer Account Site (Using the Party Site (Step 3) and Customer Account (Step 1))
    create_customer_account_sites.pls
   
5) Create Customer Account Site Use - Bill To (Identify the Customer Account Site created in Step 4 for use as BILL_TO)
    create_customer_account_site_use.pls
 
6) Create Customer Account Site Use - Ship To (Identify the Customer Account Site created in Step 4 for use as SHIP_TO)
    create_customer_account_site_use.pls
 
7) Create Org Contact Point for Phone details at Party Site level (Using the Party Site ID created in Step 3)
    create_org_contact_point_phone.pls
 
8) Create Org Contact Point for Email details at Party Site level (Using the Party Site ID created in Step 3)
    create_org_contact_point_email.pls
 
9) Create Org Contacts (Using the Object ID = Party ID created in Step 1. For Subject ID, it should be Party ID of a person, in our case we will create a common person who will be contact for all the organisations. So this Subject ID will be hardcoded.)
    create_org_contact.pls
 
10) Create Cust Account Role (Using the Party ID of Relationship that gets created in Step 9 and Cust Account ID created in Step 1 and Cust Account Site ID created in Step 4)
     create_cust_account_role.pls
 
11) Create Role Responsibility for the Cust Account Role - Bill To (Using the Cust Account Role ID created in step 10)
     create_role_responsibility.pls
 
12) Create Role Responsibility for the Cust Account Role - Ship To (Using the Cust Account Role ID created in step 10)
     create_role_responsibility.pls
 
A good document (Note: 230753.1) on metalink for different TCA API uses is available at
 
Optional Step - After Step 9 )  Create Org Contact Role (Using the Org Contact ID created in Step 9).
     create_org_contact_role.pls   - This step may not be required, check it out.   

Tuesday, August 16, 2011

Oracle Apps - Patch information from backend

Patches applied to apps instances
 select *
  from AD_APPLIED_PATCHES
  where patch_name = '6441660'
Patch level information
SELECT distinct patch_level
  FROM fnd_product_installations
 WHERE patch_level like '%AR%';

FND User - Programatically creating/updating/adding responsibilities

Following is the sample code to create a FND User 
begin
fnd_user_pkg.CreateUser(
  x_user_name                  => 'HVIRA',
  x_owner                      => 'SEED',
  x_unencrypted_password       => 'change123',
  x_start_date                 => sysdate,
  x_description                => 'Hiten Vira',
  x_email_address              => 'h@w.com');
commit;
end;
/
Following is the sample code to update a FND User 
(Useful when you want to change the password from backend) 
begin
fnd_user_pkg.UpdateUser (
  x_user_name                  => 'HVIRA',
  x_owner                      => 'SEED',
  x_unencrypted_password       => 'change123',
  x_start_date                 => sysdate,
  x_description                => 'Hiten Vira',
  x_email_address              => 'h@w.com');

commit;
end;
/
Following is the sample code to add responsibilities to a FND User
Add/Update the select statements in the FOR loop as per your needs
begin
    for r1 in (
        select 'AR_CM_KEY'                       resp_key,'AR' resp_app from dual union all
        select 'AR_CM_USER_KEY'                  resp_key,'AR' resp_app from dual union all
        select 'APPLICATION_DEVELOPER'           resp_key,'FND' resp_app from dual union all
        select 'IEX_COLLECTION_AGENT'            resp_key,'IEX' resp_app from dual union all
        select 'JTF_RESOURCE_MANAGER_AVIA'       resp_key,'JTF' resp_app from dual union all
        select 'US HR MANAGER'                   resp_key,'PER' resp_app from dual union all
        select 'FNDWF_ADMIN_WEB'                 resp_key,'FND' resp_app from dual union all
        select 'FND_FUNC_ADMIN'                  resp_key,'FND' resp_app from dual union all
        select 'SYSTEM_ADMINISTRATOR'            resp_key,'SYSADMIN' resp_app from dual union all
        select 'XDO_ADMINISTRATION'              resp_key,'XDO' resp_app from dual)
    LOOP
        fnd_user_pkg.AddResp( username => 'HVIRA',
                              resp_app => r1.resp_app,
                              resp_key => r1.resp_key,
                              security_group => 'STANDARD',
                              description    => 'Hiten Vira',
                              start_date     => SYSDATE ,
                              end_date       => NULL);
    end loop;
commit;
end;
/

Thursday, August 11, 2011

How to deploy ADF Faces web applications on Apache Tomcat (and using Apache Derby database) - Part 2


Apache Tomcat is an open source implementation of JSP and Servlet technologies which is used to host wide variety of web applications using Java technologies. It is a web server of choice for people using open source technologies.

So why would you want to create web applications using ADF technology?

  • Using JDeveloper and ADF technology it is very easy to create applications which require database interaction. (Creating data bound applications with any other technologies such as JSF, Servlets will definitely take more effort and knowledge). These applications can then be deployed over the internet or can also be deployed to run on a single machine.

So why would you want to use ADF and deploy it on Tomcat rather than Weblogic application server?

  • Weblogic is a very heavy application server (plus it requires licenseJ), whereas Tomcat is open source, light weight, easy to deploy and maintain.  Tomcat is ideally suited to run web applications on a single machine.

Following contributions available on internet were very helpful while finalizing the steps mentioned below.



These steps were implemented while working with
JDeveloper - version 11.1.2.0.0
Derby – version 10.8.1.2
Tomcat – 7.0.19

Following are the steps

1)      The first step is to get all the JAR files which are required for your application to run. As per Oracle’s directory structure, these JAR files are distributed in multiple directories available in the Middleware Home.  It is very tedious task to go and manually collect these files, to get all the relevant files in one go the following method can be used.
=> Right click on your ‘ViewController’ project
=> Select ‘Deployment’
=> Select ‘Edit’ (for the default deployment profile that is created)
=> Go to ‘File Groups/WEB-INF/lib/Contributors’
=> Select all the libraries available in the window
=> Go to ‘Platform’, Choose ‘Default Platform’ as ‘Tomcat 6.x’
=> Come back to the main JDeveloper window
=> From the ‘Build’ menu, select ‘Deploy’ and then the default deployment profile that you edited.
=> From the dialog box which opens up choose ‘Deploy to WAR’
=> This will create the WAR file in your JDeveloper ‘ViewController’ project under the ‘deploy’ directory. The WAR file will have all the JAR files that your project needs to run.
=> Create a new directory ‘adf’ under the directory ‘$CATALINA_HOME/lib’
=> Extract the JAR files to the new directory ‘adf’ created in the above step.

Following is the indicative list of the JAR files (your application may not return the same JAR files)



adf-controller-api.jar
adf-controller-rt-common.jar
adf-controller-schema.jar
adf-controller-security.jar
adf-controller.jar
adf-dt-at-rt.jar
adf-faces-databinding-rt.jar
adf-pageflow-dtrt.jar
adf-pageflow-fwk.jar
adf-pageflow-impl.jar
adf-pageflow-rc.jar
adf-richclient-api-11.jar
adf-richclient-impl-11.jar
adf-share-base.jar
adf-share-ca.jar
adf-share-security.jar
adf-share-support.jar
adfdt_common.jar
adflibfilter.jar
adflibrary.jar
adflogginghandler.jar
adfm-debugger.jar
adfm.jar
adfmweb.jar
adfsharembean.jar
adftransactionsdt.jar
bc4j-mbeans.jar
bc4jtester.jar
bundleresolver.jar
cache.jar
com.bea.core.apache.commons.collections_3.2.0.jar
commons-cli-1.0.jar
commons-digester.jar
commons-el.jar
db-ca.jar
derby.jar
derbyclient.jar
dms.jar
dvt-databindings-mds.jar
dvt-databindings.jar
dvt-faces.jar
dvt-facesbindings.jar
dvt-jclient.jar
dvt-trinidad.jar
dvt-utils.jar
fmw_audit.jar
glassfish.el_1.0.0.0_2-1.jar
glassfish.jstl_1.2.0.1.jar
groovy-all-1.6.3.jar
help-share.jar
identitystore.jar
idert.jar
inspect4.jar
iText-2.1.7.jar
jacc-spi.jar
jasperreports-4.0.2.jar
javatools-nodeps.jar
javatools.jar
javax.activation_1.1.0.0_1-1.jar
javax.mail_1.1.0.0_1-4-1.jar
javax.mail_1.4.jar
javax.management.j2ee_1.0.jar
javax.management_1.2.1.jar
javax.security.jacc_1.0.0.0_1-1.jar
jdev-cm.jar
jdev-rt.jar
jewt4.jar
jmxframework.jar
jmxspi.jar
jps-api.jar
jps-common.jar
jps-ee.jar
jps-internal.jar
jps-manifest.jar
jps-unsupported-api.jar
jrf-api.jar
jrf-client.jar
jrf.jar
jsf-api.jar
jsf-impl.jar
jsp-el-api.jar
jstl.jar
ldapjclnt11.jar
mdsrt.jar
oc4jclient.jar
ohj.jar
ojdbc6.jar
ojdbc6dms.jar
ojdl.jar
ojmisc.jar
ojsp.jar
oracle-el.jar
oracle-page-templates-ext.jar
oracle.ucp_11.1.0.jar
oracle.web-common_11.1.1.jar
oraclepki.jar
oracle_ice.jar
orai18n-mapping.jar
orai18n.jar
oramds.jar
org.apache.bcel_5.1.jar
org.apache.commons.beanutils_1.6.jar
org.apache.commons.digester_1.8.jar
org.apache.commons.logging_1.0.4.jar
osdt_cert.jar
osdt_core.jar
osdt_ws_sx.jar
osdt_xmlsec.jar
regexp.jar
resourcebundle.jar
share.jar
standard.jar
trinidad-api.jar
trinidad-impl.jar
xercesImpl.jar
xml.jar
xmlef.jar
xmlparserv2.jar











2)     Go back to the WAR deployment profile properties ‘File Groups/WEB-INF/lib/Contributors’ and deselect all the libraries that you had selected.

3)      Redeploy the WAR file, the size will be considerably less as the JAR files will not be included in this.

4)      Change the web.xml file to the one created using older version of JDeveloper (for e.g. JDeveloper 11.1.1.3.0)

5)      Comment the JPSFilter related tags in web.xml file

6)      Comment the Security related tags in adf-config.xml file.

7)      (This step is required only if you are using older version of JDeveloper. In case of JDeveloper 11.1.2.0.0 the connection type is JDBC Datasource by default.) In JDeveloper Model project. Right click on 'AppModule', click 'Configuration's. You will see two configurations 'AppModuleLocal' and 'AppModuleShared'. Edit these two configurations to change the connection type from 'JDBC URL' to 'JDBC Datasource'.

8)      In bc4j.xcfg file replace the following tag (difference is in the slash before the word ‘comp’)
      <Custom JDBCDataSource="java:comp/env/jdbc/DerbyADFDBDS"/>
      with
      <Custom JDBCDataSource="java:/comp/env/jdbc/DerbyADFDBDS "/>
Alternatively, the above change can be in JDeveloper itself while you are performing step 6.

9)      In $CATALINA_HOME/conf/catalina.properties replace
  common.loader=${catalina.base}/lib,${catalina.base}/lib/*.jar,${catalina.home}/lib,${catalina.home}/lib/*.jar   

with

common.loader=${catalina.home}/lib/adf,${catalina.home}/lib/adf/*.jar,${catalina.base}/lib,${catalina.base}/lib/*.jar,${catalina.home}/lib,${catalina.home}/lib/*.jar   

(Basically, load the ADF classes first and then load the Tomcat classes. In the example above the ADF classes and jars are stored under a new directory "adf" created under ${catalina.home}/lib)

10)  In <CATALINA_HOME> directory create a folder "config" and put in it the file jps-config.xml, this file is available in your WAR file.
Edit the version tag in this file to change the encoding from "Cp1252" to "windows-1252"

11)  Start Tomcat server. This will automatically deploy your project WAR file.

12)  Check the log files created in <CATALINA_HOME>/log directory to check for any errors. If there are any SEVERE errors try to resolve them. The most common error your might encounter is a ClassNotFoundException. Identify the relevant JAR which might be missing and place the same in <CATALINA_HOME>/lib/adf directory. To identify the JAR file name from the class file the site www.findjar.com is very helpful.

13)  After the web application is deployed successfully you can access it through the URL


where,
WAR-Name is the name of your WAR file
And
Default-Page is the default page referred to by your Unbounded task flow

For a typical URL will look like