Oracle Apps Knowledge Sharing

Query to fetch Requestor Initiated Purchase Order Changes in R12 (PO_CHANGE_REQUESTS)

            In this post, we have given a query to fetch Requestor Initiated Purchase Order Change requests.

Test Instance: R12.2.4

Query:

SELECT hou.short_code,
       pcr_r.change_request_id,
       pcr_r.initiator,
       pcr_r.action_type,
       pcr_r.document_type,
       pcr_r.document_header_id po_header_id,
       pcr_r.document_num po_number,
       pcr_r.document_revision_num po_revision_num,
       pcr_r.document_line_id po_line_id,
       pcr_r.document_line_number po_line_number,
       pcr_r.old_quantity,
       pcr_r.new_quantity,
       pcr_r.old_promised_date,
       pcr_r.new_promised_date,
       pcr_r.old_supplier_part_number,
       pcr_r.new_supplier_part_number,
       pcr_r.old_price,
       pcr_r.new_price,
       pcr_r.old_currency_unit_price,
       pcr_r.new_currency_unit_price,
       pcr_r.old_need_by_date,
       pcr_r.new_need_by_date,
       pcr_r.requester_id,
       fu.user_name requester,
       furesp.user_name responser,
       pcr_r.response_date,
       pcr_r.response_reason,
       pcr_r.creation_date,
       pcr_r.request_status,
       pcr_r.request_reason,
       pcr_r.request_level,
       pcr_r.change_active_flag
  FROM po_change_requests pcr_r,
       po_headers_all pha,
       hr_operating_units hou,
       fnd_user fu,
       fnd_user furesp
 WHERE pcr_r.document_header_id = pha.po_header_id
   AND pha.org_id = hou.organization_id
   AND pcr_r.document_type = 'PO'
   AND pcr_r.requester_id = fu.user_id
   AND pcr_r.responded_by = furesp.user_id
   AND pcr_r.initiator = 'REQUESTER'