Query to get Purchase Requisition Highest Approver

Query to get Purchase Requisition Highest Approver

Query to get Purchase Requisition Highest Approver

select pha.segment1

, pla.po_line_id

, pda.po_distribution_id

, pda.req_distribution_id

, prha.requisition_header_id

, prha.requisition_number

, prha.preparer_id req_preparer_id

, pah.performer_id req_approver_id

, person_perform.full_name performer

, person_approve.full_name approver

from po_headers_all pha

, po_lines_all pla

, po_distributions_all pda

, por_req_distributions_all prda

, por_requisition_lines_all prla

, por_requisition_headers_all prha

, po_action_history pah

, (select full_name, person_id

from per_person_names_f

where name_type = 'GLOBAL'

and trunc(sysdate) between effective_start_date and effective_end_date)person_perform

, (select full_name, person_id

from per_person_names_f

where name_type = 'GLOBAL'

and trunc(sysdate) between effective_start_date and effective_end_date)person_approve

where 1=1

and pha.segment1 in()

and pha.po_header_id = pla.po_header_id

and pha.po_header_id = pha.po_header_id

and pla.po_line_id = pda.po_line_id

and pda.req_distribution_id = prda.distribution_id

and prda.requisition_line_id = prla.requisition_line_id

and prla.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = pah.object_id

and (pah.object_type_code = 'REQ'

and pah.action_code = 'APPROVE'

and pah.performer_id not in (-1)

and pah.sequence_num = (select max(pah.sequence_num)

from po_action_history pah

where 1=1

and pah.object_id = prha.requisition_header_id

and pah.object_type_code = 'REQ'

and pah.action_code = 'APPROVE'

and pah.performer_id not in (-1)

group by pah.object_id))

and prha.preparer_id = person_perform.person_id (+)

and pah.performer_id = person_approve.person_id (+)