Query To Fetch The Unapplied Receipts Report - Doyensys Blog

• ServicesOpen menu
  • Enterprise Platform
    • Oracle Enterprise Business Suite ​
    • Oracle Fusion Cloud Application
    • Microsoft Dynamics
  • Digital Services
    • Artificial Intelligence
    • Application Development and Modernization​
    • Software Engineering​
  • Infrastructure Services
    • Hybrid Cloud Management
    • Database & Middleware
    • Network Management
    • End User Computing Services
    • Consult & Transform
  • Managed Services
  • Software Testing & Quality Assurance
  • Data Engineering, BI & Analytics
• SolutionsOpen menu
  • Smart Manufacturing
  • Cloud Transformation
  • Value Innovation
• CompanyOpen menu
  • About
  • Culture
  • Careers
  • Jobs
  • Governance
• ResourcesOpen menu
  • Books
  • Blogs
  • Case Studies
  • White Papers
  • Faq

• Oraculars
• Contact

Query to Fetch the Unapplied Receipts Report Home > EBS Technical > Query to Fetch the Unapplied Receipts Report

Introduction

This Query will help to Fetch the Unapplied Receipts Report Data

SQL Query: SELECT COMPANY COMPANY, CASE WHEN CUSTOMER_NAME IS NULL THEN ‘ **** Unidentified’ ELSE CUSTOMER_NAME END CUSTOMER_NAME, — TO_DATE (GL_DATE,’YYYY/MM/DD’) GL_DATE, TO_CHAR (GL_DATE,’DD-MON-RRRR’) GL_DATE, CUSTOMER_NUMBER CUSTOMER_NUMBER, BATCH_NAME BATCH_NAME, PAYMENT_METHOD PAYMENT_METHOD, TO_CHAR(PAYMENT_NUMBER) PAYMENT_NUMBER, RECEIPT_AMOUNT RECEIPT_AMOUNT, UNAPPLIED_AMOUNT UNAPPLIED_AMOUNT, NVL (CLAIM_AMOUNT, 0) CLAIM_AMOUNT FROM (SELECT CC.SEGMENT1 COMPANY, HP.PARTY_NAME CUSTOMER_NAME, MAX (ARAA.GL_DATE) GL_DATE, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER, ABA.NAME BATCH_NAME, ARM.NAME PAYMENT_METHOD, ACRA.RECEIPT_NUMBER PAYMENT_NUMBER, ACRA.AMOUNT RECEIPT_AMOUNT, ARAA.CASH_RECEIPT_ID, (SELECT SUM (ARAC.AMOUNT_APPLIED) FROM AR_RECEIVABLE_APPLICATIONS_ALL ARAC WHERE ARAC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID AND ARAC.STATUS IN (‘UNAPP’, ‘UNID’) AND ARAC.GL_DATE BETWEEN NVL (TRUNC (TO_DATE (TO_DATE (:P_GL_DATE_FROM, ‘RRRR/MM/DD HH24:MI:SS’ ), ‘DD-MM-RRRR’ ) ), ARAC.GL_DATE ) AND NVL (TRUNC (TO_DATE (TO_DATE (:P_GL_DATE_TO, ‘RRRR/MM/DD HH24:MI:SS’ ), ‘DD-MM-RRRR’ ) ), ARAC.GL_DATE )) UNAPPLIED_AMOUNT, (SELECT SUM (ARAC.AMOUNT_APPLIED) FROM AR_RECEIVABLE_APPLICATIONS_ALL ARAC WHERE ARAC.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID AND ARAC.STATUS = ‘OTHER ACC’ AND ARAC.GL_DATE BETWEEN NVL (TRUNC (TO_DATE (TO_DATE (:P_GL_DATE_FROM, ‘RRRR/MM/DD HH24:MI:SS’ ), ‘DD-MM-RRRR’ ) ), ARAC.GL_DATE ) AND NVL (TRUNC (TO_DATE (TO_DATE (:P_GL_DATE_TO, ‘RRRR/MM/DD HH24:MI:SS’ ), ‘DD-MM-RRRR’ ) ), ARAC.GL_DATE )) CLAIM_AMOUNT FROM AR_RECEIVABLE_APPLICATIONS_ALL ARAA, AR_CASH_RECEIPTS_ALL ACRA, AR.AR_CASH_RECEIPT_HISTORY_ALL ACRHA, AR_BATCHES_ALL ABA, AR.HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, AR_BATCH_SOURCES_ALL ABSA, GL_CODE_COMBINATIONS CC, AR_RECEIPT_METHODS ARM WHERE 1 = 1 AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID AND ARAA.STATUS IN (‘OTHER ACC’, ‘UNAPP’, ‘UNID’) AND ARAA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID AND ACRHA.BATCH_ID = ABA.BATCH_ID(+) AND ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID(+) AND HCA.PARTY_ID = HP.PARTY_ID(+) AND ABA.BATCH_SOURCE_ID = ABSA.BATCH_SOURCE_ID(+) AND ARAA.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID AND ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID AND CC.SEGMENT1 BETWEEN NVL (:P_COMPANY_SEGMENT_LOW, CC.SEGMENT1 ) AND NVL (:P_COMPANY_SEGMENT_HIGH, CC.SEGMENT1 ) AND ARAA.GL_DATE BETWEEN NVL (TRUNC (TO_DATE (TO_DATE (:P_GL_DATE_FROM, ‘RRRR/MM/DD HH24:MI:SS’ ), ‘DD-MM-RRRR’ ) ), ARAA.GL_DATE ) AND NVL (TRUNC (TO_DATE (TO_DATE (:P_GL_DATE_TO, ‘RRRR/MM/DD HH24:MI:SS’ ), ‘DD-MM-RRRR’ ) ), ARAA.GL_DATE ) AND ACRA.CURRENCY_CODE = NVL (:P_CURRENCY_CODE, ACRA.CURRENCY_CODE) AND NVL (ABA.NAME, ‘X’) BETWEEN NVL (:P_BATCH_NAME_LOW, NVL (ABA.NAME, ‘X’) ) AND NVL (:P_BATCH_NAME_HIGH, NVL (ABA.NAME, ‘X’) ) AND NVL (ABSA.NAME, ‘X’) BETWEEN NVL (:P_BATCH_SOURCE_NAME_LOW, NVL (ABSA.NAME, ‘X’) ) AND NVL (:P_BATCH_SOURCE_NAME_HIGH, NVL (ABSA.NAME, ‘X’) ) AND NVL (HP.PARTY_NAME, ‘UNID’) BETWEEN NVL (:P_CUSTOMER_NAME_LOW, NVL (HP.PARTY_NAME, ‘UNID’ ) ) AND NVL (:P_CUSTOMER_NAME_HIGH, NVL (HP.PARTY_NAME, ‘UNID’ ) ) AND NVL (HCA.ACCOUNT_NUMBER, ‘UNID’) BETWEEN NVL (:P_CUSTOMER_NUMBER_LOW, NVL (HCA.ACCOUNT_NUMBER, ‘UNID’) ) AND NVL (:P_CUSTOMER_NUMBER_HIGH, NVL (HCA.ACCOUNT_NUMBER, ‘UNID’) ) AND ACRA.RECEIPT_NUMBER BETWEEN NVL (:P_RECEIPT_NUMBER_LOW, ACRA.RECEIPT_NUMBER ) AND NVL (:P_RECEIPT_NUMBER_HIGH, ACRA.RECEIPT_NUMBER ) GROUP BY CC.SEGMENT1, HP.PARTY_NAME, HCA.ACCOUNT_NUMBER, ABA.NAME, ARM.NAME, ACRA.RECEIPT_NUMBER, ACRA.AMOUNT, ARAA.CASH_RECEIPT_ID HAVING NVL (SUM (ARAA.AMOUNT_APPLIED), 0) <> 0) A ORDER BY CUSTOMER_NAME ASC Got any queries?

Do drop a note by writing us at [email protected] or use the comment section below to ask your questions

Venkatesh Balasubramanian

• venkatesh.b@doyensys.com

Recent Posts

• Script to import standard purchase order
• Query to get Journal Entry Reserve Ledger Report
• Connect Your Oracle Cloud VCNs Like a Pro: The Complete Local Peering Gateway Guide

Follow Us

Post navigation

Previous post

BI Publisher program failed due to opp

Next post

How to set default value for Message Text Input