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 Venkatesh.b@doyensys.com or use the comment section below to ask your questions
