HZ_PARTY_SITES: table used to store the site information in Oracle TCA ( trading community architecture) structure. Below is the table structure, index and constraints of the table.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
HZ_PARTY_SITES Table Structure
| COLUMN_NAME | DATA_TYPE | NULLABLE |
| PARTY_SITE_ID | NUMBER | N |
| PARTY_ID | NUMBER | N |
| LOCATION_ID | NUMBER | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | N |
| LAST_UPDATED_BY | VARCHAR2(256) | N |
| CREATION_DATE | TIMESTAMP(6) | N |
| CREATED_BY | VARCHAR2(256) | N |
| LAST_UPDATE_LOGIN | VARCHAR2(128) | Y |
| REQUEST_ID | NUMBER | Y |
| JOB_DEFINITION_NAME | VARCHAR2(400) | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2(3600) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(120) | Y |
| CURRENCY_CODE | VARCHAR2(60) | Y |
| CORP_CURRENCY_CODE | VARCHAR2(60) | Y |
| CURCY_CONV_RATE_TYPE | VARCHAR2(120) | Y |
| ATTRIBUTE1-30 | VARCHAR2(600) | Y |
| ORIG_SYSTEM_REFERENCE | VARCHAR2(960) | Y |
| START_DATE_ACTIVE | DATE | Y |
| END_DATE_ACTIVE | DATE | Y |
| PARTY_SITE_NUMBER | VARCHAR2(120) | N |
| MAILSTOP | VARCHAR2(240) | Y |
| IDENTIFYING_ADDRESS_FLAG | VARCHAR2(4) | N |
| STATUS | VARCHAR2(4) | N |
| PARTY_SITE_NAME | VARCHAR2(960) | Y |
| ADDRESSEE | VARCHAR2(1440) | Y |
| OBJECT_VERSION_NUMBER | NUMBER | N |
| CREATED_BY_MODULE | VARCHAR2(120) | Y |
| ACTUAL_CONTENT_SOURCE | VARCHAR2(120) | Y |
| GLOBAL_LOCATION_NUMBER | VARCHAR2(160) | Y |
| DUNS_NUMBER_C | VARCHAR2(120) | Y |
| PARTY_NAME_LEGAL | VARCHAR2(1440) | Y |
| PARTY_NAME_DBA | VARCHAR2(1440) | Y |
| PARTY_NAME_DIVISION | VARCHAR2(1440) | Y |
| RELATIONSHIP_ID | NUMBER | Y |
| PARTY_USAGE_CODE | VARCHAR2(120) | Y |
| PARTY_SITE_TYPE | VARCHAR2(120) | Y |
| COMMENTS | VARCHAR2(4000) | Y |
| CONFLICT_ID | NUMBER | N |
| USER_LAST_UPDATE_DATE | TIMESTAMP(6) | Y |
| CPDRF_VER_SOR | NUMBER | Y |
| CPDRF_VER_PILLAR | NUMBER | Y |
| CPDRF_LAST_UPD | VARCHAR2(60) | Y |
| ATTRIBUTE_NUMBER1-ATTRIBUTE_NUMBER12 | NUMBER | Y |
| ATTRIBUTE_DATE1- ATTRIBUTE_DATE12 | DATE | Y |
| PARTY_SITE_LANGUAGE | VARCHAR2(16) | Y |
| OVERALL_PRIMARY_FLAG | VARCHAR2(4) | Y |
| PERSONAL_ADDRESS_FLAG | VARCHAR2(4) | Y |
| EXTN_ATTRIBUTE_TIMESTAMP001 – EXTN_ATTRIBUTE_TIMESTAMP050 | TIMESTAMP(6) | Y |
| EXTN_ATTRIBUTE_NUMBER001 – EXTN_ATTRIBUTE_NUMBER200 | NUMBER | Y |
| EXTN_ATTRIBUTE_CHAR001 – EXTN_ATTRIBUTE_CHAR350 | VARCHAR2(4000) | Y |
Index :
index type N stands for Normal, F for Function Based
| INDEX_NAME | INDEX_TYPE | UNIQUENESS | COLUMN_NAME |
| HZ_PARTY_SITES_N1 | N | N | ORIG_SYSTEM_REFERENCE |
| HZ_PARTY_SITES_N2 | N | N | PARTY_ID |
| HZ_PARTY_SITES_N2 | N | N | IDENTIFYING_ADDRESS_FLAG |
| HZ_PARTY_SITES_N2 | N | N | START_DATE_ACTIVE |
| HZ_PARTY_SITES_N2 | N | N | OVERALL_PRIMARY_FLAG |
| HZ_PARTY_SITES_N3 | N | N | GLOBAL_LOCATION_NUMBER |
| HZ_PARTY_SITES_N4 | F | N | SYS_NC00724$ |
| HZ_PARTY_SITES_N4 | F | N | PARTY_SITE_ID |
| HZ_PARTY_SITES_N5 | N | N | LOCATION_ID |
| HZ_PARTY_SITES_N6 | N | N | RELATIONSHIP_ID |
| HZ_PARTY_SITES_N6 | N | N | PARTY_ID |
| HZ_PARTY_SITES_N6 | N | N | LOCATION_ID |
| HZ_PARTY_SITES_N6 | N | N | START_DATE_ACTIVE |
| HZ_PARTY_SITES_N6 | N | N | IDENTIFYING_ADDRESS_FLAG |
| HZ_PARTY_SITES_N6 | N | N | OVERALL_PRIMARY_FLAG |
| HZ_PARTY_SITES_N7 | F | N | SYS_NC00725$ |
| HZ_PARTY_SITES_N8 | N | N | REQUEST_ID |
| HZ_PARTY_SITES_PK | N | Y | PARTY_SITE_ID |
| HZ_PARTY_SITES_U1 | N | Y | PARTY_SITE_NUMBER |
| HZ_PARTY_SITES_U1 | N | Y | CONFLICT_ID |
| HZ_PARTY_SITES_X01 | N | N | EXTN_ATTRIBUTE_NUMBER001 |
| HZ_PARTY_SITES_X02 | N | N | EXTN_ATTRIBUTE_NUMBER002 |
| HZ_PARTY_SITES_X03 | N | N | EXTN_ATTRIBUTE_NUMBER003 |
| HZ_PARTY_SITES_X04 | N | N | EXTN_ATTRIBUTE_CHAR001 |
| HZ_PARTY_SITES_X05 | N | N | EXTN_ATTRIBUTE_CHAR002 |
| HZ_PARTY_SITES_X06 | N | N | EXTN_ATTRIBUTE_NUMBER015 |
| HZ_PARTY_SITES_X07 | N | N | EXTN_ATTRIBUTE_NUMBER016 |
| HZ_PARTY_SITES_X08 | N | N | EXTN_ATTRIBUTE_CHAR026 |
| HZ_PARTY_SITES_X09 | N | N | EXTN_ATTRIBUTE_CHAR027 |
| HZ_PARTY_SITES_X10 | N | N | EXTN_ATTRIBUTE_CHAR028 |
| HZ_PARTY_SITES_X11 | N | N | EXTN_ATTRIBUTE_NUMBER023 |
| HZ_PARTY_SITES_X12 | N | N | EXTN_ATTRIBUTE_NUMBER024 |
| HZ_PARTY_SITES_X13 | N | N | EXTN_ATTRIBUTE_CHAR040 |
| HZ_PARTY_SITES_X14 | N | N | EXTN_ATTRIBUTE_CHAR041 |
| HZ_PARTY_SITES_X15 | N | N | EXTN_ATTRIBUTE_CHAR042 |
| HZ_PARTY_SITES_X16 | N | N | EXTN_ATTRIBUTE_NUMBER030 |
| HZ_PARTY_SITES_X17 | N | N | EXTN_ATTRIBUTE_NUMBER031 |
| HZ_PARTY_SITES_X18 | N | N | EXTN_ATTRIBUTE_NUMBER032 |
| HZ_PARTY_SITES_X19 | N | N | EXTN_ATTRIBUTE_CHAR043 |
| HZ_PARTY_SITES_X20 | N | N | EXTN_ATTRIBUTE_CHAR044 |
Constraints
| CONSTRAINT NAME | CONSTRAINT TYPE | SEARCH_CONDITION_VC |
| SYS_C00125510 | C | “CREATED_BY” IS NOT NULL |
| SYS_C00125513 | C | “STATUS” IS NOT NULL |
| SYS_C00125515 | C | “CONFLICT_ID” IS NOT NULL |
| SYS_C00125504 | C | “PARTY_SITE_ID” IS NOT NULL |
| SYS_C00125511 | C | “PARTY_SITE_NUMBER” IS NOT NULL |
| SYS_C00125509 | C | “CREATION_DATE” IS NOT NULL |
| SYS_C00125512 | C | “IDENTIFYING_ADDRESS_FLAG” IS NOT NULL |
| HZ_PARTY_SITES_PK | P | |
| SYS_C00125506 | C | “LOCATION_ID” IS NOT NULL |
| SYS_C00125514 | C | “OBJECT_VERSION_NUMBER” IS NOT NULL |
| SYS_C00125507 | C | “LAST_UPDATE_DATE” IS NOT NULL |
| SYS_C00125508 | C | “LAST_UPDATED_BY” IS NOT NULL |
| SYS_C00125505 | C | “PARTY_ID” IS NOT NULL |
Related Posts
Supplier Details Query in Oracle Fusion – POZ_SUPPLIERS
Vendor Site – Invoicing & Payments Query
Query to get the Vendor Fiscal Classification
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
