Hello All,
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Need to fetch all the Customers for which no E-Mail address is generated in HZ_CONTACT_POINTS table.
If E-Mail address is available then, a new entry (a line) will be present in HZ_CONTACT_POINTS table with CONTACT_POINT_TYPE as "E-Mail".
If No E-mail address, then in HZ_CONTACT_POINTS table there will be no data or NULL.
I have framed this query, keeping an outer join of "AND hps.party_site_id = hcp.owner_table_id(+)"
But it is not giving the desired results. It is fetching even the records which has CONTACT_POINT_TYPE as "Phone" and "E-Mail" in HZ_CONTACT_POINTS table.
If i include the condition, AND (hcp.email_address IS NULL AND hcp.contact_point_type NOT IN ('EMAIL','PHONE')) then none of the Customers are fetching in the output.
Let me know your thoughts on the same.
SELECT DISTINCT hca.account_number customer_num,
acv.customer_name customer_name,
hca.attribute5 mid,
hca.creation_date,
hca.status,
hcsu.site_use_code,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.postal_code,
hl.country,
hcp.contact_point_type,
hcp.email_address contact_email,
hcp.phone_area_code || ' ' || hcp.phone_number contact_phone,
hcp.url contact_web
FROM apps.ar_customers_v acv,
apps.ar_addresses_v aav,
apps.ar_contacts_v acc,
apps.ar_contact_roles_v acr,
apps.hz_phone_country_codes hpc,
apps.hz_cust_site_uses_all hcsu,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_accounts hca,
apps.hz_party_sites hps,
apps.hz_contact_points hcp,
apps.hz_locations hl
WHERE 1 = 1
AND hcsu.site_use_code = 'BILL_TO'
AND hcsu.status = 'A'
AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hps.status = 'A'
AND hps.party_site_id = hcp.owner_table_id(+)
AND hca.cust_account_id = acv.customer_id
AND acv.customer_id = aav.customer_id
AND acv.status = 'A'
AND aav.address_id = acc.address_id(+)
AND aav.status = 'A'
AND acr.contact_id(+) = acc.contact_id
AND hpc.territory_code = aav.country
AND aav.address_id = hcsu.cust_acct_site_id
AND hcasa.status = 'A'
AND hca.attribute20 = NVL (1234, hca.attribute20)
AND hcsu.primary_flag = 'Y'
-- AND (hcp.email_address IS NULL AND hcp.contact_point_type NOT IN ('EMAIL','PHONE'))
ORDER BY customer_num;
Regards,
SG