Query to display Compentencies related to Employee

Query to display Compentencies related to Employee in Performance Appraisals

SELECT pap.person_id, pap.full_name appraiser, pc.NAME compentency,
       NVL2 (prl1.NAME,
             prl1.step_value || '-' || prl1.NAME,
             prl1.step_value
            ) AS emp_prof_level,
       pce.comments, prl1.step_value
  FROM per_competence_elements pce,
       per_rating_levels_vl prl1,
       per_rating_levels_vl prl2,
       per_rating_levels_vl prl3,
       per_all_people_f pap,
       per_competences pc
 WHERE pce.proficiency_level_id = prl1.rating_level_id(+)
   AND pce.rating_level_id = prl2.rating_level_id(+)
   AND pce.weighting_level_id = prl3.rating_level_id(+)
   AND object_id = pap.person_id
   AND pc.competence_id = pce.competence_id
   AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date AND effective_end_date
   AND pce.object_name = 'ASSESSOR_ID'
   AND pce.object_id IN (
          SELECT perp.person_id
            FROM per_participants perp, per_assessments pass
           WHERE perp.participation_in_id = pass.appraisal_id
             AND perp.participation_in_table = 'PER_APPRAISALS'
             AND perp.participation_in_column = 'APPRAISAL_ID'
             AND pass.assessment_id = pce.assessment_id
             AND pass.appraisal_id =
                          74515
                               ---(Appraisal id receieved from previous query)
                               );

Updating Performance Management Plan End Date

The ability to reopen a PMP after it has been completed is available in R12.1.3, but not in the earlier releases.

Updating Performance Management Plan Appraisals After the Appraisal Task End Date (Doc ID 1207165.1)
Oracle Performance Management provides a new profile option
HR: Update PMP Appraisals
After the Appraisal Period (HR: PER_WPM_APPRAISAL_UPDATE_AFTER_PERIOD.). Using this new profile option, Enterprises can decide if they want their workers to complete PMP Appraisals after the Appraisal Task End Date and before the Plan Period End Date.
This profile option can be set at the Responsibility and User levels.
If the profile is set to Yes:
Then Managers and Workers can update PMP Appraisals after the Appraisal Task End Date.
If the profile option is set to No or is left blank:
Then Managers and Workers cannot update PMP Appraisals after the Appraisal Task End Date.
There is no default value for this profile option.
Note: This profile option applies to PMP Appraisals only.

This profile option is available from R12.1.3.

However, in lower versions there has been the request for the same thing. Since the functionality is not currently available within the lower version applications a datafix was required.

Need To Extend PMP Appraisal Dates Beyond The The Plan Dates (Doc ID 1314529.1)

The following SQL identified the invalid / incorrect data:
SELECT plan_id
FROM per_perf_mgmt_plans
WHERE plan_name like '&PLAN_NAME'; --replace &PLAN_NAME with your plan_name.

SELECT end_date
FROM per_perf_mgmt_plans
WHERE plan_id = &plan_id;

SELECT task_end_date
FROM per_appraisal_periods
WHERE plan_id = &plan_id;

=== Solution /Action Plan ===

To implement the solution, please execute the following steps on a TEST instance first:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:
i) To update the PLAN Period (IF you need both Plan and Appraisal periods need to be updated, update the plan period first):
UPDATE per_perf_mgmt_plans
SET end_date = '' --example '17-APR-2013'
WHERE plan_id = '';

iii) Use the below DML to update the task end date for Appraisals ONLY:
UPDATE per_appraisal_periods
SET task_end_date = '' --example '17-APR-2013'
WHERE plan_id = ''
AND task_end_Date = ''; -- Example: 10-APR-2013 which is the current task end date


3. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:

select * from per_perf_mgmt_plans
where plan_id =''

4. If you are satisfied with the results, issue a commit.

5. Confirm that the data is corrected when viewed in the Oracle Applications by querying the Plan Details :
NAV: HR Professional v4.0 > Performance Management > Performance Management Plan > Query up the plan

6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.

Is it Possible to Update Generate Employee Number Method From Automatic to Manual

Applies to:

Oracle Human Resources - Version: 11.5.10.0 to 12.1.1 - Release: 11.5 to 12.0

Information in this document applies to any platform.

Is it possible to move back to the manual employee numbering?

What is the impact for the persons already entered in the system?

Please note: IF Global Numbering has ever been enabled this note is no longer valid. Once Global Numbering has been enabled there is no way to back it out and change to Manual Employee numbering. Global Numbering uses a different set of tables for Number Generation and goes across multiple business groups.

Solution

The only implication would be to check/keep track of any duplication numbers.

Please note: IF Global Numbering has ever been enabled this note is no longer valid. Once Global Numbering has been enabled there is no way to back it out and change to Manual Employee numbering. Global Numbering uses a different set of tables for Number Generation and goes across multiple business groups.

Please find detailed below the steps to change the automatic to manual employee

number generation.

When creating an employee the system automatically inputs the employee number.

This value is always numeric. But some Business reasons could require that

the employee number to be alphanumeric or the employee number should be entered

manually.

1. In the standard Responsibility US HRMS Manager navigate to:

Work Structures -> Organization -> Description.

2. Query back the Business Group.

3. Click on the Organization Classification of business group.

4. Click on the [Others] button.

5. From the List of values (LOV) click on Business Group Information.

6. Click in the Flexfield box.

7. This will bring up a window called Business Group Information.

One of the fields is Employee Number Generation. This should currently say

Automatic.

8. Update this to say Manual.

9. Save the record.

To test the results.

1. In the standard responsibility US HRMS Manager navigate to:

People -> Enter and Maintain -> Create a new person.

2. Now the employee number is NOT generated automatically.

Fixed Assets Additions API Sample Script

Sample Script: Using the Additions API via Invoices

The following sample script shows how you can use the Additions API via invoices:

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;
   l_inv_rec                  FA_API_TYPES.inv_rec_type;

   l_return_status            VARCHAR2(1);          
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description               := '&description';

   -- cat info
   l_asset_cat_rec.category_id                := &category_id

   --type info
   l_asset_type_rec.asset_type                := '&ASSET_TYPE';

   -- invoice info
   l_inv_rec.fixed_assets_cost                := 2500;
   l_inv_rec.deleted_flag                     := 'NO';
   l_inv_rec.description            := l_asset_desc_rec.description;
   l_inv_rec.unrevalued_cost                  := 5555;
   l_inv_rec.create_batch_id                  := 1000;
   l_inv_rec.payables_code_combination_id     := 13528;
   l_inv_rec.feeder_system_name               := 'ACK';
   l_inv_rec.payables_cost                    := 5555;
   l_inv_rec.payables_units                   := 1;
   l_inv_rec.po_vendor_id                     := 1;
   l_inv_rec.inv_indicator                    := 1;

   l_inv_tbl (1)                              := l_inv_rec;

   -- fin info
   l_asset_fin_rec.date_placed_in_service     := '&DPIS';
   l_asset_fin_rec.depreciate_flag            := 'YES';


   -- book / trans info
   l_asset_hdr_rec.book_type_code             := '&book';

   -- distribution info 
   l_asset_dist_rec.units_assigned            := 1;
   l_asset_dist_rec.expense_ccid              := &ccid
   l_asset_dist_rec.location_ccid             := &location_id
   l_asset_dist_rec.assigned_to               := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                        := l_asset_dist_rec;

   -- call the api
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );
 
   dbms_output.put_line(l_return_status);

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                              (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;


   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sample Script: Using the Additions API with No Invoices

The following sample script shows how you can use the Additions API to test a manual change, when no invoice information is used:

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;

   l_return_status            VARCHAR2(1);     
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description                  := '&description';
   l_asset_desc_rec.asset_key_ccid               := null;

   -- cat info 
   l_asset_cat_rec.category_id                   := &category_id

   --type info
   l_asset_type_rec.asset_type                   := '&asset_type';

   -- fin info
   l_asset_fin_rec.cost                          := &cost
   l_asset_fin_rec.date_placed_in_service        := '&DPIS';
   l_asset_fin_rec.depreciate_flag               := 'YES';

   -- deprn info
   l_asset_deprn_rec.ytd_deprn                   := &ytd
   l_asset_deprn_rec.deprn_reserve               := &reserve
   l_asset_deprn_rec.bonus_ytd_deprn             := 0;
   l_asset_deprn_rec.bonus_deprn_reserve         := 0;

   -- book / trans info
   l_asset_hdr_rec.book_type_code                := '&book';

   -- distribution info
   l_asset_dist_rec.units_assigned               := 1;
   l_asset_dist_rec.expense_ccid                 := &ccid
   l_asset_dist_rec.location_ccid                := &location_id
   l_asset_dist_rec.assigned_to                  := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                           := l_asset_dist_rec;

   -- call the api 
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;

   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');

dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sample Script: Using the Additions API via Invoices with Alternative Ledger Currency

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;
   l_inv_rec                  FA_API_TYPES.inv_rec_type;

   l_return_status            VARCHAR2(1);          
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description               := '&description';

   -- cat info
   l_asset_cat_rec.category_id                := &category_id

   --type info
   l_asset_type_rec.asset_type                := '&ASSET_TYPE';

   -- invoice info
   l_inv_rec.fixed_assets_cost                := 2500;
   l_inv_rec.deleted_flag                     := 'NO';
   l_inv_rec.description             := l_asset_desc_rec.description;
   l_inv_rec.unrevalued_cost                  := 5555;
   l_inv_rec.create_batch_id                  := 1000;
   l_inv_rec.payables_code_combination_id     := 13528;
   l_inv_rec.feeder_system_name               := 'ACK';
   l_inv_rec.payables_cost                    := 5555;
   l_inv_rec.payables_units                   := 1;
   l_inv_rec.po_vendor_id                     := 1;
   l_inv_rec.inv_indicator                    := 1;

   l_inv_tbl (1)                              := l_inv_rec;

   -- rate info
   l_inv_rec.inv_rate_tbl(1).set_of_books_id  := 102;
   l_inv_rec.inv_rate_tbl(1).exchange_rate    := 2;
   l_inv_rec.inv_rate_tbl(1).cost             := 2001;

   -- fin info
   l_asset_fin_rec.date_placed_in_service     := '&DPIS';
   l_asset_fin_rec.depreciate_flag            := 'YES';


   -- book / trans info
   l_asset_hdr_rec.book_type_code             := '&book';

   -- distribution info 
   l_asset_dist_rec.units_assigned            := 1;
   l_asset_dist_rec.expense_ccid              := &ccid
   l_asset_dist_rec.location_ccid             := &location_id
   l_asset_dist_rec.assigned_to               := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                        := l_asset_dist_rec;

   -- call the api
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );
 
   dbms_output.put_line(l_return_status);

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                    (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;


   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

APP-AST-207069: This User ID Has Not Been Set Up As a Sales Force

Accessing the Collection Form Errors - APP-AST-207069: This User ID Has Not Been Set Up As a Sales Force

Symptoms
Attempting to access the Collection form and receive the following error:

ERROR:
APP-AST-207069: This user ID has not been set up as a sales force. Please contact the System Administrator

Steps to Reproduce:
Responsibility:  Receivables Manager
Navigation:  Collections > Collections

Cause
The issue is caused by not having defined a Collection Role for the Resource or the USER_ID has not been manually assigned to the employee resource tied to the USER_ID.

Solution
Please setup your Collections User correctly using the following steps from , Step #2:

A. To create an account:
Responsibility:  System Administrator
Navigation:  Security > User > Define
Enter a Username and Password

Assign the following responsibilities:
 •System Administrator
 •Application Developer
 •Receivables Manager
 •Collections Agent
 •Collections Forms Administrator
 •CRM Resource Manager
 •Collections HTML Admin
 •Telesales Agent
 •Human Resources Manager
 •CRM Administrator

B. In your HR Responsibility:
 •Setup employee
 •People>Enter & Maintain
 •Enter name, birthday, gender, SSN and Save
 •Note the employee number

C. In System Administrator Responsibility:
 •Security > User > Define
 •Query your Username
 •Enter the Full Name as it appeared in the HR setup screen above
 •Save

D. Using the CRM Resource Manager Responsibility
 •Menu: Maintain Resource
 •Create a Group  ◦-Name/Start Date (you can use anything)
   Assign Usages  Collections Application

Sales & Telesales Application
 Roles
Collections Agent
 Telesales Agent

•Import Resource ◦Resource Cat = Employee
 ◦Enter Employee # that was noted previously
 ◦Click search/select employee
 ◦Click create resource

•Default Value window ◦select start date of the resource

•Close the window
 •Select Resources click Save
 •Click Details
 •From the Resource Management Form, enter the Username
 •Click on Roles Tab (Security & Access) ◦Add the following roles:
 ◦Collections Role Type
 ◦Collections Agent Role
 ◦Telesales Role Type
 ◦Telesales Agent Role

•Click on the Group Tab
 •Query Group you created previously
 •Add as an employee to the Group
 •Close the form.

How to Query Position Hierarchy Chain of Command For A User

How to query position hierarchy chain of command for a user?

This is helpful when troubleshooting issues like 'Approval List Could Not Be Generated' when using position hierarchy.

Solution
 1.  Use the following query to determine the hierarchies to which a particular User belongs, and to identify those persons and users above the starting point User within each relevant hierarchy for this user.

QUERY1

SELECT
 peha.position_structure_id, peha.EMPLOYEE_ID,
fndu.user_id, ppos.POSITION_ID,
pps.name Hierarchy, fndu.USER_NAME UserName,
papf.FULL_NAME Person, ppos.NAME Position,
 peha.SUPERIOR_LEVEL SuperiorPositionLevel,
ppos2.NAME SuperiorPosition, papf2.FULL_NAME SuperiorPerson,
fndu2.USER_NAME SuperiorUsername, peha.SUPERIOR_ID SuperiorPersonId,
fndu2.user_id SuperiorUserId, ppos2.POSITION_ID SuperiorPosId
FROM
 PO_EMPLOYEE_HIERARCHIES_ALL peha, PER_POSITIONS ppos,
 PER_POSITIONS ppos2, per_all_people_f papf, per_all_people_f papf2,
 fnd_user fndu, fnd_user fndu2, per_position_structures pps
WHERE
 pps.business_group_id = peha.business_group_id AND
 pps.position_structure_id = peha.position_structure_id AND
 fndu2.EMPLOYEE_ID = papf2.PERSON_ID and papf2.PERSON_ID = peha.SUPERIOR_ID and
 papf2.EFFECTIVE_END_DATE > sysdate and papf.PERSON_ID = peha.employee_id and
 papf.EFFECTIVE_END_DATE > sysdate and ppos2.POSITION_ID = peha.SUPERIOR_POSITION_ID and
 ppos.position_id = peha.EMPLOYEE_POSITION_ID
and peha.superior_level > 0 and
 peha.employee_id = fndu.EMPLOYEE_ID
and fndu.USER_NAME = upper('&StartingUsername')
 ORDER BY peha.position_structure_id, peha.superior_level, papf2.full_name

The above query may return multiple names for a certain level.  This is because position hierarchy allows multiple persons to hold the same position.  When routing purchasing documents for approval the first person alphabetically (based on full_name) for that level position will be used for routing the document up the hierarchy.

2. Use the following query to find the Hierarchy value specified in the Purchasing document types form. 

This will show which position_structure_id to focus on in the above query.  Change the org_id value, document_type_code, and document_subtype as required, depending on the document and organization you are troubleshooting. 

This query will only return a result if the document type is currently setup for hierarchy routing.

QUERY2

select pdt.document_subtype, pdt.document_type_code,
pps.name Hierarchy, pdt.default_approval_path_id
 from po_document_types_all_b pdt, per_position_structures pps
 where
pps.position_structure_id = pdt.default_approval_path_id
 and pdt.org_id = 204
 and pdt.document_type_code = 'REQUISITION'
and pdt.document_subtype = 'PURCHASE'

How To Query Supervisor Chain Of Command For A User

How to query for supervisors hierarchy above an approver user when using employee supervisor chain of command?

Solution
Substitute the username in place of DCROCKETT in the following query.  This is useful when troubleshooting approval list issues with employee supervisor relationships for requisitions and purchasing documents

select fndu.user_name, pecx.full_name
From FND_USER fndu, per_employees_current_x pecx,
(
SELECT PERA.SUPERVISOR_ID --, fndu.user_name, pecx.full_name
FROM
PER_ASSIGNMENTS_F PERA
WHERE EXISTS
(SELECT '1' FROM PER_PEOPLE_F PERF,
PER_ASSIGNMENTS_F PERA1 WHERE TRUNC(SYSDATE) BETWEEN
PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE AND PERF.PERSON_ID =
PERA.SUPERVISOR_ID AND PERA1.PERSON_ID = PERF.PERSON_ID AND TRUNC(SYSDATE)
BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE AND
PERA1.PRIMARY_FLAG = 'Y' AND PERA1.ASSIGNMENT_TYPE = 'E' AND EXISTS (SELECT
'1' FROM PER_PERSON_TYPES PPT WHERE PPT.SYSTEM_PERSON_TYPE IN ('EMP',
'EMP_APL') AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID)) START WITH
PERA.PERSON_ID =
(select employee_id from fnd_user
where user_name = 'DCROCKETT') -- ** Replace DCROCKETT with your username
AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE
AND PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y' AND
PERA.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR PERA.SUPERVISOR_ID =
PERA.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND
PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y' AND
PERA.ASSIGNMENT_TYPE = 'E'
) c
where fndu.employee_id = c.supervisor_id and pecx.employee_id = c.supervisor_id

How To Query Approval Group and Approval Assignment Details For iProcurement And Purchasing Users

How can Approval Group and Approval Assignment details be queried from the database for iProcurement and Purchasing Users?

Solution
Use the Job Based query below when Employee Supervisor job based approvals are used, or Use the Position
Based query below when Position Hierarchy position based approvals are used.  The query results will show the approval list / approver list based on the job / position of the employees.

Note1:  Enter the operating unit org_id value, and specify either the User Names or the Employee IDs (comma separated). 
Note2:  The query is currently setup for Approve Purchase Requisitions; change the
pcf.CONTROL_FUNCTION_NAME value to run the query for other approval types;

JobBased
- Shows the Approval Group / Assignment rule details for specified approvers based on their JOB in HR


select fndu.user_name, papf.full_name, paaf.person_id, pcr.control_group_id, pcr.control_rule_id,
pcr.last_update_date, pcr.rule_type_code, pcr.object_code, pcr.amount_limit,
pcr.segment1_low low1, pcr.segment2_low low2, pcr.segment3_low low3, pcr.segment4_low low4,
pcr.segment5_low low5, pcr.segment6_low low6, pcr.segment7_low low7, pcr.segment8_low low8,
pcr.segment1_high high1, pcr.segment2_high high2, pcr.segment3_high high3, pcr.segment4_high high4,
pcr.segment5_high high5, pcr.segment6_high high6, pcr.segment7_high high7, pcr.segment8_high high8
from po_control_rules pcr, po_position_controls_all ppca, po_control_functions pcf,
per_all_assignments_f paaf, per_all_people_f papf, fnd_user fndu
where pcr.control_group_id = ppca.control_group_id
and ppca.org_id = &MyOrgId
and ppca.control_function_id = pcf.control_function_id
and pcf.CONTROL_FUNCTION_NAME = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
and ppca.job_id = paaf.job_id --- THIS MAKES THE QUERY FOR JOB BASED APPROVALS
and paaf.effective_end_date >= sysdate
and papf.effective_end_date >= sysdate
and paaf.person_id = papf.person_id
and concat(concat(paaf.person_id,'-'),NVL(paaf.object_version_number,0)) in
(select concat(concat(person_id, '-'), NVL(max(object_version_number),0))
from per_all_assignments_f
where person_id in (select employee_id from fnd_user where
user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
group by person_id)
and paaf.person_id = fndu.employee_id
and
(
fndu.user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
fndu.employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
order by
pcr.control_group_id, pcr.control_rule_id, fndu.user_name

PositionBased
- Shows the Approval Group / Assignment rule details for specified approvers based on their POSITION in HR
select fndu.user_name, papf.full_name, paaf.person_id, pcr.control_group_id, pcr.control_rule_id,
pcr.last_update_date, pcr.rule_type_code, pcr.object_code, pcr.amount_limit,
pcr.segment1_low low1, pcr.segment2_low low2, pcr.segment3_low low3, pcr.segment4_low low4,
pcr.segment5_low low5, pcr.segment6_low low6, pcr.segment7_low low7, pcr.segment8_low low8,
pcr.segment1_high high1, pcr.segment2_high high2, pcr.segment3_high high3, pcr.segment4_high high4,
pcr.segment5_high high5, pcr.segment6_high high6, pcr.segment7_high high7, pcr.segment8_high high8
from po_control_rules pcr, po_position_controls_all ppca, po_control_functions pcf,
per_all_assignments_f paaf, per_all_people_f papf, fnd_user fndu
where pcr.control_group_id = ppca.control_group_id
and ppca.org_id = &MyOrgId
and ppca.control_function_id = pcf.control_function_id
and pcf.CONTROL_FUNCTION_NAME = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
and ppca.position_id = paaf.position_id ---- THIS MAKES THE QUERY FOR POSITION BASED APPROVALS
and paaf.effective_end_date >= sysdate
and papf.effective_end_date >= sysdate
and paaf.person_id = papf.person_id
and concat(concat(paaf.person_id,'-'),NVL(paaf.object_version_number,0)) in
(select concat(concat(person_id, '-'), NVL(max(object_version_number),0))
from per_all_assignments_f
where person_id in (select employee_id from fnd_user where
user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
group by person_id)
and paaf.person_id = fndu.employee_id
and
(
fndu.user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
fndu.employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
order by
pcr.control_group_id, pcr.control_rule_id, fndu.user_name