Category Archives: oracle apps

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

Advertisements

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*****’, ‘ ‘,45, 1)= 0 then 45 else instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 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*****’, ‘ ‘,45, 1), case when instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 1)>45 then instr(‘One Hundred Ninety Five Thousand Two Hundred Seventeen Qatari Riyals And Twenty Six Dirhams*****’, ‘ ‘,45, 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.

PLSQL Logic to AME Approver Resultset as Table Type

In some cases when we have to bring the approvers to the display screen to show it is better to have a query so that it can be easily mapped with VO.

For Example as the below query

SELECT SEQ_NUM, FULL_NAME APPRIVER_FULL_NAME, APPROVER_POSITION approver_position, APPROVER_ROLE APPROVER_ROLE, ROWNUM SEQ_N, USER_NAME, PERSON_ID, USER_ID, USED_IN, TENDER_REQUEST_ID, REQUEST_TYPE FROM THE ( SELECT CAST ( XX_GET_APPROVERS_LIST (:1) AS xxqf_et_approvers_table) FROM DUAL)

So In the below query it fetches all the details based on the query getting from table type. So below is the sample format for creating table function based on AME output.

 

CREATE OR REPLACE FUNCTION XX_GET_APPROVERS_LIST(l_request_id in number)
RETURN xx_approvers_table
as
l_approvers_list xx_approvers_table
:= xx_approvers_table ();
— l_request_id NUMBER := 7333;
g_next_approvers ame_util.approversTable2;
l_completeYNO VARCHAR2 (100);
applicationId NUMBER := 201;
LN_USER_NAME VARCHAR2 (1000);
lc_name VARCHAR2 (200);

l_approvers_list xxqf_et_approvers_table
:= xxqf_et_approvers_table ();
l_request_type VARCHAR2 (50);
l_requisition_id NUMBER;
l_rfq_id NUMBER;

cursor get_info_pos_based(p_pos_id in number) is select papf.person_id, fu.user_id, papf.full_name, fu.user_name
from per_all_people_f papf, per_all_assignments_f paaf , fnd_user fu
where papf.person_id = paaf.person_id and fu.employee_id = papf.person_id
and paaf.position_id = p_pos_id
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and rownum =1;

l_person_id number;
l_user_id number;
lc_full_name varchar2(2000);
lc_user_name varchar2(5000);
lc_position_name varchar2(5000);
lc_request_type varchar2(1000);

BEGIN

select REQUEST_TYPE into lc_request_type from xxqf_et_tender_eval_request where TENDER_REQUEST_ID = l_request_id;

ame_api2.getAllApprovers7 (
applicationIdIn => 20003,
transactionTypeIn => ‘XX_HIER’,
transactionIdIn => l_request_id,
approvalProcessCompleteYNOut => l_completeYNO,
approversOut => g_next_approvers);

IF g_next_approvers.COUNT > 0
THEN
FOR I IN 1 .. g_next_approvers.COUNT
LOOP
if (substr(g_next_approvers (I).NAME, 0, 4) = ‘POS:’)
then
DBMS_OUTPUT.PUT_LINE (‘AT MR POSITION Based Hierarchy’);
open get_info_pos_based(substr(g_next_approvers (I).NAME, 5));
fetch get_info_pos_based into l_person_id, l_user_id, lc_full_name, lc_user_name;
close get_info_pos_based;
l_approvers_list.EXTEND;
l_approvers_list (l_approvers_list.COUNT) :=
XXQF_ET_APPROVERS_T (
g_next_approvers (I).approver_order_number,
lc_user_name,
l_person_id,
l_user_id,
lc_full_name,
g_next_approvers (I).display_name,
‘MR Appr Hierarchy’,
‘BOTH’,
l_request_id,
lc_request_type);

else
l_approvers_list.EXTEND;
l_approvers_list (l_approvers_list.COUNT) :=
XXQF_ET_APPROVERS_T (
g_next_approvers (I).approver_order_number,
g_next_approvers (I).NAME,
g_next_approvers (I).approver_order_number,
g_next_approvers (I).approver_order_number,
g_next_approvers (I).display_name,
g_next_approvers (I).approval_status,
‘MR Appr Hierarchy’,
‘BOTH’,
l_request_id,
g_next_approvers (I).NAME);
end if;
END LOOP;
END IF;

FOR I IN (SELECT * FROM TABLE (l_approvers_list))
LOOP
NULL;
DBMS_OUTPUT.PUT_LINE (
‘ AFTER EXTEND Testing the Data :’
|| I.USER_NAME
|| ‘ display_name :’); –For Testing the Values
END LOOP;

RETURN l_approvers_list;
END;

 

Unable to launch the Java Virtual Machine Located at path: ..\..\jdk\jre\bin\client\jvm.dl

On running Jdevloper for first time you may see the below error

ERROR:
Unable to launch the Java Virtual Machine
Located at path:
..\..\jdk\jre\bin\client\jvm.dll

SOLUTION:
1. go to Control Panel->System->Advanced System Settings->Environmental Variables
2. Update the ‘Path’ setting to include the following directory: ‘<jdev home dir>\jdevbin\jdk\bin’
3. Reopen the jDeveloper.

Position Hierarchy in AME

To Define Position Hierachy I have created a custom transaction type in AME and in Approvals Management Business Analyst I went to Action types and selected the HR Position level.

Once this is created we have to

Class not found error and class format Error in OAF Jdeveloper

I had this problem when doing and Extension I tried to copy the class files from server to my machine and tried to compile it. It was giving me Class not found error. After that tried to import the classes it was giving me as class format error as per the below screenshot.

After some hours of time I was able to find the problem as the compiled versions are of latest version but the jdeveloper was taking the old version of java. I resolved by editing the jdev.conf file in Jdev-R12.2.4\jdevbin\jdev\bin and in that I have commented the version of 1.5 and enabled jdk version 1.8 as shown below.

The Jdk version is that I have already installed in my system and not a preinstalled one. The Instance is oracle apps R12.2.4 Instance.

#
# Directive SetJavaHome is not required by default, except for the base
# install, since the launcher will determine the JAVA_HOME. On Windows
# it looks in ..\..\jdk, on UNIX it looks in the PATH by default.
#
# SetJavaHome C:\Java\jdk1.5.0_04
#SetJavaHome C:\Program Files (x86)\Java\jdk1.8.0_172

Oracle apps Forms not opening with error message ClassNotFoundException FormsLauncher.class

I came across a Error with a new laptop while opening the forms.

I had java 1.6 update 27 and firefox 34. When I tried to open java it was not opening after trying some things then my forms was opened.

Below are the steps

1.Remove all Java versions. Try to install current version of java in my case it is 8 update 172

2.Try to have Firefox ESR version or Recomended IE version. In my case Firefox ESR 52.8.0

3. Disable TLS in Java: In newer Java Versions you have to disable by, Go to Java control Panel> Goto Advanced Tab> Click General or Security Tree. > If you see TLs 1.1 or TLS 1.2 uncheck them.

4. In Internet Explorer Go to Internet Options> Advanced> Scroll down to Security Disable / Un check all Use TLS 1.0, Use TLS 1.1 and Use TLS 1.2.

Note: Firefox ESR 60 will not support java plugin as of now.