Monday, October 3, 2011

Status of a customer

One of my clients who run the Finance modules (AR, ARP, GL etc), Service Contracts and Field Service in E-Business Suite R12 had some issues with customers who were inactive.
The issue arised when they were creating Service Requests (through API cs_servicerequest_pub.Create_ServiceRequest) and got an error reported by the API that the request could  not be created due to the customer was invalid.

The reason that a customer is invalid can vary but one reason is that some level of the customer is inactive. In AR and the TCA model the customer (party) status can be set in the following tables:

  • hz_parties (TCA)
  • hz_party_sites (TCA)
  • hz_party_site_uses (TCA)
  • hz_cust_accounts (TCA-AR)
  • hz_cust_acct_sites_all (TCA-AR)
  • hz_cust_site_uses_all (TCA-AR)
In my clients case status was Active in all tables except hz_party_site_uses. Below I will explain how it's possible to change the statuses.

Set status in Account Receivables

If you inactivate an Account Site Address/Account Site Details the status will be set to I (Inactive) in hz_party_siteshz_party_site_useshz_cust_acct_sites_all and hz_cust_site_uses_all.


By using the Restore icon on an adress the status will be set to A (Active) in hz_party_sites and hz_cust_acct_sites_all. Note that the purpose level (hz_party_site_uses and hz_cust_site_uses_all) is still Inactive and need to be reactivated manually.

When a Business Purpose is restored the status will be set to A (Active) in hz_cust_site_uses_all and hz_party_site_uses. In hz_party_site_uses a new row is created with status A.

The strange thing is that if we look in Service (Contact Center), we now have one Active and one Inactive usage for Bill To.

The table hz_party_site_uses is version controlled and the row that have status A have object_version_number 1 (not 2 as expected).


To inactivate the account level in AR open the Account (in the Customer form) and change the status.


Set status in Service
To activate/inactivate an account, site or purpose in Service, use responsibility Field Service Manager and open Contact Center, go to tab Addresses. You need to check Include Inactive to show the inactive sites and purposes.


If you inactivate an address the status will be set to I (Inactive) in hz_party_sites, hz_party_site_uses, hz_cust_acct_sites_all and hz_cust_site_uses_all.

If you activate an address the status will be set to A (Active) in hz_party_sites and hz_cust_acct_sites_all. Note that the purpose level (hz_party_site_uses and hz_cust_site_uses_all) is still Inactive and need to be reactivated manually.

To inactivate a account go to tab Accounts.


Set status on party level
If you want to inactivate the Party level (hz_parties) you need to use the responsibility Oracle Customers Data Librarian Superuser and Navigate: Data Quality -> Party Maintenance tab.


The query below can be helpful if you want to check the statuses in the database.
SELECT hp.party_id,
       hp.party_name,
       hp.party_number,
       hps.party_site_id,
       hcas.org_id,
       hpsu.site_use_type,                
       hps.party_site_number,
       hp.status hz_parties,
       hps.status hz_party_sites,
       hpsu.status hz_party_site_uses,                 
       hcsu.site_use_code,                              
       ca.status hz_cust_accounts,
       hcas.status hz_cust_acct_sites_all,                
       hcsu.status hz_cust_site_uses_all
FROM hz_parties hp,
     hz_party_sites hps,
     hz_party_site_uses hpsu,
     hz_cust_accounts ca,
     hz_cust_acct_sites_all hcas,
     hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND   hp.party_id = ca.party_id
AND   hps.party_site_id = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hps.party_site_id = hpsu.party_site_id 
AND   hcsu.site_use_code = hpsu.site_use_type
AND   hp.party_id = party_id
AND   hcas.org_id = org_id;

No comments:

Post a Comment

javascript:void(0)