SQL : HR Position Hierarchy – to count subordinates below a given position

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
;
Advertisements

Oracle SQL Developer and JDeveloper : all-resources.dat files are getting too big

On a Windows 7 PC, these files were getting enormous, and causing problems with the size of my roaming profile. Metalink Note ID 1943500.1 gives a solution for SQL Developer, which also seems to work for JDeveloper.

The all-resources.dat file can be found in:

C:\Users\UserName\AppData\Roaming\SQL Developer\system\system_cache\var\cache

and

C:\Users\UserName\AppData\Roaming\JDeveloper\system\system_cache\var\cache

The fix is as follows…

Add the following text to the sqldeveloper.conf file (in the sqldeveloper\bin directory):

AddVMOption -Dorg.netbeans.core.update.all.resources=never

For JDeveloper, add the same line of text to the jdev.conf file (in the jdev\bin directory).

The all-resources.dat files can then be permanently deleted (Shift-Delete) to free up a considerable amount of roaming profile space.

OPENSSH: How to Create a Public/Private Key Pair

Install the OpenSSH utility on the machine which will instigate a secure connection request, by downloading the source files and copying them to (say) C:\ OPENSSH.

Open a command console (e.g. Windows cmd), and run the following command, from C:\ OPENSSH:

ssh-keygen -b 2048 -t rsa
Enter file in which to save the key: test_key
Enter passphrase: [create a passphrase, or leave blank for no passphrase]

Two files will be created:

test_key
test_key.pub

The test_key.pub file contains the public key (which can always be derived from the private key), and which can be provided to a third party to secure an SFT connection, for example.

Sun Solaris UNIX: How to Encrypt and Decrypt a File

SFTP can be used to encrypt a file in transit, but what if file-encryption-at-rest is required?

To encrypt a file “at rest” (i.e. on the file system), two things are required:

  1. the encryption algorithm must be chosen;
  2. an encryption “key” must be generated (or provided by a third party) – this is used to encrypt, and subsequently decrypt, the file.

In this example, a random key is generated in a 3DES format, using the dd utility:

dd if=/dev/urandom of=$HOME/key.3des.24 bs=24 count=1

…where:

  • if = file indicates a random key, using the /dev/urandom file
  • of = keyfile is the output file that holds the generated key
  • bs = n is the key size in bytes. For the length in bytes, divide the key length in bits by 8.
  • count = n is the count of the input blocks. The number for n should be 1.

The maximum and minimum key sizes (in bits, not bytes) can be determined using:

encrypt -l

…which gives:

Algorithm Keysize:        Min   Max (bits)
------------------------------------------
aes                       128   256
arcfour                     8  2048
des                        64    64
3des                      128   192
camellia                  128   256

The newly-generated key file, $HOME/key.3des.24, can then be used to encrypt a TEST.csv file (using the 3DES algorithm), using:

encrypt -a 3des -k $HOME/key.3des.24 -i ./TEST.csv -o ./e.TEST.csv

…and decrypted using:

decrypt -a 3des -k $HOME/key.3des.24 -i ./e.TEST.csv -o ./u.TEST.csv

	

SQL : Query to generate a calendar listing

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
)
;

SQL : Generating Query Output in XML Format

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
;

Incomplete invoice accounting events after releasing a hold using ap_holds_pkg.release_single_hold

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
);

SQL Query to identify Functions, Menus, Responsibilities and Users

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)
;

OA Framework: How to list all parameter names and values in a page

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.

SQL – an example of a tree-walking query for approval hierarchy reporting

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…

apprvl_hierarchy_crop

…is stored like this in database table:

apprvl_hierarchy_tbl

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:

apprvl_hierarchy_sql

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.