Oracle EBS R12: Restrict Approval Hierarchies on Purchase Requisition & Purchase Order as per document preparer

This post will show you the procedure to restrict the Approval Hierarchies available on the Purchase Requisition and Purchase Order forms.

As per standard functionality, all the position hierarchies setup in the HRMS module will show up on the PR & PO forms, regardless of weather the PR/PO preparer exist in the hierarchy or not.

You may configure the following personalization to ensure only those hierarchies are available in the Approval Path LOV, in which the PR/PO preparer exists.

Purchase Requisition Form

1.jpg

Condition:

Trigger Event: WHEN-NEW-ITEM-INSTANCE

Trigger Object: PO_APPROVE.FORWARD_CHECK

2.jpg

Actions:

Seq: 10

Type: Builtin

Builtin Type: Create a Record Group from Query

Argument:

select pps.name, pps.position_structure_id
  from per_position_structures pps, 
       per_pos_structure_versions psv 
 where upper(pps.name) like upper(:po_approve.approval_path) || '%' 
   AND pps.business_group_id  = (select max(nvl(fsp.business_group_id,0))
                                   from financials_system_parameters fsp) 
   and psv.business_group_id = (select max(nvl(fsp.business_group_id,0))
                                   from financials_system_parameters fsp)    
   and pps.position_structure_id = psv.position_structure_id
   and trunc(sysdate) between nvl(psv.date_from,trunc(sysdate)) and nvl(psv.date_to,trunc(sysdate))
   and pps.POSITION_STRUCTURE_ID in (
                                    SELECT hie.POSITION_STRUCTURE_ID
                                      FROM per_position_structures hie,
                                           per_assignments_f pef,
                                           po_requisition_headers_all prha,
                                           per_pos_structure_elements hiel,
                                           per_pos_structure_versions pve
                                     WHERE 1 = 1
                                       AND pef.person_id = prha.preparer_id
                                       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 prha.requisition_header_id = :PO_REQ_HDR.REQUISITION_HEADER_ID
                                       and pef.EFFECTIVE_START_DATE <= sysdate
                                       and pef.EFFECTIVE_END_DATE >= sysdate
                                     )
 order by pps.name

Group Name: XX_APPROVAL_PATH

3.jpg

Seq: 20

Type: Property

Object Type: LOV

Target Object: APPROVAL_PATH

Property Name: GROUP_NAME

Value: XX_APPROVAL_PATH

Save your work and test the results. You will now only be able to view those hierarchies in which the employee assigned logged in user exists.

The EXACT same personalization can be replicated on the Purchase Order form to achieve the same result.

The only difference will be the argument passed in the Actions of the personalization to create the Record Group. The argument for Purchase Order personalziation will be as follows:

select pps.name, pps.position_structure_id
  from per_position_structures pps, 
       per_pos_structure_versions psv 
 where upper(pps.name) like upper(:po_approve.approval_path) || '%' 
   AND pps.business_group_id  = (select max(nvl(fsp.business_group_id,0))
                                   from financials_system_parameters fsp) 
   and psv.business_group_id = (select max(nvl(fsp.business_group_id,0))
                                   from financials_system_parameters fsp)    
   and pps.position_structure_id = psv.position_structure_id
   and trunc(sysdate) between nvl(psv.date_from,trunc(sysdate)) and nvl(psv.date_to,trunc(sysdate))
   and pps.POSITION_STRUCTURE_ID in (
                                    SELECT hie.POSITION_STRUCTURE_ID
                                      FROM per_position_structures hie,
                                           per_assignments_f pef,
                                           po_headers_all pha ,
                                           per_pos_structure_elements hiel,
                                           per_pos_structure_versions pve    
                                     WHERE 1 = 1
                                       AND pef.person_id = pha.AGENT_ID
                                       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 pha.PO_HEADER_ID = :PO_HEADERS.PO_HEADER_ID
                                     )
 order by pps.name

 

 

 

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