Useful OA Queries

Here some of quite useful regular sql queries in oracle applications.

Query 1: Select responsibility name along with application name

SELECT application_short_name, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt, fnd_application fa
WHERE fa.application_id = frt.application_id;

Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = &resp_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;

Query 3: Get User name and related assigned responsibilities

SELECT DISTINCT u.user_id, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY 1;

Query 4: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Query 5: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables

applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

Query 6: Query to view the patch level status of all modules

SELECT a.application_name,
DECODE (b.status, ‘i’, ‘installed’, ‘s’, ‘shared’, ‘n / a’) status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;

Query 7: SQL to view all request who have attached to a responsibility
SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = ‘p’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query 8: SQL to view all types of request Application wise/* SELECT f.request_id,
pt.user_concurrent_program_name user_concurrent_program_name,
f.actual_start_date actual_start_date,
f.actual_completion_date actual_completion_date,
FLOOR ( ( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60
)
/ 3600
)
|| ' HOURS '
|| FLOOR ( ( ( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60
)
- FLOOR ( ( ( f.actual_completion_date
- f.actual_start_date
)
* 24
* 60
* 60
)
/ 3600
)
* 3600
)
/ 60
)
|| ' MINUTES '
|| ROUND (( ( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60
)
- FLOOR ( ( ( f.actual_completion_date
- f.actual_start_date
)
* 24
* 60
* 60
)
/ 3600
)
* 3600
- ( FLOOR ( ( ( ( f.actual_completion_date
- f.actual_start_date
)
* 24
* 60
* 60
)
- FLOOR ( ( ( f.actual_completion_date
- f.actual_start_date
)
* 24
* 60
* 60
)
/ 3600
)
* 3600
)
/ 60
)
* 60
)
)
)
|| ' SECS ' time_difference,
DECODE (p.concurrent_program_name,
'ALECDC', p.concurrent_program_name || '[' || f.description
|| ']',
p.concurrent_program_name
) concurrent_program_name,
DECODE (f.phase_code,
'R', 'Running',
'C', 'Complete',
f.phase_code
) phase,
f.status_code
FROM apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.LANGUAGE = USERENV ('Lang')
AND f.actual_start_date IS NOT NULL
ORDER BY f.actual_completion_date - f.actual_start_date DESC;

List Active Users and Their Active Responsibilities In Oracle Apps

select user_name, application_name, responsibility_name,
security_group_name,
greatest(to_date(u.start_date), to_date(ur.start_date), to_date(r.start_date)) start_date,
decode(
least(nvl(u.end_date, to_date('01/01/4712','DD/MM/YYYY')),
nvl(ur.end_date, to_date('01/01/4712','DD/MM/YYYY')),
nvl(r.end_date, to_date('01/01/4712','DD/MM/YYYY'))),
to_date('01/01/4712','DD/MM/YYYY'), '',
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date)))) end_date
from fnd_user u, fnd_user_resp_groups_all ur,
fnd_responsibility_vl r, fnd_application_vl a,
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and nvl(ur.end_date, sysdate + 1) > sysdate
and u.start_date <= sysdate
and nvl(u.end_date, sysdate + 1) > sysdate
and r.start_date <= sysdate
and nvl(r.end_date, sysdate + 1) > sysdate
and ur.security_group_id = s.security_group_id
and r.version in ('4','W','M')
--and responsibility_name = 'Functional Administrator'
order by user_name, application_name, responsibility_name,
security_group_name

Code to print webbean's child names with their ids

Enumeration enum= webBean.getChildNames();
while(enum!=null && enum.hasMoreElements())
{
String name = (String)enum.nextElement();
System.out.println(name);
}

it should print the child names, with their ids

Puzzle - $1 vanished ?

Three men go to a cheap motel, and the desk clerk charges them a sum of $30.00 for the night. The three of them split the cost ten dollars each. Later the manager comes over and tells the desk clerk that he overcharged the men, since the actual cost should have been $25.00. The manager gives the bellboy $5.00 and tells him to give it to the men. The bellboy, however, decides to cheat the men and pockets $2.00, giving each of the men only one dollar.
Now each man has paid $9.00 to stay for the night, and 3 x $9.00 = $27.00. The bellboy has pocketed $2.00. But $27.00 + $2.00 = $29.00. Where is the missing $1.00? WTF?

Solution:
Total Customer effectively paid =27
Total amnt the hotel got = 25
The difference is in bellboy's pocket = 27-25 =2

Puzzle -Daughter's Age Problem

A census taker came to a house where a man lived with
three daughters. "What are your daughters' ages?" he asked.
The man replied, "The product of their ages is 72, and the
sum of their ages is my house number."
"But that's not enough information," the census taker insisted.
"All right," answered the farmer, "the oldest loves chocolate.

What are the daughters' ages?

Solution :
We can look at all the possibilities this way - the oldest may be the same age as the middle, and the middle may be the same age as the youngest:
OLD MID YOUNG SUM
72 1 1 74
36 2 1 39
24 3 1 28
18 4 1 23
18 2 2 22
12 3 2 17
12 6 1 19
9 8 1 18
9 4 2 15
8 3 3 14
6 6 2 14
6 4 3 13
These are all the possibilities, since if we give the oldest a lower age, there is no way the product can equal 72.We know their ages add up to the farmer's house number. The sum column gives all possibilities for the house number. When the farmer gave the census person the information about the product of their ages being 72 and the sum of their ages being his house number the census person said this was not enough information. So there must have been at least two different possibilities that were still viable options for the census taker to choose from. This means the house number must have had two sums equal it. So the daughters are:
8 3 3 14
6 6 2 14
since they both add up to 14, the only number that appeared twice in the sum of the ages. The fact that the farmer had an oldest daughter says that the daughters must in fact be ages 8, 3, and 3.

Test For Palindrome - Java

package palindrome;
import java.util.*;

public class TestPalindrome
{
public TestPalindrome()
{
}

public static boolean isPalindrome(String word) {
int left = 0; // index of leftmost unchecked char
int right = word.length() -1; // index of the rightmost

while (left < right) { // continue until they reach center
if (word.charAt(left) != word.charAt(right)) {
return false; // if chars are different, finished
}
left++; // move left index toward the center
right--; // move right index toward the center
}
return true; // if finished, all chars were same
}

public static void main(String[] args)
{
TestPalindrome testPalindrome = new TestPalindrome();
String s = "Was it a rat I saw?" ;
s = s.replaceAll("[\\s\\p{Punct}]", "").toLowerCase();
boolean flag = isPalindrome(s);
System.out.println(s + " PALINDROME ? " + flag);

}

}

Extract Jar from Blob Column through Java code

I used this code to extract jar file which was stored in BLOB column of the database.
/**
*
* This code extracts jar file from cz_archives table from the BLOB column.
*
*/

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class ExtractBlob
{
public ExtractBlob()
{
}
final static int bBufLen = 4 * 8192;
static String query;
static String outFile;
static Connection conn;

public static void main(String[] args) throws FileNotFoundException, IOException, SQLException
{
String baseFolder = "C:/CZArchives";
String folderName = "";
String fileName = "";

String query = "SELECT 'TEST-Archive' FOLDER_NAME, "
+ "CZArch.ARCHIVE_URL DOCNAME,CZArch.ARCHIVE_BLOB "
+ "FROM CZ_ARCHIVES CZArch, CZ_RP_ENTRIES CZRp "
+ "WHERE CZRp.OBJECT_ID = CZArch.ARCHIVE_ID "
+ "AND CZArch.deleted_flag = '0' "
+ " AND CZRp.OBJECT_TYPE = 'ARC' "
+ "and CZArch.name = 'nameofyourjarfile' ";
//replace nameofyourjarfile with your jar file name

conn = getConnection();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Blob blob = null;
while (rs.next())
{
folderName = rs.getString(1);
fileName = rs.getString(2);
// String created = rs.getString(4);
// String updated = rs.getString(5);
blob = rs.getBlob(3);
System.out.println("Got " + folderName);
System.out.println("Got " + fileName);
// System.out.println("Created " + created);
// System.out.println("Updated " + updated);

outFile = baseFolder + "/" + folderName;

outFile = baseFolder;
long wrote = 0;
File file = new File(outFile);
file.mkdir();
outFile = outFile + "/" + fileName;

OutputStream fwriter = new FileOutputStream(outFile);
wrote = readFromBlob(blob, fwriter);
fwriter.close();
System.out.println("Wrote " + wrote + " bytes to file " + outFile);
}
rs.close();
stmt.close();
conn.close();
}

public static Connection getConnection()
{
Connection con = null;

String dsn = "::";
String uname = "apps";
String passwd = "pwd";
try
{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@" + dsn;
Class.forName(driver);
con = DriverManager.getConnection(url, uname, passwd);
}
catch (Exception ee)
{
ee.printStackTrace();
}

return con;
}

public static long readFromBlob(Blob blob, OutputStream out) throws SQLException, IOException
{
InputStream in = blob.getBinaryStream();


int length = -1;
long read = 0;
byte[] buf = new byte[bBufLen];
while ((length = in.read(buf)) != -1)
{
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}

}