We migrated to 11G R2 (11.2.0.4) for our Oracle Applications R12 few years back, yes few years back (2017) & lived with one of the worst experiences…
Connecting Oracle Developer 10g (Forms/Reports) suite to 11G database.
I have scavenged through community articles for long time before giving up. I hardly came across a single fix for the connection time that used to hang up the Developer suite at times…
Today, I decided to find a solution for the nagging SSH connection issues from Windows 11 to our LINUX application servers and realized that we didn’t update the DNS settings for them once after we decommissioned a domain controller. Once the SSH issues were rectified and addressed, my next attempt was to find a solution for “frmcmp_batch” taking long time to start compiling modules & I landed on the below post.
As we are already on 11G R2 11.2.0.4, patching was not required. All I needed was to alter the hidden parameter “_FIX_CONTROL” as mentioned in the article.
SQL> ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON';
(Use scope=spfile to make this change permanent. This will require you to restart the database.)
I opted to go without spfile for testing & as soon as applied, the “frmcmp_batch” started compiling the modules instantly, against the usual delay that ran into many minutes other times.
Out of curiosity, I tried to connect to the database from Developer 10g & the connection was instant! within a fraction of a second.
So DNS being one of the most important elements establishing successful connections, patches and fixes also play crucial role in providing stable connections. Were you stuck with the same issue? give the solution a try and let us know whether it helped you also.
Today we will see how we can use CUSTOM.pll for enabling special menus and printing a custom report by invoking the special menu that we activate using the library.
We are going to use Oracle’s seeded form “POXPOEPO” AKA Purchase Orders.
Please make sure that you make a backup for the CUSTOM.pll prior making below said modifications. CUSTOM.pll is “always” found uner $AU_TOP/resources folder
We’ll enable the SPECIAL15 menu item for the exercise.
Load up CUSTOM.pll using Oracle Forms Designer & make sure you are connected to database before loading the library file.
Attach APPCORE, APPCORE2 libraries with your copy of CUSTOM.pll
Attach FNDCONC.pll library for calling printing related activities
Your CUSTOM.pll should look like the above after attaching said libraries.
Add the block as seen with the image by the bottom of your CUSTOM.pll package body.
BEGIN
IF (event_name = 'WHEN-NEW-FORM-INSTANCE') THEN
IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN
-- fnd_message.debug(l_form_name);
app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
END IF;
END IF;
END;
Now you can proceed with writing code for what happens when “SPECIAL15” event happens
BEGIN
IF (event_name = 'SPECIAL15') THEN
IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN
print_po(name_in('PO_HEADERS.ORG_ID'),name_in('PO_HEADERS.SEGMENT1'));
--fnd_message.debug('Will Print This Order');
--app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
END IF;
END IF;
END;
Here, I am calling a procedure that I defined with CUSTOM.pll for handling print requests.
and the package body is as following (not another image, I am going to save some efforts for you)
procedure print_po(p_org_id IN NUMBER, p_order_number IN VARCHAR2) is
l_ord_num NUMBER := 0;
l_ord_type_name VARCHAR2 (240);
l_req_id_Rep NUMBER;
l_request_completed BOOLEAN := FALSE;
l_req_phase VARCHAR2 (20);
l_req_status VARCHAR2 (1000);
l_req_dev_phase VARCHAR2 (1000);
l_req_dev_status VARCHAR2 (1000);
l_req_message VARCHAR2 (1000);
l_conc_mgr_status NUMBER;
p_call_stat NUMBER;
p_activep_stat NUMBER;
l_order_category_code NUMBER;
l_report_name VARCHAR2 (40);
l_ret_report_name VARCHAR2 (40);
l_req_id NUMBER;
l_order_type_name VARCHAR2(30);
--
l_signing_person VARCHAR2(240);
l_person_designation VARCHAR2(240);
BEGIN
--fnd_message.debug('Will Print This Order');
/*This is a custom procedure that checks whether the concurrent manager is online or not, you can safely comment this line
--Check the status of Concurrent Manager
apps.xx_conc_mgr_status_p (p_call_stat, p_activep_stat);
IF p_call_stat <> 0
THEN
fnd_message.set_string ('Concurrent Manager Status Unknown');
fnd_message.show;
ELSE
IF p_activep_stat > 0
THEN
NULL; --Message('ICM is running' || l_activep);
ELSE
fnd_message.set_string (
'Concurrent Manager is down, Please try printing the invoice later');
fnd_message.show;
RAISE form_trigger_failure;
END IF;
END IF;
--Checking concurrent manager status end----
-- MESSAGE ('Concurrent manager status: up & running');
*/
BEGIN
/* I am picking up the reports names (concurrent_program_name from FND_CONCURRENT_PROGRAMS_VL view as we have different layouts for companies
you can set up a value for l_report_name while variable is defined
---
--
SELECT execution_file_name,STRING1, STRING2
INTO l_report_name, l_signing_person, l_person_designation
FROM omspoprintreg
WHERE 1 = 1 AND organization_id = p_org_id
AND TRUNC (SYSDATE) BETWEEN start_date_active
AND NVL (end_date_active, SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_message.set_string (
'No reports defined for this type of transaction, Please contact OM Super User');
fnd_message.show;
RAISE form_trigger_failure;
END;
-- FND_MESSAGE.DEBUG('Printing Order '||p_org_id||' order number '||p_order_number);
*/
--
--
l_req_id :=
fnd_request.submit_request ('PO',
l_report_name,
NULL,
SYSDATE,
FALSE,
P_ORG_ID,
P_ORDER_NUMBER,
l_signing_person,
l_person_designation,
chr(0));
--You cannot setup :SYSTEM.MESSAGE_LEVEL within CUSTOM.pll, hence use COPY
--to suppress messages like 'Two records saved'
COPY('25','SYSTEM.MESSAGE_LEVEL');
COMMIT;
-- FND_MESSAGE.DEBUG('Your request id is '||l_req_id);
l_request_completed :=
fnd_concurrent.wait_for_request (request_id => l_req_id,
INTERVAL => 1,
phase => l_req_phase,
status => l_req_status,
dev_phase => l_req_dev_phase,
dev_status => l_req_dev_status,
MESSAGE => l_req_message);
--:SYSTEM.Message_Level := '25';
COMMIT;
editor_pkg.report (l_req_id, 'Y');
end print_po;
That’s it!
Now copy the CUSTOM.pll to $AU_TOP/resources & compile it
If you don’t have syntax errors or other, you must have the fresh CUSTOM.pll
Please make sure that no users are currently online while you are compiling the CUSTOM.pll (This is only applicable to cases where the CUSTOM.pll is already deployed for different forms)
Log on to the instance, access Purchase Orders form & you should see a new menu item under “Tools” menu enabled
While CUSTOM.pll implements “editor_pkg.report” by attaching FNDCONC.pll, FORMS personalization will fail to provide the same functionality as most of the seeded forms do not have FNDCONC library attached to them by default. If you don’t want to use editor_pkg.report to open the completed report, you may create a database level procedure to submit the request(s) and call the same against SPECIAL(n) menu item through FORMS personalization.
It looks like I am getting something new everyday to blog…the latest is from Oracle Application R12 once after I added a new responsibilities to few users.
The Functions those are listed under new responsibility from the HTML page will not launch, instead a popup window appears with the statement “The Xyz Function Is Not Available Under The abcd Responsibility”
I recently had a nightmare with a custom form, that was revamped almost after 6 years of usage. Although the compiling on Production instance doesn’t have any issues, only portions of the form would load & the only few elements displayed were totally misaligned and the cells looked like just a plain straight line…
After many failed attempts, I tried to clear the cache, which we didn’t from a long time. The culprit was the cache. After releasing the cache, I recompiled the form & everything was fine.
With the above issue also, our issue was with the cache. After releasing the cache, the users were able to launch the form from the HTML page itself. If, this didn’t resolve your issues, have a look here
One of the most awkward things that keeps on happening in my life is, I land on my posts/comments those were posted years back, which were definite solutions to few perplexing situations like “Error: Missing ormi[s]://<host>:<port>”
Year 2015, I posted a comment with the below thread
explaining a possible solution to the error in attention…but I missed it.
Few days back I was setting up a VM with Oracle Applcations R12 to test SSD based storage device & landed on the same error! I didn’t have a clue…last four days I was going through each every other notes those I made, was scavenging through tones of blog posts and Oracle support documents, trying out suggestions to overcome the error below:
Executing service control script: …../admin/scripts/adoacorectl.sh stop Timeout specified in context file: 100 second(s)
We had our Oracle Application R12 implemented on RHEL 5 & later cloned the same instance on Oracle Linux 6 for TESTs. One of the major differences between RHEL 5 & 6 was the native support for IPV6 with Linux 6. Not just R12, many Oracle products WERE/ARE not yet completely compatible with IPV6 & the first thing geeks ask you to do is to disable IPV6 (Even today!)
I did, trust me I did it on the interface, I did it in the sysctl.conf & wherever possible & still had the same ormi error, whenever I tried to shutdown the application services. The temporary solution was to re-run the stop all script & finally the script completes with “0” error.
I was NOT happy & I wanted my instance to shutdown gracefully, over a single attempt. I “knew” that Oracle was very sensitive to Network stack, a screwed up hostfile, DNS, firewall…anything that is related to network could cause these kind of issues. So I decided to go through each one of them. So, first was the hosts file.
The default /etc/hosts file from Linux 6 has the entries like below
When Linux 5 have | Glad that I had a machine to cross check
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
Immediately I decided to give it a try, I chopped the unnecessary strings from the hosts file & started the application tier & tried to shutdown!
That was it, no more “Error: Missing ormi[s]://<host>:<port>”
No hacking of the Context files or disabling the IPV6 protocol is required, which could complicate SSH tunnel etc (as per Linux guys, I am not a Linux guy yet ;) in those means)
So, now you have a working solution, you have your IPV4 & IPV6 both enabled & an instance shuts down as you expect. Well, party time guys!
Hi guys
I confirm, I am not a DBA, yet my last 10 years with Oracle EBS experiments have given me opportunities to learn few facts those are not documented generally.
A general approach towards deploying a new setup/custom development for EBS is 1st developed and tested thoroughly for which a business needs testing environment. To provide such, a DBA “clones” or duplicates the existing environment and this process is called cloning.
A majority of the environments may note have the hardware with same configurations as it is available for the Production, this causes the cloned instances to perform poorly, thus making the entire testing pretty painful.
During the initial stages of learning, I had tough time digesting our part time DBA’s arguments and reasoning for the slowness, tied to the hardware limitations. Our TEST server has dual processors and 20GB memory with more than 3 TB storage that is hooked up to a 1GB network interface.
As my confidence kept on building, I started exploring the database setups and realized the DBA had only allocated 2GB for SGA & 1GB for the PGA. While questioned, he gave me all possible excuses like “Well, your instance doesn’t require more” and so on.
We got rid of him & had another DBA in the due course, who was far better and more experienced handling bigger environments. Yet we had this nagging issues related to the test instance being slow, lagging beyond our extended patience & I started asking oracle communities questions about how to speed up things.
“Not being a DBA” had it’s own limitations. Many instructions were beyond my understanding, many sounded impractical or illogical & finally one day I decided to setup the R12 (12.0.6, 11gR2 database) with 3GB for both SGA, PGA following an old discussion available with asktom.
That was the beginning. Our TEST instance performance improved to a level that, it become difficult to distinguish between the production and test instances when the Production instance is a beast (minimum for our environment)
Although, the manual SGA/PGA settings resolved one of the major performance related issues (login page load, opening large forms etc), I was still NOT convinced by the performance (Queries taking long time..). For some other reasons, I had to restart the TEST server (Which usually happens once in many months time) & was amazed to see that the performance gains were distinguishable from earlier times. As soon as I tried to access the instance, the login screen was loaded (not ignoring the cache mechanisms) faster, so did rest of the accesses. Forms based interfaces were loading faster and the queries were returned instantly etc.
I opted for another cloning. Dropped the entire R12 instances and repeated the cloning, adjusted the database parameters and “RESTARTED” the physical server, started the database and application tiers and insured that the performance gains I observed were not just a hit and miss, actually a permanent HIT that exists.
I continued my experiments further. My next attempt was to upgrade the SGA, PGA with more memory and this time I opted for 4GB for each sector. After the database & application restart I realized the poor performance. I was expecting the instance to be faster! Instead I was dealing with an instance that was as horrible as possible prior to my 3GB/restart patterns.
So I rolled back to 3GB SGA, PGA setups and was living with it until our finance consultant asked me for a fresh clone with most recent data. This time I decided to duplicate database using RMAN rather than a traditional cold back restore and cloning.
As RMAN duplicate database doesn’t require me to alter the database (as the spfile is already available), all I needed was to run the “adautoconfig” from the application tier and go online. I felt something awkward
Although the instance was “Okay”, there were few things which were NOT AT all okay. The database alert log was showing redo log files related issues. I checked the redo log files, things were looking Okay against the documents those I were referring to do the RMAN duplicate database…yet
I took the application tier offline and altered the database like following:
Increased the SGA max memory to 5GB and target to 4GB, restarted the database (40% of total available memory)
Left PGA at 3GB (Thus totaling the memory dedicated for the database to 8GB out of 20GB available)
From the database tier, executed “adautoconfig”
Execute “adautoconfig” from the application tier
Shutdown database and restarted the physical server
Restarted database and application tiers & boom, the instance was once again giving me the buttery smooth experience. I ain’t sure, whether it is technically correct, it was all about executing “adautoconfig” against both tiers after database parameter changes. I will ask Oracle community soon to confirm it.
Now, all the above experiments with an obsolete release of Oracle application R12 (12.0.6) over an OS (Linux 6) that’s not supported were performed over many months time. Lack of true technical training must be one of the reasons contributing towards painful experiences.
Well, whatever, today I feel like a winner. Just because I am able to host an instance of the application over a hardware that was labelled obsoleted just because someone didn’t know how to fine tune an instance of Oracle application, even though he is certified to do that!
If you are like me, not certified, a jack of everything, who loves to keep on experimenting, do give the above a try & post me your experiences!
regards,
rajesh
Recently one of our accountants forwarded me a screenshot, that was showing “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403” while he was trying to enter invoices against a “NEWLY” created vendor/supplier.
Our Oracle Application R12 (12.0.6) is considered 99.99999% stable, without a single technical or functional issue that really become a show stopper throughout last many years.
Well, this particular issue looked perplexing as it was not dealt by Oracle Application’s error reporting & slowly we started dwelling Oracle support documents those were dealing with the given forms error “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403”
Most of the documentations where mentioning about IBY duplicate pay party, which was not our case. Hence, I decided to open the associated form APXINWKB.fmb & located the WHEN-VALIDATE-ITEM trigger associated with the column “Purchase Order Number”. I couldn’t find any irregularities between an order that didn’t raise the error and this particular Purchase order did raise the exception, which was unhandled.
After two days of continuous attempts, I remembered that such errors happen in other forms modules when we had missing information for new vendors/suppliers. Must be due to a bug, there were times when site level details were NOT populated to organizations level details for a vendor/customer & I decided to go through all mandatory elements those were expected while creating a new vendor/supplier.
I sat with my colleague and we reached to “Payment Method”, and realized that the default payment method was not set for this particular vendor against the organization where we were getting this unhandled exception.
Once the payment method was set, the invoice was posted for the vendor successfully! So, if you come across these kind of unhandled exceptions across Oracle’s proprietary forms those deal with payments/invoices, prior exhausting yourself with cloning and patching, make sure you have all the mandatory elements for customer/vendors are properly filled in and assigned to all the organizations.
Hope this finding helps few consultants out there!
and one of my observations was strictly around the connection to database. I found forms those make calls to database level packages/procedures/functions crashing the developer when they are opened while no established connection with database exists.
Recently I was contacted by one of the visitors asking for help to resolve his issues with the crashes experienced while opening forms those were developed by someone else. We had a short TeamViewer session I realized that the issues were more than mere database connection specific. Then I remembered fixing few of such issues which were caused by missing table columns, packages/procedures/functions at the database level.
I located one of the backups and tried to open a form which I was extremely sure of crashing the developer due to missing objects at database level. All I remembered that I have added additional columns to a table that was in the form and a modified database level procedure, which were missing from freshly cloned database.
Unless I open this particular form, I will not able to re-create the objects and continue with the development as I don’t have any logging for the changes I have made to the form/tables/other objects during the development.
Then I remembered that Developer 6i hardly ever crashed due to missing objects, instead it did warn. As I had a classic domain setup using Weblogic 12c 12.2.1.3 recently with forms and reports developers, I decided to open this problematic forms module with the 12c forms developer.
That was the solution.
(The best thing about Weblogic 12c Forms is, you can opt for a standalone installation of the developer, avoiding all complexities around setting up the entire Weblogic infrastructure. Oracle have so much crap in their decision making team, having weird ideas like forcing the developers to setup an entire Weblogic infrastructure for mere development? duh!)
Now, if you are having such a situation, follow the below instructions:
MAKE many copies OF YOUR TROUBLESOME FORM, KEEP THEM IN DIFFERENT PLACES
Open Forms developer 12c, establish connection to database
Open a copy of the form that crashes 10g developer
If the form open (it should in case if it was crashing due to missing objects!), compile all objects (CTRL+SHIFT+T). This should start throwing errors about missing columns and other objects.
Alter your tables, create missing objects and open your form using Developer 10g
That’s it. You must be glad to have it fixed.
Hope this helps few Oracle developers who are mainly developing for Oracle Applications R12 using Developer 10g Suite.
This time I am going to post a solution towards one of the nagging annoyances with Oracle application R12 (12.0.6 in our specific case) & JRE securities those were implemented by JRE 7 (1.7.x)
Our instance was upgraded to use JRE 6 almost 5 years back during 12.0.4 to 12.0.6 upgrade and ever since there were dozens of JRE updates & the current JRE 1.8.0_161 (AS on 19th January 2018) & with each update, whatever the runtime parameters we add specifically for Oracle Application used to get removed, forcing us to renter it for each user.
I’ve been searching for a proper solution, not only towards the runtime parameter fix, for the security prompts those started annoying our end users whenever they tried to launch the forms, once after we forcefully updated many users who were sticking to 1.6 JRE
So after referring many documents, using some of the past experiences dealing with security certificates, I believe, have figured out how to get rid of those annoyances, minimum for a while.
Solution 1
Runtime parameters being removed after each JRE update
Add a new environment JAVA_TOOL_OPTIONS with a value -Djava.vendor=”Sun Microsystems Inc.”
You may add it under your specific user or system wide, which I usually prefer so that I don’t have to worry about the next user who will be using the same system (for some reasons)
Solution 2
Addressing JAVA security prompts
Trying to open the forms with latest JRE without adding exceptions will produce the following JAVA security error
Now we need to add the application URL with complete port details to the security tab of JRE
Now, we will be presented with the “toughest” situation to resolve, which will be explained later.
Clicking the More Information, JAVA presents more details about the digital certificate issues, stating the publisher of the digital certificate as UNKNOWN, hence this warning appears for the end user each time whenever the user tries to open a Forms based module.
You may require Domain Administrator/Administrator privileges to continue the below fixes
1st we have to retrieve the certificate from Oracle application server. Login as application manager user to LINUX environment (We’re setting up the entire stuffs for R12 application that is running on 64Bit Linux)
download $APPL_TOP/admin/appltop.cer file to local computer (If your implementation team has cloned the instances across, the appltop.cer will remain the same across different servers, so the single certificate integration with the user computer should deal with all different instances of the Oracle application (Test, Development & Production)
Once the certificate is downloaded or copied to the local computer, you need to install it. We’ll install the certificate twice, in two different locations.
Follow the images below for more details
We will install the certificate for the “Local Machine”, so that all users using the same computer will get benefitted
For the 1st instance, We MUST install this certificate to “Trusted Root Certification Authorities” Container.
For
Once the certificate is imported, We should install the certificate once again, Select “Local System” and follow the prompts. We don’t have to specify the container this time.
Now, trying to open a forms based module will present you another warning
Checking “Do not show this again for apps from the publisher and location above” will suppress this dialog until the certificate remains the same for the application server.
We are on VEEM+VMWare infrastructure from a while, yet I am paranoid to maintain copies of the backups on different media once after going through couple of nightmares. We take weekly cold backup for our ERP Production server, move the tar files to a standby Linux server, and move those backups once again to an external HDD.
So basically I have a full VM backed up, the same VM holds a weekly cold backup, standby Linux server holding a copy of the cold backup files & to finish it, again copied to an external HDD. The funniest part is, we are moving the entire VMs to a TANDBERG Quick Station as well!
Though everything works fine till date, the last part of the deal needs to intimate me about successful completion of copying the tar files to the external media, ie, HDD that is formatted using NTFS, so that I can use it on both Linux and Windows environments
Be warned: The below bash script only works in an environment that has an internal SMTP server (or I don’t know how to relay the messages through an external SMTP relay and to disappoint you further, I don’t care about relaying through external SMTP). In addition, you must be on Linux 6 and above to use the internal mail command as demonstrated below. Linux 5 doesn’t support many switches provided with the example.
Further, below example demonstrates the basic level of error capturing with “bash” scripts as well
if [ $? -eq 0 ]
then
echo "The files were successfully copied to external hard disk" | mailx -v -s "ERP Tar Files Moved to External HDD | Success" -S smtp=smtp://server.domain.com -S from="ERP Alerts <someone@example.com>" someoneelse@example.com,someone2@example.com
else
echo "Files were not copied to external HDD" | mailx -v -s "ERP Tar Files to External HDD | Failed" -S smtp=smtp://server.domain.com -S from="ERP Alerts <someone@example.com>" someoneelse@example.com,someone2@example.com
fi
[/code]
Try it and let e know whether it worked for you :)
You could run into a situation after the latest Java client runtime update, your R12 instance failing to load forms, complaining about the lower version of JRE. This is mainly due to the fact that, if your Windows OS is 64Bit, the latest java runtime update installs both 64Bit & 32Bit and most of the R12 environments run against 32Bit JRE.
Internet explorer (the only one browser that runs the JRE properly for EBS R12) loads the 64Bit java runtime by default from a 64Bit Windows OS & eventually the forms will fail to load.
At present the only possible solution to address this situation is to uninstall the 64bit version of java runtime, using “Programs and features” or “Add remove programs” based on the version of Windows you are using.