AP Invoice Aging Report Test

SELECT distinct
    vendor_name,
    vendor_number,
    vendor_site,
    invoice_number,
    invoice_amount,
    po_number,
     po_created_by,
     po_creation_date,
     project_number,
      project_name,
       task_number                     ,
       task_name,
      invoice_date,
       invoice_created_by,
       invoice_type,
	pay_group,
	invoice_payment_status,
	sched_payment_status,
       due_date,
     days_past_due,
    payment_terms,
    payment_method,
    amount_remaining,
    CASE
        WHEN days_past_due <= 0 THEN
            amount_remaining
        ELSE
            NULL
    END current_bucket,
    CASE
        WHEN days_past_due > 0
             AND days_past_due <= 30 THEN
            amount_remaining
        ELSE
            NULL
    END bucket_1_30,
    CASE
        WHEN days_past_due > 30
             AND days_past_due <= 60 THEN
            amount_remaining
        ELSE
            NULL
    END bucket_31_60,
    CASE
        WHEN days_past_due > 60
             AND days_past_due <= 90 THEN
            amount_remaining
        ELSE
            NULL
    END bucket_61_90,
    CASE
        WHEN days_past_due > 90 THEN
            amount_remaining
        ELSE
            NULL
    END greater_than_90
FROM
    (
        SELECT
            aps.vendor_name                vendor_name,
            aps.segment1                   vendor_number,
             assa.vendor_site_code   vendor_site,
            aia.invoice_num                 invoice_number,
            aia.payment_status_flag,
	   xxen_util.user_name(aia.created_by) invoice_created_by,
	   xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,
            xxen_util.client_time(aia.invoice_date)                invoice_date,
             xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_payment_status,
	   xxen_util.meaning(apsa.payment_status_flag,'INVOICE PAYMENT STATUS',200) sched_payment_status,
            xxen_util.meaning(aia.pay_group_lookup_code,'PAY GROUP',200) pay_group,
            xxen_util.client_time(apsa.due_date)                   due_date,
            TRUNC(sysdate) - TRUNC(apsa.due_date)  AS days_past_due,
            apsa.amount_remaining / nvl(aia.payment_cross_rate,1)* nvl(aia.exchange_rate,1) amount_remaining,
            term.name                   payment_terms,
            aia.invoice_amount,
            pha.segment1                        po_number,
	   xxen_util.user_name(pha.created_by) po_created_by,
            TRUNC(pha.creation_date) po_creation_date,
            ppa.segment1                        project_number,
            ppa.NAME                                project_name,
            pt.task_number                     ,
            pt.task_name,
            nvl(xxen_util.meaning(apsa.payment_method_lookup_code,'PAYMENT METHOD',200),apsa.payment_method_lookup_code) payment_method
        FROM
            ap_payment_schedules_all apsa,
            ap_invoices_all          aia,
             ap_invoice_distributions_all aida,
            po_vendors aps,
            po_vendor_sites_all assa,
            pa_projects_all ppa,
             pa_tasks pt,
              po_distributions_all pda,
              po_headers_all pha,
            ap_lookup_codes          alc1,
            ap_terms_val_v           term
        WHERE 1 = 1
            AND  aia.invoice_id = apsa.invoice_id
            AND aia.vendor_id = aps.vendor_id
            AND aia.vendor_site_id=assa.vendor_site_id
             AND aia.invoice_id = aida.invoice_id(+)
            AND aida.po_distribution_id = pda.po_distribution_id (+) 
             AND pha.po_header_id = pda.po_header_id --- [Mrugesh : Is this right?]
             AND aida.project_id=ppa.project_id(+) 
             AND aida.task_id=pt.task_id(+)
            AND aia.cancelled_date IS NULL
            AND ( nvl(apsa.amount_remaining, 0) * nvl(aia.exchange_rate, 1) ) != 0
            AND aia.payment_status_flag IN ( 'N', 'P' )
            AND alc1.lookup_type (+) = 'INVOICE TYPE'
            AND alc1.lookup_code (+) = aia.invoice_type_lookup_code
            AND aia.terms_id = term.term_id (+)
            AND aia.invoice_date <= sysdate
            AND ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, apsa.payment_status_flag, invoice_type_lookup_code)
            IN ( 'APPROVED', 'NEEDS REAPPROVAL' )
 
    )