Oracle Applications R12, Re-assign PR/PO stuck in the workflow (awaiting approval)

 

Handling Purchase Orders stuck in the work flow.

Logon to the instance as “SYSADMIN”

1

Select “Workflow Administrator Web Applications” and Select the function “Status Monitor”. This opens a jsp page show below.

2

For the purchase orders stuck in the flow, please locate “PO Approval” workflow using search function. This windows provides multiple choices to limit the amount of data retrieved. Apply it whenever it is possible to reduce the time collecting and producing data.

Image: Finding the correct workflow (for Purchase requests stuck in the workflow)

3

For Purchase Requests stuck in the work flow, please use the following query

select requisition_header_id, segment1, wf_item_type, wf_item_key, authorization_status, org_id from po_requisition_headers_all where segment1 = ‘1980’ and org_id = 105;

for Purchase order change the table name to PO_HEADERS_ALL

to find the wf_item_key which is referred as “item key” with the JSP form.

4

Once the desired information derived

Click on “Activity History” button

5

Now you may use “Reassign” function (Shown within green rectangle) to reassign the Purchase Request for approval by next immediate subordinate.

You may use the “Rewind” button to rewind the workflow to an earlier stage as well

Hope you enjoyed another quality solution from us

for Windows7bugs

rajesh

Oracle Application “Position Hierarchy SQL Query”

 

If you are asked to make a report for listing the elements with a named Position Hierarchy, it could quite difficult because of the complexity with the way Oracle is maintaining the position hierarchies. please find below a practical solution to this requirement

Step 1

Create a view

Create view XXPOSHIERARCHY_V
AS
SELECT pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME hierarchy, has.position_id,LEVEL rep_level,
hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) has,
per_pos_structure_elements pse,
per_pos_structure_versions pve,
per_position_structures pps
WHERE pse.business_group_id = 81 --Replace with your own business group id
AND pve.position_structure_id = pps.position_structure_id
AND pse.POS_STRUCTURE_VERSION_ID = pve.POS_STRUCTURE_VERSION_ID
AND sysdate between pve.date_from and NVL(pve.date_to, sysdate)
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
start with pse.parent_position_id = 
(SELECT parent_position_id FROM per_pos_structure_elements a
WHERE A.POS_STRUCTURE_VERSION_ID = pse.pos_structure_version_id
AND a.POS_STRUCTURE_ELEMENT_ID = (SELECT MIN (POS_STRUCTURE_ELEMENT_ID)
FROM per_pos_structure_elements b WHERE b.POS_STRUCTURE_VERSION_ID = A.POS_STRUCTURE_VERSION_ID))
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id;

Now from the view above, you can easily populate the reporting structure using the following query (including the positions, employee names etc)

Select 0 rnum, opv.parent_name position_effective, 0 rep_level, paaf.person_id, papf.full_name,
opv.parent_position_id, opv.name hierarchy_name
from XXPOSHIERARCHY_V opv, per_all_assignments_f paaf,
per_all_people_f  papf
where NAME LIKE 'XYZ PR%'
and paaf.position_id = opv.parent_position_id
and papf.person_id = paaf.person_id
and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate)
and sysdate between papf.effective_start_date and nvl(papf.effective_end_date, sysdate)
and rep_level = 1
UNION ALL
Select ROWNUM rnum, opv.hierarchy, opv.rep_level, paaf.person_id, papf.full_name,
opv.parent_position_id, opv.name
from XXPOSHIERARCHY_V opv, per_all_assignments_f paaf,
per_all_people_f  papf
where NAME LIKE 'XYZ PR%'
and paaf.position_id = opv.position_id
and papf.person_id = paaf.person_id
and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate)
and sysdate between papf.effective_start_date and nvl(papf.effective_end_date, sysdate)
order by 1;

Adding rownum along with the query will provide you the flexibility to maintain the rpad -ed position names intact while retrieving a particular position hierarchy details.

In order to make the entire reporting dynamic we have created a PL/SQL sequence, populating all hierarchies into a local table. Please find the logic below

CREATE TABLE XXHIERELEMENTS
(
  RNUM                NUMBER,
  POSITION_EFFECTIVE  VARCHAR2(4000 BYTE),
  REP_LEVEL           NUMBER,
  PERSON_ID           NUMBER(10),
  FULL_NAME           VARCHAR2(240 BYTE),
  PARENT_POSITION_ID  NUMBER(15),
  HIERARCHY_NAME      VARCHAR2(30 BYTE)
)

and by executing the below PL/SQL sequence populate the table one time prior the report ran (please do not forget to truncate the table prior each report run!)

 

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c1
   IS
        SELECT DISTINCT name hierarchy_name
          FROM XXPOSHIERARCHY_V  ORDER BY 1;
BEGIN
   FOR i IN C1
   LOOP
      DBMS_OUTPUT.PUT_LINE (i.hierarchy_name);

      INSERT INTO XXHIERELEMENTS
         SELECT 0 rnum,
                opv.parent_name position_effective,
                0 rep_level,
                paaf.person_id,
                papf.full_name,
                opv.parent_position_id,
                opv.name hierarchy_name
           FROM XXPOSHIERARCHY_V opv,
                per_all_assignments_f paaf,
                per_all_people_f papf
          WHERE     NAME = i.hierarchy_name
                AND paaf.position_id = opv.parent_position_id
                AND papf.person_id = paaf.person_id
                AND SYSDATE BETWEEN paaf.effective_start_date
                                AND NVL (paaf.effective_end_date, SYSDATE)
                AND SYSDATE BETWEEN papf.effective_start_date
                                AND NVL (papf.effective_end_date, SYSDATE)
                AND rep_level = 1
         UNION ALL
         SELECT ROWNUM rnum,
                opv.hierarchy,
                opv.rep_level,
                paaf.person_id,
                papf.full_name,
                opv.parent_position_id,
                opv.name
           FROM XXPOSHIERARCHY_V opv,
                per_all_assignments_f paaf,
                per_all_people_f papf
          WHERE     NAME = i.hierarchy_name
                AND paaf.position_id = opv.position_id
                AND papf.person_id = paaf.person_id
                AND SYSDATE BETWEEN paaf.effective_start_date
                                AND NVL (paaf.effective_end_date, SYSDATE)
                AND SYSDATE BETWEEN papf.effective_start_date
                                AND NVL (papf.effective_end_date, SYSDATE);
   END LOOP;

   COMMIT;
END;

We hope this thread is useful for you

For Windows7bugs,

Admin