How to find assigned Profile values from backend at different levels

This query will give the values of the profile option set at different level..
just use the profile option name or user profile option name which you see in the front end.
to query

SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
--and e.profile_option_name IN ('FND_BRANDING_SIZE','ASO_COMP_LOGO')
and f.user_profile_option_name in ('FND: Branding Size')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
and f.PROFILE_OPTION_NAME(+)=e.profile_option_name
ORDER BY e.profile_option_name;

How to find Queries Running in the Database(V$SQL) and also how to kill sessions

some times we want to know what are queries that are getting executed when any process is run..one way of finding it is using the V$Sql..the SID and Serial# can be used to kill the session in case of long running queries
set pagesize 24
set newpage 1
set linesize 125
column sql_text format a100
column user_name format a12
select b.sid, b.serial# , 
substr(b.username,1,12) user_name, 
a.sql_text
from v$sql a, v$session b
where a.hash_value = b.sql_hash_value
and sql_text not like '%from v$sql s, v$session u%'
order by b.sid;


SQL> @cur_sql
sid serial user_name sql_text
5 390 apps select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an d t.bitmapped=0

To kill a session use the below command
ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390';

Refer LOGO Location From XML

Follow the below steps for ‘Refer LOGO location from XML’

1. Insert a dummy image in template.
2. In the format picture dialog box select the web tab.  
3. Enter the following syntax in the Alternative text region to reference the image URL:

url: {IMAGE_LOCATION}

Where IMAGE_LOCATION is an element from our XML file that holds the full URL to the image .

We can also build a URL based on multiple elements at run time.

Just use the Concat function to build the URL string.

For example:

url: {concat(SERVER,’ / ‘,IMAGE_DIR,’ / ‘,IMAGE_FILE)}

This method can also be used with the OA_MEDIA reference as follows:

url: {concat{‘${OA_MEDIA}’ , ‘ / ‘,IMAGE_FILE)}

Inserting A Static/Dynamic LOGO in Header

The Logo in the rtf template can be included as a Static image (or) as a Dynamic image.

1.) The Logo in the RTF template can be included as a static image as follows.
Place the cursor in the place where you need to insert the logo. Select the image from your desktop by selecting the option

Insert -> Picture from the menu.

Browse for the Image which you want to display and select the image.

After selecting image you can see the image which you are selected, the below figure shows the example which displays Newton Apples logo:-

This is how you can include a Static Logo.

2.)    Inserting the Logo Dynamically.

As of today business the logo may change periodically for a company. If the LOGO has been inserted as a static object then the developer needs to go through each of the RTF template and needs to change the same.

But with the help of this method the logo can be reflected in the entire RTF templates by changing it in one place.

The logo can be inserted dynamically with the help of a URL.

The URL may be the path in the server (or) web address where the LOGO has been placed.
This can be done as follows

?

1 2	url:{’http://www.oracle.com/images/ora_log.gif’} url:{’${OA_MEDIA}/ORACLE_LOGO.gif’}

Insert a dummy image at the place where you need to have a logo.

Select the image and go through the format Picture -> Alt Text  and mention the URL in description.

Inline View

When you use SQL Subquery in From clause of the select statement it is called inline
view.
A common use for inline views in Oracle SQL is to simplify complex queries by
removing join operations and condensing several separate queries into a single query.
A subquery which is enclosed in parenthesis in the FROM clause may be given an
alias name. The columns selected in the subquery can be referenced in the parent
query, just as you would select from any normal table or view.

Example 1 of Inline View
Display the top five earner names and salaries from the EMPLOYEES table:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;

Example 2 of Inline View
Calculate the number of employees in each department
SELECT d.dept_id, d.name, emp_cnt.tot
FROM department d, (SELECT dept_id, count(*) tot
FROM employee
GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;

Using Oracle in-line views:

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

The best example of the in-line view is the common Oracle DBA script that is used to show the amount of free space and used space within all Oracle tablespaces. Let’s take a close look at this SQL to see how it works. Carefully note that the FROM clause in this SQL query specifies two sub-queries that perform summations and grouping from two views, dba_data_files, and dba_free_space.

In ANSI standard SQL, it is quite difficult to compare two result sets that are summed together in a single query, and this is a common problem with Oracle SQL where specific values must be compared to a summary.  Without the use of an in-line view, several separate SQL queries would need to be written, one to compute the sums from each view and another to compare the intermediate result sets.

This is a great report for display the actual amount of free space within an Oracle tablespace.

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
colimn "Total MB"   format 99,999,999

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

This SQL quickly compares the sum of the total space within each tablespace to the sum of the free space within each tablespace. Here is a sample of the output:

Basically, this query needs to compare the sum of total space within each tablespace with the sum of the free space within each tablespace.

Inserting Page Numbers

In this lesson we are going to know how to insert page numbers in RTF.

Let us create a simple RDF by using following query:

?

1 2 3 4 5 6 7 8 9

SQL>SELECT  pov.vendor_name,         ai.invoice_date,         ai.invoice_amount,         ai.payment_method_lookup_code,         ai.set_of_books_id,         ai.approved_amount,         FROM po_vendors pov, ap_invoices_all ai  WHERE pov.vendor_id = ai.vendor_id and rownum<100

 
The below picture shows the Data Model of RDF:
 

Now generate XML as we know how to generate and save .xml file in appropriate location which is used to load data into RTF for future reference.

Now we have to construct an RTF which is shown in the below picture:

By seeing above pictures we have to count how many pages are there.

With out counting pages if we insert page numbers then we can easily know how many pages are there in output report and on which page we are currently.

Now let us see how to insert pages in the RTF

The below picture shows how a sample RTF looks like and Place the cursor in the box ” Pages:-1 OF “.

Now we have to know how many pages are there in the output Report.

For that, place the cursor in the same box which is shown below

After placing the cursor then go to Insert menu and there select Field option, the same scenario is shown in the below picture:

From the above picture we can see lot of options there select Numpages option, you can see in below picture which shows that how many pages are there i.e.… total pages

After selecting Numpages option the RTF looks like:

Now we have to publish the report

Click on Template Builder => Preview => HTML/PDF/Excel/RTF (any format).

See the below sample which is in PDF format:

Next page .here, in the below output we are not seeing that at which page we are

So if we want to display page numbers for every page we should have to place page numbers inheader and footer which is mandatory and is shown below

Now we have to publish the report

Click on Template Builder => Preview => HTML/PDF/Excel/RTF (any format).

We can get below output

In the below output we can see Pages: -1 OF   4

Next page.    Here, in the below output we can see Pages: – 2 OF 4

Still now we have seen the way  that how to see in which page we are currently out of all pages.