NULL - Table Records Fetch - HZ_CONTACT_POINTS

User_FHYJ0

Hello All,

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - ProductionNLSRTL 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

Paulzip

You need to include the 'EMAIL' as part of the left join.  Maybe something like this...

If you posted table creation and insert scripts it would be easy to test.

1. SELECT acv.customer_number, hcp.* 
2.     FROM apps.ar_customers_v acv 
3.         JOIN apps.hz_cust_acct_sites_all hcasa 
4.             ON hcasa.cust_account_id = acv.customer_id 
5.         JOIN apps.hz_party_sites hps 
6.             ON hcasa.party_site_id = hps.party_site_id 
7.         JOIN apps.hz_cust_site_uses_all hcsu 
8.             ON hcasa.cust_acct_site_id = hcsu.cust_acct_site_id 
9.         LEFT OUTER JOIN apps.hz_contact_points hcp 
10.             ON    hps.party_site_id = hcp.owner_table_id and hcp.contact_point_type = 'EMAIL'
    
11.   WHERE    1 = 1 
12.         AND hcsu.site_use_code = 'BILL_TO' 
13.         AND hcsu.primary_flag = 'Y' 
14.         AND hcsu.status = 'A' 
15.         AND acv.status = 'A' 
16.         AND hcasa.status = 'A' 
17.         AND hps.status = 'A' 
18.         AND acv.attribute20 = NVL (1234, acv.attribute20) 
19.         AND hcp.contact_point_type is null
20.         AND acv.customer_number IN (664402, 
21.                                     664403, 
22.                                     664404, 
23.                                     664405, 
24.                                     664406, 
25.                                     664407) 
26. ORDER BY acv.customer_number; 

BEDE

AND (hcp.email_address IS NULL AND hcp.contact_point_type NOT IN ('EMAIL','PHONE'))  is wrong as condition. If there is no corresponding record in apps.hz_contact_points, then hcp.contact_point_type will be null, meaning that it will not be IN ('EMAIL','PHONE')). I suppose an or should be used instead of and.

User_FHYJ0

Hi Bede,

Thanks for your reply.

If i include OR condition, then none of the records are returned even though there are lot of Customers for which E-Mail address is NULL in HZ_CONTACT_POINTS table.

AND (hcp.email_address IS NULL OR hcp.contact_point_type NOT IN ('EMAIL','PHONE'))

BEDE

So, it it that you actually want all the customers that do not have e-mail? And, if so, then why join with  apps.hz_contact_points for e-mail? You should use a not exists or not in to get those for which there is no e-mail addtess in apps.hz_contact_points. I'm not sure if the join conditions are right, but I think it should be something like below:

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 not exists (select 1

               from apps.hz_contact_points hcp2

               where  hps.party_site_id = hcp2.owner_table_id

                   and hcp2.email_address IS NULL

               )
ORDER BY customer_num; 

User_FHYJ0

Yes, I want all the Customers for which E-Mail address is not available in HZ_CONTACT_POINTS table. (which means during Customer Creation, E-Mail was not provided.)

I have created 3 customers which does not have E-Mail address in the table.

Actually for the Customer there is a Party Site ID in hz_party_sites, whereas there is No record available in HZ_CONTACT_POINTS since no e-mail is provided. Hence it is NULL which is why i used Outer join.

AND hps.party_site_id = hcp.owner_table_id ( + )

But the below query did not fetch those 3 new records in the query.

  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 NOT EXISTS                (SELECT 1                  FROM apps.hz_contact_points hcp2                  WHERE    hps.party_site_id = hcp2.owner_table_id                        AND hcp2.email_address IS NULL                        )ORDER BY customer_num;

BEDE

So, some joins are wrong...  Start rewriting the query adding the joins one by one. So you will see where you have something that should not be joined or should be outer joined. Why don't you use the ANSI syntax instead of the old Oracle syntax? ANSI syntax leads to queries where the join conditions are easier to follow.

User_FHYJ0

Hi Bede,

Yes, i have split the query as below.

I have created 3 Customers, Customer Numbers are 664402,664403 and 664404

SELECT * FROM apps.ar_customers_v acv

WHERE  customer_number in(664402,664403,664404);

SELECT hps.party_site_id

  FROM apps.hz_cust_acct_sites_all hcasa, apps.hz_party_sites hps,apps.hz_cust_site_uses_all hcsu

WHERE    hcasa.party_site_id = hps.party_site_id

        AND hcsu.site_use_code = 'BILL_TO'

        AND hcsu.status = 'A'

        AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id

        AND hcasa.cust_account_id IN (59385711,59385712,59385713);

SELECT *

FROM apps.hz_contact_points hcp

WHERE owner_table_id in (SELECT hps.party_site_id

  FROM apps.hz_cust_acct_sites_all hcasa, apps.hz_party_sites hps,apps.hz_cust_site_uses_all hcsu

WHERE    hcasa.party_site_id = hps.party_site_id

        AND hcsu.site_use_code = 'BILL_TO'

        AND hcsu.status = 'A'

        AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id

        AND hcasa.cust_account_id IN (59385711,59385712,59385713)

      );

In this case, there are 3 records for contact point type - PHONE since i have provided the Telephone numbers not for E-MAIL.

I would require such Customers for which there is no entry for EMAIL in CONTACT_POINT_TYPE of HZ_CONTACT_POINTS table.

Thanks

Paulzip

If I've understood you correctly, you really need to start using modern ANSI joins, rather than SQL 89 Oracle joins.

Then your problem is a simple left join on

1. Left join on hcp2.owner_table_id AND hcp2.email_address = 'E-Mail'

And in the where clause, the predicate to make an anti join check

hcp2.email_address is null

If you can have multiple, use a where not exists

User_FHYJ0

Hi Bede,

Below are the Customers created.

664402 - Got Phone number, No Email.

664403 - Got Phone number, No Email.

664404 - Got Phone number, No Email.

664405 - No Phone number,  No Email.

664406 - No Phone number,  No Email.

664407 - Got Phone number, Got Email.

Expected Output:

664402

664403

664404

664405

664406

There will be 2 records for  (664407)  in HZ_CONTACT_POINTS; 1 with Contact_Point_Type as PHONE and Other record with 'EMAIL' type.

I have framed joining the above tables. But 664407 should not be fetched, but is fetched as well since there is an entry for PHONE ignoring EMAIL record. Even this should not fetched, since it belongs to same Customer.

SELECT acv.customer_number,hcp.*FROM    apps.ar_customers_v acv,        apps.hz_cust_acct_sites_all hcasa,        apps.hz_party_sites hps,        apps.hz_cust_site_uses_all hcsu,        apps.hz_contact_points hcpWHERE  hcasa.cust_account_id = acv.customer_id        AND hcasa.party_site_id = hps.party_site_id        AND hcsu.site_use_code = 'BILL_TO'        AND hcsu.primary_flag = 'Y'        AND hcsu.status = 'A'        AND acv.status = 'A'        AND hcasa.status = 'A'        AND hps.status = 'A'        AND hcasa.cust_acct_site_id = hcsu.cust_acct_site_id        AND hps.party_site_id = hcp.owner_table_id(+)        AND acv.attribute20 = NVL (1234, acv.attribute20)        AND NVL(hcp.contact_point_type,'XXX') <>NVL('EMAIL','XXX')--        AND NVL(hcp.contact_point_type,'XXX') <>NVL('PHONE','XXX')        AND acv.customer_number  in(664402,664403,664404,664405,664406,664407)        order by acv.customer_number;

User_FHYJ0

Thanks Paul for your reply.

Let me modify the query to ANSI and check for expected results.

User_FHYJ0

Hi Paul,

I have modified the query as below:

  SELECT acv.customer_number, hcp.*    FROM apps.ar_customers_v acv        JOIN apps.hz_cust_acct_sites_all hcasa            ON hcasa.cust_account_id = acv.customer_id        JOIN apps.hz_party_sites hps            ON hcasa.party_site_id = hps.party_site_id        JOIN apps.hz_cust_site_uses_all hcsu            ON hcasa.cust_acct_site_id = hcsu.cust_acct_site_id        LEFT OUTER JOIN apps.hz_contact_points hcp            ON    hps.party_site_id = hcp.owner_table_id  WHERE    1 = 1        AND hcsu.site_use_code = 'BILL_TO'        AND hcsu.primary_flag = 'Y'        AND hcsu.status = 'A'        AND acv.status = 'A'        AND hcasa.status = 'A'        AND hps.status = 'A'        AND acv.attribute20 = NVL (1234, acv.attribute20)        AND NVL (hcp.contact_point_type, 'XXX') <> NVL ('EMAIL', 'XXX')        AND acv.customer_number IN (664402,                                    664403,                                    664404,                                    664405,                                    664406,                                    664407)ORDER BY acv.customer_number;

I am still receiving the same results. Please correct me. Thanks!