Avenuelucky

• Example Of Surrogate Key
• Sql Surrogate Key
• What Is A Surrogate Key
• Database Surrogate Key
• Generate Surrogate Key In Oracle Account
• Create Surrogate Key In Oracle
• What Is Surrogate Key
• Oracle Surrogate Key

Creating a surrogate key using GUID column. GUID is a 16-byte binary data type in SQL Server. According to the way that GUIDs are formed, there’s nearly impossible that there will ever be any duplication in their values.

Recommendations and examples for using the IDENTITY property to create surrogate keys on tables in Synapse SQL pool.

What is a surrogate key

A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

Creating a table with an IDENTITY column

The IDENTITY property is designed to scale out across all the distributions in the Synapse SQL pool without affecting load performance. Therefore, the implementation of IDENTITY is oriented toward achieving these goals.

You can define a table as having the IDENTITY property when you first create the table by using syntax that is similar to the following statement:

You can then use INSERT..SELECT to populate the table.

This remainder of this section highlights the nuances of the implementation to help you understand them more fully.

Allocation of values

The IDENTITY property doesn't guarantee the order in which the surrogate values are allocated, which reflects the behavior of SQL Server and Azure SQL Database. However, in Synapse SQL pool, the absence of a guarantee is more pronounced.

The following example is an illustration:

In the preceding example, two rows landed in distribution 1. The first row has the surrogate value of 1 in column C1, and the second row has the surrogate value of 61. Both of these values were generated by the IDENTITY property. However, the allocation of the values is not contiguous. This behavior is by design.

Skewed data

The range of values for the data type are spread evenly across the distributions. If a distributed table suffers from skewed data, then the range of values available to the datatype can be exhausted prematurely. For example, if all the data ends up in a single distribution, then effectively the table has access to only one-sixtieth of the values of the data type. For this reason, the IDENTITY property is limited to INT and BIGINT data types only.

SELECT..INTO

When an existing IDENTITY column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

• The SELECT statement contains a join.
• Multiple SELECT statements are joined by using UNION.
• The IDENTITY column is listed more than one time in the SELECT list.
• The IDENTITY column is part of an expression.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) follows the same SQL Server behavior that's documented for SELECT..INTO. However, you can't specify an IDENTITY property in the column definition of the CREATE TABLE part of the statement. You also can't use the IDENTITY function in the SELECT part of the CTAS. To populate a table, you need to use CREATE TABLE to define the table followed by INSERT..SELECT to populate it.

Explicitly inserting values into an IDENTITY column

Synapse SQL pool supports SET IDENTITY_INSERT <your table> ON|OFF syntax. You can use this syntax to explicitly insert values into the IDENTITY column.

Many data modelers like to use predefined negative values for certain rows in their dimensions. An example is the -1 or 'unknown member' row.

The next script shows how to explicitly add this row by using SET IDENTITY_INSERT:

Loading data

The presence of the IDENTITY property has some implications to yourt be used:

Example Of Surrogate Key

• When the column data type is not INT or BIGINT
• When the column is also the distribution key
• When the table is an external table

The following related functions are not supported in Synapse SQL pool:

Common tasks

This section provides some sample code you can use to perform common tasks when you work with IDENTITY columns.

Column C1 is the IDENTITY in all the following tasks.

Find the highest allocated value for a table

Use the MAX() function to determine the highest value allocated for a distributed table:

Find the seed and increment for the IDENTITY property

You can use the catalog views to discover the identity increment and seed configuration values for a table by using the following query:

Next steps

This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips.

This chapter covers the following topics:

Overview

This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips:

Characteristics of the Oracle CZ Schema

For a description of the CZ schema, see Oracle CZ Schema.

Online Tables and Integration Tables

The CZ schema contains online and integration tables. The online and integration tables are organized into subschemas for storing the data of configuration models and saved configurations.

The online tables contain the data that is used by Oracle Configurator Developer and the runtime Oracle Configurator. Every online table that receives imported data has a corresponding import table. For example, CZ_ITEM_TYPES is populated with data from the CZ_IMP_ITEM_TYPE table during the import process. See CZ Subschemas for more information about the CZ subschemas.

The integration tables consist of import and control tables. See Import Tables for information about the import tables and Control Tables for information about control tables. See Populating the CZ Schema for information about using the integration tables.

CZ Subschemas

Both the online and integration tables of the CZ schema are organized into subschemas:

• ADMN - Administrative

• CNFG - Saved Configurations

• ITEM - Item Master

• LCE - Logic for Configuration (Generate Logic)

• PB - Publication

• PROJ - Project Structure

• RP - Repository

• RULE - Rule

• TXT - Text

• TYP - Data Typing

• UI - User Interface

• XFR - Transfer specifications and control

Additionally, there are some key table views:

• CZ_CONFIG_DETAILS_V stores selected BOM Model node records.

• CZ_CONFIG_ITEMS_V stores all selected node records for both BOM Models and Oracle Developer Models

See CZ Subschemas for a listing of tables in each subschema. For table details, see the CZ eTRM on MetaLink, Oracle’s technical support Web site.

Public Synonyms

The CZ schema does not use public synonyms.

Schema Customization

Customizing the data model of the CZ schema is not recommended, because such customizations may not be preserved during an upgrade or migration.

Various user expansion fields in the CZ schema, such as USERNUMn and USERSTRn in the CZ_PS_NODES table, are available for custom use. The data in the user expansion fields is preserved during a schema upgrade or migration. For more information, see the CZ eTRM on MetaLink, Oracle’s technical support Web site.

Import Tables

Every import table corresponds to an online table both structurally and relationally. Each import table contains the same fields as the corresponding online table, as well as additional fields to manage the import and correlate the data with the existing data in the online table.

Import tables consist of:

Because import tables are meant to capture as much data as possible, all fields are nullable and there are no integrity constraints such as primary-key definitions, unique indexes, or foreign-key references. The import tables allow batch population of the CZ schema’s online tables.

Each import table’s name is similar to its online counterpart. Import tables have CZ_IMP prefix instead of just CZ_. For example, the imported data in CZ_IMP_PROPERTY populates CZ_PROPERTIES, and CZ_IMP_ITEM_TYPE populates CZ_ITEM_TYPES.

The import tables temporarily store extracted or legacy data that concurrent programs access when creating, updating, or deleting records in the CZ schema. The CZ_IMP tables are populated by running the Populate or Refresh Configuration Models concurrent programs. For more information see Populate and Refresh Configuration Models Concurrent Programs.

For more information about:

• How data moves from sources outside the CZ schema through the import tables to the online tables, see Populating the CZ Schema

• Dependencies among import tables and import table codes, see Dependencies Among Import Tables.

Import Control Fields

Import control fields contain data that is used to manage the import process for each record. Import control data is not transferred to the online tables and is not used to resolve key values or anything else. Import Control Fields describes the import control fields.

The following table shows the Import Control Fields including field name, data type, and description.

Online Data Fields

The import tables’ data fields exactly match the fields in the corresponding online table and are used to hold the data to be put into the online table.

Surrogate Key Fields

Surrogate key fields in the import tables hold the customer-provided extrinsic identifications for data to be imported. These include both foreign surrogate keys and surrogate primary keys.

Foreign Surrogate Key – A foreign surrogate key is a reference to a different table made through that table’s surrogate primary key rather than through the online table’s integer key value. A foreign surrogate key consists of one or more fields that resolve references from one import table to another. These keys are named FSK_table_refno_fldnum, where table is the name of the referenced table, refno is the number of the table-to-table reference, and fldnum is the position of the referenced surrogate-key field in the referenced import table. Note that refno is required to keep unique names for tables with multiple references to the same table, and generally, the fldnum is 1.

Surrogate Primary Key – As a rule, imported tables contain a single field named ORIG_SYS_REF, which is used to hold the external value that uniquely identifies each record. In some cases, however, the online CZ table has a primary key consisting entirely of references to other tables. In this case, the surrogate primary key actually consists of the foreign surrogate keys that correspond to the native foreign keys in the online table.

Dependencies Among Import Tables

Dependencies among import tables must be heeded especially when custom importing single tables. Dependencies Among CZ Schema Import Tables, 'Foreign Surrogate Key' lists the column in the import table whose value is dependent on the table listed in 'Depends on'. For example, the FSK_ITEMTYPE_1_1 column in CZ_IMP_ITEM_MASTER gets its value from CZ_IMP_ITEM_TYPE.NAME and helps in key resolution. FSK_ITEMTYPE_1_1 (default) is populated depending on the PK_USEEXPANSION indicator (0, 1, or 2) in CZ_XFR_TABLES. See Populating Import Tables for the order in which the CZ_IMP tables are populated.

Note: Oracle recommends that limited usage of FSK_***_EXT columns as these columns will eventually be desupported.

A strong dependency means a value is required to successfully import that record. If Default is YES, then there is a default value in that column and import succeeds even if the dependency is strong and no value is imported. The following Dependencies Among CZ Schema Import Tables lists the dependencies.

The following table shows the dependencies between the import tables.

Control Tables

The control tables provide the mechanism for controlling what data is imported or refreshed when populating the CZ schema import tables with data from outside sources. The control table names are prefixed with CZ_XFR.

When running Oracle Configurator Populate and Refresh Configuration Models Concurrent Programs, records in the CZ_XFR tables determine which import tables are enabled for import, what data is imported, and how the data is imported.

The following tables control the import process at the table and field level:

• CZ_XFR_FIELDS

• CZ_XFR_PROJECT_BILLS

• CZ_XFR_TABLES

The following tables contain import information:

• CZ_XFR_RUN_INFOS

• CZ_XFR_RUN_RESULTS

• CZ_XFR_STATUS_CODES

CZ_XFR_TABLES identifies the mapping of the import table to the online table, as well as the rules for importing data into the CZ schema.

CZ_XFR_FIELDS identifies the transfer rules for the fields that are transferred during the Populate or Refresh Configuration Models concurrent programs. Every field is updated during import or refresh, but the update can be retracted by using the NOUPDATE flag in the CZ_XFR_FIELDS table. If a field that is transferred does not have an entry in the CZ_XFR_FIELDS table, then that field is updated.

For example, setting the NOUPDATE flag to 1 in the CZ_XFR_FIELDS table for CZ_ITEM_MASTERS.DESC_TEXT, inhibits the updating of the Item Master description in CZ_ITEM_MASTERS.DESC_TEXT when a Model is refreshed. Setting a value in the CZ_XFR_FIELDS Table shows how to set the field in the CZ_XFR_FIELDS table so that changes made to the BOM Model’s Item description do not appear in Oracle Configurator Developer.

Setting a value in the CZ_XFR_FIELDS Table

CZ_DB_SETTINGS Table

The CZ_DB_SETTINGS table provides parameters that affect certain applications and CZ schema processes.

Only one CZ_DB_SETTINGS table exists in a CZ schema.

Accessing the CZ_DB_SETTINGS Table

A user’s responsibility determines whether they can view or edit the CZ_DB_SETTINGS table. A user must have the Oracle Configurator Administrator responsibility to edit the CZ_DB_SETTINGS table through concurrent programs. For more information, see View Configurator Parameters and Modify Configurator Parameters.

Organization of the CZ_DB_SETTINGS Table

The parameters in the CZ_DB_SETTINGS table are mapped to a particular section of the CZ schema. The particular section is identified in the SECTION_NAME field and contains relevant database parameters. The sections are:

• IMPORT - Controls how BOM Model data is imported into the CZ schema

• LogicGen - Governs how the Model’s logic is generated

• ORAAPPS_INTEGRATE - Controls how Oracle Configurator integrates with other Oracle Applications

• SCHEMA - Sets general parameters that control the CZ schema

• UISERVER - Governs the behavior of the runtime Oracle Configurator user interface

Each parameter contains the following fields:

• DATA_TYPE specifies the parameter’s datatype. All CZ_DB_SETTINGS values are stored as VARCHAR2(255) in the VALUE field. If the DATA_TYPE is an integer, then the Configurator converts the data in the VALUE field to an integer before using it. For example, the Batchsize default value is stored as string 10000, but Configurator interprets string 10000 as integer 10000.

• SETTING_ID identifies the parameter.

• VALUE is the parameter’s data. This value may be set during an installation or upgrade of the database instance. The Oracle Configurator Administrator can modify a value by running the Modify Configurator Parameters concurrent program.

CZ_DB_SETTINGS Parameters

Some of the CZ_DB_SETTINGS parameter values are predefined during an installation or upgrade of Oracle Configurator. The Oracle Configurator Administrator can modify the values of these parameters by running the Modify Configurator Parameters concurrent program. For information on running concurrent programs, see Running Configurator Concurrent Programs. For specific information on modifying the parameters in the CZ_DB_SETTINGS table, see Settings in CZ_DB_SETTINGS Table that lists the parameters in the CZ_DB_SETTINGS table that can be modified.

AltBatchValidateURL

AltBatchValidateURL allows the batch validation process to bypass the URL that is normally used for batch validation. This might be necessary if your database cannot communicate with your Web server.

If Oracle Configurator uses Secure Sockets Layer (SSL), then you can enable batch validation by creating an additional non-SSL-enabled (HTTP) servlet port and specifying its URL as the value of AltBatchValidateURL. For additional SSL information, see MetaLink, Oracle’s technical support Web site.

If your configurator servlet is set up to use HTTPS, then you can set AltBatchValidateURL to be a servlet using HTTP, and avoid some SSL encryption and handshaking overhead. Since the communication is between the Application database and the Application middle tier, communication does not cross the internet and thus HTTPS may not be necessary. This configuration does require extra setup and is not required.

You can also enable batch validation by using your existing SSL-enabled port for Oracle Configurator and setting up the Oracle Wallet for use by Oracle Configurator, as described on OracleMetaLink. In this case, you do not set any value for AltBatchValidateURL.

If you use a firewall, have your database setup in a DMZ, or have some other network configuration where the database cannot communicate with the web server, then you should set up an internal web server. After setting up an internal web server, you must then set the AltBatchValidateURL setting in the CZ_DB_SETTINGS table to be the URL for the configurator servlet on your internal web server.

For more information regarding DMZ setup, see MetaLink, Oracle’s technical support Web site.

To insert the AltBatchValidateURL into the CZ_DB_SETTINGS table, use the SQL INSERT statement shown in Adding AltBatchValidateURL to CZ_DB_SETTINGS.

Adding AltBatchValidateURL to CZ_DB_SETTINGS

BadItemPropertyValue

BadItemPropertyValue indicates the action that is taken when an Item’s PROPERTY _VALUE in the CZ_IMP_ITEM_PROPERTY_VALUES table does not match the DATA_TYPE in the CZ_PROPERTIES online table. The default value (F) forces the record to be updated to include the PROPERTY_VALUE so that it is imported into the CZ_ITEM_PROPERTY_VALUES online table. Valid Values for the BadItemPropertyValue Setting lists the valid values for BadItemPropertyValue setting and the disposition:

The following table lists the valid values for the BadItemProperty value setting, along with a description of the value.

BatchSize

BatchSize indicates the number of records that are modified before committing a transaction in batch operations. The BatchSize setting is also used during a purge operation.

Ordinarily a database stored procedure runs as a single transaction that is considered pending until the calling operation commits the transaction. The pending changes are lined up in a rollback segment. If the calling operation is cancelled, then the transaction is rolled back. If the calling operation encounters an error, then the pending changes in the rollback segment are discarded. However, some batch operations, such as import, can involve many more records than the database can handle as a single transaction. If the transaction is too big, then the database fails an operation with a rollback-segment error. To avoid a rollback_segment error, import and other batch-like operations count up the modified records in the database and when the count matches the BatchSize value, the operation commits the transaction and resets the counter. Every record is not committed individually because it is considerably more economical to commit many updates at once.

BOM_REVISION

BOM_REVISION indicates the BOM revision in the Oracle Applications database from which data is being imported into the CZ schema. This setting is checked to ensure that the correct date format is used in the call to the BOM Model explosion procedure.

The value of BOM_REVISION is the Oracle Applications revision number used to determine which explosion date format to use. . Valid values are 5.0.628 for Release 10.7, 11.0.28 for Release 11.0, and 11.5.0 for both Release 11i and Release 12. If the value is null (default), then 11.5.0 is used. The call to the BOM Model explosion procedure checks up to the second decimal point of this value.

If the value is 11.5.n or Release 12, then the date format YYYY-MM-DD is used. Otherwise, DD/MON/RR is used for Release 10.7 or 11.0.

CommitSize

CommitSize indicates the number of import records in each database transaction between commits. CommitSize has the same purpose as BatchSize. for more information, see BatchSize. CommitSize is used during import.

DISPLAY_INSTANCE_NAME

DISPLAY_INSTANCE_NAME determines whether an Instance Name column appears in the Oracle Configurator Summary page. Oracle Configurator checks this setting only if multiple instances of one or more components exist in the configuration.

If DISPLAY_INSTANCE_NAME is set to TRUE and at least one component in the configuration has multiple instances, then the Instance Name column appears and displays the name of each instance.

If DISPLAY_INSTANCE_NAME is set to FALSE or there are no components with multiple instances in the configuration, then the Instance Name column does not appear. If set to False but there are multiple instances in the configuration, then instance names appear in the Description column (instead of each Item’s description).

FREEZE_REVISION

FREEZE_REVISION indicates the revision number at the freeze stage. This parameter is used to capture the revision levels for the implementation of database package bodies and views. For example, if a table is tuned to improve performance, but the fields and the data returned are the same, then there is no need to change the MAJOR_VERSION or MINOR_VERSION but the FREEZE_REVISION value reflects the reworked view. This setting is read-only and populated when applying a patch.

GenerateGatedCombo

GenerateGatedCombo determines how a FALSE logic state is propagated in Explicit Compatibility, Property-based Compatibility and Design Chart Rules. See the Oracle Configurator Developer User’s Guide for additional information about Gated Combinations.

GenerateUpdatedOnly

GenerateUpdatedOnly set to YES, causes logic generation to skip all referenced Models whose logic is up-to-date. GenerateUpdatedOnly set to NO causes the logic of all referenced Models to be generated even if their logic is up-to-date.

GenStatisticsBOM

GenStatisticsBOM set to YES forces the optimizer to update the internal statistics on the BOM_EXPLOSIONS table before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.

GenStatisticsCZ

GenStatisticsCZ set to YES forces the optimizer to update the internal statistics on the entire CZ schema before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.

MAJOR_VERSION

MAJOR_VERSION indicates the major version label for the CZ schema. This setting is read-only and is populated when upgrading the schema.

MaximumErrors

MaximumErrors indicates the limit of errors allowed before an import run is terminated. If you have a large amount of data to import, or you are not concerned with the process stopping once a certain number of errors is reached, then set this parameter to an extremely large number.

MemoryBulkSize

MemoryBulkSize regulates the memory usage of import. The smaller the setting, the less memory is required for import. This number is used during import for the cz_ps_nodes extraction procedure for specifying the number of records that are processed in the same pass. If the value entered is less than the total number of records to be imported, then the specified number of records is loaded and processed, and then the next group of records is loaded and processed. If there is no value entered, then the MemoryBulkSize is set to 10000000.

MINOR_VERSION

MINOR_VERSION indicates the minor version label for the CZ schema. This value is read-only and is populated when applying a patch. The MINOR_VERSION does not change during a particular family pack release.

MULTISESSION

MULTISESSION indicates the way in which a new import session interacts with other import sessions.

• A positive value indicates the number of seconds to wait while another import session is running. The current state is checked every second. After the number of seconds has elapsed, control goes to the waiting import session if no other session is active, or an exception is raised if another import session is still running.

• A value of 0 means do not wait if another import session is running, and immediately raise an exception if a session is already running.

• A negative value means ignore other import sessions and run this import session immediately without raising an exception. Setting this parameter to a negative number is equivalent to disabling it. If a session is currently running and a new import session begins, then the first session is not aborted and there is the risk of data corruption.

When MULTISESSION is missing from the CZ_DB_SETTINGS table, it is equivalent to the default 0.

If an import session is terminated, then the CZ_XFR_RUN_INFOS table may end up in an inconsistent state with the value of COMPLETED something other than 1.

OracleSequenceIncr

OracleSequenceIncr indicates the number of primary-key values allocated by each use of a sequence. The default setting means that keys are assigned in increments of 20. Both runtime Oracle Configurator and Configurator Developer ask for a sequence value once, and then manage the sequence value minus 1 in memory. When the block is used up, runtime Oracle Configurator and Configurator Developer again call for a sequence value. Keeping the default value at 20 saves round trips to the database.

Warning: Changing the default OracleSequenceIncr setting of 20 is likely to have adverse effects. The value of OracleSequenceIncr should not be modified.

PsNodeName

PsNodeName indicates the source field to be loaded into the NAME field in the CZ_PS_NODES table. The source field is either the RefPartNbr or the DESCRIPTION field in the MTL_SYSTEM_ITEMS table. RefPartNbr is the default so that the name loaded into the Model structure in Oracle Configurator Developer matches the name in CZ_ITEM_MASTERS.

PublicationLocalBOMSynch

PublicationLocalBOMSynch controls whether the BOM Synchronization process runs automatically when a publication is created for a Model that was imported from a remote server, on the same instance in which Configurator Developer is running. If BOM Synchronization does not run in this scenario, it is possible for the publication lookup process to fail if the host application is also running on the local instance that launched Oracle Configurator.

To automatically run BOM Synchronization in the scenario described above, set the CZ_DB_SETTINGS parameter PublicationLocalBOMSynch to YES by running the Modify Configurator Parameters concurrent program. Enter the following parameters when submitting this program:

• Section Name: Publication

• Setting ID: PublicationLocalBOMSynch

• Value: YES

• Type: 4

PublicationLogging

PublicationLogging indicates whether a trace of the publication process is logged in the CZ_DB_LOGS table. The trace is helpful for debugging purposes and can be viewed in the log file. For more information about viewing log files, see Viewing Log Files.

PublishingCopyRules

PublishingCopyRules indicates whether or not configuration rules are copied during publishing. If PublishingCopyRules is set to NO, then only Configurator Extension rules are copied during publishing. The publishing process is faster when PublishingCopyRules is set to NO.

If the PublishingCopyRules is set to YES, then all rules are copied and both the source and published Models have the same rules.

Note: Setting 'PublishingCopyRules' to 'NO' only affects you if changes are made to logic generation that are incompatible with previous versions of Oracle Configurator. If the rules for a published Model are not copied, then you cannot generate logic for the published Models. Using the NO setting requires republishing all published Models.

PurgeDeleteConfigBatchsize

When you run the concurrent program Purge Configurator Tables, you can control its commit behavior by setting this parameter, which specifies how often the purge program issues a commit, in terms of a number of configurations. For example, a value of 200 specifies a commit after deleting a batch of 200 configurations.

RefPartNbr

RefPartNbr identifies the source fields that are loaded from the MTL_SYSTEM_ITEMS table into CZ_ITEM_MASTERS.REF_PART_NBR. This is a segment from the System Item key flexfield definition.

RefPartNbr determines what name is displayed for each imported Model structure node. The default value ’CONCATENATED_SEGMENTS’ enables the BOM Model import process to construct BOM Model node names using multi-segment part numbers.

When RefPartNbr is set to ’SEGMENT1’, only MTL_SYSTEM_ITEMS.SEGMENT1 is the source of the node names in the imported Model structure. If you want to use only the first segment of a part number as the node name, the Oracle Configurator Administrator must manually set RefPartNbr to ’SEGMENT1’ by running the Modify Configurator Parameters concurrent program.

Any value for RefPartNbr other than ’CONCATENATED_SEGMENTS’ or ’SEGMENT1’ causes the import process to retrieve the value of the DESCRIPTION column from MTL_SYSTEM_ITEMS and displays the Item description as the node name in Configurator Developer.

Warning: Examine MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS to verify that the field is correctly populated. If the field is incorrectly populated, then the entry in Oracle Inventory may be wrong. If the entry is correct, check CZ_IMP_ITEM_MASTER.REF_PART_NBR to see that the value is the same as that in MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS.

Concatenated segments, including separators, must not exceed 1000 characters, which is the limit of the CZ_PS_NODES.NAME field. Any description longer than 1000 characters is truncated. The default separator is a dot (.). Other valid separators are |, -, or a custom value. See the Oracle Inventory User’s Guide for more information about setting up part numbers.

You can enter multi-segment Items in the From Item and To Item input fields when you run either the Populate or Refresh Configuration Models concurrent program. You must include any separators that exist in the Item’s part number when you enter multi-segment Item names.

Warning: When updating an existing Model in Configurator Developer to use multi- segment part numbers, you must either reimport or refresh the BOM Model. Confirm that the BOM Model is getting re-exploded during import. The CZ_DB_SETTINGS.RUN_BILL_EXPLODER should be Yes.

ResolvePropertyDataType

ResolvePropertyDataType controls whether Item Catalog Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text or Decimal Number. If the value for this setting is NO, all imported Item Properties have a data type of Text in Configurator Developer.

Sql Surrogate Key

If the value of this setting is YES, then all Descriptive Elements whose value is a number are imported as Item Properties and have a data type of Decimal Number. All Descriptive Elements whose value is text (for example, Weight) have a data type of Text.

If ResolvePropertyDataType is null, then all Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text.

ResolvePropertyDataType Setting table illustrates how ResolvePropertyDataType affects how Descriptive Elements values are imported into Oracle Configurator Developer.

What Is A Surrogate Key

Item Property is a protected field in the CZ schema (the NOUPDATE flag is set during import). Once you import a BOM Model, you cannot change an Item Property’s data type simply by modifying the ResolvePropertyDataType setting and then refreshing the BOM Model.

RestoredConfigDefaultModelLookupDate

RestoredConfigDefaultModelLookupDate setting controls which publication Oracle Configurator uses on an order when called from Order Management. If this setting is config_creation_date, then Oracle Configurator uses the order line creation date. If this setting is null, then Oracle Configurator uses sysdate.

For more information, see DEFAULT_RESTORED_CFG_DATES.

Revision Date and User

Revision Date and User is read-only and documents the date and time at which the CZ schema was last upgraded, and the username of the user who performed the task.

RUN_BILL_EXPLODER

RUN_BILL_EXPLODER is a YES/NO flag (default=YES) that indicates whether the Oracle Applications Bills of Material exploder should be run on each bill that is marked for import in the CZ_XFR_PROJECT_BILLS table in the CZ schema at the time of import. See Populating the CZ Schema for more information on exploding a BOM Model.

The Oracle Configurator Populate or Refresh Configuration Models concurrent programs load bills and Items based on top bills listed in the CZ_XFR_PROJECT_BILLS table in the CZ schema. Before extracting, if the RUN_BILL_EXPLODER setting is set to YES, then the procedure calls the BOM Model exploder to refresh data in BOM_EXPLOSIONS for each record in the CZ_XFR_PROJECT_BILLS table. If RUN_BILL_EXPLODER is set to NO, then the concurrent program transfers the BOM Models that are flagged for import in the CZ_XFR_PROJECT_BILLS table without running the BOM Model exploder first.

Note: The Populate or Refresh Configuration Models concurrent programs do not explode BOM Models when importing from a remote server. See Exploding BOM Models in Oracle Applications for details.

CZ_INTL_TEXTS contains the text string from the DESCRIPTION field in the BOM_EXPLOSIONS table for each imported BOM Model structure node.

The Oracle Configurator SQL*Plus scripts and concurrent programs target all or a subset of BOM Models exploded in the BOM_EXPLOSIONS table in the Oracle Applications database. Selected BOM Model Items come from the BOM_BILL_OF_MATERIAL and the BOM_INVENTORY_COMPONENTS tables.

Note: Importing a BOM Model from a remote instance may fail if RUN_BILL_EXPLODER is set to YES in the local instance. (In this case, an error message similar to the following appears: 'ORA-03113: end-of-file on communication channel.') If this occurs, set RUN_BILL_EXPLODER flag to No and then re-submit the import concurrent program. The new setting should enable the process to complete successfully.

SuppressSuccessMessage

The SuppressSuccessMessage setting affects runtime Oracle Configurator behavior by suppressing messages that would normally be shown. The setting determines whether a message is displayed after fixing a validation error.

If SuppressSuccessMessage is set to NO, then after fixing a validation error a runtime success message is displayed. If SuppressSuccessMessage is set to YES, then after fixing a validation error a runtime success message is not displayed.

To insert SuppressSucessMessage into CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding SuppressSuccessMessage to CZ_DB_SETTINGS.

Adding SuppressSuccessMessage to CZ_DB_SETTINGS

Database Surrogate Key

TimeImport

TimeImport enables the collection of timing information during import.

UI_NODE_NAME_CONCAT_CHARS

UI_NODE_NAME_CONCAT_CHARS sets the concatenation character that is used when generating UI captions using both the node name and description. The default concatenation character separating each text string is a comma surrounded by two spaces. (For example: 'AT62431 , Sentinal Custom Laptop'). The Oracle Configurator Administrator can change the concatenation character that separates each string by running the Modify Configurator Parameters concurrent program.

UseLocalTableInExtractionViews

UseLocalTableInExtractionViews is a YES/NO flag. If UseLocalTableInExtractionViews is set to YES, then definitions of some import extraction views include the DUAL table in the join. The UseLocalTableInExtractionViews setting is ignored if the import source server is local.

Note: If you are importing or refreshing from a remote database instance and the database instance is version 8i, then UseLocalTableInExtractionViews must be set to YES. This is because of an RDBMS bug. If this setting is not YES, then the following error appears in the cz_db_logs table after running the Populate and Refresh Configuration Models Concurrent Programs :'ORA-01025: UPI parameter out of range'

Generate Surrogate Key In Oracle Account

UtlHttpTransferTimeout

Create Surrogate Key In Oracle

UtlHttpTransferTimeout allows modification of the timeout length that is used inside the call to the UTL_HTTP.REQUEST procedure during batch validation. The value is the number of seconds. Once the call completes, the timeout is set back to its original value.

To insert UtlHttpTransferTimeout into the CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS.

What Is Surrogate Key

Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS

Oracle Surrogate Key

Note: This functionality is available only in Oracle 9i and later.