Category Archives: Oracle

Stale data Error when trying to delete the Rows in OAF

When I tried to delete the rows I was getting stale data error as shown in the above screenshot.

I have analysed my VO that it does not have dependency like Master or Child and below is code.

for(Row row1 = A.first(); row1 != null; row1 = A.next())
{
System.out.println(row1);
if(row1 != null)
{
row1.remove();
}
}
getOADBTransaction().commit();

It is straight foward code to delete the row. I am getting the error on Commit on the code. I have searched and I have added the below line in the code and then it worked.

getOADBTransaction().setLockingMode( getOADBTransaction().LOCK_NONE);

What happens it the row is getting locked internally so I have put the above code in order to release the lock during delete.

Source

Advertisements

While comparing Invoice Aging Report and Trial Balance Report some records were missing in xla_trial_balances table

When we were Reconciling data for supplier outstanding amount we checked in Trial balance which was giving a different value and Aging Report was giving another value. So when we checked the records we were able to find that some records were missing in xla_trial_balances table. So Initially we thought whether to rebuild or not. After checking the same with functional we got to know that there was a setup missing.

Go to Payables Super User –> Setup –> Accounting Setups –> Subledger Accounting Setup –> Open Account Balances Listing Definitions.

In this Screen the Trial Balance Accounts will be defined for Reporting purpose. Here we have to add the expected Natural Account so that it will populate in the Trial Balance Report, which In turn will fill data in xla_trial_balances table.

 

Oracle Apps HRMS Leave Balance Extract Query

Below is the Query to get HRMS Leave Element Balance from Payroll in Monthwise

SELECT Papf.Employee_number,
Papf.Full_name,
pbvv.VALUE SICK_DEDUCTED_ASG_YTD,
pbvv1.VALUE SICK_DEDUCTED_ASG_ITD,
ppa.effective_date
FROM Per_all_assignments_f Paaf,
Per_all_people_f Papf,
Per_all_positions Hapf,
Per_jobs Pj,
Per_grades Grd,
Pay_people_groups Ppg,
Pay_assignment_actions Paa,
Pay_payroll_actions Ppa,
pay_balance_values_v pbvv,
pay_balance_values_v pbvv1
WHERE Papf.Person_id = Paaf.Person_id
AND Paaf.Position_id = Hapf.Position_id(+)
AND Paaf.Job_id = Pj.Job_id(+)
AND Paaf.Grade_id = Grd.Grade_id(+)
AND Paaf.People_group_id = Ppg.People_group_id
AND Paaf.Assignment_id = Paa.Assignment_id
AND Paaf.Payroll_id = Ppa.Payroll_id
AND Paa.Payroll_action_id = Ppa.Payroll_action_id
AND pbvv.assignment_action_id = paa.assignment_action_id
AND pbvv1.assignment_action_id = paa.assignment_action_id
AND pbvv.DEFINED_BALANCE_ID =2109 –Balance ID
AND pbvv1.DEFINED_BALANCE_ID =3116 –Balance ID
AND Paaf.Primary_flag = ‘Y’
AND sysdate BETWEEN Paaf.Effective_start_date AND Paaf.Effective_end_date
AND sysdate BETWEEN Paaf.Effective_start_date AND Paaf.Effective_end_date
AND sysdate BETWEEN papf.Effective_start_date AND papf.Effective_end_date
AND TO_CHAR(ppa.effective_date, ‘MMYYYY’) = :MMYYYY –Month
ORDER BY Papf.Employee_number

Oracle apps OAF Page Import Error: Exception in thread “main” java.lang.NoClassDefFoundError: oracle/dms/console/DMSConsole

I usually import pages through Linux Console. This time I tried to Import a Page by Windows machine(Development Machine). I got the below Error.

 

Exception in thread “main” java.lang.NoClassDefFoundError: oracle/dms/console/DMSConsole
at oracle.jdbc.driver.DMSFactory.<clinit>(DMSFactory.java:51)
at oracle.jdbc.driver.PhysicalConnection.createDMSSensors(PhysicalConnection.java:3821)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:632)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:230)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:207)
at oracle.adf.mds.tools.util.ConnectUtils.getDBConnection(ConnectUtils.java:166)
at oracle.jrad.tools.xml.importer.XMLImporter.importDocuments(XMLImporter.java:232)
at oracle.jrad.tools.xml.importer.XMLImporter.main(XMLImporter.java:88)
Caused by: java.lang.ClassNotFoundException: oracle.dms.console.DMSConsole
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
… 11 more

After Searching I came to find that I had to modify a setup

Open the import.bat file(Import file will be in Jdeveloper (jdevbin\oaext\bin)) and modify the below line

set CLASSPATH=%JRAD_ROOT%\jdev\appslibrt\ebsuix.jar

to

set CLASSPATH=%JRAD_ROOT%\jdev\appslibrt\ebsuix.jar;%JRAD_ROOT%\lib\dms.jar

Now try to Import.

 

 

Jdeveloper Error : Error initializing server: At least one valid code-source or import-shared-library element is required

I came across this error when trying to run a page from my Jdeveloper.

1

18/09/23 15:51:30 Error initializing server: At least one valid code-source or import-shared-library element is required for shared-library “global.libraries” in /C:/SOFT/p19170592_R12_GENERIC-Jdev-R12.2.4/jdevbin/jdev/system/oracle.j2ee.10.1.3.43.6/embedded-oc4j/config/server.xml.
18/09/23 15:51:30 Fatal error: server exiting
Process exited with exit code 1.

I have migrated from old to new one from a different location and this was causing the problem.

So if you look into the error it will tell that it was not able to find the files in the server in the particular location. So we have change the correct path which is in the error.

You have to go to this path and change the location in server.xml file in your Jdeveloper.

Environment Variable has to corrected while configuring Jdeveloper

jdevbin/jdev/system/oracle.j2ee.10.1.3.43.6/embedded-oc4j/config/server.xml.

 

Thanks

Oracle apps AME Error : An invalid approver has been encountered while processing the action type hr position level.

I have encountered the below error in AME Position hierarchy.

AME Error in Position Hiearchy

I have checked completely the Position Hierarchy was fine and User, Person was valid. Then on searching I came to know that I have changed something.

Change I did previously

Previously for a user the notification was not coming so checking the wf_roles table the position was present however on checking wf_user_roles table the user was not there. So I have inactivated and deleted the role using below code

DECLARE
lc_role_name apps.wf_roles.name%TYPE := ‘POS:2115549’;
BEGIN
wf_directory.setadhocroleexpiration(lc_role_name, sysdate -1 );
COMMIT;
wf_directory.setadhocrolestatus( lc_role_name, ‘INACTIVE’);
COMMIT;
wf_directory.deleterole(lc_role_name , ‘WF_LOCAL_ROLES’ , 0);
COMMIT;
END;
UPDATE wf_local_roles x
SET x.expiration_date = (SYSDATE-1)
,x.status =’INACTIVE’
WHERE x.name = ‘POS:2115549’;
COMMIT;
begin
wf_purge.AdHocDirectory();
end;

Solution:

As I have removed the wf role for the position it gives error in the AME so we have to recreate the position with updated user in wf_role table.

The Concurrent Program Synchronize WF LOCAL tables which will be in System Administrator Responsibility will recreate the position in wf_roles.

After running the concurrent program the position and users will be updated on wf_roles and wf_user_roles table.

Reference

Help >Diagnostics > Examine Error: Function Not Available to this Responsibility. Please check with your system administrator

If you are getting the error Function Not Available to this Responsibility. Please check with your system administrator then follow the below steps

 

Step 1. Navigate to System Administrator Responsibility > Profile > System

Step 2. Query for profile ‘Utilities:Diagnostics’

Step 3. At the ‘Site’ level give value as ‘Yes’

Step 4. Save and retry and users will not get above message, and they will be able to navigate to Help >Diagnostics > Examine

Splitting of words in two statements in SQL using INSTR and SUBSTR functions

Below is the code snippet

 

 SELECT SUBSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, 0, CASE WHEN INSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘, 49, 1) = 0 THEN 49 ELSE INSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘, 49, 1) END) first_var, SUBSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, INSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘, 49, 1), CASE WHEN INSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘, 49, 1) >= 49 THEN INSTR ( ‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘, 49, 1) ELSE 0 END) sec_var FROM DUAL;

Delete or Remove or Clear Approvers in AME using AME_API2

Below is the AME Api to clear the approvers in AME Transaction. So all the approved status will be reversed and will remove status in AME.

AME_API2.CLEARALLAPPROVALS (20003,
‘XXQF_BUYER’,
LN_REQ_ID);

Soon I will wirte a post in detail about the flow of the AME Api that can be used in PLSQL

Approve and Reject Approvers in AME by AME_API2 Api

In AME we will be able to set Approve and Reject status in AME using AME_API2.

So in AME on Test Workbench we can see that the approver gets approved or rejected and AME_API will give approvers based on past approvals.

Below is API to Approve and Reject. Only the parameter is different to Approve and Reject

AME_API2.UPDATEAPPROVALSTATUS2 (
APPLICATIONIDIN => 20003,
TRANSACTIONIDIN => LN_REQ_ID, –?GENERALLY ITEMKEY
APPROVALSTATUSIN => AME_UTIL.APPROVEDSTATUS, –?APPROVED_STATUS_OR_REJECTED_STATUS
APPROVERNAMEIN => LC_USER_NAME,
TRANSACTIONTYPEIN => LC_GET_AME_TRX_TYPE);

AME_API2.UPDATEAPPROVALSTATUS2 (
APPLICATIONIDIN => 20003,
TRANSACTIONIDIN => LN_REQ_ID, –?GENERALLY ITEMKEY
APPROVALSTATUSIN => AME_UTIL.REJECTSTATUS, –?APPROVED_STATUS_OR_REJECTED_STATUS
APPROVERNAMEIN => LC_USER_NAME,
TRANSACTIONTYPEIN => LC_GET_AME_TRX_TYPE);

Thank you.