Change Oracle Apps password from Backend

SET SERVEROUTPUT ON;

DECLARE
      flag BOOLEAN;
BEGIN
      flag := fnd_user_pkg.changepassword(username=> 'MY_USER'
                                          ,newpassword => 'welcome1');
    IF flag
     THEN
           DBMS_OUTPUT.PUT_LINE('Your Password has been successfully reset');
     ELSE
           DBMS_OUTPUT.PUT_LINE('Password reset has failed');
     END IF;
END;
/
COMMIT;

Query to check Responsibilities Assigned to an Apps User

SELECT fu.user_name,
       fr.responsibility_name,
       furg.START_DATE,
       furg.END_DATE
  FROM fnd_user_resp_groups_direct furg,
       fnd_user fu,
       fnd_responsibility_tl fr
 WHERE     fu.user_name = UPPER ('&user_name')
       AND furg.user_id = fu.user_id
       AND furg.responsibility_id = fr.responsibility_id
       AND fr.language = USERENV ('LANG');

Execution History of an SQL ID

-- This script allows you to view the execution history of a SQL ID. Use it to investigate things like:

-- 1. Changing plan hash values.

-- 2. High elapsed time per execution.

-- 3. High % of cluster waits.

-- 4. Whether a SQL profile is being used.

SELECT   snap_id "Snap"
       , sql_id "SQL ID"
       , plan_hash_value "Plan"
       , end_interval_time "End Time"
       , executions "Execs"
       , ROUND(CASE WHEN executions > 0 THEN elapsed_time / executions ELSE elapsed_time END, 4) "ElapPexec"
       , ROUND(CASE WHEN executions > 0 THEN rows_processed / executions ELSE rows_processed END, 2) "RowPexec"
       , ROUND(CASE WHEN executions > 0 THEN buffer_gets / executions ELSE buffer_gets END, 0) "GetPexec"
       , ROUND(CASE WHEN executions > 0 THEN disk_reads / executions ELSE disk_reads END, 2) "ReadPexec"
       , ROUND(CASE WHEN executions > 0 THEN gc_time / executions ELSE gc_time END, 4)  "GCpExec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (gc_time / elapsed_time) ELSE 0 END, 2)  "GC %"
       , ROUND(CASE WHEN executions > 0 THEN cpu_time / executions ELSE cpu_time END, 4)  "CPUpExec"
       , ROUND(CASE WHEN executions > 0 THEN ccwait / executions ELSE ccwait END, 4)  "ConcPexec"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (cpu_time / elapsed_time) ELSE 0 END, 2)  "CPU %"
       , ROUND(CASE WHEN elapsed_time > 0 THEN 100 * (ccwait / elapsed_time) ELSE 0 END, 2)  "Conc %"
       , ROUND(elapsed_time, 1) "Elapsed"
       , parsing_schema_name "Schema"
       , sql_profile "SQL profile"
       , min_inst_id "LowInst"
       , max_inst_id "HghInst"
       , ROUND(cpu_time, 1) "CPU"
       , buffer_gets "Gets"
       , disk_reads  "Reads"
       , ROUND(gc_time, 1) "Cluster"
       , rows_processed "Rows"
       , ROUND(ccwait, 1) "Concurrency"
       , ROUND(CASE WHEN executions > 0 THEN plsql_time / executions ELSE plsql_time END, 4) "PLSpExec"
       , ROUND(CASE
                  WHEN executions > 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / executions / rows_processed / 1000000
                  WHEN executions = 0
AND                    rows_processed > 0
                  THEN
                     elapsed_time / rows_processed / 1000000
                  ELSE
                     0
               END
             , 4)
            "ElapProw"
       , sql_text "SQL Text"
FROM     (SELECT   s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI') end_interval_time
                 , SUM(sq.executions_delta) executions
                 , SUM(sq.disk_reads_delta) disk_reads
                 , SUM(sq.buffer_gets_delta) buffer_gets
                 , SUM(sq.rows_processed_delta) rows_processed
                 , SUM(sq.cpu_time_delta) / 1000000 cpu_time
                 , SUM(sq.plsexec_time_delta) / 1000000 plsql_time
                 , SUM(sq.clwait_delta) / 1000000 gc_time
                 , SUM(sq.ccwait_delta) / 1000000 ccwait
                 , SUM(elapsed_time_delta) / 1000000 elapsed_time
                 , sq.force_matching_signature
                 , MIN(sq.instance_number) min_inst_id
                 , MAX(sq.instance_number) max_inst_id
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1) sql_text
          FROM     dba_hist_sqlstat sq, dba_hist_snapshot s, dba_hist_sqltext sl
          WHERE    1 = 1
          AND      sq.sql_id = :sqlid
          --and   sq.plan_hash_value=3983895616
          AND      s.snap_id = sq.snap_id
          --and   s.snap_id between 37279 and 37305
          AND      TRUNC(s.end_interval_time, 'MI') > SYSDATE - 90
          --and   sq.parsing_schema_name in ('APPS_FR')
          AND      s.dbid = sq.dbid
          AND      sq.dbid = sl.dbid
          AND      s.instance_number = sq.instance_number
          AND      sq.sql_id = sl.sql_id
          AND      sq.elapsed_time_delta > 0
          GROUP BY s.snap_id
                 , sq.sql_id
                 , sq.plan_hash_value
                 , sq.force_matching_signature
                 , sq.parsing_schema_name
                 , TRUNC(s.end_interval_time, 'MI')
                 , sq.sql_profile
                 , DBMS_LOB.SUBSTR(sl.sql_text, 200, 1))
ORDER BY 1 DESC;

-- Once you have the SQL, use the following SQL to extract all the execution plans:

SELECT * FROM TABLE(DBMS_XPLAN.display_awr(sql_id => :sqlid , format => 'ADVANCED -ALIAS -OUTLINE'))

Oracle Apps: Find Fields in DFFs

1. Query to Find All the fields in a particular DFF:-

SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.fnd_application_vl ap
           WHERE     ffv.descriptive_flexfield_name =
                        att.descriptive_flexfield_name
                 AND ap.application_id = ffv.application_id
                 AND ffv.descriptive_flexfield_name =
                        ffc.descriptive_flexfield_name
                 AND ffv.application_id = ffc.application_id
                 AND ffc.descriptive_flex_context_code =
                        att.descriptive_flex_context_code
                 AND fvs.flex_value_set_id = att.flex_value_set_id
                 AND ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title;


2. Query to Find Only Required fields in a particular DFF :-

SELECT *
  FROM (  SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.fnd_application_vl ap
           WHERE     ffv.descriptive_flexfield_name =
                        att.descriptive_flexfield_name
                 AND ap.application_id = ffv.application_id
                 AND ffv.descriptive_flexfield_name =
                        ffc.descriptive_flexfield_name
                 AND ffv.application_id = ffc.application_id
                 AND ffc.descriptive_flex_context_code =
                        att.descriptive_flex_context_code
                 AND fvs.flex_value_set_id = att.flex_value_set_id
                 AND ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title)
 WHERE required_flag = 'Y';

MVC Architecture

•  MODEL
•  VIEW
•  CONTROLLER

How the start with CONNECT BY clause in Oracle works

This page explains the hierarchical query using the (start with) connect by clause in an Oracle SQL Query.

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause (START WITH condition1) CONNECT BY condition2 

The START WITH clause is optional and specifies the rows athat are the root(s) of the hierarchical query. If you omit this clause, then Oracle uses all rows in the table as root rows. The START WITH condition can contain a subquery, but it cannot contain a scalar subquery expression. 

The CONNECT BY clause specifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition can be any condition, however, it must use the PRIOR operator to refer to the parent row. Restriction on the CONNECT BY clause: The connect_by_condition cannot contain a regular subquery or a scalar subquery expression. 

The PRIOR operator to refer to the parent row can be used as following: 

Sample query on the employees table:

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

shows employee_id, last_name, manager_id and level in the tree for the employee hierarchy. 

In Oracle 9i a new feature for hierarchical queries is added: sys_connect_by_path It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column. 
Examples
The following example returns the path of employee names from employee Kochhar to all employees of Kochhar (and their employees): 

SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

In Oracle 10 3 new features for hierarchical queries are added:

connect_by_root

CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. Restriction on CONNECT_BY_ROOT: You cannot specify this operator in the START WITH condition or the CONNECT BY condition. Example query:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

connect_by_isleaf

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy. Example

SELECT employee_id, last_name, manager_id, connect_by_isleaf "IsLeaf"
   FROM employees
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id;

connect_by_iscycle

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0. 
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

Oracle String Functions

Function	Description
ASCII	Returns the number code that represents the specified character
ASCIISTR	Converts a string in any character set to an ASCII string using the database character set
CHR	Returns the character based on the number code
COMPOSE	Returns a Unicode string
CONCAT	Allows you to concatenate two strings together
Concat with ||	Allows you to concatenate two or more strings together
CONVERT	Converts a string from one character set to another
DECOMPOSE	Accepts a string and returns a Unicode string
DUMP	Returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
INITCAP	Sets the first character in each word to uppercase and the rest to lowercase
INSTR	Returns the location of a substring in a string
INSTR2	Returns the location of a substring in a string, using UCS2 code points
INSTR4	Returns the location of a substring in a string, using UCS4 code points
INSTRB	Returns the location of a substring in a string, using bytes instead of characters
INSTRC	Returns the location of a substring in a string, using Unicode complete characters
LENGTH	Returns the length of the specified string
LENGTH2	Returns the length of the specified string, using UCS2 code points
LENGTH4	Returns the length of the specified string, using UCS4 code points
LENGTHB	Returns the length of the specified string, using bytes instead of characters
LENGTHC	Returns the length of the specified string, using Unicode complete of characters
LOWER	Converts all letters in the specified string to lowercase
LPAD	Pads the left-side of a string with a specific set of characters
LTRIM	Removes all specified characters from the left-hand side of a string
NCHR	Returns the character based on the number code in the national character set
REGEXP_INSTR	Returns the location of a regular expression pattern in a string
REGEXP_REPLACE	Allows you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching
REGEXP_SUBSTR	Allows you to extract a substring from a string using regular expression pattern matching
REPLACE	Replaces a sequence of characters in a string with another set of characters
RPAD	Pads the right-side of a string with a specific set of characters
RTRIM	Removes all specified characters from the right-hand side of a string
SOUNDEX	Returns a phonetic representation (the way it sounds) of a string
SUBSTR	Allows you to extract a substring from a string
TRANSLATE	Replaces a sequence of characters in a string with another set of characters
TRIM	Removes all specified characters either from the beginning or the end of a string
UPPER	Converts all letters in the specified string to uppercase
VSIZE	Returns the number of bytes in the internal representation of an expression