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 ;
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:
- the encryption algorithm must be chosen;
- 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…
…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.