Query to get Supplier wise Invoice Details in Oracle APPS

   SELECT hou.name operating_unit,

         asp.vendor_name,

         asp.segment1 vendor_number,

         apsa.vendor_site_code,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id

                 AND aia.invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            toatl_booking_amount,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id

                 AND aia.invoice_type_lookup_code = 'PREPAYMENT'

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'AVAILABLE')

            prepayment_amount,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.org_id = apsa.org_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code = 'PREPAYMENT'

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'FULL')

            applied_prepayment_amount,

         (SELECT NVL (SUM (aipa.amount), 0)

            FROM ap_invoices_all aia, ap_invoice_payments_all aipa

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aipa.invoice_id = aia.invoice_id

                 AND aia.org_id = apsa.org_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code NOT IN

                        ('CREDIT', 'DEBIT', 'PREPAYMENT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            payment_amount,

         (SELECT NVL (SUM (aps.gross_amount), 0)

            FROM ap_invoices_all aia, ap_payment_schedules_all aps

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aps.invoice_id = aia.invoice_id

                 AND aia.org_id = apsa.org_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT')

                 AND aps.hold_flag = 'Y')

            hold_amount,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id 

--AND aia.invoice_type_lookup_code NOT IN ('CREDIT','DEBIT','PREPAYMENT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) IN

                        ('NEVER APPROVED', 'NEEDS REAPPROVAL', 'CANCELLED'))

            booking_value,

         (SELECT COUNT (*)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.ORG_ID = apsa.ORG_ID

                 -- AND aia.invoice_type_lookup_code NOT IN ('CREDIT','DEBIT','PREPAYMENT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) IN

                        ('NEVER APPROVED', 'NEEDS REAPPROVAL', 'CANCELLED'))

            booking_count,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code IN ('CREDIT', 'DEBIT')

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.ORG_ID = apsa.ORG_ID

                 AND aia.invoice_id NOT IN

                        (SELECT invoice_id FROM ap_invoice_payments_all)

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            deduction_amount,

         (SELECT NVL (SUM (aipa.amount), 0)

            FROM ap_invoices_all aia, ap_invoice_payments_all aipa

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aipa.invoice_id = aia.invoice_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code IN ('CREDIT', 'DEBIT')

                 AND aia.invoice_num NOT LIKE '%TDS%'

                 AND aia.invoice_num NOT LIKE '%WCT%'

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.ORG_ID = apsa.ORG_ID

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            cm_payment_amount,

         (SELECT DISTINCT aia.invoice_currency_code

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code)

            currency_code,

         (SELECT aia.exchange_rate

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aia.ORG_ID = apsa.ORG_ID

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.creation_date =

                        (SELECT MAX (aia1.creation_date)

                           FROM ap_invoices_all aia1

                          WHERE     aia1.vendor_id = asp.vendor_id

                                AND aia1.org_id = apsa.org_id

                                AND apsa.vendor_site_id = aia1.vendor_site_id

                                AND aia1.INVOICE_CURRENCY_CODE <> 'INR'))

            conversion_rate

    FROM ap_suppliers asp

,ap_supplier_sites_all apsa

,hr_operating_units hou

   WHERE asp.vendor_id = apsa.vendor_id

     AND hou.organization_id = apsa.org_id

     AND asp.vendor_name = :p_vendor_name

ORDER BY 2