Search from 700 + Posts

May 6, 2019

Query to find next approver in Oracle EBS


SELECT     LEVEL, e.*
      FROM (SELECT DISTINCT papf.person_id, papf.employee_number,
                            papf.full_name "EMPLOYEE_FULL_NAME",
                            paaf.supervisor_id,
                            papf1.employee_number "SUPERVISOR_EMP_NUMBER",
                            papf1.full_name "SUPERVISOR_FULL_NAME",
                            ppt.user_person_type,
                            papf.business_group_id
                       FROM apps.per_all_people_f papf,
                            apps.per_all_assignments_f paaf,
                            apps.per_all_people_f papf1,
                            apps.per_person_types ppt
                      WHERE papf.person_id = paaf.person_id
                        AND papf1.person_id = paaf.supervisor_id
                       -- AND papf.business_group_id = 142
                        AND papf.business_group_id = paaf.business_group_id
                        AND TRUNC (SYSDATE) BETWEEN TRUNC(NVL(papf.effective_start_date,sysdate)) AND TRUNC(NVL(papf.effective_end_date,sysdate))
                        AND TRUNC (SYSDATE) BETWEEN TRUNC(NVL(paaf.effective_start_date,sysdate)) AND TRUNC(NVL(paaf.effective_end_date,sysdate))
                        AND ppt.person_type_id = papf.person_type_id
                        AND ppt.user_person_type <> 'Ex-employee'
                        ) e
CONNECT BY PRIOR person_id = supervisor_id
START WITH person_id = 'Enter Employee ID'

Oracle AME API to find Next Approver in AME hierarchy

Below is quick way to find next approver in Oracle AME hierarchy  ( Using oracle AME APIs)

declare
a   VARCHAR2(100);
b ame_util.approversTable2;
BEGIN
 AME_API2.getNextApprovers4(applicationIdIn   => 200,
                            transactionTypeIn => 'APEXP' ,  --Application Type
                            transactionIdIn   => '9999',   --Transaction ID
                            approvalProcessCompleteYNOut => a,
                            nextApproversOut   => b);
END;

Apr 1, 2019

How to enable FND Debug for Oracle OTL


To set FND logging you need to set the following FND system profiles at User Level (User reproducing the issue) 
  1. FND: Debug Log Enabled - Yes 
  2. FND: Debug Log Level - Statement 
  3. FND: Debug Log Module - per.plsql.% 

Logout then login to the Applications 

Get the user id of your user
select user_id
from fnd_user
where user_name = '????'

Delete the existing messages for this user
delete fnd_log_messages
where user_id = ????

Recreate/Reproduce  the issue
Run this query from any Sql Tool and export the results grid to an excel spreadsheet.
select *
from fnd_log_messages
where user_id = ????
order by log_sequence

Turn off the logging when done