Friday, August 7, 2015

Query to Derive Customer Number, Site Use ID from Customer Bank Account Number

SELECT hca.account_number, hcsu.site_use_id, hca.cust_account_id INTO l_customer_number, l_cust_site_use_id, l_cust_account_id FROM hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, IBY_ACCOUNT_OWNERS IAO, IBY_EXT_BANK_ACCOUNTS IEBA WHERE hca.cust_account_id = hcas.cust_account_id AND hca.party_id = iao.account_owner_party_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.org_id = AND hca.status = 'A' AND hcas.status = 'A' AND hcsu.status = 'A' AND hcas.bill_to_flag = 'P' AND NVL (IAO.end_date, SYSDATE + 1) > SYSDATE AND ieba.bank_account_num = AND iao.ext_bank_account_id = ieba.ext_bank_account_id;