SELECT DISTINCT aiha.invoice_id, response, aiha.creation_date,
ai.invoice_date,
(SELECT vendor_name
FROM apps.po_vendors
WHERE vendor_id = ai.vendor_id) vendorname,
(SELECT ai.invoice_num
FROM ap_invoices_all ai
WHERE ai.invoice_id = aiha.invoice_id) invoice_number,
(SELECT ai.invoice_amount
FROM ap_invoices_all ai
WHERE ai.invoice_id = aiha.invoice_id)
amount_approved,
(SELECT ppf3.full_name
FROM ap_invoices_all ai,
per_all_people_f ppf3
WHERE ai.invoice_id = aiha.invoice_id
AND ai.requester_id = ppf3.person_id
AND TRUNC (SYSDATE) BETWEEN ppf3.effective_start_date
AND ppf3.effective_end_date
AND ROWNUM = 1) requester_name,
(SELECT ppf3.email_address
FROM ap_invoices_all ai,
per_all_people_f ppf3
WHERE ai.invoice_id = aiha.invoice_id
AND NVL (ai.requester_id, aiha.approver_id) =
ppf3.person_id
AND TRUNC (SYSDATE) BETWEEN ppf3.effective_start_date
AND ppf3.effective_end_date
AND ROWNUM = 1) requester_email,
(SELECT NVL (requester_id,
aiha.approver_id
)
FROM ap_invoices_all ai
WHERE ai.invoice_id = aiha.invoice_id AND ROWNUM = 1)
requester_id,
ai.wfapproval_status
FROM ap_inv_aprvl_hist_all aiha, ap_invoices_all ai
WHERE aiha.approver_id = p_approver_id
AND aiha.creation_date >= ’13-DEC-2025′
AND aiha.response NOT IN ( ‘APPROVED’)
AND ai.cancelled_by IS NULL
AND TRUNC (SYSDATE) – TRUNC (aiha.creation_date) >= 5
AND ai.invoice_id = aiha.invoice_id
AND aiha.creation_date =
(SELECT MAX (h.creation_date)
FROM ap_inv_aprvl_hist_all h
WHERE h.invoice_id = aiha.invoice_id)
–AND ai.requester_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM ap_inv_aprvl_hist_all
WHERE invoice_id = aiha.invoice_id
— AND approver_id = aiha.approver_id
AND response IN (‘APPROVED’, ‘MANUALLY APPROVED’));
Recent Posts