Oracle Application R12 12.2 one user cannot logon

Updated on: 28th Feb 2023

Our previous solution to fix “one user cannot” logon was doomed by additional issues sooner than expected. We had to regenerate the jar files using ADADMIN finally to close the issue.

===

We are using Oracle EBS R12 for last 12 years and recently upgraded to 12.2.10. We hardly had much technical issues with the instance until recent days, then came a real annoyance.

One particular user was unable to logon. Instead of OA_HTML page, he was either getting redirected to logout page or the OA_HTML page keeps on trying to load. One of the workarounds we came across was, resetting his password. Once the password changed, he was taken to setup password page and to regular landing page. Well, this also turned into troublesome as each time his session expired, we were forced to reset his password.

So what went wrong? We had an issue with concurrent manager outputs and logs opening into a blank page recently. This happened after RMAN backup up, that is scheduled and we had to run autoconfig to fix this issue. Although autoconfig fixed that issue, opened issues with user logon, as explained above. We kept on watching the alerts for possible errors and there were none. Anyhow, we decided to do the following as the ONLY one relevant document we were able to find on Oracle support was “Unable To Login With One User On Oracle EBS Application (Doc ID 2480008.1)” and the case described was not our case.

This is what finally worked:

  • Cleared global cache (didn’t fix issues)
  • Shutdown Apps tier
  • Ran autoconfig on database Tier
  • Ran autoconfig on both Run and Patch file systems
  • Restarted Apps tier and the issues were resolved

We really don’t know what happened after the RMAN & we didn’t update/altered the OS (Oracle Linux 7). Well, things could go south once in a while, after all Oracle EBS is such a huge and complex repository!

Oracle “directory” object | OS level permissions

We are using Oracle EBS R12 and for a custom module, wanted to log the Oracle seeded API outputs to custom log files for later scrutiny & error corrections.

Following the standard procedure, create the folder like below

create or replace directory OMS_LOGS as '/u01/applmgr/oms_logs'

and started testing the directory using SQL Developer where I am logged in as APPS user

I kept getting the below errors

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 8

and the detailed errors said this could be due to OS level access permissions. Luckily I landed on a stackoverflow discussion & one of the answers clearly said this error “could” be due to the file/folder permissions for user “oracle”.

As “root” I changed the permissions for the path “/u01/applmgr/oms_logs” like below

chmod g+w /u01/applmgr/oms_logs

and that fixed the invalid file operation errors. Hope this helps someone out there!

Oracle Application | “apps” user account is locked!

Today while running automatic configuration on the Application Tier, I entered the “apps” user password wrong! After the automatic configuration completed, having errors, I couldn’t start the Application tier.

It just said invalid password or database is not open.

Later after going through adautoconfig log files, I could see a number of failed connection attempts stating the account was locked, however I least expected this account to be “apps” (issues that one runs into when not properly exposed to the stack)

I opened SQL from application user and tried to login as apps/apps & was told the account was locked. I unlocked the apps account and ran the autoconfig once again, this time supplying the correct password. Everything went smooth and I was able to start the Application Tier.

So this is what happened. I supplied a wrong password for autoconfig & consecutive attempts to connect database from different scripts used the same credentials & after 10 failed attempts the account got locked up! causing rest of the issues.

So, be very careful when you are supplying apps password to scripts. As I am not an APPS DBA, it took me a while to figure this one out!

Optionally you may apply a better solution as mentioned here. Regardless, just be careful when you are supplying “apps” user password

Oracle Application R12 | Using Microsoft Edge Chrome for versions 11 & 12.0.xx

Updated on 8th October 2021 for Microsoft Edge Version 94.0.x.x & for Windows 11 that comes without Internet Explorer (or it doesn’t allow you to start Internet Explorer)

A much easier method is available with versions later than the one I used earlier.

Step# 1. Go to Edge settings (type edge://settings in the address bar and select “Default Browser”

Change “Allow sites to be reloaded in the Internet Explorer mode” to “Allow”, that will require you to restart the browser. Please restart

Step# 2. Now enter the address of your Oracle Application in the address bar and wait for the login page to load. Once loaded, click on the “three dots” menu by the extreme right of the browser and select “Reload in Internet Explorer mode”

This will open another dialog box as seen with the above image. Toggle “Open this page in Internet Explorer mode next time” and click “Done” button, that will produce more notifications like the one you could see with the below image.

It asks you to leave the Internet Explorer mode and another button “Show in toolbar”. This is an interesting button, clicking that starts showing a small tool button in the tools area and the button will reload the current page in “Internet Explorer mode”.

That’s all. Just click the toolbar button and your Oracle Application login page will be loaded in “Internet Explorer mode” immediately.

Toggling the switch to “Open this page in Internet Explorer mode next time” in the “Step# 2” adds an entry to “Internet Explorer mode pages” area for your current page and the entry is valid for next 30 days. Microsoft is expecting you to modernize your “page” within those next 30 days ;). Once this entry expires, you must get the “Open this page in Internet Explorer mode next time” dialog box once again.

October 8, 2021 update ends here.

After 25 years ever since it was launched, Windows 11 will be the first OS that is not shipping with Internet Explorer.

How does this matter to Businesses those use Oracle Application versions 11 & 12.0.xx? Well, currently Internet Explorer is the only one browser that allows to load Oracle JRE (NPAPI client) for Oracle Forms, on which much of the Oracle module rely.

Oracle Applications has patched the latest releases with a technology called JWS (Java Web Start), that let’s the users to download a jnlp file from the application and Oracle Java Run Time to start in desktop mode (without being loaded in a browser session) loading Oracle Forms. Well, this enhancement is not available for Application versions 11 & 12.0.xx

Microsoft was expecting a huge cry from the Businesses that use Oracle Applications/legacy implementations those cost millions of dollars and fine tuned for Internet Explorer. Cutting these businesses could mean loads for Microsoft, So they have integrated “IE Mode” into their chromium based “Edge browser”, which is the default browser on Windows 11. IE Mode makes Edge Chrome to “act” as if it were Internet Explorer for legacy Web based applications & loads NPAPI clients like JRE.

Today let us see how to configure Microsoft Edge Chrome for Oracle Applications.

Start Microsoft Edge Chrome & open “Settings”

Click on Default Browser & spend a moment to check currently set options.

Let us change few of those settings like shown below.

Let Internet Explorer open sites in Microsoft Edge -> Change to Allow

Allow sites to be reloaded in Internet Explorer mode ->Change to “Allow”. This will require you to restart the browser.

You must add the sites those you want to open in IE mode by clicking “Internet Explorer mode pages” Add button. Such pages will have maximum 30 days validity. Without, JRE will not load and will prompt you to download it from the default location.

Now, let us click the restart button & give it a try.

That’s all folks.

Oracle Inventory | Aging Analysis

Quite often auditors could come with some strange requirements (especially when they do not understand the business) & we had to formalize on foot couple of years back. From those days, I always wanted to put together few things and post this, which is happening today.

Our requirements were to list the receipts in 7 buckets, ie, within last 120 days, between 121-180 days, beween 181-1 year, between 1-2 Years, between 2-3 Years and 3-5 Years and 5 Years+ (and still in stock!)

Please note, I am using a custom function “omsconcorgqty_f (code provided below, that concatenates the subinventory quantities into a single column. This function could raise an exception when the column length exceeds 4000 characters.

Minimum requirements: Your EBS instance must be using Oracle database 11gR2 to use “LISTAGG” that is used with the custom function. In addition to, I am using PIVOT, that is supported from 11g.

The below example considers a situation where business has a WMS enabled organization. You can pass NULL to :P_WAREHOUSE_ID when not applicable.

If your organization uses multiple UOM for items, you must adjust the below query accordingly, especially for the cost part.

The average cost for the item is picked using the following logic.

If the :P_ORG_ID has item quantities, then the cost for the item will be picked from the same organization. If the :P_ORG_ID organization doesn’t have quantities and warehouse has, then the cost from :P_WAREHOUSE_ID organization will be picked. So there could be inventory value differences and you shouldn’t use the inventory values derived from this exercises if the above said conditions apply.

One of the challenges you are going to face is to identifying the transaction types those should be considered as a receipt. For example, we receive materials in stock through the following transactions.

SELECT a.transaction_type_id
       , a.transaction_type_name
       , a.transaction_source_type_id
       , b.transaction_source_type_name
       , a.transaction_action_id
       , c.meaning
    FROM mtl_transaction_types a
       , mtl_txn_source_types b
       , mfg_lookups c
   WHERE a.transaction_source_type_id = b.transaction_source_type_id
     AND a.transaction_action_id = c.lookup_code
     AND c.lookup_type = 'MTL_TRANSACTION_ACTION'
     AND a.transaction_type_id  IN (12,15,18,40,41,42)
ORDER BY transaction_type_id;

If your organization uses additional transaction types for receiving stock to the stores, please include them to the solution. Here the solution is about scanning the entire MTL_MATERIAL_TRANSACTIONS table & based on the volume of data/hardware resources, it could be a very painful ordeal. My suggestion is to run the query when there are least numbers of “issues” are expected.

I will try to explain the approach step by step, for better understanding.

At the first level we will pick all items for the organization(s) from the MTL_ONHAND_QUANTITIES & use this list for picking up items from the MTL_MATERIAL_TRANSACTIONS.

with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
)

The above code portion will pick all the items from quantities table that has on-hand quantities.

inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)

This code portion groups data into multiple age buckets, based on business requirements. We will be using few Analytical functions to determine balance in individual age buckets. The final SQL block will look like below.

with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
),
inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)
select inventory_item_id, age_in_days, age_bucket_balance from (
select inventory_item_id, age_in_days,
case when (running_balance <= oh_qty) then qty
else nvl(oh_qty - previous_balance, oh_qty)
end age_bucket_balance from( 
select a.*, b.oh_qty from inv_data a
inner join ohqty_data b on b.inventory_item_id = a.inventory_item_id
order by a.inventory_item_id, a.age_in_days
))
where age_bucket_balance > 0;

Problem & The solution.

Oracle inventory issues the quantities following FIFO method. Say you received 10 pieces of a material in January 2021 and additional 10 pieces of the same item on February 2021. Think of a transaction that issues 8 quantities in March 2021. The balance sheet for the item after the transaction will look like below

The given code block does this calculation! I will find some time in near future for step by step explanation for the logic & solution. I’ve many people to thank for this solution, especially my colleague Sherin Thomas Mathew, Iudith Menzel and our sales team that stood with me through a month long experiments and wrong outputs.

Now let us go to the final solution.

Create a table

 CREATE TABLE OMSINVSTAGE 
   (	INVENTORY_ITEM_ID NUMBER, 
	AGE_IN_DAYS NUMBER, 
	CLOSE_BALANCE NUMBER
   )  ;

Let us minutes change the code block with an insert

INSERT INTO OMSINVSTAGE(INVENTORY_ITEM_ID, AGE_IN_DAYS, CLOSE_BALANCE)
with ohqty_data as (
select inventory_item_id, sum(transaction_quantity) oh_qty from 
mtl_onhand_quantities 
where 
organization_id in (:p_org_id,:p_warehouse_id)
group by inventory_item_id
),
inv_data as (
select inventory_item_id, qty, age_in_days,
sum(qty) over (partition by inventory_item_id order by age_in_days) running_balance,
sum(qty) over (partition by inventory_item_id order by age_in_days)-qty previous_balance
from (
select inventory_item_id, sum(transaction_quantity) qty, 
age_in_days from (
select inventory_item_id, transaction_quantity,
case 
when (trunc (sysdate) - trunc (mmt.transaction_date)) <= 120 then 1
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 121 and 180 then 2
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 181 and 365 then 3
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 366 and 730 then 4
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 731 and 1095 then 5
when (trunc (sysdate) - trunc (mmt.transaction_date)) between 1096 and 1825 then 6
when (trunc (sysdate) - trunc (mmt.transaction_date)) > 1825 then 7
end age_in_days
from mtl_material_transactions mmt
where 
1=1
and inventory_item_id in (select distinct inventory_item_id from ohqty_data)
and organization_id in (:p_org_id,:p_warehouse_id)
and transaction_type_id in (12,15,18,40,41,42)
)
group by inventory_item_id, age_in_days
)
)
select inventory_item_id, age_in_days, age_bucket_balance from (
select inventory_item_id, age_in_days,
case when (running_balance <= oh_qty) then qty
else nvl(oh_qty - previous_balance, oh_qty)
end age_bucket_balance from( 
select a.*, b.oh_qty from inv_data a
inner join ohqty_data b on b.inventory_item_id = a.inventory_item_id
order by a.inventory_item_id, a.age_in_days
))
where age_bucket_balance > 0;

Commit & don’t forget to delete rows before NEXT run. Use a global temporary table for best results.

Once the rows are populated to our table, we can execute the final code block to generate the Ageing report.

WITH DATASET AS(
Select pivot_data.* from (
select 
inv_data.INVENTORY_ITEM_ID,
inv_data.close_balance TYPE_QTY,
inv_data.AGE_IN_DAYS from OMSINVSTAGE inv_data
where 
1=1
--inv_data.inventory_item_id = 27626
and inv_data.close_balance > 0
)
PIVOT (sum(type_qty) FOR AGE_IN_DAYS IN (1 as "120 Days",2 "121-180 Days",3 "181-365 Days",4 "1Yr-2Yr",5 "2Yr-3Yr",6 "3Yr-5Yrs",7 "5Yrs+")) pivot_data)
SELECT msi.concatenated_segments,  msi.description,msi.primary_uom_code,
CASE WHEN nvl((Select sum(transaction_quantity) from mtl_onhand_quantities moq where moq.inventory_item_id = msi.inventory_item_id and moq.organization_id=:P_ORG_ID
group by moq.inventory_item_id,moq.organization_id),0) > 0 then (
Select item_cost from cst_item_costs cic 
where cic.inventory_item_id = msi.inventory_item_id and cic.organization_id = msi.organization_id and cic.cost_type_id = 2)
ELSE
(Select item_cost from cst_item_costs cic 
where cic.inventory_item_id = msi.inventory_item_id and cic.organization_id = :P_WAREHOUSE_ID and cic.cost_type_id = 2)
END item_cost,
dataset.*,
OMSCONCORGQTY_f(:P_ORG_ID, :P_WAREHOUSE_ID, dataset.inventory_item_id, msi.primary_uom_code) org_quantity FROM DATASET
INNER JOIN mtl_system_items_kfv MSI ON MSI.INVENTORY_ITEM_ID = DATASET.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID=:P_ORG_ID
ORDER BY  msi.concatenated_segments;

omsconcorgqty_f function

This function adds (W) before the subinventories from :P_WAREHOUSE_ID organization for easier identification.

CREATE OR REPLACE FUNCTION omsconcorgqty_f (
    p_org_id        IN  NUMBER,
    p_warehouse_id  IN  NUMBER,
    p_item_id       IN  NUMBER,
    p_uom           IN  VARCHAR2
) RETURN VARCHAR2 IS
    l_qty_string VARCHAR2(4000);
BEGIN
    SELECT
        LISTAGG(subqty, ',') WITHIN GROUP(
            ORDER BY
                subqty
        )
    INTO l_qty_string
    FROM
        (
            SELECT
                inventory_item_id,
                CASE
                    WHEN organization_id = p_warehouse_id THEN
                        '(W)'
                        || subinventory_code
                        || '('
                        || to_char(SUM(transaction_quantity))
                        || ')'
                    ELSE
                        subinventory_code
                        || '('
                        || to_char(SUM(transaction_quantity))
                        || ')'
                END subqty
            FROM
                mtl_onhand_quantities_detail moq
            WHERE
                    moq.inventory_item_id = p_item_id
                AND moq.transaction_uom_code = p_uom
                AND organization_id IN ( p_org_id, p_warehouse_id )
            GROUP BY
                inventory_item_id,
                subinventory_code,
                organization_id
        )
    GROUP BY
        inventory_item_id;

    RETURN l_qty_string;
END;

Hope this helps

RVTPT-020: Subroutine rvtoe_RmaPushApi() – EQuantity cannot be greater than original ordered quantity. returned error

A single music track, movie, event, product… changes the life for many & in my case it was a single API by Oracle!

“oe_order_pub.process_order” that comes with Oracle Applications (We are using EBS R12)

The firm for which I work is using an inhouse developed module for the complete retailing & I take the credit of developing the full solution using this ONE API/around this API.

From a mere “Oracle Forms & Reports” developer with some knowledge about Oracle database, developing around this single API to facilitate Sales Orders and Returns (RMA) slowly shaped me in to whatever I am today.

We are using this custom module from last 11 years and I wouldn’t say there were no issues. We’ve many sales outlets and most of them are connected to the datacenter using ADSL data lines. Sometimes the connections caused, other times code caused, few other times some internal bugs caused problems were there, however against the volume of sales transactions those we make yearly, limited to numbers those could be counted in fingers.

So, recently I was contacted by the sales team, to resolve an issue with a sales return, with lines stuck “Awaiting Return” status. While inspecting the transaction, I realized that the salesmen tried to return this SO multiple times and instead of 2 lines against the sales order, there were 20 lines (10 attempts). I cleared whole those lines with errors and tried to receive the materials once again, bringing up the error:

RVTPT-020: Subroutine rvtoe_RmaPushApi() - EQuantity cannot be greater than original ordered quantity. returned error
 Cause:        Subroutine rvtoe_RmaPushApi() - EQuantity cannot be greater than original ordered quantity. returned an in

Eventually, I landed upon the Oracle support document “RMA Receipt Error:RVTPT-020: Subroutine rvtoe_RmaPushApi() – EQuantity Cannot Be Greater Than Original Ordered Quantity (Doc ID 2409611.1)” & according to the document, this situation arises when there are multiple transactions trying to do a RMA against the same quantities! So I ran couple of quick queries like below:

Select * from oe_order_lines_all where header_id  = (Select header_id from oe_order_headers_all where order_number='18016698');

Fetched all the line ids from the lines table against the order number & then tried to see where exactly the line ids were refernced. Whenever a RMA is facilitated the lines table fills in the columns “REFERENCE_HEADER_ID” and “REFERENCE_LINE_ID” with the header_id and line_id values from the original sales order. All I had to make sure that the line ids were referenced multiple times.

Select * from oe_order_lines_all where reference_line_id IN (4656844, 4656845);

As expected, I was able to find four lines (expected 2 lines only) and was able to track down the 2nd RMA that was automatically created by the API due to some unknown reasons (I said there were few problems using the API)

Based on the suggestions available with the support document, I cancelled the duplicate RMA transaction (Actually another Sales Order with the next immediate document number) & created a new receipt for the Sales Order that was stuck with lines having “Awaiting Return” flow status.

Hope this helps few out there.

rajesh

Windows 10 | Java client will not load on Internet Explorer

Hello guys

Internet explorer? Java client? Yes! Oracle E-Business Suite (Oracle Apps) uses NPAPI client when accessed through a web browser for Oracle Forms/Reports based applications. Currently the only one browser that allows the NPAPI client to load is Internet Explorer.

Depending upon the complex JAVA dependencies your version of Oracle Applications must be supporting a particular version of java client. Although you can use switches to load latest versions of JRE for your legacy Oracle application, not always advisable as I’ve experienced non-responsiveness & freezing.

Windows 10 version 2004 (Released by late May 2020) & subsequent cumulative updates MUST be the reason, causes a major issue with Internet Explorer (as on 26th June 2020). Once you installed & removed a higher version of JRE, JRE 6.x will not load anymore on the browser. You can reset Internet Explorer, reset your JRE & what not? Trying to open the Oracle forms based application would show the plugin being loaded and that’s all!

Strangely, I can see this issue is with only JRE 6.x when JRE 7.x and later load as usual without any problems. So if you think you have tried “everything” already & landed on this page, let us give it another try.

You can divide the below tasks into two sections. If JRE 6.x is the only one java client remaining after removing all other versions & if the JAVA_HOME or other Java related keys are not set under environment variables, just delete the “Sun” & “Java” Folders from “AppData” “Roaming”, “LocalLow” & “Local” folders. You may delete the “Javasoft” entry from “Computer\HKEY_CURRENT_USER\SOFTWARE\JavaSoft” registry path also. Try to load Oracle forms based application and if the java client is NOT at all loading, follow the below instructions.

Uninstall Java Software

After uninstallation remove all remnants of Java software from the system.

This should open the appdata folder the current user.

Let us find and delete all “Java” and “Sun” folders and content within those folders. Please note, if you are using JAVA for other than accessing Oracle EBS, the following instructions may result disastrous situations. Hence I recommend you to continue with utter caution.

Delete “Sun” and “Java” folders as I have said before from “Roaming”, “LocalLow” & “Local”

Open RegEdit

and open the below path

Computer\HKEY_CURRENT_USER\SOFTWARE\JavaSoft

Delete the “JavaSoft” folder completely.

Now switch to “Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\JavaSoft”

This is the registry path where 32 Bit version of Java software is installed. Delete this folder also (if exists after uninstallation).

Now you are ready to re-install the Java client once again. I would prefer a computer restart prior attempting re-installation, which is not mandatory.

After the re-installation of JRE, opening the internet explorer should ask you for permission to enable the client and Oracle EBS should able to load the NPAPI client properly. Do let me know whether it helped you.

References:

Regards,

rajesh

Oracle Application R12 | Print custom report using CUSTOM.pll

Hello guys,

Happy New Year!

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

frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

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.

Enjoy!

rajesh

Oracle Application R12 | The Function Is Not Available Under The Responsibility

Hello guys

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

https://knoworacle.wordpress.com/2012/11/03/the-function-is-not-available-under-the-responsibility/

regards,

rajesh

Oracle E-Business Suite | Should you clone on your desktop using Virtual Machine?

Hello guys

A long thread name? Well the topic is vast, hence the long name. I’ve been dealing with Oracle EBS or applications R12 from last 9 years. Initially I was ONLY developing for the new infrastructure, that gradually changed to taking care of the whole instance.

Painfully, however definitely I did familiarize myself with Linux (RHEL) & the database, exposing myself to building systems with copies of EBS running for development & testing.

Our instance is approximately 650GB as on date, including both application stack and database & a cold backup is hardly 125GB in tar balls. I have attempted & succeeded to build the cloned instances on my home desktop machine many times, however the performance was a huge issue, forcing me to discard the setups quite often.

This time, I decided NOT to discard as my new desktop at home is a beast compared to my previous machine, that was neither less a best ;) & to figure out “something” that will address the “performance” bottleneck.

I created a new VM using Oracle VirtualBox with following specs

6 processors, 20GB memory & 2 fixed size VDI files (120GB, 600GB) respectively for application and database repositories. I was aware of a limitation already, I was setting up the VDI files on a Western Digital Green series 2TB drive! which spins at 5400RPM!

Well, everything went smooth & and I had the instance up and running in couple of hours time & this time, the response of the instance was awesome. I even boasted about finally winning over the “biatch” to my team & sat back feeling “too proud” for the moment.

Next day (Month End 30th April 2019)

I am all excited after figuring out a way to flush GNOME desktop environment & replace it with Xfce & new tricks…

Started the VM at home, started the EBS instance and tried to access the instance from the same machine. I couldn’t even get the login page…something was gravely wrong. I decided to check the performance monitor and found the following:

Slowly I was forced to recognize the terror! The Standard Concurrent manager was configured to process 25 requests at the same time with a cache of 5 & 30 seconds sleep between the requests. Our month end has a number of scheduled jobs + Gather schema statistics in the queue. My VM was breaking up with the I/O. My 2TB storage oriented disk was NOT spinning fast enough to provide the data for the processes & I was left with the BIGGEST question of the hour “Now what?”

I stopped the Concurrent manager, adjusted the processes to 5 for Standard manager & restarted the instance. Left the instance running whole night and 1st May morning, the instance was back to normal performance as whole the scheduled jobs were finished during the night.

Next day I added one 1TB SSD & moved the application & database VDI files over to it. I was able to get the login screen within 2-3 seconds once after the application started from the VM. I submitted number of create accounting and other resource hungry jobs, which were completed in few seconds time…

Now, my setup is ONLY for the sake of it. It doesn’t have many users, it is idle most of the time & almost every day I shutdown the desktop machine after a day’s usage. This might not be the scenario at a real TEST environment. You may need to implement archive logging & RMAN, those all requiring more space & faster access to storage. A Desktop has less resources & the ONLY positive element you are going to live with is the pleasure of building it & knowledge gained while fixing few new issues.

So, can you build a performance oriented R12 using Desktop environment, the short answer is yes. Does it worth the efforts? Well, definitely YOU are the ONE who have to answer it.

Follow the space & soon I will post a thread explaining the entire exercises. If you are in a hurry, you may refer this

The above article loosely explains how to clone R12 instance on Linux 7. However the same could be followed for Linux 6 as well (both RHEL & OEL)

regards,

rajesh