The below is the query to get all the invoices on hold for a particular operating unit.
SELECT
po.comments po_notes,
pf1.full_name buyer,
ah.hold_date,
ah.hold_lookup_code,
(
SELECT
user_name
FROM
apps.fnd_user
WHERE
user_id = rh.created_by
) requisition_created_by,
(
SELECT
full_name
FROM
apps.per_all_people_f
WHERE
person_id = rh.preparer_id
AND ROWNUM = 1
) preparer,
rh.segment1 requisition_num,
po.segment1 po_number,
pl.line_num,
asa.vendor_name,
ail.item_description,
aia.voucher_num,
aia.invoice_num,
aia.invoice_date,
aia.invoice_amount,
pll.quantity quantity_ordered,
pll.quantity_billed,
pll.quantity_received,
pl.unit_price
FROM
apps.ap_holds_all ah,
apps.ap_invoices_all aia,
apps.ap_suppliers asa,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aida,
apps.po_distributions_all pda,
apps.po_line_locations_all pll,
apps.po_lines_all pl,
apps.po_headers_all po,
apps.po_requisition_headers_all rh,
apps.po_requisition_lines_all rl,
apps.po_req_distributions_all rd,
apps.per_all_people_f pf1
WHERE
ah.org_id =
AND ah.release_reason IS NULL
AND aia.invoice_id = ah.invoice_id
AND asa.vendor_id = aia.vendor_id
AND ail.invoice_id = aia.invoice_id
AND ail.line_number = aida.invoice_line_number
AND aida.invoice_id = ail.invoice_id
AND pda.po_distribution_id (+) = aida.po_distribution_id
AND pll.po_header_id (+) = pda.po_header_id
AND pll.po_line_id (+) = pda.po_line_id
AND pl.po_header_id (+) = pda.po_header_id
AND pl.po_line_id (+) = pda.po_line_id
AND po.po_header_id (+) = pl.po_header_id
AND rd.distribution_id (+) = pda.req_distribution_id
AND rl.requisition_header_id = rh.requisition_header_id (+)
AND rd.requisition_line_id = rl.requisition_line_id (+)
AND pf1.person_id (+) = po.agent_id
