SELECT NVL(SUM(AR_Pay_Sch_tbl.AMOUNT_DUE_REMAINING), 0) 		unClearedReceiptTotalAmt
  FROM HZ_CUST_Agcc_tblOUNTS_ALL           			HZ_CUST_Agcc_tblT_ALL_TBL,
       AR_PAYMENT_SCHEDULES_ALL       				AR_Pay_Sch_tbl,
       AR_RECEIVABLE_APPLICATIONS_ALL 				AR_Rec_Appl_tbl,
       HZ_HZ_CUST_Agcc_tblT_ALL_TBL_SITES_ALL       HZ_CUST_AGgcc_tbl_TBLT_SITES,
       HZ_HZ_PARTY_SITES_TBLS                 		HZ_PARTY_SITES_TBL,
       HZ_Hz_loc_tblATIONS                   		Hz_loc_tbl,
       HZ_CUST_HZ_CUST_SITE_USES_TBL_ALL          	HZ_CUST_SITE_USES_TBL,
       AR_CASH_RECEIPT_HISTORY_ALL    				AR_CASH_RECPT_HIST_TBL,
       AR_CASH_RECEIPTS_ALL           				AR_CASH_RCPT_ALL_TBL,
       GL_CODE_COMBINATIONS           				gcc_tbl
 WHERE 1=1
   AND AR_Pay_Sch_tbl.CUSTOMER_ID = HZ_CUST_Agcc_tblT_ALL_TBL.CUST_Agcc_tblOUNT_ID
   AND AR_Pay_Sch_tbl.CUSTOMER_ID = HZ_CUST_Agcc_tblT_ALL_TBL.CUST_Agcc_tblOUNT_ID
   AND HZ_CUST_AGgcc_tbl_TBLT_SITES.HZ_PARTY_SITES_TBL_ID = HZ_PARTY_SITES_TBL.HZ_PARTY_SITES_TBL_ID
   AND Hz_loc_tbl.Hz_loc_tblATION_ID = HZ_PARTY_SITES_TBL.Hz_loc_tblATION_ID
   AND AR_Pay_Sch_tbl.CASH_RECEIPT_ID = AR_CASH_RCPT_ALL_TBL.CASH_RECEIPT_ID
   AND AR_CASH_RCPT_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_Pay_Sch_tbl.CASH_RECEIPT_ID = AR_Rec_Appl_tbl.CASH_RECEIPT_ID
   AND HZ_CUST_SITE_USES_TBL.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID = HZ_CUST_AGgcc_tbl_TBLT_SITES.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID
   AND HZ_CUST_Agcc_tblT_ALL_TBL.CUST_Agcc_tblOUNT_ID = HZ_CUST_AGgcc_tbl_TBLT_SITES.CUST_Agcc_tblOUNT_ID
   AND HZ_CUST_AGgcc_tbl_TBLT_SITES.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID = HZ_CUST_SITE_USES_TBL.HZ_CUST_Agcc_tblT_ALL_TBL_SITE_ID
   AND AR_Pay_Sch_tbl.CUSTOMER_ID = HZ_CUST_AGgcc_tbl_TBLT_SITES.CUST_Agcc_tblOUNT_ID
   AND AR_Pay_Sch_tbl.CUSTOMER_SITE_USE_ID = HZ_CUST_SITE_USES_TBL.SITE_USE_ID
   AND AR_Pay_Sch_tbl.CASH_RECEIPT_ID = AR_CASH_RECPT_HIST_TBL.CASH_RECEIPT_ID
   AND NVL(HZ_CUST_SITE_USES_TBL.STATUS, 'A') = 'A'
   AND AR_Pay_Sch_tbl.CLASS = 'PMT'
   AND AR_Rec_Appl_tbl.STATUS = 'UNAPP'
   AND AR_Pay_Sch_tbl.STATUS = 'OP'
   AND HZ_CUST_SITE_USES_TBL.SITE_USE_CODE = 'BILL_TO'
   AND HZ_CUST_Agcc_tblT_ALL_TBL.Agcc_tblOUNT_NUMBER = '<ENTER AGCC TBLOUNT NUMBER>'
   AND AR_Pay_Sch_tbl.TRX_DATE <= '<ENTER_TRX_DATE>'
   AND TRUNC(AR_Pay_Sch_tbl.GL_DATE) <= '<ENTER_GL_DATE>'
   AND AR_CASH_RECPT_HIST_TBL.STATUS NOT IN ('CLEARED') HAVING
 NVL(SUM(AR_Rec_Appl_tbl.AMOUNT_APPLIED), 0) > 0