TCA Module: Table HZ_ORGANIZATION_PROFILES has multiple ACTIVE records for a single party

In our test instance (and in production instance too) we found that there few many cases where a party has multiple active records in table hz_organization_profiles. When party information is updated via Customer interface or UI, the existing record in hz_organization_profiles is end dated and new record is created. (If a party is created and updated on same day then new record in hz_organization_profile is not created and existing record is simply updated.). I was not able to simulate this issue. When I searched oracle support, I found following note

Trading Community: GDF: Ensure HZ_Organization_Profiles Have Appropriate Effective_End_Date Values and Contain No Over-Lapping Date Ranges (Doc ID 2260595.1)

This note provides a data fix script but does not offer permanent solution. I think this table is not used much in Oracle application that is why this bug is not fixed in last many years. I have seen this issue sometime in year 2012.

Effective end date column of this table too is not being updated properly. When a new record for a party is added to this table hz_organization_profiles, existing record is updated with effective_end_date trunc(sysdate - 1) and new record have effective_start_date as sysdate. For example if you update an organization at 11/06/2017 3:43pm then old record in hz_organization_profiles will have effective end date as 11/05/2017 00:00:00 and new record will have effective start date as 11/06/2017 15:43:00. So there was no active record for organization between 11/05/2017 00:00:00 and 11/06/2017 15:42:59. If Oracle wants to use time component then use in both effective start and end dates. This pains me to see how careless is Oracle AR application development.

Customer's Phone, Email and URL

Contact point such as Phone, Email or UL can be attached to a customer at multiple levels such as Customer itself, Customer's Address or through a customer's contact. Today again i had to write queries to extract customer contact information, so i added these query here.

Customer contacts and their phone, email, URL

select account_number "Account Number" , obj.party_name "Customer Name" , sub.party_name "Contact Name" , hcp.contact_point_type || ': ' || DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number , 'WEB' , hcp.url , 'Unknow contact Point Type ' || hcp.contact_point_type ) "How to Contact" from apps.hz_cust_accounts hca , apps.hz_parties obj , apps.hz_relationships rel , apps.hz_contact_points hcp , apps.hz_parties sub where hca.party_id = rel.object_id and hca.party_id = obj.party_id and rel.subject_id = sub.party_id and rel.relationship_type = 'CONTACT' and rel.directional_flag = 'F' and rel.party_id = hcp.owner_table_id and hcp.owner_table_name = 'HZ_PARTIES' ;

Customer's phone, email and URL attached to customer directly.
select account_number "Account Number" , hp.party_name "Customer Name" , hcp.contact_point_type || ': ' || DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number , 'WEB' , hcp.url , 'Unknow contact Point Type ' || hcp.contact_point_type ) "How to Contact" from apps.hz_cust_accounts hca , apps.hz_parties hp , apps.hz_contact_points hcp where hca.party_id = hp.party_id and hp.party_id = hcp.owner_table_id and hcp.owner_table_name = 'HZ_PARTIES' ;

Phone, Email and URL attached to customer's address

select account_number "Account Number" , hp.party_name "Customer Name" , loc.address1 || decode(loc.address2, null, null, ', ' || loc.address2) || decode(loc.address3, null, null, ', ' || loc.address3) || decode(loc.address4, null, null, ', ' || loc.address4) || ' ' || loc.city || ', ' || loc.state || ' ' || loc.postal_code "Address" , hcp.contact_point_type || ': ' || DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number , 'WEB' , hcp.url , 'Unknow contact Point Type ' || hcp.contact_point_type ) "How to Contact" from apps.hz_cust_accounts hca , apps.hz_parties hp , apps.hz_cust_acct_sites_all cas , apps.hz_contact_points hcp , apps.hz_party_sites hps , apps.hz_locations loc where hca.cust_account_id = cas.cust_account_id and hca.party_id = hp.party_id and hcp.owner_table_id = cas.party_site_id and hcp.owner_table_name = 'HZ_PARTY_SITES' and cas.party_site_id = hps.party_site_id and hps.location_id = loc.location_id ;

Example of Creating Customers using Customer Interface (RACUST)

This should work in Oracle Application Release 11.5.9 to Rel 12.
All Records are linked using ORIG_SYSTEM....REF Columns

   --
   -- Insert record into customer interface table
   --
   INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL
   ( ADDRESS1                                                             
   , ORIG_SYSTEM_CUSTOMER_REF               
   , ORIG_SYSTEM_ADDRESS_REF                
   , SITE_USE_CODE                          
   , INSERT_UPDATE_FLAG                     
   , CUSTOMER_NAME                          
   , CUSTOMER_NUMBER                        
   , PERSON_FLAG                                                 
   , CUSTOMER_STATUS                        
   , PRIMARY_SITE_USE_FLAG                  
   , CITY                                   
   , STATE                                                                
   , POSTAL_CODE                            
   , COUNTRY                                
   , COUNTY                  
   , JGZZ_FISCAL_CODE                                                           
   , ORG_ID                                 
   , LAST_UPDATE_DATE       
   , LAST_UPDATED_BY        
   , CREATION_DATE          
   , CREATED_BY                               
   )
   VALUES
   ( '3250 Prem Nagar'  -- ADDRESS1                                                             
   , 'SKM_TEST_03'      -- ORIG_SYSTEM_CUSTOMER_REF               
   , 'SKM_TEST_03'      -- ORIG_SYSTEM_ADDRESS_REF                
   , 'BILL_TO'          -- SITE_USE_CODE                          
   , 'I'                -- INSERT_UPDATE_FLAG                     
   , 'WINNIE POOH'      -- CUSTOMER_NAME                          
   , 'SKM-03'           -- CUSTOMER_NUMBER                        
   , 'N'                -- PERSON_FLAG                                    
   , 'A'                -- CUSTOMER_STATUS                        
   , 'Y'                -- PRIMARY_SITE_USE_FLAG                  
   , 'SAVAGE'           -- CITY                                   
   , 'MN'               -- STATE                                                   
   , 55400              -- POSTAL_CODE                            
   , 'US'               -- COUNTRY                                
   , 'SCOTT'            -- COUNTY                   
   , '21-3456789'       -- JGZZ_FISCAL_CODE                                     
   , 1                  -- ORG_ID                                 
   , SYSDATE            -- LAST_UPDATE_DATE       
   , 4670               -- LAST_UPDATED_BY        
   , SYSDATE            -- CREATION_DATE          
   , 4670               -- CREATED_BY                               
   )

   --
   -- Insert records inot profile interface table
   --
   INSERT into ar.ra_customer_profiles_int_all 
   ( ORIG_SYSTEM_CUSTOMER_REF             
   , INSERT_UPDATE_FLAG             
   , ORG_ID          
   , CREDIT_HOLD                       
   , CUSTOMER_PROFILE_CLASS_NAME
   , LAST_UPDATE_DATE       
   , LAST_UPDATED_BY        
   , CREATION_DATE          
   , CREATED_BY                               
   )
   VALUES
   ( 'SKM_TEST_03'  -- ORIG_SYSTEM_CUSTOMER_REF             
   , 'I'            -- INSERT_UPDATE_FLAG             
   , 1              -- ORG_ID          
   , 'N'            -- CREDIT_HOLD                       
   , 'DEFAULT'      -- CUSTOMER_PROFILE_CLASS_NAME
   , SYSDATE        -- LAST_UPDATE_DATE               
   , 4670           -- LAST_UPDATED_BY                
   , SYSDATE        -- CREATION_DATE                  
   , 4670           -- CREATED_BY                                                 
   )

   --
   -- Insert record into telephone interface table
   --
   INSERT INTO ar.ra_contact_phones_int_all
   ( ORIG_SYSTEM_CUSTOMER_REF       
   , ORIG_SYSTEM_ADDRESS_REF        
   , ORIG_SYSTEM_CONTACT_REF        
   , ORIG_SYSTEM_TELEPHONE_REF      
   , INSERT_UPDATE_FLAG             
   , CONTACT_FIRST_NAME             
   , CONTACT_LAST_NAME                            
   , TELEPHONE                                 
   , TELEPHONE_AREA_CODE            
   , TELEPHONE_TYPE                              
   , LAST_UPDATE_DATE               
   , LAST_UPDATED_BY                
   , CREATION_DATE                  
   , CREATED_BY                     
   , ORG_ID
   , CONTACT_POINT_TYPE
   )
   VALUES
   ( 'SKM_TEST_03'  --ORIG_SYSTEM_CUSTOMER_REF       
   , 'SKM_TEST_03'  --ORIG_SYSTEM_ADDRESS_REF        
   , 'SKM_TEST_03a' --ORIG_SYSTEM_CONTACT_REF        
   , 'SKM_TEST_03a' --ORIG_SYSTEM_TELEPHONE_REF      
   , 'I'            --INSERT_UPDATE_FLAG             
   , 'LICKEY'       --CONTACT_FIRST_NAME             
   , 'LOOSE'        --CONTACT_LAST_NAME                           
   , '123-4756'     --TELEPHONE                                
   , '612'          --TELEPHONE_AREA_CODE            
   , 'GEN'          --TELEPHONE_TYPE                              
   , SYSDATE        --LAST_UPDATE_DATE               
   , 4670           --LAST_UPDATED_BY                
   , SYSDATE        --CREATION_DATE                  
   , 4670           --CREATED_BY                     
   , 1              --ORG_ID
   , 'PHONE'        --CONTACT_POINT_TYPE
   )

   --
   -- Insert record into telephone interface table.
   -- This will be second contact for the customer
   --
   INSERT INTO ar.ra_contact_phones_int_all
   ( ORIG_SYSTEM_CUSTOMER_REF       
   , ORIG_SYSTEM_ADDRESS_REF        
   , ORIG_SYSTEM_CONTACT_REF        
   , ORIG_SYSTEM_TELEPHONE_REF      
   , INSERT_UPDATE_FLAG             
   , CONTACT_FIRST_NAME             
   , CONTACT_LAST_NAME                            
   , TELEPHONE                                 
   , TELEPHONE_AREA_CODE            
   , TELEPHONE_TYPE                              
   , LAST_UPDATE_DATE               
   , LAST_UPDATED_BY                
   , CREATION_DATE                  
   , CREATED_BY                     
   , ORG_ID
   , CONTACT_POINT_TYPE
   )
   VALUES
   ( 'SKM_TEST_03'  -- ORIG_SYSTEM_CUSTOMER_REF       
   , 'SKM_TEST_03'  -- ORIG_SYSTEM_ADDRESS_REF        
   , 'SKM_TEST_03b' -- ORIG_SYSTEM_CONTACT_REF        
   , 'SKM_TEST_03b' -- ORIG_SYSTEM_TELEPHONE_REF      
   , 'I'            -- INSERT_UPDATE_FLAG             
   , 'LICKEY'       -- CONTACT_FIRST_NAME             
   , 'LOOSE TWO'    -- CONTACT_LAST_NAME                           
   , '123-4756'     -- TELEPHONE                                
   , '512'          -- TELEPHONE_AREA_CODE            
   , 'GEN'          -- TELEPHONE_TYPE                              
   , SYSDATE        -- LAST_UPDATE_DATE               
   , 4670           -- LAST_UPDATED_BY                
   , SYSDATE        -- CREATION_DATE                  
   , 4670           -- CREATED_BY                     
   , 1              -- ORG_ID
   , 'PHONE'        -- CONTACT_POINT_TYPE
   )

   --
   -- This wil add email for contact LICKEY LOSOE
   --
   INSERT INTO ar.ra_contact_phones_int_all
   ( ORIG_SYSTEM_CUSTOMER_REF       
   , ORIG_SYSTEM_ADDRESS_REF        
   , ORIG_SYSTEM_CONTACT_REF        
   , ORIG_SYSTEM_TELEPHONE_REF      
   , INSERT_UPDATE_FLAG             
   , CONTACT_FIRST_NAME             
   , CONTACT_LAST_NAME                            
   , EMAIL_ADDRESS                             
   , LAST_UPDATE_DATE               
   , LAST_UPDATED_BY                
   , CREATION_DATE                  
   , CREATED_BY                     
   , ORG_ID
   , CONTACT_POINT_TYPE
   )
   VALUES
   ( 'SKM_TEST_03'   -- ORIG_SYSTEM_CUSTOMER_REF       
   , 'SKM_TEST_03'   -- ORIG_SYSTEM_ADDRESS_REF        
   , 'SKM_TEST_03b'  -- ORIG_SYSTEM_CONTACT_REF        
   , 'SKM_TEST_03be' -- ORIG_SYSTEM_TELEPHONE_REF      
   , 'I'             -- INSERT_UPDATE_FLAG             
   , 'LICKEY'        -- CONTACT_FIRST_NAME             
   , 'LOOSE TWO'     -- CONTACT_LAST_NAME                                     
   , 'a.b@c.com'     -- EMAIL_ADDRESS
   , SYSDATE         -- LAST_UPDATE_DATE               
   , 4670            -- LAST_UPDATED_BY                
   , SYSDATE         -- CREATION_DATE                  
   , 4670            -- CREATED_BY                     
   , 1               -- ORG_ID
   , 'EMAIL'         -- CONTACT_POINT_TYPE
   )

HZ_PERSON_PROFILES: Overlapping Start and End Dates

At our site we noticed that some of the person profile records have overlapping start and end dates. This causes problem in some reports such as adjustment register. The query given below can provide list of all such parties that have overlapping record.

SELECT pp_out.party_id
     , (SELECT account_number
          FROM ar.hz_cust_accounts ca
         WHERE ca.party_id = pp_out.party_id
       ) acct_num
     , ( SELECT pty.party_type || ',' || pty.orig_system_reference
           FROM ar.hz_parties pty
          WHERE pty.party_id = pp_out.party_id
       ) party_type
     , pp_out.person_profile_id
     , pp_out.creation_date
     , pp_out.effective_start_date
     , pp_out.effective_end_date, pp_out.* 
  FROM ar.hz_person_profiles pp_out 
 WHERE pp_out.party_id IN 
       ( SELECT party_id
           FROM ar.hz_person_profiles pp 
          WHERE EXISTS
          ( SELECT 1
              FROM ar.hz_person_profiles pp_in 
             WHERE pp_in.party_id = pp.party_id 
               AND pp_in.person_profile_id <> pp.person_profile_id 
               AND ( pp.effective_start_date 
                       BETWEEN pp_in.effective_start_date
                           AND NVL(pp_in.effective_end_date,SYSDATE+100) OR 
                     NVL(pp.effective_end_date,SYSDATE)
                       BETWEEN pp_in.effective_start_date
                           AND NVL(pp_in.effective_end_date,SYSDATE+100) 
                   ) 
          ) 
       ) 
 ORDER BY pp_out.party_id DESC
     , pp_out.creation_date

This problem was noticed in Oracle application version 11.5.9 and it still exists to some extent in Oracle Application Rel 12.

DQM and Customer Interface

If DQM is enabled and Customer Interface is used for creating or updating customers then those records can not be queried using 'Customer Standard'. Users need to run concurrent program "DQM Synchronization Program". This program moves customer information from HZ_DQM_SYNC_INTERFACE table to staged tables. List of staging tables is giveb below

1. HZ_STAGED_CONTACTS


2. HZ_STAGED_CONTACT_POINTS


3. HZ_STAGED_PARTIES


4. HZ_STAGED_PARTY_SITES

After running concurrent program "DQM Synchronization Program", user should run concurrent program
"DQM Parallel Sync Index Parent Program". This program is submitted automatically when a user updates any customer
information using customer standard or HZ APIs.

Customer Interface Does Not Populate Late Charges Fields From Profile Class

At our site, when we import customers using customer interface, we encountered this issue. At present Oracle offers only a work around. The work around is to use TCA APIs to update customer profile of affected customer. The similar issue is applicable to Profile amounts too. So we did the following

1. Find customers that are affected by this bug
2. Use following code to update customer's profile

  .......
  .......
  FOR l_profiles IN c_profiles
  LOOP
     l_prof_rec.cust_account_profile_id := l_profiles.cust_account_profile_id;
     l_prof_rec.profile_class_id        := l_profiles.profile_class_id;
     l_obj_version                      := l_profiles.object_version_number;

     HZ_CUSTOMER_PROFILE_V2PUB.update_customer_profile
     ( p_init_msg_list          => FND_API.G_TRUE
     , p_customer_profile_rec   => l_prof_rec
     , p_object_version_number  => l_obj_version
     , x_return_status          => l_ret_status
     , x_msg_count              => l_msg_count
     , x_msg_data               => l_msg_data
     ) ;
     IF l_ret_status <> FND_API.G_RET_STS_SUCCESS
     THEN
        l_errors := 1;
        IF l_msg_count >= 1
        THEN
           FOR l_count IN 1..l_msg_count
           LOOP
             fnd_msg_pub.get(l_count, 'F', l_msg_data, l_msg_index);
             write_msg('Error While Updating Profile, cust_account_profile_id = ' ||l_profiles.cust_account_profile_id
                        || ', Error: '
                        ||  l_msg_data
                      ) ;
           END LOOP;
        ELSE
           write_msg('Undocumented error from Update customer Profile API is: '
                     || l_ret_status
                     || ' MSG COUNT IS '
                     || l_msg_count
                     || ' MESSAGE IS '
                     || l_msg_data
                   );
        END IF;
     END IF;  -- END RETURN NOT SUCCESS
  END LOOP;
  .......
  .......

The procedure HZ_CUSTOMER_PROFILE_V2PUB.update_customer_profile calls do_update_customer_profile and that in turn calls HZ_CUSTOMER_PROFILES_PKG.update_row. This procedure checks if profile_class_id is passed or not. If profile class id is passed and a particular profile attribute in HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE is NULL then it is replaced with the value from HZ_PROFILE_CLASSES.



Similar fix is needed for customer Profile amounts too. Code similar to given below could be used for fixing profile amounts.

  .......
  .......
  FOR l_prof_amts IN c_prof_amts
  LOOP
     l_prof_amt_rec.cust_acct_profile_amt_id    := l_prof_amts.cust_acct_profile_amt_id;
     l_prof_amt_rec.min_fc_balance_overdue_type := 'AMOUNT';
     l_prof_amt_rec.min_fc_invoice_overdue_type := 'AMOUNT';
     l_prof_amt_rec.interest_type               := 'FIXED_RATE';
     l_prof_amt_rec.interest_rate               := l_prof_amts.interest_rate;
     l_prof_amt_rec.min_fc_balance_amount       := l_prof_amts.min_fc_balance_amount;
     l_prof_amt_rec.min_fc_invoice_amount       := l_prof_amts.min_fc_invoice_amount;
     l_obj_version                              := l_prof_amts.object_version_number;

     HZ_CUSTOMER_PROFILE_V2PUB.update_cust_profile_amt
     ( p_init_msg_list          => FND_API.G_TRUE
     , p_cust_profile_amt_rec   => l_prof_amt_rec
     , p_object_version_number  => l_obj_version
     , x_return_status          => l_ret_status
     , x_msg_count              => l_msg_count
     , x_msg_data               => l_msg_data
     ) ;
     IF l_ret_status <> FND_API.G_RET_STS_SUCCESS
     THEN
        l_errors := 1;
        IF l_msg_count >= 1
        THEN
           FOR l_count IN 1..l_msg_count
           LOOP
             fnd_msg_pub.get(l_count, 'F', l_msg_data, l_msg_index);
             write_msg('Error While Updating Profile, cust_acct_profile_amt_id = ' ||l_prof_amts.cust_acct_profile_amt_id
                        || ', Profile class:' || l_prof_amts.name
                        || ', Error: '
                        ||  l_msg_data
                      ) ;
           END LOOP;
        ELSE
           write_msg('Undocumented error from Update customer Profile API is: '
                     || l_ret_status
                     || ' MSG COUNT IS '
                     || l_msg_count
                     || ' MESSAGE IS '
                     || l_msg_data
                   );
        END IF;
     END IF;  -- END RETURN NOT SUCCESS
  END LOOP;
  .......
  .......

Oracle has created an enhancement bug for this issue. Bug number is 6790949.

TCA Module: Table Of Contents

1. Queries
   
   
   

   
   
   1. List of Customer Accounts having a Particular Email/Phone
   
   
   2. Customer's Addresses and their usage
   
   
   3. Available phones/emails/web for an account
   
   
   4. Customer's Bank Accounts
   
   
   5. Person's Credit Card Accounts (Rel12 Only)
   
   
   6. Duplicate Addresses
   
   
   7. List of Phones used by more than once customer account
   
   
   8. Parties that have only non primary Contact Points
   
   
   9. List of Customer Accounts that have 2 or more Identifying Addresses
   
   
   10. Customer Accounts that have Non Primary Phone Contact Points only
   
   

   
   
   
   


2. APIs
   
   
   
   1. HZ_CONTACT_POINT_V2PUB.update_contact_point Example
   
   
   2. HZ_PARTY_SITE_V2PUB.update_party_site Example
   
   
   
   

Person's Credit Card Accounts

This query can give details of all credit cards assigned to a person. This is applicable to Rel12.

SELECT party_name
     , party_number
     , ccnumber
     , instrument_type
     , chname "Name on the Card"
     , expirydate
     , finame
     , card_issuer_code
     , invalidation_reason
     , loc.address1
     , loc.address2
     , loc.city
     , loc.state
     , loc.postal_code
  FROM ar.hz_parties         pty
     , iby.iby_creditcard    cc
     , ar.hz_party_site_uses psu
     , ar.hz_party_sites     ps
     , ar.hz_locations       loc       
 WHERE pty.party_id      = :party_id
   AND pty.party_id      = cc.card_owner_id
   AND cc.addressid      = psu.party_site_use_id
   AND psu.party_site_id = ps.party_site_id
   AND ps.location_id    = loc.location_id

TCA API Example: HZ_CONTACT_POINT_V2PUB.update_contact_point

DECLARE
  l_phone_rec           HZ_CONTACT_POINT_V2PUB.phone_rec_type;
  l_email_rec           HZ_CONTACT_POINT_V2PUB.email_rec_type;
  l_contact_point_rec   HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
  
  l_contact_point_type VARCHAR2(100) := 'EMAIL' ;--'PHONE';
  l_primary_flag       VARCHAR2(1) := 'Y';
  l_area_code          HZ_CONTACT_POINTS.phone_area_code % TYPE := '953';
  l_phone_number       HZ_CONTACT_POINTS.phone_number % TYPE    := '9667255';
  l_email              HZ_CONTACT_POINTS.email_address % TYPE    := 's3@b.com';
  l_obj_num            NUMBER := 3;
  
  p_party_id           HZ_PARTIES.party_id % TYPE;
  x_msg_count          NUMBER;
  x_msg_data           VARCHAR2(2000);
  x_return_status      VARCHAR2(1000);
BEGIN
  
  l_email_rec.email_address     := l_email;

  l_phone_rec.phone_area_code   := l_area_code;
  l_phone_rec.phone_number      :=  l_phone_number;
  l_phone_rec.phone_line_type   := 'PHONE';
  
  l_contact_point_rec.contact_point_type  := l_contact_point_type;     --PHONE primary and secondary
  l_contact_point_rec.status              := 'A';
  l_contact_point_rec.owner_table_name    := 'HZ_PARTIES';
  l_contact_point_rec.owner_table_id      := p_party_id;
  l_contact_point_rec.primary_flag        := l_primary_flag;
  l_contact_point_rec.content_source_type := 'USER_ENTERED';
  l_contact_point_rec.contact_point_id    := 9645530; 
  
  HZ_CONTACT_POINT_V2PUB.update_contact_point
  ( p_init_msg_list          => fnd_api.g_false
  , p_contact_point_rec      => l_contact_point_rec
  , p_email_rec              => l_email_rec
  , p_phone_rec              => l_phone_rec
  , p_object_version_number  => l_obj_num
  , x_return_status          => x_return_status
  , x_msg_count              => x_msg_count
  , x_msg_data               => x_msg_data
  );
  dbms_output.put_line('Return Status :' || x_return_status);
  IF x_return_status <> 'S'
  THEN
     FOR k in 1 .. x_msg_count loop
       x_msg_data := fnd_msg_pub.get
                     ( p_msg_index => k
                     , p_encoded   => 'F'
                     ) ;
       dbms_output.put_line('Error:' || x_msg_data);
     END LOOP;      
  END IF;
END;

TCA API Example: hz_party_site_v2pub.update_party_site

This sample code updates identifying flag for the party site.

DECLARE
  l_party_site_rec hz_party_site_v2pub.PARTY_SITE_REC_TYPE;
  l_obj_num        NUMBER;
  l_return_status  VARCHAR2(1);
  l_msg_count      NUMBER;
  l_msg_data       VARCHAR2(2000);
BEGIN
  l_party_site_rec.party_site_id            := 10806693;
  l_party_site_rec.identifying_address_flag := 'Y';
  l_obj_num                                 := 1;
  
  hz_party_site_v2pub.update_party_site
  ( p_init_msg_list         =>  FND_API.G_FALSE
  , p_party_site_rec        =>  l_PARTY_SITE_REC
  , p_object_version_number => l_obj_num
  , x_return_status         => l_return_status
  , x_msg_count             => l_msg_count
  , x_msg_data              => l_msg_data
  ) ;
  dbms_output.put_line('Ret Status:' || l_return_status);
END;
/

Customer Accounts that have Non Primary Phone Contact Points only

I was asked to provide a list of customer accounts that have
1. No primary phones associated with the account
2. Have non-primary phone associated with the account

So I translated the given requirement into sql query as given below

SELECT account_number
  FROM hz_cust_accounts hca
 WHERE NOT EXISTS ( SELECT 1
                      FROM hz_contact_points hcp
                     WHERE contact_point_type = 'PHONE'
                       AND owner_table_name   = 'HZ_PARTIES'
                       AND owner_table_id     = hca.party_id
                       AND primary_flag       = 'Y'
                  )
   AND EXISTS     ( SELECT 1
                      FROM hz_contact_points hcp
                     WHERE contact_point_type    = 'PHONE'
                       AND owner_table_name      = 'HZ_PARTIES'
                       AND owner_table_id        = hca.party_id
                       AND NVL(primary_flag,'N') = 'N'
                  )

This query was very slow. There were not too many customer accounts that met the above condition. I thought about it and used the following query. It was much better.

SELECT /*+ use_nl ( phone ca) */
       account_number
  FROM hz_cust_accounts ca
     , (SELECT owner_table_id
          FROM ( SELECT owner_table_id
                      , SUM(DECODE(primary_flag,'Y',1,0)) primary_count
                      , SUM(DECODE(primary_flag,'Y',0,1)) non_primary_count
                   FROM hz_contact_points
                  WHERE status = 'A'
                    AND contact_point_type = 'PHONE'
                    AND owner_table_name   = 'HZ_PARTIES'
                  GROUP BY owner_table_id
               )
         WHERE primary_count = 0 AND non_primary_count > 0
       ) phone
  WHERE ca.party_id = phone.owner_table_id

I think this query can give better information about objects that have only non-primary phones

SELECT owner_table_name, owner_table_id
  FROM ( SELECT owner_table_name, owner_table_id
             , SUM(DECODE(primary_flag,'Y',1,0)) primary_count
             , SUM(DECODE(primary_flag,'Y',0,1)) non_primary_count
          FROM apps.hz_contact_points
         WHERE status = 'A'
           AND contact_point_type= 'PHONE'
         GROUP BY owner_table_name, owner_table_id
       )
 WHERE primary_count = 0 AND non_primary_count > 0

List of Customer Accounts that have 2 or more Identifying Addresses

Owing to some bug in 11.5.9, a customer can end up having two or more identifying addresses. This is incorrect and causes problem in some forms such as Oracle Service Request form. This query can identify such accounts and such accounts should be fixed using customer standard form.

SELECT /*+ use_nl(ps ca) */
       ca.account_number
     , cnt
     , ca.creation_date
     , ca.last_update_date
  FROM ( SELECT ps.party_id
              , COUNT(ps.party_id) cnt
           FROM hz_party_sites ps
          WHERE identifying_address_flag = 'Y'
          GROUP BY ps.party_id
         HAVING COUNT(ps.party_id) > 1 
       ) ps
     , hz_cust_accounts ca
 WHERE ps.party_id = ca.party_id

Parties that have only non primary Contact Points

At our site, all parties are suppose to have one primary contact point. But owning to some bug, some parties had only non primary contact points such as PHONE. The following queries can identify such parties

SELECT owner_table_id
  FROM ( SELECT owner_table_id
              , SUM(DECODE(primary_flag,'Y',1,0)) primary_count
              , SUM(DECODE(primary_flag,'Y',0,1)) non_primary_count
           FROM hz_contact_points
          WHERE status = 'A'
            AND contact_point_type= 'PHONE'
         GROUP BY owner_table_id
       )
 WHERE primary_count = 0 AND non_primary_count > 0

List of Phones used by more than one accounts

SELECT hcp_out.phone_area_code
     , hcp_out.phone_number
     , hca_out.account_number
     , party_name
  FROM hz_contact_points hcp_out
     , hz_cust_accounts  hca_out
     , hz_parties        party
     , ( SELECT hcp.phone_area_code
              , hcp.phone_number
           -- , COUNT(DISTINCT hca.account_number)
           FROM hz_contact_points hcp
              , hz_cust_accounts  hca
              , (SELECT hcp_in.phone_area_code
                      , hcp_in.phone_number
                   FROM hz_contact_points hcp_in
                  WHERE hcp_in.creation_date      >= TO_DATE('1-MAR-2008','DD-MON-YYYY')
                    AND hcp_in.creation_date      <  TO_DATE('1-APR-2008','DD-MON-YYYY')
                    AND hcp_in.owner_table_name   = 'HZ_PARTIES'
                    AND hcp_in.contact_point_type = 'PHONE'
                      ) new_phone
          WHERE hcp.phone_area_code = new_phone.phone_area_code
            AND hcp.phone_number    = new_phone.phone_number
            AND hca.party_id           = hcp.owner_table_id
          GROUP BY hcp.phone_area_code
                 , hcp.phone_number
         HAVING COUNT(DISTINCT hca.account_number) > 1
       ) dup_phone
  WHERE hcp_out.phone_area_code = dup_phone.phone_area_code
    AND hcp_out.phone_number    = dup_phone.phone_number
    AND hca_out.party_id        = hcp_out.owner_table_id
    AND party.party_id          = hca_out.party_id
  ORDER BY hcp_out.phone_area_code
         , hcp_out.phone_number
         , hca_out.account_number

List of Customer Accounts having a Particular Email/Phone

In this query, if you are looking for customer account for a particular email address, uncomment condition on email and comment condition of phone. This is needed for performance purpose only. It would have been better if query could perform well for search across email and phone.

SELECT account_number
      , 'Party LeveL ' || contact_point_type Communication_LeveL
      , phne.contact_point_purpose
      , phne.contact_point_type
      , decode(phne.contact_point_type, 'PHONE','('||phne.phone_area_code ||') ' ||phne.phone_number,
                                        'EMAIL', phne.email_address,
                                        'WEB'  , phne.url
              ) comm_detail
      , phne.primary_flag 
      , null   address
   FROM apps.hz_contact_points  phne
      , apps.hz_cust_accounts   acct
  WHERE acct.party_id         = phne.owner_table_id
    AND phne.owner_table_name = 'HZ_PARTIES'
 -- AND UPPER(phne.email_address) = 'EMAIL IN UPPER CASE'
    AND phne.phone_area_code = '952' AND phne.phone_number = '0007353'
  UNION
 SELECT account_number
      , 'Party Site LeveL ' || contact_point_type
      , phne.contact_point_purpose
      , phne.contact_point_type
      , decode(phne.contact_point_type, 'PHONE','('||phne.phone_area_code ||') ' ||phne.phone_number,
                                        'EMAIL', phne.email_address,
                                        'WEB'  , phne.url
              ) comm_detail
      , phne.primary_flag
      , loc.address1 || ',' ||loc.address2 || ',' ||loc.city || ',' ||loc.state || ',' ||loc.postal_code address
   FROM apps.hz_contact_points      phne
      , apps.hz_cust_acct_sites_all site
      , apps.hz_cust_accounts       acct
      , apps.hz_party_sites         psite
      , apps.hz_locations           loc
  WHERE site.cust_acct_site_id = phne.owner_table_id
    AND phne.owner_table_name  = 'HZ_PARTY_SITES'
    AND site.cust_account_id   = acct.cust_account_id
    AND site.party_site_id     = psite.party_site_id
    AND psite.location_id      = loc.location_id
--  AND UPPER(phne.email_address) = 'EMAIL IN UPPER CASE'
    AND phne.phone_area_code = '952' AND phne.phone_number = '0007353'

Customer's Addresses and their usage

SELECT acct.account_number
     , acct.status Account_Status
     , acct.account_established_date
     , acct.account_termination_date
     , acct.account_activation_date
     , party.party_name
     , party.status Party_Status
     , asite.status account_site_status
     , asite.bill_to_flag
     , asite.ship_to_flag
     , asite.customer_category_code
     , psite.party_site_name
     , psite.status Party_Site_Status
     , psite.start_date_active
     , psite.end_date_active
     , loc.address1 || ' ' || loc.address2 address
     , loc.city
     , loc.state
     , loc.postal_code
     , siteu.site_use_id
     , siteu.status Site_usage_status
     , siteu.site_use_code
     , siteu.primary_flag
     , siteu.status site_uses_status
  FROM apps.hz_cust_accounts         acct
     , apps.hz_cust_acct_sites_all   asite
     , apps.hz_party_sites           psite
     , apps.hz_locations             loc
     , apps.hz_cust_site_uses_all    siteu
     , apps.hz_parties               party
 WHERE acct.account_number        = :account_number /* in single quotes */
   AND asite.cust_account_id      = acct.cust_account_id
   AND asite.party_site_id        = psite.party_site_id
   AND psite.location_id          = loc.location_id
   AND siteu.cust_acct_site_id    = asite.cust_acct_site_id
   AND acct.party_id              = party.party_id

Available phones/emails/web for an account

SELECT account_number
      , 'Party LeveL Communication' Communication_LeveL
      , phne.contact_point_purpose
      , phne.contact_point_type
      , decode(phne.contact_point_type, 'PHONE','('||phne.phone_area_code ||') ' ||phne.phone_number,
                                        'EMAIL', phne.email_address,
                                        'WEB'  , phne.url
              ) comm_detail
      , phne.primary_flag 
      , null   address
   FROM hz_contact_points  phne
      , hz_cust_accounts   acct
  WHERE acct.party_id         = phne.owner_table_id
    AND phne.owner_table_name = 'HZ_PARTIES'
    AND acct.account_number   = '1000'
  UNION
 SELECT account_number
      , 'Party Site LeveL Communication' 
      , phne.contact_point_purpose
      , phne.contact_point_type
      , decode(phne.contact_point_type, 'PHONE','('||phne.phone_area_code ||') ' ||phne.phone_number,
                                        'EMAIL', phne.email_address,
                                        'WEB'  , phne.url
              ) comm_detail
      , phne.primary_flag
      , loc.address1 || ',' ||loc.address2 || ',' ||loc.city || ',' ||loc.state || ',' ||loc.postal_code address
   FROM hz_contact_points      phne
      , hz_cust_acct_sites_all site
      , hz_cust_accounts       acct
      , hz_party_sites         psite
      , hz_locations           loc
  WHERE site.cust_acct_site_id = phne.owner_table_id
    AND phne.owner_table_name  = 'HZ_PARTY_SITES'
    AND site.cust_account_id   = acct.cust_account_id
    AND site.party_site_id     = psite.party_site_id
    AND psite.location_id      = loc.location_id
    AND acct.account_number    = '1000'

Customer's Bank Accounts

SELECT acct.account_number
     , acct.status Account_Status
     , acct.account_established_date
     , acct.account_termination_date
     , acct.account_activation_date
     , asite.bill_to_flag
     , asite.ship_to_flag
     , asite.customer_category_code
     , psite.party_site_name
     , psite.status Party_Site_Status
     , psite.start_date_active
     , psite.end_date_active
     , loc.address1 || ' ' || loc.address2 address
     , loc.city
     , loc.state
     , loc.postal_code
     , siteu.site_use_code
     , siteu.primary_flag
     , siteu.status site_uses_status
     , banku.start_date
     , banku.end_date 
     , banku.primary_flag
     , bank.bank_account_num
     , bank.bank_account_name
     , bank.inactive_date
  FROM apps.hz_cust_accounts         acct
     , apps.hz_cust_acct_sites_all   asite
     , apps.hz_party_sites           psite
     , apps.hz_locations             loc
     , apps.hz_cust_site_uses_all    siteu
     , apps.ap_bank_account_uses_all banku
     , apps.ap_bank_accounts_all     bank
 WHERE acct.account_number        = :account_number -- in single quotes
   AND asite.cust_account_id      = acct.cust_account_id
   AND asite.party_site_id        = psite.party_site_id
   AND psite.location_id          = loc.location_id
   AND siteu.cust_acct_site_id    = asite.cust_acct_site_id
   AND banku.customer_site_use_id = siteu.site_use_id
   AND bank.bank_account_id       = banku.external_bank_account_id
 ORDER BY bank.bank_account_num

List of Duplicate Addresses

This query will find out addresses that used by at least 10 different parties.

SELECT acct.account_number
     , party.party_name
     , locations.address1
     , locations.address2
     , locations.city
     , locations.state
     , locations.postal_code
  FROM hz_party_sites psiteo
     , hz_cust_accounts acct
     , hz_parties party
     , (SELECT /*+ index ( loc HZ_LOCATIONS_N1 ) */ location_id 
             , loc.address1
             , loc.address2
             , loc.city
             , loc.state
             , loc.postal_code
             , parties
          FROM hz_locations loc
             , (SELECT loc.address1
                     , loc.address2
                     , loc.city
                     , loc.state
                     , COUNT(DISTINCT psite.party_id) parties
                  FROM hz_locations loc
                     , hz_party_sites psite
                 WHERE psite.location_id = loc.location_id
                 GROUP BY loc.address1
                        , loc.address2
                        , loc.city
                        , loc.state
                HAVING count(*) > 10
               ) addr
         WHERE loc.address1 = addr.address1
           AND loc.address2 = addr.address2
           AND loc.city = addr.city
           AND loc.state = addr.state
     ) locations
 WHERE psiteo.location_id = locations.location_id
   AND psiteo.party_id = party.party_id
   AND acct.party_id = party.party_id
 order by locations.postal_code
     , locations.state
     , locations.city
     , locations.address1
     , party.party_name
     , acct.account_number