Query to find ap credit memo in oracle apps

Query to find ap credit memo in oracle apps

In this post , I will be share the SQL query which helps to extract the AP credit memo information's in oracle apps. We know that we do create the AP credit memo in oracle apps , when we want to reduce our liability for the supplier invoice. This helps to reduce the final due amount which we need to pay to supplier against supplier invoices. Here below , i will share the detail SQL query and the important tables which we do use in the AP credit memo SQL query in oracle apps. There is no much difference between normal AP invoice query and the Credit memo SQL query in oracle apps. 

Most Commonly used tables in the Query to find ap credit memo in oracle apps

1.ap_invoices_all 

2.ap_invoice_lines_all

3.ap_invoice_distributions_all

4.ap_suppliers

5.ap_supplier_sites_all

6.po_headers_all

7.gl_code_combinations_kfv

8.ap_invoice_payments_all

9.ap_checks_all

10.ap_payment_schedules_all

11.ap_terms

 12.hr_operating_units

 13.gl_ledgers

Detail SQL Query to find ap credit memo in oracle apps

select aia.invoice_num,     

aia.invoice_type_lookup_code,

       aia.invoice_currency_code,

     aia.invoice_date,

       aps.vendor_name,

 DECODE(aia.PAYMENT_STATUS_FLAG,'N','UN-PAID','P','Partial Paid','Y','PAID') PAYMENT_STATUS_FLAG ,

 apss.vendor_site_code,

aia.invoice_amount,

 aila.amount line_amount,

pha.segment1 po_number,

aila.line_type_lookup_code,

apt.name Term_name,     

gcc.concatenated_segments distributed_code_combinations,

aca.check_number,

 aipa.amount payment_amount,

 apsa.amount_remaining,

 hou.name operating_unit

  from apps.ap_invoices_all         aia,

       apps.ap_invoice_lines_all         aila,

       apps.ap_invoice_distributions_all aida,

       apps.ap_suppliers aps,

       apps.ap_supplier_sites_all apss,

       apps.po_headers_all pha,

       apps.gl_code_combinations_kfv gcc,

       apps.ap_invoice_payments_all aipa,

       apps.ap_checks_all aca,

       apps.ap_payment_schedules_all apsa,

       apps.ap_terms apt,

       apps.hr_operating_units hou,

       apps.gl_ledgers gl

 where aia.invoice_id = aila.invoice_id

   and aila.invoice_id = aida.invoice_id

  

   and aia.invoice_type_lookup_code='CREDIT'

   and aila.line_number = aida.invoice_line_number

   and aia.vendor_id=aps.vendor_id

   and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

   AND aps.vendor_id=apss.VENDOR_ID

   and aia.po_header_id=pha.po_header_id(+)

   and aida.dist_code_combination_id=gcc.code_combination_id

   and aipa.invoice_id(+)=aia.invoice_id

   and aca.check_id   (+)=aipa.check_id

   and apsa.invoice_id=aia.invoice_id

   and apt.term_id=aia.terms_id

   and hou.organization_id=aia.org_id

   and gl.ledger_id=aia.set_of_books_id

   and aia.ORG_ID=:P_ORG_ID

Query to find ap credit memo in oracle apps