Category: Oracle Applications (R12.0.4)

Virtualize your desktop/laptop computer

On By windows7bugsIn Linux, NOT a DBA, Oracle Applications (R12.0.4), Oracle Enterprise Linux, Oracle VirtualBox, UncategorizedLeave a comment

Hello guys

This is my first post for the year 2016. Actually I have more drafts than posts this time with me and I truly hope that I will able to finish all of them in few weeks time. I’m sure you are interested about Oracle 12c products on Windows platform. Stay tuned, am on it

Coming back to the topic, I have a decent desktop computer that could be called a “half server” with the following configurations

  1. i7 processor 2nd generation
  2. 16GB DDR3 memory
  3. Around 4.5-5 terabyte of storage
  4. windows 10 64Bit Professional

I came across VMWare always 8 years back, liked it, and used it until they started charging for everything. Then Oracle VirtualBox (Earlier JAVA VirtualBox) started giving tough fight and it has become quite famous among developer communities where NOT many were truly able to afford VMWare’s paid software.

VMWare is altogether a different standard made for sophisticated environments, hence we will never compare both the products here. Our intention is to point you towards the advantages of using Virtual Machines that could less clutter your rig, give you better exposure to different technologies and a bit of networking etc

So our product of interest for this post is Oracle VirtualBox and we will see how we can utilize our existing desktops/laptops to run multiple virtual machines at the same time and thus utilize the available hardware to the maximum extends

So prior attempting to virtualize your existing computer, you need to know what kind of hardware you have and whether your computer meets minimum requirements to support virtualization.

So today is 3rd January 2016 & if your computer is 4 years old or less,  99.9% chances that your rig sure supports virtualization. Most of BIOS comes with the virtualization enabled by default.

Yet we should make sure that our computers support virtualization. Read the instructions provided here to find out whether your current processor supports virtualization OR

Just install Oracle VirtualBox and try to create a VM. You will immediately come to realize whether your rig really supports virtualization ;)

Well, that’s the brute force way of doing stuffs, adapt the one that defines you. If you are using Windows 10, I’ve noticed that many default installations enable Hyper-V by default. You need to disable it from add remove windows features console, so that you can create 64Bit VMs using Oracle VirtualBox. This post explains how to enable it, just do the opposite to disable it

Before anything else, you need to identify your processor, it’s capabilities. A nice comparison is available here for i3, i5 & i7 processors and definitely, the author favors i7 processors. Please spend few minutes to read about the differences between these three different processors.

A ripoff from http://www.pcadvisor.co.uk

What the difference between Core i3, i5, i7: Hyper-Threading

A thread in computing terms is a sequence of programmed instructions that the CPU has to process. If a CPU has one core, it can process only one thread at once, so can only do one thing at once (as before, it’s actually more complex than this, but the aim here is to keep it simple and understandable).

Hence, a dual-core CPU can process two threads at once, a quad-core four threads at once. That’s twice or four times the work in the same amount of time.

Hyper-Threading is a clever way to let a single core handle multiple threads.

A Core i3 with Hyper-Threading can process two threads per core which means a total of four threads can run simultaneously. The current Core i5 range doesn’t have Hyper-Threading so can also only process four cores. i7 processors do have it, so can process eight threads at once. Combine that with 8MB of cache and Turbo Boost Technology, and you can see why it’s good to choose a Core i7 over an i5 or i3.

Now, you should know how much physical memory you have. More, the merrier. Starting with Windows 7, computers started shipping with a minimum of 4GB as a standard. So, 4GB is enough for your OS and proposed virtualization? It’s going to be a tight fit. I will suggest an additional 4GB minimum, making the total physical memory 8GB so that you won’t have to sacrifice performance.

Finally the storage. Most of the branded PCs and laptops are coming with 500GB HDD as standard & extending the storage of a laptop is complex than of a desktop computer. For the later, all you need is another HDD which you can plug to one of the available SATA ports and configure. With a laptop, you may need to replace the HDD with a higher capacity one or use an external HDD for your additional storage requirements.

My current Virtualizations are mostly for Oracle technologies. I am a forms and reports developer, doing a certain level of .NET development & manage Oracle EBS R12 instances (“NOT a DBA”). Further I try almost all the database, weblogic versions & currently learning ASMM & RMAN. I have never attempted other areas of Oracle technologies, so my VMs run

  1. Oracle EBS R12 cloned instances
  2. Oracle database(s)
  3. Oracle weblogic server(s) with forms and reports (supported)

My EBS R12 VM has the following configuations

8GB memory out of 16GB physical, 4 processor out of 8 logical processors & almost 850GB of storage out of 4.5 terabyte total storage. Our instance has 400GB database size, 175GB application instance & the VM responds to requests instantaneously once after scheduled jobs are completed after a restart of the application. Usually I find the application responding better after 3 hours of settling down & the performance is assured throughout days and weeks until a restart.

My Weblogic, database VMs have the following configurations

4GB memory out of 16GB physical, 4 processor out of 8 logical processors & almost 250 GB of storage out of 4.5 terabyte total storage. I have my weblogic VM running 11gR2 64bit database as well. I get instant responses from both the Weblogic server and Oracle database 11gR2 from a client system, whenever accessed

Recommendations

Even though you can run multiple VMs at the same time, I would suggest, based on your hardware, limit them. Example

When I run my EBS R12 VM (8GB memory, 4 processors) my HOST computer is left with only 8GB free memory and 4 logical processors. If I start a Windows XP VM (2GB memory, 2 processors) to check the application performance, I feel my rig start slowing down and couple of times my computer shutdown with a high thermal point.

So make sure that you do tweaking to your VMs in order to make sure that your HOST doesn’t breakdown due to overload.

I always make sure that my HOST always has half of the hardware resources reserved for it, ie, 8GB memory, 4 processors regardless how many VMs I run at the same time! This is by using the VirtualBox console to alter the parameters of VMs before they are started

1

All the settings for the VMs could be altered using the settings, like increasing or decreasing the memory, processors, adding and removing storage devices etc.

2

Below you watch how fast my VM running EBS r12 responds to requests from another VM running Windows XP

Advantages of using Virtualization

The most important advantage for me is: I’ve a less cluttered HOST, said, I am NOT installing all the technologies to one OS, breaking it with conflicting versions of services and libraries and processing load.

Other advantages

  1. I can backup (copy) my entire “machine” and restore it during a total mess up or loss of data, rather than rebuilding the entire computer
  2. I get a sand-boxed environment & without fearing my attempts will break my main OS, continue the experiments
  3. I can make a VM, for example, running Oracle Enterprise Linux, copy and keep the OS installed disk somewhere and copy it to new VMs whenever required! Say, you install the OS only once and whenever you need to create a new VM with same OS, just duplicate the disk that has the OS! (Make sure you make a backup of the OS disk prior installing and configuring additional software into it)

Finally my suggestions for you, in case if you are considering to build your 1st VM using Oracle VirtualBox

  1. Majority of the Oracle geeks prefer Linux against Windows for database, weblogic deployments. So if you are NOT familiar with Linux, I suggest you start learning, regardless whether you FEEL very comfortable with it or NOT. You may be joining a firm that has reservations towards Windows OS running Oracle products, especially Oracle DBAs who have valid points like block corruptions, difficulties to recover from a crash are complex in the case of Windows OS.
  2. Install OS in a separate disk. 40GB dynamic size should be more than enough for any recent Linux distros. Avoid Linux 7 if you are truly new to Linux. Oracle Linux 6.7 should be your friend.
  3. Install 64Bit OS, so that you can take the advantage of your 64Bit processor and physical memory
  4. Install Oracle supported Linux distros (RHEL, OEL & certain versions of Debian. CentOS is not at all supported)
  5. Install the complete desktop, you are hardly going to complete the installations on pure CLI mode.
  6. Add SCSI interface to your VM and for Oracle database etc, use SCSI disks. I had a huge argument with VirtualBox guys about the performance difference between SATA VDI disks and SCSI VDI disks. I found the SCSI VDI disks performing better, however I was dismissed saying as far both the types are created on the same HDD, it must be more psychological :O
  7. Use 1TB 7200 RPM disks in the place of 2TB 5400 RPM disks. Later ones are best for data storage, when the previous ones give you better I/O. Create fixed size VDI for databases & applications, that means you will NOT able to increase the size of the disk once after created, however, it gives you faster I/O and better performance.
  8. Update your OS. As soon as the VM is built, update your OS prior installing database, weblogic etc. RHEL will NOT allow you to update the packages without subscription, hence Oracle Enterprise Linux should be your best choice of Linux distro. Please note, you shouldn’t use Oracle Linux in a production environment without acquiring sufficient licenses. Whatever I suggest here are limited for study/evaluation purposes and I don’t encourage any kind of illegal usage of software!
  9. Use Oracle’s pre-install packages to install database, EBS etc prerequisites rather than trying to download individual components from different download sites.
  10. Use shared folders between HOST and Guest (VM) so that you don’t have to sacrifice storage. Not just that, when you want to keep the backup of some files from the VM, the shared folders will make it as easy as possible
  11. Use bridged network, with Promiscuous Mode “Allow All” so that you can communicate with the VM from network
  12. 3
  13. Disable IPV6, firewall, SELinux on your Linux VM
  14. If you creating a Windows VM, I’m sure you better know how to configure your guest so that you can access it from a network.

Finally recommendations for a DESKTOP computer to try virtualization

  1. i7 processor + good quality heat sink. Your HOST and VMs are going to create loads of heat!
  2. 16GB Memory (DDR4 is the new standard, do not ignore it)
  3. 1×2 TB HDD

Few years back, such a configuration looked impossible for me, well, I saved bit by bit and made my dream computer. I’m sure you can also do it :) & trust me, a good computer opens a new world for you.

All the best and wish you all a very successful year ahead!

for Windows7bugs

rajesh

 

 

 

 

 

 

 

Oracle R12 Cloning | dbTier "ouicli.pl INSTE8_APPLY 1"

On By windows7bugsIn Linux, Oracle Applications (R12.0.4), Oracle Applications(R12), Oracle Enterprise Linux, Oracle related, UncategorizedLeave a comment

Hi guys

There could be thousands (exaggerated) reasons why a Oracle cloning process could go all bad. I’m not an application DBA, however, have enough experience with the architecture, technology as I interact with it everyday as a part of my job.

Few months back, I started doing something what a DBA should do, cloning. My prior attempts were mostly at home, using virtual machines and test instances and they were NOT as mission critical as what we do at work.

So, after the storage device was revamped with new partition structures I was asked to do a cloning for the production instance. Let me explain how the application was deployed prior the storage restructuring

  1. We had the database tier on mount point /u05
  2. Application on /u06 mount point

So, I recreated the same mount points and started the cloning process for dbTier and the process got terminated at 2% and the log files shown me an error that I was not familiar with.

“ouicli.pl INSTE8_APPLY 1”

Google searches fetched me hundreds of results for “ouicli.pl INSTE8_APPLY”, however the error codes were mostly for 255 or “-1” and apparently I didn’t have any clue what was going wrong.

So I unzipped the tar ball for database tier once again, and the cloning process got aborted at 2%,  and I was getting nervous as I was expected to make the instance online by early morning 7AM

Most of the reference materials were explaining about non-existent Oracle inventory locations, and I confirmed that it was not the case from my part (Obviously, I was overlooking at this constrain itself!)

After half a dozen times tasting failure, finally I tried to see what was written inside the oraInst.loc file

oraprod@erp-prod:/home/oraprod>cd $ORACLE_HOME
oraprod@erp-prod:/u05/oraprod/PROD/db/tech_st/10.2.0>cat oraInst.loc
inventory_loc=/u01/oraprod/PROD/db/tech_st/10.2.0/admin/oui/PROD_erp-prod/oraInventory

and I realized that inventory location was wrongly pointing towards an non-existing mount point and physical location!

I modified the oraInst.loc content with the correct mount point

inventory_loc=/u05/oraprod/PROD/db/tech_st/10.2.0/admin/oui/PROD_erp-prod/oraInventory

and the cloning process went ahead without giving another errors.

We had an instance that was running from last 6 years, which was only once cloned from a cold backup during the storage device change, and somehow the inventory location remain unchanged with the repositories.

I hope this finding could help few newbies like me out there

regards,

rajesh

Oracle EBS R12, Install/Clone on Oracle/Red Hat Linux 7.x

On By windows7bugsIn Linux, Oracle Applications (R12.0.4), Oracle Applications(R12), Oracle Enterprise LinuxLeave a comment

Hi guys

Last reviewed on: 10th January 2022

Made changes to the body for Oracle Linux 7.9

References: http://www.slideshare.net/tianpan/oracle-ebs-r1213installationlinux64bitpantian

(and dozens of EBS R12 related blogs and oracle community posts)

As far I could recollect, Oracle EBS R12.0.x & R12 12.1.x are not certified yet for Linux 7.x, let it be Red Hat Linux or Oracle enterprise Linux. Does the certification path really make guys like us trying to clone, install EBS R12 over yet to be certified platforms? Well, my case, I always did attempt & succeeded to a certain levels. Starting with Oracle 10g over Windows 7, 8 etc.

This time, my attempt was to clone our existing EBS R12 12.0.6 instance from Red Hat linux Enterprise 5 64Bit to Oracle Enterprise linux 7 64Bit.

I can’t say whether it is a rightful thing for you or for your environment. For me it was a fun project, that I achieved to a maximum satisfactory level.

Environment: Source System

Red Hat Linux 5.11 64Bit, 48GB physical memory, HBA interfaced external storage, single node EBS R12 12.0.6 with Oracle database 10.2.0.3 database

Environment: Target System

1. Oracle VirtualBox VM with 4 processors, 10GB memory, Oracle Enterprise Linux 7.9 with GUI on demand

VDI fixed size of 1.3T holding both application tier & database tier files copied from source system

2. A Desktop machine with i7 Processor, 4.5 TB Storage, 16GB physical RAM for real hardware performance testing

How to?

Definitely, this is not for a production instance, not for a production instance, not for a production instance & REMEMBER, even if you have valid Oracle support, Oracle will NOT support YOU as the platform is NOT certified for 12.0.x or 12.1.x

Oracle Enterprise Linux 7.x is not certified for EBS R12.0.x , hence I was unable to find the pre-requisites install for EBS R12 for the OS. The hacks started from this point onward

Update(10th January 2022): Oracle Linux 7.9 has the “Add-on” repository that allows you to install the pre-requisites package, however meant for later certified versions of Oracle EBS. Hence, stick to Oracle Linux 6 public yum repository based package installation as mentioned below.

(Please make sure that you fully updated your OEL 7.x box prior the attempts listed below)

As root switch the directory to

cd /etc/yum.repos.d

Go to

http://public-yum.oracle.com/ and install the ol6 repo following instructions provided. The instruction might get in to folds, just follow the below.

cd /etc/yum.repos.d/
wget http://yum.oracle.com/public-yum-ol6.repo

Please note public yum repositories will be soon removed, if you are reading this post much later, make sure that you will find relevant sources to install the pre-requisites.

Open the ol6 repo file using gedit/vim and enable the add-on repo. Make sure the OEL7 repo doesn’t have the add-ons enabled. If you miss this, you might end up rebuilding the Linux Server (That’s what I did!)

install pre-requisites for EBS R12 by issuing the following command

yum install oracle-ebs-server-R12-preinstall -y

Once the installation completed, you will notice that you have two new users created

oracle

applmgr

If your cloned instance was having different user names for both application and database, create same usernames and add them to relevant groups

for example applprod to oinstall group and oraprod to dba, oinstall groups (mandatory)

Special note: If you are attempting these hacks for Production instances, sorry buddy, YOU will never be supported by Oracle as they have recently confirmed to me through a private message that they don’t have any plans to certify 12.0.x versions on OEL 6 or 7

Prior you attempt to clone or install the 12.0.6 instance on OEL 6, 7 make sure that you have taken care of few things prior the attempt. There are few OS level files formatting mandatory for Oracle applications, those caused me almost 3 weeks of reading various blog posts, and NOT a single solution to address a weird error associated with “Error: Missing ormi[s]://<host>:<port>” error while shutting down the application services using adstpall.sh script file! And the reason was default formatting of /etc/hosts file by OEL 7 installation!

Few errors you should see when the /etc/hosts file is NOT formatted as per Oracle’s installation notes…

Executing service control script:
 …../admin/scripts/adoacorectl.sh stop
 Timeout specified in context file: 100 second(s) 

script returned:

  ERROR : Timed out( 100000 ): Interrupted Exception
  You are running adoacorectl.sh version 120.13

So the /etc/hosts file must be formatted like below with your OEL/RHEL 6.x & 7.x installations

127.0.0.1 localhost.localdomain localhost 
::1 localhost6.localdomain6 localhost6 
192.168.10.15 erp-prodbak.xyz.com erp-prodbak #Your box 
192.168.10.16 erp-prod.xyz.com erp-prod #Your production server, if at all required

Rest of the network related mandatory elements specified in the reference documents could be ignored, or you may format them as well. If you are going to touch your /etc/resolv.conf file at all, make sure that after changing the content of the resolv.conf, you make it read-only by issuing the following command

chattr +i resolv.conf

else after a reboot, this file be rewritten by the networking admin services

Now let us refer to the /etc/sysctl.conf & /etc/security/limits.conf files, which are setting system level parameters for the application. While I noticed that the previously configured parameters were causing performance degradation once after the clone, I just copied these elements from a 12.1.3 vision instance and replaced to find that, my cloned instance started flying, giving me much better performance than our Production instance that has the latest hardware and 48GB physical memory!

Once again, thoroughly check the below parameters, if something looks not appropriate for your environment, refrain from updating

as a precaution, do a backup for both the above mentioned files

cp /etc/sysctl.conf /etc/sysctl.conf.original 
cp /etc/security/limits.conf /etc/security/limits.conf.original

Now using your favorite editor, replace the content of you sysctl.conf file with following

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and
# sysctl.conf(5) for more details.
#
# Use '/sbin/sysctl -a' to list all possible parameters.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

# oracle-ebs-server-R12-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744

# oracle-ebs-server-R12-preinstall setting for kernel.sem is '256 32000 100 142'
kernel.sem = 256 32000 100 142

# oracle-ebs-server-R12-preinstall setting for kernel.shmmni is 4096
kernel.shmmni = 4096

# oracle-ebs-server-R12-preinstall setting for kernel.shmall is 1073741824 on x86_64
# oracle-ebs-server-R12-preinstall setting for kernel.shmall is 2097152 on i386

# oracle-ebs-server-R12-preinstall setting for kernel.shmmax is 4398046511104 on x86_64
# oracle-ebs-server-R12-preinstall setting for kernel.shmmax is 4294967295 on i386
kernel.shmmax = 4398046511104

# oracle-ebs-server-R12-preinstall setting for kernel.msgmni is 2878
kernel.msgmni = 2878

# oracle-ebs-server-R12-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144

# oracle-ebs-server-R12-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304

# oracle-ebs-server-R12-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144

# oracle-ebs-server-R12-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576

# oracle-ebs-server-R12-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576

# oracle-ebs-server-R12-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

Save the file and issue the following command

sysctl -p

Now, change the limits.conf file with following content

# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#            
#
#Where:
# can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
# can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
# can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open file descriptors
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#
#                 
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4

# * hard nofile 65535
# * soft nofile 4096
# * hard nproc 16384
# * soft nproc 2047


# oracle-ebs-server-R12-preinstall setting for nofile soft limit is 4096
oratest   soft   nofile    4096
appltest   soft   nofile    4096

# oracle-ebs-server-R12-preinstall setting for nofile hard limit is 65536
oratest   hard   nofile    65536
appltest   hard   nofile    65536

# oracle-ebs-server-R12-preinstall setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oratest   soft   nproc    16384
appltest   soft   nproc    16384

# oracle-ebs-server-R12-preinstall setting for nproc hard limit is 16384
oratest   hard   nproc    16384
appltest   hard   nproc    16384

# oracle-ebs-server-R12-preinstall setting for stack soft limit is 10240KB
oratest   soft   stack    10240
appltest   soft   stack    10240

# oracle-ebs-server-R12-preinstall setting for stack hard limit is 32768KB
oratest   hard   stack    32768
appltest   hard   stack    32768


# End of file

Install unzip version 5 from https://oss.oracle.com/el4/unzip/unzip.html

(This is mandatory as 12.0.x doesn’t support unzip versions above 5, and your cloning will fail with error notifying unzip is not supported)

Source the libdb.so.2 library file to /usr/lib from your source system, as the libgdbm.so.4.0.0 soft link hack will not work. If the libdb.so.2 is already a soft link in your source system, move the source file “libgdbm.so.2.0.0” from source to target box and create the soft link.

Now go ahead and enjoy the cloning/installation of your R12 12.0.x, or R12.1.x releases over OEL/RHEL 7.x (CENTOS is GNU release of RHEL, so you may experiment with that distro as well, nothing guaranteed though)

Please be warned, I have read many places that, few modules do not work on Linux 6,7 releases. Hence, the entire exercises you painfully executed may turn futile at later stages, and once again, if you have a live Oracle support, do not attempt such with your Production instance!

Enjoy guys :)

for Windows7bugs

rajesh

Oracle Applications | Making references to fnd_message package from custom PL/SQL library

On By windows7bugsIn Oracle Applications (R12.0.4), Oracle Applications(R12), Oracle related1 Comment

 

Until recent times I didn’t have a single idea that, fnd_message package was NOT a database, instead PL/SQL library object, through which forms based modules were calling various elements like

fnd_message.set_string

fnd_message.show

image

image

One of the main reasons for believing the same was as soon as I type the name of the package, both Toad  and SQL Developer identify it as already existing object!

However, few days back I decided to shed my laziness and to revamp the core retailing application I have developed two years back, using Oracle’s public APIs for Order Management & Inventory modules.

Hence, I started designing my own PL/SQL libraries and bit the bullet while trying to make a reference to fnd_message package. The call was returning errors (please refer the below image)

image

Which forced me to start googling for relevant and dependable pointers towards this particular situation and came across a forum thread where one person who I respect as a “geek” tried to explain why there is a package in the database and within an PL/SQL library with the same “fnd_message”, and gradually giving it up saying “Only oracle could explain why”

Hence I opened up the Template form and try to see which attached PL/SQL library has the listing for fnd_message component calls

image

image

and the PL/SQL library “FNDSQL” attached to the TEMPLATE.fmb by default has all the components referenced with the forms modules. Once identified, I attached the same library with my custom library

image

and the next compilation attempt was successful!

I do hope, this information is useful to few others out there, who are trying to call the fnd_message package references with their own custom PL/SQL libraries.

regards,

rajesh

Oracle E-Business Suite R12 | Google Chrome Browser access

On By windows7bugsIn Oracle Applications (R12.0.4), Oracle Applications(R12)Leave a comment

 

So still stuck with no apparent methods to start using Google Chrome for Oracle applications Release 12? Well there is a “possibility”

There is a free extension available with Google Chrome repositories called

Oracle EBS R12&11i Enablement for Chrome 0.1.3

image

You can download the same for your computer from here (Search in the extensions page with a string like “Oracle R12” as the link location may change later)

Once the extension is installed, close Chrome and restart and try to access the Application instance.

image

 

That’s it folks

for Windows7bugs

rajesh

Oracle Applications R12, Re-assign PR/PO stuck in the workflow (awaiting approval)

On By windows7bugsIn Oracle Applications (R12.0.4), Oracle Applications(R12), Oracle relatedLeave a comment

 

Handling Purchase Orders stuck in the work flow.

Logon to the instance as “SYSADMIN”

1

Select “Workflow Administrator Web Applications” and Select the function “Status Monitor”. This opens a jsp page show below.

2

For the purchase orders stuck in the flow, please locate “PO Approval” workflow using search function. This windows provides multiple choices to limit the amount of data retrieved. Apply it whenever it is possible to reduce the time collecting and producing data.

Image: Finding the correct workflow (for Purchase requests stuck in the workflow)

3

For Purchase Requests stuck in the work flow, please use the following query

select requisition_header_id, segment1, wf_item_type, wf_item_key, authorization_status, org_id from po_requisition_headers_all where segment1 = ‘1980’ and org_id = 105;

for Purchase order change the table name to PO_HEADERS_ALL

to find the wf_item_key which is referred as “item key” with the JSP form.

4

Once the desired information derived

Click on “Activity History” button

5

Now you may use “Reassign” function (Shown within green rectangle) to reassign the Purchase Request for approval by next immediate subordinate.

You may use the “Rewind” button to rewind the workflow to an earlier stage as well

Hope you enjoyed another quality solution from us

for Windows7bugs

rajesh

Oracle applications, fnd_standard.set_who

On By windows7bugsIn Oracle Applications (R12.0.4)Leave a comment

 

One of the best features of Oracle applications is the flexibility to add custom applications and extend the functionality of the business suite. Many time developers who are not well versed with the Oracle’s guidelines for custom development for Oracle applications will totally ignore the pre-requisites for fnd_standard.set_who API to work properly by avoiding to include the following mandatory columns while designing tables

  1. CREATION_DATE    DATE
  2. CREATED_BY    NUMBER
  3. LAST_UPDATE_DATE    DATE
  4. LAST_UPDATED_BY    NUMBER
  5. LAST_UPDATE_LOGIN    NUMBER

Which will fail the API call and result in “No record history available here” notification

Another possibility is, developer adds these columns with the custom tables at later stages and manually add the columns to the block, without involving the datablock wizard, thus not properly linking the block with newly created columns.

Manually adding the columns with proper column names and data types may not generate an error while compiling, however the API will not able to see those columns.

The best method to avoid this problem is, by running the data block wizard once after new columns are added to the custom table(s)

Run the data block wizard, refresh the data source and make sure you don’t have any column within the left side pan

Recompile and test the custom application once again. 99% this method should solve the fnd_standard.set_who API not updating information.

 

regards,

admin

Oracle Application R12, 7 Buckets Supplier Aging SQL

On By windows7bugsIn Oracle Applications (R12.0.4), Oracle Applications(R12)Leave a comment

 

[sourcecode language='sql'  padlinenumbers='true']
Select supplier_number, vendor_name,
sum(amount_remaining) amount_remaining,
sum(b0) "<=30",
sum(b1) "31-60",
sum(b2) "61-90",
sum(b3) "91-180",
sum(b4) "181-270",
sum(b5) "271-360",
sum(b6) ">360"
FROM(
SELECT i.invoice_date,
         round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3) amount_remaining,
         i.vendor_id, supp.vendor_name, supp.segment1 supplier_number,
         CASE
         when trunc(trunc(sysdate))-i.invoice_date <= 30 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b0
         ,
          CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 31 AND 60 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b1,
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 61 AND 90 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b2,  
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 91 AND 180 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b3  ,   
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 181 AND 270 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b4  
          ,   
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 271 AND 360 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b5     ,   
             CASE
         when trunc(sysdate)-i.invoice_date > 360 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b6    
             FROM ap_payment_schedules ps, ap_invoices i, ap_suppliers supp
   WHERE     i.invoice_id = ps.invoice_id
         AND ps.org_id = :P_ORG_ID -- Security takes care of this part, only for other reporting reqs
         AND i.vendor_id = supp.vendor_id
         AND i.cancelled_date IS NULL
         AND ps.amount_remaining <> 0
         )
GROUP BY   supplier_number, vendor_name       
ORDER BY  vendor_name
[/sourcecode]

And as usual, the main view is a protected repository, hence you have to initialize the security part in order to fetch data

[sourcecode language='sql' ]
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;

begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50854, 200);
end;


begin
MO_GLOBAL.INIT('SQLAP'); --Payables
--MO_GLOBAL.INIT('PO');
end;
[/sourcecode]

Now enjoy another quality stuff from us :)

PS for Kuwait requirements, we have rounded the figures to 3 digits, alter the script to suite your reporting requirements.

regards,

admin

Oracle ERP 12.0.6, Customer Aging SQL Query

On By windows7bugsIn Oracle Applications (R12.0.4)9 Comments

 

Update:07th March 2013

A much simpler script

[sourcecode language="sql" padlinenumbers="true"]
Select customer_id, customer_number,customer_name,
overall_credit_limit, currency_code,
customer_type,
 Balance,
cr_balance,
 &quot;&lt;=30&quot;,
 &quot;31-60&quot;,
 &quot;61-90&quot;,
 &quot;91-180&quot;,
 &quot;181-270&quot;,
&quot;271-360&quot;,
&quot;&gt;360&quot;
from(
Select customer_id, customer_number,customer_name,
xx_customer_credit_limit_f(:P_ORG_ID,a.customer_id) overall_credit_limit, NULL currency_code,
decode(a.CUSTOMER_TYPE,'R','Ex','I','In') customer_type,
nvl(omscustbalance_f (a.customer_id, :P_ORG_ID ,0,0,0),0) Balance,
0 cr_balance,
omscustbalance_f (a.customer_id, :P_ORG_ID,1,0,0) &quot;&lt;=30&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,2,31,60) &quot;31-60&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,3,61,90) &quot;61-90&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,4,91,180) &quot;91-180&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,5,181,270) &quot;181-270&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,6,271,360) &quot;271-360&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,7,0,0) &quot;&gt;360&quot;
from ar_customers a
where 
1=1
and a.customer_id = NVL(:P_CUSTOMER_ID, a.customer_id)
and decode(a.CUSTOMER_TYPE,'R','Ex','I','In') = NVL(:P_CUST_TYPE, decode(a.CUSTOMER_TYPE,'R','Ex','I','In') )
and customer_id IN (Select customer_id from ar_payment_schedules_v)
and a.status = 'A'
)
where balance &lt;&gt; 0
ORDER BY 3
[/sourcecode]

Oracle says they have the best business practices :P, however our “Accountant” geeks believe the opposite.

They are not happy with the aging buckets, provided as standard and recently IT was challenged with a requirement to produce some output which will a total of 7 buckets!!!

Hence here it is, the script

 

[sourcecode language="sql" padlinenumbers="true"]
SELECT  su.org_id organization_id, acct.cust_account_id customer_id, acct.account_number customer_number,  PARTY.PARTY_NAME customer_name, overall_credit_limit,cl.currency_code,
decode(acct.CUSTOMER_TYPE,'R','Ex','I','In') customer_type,
omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0) Balance,
(nvl(overall_credit_limit,0)-nvl(omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0),0)) cr_balance,
omscustbalance_f (acct.cust_account_id, su.org_id,1,0,0) &quot;&lt;=30&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,2,31,60) &quot;31-60&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,3,61,90) &quot;61-90&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,4,91,180) &quot;91-180&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,5,181,270) &quot;181-270&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,6,271,360) &quot;271-360&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,7,0,0) &quot;&gt;360&quot;
       FROM   hz_cust_profile_amts cl,
            hz_cust_site_uses_all su,
            hz_cust_accounts acct,
            HZ_PARTIES PARTY,
            OMS_GL_ORG_HOOKUP_V ood
    WHERE       su.org_id = :P_ORG_ID
    and ood.organization_id = su.org_id
            AND acct.cust_account_id = NVL(:P_CUSTOMER_ID, acct.cust_account_id)
            AND party.party_id = acct.party_id
            AND cl.cust_account_id = acct.cust_account_id
            AND cl.site_use_id = su.site_use_id
            AND cl.currency_code = ood.currency_code --'KWD'
            AND su.status = 'A'
order by su.org_id, PARTY.PARTY_NAME
[/sourcecode]

With this query we are referring couple of custom objects like

omscustbalance_f – function calculating the outstanding balance

&

a custom view OMS_GL_ORG_HOOKUP_V, which links both ORG_ORGANIZATION_DEFINITIONS table and GL ledger information

Code for omscustbalance_f

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION APPS.omscustbalance_f (cust_id         IN NUMBER,
                                                  p_org_id        IN NUMBER,
                                                  bucket_number   IN NUMBER,
                                                  AGE1            IN NUMBER,
                                                  AGE2            IN NUMBER)
   RETURN NUMBER
AS
   total_due    NUMBER := 0;
   att_string   VARCHAR2 (2000);
BEGIN
   --1 &lt;= 30
   --2 31-60
   --3 61-90
   --4 91-180
   --5 181-270
   --6 271-360
   --7 &gt;360
   --0 balance

   IF bucket_number = 0
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id;
   ELSIF bucket_number = 1
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id
             AND b.days_past_due &lt;= 30;
   ELSIF bucket_number = 7
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id
             AND b.days_past_due &gt; 360;
   ELSE
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND 
--you may avoid the following lines---custom requirement---
b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
--you may avoid the code until here---custom requirement---
             AND b.org_id = p_org_id
             AND b.days_past_due BETWEEN AGE1 AND AGE2;
   END IF;


   RETURN TOTAL_DUE;
END omscustbalance_f;
/
[/sourcecode]

and finally the script for view “OMS_GL_ORG_HOOKUP_V”

 

[sourcecode language="sql"]
DROP VIEW APPS.OMS_GL_ORG_HOOKUP_V;

/* Formatted on 3/3/2013 8:55:56 AM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FORCE VIEW APPS.OMS_GL_ORG_HOOKUP_V
(
   LEDGER_ID,
   NAME,
   CURRENCY_CODE,
   ORGANIZATION_NAME,
   ORGANIZATION_ID,
   ORGANIZATION_CODE,
   OPERATING_UNIT
)
AS
     SELECT gll.ledger_id,
            gll.name,
            gll.currency_code,
            ood.organization_name,
            ood.organization_id,
            ood.organization_code,
            ood.operating_unit
       FROM GL_LEDGERS gll, org_organization_definitions ood
      WHERE ood.set_of_books_id = gll.ledger_id
   ORDER BY 1;
[/sourcecode]

 

Are you done yet? Not really. If you have to run the query from a PL/SQL node, you have to initialize the security using the following 

[sourcecode language="sql"]
Begin
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;

begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50595, 222);
end;


begin
MO_GLOBAL.INIT('AR'); -- Receivables
end;

return (TRUE);
end;
[/sourcecode]

Enjoy another quality solution from us!

regards,

admin

Oracle E-Business Suite R12 Rapid forms development using Developer 6i forms

On By windows7bugsIn Oracle Applications (R12.0.4), Oracle Applications(R12)Leave a comment

 

Moving from client server architecture to R12 based domains could be quite challenging for Oracle developers, especially those who have spent years, enjoying the flexibility of testing forms modules from the local development machines prior moving the “working” modules to the production instances.

Let us see the general development scenario for R12 instances.

  1. From 10g forms, connect to database
  2. open TEMPLATE.fmb and save as “XXMYFORM” etc
  3. Then keep on adding the elements

Now, move the form module to $AU_TOP/forms/US and compile it to respective application repositories

Create form, menu items…

A long list and the most frustrating is keeping on FTP the slightly modified forms module to the R12 instance to compile and testing.

We have came across a quick resolution for this PIA(Pain in the ASS) approach (for forms modules which do not use folder views) by using developer 6i (Uhu, beware Oracle fan boys would tell you, Oracle does not support developer 6i any further)

Create a template.fmb using Developer 6i forms

Change the system coordinates to use inches in the place of points

image

  1. Save the template.fmb
  2. Using save as, save your template.fmb as a new fmb file (eg: aa.fmb)
  3. Connect to apps database instance and start adding windows, canvases, blocks etc to the form and TEST IT locally!!!
  4. TEST it until the results are as expected.
  5. Make a backup for your working module (copy aa.fmb to bb.fmb for example)

 

Open your aa.fmb file, developed using forms 6i with Developer suite 10g forms designer (You will get prompts stating the possible conversions for triggers, coding etc)

Open TEMPLATE.fmb and save it as “MYAPPSFORM.fmb”

Now, please give maximum attention towards copying the objects from your 6i module which is opened with Developer 10g forms, TO your form module which will go to apps instance

  1. Copy all the windows you have created with 6i module to 10g forms
  2. Copy all the canvases you have created with 6i module to 10g forms
  3. Copy all the blocks you have created with 6i module to 10g forms
  4. Copy record groups, lovs, form level triggers if any

Now, try to compile the 10g form. If you have copied everything from 6i, this new form shouldn’t give you any compilation errors.

Move the form to apps instance, compile, create forms, menus and do final testing, confirming everything is working fine.

regards,

admin