AP Invoice On Hold Report

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


  • January 2, 2017 | 20 views
  • Comments