TCA Analyzer Data Integrity: Site Use Flags in HZ_CUST_ACCT_SITES_ALL Should Match Those Set In HZ_CUST_SITE_USES_AL (Doc ID 2628271.1)

Last updated on MAY 20, 2024

Applies to:

Symptoms

The tables HZ_CUST_ACCT_SITES_ALL and HZ_CUST_SITE_USES_ALL do not have matching values for the Site Use Flags.
This causes problems with updates and transaction and payment posting.

HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG should match the settings in HZ_CUST_SITE_USES_ALL.SHIP_TO_FLAG and BILL_TO_FLAG.

HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG valid values are  Y (Yes) and N (No).
HZ_CUST_ACCT_SITES_ALL.SHIP_TO_FLAG and BILL_TO_FLAG valid values are P (Primary) and Y (Yes) indicating that the site use exists for the site (cust_acct_site_id). 
NULL is the setting for BILL_TO_FLAG and SHIP_TO_FLAG when the site use does not exist for the address (cust_acct_site_id) or when the address status (hz_cust_acct_sites_all.status) is Inactive.

There may be one primary site use of each type for each account (cust_account_id) per org_id.   One Primary Bill_To, Ship_to, Marketing, Dunning and so forth for each site_use_code per Org_id.

When using the UI the application automatically sets the first unique site use as Primary for each Org_Id when created.
HZ_CUST_ACCT_SITES_ALL only indicates BILL_TO and SHIP_TO as Primary or Not.  HZ_CUST_SITE_USES_ALL has Primary flags indicated for each site_use_code.

Example:

HZ_CUST_ACCT_SITES_ALL
cust_acct_id  cust_acct_site_id  status  bill_to_flag  ship_to_flag  org_id
123               4556                  A           P                               204
123               9238                  A                           Y               204

123               4556                  A           P              P               450

HZ_CUST_SITE_USES_ALL
site_use_id   cust_acct_site_id  site_use_code primary_flag Status  org_id
2894             4556                 BILL_TO           Y               A        204
2895             9238                 SHIP_TO          N               A        204

2950             4556                 BILL_TO           Y               A        450
2951             4556                 SHiP_TO           Y               A        450

When using APIs to create or update data in the account address tables, the API data must be written carefully as to keep the data in sync between the two tables.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.