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.

Advertisements

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.

xmllint : a UNIX utility to check for badly-formed XML data

It’s now commonplace to send or receive data files in XML format. And some eBusiness Suite functionality relies on XML configurations files. For example, BI Publisher data definitions are XML-formatted, and if you try to upload a badly-formed data template file to a BI Publisher data definition, Oracle responds with:

Error: The uploaded file XXAP073_BW_USER_RIGHTS_INT_XDO_DATA_TEMPL.xml is invalid. The file should be in XML-DATA-TEMPLATE format.

bi_publisher_data_template_format_error

Not very helpful.

However, there’s a useful UNIX utility that will parse an XML file, and highlight any badly-formed XML syntax. It’s called xmllint:

xmllint --noout ${XML_FILE} 

It will return 0 (zero) if the XML file contains well-formed XML data; if the XML is badly formed, xmllint will list the specific errors. For example…

bash:dave$ XML_FILE='XML_DATAFILE.xml';
bash:dave$ echo ${XML_FILE}
XML_DATAFILE.xml
bash:dave$
bash:dave$ xmllint  --noout ${XML_FILE}
XML_DATAFILE.xml:320: parser error : attributes construct error
      <element name='FIRST_NAME' value='FIRST_NAME'/>;
                                                     ^
XML_DATAFILE.xml:320: parser error : Couldn't find end of Start Tag element line 320
      <element name='FIRST_NAME' value='FIRST_NAME'/>;
                                                     ^
bash:dave$

I’ve used this in file transfer interfaces, to check that the XML file being passed contains well-formed XML data, for example:

xmllint  --noout ${XML_FILE}
VALIDATE_XML=$?

echo 'XML format validation result : ${VALIDATE_XML}'
  
if [ ${VALIDATE_XML} != 0 ]; then
  echo ' ';
  echo 'XML data is badly formed';
fi

Delete a concurrent program definition

During the build/development phase in a development environment, sometimes it’s just easier to delete a concurrent program and start again.

However, this isn’t possible via the Concurrent Programs screen.

If you have DML access to the database, you can simply delete the concurrent program using a standard API in an anonymous PL/SQL block:

BEGIN
  fnd_program.delete_program
  (program_short_name  => 'XXAP004_INACTIVATE_SUPPLIERS'
  ,application         => 'XXAP'
  );
END;