Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Advice about PO Action History Table

user16854Aug 30 2007 — edited Jan 25 2012

I've been scratching my head over this one all day, and am very stuck.

This is the SQL:

SELECT   papf2.full_name
       , pah3.sequence_num seq
       , pah3.action_code
       , pha.SEGMENT1
    FROM hr.per_all_people_f papf2
       , po.po_action_history pah3
       , po.po_headers_all pha
   WHERE pah3.employee_id = papf2.person_id
     AND pah3.object_id = pha.po_header_id
     AND TRUNC(SYSDATE) BETWEEN papf2.effective_start_date
                            AND papf2.effective_end_date
     AND pha.segment1 = 1413013
ORDER BY pah3.sequence_num DESC;

Which generates this output:

------------------------------------------------------------------
|FULL_NAME                |SEQ  |ACTION_CODE    |SEGMENT1          |
------------------------------------------------------------------
|Geoffrey, Mr Geoffrey        |6    |APPROVE        |1413013           |
|Geoffrey, Mr Geoffrey        |5    |RESERVE        |1413013           |
|Geoffrey, Mr Geoffrey        |4    |SUBMIT         |1413013           |
|Mark, Mr Mark  |3    |APPROVE        |1413013           |
|Mark, Mr Mark  |2    |RESERVE        |1413013           |
|Geoffrey, Mr Geoffrey        |1    |FORWARD        |1413013           |
|Geoffrey, Mr Geoffrey        |0    |SUBMIT         |1413013           |
------------------------------------------------------------------

What I would like to do is to be able to remove this line from the SQL:

AND pha.segment1 = 1413013

And amend the SQL to find out which POs have been approved by the same person who raised the PO

To paraphrase, the SQL would do something like:

SELECT ...
   FROM ...
WHERE
FULL_NAME IS THE SAME AS THE FIRST AND LAST VALUES FROM THE PO_ACTION_HISTORY table

But how I would do that?! I am way out of my depth.

Apologies for such a vague question.

Thank you

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 8 2007
Added on Aug 30 2007
24 comments
15,290 views