AP Invoice On Hold
Report
Description
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 the hold allows posting.
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;
By
Deepak J
No comments:
Post a Comment