This AR Receipts Without Sites query identify all receipts that are not associated with any bill-to site addresses.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query identify all receipts that are not associated with any bill-to site addresses
HAOU.NAME ORGANIZATION_NAME,
SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ACRA.RECEIPT_NUMBER ,
ABA.NAME BATCH_NAME,
ARM.NAME RECEIPT_METHOD,
CBA.BANK_ACCOUNT_NAME REMITTANCE_BANK_ACCOUNT_NAME,
ACRA.CURRENCY_CODE,
NVL (ACRA.AMOUNT, 0) RECEIPT_AMOUNT,
ACRHA.NOTE_STATUS,
HCA.TAX_ROUNDING_RULE,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
HP.CATEGORY_CODE CUSTOMER_CATEGORY_CODE,
HCA.CUSTOMER_TYPE,
ACRA.REVERSAL_CATEGORY,
ACRA.REVERSAL_REASON_CODE
FROM
AR_CASH_RECEIPTS_ALL ACRA,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
AR_BATCHES_ALL ABA,
AR_RECEIPT_METHODS ARM,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
AND ACRHA.FIRST_POSTED_RECORD_FLAG = 'Y'
AND ACRHA.BATCH_ID = ABA.BATCH_ID(+)
AND ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ACRA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID(+)
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND ACRA.CUSTOMER_SITE_USE_ID IS NULL
AND NVL (ACRA.CONFIRMED_FLAG, 'Y') = 'Y'
AND NVL (ACRA.TYPE, 'X') != 'MISC'
AND ACRA.ORG_ID = HAOU.ORGANIZATION_ID
ORDER BY
ACRA.CURRENCY_CODE,
HP.PARTY_NAME,
HCA.ACCOUNT_NUMBER,
ACRA.RECEIPT_NUMBER