WITH xx_hierarchy AS
-- -------------------------------------------------------------------------
-- Form XX position hierarchy, of parent and child position_id's
-- -------------------------------------------------------------------------
(
SELECT pse.parent_position_id        parent_pos_id
,      pse.subordinate_position_id   child_pos_id
,      (
       -- Count number of active Employee holders of each child position
       SELECT COUNT(*)
       FROM   per_assignments_x             pax
       ,      per_people_x                  ppx
       ,      per_person_type_usages_x      pptux
       ,      per_person_types              ppt
       ,      hr_all_organization_units     haou
       WHERE  1 = 1
       AND    pax.position_id = pse.subordinate_position_id
       AND    pax.primary_flag(+) = 'Y
       AND    pax.person_id = ppx.person_id
       AND    pptux.person_id = ppx.person_id
       AND    ppt.person_type_id = pptux.person_type_id
       AND    ppt.user_person_type = 'Employee'
       AND    haou.organization_id = pax.organization_id
       )    num_of_holders
FROM   per_pos_structure_elements    pse 
WHERE  1 = 1
AND    pse.pos_structure_version_id = (
                                      SELECT ppsv.pos_structure_version_id 
                                      FROM   per_position_structures     pps     
                                      ,      per_pos_structure_versions  ppsv 
                                      WHERE  pps.position_structure_id = ppsv.position_structure_id 
                                      AND    pps.name = 'XX Hierarchy'
                                      AND    SYSDATE BETWEEN ppsv.date_from AND NVL(ppsv.date_to,SYSDATE + 1)
                                      )
)
,
xx_subordinates AS
-- -------------------------------------------------------------------------
-- For each position, scan the hiearchy downwards, and calculate the total
-- number of  Employee holders of all subordinate positions
-- -------------------------------------------------------------------------
(
SELECT pp.position_id   parent_pos_id
,      pp.name          parent_pos_name
,      pj.name          parent_job_name
,      (
       SELECT SUM(xxh_sub.num_of_holders)
       FROM   xx_hierarchy   xxh_sub
       CONNECT BY PRIOR xxh_sub.child_pos_id = xxh_sub.parent_pos_id
       START WITH xxh_sub.parent_pos_id = pp.position_id
       )    AS count_subordinates
FROM   per_positions      pp
,      per_jobs           pj
WHERE  1 = 1
AND    pp.job_id = pj.job_id
)
-- -------------------------------------------------------------------------
-- List active Employees, their current position, and their total number of
-- subordinate employees
-- -------------------------------------------------------------------------
SELECT ppx.full_name
,      ppx.employee_number
,      haou.name                      org_name
,      xxs.parent_pos_name            pos_name
,      xxs.parent_job_name
,      NVL(xxs.count_subordinates,0)  count_subordinates
FROM   xx_subordinates                xxs
,      per_people_x                   ppx
,      per_assignments_x              pax
,      per_person_type_usages_x       pptux
,      per_person_types               ppt
,      hr_all_organization_units      haou
WHERE  1 = 1
AND    pax.person_id = ppx.person_id
AND    pax.primary_flag = 'Y'
AND    pax.position_id = xxs.parent_pos_id
AND    pptux.person_id = ppx.person_id
AND    ppt.person_type_id = pptux.person_type_id
AND    ppt.user_person_type = 'Employee'
AND    haou.organization_id = pax.organization_id
ORDER BY ppx.full_name
;

Instead of creating and populating a table containing calendar entries, why not write a query to generate the listing at run time. For example, the last 24 months:

SELECT *
FROM
(
WITH month_counter AS 
(
SELECT LEVEL-1 AS id 
FROM   dual 
CONNECT BY LEVEL <= 24
) 
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -id),'YYYY')    year
,      ADD_MONTHS((SYSDATE), -id - 1) + 1 first_day
,      ADD_MONTHS(LAST_DAY(SYSDATE), -id) last_day
,      TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -id),'Month')   month
,      TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -id),'Mon-YY')  period_name
FROM   month_counter
)
;

For example, output the rows of a value set in XML format:

SELECT XMLElement("GL_NOMINAL"
                 ,XMLForest(ffv.flex_value  AS "VALUE"
                           ,ffv.description AS "DESC"
                           )
                 )
FROM   fnd_flex_values_vl ffv
,      fnd_flex_value_sets ffvs
WHERE  ffv.flex_value_set_id = ffvs.flex_value_set_id
AND    ffvs.flex_value_set_name = 'GL_NOMINAL'
AND    ffv.enabled_flag = 'Y'
AND    ffv.summary_flag = 'N'
ORDER BY ffv.flex_value ASC
;

During the course of supplier invoice processing in Oracle eBusiness Suite, it may be necessary to release invoice holds programmatically, to render invoices eligible for payment. The packaged procedure

ap_holds_pkg.release_single_hold

…can be used for this purpose.

For example:

 ap_holds_pkg.release_single_hold
(x_invoice_id => p_invoice_id
,x_hold_lookup_code => v_hold_lookup_code
,x_release_lookup_code => v_release_lookup_code
,x_held_by => NULL
--,x_calling_sequence => v_calling_sequence
); 

However, this can lead to create-accounting issues, as it appears that the method results in incomplete invoice accounting events in the Sub-Ledger Accounting (SLA) tables. This can be remedied by calling another seeded procedure after releasing holds, to effectively refresh the accounting events:

ap_accounting_events_pkg.update_invoice_events_status

For example:

ap_accounting_events_pkg.update_invoice_events_status
(p_invoice_id       => p_invoice_id
,p_calling_sequence => v_calling_sequence
);

The following query will list Menus, Responsibilities and Users for a specific Function:

SELECT fffv.user_function_name
,      fmv.menu_name
,      frv.responsibility_name
,      user_name
FROM   fnd_form_functions_vl fffv
,      fnd_menu_entries_vl fmev
,      fnd_menus_vl fmv
,      fnd_responsibility_vl frv
,      fnd_user fu
,      fnd_user_resp_groups_direct furgd
WHERE  fffv.function_name = 'SYSTEM_ADMINISTRATOR'
AND    fffv.function_id = fmev.function_id
AND    fmv.menu_id = fmev.menu_id
AND    fmv.menu_id = frv.menu_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(fu.start_date) AND NVL(TRUNC(fu.end_date),SYSDATE + 1)
AND    fu.user_id = furgd.user_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(furgd.start_date) AND NVL(TRUNC(furgd.end_date),SYSDATE + 1)
AND    frv.responsibility_id = furgd.responsibility_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(frv.start_date) AND NVL(TRUNC(frv.end_date),SYSDATE + 1)
;

The java utility java.util.Enumeration can be used to list the parameter names and values “in” a framework page at run time.

Add the following code snippet to both the ProcessRequest and ProcessFormRequest methods of the page controller:

 import java.util.Enumeration;

Enumeration enums = pageContext.getParameterNames();
while(enums.hasMoreElements())
{
String paramName = enums.nextElement().toString();
// if (paramName.equalsIgnoreCase("event")){
System.out.println("Parameter Name " + paramName + " : Parameter Value " + pageContext.getParameter(paramName));
// }
} 

You can un-comment the if statement to look for specific parameter names and values.

An approval hierarchy can be concisely represented in a 2-column table – one column each for subordinate and supervisor. A “subordinate” with no supervisor indicates that this person is at the top of the approval hierarchy. Conversely, subordinates who don’t appear in the supervisor column must be at the foot of the hierarchy – the “leaf nodes” in a hierarchical tree.

So the following hierarchy…

…is stored like this in database table:

The business reporting requirement is to generate lists of users corresponding to each unique path up the hierarchy from the lowest users (the leaf nodes) to the highest (SMITH – the “root node”). In the diagram above, the leaf nodes are coloured orange.

An SQL query to generate this information can be constructed as follows…

Firstly, the users in supervisors/subordinates table could be validated to ensure that they are current users, with employee records in Oracle HRMS:

SELECT xh.subordinate   user_name
,      xh.supervisor    supervisor_name
FROM   xx_hierarchy     xh
,      fnd_user         fu_super
,      fnd_user         fu_sub
WHERE  1 = 1
AND    fu_super.user_name = xh.supervisor
AND    NVL(fu_super.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND    fu_super.employee_id IS NOT NULL              -- Is Employee
AND    fu_sub.user_name = xh.subordinate
AND    NVL(fu_sub.end_date, SYSDATE + 1) > SYSDATE   -- Active User
AND    fu_sub.employee_id IS NOT NULL                -- Is Employee

The approval hierarchy can be traversed using an SQL query and the START WITH and CONNECT BY clauses. However, it can be troublesome to join to other tables when these clauses are used, and therefore a series of in-line views can be used in a “master” SQL statement.

WITH bw_approvers AS
(
SELECT xh.subordinate   user_name
,      xh.supervisor    supervisor_name
FROM   xx_hierarchy     xh
,      fnd_user         fu_super
,      fnd_user         fu_sub
WHERE  1 = 1
AND    fu_super.user_name = xh.supervisor
AND    NVL(fu_super.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND    fu_super.employee_id IS NOT NULL              -- Is Employee
AND    fu_sub.user_name = xh.subordinate
AND    NVL(fu_sub.end_date, SYSDATE + 1) > SYSDATE   -- Active User
AND    fu_sub.employee_id IS NOT NULL                -- Is Employee
)
SELECT ba.user_name                                  user_name
,      ba.supervisor_name                            supervisor_name
,      LEVEL                                         lev
,      CONNECT_BY_ISLEAF                             is_leaf
,      SYS_CONNECT_BY_PATH(ba.user_name,'|') || '|'  connect_path
FROM   bw_approvers   ba
START WITH ba.supervisor_name IS NULL
CONNECT BY PRIOR ba.user_name = ba.supervisor_name
ORDER BY level
,      ba.user_name

Here we start at the root node of the hierarchy (START WITH), and use CONNECT BY PRIOR to join the previous subordinate to the current supervisor.

A few more keywords yield some useful information about the hierarchy:

LEVEL – “1” for the root node, “2” for the next subordinate level down, and so on;
CONNECT_BY_ISLEAF – “1” for a leaf node, otherwise “0”;
SYS_CONNECT_BY_PATH – a pipe-separated list of users between the current user and the root node.

The above query yields the following results:

So-far-so-good. Next, we want to identify the leaf nodes, so that we can list the users in the corresponding paths through the hierarchy to the root node.

WITH bw_approvers AS
(
SELECT xh.subordinate   user_name
,      xh.supervisor    supervisor_name
FROM   xx_hierarchy     xh
,      fnd_user         fu_super
,      fnd_user         fu_sub
WHERE  1 = 1
AND    fu_super.user_name = xh.supervisor
AND    NVL(fu_super.end_date, SYSDATE + 1) > SYSDATE -- Active User
AND    fu_super.employee_id IS NOT NULL              -- Is Employee
AND    fu_sub.user_name = xh.subordinate
AND    NVL(fu_sub.end_date, SYSDATE + 1) > SYSDATE   -- Active User
AND    fu_sub.employee_id IS NOT NULL                -- Is Employee
)
, bw_hierarchy AS
(
SELECT user_name                                     user_name
,      supervisor_name                               supervisor_name
,      LEVEL                                         lev
,      CONNECT_BY_ISLEAF                             is_leaf
,      SYS_CONNECT_BY_PATH(ba.user_name,'|') || '|'  connect_path
FROM   bw_approvers   ba
START WITH ba.supervisor_name IS NULL
CONNECT BY PRIOR ba.user_name = ba.supervisor_name
ORDER BY level
,      ba.user_name
)
, bw_leaf AS
-- List users with no subordinates - leaf nodes;
(
SELECT bh.user_name
,      bh.supervisor_name
,      bh.connect_path
FROM   bh.bw_hierarchy   bh
WHERE  bh.is_leaf = 1
)
SELECT ba.user_name             USER_NAME
,      bl.user_name             LEAF_USER
,      ROW_NUMBER() OVER
         (
         PARTITION BY  bl.user_name
         ORDER BY      bl.user_name ASC
         ,             INSTR('|'||bl.connect_path,ba.user_name||'|')  DESC
         )                      PRIORITY
,      bl.connect_path          CONNECT_PATH
FROM   bw_leaf         bl
,      bw_approvers    ba
WHERE  1 = 1
AND    -- the approver is in the connect_path
       INSTR('|'||bl.connect_path,ba.user_name||'|') > 0
ORDER BY bl.user_name ASC
,      INSTR('|'||bl.connect_path,ba.user_name||'|')  DESC
;

Bingo! Job done.

Incidentally, the ROW_NUMBER() pseudo-column yields a count of the level from leaf to root, relative to the leaf node.