OAF import script was wrong and so Region was not Imported

Requirement : To add a Region(Initiator Details) to a Notification for second level approver.

Problem faced : I have added that particular region and I have Imported the script but still it was not reflecting when trying to Import. We have tried to print the Region using jdr_utils and then we are able to see it was not reflecting.

We came to know that there is problem in Import script. Below was the script

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/oracle/apps/per/selfservice/appraisals/webui/ApprNtfFinalRN.xml -username apps -password apps -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= <>)(PORT=<>))(CONNECT_DATA=(SID=<>)))” -rootdir $JAVA_TOP/oracle/apps/per

Solution : As my collegue Bala helped then he was able to find that the path of rootdir was wrong and rootPackage was not given. After we have added the page imported successfully. Below is the correct script. So the rootPackage is that which changes the exact point in the Filesystem.

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/oracle/apps/per/selfservice/appraisals/webui/ApprNtfFinalRN.xml -username apps -password apps -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= <>)(PORT=<>))(CONNECT_DATA=(SID=<>)))” -rootdir $JAVA_TOP/oracle/apps/per/selfservice/appraisals/webui/ -rootPackage “/oracle/apps/per/selfservice/appraisals/webui”

Share your Views

 

Advertisements

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

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

Unable to open seeded Reports in Reports Builder

Steps to open a sample report

1. Apply patch to upgrade to Developer 10.1.2.3.0, which is the certified version on Windows 7 (32-bit).
For details, please review:

How to Install Developer Suite 10.1.2.3 on Windows Vista or Windows 7 (32bit) (Doc ID 559067.1)

2. Open Reports Builder 10.1.2.3.0.

3. Connect to database.

4. Open seeded report (for example, APXT7RVT.rdf).

 

As per the Below oracle document the steps has been given above.

Doc ID 2037038.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=107908767347540&parent=DOCUMENT&sourceId=2224886.1&id=2037038.1&_afrWindowMode=0&_adf.ctrl-state=4lxwf7w4_134

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;