Skip to content

oracleappshelp.com

Node.JS Blogs

  • MongoDB
  • Oracle R12
  • SOA Suite 12c
  • ADF 12c
  • Interview Questions
  • Node.js
  • ReactJS

Oracle AR SQL Query to get on-account receipt amount total

» Oracle Apps » Oracle Receivables » Oracle AR SQL Query to get on-account receipt amount total

Categories

  • ADF Issues
  • Angular JS
  • AP- Account Payables
  • Architecture
  • cloud computing
  • Databases
  • Devops
  • FA – Fixed Assets
  • Interview Questions
  • Java
  • Java Basics
  • Java Springs
  • JavaScript
  • JDeveloper
  • MongoDB tutorial
  • Node.js tutorial
  • OM- Order Managment
  • Oracle ADF
  • Oracle Apps
  • Oracle General Ledger
  • Oracle Receivables
  • Oracle SOA
  • ReactJS Tutorial
  • SQL Server
  • System Administrator
  • WebLogic
  • XML Tutorial

Popular Tutorials

  • MongoDB
  • Oracle R12
  • SOA Suite 12c
  • ADF 12c
  • Interview Questions
  • Node.js
  • ReactJS

Pages

  • MongoDB Tutorial for beginners
  • Oracle ADF 12c Tutorials for beginners
  • Oracle EBS R12 Tutorials
  • Oracle SOA Suite 12c Tutorials for beginners
  • React JS Programming Tutorial for beginners
  • RSS Feed
  • Style Components in React JS

Oracle AR SQL Query to get on-account receipt amount total

26 Jan 2021 oracleappsadmin

The blog provides the SQL Query to get On-Account Receipt Amount Total in Oracle Receivables.

What is On-account receipt in Receivables ?

Oracle Receivables R12 allows to apply on-account receipts to customer’s open debit items. Consider and example Customer receives the goods worth $1000 but was not happy with the received goods and thus company agrees to credit $200 to customer account. When customer makes the payment of $800, then $200 is credited as customer on-account

Oracle Receivables On-Account Receipt Amount

SELECT NVL(SUM(AR_PAYMENT_SCH_ALL_TBL.AMOUNT_DUE_REMAINING), 0) OnAccount_Recpt_TotalAmt
  FROM HZ_CUST_AGCC_TBLOUNTS_ALL      HZ_CUST_AGCC_TBLTS_ALL_TBL,
       AR_PAYMENT_SCHEDULES_ALL       AR_PAYMENT_SCH_ALL_TBL,
       AR_RECEIVABLE_APPLICATIONS_ALL AR_RECV_APPLS_ALL_TBL,
       HZ_HZ_CUST_ACCT_ALL_TBL_SITES_ALL    HZ_CUST_AGCC_TBLT_SITES ,
       HZ_HZ_PARTY_SITES_TBLS         HZ_PARTY_SITES_TBL,
       HZ_LOCATIONS                   LOC,
       HZ_CUST_HZ_SITE_USES_TBL_ALL   HZ_SITE_USES_TBL,
       AR_CASH_RECEIPTS_ALL           AR_CASH_RECPT_ALL_TBL,
       AR_CASH_RECEIPT_HISTORY_ALL    AR_CASH_RECPT_HIST_TBL,
       GL_CODE_COMBINATIONS           GCC_TBL
 WHERE 1=1
   AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_ID = HZ_CUST_AGCC_TBLTS_ALL_TBL.CUST_AGCC_TBLOUNT_ID
   AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_ID = HZ_CUST_AGCC_TBLTS_ALL_TBL.CUST_AGCC_TBLOUNT_ID
   AND HZ_CUST_AGCC_TBLT_SITES .HZ_PARTY_SITES_TBL_ID = HZ_PARTY_SITES_TBL.HZ_PARTY_SITES_TBL_ID
   AND LOC.LOCATION_ID = HZ_PARTY_SITES_TBL.LOCATION_ID
   AND AR_PAYMENT_SCH_ALL_TBL.CASH_RECEIPT_ID = AR_CASH_RECPT_ALL_TBL.CASH_RECEIPT_ID
   AND AR_CASH_RECPT_ALL_TBL.CASH_RECEIPT_ID = AR_CASH_RECPT_HIST_TBL.CASH_RECEIPT_ID
   AND AR_CASH_RECPT_HIST_TBL.AGCC_TBLOUNT_CODE_COMBINATION_ID = GCC_TBL.CODE_COMBINATION_ID
   AND AR_PAYMENT_SCH_ALL_TBL.CLASS = 'PMT'
   AND AR_PAYMENT_SCH_ALL_TBL.CASH_RECEIPT_ID = AR_RECV_APPLS_ALL_TBL.CASH_RECEIPT_ID
   AND AR_RECV_APPLS_ALL_TBL.STATUS IN ('AGCC_TBL')
   AND AR_PAYMENT_SCH_ALL_TBL.STATUS = 'OP'
   AND HZ_SITE_USES_TBL.SITE_USE_CODE = 'BILL_TO'
   AND HZ_SITE_USES_TBL.HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID = HZ_CUST_AGCC_TBLT_SITES .HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID
   AND NVL(HZ_SITE_USES_TBL.STATUS, 'A') = 'A'
   AND HZ_CUST_AGCC_TBLTS_ALL_TBL.CUST_AGCC_TBLOUNT_ID = HZ_CUST_AGCC_TBLT_SITES .CUST_AGCC_TBLOUNT_ID
   AND HZ_CUST_AGCC_TBLT_SITES .HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID = HZ_SITE_USES_TBL.HZ_CUST_AGCC_TBLTS_ALL_TBL_SITE_ID
   AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_ID = HZ_CUST_AGCC_TBLT_SITES .CUST_AGCC_TBLOUNT_ID
   AND HZ_CUST_AGCC_TBLTS_ALL_TBL.AGCC_TBLOUNT_NUMBER = '<ENTER_ACCOUNT_NUMBER>'
   AND AR_PAYMENT_SCH_ALL_TBL.TRX_DATE <= '<ENTER_TRX_DATE>'
   AND TRUNC(AR_PAYMENT_SCH_ALL_TBL.GL_DATE) <= '<ENTER_GL_DATE>'
   AND AR_PAYMENT_SCH_ALL_TBL.CUSTOMER_SITE_USE_ID = HZ_SITE_USES_TBL.SITE_USE_ID HAVING
   NVL(SUM(AR_RECV_APPLS_ALL_TBL.AMOUNT_APPLIED), 0) > 0;
Oracle Receivables AR On-Account Receipt Amount in Oracle Apps R12, how to find on-account Receipt Amount total in oracle ar r12

Share this on ...

  • No Related Posts Yet!

Meta

  • Register
  • Log in

Archives

  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • July 2019
  • June 2019
  • May 2019
  • May 2018
  • April 2018
  • November 2017
  • April 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • June 2014
  • May 2014
  • March 2014
  • September 2013
  • May 2013
  • September 2012
  • June 2012

Archives

  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • July 2019
  • June 2019
  • May 2019
  • May 2018
  • April 2018
  • November 2017
  • April 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • June 2014
  • May 2014
  • March 2014
  • September 2013
  • May 2013
  • September 2012
  • June 2012

Archives

  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • July 2019
  • June 2019
  • May 2019
  • May 2018
  • April 2018
  • November 2017
  • April 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • June 2014
  • May 2014
  • March 2014
  • September 2013
  • May 2013
  • September 2012
  • June 2012

Powered by WordPress. Theme: Brawny by Webulous Themes

Recent Posts

  • How to create legal entity registration in Oracle Fusion
  • Query to get organization structure details in Oracle Fusion General Ledger
  • Assign Legal Entities and Balancing segments in Oracle Fusion
  • Create Legal Entity in Oracle Fusion
  • How to Create Implementation Project in Oracle Fusion
  • Purchase Requisition BPM Approval Workflow Tables in Oracle Fusion
  • Query to get Oracle Fusion Payables Cloud approver comments
  • How to create implementation user in Oracle Fusion
  • Functional Setup Manager in Oracle fusion
  • Top 50 AJAX Interview Questions and Answers