Oracle EBS R12: Extract complete Purchasing Setup of an Employee(s) (Position, Approval Groups, Approval Assignments)

The query provided in this post will extract the complete setup of an HR Employee in Oracle Purchasing Module. This Setup includes:

  1. Employee Position
  2. Employee Organization
  3. Approval Group Details
  4. Approval Assignment Details
  5. Document Type
  6. Position Hierarchy Name

This may be used to extract, migrate or report your Purchasing Setups.

SELECT hr.full_name, 
       hr.employee_num,
       hou.name organization,
       hapf.name position_name,
       hapf.position_id,
       pcf.control_function_name document_type,
       hou1.name assignment_org,
       pcg.CONTROL_GROUP_NAME,
       pcr.OBJECT_CODE OBJECT,
       pcr.RULE_TYPE_CODE TYPE,
       pcr.AMOUNT_LIMIT,
       pcr.SEGMENT1_LOW||decode(pcr.SEGMENT2_LOW,NULL,NULL,'.')||
       pcr.SEGMENT2_LOW||decode(pcr.SEGMENT3_LOW,NULL,NULL,'.')||
       pcr.SEGMENT3_LOW||decode(pcr.SEGMENT4_LOW,NULL,NULL,'.')||
       pcr.SEGMENT4_LOW||decode(pcr.SEGMENT5_LOW,NULL,NULL,'.')||
       pcr.SEGMENT5_LOW||decode(pcr.SEGMENT6_LOW,NULL,NULL,'.')||
       pcr.SEGMENT6_LOW||decode(pcr.SEGMENT7_LOW,NULL,NULL,'.')||
       pcr.SEGMENT7_LOW||decode(pcr.SEGMENT8_LOW,NULL,NULL,'.')||
       pcr.SEGMENT8_LOW||decode(pcr.SEGMENT9_LOW,NULL,NULL,'.')||
       pcr.SEGMENT9_LOW||decode(pcr.SEGMENT10_LOW,NULL,NULL,'.')||
       pcr.SEGMENT10_LOW||decode(pcr.SEGMENT11_LOW,NULL,NULL,'.')  FLEX_VALUE_LOW,
       pcr.SEGMENT1_HIGH||decode(pcr.SEGMENT2_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT2_HIGH||decode(pcr.SEGMENT3_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT3_HIGH||decode(pcr.SEGMENT4_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT4_HIGH||decode(pcr.SEGMENT5_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT5_HIGH||decode(pcr.SEGMENT6_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT6_HIGH||decode(pcr.SEGMENT7_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT7_HIGH||decode(pcr.SEGMENT8_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT8_HIGH||decode(pcr.SEGMENT9_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT9_HIGH||decode(pcr.SEGMENT10_HIGH,NULL,NULL,'.')||
       pcr.SEGMENT10_HIGH||decode(pcr.SEGMENT11_HIGH,NULL,NULL,'.')  FLEX_VALUE_HIGH,
       hie.hie
  FROM hr_employees hr, 
       per_all_assignments_f paaf,
       hr_organization_units hou,
       hr_all_positions_f hapf,
       po_position_controls_all ppca,
       hr_organization_units hou1,
       po_control_functions pcf,
       po_control_groups_all pcg,
       po_control_rules pcr,
       (
    SELECT 'This Person exists in the following Hierarchies: '||
           LISTAGG(hie.NAME||', '||CHR(13) || CHR(10)) WITHIN GROUP(ORDER BY hie.NAME) HIE, pef.PERSON_ID
      FROM per_position_structures hie,
           per_assignments_f pef,
           per_pos_structure_elements hiel,
           per_pos_structure_versions pve
     WHERE 1 = 1
       AND pef.position_id = hiel.subordinate_position_id
       AND hiel.pos_structure_version_id = pve.POS_STRUCTURE_VERSION_ID
       AND pve.POSITION_STRUCTURE_ID = hie.position_structure_id
       and pef.EFFECTIVE_START_DATE <= sysdate
       and pef.EFFECTIVE_END_DATE >= sysdate
  GROUP BY pef.PERSON_ID
        ) hie
 WHERE 1 = 1
   AND hr.employee_id = paaf.person_id
   AND SYSDATE BETWEEN paaf.effective_start_date AND NVL(paaf.effective_end_date,'31-DEC-2099')
   AND hou.ORGANIZATION_ID = paaf.ORGANIZATION_ID
   AND hapf.POSITION_ID = paaf.POSITION_ID
   AND SYSDATE BETWEEN hapf.effective_start_date AND NVL(hapf.effective_end_date,'31-DEC-2099')
   AND hou1.ORGANIZATION_ID = ppca.ORG_ID 
   AND ppca.POSITION_ID = paaf.POSITION_ID
   AND ppca.CONTROL_FUNCTION_ID = pcf.CONTROL_FUNCTION_ID
   AND ppca.CONTROL_GROUP_ID = pcg.CONTROL_GROUP_ID
   AND pcg.CONTROL_GROUP_ID = pcr.CONTROL_GROUP_ID
   AND hie.person_id = paaf.PERSON_ID
   AND hr.employee_num = :P_EMPLOYEE_NO                     ---Employee NUM Parameter
--   AND hou1.NAME = :P_ORG_NAME                              ---Purchasing Operating Unit Name Parameter
--   AND pcg.CONTROL_GROUP_NAME = :P_GROUP_NAME               ---Approver Group Parameter

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s