[Oracle EBS] Fixed Asset Error APP-OFA-48392 in Asset Workbench

We have some issue when attempting to add a new asset in Assets Workbench, the following error occurs.

APP-OFA-48392 Assets Transaction upgrade is either running or incomplete for one or more reporting currencies associated with primary depreciation book.  You must first complete the upgrade before entering transactions in this book



[Linux] Upgrade PHP 5 to PHP 7 using Yum on Oracle RHEL 6.3 Santiago

My Apache Web Server was running PHP version 5.3. But my developer need to use PHP 7.0, because they running on Laravel Framework 5.5. So I decided to upgrade PHP 5.3 to PHP 7.0. Although this is a development web server, I don’t want to disturb the existing setup and also, I don’t want to have multiple versions on PHP installed. So it should be a pure upgrade of PHP.

My environment is :

[root@devapp etc]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.3 (Santiago)

Here's what I've done to upgrade PHP.
1. Configure REMI Repository
# wget https://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
# rpm -Uvh epel-release-6-8.noarch.rpm

# wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
# rpm -Uvh remi-release-6.rpm

2. Activate REMI Repository
# yum repolist
# yum-config-manager --enable remi-php70
3. Upgrade PHP 5.3 to PHP 7.0
# yum --showduplicates list php
# yum update php

4. Verify the PHP version
# php -v

5. Restart Web Server
# service httpd restart

[Linux] Install php-mbstring On ORHEL 6.3

I am running Oracle Red Hat Enterprise Linux Server release 6.3 (Santiago) with PHP Version 5.3.3 and Apache/2.2.15 (Oracle) installed.

Problem :
Recently, I have a new apps and got some errors:

Fatal error: Call to undefined function mb_detect_encoding()

This error occurs because the new application requires mbstring extension.

So here is the solution.
1. Check phpinfo();
It seem that extension does not appear in phpinfo.

2. Check Extension
[root@intranet newapps]# rpm -qa | grep php-mbstring
[root@intranet newapps]# rpm -qa | grep php

mbstring extension is not currently installed

3. Search On Yum
[root@intranet newapps]# yum search php-mbstring
Loaded plugins: refresh-packagekit, security
==================================== N/S Matched: php-mbstring ====================================
php-mbstring.x86_64 : A module for PHP applications which need multi-byte string handling

4. Install mbstring extension
[root@intranet newapps]# yum install php-mbstring

5. Restart apache
[root@intranet newapps]# service httpd restart

6. Check phpinfo();
mbstring already installed and appear in phpinfo();
We can edit the mbstring value in /etc/php.ini

7. Check the new apps

[Oracle EBS] ORA-01591 In-Doubt Transactions

Problem :
ORA-01591: lock held by in-doubt distributed transaction.
Saat sedang melakukan query ke database oracle untuk mengambil data tertentu dengan filter/kondisi where, tau2 terjadi error. Padahal itu adalah query dengan kondisi where biasa dan tidak terlalu rumit. Saat dicoba query tanpa kondisi where, tidak terjadi error. Errornya adalah :
ORA-01591: lock held by in-doubt distributed transaction 9.4.660456
Menurut oracle, penjelasan mengenai ORA-01591, adalah sebagai berikut:
ORA-01591 lock held by in-doubt distributed transaction string
Cause: An attempt was made to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action: The database administrator should query the PENDING_TRANS$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, the database administrator should contact the database administrator at the commit point if known or the end user for correct outcome, or use heuristic default if given to issue a heuristic COMMIT or ABORT command to finalize the local portion of the distributed transaction.
Cause :
Kondisi error tersebut disebut dengan In-Doubt Transactions.  Error terjadi saat kita mempunyai 2 buah proses transaction yang bersamaan dan saling menunggu untuk melakukan commit atau rollback sedangkan transaction yang ditunggu sudah crash/dead sehingga kondisi commit tidak pernah terjadi sehingga terjadilah kondisi lock. Hal itu terjadi karena :
– Mesin server yang menjalankan Oracle Database crash
– Koneksi Jaringan yang terputus saat terjadi proses transaction
– Kesalahan Aplikasi.
Penjelasan lebih lengkap bisa dilihat di Oracle® Database Administrator’s Guide

Solution :
1. Masuk ke sqlplus atau toad atau tools lainnya, connect sebagai sysdba.

2. Jalankan querty untuk melakukan pengecekan transaction id
SQL> select local_tran_id from dba_2pc_pending;

3. Lakukan rollback.
SQL> ROLLBACK FORCE '9.4.660456';
Rollback complete.
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('9.4.660456');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

4. Cek lagi
SQL> select local_tran_id from dba_2pc_pending;
no rows selected

Oke done. Problem solved.

[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,
'W','Closed Pending',
'N','Never Opened',
'P','Permanently Closed'
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.