Introduction
Invoice on Hold Report is used to review detailed information about invoices on hold. You can submit the Approval process before submitting this report to obtain the most up-to-date hold information.
The Invoice on Hold Report is divided into three sections. The first section gives you supplier, invoice, purchase order, amount, and hold information for each invoice on hold.
The second section, Invoice Hold Code Descriptions, lists all predefined and user-defined hold codes that are in the first section of the report, descriptions of each code, and whether they hold allows post.
Script to review detailed information about invoices on hold .
SELECT DISTINCT b.batch_name batch_name, v.vendor_name supplier_name,
vs.vendor_site_code site, vs.hold_all_payments_flag site_flag,
h.hold_lookup_code, alc.displayed_field,
NVL ((SELECT DISTINCT poh.segment1
FROM po_headers_all poh,
po_line_locations_all po_ll
WHERE po_ll.line_location_id =
h.line_location_id
AND po_ll.po_header_id = poh.po_header_id),
”
) po_number,
inv.invoice_num invoice_number, inv.invoice_date,
inv.invoice_amount original_amount,
inv.invoice_amount
– NVL (inv.amount_paid, 0)
+ NVL (inv.discount_amount_taken, 0) amount_remaining,
inv.description description, NVL (vs.attribute1, ”) ap_owner
FROM ap_invoices_all inv,
ap_batches_all b,
po_vendors v,
po_vendor_sites_all vs,
ap_payment_schedules_all s,
ap_holds_all h,
ap_lookup_codes alc
WHERE v.vendor_id = inv.vendor_id
AND v.vendor_id = NVL (:p_vendor_id, v.vendor_id)
AND vs.vendor_id = inv.vendor_id
AND vs.vendor_site_id = inv.vendor_site_id
AND vs.attribute1 = NVL (:p_owner, vs.attribute1)
AND vs.hold_all_payments_flag = ‘N’
AND h.invoice_id = inv.invoice_id
AND h.hold_lookup_code = NVL (:p_hold_code, h.hold_lookup_code)
AND b.batch_id(+) = inv.batch_id
AND s.invoice_id(+) = inv.invoice_id
AND inv.payment_status_flag != ‘Y’
AND inv.cancelled_date IS NULL
AND h.release_lookup_code IS NULL
AND alc.lookup_type = ‘HOLD CODE’
AND alc.lookup_code = h.hold_lookup_code
AND TRUNC (inv.creation_date) >=
DECODE (:p_start_creation_date,
NULL, TRUNC (inv.creation_date),
:p_start_creation_date
)
AND TRUNC (inv.creation_date) <=
DECODE (:p_end_creation_date,
NULL, TRUNC (inv.creation_date),
:p_end_creation_date
)
AND ( ( NVL (s.due_date, SYSDATE) >=
DECODE (:p_start_due_date,
NULL, NVL (s.due_date, SYSDATE),
:p_start_due_date
)
AND NVL (s.due_date, SYSDATE) <=
DECODE (:p_end_due_date,
NULL, NVL (s.due_date, SYSDATE),
:p_end_due_date
)
)
AND ( ( NVL (s.discount_date, SYSDATE) >=
DECODE (:p_start_discount_date,
NULL, NVL (s.discount_date, SYSDATE),
:p_start_discount_date
)
AND NVL (s.discount_date, SYSDATE) <=
DECODE (:p_end_discount_date,
NULL, NVL (s.discount_date, SYSDATE),
:p_end_discount_date
)
)
OR ( NVL (s.second_discount_date, SYSDATE) >=
DECODE (:p_start_discount_date,
NULL, NVL (s.second_discount_date,
SYSDATE
),
:p_start_discount_date
)
AND NVL (s.second_discount_date, SYSDATE) <=
DECODE (:p_end_discount_date,
NULL, NVL (s.second_discount_date,
SYSDATE
),
:p_end_discount_date
)
)
OR ( NVL (s.third_discount_date, SYSDATE) >=
DECODE (:p_start_discount_date,
NULL, NVL (s.third_discount_date,
SYSDATE
),
:p_start_discount_date
)
AND NVL (s.third_discount_date, SYSDATE) <=
DECODE (:p_end_discount_date,
NULL, NVL (s.third_discount_date,
SYSDATE
),
:p_end_discount_date
)
)
)
)
AND :p_order_by = ‘Hold Name’
ORDER BY alc.displayed_field,
UPPER (v.vendor_name),
inv.invoice_date ASC,
UPPER (b.batch_name),
inv.invoice_num,
inv.invoice_amount;
What we expect in the script.
This script helps us to comprehend how to review detailed information about invoices on hold. Couple of tables which is being used in the scripts are ap_invoices_all inv, ap_batches_all b,po_vendors v,po_vendor_sites_all ,payment_schedules_all s,ap_holds_all h,ap_lookup_codes alc etc.
Summary
This Post described the script to review detailed information about invoices on hold in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
