Query To Fetch The Unapplied Receipts Report - Doyensys Blog

Home » Ar_cash_receipt_history_all Query » Query To Fetch The Unapplied Receipts Report - Doyensys Blog

Maybe your like

  • 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 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

Tag » Ar_cash_receipt_history_all Query