How to kill an active session in Oracle apps

Hi, This post shows the queries to find an active sessions for an object and kill the session.

Note: Use the query to kill a session only if you have authorized to kill the session.

Query to see if the object has an active session:

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, session_id

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID

and b.object_id =:p_object_id;

Query to find the sis and serial# based on session id that is fetched from the above query:

select * from v$session where sid = '2777';

Command to kill the session using sis and serial#:

alter system kill session '2777,23685';

Happy learning. Cheers!!

Query to get users who have a specific responsibility is attached to them in Oracle apps

Hi! This post shows you the query to fetch the users who have a particular responsibility to them.

SELECT fu.user_name,

  frt.responsibility_name,

  TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,

  furg.end_date,

  fu.DESCRIPTION 

FROM fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

WHERE fu.user_id                 = furg.user_id

AND frt.responsibility_id        = furg.responsibility_id

AND frt.application_id           = furg.responsibility_application_id

AND NVL(furg.end_date,sysdate+1) > sysdate

AND NVL(frt.end_date,sysdate +1) > sysdate

AND NVL(fu.end_date,sysdate  +1) > sysdate

AND frt.responsibility_name      = :p_resp_name;

Happy learning. Cheers!!

How to fix invalid character in XML Error in Oracle apps R12

Hi, this post shows how to fix the invalid character in XML data error that may occur sometimes in RDF reports in Oracle apps. To fix the error, follow the below enlisted steps.

1) Run following query to determine the characterset:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

2) Set profile option FND: NATIVE CLIENT ENCODING to same value as the output of query in step 1.

--FND: NATIVE CLIENT ENCODING for XML data generation

If browser is not responding when loading XML data, recheck RDF then try fetching less data from query. If that works, recheck with all the data.

Cheers!!

Query to find order holds in Oracle apps R12

Hi! This post is to show the query to find holds on sales orders in Oracle apps R12.

Below is the query that you can use to find holds on a specific order:

SELECT ooh.order_number,

       ohd.hold_id,

       ohd.name hold_name

FROM oe_order_headers_all ooh,

     oe_order_holds_all ooha,

     oe_hold_sources_all ohs,

     oe_hold_definitions ohd

WHERE ooh.header_id = ooha.header_id

  AND ohs.hold_id = ohd.hold_id

  AND ohs.hold_source_id = ooha.hold_source_id

  AND ooh.order_number = <order_number>;

Happy learning!!

Script to run SQL Query from Host file in Oracle apps

Hi, This post shows you the sample script to run SQL query from host file in Oracle Apps.

In the below example, I used a query to fetch the directory path.

#!/bin/ksh

#----- Standard Parameters: -----#

p_apps_passwd=`echo ${1}`

p_user_id=`echo ${2}`

p_username=`echo ${3}`

p_request_id=`echo ${4}`

target_path=/u01/apps/apps_st/appl/jtm/12.0.0

p_file_directory()                                                         #Directory Path

{

sqlplus -s ${p_apps_passwd} << EOF                        #Starts from here

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET LINES 255

SET PAGES 0

SET FEEDBACK OFF

SET ECHO OFF

SET PAGESIZE 0

SET TIMING OFF

SET SERVEROUTPUT ON

SET TERM ON

SELECT file_path FROM request_table;                    #sample query

EOF

}

echo "Directory Path: $p_file_directory"                    #Printing directory path

# - Represents comments

Hope this helps for beginners. Great learning ahead!!

Customer tables and their joins in Oracle Apps

Hi! This is post will show you important customer tables in Oracle apps along with their joins.

Important Customer tables:

HZ_PARTIES

HZ_CUST_ACCOUNTS

HZ_PARTY_SITES

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_LOCATIONS

Joins:

HZ_PARTIES, HZ_PARTY_SITES - party_id

HZ_PARTIES, HZ_CUST_ACCOUNTS - party_id

HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL - cust_account_id

HZ_PARTY_SITES, HZ_CUST_ACCT_SITES_ALL - party_site_id

 HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL - cust_acct_site_id

HZ_PARTY_SITES, HZ_LOCATIONS - location_id.

Queries to get ship to and bill to address details:

Ship to address:

SELECT hp.PARTY_NAME customer,

       hl.ADDRESS1,

       hl.ADDRESS2,

       hl.CITY,

       hl.POSTAL_CODE,

       hl.STATE,

       hl.PROVINCE,

       hl.COUNTRY

FROM hz_parties hp,

  hz_cust_accounts hca,

  hz_cust_acct_sites_all hcasa,

  hz_cust_site_uses_all hcsua,

  hz_party_sites hps,

  hz_locations hl

WHERE hp.party_id        = hca.party_id

AND hca.cust_account_id   = hcasa.cust_account_id

AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID

AND hp.party_id          = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hl.location_id       = hps.location_id

AND hp.party_name= <party_name>

AND hcsua.site_use_code = 'SHIP_TO'

Bill to address:

SELECT hp.PARTY_NAME customer,

       hl.ADDRESS1,

       hl.ADDRESS2,

       hl.CITY,

       hl.POSTAL_CODE,

       hl.STATE,

       hl.PROVINCE,

       hl.COUNTRY

FROM hz_parties hp,

  hz_cust_accounts hca,

  hz_cust_acct_sites_all hcasa,

  hz_cust_site_uses_all hcsua,

  hz_party_sites hps,

  hz_locations hl

WHERE hp.party_id        = hca.party_id

AND hca.cust_account_id   = hcasa.cust_account_id

AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID

AND hp.party_id          = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hl.location_id       = hps.location_id

AND hp.party_name = <party_name>

AND hcsua.site_use_code = 'BILL_TO';

Hope this helps you. Great learning ahead!!