Query to find debit memo in oracle apps

Query to find debit memo in oracle apps

In this post , I will share the sql queries which will help to share the debit memo details for both parables and receivables modules in oracle apps r12. I will share the two sql queries for debit memo, One for AP debit memo and second one for AR debit memo in oracle apps r12. First i will try to explain the working of Debit memo for both parables and receivables modules in oracle apps r12. In AR  ,  we do create in AR Amount , if we want to Increase the Customer Liability for an AR invoice. For example , We have send one AR invoice for our customer , but after that we realized that we forgot to send the tax or freight amount in that invoice and now I want to add that amount for that invoice so for this , I will be create one Debit memo in AR and send this to the customer , so that final Pay amount have to pay by the customer is Main Invoice + Debit Memo.

In AP , Debit memo We do create  , if we want to reduce the bill amount for the supplier due to damage items received from supplier or defaulted items or wrong services. But both working at the end to reduce the final AP bill amount in oracle apps. Here below i will try to share complete SQL query to find the debit memo in oracle apps.

SQL Query to find AR debit memo in oracle apps

SELECT

 OOD.ORGANIZATION_NAME,

 RCT.TRX_NUMBER "TRX NUMBER",

  DECODE(RTT.TYPE,'DM','Dedit Memo',RTT.TYPE) TRANSACTION_TYPE,

 RCT.TRX_DATE "TRX DATE",

 RCG.GL_DATE,

 RCT.PURCHASE_ORDER,

 RCL.DESCRIPTION,

 HCA.ACCOUNT_NUMBER,

 HP.PARTY_NAME,

 RTT.NAME TRANSACTION_NAME,

RCL.LINE_TYPE AR_LINE_TYPE,

 sum((DECODE(RCT.INVOICE_CURRENCY_CODE,'INR',RCG.AMOUNT*1,RCG.AMOUNT*RCT.EXCHANGE_RATE))) TOTAL_INV_AMOUNT

 FROM

apps.ORG_ORGANIZATION_DEFINITIONS OOD,

apps.ar_customers ac,

apps.HZ_CUST_ACCOUNTS HCA,

apps.HZ_PARTIES HP,

apps.RA_CUST_TRX_TYPES_ALL RTT,

apps.HZ_CUST_SITE_USES_ALL HCSUA,

apps.RA_CUSTOMER_TRX_ALL RCT,

apps.RA_CUSTOMER_TRX_LINES_ALL RCL,

apps.RA_CUST_TRX_LINE_GL_DIST_ALL RCG

 WHERE

 RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID

 AND RCL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID

 AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID

 and HCA.CUST_ACCOUNT_ID=ac.customer_id

 AND HCA.PARTY_ID = HP.PARTY_ID

 AND RTT.TYPE='DM'

 AND RCT.CUST_TRX_TYPE_ID = RTT.CUST_TRX_TYPE_ID

 AND TO_NUMBER(RCT.INTERFACE_HEADER_ATTRIBUTE10) = OOD.ORGANIZATION_ID (+)

 AND RCT.BILL_TO_SITE_USE_ID=HCSUA.SITE_USE_ID

 GROUP BY

 RCT.CUSTOMER_TRX_ID,RCT.TRX_NUMBER,

 RCT.CUST_TRX_TYPE_ID,RCT.TRX_DATE,RCG.GL_DATE,

 RCT.CUSTOMER_TRX_ID,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_NAME,

 RCL.DESCRIPTION,

 HCA.ACCOUNT_NUMBER,

 HP.PARTY_NAME,

HCSUA.LOCATION,

 RTT.NAME,RTT.TYPE,RCL.LINE_TYPE

SQL Query to find AP debit 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='DEBIT'

   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