Invoice Aging Report Modifications ,To Get Invoices not paid & partially paid

TICKET 1    : Invoice Aging Report

                            To Get Invoices not paid & partially paid (should be Approved)

What is : Use the Aging Periods window to define time periods for the Invoice Aging Report. The Invoice Aging Report provides information about invoice payments due during four periods you specify. 

              Here we need to generate report on daily/weekly/monthly bases invoices which are approved.

Tables:         AP_PAYMENT_SCHEDULES_ALL

AP_INVOICES_ALL

                                     HZ_PARTIES

                                    AP_SUPPLIERS

                                    AP_SUPPLIERS_SITES_ALL

For Approved Invoices Query is:

AND AP_INVOICES_UTILITY_PKG.get_approval_status(

             i.invoice_id,              

             i.invoice_amount,          

             i.payment_status_flag,     

             i.invoice_type_lookup_code(CREDIT/DEBIT/PAYMENT REQUEST/RETAINAGE RELEASE/PREPAYMENT/STANDARD/MIXED/EXPENSE REPORT )='APPROVED'

AND     ((to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') - ps.due_date)

              between :C_MINDAYS and  :C_MAXDAYS)

AND     i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT

AND     i.cancelled_date IS NULL

AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0

&P_AMOUNT_PREDICATE

AND     i.payment_status_flag in ('N','P')

--code modified by teju on 4-sep-13 to get only not paid invoices

AND     i.payment_status_flag ='N'

&P_ORDER_BY

Parameters of Concurrent Program:

Here we modified : “DATA MODEL”

·                     Sort Invoice By:

·                     Trading Partner: All invoices will be sorted and grouped by Supplier’s Name

·                     Invoice Type: All the invoices will be sorted by the Invoice Type, like Standard, Prepayments, Credit Memos etc.

·                     Include Invoice Detail:

·                     Yes: The invoice information will be displayed in detail like Invoice Number, Due Date, Unpaid%, Amount Remaining.

·                     No: A summarized view of the

·                     Include Site Details:

·                     Yes: The invoices will be categorized supplier site wise.

·                     No: There will be no site information.

·                     Minimum Amount Due: There is no need to define this parameter. I think the name says it all :)

·                     Maximum Amount Due: There is no need to define this parameter. I think the name says it all :)

·                     Invoice Type: There is no need to define this parameter. I think the name says it all

·                     Trading Partner: Due to this parameter I also call this report as Supplier Aging Report.

·                     Aging Period Name: Give the name of the period of which you want to have the due calculate.