AR Invoice Query In Oracle Apps. Query to get ar invoice details in oracle apps

AR Invoice Query In Oracle Apps. Query to get ar invoice details in oracle apps

 

In this Post , We will share the AR invoice query to extract the complete information's for the Oracle AR Invoices. With this query , you will get the complete end to end of the AR invoices. This query helps to extract the Customer wise AR invoice information. This helps to capture most required formations for the AR invoice in Oracle Apps. You can refer this query to get ar invoice details in oracle apps.

 

 

AR invoice query in oracle apps

 

 

oracle AR transaction query

 

SELECT

 RCT.CUSTOMER_TRX_ID,

 RCT.TRX_NUMBER "TRX NUMBER",

 RCT.TRX_DATE "TRX DATE",

 RCG.GL_DATE,

 RCT.PURCHASE_ORDER,

 OOD.ORGANIZATION_ID,

 OOD.ORGANIZATION_NAME,

 HCSUA.SITE_USE_ID,

 HCSUA.LOCATION,

 RCL.DESCRIPTION,

 HCA.ACCOUNT_NUMBER,

 HP.PARTY_NAME,

 RTT.NAME TRANSACTION_NAME,

 RTT.TYPE TRANSACTION_TYPE,

RCL.LINE_TYPE AR_LINE_TYPE,

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

 FU.USER_ID,FU.USER_NAME

 FROM

 ORG_ORGANIZATION_DEFINITIONS OOD,

 ar_customers ac,

 HZ_CUST_ACCOUNTS HCA,

 HZ_PARTIES HP,

 RA_CUST_TRX_TYPES_ALL RTT,

 FND_USER FU,

 HZ_CUST_SITE_USES_ALL HCSUA,

 RA_CUSTOMER_TRX_ALL RCT,

 RA_CUSTOMER_TRX_LINES_ALL RCL,

 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 ac.customer_name=:p_customer_name

 AND HCA.PARTY_ID = HP.PARTY_ID

 AND RCT.LAST_UPDATED_BY = FU.USER_ID

 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

 AND RCT.TRX_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE

 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,OOD.ORGANIZATION_ID,

 HP.PARTY_NAME,

 HCSUA.SITE_USE_ID,HCSUA.LOCATION,

 FU.USER_ID,FU.USER_NAME,           

 RTT.NAME,RTT.TYPE,RCL.LINE_TYPE

 

AR Invoice Query In Oracle Apps. Query to get ar invoice details in oracle apps