Query : Invoices on hold

select distinct
ai.invoice_num,ai.invoice_amount,ai.invoice_date
,ai.invoice_received_date,ah.hold_lookup_code,ah.hold_reason,pv.vendor_name,pv.segment1 vendor_number
,ph.segment1 po_number
,gcc.segment1||’.’||gcc.segment2||’.’||gcc.segment3||’.’||gcc.segment4||’.’||gcc.segment5||’.’||gcc.segment6
–,aid.AMOUNT
from ap_invoices_all ai
,ap_holds_all ah
,po_vendors pv
,po_headers_all ph
,ap_invoice_distributions_all aid
,po_distributions_all pda
,gl_code_combinations gcc
where 1=1
and ai.invoice_id = ah.invoice_id
and ai.vendor_id = pv.vendor_id
–and ai.po_header_id = ph.po_header_id(+)
and ph.PO_HEADER_ID = pda.PO_HEADER_ID
and ah.invoice_id = aid.invoice_id
and aid.po_distribution_id = pda.po_distribution_id
and aid.dist_code_combination_id = gcc.code_combination_id

Get Invoice Number from PO Number

SELECT ap_inv.invoice_num,
po_hdr.segment1,
po_hdr.org_id
FROM ap_invoices_all ap_inv,
ap_invoice_distributions_all ap_dis,
po_distributions_all po_dis,
po_headers_all po_hdr
WHERE ap_inv.invoice_id = ap_dis.invoice_id
AND ap_dis.po_distribution_id = po_dis.po_distribution_id
AND po_dis.po_header_id = po_hdr.po_header_id
AND ap_inv.org_id = po_hdr.org_id
AND po_hdr.segment1 like ‘Give PO Number’
–AND ap_inv.invoice_num like ‘Give Invoice Number’

SQL QUERIES TO GET ORGANIZATION FROM REQUISITION, PURCHASE ORDER, PO RELEASE

From Requisition:

    select hr.name, prh.segment1, prh.org_id

    from po_requisition_headers_all prh,

    hr_all_organization_units hr

    where prh.org_id = hr.organization_id and

    prh.segment1 = '&Enter_Req_Number';

From Purchase Order:

    select hr.name, poh.segment1, poh.org_id

    from po_headers_all poh,

    hr_all_organization_units hr

    where poh.org_id = hr.organization_id and

    poh.segment1 = '&Enter_PO_Number';

From Purchase Order Release:
    select hr.name, poh.segment1, por.release_num, por.org_id

    from po_headers_all poh,

    po_releases_all por,

    hr_all_organization_units hr

    where poh.org_id = hr.organization_id and

    por.org_id = poh.org_id and

    poh.po_header_id = por.po_header_id and

    poh.segment1 = '&Enter_PO_Number' and

    por.release_num = '&Enter_Release_Num';