[Oracle EBS] Query to list Reports assigned to a Responsibility

On Oracle E Business Suites, if we want to view any report that attaches to a responsibilities, we can use this following Query.

SELECT fcpl.user_concurrent_program_name "REPORT NAME",

                fnrtl.responsibility_name, frg.request_group_name,

                fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"

           FROM apps.fnd_request_groups frg,

                apps.fnd_request_group_units frgu,

                apps.fnd_concurrent_programs fcp,

                apps.fnd_concurrent_programs_tl fcpl,

                apps.fnd_executables fe,

                apps.fnd_responsibility fnr,

                apps.fnd_responsibility_tl fnrtl

          WHERE frg.application_id = frgu.application_id

            AND frg.request_group_id = frgu.request_group_id

            AND frg.request_group_id = fnr.request_group_id

            AND frg.application_id = fnr.application_id

            AND fnr.responsibility_id = fnrtl.responsibility_id

            AND frgu.request_unit_id = fcp.concurrent_program_id

            AND frgu.unit_application_id = fcp.application_id

            AND fcp.concurrent_program_id = fcpl.concurrent_program_id

            AND fe.execution_method_code LIKE 'P' --'Oracle Reports'

            AND fcp.executable_id = fe.executable_id

            AND fe.application_id = fcp.executable_application_id

            AND fnrtl.responsibility_name LIKE '&Responsibility_Name'

            -- Example Resp. Name : Inventory, Vision Operations (USA)

            AND fnrtl.LANGUAGE = 'US'

            AND fcpl.LANGUAGE = 'US';

For example, we want to view the report that attaches to Global HRMS Manager Responsibilities. Just fill the value from responsibility_name variables into Global HRMS Manager :

Than the result is :

Untuk melihat Report apa saja yang menempel pada suatu responsibilities bisa menggunakan Query diatas.

[Oracle EBS] Query Period Status

Period status didalam Oracle Application (EBS / E-Business Suite) sangat penting. Karena saat mau melakukan posting maupun memproses transaksi dibutuhkan period yang open/future tergantung pada Aplikasi (modul) yang dipergunakan. Suatu perusahaan atau organisasi bisa menggunakan banyak modul seperti General Ledger, Inventory, Payables, Receivables, dan lain-lain. Jika kita ingin memeriksa status period nya open/closed di masing-masing modul tanpa harus berpindah-pindah modul, sehingga bisa menghemat waktu dan tenaga maka bisa di pergunakan query berikut ini. Yang dibutuhkan adalah application_name pada table fnd_application_tl, serta set_of_books_id, period_name pada table gl_period_statuses

SELECT gs.period_name, fa.application_name, gs.closing_status, gs.set_of_books_id,

DECODE (gs.closing_status,

'C','Closed',

'O','Open',

'F','Future',

'W','Closed Pending',

'N','Never Opened',

'P','Permanently Closed'

) "PERIOD_CLOSING_STATUS"

FROM gl.gl_period_statuses gs, apps.fnd_application_tl fa

WHERE fa.application_id = gs.application_id

AND fa.application_name IN

('Payables', 'Receivables', 'General Ledger') --fill with oracle module

AND gs.set_of_books_id = :set_of_books_d --fill with set of books id

AND gs.period_name in ('OCT-14','SEP-14') --fill with Period Nam

and closing_status in ('O','W','C')

group by gs.Period_name, fa.application_name, gs.closing_status, gs.set_of_books_id order by Period_name desc

[Linux] SSL Certificates, Private Keys and CSRs with OpenSSL

OpenSSL is an open-source implementation of the Secure Sockets Layer (SSL v2/v3) and Transport Layer Security (TLS) protocols.

Generate a Private Key and a CSR
This begins the process of generating two files: the Private-Key file for the decryption of your SSL Certificate, and a certificate signing request (CSR) file (used to apply for your SSL Certificate).
 
# openssl req -new -newkey rsa:2048 -nodes -keyout server.key -out server.csr

Generating SSL Certificates
If we would like to use an SSL certificate to secure a service but we do not require a CA-signed certificate, a valid (and free) solution is to sign your own certificates. We can Generate a Self-Signed Certificate from an Existing Private Key that we create before. This command creates a self-signed certificate (server.crt) from an existing private key (server.key).

# openssl req -key server.key -new -x509 -days 1095 -out server.crt

The -x509 option tells req to create a self-signed cerificate. The -days 1095 option specifies that the certificate will be valid for 1095 days (3 Years). A temporary CSR is generated to gather information to associate with the certificate.

[Apache] Redirect HTTP To HTTPS

If we want to redirect our web site to always be sent over SSL (HTTP TO HTTPS). We can do this :

1. Using Virtual Host
Just add this into apache config

<VirtualHost *:80>
    ServerName example.com
    Redirect / https://example.com/
</VirtualHost>

<VirtualHost _default_:443>
   ServerName secure.example.com
   DocumentRoot /usr/local/apache2/htdocs
   SSLEngine On
# etc...
</VirtualHost>

2. Using .htaccess

Redirect permanent /login https://mysite.example.com/login

3. Using mod_rewrite
This config can be used on .htaccess or httpd.conf

RewriteEngine On
# This will enable the Rewrite capabilities

RewriteCond %{HTTPS} !=on
# This checks to make sure the connection is not already HTTPS

RewriteRule ^/?(.*) https://%{SERVER_NAME}/$1 [R,L]
# This rule will redirect users from their original location, to the same location but using HTTPS.

[Linux] Transfer Files Or Folder Using SCP

It is very easy to transfer files on linux operating system, we can use scp (secure copy). scp allows files / folder to be copied to, from, or between different hosts. It uses ssh for data transfer and provides the same authentication and same level of security as ssh.

1. Copy from local host to remote host
scp /path/to/source-file user@remotehost:/path/to/destination-folder/

2. Copy from remote host to local host
 scp username@remotehost:filename /some/local/folder

3. Copy from remote host to remote host
scp username@remotehost1:/some/remote/folder/filename \ username@remotehost2:/some/remote/folder/

[Oracle EBS] Query To Find Application Name And Short Name

This query can be used to lists all the applications related information and to find the APPLICATION_SHORT_NAME of a module (eg. Payables, Receivables, Order Management, etc.)

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   --AND fat.application_name = 'General Ledger' 
 ORDER BY fat.application_name;

[Oracle EBS] Change EBS User Password Using PL/SQL

If you have a locked EBS user account or need to change the user passwords using FND_USER_PKG.changePassword API with PL / SQL then you can use the following script

declare
begin
if FND_USER_PKG.changePassword ('USERNAME','NEW_PASSWORD') 
then
 dbms_output.put_line(1); 
else 
 dbms_output.put_line(2) ;
end if;
end;

Don't forget to commit.