Skip to Main Content

SQL & PL/SQL

NULL - Table Records Fetch - HZ_CONTACT_POINTS

User_FHYJ0May 4 2020 — edited May 4 2020

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

This post has been answered by Paulzip on May 4 2020
Jump to Answer
Comments
Post Details
Added on May 4 2020
18 comments
1,199 views