Showing posts with label AR invoice query in oracle apps. Show all posts
Showing posts with label AR invoice query in oracle apps. Show all posts

Saturday, 17 November 2018

Oracle ar invoice balance query

Oracle ar invoice balance query

In this post , We will be discuss about SQL query to find  ar invoices Balance in oracle apps.This will help to share the complete detail of AR Invoices with the Open Balances as on date. Please find below  the complete sql Query of Oracle ar invoice balance query.
Oracle ar invoice balance query

 

Detail Oracle ar invoice balance query

select aps.*

FROM ra_customer_trx_all rcta,

ra_customer_trx_lines_all rl,

ar_payment_schedules_all aps,

ra_cust_trx_types_all rt,

hz_cust_accounts hc,

hz_parties hp,

hz_cust_acct_sites_all hcasa_bill,

hz_cust_site_uses_all hcsua_bill,

hz_party_sites hps_bill,
 
ra_cust_trx_line_gl_dist_all rct

 

WHERE rcta.cust_trx_type_id = rt.cust_trx_type_id

AND rcta.bill_to_customer_id = hc.cust_account_id

AND hc.status = 'A'

AND hp.party_id = hc.party_id

AND rcta.customer_trx_id = rl.customer_trx_id

AND rcta.customer_trx_id = aps.customer_trx_id

AND rcta.org_id = aps.org_id

AND rct.customer_trx_id = aps.customer_trx_id

AND rct.customer_trx_id = rcta.customer_trx_id

AND rct.customer_trx_id = rl.customer_trx_id

AND rct.customer_trx_line_id = rl.customer_trx_line_id

AND rcta.complete_flag = 'Y'

AND hcasa_bill.status = 'A'

AND hcsua_bill.status = 'A'

AND aps.amount_due_remaining <> 0

AND aps.status = 'OP'

AND rl.line_type IN ('FREIGHT', 'LINE')

AND hcasa_bill.cust_account_id = rcta.bill_to_customer_id

AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id

AND hcsua_bill.site_use_code = 'BILL_TO'

AND hcsua_bill.site_use_id = rcta.bill_to_site_use_id

AND hps_bill.party_site_id = hcasa_bill.party_site_id

Thursday, 7 June 2018

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

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *