Query to get AP Invoice details in R12

SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.INVOICE_ID

  ,AP_INVOICES_ALL.INVOICE_NUM

  ,AP_INVOICES_ALL.INVOICE_CURRENCY_CODE

  ,AP_INVOICES_ALL.VENDOR_ID

  ,AP_INVOICES_ALL.VENDOR_SITE_ID

  ,AP_INVOICES_ALL.INVOICE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.INVOICE_RECEIVED_DATE

  ,AP_INVOICES_ALL.CREATION_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID

  ,PO_HEADERS_ALL.SEGMENT1

  ,PO_LINES_ALL.LINE_NUM

  ,PO_HEADERS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.LAST_UPDATE_DATE

  ,PO_HEADERS_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.ITEM_DESCRIPTION

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.WFAPPROVAL_STATUS

  ,AP_INVOICES_ALL.PAYMENT_STATUS_FLAG

  ,AP_INVOICES_ALL.CANCELLED_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE

  ,PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID

  ,AP_INVOICES_ALL.TERMS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE

  ,AP_INVOICES_ALL.EXCHANGE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICES_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE

  ,AP_INVOICES_ALL.EXCHANGE_RATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICES_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED

  ,AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT

  ,AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT

  ,CASE WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL 

        THEN PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

ELSE AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

   END

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE

  ,PO_VENDORS.RECEIPT_REQUIRED_FLAG

  ,PO_VENDORS.INSPECTION_REQUIRED_FLAG

  ,PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG

  ,PO_VENDORS.LAST_UPDATE_DATE

  ,AP_INVOICES_ALL.SOURCE

  ,AP_INVOICES_ALL.PROJECT_ID

  ,AP_INVOICES_ALL.TASK_ID

  ,AP_INVOICES_ALL.EXPENDITURE_TYPE

  ,AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID

  ,AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE

  ,AP_INVOICES_ALL.PAYMENT_CROSS_RATE

  ,AP_INVOICES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.LINE_NUMBER

  ,AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICE_LINES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.LEGAL_ENTITY_ID

  FROM AP_INVOICE_DISTRIBUTIONS_ALL

      ,AP_INVOICE_LINES_ALL

  ,AP_INVOICES_ALL

  ,PO_LINES_ALL

  ,PO_HEADERS_ALL

  ,PO_VENDORS

  ,PO_VENDOR_SITES_ALL

  ,PO_DISTRIBUTIONS_ALL

  ,(SELECT AP_INVOICES_ALL.INVOICE_ID 

      FROM AP_INVOICES_ALL 

WHERE AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/Y YYY HH24:MI:SS') 

UNION 

SELECT AP_INVOICE_LINES_ALL.INVOICE_ID 

  FROM AP_INVOICE_LINES_ALL 

WHERE AP_INVOICE_LINES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM /DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_HOLDS_ALL.INVOICE_ID 

  FROM AP_HOLDS_ALL 

WHERE AP_HOLDS_ALL.RELEASE_LOOKUP_CODE IS NULL 

   AND AP_HOLDS_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/D D/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL

      ,AP_INVOICE_DISTRIBUTIONS_ALL

  ,PO_HEADERS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID

UNION 

    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, PO_LINES_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID 

   AND PO_LINES_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/DD/YYYY H H24:MI:SS') 

   ) TEMP 

 WHERE AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID 

   AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+) 

   AND AP_INVOICES_ALL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = TEMP.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICE_LINES_ALL.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER = AP_INVOICE_LINES_ALL.LINE_NUMBER 

Invalid customer transaction identifier.APPLY : ORA-01403: no data foundORA-01403: no data found in Package AR_RECEIPT_API_PUB Procedure Apply

One of the possible reason is Invoice already paid.

You can validate with below query. 

select * from ra_customer_trx_all where trx_number = '1'

note customer_trx_id from the above query.

select * from ar_receivable_applications_all

where applied_customer_trx_id = <pass customer trx id>

note cash_receipt_id from above query 

select * from ar_cash_receipts_all

where cash_receipt_id = <pass cash receipt id>

Query to get PO With Approval , Invoice and Payment Details in Oracle APPS

SELECT inv.org_id

      ,supp.segment1 supplier_number

      ,supp.vendor_name supplier_name

      ,UPPER (supp.vendor_type_lookup_code) supplier_type

      ,supp_site.vendor_site_code supplier_site_code

      ,supp_site.address_line1

  ,supp_site.address_line2

  ,supp_site.address_line3

  ,supp_site.address_line4

      ,supp_site.city 

      ,supp_site.country

      ,TO_CHAR (po_hdrs.creation_date, 'DD-MON-RRRR') po_creation_date

      ,po_hdrs.segment1 po_number

      ,po_hdrs.type_lookup_code po_type

      ,po_dist.quantity_ordered

      ,po_dist.quantity_cancelled

      ,po_lines.item_id 

      ,po_lines.item_description item_description

      ,po_lines.unit_price

      ,(NVL (po_dist.quantity_ordered, 0) - NVL (po_dist.quantity_cancelled, 0))

       * NVL (po_lines.unit_price, 0)

          po_line_amount

      ,(SELECT DECODE (ph.approved_flag, 'Y', 'Approved'

                                   , 'R', 'Requires Reapproval'

   , 'F', 'Failed Approval'

   , 'N', 'Never Approved'

   )

          FROM po.po_headers_all ph

         WHERE ph.po_header_id = po_hdrs.po_header_id)

          po_approved_status

      ,inv.invoice_type_lookup_code invoice_type

      ,inv.invoice_amount invoice_amount

      ,TO_CHAR(inv.invoice_date, 'DD-MON-RRRR') invoice_date

      ,inv.invoice_num 

      ,(SELECT DECODE (ap_inv_dist.match_status_flag, 'A', 'Approved'

                                                , 'T', 'Tested but not Validate'

,'Never Validated')

          FROM ap.ap_invoice_distributions_all ap_inv_dist

         WHERE ap_inv_dist.invoice_distribution_id = inv_dist.invoice_distribution_id) invoice_approved_status

      ,inv.amount_paid

      ,inv_pmt.amount

      ,inv_pmt.check_id

      ,inv_pmt.invoice_payment_id payment_id

      ,chk.check_number cheque_number

      ,TO_CHAR(chk.check_date, 'DD-MON-RRRR') check_date

  FROM ap_invoices_all inv

      ,ap_invoice_distributions_all inv_dist

  ,po_distributions_all po_dist

  ,po_headers_all po_hdrs

  ,ap_suppliers supp

  ,ap_supplier_sites_all supp_site

  ,po_lines_all po_lines

  ,ap_invoice_payments_all inv_pmt

      ,ap_checks_all chk

 WHERE     inv.invoice_id = inv_dist.invoice_id

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id(+)

       AND po_dist.po_header_id = po_hdrs.po_header_id(+)

       AND supp.vendor_id(+) = po_hdrs.VENDOR_ID

       AND supp_site.vendor_site_id(+) = po_hdrs.vendor_site_id

       AND po_hdrs.po_header_id = po_lines.po_header_id

       AND po_dist.po_line_id = po_lines.po_line_id

       AND inv.invoice_id = inv_pmt.invoice_id

       AND inv_pmt.check_id = chk.check_id

       AND supp_site.vendor_site_id = chk.vendor_site_id

       AND po_dist.po_header_id IS NOT NULL

       AND inv.payment_status_flag = 'Y'

       AND po_hdrs.type_lookup_code != 'BLANKET'

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